Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am not able to successfully run a code with openquery to a oracle server.

Please do not take into account field names/data types, as I had to present only a part of the whole procedure. It's quite long. I believe the problem lies in quotation marks, etc...

The procedure compiles all right. Each time I execute the procedure, an error occurs:

Msg 105, Level 15, State 1, Line 53
Unclosed quotation mark after the character string 'SELECT TO_NUMBER(XYZ_1) XYZ_1,
XYZ_2,

cast (''''0'''' as number(5)) as B1, 
cast(''''1753-01-01'''' as date) NULL_DATE

I am lost - where the heck should I put those missing quotation mark?

CREATE TABLE #tmpXYZ Header (
    [XYZ_1] [int] PRIMARY KEY,
    [XYZ_2] [varchar](20),
    [XYZ_3] [varchar](20),
        -- more fields  
    [XYZ_N] [varchar](50)
 )

declare @sqlInv nvarchar(3000)
 set @sqlInv = 

        'insert into #tmpXYZ Header (
        [XYZ_1],
        [XYZ_2],
        [XYZ_3],
        -- more fields
        [XYZ_N]

    )

    select
        * FROM OPENQUERY(XYZ_ORACLE, 
        ''SELECT TO_NUMBER(XYZ_1) XYZ_1,
        XYZ_2,

        cast (''''0'''' as number(5)) as B1, 
        cast(''''1753-01-01'''' as date) NULL_DATE,
        -- more fields
        cast ('''' '''' as varchar(20)) as A19 

    from XYZ.V_HEADER 
    where

        (DATE >= ''''TO_DATE(''''' + @startDate + ''''', ''''YYYYMMDD'''')'''' AND 
        DATE <= ''''TO_DATE(''''' + @endDate + ''''', ''''YYYYMMDD'''')'''' AND 
        QWE = ''''0'''' AND
        ABC = ''''13'''' ) '' )
    ' 

    exec sp_executesql @sqlInv
share

migration rejected from serverfault.com Jul 11 '13 at 7:54

This question came from our site for professional system and network administrators. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as off-topic by Paul White, Mat, Mark Storey-Smith, StanleyJohns, dezso Jul 11 '13 at 7:54

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Paul White, Mat, Mark Storey-Smith, StanleyJohns, dezso
If this question can be reworded to fit the rules in the help center, please edit the question.

    
cast (''''0'''' as number(5)) as B1 seems useless. Just write 0 as B1 (or cast(0 as number(5)) as B1). And you don't need to enclose function names (to_date() in single quotes, only string literals. –  a_horse_with_no_name Mar 12 '13 at 8:49

1 Answer 1

I think that your escaped quotes are too deep when you get to

cast (''''0'''' as number(5)) as B1, 

My guess is it would be

cast ('''0''' as number(5)) as B1, 

Etc for the portion in the quoted select statement string.

I think, however the portions that would be right in that would be

TO_DATE(''''' + @startDate + '''''

I am a little confused on the quotes surrounding the to_date calls, as to why you have those.

My guess in total, assuming the to_date surrounding quotes are not wrong:

CREATE TABLE #tmpXYZ Header (
    [XYZ_1] [int] PRIMARY KEY,
    [XYZ_2] [varchar](20),
    [XYZ_3] [varchar](20),
        -- more fields  
    [XYZ_N] [varchar](50)
 )

declare @sqlInv nvarchar(3000)
 set @sqlInv = 

        'insert into #tmpXYZ Header (
        [XYZ_1],
        [XYZ_2],
        [XYZ_3],
        -- more fields
        [XYZ_N]

    )

    select
        * FROM OPENQUERY(XYZ_ORACLE, 
        ''SELECT TO_NUMBER(XYZ_1) XYZ_1,
        XYZ_2,

        cast ('''0''' as number(5)) as B1, 
        cast('''1753-01-01''' as date) NULL_DATE,
        -- more fields
        cast (''' ''' as varchar(20)) as A19 

    from XYZ.V_HEADER 
    where

        (DATE >= '''TO_DATE(''''' + @startDate + ''''', '''YYYYMMDD''')''' AND 
        DATE <= '''TO_DATE(''''' + @endDate + ''''', '''YYYYMMDD''')''' AND 
        QWE = '''0''' AND
        ABC = '''13''' ) '' )
    ' 

    exec sp_executesql @sqlInv
share