delete a security group via the database

We have recently moved buildings at work and have gone from 2 domains to 1
domain. Now when i try and edit the security permission in the properties of
a report i get:
The user or group name 'carfax\domain users' is not recognized.
(rsUnknownUserName) Get Online Help
iis there any way to delete these at a database level or get round this at
all as i need to enable some of our users the functionality to subscribe to
reports and unless i can delete this it will be impossible to do.
Would i need to completely uninstall reporting services fix this problem?I am looking at scripting the deleting of this security group, would this be
the best way to go and could anyone give me any pointers?
Any help would be greatly appreciated
"Shaun Longhurst" wrote:
> We have recently moved buildings at work and have gone from 2 domains to 1
> domain. Now when i try and edit the security permission in the properties of
> a report i get:
> The user or group name 'carfax\domain users' is not recognized.
> (rsUnknownUserName) Get Online Help
> iis there any way to delete these at a database level or get round this at
> all as i need to enable some of our users the functionality to subscribe to
> reports and unless i can delete this it will be impossible to do.
> Would i need to completely uninstall reporting services fix this problem?
>|||I have also looked through the users and policy user role tables and cannot
find the invalid user anywhere.
Still pulling my hair out trying to sort this issue.
"Shaun Longhurst" wrote:
> I am looking at scripting the deleting of this security group, would this be
> the best way to go and could anyone give me any pointers?
>
> Any help would be greatly appreciated
>
> "Shaun Longhurst" wrote:
> > We have recently moved buildings at work and have gone from 2 domains to 1
> > domain. Now when i try and edit the security permission in the properties of
> > a report i get:
> >
> > The user or group name 'carfax\domain users' is not recognized.
> > (rsUnknownUserName) Get Online Help
> >
> > iis there any way to delete these at a database level or get round this at
> > all as i need to enable some of our users the functionality to subscribe to
> > reports and unless i can delete this it will be impossible to do.
> >
> > Would i need to completely uninstall reporting services fix this problem?
> >
> >

Degraded performance after applying SP4 on SQL 2000 -- repost for no response

Hi group,
I am seeing degraded performance after applying SP4 on SQL 2000.
My specific queries (they are in stored proc) access linked server. When
the process is run, I keep seeing blocks on the linked server side among
processes of the same spid spun off from the calling process like this:
A. a process (say spid 58) on initiating server runs, some times shows
blocked by a process (say spid 111) on the linked server, some times not
displaying the block but just continue to run slow;
B. a few processes (spid 111) on the linked server running, often showing
one or two of them blocked by another spid 111.
I have googled and found that there are reports of such slowed down
performance after SP4 application, not necessarily related to the use of
linked server. There is an explanation by Microsoft why processes could
show being blocked by process of the same spid. Problem is that it looks
like no solution to the problem and nor explanation why the switch of locks
is so slow.
From what I read other people report, this blocking among processes of the
same spid repeats itself so often and is so slow that you can easily catch
the display of
such blocks in EM. This is the same case by me: when the process is
running, I
easily see such blocks with refresh of EM.
My process used to run about 30 sec on SP3a, now it runs some 15 min. The
only troubleshoot hint I saw sofar is the correct setup of MS DTC, which I
checked on my systems and is set up properly.
Wish to hear from you.
QuentinThe parallel process blocking occurred long before you applied SP4.
Microsoft added visibility to that blocking in SP4 so DBAs could manage
parallelism better. Reduce the degree of parallelism on your query, or even
server-wide, and you will see the self-blocking go away.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
news:uHMgUq0JGHA.2088@.TK2MSFTNGP11.phx.gbl...
> Hi group,
> I am seeing degraded performance after applying SP4 on SQL 2000.
> My specific queries (they are in stored proc) access linked server. When
> the process is run, I keep seeing blocks on the linked server side among
> processes of the same spid spun off from the calling process like this:
> A. a process (say spid 58) on initiating server runs, some times shows
> blocked by a process (say spid 111) on the linked server, some times not
> displaying the block but just continue to run slow;
> B. a few processes (spid 111) on the linked server running, often showing
> one or two of them blocked by another spid 111.
> I have googled and found that there are reports of such slowed down
> performance after SP4 application, not necessarily related to the use of
> linked server. There is an explanation by Microsoft why processes could
> show being blocked by process of the same spid. Problem is that it looks
> like no solution to the problem and nor explanation why the switch of
> locks is so slow.
> From what I read other people report, this blocking among processes of the
> same spid repeats itself so often and is so slow that you can easily catch
> the display of
> such blocks in EM. This is the same case by me: when the process is
> running, I
> easily see such blocks with refresh of EM.
> My process used to run about 30 sec on SP3a, now it runs some 15 min. The
> only troubleshoot hint I saw sofar is the correct setup of MS DTC, which I
> checked on my systems and is set up properly.
> Wish to hear from you.
> Quentin
>
>|||Geoff, Quentin read this link:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=58294&whichpage=1
This SP4 problem is widespread and how can it be justifed, "I/O latch
jargon" or not this is not a feature, but an optimizer bug.
Queries/processes blocking themselves, that ain't parallelism. This
has been ignored by MS for too long.
John Lamarsh
Technical Manager
Full Circle Systems Inc.
Geoff N. Hiten wrote:
> The parallel process blocking occurred long before you applied SP4.
> Microsoft added visibility to that blocking in SP4 so DBAs could manage
> parallelism better. Reduce the degree of parallelism on your query, or even
> server-wide, and you will see the self-blocking go away.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
> news:uHMgUq0JGHA.2088@.TK2MSFTNGP11.phx.gbl...
> > Hi group,
> >
> > I am seeing degraded performance after applying SP4 on SQL 2000.
> >
> > My specific queries (they are in stored proc) access linked server. When
> > the process is run, I keep seeing blocks on the linked server side among
> > processes of the same spid spun off from the calling process like this:
> >
> > A. a process (say spid 58) on initiating server runs, some times shows
> > blocked by a process (say spid 111) on the linked server, some times not
> > displaying the block but just continue to run slow;
> >
> > B. a few processes (spid 111) on the linked server running, often showing
> > one or two of them blocked by another spid 111.
> >
> > I have googled and found that there are reports of such slowed down
> > performance after SP4 application, not necessarily related to the use of
> > linked server. There is an explanation by Microsoft why processes could
> > show being blocked by process of the same spid. Problem is that it looks
> > like no solution to the problem and nor explanation why the switch of
> > locks is so slow.
> > From what I read other people report, this blocking among processes of the
> > same spid repeats itself so often and is so slow that you can easily catch
> > the display of
> > such blocks in EM. This is the same case by me: when the process is
> > running, I
> > easily see such blocks with refresh of EM.
> >
> > My process used to run about 30 sec on SP3a, now it runs some 15 min. The
> > only troubleshoot hint I saw sofar is the correct setup of MS DTC, which I
> > checked on my systems and is set up properly.
> >
> > Wish to hear from you.
> >
> > Quentin
> >
> >
> >|||Thanks for both responses.
As John stated, this SP4 problem appear to be widespread. Other posts I
read through my google search support this. It does not have anything to do
whether the blocking between same spid shows up or not -- I don't care that.
It has to with the slow processing.
Strange enough that though I saw many complaint similar to mine (mostly do
not have linked server in the play), there is no solution reported, and no
heavy complaint that it does not get fixed, until I see the link John
provided. I guess it is a slow down that many can still afford, but my case
and in Jerry's case it was not.
Any additional comments are still welcome since I am still troubleshooting
this...
Quentin
<john@.fullcirclesystems.com> wrote in message
news:1138814051.851469.199190@.g14g2000cwa.googlegroups.com...
> Geoff, Quentin read this link:
> http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=58294&whichpage=1
> This SP4 problem is widespread and how can it be justifed, "I/O latch
> jargon" or not this is not a feature, but an optimizer bug.
> Queries/processes blocking themselves, that ain't parallelism. This
> has been ignored by MS for too long.
> John Lamarsh
> Technical Manager
> Full Circle Systems Inc.
> Geoff N. Hiten wrote:
>> The parallel process blocking occurred long before you applied SP4.
>> Microsoft added visibility to that blocking in SP4 so DBAs could manage
>> parallelism better. Reduce the degree of parallelism on your query, or
>> even
>> server-wide, and you will see the self-blocking go away.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>>
>>
>> "Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
>> news:uHMgUq0JGHA.2088@.TK2MSFTNGP11.phx.gbl...
>> > Hi group,
>> >
>> > I am seeing degraded performance after applying SP4 on SQL 2000.
>> >
>> > My specific queries (they are in stored proc) access linked server.
>> > When
>> > the process is run, I keep seeing blocks on the linked server side
>> > among
>> > processes of the same spid spun off from the calling process like this:
>> >
>> > A. a process (say spid 58) on initiating server runs, some times shows
>> > blocked by a process (say spid 111) on the linked server, some times
>> > not
>> > displaying the block but just continue to run slow;
>> >
>> > B. a few processes (spid 111) on the linked server running, often
>> > showing
>> > one or two of them blocked by another spid 111.
>> >
>> > I have googled and found that there are reports of such slowed down
>> > performance after SP4 application, not necessarily related to the use
>> > of
>> > linked server. There is an explanation by Microsoft why processes
>> > could
>> > show being blocked by process of the same spid. Problem is that it
>> > looks
>> > like no solution to the problem and nor explanation why the switch of
>> > locks is so slow.
>> > From what I read other people report, this blocking among processes of
>> > the
>> > same spid repeats itself so often and is so slow that you can easily
>> > catch
>> > the display of
>> > such blocks in EM. This is the same case by me: when the process is
>> > running, I
>> > easily see such blocks with refresh of EM.
>> >
>> > My process used to run about 30 sec on SP3a, now it runs some 15 min.
>> > The
>> > only troubleshoot hint I saw sofar is the correct setup of MS DTC,
>> > which I
>> > checked on my systems and is set up properly.
>> >
>> > Wish to hear from you.
>> >
>> > Quentin
>> >
>> >
>> >
>|||It appears to be related to procedure call. When I execute the stored proc
itself, the process runs for 3 min, but when I execute the content of the
stored proc in QA it takes 15 seconds.|||Hello
I had the same problem when executing the store procedure and when
executing just the code of the procedure on QA. Executing the procedure
took more than 1 hour. Executing just the code took 14 seconds!
I opened a case on PSS and the problem was related to "parameter
snifing". There is an excelent post on Ken Herdenson Blog about the
subject. http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx
The solution that PSS gave to my problem was a little bit diferent from
the solution that is on Ken's Blog.
Here is what I did:
I have a strore procedure that have only one parameter as an input
value. This parameter is used all long the code. What PSS instruct me
was to not use the store procedure parameter but a local variable
throught the code.
Example of the old code:
create procedure @.foo datetime
as
select * from bar where foo = @.foo
Example of the new code:
create procedure @.foo_1 datetime
as
declare @.foo datetime
select @.foo=@.foo_1
select * from bar where foo=@.foo
The change was very simple but made a huge impact on performance. After
the change the store procedure took just 10 seconds.
Maybe you should give a try.
Regards
Carlos Selonke
http://carlos.geekbunker.org

Degraded performance after applying SP4 on SQL 2000 -- repost for no response

Hi group,
I am seeing degraded performance after applying SP4 on SQL 2000.
My specific queries (they are in stored proc) access linked server. When
the process is run, I keep seeing blocks on the linked server side among
processes of the same spid spun off from the calling process like this:
A. a process (say spid 58) on initiating server runs, some times shows
blocked by a process (say spid 111) on the linked server, some times not
displaying the block but just continue to run slow;
B. a few processes (spid 111) on the linked server running, often showing
one or two of them blocked by another spid 111.
I have googled and found that there are reports of such slowed down
performance after SP4 application, not necessarily related to the use of
linked server. There is an explanation by Microsoft why processes could
show being blocked by process of the same spid. Problem is that it looks
like no solution to the problem and nor explanation why the switch of locks
is so slow.
From what I read other people report, this blocking among processes of the
same spid repeats itself so often and is so slow that you can easily catch
the display of
such blocks in EM. This is the same case by me: when the process is
running, I
easily see such blocks with refresh of EM.
My process used to run about 30 sec on SP3a, now it runs some 15 min. The
only troubleshoot hint I saw sofar is the correct setup of MS DTC, which I
checked on my systems and is set up properly.
Wish to hear from you.
Quentin
The parallel process blocking occurred long before you applied SP4.
Microsoft added visibility to that blocking in SP4 so DBAs could manage
parallelism better. Reduce the degree of parallelism on your query, or even
server-wide, and you will see the self-blocking go away.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
news:uHMgUq0JGHA.2088@.TK2MSFTNGP11.phx.gbl...
> Hi group,
> I am seeing degraded performance after applying SP4 on SQL 2000.
> My specific queries (they are in stored proc) access linked server. When
> the process is run, I keep seeing blocks on the linked server side among
> processes of the same spid spun off from the calling process like this:
> A. a process (say spid 58) on initiating server runs, some times shows
> blocked by a process (say spid 111) on the linked server, some times not
> displaying the block but just continue to run slow;
> B. a few processes (spid 111) on the linked server running, often showing
> one or two of them blocked by another spid 111.
> I have googled and found that there are reports of such slowed down
> performance after SP4 application, not necessarily related to the use of
> linked server. There is an explanation by Microsoft why processes could
> show being blocked by process of the same spid. Problem is that it looks
> like no solution to the problem and nor explanation why the switch of
> locks is so slow.
> From what I read other people report, this blocking among processes of the
> same spid repeats itself so often and is so slow that you can easily catch
> the display of
> such blocks in EM. This is the same case by me: when the process is
> running, I
> easily see such blocks with refresh of EM.
> My process used to run about 30 sec on SP3a, now it runs some 15 min. The
> only troubleshoot hint I saw sofar is the correct setup of MS DTC, which I
> checked on my systems and is set up properly.
> Wish to hear from you.
> Quentin
>
>
|||Geoff, Quentin read this link:
http://www.sqlteam.com/forums/topic...94&whichpage=1
This SP4 problem is widespread and how can it be justifed, "I/O latch
jargon" or not this is not a feature, but an optimizer bug.
Queries/processes blocking themselves, that ain't parallelism. This
has been ignored by MS for too long.
John Lamarsh
Technical Manager
Full Circle Systems Inc.
Geoff N. Hiten wrote:[vbcol=seagreen]
> The parallel process blocking occurred long before you applied SP4.
> Microsoft added visibility to that blocking in SP4 so DBAs could manage
> parallelism better. Reduce the degree of parallelism on your query, or even
> server-wide, and you will see the self-blocking go away.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
> news:uHMgUq0JGHA.2088@.TK2MSFTNGP11.phx.gbl...
|||Thanks for both responses.
As John stated, this SP4 problem appear to be widespread. Other posts I
read through my google search support this. It does not have anything to do
whether the blocking between same spid shows up or not -- I don't care that.
It has to with the slow processing.
Strange enough that though I saw many complaint similar to mine (mostly do
not have linked server in the play), there is no solution reported, and no
heavy complaint that it does not get fixed, until I see the link John
provided. I guess it is a slow down that many can still afford, but my case
and in Jerry's case it was not.
Any additional comments are still welcome since I am still troubleshooting
this...
Quentin
<john@.fullcirclesystems.com> wrote in message
news:1138814051.851469.199190@.g14g2000cwa.googlegr oups.com...
> Geoff, Quentin read this link:
> http://www.sqlteam.com/forums/topic...94&whichpage=1
> This SP4 problem is widespread and how can it be justifed, "I/O latch
> jargon" or not this is not a feature, but an optimizer bug.
> Queries/processes blocking themselves, that ain't parallelism. This
> has been ignored by MS for too long.
> John Lamarsh
> Technical Manager
> Full Circle Systems Inc.
> Geoff N. Hiten wrote:
>
|||It appears to be related to procedure call. When I execute the stored proc
itself, the process runs for 3 min, but when I execute the content of the
stored proc in QA it takes 15 seconds.
|||Hello
I had the same problem when executing the store procedure and when
executing just the code of the procedure on QA. Executing the procedure
took more than 1 hour. Executing just the code took 14 seconds!
I opened a case on PSS and the problem was related to "parameter
snifing". There is an excelent post on Ken Herdenson Blog about the
subject. http://blogs.msdn.com/khen1234/archi...02/424228.aspx
The solution that PSS gave to my problem was a little bit diferent from
the solution that is on Ken's Blog.
Here is what I did:
I have a strore procedure that have only one parameter as an input
value. This parameter is used all long the code. What PSS instruct me
was to not use the store procedure parameter but a local variable
throught the code.
Example of the old code:
create procedure @.foo datetime
as
select * from bar where foo = @.foo
Example of the new code:
create procedure @.foo_1 datetime
as
declare @.foo datetime
select @.foo=@.foo_1
select * from bar where foo=@.foo
The change was very simple but made a huge impact on performance. After
the change the store procedure took just 10 seconds.
Maybe you should give a try.
Regards
Carlos Selonke
http://carlos.geekbunker.org

Degraded performance after applying SP4 on SQL 2000 -- repost for no response

Hi group,
I am seeing degraded performance after applying SP4 on SQL 2000.
My specific queries (they are in stored proc) access linked server. When
the process is run, I keep seeing blocks on the linked server side among
processes of the same spid spun off from the calling process like this:
A. a process (say spid 58) on initiating server runs, some times shows
blocked by a process (say spid 111) on the linked server, some times not
displaying the block but just continue to run slow;
B. a few processes (spid 111) on the linked server running, often showing
one or two of them blocked by another spid 111.
I have googled and found that there are reports of such slowed down
performance after SP4 application, not necessarily related to the use of
linked server. There is an explanation by Microsoft why processes could
show being blocked by process of the same spid. Problem is that it looks
like no solution to the problem and nor explanation why the switch of locks
is so slow.
From what I read other people report, this blocking among processes of the
same spid repeats itself so often and is so slow that you can easily catch
the display of
such blocks in EM. This is the same case by me: when the process is
running, I
easily see such blocks with refresh of EM.
My process used to run about 30 sec on SP3a, now it runs some 15 min. The
only troubleshoot hint I saw sofar is the correct setup of MS DTC, which I
checked on my systems and is set up properly.
Wish to hear from you.
QuentinThe parallel process blocking occurred long before you applied SP4.
Microsoft added visibility to that blocking in SP4 so DBAs could manage
parallelism better. Reduce the degree of parallelism on your query, or even
server-wide, and you will see the self-blocking go away.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
news:uHMgUq0JGHA.2088@.TK2MSFTNGP11.phx.gbl...
> Hi group,
> I am seeing degraded performance after applying SP4 on SQL 2000.
> My specific queries (they are in stored proc) access linked server. When
> the process is run, I keep seeing blocks on the linked server side among
> processes of the same spid spun off from the calling process like this:
> A. a process (say spid 58) on initiating server runs, some times shows
> blocked by a process (say spid 111) on the linked server, some times not
> displaying the block but just continue to run slow;
> B. a few processes (spid 111) on the linked server running, often showing
> one or two of them blocked by another spid 111.
> I have googled and found that there are reports of such slowed down
> performance after SP4 application, not necessarily related to the use of
> linked server. There is an explanation by Microsoft why processes could
> show being blocked by process of the same spid. Problem is that it looks
> like no solution to the problem and nor explanation why the switch of
> locks is so slow.
> From what I read other people report, this blocking among processes of the
> same spid repeats itself so often and is so slow that you can easily catch
> the display of
> such blocks in EM. This is the same case by me: when the process is
> running, I
> easily see such blocks with refresh of EM.
> My process used to run about 30 sec on SP3a, now it runs some 15 min. The
> only troubleshoot hint I saw sofar is the correct setup of MS DTC, which I
> checked on my systems and is set up properly.
> Wish to hear from you.
> Quentin
>
>|||Geoff, Quentin read this link:
http://www.sqlteam.com/forums/topic...294&whichpage=1
This SP4 problem is widespread and how can it be justifed, "I/O latch
jargon" or not this is not a feature, but an optimizer bug.
Queries/processes blocking themselves, that ain't parallelism. This
has been ignored by MS for too long.
John Lamarsh
Technical Manager
Full Circle Systems Inc.
Geoff N. Hiten wrote:[vbcol=seagreen]
> The parallel process blocking occurred long before you applied SP4.
> Microsoft added visibility to that blocking in SP4 so DBAs could manage
> parallelism better. Reduce the degree of parallelism on your query, or ev
en
> server-wide, and you will see the self-blocking go away.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
> news:uHMgUq0JGHA.2088@.TK2MSFTNGP11.phx.gbl...|||Thanks for both responses.
As John stated, this SP4 problem appear to be widespread. Other posts I
read through my google search support this. It does not have anything to do
whether the blocking between same spid shows up or not -- I don't care that.
It has to with the slow processing.
Strange enough that though I saw many complaint similar to mine (mostly do
not have linked server in the play), there is no solution reported, and no
heavy complaint that it does not get fixed, until I see the link John
provided. I guess it is a slow down that many can still afford, but my case
and in Jerry's case it was not.
Any additional comments are still welcome since I am still troubleshooting
this...
Quentin
<john@.fullcirclesystems.com> wrote in message
news:1138814051.851469.199190@.g14g2000cwa.googlegroups.com...
> Geoff, Quentin read this link:
> http://www.sqlteam.com/forums/topic...294&whichpage=1
> This SP4 problem is widespread and how can it be justifed, "I/O latch
> jargon" or not this is not a feature, but an optimizer bug.
> Queries/processes blocking themselves, that ain't parallelism. This
> has been ignored by MS for too long.
> John Lamarsh
> Technical Manager
> Full Circle Systems Inc.
> Geoff N. Hiten wrote:
>|||It appears to be related to procedure call. When I execute the stored proc
itself, the process runs for 3 min, but when I execute the content of the
stored proc in QA it takes 15 seconds.|||Hello
I had the same problem when executing the store procedure and when
executing just the code of the procedure on QA. Executing the procedure
took more than 1 hour. Executing just the code took 14 seconds!
I opened a case on PSS and the problem was related to "parameter
snifing". There is an excelent post on Ken Herdenson Blog about the
subject. http://blogs.msdn.com/khen1234/arch.../02/424228.aspx
The solution that PSS gave to my problem was a little bit diferent from
the solution that is on Ken's Blog.
Here is what I did:
I have a strore procedure that have only one parameter as an input
value. This parameter is used all long the code. What PSS instruct me
was to not use the store procedure parameter but a local variable
throught the code.
Example of the old code:
create procedure @.foo datetime
as
select * from bar where foo = @.foo
Example of the new code:
create procedure @.foo_1 datetime
as
declare @.foo datetime
select @.foo=@.foo_1
select * from bar where foo=@.foo
The change was very simple but made a huge impact on performance. After
the change the store procedure took just 10 seconds.
Maybe you should give a try.
Regards
Carlos Selonke
http://carlos.geekbunker.orgsql

Defining Hirarchies Accross Dimensions

I have a cube whose dimensions include Product and Product Group. The Product Group has a Many-To-Many relationship with the cube through a bridge table to the Product dimension. Whenever I want to see the sales for product by product group on the cube browser I associate these two dimensions manually (dragging and dropping to create a hierarchy). Is it possible to define this hierarchy in the Product or Product Group dimension? Thanks in advance

+ Product Group

++++ Product

No, it is not possible to create a hierarchy spawns several dimensions.

But on the other side, try to see is there way you might be able to model your Product dimension to include ProductGroup attribute in it.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thanks,

I know you should include these things in the product dimension, but the thing is that according to the business rules, these product groups could be anything because the users define these dynamically: regional areas, brands, etc. Moreover, a product could belong to many product groups and viceversa. This is why I had to create the group as a separate dimension.

Defining custom column groups on a matrix report in report builder

I have table called Buildings which has market and sq. ft information.
I want to create a matrix report that has the market as the row group and
sq. ft ranges as the column group and shows the # of buildings in each
market that fall in the sq. ft ranges.
Sq. ft ranges are 0-5000, 5001-10000 etc...
My questions is a) is this something that can be done using report builder
b) how do i define the sq. ft ranges
c) how do I find the count of buildings whose sq. footage falls within the
range.
ThanksOn Nov 14, 1:18 pm, "shikarishambu" <shikarishamb...@.hotmail.com>
wrote:
> I have table called Buildings which has market and sq. ft information.
> I want to create a matrix report that has the market as the row group and
> sq. ft ranges as the column group and shows the # of buildings in each
> market that fall in the sq. ft ranges.
> Sq. ft ranges are 0-5000, 5001-10000 etc...
> My questions is a) is this something that can be done using report builder
> b) how do i define the sq. ft ranges
> c) how do I find the count of buildings whose sq. footage falls within the
> range.
> Thanks
A) Yes
B) In your Datasets window, right-click on the name of the dataset and
Add a new field. Call it "Sq Foot Range" and make it a calculated
field. Use an expression like
= Fields!SqFoot.Value - Fields!SqFoot.Value Mod 5000
Since you are shifting your range to include the evenly divided number
in the lower group, you really want
= CStr( ( (X-1) - (X-1) Mod 5000 ) + 1 ) & " to " & CStr( ( (X-1) -
(X-1) Mod 5000 ) + 5000 )
C) Create a Matrix with a Market in the Row Group, Sq Foot Range in
the Column Group, and Count( Fields!SqFoot.Value ) in the Details.
Hope that helps.
-- Scott

Default Value?

Hi, I'm creating a dynamic group of values using SELECT and UNION

Example:
(SELECT Description = 'Changed PhoneNumber from ' + @.old_PhoneNumber + ' to ' + @.PhoneNumber
WHERE @.PhoneNumber <> @.old_PhoneNumber UNION ALL

SELECT Description = 'Changed FaxNumber from ' + @.old_FaxNumber + ' to ' + @.FaxNumber
WHERE @.FaxNumber <> @.old_FaxNumber UNION ALL

SELECT Description = 'Changed EmailAddress from ' + @.old_EmailAddress + ' to ' + @.EmailAddress
WHERE @.EmailAddress <> @.old_EmailAddress)

The problem here is that SQL Server thinks "Description" is an int (by default probably) and gives me an error when I try to assign a string to it.

I'm taking that information and using it as a field in a INSERT INTO ... SELECT statement, so I don't think I am able to use a DECLARE statement or if that would even work.

Does anyone know how I can make it so that Description is always a varchar?

Maybe?

SELECT 'Changed PhoneNumber from ' + @.old_PhoneNumber + ' to ' + @.PhoneNumber AS Description

You could also do this:

SELECT CAST('Changed PhoneNumber from ' + @.old_PhoneNumber + ' to ' + @.PhoneNumber AS varchar) AS Description

OR:

SELECT 'Changed PhoneNumber from ' + CAST(@.old_PhoneNumber AS varchar) + ' to ' + cast(@.PhoneNumber AS varchar) AS Description

|||

The third option worked, but I only needed to do it with the Integers. Since there were integers in the string SQL Server tried to convert the entire string into an integer across every SELECT command in the union.

So since I had an integer many SELECTs down it was telling me "can't convert name to integer" even though there was no integer in sight of that particular SELECT statement. Pretty confusing if you ask me.

Default value in SP

Hello group,
I use SP to update value in myTable,
MyTable have a few columns (about 15) and I want update not all field.
Sometime I need update column 2,3,5 and next time 4,5,11.
If parametr is not defined leave current value
My question is how set as default value of parameter equal current value in
table?
My template of procedure (SP)
alter procedure MySP
(
@.idRow int //it is row id and ever is define
@.par1 int,
@.par2 int,
....
@.par15 int,
)
AS
Update MyTable set col1=@.par1, col2=@.par2 ... col15=@.par15 where idRow =
@.idRow
I want call MySP: MySP 2,4,,,,,,,,...,29 or MySP 4,4,1,3,...,34,33,12 or
MySP 1,,,,,...,45
Thx PawelRUPDATE MyTable SET col1 = COALESCE(@.par1, col1), ...
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"PawelR" <pawelratajczak@.poczta.onet.pl> wrote in message
news:%23Q95ibzRFHA.2964@.TK2MSFTNGP15.phx.gbl...
> Hello group,
> I use SP to update value in myTable,
> MyTable have a few columns (about 15) and I want update not all field.
> Sometime I need update column 2,3,5 and next time 4,5,11.
> If parametr is not defined leave current value
> My question is how set as default value of parameter equal current value
> in table?
> My template of procedure (SP)
> alter procedure MySP
> (
> @.idRow int //it is row id and ever is define
> @.par1 int,
> @.par2 int,
> .....
> @.par15 int,
> )
> AS
> Update MyTable set col1=@.par1, col2=@.par2 ... col15=@.par15 where idRow =
> @.idRow
> I want call MySP: MySP 2,4,,,,,,,,...,29 or MySP 4,4,1,3,...,34,33,12 or
> MySP 1,,,,,...,45
>
> Thx PawelR
>|||One option can be to use NULL as the default value and use functions ISNULL
or COALESCE in the UPDATE statement.
alter procedure MySP
(
@.idRow int //it is row id and ever is define
@.par1 int,
@.par2 int = null,
@.par3 int = null,
@.par4 intl,
@.par5 int = null,
....
@.par15 int,
)
AS
set nocount on
Update MyTable
set col1=@.par1, col2=coalesce(@.par2, col2), col3=coalesce(@.par3, col3),
col5=coalesce(@.par5, col5), ... col15=@.par15 where idRow = @.idRow
return @.@.error
go
"PawelR" wrote:

> Hello group,
> I use SP to update value in myTable,
> MyTable have a few columns (about 15) and I want update not all field.
> Sometime I need update column 2,3,5 and next time 4,5,11.
> If parametr is not defined leave current value
> My question is how set as default value of parameter equal current value i
n
> table?
> My template of procedure (SP)
> alter procedure MySP
> (
> @.idRow int //it is row id and ever is define
> @.par1 int,
> @.par2 int,
> .....
> @.par15 int,
> )
> AS
> Update MyTable set col1=@.par1, col2=@.par2 ... col15=@.par15 where idRow =
> @.idRow
> I want call MySP: MySP 2,4,,,,,,,,...,29 or MySP 4,4,1,3,...,34,33,12 or
> MySP 1,,,,,...,45
>
> Thx PawelR
>
>|||let the default value be null, or some other non-applicable value. something
like this:
alter procedure MySP
(
@.idRow int
@.par1 int=null,
@.par2 int=null,
....
@.par15 int,
)
AS
Update MyTable set col1=coalesce(@.par1,col1),
col2=coalesce(@.par2,col2),
..
where idRow = @.idRow
dean
"PawelR" <pawelratajczak@.poczta.onet.pl> wrote in message
news:%23Q95ibzRFHA.2964@.TK2MSFTNGP15.phx.gbl...
> Hello group,
> I use SP to update value in myTable,
> MyTable have a few columns (about 15) and I want update not all field.
> Sometime I need update column 2,3,5 and next time 4,5,11.
> If parametr is not defined leave current value
> My question is how set as default value of parameter equal current value
> in table?
> My template of procedure (SP)
> alter procedure MySP
> (
> @.idRow int //it is row id and ever is define
> @.par1 int,
> @.par2 int,
> .....
> @.par15 int,
> )
> AS
> Update MyTable set col1=@.par1, col2=@.par2 ... col15=@.par15 where idRow =
> @.idRow
> I want call MySP: MySP 2,4,,,,,,,,...,29 or MySP 4,4,1,3,...,34,33,12 or
> MySP 1,,,,,...,45
>
> Thx PawelR
>

default rs settings

is there a simple manual how to setup RS.

with the administrators group im able to view my reports. but normal users are not able to view anything. they can enter reporting services, but not open a report.

The answer to your problem is to go into Report Manager, click on the properties tab, and add a New Role Assignment.

In the Group or User name box, type Everyone, and then below that, select Browser.

Click on OK.

You can look thru the BOL to see this also. Also, check out the many ssrs blogs online.

hth

BobP

Default Recovery Model

Hello Group,
why does the default install of SQL Server 2000 place the master db in the
simple recover model? should I switch to the full model and then I can use
trans logs for point in time recover. What is the normal setting for the
recovery of the master db?
Rich,
User-defined objects should not be created in the Master system database.
The recovery model is SIMPLE and cannot be changed.
HTH
Jerry
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:6B7DDC52-F7D6-43EF-8F5D-AB60957DBF29@.microsoft.com...
> Hello Group,
> why does the default install of SQL Server 2000 place the master db in the
> simple recover model? should I switch to the full model and then I can
> use
> trans logs for point in time recover. What is the normal setting for the
> recovery of the master db?
|||Probably because the master isn't written to nearly as frequently as user
databases or even msdb. Of course, I'm just guessing.
I leave it at simple, as my master get backed up daily (nightly?)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:6B7DDC52-F7D6-43EF-8F5D-AB60957DBF29@.microsoft.com...
> Hello Group,
> why does the default install of SQL Server 2000 place the master db in the
> simple recover model? should I switch to the full model and then I can
> use
> trans logs for point in time recover. What is the normal setting for the
> recovery of the master db?
|||Or rather...should not be changed.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eqnZDW1yFHA.460@.TK2MSFTNGP15.phx.gbl...
> Rich,
> User-defined objects should not be created in the Master system database.
> The recovery model is SIMPLE and cannot be changed.
> HTH
> Jerry
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:6B7DDC52-F7D6-43EF-8F5D-AB60957DBF29@.microsoft.com...
>
|||Rich,
Kevin brings up another key topic - be sure to backup the Master and MSDB
database anytime changes occur that affect them (daily would be suggested).
HTH
Jerry
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:uQavLW1yFHA.1264@.tk2msftngp13.phx.gbl...
> Probably because the master isn't written to nearly as frequently as user
> databases or even msdb. Of course, I'm just guessing.
> I leave it at simple, as my master get backed up daily (nightly?)
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:6B7DDC52-F7D6-43EF-8F5D-AB60957DBF29@.microsoft.com...
>
|||Hello Jerry,
I do a full backup of Master nightly. I was adding a seporate job to backup
only the trans log to try to do a point in time recover. Is the master
locked into simple?
"Jerry Spivey" wrote:

> Rich,
> Kevin brings up another key topic - be sure to backup the Master and MSDB
> database anytime changes occur that affect them (daily would be suggested).
> HTH
> Jerry
> "Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
> news:uQavLW1yFHA.1264@.tk2msftngp13.phx.gbl...
>
>
|||Rich,
You can change it to FULL but you still not be able to perform transaction
log backups so what's the point. Here is the error you will get when you
try a transaction log backup of the Master system database:
Server: Msg 4212, Level 16, State 1, Line 1
Cannot back up the log of the master database. Use BACKUP DATABASE instead.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
HTH
Jerry
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:F957C681-26E6-4FC6-B2CA-8338D1D70B13@.microsoft.com...[vbcol=seagreen]
> Hello Jerry,
> I do a full backup of Master nightly. I was adding a seporate job to
> backup
> only the trans log to try to do a point in time recover. Is the master
> locked into simple?
> "Jerry Spivey" wrote:
|||Hello Jerry,
you are correct, it appears to be locked by Microsoft. I just wanted to be
sure you could not do it. I will remove Master from the trans log backups.
Now what about the MSDB, is it "locked" in the same manner?
Rich
"Jerry Spivey" wrote:

> Rich,
> You can change it to FULL but you still not be able to perform transaction
> log backups so what's the point. Here is the error you will get when you
> try a transaction log backup of the Master system database:
> Server: Msg 4212, Level 16, State 1, Line 1
> Cannot back up the log of the master database. Use BACKUP DATABASE instead.
> Server: Msg 3013, Level 16, State 1, Line 1
> BACKUP LOG is terminating abnormally.
>
> HTH
> Jerry
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:F957C681-26E6-4FC6-B2CA-8338D1D70B13@.microsoft.com...
>
>
|||Rich,
No.
HTH
Jerry
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:1AD22F41-6494-41F5-8852-692714EB366C@.microsoft.com...[vbcol=seagreen]
> Hello Jerry,
> you are correct, it appears to be locked by Microsoft. I just wanted to
> be
> sure you could not do it. I will remove Master from the trans log
> backups.
> Now what about the MSDB, is it "locked" in the same manner?
> Rich
> "Jerry Spivey" wrote:
|||Hi,
No, For MSDB you can set to FULL recovery model and do the transaction log
backup.
But my recommendation is:-
1. Master database :- Do a full database backup every day night
2. MSDB:- Do full database backup every 4 hours (Since it stores backup and
restore informations)
3. user databases:- Set FULL recovery model and do Transaction log backup
every 30 minutes
Thanks
Hari
SQL Server MVP
Thanks
Hari
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:1AD22F41-6494-41F5-8852-692714EB366C@.microsoft.com...[vbcol=seagreen]
> Hello Jerry,
> you are correct, it appears to be locked by Microsoft. I just wanted to
> be
> sure you could not do it. I will remove Master from the trans log
> backups.
> Now what about the MSDB, is it "locked" in the same manner?
> Rich
> "Jerry Spivey" wrote:

Default Recovery Model

Hello Group,
why does the default install of SQL Server 2000 place the master db in the
simple recover model? should I switch to the full model and then I can use
trans logs for point in time recover. What is the normal setting for the
recovery of the master db?Rich,
User-defined objects should not be created in the Master system database.
The recovery model is SIMPLE and cannot be changed.
HTH
Jerry
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:6B7DDC52-F7D6-43EF-8F5D-AB60957DBF29@.microsoft.com...
> Hello Group,
> why does the default install of SQL Server 2000 place the master db in the
> simple recover model? should I switch to the full model and then I can
> use
> trans logs for point in time recover. What is the normal setting for the
> recovery of the master db?|||Probably because the master isn't written to nearly as frequently as user
databases or even msdb. Of course, I'm just guessing.
I leave it at simple, as my master get backed up daily (nightly?)
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:6B7DDC52-F7D6-43EF-8F5D-AB60957DBF29@.microsoft.com...
> Hello Group,
> why does the default install of SQL Server 2000 place the master db in the
> simple recover model? should I switch to the full model and then I can
> use
> trans logs for point in time recover. What is the normal setting for the
> recovery of the master db?|||Or rather...should not be changed.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eqnZDW1yFHA.460@.TK2MSFTNGP15.phx.gbl...
> Rich,
> User-defined objects should not be created in the Master system database.
> The recovery model is SIMPLE and cannot be changed.
> HTH
> Jerry
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:6B7DDC52-F7D6-43EF-8F5D-AB60957DBF29@.microsoft.com...
>> Hello Group,
>> why does the default install of SQL Server 2000 place the master db in
>> the
>> simple recover model? should I switch to the full model and then I can
>> use
>> trans logs for point in time recover. What is the normal setting for the
>> recovery of the master db?
>|||Rich,
Kevin brings up another key topic - be sure to backup the Master and MSDB
database anytime changes occur that affect them (daily would be suggested).
HTH
Jerry
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:uQavLW1yFHA.1264@.tk2msftngp13.phx.gbl...
> Probably because the master isn't written to nearly as frequently as user
> databases or even msdb. Of course, I'm just guessing.
> I leave it at simple, as my master get backed up daily (nightly?)
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:6B7DDC52-F7D6-43EF-8F5D-AB60957DBF29@.microsoft.com...
>> Hello Group,
>> why does the default install of SQL Server 2000 place the master db in
>> the
>> simple recover model? should I switch to the full model and then I can
>> use
>> trans logs for point in time recover. What is the normal setting for the
>> recovery of the master db?
>|||Hello Jerry,
I do a full backup of Master nightly. I was adding a seporate job to backup
only the trans log to try to do a point in time recover. Is the master
locked into simple?
"Jerry Spivey" wrote:
> Rich,
> Kevin brings up another key topic - be sure to backup the Master and MSDB
> database anytime changes occur that affect them (daily would be suggested).
> HTH
> Jerry
> "Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
> news:uQavLW1yFHA.1264@.tk2msftngp13.phx.gbl...
> > Probably because the master isn't written to nearly as frequently as user
> > databases or even msdb. Of course, I'm just guessing.
> >
> > I leave it at simple, as my master get backed up daily (nightly?)
> >
> > --
> > Kevin Hill
> > President
> > 3NF Consulting
> >
> > www.3nf-inc.com/NewsGroups.htm
> >
> >
> > "Rich" <Rich@.discussions.microsoft.com> wrote in message
> > news:6B7DDC52-F7D6-43EF-8F5D-AB60957DBF29@.microsoft.com...
> >> Hello Group,
> >>
> >> why does the default install of SQL Server 2000 place the master db in
> >> the
> >> simple recover model? should I switch to the full model and then I can
> >> use
> >> trans logs for point in time recover. What is the normal setting for the
> >> recovery of the master db?
> >
> >
>
>|||Rich,
You can change it to FULL but you still not be able to perform transaction
log backups so what's the point. Here is the error you will get when you
try a transaction log backup of the Master system database:
Server: Msg 4212, Level 16, State 1, Line 1
Cannot back up the log of the master database. Use BACKUP DATABASE instead.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
HTH
Jerry
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:F957C681-26E6-4FC6-B2CA-8338D1D70B13@.microsoft.com...
> Hello Jerry,
> I do a full backup of Master nightly. I was adding a seporate job to
> backup
> only the trans log to try to do a point in time recover. Is the master
> locked into simple?
> "Jerry Spivey" wrote:
>> Rich,
>> Kevin brings up another key topic - be sure to backup the Master and MSDB
>> database anytime changes occur that affect them (daily would be
>> suggested).
>> HTH
>> Jerry
>> "Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
>> news:uQavLW1yFHA.1264@.tk2msftngp13.phx.gbl...
>> > Probably because the master isn't written to nearly as frequently as
>> > user
>> > databases or even msdb. Of course, I'm just guessing.
>> >
>> > I leave it at simple, as my master get backed up daily (nightly?)
>> >
>> > --
>> > Kevin Hill
>> > President
>> > 3NF Consulting
>> >
>> > www.3nf-inc.com/NewsGroups.htm
>> >
>> >
>> > "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> > news:6B7DDC52-F7D6-43EF-8F5D-AB60957DBF29@.microsoft.com...
>> >> Hello Group,
>> >>
>> >> why does the default install of SQL Server 2000 place the master db in
>> >> the
>> >> simple recover model? should I switch to the full model and then I
>> >> can
>> >> use
>> >> trans logs for point in time recover. What is the normal setting for
>> >> the
>> >> recovery of the master db?
>> >
>> >
>>|||Hello Jerry,
you are correct, it appears to be locked by Microsoft. I just wanted to be
sure you could not do it. I will remove Master from the trans log backups.
Now what about the MSDB, is it "locked" in the same manner?
Rich
"Jerry Spivey" wrote:
> Rich,
> You can change it to FULL but you still not be able to perform transaction
> log backups so what's the point. Here is the error you will get when you
> try a transaction log backup of the Master system database:
> Server: Msg 4212, Level 16, State 1, Line 1
> Cannot back up the log of the master database. Use BACKUP DATABASE instead.
> Server: Msg 3013, Level 16, State 1, Line 1
> BACKUP LOG is terminating abnormally.
>
> HTH
> Jerry
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:F957C681-26E6-4FC6-B2CA-8338D1D70B13@.microsoft.com...
> > Hello Jerry,
> > I do a full backup of Master nightly. I was adding a seporate job to
> > backup
> > only the trans log to try to do a point in time recover. Is the master
> > locked into simple?
> >
> > "Jerry Spivey" wrote:
> >
> >> Rich,
> >>
> >> Kevin brings up another key topic - be sure to backup the Master and MSDB
> >> database anytime changes occur that affect them (daily would be
> >> suggested).
> >>
> >> HTH
> >>
> >> Jerry
> >> "Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
> >> news:uQavLW1yFHA.1264@.tk2msftngp13.phx.gbl...
> >> > Probably because the master isn't written to nearly as frequently as
> >> > user
> >> > databases or even msdb. Of course, I'm just guessing.
> >> >
> >> > I leave it at simple, as my master get backed up daily (nightly?)
> >> >
> >> > --
> >> > Kevin Hill
> >> > President
> >> > 3NF Consulting
> >> >
> >> > www.3nf-inc.com/NewsGroups.htm
> >> >
> >> >
> >> > "Rich" <Rich@.discussions.microsoft.com> wrote in message
> >> > news:6B7DDC52-F7D6-43EF-8F5D-AB60957DBF29@.microsoft.com...
> >> >> Hello Group,
> >> >>
> >> >> why does the default install of SQL Server 2000 place the master db in
> >> >> the
> >> >> simple recover model? should I switch to the full model and then I
> >> >> can
> >> >> use
> >> >> trans logs for point in time recover. What is the normal setting for
> >> >> the
> >> >> recovery of the master db?
> >> >
> >> >
> >>
> >>
> >>
>
>|||Rich,
No.
HTH
Jerry
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:1AD22F41-6494-41F5-8852-692714EB366C@.microsoft.com...
> Hello Jerry,
> you are correct, it appears to be locked by Microsoft. I just wanted to
> be
> sure you could not do it. I will remove Master from the trans log
> backups.
> Now what about the MSDB, is it "locked" in the same manner?
> Rich
> "Jerry Spivey" wrote:
>> Rich,
>> You can change it to FULL but you still not be able to perform
>> transaction
>> log backups so what's the point. Here is the error you will get when you
>> try a transaction log backup of the Master system database:
>> Server: Msg 4212, Level 16, State 1, Line 1
>> Cannot back up the log of the master database. Use BACKUP DATABASE
>> instead.
>> Server: Msg 3013, Level 16, State 1, Line 1
>> BACKUP LOG is terminating abnormally.
>>
>> HTH
>> Jerry
>> "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> news:F957C681-26E6-4FC6-B2CA-8338D1D70B13@.microsoft.com...
>> > Hello Jerry,
>> > I do a full backup of Master nightly. I was adding a seporate job to
>> > backup
>> > only the trans log to try to do a point in time recover. Is the master
>> > locked into simple?
>> >
>> > "Jerry Spivey" wrote:
>> >
>> >> Rich,
>> >>
>> >> Kevin brings up another key topic - be sure to backup the Master and
>> >> MSDB
>> >> database anytime changes occur that affect them (daily would be
>> >> suggested).
>> >>
>> >> HTH
>> >>
>> >> Jerry
>> >> "Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
>> >> news:uQavLW1yFHA.1264@.tk2msftngp13.phx.gbl...
>> >> > Probably because the master isn't written to nearly as frequently as
>> >> > user
>> >> > databases or even msdb. Of course, I'm just guessing.
>> >> >
>> >> > I leave it at simple, as my master get backed up daily (nightly?)
>> >> >
>> >> > --
>> >> > Kevin Hill
>> >> > President
>> >> > 3NF Consulting
>> >> >
>> >> > www.3nf-inc.com/NewsGroups.htm
>> >> >
>> >> >
>> >> > "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> >> > news:6B7DDC52-F7D6-43EF-8F5D-AB60957DBF29@.microsoft.com...
>> >> >> Hello Group,
>> >> >>
>> >> >> why does the default install of SQL Server 2000 place the master db
>> >> >> in
>> >> >> the
>> >> >> simple recover model? should I switch to the full model and then I
>> >> >> can
>> >> >> use
>> >> >> trans logs for point in time recover. What is the normal setting
>> >> >> for
>> >> >> the
>> >> >> recovery of the master db?
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>|||Hi,
No, For MSDB you can set to FULL recovery model and do the transaction log
backup.
But my recommendation is:-
1. Master database :- Do a full database backup every day night
2. MSDB:- Do full database backup every 4 hours (Since it stores backup and
restore informations)
3. user databases:- Set FULL recovery model and do Transaction log backup
every 30 minutes
Thanks
Hari
SQL Server MVP
Thanks
Hari
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:1AD22F41-6494-41F5-8852-692714EB366C@.microsoft.com...
> Hello Jerry,
> you are correct, it appears to be locked by Microsoft. I just wanted to
> be
> sure you could not do it. I will remove Master from the trans log
> backups.
> Now what about the MSDB, is it "locked" in the same manner?
> Rich
> "Jerry Spivey" wrote:
>> Rich,
>> You can change it to FULL but you still not be able to perform
>> transaction
>> log backups so what's the point. Here is the error you will get when you
>> try a transaction log backup of the Master system database:
>> Server: Msg 4212, Level 16, State 1, Line 1
>> Cannot back up the log of the master database. Use BACKUP DATABASE
>> instead.
>> Server: Msg 3013, Level 16, State 1, Line 1
>> BACKUP LOG is terminating abnormally.
>>
>> HTH
>> Jerry
>> "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> news:F957C681-26E6-4FC6-B2CA-8338D1D70B13@.microsoft.com...
>> > Hello Jerry,
>> > I do a full backup of Master nightly. I was adding a seporate job to
>> > backup
>> > only the trans log to try to do a point in time recover. Is the master
>> > locked into simple?
>> >
>> > "Jerry Spivey" wrote:
>> >
>> >> Rich,
>> >>
>> >> Kevin brings up another key topic - be sure to backup the Master and
>> >> MSDB
>> >> database anytime changes occur that affect them (daily would be
>> >> suggested).
>> >>
>> >> HTH
>> >>
>> >> Jerry
>> >> "Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
>> >> news:uQavLW1yFHA.1264@.tk2msftngp13.phx.gbl...
>> >> > Probably because the master isn't written to nearly as frequently as
>> >> > user
>> >> > databases or even msdb. Of course, I'm just guessing.
>> >> >
>> >> > I leave it at simple, as my master get backed up daily (nightly?)
>> >> >
>> >> > --
>> >> > Kevin Hill
>> >> > President
>> >> > 3NF Consulting
>> >> >
>> >> > www.3nf-inc.com/NewsGroups.htm
>> >> >
>> >> >
>> >> > "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> >> > news:6B7DDC52-F7D6-43EF-8F5D-AB60957DBF29@.microsoft.com...
>> >> >> Hello Group,
>> >> >>
>> >> >> why does the default install of SQL Server 2000 place the master db
>> >> >> in
>> >> >> the
>> >> >> simple recover model? should I switch to the full model and then I
>> >> >> can
>> >> >> use
>> >> >> trans logs for point in time recover. What is the normal setting
>> >> >> for
>> >> >> the
>> >> >> recovery of the master db?
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>|||> You can change it to FULL but you still not be able to perform transaction log backups so what's
> the point.
Which is a hilarious behavior by SQL Server, IMO. We should get error message of we try to set
master to FULL. I mentioned to MS, and reply is that it would break compatibility too much. I wonder
how many of us has scripts and applications that set master to full recovery?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23KeRah1yFHA.3408@.TK2MSFTNGP09.phx.gbl...
> Rich,
> You can change it to FULL but you still not be able to perform transaction log backups so what's
> the point. Here is the error you will get when you try a transaction log backup of the Master
> system database:
> Server: Msg 4212, Level 16, State 1, Line 1
> Cannot back up the log of the master database. Use BACKUP DATABASE instead.
> Server: Msg 3013, Level 16, State 1, Line 1
> BACKUP LOG is terminating abnormally.
>
> HTH
> Jerry
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:F957C681-26E6-4FC6-B2CA-8338D1D70B13@.microsoft.com...
>> Hello Jerry,
>> I do a full backup of Master nightly. I was adding a seporate job to backup
>> only the trans log to try to do a point in time recover. Is the master
>> locked into simple?
>> "Jerry Spivey" wrote:
>> Rich,
>> Kevin brings up another key topic - be sure to backup the Master and MSDB
>> database anytime changes occur that affect them (daily would be suggested).
>> HTH
>> Jerry
>> "Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
>> news:uQavLW1yFHA.1264@.tk2msftngp13.phx.gbl...
>> > Probably because the master isn't written to nearly as frequently as user
>> > databases or even msdb. Of course, I'm just guessing.
>> >
>> > I leave it at simple, as my master get backed up daily (nightly?)
>> >
>> > --
>> > Kevin Hill
>> > President
>> > 3NF Consulting
>> >
>> > www.3nf-inc.com/NewsGroups.htm
>> >
>> >
>> > "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> > news:6B7DDC52-F7D6-43EF-8F5D-AB60957DBF29@.microsoft.com...
>> >> Hello Group,
>> >>
>> >> why does the default install of SQL Server 2000 place the master db in
>> >> the
>> >> simple recover model? should I switch to the full model and then I can
>> >> use
>> >> trans logs for point in time recover. What is the normal setting for the
>> >> recovery of the master db?
>> >
>> >
>>
>|||Every time Agent is started, the recovery for msdb is set to simple. I prefer to do log backups for
msdb. So I have an autostart Agent job that lifts recovery model for msdb to full. Agent will not
set msdb to simple on startup in 2005 (I have been promised).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:1AD22F41-6494-41F5-8852-692714EB366C@.microsoft.com...
> Hello Jerry,
> you are correct, it appears to be locked by Microsoft. I just wanted to be
> sure you could not do it. I will remove Master from the trans log backups.
> Now what about the MSDB, is it "locked" in the same manner?
> Rich
> "Jerry Spivey" wrote:
>> Rich,
>> You can change it to FULL but you still not be able to perform transaction
>> log backups so what's the point. Here is the error you will get when you
>> try a transaction log backup of the Master system database:
>> Server: Msg 4212, Level 16, State 1, Line 1
>> Cannot back up the log of the master database. Use BACKUP DATABASE instead.
>> Server: Msg 3013, Level 16, State 1, Line 1
>> BACKUP LOG is terminating abnormally.
>>
>> HTH
>> Jerry
>> "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> news:F957C681-26E6-4FC6-B2CA-8338D1D70B13@.microsoft.com...
>> > Hello Jerry,
>> > I do a full backup of Master nightly. I was adding a seporate job to
>> > backup
>> > only the trans log to try to do a point in time recover. Is the master
>> > locked into simple?
>> >
>> > "Jerry Spivey" wrote:
>> >
>> >> Rich,
>> >>
>> >> Kevin brings up another key topic - be sure to backup the Master and MSDB
>> >> database anytime changes occur that affect them (daily would be
>> >> suggested).
>> >>
>> >> HTH
>> >>
>> >> Jerry
>> >> "Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
>> >> news:uQavLW1yFHA.1264@.tk2msftngp13.phx.gbl...
>> >> > Probably because the master isn't written to nearly as frequently as
>> >> > user
>> >> > databases or even msdb. Of course, I'm just guessing.
>> >> >
>> >> > I leave it at simple, as my master get backed up daily (nightly?)
>> >> >
>> >> > --
>> >> > Kevin Hill
>> >> > President
>> >> > 3NF Consulting
>> >> >
>> >> > www.3nf-inc.com/NewsGroups.htm
>> >> >
>> >> >
>> >> > "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> >> > news:6B7DDC52-F7D6-43EF-8F5D-AB60957DBF29@.microsoft.com...
>> >> >> Hello Group,
>> >> >>
>> >> >> why does the default install of SQL Server 2000 place the master db in
>> >> >> the
>> >> >> simple recover model? should I switch to the full model and then I
>> >> >> can
>> >> >> use
>> >> >> trans logs for point in time recover. What is the normal setting for
>> >> >> the
>> >> >> recovery of the master db?
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>

Default Recovery Model

Hello Group,
why does the default install of SQL Server 2000 place the master db in the
simple recover model? should I switch to the full model and then I can use
trans logs for point in time recover. What is the normal setting for the
recovery of the master db?Rich,
User-defined objects should not be created in the Master system database.
The recovery model is SIMPLE and cannot be changed.
HTH
Jerry
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:6B7DDC52-F7D6-43EF-8F5D-AB60957DBF29@.microsoft.com...
> Hello Group,
> why does the default install of SQL Server 2000 place the master db in the
> simple recover model? should I switch to the full model and then I can
> use
> trans logs for point in time recover. What is the normal setting for the
> recovery of the master db?|||Probably because the master isn't written to nearly as frequently as user
databases or even msdb. Of course, I'm just guessing.
I leave it at simple, as my master get backed up daily (nightly?)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:6B7DDC52-F7D6-43EF-8F5D-AB60957DBF29@.microsoft.com...
> Hello Group,
> why does the default install of SQL Server 2000 place the master db in the
> simple recover model? should I switch to the full model and then I can
> use
> trans logs for point in time recover. What is the normal setting for the
> recovery of the master db?|||Or rather...should not be changed.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eqnZDW1yFHA.460@.TK2MSFTNGP15.phx.gbl...
> Rich,
> User-defined objects should not be created in the Master system database.
> The recovery model is SIMPLE and cannot be changed.
> HTH
> Jerry
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:6B7DDC52-F7D6-43EF-8F5D-AB60957DBF29@.microsoft.com...
>|||Rich,
Kevin brings up another key topic - be sure to backup the Master and MSDB
database anytime changes occur that affect them (daily would be suggested).
HTH
Jerry
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:uQavLW1yFHA.1264@.tk2msftngp13.phx.gbl...
> Probably because the master isn't written to nearly as frequently as user
> databases or even msdb. Of course, I'm just guessing.
> I leave it at simple, as my master get backed up daily (nightly?)
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:6B7DDC52-F7D6-43EF-8F5D-AB60957DBF29@.microsoft.com...
>|||Hello Jerry,
I do a full backup of Master nightly. I was adding a seporate job to backup
only the trans log to try to do a point in time recover. Is the master
locked into simple?
"Jerry Spivey" wrote:

> Rich,
> Kevin brings up another key topic - be sure to backup the Master and MSDB
> database anytime changes occur that affect them (daily would be suggested)
.
> HTH
> Jerry
> "Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
> news:uQavLW1yFHA.1264@.tk2msftngp13.phx.gbl...
>
>|||Rich,
You can change it to FULL but you still not be able to perform transaction
log backups so what's the point. Here is the error you will get when you
try a transaction log backup of the Master system database:
Server: Msg 4212, Level 16, State 1, Line 1
Cannot back up the log of the master database. Use BACKUP DATABASE instead.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
HTH
Jerry
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:F957C681-26E6-4FC6-B2CA-8338D1D70B13@.microsoft.com...[vbcol=seagreen]
> Hello Jerry,
> I do a full backup of Master nightly. I was adding a seporate job to
> backup
> only the trans log to try to do a point in time recover. Is the master
> locked into simple?
> "Jerry Spivey" wrote:
>|||Hello Jerry,
you are correct, it appears to be locked by Microsoft. I just wanted to be
sure you could not do it. I will remove Master from the trans log backups.
Now what about the MSDB, is it "locked" in the same manner?
Rich
"Jerry Spivey" wrote:

> Rich,
> You can change it to FULL but you still not be able to perform transaction
> log backups so what's the point. Here is the error you will get when you
> try a transaction log backup of the Master system database:
> Server: Msg 4212, Level 16, State 1, Line 1
> Cannot back up the log of the master database. Use BACKUP DATABASE instead
.
> Server: Msg 3013, Level 16, State 1, Line 1
> BACKUP LOG is terminating abnormally.
>
> HTH
> Jerry
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:F957C681-26E6-4FC6-B2CA-8338D1D70B13@.microsoft.com...
>
>|||Rich,
No.
HTH
Jerry
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:1AD22F41-6494-41F5-8852-692714EB366C@.microsoft.com...[vbcol=seagreen]
> Hello Jerry,
> you are correct, it appears to be locked by Microsoft. I just wanted to
> be
> sure you could not do it. I will remove Master from the trans log
> backups.
> Now what about the MSDB, is it "locked" in the same manner?
> Rich
> "Jerry Spivey" wrote:
>|||Hi,
No, For MSDB you can set to FULL recovery model and do the transaction log
backup.
But my recommendation is:-
1. Master database :- Do a full database backup every day night
2. MSDB:- Do full database backup every 4 hours (Since it stores backup and
restore informations)
3. user databases:- Set FULL recovery model and do Transaction log backup
every 30 minutes
Thanks
Hari
SQL Server MVP
Thanks
Hari
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:1AD22F41-6494-41F5-8852-692714EB366C@.microsoft.com...[vbcol=seagreen]
> Hello Jerry,
> you are correct, it appears to be locked by Microsoft. I just wanted to
> be
> sure you could not do it. I will remove Master from the trans log
> backups.
> Now what about the MSDB, is it "locked" in the same manner?
> Rich
> "Jerry Spivey" wrote:
>