To commit a transaction on SQL server A database A that also inserts a record to SQl Server B database B (this is a linked sql server) do I need the xact_abort set on before the commit trans statement. Or is doing this not possible
View 1 RepliesNo I did not write this below, this is from a vendor, I used profiler and I believe their SP is causing a blocking problem on their vendor supplied DB. It thought at the least always have a begin end or a begin trans commit trans. ANy quick opinions greatly appreciated
create procedure write_planned_service_rec
@p1 varchar(20),@p2 varchar(20),@p3 varchar(20),@p4 varchar(20),@p5 varchar(20),
@p6 varchar(20),@p7 varchar(20),@p8 varchar(20),@p9 varchar(20),
@p10 varchar(20),@p11 varchar(20),@p12 varchar(20),@p13 varchar(20),@p14
varchar(20),
@p15 varchar(20),@p16 varchar(20),@p17 varchar(20),@p18 varchar(20),@p19
varchar(20),
@p20 varchar(20)
AS
IF @p20 = 'P'
update patient set date_insurance_updated = getdate() where patient_id =
@p1 and practice_id = @p13
Does anyone know who I can truncate the transaction log of a database that
is on a linked server?
TRUNCATE LOG [linked server].[dbo].[database] WITH TRUNCATE_ONLY
does not work.
Thanks,
Kell
I'm trying to connect to another slq server db through a linked server (and synonyms) but I get the following error.
Exception has been thrown by the target of an invocation. ---> System.Data.SqlClient.SqlException: The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. ,Unable to start a nested transaction for OLE DB provider "SQLNCLI" for linked server "BURT". A nested transaction was required because the XACT_ABORT option was set to OFF. , at Westfalia.TDMN.TxObjects.TxBurtOCIInterface.BurtOCIPalletChangedRecords_Get() , --- End of inner exception stack
I've set up MSDTC and executed the procedures noted in MS's article ID 873160. I am using 2005, but the Burt server is 2000. I can run the stored procedure from my server, which queries theirs. But when I execute the sp from a service, I get the error. Any help would be greatly appreciated.
Newbie question
I have an Update trigger and an Insert trigger on a table both of which use a variant of the code below. The trigger takes information from the table and updates information in another database configured as a linked server.
The update trigger works fine without the Set XACT_ABORT ON statement.
The insert trigger crashes unless I put this line in.
The update trigger runs 10 times faster without the statement.
Is there an alternative to this that will allow the insert trigger to run without crashing? and run faster. The insert trigger takes forever to run now. (Forever being about 20 seconds). The Update trigger takes about 2 seconds without the Set XACT_ABORT ON statement and 20 seconds with it.
Any help would be appreciated.
Tom
Set XACT_ABORT ON
Select @pos=COUNT(*)From SQLPSPRD.DTEFSPRD.dbo.PS_GCC_CONTACT_DTL
where UPPER(GCC_CONTACT_ID)=UPPER(RTRIM(@tmpcompid2)) and GCC_CONTACT_SEQ=@maxseq2
if(@pos>0)
begin
Update SQLPSPRD.DTEFSPRD.dbo.PS_GCC_CONTACT_DTL
set GCC_BUSINESS_UNIT=UPPER(@tmpbus),GCC_CONTACT_NAME= isnull(@nameout,' '),
GCC_CONTACT_ID=@tmpcompid,GCC_CONTACT_SEQ=@maxseq,
GCC_CONTACT_CMPY=isnull(UPPER(@compout),' '),GCC_CONTACT_ADDR1=isnull(@add1out,' '),
GCC_CONTACT_ADDR2=isnull(@add2out,' '),GCC_CONTACT_ADDR3=isnull(@add3out,' '),
GCC_CONTACT_CITY=isnull(@tmpcity,' '),GCC_CONTACT_STATE=isnull(UPPER(@tmpstate),' '),
GCC_CONTACT_ZIP=isnull(UPPER(@zipout),' '),GCC_CONTACT_CNTRY=isnull(UPPER(@ctryout),' '),
GCC_CONTACT_PHONE=isnull(UPPER(@tel1out),' '),GCC_CONTACT_FAX=isnull(UPPER(@tel2out),' '),GCC_CONTACT_CELL=isnull(UPPER(@tel3out),' '),
GCC_CONTACT_EMAIL=isnull(@emailout,' '),GCC_EFF_STATUS=@outstatus,GCC_OPRID=isnull(@mgr out,' '),GCC_DTTM_STAMP=CONVERT(Char, GetDate(), 101)
where UPPER(RTRIM(@tmpcompid2))=UPPER(GCC_CONTACT_ID) and @maxseq2=GCC_CONTACT_SEQ
end
else
begin
Select @rcount=count(GCC_CONTACT_SEQ)
From SQLPSPRD.DTEFSPRD.dbo.PS_GCC_CONTACT_DTL
where UPPER(GCC_CONTACT_ID)=UPPER(RTRIM(@tmpcompid)) AND GCC_CONTACT_SEQ=@maxseq AND UPPER(RTRIM(@nameout))=UPPER(RTRIM(GCC_CONTACT_NAM E))
if(@rcount<1)
begin
Insert into SQLPSPRD.DTEFSPRD.dbo.PS_GCC_CONTACT_DTL
(GCC_BUSINESS_UNIT,GCC_CONTACT_ID,GCC_CONTACT_SEQ, GCC_EFFDT,GCC_CONTACT_NAME,GCC_CONTACT_CMPY,GCC_CO NTACT_TYPE,GCC_CONTACT_ADDR1,GCC_CONTACT_ADDR2,GCC _CONTACT_ADDR3,GCC_CONTACT_ADDR4,GCC_CONTACT_CITY, GCC_CONTACT_CNTY,GCC_CONTACT_STATE,GCC_CONTACT_ZIP ,GCC_CONTACT_CNTRY,GCC_CONTACT_PHONE,GCC_CONTACT_F AX,GCC_CONTACT_CELL, GCC_CONTACT_EMAIL,GCC_EFF_STATUS,GCC_OPRID,GCC_DTT M_STAMP)
values(UPPER(@tmpbus),UPPER(@tmpcompid),@maxseq,CO NVERT(Char, GetDate(), 101),isnull(@nameout,' '),
isnull(UPPER(@compout),' '),'E',isnull(@add1out,' '),isnull(@add2out,' '),isnull(@add3out,' '),' ',isnull(@tmpcity,' '),' ',
isnull(UPPER(@tmpstate),' '),isnull(UPPER(@zipout),' '),isnull(UPPER(@ctryout),' '),isnull(UPPER(@tel1out),' '),isnull(UPPER(@tel2out),' '),isnull(UPPER(@tel3out),' '),
isnull(@emailout,' '),@outstatus,isnull(@mgrout,' '),CONVERT(Char, GetDate(), 101))
end
end
end
Hi,
How to set "SET XACT_ABORT ON" Property in SQL SERVER GLobally, such that any connection to MS SQL SERVER has this property ON?
Note : By Default this property is OFF.
How can I set xact_abort to 'ON' on my server.
Thanks.
I am running update on huge table in batches. If I kill the process, will it roll back the last batch or the entire batches if I have used xact_abort on?
Thanks!
Is there any reason to use set xact_abort on OR set xact_abort off as arule in my stored procedures?
View 1 Replies View RelatedHow do you set SET XACT_ABORT to On in a c# stored procedure?
View 4 Replies View RelatedHi,
Anyone knows how to set the xact_abort attribute on in SSIS? I get an error message "Bulk Insert with another outstanding result set should be run with XACT_ABORT on" when I'm trying to enforce transactions in my package-my package uses distributed queries.
Thanks,
Lakshmi
I have my SQL7 server running on NT4 server. Currently the transaction log backups are on the same server but I want to back these up to a Win2000
server in another building. When creating a Backup Device in Enterprise Manager on the NT server it cannot see the drive but not the folder I have created on the Win2000 server, even though I have shared this folder out and set the permissions. Any help would be appreciated.. Thanks
Microsoft SQL Server Management Studio Express
select @@trancount
begin tran
select @@trancount
use ProdNetPerfMon
select @@trancount
update Nodes set Caption = 'xxxx' where Vendor = 'yyyy'
select @@trancount
commit tran
select @@trancount
Executes as expected including trancount without errors.
Nodes.Caption is updated but reverts after a few minutes.
sa privileges
What am I missing?
While troubleshooting a problem at one of our customers, we noticed the following consistent erratic behaviour of our product with the 1.1.105 JDBC driver (as well as the 1.2 CTP) when the XACT_ABORT user option has been set on the SQL server:
Set the user option XACT_ABORT on a database server with the following SQL query:
exec sp_configure 'user options', '16384'
reconfigure
Now run the following
Code Snippet
import java.sql.*;
import java.util.*;
public class Test {
public static void main(String[] args) {
try {
// Load the JDBC driver.
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
// Establish the connection to the database.
String url = "jdbc:sqlserver://server:1433";
Properties props = new Properties() ;
props.setProperty("user","user");
props.setProperty("password","password");
props.setProperty("DatabaseName","databasename");
props.setProperty("selectMethod","cursor");
Connection conn = DriverManager.getConnection(url, props);
// Set up simple prepared Update statement
PreparedStatement pstmt = conn.prepareStatement("UPDATE table SET field=0 where field=1", ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
Boolean isresultset = pstmt.execute();
System.out.println("IsUpdatecount? " + !(isresultset));
Integer updatedrecords=pstmt.getUpdateCount();
/*updatedrecords should be 0 or higher if isresultset is false
if updatedrecords=-1, the result should be a resultset, which it cannot be, but seems to be*/
System.out.println(updatedrecords+ " row(s) affected");
} catch (Exception e) {
System.err.println("Got an exception! ");
System.err.println(e.getMessage());
}
}
}
You can revert the database server back to the correct behavior bij executing the following SQL query
exec sp_configure 'user options', '0'
reconfigure
As you can see, when the SQL server user option XACT_ABORT is set, prepared UPDATE statements through the SQL JDBC driver will fail consistently when selectMethod=cursor and ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY are set, with the execute method indicating an UpdateCount is returned, but the UpdateCount itself claiming a resultset has been returned. On checking that, there is no resultset.
Somehow the option, which should do the following:
xact abort
Rolls back a transaction if a Transact-SQL statement raises a run-time error.
seems to roll back the results of an update statement in place ?
Is this a bug or something I seem to be doing wrong ?
Kind regards,
Niels
PS: With the 1.0 JDBC driver with this setting another problem occured, which pointed to a rollback done within a transaction without any exception being raised through JDBC, consistent with Angel Saenz-Badillos post on http://www.mombu.com/microsoft/sql-server-jdbc-driver/t-behavior-of-connectioncommit-249105.html
We had hoped the 1.1 driver would at least raise the exception through JDBC, but alas, the result mentioned above happened, which in the end we could track back to the server setting.
--Update--
When an SQL Trace is active on the server the following two error messages appear in the log, none of which are visible through the JDBC driver error handler:
Exception Error: 156, Severity: 15, State: 1
Exception Error: 16954, Severity: 10, State: 1
Before today when I run the package, everything is fine. Today when we use similiar database with small schema change, my package can not be run successfully. Always got '
Bulk Insert with another outstanding result set should be run with XACT_ABORT on
' error.
Any help will be appreciated. Thanks
All,
I have an SSIS package which calls several other SSIS packages. The "mother" package has TrasactionOption set to "Supported". There is a sequence container and an imbedded Execute Package Task for each of my embedded packages. Each of the sequence containers has TransactionOption = Required. DTC is running.
When I run my mother package I get the following error within the first package executed "Bulk Insert with another outstanding result set should be run with XACT_ABORT on." I've looked high and low for others with this problem, but haven't found any resolution. Can anyone tell me how to resolve this error? I have tried using BEGIN TRANSACTION instead of using Required and I turn on XACT_ABORT, but it doesn't seem to help either. Any help on this would be greatly appreciated.
Thanks
Hi ,
On my Desktop i registered Production Server in Enterprise Manager
on that Server if i go to SecurityLinked Servers
There is another Server is already mapped, when i am trying to see the Tables under that one of the
Linked Server i am getting the Error message saying that
"Error 17 SQL Server does not exist or access denied"
if i went to Production Server location and if i try to see the tables i am able to see properly, no problems
why i am not able to see from my Desk top
i am using the sa user while mapping the Production Server on my DESKTOP using (ENTERPRISE MANAGER)
And i check the Client Network Utility in the Alias using Named Pipe only, i changed to TCP/IP still same problem
What might the Problem how can i see the Tables in Linked Server from my DESKTOP
Thanks
I am using Linked Server in SQL Server 2008R2 connecting to a couple of Linked Servers.
I was able to connect Linked Servers, but I cannot point to a specific database in a Linked Server, also, I cannot rename Linked Server's name.
How to point the linked server to a specific database? How to rename the Linked Server?
The following is the code that I am using right now:
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
   @server = N'Machine123Instance456',
   @srvproduct=N'SQL Server' ;
GO
EXEC sp_addlinkedsrvlogin 'Machine123Instance456', 'false', NULL, 'username', 'password'Â Â
Are there any tips/techniques/issues when doing a begin tran and commit between a sql 2000 server/db and a sql 2005 server/db. Should you still use "set xact_abort on"? This will be a recordset of about 1-2000
View 1 Replies View RelatedIs there a way to bypass the syntax checking when adding a stored procedure via a script?
I have a script that has a LINKed server reference (see below) .
INSERT
INTO ACTDMSLINKED.ACTDMS.DBO.COILS ..etc.
ACTDMSLINKED does not exist at the time I need to add the stored procedure that references it.
PLEASE to not tell me to add the LINK and then run the script. This is not an option in this scenerio.
Thanks,
Terry
If there are two different transactions, both of which update the username column to 'xyz' for userid = 234 in 'Users' table. This is a unique value for username. Ater this update each transaction adds a row to 'AppLog' table. The transaction is only committed after second operation.
The 'username' column has a unique constraint on it.
If transaction isolation level is 'read committed', and both transaction execute the first operation when neither of the transactions have been committed, then the transaction that calls COMMIT later will error out or not? If COMMIT does not check constraints then it will NOT error out. As a result we will have a violation of unique constraint happening without any error being thrown by SQL Server.
We are seeing high number of hadr_sync_wait types on our server after setting up AOAG during peak times. We have setup sync type as synchronous commit and failover automatic. Can we change these settings to async and manual failover whenever we need and change them back to sync commit during off peak timings. Any drawbacks because of these changes ?
View 4 Replies View RelatedHello,
First of all, this is my first time using SQL SERVER 2005 express, before that i'm using POSTGRESQL database.
I would like to know how what's the equivalent command for "BEGIN","ROLLBACK","COMMIT", these are the POSTGRESQL COMMAND use to start transaction, rollback transaction and commit transaction.
Example when i use this kind of command is . I need to insert data into 3 table. before insert into table1, i issue "begin", start to insert data into table1, if table1 no error, then i proceed to table 2 and table3. if table2 and table3 no error. then issue "commit" to commit the changes. but if any error happen between table1 and table 2 or table 2 and table3, i will issue "rollback" to roll any changes that i make to table1, table2 and table3.
Maybe some one can teach me how to achieve using SQL SERVER 2005 EXPRESS.
Thanks and Regards.
Beh Chun Yit
I'm all of a sudden getting this error on a Stored Procedure that has not been touched since it was created.
Msg 266, Level 16, State 2, Procedure usp_ArchivexactControlPoint, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
CREATE PROCEDURE [dbo].[usp_ArchivexactControlPoint]
AS
DECLARE @TableName VARCHAR (50)
[Code] ...
I wnat to have a log file with all the updates and inserts made to the DB.
I understand I have to do a trans-log,how?
in order to make a query a transaction do I only have to declare trans...commit ??
tanx,
eyal
I have set up a simple trans rep from a server in the office to the web. both servers are NT4sp6aSQL 7.0. Tables only.
The publication and distribution db are on the server at the office, and there is a full time connection through the firewall.
The initialization and first replication works perfectly, but after that, there is a message from the snapshot agent that "no subscriptions needed initialization", the logreader says thare are no replicated transactions and the Distribution agent says there are no replicated transactions. What am I missing?
Thanks in advance
Does anyone know how to move the transaction log(s) of a LIVE database to a new location. I must move the log of a database to a new mirrored drive without any disruption to users. I cannot take the database offline or use the sp_detach_db stored procedure. Your inputs are much appreciated!
View 1 Replies View RelatedSQL7:
I have a 300MB db, and a transaction log near 1.3GB. Upon notification, I backed up the db log with truncate_only - no luck getting it smaller. Later, tried backup with no_log ( assuming the o/s was full - no diff)
I tried shinkfile (logfile,truncateonly) and no luck.
I tried dbcc opentrans to see if any pending trans. The db looks fine with dbcc checkdb. I managed to free up a mere 50MB. I checked the permissions on the db, and added backup db, and backup log in the db permissions for the user logged in (also tried this with sa)
I am unable to free up the space to the os. Can I somehow rid the log file and start off with a fresh log file? I need this space. As a patch I moved the log to a larger filesystem as a temporarily fix.
start/stop SQL- nothing? reboot -nothing? I played with waiting game. This log does not want to release space. The log grew from data loads.
Question1: Suggestions how to truncate this log? The contents are not really impt, but the space is.
Question2: Can I add another logfile, then use EMPTYFILE to transfer the contents to the newly added log file, then REMOVE The original logfile? In theory does this make sense?
Thanks,
Jason
Hello, During an intantanée arrest of replication of data base sql server 7 sp2 (nt4),
I lose keys on the subscriber, how don't have it? Thank you in advance easter
pascal
:confused:
Here's my situation (SQL2K)
We have a testing database we're using to convert large amounts of data from 1 system to another. We might process 5-6 million records, but don't care about being able to recover point-in-time.
I set recovery mode to simple, do a full backup every night. I keep getting large transaction logs. I manually run Shrink Database when I realize the logs are big
What can I do to prevent the logs from getting big in the first place ?? Can I prevent logging from happening ?
I keep reading various books and BOL, but I guess I don't quite "get it" yet ......
Any plain spoken, detailed suggestions would be very appreciated .... thanks in advance.
My database backs up just fine, but the transaction logs
are not. I created a maintenance plan for the database.
Any suggestions ?
Agent Job for transaction log backup
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 0A83F1BC-EEAA-
4BDF-9374-E7FF5C1215F8 -
Rpt "d:sqldataMSSQL$PRODUCTIONLOGzeon_live_commerce DB
Maintenance Plan16.txt" -DelTxtRpt 1WEEKS -VrfyBackup -
BkUpMedia DISK -BkUpLog -UseDefDir -DelBkUps 2DAYS -
CrBkSubDir -BkExt "TRN"'
The error log file for the transaction log backups is...
Microsoft (R) SQLMaint Utility (Unicode), Version Logged
on to SQL Server 'ZCHQ_SQLPRODUCTION' as 'ZCISQLSERVICE'
(trusted)
Starting maintenance plan 'zeon_live_commerce DB
Maintenance Plan1' on 6/12/2002 8:15:03 AM
Backup can not be performed on
database 'zeon_live_commerce'. This sub task is ignored.
Deleting old text reports... 1 file(s) deleted.
End of maintenance plan 'zeon_live_commerce DB Maintenance
Plan1' on 6/12/2002 8:15:03 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
If you are set up for AutoCommit why would you or should you set a explicit transaction? I have noticed that in some called stored procudures from a "container" stored procedure. (Hope I got that right) that in the called stored procedure a Begin tran is used. Can anyone help with the why and what fors? It seems to me that you want to let SQL Server handle this becuase of the danger of leaving out a Commit or Rollback? But thats me. I may be very wrong? Thanks.
Tom
Hi,
I was just wondering, if I have a DB for log shipping, and change the recovery model to bulk-insert before I do a dbreindex, does the log still grow as big as the full recovery model? as in when the DB in bulk-insert model, will dbreindex still writes to the log? What i'm trying to do is try to make the log files smaller for log ship when i'm doing the db reindexing job.
Thanks.