MSQL Update

Hi
Can anyone help me with the MSSQL following problem:
I want to update the field "Date" with today´s date in a Tabel named Customers
I want to click on a "button" that runs something like:

Update Date with Today´s date where CustomerName = TextBox.text

Thanks in advance for your helpstring strSQL = "UPDATE [Customers] SET [Date] = GetDate() WHERE CustomerName = " + TextBox.Text

basic, basic, basic

Msg 2714

Any idea why I get the following error:
Msg 2714, Level 16, State 1, Line 16
There is already an object named '#errlog' in the database.
when I run the following statement:
IF object_id('tempdb..#errlog') IS NOT NULL
DROP TABLE #errlog
IF @.@.VERSION LIKE '%9.00%'
CREATE TABLE #errlog
(
logdate datetime,
processinfo varchar(50),
logtext varchar(2000)
)
ELSE
CREATE TABLE #errlog
(
errorlog varchar(2000),
c2 int
)
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200604/1Yes...even though you have the logic contract, the
statement will be seen as two create table statements
attempting to create tables of the same name.
One option is to wrap each create table #errlog...etc
statement in an EXEC.
IF @.@.VERSION LIKE '%9.00%'
EXEC ('CREATE TABLE #errlog
(
logdate datetime,
processinfo varchar(50),
logtext varchar(2000)
)')
ELSE
EXEC('CREATE TABLE #errlog1
(
errorlog varchar(2000),
c2 int
)')
-Sue
On Mon, 24 Apr 2006 20:51:52 GMT, "cbrichards" <u3288@.uwe>
wrote:

>Any idea why I get the following error:
>Msg 2714, Level 16, State 1, Line 16
>There is already an object named '#errlog' in the database.
>when I run the following statement:
>IF object_id('tempdb..#errlog') IS NOT NULL
> DROP TABLE #errlog
>IF @.@.VERSION LIKE '%9.00%'
> CREATE TABLE #errlog
> (
> logdate datetime,
> processinfo varchar(50),
> logtext varchar(2000)
> )
>ELSE
> CREATE TABLE #errlog
> (
> errorlog varchar(2000),
> c2 int
> )|||Is that because it is a DDL statement that it nees to be wrapped as such?
Sue Hoegemeier wrote:[vbcol=seagreen]
>Yes...even though you have the logic contract, the
>statement will be seen as two create table statements
>attempting to create tables of the same name.
>One option is to wrap each create table #errlog...etc
>statement in an EXEC.
>IF @.@.VERSION LIKE '%9.00%'
> EXEC ('CREATE TABLE #errlog
> (
> logdate datetime,
> processinfo varchar(50),
> logtext varchar(2000)
> )')
>ELSE
> EXEC('CREATE TABLE #errlog1
> (
> errorlog varchar(2000),
> c2 int
> )')
>-Sue
>
>[quoted text clipped - 18 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200604/1|||In the original version you had, when parsing or compiling
the query the engine will see two distinct create table
statements trying to create a table of the same name.
If you wrap it in an exec, the engine will use deferred
resolution.
-Sue
On Mon, 24 Apr 2006 22:31:59 GMT, "cbrichards via
droptable.com" <u3288@.uwe> wrote:
[vbcol=seagreen]
>Is that because it is a DDL statement that it nees to be wrapped as such?
>Sue Hoegemeier wrote:|||Why does it see "two distinct create table statements" when it is within an
IF construct? Truly their are two create statements, but the IF is
conditional, correct?
Sue Hoegemeier wrote:[vbcol=seagreen]
>In the original version you had, when parsing or compiling
>the query the engine will see two distinct create table
>statements trying to create a table of the same name.
>If you wrap it in an exec, the engine will use deferred
>resolution.
>-Sue
>
>[quoted text clipped - 25 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200604/1|||On Wed, 26 Apr 2006 17:19:57 GMT, cbrichards via droptable.com wrote:

>Why does it see "two distinct create table statements" when it is within an
>IF construct? Truly their are two create statements, but the IF is
>conditional, correct?
Hi cbrichards,
At parse and compile time, the batch is scanned from top to bottom,
without checking conditional execution. This check sees two CREATE TABLE
statements and regards that as an error condition.
Hugo Kornelis, SQL Server MVP|||It doesn't know the path for the logic branch until run time
- not at parse or compile time. Consequently it will see two
different create table statements of the same name.
-Sue
On Wed, 26 Apr 2006 17:19:57 GMT, "cbrichards via
droptable.com" <u3288@.uwe> wrote:
[vbcol=seagreen]
>Why does it see "two distinct create table statements" when it is within an
>IF construct? Truly their are two create statements, but the IF is
>conditional, correct?
>Sue Hoegemeier wrote:

Msg 2714

Any idea why I get the following error:
Msg 2714, Level 16, State 1, Line 16
There is already an object named '#errlog' in the database.
when I run the following statement:
IF object_id('tempdb..#errlog') IS NOT NULL
DROP TABLE #errlog
IF @.@.VERSION LIKE '%9.00%'
CREATE TABLE #errlog
(
logdate datetime,
processinfo varchar(50),
logtext varchar(2000)
)
ELSE
CREATE TABLE #errlog
(
errorlog varchar(2000),
c2 int
)
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1Yes...even though you have the logic contract, the
statement will be seen as two create table statements
attempting to create tables of the same name.
One option is to wrap each create table #errlog...etc
statement in an EXEC.
IF @.@.VERSION LIKE '%9.00%'
EXEC ('CREATE TABLE #errlog
(
logdate datetime,
processinfo varchar(50),
logtext varchar(2000)
)')
ELSE
EXEC('CREATE TABLE #errlog1
(
errorlog varchar(2000),
c2 int
)')
-Sue
On Mon, 24 Apr 2006 20:51:52 GMT, "cbrichards" <u3288@.uwe>
wrote:
>Any idea why I get the following error:
>Msg 2714, Level 16, State 1, Line 16
>There is already an object named '#errlog' in the database.
>when I run the following statement:
>IF object_id('tempdb..#errlog') IS NOT NULL
> DROP TABLE #errlog
>IF @.@.VERSION LIKE '%9.00%'
> CREATE TABLE #errlog
> (
> logdate datetime,
> processinfo varchar(50),
> logtext varchar(2000)
> )
>ELSE
> CREATE TABLE #errlog
> (
> errorlog varchar(2000),
> c2 int
> )|||Is that because it is a DDL statement that it nees to be wrapped as such?
Sue Hoegemeier wrote:
>Yes...even though you have the logic contract, the
>statement will be seen as two create table statements
>attempting to create tables of the same name.
>One option is to wrap each create table #errlog...etc
>statement in an EXEC.
>IF @.@.VERSION LIKE '%9.00%'
> EXEC ('CREATE TABLE #errlog
> (
> logdate datetime,
> processinfo varchar(50),
> logtext varchar(2000)
> )')
>ELSE
> EXEC('CREATE TABLE #errlog1
> (
> errorlog varchar(2000),
> c2 int
> )')
>-Sue
>>Any idea why I get the following error:
>>Msg 2714, Level 16, State 1, Line 16
>[quoted text clipped - 18 lines]
>> c2 int
>> )
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1|||In the original version you had, when parsing or compiling
the query the engine will see two distinct create table
statements trying to create a table of the same name.
If you wrap it in an exec, the engine will use deferred
resolution.
-Sue
On Mon, 24 Apr 2006 22:31:59 GMT, "cbrichards via
SQLMonster.com" <u3288@.uwe> wrote:
>Is that because it is a DDL statement that it nees to be wrapped as such?
>Sue Hoegemeier wrote:
>>Yes...even though you have the logic contract, the
>>statement will be seen as two create table statements
>>attempting to create tables of the same name.
>>One option is to wrap each create table #errlog...etc
>>statement in an EXEC.
>>IF @.@.VERSION LIKE '%9.00%'
>> EXEC ('CREATE TABLE #errlog
>> (
>> logdate datetime,
>> processinfo varchar(50),
>> logtext varchar(2000)
>> )')
>>ELSE
>> EXEC('CREATE TABLE #errlog1
>> (
>> errorlog varchar(2000),
>> c2 int
>> )')
>>-Sue
>>Any idea why I get the following error:
>>Msg 2714, Level 16, State 1, Line 16
>>[quoted text clipped - 18 lines]
>> c2 int
>> )|||Why does it see "two distinct create table statements" when it is within an
IF construct? Truly their are two create statements, but the IF is
conditional, correct?
Sue Hoegemeier wrote:
>In the original version you had, when parsing or compiling
>the query the engine will see two distinct create table
>statements trying to create a table of the same name.
>If you wrap it in an exec, the engine will use deferred
>resolution.
>-Sue
>>Is that because it is a DDL statement that it nees to be wrapped as such?
>[quoted text clipped - 25 lines]
>> c2 int
>> )
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1|||On Wed, 26 Apr 2006 17:19:57 GMT, cbrichards via SQLMonster.com wrote:
>Why does it see "two distinct create table statements" when it is within an
>IF construct? Truly their are two create statements, but the IF is
>conditional, correct?
Hi cbrichards,
At parse and compile time, the batch is scanned from top to bottom,
without checking conditional execution. This check sees two CREATE TABLE
statements and regards that as an error condition.
--
Hugo Kornelis, SQL Server MVP|||It doesn't know the path for the logic branch until run time
- not at parse or compile time. Consequently it will see two
different create table statements of the same name.
-Sue
On Wed, 26 Apr 2006 17:19:57 GMT, "cbrichards via
SQLMonster.com" <u3288@.uwe> wrote:
>Why does it see "two distinct create table statements" when it is within an
>IF construct? Truly their are two create statements, but the IF is
>conditional, correct?
>Sue Hoegemeier wrote:
>>In the original version you had, when parsing or compiling
>>the query the engine will see two distinct create table
>>statements trying to create a table of the same name.
>>If you wrap it in an exec, the engine will use deferred
>>resolution.
>>-Sue
>>Is that because it is a DDL statement that it nees to be wrapped as such?
>>[quoted text clipped - 25 lines]
>> c2 int
>> )

MSDTC Required?

I am preparing for the installation of SQL Server 2005 Standard on a
two-node cluster. The SQL Server books Online" topic named "Before
Installing Failover Clustering" indicates that you must install MSDTC if you
are installing the Database Engine and SSIS, Notification Services or
Workstations components. I am not planning on installing SSIS or
Notification Services but would like to install the Workstations components
(specifically SSMS and the other tools) on the clustered nodes.
Is it really necessary to install MSDTC in the cluster if I don't plan on
doing distributed transactions? I want to keep the installation as simple
as possible and don't see why I need to install MSDTC in the cluster with
the Workstation components.
Any help would be appreciated.
Thanks!
Chris
SQL works a lot better with MSDTC. You can go ahead and install it in the
cluster group if you do not have a lot of distributed transactions.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Cgal" <cgallelli@.newsgroups.nospam> wrote in message
news:eN61fcOZHHA.348@.TK2MSFTNGP02.phx.gbl...
>I am preparing for the installation of SQL Server 2005 Standard on a
>two-node cluster. The SQL Server books Online" topic named "Before
>Installing Failover Clustering" indicates that you must install MSDTC if
>you are installing the Database Engine and SSIS, Notification Services or
>Workstations components. I am not planning on installing SSIS or
>Notification Services but would like to install the Workstations components
>(specifically SSMS and the other tools) on the clustered nodes.
> Is it really necessary to install MSDTC in the cluster if I don't plan on
> doing distributed transactions? I want to keep the installation as simple
> as possible and don't see why I need to install MSDTC in the cluster with
> the Workstation components.
> Any help would be appreciated.
> Thanks!
> Chris
>
|||Can you clarify "Works a lot better with..."
I always install it as a matter of procedure but if it's not necessary, how
does it help?
"Geoff N. Hiten" wrote:

> SQL works a lot better with MSDTC. You can go ahead and install it in the
> cluster group if you do not have a lot of distributed transactions.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Cgal" <cgallelli@.newsgroups.nospam> wrote in message
> news:eN61fcOZHHA.348@.TK2MSFTNGP02.phx.gbl...
>
|||SQL tries to initialize a DTS connection when it starts up. When MSDTC is
not there, SQL has to wait for a timeout so you get slower startups and
failovers.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"burt_king" <burt_king@.yahoo.com> wrote in message
news:CA063DB1-C405-44E5-9628-9C10AE5C7630@.microsoft.com...[vbcol=seagreen]
> Can you clarify "Works a lot better with..."
> I always install it as a matter of procedure but if it's not necessary,
> how
> does it help?
>
> "Geoff N. Hiten" wrote:
|||MS DTC is installed on each cluster node as part of the Windows OS
installation. What is required is to make it "clustered."
If you will only be installing a single SQL Server instance, then you can
configure the MS DTC cluster resource to use the same cluster resource group
as the SQL Server installation.
Otherwise, follow the instructions to set up a dedicated cluster resource
group for MS DTC, which will require a dedicated shared cluster disk, IP
address, and Network Name resources.
Do not run MS DTC as a resource in the Quorum cluster resource group.
Sincerely,
Anthony Thomas

"Cgal" <cgallelli@.newsgroups.nospam> wrote in message
news:eN61fcOZHHA.348@.TK2MSFTNGP02.phx.gbl...
> I am preparing for the installation of SQL Server 2005 Standard on a
> two-node cluster. The SQL Server books Online" topic named "Before
> Installing Failover Clustering" indicates that you must install MSDTC if
you
> are installing the Database Engine and SSIS, Notification Services or
> Workstations components. I am not planning on installing SSIS or
> Notification Services but would like to install the Workstations
components
> (specifically SSMS and the other tools) on the clustered nodes.
> Is it really necessary to install MSDTC in the cluster if I don't plan on
> doing distributed transactions? I want to keep the installation as simple
> as possible and don't see why I need to install MSDTC in the cluster with
> the Workstation components.
> Any help would be appreciated.
> Thanks!
> Chris
>
|||What if this were active/active sql 2000 instances. Still one MSDTC?
"Anthony Thomas" wrote:

> MS DTC is installed on each cluster node as part of the Windows OS
> installation. What is required is to make it "clustered."
> If you will only be installing a single SQL Server instance, then you can
> configure the MS DTC cluster resource to use the same cluster resource group
> as the SQL Server installation.
> Otherwise, follow the instructions to set up a dedicated cluster resource
> group for MS DTC, which will require a dedicated shared cluster disk, IP
> address, and Network Name resources.
> Do not run MS DTC as a resource in the Quorum cluster resource group.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Cgal" <cgallelli@.newsgroups.nospam> wrote in message
> news:eN61fcOZHHA.348@.TK2MSFTNGP02.phx.gbl...
> you
> components
>
>
|||Each cluster can only have a single MSDTC instance.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"SQLdba" <burt_king@.yahoo.com> wrote in message
news:D7919F72-F753-4232-80CE-DCD52FA271EA@.microsoft.com...[vbcol=seagreen]
> What if this were active/active sql 2000 instances. Still one MSDTC?
>
> "Anthony Thomas" wrote:
|||In which case, the recommended solution is to have a dedicated MS DTC
cluster resource group with dedicated dependencies (Shared Disk, IP Address,
and Network Name).
In times past, Microsoft recommended adding this resource to the Quorum
group; however, it was determined that this was a bad practices because
whenever the quorum goes offline, and DTC handles are destroyed if the two
resources are collocated.
In a dedicated group, even if the quorum group is moved, or the DTC group is
moved, pending DTC transactions and SQL Server handles are maintained.
Sincerely,
Anthony Thomas

"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:eKAYYTxaHHA.3272@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
> Each cluster can only have a single MSDTC instance.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "SQLdba" <burt_king@.yahoo.com> wrote in message
> news:D7919F72-F753-4232-80CE-DCD52FA271EA@.microsoft.com...
can[vbcol=seagreen]
resource[vbcol=seagreen]
IP[vbcol=seagreen]
plan
>
|||Actually, the recommendation was changed based on testing high-volume
systems where certain storage systems could be saturated by the DTC traffic
to the point where the Quorum disk appeared unresponsive, thus causing a
cluster failure. Since Microsoft is officially hardware agnostic, this led
to a blanket recommendation for a dedicated MSDTC group, which later was
modified to apply only to high volume systems.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:eZDWpk7aHHA.4140@.TK2MSFTNGP06.phx.gbl...
> In which case, the recommended solution is to have a dedicated MS DTC
> cluster resource group with dedicated dependencies (Shared Disk, IP
> Address,
> and Network Name).
> In times past, Microsoft recommended adding this resource to the Quorum
> group; however, it was determined that this was a bad practices because
> whenever the quorum goes offline, and DTC handles are destroyed if the two
> resources are collocated.
> In a dedicated group, even if the quorum group is moved, or the DTC group
> is
> moved, pending DTC transactions and SQL Server handles are maintained.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:eKAYYTxaHHA.3272@.TK2MSFTNGP03.phx.gbl...
> can
> resource
> IP
> plan
>

MSDTC on server is unavailable

I have a server called SCSS01 with SQL Server 2000 installed.
I have another server called NT02 with a named instance of SQL Server 2000 installed - the instance name is NT02_2000.
I have set up SCSS01 as a linked server on NT02\NT02_2000.
When I attempt to execute a transaction on NT02\NT02_2000 that updates a table on my linked server (SCSS01) I receive the following error
MSDTC on server 'NT02\NT02_2000' is unavailable.
I think this is because the MSDTC service is called NT02 and not NT02\NT02_2000.
Is this a bug or am I doing something wrong?
Any help would be appreciated.
If these are Windows 2003 servers have you installed Network MSDTC?
Check the following:
How to enable network DTC access in Windows Server 2003
http://support.microsoft.com/default.aspx?scid=kb;[LN];817064
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Hi Rand,
The server named NT02 is w2003 and I have already enables network DTC access - it made no difference.
The server named SCSS01 is windows NT4 server.
It is the names that look wrong to me - my NT02 server has MSDTC server running on it but when I run the transaction the error message indicates it is looking for an MSDTC server on 'NT02\NT02_2000'.
Using SQL Server Service Manager also seems to give strange results when looking at the 'Ditributed Transaction Coordinator' service - no matter which server I look at the status bar shows 'Running \\NT02 - MSDTC'
Can you give me some more help - I can provide more details if necessary.
|||Hi John,
From your descriptions, I understood that you would like to use Linked
Server with an Instance in SQL Server on Windows 2003. Have I understood
you? If there is anything I misunderstood, please feel free to let me know
First of all, It's OK when you seeing status bar shows 'Running \\NT02 -
MSDTC' as it's an machine-level service instead of instance-level service.
Secondly, Randy's suggestion is very helpful. Have you restart the machine
after make that change? Do you use cluster server?
Thirdly, when you are attempting executing an distributed query, what's the
error message? What's your query? Could you run the query standalone in
SCSS01 and get the right result?
At last, the following KB may hit your scenrio, would you please have a
check on them according to your error message? (I keep in mind that you are
using Windows 2003 and I just want you have a look at them if the error msg
meets)
You may receive error message 8525 when you try to run a distributed
transaction on an instance of SQL Server 2000 SP3
http://support.microsoft.com/?id=834849
You may receive a 7391 error message in SQL Server 2000 when you run a
distributed transaction against a linked server after you install Microsoft
Windows XP Service Pack 2 (839279)
http://support.microsoft.com/?id=839279
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Online Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||You have understood my problem correctly.
I undertand that MSDTC is at machine level and my machine name in NT02 so the service status bar showing 'Running \\NT02 - MSDTC' is ok.
I have restarted my machine after enabling network for msdtc - it did not make any difference.
I am not using cluster server.
I can reproduce my problem with the following simple query:
begin tran
select * from scss01.web_openroad.dbo.wt_job
The error message I get when running my transaction is:
Server: Msg 8501, Level 16, State 3, Line 2
MSDTC on server 'NT02\NT02_2000' is unavailable.
Why is it looking for MSDTC on NT02\NT02_2000?
Shouldn't it be looking for MSDTC on NT02?
The same query works perfectly if I run it on SCSS01.
I have looked at the kb articles you mentioned but they do not seem to be of any help to my situation.
Do you have any more ideas?
If you need any more details then please let me know.
Thanks
John
""Mingqing Cheng [MSFT]"" wrote:

> Hi John,
> From your descriptions, I understood that you would like to use Linked
> Server with an Instance in SQL Server on Windows 2003. Have I understood
> you? If there is anything I misunderstood, please feel free to let me know
> First of all, It's OK when you seeing status bar shows 'Running \\NT02 -
> MSDTC' as it's an machine-level service instead of instance-level service.
> Secondly, Randy's suggestion is very helpful. Have you restart the machine
> after make that change? Do you use cluster server?
> Thirdly, when you are attempting executing an distributed query, what's the
> error message? What's your query? Could you run the query standalone in
> SCSS01 and get the right result?
> At last, the following KB may hit your scenrio, would you please have a
> check on them according to your error message? (I keep in mind that you are
> using Windows 2003 and I just want you have a look at them if the error msg
> meets)
> You may receive error message 8525 when you try to run a distributed
> transaction on an instance of SQL Server 2000 SP3
> http://support.microsoft.com/?id=834849
> You may receive a 7391 error message in SQL Server 2000 when you run a
> distributed transaction against a linked server after you install Microsoft
> Windows XP Service Pack 2 (839279)
> http://support.microsoft.com/?id=839279
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
> Sincerely yours,
> Mingqing Cheng
> Microsoft Online Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
>
>
|||You have understood correctly.
I have restarted my machine after enabling network access for msdtc.
I can reproduce the problem with the following simple query:
begin tran
select * from scss01.web_openroad.dbo.wt_job
The error I am getting is:
Server: Msg 8501, Level 16, State 3, Line 2
MSDTC on server 'NT02\NT02_2000' is unavailable.
Why is it looking for MSDTC on server NT02\NT02_2000?
Shouldn't it be looking for MSDTC on server NT02?
The same query works perfectly on SCSS01.
Do you have any more ideas?
If you need any more details from me then let me know.
Thanks
John
""Mingqing Cheng [MSFT]"" wrote:

> Hi John,
> From your descriptions, I understood that you would like to use Linked
> Server with an Instance in SQL Server on Windows 2003. Have I understood
> you? If there is anything I misunderstood, please feel free to let me know
> First of all, It's OK when you seeing status bar shows 'Running \\NT02 -
> MSDTC' as it's an machine-level service instead of instance-level service.
> Secondly, Randy's suggestion is very helpful. Have you restart the machine
> after make that change? Do you use cluster server?
> Thirdly, when you are attempting executing an distributed query, what's the
> error message? What's your query? Could you run the query standalone in
> SCSS01 and get the right result?
> At last, the following KB may hit your scenrio, would you please have a
> check on them according to your error message? (I keep in mind that you are
> using Windows 2003 and I just want you have a look at them if the error msg
> meets)
> You may receive error message 8525 when you try to run a distributed
> transaction on an instance of SQL Server 2000 SP3
> http://support.microsoft.com/?id=834849
> You may receive a 7391 error message in SQL Server 2000 when you run a
> distributed transaction against a linked server after you install Microsoft
> Windows XP Service Pack 2 (839279)
> http://support.microsoft.com/?id=839279
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
> Sincerely yours,
> Mingqing Cheng
> Microsoft Online Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
>
>
|||You have understood correctly.
I have restarted my machine after enabling network access for msdtc.
I can reproduce the problem with the following simple query:
begin tran
select * from scss01.web_openroad.dbo.wt_job
The error I am getting is:
Server: Msg 8501, Level 16, State 3, Line 2
MSDTC on server 'NT02\NT02_2000' is unavailable.
Why is it looking for MSDTC on server NT02\NT02_2000?
Shouldn't it be looking for MSDTC on server NT02?
The same query works perfectly on SCSS01.
Do you have any more ideas?
If you need any more details from me then let me know.
Thanks
John
""Mingqing Cheng [MSFT]"" wrote:

> Hi John,
> From your descriptions, I understood that you would like to use Linked
> Server with an Instance in SQL Server on Windows 2003. Have I understood
> you? If there is anything I misunderstood, please feel free to let me know
> First of all, It's OK when you seeing status bar shows 'Running \\NT02 -
> MSDTC' as it's an machine-level service instead of instance-level service.
> Secondly, Randy's suggestion is very helpful. Have you restart the machine
> after make that change? Do you use cluster server?
> Thirdly, when you are attempting executing an distributed query, what's the
> error message? What's your query? Could you run the query standalone in
> SCSS01 and get the right result?
> At last, the following KB may hit your scenrio, would you please have a
> check on them according to your error message? (I keep in mind that you are
> using Windows 2003 and I just want you have a look at them if the error msg
> meets)
> You may receive error message 8525 when you try to run a distributed
> transaction on an instance of SQL Server 2000 SP3
> http://support.microsoft.com/?id=834849
> You may receive a 7391 error message in SQL Server 2000 when you run a
> distributed transaction against a linked server after you install Microsoft
> Windows XP Service Pack 2 (839279)
> http://support.microsoft.com/?id=839279
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
> Sincerely yours,
> Mingqing Cheng
> Microsoft Online Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
>
>
|||Hi John,
Thanks for your prompt updates!
First of all, as we are using distributed query, we should no longer using
BEGIN TRANSACTION. Would you please try using BEGIN DISTRIBUTED TRANSACTION
to see whether it will resolve your issue?
Secondly, if above could not resolved you issue, would you please show me
your SQL Log files? I have to use them finding the root cause of this
issue. You could only post relative log in the newsgroup
Thank you for your patience and cooperation. If you have any questions or
concerns, dont hesitate to let me know. We are
here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||I have tried using BEGIN DISTRIBUTED TRANSACTION but it makes no difference.
Below is a SQL Log file - the error at the end occurs when I attempt to execute the transaction.
2004-06-21 12:06:05.89 server Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
2004-06-21 12:06:05.89 server Copyright (C) 1988-2002 Microsoft Corporation.
2004-06-21 12:06:05.89 server All rights reserved.
2004-06-21 12:06:05.89 server Server Process ID is 2692.
2004-06-21 12:06:05.89 server Logging SQL Server messages in file 'f:\mssql2000\data\MSSQL$NT02_2000\log\ERRORLOG'.
2004-06-21 12:06:05.89 server SQL Server is starting at priority class 'normal'(2 CPUs detected).
2004-06-21 12:06:05.92 server SQL Server configured for thread mode processing.
2004-06-21 12:06:05.93 server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2004-06-21 12:06:05.96 server Attempting to initialize Distributed Transaction Coordinator.
2004-06-21 12:06:05.98 server Resource Manager Creation Failed: Result Code = 0x8004d01c
2004-06-21 12:06:06.00 spid3 Starting up database 'master'.
2004-06-21 12:06:06.17 spid5 Starting up database 'model'.
2004-06-21 12:06:06.17 server Using 'SSNETLIB.DLL' version '8.0.766'.
2004-06-21 12:06:06.18 server SQL server listening on 192.4.92.47: 1045.
2004-06-21 12:06:06.18 server SQL server listening on 127.0.0.1: 1045.
2004-06-21 12:06:06.21 server SQL server listening on TCP, Named Pipes.
2004-06-21 12:06:06.21 server SQL Server is ready for client connections
2004-06-21 12:06:06.23 spid3 Server name is 'NT02\NT02_2000'.
2004-06-21 12:06:06.23 spid3 Skipping startup of clean database id 7
2004-06-21 12:06:06.23 spid8 Starting up database 'msdb'.
2004-06-21 12:06:06.23 spid9 Starting up database 'pubs'.
2004-06-21 12:06:06.23 spid10 Starting up database 'Northwind'.
2004-06-21 12:06:06.62 spid5 Clearing tempdb database.
2004-06-21 12:06:07.06 spid5 Starting up database 'tempdb'.
2004-06-21 12:06:07.15 spid3 Recovery complete.
2004-06-21 12:06:07.15 spid3 SQL global counter collection task is created.
2004-06-21 12:06:18.23 spid51 Using 'xpsqlbot.dll' version '2000.80.194' to execute extended stored procedure 'xp_qv'.
2004-06-21 12:06:32.14 spid52 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure 'sp_MSgetversion'.
2004-06-21 12:06:36.82 spid52 Starting up database 'OPENROAD_DEVELOPMENT'.
2004-06-21 12:08:25.46 spid54 Resource Manager Creation Failed: Result Code = 0x8004d01c
""Mingqing Cheng [MSFT]"" wrote:

> Hi John,
> Thanks for your prompt updates!
> First of all, as we are using distributed query, we should no longer using
> BEGIN TRANSACTION. Would you please try using BEGIN DISTRIBUTED TRANSACTION
> to see whether it will resolve your issue?
> Secondly, if above could not resolved you issue, would you please show me
> your SQL Log files? I have to use them finding the root cause of this
> issue. You could only post relative log in the newsgroup
>
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don’t hesitate to let me know. We are
> here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Microsoft Developer Community Support
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||I have tried using BEGIN DISTRIBUTED TRANSACTION but it makes no difference.
Below is a SQL Log file - the error at the end occurs when I attempt to execute the transaction.
2004-06-21 12:06:05.89 server Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
2004-06-21 12:06:05.89 server Copyright (C) 1988-2002 Microsoft Corporation.
2004-06-21 12:06:05.89 server All rights reserved.
2004-06-21 12:06:05.89 server Server Process ID is 2692.
2004-06-21 12:06:05.89 server Logging SQL Server messages in file 'f:\mssql2000\data\MSSQL$NT02_2000\log\ERRORLOG'.
2004-06-21 12:06:05.89 server SQL Server is starting at priority class 'normal'(2 CPUs detected).
2004-06-21 12:06:05.92 server SQL Server configured for thread mode processing.
2004-06-21 12:06:05.93 server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2004-06-21 12:06:05.96 server Attempting to initialize Distributed Transaction Coordinator.
2004-06-21 12:06:05.98 server Resource Manager Creation Failed: Result Code = 0x8004d01c
2004-06-21 12:06:06.00 spid3 Starting up database 'master'.
2004-06-21 12:06:06.17 spid5 Starting up database 'model'.
2004-06-21 12:06:06.17 server Using 'SSNETLIB.DLL' version '8.0.766'.
2004-06-21 12:06:06.18 server SQL server listening on 192.4.92.47: 1045.
2004-06-21 12:06:06.18 server SQL server listening on 127.0.0.1: 1045.
2004-06-21 12:06:06.21 server SQL server listening on TCP, Named Pipes.
2004-06-21 12:06:06.21 server SQL Server is ready for client connections
2004-06-21 12:06:06.23 spid3 Server name is 'NT02\NT02_2000'.
2004-06-21 12:06:06.23 spid3 Skipping startup of clean database id 7
2004-06-21 12:06:06.23 spid8 Starting up database 'msdb'.
2004-06-21 12:06:06.23 spid9 Starting up database 'pubs'.
2004-06-21 12:06:06.23 spid10 Starting up database 'Northwind'.
2004-06-21 12:06:06.62 spid5 Clearing tempdb database.
2004-06-21 12:06:07.06 spid5 Starting up database 'tempdb'.
2004-06-21 12:06:07.15 spid3 Recovery complete.
2004-06-21 12:06:07.15 spid3 SQL global counter collection task is created.
2004-06-21 12:06:18.23 spid51 Using 'xpsqlbot.dll' version '2000.80.194' to execute extended stored procedure 'xp_qv'.
2004-06-21 12:06:32.14 spid52 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure 'sp_MSgetversion'.
2004-06-21 12:06:36.82 spid52 Starting up database 'OPENROAD_DEVELOPMENT'.
2004-06-21 12:08:25.46 spid54 Resource Manager Creation Failed: Result Code = 0x8004d01c
""Mingqing Cheng [MSFT]"" wrote:

> Hi John,
> Thanks for your prompt updates!
> First of all, as we are using distributed query, we should no longer using
> BEGIN TRANSACTION. Would you please try using BEGIN DISTRIBUTED TRANSACTION
> to see whether it will resolve your issue?
> Secondly, if above could not resolved you issue, would you please show me
> your SQL Log files? I have to use them finding the root cause of this
> issue. You could only post relative log in the newsgroup
>
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don’t hesitate to let me know. We are
> here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Microsoft Developer Community Support
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>

MSDTC on server is unavailable

I have a server called SCSS01 with SQL Server 2000 installed
I have another server called NT02 with a named instance of SQL Server 2000 installed - the instance name is NT02_2000
I have set up SCSS01 as a linked server on NT02\NT02_2000
When I attempt to execute a transaction on NT02\NT02_2000 that updates a table on my linked server (SCSS01) I receive the following error
MSDTC on server 'NT02\NT02_2000' is unavailable
I think this is because the MSDTC service is called NT02 and not NT02\NT02_2000
Is this a bug or am I doing something wrong
Any help would be appreciatedIf these are Windows 2003 servers have you installed Network MSDTC?
Check the following:
How to enable network DTC access in Windows Server 2003
http://support.microsoft.com/default.aspx?scid=kb;[LN];817064
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Hi Rand
The server named NT02 is w2003 and I have already enables network DTC access - it made no difference
The server named SCSS01 is windows NT4 server
It is the names that look wrong to me - my NT02 server has MSDTC server running on it but when I run the transaction the error message indicates it is looking for an MSDTC server on 'NT02\NT02_2000'
Using SQL Server Service Manager also seems to give strange results when looking at the 'Ditributed Transaction Coordinator' service - no matter which server I look at the status bar shows 'Running \\NT02 - MSDTC
Can you give me some more help - I can provide more details if necessary.|||Hi John,
From your descriptions, I understood that you would like to use Linked
Server with an Instance in SQL Server on Windows 2003. Have I understood
you? If there is anything I misunderstood, please feel free to let me know:)
First of all, It's OK when you seeing status bar shows 'Running \\NT02 -
MSDTC' as it's an machine-level service instead of instance-level service.
Secondly, Randy's suggestion is very helpful. Have you restart the machine
after make that change? Do you use cluster server?
Thirdly, when you are attempting executing an distributed query, what's the
error message? What's your query? Could you run the query standalone in
SCSS01 and get the right result?
At last, the following KB may hit your scenrio, would you please have a
check on them according to your error message? (I keep in mind that you are
using Windows 2003 and I just want you have a look at them if the error msg
meets)
You may receive error message 8525 when you try to run a distributed
transaction on an instance of SQL Server 2000 SP3
http://support.microsoft.com/?id=834849
You may receive a 7391 error message in SQL Server 2000 when you run a
distributed transaction against a linked server after you install Microsoft
Windows XP Service Pack 2 (839279)
http://support.microsoft.com/?id=839279
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Online Support
---
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||You have understood my problem correctly.
I undertand that MSDTC is at machine level and my machine name in NT02 so the service status bar showing 'Running \\NT02 - MSDTC' is ok.
I have restarted my machine after enabling network for msdtc - it did not make any difference.
I am not using cluster server.
I can reproduce my problem with the following simple query:
begin tran
select * from scss01.web_openroad.dbo.wt_job
The error message I get when running my transaction is:
Server: Msg 8501, Level 16, State 3, Line 2
MSDTC on server 'NT02\NT02_2000' is unavailable.
Why is it looking for MSDTC on NT02\NT02_2000?
Shouldn't it be looking for MSDTC on NT02?
The same query works perfectly if I run it on SCSS01.
I have looked at the kb articles you mentioned but they do not seem to be of any help to my situation.
Do you have any more ideas?
If you need any more details then please let me know.
Thanks
John
""Mingqing Cheng [MSFT]"" wrote:
> Hi John,
> From your descriptions, I understood that you would like to use Linked
> Server with an Instance in SQL Server on Windows 2003. Have I understood
> you? If there is anything I misunderstood, please feel free to let me know:)
> First of all, It's OK when you seeing status bar shows 'Running \\NT02 -
> MSDTC' as it's an machine-level service instead of instance-level service.
> Secondly, Randy's suggestion is very helpful. Have you restart the machine
> after make that change? Do you use cluster server?
> Thirdly, when you are attempting executing an distributed query, what's the
> error message? What's your query? Could you run the query standalone in
> SCSS01 and get the right result?
> At last, the following KB may hit your scenrio, would you please have a
> check on them according to your error message? (I keep in mind that you are
> using Windows 2003 and I just want you have a look at them if the error msg
> meets)
> You may receive error message 8525 when you try to run a distributed
> transaction on an instance of SQL Server 2000 SP3
> http://support.microsoft.com/?id=834849
> You may receive a 7391 error message in SQL Server 2000 when you run a
> distributed transaction against a linked server after you install Microsoft
> Windows XP Service Pack 2 (839279)
> http://support.microsoft.com/?id=839279
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
> Sincerely yours,
> Mingqing Cheng
> Microsoft Online Support
> ---
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
>
>|||You have understood correctly.
I have restarted my machine after enabling network access for msdtc.
I can reproduce the problem with the following simple query:
begin tran
select * from scss01.web_openroad.dbo.wt_job
The error I am getting is:
Server: Msg 8501, Level 16, State 3, Line 2
MSDTC on server 'NT02\NT02_2000' is unavailable.
Why is it looking for MSDTC on server NT02\NT02_2000?
Shouldn't it be looking for MSDTC on server NT02?
The same query works perfectly on SCSS01.
Do you have any more ideas?
If you need any more details from me then let me know.
Thanks
John
""Mingqing Cheng [MSFT]"" wrote:
> Hi John,
> From your descriptions, I understood that you would like to use Linked
> Server with an Instance in SQL Server on Windows 2003. Have I understood
> you? If there is anything I misunderstood, please feel free to let me know:)
> First of all, It's OK when you seeing status bar shows 'Running \\NT02 -
> MSDTC' as it's an machine-level service instead of instance-level service.
> Secondly, Randy's suggestion is very helpful. Have you restart the machine
> after make that change? Do you use cluster server?
> Thirdly, when you are attempting executing an distributed query, what's the
> error message? What's your query? Could you run the query standalone in
> SCSS01 and get the right result?
> At last, the following KB may hit your scenrio, would you please have a
> check on them according to your error message? (I keep in mind that you are
> using Windows 2003 and I just want you have a look at them if the error msg
> meets)
> You may receive error message 8525 when you try to run a distributed
> transaction on an instance of SQL Server 2000 SP3
> http://support.microsoft.com/?id=834849
> You may receive a 7391 error message in SQL Server 2000 when you run a
> distributed transaction against a linked server after you install Microsoft
> Windows XP Service Pack 2 (839279)
> http://support.microsoft.com/?id=839279
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
> Sincerely yours,
> Mingqing Cheng
> Microsoft Online Support
> ---
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
>
>|||You have understood correctly.
I have restarted my machine after enabling network access for msdtc.
I can reproduce the problem with the following simple query:
begin tran
select * from scss01.web_openroad.dbo.wt_job
The error I am getting is:
Server: Msg 8501, Level 16, State 3, Line 2
MSDTC on server 'NT02\NT02_2000' is unavailable.
Why is it looking for MSDTC on server NT02\NT02_2000?
Shouldn't it be looking for MSDTC on server NT02?
The same query works perfectly on SCSS01.
Do you have any more ideas?
If you need any more details from me then let me know.
Thanks
John
""Mingqing Cheng [MSFT]"" wrote:
> Hi John,
> From your descriptions, I understood that you would like to use Linked
> Server with an Instance in SQL Server on Windows 2003. Have I understood
> you? If there is anything I misunderstood, please feel free to let me know:)
> First of all, It's OK when you seeing status bar shows 'Running \\NT02 -
> MSDTC' as it's an machine-level service instead of instance-level service.
> Secondly, Randy's suggestion is very helpful. Have you restart the machine
> after make that change? Do you use cluster server?
> Thirdly, when you are attempting executing an distributed query, what's the
> error message? What's your query? Could you run the query standalone in
> SCSS01 and get the right result?
> At last, the following KB may hit your scenrio, would you please have a
> check on them according to your error message? (I keep in mind that you are
> using Windows 2003 and I just want you have a look at them if the error msg
> meets)
> You may receive error message 8525 when you try to run a distributed
> transaction on an instance of SQL Server 2000 SP3
> http://support.microsoft.com/?id=834849
> You may receive a 7391 error message in SQL Server 2000 when you run a
> distributed transaction against a linked server after you install Microsoft
> Windows XP Service Pack 2 (839279)
> http://support.microsoft.com/?id=839279
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
> Sincerely yours,
> Mingqing Cheng
> Microsoft Online Support
> ---
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
>
>|||Hi John,
Thanks for your prompt updates!
First of all, as we are using distributed query, we should no longer using
BEGIN TRANSACTION. Would you please try using BEGIN DISTRIBUTED TRANSACTION
to see whether it will resolve your issue?
Secondly, if above could not resolved you issue, would you please show me
your SQL Log files? I have to use them finding the root cause of this
issue. You could only post relative log in the newsgroup :)
Thank you for your patience and cooperation. If you have any questions or
concerns, don?t hesitate to let me know. We are
here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||I have tried using BEGIN DISTRIBUTED TRANSACTION but it makes no difference.
Below is a SQL Log file - the error at the end occurs when I attempt to execute the transaction.
2004-06-21 12:06:05.89 server Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
2004-06-21 12:06:05.89 server Copyright (C) 1988-2002 Microsoft Corporation.
2004-06-21 12:06:05.89 server All rights reserved.
2004-06-21 12:06:05.89 server Server Process ID is 2692.
2004-06-21 12:06:05.89 server Logging SQL Server messages in file 'f:\mssql2000\data\MSSQL$NT02_2000\log\ERRORLOG'.
2004-06-21 12:06:05.89 server SQL Server is starting at priority class 'normal'(2 CPUs detected).
2004-06-21 12:06:05.92 server SQL Server configured for thread mode processing.
2004-06-21 12:06:05.93 server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2004-06-21 12:06:05.96 server Attempting to initialize Distributed Transaction Coordinator.
2004-06-21 12:06:05.98 server Resource Manager Creation Failed: Result Code = 0x8004d01c
2004-06-21 12:06:06.00 spid3 Starting up database 'master'.
2004-06-21 12:06:06.17 spid5 Starting up database 'model'.
2004-06-21 12:06:06.17 server Using 'SSNETLIB.DLL' version '8.0.766'.
2004-06-21 12:06:06.18 server SQL server listening on 192.4.92.47: 1045.
2004-06-21 12:06:06.18 server SQL server listening on 127.0.0.1: 1045.
2004-06-21 12:06:06.21 server SQL server listening on TCP, Named Pipes.
2004-06-21 12:06:06.21 server SQL Server is ready for client connections
2004-06-21 12:06:06.23 spid3 Server name is 'NT02\NT02_2000'.
2004-06-21 12:06:06.23 spid3 Skipping startup of clean database id 7
2004-06-21 12:06:06.23 spid8 Starting up database 'msdb'.
2004-06-21 12:06:06.23 spid9 Starting up database 'pubs'.
2004-06-21 12:06:06.23 spid10 Starting up database 'Northwind'.
2004-06-21 12:06:06.62 spid5 Clearing tempdb database.
2004-06-21 12:06:07.06 spid5 Starting up database 'tempdb'.
2004-06-21 12:06:07.15 spid3 Recovery complete.
2004-06-21 12:06:07.15 spid3 SQL global counter collection task is created.
2004-06-21 12:06:18.23 spid51 Using 'xpsqlbot.dll' version '2000.80.194' to execute extended stored procedure 'xp_qv'.
2004-06-21 12:06:32.14 spid52 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure 'sp_MSgetversion'.
2004-06-21 12:06:36.82 spid52 Starting up database 'OPENROAD_DEVELOPMENT'.
2004-06-21 12:08:25.46 spid54 Resource Manager Creation Failed: Result Code = 0x8004d01c
""Mingqing Cheng [MSFT]"" wrote:
> Hi John,
> Thanks for your prompt updates!
> First of all, as we are using distributed query, we should no longer using
> BEGIN TRANSACTION. Would you please try using BEGIN DISTRIBUTED TRANSACTION
> to see whether it will resolve your issue?
> Secondly, if above could not resolved you issue, would you please show me
> your SQL Log files? I have to use them finding the root cause of this
> issue. You could only post relative log in the newsgroup :)
>
> Thank you for your patience and cooperation. If you have any questions or
> concerns, donâ't hesitate to let me know. We are
> here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Microsoft Developer Community Support
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||I have tried using BEGIN DISTRIBUTED TRANSACTION but it makes no difference.
Below is a SQL Log file - the error at the end occurs when I attempt to execute the transaction.
2004-06-21 12:06:05.89 server Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
2004-06-21 12:06:05.89 server Copyright (C) 1988-2002 Microsoft Corporation.
2004-06-21 12:06:05.89 server All rights reserved.
2004-06-21 12:06:05.89 server Server Process ID is 2692.
2004-06-21 12:06:05.89 server Logging SQL Server messages in file 'f:\mssql2000\data\MSSQL$NT02_2000\log\ERRORLOG'.
2004-06-21 12:06:05.89 server SQL Server is starting at priority class 'normal'(2 CPUs detected).
2004-06-21 12:06:05.92 server SQL Server configured for thread mode processing.
2004-06-21 12:06:05.93 server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2004-06-21 12:06:05.96 server Attempting to initialize Distributed Transaction Coordinator.
2004-06-21 12:06:05.98 server Resource Manager Creation Failed: Result Code = 0x8004d01c
2004-06-21 12:06:06.00 spid3 Starting up database 'master'.
2004-06-21 12:06:06.17 spid5 Starting up database 'model'.
2004-06-21 12:06:06.17 server Using 'SSNETLIB.DLL' version '8.0.766'.
2004-06-21 12:06:06.18 server SQL server listening on 192.4.92.47: 1045.
2004-06-21 12:06:06.18 server SQL server listening on 127.0.0.1: 1045.
2004-06-21 12:06:06.21 server SQL server listening on TCP, Named Pipes.
2004-06-21 12:06:06.21 server SQL Server is ready for client connections
2004-06-21 12:06:06.23 spid3 Server name is 'NT02\NT02_2000'.
2004-06-21 12:06:06.23 spid3 Skipping startup of clean database id 7
2004-06-21 12:06:06.23 spid8 Starting up database 'msdb'.
2004-06-21 12:06:06.23 spid9 Starting up database 'pubs'.
2004-06-21 12:06:06.23 spid10 Starting up database 'Northwind'.
2004-06-21 12:06:06.62 spid5 Clearing tempdb database.
2004-06-21 12:06:07.06 spid5 Starting up database 'tempdb'.
2004-06-21 12:06:07.15 spid3 Recovery complete.
2004-06-21 12:06:07.15 spid3 SQL global counter collection task is created.
2004-06-21 12:06:18.23 spid51 Using 'xpsqlbot.dll' version '2000.80.194' to execute extended stored procedure 'xp_qv'.
2004-06-21 12:06:32.14 spid52 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure 'sp_MSgetversion'.
2004-06-21 12:06:36.82 spid52 Starting up database 'OPENROAD_DEVELOPMENT'.
2004-06-21 12:08:25.46 spid54 Resource Manager Creation Failed: Result Code = 0x8004d01c
""Mingqing Cheng [MSFT]"" wrote:
> Hi John,
> Thanks for your prompt updates!
> First of all, as we are using distributed query, we should no longer using
> BEGIN TRANSACTION. Would you please try using BEGIN DISTRIBUTED TRANSACTION
> to see whether it will resolve your issue?
> Secondly, if above could not resolved you issue, would you please show me
> your SQL Log files? I have to use them finding the root cause of this
> issue. You could only post relative log in the newsgroup :)
>
> Thank you for your patience and cooperation. If you have any questions or
> concerns, donâ't hesitate to let me know. We are
> here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Microsoft Developer Community Support
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||Hi John,
To solve this issue, would you please try the following steps?
1. If you are using SQL cluster, make sure MSDTC has been installed on the
cluster
successfully, and make sure you can ping the MSDTC resource's Network name?
The following documents may be helpful to rebuild MSDTC on your cluster
server
HOWTO: Rebuild or Move MSDTC Used with a SQL Failover Cluster
http://support.microsoft.com/?id=294209
2. Please make sure you have configured the MSDTC on the Windows NT/2003 by
trying the steps mentioned below successfully, then try to reboot the
machine.
After you reboot the machine, start MSDTC and then start MSSQLServer.
--Confiuration for MSDTC on Windows 2003
a. Click "Start", point to "All Programs", point to "Administrative Tools",
and then click "Component Services".
b. In the Component Services Wizard, expand "Component Services", and then
double-click "Computers".
c. Right-click "My Computer", and then click "Properties".
d. Click the "MSDTC" tab, and then click "Security Configuration".
e. In the "Security Configuration" dialog box, click to select the "Network
DTC Access" check box.
f. Under "Network DTC Access", click "Network Transactions".
g. Make sure that "DTC Logon Account" is set to "NT
Authority\NetworkService".
--End of Configuration
3. If it still does not work, try to restart the MSDTC on the Windows NT
machine, then restart MSSQLServer on that machine.
4. If it still does not, on both machines, open regedt32.exe, go to
HKEY_LOCAL_MACHINE\Software\Microsoft\MSDTC, add a DWORD named
TurnOffRpcSecurity
with the value 1 under this key, then restart MSDTC to see if it works.
If all above still could not resolved issue, what's the detailed SQL Server
and Windows Error message for the failure of running distributed
transaction error?
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Changing the DTC Logon Account has solved my problem.
The log on account for my DTC service was set to 'Local System Account'
I assume the problem is that the local system account does not have network access rights.
It seems strange that the default log on account for DTC is the local system account because surely the whole purpose of the DTC service means that it will always need network access rights.
I am very grateful for your speedy response to this problem.
Thank you
John
""Mingqing Cheng [MSFT]"" wrote:
> Hi John,
> To solve this issue, would you please try the following steps?
> 1. If you are using SQL cluster, make sure MSDTC has been installed on the
> cluster
> successfully, and make sure you can ping the MSDTC resource's Network name?
> The following documents may be helpful to rebuild MSDTC on your cluster
> server
> HOWTO: Rebuild or Move MSDTC Used with a SQL Failover Cluster
> http://support.microsoft.com/?id=294209
> 2. Please make sure you have configured the MSDTC on the Windows NT/2003 by
> trying the steps mentioned below successfully, then try to reboot the
> machine.
> After you reboot the machine, start MSDTC and then start MSSQLServer.
> --Confiuration for MSDTC on Windows 2003
> a. Click "Start", point to "All Programs", point to "Administrative Tools",
> and then click "Component Services".
> b. In the Component Services Wizard, expand "Component Services", and then
> double-click "Computers".
> c. Right-click "My Computer", and then click "Properties".
> d. Click the "MSDTC" tab, and then click "Security Configuration".
> e. In the "Security Configuration" dialog box, click to select the "Network
> DTC Access" check box.
> f. Under "Network DTC Access", click "Network Transactions".
> g. Make sure that "DTC Logon Account" is set to "NT
> Authority\NetworkService".
> --End of Configuration
>
> 3. If it still does not work, try to restart the MSDTC on the Windows NT
> machine, then restart MSSQLServer on that machine.
> 4. If it still does not, on both machines, open regedt32.exe, go to
> HKEY_LOCAL_MACHINE\Software\Microsoft\MSDTC, add a DWORD named
> TurnOffRpcSecurity
> with the value 1 under this key, then restart MSDTC to see if it works.
> If all above still could not resolved issue, what's the detailed SQL Server
> and Windows Error message for the failure of running distributed
> transaction error?
>
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Microsoft Developer Community Support
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>