Transaction Error on Sql Server with instance name.

  • Thread starter Thread starter apiringmvp
  • Start date Start date
A

apiringmvp

Everyone,

I am trying to load a bunch of records into a Sql Server 2005 Database
using (TransactionScope ts = new TransactionScope()). I keep getting
an error message saying MSDTC on server 'SOHO/SQLEXPRESS' is
unavailable.

my SOHO server is a Win2K3 Server with SQL Server 2000 using the
default instance of SQL Server and SqlServer 2005 using the
SOHO/SQLEXPRESS instance.

I downloaded DTCPing from
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q306843 and
everything works successfully when connecting to the SOHO DTC, but I
get an error when connecting to SOHO\SQLEXPRESS, because it is not a
valid NetBios Name. See Logs Below:

I am using .NET 2.0 and SqlServer 2005 with C#.NET in a winform app. I
am updating the database using the VS Configured DataSets. That link
to SQL SERVER SPs that I created for Save(). My code for the
transaction is also attached at the very bottom

Is there anyway to set the servername for a transaction? is there a
connection string in .Net 2.0 that allows my to seperate the ServerName
and the InstanceName? I am still trying to figure out
System.Transactions in 2.0, so thanks in advanced.


:::::LOGS::::

++++++++++++++++++++++++++++++++++++++++++++++
DTCping 1.9 Report for BD******01 (TO SOHO)
++++++++++++++++++++++++++++++++++++++++++++++
RPC server is ready
++++++++++++Validating Remote Computer Name++++++++++++
12-19, 11:19:46.861-->Start DTC connection test
Name Resolution:
soho-->10.10.1.10-->sci****.com
12-19, 11:19:46.876-->Start RPC test (BDEWEY01-->soho)
RPC test is successful
Partner's CID:F9194B16-4613-4A36-BCF6-466B535B13A2
++++++++++++RPC test completed+++++++++++++++


++++++++++++++++++++++++++++++++++++++++++++++
DTCping 1.9 Report for BDE******01 ( to SOHO\SQLEXPRESS)
++++++++++++++++++++++++++++++++++++++++++++++
RPC server is ready
++++++++++++Validating Remote Computer Name++++++++++++
12-19, 11:22:13.408-->Start DTC connection test
gethostbyname can not resolve soho\sqlexpress
Error(0xB7) at nameping.cpp @43
-->gethostbyname failure
-->183(Cannot create a file when that file already exists.)
Can not resolve soho\sqlexpress
Invalid remote host name:soho\sqlexpress



----------------------------------------------------------------------------------------
:::: CODE ::::::


try
{
if (_pres == null)
throw new InvalidOperationException("There is no
presentation loaded at this time.");
if (_pres.Slides.Count == 0)
throw new Exception("Unable to import 0 slides");

if (File.Exists(this.destination.Text))
{
throw new Exception("A Presentation with that name
already exists please delete the existing presentation or choose a new
name");
this.destination.Focus();
}

List<IImportTask> tasks = new List<IImportTask>();

// Copy Presentation
tasks.Add(new MoveFilesTask(this.filename.Text,
this.destination.Text, "Moving Presentation File"));

// Create Thumbnails
List<RunFunctionTask.FunctionToCall> funcs = new
List<RunFunctionTask.FunctionToCall>();
foreach (Slide s in _pres.Slides)
funcs.Add(s.GenereateThumbNail);

tasks.Add(new RunFunctionTask(funcs, "Generating
Thumbnail Images of the slides"));

// Copy Images
List<string> images = new List<string>();
foreach (Slide s in _pres.Slides)
images.Add(Path.GetFileName(s.ImageFilename));

tasks.Add(new
MoveFilesTask(Path.GetDirectoryName(_pres.Slides[0].ImageFilename),
this.destination.Text + "-slides", images, "Moving Slide Images"));

// Copy Thumbnails
List<MoveFilesTask.GetFileName> thumbs = new
List<MoveFilesTask.GetFileName>();
foreach (Slide s in _pres.Slides)
thumbs.Add(s.GetThumbnailImageFilename);

tasks.Add(new
MoveFilesTask(Path.GetDirectoryName(_pres.Slides[0].ImageFilename),
this.destination.Text + "-slides", thumbs, "Moving Slide Thumbnails"));

int i=0;
try
{
for (i = 0; i < tasks.Count; i++)
{
Main_Progress(0, tasks.Count+1, i,
tasks.Name);
tasks.Progress += new
ProgressEventHandler(Task_Progress);
tasks.Run();
}


Main_Progress(0, tasks.Count + 1, tasks.Count,
"Saving Presentation to Database");

using (System.Transactions.TransactionScope ts =
new System.Transactions.TransactionScope())
{
int curDbOperation = 0;
int totalDbOperations = 1 + _pres.Slides.Count;
foreach (Slide s in _pres.Slides)
totalDbOperations += s.References.Count;


Task_Progress(this, new ProgressEventArgs(0,
totalDbOperations, curDbOperation++, "Saving Talk"));

int? talkId=0;
using (talksTableAdapter talkAdapter = new
talksTableAdapter())
{
talkId = talkAdapter.Save(0,
this.destination.Text, this.title.Text,
(int)this.topics.SelectedValue);
}

using (slidesTableAdapter slideAdapter = new
slidesTableAdapter())
{
foreach(Slide s in _pres.Slides)
{
Task_Progress(this, new
ProgressEventArgs(0, totalDbOperations, curDbOperation++, "Saving Slide
#" + s.SlideIndex));

// Insert Slides
int? slideId = slideAdapter.Save(0,
talkId,
s.SlideIndex,

Path.GetFileName(this.destination.Text) + "-slides/" +
Path.GetFileName(s.ThumbnailImageFilename),

Path.GetFileName(this.destination.Text) + "-slides/" +
Path.GetFileName(s.ImageFilename),
s.Title,
s.Notes,
s.Content,
s.Keywords);
}
}

ts.Complete();
}
}
catch(Exception ex)
{
for (i=i-1; i >= 0; i--)
{
Main_Progress(0, tasks.Count + 1, i, "Rolling
Back - " + tasks.Name);
tasks.Rollback();
}

MessageBox.Show("Rolled Back because " + ex.Message
+ (ex.InnerException==null?"":", " + ex.InnerException.Message));
}


}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
 
Did you start the SqlBrowser service? Did you enable the appropriate
protocols on the SQLEXPRESS instance?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

apiringmvp said:
Everyone,

I am trying to load a bunch of records into a Sql Server 2005 Database
using (TransactionScope ts = new TransactionScope()). I keep getting
an error message saying MSDTC on server 'SOHO/SQLEXPRESS' is
unavailable.

my SOHO server is a Win2K3 Server with SQL Server 2000 using the
default instance of SQL Server and SqlServer 2005 using the
SOHO/SQLEXPRESS instance.

I downloaded DTCPing from
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q306843 and
everything works successfully when connecting to the SOHO DTC, but I
get an error when connecting to SOHO\SQLEXPRESS, because it is not a
valid NetBios Name. See Logs Below:

I am using .NET 2.0 and SqlServer 2005 with C#.NET in a winform app. I
am updating the database using the VS Configured DataSets. That link
to SQL SERVER SPs that I created for Save(). My code for the
transaction is also attached at the very bottom

Is there anyway to set the servername for a transaction? is there a
connection string in .Net 2.0 that allows my to seperate the ServerName
and the InstanceName? I am still trying to figure out
System.Transactions in 2.0, so thanks in advanced.


:::::LOGS::::

++++++++++++++++++++++++++++++++++++++++++++++
DTCping 1.9 Report for BD******01 (TO SOHO)
++++++++++++++++++++++++++++++++++++++++++++++
RPC server is ready
++++++++++++Validating Remote Computer Name++++++++++++
12-19, 11:19:46.861-->Start DTC connection test
Name Resolution:
soho-->10.10.1.10-->sci****.com
12-19, 11:19:46.876-->Start RPC test (BDEWEY01-->soho)
RPC test is successful
Partner's CID:F9194B16-4613-4A36-BCF6-466B535B13A2
++++++++++++RPC test completed+++++++++++++++


++++++++++++++++++++++++++++++++++++++++++++++
DTCping 1.9 Report for BDE******01 ( to SOHO\SQLEXPRESS)
++++++++++++++++++++++++++++++++++++++++++++++
RPC server is ready
++++++++++++Validating Remote Computer Name++++++++++++
12-19, 11:22:13.408-->Start DTC connection test
gethostbyname can not resolve soho\sqlexpress
Error(0xB7) at nameping.cpp @43
-->gethostbyname failure
-->183(Cannot create a file when that file already exists.)
Can not resolve soho\sqlexpress
Invalid remote host name:soho\sqlexpress



----------------------------------------------------------------------------------------
:::: CODE ::::::


try
{
if (_pres == null)
throw new InvalidOperationException("There is no
presentation loaded at this time.");
if (_pres.Slides.Count == 0)
throw new Exception("Unable to import 0 slides");

if (File.Exists(this.destination.Text))
{
throw new Exception("A Presentation with that name
already exists please delete the existing presentation or choose a new
name");
this.destination.Focus();
}

List<IImportTask> tasks = new List<IImportTask>();

// Copy Presentation
tasks.Add(new MoveFilesTask(this.filename.Text,
this.destination.Text, "Moving Presentation File"));

// Create Thumbnails
List<RunFunctionTask.FunctionToCall> funcs = new
List<RunFunctionTask.FunctionToCall>();
foreach (Slide s in _pres.Slides)
funcs.Add(s.GenereateThumbNail);

tasks.Add(new RunFunctionTask(funcs, "Generating
Thumbnail Images of the slides"));

// Copy Images
List<string> images = new List<string>();
foreach (Slide s in _pres.Slides)
images.Add(Path.GetFileName(s.ImageFilename));

tasks.Add(new
MoveFilesTask(Path.GetDirectoryName(_pres.Slides[0].ImageFilename),
this.destination.Text + "-slides", images, "Moving Slide Images"));

// Copy Thumbnails
List<MoveFilesTask.GetFileName> thumbs = new
List<MoveFilesTask.GetFileName>();
foreach (Slide s in _pres.Slides)
thumbs.Add(s.GetThumbnailImageFilename);

tasks.Add(new
MoveFilesTask(Path.GetDirectoryName(_pres.Slides[0].ImageFilename),
this.destination.Text + "-slides", thumbs, "Moving Slide Thumbnails"));

int i=0;
try
{
for (i = 0; i < tasks.Count; i++)
{
Main_Progress(0, tasks.Count+1, i,
tasks.Name);
tasks.Progress += new
ProgressEventHandler(Task_Progress);
tasks.Run();
}


Main_Progress(0, tasks.Count + 1, tasks.Count,
"Saving Presentation to Database");

using (System.Transactions.TransactionScope ts =
new System.Transactions.TransactionScope())
{
int curDbOperation = 0;
int totalDbOperations = 1 + _pres.Slides.Count;
foreach (Slide s in _pres.Slides)
totalDbOperations += s.References.Count;


Task_Progress(this, new ProgressEventArgs(0,
totalDbOperations, curDbOperation++, "Saving Talk"));

int? talkId=0;
using (talksTableAdapter talkAdapter = new
talksTableAdapter())
{
talkId = talkAdapter.Save(0,
this.destination.Text, this.title.Text,
(int)this.topics.SelectedValue);
}

using (slidesTableAdapter slideAdapter = new
slidesTableAdapter())
{
foreach(Slide s in _pres.Slides)
{
Task_Progress(this, new
ProgressEventArgs(0, totalDbOperations, curDbOperation++, "Saving Slide
#" + s.SlideIndex));

// Insert Slides
int? slideId = slideAdapter.Save(0,
talkId,
s.SlideIndex,

Path.GetFileName(this.destination.Text) + "-slides/" +
Path.GetFileName(s.ThumbnailImageFilename),

Path.GetFileName(this.destination.Text) + "-slides/" +
Path.GetFileName(s.ImageFilename),
s.Title,
s.Notes,
s.Content,
s.Keywords);
}
}

ts.Complete();
}
}
catch(Exception ex)
{
for (i=i-1; i >= 0; i--)
{
Main_Progress(0, tasks.Count + 1, i, "Rolling
Back - " + tasks.Name);
tasks.Rollback();
}

MessageBox.Show("Rolled Back because " + ex.Message
+ (ex.InnerException==null?"":", " + ex.InnerException.Message));
}


}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
Click to expand...
 
SqlBrowser is running...
and
Protocols for SQLEXPRESS:
Shared Memory Enabled
Named Pipes Enabled
TCP/IP Disabled
VIA Disabled
 
Bill,

I noticed that you were the only person to respond to my question about the
Transactions. Could you shed any light on how I can get the TransactionScope
to work on the right server.

I could be going about this the wrong way, I am attempting to load
powerpoint documents via a Web Form into a SQL Server 2005 Database that
will be used on a website. The reason I am wraping this task in a
Transaction is because I need to insert the Talk, get the id and then insert
the slides with the new talk id, if It fails I want to rollback the Talk

Is there something I should use besides the TransactionScope?
 
Without a deeper understanding of your application I would not be able to
offer any meaningful advice. Generally, I try to answer questions where the
problem is clearly addressed by the suggestion.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Maybe I can rephrase it.

What is the best way to insert a parent row in a specific table say Talks
and then insert n Rows into a child table say Slides. If one of the slides
fails I want the all previous Slides and the Talk row to be deleted.

I would like to use a transaction, but I am new to the System.Transactions
in .NET 2.0.

This App is a C# WinForm using .NET 2.0 and Sql Server 2005 Enterprise.

1. Should i use System.Transactions for this? or should I go with Conn =
new Connection(), Trans = Conn.BeginTransaction()?

2. Is there a certian Type of Transaction that I should use. It this a
CommitableTransaction?
 
I would create a single batch that includes a BEGIN TRANSACTION followed by
all of the INSERT statements followed by a COMMIT TRANSACTION. Yes, you
could create an ADO.NET Transaction and use the DataAdapter Update method
(twice) and commit the Transaction. I would check out Pro ADO.NET 2.0 Sahil
Malik. He has a good section on ADO.NET transactions.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Back
Top