July 30, 2019 at 1:01 pm
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
What is @Query2 defined as?
July 30, 2019 at 1:16 pm
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''')
July 30, 2019 at 1:46 pm
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)
July 30, 2019 at 1:52 pm
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