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_cmds hell '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]

Re: executing DTS - DSN error...

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]

Re: executing DTS - DSN error...

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]

Re: executing DTS - DSN error...

emily (ehart624@hotma il.com) writes:[color=blue]
> 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. :)[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp