April 18, 2022 at 8:34 pm
Hello, i need your help please since i am stuck here and not sure how to fix this.
Trying to run this code below and getting this message (Must declare the scalar variable "@Name").
How can i pass the @name into the from clause so the results are returning for each db?
Thank you
DROP TABLE IF EXISTS #linkedservers
CREATE table #linkedservers
(
SRV_NAME sysname NULL
,SRV_PROVIDERNAME nvarchar(128) NULL
,SRV_PRODUCT nvarchar(128) NULL
,SRV_DATASOURCE nvarchar(4000) NULL
,SRV_PROVIDERSTRING nvarchar(4000) NULL
,SRV_LOCATION nvarchar(4000) NULL
,SRV_CAT sysname NULL
)
INSERT #linkedservers
(SRV_NAME
,SRV_PROVIDERNAME
,SRV_PRODUCT
,SRV_DATASOURCE
,SRV_PROVIDERSTRING
,SRV_LOCATION
,SRV_CAT
)
EXEC sp_linkedservers
DECLARE @Statement nvarchar(2000)
DECLARE @server_id nvarchar(150)
DECLARE LinkedServers CURSOR FOR
SELECT SRV_NAME
FROM #linkedservers where SRV_NAME in ('LinkedServe1','LinkedServe2')
ORDER BY SRV_NAME
OPEN LinkedServers
FETCH NEXT FROM LinkedServers into @server_id
WHILE @@FETCH_STATUS=0
BEGIN
/*building up dynamic sql*/
SET @Statement =N'
SELECT '''+ convert(nvarchar(150),@server_id) + N''' as [Server];
CREATE TABLE #name
(
id int IDENTITY(1,1),
[name] sysname
)
insert into #name
SELECT name
FROM [' + @server_id + '].master.sys.databases
WHERE [state] = 0;
DECLARE @Counter INT ,
@MaxId INT;
SELECT @Counter = min(Id) , @MaxId = max(Id)
FROM #name
WHILE(@Counter IS NOT NULL
AND @Counter <= @MaxId)
BEGIN
declare @Name NVARCHAR(100)
Select @Name = name
FROM #name WHERE Id = @Counter
SELECT
name AS [DataFileName],
physical_name AS [DataPhysicalName],
ROUND(size / 128.0, 1) AS [DataTotalSizeInMB],
ROUND(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS int) / 128.0, 1) AS [DataSpaceUsedInMB],
ROUND(size / 128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS int) / 128.0, 1) AS [DataAvailableSpaceInMB]
FROM ['+ @Server_id +']. ['+ @Name +'].[sys].[database_files]
SET @Counter = @Counter + 1
END
'
/*executing dynamic sql using format linkedserver.database.owner.object*/
EXECUTE master.dbo.sp_executesql @Statement
FETCH NEXT FROM LinkedServers into @server_id
END
CLOSE LinkedServers
DEALLOCATE LinkedServers
DROP TABLE #linkedservers
April 19, 2022 at 4:54 pm
FROM [' + @Server_id + ']. [' + @Name + '].[sys].[database_files]
That's your issue. You are declaring it in the dynamic SQL , yet you are referring to it outside of the dynamic SQL.
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/
April 19, 2022 at 5:48 pm
Thank you for reply Michael, i know that this the problem but i don't know how to fix this.
If you can point me to the right direction on where should i declare @Name.
Thank you
April 19, 2022 at 6:05 pm
I think I would start here:
There is no need to concatenate the variables into your code. That is SQL Injection at it's finest. Use the variables as parameters.
Also, it appears that you are trying to get disk space for your linked servers.
If you created a linked server for this reason, then that's probably not a great idea.
Have you though of using central management server and querying a group of servers at once?
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/
April 19, 2022 at 6:10 pm
Thank you Michael, let me try the link you paste. I am trying to insert this data in the centralized table which will be used with different reports.
April 19, 2022 at 8:06 pm
I think I would start here:
There is no need to concatenate the variables into your code. That is SQL Injection at it's finest. Use the variables as parameters.
Also, it appears that you are trying to get disk space for your linked servers.
If you created a linked server for this reason, then that's probably not a great idea.
Have you though of using central management server and querying a group of servers at once?
You can do that from registered servers - but only manually through a multi-server query. Not sure how you query a group of servers using CMS without using some process to loop through each folder and server to generate the connection.
I would not use linked servers for this - in fact, I would not create a process that runs from a CMS or some other central server across all servers and pull the data. Instead, I would create a process on each server and push the data to the central server. Those processes would then run concurrently rather than consecutively.
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 19, 2022 at 8:51 pm
Michael L John wrote:I think I would start here:
There is no need to concatenate the variables into your code. That is SQL Injection at it's finest. Use the variables as parameters.
Also, it appears that you are trying to get disk space for your linked servers.
If you created a linked server for this reason, then that's probably not a great idea.
Have you though of using central management server and querying a group of servers at once?
You can do that from registered servers - but only manually through a multi-server query. Not sure how you query a group of servers using CMS without using some process to loop through each folder and server to generate the connection.
I would not use linked servers for this - in fact, I would not create a process that runs from a CMS or some other central server across all servers and pull the data. Instead, I would create a process on each server and push the data to the central server. Those processes would then run concurrently rather than consecutively.
If this is something being collected and reported on regularly, I'm 100% with your method Jeffery.
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/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply