Lost in sp_executesql

  • mchofman

    Ten Centuries

    Points: 1385

    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

  • thecosmictrickster@gmail.com

    SSChampion

    Points: 10386

    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



    Scott Duncan

    MARCUS. Why dost thou laugh? It fits not with this hour.
    TITUS. Why, I have not another tear to shed;
    --Titus Andronicus, William Shakespeare


  • mchofman

    Ten Centuries

    Points: 1385

    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

  • spaghettidba

    SSC Guru

    Points: 105661

    Try using EXEC([sql]) AT [server]

  • mchofman

    Ten Centuries

    Points: 1385

    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

  • spaghettidba

    SSC Guru

    Points: 105661

    Well done, nice solution!

  • mchofman

    Ten Centuries

    Points: 1385

    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

  • thecosmictrickster@gmail.com

    SSChampion

    Points: 10386

    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.



    Scott Duncan

    MARCUS. Why dost thou laugh? It fits not with this hour.
    TITUS. Why, I have not another tear to shed;
    --Titus Andronicus, William Shakespeare


  • thecosmictrickster@gmail.com

    SSChampion

    Points: 10386

    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.



    Scott Duncan

    MARCUS. Why dost thou laugh? It fits not with this hour.
    TITUS. Why, I have not another tear to shed;
    --Titus Andronicus, William Shakespeare


  • mchofman

    Ten Centuries

    Points: 1385

    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

  • planetmatt

    SSCrazy

    Points: 2051

    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 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply