Hi everyone,
i'm trying to call a stored procedure that runs rs.exe from a DTS
package,the package works perfectly giving me no errors but when i look at
the results rs.exe has done nothing.
If I call the stored procedure directly it works just fine rendering my pdf
report on filesystem
Anyone has ideas on what is happenning?
Thank YouLnZ wrote:
> Anyone has ideas on what is happenning?
Check the permissions of the account under wich the SQL-ServerAgent-service
is running.
regards
Franksql
Showing posts with label exe. Show all posts
Showing posts with label exe. Show all posts
Sunday, March 25, 2012
Tuesday, March 20, 2012
Calling an external program
Hi,
Is there a stored proc or a function I can call from Query Analyzer to execute another .EXE and return when it's finished?
I'd like something like
SET @.RET = sp_CallEXE('c:\mypath\myprogram.exe myparameters')
(Except for the name of the function, there should be something that does this right? I mean, DTS does it already so it must be referring to a function or a SP.)
I want an answer to so that I can validate that everything went well (hence the @.RET variable)
Thanks,
Skip.xp_cmdshell may help you get where you want, but you'll have security issues to think about. It'll be something like "exec @.ret=master..xp_cmdshell '...'"
Is there a stored proc or a function I can call from Query Analyzer to execute another .EXE and return when it's finished?
I'd like something like
SET @.RET = sp_CallEXE('c:\mypath\myprogram.exe myparameters')
(Except for the name of the function, there should be something that does this right? I mean, DTS does it already so it must be referring to a function or a SP.)
I want an answer to so that I can validate that everything went well (hence the @.RET variable)
Thanks,
Skip.xp_cmdshell may help you get where you want, but you'll have security issues to think about. It'll be something like "exec @.ret=master..xp_cmdshell '...'"
Calling an application from SQL (xp_cmdshell)
Hi,
I am trying to run an executable from MS SQL Server 2000. I tried using the commands:
EXEC master..xp_cmdshell '"\\comp1\c\test.exe"', NO_OUTPUT
and
EXEC master..xp_cmdshell 'Start Test "\\comp1\c\test.exe" , NO_OUTPUT
When I go to task manager on comp1 I see that the process is listed as running only under Processes, not under Application. In addition the application doesnt seem to execute properly and the SQL Query doesnt terminate. For testing processes I have just a small VB project that pops up a message box and then exits (it works fine by running it from windows). I never see the message box, nor does it exit the process.
If I try to manually cancel the SQL Query, it still will not stop until I kill the process for test.exe from the task manager.
Does anyone have any ideas on how I could get the query to terminate properly and how to get the executable to do its thing?
Sincerely,
Andrei Girenkovyeah...don't call gui apps from xp_cmdshell unless you want to hang your server...
Only use batch executables that don't require ANY prompting...|||Well I wasn't aware of that, however the executable I'm trying to launch actually doesnt have any sort of GUI...all it does is checks some output from another program and updates some DB tables.
I found that after launching the executable through double clicking it worked fine, but when launching it from SQL it didn't update the DB. I made the small project with the message box as an easier way of testing it rather than having to go to SQL every time and checking whether or not a table got updated.
~Andrei|||Can you be more specific as to what this executable is doing ?|||[i]I made the small project with the message box
~Andrei
You don't think a message box is a gui?|||I mean the actual executable I am trying to run has no GUI. The messagebox program is only for testing. I now scrapped the message box project because of the GUI and am trying to just run/test with the actual executable, however I am not seeing any results either.
My program does the following: I am trying to record the Volume Serial of the C drive of the machine the instance of the SQL server is running. I have an executable which reads the volume serial, scrambles it, records it into the DB, and then exits out. It takes as a command line argument the name of the instance of SQL server on that machine.
I am trying to write a stored procedure that I could call which would run this executable and pass it the instance name as an argument. The call in the stored procedure is the following:
EXEC master..xp_cmdshell '"C:\volumeserial.exe" -' + (select @.@.SERVERNAME), NO_OUTPUT
Right now the select @.@.SERVERNAME part is hardcoded to the server name because I was getting an syntax error at the + before the select statement, and the comma after it. It actually looks like this:
EXEC master..xp_cmdshell '"C:\volumeserial.exe" -InstanceName', NO_OUTPUT
When I call this stored procedure I can see in the task manager under processes that Volumeserial.exe is running, however the DB doesnt get updated, the process never terminates, and the stored procedure never gets past that line. Like I mentioned earlier, the program works fine if I call it from outside SQL, so I think it's a SQL issue.
~Andrei Girenkov|||One thing to double check would be the connection string to the database. If you are using a trusted connection, remember that the application will be running under the identity of the SQL Server Service account.
Another thing to check would be permissions on the executable. Again, be sure that the SQL Server Service account as execute permissions on the application executable.
Make sure that you test you application executable while logged in as the SQL Server service account.
Regards,
hmscott|||My SQL Server Service account is the Administrator. I can run the executable just fine when logged in as Administrator.
~Andrei|||how about making the executable just return the scrambled serial number without trying to connect to the server and do the update? you can do those things in your stored procedure.
create table #tmp ([output] nvarchar(4000) null)
insert #tmp EXEC master..xp_cmdshell 'C:\volumeserial.exe'
delete #tmp where [output] is null
update y set scrambled_serial = [output]
from your_table y, #tmp where y.drive_letter = 'C'|||Thank you everyone for all your help. I had a hunch that it was not connecting to SQL properly so instead of writing the value back to the DB I had the program write it out to a file along with the command line arguments. It turned out that the problem was caused because in SQL I was passing the InstanceName parameter to the program with a dash (-). The program wasn't expecting that.
The other problem of not being able to select @.@.ServerName was fixed by just declaring the whole string as a varchar() and passing that variable to xp_cmdshell instead of the concatinated string.
Thanks again everybody who helped
~Andreisql
I am trying to run an executable from MS SQL Server 2000. I tried using the commands:
EXEC master..xp_cmdshell '"\\comp1\c\test.exe"', NO_OUTPUT
and
EXEC master..xp_cmdshell 'Start Test "\\comp1\c\test.exe" , NO_OUTPUT
When I go to task manager on comp1 I see that the process is listed as running only under Processes, not under Application. In addition the application doesnt seem to execute properly and the SQL Query doesnt terminate. For testing processes I have just a small VB project that pops up a message box and then exits (it works fine by running it from windows). I never see the message box, nor does it exit the process.
If I try to manually cancel the SQL Query, it still will not stop until I kill the process for test.exe from the task manager.
Does anyone have any ideas on how I could get the query to terminate properly and how to get the executable to do its thing?
Sincerely,
Andrei Girenkovyeah...don't call gui apps from xp_cmdshell unless you want to hang your server...
Only use batch executables that don't require ANY prompting...|||Well I wasn't aware of that, however the executable I'm trying to launch actually doesnt have any sort of GUI...all it does is checks some output from another program and updates some DB tables.
I found that after launching the executable through double clicking it worked fine, but when launching it from SQL it didn't update the DB. I made the small project with the message box as an easier way of testing it rather than having to go to SQL every time and checking whether or not a table got updated.
~Andrei|||Can you be more specific as to what this executable is doing ?|||[i]I made the small project with the message box
~Andrei
You don't think a message box is a gui?|||I mean the actual executable I am trying to run has no GUI. The messagebox program is only for testing. I now scrapped the message box project because of the GUI and am trying to just run/test with the actual executable, however I am not seeing any results either.
My program does the following: I am trying to record the Volume Serial of the C drive of the machine the instance of the SQL server is running. I have an executable which reads the volume serial, scrambles it, records it into the DB, and then exits out. It takes as a command line argument the name of the instance of SQL server on that machine.
I am trying to write a stored procedure that I could call which would run this executable and pass it the instance name as an argument. The call in the stored procedure is the following:
EXEC master..xp_cmdshell '"C:\volumeserial.exe" -' + (select @.@.SERVERNAME), NO_OUTPUT
Right now the select @.@.SERVERNAME part is hardcoded to the server name because I was getting an syntax error at the + before the select statement, and the comma after it. It actually looks like this:
EXEC master..xp_cmdshell '"C:\volumeserial.exe" -InstanceName', NO_OUTPUT
When I call this stored procedure I can see in the task manager under processes that Volumeserial.exe is running, however the DB doesnt get updated, the process never terminates, and the stored procedure never gets past that line. Like I mentioned earlier, the program works fine if I call it from outside SQL, so I think it's a SQL issue.
~Andrei Girenkov|||One thing to double check would be the connection string to the database. If you are using a trusted connection, remember that the application will be running under the identity of the SQL Server Service account.
Another thing to check would be permissions on the executable. Again, be sure that the SQL Server Service account as execute permissions on the application executable.
Make sure that you test you application executable while logged in as the SQL Server service account.
Regards,
hmscott|||My SQL Server Service account is the Administrator. I can run the executable just fine when logged in as Administrator.
~Andrei|||how about making the executable just return the scrambled serial number without trying to connect to the server and do the update? you can do those things in your stored procedure.
create table #tmp ([output] nvarchar(4000) null)
insert #tmp EXEC master..xp_cmdshell 'C:\volumeserial.exe'
delete #tmp where [output] is null
update y set scrambled_serial = [output]
from your_table y, #tmp where y.drive_letter = 'C'|||Thank you everyone for all your help. I had a hunch that it was not connecting to SQL properly so instead of writing the value back to the DB I had the program write it out to a file along with the command line arguments. It turned out that the problem was caused because in SQL I was passing the InstanceName parameter to the program with a dash (-). The program wasn't expecting that.
The other problem of not being able to select @.@.ServerName was fixed by just declaring the whole string as a varchar() and passing that variable to xp_cmdshell instead of the concatinated string.
Thanks again everybody who helped
~Andreisql
Monday, March 19, 2012
Calling an .EXE from a SQL Job?
I'm trying to call a .exe as a Job Step that pulls some data and sends out
custom emails that will execute after a DTS package runs. I tested the EXE
from the command line and it works but I don't know how to call it from with
the Job Step.
I have the step called ''Example of calling an EXE" and set an Operating
System Command (CmdExec) with:
xp_cmdshell 'C:\MyApp\SendAlert.exe' , no_output
When it runs, I check the Windows event and it says:
SQL Server Scheduled Job 'Example of calling an EXE'
(0x0AE46A0A26688D48B29A3C71684CB284) - Status: Failed - Invoked on:
2005-10-27 13:04:45 - Message: The job failed. The Job was invoked by User
sa. The last step to run was step 1 (Calling the EXE).
I have no idea why it's failing when called as a job?
One other thing I found while executing the following in query analyzer:
xp_cmdshell 'C:\MyApp\SendAlert.exe'
returns:
"The system cannot find the path specified"
Thanks again.
"Dave" wrote:
> I'm trying to call a .exe as a Job Step that pulls some data and sends out
> custom emails that will execute after a DTS package runs. I tested the EXE
> from the command line and it works but I don't know how to call it from with
> the Job Step.
> I have the step called ''Example of calling an EXE" and set an Operating
> System Command (CmdExec) with:
> xp_cmdshell 'C:\MyApp\SendAlert.exe' , no_output
> When it runs, I check the Windows event and it says:
> SQL Server Scheduled Job 'Example of calling an EXE'
> (0x0AE46A0A26688D48B29A3C71684CB284) - Status: Failed - Invoked on:
> 2005-10-27 13:04:45 - Message: The job failed. The Job was invoked by User
> sa. The last step to run was step 1 (Calling the EXE).
> I have no idea why it's failing when called as a job?
|||Dave wrote:
> I'm trying to call a .exe as a Job Step that pulls some data and
> sends out custom emails that will execute after a DTS package runs.
> I tested the EXE from the command line and it works but I don't know
> how to call it from with the Job Step.
> I have the step called ''Example of calling an EXE" and set an
> Operating System Command (CmdExec) with:
> xp_cmdshell 'C:\MyApp\SendAlert.exe' , no_output
> When it runs, I check the Windows event and it says:
> SQL Server Scheduled Job 'Example of calling an EXE'
> (0x0AE46A0A26688D48B29A3C71684CB284) - Status: Failed - Invoked on:
> 2005-10-27 13:04:45 - Message: The job failed. The Job was invoked
> by User sa. The last step to run was step 1 (Calling the EXE).
> I have no idea why it's failing when called as a job?
xp_cmdshell is in the master database. You need to fully qualify the
name if you are not in master:
exec master..xp_cmdshell 'dir'
Also, you need to make sure the SQL Server service account has access to
the folder where you placed the EXE. If not, you need to grant the
account access of move the file(s) to a folder where the account has
access.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Dave
You can also run cmd commands from SQL jobs In the same job you are running
DTS
Paul Zanbaka
DBA, Programmer Analyst
"David Gugick" wrote:
> Dave wrote:
> xp_cmdshell is in the master database. You need to fully qualify the
> name if you are not in master:
> exec master..xp_cmdshell 'dir'
> Also, you need to make sure the SQL Server service account has access to
> the folder where you placed the EXE. If not, you need to grant the
> account access of move the file(s) to a folder where the account has
> access.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
custom emails that will execute after a DTS package runs. I tested the EXE
from the command line and it works but I don't know how to call it from with
the Job Step.
I have the step called ''Example of calling an EXE" and set an Operating
System Command (CmdExec) with:
xp_cmdshell 'C:\MyApp\SendAlert.exe' , no_output
When it runs, I check the Windows event and it says:
SQL Server Scheduled Job 'Example of calling an EXE'
(0x0AE46A0A26688D48B29A3C71684CB284) - Status: Failed - Invoked on:
2005-10-27 13:04:45 - Message: The job failed. The Job was invoked by User
sa. The last step to run was step 1 (Calling the EXE).
I have no idea why it's failing when called as a job?
One other thing I found while executing the following in query analyzer:
xp_cmdshell 'C:\MyApp\SendAlert.exe'
returns:
"The system cannot find the path specified"
Thanks again.
"Dave" wrote:
> I'm trying to call a .exe as a Job Step that pulls some data and sends out
> custom emails that will execute after a DTS package runs. I tested the EXE
> from the command line and it works but I don't know how to call it from with
> the Job Step.
> I have the step called ''Example of calling an EXE" and set an Operating
> System Command (CmdExec) with:
> xp_cmdshell 'C:\MyApp\SendAlert.exe' , no_output
> When it runs, I check the Windows event and it says:
> SQL Server Scheduled Job 'Example of calling an EXE'
> (0x0AE46A0A26688D48B29A3C71684CB284) - Status: Failed - Invoked on:
> 2005-10-27 13:04:45 - Message: The job failed. The Job was invoked by User
> sa. The last step to run was step 1 (Calling the EXE).
> I have no idea why it's failing when called as a job?
|||Dave wrote:
> I'm trying to call a .exe as a Job Step that pulls some data and
> sends out custom emails that will execute after a DTS package runs.
> I tested the EXE from the command line and it works but I don't know
> how to call it from with the Job Step.
> I have the step called ''Example of calling an EXE" and set an
> Operating System Command (CmdExec) with:
> xp_cmdshell 'C:\MyApp\SendAlert.exe' , no_output
> When it runs, I check the Windows event and it says:
> SQL Server Scheduled Job 'Example of calling an EXE'
> (0x0AE46A0A26688D48B29A3C71684CB284) - Status: Failed - Invoked on:
> 2005-10-27 13:04:45 - Message: The job failed. The Job was invoked
> by User sa. The last step to run was step 1 (Calling the EXE).
> I have no idea why it's failing when called as a job?
xp_cmdshell is in the master database. You need to fully qualify the
name if you are not in master:
exec master..xp_cmdshell 'dir'
Also, you need to make sure the SQL Server service account has access to
the folder where you placed the EXE. If not, you need to grant the
account access of move the file(s) to a folder where the account has
access.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Dave
You can also run cmd commands from SQL jobs In the same job you are running
DTS
Paul Zanbaka
DBA, Programmer Analyst
"David Gugick" wrote:
> Dave wrote:
> xp_cmdshell is in the master database. You need to fully qualify the
> name if you are not in master:
> exec master..xp_cmdshell 'dir'
> Also, you need to make sure the SQL Server service account has access to
> the folder where you placed the EXE. If not, you need to grant the
> account access of move the file(s) to a folder where the account has
> access.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
Calling an .EXE from a SQL Job?
I'm trying to call a .exe as a Job Step that pulls some data and sends out
custom emails that will execute after a DTS package runs. I tested the EXE
from the command line and it works but I don't know how to call it from with
the Job Step.
I have the step called ''Example of calling an EXE" and set an Operating
System Command (CmdExec) with:
xp_cmdshell 'C:\MyApp\SendAlert.exe' , no_output
When it runs, I check the Windows event and it says:
SQL Server Scheduled Job 'Example of calling an EXE'
(0x0AE46A0A26688D48B29A3C71684CB284) - Status: Failed - Invoked on:
2005-10-27 13:04:45 - Message: The job failed. The Job was invoked by User
sa. The last step to run was step 1 (Calling the EXE).
I have no idea why it's failing when called as a job?One other thing I found while executing the following in query analyzer:
xp_cmdshell 'C:\MyApp\SendAlert.exe'
returns:
"The system cannot find the path specified"
Thanks again.
"Dave" wrote:
> I'm trying to call a .exe as a Job Step that pulls some data and sends out
> custom emails that will execute after a DTS package runs. I tested the EX
E
> from the command line and it works but I don't know how to call it from wi
th
> the Job Step.
> I have the step called ''Example of calling an EXE" and set an Operating
> System Command (CmdExec) with:
> xp_cmdshell 'C:\MyApp\SendAlert.exe' , no_output
> When it runs, I check the Windows event and it says:
> SQL Server Scheduled Job 'Example of calling an EXE'
> (0x0AE46A0A26688D48B29A3C71684CB284) - Status: Failed - Invoked on:
> 2005-10-27 13:04:45 - Message: The job failed. The Job was invoked by Use
r
> sa. The last step to run was step 1 (Calling the EXE).
> I have no idea why it's failing when called as a job?|||Dave wrote:
> I'm trying to call a .exe as a Job Step that pulls some data and
> sends out custom emails that will execute after a DTS package runs.
> I tested the EXE from the command line and it works but I don't know
> how to call it from with the Job Step.
> I have the step called ''Example of calling an EXE" and set an
> Operating System Command (CmdExec) with:
> xp_cmdshell 'C:\MyApp\SendAlert.exe' , no_output
> When it runs, I check the Windows event and it says:
> SQL Server Scheduled Job 'Example of calling an EXE'
> (0x0AE46A0A26688D48B29A3C71684CB284) - Status: Failed - Invoked on:
> 2005-10-27 13:04:45 - Message: The job failed. The Job was invoked
> by User sa. The last step to run was step 1 (Calling the EXE).
> I have no idea why it's failing when called as a job?
xp_cmdshell is in the master database. You need to fully qualify the
name if you are not in master:
exec master..xp_cmdshell 'dir'
Also, you need to make sure the SQL Server service account has access to
the folder where you placed the EXE. If not, you need to grant the
account access of move the file(s) to a folder where the account has
access.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Dave
You can also run cmd commands from SQL jobs In the same job you are running
DTS
--
Paul Zanbaka
DBA, Programmer Analyst
"David Gugick" wrote:
> Dave wrote:
> xp_cmdshell is in the master database. You need to fully qualify the
> name if you are not in master:
> exec master..xp_cmdshell 'dir'
> Also, you need to make sure the SQL Server service account has access to
> the folder where you placed the EXE. If not, you need to grant the
> account access of move the file(s) to a folder where the account has
> access.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
custom emails that will execute after a DTS package runs. I tested the EXE
from the command line and it works but I don't know how to call it from with
the Job Step.
I have the step called ''Example of calling an EXE" and set an Operating
System Command (CmdExec) with:
xp_cmdshell 'C:\MyApp\SendAlert.exe' , no_output
When it runs, I check the Windows event and it says:
SQL Server Scheduled Job 'Example of calling an EXE'
(0x0AE46A0A26688D48B29A3C71684CB284) - Status: Failed - Invoked on:
2005-10-27 13:04:45 - Message: The job failed. The Job was invoked by User
sa. The last step to run was step 1 (Calling the EXE).
I have no idea why it's failing when called as a job?One other thing I found while executing the following in query analyzer:
xp_cmdshell 'C:\MyApp\SendAlert.exe'
returns:
"The system cannot find the path specified"
Thanks again.
"Dave" wrote:
> I'm trying to call a .exe as a Job Step that pulls some data and sends out
> custom emails that will execute after a DTS package runs. I tested the EX
E
> from the command line and it works but I don't know how to call it from wi
th
> the Job Step.
> I have the step called ''Example of calling an EXE" and set an Operating
> System Command (CmdExec) with:
> xp_cmdshell 'C:\MyApp\SendAlert.exe' , no_output
> When it runs, I check the Windows event and it says:
> SQL Server Scheduled Job 'Example of calling an EXE'
> (0x0AE46A0A26688D48B29A3C71684CB284) - Status: Failed - Invoked on:
> 2005-10-27 13:04:45 - Message: The job failed. The Job was invoked by Use
r
> sa. The last step to run was step 1 (Calling the EXE).
> I have no idea why it's failing when called as a job?|||Dave wrote:
> I'm trying to call a .exe as a Job Step that pulls some data and
> sends out custom emails that will execute after a DTS package runs.
> I tested the EXE from the command line and it works but I don't know
> how to call it from with the Job Step.
> I have the step called ''Example of calling an EXE" and set an
> Operating System Command (CmdExec) with:
> xp_cmdshell 'C:\MyApp\SendAlert.exe' , no_output
> When it runs, I check the Windows event and it says:
> SQL Server Scheduled Job 'Example of calling an EXE'
> (0x0AE46A0A26688D48B29A3C71684CB284) - Status: Failed - Invoked on:
> 2005-10-27 13:04:45 - Message: The job failed. The Job was invoked
> by User sa. The last step to run was step 1 (Calling the EXE).
> I have no idea why it's failing when called as a job?
xp_cmdshell is in the master database. You need to fully qualify the
name if you are not in master:
exec master..xp_cmdshell 'dir'
Also, you need to make sure the SQL Server service account has access to
the folder where you placed the EXE. If not, you need to grant the
account access of move the file(s) to a folder where the account has
access.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Dave
You can also run cmd commands from SQL jobs In the same job you are running
DTS
--
Paul Zanbaka
DBA, Programmer Analyst
"David Gugick" wrote:
> Dave wrote:
> xp_cmdshell is in the master database. You need to fully qualify the
> name if you are not in master:
> exec master..xp_cmdshell 'dir'
> Also, you need to make sure the SQL Server service account has access to
> the folder where you placed the EXE. If not, you need to grant the
> account access of move the file(s) to a folder where the account has
> access.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
Calling an .EXE from a SQL Job?
I'm trying to call a .exe as a Job Step that pulls some data and sends out
custom emails that will execute after a DTS package runs. I tested the EXE
from the command line and it works but I don't know how to call it from with
the Job Step.
I have the step called ''Example of calling an EXE" and set an Operating
System Command (CmdExec) with:
xp_cmdshell 'C:\MyApp\SendAlert.exe' , no_output
When it runs, I check the Windows event and it says:
SQL Server Scheduled Job 'Example of calling an EXE'
(0x0AE46A0A26688D48B29A3C71684CB284) - Status: Failed - Invoked on:
2005-10-27 13:04:45 - Message: The job failed. The Job was invoked by User
sa. The last step to run was step 1 (Calling the EXE).
I have no idea why it's failing when called as a job?One other thing I found while executing the following in query analyzer:
xp_cmdshell 'C:\MyApp\SendAlert.exe'
returns:
"The system cannot find the path specified"
Thanks again.
"Dave" wrote:
> I'm trying to call a .exe as a Job Step that pulls some data and sends out
> custom emails that will execute after a DTS package runs. I tested the EXE
> from the command line and it works but I don't know how to call it from with
> the Job Step.
> I have the step called ''Example of calling an EXE" and set an Operating
> System Command (CmdExec) with:
> xp_cmdshell 'C:\MyApp\SendAlert.exe' , no_output
> When it runs, I check the Windows event and it says:
> SQL Server Scheduled Job 'Example of calling an EXE'
> (0x0AE46A0A26688D48B29A3C71684CB284) - Status: Failed - Invoked on:
> 2005-10-27 13:04:45 - Message: The job failed. The Job was invoked by User
> sa. The last step to run was step 1 (Calling the EXE).
> I have no idea why it's failing when called as a job?|||Dave wrote:
> I'm trying to call a .exe as a Job Step that pulls some data and
> sends out custom emails that will execute after a DTS package runs.
> I tested the EXE from the command line and it works but I don't know
> how to call it from with the Job Step.
> I have the step called ''Example of calling an EXE" and set an
> Operating System Command (CmdExec) with:
> xp_cmdshell 'C:\MyApp\SendAlert.exe' , no_output
> When it runs, I check the Windows event and it says:
> SQL Server Scheduled Job 'Example of calling an EXE'
> (0x0AE46A0A26688D48B29A3C71684CB284) - Status: Failed - Invoked on:
> 2005-10-27 13:04:45 - Message: The job failed. The Job was invoked
> by User sa. The last step to run was step 1 (Calling the EXE).
> I have no idea why it's failing when called as a job?
xp_cmdshell is in the master database. You need to fully qualify the
name if you are not in master:
exec master..xp_cmdshell 'dir'
Also, you need to make sure the SQL Server service account has access to
the folder where you placed the EXE. If not, you need to grant the
account access of move the file(s) to a folder where the account has
access.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Dave
You can also run cmd commands from SQL jobs In the same job you are running
DTS
--
Paul Zanbaka
DBA, Programmer Analyst
"David Gugick" wrote:
> Dave wrote:
> > I'm trying to call a .exe as a Job Step that pulls some data and
> > sends out custom emails that will execute after a DTS package runs.
> > I tested the EXE from the command line and it works but I don't know
> > how to call it from with the Job Step.
> >
> > I have the step called ''Example of calling an EXE" and set an
> > Operating System Command (CmdExec) with:
> >
> > xp_cmdshell 'C:\MyApp\SendAlert.exe' , no_output
> >
> > When it runs, I check the Windows event and it says:
> >
> > SQL Server Scheduled Job 'Example of calling an EXE'
> > (0x0AE46A0A26688D48B29A3C71684CB284) - Status: Failed - Invoked on:
> > 2005-10-27 13:04:45 - Message: The job failed. The Job was invoked
> > by User sa. The last step to run was step 1 (Calling the EXE).
> >
> > I have no idea why it's failing when called as a job?
> xp_cmdshell is in the master database. You need to fully qualify the
> name if you are not in master:
> exec master..xp_cmdshell 'dir'
> Also, you need to make sure the SQL Server service account has access to
> the folder where you placed the EXE. If not, you need to grant the
> account access of move the file(s) to a folder where the account has
> access.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
custom emails that will execute after a DTS package runs. I tested the EXE
from the command line and it works but I don't know how to call it from with
the Job Step.
I have the step called ''Example of calling an EXE" and set an Operating
System Command (CmdExec) with:
xp_cmdshell 'C:\MyApp\SendAlert.exe' , no_output
When it runs, I check the Windows event and it says:
SQL Server Scheduled Job 'Example of calling an EXE'
(0x0AE46A0A26688D48B29A3C71684CB284) - Status: Failed - Invoked on:
2005-10-27 13:04:45 - Message: The job failed. The Job was invoked by User
sa. The last step to run was step 1 (Calling the EXE).
I have no idea why it's failing when called as a job?One other thing I found while executing the following in query analyzer:
xp_cmdshell 'C:\MyApp\SendAlert.exe'
returns:
"The system cannot find the path specified"
Thanks again.
"Dave" wrote:
> I'm trying to call a .exe as a Job Step that pulls some data and sends out
> custom emails that will execute after a DTS package runs. I tested the EXE
> from the command line and it works but I don't know how to call it from with
> the Job Step.
> I have the step called ''Example of calling an EXE" and set an Operating
> System Command (CmdExec) with:
> xp_cmdshell 'C:\MyApp\SendAlert.exe' , no_output
> When it runs, I check the Windows event and it says:
> SQL Server Scheduled Job 'Example of calling an EXE'
> (0x0AE46A0A26688D48B29A3C71684CB284) - Status: Failed - Invoked on:
> 2005-10-27 13:04:45 - Message: The job failed. The Job was invoked by User
> sa. The last step to run was step 1 (Calling the EXE).
> I have no idea why it's failing when called as a job?|||Dave wrote:
> I'm trying to call a .exe as a Job Step that pulls some data and
> sends out custom emails that will execute after a DTS package runs.
> I tested the EXE from the command line and it works but I don't know
> how to call it from with the Job Step.
> I have the step called ''Example of calling an EXE" and set an
> Operating System Command (CmdExec) with:
> xp_cmdshell 'C:\MyApp\SendAlert.exe' , no_output
> When it runs, I check the Windows event and it says:
> SQL Server Scheduled Job 'Example of calling an EXE'
> (0x0AE46A0A26688D48B29A3C71684CB284) - Status: Failed - Invoked on:
> 2005-10-27 13:04:45 - Message: The job failed. The Job was invoked
> by User sa. The last step to run was step 1 (Calling the EXE).
> I have no idea why it's failing when called as a job?
xp_cmdshell is in the master database. You need to fully qualify the
name if you are not in master:
exec master..xp_cmdshell 'dir'
Also, you need to make sure the SQL Server service account has access to
the folder where you placed the EXE. If not, you need to grant the
account access of move the file(s) to a folder where the account has
access.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Dave
You can also run cmd commands from SQL jobs In the same job you are running
DTS
--
Paul Zanbaka
DBA, Programmer Analyst
"David Gugick" wrote:
> Dave wrote:
> > I'm trying to call a .exe as a Job Step that pulls some data and
> > sends out custom emails that will execute after a DTS package runs.
> > I tested the EXE from the command line and it works but I don't know
> > how to call it from with the Job Step.
> >
> > I have the step called ''Example of calling an EXE" and set an
> > Operating System Command (CmdExec) with:
> >
> > xp_cmdshell 'C:\MyApp\SendAlert.exe' , no_output
> >
> > When it runs, I check the Windows event and it says:
> >
> > SQL Server Scheduled Job 'Example of calling an EXE'
> > (0x0AE46A0A26688D48B29A3C71684CB284) - Status: Failed - Invoked on:
> > 2005-10-27 13:04:45 - Message: The job failed. The Job was invoked
> > by User sa. The last step to run was step 1 (Calling the EXE).
> >
> > I have no idea why it's failing when called as a job?
> xp_cmdshell is in the master database. You need to fully qualify the
> name if you are not in master:
> exec master..xp_cmdshell 'dir'
> Also, you need to make sure the SQL Server service account has access to
> the folder where you placed the EXE. If not, you need to grant the
> account access of move the file(s) to a folder where the account has
> access.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
Calling a VB exe from a stored procedure
Can someone tell me a straightforward way to call a VB app (that accepts command line arguments) from a stored procedure.
I have got it to work by using xp_cmdshell, but in practice, the security constraints here prevent using this. Our DBAs don't want to set the proxy account required for a non-sysadmin user to eexecute xp_cmdshell.
I know that writing an extended SP invoking a C++ dll would be the cleanest solution. However I don't have the knowledge to do that.
Any ideas?
ThanksOriginally posted by mtnlvrs
Can someone tell me a straightforward way to call a VB app (that accepts command line arguments) from a stored procedure.
I have got it to work by using xp_cmdshell, but in practice, the security constraints here prevent using this. Our DBAs don't want to set the proxy account required for a non-sysadmin user to eexecute xp_cmdshell.
I know that writing an extended SP invoking a C++ dll would be the cleanest solution. However I don't have the knowledge to do that.
Any ideas?
Thanks
I think your opening post eliminated the only two ways to do this (and actually, there's only one, since XP_CMDSHELL is just a very simple extended proc DLL)
Actually, just straight C will do for the DLL. It's not as hard as you seem to think. On the other hand, you gotta be sure of that code, since you'll be running in the engines address space. Just be sure to clean up everything, check your buffer lengths, and initialize all your pointers and it will work just fine. But I'm fairly certain extended procs are the only way to 'step outside' of what TSQL is capable of, ie run your VB code, and retain control flow.
Now, if it's OK for your VB code to run asynchronously, you could always make it step in a SQL Agent Job, and kick it off in a stored proc by executing msdb..sp_start_job, but you won't be able to return anything and your stored proc won't wait on the job to finish. And you'll have concurrency issues, as only one copy of a SQL Agent job can run at a time.
I have got it to work by using xp_cmdshell, but in practice, the security constraints here prevent using this. Our DBAs don't want to set the proxy account required for a non-sysadmin user to eexecute xp_cmdshell.
I know that writing an extended SP invoking a C++ dll would be the cleanest solution. However I don't have the knowledge to do that.
Any ideas?
ThanksOriginally posted by mtnlvrs
Can someone tell me a straightforward way to call a VB app (that accepts command line arguments) from a stored procedure.
I have got it to work by using xp_cmdshell, but in practice, the security constraints here prevent using this. Our DBAs don't want to set the proxy account required for a non-sysadmin user to eexecute xp_cmdshell.
I know that writing an extended SP invoking a C++ dll would be the cleanest solution. However I don't have the knowledge to do that.
Any ideas?
Thanks
I think your opening post eliminated the only two ways to do this (and actually, there's only one, since XP_CMDSHELL is just a very simple extended proc DLL)
Actually, just straight C will do for the DLL. It's not as hard as you seem to think. On the other hand, you gotta be sure of that code, since you'll be running in the engines address space. Just be sure to clean up everything, check your buffer lengths, and initialize all your pointers and it will work just fine. But I'm fairly certain extended procs are the only way to 'step outside' of what TSQL is capable of, ie run your VB code, and retain control flow.
Now, if it's OK for your VB code to run asynchronously, you could always make it step in a SQL Agent Job, and kick it off in a stored proc by executing msdb..sp_start_job, but you won't be able to return anything and your stored proc won't wait on the job to finish. And you'll have concurrency issues, as only one copy of a SQL Agent job can run at a time.
Sunday, March 11, 2012
calling a exe . reading from a text file from a stored procedure
Hello,
Is it possiable to call a exe file from a sql stored procedure, and what
do i need to look into to do this.
also
Is it possiable to read a text file from a sql stored statement?Tdar,
You can use xp_cmdshell. Do not use this with a graphical application that
required end-user interaction
For the latter, try:
CREATE TABLE #T (TEXT VARCHAR(500))
INSERT INTO #T EXEC MASTER..XP_CMDSHELL 'TYPE C:\TEXTFILE.TXT'
SELECT * FROM #T
--DROP TABLE #T
HTH
Jerry
"Tdar" <Tdar@.noemail.nospam> wrote in message
news:e1bd0UEyFHA.2228@.TK2MSFTNGP11.phx.gbl...
> Hello,
> Is it possiable to call a exe file from a sql stored procedure, and
> what do i need to look into to do this.
> also
> Is it possiable to read a text file from a sql stored statement?
>
>
>|||Look into xp_cmdShell for calling exe's
For reading files use BCP or DTS
http://sqlservercode.blogspot.com/
"Tdar" wrote:
> Hello,
> Is it possiable to call a exe file from a sql stored procedure, and wh
at
> do i need to look into to do this.
> also
> Is it possiable to read a text file from a sql stored statement?
>
>
>|||1: Read in Books Online about xp_cmdshell. Need to be a console application,
not a Windowing app.
2. Sure. What do you want to do with the data that you read from the text fi
le? Import to a table?
Use BULK INSERT.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tdar" <Tdar@.noemail.nospam> wrote in message news:e1bd0UEyFHA.2228@.TK2MSFTNGP11.phx.gbl...
> Hello,
> Is it possiable to call a exe file from a sql stored procedure, and wha
t do i need to look into
> to do this.
> also
> Is it possiable to read a text file from a sql stored statement?
>
>
>|||The data will just be on the second line of the text file and semicolon
delimited, that data
Will just be recorded into a table. The Program is an old Dos app so I guess
that is as good as console
As you can get.
Thanks everyone this should help a lot.
David
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uHJlOhEyFHA.2212@.TK2MSFTNGP15.phx.gbl...
> 1: Read in Books Online about xp_cmdshell. Need to be a console
> application, not a Windowing app.
> 2. Sure. What do you want to do with the data that you read from the text
> file? Import to a table? Use BULK INSERT.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Tdar" <Tdar@.noemail.nospam> wrote in message
> news:e1bd0UEyFHA.2228@.TK2MSFTNGP11.phx.gbl...
>|||If you want to read into a table, check out BULK INSERT, BCP and DTS.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tdar" <Tdar@.noemail.nospam> wrote in message news:OMEuCGFyFHA.2960@.tk2msftngp13.phx.gbl...
> The data will just be on the second line of the text file and semicolon
> delimited, that data
> Will just be recorded into a table. The Program is an old Dos app so I gue
ss
> that is as good as console
> As you can get.
>
> Thanks everyone this should help a lot.
>
> David
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:uHJlOhEyFHA.2212@.TK2MSFTNGP15.phx.gbl...
>
Is it possiable to call a exe file from a sql stored procedure, and what
do i need to look into to do this.
also
Is it possiable to read a text file from a sql stored statement?Tdar,
You can use xp_cmdshell. Do not use this with a graphical application that
required end-user interaction
For the latter, try:
CREATE TABLE #T (TEXT VARCHAR(500))
INSERT INTO #T EXEC MASTER..XP_CMDSHELL 'TYPE C:\TEXTFILE.TXT'
SELECT * FROM #T
--DROP TABLE #T
HTH
Jerry
"Tdar" <Tdar@.noemail.nospam> wrote in message
news:e1bd0UEyFHA.2228@.TK2MSFTNGP11.phx.gbl...
> Hello,
> Is it possiable to call a exe file from a sql stored procedure, and
> what do i need to look into to do this.
> also
> Is it possiable to read a text file from a sql stored statement?
>
>
>|||Look into xp_cmdShell for calling exe's
For reading files use BCP or DTS
http://sqlservercode.blogspot.com/
"Tdar" wrote:
> Hello,
> Is it possiable to call a exe file from a sql stored procedure, and wh
at
> do i need to look into to do this.
> also
> Is it possiable to read a text file from a sql stored statement?
>
>
>|||1: Read in Books Online about xp_cmdshell. Need to be a console application,
not a Windowing app.
2. Sure. What do you want to do with the data that you read from the text fi
le? Import to a table?
Use BULK INSERT.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tdar" <Tdar@.noemail.nospam> wrote in message news:e1bd0UEyFHA.2228@.TK2MSFTNGP11.phx.gbl...
> Hello,
> Is it possiable to call a exe file from a sql stored procedure, and wha
t do i need to look into
> to do this.
> also
> Is it possiable to read a text file from a sql stored statement?
>
>
>|||The data will just be on the second line of the text file and semicolon
delimited, that data
Will just be recorded into a table. The Program is an old Dos app so I guess
that is as good as console
As you can get.
Thanks everyone this should help a lot.
David
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uHJlOhEyFHA.2212@.TK2MSFTNGP15.phx.gbl...
> 1: Read in Books Online about xp_cmdshell. Need to be a console
> application, not a Windowing app.
> 2. Sure. What do you want to do with the data that you read from the text
> file? Import to a table? Use BULK INSERT.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Tdar" <Tdar@.noemail.nospam> wrote in message
> news:e1bd0UEyFHA.2228@.TK2MSFTNGP11.phx.gbl...
>|||If you want to read into a table, check out BULK INSERT, BCP and DTS.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tdar" <Tdar@.noemail.nospam> wrote in message news:OMEuCGFyFHA.2960@.tk2msftngp13.phx.gbl...
> The data will just be on the second line of the text file and semicolon
> delimited, that data
> Will just be recorded into a table. The Program is an old Dos app so I gue
ss
> that is as good as console
> As you can get.
>
> Thanks everyone this should help a lot.
>
> David
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:uHJlOhEyFHA.2212@.TK2MSFTNGP15.phx.gbl...
>
Thursday, March 8, 2012
Call Web Service From Script Task
This summary is not available. Please
click here to view the post.
Wednesday, March 7, 2012
Call SSIS remotely to run exe task in server by C#
I would like to build a C# program to connect to a remote SQL2005 server.
Then call the server SSIS to run a .exe in the server (The .exe file already exist in the SQL2005 server)
I have refer to the following webpage http://www.codeproject.com/useritems/CallSSISFromCSharp.asp
I can connect to the SQL server by the "LoadFromSqlServer" method with SQL server login A/C.
However, the SSIS failed. I believe it should be due to the window authentication issue. During SSIS execution, the SQL server login A/C does not have the access right to run the .exe file
Could anyone suggest the solution?
Thanks.
If you are running the C# executable on your computer, it is not running the SSIS package on the server,it is executing it locally. So it would be running under your account, or it may be that the .exe on the server is not visible from your computer.
To actually run the package locally on the server, you'd need to create a web service, or use SQL Agent to run the package. This post from Michael Entin has more details: http://blogs.msdn.com/michen/archive/2007/03/22/running-ssis-package-programmatically.aspx
Call external program from a stored proc / trigger
What is the stored proc that can call an external exe, etc. from a stored
procedure, function, trigger, etc.?
I can not for the life of me remember what it is. I need to an action upon
row inserts and wanted to have a trigger run an external program.
thanks,master..xp_cmdshell
AMB
"aiKeith" wrote:
> What is the stored proc that can call an external exe, etc. from a stored
> procedure, function, trigger, etc.?
> I can not for the life of me remember what it is. I need to an action upo
n
> row inserts and wanted to have a trigger run an external program.
> thanks,
>|||Hi
You can call xp_cmdshell, but it is ill advised.
What happens if your trigger fires again before the application finishes?
If the external application fails, the error could rollback the DML done.
The locks aquired by the DML, remain in force until the SP or Trigger
completes. This can cuase concurrency problems.
Regards
Mike
"aiKeith" wrote:
> What is the stored proc that can call an external exe, etc. from a stored
> procedure, function, trigger, etc.?
> I can not for the life of me remember what it is. I need to an action upo
n
> row inserts and wanted to have a trigger run an external program.
> thanks,
>|||Thank you Alej...
I believe there is a specific procedure for calling external applications /
dll's, etc. I even had a sample that called a C++ applications dll, but i
cannot find it. I did not want to use the cmd shell.
Any ideas?
"Alejandro Mesa" wrote:
> master..xp_cmdshell
>
> AMB
> "aiKeith" wrote:
>|||Hi
It is considered a bad idea to run .EXE from a trigger. Look , when a
trigger gets fired it opens a new transaction and keeps it open till all
commands will be completed succesfuly. If SQL Server raises an error inside
the trigger all transactions should be rollbacked which may cause to
locks,blocking or even deadlocks. Do you really ,really need it to be ran
from the trigger?
"aiKeith" <aiKeith@.discussions.microsoft.com> wrote in message
news:A067E30C-E88E-40D2-AD5D-181FFB98B664@.microsoft.com...
> What is the stored proc that can call an external exe, etc. from a stored
> procedure, function, trigger, etc.?
> I can not for the life of me remember what it is. I need to an action
upon
> row inserts and wanted to have a trigger run an external program.
> thanks,
>|||sp_OA*
AMB
"aiKeith" wrote:
> Thank you Alej...
> I believe there is a specific procedure for calling external applications
/
> dll's, etc. I even had a sample that called a C++ applications dll, but i
> cannot find it. I did not want to use the cmd shell.
> Any ideas?
> "Alejandro Mesa" wrote:
>|||You probably want the sp_OA procs. Lookup sp_oacreate in Books Online.
As Mike indicated, this is a really bad idea in a trigger. One issue is
how to handle a transaction that rolls back. Since there is no built-in
functionality for that you would have to detect any error condition and
call your external process again to rollback its action. You therefore
gain little from doing it in a trigger (why not just call your code
once only, at the end).
On the other hand if you don't need this to be part of an atomic
transaction then there is no need to put it in a trigger at all. Best
bet is to create an external process, outside of SQL Server, that
regularly polls the table for changes.
David Portas
SQL Server MVP
--|||Thank you all for your great comments.
I am aware that calling code from a trigger is typically bad practice, and I
almost included that in the first post.
This is only for a temporary thing... the reason I want to do it in a
trigger is:
1.) Less resources... I dont know if its going to run today, next w
or
not for a month, therefore I dont want something constantly monitoring the d
b
(even if its once a day)
2.) I can easily disable/remove the trigger.
Thanks again, you guys inspire me to answer posts.
"Uri Dimant" wrote:
> Hi
> It is considered a bad idea to run .EXE from a trigger. Look , when a
> trigger gets fired it opens a new transaction and keeps it open till all
> commands will be completed succesfuly. If SQL Server raises an error insid
e
> the trigger all transactions should be rollbacked which may cause to
> locks,blocking or even deadlocks. Do you really ,really need it to be ran
> from the trigger?
>
>
> "aiKeith" <aiKeith@.discussions.microsoft.com> wrote in message
> news:A067E30C-E88E-40D2-AD5D-181FFB98B664@.microsoft.com...
> upon
>
>
procedure, function, trigger, etc.?
I can not for the life of me remember what it is. I need to an action upon
row inserts and wanted to have a trigger run an external program.
thanks,master..xp_cmdshell
AMB
"aiKeith" wrote:
> What is the stored proc that can call an external exe, etc. from a stored
> procedure, function, trigger, etc.?
> I can not for the life of me remember what it is. I need to an action upo
n
> row inserts and wanted to have a trigger run an external program.
> thanks,
>|||Hi
You can call xp_cmdshell, but it is ill advised.
What happens if your trigger fires again before the application finishes?
If the external application fails, the error could rollback the DML done.
The locks aquired by the DML, remain in force until the SP or Trigger
completes. This can cuase concurrency problems.
Regards
Mike
"aiKeith" wrote:
> What is the stored proc that can call an external exe, etc. from a stored
> procedure, function, trigger, etc.?
> I can not for the life of me remember what it is. I need to an action upo
n
> row inserts and wanted to have a trigger run an external program.
> thanks,
>|||Thank you Alej...
I believe there is a specific procedure for calling external applications /
dll's, etc. I even had a sample that called a C++ applications dll, but i
cannot find it. I did not want to use the cmd shell.
Any ideas?
"Alejandro Mesa" wrote:
> master..xp_cmdshell
>
> AMB
> "aiKeith" wrote:
>|||Hi
It is considered a bad idea to run .EXE from a trigger. Look , when a
trigger gets fired it opens a new transaction and keeps it open till all
commands will be completed succesfuly. If SQL Server raises an error inside
the trigger all transactions should be rollbacked which may cause to
locks,blocking or even deadlocks. Do you really ,really need it to be ran
from the trigger?
"aiKeith" <aiKeith@.discussions.microsoft.com> wrote in message
news:A067E30C-E88E-40D2-AD5D-181FFB98B664@.microsoft.com...
> What is the stored proc that can call an external exe, etc. from a stored
> procedure, function, trigger, etc.?
> I can not for the life of me remember what it is. I need to an action
upon
> row inserts and wanted to have a trigger run an external program.
> thanks,
>|||sp_OA*
AMB
"aiKeith" wrote:
> Thank you Alej...
> I believe there is a specific procedure for calling external applications
/
> dll's, etc. I even had a sample that called a C++ applications dll, but i
> cannot find it. I did not want to use the cmd shell.
> Any ideas?
> "Alejandro Mesa" wrote:
>|||You probably want the sp_OA procs. Lookup sp_oacreate in Books Online.
As Mike indicated, this is a really bad idea in a trigger. One issue is
how to handle a transaction that rolls back. Since there is no built-in
functionality for that you would have to detect any error condition and
call your external process again to rollback its action. You therefore
gain little from doing it in a trigger (why not just call your code
once only, at the end).
On the other hand if you don't need this to be part of an atomic
transaction then there is no need to put it in a trigger at all. Best
bet is to create an external process, outside of SQL Server, that
regularly polls the table for changes.
David Portas
SQL Server MVP
--|||Thank you all for your great comments.
I am aware that calling code from a trigger is typically bad practice, and I
almost included that in the first post.
This is only for a temporary thing... the reason I want to do it in a
trigger is:
1.) Less resources... I dont know if its going to run today, next w
ornot for a month, therefore I dont want something constantly monitoring the d
b
(even if its once a day)
2.) I can easily disable/remove the trigger.
Thanks again, you guys inspire me to answer posts.
"Uri Dimant" wrote:
> Hi
> It is considered a bad idea to run .EXE from a trigger. Look , when a
> trigger gets fired it opens a new transaction and keeps it open till all
> commands will be completed succesfuly. If SQL Server raises an error insid
e
> the trigger all transactions should be rollbacked which may cause to
> locks,blocking or even deadlocks. Do you really ,really need it to be ran
> from the trigger?
>
>
> "aiKeith" <aiKeith@.discussions.microsoft.com> wrote in message
> news:A067E30C-E88E-40D2-AD5D-181FFB98B664@.microsoft.com...
> upon
>
>
Call an EXE from an SSIS package?
Hi. Is it possible to call an executable that resides on another server from an SSIS package? If that's not possible, is it possible to call an EXE that resides on the SQL Server box itself? Thank you 
arc_dev wrote:
Hi. Is it possible to call an executable that resides on another server from an SSIS package? If that's not possible, is it possible to call an EXE that resides on the SQL Server box itself? Thank you
Look at the Execute Process task in the control flow for locally located .exe's.
|||Thanks, Phil. That's what I needed.Saturday, February 25, 2012
call a program (activex exe, dll or something) from a script
Is it possible to do this? I am evaluating a bunch of possible solutions to
a problem. Someone came up with this idea, but could not remember for
certain if it possible.
Thanks.1. Lookup the sp_OAxxxxxx routines in SQL Online Books and it will show you
how to create COM objects and call method and set properties
2. Lookup xp_cmdshell on how to launch processes
3. Write an extended stored proc to have tighter control over what is
happening (inside your proc you can call CreateProcess() API and do many
other things)
All three of these options have security/stability implications. Let me
know if you need more details on a specific option.
Mike
"Stephanie" <IwishICould@.NoWay.com> wrote in message
news:O8qomUBTFHA.2916@.TK2MSFTNGP15.phx.gbl...
> Is it possible to do this? I am evaluating a bunch of possible solutions
to
> a problem. Someone came up with this idea, but could not remember for
> certain if it possible.
> Thanks.
>
a problem. Someone came up with this idea, but could not remember for
certain if it possible.
Thanks.1. Lookup the sp_OAxxxxxx routines in SQL Online Books and it will show you
how to create COM objects and call method and set properties
2. Lookup xp_cmdshell on how to launch processes
3. Write an extended stored proc to have tighter control over what is
happening (inside your proc you can call CreateProcess() API and do many
other things)
All three of these options have security/stability implications. Let me
know if you need more details on a specific option.
Mike
"Stephanie" <IwishICould@.NoWay.com> wrote in message
news:O8qomUBTFHA.2916@.TK2MSFTNGP15.phx.gbl...
> Is it possible to do this? I am evaluating a bunch of possible solutions
to
> a problem. Someone came up with this idea, but could not remember for
> certain if it possible.
> Thanks.
>
Call a DLL or EXE file from SQL Trigger
I need some help calling a DLL or EXE from a SQL Trigger. I have the trigger set up, except I have no clue how to call a DLL or EXE or if it is even possible. Here is what I have for the Trigger so far:
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
CREATE TRIGGER CallProgIfParentID
ON Toc
FOR INSERT
AS
IF ((select ins.[ParentID] FROM inserted ins) = '57660')
-- I want to be able to programmatically change '57660' from an ASPX page
EXEC
-- This is where the call function goes
GO
Another question I have is can a trigger be created programmatically using ASPX pages written in VB.NET in VS2003?
Thank you in advance
I have to warn you first: although calling external exes is possible, it is not recommended as there are at least a couple obvious drawbacks:
1. You would possibly lose data integrity because those external processes are NOT bound to the SQL transaction that triggers run in. For example, if the INSERT statement that fires the insert trigger is rolled back, regular trigger actions would be rolled back too, but those external processes would not. Similarly, triggers might not be able to detect errors that happen to those external processes and it ends up with the external exe failed but the trigger (as well as the firing statement) succeeded.
2. These processes will run outside of SQL Server, so you would lose total control of them. E.g. they might come back and compete with SQL Server for resources like CPU and memory.
I am wondering what kind of scenarios you have, but there got to be a better way to do it :)
Anyway, if you still decide to go with this route, you could call external exes from a trigger by:
- xp_cmdshell '<some exe>'
- For a *unsafe* CLR trigger, you could practically do anything, including calling external processes (e.g. Process class)
For you other question, yes, you can create triggers programmatically from wherever you can connect to the server.
|||set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER SendMailIfParentID
ON Toc
AFTER INSERT
AS
IF ((select ins.[ParentID] FROM inserted ins) = '57660')
EXEC master ..xp_cmdshell 'C:\Documents and Settings\michael\My Documents\Visual Studio Projects\Test\bin\Test.exe 57660'
Thats what I have for my trigger now (The 57660 is a test ID number and isn't of much relavence), either the trigger isnt being called or the EXE isn't being called. I can't figure out why it's not working.
|||I figured out what wasn't working and fixed it. Now I can't get a variable to append to the end of the command line. It keeps telling me the + isn't valid. Does anyone have any ideas?
ALTER TRIGGER SendMailIfParentID
ON Toc
AFTER INSERT
AS
IF ((select ins.[ParentID] FROM inserted ins) = '57750')
DECLARE @.MyTocId varchar(12)
SELECT @.MyTocId = (SELECT TocId FROM inserted)
EXEC master ..xp_cmdshell '"C:\Documents and Settings\michael\My Documents\Visual Studio Projects\Test\bin\Test.exe" ' + @.MyTocId
GO
|||Hi,for debugging and better handling purposes, I would suggest first putting evverything in a varaible and executing this afterwards:
IF ((select ins.[ParentID] FROM inserted ins) = '57750')
DECLARE @.MyTocId varchar(12)
SELECT @.MyTocId = '"C:\Documents and Settings\michael\My Documents\Visual Studio Projects\Test\bin\Test.exe" ' + TocId FROM inserted
EXEC master ..xp_cmdshell @.command = @.MyTocId
Warning: Triggers are fired per statement not per row, you will in addition make sure that your trigger is able to handle multiple rows affected in a trigger. In further addition you will have to make sure that the command is not executed as no row is affected as the trigger is fired (as already said) on a statement basis (even if the affected rowcount is 0).
Jens K. Suessmeyer.
http://www.sqlserver2005.de
Subscribe to:
Posts (Atom)