executing DTS - DSN error...

[emily]

Perhaps this has already been answered. If so, I apologize. I'm a
newbie at this stuff. I have a DTS package to import some Visual
Foxpro data. Yes, I said Visual Foxpro. :) The DTS package works
beautifully when I rightclick on the package and choose "execute." And
it works beautifully when I run dtsrun.... at the DOS prompt. But it
doesn't work when I try to execute it with code in Query Analyzer.
I've tried exec master..xp_cmdshell 'dtsrun ...' And I've tried
scheduling the package as a Job, disabling the Job and then using
msdb..sp_start_job to run it. Neither of those work.

I get a "DSN not found and no default driver specified."

My DSN stuff looks OK to me, but perhaps some of that is wrong. What
does the DSN have to look like in order for the code in Query Analyzer
to find it? If the DSN is screwed up, why would it work OK when
executed from some places, but not in others? What am I missing?
Any help would be much appreciated. :) thanks...
-emily

[emily]

Also, I should clarify. Everything is local. Everything's on my
laptop on my coffee table. No network. SQLServer is local, Foxpro
data is local.

[emily]

OK, false alarm, I figured this out myself. Well, I didn't really
figure it out, but I did get it to work. If I set up the FoxPro DSN as
a System DSN, then it works. User DSN doesn't. I have no idea why.
I'll continue to play around with it. Next time I won't post here
until I'm absolutely sure that I've tried absolutely everything. :)

[Erland Sommarskog]

emily (eh******@hotmail.com) writes:

OK, false alarm, I figured this out myself. Well, I didn't really
figure it out, but I did get it to work. If I set up the FoxPro DSN as
a System DSN, then it works. User DSN doesn't. I have no idea why.
I'll continue to play around with it. Next time I won't post here
until I'm absolutely sure that I've tried absolutely everything. :)

I have an idea. If SQL Server runs as Local System it is not likely
to find User DSN for your user. Thus it must be a system DSN.

Recall that when you run from QA, it's SQL Server that runs the
package.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp