DYNAMIC SQL STATEMENT - 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 2000 Forums

Transact-SQL (2000)

DYNAMIC SQL STATEMENT

Author

Topic

dewacorp.alliances

452 Posts

Posted - 2006-10-29 : 23:45:21

Hi there

I am trying to construct @BACKUPHISTORY query but in this query I use @BACKUP_LOCATION as a return value and use that for next query.

But everytime I try to PRINT @BACKUP_LOCATION is always null ?!?!

Thanks

DECLARE @PATH VARCHAR(100)
DECLARE @DB_NAME sysname
DECLARE @DB_SERVER_NAME VARCHAR(100)
DECLARE @SQL_ALTER1 VARCHAR(100)
DECLARE @SQL_ALTER2 VARCHAR(100)
DECLARE @SQL_RESTORE VARCHAR(500)
DECLARE @BACKUP_LOCATION VARCHAR(100)
DECLARE @NEW_DB_NAME VARCHAR(50)
DECLARE @DOS_CMD VARCHAR(50)
DECLARE @SQL_BACKUPHISTORY VARCHAR(200)

SET @DB_NAME 		= 'dbMASurvey'
SET @DB_SERVER_NAME	= 'SQLSERVER'
SET @NEW_DB_NAME	= @DB_NAME + '_' + @DB_SERVER_NAME + '_DRT'
SET @PATH 		= 'D:\MSSQL\Data'

IF NOT EXISTS (SELECT * FROM dbo.sysdatabases WHERE dbid = db_id(@NEW_DB_NAME))
BEGIN
	SET @DOS_CMD = 'MKDIR ' + @PATH + '\' + @NEW_DB_NAME
	EXEC xp_cmdshell @DOS_CMD
END

SET @SQL_BACKUPHISTORY = 'select top 1 @BACKUP_LOCATION = Location from ' + @DB_SERVER_NAME + '.Administration.dbo.vw_BackupHistory ' +
			 'where DBname =' + @DB_NAME + ' and TypeOf = ''Full'' and ' +
				'Location not like ''VNBU0%''' +
			 'order by EndDate DESC;'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-29 : 23:52:36

how do you execute the @SQL_BACKUPHISTORY ? using exec() or sp_executesql ?

Can you also post the execution part ?

dewacorp.alliances

452 Posts

Posted - 2006-10-29 : 23:56:17

Using EXEC()

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-30 : 00:42:26

>> Using EXEC()
No error encountered ?

You should use sp_executesql as it allows you to pass parameter in / out


DECLARE @SQL_BACKUPHISTORY NVARCHAR(200)
exec sp_executesql @SQL_BACKUPHISTORY, N'@BACKUP_LOCATION VARCHAR(100) OUTPUT', @BACKUP_LOCATION OUTPUT

sp_executesql expected nvarchar so you will need to declare @SQL_BACKUPHISTORY as nvarchar

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources