T-SQL

Question

Post reply

T-SQL

mtz676

SSCrazy Eights

Points: 9255
More actions
October 13, 2013 at 2:56 pm

#395657

How can I create a text file which will have the name of the value in a column which is unique for every ID passed as the parameter in a stored procedure
Each Name has an associated ID attached to it.I will be passing the ID as an input to the Stored procedure.
The reason is I want to do this way is I will have several distinct Names in the table .
SP:
CREATE PROCEDURE [dbo].[usp_Names]
(@ID int)
AS
select * from dbo.Table where ID=@ID
EXEC sp_makewebtask @outputfile = C:\ABC\@ID.htm, @query = 'select * from dbo.Table where ID=@ID'
Name is a column value in the table.Each Name value will have its own ID.No two Names will have same ID's.
Ex 1:I pass ID = 2 and the Name associated to it is ABC,the output file should be ABC_datestamp.html
Ex 2:I pass ID = 3 and the Name associated to it is DEF,the output file should be DEF_datestamp.html
I keep getting this error
Msg 137, Level 11, State 1, Line 0
[Microsoft][SQL Native Client][SQL Server]Must declare the scalar variable "@ID".
Msg 16805, Level 11, State 1, Procedure sp_makewebtask, Line 131
SQL Web Assistant: Could not execute the SQL statement.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

G H G Valued Member Points: 60 More actions · Answer 1

Try something like this

DECLARE @TaskOutFile VarChar(255)

select * from dbo.Table where ID=@ID

Select @TaskOutFile = 'C:\ABC\' + @ID + '.html'

EXEC sp_makewebtask @outputfile = @TaskOutFile, @query = 'select * from dbo.Table where ID=@ID'

mtz676 SSCrazy Eights Points: 9255 More actions · Answer 2

It gives me this error ...

Msg 137, Level 11, State 1, Line 0

[Microsoft][SQL Native Client][SQL Server]Must declare the scalar variable "@ID".

Msg 16805, Level 11, State 1, Procedure sp_makewebtask, Line 131

SQL Web Assistant: Could not execute the SQL statement.

Koen Verbeeck SSC Guru Points: 259155 More actions · Answer 3

sqlserver12345 (10/13/2013)
It gives me this error ...
Msg 137, Level 11, State 1, Line 0
[Microsoft][SQL Native Client][SQL Server]Must declare the scalar variable "@ID".
Msg 16805, Level 11, State 1, Procedure sp_makewebtask, Line 131
SQL Web Assistant: Could not execute the SQL statement.

The problem is that the variable @ID doesn't exist in the scope of the procedure sp_makewebtask.

In other words, @ID is not being replaced with the actual value in the string 'select * from dbo.Table where ID=@ID'. Better create a variable called @SQLQuery and execute the following statement before you call the sp.

DECLARE @SQLQuery VARCHAR(100) = 'select * from dbo.Table where ID=@ID';

SET @SQLQuery = REPLACE(@SQLQuery,'@ID',@ID);

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

mtz676 SSCrazy Eights Points: 9255 More actions · Answer 4

It still throws an error...

ALTER PROCEDURE [dbo].[usp_Names]

(@ID int)

AS

DECLARE @TaskOutFile VARCHAR(255)

DECLARE @SQLQuery VARCHAR(100) = 'select * from dbo.Table where ID=@ID';

SET @SQLQuery = REPLACE(@SQLQuery,'@ID',@ID);

Select @TaskOutFile = 'C:\' +@ID+ '.html'

EXEC sp_makewebtask @outputfile =@TaskOutFile , @query = '@SQLQuery'

Msg 139, Level 15, State 1, Procedure usp_SurgeonProcedures, Line 0

Cannot assign a default value to a local variable.

Msg 137, Level 15, State 2, Procedure usp_SurgeonProcedures, Line 6

Must declare the scalar variable "@SQLQuery".

Koen Verbeeck SSC Guru Points: 259155 More actions · Answer 5

What if you remove the quotes around @SQLQuery?

ALTER PROCEDURE [dbo].[usp_Names]

(@ID int)

AS

DECLARE @TaskOutFile VARCHAR(255)

DECLARE @SQLQuery VARCHAR(100) = 'select * from dbo.Table where ID=@ID';

SET @SQLQuery = REPLACE(@SQLQuery,'@ID',@ID);

Select @TaskOutFile = 'C:\' +@ID+ '.html'

EXEC sp_makewebtask @outputfile =@TaskOutFile , @query = @SQLQuery;

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

mtz676 SSCrazy Eights Points: 9255 More actions · Answer 6

ALTER PROCEDURE [dbo].[usp_Names]

(@ID int)

AS

DECLARE @TaskOutFile VARCHAR(255)

DECLARE @SQLQuery VARCHAR(100) = 'select * from Table where ID=@ID';

SET @SQLQuery = REPLACE(@SQLQuery,'@ID',@ID);

Select @TaskOutFile = 'C:\SSIS' +@ID + '.html'

EXEC sp_makewebtask @outputfile =@TaskOutFile , @query = @SQLQuery;

Still thows an error...

Msg 139, Level 15, State 1, Procedure usp_SurgeonProcedures, Line 0

Cannot assign a default value to a local variable.

Msg 137, Level 15, State 2, Procedure usp_SurgeonProcedures, Line 9

Must declare the scalar variable "@SQLQuery".

Msg 137, Level 15, State 2, Procedure usp_SurgeonProcedures, Line 13

Must declare the scalar variable "@SQLQuery".

Koen Verbeeck SSC Guru Points: 259155 More actions · Answer 7

sqlserver12345 (10/14/2013)
ALTER PROCEDURE [dbo].[usp_Names]
(@ID int)
AS
DECLARE @TaskOutFile VARCHAR(255)
DECLARE @SQLQuery VARCHAR(100) = 'select * from Table where ID=@ID';
SET @SQLQuery = REPLACE(@SQLQuery,'@ID',@ID);
Select @TaskOutFile = 'C:\SSIS' +@ID + '.html'
EXEC sp_makewebtask @outputfile =@TaskOutFile , @query = @SQLQuery;
Still thows an error...
Msg 139, Level 15, State 1, Procedure usp_SurgeonProcedures, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Procedure usp_SurgeonProcedures, Line 9
Must declare the scalar variable "@SQLQuery".
Msg 137, Level 15, State 2, Procedure usp_SurgeonProcedures, Line 13
Must declare the scalar variable "@SQLQuery".

I meant removing the quotes around the actual variable @SQLQuery (which I already did in the code example).

Not around the string.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

mtz676 SSCrazy Eights Points: 9255 More actions · Answer 8

Did what you suggested...still throws an error...

Here is the code I tried executing...

ALTER PROCEDURE [dbo].[usp_Names]

(@ID int)

AS

DECLARE @TaskOutFile VARCHAR(255)

DECLARE @SQLQuery VARCHAR(100) = 'select * from Table where ID=@ID';

Select @TaskOutFile = 'C:\SSIS' +@ID + '.html'

EXEC sp_makewebtask @outputfile =@TaskOutFile , @query = @SQLQuery;

Msg 139, Level 15, State 1, Procedure usp_SurgeonProcedures, Line 0

Cannot assign a default value to a local variable.

Msg 137, Level 15, State 2, Procedure usp_SurgeonProcedures, Line 10

Must declare the scalar variable "@SQLQuery".

Koen Verbeeck SSC Guru Points: 259155 More actions · Answer 9

One bug that is still in the statement is that you have to cast @ID to string in the concatenation for @TaskOutFile.

Select @TaskOutFile = 'C:\SSIS' + CONVERT(VARCHAR(10),@ID) + '.html';

The errors are about another stored procedure by the way.

I can't test the code myself, because I don't have SQL Server editions that old.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

mtz676 SSCrazy Eights Points: 9255 More actions · Answer 10

Already tried that...still getting an error...

Msg 139, Level 15, State 1, Procedure usp_Names, Line 0

Cannot assign a default value to a local variable.

Msg 137, Level 15, State 2, Procedure usp_Names, Line 12

Must declare the scalar variable "@SQLQuery".

mister.magoo SSC-Forever Points: 47174 More actions · Answer 11

In your version of SQL Server, you cannot use the form

DECLARE @myvar varchar(100) = 'Some Value';

You have to use two statements, one to DECLARE then a SET.

DECLARE @SQLQuery VARCHAR(100);

SET @SQLQuery = 'select * from Table where ID=@ID';

Next problem is that the sp_makewebtask proc knows nothing of your variable @ID...

So try this instead:

DECLARE @SQLQuery VARCHAR(100);

SET @SQLQuery = 'select * from Table where ID='+CAST(@ID AS VARCHAR(20));

This will create a query like this to pass to the sp_makewebtask:

select * from Table where ID=12345

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Koen Verbeeck SSC Guru Points: 259155 More actions · Answer 12

mister.magoo (10/14/2013)
In your version of SQL Server, you cannot use the form
DECLARE @myvar varchar(100) = 'Some Value';
You have to use two statements, one to DECLARE then a SET.
DECLARE @SQLQuery VARCHAR(100);
SET @SQLQuery = 'select * from Table where ID=@ID';

Huh. I learned TSQL on SQL Server 2005, but I complete forgot you cannot assign a value to a variable while declaring it.

Time flies by

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

You may want to consider rewriting this process using SSRS. The Web Assistant procedures have been deprecated and I don't believe they exist in SQL Server 2008 or later.

mtz676 SSCrazy Eights Points: 9255 More actions · Answer 14

The code worked for sometime ...

then it stopped working.

It gave me these extracts SSIS570.html where 570 is the ID passed as a parameter.

After testing it a couple of times it started giving me onlySSIS5.html with no data in it.Any other ID I passed say 455 it would give me SSIS4.html with no data in it.Any thoughts where the code got messed up ?

CODE:

alter procedure [dbo].[usp_Names]

(@ID varchar)

AS

DECLARE @TaskOutFile VARCHAR(255)

DECLARE @SQLQuery VARCHAR(100)

SET @SQLQuery ='select * from dbo.InfectionControl where ID='+CAST(@ID AS VARCHAR(20));

Select @TaskOutFile = 'C:\SSIS' + @ID + '.html';

EXEC sp_makewebtask @outputfile =@TaskOutFile ,

@query = @SQLQuery;

T-SQL

Cookies on SQLServerCentral