Run SQL Script from a a Batch File

Question

I'm trying to schedulling a SQL Script by Windows Scheduler:

START 
"C:\Program Files\Microsoft SQL Server\100\DTS\Binn\dtexec.exe" /FILE "C:\Users\tstjs\TESTESTESTES.sql" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF  /REPORTING EWCDI
PAUSE

However, when I run the batch file it giving the error below:

...sql" due to error 0xC00CE556 "Invalid at the top level of the document. Line 1, Column 1". This happens when loading a package and the file cannot be opened o r loaded correctly into an XML document. This can be the result of either provid ing an incorrect file name to the LoadPackage method or the XML file specified h aving an incorrect format. End Error

How can I solve that problem?

Thanks!

Answer 1

I don't think dtexec will execute a script file only SSIS packages.

---

Andy Tauber
Data Architect
The Vancouver Clinic
Website | LinkedIn

This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

Answer 2

There exists any solution to execute a SCRIPT SQL daily not using SSIS and SQL Agent neither?

Thanks for your response!

Answer 3

The issue is your attempt to use dtexec to execute a tsql script.  dtexec does not do that.  sqlcmd is used for that purpose. In the future, I suggest you try to develop your scripting solution using the command line first - get it working - and then attempt to schedule it. You should also make some effort to selecting the most appropriate forum - it does not appear that your issue has any direct relationship to XML.

Answer 4

Hi SaQvl,

As Scott metioned, you shall use sqlcmd to execute a tsql script. You can reference the below link

Use the sqlcmd Utility

If you have any question, feel free to let me know.

---

Eric Zhang
TechNet Community Support

Answer 5

Below is an example of the task scheduler settings to execute the script via SQLCMD using Windows authentication.  Under the security settings, specify a Windows login that has the needed SQL permissions.

Program: SQLCMD.EXE

Arguments: -i"C:\Users\tstjs\TESTESTESTES.sql" -S YourServer -dYourDatabase -E

---

Dan Guzman, SQL Server MVP, http://www.dbdelta.com

Answer 6

Use the batch file to call a stored procedure in your database.

Example:

sqlcmd -Q "exec storedProcName" -S YourServer -d YourDatabase -o C:\yourOutput.txt

---

Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.