Public variables - 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)

Public variables

Author

Topic

nicoart
Starting Member

11 Posts

Posted - 2006-11-13 : 23:07:28

Hi there... I am newbie for T-SQL. I am using SQL Server 2005 to automate the backup procedure. I have produced the script for back up and modify a little bit. The script looks like this below:

declare @filename nchar(100)
declare @filebak nchar (100)
declare @timenow char(12)
set @timenow = (cast(datepart(yyyy,getdate()) as varchar(4)) +
case
when len(cast(datepart(mm,getdate()) as varchar(2))) = 1 then
'0' + cast(datepart(mm,getdate()) as varchar(2))
else cast(datepart(mm,getdate()) as varchar(2))
end +
case
when len(cast(datepart(dd,getdate()) as varchar(2))) = 1 then
'0' + cast(datepart(dd,getdate()) as varchar(2))
else cast(datepart(dd,getdate()) as varchar(2))
end +
case
when len(cast(datepart(hh,getdate()) as varchar(2))) = 1 then
'0' + cast(datepart(hh,getdate()) as varchar(2))
else cast(datepart(hh,getdate()) as varchar(2))
end +
case
when len(cast(datepart(mi,getdate()) as varchar(2))) = 1 then
'0' + cast(datepart(mi,getdate()) as varchar(2))
else cast(datepart(mi,getdate()) as varchar(2))
end) + '.BAK'

set @filename = N'V:\DatabaseBackup\elldrc\elldrc_db_' + N''
set @filebak = @filename + @timenow
BACKUP DATABASE [elldrc] TO DISK = @filename WITH NOFORMAT, NOINIT, NAME = N'elldrc-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'elldrc' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'elldrc' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''elldrc'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = @filebak WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO

My problem is, the variable filebak, that is in bold, could not be recognized though it has been declared before because it is actually a local variable. Does SQL Server 2005 have syntax to define public/global variables? Or the only way is I have to re-define all variables again? Thanks in advance for your answers.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-13 : 23:23:36

remove the GO.

GO signals the end of batch file and variable declare before GO is not recognized after GO.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-13 : 23:26:18

And
@timenow can be obtain with


select	@timenow  = convert(varchar(8), getdate(), 112) + replace(convert(varchar(5), getdate(), 108), ':', '')

nicoart
Starting Member

11 Posts

Posted - 2006-11-13 : 23:32:17

Yes... thanks khtan.

Also, could you please tell me, there's command:
set @filename = N'V:\DatabaseBackup\elldrc\elldrc_db_'
what does the N in bold mean?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-14 : 00:00:08

N - Unicode string

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources