Showing posts with label error. Show all posts
Showing posts with label error. Show all posts

Thursday, March 22, 2012

Best Practices: Recording Error Information (Beyond ErrorCode and ErrorColumn)

I'm sorry for asking this, as I'm sure tha the answer is in one of the 208 matches I found searching on "ErrorCode". Unfortunately, this project is overdue and I need a solution fairly soon.

I should add that I've only been developing in SSIS since August 2007.

I have a complicated package, loading about 17 outputs of the XML Source into staging tables. I have been using the error outputs of any standard components I use, out of faith that, if Microsoft provided them, then they should be useful for something. I've been directing all of the error outputs for one of the 17 "tables" from the XML Source into a Union All, and then into an "Error staging table", for each of the 17 outputs. This table includes all of the possible columns, but permits nulls for all of them. It also incldues the ErrorCode and ErrorColumn.

Unfortunately, if these latter two are useful for anyting, I haven't found it yet.

Right now, working with our first "mostly real" data, I'm getting 100% of my input rows written into error tables. Unfortunately, the information in the tables are of limited use in determining what went wrong. For instance, the ErrorColumn seems only to be populated if there was a specific error with a specific column. The Lookup component, doesn't seem to populate ErrorColumn, even if only one column was used for the lookup! No information about the component producing the error output is supplied in the error output, either, which makes it difficult to determine which of the five or so possible error outputs is the one that produced the particular error row.

This proves that I'm missing something simple. How do people handle errors? In my custom components, I learned to use the Fire* methods to produce detailed messages before redirecting the row to the error output, but this sort of thing is not available through the standard components.

Do I really have to create a separate "add useful information to the error output" component, and use it on each error output?

Sorry for the attitude, but just when I think I'm winning, SSIS brings me back down to earth!

If you want to identify which component produced an error then simply hook a derived column component up to the error path that adds the name of the component producing the error to the data path. Thats that one taken care of!

"Do I really have to create a separate "add useful information to the error output" component, and use it on each error output?"

Whats the problem with doing that? Granted you could argue that this information should be included anyway but the rationale for it NOT being there is that it could potentially take up A LOT of memory - a rationale that I agree with. hence you have the opportunity to do it yourself.

Note that if youre using a UNION ALL to collate all of your error rows into one place then you only need to do this once.

-Jamie

|||

Jamie Thomson wrote:

If you want to identify which component produced an error then simply hook a derived column component up to the error path that adds the name of the component producing the error to the data path. Thats that one taken care of!

"Do I really have to create a separate "add useful information to the error output" component, and use it on each error output?"

Whats the problem with doing that?

The main problem is that I don't know how!

The second problem is that there are about 50 or so places I'd have to add one of these components.

In particular, how can a downstream component learn about the component next-upstream? I also don't know how I'd "do it only once" downstream of the Union All.

|||

I've implemented a Derived Column transform for the first few of my error outputs, and it works well enough.

My next issue is that I have two custom components that are very heavily used. They produce good error messages through the Fire* methods. I now would like to take the messages they produce and place them into my new ErrorMessage column. I added a dummy Derived Column transform to the start of the chain of transforms in order to make the ErrorMesage column available with the correct data type and size throughout the transform chain.

The problem is that both of my components do nonstandard things with their collection of input columns, so I can't just add ErrorMessage as an input column. I know how to get the buffer index for an input column and how to use it to get values from the buffer for the column, and how to set them into the buffer.

My question is: can I get a buffer index for a column not in my input columns collection and use that index to set the value of that column? I see that there's a Virtual Input and Virtual Input Column Collection, but I haven't seen examples of how to use it.

This does sound like a hack, and I promise to fix it once I get the leisure to write a UI for these components so they don't have to do strange things with their input column collections.

Thanks.|||

John Saunders wrote:

Jamie Thomson wrote:

If you want to identify which component produced an error then simply hook a derived column component up to the error path that adds the name of the component producing the error to the data path. Thats that one taken care of!

"Do I really have to create a separate "add useful information to the error output" component, and use it on each error output?"

Whats the problem with doing that?

The main problem is that I don't know how!

Oh right. Sorry. Go here:

SSIS Nugget: Get error descriptions

(http://blogs.conchango.com/jamiethomson/archive/2005/08/08/SSIS-Nugget_3A00_-Get-error-descriptions.aspx)

John Saunders wrote:

The second problem is that there are about 50 or so places I'd have to add one of these components.

I'm not understanding why. If you push all the error outputs into a UNION ALL then you just need to add the code that I linked to above in a single component downstream of the UNION ALL. Unless I'm misunderstanding.

John Saunders wrote:

In particular, how can a downstream component learn about the component next-upstream?

It can't but I don't know why you'd want to.

-Jamie

|||

John Saunders wrote:

My question is: can I get a buffer index for a column not in my input columns collection and use that index to set the value of that column? I see that there's a Virtual Input and Virtual Input Column Collection, but I haven't seen examples of how to use it.

Spot on. Yes, you can do that. To be honest, the virtual input is what you see in the designer. The inputitself contais the columns that are selected at design-time. hence the columns in the input are a subset of teh volumns in the virtual input.

This code will return the virtual input and let you process the columns

Code Snippet

IDtsVirtualInput90 vInput = ComponentMetadata.InputCollection[0].GetVirtualInput();

foreach (IDtsVirtualInputColumn90 in vInput)

{

///blah blah blah

}

[that's done from memory so don't hang me if it doesn't work Smile ]

By the way, I will be publishing some code, via my blog and codeplex, in the next couple of days that does exactly this i.e. process data based on the virtual input. Keep a look out for that. Addrss is below.

-Jamie

Tuesday, March 20, 2012

Best Practices Analyzer

Why do I keep getting a login error box when I try to login to the BPA? I am
logging in with the same ID as I used to create the repository.
Hi Jim
BPA just tunnels through the connection information.
Can you connect from query analyzer?
Can you please provide more info on the error that you are getting?
Thanks,
- Christian
___________________________
Christian Kleinerman
Program Manager, SQL Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"JimW" <anonymous@.discussions.microsoft.com> wrote in message
news:E9D0888C-B01E-426A-9A74-E27E3C4113FE@.microsoft.com...
> Why do I keep getting a login error box when I try to login to the BPA? I
am
> logging in with the same ID as I used to create the repository.
|||Yes I can get in with Query Analyzer. All I get is a pop up window that says Login Error with a red x and a button to click ok.
-- Christian Kleinerman [MS] wrote: --
Hi Jim
BPA just tunnels through the connection information.
Can you connect from query analyzer?
Can you please provide more info on the error that you are getting?
Thanks,
- Christian
___________________________
Christian Kleinerman
Program Manager, SQL Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"JimW" <anonymous@.discussions.microsoft.com> wrote in message
news:E9D0888C-B01E-426A-9A74-E27E3C4113FE@.microsoft.com...
> Why do I keep getting a login error box when I try to login to the BPA? I
am
> logging in with the same ID as I used to create the repository.
|||From your post seems that repository database was created successfully
during setup. This would tell me that your managed stack is working well.
(i.e. SqlClient)
Can you try to login with profiler attached capturing logon and error
events, and verify 1) whether a connection is being established and 2)
whether any error is being reported?
Thanks,
- Christian
___________________________
Christian Kleinerman
Program Manager, SQL Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"JimW" <anonymous@.discussions.microsoft.com> wrote in message
news:DB5F277F-6FFC-4C3B-902B-E1885A884A4C@.microsoft.com...
> Yes I can get in with Query Analyzer. All I get is a pop up window that
says Login Error with a red x and a button to click ok.
> -- Christian Kleinerman [MS] wrote: --
> Hi Jim
> BPA just tunnels through the connection information.
> Can you connect from query analyzer?
> Can you please provide more info on the error that you are getting?
> Thanks,
> - Christian
>
> --
> ___________________________
> Christian Kleinerman
> Program Manager, SQL Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "JimW" <anonymous@.discussions.microsoft.com> wrote in message
> news:E9D0888C-B01E-426A-9A74-E27E3C4113FE@.microsoft.com...
BPA? I
> am
>
>
|||This is what I get from profiler with all error possibilities and other things.
10NULLNULLNULLNULLNULLNULLNULL2004-05-19 08:53:32.880NULLNULLNULL
211exec sp_reset_connectionjweber0SQL Best Practices AnalyzerWPSR\jweber51NULL2004-05-19 08:53:36.940NULLNULLNULL
310exec sp_reset_connectionjweber0SQL Best Practices AnalyzerWPSR\jweber5102004-05-19 08:53:36.940000
440select * from sysobjects where name = N'bpa_active_connections' or name =N'bpa_bestpractices' or name =N'bpa_files' or name =N'bpa_instance_results' or name =N'bpa_managed_types' or name =N'bpa_properties' or name =N'bpa_bestpractice_sqlservers' or
name jweber0SQL Best Practices AnalyzerWPSR\jweber51NULL2004-05-19 08:53:36.940NULLNULLNULL
5114select * from sysobjects where name = N'bpa_active_connections' or name =N'bpa_bestpractices' or name =N'bpa_files' or name =N'bpa_instance_results' or name =N'bpa_managed_types' or name =N'bpa_properties' or name =N'bpa_bestpractice_sqlservers' o
r name jweber0SQL Best Practices AnalyzerWPSR\jweber51NULL2004-05-19 08:53:36.957NULLNULLNULL
612select * from sysobjects where name = N'bpa_active_connections' or name =N'bpa_bestpractices' or name =N'bpa_files' or name =N'bpa_instance_results' or name =N'bpa_managed_types' or name =N'bpa_properties' or name =N'bpa_bestpractice_sqlservers' or
name jweber0SQL Best Practices AnalyzerWPSR\jweber51162004-05-19 08:53:36.940374016
715NULLjweber0SQL Best Practices AnalyzerWPSR\jweber511929232004-05-19 08:50:24.033413016
814-- network protocol: TCP/IP set quoted_identifier on set implicit_transactions off set cursor_close_on_commit off set ansi_warnings on set ansi_padding on set ansi_nulls on set concat_null_yields_null on set language us_english set dateformat mdy set
datefjweber0SQL Best Practices AnalyzerWPSR\jweber51NULL2004-05-19 08:53:37.003NULLNULLNULL
95NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
-- Christian Kleinerman [MS] wrote: --
From your post seems that repository database was created successfully
during setup. This would tell me that your managed stack is working well.
(i.e. SqlClient)
Can you try to login with profiler attached capturing logon and error
events, and verify 1) whether a connection is being established and 2)
whether any error is being reported?
Thanks,
- Christian
___________________________
Christian Kleinerman
Program Manager, SQL Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"JimW" <anonymous@.discussions.microsoft.com> wrote in message
news:DB5F277F-6FFC-4C3B-902B-E1885A884A4C@.microsoft.com...
> Yes I can get in with Query Analyzer. All I get is a pop up window that
says Login Error with a red x and a button to click ok.[vbcol=seagreen]
> BPA just tunnels through the connection information.
> Can you connect from query analyzer?
> Can you please provide more info on the error that you are getting?
> ___________________________
> Christian Kleinerman
> Program Manager, SQL Engine
rights.[vbcol=seagreen]
> news:E9D0888C-B01E-426A-9A74-E27E3C4113FE@.microsoft.com...
BPA? I[vbcol=seagreen]
> am

Sunday, February 12, 2012

begin transactions

Hi I am a DBA and am having a dispute with a developer. He insists on
coding like this:
Begin train
1 insert statment
Check for error
Commit or rollback.
Since SQL Server 2000 an d 2005 has implicit transactions, my standard
is to NOT put them in unless they are needed and at least 2 statements
occur (insert, update, delete). I am assuming that these extra begin
trans affect performance and the log some how. Can anyone help me with
this. Again, this is the case when there is 1 update, delete, or
insert.
Thanks in advance.
KristinaKristinaDBA@.gmail.com wrote:
> Hi I am a DBA and am having a dispute with a developer. He insists on
> coding like this:
> Begin train
> 1 insert statment
> Check for error
> Commit or rollback.
> Since SQL Server 2000 an d 2005 has implicit transactions, my standard
> is to NOT put them in unless they are needed and at least 2 statements
> occur (insert, update, delete). I am assuming that these extra begin
> trans affect performance and the log some how. Can anyone help me with
> this. Again, this is the case when there is 1 update, delete, or
> insert.
> Thanks in advance.
> Kristina
>
I'd have to side with your developer on this one... You're "assuming"
that SQL will take care of the error handling for you. He's
GUARANTEEING that the errors will be handled in an expected fashion.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks for the advice, but I am not sure you are understanding my
question. You can check for an error without a transaction like this:
Insert table (firld a, field b)
Select a, b ....
IF @.@.error <> 0
do somthing.
The point it this, the insert will AUTOMATICALLY roll back as it is
only one statement. The question is, does another begin tran put extra
overhead on performance. The regular insert will roll back if it fails
because by default, SQL server does implicit transactions unlike
oracle. etc..
make sense?
Tracy McKibben wrote:
> KristinaDBA@.gmail.com wrote:
> > Hi I am a DBA and am having a dispute with a developer. He insists on
> > coding like this:
> >
> > Begin train
> >
> > 1 insert statment
> >
> > Check for error
> >
> > Commit or rollback.
> >
> > Since SQL Server 2000 an d 2005 has implicit transactions, my standard
> > is to NOT put them in unless they are needed and at least 2 statements
> > occur (insert, update, delete). I am assuming that these extra begin
> > trans affect performance and the log some how. Can anyone help me with
> > this. Again, this is the case when there is 1 update, delete, or
> > insert.
> >
> > Thanks in advance.
> > Kristina
> >
> I'd have to side with your developer on this one... You're "assuming"
> that SQL will take care of the error handling for you. He's
> GUARANTEEING that the errors will be handled in an expected fashion.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Thanks for the advice, but I am not sure you are understanding my
question. You can check for an error without a transaction like this:
Insert table (firld a, field b)
Select a, b ....
IF @.@.error <> 0
do somthing.
The point it this, the insert will AUTOMATICALLY roll back as it is
only one statement. The question is, does another begin tran put extra
overhead on performance. The regular insert will roll back if it fails
because by default, SQL server does implicit transactions unlike
oracle. etc..
make sense?
Tracy McKibben wrote:
> KristinaDBA@.gmail.com wrote:
> > Hi I am a DBA and am having a dispute with a developer. He insists on
> > coding like this:
> >
> > Begin train
> >
> > 1 insert statment
> >
> > Check for error
> >
> > Commit or rollback.
> >
> > Since SQL Server 2000 an d 2005 has implicit transactions, my standard
> > is to NOT put them in unless they are needed and at least 2 statements
> > occur (insert, update, delete). I am assuming that these extra begin
> > trans affect performance and the log some how. Can anyone help me with
> > this. Again, this is the case when there is 1 update, delete, or
> > insert.
> >
> > Thanks in advance.
> > Kristina
> >
> I'd have to side with your developer on this one... You're "assuming"
> that SQL will take care of the error handling for you. He's
> GUARANTEEING that the errors will be handled in an expected fashion.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||KristinaDBA@.gmail.com wrote:
> Thanks for the advice, but I am not sure you are understanding my
> question. You can check for an error without a transaction like this:
> Insert table (firld a, field b)
> Select a, b ....
> IF @.@.error <> 0
> do somthing.
> The point it this, the insert will AUTOMATICALLY roll back as it is
> only one statement. The question is, does another begin tran put extra
> overhead on performance. The regular insert will roll back if it fails
> because by default, SQL server does implicit transactions unlike
> oracle. etc..
> make sense?
>
I understood your question perfectly. Explicitly issuing a BEGIN TRAN
doesn't add any overhead to an implicit transaction. My point was that
your developer is guaranteeing the behavior of his code. His code will
also be easier to understand to someone new to SQL, who may not know or
fully understand implicit transactions. I'd compare this to arguing
over commenting your code - good comments make for good code. In this
case, good flow control makes for good code.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||No, if you have single statement autocommit transactions they are COMMITTED
when the single statement is finished, so they cannot be then rolled back.
They are automatically COMMITTED, not AUTOMATICALLY ROLLED BACK.
There are errors like constraint violations that will not cause rollbacks.
The only way to force a rollback for an error that doesn't automatically
roll back is to check the error before the commit occurs, and that means you
have to turn the transaction into an explicit transaction, using BEGIN TRAN.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
<KristinaDBA@.gmail.com> wrote in message
news:1165335197.517839.101070@.16g2000cwy.googlegroups.com...
> Thanks for the advice, but I am not sure you are understanding my
> question. You can check for an error without a transaction like this:
> Insert table (firld a, field b)
> Select a, b ....
> IF @.@.error <> 0
> do somthing.
> The point it this, the insert will AUTOMATICALLY roll back as it is
> only one statement. The question is, does another begin tran put extra
> overhead on performance. The regular insert will roll back if it fails
> because by default, SQL server does implicit transactions unlike
> oracle. etc..
> make sense?
> Tracy McKibben wrote:
>> KristinaDBA@.gmail.com wrote:
>> > Hi I am a DBA and am having a dispute with a developer. He insists on
>> > coding like this:
>> >
>> > Begin train
>> >
>> > 1 insert statment
>> >
>> > Check for error
>> >
>> > Commit or rollback.
>> >
>> > Since SQL Server 2000 an d 2005 has implicit transactions, my standard
>> > is to NOT put them in unless they are needed and at least 2 statements
>> > occur (insert, update, delete). I am assuming that these extra begin
>> > trans affect performance and the log some how. Can anyone help me with
>> > this. Again, this is the case when there is 1 update, delete, or
>> > insert.
>> >
>> > Thanks in advance.
>> > Kristina
>> >
>> I'd have to side with your developer on this one... You're "assuming"
>> that SQL will take care of the error handling for you. He's
>> GUARANTEEING that the errors will be handled in an expected fashion.
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>|||<KristinaDBA@.gmail.com> wrote in message
news:1165334426.713996.85810@.79g2000cws.googlegroups.com...
> Hi I am a DBA and am having a dispute with a developer. He insists on
> coding like this:
> Begin train
> 1 insert statment
> Check for error
> Commit or rollback.
> Since SQL Server 2000 an d 2005 has implicit transactions, my standard
> is to NOT put them in unless they are needed and at least 2 statements
> occur (insert, update, delete). I am assuming that these extra begin
> trans affect performance and the log some how. Can anyone help me with
> this. Again, this is the case when there is 1 update, delete, or
> insert.
>
For a single DML statement, there is no need, but no real cost, to wrapping
it in an explicit transaction. It might generate an extra log record or
two, but nothing to worry about.
But, what if you want to enlist this procedure in a larger transaction? I
am generally against explicit transaction handling in stored procedures.
While it's sometimes necessary and beneficial It's usually the wrong scope
to knit together transactions and decide their fate. You only really want
one level of transaction handling, either in the outermost stored procedure
or in the client code.
David|||Kalen,
I misspoke. What I mean to say is if there was an error, they would be
automically rolled back. - no need for begin trans. In the case of no
errors with a single statment, they would be automatically committed.
On Dec 5, 11:25 am, "David Browne" <davidbaxterbrowne no potted
m...@.hotmail.com> wrote:
> <Kristina...@.gmail.com> wrote in messagenews:1165334426.713996.85810@.79g2000cws.googlegroups.com...
>
>
> > Hi I am a DBA and am having a dispute with a developer. He insists on
> > coding like this:
> > Begin train
> > 1 insert statment
> > Check for error
> > Commit or rollback.
> > Since SQL Server 2000 an d 2005 has implicit transactions, my standard
> > is to NOT put them in unless they are needed and at least 2 statements
> > occur (insert, update, delete). I am assuming that these extra begin
> > trans affect performance and the log some how. Can anyone help me with
> > this. Again, this is the case when there is 1 update, delete, or
> > insert.For a single DML statement, there is no need, but no real cost, to wrapping
> it in an explicit transaction. It might generate an extra log record or
> two, but nothing to worry about.
> But, what if you want to enlist this procedure in a larger transaction? I
> am generally against explicit transaction handling in stored procedures.
> While it's sometimes necessary and beneficial It's usually the wrong scope
> to knit together transactions and decide their fate. You only really want
> one level of transaction handling, either in the outermost stored procedure
> or in the client code.
> David- Hide quoted text -- Show quoted text -|||I understand that. Did you even read my reply?
I said that there are some errors that do NOT automatically rollback, that
will commit even with the error. So the only way to roll them back is for
YOU or your code to catch them before the commit, and the only way to do
that is to have BEGIN TRAN.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
<KristinaDBA@.gmail.com> wrote in message
news:1165336494.705787.285740@.l12g2000cwl.googlegroups.com...
> Kalen,
> I misspoke. What I mean to say is if there was an error, they would be
> automically rolled back. - no need for begin trans. In the case of no
> errors with a single statment, they would be automatically committed.
> On Dec 5, 11:25 am, "David Browne" <davidbaxterbrowne no potted
> m...@.hotmail.com> wrote:
>> <Kristina...@.gmail.com> wrote in
>> messagenews:1165334426.713996.85810@.79g2000cws.googlegroups.com...
>>
>>
>> > Hi I am a DBA and am having a dispute with a developer. He insists on
>> > coding like this:
>> > Begin train
>> > 1 insert statment
>> > Check for error
>> > Commit or rollback.
>> > Since SQL Server 2000 an d 2005 has implicit transactions, my standard
>> > is to NOT put them in unless they are needed and at least 2 statements
>> > occur (insert, update, delete). I am assuming that these extra begin
>> > trans affect performance and the log some how. Can anyone help me with
>> > this. Again, this is the case when there is 1 update, delete, or
>> > insert.For a single DML statement, there is no need, but no real cost,
>> > to wrapping
>> it in an explicit transaction. It might generate an extra log record or
>> two, but nothing to worry about.
>> But, what if you want to enlist this procedure in a larger transaction?
>> I
>> am generally against explicit transaction handling in stored procedures.
>> While it's sometimes necessary and beneficial It's usually the wrong
>> scope
>> to knit together transactions and decide their fate. You only really
>> want
>> one level of transaction handling, either in the outermost stored
>> procedure
>> or in the client code.
>> David- Hide quoted text -- Show quoted text -
>|||Kalen,
I did read your reply. Can you give me a transact sql example of a
statement that won't roll back - like you mentioned a constraint
violation. I am not aware of this and would be happy to learn..
Thanks.
On Dec 5, 11:46 am, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> I understand that. Did you even read my reply?
> I said that there are some errors that do NOT automatically rollback, that
> will commit even with the error. So the only way to roll them back is for
> YOU or your code to catch them before the commit, and the only way to do
> that is to have BEGIN TRAN.
> --
> HTH
> Kalen Delaney, SQL Server MVPhttp://sqlblog.com
> <Kristina...@.gmail.com> wrote in messagenews:1165336494.705787.285740@.l12g2000cwl.googlegroups.com...
>
> > Kalen,
> > I misspoke. What I mean to say is if there was an error, they would be
> > automically rolled back. - no need for begin trans. In the case of no
> > errors with a single statment, they would be automatically committed.
> > On Dec 5, 11:25 am, "David Browne" <davidbaxterbrowne no potted
> > m...@.hotmail.com> wrote:
> >> <Kristina...@.gmail.com> wrote in
> >> messagenews:1165334426.713996.85810@.79g2000cws.googlegroups.com...
> >> > Hi I am a DBA and am having a dispute with a developer. He insists on
> >> > coding like this:
> >> > Begin train
> >> > 1 insert statment
> >> > Check for error
> >> > Commit or rollback.
> >> > Since SQL Server 2000 an d 2005 has implicit transactions, my standard
> >> > is to NOT put them in unless they are needed and at least 2 statements
> >> > occur (insert, update, delete). I am assuming that these extra begin
> >> > trans affect performance and the log some how. Can anyone help me with
> >> > this. Again, this is the case when there is 1 update, delete, or
> >> > insert.For a single DML statement, there is no need, but no real cost,
> >> > to wrapping
> >> it in an explicit transaction. It might generate an extra log record or
> >> two, but nothing to worry about.
> >> But, what if you want to enlist this procedure in a larger transaction?
> >> I
> >> am generally against explicit transaction handling in stored procedures.
> >> While it's sometimes necessary and beneficial It's usually the wrong
> >> scope
> >> to knit together transactions and decide their fate. You only really
> >> want
> >> one level of transaction handling, either in the outermost stored
> >> procedure
> >> or in the client code.
> >> David- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:#RM4bzIGHHA.2456@.TK2MSFTNGP06.phx.gbl...
>I understand that. Did you even read my reply?
> I said that there are some errors that do NOT automatically rollback, that
> will commit even with the error. So the only way to roll them back is for
> YOU or your code to catch them before the commit, and the only way to do
> that is to have BEGIN TRAN.
>
I think the confusion is around the way single DML statements are handled.
A constraint violation will not roll back a transaction, but it will "roll
back" any changes made by the the statement in which the violation occurs,
since single DML are always atomic.
David|||I apologize. I should really finish my coffee in the morning before become
insistent upon anything!
I was thinking of multiple statements where one failed but the rest
succeeded. But that of course would always have to have BEGIN TRAN.
One single statement will have a partial success. It will be all or nothing.
That being said, adding the BEGIN TRAN does not really add any extra
overhead, UNLESS you somehow forget to COMMIT or ROLLBACK and your
transaction ends up staying open for longer than necessary. Then there are
all kinds of ramifications.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
<KristinaDBA@.gmail.com> wrote in message
news:1165339226.543180.164360@.f1g2000cwa.googlegroups.com...
> Kalen,
> I did read your reply. Can you give me a transact sql example of a
> statement that won't roll back - like you mentioned a constraint
> violation. I am not aware of this and would be happy to learn..
> Thanks.
> On Dec 5, 11:46 am, "Kalen Delaney" <replies@.public_newsgroups.com>
> wrote:
>> I understand that. Did you even read my reply?
>> I said that there are some errors that do NOT automatically rollback,
>> that
>> will commit even with the error. So the only way to roll them back is for
>> YOU or your code to catch them before the commit, and the only way to do
>> that is to have BEGIN TRAN.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVPhttp://sqlblog.com
>> <Kristina...@.gmail.com> wrote in
>> messagenews:1165336494.705787.285740@.l12g2000cwl.googlegroups.com...
>>
>> > Kalen,
>> > I misspoke. What I mean to say is if there was an error, they would be
>> > automically rolled back. - no need for begin trans. In the case of no
>> > errors with a single statment, they would be automatically committed.
>> > On Dec 5, 11:25 am, "David Browne" <davidbaxterbrowne no potted
>> > m...@.hotmail.com> wrote:
>> >> <Kristina...@.gmail.com> wrote in
>> >> messagenews:1165334426.713996.85810@.79g2000cws.googlegroups.com...
>> >> > Hi I am a DBA and am having a dispute with a developer. He insists
>> >> > on
>> >> > coding like this:
>> >> > Begin train
>> >> > 1 insert statment
>> >> > Check for error
>> >> > Commit or rollback.
>> >> > Since SQL Server 2000 an d 2005 has implicit transactions, my
>> >> > standard
>> >> > is to NOT put them in unless they are needed and at least 2
>> >> > statements
>> >> > occur (insert, update, delete). I am assuming that these extra begin
>> >> > trans affect performance and the log some how. Can anyone help me
>> >> > with
>> >> > this. Again, this is the case when there is 1 update, delete, or
>> >> > insert.For a single DML statement, there is no need, but no real
>> >> > cost,
>> >> > to wrapping
>> >> it in an explicit transaction. It might generate an extra log record
>> >> or
>> >> two, but nothing to worry about.
>> >> But, what if you want to enlist this procedure in a larger
>> >> transaction?
>> >> I
>> >> am generally against explicit transaction handling in stored
>> >> procedures.
>> >> While it's sometimes necessary and beneficial It's usually the wrong
>> >> scope
>> >> to knit together transactions and decide their fate. You only really
>> >> want
>> >> one level of transaction handling, either in the outermost stored
>> >> procedure
>> >> or in the client code.
>> >> David- Hide quoted text -- Show quoted text -- Hide quoted text --
>> >> Show quoted text -
>|||KristinaDBA@.gmail.com wrote:
> Hi I am a DBA and am having a dispute with a developer. He insists on
> coding like this:
> Begin train
> 1 insert statment
> Check for error
> Commit or rollback.
> Since SQL Server 2000 an d 2005 has implicit transactions, my standard
> is to NOT put them in unless they are needed and at least 2 statements
> occur (insert, update, delete). I am assuming that these extra begin
> trans affect performance and the log some how. Can anyone help me with
> this. Again, this is the case when there is 1 update, delete, or
> insert.
> Thanks in advance.
Kristina,
I did a quick benchmark:
CREATE TABLE aa(i INT)
go
CREATE PROCEDURE Test1
AS
BEGIN
SET NOCOUNT ON
DECLARE @.i INT
SET @.i = 0
WHILE(@.i < 10000) BEGIN
INSERT aa(i) VALUES(@.i)
SET @.i = @.i + 1
END
END
go
CREATE PROCEDURE Test2
AS
BEGIN
SET NOCOUNT ON
DECLARE @.i INT
SET @.i = 0
WHILE(@.i < 10000) BEGIN
BEGIN TRANSACTION
INSERT aa(i) VALUES(@.i)
SET @.i = @.i + 1
IF @.@.ERROR<>0 BEGIN
ROLLBACK
END ELSE BEGIN
COMMIT
END
END
END
go
Test1
go
Test1
/*
Profiler results:
CPU:406
Reads: 10720
Writes: 104
Duration: 2406
*/
go
Test2
go
Test2
/*
Profiler results:
CPU:373
Reads: 10844
Writes: 116
Duration: 2406
*/
go
DROP PROCEDURE Test1
go
DROP PROCEDURE Test2
go
DROP TABLE aa
go
I ran them 3 times and I did not notice any significant differences in
neither of 4 counters.
I do not know anything about your environment, but I would not worry
about performance penalties of BEGIN TRAN in mine.
--
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/|||Thanks Alex,
That definitely clarifies the issue!
Also, Kalen - glad you are not *mad* any more...
This is a great group, I am going to stick with it. Today was my first
day checking it out :).
Kristina.
Alex Kuznetsov wrote:
> KristinaDBA@.gmail.com wrote:
> > Hi I am a DBA and am having a dispute with a developer. He insists on
> > coding like this:
> >
> > Begin train
> >
> > 1 insert statment
> >
> > Check for error
> >
> > Commit or rollback.
> >
> > Since SQL Server 2000 an d 2005 has implicit transactions, my standard
> > is to NOT put them in unless they are needed and at least 2 statements
> > occur (insert, update, delete). I am assuming that these extra begin
> > trans affect performance and the log some how. Can anyone help me with
> > this. Again, this is the case when there is 1 update, delete, or
> > insert.
> >
> > Thanks in advance.
> Kristina,
> I did a quick benchmark:
> CREATE TABLE aa(i INT)
> go
> CREATE PROCEDURE Test1
> AS
> BEGIN
> SET NOCOUNT ON
> DECLARE @.i INT
> SET @.i = 0
> WHILE(@.i < 10000) BEGIN
> INSERT aa(i) VALUES(@.i)
> SET @.i = @.i + 1
> END
> END
> go
> CREATE PROCEDURE Test2
> AS
> BEGIN
> SET NOCOUNT ON
> DECLARE @.i INT
> SET @.i = 0
> WHILE(@.i < 10000) BEGIN
> BEGIN TRANSACTION
> INSERT aa(i) VALUES(@.i)
> SET @.i = @.i + 1
> IF @.@.ERROR<>0 BEGIN
> ROLLBACK
> END ELSE BEGIN
> COMMIT
> END
> END
> END
> go
> Test1
> go
> Test1
> /*
> Profiler results:
> CPU:406
> Reads: 10720
> Writes: 104
> Duration: 2406
> */
> go
> Test2
> go
> Test2
> /*
> Profiler results:
> CPU:373
> Reads: 10844
> Writes: 116
> Duration: 2406
> */
> go
> DROP PROCEDURE Test1
> go
> DROP PROCEDURE Test2
> go
> DROP TABLE aa
> go
> I ran them 3 times and I did not notice any significant differences in
> neither of 4 counters.
> I do not know anything about your environment, but I would not worry
> about performance penalties of BEGIN TRAN in mine.
> --
> Alex Kuznetsov
> http://sqlserver-tips.blogspot.com/
> http://sqlserver-puzzles.blogspot.com/

BEGIN STACK DUMP error in log viewer

Hi

We are having problems with our application that uses SQL Server 2005 in a cluster environment. Sometimes the system stops answering and registers in the log viewer the following error:

=====================================================================

BugCheck Dump

=====================================================================

This file is generated by Microsoft SQL Server

version 9.00.1399.06

upon detection of fatal unexpected error. Please return this file, the query or program that produced the bugcheck, the database and the error log, and any other pertinent information with a Service Request.

Computer type is AT/AT COMPATIBLE.

Bios Version is IBM- 1001

Current time is 16:48:36 12/05/06.

2 Intel x86 level 15, 3600 Mhz processor (s).

Windows NT 5.2 Build 3790 CSD Service Pack 1.

Memory

MemoryLoad = 74%

Total Physical = 3327 MB

Available Physical = 858 MB

Total Page File = 9318 MB

Available Page File = 7058 MB

Total Virtual = 2047 MB

Available Virtual = 274 MB

**Dump thread - spid = 132, PSS = 0x71E09588, EC = 0x71E09590

***Stack Dump being sent to E:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump0178.txt

* *******************************************************************************

*

* BEGIN STACK DUMP:

*12/05/06 16:48:36 spid 132

*

* Location:lckmgr.cpp:10820

* Expression:GetLocalLockPartition () == xactLockInfo->GetLocalLockPartition ()

* SPID:132

* Process ID:2436

*

* Input Buffer 255 bytes -

*?16 00 00 00 12 00 00 00 02 00 01 00 00 00 84 00 00 00

*???dD 01 00 00 00 ff ff 0a 00 02 00 00 00 e7 64 09 09 04 d0

*4dS E L E C T00 34 64 09 20 00 53 00 45 00 4c 00 45 00 43 00 54 00

*TAbleA (*) 20 00 54 00 48 00 69 00 73 00 74 00 6f 00 72 00 69 00

(*) There is a query here that I have excluded in the message

*

*MODULEBASEENDSIZE

* sqlservr0100000002BA7FFF01ba8000

* ntdll7C9100007C9D3FFF000c4000

* kernel327C8000007C90BFFF0010c000

* MSVCR8078130000781CAFFF0009b000

* msvcrt77B9000077BE9FFF0005a000

* MSVCP807C4200007C4A6FFF00087000

* ADVAPI3277D9000077E3DFFF000ae000

* RPCRT477C4000077CDEFFF0009f000

* USER3277F4000077FD1FFF00092000

* GDI3277BF000077C37FFF00048000

* CRYPT32760D000076164FFF00095000

* MSASN1760B0000760C1FFF00012000

* Secur3276E7000076E82FFF00013000

* MSWSOCK71970000719B1FFF00042000

* WS2_3271A5000071A66FFF00017000

* WS2HELP71A4000071A47FFF00008000

* USERENV7684000076904FFF000c5000

* opends60333E0000333E6FFF00007000

* NETAPI3271A9000071AE7FFF00058000

* SHELL327C9E00007D1EAFFF0080b000

* SHLWAPI77EE000077F31FFF00052000

* comctl327736000077462FFF00103000

* EntApi3700000037012FFF00013000

* PSAPI76A9000076A9AFFF0000b000

* WININET779D000077A78FFF000a9000

* OLEAUT3277CF000077D7BFFF0008c000

* ole327751000077643FFF00134000

* instapi4806000048069FFF 0000a000

* CLUSAPI74CF000074D01FFF00012000

* RESUTILS74E0000074E12FFF00013000

* sqlevn704F6100004F7A0FFF00191000

* SQLOS344D0000344D4FFF00005000

* rsaenh680000006802EFFF0002f000

* AUTHZ76B6000076B73FFF00014000

* MSCOREE340C000034104FFF00045000

* msv1_076BB000076BD6FFF00027000

* iphlpapi76C1000076C29FFF0001a000

* kerberos3433000034387FFF00058000

* cryptdll766000007660BFFF0000c000

* schannel7667000076696FFF00027000

* COMRES76F30000770BCFFF0018d000

* XOLEHLP343F0000343F5FFF00006000

* MSDTCPRX3440000034477FFF00078000

* msvcp60780C000078120FFF00061000

* MTXCLU74E5000074E68FFF00019000

* VERSION77B8000077B87FFF00008000

* WSOCK3271A0000071A09FFF0000a000

* DNSAPI76DF000076E1EFFF0002f000

* winrnr76E9000076E96FFF00007000

* WLDAP3276E3000076E5EFFF0002f000

* rasadhlp76EA000076EA7FFF00008000

* hnetcfg36190000361E8FFF00059000

* wshtcpip7193000071937FFF00008000

* security3634000036343FFF00004000

* msfte36A6000036CB7FFF00258000

* dbghelp36D0000036E17FFF00118000

* WINTRUST76AD000076AFAFFF 0002b000

* imagehlp76B3000076B58FFF00029000

* dssenh6810000068123FFF00024000

* NTMARTA777B0000777D1FFF00022000

* SAMLIB36FE000036FEEFFF0000f000

* ntdsapi7661000076624FFF00015000

* xpsp2res61BF000061EBFFFF002d0000

* CLBCatQ77650000776D2FFF00083000

* sqlncli61EC0000620E1FFF00222000

* COMCTL3277E4000077ED6FFF00097000

* comdlg32761D000076218FFF00049000

* SQLNCLIR007C0000007F2FFF00033000

* msftepxy621F000062204FFF00015000

* xpsqlbot6286000062865FFF00006000

* xpstar9062880000628C4FFF00045000

* SQLSCM90628E0000628E8FFF00009000

* ODBC32629000006293CFFF0003d000

* BatchParser90629400006295DFFF0001e000

* SQLSVC906297000062989FFF0001a000

* SqlResourceLoader629A0000629A5FFF00006000

* ATL807C6300007C64AFFF0001b000

* odbcint62B0000062B17FFF00018000

* SQLSVC9062B2000062B22FFF00003000

* xpstar9062B3000062B55FFF00026000

* xplog7062B6000062B6BFFF0000c000

* xplog7062B8000062B82FFF00003000

* oledb32631B000063228FFF00079000

* MSDART6323000063249FFF0001a000

* OLEDB32R634D0000634E1FFF00012000

* activeds76D1000076D42FFF 00033000

* adsldpc76CE000076D06FFF00027000

* credui76AA000076ACDFFF0002e000

* ATL769A0000769B7FFF00018000

* adsldp711100007113DFFF0002e000

* SXS75CB000075D6BFFF000bc000

* dbghelp65D4000065E52FFF00113000

*

*Edi: 6610BCB8:636A19003E3A604062F1A0406610D8AD0279E9003E3A63D8

*Esi: 00000000:

*Eax: 6610BB9C:000042AC00000000000000007C815E02000000007C931B34

*Ebx: 0000003F:

*Ecx: 6610C20C:00000000000100070000000000740072636A19046610BBCC

*Edx: 0000003D:

*Eip: 7C815E02:10C2C95E90909000A164909000000018C334408B891C428B

*Ebp: 6610BBEC:6610BC3002172CE4000042AC000000000000000000000000

*SegCs: 0000001B:

*EFlags: 00000246:

*Esp: 6610BB98:71E09588000042AC00000000000000007C815E0200000000

*SegSs: 78130023:000000000000000000000000000000000000000000000000

* *******************************************************************************

* -

* Short Stack Dump

7C815E02 Module(kernel32+00015E02)

02172CE4 Module(sqlservr+01172CE4)

02176BA0 Module(sqlservr+01176BA0)

02019506 Module(sqlservr+01019506)

015738EE Module(sqlservr+005738EE)

021B15B6 Module(sqlservr+011B15B6)

0163DD36 Module(sqlservr+0063DD36)

010E9FA3 Module(sqlservr+000E9FA3)

010B0F5F Module(sqlservr+000B0F5F)

0102C5F8 Module(sqlservr+0002C5F8)

01BEE12B Module(sqlservr+00BEE12B)

01BF2BCB Module(sqlservr+00BF2BCB)

01BF353D Module(sqlservr+00BF353D)

010438E5 Module(sqlservr+000438E5)

01041C35 Module(sqlservr+00041C35)

0100889F Module(sqlservr+0000889F)

010089C5 Module(sqlservr+000089C5)

010086E7 Module(sqlservr+000086E7)

010D764A Module(sqlservr+000D764A)

010D7B71 Module(sqlservr+000D7B71)

010D746E Module(sqlservr+000D746E)

010D83F0 Module(sqlservr+000D83F0)

781329AA Module(MSVCR80+000029AA)

78132A36 Module(MSVCR80+00002A36)

PSS @.0x71E09588

CSession @.0x71E08278

--

m_spid = 132m_cRef = 12m_rgcRefType[0] = 1

m_rgcRefType[1] = 1m_rgcRefType[2] = 9m_rgcRefType[3] = 1

m_rgcRefType[4] = 0m_rgcRefType[5] = 0m_pmo = 0x71E08040

m_pstackBhfPool = 0x00000000m_dwLoginFlags = 0x03e0m_fBackground = 0

m_fClientRequestConnReset = 0m_fUserProc = -1m_fConnReset = 0

m_fIsConnReset = 0m_fInLogin = 0m_fReplRelease = 0

m_fKill = 0m_ulLoginStamp = 3105683m_eclClient = 5

m_protType = 5m_hHttpToken = FFFFFFFF

m_pV7LoginRec

00000000:18010000 02000972 401f0000 00000006 400c0000 ?.......r@........@....

00000014:00000000 e0030000 00000000 00000000 5e000400 ?................^...

00000028:66000200 6a000000 7a001c00 b2000c00 ca000000 ?f...j...z...........

0000003C:ca001c00 02010000 02010b00 60f120db ad481801 ?............`. ..H..

00000050:00001801 00001801 00000000 0000???????????????..............

CPhysicalConnection @.0x71E08188

-

m_pPhyConn->m_pmo = 0x71E08040m_pPhyConn->m_pNetConn = 0x71E08788m_pPhyConn->m_pConnList = 0x71E08260

m_pPhyConn->m_pSess = 0x71E08278m_pPhyConn->m_fTracked = -1m_pPhyConn->m_cbPacketsize = 8000

m_pPhyConn->m_fMars = 0m_pPhyConn->m_fKill = 0

CBatch @.0x71E08A90

m_pSess = 0x71E08278m_pConn = 0x71E089F0m_cRef = 3

m_rgcRefType[0] = 1m_rgcRefType[1] = 1m_rgcRefType[2] = 1

m_rgcRefType[3] = 0m_rgcRefType[4] = 0m_pTask = 0x006F9D38

EXCEPT (null) @.0x6610B4AC

-

exc_number = 0exc_severity = 0exc_func = 0x023D96B0

Task @.0x006F9D38

-

CPU Ticks used (ms) = 1Task State = 2

WAITINFO_INTERNAL: WaitResource = 0x00000000WAITINFO_INTERNAL: WaitType = 0x0

WAITINFO_INTERNAL: WaitSpinlock = 0x00000000SchedulerId = 0x0

ThreadId = 0x444m_state = 0m_eAbortSev = 0

EC @.0x71E09590

--

spid = 132ecid = 0ec_stat = 0x0

ec_stat2 = 0x40ec_atomic = 0x4__fSubProc = 1

ec_dbccContext = 0x00000000__pSETLS = 0x71E08A30__pSEParams = 0x71E08CD0

__pDbLocks = 0x71E09878

SEInternalTLS @.0x71E08A30

-

m_flags = 0m_TLSstatus = 3m_owningTask = 0x006F9D38

m_activeHeapDatasetList = 0x71E08A30m_activeIndexDatasetList = 0x71E08A38

SEParams @.0x71E08CD0

--

m_lockTimeout = -1m_isoLevel = 1048576m_logDontReplicate = 0

m_neverReplicate = 0m_XactWorkspace = 0x03F78940m_pSessionLocks = 0x71E09A88

m_pDbLocks = 0x71E09878m_execStats = 0x3F867018m_pAllocFileLimit = 0x00000000

Does anybody know what’s going on?

I will be very appreciate if someone can help me to solve this problem. Thank you!

Hi,

We would like to investigate this issue. Can you please file a bug via http://connect.microsoft.com/sql. Please upload SqlDump0178.mdmp and SqlDump0178.txt there as well.

Thanks, Ron D.

|||any luck on this one. We are seeing the same error.

Thanks,
Siva|||

Make sure the table being accessed have proper CLUSTERED index.

BEGIN STACK DUMP error in log viewer

Hi

We are having problems with our application that uses SQL Server 2005 in a cluster environment. Sometimes the system stops answering and registers in the log viewer the following error:

=====================================================================

BugCheck Dump

=====================================================================

This file is generated by Microsoft SQL Server

version 9.00.1399.06

upon detection of fatal unexpected error. Please return this file, the query or program that produced the bugcheck, the database and the error log, and any other pertinent information with a Service Request.

Computer type is AT/AT COMPATIBLE.

Bios Version is IBM- 1001

Current time is 16:48:36 12/05/06.

2 Intel x86 level 15, 3600 Mhz processor (s).

Windows NT 5.2 Build 3790 CSD Service Pack 1.

Memory

MemoryLoad = 74%

Total Physical = 3327 MB

Available Physical = 858 MB

Total Page File = 9318 MB

Available Page File = 7058 MB

Total Virtual = 2047 MB

Available Virtual = 274 MB

**Dump thread - spid = 132, PSS = 0x71E09588, EC = 0x71E09590

***Stack Dump being sent to E:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump0178.txt

* *******************************************************************************

*

* BEGIN STACK DUMP:

*12/05/06 16:48:36 spid 132

*

* Location:lckmgr.cpp:10820

* Expression:GetLocalLockPartition () == xactLockInfo->GetLocalLockPartition ()

* SPID:132

* Process ID:2436

*

* Input Buffer 255 bytes -

*?16 00 00 00 12 00 00 00 02 00 01 00 00 00 84 00 00 00

*???dD 01 00 00 00 ff ff 0a 00 02 00 00 00 e7 64 09 09 04 d0

*4dS E L E C T00 34 64 09 20 00 53 00 45 00 4c 00 45 00 43 00 54 00

*TAbleA (*) 20 00 54 00 48 00 69 00 73 00 74 00 6f 00 72 00 69 00

(*) There is a query here that I have excluded in the message

*

*MODULEBASEENDSIZE

* sqlservr0100000002BA7FFF01ba8000

* ntdll7C9100007C9D3FFF000c4000

* kernel327C8000007C90BFFF0010c000

* MSVCR8078130000781CAFFF0009b000

* msvcrt77B9000077BE9FFF0005a000

* MSVCP807C4200007C4A6FFF00087000

* ADVAPI3277D9000077E3DFFF000ae000

* RPCRT477C4000077CDEFFF0009f000

* USER3277F4000077FD1FFF00092000

* GDI3277BF000077C37FFF00048000

* CRYPT32760D000076164FFF00095000

* MSASN1760B0000760C1FFF00012000

* Secur3276E7000076E82FFF00013000

* MSWSOCK71970000719B1FFF00042000

* WS2_3271A5000071A66FFF00017000

* WS2HELP71A4000071A47FFF00008000

* USERENV7684000076904FFF000c5000

* opends60333E0000333E6FFF00007000

* NETAPI3271A9000071AE7FFF00058000

* SHELL327C9E00007D1EAFFF0080b000

* SHLWAPI77EE000077F31FFF00052000

* comctl327736000077462FFF00103000

* EntApi3700000037012FFF00013000

* PSAPI76A9000076A9AFFF0000b000

* WININET779D000077A78FFF000a9000

* OLEAUT3277CF000077D7BFFF0008c000

* ole327751000077643FFF00134000

* instapi4806000048069FFF 0000a000

* CLUSAPI74CF000074D01FFF00012000

* RESUTILS74E0000074E12FFF00013000

* sqlevn704F6100004F7A0FFF00191000

* SQLOS344D0000344D4FFF00005000

* rsaenh680000006802EFFF0002f000

* AUTHZ76B6000076B73FFF00014000

* MSCOREE340C000034104FFF00045000

* msv1_076BB000076BD6FFF00027000

* iphlpapi76C1000076C29FFF0001a000

* kerberos3433000034387FFF00058000

* cryptdll766000007660BFFF0000c000

* schannel7667000076696FFF00027000

* COMRES76F30000770BCFFF0018d000

* XOLEHLP343F0000343F5FFF00006000

* MSDTCPRX3440000034477FFF00078000

* msvcp60780C000078120FFF00061000

* MTXCLU74E5000074E68FFF00019000

* VERSION77B8000077B87FFF00008000

* WSOCK3271A0000071A09FFF0000a000

* DNSAPI76DF000076E1EFFF0002f000

* winrnr76E9000076E96FFF00007000

* WLDAP3276E3000076E5EFFF0002f000

* rasadhlp76EA000076EA7FFF00008000

* hnetcfg36190000361E8FFF00059000

* wshtcpip7193000071937FFF00008000

* security3634000036343FFF00004000

* msfte36A6000036CB7FFF00258000

* dbghelp36D0000036E17FFF00118000

* WINTRUST76AD000076AFAFFF 0002b000

* imagehlp76B3000076B58FFF00029000

* dssenh6810000068123FFF00024000

* NTMARTA777B0000777D1FFF00022000

* SAMLIB36FE000036FEEFFF0000f000

* ntdsapi7661000076624FFF00015000

* xpsp2res61BF000061EBFFFF002d0000

* CLBCatQ77650000776D2FFF00083000

* sqlncli61EC0000620E1FFF00222000

* COMCTL3277E4000077ED6FFF00097000

* comdlg32761D000076218FFF00049000

* SQLNCLIR007C0000007F2FFF00033000

* msftepxy621F000062204FFF00015000

* xpsqlbot6286000062865FFF00006000

* xpstar9062880000628C4FFF00045000

* SQLSCM90628E0000628E8FFF00009000

* ODBC32629000006293CFFF0003d000

* BatchParser90629400006295DFFF0001e000

* SQLSVC906297000062989FFF0001a000

* SqlResourceLoader629A0000629A5FFF00006000

* ATL807C6300007C64AFFF0001b000

* odbcint62B0000062B17FFF00018000

* SQLSVC9062B2000062B22FFF00003000

* xpstar9062B3000062B55FFF00026000

* xplog7062B6000062B6BFFF0000c000

* xplog7062B8000062B82FFF00003000

* oledb32631B000063228FFF00079000

* MSDART6323000063249FFF0001a000

* OLEDB32R634D0000634E1FFF00012000

* activeds76D1000076D42FFF 00033000

* adsldpc76CE000076D06FFF00027000

* credui76AA000076ACDFFF0002e000

* ATL769A0000769B7FFF00018000

* adsldp711100007113DFFF0002e000

* SXS75CB000075D6BFFF000bc000

* dbghelp65D4000065E52FFF00113000

*

*Edi: 6610BCB8:636A19003E3A604062F1A0406610D8AD0279E9003E3A63D8

*Esi: 00000000:

*Eax: 6610BB9C:000042AC00000000000000007C815E02000000007C931B34

*Ebx: 0000003F:

*Ecx: 6610C20C:00000000000100070000000000740072636A19046610BBCC

*Edx: 0000003D:

*Eip: 7C815E02:10C2C95E90909000A164909000000018C334408B891C428B

*Ebp: 6610BBEC:6610BC3002172CE4000042AC000000000000000000000000

*SegCs: 0000001B:

*EFlags: 00000246:

*Esp: 6610BB98:71E09588000042AC00000000000000007C815E0200000000

*SegSs: 78130023:000000000000000000000000000000000000000000000000

* *******************************************************************************

* -

* Short Stack Dump

7C815E02 Module(kernel32+00015E02)

02172CE4 Module(sqlservr+01172CE4)

02176BA0 Module(sqlservr+01176BA0)

02019506 Module(sqlservr+01019506)

015738EE Module(sqlservr+005738EE)

021B15B6 Module(sqlservr+011B15B6)

0163DD36 Module(sqlservr+0063DD36)

010E9FA3 Module(sqlservr+000E9FA3)

010B0F5F Module(sqlservr+000B0F5F)

0102C5F8 Module(sqlservr+0002C5F8)

01BEE12B Module(sqlservr+00BEE12B)

01BF2BCB Module(sqlservr+00BF2BCB)

01BF353D Module(sqlservr+00BF353D)

010438E5 Module(sqlservr+000438E5)

01041C35 Module(sqlservr+00041C35)

0100889F Module(sqlservr+0000889F)

010089C5 Module(sqlservr+000089C5)

010086E7 Module(sqlservr+000086E7)

010D764A Module(sqlservr+000D764A)

010D7B71 Module(sqlservr+000D7B71)

010D746E Module(sqlservr+000D746E)

010D83F0 Module(sqlservr+000D83F0)

781329AA Module(MSVCR80+000029AA)

78132A36 Module(MSVCR80+00002A36)

PSS @.0x71E09588

CSession @.0x71E08278

--

m_spid = 132m_cRef = 12m_rgcRefType[0] = 1

m_rgcRefType[1] = 1m_rgcRefType[2] = 9m_rgcRefType[3] = 1

m_rgcRefType[4] = 0m_rgcRefType[5] = 0m_pmo = 0x71E08040

m_pstackBhfPool = 0x00000000m_dwLoginFlags = 0x03e0m_fBackground = 0

m_fClientRequestConnReset = 0m_fUserProc = -1m_fConnReset = 0

m_fIsConnReset = 0m_fInLogin = 0m_fReplRelease = 0

m_fKill = 0m_ulLoginStamp = 3105683m_eclClient = 5

m_protType = 5m_hHttpToken = FFFFFFFF

m_pV7LoginRec

00000000:18010000 02000972 401f0000 00000006 400c0000 ?.......r@........@....

00000014:00000000 e0030000 00000000 00000000 5e000400 ?................^...

00000028:66000200 6a000000 7a001c00 b2000c00 ca000000 ?f...j...z...........

0000003C:ca001c00 02010000 02010b00 60f120db ad481801 ?............`. ..H..

00000050:00001801 00001801 00000000 0000???????????????..............

CPhysicalConnection @.0x71E08188

-

m_pPhyConn->m_pmo = 0x71E08040m_pPhyConn->m_pNetConn = 0x71E08788m_pPhyConn->m_pConnList = 0x71E08260

m_pPhyConn->m_pSess = 0x71E08278m_pPhyConn->m_fTracked = -1m_pPhyConn->m_cbPacketsize = 8000

m_pPhyConn->m_fMars = 0m_pPhyConn->m_fKill = 0

CBatch @.0x71E08A90

m_pSess = 0x71E08278m_pConn = 0x71E089F0m_cRef = 3

m_rgcRefType[0] = 1m_rgcRefType[1] = 1m_rgcRefType[2] = 1

m_rgcRefType[3] = 0m_rgcRefType[4] = 0m_pTask = 0x006F9D38

EXCEPT (null) @.0x6610B4AC

-

exc_number = 0exc_severity = 0exc_func = 0x023D96B0

Task @.0x006F9D38

-

CPU Ticks used (ms) = 1Task State = 2

WAITINFO_INTERNAL: WaitResource = 0x00000000WAITINFO_INTERNAL: WaitType = 0x0

WAITINFO_INTERNAL: WaitSpinlock = 0x00000000SchedulerId = 0x0

ThreadId = 0x444m_state = 0m_eAbortSev = 0

EC @.0x71E09590

--

spid = 132ecid = 0ec_stat = 0x0

ec_stat2 = 0x40ec_atomic = 0x4__fSubProc = 1

ec_dbccContext = 0x00000000__pSETLS = 0x71E08A30__pSEParams = 0x71E08CD0

__pDbLocks = 0x71E09878

SEInternalTLS @.0x71E08A30

-

m_flags = 0m_TLSstatus = 3m_owningTask = 0x006F9D38

m_activeHeapDatasetList = 0x71E08A30m_activeIndexDatasetList = 0x71E08A38

SEParams @.0x71E08CD0

--

m_lockTimeout = -1m_isoLevel = 1048576m_logDontReplicate = 0

m_neverReplicate = 0m_XactWorkspace = 0x03F78940m_pSessionLocks = 0x71E09A88

m_pDbLocks = 0x71E09878m_execStats = 0x3F867018m_pAllocFileLimit = 0x00000000

Does anybody know what’s going on?

I will be very appreciate if someone can help me to solve this problem. Thank you!

Hi,

We would like to investigate this issue. Can you please file a bug via http://connect.microsoft.com/sql. Please upload SqlDump0178.mdmp and SqlDump0178.txt there as well.

Thanks, Ron D.

|||any luck on this one. We are seeing the same error.

Thanks,
Siva|||

Make sure the table being accessed have proper CLUSTERED index.

BEGIN STACK DUMP error in log viewer

Hi

We are having problems with our application that uses SQL Server 2005 in a cluster environment. Sometimes the system stops answering and registers in the log viewer the following error:

=====================================================================

BugCheck Dump

=====================================================================

This file is generated by Microsoft SQL Server

version 9.00.1399.06

upon detection of fatal unexpected error. Please return this file, the query or program that produced the bugcheck, the database and the error log, and any other pertinent information with a Service Request.

Computer type is AT/AT COMPATIBLE.

Bios Version is IBM- 1001

Current time is 16:48:36 12/05/06.

2 Intel x86 level 15, 3600 Mhz processor (s).

Windows NT 5.2 Build 3790 CSD Service Pack 1.

Memory

MemoryLoad = 74%

Total Physical = 3327 MB

Available Physical = 858 MB

Total Page File = 9318 MB

Available Page File = 7058 MB

Total Virtual = 2047 MB

Available Virtual = 274 MB

**Dump thread - spid = 132, PSS = 0x71E09588, EC = 0x71E09590

***Stack Dump being sent to E:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump0178.txt

* *******************************************************************************

*

* BEGIN STACK DUMP:

*12/05/06 16:48:36 spid 132

*

* Location:lckmgr.cpp:10820

* Expression:GetLocalLockPartition () == xactLockInfo->GetLocalLockPartition ()

* SPID:132

* Process ID:2436

*

* Input Buffer 255 bytes -

*?16 00 00 00 12 00 00 00 02 00 01 00 00 00 84 00 00 00

*???dD 01 00 00 00 ff ff 0a 00 02 00 00 00 e7 64 09 09 04 d0

*4dS E L E C T00 34 64 09 20 00 53 00 45 00 4c 00 45 00 43 00 54 00

*TAbleA (*) 20 00 54 00 48 00 69 00 73 00 74 00 6f 00 72 00 69 00

(*) There is a query here that I have excluded in the message

*

*MODULEBASEENDSIZE

* sqlservr0100000002BA7FFF01ba8000

* ntdll7C9100007C9D3FFF000c4000

* kernel327C8000007C90BFFF0010c000

* MSVCR8078130000781CAFFF0009b000

* msvcrt77B9000077BE9FFF0005a000

* MSVCP807C4200007C4A6FFF00087000

* ADVAPI3277D9000077E3DFFF000ae000

* RPCRT477C4000077CDEFFF0009f000

* USER3277F4000077FD1FFF00092000

* GDI3277BF000077C37FFF00048000

* CRYPT32760D000076164FFF00095000

* MSASN1760B0000760C1FFF00012000

* Secur3276E7000076E82FFF00013000

* MSWSOCK71970000719B1FFF00042000

* WS2_3271A5000071A66FFF00017000

* WS2HELP71A4000071A47FFF00008000

* USERENV7684000076904FFF000c5000

* opends60333E0000333E6FFF00007000

* NETAPI3271A9000071AE7FFF00058000

* SHELL327C9E00007D1EAFFF0080b000

* SHLWAPI77EE000077F31FFF00052000

* comctl327736000077462FFF00103000

* EntApi3700000037012FFF00013000

* PSAPI76A9000076A9AFFF0000b000

* WININET779D000077A78FFF000a9000

* OLEAUT3277CF000077D7BFFF0008c000

* ole327751000077643FFF00134000

* instapi4806000048069FFF 0000a000

* CLUSAPI74CF000074D01FFF00012000

* RESUTILS74E0000074E12FFF00013000

* sqlevn704F6100004F7A0FFF00191000

* SQLOS344D0000344D4FFF00005000

* rsaenh680000006802EFFF0002f000

* AUTHZ76B6000076B73FFF00014000

* MSCOREE340C000034104FFF00045000

* msv1_076BB000076BD6FFF00027000

* iphlpapi76C1000076C29FFF0001a000

* kerberos3433000034387FFF00058000

* cryptdll766000007660BFFF0000c000

* schannel7667000076696FFF00027000

* COMRES76F30000770BCFFF0018d000

* XOLEHLP343F0000343F5FFF00006000

* MSDTCPRX3440000034477FFF00078000

* msvcp60780C000078120FFF00061000

* MTXCLU74E5000074E68FFF00019000

* VERSION77B8000077B87FFF00008000

* WSOCK3271A0000071A09FFF0000a000

* DNSAPI76DF000076E1EFFF0002f000

* winrnr76E9000076E96FFF00007000

* WLDAP3276E3000076E5EFFF0002f000

* rasadhlp76EA000076EA7FFF00008000

* hnetcfg36190000361E8FFF00059000

* wshtcpip7193000071937FFF00008000

* security3634000036343FFF00004000

* msfte36A6000036CB7FFF00258000

* dbghelp36D0000036E17FFF00118000

* WINTRUST76AD000076AFAFFF 0002b000

* imagehlp76B3000076B58FFF00029000

* dssenh6810000068123FFF00024000

* NTMARTA777B0000777D1FFF00022000

* SAMLIB36FE000036FEEFFF0000f000

* ntdsapi7661000076624FFF00015000

* xpsp2res61BF000061EBFFFF002d0000

* CLBCatQ77650000776D2FFF00083000

* sqlncli61EC0000620E1FFF00222000

* COMCTL3277E4000077ED6FFF00097000

* comdlg32761D000076218FFF00049000

* SQLNCLIR007C0000007F2FFF00033000

* msftepxy621F000062204FFF00015000

* xpsqlbot6286000062865FFF00006000

* xpstar9062880000628C4FFF00045000

* SQLSCM90628E0000628E8FFF00009000

* ODBC32629000006293CFFF0003d000

* BatchParser90629400006295DFFF0001e000

* SQLSVC906297000062989FFF0001a000

* SqlResourceLoader629A0000629A5FFF00006000

* ATL807C6300007C64AFFF0001b000

* odbcint62B0000062B17FFF00018000

* SQLSVC9062B2000062B22FFF00003000

* xpstar9062B3000062B55FFF00026000

* xplog7062B6000062B6BFFF0000c000

* xplog7062B8000062B82FFF00003000

* oledb32631B000063228FFF00079000

* MSDART6323000063249FFF0001a000

* OLEDB32R634D0000634E1FFF00012000

* activeds76D1000076D42FFF 00033000

* adsldpc76CE000076D06FFF00027000

* credui76AA000076ACDFFF0002e000

* ATL769A0000769B7FFF00018000

* adsldp711100007113DFFF0002e000

* SXS75CB000075D6BFFF000bc000

* dbghelp65D4000065E52FFF00113000

*

*Edi: 6610BCB8:636A19003E3A604062F1A0406610D8AD0279E9003E3A63D8

*Esi: 00000000:

*Eax: 6610BB9C:000042AC00000000000000007C815E02000000007C931B34

*Ebx: 0000003F:

*Ecx: 6610C20C:00000000000100070000000000740072636A19046610BBCC

*Edx: 0000003D:

*Eip: 7C815E02:10C2C95E90909000A164909000000018C334408B891C428B

*Ebp: 6610BBEC:6610BC3002172CE4000042AC000000000000000000000000

*SegCs: 0000001B:

*EFlags: 00000246:

*Esp: 6610BB98:71E09588000042AC00000000000000007C815E0200000000

*SegSs: 78130023:000000000000000000000000000000000000000000000000

* *******************************************************************************

* -

* Short Stack Dump

7C815E02 Module(kernel32+00015E02)

02172CE4 Module(sqlservr+01172CE4)

02176BA0 Module(sqlservr+01176BA0)

02019506 Module(sqlservr+01019506)

015738EE Module(sqlservr+005738EE)

021B15B6 Module(sqlservr+011B15B6)

0163DD36 Module(sqlservr+0063DD36)

010E9FA3 Module(sqlservr+000E9FA3)

010B0F5F Module(sqlservr+000B0F5F)

0102C5F8 Module(sqlservr+0002C5F8)

01BEE12B Module(sqlservr+00BEE12B)

01BF2BCB Module(sqlservr+00BF2BCB)

01BF353D Module(sqlservr+00BF353D)

010438E5 Module(sqlservr+000438E5)

01041C35 Module(sqlservr+00041C35)

0100889F Module(sqlservr+0000889F)

010089C5 Module(sqlservr+000089C5)

010086E7 Module(sqlservr+000086E7)

010D764A Module(sqlservr+000D764A)

010D7B71 Module(sqlservr+000D7B71)

010D746E Module(sqlservr+000D746E)

010D83F0 Module(sqlservr+000D83F0)

781329AA Module(MSVCR80+000029AA)

78132A36 Module(MSVCR80+00002A36)

PSS @.0x71E09588

CSession @.0x71E08278

--

m_spid = 132m_cRef = 12m_rgcRefType[0] = 1

m_rgcRefType[1] = 1m_rgcRefType[2] = 9m_rgcRefType[3] = 1

m_rgcRefType[4] = 0m_rgcRefType[5] = 0m_pmo = 0x71E08040

m_pstackBhfPool = 0x00000000m_dwLoginFlags = 0x03e0m_fBackground = 0

m_fClientRequestConnReset = 0m_fUserProc = -1m_fConnReset = 0

m_fIsConnReset = 0m_fInLogin = 0m_fReplRelease = 0

m_fKill = 0m_ulLoginStamp = 3105683m_eclClient = 5

m_protType = 5m_hHttpToken = FFFFFFFF

m_pV7LoginRec

00000000:18010000 02000972 401f0000 00000006 400c0000 ?.......r@........@....

00000014:00000000 e0030000 00000000 00000000 5e000400 ?................^...

00000028:66000200 6a000000 7a001c00 b2000c00 ca000000 ?f...j...z...........

0000003C:ca001c00 02010000 02010b00 60f120db ad481801 ?............`. ..H..

00000050:00001801 00001801 00000000 0000???????????????..............

CPhysicalConnection @.0x71E08188

-

m_pPhyConn->m_pmo = 0x71E08040m_pPhyConn->m_pNetConn = 0x71E08788m_pPhyConn->m_pConnList = 0x71E08260

m_pPhyConn->m_pSess = 0x71E08278m_pPhyConn->m_fTracked = -1m_pPhyConn->m_cbPacketsize = 8000

m_pPhyConn->m_fMars = 0m_pPhyConn->m_fKill = 0

CBatch @.0x71E08A90

m_pSess = 0x71E08278m_pConn = 0x71E089F0m_cRef = 3

m_rgcRefType[0] = 1m_rgcRefType[1] = 1m_rgcRefType[2] = 1

m_rgcRefType[3] = 0m_rgcRefType[4] = 0m_pTask = 0x006F9D38

EXCEPT (null) @.0x6610B4AC

-

exc_number = 0exc_severity = 0exc_func = 0x023D96B0

Task @.0x006F9D38

-

CPU Ticks used (ms) = 1Task State = 2

WAITINFO_INTERNAL: WaitResource = 0x00000000WAITINFO_INTERNAL: WaitType = 0x0

WAITINFO_INTERNAL: WaitSpinlock = 0x00000000SchedulerId = 0x0

ThreadId = 0x444m_state = 0m_eAbortSev = 0

EC @.0x71E09590

--

spid = 132ecid = 0ec_stat = 0x0

ec_stat2 = 0x40ec_atomic = 0x4__fSubProc = 1

ec_dbccContext = 0x00000000__pSETLS = 0x71E08A30__pSEParams = 0x71E08CD0

__pDbLocks = 0x71E09878

SEInternalTLS @.0x71E08A30

-

m_flags = 0m_TLSstatus = 3m_owningTask = 0x006F9D38

m_activeHeapDatasetList = 0x71E08A30m_activeIndexDatasetList = 0x71E08A38

SEParams @.0x71E08CD0

--

m_lockTimeout = -1m_isoLevel = 1048576m_logDontReplicate = 0

m_neverReplicate = 0m_XactWorkspace = 0x03F78940m_pSessionLocks = 0x71E09A88

m_pDbLocks = 0x71E09878m_execStats = 0x3F867018m_pAllocFileLimit = 0x00000000

Does anybody know what’s going on?

I will be very appreciate if someone can help me to solve this problem. Thank you!

Hi,

We would like to investigate this issue. Can you please file a bug via http://connect.microsoft.com/sql. Please upload SqlDump0178.mdmp and SqlDump0178.txt there as well.

Thanks, Ron D.

|||any luck on this one. We are seeing the same error.

Thanks,
Siva

|||Make sure the table being accessed have proper CLUSTERED index.

Friday, February 10, 2012

Begginer needing help to resolve error.

Hi,
I hope one of you may be able to help me with a small probelm I have.
I get the following error:
Microsoft JET Database Engine error '80040e14'
Syntax error (missing operator) in query expression '[Date_Completed]
is NULL WHERE (([Customer] LIKE '%Davie%' OR [Address] LIKE '%Davie%'
OR [Post_Code] LIKE '%Davie%' OR [Telephone] LIKE '%Davie%' OR
[Mobile] LIKE '%Davie%' OR [Vehicle] LIKE '%Davie%' OR [Chassis_No]
LIKE '%Davie%' OR [Engine_No] LIKE ''.
/Jobsheet/Datalist.asp, line 227
The code that it highlights is this:
<%
' Set up Record Set
Set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = 3
rs.Open sSql, conn, 1, 2 --This line in particular --
nTotalRecs = rs.RecordCount
If nDisplayRecs <= 0 Then ' Display All Records
nDisplayRecs = nTotalRecs
End If
nStartRec = 1
SetUpStartRec() ' Set Up Start Record Position
%>
I would be much obliged if anyone can provide assistance.
Cheers,
PhillIs it Access query?
"FatboyDim" <webmaster@.pb-enterprises.co.uk> wrote in message
news:1183454575.272248.54230@.c77g2000hse.googlegroups.com...
> Hi,
> I hope one of you may be able to help me with a small probelm I have.
> I get the following error:
> Microsoft JET Database Engine error '80040e14'
> Syntax error (missing operator) in query expression '[Date_Completed]
> is NULL WHERE (([Customer] LIKE '%Davie%' OR [Address] LIKE '%Davie%'
> OR [Post_Code] LIKE '%Davie%' OR [Telephone] LIKE '%Davie%' OR
> [Mobile] LIKE '%Davie%' OR [Vehicle] LIKE '%Davie%' OR [Chassis_No]
> LIKE '%Davie%' OR [Engine_No] LIKE ''.
> /Jobsheet/Datalist.asp, line 227
>
> The code that it highlights is this:
> <%
> ' Set up Record Set
> Set rs = Server.CreateObject("ADODB.Recordset")
> rs.CursorLocation = 3
> rs.Open sSql, conn, 1, 2 --This line in particular --
> nTotalRecs = rs.RecordCount
> If nDisplayRecs <= 0 Then ' Display All Records
> nDisplayRecs = nTotalRecs
> End If
> nStartRec = 1
> SetUpStartRec() ' Set Up Start Record Position
> %>
> I would be much obliged if anyone can provide assistance.
> Cheers,
> Phill
>|||Yes,
It is an Access query.
On 3 Jul, 10:27, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Is it Access query?
> "FatboyDim" <webmas...@.pb-enterprises.co.uk> wrote in message
> news:1183454575.272248.54230@.c77g2000hse.googlegroups.com...
>
> > Hi,
> > I hope one of you may be able to help me with a small probelm I have.
> > I get the following error:
> > Microsoft JET Database Engine error '80040e14'
> > Syntax error (missing operator) in query expression '[Date_Completed]
> > is NULL WHERE (([Customer] LIKE '%Davie%' OR [Address] LIKE '%Davie%'
> > OR [Post_Code] LIKE '%Davie%' OR [Telephone] LIKE '%Davie%' OR
> > [Mobile] LIKE '%Davie%' OR [Vehicle] LIKE '%Davie%' OR [Chassis_No]
> > LIKE '%Davie%' OR [Engine_No] LIKE ''.
> > /Jobsheet/Datalist.asp, line 227
> > The code that it highlights is this:
> > <%
> > ' Set up Record Set
> > Set rs = Server.CreateObject("ADODB.Recordset")
> > rs.CursorLocation = 3
> > rs.Open sSql, conn, 1, 2 --This line in particular --
> > nTotalRecs = rs.RecordCount
> > If nDisplayRecs <= 0 Then ' Display All Records
> > nDisplayRecs = nTotalRecs
> > End If
> > nStartRec = 1
> > SetUpStartRec() ' Set Up Start Record Position
> > %>
> > I would be much obliged if anyone can provide assistance.
> > Cheers,
> > Phill- Hide quoted text -
> - Show quoted text -|||Hi
Its SQL Server news group. You will be better of ask the question in Access
group
"FatboyDim" <webmaster@.pb-enterprises.co.uk> wrote in message
news:1183455450.654505.196030@.n2g2000hse.googlegroups.com...
> Yes,
> It is an Access query.
> On 3 Jul, 10:27, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> Is it Access query?
>> "FatboyDim" <webmas...@.pb-enterprises.co.uk> wrote in message
>> news:1183454575.272248.54230@.c77g2000hse.googlegroups.com...
>>
>> > Hi,
>> > I hope one of you may be able to help me with a small probelm I have.
>> > I get the following error:
>> > Microsoft JET Database Engine error '80040e14'
>> > Syntax error (missing operator) in query expression '[Date_Completed]
>> > is NULL WHERE (([Customer] LIKE '%Davie%' OR [Address] LIKE '%Davie%'
>> > OR [Post_Code] LIKE '%Davie%' OR [Telephone] LIKE '%Davie%' OR
>> > [Mobile] LIKE '%Davie%' OR [Vehicle] LIKE '%Davie%' OR [Chassis_No]
>> > LIKE '%Davie%' OR [Engine_No] LIKE ''.
>> > /Jobsheet/Datalist.asp, line 227
>> > The code that it highlights is this:
>> > <%
>> > ' Set up Record Set
>> > Set rs = Server.CreateObject("ADODB.Recordset")
>> > rs.CursorLocation = 3
>> > rs.Open sSql, conn, 1, 2 --This line in particular --
>> > nTotalRecs = rs.RecordCount
>> > If nDisplayRecs <= 0 Then ' Display All Records
>> > nDisplayRecs = nTotalRecs
>> > End If
>> > nStartRec = 1
>> > SetUpStartRec() ' Set Up Start Record Position
>> > %>
>> > I would be much obliged if anyone can provide assistance.
>> > Cheers,
>> > Phill- Hide quoted text -
>> - Show quoted text -
>