Start Free Trial
Come for the solution, stay for everything else.
Start Free Trial
build temp table using exec
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
select department, pager
(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) + ');'
Microsoft SQL Server
Microsoft SQL Server 2008
8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Log in or sign up to see answer
Become an EE member today
7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
You could also create a global temp table on the local server.
First, insure the name is absolutely unique -- I use code like:: '##' + replace(cast(newid() AS varchar(60)), '-', '') :: for that. [The only disadvantage is that forces all code that references the table to be dynamic SQL.]
Create or load that table using OPENQUERY.
Build index(es) on the table as needed.
Do the local queries that need that data.
Drop the global temp table.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Plans and Pricing
Certified Expert Program
© 1996-2023 Experts Exchange, LLC. All rights reserved. Covered by US Patent