October 26, 2005 at 3:52 pm
Using the SQL 2005 CTP, I noticed an irregularity when scripting database schema (right click on database, select "Tasks", then "Generate Scripts"). In particular, single-quotes seem to be converted into dual single-quotes in stored procedures (and possibly in other schema entities--I didn't check).
For example, a line in a stored procedure that says this:
PRINT 'Date/Time Is: ' + CONVERT(VARCHAR, GETDATE())
gets converted to:
PRINT ''Date/Time Is: '' +
CONVERT(VARCHAR, GETDATE())
Notice the dual single-quotes. This line won't compile AS IS. What's the scoop here? Is there some "quote" identifier format I need to set?
- john
October 31, 2005 at 8:00 am
This was removed by the editor as SPAM
October 31, 2005 at 12:50 pm
Hi John,
I have checked the details, and found that it is not any irregularity
but scripting creates Procedure with
EXEC dbo.sp_executesql @statement
Complete Procedure is a text string 'Create Procedure ...
Now if you have 'Date with single quote , this starting quote is considered as end of string, which is not the case here. To avoid
such misinterpretation 2 single quotes are used and this
is standard form. I am placing the complete script here run that
and then try exec test2 , it should run fine
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
IF
NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Test2]') AND type in (N'P', N'PC'))
BEGIN
EXEC
dbo.sp_executesql @statement = N'
create procedure [dbo].[Test2]
AS
PRINT ''Date/Time Is: '' + CONVERT(VARCHAR, GETDATE())
'
END
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com
October 31, 2005 at 1:04 pm
Hey, thanks for replying. Yes--you're right!
Sorry, I didn't notice they were using the "sp_executesql" which adds an additional level of quotes. I had just cut/extracted the body of the SP, without checking what was bracketing it. Works fine when you include the whole thing in there.
Thanks again.
- john
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply