DTS Copy SQL Server Task

I want to copy a database on a remote server to a local instance of SQL Server. I created a package that has the Copy SQL Server Task in it, but it seems to fail half way through every time i execute it.

I can view the local machine error log which simply reports that the table transfer was attempted.. but no explanation of why it fails. I don't have access to the host error log files either, so I have no idea why it fails.

I once almost got the 'Copy SQL Server Task' to work, but it would create duplicate records in the destination database, which I didn't want. It seems choosing the intuitive options like 'replace existing data' and choosing the table I want copied over.. does not work. I am not 100% sure the 2 databases are referentially identical either.. so it would be nice to know how to do a copy by first deleting the entire database on the destination machine, then recreating it all.

Can anyone give me some advice on which options I need to choose to achieve a proper database copy? Or, if you could show me a tutorial online that could explain how all the options work, and how I can debug what's causing my current method to fail, would be nice.

BrentWell I got it to work. It turns out that you can double click the error message to get some more details as it occurs. Strangely every account I had tried to use on my local server did not have permission to do the copying.. but only for certain attributes. So, basically by trial and error, I selected the right configuration to only copy the tables to the destination database (the accounts seemed to have permission to do certain activities).

It sure would be nice to find some info on the meaning of all the options in help or online somewhere.. if anyone knows of such a resource, they should post it.|||Thanks me!

I forgot how to view DTS backup log errors, and found my own old post to show me... double click the error in Ent. Manager!

This is what i needed to fix the problem!

DTS connection to Oracle 8; To be upgrated to Oracle 9i. Any guida

I have inherited a Sql Server 2000 instance who's DTS accesses an Oracle 8
database. That Oracle 8 database is going to be upgraded to Oracle 9i soon,
and I was wondering what I have to do to keep this all running. I think I
have to upgrade the Oracle client on the Sql Server box to 9i (since the
Oracle 8 client can not talk to Oracle 9i). Other than that, I am not aware
of anything else I have to do.
Anyone done this before?
Thanks,
Michael
"Snake" wrote:

> I have inherited a Sql Server 2000 instance who's DTS accesses an Oracle 8
> database. That Oracle 8 database is going to be upgraded to Oracle 9i soon,
> and I was wondering what I have to do to keep this all running. I think I
> have to upgrade the Oracle client on the Sql Server box to 9i (since the
> Oracle 8 client can not talk to Oracle 9i). Other than that, I am not aware
> of anything else I have to do.
> Anyone done this before?
> Thanks,
> Michael
|||You should be able to upgrade the Oracle client from 8 to 9i on your side to
solve this problem. My suggestion is to install Oracle 9i on the SQL Server
machine in parallel with the Oracle 8 client. To do this, copy your DTS
package into a new one with a similar name. Create a new DSN that uses the
Oracle 9i client and then test it on the server to make sure the Oracle 9
install worked successfully. Since the Oracle 9i client can read an Oracle 8
database you can switch to this install early and then you'll be prepared
early for the production change on the source database. After the source
database is upgraded to Oracle 9i, you can safely remove the old Oracle 8i
components from your server.
"Snake" wrote:

> I have inherited a Sql Server 2000 instance who's DTS accesses an Oracle 8
> database. That Oracle 8 database is going to be upgraded to Oracle 9i soon,
> and I was wondering what I have to do to keep this all running. I think I
> have to upgrade the Oracle client on the Sql Server box to 9i (since the
> Oracle 8 client can not talk to Oracle 9i). Other than that, I am not aware
> of anything else I have to do.
> Anyone done this before?
> Thanks,
> Michael
|||Also, keep in mind that Oracle's new DATETIME data type has "known" issues
with respect to SQL Server.
Do a search on the net for issues and the workarounds.
Sincerely,
Anthony Thomas

"Amrith" <Amrith@.discussions.microsoft.com> wrote in message
news:5C126AC9-DF98-4B2A-8F8A-E5D887A502AB@.microsoft.com...
You should be able to upgrade the Oracle client from 8 to 9i on your side to
solve this problem. My suggestion is to install Oracle 9i on the SQL Server
machine in parallel with the Oracle 8 client. To do this, copy your DTS
package into a new one with a similar name. Create a new DSN that uses the
Oracle 9i client and then test it on the server to make sure the Oracle 9
install worked successfully. Since the Oracle 9i client can read an Oracle 8
database you can switch to this install early and then you'll be prepared
early for the production change on the source database. After the source
database is upgraded to Oracle 9i, you can safely remove the old Oracle 8i
components from your server.
"Snake" wrote:

> I have inherited a Sql Server 2000 instance who's DTS accesses an Oracle 8
> database. That Oracle 8 database is going to be upgraded to Oracle 9i
soon,
> and I was wondering what I have to do to keep this all running. I think I
> have to upgrade the Oracle client on the Sql Server box to 9i (since the
> Oracle 8 client can not talk to Oracle 9i). Other than that, I am not
aware
> of anything else I have to do.
> Anyone done this before?
> Thanks,
> Michael

DTS connection to Oracle 8; To be upgrated to Oracle 9i. Any guida

I have inherited a Sql Server 2000 instance who's DTS accesses an Oracle 8
database. That Oracle 8 database is going to be upgraded to Oracle 9i soon,
and I was wondering what I have to do to keep this all running. I think I
have to upgrade the Oracle client on the Sql Server box to 9i (since the
Oracle 8 client can not talk to Oracle 9i). Other than that, I am not aware
of anything else I have to do.
Anyone done this before?
Thanks,
MichaelYou should be able to upgrade the Oracle client from 8 to 9i on your side to
solve this problem. My suggestion is to install Oracle 9i on the SQL Server
machine in parallel with the Oracle 8 client. To do this, copy your DTS
package into a new one with a similar name. Create a new DSN that uses the
Oracle 9i client and then test it on the server to make sure the Oracle 9
install worked successfully. Since the Oracle 9i client can read an Oracle 8
database you can switch to this install early and then you'll be prepared
early for the production change on the source database. After the source
database is upgraded to Oracle 9i, you can safely remove the old Oracle 8i
components from your server.
"Snake" wrote:
> I have inherited a Sql Server 2000 instance who's DTS accesses an Oracle 8
> database. That Oracle 8 database is going to be upgraded to Oracle 9i soon,
> and I was wondering what I have to do to keep this all running. I think I
> have to upgrade the Oracle client on the Sql Server box to 9i (since the
> Oracle 8 client can not talk to Oracle 9i). Other than that, I am not aware
> of anything else I have to do.
> Anyone done this before?
> Thanks,
> Michael|||"Snake" wrote:
> I have inherited a Sql Server 2000 instance who's DTS accesses an Oracle 8
> database. That Oracle 8 database is going to be upgraded to Oracle 9i soon,
> and I was wondering what I have to do to keep this all running. I think I
> have to upgrade the Oracle client on the Sql Server box to 9i (since the
> Oracle 8 client can not talk to Oracle 9i). Other than that, I am not aware
> of anything else I have to do.
> Anyone done this before?
> Thanks,
> Michael|||Also, keep in mind that Oracle's new DATETIME data type has "known" issues
with respect to SQL Server.
Do a search on the net for issues and the workarounds.
Sincerely,
Anthony Thomas
"Amrith" <Amrith@.discussions.microsoft.com> wrote in message
news:5C126AC9-DF98-4B2A-8F8A-E5D887A502AB@.microsoft.com...
You should be able to upgrade the Oracle client from 8 to 9i on your side to
solve this problem. My suggestion is to install Oracle 9i on the SQL Server
machine in parallel with the Oracle 8 client. To do this, copy your DTS
package into a new one with a similar name. Create a new DSN that uses the
Oracle 9i client and then test it on the server to make sure the Oracle 9
install worked successfully. Since the Oracle 9i client can read an Oracle 8
database you can switch to this install early and then you'll be prepared
early for the production change on the source database. After the source
database is upgraded to Oracle 9i, you can safely remove the old Oracle 8i
components from your server.
"Snake" wrote:
> I have inherited a Sql Server 2000 instance who's DTS accesses an Oracle 8
> database. That Oracle 8 database is going to be upgraded to Oracle 9i
soon,
> and I was wondering what I have to do to keep this all running. I think I
> have to upgrade the Oracle client on the Sql Server box to 9i (since the
> Oracle 8 client can not talk to Oracle 9i). Other than that, I am not
aware
> of anything else I have to do.
> Anyone done this before?
> Thanks,
> Michael

DTC problems connecting to server 2003

I have all the symptoms of KB article 839279:
"When you run a distributed transaction against an instance of SQL Server,
you may receive an error message that is similar to the following:
Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction. [OLE/DB provid
er
returned message: New transaction cannot enlist in the specified
transaction coordinator. ] OLE DB error trace [OLE/DB Provider 'SQLOLEDB
'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
This problem may occur when one of following conditions is true:
Microsoft Windows Server 2003 or Microsoft Windows XP Service Pack 2
(SP2) is installed on the computer that initiates the distributed
transaction.
Microsoft Windows Server 2003 or Microsoft Windows XP SP2 is installed on
the remote computer that is running Microsoft SQL Server 2000, and that
computer is linked to the computer that initiates the distributed
transaction."
In my case, Windows server 2003 is running SQL server 2000, and I am trying
to do an INSERT INTO <table> EXEC <storedproc> from another computer that
has SQL server 2000 installed.
I can link the remote server, and run the stored proc and look at the
resulting records, but I can't do the INSERT INTO using the result of the
stored procedure -- I get the message:
Server: Msg 8501, Level 16, State 1, Line 1
MSDTC on server 'server\database' is unavailable.
Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d01c].
In the "workaround" section of the KB article, Step 2, substeps g-i are not
possible on Windows server 2003 (non-SP1). It says to select Allow
Inbound and Allow Outbound, and also to check No Authentication Required in
the Transaction Manager Communication box. I can't! Those choices do not
appear in the dialog boxes.
Any suggestions on how to set the DTC "No Authentication Required" option?
David WalkerSEE http://support.microsoft.com/defaul...kb;en-us;827805
from http://support.microsoft.com/?kbid=555017
Disable RPC Security for MSDTC Service on SQL Server
This step requires accessing and modifying the registry. If you have
not already done so, it is highly recommended that you back up the
registry at this time.
1. Click Start, click Run, type in "Regedt32", and click OK.
2. Select HKEY_LOCAL_MACHINE, then SOFTWARE, then Microsoft.
3. Right-click on MSDTC, point to Add, then select DWORD Value.
4. Rename the key from the default New Value #1 to
TurnOffRpcSecurity.
5. Double-click the new key and change the value to 1.
6. Close the Registry Editor and restart the SQL Server

DTC problems connecting to server 2003

I have all the symptoms of KB article 839279:
"When you run a distributed transaction against an instance of SQL Server,
you may receive an error message that is similar to the following:
Server: Msg 7391, Level 16, State 1, Line 1
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].
This problem may occur when one of following conditions is true:
Microsoft Windows Server 2003 or Microsoft Windows XP Service Pack 2
(SP2) is installed on the computer that initiates the distributed
transaction.
Microsoft Windows Server 2003 or Microsoft Windows XP SP2 is installed on
the remote computer that is running Microsoft SQL Server 2000, and that
computer is linked to the computer that initiates the distributed
transaction."
In my case, Windows server 2003 is running SQL server 2000, and I am trying
to do an INSERT INTO <table> EXEC <storedproc> from another computer that
has SQL server 2000 installed.
I can link the remote server, and run the stored proc and look at the
resulting records, but I can't do the INSERT INTO using the result of the
stored procedure -- I get the message:
Server: Msg 8501, Level 16, State 1, Line 1
MSDTC on server 'server\database' is unavailable.
Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d01c].
In the "workaround" section of the KB article, Step 2, substeps g-i are not
possible on Windows server 2003 (non-SP1). It says to select Allow
Inbound and Allow Outbound, and also to check No Authentication Required in
the Transaction Manager Communication box. I can't! Those choices do not
appear in the dialog boxes.
Any suggestions on how to set the DTC "No Authentication Required" option?
David Walker
SEE http://support.microsoft.com/default...b;en-us;827805
from http://support.microsoft.com/?kbid=555017
Disable RPC Security for MSDTC Service on SQL Server
This step requires accessing and modifying the registry. If you have
not already done so, it is highly recommended that you back up the
registry at this time.
1. Click Start, click Run, type in "Regedt32", and click OK.
2. Select HKEY_LOCAL_MACHINE, then SOFTWARE, then Microsoft.
3. Right-click on MSDTC, point to Add, then select DWORD Value.
4. Rename the key from the default New Value #1 to
TurnOffRpcSecurity.
5. Double-click the new key and change the value to 1.
6. Close the Registry Editor and restart the SQL Server

DTC in multiple instance cluster

WE have a 2 node win 2k3 x64 cluster (also x86 clusters with same problem)
with 2 instanses of SQL 2005 x64 and the MSDTC resource in the admin.
cluster group (quorum group).
This is a active/passive cluster, all 3 groups/vir. servers on same node.
The problem appears when one of the SQL servers fail over to the other node.
Then the MSDTC transactions fail on this sql server.
Result = we have to manually fail over the MSDTC resource/group also.
I have read about a DTC proxy but how do I get this to work ?
Bj?rn
Some say that this shouldn't happen, but we experienced this often as well.
The fix is to place the MS DTC in its own cluster resource group, which will
require another set of dedicated IP Addresses, physical disk, and network
name resources.
http://support.microsoft.com/kb/301600/en-us
Sincerely,
Anthony Thomas

"Bjrn" <Bjrn@.discussions.microsoft.com> wrote in message
news:251D1C52-C076-40A0-BE2B-2B5EF4031146@.microsoft.com...
> WE have a 2 node win 2k3 x64 cluster (also x86 clusters with same problem)
> with 2 instanses of SQL 2005 x64 and the MSDTC resource in the admin.
> cluster group (quorum group).
> This is a active/passive cluster, all 3 groups/vir. servers on same node.
> The problem appears when one of the SQL servers fail over to the other
node.
> Then the MSDTC transactions fail on this sql server.
> Result = we have to manually fail over the MSDTC resource/group also.
> I have read about a DTC proxy but how do I get this to work ?
> Bjrn
>

dt_ procs and the public role

In a SQL 2k instance (latest SP) some of my user databases show the public role with execute on a variety of stored procs named dt_* (i.e. dt_addtosourcecontrol). However, not all the user databases do this, some do not grant the public role execute on these procs .

So, can someone explain what generates these permissions and is it acceptable to remove them? If I have a database that does not grant public access, should I be concerned? I don't see any reference to these procs in BOL.

TIA,

Moblex

This seems to be a Visual Studio generated procedure, so you should ask this question on a Visual Studio forum. Here's a related thread

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=562892&SiteID=17

that I found with the following search query:

http://search.live.com/results.aspx?q=dt_addtosourcecontrol&mkt=en-us&FORM=LVSP&go.x=10&go.y=13

Thanks
Laurentiu