Automating SQL Tasks with VBScript (SQL DMO)

Needless to mention, we come across instances when we want to automate certain SQL Tasks. We might be in need of the automation because we might want to accomplish some repeated work, or there is a requirement as part of the project.

Below are some of the tasks that I had to automate while working on a project. I am more onto SQL Server, so VBScripting is not my primary skill. I had to learn (ofcourse from the Internet - so you might find some examples here, elsewhere as well) and implement. I learnt that VBScript is a very powerful means of doing this. I hope once you have gone through this, you will agree with me.


' ------------------------------------------------------------------------------------------------------------
'1. Connect to SQL Server
' ------------------------------------------------------------------------------------------------------------


sServer = "."
Set oSQLServer = CreateObject("SQLDMO.SQLServer")
oSQLServer.LoginSecure = True
oSQLServer.Connect sServer

'/*********************************************************************************
' ------------------------------------------------------------------------------------------------------------
'2. Back up Database

' ------------------------------------------------------------------------------------------------------------


sServer = "."
sDatabase = "Northwind"
rootDir = "D:\Backup\"
sDumpFile = "Northwind.bak"

Set oSQLServer = CreateObject("SQLDMO.SQLServer")

oSQLServer.LoginSecure = True
oSQLServer.Connect sServer


Set oBackup = CreateObject("SQLDMO.BackUp")
oBackup.Database = sDatabase
oBackup.Files = rootDir & sDumpFile
oBackUp.BackupSetDescription = "Backup - " & sDatabase
oBackup.Initialize = "TRUE"
oBackup.SQLBackup oSQLServer


'/*********************************************************************************
' ------------------------------------------------------------------------------------------------------------
'3. Restore Database
' ------------------------------------------------------------------------------------------------------------


sServer = "."
sDatabase = "Northwind"
rootDir = "D:\Backup"
DataFileLoc = "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL"
LogFileLoc = "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL"


Set oSQLServer = CreateObject("SQLDMO.SQLServer")
oSQLServer.LoginSecure = True
oSQLServer.Connect sServer
Set oRestore = CreateObject("SQLDMO.Restore")
oRestore.Action = SQLDMORestore_Database
oRestore.Database = sDatabase
oRestore.ReplaceDatabase = True

oRestore.Files = rootDir & "\Northwind.bak"
oRestore.RelocateFiles = "[Northwind],[" & DataFileLoc &"\Data\Northwind.mdf], [Northwind_log],[" &LogFileLoc &"\Data\Northwind_1.ldf]"

oRestore.FileNumber = 1


oRestore.SQLRestore oSQLServer


'/********************************************************************************
' ------------------------------------------------------------------------------------------------------------
'4. Run a .sql file
' ------------------------------------------------------------------------------------------------------------

sServer = "."
sScriptPath = "D:\SQLScripts"
Set WshShell = WScript.CreateObject("WScript.Shell")
WshShell.Run "sqlcmd -S " & sServer & " -d Northwind -E -i """& "" & sScriptPath&"\CreateTable.sql"""

'/********************************************************************************
' ------------------------------------------------------------------------------------------------------------
'5. Set single user mode and Detach DB
' ------------------------------------------------------------------------------------------------------------

sServer = "."
sDatabase = "Northwind"
Set WshShell = WScript.CreateObject("WScript.Shell")

sAltDBCmd = "sqlcmd -S " & sServer & " -d master -E -Q""ALTER DATABASE " & sDatabase & " SET SINGLE_USER WITH ROLLBACK IMMEDIATE"""


sCmdDet1 = "sqlcmd -S " & sServer & " -E -Q""EXEC sp_detach_db '" & sDatabase & "','TRUE' "" "


ObjAltDBExcode1 = WshShell.Run(sAltDBCmd,5,True)
ObjDetachExcode1 = WshShell.Run(sCmdDet1,5,True)


'/********************************************************************************
' ------------------------------------------------------------------------------------------------------------
'6. Attach a Database
' ------------------------------------------------------------------------------------------------------------


sServer = "."
sDatabase = "Northwind"
DataFileLoc = "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\"
LogFileLoc = "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\"

Set WshShell = WScript.CreateObject("WScript.Shell")

sCmd1 = "sqlcmd -S " & sServer & " -E -Q""EXEC sp_attach_db @dbname= '" & sDatabase & "', @filename1 = '"&DataFileLoc&"Northwind.mdf',@filename2 = '"&LogFileLoc&"Northwind_1.LDF'"" "


ObjAttachExcode1 = WshShell.Run(sCmd1,5,True)


'/*******************************************************************************--
'-----------------------------------------------------------------------------------------------------------
'7. Script out the SQL Agent Jobs
' ------------------------------------------------------------------------------------------------------------

sServer = "."
sbackupLoc = "D:\backup\"
Set oSQLServer = CreateObject("SQLDMO.SQLServer")
Set fso = CreateObject("scripting.filesystemobject")

oSQLServer.LoginSecure = True
oSQLServer.Connect sServer


Set objDB = oSQLServer.Databases
Set objJob = oSQLServer.JobServer

If NOT(fso.FolderExists(sbackupLoc & "\Jobs\")) Then
                    fso.CreateFolder(sbackupLoc & "\Jobs\")
End If


For each job in objJob.Jobs
                  sFileName = sbackupLoc & "\" & "\Jobs\" & Replace(job.Name, "\", "-") & ".sql"
                  job.Script 327, sfileName
Next




'/********************************************************************************
' ------------------------------------------------------------------------------------------------------------
'8. Change the Collation of the SQL Server Instance
' ------------------------------------------------------------------------------------------------------------

sServer = "."
Set oSQLServer2 = CreateObject("SQLDMO.SQLServer2")
Set WshShell = WScript.CreateObject("WScript.Shell")

oSQLServer2.LoginSecure = True
oSQLServer2.Connect sServer


If oSQLServer2.Collation = "SQL_Latin1_General_CP1_CI_AS" Then
                 MsgBox ("No action required; this is the required collation")
Else
                MsgBox ("Changing the collation to SQL_Latin1_General_CP1_CI_AS")

                 sCommand = ""& Chr(34) & "C:\Program Files\MSSQLSOURCE\setup.exe" & Chr(34) & "/qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS ASCOLLATION=SQL_Latin1_General_CP1_CI_AS"


On Error Resume Next
ColSetExcode = WshShell.Run(sCommand,5,True) 'Executing the command

If ColSetExcode <> 0 Then
         MsgBox ("Could not set the new collation " & ColSetExcode)
Else
       MsgBox ("Collation set successfully to SQL_Latin1_General_CP1_CI_AS")
End If


End If


'/********************************************************************************
' ------------------------------------------------------------------------------------------------------------
'9. Function to check the Successful Installation of SQL Server 2005
' ------------------------------------------------------------------------------------------------------------


CheckSQL2005Install()

Function CheckSQL2005Install()

On Error Resume Next
Set WshShell = WScript.CreateObject("WScript.Shell")

'***********************************************
' Check for the Registry key of the Data Folder
'***********************************************

WshShell.Popup "Checking the Registry key",5,"SQLUpgrade",64

DataRoot = WshShell.RegRead ("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup\SQLDataRoot")

If Err.Number <> 0 Then
           Installation = 1
Else
            Installation = 0
End If


On Error Resume Next

Set fsoFileSystem = CreateObject("scripting.filesystemobject")

If Err.Number <> 0 Then
                Wscript.Echo "Cannot create FileSystem Object... Hence Quitting"
                Wscript.Quit()
End If


Set ofolder = fsoFileSystem.GetFolder(DataRoot)

If Err.Number <> 0 Then
                 Wscript.Echo "Invalid SQL Server folder location Specified.. Quitting.."
                  Installation = 1
End If


'*****************************************************************
' Check for the Data and Transaction log files in the data folder
'*****************************************************************

Set ofiles = ofolder.Files
For Each objFile In ofiles


      sType = objFile.Type


If ( sType = "SQL Server Database Primary Data File" Or "SQL Server Database Transaction Log File") Then
       Installation = 0
Else
       Installation = 1
End If
Next



'***********************************************
' Check whether or not the services are running
'***********************************************


WshShell.Popup "Checking SQL Server Services..",5,"SQLUpgrade",64

Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & sServer & "\root\cimv2")

Set colRunningServices = objWMIService.ExecQuery _
("Select * from Win32_Service where Name = 'MSSQLSERVER'")


For Each objService in colRunningServices
If objService.State = "Running" Then
         Installation = 0

Set colRunningServices2 = objWMIService.ExecQuery _
("Select * from Win32_Service where Name = 'SQLSERVERAGENT'")
For Each objService2 in colRunningServices2
If objService2.State = "Running" Then
      Installation = 0
Else


WshShell.Popup "SQL Server Agent Service not is Running, Starting the Service...",6,"SQLUpgrade",64
sCommand2 = objService2.StartService()
ObjExcode = WshShell.Run(sCommand2,5,WaitOnReturn) 'Executing the command


If ObjExcode <> 0 Then
         Wscript.Echo "Could not start SQL Server Agent Service" & Err.Description
Else
         WshShell.Popup "SQL Server Agent Service started successfully",4,"SQLUpgrade",64
End If
End If


Next


Else


WshShell.Popup "SQL Server Service not is Running, Starting the Service...",6,"SQLUpgrade",64
sCommand = objService.StartService()
ObjExcode = WshShell.Run(sCommand,5,WaitOnReturn) 'Executing the command

If ObjExcode <> 0 Then
        Wscript.Echo "Could not start SQL Server Service" & Err.Description
Else
        WshShell.Popup "SQL Server Service started sucessfully",4,"SQLUpgrade",64
End If


Set colRunningServices2 = objWMIService.ExecQuery _
("Select * from Win32_Service where Name = 'SQLSERVERAGENT'")


For Each objService2 in colRunningServices2
If objService2.State = "Running" Then
            Installation = 0
Else


WshShell.Popup "SQL Server Agent Service not is Running, Starting the Service...",6,"SQLUpgrade",64
sCommand2 = objService2.StartService()
ObjExcode = WshShell.Run(sCommand2,5,WaitOnReturn) 'Executing the command

If ObjExcode <> 0 Then
          Wscript.Echo "Could not start SQL Server Agent Service" & Err.Description
Else
         WshShell.Popup "SQL Server Agent Service started successfully",4,"SQLUpgrade",64
End If
End If
Next
End If
Next

CheckSQL2005Install = Installation

If CheckSQL2005Install = 0 Then
          MsgBox("SQL Server 2005 is installed Successfully")
Else
          MsgBox("SQL Server 2005 not installed properly, or could not check")
End If






End Function



'/********************************************************************************

' ------------------------------------------------------------------------------------------------------------
'10. Import SSIS packages into SSIS package Store - into Maintenance Plan - since the below is a maintenance plan
' ------------------------------------------------------------------------------------------------------------


sPackagePath = "D:\SQL\scripts" 'make sure this path has the package file ReIndexPlan.dtsx
Set WshShell = WScript.CreateObject("WScript.Shell")

WshShell.Run "dtutil /FILE " & sPackagePath& "\ReIndexPlan.dtsx /COPY DTS;""MSDB\Maintenance Plans\ReIndexPlan"""

Maintenance Plan SSIS Packages cause problems on the exported server

Most often - I would say always - we develop the solutions on the development server and they are then implemented on Production. No where I have seen this case that the solution is directly developed on the Production - might happen somewhere, but that's not the matter of discussion anyway :-)...

When it comes to Maintenance Plans on SQL Server 2005 (or 2008 - I am not yet sure), the plan is created as a SSIS Package. So, it needs to be made portable by adding package configurations to it. Now, once the SSIS package is ready with confgurations to be deployed on the Production, we "import" it there. Here, I have seen a couple of issues which I will elaborate.

      1. One, when the package is imported, it is available in the Managment --> Maintenance Plans node of the Object Explorer.

Right click on the Maintenance Plan and select "Edit". It opens up in the designer. On the maintenance task, right click, and edit.



Ideally, we should be able to edit it. Instead it sometimes throws this error -

To resole this, open the package in the SQL Server Business Intelligence Development Studio, and in the properties of the maintenance task (like the Backup or the ReIndex as in the above screenshot), check the value of "DestinationCreationType". make sure that it is set to "Auto" and NOT "Manual". Then you should not anymore see this error and should be able to modify the package as required.

      2. Another issue is that, when the package is imported on the target server, it should ideally create a job automatically. But it does not. So, we need to create the job manually (lets script this job out from the server where we developed the maintenance plan). Let's spare that, and address even more complexity of the issue. When we run this job, (or even run the SSIS package alone), though it creates a back up of the database (if the maintenance plan is about database backup), but fails to finish successfully. It gives this error, or a similar error:

Started: 8:23:25 PM

Error: 2008-12-16 20:23:26.85
Code: 0xC002F210
Source: {7C327821-52CE-4177-B79C-6CFD236FF95A} Execute SQL Task
Description: Executing the query "DECLARE @Guid UNIQUEIDENTIFIER



EXECUTE msdb..sp_maintplan_open_logentry '{74485280-D50F-4C7A-BCCA-1C58DFD5C7FB}', '{D61892B1-D122-4751-8DC4-33F739195545}',NULL, @Guid OUTPUT


Select CONVERT(nvarchar(38),@Guid) AS RunId" failed with the following error: "The INSERT statement conflicted with the FOREIGN KEY constraint "FK_sysmaintplan_log_subplan_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'subplan_id'.
The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.


End Error
Error: 2008-12-16 20:23:29.88
Code: 0xC0024104
Source: {81E5BDFE-7937-4766-8A9F-A04D2E80BD28}
Description: The Execute method on the task returned error code 0x80131501 (An exception occurred while executing a Transact-SQL statement or batch.). The Execute method must succeed, and indicate the result using an "out" parameter.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 8:23:25 PM
Finished: 8:23:30 PM
Elapsed: 4.094 seconds 

So, basically, it is now successful in doing what it is primarily supposed to (creating a backup of the database - or whatever the maintenance plan was created for), but fails on this step.

In many cases, the error is because of this. When we had imported the maintenance plan (SSIS package) into the destination server, somehow, this entry was not added to the table msdb..sysmaintplan_subplans. I am not sure why this happens. Also, when the package was imported it did not create a job. So, this job id information was also not added to this table msdb..sysmaintplan_subplans. Basically to say that the new package information did not reach this table.

The SPROC sp_maintplan_open_logentry adds an execution log into the table msdb..sysmaintplan_log, which has a FOREIGN KEY relation with the table msdb..sysmaintplan_subplans. Now, since the plan information itself is not available in this table, the log insertion will also fail.

We should add an entry manually into msdb..sysmaintplan_subplans for this package with the job id that we created manually. And the job would start to finish successfully.

  So, inconsistencies seen:

• A job does not get created for the maintenance plan when it is imported.
• A new entry into the table msdb..sysmaintplan_subplans is not added when the package is imported. 

Thanks for reading and I hope it help you solve your problem - well, if you faced a similar one... :-)

~ Manoj Deshpande

SQL Agent job calling SSIS package never completes!

While I was working on SSIS Packages for a project, and was trying to schedule it with a SQL Agent job, I came across a situation.

I was using two SSIS Packages. I configured each package to be called from the SQL Agent job as a separate step, as they were to do different tasks – one dependent on the other. The first package was called as part of the second step in the job, and the second was called in the 5th step. I noticed that the job would get past the second step, third and the fourth. But on the fifth, it kept running – indefinitely.

More strange was the fact that the SSIS Package when run from the SQL Server Business Intelligence Development Studio, was running successfully (and finished, which is of more importance). But the issue was only when the package was scheduled in a SQL Agent Job.

It didn’t end there. I almost did a research on the net and wished to try something I found. It said, restart the server! The great restart! I did that and phew! It completed when I run it. I did not stop there. I tried to run once more and as expected it never completed. I restarted the SQL Server Service – it also restarted the SQL Agent Service. Then again, when I run the job, it completed. I tried once more, it never completed!! At this point I was frustrated. After a long research, I tried putting a hosts file entry. Details below.

The SSIS Package – in turn the Server was trying to reach the website http://crl.microsoft.com to call the CryptoAPI function to verify the certificates that are assigned to the SQL Server assembly files. The function CryptoAPI would check Certificate Revocation List on that site. This surely requires that the Server in on the internet. As it usually happens, the servers do not have open connection to the internet, and hence it was never completing, probably waiting to get connected.
As a solution, I put this website to point to 127.0.0.1 (self) in the hosts file – and guess what, the job started to complete successfully thereafter. Here is a screenshot of the hosts file on the server –

It is important to note that the server I was working on is a 32-bit server.

I sincerely hope this post saves hours of time for someone!

~ Manoj Deshpande.