Error: 644

I get this warning when I do a dbcc checkdb:

Server: Msg 2750, Level 16, State 4, Line 2
Warning: Page (1:300465), Slot 9 in object 2009058193 Index 0 Column Val value -1.#QNAN is out of range for data type "real". Update column to a legal value.

A service using a stored procedure result in the following errir in the event log:

Error 644, Severity: 21, State 5
Could not find the index entry for RID '16ca8880200' in index page (1:31928), index ID 7, database 'BPS'.

I have tried 'dbcc checkdb' and 'dbcc checktable' both with option REPAIR_REBUILD and I have tried 'dbcc reindex' but none of them helped.

Are there other options or can I find the column that shall be updated to a legal value?Did you get any error messages when your ran the DBCC's?

Sounds like a corruption problem to me...

Can you export the data? You may need to do it in ranges...you'll find there will be probably 1 corrupt row...

SELECT * FROM myTable WHERE identity < 1000
and so on...do a binary search..start at the middle your data...

In either Case, you'll either need to do a restore (which hopefully isn't corrupt), or export the data and build a new db...don't know what else could be hosed..

But that's only my own opinion (MOO)|||Have you tried to drop the index and recreate it ?
Have you used the REPAIR_ALLOW_DATA_LOSS option ?

error with subquery.....

I am getting an error from a query that that has a subquery.

Msg 512, Level 16, State 1, Line 2

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

(0 row(s) affected)

This is a the query.

select *

from dhcp

where nameofcomputer = (SELECT p.nameofcomputer

FROM v_Pams_DHCP p

Left Outer Join adat2005_main a

ON p.nameofcomputer = a.nameofcomputer

where a.serialnumber is null)

Thanks in advance.

Gene

Use in operator,

Code Snippet

select

*

from

dhcp

where

nameofcomputer

in (

SELECT

p.nameofcomputer

FROM

v_Pams_DHCP p

Left Outer Join adat2005_main a

ON p.nameofcomputer = a.nameofcomputer

where

a.serialnumber is null

)

Exists Might be faster than IN,

Code Snippet

select

*

from

dhcp

where

Exists

(

SELECT

p.nameofcomputer

FROM

v_Pams_DHCP p

Left Outer Join adat2005_main a

ON p.nameofcomputer = a.nameofcomputer

where

a.serialnumber is null and p.nameofcomputer = dhcp.nameofcomputer

)

error with linked server

Hi
when i try to run a query using linked servers, i get the following
error.

Server: Msg 125, Level 15, State 1, Line 1
Case expressions may only be nested to level 10.

I do have more than 10 case statements, it works fine when it is less
than 10. can anyone tell me if there is a way to have more than 10
case statements. thanks alot.

Jay

my query
Select category, val, Sum(QTY) As QTY , yr
From
(
Select val, QTY2 As QTY,
KEEP = Case
When code = '004' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '005' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '003' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '017' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '007' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '008' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '009' And ((YR > 2003) Or (YR = 2003 And MON > 11))
Then 'N'
When CODE = '010' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '038' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '032' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '030' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '018' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
Else 'Y' End
From
amf a Join linkedserver.source.dbo.table2 b On a.COM = b.COM
Where CATEGORY In ('1') And CODE In ('001','003','004','005')
And b.YR Between 2003 And 2004 And b.MON <= 1
) x
Where KEEP = 'Y'
Group By CATEGORY, YR"Jay" <webforum2000@.yahoo.com> wrote in message
news:9594a55e.0404230831.50bcabe0@.posting.google.c om...
> Hi
> when i try to run a query using linked servers, i get the following
> error.
> Server: Msg 125, Level 15, State 1, Line 1
> Case expressions may only be nested to level 10.
> I do have more than 10 case statements, it works fine when it is less
> than 10. can anyone tell me if there is a way to have more than 10
> case statements. thanks alot.
> Jay
> my query
> Select category, val, Sum(QTY) As QTY , yr
> From
> (
> Select val, QTY2 As QTY,
> KEEP = Case
> When code = '004' And ((YR > 2003) Or (YR = 2003 And MON > 12))
> Then 'N'
> When CODE = '005' And ((YR > 2003) Or (YR = 2003 And MON > 12))
> Then 'N'
> When CODE = '003' And ((YR > 2003) Or (YR = 2003 And MON > 12))
> Then 'N'
> When CODE = '017' And ((YR > 2003) Or (YR = 2003 And MON > 12))
> Then 'N'
> When CODE = '007' And ((YR > 2003) Or (YR = 2003 And MON > 12))
> Then 'N'
> When CODE = '008' And ((YR > 2003) Or (YR = 2003 And MON > 12))
> Then 'N'
> When CODE = '009' And ((YR > 2003) Or (YR = 2003 And MON > 11))
> Then 'N'
> When CODE = '010' And ((YR > 2003) Or (YR = 2003 And MON > 12))
> Then 'N'
> When CODE = '038' And ((YR > 2003) Or (YR = 2003 And MON > 12))
> Then 'N'
> When CODE = '032' And ((YR > 2003) Or (YR = 2003 And MON > 12))
> Then 'N'
> When CODE = '030' And ((YR > 2003) Or (YR = 2003 And MON > 12))
> Then 'N'
> When CODE = '018' And ((YR > 2003) Or (YR = 2003 And MON > 12))
> Then 'N'
> Else 'Y' End
> From
> amf a Join linkedserver.source.dbo.table2 b On a.COM = b.COM
> Where CATEGORY In ('1') And CODE In ('001','003','004','005')
> And b.YR Between 2003 And 2004 And b.MON <= 1
> ) x
> Where KEEP = 'Y'
> Group By CATEGORY, YR

Since most of your conditions are the same, have you tried something like
this?

Select category, val, Sum(QTY) As QTY , yr
From
(
Select val, QTY2 As QTY,
KEEP = Case
When code in ('004', '005, '003', '017', /* etc. */)
And ((YR > 2003) Or (YR = 2003 And MON > 12))
Else 'Y' End
From
amf a Join linkedserver.source.dbo.table2 b On a.COM = b.COM
Where CATEGORY In ('1') And CODE In ('001','003','004','005')
And b.YR Between 2003 And 2004 And b.MON <= 1
) x

Simon|||
hi
thanks for the suggestion. I have a problem, this is one of the query
where it is all the same, in a few others it varies a lot. i want to
know if the limitaion exists in sql using linked servers ( since it
works fine if i dont use linked servers and have them in the same
server). i want to get aroud this, so that i dont have to change all my
existing queries, and would hamper my using linked server. thanks.. any
suggestion?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Error while running xp_cmdshell

I am getting the following error when running a command in Query
Analyzer.

Msg 50001, Level 1, State 50001
xpsql.cpp: Error 5 from CreateProcess on line 675

Here is the command that I am running that generates this error
message:

xp_cmdshell "@.ECHO test message > c:\temp\newtemp.txt"

The command inside the xp_cmdshell command runs successfully from a
Windows command prompt. Simpler commands also fail with the same error
message. For example:

xp_cmdshell 'dir'

Can anyone suggest a solution to this problem? I assume this problem is
due to a permissions/security issue. I have given Everyone Full Control
over c:\temp.

Windows 2000
SQL Server 2000

Thanks for any help.Probably a permissions issue.

Best way to troubleshoot this is as follows:

1. Log in as "sa" or as a sysadmin user (you are already doing this
step right?)
2. Try running the xp_cmdshell (does it work?)
3. If no, check the startup account for the MSSQLSERVER service - does
this user have the ability to run this command? Not sure? Log off the
server and log back in as this service startup user.. Try running the
DOS command...

The key is to make sure the account running the SQL Server service has
the p|||Chris Allison (chris.f.allison@.gmail.com) writes:
> I am getting the following error when running a command in Query
> Analyzer.
> Msg 50001, Level 1, State 50001
> xpsql.cpp: Error 5 from CreateProcess on line 675
> Here is the command that I am running that generates this error
> message:
> xp_cmdshell "@.ECHO test message > c:\temp\newtemp.txt"
> The command inside the xp_cmdshell command runs successfully from a
> Windows command prompt. Simpler commands also fail with the same error
> message. For example:
> xp_cmdshell 'dir'

And what does "xp_cmdshell 'set'" yield? That is, a command that
does not perform any disc access at all?

"NET HELPMSG 5" gives "Access is denied", but I'm too bad at Windows
to say what access it is. Since this happens in CreateProcess one
could suspect that access is denied when try to create a subprocess.

Under which user does SQL Server run? And are yourself logged into
SQL Server as sysadmin, or as an unpriviledged user?

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi, the xp_cmdshell stored procedure needs grants in cmd.exe

go to %systemroot%\system32\cmd.exe right click, and add grants.

It is not recommended by security reasons.
regards

Sha0
www.badchecksum.com|||One simple answer:

Antivirus software!

It could be any of the solutions suggested on the internet when searching for "Error 5 from CreateProcess" on Google, but I tried them all and none of them helped.

I uninstalled the antivirus software (Panda) and it worked like a charm!

Try uninstalling (or configuring) your antivirus software.

Kind regards, good luck and may the source be with you

/Boozon

Error while Quering Qracle table from SQL 2000

Hi All

i am receiving following error while querying a particular table in Oracle form SQl 2000.
Other tables can be queried.

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandPrepare::Prepare returned 0x80004005: The provider did not give any information about the error.].

Someone coould please guide me as to how to resolve this error?

Regrads,
YogeetaYOUR QUESTION IS NOT CLEAR .

Please try to post your question clearly and pass few more informations regarding your problem.|||

Quote:

Originally Posted by debasisdas

YOUR QUESTION IS NOT CLEAR .

Please try to post your question clearly and pass few more informations regarding your problem.

A user is accessing one particular Oracle table from SQL2005.For a particular table while running a simple sekect query he gets the following error message.
The other tables can be queried from the connected server in MS SQL.

Error Received:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandPrepare::Prepare returned 0x80004005: The provider did not give any information about the error.].

I hope now its clear and can enlighten me on this.

Regards

Error while creating assembly

I am trying to create an assembly on a sql server 2005 machine but it gives me following error:

Msg 33009, Level 16, State 2, Line 2
The database owner SID recorded in the master database differs from the database owner SID recorded in database 'XYZ'. You should correct this situation by resetting the owner of database 'XYZ' using the ALTER AUTHORIZATION statement.

I tried using the alter authorization statement to change the owner.
It did not work.

I am able to create same assembly on another test database but can not create it on this database.

Is this because of orphan logins?

Thanks for the help.

Harshal.Hi

Was this db attached from another instance or upgraded from 2k?

I have had this problem - changing the owner is simple. What do you mean by it didn't work? It should be pretty easy e.g.

USE Mydb
GO

EXEC dbo.sp_changedbowner @.loginame = N'sa', @.map = false
GO|||More info:
http://support.microsoft.com/kb/918040|||Thanks for the reply.
Yes this database was restored from a backup taken from another machine. I fixed the orphaned user and changed the db owner to other user. but still the error persists. though i did not change sa owner.
will try fixin the SA login.


Hi

Was this db attached from another instance or upgraded from 2k?

I have had this problem - changing the owner is simple. What do you mean by it didn't work? It should be pretty easy e.g.

USE Mydb
GO

EXEC dbo.sp_changedbowner @.loginame = N'sa', @.map = false
GO|||Thanks a lot pootle flump.
It worked.