SQL Server 2000 SP3 Transaction Log Errors

May 31, 2008

Hi!

We have a Microsoft SQL Server 2000 SP3 running database for Microsoft
Navision 3.7

From time we encounter problems, especially when running heavy query
procedures from Navision, with the transaction log. It's actually setup as
folows:

File properties:
File growth By percent (10)
Restrict file growth (MB) 10000

OPTIONS:
Recovery model: simple
Settings: Autoupdate statistics, Auto create statistics, Autoshrink

We get the following errors (once every 2-3 months so far):

The log file for database 'ME_Prod' is full. Back up the transaction log for
the database to free up some log space..


in between numerous abovementioned messages I have the following:
Configuration option 'show advanced options' changed from 1 to 1. Run the
RECONFIGURE statement to install..

Could not write a CHECKPOINT record in database ID 9 because the log is out
of space.

Automatic checkpointing is disabled in database 'ME_Prod' because the log is
out of space. It will continue when the database owner successfully
checkpoints the database. Free up some space or extend the database and then
run the CHECKPOINT statement.

View 10 Replies


ADVERTISEMENT

SQL 2000 Server Backup Errors

Aug 20, 2004

I am experiencing the following problem when running a backup. This process was working really fine for 3 months.

BACKUP DATABASE [DBLIVE] TO DISK = N'C:BACKUPSdblive' WITH INIT , NOUNLOAD , NAME = N'DBLIVE backup C', NOSKIP , STATS = 10, NOFORMAT

Database size 10G
Recovery Mode : Simple
Daily Backup

Error message:

Executed as user: ACC33Administrator. 10 percent backed up. [SQLSTATE 01000] (Message 3211) 20 percent backed up. [SQLSTATE 01000] (Message 3211) 30 percent backed up. [SQLSTATE 01000] (Message 3211) 40 percent backed up. [SQLSTATE 01000] (Message 3211) 50 percent backed up. [SQLSTATE 01000] (Message 3211) 60 percent backed up. [SQLSTATE 01000] (Message 3211) Nonrecoverable I/O error occurred on file 'D:sqldbDBLIVE_Data.MDF'. [SQLSTATE 42000] (Error 3271) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

Thank You.

View 9 Replies View Related

DTC Errors 7395 And 1206 Between SQL Server 2005 And SQL Server 2000 Sp4 - Part1

Feb 6, 2008



Our software vendors upgraded their software and our environment - and applications do not interface as they ought. The vendors do not appear to have any further ideas (to those listed below) and at this stage neither do I. It is now a week since the *upgrade* went live. Looking for new suggestions....


Full story (epic) below. Object names have been changed to protect the *innocent*.





Our organisation had 2 applications:-

appBank
appHouse

both running on the respective platforms:

Server name svrCAT:
Windows Server 2000 sp 4 / SQL Server 2000

Server name svrDOG:
Windows Server 2000 sp 4 / SQL Server 2000



Transactions were "posted" from svrDOG to svrCAT via a linked SQL Server, using login dbL1nkLogin.

dbL1nkLogin was made a user of (with relevant table permissions) of the respective databases:

dbForBank
dbForHouse



*** At this point all was working ***


Upgrading appBank to appBigBank required SQL Server 2005 (and more diskspace).


A new server was purchased:

Windows Server 2003 R2 sp 2 / SQL Server 2005 (Server name svrHORSE)



A linked server was created from the SQL instance on svrHORSE to svrDOG using login name dbL1nkLogin.
dbL1nkLogin was made a user (with relevant table permissions) on database:

dbForBigBank


Attempts to "post" transactions were "posted" from svrDOG to svrHORSE via the linked SQL Server fail with
the appBank application generating the following errors:

On first attempt:

************* FILE ERROR *************
Error Code...... 3902-
Error Message...The COMMIT TRANSACTION request has no co
.rresponding BEGIN TRANSACTION.
Table Name......** NOT APPLICABLE **
Description.....** NOT APPLICABLE **
Function....... COMMIT
sys991: Program already in use
COBOL error at 000444 in sys500
Called from 00076A in sys990ssv
Called from 000457 in sys991
Called from 01177D in gls489
Called from 000DFA in gls396
Called from 003961 in gls394
Called from 000258 in glp900
Called from 003DC1 in syp250

On subsequent attempts:

************* FILE ERROR *************
Error Code...... 1206-
Error Message...The Microsoft Distributed Transaction Co
.ordinator (MS DTC) has cancell
Table Name......tblHouseFinancials
Description.....DYNAMIC SQL CALL INTERLUDE
Function....... UPDATE
*FAILED* Processing Job 'WIN/'



* Parameters and controls within the appBank application were verified.

* Password on dbL1nkLogin was reset in the SQL instances on svrHORSE and svrDOG

* Compared the link server settings on svrHORSE to svrCAT:

Security tab: Be made using security context - Remote Logging: dbL1nkLogin / password

Server Options: Collation Compatible checked (on svrHORSE only)
Data Access checked
RPC checked
RPC Out checked


* Made dbL1nkLogin dbo on databases dbForBigBank and dbForHouse.
* Changed default database from master to dbForBigBank on svrHORSE
* Rebooted svrHORSE and svrDOG (multiple times)
* Verified that svrHORSE could ping svrDOG, and vice versa
* Using DTCping.exe, Verified that the SQL instance on svrHORSE could ping svrDOG, and vice versa
* On svrHORSE, Administrative Tools -> Component Services -> Computers has the following settings for "My Computer" Network DTC Access:


Network DTC Access - checked
Allow Remote Clients - checked
Allow Remote Administration - checked
Allow Inbound - checked
Allow Outbound - checked
No Authentication required - checked
Enable XA Transactions - checked
DTC Logon Account - NT AuthorityNetworkService

These settings were already set. My understanding of KB329332 suggest they are correct. As I could not establish the configuration date of these settings rebooted the server to ensure they'd taken effect.



* Loaded SQL Server 2000 sp4 on svrDOG (Rebooted afterwards in case services not stopped and started correctly)
srvDOG now has MDAC 2.8 sp1 (2.81.1117.6) while srvHORSE has a later version (2.82.3959)- I assume sp2. While they are not the same version, MDAC 2.8 sp2 KB 231943 advises that 2.8 sp2 is not available via the web - so I think it's the best I can do).


* Stopped and manually started MSDTC and SQL Server (via Administrative Tools - Services) in case MSDTC was not being started first.



* KB873160 suggests opening port 135 and adding msdtc.exe as an exception in the Windows Firewall. At present there is no firewall on svrHORSE - which I would assume negates this necessity (at present). In Administration Tools - Services there was a service for the firewall running. Stopping this service has no effect on this issue.



* ( The svrHORSE machine was not created from an ghosted image ).


** None of these things have resolved the issue. **



.... to be continued...

KD

View 1 Replies View Related

Errors After Installing Server 2000 DTS Designer Components

Jan 30, 2007

Hello,

After Having installed the SQL Server 2000 DTS Designer Components necessary
to edit legacy DTS packages on 2005 server, I keep getting the following message when trying to open SQL Server 200 Enterprise Manager:

"The procedure entry point
?ProcessExecute@@YAXPAUHWND_@@PBG1@Z could not be located in teh dynamic link library SEMSFC.dll."


Is there a way to fix so that I can open Enterprise manager?

Thanks!



View 9 Replies View Related

Getting ODBC Connection Errors With SQL Server 2000 On Windows XP

Apr 21, 2008

Hi all-

I setup a new ODBC connection to MS SQL Server 2000 on Windows XP and keep getting the following errors when I try to
either register a new SQL Server 2000 group within Enterprise Manager or test the new ODBC connection:

Current configuration

- MS SQL Server 2000
-Windows XP
-ODBC 3.85.1117


Errors:

I. Within SQL Server 2000 Enterprise Manager

Error: SQL Server connection open

II. When I test the ODBC connection:

Microsoft SQL Server ODBC Driver Version 03.85.1117

Running connectivity tests...

Attempting connection
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]Specified SQL server not found.

TESTS FAILED!

Now here is what has me totally confused:

I can use the command line ISQL to access the database on the server and IP is all correct and port is available.

Any tips how to get this ODBC working?

Thanks
Ben Prusinski

View 1 Replies View Related

Distributed Transaction Errors

Jan 13, 2014

I was getting this error message from our Cold Fusion application front end when it was trying to execute one of our stored procedures.The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "SERVERNAME" was unable to begin a distributed transaction.

I was a bit puzzled by this because we don't use distributed transactions (at least I don't specifically code them). I did some research online and I found out how to modify the DTC component on the server to have the proper configurations.Then, when trying again we got this error message:

Unable to start a nested transaction for OLE DB provider "SQLNCLI10" for linked server "SERVERNAME". A nested transaction was required because the XACT_ABORT option was set to OFF.

So, I was able to resolve that as well by changing that option in the stored procedure...Now, there are 3 stored procedures - One does inserts; one does updates; and one does deletes.The actions are being done to a view in a database on another server. The view definition uses a linked server.

The error was/is only happening on the INSERT stored procedure. So, I'm a little baffled as to why it only bombs on the insert stored procedure and not the others. They are all coded in the same fashion..Do distributed transactions work differently if its an insert vs. update or delete? Why is it all of the sudden treating these as distributed transactions when they aren't coded as such?

The code is very simple and looks just like this:

INSERT vw_Name
SELECT bla, bla2, bla3
FROM local table
WHERE bla bla

And again vw_Name would be a table on another server that we have via Linked Server. It is also a SQL Server (but its SQL 2000).

View 6 Replies View Related

SQL Server 2000 And Transaction Log Error

Jul 20, 2005

Hi All:I am getting an error when trying to open a recordset in SQL Server 2000.The error states that the transaction log is full. Is there any way I canclear out or empty the transaction log, or get rid of it alltogether as itis not really needed?Any help would be appreciated.Thanks and regards,Ryan

View 1 Replies View Related

Transaction Log Shipping Script Errors

Dec 10, 2007



Greetings:
When I script out my log shipping configuration from the GUI and subsequently drop the log shipping and try to recreate it with the created script, the backup and restore functions do not seem to be working; please see script below. Is there an additional step (or steps) that the SSMS GUI does not output when it creates the script for log shipping? I noticed in the GUI after I run the script that the destination folder for copied files is blank as well.

Example error from backup/restore job - Error: The path is not of a legal form.(mscorlib)


-- Execute the following statements at the Primary to configure Log Shipping

-- for the database [rdevsql2].[SymbolLookUp],

-- The script needs to be run at the Primary in the context of the [msdb] database.

-------------------------------------------------------------------------------------

-- Adding the Log Shipping configuration

-- ****** Begin: Script to be run at Primary: [rdevsql2] ******



DECLARE @LS_BackupJobId AS uniqueidentifier

DECLARE @LS_PrimaryId AS uniqueidentifier

DECLARE @SP_Add_RetCode As int



EXEC @SP_Add_RetCode = master.dbo.sp_add_log_shipping_primary_database

@database = N'SymbolLookUp'

,@backup_directory = N'm:ackups'

,@backup_share = N'\rdevsql2m$ackups'

,@backup_job_name = N'LSBackup_SymbolLookUp'

,@backup_retention_period = 60

,@monitor_server = N'RDEVSQL1'

,@monitor_server_security_mode = 1

,@backup_threshold = 60

,@threshold_alert_enabled = 1

,@history_retention_period = 60

,@backup_job_id = @LS_BackupJobId OUTPUT

,@primary_id = @LS_PrimaryId OUTPUT

,@overwrite = 1

,@ignoreremotemonitor = 1



IF (@@ERROR = 0 AND @SP_Add_RetCode = 0)

BEGIN

DECLARE @LS_BackUpScheduleUID As uniqueidentifier

DECLARE @LS_BackUpScheduleID AS int



EXEC msdb.dbo.sp_add_schedule

@schedule_name =N'LSBackupSchedule_rdevsql21'

,@enabled = 1

,@freq_type = 4

,@freq_interval = 1

,@freq_subday_type = 4

,@freq_subday_interval = 1

,@freq_recurrence_factor = 0

,@active_start_date = 20071207

,@active_end_date = 99991231

,@active_start_time = 0

,@active_end_time = 235900

,@schedule_uid = @LS_BackUpScheduleUID OUTPUT

,@schedule_id = @LS_BackUpScheduleID OUTPUT

EXEC msdb.dbo.sp_attach_schedule

@job_id = @LS_BackupJobId

,@schedule_id = @LS_BackUpScheduleID

EXEC msdb.dbo.sp_update_job

@job_id = @LS_BackupJobId

,@enabled = 1



END



EXEC master.dbo.sp_add_log_shipping_primary_secondary

@primary_database = N'SymbolLookUp'

,@secondary_server = N'RDEVSQL1'

,@secondary_database = N'SymbolLookUp'

,@overwrite = 1

-- ****** End: Script to be run at Primary: [rdevsql2] ******



-- ****** Begin: Script to be run at Monitor: [RDEVSQL1] ******



EXEC rdevsql1.msdb.dbo.sp_processlogshippingmonitorprimary

@mode = 1

,@primary_id = N'4d80db8c-e090-4dc0-8af6-d5f5802c4207'

,@primary_server = N'rdevsql2'

,@monitor_server = N'RDEVSQL1'

,@monitor_server_security_mode = 1

,@primary_database = N'SymbolLookUp'

,@backup_threshold = 60

,@threshold_alert = 14420

,@threshold_alert_enabled = 1

,@history_retention_period = 60

-- ****** End: Script to be run at Monitor: [RDEVSQL1] ******



-- Execute the following statements at the Secondary to configure Log Shipping

-- for the database [RDEVSQL1].[SymbolLookUp],

-- the script needs to be run at the Secondary in the context of the [msdb] database.

-------------------------------------------------------------------------------------

-- Adding the Log Shipping configuration

-- ****** Begin: Script to be run at Secondary: [RDEVSQL1] ******



DECLARE @LS_Secondary__CopyJobId AS uniqueidentifier

DECLARE @LS_Secondary__RestoreJobId AS uniqueidentifier

DECLARE @LS_Secondary__SecondaryId AS uniqueidentifier

DECLARE @LS_Add_RetCode As int



EXEC @LS_Add_RetCode = rdevsql1.master.dbo.sp_add_log_shipping_secondary_primary

@primary_server = N'rdevsql2'

,@primary_database = N'SymbolLookUp'

,@backup_source_directory = N'\rdevsql2m$ackups'

,@backup_destination_directory = N''

,@copy_job_name = N''

,@restore_job_name = N''

,@file_retention_period = 4320

,@monitor_server = N'RDEVSQL1'

,@monitor_server_security_mode = 1

,@overwrite = 1

,@copy_job_id = @LS_Secondary__CopyJobId OUTPUT

,@restore_job_id = @LS_Secondary__RestoreJobId OUTPUT

,@secondary_id = @LS_Secondary__SecondaryId OUTPUT

IF (@@ERROR = 0 AND @LS_Add_RetCode = 0)

BEGIN

DECLARE @LS_SecondaryCopyJobScheduleUID As uniqueidentifier

DECLARE @LS_SecondaryCopyJobScheduleID AS int



EXEC rdevsql1.msdb.dbo.sp_add_schedule

@schedule_name =N'DefaultCopyJobSchedule'

,@enabled = 1

,@freq_type = 4

,@freq_interval = 1

,@freq_subday_type = 4

,@freq_subday_interval = 15

,@freq_recurrence_factor = 0

,@active_start_date = 20071207

,@active_end_date = 99991231

,@active_start_time = 0

,@active_end_time = 235900

,@schedule_uid = @LS_SecondaryCopyJobScheduleUID OUTPUT

,@schedule_id = @LS_SecondaryCopyJobScheduleID OUTPUT

EXEC rdevsql1.msdb.dbo.sp_attach_schedule

@job_id = @LS_Secondary__CopyJobId

,@schedule_id = @LS_SecondaryCopyJobScheduleID

DECLARE @LS_SecondaryRestoreJobScheduleUID As uniqueidentifier

DECLARE @LS_SecondaryRestoreJobScheduleID AS int



EXEC rdevsql1.msdb.dbo.sp_add_schedule

@schedule_name =N'DefaultRestoreJobSchedule'

,@enabled = 1

,@freq_type = 4

,@freq_interval = 1

,@freq_subday_type = 4

,@freq_subday_interval = 15

,@freq_recurrence_factor = 0

,@active_start_date = 20071207

,@active_end_date = 99991231

,@active_start_time = 0

,@active_end_time = 235900

,@schedule_uid = @LS_SecondaryRestoreJobScheduleUID OUTPUT

,@schedule_id = @LS_SecondaryRestoreJobScheduleID OUTPUT

EXEC rdevsql1.msdb.dbo.sp_attach_schedule

@job_id = @LS_Secondary__RestoreJobId

,@schedule_id = @LS_SecondaryRestoreJobScheduleID



END



DECLARE @LS_Add_RetCode2 As int



IF (@@ERROR = 0 AND @LS_Add_RetCode = 0)

BEGIN

EXEC @LS_Add_RetCode2 = rdevsql1.master.dbo.sp_add_log_shipping_secondary_database

@secondary_database = N'SymbolLookUp'

,@primary_server = N'rdevsql2'

,@primary_database = N'SymbolLookUp'

,@restore_delay = 0

,@restore_mode = 0

,@disconnect_users = 0

,@restore_threshold = 45

,@threshold_alert_enabled = 1

,@history_retention_period = 60

,@overwrite = 1

END



IF (@@error = 0 AND @LS_Add_RetCode = 0)

BEGIN

EXEC rdevsql1.msdb.dbo.sp_update_job

@job_id = @LS_Secondary__CopyJobId

,@enabled = 1

EXEC rdevsql1.msdb.dbo.sp_update_job

@job_id = @LS_Secondary__RestoreJobId

,@enabled = 1

END



-- ****** End: Script to be run at Secondary: [RDEVSQL1] ******


help is much appreciated,
Derek


View 1 Replies View Related

Controlling A Transaction By User In SQL Server 2000

Jun 14, 2007

Hey Folks!
I have a typical requirement by my client. On submitting a Update (Bulk) button a huge database operation starts. A huge bulk update operation need to be performed. This would take 2-3 minutes some times. Client wants a cancel button in this case where he can be given a way to cancel the database Transaction.
 Please let me know in case if there is a way out.
Thanks, in advance.
Regards,
Uday.D

View 2 Replies View Related

SQL Server 2000 And Transaction Log Error - More Info

Jul 20, 2005

Hi All:I am getting an error when trying to open a recordset in SQL Server 2000.The error states that the transaction log is full. Is there any way I canclear out or empty the transaction log, or get rid of it alltogether as itis not really needed?Any help would be appreciated.Thanks and regards,RyanThe error msg is: tempdb transaction log is full. B/U transaction log tofree up space..."

View 2 Replies View Related

SQL Server 2000 - Table Transaction Date

Aug 9, 2007

(I may be in the wrong forum.)
How do I obtain/find the properties of a table using SQL Query Analyer (SQL Server 2000)? Specifically, I would like to run a query to find the most recent date of any transaction on a table. I have a script that I use for SQL Server 2005 but it doesn't work in 2000. I don't know 2000 but I'm guessing that the syntax is different?

Here's the 2005 SQL Server script (stolen from 2005 Books Online BTW):

CREATE TABLE ddl_log (PostTime datetime, DB_User nvarchar(100), Event nvarchar(100), TSQL nvarchar(2000));

GO

CREATE TRIGGER ddl_log

ON DATABASE

FOR DDL_DATABASE_LEVEL_EVENTS

AS

DECLARE @data XML

SET @data = EVENTDATA()

INSERT ddl_log

(PostTime, DB_User, Event, TSQL)

VALUES

(GETDATE(),

CONVERT(nvarchar(100), CURRENT_USER),

@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),

@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ;

GO


Here's the error:

Msg 156, Level 15, State 1, Procedure ddl_log, Line 2

Incorrect syntax near the keyword 'DATABASE'.


Remember, I want to do the same thing in SQL Server 2000.

Thanks in advance for any assistance you can provide.

View 1 Replies View Related

Implicit Transaction Mode In SQL Server 2000

May 17, 2007

Hi all:



I know i can use the sentence SET IMPLICIT_TRANSACTIONS ON in a Stored Procedure to force SQL Server to set the connection into implicit transaction mode.



Have i a sentence or configuration to force all SQL Server connections to implicit transaction mode?



Thanks in advance.

View 2 Replies View Related

Newbie - Sql Server 2000 Transaction Log Size Limit

Jan 12, 2004

Has anybody encountered a physical size limit for a sql server 2000 transaction log running on win2k?

Transaction log reached ~6Gb before rolling back the delete stating transaction log was full. There was 42Gb free on the server and the log was set to unlimited growth.

View 3 Replies View Related

Control Transaction In Sybase Database Using Sql-server 2000

Apr 20, 2004

Can anyone help me with this scenario!!!!
I have a sybase database and a sqlserver 2000 database.
I want to insert data into sybase database table thru sql-server 2000 using distributed queries
When i execute the following the transaction


Create procedure myCurrentDataBaseProcedure
as
begin

begin tran
insert into mytable values(1)
if @@error <>0
begin
rollback transaction
return
end
insert into sybasedatabaseserver.databasename.dbo.tablename values(1)
if @@error <>0
begin
rollback transaction
return
end
commit transaction
end

The procedure is created in sql server database
trying to execute this procedure..shows error
The first part of the procedure is executed.

But the error is here
insert into sybasedatabaseserver.databasename.dbo.tablename values(1)
The data is succesfully inserted in the local database
I am unable to insert data into the remote database
Can anyone suggest me wht shd i do in this scenario
Are there any drivers to be loaded to commit this transactions

Pl.Help

View 5 Replies View Related

Transaction Replication && Data Archiving On SQL Server 2000

Jul 23, 2005

Hi techiesI have set up a Transaction replication from My Primary Server toSecondary Server on Orders table.Thousand of records gets inserted on Orders every hour which getreplicated on the secondary server. it works finereporting apps uses Secondory server's Orders table data for generatingreports .The Problem :Let say if i want to Remove older records from Orders table in theprimary serverwith out reflecting this change on the secondary server.is there a way to PREVENT this operation /transaction to be propogatedto the secondary server.Note : i am moving the records to another table (orders_Archive ) anddeleteing the rows from orders table . Also I need all the rows to bepresent on the secondary server table.Please advice ASAPRegards,Raj

View 4 Replies View Related

SQL Server 2000 - Run Transaction Log Backup Using Windows CMD File

Mar 19, 2007

How I can run a SQL Server Transaction Log Backup using a Windows CMD File.  It's that posible?  Anyone know the command or how do this?

We already have a application monitoring our SQL databases.  Everytime the transaction log get full, it sends us an alert.  Would be better if our application repair the problem itself, executing a command (Windows CMD File).

I'm not a expert using SQL databases or MS DOS Prompt commands.

Any help will be appreciate.

View 1 Replies View Related

How To Skip Snapshot In Transaction Replication (from SQL Server 2005 To 2000)

Jun 13, 2006

We have two SQL Server 2005 production DB at remote sites. Due to network bandwidth issue, we need to replicate these DBs (publishers and distributers) to central corporate SQL 2000 DB (subscriber for backup and possible reporting (and in rare case as a failover server).

We would start out with backup from SQL 2000 db restored on remote SQL 2005 DBs. When we have DB issue on remote 2005 DB, we want to restore it from central corp. 2000 DB backup. Since two DBs are replicating to central DB, we DO NOT want combined db back up data on restored remote 2005 db. We can restore the db and delete unwanted data before we turn on replication from this restored server. So, this is not a problem.

The real problem is how to avoid snapshot replication (during initialization) when we create a transaction replication on this restored server to avoid over writing data on the central subcriber sql 2000 DB???

HELP!!



View 5 Replies View Related

Transaction-Log Reader Subsystem Errors On: The Process Could Not Execute 'sp_replcmds'

Apr 10, 2006


Hello,
I currently have a Transactional Log reader agent failing with the below error:
The process could not execute 'sp_replcmds'
Error: 14151, Severity: 18, State: 1
SQL Server Assertion: File: <logscan.cpp>, line=2223
Failed Assertion = 'm_noOfScAlloc == 0'.
Stack Signature for the dump is 0x24642FE5
Error: 3624, Severity: 20, State: 1.
SQL Server Assertion: File: <logscan.cpp>, line=1985
Failed Assertion = 'startLSN >= m_curLSN'.
Stack Signature for the dump is 0xD7150BD4
Now, I understand that SP4 is supposed to fix a similar issue. SP4 has been installed and the errors keep happening. I do notice that the hot fix mentions different line numbers than the above errors. Does anyone know if this is a new bug? If not can someone explain the fixes to me, thanks,

Tech Drone.

View 3 Replies View Related

SQL Server 2008 :: How To Make Sproc Return Errors For Underlying Table Errors

Jul 1, 2015

I recently updated the datatype of a sproc parameter from bit to tinyint. When I executed the sproc with the updated parameters the sproc appeared to succeed and returned "1 row(s) affected" in the console. However, the update triggered by the sproc did not actually work.

The table column was a bit which only allows 0 or 1 and the sproc was passing a value of 2 so the table was rejecting this value. However, the sproc did not return an error and appeared to return success. So is there a way to configure the database or sproc to return an error message when this type of error occurs?

View 1 Replies View Related

Plz Help Me Consistency Errors In MSSQL 2000

Feb 28, 2007

I’ve got a big problem with my Database,
Whenever I runs my query I’ve got error on the half way.
I’m using MSSQL 2000.
So I run DBCC CHECKDB and the follow errors result shown.
I don’t know how to solve that problem.

Plz help me plz help me
If that database crush I’m gonna be fu*ked up by my boss.
:...( plz


DBCC results for 'Table_1'.
Server: Msg 8928, Level 16, State 2, Line 1
Object ID 795149878, index ID 0: Page (1:352679) could not be processed. See other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 795149878, index ID 0, page (1:352679), row 44. Test (!(hdr->r_tagA & (VERSION_MASK | RECTAG_RESV_A | RECTAG_RESV_B))) failed. Values are 157 and 193.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 795149878, index ID 0, page (1:352679), row 44. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 18754 and 3160.

There are 0 rows in 1 pages for object 'Total_Profit_Loss'.
Server: Msg 2511, Level 16, State 2, Line 1
Table error: Object ID 1211151360, Index ID 0. Keys out of order on page (1:161199), slots 25 and 26.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1211151360, Index ID 0. Keys out of order on page (1:173791), slots 4 and 5.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1211151360, index ID 0: Page (1:206443) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 1211151360, index ID 0, page (1:206443). Test (m_freeCnt == freeCnt) failed. Values are 660 and 692.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1211151360, Index ID 0. Keys out of order on page (1:217603), slots 18 and 19.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1211151360, Index ID 0. Keys out of order on page (1:266945), slots 26 and 27.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1211151360, Index ID 0. Keys out of order on page (1:268051), slots 28 and 29.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1211151360, index ID 0: Page (1:277634) could not be processed. See other errors for details.

DBCC results for 'Pin'.
Server: Msg 8939, Level 16, State 106, Line 1
Table error: Object ID 1211151360, index ID 0, page (1:277634). Test (m_freeCnt == freeCnt) failed. Values are 2168 and 2164.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1211151360, index ID 0: Page (1:299697) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 1211151360, index ID 0, page (1:299697). Test (m_freeCnt == freeCnt) failed. Values are 3435 and 3431.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1211151360, index ID 0: Page (1:302290) could not be processed. See other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 1211151360, index ID 0, page (1:302290), row 12. Test (((UNALIGNED DataRecHdr*) m_pRec)->r_tagB == 0) failed. Values are 4 and 0.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1211151360, Index ID 0. Keys out of order on page (1:900223), slots 2 and 3.
There are 5940869 rows in 210312 pages for object 'Pin'.
CHECKDB found 0 allocation errors and 14 consistency errors in table 'Pin' (object ID 1211151360).

CHECKDB found 0 allocation errors and 23 consistency errors in database 'MY_SQL_DB'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (MY_SQL_DB ).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

View 1 Replies View Related

URGENT: Sp_repldropcolumn Errors (SQL-2000)

Mar 27, 2006

Environment:


- Publisher: SQL 2000 (SP4)


- Distributor/Subscriber: SQL 2000 (SP4)


- Replication: Transactional/Continual updates





Issue: Calls to sp_repldropcolumn are generating the following error:





Server: Msg 4932, Level 16, State 1, Line 1


ALTER TABLE DROP COLUMN failed because '_column_' is currently replicated.





The column in question is a text-blob, so I am unsure if the data type is at


fault.





I'd prefer to use sp_repldropcolumn versus drop subscription->drop


article->drop column->add article->add subscription method.





Any help appreciated.

View 1 Replies View Related

Numeric Comparison Errors On SQL 2000

Feb 4, 2008



I have several SQL 2000 servers that are running SQL 8.002162. Most are Windows 2003. Because of a note I saw in a non MS forum, I was testing these servers for the numeric comparison issue in MS article 899976, "FIX: A query that involves data that is the numeric data type may return incorrect results in SQL Server 2000 SP3 and in earlier SQL Server 2000 service packs". All the servers act as expected (fixed) since they are running post SP4 and patches. But, one server fails the comparison test scripts in the KB. All these servers are running the same version of SQL. The server that still fails is windows 2003 SP1.

Why does one server reporting to be version 8.002162 fail a test that should be patched and does it mean that although the server is reporting it is version 8.00.2162 it has failed some updates and might be upatched for other issues?

View 1 Replies View Related

Ignore Errors In SqlServer 2000 (try/catch)

Jul 7, 2006

Is there any way to emulate the try/catch mechanism that SqlServer2005 provides using SqlServer2000?
Or more simply,
Is there any kind of IGNORE_ERROR or CONTINUE_ON_ERROR setting for SqlServer 2000?

Thanks

- John

View 2 Replies View Related

SQL 2000 View Has Column Binding Errors

Feb 11, 2008


Hi all,

I'm trying to use a SQL 2000 view in one of my sources. The view isnt anything special --- just three tables that have been unioned together. All these three tables have the EXACT same datatypes as well as column names. There are no constraints on these tables (yet). There is an identity seed on the first ID column. However, when I try to access this view, it generates the following error:

Server: Msg 4502, Level 16, State 1, Procedure MyView, Line 5
View or function 'MyView' has more column names specified than columns defined.
Server: Msg 4413, Level 16, State 1, Line 1
Could not use view or function 'MyView' because of binding errors.

Does anybody have any experience with this?

View 3 Replies View Related

Corrupted Table In SQL 2000, DBCC CheckDB Found No Errors

Jan 21, 2008

Hello,

I am facing an issue with a table in my database.

1) When I run a query Select * from MY_Table the query never completes. If I press cancel in Query Analyzer after 1 second or 2 minutes, it always shows the same 174 records. The total table size is 800 KB with 3148 rows .. according to TaskPad

2) No queries, even queries that would return only 1 record complete execution against that table

3) I am unable to rename the table, if I try to rename the table Enterprise Manager becomes unresponsive and has to be closed.

4) If I try to browse the rows through Enterprise manager by scrolling through the records, it allows me to go down through a number of rows and then Enterprise manager gives me a timeout expired error.

5) I ran a DBCC CheckDB command ran for 1 hr and 7 minutes. It says there are 0 allocation errors and 0 consistency errors in the database.

6) The other database tables seem to be OK and the Database is operational except for calls involving the 1 Table

7) I am unable to Drop the Table

8) DBCC CheckAlloc finds 0 allocation errors and 0 consistency errors.

9) DBCC CheckTable on MY_Table returns with: There are 3148 rows in 33 pages for object 'MY_Table' .. no errors are printed.


Can one of the SQL Gurus on this forum please recommend a course of action.

Thanks in advance.

View 1 Replies View Related

RS 2000 - Can't Start Report Manager / Permission Errors / Need To Update Data Source

Jul 5, 2007

We are going insane trying to start Report Manager on a SQL Reporting Services 2000 installation. The programmer/admin who originally set this up for us is gone.



We recently upgraded a database/application server to a new server, causing the data source being used by reports in reporting services to no longer be valid. Unfortunately, we do not have access to the original report project to 're-deploy' with the corrected data source.



We desperately need to get the reports that are installed to retrieve their data from the new database location/machine. We understand this can be done by specifying a new data source in Report Manager. (To clarify, we have NOT moved the report servier installation or database, these remain in place - it's just the deployed reports that no longer point to the correct data source.)



For some reason, our Report Manager will no longer run - when we try to launch it, we get a windows login dialog - nothing will work here. We've tried both local and domain admins and constantly get ACCESS IS DENIED 401.3 error messages that we do not have permission/problems with ACL's.



We've gone so far as to allow EVERYONE read/write access to the ReportManager and ReportServer folders and the RS virtual directories, but nothing seems to help.



Can anyone help with this? Ideas on how to change our data source, or how to get back into Report Manager?



Since we are somewhat technical, but not experts, and don't have much more time to waste, we are willing to pay $500 for this project to someone willing to access the server and fix the problem so that the reports point to the correct database and restore access to Report Manager.



Thanks in advance for any help.

View 1 Replies View Related

SQL Server Admin 2014 :: Restore Lost Transaction From Transaction Log File

Jun 10, 2015

I have Full database backup upto previous day and transaction logfile of Today transaction. my database has crashed. I have restored previous day's Full backup. I have faced difficulty to restore today's transaction from today's transaction log. What are the steps to restore full database back and one day's transaction log file. Note: there is no differential database backup and transaction backup.

View 8 Replies View Related

SS 2000 Transaction Log

Feb 14, 2005

Hi all,

I'm having an issue shrinking a transaction log, and am hoping that one of you guys could give me some advice....

Currently, this database (DB1) has approximately 20GB of data, and my transaction log has grown to 22GB.

The database is in Simple Mode (although may have been created as Full, then changed)

I have a Maint. Plan backing up the DB daily, but noticed today that this has not been working for the last week :'(

There are 3 other databases on this same server, which are similar sizs in GB, but have tiny Trans Logs - their backups have of course been working fine.

I've backed up DB1 this morning - a whopping 42GB file - then issued a "dbcc shrinkfile", followed by a "backup log ... truncate_only", followed by another "shrinkfile", but the log is still 22GB

Any suggestions??? Please....

View 1 Replies View Related

How To Minimize The Transaction Log SQL 2000

Dec 11, 2001

My database log file is now 53M, is it a way to minimize it, delete it or erase it, this log file is to big for nothing, I try to shrink it and delete it manually, but this corrupt the database, can someone help me thx ?

View 2 Replies View Related

MS SQL 2000- Reduce Transaction Log Size

May 5, 2004

The size of my transaction log file is out of control. I've backed up the database and the transaction log went from 120 GB to 120 MB. Now, I can't reduce the size of the transaction log file. It's still at 120 GB (w/ almost all of that being held as Free Space). I get errors when I try to manually reduce the file size. Any tips?

View 9 Replies View Related

Error 8525: Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction.

May 31, 2008

Hi All

I'm getting this when executing the code below. Going from W2K/SQL2k SP4 to XP/SQL2k SP4 over a dial-up link.

If I take away the begin tran and commit it works, but of course, if one statement fails I want a rollback. I'm executing this from a Delphi app, but I get the same from Qry Analyser.

I've tried both with and without the Set XACT . . ., and also tried with Set Implicit_Transactions off.

set XACT_ABORT ON
Begin distributed Tran
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1 and DONE = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
COMMIT TRAN


It's got me stumped, so any ideas gratefully received.Thx

View 1 Replies View Related

Load SQL 7 Database And Transaction Log Backups To SQL 2000

Jul 20, 2005

Is it possible to load both the SQL 7 database and transaction logbackups to SQL 2000 ? I assume it will perform the upgrade during theload.Thanks,James

View 4 Replies View Related

Distributed Transaction On Windows 2000 Professional

Jun 19, 2006

Just curious if anyone out there has had success running a Distributed Transaction on Windows 2000 Professional SP4 lately?

I have come to the conclusion that is is virutally impossible. I am running MSDE on a Win2k Pro machine and trying to run a distributed transaction via linked server and am getting the

"The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction."

error. Yes I have been scouring the web looking for answers and have implemented pretty much everything I have run accross... and Yes the MSDTC service is started on both machines... BTW: If I run the procedure on XP it works great!!!

I am wondering if anyone even uses Win2k Pro anymore let alone trying to run a distributed tarnsaction on the darn thing. 

If anyone out there has it running let me know I would love to chat with you for a minute or two.

 

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved