troubleshooting Question

build temp table using exec

Avatar of johnnyg123
johnnyg123Flag for United States of America asked on
DatabasesMicrosoft SQL ServerMicrosoft SQL Server 2008
2 Comments1 Solution660 ViewsLast Modified:
apparently can't use parameter in an open query so have to use exec query

I am trying to create temp table using the results of the open query but am running into problems

here is what I have so far:

(for testing purposes I am hard coding user value. )




Declare @username varchar(50)
Set @username = 'dryan'

Declare @query varchar(500)



Set @query =

' WITH TempTableTierCreditsMonth1 (
      department, pager
      )
AS (

select department, pager
 from openquery
(AD1,' +  char(39) + 'SELECT  department, pager
FROM '  + char(39) + char(39) + 'LDAP://test.local'  + char(39) + char(39) +
 ' WHERE objectCategory =  '  + char(39) + char(39) + 'Person'  + char(39) + char(39) +
' AND objectClass = '  + char(39) + char(39) + 'user'  + char(39) + char(39) + ' and samaccountname =  '  + char(39) + char(39) +
@username  + char(39) + char(39) + char(39) +  ');'


exec(@query)
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 2 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros