April 14, 2014 at 10:22 am
I'm attempting to run a script via management studio.
Created a new job. Under Job Step Properties, Type was left at "Transact-SQL script (T-SQL)". Database points to the appropriate one. Under command the following syntax is used:
sqlcmd -S NCSQL2K8DEV01\MSSQLSERVER –i F:\test\actual.sql -o C:\test\actual.txt
Under tools/options/query Execution/SQL Server/General,checkboxx "by default, open new queries in SQLCMD mode" is selected. The SQL script is a simple select of the top 10 rows.
Running the job fails, job history does not provide much guidance, indicating "Incorrect syntax near 'S'. Running the actual script replacing the above syntax succeeds. I've googled this for quite a while.
Any explanation as to what I am doing wrong? Comments / urls are appreciated. Thanks.
April 14, 2014 at 1:58 pm
Perhaps you should choose "Operating System" for the Type as sqlcmd is a command line utility?
--Vadim R.
April 16, 2014 at 1:47 pm
It took some review but got it to work. Your suggestion about modifying type to Operating System did help. It was also required modifying the sqlcmd input (final version 'for now'):
sqlcmd -i E:\test\query.sql -o E:\test\queryout.txt
This was it. The debug effort started with a command line prompt. Starting my testing here, believing if I can't run it here, SSMS won't work as well. Used a simple SQL script which included the USE database along with a select clause. Keep it simple.
Once that worked (still in command prompt), then used the above syntax with the addition of a "-d databasename" syntax. This worked as well. I then went to SSMS and tried it - this promptly failed (type was T-SQL). I realized the USE database was in the SQL script so "-d database" seemed extraneous. Removed it, re-ran and it still failed. Then changed type from T-SQL to Operating System and it worked.
Thank you for your help...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply