I have an old application running on SQL Server 6.5 (6.50.416) that has a number of internal issues - inability to dump the transaction logs (would dump 9gb out of a 10gb datbase everytime regardless of truncating the logs), transaction log size of 0 even though 700mb allocated, very long DBCC's (though they complete properly).
I am trying to salvage the database by using DTS to copy out the schema, data, sp's, etc. into a new database.
I have the conversion running without error. Data usage goes from almost 10gb down to under 3gb and the transaction logs are now working.
However, when I use DBCC checkdb on the new database, I get the following (just a small sample):
Checking 524528902
Msg 2506, Level 16, State 3
Table Corrupt: The values in adjust table should be in ascending order starting from the end of the table (page#=25930 row#=10); check adjust table in this row
Msg 2524, Level 16, State 1
Table Corrupt: Row length is inconsistent between the computed row length and the recorded row length on page; check the following page and row: pageno=25930 row#=10 computed row length=551 row length on page=550
Msg 2506, Level 16, State 3
Table Corrupt: The values in adjust table should be in ascending order starting from the end of the table (page#=25941 row#=2); check adjust table in this row
Msg 2524, Level 16, State 1
Table Corrupt: Row length is inconsistent between the computed row length and the recorded row length on page; check the following page and row: pageno=25941 row#=2 computed row length=527 row length on page=526
Msg 2596, Level 16, State 1
Page pointer = 0x1b20a800, pageno = 25941, status = 0xD, objectid = 524528902, indexid = 0 has an incorrect pgfreeoff value of 1794. The offset should be 1384.
Msg 2506, Level 16, State 3
Table Corrupt: The values in adjust table should be in ascending order starting from the end of the table (page#=25967 row#=2); check adjust table in this row
Msg 2524, Level 16, State 1
Table Corrupt: Row length is inconsistent between the computed row length and the recorded row length on page; check the following page and row: pageno=25967 row#=2 computed row length=527 row length on page=526
Msg 2596, Level 16, State 1
Page pointer = 0x22ab9000, pageno = 25967, status = 0x5, objectid = 524528902, indexid = 0 has an incorrect pgfreeoff value of 1667. The offset should be 1345.
Msg 2506, Level 16, State 3
Table Corrupt: The values in adjust table should be in ascending order starting from the end of the table (page#=26146 row#=0); check adjust table in this row
Msg 2524, Level 16, State 1
Table Corrupt: Row length is inconsistent between the computed row length and the recorded row length on page; check the following page and row: pageno=26146 row#=0 computed row length=551 row length on page=550
Msg 2506, Level 16, State 3
Table Corrupt: The values in adjust table should be in ascending order starting from the end of the table (page#=26151 row#=5); check adjust table in this row
Msg 2524, Level 16, State 1
Table Corrupt: Row length is inconsistent between the computed row length and the recorded row length on page; check the following page and row: pageno=26151 row#=5 computed row length=521 row length on page=520
Msg 2506, Level 16, State 3
Table Corrupt: The values in adjust table should be in ascending order starting from the end of the table (page#=26163 row#=0); check adjust table in this row
Msg 2524, Level 16, State 1
Table Corrupt: Row length is inconsistent between the computed row length and the recorded row length on page; check the following page and row: pageno=26163 row#=0 computed row length=546 row length on page=545
Msg 2596, Level 16, State 1
Page pointer = 0x218ba800, pageno = 26163, status = 0x5, objectid = 524528902, indexid = 0 has an incorrect pgfreeoff value of 1387. The offset should be 577.
etc...
The errors occur only on 3 specific user tables, and are consistent each time I try the conversion. Same exact page numbers and offsets (page pointers are of course different). I do not get any DBCC errors on the original DB at all. I can do select's on the tables without issue, and they seem to have the correct amount of data (did row counts and sums of various columns)
Any insight into what might be causing this or what the actual error is would be welcome.
I have designed a matrix report to display activities for which I added a table with in one of the cell When I run the report, I see activities but the row alignment is not proper and also when there are no records it displays blank.
I have a database with a corrupt table and no matter what I do (dbcc's and rebuilding indexes), there are row fetch errors still occurring daily. My solution now is just to drop the table and recreate it. Is it as simple as copying the data out of the original table, dropping the original table, and recreating the original table? What about the pointers to other pages? Referential integrety? Please give me a good reference for this subject or let me know. Thank you?
I get the following errors when I ran dbcc checkdb..
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 0, index ID 0, page ID (1:13015). The PageId in the page header = (0:0). [Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 0, index ID 0, page ID (1:13039). The PageId in the page header = (0:0). [Microsoft][ODBC SQL Server Driver][SQL Server]Object ID 18099105, index ID 0: Page (1:13015) could not be processed. See other errors for details. [Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 18099105, index ID 1. Page (1:12777) is missing a reference from previous page (1:12567). Possible chain linkage problem.
and
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 562101043, index ID 2. The high key value on page (1:1779) (level 0) is not less than the low key value in the parent (0:1), slot 0 of the next page (1:13072).
What does it mean and how can i resolve them.... Database went into suspect mode , is this the likely cause..
I have a table in my database called Users:CREATE TABLE [Users] ([UserID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Users_UserID]DEFAULT (newid()),[UserName] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[password] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[EmailAddress] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_ASNOT NULL ,[ValidFrom] [datetime] NOT NULL ,[ValidTo] [datetime] NULL ,[passwordSalt] [bigint] NOT NULL ,[FullName] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[CreatorUserID] [uniqueidentifier] NOT NULL ,[DeletorUserID] [uniqueidentifier] NULL ,CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED([UserID],[]) ON [PRIMARY] ,CONSTRAINT [UX_Users_UserName] UNIQUE NONCLUSTERED([UserName]) ON [PRIMARY] ,CONSTRAINT [FK_Users_UsersCreator] FOREIGN KEY([CreatorUserID]) REFERENCES [Users] ([UserID]),CONSTRAINT [FK_Users_UsersDeletor] FOREIGN KEY([DeletorUserID]) REFERENCES [Users] ([UserID]),CONSTRAINT [CK_Users_Finished] CHECK ([ValidTo] is null and[DeletorUserID] is null or ((not([ValidTo] is null))) and((not([DeletorUserID] is null)))),CONSTRAINT [CK_Users_ValidDates] CHECK ([ValidFrom] <= [ValidTo])) ON [PRIMARY]GOIf you're looking at the Primary Key constraint above, you can probablyalready see the problem (this script was produced by Query Analyzer).If I try to get query analyzer to just script the PK, I get thefollowing error message:[SQL-DMO]The name '' is not a valid object identifier, or is not avalid format for this property or method.I've googled for this phrase, and cannot locate it. If I drop all ofthe constraints in my database, and reapply the constraints to Usersusing the following script:alter table Users add constraintDF_Users_UserID DEFAULT NEWID() FOR UserIDgoalter table Users add constraintPK_Users PRIMARY KEY(UserID) on [PRIMARY]goalter table Users add constraintUX_Users_UserName UNIQUE(UserName)goalter table Users add constraintCK_Users_ValidDates CHECK(ValidFrom <= ValidTo)goalter table Users add constraintCK_Users_Finished CHECK((ValidTo is null andDeletorUserID is null)or(not ValidTo is null andnot DeletorUserID is null))goalter table Users add constraintFK_Users_UsersCreator FOREIGN KEY(CreatorUserID)REFERENCES Users(UserID)goalter table Users add constraintFK_Users_UsersDeletor FOREIGN KEY(DeletorUserID)REFERENCES Users(UserID)goI *still* have this wierd PK. I've checked through both theINFORMATION_SCHEMA views and directly through the system tables, and sofar as they are concerned there is only a single column (UserID) actingas part of the PK.So, any ideas? It appears to be preventing us from setting upreplication. Thankfully, it also occurs in a restored backup of the DB,so I'll be able to reproduce/attempt fixes to my hearts content.Thanks,Damien
Server: Msg 8928, Level 16, State 1, Line 1 Object ID 1513928615, index ID 0: Page (3:33709) could not be processed. See other errors for details. Server: Msg 8944, Level 16, State 1, Line 1 Table error: Object ID 1513928615, index ID 0, page (3:33709), row 0. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 95 and 30. DBCC results for 'Result'. There are 41589306 rows in 660667 pages for object 'Result'. CHECKTABLE found 0 allocation errors and 2 consistency errors in table 'Result' (object ID 1513928615). repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (LASAR2.dbo.Result ).
Hi, I use SQL Server 2000 as a backend database for my Access Front end. It has been working fine for months with no problems. This morning I arrived in work to find a problem with a table called "TimeSheets". If I try to access the table through Access I get an ODBC timeout error. Likewise, if I open the table in Enterprise manager, it opens fine, but any sorts or if I try to go to the last record, it returns the following error: "[Microsoft][ODBC SQL Server Driver] Timout expired".
So I tried to query the table in SQL Query Analyzer. Everytime it freezes on record 15,936. The table holds 17,643 records.
I tried running DBCC CHECKTABLE ('Timesheets'), and get the following message:
DBCC results for 'Timesheet - Item'. There are 17643 rows in 401 pages for object 'Timesheet - Item'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I can't see any error messages, but by now I'm reaching the limit of my knowledge of SQL Server.
So, can anybody please help me with this? Any suggestions why my table has apparently become corrupted? Any suggestions how I might fix it?
Dear group,we are running a SQL-Server Database which is about 30 GB large. Thepurpose of this database is to contain periodic data from automaticdevices which insert values into some tables.Unfortunately most of these tables don't have a key (and a key can onlybe introduced when the application programmers have changed theirsoftware). Tables have this structuredeviceno timestamp datawhere we expect for every device and timestamp one row of data.In the ongoing operation it happens that the index of this large tablegets corrupted and a select from this table yields 2 rows for somedevices.In fact a select "SELECT DEVICENO, TIMESTAMP, COUNT(*) FROM TABLE GROUPBY DEVICENO, TIMESTAMP HAVING COUNT(*) > 1" returns lots of data.After rebuild of the indexes the table is "clean" again.What could cause the index corruption ?Missing key?Faulty application program ?a combination of both ?How can i prevent this from happening again, as long as there is noupdated database / application ?I'd be grateful for any useful commentRegardsUli
I am using SQL Server 2005 and trying to create a linked server on Oracle 10. I used the commands below: EXEC sp_addlinkedserver @server = 'test1', @srvproduct = 'Oracle', @provider = 'MSDAORA', @datasrc = 'testsource' exec sp_addlinkedsrvlogin @rmtsrvname = 'test1', @useself = 'false', @rmtuser='sp', @rmtpassword='sp'
When I execute select * from test1...COUNTRY I get the error. "The OLE DB provider "MSDAORA" for linked server "...." does not contain the table "COUNTRY". The table either does not exist or the current user does not have permissions on that table." The 'sp' user I am connecting is the owner of the table. What could be the problem ? Thanks a lot.
I have created a table Table with name as Varchar and id as int. Now i have started inserting the rows like, insert into Table values ('arun',20).Yes i have inserted a row in the table. Now i have got the values " arun's ", 50. insert into Table values('arun's',20) My sqlserver is giving me an error instead of inserting the row. How will you solve this problem?
I am having a table called as status ,in that table one field is there i.e. currentstatus. the rows which are having currentstatus as "ticket closed",i want to move those rows into other table called repository which is having same table structure as status table. I can do programatically. but is there any way for every 3 months system has to check and do this action means moving to repository table automatically?
I'm inserting from TempAccrual to VacationAccrual . It works nicely, however if I run this script again it will insert the same values again in VacationAccrual. How do I block that? IF there is a small change in one of the column in TempAccrual then allow insert. Here is my query
INSERT INTO vacationaccrual (empno, accrued_vacation, accrued_sick_effective_date, accrued_sick, import_date)
For reasons that are not relevant (though I explain them below *), Iwant, for all my users whatever privelige level, an SP which createsand inserts into a temporary table and then another SP which reads anddrops the same temporary table.My users are not able to create dbo tables (eg dbo.tblTest), but arepermitted to create tables under their own user (eg MyUser.tblTest). Ihave found that I can achieve my aim by using code like this . . .SET @SQL = 'CREATE TABLE ' + @MyUserName + '.' + 'tblTest(tstIDDATETIME)'EXEC (@SQL)SET @SQL = 'INSERT INTO ' + @MyUserName + '.' + 'tblTest(tstID) VALUES(GETDATE())'EXEC (@SQL)This becomes exceptionally cumbersome for the complex INSERT & SELECTcode. I'm looking for a simpler way.Simplified down, I am looking for something like this . . .CREATE PROCEDURE dbo.TestInsert ASCREATE TABLE tblTest(tstID DATETIME)INSERT INTO tblTest(tstID) VALUES(GETDATE())GOCREATE PROCEDURE dbo.TestSelect ASSELECT * FROM tblTestDROP TABLE tblTestIn the above example, if the SPs are owned by dbo (as above), CREATETABLE & DROP TABLE use MyUser.tblTest while INSERT & SELECT usedbo.tblTest.If the SPs are owned by the user (eg MyUser.TestInsert), it workscorrectly (MyUser.tblTest is used throughout) but I would have to havea pair of SPs for each user.* I have MS Access ADP front end linked to a SQL Server database. Forreports with complex datasets, it times out. Therefore it suit mypurposes to create a temporary table first and then to open the reportbased on that temporary table.
The following dbo.Tables of Northwind.mdf in my .SQLEXPRESS (SQL Server Management Studio Express) are missing: dbo.Categories dbo.CustomerCustomerDemo dbo.CustomerDemographics dbo.Customers dbo.Employees dbo.EmployeeTerritories dbo.Order Details dbo.Orders dbo.Products dbo.Regions dbo.Shippers dbo.Suppliers dbo.Territories.
But, I have these dbo.Tables in a different Database "xyzDatabase". How can I copy each of these dbo.Tables to the another blank dbo.Table of Northwind Database?
I right clicked on the dbo.Categories and I saw the following thing: dbo.Categories New Table... Modify Open Table Script Table as |> CREATYE To |> DROP To |> SELECT To |> INSERT To |> New Query Editor Window File.... Clipboard UPDATE To |> DELETE to |> From the above observation,I think it is possible to copy the dbo.Table from the one Database to the Northwind Database that needs to be repaired. Please help and advise me how to do this task or tell me where I can find the Microsoft document that gives the details of this X-copy thing.
Thanks in advance, Scott Chang
P. S. I am using VB 2005 Express to create a project to learn "Calling Stored Procedures with ADO.NET" (see Paul Kimmel's article in http://www.developer.com/db/article.php/3438221) that needs the dbo.Tables of Northwind Database and my Northwind Database has been screwed up for quite a while and needs a big repair.
Does anyone know how or where to adjust Ram Memory usage for SQL 2000. I've just added Changed the 512 MB Ram that came with the Server and Exchanged it with 4 GIG Ram . Is it a good Idea to allow only 2GiG for SQL . I 've heard that SQL will take/use all Ram that you install if you let. If this is true Can anyone advise on how/where to make adjustments. Thank You...
I'm having trouble getting the namespaces correct in the XML that is generated by SQL. The XML consists of two parts: a header part with some context information and the payload part with the actual data. With my current SQL script, the namespace is also copied to the payload part.
Here are some DDL and DML scripts to generate two test tables and some test data:
Code: CREATE TABLE dbo.context( idintNOT NULLconstraint pk_dbo_context primary key, namenvarchar(100)NOT NULL ) INSERT INTO dbo.context(id, name) VALUES (1, 'Here comes some great context information.')
[Code] ....
This is my current FOR XML script that generates the XML:
Code: ;WITH XMLNAMESPACES (DEFAULT 'http://services.registersubscription-02_00.a.cool.url.com') SELECT CEXT.name AS [Context/Name], (SELECT CONT.id AS [Content/Reference],
[Code] ....
This generates this XML (notice that the namespace is repetated in the <Questions> element):
Code: <RegisterSubscription xmlns="http://services.registersubscription-02_00.a.cool.url.com"> <Request> <Context> <Name>Here comes some great context information.</Name>
[Code] ....
The XML should be like this:
Code: <RegisterSubscription xmlns="http://services.registersubscription-02_00.a.cool.url.com"> <Request xmlns=""> <Context> <Name>Here comes some great context information.</Name>
[Code] ....
The generated XML does not pass the XSD validation.
I have a table with an identity column..How will the identity gaps be adjusted if i delete few records in the table..ie..the sequence should automatically adjusted..Is there any way for this ?
ID Name City 1 abc xyz 2 mexm mcel 3 olekc kcome
Suppose i delete the record where ID=2..still the sequence should be auto adjusted..ie.the record of ID=3 should become ID=2 automatically..there shouldn't be any gaps.
Hi everyone,Here is the problem I am facing with. I have a form which has multiple fields including Price (read only), Discount(read/write), TotalSellPrice(read/write), Quantity(read/write) ... What I need to do is I need to adjust TotalSellPrice value if there was a new Discount value entered and vise versa. If both values have been changed I should use Discount value entered and calculate the TotalSellPrice. I am having hard time figuring the query out. Any thoughts or ideas in what direction should I go.Thanks for your help!
--Table 1 "Employee" CREATE TABLE [MyCompany].[Employee]( [EmployeeGID] [int] IDENTITY(1,1) NOT NULL, [BranchFID] [int] NOT NULL, [FirstName] [varchar](50) NOT NULL, [MiddleName] [varchar](50) NOT NULL, [LastName] [varchar](50) NOT NULL, CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ( [EmployeeGID] ) GO ALTER TABLE [MyCompany].[Employee] WITH CHECK ADD CONSTRAINT [FK_Employee_BranchFID] FOREIGN KEY([BranchFID]) REFERENCES [myCompany].[Branch] ([BranchGID]) GO ALTER TABLE [MyCompany].[Employee] CHECK CONSTRAINT [FK_Employee_BranchFID]
-- Table 2 "Branch" CREATE TABLE [Mycompany].[Branch]( [BranchGID] [int] IDENTITY(1,1) NOT NULL, [BranchName] [varchar](50) NOT NULL, [City] [varchar](50) NOT NULL, [ManagerFID] [int] NOT NULL, CONSTRAINT [PK_Branch] PRIMARY KEY CLUSTERED ( [BranchGID] ) GO ALTER TABLE [MyCompany].[Branch] WITH CHECK ADD CONSTRAINT [FK_Branch_ManagerFID] FOREIGN KEY([ManagerFID]) REFERENCES [MyCompany].[Employee] ([EmployeeGID]) GO ALTER TABLE [MyCompany].[Branch] CHECK CONSTRAINT [FK_Branch_ManagerFID]
--Foreign IDs = FID --generated IDs = GID Then I try a simple single row DELETE
DELETE FROM MyCompany.Employee WHERE EmployeeGID= 39
Well this might look like a very basic error: I get this Error after trying to delete something from Table €œEmployee€?
The DELETE statement conflicted with the REFERENCE constraint "FK_Branch_ManagerFID". The conflict occurred in database "MyDatabase", table "myCompany.Branch", column 'ManagerFID'.
Yes what I€™ve been doing is to deactivate the foreign key constraint, in both tables when performing these kinds of operations, same thing if I try to delete a €œBranch€? entry, basically each entry in €œbranch€? and €œEmployee€? is child of each other which makes things more complicated.
My question is, is there a simple way to overcome this obstacle without having to deactivate the foreign key constraints every time or a good way to prevent this from happening in the first place? Is this when I have to use €œON DELETE CASCADE€? or something?
One server, a part of a 2 node always on cluster, had problems, I had to restart.
I'm getting this error for example now:
Error: 49910, Severity: 10, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped. + SQL Server was unable to run a new system task, either because there is insufficient memory or the number of configured sessions exceeds the maximum allowed in the server. Verify that the server has adequate memory. Use sp_configure with option 'user connections' to check the maximum number of user connections allowed. Use sys.dm_exec_sessions to check the current number of sessions, including user processes.
Now, on the instance I have activated maximum servere memory, my question; Is it possible to adjust this setting without starting the service?
Banti writes "IF i create temporary table by using #table and ##table then what is the difference. i found no difference. pls reply. first: create table ##temp ( name varchar(25), roll int ) insert into ##temp values('banti',1) select * from ##temp second: create table #temp ( name varchar(25), roll int ) insert into #temp values('banti',1) select * from #temp
both works fine , then what is the difference waiting for ur reply Banti"
My default filter settings are: Name Schema Created How do I add an additional filter such as 'Modified'? I can get into the Filter Dialog box by clicking on the Funnel, but I can't figure out how to add another filter to the list.
I developed a SSRS report, the problem is i dont have data in DEV server. So i dont know how to adjust the column lengths in ssrs report. is there any property so that the column length can be adjusted dynamically based on the data length whenever data is available in production.
I would like to return the nearest date of Table B in my table like for
ID W001 in table B should return ID A002 CreatedDatetime: 2014-06-03 20:05:48.000 ID W002 in table B should return ID A004 CreatedDatetime: 2014-06-04 01:05:48.000
I have a column defined as smalldatetime. Default length (4), and "allow NULLS" is checked.In the Enterprise Manager UI, when i enter data into that table row, if i just tab past that column, all is well, and the value is represented in the UI as <NULL>.The problem comes once i ever enter a date into that column. Say i have entered a date (all is well), and now i want to remove that entry and go back to NULL (after the date value has been committed, different entry session, say).How is that done?It seems to me, once a date has ever been entered into that column, now, if i try to remove it, i get the error "The value you entered is not consistant with the data type or length of the column, or over grid buffer limit". I have tried deleting the value, entering spaces, entering the string NULL or the string <NULL>; maybe some other tries as well, but none works, i always get that error message and am not allowed to proceed past that cell until i restore a date value to it. I want to get back to <NULL>.Anybody know?Thank you.Tom
I am stuck on finding a solution to transpose source data from a system via a metadata look-up table into a destination table. I need a method to transpose/pivot the source data into columns (which are by various data-types). The datatypes for each column are listed in a metadata table.
Source Data Table:
Table Name: Source
SrcID AGE City Date 01 32 London 01-01-2013 02 35 Lagos 02-01-2013 03 36 NY 03-01-2013
Metadata Table:
Table Name:Metadata
MetaID Column_Name Column_type 11 AGE col_integer 22 City col_character 33 Date col_date
Destination table:
The source data to be loaded into the destination table(as shown below):