Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rubs_65
Posting Yak Master
144 Posts |
 Posted - 2005-09-22 : 16:59:55
|
| Hi,We have a maintenance job that consists of backup command, compress and ftp command as 3 different steps. During backup we are dynamically generating a backup file name by appending with current time stamp like:name_[DATE]_[TIME] but now i need to use the same name in compress and ftp commands. Is it possible that i can create this name and store it in the variable as a 1st step and then use the variable in all the 3 steps ORwhat is the other option that can be used?Thanks--Harvinder |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-09-23 : 04:36:43
|
yes i think you can, coz they'll have different extensions and/or locations? so essentially you won't have identical filenames to mess up with your job  --------------------keeping it simple... |
 |
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2005-09-25 : 21:12:33
|
| Hi, I don't think you can, as i don't think global var works in jobs as well, unless you store that name in a table (temp table but not #temp), if you come up with another way, pls let me know as well. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-09-26 : 01:36:10
|
| not as a global variable, but as an input to the next steplet's say you have step1 that will create the backupstep 2 that will compress, you can query the directory for the backup namesteps...or if you are encapsulating all of the backup commands for different databases in one job, i suggest you use a table to store the data you'll need like:databasename, backupname, directory, dateofbackup, etc...this way you can query the table and retrieve the infoHTH--------------------keeping it simple... |
|
|
|
rubs_65
Posting Yak Master
144 Posts |
Posted - 2005-09-26 : 10:59:10
|
| Can you explain with example how can we do this "but as an input to the next step"?Also if i use the table approach,Backup job will be running as t-sql so it is easy to query the table and get the filename but compreess and ftp steps will be running as cmdexec, so either i may have to use osql or suggest me with example what is the best way to access table while running cmdexec commands.Thanks--Harvinder |
|
|
|
Sharky
Starting Member
23 Posts |
Posted - 2005-09-26 : 11:06:24
|
| what about creating a small table in the first step that stores the filename for the current run, subsequent steps just query that table for the filename? |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-09-28 : 02:59:53
|
you need to use dynamic sql and run that with xp_cmdshell (tsql version of cmdexec)so instead of having a cmdexec job step, you'll use a tsql job step:create table #table(field1 nvarchar(4000))insert into #table(field1)exec master..xp_cmdshell 'dir *'select * from #tabledrop table #jen table approachmuch easier for you because you don't need to query the filename on the location, you just need to query the table and concatenate the filename to your command for compress and ftp...declare @var nvarchar(4000),@file nvarchar(100)set @var='whatever your command plus the filename you've queried'exec xp_cmdshell @var --is valid where @var is nvarchar datatype HTH--------------------keeping it simple... |
|
|
|
|
|
|
|
|
variable question in sql server job