August 14, 2009 at 11:44 am
i am creating an package where it gets from a webservice and retrieve the data thru xml task and insert into a sql table
it works fine in cases where the name has single quotes like o'connor, o'rielly it throws an error.
My current insert statement is below. i get and i am replacing with null. This works for all data except with single quotes.
"insert into MyTable(column1) values ('" + REPLACE((DT_STR, 25, 1252) @[User::XMLTestVar1], "", "") + "')"
Can anybody let me know how to handle those type of names too?
August 14, 2009 at 2:37 pm
Try this
"insert into MyTable(column1) values ('" + REPLACE((DT_STR, 25, 1252) @[User::XMLTestVar1], "'", "''") + "')"
I have a ssis that logs on error on event handler. and I have used this one, should work too for you
August 14, 2009 at 2:45 pm
You need to escape the single-quote/apostrophe in the name. This should work:
"insert into MyTable(column1) values ('" + QuoteName(REPLACE((DT_STR, 25, 1252) @[User::XMLTestVar1], "", ""), '''') + "')"
You can run this to see how it works:
DECLARE @name VARCHAR(20),
@sql VARCHAR(100)
SET @name = 'o''sullivan'
SET @sql = 'Select ' + QUOTENAME(@name, '''')
EXEC(@sql)
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 14, 2009 at 2:59 pm
I tried it worked.
i have one more scenario
How do i implement the single quotes in the attached file. any help
August 17, 2009 at 6:27 am
I think this is what you need.
"insert into MyTable(column1) values (QuoteName(" + REPLACE((DT_STR, 25, 1252) @[User::XMLTestVar1], "", "") + ", ''''))"
You may have to tweak it a little to make sure quotes get in the right place. Basically you want your query to wrap the quotename function around the string.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply