Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Location
    Australia
    Posts
    46

    Question Unanswered: OpenQuery Error??

    Hi All,

    I have an Openquery insert within a trigger. When i go to check the syntax it errors with the following message...


    Error 403. Invalid Error for Data Type. Operator equals add, type equals varchar.

    Below is my Openquery statement

    SET @TSQL2 = 'INSERT INTO ' +
    'OPENQUERY([TRILOGY-TSG],''Select DET_NUMBERA, ADR_TYPEA, ADR_LINE_1A, ADR_LINE_2A, ADR_LINE_3A, ADR_LINE_4A, ADR_PST_CODEA, ADR_AREA_CODA, ADR_PHONEA, ADR_CNT_SURA, ' +
    'ADR_CNT_NAMEA, ADR_CNT_RELA, FILLER_01A, ADR_STATEA, ADR_MOBILEA, FILLER_02A, SRGTE_KEY_1 FROM CHRISCS.EMADR'') ' +
    'VALUES(''' + @EMPLOYEE_NO + ''', ''E'', ''' + @ADDRESS1 + ''', ''' + @ADDRESS2 + ''', ''' + @SUBURB + ''', ''' + @COUNTRY + ''', ''' + @POSTCODE + ''', ' +
    '''' + @AREACODE + ''', ''' + @WORK1 + ''', ''' + @ECD_SURNAME + ''', ''' + @ECD_FIRSTNAME + ''', ''' + @ECD_RELATIONSHIP + ''', '''', ''' + @STATE + ''', ''' + @MOBILE + ''', ' +
    ''' IT :21105101017 '', ''' + @KEYE + ''')'


    All variables are varchar except for @KEYE which is Varbinary and this is the one that is causing the error on, because if I take it out the syntax is correct.

    Any ideas why this occurs and how do I add a varbinary variable to the statement??

    Regards
    Anthony

  2. #2
    Join Date
    Feb 2003
    Location
    Germany
    Posts
    53

    Re: OpenQuery Error??

    you are trying to concat string with varbin - this will not work. you have to convert to varchar before...

  3. #3
    Join Date
    Jan 2003
    Location
    Australia
    Posts
    46

    Re: OpenQuery Error??

    Originally posted by msieben
    you are trying to concat string with varbin - this will not work. you have to convert to varchar before...

    How do I do this?? Can you show me an example.

  4. #4
    Join Date
    Feb 2003
    Location
    Germany
    Posts
    53

    Re: OpenQuery Error??

    Originally posted by aljubicic
    :
    Below is my Openquery statement

    SET @TSQL2 = 'INSERT INTO ' +
    'OPENQUERY([TRILOGY-TSG],''Select DET_NUMBERA, ADR_TYPEA, ADR_LINE_1A, ADR_LINE_2A, ADR_LINE_3A, ADR_LINE_4A, ADR_PST_CODEA, ADR_AREA_CODA, ADR_PHONEA, ADR_CNT_SURA, ' +
    'ADR_CNT_NAMEA, ADR_CNT_RELA, FILLER_01A, ADR_STATEA, ADR_MOBILEA, FILLER_02A, SRGTE_KEY_1 FROM CHRISCS.EMADR'') ' +
    'VALUES(''' + @EMPLOYEE_NO + ''', ''E'', ''' + @ADDRESS1 + ''', ''' + @ADDRESS2 + ''', ''' + @SUBURB + ''', ''' + @COUNTRY + ''', ''' + @POSTCODE + ''', ' +
    '''' + @AREACODE + ''', ''' + @WORK1 + ''', ''' + @ECD_SURNAME + ''', ''' + @ECD_FIRSTNAME + ''', ''' + @ECD_RELATIONSHIP + ''', '''', ''' + @STATE + ''', ''' + @MOBILE + ''', ' +
    ''' IT :21105101017 '', ''' + @KEYE + ''')'
    :
    the code you posted will build the insert statement togeter and put it into @TSQL2 as a varchar. so everything you put toghether needs to be varchar or to be converted to (either by the sql-server or by using "convert(". i don't know what you really want to do - but look alt sp_executesql in BOL. you can store your statement into varchar and use parameters, which will be replaced at runtime. so you don't have to push your values list into the varchar at all.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •