Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
I wouldn’t do this anymore, but I ran across a post where someone couldn’t use PowerShell in their organization. A poor decision, IMHO, for the Microsoft platform, but it is a restriction. In this case, the user wanted to get a batch file to run a SQLCMD script. This post shows how.
Two Files
Let’s suppose I have two files in a folder. In this case, I have a version.sql file that contains this:
select @@version
The other is my batch file, which I’ll name runsql.cmd. In this file, I’ll do a few things. First, suppress the code with this:
@@echo off
That’s just a good habit, though you might leave this out until things are working. Now, I will use a loop to get a list of files with an extension. I use the FOR loop in this way:
for %%x in (*.sql) do (
This gets a list of all .sql files in the current folder. For each one, we will process all statements inside the parenthesis. The open is on the line above, the close will be below.
The next lines are my sqlcmd call and the various items I need. In my case, I’ll get the instance name as a parameter and use trusted authentication.
sqlcmd -S "%1" -E -i %%x )
I could use other parameters (%2, %3, etc.) to get a user and password if I wanted to. Instead, I’ll get the instance as a parameter, and then pass the filename in to sqlcmd with the –i parameter.
When I run this, with my single .sql file, I see this:
Easy to do, and I could add other .sql files in here if I wanted them to run.
SQLNewBlogger
This was a quick post to write in answer to someone asking a question. I knew about the %1, %2, and searched to find a quick SO post on getting filenames into a variable. It actually took about 5 minutes to research and test (and post) and then about 10 minutes to write this up.
You could do this, showing some knowledge of learning and creating a solution. For extra credit, how can I capture output of this?