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
Anthonyyou are trying to concat string with varbin - this will not work. you have to convert to varchar before...|||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.|||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.