Sql Linked Server: via

Showing posts with label via. Show all posts

Monday, March 26, 2012

Linked Server Problem

We have 2 SQl Servers. Server A is SQL 7, and Server B is SQL 2000. We nee
d
to push data from server A to server B. My plan is to do this via executing
a stored procedure on the 2000 server. However, I am getting the following
error: "SQL Server does not exist, or access denied". I don't get this erro
r
when I am going from a 2000 server to a 7 server. Is it possible to push
data from a 7 server to a 2000 server using linked servers? Any help would
be greatly appreciated. Thanks!Take the Linked server out of the equation and test with ISQL.exe from SQL
7 to SQL 2000.
This will give you the proper error message to tshoot the problem.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Linked Server Performance

I call a stored proc via a linked server like:
EXEC LINKEDSERVER.dbname.dbo.usp_myproc
The proc is very basic and executes immediately when called from the local
box. But when it is called as a linked server call, it takes 30-40 seconds
to execute.
The newtork traffic is not heavy. What are some things I should look at to
improve this performance?
Thanks
DaveDoes usp_myproc only work with data on the remote server ?
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"DaveF" <davef@.comcast.net> wrote in message
news:%23ekAsrpbDHA.3248@.tk2msftngp13.phx.gbl...
I call a stored proc via a linked server like:
EXEC LINKEDSERVER.dbname.dbo.usp_myproc
The proc is very basic and executes immediately when called from the local
box. But when it is called as a linked server call, it takes 30-40 seconds
to execute.
The newtork traffic is not heavy. What are some things I should look at to
improve this performance?
Thanks
Dave

Friday, March 23, 2012

Linked Server Issues

Good Morning All,
I am receiving the following error message when attempting to update tables
with a trigger that call a stored procedure via a linked server. If I copy
and paste the execute statement from the triggerinto query analyzer it
completes without error.
Server: Msg 7391, Level 16, State 1, Procedure UpdateUnicenterTextFiles,
Line 5
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Any suggestions about what is causing this and/or how I can correct it would
be greatly appreciated.
Thanks in advance,
Elecia
Are You using any distributed transations
go to administrative tools\component
services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
Check if you have Enable XA transactions and allow remote clients and
Noauthntication Required
Options
Thanks,
Saradhi
|||Thanks I will check that. I am also looking at the possibility of it being a
firewall issue and that maybe some of the necesary ports may be blocked.
Elecia
"saradhi" wrote:

> Are You using any distributed transations
>
> go to administrative tools\component
> services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
> Check if you have Enable XA transactions and allow remote clients and
> Noauthntication Required
> Options
> Thanks,
> Saradhi
>

Linked Server Issues

Good Morning All,
I am receiving the following error message when attempting to update tables
with a trigger that call a stored procedure via a linked server. If I copy
and paste the execute statement from the triggerinto query analyzer it
completes without error.
Server: Msg 7391, Level 16, State 1, Procedure UpdateUnicenterTextFiles,
Line 5
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Any suggestions about what is causing this and/or how I can correct it would
be greatly appreciated.
Thanks in advance,
--
EleciaAre You using any distributed transations
go to administrative tools\component
services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
Check if you have Enable XA transactions and allow remote clients and
Noauthntication Required
Options
Thanks,
Saradhi|||Thanks I will check that. I am also looking at the possibility of it being a
firewall issue and that maybe some of the necesary ports may be blocked.
--
Elecia
"saradhi" wrote:

> Are You using any distributed transations
>
> go to administrative tools\component
> services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
> Check if you have Enable XA transactions and allow remote clients and
> Noauthntication Required
> Options
> Thanks,
> Saradhi
>

Linked Server Issues

Good Morning All,
I am receiving the following error message when attempting to update tables
with a trigger that call a stored procedure via a linked server. If I copy
and paste the execute statement from the triggerinto query analyzer it
completes without error.
Server: Msg 7391, Level 16, State 1, Procedure UpdateUnicenterTextFiles,
Line 5
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Any suggestions about what is causing this and/or how I can correct it would
be greatly appreciated.
Thanks in advance,
--
EleciaAre You using any distributed transations
go to administrative tools\component
services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
Check if you have Enable XA transactions and allow remote clients and
Noauthntication Required
Options
Thanks,
Saradhi|||Thanks I will check that. I am also looking at the possibility of it being a
firewall issue and that maybe some of the necesary ports may be blocked.
--
Elecia
"saradhi" wrote:
> Are You using any distributed transations
>
> go to administrative tools\component
> services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
> Check if you have Enable XA transactions and allow remote clients and
> Noauthntication Required
> Options
> Thanks,
> Saradhi
>sql

Linked Server issue

I have a problem querying a linked server connected via the OleDB provider for ODBC.

This is the DataDirect 32-BIT SequeLink 5.4 client driver connecting to a FileMaker Pro 8 database. I've tested this in VB.net 2005 by adding an ODBC connection in server explorer and a data grid to a form. I can run the tooty and the data loads in the grid with no errors what so ever.

So using the above ODBC connection I added a linked server called 'DNA' and run the following query on the Stock_DNA table.

SELECT * FROM OPENQUERY(DNA, 'SELECT * FROM Stock_DNA')

The result I get is this;

OLE DB provider "MSDASQL" for linked server "DNA" returned message "Requested conversion is not supported.".

Msg 7341, Level 16, State 2, Line 1

Cannot get the current row value of column "[MSDASQL].Stock_number" from OLE DB provider "MSDASQL" for linked server "DNA".

Now, if I specify field names in the OPENQUERY statement so that no text columns are returned the query runs fine, but anytime I include the text columns it blows up.

I also can't use four part naming with out getting an error as follows;

I give the statement;

SELECT * FROM DNA...Stock_DNA

and get this;

Msg 7313, Level 16, State 1, Line 1

An invalid schema or catalog was specified for the provider "MSDASQL" for linked server "DNA".

Any help or work arounds would be greatly appreciated.

It sounds like the problem is not related to the ODBC connection string, but with the Linked servers that are defined within the SQL server itself. When you invoke the linked server query the actual schema does not match the schema stored by the linked server information.

I have seen this twice - and i had to use a different solution each time:

1) define an index on the table you are selecting from - sometimes SQL can get confused without one.

2) define a view for the data you want and select from the view with the ODBC connection string.

Hope this helps - let me know how you get on.

Mike

|||

I am also having the "Cannot get the current row value" problem with text columns from an OPENQUERY call for a MySQL linked server. Does anyone know a fix for this problem?

|||

I am also trying to connect a Filemaker Pro database with .Net. Could you post your code?

Here is my attempt to load a DataGridView with Filemaker Pro data:

string sConnString = "DRIVER={DataDirect 32-BIT SequeLink 5.4};SDSN=Sample10;HST=localHost;PRT=2399;UID=admin;";
odbcConnection1 = new OdbcConnection(sConnString);
try
{
odbcConnection1.Open();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}

//connection opens okay

//The following doesn't work:
string strSQL = "select * from FirstName";
OdbcDataAdapter da = new OdbcDataAdapter(strSQL, odbcConnection1);
System.Data.DataSet ds = new System.Data.DataSet();
da.Fill(ds, "FirstName"); //ERROR [HY000] Unable to retrieve error message from backend ODBC Driver.
dataGridView1.DataSource = ds.Tables["FirstName"];

|||Chekc whether the data types between the source and target are similar or matches the requirement such varchar to varchar or nvarchar to nvarchar in thsi case.|||

Thanks Satya. With more testing, I have found that sometimes I can pull a table out, but not always.

Types in table that can be loaded:

VARCHAR

Types in table the cannot be loaded:

DOUBLE, VARCHAR, DATE

I am loaing the data into a dataset. Is DATE a type that a dataset can't handle?

EDIT> Yes, the field that uses type DATE can't be loaded. Thanks for the tip.

|||Glad to know more from your feedback, as explained you might need to check the compatbility of fields between the source and SQL Server table.|||

I'm at the same point as Brandon C...whose question seems to not have been really addressed.

To add to his post, if I try to use 4-part naming to query the linked server (FILEMAKER8), and just make up a schema name (dbschema) and run the query "SELECT Profiles.* FROM FILEMAKER8.dbschema..Profile Profiles", I get the following error:

OLE DB provider "MSDASQL" for linked server "FILEMAKER8" returned message "Unspecified error".
OLE DB provider "MSDASQL" for linked server "FILEMAKER8" returned message "[DataDirect][ODBC SequeLink driver]The qualifier 'dbschema' was larger than SQL_MAX_QUALIFIER_LEN from SQLGetInfo (0).".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDASQL" for linked server "FILEMAKER8". The provider supports the interface, but returns a failure code when it is used.

I'm gonna take a guess that the underlying DataDirect 32-BIT SequeLink 5.4 client driver doesn't support schemas? (If that makes sense).

Is it possible to force the MSDASQL provider to interpret all text column types a certain way? (don't know if that makes sense either)

|||Just an FYI,

I never was able to get the LinkedServer to work properly. I wound up creating some DSNs using the DataDirect 32-BIT SequeLink 5.4 driver on my server and a Web Service in VB.NET to access them.

Using the OdbcDataAdapter I am able to populate and return a DataSet regardless of the Data Types in the table.|||I think you are using filemaker as a linked server, if so ensure you relevant compatible drivers to perform the connection.|||

Yes, the DataDirect 32-BIT SequeLink 5.4 driver is the correct one for Filemaker. You should find the installer in your Filemaker folder ->\Extensions\xDBC Support\bin. Thanks for that tip, Brandon, I couldn't pull out some Filemaker data types, like DATE, for example.

|||Just a little more follow up.

I had to create a connection for each table or 'FORM' in FileMaker.

The code I'm using return the data set would be similar to the following;

Code Snippet

<WebMethod(Description:="This method takes a stock number and returns the" & _
" record from the Stock_DNA table.")> _
Public Function ReturnStockDNARecord(ByVal StockNo As String) As DataSet
Dim sSQL As String = ""
Dim sConnect As String = ""
Dim oConnection As New Odbc.OdbcConnection

oConnection.ConnectionString = "DSN=ABRC_DNA;UID=SYSTEM;PWD=SYSTEM;SDSN=Stock_DNA"
Try
oConnection.Open()
Catch e As Exception
LogAppError(e.Message & vbTab & e.Source & vbTab & _
e.StackTrace & vbTab & CStr(Now))
Return Nothing
End Try

sSQL = "SELECT * FROM Stock_DNA WHERE ABRC_stock_number = '" & StockNo.Trim & "'"

Dim DA As Odbc.OdbcDataAdapter = New Odbc.OdbcDataAdapter(sSQL, oConnection)
Dim DS As DataSet = New DataSet
DA.Fill(DS, "Stock_DNA")
oConnection.Close()
oConnection.Dispose()
Return DS
End Function

Simple but it does the job.
|||

Actually, that looks very much like what I have. Does it crash for you if you try to load a table with a DATE column?

Linked Server issue

I have a problem querying a linked server connected via the OleDB provider for ODBC.

So using the above ODBC connection I added a linked server called 'DNA' and run the following query on the Stock_DNA table.

SELECT * FROM OPENQUERY(DNA, 'SELECT * FROM Stock_DNA')

The result I get is this;

OLE DB provider "MSDASQL" for linked server "DNA" returned message "Requested conversion is not supported.".

Msg 7341, Level 16, State 2, Line 1

Cannot get the current row value of column "[MSDASQL].Stock_number" from OLE DB provider "MSDASQL" for linked server "DNA".

Now, if I specify field names in the OPENQUERY statement so that no text columns are returned the query runs fine, but anytime I include the text columns it blows up.

I also can't use four part naming with out getting an error as follows;

I give the statement;

SELECT * FROM DNA...Stock_DNA

and get this;

Msg 7313, Level 16, State 1, Line 1

An invalid schema or catalog was specified for the provider "MSDASQL" for linked server "DNA".

Any help or work arounds would be greatly appreciated.

I have seen this twice - and i had to use a different solution each time:

1) define an index on the table you are selecting from - sometimes SQL can get confused without one.

2) define a view for the data you want and select from the view with the ODBC connection string.

Hope this helps - let me know how you get on.

Mike

|||

I am also having the "Cannot get the current row value" problem with text columns from an OPENQUERY call for a MySQL linked server. Does anyone know a fix for this problem?

|||

I am also trying to connect a Filemaker Pro database with .Net. Could you post your code?

Here is my attempt to load a DataGridView with Filemaker Pro data:

//connection opens okay

|||Chekc whether the data types between the source and target are similar or matches the requirement such varchar to varchar or nvarchar to nvarchar in thsi case.|||

Thanks Satya. With more testing, I have found that sometimes I can pull a table out, but not always.

Types in table that can be loaded:

VARCHAR

Types in table the cannot be loaded:

DOUBLE, VARCHAR, DATE

I am loaing the data into a dataset. Is DATE a type that a dataset can't handle?

EDIT> Yes, the field that uses type DATE can't be loaded. Thanks for the tip.

|||Glad to know more from your feedback, as explained you might need to check the compatbility of fields between the source and SQL Server table.|||

I'm at the same point as Brandon C...whose question seems to not have been really addressed.

OLE DB provider "MSDASQL" for linked server "FILEMAKER8" returned message "Unspecified error".

OLE DB provider "MSDASQL" for linked server "FILEMAKER8" returned message "[DataDirect][ODBC SequeLink driver]The qualifier 'dbschema' was larger than SQL_MAX_QUALIFIER_LEN from SQLGetInfo (0).".

Msg 7311, Level 16, State 2, Line 1

Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDASQL" for linked server "FILEMAKER8". The provider supports the interface, but returns a failure code when it is used.

I'm gonna take a guess that the underlying DataDirect 32-BIT SequeLink 5.4 client driver doesn't support schemas? (If that makes sense).

Is it possible to force the MSDASQL provider to interpret all text column types a certain way? (don't know if that makes sense either)

|||Just an FYI,

I never was able to get the LinkedServer to work properly. I wound up creating some DSNs using the DataDirect 32-BIT SequeLink 5.4 driver on my server and a Web Service in VB.NET to access them.

|||Just a little more follow up.

I had to create a connection for each table or 'FORM' in FileMaker.

The code I'm using return the data set would be similar to the following;

Code Snippet

sSQL = "SELECT * FROM Stock_DNA WHERE ABRC_stock_number = '" & StockNo.Trim & "'"

Dim DA As Odbc.OdbcDataAdapter = New Odbc.OdbcDataAdapter(sSQL, oConnection)
Dim DS As DataSet = New DataSet
DA.Fill(DS, "Stock_DNA")
oConnection.Close()
oConnection.Dispose()
Return DS
End Function

Simple but it does the job.
|||

Actually, that looks very much like what I have. Does it crash for you if you try to load a table with a DATE column?

Linked Server issue

I have a problem querying a linked server connected via the OleDB provider for ODBC.

So using the above ODBC connection I added a linked server called 'DNA' and run the following query on the Stock_DNA table.

SELECT * FROM OPENQUERY(DNA, 'SELECT * FROM Stock_DNA')

The result I get is this;

OLE DB provider "MSDASQL" for linked server "DNA" returned message "Requested conversion is not supported.".

Msg 7341, Level 16, State 2, Line 1

Cannot get the current row value of column "[MSDASQL].Stock_number" from OLE DB provider "MSDASQL" for linked server "DNA".

Now, if I specify field names in the OPENQUERY statement so that no text columns are returned the query runs fine, but anytime I include the text columns it blows up.

I also can't use four part naming with out getting an error as follows;

I give the statement;

SELECT * FROM DNA...Stock_DNA

and get this;

Msg 7313, Level 16, State 1, Line 1

An invalid schema or catalog was specified for the provider "MSDASQL" for linked server "DNA".

Any help or work arounds would be greatly appreciated.

I have seen this twice - and i had to use a different solution each time:

1) define an index on the table you are selecting from - sometimes SQL can get confused without one.

2) define a view for the data you want and select from the view with the ODBC connection string.

Hope this helps - let me know how you get on.

Mike

|||

I am also having the "Cannot get the current row value" problem with text columns from an OPENQUERY call for a MySQL linked server. Does anyone know a fix for this problem?

|||

I am also trying to connect a Filemaker Pro database with .Net. Could you post your code?

Here is my attempt to load a DataGridView with Filemaker Pro data:

//connection opens okay

|||Chekc whether the data types between the source and target are similar or matches the requirement such varchar to varchar or nvarchar to nvarchar in thsi case.|||

Thanks Satya. With more testing, I have found that sometimes I can pull a table out, but not always.

Types in table that can be loaded:

VARCHAR

Types in table the cannot be loaded:

DOUBLE, VARCHAR, DATE

I am loaing the data into a dataset. Is DATE a type that a dataset can't handle?

EDIT> Yes, the field that uses type DATE can't be loaded. Thanks for the tip.

|||Glad to know more from your feedback, as explained you might need to check the compatbility of fields between the source and SQL Server table.|||

I'm at the same point as Brandon C...whose question seems to not have been really addressed.

OLE DB provider "MSDASQL" for linked server "FILEMAKER8" returned message "Unspecified error".
OLE DB provider "MSDASQL" for linked server "FILEMAKER8" returned message "[DataDirect][ODBC SequeLink driver]The qualifier 'dbschema' was larger than SQL_MAX_QUALIFIER_LEN from SQLGetInfo (0).".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDASQL" for linked server "FILEMAKER8". The provider supports the interface, but returns a failure code when it is used.

I'm gonna take a guess that the underlying DataDirect 32-BIT SequeLink 5.4 client driver doesn't support schemas? (If that makes sense).

Is it possible to force the MSDASQL provider to interpret all text column types a certain way? (don't know if that makes sense either)

|||Just an FYI,

I never was able to get the LinkedServer to work properly. I wound up creating some DSNs using the DataDirect 32-BIT SequeLink 5.4 driver on my server and a Web Service in VB.NET to access them.

|||Just a little more follow up.

I had to create a connection for each table or 'FORM' in FileMaker.

The code I'm using return the data set would be similar to the following;

Code Snippet

sSQL = "SELECT * FROM Stock_DNA WHERE ABRC_stock_number = '" & StockNo.Trim & "'"

Dim DA As Odbc.OdbcDataAdapter = New Odbc.OdbcDataAdapter(sSQL, oConnection)
Dim DS As DataSet = New DataSet
DA.Fill(DS, "Stock_DNA")
oConnection.Close()
oConnection.Dispose()
Return DS
End Function

Simple but it does the job.
|||

Actually, that looks very much like what I have. Does it crash for you if you try to load a table with a DATE column?

Linked Server issue

I have a problem querying a linked server connected via the OleDB provider for ODBC.

So using the above ODBC connection I added a linked server called 'DNA' and run the following query on the Stock_DNA table.

SELECT * FROM OPENQUERY(DNA, 'SELECT * FROM Stock_DNA')

The result I get is this;

OLE DB provider "MSDASQL" for linked server "DNA" returned message "Requested conversion is not supported.".

Msg 7341, Level 16, State 2, Line 1

Cannot get the current row value of column "[MSDASQL].Stock_number" from OLE DB provider "MSDASQL" for linked server "DNA".

Now, if I specify field names in the OPENQUERY statement so that no text columns are returned the query runs fine, but anytime I include the text columns it blows up.

I also can't use four part naming with out getting an error as follows;

I give the statement;

SELECT * FROM DNA...Stock_DNA

and get this;

Msg 7313, Level 16, State 1, Line 1

An invalid schema or catalog was specified for the provider "MSDASQL" for linked server "DNA".

Any help or work arounds would be greatly appreciated.

I have seen this twice - and i had to use a different solution each time:

1) define an index on the table you are selecting from - sometimes SQL can get confused without one.

2) define a view for the data you want and select from the view with the ODBC connection string.

Hope this helps - let me know how you get on.

Mike

|||

I am also having the "Cannot get the current row value" problem with text columns from an OPENQUERY call for a MySQL linked server. Does anyone know a fix for this problem?

|||

I am also trying to connect a Filemaker Pro database with .Net. Could you post your code?

Here is my attempt to load a DataGridView with Filemaker Pro data:

//connection opens okay

|||Chekc whether the data types between the source and target are similar or matches the requirement such varchar to varchar or nvarchar to nvarchar in thsi case.|||

Thanks Satya. With more testing, I have found that sometimes I can pull a table out, but not always.

Types in table that can be loaded:

VARCHAR

Types in table the cannot be loaded:

DOUBLE, VARCHAR, DATE

I am loaing the data into a dataset. Is DATE a type that a dataset can't handle?

EDIT> Yes, the field that uses type DATE can't be loaded. Thanks for the tip.

|||Glad to know more from your feedback, as explained you might need to check the compatbility of fields between the source and SQL Server table.|||

I'm at the same point as Brandon C...whose question seems to not have been really addressed.

OLE DB provider "MSDASQL" for linked server "FILEMAKER8" returned message "Unspecified error".
OLE DB provider "MSDASQL" for linked server "FILEMAKER8" returned message "[DataDirect][ODBC SequeLink driver]The qualifier 'dbschema' was larger than SQL_MAX_QUALIFIER_LEN from SQLGetInfo (0).".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDASQL" for linked server "FILEMAKER8". The provider supports the interface, but returns a failure code when it is used.

I'm gonna take a guess that the underlying DataDirect 32-BIT SequeLink 5.4 client driver doesn't support schemas? (If that makes sense).

Is it possible to force the MSDASQL provider to interpret all text column types a certain way? (don't know if that makes sense either)

|||Just an FYI,

I never was able to get the LinkedServer to work properly. I wound up creating some DSNs using the DataDirect 32-BIT SequeLink 5.4 driver on my server and a Web Service in VB.NET to access them.

|||Just a little more follow up.

I had to create a connection for each table or 'FORM' in FileMaker.

The code I'm using return the data set would be similar to the following;

Code Snippet

sSQL = "SELECT * FROM Stock_DNA WHERE ABRC_stock_number = '" & StockNo.Trim & "'"

Dim DA As Odbc.OdbcDataAdapter = New Odbc.OdbcDataAdapter(sSQL, oConnection)
Dim DS As DataSet = New DataSet
DA.Fill(DS, "Stock_DNA")
oConnection.Close()
oConnection.Dispose()
Return DS
End Function

Simple but it does the job.
|||

Actually, that looks very much like what I have. Does it crash for you if you try to load a table with a DATE column?

Linked Server issue

I have a problem querying a linked server connected via the OleDB provider for ODBC.

So using the above ODBC connection I added a linked server called 'DNA' and run the following query on the Stock_DNA table.

SELECT * FROM OPENQUERY(DNA, 'SELECT * FROM Stock_DNA')

The result I get is this;

OLE DB provider "MSDASQL" for linked server "DNA" returned message "Requested conversion is not supported.".

Msg 7341, Level 16, State 2, Line 1

Cannot get the current row value of column "[MSDASQL].Stock_number" from OLE DB provider "MSDASQL" for linked server "DNA".

Now, if I specify field names in the OPENQUERY statement so that no text columns are returned the query runs fine, but anytime I include the text columns it blows up.

I also can't use four part naming with out getting an error as follows;

I give the statement;

SELECT * FROM DNA...Stock_DNA

and get this;

Msg 7313, Level 16, State 1, Line 1

An invalid schema or catalog was specified for the provider "MSDASQL" for linked server "DNA".

Any help or work arounds would be greatly appreciated.

I have seen this twice - and i had to use a different solution each time:

1) define an index on the table you are selecting from - sometimes SQL can get confused without one.

2) define a view for the data you want and select from the view with the ODBC connection string.

Hope this helps - let me know how you get on.

Mike

|||

I am also having the "Cannot get the current row value" problem with text columns from an OPENQUERY call for a MySQL linked server. Does anyone know a fix for this problem?

|||

I am also trying to connect a Filemaker Pro database with .Net. Could you post your code?

Here is my attempt to load a DataGridView with Filemaker Pro data:

//connection opens okay

|||Chekc whether the data types between the source and target are similar or matches the requirement such varchar to varchar or nvarchar to nvarchar in thsi case.|||

Thanks Satya. With more testing, I have found that sometimes I can pull a table out, but not always.

Types in table that can be loaded:

VARCHAR

Types in table the cannot be loaded:

DOUBLE, VARCHAR, DATE

I am loaing the data into a dataset. Is DATE a type that a dataset can't handle?

EDIT> Yes, the field that uses type DATE can't be loaded. Thanks for the tip.

|||Glad to know more from your feedback, as explained you might need to check the compatbility of fields between the source and SQL Server table.|||

I'm at the same point as Brandon C...whose question seems to not have been really addressed.

OLE DB provider "MSDASQL" for linked server "FILEMAKER8" returned message "Unspecified error".

OLE DB provider "MSDASQL" for linked server "FILEMAKER8" returned message "[DataDirect][ODBC SequeLink driver]The qualifier 'dbschema' was larger than SQL_MAX_QUALIFIER_LEN from SQLGetInfo (0).".

Msg 7311, Level 16, State 2, Line 1

Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDASQL" for linked server "FILEMAKER8". The provider supports the interface, but returns a failure code when it is used.

I'm gonna take a guess that the underlying DataDirect 32-BIT SequeLink 5.4 client driver doesn't support schemas? (If that makes sense).

Is it possible to force the MSDASQL provider to interpret all text column types a certain way? (don't know if that makes sense either)

|||Just an FYI,

I never was able to get the LinkedServer to work properly. I wound up creating some DSNs using the DataDirect 32-BIT SequeLink 5.4 driver on my server and a Web Service in VB.NET to access them.

|||Just a little more follow up.

I had to create a connection for each table or 'FORM' in FileMaker.

The code I'm using return the data set would be similar to the following;

Code Snippet

sSQL = "SELECT * FROM Stock_DNA WHERE ABRC_stock_number = '" & StockNo.Trim & "'"

Dim DA As Odbc.OdbcDataAdapter = New Odbc.OdbcDataAdapter(sSQL, oConnection)
Dim DS As DataSet = New DataSet
DA.Fill(DS, "Stock_DNA")
oConnection.Close()
oConnection.Dispose()
Return DS
End Function

Simple but it does the job.
|||

Actually, that looks very much like what I have. Does it crash for you if you try to load a table with a DATE column?

|||I have a 3-part integration project:

SQL 2005 -> Filemaker 8 -> SQL 2005

All parts ideally should work seamlessly. Part of my process is to run a query of data form SQL2005 and attempt to join in data from FileMaker. Unfortunately, I've never been able to get this to work via the FABULOUS drivers designed for supposedly just this sort of task. What I've ended up having to do is do all of my joining and subsequent querying in CODE (I'm using .NET as well, works fine). This is a less than ideal situation, especially as both the filemaker server and first sql2k5 server are on the same physical machine.

Anybody else manage to actually get linked servers working?

Linked Server issue

I have a problem querying a linked server connected via the OleDB provider for ODBC.

So using the above ODBC connection I added a linked server called 'DNA' and run the following query on the Stock_DNA table.

SELECT * FROM OPENQUERY(DNA, 'SELECT * FROM Stock_DNA')

The result I get is this;

OLE DB provider "MSDASQL" for linked server "DNA" returned message "Requested conversion is not supported.".

Msg 7341, Level 16, State 2, Line 1

Cannot get the current row value of column "[MSDASQL].Stock_number" from OLE DB provider "MSDASQL" for linked server "DNA".

Now, if I specify field names in the OPENQUERY statement so that no text columns are returned the query runs fine, but anytime I include the text columns it blows up.

I also can't use four part naming with out getting an error as follows;

I give the statement;

SELECT * FROM DNA...Stock_DNA

and get this;

Msg 7313, Level 16, State 1, Line 1

An invalid schema or catalog was specified for the provider "MSDASQL" for linked server "DNA".

Any help or work arounds would be greatly appreciated.

I have seen this twice - and i had to use a different solution each time:

1) define an index on the table you are selecting from - sometimes SQL can get confused without one.

2) define a view for the data you want and select from the view with the ODBC connection string.

Hope this helps - let me know how you get on.

Mike

|||

I am also having the "Cannot get the current row value" problem with text columns from an OPENQUERY call for a MySQL linked server. Does anyone know a fix for this problem?

|||

I am also trying to connect a Filemaker Pro database with .Net. Could you post your code?

Here is my attempt to load a DataGridView with Filemaker Pro data:

//connection opens okay

|||Chekc whether the data types between the source and target are similar or matches the requirement such varchar to varchar or nvarchar to nvarchar in thsi case.|||

Thanks Satya. With more testing, I have found that sometimes I can pull a table out, but not always.

Types in table that can be loaded:

VARCHAR

Types in table the cannot be loaded:

DOUBLE, VARCHAR, DATE

I am loaing the data into a dataset. Is DATE a type that a dataset can't handle?

EDIT> Yes, the field that uses type DATE can't be loaded. Thanks for the tip.

|||Glad to know more from your feedback, as explained you might need to check the compatbility of fields between the source and SQL Server table.|||

I'm at the same point as Brandon C...whose question seems to not have been really addressed.

OLE DB provider "MSDASQL" for linked server "FILEMAKER8" returned message "Unspecified error".

OLE DB provider "MSDASQL" for linked server "FILEMAKER8" returned message "[DataDirect][ODBC SequeLink driver]The qualifier 'dbschema' was larger than SQL_MAX_QUALIFIER_LEN from SQLGetInfo (0).".

Msg 7311, Level 16, State 2, Line 1

Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDASQL" for linked server "FILEMAKER8". The provider supports the interface, but returns a failure code when it is used.

I'm gonna take a guess that the underlying DataDirect 32-BIT SequeLink 5.4 client driver doesn't support schemas? (If that makes sense).

Is it possible to force the MSDASQL provider to interpret all text column types a certain way? (don't know if that makes sense either)

|||Just an FYI,

I never was able to get the LinkedServer to work properly. I wound up creating some DSNs using the DataDirect 32-BIT SequeLink 5.4 driver on my server and a Web Service in VB.NET to access them.

|||Just a little more follow up.

I had to create a connection for each table or 'FORM' in FileMaker.

The code I'm using return the data set would be similar to the following;

Code Snippet

sSQL = "SELECT * FROM Stock_DNA WHERE ABRC_stock_number = '" & StockNo.Trim & "'"

Dim DA As Odbc.OdbcDataAdapter = New Odbc.OdbcDataAdapter(sSQL, oConnection)
Dim DS As DataSet = New DataSet
DA.Fill(DS, "Stock_DNA")
oConnection.Close()
oConnection.Dispose()
Return DS
End Function

Simple but it does the job.
|||

Actually, that looks very much like what I have. Does it crash for you if you try to load a table with a DATE column?

Wednesday, March 21, 2012

Linked Server from SQL Server 2005 (x64) to Sybase

Hi,

We have been accessing databases on ASE 12.5 (on HPUX) from MS SQL Server 2000 (x32) successfully for years. We do this via linked servers we create on MS SQL Server. When creating the linked server, we would specify the Microsoft OLE Provider for ODBC as the provider, and the data source is the name of a DSN created with the Sybase ASE ODBC driver (4.20.00.67) that points the the HPUX box on which are the Sybase databases.

We have also gotten this to work just fine on MS SQL Server 2005 (x32) Standard Edition SP1. However, we cannot seem to get this to work on MS SQL Server 2005 (x64) Enterprise Edition SP1. The Microsoft OLE DB Provider is not available as an option when creating the linked server on MS SQL Server 2005 (x64). We have tried specifying the SQL Native Client as the provider, but this results in no connection being made and reports the following error:

OLE DB provider "SQLNCLI" for linked server "GCMTESTSQLNATIVE" returned message "Login timeout expired".

OLE DB provider "SQLNCLI" for linked server "GCMTESTSQLNATIVE" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".

Msg 53, Level 16, State 1, Line 0

Named Pipes Provider: Could not open a connection to SQL Server [53].

Do we need to use a different ASE driver when accessing Sybase databases fom the MS SQL Server (x64) platform? Or is there a way to successfully create a linked server on MS SQL Server 2005 (x64) that can communicate with the 32-bit ASE ODBC driver?

Thanks,

Rocco M.

This post should help you out. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=381849&SiteID=1|||

Hello,

Thanks for the pointer.

So, it appears that it is not possible to have a linked server created in SQL Server 2005 x64 use a 32-bit DSN (i.e. created with a 32 bit driver). The answer is that I need to create a 64-bit DSN in order to create a linked server in SQL Server 2005 x64. The problem is that many vendors (i.e. Sybase) do not have a 64-bit driver.

Looks like our migration is SQL Server 2005 x64 will have to wait unless we can get this to work with perhaps a third party vendore such as Data Direct. Any one using Data Direct to connect to Sybase from SQL Server 2005 x64?

Thanks,

Rocco M.

|||

There exists a workaround that you can use until Sybase releases an OLE DB Provider for x64 (they currently have a 64 Bit ODBC Driver but you cannot use it with SqlServer 64 because there is no 64 bit MSDASQL)

Install a 32-bit Named Instance of SQL Server 2005 on the same machine.

Setup your Sybase Linked Server on the 32 bit instance and then setup the 32 Bit named instance as a Linked Server on the 64 Bit instance and be prepared to get creative with defining SYNONYM's and liberal user of EXEC () AT {Machinename}

basically something like this

64Bit SqlServer --> 32 Bit Sql Server 2005 -> Sybase ASE

|||

If anyone has found a way (using a 3rd-party solution or whatever) to get a linked server to Sybase from an x64 SQL Server installation, I'd love to hear about it.

Barring that, can ehosca (or someone) provide more detail about the workaround?

sql

Linked Server from SQL Server 2005 (x64) to Sybase

Hi,

OLE DB provider "SQLNCLI" for linked server "GCMTESTSQLNATIVE" returned message "Login timeout expired".

Msg 53, Level 16, State 1, Line 0

Named Pipes Provider: Could not open a connection to SQL Server [53].

Thanks,

Rocco M.

This post should help you out. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=381849&SiteID=1|||

Hello,

Thanks for the pointer.

Thanks,

Rocco M.

|||

Install a 32-bit Named Instance of SQL Server 2005 on the same machine.

basically something like this

64Bit SqlServer --> 32 Bit Sql Server 2005 -> Sybase ASE

|||

If anyone has found a way (using a 3rd-party solution or whatever) to get a linked server to Sybase from an x64 SQL Server installation, I'd love to hear about it.

Barring that, can ehosca (or someone) provide more detail about the workaround?

Linked server from Sql 2000 to Sql 2005

I'm having issue in connecting to sql 2005 from sql 2000 via linked server.

Please advice what I should do to overcome the problem.

Thanks.

-EC

Do you get an error message ?

http://www.sqlserver2005.de

|||

True that helps to resolve the issue.

Refer to http://blogs.msdn.com/sql_protocols/archive/2005/12/22/506607.aspx fyi.

|||

Error 7399: OLE DB provider 'SQL OLEDB' reported an error.

OLE DB error trace [OLE/DB Provider 'SQL OLEDB' IDBInitialize returned 0x80004005: ].

Problem still exist, please advice.

Monday, March 19, 2012

Linked server error in SQL 2005 RTM

I have a stored procedure which inserts data into a temp table by calling
another stored procedure via a linked server. It works fine under SQL 2000.
However I'm getting the below error after upgrading to SQL 2005. Below is
also an example of the code. If I take out the insert into the temp table
then the procedure executes without error.
insert into #rs
exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
@.param_string
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.BTW I am running on Windows 2003 SP1.
"Quinn" <dellsql@.newsgroups.nospam> wrote in message
news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
>I have a stored procedure which inserts data into a temp table by calling
>another stored procedure via a linked server. It works fine under SQL 2000.
>However I'm getting the below error after upgrading to SQL 2005. Below is
>also an example of the code. If I take out the insert into the temp table
>then the procedure executes without error.
> insert into #rs
> exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> @.param_string
>
> 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.
>|||Hi,
It seems to be a DTC issue. Is SQL server 2000 that works on the same box
with with 2005 instance. If not, could you download and run DTCPing, as
described in
in 306843 How To Troubleshoot MS DTC Firewall Issues
http://support.microsoft.com/?id=306843?
Also, use this in your script so that you may get more information about
the error:
SET IMPLICIT_TRANSACTIONS OFF
SET XACT_ABORT ON
BEGIN TRAN
insert into #rs
exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
@.param_string
COMMIT TRAN
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Quinn" <dellsql@.newsgroups.nospam>
| References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
| Subject: Re: Linked server error in SQL 2005 RTM
| Date: Fri, 11 Nov 2005 02:14:29 -0600
| Lines: 28
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| X-RFC2646: Format=Flowed; Response
| Message-ID: <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com 70.112.155.207
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410735
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| BTW I am running on Windows 2003 SP1.
|
| "Quinn" <dellsql@.newsgroups.nospam> wrote in message
| news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
| >I have a stored procedure which inserts data into a temp table by
calling
| >another stored procedure via a linked server. It works fine under SQL
2000.
| >However I'm getting the below error after upgrading to SQL 2005. Below
is
| >also an example of the code. If I take out the insert into the temp
table
| >then the procedure executes without error.
| >
| > insert into #rs
| >
| > exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
| > @.param_string
| >
| >
| > 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.
| >
| >
|
|
||||Yes. It worked fine on SQL 2000. DTCPING was also successful.
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl...
> Hi,
> It seems to be a DTC issue. Is SQL server 2000 that works on the same box
> with with 2005 instance. If not, could you download and run DTCPing, as
> described in
> in 306843 How To Troubleshoot MS DTC Firewall Issues
> http://support.microsoft.com/?id=306843?
> Also, use this in your script so that you may get more information about
> the error:
> SET IMPLICIT_TRANSACTIONS OFF
> SET XACT_ABORT ON
> BEGIN TRAN
> insert into #rs
> exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> @.param_string
>
> COMMIT TRAN
>
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> --
> | From: "Quinn" <dellsql@.newsgroups.nospam>
> | References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
> | Subject: Re: Linked server error in SQL 2005 RTM
> | Date: Fri, 11 Nov 2005 02:14:29 -0600
> | Lines: 28
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> | X-RFC2646: Format=Flowed; Response
> | Message-ID: <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com 70.112.155.207
> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410735
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | BTW I am running on Windows 2003 SP1.
> |
> | "Quinn" <dellsql@.newsgroups.nospam> wrote in message
> | news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
> | >I have a stored procedure which inserts data into a temp table by
> calling
> | >another stored procedure via a linked server. It works fine under SQL
> 2000.
> | >However I'm getting the below error after upgrading to SQL 2005. Below
> is
> | >also an example of the code. If I take out the insert into the temp
> table
> | >then the procedure executes without error.
> | >
> | > insert into #rs
> | >
> | > exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> | > @.param_string
> | >
> | >
> | > 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.
> | >
> | >
> |
> |
> |
>|||Looks like there is a problem with the server because pointing the linked
server to a different server works fine.
"Quinn" <dellsql@.newsgroups.nospam> wrote in message
news:uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl...
> Yes. It worked fine on SQL 2000. DTCPING was also successful.
> "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
> news:NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl...
>|||Hello,
Before we go further, I'd like to confirm the sitatution is that:
The linked server on SQL 2000 instance to this same server works fine.
The linked server on SQL 2005 instance to this server does not work
The linked server on SQL 2005 instance to another server does work.
DTCping works fine to that remote server from the problemtic server.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Quinn" <dellsql@.newsgroups.nospam>
| References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
<#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
<NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl>
<uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl>
| Subject: Re: Linked server error in SQL 2005 RTM
| Date: Sat, 12 Nov 2005 21:37:54 -0600
| Lines: 108
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| X-RFC2646: Format=Flowed; Response
| Message-ID: <ePYujOA6FHA.3296@.TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com 70.112.155.207
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410905
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Looks like there is a problem with the server because pointing the linked
| server to a different server works fine.
|
| "Quinn" <dellsql@.newsgroups.nospam> wrote in message
| news:uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl...
| > Yes. It worked fine on SQL 2000. DTCPING was also successful.
| >
| > "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
| > news:NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl...
| >> Hi,
| >>
| >> It seems to be a DTC issue. Is SQL server 2000 that works on the same
box
| >> with with 2005 instance. If not, could you download and run DTCPing,
as
| >> described in
| >>
| >> in 306843 How To Troubleshoot MS DTC Firewall Issues
| >> http://support.microsoft.com/?id=306843?
| >>
| >> Also, use this in your script so that you may get more information
about
| >> the error:
| >>
| >> SET IMPLICIT_TRANSACTIONS OFF
| >> SET XACT_ABORT ON
| >> BEGIN TRAN
| >>
| >> insert into #rs
| >>
| >> exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
| >> @.param_string
| >>
| >>
| >> COMMIT TRAN
| >>
| >>
| >> Best Regards,
| >>
| >> Peter Yang
| >> MCSE2000/2003, MCSA, MCDBA
| >> Microsoft Online Partner Support
| >>
| >> When responding to posts, please "Reply to Group" via your newsreader
so
| >> that others may learn and benefit from your issue.
| >>
| >> ========================================
=============
| >>
| >>
| >>
| >> This posting is provided "AS IS" with no warranties, and confers no
| >> rights.
| >>
| >> --
| >> | From: "Quinn" <dellsql@.newsgroups.nospam>
| >> | References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
| >> | Subject: Re: Linked server error in SQL 2005 RTM
| >> | Date: Fri, 11 Nov 2005 02:14:29 -0600
| >> | Lines: 28
| >> | X-Priority: 3
| >> | X-MSMail-Priority: Normal
| >> | X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
| >> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| >> | X-RFC2646: Format=Flowed; Response
| >> | Message-ID: <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
| >> | Newsgroups: microsoft.public.sqlserver.server
| >> | NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com
70.112.155.207
| >> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| >> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410735
| >> | X-Tomcat-NG: microsoft.public.sqlserver.server
| >> |
| >> | BTW I am running on Windows 2003 SP1.
| >> |
| >> | "Quinn" <dellsql@.newsgroups.nospam> wrote in message
| >> | news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
| >> | >I have a stored procedure which inserts data into a temp table by
| >> calling
| >> | >another stored procedure via a linked server. It works fine under
SQL
| >> 2000.
| >> | >However I'm getting the below error after upgrading to SQL 2005.
Below
| >> is
| >> | >also an example of the code. If I take out the insert into the temp
| >> table
| >> | >then the procedure executes without error.
| >> | >
| >> | > insert into #rs
| >> | >
| >> | > exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
| >> | > @.param_string
| >> | >
| >> | >
| >> | > 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.
| >> | >
| >> | >
| >> |
| >> |
| >> |
| >>
| >
| >
|
|
||||I also have this problem.
I have SQL Server 2005 Express on a Windows XP SP 2 machine and on a Windows
2003 Server machine. I have configured DTC for both machines per the
documentation that is out there. I have tried many different combinations of
settings and so forth, with no success.
I can successfully execute queries from the Windows XP SP 2 machine once the
servers are linked, but when I try to do a distributed transaction, I get th
e
same error.
This was working with MSDE.
-Corey
"Peter Yang [MSFT]" wrote:

> Hello,
> Before we go further, I'd like to confirm the sitatution is that:
> The linked server on SQL 2000 instance to this same server works fine.
> The linked server on SQL 2005 instance to this server does not work
> The linked server on SQL 2005 instance to another server does work.
> DTCping works fine to that remote server from the problemtic server.
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
>
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> --
> | From: "Quinn" <dellsql@.newsgroups.nospam>
> | References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
> <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
> <NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl>
> <uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl>
> | Subject: Re: Linked server error in SQL 2005 RTM
> | Date: Sat, 12 Nov 2005 21:37:54 -0600
> | Lines: 108
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> | X-RFC2646: Format=Flowed; Response
> | Message-ID: <ePYujOA6FHA.3296@.TK2MSFTNGP09.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com 70.112.155.207
> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410905
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | Looks like there is a problem with the server because pointing the linke
d
> | server to a different server works fine.
> |
> | "Quinn" <dellsql@.newsgroups.nospam> wrote in message
> | news:uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl...
> | > Yes. It worked fine on SQL 2000. DTCPING was also successful.
> | >
> | > "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
> | > news:NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl...
> | >> Hi,
> | >>
> | >> It seems to be a DTC issue. Is SQL server 2000 that works on the same
> box
> | >> with with 2005 instance. If not, could you download and run DTCPing,
> as
> | >> described in
> | >>
> | >> in 306843 How To Troubleshoot MS DTC Firewall Issues
> | >> http://support.microsoft.com/?id=306843?
> | >>
> | >> Also, use this in your script so that you may get more information
> about
> | >> the error:
> | >>
> | >> SET IMPLICIT_TRANSACTIONS OFF
> | >> SET XACT_ABORT ON
> | >> BEGIN TRAN
> | >>
> | >> insert into #rs
> | >>
> | >> exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> | >> @.param_string
> | >>
> | >>
> | >> COMMIT TRAN
> | >>
> | >>
> | >> Best Regards,
> | >>
> | >> Peter Yang
> | >> MCSE2000/2003, MCSA, MCDBA
> | >> Microsoft Online Partner Support
> | >>
> | >> When responding to posts, please "Reply to Group" via your newsreader
> so
> | >> that others may learn and benefit from your issue.
> | >>
> | >> ========================================
=============
> | >>
> | >>
> | >>
> | >> This posting is provided "AS IS" with no warranties, and confers no
> | >> rights.
> | >>
> | >> --
> | >> | From: "Quinn" <dellsql@.newsgroups.nospam>
> | >> | References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
> | >> | Subject: Re: Linked server error in SQL 2005 RTM
> | >> | Date: Fri, 11 Nov 2005 02:14:29 -0600
> | >> | Lines: 28
> | >> | X-Priority: 3
> | >> | X-MSMail-Priority: Normal
> | >> | X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
> | >> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> | >> | X-RFC2646: Format=Flowed; Response
> | >> | Message-ID: <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
> | >> | Newsgroups: microsoft.public.sqlserver.server
> | >> | NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com
> 70.112.155.207
> | >> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.g
bl
> | >> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:41073
5
> | >> | X-Tomcat-NG: microsoft.public.sqlserver.server
> | >> |
> | >> | BTW I am running on Windows 2003 SP1.
> | >> |
> | >> | "Quinn" <dellsql@.newsgroups.nospam> wrote in message
> | >> | news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
> | >> | >I have a stored procedure which inserts data into a temp table by
> | >> calling
> | >> | >another stored procedure via a linked server. It works fine under
> SQL
> | >> 2000.
> | >> | >However I'm getting the below error after upgrading to SQL 2005.
> Below
> | >> is
> | >> | >also an example of the code. If I take out the insert into the tem
p
> | >> table
> | >> | >then the procedure executes without error.
> | >> | >
> | >> | > insert into #rs
> | >> | >
> | >> | > exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1
,
> | >> | > @.param_string
> | >> | >
> | >> | >
> | >> | > OLE DB provider "SQLNCLI" for linked server "linkedserver" return
ed
> | >> | > 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.
> | >> | >
> | >> | >
> | >> |
> | >> |
> | >> |
> | >>
> | >
> | >
> |
> |
> |
>

Linked server error in SQL 2005 RTM

I have a stored procedure which inserts data into a temp table by calling
another stored procedure via a linked server. It works fine under SQL 2000.
However I'm getting the below error after upgrading to SQL 2005. Below is
also an example of the code. If I take out the insert into the temp table
then the procedure executes without error.
insert into #rs
exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
@.param_string
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.
BTW I am running on Windows 2003 SP1.
"Quinn" <dellsql@.newsgroups.nospam> wrote in message
news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
>I have a stored procedure which inserts data into a temp table by calling
>another stored procedure via a linked server. It works fine under SQL 2000.
>However I'm getting the below error after upgrading to SQL 2005. Below is
>also an example of the code. If I take out the insert into the temp table
>then the procedure executes without error.
> insert into #rs
> exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> @.param_string
>
> 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.
>
|||Hi,
It seems to be a DTC issue. Is SQL server 2000 that works on the same box
with with 2005 instance. If not, could you download and run DTCPing, as
described in
in 306843 How To Troubleshoot MS DTC Firewall Issues
http://support.microsoft.com/?id=306843?
Also, use this in your script so that you may get more information about
the error:
SET IMPLICIT_TRANSACTIONS OFF
SET XACT_ABORT ON
BEGIN TRAN
insert into #rs
exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
@.param_string
COMMIT TRAN
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Quinn" <dellsql@.newsgroups.nospam>
| References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
| Subject: Re: Linked server error in SQL 2005 RTM
| Date: Fri, 11 Nov 2005 02:14:29 -0600
| Lines: 28
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| X-RFC2646: Format=Flowed; Response
| Message-ID: <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com 70.112.155.207
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP09.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410735
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| BTW I am running on Windows 2003 SP1.
|
| "Quinn" <dellsql@.newsgroups.nospam> wrote in message
| news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
| >I have a stored procedure which inserts data into a temp table by
calling
| >another stored procedure via a linked server. It works fine under SQL
2000.
| >However I'm getting the below error after upgrading to SQL 2005. Below
is
| >also an example of the code. If I take out the insert into the temp
table
| >then the procedure executes without error.
| >
| > insert into #rs
| >
| > exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
| > @.param_string
| >
| >
| > 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.
| >
| >
|
|
|
|||Yes. It worked fine on SQL 2000. DTCPING was also successful.
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl...
> Hi,
> It seems to be a DTC issue. Is SQL server 2000 that works on the same box
> with with 2005 instance. If not, could you download and run DTCPing, as
> described in
> in 306843 How To Troubleshoot MS DTC Firewall Issues
> http://support.microsoft.com/?id=306843?
> Also, use this in your script so that you may get more information about
> the error:
> SET IMPLICIT_TRANSACTIONS OFF
> SET XACT_ABORT ON
> BEGIN TRAN
> insert into #rs
> exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> @.param_string
>
> COMMIT TRAN
>
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> --
> | From: "Quinn" <dellsql@.newsgroups.nospam>
> | References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
> | Subject: Re: Linked server error in SQL 2005 RTM
> | Date: Fri, 11 Nov 2005 02:14:29 -0600
> | Lines: 28
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> | X-RFC2646: Format=Flowed; Response
> | Message-ID: <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com 70.112.155.207
> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP09.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410735
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | BTW I am running on Windows 2003 SP1.
> |
> | "Quinn" <dellsql@.newsgroups.nospam> wrote in message
> | news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
> | >I have a stored procedure which inserts data into a temp table by
> calling
> | >another stored procedure via a linked server. It works fine under SQL
> 2000.
> | >However I'm getting the below error after upgrading to SQL 2005. Below
> is
> | >also an example of the code. If I take out the insert into the temp
> table
> | >then the procedure executes without error.
> | >
> | > insert into #rs
> | >
> | > exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> | > @.param_string
> | >
> | >
> | > 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.
> | >
> | >
> |
> |
> |
>
|||Looks like there is a problem with the server because pointing the linked
server to a different server works fine.
"Quinn" <dellsql@.newsgroups.nospam> wrote in message
news:uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl...
> Yes. It worked fine on SQL 2000. DTCPING was also successful.
> "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
> news:NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl...
>
|||Hello,
Before we go further, I'd like to confirm the sitatution is that:
The linked server on SQL 2000 instance to this same server works fine.
The linked server on SQL 2005 instance to this server does not work
The linked server on SQL 2005 instance to another server does work.
DTCping works fine to that remote server from the problemtic server.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Quinn" <dellsql@.newsgroups.nospam>
| References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
<#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
<NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl>
<uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl>
| Subject: Re: Linked server error in SQL 2005 RTM
| Date: Sat, 12 Nov 2005 21:37:54 -0600
| Lines: 108
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| X-RFC2646: Format=Flowed; Response
| Message-ID: <ePYujOA6FHA.3296@.TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com 70.112.155.207
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP09.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410905
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Looks like there is a problem with the server because pointing the linked
| server to a different server works fine.
|
| "Quinn" <dellsql@.newsgroups.nospam> wrote in message
| news:uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl...
| > Yes. It worked fine on SQL 2000. DTCPING was also successful.
| >
| > "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
| > news:NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl...
| >> Hi,
| >>
| >> It seems to be a DTC issue. Is SQL server 2000 that works on the same
box
| >> with with 2005 instance. If not, could you download and run DTCPing,
as
| >> described in
| >>
| >> in 306843 How To Troubleshoot MS DTC Firewall Issues
| >> http://support.microsoft.com/?id=306843?
| >>
| >> Also, use this in your script so that you may get more information
about
| >> the error:
| >>
| >> SET IMPLICIT_TRANSACTIONS OFF
| >> SET XACT_ABORT ON
| >> BEGIN TRAN
| >>
| >> insert into #rs
| >>
| >> exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
| >> @.param_string
| >>
| >>
| >> COMMIT TRAN
| >>
| >>
| >> Best Regards,
| >>
| >> Peter Yang
| >> MCSE2000/2003, MCSA, MCDBA
| >> Microsoft Online Partner Support
| >>
| >> When responding to posts, please "Reply to Group" via your newsreader
so
| >> that others may learn and benefit from your issue.
| >>
| >> ================================================== ===
| >>
| >>
| >>
| >> This posting is provided "AS IS" with no warranties, and confers no
| >> rights.
| >>
| >> --
| >> | From: "Quinn" <dellsql@.newsgroups.nospam>
| >> | References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
| >> | Subject: Re: Linked server error in SQL 2005 RTM
| >> | Date: Fri, 11 Nov 2005 02:14:29 -0600
| >> | Lines: 28
| >> | X-Priority: 3
| >> | X-MSMail-Priority: Normal
| >> | X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
| >> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| >> | X-RFC2646: Format=Flowed; Response
| >> | Message-ID: <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
| >> | Newsgroups: microsoft.public.sqlserver.server
| >> | NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com
70.112.155.207
| >> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP09.phx.gbl
| >> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410735
| >> | X-Tomcat-NG: microsoft.public.sqlserver.server
| >> |
| >> | BTW I am running on Windows 2003 SP1.
| >> |
| >> | "Quinn" <dellsql@.newsgroups.nospam> wrote in message
| >> | news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
| >> | >I have a stored procedure which inserts data into a temp table by
| >> calling
| >> | >another stored procedure via a linked server. It works fine under
SQL
| >> 2000.
| >> | >However I'm getting the below error after upgrading to SQL 2005.
Below
| >> is
| >> | >also an example of the code. If I take out the insert into the temp
| >> table
| >> | >then the procedure executes without error.
| >> | >
| >> | > insert into #rs
| >> | >
| >> | > exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
| >> | > @.param_string
| >> | >
| >> | >
| >> | > 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.
| >> | >
| >> | >
| >> |
| >> |
| >> |
| >>
| >
| >
|
|
|
|||I also have this problem.
I have SQL Server 2005 Express on a Windows XP SP 2 machine and on a Windows
2003 Server machine. I have configured DTC for both machines per the
documentation that is out there. I have tried many different combinations of
settings and so forth, with no success.
I can successfully execute queries from the Windows XP SP 2 machine once the
servers are linked, but when I try to do a distributed transaction, I get the
same error.
This was working with MSDE.
-Corey
"Peter Yang [MSFT]" wrote:

> Hello,
> Before we go further, I'd like to confirm the sitatution is that:
> The linked server on SQL 2000 instance to this same server works fine.
> The linked server on SQL 2005 instance to this server does not work
> The linked server on SQL 2005 instance to another server does work.
> DTCping works fine to that remote server from the problemtic server.
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
> --
> | From: "Quinn" <dellsql@.newsgroups.nospam>
> | References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
> <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
> <NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl>
> <uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl>
> | Subject: Re: Linked server error in SQL 2005 RTM
> | Date: Sat, 12 Nov 2005 21:37:54 -0600
> | Lines: 108
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> | X-RFC2646: Format=Flowed; Response
> | Message-ID: <ePYujOA6FHA.3296@.TK2MSFTNGP09.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com 70.112.155.207
> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP09.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410905
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | Looks like there is a problem with the server because pointing the linked
> | server to a different server works fine.
> |
> | "Quinn" <dellsql@.newsgroups.nospam> wrote in message
> | news:uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl...
> | > Yes. It worked fine on SQL 2000. DTCPING was also successful.
> | >
> | > "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
> | > news:NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl...
> | >> Hi,
> | >>
> | >> It seems to be a DTC issue. Is SQL server 2000 that works on the same
> box
> | >> with with 2005 instance. If not, could you download and run DTCPing,
> as
> | >> described in
> | >>
> | >> in 306843 How To Troubleshoot MS DTC Firewall Issues
> | >> http://support.microsoft.com/?id=306843?
> | >>
> | >> Also, use this in your script so that you may get more information
> about
> | >> the error:
> | >>
> | >> SET IMPLICIT_TRANSACTIONS OFF
> | >> SET XACT_ABORT ON
> | >> BEGIN TRAN
> | >>
> | >> insert into #rs
> | >>
> | >> exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> | >> @.param_string
> | >>
> | >>
> | >> COMMIT TRAN
> | >>
> | >>
> | >> Best Regards,
> | >>
> | >> Peter Yang
> | >> MCSE2000/2003, MCSA, MCDBA
> | >> Microsoft Online Partner Support
> | >>
> | >> When responding to posts, please "Reply to Group" via your newsreader
> so
> | >> that others may learn and benefit from your issue.
> | >>
> | >> ================================================== ===
> | >>
> | >>
> | >>
> | >> This posting is provided "AS IS" with no warranties, and confers no
> | >> rights.
> | >>
> | >> --
> | >> | From: "Quinn" <dellsql@.newsgroups.nospam>
> | >> | References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
> | >> | Subject: Re: Linked server error in SQL 2005 RTM
> | >> | Date: Fri, 11 Nov 2005 02:14:29 -0600
> | >> | Lines: 28
> | >> | X-Priority: 3
> | >> | X-MSMail-Priority: Normal
> | >> | X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
> | >> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> | >> | X-RFC2646: Format=Flowed; Response
> | >> | Message-ID: <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
> | >> | Newsgroups: microsoft.public.sqlserver.server
> | >> | NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com
> 70.112.155.207
> | >> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP09.phx.gbl
> | >> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410735
> | >> | X-Tomcat-NG: microsoft.public.sqlserver.server
> | >> |
> | >> | BTW I am running on Windows 2003 SP1.
> | >> |
> | >> | "Quinn" <dellsql@.newsgroups.nospam> wrote in message
> | >> | news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
> | >> | >I have a stored procedure which inserts data into a temp table by
> | >> calling
> | >> | >another stored procedure via a linked server. It works fine under
> SQL
> | >> 2000.
> | >> | >However I'm getting the below error after upgrading to SQL 2005.
> Below
> | >> is
> | >> | >also an example of the code. If I take out the insert into the temp
> | >> table
> | >> | >then the procedure executes without error.
> | >> | >
> | >> | > insert into #rs
> | >> | >
> | >> | > exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> | >> | > @.param_string
> | >> | >
> | >> | >
> | >> | > 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.
> | >> | >
> | >> | >
> | >> |
> | >> |
> | >> |
> | >>
> | >
> | >
> |
> |
> |
>

Monday, March 26, 2012

Linked Server Problem

Linked Server Performance

Friday, March 23, 2012

Linked Server Issues

Linked Server Issues

Linked Server Issues

Linked Server issue

Linked Server issue

Linked Server issue

Linked Server issue

Linked Server issue

Linked Server issue

Wednesday, March 21, 2012

Linked Server from SQL Server 2005 (x64) to Sybase

Linked Server from SQL Server 2005 (x64) to Sybase

Linked server from Sql 2000 to Sql 2005

Monday, March 19, 2012

Linked server error in SQL 2005 RTM

Linked server error in SQL 2005 RTM

Sql Linked Server

Blog Archive

About Me