Category Archives: SQL How To

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.

How to connect to SQL Server 2005 Embedded Edition

Posted on by

From SQL Server Management Studio, you cannot connect to a SQL Server Embedded Edition instance by just providing the server name. You need to put the following in the server name input:

\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query

The files for an embedded edition instance should be at:
C:\WINDOWS\SYSMSI\SSEE\MSSQL.2005\MSSQL\

I have seen multiple people baffled by this question. Hence, I thought I would put up a post about it.