DImension depends on other dimesion

I have a parent child hierarchy in a table.

Due to some requirements we need to create the same hierarchies for different years with different primary keys.

i have data for 2005 and 2006

when i create a new parent chile dimension I am getting all 2005 and 2006 hierarchies.

I have one more dimension where I list only years.

Now I want to fileter the first diemsion depending on second dimension.

I tried "depends on Dimension" property.

it is not allowing me to save the dimesion at all.

It is saying invalid parent child relationship

if i remove the "depends on Dimension" then every thing is working fine except filtering.

I need filtering of One dimesion from other.

Pleas help!!!!

Thanks

Pasupula

How to use "depends on Dimension" property for parent child relation dimension|||

This paper on Many-Many dimensional modelling in AS 2005 by Marco Russo may help you - page 73 discusses how to deal with multiple parent-child hierarchies. There is a Hierarchy attribute, which selects a specific hierarchy:

http://www.sqlbi.eu/Home/tabid/36/ctl/Details/mid/374/ItemID/7/Default.aspx

>>

...

Multiple Hierarchies

Parent-child dimensions are a useful feature of Analysis Services. They can be used to model hierarchical and fast changing dimensions like sales or employee organizations. A limitation of this feature is that you can define only one parent-child hierarchy in a dimension. In the real world, this may be an issue. For example, in the middle of a company reorganization, someone may need to analyze alternatively the present with the eyes of the past (actual data for previous organization hierarchy) and the past with the eyes of the present (past data for actual organization hierarchy).

...

>>

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 schema formats? (SQLXMLBulkLoad and VS2k3)

Howdy,
What is different between the schema requirements for SQLXMLBulkLoad
and the schema generated from XML with Visual Studio?
Specifically I'm trying to bulk load XML output from the gotdotnet
SharePoint Reports utility
(http://www.gotdotnet.com/workspaces...r />
cb29970eb)
with SQLXML 3 sp3 and MS SQL 2000 sp4. The schema was created using
Visual Studio 2003.
I've got the BOL examples working, including using SchemaGen to create
the required tables. However running the bulk load with my target XML
doesn't seem to do anything--no tables created, no data loaded, no
error at the command prompt, and no error log generated.
Any tips?
TIA,
Sean G.Hi,
If you send me the schema and the xml file I will take a look of your proble
m.
--
Thanks,
Monica Frintu
"SeanGerman@.gmail.com" wrote:

> Howdy,
> What is different between the schema requirements for SQLXMLBulkLoad
> and the schema generated from XML with Visual Studio?
> Specifically I'm trying to bulk load XML output from the gotdotnet
> SharePoint Reports utility
> (http://www.gotdotnet.com/workspaces.../>
accb29970eb)
> with SQLXML 3 sp3 and MS SQL 2000 sp4. The schema was created using
> Visual Studio 2003.
> I've got the BOL examples working, including using SchemaGen to create
> the required tables. However running the bulk load with my target XML
> doesn't seem to do anything--no tables created, no data loaded, no
> error at the command prompt, and no error log generated.
> Any tips?
>
> TIA,
>
> Sean G.
>|||Thanks for the offer Monica. But I've decided to build a schema by
hand rather than fix the one created by Visual Studio.
I don't know enough about XML and XSD to describe all the relevant
differences between the automatically created schema that doesn't work
and the manually created one that is working, but let me know if you're
interested in a comparison between what VS creates and what BulkLoad
wants.
Thanks again,
Sean G.|||Hi Sean,
Yes, it may be useful for us to look at the differences...
Thanks
Michael
<SeanGerman@.gmail.com> wrote in message
news:1136478947.978472.29760@.g47g2000cwa.googlegroups.com...
> Thanks for the offer Monica. But I've decided to build a schema by
> hand rather than fix the one created by Visual Studio.
> I don't know enough about XML and XSD to describe all the relevant
> differences between the automatically created schema that doesn't work
> and the manually created one that is working, but let me know if you're
> interested in a comparison between what VS creates and what BulkLoad
> wants.
> Thanks again,
>
> Sean G.
>|||Michael,
I'll be able to post the schema files this evening, but I can summarize
the differences. To reiterate, the xml is output from the gotdotnet
SharePoint Reports utility.
For the working file I used xsd:schema while the one generated by
Visual Studio 2k3 is xs:schema.
For the working file, elements have the minimum attributes--name,
type(, sql:relation, sql:relationship as needed).
The generated file has minOccurs, maxOccurs.
The working file has the xsd:annotation element and sql:relation and
sql:relationship attributes for SchemaGen=True.
The generated schema does not have this information, but still doesn't
work even after I add it.
There are some elements in the xml I have commented out in the
schema--primarily for dashes in element names--but those kick up an
"invalid value for 'column'" error during BulkLoad.
The thing that puzzles me is BulkLoad with the VS-generated schema
produces no (visible) output--no tables created, no data loaded, and no
error messages.
Other times I've seen this behavior from BulkLoad indicated a logical
error in the schema data. E.g. a well-formatted schema where the
element names don't match the names in the xml; or something labeled an
element in the schema is an attribute in the xml. But I can't find any
such issue in this schema. *shrug*
But like I said, I was able to create a (mostly, except for invalid
column names) working schema, though I wouldn't mind pinning down this
issue for the sake of edukashun. ;)
Sean G.

different schema formats? (SQLXMLBulkLoad and VS2k3)

Howdy,
What is different between the schema requirements for SQLXMLBulkLoad
and the schema generated from XML with Visual Studio?
Specifically I'm trying to bulk load XML output from the gotdotnet
SharePoint Reports utility
(http://www.gotdotnet.com/workspaces/...2-5accb29970eb)
with SQLXML 3 sp3 and MS SQL 2000 sp4. The schema was created using
Visual Studio 2003.
I've got the BOL examples working, including using SchemaGen to create
the required tables. However running the bulk load with my target XML
doesn't seem to do anything--no tables created, no data loaded, no
error at the command prompt, and no error log generated.
Any tips?
TIA,
Sean G.
Hi,
If you send me the schema and the xml file I will take a look of your problem.
Thanks,
Monica Frintu
"SeanGerman@.gmail.com" wrote:

> Howdy,
> What is different between the schema requirements for SQLXMLBulkLoad
> and the schema generated from XML with Visual Studio?
> Specifically I'm trying to bulk load XML output from the gotdotnet
> SharePoint Reports utility
> (http://www.gotdotnet.com/workspaces/...2-5accb29970eb)
> with SQLXML 3 sp3 and MS SQL 2000 sp4. The schema was created using
> Visual Studio 2003.
> I've got the BOL examples working, including using SchemaGen to create
> the required tables. However running the bulk load with my target XML
> doesn't seem to do anything--no tables created, no data loaded, no
> error at the command prompt, and no error log generated.
> Any tips?
>
> TIA,
>
> Sean G.
>
|||Thanks for the offer Monica. But I've decided to build a schema by
hand rather than fix the one created by Visual Studio.
I don't know enough about XML and XSD to describe all the relevant
differences between the automatically created schema that doesn't work
and the manually created one that is working, but let me know if you're
interested in a comparison between what VS creates and what BulkLoad
wants.
Thanks again,
Sean G.
|||Hi Sean,
Yes, it may be useful for us to look at the differences...
Thanks
Michael
<SeanGerman@.gmail.com> wrote in message
news:1136478947.978472.29760@.g47g2000cwa.googlegro ups.com...
> Thanks for the offer Monica. But I've decided to build a schema by
> hand rather than fix the one created by Visual Studio.
> I don't know enough about XML and XSD to describe all the relevant
> differences between the automatically created schema that doesn't work
> and the manually created one that is working, but let me know if you're
> interested in a comparison between what VS creates and what BulkLoad
> wants.
> Thanks again,
>
> Sean G.
>
|||Michael,
I'll be able to post the schema files this evening, but I can summarize
the differences. To reiterate, the xml is output from the gotdotnet
SharePoint Reports utility.
For the working file I used xsd:schema while the one generated by
Visual Studio 2k3 is xs:schema.
For the working file, elements have the minimum attributes--name,
type(, sql:relation, sql:relationship as needed).
The generated file has minOccurs, maxOccurs.
The working file has the xsd:annotation element and sql:relation and
sql:relationship attributes for SchemaGen=True.
The generated schema does not have this information, but still doesn't
work even after I add it.
There are some elements in the xml I have commented out in the
schema--primarily for dashes in element names--but those kick up an
"invalid value for 'column'" error during BulkLoad.
The thing that puzzles me is BulkLoad with the VS-generated schema
produces no (visible) output--no tables created, no data loaded, and no
error messages.
Other times I've seen this behavior from BulkLoad indicated a logical
error in the schema data. E.g. a well-formatted schema where the
element names don't match the names in the xml; or something labeled an
element in the schema is an attribute in the xml. But I can't find any
such issue in this schema. *shrug*
But like I said, I was able to create a (mostly, except for invalid
column names) working schema, though I wouldn't mind pinning down this
issue for the sake of edukashun. ;)
Sean G.

Differences between SQL Server 2005 on Windows 2000 or W2K3

Hi,
I know that the SQL Server 2005 Enterprise System Requirements said that
it's possible to install SQL Server 2005 on Windows 2000 Server.
But I'd like to know if there's known issues of features not working well on
W2K Server ?
Thanks for your help.
Regards
jeffHi Jeff
I have not tried this, as W2K3 has significantly improved I/O it would be
worthwhile upgrading.
John
"Jeff" wrote:

> Hi,
> I know that the SQL Server 2005 Enterprise System Requirements said that
> it's possible to install SQL Server 2005 on Windows 2000 Server.
> But I'd like to know if there's known issues of features not working well
on
> W2K Server ?
> Thanks for your help.
> Regards
> jeff

Differences between SQL Server 2005 on Windows 2000 or W2K3

Hi,
I know that the SQL Server 2005 Enterprise System Requirements said that
it's possible to install SQL Server 2005 on Windows 2000 Server.
But I'd like to know if there's known issues of features not working well on
W2K Server ?
Thanks for your help.
Regards
jeffHi Jeff
I have not tried this, as W2K3 has significantly improved I/O it would be
worthwhile upgrading.
John
"Jeff" wrote:
> Hi,
> I know that the SQL Server 2005 Enterprise System Requirements said that
> it's possible to install SQL Server 2005 on Windows 2000 Server.
> But I'd like to know if there's known issues of features not working well on
> W2K Server ?
> Thanks for your help.
> Regards
> jeff