Tagged Questions

2
votes
2answers
69 views

How to rollback the identity seed after deadlock

Now that's an approximate sequence of operations Im performing: SET IDENTITY_INSERT <table-name> ON; INSERT SOMETHING to <table-name> with explicitly specifyed id DECLARE @oldID bigint ...
1
vote
0answers
32 views

Does SQL Server place shared locks on scanned records when using REPEATABLE READ [duplicate]

Assume a SQLCmd session which is using transaction isolation level REPEATABLE READ. In this session I start a transaction and execute an UPDATE statement with a WHERE clause on a non indexed column. ...
0
votes
1answer
91 views

Query notifications and connection options

I'm investigating a problem with query notifications. The platform is SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Standard Edition. .NET errors show a problem reported from SqlNotificationInfo ...
3
votes
1answer
183 views

Trigger in combination with transaction

Suppose we have the following situation: We have a table (let's say Table_A), wich has a trigger on INSERT. The trigger job is to update some rows in table_B based on the inserted values in table_A. ...
0
votes
1answer
147 views

Does TRUNCATE TABLE affects isolation level in Sql Server?

As I am working on the project which has SQL SERVER as a database....I'm maintaining isolation level in my project where I enabled snapshot isolation level for my project.... As I have to make some ...
1
vote
1answer
43 views

Does a transaction cover the subroutines?

When I begin transaction and exec a stored procedure, does that transaction cover the changes made by the stored procedure I executed? Would a rollback transaction cancel the changes made by the ...
1
vote
1answer
110 views

Commit Insert but Hold Lock

I have implemented what is effectively a reliable queue through the following: CREATE TABLE Queue (MessageID UNIQUEIDENTIFIER, Message varchar(255)) --Enqueue Command DECLARE @MessageID ...
1
vote
1answer
216 views

SQL Server - how transactions and transaction log work (simplified)

I have a theory on how transactions work, but I would like if someone could verify it or correct some points if possible. Let's consider we have a database with full recovery model. Now, everything ...
3
votes
2answers
324 views

Transaction and Try-catch in SQL Server Job

We have DML operations in each step of a SQL Server job. To ensure the update/insert will be rolled back in case something goes wrong, I have wrapped the data modifications of each step in TRY CATCH ...
9
votes
1answer
606 views

SQL query for combinations without repetition

I need a query which can be used in (or as) a function and retrieves all combinations of n values. And I need all combinations of length k where k = 1..n. Extended sample input and result so input ...
2
votes
1answer
94 views

Transaction level difference between using a large IN filter VS. “BEGIN TRAN/COMMIT”

I would like to delete about 100,000 records with minimal server overhead. I've had a few nagging questions I haven't been able to test properly so I figured I'd ask some experts here. Which would ...
4
votes
3answers
230 views

Can I change table structure in a transaction and then roll it back if there is an error?

I have some ALTER TABLE statements that I am running. Not all of them work (they are the result of running SQL Data Compare) and I want to group them in some transactions and roll back the statements ...
3
votes
1answer
135 views

SQL Server : KEY lock prevents FK on nearby row, but not SELECT [closed]

I have the following table structure (simplified, pseudocode): TABLE dbo.Users (Id int NOT NULL IDENTITY PRIMARY KEY) TABLE dbo.Transfers ( Id int NOT NULL IDENTITY PRIMARY KEY, ...
3
votes
2answers
1k views

Will a delete then insert within a transaction cascade?

I had a question about what the expected and actual behaviour for the following scenario is. The scenario is one where a database table ([table1]) is cleared and reloaded every day. The table has an ...
0
votes
0answers
54 views

Biztalk + BI = where? [closed]

I have heard that you can use Biztalk in to transfer data to the Business intelligence. Exactly where do you put the Biztalk's integration in relation to the architecture of Business Intelligence?
3
votes
3answers
309 views

Why am i getting deadlocks with 1 read and 1 write thread in SQL

if anyone could please help me understand why the following transactions deadlock? I am providing the 2 transactions and the indexes for each table: Transaction 1: SELECT blockId, blockData, ...
1
vote
2answers
135 views

INSERT statements in a transaction and locking a range of rows

I have a table that is concurrently accessed by many users. Typically, INSERTs to the table are made one at a time. Occassionaly, users will add a "batch" of rows at the same time. The batch is ...
5
votes
1answer
159 views

Triggers and the Transaction

We recently made a change to create constraints based on logic previously used in stored procedures, and part of that included the use of INSTEAD OF triggers to centralize logic. The logic is ...
2
votes
2answers
1k views

What server property makes TransactionScope escalating to MSDTC?

In our application we're using TransactionScope's. We're aiming to not use the MSDTC service because it's a lot slower than lightweight transactions. using (var transactionScope = new ...
5
votes
1answer
146 views

Does the transaction isolation level affect indices as well?

Here's one for the SQL Server gurus: if I set my SQL Server 2008 transaction isolation level to READ UNCOMMITTED, does that also affect the index pages? E.g. using ISOLATION LEVEL READ UNCOMMITTED, ...
0
votes
1answer
1k views

how to make a table unlocked within transaction block

I am using SQL Server 2008. I have several stored procedures that process different reports. Each stored procedure makes use of Begin Transaction' and 'Commit Transaction, using the default ...
1
vote
2answers
667 views

SQL Server 2008 R2 - How to check Foreign Key constraints in a transaction only when committed?

We are synchronizing tables between two databases (yes, it needs to be done by our software and not by replication etc.) Both databases are identical with many foreign-key constraints on tables. ...
3
votes
1answer
187 views

Does blocking always mean open transaction?

This may seem a bit dull question but does blocking always mean that there is open transaction and that may cause transaction log grow to infinity because open transaction prevents log truncation ...
5
votes
3answers
1k views

SQL Server - what isolation level for non-blocking select statements?

I have a long running transaction (called, say, T1) that performs some deletes, updates and inserts on a table in SQL Server 2008 R2. At the same time, another process periodically runs select ...
11
votes
3answers
7k views

What risks are there if we enable read committed snapshot in sql-server?

I have read here that some extra data will be stored per row so we might see a performance degradation but what other risks are there? eg. Will this affect recovery of the database? Is there anything ...
3
votes
1answer
178 views

Is SQL Server Replication transactionally 'safe' across all published tables?

We have a system that has one of its datasources still on SQL Server 2000. In order to achieve higher availability, we are looking to setup transactional replication from this 2000 server to a 2008 ...
9
votes
1answer
9k views

How to find out who deleted some data SQL Server

My boss had a query from a customer yesterday asking how they could find out who deleted some data in their SQL Server database (it is the express edition if that matters). I thought this could be ...
5
votes
3answers
2k views

Will the transaction log shrink automagically in SQL Server?

When SQL Server database in a SIMPLE mode, you don't have to care about the transaction log bakcups. But in a SIMPLE mode, the transaction log seems to grow as it does in FULL mode. Does is truncate ...
2
votes
1answer
356 views

SQL Server Logon Trigger causing problems with the Distributed Transaction Coordinator

I've created the following simple logon trigger on my SQL Server 2005: CREATE TRIGGER LOGON_RESTRICTION ON ALL SERVER FOR LOGON AS BEGIN PRINT 'Hello World' END GO Which seems to be working fine ...
1
vote
2answers
850 views

Distributed Transactions between SQL Server 2000 & MySQL Stopped Working

We have been successfully copying data from a MySQL Slave database into a SQL Server 2000 database. The MySQL server is a linked server. I have tried using v3.51 and v5.1.8 of the ODBC connector ...
3
votes
1answer
813 views

Why is this rollback needed when using sp_addextendedproperty in a stored procedure?

In enter link description here I showed, how I document databases. To insert the extended properties, initially I uses plan sequences of sp_addextendedproperty calls. But lately I wanted to catch ...