There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.
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.
-
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.
Active 1 day ago
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
Active 1 day ago
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?
Active 1 day ago
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
Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes. We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses
Course of the Month4 days, 18 hours left to enroll
580 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.