How do I pass a Value to a SQL 2005 Job?

Question

Post reply

How do I pass a Value to a SQL 2005 Job?

Viewing 8 posts - 31 through 37 (of 37 total)

You must be logged in to reply to this topic. Login to reply

Jason Wisdom-380639 SSCrazy Points: 2083 More actions · Answer 1

Yes, I got occupied with something else but I will review it soon.

Thx much.

JASONLMCLEAN SSC Rookie Points: 47 More actions · Answer 2

I use to have to do this when working with PeopleSoft SQR. We would dynamically create the .bat file. For example your file code could look like this:

line 1 ftp xxxxxx login password xxxl;kjdf;lk

$date

line 2 more code

Basically I would concatenate line1 with the date and then concatenate line 2 with (line1 & date). save this file off and then execute it. You can use the script function to accomplish this in SSIS.

Jason

Cowboy DBA SSCrazy Points: 2135 More actions · Answer 3

Try this as a T-SQL task in your job

Declare @task nvarchar (100)

Declare @date nvarchar (100)

Set @date = getdate()

Set @task = 'Exec master.dbo.xp_cmdshell ' + char (39) + 'D:\scripts\step1.bat ' + @date + char (39)

Exec sp_executesql @task

Obviously you can set @date to whatever you want.

Rick Carisse SSC Enthusiast Points: 140 More actions · Answer 4

You could also just call the other batch files from a main one something like this. Don't understand why you would do it from within Sql Server though.

Hope this is useful for you.

EXEC master..xp_cmdshell 'main.cmd 2008-01-01'

***contents of main.cmd below

@ECHO OFF

CLS

if %1 EQU 0 goto InpError

CALL Batch1.cmd %1

CALL Batch2.cmd %1

CALL Batch3.cmd %1

CALL Batch4.cmd %1

goto end

:InpError

@ECHO You must enter a date (yyyy-mm-dd) as a parameter to this command file.

:end

Rick

Jason Wisdom-380639 SSCrazy Points: 2083 More actions · Answer 5

Rick Carisse (9/5/2008)
Hope this is useful for you.
EXEC master..xp_cmdshell 'main.cmd 2008-01-01'
:end

Can not use xp_cmdshell.

However, you did give me an idea......

Jason Wisdom-380639 SSCrazy Points: 2083 More actions · Answer 6

Can I have a .bat file call 4 lines?

C> runme.bat 20080101

(runme bat: )

runme1.bat %1

runme2.bat %1

runme3.bat %1

runme4.bat %1

Would this work?

Adam Bean One Orange Chip Points: 26631 More actions · Answer 7

Adam Bean

One Orange Chip

Points: 26631

September 5, 2008 at 2:32 pm

#868041

That VBScript not work for you?

Rick Carisse SSC Enthusiast Points: 140 More actions · Answer 8

Yes, but you have to use call to run the other batch files.

Like this:

C> runme.bat 20080101

(runme bat: )

call runme1.bat %1

call runme2.bat %1

call runme3.bat %1

call runme4.bat %1

Rick