10-26-12, 13:14 #1Registered User
- Join Date
- Oct 2012
I created stored procedure to create trigger file in a perticular directory using
I am calling the procedure from windows batch script as follws
@@set osq200=osql /a 4096 /b /E /e /d %dbn% /m-1 /r 0 /S %dbs% /Q "exec SP_Create_TriggerFile %2,%1 "
@%osq200% >>%3rec.txt 2>%3rec_err.txt
@if %dberr% GTR 0 goto createDATriggerFileErr >>%3rec.txt
If the directory doesn't exist, its throwing error "The system cannot find the path specified" , but the %errorlevel% still showing as 0..
10-26-12, 14:39 #2Resident Curmudgeon
Provided Answers: 54
- Join Date
- Feb 2004
- In front of the computer
This was a relatively common problem at one client, but it depends on a specific build of the OS and a range of builds of OSQL. It does not occur with SQLCMD.EXE, so the easy fix would be to use SQLCMD.EXE instead of OSQL.EXE but that sidesteps a much larger problem.
Using xp_cmdshell is terribly insecure, which is why Microsoft has all but removed xp_cmdshell from the SQL Server product. Calling xp_cmdshell from within a trigger is a performance nightmare waiting for its next victim. You seem to be playing will many tools that can and will hurt you badly!
You may or may not realize it, but you are playing in very dangerous territory. I would make finding another way to do whatever you need to do a very high priority!
-PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.