SQL JOB step to execute a stored proc

  • 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!

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • The question stands: why not a linked server?

    -- Gianluca Sartori

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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