October 5, 2005 at 4:20 pm
I posted the following post in DTS forum, but got no replies so posting here, in case anyone can help out from this forum! Thanks.
Hello,
Following is the goal I am trying to achieve:
Table: tSQLServers
Fields: ServerName, ServerTypeID, IsActive
DTS Package: MyDTS with Conn1 and Conn2 and a SQL Task that executes a SQL Job. Conn1 will be static connection querying a table to get SQL SERVER names out and assign each to Conn2 so long WHERE conditions match until the table is empty.
SQL using Conn1:
SELECT ServerName
FROM tSQLServers
WHERE ServerTypeID = 2 AND IsActive = 1
Assign the ServerName to Conn2 and run the job in SQL Task in each server coming out of the above SQL SELECT statement.
I know this can be done using a combination of ActiveX Script and Global Variables or Dynamic Properties and Global Variables, but not sure of the exact syntax and logical order. Has anyone done this before? Can you suggest a simple way of getting Server names out of a table and put it into an array or (want to avoid use of cursors) something similar if necessary then iterate through the resultset and assign each server name from the resultset to a SQL Server connection object (Conn2 in my case) then run SQL Task for each new Conn2 server name until resultset is EOF? Basically, the DESTINATION connection should dynamically be set to a SQL Server from the SELECT query above, which returns more than one Server Name in the resultset.
I am having trouble finding the exact logic so if you can help or direct, that'd be great.
JN
October 5, 2005 at 4:35 pm
The are probably a half dozen different ways to do this, but here's 1.
This article will help: http://www.databasejournal.com/features/mssql/article.php/1459181
You create 2 packages and 1 stored proc. The outer package queries your [tSQLServers] table. The stored proc uses the code in the above article to execute a DTS package. You use the results of the query in your outer package to call the stored proc repeatedly, passing the connection server name in as a parameter. The inner package takes the passed DTS global variable containing the passed-in server name and sets it's connection property via an initial ActiveX script task.
It ain't pretty ...
October 6, 2005 at 5:06 am
This kind of problems may be easier when SQL Server 2005 arrives. In sqlcmd (the 'replacement' for isql/osql) command utility, you can connect to more than one server within the same batch.
/Kenneth
October 6, 2005 at 7:22 am
I understood all ther parts of your post more or less, but I am not clear as to how I use the 'results of my outer package...' Say I have a SQL Task with following SQL:
SELECT ServerName
FROM tSQLServers
WHERE ServerTypeID = 2 AND IsActive = 1
Then what?? I can even fire the execute for the OLE SPROC for each server from my resultset, but how to build a resultset is where I am stuck! If I use a GV or OUTPUT or any kind of PARAM it only stores one value from the above SELECT unlike fetching the resultset in a cursor or array like storage space! Could you please clarify how I get the whole list of servers out of my resultset and then pass each one to the OLE SPROC in that artical and execute the inner package with each server name from the resultset until the list is empty?
Thanks.
JN
October 6, 2005 at 11:10 am
I found a script that I manipulated to suite my needs, but not sure if it is what I was looking for and if it's valid so I'd appreicate if you can comment on the logic and see if it serves the purpose listed above in this thread?? Thanks!!
'************************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Dim ADOcon
Dim ADOrst
Dim strSQL1
Dim strSQL2
Dim DestCon
Set ADOcon = CreateObject("ADODB.Connection")
Set ADOrst = CreateObject("ADODB.Recordset")
ADOcon.Provider = "sqloledb"
ADOcon.Properties("Data Source").Value = "(MySQL1\I1)"
ADOcon.Properties("Initial Catalog").Value ="MyMaint"
ADOcon.Properties("User ID").Value ="Domain\User"
ADOcon.Properties("Password").Value ="MyPWD"
ADOcon.connectiontimeout=0
ADOcon.commandtimeout=0
ADOcon.Open strConnection
strSQL1 = " SELECT ServerName" &_
" FROM tSQLServers S " &_
" WHERE S.ServerTypeID = 2 AND S.IsActive = 1 "
ADOrst.Open strSQL1, ADOcon
Do while not ADOrst.EOF
DTSGlobalVariables("gv_SQLServer").Value = TRIM(ADOrst.Fields("ServerName"))
Set DestCon = CreateObject("ADODB.Connection")
DestCon.Provider = "sqloledb"
DestCon.Properties("Data Source").Value = DTSGlobalVariables("gv_SQLServer").Value
DestCon.Properties("Initial Catalog").Value ="MSDB"
DestCon.Properties("User ID").Value ="Domain\User"
DestCon.Properties("Password").Value ="MyPWD"
DestCon.connectiontimeout=0
DestCon.commandtimeout=0
DestCon.Open strConnection
strSQL2 = " msdb..sp_start_job 'Check Mail' "
DestCon.Execute strSQL2
ADOrst.MoveNext
Loop
ADOrst.Close
ADOcon.Close
Main = DTSTaskExecResult_Success
End Function
October 10, 2005 at 11:07 am
If anyone interested in knowing how I resolved and implemented the question in this thread, here it is:
Table: tSQLServers
Lists SQL Server names and other related fields.
SPROC: usp_get_SQLServers
Queries tSQLServers for ServerName (row by row processing, but no cursor use) and runs EXEC dbo.spExec_DTS_CheckMailOutage passing necessary variables. The EXEC spExec_DTS_CheckMailOutage is issued as many times as there are Servers in tSQLServers table matching WHERE clause.
CREATE PROCEDURE usp_get_SQLServers
/*
*********************************************
Name: dbo.usp_get_SQLServers
Purpose: Queries tSQLServers for ServerName (row by row processing) and runs EXEC dbo.spExec_DTS_CheckMailOutage
passing necessary variables. The EXEC spExec_DTS_CheckMailOutage is issued as many times as there are
Servers in tSQLServers table matching WHERE clause.
Author: JN
Created: 10/7/2005
Tested: 10/7/2005, 4PM on Stage SQL Servers
*********************************************
-*-*-*- REVISIONS -*-*-*-
*********************************************
*/
AS
SET NOCOUNT OFF
SET SET ANSI_WARNINGS OFF
DECLARE @SQLServer_Count INT,
@sqlserver VARCHAR (30)
SET @SQLServer_Count = 0
SELECT ServerName INTO #tmpSQLServer
FROM dbo.tSQLServers S
WHERE S.ServerTypeID = 2 AND S.IsActive = 1
WHILE @SQLServer_Count < (SELECT COUNT(*)
FROM dbo.tSQLServers S
WHERE S.ServerTypeID = 2 AND S.IsActive = 1
)
BEGIN
SELECT TOP 1 @sqlserver = ServerName From #tmpSQLServer
-- EXEC spExec_DTS_CheckMailOutage 'SQL Server where DTS is', 'DTS Package Name', SQL Server Security Level, 'DTS Global Variable Name', Destination SQL Server to run the job in.
EXEC dbo.spExec_DTS_CheckMailOutage 'SQL8DS1\I1', 'Check_Mail_Outage_TEST1', 1, 'gvSQLServer', @sqlserver
DELETE FROM #tmpSQLServer WHERE ServerName = @sqlserver
SET @sqlserver = NULL
SET @SQLServer_Count = @SQLServer_Count + 1
END
DROP TABLE #tmpSQLServer
GO
SPROC1: spExec_DTS_CheckMailOutage
Receives necessary variable values from dbo.usp_get_SQLServers SPROC and Execute DTS "Check_Mail_Outage_TEST1" via sp_OAxxxx specifying one DTS Global Variable. Original content courtesy of Database Journal site (referred by PW) - http://www.databasejournal.com/features/mssql/article.php/10894_1459181_3. Modified to suite my needs.
DTS Package: Check_Mail_Outage_TEST1 (residing on MySQLServer\I1)
DTS executed from within spExec_DTS_CheckMailOutage SPROC, which feeds a value to gvSQLServer global variable. This package executes JOB msdb..sp_start_job 'Check Mail Outage' in Destination SQL Server set by gvSQLServer to verify if SQL Agent and Mail Services are working properly as expected. The SQL in the JOB sends two e-mails to SQL@MYDomain.com mail group results of the mail verification job stating success or failure.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply