User Tools

Site Tools


notes:sql:utilities

SQL Server Utilities

  • osql - uses ODBC API to communicate with SQL Server
  • isql - based on DB-Library API; cannot access ntext, varchar>255 etc.
  • bcp - used to import and export data to and from SQL Server

Location of SQL Server utilities (for SQL Server 2012 Express):

C:\Program Files\Microsoft SQL Server\110\Tools\Binn

BCP

BCP copies data between an SQL Server and a data file:

  • 'out' - direction: from the table or view to a file
  • -c - character data type
  • default separators: \t - the field separator, \n - the row terminator
REM Example #1
bcp MyDatabase..Customers c:\customers.txt -c -Sserver -T
 
REM Example #2
bcp MyDatabase..Customers out customers.txt -c -t , -r \n -Sserver -Uuser -Ppassword

OSQL

The most commonly used arguments (it's possible to use / instead of - ):

  • -U login_id - The user login ID
  • -P password
  • -E - Trusted connection
  • -S server_name - The SQL Server to which to connect
  • -i input_file -or- < input_file - The file that contains a batch of T-SQL statements or stored procedures
  • -o output_file -or- > output_file - The file that receives output from osql
  • -b - osql will return a DOS ERRORLEVEL value when an error occurs. ERRORLEVEL=1 when the SQL Server error message has a severity of 10 or greater; otherwise, the value is 0.

Examples:

osql -E -S servername -d dbname -i script.sql -n > output.txt
osql -S servername -d dbname -U username -P password -i script.sql -n > output.txt
osql -U username -P password -S server -d database -w 512
osql -U username -P password -d database -i script.sql -o output.txt
osql -U username -P password -d database
osql -E -S server -d database -i script.sql -n -o output.txt

Working in the interactive mode:

osql /E
1> use nothwind
2> select * from mytable
3> go
4> exit  (or quit)

xp_cmdshell

Execute a command prompt utility from within SQL:

Example #1:
 
DECLARE @cmd VARCHAR(200), @SQL VARCHAR(150)
-- ... some code to populate @sql goes here
SET @cmd = 'bcp "' + @SQL + '" -c -Sserver -T' 
EXEC master..xp_cmdshell @cmd
 
Example #2:
 
DECLARE @msg VARCHAR(500) 
SELECT @msg = 'osql -Sserver -E -iTest.sql -oLog.txt -dDatabase' 
EXEC master..xp_cmdshell @msg
notes/sql/utilities.txt · Last modified: 2015/06/24 by admin