Store Proc Error

Question

Store Proc Error

Tara-1044200

SSCoach

Points: 15951
More actions
August 1, 2009 at 7:42 am

#207529

ALTER PROCEDURE TraceReport
@server varchar(6),@FromDate datetime,@ToDate datetime
AS
BEGIN
SET NOCOUNT ON;
EXEC('SELECT databasename,starttime,eventclass,LoginName,TargetUsername,Objectname
FROM ' + @server + '.TRACEDB.dbo.TraceTable where starttime between '+@FromDate+' and '+@ToDate+'' )
--
END
EXEC TraceReport 'obbeaver','2009-02-11','2009-04-19'
Error :
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '11'.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

Is it including the "EXEC" line as a part of the stored procedure? Or are you highlighting and running the ALTER segment separately?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Tara-1044200 SSCoach Points: 15951 More actions · Answer 2

I am executing store proc and getting the error, its not included in the proc

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

If you replace the exec with a print statement in to print the full text of the dynamic SQL, what does it print?

Print 'SELECT databasename,starttime,eventclass,LoginName,TargetUsername,Objectname

FROM ' + @server + '.TRACEDB.dbo.TraceTable where starttime between '+@FromDate+' and '+@ToDate+''

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Tara-1044200 SSCoach Points: 15951 More actions · Answer 4

I get error when executing the procedure not within the proc.

if i replace with print i get this error:

Msg 241, Level 16, State 1, Procedure ReportTrace, Line 19
Conversion failed when converting datetime from character string.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Then try explicitly casting your dates to string before concatenating them, use the CONVERT function.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 6

It's more than just that... you also have to quote encapsulate the dates...

SELECT QUOTENAME(GETDATE(),'''')

ALTER PROCEDURE TraceReport

@server varchar(6),@FromDate datetime,@ToDate datetime

AS

BEGIN

SET NOCOUNT ON;

EXEC('SELECT databasename,starttime,eventclass,LoginName,TargetUsername,Objectname

FROM ' + @server + '.TRACEDB.dbo.TraceTable where starttime between '+QUOTENAME(@FromDate,'''')+' and '+QUOTENAME(@FromDate,''''))

END

You also don't need the last set of quotes so I took those out.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Tara-1044200 SSCoach Points: 15951 More actions · Answer 7

Tara-1044200

SSCoach

Points: 15951

August 1, 2009 at 7:05 pm

#1033415

Jeff

Incorrect syntax near 'QUOTENAME'.

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 8

Ah crud... sorry Tara... I forgot that EXEC will only take constants and variables as operands. It won't take functions. For example...

EXEC('SELECT ' + SQRT(4))

... fails just like QUOTENAME does. Instead, we have to build the dynamic SQL into a variable and then execute the variable. In your case, the sproc would need to look something like this...

ALTER PROCEDURE TraceReport

@server VARCHAR(6),

@FromDate DATETIME,

@ToDate DATETIME

AS

BEGIN

--===== Supress the autodisplay of rowcounts to prevent false error returns

SET NOCOUNT ON;

--===== Declare and set a dynamic SQL variable with the correct SQL construction

-- base on the spoc input parameters. We need to do this separately because

-- of the use of functions. It's also a good practice to support troubleshooting

-- because you can print out the SQL.

DECLARE @sql VARCHAR(8000)

SELECT @sql = 'SELECT databasename,starttime,eventclass,LoginName,TargetUsername,Objectname '

+ 'FROM ' + @server + '.TRACEDB.dbo.TraceTable where starttime between '

+ QUOTENAME(@FromDate,'''') + ' AND '+QUOTENAME(@FromDate,'''')

--===== Uncomment this line for troubleshooting

--PRINT @sql

--===== Execute the constructed dynamic SQL.

EXEC (@SQL)

END

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 9

Actually, this is the way I write code at work... it makes debugging much, much easier...

ALTER PROCEDURE TraceReport

@server VARCHAR(6),

@FromDate DATETIME,

@ToDate DATETIME,

@Debug BIT = 0

AS

BEGIN

--===== Supress the autodisplay of rowcounts to prevent false error returns

SET NOCOUNT ON;

--===== Declare and set a dynamic SQL variable with the correct SQL construction

-- base on the spoc input parameters. We need to do this separately because

-- of the use of functions. It's also a good practice to support troubleshooting

-- because you can print out the SQL.

DECLARE @sql VARCHAR(8000)

SELECT @sql = 'SELECT databasename,starttime,eventclass,LoginName,TargetUsername,Objectname '

+ 'FROM ' + @server + '.TRACEDB.dbo.TraceTable where starttime between '

+ QUOTENAME(@FromDate,'''') + ' AND '+QUOTENAME(@FromDate,'''')

--===== Print the SQL if Debug is "on"

IF @Debug 0 PRINT @sql

--===== Execute the constructed dynamic SQL.

EXEC (@SQL)

END

Ignore this next little window... I'm "tricking" the wrap function of this display to not wrap...

____________________________________________________________________________________________________________________

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Tara-1044200 SSCoach Points: 15951 More actions · Answer 10

Tara-1044200

SSCoach

Points: 15951

August 2, 2009 at 1:11 pm

#1033463

THanks Jeff, QUOTENAME helped me.