Solved

How to run bunch of sql files on particular database

Posted on 2013-12-16
6
576 Views
Last Modified: 2013-12-17
I would like to run bunch of sql files on particular database using either batch file or C# program. How to achive this? I tried below batch file code but no luck.  The folder could be anywhere in my  computer. Please do assist.
for %%G in (*.sql) do sqlcmd /S servername /d databaseName -E -i"%%G"
pause

Open in new window

and

c:\>for %f in (*.sql) do sqlcmd /S <servername> /d <dbname> /E /i "%f"

Open in new window

0
Comment
Question by:Easwaran Paramasivam
[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
6 Comments
 
LVL 11

Assisted Solution

by:John_Vidmar
John_Vidmar earned 500 total points
ID: 39723873
@echo off
cls
rem Usage:  ScriptName.cmd server database "SQL folder"

rem validate passed-parameters
if [%1]==[] (
	echo missing server
	exit -1
)
if [%2]==[] (
	echo missing database
	exit -1
)
if [%3]==[] (
	echo missing folder
	exit -1
)

rem change to SQL folder
cd %3

rem process SQL files
for %%G in (*.sql) do (
	echo sqlcmd /S %1 /d %2 -E -i"%%G"
	sqlcmd /S %1 /d %2 -E -i"%%G"
	if ERRORLEVEL 1 exit -1
)

Open in new window

0
 
LVL 16

Author Comment

by:Easwaran Paramasivam
ID: 39723910
Thanks. Username password to the DB server are not required?
0
 
LVL 11

Expert Comment

by:John_Vidmar
ID: 39723921
In your example you used -E, which is windows-authentication.  If you have username/password then you could provide that, here is the syntax:
usage: Sqlcmd            [-U login id]          [-P password]
  [-S server]            [-H hostname]          [-E trusted connection]
  [-N Encrypt Connection][-C Trust Server Certificate]
  [-d use database name] [-l login timeout]     [-t query timeout]
  [-h headers]           [-s colseparator]      [-w screen width]
  [-a packetsize]        [-e echo input]        [-I Enable Quoted Identifiers]
  [-c cmdend]            [-L[c] list servers[clean output]]
  [-q "cmdline query"]   [-Q "cmdline query" and exit]
  [-m errorlevel]        [-V severitylevel]     [-W remove trailing spaces]
  [-u unicode output]    [-r[0|1] msgs to stderr]
  [-i inputfile]         [-o outputfile]        [-z new password]
  [-f <codepage> | i:<codepage>[,o:<codepage>]] [-Z new password and exit]
  [-k[1|2] remove[replace] control characters]
  [-y variable length type display width]
  [-Y fixed length type display width]
  [-p[1] print statistics[colon format]]
  [-R use client regional setting]
  [-b On error batch abort]
  [-v var = "value"...]  [-A dedicated admin connection]
  [-X[1] disable commands, startup script, enviroment variables [and exit]]
  [-x disable variable substitution]
  [-? show syntax summary]

Open in new window

0
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
LVL 16

Author Comment

by:Easwaran Paramasivam
ID: 39724180
Thanks.  To execute in current folder I used below query. It works. How to include the subfolder of current folder here? Could you please suggest?

REM  - Get curernt directory

Pushd "%~dp0"

REM - Get all sql files and run specified DB

for %%G in (*.sql) do (
	sqlcmd /S server /U sa  /P pa@@w /d test -i"%%G"

	if ERRORLEVEL 1 exit -1
) 

pause

Open in new window

0
 
LVL 11

Accepted Solution

by:
John_Vidmar earned 500 total points
ID: 39724233
If you want to hard-code everything in the command-script instead of using passed-parameters then that's okay.  The for-loop may be enhanced to use relative/absolute folder notation:
for %%G in (blah\*.sql)

for %%G in (c:\temp\blah\*.sql)
0
 
LVL 16

Author Closing Comment

by:Easwaran Paramasivam
ID: 39725751
Excellent!
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

739 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