Running SQL scripts from a batch file

From the command line, SQL Server provides a utilitiy called OSQL. Using this utility, you'll need to specify the nessasary login information or use a trusted connection depending on how it's going to run. See osql /? for more switches. The following example expects you have a SQL Account Username and Password.  Your file containing your scripts should be in the same format that you might run in Emterprise Manager with SQL statments followed by the ";" character.

C:\<path>\osql -e -U <username> -P <password> -S <server>\<instance> -i <full path to file containing SQL Commands>

As to your second question regarding indexes. Yes, I think so, Right-Click, Script Index as Create To... but you'll also need to create a seprate script to enable full-text indexing such as the following:

 
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [Table].[dbo].[sp_fulltext_database] @action = 'enable'
end

Select allOpen in new window

Under earlier versions of SQL Server you had the option of creating something called a DTS (data transformation services) Package.  These packages can have one or more job steps, and you can define what order and what dependencies there are between steps.   You schedule the job (nightly, weekly, monthly etc), and you can get it to e-mail you if the job does not complete successfully (or if it does).

Under SQL Server 2005, they call this SQL Server Integration Services (SSIS).  I haven't used it, but I'll bet it works much the same way.

Better using sqlcmd - new utility in 2005 - no problems with unicode scripts. I'm using script bellow that is reading all SQL files in current directory and executes it against you 2005 SQL server.

run this bat with 2 arguments: sqlServerName sqlAccountPassword

@echo off
 
rem Test number of command line arguments
if "%~2"=="" goto invalidParams
 
rem -----------
rem Deploy each file *.sql in current dir to mssql2005   
for %%i in (*.sql) do sqlcmd -SyourSqlServerName -d%~1 -UyourUserName -P%~2 -i %%i
 
goto end
 
rem -----------
:invalidParams
echo Syntax: deployDDL dbname password (ServerName and UserName is hardcoded)
 
rem ----------- 
:end

Select allOpen in new window

Ah, yes. My mistake. SQL 2005 uses sqlcmd, not osql, but the concept is the same. Mdougan also makes a good point. This is the perfect case for uses SSIS. It wouldn't be very difficult to generate a package that executed whatever SQL you needed to run. You could also then handle any erros much better and perhaps send you an email when it's finished if you wanted. It can also be triggered from the command line usin DTSExec. If you're not farmiliar with DTS or SSIS, Microsoft has a good Intro -level on-line elearning class on the subject:

Course 2943: Updating Your Data Integration Skills to Microsoft® SQL Server" 2005 Integration Services
https://www.microsoftelearning.com/eLearning/courseDetail.aspx?courseId=58900

Note: This is a free six-hour course with lab, but you'll need a login before you can get to the course content.

Read over the chapter on SISS in my book (Microsoft's SQL Server 2005, Administrator's Pocket Consultant) and althought it discusses that it can do, it is lacking in details.

Using the sqlcmd sounds more like what I was hoping for.  I was hoping to create a .bat file that I could run via the scheduler on the server.  This.bat file would:
- ftp the files to the server (from a remote location),
- run the sql script I created to drop my table
- run the sql script I created to create my table
- run the sql script I created that does a bulk insert of the ftp'd file into the table

I still don't see how to create the script to create the full-text index on the fields I need in the table.  I do not see the options you say about scripting a a full-text index, bandriese, when I right click on the table.  

I created a .bat file that has a series of the:  sqlcmd -E -e -i something.sql
I have it running the drop, create, insert, and create full-text index .sql command files I've created and it appears to work.

This appears that once I take care of getting the files from the remote system to thise server that a .bat file running these commands will do the trick for me.  I would like to learn more about Integration Service projects and will do some reasearch on them to see how they might better serve my needs, but it looks like this sqlcmd info is what I need to get me going with this project.   Any caveats?

One part of original question is still an open issue for me.  I have a full-text index created for a table.  I want to script the creating of that full-text index and I am not sure how to do that.


It was suggested by bandriese that I do the following:
"In SSMS drill down to the full-text index you have currently (which is under the table) Database --> Tables --> Yourtable --> Indexes --> Your_Full_Text_Index. Then right click on the index itself and select Script Index As... CREATE TO. "

However when I go to indexes for my table, the only index that shows up is my clustered main key index, not the full-text index I created by right clicking on my table and then clicking on the full-text index selection.  Any thoguhts on how to script this full-text index?