Saving the Result of a query to a Text File in Sql Server

After being a part of the Sql Server MSDN forum for a quite a long time, One question I answered many time is “How to save the output of query to a text file in Sql Server?”
So thought of sharing a simple solution with you using BCP utility.

A simple query to save output of query to a text file is goes like this –

EXEC master..XP_CmdShell 'BCP "SELECT * FROM Database.dbo.TableName" queryout "c:\Mangal.txt" -c -T'

If you execute the above query and get the following error –

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

Then first you need to enable the xp_cmdshell procedure. To enable the xp_cmdshell execute -

sp_configure 'show advanced options',1
GO
RECONFIGURE
GO
sp_configure Xp_CmdShell,1
GO
RECONFIGURE WITH OVERRIDE
GO

Again back to BCP, Here
-c : Performs the operation using a character data type. This option does not prompt for each field; it uses char as the storage type, without prefixes and with \t (tab character) as the field separator and \n (new line character) as the row terminator.

-T : Specifies that the bcp utility connects to SQL Server with a trusted connection using integrated security. The security credentials of the network user, login_id, and password are not required. If –T is not specified, you need to specify –U and –P to successfully log in.

You can save the file to txt, xls, dbf, xml formats also.

There are so many other options available with BCP command.
e.g.
With –U and –P you can provide the username and password when you are not using the Windows Authentication (Trusted Connection).

For more on BCP utility you can read from Microsoft’s Books Online.
BCP Utility Books Online


Idea for this post was just to provide a simple solution on how to save the result of a query to a file?.

Well there is another way of directly sending the result of query to a file is by setting the “Result To Text” in management studio.
For that do –
Open the Management Studio.
In menu bar click to “Query” >> “Result to” and then select “Result To Text.”

Hopefully many of you find this post helpful.

- Mangal

Linked Server With Excel.

In this part I’ll help you in creating a Linked Server with Excel. Or simply how to import/query Excel in Sql Server.

In Sql Server 2005 you need to enable the 'Ad Hoc Distributed Queries'. For that first execute the following scripts.
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OverRide
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE WITH OverRide
GO

And now here go
1.
SELECT * FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data Source=E:\Mangal.xls;Extended Properties=Excel 8.0')...Sheet1$
2.
SELECT * FROM OPENROWSET ( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=E:\Mangal.xls', Sheet1$)
3.
SELECT * FROM OPENROWSET ( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=E:\Mangal.xls', 'SELECT * FROM [Sheet1$]')

Here Excel 8.0 means this is applicable to Excel 2002, Excel 2000, or Excel 97 workbook.
Database means path of your excel book and its name.

Now you can do any Sql query with excel that you usualy do with any table. For example you can join excel with other tables, you can insert excel rows in other tables.
Lets say you want to insert first 3 columns of Excel sheet into table Customers.
You can do
INSERT INTO customers (CustId, CustomerName, Address )
SELECT Custid, CustomerName, Address
FROM OPENROWSET ( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; Database=E:\Mangal.xls', 'SELECT * FROM [Sheet1$]')

Or to join a table with Excel

SELECT A.ColumnName, E.ColumnName
FROM OPENROWSET ( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=E:\Mangal.xls', 'SELECT * FROM [Sheet1$]' ) E
INNER JOIN Sql_Table A
ON a.id=e.id

- Mangal Pardeshi.

Some Useful Views And SPs in Sql Server.

Here is the good collection of views and Store Procedure which can be very useful.
Applicable for Sql Server 2005 and +

VIEWS : To use all the views you just need to do SELECT * FROM VIEW_NAME
e.g SELECT * FROM sys.databases

• sys.databases : Lists all the databases in Sql Server.
• sys.tables : Lists all the Tables in the database.
• sys.views : Lists all the views in the database.
• sys.procedures : Lists all the Procedures in the database.
• sys.triggers : Lists all the Triggers in the database.

• sys.columns : Lists all the columns of tables in database.
• sys.syscolumns : Lists all the columns in database including of those SP.
• sys.key_constraints : Lists all primary key or unique constraints in database. For primary key TYPE = 'PK' and for unique keys TYPE = 'UQ'
• sys.check_constraints : Lists all the Check Constraints in the database.
• sys.default_constraints : Lists all the Default Constarints in the database.
• sys.foreign_keys : Lists all the Foreign Keys in the database.

• sys.syslogins : Lists all the login names in server.
• sys.sql_logins : Lists all the Sql Authentication Logins in server.
• sys.sysusers : Lists all the users in database.

-- I'll Add some more very soon.

- Mangal Pardeshi.

Sql Server Connection Issues - Msg 18456

Now I'll try to help you understand the connection issues with Sql Server.
Today we will focus on the error

"Msg 18456, Level 14, State 1, Server , Line 1"
"Login failed for user ''."

This error is nothing but an authentication failure that involves a bad password or user name. So whenever you get a similar error be sure that is somthing related to user name or password.
And key to solve the issue is STATE number you get in the error.

• 2 and 5 : Invalid userid.
• 6 : Attempt to use a Windows login name with SQL Authentication.
• 7 : Login disabled and password mismatch.
• 8 : Password mismatch.
• 9 : Invalid password.
• 11 and 12 : Valid login but server access failure.
• 13 : SQL Server service paused.
• 16 : User don't have permission to log into Server.
• 18 : Change password required.

And as it seems you get the error Msg 18456 when you trying to connect Sql server in Sql Authentication.

Some points I like to add....

State 23 : Is one of the very rare errors, normaly happens when you try to connect to server when it is shutting down.

States 11 and 12 : When the Windows user trying to access the server and he doesn't have rights. Chances are you are on Windows Vista :).

- Mangal Pardeshi.

Date Formats In SQL SERVER.

In this post I will try to explain how to convert date in required format.

As we know by default (U.S. English) Sql Server shows dates in mdy.
i.e.
2008-11-22 14:02:12.513
OR
11/22/2008 2:02:00 PM
And so many times you wants the date in dd/mm/yy or dd Mon yyyy or any other format you want.

The function used by sql server to convert the dates in required format is CONVERT. Which I personaly find more easy to use compare to to_date used by Oracle or other fuctions used by other DBMS.

Syntax for the CONVERT is
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
Where data_type can be either string datatype (char/ varchar) or datetime.
And Style in nothing but numbers in 1st 2 columns in following table .




1. Now for example If I want the date in "mon dd yyyy"
I will choose the style as 100 (from the above table)

SELECT CONVERT( varchar(50), date_column, 100)
FROM table_name
go;

e.g.
SELECT CONVERT(varchar(50), GETDATE(), 100)
GO
/* output
Nov 22 2008 2:21PM
*/
2. Or if I want mm/dd/yyyy I will select style 101

SELECT CONVERT(varchar(50), GETDATE(), 101)
GO
/* output
11/22/2008
*/

Now if instead of 2008 if I want only 08 I will select style from 1st column instead of column 2 which is style 1

SELECT CONVERT(varchar(50), GETDATE(), 1)
GO
/* output
11/22/08
*/
So if you want the century part in year (2008, 2009)select style from column 2 (100, 101, 102) or if you want only 2 digits of year (08, 09) select style from 1st column (1, 2, 3).

For more on CONVERT see
CAST and CONVERT (Transact-SQL) Books Online


- Mangal Pardeshi.