Incorrect use of the xml data type method ''modify''. A non-mutator method is expected in th

Hi,

I'm getting an error when I'm executing the follwing query in SQL Server 2005

declare @.xml xml

set @.xml = '<ROOT>

<Customer CustomerID="VINET" ContactName="Paul Henriot">

<Order OrderID="10248" CustomerID="VINET" EmployeeID="5"

OrderDate="1996-07-04T00:00:00">

<OrderDetail ProductID="11" Quantity="12"/>

<OrderDetail ProductID="42" Quantity="10"/>

</Order>

</Customer>

<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">

<Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"

OrderDate="1996-08-16T00:00:00">

<OrderDetail ProductID="72" Quantity="3"/>

</Order>

</Customer>

</ROOT>'

if @.xml.exist('//Order[OrderID="10283"]')=0

begin

set @.xml = @.xml.modify('insert <Order OrderID="10284" CustomerID="LILAS" EmployeeID="3"

OrderDate="1996-08-16T00:00:00"></Order> after //Order[@.OrderID="10283"]')

select @.xml

end

The error is

Incorrect use of the xml data type method 'modify'. A non-mutator method is expected in this context.

Please give me the answare

Regards,

Koustav

Here is a corrected, working version that simply uses SET @.x.modify:

Code Snippet

declare @.xml xml

set @.xml = '<ROOT>

<Customer CustomerID="VINET" ContactName="Paul Henriot">

<Order OrderID="10248" CustomerID="VINET" EmployeeID="5"

OrderDate="1996-07-04T00:00:00">

<OrderDetail ProductID="11" Quantity="12"/>

<OrderDetail ProductID="42" Quantity="10"/>

</Order>

</Customer>

<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">

<Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"

OrderDate="1996-08-16T00:00:00">

<OrderDetail ProductID="72" Quantity="3"/>

</Order>

</Customer>

</ROOT>'

if @.xml.exist('//Order[OrderID="10283"]')=0

begin

set @.xml.modify('

insert <Order OrderID="10284" CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00"></Order> after (//Order[@.OrderID="10283"])[1]')

select @.xml

end

|||

Thanx Martin its now working fine.

I've another query How will I get an attribute's value of a particuler node?

say for example

I've @.xml as xml document and I've find out the following node using xquery

<Order OrderID="10284" CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00"></Order>

Now I want to check if OrderDate >= "1996-08-16" then insert some OrderDetail.

How will I read this OrderDate attribute's value.

Please help me.

Regards,

Koustav

|||

I hope the following example helps:

Code Snippet

DECLARE @.x xml;

SET @.x = '

<Orders>

<Order OrderID="10284" CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00"></Order>

<Order OrderID="10285" CustomerID="Someone" EmployeeID="3" OrderDate="1994-08-16T00:00:00"></Order>

</Orders>';

IF @.x.exist('

/Orders/Order[@.OrderDate >= "1996-08-16T00:00:00"]

') = 1

BEGIN

SET @.x.modify('

insert <OrderDetail Product="Something"/>

into (/Orders/Order[@.OrderDate >= "1996-08-16T00:00:00"])[1]

');

SELECT @.x

END

|||Thanx a lot Martin. It perfectly working.

|||

Continuing the above example I'm facing anaother problem.

Instead of insert xml node I'm trying to replace an attribute's value. I've written

if @.xml.exist('/Customer/Order[@.OrderID="10283"]')=0

begin

--set @.xml.modify('insert <Order OrderID="10284" CustomerID="LILAS" EmployeeID="3"

-- OrderDate="1996-08-16T00:00:00"></Order> after (//Order[@.OrderID="10283"])[1]')

set @.xml.modify('declare namespace ns="http://myOrder";

replace value of (//nsrder[@.OrderID="10283"]/OrderDetail/@.Quantity)[1] with 4')

select @.xml

end

The XML is untyped xml. There are no such schema named "myOrder" but if I'm not giving the line "declare namespace ns='http://myOrder';" its throwing error.

Now the problem is. this query is executing but the value dose not replace.

Please give me the solution.

|||

The following sample code works flawlessly for me, the Quantity attribute value is changed from 3 to 4:

Code Snippet

DECLARE @.xml xml;

SET @.xml = '<Customer>

<Order OrderID="10283">

<OrderDetail Quantity="3"/>

</Order>

</Customer>';

IF @.xml.exist('/Customer/Order[@.OrderID="10283"]') = 1

BEGIN

SET @.xml.modify('

replace value of (//Order[@.OrderID="10283"]/OrderDetail/@.Quantity)[1] with 4

');

END;

SELECT @.xml;

|||

Thank you Martin, I don't know why it was not running in my environment. But fortunatly now it aslo running in my machine. Thanks for your help.

Martine, I'm also facing another problem.

...

...

declare @.CID varchar(10)

DECLARE C1 CURSOR FOR SELECT ID FROM select customerID from customer

OPEN C1;

FETCH NEXT FROM C1 into @.CID

WHILE @.@.FETCH_STATUS = 0

BEGIN

set @.queryString = 'for $cust in //Customer where $cust/@.CustmerID = "' + @.CID + '" return $cust'

--set @.detail = @.xml.query('for $cust in //Customer where $cust/@.CustmerID = "' + @.CID + '" return $cust')

set @.detail = @.xml.query(@.queryString)

select @.detail

select @.queryString

FETCH NEXT FROM C1 into @.CID

END

....

...

Its showing an error

The argument 1 of the xml data type method "query" must be a string literal.

That means query() can't execute a string veriable. But I've to meet this type of requirment. From above XML I want to read each node then doing some processing into subnodes and then put the value into a table. So first I've to process 1st customer node and its child nodes then shift to 2nd customer and so on.

Please help me how will I proceed.

|||

You can use sql:variable as follows:

Code Snippet

DECLARE @.CID nvarchar(10);

SET @.CID = '1234';

DECLARE @.xml xml;

SET @.xml = '<root>

<Customer CustomerId="1233"/>

<Customer CustomerId="1234"/>

</root>'

SELECT @.xml.query('

for $c in root/Customer

where $c/@.CustomerId = sql:variable("@.CID")

return $c

');

|||

Hi,

I have problem in replacing set of elements with text.

declare @.dtToday datetime

, @.input xml

set @.dtToday = getdate()

-- here is my input XML

set @.input = '<TsfDesc>

<physical_to_loc>1000004</physical_to_loc>

<to_loc_type>S</to_loc_type>

<pick_not_before_date>

<year>2001</year>

<month>03</month>

<day>09</day>

<hour>00</hour>

<minute>00</minute>

<second>00</second>

</pick_not_before_date>

<to_loc_type>S</to_loc_type>

</TsfDesc>'

I would like to get the output as shown below

'<TsfDesc>

<physical_to_loc>1000004</physical_to_loc>

<to_loc_type>S</to_loc_type>

<pick_not_before_date>03/09/2001 00:00:00

</pick_not_before_date>

<to_loc_type>S</to_loc_type>

</TsfDesc>'

would you please help me?

Thanks in advance.

Meeran.

|||

Here is a solution using several steps:

Code Snippet

DECLARE @.input xml;

set @.input = '<TsfDesc>

<physical_to_loc>1000004</physical_to_loc>

<to_loc_type>S</to_loc_type>

<pick_not_before_date>

<year>2001</year>

<month>03</month>

<day>09</day>

<hour>00</hour>

<minute>00</minute>

<second>00</second>

</pick_not_before_date>

<to_loc_type>S</to_loc_type>

</TsfDesc>';

DECLARE @.date nvarchar(19);

SET @.date = @.input.value('

concat((TsfDesc/pick_not_before_date/day)[1], "/", (TsfDesc/pick_not_before_date/month)[1], "/", (TsfDesc/pick_not_before_date/year)[1], " ", (TsfDesc/pick_not_before_date/hour)[1], ":", (TsfDesc/pick_not_before_date/minute)[1], ":", (TsfDesc/pick_not_before_date/second)[1])

', 'nvarchar(19)');

SET @.input.modify('

delete TsfDesc/pick_not_before_date/node()

')

SET @.input.modify('

insert text{sql:variable("@.date")}

into (TsfDesc/pick_not_before_date)[1]

');

SELECT @.input;

|||

Thanks a lot Martin, for your prompt response.

incorrect syntax question

i have a table of data that the user can enter into, the data type is set to "text" and has worked in some test so far, but when i type data in '' marks such as :

'text here'

it gives me an incorrect syntax error, is there a way around this? or is the '' charectors invalid? thanks John

A single quote is a string separator. So if your data has single quotes you might have to excape it with double quotes: example:Select'test''s'

|||Are you using parameters? If not, you should. It will help avoid this kind of problem. Others might suggest doubling the apostrophes, but while it works, that is not the answer.|||

Mikesdotnetting:

Are you using parameters? If not, you should. It will help avoid this kind of problem. Others might suggest doubling the apostrophes, but while it works, that is not the answer.

Agreed.

|||

parameters will stop me getting this error? awesome! thanks for your help John

|||

I have an insert query which gives me a similar error, I cant see why its not working, the error is

A potentially dangerous Request.Form value was detected from the client (ctl00$ContentPlaceHolder1$CommentBox="<b>test text</b>").

My code is :

Connection.Open();
SqlCommand InsertItem = new SqlCommand("INSERT INTO TestTable(Inserted) VALUES ('@.item')", Connection);
InsertItem.Parameters.Add("@.item", SqlDbType.VarChar).Value = Textbox1.Text;
InsertItem.ExecuteNonQuery();
Connection.Close();
I simply tryed to insert the text string <b>test text</b>

Thanks John

|||

You dont need to put quotes if you are using parameterized queries.

SqlCommand InsertItem = new SqlCommand("INSERT INTO TestTable(Inserted) VALUES (@.item)", Connection);

|||It's objecting to the fact that you are trying to input html tags. Set ValidateRequest to false in the @.Page directive:http://www.asp.net/learn/whitepapers/request-validation/|||

thats awesome thanks, that answered every question i could come up with! haha John

'Incorrect Syntax near' Error in Create Type in 2005

This is getting really frustrating. I've searched and tried about everything I can think of. I have an assembly added to my database by using Sql Management Console. The assembly is called TestUDT. I have defined 4 structs within that assembly, Person, Transaction, Payment, and Seller. The assembly was added to SqlServer 2005 with dbo as the owner, the permission set is SAFE, and the assembly is signed. The structs are within the Test namespace.

When ever I type the following in a Query window, just checking the syntax fails, let alone trying to execute it.

CREATE TYPE dbo.Seller EXTERNAL NAME TestUDT.[Test.Seller]

The error is:

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'TestUDT'.
What am I doing wrong?

Thanks
]Monty[

I'm sorry that I can't help directly, but the same syntax works fine for me here. You sure you don't have any "strange" characters in the syntax, or....?

Niels|||OK, I figured this out.

The database existed in the original SQLServer 2000 database. When I upgraded to 2005, the compatibilty level of the database remained at 2000. After changing the compatibility level to 2005, the command now works.

Important safety tip: remember to have the compatibility set to 2005 on the database if you're going to create a CLR type.

Of course, SqlServer lets you add the assembly without complaining and no where in the docs on CREATE TYPE is this mentioned.

]Monty[|||>>Of course, SqlServer lets you add the assembly without complaining and no where in the docs on CREATE TYPE is this mentioned.

Hi Monty,
Thank you for pointing out this error in the documentation. I've created a documentation bug which will be fixed in a future refresh of Books Online.

Just as an FYI, you can create documentation bugs by clicking the Send Feedback button at the top of a topic. Your comments are used to automatically create a bug that is assigned to the appropriate writer. For SQL Server 2005, we will be releasing quarterly updates to Books Online, so you can expect to see your feedback incorporated in a fairly reasonable amount of time.

Regards,|||Thanks for adding the doc bug. I tried to do the feedback thing, but of course here at work we have Lotus Notes (ugh!) and all kinds of security policy, and I was never able to get a dialog or email to come up to give the feed back. For those of us in that situation, it would be nice to have an email address or an URL presented instead of just a button to click.

Thanks
]Monty[|||>For those of us in that situation, it would be nice to have an email address or an URL presented instead of just a button to click.
Thanks for the suggestion. I've forwarded your idea to the customer feedback team.

Regards,sql

Incorrect date in column for 25,000 rows

I have a table with a column in it called Date, which is of the type DateTime, and for the last two years I have been adding data which I found out was incorrect.

My dates are all a day in the future, so I need to reduce each date by one day.

I can easily use a select script to reveal the 25,000 rows which are all incorrect dates. But I can't figure out how to update each and every row to subtract one day from each date.

So where I have:

26/01/2005

I would like to have:

25/01/2005

and of course for every record. Obviously way too many to do manually :-(

Can anyone show me a script that will get what I'm after.

Tia

Tailwag

After one your 25000 rows with invalid data, that is not nice to find out. But it is easy to solve, just run this query:

UPDATE [MyTable] SET [MyDate] = DATEADD(year, -1, [MyDate])

Don't forget to replace MyTable with your table name and MyDate with your datetime field name.
I have tested it before posting this solution, because i don't want to be responsible for lozing 25000 rows on friday.

|||

Also, replace year with day!

So the update query must be:

UPDATE [MyTable] SET [MyDate] = DATEADD(day, -1, [MyDate])

- Jeroen Boiten

|||Indeed! Thanks Jeroen, i overlooked that one. Thought only the years where incorrect.|||

Thank you PJ, and also Jeroen. I ran the query and 'Viola' it worked flawlessly, you guys are now extremely high on my best friends of all time list

Tia.

Tailwag

P.S. The B_ _ _ _ Friday curse has finally been thwarted!!!

Incorporating Form Authentication with Report Services

Hi all,

I am new to reporting services in SQL SERVER 2005. I configured my reporting server which is currently runnung when i type //localhost/reportserver in the URL it give me results. Is there anyway of incorporating security in ASP.NET 2.0 using Form Authentication So long as the user clicks on the link having been authenticated he can access the reports on the reporting server. For example I am working on an academic project whereby when user logins, I create a link that him to report server as in

//localhost/reportserver and when user clicks on that link, he can be able to download whatever report he might need.

I am running all these one local machine.

Could someone help me out

Try one of these links. I am beginning to think Russell is the SSRS god.

http://blogs.msdn.com/bimusings/archive/2005/11/29/497848.aspx

http://blogs.msdn.com/bimusings/archive/2005/12/05/500195.aspx

http://blogs.msdn.com/bimusings/archive/2005/09/08/462544.aspx

Ron

Inconsistent result set.

Hi All
What does it mean if i have a result set that comes as < long text>? I have
a table with a column of data type txt and size 16. When i try to get data
from that colomun, either i get the data or i get the result set as <long
text>. What is causing this inconsistency in my result set? Thanx in advance
.Enterprise Manager will not display large pieces of text stored in a text or
ntext column.
If you need to see more of the text values use Query Analyzer. But mind the
fact, that by default QA only displays up to 256 characters, so you'd need t
o
change that to suit your needs. Go to Tools | Options | Results and set
"Maximum characters per column" to a value of your choice (between 30 and
8192).
Or, better yet, manipulate long text in an appropriate client application.
Above all, get out of Enterprise Manager. ;)
ML|||Thanx for the reply,but still why is it at times i get the right result set.
I should be getting the '<long text>' all the time i run my query from what
you are saying. Also i have been doing this in QA. Thanx once again.
"ML" wrote:

> Enterprise Manager will not display large pieces of text stored in a text
or
> ntext column.
> If you need to see more of the text values use Query Analyzer. But mind th
e
> fact, that by default QA only displays up to 256 characters, so you'd need
to
> change that to suit your needs. Go to Tools | Options | Results and set
> "Maximum characters per column" to a value of your choice (between 30 and
> 8192).
> Or, better yet, manipulate long text in an appropriate client application.
> Above all, get out of Enterprise Manager. ;)
>
> ML|||I honestly do not know the exact limit used to display either actual
text/ntext or a generic <long text> message in Enterprise Manager, since I
really don't use it often. :)
But I have noticed that the limit may not be a fixed value. I think it
depends on the size of the active result set, but I'm not sure. Maybe someon
e
else knows. Anyway, since large text and/or images for that matter only mean
anything when displayed and manipulated in an appropriate client application
,
the ratio behind the "EM fun" is irrelevant. At least IMHO.
ML

Inconsistencies Errors 8904 8913 8928 8906

Is there any possibility to correct such type of allocation problems without
data loss?
After correction with ALLOW_DATA_LOSS option, is there any tool to check for
referential integrity to discover witch records on witch tables disppeared
with deallocations?
Thanks you allI'm sorry, I searched in the newsgroup and I found in many cases there is
nothing to do even with the ALLOW_DATA_LOSS option (I still didn't try it).
For the moment I'm waiting for a CHECKTABLE REPAIR_REBUILD to end (it takes
hours...)
"Argo" wrote:
> Is there any possibility to correct such type of allocation problems without
> data loss?
> After correction with ALLOW_DATA_LOSS option, is there any tool to check for
> referential integrity to discover witch records on witch tables disppeared
> with deallocations?
> Thanks you all
>|||If DBCC reports that REPAIR_ALLOW_DATA_LOSS is the repair level required to
fix these errors, then a REPAIR_REBUILD will not completely remove the
problem. These are allocation errors, and will likely not be affected by a
REPAIR_REBUILD. Your best bet is always to restore from a backup. Do you
have one available?
--
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Argo" <falk62@.yahoo.com> wrote in message
news:9BE9595C-FD19-4557-969D-F46B56985D36@.microsoft.com...
> I'm sorry, I searched in the newsgroup and I found in many cases there is
> nothing to do even with the ALLOW_DATA_LOSS option (I still didn't try
> it).
> For the moment I'm waiting for a CHECKTABLE REPAIR_REBUILD to end (it
> takes
> hours...)
> "Argo" wrote:
>> Is there any possibility to correct such type of allocation problems
>> without
>> data loss?
>> After correction with ALLOW_DATA_LOSS option, is there any tool to check
>> for
>> referential integrity to discover witch records on witch tables
>> disppeared
>> with deallocations?
>> Thanks you all|||Regarding your question about checking constraints, the only tool in SQL
Server is DBCC CHECKCONSTRAINTS which will validate FOREIGN KEY and CHECK
constraints. There's nothing that will validate referential integrity
constraints.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ryan Stonecipher [MSFT]" <ryanston@.microsoft.com> wrote in message
news:OCSCHmuCFHA.2608@.TK2MSFTNGP10.phx.gbl...
> If DBCC reports that REPAIR_ALLOW_DATA_LOSS is the repair level required
to
> fix these errors, then a REPAIR_REBUILD will not completely remove the
> problem. These are allocation errors, and will likely not be affected by
a
> REPAIR_REBUILD. Your best bet is always to restore from a backup. Do you
> have one available?
> --
> Ryan Stonecipher
> Microsoft Sql Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Argo" <falk62@.yahoo.com> wrote in message
> news:9BE9595C-FD19-4557-969D-F46B56985D36@.microsoft.com...
> > I'm sorry, I searched in the newsgroup and I found in many cases there
is
> > nothing to do even with the ALLOW_DATA_LOSS option (I still didn't try
> > it).
> > For the moment I'm waiting for a CHECKTABLE REPAIR_REBUILD to end (it
> > takes
> > hours...)
> >
> > "Argo" wrote:
> >
> >> Is there any possibility to correct such type of allocation problems
> >> without
> >> data loss?
> >> After correction with ALLOW_DATA_LOSS option, is there any tool to
check
> >> for
> >> referential integrity to discover witch records on witch tables
> >> disppeared
> >> with deallocations?
> >> Thanks you all
> >>
>|||Now I executed CHECKTABLE repair_rebuild on all the three tables indicated by
the checkdb (they repaired all the inconsistencies). It has taken 10 hours
because the tables are very big. Now I started a CHECKDB repair_rebuild and I
would like to show you the results without all the inconsistencies fixed by
the checktable commands.
The good backup should be of january 14 because the HW problem occurred on
15 but everything looked OK up to these days. (It is a SAP R/3 Production
System).
I think the possibility to reapply all the logs since then should be
avoided. In this period we had a tremendous activity on this system.(closing
of 2004 accounting)
Isn't it?
"Ryan Stonecipher [MSFT]" wrote:
> If DBCC reports that REPAIR_ALLOW_DATA_LOSS is the repair level required to
> fix these errors, then a REPAIR_REBUILD will not completely remove the
> problem. These are allocation errors, and will likely not be affected by a
> REPAIR_REBUILD. Your best bet is always to restore from a backup. Do you
> have one available?
> --
> Ryan Stonecipher
> Microsoft Sql Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Argo" <falk62@.yahoo.com> wrote in message
> news:9BE9595C-FD19-4557-969D-F46B56985D36@.microsoft.com...
> > I'm sorry, I searched in the newsgroup and I found in many cases there is
> > nothing to do even with the ALLOW_DATA_LOSS option (I still didn't try
> > it).
> > For the moment I'm waiting for a CHECKTABLE REPAIR_REBUILD to end (it
> > takes
> > hours...)
> >
> > "Argo" wrote:
> >
> >> Is there any possibility to correct such type of allocation problems
> >> without
> >> data loss?
> >> After correction with ALLOW_DATA_LOSS option, is there any tool to check
> >> for
> >> referential integrity to discover witch records on witch tables
> >> disppeared
> >> with deallocations?
> >> Thanks you all
> >>
>
>|||I analysed the CHECKDB log. There are 89 consistency errors that have been
fixed with the subsequent CHECKTABLE repair_rebuild commands. But there are
these 5 allocation errors:
Server: Msg 8904, Level 16, State 1, Line 1
Extent (3:1488776) in database ID 7 is allocated by more than one allocation
object.
Server: Msg 8913, Level 16, State 1, Line 1
Extent (3:1488776) is allocated to 'SGAM' and at least one other object.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 0, index ID 0: Page (3:1488782) could not be processed. See other
errors for details.
Server: Msg 8913, Level 16, State 1, Line 1
Extent (3:1488776) is allocated to 'BSAD' and at least one other object.
Server: Msg 8906, Level 16, State 1, Line 1
Page (3:1488782) in database ID 7 is allocated in the SGAM (3:1022465) and
PFS (3:1488192), but was not allocated in any IAM. PFS flags 'IAM_PG
MIXED_EXT ALLOCATED 0_PCT_FULL'.
they all relate to just one extent and one page as I can see. Does the
allow_data_loss level eliminates the problem? In this case I have lost 9
pages of data (8+1=72K)?
Thank you all for your help
p.s. I'm testing the checkconstraints behaviour
"Ryan Stonecipher [MSFT]" wrote:
> If DBCC reports that REPAIR_ALLOW_DATA_LOSS is the repair level required to
> fix these errors, then a REPAIR_REBUILD will not completely remove the
> problem. These are allocation errors, and will likely not be affected by a
> REPAIR_REBUILD. Your best bet is always to restore from a backup. Do you
> have one available?
> --
> Ryan Stonecipher
> Microsoft Sql Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Argo" <falk62@.yahoo.com> wrote in message
> news:9BE9595C-FD19-4557-969D-F46B56985D36@.microsoft.com...
> > I'm sorry, I searched in the newsgroup and I found in many cases there is
> > nothing to do even with the ALLOW_DATA_LOSS option (I still didn't try
> > it).
> > For the moment I'm waiting for a CHECKTABLE REPAIR_REBUILD to end (it
> > takes
> > hours...)
> >
> > "Argo" wrote:
> >
> >> Is there any possibility to correct such type of allocation problems
> >> without
> >> data loss?
> >> After correction with ALLOW_DATA_LOSS option, is there any tool to check
> >> for
> >> referential integrity to discover witch records on witch tables
> >> disppeared
> >> with deallocations?
> >> Thanks you all
> >>
>
>|||I obtained an unexpected result from the execution of CHECKDB with
REPAIR_REBUILD option.
0 allocation errors and 319 consistency errors found
All the 319 consistency errors fixed. They were all Errors 8952 and 8956.
What did it happen? The 5 allocation error disappeared?
Now I'm executing a simple CHEKDB with NO_INFOMSGS,ALL_ERRORMSGS
"Ryan Stonecipher [MSFT]" wrote:
> If DBCC reports that REPAIR_ALLOW_DATA_LOSS is the repair level required to
> fix these errors, then a REPAIR_REBUILD will not completely remove the
> problem. These are allocation errors, and will likely not be affected by a
> REPAIR_REBUILD. Your best bet is always to restore from a backup. Do you
> have one available?
> --
> Ryan Stonecipher
> Microsoft Sql Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Argo" <falk62@.yahoo.com> wrote in message
> news:9BE9595C-FD19-4557-969D-F46B56985D36@.microsoft.com...
> > I'm sorry, I searched in the newsgroup and I found in many cases there is
> > nothing to do even with the ALLOW_DATA_LOSS option (I still didn't try
> > it).
> > For the moment I'm waiting for a CHECKTABLE REPAIR_REBUILD to end (it
> > takes
> > hours...)
> >
> > "Argo" wrote:
> >
> >> Is there any possibility to correct such type of allocation problems
> >> without
> >> data loss?
> >> After correction with ALLOW_DATA_LOSS option, is there any tool to check
> >> for
> >> referential integrity to discover witch records on witch tables
> >> disppeared
> >> with deallocations?
> >> Thanks you all
> >>
>
>|||Hi
After a DB has gone corrupt, and having 'fixed' it, I always DTS all the
data out into another DB. I don't trust the original DB anymore. Use the new
DB as the working one and the 'fixed' one as a reference in case you are
worried about data loss.
The problem with SAP is that it does not use FK constraints, so knowing if
all the data is there after a fix, is difficult.
If you have a backup from before the corruption, with all the logs to the
present, do a restore. Use this to verify how much data you have lost by
comparing it to your fixed DB.
At the same time, tell Finance that they have to sign-off on the reports
they run after you fix the DB. Then you have covered your ass, in case they
come to you next year and say "you know, you lost us more data than what we
though" and blame you for the company going bust.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Argo" <falk62@.yahoo.com> wrote in message
news:875CCF2F-8169-43D3-9D57-85520FEA261C@.microsoft.com...
> I obtained an unexpected result from the execution of CHECKDB with
> REPAIR_REBUILD option.
> 0 allocation errors and 319 consistency errors found
> All the 319 consistency errors fixed. They were all Errors 8952 and 8956.
> What did it happen? The 5 allocation error disappeared?
> Now I'm executing a simple CHEKDB with NO_INFOMSGS,ALL_ERRORMSGS
>
>
> "Ryan Stonecipher [MSFT]" wrote:
> > If DBCC reports that REPAIR_ALLOW_DATA_LOSS is the repair level required
to
> > fix these errors, then a REPAIR_REBUILD will not completely remove the
> > problem. These are allocation errors, and will likely not be affected
by a
> > REPAIR_REBUILD. Your best bet is always to restore from a backup. Do
you
> > have one available?
> >
> > --
> > Ryan Stonecipher
> > Microsoft Sql Server Storage Engine, DBCC
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "Argo" <falk62@.yahoo.com> wrote in message
> > news:9BE9595C-FD19-4557-969D-F46B56985D36@.microsoft.com...
> > > I'm sorry, I searched in the newsgroup and I found in many cases there
is
> > > nothing to do even with the ALLOW_DATA_LOSS option (I still didn't try
> > > it).
> > > For the moment I'm waiting for a CHECKTABLE REPAIR_REBUILD to end (it
> > > takes
> > > hours...)
> > >
> > > "Argo" wrote:
> > >
> > >> Is there any possibility to correct such type of allocation problems
> > >> without
> > >> data loss?
> > >> After correction with ALLOW_DATA_LOSS option, is there any tool to
check
> > >> for
> > >> referential integrity to discover witch records on witch tables
> > >> disppeared
> > >> with deallocations?
> > >> Thanks you all
> > >>
> >
> >
> >|||Sometimes a single error can 'hide' other errors by preventing a particular
set of checks running. In this case it seems like one of the previous
rebuild operations you performed fixed an allocation error that was
preventing the non-clustered index cross-checks from running (this check is
what found the 8952 and 8956 errors). This is completely expected behavior.
Without studying the initial list of errors from CHECKDB as well as your
database in the corrupted state, I can't tell the exact sequence of events
but what I've said seems the most likely (and you're not going to get a
better explanation from anyone else I'm afraid)
Now that you've run all these repairs, your database's inherent business
logic is likely to be broken unless you've taken pains to keep track of
exactly what's been done by repair.
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Argo" <falk62@.yahoo.com> wrote in message
news:875CCF2F-8169-43D3-9D57-85520FEA261C@.microsoft.com...
> I obtained an unexpected result from the execution of CHECKDB with
> REPAIR_REBUILD option.
> 0 allocation errors and 319 consistency errors found
> All the 319 consistency errors fixed. They were all Errors 8952 and 8956.
> What did it happen? The 5 allocation error disappeared?
> Now I'm executing a simple CHEKDB with NO_INFOMSGS,ALL_ERRORMSGS
>
>
> "Ryan Stonecipher [MSFT]" wrote:
> > If DBCC reports that REPAIR_ALLOW_DATA_LOSS is the repair level required
to
> > fix these errors, then a REPAIR_REBUILD will not completely remove the
> > problem. These are allocation errors, and will likely not be affected
by a
> > REPAIR_REBUILD. Your best bet is always to restore from a backup. Do
you
> > have one available?
> >
> > --
> > Ryan Stonecipher
> > Microsoft Sql Server Storage Engine, DBCC
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "Argo" <falk62@.yahoo.com> wrote in message
> > news:9BE9595C-FD19-4557-969D-F46B56985D36@.microsoft.com...
> > > I'm sorry, I searched in the newsgroup and I found in many cases there
is
> > > nothing to do even with the ALLOW_DATA_LOSS option (I still didn't try
> > > it).
> > > For the moment I'm waiting for a CHECKTABLE REPAIR_REBUILD to end (it
> > > takes
> > > hours...)
> > >
> > > "Argo" wrote:
> > >
> > >> Is there any possibility to correct such type of allocation problems
> > >> without
> > >> data loss?
> > >> After correction with ALLOW_DATA_LOSS option, is there any tool to
check
> > >> for
> > >> referential integrity to discover witch records on witch tables
> > >> disppeared
> > >> with deallocations?
> > >> Thanks you all
> > >>
> >
> >
> >|||In a previous post I specified the initial 5 allocation errors, if it can be
useful for you...
The CHECKDB with repair_fast option fixed 1 more 8952/8956 error. Now I
launched a new CHECKDB (without any option) that will end in about 9 hours
from now.
I didn't understand the last part of your post. What do you mean when you
say: "...your database's inherent business logic is likely to be broken
unless you've taken pains to keep track of exactly what's been done by
repair..."?
I have printed all the subsequent errors found and fixed by the DBCC tool.
I kept track of the order of actions I executed so I can reapply them in the
production system step by step.
Is business logic most likely lost anyway? In this case I can't rely on this
solution because it's hard to verify SAP's business logic (see Mike Epprecht
post)
Thanks
Andrea
"Paul S Randal [MS]" wrote:
> Sometimes a single error can 'hide' other errors by preventing a particular
> set of checks running. In this case it seems like one of the previous
> rebuild operations you performed fixed an allocation error that was
> preventing the non-clustered index cross-checks from running (this check is
> what found the 8952 and 8956 errors). This is completely expected behavior.
> Without studying the initial list of errors from CHECKDB as well as your
> database in the corrupted state, I can't tell the exact sequence of events
> but what I've said seems the most likely (and you're not going to get a
> better explanation from anyone else I'm afraid)
> Now that you've run all these repairs, your database's inherent business
> logic is likely to be broken unless you've taken pains to keep track of
> exactly what's been done by repair.
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Argo" <falk62@.yahoo.com> wrote in message
> news:875CCF2F-8169-43D3-9D57-85520FEA261C@.microsoft.com...
> > I obtained an unexpected result from the execution of CHECKDB with
> > REPAIR_REBUILD option.
> > 0 allocation errors and 319 consistency errors found
> > All the 319 consistency errors fixed. They were all Errors 8952 and 8956.
> > What did it happen? The 5 allocation error disappeared?
> >
> > Now I'm executing a simple CHEKDB with NO_INFOMSGS,ALL_ERRORMSGS
> >
> >
> >
> >
> > "Ryan Stonecipher [MSFT]" wrote:
> >
> > > If DBCC reports that REPAIR_ALLOW_DATA_LOSS is the repair level required
> to
> > > fix these errors, then a REPAIR_REBUILD will not completely remove the
> > > problem. These are allocation errors, and will likely not be affected
> by a
> > > REPAIR_REBUILD. Your best bet is always to restore from a backup. Do
> you
> > > have one available?
> > >
> > > --
> > > Ryan Stonecipher
> > > Microsoft Sql Server Storage Engine, DBCC
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > > "Argo" <falk62@.yahoo.com> wrote in message
> > > news:9BE9595C-FD19-4557-969D-F46B56985D36@.microsoft.com...
> > > > I'm sorry, I searched in the newsgroup and I found in many cases there
> is
> > > > nothing to do even with the ALLOW_DATA_LOSS option (I still didn't try
> > > > it).
> > > > For the moment I'm waiting for a CHECKTABLE REPAIR_REBUILD to end (it
> > > > takes
> > > > hours...)
> > > >
> > > > "Argo" wrote:
> > > >
> > > >> Is there any possibility to correct such type of allocation problems
> > > >> without
> > > >> data loss?
> > > >> After correction with ALLOW_DATA_LOSS option, is there any tool to
> check
> > > >> for
> > > >> referential integrity to discover witch records on witch tables
> > > >> disppeared
> > > >> with deallocations?
> > > >> Thanks you all
> > > >>
> > >
> > >
> > >
>
>|||Finally the checkdb is OK. There are no errors. The complete sequence of
actions I executed is:
1) initial checkdb (5 allocation errors 89 consistency error)
(repair_allow_data_loss minimum required)
2) checktable (BSIS,repair_rebuild) 55 consistency error fixed
3) checktable (BSE_CLR,repair_rebuild) 28 consistency error fixed
4) checktable (BSAD,repair_rebuild) 6 consistency error fixed
5) checkdb with repair_rebuild 319 new consistency error fixed
6) checkdb 1 more consistency error found (repair_fast minimun required)
7) checkdb with repair_fast (1 consistency error fixed)
8) final checkdb (NO ERRORS)
As you can see I never used more than repair_rebuild level checks.
May I be sure I didn't lose any data?
Why you say that "...Now that you've run all these repairs, your database's
inherent business logic is likely to be broken ..."' What do you mean?
Thank you Paul
"Paul S Randal [MS]" wrote:
> Sometimes a single error can 'hide' other errors by preventing a particular
> set of checks running. In this case it seems like one of the previous
> rebuild operations you performed fixed an allocation error that was
> preventing the non-clustered index cross-checks from running (this check is
> what found the 8952 and 8956 errors). This is completely expected behavior.
> Without studying the initial list of errors from CHECKDB as well as your
> database in the corrupted state, I can't tell the exact sequence of events
> but what I've said seems the most likely (and you're not going to get a
> better explanation from anyone else I'm afraid)
> Now that you've run all these repairs, your database's inherent business
> logic is likely to be broken unless you've taken pains to keep track of
> exactly what's been done by repair.
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Argo" <falk62@.yahoo.com> wrote in message
> news:875CCF2F-8169-43D3-9D57-85520FEA261C@.microsoft.com...
> > I obtained an unexpected result from the execution of CHECKDB with
> > REPAIR_REBUILD option.
> > 0 allocation errors and 319 consistency errors found
> > All the 319 consistency errors fixed. They were all Errors 8952 and 8956.
> > What did it happen? The 5 allocation error disappeared?
> >
> > Now I'm executing a simple CHEKDB with NO_INFOMSGS,ALL_ERRORMSGS
> >
> >
> >
> >
> > "Ryan Stonecipher [MSFT]" wrote:
> >
> > > If DBCC reports that REPAIR_ALLOW_DATA_LOSS is the repair level required
> to
> > > fix these errors, then a REPAIR_REBUILD will not completely remove the
> > > problem. These are allocation errors, and will likely not be affected
> by a
> > > REPAIR_REBUILD. Your best bet is always to restore from a backup. Do
> you
> > > have one available?
> > >
> > > --
> > > Ryan Stonecipher
> > > Microsoft Sql Server Storage Engine, DBCC
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > > "Argo" <falk62@.yahoo.com> wrote in message
> > > news:9BE9595C-FD19-4557-969D-F46B56985D36@.microsoft.com...
> > > > I'm sorry, I searched in the newsgroup and I found in many cases there
> is
> > > > nothing to do even with the ALLOW_DATA_LOSS option (I still didn't try
> > > > it).
> > > > For the moment I'm waiting for a CHECKTABLE REPAIR_REBUILD to end (it
> > > > takes
> > > > hours...)
> > > >
> > > > "Argo" wrote:
> > > >
> > > >> Is there any possibility to correct such type of allocation problems
> > > >> without
> > > >> data loss?
> > > >> After correction with ALLOW_DATA_LOSS option, is there any tool to
> check
> > > >> for
> > > >> referential integrity to discover witch records on witch tables
> > > >> disppeared
> > > >> with deallocations?
> > > >> Thanks you all
> > > >>
> > >
> > >
> > >
>
>

Inconsistencies Errors 8904 8913 8928 8906

Is there any possibility to correct such type of allocation problems without
data loss?
After correction with ALLOW_DATA_LOSS option, is there any tool to check for
referential integrity to discover witch records on witch tables disppeared
with deallocations?
Thanks you all
I'm sorry, I searched in the newsgroup and I found in many cases there is
nothing to do even with the ALLOW_DATA_LOSS option (I still didn't try it).
For the moment I'm waiting for a CHECKTABLE REPAIR_REBUILD to end (it takes
hours...)
"Argo" wrote:

> Is there any possibility to correct such type of allocation problems without
> data loss?
> After correction with ALLOW_DATA_LOSS option, is there any tool to check for
> referential integrity to discover witch records on witch tables disppeared
> with deallocations?
> Thanks you all
>
|||If DBCC reports that REPAIR_ALLOW_DATA_LOSS is the repair level required to
fix these errors, then a REPAIR_REBUILD will not completely remove the
problem. These are allocation errors, and will likely not be affected by a
REPAIR_REBUILD. Your best bet is always to restore from a backup. Do you
have one available?
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Argo" <falk62@.yahoo.com> wrote in message
news:9BE9595C-FD19-4557-969D-F46B56985D36@.microsoft.com...[vbcol=seagreen]
> I'm sorry, I searched in the newsgroup and I found in many cases there is
> nothing to do even with the ALLOW_DATA_LOSS option (I still didn't try
> it).
> For the moment I'm waiting for a CHECKTABLE REPAIR_REBUILD to end (it
> takes
> hours...)
> "Argo" wrote:
|||Regarding your question about checking constraints, the only tool in SQL
Server is DBCC CHECKCONSTRAINTS which will validate FOREIGN KEY and CHECK
constraints. There's nothing that will validate referential integrity
constraints.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ryan Stonecipher [MSFT]" <ryanston@.microsoft.com> wrote in message
news:OCSCHmuCFHA.2608@.TK2MSFTNGP10.phx.gbl...
> If DBCC reports that REPAIR_ALLOW_DATA_LOSS is the repair level required
to
> fix these errors, then a REPAIR_REBUILD will not completely remove the
> problem. These are allocation errors, and will likely not be affected by
a
> REPAIR_REBUILD. Your best bet is always to restore from a backup. Do you
> have one available?
> --
> Ryan Stonecipher
> Microsoft Sql Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Argo" <falk62@.yahoo.com> wrote in message
> news:9BE9595C-FD19-4557-969D-F46B56985D36@.microsoft.com...
is[vbcol=seagreen]
check
>
|||Now I executed CHECKTABLE repair_rebuild on all the three tables indicated by
the checkdb (they repaired all the inconsistencies). It has taken 10 hours
because the tables are very big. Now I started a CHECKDB repair_rebuild and I
would like to show you the results without all the inconsistencies fixed by
the checktable commands.
The good backup should be of january 14 because the HW problem occurred on
15 but everything looked OK up to these days. (It is a SAP R/3 Production
System).
I think the possibility to reapply all the logs since then should be
avoided. In this period we had a tremendous activity on this system.(closing
of 2004 accounting)
Isn't it?
"Ryan Stonecipher [MSFT]" wrote:

> If DBCC reports that REPAIR_ALLOW_DATA_LOSS is the repair level required to
> fix these errors, then a REPAIR_REBUILD will not completely remove the
> problem. These are allocation errors, and will likely not be affected by a
> REPAIR_REBUILD. Your best bet is always to restore from a backup. Do you
> have one available?
> --
> Ryan Stonecipher
> Microsoft Sql Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Argo" <falk62@.yahoo.com> wrote in message
> news:9BE9595C-FD19-4557-969D-F46B56985D36@.microsoft.com...
>
>
|||I analysed the CHECKDB log. There are 89 consistency errors that have been
fixed with the subsequent CHECKTABLE repair_rebuild commands. But there are
these 5 allocation errors:
Server: Msg 8904, Level 16, State 1, Line 1
Extent (3:1488776) in database ID 7 is allocated by more than one allocation
object.
Server: Msg 8913, Level 16, State 1, Line 1
Extent (3:1488776) is allocated to 'SGAM' and at least one other object.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 0, index ID 0: Page (3:1488782) could not be processed. See other
errors for details.
Server: Msg 8913, Level 16, State 1, Line 1
Extent (3:1488776) is allocated to 'BSAD' and at least one other object.
Server: Msg 8906, Level 16, State 1, Line 1
Page (3:1488782) in database ID 7 is allocated in the SGAM (3:1022465) and
PFS (3:1488192), but was not allocated in any IAM. PFS flags 'IAM_PG
MIXED_EXT ALLOCATED 0_PCT_FULL'.
they all relate to just one extent and one page as I can see. Does the
allow_data_loss level eliminates the problem? In this case I have lost 9
pages of data (8+1=72K)?
Thank you all for your help
p.s. I'm testing the checkconstraints behaviour
"Ryan Stonecipher [MSFT]" wrote:

> If DBCC reports that REPAIR_ALLOW_DATA_LOSS is the repair level required to
> fix these errors, then a REPAIR_REBUILD will not completely remove the
> problem. These are allocation errors, and will likely not be affected by a
> REPAIR_REBUILD. Your best bet is always to restore from a backup. Do you
> have one available?
> --
> Ryan Stonecipher
> Microsoft Sql Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Argo" <falk62@.yahoo.com> wrote in message
> news:9BE9595C-FD19-4557-969D-F46B56985D36@.microsoft.com...
>
>

Inconsistencies Errors 8904 8913 8928 8906

Is there any possibility to correct such type of allocation problems without
data loss?
After correction with ALLOW_DATA_LOSS option, is there any tool to check for
referential integrity to discover witch records on witch tables disppeared
with deallocations?
Thanks you allI'm sorry, I searched in the newsgroup and I found in many cases there is
nothing to do even with the ALLOW_DATA_LOSS option (I still didn't try it).
For the moment I'm waiting for a CHECKTABLE REPAIR_REBUILD to end (it takes
hours...)
"Argo" wrote:

> Is there any possibility to correct such type of allocation problems witho
ut
> data loss?
> After correction with ALLOW_DATA_LOSS option, is there any tool to check f
or
> referential integrity to discover witch records on witch tables disppeared
> with deallocations?
> Thanks you all
>|||If DBCC reports that REPAIR_ALLOW_DATA_LOSS is the repair level required to
fix these errors, then a REPAIR_REBUILD will not completely remove the
problem. These are allocation errors, and will likely not be affected by a
REPAIR_REBUILD. Your best bet is always to restore from a backup. Do you
have one available?
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Argo" <falk62@.yahoo.com> wrote in message
news:9BE9595C-FD19-4557-969D-F46B56985D36@.microsoft.com...[vbcol=seagreen]
> I'm sorry, I searched in the newsgroup and I found in many cases there is
> nothing to do even with the ALLOW_DATA_LOSS option (I still didn't try
> it).
> For the moment I'm waiting for a CHECKTABLE REPAIR_REBUILD to end (it
> takes
> hours...)
> "Argo" wrote:
>|||Regarding your question about checking constraints, the only tool in SQL
Server is DBCC CHECKCONSTRAINTS which will validate FOREIGN KEY and CHECK
constraints. There's nothing that will validate referential integrity
constraints.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ryan Stonecipher [MSFT]" <ryanston@.microsoft.com> wrote in message
news:OCSCHmuCFHA.2608@.TK2MSFTNGP10.phx.gbl...
> If DBCC reports that REPAIR_ALLOW_DATA_LOSS is the repair level required
to
> fix these errors, then a REPAIR_REBUILD will not completely remove the
> problem. These are allocation errors, and will likely not be affected by
a
> REPAIR_REBUILD. Your best bet is always to restore from a backup. Do you
> have one available?
> --
> Ryan Stonecipher
> Microsoft Sql Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Argo" <falk62@.yahoo.com> wrote in message
> news:9BE9595C-FD19-4557-969D-F46B56985D36@.microsoft.com...
is[vbcol=seagreen]
check[vbcol=seagreen]
>|||Now I executed CHECKTABLE repair_rebuild on all the three tables indicated b
y
the checkdb (they repaired all the inconsistencies). It has taken 10 hours
because the tables are very big. Now I started a CHECKDB repair_rebuild and
I
would like to show you the results without all the inconsistencies fixed by
the checktable commands.
The good backup should be of january 14 because the HW problem occurred on
15 but everything looked OK up to these days. (It is a SAP R/3 Production
System).
I think the possibility to reapply all the logs since then should be
avoided. In this period we had a tremendous activity on this system.(closing
of 2004 accounting)
Isn't it?
"Ryan Stonecipher [MSFT]" wrote:

> If DBCC reports that REPAIR_ALLOW_DATA_LOSS is the repair level required t
o
> fix these errors, then a REPAIR_REBUILD will not completely remove the
> problem. These are allocation errors, and will likely not be affected by
a
> REPAIR_REBUILD. Your best bet is always to restore from a backup. Do you
> have one available?
> --
> Ryan Stonecipher
> Microsoft Sql Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Argo" <falk62@.yahoo.com> wrote in message
> news:9BE9595C-FD19-4557-969D-F46B56985D36@.microsoft.com...
>
>|||I analysed the CHECKDB log. There are 89 consistency errors that have been
fixed with the subsequent CHECKTABLE repair_rebuild commands. But there are
these 5 allocation errors:
Server: Msg 8904, Level 16, State 1, Line 1
Extent (3:1488776) in database ID 7 is allocated by more than one allocation
object.
Server: Msg 8913, Level 16, State 1, Line 1
Extent (3:1488776) is allocated to 'SGAM' and at least one other object.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 0, index ID 0: Page (3:1488782) could not be processed. See other
errors for details.
Server: Msg 8913, Level 16, State 1, Line 1
Extent (3:1488776) is allocated to 'BSAD' and at least one other object.
Server: Msg 8906, Level 16, State 1, Line 1
Page (3:1488782) in database ID 7 is allocated in the SGAM (3:1022465) and
PFS (3:1488192), but was not allocated in any IAM. PFS flags 'IAM_PG
MIXED_EXT ALLOCATED 0_PCT_FULL'.
they all relate to just one extent and one page as I can see. Does the
allow_data_loss level eliminates the problem? In this case I have lost 9
pages of data (8+1=72K)?
Thank you all for your help
p.s. I'm testing the checkconstraints behaviour
"Ryan Stonecipher [MSFT]" wrote:

> If DBCC reports that REPAIR_ALLOW_DATA_LOSS is the repair level required t
o
> fix these errors, then a REPAIR_REBUILD will not completely remove the
> problem. These are allocation errors, and will likely not be affected by
a
> REPAIR_REBUILD. Your best bet is always to restore from a backup. Do you
> have one available?
> --
> Ryan Stonecipher
> Microsoft Sql Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Argo" <falk62@.yahoo.com> wrote in message
> news:9BE9595C-FD19-4557-969D-F46B56985D36@.microsoft.com...
>
>

Including SQL Server Allow Nulls fields in updateable controls

When I include a field from my SQL Server database, which has it's Allow Nulls value checked, in the data source of any type of control with it's Enable Editing property check, I then can not edit the record! If I remove the Allow Nulls field I can then edit it! What am I missing here?

If your record is going to have null values in it (which is quite common) you have to add a DataSet file to your site, create a TableAdapter and then use the TableAdapter as the data source 'Object' for the data control. ... ... Wait, ... let me breath... ... that's not the end of it... ... ... When choosing the data source for the control, make sure when you get to the Define Parameters dialog that the advanced property ConvertEmptyStringToNull is true. It took me about 12 hours to figure this out but I have also read that it has taken others up to a week so I guess I'm doing good. I REALLY WISH MICROSOFT WOULD HAVE SPELLED THIS OUT MORE CLEARLY IN THEIR HELP FILES.

IN(@variable) clause and Table Data Type variable

Hi this question has been asked several times and some solution has been
provided already. But the one I am facing is with a twist. I need to use the
IN() clause with a variable as its parameter. The variable is a list of comm
a
separated character values all enclosed in pairs of single quotes. I could
have solved this problem by enclosing the final query in a single quote and
running Exec command on it (with the Variable list outside the quotes) but I
also need to use a Table data type variable which raises error when EXEC
command is run.
Followig is the example that may explain well.
I have oversimplified this example and it does things that we would not do
in normal situation
use pubs;
-- declare and set Table variable
declare @.TableVariable TABLE ( col char(4) );
INSERT @.TableVariable
Select pub_id FROM publishers
;
--declare and set CSV single quoted characters list
declare @.ListVariable varchar(100);
set @.ListVariable = ' ''0736'', ''0877'' '; --these pub_ids are in the
publishers table, promise
--the query where the Table variable is used as well as the IN() clause is
used
Select TableVariable.col
From @.TableVariable as TableVariable
Where TableVariable.col IN (@.ListVariable)
-- returns 0 rows
--if we use Exec by replacing the last code section above with as following
declare @.command varchar(2000)
set @.command='
Select TableVariable.col
From @.TableVariable as TableVariable
Where TableVariable.col IN ('+@.ListVariable+')
'
exec (@.command)
--Then we get the error message:
-- Must declare the variable '@.TableVariable'.
Expand AllCollapse All
Manage Your Profile |Legal |Contact Us |MSDN Flash NewsletterI'd suggest using something like this. in (select id from tablname)
in your dynamic SQL example don't use the variable.
Set @.command = ' Select TableVariable.col
>From @.TableVariable as TableVariable
Where TableVariable.col IN ('
loop on list
select @.command = @.command + each number
End
select @.command = @.command + ')'
exec (@.command)|||Use a temp table instead.
David Gugick
Imceda Software
www.imceda.com|||http://www.sommarskog.se/arrays-in-sql.html
David Portas
SQL Server MVP
--
"Aamir Ghanchi" <AamirGhanchi@.discussions.microsoft.com> wrote in message
news:EAE233F0-A1C4-4CEF-9F72-8EB4EB4884E2@.microsoft.com...
> Hi this question has been asked several times and some solution has been
> provided already. But the one I am facing is with a twist. I need to use
> the
> IN() clause with a variable as its parameter. The variable is a list of
> comma
> separated character values all enclosed in pairs of single quotes. I could
> have solved this problem by enclosing the final query in a single quote
> and
> running Exec command on it (with the Variable list outside the quotes) but
> I
> also need to use a Table data type variable which raises error when EXEC
> command is run.
> Followig is the example that may explain well.
> I have oversimplified this example and it does things that we would not do
> in normal situation
> use pubs;
> -- declare and set Table variable
> declare @.TableVariable TABLE ( col char(4) );
> INSERT @.TableVariable
> Select pub_id FROM publishers
> ;
> --declare and set CSV single quoted characters list
> declare @.ListVariable varchar(100);
> set @.ListVariable = ' ''0736'', ''0877'' '; --these pub_ids are in the
> publishers table, promise
> --the query where the Table variable is used as well as the IN() clause is
> used
> Select TableVariable.col
> From @.TableVariable as TableVariable
> Where TableVariable.col IN (@.ListVariable)
> -- returns 0 rows
> --if we use Exec by replacing the last code section above with as
> following
> declare @.command varchar(2000)
> set @.command='
> Select TableVariable.col
> From @.TableVariable as TableVariable
> Where TableVariable.col IN ('+@.ListVariable+')
> '
> exec (@.command)
> --Then we get the error message:
> -- Must declare the variable '@.TableVariable'.
>
> Expand AllCollapse All
>
> Manage Your Profile |Legal |Contact Us |MSDN Flash Newsletter
>|||On Mon, 7 Feb 2005 14:31:03 -0800, Aamir Ghanchi wrote:
(snip)
I have already answered this question in .server, even before you posted
it (twice!) to this group. Please don't multi-post.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Good thought. and this is what I did initially. but my IN() clause is
enclosed within the aggregate function SUM and subquery is not allowed in it
.
Thanks though.
"Paul Moore" wrote:

> I'd suggest using something like this. in (select id from tablname)
>
> in your dynamic SQL example don't use the variable.
> Set @.command = ' Select TableVariable.col
> Where TableVariable.col IN ('
> loop on list
> select @.command = @.command + each number
> End
> select @.command = @.command + ')'
> exec (@.command)
>|||slow though, but I guess thats the only viable option I am left with.
Thank you.
"David Gugick" wrote:

> Use a temp table instead.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||I understand. I just started using MSDN website for posting messages since
the new Google Groups interface eats up all the indentation on code snippets
.
Bad thing, I can't crosspost through MSDN site :(
thanks.
"Hugo Kornelis" wrote:

> On Mon, 7 Feb 2005 14:31:03 -0800, Aamir Ghanchi wrote:
> (snip)
> I have already answered this question in .server, even before you posted
> it (twice!) to this group. Please don't multi-post.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||>> but my IN() clause is enclosed within the aggregate function SUM
and subquery is not allowed in it. <<
What did you think that the arithmetic sum of a logical expression
would be anyway? Think about it for two seconds.

IN(@ListVariable) and TABLE Data Type

Hi this question has been asked several times and some solution has been
provided already. But the one I am facing is with a twist. I need to use the
IN() clause with a variable as its parameter. The variable is a list of comma
separated character values all enclosed in pairs of single quotes. I could
have solved this problem by enclosing the final query in a single quote and
running Exec command on it (with the Variable list outside the quotes) but I
also need to use a Table data type variable which raises error when EXEC
command is run.
Followig is the example that may explain well.
I have oversimplified this example and it does things that we would not do
in normal situation
use pubs;
-- declare and set Table variable
declare @.TableVariable TABLE ( col char(4) );
INSERT @.TableVariable
Select pub_id FROM publishers
;
--declare and set CSV single quoted characters list
declare @.ListVariable varchar(100);
set @.ListVariable = ' ''0736'', ''0877'' '; --these pub_ids are in the
publishers table, promise
--the query where the Table variable is used as well as the IN() clause is
used
Select TableVariable.col
From @.TableVariable as TableVariable
Where TableVariable.col IN (@.ListVariable)
-- returns 0 rows
--if we use Exec by replacing the last code section above with as following
declare @.command varchar(2000)
set @.command='
Select TableVariable.col
From @.TableVariable as TableVariable
Where TableVariable.col IN ('+@.ListVariable+')
'
exec (@.command)
--Then we get the error message:
-- Must declare the variable '@.TableVariable'.
On Mon, 7 Feb 2005 13:27:06 -0800, "aamirghanchi@.yahoo.com"
<aamirghanchi@.yahoo.com@.discussions.microsoft.com> wrote:

> I need to use the
>IN() clause with a variable as its parameter. The variable is a list of comma
>separated character values all enclosed in pairs of single quotes. I could
>have solved this problem by enclosing the final query in a single quote and
>running Exec command on it (with the Variable list outside the quotes) but I
>also need to use a Table data type variable which raises error when EXEC
>command is run.
Hi aamirqhanchi,
http://www.sommarskog.se/arrays-in-sql.html
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

IN(@ListVariable) and TABLE Data Type

Hi this question has been asked several times and some solution has been
provided already. But the one I am facing is with a twist. I need to use the
IN() clause with a variable as its parameter. The variable is a list of comm
a
separated character values all enclosed in pairs of single quotes. I could
have solved this problem by enclosing the final query in a single quote and
running Exec command on it (with the Variable list outside the quotes) but I
also need to use a Table data type variable which raises error when EXEC
command is run.
Followig is the example that may explain well.
I have oversimplified this example and it does things that we would not do
in normal situation
use pubs;
-- declare and set Table variable
declare @.TableVariable TABLE ( col char(4) );
INSERT @.TableVariable
Select pub_id FROM publishers
;
--declare and set CSV single quoted characters list
declare @.ListVariable varchar(100);
set @.ListVariable = ' ''0736'', ''0877'' '; --these pub_ids are in the
publishers table, promise
--the query where the Table variable is used as well as the IN() clause is
used
Select TableVariable.col
From @.TableVariable as TableVariable
Where TableVariable.col IN (@.ListVariable)
-- returns 0 rows
--if we use Exec by replacing the last code section above with as following
declare @.command varchar(2000)
set @.command='
Select TableVariable.col
From @.TableVariable as TableVariable
Where TableVariable.col IN ('+@.ListVariable+')
'
exec (@.command)
--Then we get the error message:
-- Must declare the variable '@.TableVariable'.On Mon, 7 Feb 2005 13:27:06 -0800, "aamirghanchi@.yahoo.com"
<aamirghanchi@.yahoo.com@.discussions.microsoft.com> wrote:

> I need to use the
>IN() clause with a variable as its parameter. The variable is a list of com
ma
>separated character values all enclosed in pairs of single quotes. I could
>have solved this problem by enclosing the final query in a single quote and
>running Exec command on it (with the Variable list outside the quotes) but
I
>also need to use a Table data type variable which raises error when EXEC
>command is run.
Hi aamirqhanchi,
http://www.sommarskog.se/arrays-in-sql.html
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

IN(@ListVariable) and TABLE Data Type

Hi this question has been asked several times and some solution has been
provided already. But the one I am facing is with a twist. I need to use the
IN() clause with a variable as its parameter. The variable is a list of comma
separated character values all enclosed in pairs of single quotes. I could
have solved this problem by enclosing the final query in a single quote and
running Exec command on it (with the Variable list outside the quotes) but I
also need to use a Table data type variable which raises error when EXEC
command is run.
Followig is the example that may explain well.
I have oversimplified this example and it does things that we would not do
in normal situation
use pubs;
-- declare and set Table variable
declare @.TableVariable TABLE ( col char(4) );
INSERT @.TableVariable
Select pub_id FROM publishers
;
--declare and set CSV single quoted characters list
declare @.ListVariable varchar(100);
set @.ListVariable = ' ''0736'', ''0877'' '; --these pub_ids are in the
publishers table, promise
--the query where the Table variable is used as well as the IN() clause is
used
Select TableVariable.col
From @.TableVariable as TableVariable
Where TableVariable.col IN (@.ListVariable)
-- returns 0 rows
--if we use Exec by replacing the last code section above with as following
declare @.command varchar(2000)
set @.command='
Select TableVariable.col
From @.TableVariable as TableVariable
Where TableVariable.col IN ('+@.ListVariable+')
'
exec (@.command)
--Then we get the error message:
-- Must declare the variable '@.TableVariable'.On Mon, 7 Feb 2005 13:27:06 -0800, "aamirghanchi@.yahoo.com"
<aamirghanchi@.yahoo.com@.discussions.microsoft.com> wrote:
> I need to use the
>IN() clause with a variable as its parameter. The variable is a list of comma
>separated character values all enclosed in pairs of single quotes. I could
>have solved this problem by enclosing the final query in a single quote and
>running Exec command on it (with the Variable list outside the quotes) but I
>also need to use a Table data type variable which raises error when EXEC
>command is run.
Hi aamirqhanchi,
http://www.sommarskog.se/arrays-in-sql.html
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

In SQL2000 what is = to MySQL's field type of LONGTEXT?

I just found out that MYSQL handels LONGTEXT and I know that SQL2000 does not
have such a field type. When I first was developing my DB application I tried
to use BLOB's unsuccessfully and ended up using an XML data solution instead
for large text amounts. (The application is simple field storing webpage
content.) Currenly I've configured the text fields as VarChars but that
definitely has some limits. Thanks if you can help.
SQL Server 2000's version is TEXT (a member of the BLOB family)
SQL Server 2005 has NVARCHAR(MAX) which can be used a lot easier.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"bobobenito" <bobobenito@.discussions.microsoft.com> wrote in message
news:975E78A2-92DC-4300-AF7E-A1D73DC9DB58@.microsoft.com...
>I just found out that MYSQL handels LONGTEXT and I know that SQL2000 does
>not
> have such a field type. When I first was developing my DB application I
> tried
> to use BLOB's unsuccessfully and ended up using an XML data solution
> instead
> for large text amounts. (The application is simple field storing webpage
> content.) Currenly I've configured the text fields as VarChars but that
> definitely has some limits. Thanks if you can help.

In SQL2000 what is = to MySQL's field type of LONGTEXT?

I just found out that mysql handels LONGTEXT and I know that SQL2000 does no
t
have such a field type. When I first was developing my DB application I trie
d
to use BLOB's unsuccessfully and ended up using an XML data solution instead
for large text amounts. (The application is simple field storing webpage
content.) Currenly I've configured the text fields as VarChars but that
definitely has some limits. Thanks if you can help.SQL Server 2000's version is TEXT (a member of the BLOB family)
SQL Server 2005 has NVARCHAR(MAX) which can be used a lot easier.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"bobobenito" <bobobenito@.discussions.microsoft.com> wrote in message
news:975E78A2-92DC-4300-AF7E-A1D73DC9DB58@.microsoft.com...
>I just found out that mysql handels LONGTEXT and I know that SQL2000 does
>not
> have such a field type. When I first was developing my DB application I
> tried
> to use BLOB's unsuccessfully and ended up using an XML data solution
> instead
> for large text amounts. (The application is simple field storing webpage
> content.) Currenly I've configured the text fields as VarChars but that
> definitely has some limits. Thanks if you can help.

In SQL2000 what is = to MySQL's field type of LONGTEXT?

I just found out that MYSQL handels LONGTEXT and I know that SQL2000 does not
have such a field type. When I first was developing my DB application I tried
to use BLOB's unsuccessfully and ended up using an XML data solution instead
for large text amounts. (The application is simple field storing webpage
content.) Currenly I've configured the text fields as VarChars but that
definitely has some limits. Thanks if you can help.SQL Server 2000's version is TEXT (a member of the BLOB family)
SQL Server 2005 has NVARCHAR(MAX) which can be used a lot easier.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"bobobenito" <bobobenito@.discussions.microsoft.com> wrote in message
news:975E78A2-92DC-4300-AF7E-A1D73DC9DB58@.microsoft.com...
>I just found out that MYSQL handels LONGTEXT and I know that SQL2000 does
>not
> have such a field type. When I first was developing my DB application I
> tried
> to use BLOB's unsuccessfully and ended up using an XML data solution
> instead
> for large text amounts. (The application is simple field storing webpage
> content.) Currenly I've configured the text fields as VarChars but that
> definitely has some limits. Thanks if you can help.