September 18, 2007 at 8:09 am
My requirement is fairly straight forward, I hope!
I have a table of server names and 1 query to run against each of them. I want to connect to each server in turn run a query against it, output the results to a table and continue with the next server.
However I can not seem to pass the servername in as a variable in the form of :connect $(servename) it won't parse it.
In this instance it is a straightforward query: sp_helpsrvrolemember. In the future it could be any query at all.
This link is got me some of the way there, but not enough!
http://www.databasejournal.com/features/mssql/article.php/3566401
Hope you can help
Rob.
September 18, 2007 at 12:07 pm
Hi Rob,
Here are some hints and some experiment results:
a batch file accepts parameters. I created a sample sqlcmd_allservers.bat as following:
sqlcmd -S %1 -Q"Select name from syslogins"
PAUSE
here the query is any query you want to run on all servers. %1 is a variable server name that you will pass to this batch file. You may probably do it in a DOS script. If you run this file manually with supplying a server name as a parameter, it works and SQLCMD connects to the server which name is specified as the parameter.
I tried to dynamically get server names from the table on a local server. The name of the database and the table where names of other server are stored is dba.dbo.appservers. I did it as a batch to dynamically create a set of sqlcmd strings:
:setvar MyTable dba.dbo.appservers
set nocount on
SELECT "sqlcmd -Q "":!! C:\sqlcmd_allservers.bat " + Server_name + " "" " from $(MyTable)
lets call this batch sqlcmd_call.bat and run the following from the command line:
C:\>sqlcmd -S MyLocalServer -i sqlcmd_call.bat -o sqlcmd_run.bat
The output file sqlcmd_run.bat that is generated by the above will contain the following set of statements that if run will exeute your original query on all servers that have their names stored in the table on the local server. You will need to remove PAUSE from sqlcmd_allservers.bat and decide how you will dispose of the first line with ------
---------------------------------------------------------------
sqlcmd -Q ":!! C:\sqlcmd_allservers.bat ServerName1 "
sqlcmd -Q ":!! C:\sqlcmd_allservers.bat ServerName2 "
sqlcmd -Q ":!! C:\sqlcmd_allservers.bat Servername3 "
This is just the idea, not a perfect implementation. I am sure it could be done much easier. For example, I myself would use Openrowset function.
Regards,Yelena Varsha
September 19, 2007 at 2:50 am
Many thanks for the post. I'll experiment with this alter. I am hoping for an anwser that doesn't require batch files, I had used a similar method a long time ago with osql and had hoped sqlcmd had more advanced techniques now!
September 19, 2007 at 7:49 am
I've attached code to show you how I do this.
This creates a list of 3 servers. DB1 DB2 and DB3. Cycles through each server and return the status of all jobs to tJobReport table.
steps to set up:
Create a SQL User account on each server. (same username/password)
create the tJobreport table
Create the stored procedures
execute the sp: exec CRS_ForEachServer spJobReport
Hopefully I haven't missed anything.
/****** Object: StoredProcedure [dbo].[CRS_ForEachServer] Script Date: 09/19/2007 09:40:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[CRS_ForEachServer] @ProcedureName varchar(100)
as
set NOCOUNT on
DECLARE @ServerName varchar(150)
Create table #ServerList (servername varchar(20))
insert into #ServerList (servername) values ('DB1')
insert into #ServerList (servername) values ('DB2')
insert into #ServerList (servername) values ('DB3')
DECLARE Server_cursor CURSOR FOR
SELECT servername from #ServerList
OPEN Server_cursor
FETCH NEXT FROM Server_cursor INTO @ServerName
WHILE @@FETCH_STATUS = 0
BEGIN
--Print @Servername
exec @ProcedureName @servername
FETCH NEXT FROM Server_cursor INTO @ServerName
END
CLOSE Server_cursor
DEALLOCATE Server_cursor
Drop Table #ServerList
create function [dbo].[fn_ConnectOPENDataSource] ( @server varchar(150))
returns varchar(150)
as
begin
declare @servername varchar(150)
select @servername = 'OPENDATASOURCE(''SQLOLEDB'',''Data Source='+@server+';User ID=SQLLinkedServer;Password=whatever'')'
return @servername
end
GO
/****** Object: StoredProcedure [dbo].[spJobReport] Script Date: 09/19/2007 09:42:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spJobReport] @ServerName varchar(150)
AS
SET NOCOUNT ON
/*
declare @servername varchar(150)
set @servername = 'DB1'
--print @servername
*/
declare @sqlcmd nvarchar(1000)
delete from tJobReport where server = @ServerName
set @servername = dbo.fn_ConnectOPENDataSource(@servername)
--print @servername
--if ltrim(substring(@@version,22,5)) = '2000'
DECLARE @PreviousDate datetime
DECLARE @Year VARCHAR(4)
DECLARE @Month VARCHAR(2)
DECLARE @MonthPre VARCHAR(2)
DECLARE @Day VARCHAR(2)
DECLARE @DayPre VARCHAR(2)
DECLARE @FinalDate INT
-- Initialize Variables
SET @PreviousDate = DATEADD(dd, -7, GETDATE()) -- Last 7 days
SET @Year = DATEPART(yyyy, @PreviousDate)
SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate))
SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)),2)
SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate))
SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)),2)
SET @Day = DATEPART(dd, @PreviousDate)
SET @FinalDate = CAST(@Year + @Month + @Day AS INT)
Set @sqlcmd = 'INSERT INTO tJobReport (server, jobname, status, rundate, runtime, runduration)
SELECT h.server, j.[name],
CASE h.run_status
WHEN 0 THEN ''Failed''
WHEN 1 THEN ''Succeeded''
WHEN 2 THEN ''Retry''
WHEN 3 THEN ''Canceled''
ELSE ''Unknown''
END,
dbo.ConvertToText (h.run_date,''date'') ,
dbo.ConvertToText (h.run_time, ''time''),
dbo.ConvertToText (h.run_duration,''time'')
FROM ' + @servername + '.msdb.dbo.sysjobhistory h
INNER JOIN ' + @servername + '.msdb.dbo.sysjobs j
ON h.job_id = j.job_id
INNER JOIN ' + @servername + '.msdb.dbo.sysjobsteps s
ON j.job_id = s.job_id
WHERE h.run_status = 0 -- Failure
AND h.run_date > ' + cast(@FinalDate as varchar(10)) + '
AND h.step_id = 0'
--print @sqlcmd
exec sp_executesql @sqlcmd
GO
/****** Object: Table [dbo].[tJobReport] Script Date: 09/19/2007 09:43:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tJobReport](
[lngID] [int] IDENTITY(1,1) NOT NULL,
[server] [varchar](20) NULL,
[jobname] [varchar](100) NULL,
[status] [varchar](10) NULL,
[rundate] [varchar](10) NULL,
[runtime] [char](8) NULL,
[runduration] [char](8) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
September 20, 2007 at 9:10 am
Janet,
Thanks for posting your code. Good job and well commented!
Regards,Yelena Varsha
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply