Archive for the ‘SQL Server 2000’ Category
Posted by jpluimers on 2013/01/18
In most database index nodes are doubly linked to allow bi-directional scans. http://is.gd/8CMb7w, however not for InterBase and FireBird, there the reverse link isn’t used because it can be inconsistent due to write order of index pages.
The result is that in Firebird and InterBase, indexes are single-directional (either ascending or descending).
This is for your safety: it guarantees index consistency, even if because of EMP, your machine suddenly reboots after your tank fired a missile.
–jeroen
via Twitter / Avalanche1979: @SQLPerfTips For Firebird the ….
(Wow, did I really wrote 1200 blog posts?)
Posted in Database Development, DB2, Development, Firebird, InterBase, MySQL, OracleDB, PostgreSQL, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 7, Sybase | Leave a Comment »
Posted by jpluimers on 2012/12/25
In SQL Server 2000 and up, the easiest way to re-add a user that got orphaned is with a script like below.
The script does not correct the SID, but in stead sets new permissions (in this case, db_datareader and db_datawriter). Most of the times that is not a problem.
The script uses these stored procedures:
- sp_dropuser – drops a user from the current database
- sp_droplogin – drops a login from the current server
- sp_addlogin – adds a login to the database server
- sp_adduser – adds a user to the current database (you can add both a SQL user – with name and password – and a Windows user)
- sp_addrolemember – adds a member to a certain role
More modern versions have alternatives to these stored procedures, but the stored procedures work with the widest ranges of SQL Server versions.
-- Execute this script as SA or DB Administrator
use MyDatabase -- the databae where you want the user to be re-added to
-- you cannot perform a 'use [MyDatabase]' without destroying the context (and declared variables)
-- http://stackoverflow.com/questions/9165513/sql-server-change-current-database-via-variable
-- it is possible with exec (@exec_stmt) (as sp_droplogin does it), but it is a bit cumbersome for a relatively simple script
declare @loginname sysname
declare @passwd sysname
set @loginname = 'MyUser'
set @passwd = 'MyPassword'
-- from current database
exec sp_dropuser @loginname
-- from server
exec sp_droplogin @loginname
-- to current server
exec sp_addlogin @loginname, @passwd
-- to current database
exec sp_adduser @loginname
-- roles to add the user to
exec sp_addrolemember db_datareader, @loginname
exec sp_addrolemember db_datawriter, @loginname
If the user didn’t exist in the database, or didn’t exist as a login on the server, you can get two errors like these: Read the rest of this entry »
Posted in Database Development, Development, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 7 | Leave a Comment »
Posted by jpluimers on 2012/11/29
Great post: SQL Server: JOIN vs IN vs EXISTS – the logical difference.
Be aware of the three valued logic when NULL gets involved.
EXIST comes closes to what you expect.
And be aware that with IN, you cannod pass one parameter containing more than 1 IN value: sql server 2005 – Passing multiple values for one SQL parameter – Stack Overflow..
–jeroen
Posted in Database Development, Development, SQL, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 7 | Leave a Comment »
Posted by jpluimers on 2012/11/27
I could only find the System Error Messages overview for:
Are there any such links for SQL Server 2005 and up?
The odd thing is online lists of Database Engine Error Severities are available for multiple versions of SQL Server: 2005, 2008 and 2008 R2.
–jeroen
Posted in Database Development, Development, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 7 | 1 Comment »
Posted by jpluimers on 2012/11/22
Thanks SQLMenace (Denis Gobo) for this great tip on getting min/max/average row sizes (and more) using DBCC SHOWCONTIG.
Don’t forget the “with tableresults”, without it, it will skip the min/max/average recordsize from the results, and present the results as text (not as a row).
Run DBCC SHOWCONTIG with your table name
dbcc showcontig ('TableName') with tableresults
then look at max min and average record size
This feature works at least from SQL Server 2000 onward, though somewhere after SQL Server 2012 it will be removed.
As of SQL Server 2005 you can use sys.dm_db_index_physical_stats. An example on how to use that is here.
It just made me shiver when finding out an unindexed table with 9 million rows averaging about 300 bytes took 8 minutes to query.
Time to add some indexes, and have someone look at the disk back-end.
–jeroen
via: sql server – Size of a single Record ? SQL – Stack Overflow.
Posted in Database Development, Development, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 | Leave a Comment »
Posted by jpluimers on 2012/10/16
Being used to do Unit Testing in most of my regular code, I want to do the same for SQL Server code.
Target is SQL Server 2000 and up (since I’m involved in a big migration project getting a lot of SQL Server 2000 data and code to be upgraded to SQL Server 2012 or SQL Server 2008 R2).
Here are a few links that are on my research list:
–jeroen
Posted in Database Development, Development, Software Development, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 | Leave a Comment »
Posted by jpluimers on 2012/10/11
Never call this encryption, as ROT13 is just a kind of obfuscation for text and can be easily revealed (like the reveal button in good old Teletext).
A few T-SQL versions of this algorithm, both working for SQL Server 2000 and up:
–jeroen
Posted in Database Development, Development, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 | Leave a Comment »
Posted by jpluimers on 2012/10/09
As a follow-up on my earlier number validation posts (Elf proef in C# and Other number verifications), I found a nice T-SQL version of the Elfproef for Dutch bank account numbers.
It works at least from SQL Server 2000 and up, most likely also in the (unsupported) SQL Server 7.
–jeroen
via: Elfproef als T-SQL UDF.
Posted in .NET, C#, Database Development, Development, Software Development, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 7 | Leave a Comment »
Posted by jpluimers on 2012/09/26
Database Workbench is my tool of choice for doing database work: it supports many backends in a consistent manner, and behaves a lot like Delphi (like running and debugging stored procedures).
Yesterday the free Lite Editions of Version 4.3.1 got released:
Database Workbench 4.3.1 free Lite Editions released
This new release of Database Workbench brings new features and enhancements, as requested by our users.
The free Lite Editions are now available.
More information about Database Workbench is available at the Database Workbench page, download your copy today via our downloads page, pricing information is available, the limited Lite Editions are available for free.
Changes in this release
- The full details and list of changes are available here and here.
- New
- MySQL Stored Procedure, Function and Trigger Debugging (Pro Edition only)
- InterBase and Firebird syntax check in Trigger Editor (Pro Edition only)
- Incremental search of data in SQL, Table and View Editor
Changes
- More compact taskbar
- MySQL support for BINARY and VARBINARY datatypes
- MySQL error fixed when not having access to mysql.procs
- MySQL fix for fetching foreign key information
- Data Import and Export fixes
- Windows 7 event log error by SideBySide fixed
And much more…
–jeroen
via News @ Upscene Productions.
Posted in Database Development, Delphi, Development, Firebird, InterBase, MySQL, OracleDB, PostgreSQL, Software Development, SQL, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 7 | 1 Comment »
Posted by jpluimers on 2012/09/12
Every once in a while I need something like this, and I always forget the best way to do it: pad numbers so they start with leading zeros so they appear fixed with.
The SQLAuthority phrases it:
There is no ready made function in SQL Server which offers this solution but we can quickly write up something very simple.
SQLUSA has some more ways to do it, and all basically come down to this:
- Convert your number to VARCHAR (use CAST or CONVERT) .
- Prepend the converted number with the maximum number of zeros you require (either with a fixed length string like
'00000000', or use the REPLICATE function).
- Take the right most characters of the required length using the RIGHT function.
You can do similar things with LEFT and padding (for instance with spaces to the right).
One example:
SELECT RIGHT('0000000000' + CAST(31415 AS VARCHAR(10)), 10) AS PaddedPiInteger
SELECT LEFT(CAST(31415 AS VARCHAR(10)) + ' ', 10) AS PaddedPiInteger
–jeroen
via SQL SERVER – Pad Ride Side of Number with 0 – Fixed Width Number Display « SQL Server Journey with SQL Authority.
Posted in Database Development, Development, SQL, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 7 | Leave a Comment »
The Wiert Corner - irregular stream of stuff 