October 28, 2013 at 4:22 am
Hi all
How can I escape the apostrophes when I am using the variables inside a .net script?
See below:
I have a table which contains a column (VARCHAR) [FileSystemPath], amongst others, the FileSystemPath column has data stored which includes apostrophes in the text eg:
[font="Courier New"]Alistair_McGowan's Big Impression_S4_Captions_Jan_2013_550585858.doc[/font]
Now when I query this table the results are returned just fine.
[font="Courier New"]SELECT FileSystemPath from TABLE[/font]
[font="Courier New"]\\wwfis1012a\DocumentStore\Sku\ABAYY122L\Alistair_McGowan's Big Impression_S4_Captions_Jan_2013_550585858.doc[/font]
Which is fine 🙂
However I need to get another value [OriginalName] based on the filesystempath
[font="Courier New"]SELECT [OriginalName] from Table
Where FileSystemPath = 'Alistair_McGowan's Big Impression_S4_Captions_Jan_2013_550585858.doc'[/font]
Which gives me the expected error: [font="Courier New"]Incorrect syntaxt near 's Big_' Unclosed quotation mark after the character string '[/font]
Now, of course, normally if I was running this query I would simply escape the ' in the McGowan's and everything would be fine...
However I am doing all of this query inside a .net script within SSIS, I have around 250,000 records to go through.
Here is my script below the important thing here is the variable User::SingleFileName is the aforementioned Alaistair McGowan etc.,
[font="Courier New"]Dim fileSystemPath As String
fileSystemPath = Dts.Variables("User::SingleFileName").Value
Dts.Variables("User::destNameSQLQuery").Value = "" & _
"select [OriginalName] from [Bbc.Ww.Wam.Migration2012.Database].dbo.[PreSalesSKU_ForRename] " & _
"where FileSystempath = '" & fileSystemPath & "'"
[/font]
Which equates to
[font="Courier New"]select [OriginalName] from [Bbc.Ww.Wam.Migration2012.Database].dbo.[PreSalesSKU_ForRename]
where FileSystempath =
'wwlis1021a\\DocumentStore\\Sku\\ABAY122L\\Alistair_McGowan's_Big_ Impression_S4_Captions_Jan_2013_634940195179370992.doc'
[/font]
which of course fails. 🙁
So my question is: How can I escape the apostrophes when I am using the variables inside a .net script?
Any help greatly greatly received.
Paul
October 28, 2013 at 4:50 am
How about replacing ' with '' in your fileSystemPath variable when you create the SQL?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 28, 2013 at 6:14 am
Thanks I thought of that, but sadly the value is then wrong on what SQL is looking for:
The FileSystemPath has to match exactly, so if I remove the apostrophes then it changes the path so the query never finds the original name 🙁
P
October 28, 2013 at 6:41 am
pnr8uk (10/28/2013)
Thanks I thought of that, but sadly the value is then wrong on what SQL is looking for:The FileSystemPath has to match exactly, so if I remove the apostrophes then it changes the path so the query never finds the original name 🙁
P
I'm not suggesting removing apostrophes. I'm suggesting replacing single apostrophes with two.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 28, 2013 at 7:01 am
Phil Parkin (10/28/2013)
pnr8uk (10/28/2013)
Thanks I thought of that, but sadly the value is then wrong on what SQL is looking for:The FileSystemPath has to match exactly, so if I remove the apostrophes then it changes the path so the query never finds the original name 🙁
P
I'm not suggesting removing apostrophes. I'm suggesting replacing single apostrophes with two.
this, you simply have to make ' into '', and things should work.
October 28, 2013 at 8:37 am
Thanks Phil - Yes I can't believe how much time I spent trying to find something 'clever'! What a plumb I am!
Thanks for your suggestion
Paul
October 28, 2013 at 9:28 am
Actually it is more complicated than a REPLACE will allow. The REPLACE allows me to choose the column now, but later on in the package, I need the proper name again to copy the physical file. The proper name being the one with the apostrophe.... 🙁
Think I am screwed unless I go out to a temp table, then REPLACE the value again to populate an new variable to get hold of the real filename ... SIGH
October 28, 2013 at 9:33 am
Deleted
October 28, 2013 at 9:36 am
Err why have you deleted?
October 28, 2013 at 9:41 am
pnr8uk (10/28/2013)
Actually it is more complicated than a REPLACE will allow. The REPLACE allows me to choose the column now, but later on in the package, I need the proper name again to copy the physical file. The proper name being the one with the apostrophe.... 🙁Think I am screwed unless I go out to a temp table, then REPLACE the value again to populate an new variable to get hold of the real filename ... SIGH
I was not suggesting that you modify the value in the variable - just that you use the replace() when you build the SQL command.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 28, 2013 at 9:46 am
No I get that Phil, you've been a great help, I do a replace in the original query which leaves me with the query reading....
[font="Courier New"]select [OriginalName] from [Bbc.Ww.Wam.Migration2012.Database].dbo.[PreSalesSKU_ForRename]
where FileSystempath =
'wwlis1021a\\DocumentStore\\Sku\\ABAY122L\\Alistair_McGowan''s_Big_ Impression_S4_Captions_Jan_2013_634940195179370992.doc'[/font]
Which is okay and moves me forward, gives me the OriginalName etc., no problem
However later in the package I have to copy the file name to a new location, the file name being.....
[font="Courier New"]Alistair_McGowan's_Big_ Impression_S4_Captions_Jan_2013_634940195179370992.doc'[/font]
I used the SingleFileName variable as the source file for the file system task to copy the file and of course the file is now [font="Courier New"]Alistair_McGowan''s_Big_ Impression_S4_Captions_Jan_2013_634940195179370992.doc'[/font] which is not the file name... 🙁
Seems no way to do this in t-sql alone.
Thanks for all your suggestions and help.
Paul
October 28, 2013 at 12:59 pm
... and of course the file is now Alistair_McGowan''s_Big_ Impression_S4_Captions_Jan_2013_634940195179370992.doc'
I do not understand this bit. Let me explain why.
Step 1: create the SQL
----------------------
Dim fileSystemPath As String
fileSystemPath = Dts.Variables("User::SingleFileName").Value
Dts.Variables("User::destNameSQLQuery").Value = "" & _
"select [OriginalName] from [Bbc.Ww.Wam.Migration2012.Database].dbo.[PreSalesSKU_ForRename] " & _
"where FileSystempath = '" & replace(fileSystemPath,"'","''") & "'"
(I did not check the syntax of the replace function, but you'll get my drift.)
Step 2: Do the file rename
-------------------------
Use User::SingleFileName as required.
I must be missing something...
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply