SQLTeam.com | Weblogs | Forums

SQL Query Outside of Management Studio


#1

I need to run a Query outside of management studio if possible. I need to change the state of my access controllers after the servers restart which involves changing a value from 0 then back to 1 in my Panel Coulmn. I am trying to allow this for people who do not have access to SQL and create a simple one click method of changing that info.


#2

You could use SQLCMD. This can be run from a CMD prompt - e.g. a Batch File or similar.

Or you could use an Application Language and an ADO (say, other flavours available ... e.g. ODBC) database connection.

Or you could do it from something like a Spreadsheet, using a Database Connection (if memory serves me that's using something called MSQUERY). That would definitely be my least favourite choice!! I've only ever used MSQUERY in an XLS to PULL data from the database INTO the spreadsheet, which seems a more logical use in Excel to me ... but it will work the other way round, if you ask it to.

The Windows Task Scheduler has an "On Boot" event. You can have that run a batch file, that flips the 0/1 value in your SQL database, would that do? i.e. instead of having a Human do it, just get the "On Boot" event [on each server] to trigger that action automatically?


#3

You could also use Powershell script - once you have installed the correct components you would be able to:

> Invoke-SqlCmd 
    -ServerInstance {servername} `
    -Database {databasename} `
    -Query "Your update query here";

You can then create a shortcut on the user's desktop that executes the script.

Note: this uses Windows Authentication so you would have to insure the users have been setup. It also assumes you have an UPDATE statement and the users have permissions to update the table. I would highly recommend NOT using an UPDATE statement and instead use a stored procedure and grant the users access to ONLY execute the stored procedure.