executing DTS - DSN error...

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...
-emilyAlso, I should clarify. Everything is local. Everything's on my
laptop on my coffee table. No network. SQLServer is local, Foxpro
data is local.|||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. :)|||emily (ehart624@.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, esquel@.sommarskog.se

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

Executing an application from a query

Hi All,
Is it possible to execute a file or perhaps send a call to a
webpage using an SQL query? Does anyone know of any such feature.
Basically I need to call a webpage(coldfusion script) from my query and
I am wondering if anyone knows of any way that his would be possible.
Thanks a heap in advance for all your help !
Harkirat>> Is it possible to execute a file or perhaps send a call to a webpage
Can you be more specific? SQL Queries are executed on the server while web
pages are displayed on the client. So what exactly do you mean by "calling"
a webpage? Are you trying to display the webpage on the client or somewhere
else? SQL Server has some provisions for generating web pages based on
generated resultsets, but I am not sure that is what you are looking for.
Anith|||Hi Anith,
What I need to do is be able to execute a coldfusion script
via a query. The script can be run via a webpage e.g.
http://mysite.com/CFScript.cfm
So if I could make a 'call' to my webpage via my query that would work.
Also if this is not a possibility would it be possible to execute a
.exe file using a query? That might help too.
Thanks for your reply.
Harkirat
Anith Sen wrote:
> Can you be more specific? SQL Queries are executed on the server while web
> pages are displayed on the client. So what exactly do you mean by "calling
"
> a webpage? Are you trying to display the webpage on the client or somewher
e
> else? SQL Server has some provisions for generating web pages based on
> generated resultsets, but I am not sure that is what you are looking for.
> --
> Anith|||You can use a stored procedure to execute an .exe file using
xp_cmdshell, but there are security issues. I don't think that it;s
the best approach to what you are trying to do, however; what does a
cold fusion script have to do with your data?
Can you provide a little more detail about the business problem you're
trying to solve, and perhaps we can suggest a better alternative.
Stu|||Hi Stu,
My coldfusion script has logic that
updates a table in the database. I need this process to run everytime a
new row is inserted in my table. Hence I was thinking of putting an
insert trigger that calls this coldfusion script.
Can you give an example on how to execute a .exe using a stored
procedure. I could perhaps make a .exe that executes the coldfusion
script which in turn would be called via my trigger.
Thanks.
Harkirat|||Hi
Look at xp_cmdshell in BOL.
If your exe fails, the insert will get rolled back as an error like that can
not be handled in T-SQL. It is not a good idea. Rather write a row in a
queue table, and have something poll the table and then call your exe.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"harry" <i1073@.tamu.edu> wrote in message
news:1124657414.378047.144430@.g44g2000cwa.googlegroups.com...
> Hi Stu,
> My coldfusion script has logic that
> updates a table in the database. I need this process to run everytime a
> new row is inserted in my table. Hence I was thinking of putting an
> insert trigger that calls this coldfusion script.
> Can you give an example on how to execute a .exe using a stored
> procedure. I could perhaps make a .exe that executes the coldfusion
> script which in turn would be called via my trigger.
> Thanks.
> Harkirat
>|||I think your application would perform better if you could keep the
database logic at the dataase level; if it were me, I would probably
simply run the logic in T-SQL as part of the trigger. Why bubble back
up (unless of course your coldfusion script is extremely complicated)?
As for an example, I don't write many triggers, and I disable
xp_cmdshell altogether. Books OnLine is your best bet.
Stu|||Hi Stu,
My coldfusion is indeed complicated thats why I don't wish
to do it in SQL.
Thanks for your help.
Harkirat|||Hi Mike,
I had already thought of that. Seems like my best bet
now.
Thanks for your help.
Harkirat

executing a trigger

HI all,
is the only or perhaps the best way of executing a tables trigger via
another trigger or SP by actually updating said table
I have a calulation in table a update trigger. It is based on related values
in table b But if those value in table b are change, table a's values need
to be recalculated.
Am I on the right track, or is there a betterway of doing this.
Thanks
RobertIf you don=B4r have control about the process changing the first table,
thats the only way. If yxou have the chance to control the process, the
logic and the statements fired, just add another statement which does
the update of the second table within the same transaction.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Robert
In that case you need to use a trigger .Create a trigger on table b and
check if the data has changed run a calculation on table Actually if you'd
post samle data and an expected result we'd suggest may be a better solution
"Robert Bravery" <me@.u.com> wrote in message
news:eMqdx6aSGHA.5036@.TK2MSFTNGP12.phx.gbl...
> HI all,
> is the only or perhaps the best way of executing a tables trigger via
> another trigger or SP by actually updating said table
> I have a calulation in table a update trigger. It is based on related
> values
> in table b But if those value in table b are change, table a's values need
> to be recalculated.
> Am I on the right track, or is there a betterway of doing this.
> Thanks
> Robert
>|||HI,
Thanks to all,
Uri, I'm still in design phase with this one. trtying to cover all my bases.
Robert
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OLS2eGbSGHA.5108@.TK2MSFTNGP11.phx.gbl...
> Robert
> In that case you need to use a trigger .Create a trigger on table b and
> check if the data has changed run a calculation on table Actually if
you'd
> post samle data and an expected result we'd suggest may be a better
solution
>
>
> "Robert Bravery" <me@.u.com> wrote in message
> news:eMqdx6aSGHA.5036@.TK2MSFTNGP12.phx.gbl...
need
>