SQL Server 2005 has introduced TRY / CATCH block which made error handling easy. The construct is similar to error-handling concepts in languages like C#. If an error occurs in the TRY block, or in a stored procedure called by the TRY block, execution is transferred to the CATCH block. Error Handling Functions In the […]
If you want to process each row at a time, Cursors is your best bet. You could perform complex operations of row by row sequentially. Imagine cursor as a pointer in C++ pointing to a row in a result set. To summarize the whole process, you could say using cursors involves these simple four steps […]
SQL Server supports functions for both modifying and reading XML data. In this article, I would concentrate reading XML using T-SQL.
Methods on XML Data Type
The query() method is useful for extracting parts of an XML instance. The XQuery expression evaluates to a list of XML nodes. The subtree rooted at each of these nodes is returned in document order. The result type is untyped XML.
The value() method extracts a scalar value from an XML instance. It returns the value of the node the XQuery expression evaluates to. This value is converted to a Transact-SQL type specified as the second argument of the value() method.
The exist() method is useful for existential checks on an XML instance. It returns 1 if the XQuery expression evaluates to non-null node list; otherwise it returns 0.
The nodes() method yields instances of a special XML data type, each of which has its context set to a different node that the XQuery expression evaluates to. The special XML data type supports the query(), value(), nodes(), and exist() methods, and can be used in count(*) aggregations and NULL checks. All other uses result in an error.
Imagine a database where you have hundreds of stored procedures, functions, tables & views. It would be cumbersome to search for a text or a in all those database objects. Fortunately there is an easy way to get this information by querying against sysobjects& syscomments. In this article, I would layout sql scripts to solve various requirements.
SQL Server supports two authentication Modes 1) Windows Authentication In case of Windows Authentication, the KERBEROS token assigned during the logon process would be sent to SQL Server when requesting resources. In this case, such kind of accounts must belong to the same domain as the hosting SQL Server. In the Active Directory domain environment, […]
By design a table shouldn’t contain duplicate rows. However sometimes under peculiar cases a table may end up having duplicate rows. In some cases we might have to disable constraints while loading large amounts of data into a table for performance reasons. You woule get errors if you try to enable constraints after loading. This is a common problem in almost all the enterprises, small & big. We could get rid of these rows in most cases. In this article I would discuss some scenarios & solutions.
In order to bulk import xml data from a file, you have to apply SINGLE_BLOB option. It makes sure that the xml parser imports the data as per the encoding scheme specified in the XML declaration.
You could also use table hints such as TABLOCK, you could also use specialized table hints such as IGNORE_CONSTRAINTS, IGNORE_TRIGGERS, KEEPDEFAULTS and KEEPIDENTITY.