apostrophe in parameterised SP call trouble

[spacehopper_man]

hi -

I am "apostrophe in sql" problems ;)

I am executing a stored procedure on SQL Server - and passing in a
string parameter.

the string has a single apostrophe in it.

the call is failing with an "Incorrect syntax" message.

when I profile SQL Server I see that it is being passed a command that
looks like this:

exec
up_myproc @iUserId = 1, @iCustomerId = 1,
@vcCustomerUser = 'o***************@messagelabs.com',
@dtStartDate = 'Jan 12 2005 5:41:18:370PM' ........

note that the apostrophe has now been doubled (presumably by the
framework) - and this statement now rejected by SQL Server.

so it seems that something (the framework somewhere) is attempting to
escape my query, but is doing so in such a way as to cause broken SQL
exec statements.
Any ideas what to do?
Anyone seen this before?

ta,
Oli.

[Patrice]

What if you execute the same SQL in SQL Server ?

For now, it seems to me it's correct to double the quote. The exact error
message could help.

Patrice
--

<sp*************@yahoo.com> a écrit dans le message de
news:11**********************@f14g2000cwb.googlegr oups.com...

hi -

I am "apostrophe in sql" problems ;)

I am executing a stored procedure on SQL Server - and passing in a
string parameter.

the string has a single apostrophe in it.

the call is failing with an "Incorrect syntax" message.

when I profile SQL Server I see that it is being passed a command that
looks like this:

exec
up_myproc @iUserId = 1, @iCustomerId = 1,
@vcCustomerUser = 'o***************@messagelabs.com',
@dtStartDate = 'Jan 12 2005 5:41:18:370PM' ........

note that the apostrophe has now been doubled (presumably by the
framework) - and this statement now rejected by SQL Server.

so it seems that something (the framework somewhere) is attempting to
escape my query, but is doing so in such a way as to cause broken SQL
exec statements.
Any ideas what to do?
Anyone seen this before?

ta,
Oli.

[Bob Barrows [MVP]]

sp*************@yahoo.com wrote:

hi -

I am "apostrophe in sql" problems ;)

I am executing a stored procedure on SQL Server - and passing in a
string parameter.

the string has a single apostrophe in it.

the call is failing with an "Incorrect syntax" message.

when I profile SQL Server
Good.
I see that it is being passed a command that
looks like this:

exec
up_myproc @iUserId = 1, @iCustomerId = 1,
@vcCustomerUser = 'o***************@messagelabs.com',
@dtStartDate = 'Jan 12 2005 5:41:18:370PM' ........

If you copy and paste this into SQL Query Analyzer and run it, I think you
will find that the parameter value is correct. It's supposed to double up
embedded apostrophes to prevent them from being treated as literals.

I think you will find that the problem lies elsewhere than what you've shown
us.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[spacehopper_man]

fixed it!

the data was making it into the procedure.... but we were doing
something odd - and dyncamically building and executing more SQL inside
that - that's were we had the problems.

thanks everybody,
regards,
Oli