Apostrophes ' inside a SQL table called through script task

  • 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

  • How about replacing ' with '' in your fileSystemPath variable when you create the SQL?

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • 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

  • 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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • 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.

  • 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

  • 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

  • Deleted

  • Err why have you deleted?

  • 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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • 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

  • ... 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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

Viewing 12 posts - 1 through 11 (of 11 total)

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