June 20, 2022 at 12:22 pm
Hello!
Here is an example of a script that selects into one table the same data spread across 2 linked servers
--/****** Script for SelectTopNRows command from SSMS ******/
DECLARE @DateTimeStart DATETIME;
DECLARE @DateTimeEnd DATETIME;
SET @dateTimeStart= '2022-06-10 10:00:00'
SET @dateTimeEnd= '2022-06-10 11:00:00';
IF OBJECT_ID(N'tempdb..#TQC') IS NOT NULL
BEGIN
DROP TABLE #TQC
END
SELECT
cntQUEUE as cntQUEUE
,cntDate as cntDate
,cntSHEETSTOTAL as cntSHEETSTOTAL
,cntSHEETSCOLOR as cntSHEETSCOLOR
INTO #TQC
FROM [w].[dbo].[qc]
WHERE cntDATE BETWEEN @DateTimeStart AND @DateTimeEnd
UNION
SELECT cntQUEUE as cntQUEUE
,cntDate as cntDate
,cntSHEETSTOTAL as cntSHEETSTOTAL
,cntSHEETSCOLOR as cntSHEETSCOLOR
FROM [s00-6800-be01\w].[w].[dbo].[qc]
WHERE cntDATE BETWEEN @DateTimeStart AND @DateTimeEnd
SELECT * FROM #TQC
ORDER BY cntQUEUE
I've got about 40 servers to link the data from
So, is there a way to save the effort and write smth like
@TABLELIST = '[s00-0001-be01\w].[w].[dbo].[qc]','[s00-0002-be01\w].[w].[dbo].[qc]','[s00-0040\w].[w].[dbo].[qc]'
SELECT cntQUEUE as cntQUEUE
,cntDate as cntDate
,cntSHEETSTOTAL as cntSHEETSTOTAL
,cntSHEETSCOLOR as cntSHEETSCOLOR
FROM FOREACH @TABLE IN @TABLELIST
WHERE cntDATE BETWEEN @DateTimeStart AND @DateTimeEnd
Would be nice to read smth's comment on this
June 20, 2022 at 1:25 pm
Do all the linked servers definitely have the table dbo.qc in the database w? Are all these objects exactly the same definition? Do you have have to use UNION (which is a very expensive operator) rather than UNION ALL?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 20, 2022 at 1:32 pm
No, SQL Server doesn't let you pass tables or columns as parameters. You could derive something like this using dynamic SQL.
But I might also we concerned about protentional blocking when unioning together forty linked servers. I'd be more inclined to write each of the inserts into a temp or staging table independently, and then use group by or select distinct to eliminate duplicates (I assume that's the reason you used UNION instead of UNION ALL)
June 20, 2022 at 4:33 pm
Thank you, ratbak! Sorry, but I can't really catch your drift. Do you mean that with dynamic SQL it is somehow possible to do FOREACH or write less code?
June 20, 2022 at 4:39 pm
Hello!
Thank you, Thom for your questions!
Do all the linked servers definitely have the table dbo.qc in the database w?
Are all these objects exactly the same definition?
The answer is yes to both questions. I learned a minute ago what UNION ALL is. Will use this operator rather than UNION
With these answers in mind, do you think that there's a way to simplify UNION ALL query for 40 linked servers?
June 20, 2022 at 5:07 pm
Do you mean that with dynamic SQL it is somehow possible to do FOREACH or write less code?
Dynamic SQL could achieve the "writing less code" goal using something like this:
SET NOCOUNT ON;
DECLARE @tablelist NVARCHAR(MAX) = N'[s00-0001-be01\w],[s00-0002-be01\w],[s00-0040\w]'
DROP TABLE IF EXISTS #servers
CREATE TABLE #servers
(ServerName SYSNAME NOT NULL PRIMARY KEY);
INSERT INTO #servers (ServerName)
SELECT value
FROM STRING_SPLIT(@tablelist,',') tableList
--SELECT * FROM #servers;
DECLARE @sql NVARCHAR(max) = N'';
SELECT @sql = @sql + CONCAT('SELECT cntQUEUE as cntQUEUE,cntDate as cntDate,cntSHEETSTOTAL as cntSHEETSTOTAL,cntSHEETSCOLOR as cntSHEETSCOLOR
FROM ',#servers.ServerName,'.[w].[dbo].[qc]
WHERE cntDATE BETWEEN @DateTimeStart AND @DateTimeEnd UNION ALL
')
FROM #servers;
SET @sql = LEFT(@sql,LEN(@sql) - 12)
PRINT @sql;
You could modify to insert into your chosen temp or staging table.
But unless the linked servers change/increase frequently, and given that the "more code" approach could be accomplished mostly w/ copy & paste (replacing linked server name for each), I wouldn't necessarily be too concerned about writing more code once.
In fact, if the list is relatively static, you could paste the code generated w/ dynamic sql into a script or stored procedure.
June 21, 2022 at 7:59 am
If possible, avoid linked servers.
your code may block others ( local to the linked server ) !
Maybe your process is even better off doing its stuff using e.g. SSIS ( or even direct powerbi of that is your end platform )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 22, 2022 at 6:10 pm
Of far more interest to me is the reasoning behind having 40 linked servers, and apparently each has the same database structure.
What kinds of things were you trying to accomplish with this architecture?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
September 17, 2022 at 5:46 am
Hi!
It's a decentralized system that tracks activity of some sort. Each local server gathers stat into its, local, DB, then relay it to the central server.
Old technology, but it works quite well )
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply