apostrophe in parameterised SP call trouble

  • Thread starter Thread starter spacehopper_man
  • Start date Start date
S

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 = 'oliver.o''(e-mail address removed)',
@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.
 
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
 
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 = 'oliver.o''(e-mail address removed)',
@dtStartDate = 'Jan 12 2005 5:41:18:370PM' ........
Click to expand...

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
 
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
 
Back
Top