SET TRANSACTION ISOLATION LEVEL for VIEW

[jacob94]

Is it possible to SET TRANSACTION ISOLATION LEVEL in a view or only a stored procedure? If so, please show me the CREATE VIEW AS statement with isoloation level to read UNCOMMITTED.

Thanks!

 

[ptheriault]

Here is the example right out of BOL.

USE AdventureWorks
GO
SET TRANSACTION ISOLATION LEVEL [COLOR=red]Your Level [/color];
GO
BEGIN TRANSACTION;
SELECT EmployeeID
    FROM HumanResources.Employee;
GO

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[SQLDenis]

I don;t recommend this because of dirty, phantom and repeatable reads but here you go anyway

the nolock hint is the same as uncommited transactional level
create view abc
as
select * from table1 with (nolock)
union all
select * from table2 with (nolock)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[mrdenny]

The example that Paul gives sets the isolation level on the transaction which creates the view, not on the actual view it self.

To set the isolation level within a view you will need to use the nolock table hint that SQLDenis has shown.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[jacob94]

Thanks for the feedback. Paul, I saw the example you posted, but it does not let you save it as a view.

The nolock hint is the way I will have to go however my initial view is rather complex or at least with my knowledge of sql it seems to be. Not comparing myself you all the sql wizes on here!

My question is this. I have the complicated view joining three tables, one of which is on another sql server. I am using the FROM OPENQUERY method to pass the sql to the other linked server sql database. The I am using a stored procedure which is fired from a trigger that call on the “complicated view” for accessing data. Can I simply put the WITH (NOLOCKS) statement after the FROM in the SP that is calling the view or do I need to do this somehow in the “complicated view three times? I am not sure the firing sequence. I assume it is SP, then call the view but I may be wrong.

Please let me know and thanks for all the help. If necessary I will post the “complicated” view next.

Jacob

 

[mrdenny]

You should put the NOLOCK hints within the view.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[jacob94]

Ok, how do you do this when you are joining three tables one of which uses FROM OPENQUERY?

Also, the reason I can to this conclusion was databases locks and blocks that kept occuring. Will the No Locks resolve all database blocks resulting from this transaction?

 

[mrdenny]

The Query will look something like this.

select a.Col1, b.Col2, c.Col3
from Table1 with a (nolock)
join Table2 with b (nolock) on a.Col5 = b.Col5
join OPENQUERY('select * from table with (nolock)'...) c on a.col5 = c.Col5

Yes this should stop you from blocking other users.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[jacob94]

mrdenny:
Thanks, the NOLOCKS eliminated the locking/blocking issues but affected some other database processess on the SQL server.

My trigger is firing 100K times and appending data each time to a linked sql server. Other software running on the sql server that i have no control over was getting async errors etc...maybe resulting from the WITH(NOLOCKS) change. Is there a better way to move data to another table on another server via a trigger that would put less load on the system? What are some other methods, like fire a DTS package from a trigger (would this manage the load better). Any ideas would be great?

 

[mrdenny]

The WITH (NOLOCKS) won't effect anything but those three tables that you are dealing with. All you are doing is doing dirty reads. What are the errors that you are getting from the other app? Is that app using the same tables that the view is using?

How does the view relate to the trigger?

Launching a DTS package from a trigger is a very bad idea. The trigger can't complete until you have shelled out to the OS via xp_cmdshell and the package has completed. This also requires giving all users access to run xp_cmdshell.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[jacob94]

Let me get back to this after we explore this question:

Please see this thread...
thread183-1323079

I am very confused and could really use your advise...

In my linked server set up, I can execute a
INSERT INTO linkedserver.dbname.dbo.test from query analyizer with no problem from server a to server b.

when i try to put this on a FOR INSERT trigger
INSERT INTO LINKEDSERVER TABLE
FROM INSERTED etc...for every insertered record, it hangs the database and nothing gets inserted...

This seems impossible because I can do this same command from QA minus the FOR INSERTED command. I checked both server's MS DTC Services and they seem fine and permit me to do the insert in QA...This seems crazy...

Any ideas on shipping the data over on a FOR INSERT trigger to another SQL db or why my setup is failing???