SQL Server and short tips – 2

• Sqlcmd utility comes in replacement of osql utility (with SQL 2000 version) and isql utility (with SQL 7.0 version) but still you can run osql with SQL Server 2005/2008.
• Sqlcmd used to reach the sql server instance from outside of SSMS or GUI for repetitive tasks or automate the execution of SQL scripts.
• To see all commands of sqlcmd use the following: sqlcmd /?
• You can access the instance of SQL Server as Administrator and only one connection allowed for troubleshoot problems even if the server does not responses to any client connections.

Let's see this example which passes a variable from the sqlcmd to sql script file

1. Create in c:\script.sql file
2. Write in this file the following command:

USE AdventureWorks
GO
SELECT $(ColumnName) FROM $(TableName)
GO

3. Open cmd window then run this command

sqlcmd -S .\sql2008 -E -i c:\script.sql -v ColumnName="ProductNumber" TableName="Production.Product"

And then press enter.

• You can shut down your instance (stop the instance service) by using the following command:

After connection has made, write the following:

Shutdown with nowait

• And last tip is a Security in Sql 2005/2008 is inherited in Hierarchical scope.