Dynamic SQL statement

Question

Dynamic SQL statement

Harold Buckner

SSCarpal Tunnel

Points: 4175
More actions
August 20, 2004 at 11:04 am

#66525

All of you experts out there, I have a question dealing with single quotes in data.
I'm in the process of tring to determine how to hande single quotes in DTSSource("xxxx") when building a dynamic SQL string that will be used for an ADO Database connection. I created a multiphase data pumb package and in the Transform Failure Phase I built a sql string to write the failed rows to an error table. Some of the DTSSource("xxxxx") contain single quotes that will brake the SQL string. I know that we could use the replace function and replace the single quote with a space or something of that matter. However, I do not believe that would be the best way to handle it. In the following example:
sSQL = "INSERT INTO [Northwind].[dbo].[OrdersReject]([OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName])"
   sSQL = sSQL & " Values ('" & DTSSource("OrderID") & "', '" & DTSSource("CustomerID")
   sSQL = sSQL &   "', '" & DTSSource("EmployeeID") & "', '" & DTSSource("OrderDate") & "', "
   sSQL = sSQL & "NULL, '" & DTSSource("ShippedDate") & "', '"
   sSQL = sSQL & DTSSource("ShipVia") & "', "
   sSQL = sSQL & DTSSource("Freight") & ", '"
   sSQL = sSQL & DTSSource("ShipName") & "')"
The last statement could contain a single quote, What would be the proper way to handle this case? Thanks for your help.

Viewing 3 posts - 1 through 2 (of 2 total)

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

Jim P. SSCrazy Eights Points: 8725 More actions · Answer 1

Check the BOL for "SET QUOTED_IDENTIFIER" as in:

When SET QUOTED_IDENTIFIER is OFF (default), literal strings in
expressions can be delimited by single or double quotation marks. If a
literal string is delimited by double quotation marks, the string can
contain embedded single quotation marks, such as apostrophes.

----------------
Jim P.

A little bit of this and a little byte of that can cause bloatware.

Harold Buckner SSCarpal Tunnel Points: 4175 More actions · Answer 2

using the ole db drivers, I guess the SET QUOTED_IDENTIFIER is ON by default. I send the SET QUOTED_IDENTIFIER is OFF command first and then my querry and it worked fine with the double quotes. Thanks for you help.