SQL SERVER – How to Escape Single Quotes – Fix: Error: 105 Unclosed quotation mark after the character string ‘

Jr. Developer asked me other day how to escape single quote?

User can escape single quote using two single quotes (NOT double quote).

Following T-SQL Script will give error

USE AdventureWorks
GO
SELECT *
FROM Person.Address
WHERE City = ‘Villeneuve-d’Ascq’
GO

Resultset:

Error: 105 Unclosed quotation mark after the character string ‘

To fix the error mentioned above, escape single quotes with two single quotes as displayed in following example.

USE AdventureWorks
GO
SELECT *
FROM Person.Address
WHERE City = 'Villeneuve-d''Ascq'
GO

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

69 thoughts on “SQL SERVER – How to Escape Single Quotes – Fix: Error: 105 Unclosed quotation mark after the character string ‘

  1. @ENTITY_NAME NVARCHAR(100)

    IF @ENTITY_NAME = ‘%’
    BEGIN
    SELECT @WHERE = @WHERE + ‘ AND (T.ENTITY_ID LIKE ”’ + @ENTITY_NAME + ”’ OR T.ENTITY_ID IS NULL)’
    END

    (If @ENTITY_NAME value is ‘ramarao’ in this I did not get any problem. But, ‘ramarao’s ‘ I am getting error like Unclosed quotation mark after the character string ‘). Can you please help out me by giving proper code (how to replace two single quotes in place of one single quote).

    Thanks in Advance.

    Thanks,
    B. Rama Rao

    • Problem is mother of solution…
      Here’s one of the solution to pass single quote (‘) a.k.a. Apostrophe in searches as variables.
      Just add the following to your T-SQL

      Declare @find nvarchar(5)
      Declare @replace nvarchar(5)
      Declare @yourvariable –this is the variable used in search

      Set @find = ”+ char(39) +” — these are two single quotes
      Set @replace = ”+char(39)+char(39)+”
      Set @yourvariable = replace(@yourvariable,@find,@replace) –this will replace one single quote with two double quote

      This will be helpful in statement such as select * from tbl_name where column_name like ‘%+@yourvariable+%’

      Hope this helps and saves some development time.
      bhattji

    • I think what the OP is looking for, is something like the code below.

      I had the same issue when I imported some Countries, one of which was Cote d’lvoire. But is was misspelled in Import file as Cote d’lovire. So I tried this code, but obviously would not work:

      UPDATE [dbo].[Country_Codes_ISO]
      SET Country = ‘Cote d’lvoire’
      WHERE Country = ‘Cote d’lovire’

      So what you need to do is use the CHAR function, and the ASCII Code for a Single quote is 39. So using this code below works:

      UPDATE [dbo].[Country_Codes_ISO]
      SET Country = ‘Cote d’ + CHAR(39) + ‘lvoire’
      WHERE Country = ‘Cote d’ + CHAR(39) + + ‘lovire’

  2. OK this is easiest example
    My value that I want to query is -> Ma’ry (you will see it has ‘ among word)

    select Name
    from employee
    where Name = ‘Ma”ry’

    word by word -> Single quote+Ma+Single quote+Single quote+ry+Single quote

  3. This is a helpful post.

    This is hideous functionality. Completely unintuitive. One more reason for me to hate SQL Server…

  4. Oh, and for you other [removed word]:

    set quoted_identifier off
    select “Hello ‘you’ if that’s your real name”
    set quoted identifier on

    Learn how to learn!

    • Martin,

      How’s it feel up on your ivory tower?

      Thanks for sharing your solution for the we, your inferiors. It’s a good solution and just saved me some time.

      But WTF do you mean by scolding us rubes with “Learn how to learn?” Are you going to tell us that you’ve never posted a perfectly good question on one forum or another?

      One way to learn is to ask. That’s what the OP was doing.

  5. Hello, I have a set of columns I need use in a query but they contain single quote elements.

    e.g.: [Mark's House]
    Whenever I try to do this:

    SET @sql_query = ‘INSERT INTO ‘ + @TABLE_XYZ + ‘ ( ‘ + @TRG_FIELDS + ‘ ) VALUES ( ‘ + @COLUMN_ABC ‘ + ‘)’ ;
    EXEC ( @sql_query );

    The query works as long as the columns do not contain the single quote element.

    Can anyone give me hand on this?
    I am no longer sure of what could be the solution to this.

    I am sure it’s simple. :(

  6. @ed.

    Either escape the single-quote with another, or encase it in brackets.

    VALUES ([ ' + @COLUMN_ABC + '])’';

  7. Thanks Brian. I am lost with the double quote at the end of the line. This is the query as I have it:

    SET @sql_query = ‘INSERT INTO ‘ + @TRG__db_table + ‘ ( ‘ + @TRG__tb_fields + ‘ ) VALUES ( ‘ + @user_name + ‘, ‘ + @TRG__address + ‘, ‘ + @TRG__user_name + ‘ , ‘ + @TRG__city_town + ‘, ‘ + @TRG__state_province + ‘ )';

    PRINT ‘SQL-Query: ‘ + @sql_query;

    Should I make it like:

    SET @sql_query = ‘INSERT INTO [' + @TRG__db_table + '] ( [' + @TRG__tb_fields + '] ) VALUES ( [' + @user_name + '], [' + @TRG__address + '], [' + @TRG__user_name + '], [' + @TRG__city_town + '], [' + @TRG__state_province + '] )';

    If this is ok, where does the double quote should be placed?

  8. I have tried your suggestion and this is the result:

    SET @sql_query = “SELECT COUNT( loc_name + ctv_name + xyz_code ) FROM [dbo].[' + @country + '_Listings] WHERE ( loc_name = [' + @province + '] ) AND ( ctv_name = [' + @city + '] ) AND ( sic_code = [' + @xyz_code + '] )”;

    Msg 103, Level 15, State 4, Procedure set_RECORDS, Line 54
    The identifier that starts with ‘

    SELECT COUNT( loc_name + ctv_name + xyz_code ) FROM [dbo].[' + @country + '_Listings]‘

    is too long. Maximum length is 128.

  9. @ed.

    Sorry about that. The double-quote should be a single-quote. It needs to end off the ])

    In your last example too, make the double-quote a single-quote.

    Double-quote are used to quote names, such as a TABLE or COLUMN. Single-quotes are used for values.

  10. @ed.

    I made a mistake. Brackets are for objects, not literals.

    So, escape it with a single-quote. REPLACE(col, ””, ”””)

    SET @sql_query = ‘SELECT COUNT(loc_name + ctv_name + xyz_code )’
    + ‘ FROM [dbo].[' + @country + '_Listings]‘
    + ‘ WHERE loc_name = ‘ + REPLACE(@province, ””, ”””)
    + ‘ AND ctv_name = ‘ + REPLACE(@city, ””, ”””)
    + ‘ AND sic_code = ‘ + REPLACE(@xyz_code, ””, ”””);

    The confusion here is that you are using dynamic SQL. If you use parameters instead, all these problems go away.

    Obviously, you want dynamic SQL because of the TABLE which is dynamic. But is that really what you want? Why not have one Listing TABLE with country as a COLUMN inside it?

  11. We have a DataSet with Table Adapters built for MSAccess using OLEDB. We are using Visual Studio 2005 and .Net 2.0.

    When the designer creates insert, update and delete queries it encloses the table name and field names with a ` (ASCII char 96).
    For example:

    INSERT INTO `table` (`col1`, `col2`, `col3`) values (….)

    Is there a reason for this character? Assuming it’s for escape purposes, is there a setting to make Visual Studio use another character, eg “[" or "]“?

  12. Our main goal is to use the same SQL queries (basic inserts, updates, deletes) in both MS Access and SQL Server.
    Our app was originally written for Access using the VS designer to generate the queries.

    After testing these queries, we found that if the ` char was removed and replaced by [ ] brackets, then all the queries would work in both db’s.

  13. Following dbcc statement work fine from QA, but it failing using in a sql job.

    Please help…..

    Use “EPO_USLAX-EPO01″
    go
    DBCC SHRINKFILE (“ePO_USLAX-EPO01″, 20)
    GO
    DBCC SHRINKFILE (“ePO_USLAX-EPO01_log”, 10)
    go

  14. @rana

    Two Options.

    1. Create a procedure and execute that procedure in the job.
    USE [EPO_USLAX-EPO01]
    Create Proc [USp_ShrinkDB]
    AS
    DBCC SHRINKFILE ( ‘ePO_USLAX-EPO01′, 20)
    DBCC SHRINKFILE (‘ePO_USLAX-EPO01_log’, 10)
    go

    put this script in the job.
    Exec [EPO_USLAX-EPO01]..[USp_ShrinkDB]

    2. What if you do this in two different steps of the same job, Something like this,

    Create a job,
    First Step in that job is to only shrink one file
    USE [EPO_USLAX-EPO01]
    DBCC SHRINKFILE ( ‘ePO_USLAX-EPO01′, 20)

    Second step in that job will be to shrink second file.
    USE [EPO_USLAX-EPO01]
    DBCC SHRINKFILE (‘ePO_USLAX-EPO01_log’, 10)

    ~IM.

  15. Edited for some typo

    @tableName varchar(100),
    @Code nvarchar(10),
    @label nvarchar(max),
    @Lang nchar(2),
    @Reg nchar(2),
    @Fran nchar(3)

    SET @query = ‘INSERT INTO ‘ + @tableName
    + ‘ (Code,label,Lang,Reg,Fran) VALUES’
    +’ (”’+ @Code +”’,”’ @label +”’,”’+@Lang+”’,”’+@Reg+”’,”’+@Fran+”’)’

    execute(@query)

    this query will not work when i have label like this
    A00 ”B & Y – “NIPO””

    how to fix this

    • Just go with brian advice, you can either replace or remove quotes and it will work. If any one have any thing better please put comment.

      –remove single quote
      set @label = REPLACE(@label ,””,”)

      –replace single quote with doule quote
      set @label = REPLACE(@label, ””, ‘”‘);

      Thanks

  16. I need to get the error resolved for the code below

    ————–error————————–

    USE AVME
    Declare @SQL nvarchar(MAX)
    Declare @docpath nvarchar(MAX)
    Set @docpath = N’E:\Workflow\SPP\Attachments\ModelCodes\claim.xls’
    Set @SQL = ‘INSERT INTO [Tactical.Claim_LineItems_BULK_Audi] ([JObCreator], [ModelCode],
    [ChassisNo], [CommissionNo], [AGSupport],[AVMESupport], [Brand], [Remark])
    SELECT *
    FROM OPENROWSET (”Microsoft.ACE.OLEDB.12.0”, ”Excel 12.0;;Database=”’+@docpath+”’,
    ”select * from [Sheet1$]”)’
    PRINT @SQL
    EXEC(@SQL)

    ————error———————————————-

    INSERT INTO [Tactical.Claim_LineItems_BULK_Audi] ([JObCreator], [ModelCode],
    [ChassisNo], [CommissionNo], [AGSupport],[AVMESupport], [Brand], [Remark])
    SELECT *
    FROM OPENROWSET (‘Microsoft.ACE.OLEDB.12.0′, ‘Excel 12.0;;Database=’E:\Workflow\SPP\Attachments\ModelCodes\claim.xls’,
    ‘select * from [Sheet1$]‘)
    Msg 102, Level 15, State 1, Line 4
    Incorrect syntax near ‘E’.
    Msg 105, Level 15, State 1, Line 5
    Unclosed quotation mark after the character string ‘)’.

  17. Hi,

    I have a new variation on this problem. I have a varchar() field called Notes where users can enter anything they want. If they use a single quote for a grammatical contraction, then it causes an error when I try to pass the string in an UPDATE statement. So, for intance:

    New account, didn’t ask for conversion.

    causes an “Unclosed quotation mark after the character string” error.

    I noticed that when I BULK imported the table into SQLServer, it wrapped all these text strings in double quotes:

    “New account, didn’t ask for conversion.”

    but that doesn’t seem to fix the problem. When I try to pass that text string in an UPDATE statement I still get the error.

    Is there a way to wrap up these text strings so they automatically get escaped by SQLServer? I understand the syntax for escaping them when I’m writing the whole SQL statement, but I can’t ask my users to avoid single quotes in their comments.

    Thanks,
    Bill J.

  18. Hi Pinal,
    My question is whether sql server 2008 has taken care of apostrophe problem. I am not facing any problem of apostrophe in my code when i insert data in table from UI. I am using SQL server 2008 and stored procedure to insert data from UI to database. I tried to find but not able to find any blog where it is mentioned so still in doubt how SP is adding data with apostrophe in table. I want to make myself double sure.

  19. This does not always work…Here is an example:

    drop table dirresults
    CREATE TABLE DirResults (Diroutput VARCHAR(500))
    INSERT DirResults
    exec master..xp_cmdshell ‘osql -S127.0.0.1,1450 -T -Q “exec sp_executesql N”select * from pubs..authors where state = ”a”””‘

  20. Hi Gurus,

    I hope here I am going to get my answer.

    The code is working from SQL Server 2000 to 2008.
    However the application is crash in 2008R2.

    Here is the code:

    CREATE TABLE abc (
    col1 varchar (7000) NOT NULL,
    PRIMARY KEY (col1))

    INSERT INTO abc ( col1 )
    VALUES( ‘a’ ) // Different Values in a loop

    Check the SQLDB CODE If It is Duplicate Then
    Do Some Calculation
    ELSE
    Loop

    In 2008R2 as soon as it finds a duplicate it crashed.

  21. I am using a fully loaded sqlite3 database where embedded single and double quotes are included in many of the varchar fields. What select query could i write to simply query the database without things breaking?

    for example
    where col2 is know to contain embedded single and double quotes
    Select col1, col2 from table_Name;

  22. ALTER PROCEDURE [dbo].[DBSP_SF_GetAccountsLikeAccNo]
    @AccountNo VARCHAR(20),
    @BusinessName VARCHAR(40),
    @Type VARCHAR(2)
    AS
    BEGIN
    DECLARE @str VARCHAR(100)
    SELECT @str=REPLACE(@BusinessName,””,”)

    IF @Type = ‘AN’
    BEGIN
    SELECT AccountNo, BusinessName FROM CustomerDetails WITH (NOLOCK) WHERE AccountNo like @AccountNo + ‘%’
    END
    ELSE IF @Type = ‘BN’
    BEGIN
    SELECT AccountNo, BusinessName FROM CustomerDetails WITH (NOLOCK) WHERE BusinessName like @str + ‘%’

    END
    END
    if the value is Andersons The Grocery Store .in this I did not get any problem. But, ‘Anderson’s “The Grocery Store”‘ I am getting error like Unclosed quotation mark after the character string ‘). Can you please help out me by giving proper code (how to replace two single quotes in place of one single quote).
    plz helpme any modify my SP

  23. Hi Pinal,

    I am developing a .net application and as part of this, i have a stored procedure that gets me the data with required format. This stored procedure has dynamic sql in it where the formatting of the data takes place.

    Below is the query that i am using to format any data based on its column datatype. This query just does the following, I have used master DB for this example,

    1. @query variable is formed with a sql query where this query has a case statement in which column name is passed to scalar function(udfSelDataTypeOfColumn) and datatype is got from it.
    2. If this datatype is of CHAR or NCHAR or NVARCHAR or VARCHAR then i want the output of the column value enclosed in a single quote.
    Suppose, the output value is a varchar with value as Pinal then this query has to return ‘Pinal’ else for other datatypes same value can be returned like (int)1234, (bit)1
    I tried formatting with single quotes and concatinating but no luck.

    Please let me know a solution to this problem and explain me how single quotes intepretation is done by sql runtime.

    USE master

    DECLARE @query NVARCHAR(MAX),
    @charDataType NVARCHAR(20)

    SELECT @charDataType=”’%char%”’
    select @query=’SELECT TOP 10
    CASE WHEN dbo.udfSelDataTypeOfColumn(”name”) like ‘+@charDataType+ ‘then ”” +name+ ”” else ”name” end
    FROM spt_values WITH(NOLOCK)’
    EXEC sp_executesql @query

  24. I have getting same problem in SQL string kindly help me out about this error my sring is
    “INSERT INTO LoginMaster(UserName, Password, DistrictID, AgencyID, Address, BlockID, PanchayatID, UserType, ContactNo, EmailAddress, SQuestion, SAnswer, Status) VALUES (‘” + UserName.Text + “‘, ‘” & Password.Text & “‘,'” & district.SelectedValue & “‘,'” & agency.SelectedValue & “‘,'” & AgencyAddress.Text & “‘,’0′,’0′,'” & UserType.SelectedValue & “‘,'” & contactno.Text & “‘,'” & emailaddress.Text & “‘ ,'” & SQuestion.Text & “‘,'” & SAnswer.Text & “‘,’1′)”

    plz help me

  25. I am creating a SP the exports DB responses to Excel file, excel file code is working fine, I am using Dynamic SQL, when i am appending SP parameter variable inside the dynamic query i am facing issues of ‘ (quotes) i think .The SP parameter is stored in @ForYear variable. when i am hard coding the @ForYear with say 2012 the query is executing without error. please help.

    –WHEN I EXECUTE THE SP

    exec Proc_ExportEntireYearlyResponsesToExcel 2012

    –THE ERROR IS

    Unclosed quotation mark after the character string ‘)SELECT * FROM @PVTable’.

    ALTER PROCEDURE [dbo].[Proc_ExportEntireYearlyResponsesToExcel]
    — Add the parameters for the stored procedure here
    @ForYear Varchar(250)
    AS
    BEGIN

    EXEC sp_configure ‘show advanced options’, 1; RECONFIGURE
    EXEC sp_configure ‘Ad Hoc Distributed Queries’, 1; RECONFIGURE

    Declare @Var1 as nvarchar(MAX)
    set @Var1=””+ ‘Microsoft.ACE.OLEDB.12.0′ +”” +’,’
    Declare @Var2 as nvarchar(MAX)
    set @Var2=””+’Excel 12.0;Database=C:\destination\EntireYearlyMESRespondentResponses.xlsx;’+”” +’,’
    Declare @Var3 as nvarchar(MAX)
    set @Var3=””+’SELECT * FROM [Sheet2$]‘+””
    declare @Var4 as nvarchar(max)
    set @Var4=@Var1+@Var2+@Var3
    PRINT ‘OLEDB for Office 2007 –> ‘+@Var4 — Correctly printed [OLEDB for Office 2007 –> ‘Microsoft.ACE.OLEDB.12.0′,’Excel 12.0;Database=C:\destination\EntireYearlyMESRespondentResponses.xlsx;’,’SELECT * FROM [Sheet2$]‘]

    Declare @ResponsesQuery varchar(MAX)
    set @ResponsesQuery=
    (‘
    declare @QCol table
    (
    ColumnId int identity(1,1),
    QuestionIdentifierID int
    )

    insert into
    @QCol (QuestionIdentifierID)
    select
    QuestionIdentifierID
    from
    dbo.QuestionIdentifier as qi with(nolock) left join
    dbo.Question as q with(nolock) on qi.QuestionID = q.QuestionID
    where
    q.SurveyID = 1

    declare @PVTable table
    (
    RespondentID int,
    IndustryName varchar(250),
    BusinessSectorName varchar(250),
    CompanyName varchar(250),
    OpenDateTime varchar(250),
    [1] varchar(250),
    [2] varchar(250),
    [3] varchar(250),
    [4] varchar(250),
    [5] varchar(250),
    [6] varchar(250)
    )

    insert into @PVTable
    select
    RespondentID,
    IndustryName,
    BusinessSectorName,
    CompanyName,
    CONVERT(CHAR(4),OpenDateTime,120) as OpenDateTime,
    [1] as Q18_1,
    [2] as Q18_2,
    [3] as Q18_3,
    [4] as Q12_1,
    [5] as Q12_2,
    [6] as Q36_1

    from
    (
    select
    qt1.RespondentID,
    qt1.IndustryName,
    qt1.BusinessSectorName,
    qt1.CompanyName,
    qt1.OpenDateTime

    from
    @QCol as qc join
    (
    select distinct
    rt.RespondentID,
    i.[Name] as IndustryName,
    bs.[Name] as BusinessSectorName,
    cmp.[Name] as CompanyName,
    sc.OpenDateTime,
    from
    dbo.Table1 rt left join
    dbo.Table2 r on rt.RespondentID = r.RespondentID left join
    .
    .
    .
    .

    where
    c.ChoiceListID is null and
    CONVERT(CHAR(4), sc.OpenDateTime, 120)=’+@ForYear+’
    ) qt1 on qc.QuestionIdentifierID = qt1.QuestionIdentifierId

    union
    select
    qt2.RespondentID,
    qt2.IndustryName,
    qt2.BusinessSectorName,
    qt2.CompanyName,
    qt2.OpenDateTime,

    from
    @QCol as qc join
    (
    select distinct
    t1.RespondentID,
    t1.IndustryName,
    t1.BusinessSectorName,
    t1.CompanyName,
    t1.OpenDateTime,
    from
    dbo.Choice c join
    (
    select
    rt.RespondentID,
    i.[Name] as IndustryName,
    bs.[Name] as BusinessSectorName,
    cmp.[Name] as CompanyName,
    sc.OpenDateTime,
    from
    dbo.Table1 rt left join
    dbo.Table2 r on rt.RespondentID = r.RespondentID left join
    .
    .
    .
    .

    where

    CONVERT(CHAR(4), sc.OpenDateTime, 120)=’+@ForYear+’

    ) t1 on c.ChoiceListID = t1.ChoiceListID and t1.AnswerValue = cast(c.[Value] as varchar)
    ) qt2 on qc.QuestionIdentifierID = qt2.QuestionIdentifierID
    ) t2

    pivot
    (
    max([AnswerValue])
    for QuestionIdentifierID in
    ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31],[32],[33],[34],[35],[36],[37],[38],[39],[40],[41],[42],[43],[44],[45],[46],[47],[48],[49],[50],[51],[52],[53],[54],[55],[56],[57],[58],[59],[60],[61],[62],[63],[64],[65],[66],[67],[68],[69],[70],[71],[72],[73],[74],[75],[76],[77],[78],[79],[80],[81],[82],[83],[84],[85],[86],[87],[88],[89],[90],[91],[92],[93],[94],[95],[96],[97],[98],[99],[100],[101],[102],[103],[104],[105])
    ) as pvt
    order by RespondentID;

    INSERT INTO OPENROWSET (‘+@Var4+’) SELECT * FROM @PVTable
    ‘)

    –FILE MANUPLATION CODE HERE and it works fine
    EXEC Master.dbo.xp_fileexist @FileToBeDeletedOnExistance, @File_Exists OUT
    IF @File_Exists = 1
    begin
    execute (@ResponsesQuery) — error is here
    END
    ELSE
    begin
    — any other query
    END

    –WHEN I EXECUTE THE SP

    exec Proc_ExportEntireYearlyResponsesToExcel 2012

    –THE ERROR IS

    Unclosed quotation mark after the character string ‘)SELECT * FROM @PVTable’.

  26. Hello Friends, I am using a SP to export yearly Responses to an excel file by providing year to an SP, the @ForYear variable captures the parameter. Excel file code and other stuff is working fine when i am hard coding the @ForYear as 2012 in the dynamic SQL. I am using dynamic SQL and inserting the SQL parameter (@ForYear) inside it and then executing the Dynamic SQL based of file exist logic

    –WHEN I EXECUTE THE SP

    exec Proc_ExportEntireYearlyResponsesToExcel 2012

    –THE ERROR IS

    Unclosed quotation mark after the character string ‘)SELECT * FROM @PVTable’.

    AGAIN WHEN I HARDCODE THE @ForYear as 2012 or 2011 data is coming in the excel file and there is no error

    PLEASE HELP ITS AN EOD TASK

    –Query starts here

    ALTER PROCEDURE [dbo].[Proc_ExportEntireYearlyResponsesToExcel]
    — Add the parameters for the stored procedure here
    @ForYear Varchar(250)
    AS
    BEGIN

    EXEC sp_configure ‘show advanced options’, 1; RECONFIGURE
    EXEC sp_configure ‘Ad Hoc Distributed Queries’, 1; RECONFIGURE

    Declare @Var1 as nvarchar(MAX)
    set @Var1=””+ ‘Microsoft.ACE.OLEDB.12.0′ +”” +’,’
    Declare @Var2 as nvarchar(MAX)
    set @Var2=””+’Excel 12.0;Database=C:\destination\EntireYearlyMESRespondentResponses.xlsx;’+”” +’,’
    Declare @Var3 as nvarchar(MAX)
    set @Var3=””+’SELECT * FROM [Sheet2$]‘+””
    declare @Var4 as nvarchar(max)
    set @Var4=@Var1+@Var2+@Var3
    PRINT ‘OLEDB for Office 2007 –> ‘+@Var4 — Correctly printed [OLEDB for Office 2007 –> ‘Microsoft.ACE.OLEDB.12.0′,’Excel 12.0;Database=C:\destination\EntireYearlyMESRespondentResponses.xlsx;’,’SELECT * FROM [Sheet2$]‘]

    Declare @ResponsesQuery varchar(MAX)
    set @ResponsesQuery=
    (‘
    declare @QCol table
    (
    ColumnId int identity(1,1),
    QuestionIdentifierID int
    )

    insert into
    @QCol (QuestionIdentifierID)
    select
    QuestionIdentifierID
    from
    dbo.QuestionIdentifier as qi with(nolock) left join
    dbo.Question as q with(nolock) on qi.QuestionID = q.QuestionID
    where
    q.SurveyID = 1

    declare @PVTable table
    (
    RespondentID int,
    IndustryName varchar(250),
    BusinessSectorName varchar(250),
    CompanyName varchar(250),
    OpenDateTime varchar(250),
    [1] varchar(250),
    [2] varchar(250),
    [3] varchar(250),
    [4] varchar(250),
    [5] varchar(250),
    [6] varchar(250)
    )

    insert into @PVTable
    select
    RespondentID,
    IndustryName,
    BusinessSectorName,
    CompanyName,
    CONVERT(CHAR(4),OpenDateTime,120) as OpenDateTime,
    [1] as Q18_1,
    [2] as Q18_2,
    [3] as Q18_3,
    [4] as Q12_1,
    [5] as Q12_2,
    [6] as Q36_1

    from
    (
    select
    qt1.RespondentID,
    qt1.IndustryName,
    qt1.BusinessSectorName,
    qt1.CompanyName,
    qt1.OpenDateTime

    from
    @QCol as qc join
    (
    select distinct
    rt.RespondentID,
    i.[Name] as IndustryName,
    bs.[Name] as BusinessSectorName,
    cmp.[Name] as CompanyName,
    sc.OpenDateTime,
    from
    dbo.Table1 rt left join
    dbo.Table2 r on rt.RespondentID = r.RespondentID left join
    .
    .
    .
    .

    where
    c.ChoiceListID is null and
    CONVERT(CHAR(4), sc.OpenDateTime, 120)=’+@ForYear+’
    ) qt1 on qc.QuestionIdentifierID = qt1.QuestionIdentifierId

    union
    select
    qt2.RespondentID,
    qt2.IndustryName,
    qt2.BusinessSectorName,
    qt2.CompanyName,
    qt2.OpenDateTime,

    from
    @QCol as qc join
    (
    select distinct
    t1.RespondentID,
    t1.IndustryName,
    t1.BusinessSectorName,
    t1.CompanyName,
    t1.OpenDateTime,
    from
    dbo.Choice c join
    (
    select
    rt.RespondentID,
    i.[Name] as IndustryName,
    bs.[Name] as BusinessSectorName,
    cmp.[Name] as CompanyName,
    sc.OpenDateTime,
    from
    dbo.Table1 rt left join
    dbo.Table2 r on rt.RespondentID = r.RespondentID left join
    .
    .
    .
    .

    where

    CONVERT(CHAR(4), sc.OpenDateTime, 120)=’+@ForYear+’

    ) t1 on c.ChoiceListID = t1.ChoiceListID and t1.AnswerValue = cast(c.[Value] as varchar)
    ) qt2 on qc.QuestionIdentifierID = qt2.QuestionIdentifierID
    ) t2

    pivot
    (
    max([AnswerValue])
    for QuestionIdentifierID in
    ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31],[32],[33],[34],[35],[36],[37],[38],[39],[40],[41],[42],[43],[44],[45],[46],[47],[48],[49],[50],[51],[52],[53],[54],[55],[56],[57],[58],[59],[60],[61],[62],[63],[64],[65],[66],[67],[68],[69],[70],[71],[72],[73],[74],[75],[76],[77],[78],[79],[80],[81],[82],[83],[84],[85],[86],[87],[88],[89],[90],[91],[92],[93],[94],[95],[96],[97],[98],[99],[100],[101],[102],[103],[104],[105])
    ) as pvt
    order by RespondentID;

    INSERT INTO OPENROWSET (‘+@Var4+’)SELECT * FROM @PVTable

    ‘)
    –FILE MANUPLATION CODE HERE and it works fine
    EXEC Master.dbo.xp_fileexist @FileToBeDeletedOnExistance, @File_Exists OUT
    IF @File_Exists = 1
    begin
    execute (@ResponsesQuery) — error is here
    END
    ELSE
    begin
    — any other query
    END

    –Query ends here

    –WHEN I EXECUTE THE SP

    exec Proc_ExportEntireYearlyResponsesToExcel 2012

    –THE ERROR IS

    Unclosed quotation mark after the character string ‘)SELECT * FROM @PVTable’.

  27. Hi pinal dev

    i have a question like this…..

    i have a string say s=’kumar’ and i want to insert ‘UMAR’ only into my data base pls give me a query.

    • Kumar, What is the final objective
      Are you trying to remove the first letter of string and present the rest
      OR
      Are you trying to get the last four letters from the string.
      Please elaborate.
      bhattji

  28. I have a table fields which contains characters like ‘s and because of SQL limitation I am getting errors in application. Such characters sequence stored because of laps during conversion time. Can any one help me to replace escape ‘ characters with other one…

    Thanks…

    Devang

  29. Dear Sir,

    I am trying to execute a batch statement which includes ‘Database, Tables, Stored Procedures Creation etc.’

    It is a very big sql string. Here it would be difficult to find every single quote and replace it with two single quotes…

    Isn’t there any option at the start of the string only ( like ‘@’ in C#) so that I can keep my whole string as it is. It will save a lot of time.

    Please help me to resole this.

    Thank You.

    Amol

  30. Dear Sir,

    If I have a batch statement containing “Database, Tables, Stored Procedures” creation, then there’s gonna be thousands of lines containing single quote. It would be difficult to find each one and replacing with two singles.

    Isn’t there a way like what @ operator doses in C# ?

    Please help me to resolve this issue.

    Thank You

    Amol

  31. Hmm. Doubling up single quotes stops me getting the “unclosed quotation” error, however it’s not pattern matching the data in the way I would hope. Searching on “Connor” brings back many results (which are displayed as “O’Connor”) whereas searching on “O’Connor” brings back none.

    this is where my php code adds the relevant line to the query…
    $querystr .= “AND UPPER(Authors) COLLATE SQL_Latin1_General_Cp1_CI_AS LIKE ‘%”.strtoupper(ereg_replace(“‘”,”””,$Authors)).”%’ “;
    .. which once built, is then run against the mssql DB.
    Any & all ideas welcome…!

    • OK, I’ve found a fairly inelegant solution… there was an escaping “\” in there, so my search string was “O\”Connor”. Have now changed the code to:
      $querystr .= “AND UPPER(Authors) COLLATE SQL_Latin1_General_Cp1_CI_AS LIKE ‘%”.strtoupper(ereg_replace(“‘”,”_”,stripslashes($Authors))).”%’ “;

      …and this appears to work – the underscore allowing for the apostrophe, and the % working for…everything else.

      Thanks for providing me a forum to think through this in public ;-)

  32. hi guys plz help me in making a function in T-sql through which i can change the string like
    Double Quto(“) into single quto(‘) by using loop…
    like Microsoft”s into Microsoft’s
    Microsoft”” into Microsoft’s
    and Microsoft’s shuld remain Microsoft’s

  33. Hello Sir i am using a query in which i have generated a string from Comma separate and when i use it in “select id from tblName where id in(@str) ” here id is integer and i have @str as nvarchar its not giving result as it should.. please help me … Thanks in advance…….

  34. i need sql insert stament without stored procedure to insert dynamic values from textbox which contains of (Apostrophe,Double quotes)! Simple example plz

  35. can any one tell me ,what to do when we are getting data from web server with @ ‘ @ ,i mean single quote.i have to load that data in my sql database.please tell the solution.
    Regards
    Ajay Kohar

  36. Pingback: SQL SERVER – Weekly Series – Memory Lane – #017 | SQL Server Journey with SQL Authority

  37. In order to be in compliance with strict security requirements as a DBA on a
    military installation, I am required to run some queries against every
    database on ever server – too many reasons and requirements to explain here.
    I taken the auditors queries in the requirement pdf and modified code to get
    the information in one procedure. It works PERFECT on all servers except the
    one that has Sharepoint DB’s. Whoever initially set up Sharepoint evidently
    used the wizard because the naming convention has a GUID which includes
    three dashes:
    SharePoint_AdminContent_2e102d4f-2187-469d-8528-5bbd08c1a49b

    When my code runs through SELECT name FROM [master].sys.databases, it grabs
    everything up to the 1st dash, then give me an error:
    Msg 911, Level 16, State 1, Line 6 Could not locate entry in sysdatabases
    for database ‘SharePoint_AdminContent_2e102d4f’. No entry found with that
    name. Make sure that the name is entered correctly.

    I have tried everything from concantenating with brackets and excluding that
    DB in the query by various means, but nothing works. I know there is a
    process to change the Sharepoint DB name, but I do not have authority to do
    this, so I need to find a work-around in code in order to not have to run
    the canned queries on around 100 DB’s on this server.

    Here is the code:

    create table #hd_rc ( ServerName NVARCHAR(100), DBName VARCHAR(100),
    [Schema] VARCHAR(100),type_desc VARCHAR(100), [User] VARCHAR(100) ) DECLARE
    @command varchar(1000)

    SELECT @command = ‘IF EXISTS (
    SELECT name FROM [master].sys.databases WHERE state = 0 and name = ”?” )
    BEGIN USE ? EXEC(” INSERT INTO #hd_rc
    SELECT distinct @@servername as ServerName, db_name() as DBName, s.name AS
    [Schema], u.type_desc, l.name AS [User] FROM sys.all_objects o JOIN
    sys.schemas s ON o.schema_id = s.schema_id JOIN sys.database_principals u ON
    s.principal_id = u.principal_id JOIN master.sys.sql_logins l ON u.sid =
    l.sid WHERE o.is_ms_shipped = 0 AND l.is_disabled = 0;
    ”)
    END’ EXEC sp_MSforeachdb @command

    select * from #hd_rc drop table #hd_rc

    Thank you in advance for any assistance!! Mdh

  38. You are a legend, this is an awesome fix, I love how google drives me onto your site every time I have an sql server question. Where do I donate?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s