Tuesday, August 19, 2008

Control sql server through command prompt

Following are listing of Sql command to handle sql server from command prompt
(In case of sql server 2000 use osql,and sqlcmd for 2005)

usage: Sqlcmd            [-U login id]          [-P password]
  [-S server]            [-H hostname]          [-E trusted connection]
  [-d use database name] [-l login timeout]     [-t query timeout]
  [-h headers]           [-s colseparator]      [-w screen width]
  [-a packetsize]        [-e echo input]        [-I Enable Quoted Identifiers]
  [-c cmdend]            [-L[c] list servers[clean output]]
  [-q "cmdline query"]   [-Q "cmdline query" and exit]
  [-m errorlevel]        [-V severitylevel]     [-W remove trailing spaces]
  [-u unicode output]    [-r[0|1] msgs to stderr]
  [-i inputfile]         [-o outputfile]        [-z new password]
  [-f <codepage> | i:<codepage>[,o:<codepage>]] [-Z new password and exit]
  [-k[1|2] remove[replace] control characters]
  [-y variable length type display width]
  [-Y fixed length type display width]
  [-p[1] print statistics[colon format]]
  [-R use client regional setting]
  [-b On error batch abort]
  [-v var = "value"...]  [-A dedicated admin connection]
  [-X[1] disable commands, startup script, environment variables [and exit]]
  [-x disable variable substitution]
  [-? show syntax summary]

The example below shows how to handle sql server
First of all to open the command promt go through the following process
Click on start->then Run->type cmd->Press enter->now you can see a command promt window.

1->Example to show server name and version of sql server in 2005)
Type the Following command to command prompt nothing else.
SQLCMD -S P13 -E
1> select @@servername
2> Select @@version
3> go
Note:->Here P13 indicate server name and E for windows authentication.
(N.B->Just use OSQL instead of SQLCMD in case of 2000 server)

Alternative way.
SQLCMD -U sa -P sa -Q"Select @@servername"
(N.B->Just use OSQL instead of SQLCMD in case of 2000 server)
Note:->Here sa= your sql server username and sa your sql server password.

2->Example to changes the default database to 'master':
SQLCMD -d master -U sa -P sa -Q"EXEC sp_defaultdb 'MyLogin','master'"
(N.B->Just use OSQL instead of SQLCMD in case of 2000 server)
Note:->Here sa= your sql server username and sa your sql server password
(N.B->To come out from a command you can type quit)

2->Example to changes the any database to 'any database':
SQLCMD -d osaosa -U sa -P sa -Q"EXEC sp_renamedb 'olddb', 'newdb'"
(N.B->Just use OSQL instead of SQLCMD in case of 2000 server)
Note:->Here sa= your sql server username and sa your sql server password

1 comment:

D said...

Hi, nice post!
I have a question, how can i connect to the default sql instance from the cmd, without knowing the instance name.
I think it is possible, but i have no idea how to do it.