Post reply

sp_executesql - Not getting any data in output variable

  • July 30, 2019 at 1:01 pm

    #3667188 

    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?

  • July 30, 2019 at 1:15 pm

    #3667209

    What is @Query2 defined as?

     

  • July 30, 2019 at 1:16 pm

    #3667210 

    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''')

    • This reply was modified 5 years, 3 months ago by  MattNorman88.

  • July 30, 2019 at 1:46 pm

    #3667217

    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 5 years, 3 months ago by  tripleAxe.

  • July 30, 2019 at 1:52 pm

    #3667219

    Perfect, that's now working.

    Appreciate the help

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

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