Need a workaround for sp_makewebtask @query = SP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need a workaround for sp_makewebtask @query = SP

Author

Topic

hethy
Starting Member

8 Posts

Posted - 2002-08-20 : 09:04:07

I need to be able to use an SP as one of the queries in my sp_makewebtask statement, but it seems that it only allows you to use regular SELECT statements.

Here is what I'm doing:

create table table1
(
table1# int primary key,
table1 varchar(50)
)
GO
INSERT INTO table1 values (11,'Eleven')
INSERT INTO table1 values (12,'Twelve')
GO
create table table2
(
table2# int primary key,
table2 varchar(50)
)
GO
INSERT INTO table2 values (111,'One hundred eleven')
INSERT INTO table2 values (112,'One hundred twelve')
GO
CREATE PROC GET_table2_info AS
SELECT table2#,table2 FROM table2 ORDER BY table2
GO

So far so good. Now, this is the sp_makewebtask statement that WORKS:

EXECUTE sp_makewebtask @outputfile = 'E:\TestFolder\TestFile.asp',
@query = 'SELECT table1#,table1 FROM table1 ORDER BY table1 SELECT table2#,table2 FROM table2 ORDER BY table2', @templatefile = 'E:\TestFolder\TestFile.tpl',
@dbname = 'aubuchon', @rowcnt = 0, @whentype = 10, @datachg = N'TABLE=table1',
@procname="TestWebTask"
GO

This one doesn't -- and the only difference is that the second query passed in is an SP and not just a SELECT statement:

EXECUTE sp_makewebtask @outputfile = 'E:\TestFolder\TestFile2.asp',
@query = 'SELECT table1#,table1 FROM table1 ORDER BY table1 GET_table2_info', @templatefile = 'E:\TestFolder\TestFile.tpl',
@dbname = 'aubuchon', @rowcnt = 0, @whentype = 10, @datachg = N'TABLE=table1',
@procname="TestWebTask2"
GO

When I try to run the second sp_makewebtask statement, Query Analyzer gives me this error:

Server: Msg 170, Level 11, State 1, Line 0
Line 1: Incorrect syntax near 'GET_table2_info'.
Server: Msg 16805, Level 11, State 1, Procedure sp_makewebtask, Line 125
SQL Web Assistant: Could not execute the SQL statement.

This example was run assuming that on your machine there's a file at 'E:\TestFolder\TestFile.tpl'. The code for this file is as follows:

<HTML>
<BODY>

First Table

<%begindetail%>
<%insert_data_here%>: <%insert_data_here%> 
<%enddetail%>

<HR>
Second Table

<%begindetail%>
<%insert_data_here%>: <%insert_data_here%> 
<%enddetail%>


</BODY>
</HTML>

I've been poring over BOL and the web on this question, and coming up empty so far. :(

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-20 : 09:06:39

You need to use the EXECUTE command in front of the SP's name. Also you should separate multiple commands with a semicolon (;).