DTS export (lincensing problem)

I have a problem with licencing mode in sqlserver when i use I export table
from database to another database on different servers
On the same server the dts export run correctely between 2 database
but on different servers i have this message
The servers are on sqlserver 7 with tha same configuration

The licensing mode of sqlserver don t permit to run the DTS export

Where i can change this parameter

RegardsCall your vendor and buy the licenses. It will automagically change for you.|||Hello

We have buy the licence but the problem is output ?

DTS and bcp

I heard that underlying the DTS Import/export wizard is bcp.. Is that true ?
I wanted to move data between 2 servers and it seemed bcp first copies to a
file and then you can import from the file into the destination table
File will be created in the script file directory when you copy objects and
data.
If your servers are not connected then you may want to use backup/restore or
detach/attach as a quicker transfer method.
http://support.microsoft.com/default...22120121120120
John
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eLID4h2UFHA.3056@.TK2MSFTNGP14.phx.gbl...
>I heard that underlying the DTS Import/export wizard is bcp.. Is that true
>?
> I wanted to move data between 2 servers and it seemed bcp first copies to
> a
> file and then you can import from the file into the destination table
>
>

DTS and bcp

I heard that underlying the DTS Import/export wizard is bcp.. Is that true ?
I wanted to move data between 2 servers and it seemed bcp first copies to a
file and then you can import from the file into the destination tableFile will be created in the script file directory when you copy objects and
data.
If your servers are not connected then you may want to use backup/restore or
detach/attach as a quicker transfer method.
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546#XSLTH3151121122120121120120
John
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eLID4h2UFHA.3056@.TK2MSFTNGP14.phx.gbl...
>I heard that underlying the DTS Import/export wizard is bcp.. Is that true
>?
> I wanted to move data between 2 servers and it seemed bcp first copies to
> a
> file and then you can import from the file into the destination table
>
>

DTS and bcp

I heard that underlying the DTS Import/export wizard is bcp.. Is that true ?
I wanted to move data between 2 servers and it seemed bcp first copies to a
file and then you can import from the file into the destination tableFile will be created in the script file directory when you copy objects and
data.
If your servers are not connected then you may want to use backup/restore or
detach/attach as a quicker transfer method.
121120120" target="_blank">http://support.microsoft.com/defaul...r />
121120120
John
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eLID4h2UFHA.3056@.TK2MSFTNGP14.phx.gbl...
>I heard that underlying the DTS Import/export wizard is bcp.. Is that true
>?
> I wanted to move data between 2 servers and it seemed bcp first copies to
> a
> file and then you can import from the file into the destination table
>
>

DTS / Export Data in Sql2005 Mgm Studio

Dear all,

I have a SQL 2000 and SQL 2005 servers separately, but by whatever reason, I can't use SQL 2000 Enterprise Mgr anymore now so I have to use SQL 2005 Mgm studio to open up my SQL 2000 databases now.

However, I'd like to export data from Sql 2000 to another Sql 2000 server, but via a Sql 2005 mgm studio interface right now. How can I do so? I can't find "Export Data" from the "Task" context menu in Sql 2005 mgm studio.

Thanks,

Hi,

if you want to use the SQL Server MS for that you have to right click on whatever database on choose Tasks..Export data. That should be straight forward for you like in SQL 2k days.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

DTS - Strange Behavior

Hi,
I've created quite a few DTS packages that open up VPN connections to remote
SQL Servers, transfer data to local SQL Servers, writes audit records, then
close the VPN connections. Everything has been working well. Saturday all
of the packages (scheduled as jobs) began to fail. Friday we did do some
physical server migrations but the SQL Servers were not touched. Nothing
else has changed. When I examined the DTS packages I noticed that if I
execute the package manually none of the tasks ever execute; they stay in a
"Waiting" state with a yellow icon. This occurs for both Meta Data and SQL
Server stored packages. If I open the package in design mode, I get the
same "Waiting" behavior. Eventually an error is returned in either case
which states:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
not exist or access is denied.
After the error for each step it lists "Not Run" with a white X in a black
circle. In the past successful execution of the package would list success
(green icon) for each step moving down through the package as each step
succeeded (as expected).
The funny thing is if I open the package up in design mode and execute any
individual step, the the step executes successfully. I.e., individual steps
work but not the whole DTS package whether opened in design mode or not.
If I create a new test DTS package, the package works as expected. If I
save one of the existing DTS packages out to a DTS file and import it back
into DTS and save it under a different name, same failure behavior as listed
above. If I create a new DTS package and copy/paste the steps individually
(from a failing package) into a new package and then save it, the package
works as expected.
Any ideas? I need to get this working again because were talking about
millions of records. And preferably, I would rather not spend two days
recreating the same packages over again.
Thanks
Jerry
PS - The double post is in leu of time constraints.
Also,
This is Windows 2000 SP4 SQL Server 2000 SP4. No errors are reported in the
DTS error log.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>
|||Also,
This is Windows 2000 SP4 SQL Server 2000 SP4. No errors are reported in the
DTS error log.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>
|||Got it. For future views of this post:
The issue lied in the fact that logging for the package execution was going
to another SQL Server that was taken down (testing box). Updating the
logging to the prod box (which should have occurred at the begining) fixed
the issue.
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>

DTS - Strange Behavior

Hi,
I've created quite a few DTS packages that open up VPN connections to remote
SQL Servers, transfer data to local SQL Servers, writes audit records, then
close the VPN connections. Everything has been working well. Saturday all
of the packages (scheduled as jobs) began to fail. Friday we did do some
physical server migrations but the SQL Servers were not touched. Nothing
else has changed. When I examined the DTS packages I noticed that if I
execute the package manually none of the tasks ever execute; they stay in a
"Waiting" state with a yellow icon. This occurs for both Meta Data and SQL
Server stored packages. If I open the package in design mode, I get the
same "Waiting" behavior. Eventually an error is returned in either case
which states:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server
does
not exist or access is denied.
After the error for each step it lists "Not Run" with a white X in a black
circle. In the past successful execution of the package would list success
(green icon) for each step moving down through the package as each step
succeeded (as expected).
The funny thing is if I open the package up in design mode and execute any
individual step, the the step executes successfully. I.e., individual steps
work but not the whole DTS package whether opened in design mode or not.
If I create a new test DTS package, the package works as expected. If I
save one of the existing DTS packages out to a DTS file and import it back
into DTS and save it under a different name, same failure behavior as listed
above. If I create a new DTS package and copy/paste the steps individually
(from a failing package) into a new package and then save it, the package
works as expected.
Any ideas? I need to get this working again because were talking about
millions of records. And preferably, I would rather not spend two days
recreating the same packages over again.
Thanks
Jerry
PS - The double post is in leu of time constraints.Also,
This is Windows 2000 SP4 SQL Server 2000 SP4. No errors are reported in the
DTS error log.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Serv
er does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>|||Also,
This is Windows 2000 SP4 SQL Server 2000 SP4. No errors are reported in the
DTS error log.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Serv
er does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>|||Got it. For future views of this post:
The issue lied in the fact that logging for the package execution was going
to another SQL Server that was taken down (testing box). Updating the
logging to the prod box (which should have occurred at the begining) fixed
the issue.
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Serv
er does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>

DTS - Strange Behavior

Hi,
I've created quite a few DTS packages that open up VPN connections to remote
SQL Servers, transfer data to local SQL Servers, writes audit records, then
close the VPN connections. Everything has been working well. Saturday all
of the packages (scheduled as jobs) began to fail. Friday we did do some
physical server migrations but the SQL Servers were not touched. Nothing
else has changed. When I examined the DTS packages I noticed that if I
execute the package manually none of the tasks ever execute; they stay in a
"Waiting" state with a yellow icon. This occurs for both Meta Data and SQL
Server stored packages. If I open the package in design mode, I get the
same "Waiting" behavior. Eventually an error is returned in either case
which states:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
not exist or access is denied.
After the error for each step it lists "Not Run" with a white X in a black
circle. In the past successful execution of the package would list success
(green icon) for each step moving down through the package as each step
succeeded (as expected).
The funny thing is if I open the package up in design mode and execute any
individual step, the the step executes successfully. I.e., individual steps
work but not the whole DTS package whether opened in design mode or not.
If I create a new test DTS package, the package works as expected. If I
save one of the existing DTS packages out to a DTS file and import it back
into DTS and save it under a different name, same failure behavior as listed
above. If I create a new DTS package and copy/paste the steps individually
(from a failing package) into a new package and then save it, the package
works as expected.
Any ideas? I need to get this working again because were talking about
millions of records. And preferably, I would rather not spend two days
recreating the same packages over again.
Thanks
Jerry
PS - The double post is in leu of time constraints.Also,
This is Windows 2000 SP4 SQL Server 2000 SP4. No errors are reported in the
DTS error log.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>|||Also,
This is Windows 2000 SP4 SQL Server 2000 SP4. No errors are reported in the
DTS error log.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>|||Got it. For future views of this post:
The issue lied in the fact that logging for the package execution was going
to another SQL Server that was taken down (testing box). Updating the
logging to the prod box (which should have occurred at the begining) fixed
the issue.
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>

DTS - Problem with Image field

DTS is not properly exporting a database between the servers when a table is containing a image field

Reason it gives as

::Error::
Transfer Status: Transferring Data: (Table '[dbo].[Here comes the table name used ]')
Data truncation occurred in table [dbo].[Here comes the table name used ], column 1.

any workaround or solutions would be more appericated

any help would be more appericated and important to find a solution

|||

Maybe there is some image data larger than 8388602 bytes being transfered using Copy SQL Server Objects task, in this case try Import/Export Wizard instead.

And there are some other considerations you should take when using DTS to transfer BLOB data (image, text, ntext), please take a look at:

Data Conversion and Transformation Considerations

|||

that is excatly the problem , DTS truncates BLOB data and shows the error.

But how to resolve it , Iam using sql server 2000 and win xp in local server.

is BLOB data is still a issue in sql server 2000 or it is solved.

|||If you use Import/Export wizard to transfer the BOLB data (you can also save this taks to DTS package), will the same error be raised?|||

Import/Export Wizard will go to DTS package only .

Ok.

What is the command to find the sql sever installed in the system and the service pack?

Help would be appericated in getting the command

|||

Try T-SQL command:

SELECT @.@.VERSION

|||

It is showing in the sql query analyser

I wanted the command to be executed in the command prompt

and how to know the version of sp installed in the system

|||Then how about to exeute the T-SQL command in osql in command prompt|||

Can u give me the exact command.

What is the star which appears on left side along the thread

|||

Ganesh@.Nilgris:

Can u give me the exact command.

osql is a command-line utility which allows you to execute T-SQL commands So just start osql in command prompt then type T-SQL command as you can do in Query Analyzer and remember to enter "go" to submit a batch:

C:\Documents and Settings\Administrator>osql -E
1> select @.@.version
2> go

-----------------

---------------

---------------

---------------

--
Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
Apr 14 2006 01:12:25
C
opyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition on Wind
ows NT 5.2 (Build 3790: Service Pack 1)

(1 row affected)

|||

Thats was a good one, It was usefull in learinig sql commands in cmd prompt

But how do u still find the service pack .

DTS - 5 million row load with indexes?

When loading large quantities between servers via DTS, I'm wondering
what's best to do with a table that has an index on it. It has about
10 columns and has a Primary key on the first 2 columns. Is the index
having to do extra work as the data load progresses? Would it be
better to load the data in an unindexed table, and then reindex it
using a tsql statement?The table that you are loading into - is that an empty table?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122554831.421049.180070@.g14g2000cwa.googlegroups.com...
When loading large quantities between servers via DTS, I'm wondering
what's best to do with a table that has an index on it. It has about
10 columns and has a Primary key on the first 2 columns. Is the index
having to do extra work as the data load progresses? Would it be
better to load the data in an unindexed table, and then reindex it
using a tsql statement?|||Yes - empty table. But after the data comes in I'll be doing some
processing and will need the indexes.|||Good. If it is an empty table, then you could keep the clustered index and
still load the data quickly using BULK INSERT command, provided the data is
coming from a text file. BUKK INSERT command allows you to specify an ORDER
parameter which indicates the order of the clustered index columns. You just
have to make sure the text file is also sorted in the same order as the
clustered index.
If you are loading from a SQL Server database table, then I suggest you load
the data into the empty table, without any indexes. After the data load
create the clustered index.
Please search Google for the following keywords. There are some good
resources out there on this topic:
optimizing data load performance sql server
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122559237.926405.177340@.g49g2000cwa.googlegroups.com...
Yes - empty table. But after the data comes in I'll be doing some
processing and will need the indexes.|||It is in fact coming from another SQL Server, and you're right -
loading without the index perfomed much better. Then I recreated the
indexes and this did not take very long. Text file loading sounds
pretty fast too, but I would have to unload to text file from the
source sql server then load back in, so i figured this way would be
just as fast.|||unc27932@.yahoo.com wrote:
> It is in fact coming from another SQL Server, and you're right -
> loading without the index perfomed much better. Then I recreated the
> indexes and this did not take very long. Text file loading sounds
> pretty fast too, but I would have to unload to text file from the
> source sql server then load back in, so i figured this way would be
> just as fast.
If you're coming from another SQL Server, have you tried selecting the
data from the "other" table in the clustered index key order of the
destination table. That would likely help insert speed a lot and might
allow you to keep the clustered index in place without undue stress
during insert.
David Gugick
Quest Software
www.imceda.com
www.quest.com

DTS - 5 million row load with indexes?

When loading large quantities between servers via DTS, I'm wondering
what's best to do with a table that has an index on it. It has about
10 columns and has a Primary key on the first 2 columns. Is the index
having to do extra work as the data load progresses? Would it be
better to load the data in an unindexed table, and then reindex it
using a tsql statement?The table that you are loading into - is that an empty table?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122554831.421049.180070@.g14g2000cwa.googlegroups.com...
When loading large quantities between servers via DTS, I'm wondering
what's best to do with a table that has an index on it. It has about
10 columns and has a Primary key on the first 2 columns. Is the index
having to do extra work as the data load progresses? Would it be
better to load the data in an unindexed table, and then reindex it
using a tsql statement?|||Yes - empty table. But after the data comes in I'll be doing some
processing and will need the indexes.|||Good. If it is an empty table, then you could keep the clustered index and
still load the data quickly using BULK INSERT command, provided the data is
coming from a text file. BUKK INSERT command allows you to specify an ORDER
parameter which indicates the order of the clustered index columns. You just
have to make sure the text file is also sorted in the same order as the
clustered index.
If you are loading from a SQL Server database table, then I suggest you load
the data into the empty table, without any indexes. After the data load
create the clustered index.
Please search Google for the following keywords. There are some good
resources out there on this topic:
optimizing data load performance sql server
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122559237.926405.177340@.g49g2000cwa.googlegroups.com...
Yes - empty table. But after the data comes in I'll be doing some
processing and will need the indexes.|||It is in fact coming from another SQL Server, and you're right -
loading without the index perfomed much better. Then I recreated the
indexes and this did not take very long. Text file loading sounds
pretty fast too, but I would have to unload to text file from the
source sql server then load back in, so i figured this way would be
just as fast.|||unc27932@.yahoo.com wrote:
> It is in fact coming from another SQL Server, and you're right -
> loading without the index perfomed much better. Then I recreated the
> indexes and this did not take very long. Text file loading sounds
> pretty fast too, but I would have to unload to text file from the
> source sql server then load back in, so i figured this way would be
> just as fast.
If you're coming from another SQL Server, have you tried selecting the
data from the "other" table in the clustered index key order of the
destination table. That would likely help insert speed a lot and might
allow you to keep the clustered index in place without undue stress
during insert.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com

DTS - 5 million row load with indexes?

When loading large quantities between servers via DTS, I'm wondering
what's best to do with a table that has an index on it. It has about
10 columns and has a Primary key on the first 2 columns. Is the index
having to do extra work as the data load progresses? Would it be
better to load the data in an unindexed table, and then reindex it
using a tsql statement?
The table that you are loading into - is that an empty table?
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122554831.421049.180070@.g14g2000cwa.googlegr oups.com...
When loading large quantities between servers via DTS, I'm wondering
what's best to do with a table that has an index on it. It has about
10 columns and has a Primary key on the first 2 columns. Is the index
having to do extra work as the data load progresses? Would it be
better to load the data in an unindexed table, and then reindex it
using a tsql statement?
|||Yes - empty table. But after the data comes in I'll be doing some
processing and will need the indexes.
|||Good. If it is an empty table, then you could keep the clustered index and
still load the data quickly using BULK INSERT command, provided the data is
coming from a text file. BUKK INSERT command allows you to specify an ORDER
parameter which indicates the order of the clustered index columns. You just
have to make sure the text file is also sorted in the same order as the
clustered index.
If you are loading from a SQL Server database table, then I suggest you load
the data into the empty table, without any indexes. After the data load
create the clustered index.
Please search Google for the following keywords. There are some good
resources out there on this topic:
optimizing data load performance sql server
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122559237.926405.177340@.g49g2000cwa.googlegr oups.com...
Yes - empty table. But after the data comes in I'll be doing some
processing and will need the indexes.
|||It is in fact coming from another SQL Server, and you're right -
loading without the index perfomed much better. Then I recreated the
indexes and this did not take very long. Text file loading sounds
pretty fast too, but I would have to unload to text file from the
source sql server then load back in, so i figured this way would be
just as fast.
|||unc27932@.yahoo.com wrote:
> It is in fact coming from another SQL Server, and you're right -
> loading without the index perfomed much better. Then I recreated the
> indexes and this did not take very long. Text file loading sounds
> pretty fast too, but I would have to unload to text file from the
> source sql server then load back in, so i figured this way would be
> just as fast.
If you're coming from another SQL Server, have you tried selecting the
data from the "other" table in the clustered index key order of the
destination table. That would likely help insert speed a lot and might
allow you to keep the clustered index in place without undue stress
during insert.
David Gugick
Quest Software
www.imceda.com
www.quest.com

DTC restart shutdown SQL server, why?

SQL 2000 SP4 standard on Windows 2003 Enterprise.

On Friday our DTC service restarted on a couple of servers (we think cause of a config change made by the network guys) but on one server it also shutdown the sql server... We cannot find anything that indicates why. I have looked and cannot find any reason why SQL would be a require DTC to be running expecially since none of the other servers shut down.

The only difference in this one server as compared to the others is it is a publisher for merge replication and the other servers are either a subscriber or publisher of transactional. Would this have anything to do with it?

Also is SQL actually dependant on DTC for any reason that would cause it to be shutdown when DTC is restarted?

A down MSDTC shouldn't cause your sqlserver to stop running. There must be something else in the loop. What can you find in sql logs (i.e. errorlog.*).

DTC Problem - Unable to begin a transaction

I have a strange one. I have 2 Win2K3 servers, one running SQL 2K5 and the
other 2K SP4. From the 2K5 SQL Server I issue the following query ...
declare @.tmpStr varchar(2000);
declare @.ib table (EventType nvarchar(30), Parameters int, EventInfo
nvarchar(255));
set @.tmpstr = 'exec(''dbcc inputbuffer(71) WITH NO_INFOMSGS'') at
[SQL200Server];';
insert into @.ib
exec (@.tmpStr);
and I get the following error messages ...
OLE DB provider "SQLNCLI" for linked server "SQL2000Server" returned message
"No transaction is active.".
Msg 7391, Level 16, State 2, Line 1
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "SQL2000Server" was unable to begin a distributed transaction.
If I simply comment out the 'insert into @.ib' line, it works fine. We
apparently still have something misconfigured in DTC between the servers, bu
t
I can't figure out what? Any ideas anyone? Thanks in advance.Arghknork wrote:
> If I simply comment out the 'insert into @.ib' line, it works fine. We
> apparently still have something misconfigured in DTC between the servers,
but
> I can't figure out what? Any ideas anyone? Thanks in advance.
>
Network access to MSDTC is disabled by default on a Windows 2003 Server
machine.
http://blogs.msdn.com/mab/archive/2.../30/508273.aspx|||Arghknork wrote:
> If I simply comment out the 'insert into @.ib' line, it works fine. We
> apparently still have something misconfigured in DTC between the servers,
but
> I can't figure out what? Any ideas anyone? Thanks in advance.
>
Network access to MSDTC is disabled by default on a Windows 2003 Server
machine.
http://blogs.msdn.com/mab/archive/2.../30/508273.aspx|||True, and we have it enabled. This has to be something more subtile than
that. Both these machines participate in other DTC activity with other
servers, they just don't want to play nice together in this one specific
incident. As I mentioned, I can comment out the onbe line and get a correct
response. It's only when I try to return the result set back into a table
variable that I get the failure. Thanks for the response.
"Tracy McKibben" wrote:

> Arghknork wrote:
> Network access to MSDTC is disabled by default on a Windows 2003 Server
> machine.
> http://blogs.msdn.com/mab/archive/2.../30/508273.aspx
>|||True, and we have it enabled. This has to be something more subtile than
that. Both these machines participate in other DTC activity with other
servers, they just don't want to play nice together in this one specific
incident. As I mentioned, I can comment out the onbe line and get a correct
response. It's only when I try to return the result set back into a table
variable that I get the failure. Thanks for the response.
"Tracy McKibben" wrote:

> Arghknork wrote:
> Network access to MSDTC is disabled by default on a Windows 2003 Server
> machine.
> http://blogs.msdn.com/mab/archive/2.../30/508273.aspx
>

DTC Problem - Unable to begin a transaction

I have a strange one. I have 2 Win2K3 servers, one running SQL 2K5 and the
other 2K SP4. From the 2K5 SQL Server I issue the following query ...
declare @.tmpStr varchar(2000);
declare @.ib table (EventType nvarchar(30), Parameters int, EventInfo
nvarchar(255));
set @.tmpstr = 'exec(''dbcc inputbuffer(71) WITH NO_INFOMSGS'') at
[SQL200Server];';
insert into @.ib
exec (@.tmpStr);
and I get the following error messages ...
OLE DB provider "SQLNCLI" for linked server "SQL2000Server" returned message
"No transaction is active.".
Msg 7391, Level 16, State 2, Line 1
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "SQL2000Server" was unable to begin a distributed transaction.
If I simply comment out the 'insert into @.ib' line, it works fine. We
apparently still have something misconfigured in DTC between the servers, but
I can't figure out what? Any ideas anyone? Thanks in advance.Arghknork wrote:
> If I simply comment out the 'insert into @.ib' line, it works fine. We
> apparently still have something misconfigured in DTC between the servers, but
> I can't figure out what? Any ideas anyone? Thanks in advance.
>
Network access to MSDTC is disabled by default on a Windows 2003 Server
machine.
http://blogs.msdn.com/mab/archive/2005/12/30/508273.aspx|||True, and we have it enabled. This has to be something more subtile than
that. Both these machines participate in other DTC activity with other
servers, they just don't want to play nice together in this one specific
incident. As I mentioned, I can comment out the onbe line and get a correct
response. It's only when I try to return the result set back into a table
variable that I get the failure. Thanks for the response.
"Tracy McKibben" wrote:
> Arghknork wrote:
> > If I simply comment out the 'insert into @.ib' line, it works fine. We
> > apparently still have something misconfigured in DTC between the servers, but
> > I can't figure out what? Any ideas anyone? Thanks in advance.
> >
> >
> Network access to MSDTC is disabled by default on a Windows 2003 Server
> machine.
> http://blogs.msdn.com/mab/archive/2005/12/30/508273.aspx
>

DTC Fails

Please Help!...Recently we started seeing DTC failures between two of our
servers. The calling server(A) is Win2003 and the destination (B) is Win2K. I
t was working a week ago and our Network gurus say nothing has changed on the
network.
I've set the "TurnOffRpcSecurity" per KB article #827805. Both boxes are
in the same domain and network segment with no firewall between them. I also
added entries to the host and lmhost files on both.
I downloaded and ran both DTCPing and DTCTester utilties. DTCPing succeeds
when run from A, but not from B (it hangs in the bind phase after
successfully completing the RPC portion but no error is retuned)The DTCTester
returns the following when run from either:
Executed: dtctester
DSN: dtctest
User Name: sa
Password:
tablename= #dtc5330
Creating Temp Table for Testing: #dtc5330
Warning: No Columns in Result Set From Executing: 'create table #dtc5330
(ival i
nt)'
Initializing DTC
Beginning DTC Transaction
Enlisting Connection in Transaction
Error:
SQLSTATE=25S12,Native error=-2147168242,msg='[Microsoft][ODBC SQL Server
Driver]
Distributed transaction error'
Error:
SQLSTATE=24000,Native error=0,msg=[Microsoft][ODBC SQL Server Driver]Invalid
cur
sor state
Typical Errors in DTC Output When
a. Firewall Has Ports Closed
-OR-
b. Bad WINS/DNS entries
-OR-
c. Misconfigured network
-OR-
d. Misconfigured SQL Server machine that has multiple netcards.
Aborting DTC Transaction
Releasing DTC Interface Pointers
Successfully Released pTransaction Pointer.If the 2003 server has had sp1 installed recently, this may be your
problem.You will need to change the security settings in DCOM.
"Joe Loyd" wrote:
> Please Help!...Recently we started seeing DTC failures between two of our
> servers. The calling server(A) is Win2003 and the destination (B) is Win2K. I
> t was working a week ago and our Network gurus say nothing has changed on the
> network.
> I've set the "TurnOffRpcSecurity" per KB article #827805. Both boxes are
> in the same domain and network segment with no firewall between them. I also
> added entries to the host and lmhost files on both.
> I downloaded and ran both DTCPing and DTCTester utilties. DTCPing succeeds
> when run from A, but not from B (it hangs in the bind phase after
> successfully completing the RPC portion but no error is retuned)The DTCTester
> returns the following when run from either:
> Executed: dtctester
> DSN: dtctest
> User Name: sa
> Password:
> tablename= #dtc5330
> Creating Temp Table for Testing: #dtc5330
> Warning: No Columns in Result Set From Executing: 'create table #dtc5330
> (ival i
> nt)'
> Initializing DTC
> Beginning DTC Transaction
> Enlisting Connection in Transaction
> Error:
> SQLSTATE=25S12,Native error=-2147168242,msg='[Microsoft][ODBC SQL Server
> Driver]
> Distributed transaction error'
> Error:
> SQLSTATE=24000,Native error=0,msg=[Microsoft][ODBC SQL Server Driver]Invalid
> cur
> sor state
> Typical Errors in DTC Output When
> a. Firewall Has Ports Closed
> -OR-
> b. Bad WINS/DNS entries
> -OR-
> c. Misconfigured network
> -OR-
> d. Misconfigured SQL Server machine that has multiple netcards.
> Aborting DTC Transaction
> Releasing DTC Interface Pointers
> Successfully Released pTransaction Pointer.
>|||Thank you for the response! What additional settings need to be made?
"BitWise" wrote:
> If the 2003 server has had sp1 installed recently, this may be your
> problem.You will need to change the security settings in DCOM.
> "Joe Loyd" wrote:
> > Please Help!...Recently we started seeing DTC failures between two of our
> > servers. The calling server(A) is Win2003 and the destination (B) is Win2K. I
> > t was working a week ago and our Network gurus say nothing has changed on the
> > network.
> >
> > I've set the "TurnOffRpcSecurity" per KB article #827805. Both boxes are
> > in the same domain and network segment with no firewall between them. I also
> > added entries to the host and lmhost files on both.
> >
> > I downloaded and ran both DTCPing and DTCTester utilties. DTCPing succeeds
> > when run from A, but not from B (it hangs in the bind phase after
> > successfully completing the RPC portion but no error is retuned)The DTCTester
> > returns the following when run from either:
> >
> > Executed: dtctester
> > DSN: dtctest
> > User Name: sa
> > Password:
> > tablename= #dtc5330
> > Creating Temp Table for Testing: #dtc5330
> > Warning: No Columns in Result Set From Executing: 'create table #dtc5330
> > (ival i
> > nt)'
> > Initializing DTC
> > Beginning DTC Transaction
> > Enlisting Connection in Transaction
> > Error:
> > SQLSTATE=25S12,Native error=-2147168242,msg='[Microsoft][ODBC SQL Server
> > Driver]
> > Distributed transaction error'
> > Error:
> > SQLSTATE=24000,Native error=0,msg=[Microsoft][ODBC SQL Server Driver]Invalid
> > cur
> > sor state
> > Typical Errors in DTC Output When
> > a. Firewall Has Ports Closed
> > -OR-
> > b. Bad WINS/DNS entries
> > -OR-
> > c. Misconfigured network
> > -OR-
> > d. Misconfigured SQL Server machine that has multiple netcards.
> > Aborting DTC Transaction
> > Releasing DTC Interface Pointers
> > Successfully Released pTransaction Pointer.
> >
> >|||At the run command type dcomcnfg.exe
Expand Component services >> Computers >> My Computer
Right click My Computer and choose properties
Click on the MSDTC tab
Choose the Security Configuration Button
Under Transaction Manager Communication group, select No Authentication
Required
Click OK
"Joe Loyd" wrote:
> Thank you for the response! What additional settings need to be made?
> "BitWise" wrote:
> > If the 2003 server has had sp1 installed recently, this may be your
> > problem.You will need to change the security settings in DCOM.
> >
> > "Joe Loyd" wrote:
> >
> > > Please Help!...Recently we started seeing DTC failures between two of our
> > > servers. The calling server(A) is Win2003 and the destination (B) is Win2K. I
> > > t was working a week ago and our Network gurus say nothing has changed on the
> > > network.
> > >
> > > I've set the "TurnOffRpcSecurity" per KB article #827805. Both boxes are
> > > in the same domain and network segment with no firewall between them. I also
> > > added entries to the host and lmhost files on both.
> > >
> > > I downloaded and ran both DTCPing and DTCTester utilties. DTCPing succeeds
> > > when run from A, but not from B (it hangs in the bind phase after
> > > successfully completing the RPC portion but no error is retuned)The DTCTester
> > > returns the following when run from either:
> > >
> > > Executed: dtctester
> > > DSN: dtctest
> > > User Name: sa
> > > Password:
> > > tablename= #dtc5330
> > > Creating Temp Table for Testing: #dtc5330
> > > Warning: No Columns in Result Set From Executing: 'create table #dtc5330
> > > (ival i
> > > nt)'
> > > Initializing DTC
> > > Beginning DTC Transaction
> > > Enlisting Connection in Transaction
> > > Error:
> > > SQLSTATE=25S12,Native error=-2147168242,msg='[Microsoft][ODBC SQL Server
> > > Driver]
> > > Distributed transaction error'
> > > Error:
> > > SQLSTATE=24000,Native error=0,msg=[Microsoft][ODBC SQL Server Driver]Invalid
> > > cur
> > > sor state
> > > Typical Errors in DTC Output When
> > > a. Firewall Has Ports Closed
> > > -OR-
> > > b. Bad WINS/DNS entries
> > > -OR-
> > > c. Misconfigured network
> > > -OR-
> > > d. Misconfigured SQL Server machine that has multiple netcards.
> > > Aborting DTC Transaction
> > > Releasing DTC Interface Pointers
> > > Successfully Released pTransaction Pointer.
> > >
> > >

DTC Error

Hi there,
We are having a issue with MSDTC saying that the DTC service is unavailable
when runing a distributed transaction between two WIN2003 servers running
SQL 2000 SP3. Have read all the articles but I am not having much sucess.
Have enabled network DTC access on both servers. The service account is
running under a domain admin account. It seems that one server is the common
donominator as all distributed transactions with this server fails. We read
an article that said to add the reg value:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\TurnOf fRpcSecurity = 1, this had
the effect of changing the error message to:
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Not sure where to look next, any assistance will be appreciated.
Thanks
Devron Blatchford.
Hi,
I had a similar problem with my Distributed transactions and the problem was
the name resolution within the two servers. I downloaded the MS DTCPing.exe
from microsoft and i found that there wasn't the appropriate name resolution.
Also, if your servers aren't in the same domain, you must trust these domains.
Download the MS DTCPing.exe from the following article
http://support.microsoft.com/default...b;en-us;306843
Andreas
"Devron Blatchford" wrote:

> Hi there,
> We are having a issue with MSDTC saying that the DTC service is unavailable
> when runing a distributed transaction between two WIN2003 servers running
> SQL 2000 SP3. Have read all the articles but I am not having much sucess.
> Have enabled network DTC access on both servers. The service account is
> running under a domain admin account. It seems that one server is the common
> donominator as all distributed transactions with this server fails. We read
> an article that said to add the reg value:
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\TurnOf fRpcSecurity = 1, this had
> the effect of changing the error message to:
> Server: Msg 7391, Level 16, State 1, Line 2
> The operation could not be performed because the OLE DB provider 'SQLOLEDB'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> Not sure where to look next, any assistance will be appreciated.
> Thanks
> Devron Blatchford.
>
>

DTC Error

I have a project with two SQL servers on two seperate
subnet, following distribute query will run on B server.
update [xxx.xxx.xxx.xxx].[abce].[dbo].[abcede] set
uploadstatus='InProgress'
If I include this statement in Transaction Like this way:
Begin Tran
update [xxx.xxx.xxx.xxx].[abce].[dbo].[abcede] set
uploadstatus='InProgress'
commit Tran
I got following error' Server: Msg 7391, Level 16, State
1, Line 2
The operation could not be performed because the OLE DB
provider 'SQLOLEDB' was unable to begin a distributed
transaction.
[OLE/DB provider returned message: New transaction cannot
enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].'
Link server is setup okay for sure, any suggestions from
microsoft?
For DTC , two sql server have to be the same subnet or
same domain?
Thanks
David Liu
MCSE/MCDBA
Moneris.com
Is one of the servers Windows Server 2003 by any chance - see below
http://support.microsoft.com/default...b;en-us;816701
Andy Ball
Greenfell Computing Ltd
"David Liu" <david.z.liu@.moneris.com> wrote in message
news:2ef101c4289c$c6c0a660$a101280a@.phx.gbl...
> I have a project with two SQL servers on two seperate
> subnet, following distribute query will run on B server.
> update [xxx.xxx.xxx.xxx].[abce].[dbo].[abcede] set
> uploadstatus='InProgress'
> If I include this statement in Transaction Like this way:
> Begin Tran
> update [xxx.xxx.xxx.xxx].[abce].[dbo].[abcede] set
> uploadstatus='InProgress'
> commit Tran
> I got following error' Server: Msg 7391, Level 16, State
> 1, Line 2
> The operation could not be performed because the OLE DB
> provider 'SQLOLEDB' was unable to begin a distributed
> transaction.
> [OLE/DB provider returned message: New transaction cannot
> enlist in the specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].'
> Link server is setup okay for sure, any suggestions from
> microsoft?
> For DTC , two sql server have to be the same subnet or
> same domain?
> Thanks
> David Liu
> MCSE/MCDBA
> Moneris.com
>
>
>
|||Both of them are Windows 2000 Advanced Server.
Does this case need to be same subnet and same domain?
DTC use RPC for communication for sure.
Really wired
Thanks for your information

>--Original Message--
>Is one of the servers Windows Server 2003 by any chance -
see below
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;816701[vbcol=seagreen]
>
>--
>Andy Ball
>Greenfell Computing Ltd
>"David Liu" <david.z.liu@.moneris.com> wrote in message
>news:2ef101c4289c$c6c0a660$a101280a@.phx.gbl...
way:[vbcol=seagreen]
cannot
>
>.
>

DTC error

Hi,
I have two MS SQL2000 servers in local network . Let's
say A and B. Server B is linked server for A.
When I'm starting following select on server A :
========================================= BEGIN TRAN
SELECT *
FROM OPENQUERY(DW_SERVER, 'SELECT p.AP_ID,
p.AP_NAME, p.PT_NAME,
p.AP_DEBIT_SOURCE, dbo.UF_GET_FULL_SOURCE
(p.AP_DEBIT_SOURCE, p.PT_NAME) AS AP_FULL_SOURCE,
p.AP_VOL_FACE, p.AP_TIME_FACE,
p.AP_EXPIRED_PERIOD
FROM dbo.AAA_DW_PROFILE_DIM p ')
ROLLBACK TRAN
============================================
I'm getting following error:
========================================= Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB
provider 'SQLOLEDB' was unable to begin a distributed
transaction.
[OLE/DB provider returned message: New transaction cannot
enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
==========================================
DTC is started on both servers.
During investigation of this issue I've started
DTCPing utilite. After this, in DTCPing's log file
I have found only following problem string:
================================================= 07-14, 19:55:100-->WARNING:the CID values for both test
machines are the same
while this problem won't stop DTCping test, MSDTC
will fail for this
====================================================
Am I right? The error was connected with this issue?
What should I do next?What is dbo.UF_GET_FULL_SOURCE in your SQL? and has no comma after
"andy" <akorotaev@.estylesoft.com> wrote in message
news:07dd01c34ab1$8b611c90$a601280a@.phx.gbl...
> Hi,
> I have two MS SQL2000 servers in local network . Let's
> say A and B. Server B is linked server for A.
> When I'm starting following select on server A :
> =========================================> BEGIN TRAN
> SELECT *
> FROM OPENQUERY(DW_SERVER, 'SELECT p.AP_ID,
> p.AP_NAME, p.PT_NAME,
> p.AP_DEBIT_SOURCE, dbo.UF_GET_FULL_SOURCE
> (p.AP_DEBIT_SOURCE, p.PT_NAME) AS AP_FULL_SOURCE,
> p.AP_VOL_FACE, p.AP_TIME_FACE,
> p.AP_EXPIRED_PERIOD
> FROM dbo.AAA_DW_PROFILE_DIM p ')
> ROLLBACK TRAN
> ============================================> I'm getting following error:
> =========================================> Server: Msg 7391, Level 16, State 1, Line 2
> The operation could not be performed because the OLE DB
> provider 'SQLOLEDB' was unable to begin a distributed
> transaction.
> [OLE/DB provider returned message: New transaction cannot
> enlist in the specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> ==========================================> DTC is started on both servers.
> During investigation of this issue I've started
> DTCPing utilite. After this, in DTCPing's log file
> I have found only following problem string:
> =================================================> 07-14, 19:55:100-->WARNING:the CID values for both test
> machines are the same
> while this problem won't stop DTCping test, MSDTC
> will fail for this
> ====================================================> Am I right? The error was connected with this issue?
> What should I do next?
>
>
>
>
>|||dbo.UF_GET_FULL_SOURCE is function.
No any comma needed here.
>--Original Message--
>What is dbo.UF_GET_FULL_SOURCE in your SQL? and has no
comma after
>"andy" <akorotaev@.estylesoft.com> wrote in message
>news:07dd01c34ab1$8b611c90$a601280a@.phx.gbl...
>> Hi,
>> I have two MS SQL2000 servers in local network . Let's
>> say A and B. Server B is linked server for A.
>> When I'm starting following select on server A :
>> =========================================>> BEGIN TRAN
>> SELECT *
>> FROM OPENQUERY(DW_SERVER, 'SELECT p.AP_ID,
>> p.AP_NAME, p.PT_NAME,
>> p.AP_DEBIT_SOURCE, dbo.UF_GET_FULL_SOURCE
>> (p.AP_DEBIT_SOURCE, p.PT_NAME) AS AP_FULL_SOURCE,
>> p.AP_VOL_FACE, p.AP_TIME_FACE,
>> p.AP_EXPIRED_PERIOD
>> FROM dbo.AAA_DW_PROFILE_DIM p ')
>> ROLLBACK TRAN
>> ============================================>> I'm getting following error:
>> =========================================>> Server: Msg 7391, Level 16, State 1, Line 2
>> The operation could not be performed because the OLE DB
>> provider 'SQLOLEDB' was unable to begin a distributed
>> transaction.
>> [OLE/DB provider returned message: New transaction
cannot
>> enlist in the specified transaction coordinator. ]
>> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
>> ITransactionJoin::JoinTransaction returned 0x8004d00a].
>> ==========================================>> DTC is started on both servers.
>> During investigation of this issue I've started
>> DTCPing utilite. After this, in DTCPing's log file
>> I have found only following problem string:
>> =================================================>> 07-14, 19:55:100-->WARNING:the CID values for both test
>> machines are the same
>> while this problem won't stop DTCping test, MSDTC
>> will fail for this
>> ====================================================>> Am I right? The error was connected with this issue?
>> What should I do next?
>>
>>
>>
>>
>
>.
>|||Andy,
take a look at technet article Q306843.
Section 11 refers to duplicate CIDs and using GUIDGEN to
fix this...
You might also want to look at the following article :
http://support.microsoft.com/default.aspx?scid=kb;EN-
US;162001
I am guessing this is on a ghosted or cloned server ?
Hope this helps.
Steve.
>--Original Message--
>
>dbo.UF_GET_FULL_SOURCE is function.
>No any comma needed here.
>
>>--Original Message--
>>What is dbo.UF_GET_FULL_SOURCE in your SQL? and has no
>comma after
>>"andy" <akorotaev@.estylesoft.com> wrote in message
>>news:07dd01c34ab1$8b611c90$a601280a@.phx.gbl...
>> Hi,
>> I have two MS SQL2000 servers in local network . Let's
>> say A and B. Server B is linked server for A.
>> When I'm starting following select on server A :
>> =========================================>> BEGIN TRAN
>> SELECT *
>> FROM OPENQUERY(DW_SERVER, 'SELECT p.AP_ID,
>> p.AP_NAME, p.PT_NAME,
>> p.AP_DEBIT_SOURCE, dbo.UF_GET_FULL_SOURCE
>> (p.AP_DEBIT_SOURCE, p.PT_NAME) AS AP_FULL_SOURCE,
>> p.AP_VOL_FACE, p.AP_TIME_FACE,
>> p.AP_EXPIRED_PERIOD
>> FROM dbo.AAA_DW_PROFILE_DIM p ')
>> ROLLBACK TRAN
>> ============================================>> I'm getting following error:
>> =========================================>> Server: Msg 7391, Level 16, State 1, Line 2
>> The operation could not be performed because the OLE DB
>> provider 'SQLOLEDB' was unable to begin a distributed
>> transaction.
>> [OLE/DB provider returned message: New transaction
>cannot
>> enlist in the specified transaction coordinator. ]
>> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
>> ITransactionJoin::JoinTransaction returned 0x8004d00a].
>> ==========================================>> DTC is started on both servers.
>> During investigation of this issue I've started
>> DTCPing utilite. After this, in DTCPing's log file
>> I have found only following problem string:
>> =================================================>> 07-14, 19:55:100-->WARNING:the CID values for both test
>> machines are the same
>> while this problem won't stop DTCping test, MSDTC
>> will fail for this
>> ====================================================>> Am I right? The error was connected with this issue?
>> What should I do next?
>>
>>
>>
>>
>>
>>.
>.
>

DTC Error

Hi there,
We are having a issue with MSDTC saying that the DTC service is unavailable
when runing a distributed transaction between two WIN2003 servers running
SQL 2000 SP3. Have read all the articles but I am not having much sucess.
Have enabled network DTC access on both servers. The service account is
running under a domain admin account. It seems that one server is the common
donominator as all distributed transactions with this server fails. We read
an article that said to add the reg value:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
DTC\TurnOffRpcSecurity = 1, this had
the effect of changing the error message to:
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Not sure where to look next, any assistance will be appreciated.
Thanks
Devron Blatchford.Hi,
I had a similar problem with my Distributed transactions and the problem was
the name resolution within the two servers. I downloaded the MS DTCPing.exe
from microsoft and i found that there wasn't the appropriate name resolution
.
Also, if your servers aren't in the same domain, you must trust these domain
s.
Download the MS DTCPing.exe from the following article
http://support.microsoft.com/defaul...kb;en-us;306843
Andreas
"Devron Blatchford" wrote:

> Hi there,
> We are having a issue with MSDTC saying that the DTC service is unavailab
le
> when runing a distributed transaction between two WIN2003 servers running
> SQL 2000 SP3. Have read all the articles but I am not having much sucess.
> Have enabled network DTC access on both servers. The service account is
> running under a domain admin account. It seems that one server is the comm
on
> donominator as all distributed transactions with this server fails. We rea
d
> an article that said to add the reg value:
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
DTC\TurnOffRpcSecurity = 1, this h
ad
> the effect of changing the error message to:
> Server: Msg 7391, Level 16, State 1, Line 2
> The operation could not be performed because the OLE DB provider 'SQLOLEDB
'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in th
e
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> Not sure where to look next, any assistance will be appreciated.
> Thanks
> Devron Blatchford.
>
>