Content Preview: rss
1162 days ago
Bulk importing XML as a binary byte stream When bulk loading XML from a file that contains an encoding declaration that you want to apply, specify the SINGLE_BLOB in OPENROWSET. This option ensures that the XML parser in SQL Server imports the data according to the encoding scheme specified in the XML declaration. For example, the following INSERT statement inserts an XML instance in a single column table. USE tempdb CREATE TABLE T (IntCol int, XmlCol xml) GO INSERT INTO T(XmlCol) SELECT * FROM OPENROWSET( BULK 'c:\SampleFolder\SampleData3.txt', SINGLE_BLOB) AS x To run this bulk load, create a utf-8 encoded file (c:\SampleFolder\SampleData3.txt) with the following sample instance that specifies the UTF-8 encoding scheme and then execute the INSERT statement. <?xml version="1.0" encoding="UTF-8"?> <Root> <ProductDescription ProductModelID="5"> <Summary>Some ...
1165 days ago
New to SQL Server 2005, is The XML data type, which lets you store XML documents and fragments in a SQL Server database. An XML fragment is an XML instance that is missing a single top-level element. You can create columns and variables of the XML type and store XML instances in them. Note that the stored representation of XML data type instances cannot exceed 2 GB. Where can you use the XML data type a. Table b. Variable c. Parameters d. Return of a function To do: Store XML to a table a. Create a Table with XML data type CREATE TABLE ProductDocs (ID INT IDENTITY PRIMARY KEY, ProductDoc XML NOT NULL) GO b. Store XML data Into the table INSERT INTO ProductDocs VALUES(' <Product> <ProductID>1</ProductID> <ProductName>Chai</ProductName> <SupplierID>1</SupplierID> <CategoryID>1</CategoryID> <QuantityPerUnit>10 boxes x 20 bags</QuantityPerUnit> ...
1191 days ago
A database snapshot is a read-only, static view of a database (called the source database). Each database snapshot is transactionally consistent with the source database at the moment of the snapshot's creation. When you create a database snapshot, the source database will typically have open transactions. Before the snapshot becomes available, the open transactions are rolled back to make the database snapshot transactionally consistent. How to ? Run the following Sql codes to create the snapshot CREATE DATABASE AdventureWorks_dbss1800 ON ( NAME = AdventureWorks_Data, FILENAME = 'C:\central data\central data\snapshots\data_1800.ss' ) AS SNAPSHOT OF AdventureWorks; Why do the snapshot? 1. can be used to maintain historical data how does the data look like at a particular point in time such as the end of every quarter or month which can be used for historical reporting. 2. It serves a read-only mirror of the data You can ...
1221 days ago
SYNTAX BEGIN TRY <SQL Statements> END TRY BEGIN CATCH TRAN_ABORT <SQL Statements> END CATCH OVERVIEW Prior to sql server 2005 error handling is done by querying @@error after every line of execution. In sql server 2005, try catch block provides a new structure way of handling error similar to those of many development language such as VB, Java and C# One of the advantage of try catch block over its old friend @@error is that it enables transaction abort errors to be captured with no loss of the transaction context EXAMPLES a. constraint violation USE AdventureWorks BEGIN TRY UPDATE HumanResources.Employee SET Gender = 'X' WHERE EmployeeID = 1; END TRY BEGIN CATCH SELECT ERROR_NUMBER( ) ErrorNumber, ERROR_STATE( ) ErrorState, ERROR_SEVERITY( ) ErrorSeverity, ERROR_MESSAGE( ) ErrorMessage; END CATCH b. selecting from non existing table USE AdventureWorks; ...
