eternal lock?

Hi,

does sb recognize (aspects of) the following problem? Or better, know a
solution or direction to search?

At work I've inherited a series of delphi applications that access a common
database using SQL Server 2000 (sp3, sp4 update in preparation due to this
problem). Applications run on one server, db on the second. Both are dual
xeon 2.8 or 3 GHz with 2 GB ram. The apps use about one GB (800MB) memory,
the db too (is configured to use more, 1.8GB, but doesn't.) The db is also
replicated to a third machine.

The problem is that sometimes, after a cascade of query timeouts (recorded
by the apps in the eventlog, cause is the commandtime set on all
components), the whole applications seems to stop responding. Restarting the
apps doesn't solve the problem, rebooting the application server does, which
leads me to believe the problem is in MDAC on the app server? The app server
has an own unused sql server instance (used in migrations) btw.

The problems occur during busier times, but nothing spectacular (up to
ten-thousand of queries per hour maybe).

The problem sometimes goes away after a few minutes in about half of the
cases, but if not, it seems perpetual till reboot (at least 13 hours).

Another notable point is that not all queries time out, most writes (which
append a row or change a row) seem to go ok, same with selects that get a
record for a primary key value, and pure read selects flagged with NOLOCK.
The queries that go wrong all get lists that touch central tables (either
directly or via joins).

The behaviour is consistent with an external row/page lock somewhere that
doesn't go away.

Database layout is fairly uninteresting. A db or 3 (one read-only), the
larger one having say 30 tables,

cardinality of the tables is not that much of a problem. Tens of thousands
of rows max, except a logging table with maybe 300000 tuples. (which is only
traversed for mgmnt info, and not during busy hours) No binary or other
disproportionally large fields, Most db access done based on primary/foreign
keys.

Other details:
- Replication overhead can be considered low (we are talking about
thousand(s) mutationsper day, nothing significant.
- commandtimeout on all db components is set (to 30s)
- all cursors are clientside, except the component used for getting lists,
that has
location=cluseserver; cursortype=ctopenforwardonly;
cachesize=250; locktype=readonly
- the apps are not threaded.
- D6 patched with all three patches

Thnks in advanceMarco van de Voort (marcov@.stack.nl) writes:
> The problem is that sometimes, after a cascade of query timeouts
> (recorded by the apps in the eventlog, cause is the commandtime set on
> all components), the whole applications seems to stop responding.
> Restarting the apps doesn't solve the problem, rebooting the application
> server does, which leads me to believe the problem is in MDAC on the app
> server? The app server has an own unused sql server instance (used in
> migrations) btw.

Have you examined blocking?

With this superficial information about the system it is difficult to
say for sure, but it does smell of a well-known gotcha (been there, done
that myself).

To wit, if a query times out, and there is a transaction in progress,
the transaction is not rolled back automatically. It is irrelevant
whether the transaction was started prior to the submission of the
query batch, or started within the query batch that timed out.

The application must handle this by submitting

IF @.@.trancount > 0 ROLLBACK TRANSACTION

in case of a query timeout.

If the application fails to observe this, the result is chaos.
Transactions never commits, which means that processes keeps on
acquiring more and more locks, and you get blocking galore. And
when you finally restart something, you lose all the updates...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On 2006-05-22, Erland Sommarskog <esquel@.sommarskog.se> wrote:
> To wit, if a query times out, and there is a transaction in progress,
> the transaction is not rolled back automatically. It is irrelevant
> whether the transaction was started prior to the submission of the
> query batch, or started within the query batch that timed out.
> The application must handle this by submitting
> IF @.@.trancount > 0 ROLLBACK TRANSACTION

I call the rollback of the relevant ADO connection on the exception caused
by the timeout.|||Marco van de Voort (marcov@.stack.nl) writes:
>> To wit, if a query times out, and there is a transaction in progress,
>> the transaction is not rolled back automatically. It is irrelevant
>> whether the transaction was started prior to the submission of the
>> query batch, or started within the query batch that timed out.
>>
>> The application must handle this by submitting
>>
>> IF @.@.trancount > 0 ROLLBACK TRANSACTION
> I call the rollback of the relevant ADO connection on the exception caused
> by the timeout.

So, did you investiagate if you have any blocking?

Also, I have you examined the SQL Server error log?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On 2006-05-23, Erland Sommarskog <esquel@.sommarskog.se> wrote:
> Marco van de Voort (marcov@.stack.nl) writes:
>>> To wit, if a query times out, and there is a transaction in progress,
>>> the transaction is not rolled back automatically. It is irrelevant
>>> whether the transaction was started prior to the submission of the
>>> query batch, or started within the query batch that timed out.
>>>
>>> The application must handle this by submitting
>>>
>>> IF @.@.trancount > 0 ROLLBACK TRANSACTION
>>
>> I call the rollback of the relevant ADO connection on the exception caused
>> by the timeout.

(for the record: I already did this all the time, it is not a change)

> So, did you investiagate if you have any blocking?

It occurs less than once a month (unfortunately on a painful moment). IOW, I
can't reproduce it at will. Which is why I asked if sb recognized the
problems.

> Also, I have you examined the SQL Server error log?

Yes, nothing. But maybe my logging settings are wrong.|||Marco van de Voort (marcov@.stack.nl) writes:
> It occurs less than once a month (unfortunately on a painful moment).
> IOW, I can't reproduce it at will. Which is why I asked if sb recognized
> the problems.

I'm afraid that without access to the real situation, it is difficult
to say that much intelligent. The fact that it occurs only rarely,
indicates that it is not a general problem with unhandled query timeouts.
But it still smells of transactions that are not committed/rolled back
properly.

The only thing I can suggest is that when it sets try to collect as
much data you can before the reboot panic sets in. I have a stored
procedure on my web site which is good for this purpose:
http://www.sommarskog.se/sqlutil/aba_lockinfo.html.

>> Also, I have you examined the SQL Server error log?
> Yes, nothing. But maybe my logging settings are wrong.

It's always good to have trace flags 1204 and 3605 enabled to get
deadlock information written to the error log, but that was not I
had in mind. I was thinking of error 17883, which indicates that
SQL Server is in bad shape at the moment. This diagnostic message
was added in SQL 2000 SP3, and was augmented in some hotfixes soon
thereafter. SP4 has an even wider set of these messages.

The fact that you don't have message 17883 in the log amplifies the
impression that the problem is related to the application.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

Estimating Database Size Issue

All,
I am following the guidelines laid out on the following page to
calculate an estimated size for a database.
http://msdn2.microsoft.com/en-us/library/ms178085.aspx.
However, I am noticing something weird. Because
Index_Rows_Per_Page is calculated by dividing the (Index_Row_Size +
2), the MORE variable and non-variable keys you add to your table, the
SMALLER the size of the database gets. Does this make sense to anyone
because it doesn't make sense to me.
Here's my database:
Num_Rows = 75,000,000
Num_Cols = 3
Fixed_Data_Size = 8
Num_Variable_Cols = 0
Max_Var_Size = 0
Num_Key_Cols = 2
Fixed_Key_Size = 4
Num_Variable_Key_Cols = 0
Max_Var_Key_Size = 0
Num_Nullable_Key_Cols = 0
Using this information, I get 438.07GB as the size of my
database.
If I change the number of Fixed_Key_Size to 6, the size of the
database drops to 335.25GB.
Again, this doesn't make sense to me. If quadruple checked my
math against what's in the URL above, and it appears to match what is
in the document. Is Microsoft's calculation wrong? Surely not. Maybe I
missed something? Are you guys getting the same results based off of
my info above?
Thanks,
JeremyI remember when I first read this article I felt pretty overwhelmed.
I've found the many scripts and articles from www.sqlservercentral.com
(and www.sql-server-perfromance.com I think) to be much more
intuitive. I know this doesn't really answer your question, its more
of a suggestion.
Erik|||Thanks Erik. I was looking further through the code I wrote, value by
value, and it turns out that it's either the LOG function or the
Summation function (or a combination of both) that is making the size
decrease when the byte size of indexed keys increases. But it still
doesn't make sense.
I'll take a look at those links though, thanks!
Jeremysql

Estimating Database Size Issue

All,
I am following the guidelines laid out on the following page to
calculate an estimated size for a database.
http://msdn2.microsoft.com/en-us/library/ms178085.aspx.
However, I am noticing something weird. Because
Index_Rows_Per_Page is calculated by dividing the (Index_Row_Size +
2), the MORE variable and non-variable keys you add to your table, the
SMALLER the size of the database gets. Does this make sense to anyone
because it doesn't make sense to me.
Here's my database:
Num_Rows = 75,000,000
Num_Cols = 3
Fixed_Data_Size = 8
Num_Variable_Cols = 0
Max_Var_Size = 0
Num_Key_Cols = 2
Fixed_Key_Size = 4
Num_Variable_Key_Cols = 0
Max_Var_Key_Size = 0
Num_Nullable_Key_Cols = 0
Using this information, I get 438.07GB as the size of my
database.
If I change the number of Fixed_Key_Size to 6, the size of the
database drops to 335.25GB.
Again, this doesn't make sense to me. If quadruple checked my
math against what's in the URL above, and it appears to match what is
in the document. Is Microsoft's calculation wrong? Surely not. Maybe I
missed something? Are you guys getting the same results based off of
my info above?
Thanks,
Jeremy
I remember when I first read this article I felt pretty overwhelmed.
I've found the many scripts and articles from www.sqlservercentral.com
(and www.sql-server-perfromance.com I think) to be much more
intuitive. I know this doesn't really answer your question, its more
of a suggestion.
Erik
|||Thanks Erik. I was looking further through the code I wrote, value by
value, and it turns out that it's either the LOG function or the
Summation function (or a combination of both) that is making the size
decrease when the byte size of indexed keys increases. But it still
doesn't make sense.
I'll take a look at those links though, thanks!
Jeremy

Escaping a comma in a LIKE statement

A simple question I'm sure...
I am having problems with the following statement:
Select * from data where
Company LIKE '%HEI,%'
It appears that the comma is being interpreted as something other than a nor
mal string value. When I remove the comma everything works fine. Is there a
way to escape the comma? I've spent a great deal of time looking for the ans
wer, apparently I am not lo
oking in the right places. Any help would be appreciated.Can you be a bit more specific, or show us a repro? Below statement work jus
t like expected, i.e., the comma
does not get any special treatment:
SELECT * FROM
(
SELECT 'HEL,oo' AS x
UNION
SELECT 'HELOO' AS x
) AS d
WHERE x LIKE 'HEL,%'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MSchlaud" <MSchlaud@.discussions.microsoft.com> wrote in message
news:CE4ECBB0-2DEB-479E-8277-736B3ECA2138@.microsoft.com...
> A simple question I'm sure...
> I am having problems with the following statement:
> Select * from data where
> Company LIKE '%HEI,%'
> It appears that the comma is being interpreted as something other than a normal st
ring value. When I remove
the comma everything works fine. Is there a way to escape the comma? I've sp
ent a great deal of time looking
for the answer, apparently I am not looking in the right places. Any help wo
uld be appreciated.|||Sure. Here's the full query and the resulting error message:
Query:
Select * From data
WHERE Company like '%HEI %',
And RequestDate BETWEEN {d '2004-06-01'} AND {d '2004-06-15'}
Here's the error message:
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect
syntax near ','.
"Tibor Karaszi" wrote:

> Can you be a bit more specific, or show us a repro? Below statement work j
ust like expected, i.e., the comma
> does not get any special treatment:
> SELECT * FROM
> (
> SELECT 'HEL,oo' AS x
> UNION
> SELECT 'HELOO' AS x
> ) AS d
> WHERE x LIKE 'HEL,%'
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "MSchlaud" <MSchlaud@.discussions.microsoft.com> wrote in message
> news:CE4ECBB0-2DEB-479E-8277-736B3ECA2138@.microsoft.com...
> the comma everything works fine. Is there a way to escape the comma? I've
spent a great deal of time looking
> for the answer, apparently I am not looking in the right places. Any help
would be appreciated.
>
>|||I see the problem. Don't know how I missed the comma. Thanks anyway
"MSchlaud" wrote:
[vbcol=seagreen]
> Sure. Here's the full query and the resulting error message:
> Query:
> Select * From data
> WHERE Company like '%HEI %',
> And RequestDate BETWEEN {d '2004-06-01'} AND {d '2004-06-15'}
> Here's the error message:
> ODBC Error Code = 37000 (Syntax error or access violation)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorre
ct syntax near ','.
>
> "Tibor Karaszi" wrote:
>|||On Wed, 16 Jun 2004 13:31:01 -0700, MSchlaud wrote:

>Sure. Here's the full query and the resulting error message:
>Query:
>Select * From data
>WHERE Company like '%HEI %',
>And RequestDate BETWEEN {d '2004-06-01'} AND {d '2004-06-15'}
>Here's the error message:
>ODBC Error Code = 37000 (Syntax error or access violation)
>[Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrec
t syntax near ','.
>
Hi MSchlaud,
You have the comma outside of the apostrophe.
In your original question, you said Company LIKE '%HEI,%' (with the comma
as part of the LIKE argument).
In the above, you have Company LIKE '%HEI% ', (with a space between the
apostrophes and a comma after the closing apoostrophe)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I'm sorry but that is not something I can execute as you didn't post the CRE
ATE TABLE and INSERT statements.
I.e., I cannot try to reproduce that error message.
However, trying to parse only give me that you have a comma *after* the stri
ng definition (outside the
string), before the word AND. Try below:
SELECT * FROM data
WHERE Company LIKE '%HEI %'
AND RequestDate BETWEEN {d '2004-06-01'} AND {d '2004-06-15'}
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MSchlaud" <MSchlaud@.discussions.microsoft.com> wrote in message
news:B2396540-2022-4711-A1F2-331F46BD1DC2@.microsoft.com...[vbcol=seagreen]
> Sure. Here's the full query and the resulting error message:
> Query:
> Select * From data
> WHERE Company like '%HEI %',
> And RequestDate BETWEEN {d '2004-06-01'} AND {d '2004-06-15'}
> Here's the error message:
> ODBC Error Code = 37000 (Syntax error or access violation)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorre
ct syntax near ','.
>
> "Tibor Karaszi" wrote:
>
comma[vbcol=seagreen]
remove[vbcol=seagreen]
looking[vbcol=seagreen]|||Yes, I see that now. Thanks for the quick responses.
"Hugo Kornelis" wrote:

> On Wed, 16 Jun 2004 13:31:01 -0700, MSchlaud wrote:
>
> Hi MSchlaud,
> You have the comma outside of the apostrophe.
> In your original question, you said Company LIKE '%HEI,%' (with the comma
> as part of the LIKE argument).
> In the above, you have Company LIKE '%HEI% ', (with a space between the
> apostrophes and a comma after the closing apoostrophe)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Escaping a comma in a LIKE statement

A simple question I'm sure...
I am having problems with the following statement:
Select * from data where
Company LIKE '%HEI,%'
It appears that the comma is being interpreted as something other than a normal string value. When I remove the comma everything works fine. Is there a way to escape the comma? I've spent a great deal of time looking for the answer, apparently I am not lo
oking in the right places. Any help would be appreciated.
Can you be a bit more specific, or show us a repro? Below statement work just like expected, i.e., the comma
does not get any special treatment:
SELECT * FROM
(
SELECT 'HEL,oo' AS x
UNION
SELECT 'HELOO' AS x
) AS d
WHERE x LIKE 'HEL,%'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MSchlaud" <MSchlaud@.discussions.microsoft.com> wrote in message
news:CE4ECBB0-2DEB-479E-8277-736B3ECA2138@.microsoft.com...
> A simple question I'm sure...
> I am having problems with the following statement:
> Select * from data where
> Company LIKE '%HEI,%'
> It appears that the comma is being interpreted as something other than a normal string value. When I remove
the comma everything works fine. Is there a way to escape the comma? I've spent a great deal of time looking
for the answer, apparently I am not looking in the right places. Any help would be appreciated.
|||Sure. Here's the full query and the resulting error message:
Query:
Select * From data
WHERE Company like '%HEI %',
And RequestDate BETWEEN {d '2004-06-01'} AND {d '2004-06-15'}
Here's the error message:
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near ','.
"Tibor Karaszi" wrote:

> Can you be a bit more specific, or show us a repro? Below statement work just like expected, i.e., the comma
> does not get any special treatment:
> SELECT * FROM
> (
> SELECT 'HEL,oo' AS x
> UNION
> SELECT 'HELOO' AS x
> ) AS d
> WHERE x LIKE 'HEL,%'
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "MSchlaud" <MSchlaud@.discussions.microsoft.com> wrote in message
> news:CE4ECBB0-2DEB-479E-8277-736B3ECA2138@.microsoft.com...
> the comma everything works fine. Is there a way to escape the comma? I've spent a great deal of time looking
> for the answer, apparently I am not looking in the right places. Any help would be appreciated.
>
>
|||I see the problem. Don't know how I missed the comma. Thanks anyway
"MSchlaud" wrote:
[vbcol=seagreen]
> Sure. Here's the full query and the resulting error message:
> Query:
> Select * From data
> WHERE Company like '%HEI %',
> And RequestDate BETWEEN {d '2004-06-01'} AND {d '2004-06-15'}
> Here's the error message:
> ODBC Error Code = 37000 (Syntax error or access violation)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near ','.
>
> "Tibor Karaszi" wrote:
|||On Wed, 16 Jun 2004 13:31:01 -0700, MSchlaud wrote:

>Sure. Here's the full query and the resulting error message:
>Query:
>Select * From data
>WHERE Company like '%HEI %',
>And RequestDate BETWEEN {d '2004-06-01'} AND {d '2004-06-15'}
>Here's the error message:
>ODBC Error Code = 37000 (Syntax error or access violation)
>[Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near ','.
>
Hi MSchlaud,
You have the comma outside of the apostrophe.
In your original question, you said Company LIKE '%HEI,%' (with the comma
as part of the LIKE argument).
In the above, you have Company LIKE '%HEI% ', (with a space between the
apostrophes and a comma after the closing apoostrophe)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||I'm sorry but that is not something I can execute as you didn't post the CREATE TABLE and INSERT statements.
I.e., I cannot try to reproduce that error message.
However, trying to parse only give me that you have a comma *after* the string definition (outside the
string), before the word AND. Try below:
SELECT * FROM data
WHERE Company LIKE '%HEI %'
AND RequestDate BETWEEN {d '2004-06-01'} AND {d '2004-06-15'}
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MSchlaud" <MSchlaud@.discussions.microsoft.com> wrote in message
news:B2396540-2022-4711-A1F2-331F46BD1DC2@.microsoft.com...[vbcol=seagreen]
> Sure. Here's the full query and the resulting error message:
> Query:
> Select * From data
> WHERE Company like '%HEI %',
> And RequestDate BETWEEN {d '2004-06-01'} AND {d '2004-06-15'}
> Here's the error message:
> ODBC Error Code = 37000 (Syntax error or access violation)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near ','.
>
> "Tibor Karaszi" wrote:
comma[vbcol=seagreen]
remove[vbcol=seagreen]
looking[vbcol=seagreen]
|||Yes, I see that now. Thanks for the quick responses.
"Hugo Kornelis" wrote:

> On Wed, 16 Jun 2004 13:31:01 -0700, MSchlaud wrote:
>
> Hi MSchlaud,
> You have the comma outside of the apostrophe.
> In your original question, you said Company LIKE '%HEI,%' (with the comma
> as part of the LIKE argument).
> In the above, you have Company LIKE '%HEI% ', (with a space between the
> apostrophes and a comma after the closing apoostrophe)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Escape the '\' character

I noticed that the following character needs escaping with another \ but
only if it is not followed by another character
What is the rule
Are there any other character with that need escaping except ' (single
quote) and wildcard characters when used with LIKE
Thank you,
SamuelA lesser known fact about the Transact-SQL parser is that a backslash ('')
is a continuation character (like the C programming language). When a
backslash is found at the end of a line in a literal string, the backslash
and line terminator characters are ignored. Specifying the additional
backslash isn't technically an escape, it's just another character in the
literal string. For example
SELECT 'test\
ing'
-- result is 'testing'
SELECT 'test\\
ing'
-- result is 'test\ing'
BTW, I first learned of this issue when helping a user who was obfuscating
data. The backslash and newline characters were getting dropped when the
algorithm introduced a backslash at the end of a line. This is yet one
more reason that one should always use parameteritized SQL statements.
Hope this helps.
Dan Guzman
SQL Server MVP
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:OAfNhcAgGHA.1456@.TK2MSFTNGP04.phx.gbl...
>I noticed that the following character needs escaping with another \ but
>only if it is not followed by another character
> What is the rule
> Are there any other character with that need escaping except ' (single
> quote) and wildcard characters when used with LIKE
> Thank you,
> Samuel
>|||very interesting indeed,
thank you
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e2gVaHLgGHA.2476@.TK2MSFTNGP03.phx.gbl...
>A lesser known fact about the Transact-SQL parser is that a backslash ('')
>is a continuation character (like the C programming language). When a
>backslash is found at the end of a line in a literal string, the backslash
>and line terminator characters are ignored. Specifying the additional
>backslash isn't technically an escape, it's just another character in the
>literal string. For example
> SELECT 'test\
> ing'
> -- result is 'testing'
> SELECT 'test\\
> ing'
> -- result is 'test\ing'
> BTW, I first learned of this issue when helping a user who was obfuscating
> data. The backslash and newline characters were getting dropped when the
> algorithm introduced a backslash at the end of a line. This is yet one
> more reason that one should always use parameteritized SQL statements.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
> news:OAfNhcAgGHA.1456@.TK2MSFTNGP04.phx.gbl...
>|||So you never need to escape a letter except the single quote and except
wildcard character when using LIKE
What I still don't understand why if I type "A\" & VBCR & "B" I get
A
B
And if I type ' ' as the last character in the line in a multi line Textbox
it will NOT ignore it and I will get
A\
B
Why is that?
Thanks,
Samuel Shulman
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e2gVaHLgGHA.2476@.TK2MSFTNGP03.phx.gbl...
>A lesser known fact about the Transact-SQL parser is that a backslash ('')
>is a continuation character (like the C programming language). When a
>backslash is found at the end of a line in a literal string, the backslash
>and line terminator characters are ignored. Specifying the additional
>backslash isn't technically an escape, it's just another character in the
>literal string. For example
> SELECT 'test\
> ing'
> -- result is 'testing'
> SELECT 'test\\
> ing'
> -- result is 'test\ing'
> BTW, I first learned of this issue when helping a user who was obfuscating
> data. The backslash and newline characters were getting dropped when the
> algorithm introduced a backslash at the end of a line. This is yet one
> more reason that one should always use parameteritized SQL statements.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
> news:OAfNhcAgGHA.1456@.TK2MSFTNGP04.phx.gbl...
>|||> And if I type ' ' as the last character in the line in a multi line
> Textbox it will NOT ignore it and I will get
> A\
> B
I would expect that behavior if you using a parameterized SQL Statement.
However, if the SQL statement string is constructed like the example below,
you should get 'AB':
strSql = "INSERT INTO MyTable VALUES('" & _
Request("textBoxValue") & _
"')")
Hope this helps.
Dan Guzman
SQL Server MVP
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:Ou3pyzLgGHA.4304@.TK2MSFTNGP05.phx.gbl...
> So you never need to escape a letter except the single quote and except
> wildcard character when using LIKE
> What I still don't understand why if I type "A\" & VBCR & "B" I get
> A
> B
> And if I type ' ' as the last character in the line in a multi line
> Textbox it will NOT ignore it and I will get
> A\
> B
> Why is that?
> Thanks,
> Samuel Shulman
>
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:e2gVaHLgGHA.2476@.TK2MSFTNGP03.phx.gbl...
>|||Can you please define what parameterized statement
Does is matter how the variable is assigned the value?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23Pg3pPMgGHA.1856@.TK2MSFTNGP03.phx.gbl...
> I would expect that behavior if you using a parameterized SQL Statement.
> However, if the SQL statement string is constructed like the example
> below, you should get 'AB':
> strSql = "INSERT INTO MyTable VALUES('" & _
> Request("textBoxValue") & _
> "')")
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
> news:Ou3pyzLgGHA.4304@.TK2MSFTNGP05.phx.gbl...
>|||> Can you please define what parameterized statement
A parameterized statement contains parameter markers instead of literal
values. Actual parameter values are substituted for parameter markers at
query execution time. Parameterized statements have several advantages,
such as improved security, no need for special quote handling and execution
plan reuse.
Below is an ADO example. ADO.NET has a slightly different object model but
the basic principle is the same and you can use named parameter markers with
the ADO.NET SqlClient provider.
Set command = CreateObject("ADODB.Command")
command.ActiveConnection = connection
command.CommandText = "INSERT INTO MyTable VALUES(?)"
Set textBoxParameter = command.CreateParameter( _
"@.textBoxParameter", adVarchar, adParamInput, 50,
Request("textBoxValue"))
command.Parameters.Append textBoxParameter
Set Rs = command.Execute
Hope this helps.
Dan Guzman
SQL Server MVP
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:OEqmw%23MgGHA.4004@.TK2MSFTNGP04.phx.gbl...
> Can you please define what parameterized statement
> Does is matter how the variable is assigned the value?
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:%23Pg3pPMgGHA.1856@.TK2MSFTNGP03.phx.gbl...
>sql

Escape the '\' character

I noticed that the following character needs escaping with another \ but
only if it is not followed by another character
What is the rule
Are there any other character with that need escaping except ' (single
quote) and wildcard characters when used with LIKE
Thank you,
SamuelA lesser known fact about the Transact-SQL parser is that a backslash ('\')
is a continuation character (like the C programming language). When a
backslash is found at the end of a line in a literal string, the backslash
and line terminator characters are ignored. Specifying the additional
backslash isn't technically an escape, it's just another character in the
literal string. For example
SELECT 'test\
ing'
-- result is 'testing'
SELECT 'test\\
ing'
-- result is 'test\ing'
BTW, I first learned of this issue when helping a user who was obfuscating
data. The backslash and newline characters were getting dropped when the
algorithm introduced a backslash at the end of a line. This is yet one
more reason that one should always use parameteritized SQL statements.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:OAfNhcAgGHA.1456@.TK2MSFTNGP04.phx.gbl...
>I noticed that the following character needs escaping with another \ but
>only if it is not followed by another character
> What is the rule
> Are there any other character with that need escaping except ' (single
> quote) and wildcard characters when used with LIKE
> Thank you,
> Samuel
>|||very interesting indeed,
thank you
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e2gVaHLgGHA.2476@.TK2MSFTNGP03.phx.gbl...
>A lesser known fact about the Transact-SQL parser is that a backslash ('\')
>is a continuation character (like the C programming language). When a
>backslash is found at the end of a line in a literal string, the backslash
>and line terminator characters are ignored. Specifying the additional
>backslash isn't technically an escape, it's just another character in the
>literal string. For example
> SELECT 'test\
> ing'
> -- result is 'testing'
> SELECT 'test\\
> ing'
> -- result is 'test\ing'
> BTW, I first learned of this issue when helping a user who was obfuscating
> data. The backslash and newline characters were getting dropped when the
> algorithm introduced a backslash at the end of a line. This is yet one
> more reason that one should always use parameteritized SQL statements.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
> news:OAfNhcAgGHA.1456@.TK2MSFTNGP04.phx.gbl...
>>I noticed that the following character needs escaping with another \ but
>>only if it is not followed by another character
>> What is the rule
>> Are there any other character with that need escaping except ' (single
>> quote) and wildcard characters when used with LIKE
>> Thank you,
>> Samuel
>|||So you never need to escape a letter except the single quote and except
wildcard character when using LIKE
What I still don't understand why if I type "A\" & VBCR & "B" I get
A
B
And if I type ' \' as the last character in the line in a multi line Textbox
it will NOT ignore it and I will get
A\
B
Why is that?
Thanks,
Samuel Shulman
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e2gVaHLgGHA.2476@.TK2MSFTNGP03.phx.gbl...
>A lesser known fact about the Transact-SQL parser is that a backslash ('\')
>is a continuation character (like the C programming language). When a
>backslash is found at the end of a line in a literal string, the backslash
>and line terminator characters are ignored. Specifying the additional
>backslash isn't technically an escape, it's just another character in the
>literal string. For example
> SELECT 'test\
> ing'
> -- result is 'testing'
> SELECT 'test\\
> ing'
> -- result is 'test\ing'
> BTW, I first learned of this issue when helping a user who was obfuscating
> data. The backslash and newline characters were getting dropped when the
> algorithm introduced a backslash at the end of a line. This is yet one
> more reason that one should always use parameteritized SQL statements.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
> news:OAfNhcAgGHA.1456@.TK2MSFTNGP04.phx.gbl...
>>I noticed that the following character needs escaping with another \ but
>>only if it is not followed by another character
>> What is the rule
>> Are there any other character with that need escaping except ' (single
>> quote) and wildcard characters when used with LIKE
>> Thank you,
>> Samuel
>|||> And if I type ' \' as the last character in the line in a multi line
> Textbox it will NOT ignore it and I will get
> A\
> B
I would expect that behavior if you using a parameterized SQL Statement.
However, if the SQL statement string is constructed like the example below,
you should get 'AB':
strSql = "INSERT INTO MyTable VALUES('" & _
Request("textBoxValue") & _
"')")
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:Ou3pyzLgGHA.4304@.TK2MSFTNGP05.phx.gbl...
> So you never need to escape a letter except the single quote and except
> wildcard character when using LIKE
> What I still don't understand why if I type "A\" & VBCR & "B" I get
> A
> B
> And if I type ' \' as the last character in the line in a multi line
> Textbox it will NOT ignore it and I will get
> A\
> B
> Why is that?
> Thanks,
> Samuel Shulman
>
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:e2gVaHLgGHA.2476@.TK2MSFTNGP03.phx.gbl...
>>A lesser known fact about the Transact-SQL parser is that a backslash
>>('\') is a continuation character (like the C programming language). When
>>a backslash is found at the end of a line in a literal string, the
>>backslash and line terminator characters are ignored. Specifying the
>>additional backslash isn't technically an escape, it's just another
>>character in the literal string. For example
>> SELECT 'test\
>> ing'
>> -- result is 'testing'
>> SELECT 'test\\
>> ing'
>> -- result is 'test\ing'
>> BTW, I first learned of this issue when helping a user who was
>> obfuscating data. The backslash and newline characters were getting
>> dropped when the algorithm introduced a backslash at the end of a line.
>> This is yet one more reason that one should always use parameteritized
>> SQL statements.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
>> news:OAfNhcAgGHA.1456@.TK2MSFTNGP04.phx.gbl...
>>I noticed that the following character needs escaping with another \ but
>>only if it is not followed by another character
>> What is the rule
>> Are there any other character with that need escaping except ' (single
>> quote) and wildcard characters when used with LIKE
>> Thank you,
>> Samuel
>>
>|||Can you please define what parameterized statement
Does is matter how the variable is assigned the value?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23Pg3pPMgGHA.1856@.TK2MSFTNGP03.phx.gbl...
>> And if I type ' \' as the last character in the line in a multi line
>> Textbox it will NOT ignore it and I will get
>> A\
>> B
> I would expect that behavior if you using a parameterized SQL Statement.
> However, if the SQL statement string is constructed like the example
> below, you should get 'AB':
> strSql = "INSERT INTO MyTable VALUES('" & _
> Request("textBoxValue") & _
> "')")
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
> news:Ou3pyzLgGHA.4304@.TK2MSFTNGP05.phx.gbl...
>> So you never need to escape a letter except the single quote and except
>> wildcard character when using LIKE
>> What I still don't understand why if I type "A\" & VBCR & "B" I get
>> A
>> B
>> And if I type ' \' as the last character in the line in a multi line
>> Textbox it will NOT ignore it and I will get
>> A\
>> B
>> Why is that?
>> Thanks,
>> Samuel Shulman
>>
>>
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:e2gVaHLgGHA.2476@.TK2MSFTNGP03.phx.gbl...
>>A lesser known fact about the Transact-SQL parser is that a backslash
>>('\') is a continuation character (like the C programming language).
>>When a backslash is found at the end of a line in a literal string, the
>>backslash and line terminator characters are ignored. Specifying the
>>additional backslash isn't technically an escape, it's just another
>>character in the literal string. For example
>> SELECT 'test\
>> ing'
>> -- result is 'testing'
>> SELECT 'test\\
>> ing'
>> -- result is 'test\ing'
>> BTW, I first learned of this issue when helping a user who was
>> obfuscating data. The backslash and newline characters were getting
>> dropped when the algorithm introduced a backslash at the end of a line.
>> This is yet one more reason that one should always use parameteritized
>> SQL statements.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
>> news:OAfNhcAgGHA.1456@.TK2MSFTNGP04.phx.gbl...
>>I noticed that the following character needs escaping with another \ but
>>only if it is not followed by another character
>> What is the rule
>> Are there any other character with that need escaping except ' (single
>> quote) and wildcard characters when used with LIKE
>> Thank you,
>> Samuel
>>
>>
>|||> Can you please define what parameterized statement
A parameterized statement contains parameter markers instead of literal
values. Actual parameter values are substituted for parameter markers at
query execution time. Parameterized statements have several advantages,
such as improved security, no need for special quote handling and execution
plan reuse.
Below is an ADO example. ADO.NET has a slightly different object model but
the basic principle is the same and you can use named parameter markers with
the ADO.NET SqlClient provider.
Set command = CreateObject("ADODB.Command")
command.ActiveConnection = connection
command.CommandText = "INSERT INTO MyTable VALUES(?)"
Set textBoxParameter = command.CreateParameter( _
"@.textBoxParameter", adVarchar, adParamInput, 50,
Request("textBoxValue"))
command.Parameters.Append textBoxParameter
Set Rs = command.Execute
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:OEqmw%23MgGHA.4004@.TK2MSFTNGP04.phx.gbl...
> Can you please define what parameterized statement
> Does is matter how the variable is assigned the value?
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:%23Pg3pPMgGHA.1856@.TK2MSFTNGP03.phx.gbl...
>> And if I type ' \' as the last character in the line in a multi line
>> Textbox it will NOT ignore it and I will get
>> A\
>> B
>> I would expect that behavior if you using a parameterized SQL Statement.
>> However, if the SQL statement string is constructed like the example
>> below, you should get 'AB':
>> strSql = "INSERT INTO MyTable VALUES('" & _
>> Request("textBoxValue") & _
>> "')")
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
>> news:Ou3pyzLgGHA.4304@.TK2MSFTNGP05.phx.gbl...
>> So you never need to escape a letter except the single quote and except
>> wildcard character when using LIKE
>> What I still don't understand why if I type "A\" & VBCR & "B" I get
>> A
>> B
>> And if I type ' \' as the last character in the line in a multi line
>> Textbox it will NOT ignore it and I will get
>> A\
>> B
>> Why is that?
>> Thanks,
>> Samuel Shulman
>>
>>
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:e2gVaHLgGHA.2476@.TK2MSFTNGP03.phx.gbl...
>>A lesser known fact about the Transact-SQL parser is that a backslash
>>('\') is a continuation character (like the C programming language).
>>When a backslash is found at the end of a line in a literal string, the
>>backslash and line terminator characters are ignored. Specifying the
>>additional backslash isn't technically an escape, it's just another
>>character in the literal string. For example
>> SELECT 'test\
>> ing'
>> -- result is 'testing'
>> SELECT 'test\\
>> ing'
>> -- result is 'test\ing'
>> BTW, I first learned of this issue when helping a user who was
>> obfuscating data. The backslash and newline characters were getting
>> dropped when the algorithm introduced a backslash at the end of a line.
>> This is yet one more reason that one should always use parameteritized
>> SQL statements.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
>> news:OAfNhcAgGHA.1456@.TK2MSFTNGP04.phx.gbl...
>>I noticed that the following character needs escaping with another \
>>but only if it is not followed by another character
>> What is the rule
>> Are there any other character with that need escaping except ' (single
>> quote) and wildcard characters when used with LIKE
>> Thank you,
>> Samuel
>>
>>
>>
>

Errror 8144

I get following error
Server: Msg 8144, Level 16, State 2, Procedure pPMEmployeeUpdate, Line 0
[Microsoft][ODBC SQL Server Driver][SQL Server]Procedure or function
pPMEmployeeUpdate has too many arguments specified.
Any Ideas Why can it appear?
Following are NOT the reason:
1. C# code is correct
The error appears even if I debug the procedure in QA with the same error.
Thanks
ShimonI suggest that you post the TSQL signature of the procedure and use a Profil
er trace to catch the
call of the procedure and post that as well. It sounds like you have called
the procedure with more
parameters than was defined in the CREATE PROC statement.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Shimon Sim" <estshim@.att.net> wrote in message news:%23a5b7MnGFHA.1740@.TK2MSFTNGP09.phx.gb
l...
>I get following error
> Server: Msg 8144, Level 16, State 2, Procedure pPMEmployeeUpdate, Line 0
> [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure or function pPMEmployeeUpdat
e has too
> many arguments specified.
> Any Ideas Why can it appear?
> Following are NOT the reason:
> 1. C# code is correct
> The error appears even if I debug the procedure in QA with the same error.
> Thanks
> Shimon
>|||You have given more parameters than required
Madhivanan|||Thanks,
create PROCEDURE pPropertyManagerUpdate
(
@.PropertyManagerID int,
@.FirstName varchar( 25 ),
@.LastName varchar( 25 ),
@.Phone varchar( 15 ),
@.Fax varchar( 15 ),
@.WirelessPhone varchar( 15 ),
@.Email varchar( 25 ),
@.UserName varchar( 25 ),
@.Password binary( 24 )
)
AS
UPDATE PropertyManager
SET FirstName = @.FirstName, LastName = @.LastName, Phone = @.Phone,
Fax = @.Fax, WirelessPhone = @.WirelessPhone, Email =
@.Email,
UserName = @.UserName, [Password] = @.Password
WHERE PropertyManagerID = @.PropertyManagerID
RETURN
GO
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uxjsVVnGFHA.4032@.TK2MSFTNGP12.phx.gbl...
>I suggest that you post the TSQL signature of the procedure and use a
>Profiler trace to catch the call of the procedure and post that as well. It
>sounds like you have called the procedure with more parameters than was
>defined in the CREATE PROC statement.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Shimon Sim" <estshim@.att.net> wrote in message
> news:%23a5b7MnGFHA.1740@.TK2MSFTNGP09.phx.gbl...
>|||It is imporsible to do if you debug SP in QA.
Thanks
Shimon
<madhivanan2001@.gmail.com> wrote in message
news:1109252093.459864.275520@.l41g2000cwc.googlegroups.com...
> You have given more parameters than required
> Madhivanan
>|||Shimon,
The Error is for the sp pPMEmployeeUpdate and you
posted the code for pPropertyManagerUpdate !!!
BTW, do you have a trigger on the PropertyManager table?
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Shimon Sim" <estshim@.att.net> wrote in message
news:%23h7dQbnGFHA.3912@.TK2MSFTNGP10.phx.gbl...
> Thanks,
> create PROCEDURE pPropertyManagerUpdate
> (
> @.PropertyManagerID int,
> @.FirstName varchar( 25 ),
> @.LastName varchar( 25 ),
> @.Phone varchar( 15 ),
> @.Fax varchar( 15 ),
> @.WirelessPhone varchar( 15 ),
> @.Email varchar( 25 ),
> @.UserName varchar( 25 ),
> @.Password binary( 24 )
> )
> AS
> UPDATE PropertyManager
> SET FirstName = @.FirstName, LastName = @.LastName, Phone = @.Phone,
> Fax = @.Fax, WirelessPhone = @.WirelessPhone, Email =
> @.Email,
> UserName = @.UserName, [Password] = @.Password
> WHERE PropertyManagerID = @.PropertyManagerID
> RETURN
> GO
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:uxjsVVnGFHA.4032@.TK2MSFTNGP12.phx.gbl...
>|||My best guess is that you are calling pPMEmployeeUpdate
From the Insert trigger for the table PropertyManager.
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Shimon Sim" <estshim@.att.net> wrote in message
news:eravnbnGFHA.2616@.tk2msftngp13.phx.gbl...
> It is imporsible to do if you debug SP in QA.
> Thanks
> Shimon
> <madhivanan2001@.gmail.com> wrote in message
> news:1109252093.459864.275520@.l41g2000cwc.googlegroups.com...
>|||Wow
How come I didn't notice that.
Thanks, let me look again.
Shimon.
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:ees1thnGFHA.3088@.tk2msftngp13.phx.gbl...
> Shimon,
> The Error is for the sp pPMEmployeeUpdate and you
> posted the code for pPropertyManagerUpdate !!!
> BTW, do you have a trigger on the PropertyManager table?
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "Shimon Sim" <estshim@.att.net> wrote in message
> news:%23h7dQbnGFHA.3912@.TK2MSFTNGP10.phx.gbl...
>

Error--Urgent

I am geeting the following error in dts
"Duplicate key was ignored"
How to suppress this message in dts?
Version : Sql server 2000 service pack 3a
Thanks
Bala
Don't think you can suppress the message. There are a few
ways to deal with it - one option is to import the data into
a staging table and then import the data into the real table
using SQL for whatever logic you need to import from the
staging table into the final table.
-Sue
On Wed, 16 Jun 2004 15:11:24 -0700, "Bala"
<Balak@.herbalife.com> wrote:

>I am geeting the following error in dts
>"Duplicate key was ignored"
>How to suppress this message in dts?
>Version : Sql server 2000 service pack 3a
>
>Thanks
>Bala
>

Error--Urgent

I am geeting the following error in dts
"Duplicate key was ignored"
How to suppress this message in dts?
Version : Sql server 2000 service pack 3a
Thanks
BalaDon't think you can suppress the message. There are a few
ways to deal with it - one option is to import the data into
a staging table and then import the data into the real table
using SQL for whatever logic you need to import from the
staging table into the final table.
-Sue
On Wed, 16 Jun 2004 15:11:24 -0700, "Bala"
<Balak@.herbalife.com> wrote:
>I am geeting the following error in dts
>"Duplicate key was ignored"
>How to suppress this message in dts?
>Version : Sql server 2000 service pack 3a
>
>Thanks
>Bala
>sql

Error--Urgent

I am geeting the following error in dts
"Duplicate key was ignored"
How to suppress this message in dts?
Version : Sql server 2000 service pack 3a
Thanks
BalaDon't think you can suppress the message. There are a few
ways to deal with it - one option is to import the data into
a staging table and then import the data into the real table
using SQL for whatever logic you need to import from the
staging table into the final table.
-Sue
On Wed, 16 Jun 2004 15:11:24 -0700, "Bala"
<Balak@.herbalife.com> wrote:

>I am geeting the following error in dts
>"Duplicate key was ignored"
>How to suppress this message in dts?
>Version : Sql server 2000 service pack 3a
>
>Thanks
>Bala
>

Error-Unable to get report parameters:Unable to access Web Service

Hi - In our RS + SP1 installation on Windows Server 2003, we recently began
to get the following error:
"Unable to get report parameters: Unable to access Web Service: The request
failed with HTTP status 403: Forbidden."
We are calling Render() in code using an account which has admin rights to
the RS box, is not locked out, etc. Event logs show no failures and the RS
logs are as useless as ever. I see no access denieds from Windows auditing
either.
Thoughts?
thanks muchHi,
Thanks for your posting!
From your descriptions, I understood that you are not able to access Web
Service with the error message "Unable to get report parameters: Unable to
access Web Service: The request failed with HTTP status 403: Forbidden."
Have I understood you? Correct me if I was wrong.
Based on my knowledge, it seems that "anonymous" for Reportserver virual
folder is enabled. Please try the following steps and let me know whether
it works
1. Right click ReportServer in IIS->Properties->Directory Security
2. Make sure "Intergrated Windows authentication" is checked and "enable
anonymouse access" is unchecked.
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael, I looked at my anonymous setting but it was set properly.
By chance, I tried to execute http://server/reportserver/reportservice.asmx
and it said that there were no permissions to execute scripts in this virtual
root. Sure enough, I looked at the ReportServer virtual root config and the
Execute permissions were improperly set. Not sure how changed, but once I
put it back to "Scripts and Executables" I was good.
Thanks much
"Michael Cheng [MSFT]" wrote:
> Hi,
> Thanks for your posting!
> From your descriptions, I understood that you are not able to access Web
> Service with the error message "Unable to get report parameters: Unable to
> access Web Service: The request failed with HTTP status 403: Forbidden."
> Have I understood you? Correct me if I was wrong.
> Based on my knowledge, it seems that "anonymous" for Reportserver virual
> folder is enabled. Please try the following steps and let me know whether
> it works
> 1. Right click ReportServer in IIS->Properties->Directory Security
> 2. Make sure "Intergrated Windows authentication" is checked and "enable
> anonymouse access" is unchecked.
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>|||Hi,
Thanks for your prompt updates!
It's great to hear that it did turn out to be an authority issue and you
yourself have resolved it.
Thank you for your patience and corporation. If you have any questions or
concerns on SQL Server, don't hesitate to let me know. We are always here
to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.sql

Errors: Event Ids 17055 and 19011.

Hi everyone,
Occasionally we get timeout errors in our custom applications, which are
inserting/updating data.
They seem to coincide with the following errors:
Event ID: 19011
SuperSocket info: ConnectionListen(Shared-Memory (LPC)) : Error 5.
Event ID: 17055
17113 :
initconfig: Error 32(error not found) opening 'D:\Microsoft SQL
Server\MSSQL\data\master.mdf' for configuration information.
We cannot determine the cause of the errors.
Any help will be appreciated.
Best Regards.Hi Sezgin,
The symptom is similar to what is described in the
following KB article:
815249 FIX: Performance of a query that is run from a
client program on a SQL
http://support.microsoft.com/?id=815249
You may want to try the workaround first to see if it
works for you.
Generally, random issues can be caused by various
factors, and it is difficult to locate the root cause in
a newsgroup thread. If the issue still exists after you
have tried the workaround in the KB815249, to
efficiently troubleshoot this issue, we recommend that
you contact Microsoft Product Support Services and open
a support incident and work with a dedicated Support
Professional.
Please be advised that contacting phone support will be
a charged call. However, if you are simply requesting a
hotfix be sent to you and no other support then charges
are usually refunded or waived.
To obtain the phone numbers for specific technology
request please take a look at the web site listed below.
http://support.microsoft.com/defaul...scid=fh;EN-US;P
HONENUMBERS
If you are outside the US please see
http://support.microsoft.com for regional support phone
numbers.
If anything is unclear, feel free to let me know.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
This posting is provided "AS IS" with no warranties, and
confers no rights.
--
>From: "Sezgin Rafed" <anonymous@.anonymous.com>
>Subject: Errors: Event Ids 17055 and 19011.
>Date: Wed, 2 Feb 2005 17:51:39 +0200
>Lines: 22
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.3790.181
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.181
>Message-ID: <#kTmW8TCFHA.3908@.TK2MSFTNGP12.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host:
host-213-194-84-162.borusantelekom.com 213.194.84.162
>Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.p
hx.gbl!TK2MSFTNGP12.phx.gbl
>Xref: cpmsftngxa10.phx.gbl
microsoft.public.sqlserver.server:376662
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Hi everyone,
>Occasionally we get timeout errors in our custom
applications, which are
>inserting/updating data.
>They seem to coincide with the following errors:
>Event ID: 19011
>SuperSocket info: ConnectionListen(Shared-Memory (LPC))
: Error 5.
>
>Event ID: 17055
>17113 :
>initconfig: Error 32(error not found) opening
'D:\Microsoft SQL
>Server\MSSQL\data\master.mdf' for configuration
information.
>We cannot determine the cause of the errors.
>Any help will be appreciated.
>Best Regards.
>
>|||We've experienced the same problem and it started when we used
sp_addlinkedserver and began querying that server. Our query was creating
threads that it could not close and created problems.
The MS article you mentioned talked about a hot fix, however, they also said
that the hot fix is in testing.
Does anyone know when the next hot fix will be out?
Thanks!
Ed Klopfenstein
Visual String
Boise, Idaho
"William Wang[MSFT]" wrote:

> Hi Sezgin,
> The symptom is similar to what is described in the
> following KB article:
> 815249 FIX: Performance of a query that is run from a
> client program on a SQL
> http://support.microsoft.com/?id=815249
> You may want to try the workaround first to see if it
> works for you.
> Generally, random issues can be caused by various
> factors, and it is difficult to locate the root cause in
> a newsgroup thread. If the issue still exists after you
> have tried the workaround in the KB815249, to
> efficiently troubleshoot this issue, we recommend that
> you contact Microsoft Product Support Services and open
> a support incident and work with a dedicated Support
> Professional.
> Please be advised that contacting phone support will be
> a charged call. However, if you are simply requesting a
> hotfix be sent to you and no other support then charges
> are usually refunded or waived.
> To obtain the phone numbers for specific technology
> request please take a look at the web site listed below.
> http://support.microsoft.com/defaul...scid=fh;EN-US;P
> HONENUMBERS
> If you are outside the US please see
> http://support.microsoft.com for regional support phone
> numbers.
> If anything is unclear, feel free to let me know.
> Sincerely,
> William Wang
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from your issue.
> This posting is provided "AS IS" with no warranties, and
> confers no rights.
> --
> host-213-194-84-162.borusantelekom.com 213.194.84.162
> cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.p
> hx.gbl!TK2MSFTNGP12.phx.gbl
> microsoft.public.sqlserver.server:376662
> applications, which are
> : Error 5.
> 'D:\Microsoft SQL
> information.
>

Errors: Event Ids 17055 and 19011.

Hi everyone,
Occasionally we get timeout errors in our custom applications, which are
inserting/updating data.
They seem to coincide with the following errors:
Event ID: 19011
SuperSocket info: ConnectionListen(Shared-Memory (LPC)) : Error 5.
Event ID: 17055
17113 :
initconfig: Error 32(error not found) opening 'D:\Microsoft SQL
Server\MSSQL\data\master.mdf' for configuration information.
We cannot determine the cause of the errors.
Any help will be appreciated.
Best Regards.
Hi Sezgin,
The symptom is similar to what is described in the
following KB article:
815249 FIX: Performance of a query that is run from a
client program on a SQL
http://support.microsoft.com/?id=815249
You may want to try the workaround first to see if it
works for you.
Generally, random issues can be caused by various
factors, and it is difficult to locate the root cause in
a newsgroup thread. If the issue still exists after you
have tried the workaround in the KB815249, to
efficiently troubleshoot this issue, we recommend that
you contact Microsoft Product Support Services and open
a support incident and work with a dedicated Support
Professional.
Please be advised that contacting phone support will be
a charged call. However, if you are simply requesting a
hotfix be sent to you and no other support then charges
are usually refunded or waived.
To obtain the phone numbers for specific technology
request please take a look at the web site listed below.
http://support.microsoft.com/default...cid=fh;EN-US;P
HONENUMBERS
If you are outside the US please see
http://support.microsoft.com for regional support phone
numbers.
If anything is unclear, feel free to let me know.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
This posting is provided "AS IS" with no warranties, and
confers no rights.
--
>From: "Sezgin Rafed" <anonymous@.anonymous.com>
>Subject: Errors: Event Ids 17055 and 19011.
>Date: Wed, 2 Feb 2005 17:51:39 +0200
>Lines: 22
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.3790.181
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.181
>Message-ID: <#kTmW8TCFHA.3908@.TK2MSFTNGP12.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host:
host-213-194-84-162.borusantelekom.com 213.194.84.162
>Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl!TK2MSFT NGP08.p
hx.gbl!TK2MSFTNGP12.phx.gbl
>Xref: cpmsftngxa10.phx.gbl
microsoft.public.sqlserver.server:376662
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Hi everyone,
>Occasionally we get timeout errors in our custom
applications, which are
>inserting/updating data.
>They seem to coincide with the following errors:
>Event ID: 19011
>SuperSocket info: ConnectionListen(Shared-Memory (LPC))
: Error 5.
>
>Event ID: 17055
>17113 :
>initconfig: Error 32(error not found) opening
'D:\Microsoft SQL
>Server\MSSQL\data\master.mdf' for configuration
information.
>We cannot determine the cause of the errors.
>Any help will be appreciated.
>Best Regards.
>
>
|||We've experienced the same problem and it started when we used
sp_addlinkedserver and began querying that server. Our query was creating
threads that it could not close and created problems.
The MS article you mentioned talked about a hot fix, however, they also said
that the hot fix is in testing.
Does anyone know when the next hot fix will be out?
Thanks!
Ed Klopfenstein
Visual String
Boise, Idaho
"William Wang[MSFT]" wrote:

> Hi Sezgin,
> The symptom is similar to what is described in the
> following KB article:
> 815249 FIX: Performance of a query that is run from a
> client program on a SQL
> http://support.microsoft.com/?id=815249
> You may want to try the workaround first to see if it
> works for you.
> Generally, random issues can be caused by various
> factors, and it is difficult to locate the root cause in
> a newsgroup thread. If the issue still exists after you
> have tried the workaround in the KB815249, to
> efficiently troubleshoot this issue, we recommend that
> you contact Microsoft Product Support Services and open
> a support incident and work with a dedicated Support
> Professional.
> Please be advised that contacting phone support will be
> a charged call. However, if you are simply requesting a
> hotfix be sent to you and no other support then charges
> are usually refunded or waived.
> To obtain the phone numbers for specific technology
> request please take a look at the web site listed below.
> http://support.microsoft.com/default...cid=fh;EN-US;P
> HONENUMBERS
> If you are outside the US please see
> http://support.microsoft.com for regional support phone
> numbers.
> If anything is unclear, feel free to let me know.
> Sincerely,
> William Wang
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from your issue.
> This posting is provided "AS IS" with no warranties, and
> confers no rights.
> --
> host-213-194-84-162.borusantelekom.com 213.194.84.162
> cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl!TK2MSFT NGP08.p
> hx.gbl!TK2MSFTNGP12.phx.gbl
> microsoft.public.sqlserver.server:376662
> applications, which are
> : Error 5.
> 'D:\Microsoft SQL
> information.
>

Errors with SP4 upgrade... Please Help

I am having problems accessing DTS after install SP4 and was wondering
if someone could offer some advice.

I installed SP4 and got the following error after it competed.

Unable to write to response file 'U:\WINDOWS\setup.iss' during
recording. Please ensure enough space is available on target drive.

I got the error 3 times (3 pop-ups).

After the install I could not access DTS. I was getting 2 error
pop-ups.
1. 'rebbrui.rll is missing'
2. Snap-In failed to initialize: Meta Data Services

I read the groups and tried several things.

I tried to reinstall MDAC and it did not help.

I tried to register several dlls and it did not help.

I found this post and it seemed to help.
Repair Script:
http://www.experts-exchange.com/Dat...Q_21445589.html

I was then able to access DTS using the profile (which is an
administrator) which
I used to install SP4 and run the above repair script. However, no
other profile can open or run DTS.

I tried running the repair script under the other profiles and it did
not help.

I was receiving 2 errors for the other profiles. I only documented one
and I am no longer getting the other. Now it just goes to a white
screen and hangs.

1st error:
DTS Designer Error
The specified module could not be found.

2nd error:
???

I tried reinstalling client tools and SP4 and it did not help. Now I
get one additional error.

Before I can expand the server node in enterprise manager I get the
following error.
Snap-in failed to initialize
Name: Meta Data Services
CLSID: {1DBA4DD4-EB97-4FD2-AB80-9D0D4BA74034}

Does anyone have a suggestion on how I might fix this?

I am running SQL Server 2000 Enterprise on Windows 2000 Server with 4
GB RAM.

I did notices that someone has set the /3GB switch. I know this is not
supported on this OS but it was not causing major problems before the
upgrade. Could it be the culprit?Finally got this fixed. It was a real pain!

The registry on this server looked all hacked up to me. It looks like
many of the keys referenced U:/ instead of C:/

Some of the necessary dlls were pointing to U: also.

I had to manually uninstall SQL Server. Then reinstall. Then uninstall
via add remove programs. Then I did another uninstall/reinstall
(because i got a few errors).

I reinstalled, upgraded to sp4, stopped the service, swapped my master,
msdb, tempdb with the originals and everything is working great.

Errors with Service Pack 1

Hi Ng,
i have some problems with Rs Sp 1. I installed Sp 1 on the server and on the
pc where vs.net is installed. Now I got the following Problem:
I design a report and go to the preview mode. If an error is found, for
example a syntax problem in an expression, the report isn't displayed. No
problem till here. But now I change the expression, so that it's correct,
the report can't be displayed anyway. And from now on, the report won't show
in preview mode ever, even when the report is complete empty!!!
I deletet the report, copied the rdl code in a new one, and it could be
displayed. Renaming this report to the other name, can't be displayed...
Any Ideas?
Thanks in Advance
G. SchmelzerCould you please send me the exact steps to reproduce? I tried the following
but couldn't reproduce the issue:
1. Go to Layout view in Report Designer
2. Drop a textbox
3. Type the expression =Globals!ReportName in it
4. Preview (works fine)
5. Go back to layout.
6. Change the expression to =zzz_Globals!ReportName
7. Preview (returns an error)
8. Go back to layout.
9. Change the expression back to =Globals!ReportName
10. Preview (works fine)
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"G. Schmelzer" <asdf@.de.de> wrote in message
news:OwPYh2nXEHA.2816@.TK2MSFTNGP11.phx.gbl...
> Hi Ng,
> i have some problems with Rs Sp 1. I installed Sp 1 on the server and on
the
> pc where vs.net is installed. Now I got the following Problem:
> I design a report and go to the preview mode. If an error is found, for
> example a syntax problem in an expression, the report isn't displayed. No
> problem till here. But now I change the expression, so that it's correct,
> the report can't be displayed anyway. And from now on, the report won't
show
> in preview mode ever, even when the report is complete empty!!!
> I deletet the report, copied the rdl code in a new one, and it could be
> displayed. Renaming this report to the other name, can't be displayed...
>
> Any Ideas?
> Thanks in Advance
> G. Schmelzer
>

Errors while running SQLH2 Tool

I am recieving following errors while running SQLH2
can you advice how can i resolve them
Errors:
1.InvalidOperationException Cannot read log entry number 4243737.
2.NullReferenceException Object reference not set to an instance of an
object.
3.Event Log There is no gap in history, but the
previous last event
is out of bounds
What version of SQLH2 are you running? I hope it's updated version
(2.0.024).
1. Quote from SQLH2 V2 Deployment Guide
Cannot read log entry number ###.
This error usually indicates that the event log on the target machine is
corrupted. It's easy to verify using Event Viewer - try to open the
suspected event log and check if you can read events from it (sometimes
Event Viewer is able to show the list of events, but an actual attempt to
read some events fails).
I would need more inofmation regarding the other two.
Please send a mail to SQLH2@.microsoft.com
(with your H2log.txt attached).
Thanks
Grigory
This posting is provided "AS IS" with no warranties, and confers no rights.
Please reply to the newsgroups only, thanks.
"Aneel Ismaily" <Aneel Ismaily@.discussions.microsoft.com> wrote in message
news:A219CD0B-C253-43E2-90E2-487CE248BAF3@.microsoft.com...
> I am recieving following errors while running SQLH2
> can you advice how can i resolve them
> Errors:
> 1.InvalidOperationException Cannot read log entry number 4243737.
> 2.NullReferenceException Object reference not set to an instance of
an
> object.
> 3.Event Log There is no gap in history, but the
> previous last event
> is out of bounds
>

Errors while running DBCC CheckDb

I get the following errors when DBCC CheckDb is executed
on 'fmcomp003' database :
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:1881) with latch type SH.
sysindexes failed.
Server: Msg 8944, Level 16, State 1, Line 1
Table Corrupt: Object ID 2, index ID 0, page (1:1881), row
14. Test (ColumnOffsets <= (nextRec - pRec)) failed.
Values are 82 and 0.
DBCC results for 'fmcomp003'.Hi
DBCC CHECKDB has few arguments which you can use in this case
For more details please refer to BOL.
"Amit Chaudhary" <anonymous@.discussions.microsoft.com> wrote in message
news:2b81b01c467e1$63299ab0$a301280a@.phx
.gbl...
> I get the following errors when DBCC CheckDb is executed
> on 'fmcomp003' database :
> Server: Msg 8966, Level 16, State 1, Line 1
> Could not read and latch page (1:1881) with latch type SH.
> sysindexes failed.
> Server: Msg 8944, Level 16, State 1, Line 1
> Table Corrupt: Object ID 2, index ID 0, page (1:1881), row
> 14. Test (ColumnOffsets <= (nextRec - pRec)) failed.
> Values are 82 and 0.
> DBCC results for 'fmcomp003'.
>|||Hi,
I didn't understand the error as well as the severity of
the error. And please tell what is BOL.
Amit Chaudhary
>--Original Message--
>Hi
>DBCC CHECKDB has few arguments which you can use in this
case
>For more details please refer to BOL.
>
>"Amit Chaudhary" <anonymous@.discussions.microsoft.com>
wrote in message
> news:2b81b01c467e1$63299ab0$a301280a@.phx
.gbl...
SH.[vbcol=seagreen]
row[vbcol=seagreen]
>
>.
>|||Amit
Seems your table is corrupted. Do you have a last backup of the database?
Books OnLine comes with SQL Server installation.
"Amit Chaudhary" <anonymous@.discussions.microsoft.com> wrote in message
news:2afca01c467e7$5fbe4d70$a401280a@.phx
.gbl...[vbcol=seagreen]
> Hi,
> I didn't understand the error as well as the severity of
> the error. And please tell what is BOL.
> Amit Chaudhary
> case
> wrote in message
> SH.
> row|||BOL is SQL Server documentation (Books On Line)..
Open BOL and search from the message# ( which is the SQL Error number)
8966...
You can also often get good information by simply googling the error number
as well...
To find the table name (from object id 2)
in query analyzer, use the database and
select object_name(2)
Object IDs < 100 are system tables..
IndexID of 0 means it is a table with no clustered index...
You will probably have to restore... You can also open a call to Microsoft
product Support ( PSS) ... I think the call now costs about $249.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Amit Chaudhary" <anonymous@.discussions.microsoft.com> wrote in message
news:2b81b01c467e1$63299ab0$a301280a@.phx
.gbl...
> I get the following errors when DBCC CheckDb is executed
> on 'fmcomp003' database :
> Server: Msg 8966, Level 16, State 1, Line 1
> Could not read and latch page (1:1881) with latch type SH.
> sysindexes failed.
> Server: Msg 8944, Level 16, State 1, Line 1
> Table Corrupt: Object ID 2, index ID 0, page (1:1881), row
> 14. Test (ColumnOffsets <= (nextRec - pRec)) failed.
> Values are 82 and 0.
> DBCC results for 'fmcomp003'.
>|||> And please tell what is BOL.
Books OnLine.
http://www.aspfaq.com/2229
http://www.aspfaq.com/
(Reverse address to reply.)|||What is the version of SQL used here?
If its ver.7 then ensure to apply latest Service pack 4 to the SQL.
Also run DBCC CHECK with REPAIR clause in order to resolve the issue, if not
check event viewer for any information on hardware issues on the SQL server
.
--
Satya SKJ
"Amit Chaudhary" wrote:

> I get the following errors when DBCC CheckDb is executed
> on 'fmcomp003' database :
> Server: Msg 8966, Level 16, State 1, Line 1
> Could not read and latch page (1:1881) with latch type SH.
> sysindexes failed.
> Server: Msg 8944, Level 16, State 1, Line 1
> Table Corrupt: Object ID 2, index ID 0, page (1:1881), row
> 14. Test (ColumnOffsets <= (nextRec - pRec)) failed.
> Values are 82 and 0.
> DBCC results for 'fmcomp003'.
>|||Your sysindexes table has a corruption at the leaf level of the clustered
index. This is not repairable and your only option is to restore from your
last known good backup.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Amit Chaudhary" <anonymous@.discussions.microsoft.com> wrote in message
news:2b81b01c467e1$63299ab0$a301280a@.phx
.gbl...
> I get the following errors when DBCC CheckDb is executed
> on 'fmcomp003' database :
> Server: Msg 8966, Level 16, State 1, Line 1
> Could not read and latch page (1:1881) with latch type SH.
> sysindexes failed.
> Server: Msg 8944, Level 16, State 1, Line 1
> Table Corrupt: Object ID 2, index ID 0, page (1:1881), row
> 14. Test (ColumnOffsets <= (nextRec - pRec)) failed.
> Values are 82 and 0.
> DBCC results for 'fmcomp003'.
>|||Repair cannot fix this error.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Satya SKJ" <satyaskj@.yahoo.co.uk> wrote in message
news:53F0C4CC-F7AE-4F72-8AE3-256FEB2C169B@.microsoft.com...
> What is the version of SQL used here?
> If its ver.7 then ensure to apply latest Service pack 4 to the SQL.
> Also run DBCC CHECK with REPAIR clause in order to resolve the issue, if
not check event viewer for any information on hardware issues on the SQL
server.[vbcol=seagreen]
> --
> --
> Satya SKJ
>
> "Amit Chaudhary" wrote:
>

Errors while running DBCC CheckDb

I get the following errors when DBCC CheckDb is executed
on 'fmcomp003' database :
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:1881) with latch type SH.
sysindexes failed.
Server: Msg 8944, Level 16, State 1, Line 1
Table Corrupt: Object ID 2, index ID 0, page (1:1881), row
14. Test (ColumnOffsets <= (nextRec - pRec)) failed.
Values are 82 and 0.
DBCC results for 'fmcomp003'.
Hi
DBCC CHECKDB has few arguments which you can use in this case
For more details please refer to BOL.
"Amit Chaudhary" <anonymous@.discussions.microsoft.com> wrote in message
news:2b81b01c467e1$63299ab0$a301280a@.phx.gbl...
> I get the following errors when DBCC CheckDb is executed
> on 'fmcomp003' database :
> Server: Msg 8966, Level 16, State 1, Line 1
> Could not read and latch page (1:1881) with latch type SH.
> sysindexes failed.
> Server: Msg 8944, Level 16, State 1, Line 1
> Table Corrupt: Object ID 2, index ID 0, page (1:1881), row
> 14. Test (ColumnOffsets <= (nextRec - pRec)) failed.
> Values are 82 and 0.
> DBCC results for 'fmcomp003'.
>
|||Hi,
I didn't understand the error as well as the severity of
the error. And please tell what is BOL.
Amit Chaudhary
>--Original Message--
>Hi
>DBCC CHECKDB has few arguments which you can use in this
case
>For more details please refer to BOL.
>
>"Amit Chaudhary" <anonymous@.discussions.microsoft.com>
wrote in message[vbcol=seagreen]
>news:2b81b01c467e1$63299ab0$a301280a@.phx.gbl...
SH.[vbcol=seagreen]
row
>
>.
>
|||Amit
Seems your table is corrupted. Do you have a last backup of the database?
Books OnLine comes with SQL Server installation.
"Amit Chaudhary" <anonymous@.discussions.microsoft.com> wrote in message
news:2afca01c467e7$5fbe4d70$a401280a@.phx.gbl...[vbcol=seagreen]
> Hi,
> I didn't understand the error as well as the severity of
> the error. And please tell what is BOL.
> Amit Chaudhary
> case
> wrote in message
> SH.
> row
|||BOL is SQL Server documentation (Books On Line)..
Open BOL and search from the message# ( which is the SQL Error number)
8966...
You can also often get good information by simply googling the error number
as well...
To find the table name (from object id 2)
in query analyzer, use the database and
select object_name(2)
Object IDs < 100 are system tables..
IndexID of 0 means it is a table with no clustered index...
You will probably have to restore... You can also open a call to Microsoft
product Support ( PSS) ... I think the call now costs about $249.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Amit Chaudhary" <anonymous@.discussions.microsoft.com> wrote in message
news:2b81b01c467e1$63299ab0$a301280a@.phx.gbl...
> I get the following errors when DBCC CheckDb is executed
> on 'fmcomp003' database :
> Server: Msg 8966, Level 16, State 1, Line 1
> Could not read and latch page (1:1881) with latch type SH.
> sysindexes failed.
> Server: Msg 8944, Level 16, State 1, Line 1
> Table Corrupt: Object ID 2, index ID 0, page (1:1881), row
> 14. Test (ColumnOffsets <= (nextRec - pRec)) failed.
> Values are 82 and 0.
> DBCC results for 'fmcomp003'.
>
|||> And please tell what is BOL.
Books OnLine.
http://www.aspfaq.com/2229
http://www.aspfaq.com/
(Reverse address to reply.)
|||What is the version of SQL used here?
If its ver.7 then ensure to apply latest Service pack 4 to the SQL.
Also run DBCC CHECK with REPAIR clause in order to resolve the issue, if not check event viewer for any information on Hardware issues on the SQL server.
--
Satya SKJ
"Amit Chaudhary" wrote:

> I get the following errors when DBCC CheckDb is executed
> on 'fmcomp003' database :
> Server: Msg 8966, Level 16, State 1, Line 1
> Could not read and latch page (1:1881) with latch type SH.
> sysindexes failed.
> Server: Msg 8944, Level 16, State 1, Line 1
> Table Corrupt: Object ID 2, index ID 0, page (1:1881), row
> 14. Test (ColumnOffsets <= (nextRec - pRec)) failed.
> Values are 82 and 0.
> DBCC results for 'fmcomp003'.
>
|||Your sysindexes table has a corruption at the leaf level of the clustered
index. This is not repairable and your only option is to restore from your
last known good backup.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Amit Chaudhary" <anonymous@.discussions.microsoft.com> wrote in message
news:2b81b01c467e1$63299ab0$a301280a@.phx.gbl...
> I get the following errors when DBCC CheckDb is executed
> on 'fmcomp003' database :
> Server: Msg 8966, Level 16, State 1, Line 1
> Could not read and latch page (1:1881) with latch type SH.
> sysindexes failed.
> Server: Msg 8944, Level 16, State 1, Line 1
> Table Corrupt: Object ID 2, index ID 0, page (1:1881), row
> 14. Test (ColumnOffsets <= (nextRec - pRec)) failed.
> Values are 82 and 0.
> DBCC results for 'fmcomp003'.
>
|||Repair cannot fix this error.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Satya SKJ" <satyaskj@.yahoo.co.uk> wrote in message
news:53F0C4CC-F7AE-4F72-8AE3-256FEB2C169B@.microsoft.com...
> What is the version of SQL used here?
> If its ver.7 then ensure to apply latest Service pack 4 to the SQL.
> Also run DBCC CHECK with REPAIR clause in order to resolve the issue, if
not check event viewer for any information on Hardware issues on the SQL
server.[vbcol=seagreen]
> --
> --
> Satya SKJ
>
> "Amit Chaudhary" wrote:
sql

Errors while running DBCC CheckDb

I get the following errors when DBCC CheckDb is executed
on 'fmcomp003' database :
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:1881) with latch type SH.
sysindexes failed.
Server: Msg 8944, Level 16, State 1, Line 1
Table Corrupt: Object ID 2, index ID 0, page (1:1881), row
14. Test (ColumnOffsets <= (nextRec - pRec)) failed.
Values are 82 and 0.
DBCC results for 'fmcomp003'.Hi
DBCC CHECKDB has few arguments which you can use in this case
For more details please refer to BOL.
"Amit Chaudhary" <anonymous@.discussions.microsoft.com> wrote in message
news:2b81b01c467e1$63299ab0$a301280a@.phx.gbl...
> I get the following errors when DBCC CheckDb is executed
> on 'fmcomp003' database :
> Server: Msg 8966, Level 16, State 1, Line 1
> Could not read and latch page (1:1881) with latch type SH.
> sysindexes failed.
> Server: Msg 8944, Level 16, State 1, Line 1
> Table Corrupt: Object ID 2, index ID 0, page (1:1881), row
> 14. Test (ColumnOffsets <= (nextRec - pRec)) failed.
> Values are 82 and 0.
> DBCC results for 'fmcomp003'.
>|||Hi,
I didn't understand the error as well as the severity of
the error. And please tell what is BOL.
Amit Chaudhary
>--Original Message--
>Hi
>DBCC CHECKDB has few arguments which you can use in this
case
>For more details please refer to BOL.
>
>"Amit Chaudhary" <anonymous@.discussions.microsoft.com>
wrote in message
>news:2b81b01c467e1$63299ab0$a301280a@.phx.gbl...
>> I get the following errors when DBCC CheckDb is executed
>> on 'fmcomp003' database :
>> Server: Msg 8966, Level 16, State 1, Line 1
>> Could not read and latch page (1:1881) with latch type
SH.
>> sysindexes failed.
>> Server: Msg 8944, Level 16, State 1, Line 1
>> Table Corrupt: Object ID 2, index ID 0, page (1:1881),
row
>> 14. Test (ColumnOffsets <= (nextRec - pRec)) failed.
>> Values are 82 and 0.
>> DBCC results for 'fmcomp003'.
>
>.
>|||Amit
Seems your table is corrupted. Do you have a last backup of the database?
Books OnLine comes with SQL Server installation.
"Amit Chaudhary" <anonymous@.discussions.microsoft.com> wrote in message
news:2afca01c467e7$5fbe4d70$a401280a@.phx.gbl...
> Hi,
> I didn't understand the error as well as the severity of
> the error. And please tell what is BOL.
> Amit Chaudhary
> >--Original Message--
> >Hi
> >DBCC CHECKDB has few arguments which you can use in this
> case
> >For more details please refer to BOL.
> >
> >
> >"Amit Chaudhary" <anonymous@.discussions.microsoft.com>
> wrote in message
> >news:2b81b01c467e1$63299ab0$a301280a@.phx.gbl...
> >> I get the following errors when DBCC CheckDb is executed
> >> on 'fmcomp003' database :
> >> Server: Msg 8966, Level 16, State 1, Line 1
> >> Could not read and latch page (1:1881) with latch type
> SH.
> >> sysindexes failed.
> >> Server: Msg 8944, Level 16, State 1, Line 1
> >> Table Corrupt: Object ID 2, index ID 0, page (1:1881),
> row
> >> 14. Test (ColumnOffsets <= (nextRec - pRec)) failed.
> >> Values are 82 and 0.
> >> DBCC results for 'fmcomp003'.
> >>
> >
> >
> >.
> >|||BOL is SQL Server documentation (Books On Line)..
Open BOL and search from the message# ( which is the SQL Error number)
8966...
You can also often get good information by simply googling the error number
as well...
To find the table name (from object id 2)
in query analyzer, use the database and
select object_name(2)
Object IDs < 100 are system tables..
IndexID of 0 means it is a table with no clustered index...
You will probably have to restore... You can also open a call to Microsoft
product Support ( PSS) ... I think the call now costs about $249.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Amit Chaudhary" <anonymous@.discussions.microsoft.com> wrote in message
news:2b81b01c467e1$63299ab0$a301280a@.phx.gbl...
> I get the following errors when DBCC CheckDb is executed
> on 'fmcomp003' database :
> Server: Msg 8966, Level 16, State 1, Line 1
> Could not read and latch page (1:1881) with latch type SH.
> sysindexes failed.
> Server: Msg 8944, Level 16, State 1, Line 1
> Table Corrupt: Object ID 2, index ID 0, page (1:1881), row
> 14. Test (ColumnOffsets <= (nextRec - pRec)) failed.
> Values are 82 and 0.
> DBCC results for 'fmcomp003'.
>|||> And please tell what is BOL.
Books OnLine.
http://www.aspfaq.com/2229
--
http://www.aspfaq.com/
(Reverse address to reply.)|||Your sysindexes table has a corruption at the leaf level of the clustered
index. This is not repairable and your only option is to restore from your
last known good backup.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Amit Chaudhary" <anonymous@.discussions.microsoft.com> wrote in message
news:2b81b01c467e1$63299ab0$a301280a@.phx.gbl...
> I get the following errors when DBCC CheckDb is executed
> on 'fmcomp003' database :
> Server: Msg 8966, Level 16, State 1, Line 1
> Could not read and latch page (1:1881) with latch type SH.
> sysindexes failed.
> Server: Msg 8944, Level 16, State 1, Line 1
> Table Corrupt: Object ID 2, index ID 0, page (1:1881), row
> 14. Test (ColumnOffsets <= (nextRec - pRec)) failed.
> Values are 82 and 0.
> DBCC results for 'fmcomp003'.
>|||Repair cannot fix this error.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Satya SKJ" <satyaskj@.yahoo.co.uk> wrote in message
news:53F0C4CC-F7AE-4F72-8AE3-256FEB2C169B@.microsoft.com...
> What is the version of SQL used here?
> If its ver.7 then ensure to apply latest Service pack 4 to the SQL.
> Also run DBCC CHECK with REPAIR clause in order to resolve the issue, if
not check event viewer for any information on Hardware issues on the SQL
server.
> --
> --
> Satya SKJ
>
> "Amit Chaudhary" wrote:
> > I get the following errors when DBCC CheckDb is executed
> > on 'fmcomp003' database :
> > Server: Msg 8966, Level 16, State 1, Line 1
> > Could not read and latch page (1:1881) with latch type SH.
> > sysindexes failed.
> > Server: Msg 8944, Level 16, State 1, Line 1
> > Table Corrupt: Object ID 2, index ID 0, page (1:1881), row
> > 14. Test (ColumnOffsets <= (nextRec - pRec)) failed.
> > Values are 82 and 0.
> > DBCC results for 'fmcomp003'.
> >
> >