Handling QUOTES in text strings.

Question

Handling QUOTES in text strings.

Melissa A Dougherty

Old Hand

Points: 334
More actions
September 15, 2006 at 7:50 pm

#114794

I am having an issue excepting quotes from a java app in a text string. We are using a stored procedure and several variables are text strings. In the stored procedure I also kick off another sproc on a linked server. I can get the local db to stored the data correctly using the REPLACE function, however, that's not working on the linked server. The string data is sent into the variable as so... 'It''s fun working with quotes'
Any help would be great.
Thanks, Melissa

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

john-132685 SSCertifiable Points: 6134 More actions · Answer 1

I'm always in favor of making the client communicate correctly with the server. So the application passing the information should be responsible for passing it correctly.

However, if you want to handle it, why not make a function on your end that cleans strings of superfluous quotes, and always call this first before passing anything on? (You say you kick off the stored proc on the related server, so it sounds like you have the opportunity to fix it before.)

(Note that the problem with cleaning it on your end is that you don't always know that there isn't supposed to be two single quotes together in a particular string.)

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 2

does the linked server have a different quoted identifier setting or collation?

www.sql-library.com[/url]

Gift Peddie SSC Guru Points: 73570 More actions · Answer 3

I think it is better to handle them as what they are in Java or C# they are string literals with double-qoutes (") and in ANSI SQL they are Qouted Identifiers and Delimiters per ANSI SQL 1992. The key is there are fixed rules of how to handle them. Try the link below for SQL Server Delimiters, sorry I don't write Java anymore but you could check the docs of how to handle them. In C# ("") is required to use them. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms176027.aspx

Kind regards,
Gift Peddie

Melissa A Dougherty Old Hand Points: 334 More actions · Answer 4

My developer says he doesn't have any options for how he submits the variables into the stored procedure. I am checking into the settings for the quoted identifier setting or collation. Where do I look for the seeting of the quoted identifier?

Thanks,

Melissa

Melissa A Dougherty Old Hand Points: 334 More actions · Answer 5

The quoted identifier on both servers is unchecked.... however, the collation is different. Both are Latin1 but different. Does that make a big difference?

Melissa

Gift Peddie SSC Guru Points: 73570 More actions · Answer 6

You need to turn quoted_identifier on per the instructions in the link I posted and give this link to your developer most people don't know about literals, almost like idoms in English you don't need to know them. But they come with fixed rules of what you can do or your application will have to run without. Hope this help.

http://mindprod.com/jgloss/literals.html

Kind regards,
Gift Peddie