October 5, 2011 at 7:55 am
Hi All,
I have to create a SQL Job which has 2 steps.
Steps 1 is SSIS packages ,Step 2 should execute a sql job.
The problem i am having to create step 2 is that the stored proc is in a different server and the job runs on a different server.
Can some one guide me through this without using linked server.
Thanks!
October 5, 2011 at 8:37 am
The problem i am having to create step 2 is that the stored proc is in a different server and the job runs on a different server. Can some one guide me through this without using linked server.
If a linked server is not an option (clearly the easiest way), IMHO all you can do is execute a *.bat file to run the procedure/start the job or use xp_cmdshell...this means you'll have to enable xp_cmdshell...and write a couple of lines of code to run the procedure. You should have no issues running a start job on another server, depending on how you have everything set up.
Some of this forum's experts may be able to help out with better ways though...
Example:
EXECUTE master..xp_cmdshell 'osql -SServername -ddbname -Q"exec RunMyJobOnAnotherServerProcedure.sql ''Param1',''Param2'',''Param3'',''Etc''" -E'
If you don't mine me asking, what's the reasoning for not using a linked server?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
October 5, 2011 at 8:43 am
sqlcmd is preferred to osql, but I agree - why not use a linked server?
You may also wish to investigate Service Broker, or create another SSIS package that runs the job on the remote server.
John
October 5, 2011 at 9:07 am
Thanks guys for your replies!
I tried using the code jessie gave.... and i get the following output
My code:
EXECUTE master..xp_cmdshell
'osql server1 database1 -Q"exec dbo.storedprocedure" -E'
Output:
Microsoft (R) SQL Server Command Line Tool
Version 10.0.1600.22 NT INTEL X86
Copyright (c) Microsoft Corporation. All rights reserved.
NULL
Note: osql does not support all features of SQL Server 2008.
Use sqlcmd instead. See SQL Server Books Online for details.
NULL
usage: osql [-U login id] [-P password]
[-S server] [-H hostname] [-E trusted connection]
[-d use database name] [-l login timeout] [-t query timeout]
[-h headers] [-s colseparator] [-w columnwidth]
[-a packetsize] [-e echo input] [-I Enable Quoted Identifiers]
[-L list servers] [-c cmdend] [-D ODBC DSN name]
[-q "cmdline query"] [-Q "cmdline query" and exit]
[-n remove numbering] [-m errorlevel]
[-r msgs to stderr] [-V severitylevel]
[-i inputfile] [-o outputfile]
[-p print statistics] [-b On error batch abort]
[-X[1] disable commands [and exit with warning]]
[-O use Old ISQL behavior disables the following]
<EOF> batch processing
Auto console width scaling
Wide messages
default errorlevel is -1 vs 1
[-? show syntax summary]
NULL
October 5, 2011 at 9:14 am
Yes, as John states, "sqlcmd is preferred to osql"
Try that instead (and apologies for giving you the bad code!)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
October 5, 2011 at 9:20 am
I used it but still no result
My code:
EXECUTE master..xp_cmdshell
'sqlcmd server1 database1 -Q"exec dbo.storedprocedure" -E'
Output:
Sqlcmd: 'CLTDVSQLApp1\DVAPPINST1': Unknown Option. Enter '-?' for help.
NULL
October 5, 2011 at 9:26 am
kevin4u06 (10/5/2011)
Enter '-?' for help
There's your clue. Run [font="Courier New"]sqlcmd -? [/font]in order to get the proper syntax.
John
October 7, 2011 at 4:10 am
The question stands: why not a linked server?
-- Gianluca Sartori
October 10, 2011 at 7:04 am
I'm still not getting it....
EXECUTE master..xp_cmdshell 'sqlcmd -Q"exec server1.database1.sample_test"'
error:
HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is
configured to allow remote connections. For more information see SQL Server Books Online..
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.
NULL
October 10, 2011 at 7:30 am
Why have you removed the -E switch? Are you running the command interactively, or is it running in a job step? Is the server you're trying to connect to the same as the one you're running the command on?
John
October 10, 2011 at 9:19 am
i'm not running the command in a job.....just testing it in the ssms...once it works fine then i'll run it in the job
October 10, 2011 at 1:54 pm
Do you have remote connections and xp_cmdshell enabled?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
October 11, 2011 at 1:33 am
One question answered out of three, so I'm still guessing. Here's what I can tell you:
(1) Your SQL Server service account needs to have a login on the remote server and permission to do whatever it is you're trying to do
(2) You need to specify the server name as a parameter to sqlcmd. It'll only stand any chance of working the way you've done it if you have a remote server set up on the server on which you're running sqlcmd. Even then, you need to get your four-part naming convention right
John
October 20, 2011 at 2:16 pm
Hi all,
Sorry for the late reply. I finally got the issue resolved.
this is the solution
SQLCMD -S Server_1 -d Database1 -Q "dbo.StoredProc"
I forgot to use the switches
November 22, 2011 at 10:21 am
Why not a linked server?
Here's my situation and you can tell me what you'd do. I just started at a new company. I'm part of a Windows group associated with a sysadmin login. However, I am not the boss and the boss doesn't want to create linked servers for our 40+ machines. However, the boss does want a list of failed jobs, viewable in SSRS, from each of the servers.
My soloution is to run sqlcmd and bcp through xp_cmdshell to kickout the results of a query from each of the machines. I pass the instance to the cmd from a list we have and just iterate through it. This gives me a single place to view all the jobs without having to place a new job or stored proc on each server.
My only problem is I want to capture the error created by sqlcmd when a connection times out, but the @@error parameter doesn't seem to be doing the job. Otherwise my code works great.
Here's the query that is run in the RetrieveFailedJobs.sql file:
SELECT @@SERVERNAME ServerName, A.name JobName, A.enabled, A.description, A.date_created, A.date_modified,
B.last_run_date, B.last_run_duration, B.last_outcome_message
INTO FailedTemp
FROM msdb.dbo.sysjobs A, msdb.dbo.sysjobservers B
WHERE A.job_id = B.job_id AND B.last_run_outcome = 0
ORDER BY last_run_date DESC
Here's the format file used for the bcp and OpenRowSet operations (use your imagination and see it all pretty and lined up):
10.0
9
1 SQLNCHAR 2 256 "" 1 ServerName SQL_Latin1_General_CP1_CI_AS
2 SQLNCHAR 2 256 "" 2 JobName SQL_Latin1_General_CP1_CI_AS
3 SQLTINYINT 0 1 "" 3 enabled ""
4 SQLNCHAR 2 1024 "" 4 description SQL_Latin1_General_CP1_CI_AS
5 SQLDATETIME 0 8 "" 5 date_created ""
6 SQLDATETIME 0 8 "" 6 date_modified ""
7 SQLINT 0 4 "" 7 last_run_date ""
8 SQLINT 0 4 "" 8 last_run_duration ""
9 SQLNCHAR 2 8000 "" 9 last_outcome_message SQL_Latin1_General_CP1_CI_AS
And here's the code:
USE master
go
DECLARE
@nxtSvrName VARCHAR(50),
@svrName VARCHAR(50),
@path VARCHAR(250),
@cmd VARCHAR(1000),
@loopCount INT
--This is where the related files are stored to make this work
SET @path = '\\myPath\'
--This is our loop counter for iterating through known servers
SELECT @loopCount = COUNT(DISTINCT Instance)
FROM SS_DBA_Dashboard.dbo.[Server Inventory]
--Loop through known servers to gather failed jobs in one place
WHILE @loopCount > 0
BEGIN
--Loop starts with setting the server from the next server in the list
SELECT @nxtSvrName = MAX(Instance)
FROM SS_DBA_Dashboard.dbo.[Server Inventory]
WHERE @nxtSvrName IS NULL OR Instance < @nxtSvrName
--If a connection fails to the server we'll need to change this, but we want to keep the value of @nxtSvrName for proper looping
SET @svrName = @nxtSvrName
--This will build a table called FailedTemp in the master database of the server identified above
SET @cmd = 'sqlcmd -S '+@svrName+' -i '+@path+'RetrieveFailedJobs.SQL -b'
--PRINT @cmd
EXEC xp_cmdshell @cmd
--If a connection fails, create the table locally and reset the @srvName
-->>NOT WORKING
IF @@error <> 0
BEGIN
SELECT @svrName col1,NULL col2,NULL col3,NULL col4,NULL col5,NULL col6,NULL col7,NULL col8,NULL col9
INTO FailedTemp
SET @svrName = 'DefaultSvrName'
END--if
--If this is the first time through the loop, bcp out to FailedJobs.txt, else bcp out to tmp.txt, append and delete
IF @loopCount = (SELECT COUNT(DISTINCT Instance) FROM SS_DBA_Dashboard.dbo.[Server Inventory])
BEGIN
SET @cmd = 'bcp master.dbo.FailedTemp out '+@path+'FailedJobs.txt -f ' +@path +'FailedJobs.FMT -T -S' + @svrName
--PRINT @cmd
EXEC xp_cmdshell @cmd
END --if
ELSE
BEGIN
SET @cmd = 'bcp master.dbo.FailedTemp out '+@path+'tmp.txt -f ' +@path +'FailedJobs.FMT -T -S' + @svrName
--PRINT @cmd
EXEC xp_cmdshell @cmd
SET @cmd = 'type ' + @path + 'tmp.txt >> '+ @path + 'FailedJobs.txt'
--PRINT @cmd
exec master..xp_cmdshell @cmd
SET @cmd = 'del '+ @path + 'tmp.txt'
--PRINT @cmd
exec master..xp_cmdshell @cmd
END--else
--Clean up the failed jobs table
SET @cmd = 'sqlcmd -S '+@svrName+' -i '+@path+'DropFailedTemp.SQL'
--PRINT @cmd
EXEC xp_cmdshell @cmd
--Decrement and do it again
SET @loopCount -=1
END--while
--Read the contents of the file
SET @cmd = 'SELECT * FROM OPENROWSET(BULK ''' + @path + 'FailedJobs.txt'', FORMATFILE = '''+ @path + 'FailedJobs.FMT'') AS X'
--PRINT @cmd
EXEC (@cmd)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply