MSDTC and Win2K3: sp1

Showing posts with label sp1. Show all posts

Friday, March 23, 2012

MSOLAP Connection error

Hi,
I have RS with sp1 in one server and MS Analysis server with sp3 in another.
Both inside of Domain and the user is the administrator. Also I have the
correct roles in the AS.
I got this error:
An error has occurred during report processing.
Cannot create a connection to data source 'Sales_MDX_Colinas23'. Database
'Sales' does not exist.
In the dev. env works fine and also if AS is in the same server as RS.
The problem is when AS and RS are in different server.
Does some one knows whats wrong?
Thanks in advanceDid you get a solution for this. I have the same problem?
"Carlos Lara" wrote:
> Hi,
> I have RS with sp1 in one server and MS Analysis server with sp3 in another.
> Both inside of Domain and the user is the administrator. Also I have the
> correct roles in the AS.
> I got this error:
> An error has occurred during report processing.
> Cannot create a connection to data source 'Sales_MDX_Colinas23'. Database
> 'Sales' does not exist.
> In the dev. env works fine and also if AS is in the same server as RS.
> The problem is when AS and RS are in different server.
> Does some one knows whats wrong?
> Thanks in advance
>|||Yes, I am also having the same problem and noticed that Jane has also posted
the same problem under "Database connection problem".
Can some please help.
Cheers.
"Ramdas" wrote:
> Did you get a solution for this. I have the same problem?
> "Carlos Lara" wrote:
> > Hi,
> > I have RS with sp1 in one server and MS Analysis server with sp3 in another.
> > Both inside of Domain and the user is the administrator. Also I have the
> > correct roles in the AS.
> >
> > I got this error:
> > An error has occurred during report processing.
> > Cannot create a connection to data source 'Sales_MDX_Colinas23'. Database
> > 'Sales' does not exist.
> >
> > In the dev. env works fine and also if AS is in the same server as RS.
> >
> > The problem is when AS and RS are in different server.
> >
> > Does some one knows whats wrong?
> >
> > Thanks in advance
> >
> >|||Same problem here as well. Any luck?
Cheers,
"Kelvin" wrote:
> Yes, I am also having the same problem and noticed that Jane has also posted
> the same problem under "Database connection problem".
> Can some please help.
> Cheers.
> "Ramdas" wrote:
> > Did you get a solution for this. I have the same problem?
> >
> > "Carlos Lara" wrote:
> >
> > > Hi,
> > > I have RS with sp1 in one server and MS Analysis server with sp3 in another.
> > > Both inside of Domain and the user is the administrator. Also I have the
> > > correct roles in the AS.
> > >
> > > I got this error:
> > > An error has occurred during report processing.
> > > Cannot create a connection to data source 'Sales_MDX_Colinas23'. Database
> > > 'Sales' does not exist.
> > >
> > > In the dev. env works fine and also if AS is in the same server as RS.
> > >
> > > The problem is when AS and RS are in different server.
> > >
> > > Does some one knows whats wrong?
> > >
> > > Thanks in advance
> > >
> > >sql

Monday, March 19, 2012

Msg 601 - bizarre

Hi - we are on SQL 2005 and I jsut recently applied SP1. We have a reporting
database that I load each night with a SSIS package - PK's are dropped then
readded at the end - I am getting the strangest error rebuilding one PK on
one table - I have tested adding the PK thru the GUI with the same error -
all the doc on this error suggests it comes from using hints to change
isolation levels running queries, but I am simply trying to add a Primary
Key. Anyone seen this or ahve any ideas? Thanks so much, I've commented out
the offending line for now but would like to get the PK back on this table -
I also ran a clean DBCC checktable. here is the statement and the error -
(Select a.name from sysobjects a where a.xtype = 'PK'
and a.name = 'PK_drug_test') Alter Table [dbo].[drug_test] ADD
Constraint PK_drug_test Primary Key Clustered
(
[test_id],
[lcd_id]
) ON [PRIMARY]
Msg 601, Level 12, State 3, Line 2
Could not continue scan with NOLOCK due to data movement.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
The statement has been terminated.
--
John F.Hi
Did you change the Isolation level in the Tools/Options/Query Execution/SQL
Server
or Query/Query Options/Advanced settings in Managerment Studio? If not then
you may want to check what is being set by using SQL Profiler.
John
"John F." wrote:
> Hi - we are on SQL 2005 and I jsut recently applied SP1. We have a reporting
> database that I load each night with a SSIS package - PK's are dropped then
> readded at the end - I am getting the strangest error rebuilding one PK on
> one table - I have tested adding the PK thru the GUI with the same error -
> all the doc on this error suggests it comes from using hints to change
> isolation levels running queries, but I am simply trying to add a Primary
> Key. Anyone seen this or ahve any ideas? Thanks so much, I've commented out
> the offending line for now but would like to get the PK back on this table -
> I also ran a clean DBCC checktable. here is the statement and the error -
> (Select a.name from sysobjects a where a.xtype = 'PK'
> and a.name = 'PK_drug_test') Alter Table [dbo].[drug_test] ADD
> Constraint PK_drug_test Primary Key Clustered
> (
> [test_id],
> [lcd_id]
> ) ON [PRIMARY]
> Msg 601, Level 12, State 3, Line 2
> Could not continue scan with NOLOCK due to data movement.
> Msg 1750, Level 16, State 0, Line 2
> Could not create constraint. See previous errors.
> The statement has been terminated.
> --
> John F.|||"John F." <juantana@.newsgroups.microsoft.com> wrote in message
news:EF5E716D-5896-4207-96F6-F9928EDCA403@.microsoft.com...
> Hi - we are on SQL 2005 and I jsut recently applied SP1. We have a
> reporting
> database that I load each night with a SSIS package - PK's are dropped
> then
> readded at the end -
You shouldn't be dropping and readding clustered PK's. You _might_ want to
drop non-clustered indexes and rebuild them at the end. But loading the
data into a page heap and then moving all the data pages into a clustered
index is unlikely to be faster than loading the data into the clustered
index to begin with. And since it completely recreates the table, you might
as well load into a staging table.
Also in SQL 2005 instead of dropping secondary indexes, you can just disable
them and rebuild them later. This is much easier to script and manage you
don't have to hard-code all the index definitions in the load script.
David|||we did extensive testing and leaving the PK's (clustered or non) made the
SSIS load come to a complete crawl as it tried to maintain/update them - we
do a complete refresh every time so we are a bit of a different scenario.
Back to the original issue - the isolation level is not being changed at all
during this and the error doesn't seem to match the activity at all.
--
John F.
"David Browne" wrote:
> "John F." <juantana@.newsgroups.microsoft.com> wrote in message
> news:EF5E716D-5896-4207-96F6-F9928EDCA403@.microsoft.com...
> > Hi - we are on SQL 2005 and I jsut recently applied SP1. We have a
> > reporting
> > database that I load each night with a SSIS package - PK's are dropped
> > then
> > readded at the end -
> You shouldn't be dropping and readding clustered PK's. You _might_ want to
> drop non-clustered indexes and rebuild them at the end. But loading the
> data into a page heap and then moving all the data pages into a clustered
> index is unlikely to be faster than loading the data into the clustered
> index to begin with. And since it completely recreates the table, you might
> as well load into a staging table.
> Also in SQL 2005 instead of dropping secondary indexes, you can just disable
> them and rebuild them later. This is much easier to script and manage you
> don't have to hard-code all the index definitions in the load script.
> David
>
>|||Hi John
You may want to try explicitly setting the ONLINE option to OFF and the
MAXDOP option to 1.
John
"John F." wrote:
> we did extensive testing and leaving the PK's (clustered or non) made the
> SSIS load come to a complete crawl as it tried to maintain/update them - we
> do a complete refresh every time so we are a bit of a different scenario.
> Back to the original issue - the isolation level is not being changed at all
> during this and the error doesn't seem to match the activity at all.
> --
> John F.
>
> "David Browne" wrote:
> >
> > "John F." <juantana@.newsgroups.microsoft.com> wrote in message
> > news:EF5E716D-5896-4207-96F6-F9928EDCA403@.microsoft.com...
> > > Hi - we are on SQL 2005 and I jsut recently applied SP1. We have a
> > > reporting
> > > database that I load each night with a SSIS package - PK's are dropped
> > > then
> > > readded at the end -
> >
> > You shouldn't be dropping and readding clustered PK's. You _might_ want to
> > drop non-clustered indexes and rebuild them at the end. But loading the
> > data into a page heap and then moving all the data pages into a clustered
> > index is unlikely to be faster than loading the data into the clustered
> > index to begin with. And since it completely recreates the table, you might
> > as well load into a staging table.
> >
> > Also in SQL 2005 instead of dropping secondary indexes, you can just disable
> > them and rebuild them later. This is much easier to script and manage you
> > don't have to hard-code all the index definitions in the load script.
> >
> > David
> >
> >
> >
> >|||hey, thanks to everyone for the help - I will try explicitly setting the
ONLINE to off and the MAXDOP to 1. FYI if it makes any diff this is a 64 bit
server jsut recently updated to SP1. David in regards to your comment, I am
changing the previous developer's code to build the PK's as non-clustered as
well. Is there a better way to disable them to load the tons of data we load
every night and then re-enable? we reaplce all data for any given district
when we receive their files, but the other data in the database stays the
same until those corresponding districts are rec'd.
We are getting a few strange erros on this server suddenly - may not be
related but last night my maint plan ran out of stack space on and update
statistics as well.
Thanks again for the help, guys.
--
John F.
"David Browne" wrote:
> "John F." <juantana@.newsgroups.microsoft.com> wrote in message
> news:EF5E716D-5896-4207-96F6-F9928EDCA403@.microsoft.com...
> > Hi - we are on SQL 2005 and I jsut recently applied SP1. We have a
> > reporting
> > database that I load each night with a SSIS package - PK's are dropped
> > then
> > readded at the end -
> You shouldn't be dropping and readding clustered PK's. You _might_ want to
> drop non-clustered indexes and rebuild them at the end. But loading the
> data into a page heap and then moving all the data pages into a clustered
> index is unlikely to be faster than loading the data into the clustered
> index to begin with. And since it completely recreates the table, you might
> as well load into a staging table.
> Also in SQL 2005 instead of dropping secondary indexes, you can just disable
> them and rebuild them later. This is much easier to script and manage you
> don't have to hard-code all the index definitions in the load script.
> David
>
>|||John, that worked! Thanks so much - I would've never figured out to
exlpicitly set MAXDOP on a PK rebuild, wonder why it works for awhile without
those options set and then starts throwing the error though? So weird.
Thanks again!
--
John F.
"John Bell" wrote:
> Hi John
> You may want to try explicitly setting the ONLINE option to OFF and the
> MAXDOP option to 1.
> John
> "John F." wrote:
> > we did extensive testing and leaving the PK's (clustered or non) made the
> > SSIS load come to a complete crawl as it tried to maintain/update them - we
> > do a complete refresh every time so we are a bit of a different scenario.
> > Back to the original issue - the isolation level is not being changed at all
> > during this and the error doesn't seem to match the activity at all.
> > --
> > John F.
> >
> >
> > "David Browne" wrote:
> >
> > >
> > > "John F." <juantana@.newsgroups.microsoft.com> wrote in message
> > > news:EF5E716D-5896-4207-96F6-F9928EDCA403@.microsoft.com...
> > > > Hi - we are on SQL 2005 and I jsut recently applied SP1. We have a
> > > > reporting
> > > > database that I load each night with a SSIS package - PK's are dropped
> > > > then
> > > > readded at the end -
> > >
> > > You shouldn't be dropping and readding clustered PK's. You _might_ want to
> > > drop non-clustered indexes and rebuild them at the end. But loading the
> > > data into a page heap and then moving all the data pages into a clustered
> > > index is unlikely to be faster than loading the data into the clustered
> > > index to begin with. And since it completely recreates the table, you might
> > > as well load into a staging table.
> > >
> > > Also in SQL 2005 instead of dropping secondary indexes, you can just disable
> > > them and rebuild them later. This is much easier to script and manage you
> > > don't have to hard-code all the index definitions in the load script.
> > >
> > > David
> > >
> > >
> > >
> > >|||John - that worked! Thanks so much for your help, the PK rebuilt just fine.
We started getting the error on another table too so I guess I can change
them to nonclustered and then add those two options for all - wouldve never
thought to explicitly set the degree of parallelism - wonder why it works w/o
those options for awhile and then starts failing throwing the error all of a
sudden? anyway thanks again!
--
John F.
"John Bell" wrote:
> Hi John
> You may want to try explicitly setting the ONLINE option to OFF and the
> MAXDOP option to 1.
> John
> "John F." wrote:
> > we did extensive testing and leaving the PK's (clustered or non) made the
> > SSIS load come to a complete crawl as it tried to maintain/update them - we
> > do a complete refresh every time so we are a bit of a different scenario.
> > Back to the original issue - the isolation level is not being changed at all
> > during this and the error doesn't seem to match the activity at all.
> > --
> > John F.
> >
> >
> > "David Browne" wrote:
> >
> > >
> > > "John F." <juantana@.newsgroups.microsoft.com> wrote in message
> > > news:EF5E716D-5896-4207-96F6-F9928EDCA403@.microsoft.com...
> > > > Hi - we are on SQL 2005 and I jsut recently applied SP1. We have a
> > > > reporting
> > > > database that I load each night with a SSIS package - PK's are dropped
> > > > then
> > > > readded at the end -
> > >
> > > You shouldn't be dropping and readding clustered PK's. You _might_ want to
> > > drop non-clustered indexes and rebuild them at the end. But loading the
> > > data into a page heap and then moving all the data pages into a clustered
> > > index is unlikely to be faster than loading the data into the clustered
> > > index to begin with. And since it completely recreates the table, you might
> > > as well load into a staging table.
> > >
> > > Also in SQL 2005 instead of dropping secondary indexes, you can just disable
> > > them and rebuild them later. This is much easier to script and manage you
> > > don't have to hard-code all the index definitions in the load script.
> > >
> > > David
> > >
> > >
> > >
> > >|||Hi
I guess it depends on how much work the rebuild is estimated to take and/or
if it thinks it can do parts in parallel. I guess it thought wrong!! You may
want to log this at http://lab.msdn.microsoft.com/productfeedback/default.aspx
John
"John F." wrote:
> John, that worked! Thanks so much - I would've never figured out to
> exlpicitly set MAXDOP on a PK rebuild, wonder why it works for awhile without
> those options set and then starts throwing the error though? So weird.
> Thanks again!
> --
> John F.
>
> "John Bell" wrote:
> > Hi John
> >
> > You may want to try explicitly setting the ONLINE option to OFF and the
> > MAXDOP option to 1.
> >
> > John
> >
> > "John F." wrote:
> >
> > > we did extensive testing and leaving the PK's (clustered or non) made the
> > > SSIS load come to a complete crawl as it tried to maintain/update them - we
> > > do a complete refresh every time so we are a bit of a different scenario.
> > > Back to the original issue - the isolation level is not being changed at all
> > > during this and the error doesn't seem to match the activity at all.
> > > --
> > > John F.
> > >
> > >
> > > "David Browne" wrote:
> > >
> > > >
> > > > "John F." <juantana@.newsgroups.microsoft.com> wrote in message
> > > > news:EF5E716D-5896-4207-96F6-F9928EDCA403@.microsoft.com...
> > > > > Hi - we are on SQL 2005 and I jsut recently applied SP1. We have a
> > > > > reporting
> > > > > database that I load each night with a SSIS package - PK's are dropped
> > > > > then
> > > > > readded at the end -
> > > >
> > > > You shouldn't be dropping and readding clustered PK's. You _might_ want to
> > > > drop non-clustered indexes and rebuild them at the end. But loading the
> > > > data into a page heap and then moving all the data pages into a clustered
> > > > index is unlikely to be faster than loading the data into the clustered
> > > > index to begin with. And since it completely recreates the table, you might
> > > > as well load into a staging table.
> > > >
> > > > Also in SQL 2005 instead of dropping secondary indexes, you can just disable
> > > > them and rebuild them later. This is much easier to script and manage you
> > > > don't have to hard-code all the index definitions in the load script.
> > > >
> > > > David
> > > >
> > > >
> > > >
> > > >|||I will go log it - FYI I can take off the options you suggest and run the PK
nonclustered just fine as well, so it may be more of an issue of shuffling a
huge amount of data around on disk after the fact - this is running late at
night so I dont think its fighting with other processes. So the answer is
either build them nonclustered or if building clustered use the MAXDOP=1 and
ONLINE=NO options - thanks again.
--
John F.
"John Bell" wrote:
> Hi
> I guess it depends on how much work the rebuild is estimated to take and/or
> if it thinks it can do parts in parallel. I guess it thought wrong!! You may
> want to log this at http://lab.msdn.microsoft.com/productfeedback/default.aspx
> John
> "John F." wrote:
> > John, that worked! Thanks so much - I would've never figured out to
> > exlpicitly set MAXDOP on a PK rebuild, wonder why it works for awhile without
> > those options set and then starts throwing the error though? So weird.
> > Thanks again!
> > --
> > John F.
> >
> >
> > "John Bell" wrote:
> >
> > > Hi John
> > >
> > > You may want to try explicitly setting the ONLINE option to OFF and the
> > > MAXDOP option to 1.
> > >
> > > John
> > >
> > > "John F." wrote:
> > >
> > > > we did extensive testing and leaving the PK's (clustered or non) made the
> > > > SSIS load come to a complete crawl as it tried to maintain/update them - we
> > > > do a complete refresh every time so we are a bit of a different scenario.
> > > > Back to the original issue - the isolation level is not being changed at all
> > > > during this and the error doesn't seem to match the activity at all.
> > > > --
> > > > John F.
> > > >
> > > >
> > > > "David Browne" wrote:
> > > >
> > > > >
> > > > > "John F." <juantana@.newsgroups.microsoft.com> wrote in message
> > > > > news:EF5E716D-5896-4207-96F6-F9928EDCA403@.microsoft.com...
> > > > > > Hi - we are on SQL 2005 and I jsut recently applied SP1. We have a
> > > > > > reporting
> > > > > > database that I load each night with a SSIS package - PK's are dropped
> > > > > > then
> > > > > > readded at the end -
> > > > >
> > > > > You shouldn't be dropping and readding clustered PK's. You _might_ want to
> > > > > drop non-clustered indexes and rebuild them at the end. But loading the
> > > > > data into a page heap and then moving all the data pages into a clustered
> > > > > index is unlikely to be faster than loading the data into the clustered
> > > > > index to begin with. And since it completely recreates the table, you might
> > > > > as well load into a staging table.
> > > > >
> > > > > Also in SQL 2005 instead of dropping secondary indexes, you can just disable
> > > > > them and rebuild them later. This is much easier to script and manage you
> > > > > don't have to hard-code all the index definitions in the load script.
> > > > >
> > > > > David
> > > > >
> > > > >
> > > > >
> > > > >

Monday, March 12, 2012

Msg 4712: cannot truncate table

Hello,

I am running SQL Server 2005 SP1. Whenever I issue a 'truncate' query to the DB engine it fails if the table to be modified is currently referenced by a foreign key constraint.

A code snippet (T-SQL) that demonstrates the problem is beneath.

Does anybody know a fix-it?

Thanks in advance,
Intenion

USE tempdb

-- Clearing the 'tmpdb' database

IFOBJECT_ID('user')ISNOTNULL

DROPTABLE [user];

IFOBJECT_ID('address')ISNOTNULL

DROPTABLE address;

-- Creating tables: 'address' and 'user'

CREATETABLE address

(

id INTIDENTITY(0, 1)CONSTRAINT pk_address PRIMARYKEY,

zip SMALLINTNOTNULLCONSTRAINT chk_zip CHECK(zip > 0),

street NVARCHAR(50)NOTNULLCONSTRAINT chk_street CHECK(LEN(street)> 0),

number SMALLINTNOTNULLCONSTRAINT chk_number CHECK(number > 0)

CONSTRAINT unq_address UNIQUE(zip, street, number)

);

CREATETABLE [user]

(

email VARCHAR(50)CONSTRAINT pk_user PRIMARYKEY,

address INTNULLCONSTRAINT fk_user_address FOREIGNKEYREFERENCES address (id)ONDELETESETNULL

);

-- Populating tables

INSERTINTO address VALUES(2365,'street 1', 2);

INSERTINTO address VALUES(23369,'street 2', 2);

INSERTINTO [user] VALUES('someone@.isp.net', 0);

INSERTINTO [user] VALUES('somebody@.isp-r-us.com', 1);

-- Truncate query that fails

TRUNCATETABLE address;

-- Delete query that passes

--DELETE address;

Hi,

this is by design. YOu either have to drop the FK first or use the DELETE statement.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi,

Maybe a documentation update would be welcome for the 'truncate table' topic in Books Online.

Thank you for your answer,
Intenion.

|||

Jens K. Suessmeyer wrote:
Hi,
this is by design. YOu either have to drop the FK first or use the DELETE statement.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de

I think I am running into the same situation. Would you mind showing how (using SMO) that I would drop the FK (then readd it so that the table is not changed)? It seems from the documentation that DELETE also tries to enforce the FK constraint so the error would be reasserted if I used DELETE.

Kevin

Msg 4712: cannot truncate table

Hello,

I am running SQL Server 2005 SP1. Whenever I issue a 'truncate' query to the DB engine it fails if the table to be modified is currently referenced by a foreign key constraint.

A code snippet (T-SQL) that demonstrates the problem is beneath.

Does anybody know a fix-it?

Thanks in advance,
Intenion

USE tempdb

-- Clearing the 'tmpdb' database

IF OBJECT_ID('user') IS NOT NULL

DROP TABLE [user];

IF OBJECT_ID('address') IS NOT NULL

DROP TABLE address;

-- Creating tables: 'address' and 'user'

CREATE TABLE address

(

id INT IDENTITY (0, 1) CONSTRAINT pk_address PRIMARY KEY,

zip SMALLINT NOT NULL CONSTRAINT chk_zip CHECK (zip > 0),

street NVARCHAR(50) NOT NULL CONSTRAINT chk_street CHECK (LEN(street) > 0),

number SMALLINT NOT NULL CONSTRAINT chk_number CHECK (number > 0)

CONSTRAINT unq_address UNIQUE (zip, street, number)

);

CREATE TABLE [user]

(

email VARCHAR(50) CONSTRAINT pk_user PRIMARY KEY,

address INT NULL CONSTRAINT fk_user_address FOREIGN KEY REFERENCES address (id) ON DELETE SET NULL

);

-- Populating tables

INSERT INTO address VALUES (2365, 'street 1', 2);

INSERT INTO address VALUES (23369, 'street 2', 2);

INSERT INTO [user] VALUES ('someone@.isp.net', 0);

INSERT INTO [user] VALUES ('somebody@.isp-r-us.com', 1);

-- Truncate query that fails

TRUNCATE TABLE address;

-- Delete query that passes

--DELETE address;

Hi,

this is by design. YOu either have to drop the FK first or use the DELETE statement.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi,

Maybe a documentation update would be welcome for the 'truncate table' topic in Books Online.

Thank you for your answer,
Intenion.

|||

Jens K. Suessmeyer wrote:
Hi,
this is by design. YOu either have to drop the FK first or use the DELETE statement.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de

Kevin

Msg 3910 Transaction context in use by another session.

We are testing SQL 2005 SP1 in a multi-server environment. Stored Procedure 'A' is executed on server.database 'A'. It calls SP 'B' on server.database 'B'. SP 'B' in turn calls SP 'C' which is back on server.database 'A'. So in essence A calls an SP on B, which calls an SP back on A. Linked servers are present on both servers to enable these cross-server calls. This scenario works in our SQL 2000 environment.

In the SQL 2005 environment, we get the Msg 3910 message stated in the subject. Is this truly a 'loopback server' situation and if so, why does it work in SQL 2000? Did this functionality change in SQL 2005? In SQL 2005 SP1? This may seem to be a klugy way to do things but unfortunately, it is a core part of our application security system and won't be easily undone. Any ideas would be appreciated. Thanks.

The problem was not introduced in SQL 2005 SP1. The error still occurs when run using two servers with the base SQL 2005 install.|||

We have an application that uses link servers and is running into the same error.

Looks like an undocumented feature in SS2005 or a BUG.

Anyone have any ideas on how to get around it?

Eric

|||

We worked the issue through with Microsoft support and I've included their comments and resolution below. The problem with their answer is that if a consistent data view is their goal, then this should also fail when all the databases are on a single server. Perhaps they'll make this consistent in a future service pack. Anyway, such as it is, here's the answer.

Beginning of Microsoft response:

Yes, this is a known problem and we change the behavior for good reason in Yukon. We’d like you to work around this issue.

The reason we change to current SQL Server 2005 behavior is that it can guarantee consistent data view, while SQL Server 2000 behavior may expose inconsistent data if the loopback tries to access the table that is been changed.

other ways to avoid this error:

1. Change the application logic to not use loopback.

2. Separate the INSERT EXEC into two steps -- Run remote EXEC first(which will succeed for loopback) and put it into a remote table and then run a SELECT from the remote table

But in general, SQL Server doesn’t support loopback server, so you are still at risk that you might get impacted in future release. So the best way is to avoid using the loopback.

End of Microsoft response.

Hope this helps.

|||I was facing a similar problem wherein I was using loopback linked servers for synchronizing multiple databases on same server as well as remote servers. The code was generically handling this. Will have to look for alternate way as support for loopback server is not guaranteed.

Thanks

Chandrakant Karale.

Msg 3910 Transaction context in use by another session.

The problem was not introduced in SQL 2005 SP1. The error still occurs when run using two servers with the base SQL 2005 install.|||

We have an application that uses link servers and is running into the same error.

Looks like an undocumented feature in SS2005 or a BUG.

Anyone have any ideas on how to get around it?

Eric

|||

Beginning of Microsoft response:

Yes, this is a known problem and we change the behavior for good reason in Yukon. We’d like you to work around this issue.

other ways to avoid this error:

1. Change the application logic to not use loopback.

2. Separate the INSERT EXEC into two steps -- Run remote EXEC first(which will succeed for loopback) and put it into a remote table and then run a SELECT from the remote table

But in general, SQL Server doesn’t support loopback server, so you are still at risk that you might get impacted in future release. So the best way is to avoid using the loopback.

End of Microsoft response.

Hope this helps.

Thanks

Chandrakant Karale.

Msg 3910 Transaction context in use by another session.

The problem was not introduced in SQL 2005 SP1. The error still occurs when run using two servers with the base SQL 2005 install.|||

We have an application that uses link servers and is running into the same error.

Looks like an undocumented feature in SS2005 or a BUG.

Anyone have any ideas on how to get around it?

Eric

|||

Beginning of Microsoft response:

Yes, this is a known problem and we change the behavior for good reason in Yukon. We’d like you to work around this issue.

other ways to avoid this error:

1. Change the application logic to not use loopback.

2. Separate the INSERT EXEC into two steps -- Run remote EXEC first(which will succeed for loopback) and put it into a remote table and then run a SELECT from the remote table

But in general, SQL Server doesn’t support loopback server, so you are still at risk that you might get impacted in future release. So the best way is to avoid using the loopback.

End of Microsoft response.

Hope this helps.

Thanks

Chandrakant Karale.

Saturday, February 25, 2012

msdtc is causing my cluster to be unavailiable via it's virtual na

I have windows 2003 sp1 running in a 2-node cluster. The cluster is running
sql sp4. I tried failing over my cluster after a windows update and i'm not
sure if that's what threw my server's off or something else. But here's the
situation. I currently have the cluster group running on node 2, the msdtc
running on node 1 and the sql group running on node 2. When i move the msdtc
group to node 2 (ie.. the server which should be running all services)i cant
ping the sql virtual server by name or ip. I am however able to contact the
individual servers via their names sql1 and sql2. So basically i lose my
connection to the virtual sql server throughout my network when the msdtc is
running on the same server. Is there an update? upgrade? to the msdtc or
anything else i can do to fix this issue?
Ohh yes and there's no errors in the event log.
What are the Resource Group names, Resource Network Names, and Resource IP
addresses? What other resources a apart of each group?
Sounds like a DNS registration issue. Does the SQL Server group go offline
or fail over when the MSDTC group is moved to node 2?
Sincerely,
Anthony Thomas

"Sunny" <Sunny@.discussions.microsoft.com> wrote in message
news:12879549-04B5-454B-A44C-C075061EAEC1@.microsoft.com...
> I have windows 2003 sp1 running in a 2-node cluster. The cluster is
running
> sql sp4. I tried failing over my cluster after a windows update and i'm
not
> sure if that's what threw my server's off or something else. But here's
the
> situation. I currently have the cluster group running on node 2, the
msdtc
> running on node 1 and the sql group running on node 2. When i move the
msdtc
> group to node 2 (ie.. the server which should be running all services)i
cant
> ping the sql virtual server by name or ip. I am however able to contact
the
> individual servers via their names sql1 and sql2. So basically i lose my
> connection to the virtual sql server throughout my network when the msdtc
is
> running on the same server. Is there an update? upgrade? to the msdtc or
> anything else i can do to fix this issue?
> Ohh yes and there's no errors in the event log.

msdtc config / sql server 2k cluster(sp4) / W2k3 sp1

I have an application that uses com components to connect to it's backend sql
server db. Right now the db is running on a W2k server (sp4)
(non-clustered). I've copied the dbs to the clustered environment that we
are migrating all of our databases to, and now the com components do not
connect. I've installed the Network DTC components and have configured the
MSDTC resource via Cluster Adminstrator and can see that the service starts
on each server in the node as I fail the cluster group between those nodes.
I've read and adhered to doc 301600 and it's sub article of 817064
(pre-reqs). Not sure what else may be wrong or that I need to validate. The
application returns the following error when the user tries to login via the
web portal: New transaction cannot enlist in the specified transaction
coordinator. The application runs on a W2k (sp4) server. Any guidance would
be greatly appreciated.
It isn't anything to do with the cluster. This would happen on a stand-alone
system anyway.
HOWTO: Enable DTC Between Web Servers and SQL Servers Running Windows Server
2003
http://support.microsoft.com/default...b;en-us;555017
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"LufkinTXDbajohn" <LufkinTXDbajohn@.discussions.microsoft.com> wrote in
message news:A0E2373E-5E90-45B2-B3EA-9BD32F6D4ABB@.microsoft.com...
>I have an application that uses com components to connect to it's backend
>sql
> server db. Right now the db is running on a W2k server (sp4)
> (non-clustered). I've copied the dbs to the clustered environment that we
> are migrating all of our databases to, and now the com components do not
> connect. I've installed the Network DTC components and have configured
> the
> MSDTC resource via Cluster Adminstrator and can see that the service
> starts
> on each server in the node as I fail the cluster group between those
> nodes.
> I've read and adhered to doc 301600 and it's sub article of 817064
> (pre-reqs). Not sure what else may be wrong or that I need to validate.
> The
> application returns the following error when the user tries to login via
> the
> web portal: New transaction cannot enlist in the specified transaction
> coordinator. The application runs on a W2k (sp4) server. Any guidance
> would
> be greatly appreciated.

Friday, March 23, 2012

MSOLAP Connection error

Monday, March 19, 2012

Msg 601 - bizarre

Msg 601 - bizarre

Monday, March 12, 2012

Msg 4712: cannot truncate table

Msg 4712: cannot truncate table

Msg 3910 Transaction context in use by another session.

Msg 3910 Transaction context in use by another session.

Msg 3910 Transaction context in use by another session.

Saturday, February 25, 2012

msdtc is causing my cluster to be unavailiable via it's virtual na

msdtc config / sql server 2k cluster(sp4) / W2k3 sp1

MSDTC and Win2K3

Blog Archive

About Me