October 13, 2013 at 2:56 pm
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.
October 13, 2013 at 4:33 pm
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'
October 13, 2013 at 5:54 pm
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.
October 14, 2013 at 3:33 am
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
October 14, 2013 at 11:24 am
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".
October 14, 2013 at 12:29 pm
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
October 14, 2013 at 1:13 pm
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".
October 14, 2013 at 1:15 pm
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
October 14, 2013 at 1:33 pm
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".
October 14, 2013 at 3:06 pm
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
October 14, 2013 at 5:14 pm
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".
October 14, 2013 at 6:05 pm
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);October 15, 2013 at 2:19 am
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
October 15, 2013 at 3:21 am
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.
October 15, 2013 at 12:39 pm
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;
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply