Bogus FK Violation On Insert - Transaction Failure?
May 23, 2008
I have 14 beta users on a PPC application based on .NET CF 2.0 and SQLCE 3.0. Data is sent back and forth as XML via web services. One user cannot complete the initial download - he receivces the following error every time:
A foreign key value cannot be inserted because a corresponding primary key value does not exist. [ Primary key constraint name = FK_jobtask_job ]
I have run the queries for this user and verified that no task has a JobID that is not in the Job table. I am also able to successfully complete the initial download as the user in the Mobile 5.0 PPC emulator. His device has 130 MB of free storage space and 20 MB program space (my emulator has 11 MB storage and 70 MB program).
The initial download for this user is about 14 MB of XML, all of which will be inserted in the SDF under a single transaction. Although the SDF was only 4.75 MB on my emulator (after successfully downloading as this user) I am wondering if there is an unreported error in the handling of the transaction or its temp file that causes the error he receives. Frankly, I've run out of ideas. Does anyone have details about transaction temp file size limits or any other ideas that might lead to a resolution?
Scott
View 3 Replies
ADVERTISEMENT
Oct 31, 2007
I have been battling this for 2 days! I could write a book on what does not work. I have even tried entering the data manually through the 'Open Table' SSMS function from the Object Explorer. I only have 5 records in the table. The first 4 I added a week or so ago during dev from the ASP.Net 2.0 (w VB) website that is the front end for this DB. I use a DAL between the site and DB.I added two new fields the other day and everything seemed to work fine. Now I get 'Siteid CANNOT BE NULL'. I abandoned attempts to Insert from the Website. The following was done directly through SSMS.I switched all of the columns (except PK) to allow nulls. I could add/update a record, no problem, even with directly typed data in some fields. I could also add one with only one field populated (some have defaults, some do not).Here is the table code:USE [bro122]GO/****** Object: Table [dbo].[tbsaDrem] Script Date: 10/31/2007 18:46:34 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[tbsaDrem]([DremID] [int] IDENTITY(1,1) NOT NULL,[CustID] [int] NOT NULL CONSTRAINT [DF_tbsaDrem_CustID] DEFAULT ((0)),[RealityYN] [bit] NULL CONSTRAINT [DF_tbsaDrem_ContractYN] DEFAULT ((0)),[DreamName] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_tbsaDrem_DreamName] DEFAULT ('New (Modify Name)'),[SiteID] [int] NOT NULL,[SitePremPric] [money] NULL CONSTRAINT [DF_tbsaDrem_SiteBasePric] DEFAULT ((0)),[NbhdBasePric] [money] NULL CONSTRAINT [DF_tbsaDrem_NbhdBasePric] DEFAULT ((0)),[PlanCtlgID] [int] NOT NULL,[PlanPric] [money] NULL CONSTRAINT [DF_tbsaDrem_PlanPric] DEFAULT ((0)),[SpecTotl] [money] NULL CONSTRAINT [DF_tbsaDrem_SpecTotl] DEFAULT ((0)),[DremTotl] AS ((([NbhdBasePric]+[SitePremPric])+[PlanPric])+[SpecTotl]) PERSISTED,[Modified] [datetime] NOT NULL CONSTRAINT [DF_tbsaDrem_Modified] DEFAULT (getdate()),[ModUser] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_tbsaDrem_ModUser] DEFAULT ('None'),[Created] [datetime] NOT NULL CONSTRAINT [DF_tbsaScenario_CreatedDate] DEFAULT (getdate()),[ts] [timestamp] NOT NULL,CONSTRAINT [PK_tbsaDremID] PRIMARY KEY CLUSTERED ([DremID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 60) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'This is flagged with a Trigger when a Dream becomes a Reality. ( contract is ratified.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tbsaDrem', @level2type=N'COLUMN',@level2name=N'RealityYN'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Heading Data for Each Dream (Scenario) for Customers. Each Dream can and must have only one Customer, Site, Plan. Dream Items are stored in the tbsaDreamItem table.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tbsaDrem'GOALTER TABLE [dbo].[tbsaDrem] WITH CHECK ADD CONSTRAINT [FK_tbsaDrem_tbjoSite] FOREIGN KEY([SiteID])REFERENCES [dbo].[tbjoSite] ([SiteID])GOALTER TABLE [dbo].[tbsaDrem] CHECK CONSTRAINT [FK_tbsaDrem_tbjoSite]GOALTER TABLE [dbo].[tbsaDrem] WITH CHECK ADD CONSTRAINT [FK_tbsaDrem_tbpdPlanCtlg] FOREIGN KEY([PlanCtlgID])REFERENCES [dbo].[tbsaCtlg] ([CtlgID])GOALTER TABLE [dbo].[tbsaDrem] CHECK CONSTRAINT [FK_tbsaDrem_tbpdPlanCtlg]GOALTER TABLE [dbo].[tbsaDrem] WITH CHECK ADD CONSTRAINT [FK_tbsaDrem_tbsaCust] FOREIGN KEY([CustID])REFERENCES [dbo].[tbsaCust] ([CustID])GOALTER TABLE [dbo].[tbsaDrem] CHECK CONSTRAINT [FK_tbsaDrem_tbsaCust]Any help is enormously appreciated!!!
View 4 Replies
View Related
Aug 9, 2007
I've read a few different articticles wrt how the handle this error gracefully. I am thinking of wrapping my sql insert statement in a try catch and have the catch be something likeIF ( e.ToString() LIKE '% System.Data.SqlClient.SqlException:
Violation of PRIMARY KEY constraint 'PK_PS_HR_Hrs'. Cannot insert duplicate key
in object %'){lable1.text = "Sorry, you already have reported hours for that day, please select anothe rdate" } Is there a better way?TIA Dan
View 4 Replies
View Related
Dec 23, 2007
The following SP causes the error "Violation of UNIQUE KEY constraint 'AlumniID'. Cannot insert duplicate key in object [table name].
The statement has been terminated." AlumniID is the table's PK and is set to autoincrement. I'd appreciate any help or suggestions.
1 ALTER PROCEDURE dbo.sp_CreateUser
2
3 @UserID uniqueidentifier,
4 @UserName nvarchar(128),
5 @Email nvarchar(50),
6 @FirstName nvarchar(25),
7 @LastName nvarchar(50),
8 @Teacher nvarchar(25),
9 @GradYr int
10
11 AS
12 SET NOCOUNT ON;
13 --DECLARE @UserID uniqueidentifier
14 --SELECT @UserID = NULL
15 --SELECT @UserID = UserID FROM dbo.aspnet_Users WHERE LOWER(@UserName) = LoweredUserName-- AND @ApplicationId = ApplicationId
16 INSERT INTO [table]
17 (UserID,UserName,Email,FirstName,LastName,Teacher,GradYr)
18 VALUES (@UserID,@UserName,@Email,@FirstName,@LastName,@Teacher,@GradYr
View 2 Replies
View Related
Mar 14, 2008
I have a table with 0 records. When I try to insert records using a SP, it gives the following error.
Violation of UNIQUE KEY Constraint 'constraint name'. Cannot insert duplicate key in object 'Objectname'.
How do I resolve this.
Thanks.
View 1 Replies
View Related
Jan 20, 2004
Cannot find an answer to this in previous posting, though there are similar topics.
My primary key "ID" requires a value (is not nullable), and not explictly providing it with one when I update a new record gives the following error:
Cannot insert the value NULL into column 'ID', table 'AdClub.mediaq.News'; column does not allow nulls. INSERT fails.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'ID', table 'AdClub.mediaq.News'; column does not allow nulls. INSERT fails.
However, trying to stuff that field with a recordCount+1 value (or any value), I get this error:
Violation of PRIMARY KEY constraint 'Primary Key'. Cannot insert duplicate key in object 'News'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'Primary Key'. Cannot insert duplicate key in object 'News'.
Cannot figure this one out. The value I'm providing for that field is known to be unique, but the SQL Server spits it out each time.
Is there a way to have the ID primary field automatically update with a new value when a new record is generated? This is how I used to do it in Access, but cannot find a similar feature in SQL Server. I'm sure I'm missing something simple, but right now I'm stuck in this "Catch-22" situation.
Please help!
View 5 Replies
View Related
Oct 19, 2007
the point here that i have a small table with two fileds,
ID (guid) as primerykey
RAF(char)
and the table is empty when i add a new row i recieve this exception,
Violation of PRIMARY KEY constraint 'PK_tblType'. Cannot insert duplicate key in object 'dbo.tblType'.
i found no way to solve the problem.
thanks in advans
View 7 Replies
View Related
Sep 8, 2013
I have table variable in which I am inserting data from sql server database. I have made one of the columns called repaidID a primary key so that a clustered index will be created on the table variable. When I run the stored procedure used to insert the data. I have this error message; Violation of Primary key Constraint. Cannot insert duplicate primary key in object. The value that is causing this error is (128503).
I have queried the repaidid 128503 in the database to see if it is a duplicate but could not find any duplicate. The repaidID is a unique id normally use by my company and does not have duplicates.
View 9 Replies
View Related
Sep 9, 2007
When running SQLEVAL.EXE, I'm getting an error message when the installation wizard finished 'reading contents of the package' reading:
There is not enough space on C: to extract this package.
Having checked the requirements http://www.microsoft.com/sql/editions/developer/sysreqs.mspx, the free disk space on my c: is 45.42Gb.
Any ideas?
View 1 Replies
View Related
Aug 4, 2000
I seem to have a problem with my SQL Server log backup system. First I was receiving alerts that the log file was filling up, so I increased it from 3Mb to 150Mb, but then I got another alert saying the same thing, later on I got an alert saying that the log backup had failed. I then decided to truncate the log and do a full backup after as suggested, however this did not work, I don't know if I am doing something wrong, this is really confusing and frustrating.
View 1 Replies
View Related
Aug 15, 1999
I have scheduled transaction log backups to occur every 15 minutes with database backups every sunday at 1.00AM and differential db backups every day at 1.30AM.
While viewing the job history of the transaction log backups through enterprise manager, I noticed that one transaction log backup had failed at 1.00AM on sunday. The error message was,
"Backup, CHECKALLOC, bulk copy, SELECT INTO, and file manipulation (such as CREATE FILE) operations on a database must be serialized. Reissue the statement after the current backup, CHECKALLOC, or file manipulation operation is completed. [SQLSTATE 42000] (Error 3023) Backup or restore operation terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed. "
Please let me know what the error message implies. Will there be any problem if the transaction log backup takes place at the same time as the full database backup or diff. db backup?
Thankyou.
Praveena
View 2 Replies
View Related
Jan 14, 2002
The Transaction log backups that I do for the msdb and master databases ALWAYS fail. The full database backups always succeed. Are the master and msdb databases not supposed to have their transaction logs backed up? Jobs where configured thru the Database Maintenance wizard. Will the jobs fail if nothing has changed in either database? I am also confused by the fact that database and log backups for the model database ALWAYS work when I expect their to be little if none activity in that database.
Thanks in advance,
Riley
View 2 Replies
View Related
May 10, 2006
I’m attempting to insert the result set from a remote query into a local table and I’m getting the following error:
Msg 8501, Level 16, State 1, Line 1
MSDTC on server 'REMOTESVR' is unavailable.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d01c].
Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
MSDTC is running on both servers. Has anyone ever seen this or have any insight into the cause?
The code that I’m trying to run is:
create table msver
(
[Index] int,
[Name] varchar(30),
Internal_Value varchar(20),
Character_Value varchar(512)
)
insert into msver
exec ('exec [REMOTESVR].master.dbo.xp_msver')
Note that the remote query works fine.
Thanks!
Eric
View 3 Replies
View Related
May 26, 2008
Hi
I have a job that runs full backs and that was successfull but all my transaction log backups failed. This is the error message i get:
1.VDI error 1010: Failed to get configuration from server. Check that the SQL Server instance is running, and that you have the SQL Server Systems Administrator server role. Error code: (-2139684861: The api was waiting and the timeout interval had elapsed.)
2.SQL error 3013: BACKUP LOG is terminating abnormally.
3.SQL error 4214: BACKUP LOG cannot be performed because there is no current database backup.
Please assist.
View 6 Replies
View Related
Feb 17, 2003
I'm getting "Executed as user: SPIESQLService. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed." on the TRN backup portion of the maintenance plan for the msdb and model databases. On review of files created it's clear that the msdb trn log backup is failing, but there's no other error to indicate the underlying problem.
Suggestions?
TIA,
Al
View 1 Replies
View Related
May 14, 2007
I have some tables that I need to wipeout and reload once per day. So I have created a T-SQL task that does a truncate table, then a data flow task, then a update statistics task. Running those works fine, but as soon as I put them all into a sequence container and set the container to require transactions, the first step gets a connection failure. "Failed to acquire connection "<name>". Connection may not be configured correctly or you may not have the right permissions on this connection". I am using a .Net SQLClient data provider for the T-SQL task and in the data flow task I have to use a OLEDB provider so that I can run the task locally in development.
Is there something I am doing wrong or is there some other way to handle truncate, load, update stats all in a transaction?
Thanks.
Tim
View 8 Replies
View Related
Sep 20, 2007
I've written some provider independent code that dynamically generates and loads DbCommands with data via Parameters and executes them within a transaction. Everything works fine until an exception occurs and then the DbCommand.Transaction property that was set goes to null, effectively killing any chance of rolling back the transaction. I've noticed this only happens when the executed command is of type SqlCommand. The OracleCommand mainitains its link to the set transaction of failure. Upon further investigation, I found that this only occurs when parameters are used but not if I dynamically generate the insert statement as a string.. For instance:SqlConnection conn = new SqlConnection(connectionString);conn.Open();SqlTransaction transaction = conn.BeginTransaction();SqlCommand comm = new SqlCommand();comm.Connection = conn;comm.Transaction = transaction; command.commandText = "INSERT INTO Table1 Values (1, 2, 3)";command.ExecuteNonQuery(); If I execute that and it fails the command.Transaction property will remain set to transaction, but in this scenario: SqlConnection conn = new SqlConnection(connectionString);conn.Open();SqlTransaction transaction = conn.BeginTransaction();SqlCommand comm = new SqlCommand();comm.Connection = conn;comm.Transaction = transaction; comm.commandText = "INSERT INTO Table1 Values (@Col1,@Col2, @Col3)";comm.Parameters.add(new SqlParameter("@Col1", 1); comm.Parameters.add(new SqlParameter("@Col2", 2);comm.Parameters.add(new SqlParameter("@Col3", 3); comm.ExecuteNonQuery(); Upon failing the comm.Transaction property gets somehow goes null and the Connection property of the transaction object also goes null.Any ideas on why this only happens with a SqlCommand but no other DbCommand?
View 4 Replies
View Related
May 18, 2005
If I have a database backup from sunday, and a failure occurs monday... Can the backup .mdf and .ldf files be attached, and the backup log after the point of failure be applied to them?
The problem I am having is it looks like you can only restore from a .bak file, and then apply the log at the point of failure. IT doesn't look like you can restore the .ldf/.mdf files, and then apply the backup log from the point of failure.
Can someone please help? I'm in desparate need of fixing this !
Thanks,dp
View 1 Replies
View Related
Dec 2, 2004
I have a file with 21345 records in it and it is a tab delimited file.When I use bulk insert on that file to a table it is inserting only the first 10680 records and is not loading the rest of the records but when I use the DTS package to insert the file to the table it is inserting all the 21345 records into the table.
I do not want to use DTS ,I want to use the bulk insert.This is the first time I have ever encountered such a strange thing happening.Can someone please put some light into it and tell me what could be the problem . I would really appreciate it.
Thanks
View 4 Replies
View Related
Sep 10, 2007
Hello All
I was trying to insert some row from one table to another of different database.
I was using Execute SQL task along with Foreach loop container.
In my execute SQL task I am using this query
SET IDENTITY_INSERT dbo.Table1 ON
INSERT INTO dbo.Table1
SELECT * FROM DB2.dbo.Table2
WHERE TableKey = ?
When executed I get this error:
failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
While the same query when executed in Management Studio Its successful.
The properties I set
For Each Loop Editor Settings:
1) Collection: a) Enumerator Set to ForEach ADO Enumerator
b) ADO Object Source Variable: User:bjectVariablename
c) Checked Rows in the first table
2) Variable Mapping: New Int Variable2 and Index = 0 to set it to first colunm.
3) Expression: Left blank
Execute SQL Task Editor:
1) General: a) Timeout : 0
b) CodePage: 1252
c) Result Set: None
d) SQLSourceType: Directinput
e) SQL Statement: SET IDENTITY_INSERT dbo.Table1 ON INSERT INTO dbo.Table1 SELECT * FROM DB2.dbo.Table2 WHERE TableKey = ?
f) BypassPrepare: False
2)Parameter Mapping: Variable Name : New Integer variable2 selected
Direction: Input
DataType: Long
ParameterName: 0
Can somebody help me in this regards.
Reference:
a) http://www.whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/03/03/126.aspx
View 10 Replies
View Related
Nov 1, 1999
Using BULK Insert with a format file I am receiving the following message:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAMS' reported an error. The provider did not give any information about the error.
The statement has been terminated.
I am running SQL Server 7.0 w/ SP1 applied. The same data file and format files work fine if I use bcp.
The data file contains fixed length records.
Any ideas what the problem is?
View 2 Replies
View Related
Jan 6, 2014
We have created a DDL trigger on SQL server 2005 database for DB audit purpose. Following is the script used for trigger creation
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ChangeLog](
[Code] ....
After the DDL trigger creation. Application team started reporting following error while executing a stored procedure.
*********************************
Error 1:
INSERT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.
Error2:
[Execute SQL Task] Error: Executing the query "exec sp_drop_indexes_EnhLeaseData delete from dbo.leases where vin_num='XXX' and lease_acct_num='XXXX' delete from dbo.leases where vin_num='XXX' and lease_acct_num='080066225' " failed with the following error: "INSERT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
View 1 Replies
View Related
Oct 21, 2003
Hello Everybody!
Please help me understand following.
We have sql 2k(sp3), Win 2000 Server.
This morning I noticed that our website was down due to timeout error. The asp page was timing out while one of the stored procedures was trying to insert a record into table. Situation on the server was as following:
mdf file - 3.7GB (2.1GB used)
ldf file - 3.1GB (2.9GB used)
Both mdf and ldf are set to grow automatically by 10% with no limit.
Actual space on hard disk was 1.9GB
I tried to run that stored procedure thru QA and it was taking unreasonable amount of time so I had to stop it (usually it takes split of a second).
I went ahead and freed up some space on hard disk and it worked.
My question is:
1)why did it fail to grow into the remaining 1.9GB of space when it filled up to 3.1GB (10% of 3.1GB is 300MB and that's way under 1.9GB I had)??
Thank you very much in advance.
View 4 Replies
View Related
Sep 30, 2007
Hi !
I want to insert master/detail data using transaction if while insert if error it will Rollback. Help me! Thanks
View 2 Replies
View Related
Jan 10, 2007
SQL Server 2005XEON CPU 3.0GMEMORY 2.0GRAID
Tow tables:HIS_HTTP_ONLINE_LOG(PARTITION) FOR HISTORY DATAREL_HTTP_ONLINE_LOG(NOT PARTITIONED) FOR EVERYDAY DATA,AND THEY HAVE THE SAME STRUCTURE
CREATE TABLE HIS_HTTP_ONLINE_LOG(ID numeric(20,0) NOT NULL,USERID varchar(32) NOT NULL,USERIP varchar(16) NOT NULL,USERPORT numeric(10, 0) NULL,OBJECTIP varchar(16) NULL,OBJECTPORT numeric(10, 0) NULL,HTTPURL varchar(256) NULL,HTTPHOST varchar(128) NULL,HTTPDNS varchar(128) NULL,VISITIME numeric(10, 0) NULL,STARTIME datetime NOT NULL,ENDTIME datetime NOT NULL).......
SELECT * INTO REL_HTTP_ONLINE_LOG SELECT * FROM HIS_HTTP_ONLINE_LOGWHERE 1=2
There are 5 indexes in HIS_HTTP_ONLINE_LOG ,There is not one index in REL_HTTP_ONLINE_LOG
There are about 5000,000 records in REL_HTTP_ONLINE_LOG everyday,at night it will move into HIS_HTTP_ONLINE_LOG automatically,The data of everyday in REL_HTTP_ONLINE_LOG will be last 90 days.
My operations:1: ALTER DATABASE DB SET RECOVERY SIMPLE2: EXEC SP_DBOPTION DB, 'select into/bulkcopy', 'TRUE'3：INSERT INTO REL_HTTP_ONLINE_LOG SELECT * FROM HIS_HTTP_ONLINE_LOGWHERE 1=24: TRUNCATE TABLE REL_HTTP_ONLINE_LOG
ASK:why the step 3 cost so much time ? (about 1 hour) and how can I reduce the transaction logs in this period ? Could you give me some suggestions ?Thanks!
View 5 Replies
View Related
Jan 10, 2007
SQL Server 2005XEON CPU 3.0GMEMORY 2.0GRAID
Tow tables:HIS_HTTP_ONLINE_LOG(PARTITION) FOR HISTORY DATAREL_HTTP_ONLINE_LOG(NOT PARTITIONED) FOR EVERYDAY DATA,AND THEY HAVE THE SAME STRUCTURE
CREATE TABLE HIS_HTTP_ONLINE_LOG(ID numeric(20,0) NOT NULL,USERID varchar(32) NOT NULL,USERIP varchar(16) NOT NULL,USERPORT numeric(10, 0) NULL,OBJECTIP varchar(16) NULL,OBJECTPORT numeric(10, 0) NULL,HTTPURL varchar(256) NULL,HTTPHOST varchar(128) NULL,HTTPDNS varchar(128) NULL,VISITIME numeric(10, 0) NULL,STARTIME datetime NOT NULL,ENDTIME datetime NOT NULL).......
SELECT * INTO REL_HTTP_ONLINE_LOG SELECT * FROM HIS_HTTP_ONLINE_LOGWHERE 1=2
There are 5 indexes in HIS_HTTP_ONLINE_LOG ,There is not one index in REL_HTTP_ONLINE_LOG
There are about 5000,000 records in REL_HTTP_ONLINE_LOG everyday,at night it will move into HIS_HTTP_ONLINE_LOG automatically,The data of everyday in REL_HTTP_ONLINE_LOG will be last 90 days.
My operations:1: ALTER DATABASE DB SET RECOVERY SIMPLE2: EXEC SP_DBOPTION DB, 'select into/bulkcopy', 'TRUE'3：INSERT INTO REL_HTTP_ONLINE_LOG SELECT * FROM HIS_HTTP_ONLINE_LOGWHERE 1=24: TRUNCATE TABLE REL_HTTP_ONLINE_LOG
ASK:why the step 3 cost so much time ? (about 1 hour) and how can I reduce the transaction logs in this period ? Could you give me some suggestions ?Thanks!
View 1 Replies
View Related
Feb 17, 2004
I have 3 development SQL Servers A, B & C, all running SQL 2000 sp3 and Windows 2003. Servers B & C have a linked server pointing to A, and A has one pointing to B & C. The linkedservers all have RPC , RPC out enabled. I have a stored procedure called test on server A.
Create Proc test
as
Select Top 5 first_name, last_name from people
GO
--code ran on Servers B & C:
create table #tmptbl (nm varchar(100), nm2 varchar(100))
insert into #tmptbl
Exec ServerA.db1.dbo.test
When the Insert....Exec code above is ran from server B it works fine, however when I run it from Server C, I get error 7391
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a]
But regular linked server calls (directly to tables) and openquery calls work fine from either server...
Eg
insert into #tmp
Select top 5 first_name, last_name from ServerA.db1.dbo.people
--and this works also
insert into #tmp
Select * from openquery(ServerA, 'Exec db1.dbo.test')
Both servers (B & C) appear to be configured the same, and
I have reconfigured MSDTC on all three boxes through control panel and component manager, have tried using SET xact_abort, SET implicit_transactions, registry hacks (TurnoffRPCsecurity), basically everything listed on Microsoft, and everything I've been able to find in these groups.
If anyone has any ideas, I'd like to hear them.
Tim.
View 3 Replies
View Related
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
Mar 4, 2007
i am creating/uploading a new file on the webserver, and if it is successfully i want to insert a record in the database (with the filename).is there a way to create a transaction for this so that if either operation fails they both fail?
View 2 Replies
View Related
Feb 7, 2008
I have looked at the membership and roles stored procs from Microsoft and noticed that most of them are wrapped into a transaction. Ok some of the stored procs updated more than one table in which case it makes sense to wrap the code into a transaction. Our stored procs are a little simpler and insert, update or delete only one table for the most part. My question is: What is good practice, should I wrap my stored procs in transactions or because I am only updating one table leave it the way it is, see sample below: Please advise, newbie
ALTER PROCEDURE [dbo].[syl_Category_Insert] @CategoryName nvarchar(64), @LanguageID int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRYINSERT INTO [syl_Categories]
VALUES(
@CategoryName,
@LanguageID)
SELECT SCOPE_IDENTITY() AS [CategoryID]
RETURNEND TRY
BEGIN CATCH
--Execute LogError_Insert SP EXECUTE [dbo].[syl_LogError_Insert];
--Being in a Catch Block indicates failure.
--Force RETURN to -1 for consistency (other return values are generated, such as -6).RETURN -1
END CATCH
END
View 2 Replies
View Related
Apr 11, 2008
Hello,
I have problem for insert multiple query for insert in differenr tabels for a single record.
I have mail record for candidate and now i wants to insert candiate labour info, candidate passport detail in diff tabel like candidatLabour and candidatePassport,
i used two store procedure for it and i write code for it.and it works fine,but i think that if one SP executed and one record inserted but then some problem occure and 2nd SP not executed then...........
so plz help me
Thanks
View 5 Replies
View Related
Jul 21, 2004
Hi, guys
I try to add some error check and transaction and rollback function on my insert stored procedure but I have an error "Error converting data type varchar to smalldatatime" if i don't use /*error check*/ code, everything went well and insert a row into contract table.
could you correct my code, if you know what is the problem?
thanks
My contract table DDL:
************************************************** ***
create table contract(
contractNum int identity(1,1) primary key,
contractDate smalldatetime not null,
tuition money not null,
studentId char(4) not null foreign key references student (studentId),
contactId int not null foreign key references contact (contactId)
);
My insert stored procedure is:
************************************************** *****
create proc sp_insert_new_contract
( @contractDate[smalldatetime],
@tuition [money],
@studentId[char](4),
@contactId[int])
as
if not exists (select studentid
from student
where studentid = @studentId)
begin
print 'studentid is not a valid id'
return -1
end
if not exists (select contactId
from contact
where contactId = @contactId)
begin
print 'contactid is not a valid id'
return -1
end
begin transaction
insert into contract
([contractDate],
[tuition],
[studentId],
[contactId])
values
(@contractDate,
@tuition,
@studentId,
@contactId)
/*Error Check */
if @@error !=0 or @@rowcount !=1
begin
rollback transaction
print ‘Insert is failed’
return -1
end
print ’New contract has been added’
commit transaction
return 0
go
View 1 Replies
View Related
