Archive for March, 2009

SQLXML : How To Save XML Query Results to a File Using BCP

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.

Read the rest of this entry »

VN:F [1.9.22_1171]
Rating: 8.3/10 (22 votes cast)
VN:F [1.9.22_1171]
Rating: +3 (from 3 votes)

SQLXML : How to Bulk Load XML From a File Using OPENXML

Using OPENXML you can import XML documents into an XML variable, or into a table that has XML columns. The following is an example:

   1: -- ==========================================================================
   2: -- Object       : OPENXML1.sql
   3: -- Object Type  : Script
   4: -- Description  : Various examples
   5: -- Developer    : Donabel Santos
   6: -- Origin       : 2008/08/17
   7: -- Last Modified: 2008/10/04
   8: -- Notes        : 
   9: -- ==========================================================================
  10:  
  11: -- bulk load
  12: -- For this example, XML file must be saved in C:\
  13: -- XML file also should specify UTF-8 encoding, ie:
  14: -- <?xml version="1.0" encoding="UTF-8"?>
  15:  
  16: DECLARE @XMLTable TABLE
  17: (
  18:    xmlcol XML
  19: )
  20:  
  21: INSERT INTO @XMLTable(xmlcol)
  22: SELECT
  23:     InvoicesXML
  24: FROM
  25: (
  26:     SELECT * 
  27:     FROM OPENROWSET(BULK 'c:\invoice.xml',SINGLE_BLOB) AS Invoices
  28: ) AS Invoices(InvoicesXML)
  29:  
  30: SELECT *
  31: FROM @XMLTable
  32:  
  33:  

VN:F [1.9.22_1171]
Rating: 9.1/10 (12 votes cast)
VN:F [1.9.22_1171]
Rating: +1 (from 1 vote)

SQLXML : How to Alter Existing Column Schema (XSD)

Sometimes you may need to change schema definitions in existing columns.

The following shows an example of how you can do this:

   1: -- drop the XML schema from existing column definitions
   2: ALTER TABLE AD
   3:    ALTER COLUMN Title xml
   4:  
   5: -- DROP IF EXISTS
   6: IF    EXISTS (SELECT name 
   7:               FROM  sys.xml_schema_collections 
   8:               WHERE name='TitleSchema')
   9:    DROP  XML SCHEMA COLLECTION TitleSchema
  10: GO
  11:  
  12: -- new definition
  13: -- there can only be one Title element
  14: CREATE XML SCHEMA COLLECTION TitleSchema AS 
  15: '<?xml version="1.0" encoding="utf-8"?>
  16: <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  17:   <xsd:element name="Title">
  18:     <xsd:complexType>
  19:          <xsd:attribute name="Type" type="xsd:string" />
  20:          <xsd:attribute name="Value" type="xsd:string" use="optional" />
  21:     </xsd:complexType>
  22:   </xsd:element>
  23: </xsd:schema>'
  24: GO
  25:  
  26: -- IMPORTANT: before you add back the schema to the column 
  27: -- definition, make sure all values in your existing column 
  28: -- comply with the new schema definition
  29: ALTER TABLE AD
  30:    ALTER COLUMN Title xml(TitleSchema)

VN:F [1.9.22_1171]
Rating: 8.5/10 (4 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

SQL Server 2005/2008 System Views Map

Excerpt from the Microsoft Site:

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:

http://download.microsoft.com/download/B/4/7/B47BB887-29ED-42F5-9289-90689CA1FB10/SQL_Server_2008_System_Views_Poster.pdf

There is an equivalent one for SQL Server 2005:

http://download.microsoft.com/download/0/d/f/0dfe488e-a335-4480-8a8a-b405e32f4368/SQL2005_Sys_Views.pdf

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

SQLTeach/DevTeach Vancouver Stimulus Package

SQLTeach/DevTeach Vancouver 2009 - Stimulus Package

SQLTeach/DevTeach Vancouver 2009 - Stimulus Package

SQLTeach/DevTeach is offering a “stimulus package” – register for 2 attendees to the SQLTeach/DevTeach Vancouver conference on June 8-12, 2009

Excerpt from DevTeach site:

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

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

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.

   1:  
   2: CREATE TABLE t (id INT)
   3: GO
   4:  
   5: INSERT INTO t VALUES(1)
   6: INSERT INTO t VALUES(2)
   7: INSERT INTO t VALUES(3)
   8: INSERT INTO t VALUES(4)
   9: GO
  10:  
  11: -- this displays what was deleted
  12: DELETE t
  13:        OUTPUT deleted.id AS 'deleted';
  14:  
  15: -- this displays what is inserted
  16: INSERT INTO t
  17:     OUTPUT inserted.id AS 'inserted'
  18: VALUES(1)

VN:F [1.9.22_1171]
Rating: 9.5/10 (2 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

SQL Server in the Cloud is coming?

Brent Ozar posted in his blog: Real SQL Server in the Cloud is Coming Fast. Sounds exciting, and scary. Definitely feels like we are fast forwarding into the future.

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.

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

SQLXML : How to List Columns That Have the XML Data Type

   1: -- this lists the table catalog, table name, 
   2: -- column name, and data type
   3: SELECT 
   4:     TABLE_CATALOG,
   5:     TABLE_NAME,
   6:     COLUMN_NAME, 
   7:     DATA_TYPE 
   8: FROM 
   9:     INFORMATION_SCHEMA.COLUMNS
  10: WHERE 
  11:     DATA_TYPE = 'xml'
  12:  

 

   1: -- this lists the corresponding schemas
   2: SELECT 
   3:     DISTINCT
   4:     OBJECT_NAME(sys.columns.object_id)        AS 'TableName',
   5:     sys.columns.name                    AS 'ColName',
   6:     sys.xml_schema_collections.name            AS 'Schema' 
   7: FROM 
   8:     sys.columns
   9:     LEFT JOIN     sys.xml_schema_collections 
  10:     ON sys.columns.xml_collection_id = sys.xml_schema_collections.xml_collection_id
  11: ORDER BY 
  12:     OBJECT_NAME(sys.columns.object_id), sys.columns.name    
  13:  

VN:F [1.9.22_1171]
Rating: 9.0/10 (1 vote cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)
`