Using OSQL and DTSRUN

[mike]

I have a question. I am doing some work for someone and I have a batch
file that they can run that will execute an OSQL line and a DTSRUN
line. In both lines I run them using the /S /U /P switches and of
course the /N or /i switch to tell it what to run. I have also tried
replacing the /U /P switches with the /E switch.

My problem is that as long as I specify the users password on the OSQL
line (either with /U & /P or with /E & /P) it will run. If I try and
just use the /E it will say password failed for DOMAIN/USER . Ok, I
don't really care I can specify the password and the script will run.

However no matter what I do the DTSRUN line will not run, it gives me
this same password error.

I can run this line just fine on my PC on my network and my domain
using just the /S /E switches.

Any ideas as to why it will work for OSQL but not DTSRUN?
Thanks in advance.

[Erland Sommarskog]

mike (mi********@gmail.com) writes:

I have a question. I am doing some work for someone and I have a batch
file that they can run that will execute an OSQL line and a DTSRUN
line. In both lines I run them using the /S /U /P switches and of
course the /N or /i switch to tell it what to run. I have also tried
replacing the /U /P switches with the /E switch.

My problem is that as long as I specify the users password on the OSQL
line (either with /U & /P or with /E & /P) it will run. If I try and
just use the /E it will say password failed for DOMAIN/USER . Ok, I
don't really care I can specify the password and the script will run.
When I try /E and /P with OSQL, I get "Error: Conflicting switches : -E and
-P"

And the message with /E only, would not really be a about password, but
something like "not associated with a trusted connection".
However no matter what I do the DTSRUN line will not run, it gives me
this same password error.

I can run this line just fine on my PC on my network and my domain
using just the /S /E switches.

Any ideas as to why it will work for OSQL but not DTSRUN?

Apparently trusted connection does not work to your customer's domain.
In that case you need to use SQL authentication, but that is not on
the form DOMAIN/USER.

It could help little if you posted the exact command lines you are
using, and the error you get with each line.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[mike]

Yea to me using the /E /P switches did not make sense either, but using
the /E switch by itself gave the error and using both worked for me as
dud using the /U /P which is more standard.
Here is what I am using

dtsrun /S server /U username /P password /N dtspackage
Here is what it is returning

Step 'Copy Data from xxxxxxxx to [xxxx].[dbo].[xxxxxxxxxxxx] Step'
failed

Step Error Source: Microsoft OLE DB Provider for SQL Server
Step Error Description:Login failed for user 'domain\username'.
Step Error code: 80040E4D
Step Error Help File:
Step Error Help Context ID:0
But like I said if I use the OSQL lines they run fine like this

osql /S server /U username /P password /i c:\live_scripts\script.sql

So why would login work for the script and not for the dts package?

[Erland Sommarskog]

mike (mi********@gmail.com) writes:

Yea to me using the /E /P switches did not make sense either, but using
the /E switch by itself gave the error and using both worked for me as
dud using the /U /P which is more standard.
Here is what I am using

dtsrun /S server /U username /P password /N dtspackage
Here is what it is returning

Step 'Copy Data from xxxxxxxx to [xxxx].[dbo].[xxxxxxxxxxxx] Step'
failed

Step Error Source: Microsoft OLE DB Provider for SQL Server
Step Error Description:Login failed for user 'domain\username'.
Step Error code: 80040E4D
Step Error Help File:
Step Error Help Context ID:0
But like I said if I use the OSQL lines they run fine like this

osql /S server /U username /P password /i c:\live_scripts\script.sql

So why would login work for the script and not for the dts package?

One cannot accuse me for knowing too much about DTS, but what does
the DTS package do? Does it access the server you connect to only?
/S /U and /P serves to find the package as such, but does not the
package itself log in somewhere? Looks llike this login fails.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[mike]

Good question, something I will have to look into. It is taking data
from some access tables and pulls them into SQL However these access
tables are on a different machine, not on the SQL server. Maybe the
failure is on the machine that has the access tables.
By the way it runs just fine if I run it from the SQL server. We just
want to be able to run it from an individual workstation.

[Erland Sommarskog]

mike (mi********@gmail.com) writes:

Good question, something I will have to look into. It is taking data
from some access tables and pulls them into SQL However these access
tables are on a different machine, not on the SQL server. Maybe the
failure is on the machine that has the access tables.
By the way it runs just fine if I run it from the SQL server. We just
want to be able to run it from an individual workstation.

The error message comes from the SQL Server OLE DB Provider, so the
problem is not likely to be with the access to the Access databases.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx