Tag: administration

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?

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

While testing a script recently, I needed to set a few backup folders for my instance. This was a striped backup, and using one folder wouldn’t make sense. In modern OSes, we can’t just create a folder and expect that our processes can read it. We need to explicitly set permissions.

It’s fairly easy for SQL Server, but since it wasn’t obvious, I decided to take a minute and document this.

If I create a folder, say c:\sqlbackup, I see this kind of thing in properties on my Window 10 machine.

If I click “Edit”, I get a similar view.

Now, my SQL Server process can’t access this. If I try to restore a backup from here, I’ll get a permissions error.

That’s fine. In the Permissions, I can click Add and I’ll get this dialog.

From here, I can enter “NT Service\MSSQLServer” or “NT ServiceMSSQL$SQL2017” for a named instance. My named instance is SQL2017.  Note the space in “NT Service”

If I click the “Check Names”, this will resolve for the built in service account.

Then I can click OK and set the appropriate permissions.

If your service account is something not built in, it’s usually easy to find and add, but for build in accounts, you need the “NT Service”.

SQLNewBlogger

You can take a simple thing here that you needed to solve and write about it. This took my about 5 minutes to solve, playing with different names, and then about 5 minutes to write.

Showcase your knowledge today.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I went to check a network protocol setting for SQL Server the other day on my (newish laptop) and was disappointed.

This is Windows 10 and on this machine, I’d installed SQL Server 2014, 2016, and 2017. I thought that at least SQL Server 2014 had the SQL Server Configuration Manager installed, but it appears not. I know that this has been a tool that sometimes gets hidden in recent versions, but I was sure I’d seen it here.

Either I’m wrong or Windows 10 has changed.

In any case, the Computer Management MMC plugin has it. You can run this in a couple ways. First, hit the Start menu and type “Computer Man”. You’ll get something like this and can run this:

The other choice is to his Win+R (run) and type “compmgmt.msc”. Both will get you here:

If you now expand the Services and Applications, you’ll see the SQL Server Configuration Manager and the various items underneath it. For me, the top one (most recent?) was the SQL Server 2017 version. The others were below as other snap-ins.

The good thing about this is I can also manage local users and see the local logs, things I sometimes need when configurating SQL Server.

SQLNewBlogger

An easy post that solves a common problem, and shows I know some tips and tricks. How would you rewrite this post? You could show this knowledge with a quick 10 minutes of your time.