Linked Server Problem from SQL 2005 to SQL 2000 Database

I have been unable to get my linked server to work when running queries against it in SQL Server 2005.

Example SQL Statement on my SQL 2005 server that I'm trying to run:

select top 100 * from [linkedservername].databsename.dbo.tablename

Error Message:

Msg 7416, Level 16, State 1, Line 1

Access to the remote server is denied because no login-mapping exists.

Print screen of the security properties of my SQL 2005 Linked Server:

http://www.webfound.net/linkedserver.jpg

The user sqladmin definitely has the right permissions to the right databases on the target linked server.

On your screenshot, your login is dschinkel, it doesn't have a mapping. If you want all of your accounts to automatically use the apexfm\sqladmin account, you should select the radio button that says "Be made using this security context:" and fill in the login information there.

Be advised that if SQLAdmin has full administrative privileges on the remote server than ANY user with access privileges to the local server will have full admin privileges to the remote server. A very scary proposition indeed.

sql

Linked Server and unable to begin a distributed transaction

I have a database containing my own tables and data and I wanted to
be able to query this against an accountancy program which has an ODBC
driver. This was never a problem with MS Access and Jet but I hit Jet's
limitations and have moved to SQL.
Creating my own SQL database was no problem, but I was unsure of the
best way to be able to be able to have my SQL tables and my accountancy
software tables appearing in the same Access front end.
I created a linked server to the accountancy program. This was
successful in that I could see all the tables below the linked server in
enterprise manager.
My problem occurs when I try to bring the data from these tables
into my SQL database.
I create a new view in my database:-

SELECT *
FROM OPENQUERY(SAGE_SERVER, 'SELECT * FROM STOCK')

(My linked server is called 'SAGE_SERVER' and I am trying to retrieve
all columns from the STOCK table.)

I then try to save this view and get the following errors.

ODBC Error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
could not be performed because the OLE DB provider 'MSDASQL' was unable
to begin a distributed transaction.
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB Error Trace[OLE/DB
Provider 'MSDASQL' ITransactionJoi JoinTransaction returned 0x8004d00a].

Thanks in advance,

Marcus Thornton."Marcus" <marcus@.automint.co.uk> wrote in message
news:MPG.19748ac86a8335b3989681@.192.168.1.50...
> I have a database containing my own tables and data and I wanted to
> be able to query this against an accountancy program which has an ODBC
> driver. This was never a problem with MS Access and Jet but I hit Jet's
> limitations and have moved to SQL.
> Creating my own SQL database was no problem, but I was unsure of the
> best way to be able to be able to have my SQL tables and my accountancy
> software tables appearing in the same Access front end.
> I created a linked server to the accountancy program. This was
> successful in that I could see all the tables below the linked server in
> enterprise manager.
> My problem occurs when I try to bring the data from these tables
> into my SQL database.
> I create a new view in my database:-
> SELECT *
> FROM OPENQUERY(SAGE_SERVER, 'SELECT * FROM STOCK')
> (My linked server is called 'SAGE_SERVER' and I am trying to retrieve
> all columns from the STOCK table.)
> I then try to save this view and get the following errors.
> ODBC Error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
> could not be performed because the OLE DB provider 'MSDASQL' was unable
> to begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB Error Trace[OLE/DB
> Provider 'MSDASQL' ITransactionJoi JoinTransaction returned 0x8004d00a].
> Thanks in advance,
> Marcus Thornton.

You might find this post (and the rest of the thread) useful - it has some
quite detailed information on troubleshooting distributed transaction
errors:

http://groups.google.com/groups?hl=...%40cpmsftngxa07

Simon

Linked Server -- unexpected return data length

Hello everybody,
Thank you for your attention.
I can get numeric data from a linked HP database but unable to pull character type data. Here is the error message:
Server: Msg 7347, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' returned an unexpected data length for the fixed-length column '[].CUSTPART'. The expected data length is 20, while the returned data length is 11.
I went thru Microsoft Knowledge Garden but didn't find any satisfactory answers. Your help is greatly appreciated.
Wei
Hi Wei,
Some 3rd party ODBC drivers behave differently than SQL Server ODBC driver
in the way
they handle fixed length char columns. The difference involves how the
length
indicator value for fixed length columns is handled. If SQL Server ODBC
driver is
being used, the length indicator buffer (last parameter to SQLBindCol) is
always
populated with the max length of the fixed length character column no
matter how
much data is actually in the given data field. The only exception to this
is NULL
values, where the length indicator buffer is populated with the value '-1'.
Some 3rd party ODBC drivers, however, do not take this approach and instead
populate the length indicator buffer with the exact number of bytes of
character
data in the current database field.
To Overcome this, set the trace flag 8765 and you should not face the
problem
To set the flag, in the SQL Server Properties on the General Tab, click on
Startup Parameters and add the new parameter as "-T8765" as one word
without the quotes.
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Thank you very much Ashish. It works like magic!! I wish I
had checked your answer earlier.
Wei

>--Original Message--
>Hi Wei,
>Some 3rd party ODBC drivers behave differently than SQL
Server ODBC driver
>in the way
>they handle fixed length char columns. The difference
involves how the
>length
>indicator value for fixed length columns is handled. If
SQL Server ODBC
>driver is
>being used, the length indicator buffer (last parameter
to SQLBindCol) is
>always
>populated with the max length of the fixed length
character column no
>matter how
>much data is actually in the given data field. The only
exception to this
>is NULL
>values, where the length indicator buffer is populated
with the value '-1'.
>Some 3rd party ODBC drivers, however, do not take this
approach and instead
>populate the length indicator buffer with the exact
number of bytes of
>character
>data in the current database field.
>To Overcome this, set the trace flag 8765 and you should
not face the
>problem
>To set the flag, in the SQL Server Properties on the
General Tab, click on
>Startup Parameters and add the new parameter as "-
T8765" as one word
>without the quotes.
>
>HTH
>Ashish
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>.
>

Linked Server -- unexpected return data length

Hello everybody,
Thank you for your attention.
I can get numeric data from a linked HP database but unable to pull characte
r type data. Here is the error message:
Server: Msg 7347, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' returned an unexpected data length for the fixed-l
ength column '[].CUSTPART'. The expected data length is 20, while the re
turned data length is 11.
I went thru Microsoft Knowledge Garden but didn't find any satisfactory answ
ers. Your help is greatly appreciated.
WeiHi Wei,
Some 3rd party ODBC drivers behave differently than SQL Server ODBC driver
in the way
they handle fixed length char columns. The difference involves how the
length
indicator value for fixed length columns is handled. If SQL Server ODBC
driver is
being used, the length indicator buffer (last parameter to SQLBindCol) is
always
populated with the max length of the fixed length character column no
matter how
much data is actually in the given data field. The only exception to this
is NULL
values, where the length indicator buffer is populated with the value '-1'.
Some 3rd party ODBC drivers, however, do not take this approach and instead
populate the length indicator buffer with the exact number of bytes of
character
data in the current database field.
To Overcome this, set the trace flag 8765 and you should not face the
problem
To set the flag, in the SQL Server Properties on the General Tab, click on
Startup Parameters and add the new parameter as "-T8765" as one word
without the quotes.
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thank you very much Ashish. It works like magic!! I wish I
had checked your answer earlier.
Wei

>--Original Message--
>Hi Wei,
>Some 3rd party ODBC drivers behave differently than SQL
Server ODBC driver
>in the way
>they handle fixed length char columns. The difference
involves how the
>length
>indicator value for fixed length columns is handled. If
SQL Server ODBC
>driver is
>being used, the length indicator buffer (last parameter
to SQLBindCol) is
>always
>populated with the max length of the fixed length
character column no
>matter how
>much data is actually in the given data field. The only
exception to this
>is NULL
>values, where the length indicator buffer is populated
with the value '-1'.
>Some 3rd party ODBC drivers, however, do not take this
approach and instead
>populate the length indicator buffer with the exact
number of bytes of
>character
>data in the current database field.
>To Overcome this, set the trace flag 8765 and you should
not face the
>problem
>To set the flag, in the SQL Server Properties on the
General Tab, click on
>Startup Parameters and add the new parameter as "-
T8765" as one word
>without the quotes.
>
>HTH
>Ashish
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>.
>