Solved

Running SQL scripts from a batch file

Posted on 2008-06-10
8
9,056 Views
Last Modified: 2012-06-27
New to SQL Server 2005.  I'm wanting to batch upload data from a remote system (VMS) into a SQL Server 2005 database.  I've scripted out into files, the commands I need to create the table(s), insert the data, and then to drop the tables.  I'm able to run the commands manually, can anyone share with me information on how to put these commands into a .bat file that I can run via the scheduler.

Also...is there a wait to script out the commands needed to create the Full-Text Indexes I need for these tables.  I didn't see how to do this in the SQL Server Management Studio.
0
Comment
Question by:ckroh
8 Comments
 
LVL 3

Expert Comment

by:bandriese
ID: 21753467
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

Open in new window

0
 
LVL 18

Expert Comment

by:mdougan
ID: 21753797
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.
0
 
LVL 8

Expert Comment

by:srnar
ID: 21753881
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

Open in new window

0
 
LVL 3

Expert Comment

by:bandriese
ID: 21754387
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.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:ckroh
ID: 21833353
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.  
0
 
LVL 3

Accepted Solution

by:
bandriese earned 125 total points
ID: 21833889
If you get into the BI designer and create a new Integration Services project. I think you'll figure out what you need to do. In your case if you have most of the SQL already. You can add several execute SQL objects to your project to get the job done. You can then run the package but double clicking or run it from a command line, a Agent Job, etc. The eLearning course will also tell you what you need to get started.

As for the command line method. See the sample below. BOL (Books On-Line) will also have a more detailed description of how to use sqlcmd.

In regards tot he Full-Text Index. 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.
A string of these would be used in a batch file. -E says to use a trusted connection. -e is echo. -i says use this file for the commands. 
 

sqlcmd -E -e -i something.sql
 

Or you could do it this way with a SQL Account.
 

SET USER=sa

SET PASSWORD=sa
 

osql -e -U %USER% -P %PASSWORD% -i sgmsdb.sql

Open in new window

0
 

Author Comment

by:ckroh
ID: 21957642
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?

0
 

Author Comment

by:ckroh
ID: 22010221
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?
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now