Differential Backup did not work

Hi all,
I've been using this stored proc to do full and log backups for a while
and it's working perfectly. Now, the requirements have changed I've to
run full backup at 7:00PM and differential at 5:00AM. The full and log
backups are still working not differential. Something is wroing in my
codes on differentail backup task.
Thanks so much,
Silaphet,
Here is the error report from the job history:
Executed as user: NT AUTHORITY\SYSTEM. Incorrect syntax near the
keyword 'TO'. [SQLSTATE 42000] (Error 156). The step failed.
Below is my stored proc code:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- drop proc spOM_BackUpDB
--Exec spOM_BackUpDB 'FULL', 'CLI', 'ZION_PROD', 4
--Exec spOM_BackUpDB 'FULL', 'CLI', 'socConfig', 4
--Exec spOM_BackUpDB 'FULL', 'CLI', 'ObjectManager', 4
--Exec spOM_BackUpDB 'TRAN', 'CLI', 'ZION_PROD', 4
--Exec spOM_BackUpDB 'TRAN', 'CLI', 'socConfig', 4
--Exec spOM_BackUpDB 'TRAN', 'CLI', 'ObjectManager', 4
ALTER Proc spOM_BackUpDB_Test
@.Full_TranChar(4),--FULL or TRAN
@.ServerAliasVarChar(5),--CLI or FIN or OM or RPT
@.db_nameVarchar(150),--Name of Database
@.StripesInt--Number of stripes to dump it to
AS
Declare
@.cmd varchar(8000),
@.rcmd varchar (8000),
@.File Varchar(8000),
@.UNCPathVarchar(8000),
@.ServerNameVarchar(200),--Name of server
@.StripeNumint,
@.RuntimeDateTime,
@.CompressFileVarchar(8000)
Set @.File = ''
Set @.ServerName = @.@.servername
Set @.UNCPath = '\\Houdbs0101\K_Drive\' + @.ServerName +'\' + @.db_name +
'\'
Set @.StripeNum = 1
Set @.Runtime = GetDate()
SELECT @.FILE = @.uncpath + @.db_name + '_' + @.Full_Tran + '_' +
convert(varchar(24),@.Runtime,112) +
replace(convert(varchar(5),@.Runtime,114),':','') + '_' +
Convert(Varchar(3),@.StripeNum) + '.Bak'
Select @.CompressFile = @.uncpath + @.db_name + '_' + @.Full_Tran + '_' +
convert(varchar(24),@.Runtime,112) +
replace(convert(varchar(5),@.Runtime,114),':','') + '*.*'
Set @.StripeNum = @.StripeNum + 1
If @.Full_Tran = 'FULL'
Begin
set @.cmd = ' BACKUP DATABASE ' + @.db_name
End
DECLARE @.Full_TranChar(4)
IF @.Full_Tran = 'DIFF'
BEGIN
set @.cmd = ' BACKUP DATABASE ' + @.db_name + +
'WITH DIFFERENTIAL '
END
If @.Full_Tran = 'TRAN'
Begin
set @.cmd = ' BACKUP LOG ' + @.db_name
End
set @.cmd = @.cmd + ' TO DISK = ''''' + @.FILE + ''''''
While @.StripeNum <= @.Stripes
Begin
set @.cmd = @.cmd + ', DISK = ''''' + @.uncpath + @.db_name + '_' +
@.Full_Tran + '_' + convert(varchar(24),@.Runtime,112) +
replace(convert(varchar(5),@.Runtime,114),':','') + '_' +
Convert(Varchar(3),@.StripeNum) + '.Bak' + ''''''
Set @.StripeNum = @.StripeNum + 1
End
set @.Rcmd = '[' + @.ServerAlias + '].master.dbo.sp_sqlexec ''' + @.cmd +
''''
Select @.Rcmd
EXECUTE (@.Rcmd)
/*
Set @.cmd = '"\\Houdbs0101\L_Drive\WinRAR\RAR.exe" a -ep "' + @.uncpath +
@.db_name + '_' + @.Full_Tran + '_' + convert(varchar(24),@.Runtime,112) +
replace(convert(varchar(24),@.Runtime,114),':','') + '.rar' + '" "' +
@.CompressFile + '"'
Select @.cmd
set @.Rcmd = '[' + @.ServerAlias + '].master.dbo.xp_cmdshell ''' + @.cmd +
''''
Select @.Rcmd
EXECUTE (@.Rcmd)
*/
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Under the section "IF @.Full_Tran = 'DIFF'", it should be:
BEGIN
SET @.cmd = ' BACKUP DATABASE ' + @.db_name + ' WITH DIFFERENTIAL '
END
Remove the extra + sign and add a space before the word "WITH"
"Silaphet" wrote:

> Hi all,
> I've been using this stored proc to do full and log backups for a while
> and it's working perfectly. Now, the requirements have changed I've to
> run full backup at 7:00PM and differential at 5:00AM. The full and log
> backups are still working not differential. Something is wroing in my
> codes on differentail backup task.
> Thanks so much,
> Silaphet,
> Here is the error report from the job history:
> Executed as user: NT AUTHORITY\SYSTEM. Incorrect syntax near the
> keyword 'TO'. [SQLSTATE 42000] (Error 156). The step failed.
>
> Below is my stored proc code:
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> -- drop proc spOM_BackUpDB
> --Exec spOM_BackUpDB 'FULL', 'CLI', 'ZION_PROD', 4
> --Exec spOM_BackUpDB 'FULL', 'CLI', 'socConfig', 4
> --Exec spOM_BackUpDB 'FULL', 'CLI', 'ObjectManager', 4
> --Exec spOM_BackUpDB 'TRAN', 'CLI', 'ZION_PROD', 4
> --Exec spOM_BackUpDB 'TRAN', 'CLI', 'socConfig', 4
> --Exec spOM_BackUpDB 'TRAN', 'CLI', 'ObjectManager', 4
> ALTER Proc spOM_BackUpDB_Test
> @.Full_TranChar(4),--FULL or TRAN
> @.ServerAliasVarChar(5),--CLI or FIN or OM or RPT
> @.db_nameVarchar(150),--Name of Database
> @.StripesInt--Number of stripes to dump it to
> AS
>
> Declare
> @.cmd varchar(8000),
> @.rcmd varchar (8000),
> @.File Varchar(8000),
> @.UNCPathVarchar(8000),
> @.ServerNameVarchar(200),--Name of server
> @.StripeNumint,
> @.RuntimeDateTime,
> @.CompressFileVarchar(8000)
>
> Set @.File = ''
> Set @.ServerName = @.@.servername
> Set @.UNCPath = '\\Houdbs0101\K_Drive\' + @.ServerName +'\' + @.db_name +
> '\'
> Set @.StripeNum = 1
> Set @.Runtime = GetDate()
> SELECT @.FILE = @.uncpath + @.db_name + '_' + @.Full_Tran + '_' +
> convert(varchar(24),@.Runtime,112) +
> replace(convert(varchar(5),@.Runtime,114),':','') + '_' +
> Convert(Varchar(3),@.StripeNum) + '.Bak'
> Select @.CompressFile = @.uncpath + @.db_name + '_' + @.Full_Tran + '_' +
> convert(varchar(24),@.Runtime,112) +
> replace(convert(varchar(5),@.Runtime,114),':','') + '*.*'
> Set @.StripeNum = @.StripeNum + 1
> If @.Full_Tran = 'FULL'
> Begin
> set @.cmd = ' BACKUP DATABASE ' + @.db_name
> End
> DECLARE @.Full_TranChar(4)
> IF @.Full_Tran = 'DIFF'
> BEGIN
> set @.cmd = ' BACKUP DATABASE ' + @.db_name + +
> 'WITH DIFFERENTIAL '
> END
> If @.Full_Tran = 'TRAN'
> Begin
> set @.cmd = ' BACKUP LOG ' + @.db_name
> End
> set @.cmd = @.cmd + ' TO DISK = ''''' + @.FILE + ''''''
>
> While @.StripeNum <= @.Stripes
> Begin
> set @.cmd = @.cmd + ', DISK = ''''' + @.uncpath + @.db_name + '_' +
> @.Full_Tran + '_' + convert(varchar(24),@.Runtime,112) +
> replace(convert(varchar(5),@.Runtime,114),':','') + '_' +
> Convert(Varchar(3),@.StripeNum) + '.Bak' + ''''''
> Set @.StripeNum = @.StripeNum + 1
> End
> set @.Rcmd = '[' + @.ServerAlias + '].master.dbo.sp_sqlexec ''' + @.cmd +
> ''''
> Select @.Rcmd
> EXECUTE (@.Rcmd)
> /*
> Set @.cmd = '"\\Houdbs0101\L_Drive\WinRAR\RAR.exe" a -ep "' + @.uncpath +
> @.db_name + '_' + @.Full_Tran + '_' + convert(varchar(24),@.Runtime,112) +
> replace(convert(varchar(24),@.Runtime,114),':','') + '.rar' + '" "' +
> @.CompressFile + '"'
> Select @.cmd
> set @.Rcmd = '[' + @.ServerAlias + '].master.dbo.xp_cmdshell ''' + @.cmd +
> ''''
> Select @.Rcmd
> EXECUTE (@.Rcmd)
> */
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>

Differential Backup did not work

Hi all,
I've been using this stored proc to do full and log backups for a while
and it's working perfectly. Now, the requirements have changed I've to
run full backup at 7:00PM and differential at 5:00AM. The full and log
backups are still working not differential. Something is wroing in my
codes on differentail backup task.
Thanks so much,
Silaphet,
Here is the error report from the job history:
Executed as user: NT AUTHORITY\SYSTEM. Incorrect syntax near the
keyword 'TO'. [SQLSTATE 42000] (Error 156). The step failed.
Below is my stored proc code:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- drop proc spOM_BackUpDB
-- Exec spOM_BackUpDB 'FULL', 'CLI', 'ZION_PROD', 4
-- Exec spOM_BackUpDB 'FULL', 'CLI', 'socConfig', 4
-- Exec spOM_BackUpDB 'FULL', 'CLI', 'ObjectManager', 4
-- Exec spOM_BackUpDB 'TRAN', 'CLI', 'ZION_PROD', 4
-- Exec spOM_BackUpDB 'TRAN', 'CLI', 'socConfig', 4
-- Exec spOM_BackUpDB 'TRAN', 'CLI', 'ObjectManager', 4
ALTER Proc spOM_BackUpDB_Test
@.Full_Tran Char(4), --FULL or TRAN
@.ServerAlias VarChar(5), --CLI or FIN or OM or RPT
@.db_name Varchar(150), --Name of Database
@.Stripes Int --Number of stripes to dump it to
AS
Declare
@.cmd varchar(8000),
@.rcmd varchar (8000),
@.File Varchar(8000),
@.UNCPath Varchar(8000),
@.ServerName Varchar(200), --Name of server
@.StripeNum int,
@.Runtime DateTime,
@.CompressFile Varchar(8000)
Set @.File = ''
Set @.ServerName = @.@.servername
Set @.UNCPath = '\\Houdbs0101\K_Drive' + @.ServerName +'' + @.db_name +
''
Set @.StripeNum = 1
Set @.Runtime = GetDate()
SELECT @.FILE = @.uncpath + @.db_name + '_' + @.Full_Tran + '_' +
convert(varchar(24),@.Runtime,112) +
replace(convert(varchar(5),@.Runtime,114)
,':','') + '_' +
Convert(Varchar(3),@.StripeNum) + '.Bak'
Select @.CompressFile = @.uncpath + @.db_name + '_' + @.Full_Tran + '_' +
convert(varchar(24),@.Runtime,112) +
replace(convert(varchar(5),@.Runtime,114)
,':','') + '*.*'
Set @.StripeNum = @.StripeNum + 1
If @.Full_Tran = 'FULL'
Begin
set @.cmd = ' BACKUP DATABASE ' + @.db_name
End
DECLARE @.Full_Tran Char(4)
IF @.Full_Tran = 'DIFF'
BEGIN
set @.cmd = ' BACKUP DATABASE ' + @.db_name + +
'WITH DIFFERENTIAL '
END
If @.Full_Tran = 'TRAN'
Begin
set @.cmd = ' BACKUP LOG ' + @.db_name
End
set @.cmd = @.cmd + ' TO DISK = '' + @.FILE + '''
While @.StripeNum <= @.Stripes
Begin
set @.cmd = @.cmd + ', DISK = '' + @.uncpath + @.db_name + '_' +
@.Full_Tran + '_' + convert(varchar(24),@.Runtime,112) +
replace(convert(varchar(5),@.Runtime,114)
,':','') + '_' +
Convert(Varchar(3),@.StripeNum) + '.Bak' + '''
Set @.StripeNum = @.StripeNum + 1
End
set @.Rcmd = '[' + @.ServerAlias + '].master.dbo.sp_sqlexec ''' + @.cmd +
''''
Select @.Rcmd
EXECUTE (@.Rcmd)
/*
Set @.cmd = '"\\Houdbs0101\L_Drive\WinRAR\RAR.exe" a -ep "' + @.uncpath +
@.db_name + '_' + @.Full_Tran + '_' + convert(varchar(24),@.Runtime,112) +
replace(convert(varchar(24),@.Runtime,114
),':','') + '.rar' + '" "' +
@.CompressFile + '"'
Select @.cmd
set @.Rcmd = '[' + @.ServerAlias + '].master.dbo.xp_cmdshell ''' + @.cmd +
''''
Select @.Rcmd
EXECUTE (@.Rcmd)
*/
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOUnder the section "IF @.Full_Tran = 'DIFF'", it should be:
BEGIN
SET @.cmd = ' BACKUP DATABASE ' + @.db_name + ' WITH DIFFERENTIAL '
END
Remove the extra + sign and add a space before the word "WITH"
"Silaphet" wrote:

> Hi all,
> I've been using this stored proc to do full and log backups for a while
> and it's working perfectly. Now, the requirements have changed I've to
> run full backup at 7:00PM and differential at 5:00AM. The full and log
> backups are still working not differential. Something is wroing in my
> codes on differentail backup task.
> Thanks so much,
> Silaphet,
> Here is the error report from the job history:
> Executed as user: NT AUTHORITY\SYSTEM. Incorrect syntax near the
> keyword 'TO'. [SQLSTATE 42000] (Error 156). The step failed.
>
> Below is my stored proc code:
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> -- drop proc spOM_BackUpDB
> -- Exec spOM_BackUpDB 'FULL', 'CLI', 'ZION_PROD', 4
> -- Exec spOM_BackUpDB 'FULL', 'CLI', 'socConfig', 4
> -- Exec spOM_BackUpDB 'FULL', 'CLI', 'ObjectManager', 4
> -- Exec spOM_BackUpDB 'TRAN', 'CLI', 'ZION_PROD', 4
> -- Exec spOM_BackUpDB 'TRAN', 'CLI', 'socConfig', 4
> -- Exec spOM_BackUpDB 'TRAN', 'CLI', 'ObjectManager', 4
> ALTER Proc spOM_BackUpDB_Test
> @.Full_Tran Char(4), --FULL or TRAN
> @.ServerAlias VarChar(5), --CLI or FIN or OM or RPT
> @.db_name Varchar(150), --Name of Database
> @.Stripes Int --Number of stripes to dump it to
> AS
>
> Declare
> @.cmd varchar(8000),
> @.rcmd varchar (8000),
> @.File Varchar(8000),
> @.UNCPath Varchar(8000),
> @.ServerName Varchar(200), --Name of server
> @.StripeNum int,
> @.Runtime DateTime,
> @.CompressFile Varchar(8000)
>
> Set @.File = ''
> Set @.ServerName = @.@.servername
> Set @.UNCPath = '\\Houdbs0101\K_Drive' + @.ServerName +'' + @.db_name +
> ''
> Set @.StripeNum = 1
> Set @.Runtime = GetDate()
> SELECT @.FILE = @.uncpath + @.db_name + '_' + @.Full_Tran + '_' +
> convert(varchar(24),@.Runtime,112) +
> replace(convert(varchar(5),@.Runtime,114)
,':','') + '_' +
> Convert(Varchar(3),@.StripeNum) + '.Bak'
> Select @.CompressFile = @.uncpath + @.db_name + '_' + @.Full_Tran + '_' +
> convert(varchar(24),@.Runtime,112) +
> replace(convert(varchar(5),@.Runtime,114)
,':','') + '*.*'
> Set @.StripeNum = @.StripeNum + 1
> If @.Full_Tran = 'FULL'
> Begin
> set @.cmd = ' BACKUP DATABASE ' + @.db_name
> End
> DECLARE @.Full_Tran Char(4)
> IF @.Full_Tran = 'DIFF'
> BEGIN
> set @.cmd = ' BACKUP DATABASE ' + @.db_name + +
> 'WITH DIFFERENTIAL '
> END
> If @.Full_Tran = 'TRAN'
> Begin
> set @.cmd = ' BACKUP LOG ' + @.db_name
> End
> set @.cmd = @.cmd + ' TO DISK = '' + @.FILE + '''
>
> While @.StripeNum <= @.Stripes
> Begin
> set @.cmd = @.cmd + ', DISK = '' + @.uncpath + @.db_name + '_' +
> @.Full_Tran + '_' + convert(varchar(24),@.Runtime,112) +
> replace(convert(varchar(5),@.Runtime,114)
,':','') + '_' +
> Convert(Varchar(3),@.StripeNum) + '.Bak' + '''
> Set @.StripeNum = @.StripeNum + 1
> End
> set @.Rcmd = '[' + @.ServerAlias + '].master.dbo.sp_sqlexec ''' + @.cmd +
> ''''
> Select @.Rcmd
> EXECUTE (@.Rcmd)
> /*
> Set @.cmd = '"\\Houdbs0101\L_Drive\WinRAR\RAR.exe" a -ep "' + @.uncpath +
> @.db_name + '_' + @.Full_Tran + '_' + convert(varchar(24),@.Runtime,112) +
> replace(convert(varchar(24),@.Runtime,114
),':','') + '.rar' + '" "' +
> @.CompressFile + '"'
> Select @.cmd
> set @.Rcmd = '[' + @.ServerAlias + '].master.dbo.xp_cmdshell ''' + @.cmd
+
> ''''
> Select @.Rcmd
> EXECUTE (@.Rcmd)
> */
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>

Differential Backup did not work

Hi all,
I've been using this stored proc to do full and log backups for a while
and it's working perfectly. Now, the requirements have changed I've to
run full backup at 7:00PM and differential at 5:00AM. The full and log
backups are still working not differential. Something is wroing in my
codes on differentail backup task.
Thanks so much,
Silaphet,
Here is the error report from the job history:
Executed as user: NT AUTHORITY\SYSTEM. Incorrect syntax near the
keyword 'TO'. [SQLSTATE 42000] (Error 156). The step failed.
Below is my stored proc code:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- drop proc spOM_BackUpDB
-- Exec spOM_BackUpDB 'FULL', 'CLI', 'ZION_PROD', 4
-- Exec spOM_BackUpDB 'FULL', 'CLI', 'socConfig', 4
-- Exec spOM_BackUpDB 'FULL', 'CLI', 'ObjectManager', 4
-- Exec spOM_BackUpDB 'TRAN', 'CLI', 'ZION_PROD', 4
-- Exec spOM_BackUpDB 'TRAN', 'CLI', 'socConfig', 4
-- Exec spOM_BackUpDB 'TRAN', 'CLI', 'ObjectManager', 4
ALTER Proc spOM_BackUpDB_Test
@.Full_Tran Char(4), --FULL or TRAN
@.ServerAlias VarChar(5), --CLI or FIN or OM or RPT
@.db_name Varchar(150), --Name of Database
@.Stripes Int --Number of stripes to dump it to
AS
Declare
@.cmd varchar(8000),
@.rcmd varchar (8000),
@.File Varchar(8000),
@.UNCPath Varchar(8000),
@.ServerName Varchar(200), --Name of server
@.StripeNum int,
@.Runtime DateTime,
@.CompressFile Varchar(8000)
Set @.File = ''
Set @.ServerName = @.@.servername
Set @.UNCPath = '\\Houdbs0101\K_Drive\' + @.ServerName +'\' + @.db_name +
'\'
Set @.StripeNum = 1
Set @.Runtime = GetDate()
SELECT @.FILE = @.uncpath + @.db_name + '_' + @.Full_Tran + '_' +
convert(varchar(24),@.Runtime,112) +
replace(convert(varchar(5),@.Runtime,114),':','') + '_' +
Convert(Varchar(3),@.StripeNum) + '.Bak'
Select @.CompressFile = @.uncpath + @.db_name + '_' + @.Full_Tran + '_' +
convert(varchar(24),@.Runtime,112) +
replace(convert(varchar(5),@.Runtime,114),':','') + '*.*'
Set @.StripeNum = @.StripeNum + 1
If @.Full_Tran = 'FULL'
Begin
set @.cmd = ' BACKUP DATABASE ' + @.db_name
End
DECLARE @.Full_Tran Char(4)
IF @.Full_Tran = 'DIFF'
BEGIN
set @.cmd = ' BACKUP DATABASE ' + @.db_name + +
'WITH DIFFERENTIAL '
END
If @.Full_Tran = 'TRAN'
Begin
set @.cmd = ' BACKUP LOG ' + @.db_name
End
set @.cmd = @.cmd + ' TO DISK = '' + @.FILE + '''
While @.StripeNum <= @.Stripes
Begin
set @.cmd = @.cmd + ', DISK = '' + @.uncpath + @.db_name + '_' +
@.Full_Tran + '_' + convert(varchar(24),@.Runtime,112) +
replace(convert(varchar(5),@.Runtime,114),':','') + '_' +
Convert(Varchar(3),@.StripeNum) + '.Bak' + '''
Set @.StripeNum = @.StripeNum + 1
End
set @.Rcmd = '[' + @.ServerAlias + '].master.dbo.sp_sqlexec ''' + @.cmd +
''''
Select @.Rcmd
EXECUTE (@.Rcmd)
/*
Set @.cmd = '"\\Houdbs0101\L_Drive\WinRAR\RAR.exe" a -ep "' + @.uncpath +
@.db_name + '_' + @.Full_Tran + '_' + convert(varchar(24),@.Runtime,112) +
replace(convert(varchar(24),@.Runtime,114),':','') + '.rar' + '" "' +
@.CompressFile + '"'
Select @.cmd
set @.Rcmd = '[' + @.ServerAlias + '].master.dbo.xp_cmdshell ''' + @.cmd +
''''
Select @.Rcmd
EXECUTE (@.Rcmd)
*/
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOUnder the section "IF @.Full_Tran = 'DIFF'", it should be:
BEGIN
SET @.cmd = ' BACKUP DATABASE ' + @.db_name + ' WITH DIFFERENTIAL '
END
Remove the extra + sign and add a space before the word "WITH"
"Silaphet" wrote:
> Hi all,
> I've been using this stored proc to do full and log backups for a while
> and it's working perfectly. Now, the requirements have changed I've to
> run full backup at 7:00PM and differential at 5:00AM. The full and log
> backups are still working not differential. Something is wroing in my
> codes on differentail backup task.
> Thanks so much,
> Silaphet,
> Here is the error report from the job history:
> Executed as user: NT AUTHORITY\SYSTEM. Incorrect syntax near the
> keyword 'TO'. [SQLSTATE 42000] (Error 156). The step failed.
>
> Below is my stored proc code:
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> -- drop proc spOM_BackUpDB
> -- Exec spOM_BackUpDB 'FULL', 'CLI', 'ZION_PROD', 4
> -- Exec spOM_BackUpDB 'FULL', 'CLI', 'socConfig', 4
> -- Exec spOM_BackUpDB 'FULL', 'CLI', 'ObjectManager', 4
> -- Exec spOM_BackUpDB 'TRAN', 'CLI', 'ZION_PROD', 4
> -- Exec spOM_BackUpDB 'TRAN', 'CLI', 'socConfig', 4
> -- Exec spOM_BackUpDB 'TRAN', 'CLI', 'ObjectManager', 4
> ALTER Proc spOM_BackUpDB_Test
> @.Full_Tran Char(4), --FULL or TRAN
> @.ServerAlias VarChar(5), --CLI or FIN or OM or RPT
> @.db_name Varchar(150), --Name of Database
> @.Stripes Int --Number of stripes to dump it to
> AS
>
> Declare
> @.cmd varchar(8000),
> @.rcmd varchar (8000),
> @.File Varchar(8000),
> @.UNCPath Varchar(8000),
> @.ServerName Varchar(200), --Name of server
> @.StripeNum int,
> @.Runtime DateTime,
> @.CompressFile Varchar(8000)
>
> Set @.File = ''
> Set @.ServerName = @.@.servername
> Set @.UNCPath = '\\Houdbs0101\K_Drive\' + @.ServerName +'\' + @.db_name +
> '\'
> Set @.StripeNum = 1
> Set @.Runtime = GetDate()
> SELECT @.FILE = @.uncpath + @.db_name + '_' + @.Full_Tran + '_' +
> convert(varchar(24),@.Runtime,112) +
> replace(convert(varchar(5),@.Runtime,114),':','') + '_' +
> Convert(Varchar(3),@.StripeNum) + '.Bak'
> Select @.CompressFile = @.uncpath + @.db_name + '_' + @.Full_Tran + '_' +
> convert(varchar(24),@.Runtime,112) +
> replace(convert(varchar(5),@.Runtime,114),':','') + '*.*'
> Set @.StripeNum = @.StripeNum + 1
> If @.Full_Tran = 'FULL'
> Begin
> set @.cmd = ' BACKUP DATABASE ' + @.db_name
> End
> DECLARE @.Full_Tran Char(4)
> IF @.Full_Tran = 'DIFF'
> BEGIN
> set @.cmd = ' BACKUP DATABASE ' + @.db_name + +
> 'WITH DIFFERENTIAL '
> END
> If @.Full_Tran = 'TRAN'
> Begin
> set @.cmd = ' BACKUP LOG ' + @.db_name
> End
> set @.cmd = @.cmd + ' TO DISK = '' + @.FILE + '''
>
> While @.StripeNum <= @.Stripes
> Begin
> set @.cmd = @.cmd + ', DISK = '' + @.uncpath + @.db_name + '_' +
> @.Full_Tran + '_' + convert(varchar(24),@.Runtime,112) +
> replace(convert(varchar(5),@.Runtime,114),':','') + '_' +
> Convert(Varchar(3),@.StripeNum) + '.Bak' + '''
> Set @.StripeNum = @.StripeNum + 1
> End
> set @.Rcmd = '[' + @.ServerAlias + '].master.dbo.sp_sqlexec ''' + @.cmd +
> ''''
> Select @.Rcmd
> EXECUTE (@.Rcmd)
> /*
> Set @.cmd = '"\\Houdbs0101\L_Drive\WinRAR\RAR.exe" a -ep "' + @.uncpath +
> @.db_name + '_' + @.Full_Tran + '_' + convert(varchar(24),@.Runtime,112) +
> replace(convert(varchar(24),@.Runtime,114),':','') + '.rar' + '" "' +
> @.CompressFile + '"'
> Select @.cmd
> set @.Rcmd = '[' + @.ServerAlias + '].master.dbo.xp_cmdshell ''' + @.cmd +
> ''''
> Select @.Rcmd
> EXECUTE (@.Rcmd)
> */
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>

Different execution plan between stored proc and ad-hoc query

We have just encountered a problem with a particular stored procedure which I would appreciate anyone's thoughts on.

The proc in question contains a single select statement which simply joins four tables and filters based on two parameters. It normally runs in well under a second but this morning we came in to find it was running in anything up to a minute.

After investigation we found that it was running a clustered index scan on one of the larger tables (approx 10.5m rows) rather than using the appropriate index. However, when we took the select statement out and ran it as an ad-hoc query it was utilising the index and performing correctly.

We tried recompiling the proc and also ran sp_updatestats on the database but we were still getting the different execution plans being generated. It was only after we ran UPDATE STATISTICS WITH FULLSCAN on the table in question that the proc went back to performing normally.

Can anyone shed any light on why the proc and query were using different execution plans, even after recompiling the proc? The table is heavily utilised in all areas of the system so I could understand if the stats got out of date (although autostats is on and sp_updatestats does get run on the database 2 or 3 times a day through an automated process).

Also, this is the second week in a row where this behaviour has occured so we need to try and mitigate the risks next week. Obviously we could schedule a stats update with fullscan early Monday morning but we would like to understand what is causing this and try and fix it "properly".

cheers

James

If the system is heavily updated, the stats could get stale. Consider running [sp_updatestats 'resample'] or explicitly forcing an index (the latter is not really recommended unless you fully understand the consequences).

e.g.

select * from tb with (index(myindex))

|||

It's very quite possible to get two different plans. Take for example these two TSQL statements:

a)

declare @.x int

set @.x = 99

select * from t1 where col1 = @.x

b)

select * from t1 where col1=99

In a), the optimizer evalutes the whole batch, and cannot evalute the literal value of 99 since it's a parameter value set inside a batch. So it makes a guesstimate and optimizes for a given value which it thinks may be the most correct/optimal. Often times it is, often times it is not. In b), the statement is evaluated and immediately it knows the value of 99, so it can get a very accurate estimate, thus producing a more reliable plan.

The same holds true for stored procedures. If you create a stored procedure and pass in a parameter value used in your WHERE clause, the optimizer will most likely produce a far better estimate than if you created a procedure which declared variables, set them, and then used them in your WHERE clause. It's always beneficial in a proc to try not to declare variables, set them and then use them in a query. If you can pass them as a parameter, the query has a better chance of producing an optimal plan based on accurate estimates.

Where you can also get into trouble is if the plan is cached with a value that gave good estimates/performance at the time it was created, but then as time passes and changes occur to the tables, yes the stats can get out of date, and you'll need to either update stats or clear the cache to remove the stale stats/plan.

If you got lost with what i was trying to say above, forgive me, I'm sure it's documented in some whitepaper somewhere, but I know this is an issue as merge replication procs and triggers hit this kind of problem often.

|||

Thank you both for your comments. Greg, that makes sense and does explain why we may have been seeing the different plan.

This problem actually appeared again yesterday, although this time the ad-hoc query was using the same (flawed) plan as the proc. We have decided in this instance to use an index hint as there are really no circumstances where it should be doing a full scan. As OJ says, I know these are not really recommended but I think I'm happy for this to be one of the rare expections!

cheers

James

|||Which version of SQL Server are you using? If 2005 then you might opt for the "Optimize For" hint or "Option Recompile". When using an index hint, you run the risk of accidentally breaking code later if that index is dropped or changed to include different columns.

different database connection in clr stored proc

Hi,
Is it possible, in a CLR Stored Procedure, to connect to a different
database on the same server as the database under which the current
connection is for/running?
In other words, within a CLR Stored Proc running on database A, is it
possible to then connect to Database B, and insert records into tables in
Database B from data contained in tables in Database A, where both Database
A
and Database B reside on the same instance of SQL Server 2005 (same server)?
Thanks for any help or ideas on this.Thank you for posting in the MSDN newsgroup.
As for SQL 2005 CLR stored procedure, we can establish other connection to
external database(even external database server instance/remote) in the CLR
sp's code. e.g.
=======================
public class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void PriceSum(out SqlString value)
{
StringBuilder sb = new StringBuilder();
using (SqlConnection connection = new SqlConnection("context
connection=true"))
{
//access the same database's resource...
}
// access an external database on the same server/same instance
using (SqlConnection conn = new SqlConnection("Data
Source=localhost;Initial Catalog=AdventureWorks;Integrated Security=True"))
{
conn.Open();
SqlCommand comm = new SqlCommand("SELECT ProductCategoryID,
Name FROM Production.ProductCategory", conn);
SqlDataReader reader = comm.ExecuteReader();
using (reader)
{
while (reader.Read())
{
sb.Append(")(" + reader.GetString(1));
}
}
}
value = new SqlString(sb.ToString());
}
}
========================
And as for such CLR assembly, you need to grant it "External Access"
permission(safe is insufficient).
Hope this helps.
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi,
Thanks for that Steven! I granted my assembly "External Access" as opposed
to "Safe", and it compiled fine. However, when I attempted to deploy it to m
y
SQL Server, i got the following error message:
CREATE ASSEMBLY for assembly 'MyCLRStoredProcTest' failed because assembly '
MyCLRStoredProcTest ' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS
.
The assembly is authorized when either of the following is true: the
database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the databas
e
has the TRUSTWORTHY database property on; or the assembly is signed with a
certificate or an asymmetric key that has a corresponding login with EXTERNA
L
ACCESS ASSEMBLY permission.
I'm guessing for best security, I should sign my assembly with a certificate
or an asymmetric key. Would you be able to suggest where to look to find out
what is involved with that? (and the other 'fix' options noted in my error
message) Thanks.
Best Regards,
Mark
"Steven Cheng[MSFT]" wrote:

> Thank you for posting in the MSDN newsgroup.
> As for SQL 2005 CLR stored procedure, we can establish other connection to
> external database(even external database server instance/remote) in the CL
R
> sp's code. e.g.
> =======================
> public class StoredProcedures
> {
> [Microsoft.SqlServer.Server.SqlProcedure]
> public static void PriceSum(out SqlString value)
> {
> StringBuilder sb = new StringBuilder();
> using (SqlConnection connection = new SqlConnection("context
> connection=true"))
> {
> //access the same database's resource...
>
> }
>
> // access an external database on the same server/same instance
> using (SqlConnection conn = new SqlConnection("Data
> Source=localhost;Initial Catalog=AdventureWorks;Integrated Security=True")
)
> {
> conn.Open();
> SqlCommand comm = new SqlCommand("SELECT ProductCategoryID,
> Name FROM Production.ProductCategory", conn);
> SqlDataReader reader = comm.ExecuteReader();
> using (reader)
> {
> while (reader.Read())
> {
> sb.Append(")(" + reader.GetString(1));
> }
> }
> }
> value = new SqlString(sb.ToString());
> }
> }
> ========================
> And as for such CLR assembly, you need to grant it "External Access"
> permission(safe is insufficient).
> Hope this helps.
> Regards,
> Steven Cheng
> Microsoft Online Community Support
>
> ========================================
==========
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
==========
>
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
>
>|||Hello Mark,
The error message indicate that you need to turn on the "TRUSTWORTHY"
property of the database which will add an "external access" assembly.
Anyway, you can follow the below web article's steps which include the
complete things we need to configure when install a custom assembly which
will require External_Access:
#CLR Stored Procedure Calling External Web Service
http://davidhayden.com/blog/dave/ar...04/25/2924.aspx
Hope this helps.
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Thanks again Steven! Works beautifully. And thanks for the link to the
blog...very helpful.|||You're welcome Mark,
Have a nice day!
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)