By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,064 Members | 1,449 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,064 IT Pros & Developers. It's quick & easy.

apostrophe in parameterised SP call trouble

P: n/a
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.

Nov 19 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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.

Nov 19 '05 #2

P: n/a
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.
Nov 19 '05 #3

P: n/a
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

Nov 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.