Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-SQL


T-SQL

Author
Message
sqlnewbie17
sqlnewbie17
Old Hand
Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)

Group: General Forum Members
Points: 330 Visits: 1199
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.
G H G
G H G
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 47
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'
sqlnewbie17
sqlnewbie17
Old Hand
Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)

Group: General Forum Members
Points: 330 Visits: 1199
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
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16441 Visits: 13202
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);





How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
sqlnewbie17
sqlnewbie17
Old Hand
Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)

Group: General Forum Members
Points: 330 Visits: 1199
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
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16441 Visits: 13202
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;





How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
sqlnewbie17
sqlnewbie17
Old Hand
Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)

Group: General Forum Members
Points: 330 Visits: 1199
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
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16441 Visits: 13202
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.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
sqlnewbie17
sqlnewbie17
Old Hand
Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)

Group: General Forum Members
Points: 330 Visits: 1199
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
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16441 Visits: 13202
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.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search