sp_executesql - Not getting any data in output variable

  • MattNorman88

    SSC Journeyman

    Points: 77

    EXEC sp_executesql
    @stmt=@Query2,
    @params=N'@DatabaseSize int OUTPUT',
    @DatabaseSize=@DatabaseSize OUTPUT;

    This is running and returning the data as expected however the @DatabaseSize variable remains as NULL.

    Can anyone see anything wrong with this?

  • tripleAxe

    SSCertifiable

    Points: 5605

    What is @Query2 defined as?

     

  • MattNorman88

    SSC Journeyman

    Points: 77

    SET @Query1 = FORMATMESSAGE(N'''SELECT SUM([master].[sys].[master_files].)*8/1024 FROM [master].[sys].[master_files] WHERE [master].[sys].[master_files].[name] = ''''%s''''', @DatabaseName)
    SET @Query2 = FORMATMESSAGE(N'SELECT * FROM OPENQUERY([%s],%s'')', @LinkedServerName, @Query1)

    Formatted it is:

    SELECT * FROM OPENQUERY([DESKTOP-ME5N9LH\SQLEXPRESS],'SELECT SUM([master].[sys].[master_files].)*8/1024 FROM [master].[sys].[master_files] WHERE [master].[sys].[master_files].[name] = ''TestDB''')
  • tripleAxe

    SSCertifiable

    Points: 5605

    You need to name the column in Query1, and also add the size column name back in.

    e.g.

    SET @Query1 = FORMATMESSAGE(N'''SELECT SUM([master].[sys].[master_files].size)*8/1024 [Size] FROM [master].[sys].[master_files] WHERE [master].[sys].[master_files].[name] = ''''%s''''', @DatabaseName)

    And also in @Query2 you need to set the value of the parameter @DatabaseSize.

     

    e.g.

    SET @Query2 = FORMATMESSAGE(N'SELECT @DatabaseSize = [Size] FROM OPENQUERY([%s],%s'')', @LinkedServerName, @Query1)

     

    • This reply was modified 1 year ago by  tripleAxe.
  • MattNorman88

    SSC Journeyman

    Points: 77

    Perfect, that's now working.

    Appreciate the help

Viewing 5 posts - 1 through 5 (of 5 total)

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