Prev: Change SQL License Key
Next: To update a clr dll should I drop the assembly?
From: Eric Russell on 1 Jul 2008 10:58
Yes, it seems that openquery won't accept a variable or concatenated string.
I guess to pull this off, we would have to hold our nose and execute the
openquery as dynamic SQL. A hacker would love to poke around with a procedure
that does this, so be sure to lock down the permissions.

declare
@SQL as varchar(8000),
@UserName as varchar(8000),
@ServerName as varchar(8000)

select @UserName = 'Bob', @ServerName = 'MyServer'

select @SQL =
replace(
replace(
'
select * from openquery( ADSI, ''SELECT NAME,SAMAccountName FROM
''''@ServerName'''' WHERE objectCategory = ''''Person'''' AND objectClass =
''''user'''' and name = ''''@UserName'''''')
',
'@UserName',@UserName),
'@ServerName',@ServerName)

exec (@SQL)



"Adi" wrote:
>
> You can't use a variable inside openquery function, so the line select
> * from openquery( ADSI, @SQL ) will cause an error.
>
> Adi
>
 | 
Pages: 1
Prev: Change SQL License Key
Next: To update a clr dll should I drop the assembly?