Within a store procedure I would like to pump up the isolation level to
serializable just for one particular transaction and restore it back to the
previous level when it's done even if there is a fatal error, would someone
know how to do that? Thank you very much
Here is what I thought the implemenation/body of my store procedure should
be but I see problems
--STORE PROCEDURE --
--If I set transaction isolation level here before the transaction begins,
it will stay there through out the life of the connection, right? I don't
want that.
BEGIN TRANSACTION
-- If I set transaction isolation level here, it might not be
applicable for this transaction, right?
-- DOING SOME QUERYING AND UPDATING HERE
COMMIT TRANSACTION
-- If I restore the isolation level here, there are 2 issues? 1) How to read
it and restore it 2) If my update fails,this will never get executed
Zeng
You can set the isolation level back to READ COMMITTED:
SET TRANSACTION ISOLATION SERIALIZABLE
BEGIN TRANSACTION
... trap errors!
If errors,
ROLLBACK
else
COMMIT
SET TRANSACTION ISOLATION READ COMMITTED
RETURN
You can read the current isolation level with DBCC USEROPTIONS, but you only
see the isolation level if it's been changed once.
Hope this helps,
Ron
Ron Talmage
SQL Server MVP
"Zeng" <zzy@.nonospam.com> wrote in message
news:%23uQxIXh7EHA.4040@.TK2MSFTNGP14.phx.gbl...
> Within a store procedure I would like to pump up the isolation level to
> serializable just for one particular transaction and restore it back to
the
> previous level when it's done even if there is a fatal error, would
someone
> know how to do that? Thank you very much
> Here is what I thought the implemenation/body of my store procedure should
> be but I see problems
>
> --STORE PROCEDURE --
> --If I set transaction isolation level here before the transaction begins,
> it will stay there through out the life of the connection, right? I don't
> want that.
> BEGIN TRANSACTION
> -- If I set transaction isolation level here, it might not be
> applicable for this transaction, right?
> -- DOING SOME QUERYING AND UPDATING HERE
> COMMIT TRANSACTION
> -- If I restore the isolation level here, there are 2 issues? 1) How to
read
> it and restore it 2) If my update fails,this will never get executed
>
|||That won't work well for me because:
1) Fatal error doesn't go through the trap
2) Read Committed is not always what my store proc might start out with.
"Ron Talmage" <rtalmage@.prospice.com> wrote in message
news:e%23Do%23jh7EHA.2192@.TK2MSFTNGP14.phx.gbl...
> Zeng
> You can set the isolation level back to READ COMMITTED:
> SET TRANSACTION ISOLATION SERIALIZABLE
> BEGIN TRANSACTION
> ... trap errors!
> If errors,
> ROLLBACK
> else
> COMMIT
> SET TRANSACTION ISOLATION READ COMMITTED
> RETURN
> You can read the current isolation level with DBCC USEROPTIONS, but you
only[vbcol=seagreen]
> see the isolation level if it's been changed once.
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "Zeng" <zzy@.nonospam.com> wrote in message
> news:%23uQxIXh7EHA.4040@.TK2MSFTNGP14.phx.gbl...
> the
> someone
should[vbcol=seagreen]
begins,[vbcol=seagreen]
don't
> read
>
|||Easy: simply add the statement With HoldLock to your Select query that
you want to be Serializable.
S. L.
"Zeng" <zzy@.nonospam.com> wrote in message
news:%23uQxIXh7EHA.4040@.TK2MSFTNGP14.phx.gbl...
> Within a store procedure I would like to pump up the isolation level to
> serializable just for one particular transaction and restore it back to
> the
> previous level when it's done even if there is a fatal error, would
> someone
> know how to do that? Thank you very much
> Here is what I thought the implemenation/body of my store procedure should
> be but I see problems
>
> --STORE PROCEDURE --
> --If I set transaction isolation level here before the transaction begins,
> it will stay there through out the life of the connection, right? I don't
> want that.
> BEGIN TRANSACTION
> -- If I set transaction isolation level here, it might not be
> applicable for this transaction, right?
> -- DOING SOME QUERYING AND UPDATING HERE
> COMMIT TRANSACTION
> -- If I restore the isolation level here, there are 2 issues? 1) How to
> read
> it and restore it 2) If my update fails,this will never get executed
>
|||Zeng,
There's nothing you can do about the fatal errors that cancel an entire
batch. For fatal errors that simply abort the procedure, you can set up
wrapper code around the call to the proc that will restore the former
isolation level.
Here's one possible way:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
DECLARE @.SetValue varchar(100)
CREATE TABLE #useroptions (SetOption varchar(100), SetValue varchar(100))
INSERT #useroptions
EXEC ('DBCC USEROPTIONS')
SET @.SetValue = (SELECT SetValue FROM #useroptions WHERE SetOption =
'isolation level')
DROP TABLE #useroptions
SELECT @.SetValue
-- Simulates a call to a procedure, that changes the isolation level
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
-- Now back in the calling code:
IF @.SetValue IS NULL OR @.SetValue = 'read committed'
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
IF @.SetValue = 'read uncommitted'
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
IF @.SetValue = 'serializable'
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
IF @.SetValue = 'repeatable read'
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
Ron
Ron Talmage
SQL Server MVP
"Zeng" <zzy@.nonospam.com> wrote in message
news:%23E597oh7EHA.2552@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> That won't work well for me because:
> 1) Fatal error doesn't go through the trap
> 2) Read Committed is not always what my store proc might start out with.
>
> "Ron Talmage" <rtalmage@.prospice.com> wrote in message
> news:e%23Do%23jh7EHA.2192@.TK2MSFTNGP14.phx.gbl...
> only
to[vbcol=seagreen]
to[vbcol=seagreen]
> should
PROCEDURE --[vbcol=seagreen]
> begins,
> don't
to
>
|||SET commands doesn't stick after the proc execution, so you only need to restore the isolation level
if you have further statements *inside* the procedure that you want to restore isolation level for.
Evidence:
USE tempdb
GO
CREATE PROC p
AS
DBCC USEROPTIONS
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
DBCC USEROPTIONS
GO
EXEC p
DBCC USEROPTIONS
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Zeng" <zzy@.nonospam.com> wrote in message news:%23E597oh7EHA.2552@.TK2MSFTNGP09.phx.gbl...
> That won't work well for me because:
> 1) Fatal error doesn't go through the trap
> 2) Read Committed is not always what my store proc might start out with.
>
> "Ron Talmage" <rtalmage@.prospice.com> wrote in message
> news:e%23Do%23jh7EHA.2192@.TK2MSFTNGP14.phx.gbl...
> only
> should
> begins,
> don't
>
Showing posts with label level. Show all posts
Showing posts with label level. Show all posts
Wednesday, March 28, 2012
restore transaction isolation level
restore transaction isolation level
Within a store procedure I would like to pump up the isolation level to
serializable just for one particular transaction and restore it back to the
previous level when it's done even if there is a fatal error, would someone
know how to do that? Thank you very much
Here is what I thought the implemenation/body of my store procedure should
be but I see problems
--STORE PROCEDURE --
--If I set transaction isolation level here before the transaction begins,
it will stay there through out the life of the connection, right? I don't
want that.
BEGIN TRANSACTION
-- If I set transaction isolation level here, it might not be
applicable for this transaction, right?
-- DOING SOME QUERYING AND UPDATING HERE
COMMIT TRANSACTION
-- If I restore the isolation level here, there are 2 issues? 1) How to read
it and restore it 2) If my update fails,this will never get executedZeng
You can set the isolation level back to READ COMMITTED:
SET TRANSACTION ISOLATION SERIALIZABLE
BEGIN TRANSACTION
... trap errors!
If errors,
ROLLBACK
else
COMMIT
SET TRANSACTION ISOLATION READ COMMITTED
RETURN
You can read the current isolation level with DBCC USEROPTIONS, but you only
see the isolation level if it's been changed once.
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Zeng" <zzy@.nonospam.com> wrote in message
news:%23uQxIXh7EHA.4040@.TK2MSFTNGP14.phx.gbl...
> Within a store procedure I would like to pump up the isolation level to
> serializable just for one particular transaction and restore it back to
the
> previous level when it's done even if there is a fatal error, would
someone
> know how to do that? Thank you very much
> Here is what I thought the implemenation/body of my store procedure should
> be but I see problems
>
> --STORE PROCEDURE --
> --If I set transaction isolation level here before the transaction begins,
> it will stay there through out the life of the connection, right? I don't
> want that.
> BEGIN TRANSACTION
> -- If I set transaction isolation level here, it might not be
> applicable for this transaction, right?
> -- DOING SOME QUERYING AND UPDATING HERE
> COMMIT TRANSACTION
> -- If I restore the isolation level here, there are 2 issues? 1) How to
read
> it and restore it 2) If my update fails,this will never get executed
>|||That won't work well for me because:
1) Fatal error doesn't go through the trap
2) Read Committed is not always what my store proc might start out with.
"Ron Talmage" <rtalmage@.prospice.com> wrote in message
news:e%23Do%23jh7EHA.2192@.TK2MSFTNGP14.phx.gbl...
> Zeng
> You can set the isolation level back to READ COMMITTED:
> SET TRANSACTION ISOLATION SERIALIZABLE
> BEGIN TRANSACTION
> ... trap errors!
> If errors,
> ROLLBACK
> else
> COMMIT
> SET TRANSACTION ISOLATION READ COMMITTED
> RETURN
> You can read the current isolation level with DBCC USEROPTIONS, but you
only
> see the isolation level if it's been changed once.
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "Zeng" <zzy@.nonospam.com> wrote in message
> news:%23uQxIXh7EHA.4040@.TK2MSFTNGP14.phx.gbl...
> >
> > Within a store procedure I would like to pump up the isolation level to
> > serializable just for one particular transaction and restore it back to
> the
> > previous level when it's done even if there is a fatal error, would
> someone
> > know how to do that? Thank you very much
> >
> > Here is what I thought the implemenation/body of my store procedure
should
> > be but I see problems
> >
> >
> > --STORE PROCEDURE --
> >
> > --If I set transaction isolation level here before the transaction
begins,
> > it will stay there through out the life of the connection, right? I
don't
> > want that.
> >
> > BEGIN TRANSACTION
> > -- If I set transaction isolation level here, it might not be
> > applicable for this transaction, right?
> >
> > -- DOING SOME QUERYING AND UPDATING HERE
> >
> > COMMIT TRANSACTION
> >
> > -- If I restore the isolation level here, there are 2 issues? 1) How to
> read
> > it and restore it 2) If my update fails,this will never get executed
> >
> >
>|||Easy: simply add the statement « With HoldLock » to your Select query that
you want to be Serializable.
S. L.
"Zeng" <zzy@.nonospam.com> wrote in message
news:%23uQxIXh7EHA.4040@.TK2MSFTNGP14.phx.gbl...
> Within a store procedure I would like to pump up the isolation level to
> serializable just for one particular transaction and restore it back to
> the
> previous level when it's done even if there is a fatal error, would
> someone
> know how to do that? Thank you very much
> Here is what I thought the implemenation/body of my store procedure should
> be but I see problems
>
> --STORE PROCEDURE --
> --If I set transaction isolation level here before the transaction begins,
> it will stay there through out the life of the connection, right? I don't
> want that.
> BEGIN TRANSACTION
> -- If I set transaction isolation level here, it might not be
> applicable for this transaction, right?
> -- DOING SOME QUERYING AND UPDATING HERE
> COMMIT TRANSACTION
> -- If I restore the isolation level here, there are 2 issues? 1) How to
> read
> it and restore it 2) If my update fails,this will never get executed
>|||Zeng,
There's nothing you can do about the fatal errors that cancel an entire
batch. For fatal errors that simply abort the procedure, you can set up
wrapper code around the call to the proc that will restore the former
isolation level.
Here's one possible way:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
DECLARE @.SetValue varchar(100)
CREATE TABLE #useroptions (SetOption varchar(100), SetValue varchar(100))
INSERT #useroptions
EXEC ('DBCC USEROPTIONS')
SET @.SetValue = (SELECT SetValue FROM #useroptions WHERE SetOption ='isolation level')
DROP TABLE #useroptions
SELECT @.SetValue
-- Simulates a call to a procedure, that changes the isolation level
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
-- Now back in the calling code:
IF @.SetValue IS NULL OR @.SetValue = 'read committed'
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
IF @.SetValue = 'read uncommitted'
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
IF @.SetValue = 'serializable'
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
IF @.SetValue = 'repeatable read'
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
Ron
--
Ron Talmage
SQL Server MVP
"Zeng" <zzy@.nonospam.com> wrote in message
news:%23E597oh7EHA.2552@.TK2MSFTNGP09.phx.gbl...
> That won't work well for me because:
> 1) Fatal error doesn't go through the trap
> 2) Read Committed is not always what my store proc might start out with.
>
> "Ron Talmage" <rtalmage@.prospice.com> wrote in message
> news:e%23Do%23jh7EHA.2192@.TK2MSFTNGP14.phx.gbl...
> > Zeng
> >
> > You can set the isolation level back to READ COMMITTED:
> >
> > SET TRANSACTION ISOLATION SERIALIZABLE
> > BEGIN TRANSACTION
> > ... trap errors!
> >
> > If errors,
> > ROLLBACK
> > else
> > COMMIT
> >
> > SET TRANSACTION ISOLATION READ COMMITTED
> > RETURN
> >
> > You can read the current isolation level with DBCC USEROPTIONS, but you
> only
> > see the isolation level if it's been changed once.
> >
> > Hope this helps,
> > Ron
> > --
> > Ron Talmage
> > SQL Server MVP
> >
> > "Zeng" <zzy@.nonospam.com> wrote in message
> > news:%23uQxIXh7EHA.4040@.TK2MSFTNGP14.phx.gbl...
> > >
> > > Within a store procedure I would like to pump up the isolation level
to
> > > serializable just for one particular transaction and restore it back
to
> > the
> > > previous level when it's done even if there is a fatal error, would
> > someone
> > > know how to do that? Thank you very much
> > >
> > > Here is what I thought the implemenation/body of my store procedure
> should
> > > be but I see problems
> > >
> > >
> > > --STORE
PROCEDURE --
> > >
> > > --If I set transaction isolation level here before the transaction
> begins,
> > > it will stay there through out the life of the connection, right? I
> don't
> > > want that.
> > >
> > > BEGIN TRANSACTION
> > > -- If I set transaction isolation level here, it might not be
> > > applicable for this transaction, right?
> > >
> > > -- DOING SOME QUERYING AND UPDATING HERE
> > >
> > > COMMIT TRANSACTION
> > >
> > > -- If I restore the isolation level here, there are 2 issues? 1) How
to
> > read
> > > it and restore it 2) If my update fails,this will never get executed
> > >
> > >
> >
> >
>|||SET commands doesn't stick after the proc execution, so you only need to restore the isolation level
if you have further statements *inside* the procedure that you want to restore isolation level for.
Evidence:
USE tempdb
GO
CREATE PROC p
AS
DBCC USEROPTIONS
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
DBCC USEROPTIONS
GO
EXEC p
DBCC USEROPTIONS
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Zeng" <zzy@.nonospam.com> wrote in message news:%23E597oh7EHA.2552@.TK2MSFTNGP09.phx.gbl...
> That won't work well for me because:
> 1) Fatal error doesn't go through the trap
> 2) Read Committed is not always what my store proc might start out with.
>
> "Ron Talmage" <rtalmage@.prospice.com> wrote in message
> news:e%23Do%23jh7EHA.2192@.TK2MSFTNGP14.phx.gbl...
>> Zeng
>> You can set the isolation level back to READ COMMITTED:
>> SET TRANSACTION ISOLATION SERIALIZABLE
>> BEGIN TRANSACTION
>> ... trap errors!
>> If errors,
>> ROLLBACK
>> else
>> COMMIT
>> SET TRANSACTION ISOLATION READ COMMITTED
>> RETURN
>> You can read the current isolation level with DBCC USEROPTIONS, but you
> only
>> see the isolation level if it's been changed once.
>> Hope this helps,
>> Ron
>> --
>> Ron Talmage
>> SQL Server MVP
>> "Zeng" <zzy@.nonospam.com> wrote in message
>> news:%23uQxIXh7EHA.4040@.TK2MSFTNGP14.phx.gbl...
>> >
>> > Within a store procedure I would like to pump up the isolation level to
>> > serializable just for one particular transaction and restore it back to
>> the
>> > previous level when it's done even if there is a fatal error, would
>> someone
>> > know how to do that? Thank you very much
>> >
>> > Here is what I thought the implemenation/body of my store procedure
> should
>> > be but I see problems
>> >
>> >
>> > --STORE PROCEDURE --
>> >
>> > --If I set transaction isolation level here before the transaction
> begins,
>> > it will stay there through out the life of the connection, right? I
> don't
>> > want that.
>> >
>> > BEGIN TRANSACTION
>> > -- If I set transaction isolation level here, it might not be
>> > applicable for this transaction, right?
>> >
>> > -- DOING SOME QUERYING AND UPDATING HERE
>> >
>> > COMMIT TRANSACTION
>> >
>> > -- If I restore the isolation level here, there are 2 issues? 1) How to
>> read
>> > it and restore it 2) If my update fails,this will never get executed
>> >
>> >
>>
>sql
serializable just for one particular transaction and restore it back to the
previous level when it's done even if there is a fatal error, would someone
know how to do that? Thank you very much
Here is what I thought the implemenation/body of my store procedure should
be but I see problems
--STORE PROCEDURE --
--If I set transaction isolation level here before the transaction begins,
it will stay there through out the life of the connection, right? I don't
want that.
BEGIN TRANSACTION
-- If I set transaction isolation level here, it might not be
applicable for this transaction, right?
-- DOING SOME QUERYING AND UPDATING HERE
COMMIT TRANSACTION
-- If I restore the isolation level here, there are 2 issues? 1) How to read
it and restore it 2) If my update fails,this will never get executedZeng
You can set the isolation level back to READ COMMITTED:
SET TRANSACTION ISOLATION SERIALIZABLE
BEGIN TRANSACTION
... trap errors!
If errors,
ROLLBACK
else
COMMIT
SET TRANSACTION ISOLATION READ COMMITTED
RETURN
You can read the current isolation level with DBCC USEROPTIONS, but you only
see the isolation level if it's been changed once.
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Zeng" <zzy@.nonospam.com> wrote in message
news:%23uQxIXh7EHA.4040@.TK2MSFTNGP14.phx.gbl...
> Within a store procedure I would like to pump up the isolation level to
> serializable just for one particular transaction and restore it back to
the
> previous level when it's done even if there is a fatal error, would
someone
> know how to do that? Thank you very much
> Here is what I thought the implemenation/body of my store procedure should
> be but I see problems
>
> --STORE PROCEDURE --
> --If I set transaction isolation level here before the transaction begins,
> it will stay there through out the life of the connection, right? I don't
> want that.
> BEGIN TRANSACTION
> -- If I set transaction isolation level here, it might not be
> applicable for this transaction, right?
> -- DOING SOME QUERYING AND UPDATING HERE
> COMMIT TRANSACTION
> -- If I restore the isolation level here, there are 2 issues? 1) How to
read
> it and restore it 2) If my update fails,this will never get executed
>|||That won't work well for me because:
1) Fatal error doesn't go through the trap
2) Read Committed is not always what my store proc might start out with.
"Ron Talmage" <rtalmage@.prospice.com> wrote in message
news:e%23Do%23jh7EHA.2192@.TK2MSFTNGP14.phx.gbl...
> Zeng
> You can set the isolation level back to READ COMMITTED:
> SET TRANSACTION ISOLATION SERIALIZABLE
> BEGIN TRANSACTION
> ... trap errors!
> If errors,
> ROLLBACK
> else
> COMMIT
> SET TRANSACTION ISOLATION READ COMMITTED
> RETURN
> You can read the current isolation level with DBCC USEROPTIONS, but you
only
> see the isolation level if it's been changed once.
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "Zeng" <zzy@.nonospam.com> wrote in message
> news:%23uQxIXh7EHA.4040@.TK2MSFTNGP14.phx.gbl...
> >
> > Within a store procedure I would like to pump up the isolation level to
> > serializable just for one particular transaction and restore it back to
> the
> > previous level when it's done even if there is a fatal error, would
> someone
> > know how to do that? Thank you very much
> >
> > Here is what I thought the implemenation/body of my store procedure
should
> > be but I see problems
> >
> >
> > --STORE PROCEDURE --
> >
> > --If I set transaction isolation level here before the transaction
begins,
> > it will stay there through out the life of the connection, right? I
don't
> > want that.
> >
> > BEGIN TRANSACTION
> > -- If I set transaction isolation level here, it might not be
> > applicable for this transaction, right?
> >
> > -- DOING SOME QUERYING AND UPDATING HERE
> >
> > COMMIT TRANSACTION
> >
> > -- If I restore the isolation level here, there are 2 issues? 1) How to
> read
> > it and restore it 2) If my update fails,this will never get executed
> >
> >
>|||Easy: simply add the statement « With HoldLock » to your Select query that
you want to be Serializable.
S. L.
"Zeng" <zzy@.nonospam.com> wrote in message
news:%23uQxIXh7EHA.4040@.TK2MSFTNGP14.phx.gbl...
> Within a store procedure I would like to pump up the isolation level to
> serializable just for one particular transaction and restore it back to
> the
> previous level when it's done even if there is a fatal error, would
> someone
> know how to do that? Thank you very much
> Here is what I thought the implemenation/body of my store procedure should
> be but I see problems
>
> --STORE PROCEDURE --
> --If I set transaction isolation level here before the transaction begins,
> it will stay there through out the life of the connection, right? I don't
> want that.
> BEGIN TRANSACTION
> -- If I set transaction isolation level here, it might not be
> applicable for this transaction, right?
> -- DOING SOME QUERYING AND UPDATING HERE
> COMMIT TRANSACTION
> -- If I restore the isolation level here, there are 2 issues? 1) How to
> read
> it and restore it 2) If my update fails,this will never get executed
>|||Zeng,
There's nothing you can do about the fatal errors that cancel an entire
batch. For fatal errors that simply abort the procedure, you can set up
wrapper code around the call to the proc that will restore the former
isolation level.
Here's one possible way:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
DECLARE @.SetValue varchar(100)
CREATE TABLE #useroptions (SetOption varchar(100), SetValue varchar(100))
INSERT #useroptions
EXEC ('DBCC USEROPTIONS')
SET @.SetValue = (SELECT SetValue FROM #useroptions WHERE SetOption ='isolation level')
DROP TABLE #useroptions
SELECT @.SetValue
-- Simulates a call to a procedure, that changes the isolation level
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
-- Now back in the calling code:
IF @.SetValue IS NULL OR @.SetValue = 'read committed'
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
IF @.SetValue = 'read uncommitted'
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
IF @.SetValue = 'serializable'
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
IF @.SetValue = 'repeatable read'
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
Ron
--
Ron Talmage
SQL Server MVP
"Zeng" <zzy@.nonospam.com> wrote in message
news:%23E597oh7EHA.2552@.TK2MSFTNGP09.phx.gbl...
> That won't work well for me because:
> 1) Fatal error doesn't go through the trap
> 2) Read Committed is not always what my store proc might start out with.
>
> "Ron Talmage" <rtalmage@.prospice.com> wrote in message
> news:e%23Do%23jh7EHA.2192@.TK2MSFTNGP14.phx.gbl...
> > Zeng
> >
> > You can set the isolation level back to READ COMMITTED:
> >
> > SET TRANSACTION ISOLATION SERIALIZABLE
> > BEGIN TRANSACTION
> > ... trap errors!
> >
> > If errors,
> > ROLLBACK
> > else
> > COMMIT
> >
> > SET TRANSACTION ISOLATION READ COMMITTED
> > RETURN
> >
> > You can read the current isolation level with DBCC USEROPTIONS, but you
> only
> > see the isolation level if it's been changed once.
> >
> > Hope this helps,
> > Ron
> > --
> > Ron Talmage
> > SQL Server MVP
> >
> > "Zeng" <zzy@.nonospam.com> wrote in message
> > news:%23uQxIXh7EHA.4040@.TK2MSFTNGP14.phx.gbl...
> > >
> > > Within a store procedure I would like to pump up the isolation level
to
> > > serializable just for one particular transaction and restore it back
to
> > the
> > > previous level when it's done even if there is a fatal error, would
> > someone
> > > know how to do that? Thank you very much
> > >
> > > Here is what I thought the implemenation/body of my store procedure
> should
> > > be but I see problems
> > >
> > >
> > > --STORE
PROCEDURE --
> > >
> > > --If I set transaction isolation level here before the transaction
> begins,
> > > it will stay there through out the life of the connection, right? I
> don't
> > > want that.
> > >
> > > BEGIN TRANSACTION
> > > -- If I set transaction isolation level here, it might not be
> > > applicable for this transaction, right?
> > >
> > > -- DOING SOME QUERYING AND UPDATING HERE
> > >
> > > COMMIT TRANSACTION
> > >
> > > -- If I restore the isolation level here, there are 2 issues? 1) How
to
> > read
> > > it and restore it 2) If my update fails,this will never get executed
> > >
> > >
> >
> >
>|||SET commands doesn't stick after the proc execution, so you only need to restore the isolation level
if you have further statements *inside* the procedure that you want to restore isolation level for.
Evidence:
USE tempdb
GO
CREATE PROC p
AS
DBCC USEROPTIONS
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
DBCC USEROPTIONS
GO
EXEC p
DBCC USEROPTIONS
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Zeng" <zzy@.nonospam.com> wrote in message news:%23E597oh7EHA.2552@.TK2MSFTNGP09.phx.gbl...
> That won't work well for me because:
> 1) Fatal error doesn't go through the trap
> 2) Read Committed is not always what my store proc might start out with.
>
> "Ron Talmage" <rtalmage@.prospice.com> wrote in message
> news:e%23Do%23jh7EHA.2192@.TK2MSFTNGP14.phx.gbl...
>> Zeng
>> You can set the isolation level back to READ COMMITTED:
>> SET TRANSACTION ISOLATION SERIALIZABLE
>> BEGIN TRANSACTION
>> ... trap errors!
>> If errors,
>> ROLLBACK
>> else
>> COMMIT
>> SET TRANSACTION ISOLATION READ COMMITTED
>> RETURN
>> You can read the current isolation level with DBCC USEROPTIONS, but you
> only
>> see the isolation level if it's been changed once.
>> Hope this helps,
>> Ron
>> --
>> Ron Talmage
>> SQL Server MVP
>> "Zeng" <zzy@.nonospam.com> wrote in message
>> news:%23uQxIXh7EHA.4040@.TK2MSFTNGP14.phx.gbl...
>> >
>> > Within a store procedure I would like to pump up the isolation level to
>> > serializable just for one particular transaction and restore it back to
>> the
>> > previous level when it's done even if there is a fatal error, would
>> someone
>> > know how to do that? Thank you very much
>> >
>> > Here is what I thought the implemenation/body of my store procedure
> should
>> > be but I see problems
>> >
>> >
>> > --STORE PROCEDURE --
>> >
>> > --If I set transaction isolation level here before the transaction
> begins,
>> > it will stay there through out the life of the connection, right? I
> don't
>> > want that.
>> >
>> > BEGIN TRANSACTION
>> > -- If I set transaction isolation level here, it might not be
>> > applicable for this transaction, right?
>> >
>> > -- DOING SOME QUERYING AND UPDATING HERE
>> >
>> > COMMIT TRANSACTION
>> >
>> > -- If I restore the isolation level here, there are 2 issues? 1) How to
>> read
>> > it and restore it 2) If my update fails,this will never get executed
>> >
>> >
>>
>sql
restore transaction isolation level
Within a store procedure I would like to pump up the isolation level to
serializable just for one particular transaction and restore it back to the
previous level when it's done even if there is a fatal error, would someone
know how to do that? Thank you very much
Here is what I thought the implemenation/body of my store procedure should
be but I see problems
--STORE PROCEDURE --
--If I set transaction isolation level here before the transaction begins,
it will stay there through out the life of the connection, right? I don't
want that.
BEGIN TRANSACTION
-- If I set transaction isolation level here, it might not be
applicable for this transaction, right?
-- DOING SOME QUERYING AND UPDATING HERE
COMMIT TRANSACTION
-- If I restore the isolation level here, there are 2 issues? 1) How to read
it and restore it 2) If my update fails,this will never get executedZeng
You can set the isolation level back to READ COMMITTED:
SET TRANSACTION ISOLATION SERIALIZABLE
BEGIN TRANSACTION
... trap errors!
If errors,
ROLLBACK
else
COMMIT
SET TRANSACTION ISOLATION READ COMMITTED
RETURN
You can read the current isolation level with DBCC USEROPTIONS, but you only
see the isolation level if it's been changed once.
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Zeng" <zzy@.nonospam.com> wrote in message
news:%23uQxIXh7EHA.4040@.TK2MSFTNGP14.phx.gbl...
> Within a store procedure I would like to pump up the isolation level to
> serializable just for one particular transaction and restore it back to
the
> previous level when it's done even if there is a fatal error, would
someone
> know how to do that? Thank you very much
> Here is what I thought the implemenation/body of my store procedure should
> be but I see problems
>
> --STORE PROCEDURE --
> --If I set transaction isolation level here before the transaction begins,
> it will stay there through out the life of the connection, right? I don't
> want that.
> BEGIN TRANSACTION
> -- If I set transaction isolation level here, it might not be
> applicable for this transaction, right?
> -- DOING SOME QUERYING AND UPDATING HERE
> COMMIT TRANSACTION
> -- If I restore the isolation level here, there are 2 issues? 1) How to
read
> it and restore it 2) If my update fails,this will never get executed
>|||That won't work well for me because:
1) Fatal error doesn't go through the trap
2) Read Committed is not always what my store proc might start out with.
"Ron Talmage" <rtalmage@.prospice.com> wrote in message
news:e%23Do%23jh7EHA.2192@.TK2MSFTNGP14.phx.gbl...
> Zeng
> You can set the isolation level back to READ COMMITTED:
> SET TRANSACTION ISOLATION SERIALIZABLE
> BEGIN TRANSACTION
> ... trap errors!
> If errors,
> ROLLBACK
> else
> COMMIT
> SET TRANSACTION ISOLATION READ COMMITTED
> RETURN
> You can read the current isolation level with DBCC USEROPTIONS, but you
only
> see the isolation level if it's been changed once.
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "Zeng" <zzy@.nonospam.com> wrote in message
> news:%23uQxIXh7EHA.4040@.TK2MSFTNGP14.phx.gbl...
> the
> someone
should[vbcol=seagreen]
begins,[vbcol=seagreen]
don't[vbcol=seagreen]
> read
>|||Easy: simply add the statement With HoldLock to your Select query that
you want to be Serializable.
S. L.
"Zeng" <zzy@.nonospam.com> wrote in message
news:%23uQxIXh7EHA.4040@.TK2MSFTNGP14.phx.gbl...
> Within a store procedure I would like to pump up the isolation level to
> serializable just for one particular transaction and restore it back to
> the
> previous level when it's done even if there is a fatal error, would
> someone
> know how to do that? Thank you very much
> Here is what I thought the implemenation/body of my store procedure should
> be but I see problems
>
> --STORE PROCEDURE --
> --If I set transaction isolation level here before the transaction begins,
> it will stay there through out the life of the connection, right? I don't
> want that.
> BEGIN TRANSACTION
> -- If I set transaction isolation level here, it might not be
> applicable for this transaction, right?
> -- DOING SOME QUERYING AND UPDATING HERE
> COMMIT TRANSACTION
> -- If I restore the isolation level here, there are 2 issues? 1) How to
> read
> it and restore it 2) If my update fails,this will never get executed
>|||Zeng,
There's nothing you can do about the fatal errors that cancel an entire
batch. For fatal errors that simply abort the procedure, you can set up
wrapper code around the call to the proc that will restore the former
isolation level.
Here's one possible way:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
DECLARE @.SetValue varchar(100)
CREATE TABLE #useroptions (SetOption varchar(100), SetValue varchar(100))
INSERT #useroptions
EXEC ('DBCC USEROPTIONS')
SET @.SetValue = (SELECT SetValue FROM #useroptions WHERE SetOption =
'isolation level')
DROP TABLE #useroptions
SELECT @.SetValue
-- Simulates a call to a procedure, that changes the isolation level
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
-- Now back in the calling code:
IF @.SetValue IS NULL OR @.SetValue = 'read committed'
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
IF @.SetValue = 'read uncommitted'
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
IF @.SetValue = 'serializable'
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
IF @.SetValue = 'repeatable read'
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
Ron
--
Ron Talmage
SQL Server MVP
"Zeng" <zzy@.nonospam.com> wrote in message
news:%23E597oh7EHA.2552@.TK2MSFTNGP09.phx.gbl...
> That won't work well for me because:
> 1) Fatal error doesn't go through the trap
> 2) Read Committed is not always what my store proc might start out with.
>
> "Ron Talmage" <rtalmage@.prospice.com> wrote in message
> news:e%23Do%23jh7EHA.2192@.TK2MSFTNGP14.phx.gbl...
> only
to[vbcol=seagreen]
to[vbcol=seagreen]
> should
PROCEDURE --[vbcol=seagreen]
> begins,
> don't
to[vbcol=seagreen]
>|||SET commands doesn't stick after the proc execution, so you only need to res
tore the isolation level
if you have further statements *inside* the procedure that you want to resto
re isolation level for.
Evidence:
USE tempdb
GO
CREATE PROC p
AS
DBCC USEROPTIONS
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
DBCC USEROPTIONS
GO
EXEC p
DBCC USEROPTIONS
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Zeng" <zzy@.nonospam.com> wrote in message news:%23E597oh7EHA.2552@.TK2MSFTNGP09.phx.gbl...[v
bcol=seagreen]
> That won't work well for me because:
> 1) Fatal error doesn't go through the trap
> 2) Read Committed is not always what my store proc might start out with.
>
> "Ron Talmage" <rtalmage@.prospice.com> wrote in message
> news:e%23Do%23jh7EHA.2192@.TK2MSFTNGP14.phx.gbl...
> only
> should
> begins,
> don't
>[/vbcol]
serializable just for one particular transaction and restore it back to the
previous level when it's done even if there is a fatal error, would someone
know how to do that? Thank you very much
Here is what I thought the implemenation/body of my store procedure should
be but I see problems
--STORE PROCEDURE --
--If I set transaction isolation level here before the transaction begins,
it will stay there through out the life of the connection, right? I don't
want that.
BEGIN TRANSACTION
-- If I set transaction isolation level here, it might not be
applicable for this transaction, right?
-- DOING SOME QUERYING AND UPDATING HERE
COMMIT TRANSACTION
-- If I restore the isolation level here, there are 2 issues? 1) How to read
it and restore it 2) If my update fails,this will never get executedZeng
You can set the isolation level back to READ COMMITTED:
SET TRANSACTION ISOLATION SERIALIZABLE
BEGIN TRANSACTION
... trap errors!
If errors,
ROLLBACK
else
COMMIT
SET TRANSACTION ISOLATION READ COMMITTED
RETURN
You can read the current isolation level with DBCC USEROPTIONS, but you only
see the isolation level if it's been changed once.
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Zeng" <zzy@.nonospam.com> wrote in message
news:%23uQxIXh7EHA.4040@.TK2MSFTNGP14.phx.gbl...
> Within a store procedure I would like to pump up the isolation level to
> serializable just for one particular transaction and restore it back to
the
> previous level when it's done even if there is a fatal error, would
someone
> know how to do that? Thank you very much
> Here is what I thought the implemenation/body of my store procedure should
> be but I see problems
>
> --STORE PROCEDURE --
> --If I set transaction isolation level here before the transaction begins,
> it will stay there through out the life of the connection, right? I don't
> want that.
> BEGIN TRANSACTION
> -- If I set transaction isolation level here, it might not be
> applicable for this transaction, right?
> -- DOING SOME QUERYING AND UPDATING HERE
> COMMIT TRANSACTION
> -- If I restore the isolation level here, there are 2 issues? 1) How to
read
> it and restore it 2) If my update fails,this will never get executed
>|||That won't work well for me because:
1) Fatal error doesn't go through the trap
2) Read Committed is not always what my store proc might start out with.
"Ron Talmage" <rtalmage@.prospice.com> wrote in message
news:e%23Do%23jh7EHA.2192@.TK2MSFTNGP14.phx.gbl...
> Zeng
> You can set the isolation level back to READ COMMITTED:
> SET TRANSACTION ISOLATION SERIALIZABLE
> BEGIN TRANSACTION
> ... trap errors!
> If errors,
> ROLLBACK
> else
> COMMIT
> SET TRANSACTION ISOLATION READ COMMITTED
> RETURN
> You can read the current isolation level with DBCC USEROPTIONS, but you
only
> see the isolation level if it's been changed once.
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "Zeng" <zzy@.nonospam.com> wrote in message
> news:%23uQxIXh7EHA.4040@.TK2MSFTNGP14.phx.gbl...
> the
> someone
should[vbcol=seagreen]
begins,[vbcol=seagreen]
don't[vbcol=seagreen]
> read
>|||Easy: simply add the statement With HoldLock to your Select query that
you want to be Serializable.
S. L.
"Zeng" <zzy@.nonospam.com> wrote in message
news:%23uQxIXh7EHA.4040@.TK2MSFTNGP14.phx.gbl...
> Within a store procedure I would like to pump up the isolation level to
> serializable just for one particular transaction and restore it back to
> the
> previous level when it's done even if there is a fatal error, would
> someone
> know how to do that? Thank you very much
> Here is what I thought the implemenation/body of my store procedure should
> be but I see problems
>
> --STORE PROCEDURE --
> --If I set transaction isolation level here before the transaction begins,
> it will stay there through out the life of the connection, right? I don't
> want that.
> BEGIN TRANSACTION
> -- If I set transaction isolation level here, it might not be
> applicable for this transaction, right?
> -- DOING SOME QUERYING AND UPDATING HERE
> COMMIT TRANSACTION
> -- If I restore the isolation level here, there are 2 issues? 1) How to
> read
> it and restore it 2) If my update fails,this will never get executed
>|||Zeng,
There's nothing you can do about the fatal errors that cancel an entire
batch. For fatal errors that simply abort the procedure, you can set up
wrapper code around the call to the proc that will restore the former
isolation level.
Here's one possible way:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
DECLARE @.SetValue varchar(100)
CREATE TABLE #useroptions (SetOption varchar(100), SetValue varchar(100))
INSERT #useroptions
EXEC ('DBCC USEROPTIONS')
SET @.SetValue = (SELECT SetValue FROM #useroptions WHERE SetOption =
'isolation level')
DROP TABLE #useroptions
SELECT @.SetValue
-- Simulates a call to a procedure, that changes the isolation level
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
-- Now back in the calling code:
IF @.SetValue IS NULL OR @.SetValue = 'read committed'
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
IF @.SetValue = 'read uncommitted'
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
IF @.SetValue = 'serializable'
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
IF @.SetValue = 'repeatable read'
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
Ron
--
Ron Talmage
SQL Server MVP
"Zeng" <zzy@.nonospam.com> wrote in message
news:%23E597oh7EHA.2552@.TK2MSFTNGP09.phx.gbl...
> That won't work well for me because:
> 1) Fatal error doesn't go through the trap
> 2) Read Committed is not always what my store proc might start out with.
>
> "Ron Talmage" <rtalmage@.prospice.com> wrote in message
> news:e%23Do%23jh7EHA.2192@.TK2MSFTNGP14.phx.gbl...
> only
to[vbcol=seagreen]
to[vbcol=seagreen]
> should
PROCEDURE --[vbcol=seagreen]
> begins,
> don't
to[vbcol=seagreen]
>|||SET commands doesn't stick after the proc execution, so you only need to res
tore the isolation level
if you have further statements *inside* the procedure that you want to resto
re isolation level for.
Evidence:
USE tempdb
GO
CREATE PROC p
AS
DBCC USEROPTIONS
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
DBCC USEROPTIONS
GO
EXEC p
DBCC USEROPTIONS
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Zeng" <zzy@.nonospam.com> wrote in message news:%23E597oh7EHA.2552@.TK2MSFTNGP09.phx.gbl...[v
bcol=seagreen]
> That won't work well for me because:
> 1) Fatal error doesn't go through the trap
> 2) Read Committed is not always what my store proc might start out with.
>
> "Ron Talmage" <rtalmage@.prospice.com> wrote in message
> news:e%23Do%23jh7EHA.2192@.TK2MSFTNGP14.phx.gbl...
> only
> should
> begins,
> don't
>[/vbcol]
Friday, March 23, 2012
RESTORE sqlserver2005DB to sqlserver2000
Hi all,
Does the compatibility level of sqlserver2005 affects the transfering
of the 2005DB to sqlserver2000?
Our 2005 database has a compatibility level 80.And our sqlserver2000
has the same compatibility level.While transfering database(from2005 to
2000), do we need to handle this compatibility?
Thanks,
Mukut
Tibor Karaszi wrote:
> No, the database is physically upgraded when you restore 2000 into 2005, and there is no physical
> downgrade feature. I assume you here mean backup/restore or detach/attach.
Hi Tibor,
Truely, our requirement is to take a back up of sqlserver2005 DB and
restore it to sqlserver2000. We are getting error as
follows:Error3169:The backed-up database has on-disk structure
version<..>.The server support version 539 and can not restore or
upgrade this database.RESTORE FILELIST is terminating abnormally.
We could not resolve it.We are not sure whether compatibility or
something else is the problem or it is technically impossible to
restore back up of sqlserver2005 DB into sqlserver2000.
Tibor,could u pls suggest us what should we do in order to restore,if
possible, sqlserver2005DB into sqlserver2000 ? Does Microsoft suggest
anything in this regard?
Thanks,
Mukut
|||You won't be able to restore a 2005 backup onto a 2000 server. The
best you'll be able to do is to either set up a DTS transfer or export
the data to a flat file. You might check out whether BCP inSQL 2005
outputs data in a format readable by SQL 2000.
On 1 Nov 2006 13:38:20 -0800, "Mukut" <hidevraj@.gmail.com> wrote:
>Tibor Karaszi wrote:
>Hi Tibor,
>Truely, our requirement is to take a back up of sqlserver2005 DB and
>restore it to sqlserver2000. We are getting error as
>follows:Error3169:The backed-up database has on-disk structure
>version<..>.The server support version 539 and can not restore or
>upgrade this database.RESTORE FILELIST is terminating abnormally.
>We could not resolve it.We are not sure whether compatibility or
>something else is the problem or it is technically impossible to
>restore back up of sqlserver2005 DB into sqlserver2000.
>Tibor,could u pls suggest us what should we do in order to restore,if
>possible, sqlserver2005DB into sqlserver2000 ? Does Microsoft suggest
>anything in this regard?
>Thanks,
>Mukut
sql
Does the compatibility level of sqlserver2005 affects the transfering
of the 2005DB to sqlserver2000?
Our 2005 database has a compatibility level 80.And our sqlserver2000
has the same compatibility level.While transfering database(from2005 to
2000), do we need to handle this compatibility?
Thanks,
Mukut
Tibor Karaszi wrote:
> No, the database is physically upgraded when you restore 2000 into 2005, and there is no physical
> downgrade feature. I assume you here mean backup/restore or detach/attach.
Hi Tibor,
Truely, our requirement is to take a back up of sqlserver2005 DB and
restore it to sqlserver2000. We are getting error as
follows:Error3169:The backed-up database has on-disk structure
version<..>.The server support version 539 and can not restore or
upgrade this database.RESTORE FILELIST is terminating abnormally.
We could not resolve it.We are not sure whether compatibility or
something else is the problem or it is technically impossible to
restore back up of sqlserver2005 DB into sqlserver2000.
Tibor,could u pls suggest us what should we do in order to restore,if
possible, sqlserver2005DB into sqlserver2000 ? Does Microsoft suggest
anything in this regard?
Thanks,
Mukut
|||You won't be able to restore a 2005 backup onto a 2000 server. The
best you'll be able to do is to either set up a DTS transfer or export
the data to a flat file. You might check out whether BCP inSQL 2005
outputs data in a format readable by SQL 2000.
On 1 Nov 2006 13:38:20 -0800, "Mukut" <hidevraj@.gmail.com> wrote:
>Tibor Karaszi wrote:
>Hi Tibor,
>Truely, our requirement is to take a back up of sqlserver2005 DB and
>restore it to sqlserver2000. We are getting error as
>follows:Error3169:The backed-up database has on-disk structure
>version<..>.The server support version 539 and can not restore or
>upgrade this database.RESTORE FILELIST is terminating abnormally.
>We could not resolve it.We are not sure whether compatibility or
>something else is the problem or it is technically impossible to
>restore back up of sqlserver2005 DB into sqlserver2000.
>Tibor,could u pls suggest us what should we do in order to restore,if
>possible, sqlserver2005DB into sqlserver2000 ? Does Microsoft suggest
>anything in this regard?
>Thanks,
>Mukut
sql
Labels:
2005db,
affects,
compatibility,
database,
level,
microsoft,
mysql,
oracle,
restore,
server,
sql,
sqlserver2000,
sqlserver2000our,
sqlserver2005,
sqlserver2005db,
transferingof
RESTORE sqlserver2005DB to sqlserver2000
Hi all,
Does the compatibility level of sqlserver2005 affects the transfering
of the 2005DB to sqlserver2000?
Our 2005 database has a compatibility level 80.And our sqlserver2000
has the same compatibility level.While transfering database(from2005 to
2000), do we need to handle this compatibility?
Thanks,
Mukut> Does the compatibility level of sqlserver2005 affects the transfering
> of the 2005DB to sqlserver2000?
No, the database is physically upgraded when you restore 2000 into 2005, and there is no physical
downgrade feature. I assume you here mean backup/restore or detach/attach.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mukut" <hidevraj@.gmail.com> wrote in message
news:1162378083.366854.21150@.k70g2000cwa.googlegroups.com...
> Hi all,
> Does the compatibility level of sqlserver2005 affects the transfering
> of the 2005DB to sqlserver2000?
> Our 2005 database has a compatibility level 80.And our sqlserver2000
> has the same compatibility level.While transfering database(from2005 to
> 2000), do we need to handle this compatibility?
> Thanks,
> Mukut
>|||Tibor Karaszi wrote:
> > Does the compatibility level of sqlserver2005 affects the transfering
> > of the 2005DB to sqlserver2000?
> No, the database is physically upgraded when you restore 2000 into 2005, and there is no physical
> downgrade feature. I assume you here mean backup/restore or detach/attach.
Hi Tibor,
Truely, our requirement is to take a back up of sqlserver2005 DB and
restore it to sqlserver2000. We are getting error as
follows:Error3169:The backed-up database has on-disk structure
version<..>.The server support version 539 and can not restore or
upgrade this database.RESTORE FILELIST is terminating abnormally.
We could not resolve it.We are not sure whether compatibility or
something else is the problem or it is technically impossible to
restore back up of sqlserver2005 DB into sqlserver2000.
Tibor,could u pls suggest us what should we do in order to restore,if
possible, sqlserver2005DB into sqlserver2000 ? Does Microsoft suggest
anything in this regard?
Thanks,
Mukut|||You won't be able to restore a 2005 backup onto a 2000 server. The
best you'll be able to do is to either set up a DTS transfer or export
the data to a flat file. You might check out whether BCP inSQL 2005
outputs data in a format readable by SQL 2000.
On 1 Nov 2006 13:38:20 -0800, "Mukut" <hidevraj@.gmail.com> wrote:
>Tibor Karaszi wrote:
>> > Does the compatibility level of sqlserver2005 affects the transfering
>> > of the 2005DB to sqlserver2000?
>> No, the database is physically upgraded when you restore 2000 into 2005, and there is no physical
>> downgrade feature. I assume you here mean backup/restore or detach/attach.
>Hi Tibor,
>Truely, our requirement is to take a back up of sqlserver2005 DB and
>restore it to sqlserver2000. We are getting error as
>follows:Error3169:The backed-up database has on-disk structure
>version<..>.The server support version 539 and can not restore or
>upgrade this database.RESTORE FILELIST is terminating abnormally.
>We could not resolve it.We are not sure whether compatibility or
>something else is the problem or it is technically impossible to
>restore back up of sqlserver2005 DB into sqlserver2000.
>Tibor,could u pls suggest us what should we do in order to restore,if
>possible, sqlserver2005DB into sqlserver2000 ? Does Microsoft suggest
>anything in this regard?
>Thanks,
>Mukut
Does the compatibility level of sqlserver2005 affects the transfering
of the 2005DB to sqlserver2000?
Our 2005 database has a compatibility level 80.And our sqlserver2000
has the same compatibility level.While transfering database(from2005 to
2000), do we need to handle this compatibility?
Thanks,
Mukut> Does the compatibility level of sqlserver2005 affects the transfering
> of the 2005DB to sqlserver2000?
No, the database is physically upgraded when you restore 2000 into 2005, and there is no physical
downgrade feature. I assume you here mean backup/restore or detach/attach.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mukut" <hidevraj@.gmail.com> wrote in message
news:1162378083.366854.21150@.k70g2000cwa.googlegroups.com...
> Hi all,
> Does the compatibility level of sqlserver2005 affects the transfering
> of the 2005DB to sqlserver2000?
> Our 2005 database has a compatibility level 80.And our sqlserver2000
> has the same compatibility level.While transfering database(from2005 to
> 2000), do we need to handle this compatibility?
> Thanks,
> Mukut
>|||Tibor Karaszi wrote:
> > Does the compatibility level of sqlserver2005 affects the transfering
> > of the 2005DB to sqlserver2000?
> No, the database is physically upgraded when you restore 2000 into 2005, and there is no physical
> downgrade feature. I assume you here mean backup/restore or detach/attach.
Hi Tibor,
Truely, our requirement is to take a back up of sqlserver2005 DB and
restore it to sqlserver2000. We are getting error as
follows:Error3169:The backed-up database has on-disk structure
version<..>.The server support version 539 and can not restore or
upgrade this database.RESTORE FILELIST is terminating abnormally.
We could not resolve it.We are not sure whether compatibility or
something else is the problem or it is technically impossible to
restore back up of sqlserver2005 DB into sqlserver2000.
Tibor,could u pls suggest us what should we do in order to restore,if
possible, sqlserver2005DB into sqlserver2000 ? Does Microsoft suggest
anything in this regard?
Thanks,
Mukut|||You won't be able to restore a 2005 backup onto a 2000 server. The
best you'll be able to do is to either set up a DTS transfer or export
the data to a flat file. You might check out whether BCP inSQL 2005
outputs data in a format readable by SQL 2000.
On 1 Nov 2006 13:38:20 -0800, "Mukut" <hidevraj@.gmail.com> wrote:
>Tibor Karaszi wrote:
>> > Does the compatibility level of sqlserver2005 affects the transfering
>> > of the 2005DB to sqlserver2000?
>> No, the database is physically upgraded when you restore 2000 into 2005, and there is no physical
>> downgrade feature. I assume you here mean backup/restore or detach/attach.
>Hi Tibor,
>Truely, our requirement is to take a back up of sqlserver2005 DB and
>restore it to sqlserver2000. We are getting error as
>follows:Error3169:The backed-up database has on-disk structure
>version<..>.The server support version 539 and can not restore or
>upgrade this database.RESTORE FILELIST is terminating abnormally.
>We could not resolve it.We are not sure whether compatibility or
>something else is the problem or it is technically impossible to
>restore back up of sqlserver2005 DB into sqlserver2000.
>Tibor,could u pls suggest us what should we do in order to restore,if
>possible, sqlserver2005DB into sqlserver2000 ? Does Microsoft suggest
>anything in this regard?
>Thanks,
>Mukut
Labels:
2005db,
affects,
compatibility,
database,
level,
microsoft,
mysql,
oracle,
restore,
server,
sql,
sqlserver2000,
sqlserver2005,
sqlserver2005db,
transfering
RESTORE sqlserver2005DB to sqlserver2000
Hi all,
Does the compatibility level of sqlserver2005 affects the transfering
of the 2005DB to sqlserver2000?
Our 2005 database has a compatibility level 80.And our sqlserver2000
has the same compatibility level.While transfering database(from2005 to
2000), do we need to handle this compatibility?
Thanks,
Mukut> Does the compatibility level of sqlserver2005 affects the transfering
> of the 2005DB to sqlserver2000?
No, the database is physically upgraded when you restore 2000 into 2005, and
there is no physical
downgrade feature. I assume you here mean backup/restore or detach/attach.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mukut" <hidevraj@.gmail.com> wrote in message
news:1162378083.366854.21150@.k70g2000cwa.googlegroups.com...
> Hi all,
> Does the compatibility level of sqlserver2005 affects the transfering
> of the 2005DB to sqlserver2000?
> Our 2005 database has a compatibility level 80.And our sqlserver2000
> has the same compatibility level.While transfering database(from2005 to
> 2000), do we need to handle this compatibility?
> Thanks,
> Mukut
>|||Tibor Karaszi wrote:
> No, the database is physically upgraded when you restore 2000 into 2005, a
nd there is no physical
> downgrade feature. I assume you here mean backup/restore or detach/attach.
Hi Tibor,
Truely, our requirement is to take a back up of sqlserver2005 DB and
restore it to sqlserver2000. We are getting error as
follows:Error3169:The backed-up database has on-disk structure
version<..>.The server support version 539 and can not restore or
upgrade this database.RESTORE FILELIST is terminating abnormally.
We could not resolve it.We are not sure whether compatibility or
something else is the problem or it is technically impossible to
restore back up of sqlserver2005 DB into sqlserver2000.
Tibor,could u pls suggest us what should we do in order to restore,if
possible, sqlserver2005DB into sqlserver2000 ? Does Microsoft suggest
anything in this regard?
Thanks,
Mukut|||You won't be able to restore a 2005 backup onto a 2000 server. The
best you'll be able to do is to either set up a DTS transfer or export
the data to a flat file. You might check out whether BCP inSQL 2005
outputs data in a format readable by SQL 2000.
On 1 Nov 2006 13:38:20 -0800, "Mukut" <hidevraj@.gmail.com> wrote:
>Tibor Karaszi wrote:
>Hi Tibor,
>Truely, our requirement is to take a back up of sqlserver2005 DB and
>restore it to sqlserver2000. We are getting error as
>follows:Error3169:The backed-up database has on-disk structure
>version<..>.The server support version 539 and can not restore or
>upgrade this database.RESTORE FILELIST is terminating abnormally.
>We could not resolve it.We are not sure whether compatibility or
>something else is the problem or it is technically impossible to
>restore back up of sqlserver2005 DB into sqlserver2000.
>Tibor,could u pls suggest us what should we do in order to restore,if
>possible, sqlserver2005DB into sqlserver2000 ? Does Microsoft suggest
>anything in this regard?
>Thanks,
>Mukut
Does the compatibility level of sqlserver2005 affects the transfering
of the 2005DB to sqlserver2000?
Our 2005 database has a compatibility level 80.And our sqlserver2000
has the same compatibility level.While transfering database(from2005 to
2000), do we need to handle this compatibility?
Thanks,
Mukut> Does the compatibility level of sqlserver2005 affects the transfering
> of the 2005DB to sqlserver2000?
No, the database is physically upgraded when you restore 2000 into 2005, and
there is no physical
downgrade feature. I assume you here mean backup/restore or detach/attach.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mukut" <hidevraj@.gmail.com> wrote in message
news:1162378083.366854.21150@.k70g2000cwa.googlegroups.com...
> Hi all,
> Does the compatibility level of sqlserver2005 affects the transfering
> of the 2005DB to sqlserver2000?
> Our 2005 database has a compatibility level 80.And our sqlserver2000
> has the same compatibility level.While transfering database(from2005 to
> 2000), do we need to handle this compatibility?
> Thanks,
> Mukut
>|||Tibor Karaszi wrote:
> No, the database is physically upgraded when you restore 2000 into 2005, a
nd there is no physical
> downgrade feature. I assume you here mean backup/restore or detach/attach.
Hi Tibor,
Truely, our requirement is to take a back up of sqlserver2005 DB and
restore it to sqlserver2000. We are getting error as
follows:Error3169:The backed-up database has on-disk structure
version<..>.The server support version 539 and can not restore or
upgrade this database.RESTORE FILELIST is terminating abnormally.
We could not resolve it.We are not sure whether compatibility or
something else is the problem or it is technically impossible to
restore back up of sqlserver2005 DB into sqlserver2000.
Tibor,could u pls suggest us what should we do in order to restore,if
possible, sqlserver2005DB into sqlserver2000 ? Does Microsoft suggest
anything in this regard?
Thanks,
Mukut|||You won't be able to restore a 2005 backup onto a 2000 server. The
best you'll be able to do is to either set up a DTS transfer or export
the data to a flat file. You might check out whether BCP inSQL 2005
outputs data in a format readable by SQL 2000.
On 1 Nov 2006 13:38:20 -0800, "Mukut" <hidevraj@.gmail.com> wrote:
>Tibor Karaszi wrote:
>Hi Tibor,
>Truely, our requirement is to take a back up of sqlserver2005 DB and
>restore it to sqlserver2000. We are getting error as
>follows:Error3169:The backed-up database has on-disk structure
>version<..>.The server support version 539 and can not restore or
>upgrade this database.RESTORE FILELIST is terminating abnormally.
>We could not resolve it.We are not sure whether compatibility or
>something else is the problem or it is technically impossible to
>restore back up of sqlserver2005 DB into sqlserver2000.
>Tibor,could u pls suggest us what should we do in order to restore,if
>possible, sqlserver2005DB into sqlserver2000 ? Does Microsoft suggest
>anything in this regard?
>Thanks,
>Mukut
Labels:
2005db,
affects,
compatibility,
database,
level,
microsoft,
mysql,
oracle,
restore,
server,
sql,
sqlserver2000,
sqlserver2000our,
sqlserver2005,
sqlserver2005db,
transferingof
Subscribe to:
Posts (Atom)