vrijdag 28 maart 2008

Login "error States" in SQL Log

States Description

2 and 5 Invalid userid
6 Attempt to use a Windows login name with SQL Authentication
7 Login disabled and password mismatch
8 Password mismatch
9 Invalid password
11 and 12 Valid login but server access failure
13 SQL Server service paused
18 Change password required

donderdag 27 maart 2008

SQL Server Management Studio takes a long time to load, how to improve the load performance of this tool?

In general the SQL Server Management Studio takes at least 10 to 15 seconds to load on a user machine. Sometimes the SSMS tool can take a long time to load when there are a lot of SQL Server instances, this problem may also occur on the new 64-bit SQL Server 2005 platform.
One resolution is to add a switch to the Management Studio shortcut as defined below. Access the below screen by going to Start > Programs > SQL Server 2005 program group > 'SQL Server Management Studio' program, right-clicking will get you the following screen by opening the General tab, then select shortcut tab to add 'nosplash' switch:

Also the user needs to tweak the Internet Explorer security settings as follows:
Uncheck two options in Internet Explorer's Advanced Security settings. Namely:
"Check for publisher's certificate revocation"
"Check for server certificate revocation".
Both these steps combined should improve the load time of SQL Server Management Studio.

woensdag 26 maart 2008

How to debug T-SQL stored procedures in SQLServer 2005

We will debug our procedure, not from Management Studio but from the Visual Studio 2005 (only Team Systems or Professional editions) development environment. I mention this because under SQL Server 2000 we are able to debug stored procedures using Query Analyzer. Perhaps debugging capabilities will be added to Management Studio in the future.
Before any debugging can take place, you first must create the store procedure. This can be done either by using Management Studio/New Query or by building the procedure graphically in Visual Studio 2005. Stored procedures can be created in Visual Studio by opening a new database project and using the Visual Studio Installed Templates.
Either way, once the procedure exists, you are ready to start debugging from Visual Studio 2005. When you start up Visual Studio for debugging purposes, you don't have to create a project. Instead, you can create a connection to your database from Server Explorer (You will have to provide your server name along with your login credentials and choose the AdventureWorks database.) Then you can drill down and open up the stored procedures folder. Next, right click on the procedure you wish to debug and choose Step Into Stored Procedure from the context menu. Then you are ready to start debugging!

Note: Before you can start "Remote debugging", you have to install "msvsmon.exe" from the visual studio CD or the sqlserver directory "C:\Program Files\Microsoft SQL Server\90\Shared\1033\rdbgsetup.exe".

Micorosft development team is looking to implement a better way within SQL Server 2008 and as of now 2005 is only way out with Visual Studio.

Moving msdb

Moving MSDB is more complicated than other system databases and requires you to add trace flags and parameters to the SQL Server startup to detach msdb, then remove them and restart SQL Server to attach it:

Start SQL Server with -c, -m and -T3608 and then perform a detach. You would then remove these parameters before attaching MSDB.

vrijdag 21 maart 2008

Moving System Databases

If you are moving system databases (master, msdb, model, tempdb), you would change the location of master by changing the startup parameters, bringing down the instance, and moving the master.mdf and master.ldf files.
Secondly you need to address the model database, because tempdb is reallocated every time the instance starts. This reallocation requires the existence of model to be successful.
The last one you have to re-address is the msdb, because msdb is used by the SQL Server agent and has no bearing on the stability of the database engine.

donderdag 20 maart 2008

Hierarchyid Travesal (SQLServer2008)

In which type of tree traversal would all nodes at a particular level below the root be traversed before those at lower levels?

Given a tree such as this:
/ \
2 3 4
/ \ / \ \
5 6 7 8 9

a traversal of nodes in 1-2-3-4-5 order would be a breadth-first traversal.