MAX dont know what type is returning

Hi guys,

I'm doing some function in the SQLExpress using the max func and I cant figure out if its returning null or empty string. It always return null even it's returning index.

Please help.

Here's is the sample data of the table that I'm manipuilating

code name

code name

code_1 name_1

code_2 name_2

tues_2 name_4

code_4 name_3

>>> What am trying to get is the maximum index of the code

Below is my function

declare @.codeIndex varchar(20)

select @.codeIndex = isnull(max(isnull(substring([code],len('code')+2), len'code')) ,0)),0) from table where [code] like 'code%'

print @.codeIndex

if @.codeIndex is null

begin

print 'Code index is null

end

else

begin

print 'we got an index!'

end

>>> the output of this is always null even it's returning an index

Can you post a full repro? You code didn't compile (not correct number of parameters etc). Either against the pubs database or also post CREATE TABLE and some INSERT statements. I changed the code so it at least compiled (but I doubt it has correct logic), and there doesn't seem to anything wrong with the MAX function per se.: declare @.codeIndex varchar(20) select @.codeIndex = isnull(max(isnull(substring([au_lname],len('code')+2, len('code')) ,0)),0) from authors print @.codeIndex if @.codeIndex is null begin print 'Code index is null' end else begin print 'we got an index!' end -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ wrote in message news:b4844e9a-27a2-49d0-8a11-482daa3423db@.discussions.microsoft.com...
> Hi guys, >
> I'm doing some function in the SQLExpress using the max func and I
> cant figure out if its returning null or empty string. It always return
> null even it's returning index. >
> Please help. >
> Here's is the sample data of the table that I'm manipuilating >
> code name >
> code name >
> code_1 name_1 >
> code_2 name_2 >
> tues_2 name_4 >
> code_4 name_3 > > > >>>> What am trying to get is the maximum index of the code >
> Below is my function >
> declare @.codeIndex varchar(20) >
> select @.codeIndex = isnull(max(isnull(substring([code],len('code')+2),
> len'code')) ,0)),0) from table where [code] like 'code%' > > >
> print @.codeIndex >
> if @.codeIndex is null >
> begin >
> print 'Code index is null >
> end >
> else >
> begin >
> print 'we got an index!' >
> end > >>>> the output of this is always null even it's returning an index >
>|||

Hi NNTP,

Thanks! I already figured it out; maybe, I'm just exhausted last friday.

Max Data Type in SQL2000

Anything I can use in SQL2000 in place of varchar(max) (only in SQL2005) with a 2 GB-per-instance capacity

DECLARE jkcursor CURSOR

READ_ONLY

FOR SELECT top 100 episodeid,episodedate,journalentry

FROM ccnidcdw.[Pre-AuthThin].dbo.tbljournal

WHERE journalentry like '%CoCustServ%'

DECLARE @.episodeid varchar(15), @.episodedate datetime, @.journal varchar(2gb)

OPEN jkcursor

FETCH NEXT FROM jkcursor INTO @.episodeid,@.episodedate,@.journal

WHILE (@.@.fetch_status = 0)

BEGIN

print @.journal

FETCH NEXT FROM jkcursor INTO @.episodeid,@.episodedate,@.journal

END

CLOSE jkcursor

DEALLOCATE jkcursor

thanks

Text/NText and Image comes with restrictions. Check the link below.

http://msdn2.microsoft.com/en-us/library/aa276838(SQL.80).aspx

|||

anything I can do with varchar ?

|||

Nope. Varchar(max) & NVarchar(max) are introduced very first in SQL Server 2005. You have to use Text/NText. But with lot of restrictions.

You don't have other options in SQL Server 2000.

|||

The limit for Varchar is 8000 while Nvarchar is 4000 and there is no Varchar/Nvarchar(max) in 2000 so you have to use Text/NText but you cannot do comparison and other operation with Text/NText. The restrictions are covered in the link I posted.

|||thanks for you help

Matrix Totals Problem - SSRS 2005

I wasn't able to get my totals working accurately in a table footer using SUM, so I though hmm, maybe try matrix...well, still same type of problems...a bit different but still, my totals are not totalling the columns!

Screen Shot:

http://www.photopizzaz.biz/matrix_totals_problem.jpg

I don't know what else to do, I give up at this point and need help from someone here who has already gone through these headaches!

I don't understand why BOL doesn't address more complicated issues such as this...they barely tell you anything there.

I believe you need to preface your column expressions with sum: ie: the expression under customer number would be: sum(fields!customernumber.Value)sql

Matrix report with Commant type Text - cannot set OR condition

I have a matrix report that uses a Dataset of Command type - Text. I am
trying to set an OR condition but I am not able to - it defaults to AND. The
field in the filters is disabled in both the DataSet > Edit SElected Dataset
and Layout > Table > Properties.
Is there a way to specify the OR condition?
ThanksOn Feb 5, 3:55 pm, "shikarishambu" <shikarishamb...@.hotmail.com>
wrote:
> I have a matrix report that uses a Dataset of Command type - Text. I am
> trying to set an OR condition but I am not able to - it defaults to AND. The
> field in the filters is disabled in both the DataSet > Edit SElected Dataset
> and Layout > Table > Properties.
> Is there a way to specify the OR condition?
> Thanks
I'm not sure if I quite follow you. In terms of the Layout >> Table >>
Properties, the only option available for Fields is 'And.' For Dataset
>> Edit Selected Dataset, for the Filter tab, the same is also true.
In this case, the Command Type of text or Stored Procedure doesn't
really matter. It seems like you will want to create a separate
dataset for each filtering option in order to get the 'Or'
functionality. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Sorry, if i was not being clear. I have a dataset which has two date
fields - intial date and execution date. I have a parameter - for year
I want to show all rows that match the following condition
initial date_year = parm_year OR execution date_year = parm_year.
Is this not possible using the filters tab?
Thanks
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:def7007a-2bd5-4718-b21d-5540dbe4e6b0@.s8g2000prg.googlegroups.com...
> On Feb 5, 3:55 pm, "shikarishambu" <shikarishamb...@.hotmail.com>
> wrote:
>> I have a matrix report that uses a Dataset of Command type - Text. I am
>> trying to set an OR condition but I am not able to - it defaults to AND.
>> The
>> field in the filters is disabled in both the DataSet > Edit SElected
>> Dataset
>> and Layout > Table > Properties.
>> Is there a way to specify the OR condition?
>> Thanks
>
> I'm not sure if I quite follow you. In terms of the Layout >> Table >>
> Properties, the only option available for Fields is 'And.' For Dataset
>> Edit Selected Dataset, for the Filter tab, the same is also true.
> In this case, the Command Type of text or Stored Procedure doesn't
> really matter. It seems like you will want to create a separate
> dataset for each filtering option in order to get the 'Or'
> functionality. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant|||On Feb 6, 11:32 am, "shikarishambu" <shikarishamb...@.hotmail.com>
wrote:
> Sorry, if i was not being clear. I have a dataset which has two date
> fields - intial date and execution date. I have a parameter - for year
> I want to show all rows that match the following condition
> initial date_year = parm_year OR execution date_year = parm_year.
> Is this not possible using the filters tab?
> Thanks
> "EMartinez" <emartinez...@.gmail.com> wrote in message
> news:def7007a-2bd5-4718-b21d-5540dbe4e6b0@.s8g2000prg.googlegroups.com...
> > On Feb 5, 3:55 pm, "shikarishambu" <shikarishamb...@.hotmail.com>
> > wrote:
> >> I have a matrix report that uses a Dataset of Command type - Text. I am
> >> trying to set an OR condition but I am not able to - it defaults to AND.
> >> The
> >> field in the filters is disabled in both the DataSet > Edit SElected
> >> Dataset
> >> and Layout > Table > Properties.
> >> Is there a way to specify the OR condition?
> >> Thanks
> > I'm not sure if I quite follow you. In terms of the Layout >> Table >>
> > Properties, the only option available for Fields is 'And.' For Dataset
> >> Edit Selected Dataset, for the Filter tab, the same is also true.
> > In this case, the Command Type of text or Stored Procedure doesn't
> > really matter. It seems like you will want to create a separate
> > dataset for each filtering option in order to get the 'Or'
> > functionality. Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
Thanks for the clarification. The most effective way to do this is via
a stored procedure (Command type: StoredProcedure)/query (Command
type: text) that is sourcing the report. That way you can set the
dataset query to something like:
select xxxxx, ... from tableX where initial_date_year = @.parm_year OR
execution_date_year = @.parm_year
And then select the 'Edit Selected Dataset' [...] button on the 'Data'
tab >> 'Parameters' tab >> and for the 'Name' you should have
'@.parm_year' and then for the 'Value' select '=Parameters!
parm_year.Value' (assuming that your report parameter is named
'parm_year'). Hope this helps further.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thank you very much for the detailed explanation. Unfortunately, I am not in
a position to create sp as I not accessing the DB directly. My SSRS instance
interfaces with Microsoft Dynamics AX - which uses perspectives (models) to
expose the DB entities to SSRS.
Thanks
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:9deb4ce6-b98e-452e-b0dd-4ca5103b567f@.e10g2000prf.googlegroups.com...
> On Feb 6, 11:32 am, "shikarishambu" <shikarishamb...@.hotmail.com>
> wrote:
>> Sorry, if i was not being clear. I have a dataset which has two date
>> fields - intial date and execution date. I have a parameter - for year
>> I want to show all rows that match the following condition
>> initial date_year = parm_year OR execution date_year = parm_year.
>> Is this not possible using the filters tab?
>> Thanks
>> "EMartinez" <emartinez...@.gmail.com> wrote in message
>> news:def7007a-2bd5-4718-b21d-5540dbe4e6b0@.s8g2000prg.googlegroups.com...
>> > On Feb 5, 3:55 pm, "shikarishambu" <shikarishamb...@.hotmail.com>
>> > wrote:
>> >> I have a matrix report that uses a Dataset of Command type - Text. I
>> >> am
>> >> trying to set an OR condition but I am not able to - it defaults to
>> >> AND.
>> >> The
>> >> field in the filters is disabled in both the DataSet > Edit SElected
>> >> Dataset
>> >> and Layout > Table > Properties.
>> >> Is there a way to specify the OR condition?
>> >> Thanks
>> > I'm not sure if I quite follow you. In terms of the Layout >> Table >>
>> > Properties, the only option available for Fields is 'And.' For Dataset
>> >> Edit Selected Dataset, for the Filter tab, the same is also true.
>> > In this case, the Command Type of text or Stored Procedure doesn't
>> > really matter. It seems like you will want to create a separate
>> > dataset for each filtering option in order to get the 'Or'
>> > functionality. Hope this helps.
>> > Regards,
>> > Enrique Martinez
>> > Sr. Software Consultant
>
> Thanks for the clarification. The most effective way to do this is via
> a stored procedure (Command type: StoredProcedure)/query (Command
> type: text) that is sourcing the report. That way you can set the
> dataset query to something like:
> select xxxxx, ... from tableX where initial_date_year = @.parm_year OR
> execution_date_year = @.parm_year
> And then select the 'Edit Selected Dataset' [...] button on the 'Data'
> tab >> 'Parameters' tab >> and for the 'Name' you should have
> '@.parm_year' and then for the 'Value' select '=Parameters!
> parm_year.Value' (assuming that your report parameter is named
> 'parm_year'). Hope this helps further.
> Regards,
> Enrique Martinez
> Sr. Software Consultant|||On Feb 6, 12:32=A0pm, "shikarishambu" <shikarishamb...@.hotmail.com>
wrote:
> Sorry, if i was not being clear. I have a dataset which has two date
> fields - intial date and execution date. I have a parameter - for year
> I want to show all rows that match the following condition
> initial date_year =3D parm_year OR execution date_year =3D parm_year.
> Is this not possible using the filters tab?
> Thanks
> "EMartinez" <emartinez...@.gmail.com> wrote in message
> news:def7007a-2bd5-4718-b21d-5540dbe4e6b0@.s8g2000prg.googlegroups.com...
>
> > On Feb 5, 3:55 pm, "shikarishambu" <shikarishamb...@.hotmail.com>
> > wrote:
> >> I have a matrix report that uses a Dataset of Command type - Text. I am=
> >> trying to set an OR condition but I am not able to - it defaults to AND=.
> >> The
> >> field in the filters is disabled in both the DataSet > Edit SElected
> >> Dataset
> >> and Layout > Table > Properties.
> >> Is there a way to specify the OR condition?
> >> Thanks
> > I'm not sure if I quite follow you. In terms of the Layout >> Table >>
> > Properties, the only option available for Fields is 'And.' For Dataset
> >> Edit Selected Dataset, for the Filter tab, the same is also true.
> > In this case, the Command Type of text or Stored Procedure doesn't
> > really matter. It seems like you will want to create a separate
> > dataset for each filtering option in order to get the 'Or'
> > functionality. Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant- Hide quoted text -
> - Show quoted text -
In the "Filters" menu, you have Expression , Operator, and Value
I would suggest that you have an Expression:
=3D CBool( (Fields!InitialDateYear =3D Parameters!Year.Value )
Or ( Fields!ExecutionDateYear.Value =3D Parameters!Year.Value ) )
Operator: =3D
Value:
=3D True
This way all of the conditional OR is handled in the Expression that
returns True of False, then you filter on only the rows that are True
-- Scott|||Will try that out. Smart work around :)
"Orne" <polysillycon@.yahoo.com> wrote in message
news:27fce259-12e2-4bc0-a129-ade4a24c7148@.h11g2000prf.googlegroups.com...
On Feb 6, 12:32 pm, "shikarishambu" <shikarishamb...@.hotmail.com>
wrote:
> Sorry, if i was not being clear. I have a dataset which has two date
> fields - intial date and execution date. I have a parameter - for year
> I want to show all rows that match the following condition
> initial date_year = parm_year OR execution date_year = parm_year.
> Is this not possible using the filters tab?
> Thanks
> "EMartinez" <emartinez...@.gmail.com> wrote in message
> news:def7007a-2bd5-4718-b21d-5540dbe4e6b0@.s8g2000prg.googlegroups.com...
>
> > On Feb 5, 3:55 pm, "shikarishambu" <shikarishamb...@.hotmail.com>
> > wrote:
> >> I have a matrix report that uses a Dataset of Command type - Text. I am
> >> trying to set an OR condition but I am not able to - it defaults to
> >> AND.
> >> The
> >> field in the filters is disabled in both the DataSet > Edit SElected
> >> Dataset
> >> and Layout > Table > Properties.
> >> Is there a way to specify the OR condition?
> >> Thanks
> > I'm not sure if I quite follow you. In terms of the Layout >> Table >>
> > Properties, the only option available for Fields is 'And.' For Dataset
> >> Edit Selected Dataset, for the Filter tab, the same is also true.
> > In this case, the Command Type of text or Stored Procedure doesn't
> > really matter. It seems like you will want to create a separate
> > dataset for each filtering option in order to get the 'Or'
> > functionality. Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant- Hide quoted text -
> - Show quoted text -
In the "Filters" menu, you have Expression , Operator, and Value
I would suggest that you have an Expression:
= CBool( (Fields!InitialDateYear = Parameters!Year.Value )
Or ( Fields!ExecutionDateYear.Value = Parameters!Year.Value ) )
Operator: =
Value:
= True
This way all of the conditional OR is handled in the Expression that
returns True of False, then you filter on only the rows that are True
-- Scottsql

Matrix Question

I have a view that gives me the following information from some tables.

Cust # Cust Name Date Order Type

001 John Doe 20070401 OR1

002 Miss Doe 20070401 OR2

001 John Doe 20070402 OR2

002 Miss Doe 20070402 OR2

What I would like to do is set up a matrix type report. The report is by the last 6 rolling order dates, with some % columns. So two row examples might be

Cust# Cust Name 20070401 20070402 % (of last x = OR1) %(of last x = OR2)

001 John Doe OR1 OR2 50% 50%

002 Miss Doe OR2 OR2 0% 100%

Another column just like the last two % based off a 3rd type, and finally a total % column that simply adds the 3 columns up (should always equal 100%, just an error check)

First, can this be done with a matrix? I tried a table but it lists cust# twice, but I can be doing it wrong. I am ok doing this within the query if need be, if someone gives me a hint how

Thanks,

When you tried the table, did you set a row group on the customer number?|||Yes, and it will not give me a column for each date in table form.

Matrix or Table

Good afternoon!
I have the following problem:
There is a table with following structure: PersonID, PersonName,
Field1. Filed1 is enum type with values (A1, A2, A3, B1,B2,B3, ...,
Z1,Z2,Z3).
I need the following table:
PersonID|PersonName|A1| A2|A3|(A1 + A2 + A3)|(A1+A2+A3)/Total | ... |
Z1|Z2|Z3| Z1 + Z2 + Z3|(Z1+Z2+Z3)/Total|Total
Is Table is the only solution (and I really need to fill each cell
with details) or it cone be done by means of Matrix
Thank you,you can use either, but Matrix is dynamic - table is static
If there will always be a fixed number of columns, then you can use table,
but in your example, i'd go the matrix approach.
<mnemets@.gmail.com> wrote in message
news:1170333996.383084.308920@.j27g2000cwj.googlegroups.com...
> Good afternoon!
> I have the following problem:
> There is a table with following structure: PersonID, PersonName,
> Field1. Filed1 is enum type with values (A1, A2, A3, B1,B2,B3, ...,
> Z1,Z2,Z3).
> I need the following table:
> PersonID|PersonName|A1| A2|A3|(A1 + A2 + A3)|(A1+A2+A3)/Total | ... |
> Z1|Z2|Z3| Z1 + Z2 + Z3|(Z1+Z2+Z3)/Total|Total
> Is Table is the only solution (and I really need to fill each cell
> with details) or it cone be done by means of Matrix
> Thank you,
>|||On 1 æÅ×., 18:46, "Immy" <therealasianb...@.hotmail.com> wrote:
> you can use either, butMatrixis dynamic -tableis static
> If there will always be a fixed number of columns, then you can use table,
> but in your example, i'd go thematrixapproach.
Thank you for reply.
Bur really I can't do it by matrix. Which column groups should I use,
and how can I do it ?
Thank you,|||To be clear "I can't do it by matrix" means "I don't know how to do
it by matrix"|||It means 26 * 3 + 2 = 80 columns in your table is it ok that you can have
this much, you cannot read and you need to scroll or you need all alphabets
in one row each.
Amarnath
"mnemets@.gmail.com" wrote:
> Good afternoon!
> I have the following problem:
> There is a table with following structure: PersonID, PersonName,
> Field1. Filed1 is enum type with values (A1, A2, A3, B1,B2,B3, ...,
> Z1,Z2,Z3).
> I need the following table:
> PersonID|PersonName|A1| A2|A3|(A1 + A2 + A3)|(A1+A2+A3)/Total | ... |
> Z1|Z2|Z3| Z1 + Z2 + Z3|(Z1+Z2+Z3)/Total|Total
> Is Table is the only solution (and I really need to fill each cell
> with details) or it cone be done by means of Matrix
> Thank you,
>|||Actually I am talking about 3 groups (A,B,C). In a future the nuber of
these group can be changed (not more than 5!). So it means 3*4 (three
values in a group and subtotal for the group) + 1 = 13. Maximum is 5*4
+ 1 = 21. Not so bad.
But the real problem is that I have no ideas how such matrix can be
done.
Thank you

Matrix Last Column Sort Descending

I would like to sort the following matrix data:
July-07 Aug-07 Sep-07
Type A 5 1 4
Type B 6 3 3
Type C 4 2 6
Other 5 5 5
by the maximum date column (i.e. Sep), please note this month will
change each month
July-07 Aug-07 Sep-07
Type C 4 2 6
Type A 5 1 4
Type B 6 3 3
Other 5 5 5
I have tried the following in: Properties / Groups / Rows / Sorting
EXPRESSION
=IIF(Fields!Date.Value = Max(Fields!Date.Value), IIF(Fields!Type.Value
= "Other", -100, Fields!Amount.Value), -1000)
DIRECTION
Descending
With no success.
Any help would be greatly appreciated
Thanks
SarahOn Sep 19, 6:26 pm, sez...@.gmail.com wrote:
> I would like to sort the following matrix data:
> July-07 Aug-07 Sep-07
> Type A 5 1 4
> Type B 6 3 3
> Type C 4 2 6
> Other 5 5 5
> by the maximum date column (i.e. Sep), please note this month will
> change each month
> July-07 Aug-07 Sep-07
> Type C 4 2 6
> Type A 5 1 4
> Type B 6 3 3
> Other 5 5 5
> I have tried the following in: Properties / Groups / Rows / Sorting
> EXPRESSION
> =IIF(Fields!Date.Value = Max(Fields!Date.Value), IIF(Fields!Type.Value
> = "Other", -100, Fields!Amount.Value), -1000)
> DIRECTION
> Descending
> With no success.
> Any help would be greatly appreciated
> Thanks
> Sarah
If I understand you correctly, you might be able to get away with:
=iif(Fields!Date.Value = Max(Fields!Date.Value), Fields!
ValueField.Value, 0): Direction: Desc
If this does not work, you may need to sort based on an additional
field added to the returned dataset that has its value determined in
the stored procedure/query that is sourcing the report; possibly, via
cursor or while loop.
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Hi Enrique,
Thanks for you suggestions. I have now added a column into the SP
called Rank, this ranks the last months data by amount, I then try to
sort the ROW by Rank and still no success.
Thanks
Sarah|||On Sep 19, 11:45 pm, sez...@.gmail.com wrote:
> Hi Enrique,
> Thanks for you suggestions. I have now added a column into the SP
> called Rank, this ranks the last months data by amount, I then try to
> sort the ROW by Rank and still no success.
> Thanks
> Sarah
You're welcome. Are you sorting the main group in the matrix control
by the Rank column? If not, this might help.
Regards,
Enrique Martinez
Sr. Software Consultant|||Hi,
The Rank column is calucated in T-SQL, it is based on the amount
value, which is the coulmn of data in the matrix table. To add to my
worries, my chart is no longer working (which I think is related to
Rank), at least it's Friday :-)
Sarah|||All working this morning!
ORDER BY
Date DESC
Rank
in SP worked perfectly :-)
thanks for your help|||On Sep 23, 4:36 pm, sez...@.gmail.com wrote:
> All working this morning!
> ORDER BY
> Date DESC
> Rank
> in SP worked perfectly :-)
> thanks for your help
You're welcome. Let me know if I can be of further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Matrix Background Color Change

Hi All.
Is there anyway to change matrix type reoports rows to change background
colors rows alternate row to red or else white in color. Please help me. It
works fine in on table type.
Thanks!
ReddyThis is Chris Webbs' description on how to do it
http://blogs.msdn.com/chrishays/archive/2004/08/30/GreenBarMatrix.aspx
Kaisa M. Lindahl Lervik
"Reddy" <Reddy@.discussions.microsoft.com> wrote in message
news:9805F625-7622-4C6D-9D6D-30CA7E99E601@.microsoft.com...
> Hi All.
> Is there anyway to change matrix type reoports rows to change background
> colors rows alternate row to red or else white in color. Please help me.
> It
> works fine in on table type.
> Thanks!
> Reddy

Master, Model, MSDB What Recovery Models

What Recovery Model and what Backup Type should be used on the Master, Model
and MSDB databases?Master and MSDB no choice as they get reset to simple anyway. Regular
(daily) full backups tends to be the most common.
Model - matters little but it does set the default recovey model for new
databses being created.
Mike John
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:70327943-6C4C-46B0-98AD-C875FCD7F9D1@.microsoft.com...
> What Recovery Model and what Backup Type should be used on the Master,
> Model
> and MSDB databases?|||Hmmmm
1. Are you saying that that the Master and MSDB will always stay in simple
no matter what I pick?
2. If I do a full back up of the Master and MSDB every night and the
recovery mode is locked to simple, the Transaction Log that comes with the
full is not really used, it just seems to restore to the last "static" state.
"Mike John" wrote:
> Master and MSDB no choice as they get reset to simple anyway. Regular
> (daily) full backups tends to be the most common.
> Model - matters little but it does set the default recovey model for new
> databses being created.
> Mike John
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:70327943-6C4C-46B0-98AD-C875FCD7F9D1@.microsoft.com...
> > What Recovery Model and what Backup Type should be used on the Master,
> > Model
> > and MSDB databases?
>
>|||You can have msdb in full recovery mode. I usually to log backups for msdb. The problem is that
Agent will set msdb to simple on startup. But you can create a job that reverses this and set the
job as a startup job.
You can set master to full, but that is a "null" operation as behavior is still same as simple and
you still cannot do log backups. I'd prefer an error message if I (try) to set master to full.
The transaction log is definitely used in simple recovery mode, for things as rollbacks and recovery
on startup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:62355AFE-F239-49BB-8F33-C50CDCD900C8@.microsoft.com...
> Hmmmm
> 1. Are you saying that that the Master and MSDB will always stay in simple
> no matter what I pick?
> 2. If I do a full back up of the Master and MSDB every night and the
> recovery mode is locked to simple, the Transaction Log that comes with the
> full is not really used, it just seems to restore to the last "static" state.
> "Mike John" wrote:
>> Master and MSDB no choice as they get reset to simple anyway. Regular
>> (daily) full backups tends to be the most common.
>> Model - matters little but it does set the default recovey model for new
>> databses being created.
>> Mike John
>> "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> news:70327943-6C4C-46B0-98AD-C875FCD7F9D1@.microsoft.com...
>> > What Recovery Model and what Backup Type should be used on the Master,
>> > Model
>> > and MSDB databases?
>>

Master, Model, MSDB What Recovery Models

What Recovery Model and what Backup Type should be used on the Master, Model
and MSDB databases?
Master and MSDB no choice as they get reset to simple anyway. Regular
(daily) full backups tends to be the most common.
Model - matters little but it does set the default recovey model for new
databses being created.
Mike John
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:70327943-6C4C-46B0-98AD-C875FCD7F9D1@.microsoft.com...
> What Recovery Model and what Backup Type should be used on the Master,
> Model
> and MSDB databases?
|||Hmmmm
1. Are you saying that that the Master and MSDB will always stay in simple
no matter what I pick?
2. If I do a full back up of the Master and MSDB every night and the
recovery mode is locked to simple, the Transaction Log that comes with the
full is not really used, it just seems to restore to the last "static" state.
"Mike John" wrote:

> Master and MSDB no choice as they get reset to simple anyway. Regular
> (daily) full backups tends to be the most common.
> Model - matters little but it does set the default recovey model for new
> databses being created.
> Mike John
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:70327943-6C4C-46B0-98AD-C875FCD7F9D1@.microsoft.com...
>
>
|||You can have msdb in full recovery mode. I usually to log backups for msdb. The problem is that
Agent will set msdb to simple on startup. But you can create a job that reverses this and set the
job as a startup job.
You can set master to full, but that is a "null" operation as behavior is still same as simple and
you still cannot do log backups. I'd prefer an error message if I (try) to set master to full.
The transaction log is definitely used in simple recovery mode, for things as rollbacks and recovery
on startup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:62355AFE-F239-49BB-8F33-C50CDCD900C8@.microsoft.com...[vbcol=seagreen]
> Hmmmm
> 1. Are you saying that that the Master and MSDB will always stay in simple
> no matter what I pick?
> 2. If I do a full back up of the Master and MSDB every night and the
> recovery mode is locked to simple, the Transaction Log that comes with the
> full is not really used, it just seems to restore to the last "static" state.
> "Mike John" wrote:

Master, Model, MSDB What Recovery Models

What Recovery Model and what Backup Type should be used on the Master, Model
and MSDB databases?Master and MSDB no choice as they get reset to simple anyway. Regular
(daily) full backups tends to be the most common.
Model - matters little but it does set the default recovey model for new
databses being created.
Mike John
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:70327943-6C4C-46B0-98AD-C875FCD7F9D1@.microsoft.com...
> What Recovery Model and what Backup Type should be used on the Master,
> Model
> and MSDB databases?|||Hmmmm
1. Are you saying that that the Master and MSDB will always stay in simple
no matter what I pick?
2. If I do a full back up of the Master and MSDB every night and the
recovery mode is locked to simple, the Transaction Log that comes with the
full is not really used, it just seems to restore to the last "static" state
.
"Mike John" wrote:

> Master and MSDB no choice as they get reset to simple anyway. Regular
> (daily) full backups tends to be the most common.
> Model - matters little but it does set the default recovey model for new
> databses being created.
> Mike John
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:70327943-6C4C-46B0-98AD-C875FCD7F9D1@.microsoft.com...
>
>|||You can have msdb in full recovery mode. I usually to log backups for msdb.
The problem is that
Agent will set msdb to simple on startup. But you can create a job that reve
rses this and set the
job as a startup job.
You can set master to full, but that is a "null" operation as behavior is st
ill same as simple and
you still cannot do log backups. I'd prefer an error message if I (try) to s
et master to full.
The transaction log is definitely used in simple recovery mode, for things a
s rollbacks and recovery
on startup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:62355AFE-F239-49BB-8F33-C50CDCD900C8@.microsoft.com...[vbcol=seagreen]
> Hmmmm
> 1. Are you saying that that the Master and MSDB will always stay in simple
> no matter what I pick?
> 2. If I do a full back up of the Master and MSDB every night and the
> recovery mode is locked to simple, the Transaction Log that comes with the
> full is not really used, it just seems to restore to the last "static" sta
te.
> "Mike John" wrote:
>