OPENROWSET

[allerhop]

Hello Experts,

I have a problem with OPENROWSET:

exec('
select x.*,getdate() as datum
from openrowset(''SQLOLEDB'',''SERVER1'';''user'';''pw'',''
select anr,alist,aidnr,
(select sum(bpstueck) from nachschau.dbo.bestkp where bpbeltyp=''B'' and bpidnr=mlidnr)
from main.dbo.table1
left join main.dbo.table2 on lfdnr=aidnr
'') as x
')

I get the error:
wrong syntax near 'B'


Please Help

 

[PWise]

try Single quotes

bpbeltyp='B'

 

[allerhop]

hello

I get the same errror

 

[quatermass]

Hi,

As the SQL statement is a string for execution, just pasting in a working query won't work if there is a quote anywhere in it. SQL will take a single quote as being the end of what is to be executed and will attempt to run that and the remenants of the string.

It will try to run

select x.*,getdate() as datum
from openrowset(''SQLOLEDB'',''SERVER1'';''user'';''pw'',''
select anr,alist,aidnr,
(select sum(bpstueck) from nachschau.dbo.bestkp where bpbeltyp=

and

B

and

and bpidnr=mlidnr) 
from main.dbo.table1
left join main.dbo.table2 on lfdnr=aidnr
) as x

B isn't a valid SQL statement, therefore you get the error.

A '' (double single quote) will be interpreted by SQL as a single quote at runtime

Try replacing the double quotes around the B with 2 single quotes

''B''

Hope this helps

**************************************************************
Rock is Dead (Long Live Paper and Scissors)
**************************************************************