November 24, 2004 at 2:37 pm
Hello,
I am having some problems with Global Variables in an Execute SQL task. I have a script where I want to use the same global variable (year) in it several times (see below). When I try to set the parameter in the task (after changing all of the hard coded years to a ?), it bombs out and kicks me back to the desktop.
I tried changing my script to only use one parameter (see below) and then set a variable equal to that parameter. When I try this, it refuses to parse. It seems like this should be very straightforward. The script parses (and runs) just fine when I set @year equal to '2004' instead of setting a parameter.
Any ideas would be greatly appreciated.
Chris
Code
Declare Year nvarchar(4)
set @year = ?
Delete from Admin.dbo.CDSNightlyCount Where SLSYEAR = @year and TableName = 'JOBS'
go
Insert Admin.dbo.CDSNightlyCount
Select @year, '003', 'JOBS',
(Select count(*) from SLS.SLS.CO3JOBS),
(Select count(*) from SRQCDS.dbo.JOBS where SLSYEAR = @year and SLSCO = 003),
getdate()
Insert Admin.dbo.CDSNightlyCount
Select @year, '007', 'JOBS',
(Select count(*) from SLS.SLS.CO7JOBS),
(Select count(*) from SRQCDS.dbo.JOBS where SLSYEAR = @year and SLSCO = 007),
getdate()
Insert Admin.dbo.CDSNightlyCount
Select @year, '009', 'JOBS',
(Select count(*) from SLS.SLS.CO9JOBS),
(Select count(*) from SRQCDS.dbo.JOBS where SLSYEAR = @year and SLSCO = 009),
getdate()
Insert Admin.dbo.CDSNightlyCount
Select @year, '030', 'JOBS',
(Select count(*) from SLS.SLS.CO30JOBS),
(Select count(*) from SRQCDS.dbo.JOBS where SLSYEAR = @year and SLSCO = 030),
getdate()
Insert Admin.dbo.CDSNightlyCount
Select @year, '032', 'JOBS',
(Select count(*) from SLS.SLS.CO32JOBS),
(Select count(*) from SRQCDS.dbo.JOBS where SLSYEAR = @year and SLSCO = 032),
getdate()
Insert Admin.dbo.CDSNightlyCount
Select @year, '011', 'JOBS',
(Select count(*) from SLS.SLS.CO11JOBS),
(Select count(*) from SRQCDS.dbo.JOBS where SLSYEAR = @year and SLSCO = 011),
getdate()
Insert Admin.dbo.CDSNightlyCount
Select @year, '080', 'JOBS',
(Select count(*) from SLS.SLS.CO80JOBS),
(Select count(*) from SRQCDS.dbo.JOBS where SLSYEAR = @year and SLSCO = 080),
getdate()
Insert Admin.dbo.CDSNightlyCount
Select @year, '091', 'JOBS',
(Select count(*) from SLS.SLS.CO91JOBS),
(Select count(*) from SRQCDS.dbo.JOBS where SLSYEAR = @year and SLSCO = 091),
getdate()
November 29, 2004 at 8:00 am
This was removed by the editor as SPAM
November 29, 2004 at 1:43 pm
Take all the code you posted after
Declare Year nvarchar(4)
set @year = ?
and place it in a stored procedure. For the sake of an example I'll call the SP spYear
Now put this command in your Execute SQL Task:
EXEC spYear @year = ?
Then click the parameter button to set your input parameter to the name of the global variable where the year value is stored.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
November 29, 2004 at 1:46 pm
I was thinking about putting all of that code in a stored proc, but I wanted to figure out why the code above wouldn't work. It seems like it should. I'll have to play with it a bit and see what I can get to work.
Thanks,
Chris
November 29, 2004 at 2:04 pm
I have had similar problems as what you mention and had to put everything into a SP to get it to work properly. I don't know that you can assign the value of a global variable directly to a variable like you are showing here.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply