Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

T-SQL Expand / Collapse
Author
Message
Posted Sunday, October 13, 2013 2:56 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 5:09 PM
Points: 178, Visits: 579
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.
Post #1504291
Posted Sunday, October 13, 2013 4:33 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, October 13, 2013 4:37 PM
Points: 14, Visits: 38
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'

Post #1504294
Posted Sunday, October 13, 2013 5:54 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 5:09 PM
Points: 178, Visits: 579
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.
Post #1504297
Posted Monday, October 14, 2013 3:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:15 AM
Points: 13,017, Visits: 10,800
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1504378
Posted Monday, October 14, 2013 11:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 5:09 PM
Points: 178, Visits: 579
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".
Post #1504498
Posted Monday, October 14, 2013 12:29 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:15 AM
Points: 13,017, Visits: 10,800
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1504509
Posted Monday, October 14, 2013 1:13 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 5:09 PM
Points: 178, Visits: 579
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".
Post #1504523
Posted Monday, October 14, 2013 1:15 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:15 AM
Points: 13,017, Visits: 10,800
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1504524
Posted Monday, October 14, 2013 1:33 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 5:09 PM
Points: 178, Visits: 579
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".
Post #1504528
Posted Monday, October 14, 2013 3:06 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:15 AM
Points: 13,017, Visits: 10,800
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1504550
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse