Tag Archives: sql server 2008

Create comma separated file from SQL Server 2008 with BCP and XP_CMDSHELL

I was assigned a task few days back to generate comma separated file from SQL Server table, It is very easy to do so with BCP utility. Generally we used to use BCP from DOS prompt but herewith I am going to use BCP right from my SSMS with XP_CMDSHELL.

 

use Adventureworks

CREATE TABLE [dbo].[emps](

[Name] [varchar](50),

[Dept] [varchar](10),

[Company] [varchar](15)

) ON [PRIMARY]

GO

 

–insert records

INSERT INTO emps

SELECT ‘RITESH’,‘MIS’,‘ECHEM’ UNION ALL

SELECT ‘Rajan’,‘MIS’,‘mar’

 

 

declare @sql varchar(1000)

–creating emps.txt file in D drive from just created table above

select @sql = ‘bcp Adventureworks..emps out d:\emps.txt -c -t, -T -S ‘+ @@servername

–print @sql

–generally, you should run BCP from command prompt, if you wish to do it from

–SSMS, you have to use xp_cmdshell

exec master..xp_cmdshell @sql

 

 

If your xp_cmdshell is disable, there is one small article to enable it at:
http://www.sqlhub.com/2009/05/enable-xpcmdshell-in-sql-server-2008.html

I have written some more interesting article on BCP which you can find at
http://www.sqlhub.com/2009/03/bcp-or-bulk-copy-program-in-sql-server.html
http://www.sqlhub.com/2009/03/bcp-export-data-from-sql-server-to-flat.html
http://www.sqlhub.com/2009/03/bulk-insert-bulk-copy-or-bcp-difference.html

 

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Enable XP_cmdshell in SQL Server 2008

XP_CMDSHELL is very useful extended stored procedure in Microsoft SQL Server 2008 which will give you power to run command as an operating system power shell. By default it is disabled in any Microsoft SQL server version due to security reasons but you can enable it for your need. Herewith, I am giving you T-SQL commands to enable it.
–list all setting which you can configure with SP_Configure stored procedure
–you may not be able to see XP_CMDSHELL command in the list.
EXECUTE SP_CONFIGURE
–see full list of command by making show advanced options ON
EXECUTE SP_CONFIGURE ‘show advanced options’, 1
RECONFIGURE WITH OVERRIDE
GO
–enable xp_cmdshell command
EXECUTE SP_CONFIGURE ‘xp_cmdshell’, ‘1’
RECONFIGURE WITH OVERRIDE
GO

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Difference between Microsoft SQL Server 2005 and 2008

This question may rise every time new version comes into the market. People will buy new version if it is going to make their life easy and this is one of the common interview question as well if you wrote in your CV that you know MS-SQL Server 2005 and 2008.

Anyway, let us move on the topic and starts our journey. BTW, if you are interested to know difference between Microsoft SQL Server 2000 and 2005 than do visit http://ritesh-a-shah.blogspot.com/2009/03/difference-between-microsoft-sql-server.html

Basically one can write a book on the differences as Microsoft SQL Server 2008 comes up with lots of new features but we will look at it at glance.

Microsoft has divided new features of SQL-Server 2008 in four parts.

Enterprise Data Platform
Dynamic Development
Beyond Relational
Pervasive Insight

You can dig details about sub-category of all above four categories from Microsoft Website. URL of the same is given at the end of this article. I will explain some of the features which I like most.

New Date and Time Data Types: This feature gave solution to age old problem of Microsoft SQL Server. You can now have only Date data type without any time and you can have only Time data type without having any date stamp. You can have precision up to 100 nanoseconds for Time data type. One more advancement is you will have Date Time Offset which will store UTC time zone aware value.
Table Value Parameter: We had not any way to pass complete table to Stored Procedure in any of the previous version of SQL Server so we have worked with workaround solutions but now we have a way to pass complete table to stored procedure.

Spatial Data: this is really amazing data type. It allows storing Latitude, Longitude and GPS based data entry to be stored.

Encryption: This feature allows encrypting the database backup so that it will prevent from the data tempering.

Office 2007: You can directly export your report from SQL Server Reporting Service (SSRS) to word file and you can use your word and excel file as a source of report in SSRS.

Merge: This is also one of the beautiful features. You can now merge UPDATE, INSERT and DELETE statement with SELECT based on the condition.

Reference: Ritesh Shah
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah