BTerrell Group Blog

Scheduled SQL Server Data Export to CSV using SQLCMD

Posted by Kevin Yu on Tue, Jun 24, 2014

You may recall my blog article last month about using the SQLCMD utility to run a script from the command prompt to restore a database. Ever since then, I’ve found more and more opportunities for the SQLCMD utility to help me manage challenges in Microsoft SQL Server.

This time, the challenge was to generate a scheduled CSV output of data from SQL Server. I could get the data I wanted formatted correctly by using a SQL query and then copy/paste into Excel, but I needed to find a way to have this process done automatically, on a schedule. A little bit of Google-ing led me back to utilizing the SQLCMD utility.

I saved my SQL query that returned the data formatted the way I needed and then entered the text below in the command prompt line:

sqlcmd -S server\instance -i  c:\SQL_Query.sql -o c:\CSV_Output.csv -s”,”

This generated the CSV file, but I still required this process to be scheduled. I knew I could use Windows Task Scheduler to start a program on a schedule I specified, and since SQLCMD is run from the command prompt (cmd.exe), I set up a task to start cmd.exe and used the above text in the add arguments field.

 6 24image1

I’ve included a list of other arguments for your reference you can pass sqlcmd:

   -a packet_size

   -A (dedicated administrator connection)

   -b (terminate batch job if there is an error)

   -c batch_terminator

   -C (trust the server certificate)

   -d db_name

   -e (echo input)

   -E (use trusted connection)

   -f codepage | i:codepage[,o:codepage] | o:codepage[,i:codepage]

   -h rows_per_header

   -H workstation_name

   -i input_file

   -I (enable quoted identifiers)

   -k[1 | 2] (remove or replace control characters)

   -K application_intent

   -l login_timeout

   -L[c] (list servers, optional clean output)

   -m error_level

   -M multisubnet_failover

   -N (encrypt connection)

   -o output_file

   -p[1] (print statistics, optional colon format)

   -P password

   -q "cmdline query"

   -Q "cmdline query" (and exit)

   -r[0 | 1] (msgs to stderr)

   -R (use client regional settings)

   -s col_separator

   -S [protocol:]server[\instance_name][,port]

   -t query_timeout

   -u (unicode output file)

   -U login_id

   -v var = "value"

   -V error_severity_level

   -w column_width

   -W (remove trailing spaces)

   -x (disable variable substitution)

   -X[1] (disable commands, startup script, environment variables and optional exit)

   -y variable_length_type_display_width

   -Y fixed_length_type_display_width

   -z new_password

   -Z new_password (and exit)

 

I set up my task to run every night at 2AM and it is working perfectly. The SQLCMD utility helped me resolve two challenges in the past month and I hope to find more opportunities to use SQLCMD to share moving forward.

Tags: SQL, Microsoft

SQL Server Troubleshooting: Collation Conflict

Posted by Kevin Yu on Tue, Jun 10, 2014

 

I was working on a project last week that required me to create a JOIN that links two tables in the same database. Typically this is a relatively simple process, but when I tried to execute my query, I got the following error message:

6 10image1

 

 

 

 

I was surprised to see this message since a single collation is usually used throughout a database. I checked the collation for each column in the tables by using the following query:

                SELECT col.name, col.collation_name

                FROM sys.columns col

                WHERE object_id = OBJECT_ID(‘TableName’)

I found that the two tables I wanted to join were indeed using two different collation methods. In order to get around this issue, I forced which collation I wanted to use by using the COLLATE clause:

                SELECT *

                FROM Table1

                JOIN Table2

                ON Table1.ColumnA = Table2.ColumnA COLLATE SQL_Latin1_General_CP1_CI_AS

You can change either the first table’s collation method to match the second, or vice versa. You can also use COLLATE DATABASE_DEFAULT to use the default collation method set in the database properties.

I hope this will save you some time in the future when dealing with collation issues.

 

Tags: SQL, server, collate, collation

Restoring a SQL Database to an Older Version of SQL

Posted by Michelle Tanner on Tue, May 13, 2014

by Kevin Yu


I ran across an interesting situation last week where I needed to restore a backup copy of a SQL 2012 database to SQL 2008 R2. You can restore databases from 2005 or 2008 to 2012 by using the backup and restore functions in SQL Server Management Studio, but not the other way around.

What I did to get work around this issue was to use the Generate Scripts… function.

5 13image1 resized 600
This brings up a wizard that lets you choose what objects you want to include and provides you some options with how your scripts should be saved or published. Within the Advanced Scripting Options window, I chose SQL Server 2008 R2 since that was the version I needed restored. I also included Schema and data for the Types of data to script.

If the script is not too large, you can just run the script in SQL Server Management Studio without any issues. However in my case, since I chose to script the schema and data, I got an error message when trying to run the script saying that I had insufficient memory to continue the execution of the program.

To work around this issue, I used the sqlcmd utility to run my script from the command prompt.

  1. Open command prompt window
  2. Type sqlcmd –S server\instance –i C:\MyScriptFile.sql
  3. Press Enter.

I’ve included a list of other arguments you can pass sqlcmd below:

 5 13image2 resized 600

In this situation where the database versions differ, I actually recommend upgrading the older database version to match the newer version if possible. If not, then I hope the steps I took above help you work around the compatibility issues between versions.

Tags: SQL database, SQL