print - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2009-05-14 : 03:35:41

Hi,
Do you see what is wrong with this query as I do not get anything in the print statement.
Thanks

declare @FileSizeDataDatabase int
declare @DBname varchar(20)
declare @FileNameData varchar(20)

declare @sql nvarchar(1000)

set @DBname = 'model'
set @FileNameData = 'modeldev'

set @sql = 'SELECT '
+ convert(varchar(2), @FileSizeDataDatabase) + '= (( size * 8 )/ (1024))
FROM
sys.master_files
WHERE
db_name ( database_id ) =' + @DBname + '
and Name =' + @FileNameData

print @sql

exec sp_executesql @sql

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-14 : 03:46:44

you need to encluse the string in single quote


set @sql = 'SELECT '
+ convert(varchar(2), @FileSizeDataDatabase) + '= (( size * 8 )/ (1024))
FROM
sys.master_files
WHERE
db_name ( database_id ) = ''' + @DBname + ''''
and Name = ''' + @FileNameData + ''''

KH
[spoiler]Time is always against us[/spoiler]

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2009-05-14 : 03:56:32

Hi,
Tried it but now the error is:
unclosed quotation mark after the character string '' + @FilenameData + ''

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2009-05-14 : 03:59:22

quote:
Originally posted by arkiboys

Hi,
Tried it but now the error is:
unclosed quotation mark after the character string '' + @FilenameData + ''

This is what I have now which doe snot give an error but nothing prints:
set @sql = 'SELECT '
+ convert(varchar(2), @FileSizeDataDatabase) + '= (( size * 8 )/ (1024))
FROM
sys.master_files
WHERE
db_name ( database_id ) =''' + @DBname + '''
and Name =''' + @FileNameData + ''''

anendu
Starting Member

1 Post

Posted - 2009-05-14 : 04:13:16

Hi,
Pass some Values to @FileSizeDataDatabase parameter.It will work.

Regards
Anendu

quote:
Originally posted by arkiboys

Hi,
Do you see what is wrong with this query as I do not get anything in the print statement.
Thanks

declare @FileSizeDataDatabase int
declare @DBname varchar(20)
declare @FileNameData varchar(20)

declare @sql nvarchar(1000)

set @DBname = 'model'
set @FileNameData = 'modeldev'

set @sql = 'SELECT '
+ convert(varchar(2), @FileSizeDataDatabase) + '= (( size * 8 )/ (1024))
FROM
sys.master_files
WHERE
db_name ( database_id ) =' + @DBname + '
and Name =' + @FileNameData

print @sql

exec sp_executesql @sql

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-14 : 04:23:26

[code]
DECLARE @FileSizeDataDatabase int
DECLARE @DBname varchar(20)
DECLARE @FileNameData varchar(20)

DECLARE @sql nvarchar(1000)

SET @DBname = 'model'
SET @FileNameData = 'modeldev'

SELECT @sql = N'SELECT @FileSizeDataDatabase = (( size * 8 )/ (1024))' + CHAR(13)
SELECT @sql = @sql + N'FROM sys.master_files' + CHAR(13)
SELECT @sql = @sql + N'WHERE db_name ( database_id ) = @DBname' + CHAR(13)
SELECT @sql = @sql + N'AND Name = @FileNameData'

print @sql

EXEC sp_executesql @sql, N'@FileSizeDataDatabase int OUTPUT, @DBname varchar(20), @FileNameData varchar(20)',
@FileSizeDataDatabase OUTPUT, @DBname, @FileNameData

SELECT @FileSizeDataDatabase
[/code]

KH
[spoiler]Time is always against us[/spoiler]

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2009-05-14 : 04:26:25

Thank you all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources