Converting DMO to SMO

SQL 2008 no longer supports DMO. Below is some VB.NET Code for creating a database in VB.NET using DMO


Imports SQLDMO

..

Public Function CREATE_DATABASE(ByVal NewDatabaseName As String, ByVal MDFPath As String, ByVal LDFPath As String, ByRef Message As String) As Boolean

REM Create an SQLDMO application
Dim sqlApp As New SQLDMO.ApplicationClass()

REM Create an Server, which resembles to your actual server
Dim srv As New SQLDMO.SQLServerClass()

REM Create Database
Dim nDatabase As New SQLDMO.Database()

REM Create Data Files
Dim nDBFileData As New SQLDMO.DBFile()

REM Create Log Files
Dim nLogFile As New SQLDMO.LogFile()

Try
REM Assign a name to database
nDatabase.Name = NewDatabaseName

REM Assign a name to datafile
nDBFileData.Name = NewDatabaseName
nDBFileData.PhysicalName = MDFPath
nDBFileData.PrimaryFile = True
nDBFileData.Size = 2
nDBFileData.FileGrowthType = SQLDMO.SQLDMO_GROWTH_TYPE.SQLDMOGrowth_MB
nDBFileData.FileGrowth = 1

REM Add the DBFile object
nDatabase.FileGroups.Item("PRIMARY").DBFiles.Add(nDBFileData)
REM Assign name to Log files
nLogFile.Name = NewDatabaseName + "_Log"
nLogFile.PhysicalName = LDFPath
nLogFile.Size = 2
nDatabase.TransactionLog.LogFiles.Add(nLogFile)
srv.LoginSecure = True
srv.Connect(_SourceDatabaseServer)
srv.Databases.Add(nDatabase)
srv.DisConnect()
srv = Nothing
Return True

Catch ex As Exception
Message = ex.Message
Return False
End Try

End Function


To do the same thing in SMO


Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common

...

Public Function CREATE_DATABASE(ByVal NewDatabaseName As String, ByVal MDFPath As String, ByVal LDFPath As String, ByRef Message As String) As Boolean

Try
'Server server = new Server("localhost");
Dim iServer As New Server(_SourceDatabaseServer)

'Database database = new Database(server, "TESTDATABASE");
Dim iDB As New Database(iServer, NewDatabaseName)

'FileGroup fileGroup = new FileGroup(database, "PRIMARY");
Dim iFileGroup As New FileGroup(iDB, "PRIMARY")

'DataFile dataFile = new DataFile(fileGroup, "TESTDTABASE_DATA", "c:
'\\TESTDATABASE_DATA.mdf");
Dim iDataFile As New DataFile(iFileGroup, NewDatabaseName + "_Data", MDFPath)

'dataFile.GrowthType = FileGrowthType.Percent;
'dataFile.Growth = 10;
'dataFile.Size = 4000;
'fileGroup.Files.Add(dataFile);

iFileGroup.Files.Add(iDataFile)
'database.FileGroups.Add(fileGroup);

iDB.FileGroups.Add(iFileGroup)
'LogFile logFile = new LogFile(database, "TESTDATABASE_LOG", "c:
'\\TESTDATABASE_LOG.ldf");
Dim iLogFile As New LogFile(iDB, NewDatabaseName + "_Log", LDFPath)

iLogFile.GrowthType = FileGrowthType.Percent

'logFile.Growth = 10;
iLogFile.Size = 2

'database.LogFiles.Add(logFile);
iDB.LogFiles.Add(iLogFile)

'database.Create(false);
iDB.Create(False)

Return True

Catch ex As Exception
Message = ex.Message
Return False
End Try

End Function

Background Worker Threads in WPF

Here's a nice example how to use background worker threads in WPF

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Shapes;
using System.ComponentModel;
using System.Threading;
namespace MultiCoreProgramming
{
///

/// Interaction logic for BackgroundWorkerThreadInLine.xaml
///

public partial class BackgroundWorkerThreadInLine : Window
{
public BackgroundWorkerThreadInLine()
{
InitializeComponent();
}
private void button1_Click(object sender, RoutedEventArgs e)
{
ValidateAndPersistLayers();
}
private void ValidateAndPersistLayers()
{
this.button1.IsEnabled = false;
var worker = new BackgroundWorker();
worker.DoWork += (doSender, doEventArgs) =>
{
// DO WORK
// BUT if you access dependancy properties you will get the error
// The calling thread cannot access this object because a different thread owns it.
if (true)
{
// Success
doEventArgs.Result = true;
}
//else
//{
// // Failure
// doEventArgs.Result = false;
//}
};
worker.RunWorkerCompleted += (runSender, runEventArgs) =>
{
this.button1.IsEnabled = true;
//this.nameResults.Content = runEventArgs.Result as string;
};
worker.RunWorkerAsync();

}
}
}

Hallo World Asynchronous WCF calls from WPF

Here is how to call the default service in a non blocking way in WPF:


Add the reference
using System.ComponentModel;

Then the code looks like:

namespace AsynchronousNonBlocking
{
public partial class Window1 : Window
{

ServiceReference1.Service1Client test2;

public Window1()
{
InitializeComponent();
test2 = new ServiceReference1.Service1Client();
}

private void btnSynchronous_Click(object sender, RoutedEventArgs e)
{
ServiceReference1.Service1Client test = new ServiceReference1.Service1Client();
test.DoWork();
}

private void btnAsynchronous_Click(object sender, RoutedEventArgs e)
{
test2.DoWorkCompleted += new EventHandler(DoWorkCallback);
test2.DoWorkAsync();
}

public event System.EventHandler DoWorkCompleted;

static void DoWorkCallback(object sender, AsyncCompletedEventArgs e)
{
Console.WriteLine("Done");
}
}
}

Simple TDD WCF oversight

I am a Test Driven Development fanatic and I started using WCF applications.
Development using the built in webserver with VS2008 worked fine but as soon as I deploy the WCF service I get the error:

"Could not find default endpoint element that references contract 'zzzServiceReference.Izzz' in the ServiceModel client configuration section. This might be because no configuration file was found for your application, or because no endpoint element matching this contract could be found in the client element."

I spent quite some time checking the config files. The problem was I was editing the App.Config file in a sub project. What I should have done is create an App.config in the unit test sub project.

Single User Mode

I had an interesting experience setting up a copy of a productive system. Everything was working fine up until I wanted to restore the backup of the database. Then I got an error that the database backup was incomplete. Since it was 1am in the morning I thought now is the time to make sure that we had a backup of the productive system. So I start the backup. After about 20 minutes the backup fails and sets the database into a Single User mode. This can be seen in the Microsoft SQL Management studio next to the database. When selecting the properties of this database I got an error message to the effect that the database was opened by another user. Since it was early in the morning I decided to restart the server and while the server was restarting to lookup more details over the single user mode with the hope that in the time it takes to find out more information the reboot would solve the issue. Well, the server did not restart. Being remote this posed some problems. Apparently the night before one of the led’s on harddisk of this server started to shine orange instead of green and that in the event log there where entries like “Drive Array Physical Status Change… has a new status of 3 … 3=failed”

When the system administrators got back in the office the faulty disk was changed and the RAID was restored. But we still had the single user mode problem. From my search on the internet I found 2 commands:

alter database db-name set SINGLE_USER
alter database db-name set MULTI_USER

So I tried the second of these commands and got the message to the effect that the database was in single user mode and a user is already connected to it. I thought this was a little odd since the database server had just restarted. My next attempt was to use a stored procedure within master to kill all connections to this database and detatch it. This looked like

ALTER PROCEDURE [dbo].[sp_Detach_DB_FORCED]
@DATABASE_NAME as VARCHAR(40)
AS
BEGIN

DECLARE @spid int, @str varchar(1000), @DBname varchar (50)
SET @DBname = @DATABASE_NAME

DECLARE curGetSpids
CURSOR FOR
SELECT p.Spid
FROM master..sysprocesses p, master..sysdatabases d (NOLOCK)
where p.dbid = d.dbid
and d.name = @DBname


OPEN curGetSpids
FETCH NEXT FROM curGetSpids INTO @spid
WHILE(@@FETCH_STATUS<>-1)
BEGIN
SELECT @str='KILL '+CONVERT(varchar(3),@spid)
EXEC (@str)
FETCH NEXT FROM curGetSpids INTO @spid
END
CLOSE curGetSpids
DEALLOCATE curGetSpids

EXEC sp_detach_db @DBname
END

Unfortunately this came back with a similar set of messages. After digging further in the internet I found

Sp_who

This lists the spid of all processes that are connected to databases hosted on the server. It turns out that the database was locked by a select statement of a job that had become frozen. To get rid of this connection

Kill spid

Then the alter database statement worked and we all lived happily ever after :)

Connecting to functions in Oracle

Currently there is no data adapter for Linq to Oracle. This means when connecting to Oracle you need to use ADO.NET or OLDDB. I have been trying to connect to a function that look like

FUNCTION getContract(i_businessID IN tblContract.BusinessID%TYPE,
i_periodFrom IN VARCHAR2,
i_periodTo IN VARCHAR2)
RETURN NUMBER;

The OLEDB Way

Use the following imports:

Imports System.Data.Common
Imports System.Data
Imports System.Data.OracleClient
Imports System.Data.OleDb
Imports System.Data.OracleClient.OracleType

Setup the connection string:

_DBConnStr = "Provider=MSDAORA;Data Source=" + ServerName + ";User Id=" + UserName + ";Password=" + Password + ""

The the function looks like:

Public Function GetContract(ByRef SPInput As sp_GetContractInfo.TableStructure, ByRef Message As String) As Integer

Try

Dim cnOra As New OleDbConnection
cnOra.ConnectionString = _DBConnStr
cnOra.Open()

Dim oCmd As New OleDbCommand

oCmd.CommandText = "{? = call Schema.Package.getContract(?,

Dim o_retValParam As OleDbParameter
o_retValParam = New OleDbParameter("RETVAL", System.Data.OleDb.OleDbType.VarNumeric)
o_retValParam.Direction = ParameterDirection.ReturnValue
o_retValParam.Size = 32
oCmd.Parameters.Add(o_retValParam)


Dim i_businessIDParam As OleDbParameter
i_businessIDParam = New OleDbParameter("i_businessID", System.Data.OleDb.OleDbType.VarChar)
i_businessIDParam.Direction = ParameterDirection.Input
i_businessIDParam.Size = SPInput.i_BusinessId.Length
oCmd.Parameters.Add(i_businessIDParam)
'i_businessID IN 'tblContract.BusinessID%TYPE

Dim i_periodFromParam As OleDbParameter
i_periodFromParam = New OleDbParameter("i_periodFrom", System.Data.OleDb.OleDbType.VarChar)
i_periodFromParam.Direction = ParameterDirection.Input
i_periodFromParam.Size = 19
oCmd.Parameters.Add(i_periodFromParam)
' i_periodFrom IN VARCHAR2,

Dim i_periodToParam As OleDbParameter
i_periodToParam = New OleDbParameter("i_periodTo", System.Data.OleDb.OleDbType.VarChar)
i_periodToParam.Direction = ParameterDirection.Input
i_periodToParam.Size = 19
oCmd.Parameters.Add(i_periodToParam)
' i_periodTo IN VARCHAR2,

Dim i_periodFrom As String = SPInput.i_PeriodFrom.ToString("yyyy-MM-dd") + " 00:00:00"
Dim i_periodTo As String = SPInput.i_PeriodTo.ToString("yyyy-MM-dd") + " 23:59:59"

oCmd.Parameters("i_businessID").Value = SPInput.i_BusinessId
oCmd.Parameters("i_periodFrom").Value = i_periodFrom
oCmd.Parameters("i_periodTo").Value = i_periodTo

oCmd.Connection = cnOra
Dim Result As Integer
oCmd.ExecuteNonQuery()

oCmd.ToString()
Return Result

Catch ex As Exception
Message= ex.ToString
Return -1
End Try
Return True

End Function


The ADO.NET way


Imports System
Imports System.Data.OracleClient
Imports System.Data


_DBConnStr = "Data Source=" + ServerName + ";User ID=" + UserName + ";Password=" + Password + ""

Then the function looks like

Public Function GetContract(ByRef SPInput As sp_GetContractInfo.TableStructure, ByRef Message As String) As Integer

Try

Dim cnOra As New OracleCommand
Dim conOra As New OracleConnection(_DBConnStr)

cnOra.Connection = conOra

cnOra.CommandText = "SchemaName.PackageName.getContract"
cnOra.CommandType = CommandType.StoredProcedure

cnOra.Parameters.Add("RETVAL", OracleType.Number).Direction = ParameterDirection.ReturnValue
cnOra.Parameters.Add("i_businessID", OracleType.VarChar, SPInput.i_BusinessId.Length).Direction = ParameterDirection.Input
cnOra.Parameters.Add("i_periodFrom", OracleType.VarChar, 19).Direction = ParameterDirection.Input
cnOra.Parameters.Add("i_periodTo", OracleType.VarChar, 19).Direction = ParameterDirection.Input

Dim i_periodFrom As String = SPInput.i_PeriodFrom.ToString("yyyy-MM-dd") + " 00:00:00"
Dim i_periodTo As String = SPInput.i_PeriodTo.ToString("yyyy-MM-dd") + " 23:59:59"


cnOra.Parameters("i_businessID").Value = SPInput.i_BusinessId
cnOra.Parameters("i_periodFrom").Value = i_periodFrom
cnOra.Parameters("i_periodTo").Value = i_periodTo

cnOra.Connection.Open()
cnOra.ExecuteNonQuery()


Dim Result As Integer
Result = cnOra.Parameters("RETVAL").Value

Return Result

Catch ex As Exception
Message = ex.Message
Return -1
End Try
Return True

End Function

A problem with out of date table statistics

We had an interesting database by problem We extracted data from an original database into a new database. Then we ran a specific query on both databases.So the query runs over exactly the same data, but on the original database the query took 14 hours to complete and on the new database 20 seconds.

The root cause was out-of-date statistics on the tables/views. I built up a new maintenance plan, including the Rebuild Index task and Update Statistics task and after finishing the maintplan, the same query finished within 16 seconds on the original database.

I also compared the execution plans and found it is the out-of-date statistics which caused SQL Server resulted in wrong cardinality estimation. The problem was that there was a huge difference between the EstimateRows and Rows columns. The estimated rows for the entire view is incorrectly estimated to 1 although there are 84938 rows existing. So SQL Server incorrectly generated the poor nested loop join operator based on this incorrect cardinality estimation. The result is that SQL Server had to run the table scan and was the reason why we saw the “IO_Completion” waittype and unnecessary disk bottleneck.

From this interesting case, we can learn two things:
(1) Frequently update statistics is a best practice to keep SQL Server “thinking” properly.
(2) Even with incorrect/out-of-date statistics, a good database design (correct indexing) could also help avoid the unnecessary overhead caused by the incorrect statistics.

To clear out of date statistics use sp_updatestats. But be carefull executing this on a productive system as it can lead to performance issues as the SQL server has less information to base it’s execution plans on