We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Course Of The Month
3 days, 2 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Running SQL scripts from a batch file
Posted on 2008-06-10
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.
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.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
3
-
3
- +1
8 Comments
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:
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
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.
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
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
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.
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.
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.
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.
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
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?
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?
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?
Featured Post
Everything you need to know about backup and disaster recovery with AWS, for FREE!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses
Course of the Month3 days, 2 hours left to enroll
695 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.