TSQL - SP_makewebtask - SQL Server Forums

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

Transact-SQL (2000)

TSQL - SP_makewebtask

Author

Topic

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2004-03-18 : 01:44:05

Hi

I'm trying to execute the following code but it keeps giving me the following error:[Microsoft][ODBC SQL Server Driver]Syntax error or access violation

Here's the code:

use sms_system

Go

execute SP_makewebtask @outputfile = 'C:\Daily Count.html',

@query = 'SELECT COUNT(Enterprise_nbr) AS [Count], maint_user_code
FROM sql.sms_system.dbo.History_Reo
WHERE (Survey_Code = agri2002 OR Survey_Code = agri2003) 
	AND (Reo_code = 25 OR Reo_code = 27) 
	AND (maint_date > CONVERT(DATETIME,  2004-03-17 00:00:00, 102)) 
	AND (maint_date < CONVERT(DATETIME,  2004-03-18 00:00:00,  102))
GROUP BY maint_user_code',

@bold = 1,
@resultstitle = 'Test Results Set',
@dbname = 'sms_system',

@whentype = 10,
@datachg = {history_reo[Maint_date]}

Go

I ran the "Index Analysis" for the query and it's returning th following:
The query was unparseable for one of the following reasons:
1. The user has selected the wrong database or text of the query selected for analysis is not parseable.
2. The keyword GO was selected for tuning.

I know that there's no syntax error - what can i do to fix this?

You can't teach an old mouse new clicks.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-03-18 : 03:08:01

Hi Richard, how's Virgin coming along?

I guess the syntax error in the query is the missing single quotes for the string parameters to the Survey code conditions. Same goes for the maint_date dates. Since this query is already inside single quotes, you will need to "escape" any quotes inside by doubling them, like this:


...
@query = 'SELECT COUNT(Enterprise_nbr) AS [Count], maint_user_code
FROM sql.sms_system.dbo.History_Reo
WHERE (Survey_Code = ''agri2002'' OR Survey_Code = ''agri2003'') 
	AND (Reo_code = 25 OR Reo_code = 27) 
	AND (maint_date > CONVERT(DATETIME,  ''2004-03-17 00:00:00'', 102)) 
	AND (maint_date < CONVERT(DATETIME,  ''2004-03-18 00:00:00'', 102))
GROUP BY maint_user_code',
...

Are you trying to run Index Analysis on the whole SQL you have posted above? You should try to run it on just the text inside the @query parameter. Copy and paste it into a new QA window, change the double apostrophes to single apostrophes, and then run the Index Analysis.

OS

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2004-03-18 : 03:41:56

No Go Mate!
I included the double quotations (''Parameter'') and it's still giving me the same error???


use sms_system

Go

execute SP_makewebtask @outputfile = 'C:\Daily Count.html',

@query = 'SELECT COUNT(Enterprise_nbr) AS [Count], maint_user_code
FROM sql.sms_system.dbo.History_Reo
WHERE (Survey_Code = ''agri2002'' OR Survey_Code = ''agri2003'') 
	AND (Reo_code = ''25'' OR Reo_code = ''27'') 
	AND (maint_date > CONVERT(DATETIME,  ''2004-03-17 00:00:00'', 102)) 
	AND (maint_date < CONVERT(DATETIME,  ''2004-03-18 00:00:00'',  102))
GROUP BY maint_user_code',

@bold = 1,
@resultstitle = 'Test Results Set',
@dbname = 'sms_system',

@whentype = 10,
@datachg = {history_reo[Maint_date]}

Go

Any ideas???

PS. I'm sexually active now!

You can't teach an old mouse new clicks.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-03-18 : 04:44:28

Greetings Countryman.

Try This:
use sms_system

Go

execute SP_makewebtask @outputfile = 'C:\Daily Count.html',

@query = 'SELECT COUNT(Enterprise_nbr) AS [Count], maint_user_code
FROM sql.sms_system.dbo.History_Reo
WHERE (Survey_Code = ''agri2002'' OR Survey_Code = ''agri2003'')
AND (Reo_code = ''25'' OR Reo_code = ''27'')
AND (maint_date > CONVERT(DATETIME, ''2004-03-17 00:00:00'', 102))
AND (maint_date < CONVERT(DATETIME, ''2004-03-18 00:00:00'', 102))
GROUP BY maint_user_code',

@bold = 1,
@resultstitle = 'Test Results Set',
@dbname = 'sms_system',

@whentype = 10,
@datachg = 'TABLE=history_reo COLUMN=Maint_date'

Go

Duane.

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2004-03-18 : 05:02:46

Duane for SQL President!

Thanks - I tried the code and it working fine

Thanks mate.

You can't teach an old mouse new clicks.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-03-18 : 05:21:19

Oh, so was it the @datachg thingy too? Don't know, never tried this sp before. I guess we tackle one bug at a time.

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2004-03-18 : 05:44:27

hehehehe

This is a cool procedure but I WOULDN'T suggest using it with "@whentype = 10" cause every time it executes it blocks inserts and or updates into the trigger field (in my case maint_date).

You can't teach an old mouse new clicks.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-03-19 : 04:45:54

Personaly. I'd do the web stuff with asp rather than sp_makewebtask.

Duane.

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2004-03-19 : 04:54:08

You must crawl before you walk.

Give me time...

You can't teach an old mouse new clicks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources