Error Query Data Through Linked Server , SQL Server 2005

May 27, 2008

Hello,

I have a development and a production SQL server instance environment set up on 2 independent machines. Each machine is running Windows 2003 for an OS, while each server instance is version SQL Server 2005. On friday, I experienced difficulties querying one environment from the other through linked servers. I would get the error below:
.

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


The linked servers had been previously set up and had been running without any issues. Dropping and recreating the linked servers did not help at all, and all attempts to google the error led to accounts of either SQL Server 2005-SQL Server 2000 procedures compatibility or 64 bit - 32 bit compatibily related errors. Neither of the two were relevant as both my environment have the same technology, both hardware and software.

Mysteriously, the linked server worked this morning without any issue at all. One co-worker suggests gremlins are at work, while another figures that my set up had 'checked out for the long weekend'. Unfortunately, neither explanation is plausible, so my quest to find out what could have gone wrong, and hopefully put preventitive measures in place for the future goes on. Does anybody have any idea what the issue could have been?

Thanks,
Simba

View 1 Replies


ADVERTISEMENT

Running A Distributed Query Against A Loopback Linked Server In SQL Server 2005 Is Not Supported

Aug 27, 2007

I receive the following error message when I run a distributed query against a loopback linked server in SQL Server 2005:
The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.

To resolve this problem, I was told that running a distributed query against a loopback linked server is not supported in SQL Server 2005. And I am suggested to use a remote server definition (sp_addserver) instead of a linked server definition to resolve this problem. (Although this is only a temporary resolution, which will deprecate in Katmai)

However, I run into another problem when I use the remote server definition. I receive the following error message:
Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'ServerNameSQL2005' because '' is not defined as a remote login at the server. Verify that you have specified the correct login name.

Could anyone please help me out?
(I include the reproduce steps for the first error message, followed by my resolution that generates the second error message)
======
Reproduce steps for the first error message
======


On the ComputerAInstanceA instance, run the following statement to create a database and a table:
CREATE DATABASE DatabaseA
GO
USE DatabaseA
GO
CREATE TABLE TestTable(Col1 int, Col2 varchar(50))
GO
INSERT INTO TestTable VALUES (1, 'Hello World')
GO

On the ComputerBInstanceB instance, run the following statement to create a database and a table:
CREATE DATABASE DatabaseB
GO
USE DatabaseB
GO
CREATE TABLE TestTable (Col1 int, Col2 varchar(50))
GO

On the ComputerAInstanceA instance, create a linked server that links to the ComputerBInstanceB instance. Assume the name of the linked server is LNK_ServerB.

On the ComputerBInstanceB instance, create a linked server that links to the ComputerAInstanceA instance. Assume the name of the linked server is LNK_ServerA.

On the ComputerBInstanceB instance, run the following statement:
USE DatabaseB
GO
CREATE PROCEDURE InsertA AS
BEGIN
SELECT * from LNK_ServerA.DatabaseA.dbo.TestTable
END
GO

On the ComputerAInstanceA instance, run the following statement:
USE DatabaseA
GO
INSERT INTO TestTable
EXEC LNK_ServerB.DatabaseB.dbo.InsertA
GO
Then I receive the first error message.

=======
My resolution that generates the second error message
=======


On the ComputerBInstanceB instance, run the following statement:
sp_addserver 'ComputerAInstanceA'
GO
sp_serveroption 'ComputerAInstanceA', 'Data Access', 'TRUE'
GO
USE DatabaseB
GO
CREATE PROCEDURE InsertA AS
BEGIN
SELECT * FROM [ComputerAInstanceA].DatabaseA.dbo.TestTable
END
GO

On the ComputerAInstanceA instance, run the following statement:
USE DatabaseA
GO
INSERT INTO TestTable
EXECUTE [ComputerBInstanceB].[DatabaseB].[dbo].[InsertA]
GO
Then I receive the second error message.

View 1 Replies View Related

SQL Server 2000 To SQL 2005 Linked Server - Query Problem

Jan 16, 2008

Hello

SQL 2000: 8.00.2187 x86, 8 way 700mhz, 6GB Ram
SQL 2005: 9.00.3042 IA64 2 Way Dual-Core 1.66Mhz 16 GB ram

Symptoms

Querys to the SQL 2005 box from SQL 2000 work but when the query is parameterised with non-literals (@variables) then the query run on the SQL 2005 box excludes any where clause causing the entire table to be returned to the SQL 200 box. When the query is parameterised using literal values the query is executed on SQL 2005 including the where clause.

At first I thought that the "Collation Compatible" setting was the culprit but setting this to 1 made no difference. Other SQL 2000 boxes work as expected and any queries from these using literal and non-literal parameters.

Please, any ideas?



Working

SELECT A.Column FROM linkedServer.IA.dbo.Table Where A.Column = 'value'

Not Working (correctly anyway!)


DECALRE @Value tinyint
SET @Value = 22
SELECT A.Column FROM linkedServer.IA.dbo.Table Where A.Column = @value

View 5 Replies View Related

Linked Server 2000 To 2005: Error 17 Sql Server Does Not Exist Or Access Denined.

Aug 30, 2006

I'm trying to link SQL Server 2000 sp3 to SQL 2005 64 bit. I keep getting Error 17 sql server does not exist or access denined. I tried adding remote user mapping and chaning the linked server properties to "Be made using this security context" without any success.
Any help is appreciated.

View 4 Replies View Related

Problems Reading Data From Linked Server To Excel In SQL Server 2005

Oct 4, 2007

I have an Excel sheet that is dynamically updated (through DDE) and I want to import this data to a table in SQL Server 2005. Using SQL Server Management Studio to configure an Excel data source as a linked server.
(http://support.microsoft.com/kb/306397/EN-US/)

Following the first 5 steps should let me acces the table (but I cannot view the data in SQL Server 2005). However, I could not find how to export the data into an existing table. Does anyone know how or can give a pointer to document describing how to do this?

View 8 Replies View Related

SQL Server Admin 2014 :: Error While Updating Data Using Oracle Linked Server

Sep 11, 2015

We have oracle linked server created on one of the sql server 2008 standard , we are fetching data from oracle and updating some records in sql server . Previously its working fine but we are suddenly facing below issue.

Below error occurred during process .

OLE DB provider "OraOLEDB.Oracle" for linked server "<linkedservername>" returned message "".
Msg 7346, Level 16, State 2, Line 1
Cannot get the data of the row from the OLE DB provider "OraOLEDB.Oracle" for linked server "<linked server name>".

View 7 Replies View Related

Importing Data From Oracle 10g To Sql Server 2005 Using Linked Server

Jul 30, 2007

Hi,

I am using Windows 2003 server and Sqlserver 2005 by the use of Linked server , I made a connection to Oracle 10g after that I am importing records from Oracle to sqlserver 2005. When I made tnsnames.ora in sql machine , it worked fine but when i am using tnsnames file from oracle server then i fiired importing procedure it returns below maintain error :

OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Unspecified error".

OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Oracle error occurred, but error message could not be retrieved from Oracle.".

Msg 7311, Level 16, State 2, Line 1

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


Please let me know.

Thanks

View 15 Replies View Related

Linked Server Query Error

Feb 10, 2003

SQL 2000
I have a process that calls several stored procs which access a database on a linked server.
code that fails:

SELECT DISTINCT em.er_id, em.er_name, bp.bpo_id, bp.bpo_name
FROM [dbrptc13dayoldprod].ues.dbo.Employer em
Inner Join [dbrptc13dayoldprod].ues.dbo.BPO bp ON
em.er_bpoid = bp.bpo_id

Error message:
Server: Msg 913, Level 16, State 8, Line 1 Could not find database ID 6. Database may not be activated yet or may be in transition.

The database is accessible from query analyzer with a simple select from the linked server. Also if I change any letter in the ues.dbo.Employer em or ues.dbo.BPO bp part to a different case it works fine.
For example: -changed the BPO to BPo- this works!
SELECT DISTINCT em.er_id, em.er_name, bp.bpo_id, bp.bpo_name
FROM [dbrptc13dayoldprod].ues.dbo.Employer em
Inner Join [dbrptc13dayoldprod].ues.dbo.BPo bp ON
em.er_bpoid = bp.bpo_id


Please help I can't figure this one out.

Thanks.

View 1 Replies View Related

SQL Server 2005 X64 Linked Server Error

Jul 27, 2006

We use Windows 2003 Server (64) on AMD64 and SQL Server 2005 Developer Edition x64 + SP1
Problem:
I can not execute any sql on a linked server using the native Provider (SQLCLNI)
Example (create a linked server on the same machine, other database (msdb) and try to execute any simple select using OPENQUERY
sp_addlinkedserver @server='ls',@srvproduct='SQLNCLI',@provider='SQLNCLI'
,@provstr='Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=msdb;Data Source=localhost;'
Select * from OPENQUERY(ls,'Select * from Sysobjects')
Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "ls" supplied inconsistent metadata for a column. The column "name" (compile-time ordinal 1) of object "Select * from Sysobjects" was reported to have a "Incomplete schema-error logic." of 0 at compile time and 0 at run time.

View 11 Replies View Related

Linked Server Error From SQL 2005 X64 To X32

Nov 13, 2007

I have a problem I have spent hours on already. I have found multiple posts, but not a solution to this problem. This is not a rights issue.

I have two SQL 2005 servers (I cannot upgrade to SP2 yet):
ServerA - ver 9.00.2153.00 (X64)
ServerB - ver 9.00.2153.00 (Intel X86)


I create a linked server on ServerB pointed to ServerA, uses 'sa' for all logins, works perfectly.
I create a linked server on ServerA pointed to ServerB, uses 'sa' for all logins, FAILS.

Run on ServerA:

SELECT *
FROM OPENQUERY([ServerB], 'SELECT [field1] FROM database.dbo.tablea')

Returns:

Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "ServerB" supplied inconsistent metadata for a column. The column "field1" (compile-time ordinal 1) of object "SELECT [field1] FROM database.dbo.tablea" was reported to have a "Incomplete schema-error logic." of 0 at compile time and 0 at run time.



Run on ServerA:

SELECT [field1] FROM [ServerB].database.dbo.tablea

Returns:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "ServerB" reported an error. The provider did not give any information about the error.
Msg 7312, Level 16, State 1, Line 1
Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked server "ServerB". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema


Any bright ideas?

View 3 Replies View Related

Can See But Cannot Query DB2 Data Via Linked Server

Jun 13, 2001

I have an ODBC DSN that lets me see the data via Access, however, in EM I set up the DB2 server as a linked server using the OLEDB provider for ODBC, with a Linked server name of DB2DB, a product name of MY DB2 connection and the data source of db2 (which is the DSN name)
The Provider string, Location and Catalog I leave blank
Under security, I assign all users to be a valid username/password

I can then see the tables in EM, but when I try to query them in QA I get this

Select * from db2db...customer

Server: Msg 7313, Level 16, State 1 Line 1
Invalid Schema or catalog specified for provider 'MSDASQL'

I am using SS7.0, and DB2 connect V7.1
I do not have/use SNA server

My actual requirement is to have a job autmatically run every day to copy certain records onto the SQL Server, but the first step is to be able to query the DB2 data

any ideas ? I think I need to enter something in the catalog box, but what ?

thanks in advance.

View 1 Replies View Related

Query Data From Linked Server

Oct 12, 1999

I am trying to run a select statements against linked server.
I have NT server running SQL7 Enterprice and Rumba2000. I used Rumba driver to configure a DSN (ODBC based) to a AS400/DB2. Then I created a linked server. Good news are : I can see a list of tables.
Bad news : when I am trying to run a select statement either through stored procedures or directly in SQL analyzer I am receiving error message Object 'linkedservername.databasename.tablename' doesn't exist.
What am I missing here?

View 2 Replies View Related

Linked Server Query - Timeout Error Under Load

Apr 23, 2002

Hello,

I am researching alternatives to a stored procedure that uses a linked server query that my ASP page uses to retrieve records to the page.

Should I:

Use replication (Data is constantly updated, so a snapshot won't do. Merge replication?)

Break up the stored procedure into subqueries?

Use a view on the remote server instead of the actual tables?

I am trying to reduce the amount of table scans, what is the best way to do this?

Here is the stored procedure I am trying to tune:
(@startDate and @endDate are parameters passed from the web page):

CREATE PROCEDURE dbo.spELRMCcardXtionByDate @dcid nvarchar(255), @startDate datetime, @endDate datetime
AS
SELECT Store.[Str#], Store.[Dcid#], E.card_number, E.program_number
, E.start_date, E.end_date, E.card_number, E.event_number
, E.status, E.budget, E.scheduled_date, P.tx_time, P.purchase_amount
, L.merchant_name
FROM (Store INNER JOIN Event E ON Store.[DemoID#] = E.event_number)
LEFT JOIN (Location L RIGHT JOIN POS_TX P ON L.location_number = P.location_number)
ON E.event_number = P.event_number
WHERE (((Store.[Dcid#])= @dcid)) AND E.scheduled_date BETWEEN @startDate AND @endDate
ORDER BY Store.[Str#]
GO

Any suggestions on my sp or anything else that can implement would be greatly appreciated.

TIA,

Bruce Johnston
Programmer/Analyst

View 1 Replies View Related

SQL Server + Oracle Data Linked In One Query

Jun 9, 2006

I need to link some data from SQL Server 2005 with Oracle 10 data.One way is to link Oracle server to SQL Server and use ROWSOURCE forretrieving data.What other ways for joing data from both databases exist ?Can I do it from SQLCRL VB - Stored Procedure ? If yes, what objectsshall I use for opening database and running an sql ?
When I tried to import system.data.oracleclient, it was not available. Do I need to install anything for being able to use it ?
Thanks a lot.

View 4 Replies View Related

Problem Query Data Through Linked Server.

Aug 16, 2007

Hi,

Please bear with me if my vocabulary or phraseology is wrong (and if it is, I wouldn’t mind being corrected).

Background:

We have an application that uses a Cache database (which is a complete black box for me). The data is exposed to us through ODBC. We have set up a linked server that I can see the names of the tables and views in Enterprise Manager and can query on them with Query Analyzer.

Problem:

There are a lot of extra long string fields. I can see the data in both Crystal Reports and Access (in Crystal they are shown defined as sting [32395] and in Access they are shown as a Memo field).

I cannot get Query Analyzer to display the data. I have tried the following select statements:

select definition_text from cwsavpmtest_live..system.problem_def_data

select cast(definition_text as ntext) from cwsavpmtest_live..system.problem_def_data
select convert(ntext,definition_text) from cwsavpmtest_live..system.problem_def_data

select cast(definition_text as text) from cwsavpmtest_live..system.problem_def_data
select convert(text,definition_text) from cwsavpmtest_live..system.problem_def_data

select cast(definition_text as varchar(8000)) from cwsavpmtest_live..system.problem_def_data
select convert(varchar(8000), definition_text) from cwsavpmtest_live..system.problem_def_data

… and get the same error for every one:
quote:
Server: Msg 7341, Level 16, State 2, Line 1
Could not get the current row value of column '[cwsavpmtest_live]..[system].[problem_def_data].definition_text' from the OLE DB provider 'MSDASQL'.
[OLE/DB provider returned message: Requested conversion is not supported.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowset::GetData returned 0x80040e1d].

So my question is: Am I out of luck and have to be content with looking at the data in Access or Crystal or is there some way I can look at these fields through Query Analyzer?

Thanks,

Laurie

View 1 Replies View Related

SQL Server + Oracle Data Linked In One Query

Jun 8, 2006

I need to link some data from SQL Server 2005 with Oracle 10 data.One way is to link Oracle server to SQL Server and use ROWSOURCE forretrieving data.What other ways for joing data from both databases exist ?Can I do it from SQLCRL VB - Stored Procedure ? If yes, what objectsshall I use for opening database and running an sql ?Thanks a lot.

View 3 Replies View Related

Error In Accessing Data Via Linked Server

Nov 23, 2006

 

Hi,

     I am trying to access the View through linked server connection .The linked server connection is between two sql server 2005.

    The problem is I am not able to access 2 columns of nvarchar datatype of length 255, from the view through select statement.

     I do a SELECT col1,col2 from VIEW,then getting the following error .

     Cannot get the data of the row from the OLE DB provider "SQLNCLI" for linked server "SRVXPR". Could not convert the data value due to reasons other than sign mismatch or overflow.


  Then I tried with

      Select  cast(Col1 as nvarchar) ,

                  cast(col2 as nvarchar) from VIEW,

     I am able to get the values.

    The collation type is same for all underlying tables and also for the servers.

     Is there any restrictions in handling nvarchar data thru linked server/ anyother size limitation.

     I don't want to use cast as this will lead to performance problem.       

 

Thanks,

Philip

 

                   

                   

 

View 5 Replies View Related

Linked Server Query To Retrieve Data From Two Different Instances

May 12, 2015

I know how to use Linked server query to retrieve data from two different sql on premise instances.I would like to try the same on sql server instances hosted on azure.When I connect to sql instance, I don't see ServerOBjects->Linked server. I just see Database and security.Is this possible on sql azure, if so how can we achieve it

View 4 Replies View Related

SQl 2005 Express - Linked Server - Access ADP - MSDASQL Error

Jan 8, 2008

Hi everyone,

I'v finally managed to get a New Linked Server setup on SQL 2005 Express SP2 using the 'Microsoft OLE DB Provider for ODBC Drivers' MSDASQL. I have a Database setup on the SQL 2005 Express Database Engine which contains a query that OPENQUERY's the linked server. The query executes OK.

The problems in when I link an Access 2003 SP2 .adp file to the SQL 2005 Database Engine database. All objects, including the query containing the OPENQUERY (to the linked server) appear. However, when I run the same query within Access I get the following error:

"The OLE DB Provider for Linked Server "name" reported an error."
"The Provider reported an unexpected catastropic failure"

So the query works running within SQL 2005 Management Studio, but the same query doesn't work running within an Access 2003 SP2 ADP. I've closed down SQL 2005 Management Studio, but the same error is displayed.

I didn't know if the problem lies in Access 2003 SP2 or SQL 2005 Express SP2.

Any takers?? (I haven't logged this Thread in an Access forum)

Any assistance is appreciated.

Alan

View 1 Replies View Related

Oracle Linked Server Connection Error After Upgrading To SQL 2005

Mar 14, 2007

Hello all.

I upgraded a SQL 2000 installation to SQL 2005 this past weekend. Minimal issues overall, but one that did crop up relates to a linked server to an Oracle database. The linked server worked before the upgrade, and stopped working immediately after.

We're running on Windows 2003 x64, latest service pack. The linked server is set up using the MSDAORA provider. The error I'm getting post-upgrade is:

=====================

OLE DB provider "MSDAORA" for linked server "PROD" returned message "ORA-06413: Connection not open.".

Msg 7303, Level 16, State 1, Procedure RDM_GET_REP_LIST_SP, Line 12

Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "PROD".

=====================

A tnsping from the server works fine. I've also tried recreating the linked server. Any help would be appreciated. Let me know what other information would be useful.

Thanks,

Adam

View 3 Replies View Related

Error Querying Data From DB2 Linked Server Via IBMDASQL

May 2, 2007

I've setup a linked server in SQL 2005 x64 SP2 to retrieve data from OS/400 DB2. When I perform a query on the linked server (select * from openquery(<linkedserver>, "select * from LIB.FILE1'), the following error was returned.

Error:
Msg 7372, Level 16, State 4, Line 1
Cannot get properties from OLE DB provider "IBMDASQL" for linked server "<linkedserver>".I used the same linked server setup procedure on another SQl server with same configuration (but SP1) 6 months ago and it was OK.SQL Server Configuration:SQL 2005 x64 SP2 Windows 2003 SP1iSeries Access V5R3M0 patch SI24723
Linked Server Script:
/****** Object: LinkedServer [OS400] Script Date: 05/02/2007 15:21:24 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'STEMMS1', @srvproduct=N'OS400', @provider=N'IBMDASQL', @datasrc=N'<linkedserver>', @catalog=N'S654803D'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'connect timeout', @optvalue=N'60'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'query timeout', @optvalue=N'120'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'use remote collation', @optvalue=N'true'
I've tried searching the Internet for solution but yielded no results. Can anyone help?

View 3 Replies View Related

SQLNCLI --Linked Server Provider -- Nested Query Problem (SQL 2005)

Nov 30, 2007

Ok here is a run down of the situation:

I'm running Server 'A' and Server 'B' which are both on SQL 2005 SP2. Server B connects to Server A using the linked server functionality via the SQLNCLI provider. I am issuing an update statement from a web api in a nested transaction that uses a distrubted transaction.


However, I am receiving the following error :

Unable to start a nested transaction for OLE DB provider "SQLNCLI" for linked server "A". A nested transaction was required because the XACT_ABORT option was set to OFF.
OLE DB provider "SQLNCLI" for linked server "A" returned message "Cannot start more transactions on this session.".


I've researched the issue and understand how XACT_ABORT works.

Also, I looked at the Linked Server provider options for SQLNCLI and came accross the Nested Queries option being unchecked. I checked the option and applied it to the Linked Server.

Okay, so after my long post my questions are: Do i need to restart SQL in order for this to take effect? If not, what do i need to do? I 've restarted IIS to no avail .

View 1 Replies View Related

Distributed Query From SS 2000 To Access Catalog Views On SS 2005 Via Linked Server

Aug 24, 2006

I am trying to write some admin only procedures which will collect information to one of my development server from other production and development servers.
 
I have created linked servers to access these other servers on the development server. This development server is SQL Server 2000 EE. Other servers which I want to access are 2000 and 2005 (vaious editions)
 
E.g I have another development server called PRODTEST which is SQL Server 2005 and on the development server I have created a linked server pointing to PRODTEST called TESTLINKSRV. I want to access new object catalog view (as I do not want to use sysobjects)
 
When I run the following query
 
SELECT * FROM [TESTLINKSRV].[DBNAME].[sys].[objects]
 
I get following error,
 
OLE DB error trace [Non-interface error:  OLE DB provider does not contain the table: ProviderName=' TESTLINKSRV ', TableName='" DBNAME "."sys"."objects"'].
 
Msg 7314, Level 16, State 1, Line 1
 
OLE DB provider ' TESTLINKSRV ' does not contain table '"DBNAME"."sys"."objects"'.  The table either does not exist or the current user does not have permissions on that table.
 
So I try this query
 
SELECT * FROM [TESTLINKSRV].[DBNAME].[sys.objects]
 
and I get following error
 
Msg 208, Level 16, State 1, Line 1
 
Invalid object name TESTLINKSRV.DBNAME.sys.objects'.
 
So bottom line is how do I access catalog views on a 2005 server from a 2000 server using linked server?
 
I hope someone understands what I am trying to achieve. Please let me know what is it that I am doing wrong.
Thank you

View 5 Replies View Related

Severe Error After Installing SQL2005 SP2 Query To Linked Server Foxpro With Oledb

Oct 24, 2007

After installing sql2005 sp2 a simple select query to a linked server reports the following error message:

Msg 0, Level 11, State 0, Line 0A severe error occurred on the current command. The results, if any, should be discarded.Msg 0, Level 20, State 0, Line 0A severe error occurred on the current command. The results, if any, should be discarded.
Before installing SP2 we used sql2005 without any service packs, the linked server worked fine.

The linked server is a Visual FoxPro database.

After uninstalling and installing the 'Microsoft OLE DB Provider for Visual FoxPro 9.0' the issue stil remains.

View 3 Replies View Related

DB Engine :: Linked Server - Getting Error When Performing Cross Instance Query With Joins

Apr 26, 2015

I've successfully created a Linked Server that connects a local DB Engine with another DB Engine through an ip over an extranet. I am able to run simple Select statement queries on the Local DB Engine and get results from the linked server. However when attempting to perform more complex queries that join tables from the linked server with tables from the local DB server, I get the following error message after several minutes of execution:

OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Protocol error in TDS stream".
OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Communication link failure".

Msg -1, Level 16, State 1, Line 0

Session Provider: Physical connection is not usable [xFFFFFFFF].

OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Communication link failure".

Msg -1, Level 16, State 1, Line 0

Session Provider: Physical connection is not usable [xFFFFFFFF].

OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" 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.

How I can resolve it. I've read on Distributed Transactions but I understand that it only applies to manipulation statements?

Both are SQL servers. Linked Server is SQL2008R2 if not mistaken. Local DB Engine is SQL2014.

View 3 Replies View Related

Problem When Creating SP To Import Big Data From Linked Server Mysql By Using Openquery() To Sql 2005

Oct 27, 2007

I'm trying to use linked server to import big amount of data(around 9 million rows) from mysql to a sql 2005 table.
I plan to create a stored procedure and use job agent to run it at off-peak time everyday. The SP is like:

.....
Truncate table sqltblA;

Insert Into sqltblA(col1, col2, col3)
Select col1, col2, col3
From OpenQuery('mysql', 'Select col1, col2, col3 From mytblB')
.....

But when I tried to CREATE the above SP from management studio, seems the sql server was trying to get all data from table mytblB and ended up with failure after several minutes hang. The error message is sth. like "running out memeory". BTW, during that several minutes hang, I can't query mytblB using mysql's tool as well. it seems to me that table got locked.

However if i try to change the returned result to 100,000 rows by changing script to

Insert Into sqltblA(col1, col2, col3)
Select col1, col2, col3
From OpenQuery('mysql', 'Select col1, col2, col3 From mytblB Limit 100000')
The SP could be created without any problem and running the SP could get the data in table sqltblA.
But that's not what I want, I need all rows instead of a certain amount of rows.


Does anyone know what's going on there when I try to CREATE that SP and any solution to it?

Plus I tried to use SSIS as well. When I'm configuring the DataReader source, which uses ADO.NET's ODBC option to connect to mysql, if its sqlcommand is "select * from mytblB" without the Limit key word, then the configuration page will hang there as well and table mytblB is not accessible by using mysql tool. I'm just sick of wasting time on that and that's why I chose to use SP istead of SSIS. But same result. :-(

View 1 Replies View Related

Error While Subsciber Syncronizing Data With Sql Server 2005 Database Through IIS Server

Jan 16, 2008



internal error: HTTP header information is either corrupted or missing in the transport message. It could be a network transmission error or an IIS problem.(28035).

Any one of you have any suggestions??

View 1 Replies View Related

Problem Accessing A SQL Server 2000 Linked Server From SQL Server 2005

May 4, 2007

Hi
I have created a linked server from SQL Server 2005 (SP 1) to SQL Service 2000 (SP 4) with a sql server login that is available on both servers but with different passwords and permissions.



I am getting the following error while accessing the linked server in management studio based on the scenario given below ;



------ Error Message Starts
OLE DB provider "SQLNCLI" for linked server "(SQL Server 2000 instance name)" 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 18456, Level 14, State 1, Line 0
Login failed for user 'abc'.
------ Error Message Ends


Consider login name is abc.
Now this login abc has sysadmin rights on sql server 2005.
The same login abc has only db_datareader rights on sql server 2000 on just one database and is not associated with any fixed server role.

I have configured the linked server using the following options;
1. I have tried impersonating login from SQL Server 2005 to SQL Server 2000 .
2. I have also tried specifying remote login / password option.


Anyone having any idea, would be of great help.
Regards,
Salman Shehbaz.

View 3 Replies View Related

Connect From A SQL Server 2005 Db To A SQL Server 2000 Db, Without Linked Server Connection

Apr 18, 2007

Can I connect from a SQL Server 2005 database to a SQL Server 2000 database, without establishing a linked server connection.



I need to fire a SELECT query on a SQL Server 2000 database, but don't want to add it as a linked server. Is there any way I can do this or its not possible??

View 1 Replies View Related

Instructions For Setting Up A Sybase ASE Server As A SQL Server 2005 Linked Server?

Dec 28, 2005

I have a Sybase Adaptive Server Enterprise server which I need to set up as a linked server in SQL Server 2005.  The Sybase server is version 12.5.2, and the Sybase ODBC driver version is 4.20.00.67.  I have already installed the Sybase client software on the server.


I also created a SystemDSN on the SQL Server to connect to the Sybase server.  I tested the connection and it was able to connect.

I ran the following code to create the linked server:

<code>

EXEC master.dbo.sp_addlinkedserver @server = N'LinkedServerName', @srvproduct=N'Sybase', @provider=N'MSDASQL', @datasrc=N'Sybase System DSN', @provstr=N'"Provider=Sybase.ASEOLEDBProvider;Server Name=servername,5000;Initial Catalog=databasename;User Id=username;Password=password"'

</code>

I then ran sp_tables_ex to make sure I could view the tables in the Sybase database.  Here is the error message I get:

<code>

OLE DB provider "MSDASQL" for linked server "LinkedServerName" returned message "[DataDirect][ODBC Sybase Wire Protocol driver]Error parsing connect string at offset 13. ".

Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "LinkedServerName".

</code>

Any ideas what is happening here?

View 10 Replies View Related

Error :Execute Trigger From Remote Server To Another Server By Linked Server

Jul 22, 2007

i did "Linked server" between To Servers , and it's Working.

---------------------------



For Example :

Server 1 =S1.

Server = S2.

i create table in S1 : name = TblS1

i create same table in S2 : name TblS2



and i create trigger(name tr_cpD) From S1 in TblS1 For send data To TblS2 in S2

/****************** trigger Code ***************

CREATE TRIGGER dbo.tr_cpD

ON dbo.TblS1

AFTER INSERT

AS


BEGIN





SET NOCOUNT ON;


insert into [S2].[dbname].[dbo].[TblS2] Select ID,Name from insertedEND

**************************************************



result is :

Msg 7399, Level 16, State 1, Procedure tr_cpD, Line 14

The OLE DB provider "SQLNCLI" for linked server "S2" reported an error. The provider did not give any information about the error.

Msg 7312, Level 16, State 1, Procedure tr_cpD, Line 14

Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked server "S2". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.





how i can execute this trigger



View 5 Replies View Related

SQL Server 2005 Error 8630: Internal Query Processor Error

Jan 22, 2007

I have a SProc that runs across many clients without any problems. Every now and then, though, I get the following error:

Internal Query Processor Error: The query processor encountered an unexpected error during execution. [SQLSTATE 42000] (Error 8630).

All I am doing is populating Temp tables with some data and then joining them together to create a Global Temp table that is being BCP'd to a network share.

Has anyone come across this error in SQL Server 2005? I cannot find anything on Google or Microcsoft.

Thanks,
Robert

View 1 Replies View Related

SQL Server 2008 :: Linked Server Tests Fine But Query Does Not Work

Apr 16, 2015

Using a 32-Bit SQL Server 2008 Express on my LOCAL PC. I downloaded the Advantage 8.1 OLE DB Provider and created a linked server to a REMOTE Advantage 8.1 database server. There is no data dictionary on the Advantage server.

Here is my linked server:

EXEC master.dbo.sp_addlinkedserver @server = N'1xx.1xx.xx.1xx', @srvproduct=N'Advantage', @provider=N'Advantage OLE DB Provider', @datasrc=N'1xx.1xx.xx.1xxeccET', @provstr=N'servertype=ads_local_server;tabletype=ads_cdx;'--tabletype=’ADS_ADT’ (this test works too)
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'1xx.1xx.xx.1xx',@useself=N'False',@locallogin=Null,@rmtuser='adssys',@rmtpassword=Null

Testing the link succeeds with above. Using “ads_REMOTE_server” instead of “ads_local_server” and the test fails. Here is my problem, using the following queries will not work. Perhaps it’s a permissions issue? When I specify my local credentials for the remote server in the linked server it still does not work.

SELECT * FROM OPENQUERY([1xx.1xx.xx.1xx], 'SELECT * FROM ActType')

OLE DB provider "Advantage OLE DB Provider" for linked server "1xx.1xx.xx.1xx" returned message "Error 7200: AQE Error: State = HY000; NativeError = 5004; [Extended Systems][Advantage SQL][ASA] Error 5004: Either ACE could not find the specified file, or you do not have sufficient rights to access the file. Table name: ActType SELECT * FROM ActType".
Msg 7321, Level 16, State 2, Line 2

An error occurred while preparing the query "SELECT * FROM ActType" for execution against OLE DB provider "Advantage OLE DB Provider" for linked server "1xx.1xx.xx.1xx".

View 0 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved