January 27, 2010 at 3:04 pm
I want to read the SpaceUsed property for a file in another database then the database I'm currently in. I have made the script below, but it returns an error.
DECLARE @sql4 nvarchar(4000)
DECLARE @sql5 nvarchar(4000)
DECLARE @ParmDefinition nvarchar (500)
DECLARE @ParmDefinition2 nvarchar (500)
DECLARE @Prop int
SET @sql5 = 'USE [model]; SET @PropOUT2 = (SELECT FILEPROPERTY (''modeldev'',''SpaceUsed''))'
PRINT '@sql5 = ' + @sql5
PRINT ''
SET @ParmDefinition2 = N'@PropOUT2 int OUTPUT'
SET @sql4 = 'master.dbo.sp_executesql @sql5, '+@ParmDefinition2+', @PropOUT2=@PropOUT OUTPUT;'
PRINT '@sql4 = ' + @sql4
PRINT ''
SET @ParmDefinition = N'@sql5 nvarchar(4000), @PropOUT int OUTPUT'
PRINT 'Final stament = ''EXEC sp_executesql '+@sql4+', '+@ParmDefinition+', '+@sql5+', @PropOUT=@Prop OUTPUT'
PRINT ''
EXEC sp_executesql @sql4, @ParmDefinition, @sql5, @PropOUT=@Prop OUTPUT
PRINT @Prop
The output of the script is:
@sql5 = USE [model]; SET @PropOUT2 = (SELECT FILEPROPERTY ('modeldev','SpaceUsed'))
@sql4 = master.dbo.sp_executesql @sql5, @PropOUT2 int OUTPUT, @PropOUT2=@PropOUT OUTPUT;
Final stament = 'EXEC sp_executesql master.dbo.sp_executesql @sql5, @PropOUT2 int OUTPUT, @PropOUT2=@PropOUT OUTPUT;, @sql5 nvarchar(4000), @PropOUT int OUTPUT, USE [model]; SET @PropOUT2 = (SELECT FILEPROPERTY ('modeldev','SpaceUsed')), @PropOUT=@Prop OUTPUT
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@PropOUT2".
Can someone help me with this?
When I get it to work, the database name and filename will also become variable and I will also execute it on other servers through a linked server connection, but first I want this to work.
Kind regards,
Marco
January 27, 2010 at 7:04 pm
The output you gave is not the output I got when I tried running your code. Seems awfully complex - were actually trying to run sp_executesql from within sp_executesql?
Anyway, try running the code below. It runs through every online database on the current server and returns the size of the primary data file. Of course, this doesn't take into account any databases that have more than one data file.
DECLARE @sql nvarchar(4000),
@params nvarchar(4000),
@SpaceUsed int,
@DBName sysname,
@PrimaryFileName sysname;
DECLARE crs_dbname CURSOR FOR
SELECT [name]
FROM master.sys.databases
WHERE DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
ORDER BY [name];
OPEN crs_dbname;
FETCH NEXT FROM crs_dbname INTO @DBName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = N'SELECT @PrimaryFileName = [Name] FROM ' + @DBName + N'.sys.sysfiles WHERE [FileID] = 1';
SET @params = N'@PrimaryFileName sysname OUTPUT';
EXEC master..sp_executesql @sql, @params, @PrimaryFileName = @PrimaryFileName OUTPUT;
SET @sql = N'USE ' + @DBName + N'; SELECT @SpaceUsedOUT = FILEPROPERTY(''' + @PrimaryFileName + ''', ''SpaceUsed'');';
SET @params = N'@SpaceUsedOUT int OUTPUT';
EXEC master..sp_executesql @sql, @params, @SpaceUsedOUT = @SpaceUsed OUTPUT;
SELECT @DBName AS "DB Name", @SpaceUsed AS "Space Used";
FETCH NEXT FROM crs_dbname INTO @DBName;
END;
CLOSE crs_dbname;
DEALLOCATE crs_dbname;
GO
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
January 28, 2010 at 6:27 am
Scott
Thanks for your reply. Your code works fine on the local server, but I also want to excute it through a linked server connection. That's why I'm trying to run sp_executesql from within sp_executesql. The FILEPROPERTY function needs to run on the linked server.
The procedure works fine as long as I don't want to put the result in a variable.
Code without result in a variable:
DECLARE @sql4 nvarchar(4000)
DECLARE @sql5 nvarchar(4000)
DECLARE @ParmDefinition nvarchar (500)
SET @sql5 = N'USE [model]; SELECT FILEPROPERTY (''modeldev'',''SpaceUsed'')'
SET @sql4 = 'master.dbo.sp_executesql @sql5'
SET @ParmDefinition = N'@sql5 nvarchar(4000)'
EXEC master..sp_executesql @sql4, @ParmDefinition, @sql5
Result is:
184
Code with result in a variable:
DECLARE @sql4 nvarchar(4000)
DECLARE @sql5 nvarchar(4000)
DECLARE @ParmDefinition nvarchar (500)
DECLARE @ParmDefinition2 nvarchar (500)
DECLARE @Prop int
SET @sql5 = N'USE [model]; SELECT @PropOUT2 = FILEPROPERTY (''modeldev'',''SpaceUsed'')'
SET @ParmDefinition2 = N'@PropOUT2 int OUTPUT'
SET @sql4 = 'master.dbo.sp_executesql @sql5, '+@ParmDefinition2+', @PropOUT2 = @PropOUT OUTPUT'
SET @ParmDefinition = N'@sql5 nvarchar(4000), @PropOUT int OUTPUT'
EXEC master..sp_executesql @sql4, @ParmDefinition, @sql5, @PropOUT = @Prop OUTPUT
PRINT @Prop
Result is:
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@PropOUT2".
Regards,
Marco
January 28, 2010 at 6:37 am
Try using EXEC([sql]) AT [server]
-- Gianluca Sartori
January 28, 2010 at 7:05 am
Gianluca,
With EXEC([sql]) AT [server] I can't send the output to a variable.
With this statement it is possible:
DECLARE @prop int
EXEC pc.master.dbo.sp_executesql
N'USE [model]; SELECT @prop=FILEPROPERTY (''modeldev'',''SpaceUsed'')',
N'@Prop INT OUTPUT',
@prop OUTPUT
PRINT @prop
Problem solved :-)!
Scott and Gianluca, thanks for your help!
Marco
January 28, 2010 at 7:13 am
Well done, nice solution!
-- Gianluca Sartori
January 28, 2010 at 11:07 am
Ha, I was happy to soon. We haven't solved it completely. Now I'm able to run it on a linked server, but I want it to run on different linked server by passing a variable servername to the procedure. Unfortunatly EXEC can't resolve variables in it's statement (EXEC @servername.master.dbo..... doesn't work). Now we are back at the original problem of this topic.
We need to execute an sp_executesql in another sp_executesql, but how can I return the result to a variable.
DECLARE @ServerName sysname
DECLARE @prop int
DECLARE @sql nvarchar(4000)
DECLARE @Param nvarchar(50)
SET @ServerName='pc'
SET @sql=@ServerName+'.master.dbo.sp_executesql N''USE [model]; SELECT @PropOUT=FILEPROPERTY (''''modeldev'''',''''SpaceUsed'''')'', N''@PropOUT INT OUTPUT'', @propOUT OUTPUT'
SET @Param='@PropOUT INT OUTPUT'
EXEC master.dbo.sp_executesql @sql, @Param, @PropOUT=@prop
PRINT @prop
Regards,
Marco
January 28, 2010 at 2:35 pm
To start with, here's solution to your undefined variable error:
DECLARE @sql4 nvarchar(4000)
DECLARE @sql5 nvarchar(4000)
DECLARE @ParmDefinition nvarchar (500)
DECLARE @ParmDefinition2 nvarchar (500)
DECLARE @Prop int
SET @sql5 = N'USE [model]; SELECT @PropOUT2 = FILEPROPERTY (''modeldev'',''SpaceUsed'')'
SET @ParmDefinition2 = N'@PropOUT2 int OUTPUT'
SET @sql4 = 'EXEC master.dbo.sp_executesql @sql5, N''' + @ParmDefinition2 + ''', @PropOUT2 = @PropOUT OUTPUT'
SET @ParmDefinition = N'@sql5 nvarchar(4000), @PropOUT int OUTPUT'
EXEC master..sp_executesql @sql4, @ParmDefinition, @sql5, @PropOUT = @Prop OUTPUT
PRINT @Prop
The problem was that you were adding the text from @ParmDefinition2 to the @sql4 string, so it was coming out like this:
EXEC master.dbo.sp_executesql @sql5, @PropOUT2 int OUTPUT, @PropOUT2 = @PropOUT OUTPUT
You can pass the parameter string as a string (as opposed to a string variable) but you need to enclose it in quotes. My fix above returns this for @sql4:
EXEC master.dbo.sp_executesql @sql5, N'@PropOUT2 int OUTPUT', @PropOUT2 = @PropOUT OUTPUT
Which seems to work fine.
Now to work on the server name bit.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
January 28, 2010 at 3:01 pm
This seems to work:
DECLARE @sql4 nvarchar(4000)
DECLARE @sql5 nvarchar(4000)
DECLARE @ParmDefinition nvarchar (500)
DECLARE @ParmDefinition2 nvarchar (500)
DECLARE @Prop int
DECLARE @SrvName sysname
SET @SrvName = N'AServer'
SET @sql5 = N'USE [model]; SELECT @PropOUT2 = FILEPROPERTY (''modeldev'',''SpaceUsed'')'
SET @ParmDefinition2 = N'@PropOUT2 int OUTPUT'
SET @sql4 = N'EXEC ' + @SrvName + N'.master.dbo.sp_executesql @sql5, N''' + @ParmDefinition2 + N''', @PropOUT2 = @PropOUT OUTPUT'
print @sql4
SET @ParmDefinition = N'@sql5 nvarchar(4000), @PropOUT int OUTPUT'
EXEC master..sp_executesql @sql4, @ParmDefinition, @sql5, @PropOUT = @Prop OUTPUT
PRINT @Prop
You could then use a cursor or something to go through sys.servers and repeat for each linked server. Or hard code the server name in a master stored proc that calls a stored proc with the above code in it. Depends how dynamic you want to make it.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
January 29, 2010 at 2:15 pm
Scott,
thank you, thank you, thank you.
This is great. I will use this for my reports (i'm creating a report for my morning check and want it to report all databases with freespace less then 20%). Most monitoring tools can't handle secondary files, but now we can.
Thank you again,
Marco
Thank you
September 9, 2019 at 12:22 pm
Scott.
That is the best example of double nesting Dynamic SQL with Output parameters I've seen even 9 years later. Just what I've been struggling with. Thank you.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply