DTS execution from client machine fails connecting to Oracle

I have created a DTS package that pulls data in from Oracle into SQL
Server. When I run it directly on the server (from Enterprise
Manager), it works fine. When I run it from Enterprise Manager on a
client machine that does not have the Oracle client software, it does
not run, giving me the error: "The Oracle client and networking
components were not found...". I was hoping I wouldn't need to have
the client software installed on a machine other than the server where
Sql Server is running. The problem here is that there are a number of
machines from where I would like to execute this DTS package that I do
not want to install/configure the Oracle client software. I don't
quite understand why the configuration of the client is important
here. In my mind, when I am using enterprise manager from a client
machine, I am using it sort of like a terminal services client to
connect to the server. I guess there is a lot more happening in the
background.

Thanks for any feedback,
MarcusDo you have the proper ODBC driver that you are using in the DTS on the
client machine? same as the one on your server.

"Marcus" <holysmokes99@.hotmail.com> wrote in message
news:1783abaf.0307250838.70a1aaa4@.posting.google.c om...
> I have created a DTS package that pulls data in from Oracle into SQL
> Server. When I run it directly on the server (from Enterprise
> Manager), it works fine. When I run it from Enterprise Manager on a
> client machine that does not have the Oracle client software, it does
> not run, giving me the error: "The Oracle client and networking
> components were not found...". I was hoping I wouldn't need to have
> the client software installed on a machine other than the server where
> Sql Server is running. The problem here is that there are a number of
> machines from where I would like to execute this DTS package that I do
> not want to install/configure the Oracle client software. I don't
> quite understand why the configuration of the client is important
> here. In my mind, when I am using enterprise manager from a client
> machine, I am using it sort of like a terminal services client to
> connect to the server. I guess there is a lot more happening in the
> background.
> Thanks for any feedback,
> Marcus|||Marcus wrote:

> I have created a DTS package that pulls data in from Oracle into SQL
> Server. When I run it directly on the server (from Enterprise
> Manager), it works fine. When I run it from Enterprise Manager on a
> client machine that does not have the Oracle client software, it does
> not run, giving me the error: "The Oracle client and networking
> components were not found...". I was hoping I wouldn't need to have
> the client software installed on a machine other than the server where
> Sql Server is running. The problem here is that there are a number of
> machines from where I would like to execute this DTS package that I do
> not want to install/configure the Oracle client software. I don't
> quite understand why the configuration of the client is important
> here. In my mind, when I am using enterprise manager from a client
> machine, I am using it sort of like a terminal services client to
> connect to the server. I guess there is a lot more happening in the
> background.
> Thanks for any feedback,
> Marcus

The Oracle client is required. So is paying attention to the license
agreement.

--
Daniel Morgan
http://www.outreach.washington.edu/...oad/oad_crs.asp
damorgan@.x.washington.edu
(replace 'x' with a 'u' to reply)sql

DTS execution from client machine fails connecting to Oracle

I have created a DTS package that pulls data in from Oracle into SQL
Server. When I run it directly on the server (from Enterprise
Manager), it works fine. When I run it from Enterprise Manager on a
client machine that does not have the Oracle client software, it does
not run, giving me the error: "The Oracle client and networking
components were not found...". I was hoping I wouldn't need to have
the client software installed on a machine other than the server where
Sql Server is running. The problem here is that there are a number of
machines from where I would like to execute this DTS package that I do
not want to install/configure the Oracle client software. I don't
quite understand why the configuration of the client is important
here. In my mind, when I am using enterprise manager from a client
machine, I am using it sort of like a terminal services client to
connect to the server. I guess there is a lot more happening in the
background.
Thanks for any feedback,
MarcusDo you have the proper ODBC driver that you are using in the DTS on the
client machine? same as the one on your server.
"Marcus" <holysmokes99@.hotmail.com> wrote in message
news:1783abaf.0307250838.70a1aaa4@.posting.google.com...
> I have created a DTS package that pulls data in from Oracle into SQL
> Server. When I run it directly on the server (from Enterprise
> Manager), it works fine. When I run it from Enterprise Manager on a
> client machine that does not have the Oracle client software, it does
> not run, giving me the error: "The Oracle client and networking
> components were not found...". I was hoping I wouldn't need to have
> the client software installed on a machine other than the server where
> Sql Server is running. The problem here is that there are a number of
> machines from where I would like to execute this DTS package that I do
> not want to install/configure the Oracle client software. I don't
> quite understand why the configuration of the client is important
> here. In my mind, when I am using enterprise manager from a client
> machine, I am using it sort of like a terminal services client to
> connect to the server. I guess there is a lot more happening in the
> background.
> Thanks for any feedback,
> Marcus|||Unfortunately you will need the Oracle connectivity on the server/or
anywhere else that tells this package to execute
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org|||Marcus wrote:
> I have created a DTS package that pulls data in from Oracle into SQL
> Server. When I run it directly on the server (from Enterprise
> Manager), it works fine. When I run it from Enterprise Manager on a
> client machine that does not have the Oracle client software, it does
> not run, giving me the error: "The Oracle client and networking
> components were not found...". I was hoping I wouldn't need to have
> the client software installed on a machine other than the server where
> Sql Server is running. The problem here is that there are a number of
> machines from where I would like to execute this DTS package that I do
> not want to install/configure the Oracle client software. I don't
> quite understand why the configuration of the client is important
> here. In my mind, when I am using enterprise manager from a client
> machine, I am using it sort of like a terminal services client to
> connect to the server. I guess there is a lot more happening in the
> background.
> Thanks for any feedback,
> Marcus
The Oracle client is required. So is paying attention to the license
agreement.
--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan@.x.washington.edu
(replace 'x' with a 'u' to reply)

DTS Designer Error: The specified module could not be found

Hi folks
I am connecting to a SQL Server 2000 (SP3) from a client Enterprise
Manager. It connects to it, well.
But if I try to open an existing DTS package (located under Local
packages) it throws an error "DTS Designer Error The specified module
could not be found" and it closes.
Same error occurs if I try to open a .dts file also.
Your help is appreciated.
Thanks in advance
Venkat.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Hi
Searching google for you error messages turned up these two possible
solutions:
http://tinyurl.com/66tsu
http://tinyurl.com/6e6jm
John
"Venkatesan" wrote:

> Hi folks
> I am connecting to a SQL Server 2000 (SP3) from a client Enterprise
> Manager. It connects to it, well.
> But if I try to open an existing DTS package (located under Local
> packages) it throws an error "DTS Designer Error The specified module
> could not be found" and it closes.
> Same error occurs if I try to open a .dts file also.
> Your help is appreciated.
> Thanks in advance
> Venkat.
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
>

DTS connecting to CA Datacom

I am trying to import data from a CA Datacom database on a mainframe using SQL Server2000 Data Transformation Services. I can sign in with my username and password and see all the CA Datacom tables listed in DTS. When I select a table I get an error:

"Your provider does not support all the interfaces/methods required by DTS."

I am using the ODBC driver: CA Datacom/DB version 78.73.7970.

I have read in some other posts on the internet of people using this ODBC driver and being able to import data into SQL Server. Anybody have any ideas? Anybody doing this successfully?

your input is greatly appreciated.Ok.. I figured it out. For anyone else that may ever try doing this:

Instead of selecting the CA /Datacom DB driver in the pulldown list in DTS, I chose "Other ODBC data source" and this let me download tables.

DTS - Error

Hi,

I encouter a mystic behaviour:

1. When I am starting my DTS-package (using "other ODBC-Device" for connecting) by hand, it runs always reliable.

2. When I schedule this task, it gets always the following error:

Executed as user: ACB\Dienst-SQL. ...nStart: Copy Data from FZG_BST_DTN_247 to [Afib].[dbo].[FZG_BST_DTN_247] Step DTSRun OnError: Copy Data from FZG_BST_DTN_247 to [Afib].[dbo].[FZG_BST_DTN_247] Step, Error = -2147467259 (80004005) Error string: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 0 (0) Error string: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 DTSRun OnFinish: Copy Data from FZG_BST_DTN_247 to [Afib].[dbo].[FZG_BST_DTN_247] Step DTSRun OnStart: DTSStep_OMWCustomTasks.OMWTransferErrors_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSSt... Process Exit Code 12. The step failed.
:(

Can anybody explain me the different handling of a scheduled task and a task startet manually ?

Thx in advanceI would guess your dts package is using a user dsn to make one of your connections. When you schedule the job you are not running as the same user when you execute the package interactively and therefore you can not file the dsn.

If this is the case I think changing the dsn to a system dsn will fix the problem.

If you are not doing this then you'll need to give more information about how that specific connect is defined.

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

DSNLess connection with Failover to a SQL Server 2005 cluster

Hi,

We have configured two SQL Server 2005 in a cluster (without Witness).

We have severals ASP (using ADO) that are connecting to SQL Server 2005 with SQL Native Client in a DSNLess connection String :

driver={SQL Native Client}; server=MAIN\MAIN; Failover Partner=BCKUP\BCKUP; uid=log;pwd=pwd;database=MyBase

When using this connection string, the connection to MAIN is alrigth, but when we simulate a unavailability situation on "MyBase" or on the entire MAIN instance, the new connection are joining the MAIN and not the BCKUP SQL Server.

We tryed several Connection Strings including Failover_Partner, Initliag Catalog instead of database... but no one succed in joining the BCKUP SQL Server.

I precise that the DNS on the PC that connect to SQL server is configured and both MAIN and BCKUP are resolved in command line or in explorer.

Is there an other driver that must be used, or is there an other parameter to set in the connection string ?

Regards.

When you configure Mirroring without a witness, failover is not automatic. You must manually failover the database - you do this via alter database or using the button provided in the database mirroring properties.

Matt Hollingsworth

Sr. Program Manager

Microsoft SQL Server

DSNLess connection with Failover on SQL Server 2005 Cluster ?

Hi,

We have configured two SQL Server 2005 in a cluster (without Witness).

We have severals ASP (using ADO) that are connecting to SQL Server 2005 with SQL Native Client in a DSNLess connection String :

driver={SQL Native Client}; server=MAIN\MAIN; Failover Partner=BCKUP\BCKUP; uid=log;pwd=pwd;database=MyBase

When using this connection string, the connection to MAIN is alrigth, but when we simulate a unavailability situation on "MyBase" or on the entire MAIN instance, the new connection are joining the MAIN and not the BCKUP SQL Server.

We tryed several Connection Strings including Failover_Partner, Initliag Catalog instead of database... but no one succed in joining the BCKUP SQL Server.

I precise that the DNS on the PC that connect to SQL server is configured and both MAIN and BCKUP are resolved in command line or in explorer.

Is there an other driver that must be used, or is there an other parameter to set in the connection string ?

Regards.

Failover Partner is used in the case of database mirroring, not for the case of cluster. Check here: http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

Cluster is transparent to client, i.e., client should be able to connect to the server without knowing the server is a cluster or not. In cluster case, use "server=your_virtual_server_name" in the connection string. Please check my blog at http://blogs.msdn.com/sql_protocols/archive/2005/12/05/500013.aspx and let us know if you have further problem.