April 13, 2015 at 5:21 pm
I am constructing a query. It will pull data from 10 different databases. Rather than write the code then different times, I am using a cursor to build the query. However, the only was I can think of to do it is to use dynamic SQL. I don't mind using it, but it has its limitations in this application. Here is a sample:
IF OBJECT_ID('TmpVendDBName', 'U') IS NOT NULL DROP TABLE TmpVendDBName
CREATE TABLE TmpVendDBName (BusinessUnit NVARCHAR(100), VendDBName NVARCHAR(100))
INSERT INTO TmpVendDBName SELECT 'Australia','AUS_DB';
INSERT INTO TmpVendDBName SELECT 'Brazil','BRL_DB';
INSERT INTO TmpVendDBName SELECT 'United States','US_DB';
DECLARE @BUName NVARCHAR(100)
DECLARE @DBName NVARCHAR(100)
DECLARE VendInvCursor CURSOR FOR
SELECT BusinessUnit, VendDBName FROM TmpVendDBName
OPEN VendInvCursor
FETCH NEXT FROM VendInvCursor INTO @BUName, @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @SQLExecA NVARCHAR(MAX)
SET @SQLExecA ='
SELECT '' + @BUName + '' AS BusinessUnit
, Item
, ItemDescription
FROM ' + @DBName + '.ProductTable
WHERE ItemStatus = ''Active''
'
PRINT(@SQLExecA)
FETCH NEXT FROM VendInvCursor INTO @BUName, @DBName
END
CLOSE VendInvCursor
DEALLOCATE VendInvCursor
Is there a way to write this in such a way that I don't have to use dynamic SQL?
Thanks.
Steve
April 13, 2015 at 6:00 pm
Either you write the 10 queries or you use Dynamic SQL. You could use a procedure called sp_MSforeachdb (which is undocumented) or Aaron Bertrand's version http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/29/a-more-reliable-and-more-flexible-sp-msforeachdb.aspx
What limitations are you concerned about?
April 13, 2015 at 6:40 pm
There are two limitations that I see. First, the queries are fairly large, so I bump up against the max. I know I can split it up, but it's a hassle. The second is that it makes it difficult to modify. When I extract a piece of code to modify and test it, I have to take out the extra quote marks and replace the parameters with objects, then reverse the process when I need to reinsert it. It takes time, and opens it up for mistakes.
April 13, 2015 at 11:42 pm
sdownen05 (4/13/2015)
There are two limitations that I see. First, the queries are fairly large, so I bump up against the max. I know I can split it up, but it's a hassle. The second is that it makes it difficult to modify. When I extract a piece of code to modify and test it, I have to take out the extra quote marks and replace the parameters with objects, then reverse the process when I need to reinsert it. It takes time, and opens it up for mistakes.
There is virtually no max for dynamic SQL upto NVARCHAR(MAX). That's a billion characters. And if you write it correctly, you won't have any problems modifying it. The way to write this one correctly is to make a view against the tables in the different databases and then just query the view. The underlying indexes will be used. Your query would then not need to be dynamic at all.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 14, 2015 at 7:54 am
Jeff,
I guess I was confused about NVARCHAR(MAX). I thought the max was 4000 characters, because when I print the results of my dynamic query it will cut it off. I guess it is a problem with the PRINT function and not the maximum value of NVARCHAR.
As far as views, if I understand right, I should create ten views, then use my stored procedure to query those views. Is that correct?
Thanks.
Steve
April 14, 2015 at 8:08 am
sdownen05 (4/14/2015)
Jeff,I guess I was confused about NVARCHAR(MAX). I thought the max was 4000 characters, because when I print the results of my dynamic query it will cut it off. I guess it is a problem with the PRINT function and not the maximum value of NVARCHAR.
As far as views, if I understand right, I should create ten views, then use my stored procedure to query those views. Is that correct?
Thanks.
Steve
NP. And, no... not 10 views. 1 view with 10 SELECT/UNION ALLs. It'll be treated like one table. You could actually take it one step further and add a constraint to the table for the "vendor" in each database and you would have a "partitioned view" that would also allow for "partition elimination" in queries with criteria for the vendor that don't need to address all the databases.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 14, 2015 at 8:12 am
And to be sure, the query for the view would just be...
SELECT columnlist FROM databaseA.dbo.table UNION ALL
SELECT columnlist FROM databaseB.dbo.table UNION ALL
SELECT columnlist FROM databaseC.dbo.table UNION ALL
etc...
Don't use "*" for the column list here. Too much of a chance of something going haywire later in life. You can easily get the column list for the tables by clicking and dragging the "columns" "folder" from the Object Explorer into the code window. Keep in mind that each table must have the same number and order of columns for all the SELECTs in this view.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 14, 2015 at 8:16 am
Thank you, Jeff. That is how I will handle this. Thanks for the solution, and thanks for the reminders.
April 14, 2015 at 8:29 am
To make it more flexible, you might consider doing it like this:
SELECT 'databaseA', columnlist FROM databaseA.dbo.table UNION ALL
SELECT 'databaseB', columnlist FROM databaseB.dbo.table UNION ALL
SELECT 'databaseC', columnlist FROM databaseC.dbo.table UNION ALL
etc...
I think that might be what Jeff was referring to when he talked about constraints and partitioned views.
John
April 14, 2015 at 10:49 am
John Mitchell-245523 (4/14/2015)
To make it more flexible, you might consider doing it like this:SELECT 'databaseA', columnlist FROM databaseA.dbo.table UNION ALL
SELECT 'databaseB', columnlist FROM databaseB.dbo.table UNION ALL
SELECT 'databaseC', columnlist FROM databaseC.dbo.table UNION ALL
etc...
I think that might be what Jeff was referring to when he talked about constraints and partitioned views.
John
That's virtually identical to how we solved a similar problem. The only thing we ran into was when someone wanted to add new items into the collection. Since they all used the same naming structure and nothing else used the same structure, we were able to create a stored procedure to recreate the view as needed. It's worked out well over the several years we've had it in place.
April 16, 2015 at 11:10 am
Try this...
DECLARE @t table(ID int not null identity(1,1), name varchar(255));
INSERT INTO @t(name)
SELECT name
FROM sys.databases -WHERE database_id > 4
SET @total = @@ROWCOUNT;
SET @id=1;
WHILE @id <= @total
BEGIN
SELECT @dbname = name FROM @t WHERE ID = @id
SET @sql =
'
USE [' + @dbname + '];
SELECT BusinessUnit, VendDBName
FROM TmpVendDBName
WHERE ItemStatus = ''Active''
'
--PRINT @sql
EXECUTE sp_executesql @sql
SET @id = @id + 1
END
You can even customize the WHERE clause and skip some databases if needed.
April 21, 2015 at 12:33 am
Thanks to everyone for their help. Ed, I am using your solution, and it works perfectly! Thanks.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply