Quotes in comments

  • Thread starter Thread starter Steve Jorgensen
  • Start date Start date
S

Steve Jorgensen

Has anyone else seen this odd behavior?

A CREATE PROCEDURE script that runs fine from query analyzer will fail when run
from an Access 2002 ADP via an ADO Command if there is a single quote (such as
in the word don't) in a comment section of the script. It doesn't matter
whether the comment is a single-line variety delimited as -- ... or a block
comment delimited as /* ... */.

For now, I'm working around this by removing the apostrophes, changing "don't"
to do not, etc.
 
Hi Steve,

use double quote ('')

This behaviour is by default because the single quote is a delimiter
for strings.

HTH ;-)
Click to expand...

The default for what? I'm aware that for any string constants in my SQL, I have
to double the single-quote character, and I do that. Single quotes are fine in
comments as far as SQL Server is concerned, and they are not treated as string
delimiters when in the context of a comment. Why should this be valid SQL
syntax on the server, but not valid via ADO?

The problem I'm having is that I'm using one set of code to read object
definitions from the server and store them in tables, and another set of code to
re-build the objects later by executing the SQL. It's too complicated to write
a parser to figure out if there are any single quotes inside comments and double
them, and besides, that would result in comments containing words like don''t.
My code currently works fine for any object definitions I have no matter how
convoluted except those with apostrophes in comments.
 
The problem is not with comments per se, it is any comment with an apostrophe
anywhere in the comment text. For example...

Create Procedure "SP_Test"
As
set nocount on

/* I don't want to have a problem with this apostrophe */

return
 
when I run the sQL profiler this is what it displays:

-SQL:BatchCompleted Create Procedure SP_Test3 As set nocount on return
Create Procedure SP_Test3 As set nocount on /* I don't want to have a
problem with this apostrophe */
return
+SQL:BatchCompleted IF @@TRANCOUNT > 0 COMMIT TRAN

set implicit_transactions off
...sp_procedure_params_rowset N'SP_Test3', 1, N'dbo', NULL
SET NO_BROWSETABLE ON
SET FMTONLY ON exec sp_execute 2 SET FMTONLY OFF
 
I wonder if it's a problem with the new wrapper ADO provider used in Access 2002
instead of the Data Shaping provider. I've had all kinds of hassles with that.
 
SJ> Has anyone else seen this odd behavior?

Yes, I see it.

DoCmd.RunSQL "create procedure quotetest as /* comment with a qu'ote inside */
select 1"

indeed returns error. Must be a bug in Access.

Vadim
 
Hi Steven,

I tested the stored procedure in Access 2002 SP2 (ADP), there is no problem
on my side.

Create Procedure "SP_Test"
As
set nocount on

/* I don't want to have a problem with this apostrophe */

return

You may try to apply SP-2 to see if the problem still occurs.



Sincerely,

Alick Ye, MCSD
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.


--------------------
| From: Steve Jorgensen <[email protected]>
| Newsgroups: microsoft.public.access.adp.sqlserver
| X-Tomcat-NG: microsoft.public.access.adp.sqlserver
|
| Has anyone else seen this odd behavior?
|
| A CREATE PROCEDURE script that runs fine from query analyzer will fail
when run
| from an Access 2002 ADP via an ADO Command if there is a single quote
(such as
| in the word don't) in a comment section of the script. It doesn't matter
| whether the comment is a single-line variety delimited as -- ... or a
block
| comment delimited as /* ... */.
|
| For now, I'm working around this by removing the apostrophes, changing
"don't"
| to do not, etc.
|
 
AM> I tested the stored procedure in Access 2002 SP2 (ADP), there is
AM> no problem on my side.

looks like you created the s.p. from database window and saved it? Steven indicated:

when run from an Access 2002 ADP via an ADO Command

i.e., from VBA code, not from the database window.


Vadim


AM> Create Procedure "SP_Test"
AM> As
AM> set nocount on

AM> /* I don't want to have a problem with this apostrophe */

AM> return

AM> You may try to apply SP-2 to see if the problem still occurs.

AM> Sincerely,

AM> Alick Ye, MCSD
AM> Microsoft Online Partner Support

AM> Get Secure! - www.microsoft.com/security
AM> This posting is provided "as is" with no warranties and confers no
AM> rights.

AM> --------------------
AM>> From: Steve Jorgensen <[email protected]>
AM>> Newsgroups: microsoft.public.access.adp.sqlserver
AM>> X-Tomcat-NG: microsoft.public.access.adp.sqlserver
AM>>
AM>> Has anyone else seen this odd behavior?
AM>>
AM>> A CREATE PROCEDURE script that runs fine from query analyzer will
AM>> fail
AM> when run
AM>> from an Access 2002 ADP via an ADO Command if there is a single
AM>> quote
AM> (such as
AM>> in the word don't) in a comment section of the script. It
AM>> doesn't matter whether the comment is a single-line variety
AM>> delimited as -- ... or a
AM> block
AM>> comment delimited as /* ... */.
AM>>
AM>> For now, I'm working around this by removing the apostrophes,
AM>> changing
AM> "don't"
AM>> to do not, etc.
AM>>
 
Well, that's great unless I want to rebuild my server-side query objects in my
ADP. Then, I guess I'm just out of luck, eh?
 
correction, if you want to build the programmatically thru the ADO it is a
problem
However if you need to rebuild them, in mass, It is best to use the Script
generated by the SQL Server manager thru the Query analyzer.
you can use the ADP's stored procedure section to add comments thru cut and
paste or typing, with no error.
 
I wrote this code for 3 reasons:

1. The scripts generated by Enterprise manager were not generating all the
object scripts in the correct order which was causing it to take me up to an
hour of manual fiddling to fix the script before it could run properly. My
Access routine allows me to specify the build order for object names.

2. I wanted this to be a quick, automated procedure, not one that requires
first generating the script in Enterprise Manager, then opening the script in
Query Analyzer, then run it.

3. Enterprise manager fails to script extended properties for columns, so any
column-level settings for functions of views are destroyed.

correction, if you want to build the programmatically thru the ADO it is a
problem
However if you need to rebuild them, in mass, It is best to use the Script
generated by the SQL Server manager thru the Query analyzer.
you can use the ADP's stored procedure section to add comments thru cut and
paste or typing, with no error.
Click to expand...

If that works, and I'm presuming that used ADO, I'm still wondering why my code
can't do the same thing using ADO.
 
Back
Top