Post reply

Apostrophes ' inside a SQL table called through script task

  • October 28, 2013 at 4:22 am

    #281712

    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

    #1661898

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

  • October 28, 2013 at 6:14 am

    #1661919

    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

    #1661931

    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.

  • October 28, 2013 at 7:01 am

    #1661940

    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

    #1661998

    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

    #1662041

    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

    #1662045

    Deleted

  • October 28, 2013 at 9:36 am

    #1662048

    Err why have you deleted?

  • October 28, 2013 at 9:41 am

    #1662054

    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.

  • October 28, 2013 at 9:46 am

    #1662058

    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

    #1662143

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

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

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy