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 = '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.

 

[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

 

[Bob Barrows [MVP]]

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' ........

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

 

[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