Running sql from command prompt

Question

Running sql from command prompt

shahgols

SSCrazy Eights

Points: 9874
More actions
July 22, 2004 at 1:14 pm

#161192

Hi everyone,
Is it possible to run sql statements from the command prompt? I have a .bat file with sql statments that I would like to run from the cmd. Thanks for your help.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

RSP SSCrazy Points: 2614 More actions · Answer 1

Absolutely! Take a look at SQL Books On-Line, or other resources, and look at OSQL and ISQL, both with are run through cmd line. HTH

Gabriel-126745 SSC Veteran Points: 280 More actions · Answer 2

try :

osql /? .

You can find an enhanced version eisql at

http://www.imranweb.com

shahgols SSCrazy Eights Points: 9874 More actions · Answer 3

Thank you everyone!

I have put together an osql command (and am looking at the eisql) but when I try to run "Exec sp_detach_db...", it says that it cannot detach the database because it is in use. Does anyone know of a way to clear all connections to the database (through scripts) before attempting to detach? Thanks again!

RSP SSCrazy Points: 2614 More actions · Answer 4

Although this is a different issue, I will post a script to use below (be careful killing users) Add the databases for which to kill the processes (OR change it to NOT IN ('master') HTH!

-------------------------------------------

DECLARE @ps VARCHAR(255)

DECLARE @id smallint

DECLARE c CURSOR FOR

SELECT

DISTINCT

spid

FROM

master..sysprocesses

WHERE

dbid

IN

(SELECT

dbid

FROM

master..sysdatabases

WHERE

name

IN

('YourDB1', 'YourDB2', 'Etc.'))

OPEN c

FETCH NEXT FROM c INTO @id

WHILE (@@FETCH_STATUS -1)

BEGIN

SELECT @ps = "KILL " + CONVERT(VARCHAR(8), @id)

EXEC(@ps)

FETCH NEXT FROM c INTO @id

END

CLOSE c

DEALLOCATE c

shahgols SSCrazy Eights Points: 9874 More actions · Answer 5

shahgols

SSCrazy Eights

Points: 9874

July 22, 2004 at 2:53 pm

#515770

Beautiful! Thank you very much!

Johan Bijnens SSC Guru Points: 135361 More actions · Answer 6

in sql2k you can also use

alter database yourdb SET OFFLINE WITH ROLLBACK IMMEDIATE

This way you avoid problems with reattatching the db at the same server and all users keep their settings and you can copy the db-files.

Later on you can then use :

alter database yourdb SET ONLINE

btw to take a backup ... use BACKUP DATABASE look at BOL

There is no need for a db to be offline or detached to take a backup !

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

shahgols SSCrazy Eights Points: 9874 More actions · Answer 7

Thank you alzdba. Can you (or anyone) tell me what the rollback immediate will do? What happens to running scripts? What happens to the data that is in the dirty buffer? Etc. Thanks again.

Johan Bijnens SSC Guru Points: 135361 More actions · Answer 8

from BOL :

WITH <termination>

Specifies when to roll back incomplete transactions when the database is transitioned from one state to another. Only one termination clause can be specified and it follows the SET clauses.

ROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATE: Specifies whether to roll back after the specified number of seconds or immediately. If the termination clause is omitted, transactions are allowed to commit or roll back on their own.
NO_WAIT: Specifies that if the requested database state or option change cannot complete immediately without waiting for transactions to commit or roll back on their own, the request will fail.

So the rollback immediate would have the same user effect as the combination of "kill", but because of the "alter database" the new db-state is so that no user can connect.