Linked Server Problems from SQL2005

When I run linked server queries from SQL2005 it fails.
Query Example :
SELECT * FROM [servername].master.dbo.sysobjects
Error:
OLE DB provider "SQLNCLI" for linked server " servername" returned
message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote
host.
Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection.
Notes:
The linked server is configured to use the current logins security
context. It does not matter whether I connect to SQL2000 or SQL2005.
I have a case with MS (6 hours on the phone yesterday), but no help
yet. All spn settings are as recommended by Microsoft. The MS engineer
states that SQL is not passing the login as indicated in a capture file
from netcap.
Any help is appreciated.
TerryHi
Is the remote server SQL2005? Have you tried using a SQL Server login? Can
you use openquery to return the result?
John
"Terry" wrote:

> When I run linked server queries from SQL2005 it fails.
> Query Example :
> SELECT * FROM [servername].master.dbo.sysobjects
> Error:
> OLE DB provider "SQLNCLI" for linked server " servername" returned
> message "Communication link failure".
> Msg 10054, Level 16, State 1, Line 0
> TCP Provider: An existing connection was forcibly closed by the remote
> host.
> Msg 18452, Level 14, State 1, Line 0
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection.
> Notes:
> The linked server is configured to use the current logins security
> context. It does not matter whether I connect to SQL2000 or SQL2005.
> I have a case with MS (6 hours on the phone yesterday), but no help
> yet. All spn settings are as recommended by Microsoft. The MS engineer
> states that SQL is not passing the login as indicated in a capture file
> from netcap.
> Any help is appreciated.
> Terry
>|||Terry wrote:
> When I run linked server queries from SQL2005 it fails.
> Query Example :
> SELECT * FROM [servername].master.dbo.sysobjects
> Error:
> OLE DB provider "SQLNCLI" for linked server " servername" returned
> message "Communication link failure".
> Msg 10054, Level 16, State 1, Line 0
> TCP Provider: An existing connection was forcibly closed by the remote
> host.
> Msg 18452, Level 14, State 1, Line 0
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection.
> Notes:
> The linked server is configured to use the current logins security
> context. It does not matter whether I connect to SQL2000 or SQL2005.
> I have a case with MS (6 hours on the phone yesterday), but no help
> yet. All spn settings are as recommended by Microsoft. The MS engineer
> states that SQL is not passing the login as indicated in a capture file
> from netcap.
> Any help is appreciated.
> Terry
>
You are connecting to ServerA using Windows Authentication, not a SQL
login. The linked server connection is attempting to pass your SQL
login over to ServerB. You'll have to login to ServerA using a true SQL
login, or setup an explicit user mapping in the linked server definition.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I should be able to use Windows Authenitication, and have always in the
past with SQL2000. Auditing concerns require I know who is connecting.
Mapped logins do work but this is not a solution.
The problem occurs when I connect to SQL2000 or SQL2005 from SQL2005.
Terry
Tracy McKibben wrote:
> Terry wrote:
> You are connecting to ServerA using Windows Authentication, not a SQL
> login. The linked server connection is attempting to pass your SQL
> login over to ServerB. You'll have to login to ServerA using a true SQL
> login, or setup an explicit user mapping in the linked server definition.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||On the 2000 server, run the instcat.sql from the latest service pack you
have installed. This fixes the connect strings. You have to edit the
instcat.sql to put in the correct SQL version. This is documented in the
instcat.sql file. This fixed my 2005->2000 linked server problems. Same as
your problem.
Jackie
"Terry" <tduffy@.calamos.com> wrote in message
news:1152881495.272827.29100@.h48g2000cwc.googlegroups.com...
>I should be able to use Windows Authenitication, and have always in the
> past with SQL2000. Auditing concerns require I know who is connecting.
> Mapped logins do work but this is not a solution.
> The problem occurs when I connect to SQL2000 or SQL2005 from SQL2005.
> Terry
> Tracy McKibben wrote:
>|||Thank you for the responses...
instcat.sql fixes the schema binding errors on 2005->2000 servers. It
does not resolve my issues. instcat has already been run on my 2000
boxes.
Terry
Jackie Brophy wrote:[vbcol=seagreen]
> On the 2000 server, run the instcat.sql from the latest service pack you
> have installed. This fixes the connect strings. You have to edit the
> instcat.sql to put in the correct SQL version. This is documented in the
> instcat.sql file. This fixed my 2005->2000 linked server problems. Same as
> your problem.
> Jackie
> "Terry" <tduffy@.calamos.com> wrote in message
> news:1152881495.272827.29100@.h48g2000cwc.googlegroups.com...|||Hi Terry
I assume that you are using the latest service packs on both instances?
Previously this error has been the result of a virus checker or firewall
blocking communications or possibly a faulty network. Do you get any command
s
running on the remote system?
Windows authentication should not require the user to map a linked server
login, but you can run it with the remote server name parameter only to make
sure these are mapped.
You may want to try and check what protocols you are using and try a
different one!
John
"Terry" wrote:

> Thank you for the responses...
> instcat.sql fixes the schema binding errors on 2005->2000 servers. It
> does not resolve my issues. instcat has already been run on my 2000
> boxes.
> Terry
> Jackie Brophy wrote:
>|||We are still on RTM SQL05, but similar issues (double hops only) exist
on a dev box with sp1. SQL 2000 is a mix of SP3 and SP4.
Firewall and anti-virus are not any issue. I am not sure what you mean
by "commands
running on the remote system." Also, any ideas what might be "faulty"
in the network.
Linked servers with mapped logins works.
Thanks
John Bell wrote:[vbcol=seagreen]
> Hi Terry
> I assume that you are using the latest service packs on both instances?
> Previously this error has been the result of a virus checker or firewall
> blocking communications or possibly a faulty network. Do you get any comma
nds
> running on the remote system?
> Windows authentication should not require the user to map a linked server
> login, but you can run it with the remote server name parameter only to ma
ke
> sure these are mapped.
> You may want to try and check what protocols you are using and try a
> different one!
> John
> "Terry" wrote:
>|||Hi Terry
Use SQL profiler on both machines to try and see what is happening. Did you
check the protocols being used?
http://msdn2.microsoft.com/en-us/library/ms175496.aspx
You may want to make sure that this still occurs on the latest hotfixes for
each version. If that fails you may want to raise an incident with PSS for
this.
John
"Terry" wrote:

> We are still on RTM SQL05, but similar issues (double hops only) exist
> on a dev box with sp1. SQL 2000 is a mix of SP3 and SP4.
> Firewall and anti-virus are not any issue. I am not sure what you mean
> by "commands
> running on the remote system." Also, any ideas what might be "faulty"
> in the network.
> Linked servers with mapped logins works.
> Thanks
>
> John Bell wrote:
>

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 - AS400 - Missing Data

I set up a linked server from SQL to an AS400 and it connects ok, but does
not return all data. For example, if the as400 file has 1059 records, it
returns 1001, if it has 4080, it will return 4004, and so on. The ODBC
connection works fine though, it is only when I use the ODBC connection thru
a Linked Server that I have a problem.
It appears as if it drops the last physical block.
Any help would be appreciated, this is driving me nuts.
Thanks,
DennyAre you accessing logical files or physical files on the
400? Also, what provider options were selected when this was
set up?
And...if you are using the CA drivers have you made sure the
CA on the SQL Server box is at that same patch level as your
AS400? Can't remember offhand...I think the option to check
and update it is on the Client Access menu somewhere.
-Sue
On Thu, 7 Sep 2006 15:34:33 -0700, "Denny"
<denny@.newsgroup.com> wrote:

>I set up a linked server from SQL to an AS400 and it connects ok, but does
>not return all data. For example, if the as400 file has 1059 records, it
>returns 1001, if it has 4080, it will return 4004, and so on. The ODBC
>connection works fine though, it is only when I use the ODBC connection thr
u
>a Linked Server that I have a problem.
>It appears as if it drops the last physical block.
>Any help would be appreciated, this is driving me nuts.
>Thanks,
>Denny
>

Linked Server - AS400 - Missing Data

I set up a linked server from SQL to an AS400 and it connects ok, but does
not return all data. For example, if the as400 file has 1059 records, it
returns 1001, if it has 4080, it will return 4004, and so on. The ODBC
connection works fine though, it is only when I use the ODBC connection thru
a Linked Server that I have a problem.
It appears as if it drops the last physical block.
Any help would be appreciated, this is driving me nuts.
Thanks,
DennyAre you accessing logical files or physical files on the
400? Also, what provider options were selected when this was
set up?
And...if you are using the CA drivers have you made sure the
CA on the SQL Server box is at that same patch level as your
AS400? Can't remember offhand...I think the option to check
and update it is on the Client Access menu somewhere.
-Sue
On Thu, 7 Sep 2006 15:34:33 -0700, "Denny"
<denny@.newsgroup.com> wrote:
>I set up a linked server from SQL to an AS400 and it connects ok, but does
>not return all data. For example, if the as400 file has 1059 records, it
>returns 1001, if it has 4080, it will return 4004, and so on. The ODBC
>connection works fine though, it is only when I use the ODBC connection thru
>a Linked Server that I have a problem.
>It appears as if it drops the last physical block.
>Any help would be appreciated, this is driving me nuts.
>Thanks,
>Denny
>

Linked Server

Hi,

Is there a way to batch INSERTs from a linked server?

For example, I'm importing data into a sql db using the following linked server query.

SELECT * INTO SQLDB.dbo.Table_SS FROM OPENQUERY (ORACLELINK, 'SELECT * FROM SCHEMA.Table_Ora')

If the table Table_Ora has say 5 million rows..........is there anyway that we can batch the INSERTs so that the query will commit at say every 30 or 40k rows?

or any other better approaches?

Thanks,
Siva.

There's a way for doing so in SQL, but you will not like it since it tends to unnecessary long times of execution. Much more suitable approach for this is a DTS / SSIS package (depending on version of MSSQL you are using) with Data Flow Task - in its settings you can specify the size, in rows, of such a batch.

Also, if this task was the only reason to create a linked server, you may easily get rid of it since DTS connects directly to the source server without any go-betweens.