use with dynamic sql

Question

Post reply

use with dynamic sql

Sailor

SSCertifiable

Points: 5246
More actions
May 10, 2012 at 7:12 am

#255633

I'm trying to do in a script:
SET @DYNSQL = 'USE ' + @DBName + ' SELECT name AS [File Name] , physical_name AS [Physical Name], size/128.0 AS [Total Size in MB], size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS int)/128.0 AS [Available Space In MB], [file_id] FROM sys.database_files WITH (NOLOCK) OPTION (RECOMPILE)'
print @dynsql
EXEC SP_EXECUTESQL @DYNSQL
output:
USE wpjcrdb SELECT name AS [File Name] , physical_name AS [Physical Name], size/128.0 AS [Total Size in MB], size/128.0 -
CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [Available Space In MB], [file_id] FROM sys.database_files WITH (NOLOCK) OPTION (RECOMPILE);
[/code]
There's no output of the sp_executesql. But, if I copy and paste the SQL from the print statement the whole statement works fine and I get the output.
Now I run the sql above, the output is fine:
JCRDB_DATAD:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\/Data/wpjcrdb_Data.MDF17080.00000015.6250001
DB_LOGD:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\/Data/wpjcrdb_Log.LDF2265.0000002208.1875002
I've done use ... alter table before and that works with dynamic sql, but this doesn't work.

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

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

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 1

It works for me.

How are you calling it?

Converting oxygen into carbon dioxide, since 1955.

RossRoss SSCommitted Points: 1672 More actions · Answer 2

Works fine for me:

DECLARE@DBName NVARCHAR(50),

@DYNSQL NVARCHAR(MAX)

SET@DBName = 'master'

SET@DYNSQL = 'USE ' + @DBName + ' SELECT name AS [File Name] , physical_name AS [Physical Name], size/128.0 AS [Total Size in MB], size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS int)/128.0 AS [Available Space In MB], [file_id] FROM sys.database_files WITH (NOLOCK) OPTION (RECOMPILE)'

--print @dynsql

EXEC SP_EXECUTESQL @DYNSQL

Returns:

masterc:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\master.mdf4.0000001.0625001

mastlogc:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\mastlog.ldf1.2500000.3906252

Sailor SSCertifiable Points: 5246 More actions · Answer 3

O stupid me, I just realized it's showing up on the Results tab, I was expecting to see it on the Messages tab! After I set results to text, it works ok.