How to pass parameters into sql jobs?
-
How to pass the parameters of @dbName, @LogicalName, @ToServer to job dynamically using stored procedure?
-
How to pass the parameters of @dbName, @LogicalName, @ToServer to job dynamically using stored procedure?
This belongs in the SQL forum, where I'd advise you to look into how google works. Parameters are specified by adding them to the parameters collection, heaps of info on the web, or in your books.
Christian Graus - Microsoft MVP - C++ "also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
-
This belongs in the SQL forum, where I'd advise you to look into how google works. Parameters are specified by adding them to the parameters collection, heaps of info on the web, or in your books.
Christian Graus - Microsoft MVP - C++ "also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
hello Christian Graus i cant find reply of this question in sql forum, that's why for fast reply i put it here.
-
hello Christian Graus i cant find reply of this question in sql forum, that's why for fast reply i put it here.
And what did you find with google ? What code are you using to call your proc ?
Christian Graus - Microsoft MVP - C++ "also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
-
And what did you find with google ? What code are you using to call your proc ?
Christian Graus - Microsoft MVP - C++ "also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
nothing find from google. that's why i put my question here. i have more expectations from codeproject than google. This is my sql job: USE [master] GO declare @file as varchar(500) declare @dbName as varchar(50) declare @LogicalName as varchar(50) declare @ToServer as varchar(50) exec('ALTER DATABASE ['+@dbName+'] SET SINGLE_USER WITH ROLLBACK IMMEDIATE') exec('RESTORE DATABASE ['+@dbName+'] FROM DISK = N''F:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Backup\'+@file+''' WITH FILE = 1, MOVE N'''+@LogicalName+''' TO N''F:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\'+@dbName+'.mdf'', MOVE N'''+@LogicalName+'_log'' TO N''F:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\'+@dbName+'_log.ldf'', NOUNLOAD, REPLACE, STATS = 10') GO I want to pass above four parameters like @file,@dbName,@LogicalName,@ToServer from my stored procedure to above job. so, what i have to do for this? this is my stored procedure: ALTER PROCEDURE runjob AS Declare @backupfile varchar(500),@databasename varchar(50), @logicaldatabasename varchar(50), @toservername varchar(50) set @backupfile = 'test1.bak'; set @databasename = 'test1'; set @logicaldatabasename = 'test1'; set @toservername = 'Live'; BEGIN --exec sp_who2 EXEC msdb.dbo.sp_add_job @job_name = 'k', @file = @backupfile, @dbName = @databasename, @LogicalName = @logicaldatabasename, @ToServer = @toservername EXEC msdb.dbo.sp_start_job k END so, what i have to do for this? and i m receiving an error @file is not a parameter for procedure sp_add_job.
-
nothing find from google. that's why i put my question here. i have more expectations from codeproject than google. This is my sql job: USE [master] GO declare @file as varchar(500) declare @dbName as varchar(50) declare @LogicalName as varchar(50) declare @ToServer as varchar(50) exec('ALTER DATABASE ['+@dbName+'] SET SINGLE_USER WITH ROLLBACK IMMEDIATE') exec('RESTORE DATABASE ['+@dbName+'] FROM DISK = N''F:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Backup\'+@file+''' WITH FILE = 1, MOVE N'''+@LogicalName+''' TO N''F:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\'+@dbName+'.mdf'', MOVE N'''+@LogicalName+'_log'' TO N''F:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\'+@dbName+'_log.ldf'', NOUNLOAD, REPLACE, STATS = 10') GO I want to pass above four parameters like @file,@dbName,@LogicalName,@ToServer from my stored procedure to above job. so, what i have to do for this? this is my stored procedure: ALTER PROCEDURE runjob AS Declare @backupfile varchar(500),@databasename varchar(50), @logicaldatabasename varchar(50), @toservername varchar(50) set @backupfile = 'test1.bak'; set @databasename = 'test1'; set @logicaldatabasename = 'test1'; set @toservername = 'Live'; BEGIN --exec sp_who2 EXEC msdb.dbo.sp_add_job @job_name = 'k', @file = @backupfile, @dbName = @databasename, @LogicalName = @logicaldatabasename, @ToServer = @toservername EXEC msdb.dbo.sp_start_job k END so, what i have to do for this? and i m receiving an error @file is not a parameter for procedure sp_add_job.
Specify all parameters like this... ALTER PROCEDURE runjob @file1 varchar(50). @directoryName varchar(50) AS
“The woods are lovely, dark and deep. But I have promises to keep, and miles to go before I sleep.”
-
nothing find from google. that's why i put my question here. i have more expectations from codeproject than google. This is my sql job: USE [master] GO declare @file as varchar(500) declare @dbName as varchar(50) declare @LogicalName as varchar(50) declare @ToServer as varchar(50) exec('ALTER DATABASE ['+@dbName+'] SET SINGLE_USER WITH ROLLBACK IMMEDIATE') exec('RESTORE DATABASE ['+@dbName+'] FROM DISK = N''F:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Backup\'+@file+''' WITH FILE = 1, MOVE N'''+@LogicalName+''' TO N''F:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\'+@dbName+'.mdf'', MOVE N'''+@LogicalName+'_log'' TO N''F:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\'+@dbName+'_log.ldf'', NOUNLOAD, REPLACE, STATS = 10') GO I want to pass above four parameters like @file,@dbName,@LogicalName,@ToServer from my stored procedure to above job. so, what i have to do for this? this is my stored procedure: ALTER PROCEDURE runjob AS Declare @backupfile varchar(500),@databasename varchar(50), @logicaldatabasename varchar(50), @toservername varchar(50) set @backupfile = 'test1.bak'; set @databasename = 'test1'; set @logicaldatabasename = 'test1'; set @toservername = 'Live'; BEGIN --exec sp_who2 EXEC msdb.dbo.sp_add_job @job_name = 'k', @file = @backupfile, @dbName = @databasename, @LogicalName = @logicaldatabasename, @ToServer = @toservername EXEC msdb.dbo.sp_start_job k END so, what i have to do for this? and i m receiving an error @file is not a parameter for procedure sp_add_job.
Oh.... see, not only was this entirely a SQL question ( did you post it there ? ), but you were not asking what I thought you were asking. I kept asking you how you called the proc, b/c I thought you wanted to know how to pass the parameter into the proc. Really, you wanted to know how to write a stored proc in the first place. This is where posting code really helps, if you'd done that in the first place, I'd have made sense of your question and answered it ( the way you pass params into a proc depends on how you're calling it in .NET code, that's why I kept asking that and not giving a full answer ).
Christian Graus - Microsoft MVP - C++ "also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )