Problem with Jobs... Plz help!

I have several jobs that run simple T-SQL scripts...
Most of them, fail, with the following problem:
"Executed as user: dba. The conversion of a char data type to a datetime
data type resulted in an out-of-range datetime value. [SQLSTATE 22007] (Error
242) Associated statement is not prepared [SQLSTATE HY007] (Error 0) Cursor
is not open. [SQLSTATE 42000] (Error 16917). The step failed."
Some parts are specific from a example job I'll post here, but the "char to
datetime conversion" error, appears in all of them.. There is transcript from
the Job that raised that error:
/***********/
DECLARE @.prt varchar (10), @.ano varchar (4), @.dias varchar (5), @.data
varchar (10), @.IP varchar (50), @.cmd varchar (500)
IF datepart (hh, getdate()) = 10-- or datepart (hh, getdate()) = 15
BEGIN
DECLARE cur_opv CURSOR FOR
select prot_nr_prt
,prot_nr_ano
,dias
,dh_recebimeto
,uaie_ds_sigla
from int_prot_jur.dbo.vw_OPV_AVISO_ANDAMENTO
OPEN cur_opv
FETCH NEXT FROM cur_opv
INTO @.prt, @.ano, @.dias, @.data, @.IP
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.cmd = 'xp_cmdshell ''net send ssouza "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providencia há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"'''
exec (@.cmd)
set @.cmd = 'xp_cmdshell ''net send fyueda "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providencia há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"'''
exec (@.cmd)
set @.cmd = 'xp_cmdshell ''net send rolima "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"'''
exec (@.cmd)
set @.cmd = 'xp_cmdshell ''net send pbarbosa "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"'''
exec (@.cmd)
set @.cmd = 'xp_cmdshell ''net send agallo "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"'''
exec (@.cmd)
set @.cmd = 'xp_cmdshell ''net send mntorres "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"'''
exec (@.cmd)
FETCH NEXT FROM cur_opv
INTO @.prt, @.ano, @.dias, @.data, @.IP
END
CLOSE cur_opv
DEALLOCATE cur_opv
END
/***********/
It's important to note that, the ONLY time when this script deal with some
datetime type, is on the DATEPART function on the If. This If is just to see
if it is 10 o'clock... It wasn't me who wrote this script, because it's
stupid to control the date by the script and not by the job schedule..
Anyways, I prefered to keep this way... But I don't think that those line of
code could be the problem...
These job errors are making me crazy! If someone know something about, plz
help me, I can send some other samples of jobs returning errors!
Thanks!!!
Rafa?
I don't see the problem either so try this...
1. check the view, make sure the problem is NOT in the view... Select from
the view etc in query analyzer. then
2. copy out the entire cursor loop in QA and test it. THEN (leaving OUT the
xp_cmdshell stuff - just get the loop going.)
3. add the IF statement with the datepart...
Sneak up on the problem in query analyzer...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Rafa" <Rafa@.discussions.microsoft.com> wrote in message
news:AB7004F1-6029-4DF0-BB5F-965587234C87@.microsoft.com...
> I have several jobs that run simple T-SQL scripts...
> Most of them, fail, with the following problem:
> "Executed as user: dba. The conversion of a char data type to a datetime
> data type resulted in an out-of-range datetime value. [SQLSTATE 22007]
(Error
> 242) Associated statement is not prepared [SQLSTATE HY007] (Error 0)
Cursor
> is not open. [SQLSTATE 42000] (Error 16917). The step failed."
> Some parts are specific from a example job I'll post here, but the "char
to
> datetime conversion" error, appears in all of them.. There is transcript
from
> the Job that raised that error:
> /***********/
> DECLARE @.prt varchar (10), @.ano varchar (4), @.dias varchar (5), @.data
> varchar (10), @.IP varchar (50), @.cmd varchar (500)
> IF datepart (hh, getdate()) = 10-- or datepart (hh, getdate()) = 15
> BEGIN
> DECLARE cur_opv CURSOR FOR
> select prot_nr_prt
> ,prot_nr_ano
> ,dias
> ,dh_recebimeto
> ,uaie_ds_sigla
> from int_prot_jur.dbo.vw_OPV_AVISO_ANDAMENTO
> OPEN cur_opv
> FETCH NEXT FROM cur_opv
> INTO @.prt, @.ano, @.dias, @.data, @.IP
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> set @.cmd = 'xp_cmdshell ''net send ssouza "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providencia h ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> set @.cmd = 'xp_cmdshell ''net send fyueda "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providencia h ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> set @.cmd = 'xp_cmdshell ''net send rolima "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providncias h ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> set @.cmd = 'xp_cmdshell ''net send pbarbosa "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providncias h ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> set @.cmd = 'xp_cmdshell ''net send agallo "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providncias h ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> set @.cmd = 'xp_cmdshell ''net send mntorres "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providncias h ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> FETCH NEXT FROM cur_opv
> INTO @.prt, @.ano, @.dias, @.data, @.IP
> END
> CLOSE cur_opv
> DEALLOCATE cur_opv
> END
> /***********/
> It's important to note that, the ONLY time when this script deal with some
> datetime type, is on the DATEPART function on the If. This If is just to
see
> if it is 10 o'clock... It wasn't me who wrote this script, because it's
> stupid to control the date by the script and not by the job schedule..
> Anyways, I prefered to keep this way... But I don't think that those line
of
> code could be the problem...
> These job errors are making me crazy! If someone know something about, plz
> help me, I can send some other samples of jobs returning errors!
> Thanks!!!
> Rafa
|||The view is Pretty Simple...
It don't even have a datetime field... Look at it's structure:
prot_nr_prtint
prot_nr_anosmallint
diasint
dh_recebimetovarchar(10)
uaie_ds_siglavarchar(12)
The strange thing about this, is that the same error occurs on other jobs...
And when I copy the T-SQL Statement to QA, it runs perfectly...
I don't know if it can be some Database option conflicting with some
statement the people are using on the procs...
"Wayne Snyder" wrote:

> I don't see the problem either so try this...
> 1. check the view, make sure the problem is NOT in the view... Select from
> the view etc in query analyzer. then
> 2. copy out the entire cursor loop in QA and test it. THEN (leaving OUT the
> xp_cmdshell stuff - just get the loop going.)
> 3. add the IF statement with the datepart...
> Sneak up on the problem in query analyzer...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Rafa?" <Rafa@.discussions.microsoft.com> wrote in message
> news:AB7004F1-6029-4DF0-BB5F-965587234C87@.microsoft.com...
> (Error
> Cursor
> to
> from
> '"'''
> '"'''
> '"'''
> '"'''
> '"'''
> '"'''
> see
> of
>
>
sql

Problem with Jobs... Plz help!

I have several jobs that run simple T-SQL scripts...
Most of them, fail, with the following problem:
"Executed as user: dba. The conversion of a char data type to a datetime
data type resulted in an out-of-range datetime value. [SQLSTATE 22007] (
Error
242) Associated statement is not prepared [SQLSTATE HY007] (Error 0) C
ursor
is not open. [SQLSTATE 42000] (Error 16917). The step failed."
Some parts are specific from a example job I'll post here, but the "char to
datetime conversion" error, appears in all of them.. There is transcript fro
m
the Job that raised that error:
/***********/
DECLARE @.prt varchar (10), @.ano varchar (4), @.dias varchar (5), @.data
varchar (10), @.IP varchar (50), @.cmd varchar (500)
IF datepart (hh, getdate()) = 10-- or datepart (hh, getdate()) = 15
BEGIN
DECLARE cur_opv CURSOR FOR
select prot_nr_prt
,prot_nr_ano
,dias
,dh_recebimeto
,uaie_ds_sigla
from int_prot_jur.dbo.vw_OPV_AVISO_ANDAMENTO
OPEN cur_opv
FETCH NEXT FROM cur_opv
INTO @.prt, @.ano, @.dias, @.data, @.IP
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.cmd = 'xp_cmdshell ''net send ssouza "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providencia há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"''
'
exec (@.cmd)
set @.cmd = 'xp_cmdshell ''net send fyueda "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providencia há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"''
'
exec (@.cmd)
set @.cmd = 'xp_cmdshell ''net send rolima "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"
'''
exec (@.cmd)
set @.cmd = 'xp_cmdshell ''net send pbarbosa "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"
'''
exec (@.cmd)
set @.cmd = 'xp_cmdshell ''net send agallo "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"
'''
exec (@.cmd)
set @.cmd = 'xp_cmdshell ''net send mntorres "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"
'''
exec (@.cmd)
FETCH NEXT FROM cur_opv
INTO @.prt, @.ano, @.dias, @.data, @.IP
END
CLOSE cur_opv
DEALLOCATE cur_opv
END
/***********/
It's important to note that, the ONLY time when this script deal with some
datetime type, is on the DATEPART function on the If. This If is just to see
if it is 10 o'clock... It wasn't me who wrote this script, because it's
stupid to control the date by the script and not by the job schedule..
Anyways, I prefered to keep this way... But I don't think that those line of
code could be the problem...
These job errors are making me crazy! If someone know something about, plz
help me, I can send some other samples of jobs returning errors!
Thanks!!!
Rafa?I don't see the problem either so try this...
1. check the view, make sure the problem is NOT in the view... Select from
the view etc in query analyzer. then
2. copy out the entire cursor loop in QA and test it. THEN (leaving OUT the
xp_cmdshell stuff - just get the loop going.)
3. add the IF statement with the datepart...
Sneak up on the problem in query analyzer...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Rafa" <Rafa@.discussions.microsoft.com> wrote in message
news:AB7004F1-6029-4DF0-BB5F-965587234C87@.microsoft.com...
> I have several jobs that run simple T-SQL scripts...
> Most of them, fail, with the following problem:
> "Executed as user: dba. The conversion of a char data type to a datetime
> data type resulted in an out-of-range datetime value. [SQLSTATE 22007]
(Error
> 242) Associated statement is not prepared [SQLSTATE HY007] (Error 0)
Cursor
> is not open. [SQLSTATE 42000] (Error 16917). The step failed."
> Some parts are specific from a example job I'll post here, but the "char
to
> datetime conversion" error, appears in all of them.. There is transcript
from
> the Job that raised that error:
> /***********/
> DECLARE @.prt varchar (10), @.ano varchar (4), @.dias varchar (5), @.data
> varchar (10), @.IP varchar (50), @.cmd varchar (500)
> IF datepart (hh, getdate()) = 10-- or datepart (hh, getdate()) = 15
> BEGIN
> DECLARE cur_opv CURSOR FOR
> select prot_nr_prt
> ,prot_nr_ano
> ,dias
> ,dh_recebimeto
> ,uaie_ds_sigla
> from int_prot_jur.dbo.vw_OPV_AVISO_ANDAMENTO
> OPEN cur_opv
> FETCH NEXT FROM cur_opv
> INTO @.prt, @.ano, @.dias, @.data, @.IP
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> set @.cmd = 'xp_cmdshell ''net send ssouza "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providencia h ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> set @.cmd = 'xp_cmdshell ''net send fyueda "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providencia h ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> set @.cmd = 'xp_cmdshell ''net send rolima "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providncias h ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> set @.cmd = 'xp_cmdshell ''net send pbarbosa "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providncias h ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> set @.cmd = 'xp_cmdshell ''net send agallo "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providncias h ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> set @.cmd = 'xp_cmdshell ''net send mntorres "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providncias h ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> FETCH NEXT FROM cur_opv
> INTO @.prt, @.ano, @.dias, @.data, @.IP
> END
> CLOSE cur_opv
> DEALLOCATE cur_opv
> END
> /***********/
> It's important to note that, the ONLY time when this script deal with some
> datetime type, is on the DATEPART function on the If. This If is just to
see
> if it is 10 o'clock... It wasn't me who wrote this script, because it's
> stupid to control the date by the script and not by the job schedule..
> Anyways, I prefered to keep this way... But I don't think that those line
of
> code could be the problem...
> These job errors are making me crazy! If someone know something about, plz
> help me, I can send some other samples of jobs returning errors!
> Thanks!!!
> Rafa|||The view is Pretty Simple...
It don't even have a datetime field... Look at it's structure:
prot_nr_prt int
prot_nr_ano smallint
dias int
dh_recebimeto varchar(10)
uaie_ds_sigla varchar(12)
The strange thing about this, is that the same error occurs on other jobs...
And when I copy the T-SQL Statement to QA, it runs perfectly...
I don't know if it can be some Database option conflicting with some
statement the people are using on the procs...
"Wayne Snyder" wrote:

> I don't see the problem either so try this...
> 1. check the view, make sure the problem is NOT in the view... Select from
> the view etc in query analyzer. then
> 2. copy out the entire cursor loop in QA and test it. THEN (leaving OUT t
he
> xp_cmdshell stuff - just get the loop going.)
> 3. add the IF statement with the datepart...
> Sneak up on the problem in query analyzer...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Rafa?" <Rafa@.discussions.microsoft.com> wrote in message
> news:AB7004F1-6029-4DF0-BB5F-965587234C87@.microsoft.com...
> (Error
> Cursor
> to
> from
> '"'''
> '"'''
> '"'''
> '"'''
> '"'''
> '"'''
> see
> of
>
>

Problem with Jobs... Plz help!

I have several jobs that run simple T-SQL scripts...
Most of them, fail, with the following problem:
"Executed as user: dba. The conversion of a char data type to a datetime
data type resulted in an out-of-range datetime value. [SQLSTATE 22007] (Error
242) Associated statement is not prepared [SQLSTATE HY007] (Error 0) Cursor
is not open. [SQLSTATE 42000] (Error 16917). The step failed."
Some parts are specific from a example job I'll post here, but the "char to
datetime conversion" error, appears in all of them.. There is transcript from
the Job that raised that error:
/***********/
DECLARE @.prt varchar (10), @.ano varchar (4), @.dias varchar (5), @.data
varchar (10), @.IP varchar (50), @.cmd varchar (500)
IF datepart (hh, getdate()) = 10-- or datepart (hh, getdate()) = 15
BEGIN
DECLARE cur_opv CURSOR FOR
select prot_nr_prt
,prot_nr_ano
,dias
,dh_recebimeto
,uaie_ds_sigla
from int_prot_jur.dbo.vw_OPV_AVISO_ANDAMENTO
OPEN cur_opv
FETCH NEXT FROM cur_opv
INTO @.prt, @.ano, @.dias, @.data, @.IP
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.cmd = 'xp_cmdshell ''net send ssouza "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providencia há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"'''
exec (@.cmd)
set @.cmd = 'xp_cmdshell ''net send fyueda "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providencia há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"'''
exec (@.cmd)
set @.cmd = 'xp_cmdshell ''net send rolima "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"'''
exec (@.cmd)
set @.cmd = 'xp_cmdshell ''net send pbarbosa "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"'''
exec (@.cmd)
set @.cmd = 'xp_cmdshell ''net send agallo "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"'''
exec (@.cmd)
set @.cmd = 'xp_cmdshell ''net send mntorres "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"'''
exec (@.cmd)
FETCH NEXT FROM cur_opv
INTO @.prt, @.ano, @.dias, @.data, @.IP
END
CLOSE cur_opv
DEALLOCATE cur_opv
END
/***********/
It's important to note that, the ONLY time when this script deal with some
datetime type, is on the DATEPART function on the If. This If is just to see
if it is 10 o'clock... It wasn't me who wrote this script, because it's
stupid to control the date by the script and not by the job schedule..
Anyways, I prefered to keep this way... But I don't think that those line of
code could be the problem...
These job errors are making me crazy! If someone know something about, plz
help me, I can send some other samples of jobs returning errors!
Thanks!!!
Rafa®I don't see the problem either so try this...
1. check the view, make sure the problem is NOT in the view... Select from
the view etc in query analyzer. then
2. copy out the entire cursor loop in QA and test it. THEN (leaving OUT the
xp_cmdshell stuff - just get the loop going.)
3. add the IF statement with the datepart...
Sneak up on the problem in query analyzer...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Rafa®" <Rafa@.discussions.microsoft.com> wrote in message
news:AB7004F1-6029-4DF0-BB5F-965587234C87@.microsoft.com...
> I have several jobs that run simple T-SQL scripts...
> Most of them, fail, with the following problem:
> "Executed as user: dba. The conversion of a char data type to a datetime
> data type resulted in an out-of-range datetime value. [SQLSTATE 22007]
(Error
> 242) Associated statement is not prepared [SQLSTATE HY007] (Error 0)
Cursor
> is not open. [SQLSTATE 42000] (Error 16917). The step failed."
> Some parts are specific from a example job I'll post here, but the "char
to
> datetime conversion" error, appears in all of them.. There is transcript
from
> the Job that raised that error:
> /***********/
> DECLARE @.prt varchar (10), @.ano varchar (4), @.dias varchar (5), @.data
> varchar (10), @.IP varchar (50), @.cmd varchar (500)
> IF datepart (hh, getdate()) = 10-- or datepart (hh, getdate()) = 15
> BEGIN
> DECLARE cur_opv CURSOR FOR
> select prot_nr_prt
> ,prot_nr_ano
> ,dias
> ,dh_recebimeto
> ,uaie_ds_sigla
> from int_prot_jur.dbo.vw_OPV_AVISO_ANDAMENTO
> OPEN cur_opv
> FETCH NEXT FROM cur_opv
> INTO @.prt, @.ano, @.dias, @.data, @.IP
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> set @.cmd = 'xp_cmdshell ''net send ssouza "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providencia há ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> set @.cmd = 'xp_cmdshell ''net send fyueda "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providencia há ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> set @.cmd = 'xp_cmdshell ''net send rolima "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> set @.cmd = 'xp_cmdshell ''net send pbarbosa "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> set @.cmd = 'xp_cmdshell ''net send agallo "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> set @.cmd = 'xp_cmdshell ''net send mntorres "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> FETCH NEXT FROM cur_opv
> INTO @.prt, @.ano, @.dias, @.data, @.IP
> END
> CLOSE cur_opv
> DEALLOCATE cur_opv
> END
> /***********/
> It's important to note that, the ONLY time when this script deal with some
> datetime type, is on the DATEPART function on the If. This If is just to
see
> if it is 10 o'clock... It wasn't me who wrote this script, because it's
> stupid to control the date by the script and not by the job schedule..
> Anyways, I prefered to keep this way... But I don't think that those line
of
> code could be the problem...
> These job errors are making me crazy! If someone know something about, plz
> help me, I can send some other samples of jobs returning errors!
> Thanks!!!
> Rafa®|||The view is Pretty Simple...
It don't even have a datetime field... Look at it's structure:
prot_nr_prt int
prot_nr_ano smallint
dias int
dh_recebimeto varchar(10)
uaie_ds_sigla varchar(12)
The strange thing about this, is that the same error occurs on other jobs...
And when I copy the T-SQL Statement to QA, it runs perfectly...
I don't know if it can be some Database option conflicting with some
statement the people are using on the procs...
"Wayne Snyder" wrote:
> I don't see the problem either so try this...
> 1. check the view, make sure the problem is NOT in the view... Select from
> the view etc in query analyzer. then
> 2. copy out the entire cursor loop in QA and test it. THEN (leaving OUT the
> xp_cmdshell stuff - just get the loop going.)
> 3. add the IF statement with the datepart...
> Sneak up on the problem in query analyzer...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Rafa®" <Rafa@.discussions.microsoft.com> wrote in message
> news:AB7004F1-6029-4DF0-BB5F-965587234C87@.microsoft.com...
> > I have several jobs that run simple T-SQL scripts...
> > Most of them, fail, with the following problem:
> >
> > "Executed as user: dba. The conversion of a char data type to a datetime
> > data type resulted in an out-of-range datetime value. [SQLSTATE 22007]
> (Error
> > 242) Associated statement is not prepared [SQLSTATE HY007] (Error 0)
> Cursor
> > is not open. [SQLSTATE 42000] (Error 16917). The step failed."
> >
> > Some parts are specific from a example job I'll post here, but the "char
> to
> > datetime conversion" error, appears in all of them.. There is transcript
> from
> > the Job that raised that error:
> >
> > /***********/
> >
> > DECLARE @.prt varchar (10), @.ano varchar (4), @.dias varchar (5), @.data
> > varchar (10), @.IP varchar (50), @.cmd varchar (500)
> > IF datepart (hh, getdate()) = 10-- or datepart (hh, getdate()) = 15
> > BEGIN
> > DECLARE cur_opv CURSOR FOR
> > select prot_nr_prt
> > ,prot_nr_ano
> > ,dias
> > ,dh_recebimeto
> > ,uaie_ds_sigla
> > from int_prot_jur.dbo.vw_OPV_AVISO_ANDAMENTO
> > OPEN cur_opv
> > FETCH NEXT FROM cur_opv
> > INTO @.prt, @.ano, @.dias, @.data, @.IP
> > WHILE @.@.FETCH_STATUS = 0
> > BEGIN
> > set @.cmd = 'xp_cmdshell ''net send ssouza "A OPV do processo ' + @.prt +
> > '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> > providencia há ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
> '"'''
> > exec (@.cmd)
> > set @.cmd = 'xp_cmdshell ''net send fyueda "A OPV do processo ' + @.prt +
> > '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> > providencia há ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
> '"'''
> > exec (@.cmd)
> > set @.cmd = 'xp_cmdshell ''net send rolima "A OPV do processo ' + @.prt +
> > '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> > providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
> '"'''
> > exec (@.cmd)
> > set @.cmd = 'xp_cmdshell ''net send pbarbosa "A OPV do processo ' + @.prt +
> > '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> > providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
> '"'''
> > exec (@.cmd)
> > set @.cmd = 'xp_cmdshell ''net send agallo "A OPV do processo ' + @.prt +
> > '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> > providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
> '"'''
> > exec (@.cmd)
> > set @.cmd = 'xp_cmdshell ''net send mntorres "A OPV do processo ' + @.prt +
> > '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> > providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
> '"'''
> > exec (@.cmd)
> >
> > FETCH NEXT FROM cur_opv
> > INTO @.prt, @.ano, @.dias, @.data, @.IP
> > END
> > CLOSE cur_opv
> > DEALLOCATE cur_opv
> > END
> >
> > /***********/
> >
> > It's important to note that, the ONLY time when this script deal with some
> > datetime type, is on the DATEPART function on the If. This If is just to
> see
> > if it is 10 o'clock... It wasn't me who wrote this script, because it's
> > stupid to control the date by the script and not by the job schedule..
> > Anyways, I prefered to keep this way... But I don't think that those line
> of
> > code could be the problem...
> >
> > These job errors are making me crazy! If someone know something about, plz
> > help me, I can send some other samples of jobs returning errors!
> >
> > Thanks!!!
> > Rafa®
>
>

Problem with job that run ssis pckg

Hi,

i have a job that run ssis pckgs and it failed when the server is log off with this error msg:

Executed as user: xxxxx\dwhadmin. ...0.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 9:15:00 AM Error: 2007-07-11 09:15:01.71 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTSassword" with error 0x80070002 "The system cannot find the file specified.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2007-07-11 09:15:26.77 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "PackagePassword" with error 0x80070002 "The system cannot find the file specified.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2007-07-11 09:15:26.77 Code: 0xC0016016 Source: Description: Faile... The package execution fa... The step failed.

what can be the problem?

thanks.

Hi Steam5,

There are already answers aviled in this forums. I got it resolved in my case. There are several ways.

One way to deploy an SSIS package into a new machine where you actually created this package is to Use "Save copy of <Your SSIS Pkg> as" on the File menu in BIDS. Use EncryptAllSensitivewithPassword option with a password. Now this package can be executed on another Box using dtexec with /De option to decrypt the password.

Thanks

Subhash Subramanyam

|||

Hi Subhash,

thanks for the reply but it's not working.

:-( .

any other idea?

|||

Hi

We get slightly different errors here with SSIS, but the step succeeds.

Description: Failed to decrypt protected XML node "DTSassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error DTExec: The package execution returned DTSER_SUCCESS (0). Started: 09:55:33 Finished: 09:55:34 Elapsed: 1.15 seconds. The package executed successfully. The step succeeded.

Have you tried hardcoding the password into the connection string in the job step? Sometimes depending on the type of connection (oracle OLDBD) the password is not deployed with the package and you need to override the connection string with the one in the job, including the password.

Hope it helps, bit of a long shot.

Cheers

Matt

problem with inserting or updating dates in mssql 2000

Hello.I've read many topics about this problem but i couldn't figure it out.
I use form where user must insert 2 dates using texboxes.-One is required and other is optional.
Sql 2000 is inserting either '20061105' or '2006.11.05' on insert update but select query returns 05.11.2006 on my report.
Question 1.How do I insert or update dates from my form where date is entered dd.mm.yyyy to sql 2000 table?
question 2. What to do if user left optional texbox date empty.

I'm using SP and function with arguments (byval texbox1.text as date, byval texbox2.text as date)
and parameters @.date1, sqldbtype date =texbox1.textYou can get the date as;

DateTime myDate = DateTime.ParseExact(txtDate.Text, "dd.M.yyyy", System.Globalization.CultureInfo.InvariantCulture);

Now that you have your text box's date as a DateTime variable you can supply that to a SQL parameter query in any format you want, such as dd MMM yyyy

|||I understand that.
I wanna know how to change format of date wich is inserted as d.M.yyyy in texbox to yyyy.M.d because it is passed to sp parameter and it must be yyyy.M.d to do successful insert/update?

Problem with INSERT Statement.

I have an insert statement thats causing me some issues.
Dim strname As String
Dim myname As String
myname = My.User.Name
strname = "INSERT INTO [Item Conversion Header Table]([InitiatorName])VALUES(" & myname & ")"
How do I do the syntax correctly for it to be inserted into my SQL Server correctly?

Thanks,
Tom

I'm not going to answer your question directly because what you are doing is a great security risk. You risk your database coming under a SQL Injection attack so I am not going to give you the quick fix. I am going to tell you how to fix your code AND plug the gaping security hole that you have.
Your code injects the string myname directly into the SQL statement. This should be replaced with a parameter so that the attack surface of the application is reduced.
strname = "INSERT INTO [Item Conversion Header Table]([InitiatorName])VALUES(@.myname)"
Dim cmd as SqlCommand
cmd = New SqlCommand(strname, connection)
cmd.Parameters.Add("@.myname", myname)
cmd.ExecuteNonQuery()
I have replaced your injection with a parameter name (@.myname). Then in the command object I add a parameter with the same name and give it the value it needs.
Finally, here is an article aboutSQL Injection attacks and how to prevent them.

|||Om Sri Sai Ram
Forgot the single '. Use following statement.
strname = "INSERT INTO [Item Conversion Header Table]([InitiatorName])VALUES('" & myname & "')"
Thanks,
Ram|||

potturi_rp wrote:

Om Sri Sai Ram
Forgot the single '. Use following statement.
strname = "INSERT INTO [Item Conversion Header Table]([InitiatorName])VALUES('" & myname & "')"
Thanks,
Ram

Thats the same thing I had.

But I follow on not using the pure injection method.

Thanks,

Tom

Problem with identity user in a report

Hi,

I use forms authentication with my Report manager. The problem is that I can't display the same information to all the users into the report. For example, in my report, i have one dataset that give me a list of all the store of a compagny. And i have to display only the store onto the user is working. If the user is a boss, he can see the information of all the stores. So i have to know in my report what is the login he uses in the form authetication and i don't know how to do.

Excuse me for my bad english and thank you.

Regards,

Shaouk.

Well Shaouk what i have understand from your point is that for that you must have your data base structure like that you must know which company is the user associated to. Means your company table should have column of user id Or if user can be associated with mulitiple companies then third table with comanyID and UserID should exist SO you may know that the logged in user is dealing with this or those comapnies.

Then after user logged in send userID to the stored procedure from which you are geting the company list so you may filter them on basis of that userId or if using dataset with already populated companies with userId's associated with them then filter that dataset on the basis of that logged in UserID

I Hope that works

Regards

Nabil Khalid

|||

Hello Shaouk,

From within your report, you can make use of User!UserID to determine who is running the report.

Hope this helps.

Jarret

Problem with Ghost Cleaner...

I have problem with 'Ghost cleanup' system process. It is locking up my tables and user transactions are keep getting wait status. So is there any way to disable or change the schedule of ghost cleaner?
Thanks in advance..Sorry, never heard of 'Ghost Cleaner'.|||As I understand it, when you run a query, the record on disk is simply flagged as deleted, and the Ghost Cleanup process does the actual deletes. If you are already on the latest service pack, then you would want to look at how often your data is deleted/refreshed or updated.

EDIT: Oh, as such, the Ghost Celaner can not be disabled, or else your database would simply bloat out of proportion.|||We actually had a great thread on the subject of "ghost records" and the cleanup process a few years ago... We had a number of posts from Paul Randall, the GPM responsible for the Microsoft SQL Server physical storage engine that covered the topic in some detail. Unfortunately that was a bit of DBForums history that was lost in a server melt-down.

The actual thread is called the "Ghost Exorcisor" and it runs as a system task (higher CPU priority than a user task) according to rules that are fairly simple, but beyond user control. There is a trace flag that you can set to prevent the Ghost Exorciser from running, but this is a VERY bad idea in most cases.

If you want to pursue this issue, I would suggest that you contact Microsoft Professional Support Services. They will want a bunch of information from you, will probably not charge you for the call, and will probably have a lot of good ideas that will help you more than just turning the exorciser off.

-PatP

Problem with function

Hello,

I am unable to resolve an error message for following user defined function.

Scope of this function:

This functions generates a unique identifier no from a table and returns it to caller.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION SystemNoShiva1

( -- Add the parameters for the function here

)

RETURNS int

AS

begin

DECLARE @.st2 numeric(18,0)

DECLARE @.sys01 numeric(18,0)

declare sys cursor for Select Max(SystemNo) From TISSystemMst1

open sys

Fetch sys into @.sys01

begin

--DECLARE @.st2 numeric(18,0)

if (@.@.fetch_status = 0)

-- IF @.@.ROWCOUNT <> 0

close sys

Deallocate sys

select @.sys01 = @.sys01 + 1

--update TISSystemMst1 set systemno = (@.sys01)

insert into TISSystemMst1 values(@.sys01)

select @.st2 = @.sys01

end

RETURN @.st2

end

Messeg I receive is

Msg 128, Level 15, State 1, Line 17

The name "sys" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

How can I clear above error message?

Nilkanth Desai

Hi,

A typical cursor fetch as follows, however looking at the script, why are you using a cursor, if we are simply selecting the topmost (Max) value and incrementing it?

If you are simply incrementing the topmost value why not use something like

CREATE FUNCTION SystemNoShiva1

( -- Add the parameters for the function here

)

RETURNS int

declare @.st2 int

set @.st2 = (Select Max(SystemNo)+1 From TISSystemMst1)

insert into TISSystemMst1 values (@.st2)

return @.st2

Go

--Cursor Option As Follows--

declare @.sys01 int

Declare sys Cursor For
Select Max(SystemNo) From TISSystemMst1

Open sys
fetch next from sys
into @.sys01

WHILE @.@.fetch_status=0
begin

set @.sys01 = (@.sys01+1)

insert into TISSystemMst1 values(@.sys01)

fetch next from sys
into @.sys01
end

close sys
deallocate sys

return @.sys01

Hope this helps

|||

Hello GDR,

Thx for u r prompt reply. I tried both samples but receive error as under.

Sample 1

While creating this function I receive message "Incorrect syntext near the keyword declare"

In Sample 2

In this code sample I receive same error message as I was receiving in my previous (main) message.

Nilkanth Desai

|||

My humble appologies, the 'AS' is missing, I wrote the script on the forum page, not on a SQL interface thus parsing was not an option,

CREATE FUNCTION SystemNoShiva1

RETURNS int

AS

begin

declare @.st2 int

set @.st2 = (Select Max(SystemNo)+1 From TISSystemMst1)

insert into TISSystemMst1 values (@.st2)

return @.st2

End

Problem with Full Text Search and Joined Tables

I have a sproc that accepts variables from an ASP page to process the
search for the user. The user can choose 3 methods:
1.)Any Occurence
2.)Word/Phrase
3.)Exact Word/Phrase
The sproc goes through some if statments to decide which sql statement
to execute based on the method chosen by the user. The user can also
type in a keyword (of course) but can apply the key word to one column
or two columns. The columns are Name and Description (even though
they may not be Name or Description in the database...just a generic
name to use). Since the sproc is not my problem i'll just give a
little exampe of how it works instead of posting all that code, heh.
if method = 1
Begin
if name and description = 1
Begin
execute sql
End
if name = 1
Begin
execute sql
End
if description = 1
Begin
execute sql
End
End
And the code would be the same for the next two methods. My problem
is with the sql statment that executes when the name and description
are equal to 1. I create views and usually do a join between the view
and the table i want to search on. Then i return the results and that
works fine. The problem with this search is that the Name column is
in one table and the Description in another. I still need the view
cause it contains the other 15 tables of info i use to display and
filter data. But i don't know how to search on both tables and get
the resutls i want. Here is my code so far...
Set @.stmt = 'Select * From cuPolicyMemosView
RIGHT OUTER JOIN CONTAINSTABLE(PolicyMemos, Name, ' + "'" + '"' +
@.strSearchLike + '"' + "'" + ') AS Search ON
cuPolicyMemosView.PolicyMemosID = Search.[KEY]
LEFT JOIN CONTAINSTABLE(PolicyMemosFormNumber, Description, ' + "'" +
'"' + @.strSearchLike + '"' + "'" + ') AS Search2 ON
cuPolicyMemosView.PMFNID = Search2.[KEY] Where ((DATEDIFF(d,
ExpirationDate + 30, GETDATE()) < 0) OR (ExpirationDate IS NULL))
ORDER BY ' + @.strSort
Exec(@.stmt)
I can get the Name just fine when i do a search. But i can't get the
Description. I've tried RIGHT, LEFT, FULL joins...can't get the
Description. I've heard that the only way might be to put the info i
get back from each table seperatley and the put that info in a temp
table and search off of that. That might be a possiblity, but i'm not
100% sure how to do it, heh. I would rather just have a way to alter
my current SQL statment, but if that can't be done then i'm open to
any other suggestions. I hope this all makes sense, heh, i know its a
little unclear...but if anyone has dealt with the joined tables search
problem and knows a good way around that problem in general then thats
all the help i need. Thanks in advance.
Taylor
Taylor,
I've read your posting with interest and it seems that this is the main
issue: "The problem with this search is that the Name column is
in one table and the Description in another. I still need the view cause it
contains the other 15 tables of info i use to display and filter data. But
i don't know how to search on both tables and get the results i want."
The problem of searching across tables with SQL FTS is well know as I posted
three times on this issue just yesterday... I've attached a SQL script file
(Containstable_multiple_tables.sql) with schema and inserted data that I
*think* is related to what you are trying to do, although it does not use
dynamic SQL code... Also, in your code snippet example below could you
provide the name of the view &/or code. If that's not possible, you can just
create a similar view with new tables that work with the tables in the
attached file.
Regards,
John
PS: If you cannot access the attached file, feel free to email me directly
and I'll email it to you.
"Taylor" <dryrye@.juno.com> wrote in message
news:9c0e390c.0409010657.4167393d@.posting.google.c om...
> I have a sproc that accepts variables from an ASP page to process the
> search for the user. The user can choose 3 methods:
> 1.)Any Occurence
> 2.)Word/Phrase
> 3.)Exact Word/Phrase
> The sproc goes through some if statments to decide which sql statement
> to execute based on the method chosen by the user. The user can also
> type in a keyword (of course) but can apply the key word to one column
> or two columns. The columns are Name and Description (even though
> they may not be Name or Description in the database...just a generic
> name to use). Since the sproc is not my problem i'll just give a
> little exampe of how it works instead of posting all that code, heh.
> if method = 1
> Begin
> if name and description = 1
> Begin
> execute sql
> End
> if name = 1
> Begin
> execute sql
> End
> if description = 1
> Begin
> execute sql
> End
> End
> And the code would be the same for the next two methods. My problem
> is with the sql statment that executes when the name and description
> are equal to 1. I create views and usually do a join between the view
> and the table i want to search on. Then i return the results and that
> works fine. The problem with this search is that the Name column is
> in one table and the Description in another. I still need the view
> cause it contains the other 15 tables of info i use to display and
> filter data. But i don't know how to search on both tables and get
> the resutls i want. Here is my code so far...
> Set @.stmt = 'Select * From cuPolicyMemosView
> RIGHT OUTER JOIN CONTAINSTABLE(PolicyMemos, Name, ' + "'" + '"' +
> @.strSearchLike + '"' + "'" + ') AS Search ON
> cuPolicyMemosView.PolicyMemosID = Search.[KEY]
> LEFT JOIN CONTAINSTABLE(PolicyMemosFormNumber, Description, ' + "'" +
> '"' + @.strSearchLike + '"' + "'" + ') AS Search2 ON
> cuPolicyMemosView.PMFNID = Search2.[KEY] Where ((DATEDIFF(d,
> ExpirationDate + 30, GETDATE()) < 0) OR (ExpirationDate IS NULL))
> ORDER BY ' + @.strSort
> Exec(@.stmt)
> I can get the Name just fine when i do a search. But i can't get the
> Description. I've tried RIGHT, LEFT, FULL joins...can't get the
> Description. I've heard that the only way might be to put the info i
> get back from each table seperatley and the put that info in a temp
> table and search off of that. That might be a possiblity, but i'm not
> 100% sure how to do it, heh. I would rather just have a way to alter
> my current SQL statment, but if that can't be done then i'm open to
> any other suggestions. I hope this all makes sense, heh, i know its a
> little unclear...but if anyone has dealt with the joined tables search
> problem and knows a good way around that problem in general then thats
> all the help i need. Thanks in advance.
> Taylor
begin 666 Containstable_mutiple_tables.sql
M#0HM+0EF:6QE;F%M93H@.0V]N=&%I;G-T86)L95]M=71I<&QE7W1A8FQE<RYS
M<6P-"BTM"7!U<G!O<V4Z('1O(&1O8W5M96YT(&AO=R!T;R!U<V4@.8V ]N=&%I
M;G-T86)L92!A;F0@.9G)E971E>'1T86)L92!I;B!M=71I<&QE($944 R!Q=65R
M:65S#0HM+0EM;V1I9FEE9#H@.,3 Z,#4@.4$T@.-R\Q,B\R,# T#0H-"G5S92!S
M<6QF=',-"F=O#0IS96QE8W0@.0$!V97)S:6]N("TM($UI8W)O<V]F="!344P@.
M4V5R=F5R(" R,# P("T@.."XP,"XW-C @.;VX@.16YT97)P<FES92!%9&ET:6]N
M(&]N(%=I;F1O=W,@.3E0@.-2XR("A"=6EL9" S-SDP.B I#0HO*@.T*+2TM+2TM
M+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+ 2TM#0I-:6-R
M;W-O9G0@.4U%,(%-E<G9E<B @.,C P," M(#@.N,# N-S8P("A);G1E;"!8.#8I
M("T@.1&5V96QO<&5R($5D:71I;VX@.;VX@.5VEN9&]W<R!.5" U+C$@.*$)U:6QD
M(#(V,# Z(%-E<G9I8V4@.4&%C:R Q*0T*4$LO1DL@.<F5L871I;VYS:&EP(&)E
M='=E96X@.271E;5-T;V-K+D]R9&5R3F\@./2!)=&5M5&ET;&5S+D]R9&5R3F\@.
M86YD(&%L<V\@.271E;5-T;V-K+D]R9&5R3F\@./2!)=&5M2&%R9'=A<F4N3W)D
M97).;RX@.#0HJ+PT*#0HM+2!$969I;F4@.=&%B;&5S+BXN#0I#4 D5!5$4@.5$%"
M3$4@.6V1B;UTN6TET96U3=&]C:UT@.* T*(%M/<F1E<DYO72!;=F%R8VAA<ET@.
M*#4P*2!#3TQ,051%(%-13%],871I;C%?1V5N97)A;%]#4#%?0TE?05,@.3D]4
M($Y53$P@.+" M+2!02R!)=&5M4W1O8VL@.+2!U;FEQ=64@./PT*(%M,86)E;%T@.
M6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q
M7T-)7T%3($Y/5"!.54Q,("P-"B!;4V]F=$AA<F1=(%MV87)C:&%R72 H-3 I
M($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L7T-0,5]#25]!4R!.54Q,("P-
M"B!;36%N0V]D95T@.6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ
M7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M$97-C<FEP=&EO;ET@.6W9A
M<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)
M7T%3($Y53$P@.+ T*(%MD7U5P9&%T95T@.6V1A=&5T:6UE72!.54Q,("P-"B!;
M17AC;'5S:79E72!;=F%R8VAA<ET@.*#4P*2!#3TQ,051%(%-13%],871I;C%?
M1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6T%F9FEL:6%T95T@.6W9A<F-H
M87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3
M($Y53$P@.+ T*(%M.97=296QE87-E72!;=F%R8VAA<ET@.*#4P*2!#3TQ,051%
M(%-13%],871I;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6U-T871U
M<UT@.6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?
M0U Q7T-)7T%3($Y53$P@.+ T*(%M);G9=(%MD96-I;6%L72@.U+" P*2!.54Q,
M("P-"B!;9%]$871E72!;9&%T971I;65=($Y53$P@.+ T*(%M#871E9V]R>5T@.
M6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q
M7T-)7T%3($Y53$P@.+ T*(%M3:6YG;&5!<G1I<W1=(%MV87)C:&%R72 H,RD@.
M0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*
M(%MS7U1Y<&5=(%MV87)C:&%R72 H-3 I($-/3$Q!5$4@.4U%,7TQA=&EN,5]'
M96YE<F%L7T-0,5]#25]!4R!.54Q,("P-"B!;5'EP941E<V-R:7!=(%MV87)C
M:&%R72 H,3 P*2!#3TQ,051%(%-13%],871I;C%?1V5N97)A;%]#4#%?0TE?
M05,@.3E5,3" L#0H@.6T-A<V5D72!;=F%R8VAA<ET@.*#4P*2!#3TQ,051%(%-1
M3%],871I;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6U!A8VME9%T@.
M6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q
M7T-)7T%3($Y53$P@.+ T*(%M-86YU9ET@.6W9A<F-H87)=("@.U,"D@.0T],3$%4
M12!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M-86Y3
M;W)T72!;:6YT72!.54Q,("P-"B!;5V5I9VAT72!;9&5C:6UA;%TH-2P@.,BD@.
M3E5,3" L#0H@.6TEC;VYS-S5=(%MV87)C:&%R72 H,3 P*2!#3TQ,051%(%-1
M3%],871I;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6TEC;VYS,3 P
M72!;=F%R8VAA<ET@.*#$P,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?
M0U Q7T-)7T%3($Y53$P@.+ T*(%M)8V]N<S(P,%T@.6W9A<F-H87)=("@.Q,# I
M($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L7T-0,5]#25]!4R!.54Q,("P-
M"B!;26-O;G,S,#!=(%MV87)C:&%R72 H,3 P*2!#3TQ,051%(%-13%],871I
M;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6TE$72!;;G5M97)I8UTH
M,3 L(# I($E$14Y42519("@.Q+" Q*2!.3U0@.3E5,3" M+2TM+2TM+2TM+2TM
M+2TM+2TM+2TM+2TM+2TM/B!O<B!02R _(&IU<W0@.9F]R($94($EN9&5X(#\-
M"BD@.3TX@.6U!224U!4EE=#0I'3PT*04Q415(@.5$%"3$4@.271E; 5-T;V-K(%=)
M5$@.@.3D]#2$5#2R!!1$0@.4%))34%262!+15D@.0TQ54U1%4D5$("A/<F1E<DYO
M*0T*9V\-"BTM($EN<V5R="!T97-T('-A;7!L92!D871A#0II;G-E<G0@.:6YT
M;R!)=&5M4W1O8VL@.=F%L=65S("@.G,# P,2<L("=4:&ES(&ES($QA8F5L(# P
M,#$@.;V8@.<F]W(&]N92!":6QL>2!*;V5L)RQ.54Q,+$Y53$PL3E5,3"Q.54Q,
M+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3 E5,3"Q.54Q,
M+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3 E5,3"Q.54Q,
M+$Y53$PI#0II;G-E<G0@.:6YT;R!)=&5M4W1O8VL@.=F%L=65S("@.G,# P,B<L
M("=4:&ES(&ES($QA8F5L(# P,#(@.;V8@.<F]W('1W;R!3=&EN9R<L3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,*0T*:6YS97)T(&EN=&\@.271E; 5-T;V-K('9A
M;'5E<R H)S P,#,G+" G5&AI<R!I<R!,86)E;" P,# S(&]F(')O=R!T:')E
M92!344P@.4V5R=F5R)RQ.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PI#0II;G-E
M<G0@.:6YT;R!)=&5M4W1O8VL@.=F%L=65S("@.G,# P-"<L("=4:&ES(&ES($QA
M8F5L(# P,#0@.;V8@.<F]W(&9O=7(@.3U)!0TQ%)RQ.54Q,+$Y53$PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PI#0HM+2!R;W<@.3D]4(&EN($ET96U4:71L97,L(&)U="!I<R!I
M;B!)=&5M2&%R9'=A<F4-"FEN<V5R="!I;G1O($ET96U3=&]C:R!V86QU97,@.
M*"<P,# U)RP@.)U1H:7,@.:7,@.3&%B96P@.,# P-2!O9B!R;W<@.9FEV92!);G1E
M<F9A8V4G+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+ $Y53$PL3E5,
M3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+ $Y53$PL3E5,
M3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"D-"F=O#0H-"D-214%4
M12!404),12!;9&)O72Y;271E;51I=&QE<UT@.* T*(%M/<F1E<DYO72!;=F%R
M8VAA<ET@.*#4P*2!#3TQ,051%(%-13%],871I;C%?1V5N97)A;%]#4#%?0TE?
M05,@.3D]4($Y53$P@.+ T*(%M4:71L95T@.6W9A<F-H87)=("@.W,"D@.0T],3$%4
M12!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y/5"!.54Q,("P-"B!;
M07)T:7-T72!;=F%R8VAA<ET@.*#8P*2!#3TQ,051%(%-13%],871I;C%?1V5N
M97)A;%]#4#%?0TE?05,@.3D]4($Y53$P@.+ T*(%M,;V-A=&EO;ET@.6W9A<F-H
M87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3
M($Y/5"!.54Q,("P-"B!;4V]R=$ME>5T@.6V1E8VEM86Q=*#4L(# I($Y/5"!.
M54Q,("P-"B!;1&ES8U])1%T@.6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?
M3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M-4#-&:6QE<UT@.
M6W9A<F-H87)=("@.Q,# I($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L7T-0
M,5]#25]!4R!.54Q,("P-"B!;241=(%MN=6UE<FEC72@.Q,"P@.,"D@.241%3E1)
M5%D@.*#$L(#$I($Y/5"!.54Q,#0HI($].(%M04DE-05)970T*1T\-"D%,5$52
M(%1!0DQ%($ET96U4:71L97,@.5TE42"!.3T-(14-+($%$1"!04DE-05)9($M%
M62!#3%535$52140@.*$]R9&5R3F\I#0IG;PT*+2T@.26YS97)T('1E<W0@.<V%M
M<&QE(&1A=&$-"FEN<V5R="!I;G1O($ET96U4:71L97,@.=F%L=65S("@.G,# P
M,2<L)U1H:7,@.:7,@.5&ET;&4@.,# P,2!O9B!R;W<@.;VYE(%-T<F%N9V5R)RPG
M0FEL;'D@.2F]E;"<L)R!3;VUE<&QA8V4@.:&5R92<L,2Q.54Q,+$Y53$PI#0I I
M;G-E<G0@.:6YT;R!)=&5M5&ET;&5S('9A;'5E<R H)S P,#(G+"=4:&ES(&ES
M(%1I=&QE(# P,#(@.;V8@.<F]W('1W;R!3=&EN9R<L)U-T:6YG)RPG(%-O;65P
M;&%C92!T:&5R92<L.2Q.54Q,+$Y53$PI#0II;G-E<G0@.:6YT;R!)=&5M5&ET
M;&5S('9A;'5E<R H)S P,#,G+"=4:&ES(&ES(%1I=&QE(# P,#,@.;V8@.<F]W
M('1H<F5E(%-13"!397)V97(G+"=-:6-R;W-O9G0G+"<@.4V]M97!L86-E(&5L
M<V4G+#@.L3E5,3"Q.54Q,*0T*:6YS97)T(&EN=&\@.271E;51I= &QE<R!V86QU
M97,@.*"<P,# T)RPG5&AI<R!I<R!4:71L92 P,# T(&]F(')O=R!F;W5R($]2
M04-,12<L)T]R86-L92<L)R!3;VUE<&QA8V4@.96QS92<L-"Q.54Q,+$Y53$PI
M#0IG;PT*#0HM+2!)=&5M4W1O8VLN3W)D97).;R ]($ET96U(87)D=V%R92Y/
M<F1E<DYO+B -"D-214%412!404),12!;9&)O72Y;271E;4AA<F1W87)E72 H
M#0H@.6T]R9&5R3F]=(%MV87)C:&%R72 H-3 I($-/3$Q!5$4@.4U%,7TQA=&EN
M,5]'96YE<F%L7T-0,5]#25]!4R!.3U0@.3E5,3" L#0H@.6W-?<&%G95T@.6W9A
M<F-H87)=("@.V,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)
M7T%3($Y53$P@.+ T*(%MS7U1H=6UB72!;=F%R8VAA<ET@.*#$U,"D@.0T],3$%4
M12!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%MS7W!I
M8UT@.6W9A<F-H87)=("@.Q-3 I($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L
M7T-0,5]#25]!4R!.54Q,("P-"B!;4$1&4V-A;&5=(%MV87)C:&%R72 H-3 I
M($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L7T-0,5]#25]!4R!.54Q,("P-
M"B!;;5]3<&5C<UT@.6W9A<F-H87)=("@.X,# P*2!#3TQ,051%(%-13%],871I
M;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6U!$1E-P96-S72!;=F%R
M8VAA<ET@.*#@.P,# I($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L7T-0,5]#
M25]!4R!.54Q,("P-"B!;3F]T97-=(%MV87)C:&%R72 H.# P,"D@.0T],3$%4
M12!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M-961I
M85T@.6W9A<F-H87)=("@.S*2!#3TQ,051%(%-13%],871I;C%?1V5N97)A;%]#
M4#%?0TE?05,@.3E5,3" L#0H@.6T-A<W-=(%MV87)C:&%R72 H,RD@.0T],3$%4
M12!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M#1$==
M(%MV87)C:&%R72 H,RD@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q
M7T-)7T%3($Y53$P@.+ T*(%M60T1=(%MV87)C:&%R72 H,RD@.0T],3$%412!3
M44Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M$5D1=(%MV
M87)C:&%R72 H,RD@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)
M7T%3($Y53$P@.+ T*(%M)1%T@.6VYU;65R:6-=*#$P+" P*2!)1$5.5$E462 H
M,2P@.,2D@.3D]4($Y53$P-"BD@.3TX@.6U!224U!4EE=#0I'3PT*04Q415(@.5$%"
M3$4@.271E;4AA<F1W87)E(%=)5$@.@.3D]#2$5#2R!!1$0@.4%))34%262!+15D@.
M0TQ54U1%4D5$("A/<F1E<DYO*0T*9V\-"B\J("TM($=O="!T:&4@.9F]L;&]W
M:6YG('=A<FYI;F<@.=VAE;B!C<F5A=&EN9R!T86)L93H@.271E; 4AA<F1W87)E
M#0I787)N:6YG.B!4:&4@.=&%B;&4@.)TET96U(87)D=V%R92<@.: &%S(&)E96X@.
M8W)E871E9"!B=70@.:71S(&UA>&EM=6T@.<F]W('-I>F4@.*#(T-3,R*2!E>&-E
M961S('1H92!M87AI;75M(&YU;6)E<B!O9B!B>71E<R!P97(@.< F]W("@.X,#8P
M*2X@.#0I)3E-%4E0@.;W(@.55!$051%(&]F(&$@.<F]W(&EN('1H:7,@.=&%B;&4@.
M=VEL;"!F86EL(&EF('1H92!R97-U;'1I;F<@.<F]W(&QE;F=T:"!E>&-E961S
M(#@.P-C @.8GET97,N#0HJ+PT*+2T@.26YS97)T(%-A;7!L92!D871A#0II;G-E
M<G0@.:6YT;R!)=&5M2&%R9'=A<F4@.=F%L=65S("@.G,# P,2<L("=4:&ES(&ES
M($QA8F5L(# P,#$@.;V8@.<F]W(&]N92!3=')A;F=E<B<L3E5,3"Q.54Q,+$Y5
M3$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3 "Q.54Q,*0T*
M:6YS97)T(&EN=&\@.271E;4AA<F1W87)E('9A;'5E<R H)S P,#(G+" G5&AI
M<R!I<R!,86)E;" P,# R(&]F(')O=R!T=V\@.4W1I;F<G+$Y53$PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"D-
M"FEN<V5R="!I;G1O($ET96U(87)D=V%R92!V86QU97,@.*"<P, # S)RP@.)U1H
M:7,@.:7,@.3&%B96P@.,# P,R!O9B!R;W<@.=&AR964@.4U%,(%-E<G9E<B<L3E5,
M3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+ $Y53$PL3E5,
M3"Q.54Q,*0T*:6YS97)T(&EN=&\@.271E;4AA<F1W87)E('9A; '5E<R H)S P
M,#0G+" G5&AI<R!I<R!,86)E;" P,# T(&]F(')O=R!F;W5R($]204-,12<L
M3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.5 4Q,+$Y53$PL
M3E5,3"Q.54Q,*0T*+2T@.<F]W($Y/5"!I;B!)=&5M5&ET;&5S+"!B=70@.:7,@.
M:6X@.271E;5-T;V-K#0II;G-E<G0@.:6YT;R!)=&5M2&%R9'=A<F4@.=F%L=65S
M("@.G,# P-2<L("=4:&ES(&ES($QA8F5L(# P,#4@.;V8@.<F]W(&9I=F4@.26YT
M97)F86-E)RQ.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+ $Y5
M3$PL3E5,3"Q.54Q,+$Y53$PI#0IG;PT*#0HM+2!C;VYF:7)M( '-A;7!L92!D
M871A(&%N9"!&5"UE;F%B;&4@.=&%B;&5S('9I82!%;G1E<G!R: 7-E($UA;F%G
M97(N+BX-"G-E;&5C=" J(&9R;VT@.271E;5-T;V-K#0HM+2!T<G5N8V%T92!T
M86)L92!)=&5M4W1O8VL-"G-E;&5C=" J(&9R;VT@.271E;51I=&QE<PT*+2T@.
M=')U;F-A=&4@.=&%B;&4@.271E;51I=&QE<PT*<V5L96-T("H@.9G)O;2!)=&5M
M2&%R9'=A<F4-"BTM('1R=6YC871E('1A8FQE($ET96U(87)D=V%R90T*9V \-
M"@.T*+2T@.#0HM+2!/<FEG:6YA;"!P<F]B;&5M('%U97)Y+"!W;W)K<R!W:71H
M('1H92!A8F]V92!D871A+"!B=70@.;VYL>2!W:71H(&$@.8V]M;6]N('=O<F0@.
M(G)O=R(@.:7,@.<75E<FEE9"!F<F]M(&%L;"!T:')E92!T86)L97,N#0I$14-,
M05)%($!396%R8VA#<FET97)I82!V87)C:&%R*#$P,"D-"E-%5"! 4V5A<F-H
M0W)I=&5R:6$@./2 G(")R;W<J(B G("TM('!R:6UA<GD@.<V5A<F-H(&-O;'5M
M;B!I<R!)=&5M4W1O8VLN3&%B96P@.*&]T:&5R(&-O;'5M;G,L(&QE9G0@.3E5,
M3"D-"E-%3$5#5"!)=&5M4W1O8VLN3W)D97).;RQ)=&5M4W1O8VLN3&%B9 6P@.
M#0I&4D]-($ET96U3=&]C:PT*("!)3DY%4B!*3TE.($ET96U4:71L97,@.("!/
M3B!)=&5M4W1O8VLN3W)D97).;R ]($ET96U4:71L97,N3W)D97).;PT*("!)
M3DY%4B!*3TE.($ET96U(87)D=V%R92!/3B!)=&5M4W1O8VLN3W)D97).;R ]
M($ET96U(87)D=V%R92Y/<F1E<DYO#0H@.($E.3D52($I/24X@.1E)%151%6%14
M04),12A)=&5M4W1O8VLL("HL($!396%R8VA#<FET97)I82D@.0 5,@.1E-?5$%"
M3$4@.3TX@.1E-?5$%"3$4N6TM%65T@./2!)=&5M4W1O8VLN3W)D97).;PT*(" @.
M("!/4D1%4B!"62!&4U]404),12Y286YK($1%4T,-"BTM(#%S="!T97-T.B T
M(')O=W,@.8F5C87-U92!O;FQY($ET96U3=&]C:R!I<R!R969E<F5N8V5D(&EN
M($92145415A45$%"3$4@.8VQA<W5E#0H-"@.T*+2T@.36]D:69E9"!/<FEG:6YA
M;"!Q=65R>0T*1$5#3$%212! 4V5A<F-H0W)I=&5R:6$@.=F%R8VAA<B@.Q,# I
M#0I3150@.0%-E87)C:$-R:71E<FEA(#T@.)R B<F]W*B(@.)R @.+2T@.5&5S="!W
M:71H(&)O=&@.@.8V]N=&%I;G-T86)L92 F(&9R965T97AT($%.1"!/4B!"971E
M=V5N(&9O<B!E>'!E8W1E9"!R97-U;'1S#0I314Q%0U0@.9&ES=&EN8W0@.92Y/
M<F1E<DYO+"!E+DQA8F5L("TM(&1I<V-T:6YC="!R97%U<FEE9"!T;R!G970@.
M-2!R;W=S+"!S=&EL;"!N;W0@.97AA8W1L>2!S86UE(&%S(&%B;W9 E+BXN#0IF
M<F]M($ET96U3=&]C:R!!4R!E+"!)=&5M5&ET;&5S('0L($ET96U(87)D=V%R
M92!H+ T*(" @.("!F<F5E=&5X='1A8FQE*$ET96U3=&]C:RP@.3&%B96PL($!3
M96%R8VA#<FET97)I82D@.87,@.02P-"B @.(" @.9G)E971E>'1T86)L92A)=&5M
M5&ET;&5S+"!4:71L92P@.0%-E87)C:$-R:71E<FEA*2!A<R!"+ T*(" @.("!F
M<F5E=&5X='1A8FQE*$ET96U(87)D=V%R92P@.<U]P86=E+"! 4V5A<F-H0W)I
M=&5R:6$I(&%S($,-"B @.(" @.("!W:&5R90T*(" @.(" @.(" @.02Y;2T5972 ]
M(&4N3W)D97).;R!A;F0-"B @.(" @.(" @.($(N6TM%65T@./2!T+D]R9&5R3F\@.
M86YD#0H@.(" @.(" @.("!#+EM+15E=(#T@.:"Y/<F1E<DYO#0HM+2 Q<W0@.=&5S
M=#H@.3D]4('1H92!S86UE(&%S(&%B;W9E(&%S('1H:7,@.<75E<GD@.9V5T< R U
M(')O=W,L(&%D9&ET:6]N86P@.<F]W(&ES(")4:&ES(&ES($QA8F5L(# P,#4@.
M;V8@.<F]W(&9I=F4@.26YT97)F86-E(@.T*#0H-"BTM(%1E<W0@.=VET:"!B;W1H
M(&-O;G1A:6YS=&%B;&4@.)B!F<F5E=&5X="!!3D0@.3U(@.0F5T97=E; B!E86-H
M(0T*4T5,14-4(&1I<W1I;F-T(&4N3W)D97).;RP@.92Y,86)E; T*9G)O;2!)
M=&5M4W1O8VL@.05,@.92P@.271E;51I=&QE<R!T+ T*(" @.("!C;VYT86EN<W1A
M8FQE*$ET96U3=&]C:RP@.3&%B96PL("=":6QL>2<I(&%S($$L#0H@.(" @.(&-O
M;G1A:6YS=&%B;&4H271E;51I=&QE<RP@.5&ET;&4L("=3=')A; F=E<B<I(&%S
M($(@.+2T@.;F]W(&=E='1I;F<@.<F5S=6QT<R A($UU<W0@.:&%V92!C;VQU;6XM
M<W!E8VEF:6,@.<V5A<F-H('=O<F0A#0H@.(" @.(" @.=VAE<F4-"B @.(" @.(" @.
M($$N6TM%65T@./2!E+D]R9&5R3F\@.;W(@.+2T@.3U(@.+2T@.9V5N97)A=&5S(&QA
M<F=E<B!R97-U;'0L('-O('5S92!D:7-T:6YC="!E+D]R9&5R3F\L(')E='5R
M;G,@.-2!R;W=S('=I=&@.@.9&ES=&EN8W0-"B @.(" @.(" @.($(N6TM%65T@./2!T
M+D]R9&5R3F\-"@.T*<V5L96-T("H@.9G)O;2!)=&5M4W1O8VL@.=VAE<F4@.8V]N
M=&%I;G,H*BPG0FEL;'DG*2 M+2!R971U<FYS(#$@.<F]W+@.T*#0H-"BTM($UO
M9&EF:65D($]R:6=I;F%L('%U97)Y+"!R96UO=F5D('1H92! 4V5A<F-H0W)I
M=&5R:6$@.=F%R:6%B;&4@.=&\@.=&5S="!W:71H('1A8FQE(&%N9 "!C;VQU;6XM
M<W!E8VEF:6,@.<V5A<F-H('=O<F1S#0I314Q%0U0@.9&ES=&EN8W0@.92Y/<F1E
M<DYO+"!E+DQA8F5L#0IF<F]M($ET96U3=&]C:R!!4R!E+"!)=&5M5&ET;&5S
M('0L($ET96U(87)D=V%R92!H+ T*(" @.("!C;VYT86EN<W1A8FQE*$ET96U3
M=&]C:RP@.3&%B96PL("=":6QL>2<I(&%S($$L#0H@.(" @.(&-O;G1A:6YS=&%B
M;&4H271E;51I=&QE<RP@.5&ET;&4L("=3=')A;F=E<B<I(&%S( $(L#0H@.(" @.
M(&-O;G1A:6YS=&%B;&4H271E;4AA<F1W87)E+"!S7W!A9V4L("=R; W<G*2!A
M<R!##0H@.(" @.(" @.=VAE<F4-"B @.(" @.(" @.($$N6TM%65T@./2!E+D]R9&5R
M3F\@.86YD("TM($]2(#T@.9V5N97)A=&5S(&UU=&EP;&4@.<F]W<RP@.86YD('1H
M97)E9F]R92!N965D<R @.9&ES=&EN8W0@.92Y/<F1E<DYO+@.T*(" @.(" @.(" @.
M0BY;2T5972 ]('0N3W)D97).;R!A;F0-"B @.(" @.(" @.($,N6TM%65T@./2!H
M+D]R9&5R3F\-"BTM(#%S="!T97-T.B P(')O=W,L('-A;64@.87,@.86)O=F4-
9"@.T*+2T@./&5O9CX-"@.T*#0H-"@.T*#0H-"@.``
`
end
|||Here's my stab at this. Note that you have to change the @.separator value
from ' AND' to ' OR ' when you want to change from all the words to some of
the words.
create database test
go
use test
go
create table PolicyMemos
(
PolicyMemosID int not null primary key identity(1,1),
[Name] varchar(200),
ExpirationDate datetime
)
go
create table PolicyMemosFormNumber
(
PMFNID int not null primary key identity(1,1),
PolicyMemosID int not null foreign key references
policyMemos(PolicyMemosID ),
[Description] varchar(200)
)
go
alter View cuPolicyMemosView
as
select [Name], ExpirationDate, [Description]
,[policymemosid]=PolicyMemos.PolicyMemosID,PMFNID
from PolicyMemos, PolicyMemosFormNumber
where PolicyMemos.PolicyMemosID =PolicyMemosFormNumber.PolicyMemosID
go
sp_fulltext_database 'enable'
go
sp_fulltext_catalog 'test','create'
go
declare @.indexname varchar(130)
select @.indexname = name from sysindexes where id=object_id('PolicyMemos')
exec sp_fulltext_table 'PolicyMemos', 'create', 'test', @.indexname
go
sp_fulltext_column 'PolicyMemos','Name','add',1033
go
sp_fulltext_table 'PolicyMemos', 'activate'
go
sp_fulltext_table 'PolicyMemos', 'start_full'
go
declare @.indexname varchar(130)
select @.indexname = name from sysindexes where
id=object_id('PolicyMemosFormNumber')
exec sp_fulltext_table 'PolicyMemosFormNumber', 'create', 'test', @.indexname
go
sp_fulltext_column 'PolicyMemosFormNumber','Description','add',1033
go
sp_fulltext_table 'PolicyMemosFormNumber', 'activate'
go
sp_fulltext_table 'PolicyMemosFormNumber', 'start_full'
go
insert into PolicyMemos ([name], expirationdate) values('New York City',
getdate()-20)
go
insert into PolicyMemos ([name], expirationdate) values('Manhattan',
getdate()-30)
go
insert into PolicyMemos ([name], expirationdate) values('Jersey City',
getdate()-30)
go
insert into PolicyMemos ([name], expirationdate) values('Englishtown',
getdate()-30)
go
insert into PolicyMemosFormNumber (PolicyMemosID , description )
values(1,'study of water table levels')
go
insert into PolicyMemosFormNumber (PolicyMemosID , description )
values(1,'study of soil samples')
go
insert into PolicyMemosFormNumber (PolicyMemosID , description )
values(1,'study of granite formations')
go
insert into PolicyMemosFormNumber (PolicyMemosID , description )
values(2,'study of shale formations')
go
insert into PolicyMemosFormNumber (PolicyMemosID , description )
values(2,'study of marble formations')
go
insert into PolicyMemosFormNumber (PolicyMemosID , description )
values(2,'study of top soil')
go
declare @.strSort varchar(20)
set @.strSort ='Name'
declare @.stmt varchar(2000)
declare @.strSearchLike varchar(200)
declare @.strSearchParsed varchar(200)
declare @.separator varchar(5)
set @.separator=' OR '
set @.strSearchLike ='manhattan soil'
select @.strSearchLike=case when left(@.strSearchLike,1)=char(32) then
substring(@.strSearchLike,2,len(@.strSearchLike)-1) else @.strSearchLike end
set @.strSearchParsed=''
select @.strSearchlike =replace(@.strsearchLike,' ',char(32))
declare @.counter int
declare @.NumberOfWhiteSpaces int
set @.NumberOfWhiteSpaces=len(@.strSearchLike)-len(replace(@.strSearchLike,'
',''))
set @.counter=@.NumberOfWhiteSpaces
while @.counter >0
begin
select @.strSearchParsed=@.strSearchParsed+left(@.strSearchL ike, charindex('
',@.strSearchLike)-1)+ char(34)+@.separator+char(34)
if @.counter<>0
select @.strSearchlike=substring(@.strSearchLike,charindex( '
',@.strSearchLike)+1,len(@.strSearchLike))
select @.counter=@.counter-1
end
select @.strSearchParsed=char(34)+ @.strSearchParsed+
char(32)+@.strSearchLike+char(34)
Set @.stmt = 'Select * From cuPolicyMemosView
RIGHT OUTER JOIN CONTAINSTABLE(PolicyMemos, Name, ' ++char(39)+
@.strSearchParsed + char(39)+ ') AS Search ON
cuPolicyMemosView.PolicyMemosID = Search.[KEY]
LEFT JOIN CONTAINSTABLE(PolicyMemosFormNumber, Description, ' + char(39) +
@.strSearchParsed +char(39) + ') AS Search2 ON
cuPolicyMemosView.PMFNID = Search2.[KEY]
--Where ((DATEDIFF(d,ExpirationDate + 30, GETDATE()) < 0) OR (ExpirationDate
IS NULL))
ORDER BY ' + @.strSort
exec (@.stmt)
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Taylor" <dryrye@.juno.com> wrote in message
news:9c0e390c.0409010657.4167393d@.posting.google.c om...
> I have a sproc that accepts variables from an ASP page to process the
> search for the user. The user can choose 3 methods:
> 1.)Any Occurence
> 2.)Word/Phrase
> 3.)Exact Word/Phrase
> The sproc goes through some if statments to decide which sql statement
> to execute based on the method chosen by the user. The user can also
> type in a keyword (of course) but can apply the key word to one column
> or two columns. The columns are Name and Description (even though
> they may not be Name or Description in the database...just a generic
> name to use). Since the sproc is not my problem i'll just give a
> little exampe of how it works instead of posting all that code, heh.
> if method = 1
> Begin
> if name and description = 1
> Begin
> execute sql
> End
> if name = 1
> Begin
> execute sql
> End
> if description = 1
> Begin
> execute sql
> End
> End
> And the code would be the same for the next two methods. My problem
> is with the sql statment that executes when the name and description
> are equal to 1. I create views and usually do a join between the view
> and the table i want to search on. Then i return the results and that
> works fine. The problem with this search is that the Name column is
> in one table and the Description in another. I still need the view
> cause it contains the other 15 tables of info i use to display and
> filter data. But i don't know how to search on both tables and get
> the resutls i want. Here is my code so far...
> Set @.stmt = 'Select * From cuPolicyMemosView
> RIGHT OUTER JOIN CONTAINSTABLE(PolicyMemos, Name, ' + "'" + '"' +
> @.strSearchLike + '"' + "'" + ') AS Search ON
> cuPolicyMemosView.PolicyMemosID = Search.[KEY]
> LEFT JOIN CONTAINSTABLE(PolicyMemosFormNumber, Description, ' + "'" +
> '"' + @.strSearchLike + '"' + "'" + ') AS Search2 ON
> cuPolicyMemosView.PMFNID = Search2.[KEY] Where ((DATEDIFF(d,
> ExpirationDate + 30, GETDATE()) < 0) OR (ExpirationDate IS NULL))
> ORDER BY ' + @.strSort
> Exec(@.stmt)
> I can get the Name just fine when i do a search. But i can't get the
> Description. I've tried RIGHT, LEFT, FULL joins...can't get the
> Description. I've heard that the only way might be to put the info i
> get back from each table seperatley and the put that info in a temp
> table and search off of that. That might be a possiblity, but i'm not
> 100% sure how to do it, heh. I would rather just have a way to alter
> my current SQL statment, but if that can't be done then i'm open to
> any other suggestions. I hope this all makes sense, heh, i know its a
> little unclear...but if anyone has dealt with the joined tables search
> problem and knows a good way around that problem in general then thats
> all the help i need. Thanks in advance.
> Taylor
|||note that in this statement select @.strSearchlike =replace(@.strsearchLike,'
',char(32))
I am replacing two consecutive white spaces with a single white space.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ezWC7UPkEHA.2412@.TK2MSFTNGP15.phx.gbl...
> Here's my stab at this. Note that you have to change the @.separator value
> from ' AND' to ' OR ' when you want to change from all the words to some
of
> the words.
> create database test
> go
> use test
> go
> create table PolicyMemos
> (
> PolicyMemosID int not null primary key identity(1,1),
> [Name] varchar(200),
> ExpirationDate datetime
> )
> go
> create table PolicyMemosFormNumber
> (
> PMFNID int not null primary key identity(1,1),
> PolicyMemosID int not null foreign key references
> policyMemos(PolicyMemosID ),
> [Description] varchar(200)
> )
> go
> alter View cuPolicyMemosView
> as
> select [Name], ExpirationDate, [Description]
> ,[policymemosid]=PolicyMemos.PolicyMemosID,PMFNID
> from PolicyMemos, PolicyMemosFormNumber
> where PolicyMemos.PolicyMemosID =PolicyMemosFormNumber.PolicyMemosID
> go
> sp_fulltext_database 'enable'
> go
> sp_fulltext_catalog 'test','create'
> go
> declare @.indexname varchar(130)
> select @.indexname = name from sysindexes where id=object_id('PolicyMemos')
> exec sp_fulltext_table 'PolicyMemos', 'create', 'test', @.indexname
> go
> sp_fulltext_column 'PolicyMemos','Name','add',1033
> go
> sp_fulltext_table 'PolicyMemos', 'activate'
> go
> sp_fulltext_table 'PolicyMemos', 'start_full'
> go
> declare @.indexname varchar(130)
> select @.indexname = name from sysindexes where
> id=object_id('PolicyMemosFormNumber')
> exec sp_fulltext_table 'PolicyMemosFormNumber', 'create', 'test',
@.indexname
> go
> sp_fulltext_column 'PolicyMemosFormNumber','Description','add',1033
> go
> sp_fulltext_table 'PolicyMemosFormNumber', 'activate'
> go
> sp_fulltext_table 'PolicyMemosFormNumber', 'start_full'
> go
> insert into PolicyMemos ([name], expirationdate) values('New York City',
> getdate()-20)
> go
> insert into PolicyMemos ([name], expirationdate) values('Manhattan',
> getdate()-30)
> go
> insert into PolicyMemos ([name], expirationdate) values('Jersey City',
> getdate()-30)
> go
> insert into PolicyMemos ([name], expirationdate) values('Englishtown',
> getdate()-30)
> go
> insert into PolicyMemosFormNumber (PolicyMemosID , description )
> values(1,'study of water table levels')
> go
> insert into PolicyMemosFormNumber (PolicyMemosID , description )
> values(1,'study of soil samples')
> go
> insert into PolicyMemosFormNumber (PolicyMemosID , description )
> values(1,'study of granite formations')
> go
> insert into PolicyMemosFormNumber (PolicyMemosID , description )
> values(2,'study of shale formations')
> go
> insert into PolicyMemosFormNumber (PolicyMemosID , description )
> values(2,'study of marble formations')
> go
> insert into PolicyMemosFormNumber (PolicyMemosID , description )
> values(2,'study of top soil')
> go
>
> declare @.strSort varchar(20)
> set @.strSort ='Name'
> declare @.stmt varchar(2000)
> declare @.strSearchLike varchar(200)
> declare @.strSearchParsed varchar(200)
> declare @.separator varchar(5)
> set @.separator=' OR '
> set @.strSearchLike ='manhattan soil'
> select @.strSearchLike=case when left(@.strSearchLike,1)=char(32) then
> substring(@.strSearchLike,2,len(@.strSearchLike)-1) else @.strSearchLike end
> set @.strSearchParsed=''
> select @.strSearchlike =replace(@.strsearchLike,' ',char(32))
> declare @.counter int
> declare @.NumberOfWhiteSpaces int
> set @.NumberOfWhiteSpaces=len(@.strSearchLike)-len(replace(@.strSearchLike,'
> ',''))
> set @.counter=@.NumberOfWhiteSpaces
> while @.counter >0
> begin
> select @.strSearchParsed=@.strSearchParsed+left(@.strSearchL ike, charindex('
> ',@.strSearchLike)-1)+ char(34)+@.separator+char(34)
> if @.counter<>0
> select @.strSearchlike=substring(@.strSearchLike,charindex( '
> ',@.strSearchLike)+1,len(@.strSearchLike))
> select @.counter=@.counter-1
> end
> select @.strSearchParsed=char(34)+ @.strSearchParsed+
> char(32)+@.strSearchLike+char(34)
> Set @.stmt = 'Select * From cuPolicyMemosView
> RIGHT OUTER JOIN CONTAINSTABLE(PolicyMemos, Name, ' ++char(39)+
> @.strSearchParsed + char(39)+ ') AS Search ON
> cuPolicyMemosView.PolicyMemosID = Search.[KEY]
> LEFT JOIN CONTAINSTABLE(PolicyMemosFormNumber, Description, ' + char(39)
+
> @.strSearchParsed +char(39) + ') AS Search2 ON
> cuPolicyMemosView.PMFNID = Search2.[KEY]
> --Where ((DATEDIFF(d,ExpirationDate + 30, GETDATE()) < 0) OR
(ExpirationDate
> IS NULL))
> ORDER BY ' + @.strSort
> exec (@.stmt)
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Taylor" <dryrye@.juno.com> wrote in message
> news:9c0e390c.0409010657.4167393d@.posting.google.c om...
>

Problem with execution of DTS

Hello,
I have one server where i create one DTS associated with
one user account that is not DBAdmin. The DTS always run
until i change the passwords of my DBAdmin account(in this
case (dba)).
Im quite sure that the user account is the only one
associated with the DTS but since i change the password
the DTS execution always fail and when i see the history
its shown to me that there was one login error of
the "dba".
I dont know if this account is linked anywhere else with
the DTS's.
Best regards
perhaps you have any connection icon in your dts where you use mixed
autentication to login to your server and you're connecting with any login
that uses the dbaadmin user
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:16ccf01c417d3$57b53c10$a401280a@.phx.gbl...
> Hello,
> I have one server where i create one DTS associated with
> one user account that is not DBAdmin. The DTS always run
> until i change the passwords of my DBAdmin account(in this
> case (dba)).
> Im quite sure that the user account is the only one
> associated with the DTS but since i change the password
> the DTS execution always fail and when i see the history
> its shown to me that there was one login error of
> the "dba".
> I dont know if this account is linked anywhere else with
> the DTS's.
> Best regards
>

Problem with execution of DTS

Hello,
I have one server where i create one DTS associated with
one user account that is not DBAdmin. The DTS always run
until i change the passwords of my DBAdmin account(in this
case (dba)).
Im quite sure that the user account is the only one
associated with the DTS but since i change the password
the DTS execution always fail and when i see the history
its shown to me that there was one login error of
the "dba".
I dont know if this account is linked anywhere else with
the DTS's.
Best regardsperhaps you have any connection icon in your dts where you use mixed
autentication to login to your server and you're connecting with any login
that uses the dbaadmin user
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:16ccf01c417d3$57b53c10$a401280a@.phx
.gbl...
> Hello,
> I have one server where i create one DTS associated with
> one user account that is not DBAdmin. The DTS always run
> until i change the passwords of my DBAdmin account(in this
> case (dba)).
> Im quite sure that the user account is the only one
> associated with the DTS but since i change the password
> the DTS execution always fail and when i see the history
> its shown to me that there was one login error of
> the "dba".
> I dont know if this account is linked anywhere else with
> the DTS's.
> Best regards
>

Problem with dynamic security and dependent dimension (SSAS 2k5)

Hi all,

I try to implement dynamic security and I have a problem with a dependent dimension.

What I want:

Each user is responsible for one or more branches of the company, so each user should see the data the branches, she is responsible for.

What I get:

Error: Das Branch-Attribut in der DIM_ZWST-Dimension weist einen ungültigen Sicherheitsausdruck für eine generierte Dimension auf. (sorry, it's german) It means: The Branch-attribute of the DIM_ZWST-dimension has an invalid security expression for a generated dimension.

This is my setup:

SSAS 2k5 Dimensions: DIM_USERS with attributes Name, Loginname (this is NT user name and should correspond to the MDX UserName()-function) DIM_ZWST: These are the branches of the company. Each user should be allowed to view one ore more branches. Cube: I have a measure group 'PERMISSIONS' where each combination of a user and a branch has a value of '1' for the measure HAS_PERMISSIONS. Every other combination doesn't exist. I can check this by just browsing that measure. My other measure groups are related m:n with the DIM_USERS dimension via the PERMISSIONS measure group. Browsing the cube I get all the values I would like to see. Roles: After playing around with the STRTOSET and STRTOMEMBER functions, what worked for the DIM_USERS dimension but not for the DIM_ZWST function I tried it with a hardcoded role: DIM_USERS, allowed group = {[DIM_USERS].[LOGINNAME].&[MYDOMAIN\testuser]} (this one works!) DIM_ZWST, allowed group = NONEMPTY([DIM_ZWST].[Branches].[Branch].Members, ([DIM_USERS].[LOGINNAME].&[MYDOMAIN\testuser], [MEASURES].[HAS_PERMISSION])) (this one doesn't work!)

I don't wont to implement an assembly for retrieving my valid set of members.

Does anyone has any ideas?

Michael

So, what exactly do you get if you run the following MDX query

SELECT {} ON 0, NONEMPTY([DIM_ZWST].[Branches].[Branch].Members, ([DIM_USERS].[LOGINNAME].&[MYDOMAIN\testuser], [MEASURES].[HAS_PERMISSION])) ON 1 FROM Cube

|||

Hi Mosha,

thanks for your reply. I get a list of all branches the user is allowed to see, if I run the query with my admin user. (Running the query with my test user results in the same error as mentioned above)

Michael

|||

Hi all,

I just have another strange effect: If I try editing the attribute security of my branch attribute in Management Studio it will show me a dialog listening exactly my allowed branches saying, that the unqiue member names are no longer valid and I can answer yes or no to the question, if I want to delete these ones.

Michael

|||OK, this should work then... One more guess: Is dimension DIM_ZWST used in any other cube ? If the other cube doesn't have same Permission measure group, that could be a problem, but I think this is not very likely...|||

Hi Mosha,

yes, it is used in another cube, but I have implemented security on the cube dimension not the database dimension, because the latter didn't work at all. I guess that it didn't work because the database dimension doesn't know anything about measure groups of any cubes.

Second, my other cubes shouldn't be visible to that kind of users at all.

Thanks so far,

Michael

|||

Hi all,

I just want to let you know, how I have solved my problem:

I have built a SSIS package that will run every night and that will create, update or delete my roles, depending on what information has changed in the underlying database table. Now I have many roles but in AS2k5 this shouldn't be a problem anymore (I hope ).

Michael

|||

Michael,

Will you give me some hints on what your package looked like? I have the exact same problem but am not sure how to go about creating the roles in SSIS. We had though about using the script component somehow with the api, or something with xml but we are not too sure yet.

Thanks in advance,

Sean

|||

Hi Sean,

sorry, I missed your post, because I didn't get an email for this.

It was a solution for our customer, so at the moment I can't send you any code snippets. I decided to do it rather with a script component inside of a data transformation than with some xml because the xml is a little bit clumsy.

I have database view as a datasource where I get the user logins and the branch ids. This will lead into a script component, e.g. as a destination. There you can use AMO scripting for creating and updating the roles. To distinguish between "normal" roles and "automatic" roles I have used a naming schema like "prefix_" + userid.

For deleting unused roles you can have a script component as datasource which returns userid and branch of the existing role. You can use a lookup with an error path to another script component to delete the unused roles.

Michael

|||

Thanks Michael!

Sean

Problem with dynamic security and dependent dimension (SSAS 2k5)

Hi all,

I try to implement dynamic security and I have a problem with a dependent dimension.

What I want:

Each user is responsible for one or more branches of the company, so each user should see the data the branches, she is responsible for.

What I get:

Error: Das Branch-Attribut in der DIM_ZWST-Dimension weist einen ungültigen Sicherheitsausdruck für eine generierte Dimension auf. (sorry, it's german) It means: The Branch-attribute of the DIM_ZWST-dimension has an invalid security expression for a generated dimension.

This is my setup:

SSAS 2k5

Dimensions:

DIM_USERS with attributes Name, Loginname (this is NT user name and should correspond to the MDX UserName()-function)

DIM_ZWST: These are the branches of the company. Each user should be allowed to view one ore more branches.

Cube:

I have a measure group 'PERMISSIONS' where each combination of a user and a branch has a value of '1' for the measure HAS_PERMISSIONS. Every other combination doesn't exist. I can check this by just browsing that measure.

My other measure groups are related m:n with the DIM_USERS dimension via the PERMISSIONS measure group. Browsing the cube I get all the values I would like to see.

Roles:

After playing around with the STRTOSET and STRTOMEMBER functions, what worked for the DIM_USERS dimension but not for the DIM_ZWST function I tried it with a hardcoded role:

DIM_USERS, allowed group = {[DIM_USERS].[LOGINNAME].&[MYDOMAIN\testuser]} (this one works!)

DIM_ZWST, allowed group = NONEMPTY([DIM_ZWST].[Branches].[Branch].Members, ([DIM_USERS].[LOGINNAME].&[MYDOMAIN\testuser], [MEASURES].[HAS_PERMISSION])) (this one doesn't work!)

I don't wont to implement an assembly for retrieving my valid set of members.

Does anyone has any ideas?

Michael

So, what exactly do you get if you run the following MDX query

SELECT {} ON 0, NONEMPTY([DIM_ZWST].[Branches].[Branch].Members, ([DIM_USERS].[LOGINNAME].&[MYDOMAIN\testuser], [MEASURES].[HAS_PERMISSION])) ON 1 FROM Cube

|||

Hi Mosha,

thanks for your reply. I get a list of all branches the user is allowed to see, if I run the query with my admin user. (Running the query with my test user results in the same error as mentioned above)

Michael

|||

Hi all,

I just have another strange effect: If I try editing the attribute security of my branch attribute in Management Studio it will show me a dialog listening exactly my allowed branches saying, that the unqiue member names are no longer valid and I can answer yes or no to the question, if I want to delete these ones.

Michael

|||OK, this should work then... One more guess: Is dimension DIM_ZWST used in any other cube ? If the other cube doesn't have same Permission measure group, that could be a problem, but I think this is not very likely...|||

Hi Mosha,

yes, it is used in another cube, but I have implemented security on the cube dimension not the database dimension, because the latter didn't work at all. I guess that it didn't work because the database dimension doesn't know anything about measure groups of any cubes.

Second, my other cubes shouldn't be visible to that kind of users at all.

Thanks so far,

Michael

|||

Hi all,

I just want to let you know, how I have solved my problem:

I have built a SSIS package that will run every night and that will create, update or delete my roles, depending on what information has changed in the underlying database table. Now I have many roles but in AS2k5 this shouldn't be a problem anymore (I hope ).

Michael

|||

Michael,

Will you give me some hints on what your package looked like? I have the exact same problem but am not sure how to go about creating the roles in SSIS. We had though about using the script component somehow with the api, or something with xml but we are not too sure yet.

Thanks in advance,

Sean

|||

Hi Sean,

sorry, I missed your post, because I didn't get an email for this.

It was a solution for our customer, so at the moment I can't send you any code snippets. I decided to do it rather with a script component inside of a data transformation than with some xml because the xml is a little bit clumsy.

I have database view as a datasource where I get the user logins and the branch ids. This will lead into a script component, e.g. as a destination. There you can use AMO scripting for creating and updating the roles. To distinguish between "normal" roles and "automatic" roles I have used a naming schema like "prefix_" + userid.

For deleting unused roles you can have a script component as datasource which returns userid and branch of the existing role. You can use a lookup with an error path to another script component to delete the unused roles.

Michael

|||

Thanks Michael!

Sean