One way to save your XML query results to the file system is by using bcp (bulk copy program).
Be aware of the following before deciding to use bcp for your regular export requirements:
bcp is a program external to SSMS. If you need to use this from within your scripts, you will need to enable xp_cmdshell. xp_cmdshell is an extended stored procedure that allows external command line processes to be executed from within SQL Server. Enabling xp_cmdshell is considered to be a big no no in terms of security because this opens up avenues for malicious attacks through SQL Server.
Depending on how much data you need to export, you may need to batch your export to overcome rowsize limitations of bcp.
If you intend to use bcp from within SSMS, you will need to enable xp_cmdshell first, otherwise you will 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.
The following example walks you through enabling xp_cmdshell, and using bcp from within SSMS to save your XML query to an external file.
The Microsoft SQL Server 2008 System Views Map shows the key system views included in SQL Server 2008, and the relationships between them. The map is similar to the Microsoft SQL Server 2005 version and includes updates for the new and updated the Microsoft SQL Server 2008 features such as resource governor, extended events, full-text search, and others.
You can download the SQL Server 2008 version from:
In difficult times the best thing you can do is to upgrade your skills. This is why DevTeach came up with the idea of an Education stimulus package. You can get a free registration when your company or group registers two attendees at our main event. So register 2 attendees and get one free! This promotion is applicable on the early bird price and will run until June 8th. You can take advantage of this deal even if you are using a rebate code. If you compare our price with any similar event in the USA you will find that our price is significantly lower. For you guys coming from the US it’s even a better deal because of the exchange rate
In case you haven’t come across this yet. The OUTPUT clause allows you to display or manipulate rows affected by INSERT/UPDATE/DELETE, similar to the inserted and deleted virtual tables in a DML trigger.
I think it’s still going to be a slow process. Not everyone will switch over, and we have to get over a huge "trust issue". I still have clients who are working on SQL Server 2000 boxes, and using Windows XP for desktop use. Some clients refuse to upgrade from SQL Server 2005 to SQL Server 2008, unless they see a lot of "evidence" that SQL Server 2008 is reliable as a rock (or something like that).
The adoption process needs not just the technological backbone – robust architecture, reliable services, no downtime. For SQL Server in the Cloud to be accepted (just like anything else), the way we think about data and infrastructure needs to change too. At this point, many business still feel "safer" when their data sits on their local boxes or on boxes within their own networks. In addition, SQL in the cloud needs to be secure enough to meet legal trictions and regulatory compliance procedures.