Monthly Archives: December 2009

How to find startup parameters for SQL Server 2005 using WMI

Posted on by

SQL Server startup parameters -d, -l, -e which store the information about the master database data file, log file and ERRORLOG locations respectively are stored under the registry key: 
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\MSSQLServer\Parameters

However, if you need to find out the startup parameters without using the regedit snap-in (default and any extra ones added), you can use the following script:

 <<SCRIPT>>

strComputer = "."
 

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\Microsoft\SqlServer\ComputerManagement")

 

Set colItems = objWMIService.ExecQuery( _

 

"SELECT * FROM SqlServiceAdvancedProperty WHERE SqlServiceType = 1 and PropertyName = 'STARTUPPARAMETERS'",,48)

 

For Each objItem in colItems

 

Wscript.Echo "ServiceName: " & objItem.ServiceName

 

Wscript.Echo "PropertyName: " & objItem.PropertyName

 

Wscript.Echo "PropertyStrValue: " & objItem.PropertyStrValue

 

Next

 

 <</SCRIPT>

Cannot modify table column in SQL Server 2008 using Table Designer

Posted on by

There is a new option added in SQL Server 2008 for modifying tables while using the Table Designed in Management Studio which will prevent saving any changes to a table that needs a drop/re-create of the table. The error that you would see in the Management Studio is:

TITLE: Microsoft SQL Server Management Studio
——————————

User canceled out of save dialog
(MS Visual Database Tools)

If you want to modify a table property through the UI which requires a table recreation, then you need to change the following options in Management Studio:

Tools ->Options-> Designers -> Table and Database Designers -> uncheck the option "Prevent saving changes that require table re-creation"

How to script out indexes for a database using DMO

Posted on by

Create the following stored procedure in your database: 

CREATE PROCEDURE [dbo].[proc_genscript]
 

@ServerName varchar(30),
 

@DBName varchar(30),
 

@ObjectName varchar(50),
 

@ObjectType varchar(10),
 

@TableName varchar(50),
 

@ScriptFile varchar(255)
 

AS
 

DECLARE @CmdStr varchar(255)
 

DECLARE @object int
 

DECLARE @hr int
 

SET NOCOUNT ON
 

SET @CmdStr = 'Connect('+@ServerName+')'
 

EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT
 

EXEC @hr = sp_OASetProperty @object, 'LoginSecure', TRUE
 

EXEC @hr = sp_OAMethod @object,@CmdStr
 

SET @CmdStr =
 

CASE @ObjectType
 

WHEN 'Database' THEN 'Databases("'
 

WHEN 'Procedure' THEN 'Databases("' + @DBName + '").StoredProcedures("'
 

WHEN 'View' THEN 'Databases("' + @DBName + '").Views("'
 

WHEN 'Table' THEN 'Databases("' + @DBName + '").Tables("'
 

WHEN 'Index' THEN 'Databases("' + @DBName + '").Tables("' +
 

@TableName + '").Indexes("'
 

WHEN 'Trigger' THEN 'Databases("' + @DBName + '").Tables("' +
 

@TableName + '").Triggers("'
 

WHEN 'Key' THEN 'Databases("' + @DBName + '").Tables("' + @TableName +
 

'").Keys("'
 

WHEN 'Check' THEN 'Databases("' + @DBName + '").Tables("' +
 

@TableName + '").Checks("'
 

WHEN 'Job' THEN 'Jobserver.Jobs("'
 

END
 

SET @CmdStr = @CmdStr + @ObjectName + '").Script(5,"' + @ScriptFile + '")'
 

EXEC @hr = sp_OAMethod @object, @CmdStr
 

EXEC @hr = sp_OADestroy @object




After the above stored procedure is created, you can create the commands for generating the index creation scripts. 


The script below will generate the CREATE script for the Primary Key for the table tbl_tran_repl (Key name = PK_tbl_tran_repl) and store it in the file PK_tbl_tran_repl.sql under the C:\Database folder. 


Example: 


DECLARE @return_value int
 

EXEC @return_value = [dbo].[proc_genscript]
 

@ServerName = '<SQL Server Name>',
 

@DBName = '<Database Name>',
 

@ObjectName = 'PK_tbl_tran_repl',
 

@ObjectType = 'KEY',
 

@TableName = 'tbl_tran_repl',
 

@ScriptFile = 'C:\Database\PK_tbl_tran_repl.sql'
 

SELECT 'Return Value ' = @return_value




The original script is obtained from Database Journal.

How to backup SQL Server databases to a mapped drive

Posted on by

While taking backups for SQL Server databases onto a mapped drive you might get the following error:

"The system cannot find the path specified."

This is because a network share that you map using a local drive letter will not be visible to a SQL Server instance as it is running as a service. So, the SQL Server service runs in the context of the local console context with the security context of the startup account of SQL Server. Also, mapped drives are specific to a session and not visible to a service started in the local console context.

So, if you want to backup a SQL Server database to a mapped drive using a local drive letter you have the following options:

1. Run the following command from a query window  EXEC xp_cmdshell ‘net use <drivename> <share name>’ — where <drive name>: Letter used to map the drive <share name>: UNC path to the share

2. After that you should be able to backup using the mapped drive letter

3. Your Management Studio Object Explorer should be able to list the above drive

Net use documentation:

http://technet.microsoft.com/en-us/library/bb490717.aspx

The drawback here is that once the SQL Server service is restarted, the mapped drive will no longer be visible because it will be unmapped. If you want to persist the mapped drive information then you need to create a startup procedure for executing the script in Step 1.

The easiest way would be to create a backup device using the UNC path of the remote share that you want to take the database backups on. One thing you need to keep in mind that the SQL Server startup account needs to have full permissions on the remote share.

Retrieving TCP/IP Properties using WMI

Posted on by

This can be done using the following script 

******* START OF SCRIPT ******** 

strComputer = "." 

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\Microsoft\SqlServer\ComputerManagement") 

Set colItems = objWMIService.ExecQuery( _ 

"SELECT * FROM ServerNetworkProtocolProperty where InstanceName='MSSQLSERVER' and ProtocolName = 'Tcp'",,48) 

For Each objItem in colItems 

Wscript.Echo "-----------------------------------" 

Wscript.Echo "ServerNetworkProtocolProperty instance" 

Wscript.Echo "-----------------------------------" 

Wscript.Echo "InstanceName: " & objItem.InstanceName 

Wscript.Echo "IPAddressName: " & objItem.IPAddressName 

Wscript.Echo "PropertyName: " & objItem.PropertyName 

Wscript.Echo "PropertyNumVal: " & objItem.PropertyNumVal 

Wscript.Echo "PropertyStrVal: " & objItem.PropertyStrVal 

Wscript.Echo "PropertyType: " & objItem.PropertyType 

Wscript.Echo "PropertyValType: " & objItem.PropertyValType 

Wscript.Echo "ProtocolName: " & objItem.ProtocolName 

Next

******* END OF SCRIPT ******** 

This uses the class ServerNetworkProtocolProperty. Similarly, you use the above script and modify it to obtain information for the other server network protocols.