Running a vb.net exe from a dts

I'm trying to run a VB.NET exe from a dts.
This works when I run the DTS manually but just hangs when I schedule the DTS.
Has anyone seen this before ?Many times when you can run a DTS package manually, but not thru a scheduled job, it boills down to one of two things. Either you are running it from your machine instead of the server, or you have permissions that SQL Agent does not.

Physically logon to the SQL Server (not just open Enterprise Manager) and try to run your package ... if it fails, you were referencing files on your local box and not the server.

If it succeeds, then you need to look at the permissions of the service logon running SQL Agent verses your permissions.|||Thanks for the reply.
The DBA stated that the following error was found on the db server:

"Unable to Locate DLL - The Dynamic Link Library mscoree.dll could not be found in the specified path"

He is currently installing the .net framework on the sequel server.

Do you think this will do the job ?|||After installing the .net framework on the sequel server, I re run the dts and got the following message from the event viewer:

Event Type: Error
Event Source: .NET Runtime
Event Category: None
Event ID: 0
Date: 22/05/2007
Time: 14:54:35
User: N/A
Computer: SQLXXXXX
Description:
The description for Event ID ( 0 ) in Source ( .NET Runtime ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: .NET Runtime version 1.1.4322.573- FraudDailyImport.exe - Common Language Runtime Debugging Services: Application has generated an exception that could not be handled.

Process id=0x1224 (4644), Thread id=0xe64 (3684).

Click OK to terminate the application.
Click CANCEL to debug the application..[/B]

Anybody got any ideas ?

Thanks in advance.sql

running a vb.net dll from sql server

We have a new application were building which includes a module to preform
overnight processing.
Originally the idea was to schedule a job on the server to run for each
database that needs the processing completed.
Whats the best way to do this?
we are using vs 2003, ms slq server 2000
1) All the code to execute is in a vb.net dll
2) needs to be run against one or more databases
suggestions?There are a number of ways to accomplish this. One method is to wrap your
DLL in a command-line EXE that takes arguments for server, database, etc.
and then schedule a SQL Agent job with a CmdExec step for each database.
Hope this helps.
Dan Guzman
SQL Server MVP
"Claude Hebert" <chebert@.rvmags.com> wrote in message
news:ug8QC2$JGHA.2392@.TK2MSFTNGP09.phx.gbl...
> We have a new application were building which includes a module to preform
> overnight processing.
> Originally the idea was to schedule a job on the server to run for each
> database that needs the processing completed.
> Whats the best way to do this?
> we are using vs 2003, ms slq server 2000
> 1) All the code to execute is in a vb.net dll
> 2) needs to be run against one or more databases
> suggestions?
>|||....my first thought is that the VB.Net app logs in and does its thing.
In the old days, I'd write my dll in VB6 and use an ActiveX DTS or TSQL
sp_OACreate.
HTH
JeffP...
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uHSQD$$JGHA.3276@.TK2MSFTNGP09.phx.gbl...
> There are a number of ways to accomplish this. One method is to wrap your
> DLL in a command-line EXE that takes arguments for server, database, etc.
> and then schedule a SQL Agent job with a CmdExec step for each database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Claude Hebert" <chebert@.rvmags.com> wrote in message
> news:ug8QC2$JGHA.2392@.TK2MSFTNGP09.phx.gbl...
>|||What about using rundll32.exe?
--
Andrey Odegov
avodeGOV@.yandex.ru
(remove GOV to respond)|||the best method is to do with
SP_OACreate procedure
steps are :
1. if u r using vb.net dll then make sure the value of Register for COM
Interop value TRUE in Project properties ->Configuration Properties
->Build.
2. add strongname with ur project by sn -k utility.
3. publish ur dll in GAC
4. put ur dll and tlb in system32
5. register ur tlb using regtlibv12 tools
6. in sql server stored procedure create a object of tha dll by using
sp_OACreate.
7.use sp_OAMethod to access methods of that Dll.
8. use sp_OAGetProperty,sp_OASetProperty for getting or setting values.
9. use sp_OADestroy to destroy the object at last.
this is really simple.
if u need example for this then mail me personally at
kissvineet@.yahoo.com

Running a DTS Package from vb.net

Can anyone point me to some sample code ord give me an example. I'm trying to run a DTS package from my asp.net/vb.net application. I don't need to pass it any variable, just need it to run. I can't seem to find anything on the net for this.
Thanks in advance,
Ryan

There are two ways to do it, through VB.NET code, or through a stored procedure using master..xp_cmdshell. Here's one:

http://www.realworldasp.net/article.asp?article=61
http://www.msnewsgroups.net/group/microsoft.public.dotnet.languages.csharp/topic7332.aspx

|||

bmains wrote:

There are two ways to do it, through VB.NET code, or through a stored procedure using master..xp_cmdshell. Here's one:

http://www.realworldasp.net/article.asp?article=61
http://www.msnewsgroups.net/group/microsoft.public.dotnet.languages.csharp/topic7332.aspx

The links you provided are not working solutions because the creators have not addressed the SQL Server Agent permissions needed to run DTS with dtsrun.exe and xp_cmdshell.
To run DTS package through a stored proc you either use DTSRUN.exe or XP_CMDSHELL which is SQL Server Agent dependent. Try the links below for DTSRUN.exe sample code and XP_CMDSHELL configurations with permissions. What I am saying is to run DTS with SQL Server Agent dependent service like xp_CMDSHELL you must give the account used to install SQL Server Agent Admin permissions in Windows and SQL Server. Hope this helps.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp

http://www.sqlteam.com/item.asp?ItemID=19595

|||This seems way too complicated. Isn't there simple code I can use to create a stored procedue that calls a dts package. Then I can just execute the stored procedure from my asp page. I'm just looking for some sample T-SQL code that executes a DTS Package.
like this. spexecuteDTSPackage('mypackage')
This would be wonderful.|||I don't know of an easier way because it is SQL Server Agent that runs your DTS package through xp_cmdshell.|||That is the DTSrun utility, which in a stored procedure, you have to use master..xp_cmdShell, and get the rights to do so. See, to run it, it needs to know what server to run it on, what is the name, what are the parameters being passed into it (which is cool because you can dynamically assign the server variables in the package with values passed into the stored procedure), what is the package password if there is one|||

I finally got it working. I think I'm going to write a tutorial since there aren't any good ones out there.

I generated the code for the dtsrun.exe with the dtsgui tool then created a stored procedure.

Thanks for all your help.

|||

You ever write a tutorial for this? I have the same problem and need major help getting a dts to run from .net.

Cordell