Estimeted Time for SP

Hi,
How can i know estimated time taken by a SP before actual execution ?
thankscheck from the Execution Plan.
best Regards,
Chandra
---
"DMP" wrote:

> Hi,
> How can i know estimated time taken by a SP before actual execution ?
> thanks
>
>|||Sorry , I think you don't understand my requirement.
Basically,I am calling a SP from VB and showing a progress bar till SP is
running.
How can i control progessBar.Value till SP running ?
Any Idea to increase the ProgressBar symetrically ......
thanks,
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:C44599AA-74D3-40B8-BC59-3F0C44B140C8@.microsoft.com...
> check from the Execution Plan.
>
> --
> best Regards,
> Chandra
> ---
>
> "DMP" wrote:
>|||Sorry,Ithink you don't understand my requirement.
Basically, I am calling a SP from VB and showing a progressbar till SP is
running.
How can i control to increase the progressbar value symetrically ?
Any idea ?
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:C44599AA-74D3-40B8-BC59-3F0C44B140C8@.microsoft.com...
> check from the Execution Plan.
>
> --
> best Regards,
> Chandra
> ---
>
> "DMP" wrote:
>|||DMP wrote:
> Hi,
> How can i know estimated time taken by a SP before actual execution ?
> thanks
There is no way to know how long a stored procedure (or any query) is
going to take to execute, short of performing some tests. In order to
display a progress bar, you'll need to use async execution. If it's a
short query, you're better off not worrying about the progress bar
(whether you use an async call or not). If your using .Net, then an
async call is not possible. You'll have to run it on another thread if
you don't want to tie up the main application thread during execution.
If you're fetching rows, you can provide some feedback on screen while
the fetching is taking place since by that time control has returned to
the application.
David Gugick
Imceda Software
www.imceda.com|||Hi
I don't think this is possible. What you can do is gather information about
how long it will normally take and then use that time as a guideline. If thi
s
is taking a long time you may think of changing the method of implementation
.
You may be able to split the process into smaller units or use a different
delivery mechanism that does not require the user to watch a progress bar
tick away!
John
"DMP" wrote:

> Sorry,Ithink you don't understand my requirement.
> Basically, I am calling a SP from VB and showing a progressbar till SP is
> running.
> How can i control to increase the progressbar value symetrically ?
> Any idea ?
>
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:C44599AA-74D3-40B8-BC59-3F0C44B140C8@.microsoft.com...
>
>|||Basically I call a SP from VB and showing a ProgressBar till SP execution.
How can I increase the progressbar symetrically ?
That is why I needed the sp execution time.
Or any good sugg.
Thanks,
"DMP" <debdulal.mahapatra@.fi-tek.co.in> wrote in message
news:O0BsDvfUFHA.3624@.TK2MSFTNGP10.phx.gbl...
> Hi,
> How can i know estimated time taken by a SP before actual execution ?
> thanks
>|||Although it doesn't give you percentage completion in time, you could track
where a proc is in its execution by having it write to a table at certain
points in the code. If you run it async as other people have pointed out,
then another thread could query your "progress" table and determine how ar
along the first proc is.
Pretty kludgy but might be what you want.
"DMP" wrote:

> Basically I call a SP from VB and showing a ProgressBar till SP execution.
> How can I increase the progressbar symetrically ?
> That is why I needed the sp execution time.
> Or any good sugg.
> Thanks,
> "DMP" <debdulal.mahapatra@.fi-tek.co.in> wrote in message
> news:O0BsDvfUFHA.3624@.TK2MSFTNGP10.phx.gbl...
>
>

Estimated Execution Time

Hi,
Is it possible to know the Estimated Execution Time of a stored procedure
before actual execution from VB/SQL Server Application ?DMP wrote:
> Hi,
> Is it possible to know the Estimated Execution Time of a stored
> procedure before actual execution from VB/SQL Server Application ?
Sure. From Query Analyzer, enter the execution text for the procedure
with the parameters you want and use the Query | Display Estimated
Execution Plan (CTRL + L). You can also use SET SHOWPLAN_ALL ON / OFF in
batches before the procedure to see a textual execution plan.
David Gugick
Imceda Software
www.imceda.com|||Yes of course, just look at this example:
SET SHOWPLAN_ALL ON
GO
Select sc.name,st.name,st.length from syscolumns sc
Inner join sysobjects so on
sc.id = so.id
Inner join systypes st on
sc.xtype = st.xtype
Where so.Xtype = 'U' And
so.name like 'Orders'
GO
SET SHOWPLAN_ALL OFF
GO
SET SHOWPLAN_TEXT ON
GO
Select sc.name,st.name,st.length from syscolumns sc
Inner join sysobjects so on
sc.id = so.id
Inner join systypes st on
sc.xtype = st.xtype
Where so.Xtype = 'U' And
so.name like 'Orders'
GO
SET SHOWPLAN_TEXT OFF
HTH, Jens Smeyer.
"DMP" <debdulal.mahapatra@.fi-tek.co.in> schrieb im Newsbeitrag
news:O2U$n5%23PFHA.244@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is it possible to know the Estimated Execution Time of a stored procedure
> before actual execution from VB/SQL Server Application ?
>

Estimated Execution Plan-Diff betw Seek Predicate and Predicate?

Hi experts,
I am looking at an estimated execution plan for a query that joins between 2
significantly big tables. One of the Clustered Index Seek uses has both
Predicate and Seek Predicate. What are the differences between Seek Predicate
and Predicate?
In detail, this is what I have done:
...
INNER JOIN BonusPromotion BP ON
...
AND BP.status = 'A'--Active
AND BP.validStartDate >= @.tlBeginDate AND BP.endDate <= @.tlEndDate
...
The index used is the primary key along fields.
endDate,
validStartDate,
storeID,
emEAN,
bpID
Seek predicate is along field endDate, while Predicate is along field
validStartDate. My impression of Seek predicate is very fast search
performance. So why can't the Seek predicate include BOTH endDate and
validStartDate? Therefore back to my 1st question: What are the differences
between Seek Predicate and Predicate?
Hi
What are the differences between Seek Predicate
> and Predicate?
http://blogs.msdn.com/craigfr/archive/2006/07/07/652668.aspx
..
"HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
news:4AC93D3A-69C0-4BD2-9CBD-2EEB86714040@.microsoft.com...
> Hi experts,
> I am looking at an estimated execution plan for a query that joins between
> 2
> significantly big tables. One of the Clustered Index Seek uses has both
> Predicate and Seek Predicate. What are the differences between Seek
> Predicate
> and Predicate?
> In detail, this is what I have done:
> ...
> INNER JOIN BonusPromotion BP ON
> ...
> AND BP.status = 'A' --Active
> AND BP.validStartDate >= @.tlBeginDate AND BP.endDate <= @.tlEndDate
> ...
> The index used is the primary key along fields.
> endDate,
> validStartDate,
> storeID,
> emEAN,
> bpID
> Seek predicate is along field endDate, while Predicate is along field
> validStartDate. My impression of Seek predicate is very fast search
> performance. So why can't the Seek predicate include BOTH endDate and
> validStartDate? Therefore back to my 1st question: What are the
> differences
> between Seek Predicate and Predicate?
|||HardKhor,
BP.endDate will be the only seek predicate, because you are not
searching for an exact match for that column, but for a range of values.
This means the other predicates will be evaluated by scanning the index
pages in this range of endDate.
Theoretically, it could still seek the next index column (in your case
validStartDate), but SQL Server does not support this. And even if it
did, the multiple (looped) seeks would only be useful if the preceding
column (in your case endDate) has a very low selectivity, and the index
is very shallow. Otherwise the cost of the multiple seeks would exceed
the cost of scanning the index pages.
HTH,
Gert-Jan
HardKhor wrote:
> Hi experts,
> I am looking at an estimated execution plan for a query that joins between 2
> significantly big tables. One of the Clustered Index Seek uses has both
> Predicate and Seek Predicate. What are the differences between Seek Predicate
> and Predicate?
> In detail, this is what I have done:
> ...
> INNER JOIN BonusPromotion BP ON
> ...
> AND BP.status = 'A' --Active
> AND BP.validStartDate >= @.tlBeginDate AND BP.endDate <= @.tlEndDate
> ...
> The index used is the primary key along fields.
> endDate,
> validStartDate,
> storeID,
> emEAN,
> bpID
> Seek predicate is along field endDate, while Predicate is along field
> validStartDate. My impression of Seek predicate is very fast search
> performance. So why can't the Seek predicate include BOTH endDate and
> validStartDate? Therefore back to my 1st question: What are the differences
> between Seek Predicate and Predicate?
sql

Estimated Execution Plan-Diff betw Seek Predicate and Predicate?

Hi experts,
I am looking at an estimated execution plan for a query that joins between 2
significantly big tables. One of the Clustered Index Seek uses has both
Predicate and Seek Predicate. What are the differences between Seek Predicate
and Predicate?
In detail, this is what I have done:
...
INNER JOIN BonusPromotion BP ON
...
AND BP.status = 'A' --Active
AND BP.validStartDate >= @.tlBeginDate AND BP.endDate <= @.tlEndDate
...
The index used is the primary key along fields.
endDate,
validStartDate,
storeID,
emEAN,
bpID
Seek predicate is along field endDate, while Predicate is along field
validStartDate. My impression of Seek predicate is very fast search
performance. So why can't the Seek predicate include BOTH endDate and
validStartDate? Therefore back to my 1st question: What are the differences
between Seek Predicate and Predicate?Hi
What are the differences between Seek Predicate
> and Predicate?
http://blogs.msdn.com/craigfr/archive/2006/07/07/652668.aspx
.
"HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
news:4AC93D3A-69C0-4BD2-9CBD-2EEB86714040@.microsoft.com...
> Hi experts,
> I am looking at an estimated execution plan for a query that joins between
> 2
> significantly big tables. One of the Clustered Index Seek uses has both
> Predicate and Seek Predicate. What are the differences between Seek
> Predicate
> and Predicate?
> In detail, this is what I have done:
> ...
> INNER JOIN BonusPromotion BP ON
> ...
> AND BP.status = 'A' --Active
> AND BP.validStartDate >= @.tlBeginDate AND BP.endDate <= @.tlEndDate
> ...
> The index used is the primary key along fields.
> endDate,
> validStartDate,
> storeID,
> emEAN,
> bpID
> Seek predicate is along field endDate, while Predicate is along field
> validStartDate. My impression of Seek predicate is very fast search
> performance. So why can't the Seek predicate include BOTH endDate and
> validStartDate? Therefore back to my 1st question: What are the
> differences
> between Seek Predicate and Predicate?|||HardKhor,
BP.endDate will be the only seek predicate, because you are not
searching for an exact match for that column, but for a range of values.
This means the other predicates will be evaluated by scanning the index
pages in this range of endDate.
Theoretically, it could still seek the next index column (in your case
validStartDate), but SQL Server does not support this. And even if it
did, the multiple (looped) seeks would only be useful if the preceding
column (in your case endDate) has a very low selectivity, and the index
is very shallow. Otherwise the cost of the multiple seeks would exceed
the cost of scanning the index pages.
HTH,
Gert-Jan
HardKhor wrote:
> Hi experts,
> I am looking at an estimated execution plan for a query that joins between 2
> significantly big tables. One of the Clustered Index Seek uses has both
> Predicate and Seek Predicate. What are the differences between Seek Predicate
> and Predicate?
> In detail, this is what I have done:
> ...
> INNER JOIN BonusPromotion BP ON
> ...
> AND BP.status = 'A' --Active
> AND BP.validStartDate >= @.tlBeginDate AND BP.endDate <= @.tlEndDate
> ...
> The index used is the primary key along fields.
> endDate,
> validStartDate,
> storeID,
> emEAN,
> bpID
> Seek predicate is along field endDate, while Predicate is along field
> validStartDate. My impression of Seek predicate is very fast search
> performance. So why can't the Seek predicate include BOTH endDate and
> validStartDate? Therefore back to my 1st question: What are the differences
> between Seek Predicate and Predicate?

Estimated Execution Plan-Diff betw Seek Predicate and Predicate?

Hi experts,
I am looking at an estimated execution plan for a query that joins between 2
significantly big tables. One of the Clustered Index Seek uses has both
Predicate and Seek Predicate. What are the differences between Seek Predicat
e
and Predicate?
In detail, this is what I have done:
...
INNER JOIN BonusPromotion BP ON
...
AND BP.status = 'A' --Active
AND BP.validStartDate >= @.tlBeginDate AND BP.endDate <= @.tlEndDate
...
The index used is the primary key along fields.
endDate,
validStartDate,
storeID,
emEAN,
bpID
Seek predicate is along field endDate, while Predicate is along field
validStartDate. My impression of Seek predicate is very fast search
performance. So why can't the Seek predicate include BOTH endDate and
validStartDate? Therefore back to my 1st question: What are the differences
between Seek Predicate and Predicate?Hi
What are the differences between Seek Predicate
> and Predicate?
http://blogs.msdn.com/craigfr/archi.../07/652668.aspx
.
"HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
news:4AC93D3A-69C0-4BD2-9CBD-2EEB86714040@.microsoft.com...
> Hi experts,
> I am looking at an estimated execution plan for a query that joins between
> 2
> significantly big tables. One of the Clustered Index Seek uses has both
> Predicate and Seek Predicate. What are the differences between Seek
> Predicate
> and Predicate?
> In detail, this is what I have done:
> ...
> INNER JOIN BonusPromotion BP ON
> ...
> AND BP.status = 'A' --Active
> AND BP.validStartDate >= @.tlBeginDate AND BP.endDate <= @.tlEndDate
> ...
> The index used is the primary key along fields.
> endDate,
> validStartDate,
> storeID,
> emEAN,
> bpID
> Seek predicate is along field endDate, while Predicate is along field
> validStartDate. My impression of Seek predicate is very fast search
> performance. So why can't the Seek predicate include BOTH endDate and
> validStartDate? Therefore back to my 1st question: What are the
> differences
> between Seek Predicate and Predicate?|||HardKhor,
BP.endDate will be the only seek predicate, because you are not
searching for an exact match for that column, but for a range of values.
This means the other predicates will be evaluated by scanning the index
pages in this range of endDate.
Theoretically, it could still seek the next index column (in your case
validStartDate), but SQL Server does not support this. And even if it
did, the multiple (looped) seeks would only be useful if the preceding
column (in your case endDate) has a very low selectivity, and the index
is very shallow. Otherwise the cost of the multiple seeks would exceed
the cost of scanning the index pages.
HTH,
Gert-Jan
HardKhor wrote:
> Hi experts,
> I am looking at an estimated execution plan for a query that joins between
2
> significantly big tables. One of the Clustered Index Seek uses has both
> Predicate and Seek Predicate. What are the differences between Seek Predic
ate
> and Predicate?
> In detail, this is what I have done:
> ...
> INNER JOIN BonusPromotion BP ON
> ...
> AND BP.status = 'A' --Active
> AND BP.validStartDate >= @.tlBeginDate AND BP.endDate <= @.tlEndDate
> ...
> The index used is the primary key along fields.
> endDate,
> validStartDate,
> storeID,
> emEAN,
> bpID
> Seek predicate is along field endDate, while Predicate is along field
> validStartDate. My impression of Seek predicate is very fast search
> performance. So why can't the Seek predicate include BOTH endDate and
> validStartDate? Therefore back to my 1st question: What are the difference
s
> between Seek Predicate and Predicate?

Estimated Execution Plan Fails, XML Error?

Good afternoon, all. Whenever I try to use Query | Display Estimated
Execution Plan in SQL Studio on any non-trivial query, I get this unhelpful
message:
Error processing execution plan results. The error message is:
There is an error in XML document (1, 501).
There is an unclosed literal string. Line 1, position 501.
The only suggestion I've heard of is to instal SQL SPs, but that didn't
help. This is SQL 2K5 running on Win2K3. I don't believe there's any
strangeness like odd characters in instance names, and all language settings
are default. I've seen this before on another server, but Google has nothing.
Any ideas? Thanks!
I think I heard that the XML used for this can not handle some unusual
object names, but at best that is a long shot.
Roy Harvey
Beacon Falls, CT
On Mon, 6 Aug 2007 13:32:02 -0700, JonOfAllTrades
<JonOfAllTrades@.discussions.microsoft.com> wrote:

> Good afternoon, all. Whenever I try to use Query | Display Estimated
>Execution Plan in SQL Studio on any non-trivial query, I get this unhelpful
>message:
>Error processing execution plan results. The error message is:
>There is an error in XML document (1, 501).
>There is an unclosed literal string. Line 1, position 501.
> The only suggestion I've heard of is to instal SQL SPs, but that didn't
>help. This is SQL 2K5 running on Win2K3. I don't believe there's any
>strangeness like odd characters in instance names, and all language settings
>are default. I've seen this before on another server, but Google has nothing.
> Any ideas? Thanks!

Estimated execution plan

I've been using the estimated execution plan feature in SQL 2005 and I see that the "cost %" is sometimes just wacko (e.g. 333% or 25173%).

That makes me wonder just how much I can count on the estimated subtree cost value for a given path in the plan. I relied on this value (subtree cost) in SQL 2000 and would occasionally see the same kinds of wacko "cost%". I was hoping that 2005 would clean this up.

I am aware that the calculations in the plan depend upon a representative database of data. I think I am very experienced using the estimated execution plan tool in SQL 2000. I'm just concerned that, with the new release, that perhaps I shouldn't trust it as much, especially after continuing to see the wierdness with the cost% displayed.

Does anyone have any reliable information about whether the estimated subtree cost is something I should rely upon, even if the cost% is screwy?

Thanks for any insight.

To clarify the original question:

Is the calculation for an estimated subtree cost calculated independently of the displayed "cost %" for an individual, graphically-displayed (logical | physical) operator in the estimated execution plan?

I would guess that it could be independent since the subtree cost should be: a percent of the overall procedure being estimated

whereas the "cost %" for an individual, graphically-displayed (logical | physical) operator is a percent of the estimated cost of the command to be executed.

On the other-hand, the same operator cost value could also be used in both calculations.

Probably only the person who wrote that code might know, so I will do some experimenting to see if the values equate between SQL versions. Also, I am overall more pleased with what I see in the 2005 estimated execution plan presentation so don't judge this as a strong complaint. The new version is more useful.

Estimated Execution Plan

How do I display this in Query Analyzer ?Hi,
Go to Query menu -- Click Show Execution plan.
After that execute the Query.
Thanks
Hari
SQL Server MVP
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%23blIBj5yEHA.3236@.TK2MSFTNGP15.phx.gbl...
> How do I display this in Query Analyzer ?
>|||To display the estimated execution plan, select Query --> Display Estimated
Execution Plan on the menu or press Ctrl-L.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%23blIBj5yEHA.3236@.TK2MSFTNGP15.phx.gbl...
> How do I display this in Query Analyzer ?
>|||On Tue, 16 Nov 2004 08:10:29 -0600, "Dan Guzman"
<guzmanda@.nospam-online.sbcglobal.net> wrote:
>To display the estimated execution plan, select Query --> Display Estimated
>Execution Plan on the menu or press Ctrl-L.
Or click the little icon.
Or SET SHOWPLAN_ALL ON or SET SHOWPLAN_TEXT ON.
J.sql

Estimated Execution Plan

How do I display this in Query Analyzer ?
Hi,
Go to Query menu -- Click Show Execution plan.
After that execute the Query.
Thanks
Hari
SQL Server MVP
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%23blIBj5yEHA.3236@.TK2MSFTNGP15.phx.gbl...
> How do I display this in Query Analyzer ?
>
|||To display the estimated execution plan, select Query --> Display Estimated
Execution Plan on the menu or press Ctrl-L.
Hope this helps.
Dan Guzman
SQL Server MVP
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%23blIBj5yEHA.3236@.TK2MSFTNGP15.phx.gbl...
> How do I display this in Query Analyzer ?
>
|||On Tue, 16 Nov 2004 08:10:29 -0600, "Dan Guzman"
<guzmanda@.nospam-online.sbcglobal.net> wrote:
>To display the estimated execution plan, select Query --> Display Estimated
>Execution Plan on the menu or press Ctrl-L.
Or click the little icon.
Or SET SHOWPLAN_ALL ON or SET SHOWPLAN_TEXT ON.
J.

Estimated Execution Plan

How do I display this in Query Analyzer ?Hi,
Go to Query menu -- Click Show Execution plan.
After that execute the Query.
Thanks
Hari
SQL Server MVP
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%23blIBj5yEHA.3236@.TK2MSFTNGP15.phx.gbl...
> How do I display this in Query Analyzer ?
>|||To display the estimated execution plan, select Query --> Display Estimated
Execution Plan on the menu or press Ctrl-L.
Hope this helps.
Dan Guzman
SQL Server MVP
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%23blIBj5yEHA.3236@.TK2MSFTNGP15.phx.gbl...
> How do I display this in Query Analyzer ?
>|||On Tue, 16 Nov 2004 08:10:29 -0600, "Dan Guzman"
<guzmanda@.nospam-online.sbcglobal.net> wrote:
>To display the estimated execution plan, select Query --> Display Estimated
>Execution Plan on the menu or press Ctrl-L.
Or click the little icon.
Or SET SHOWPLAN_ALL ON or SET SHOWPLAN_TEXT ON.
J.

errors in ssis package execution

i have sql server 2005 standard edition(9.0.3054) with sp2, My ssis packages
were initially working fine,After installing sql server 2008 ctp, all ssis
packages stopped executing and giving error "Could not create DTS.Application
because of error 0x80040154", also import export wizard in sql 2005 is not
working.
After that i uninstalled sql server 2008 ctp and integration
service of sql 2005, and again installed integrtaion service of sql 2005 but
still i am getting error in ssis package execution.Please suggest me a
solution asap , as it is on a production server.
Thanks
Rupesh Mondal.
I had already removed and reinstalled "ssis" but it didnt worked ok,and i
already had sp2 installed on my database server.
Thanks and Regards,
Rupesh Mondal.
"John Bell" wrote:

> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
> news:CF99A5D6-B6C6-4484-9BD2-B6237A1A3D05@.microsoft.com...
> Hi Rupesh
> Error Code 0x80040154 relates to class not registered, therefore it looks
> like a SSIS component has been corrupted. The easiest solution may be to
> remove and re-install SSIS or SQL Server. If you are not on SP2 then you may
> just get away with installing that.
> John
>
>

errors in ssis package execution

i have sql server 2005 standard edition(9.0.3054) with sp2, My ssis packages
were initially working fine,After installing sql server 2008 ctp, all ssis
packages stopped executing and giving error "Could not create DTS.Application
because of error 0x80040154", also import export wizard in sql 2005 is not
working.
After that i uninstalled sql server 2008 ctp and integration
service of sql 2005, and again installed integrtaion service of sql 2005 but
still i am getting error in ssis package execution.Please suggest me a
solution asap , as it is on a production server.
Thanks
Rupesh Mondal."Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
news:CF99A5D6-B6C6-4484-9BD2-B6237A1A3D05@.microsoft.com...
>i have sql server 2005 standard edition(9.0.3054) with sp2, My ssis
>packages
> were initially working fine,After installing sql server 2008 ctp, all ssis
> packages stopped executing and giving error "Could not create
> DTS.Application
> because of error 0x80040154", also import export wizard in sql 2005 is not
> working.
> After that i uninstalled sql server 2008 ctp and integration
> service of sql 2005, and again installed integrtaion service of sql 2005
> but
> still i am getting error in ssis package execution.Please suggest me a
> solution asap , as it is on a production server.
> Thanks
> Rupesh Mondal.
Hi Rupesh
Error Code 0x80040154 relates to class not registered, therefore it looks
like a SSIS component has been corrupted. The easiest solution may be to
remove and re-install SSIS or SQL Server. If you are not on SP2 then you may
just get away with installing that.
John|||I had already removed and reinstalled "ssis" but it didnt worked ok,and i
already had sp2 installed on my database server.
Thanks and Regards,
Rupesh Mondal.
"John Bell" wrote:
> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
> news:CF99A5D6-B6C6-4484-9BD2-B6237A1A3D05@.microsoft.com...
> >i have sql server 2005 standard edition(9.0.3054) with sp2, My ssis
> >packages
> > were initially working fine,After installing sql server 2008 ctp, all ssis
> > packages stopped executing and giving error "Could not create
> > DTS.Application
> > because of error 0x80040154", also import export wizard in sql 2005 is not
> > working.
> > After that i uninstalled sql server 2008 ctp and integration
> > service of sql 2005, and again installed integrtaion service of sql 2005
> > but
> > still i am getting error in ssis package execution.Please suggest me a
> > solution asap , as it is on a production server.
> >
> > Thanks
> > Rupesh Mondal.
> Hi Rupesh
> Error Code 0x80040154 relates to class not registered, therefore it looks
> like a SSIS component has been corrupted. The easiest solution may be to
> remove and re-install SSIS or SQL Server. If you are not on SP2 then you may
> just get away with installing that.
> John
>
>|||Hi
Have you re-installed .NET? Otherwise I would re-install from scratch.
Hopefully this is not a production server!
John
"Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
news:81A467AD-B56B-44FC-92C9-D4FA96019C87@.microsoft.com...
>I had already removed and reinstalled "ssis" but it didnt worked ok,and i
> already had sp2 installed on my database server.
> Thanks and Regards,
> Rupesh Mondal.
> "John Bell" wrote:
>> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
>> news:CF99A5D6-B6C6-4484-9BD2-B6237A1A3D05@.microsoft.com...
>> >i have sql server 2005 standard edition(9.0.3054) with sp2, My ssis
>> >packages
>> > were initially working fine,After installing sql server 2008 ctp, all
>> > ssis
>> > packages stopped executing and giving error "Could not create
>> > DTS.Application
>> > because of error 0x80040154", also import export wizard in sql 2005 is
>> > not
>> > working.
>> > After that i uninstalled sql server 2008 ctp and
>> > integration
>> > service of sql 2005, and again installed integrtaion service of sql
>> > 2005
>> > but
>> > still i am getting error in ssis package execution.Please suggest me a
>> > solution asap , as it is on a production server.
>> >
>> > Thanks
>> > Rupesh Mondal.
>> Hi Rupesh
>> Error Code 0x80040154 relates to class not registered, therefore it looks
>> like a SSIS component has been corrupted. The easiest solution may be to
>> remove and re-install SSIS or SQL Server. If you are not on SP2 then you
>> may
>> just get away with installing that.
>> John
>>

Errors during Maintenance Plan execution

I am getting error on several database when trying to use
the Maintenance Plan to backup all databses in our SQL
Server 2000 with Service Pack 3.
The following is the text generated from one of the
database in the log file. The other databases generate
the same error message
Log portion============================== [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070:
[Microsoft][ODBC SQL Server Driver][SQL Server]Database
state cannot be changed while other users are using the
database 'SQLCatalog'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
DATABASE statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]
sp_dboption command failed.
[18] Database SQLCatalog: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair
statement not processed. Database needs to be in single
user mode.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair
statement not processed. Database needs to be in single
user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
========================================
Any ideas on how to handle this? Is this a matter of
running the maintenance plan at a different time?You've checked the option to "Attempt to repair minor problems" for which
SQL Server tries to set the db in single user mode which will fail if you
have users in the database. Remove that option, if you do get a problem with
the database, you want to know about it and be there to make a conscious
decision of your actions at that stage.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jim" <jim.abel@.lmco.com> wrote in message
news:051501c3bdaf$b172e7d0$3101280a@.phx.gbl...
> I am getting error on several database when trying to use
> the Maintenance Plan to backup all databses in our SQL
> Server 2000 with Service Pack 3.
> The following is the text generated from one of the
> database in the log file. The other databases generate
> the same error message
> Log portion==============================> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Database
> state cannot be changed while other users are using the
> database 'SQLCatalog'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
> DATABASE statement failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]
> sp_dboption command failed.
> [18] Database SQLCatalog: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
> statement not processed. Database needs to be in single
> user mode.
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
> statement not processed. Database needs to be in single
> user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> ========================================> Any ideas on how to handle this? Is this a matter of
> running the maintenance plan at a different time?|||That helped. So now I have an error with the transaction
log backup portion of the Maintenace Plan.
Here is the erro
==========================================================Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe
failed. [SQLSTATE 42000] (Error 22029). The step failed.
===============================================
I can't find this in the error logs or the backup log so
I'm not sure what is happening
>--Original Message--
>You've checked the option to "Attempt to repair minor
problems" for which
>SQL Server tries to set the db in single user mode which
will fail if you
>have users in the database. Remove that option, if you
do get a problem with
>the database, you want to know about it and be there to
make a conscious
>decision of your actions at that stage.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Jim" <jim.abel@.lmco.com> wrote in message
>news:051501c3bdaf$b172e7d0$3101280a@.phx.gbl...
>> I am getting error on several database when trying to
use
>> the Maintenance Plan to backup all databses in our SQL
>> Server 2000 with Service Pack 3.
>> The following is the text generated from one of the
>> database in the log file. The other databases generate
>> the same error message
>> Log portion==============================>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Database
>> state cannot be changed while other users are using the
>> database 'SQLCatalog'
>> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
>> DATABASE statement failed.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
>> sp_dboption command failed.
>> [18] Database SQLCatalog: Check Data and Index
Linkage...
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
>> statement not processed. Database needs to be in single
>> user mode.
>> The following errors were found:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
>> statement not processed. Database needs to be in single
>> user mode.
>> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> ========================================>> Any ideas on how to handle this? Is this a matter of
>> running the maintenance plan at a different time?
>
>.
>|||Possibly the database is in simple recovery model. You will not find details
error info from looking at just the job. There's a GUI for this in the main
folder, but I prefer the text report file option that maint wiz has.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jim" <jim.abel@.lmco.com> wrote in message
news:10f501c3be74$0be078d0$a401280a@.phx.gbl...
> That helped. So now I have an error with the transaction
> log backup portion of the Maintenace Plan.
> Here is the erro
> ==========================================================> Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe
> failed. [SQLSTATE 42000] (Error 22029). The step failed.
> ===============================================> I can't find this in the error logs or the backup log so
> I'm not sure what is happening
> >--Original Message--
> >You've checked the option to "Attempt to repair minor
> problems" for which
> >SQL Server tries to set the db in single user mode which
> will fail if you
> >have users in the database. Remove that option, if you
> do get a problem with
> >the database, you want to know about it and be there to
> make a conscious
> >decision of your actions at that stage.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"Jim" <jim.abel@.lmco.com> wrote in message
> >news:051501c3bdaf$b172e7d0$3101280a@.phx.gbl...
> >> I am getting error on several database when trying to
> use
> >> the Maintenance Plan to backup all databses in our SQL
> >> Server 2000 with Service Pack 3.
> >> The following is the text generated from one of the
> >> database in the log file. The other databases generate
> >> the same error message
> >>
> >> Log portion==============================> >> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070:
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]Database
> >> state cannot be changed while other users are using the
> >> database 'SQLCatalog'
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
> >> DATABASE statement failed.
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]
> >> sp_dboption command failed.
> >> [18] Database SQLCatalog: Check Data and Index
> Linkage...
> >> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919:
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
> >> statement not processed. Database needs to be in single
> >> user mode.
> >>
> >> The following errors were found:
> >>
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
> >> statement not processed. Database needs to be in single
> >> user mode.
> >> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> >>
> >> ========================================> >>
> >> Any ideas on how to handle this? Is this a matter of
> >> running the maintenance plan at a different time?
> >
> >
> >.
> >|||Hello Jim,
First of all, thanks to Tibor for pointing you in the right direction.
Just as an additional information,
you can refer to the following article which explains how to t-shoot
General SQL Maint Wiz failures,
INF: Troubleshooting Database Maintenance Plan Failures
http://support.microsoft.com/default.aspx?scid=kb;en-us;288577
In case, you are doing Transaction log backups of one of the databases
which is set to
Simple Recovery Mode, you might want to refer to following article
explaining this scenario,
BUG: Expired Transaction Log Backups May Not Be Deleted by Maintenance Plan
http://support.microsoft.com/default.aspx?scid=kb;en-us;303292
Please let us know if these suggestions resolve your issue, if not , feel
free to post any further questions you have.
Thanks for posting to MSDN Managed Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Professional
Microsoft highly recommends to all of our customers that they visit the
http://www.microsoft.com/protect site and perform the three straightforward
steps listed to improve your computer?s security."
This posting is provided "AS IS" with no warranties, and confers no rights.
>Content-Class: urn:content-classes:message
>From: "Jim" <jim.abel@.lmco.com>
>Sender: "Jim" <jim.abel@.lmco.com>
>References: <051501c3bdaf$b172e7d0$3101280a@.phx.gbl>
<#iNW#wbvDHA.1872@.TK2MSFTNGP09.phx.gbl>
>Subject: Re: Errors during Maintenance Plan execution
>Date: Tue, 9 Dec 2003 08:46:52 -0800
>Lines: 71
>Message-ID: <10f501c3be74$0be078d0$a401280a@.phx.gbl>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="iso-8859-1"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>X-MIMEOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>thread-index: AcO+dAvglHDFmnxfSiyfAzlM01uGHQ==>Newsgroups: microsoft.public.sqlserver.server
>Path: cpmsftngxa07.phx.gbl
>Xref: cpmsftngxa07.phx.gbl microsoft.public.sqlserver.server:319894
>NNTP-Posting-Host: tk2msftngxa12.phx.gbl 10.40.1.164
>X-Tomcat-NG: microsoft.public.sqlserver.server
>That helped. So now I have an error with the transaction
>log backup portion of the Maintenace Plan.
>Here is the erro
>==========================================================>Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe
>failed. [SQLSTATE 42000] (Error 22029). The step failed.
>===============================================>I can't find this in the error logs or the backup log so
>I'm not sure what is happening
>>--Original Message--
>>You've checked the option to "Attempt to repair minor
>problems" for which
>>SQL Server tries to set the db in single user mode which
>will fail if you
>>have users in the database. Remove that option, if you
>do get a problem with
>>the database, you want to know about it and be there to
>make a conscious
>>decision of your actions at that stage.
>>--
>>Tibor Karaszi, SQL Server MVP
>>Archive at:
>>http://groups.google.com/groups?
>oi=djq&as_ugroup=microsoft.public.sqlserver
>>
>>"Jim" <jim.abel@.lmco.com> wrote in message
>>news:051501c3bdaf$b172e7d0$3101280a@.phx.gbl...
>> I am getting error on several database when trying to
>use
>> the Maintenance Plan to backup all databses in our SQL
>> Server 2000 with Service Pack 3.
>> The following is the text generated from one of the
>> database in the log file. The other databases generate
>> the same error message
>> Log portion==============================>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Database
>> state cannot be changed while other users are using the
>> database 'SQLCatalog'
>> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
>> DATABASE statement failed.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
>> sp_dboption command failed.
>> [18] Database SQLCatalog: Check Data and Index
>Linkage...
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
>> statement not processed. Database needs to be in single
>> user mode.
>> The following errors were found:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
>> statement not processed. Database needs to be in single
>> user mode.
>> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> ========================================>> Any ideas on how to handle this? Is this a matter of
>> running the maintenance plan at a different time?
>>
>>.
>|||All right guys are really helping me out. I looked up
the proble and it has come down to the following The
maintenace Plan says that it cannot do transaction log
backups on the master and the msdb databases.
My question now is how can I exclude those 2 DB's from
the Transaction log backup portion of the maintenance
Plan?
>--Original Message--
>
>Hello Jim,
>First of all, thanks to Tibor for pointing you in the
right direction.
>Just as an additional information,
>you can refer to the following article which explains
how to t-shoot
>General SQL Maint Wiz failures,
>INF: Troubleshooting Database Maintenance Plan Failures
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;288577
>In case, you are doing Transaction log backups of one of
the databases
>which is set to
>Simple Recovery Mode, you might want to refer to
following article
>explaining this scenario,
>BUG: Expired Transaction Log Backups May Not Be Deleted
by Maintenance Plan
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;303292
>Please let us know if these suggestions resolve your
issue, if not , feel
>free to post any further questions you have.
>Thanks for posting to MSDN Managed Newsgroup.
>Vikrant Dalwale
>Microsoft SQL Server Support Professional
>Microsoft highly recommends to all of our customers
that they visit the
>http://www.microsoft.com/protect site and perform the
three straightforward
>steps listed to improve your computer's security."
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>
>--
>>Content-Class: urn:content-classes:message
>>From: "Jim" <jim.abel@.lmco.com>
>>Sender: "Jim" <jim.abel@.lmco.com>
>>References: <051501c3bdaf$b172e7d0$3101280a@.phx.gbl>
><#iNW#wbvDHA.1872@.TK2MSFTNGP09.phx.gbl>
>>Subject: Re: Errors during Maintenance Plan execution
>>Date: Tue, 9 Dec 2003 08:46:52 -0800
>>Lines: 71
>>Message-ID: <10f501c3be74$0be078d0$a401280a@.phx.gbl>
>>MIME-Version: 1.0
>>Content-Type: text/plain;
>> charset="iso-8859-1"
>>Content-Transfer-Encoding: 7bit
>>X-Newsreader: Microsoft CDO for Windows 2000
>>X-MIMEOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>>thread-index: AcO+dAvglHDFmnxfSiyfAzlM01uGHQ==>>Newsgroups: microsoft.public.sqlserver.server
>>Path: cpmsftngxa07.phx.gbl
>>Xref: cpmsftngxa07.phx.gbl
microsoft.public.sqlserver.server:319894
>>NNTP-Posting-Host: tk2msftngxa12.phx.gbl 10.40.1.164
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>That helped. So now I have an error with the
transaction
>>log backup portion of the Maintenace Plan.
>>Here is the erro
>>==========================================================>>Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe
>>failed. [SQLSTATE 42000] (Error 22029). The step
failed.
>>===============================================>>I can't find this in the error logs or the backup log
so
>>I'm not sure what is happening
>>--Original Message--
>>You've checked the option to "Attempt to repair minor
>>problems" for which
>>SQL Server tries to set the db in single user mode
which
>>will fail if you
>>have users in the database. Remove that option, if you
>>do get a problem with
>>the database, you want to know about it and be there
to
>>make a conscious
>>decision of your actions at that stage.
>>--
>>Tibor Karaszi, SQL Server MVP
>>Archive at:
>>http://groups.google.com/groups?
>>oi=djq&as_ugroup=microsoft.public.sqlserver
>>
>>"Jim" <jim.abel@.lmco.com> wrote in message
>>news:051501c3bdaf$b172e7d0$3101280a@.phx.gbl...
>> I am getting error on several database when trying
to
>>use
>> the Maintenance Plan to backup all databses in our
SQL
>> Server 2000 with Service Pack 3.
>> The following is the text generated from one of the
>> database in the log file. The other databases
generate
>> the same error message
>> Log portion==============================>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error
5070:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
Database
>> state cannot be changed while other users are using
the
>> database 'SQLCatalog'
>> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
>> DATABASE statement failed.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
>> sp_dboption command failed.
>> [18] Database SQLCatalog: Check Data and Index
>>Linkage...
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error
7919:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
>> statement not processed. Database needs to be in
single
>> user mode.
>> The following errors were found:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
>> statement not processed. Database needs to be in
single
>> user mode.
>> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> ========================================>> Any ideas on how to handle this? Is this a matter of
>> running the maintenance plan at a different time?
>>
>>.
>>
>.
>|||Hello Jim,
You need to create a seperate Maintenance Plan for those DBs which need
both the Database and Transaction log backups and
a seperate one for master and msdb for only Database backups.
Does that answer your question ?
Thanks for using MSDN Managed Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Professional
Microsoft highly recommends to all of our customers that they visit the
http://www.microsoft.com/protect site and perform the three straightforward
steps listed to improve your computer?s security.
This posting is provided "AS IS" with no warranties, and confers no rights.
>Content-Class: urn:content-classes:message
>From: "Jim" <jim.abel@.lmco.com>
>Sender: "Jim" <jim.abel@.lmco.com>
>References: <051501c3bdaf$b172e7d0$3101280a@.phx.gbl>
<#iNW#wbvDHA.1872@.TK2MSFTNGP09.phx.gbl>
<10f501c3be74$0be078d0$a401280a@.phx.gbl>
<TChLAfovDHA.2520@.cpmsftngxa07.phx.gbl>
>Subject: Re: Errors during Maintenance Plan execution
>Date: Fri, 12 Dec 2003 08:27:51 -0800
>Lines: 171
>Message-ID: <05dc01c3c0cc$e343d240$a001280a@.phx.gbl>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="iso-8859-1"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>Thread-Index: AcPAzONDhi3qx6uXTfm9O5c0Z9Bk1A==>Newsgroups: microsoft.public.sqlserver.server
>Path: cpmsftngxa07.phx.gbl
>Xref: cpmsftngxa07.phx.gbl microsoft.public.sqlserver.server:320465
>NNTP-Posting-Host: tk2msftngxa08.phx.gbl 10.40.1.160
>X-Tomcat-NG: microsoft.public.sqlserver.server
>All right guys are really helping me out. I looked up
>the proble and it has come down to the following The
>maintenace Plan says that it cannot do transaction log
>backups on the master and the msdb databases.
>My question now is how can I exclude those 2 DB's from
>the Transaction log backup portion of the maintenance
>Plan?
>>--Original Message--
>>
>>Hello Jim,
>>First of all, thanks to Tibor for pointing you in the
>right direction.
>>Just as an additional information,
>>you can refer to the following article which explains
>how to t-shoot
>>General SQL Maint Wiz failures,
>>INF: Troubleshooting Database Maintenance Plan Failures
>>http://support.microsoft.com/default.aspx?scid=kb;en-
>us;288577
>>In case, you are doing Transaction log backups of one of
>the databases
>>which is set to
>>Simple Recovery Mode, you might want to refer to
>following article
>>explaining this scenario,
>>BUG: Expired Transaction Log Backups May Not Be Deleted
>by Maintenance Plan
>>http://support.microsoft.com/default.aspx?scid=kb;en-
>us;303292
>>Please let us know if these suggestions resolve your
>issue, if not , feel
>>free to post any further questions you have.
>>Thanks for posting to MSDN Managed Newsgroup.
>>Vikrant Dalwale
>>Microsoft SQL Server Support Professional
>>Microsoft highly recommends to all of our customers
>that they visit the
>>http://www.microsoft.com/protect site and perform the
>three straightforward
>>steps listed to improve your computer's security."
>>This posting is provided "AS IS" with no warranties, and
>confers no rights.
>>
>>--
>>Content-Class: urn:content-classes:message
>>From: "Jim" <jim.abel@.lmco.com>
>>Sender: "Jim" <jim.abel@.lmco.com>
>>References: <051501c3bdaf$b172e7d0$3101280a@.phx.gbl>
>><#iNW#wbvDHA.1872@.TK2MSFTNGP09.phx.gbl>
>>Subject: Re: Errors during Maintenance Plan execution
>>Date: Tue, 9 Dec 2003 08:46:52 -0800
>>Lines: 71
>>Message-ID: <10f501c3be74$0be078d0$a401280a@.phx.gbl>
>>MIME-Version: 1.0
>>Content-Type: text/plain;
>> charset="iso-8859-1"
>>Content-Transfer-Encoding: 7bit
>>X-Newsreader: Microsoft CDO for Windows 2000
>>X-MIMEOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>>thread-index: AcO+dAvglHDFmnxfSiyfAzlM01uGHQ==>>Newsgroups: microsoft.public.sqlserver.server
>>Path: cpmsftngxa07.phx.gbl
>>Xref: cpmsftngxa07.phx.gbl
>microsoft.public.sqlserver.server:319894
>>NNTP-Posting-Host: tk2msftngxa12.phx.gbl 10.40.1.164
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>That helped. So now I have an error with the
>transaction
>>log backup portion of the Maintenace Plan.
>>Here is the erro
>>========================================================>==>>Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe
>>failed. [SQLSTATE 42000] (Error 22029). The step
>failed.
>>===============================================>>I can't find this in the error logs or the backup log
>so
>>I'm not sure what is happening
>>--Original Message--
>>You've checked the option to "Attempt to repair minor
>>problems" for which
>>SQL Server tries to set the db in single user mode
>which
>>will fail if you
>>have users in the database. Remove that option, if you
>>do get a problem with
>>the database, you want to know about it and be there
>to
>>make a conscious
>>decision of your actions at that stage.
>>--
>>Tibor Karaszi, SQL Server MVP
>>Archive at:
>>http://groups.google.com/groups?
>>oi=djq&as_ugroup=microsoft.public.sqlserver
>>
>>"Jim" <jim.abel@.lmco.com> wrote in message
>>news:051501c3bdaf$b172e7d0$3101280a@.phx.gbl...
>> I am getting error on several database when trying
>to
>>use
>> the Maintenance Plan to backup all databses in our
>SQL
>> Server 2000 with Service Pack 3.
>> The following is the text generated from one of the
>> database in the log file. The other databases
>generate
>> the same error message
>> Log portion==============================>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error
>5070:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
>Database
>> state cannot be changed while other users are using
>the
>> database 'SQLCatalog'
>> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
>> DATABASE statement failed.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
>> sp_dboption command failed.
>> [18] Database SQLCatalog: Check Data and Index
>>Linkage...
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error
>7919:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
>> statement not processed. Database needs to be in
>single
>> user mode.
>> The following errors were found:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
>> statement not processed. Database needs to be in
>single
>> user mode.
>> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> ========================================>> Any ideas on how to handle this? Is this a matter of
>> running the maintenance plan at a different time?
>>
>>.
>>
>>.
>|||Thank you Vikrant
That answers all of my questions.
I appreciate all of the help.
Happy Holidays
>--Original Message--
>Hello Jim,
>You need to create a seperate Maintenance Plan for
those DBs which need
>both the Database and Transaction log backups and
> a seperate one for master and msdb for only Database
backups.
>Does that answer your question ?
>Thanks for using MSDN Managed Newsgroup.
>Vikrant Dalwale
>Microsoft SQL Server Support Professional
>
>Microsoft highly recommends to all of our customers
that they visit the
>http://www.microsoft.com/protect site and perform the
three straightforward
>steps listed to improve your computer's security.
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>
>--
>>Content-Class: urn:content-classes:message
>>From: "Jim" <jim.abel@.lmco.com>
>>Sender: "Jim" <jim.abel@.lmco.com>
>>References: <051501c3bdaf$b172e7d0$3101280a@.phx.gbl>
><#iNW#wbvDHA.1872@.TK2MSFTNGP09.phx.gbl>
><10f501c3be74$0be078d0$a401280a@.phx.gbl>
><TChLAfovDHA.2520@.cpmsftngxa07.phx.gbl>
>>Subject: Re: Errors during Maintenance Plan execution
>>Date: Fri, 12 Dec 2003 08:27:51 -0800
>>Lines: 171
>>Message-ID: <05dc01c3c0cc$e343d240$a001280a@.phx.gbl>
>>MIME-Version: 1.0
>>Content-Type: text/plain;
>> charset="iso-8859-1"
>>Content-Transfer-Encoding: 7bit
>>X-Newsreader: Microsoft CDO for Windows 2000
>>X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>>Thread-Index: AcPAzONDhi3qx6uXTfm9O5c0Z9Bk1A==>>Newsgroups: microsoft.public.sqlserver.server
>>Path: cpmsftngxa07.phx.gbl
>>Xref: cpmsftngxa07.phx.gbl
microsoft.public.sqlserver.server:320465
>>NNTP-Posting-Host: tk2msftngxa08.phx.gbl 10.40.1.160
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>All right guys are really helping me out. I looked up
>>the proble and it has come down to the following The
>>maintenace Plan says that it cannot do transaction log
>>backups on the master and the msdb databases.
>>My question now is how can I exclude those 2 DB's from
>>the Transaction log backup portion of the maintenance
>>Plan?
>>--Original Message--
>>
>>Hello Jim,
>>First of all, thanks to Tibor for pointing you in the
>>right direction.
>>Just as an additional information,
>>you can refer to the following article which explains
>>how to t-shoot
>>General SQL Maint Wiz failures,
>>INF: Troubleshooting Database Maintenance Plan Failures
>>http://support.microsoft.com/default.aspx?scid=kb;en-
>>us;288577
>>In case, you are doing Transaction log backups of one
of
>>the databases
>>which is set to
>>Simple Recovery Mode, you might want to refer to
>>following article
>>explaining this scenario,
>>BUG: Expired Transaction Log Backups May Not Be
Deleted
>>by Maintenance Plan
>>http://support.microsoft.com/default.aspx?scid=kb;en-
>>us;303292
>>Please let us know if these suggestions resolve your
>>issue, if not , feel
>>free to post any further questions you have.
>>Thanks for posting to MSDN Managed Newsgroup.
>>Vikrant Dalwale
>>Microsoft SQL Server Support Professional
>>Microsoft highly recommends to all of our customers
>>that they visit the
>>http://www.microsoft.com/protect site and perform the
>>three straightforward
>>steps listed to improve your computer's security."
>>This posting is provided "AS IS" with no warranties,
and
>>confers no rights.
>>
>>--
>>Content-Class: urn:content-classes:message
>>From: "Jim" <jim.abel@.lmco.com>
>>Sender: "Jim" <jim.abel@.lmco.com>
>>References: <051501c3bdaf$b172e7d0$3101280a@.phx.gbl>
>><#iNW#wbvDHA.1872@.TK2MSFTNGP09.phx.gbl>
>>Subject: Re: Errors during Maintenance Plan execution
>>Date: Tue, 9 Dec 2003 08:46:52 -0800
>>Lines: 71
>>Message-ID: <10f501c3be74$0be078d0$a401280a@.phx.gbl>
>>MIME-Version: 1.0
>>Content-Type: text/plain;
>> charset="iso-8859-1"
>>Content-Transfer-Encoding: 7bit
>>X-Newsreader: Microsoft CDO for Windows 2000
>>X-MIMEOLE: Produced By Microsoft MimeOLE
V5.50.4910.0300
>>thread-index: AcO+dAvglHDFmnxfSiyfAzlM01uGHQ==>>Newsgroups: microsoft.public.sqlserver.server
>>Path: cpmsftngxa07.phx.gbl
>>Xref: cpmsftngxa07.phx.gbl
>>microsoft.public.sqlserver.server:319894
>>NNTP-Posting-Host: tk2msftngxa12.phx.gbl 10.40.1.164
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>That helped. So now I have an error with the
>>transaction
>>log backup portion of the Maintenace Plan.
>>Here is the erro
>>========================================================>>==>>Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe
>>failed. [SQLSTATE 42000] (Error 22029). The step
>>failed.
>>===============================================>>I can't find this in the error logs or the backup log
>>so
>>I'm not sure what is happening
>>--Original Message--
>>You've checked the option to "Attempt to repair
minor
>>problems" for which
>>SQL Server tries to set the db in single user mode
>>which
>>will fail if you
>>have users in the database. Remove that option, if
you
>>do get a problem with
>>the database, you want to know about it and be there
>>to
>>make a conscious
>>decision of your actions at that stage.
>>--
>>Tibor Karaszi, SQL Server MVP
>>Archive at:
>>http://groups.google.com/groups?
>>oi=djq&as_ugroup=microsoft.public.sqlserver
>>
>>"Jim" <jim.abel@.lmco.com> wrote in message
>>news:051501c3bdaf$b172e7d0$3101280a@.phx.gbl...
>> I am getting error on several database when trying
>>to
>>use
>> the Maintenance Plan to backup all databses in our
>>SQL
>> Server 2000 with Service Pack 3.
>> The following is the text generated from one of the
>> database in the log file. The other databases
>>generate
>> the same error message
>> Log portion==============================>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error
>>5070:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
>>Database
>> state cannot be changed while other users are
using
>>the
>> database 'SQLCatalog'
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
ALTER
>> DATABASE statement failed.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
>> sp_dboption command failed.
>> [18] Database SQLCatalog: Check Data and Index
>>Linkage...
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error
>>7919:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
Repair
>> statement not processed. Database needs to be in
>>single
>> user mode.
>> The following errors were found:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
Repair
>> statement not processed. Database needs to be in
>>single
>> user mode.
>> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> ========================================>> Any ideas on how to handle this? Is this a matter
of
>> running the maintenance plan at a different time?
>>
>>.
>>
>>.
>>
>.
>|||I have a similar problem so I verified that my database is
the only one in the maintenance plan (does not include
master or msdb). Yet my Transaction Log backup still
fails with the same message:
"The job failed. The Job was invoked by User U1ST074SORTS2
\rdenison. The last step to run was step 1 (Step 1)."
"sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The
step failed."
Any other ideas? I'm really interested in resolving this
because my Xaction log gets huge, especially on a
distributor server.
Roger.
>--Original Message--
>All right guys are really helping me out. I looked up
>the proble and it has come down to the following The
>maintenace Plan says that it cannot do transaction log
>backups on the master and the msdb databases.
>My question now is how can I exclude those 2 DB's from
>the Transaction log backup portion of the maintenance
>Plan?
>>--Original Message--
>>
>>Hello Jim,
>>First of all, thanks to Tibor for pointing you in the
>right direction.
>>Just as an additional information,
>>you can refer to the following article which explains
>how to t-shoot
>>General SQL Maint Wiz failures,
>>INF: Troubleshooting Database Maintenance Plan Failures
>>http://support.microsoft.com/default.aspx?scid=kb;en-
>us;288577
>>In case, you are doing Transaction log backups of one of
>the databases
>>which is set to
>>Simple Recovery Mode, you might want to refer to
>following article
>>explaining this scenario,
>>BUG: Expired Transaction Log Backups May Not Be Deleted
>by Maintenance Plan
>>http://support.microsoft.com/default.aspx?scid=kb;en-
>us;303292
>>Please let us know if these suggestions resolve your
>issue, if not , feel
>>free to post any further questions you have.
>>Thanks for posting to MSDN Managed Newsgroup.
>>Vikrant Dalwale
>>Microsoft SQL Server Support Professional
>>Microsoft highly recommends to all of our customers
>that they visit the
>>http://www.microsoft.com/protect site and perform the
>three straightforward
>>steps listed to improve your computer's security."
>>This posting is provided "AS IS" with no warranties, and
>confers no rights.
>>
>>--
>>Content-Class: urn:content-classes:message
>>From: "Jim" <jim.abel@.lmco.com>
>>Sender: "Jim" <jim.abel@.lmco.com>
>>References: <051501c3bdaf$b172e7d0$3101280a@.phx.gbl>
>><#iNW#wbvDHA.1872@.TK2MSFTNGP09.phx.gbl>
>>Subject: Re: Errors during Maintenance Plan execution
>>Date: Tue, 9 Dec 2003 08:46:52 -0800
>>Lines: 71
>>Message-ID: <10f501c3be74$0be078d0$a401280a@.phx.gbl>
>>MIME-Version: 1.0
>>Content-Type: text/plain;
>> charset="iso-8859-1"
>>Content-Transfer-Encoding: 7bit
>>X-Newsreader: Microsoft CDO for Windows 2000
>>X-MIMEOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>>thread-index: AcO+dAvglHDFmnxfSiyfAzlM01uGHQ==>>Newsgroups: microsoft.public.sqlserver.server
>>Path: cpmsftngxa07.phx.gbl
>>Xref: cpmsftngxa07.phx.gbl
>microsoft.public.sqlserver.server:319894
>>NNTP-Posting-Host: tk2msftngxa12.phx.gbl 10.40.1.164
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>That helped. So now I have an error with the
>transaction
>>log backup portion of the Maintenace Plan.
>>Here is the erro
>>========================================================>==>>Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe
>>failed. [SQLSTATE 42000] (Error 22029). The step
>failed.
>>===============================================>>I can't find this in the error logs or the backup log
>so
>>I'm not sure what is happening
>>--Original Message--
>>You've checked the option to "Attempt to repair minor
>>problems" for which
>>SQL Server tries to set the db in single user mode
>which
>>will fail if you
>>have users in the database. Remove that option, if you
>>do get a problem with
>>the database, you want to know about it and be there
>to
>>make a conscious
>>decision of your actions at that stage.
>>--
>>Tibor Karaszi, SQL Server MVP
>>Archive at:
>>http://groups.google.com/groups?
>>oi=djq&as_ugroup=microsoft.public.sqlserver
>>
>>"Jim" <jim.abel@.lmco.com> wrote in message
>>news:051501c3bdaf$b172e7d0$3101280a@.phx.gbl...
>> I am getting error on several database when trying
>to
>>use
>> the Maintenance Plan to backup all databses in our
>SQL
>> Server 2000 with Service Pack 3.
>> The following is the text generated from one of the
>> database in the log file. The other databases
>generate
>> the same error message
>> Log portion==============================>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error
>5070:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
>Database
>> state cannot be changed while other users are using
>the
>> database 'SQLCatalog'
>> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
>> DATABASE statement failed.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
>> sp_dboption command failed.
>> [18] Database SQLCatalog: Check Data and Index
>>Linkage...
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error
>7919:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
>> statement not processed. Database needs to be in
>single
>> user mode.
>> The following errors were found:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
>> statement not processed. Database needs to be in
>single
>> user mode.
>> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> ========================================>> Any ideas on how to handle this? Is this a matter of
>> running the maintenance plan at a different time?
>>
>>.
>>
>>.
>.
>|||Why not create your own job to do the backups and not rely on the MP at all?
Then you will know exactly what it is trying to do and when.
--
Andrew J. Kelly
SQL Server MVP
"Roger Denison" <anonymous@.discussions.microsoft.com> wrote in message
news:215001c3cfee$1f72d010$3101280a@.phx.gbl...
> I have a similar problem so I verified that my database is
> the only one in the maintenance plan (does not include
> master or msdb). Yet my Transaction Log backup still
> fails with the same message:
> "The job failed. The Job was invoked by User U1ST074SORTS2
> \rdenison. The last step to run was step 1 (Step 1)."
> "sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The
> step failed."
> Any other ideas? I'm really interested in resolving this
> because my Xaction log gets huge, especially on a
> distributor server.
> Roger.
> >--Original Message--
> >All right guys are really helping me out. I looked up
> >the proble and it has come down to the following The
> >maintenace Plan says that it cannot do transaction log
> >backups on the master and the msdb databases.
> >
> >My question now is how can I exclude those 2 DB's from
> >the Transaction log backup portion of the maintenance
> >Plan?
> >
> >>--Original Message--
> >>
> >>
> >>Hello Jim,
> >>
> >>First of all, thanks to Tibor for pointing you in the
> >right direction.
> >>Just as an additional information,
> >>you can refer to the following article which explains
> >how to t-shoot
> >>General SQL Maint Wiz failures,
> >>
> >>INF: Troubleshooting Database Maintenance Plan Failures
> >>http://support.microsoft.com/default.aspx?scid=kb;en-
> >us;288577
> >>
> >>In case, you are doing Transaction log backups of one of
> >the databases
> >>which is set to
> >>Simple Recovery Mode, you might want to refer to
> >following article
> >>explaining this scenario,
> >>
> >>BUG: Expired Transaction Log Backups May Not Be Deleted
> >by Maintenance Plan
> >>http://support.microsoft.com/default.aspx?scid=kb;en-
> >us;303292
> >>
> >>Please let us know if these suggestions resolve your
> >issue, if not , feel
> >>free to post any further questions you have.
> >>
> >>Thanks for posting to MSDN Managed Newsgroup.
> >>
> >>Vikrant Dalwale
> >>
> >>Microsoft SQL Server Support Professional
> >>
> >>Microsoft highly recommends to all of our customers
> >that they visit the
> >>http://www.microsoft.com/protect site and perform the
> >three straightforward
> >>steps listed to improve your computer's security."
> >>This posting is provided "AS IS" with no warranties, and
> >confers no rights.
> >>
> >>
> >>
> >>--
> >>Content-Class: urn:content-classes:message
> >>From: "Jim" <jim.abel@.lmco.com>
> >>Sender: "Jim" <jim.abel@.lmco.com>
> >>References: <051501c3bdaf$b172e7d0$3101280a@.phx.gbl>
> >><#iNW#wbvDHA.1872@.TK2MSFTNGP09.phx.gbl>
> >>Subject: Re: Errors during Maintenance Plan execution
> >>Date: Tue, 9 Dec 2003 08:46:52 -0800
> >>Lines: 71
> >>Message-ID: <10f501c3be74$0be078d0$a401280a@.phx.gbl>
> >>MIME-Version: 1.0
> >>Content-Type: text/plain;
> >> charset="iso-8859-1"
> >>Content-Transfer-Encoding: 7bit
> >>X-Newsreader: Microsoft CDO for Windows 2000
> >>X-MIMEOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
> >>thread-index: AcO+dAvglHDFmnxfSiyfAzlM01uGHQ==> >>Newsgroups: microsoft.public.sqlserver.server
> >>Path: cpmsftngxa07.phx.gbl
> >>Xref: cpmsftngxa07.phx.gbl
> >microsoft.public.sqlserver.server:319894
> >>NNTP-Posting-Host: tk2msftngxa12.phx.gbl 10.40.1.164
> >>X-Tomcat-NG: microsoft.public.sqlserver.server
> >>
> >>That helped. So now I have an error with the
> >transaction
> >>log backup portion of the Maintenace Plan.
> >>
> >>Here is the erro
> >>
> >>========================================================> >==> >>Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe
> >>failed. [SQLSTATE 42000] (Error 22029). The step
> >failed.
> >>===============================================> >>
> >>I can't find this in the error logs or the backup log
> >so
> >>I'm not sure what is happening
> >>--Original Message--
> >>You've checked the option to "Attempt to repair minor
> >>problems" for which
> >>SQL Server tries to set the db in single user mode
> >which
> >>will fail if you
> >>have users in the database. Remove that option, if you
> >>do get a problem with
> >>the database, you want to know about it and be there
> >to
> >>make a conscious
> >>decision of your actions at that stage.
> >>
> >>--
> >>Tibor Karaszi, SQL Server MVP
> >>Archive at:
> >>http://groups.google.com/groups?
> >>oi=djq&as_ugroup=microsoft.public.sqlserver
> >>
> >>
> >>"Jim" <jim.abel@.lmco.com> wrote in message
> >>news:051501c3bdaf$b172e7d0$3101280a@.phx.gbl...
> >> I am getting error on several database when trying
> >to
> >>use
> >> the Maintenance Plan to backup all databses in our
> >SQL
> >> Server 2000 with Service Pack 3.
> >> The following is the text generated from one of the
> >> database in the log file. The other databases
> >generate
> >> the same error message
> >>
> >> Log portion==============================> >> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error
> >5070:
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]
> >Database
> >> state cannot be changed while other users are using
> >the
> >> database 'SQLCatalog'
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
> >> DATABASE statement failed.
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]
> >> sp_dboption command failed.
> >> [18] Database SQLCatalog: Check Data and Index
> >>Linkage...
> >> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error
> >7919:
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
> >> statement not processed. Database needs to be in
> >single
> >> user mode.
> >>
> >> The following errors were found:
> >>
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
> >> statement not processed. Database needs to be in
> >single
> >> user mode.
> >> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> >>
> >> ========================================> >>
> >> Any ideas on how to handle this? Is this a matter of
> >> running the maintenance plan at a different time?
> >>
> >>
> >>.
> >>
> >>
> >>
> >>.
> >>
> >.
> >

Errors and Stored Procs

Ok, I've read somewhere(which I'm looking for again :\ ) that said that there are errors like DeadLock that kills the execution of a stored proc and there are other errors that do not necessarily kill the rest of the execution of the stored proc. Is that true? If so does anyone have any links I can read. What I'm seeing is a bad id in the foreign key and I think what is happening is that there was a unique constraint error on the first insert but the stored proc continued executing and used the bad id later on in the stored proc.

I do know I can use the @.@.error and will start using it but I need more proof to agree or not agree with my theory.

Thanks ahead of time for any information you can give me either way.

DMWYour assumption/theory is correct. The batch executes till the end or the first logically reachable RETURN.|||Thank you. I just wrote a script that proved that to me. If anyone else is interested...

/************Execute to create the tables****************/
--create test table
CREATE TABLE mytesttable
(
theid int UNIQUE NONCLUSTERED,
thedate datetime
)

--Insert values into the table
insert into mytesttable
(theid, thedate) values
(1, '1/1/2004')
insert into mytesttable
(theid, thedate) values
(2, '2/1/2004')
insert into mytesttable
(theid, thedate) values
(3, '3/1/2004')

/************************************************** */

/************Execute to create the stored proc**************/
create procedure spMytest

as
declare @.theid int
declare @.err1 int
declare @.err2 int

begin transaction thetest
--Force the unique constrant to happen
select @.theid = max(theid) from mytesttable

insert into mytesttable
(theid, thedate) values
(@.theid, '4/1/2004')
set @.err1 = @.@.error
commit transaction thetest

update mytesttable set thedate = getdate() where theid = @.theid
select @.err1

/************************************************** */

/*****************Run the next lines to watch the fun********/

--Run the stored Proc
exec spMytest
--look at the results
select * from mytesttable
/************************************************** */



Hope this helps someone else

DMW

error-Line 1: Incorrect syntax near =

hi,

i got this error when i run app.

-----
Line 1: Incorrect syntax near '='.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '='.

Source Error:

Line 40: Dim adpt As New SqlDataAdapter("SELECT * FROM SMS_student_class_master WHERE" & _
Line 41: "stud_id=" & sid, con)
Line 42: adpt.Fill(ds, "SMS_student_class_master")
Line 43: txt.Text = ds.Tables.Item("roll_no").ToString
Line 44: con.Close()

Source File: c:\inetpub\wwwroot\aspnet\sms\assignment_d.aspx.vb Line: 42
---

what should i do?
anyone have any idea?
plz give solution.
it's urgent.

thanks in advanceThis error indicates that there is an error in your sql statement. It looks like you do not have a space after your WHERE keyword.

hope this does the trick,
sivilian|||I think you have forgotten to put a space after WHERE and before stud_id

According to your string that you have formatted in Line 40 and 41, the sql string that will comes up will be like so

SELECT * FROM SMS_student_class_master WHEREstud_id=123

You should have put the string like below:

Dim adpt As New SqlDataAdapter("SELECT * FROM SMS_student_class_master WHERE " & _
"stud_id=" & sid, con)