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.

Only administrators can connect

We have been having a problem recently where all of a sudden sql starts
denying connections. The errors in the sql log say "login failed for
<user>. only administrators may connect at this time". I've been unable to
find any documentation on the net for what exactly this is and I can't seem
to figure out what's causing it. Any suggestions?Did you by any chance start the server with -m? If so, remove -m will allow
non administrator to connect.
--
Gang He
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brent Mills" <*bcm*@.hotmail.com> wrote in message
news:u7MycX#$DHA.1224@.TK2MSFTNGP11.phx.gbl...
> We have been having a problem recently where all of a sudden sql starts
> denying connections. The errors in the sql log say "login failed for
> <user>. only administrators may connect at this time". I've been unable
to
> find any documentation on the net for what exactly this is and I can't
seem
> to figure out what's causing it. Any suggestions?
>|||No, that's the weird thing. It works fine for about a week or two and then
all of a sudden it just starts denying connections.
"Gang He [MSFT]" <ganghe@.online.microsoft.com> wrote in message
news:OPZBCl$$DHA.1452@.TK2MSFTNGP09.phx.gbl...
> Did you by any chance start the server with -m? If so, remove -m will
allow
> non administrator to connect.
> --
> Gang He
> SQL Server Storage Engine Development
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Brent Mills" <*bcm*@.hotmail.com> wrote in message
> news:u7MycX#$DHA.1224@.TK2MSFTNGP11.phx.gbl...
> > We have been having a problem recently where all of a sudden sql starts
> > denying connections. The errors in the sql log say "login failed for
> > <user>. only administrators may connect at this time". I've been
unable
> to
> > find any documentation on the net for what exactly this is and I can't
> seem
> > to figure out what's causing it. Any suggestions?
> >
> >
>

Only administrators can connect

We have been having a problem recently where all of a sudden sql starts
denying connections. The errors in the sql log say "login failed for
<user>. only administrators may connect at this time". I've been unable to
find any documentation on the net for what exactly this is and I can't seem
to figure out what's causing it. Any suggestions?Did you by any chance start the server with -m? If so, remove -m will allow
non administrator to connect.
Gang He
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brent Mills" <*bcm*@.hotmail.com> wrote in message
news:u7MycX#$DHA.1224@.TK2MSFTNGP11.phx.gbl...
> We have been having a problem recently where all of a sudden sql starts
> denying connections. The errors in the sql log say "login failed for
> <user>. only administrators may connect at this time". I've been unable
to
> find any documentation on the net for what exactly this is and I can't
seem
> to figure out what's causing it. Any suggestions?
>|||No, that's the weird thing. It works fine for about a week or two and then
all of a sudden it just starts denying connections.
"Gang He [MSFT]" <ganghe@.online.microsoft.com> wrote in message
news:OPZBCl$$DHA.1452@.TK2MSFTNGP09.phx.gbl...
> Did you by any chance start the server with -m? If so, remove -m will
allow
> non administrator to connect.
> --
> Gang He
> SQL Server Storage Engine Development
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Brent Mills" <*bcm*@.hotmail.com> wrote in message
> news:u7MycX#$DHA.1224@.TK2MSFTNGP11.phx.gbl...
unable
> to
> seem
>