Help With Scheduled Reports

When i set up a subscription i get the following error
Failure sending mail: The Report Server has encountered a configuration
error; more details in the log files
I have found the following on MSDN
http://support.microsoft.com/default.aspx?scid=kb;en-us;842423
The above details fixes for 2000 and 2003 domains however we are on a NT
domain
When i change the account to a domain account (even administrator) when it
is time for the subscription to run it does not execute
does any body have any ideas on how to fix this
thanks
RegWe had the same issue awhile back. Make sure you're up to date on your
service packs. Also, you can check the log file (ReportServerService) for
more error messages and details.
"Reg Besseling" wrote:
> When i set up a subscription i get the following error
> Failure sending mail: The Report Server has encountered a configuration
> error; more details in the log files
> I have found the following on MSDN
> http://support.microsoft.com/default.aspx?scid=kb;en-us;842423
> The above details fixes for 2000 and 2003 domains however we are on a NT
> domain
> When i change the account to a domain account (even administrator) when it
> is time for the subscription to run it does not execute
> does any body have any ideas on how to fix this
> thanks
> Reg
>|||It appears that applying MS05-043 , MS05-038 and MS05-039 caused the issue
to start as all was working before
"daw" <daw@.discussions.microsoft.com> wrote in message
news:CA6823E7-640E-4E86-B59F-65049301D885@.microsoft.com...
> We had the same issue awhile back. Make sure you're up to date on your
> service packs. Also, you can check the log file (ReportServerService) for
> more error messages and details.
> "Reg Besseling" wrote:
>> When i set up a subscription i get the following error
>> Failure sending mail: The Report Server has encountered a configuration
>> error; more details in the log files
>> I have found the following on MSDN
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;842423
>> The above details fixes for 2000 and 2003 domains however we are on a NT
>> domain
>> When i change the account to a domain account (even administrator) when
>> it
>> is time for the subscription to run it does not execute
>> does any body have any ideas on how to fix this
>> thanks
>> Reg
>>

Help with query optimization

Hi,
Suppose that I have a Master table with almost 40,000 records. The Details
table contains 50,000 records. My query Inner Joins these two table and the
query optimizer chooses Hash Join algorithm to perform the query.
Obviously it will not be a good idea to try to change the plan to a Nested
Loop because the number of rows in tables are large and close to each other.
Therefore Merge Join will (probably) be the best algorithm. I must create an
index on Details table beginning with FK column and including other columns
to cover the query.
The problem is that sometimes the number of required columns are more that
allowable quantity or the length of index exceeds 900 bytes.
Should I convince my boss to be satisfy with Hash Join or there's a
solution?
Any help will be greatly appreciated.
LeilaAnother solution would be to have a clustered index on the foreign table
starting with the foreign key column.
FYI, in SQL Server 2005 you will be able to create indexes with included
non-key columns for covering purposes, and the 900 bytes limitation does not
apply to included non-key columns.
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"Leila" <leilas@.hotpop.com> wrote in message
news:eI50yOoRFHA.2932@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Suppose that I have a Master table with almost 40,000 records. The Details
> table contains 50,000 records. My query Inner Joins these two table and
> the
> query optimizer chooses Hash Join algorithm to perform the query.
> Obviously it will not be a good idea to try to change the plan to a Nested
> Loop because the number of rows in tables are large and close to each
> other.
> Therefore Merge Join will (probably) be the best algorithm. I must create
> an
> index on Details table beginning with FK column and including other
> columns
> to cover the query.
> The problem is that sometimes the number of required columns are more that
> allowable quantity or the length of index exceeds 900 bytes.
> Should I convince my boss to be satisfy with Hash Join or there's a
> solution?
> Any help will be greatly appreciated.
> Leila
>|||Thanks Itzik,
I thought about it, but I have several queries like that, I cannot have a
clustered index for each ;-)
Any solution before 2005?!
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:em72QUoRFHA.164@.TK2MSFTNGP12.phx.gbl...
> Another solution would be to have a clustered index on the foreign table
> starting with the foreign key column.
> FYI, in SQL Server 2005 you will be able to create indexes with included
> non-key columns for covering purposes, and the 900 bytes limitation does
not
> apply to included non-key columns.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Leila" <leilas@.hotpop.com> wrote in message
> news:eI50yOoRFHA.2932@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> > Suppose that I have a Master table with almost 40,000 records. The
Details
> > table contains 50,000 records. My query Inner Joins these two table and
> > the
> > query optimizer chooses Hash Join algorithm to perform the query.
> > Obviously it will not be a good idea to try to change the plan to a
Nested
> > Loop because the number of rows in tables are large and close to each
> > other.
> > Therefore Merge Join will (probably) be the best algorithm. I must
create
> > an
> > index on Details table beginning with FK column and including other
> > columns
> > to cover the query.
> > The problem is that sometimes the number of required columns are more
that
> > allowable quantity or the length of index exceeds 900 bytes.
> > Should I convince my boss to be satisfy with Hash Join or there's a
> > solution?
> > Any help will be greatly appreciated.
> > Leila
> >
> >
>|||<snip>... and including other columns to cover the query.</snip>
WHy do you need it to be a covering index? That is useful when the number
of columns required by a query is small, but if a query returns a large
number of columns, that is not a good idea, and whwther or not a covering
index exists will not affect the type of join algorithm the optmizer uses...
"Leila" wrote:
> Hi,
> Suppose that I have a Master table with almost 40,000 records. The Details
> table contains 50,000 records. My query Inner Joins these two table and the
> query optimizer chooses Hash Join algorithm to perform the query.
> Obviously it will not be a good idea to try to change the plan to a Nested
> Loop because the number of rows in tables are large and close to each other.
> Therefore Merge Join will (probably) be the best algorithm. I must create an
> index on Details table beginning with FK column and including other columns
> to cover the query.
> The problem is that sometimes the number of required columns are more that
> allowable quantity or the length of index exceeds 900 bytes.
> Should I convince my boss to be satisfy with Hash Join or there's a
> solution?
> Any help will be greatly appreciated.
> Leila
>
>|||Indexed views is another option.
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"Leila" <leilas@.hotpop.com> wrote in message
news:%23ZlieboRFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Thanks Itzik,
> I thought about it, but I have several queries like that, I cannot have a
> clustered index for each ;-)
> Any solution before 2005?!
>
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
> message
> news:em72QUoRFHA.164@.TK2MSFTNGP12.phx.gbl...
>> Another solution would be to have a clustered index on the foreign table
>> starting with the foreign key column.
>> FYI, in SQL Server 2005 you will be able to create indexes with included
>> non-key columns for covering purposes, and the 900 bytes limitation does
> not
>> apply to included non-key columns.
>> --
>> BG, SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "Leila" <leilas@.hotpop.com> wrote in message
>> news:eI50yOoRFHA.2932@.TK2MSFTNGP09.phx.gbl...
>> > Hi,
>> > Suppose that I have a Master table with almost 40,000 records. The
> Details
>> > table contains 50,000 records. My query Inner Joins these two table and
>> > the
>> > query optimizer chooses Hash Join algorithm to perform the query.
>> > Obviously it will not be a good idea to try to change the plan to a
> Nested
>> > Loop because the number of rows in tables are large and close to each
>> > other.
>> > Therefore Merge Join will (probably) be the best algorithm. I must
> create
>> > an
>> > index on Details table beginning with FK column and including other
>> > columns
>> > to cover the query.
>> > The problem is that sometimes the number of required columns are more
> that
>> > allowable quantity or the length of index exceeds 900 bytes.
>> > Should I convince my boss to be satisfy with Hash Join or there's a
>> > solution?
>> > Any help will be greatly appreciated.
>> > Leila
>> >
>> >
>>
>|||Are you trying to join the entire table at a time? If so it might be
cheaper overall to just go Hash Join. It is a pretty good algorithm, though
you are right the Merge Join will be good. I wouldn't try to index all rows
necessarily because that will be costly to maintain.
The Nested loops join should be the best algorithm for a simple one to many
with low cardinality (which you should have since your parent table only has
4/5 of the number of rows that the child has) and a reasonable join key. Do
you have an index on the foreign key now? Can you post the table
structures?
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Leila" <leilas@.hotpop.com> wrote in message
news:eI50yOoRFHA.2932@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Suppose that I have a Master table with almost 40,000 records. The Details
> table contains 50,000 records. My query Inner Joins these two table and
> the
> query optimizer chooses Hash Join algorithm to perform the query.
> Obviously it will not be a good idea to try to change the plan to a Nested
> Loop because the number of rows in tables are large and close to each
> other.
> Therefore Merge Join will (probably) be the best algorithm. I must create
> an
> index on Details table beginning with FK column and including other
> columns
> to cover the query.
> The problem is that sometimes the number of required columns are more that
> allowable quantity or the length of index exceeds 900 bytes.
> Should I convince my boss to be satisfy with Hash Join or there's a
> solution?
> Any help will be greatly appreciated.
> Leila
>|||If the index is not covering, then I suppose bookmark lookup will be
required to gather other columns. Can lookup happen in a merge join?
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:995BAB85-A165-4239-A14F-8A263CC2F115@.microsoft.com...
> <snip>... and including other columns to cover the query.</snip>
> WHy do you need it to be a covering index? That is useful when the number
> of columns required by a query is small, but if a query returns a large
> number of columns, that is not a good idea, and whwther or not a covering
> index exists will not affect the type of join algorithm the optmizer
uses...
>
> "Leila" wrote:
> > Hi,
> > Suppose that I have a Master table with almost 40,000 records. The
Details
> > table contains 50,000 records. My query Inner Joins these two table and
the
> > query optimizer chooses Hash Join algorithm to perform the query.
> > Obviously it will not be a good idea to try to change the plan to a
Nested
> > Loop because the number of rows in tables are large and close to each
other.
> > Therefore Merge Join will (probably) be the best algorithm. I must
create an
> > index on Details table beginning with FK column and including other
columns
> > to cover the query.
> > The problem is that sometimes the number of required columns are more
that
> > allowable quantity or the length of index exceeds 900 bytes.
> > Should I convince my boss to be satisfy with Hash Join or there's a
> > solution?
> > Any help will be greatly appreciated.
> > Leila
> >
> >
> >|||Thanks Louis!
> Are you trying to join the entire table at a time?
Yes, I need to.
> The Nested loops join should be the best algorithm for a simple one to
many
> with low cardinality (which you should have since your parent table only
has
> 4/5 of the number of rows that the child has) and a reasonable join key.
Nested loop should be good when the Master table is small. I don't think if
40,000 index seeks on Details table can result in a good performance.
> Do you have an index on the foreign key now?
Yes but it doesn't help.
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:uEJJl$qRFHA.3476@.TK2MSFTNGP10.phx.gbl...
> Are you trying to join the entire table at a time? If so it might be
> cheaper overall to just go Hash Join. It is a pretty good algorithm,
though
> you are right the Merge Join will be good. I wouldn't try to index all
rows
> necessarily because that will be costly to maintain.
> The Nested loops join should be the best algorithm for a simple one to
many
> with low cardinality (which you should have since your parent table only
has
> 4/5 of the number of rows that the child has) and a reasonable join key.
Do
> you have an index on the foreign key now? Can you post the table
> structures?
>
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "Leila" <leilas@.hotpop.com> wrote in message
> news:eI50yOoRFHA.2932@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> > Suppose that I have a Master table with almost 40,000 records. The
Details
> > table contains 50,000 records. My query Inner Joins these two table and
> > the
> > query optimizer chooses Hash Join algorithm to perform the query.
> > Obviously it will not be a good idea to try to change the plan to a
Nested
> > Loop because the number of rows in tables are large and close to each
> > other.
> > Therefore Merge Join will (probably) be the best algorithm. I must
create
> > an
> > index on Details table beginning with FK column and including other
> > columns
> > to cover the query.
> > The problem is that sometimes the number of required columns are more
that
> > allowable quantity or the length of index exceeds 900 bytes.
> > Should I convince my boss to be satisfy with Hash Join or there's a
> > solution?
> > Any help will be greatly appreciated.
> > Leila
> >
> >
>|||Great! Do you mean I create an indexed view on all required columns of
Details table and join the Master with this view or ...?
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:uslJpOpRFHA.3880@.tk2msftngp13.phx.gbl...
> Indexed views is another option.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Leila" <leilas@.hotpop.com> wrote in message
> news:%23ZlieboRFHA.3928@.TK2MSFTNGP09.phx.gbl...
> > Thanks Itzik,
> > I thought about it, but I have several queries like that, I cannot have
a
> > clustered index for each ;-)
> > Any solution before 2005?!
> >
> >
> > "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
> > message
> > news:em72QUoRFHA.164@.TK2MSFTNGP12.phx.gbl...
> >> Another solution would be to have a clustered index on the foreign
table
> >> starting with the foreign key column.
> >>
> >> FYI, in SQL Server 2005 you will be able to create indexes with
included
> >> non-key columns for covering purposes, and the 900 bytes limitation
does
> > not
> >> apply to included non-key columns.
> >>
> >> --
> >> BG, SQL Server MVP
> >> www.SolidQualityLearning.com
> >>
> >>
> >> "Leila" <leilas@.hotpop.com> wrote in message
> >> news:eI50yOoRFHA.2932@.TK2MSFTNGP09.phx.gbl...
> >> > Hi,
> >> > Suppose that I have a Master table with almost 40,000 records. The
> > Details
> >> > table contains 50,000 records. My query Inner Joins these two table
and
> >> > the
> >> > query optimizer chooses Hash Join algorithm to perform the query.
> >> > Obviously it will not be a good idea to try to change the plan to a
> > Nested
> >> > Loop because the number of rows in tables are large and close to each
> >> > other.
> >> > Therefore Merge Join will (probably) be the best algorithm. I must
> > create
> >> > an
> >> > index on Details table beginning with FK column and including other
> >> > columns
> >> > to cover the query.
> >> > The problem is that sometimes the number of required columns are more
> > that
> >> > allowable quantity or the length of index exceeds 900 bytes.
> >> > Should I convince my boss to be satisfy with Hash Join or there's a
> >> > solution?
> >> > Any help will be greatly appreciated.
> >> > Leila
> >> >
> >> >
> >>
> >>
> >
> >
>|||Yes, sure...
"Leila" wrote:
> If the index is not covering, then I suppose bookmark lookup will be
> required to gather other columns. Can lookup happen in a merge join?
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:995BAB85-A165-4239-A14F-8A263CC2F115@.microsoft.com...
> > <snip>... and including other columns to cover the query.</snip>
> >
> > WHy do you need it to be a covering index? That is useful when the number
> > of columns required by a query is small, but if a query returns a large
> > number of columns, that is not a good idea, and whwther or not a covering
> > index exists will not affect the type of join algorithm the optmizer
> uses...
> >
> >
> > "Leila" wrote:
> >
> > > Hi,
> > > Suppose that I have a Master table with almost 40,000 records. The
> Details
> > > table contains 50,000 records. My query Inner Joins these two table and
> the
> > > query optimizer chooses Hash Join algorithm to perform the query.
> > > Obviously it will not be a good idea to try to change the plan to a
> Nested
> > > Loop because the number of rows in tables are large and close to each
> other.
> > > Therefore Merge Join will (probably) be the best algorithm. I must
> create an
> > > index on Details table beginning with FK column and including other
> columns
> > > to cover the query.
> > > The problem is that sometimes the number of required columns are more
> that
> > > allowable quantity or the length of index exceeds 900 bytes.
> > > Should I convince my boss to be satisfy with Hash Join or there's a
> > > solution?
> > > Any help will be greatly appreciated.
> > > Leila
> > >
> > >
> > >
>
>|||That's one option. The other (in case it's an Enterprise edition), is to
continue querying the base tables.
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"Leila" <leilas@.hotpop.com> wrote in message
news:ucfOGHsRFHA.3120@.TK2MSFTNGP10.phx.gbl...
> Great! Do you mean I create an indexed view on all required columns of
> Details table and join the Master with this view or ...?
>
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
> message
> news:uslJpOpRFHA.3880@.tk2msftngp13.phx.gbl...
>> Indexed views is another option.
>> --
>> BG, SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "Leila" <leilas@.hotpop.com> wrote in message
>> news:%23ZlieboRFHA.3928@.TK2MSFTNGP09.phx.gbl...
>> > Thanks Itzik,
>> > I thought about it, but I have several queries like that, I cannot have
> a
>> > clustered index for each ;-)
>> > Any solution before 2005?!
>> >
>> >
>> > "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
>> > message
>> > news:em72QUoRFHA.164@.TK2MSFTNGP12.phx.gbl...
>> >> Another solution would be to have a clustered index on the foreign
> table
>> >> starting with the foreign key column.
>> >>
>> >> FYI, in SQL Server 2005 you will be able to create indexes with
> included
>> >> non-key columns for covering purposes, and the 900 bytes limitation
> does
>> > not
>> >> apply to included non-key columns.
>> >>
>> >> --
>> >> BG, SQL Server MVP
>> >> www.SolidQualityLearning.com
>> >>
>> >>
>> >> "Leila" <leilas@.hotpop.com> wrote in message
>> >> news:eI50yOoRFHA.2932@.TK2MSFTNGP09.phx.gbl...
>> >> > Hi,
>> >> > Suppose that I have a Master table with almost 40,000 records. The
>> > Details
>> >> > table contains 50,000 records. My query Inner Joins these two table
> and
>> >> > the
>> >> > query optimizer chooses Hash Join algorithm to perform the query.
>> >> > Obviously it will not be a good idea to try to change the plan to a
>> > Nested
>> >> > Loop because the number of rows in tables are large and close to
>> >> > each
>> >> > other.
>> >> > Therefore Merge Join will (probably) be the best algorithm. I must
>> > create
>> >> > an
>> >> > index on Details table beginning with FK column and including other
>> >> > columns
>> >> > to cover the query.
>> >> > The problem is that sometimes the number of required columns are
>> >> > more
>> > that
>> >> > allowable quantity or the length of index exceeds 900 bytes.
>> >> > Should I convince my boss to be satisfy with Hash Join or there's a
>> >> > solution?
>> >> > Any help will be greatly appreciated.
>> >> > Leila
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>|||Sorry I didn't get it, could please tell me more!
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:#90aRmvRFHA.3928@.TK2MSFTNGP09.phx.gbl...
> That's one option. The other (in case it's an Enterprise edition), is to
> continue querying the base tables.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Leila" <leilas@.hotpop.com> wrote in message
> news:ucfOGHsRFHA.3120@.TK2MSFTNGP10.phx.gbl...
> > Great! Do you mean I create an indexed view on all required columns of
> > Details table and join the Master with this view or ...?
> >
> >
> > "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
> > message
> > news:uslJpOpRFHA.3880@.tk2msftngp13.phx.gbl...
> >> Indexed views is another option.
> >>
> >> --
> >> BG, SQL Server MVP
> >> www.SolidQualityLearning.com
> >>
> >>
> >> "Leila" <leilas@.hotpop.com> wrote in message
> >> news:%23ZlieboRFHA.3928@.TK2MSFTNGP09.phx.gbl...
> >> > Thanks Itzik,
> >> > I thought about it, but I have several queries like that, I cannot
have
> > a
> >> > clustered index for each ;-)
> >> > Any solution before 2005?!
> >> >
> >> >
> >> > "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
> >> > message
> >> > news:em72QUoRFHA.164@.TK2MSFTNGP12.phx.gbl...
> >> >> Another solution would be to have a clustered index on the foreign
> > table
> >> >> starting with the foreign key column.
> >> >>
> >> >> FYI, in SQL Server 2005 you will be able to create indexes with
> > included
> >> >> non-key columns for covering purposes, and the 900 bytes limitation
> > does
> >> > not
> >> >> apply to included non-key columns.
> >> >>
> >> >> --
> >> >> BG, SQL Server MVP
> >> >> www.SolidQualityLearning.com
> >> >>
> >> >>
> >> >> "Leila" <leilas@.hotpop.com> wrote in message
> >> >> news:eI50yOoRFHA.2932@.TK2MSFTNGP09.phx.gbl...
> >> >> > Hi,
> >> >> > Suppose that I have a Master table with almost 40,000 records. The
> >> > Details
> >> >> > table contains 50,000 records. My query Inner Joins these two
table
> > and
> >> >> > the
> >> >> > query optimizer chooses Hash Join algorithm to perform the query.
> >> >> > Obviously it will not be a good idea to try to change the plan to
a
> >> > Nested
> >> >> > Loop because the number of rows in tables are large and close to
> >> >> > each
> >> >> > other.
> >> >> > Therefore Merge Join will (probably) be the best algorithm. I must
> >> > create
> >> >> > an
> >> >> > index on Details table beginning with FK column and including
other
> >> >> > columns
> >> >> > to cover the query.
> >> >> > The problem is that sometimes the number of required columns are
> >> >> > more
> >> > that
> >> >> > allowable quantity or the length of index exceeds 900 bytes.
> >> >> > Should I convince my boss to be satisfy with Hash Join or there's
a
> >> >> > solution?
> >> >> > Any help will be greatly appreciated.
> >> >> > Leila
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>|||How slow is it now, and how much faster do you want it? That is a tall
order to join so many rows at once. It sounds like you might be stuck where
you are. Can you post the plan?
--
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Leila" <leilas@.hotpop.com> wrote in message
news:OCSOPHsRFHA.3120@.TK2MSFTNGP10.phx.gbl...
> Thanks Louis!
>> Are you trying to join the entire table at a time?
> Yes, I need to.
>> The Nested loops join should be the best algorithm for a simple one to
> many
>> with low cardinality (which you should have since your parent table only
> has
>> 4/5 of the number of rows that the child has) and a reasonable join key.
> Nested loop should be good when the Master table is small. I don't think
> if
> 40,000 index seeks on Details table can result in a good performance.
>> Do you have an index on the foreign key now?
> Yes but it doesn't help.
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:uEJJl$qRFHA.3476@.TK2MSFTNGP10.phx.gbl...
>> Are you trying to join the entire table at a time? If so it might be
>> cheaper overall to just go Hash Join. It is a pretty good algorithm,
> though
>> you are right the Merge Join will be good. I wouldn't try to index all
> rows
>> necessarily because that will be costly to maintain.
>> The Nested loops join should be the best algorithm for a simple one to
> many
>> with low cardinality (which you should have since your parent table only
> has
>> 4/5 of the number of rows that the child has) and a reasonable join key.
> Do
>> you have an index on the foreign key now? Can you post the table
>> structures?
>>
>> --
>> ----
> --
>> Louis Davidson - drsql@.hotmail.com
>> SQL Server MVP
>> Compass Technology Management - www.compass.net
>> Pro SQL Server 2000 Database Design -
>> http://www.apress.com/book/bookDisplay.html?bID=266
>> Blog - http://spaces.msn.com/members/drsql/
>> Note: Please reply to the newsgroups only unless you are interested in
>> consulting services. All other replies may be ignored :)
>> "Leila" <leilas@.hotpop.com> wrote in message
>> news:eI50yOoRFHA.2932@.TK2MSFTNGP09.phx.gbl...
>> > Hi,
>> > Suppose that I have a Master table with almost 40,000 records. The
> Details
>> > table contains 50,000 records. My query Inner Joins these two table and
>> > the
>> > query optimizer chooses Hash Join algorithm to perform the query.
>> > Obviously it will not be a good idea to try to change the plan to a
> Nested
>> > Loop because the number of rows in tables are large and close to each
>> > other.
>> > Therefore Merge Join will (probably) be the best algorithm. I must
> create
>> > an
>> > index on Details table beginning with FK column and including other
>> > columns
>> > to cover the query.
>> > The problem is that sometimes the number of required columns are more
> that
>> > allowable quantity or the length of index exceeds 900 bytes.
>> > Should I convince my boss to be satisfy with Hash Join or there's a
>> > solution?
>> > Any help will be greatly appreciated.
>> > Leila
>> >
>> >
>>
>|||Sure.
In Enterprise edition the optimizer can consider using an indexed view even
if you don't query the view directly, rather the base tables.
It doesn't work in all cases, i.e., there are still cases where the
optimizer reverts to the base tables and not the indexed view, but you can
try and hope for the best.
BTW, SQL Server 2005 does a better job at this, and it uses the indexed view
in more cases.
If it's not an Enterprise edition, in order to use the indexed view, you
must:
1. Query the view directly
2. Specify the NOEXPAND hint
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"Leila" <leilas@.hotpop.com> wrote in message
news:Ob0uibwRFHA.576@.TK2MSFTNGP15.phx.gbl...
> Sorry I didn't get it, could please tell me more!
>
>
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
> message
> news:#90aRmvRFHA.3928@.TK2MSFTNGP09.phx.gbl...
>> That's one option. The other (in case it's an Enterprise edition), is to
>> continue querying the base tables.
>> --
>> BG, SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "Leila" <leilas@.hotpop.com> wrote in message
>> news:ucfOGHsRFHA.3120@.TK2MSFTNGP10.phx.gbl...
>> > Great! Do you mean I create an indexed view on all required columns of
>> > Details table and join the Master with this view or ...?
>> >
>> >
>> > "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
>> > message
>> > news:uslJpOpRFHA.3880@.tk2msftngp13.phx.gbl...
>> >> Indexed views is another option.
>> >>
>> >> --
>> >> BG, SQL Server MVP
>> >> www.SolidQualityLearning.com
>> >>
>> >>
>> >> "Leila" <leilas@.hotpop.com> wrote in message
>> >> news:%23ZlieboRFHA.3928@.TK2MSFTNGP09.phx.gbl...
>> >> > Thanks Itzik,
>> >> > I thought about it, but I have several queries like that, I cannot
> have
>> > a
>> >> > clustered index for each ;-)
>> >> > Any solution before 2005?!
>> >> >
>> >> >
>> >> > "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
>> >> > message
>> >> > news:em72QUoRFHA.164@.TK2MSFTNGP12.phx.gbl...
>> >> >> Another solution would be to have a clustered index on the foreign
>> > table
>> >> >> starting with the foreign key column.
>> >> >>
>> >> >> FYI, in SQL Server 2005 you will be able to create indexes with
>> > included
>> >> >> non-key columns for covering purposes, and the 900 bytes limitation
>> > does
>> >> > not
>> >> >> apply to included non-key columns.
>> >> >>
>> >> >> --
>> >> >> BG, SQL Server MVP
>> >> >> www.SolidQualityLearning.com
>> >> >>
>> >> >>
>> >> >> "Leila" <leilas@.hotpop.com> wrote in message
>> >> >> news:eI50yOoRFHA.2932@.TK2MSFTNGP09.phx.gbl...
>> >> >> > Hi,
>> >> >> > Suppose that I have a Master table with almost 40,000 records.
>> >> >> > The
>> >> > Details
>> >> >> > table contains 50,000 records. My query Inner Joins these two
> table
>> > and
>> >> >> > the
>> >> >> > query optimizer chooses Hash Join algorithm to perform the query.
>> >> >> > Obviously it will not be a good idea to try to change the plan to
> a
>> >> > Nested
>> >> >> > Loop because the number of rows in tables are large and close to
>> >> >> > each
>> >> >> > other.
>> >> >> > Therefore Merge Join will (probably) be the best algorithm. I
>> >> >> > must
>> >> > create
>> >> >> > an
>> >> >> > index on Details table beginning with FK column and including
> other
>> >> >> > columns
>> >> >> > to cover the query.
>> >> >> > The problem is that sometimes the number of required columns are
>> >> >> > more
>> >> > that
>> >> >> > allowable quantity or the length of index exceeds 900 bytes.
>> >> >> > Should I convince my boss to be satisfy with Hash Join or there's
> a
>> >> >> > solution?
>> >> >> > Any help will be greatly appreciated.
>> >> >> > Leila
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>|||Thanks indeed!
What if i create an indexed view from the main INNER JOIN query rather than
creating indexed view from only Details table?
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:ePgHVk1RFHA.904@.tk2msftngp13.phx.gbl...
> Sure.
> In Enterprise edition the optimizer can consider using an indexed view
even
> if you don't query the view directly, rather the base tables.
> It doesn't work in all cases, i.e., there are still cases where the
> optimizer reverts to the base tables and not the indexed view, but you can
> try and hope for the best.
> BTW, SQL Server 2005 does a better job at this, and it uses the indexed
view
> in more cases.
> If it's not an Enterprise edition, in order to use the indexed view, you
> must:
> 1. Query the view directly
> 2. Specify the NOEXPAND hint
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Leila" <leilas@.hotpop.com> wrote in message
> news:Ob0uibwRFHA.576@.TK2MSFTNGP15.phx.gbl...
> > Sorry I didn't get it, could please tell me more!
> >
> >
> >
> >
> > "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
> > message
> > news:#90aRmvRFHA.3928@.TK2MSFTNGP09.phx.gbl...
> >> That's one option. The other (in case it's an Enterprise edition), is
to
> >> continue querying the base tables.
> >>
> >> --
> >> BG, SQL Server MVP
> >> www.SolidQualityLearning.com
> >>
> >>
> >> "Leila" <leilas@.hotpop.com> wrote in message
> >> news:ucfOGHsRFHA.3120@.TK2MSFTNGP10.phx.gbl...
> >> > Great! Do you mean I create an indexed view on all required columns
of
> >> > Details table and join the Master with this view or ...?
> >> >
> >> >
> >> > "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
> >> > message
> >> > news:uslJpOpRFHA.3880@.tk2msftngp13.phx.gbl...
> >> >> Indexed views is another option.
> >> >>
> >> >> --
> >> >> BG, SQL Server MVP
> >> >> www.SolidQualityLearning.com
> >> >>
> >> >>
> >> >> "Leila" <leilas@.hotpop.com> wrote in message
> >> >> news:%23ZlieboRFHA.3928@.TK2MSFTNGP09.phx.gbl...
> >> >> > Thanks Itzik,
> >> >> > I thought about it, but I have several queries like that, I cannot
> > have
> >> > a
> >> >> > clustered index for each ;-)
> >> >> > Any solution before 2005?!
> >> >> >
> >> >> >
> >> >> > "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote
in
> >> >> > message
> >> >> > news:em72QUoRFHA.164@.TK2MSFTNGP12.phx.gbl...
> >> >> >> Another solution would be to have a clustered index on the
foreign
> >> > table
> >> >> >> starting with the foreign key column.
> >> >> >>
> >> >> >> FYI, in SQL Server 2005 you will be able to create indexes with
> >> > included
> >> >> >> non-key columns for covering purposes, and the 900 bytes
limitation
> >> > does
> >> >> > not
> >> >> >> apply to included non-key columns.
> >> >> >>
> >> >> >> --
> >> >> >> BG, SQL Server MVP
> >> >> >> www.SolidQualityLearning.com
> >> >> >>
> >> >> >>
> >> >> >> "Leila" <leilas@.hotpop.com> wrote in message
> >> >> >> news:eI50yOoRFHA.2932@.TK2MSFTNGP09.phx.gbl...
> >> >> >> > Hi,
> >> >> >> > Suppose that I have a Master table with almost 40,000 records.
> >> >> >> > The
> >> >> > Details
> >> >> >> > table contains 50,000 records. My query Inner Joins these two
> > table
> >> > and
> >> >> >> > the
> >> >> >> > query optimizer chooses Hash Join algorithm to perform the
query.
> >> >> >> > Obviously it will not be a good idea to try to change the plan
to
> > a
> >> >> > Nested
> >> >> >> > Loop because the number of rows in tables are large and close
to
> >> >> >> > each
> >> >> >> > other.
> >> >> >> > Therefore Merge Join will (probably) be the best algorithm. I
> >> >> >> > must
> >> >> > create
> >> >> >> > an
> >> >> >> > index on Details table beginning with FK column and including
> > other
> >> >> >> > columns
> >> >> >> > to cover the query.
> >> >> >> > The problem is that sometimes the number of required columns
are
> >> >> >> > more
> >> >> > that
> >> >> >> > allowable quantity or the length of index exceeds 900 bytes.
> >> >> >> > Should I convince my boss to be satisfy with Hash Join or
there's
> > a
> >> >> >> > solution?
> >> >> >> > Any help will be greatly appreciated.
> >> >> >> > Leila
> >> >> >> >
> >> >> >> >
> >> >> >>
> >> >> >>
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>|||Even better. The indexed view can cover the whole join query saving the need
for rejoining every time you query.
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"Leila" <leilas@.hotpop.com> wrote in message
news:%23cSBjY4RFHA.2748@.TK2MSFTNGP09.phx.gbl...
> Thanks indeed!
> What if i create an indexed view from the main INNER JOIN query rather
> than
> creating indexed view from only Details table?
>
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
> message
> news:ePgHVk1RFHA.904@.tk2msftngp13.phx.gbl...
>> Sure.
>> In Enterprise edition the optimizer can consider using an indexed view
> even
>> if you don't query the view directly, rather the base tables.
>> It doesn't work in all cases, i.e., there are still cases where the
>> optimizer reverts to the base tables and not the indexed view, but you
>> can
>> try and hope for the best.
>> BTW, SQL Server 2005 does a better job at this, and it uses the indexed
> view
>> in more cases.
>> If it's not an Enterprise edition, in order to use the indexed view, you
>> must:
>> 1. Query the view directly
>> 2. Specify the NOEXPAND hint
>> --
>> BG, SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "Leila" <leilas@.hotpop.com> wrote in message
>> news:Ob0uibwRFHA.576@.TK2MSFTNGP15.phx.gbl...
>> > Sorry I didn't get it, could please tell me more!
>> >
>> >
>> >
>> >
>> > "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
>> > message
>> > news:#90aRmvRFHA.3928@.TK2MSFTNGP09.phx.gbl...
>> >> That's one option. The other (in case it's an Enterprise edition), is
> to
>> >> continue querying the base tables.
>> >>
>> >> --
>> >> BG, SQL Server MVP
>> >> www.SolidQualityLearning.com
>> >>
>> >>
>> >> "Leila" <leilas@.hotpop.com> wrote in message
>> >> news:ucfOGHsRFHA.3120@.TK2MSFTNGP10.phx.gbl...
>> >> > Great! Do you mean I create an indexed view on all required columns
> of
>> >> > Details table and join the Master with this view or ...?
>> >> >
>> >> >
>> >> > "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
>> >> > message
>> >> > news:uslJpOpRFHA.3880@.tk2msftngp13.phx.gbl...
>> >> >> Indexed views is another option.
>> >> >>
>> >> >> --
>> >> >> BG, SQL Server MVP
>> >> >> www.SolidQualityLearning.com
>> >> >>
>> >> >>
>> >> >> "Leila" <leilas@.hotpop.com> wrote in message
>> >> >> news:%23ZlieboRFHA.3928@.TK2MSFTNGP09.phx.gbl...
>> >> >> > Thanks Itzik,
>> >> >> > I thought about it, but I have several queries like that, I
>> >> >> > cannot
>> > have
>> >> > a
>> >> >> > clustered index for each ;-)
>> >> >> > Any solution before 2005?!
>> >> >> >
>> >> >> >
>> >> >> > "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote
> in
>> >> >> > message
>> >> >> > news:em72QUoRFHA.164@.TK2MSFTNGP12.phx.gbl...
>> >> >> >> Another solution would be to have a clustered index on the
> foreign
>> >> > table
>> >> >> >> starting with the foreign key column.
>> >> >> >>
>> >> >> >> FYI, in SQL Server 2005 you will be able to create indexes with
>> >> > included
>> >> >> >> non-key columns for covering purposes, and the 900 bytes
> limitation
>> >> > does
>> >> >> > not
>> >> >> >> apply to included non-key columns.
>> >> >> >>
>> >> >> >> --
>> >> >> >> BG, SQL Server MVP
>> >> >> >> www.SolidQualityLearning.com
>> >> >> >>
>> >> >> >>
>> >> >> >> "Leila" <leilas@.hotpop.com> wrote in message
>> >> >> >> news:eI50yOoRFHA.2932@.TK2MSFTNGP09.phx.gbl...
>> >> >> >> > Hi,
>> >> >> >> > Suppose that I have a Master table with almost 40,000 records.
>> >> >> >> > The
>> >> >> > Details
>> >> >> >> > table contains 50,000 records. My query Inner Joins these two
>> > table
>> >> > and
>> >> >> >> > the
>> >> >> >> > query optimizer chooses Hash Join algorithm to perform the
> query.
>> >> >> >> > Obviously it will not be a good idea to try to change the plan
> to
>> > a
>> >> >> > Nested
>> >> >> >> > Loop because the number of rows in tables are large and close
> to
>> >> >> >> > each
>> >> >> >> > other.
>> >> >> >> > Therefore Merge Join will (probably) be the best algorithm. I
>> >> >> >> > must
>> >> >> > create
>> >> >> >> > an
>> >> >> >> > index on Details table beginning with FK column and including
>> > other
>> >> >> >> > columns
>> >> >> >> > to cover the query.
>> >> >> >> > The problem is that sometimes the number of required columns
> are
>> >> >> >> > more
>> >> >> > that
>> >> >> >> > allowable quantity or the length of index exceeds 900 bytes.
>> >> >> >> > Should I convince my boss to be satisfy with Hash Join or
> there's
>> > a
>> >> >> >> > solution?
>> >> >> >> > Any help will be greatly appreciated.
>> >> >> >> > Leila
>> >> >> >> >
>> >> >> >> >
>> >> >> >>
>> >> >> >>
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>

help with query

hi all
Need a bit of help/direction with an sql query
i have a table with customer details and in another with customer orders. I need to show all the orders in the same field based upon the id from the customer details table.In other words to piggy back all the orders in one field.Do i use some sort of sub query?
if i have :

customer orders:

custID Order
c1 Vauxhall
c1 ford
c1 VW
c1 BMW

so that i get:

customer car orders
c1 Vauxhall ford VW BMW


How can i achieve this? thanks daveThe best way to do a pivot is using the client software. It can be done reasonably cleanly on the server using database engine specific code. It can also be done with pure SQL, but that requires some assumptions and some rather ugly code.

The short answer is: you should really do this on the client.

-PatP|||Why not write a function that returns a list of orders and use it in the select list.
create function get_orders(i_custid VARCHAR2) RETURN VARCHAR2
IS
retval VARCHAR2(999);
BEGIN
for rec in (select order from customer_orders where custid = d_custid)
loop
retval := retval || ' '|| order;
end loop;
return retval;
end;

select distinct custid customer, get_orders(custid) "car orders"
from customer_orders;

Now, that's oracle specific but you get the gist.|||in sybase asa, use the list (http://sybooks.sybase.com/onlinebooks/group-sas/awg0802e/dbrfen8/@.Generic__BookTextView/22251;pt=14246/*;nh=1?DwebQuery=LIST+function&DwebSearchAll=1) function:
select customers.custID
, list(custorder)
from customers
left outer
join orders
on customers.custID
= orders.custID
group
by customers.custid

in mysql 4.1, use the group_concat (http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html) function:
select customers.custID
, group_concat(custorder)
from customers
left outer
join orders
on customers.custID
= orders.custID
group
by customers.custid

in other less advanced databases, write a program

;)|||Arrr, maybe we ought to start with why would you ever need this?
If I was to design my own DBMS I would have put this type of function on the LRU list and flush out of SGA ASAP.
:D|||thanks.. all your help is much appriciated ;)|||thanks.. all your help is much appriciated ;)
What DB are you on anyways?

Help with multiple jobs failing

Hello,
For some reason a couple of our jobs have been failing lately. We get
the following msg on the details of the job. Any ideas? Thanks in advance.
Msg:
Unable to connect to SQL Server 'COMPUTERNAME\INSTANCENAME'. The step
failed.
1. Is this the same server where the jobs are running?
2. Have you checked your SQL Agent logs for any more information on these
error messages?
3. Can you connect to this named isntance using Query analyser, SEM, etc?
4. Was this sql server "moved" from another box, or any such thing?
Thanks,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
|||Vikram,
1. Yes
2. See below output
3. Yes
4. We did upgrade the sql box a couple of months ago. We created scripts
and ran them against the new instance. The seem to be ran fine from Query
analyser. It's not the same job that fails everytime either it seems to be
random.
jake
2004-06-16 08:19:24 - ? [393] Waiting for SQL Server to recover databases...
2004-06-16 08:21:22 - ? [100] Microsoft SQLServerAgent version 8.00.760 (x86
unicode retail build) : Process ID
2004-06-16 08:21:23 - ? [100] Microsoft SQLServerAgent version 8.00.760 (x86
unicode retail build) : Process ID 1596
2004-06-16 08:21:23 - ? [101] SQL Server computername\DBSERVER version
8.00.760 (0 connection limit)
2004-06-16 08:21:23 - ? [102] SQL Server ODBC driver version 3.85.1025
2004-06-16 08:21:23 - ? [103] NetLib being used by driver is DBMSLPCN.DLL;
Local host server is computername\DBSERVER
2004-06-16 08:21:23 - ? [310] 1 processor(s) and 992 MB RAM detected
2004-06-16 08:21:23 - ? [339] Local computer is computername running Windows
NT 5.2 (3790)
2004-06-16 08:21:23 - ! [364] The Messenger service has not been started -
NetSend notifications will not be sent
2004-06-16 08:21:23 - ? [129] SQLAgent$DBSERVER starting under Windows NT
service control
2004-06-16 08:21:23 - ? [392] Using MAPI32.DLL from C:\WINDOWS\SYSTEM32
(version 1.0.2536.0)
2004-06-16 08:21:23 - ? [196] Attempting to start mail session using profile
'Outlook'...
2004-06-16 08:21:25 - ? [353] Mail session started (using MAPI1)
2004-06-16 08:21:25 - + [396] An idle CPU condition has not been defined -
OnIdle job schedules will have no effect
2004-06-17 05:19:32 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-17 05:19:32 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-18 02:04:11 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-18 05:21:14 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-18 05:21:15 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-19 05:13:16 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-20 05:15:57 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-20 05:15:57 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-20 05:20:23 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-21 02:14:18 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-21 02:14:18 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-21 05:16:45 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-21 05:16:45 - ! [382] Logon to server 'computername\DBSERVER' failed
(SaveAllSchedules)
2004-06-21 05:17:53 - ! [298] SQLServer Error: 17, SQL Server does not exist
or access denied. [SQLSTATE 08001]
2004-06-21 05:17:53 - ! [298] SQLServer Error: 53, ConnectionOpen
(Connect()). [SQLSTATE 01000]
2004-06-21 08:04:08 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-21 08:04:08 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-22 02:18:02 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-22 05:18:36 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-22 05:20:39 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-22 05:20:39 - ! [382] Logon to server 'computername\DBSERVER' failed
(SaveAllSchedules)
2004-06-22 07:01:32 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-23 02:17:10 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-23 02:17:15 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-23 02:19:15 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-23 07:30:41 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-23 08:19:30 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-23 08:19:30 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-24 05:15:41 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-24 05:15:41 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-24 05:16:53 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-24 05:16:53 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-25 02:08:23 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-25 02:08:23 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-25 02:09:27 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-25 02:09:32 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-25 02:18:07 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-26 05:27:36 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-26 05:29:40 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-26 05:29:40 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-28 02:03:04 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-28 05:16:25 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-28 08:50:06 - ? [131] SQLAgent$DBSERVER service stopping due to a
stop request from a user, process, or the OS...
2004-06-28 08:50:07 - ? [358] Mail session ended
2004-06-28 08:50:09 - ? [098] SQLServerAgent terminated (normally)
"Vikram Jayaram [MS]" <vikramj@.online.microsoft.com> wrote in message
news:N8GBxNPXEHA.328@.cpmsftngxa10.phx.gbl...
> 1. Is this the same server where the jobs are running?
> 2. Have you checked your SQL Agent logs for any more information on these
> error messages?
> 3. Can you connect to this named isntance using Query analyser, SEM, etc?
> 4. Was this sql server "moved" from another box, or any such thing?
> Thanks,
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>
|||Vikram,
1. Yes
2. See below output
3. Yes
4. We did upgrade the sql box a couple of months ago. We created scripts
and ran them against the new instance. The seem to be ran fine from Query
analyser. It's not the same job that fails everytime either it seems to be
random.
jake
2004-06-16 08:19:24 - ? [393] Waiting for SQL Server to recover databases...
2004-06-16 08:21:22 - ? [100] Microsoft SQLServerAgent version 8.00.760 (x86
unicode retail build) : Process ID
2004-06-16 08:21:23 - ? [100] Microsoft SQLServerAgent version 8.00.760 (x86
unicode retail build) : Process ID 1596
2004-06-16 08:21:23 - ? [101] SQL Server computername\DBSERVER version
8.00.760 (0 connection limit)
2004-06-16 08:21:23 - ? [102] SQL Server ODBC driver version 3.85.1025
2004-06-16 08:21:23 - ? [103] NetLib being used by driver is DBMSLPCN.DLL;
Local host server is computername\DBSERVER
2004-06-16 08:21:23 - ? [310] 1 processor(s) and 992 MB RAM detected
2004-06-16 08:21:23 - ? [339] Local computer is computername running Windows
NT 5.2 (3790)
2004-06-16 08:21:23 - ! [364] The Messenger service has not been started -
NetSend notifications will not be sent
2004-06-16 08:21:23 - ? [129] SQLAgent$DBSERVER starting under Windows NT
service control
2004-06-16 08:21:23 - ? [392] Using MAPI32.DLL from C:\WINDOWS\SYSTEM32
(version 1.0.2536.0)
2004-06-16 08:21:23 - ? [196] Attempting to start mail session using profile
'Outlook'...
2004-06-16 08:21:25 - ? [353] Mail session started (using MAPI1)
2004-06-16 08:21:25 - + [396] An idle CPU condition has not been defined -
OnIdle job schedules will have no effect
2004-06-17 05:19:32 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-17 05:19:32 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-18 02:04:11 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-18 05:21:14 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-18 05:21:15 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-19 05:13:16 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-20 05:15:57 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-20 05:15:57 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-20 05:20:23 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-21 02:14:18 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-21 02:14:18 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-21 05:16:45 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-21 05:16:45 - ! [382] Logon to server 'computername\DBSERVER' failed
(SaveAllSchedules)
2004-06-21 05:17:53 - ! [298] SQLServer Error: 17, SQL Server does not exist
or access denied. [SQLSTATE 08001]
2004-06-21 05:17:53 - ! [298] SQLServer Error: 53, ConnectionOpen
(Connect()). [SQLSTATE 01000]
2004-06-21 08:04:08 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-21 08:04:08 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-22 02:18:02 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-22 05:18:36 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-22 05:20:39 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-22 05:20:39 - ! [382] Logon to server 'computername\DBSERVER' failed
(SaveAllSchedules)
2004-06-22 07:01:32 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-23 02:17:10 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-23 02:17:15 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-23 02:19:15 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-23 07:30:41 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-23 08:19:30 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-23 08:19:30 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-24 05:15:41 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-24 05:15:41 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-24 05:16:53 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-24 05:16:53 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-25 02:08:23 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-25 02:08:23 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-25 02:09:27 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-25 02:09:32 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-25 02:18:07 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-26 05:27:36 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-26 05:29:40 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-26 05:29:40 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-28 02:03:04 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-28 05:16:25 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-28 08:50:06 - ? [131] SQLAgent$DBSERVER service stopping due to a
stop request from a user, process, or the OS...
2004-06-28 08:50:07 - ? [358] Mail session ended
2004-06-28 08:50:09 - ? [098] SQLServerAgent terminated (normally)
"Vikram Jayaram [MS]" <vikramj@.online.microsoft.com> wrote in message
news:N8GBxNPXEHA.328@.cpmsftngxa10.phx.gbl...
> 1. Is this the same server where the jobs are running?
> 2. Have you checked your SQL Agent logs for any more information on these
> error messages?
> 3. Can you connect to this named isntance using Query analyser, SEM, etc?
> 4. Was this sql server "moved" from another box, or any such thing?
> Thanks,
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>

Help with multiple jobs failing

Hello,
For some reason a couple of our jobs have been failing lately. We get
the following msg on the details of the job. Any ideas? Thanks in advance.
Msg:
Unable to connect to SQL Server 'COMPUTERNAME\INSTANCENAME'. The step
failed.1. Is this the same server where the jobs are running?
2. Have you checked your SQL Agent logs for any more information on these
error messages?
3. Can you connect to this named isntance using Query analyser, SEM, etc?
4. Was this sql server "moved" from another box, or any such thing?
Thanks,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||Vikram,
1. Yes
2. See below output
3. Yes
4. We did upgrade the sql box a couple of months ago. We created scripts
and ran them against the new instance. The seem to be ran fine from Query
analyser. It's not the same job that fails everytime either it seems to be
random.
jake
2004-06-16 08:19:24 - ? [393] Waiting for SQL Server to recover databases...
2004-06-16 08:21:22 - ? [100] Microsoft SQLServerAgent version 8.00.760 (x86
unicode retail build) : Process ID
2004-06-16 08:21:23 - ? [100] Microsoft SQLServerAgent version 8.00.760 (x86
unicode retail build) : Process ID 1596
2004-06-16 08:21:23 - ? [101] SQL Server computername\DBSERVER version
8.00.760 (0 connection limit)
2004-06-16 08:21:23 - ? [102] SQL Server ODBC driver version 3.85.1025
2004-06-16 08:21:23 - ? [103] NetLib being used by driver is DBMSLPCN.DLL;
Local host server is computername\DBSERVER
2004-06-16 08:21:23 - ? [310] 1 processor(s) and 992 MB RAM detected
2004-06-16 08:21:23 - ? [339] Local computer is computername running Windows
NT 5.2 (3790)
2004-06-16 08:21:23 - ! [364] The Messenger service has not been started -
NetSend notifications will not be sent
2004-06-16 08:21:23 - ? [129] SQLAgent$DBSERVER starting under Windows NT
service control
2004-06-16 08:21:23 - ? [392] Using MAPI32.DLL from C:\WINDOWS\SYSTEM32
(version 1.0.2536.0)
2004-06-16 08:21:23 - ? [196] Attempting to start mail session using profile
'Outlook'...
2004-06-16 08:21:25 - ? [353] Mail session started (using MAPI1)
2004-06-16 08:21:25 - + [396] An idle CPU condition has not been defined -
OnIdle job schedules will have no effect
2004-06-17 05:19:32 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-17 05:19:32 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-18 02:04:11 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-18 05:21:14 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-18 05:21:15 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-19 05:13:16 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-20 05:15:57 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-20 05:15:57 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-20 05:20:23 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-21 02:14:18 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-21 02:14:18 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-21 05:16:45 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-21 05:16:45 - ! [382] Logon to server 'computername\DBSERVER' failed
(SaveAllSchedules)
2004-06-21 05:17:53 - ! [298] SQLServer Error: 17, SQL Server does not exist
or access denied. [SQLSTATE 08001]
2004-06-21 05:17:53 - ! [298] SQLServer Error: 53, ConnectionOpen
(Connect()). [SQLSTATE 01000]
2004-06-21 08:04:08 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-21 08:04:08 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-22 02:18:02 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-22 05:18:36 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-22 05:20:39 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-22 05:20:39 - ! [382] Logon to server 'computername\DBSERVER' failed
(SaveAllSchedules)
2004-06-22 07:01:32 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-23 02:17:10 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-23 02:17:15 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-23 02:19:15 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-23 07:30:41 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-23 08:19:30 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-23 08:19:30 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-24 05:15:41 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-24 05:15:41 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-24 05:16:53 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-24 05:16:53 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-25 02:08:23 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-25 02:08:23 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-25 02:09:27 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-25 02:09:32 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-25 02:18:07 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-26 05:27:36 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-26 05:29:40 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-26 05:29:40 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-28 02:03:04 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-28 05:16:25 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-28 08:50:06 - ? [131] SQLAgent$DBSERVER service stopping due to a
stop request from a user, process, or the OS...
2004-06-28 08:50:07 - ? [358] Mail session ended
2004-06-28 08:50:09 - ? [098] SQLServerAgent terminated (normally)
"Vikram Jayaram [MS]" <vikramj@.online.microsoft.com> wrote in message
news:N8GBxNPXEHA.328@.cpmsftngxa10.phx.gbl...
> 1. Is this the same server where the jobs are running?
> 2. Have you checked your SQL Agent logs for any more information on these
> error messages?
> 3. Can you connect to this named isntance using Query analyser, SEM, etc?
> 4. Was this sql server "moved" from another box, or any such thing?
> Thanks,
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>

Help with multiple jobs failing

Hello,
For some reason a couple of our jobs have been failing lately. We get
the following msg on the details of the job. Any ideas? Thanks in advance.
Msg:
Unable to connect to SQL Server 'COMPUTERNAME\INSTANCENAME'. The step
failed.1. Is this the same server where the jobs are running?
2. Have you checked your SQL Agent logs for any more information on these
error messages?
3. Can you connect to this named isntance using Query analyser, SEM, etc?
4. Was this sql server "moved" from another box, or any such thing?
Thanks,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||Vikram,
1. Yes
2. See below output
3. Yes
4. We did upgrade the sql box a couple of months ago. We created scripts
and ran them against the new instance. The seem to be ran fine from Query
analyser. It's not the same job that fails everytime either it seems to be
random.
jake
2004-06-16 08:19:24 - ? [393] Waiting for SQL Server to recover database
s...
2004-06-16 08:21:22 - ? [100] Microsoft SQLServerAgent version 8.00.760
(x86
unicode retail build) : Process ID
2004-06-16 08:21:23 - ? [100] Microsoft SQLServerAgent version 8.00.760
(x86
unicode retail build) : Process ID 1596
2004-06-16 08:21:23 - ? [101] SQL Server computername\DBSERVER version
8.00.760 (0 connection limit)
2004-06-16 08:21:23 - ? [102] SQL Server ODBC driver version 3.85.1025
2004-06-16 08:21:23 - ? [103] NetLib being used by driver is DBMSLPCN.DL
L;
Local host server is computername\DBSERVER
2004-06-16 08:21:23 - ? [310] 1 processor(s) and 992 MB RAM detected
2004-06-16 08:21:23 - ? [339] Local computer is computername running Win
dows
NT 5.2 (3790)
2004-06-16 08:21:23 - ! [364] The Messenger service has not been started
-
NetSend notifications will not be sent
2004-06-16 08:21:23 - ? [129] SQLAgent$DBSERVER starting under Windows N
T
service control
2004-06-16 08:21:23 - ? [392] Using MAPI32.DLL from C:\WINDOWS\SYSTEM32
(version 1.0.2536.0)
2004-06-16 08:21:23 - ? [196] Attempting to start mail session using pro
file
'Outlook'...
2004-06-16 08:21:25 - ? [353] Mail session started (using MAPI1)
2004-06-16 08:21:25 - + [396] An idle CPU condition has not been defined
-
OnIdle job schedules will have no effect
2004-06-17 05:19:32 - ! [165] ODBC Error: 0, Timeout expired [SQLSTA
TE
HYT00]
2004-06-17 05:19:32 - ! [382] Logon to server 'computername\DBSERVER' fa
iled
(ConnAttemptCachableOp)
2004-06-18 02:04:11 - ! [165] ODBC Error: 0, Timeout expired [SQLSTA
TE
HYT00]
2004-06-18 05:21:14 - ! [165] ODBC Error: 0, Timeout expired [SQLSTA
TE
HYT00]
2004-06-18 05:21:15 - ! [382] Logon to server 'computername\DBSERVER' fa
iled
(ConnAttemptCachableOp)
2004-06-19 05:13:16 - ! [165] ODBC Error: 0, Timeout expired [SQLSTA
TE
HYT00]
2004-06-20 05:15:57 - ! [165] ODBC Error: 0, Timeout expired [SQLSTA
TE
HYT00]
2004-06-20 05:15:57 - ! [382] Logon to server 'computername\DBSERVER' fa
iled
(ConnAttemptCachableOp)
2004-06-20 05:20:23 - ! [165] ODBC Error: 0, Timeout expired [SQLSTA
TE
HYT00]
2004-06-21 02:14:18 - ! [165] ODBC Error: 0, Timeout expired [SQLSTA
TE
HYT00]
2004-06-21 02:14:18 - ! [382] Logon to server 'computername\DBSERVER' fa
iled
(ConnAttemptCachableOp)
2004-06-21 05:16:45 - ! [165] ODBC Error: 0, Timeout expired [SQLSTA
TE
HYT00]
2004-06-21 05:16:45 - ! [382] Logon to server 'computername\DBSERVER' fa
iled
(SaveAllSchedules)
2004-06-21 05:17:53 - ! [298] SQLServer Error: 17, SQL Server does not e
xist
or access denied. [SQLSTATE 08001]
2004-06-21 05:17:53 - ! [298] SQLServer Error: 53, ConnectionOpen
(Connect()). [SQLSTATE 01000]
2004-06-21 08:04:08 - ! [165] ODBC Error: 0, Timeout expired [SQLSTA
TE
HYT00]
2004-06-21 08:04:08 - ! [382] Logon to server 'computername\DBSERVER' fa
iled
(ConnAttemptCachableOp)
2004-06-22 02:18:02 - ! [165] ODBC Error: 0, Timeout expired [SQLSTA
TE
HYT00]
2004-06-22 05:18:36 - ! [165] ODBC Error: 0, Timeout expired [SQLSTA
TE
HYT00]
2004-06-22 05:20:39 - ! [165] ODBC Error: 0, Timeout expired [SQLSTA
TE
HYT00]
2004-06-22 05:20:39 - ! [382] Logon to server 'computername\DBSERVER' fa
iled
(SaveAllSchedules)
2004-06-22 07:01:32 - ! [165] ODBC Error: 0, Timeout expired [SQLSTA
TE
HYT00]
2004-06-23 02:17:10 - ! [165] ODBC Error: 0, Timeout expired [SQLSTA
TE
HYT00]
2004-06-23 02:17:15 - ! [382] Logon to server 'computername\DBSERVER' fa
iled
(ConnAttemptCachableOp)
2004-06-23 02:19:15 - ! [165] ODBC Error: 0, Timeout expired [SQLSTA
TE
HYT00]
2004-06-23 07:30:41 - ! [165] ODBC Error: 0, Timeout expired [SQLSTA
TE
HYT00]
2004-06-23 08:19:30 - ! [165] ODBC Error: 0, Timeout expired [SQLSTA
TE
HYT00]
2004-06-23 08:19:30 - ! [382] Logon to server 'computername\DBSERVER' fa
iled
(ConnAttemptCachableOp)
2004-06-24 05:15:41 - ! [165] ODBC Error: 0, Timeout expired [SQLSTA
TE
HYT00]
2004-06-24 05:15:41 - ! [382] Logon to server 'computername\DBSERVER' fa
iled
(ConnAttemptCachableOp)
2004-06-24 05:16:53 - ! [165] ODBC Error: 0, Timeout expired [SQLSTA
TE
HYT00]
2004-06-24 05:16:53 - ! [382] Logon to server 'computername\DBSERVER' fa
iled
(ConnAttemptCachableOp)
2004-06-25 02:08:23 - ! [165] ODBC Error: 0, Timeout expired [SQLSTA
TE
HYT00]
2004-06-25 02:08:23 - ! [382] Logon to server 'computername\DBSERVER' fa
iled
(ConnAttemptCachableOp)
2004-06-25 02:09:27 - ! [165] ODBC Error: 0, Timeout expired [SQLSTA
TE
HYT00]
2004-06-25 02:09:32 - ! [382] Logon to server 'computername\DBSERVER' fa
iled
(ConnAttemptCachableOp)
2004-06-25 02:18:07 - ! [165] ODBC Error: 0, Timeout expired [SQLSTA
TE
HYT00]
2004-06-26 05:27:36 - ! [165] ODBC Error: 0, Timeout expired [SQLSTA
TE
HYT00]
2004-06-26 05:29:40 - ! [165] ODBC Error: 0, Timeout expired [SQLSTA
TE
HYT00]
2004-06-26 05:29:40 - ! [382] Logon to server 'computername\DBSERVER' fa
iled
(ConnAttemptCachableOp)
2004-06-28 02:03:04 - ! [165] ODBC Error: 0, Timeout expired [SQLSTA
TE
HYT00]
2004-06-28 05:16:25 - ! [165] ODBC Error: 0, Timeout expired [SQLSTA
TE
HYT00]
2004-06-28 08:50:06 - ? [131] SQLAgent$DBSERVER service stopping due to
a
stop request from a user, process, or the OS...
2004-06-28 08:50:07 - ? [358] Mail session ended
2004-06-28 08:50:09 - ? [098] SQLServerAgent terminated (normally)
"Vikram Jayaram [MS]" <vikramj@.online.microsoft.com> wrote in message
news:N8GBxNPXEHA.328@.cpmsftngxa10.phx.gbl...
> 1. Is this the same server where the jobs are running?
> 2. Have you checked your SQL Agent logs for any more information on these
> error messages?
> 3. Can you connect to this named isntance using Query analyser, SEM, etc?
> 4. Was this sql server "moved" from another box, or any such thing?
> Thanks,
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>|||Did you ever figure out what caused this? I had the same problem a few days
ago. I restarted the server and that stopped the errors but I would like to
know the cause.

Help with layout of a 'details' report

I'm trying to layout a report with the data from specific rows placed
in TextBoxes, as I want to use the report for printing out details.
My DataSet has 2 colums: 'QuestionID' and 'Answer', eg:
QuestionID Answer
--
100 Some text
101 31-Jul-07
102 More text
etc.
I want to position the Answer for a particular QuestionID in a
TextBox, as follows:
[Answer for QuestionId 100] [Answer for QuestionId 101]
[ Answer for Question Id 102
]
I need to be able to vary the position and size of each TextBox, which
I'm not sure I can do if I embed them in a table/list.
I know that I can pivot the data in the query before I fill the
dataset, which would give me a single row with a column for each
question. However I'm dealing with about 100 question id and I was
hoping to avoid the complex pivot query etc (even using SQL Server
2005 Pivot function).
Cheers,
Canice.On Jul 31, 9:19 am, Canice <canice.b...@.crestsolutions.ie> wrote:
> I'm trying to layout a report with the data from specific rows placed
> in TextBoxes, as I want to use the report for printing out details.
> My DataSet has 2 colums: 'QuestionID' and 'Answer', eg:
> QuestionID Answer
> --
> 100 Some text
> 101 31-Jul-07
> 102 More text
> etc.
> I want to position the Answer for a particular QuestionID in a
> TextBox, as follows:
> [Answer for QuestionId 100] [Answer for QuestionId 101]
> [ Answer for Question Id 102
> ]
> I need to be able to vary the position and size of each TextBox, which
> I'm not sure I can do if I embed them in a table/list.
> I know that I can pivot the data in the query before I fill the
> dataset, which would give me a single row with a column for each
> question. However I'm dealing with about 100 question id and I was
> hoping to avoid the complex pivot query etc (even using SQL Server
> 2005 Pivot function).
> Cheers,
> Canice.
>From what you have described, you may want to have the report broken
into columns. You should use a table control and then follow these
steps to limit the records in the table for column handling.
1. In the 'Layout' view of BIDS, select the table.
2. Right click the table and select 'Properties.'
3. On the 'Groups' tab, select the 'Add...' button.
4. Below 'Group on:' and 'Expression' enter the following:
'=Ceiling(RowNumber(Nothing)/18)'
5. Select 'Page break at end'
5. Select 'OK' and 'OK' again.
[NOTE: The format in step 4 is: =Ceiling(RowNumber(Nothing)/
NumberOfRowsPerColumn) ]
Then, to set the number of columns, you would do the following:
1. In the 'Layout' view of BIDS, select the 'Report' tab at the top.
2. Select 'Report Properties...' from the drop-down menu.
3. Select the 'Layout' tab.
4. Below 'Columns:' select the number of columns to split the report
into.
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Aug 1, 1:47 am, EMartinez <emartinez...@.gmail.com> wrote:
> On Jul 31, 9:19 am,Canice<canice.b...@.crestsolutions.ie> wrote:
>
> > I'm trying to layout a report with the data from specific rows placed
> > in TextBoxes, as I want to use the report for printing out details.
> > My DataSet has 2 colums: 'QuestionID' and 'Answer', eg:
> > QuestionID Answer
> > --
> > 100 Some text
> > 101 31-Jul-07
> > 102 More text
> > etc.
> > I want to position the Answer for a particular QuestionID in a
> > TextBox, as follows:
> > [Answer for QuestionId 100] [Answer for QuestionId 101]
> > [ Answer for Question Id 102
> > ]
> > I need to be able to vary the position and size of each TextBox, which
> > I'm not sure I can do if I embed them in a table/list.
> > I know that I can pivot the data in the query before I fill the
> > dataset, which would give me a single row with a column for each
> > question. However I'm dealing with about 100 question id and I was
> > hoping to avoid the complex pivot query etc (even using SQL Server
> > 2005 Pivot function).
> > Cheers,
> >Canice.
> >From what you have described, you may want to have the report broken
> into columns. You should use a table control and then follow these
> steps to limit the records in the table for column handling.
> 1. In the 'Layout' view of BIDS, select the table.
> 2. Right click the table and select 'Properties.'
> 3. On the 'Groups' tab, select the 'Add...' button.
> 4. Below 'Group on:' and 'Expression' enter the following:
> '=Ceiling(RowNumber(Nothing)/18)'
> 5. Select 'Page break at end'
> 5. Select 'OK' and 'OK' again.
> [NOTE: The format in step 4 is: =Ceiling(RowNumber(Nothing)/
> NumberOfRowsPerColumn) ]
> Then, to set the number of columns, you would do the following:
> 1. In the 'Layout' view of BIDS, select the 'Report' tab at the top.
> 2. Select 'Report Properties...' from the drop-down menu.
> 3. Select the 'Layout' tab.
> 4. Below 'Columns:' select the number of columns to split the report
> into.
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
Hi Enrique,
Thats for the reply but I don't think your solution is what I was
looking for.
>From my (very basic) knowledge of SSRS, your solution will wrap the
DataSet returned into a number of columns/rows on multiple pages.
What I was looking for is a more free-form layout, where I can place
TextBox fields for specific answers anywhere on the form (this is for
a print layout). The expression for each TextBox would be as follows:
=IIf(Fields!QuestionId.Value = 42231, Fields!Answer.Value, "")
Where the value 42231 represents a particular answer, and each TextBox
would have a different question id.
I also need all the results on a single page, again so that I can use
it for printing.
Thanks,
Canice.|||On Aug 1, 9:22 am, Canice <canice.b...@.crestsolutions.ie> wrote:
> On Aug 1, 1:47 am, EMartinez <emartinez...@.gmail.com> wrote:
>
> > On Jul 31, 9:19 am,Canice<canice.b...@.crestsolutions.ie> wrote:
> > > I'm trying to layout a report with the data from specific rows placed
> > > in TextBoxes, as I want to use the report for printing out details.
> > > My DataSet has 2 colums: 'QuestionID' and 'Answer', eg:
> > > QuestionID Answer
> > > --
> > > 100 Some text
> > > 101 31-Jul-07
> > > 102 More text
> > > etc.
> > > I want to position the Answer for a particular QuestionID in a
> > > TextBox, as follows:
> > > [Answer for QuestionId 100] [Answer for QuestionId 101]
> > > [ Answer for Question Id 102
> > > ]
> > > I need to be able to vary the position and size of each TextBox, which
> > > I'm not sure I can do if I embed them in a table/list.
> > > I know that I can pivot the data in the query before I fill the
> > > dataset, which would give me a single row with a column for each
> > > question. However I'm dealing with about 100 question id and I was
> > > hoping to avoid the complex pivot query etc (even using SQL Server
> > > 2005 Pivot function).
> > > Cheers,
> > >Canice.
> > >From what you have described, you may want to have the report broken
> > into columns. You should use a table control and then follow these
> > steps to limit the records in the table for column handling.
> > 1. In the 'Layout' view of BIDS, select the table.
> > 2. Right click the table and select 'Properties.'
> > 3. On the 'Groups' tab, select the 'Add...' button.
> > 4. Below 'Group on:' and 'Expression' enter the following:
> > '=Ceiling(RowNumber(Nothing)/18)'
> > 5. Select 'Page break at end'
> > 5. Select 'OK' and 'OK' again.
> > [NOTE: The format in step 4 is: =Ceiling(RowNumber(Nothing)/
> > NumberOfRowsPerColumn) ]
> > Then, to set the number of columns, you would do the following:
> > 1. In the 'Layout' view of BIDS, select the 'Report' tab at the top.
> > 2. Select 'Report Properties...' from the drop-down menu.
> > 3. Select the 'Layout' tab.
> > 4. Below 'Columns:' select the number of columns to split the report
> > into.
> > Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
> Hi Enrique,
> Thats for the reply but I don't think your solution is what I was
> looking for.
> >From my (very basic) knowledge of SSRS, your solution will wrap the
> DataSet returned into a number of columns/rows on multiple pages.
> What I was looking for is a more free-form layout, where I can place
> TextBox fields for specific answers anywhere on the form (this is for
> a print layout). The expression for each TextBox would be as follows:
> =IIf(Fields!QuestionId.Value = 42231, Fields!Answer.Value, "")
> Where the value 42231 represents a particular answer, and each TextBox
> would have a different question id.
> I also need all the results on a single page, again so that I can use
> it for printing.
> Thanks,
> Canice.
Unfortunately, this type of layout is not supported in SSRS. Aside
from the text boxes being individually placed (as you suggested) and
using expression references to the same or different datasets, there
are not very many options available. In order to make the expressions
work that you mentioned above, you will need to use aggregates (i.e.,
=IIf(Max(Fields!QuestionId.Value) = 42231, Fields!Answer.Value, "")).
Another option, but still not free form, is to use a table control and
play w/the standard expressions and border styles based on expressions
to give the appearance of free form. Of course, this can be a bit
tricky and will not completely fit your purpose. The closest thing I
can think of to your desired outcome (if PDF export is an option), is
an open source library alternative (iTextSharp) which has some good
documentation (http://sourceforge.net/projects/itextsharp/
http://itextsharp.sourceforge.net/tutorial/ ); however, you will need
to be comfortable w/C# or VB.NET and use the library as part of a
custom application. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Aug 2, 12:16 am, EMartinez <emartinez...@.gmail.com> wrote:
> On Aug 1, 9:22 am, Canice <canice.b...@.crestsolutions.ie> wrote:
>
> > On Aug 1, 1:47 am, EMartinez <emartinez...@.gmail.com> wrote:
> > > On Jul 31, 9:19 am,Canice<canice.b...@.crestsolutions.ie> wrote:
> > > > I'm trying to layout a report with the data from specific rows placed
> > > > in TextBoxes, as I want to use the report for printing out details.
> > > > My DataSet has 2 colums: 'QuestionID' and 'Answer', eg:
> > > > QuestionID Answer
> > > > --
> > > > 100 Some text
> > > > 101 31-Jul-07
> > > > 102 More text
> > > > etc.
> > > > I want to position the Answer for a particular QuestionID in a
> > > > TextBox, as follows:
> > > > [Answer for QuestionId 100] [Answer for QuestionId 101]
> > > > [ Answer for Question Id 102
> > > > ]
> > > > I need to be able to vary the position and size of each TextBox, which
> > > > I'm not sure I can do if I embed them in a table/list.
> > > > I know that I can pivot the data in the query before I fill the
> > > > dataset, which would give me a single row with a column for each
> > > > question. However I'm dealing with about 100 question id and I was
> > > > hoping to avoid the complex pivot query etc (even using SQL Server
> > > > 2005 Pivot function).
> > > > Cheers,
> > > >Canice.
> > > >From what you have described, you may want to have the report broken
> > > into columns. You should use a table control and then follow these
> > > steps to limit the records in the table for column handling.
> > > 1. In the 'Layout' view of BIDS, select the table.
> > > 2. Right click the table and select 'Properties.'
> > > 3. On the 'Groups' tab, select the 'Add...' button.
> > > 4. Below 'Group on:' and 'Expression' enter the following:
> > > '=Ceiling(RowNumber(Nothing)/18)'
> > > 5. Select 'Page break at end'
> > > 5. Select 'OK' and 'OK' again.
> > > [NOTE: The format in step 4 is: =Ceiling(RowNumber(Nothing)/
> > > NumberOfRowsPerColumn) ]
> > > Then, to set the number of columns, you would do the following:
> > > 1. In the 'Layout' view of BIDS, select the 'Report' tab at the top.
> > > 2. Select 'Report Properties...' from the drop-down menu.
> > > 3. Select the 'Layout' tab.
> > > 4. Below 'Columns:' select the number of columns to split the report
> > > into.
> > > Hope this helps.
> > > Regards,
> > > Enrique Martinez
> > > Sr. Software Consultant
> > Hi Enrique,
> > Thats for the reply but I don't think your solution is what I was
> > looking for.
> > >From my (very basic) knowledge of SSRS, your solution will wrap the
> > DataSet returned into a number of columns/rows on multiple pages.
> > What I was looking for is a more free-form layout, where I can place
> > TextBox fields for specific answers anywhere on the form (this is for
> > a print layout). The expression for each TextBox would be as follows:
> > =IIf(Fields!QuestionId.Value = 42231, Fields!Answer.Value, "")
> > Where the value 42231 represents a particular answer, and each TextBox
> > would have a different question id.
> > I also need all the results on a single page, again so that I can use
> > it for printing.
> > Thanks,
> > Canice.
> Unfortunately, this type of layout is not supported in SSRS. Aside
> from the text boxes being individually placed (as you suggested) and
> using expression references to the same or different datasets, there
> are not very many options available. In order to make the expressions
> work that you mentioned above, you will need to use aggregates (i.e.,
> =IIf(Max(Fields!QuestionId.Value) = 42231, Fields!Answer.Value, "")).
> Another option, but still not free form, is to use a table control and
> play w/the standard expressions and border styles based on expressions
> to give the appearance of free form. Of course, this can be a bit
> tricky and will not completely fit your purpose. The closest thing I
> can think of to your desired outcome (if PDF export is an option), is
> an open source library alternative (iTextSharp) which has some good
> documentation (http://sourceforge.net/projects/itextsharp/http://itextsharp.sourceforge.net/tutorial/); however, you will need
> to be comfortable w/C# or VB.NET and use the library as part of a
> custom application. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
Thanks again Enrique, at least you've eliminated that possibility.
I'm a C# developer so using the iTextSharp API shouldn't be a problem,
however I was hoping to avoid going down the PDF route from past
experiences.
Looks like I might have to do the pivot operation after all, or use
ASP.NET to do the layout (its from a web-app).
Cheers,
Canice.|||On Aug 2, 4:06 am, Canice <canice.b...@.crestsolutions.ie> wrote:
> On Aug 2, 12:16 am, EMartinez <emartinez...@.gmail.com> wrote:
>
> > On Aug 1, 9:22 am, Canice <canice.b...@.crestsolutions.ie> wrote:
> > > On Aug 1, 1:47 am, EMartinez <emartinez...@.gmail.com> wrote:
> > > > On Jul 31, 9:19 am,Canice<canice.b...@.crestsolutions.ie> wrote:
> > > > > I'm trying to layout a report with the data from specific rows placed
> > > > > in TextBoxes, as I want to use the report for printing out details.
> > > > > My DataSet has 2 colums: 'QuestionID' and 'Answer', eg:
> > > > > QuestionID Answer
> > > > > --
> > > > > 100 Some text
> > > > > 101 31-Jul-07
> > > > > 102 More text
> > > > > etc.
> > > > > I want to position the Answer for a particular QuestionID in a
> > > > > TextBox, as follows:
> > > > > [Answer for QuestionId 100] [Answer for QuestionId 101]
> > > > > [ Answer for Question Id 102
> > > > > ]
> > > > > I need to be able to vary the position and size of each TextBox, which
> > > > > I'm not sure I can do if I embed them in a table/list.
> > > > > I know that I can pivot the data in the query before I fill the
> > > > > dataset, which would give me a single row with a column for each
> > > > > question. However I'm dealing with about 100 question id and I was
> > > > > hoping to avoid the complex pivot query etc (even using SQL Server
> > > > > 2005 Pivot function).
> > > > > Cheers,
> > > > >Canice.
> > > > >From what you have described, you may want to have the report broken
> > > > into columns. You should use a table control and then follow these
> > > > steps to limit the records in the table for column handling.
> > > > 1. In the 'Layout' view of BIDS, select the table.
> > > > 2. Right click the table and select 'Properties.'
> > > > 3. On the 'Groups' tab, select the 'Add...' button.
> > > > 4. Below 'Group on:' and 'Expression' enter the following:
> > > > '=Ceiling(RowNumber(Nothing)/18)'
> > > > 5. Select 'Page break at end'
> > > > 5. Select 'OK' and 'OK' again.
> > > > [NOTE: The format in step 4 is: =Ceiling(RowNumber(Nothing)/
> > > > NumberOfRowsPerColumn) ]
> > > > Then, to set the number of columns, you would do the following:
> > > > 1. In the 'Layout' view of BIDS, select the 'Report' tab at the top.
> > > > 2. Select 'Report Properties...' from the drop-down menu.
> > > > 3. Select the 'Layout' tab.
> > > > 4. Below 'Columns:' select the number of columns to split the report
> > > > into.
> > > > Hope this helps.
> > > > Regards,
> > > > Enrique Martinez
> > > > Sr. Software Consultant
> > > Hi Enrique,
> > > Thats for the reply but I don't think your solution is what I was
> > > looking for.
> > > >From my (very basic) knowledge of SSRS, your solution will wrap the
> > > DataSet returned into a number of columns/rows on multiple pages.
> > > What I was looking for is a more free-form layout, where I can place
> > > TextBox fields for specific answers anywhere on the form (this is for
> > > a print layout). The expression for each TextBox would be as follows:
> > > =IIf(Fields!QuestionId.Value = 42231, Fields!Answer.Value, "")
> > > Where the value 42231 represents a particular answer, and each TextBox
> > > would have a different question id.
> > > I also need all the results on a single page, again so that I can use
> > > it for printing.
> > > Thanks,
> > > Canice.
> > Unfortunately, this type of layout is not supported in SSRS. Aside
> > from the text boxes being individually placed (as you suggested) and
> > using expression references to the same or different datasets, there
> > are not very many options available. In order to make the expressions
> > work that you mentioned above, you will need to use aggregates (i.e.,
> > =IIf(Max(Fields!QuestionId.Value) = 42231, Fields!Answer.Value, "")).
> > Another option, but still not free form, is to use a table control and
> > play w/the standard expressions and border styles based on expressions
> > to give the appearance of free form. Of course, this can be a bit
> > tricky and will not completely fit your purpose. The closest thing I
> > can think of to your desired outcome (if PDF export is an option), is
> > an open source library alternative (iTextSharp) which has some good
> > documentation (http://sourceforge.net/projects/itextsharp/http://itextsharp.sourcefo...however, you will need
> > to be comfortable w/C# or VB.NET and use the library as part of a
> > custom application. Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
> Thanks again Enrique, at least you've eliminated that possibility.
> I'm a C# developer so using the iTextSharp API shouldn't be a problem,
> however I was hoping to avoid going down the PDF route from past
> experiences.
> Looks like I might have to do the pivot operation after all, or use
> ASP.NET to do the layout (its from a web-app).
> Cheers,
> Canice.
You're welcome. Sorry that I could not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant