Sql Server 2008 Standard Edition Installation on Win 2008 R2 Issue

In the process of setting up Project Server 2010 and SharePoint 2010 on Windows Server 2008 R2 faced a strange issue as shown in the following image.

This instillation error occurred even though after verifying the Software and Hardware requirements as well as prerequisites for SQL Server 2008 installation with the initial steps of the installation.



Error indicates that there is a network error while reading the file Sql21D7.tmp.msi. When I checked, this file is not available in the specified path. I even tried to install the Database engine with Full Text Search and Replication and also even tried installing only the Database engine, but still installation failed with the same error.

I verified all the installation documents as well googled for this error with no luck.

Finally, copied and pasted one of the msi file in the installation location (indicated in the error message) and renamed the file to Sql21D7.tmp.msi. After this change we are able to install the SQL Server 2008 successfully.

Right now I am not sure where it will impact, i will post my findings after working on the server for next few days...

Text Parsing in SQL Server

There are lots scenarios in database procedure we may required to parse the test. Most common scenario is to parse the comma separated text.

For example if we want to get the details of few employees, we will send those employee ids separated by a delimiter as a parameter to the stored procedure.

Till SQL Server 2000 we will be parsing the text character by character and then we will split according to the delimiter.

Following is the general split function which we can use in any version of SQL Server:

Function Code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER FUNCTION [dbo].[FNSplitString]
(
-- Add the parameters for the function here
@string nvarchar(4000),
@delimiter nvarchar(10)
)
RETURNS
@StringValueTable TABLE
(
-- Add the column definitions for the TABLE variable here
Pos int IDENTITY(1,1),
StringValue nvarchar(4000)
)
AS
BEGIN
-- Fill the table variable with the rows for your result set
DECLARE @delimiterPos int
DECLARE @StringValue nvarchar(4000)

SET @string = @string + @delimiter

-- Loop thru all the characters
WHILE(LEN(@string)>0)
BEGIN

SET @delimiterPos = CHARINDEX(@delimiter,@string)

SET @StringValue = SUBSTRING(@string,0,@delimiterPos)

-- Insert the record into table variable
INSERT INTO @StringValueTable VALUES (@StringValue)

SET @string = SUBSTRING(@string,@delimiterPos+1,LEN(@string))

-- End the loop if there no more elements
IF @delimiterPos = 0
BEGIN
Break
END

END

RETURN
END

Test Sample:

SELECT * FROM [dbo].[FNSplitString]('a,bc,def,ghij,klmno,pqrstu,vw,x,yz',',')

With the new feature "CTE" in SQL Server 2005 and above we can implement the same very easily without any explicit looping mentioned in the above code.

Following is the split function using CTE:

Function Code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE FUNCTION [dbo].[FNSplitString_New]
(
-- Add the parameters for the function here
@string nvarchar(4000),
@delimiter nvarchar(1)
)
RETURNS
@StringValueTable TABLE
(
-- Add the column definitions for the TABLE variable here
Pos int IDENTITY(1,1),
StringValue nvarchar(4000)
)
AS
BEGIN

Declare @SourceString nvarchar(MAX)

SET @SourceString = ',' + @string + ',';

-- Query using CTE
WITH CTE (Indx, Position) AS
(
SELECT CHARINDEX(@delimiter, @SourceString) Indx, 1 AS Position
UNION ALL
SELECT CHARINDEX(@delimiter, @SourceString, Indx+1) , Position + 1
FROM CTE WHERE CHARINDEX(@delimiter, @SourceString , Indx+1) <> 0
)
INSERT INTO @StringValueTable(StringValue)
SELECT SUBSTRING(@SourceString, B.Indx+1, A.Indx - B.Indx - 1) Val
FROM CTE A INNER JOIN CTE B ON A.Position = B.Position + 1
OPTION (MAXRECURSION 5000)

-- In the above query recursive option is limited to 5000
-- By default it is 100, which means we cannot loop recursively more than 100 times

RETURN
END

Test Sample:

SELECT * FROM [dbo].[FNSplitString_New]('a,bc,def,ghij,klmno,pqrstu,vw,x,yz',',')

In most of our applications we are using first approach as we are reusing from long time. Now we started taking the power of new features like CTE in our regular design and development.

We can also think about passing the 'n' parameter values to a procedure as an xml. And by using XML query capability we can get the individual values as part of the query itself.

SQL Server Table Variables in Transactions

Today in one of the forum I found an intersting question to execute few SQL Statments as not part of the enclosed the transaction.

Let us consider we have a following Scenario:
We have lots of statements participating in the transaction as

Begin Transaction

SQL Inerts Statement 1
Log a message in Log Table
SQL Update Statement 1
Log a message in Log Table
SQL Delete Statement 1

Commit Transaction / Rollback Transaction

In the above block if the transaction is rollbacked then we will be loosing the enteries in Log table too. But if we need the data available in the Log tables without participating in the transaction...

Now the Question is How we can acheive it...?

Immediately after see the question, most of the people thinks about using inner transaction. we have to remember that:

"Committing inner transactions is ignored by the SQL Server Database Engine. The transaction is either committed or rolled back based on the action taken at the end of the outermost transaction. If the outer transaction is committed, the inner nested transactions are also committed. If the outer transaction is rolled back, then all inner transactions are also rolled back, regardless of whether or not the inner transactions were individually committed."

So the Inner/Nested Transaction are ruled out... Now we have to think that is there any way we can get the solution...?

Yes, we can do that by using Local Table Variables. One of the importatnt behaviour of the Local Table Variable will not have any impact of the transaction. Which means irrespective of the Commit/Rollback there will not be any change in the local table variables.

Following the sample demomstrating the same:

-- Create a Sample Table to test
Create Table tblSample(col1 varchar(10))

-- declare log table variable
Declare @Tbl table(Col1 varchar(10))

--
Begin TRansaction

-- Insert Data into table variable
INSERT INTO @Tbl values ('A11')

-- Insert data into actual table
INSERT INTO tblSample
SELECT * FROM
(
Select 'A' Col1
UNION ALL
Select 'B' Col1
) T

-- Insert data into table variable
INSERT INTO @Tbl values ('B11')

rollback Transaction
--commit Transaction

SELECT * from @Tbl
SELECT * from tblSample

Run the above block once with rollback and second time with commit transaction, you will find the same data in the log table @tbl.

Hope this provides some additonal info on local table variables and how we can use in these scenarios.

XML DML INSERT operation in SQL Server

Recently in one of our project the requirement is to insert element within existing XML which is a column(XML Data type) in a table. In other words we have to insert dynamic xml in another xml. I hear the similar requirement in most of the forums...

We can use one of the XML DML operation "INSERT" - Inserts one or more nodes identified by Expression1 as child nodes or siblings of the node identified by Expression2.

Using this Insert operation we can insert only a static nodes as shown below:

-- Declare Local Variables
DECLARE @MainXML XML
DECLARE @NewXML XML

-- Sample XML with employee information
SET @MainXML = '<employees>
<employee>
<fname>John</fname>
<lname>V</lname>
<desig>SSE</desig>
</employee>
<employee>
<fname>Pascal</fname>
<lname>J</lname>
<desig>SE</desig>
</employee>
<employee>
<fname>Naveen</fname>
<lname>M</lname>
<desig>Engineer</desig>
</employee>
</employees>'

-- Inserting static xml
SET @MainXML.modify('insert (<employee><fname>Test</fname></employee>) as last into (/employees)[1]')

-- To verify the reault
select @MainXML

Now if we want to insert an XML which is available within the local variable @NewXML:

-- Assigning the sample xml to the local variable
SET @NewXML = '<employee><fname>Test</fname></employee>'

Now if we try to insert this xml from the local variable using sql:variable:

SET @MainXML.modify(' insert {sql:variable("@NewXML")} as last into (/employees)[1]')

We will get an error message indicating:

XQuery [modify()]: An expression was expected

In SQL Server 2005 the work around will be:

1. Dynamic Query
2. Combination of the XML DML Operations

Here I am not concentrating on the Dynamic Query approach as it is pretty simple to implement and it may not be a best approach when compared to other.

Following set of queries using DML operation, we can insert XML data from a local variable:

-- Appending both the Main XML and New XML to insert into Main XML with a root node
SET @MainXML = '<root>' + CAST(@MainXML AS VARCHAR(MAX)) + '<new>' + CAST(@NewXML AS VARCHAR(MAX)) + '</new>' + '</root>'

-- Moving employee element from new element to main xml as part of employees element
SET @MainXML.modify(' insert (//new/employee) as last into (/employees)[1]')

-- Deleting the new element
SET @MainXML.modify(' delete (//new)')

-- Steps to move employee element to the end and to delete the root element
SET @MainXML.modify(' insert (//root/employees) after (/root)[1] ')
SET @MainXML.modify(' delete (/root)')

-- Selecting the xml for final verification
SELECT @MainXML

In Summary following is the sample Script in SQL Server 2005 to insert a dynamic XML into another XML:

DECLARE @MainXML XML
DECLARE @NewXML XML


SET @MainXML = '<employees>
<employee>
<fname>John</fname>
<lname>V</lname>
<desig>SSE</desig>
</employee>
<employee>
<fname>Pascal</fname>
<lname>J</lname>
<desig>SE</desig>
</employee>
<employee>
<fname>Naveen</fname>
<lname>M</lname>
<desig>Engineer</desig>
</employee>
</employees>'

SET @NewXML = '<employee><fname>Test</fname></employee>'

SET @MainXML = '<root>' + CAST(@MainXML AS VARCHAR(MAX)) + '<new>' + CAST(@NewXML AS VARCHAR(MAX)) + '</new>' + '</root>'

SET @MainXML.modify(' insert (//new/employee) as last into (/employees)[1]')

SET @MainXML.modify(' delete (//new)')

SET @MainXML.modify(' insert (//root/employees) after (/root)[1] ')
SET @MainXML.modify(' delete (/root)')

SELECT @MainXML

These many steps are not required for SQL Server 2008 as it supports to insert the dynamic XML directly into another XML. Following is the sample script for SQL Server 2008:

DECLARE @MainXML XML
DECLARE @NewXML XML


SET @MainXML = '<employees>
<employee>
<fname>John</fname>
<lname>V</lname>
<desig>SSE</desig>
</employee>
<employee>
<fname>Pascal</fname>
<lname>J</lname>
<desig>SE</desig>
</employee>
<employee>
<fname>Naveen</fname>
<lname>M</lname>
<desig>Engineer</desig>
</employee>
</employees>'


SET @NewXML = '<employee><fname>Test</fname></employee>'

-- One statement in SQL Server 2008 to do all the operation what we mentioned above for SQL Server 2005
SET @MainXML.modify(' insert {sql:variable("@NewXML")} as last into (/employees)[1]')

select @MainXML

This we implemented in most of our applications, hope it will be useful to most of the folks...

SQL Server Database Backups using SMO in C#

In the previous post we looked at the What is SMO and where we can use SMO. Now we will see how we can use SMO for taking SQL Server Database backups in C# code.

The first step in our code is to estblish the connection to the database. In our regular .net applications we will use SqlConnection for connecting to the database server. In SMO APIs we will be using the class called "Server" as shown below:

Microsoft.SqlServer.Management.Smo.Server objSrv = new Server("Database Server Name");

if we are planning to connect to any particular instance, we can even pass the instance name as part of the database server name as:

Microsoft.SqlServer.Management.Smo.Server objSrv = new Server("Database Server Name\\Instance Name");

With this line we will be establishing the connectivity to the database server. To get any information about the server, we can use this object. For example to get the SQL Server edition like Enterprise/Standard/Developer edition, we can use Information property within the Server object as shown below:

String sEdition = objSrv.Information.Edition;

Using this Information class we can get lots of information about the SQL Server like OSVersion, Collation, CaseSensitive, Clustered, Physical Memory and Version etc.

Next step for us is to create the instance of the Backup class which is core part for taking the backup of the database:

Microsoft.SqlServer.Management.Smo.Backup objBkpUp = new Backup();

There is should some option to provide details like Database Name, Backup Options (Database/Log File/Data File). The option is nothing but the Backup class as shown below:

objBkpUp.Action = BackupActionType.Database;
objBkpUp.Database = "MyDataBase";
objBkpUp.MediaName = "FileSystem";

And also we have to provide the info about the Device (File/Tape/Pipe) for taking the backup. For this we will be using the BackupDeviceItem class.

BackupDeviceItem objBkpDeviceItem = new BackupDeviceItem();
objBkpDeviceItem.DeviceType = DeviceType.File;
objBkpDeviceItem.Name = "C:\\filename.bak";

Once device is created we have to add it to the Backup instance as:

objBkpUp.Devices.Add(objBkpDeviceItem);

Finally we are ready to initiate a call for the backup operation by passing the Server instance:

objBkpUp.Initialize = true;
objBkpUp.SqlBackup(objSrv);

Here is the complete code:

Microsoft.SqlServer.Management.Smo.Backup objBkpUp = new Backup();
Microsoft.SqlServer.Management.Smo.Server objSrv = new Server("DBName");


Console.WriteLine("Server Edition: " + objSrv.Information.Edition);

objBkpUp.Action = BackupActionType.Database;
objBkpUp.Database = "MyDataBase";
objBkpUp.MediaName = "FileSystem";

BackupDeviceItem objBkpDeviceItem = new BackupDeviceItem();
objBkpDeviceItem.DeviceType = DeviceType.File;
objBkpDeviceItem.Name = "h:\\zzTest.bak";

objBkpUp.Devices.Add(objBkpDeviceItem);

objBkpUp.Initialize = true;
objBkpUp.SqlBackup(objSrv);

Please note that we have to add the following assembly references:

Microsoft.SqlServer.Smo
Microsoft.SqlServer.ConnectionInfo

and in the code we have to refer following namespace:

using Microsoft.SqlServer.Management.Smo;

What is SMO?

Are we wondering what is this SMO...?

Recently one of our team member asked me a question on who we can write a C# code for taking a SQL Server Database Bakup without using "Backup Database" sql command which made me to write this post.

SMO is Sql Server Management Objects providing a set of APIs for managing SQL Server.
In a short note:

"It is a collection of objects that are designed for programming all the aspects of Managing the Microsoft SQL Server"

And also we can say that SMO is a replacement of SQL-DMO(SQL Server Distributed Management Objects). SMO design goal is to mainly improve Scalability and Performance when compared to the SQL-DMO which is available with SQL Server 2000. SMO is relaced with SQL Server 2005 and even it supports SQL Server 2000 as well.

We can use SMO to perform any of the following administrative operations on the SQL Server:

Create databases

Perform backups

Create jobs

Configure SQL Server

Assign permissions

and to perform many other administrative tasks.

Following are the set of Assemblies available for the programming:

Microsoft.SqlServer.management

Microsoft.SqlServer.Management.NotificationServices

Microsoft.SqlServer.Management.Smo

Microsoft.SqlServer.Management.Smo.Agent

Microsoft.SqlServer.Management.Smo.Broker

Microsoft.SqlServer.Management.Smo.Mail

Microsoft.SqlServer.Management.Smo.RegisteredServers

Microsoft.SqlServer.Management.Smo.Wmi

Microsoft.SqlServer.Management.Trace namespaces

In the comming post I will explain few set of Objects and how can we write simple program in C# to backup a particular database using SMO APIs.

How to get deep nested elements in SQL Queries using FOR XML PATH

Recently in one of the forums I came across this question.

How can we have deep nested elements in the xml when we are using FOR XML PATH in a SQL Query.

For example the following query

SELECT 'value' AS 'orderdatabase/allorders/customer/information/orderaddress/shippingaddress/address1/home/address1/blahblahblahblahblahblahblahblah'
FOR XML PATH('')

will give an error indicating that the identifier is too long and it should not exceed 128 characters. But the output expected from the query is:

<root>
 <orderdatabase>
  <allorders>
   <customer>
    <information>
     <orderaddress>
      <shippingaddress>
       <address1>
        <home>
         <address1>
          <blahblahblahblahblahblahblahblah>value</blahblahblahblahblahblahblahblah>
         </address1>
        </home>
       </address1>
      </shippingaddress>
     </orderaddress>
    </information>
   </customer>
  </allorders>
 </orderdatabase>
</root>

In most of the scenarios in our real world code this might be required. We can resolve this with a workaround using sub queries which will return an xml which is subset of the parent xml. Following is the sample query to resolve the above issue:

SELECT '' 'orderdatabase/allorders/customer/information/orderaddress',
(
SELECT 'value' 'shippingaddress/address1/home/address1/blahblahblahblahblahblahblahblah'
FOR XML PATH(''), type
) AS 'orderdatabase/allorders/customer/information/orderaddress'
FOR XML PATH(''),root('root')

We can refer any of the sql table columns using {sql:column("column name")}.

This is a simple tip/work around for long nested xml's from a query.