Archive for the ‘Uncategorized’ Category
Saturday, January 5th, 2008
Find row count of one or more tables
The following code can be used to retrieve the rowcount of each tables in the database. Let us create a function (TABLE VALUED) which returns the row count of all the tables.
CREATE FUNCTION dbo.GetRowCount()
RETURNS TABLE
RETURN
SELECT
st.Name,
...
Posted in Uncategorized | Comments Off
Saturday, January 5th, 2008
I had been creating some DTDs for a book that I am currently writing. The book is not about DTD. The title of the book is "XSD For SQL Server 2005 Developers" and it aims at helping SQL Server 2005 developers to make effective use of XML ...
Posted in Uncategorized | Comments Off
Saturday, January 5th, 2008
Prior to SQL Server 2005, it was hard dealing with large string values. NVARCHAR and VARCHAR data types had a limitation of 8000 bytes (VARCHAR(8000) and NVARCHAR(4000)). To store large values, most people used TEXT columns and others broke the value into multiple rows. Dealing with ...
Posted in Uncategorized | Comments Off
Saturday, January 5th, 2008
Application roles are set of permissions that you can group together for a specific application. When a user connects to the database through the specific application, he or she will be able to perform the actions permitted under the specific application role.
I have not used application ...
Posted in Uncategorized | Comments Off
Saturday, January 5th, 2008
Report definitions [RDL files] are stored in the "catalog" table of ReportServer database. This table has a field "content" which stores the report definition as an image/text value.
The following query will extract a list of all reports and the stored procedures used by them, by querying ...
Posted in Uncategorized | Comments Off
Friday, January 4th, 2008
If you are using SQL Server 2008 November CTP, you can insert one XML variable into anther using the XQuery insert operator. Here is an example:
-- declare an XML variable
DECLARE @x XML
SET @x = '<Root></Root>'
-- create another XML variable
DECLARE @t XML
SELECT @t = (SELECT TOP 3 name FROM ...
Posted in Uncategorized | 1 Comment »
Wednesday, December 5th, 2007
Part 1
Part 2
Having fixed the problem with the sort order, let us go ahead with the rest of the code. Let us add Addresses under the AddressCollection node and come up with the final version of the code. We need to add a new level, Tag 4. Note that I ...
Posted in Uncategorized | Comments Off
Wednesday, December 5th, 2007
I am pretty sure that parsing a delimited string is one of the basic string operation that all of us do. Any programmer must have used it at least once in his or her programming career. Based on the programming language he or she uses, there might ...
Posted in Uncategorized | Comments Off
Wednesday, December 5th, 2007
One of the readers at SQLServerCentral forum asked me about passing a DataTable to a stored procedure. He wanted to get an XML representation of the DataTable and then pass it to the stored procedure. I tried to answer the question and came up with a complete ...
Posted in Uncategorized | Comments Off
Wednesday, December 5th, 2007
Continued from Part 1
Let us move ahead. Under each agent, we need a node named AddressCollection. Let us add the code for that.
SELECT
1 AS Tag,
NULL AS Parent,
NULL AS 'Agents!1!',
NULL AS 'Agent!2!AgentID',
NULL AS 'Agent!2!Fname!Element',
NULL AS 'Agent!2!SSN!Element',
NULL AS 'AddressCollection!3!Element'
UNION ALL
SELECT
2 AS Tag, 1 AS Parent,
NULL, AgentID, Fname, SSN,
NULL
FROM @agent
UNION ALL
SELECT
3 AS Tag, ...
Posted in Uncategorized | Comments Off