April 17, 2009 at 5:53 am
Hello,
I have a Table (#TCompanyNames) that has database names.
I query a master table to get a list of databases that I need to query. In each database there will be two tables that need to be queried.
I know how to write the dynamic sql. I was wondering is there an alternative to write a dynamic sql statement and then executing it.
For example:
Create table #TCompanyNames (
CompanyName varchar(100) primary key);
insert into #TCompanyNames (CompanyName)
select companyname from from Sales..mastercompany where company = 'Active' and profitable = 'Yes' order by companyname
Now in each database there are two tables TablesA, TableB
Create Table TableA (
CompanyName varchar(100),
CompanyProfit decimal(18,3))
Create Table TableB (
CompanyName varchar(100),
CompanyProfit decimal(18,3))
As I am writing my stored procedure, I will need to query databasename..tablename and inserting the records into a master table for each database name in #TCompanyNames:
insert into mastertable
select companyname, company profit from databasename..tablename
Can this be done without using a cursor or dynamic sql?
Thanks.
Tony
Things will work out. Get back up, change some parameters and recode.
April 17, 2009 at 7:29 am
you are right in your suspicions, it's gotta be dynamic.
the reason is any sql statement cannot take a variable for an Object.
so whatever the object, whether databasename or tablename, if it is coming from a table, it's going to be treated just a string,and not an object, so you've got to use dynamic SQL
Lowell
April 17, 2009 at 10:11 am
The only way to avoid dynamic SQL would be to create a partitioned view. You could create the view dynamically, and then reference the view in your procedures.
So, you could have something like this:
CREATE VIEW dbo.CompaniesViewTable1 AS
SELECT {columns} FROM Company1.dbo.Table1 WHERE ...
UNION ALL SELECT {columns} FROM Company2.dbo.Table1 WHERE ...
...
GO
CREATE VIEW dbo.CompaniesViewTable2 AS
SELECT {columns} FROM Company1.dbo.Table2 WHERE ...
UNION ALL SELECT {columns} FROM Company2.dbo.Table2 WHERE ...
...
GO
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 17, 2009 at 10:37 am
Thanks guys.
I am learning so much from this forum! I like to research and investigate and come up with a strategy or a thought. Then I bounce it off this forum just to make sure.
I thought dynamic sql was the way, but wanted to make sure!
Thanks again.
Tony
Things will work out. Get back up, change some parameters and recode.
April 19, 2009 at 2:27 pm
Our head DBA and I both agree that using dynamic SQL to pull together information from multiple databases is a good way to go. In our environment, the DBAs sometimes move databases from one server to another for load balancing purposes. If linked server names and DB names are retrieved from a table, all they need to do is update the table and everything works. No updates to the code are required.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 19, 2009 at 7:52 pm
WebTechie38 (4/17/2009)
Thanks guys.I am learning so much from this forum! I like to research and investigate and come up with a strategy or a thought. Then I bounce it off this forum just to make sure.
I thought dynamic sql was the way, but wanted to make sure!
Thanks again.
Tony
If that is the case, I recommend that you take a serious look at synonyms. Usings synonyms will allow you to avoid the usage of dynamic SQL and the inherent security problems.
For example:
CREATE SYNONYM local.ObjectName FOR server.remotedb.dbo.ObjectName;
And in your code, you reference the local.ObjectName. If the DBA's move things around, all they have to do us update the synonym and there are no code changes needed.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 20, 2009 at 3:16 am
$0.02:
SYNONYMs are very cool, but beware of incompatibilities with any (usually legacy) code which might barf when it sees a synonym instead of a 'real' object.
We had an instance where some VB6 code (using ADO I think) blew up horribly when trying to execute a stored procedure via a synonym. ADO (or whatever it was) was issuing some SQL in the background to get information about the procedure before binding it. The SQL in question was not synonym-aware, and died screaming.
That said, I am a big fan of synonyms - they are a much underused feature.
Cheers,
Paul
April 20, 2009 at 6:53 am
I have nothing against synonyms, but wouldn't the use of synonyms require distinct sets of code for each synonym accessed? When pulling numbers from similar tables in different databases, a while loop ensures that there is only one set of code to maintain. HOWEVER, it suddenly occurs to me that I might be able to alter the synonym with each iteration of the while loop. Hmmmmmm.... might be worth a little performance testing.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 20, 2009 at 9:46 am
Bob Hovious (4/20/2009)
I have nothing against synonyms, but wouldn't the use of synonyms require distinct sets of code for each synonym accessed? When pulling numbers from similar tables in different databases, a while loop ensures that there is only one set of code to maintain. HOWEVER, it suddenly occurs to me that I might be able to alter the synonym with each iteration of the while loop. Hmmmmmm.... might be worth a little performance testing.
Yes, synonyms require distinct sets of code. And, your idea of modifying the synonym will not work unless you can guarantee that the code in question can only be run by one process at one time (serialize the process).
If I need to access multiple databases from a single location, I can easily do this:
SELECT {columns}
FROM database.schema.object;
However, what happens when we move the database? Now, I have to go in and modify all the code that access that database and change it - to either a different database or a linked server and database.
If I had used a synonym for the above, then all I have to do is change the synonym and the code still works and still functions correctly.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 20, 2009 at 10:45 am
The while loop (for only 10-20 tables on average) serves to serialize the processing. In our case, the processing is identical, which is why we used a loop without hard coding the fully qualified names, including linked server. Where the processing is distinctly different from DB to DB the use of synonyms makes much more sense.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply