Incorrect Syntax near 'False'

Question

Incorrect Syntax near 'False'

LillyDBA

Mr or Mrs. 500

Points: 515
More actions
March 18, 2010 at 7:57 am

#103911

Hi,
I am using this strategy to deploy the same stored procedure to several databases:
http://www.kodyaz.com/articles/Create-Stored-Procedure-using-sp_msforeachdb-on-all-databases.aspx
However, I am getting an Incorrect syntax near 'False' error
Here is a sample sp that I am deploying:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROC Test1
@StartDate DateTime = NULL,
@EndDate DateTime = NULL
AS
BEGIN
SET NOCOUNT ON
DECLARE @Id as Int
select * from dbo.MyTable
where IsTrue = 'False'
and Date between @StartDate and @EndDate
and Id = @Id
RETURN 0
END
Here is what I am running:
EXEC sp_MSForEachDB 'CreateProcedure ''[?]'', ''Test1'''
I really appreciate any help.

Viewing 9 posts - 1 through 9 (of 9 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

It's probably a question of string building. False has single-quotes around it that are probably breaking the dynamic SQL.

Try replacing that with a 0 (assuming it's a bit field), and see if that works.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

LillyDBA Mr or Mrs. 500 Points: 515 More actions · Answer 2

I alredy tried that, but I get the same error on each area that I have a string. My real sp has a lot of strings. I cannot just change them all to 0 or 1.

GSquared SSC Guru Points: 260824 More actions · Answer 3

Then you'll need to use a different deployment method.

I would generally not include the same proc in multiple databases. I have a database called "Common" where I store that kind of thing. Set up the proc to have a parameter for the database name, and then use either Union commands or dynamic SQL to query just the one table you want. That way you only need to maintain the code in one place.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

LillyDBA Mr or Mrs. 500 Points: 515 More actions · Answer 4

LillyDBA

Mr or Mrs. 500

Points: 515

March 18, 2010 at 8:39 am

#1136005

I must deploy the same sp to several databases.

GSquared SSC Guru Points: 260824 More actions · Answer 5

Write a dynamic SQL script that will allow you to do so. Here's a simple example:

DECLARE @SQL NVARCHAR(MAX);

SELECT @SQL = 'CREATE PROC Test1

@StartDate DateTime = NULL,

@EndDate DateTime = NULL

AS

BEGIN

SET NOCOUNT ON

DECLARE @Id as Int

select * from dbo.MyTable

where IsTrue = ''False''

and Date between @StartDate and @EndDate

and Id = @Id

RETURN 0';

SELECT 'use ' + NAME + '; exec (''' + @SQL + ''');'

FROM sys.databases AS D;

Copy-and-paste the results into a new query window and then run them. (Verify they are correct first, of course.)

Or make a cursor to step over each row of that, and execute them automatically from that.

Or concatenate that into a single script variable and execute that.

Or get a copy of RedGate's multi-script product and make it do the work for you.

There are lots of options.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

LillyDBA Mr or Mrs. 500 Points: 515 More actions · Answer 6

Thank you very much for the idea. I tried it with copy and paste the results, but I received the same error: Incorrect Syntax near 'False', and I also received additional errors: must declare scalar variable @bla

GSquared SSC Guru Points: 260824 More actions · Answer 7

I'd have to see the resulting script to tell you what's wrong with it.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

Gus, I'm suspecting that [IsTrue] is actually a bit column and not a string.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]