.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Kaviya Balasubramanian
Sgraph Infotech
Imran Ghani
Post New Web Links

Distributed transaction using linked server not working in SQL Server 2008 64 bit

Posted By:      Posted Date: October 03, 2010    Points: 0   Category :Sql Server
 

Hi. I have had an issue trying to get distributed transactions to work in SQL Server 2008 using a linked server. The error message I get is

OLE DB provider "SQLNCLI10" for linked server "pod1" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 3
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "pod1" was unable to begin a distributed transaction.

My Environment:

Windows Server 2008 Enterprise 64 bit, SQL Server 2008 Enterprise 64 bit

Problem occurs with multiple different builds of SQL Server 2008 - I have been able to reproduce the problem with 10.0.1600.0, 10.0.1779.0 (CU 2), as well as 10.0.2531 (SP1)

 

I am aware that other people have had this issue and have reviewed all existing posts. I have verified that the MSDTC is configured correctly on both machines. I have also used DTCTester to verify that DTC is working correctly on all machines in question. None of the mentioned resolutions has solved this problem for me.

I am not seeing this problem occur on my 32 bit test machines - it is only occurring if at least one of the two machines is 64 bit.

 




View Complete Post


More Related Resource Links

Linked server from SQL Server 2008 R2 to SQL Server 2005 not working

  

I'm trying to create a linked server from SQL Server r2 to SQL Server 2005 (both 64 bit Enterprise) using the following script

 EXEC master.dbo.sp_addlinkedserver
 @server = N'TestLS'
 ,@srvproduct=''
 ,@provider='SQLOLEDB.1'
 ,@datasrc=N'dw_prod'
 ,@provstr='Integrated Security=SSPI;';
 
 GO

SELECT
    *
 FROM
    [TestLS].[I_OLAP].[dbo].[FACT_AUDIT]
I verified that the Kerberos is working.

When I try to run a four part query as below it gives me the error

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
OLE DB provider "SQLNCLI10" for linked server "TestLS" returned message "Invalid connection string attribute".


If I try to set up the linked server against localhost (i.e. SQL 2008 R2, it works just fine).


Thanks,
GB


GBM

Not working on Windows Server 2008

  

Hi,

 

I had the CSS friendly adapters working great on our old server (windows server 2003 - 32bit) and now we have moved to a 64 bit environment and now I can not get the CSS friendly adapters to work.

We are using SharePoint 2007.

Is there another work thru for 64bit environment.

I notice windows server 2008 has a directory  C:\Windows\Microsoft.NET\Framework64 would I be using the browser file in this directory???

Please help!


Indexing services on window server 2008 is not working with .net

  
Hi, I am useing Microsoft indexing services in windows 2008 64 bit .I am getting error like "Exception from HRESULT: 0xC0000005" while i am createing  new catlog from .net 2.0.I am using  "ciodm.dll" for adding new catlog. i am using this code for create new catelog.   Dim Admin As New CIODMLib.AdminIndexServer   Admin.AddCatalog(varContractNo, txtCatelog.Text + "\" + varContractNo) Admin.Stop() Admin.Start() AddScope(txtDocFolderPath.Text, txtSite.Text, txtCatelogname.Text)   Please help me anybody have fixed this issue on window server 2008 64 bit.  

Distributed Transactions fail on Linked server

  
We get the below error while performing a distributed transaction on linked server. We have several linked servers configured in the source server and all of them succeed with the distributed transaction except on one.   We did all the basic troubleshooting and moreover the distributed transactions work fine if we use a remote server instead.   Need your expert guidance in resolving this issue     Error:   OLE DB provider "SQLNCLI10" for linked server "SERVERNAME.REDMOND.CORP.MICROSOFT.COM" returned message "No transaction is active.". Msg 7391, Level 16, State 2, Line 3 The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "SERVERNAME.REDMOND.CORP.MICROSOFT.COM" was unable to begin a distributed transaction.     Test code:   begin distributed transaction   select top 10 * from [SERVERNAME.REDMOND.CORP.MICROSOFT.COM].master.sys.objects   ROLLBACK     Source server :      Microsoft SQL Server 2008 (RTM) - 10.0.1779.0 (X64)        Nov 12 2008 12:10:04        Copyright (c) 1988-2008 Microsoft Corporation        Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6001: Service Pack 1) (VM)     Target server :     Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)        Jul  9 2008 14:43:34        Copyright (c) 1988-2008 Microsoft Corporation        Enterprise Edition

SQL Server 2008 Linked Server SELECT INTO issue

  
I have a SQL Server 2008 installation running on a clustered Windows Server 2008 R2. I am trying to execute a query on a remote SQL Server to create a table. In order to do so, I call a stored procedure on the remote SQL Server. The stored procedure's description is as follows: ------------------------------------- Create procedure [dbo].[USP_RemoteExec] @varSQL varchar(max) as declare @tempSql nvarchar(max); set @tempSql = CONVERT(nvarchar(max),@varSQL); exec sp_executesql @tempSql ------------------------------------- I pass the following command to the remote stored procedure as follows: exec [servername].[dbname].dbo.USP_RemoteExecute @varSQL='if object_id(''testdb.dbo.tmp_testEmpty3'') is not null drop table testdb.dbo.tmp_testEmpty3; create table testdb.dbo.tmp_testempty3(col1 int, col2 varchar(20));' go select * from [servername].testdb.dbo.tmp_testEmpty3 go This statement returns the following: --------------------------------- col1,col2 (0 row(s) affected) But, when I run the following statement: exec [servername].[dbname].dbo.USP_RemoteExecute @varSQL='if object_id(''testdb.dbo.tmp_testEmpty'') is not null drop table testdb.dbo.tmp_testEmpty; select * into testdb.dbo.tmp_testEmpty from (select top 10 mytab.[col1] as [col1] , mytab.[col2] as [col2] , mytab.[col3] as [col3] FROM testdb.dbo.mytab as [mytab] with (nolock)) as A' go select * from [

SQL Server 2008 Linked Server SELECT INTO issue

  
I have a SQL Server 2008 installation running on a clustered Windows Server 2008 R2. I am trying to execute a query on a remote SQL Server to create a table. In order to do so, I call a stored procedure on the remote SQL Server. The stored procedure's description is as follows: ------------------------------------- Create procedure [dbo].[USP_RemoteExec] @varSQL varchar(max) as declare @tempSql nvarchar(max); set @tempSql = CONVERT(nvarchar(max),@varSQL); exec sp_executesql @tempSql ------------------------------------- I pass the following command to the remote stored procedure as follows: exec [servername].[dbname].dbo.USP_RemoteExecute @varSQL='if object_id(''testdb.dbo.tmp_testEmpty3'') is not null drop table testdb.dbo.tmp_testEmpty3; create table testdb.dbo.tmp_testempty3(col1 int, col2 varchar(20));' go select * from [servername].testdb.dbo.tmp_testEmpty3 go This statement returns the following: --------------------------------- col1,col2 (0 row(s) affected) But, when I run the following statement: exec [servername].[dbname].dbo.USP_RemoteExecute @varSQL='if object_id(''testdb.dbo.tmp_testEmpty'') is not null drop table testdb.dbo.tmp_testEmpty; select * into testdb.dbo.tmp_testEmpty from (select top 10 mytab.[col1] as [col1] , mytab.[col2] as [col2] , mytab.[col3] as [col3] FROM testdb.dbo.mytab as [mytab] with (nolock)) as A' go select * from [

Problems with linked server to Analysis Services (SQL 2008)

  
I have problem with creating linked server from SQL database to Analsis services. BOth services are running on same machine. Operating system is Windows 2008. I create linked server (I use windows authentication and I am administrator on AS):  EXEC sp_addlinkedserver @server= 'OLAP_PRETOKI', @srvproduct = '', @provider='MSOLAP', @datasrc='localhost', @catalog='DWDatabase'  But when I try to test connection I get error (in the event log) and in the error log/dump I get this: 2010-09-03 13:48:28.41 Server Error: 17310, Severity: 20, State: 1. 2010-09-03 13:48:28.41 Server A user request from the session with SPID 57 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory. 2010-09-03 13:48:32.53 spid58 Open of fault log C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\log\exception.log failed. 2010-09-03 13:48:32.65 spid58 Using 'dbghelp.dll' version '4.0.5' 2010-09-03 13:48:32.66 spid58 SqlDumpExceptionHandler: Process 58 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process. 2010-09-03 13:48:32.66 spid58 * ******************************************************************************* 2010-09-03 13:48:32.66 spid58 * 2010-09-03 13:48:32.66 spid58 * BEGIN STACK DUMP: 2010-09-03 13:4

how to make working WPF 2010 program make changes to SQL Server 2008 Express files

  
(1) I have a working WPF 2010 program that manipulates a table in SQL Server 2008 Express (2) the program does delete, update, select and insert correctly however in its own project file folder logbooklmk.mdf   logbooklmk_log.ldf (3) I want the actual SQL Server 2008 express files to be changed yes I know the issue is sharing and the copy option only applies to the local project files. I found this out by trying always copy, never copy and copy if newer. I probably have to change the .xsd folder to refer to the actual SQL Server 2008 Express file. Tried that once and it failed. Something about sharing not allowed.

Problems with linked server to Analysis Services (SQL 2008)

  
I have problem with creating linked server from SQL database to Analsis services. BOth services are running on same machine. Operating system is Windows 2008. I create linked server (I use windows authentication and I am administrator on AS):  EXEC sp_addlinkedserver @server= 'OLAP_PRETOKI', @srvproduct = '', @provider='MSOLAP', @datasrc='localhost', @catalog='DWDatabase'  But when I try to test connection I get error (in the event log) and in the error log/dump I get this: 2010-09-03 13:48:28.41 Server Error: 17310, Severity: 20, State: 1. 2010-09-03 13:48:28.41 Server A user request from the session with SPID 57 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory. 2010-09-03 13:48:32.53 spid58 Open of fault log C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\log\exception.log failed. 2010-09-03 13:48:32.65 spid58 Using 'dbghelp.dll' version '4.0.5' 2010-09-03 13:48:32.66 spid58 SqlDumpExceptionHandler: Process 58 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process. 2010-09-03 13:48:32.66 spid58 * ******************************************************************************* 2010-09-03 13:48:32.66 spid58 * 2010-09-03 13:48:32.66 spid58 * BEGIN STACK DUMP: 2010-09-03 13:4

Transaction Handling in SQL Server 2008

  
Hi there,        I am using .NET (C#) and sql server 2008 for my application, I am fetching records from a table and using gridview to show them.        Now saving selected records (using checkbox to select records) into another table row by row. before saving the records if records with same ID is present need to delete those records. could anyone tell me how to use transaction in SQL server 2008. any blog or website............... anything   Thanks, Sadaf

CLR stored procedures asssemblies working in sql server 2005 but giving issues over sql server 2008

  
Hi I developed CLR stored procedures assemblies using VS2005 and deployed in sql server 2005 are working fine. Currently we are migrating to sql server 2008. The same CLR assemblies are not working in 2008 version and raising following error. Here i created CLR assemblies in sql server 2008 using 'sa' user account which is having db_owner permission over 'mydatabase'. I am getting an error message like "A .NET Framework error occurred during execution of user-defined routine or aggregate". In sql logs showing messages like as -- Common language runtime (CLR) functionality initialized using CLR version v2.0.50727 from C:\Windows\Microsoft.NET\Framework\v2.0.50727\. -- AppDomain 2 (mydatabase.dbo[runtime].1) created.   Please let me know what may be the cause for this type of issues. Is there any specific backward compatibility settings has to be done over sql server 2008 or sql server 2008 R2 while using 2005 CLR routines?   Thanks in advance.  

Problems with MSDASQL when working with linked server from t-sql

  
Good day. I have a big problem when working with linked server to PostgreSQL from t-sql. ------------------------------------------- Windows Server 2003 SP2 x64 Updating OLE DB-Provider for ODBC WindowsServer2003.WindowsXP-KB948459-v2-x64 SQL Server 2008 R2 Ent x64 (10.50.1734)  x64 ODBC Provider for Postgres ------------------------------------------- Created system dsn on PostgreSQL. Created a linked server using this dsn through MSDASQL provider. Settings MSDASQL provider by default (only Allow inprocess = true). Selects the linked server works fine. If I try to insert a table on a linked server: insert PG_SERVER.BASE.[public].codepost select top 50000 p1, p2, p3, p4, p5 from #qwe then inserting a small number of lines (up to 10000) of the form 'test', 'test', 'test', 'test', 'test'  it works, but more of a script hangs with different wait types (oledb, preemptive_com_release ...) and does not help even kill process, only restart sql server service. How to solve this problem?

query with locks not working in sql server 2008

  
I have a select query with join on some view and locks as (readpas,updlock) after executiong this query it executes the same query with little modification( no locks etc). This sequence of query executes fine in SQL server 2000/2005.   When I am executing same seq in sql server 2008 through application, I am getting timeout for second query. Can someone please suggest what can be the reason? I cannot post the query.Cheers!!! Vatsa

SQL Server 2008 R2 64 bit working with out Hot fixes -- Job modification and Creattin

  

 Hi all

I Installed SQL Server 2008 R2 and it is side by side with SQL Server 2005 (64bit)

Here my version

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46
Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2) Hypervisor).

I am able to create Jobs and able to modify the Jobs with out appling any hotfix.


old post which is recomended to applu hotfix

http://social.msdn.microsoft.com/Forums/en/sqltools/thread/778630a4-30b5-4fea-8fd5-5a4c631c78ad

1. Why my server not given  gives error while creating /modifing Job ?

2.From  wizard/mantenancePlans  Iam creating Jobs it is correct test.

2.Is it because of SQL Server 2005

 

Please some suggest me..why I a

restore SQL server 2005 database on new SQL server 2008 not working

  
please any one advice me ,i have Great Plain database on SQL server 2005 when i tried to install new SQL server 2008 and restore my Database the GreatPlain not working well as well the Data Base

2008 Report not working on 2005 report server

  

Hello, I have been researching this issue and it seems if I have created a report using 2008, I cannot upload it to a 2005 report server.

I just wanted to check and see if I have any other options than to re-create the report using 2005? Would copy/paste of the XML from 2008 to 2005 work?

Thanks! Linda

 


Unable to connect Excel 2003 from linked server in sql 2008 64 bit

  
Hi ALL,

I was unable to connect to excel 2003 with linked server created in sql server 2008 64 bit. This was working fine with sql server 2005 32 bit.

I know that there no 64 bit drivers for JET drivers, but i am looking for some alternative ways to do the same job.

I dont want to migrate to Excel 2007.

Thanks in advance for all your help.

Thanks,
Loknath
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend