Do I need to delete sp_replincrementlsn?

Hi,
maybe someone have suffered this before.
I'm able to make in the server a publication (W2K ADV. with SQL Server 2000
Standar and SP2). I can't upgrade the SP4 now.
Then I'm trying to make in my computer a suscription (WXP with SQL Server
developer and SP4) .
The error is the "14053: Can't load the DLL replincrementlsn, etc." when I
search the stored procedure sp_replincrementlsn I can find it in the master
database in the stored procedures (not in extended stored procedures) so if
I try to register the xprepl.dll I can't because the procedure already
exists.
What can I do? Can I delete a stored procedure of the master database? How?
Thank's in advance!!!
Un saludo,
Enrique Barcel
I think you need to reapply the current sp. Have you tried a reboot? I have
run into resource problems like this that were solved on reboot.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Enrique Barcel" <ebarceloSINSPAM@.EnerWeb.es> wrote in message
news:%23cpZLPyeFHA.1400@.TK2MSFTNGP15.phx.gbl...
> Hi,
> maybe someone have suffered this before.
> I'm able to make in the server a publication (W2K ADV. with SQL Server
> 2000
> Standar and SP2). I can't upgrade the SP4 now.
> Then I'm trying to make in my computer a suscription (WXP with SQL Server
> developer and SP4) .
> The error is the "14053: Can't load the DLL replincrementlsn, etc." when I
> search the stored procedure sp_replincrementlsn I can find it in the
> master
> database in the stored procedures (not in extended stored procedures) so
> if
> I try to register the xprepl.dll I can't because the procedure already
> exists.
> What can I do? Can I delete a stored procedure of the master database?
> How?
> Thank's in advance!!!
> Un saludo,
> Enrique Barcel
>
>
sql

Distribution of SQL Server 2005 DB with Full-Text Search

Using SQL Server 2005 Express (Advanced SP2) I have created a Full-Text Search application in VB for distribution on CD for single PCs. Works fine on my local machine during development.

Although the SQL Server 2005 Express edition can be distributed freely, it does not seem to support Full-Text searches in the distributed version. Is this true? Or am I missing something with my deployment?

If I need another version of Sql Server for distribution of a Full-Text Search app, how do I go about obtaining the proper DB and permission for distribution? The DB size is about 600 MB.

Oh yes, it does support those. YOu will have to install the fulltext search service with the setup and it should work then. If you are installing it using unattended setup file, make sure that you include the feature of fulltext search. There is a sample within the template file which shows how to do it.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi Jens,

Great to hear that it does support full text search when distributed.

I have installed Sql Server Express 2005 (Adv. SP2) on three machines with full text search enabled during setup. So I think I am getting that part OK.I am not sure I know what you mean by, “There is a sample within the template file which shows how to do it.”

I am able to do full text searches in Management Studio but have never been able to do full-text searches in Visual Basic Express due to the limitation associated with user instance (“Cannot use full-text search in user instance.”)

I have been trying out Visual Studio 2008 Beta 2 and am able to do full-text search in Visual Basic when connecting directly to the DB attached to the server instance.But still get the same error when adding the Pubs DB to the Solution within VB that relies on the User Instance.

Here is a simple app that demonstrates the problem.The full-text search works with the connection to the server instance but not through the User Instance within VB.

Code Snippet

Imports System.Data

Imports System.Data.SqlClient

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim strSearch

strSearch = TextBox1.Text

'Added to Solution as existing item and User Instance – does not work

'Dim conn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\PUBS.MDF;Integrated Security=True;User Instance=True")

'Connected through Sql Server Express and this works!

Dim conn As New SqlConnection("Data Source=OFFICE\SQLEXPRESS;Initial Catalog=Pubs;Integrated Security=True")

Dim ds As New DataSet

conn.Open()

Dim adp As New SqlDataAdapter("Select * From Titles WHERE CONTAINS(Notes, ' """ & strSearch & """ ')", conn)

adp.Fill(ds)

DataGridView1.DataSource = ds.Tables(0)

conn.Close()

End Sub

End Class

I guess if I could distribute the program so that it does not rely on the User Instance during runtime, it would work.Just don’t know how to do that.

So I think I am close to getting the distribution issue resolved, but just don't know what to try next. Thanks for any help you can provide in working this out.

|||

OK, you did not mention that you are using user instances, this feature is NOT supported on a user instance as the error message tells you.

http://msdn2.microsoft.com/en-us/library/ms143684.aspx

|||

Thanks for the info - a bit discouraging but not entirely unexpected. I have been wrestling with this issue for awhile and had basically given up on doing full text search outside Management Studio. Then with the VS 2008 beta I have the option of connecting directly to the server and doing the FTS in Visual Basic.

So I am back to my original question of how to distribute a FTS program using SQL Server. Do I need another version of SQL Server that supports FTS in User Instances? Or some variation on this theme?

Or, is there a way to use SQL Server 2005 Express (Adv SP2) with VB in a way other than with User Instances?

The DB is a static, historical, read only archive so security is not an issue for this application.

I am assuming there must be a way to create a FTS program with SQL Server for distribution on a CD or DVD. If SQL Server Express will not do it, what other options do I have? Or is the problem more a matter of the limitation of User Instances within Visual Basic itself?

Thanks again for the info and any further guidance you can provide.

|||

Hi,

"So I am back to my original question of how to distribute a FTS program using SQL Server. Do I need another version of SQL Server that supports FTS in User Instances? Or some variation on this theme?"

No, the only version which supports user instances is Express. YOu probably will need a variation of the planned envrionment.

"

Or, is there a way to use SQL Server 2005 Express (Adv SP2) with VB in a way other than with User Instances?

The DB is a static, historical, read only archive so security is not an issue for this application.

"

You will need server instances. Install SQL Server Express on the computer and attach the database to the server instance, instead of just attaching it via a user instance. This can be either done using the sp_attachdb command or the Management GUI.|||

I believe that I have attached the DB to the server instance of SQL Server Express using the Management GUI. Otherwise I would not be able to do FTS in Managment Studio. I also think that is why I am able to do FTS on the DB within Visual Studio as attached to the server rather than through a User Instance which does not support FTS.

I am not clear on what happens when I deploy the application including SQL Server 2005 Express. When I have done it before I was able to do regular DB activities, but got he error message about not supporting FTS only when I tried to do the FTS. So I am able to deploy the DB and use it, but it must be in a User Instance which apparently will never support FTS.

Can SQL Server Express be deployed in a way that the distributed version does not have to rely on a User Instance? If not, I don't see how the Express version will ever be usable for FTS in a distributed application.

Thanks for your support in helping me sort this out.

|||

I have been trying to deploy the application with the DB attached to a Server Instance rather than User Instance. No luck yet. I have no problem running the FTS on a Server Instance in VB during development. So it seems to be a matter of getting it deployed properly.

Just so that I am clear as to what I am trying to do, am I correct in assuming that I can deploy Sql Server 2005 Express (Advanced SP2) with my Visual Basic app and it will support Full Text Search so long as the DB is attached to a server instance rather than user instance? Thus I just need to learn how to access the DB with a server instance in the distributed program. Do I understand you correctly on this?

Distribution of SQL Server 2005 DB with Full-Text Search

Using SQL Server 2005 Express (Advanced SP2) I have created a Full-Text Search application in VB for distribution on CD for single PCs. Works fine on my local machine during development.

Although the SQL Server 2005 Express edition can be distributed freely, it does not seem to support Full-Text searches in the distributed version. Is this true? Or am I missing something with my deployment?

If I need another version of Sql Server for distribution of a Full-Text Search app, how do I go about obtaining the proper DB and permission for distribution? The DB size is about 600 MB.

Oh yes, it does support those. YOu will have to install the fulltext search service with the setup and it should work then. If you are installing it using unattended setup file, make sure that you include the feature of fulltext search. There is a sample within the template file which shows how to do it.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi Jens,

Great to hear that it does support full text search when distributed.

I have installed Sql Server Express 2005 (Adv. SP2) on three machines with full text search enabled during setup. So I think I am getting that part OK.I am not sure I know what you mean by, “There is a sample within the template file which shows how to do it.”

I am able to do full text searches in Management Studio but have never been able to do full-text searches in Visual Basic Express due to the limitation associated with user instance (“Cannot use full-text search in user instance.”)

I have been trying out Visual Studio 2008 Beta 2 and am able to do full-text search in Visual Basic when connecting directly to the DB attached to the server instance.But still get the same error when adding the Pubs DB to the Solution within VB that relies on the User Instance.

Here is a simple app that demonstrates the problem.The full-text search works with the connection to the server instance but not through the User Instance within VB.

Code Snippet

Imports System.Data

Imports System.Data.SqlClient

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim strSearch

strSearch = TextBox1.Text

'Added to Solution as existing item and User Instance – does not work

'Dim conn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\PUBS.MDF;Integrated Security=True;User Instance=True")

'Connected through Sql Server Express and this works!

Dim conn As New SqlConnection("Data Source=OFFICE\SQLEXPRESS;Initial Catalog=Pubs;Integrated Security=True")

Dim ds As New DataSet

conn.Open()

Dim adp As New SqlDataAdapter("Select * From Titles WHERE CONTAINS(Notes, ' """ & strSearch & """ ')", conn)

adp.Fill(ds)

DataGridView1.DataSource = ds.Tables(0)

conn.Close()

End Sub

End Class

I guess if I could distribute the program so that it does not rely on the User Instance during runtime, it would work.Just don’t know how to do that.

So I think I am close to getting the distribution issue resolved, but just don't know what to try next. Thanks for any help you can provide in working this out.

|||

OK, you did not mention that you are using user instances, this feature is NOT supported on a user instance as the error message tells you.

http://msdn2.microsoft.com/en-us/library/ms143684.aspx

|||

Thanks for the info - a bit discouraging but not entirely unexpected. I have been wrestling with this issue for awhile and had basically given up on doing full text search outside Management Studio. Then with the VS 2008 beta I have the option of connecting directly to the server and doing the FTS in Visual Basic.

So I am back to my original question of how to distribute a FTS program using SQL Server. Do I need another version of SQL Server that supports FTS in User Instances? Or some variation on this theme?

Or, is there a way to use SQL Server 2005 Express (Adv SP2) with VB in a way other than with User Instances?

The DB is a static, historical, read only archive so security is not an issue for this application.

I am assuming there must be a way to create a FTS program with SQL Server for distribution on a CD or DVD. If SQL Server Express will not do it, what other options do I have? Or is the problem more a matter of the limitation of User Instances within Visual Basic itself?

Thanks again for the info and any further guidance you can provide.

|||

Hi,

"So I am back to my original question of how to distribute a FTS program using SQL Server. Do I need another version of SQL Server that supports FTS in User Instances? Or some variation on this theme?"

No, the only version which supports user instances is Express. YOu probably will need a variation of the planned envrionment.

"

Or, is there a way to use SQL Server 2005 Express (Adv SP2) with VB in a way other than with User Instances?

The DB is a static, historical, read only archive so security is not an issue for this application.

"

You will need server instances. Install SQL Server Express on the computer and attach the database to the server instance, instead of just attaching it via a user instance. This can be either done using the sp_attachdb command or the Management GUI.|||

I believe that I have attached the DB to the server instance of SQL Server Express using the Management GUI. Otherwise I would not be able to do FTS in Managment Studio. I also think that is why I am able to do FTS on the DB within Visual Studio as attached to the server rather than through a User Instance which does not support FTS.

I am not clear on what happens when I deploy the application including SQL Server 2005 Express. When I have done it before I was able to do regular DB activities, but got he error message about not supporting FTS only when I tried to do the FTS. So I am able to deploy the DB and use it, but it must be in a User Instance which apparently will never support FTS.

Can SQL Server Express be deployed in a way that the distributed version does not have to rely on a User Instance? If not, I don't see how the Express version will ever be usable for FTS in a distributed application.

Thanks for your support in helping me sort this out.

|||

I have been trying to deploy the application with the DB attached to a Server Instance rather than User Instance. No luck yet. I have no problem running the FTS on a Server Instance in VB during development. So it seems to be a matter of getting it deployed properly.

Just so that I am clear as to what I am trying to do, am I correct in assuming that I can deploy Sql Server 2005 Express (Advanced SP2) with my Visual Basic app and it will support Full Text Search so long as the DB is attached to a server instance rather than user instance? Thus I just need to learn how to access the DB with a server instance in the distributed program. Do I understand you correctly on this?

distribution cleanup cannot clean up snapshot folder

We are using SQL Server 2005 SP2 to do transactional replication.

We and have a separate service account for the SQL Agents (sqladmin) vs. SQL Replication Agents (sqlrepadmin). It is my understanding this is a replication security best practice. The sqlrepadmin has full permissions on the snapshot share folder and it's subdirectories. The sqladmin account does not have permissions at all.

I have been getting an error message when we run the distribution clean up job.

Executed as user: PROD\sqladmin. Could not remove directory '\\Tes01box\Repldata\unc\qabox01_DB01_TO_ORACLE\20070905104896\'. Check the security context of xp_cmdshell

I have dropped the publication and recreated which is what appears to have caused the error.

From

http://technet.microsoft.com/en-us/library/ms151151.aspx

Note:

If a publication is dropped, replication attempts to remove the snapshot folder under the security context of the SQL Server service account. If this account does not have sufficient privileges, log in with an account that does have sufficient privileges and remove the folder manually. Removing a folder requires the Modify privilege if the folder is a local path or the Full Control privilege if the folder is a network path.

The note above implies that the SQL Server service account (sqladmin) needs permissions on the snapshot folder as well.

Finally my questions:

Is there a workaround that will allow the distribution cleanup job to run as sqlrepadmin and perform the delete?

If both sqlrepadmin and sqladmin need permissions to the snapshot what is the reasoning from a security perspective of separating them out?

open up your distribution clean up task job, and in the job step properties do the following:

setuser 'sqlrepadmin'
GO
EXEC dbo.sp_MSdistribution_cleanup @.min_distretention = 0, @.max_distretention = 72
GO|||

I did have to qualify the domain, as in DOMAIN\sqlrepadmin to get it to work. We have same login for 2 different domains.

What an amazingly simple and elegant solution. I feel silly that I did not think of this.

Thank you Hilary.

distribution cleanup cannot clean up snapshot folder

We are using SQL Server 2005 SP2 to do transactional replication.

We and have a separate service account for the SQL Agents (sqladmin) vs. SQL Replication Agents (sqlrepadmin). It is my understanding this is a replication security best practice. The sqlrepadmin has full permissions on the snapshot share folder and it's subdirectories. The sqladmin account does not have permissions at all.

I have been getting an error message when we run the distribution clean up job.

Executed as user: PROD\sqladmin. Could not remove directory '\\Tes01box\Repldata\unc\qabox01_DB01_TO_ORACLE\20070905104896\'. Check the security context of xp_cmdshell

I have dropped the publication and recreated which is what appears to have caused the error.

From

http://technet.microsoft.com/en-us/library/ms151151.aspx

Note:

If a publication is dropped, replication attempts to remove the snapshot folder under the security context of the SQL Server service account. If this account does not have sufficient privileges, log in with an account that does have sufficient privileges and remove the folder manually. Removing a folder requires the Modify privilege if the folder is a local path or the Full Control privilege if the folder is a network path.

The note above implies that the SQL Server service account (sqladmin) needs permissions on the snapshot folder as well.

Finally my questions:

Is there a workaround that will allow the distribution cleanup job to run as sqlrepadmin and perform the delete?

If both sqlrepadmin and sqladmin need permissions to the snapshot what is the reasoning from a security perspective of separating them out?

open up your distribution clean up task job, and in the job step properties do the following:

setuser 'sqlrepadmin'
GO
EXEC dbo.sp_MSdistribution_cleanup @.min_distretention = 0, @.max_distretention = 72
GO|||

I did have to qualify the domain, as in DOMAIN\sqlrepadmin to get it to work. We have same login for 2 different domains.

What an amazingly simple and elegant solution. I feel silly that I did not think of this.

Thank you Hilary.

Distribution clean up: distribution job not removing snapshots

On SQL Server 2005 SP2 for Publisher and Distributor on the same instance, my old snapshots are not being cleaned up.

The following error is in the agent history:

Executed as user: Domain\MyUser. Could not remove directory '\\vmsql01\ReplData\unc\Publication_TRANSACTIONAL\20070702104416\'. Check the security context of xp_cmdshell and close other processes that may be accessing the directory. [SQLSTATE 42000] (Error 20015). The step failed.

xp_cmdshell is enabled and I can run commands like :

exec master.dbo.xp_cmdshell ' md c:\TestFolder\'

The permissions to the snapshot share and file system are that Domain\MyUser has full control.

I have logged into the machine as this user and can remove snapshots so it does not seem to be a permission issue.

On other machines I do not get any errors but the snapshot folder still is not cleaned up.

Any suggestion as to what the problem could be?

Thanks,

Amy

hi amy,

i think there's a sql job in msdb

if i quite remember well to do the

clean up

you may manually trigger

regards,

joey

|||

Update:

I ran filemonitor while the cleanup job was running and noticed it was telling me the user that owns the SQL Server Service had access denied errors. I gave that user permissions to the snapshot folder and it is now cleaning up (well any of the really old stuff it is not but anything that has expired lately it has cleaned up).

My SQLUser1 owns the SQL Server Server and SQLUser2 owns the SQL Server Agent Service.

I thought the SQLUser2 would be the one who required permissions to the snapshot folder to clean it up?

Does xp_cmdshell need to be enabled for the cleanup job to work? I have not found anything in BOL to suggest that but ...

The other machines are still running cleanup jobs without error and still not removing any snapshots.

Any suggestions?

Thanks,

Amy

Distributed Transactions with SQL Express, Server 2003, and XP SP2

I have SQL Express installed on a Windows XP SP2 machine and on a Windows
Server 2003 machine. I added the Windows Server 2003 machine as a linked
server on the Windows XP SP2 machine.
I have checked and double checked that the DTC settings are correct on both
machines and that the DTC is running on both machines, but I am unable to
execute a distributed transaction. I have even tried playing around with many
different combinations of settings to try to get this to work. I have
followed the directions in many of the documents that can be found online on
this issue, but without success.
I am using SQL Server security and am able to execute queries if I do not
begin a transaction. But I cannot execute them if I begin a transaction.
When I execute them in a transaction, I get the following error:
OLE DB provider "SQLNCLI" for linked server "linkedserver" returned message
"No transaction is active.".
Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "linkedserver" was unable to begin a distributed transaction.
Does anyone have any ideas?
--
Corey YoungCan MSDTC get through the XP Firewall?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Young, Corey" <YoungCorey@.discussions.microsoft.com> wrote in message
news:E2E6FDA4-D185-4625-9BEB-D4995254EE70@.microsoft.com...
>I have SQL Express installed on a Windows XP SP2 machine and on a Windows
> Server 2003 machine. I added the Windows Server 2003 machine as a linked
> server on the Windows XP SP2 machine.
> I have checked and double checked that the DTC settings are correct on
> both
> machines and that the DTC is running on both machines, but I am unable to
> execute a distributed transaction. I have even tried playing around with
> many
> different combinations of settings to try to get this to work. I have
> followed the directions in many of the documents that can be found online
> on
> this issue, but without success.
> I am using SQL Server security and am able to execute queries if I do not
> begin a transaction. But I cannot execute them if I begin a transaction.
> When I execute them in a transaction, I get the following error:
> OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
> message
> "No transaction is active.".
> Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "linkedserver" was unable to begin a distributed
> transaction.
> Does anyone have any ideas?
> --
> Corey Young
>|||I turned the firewall off on both machines.
--
Corey Young
"Roger Wolter[MSFT]" wrote:
> Can MSDTC get through the XP Firewall?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Young, Corey" <YoungCorey@.discussions.microsoft.com> wrote in message
> news:E2E6FDA4-D185-4625-9BEB-D4995254EE70@.microsoft.com...
> >I have SQL Express installed on a Windows XP SP2 machine and on a Windows
> > Server 2003 machine. I added the Windows Server 2003 machine as a linked
> > server on the Windows XP SP2 machine.
> >
> > I have checked and double checked that the DTC settings are correct on
> > both
> > machines and that the DTC is running on both machines, but I am unable to
> > execute a distributed transaction. I have even tried playing around with
> > many
> > different combinations of settings to try to get this to work. I have
> > followed the directions in many of the documents that can be found online
> > on
> > this issue, but without success.
> >
> > I am using SQL Server security and am able to execute queries if I do not
> > begin a transaction. But I cannot execute them if I begin a transaction.
> >
> > When I execute them in a transaction, I get the following error:
> >
> > OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
> > message
> > "No transaction is active.".
> >
> > Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
> >
> > The operation could not be performed because OLE DB provider "SQLNCLI" for
> > linked server "linkedserver" was unable to begin a distributed
> > transaction.
> >
> > Does anyone have any ideas?
> >
> > --
> > Corey Young
> >
>
>

Distributed Transactions with SQL Express, Server 2003, and XP SP2

I have SQL Express installed on a Windows XP SP2 machine and on a Windows
Server 2003 machine. I added the Windows Server 2003 machine as a linked
server on the Windows XP SP2 machine.
I have checked and double checked that the DTC settings are correct on both
machines and that the DTC is running on both machines, but I am unable to
execute a distributed transaction. I have even tried playing around with many
different combinations of settings to try to get this to work. I have
followed the directions in many of the documents that can be found online on
this issue, but without success.
I am using SQL Server security and am able to execute queries if I do not
begin a transaction. But I cannot execute them if I begin a transaction.
When I execute them in a transaction, I get the following error:
OLE DB provider "SQLNCLI" for linked server "linkedserver" returned message
"No transaction is active.".
Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "linkedserver" was unable to begin a distributed transaction.
Does anyone have any ideas?
Corey Young
Can MSDTC get through the XP Firewall?
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Young, Corey" <YoungCorey@.discussions.microsoft.com> wrote in message
news:E2E6FDA4-D185-4625-9BEB-D4995254EE70@.microsoft.com...
>I have SQL Express installed on a Windows XP SP2 machine and on a Windows
> Server 2003 machine. I added the Windows Server 2003 machine as a linked
> server on the Windows XP SP2 machine.
> I have checked and double checked that the DTC settings are correct on
> both
> machines and that the DTC is running on both machines, but I am unable to
> execute a distributed transaction. I have even tried playing around with
> many
> different combinations of settings to try to get this to work. I have
> followed the directions in many of the documents that can be found online
> on
> this issue, but without success.
> I am using SQL Server security and am able to execute queries if I do not
> begin a transaction. But I cannot execute them if I begin a transaction.
> When I execute them in a transaction, I get the following error:
> OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
> message
> "No transaction is active.".
> Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "linkedserver" was unable to begin a distributed
> transaction.
> Does anyone have any ideas?
> --
> Corey Young
>

Distributed Transactions with SQL Express, Server 2003, and XP SP2

I have SQL Express installed on a Windows XP SP2 machine and on a Windows
Server 2003 machine. I added the Windows Server 2003 machine as a linked
server on the Windows XP SP2 machine.
I have checked and double checked that the DTC settings are correct on both
machines and that the DTC is running on both machines, but I am unable to
execute a distributed transaction. I have even tried playing around with man
y
different combinations of settings to try to get this to work. I have
followed the directions in many of the documents that can be found online on
this issue, but without success.
I am using SQL Server security and am able to execute queries if I do not
begin a transaction. But I cannot execute them if I begin a transaction.
When I execute them in a transaction, I get the following error:
OLE DB provider "SQLNCLI" for linked server "linkedserver" returned message
"No transaction is active.".
Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "linkedserver" was unable to begin a distributed transaction.
Does anyone have any ideas?
Corey YoungCan MSDTC get through the XP Firewall?
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Young, Corey" <YoungCorey@.discussions.microsoft.com> wrote in message
news:E2E6FDA4-D185-4625-9BEB-D4995254EE70@.microsoft.com...
>I have SQL Express installed on a Windows XP SP2 machine and on a Windows
> Server 2003 machine. I added the Windows Server 2003 machine as a linked
> server on the Windows XP SP2 machine.
> I have checked and double checked that the DTC settings are correct on
> both
> machines and that the DTC is running on both machines, but I am unable to
> execute a distributed transaction. I have even tried playing around with
> many
> different combinations of settings to try to get this to work. I have
> followed the directions in many of the documents that can be found online
> on
> this issue, but without success.
> I am using SQL Server security and am able to execute queries if I do not
> begin a transaction. But I cannot execute them if I begin a transaction.
> When I execute them in a transaction, I get the following error:
> OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
> message
> "No transaction is active.".
> Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "linkedserver" was unable to begin a distributed
> transaction.
> Does anyone have any ideas?
> --
> Corey Young
>

distributed transaction issue

Dear gurus,
Your help is greatly appreciated...
My environment:
SQL Server 2K5 EE SP2 build# 3154 on Windows 2K3 SP1
I have three servers:
Server_1, Server_2, and Server_3
My issues:
I have the issue between Server_1 and Server_2 when I start a distributed
transaction (all linked server are set up properly), like:
on Server_1:
begin distributed tran
select * from Server_2.master.sys.databases
commit tran
I will receive the error:
OLE DB provider "SQLNCLI" for linked server "Server_2" returned message "No
transaction is active.".
Msg 7391, Level 16, State 2, Line 2
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "Server_2" was unable to begin a distributed transaction.
This happens on Server_2, when I run the following:
begin distributed tran
select * from Server_1.master.sys.databases
commit tran
However if I run the query on Server_3 or run against Server_3 from either
Server_1 or Server_2, no error occurs:
i.e if I run on Server_3:
begin distributed tran
select * from Server_1.master.sys.databases
select * from Server_2.master.sys.databases
commit tran
or run on either Server_1 or Server_2
begin distributed tran
select * from Server_3.master.sys.databases
commit tran
they are all successful.
What puzzles me is the fact:
Server_1 and Server_3 can have distributed transactions without problem, and
so can Server2 and Server_3, but NOT Server_1 and Server_2.
So this seems something is wrong between Server_1 and Server_2, also I can
confirm all servers are in the same domain and MS DTC is running on all
servers fine.
can some shed some lights on how come this can happen?
Thanks in advance for your time.
Kind regards,
Jeff
There are a lot of different possiblities. I would google for the latest
com+ rollup and configuring dtc through a firewall kb for starters.
Jason Massie
http://statisticsio.com
"jeff_y" <jeffrey_ya@.abc.com> wrote in message
news:OgufZO8KIHA.5360@.TK2MSFTNGP03.phx.gbl...
> Dear gurus,
> Your help is greatly appreciated...
> My environment:
> SQL Server 2K5 EE SP2 build# 3154 on Windows 2K3 SP1
> I have three servers:
> Server_1, Server_2, and Server_3
> My issues:
> I have the issue between Server_1 and Server_2 when I start a distributed
> transaction (all linked server are set up properly), like:
> on Server_1:
> begin distributed tran
> select * from Server_2.master.sys.databases
> commit tran
> I will receive the error:
> OLE DB provider "SQLNCLI" for linked server "Server_2" returned message
> "No transaction is active.".
> Msg 7391, Level 16, State 2, Line 2
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "Server_2" was unable to begin a distributed transaction.
> This happens on Server_2, when I run the following:
> begin distributed tran
> select * from Server_1.master.sys.databases
> commit tran
> However if I run the query on Server_3 or run against Server_3 from either
> Server_1 or Server_2, no error occurs:
> i.e if I run on Server_3:
> begin distributed tran
> select * from Server_1.master.sys.databases
> select * from Server_2.master.sys.databases
> commit tran
> or run on either Server_1 or Server_2
> begin distributed tran
> select * from Server_3.master.sys.databases
> commit tran
> they are all successful.
> What puzzles me is the fact:
> Server_1 and Server_3 can have distributed transactions without problem,
> and so can Server2 and Server_3, but NOT Server_1 and Server_2.
> So this seems something is wrong between Server_1 and Server_2, also I can
> confirm all servers are in the same domain and MS DTC is running on all
> servers fine.
>
> can some shed some lights on how come this can happen?
> Thanks in advance for your time.
> Kind regards,
> Jeff
>

distributed transaction issue

Dear gurus,
Your help is greatly appreciated...
My environment:
SQL Server 2K5 EE SP2 build# 3154 on Windows 2K3 SP1
I have three servers:
Server_1, Server_2, and Server_3
My issues:
I have the issue between Server_1 and Server_2 when I start a distributed
transaction (all linked server are set up properly), like:
on Server_1:
begin distributed tran
select * from Server_2.master.sys.databases
commit tran
I will receive the error:
OLE DB provider "SQLNCLI" for linked server "Server_2" returned message "No
transaction is active.".
Msg 7391, Level 16, State 2, Line 2
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "Server_2" was unable to begin a distributed transaction.
This happens on Server_2, when I run the following:
begin distributed tran
select * from Server_1.master.sys.databases
commit tran
However if I run the query on Server_3 or run against Server_3 from either
Server_1 or Server_2, no error occurs:
i.e if I run on Server_3:
begin distributed tran
select * from Server_1.master.sys.databases
select * from Server_2.master.sys.databases
commit tran
or run on either Server_1 or Server_2
begin distributed tran
select * from Server_3.master.sys.databases
commit tran
they are all successful.
What puzzles me is the fact:
Server_1 and Server_3 can have distributed transactions without problem, and
so can Server2 and Server_3, but NOT Server_1 and Server_2.
So this seems something is wrong between Server_1 and Server_2, also I can
confirm all servers are in the same domain and MS DTC is running on all
servers fine.
can some shed some lights on how come this can happen?
Thanks in advance for your time.
Kind regards,
JeffThere are a lot of different possiblities. I would google for the latest
com+ rollup and configuring dtc through a firewall kb for starters.
Jason Massie
http://statisticsio.com
"jeff_y" <jeffrey_ya@.abc.com> wrote in message
news:OgufZO8KIHA.5360@.TK2MSFTNGP03.phx.gbl...
> Dear gurus,
> Your help is greatly appreciated...
> My environment:
> SQL Server 2K5 EE SP2 build# 3154 on Windows 2K3 SP1
> I have three servers:
> Server_1, Server_2, and Server_3
> My issues:
> I have the issue between Server_1 and Server_2 when I start a distributed
> transaction (all linked server are set up properly), like:
> on Server_1:
> begin distributed tran
> select * from Server_2.master.sys.databases
> commit tran
> I will receive the error:
> OLE DB provider "SQLNCLI" for linked server "Server_2" returned message
> "No transaction is active.".
> Msg 7391, Level 16, State 2, Line 2
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "Server_2" was unable to begin a distributed transaction.
> This happens on Server_2, when I run the following:
> begin distributed tran
> select * from Server_1.master.sys.databases
> commit tran
> However if I run the query on Server_3 or run against Server_3 from either
> Server_1 or Server_2, no error occurs:
> i.e if I run on Server_3:
> begin distributed tran
> select * from Server_1.master.sys.databases
> select * from Server_2.master.sys.databases
> commit tran
> or run on either Server_1 or Server_2
> begin distributed tran
> select * from Server_3.master.sys.databases
> commit tran
> they are all successful.
> What puzzles me is the fact:
> Server_1 and Server_3 can have distributed transactions without problem,
> and so can Server2 and Server_3, but NOT Server_1 and Server_2.
> So this seems something is wrong between Server_1 and Server_2, also I can
> confirm all servers are in the same domain and MS DTC is running on all
> servers fine.
>
> can some shed some lights on how come this can happen?
> Thanks in advance for your time.
> Kind regards,
> Jeff
>