So how do I enable SQLCMD Mode?

Well, in SSMS.exe, you click on 'Query' then 'SQL CMD Mode'. Simples!

But wait! There's a catch if your script has variables, or os prompts:

Because you are not starting SQLCMD from the command line, there are some limitations when running Query Editor in SQLCMD Mode. You cannot pass in command-line parameters such as variables, and, because the Query Editor does not have the ability to respond to operating system prompts, you must be careful not to execute interactive statements.

What then?

Open up the Command Prompt, and load your script in thusly:

sqlcmd -S servername -U username -P password -i C:\Folder\Script.txt

And then it'll all work nicely, assuming that the script itself is sensible. For more parameters for the SQLCMD utility, see here.

SQL Configuration Manager Suddenly Unavailable

Last night, I installed SP1 onto a SQL 2008 Cluster. This morning, looking at another issue, I decided to mosey into the Configuration Manager, in order to double check which Ports SQL was operating on.

Imagine my horror when I got the following error, regardless of the account I used to try and open the tool:

"Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 servers or above with SQL Server Configuration Manager. Invalid class [0x80041010]"

Googling around suggested several solutions involving changing folder permissions, and also running something called mofcomp. This in particular seemed relevant to my situation, where an installation had recently been run on the server. It would seem that, occasionally, the .mof files get corrupted during an installation. They're Managed Object Format files, and used for storing system configuration information.

They can be found in the Shared folder for the 32 bit components of your SQL Server, for SQL 2005 and above. Thus, on a 64 bit cluster, they were here:

C:\Program Files (x86)\Microsoft SQL Server\100\Shared

And running this command in cmd fixed the problem. Phew.

C:\Program Files (x86)\Microsoft SQL Server\100\Shared>mofcomp "C:\Program Files (x86)\Microsoft SQL Server\10\Shared\sqlmgmproviderxpsp2up.mof"

OSQL -E

Sometimes, you get a black-box installation by a third party supplier: and, when you politely enquire as to whether you can install SSMS on the server so that you can pull out pertinent information for your SQL Estate Audit (because, naturally, the installation on the server is such that you cannot connect remotely: you just know that the server is there), the answer is no. Grrr.

But not the end of the world. Via the command prompt, we can open a connection to that instance of SQL, and run whatever queries we fancy with a local administrator's account (because, let's face it, if they haven't installed SSMS, and they're a third party supplier, the likelihood is that they haven't disabled the BuiltIn\Administrators group....).

Start off by checking the Services on the Server, to get an indication of what you've got: also check SQL Server Configuration. This will let you know how many instances of SQL are on the server, and whether they have a name other than the default servername. On the server I'm looking at, I've got two instances of SQL Express. I can connect to one by typing the following in the command prompt:

OSQL -E

For the second, I need to be a bit more specific, and type

OSQL -E -S ServerName\InstanceName

Once I've got OSQL running, I can query merrily. Every T-SQL statement that I can run in SSMS or Query Analyzer can be run in OSQL. I can execute stored procedures. I can change databases. I just need to remember that instead of hitting 'F5' to run my statement, I need to end my statement with 'GO', and press enter. It's worthwhile not running 'SELECT *', since the formatting leaves something to be desired, and it's a bit tricky....

So, what might my useful statements be? I've used a selection of the following today

To list my databases
SELECT name
FROM sys.databases
GO

To check authentication mode

SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
WHEN 1 THEN 'Windows Authentication'
WHEN 0 THEN 'Windows and SQL Server Authentication'
ELSE 'Unknown'
END
GO

To check recovery mode

SELECT recovery_model_desc
FROM sys.databases
GO

To check collation

SELECT name, collation_name
FROM sys.databases
GO

To check database owner

SELECT name, SUSER_SNAME(owner_sid)FROM sys.databases
GO

To check SQL Version, Windows Version and SQL Service Pack

SELECT @@VERSION
GO
SELECT ServerProperty('ProductLevel')
GO

To check database size

USE dbname
GO
EXEC sp_spaceused
GO

It's pretty easy to find out what you need by searching for the criteria, with an additional search of 'T-SQL' slapped at the front of the string. More additions will be made here as I come to need them, however, this got me what I wanted for my spreadsheet....

OSQL sort of isn't...

'OSQL' is not recognized as an internal or external command, operable
program or batch file.

Oh. But it's where it should be! C:\Program Files\Microsoft SQL Server\80\Tools\Binn (not my installation).

Within CMD, you can type 'set path' to check the variables in the default path. If the actual location of osql.exe isn't in there (or, rather, its parent folder - and it should be there, because SQL is supposed to install it!)

Follow the instructions here.

Sorted. And so quickly.