After SQL 2005 SP2, Query To Apartment Linked Servers Fails
Mar 6, 2007
We have a linked server that is an Apartment model OLE DB Provider. It works fine on SQL 2005 SP1. After applying SP2, we get the following msg when running a query against the linked server. I cannot find anything in the SP2 doc that indicates a change of behavior for linked servers. Any ideas ?
Bill
Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'DBAmp.DBAmp' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
View 4 Replies
ADVERTISEMENT
Apr 4, 2007
We have a linked server that is an Apartment model OLE DB Provider. It works fine on SQL 2005 SP1 and previous versions.
After applying SP2, we get the following error message when running a query against the linked server.
I cannot find anything in the SP2 documentation that indicates a change of behavior for linked servers.
Any ideas ?
Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'XXX.XXXXX' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
Toby
View 7 Replies
View Related
May 9, 2008
Get the following error when trying to restore from one SQL 2005 server (64-bit, standard edition) to another (32-bit, enterprise edition).
Msg 3169, Level 16, State 1, Line 1
The backed-up database has on-disk structure version 611. The server supports version 539 and cannot restore or upgrade this database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
View 3 Replies
View Related
Nov 13, 2006
For example I have 15 records in which accountID is a PK. AccountID 1,2,3,4,5 is linked on server1. I want to find out records that are not linked. So its record 6 - 15. Can someone show the stored procs for this scenario? Or the query statement to do this... Im new to linked servers. Thanks alot.
View 1 Replies
View Related
Jan 11, 2000
I have successfully connected to a sybase 11 database and have successfully run a couple of open query statements against this database, I have now placed this open query in a stored procedure and it works well, but when I want to pass parameter variables has part of my open query it does not like it. It asks me to declare the variable which is a parameter of the stored procedure. Is there any way I can pass in a variable value has part of my query
************************************************** *********************8
CREATE PROCEDURE qse_check_label_projectid
@projectid char(18)
AS
Select * from openquery(MRTEST32,'SELECT
Project_id
FROM
DBO.MRT_PROJECT
WHERE
Project_id = @PROJECTID ')
************************************************** ***************************
Please I believe there should be a way to pass a value through this open query function!
View 2 Replies
View Related
May 8, 2007
created a linked server. But i can't query the table.
i tried different combinations but they dont work.
LS: dccs_danville
DB: intdccs_dv.gdb
table:tblload
select top 10 * from dccs_danville.INTDCCS_DV.gdb.tblload
select top 10 * from dccs_danville.INTDCCS_DV.dbo.tblload
select top 10 * from dccs_danville.INTDCCS_DV...tblload
select top 10 * from dccs_danville.INTDCCS_DV.tblload
select top 10 * from dccs_danville...tblload
select top 10 * from dccs_danville.INTDCCS_DV.tblload
also tried EXEC sp_tables_ex 'dccs_danville'
tblload is able to be queried.
anyone know what is wrong?
View 3 Replies
View Related
Jul 26, 2005
I can't define a linked server in SQL Server 2005 x64 edition (to a SQLServer 2000 instance).
View 29 Replies
View Related
Mar 25, 2008
Hi,
I have:
- SQL 2005 Dev on machine A
- SQL 2005 Dev on machine B
I've made a linked server from A to B using:
- Windows authentication
- SQL Server authentication
However, I ran into the following "problems":
- When creating a linked server from A to B, when I do a "new linked server" I have to give it the same name as machine B.
- Therefore, I can only make one linked server to B (not f.ex. make two, one Windows authentication and one SQL Server authentication).
Is it only possable to make one linked server to a machine running SQL Server 2005?
Thank you.
View 6 Replies
View Related
Jun 2, 2007
Dear all
I have created linked server object between a sql 2000 and sql 2005 database. i have the latest mdacs installed and all the services enabled on both sides.
But when i try and run a sql statement on the sql 2000 side i get this error:
[DBNETLIB][ConnectionOpen (PreLoginHandshake()).]General network error. Check your network documentation.
If i try and run a swl statment on the sql server 2005 side the procedure works.
Has anyone experienced the same problem?Any advice?
Thank you so much for the help.
Dan
View 2 Replies
View Related
Jul 25, 2006
We have two sql servers using Windows authentication. One is sql 2000 the other sql 2005. When logged in as "sa", I can link these servers and run a SELECT distributed query without any problem.
But when one of our developers runs the same query (he is "dbo" of each database on each of the servers) he gets the error:
"OLE DB Provider "SQLNCLI" for linked server <Servername> returned message "Communication Link Failure"
...
Login failed for user '(null'). reason: Not associated with a trusted SQL Server connection"
Despite that, I, as "sa" equivalent, have no problem so it appears to be a permisisons issue. Any ideas what is going on here?
TIA,
Barkingdog
View 3 Replies
View Related
Aug 9, 2007
I am having trouble finding any documentation on how linked servers are set-up under SQL 2005 for log-shipping and replication. Under SQL 2000, the only linked servers "visible" were ones that were user-defined. Under SQL 2005, it seems when you set-up log-shipping or replication, you get these new linked servers which are automatically defined. I would like to understand them and in particular, how security is maintained. We recently had a production issue where after some problem with a server which resulted in having to restart SQL Server, and shrink msdb, log-shipping started to fail authentication when trying to run alert jobs. Only after rebooting the server, and also the log-ship target server, and waiting 9 hours, did the authentication correct itself. I would like to know how it resolved itself and what took so long?
View 3 Replies
View Related
Jun 3, 2007
Dear all
I have created linked server object between a sql 2000 and sql 2005 database. i have the latest mdacs installed and all the services enabled on both sides.
But when i try and run a sql statement on the sql 2000 side i get this error:
[DBNETLIB][ConnectionOpen (PreLoginHandshake()).]General network error. Check your network documentation.
If i try and run a sql statment on the sql server 2005 side the procedure works.
Has anyone experienced the same problem?Any advice?
Thank you so much for the help.
Dan
View 4 Replies
View Related
Dec 5, 2006
I have been trying to figure out for some time why Linked Servers do not continue to run. I start up my SQL Server Database service, and then using SQL Server Management Studio I create one or more linked servers to Microsoft Access Databases using the Jet Provider. I then run some queries to that access those linked servers without any problems. (One currently is called 'THE')
After some time has passed, possibly minutes, hours or days, I can no longer access any of the linked servers. No data, queries, tables, or anything is accessible to me anymore. I use this command "EXEC sp_testlinkedserver N'THE' " to test the linked server and get the following error msg.
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "THE" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Procedure sp_testlinkedserver, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "THE".
The only solution I have to fix this currently is to restart the SQL Server Database service. I have tried deleting and recreating the Linked server before doing this to no avail. I have also used the FileMon and RegMon tools on the server to try to determine the problem but have no no success. After restarting the SQL service I can run the "EXEC sp_testlinkedserver N'THE' " command successfully "Command(s) completed successfully." and can access the linked servers again without any problems.
Any help with this is greatly appreciated.
Mark.
P.S. I have tried changing the security options on the linked server from "Be made without a context." to using the "Admin" username and leaving the password blank, to several other possible variations and options, as well as setting up the system.mdw database in the registry to no avail.
View 22 Replies
View Related
Jul 20, 2005
Greetings,I have 3 servers all running SQL Server 2000 - 8.00.818. Lets callthem parent, child1, and child 2.On parent, I create a view called item as follows:CREATE view Item asselect * from child1.dbchild1.dbo.Item union allselect * from child2.DBChild2.dbo.ItemOn child1 and child2, I have a table "item" with a column named "id"datatype uniqueidentifier (and many other columns). There is anon-clustered index created over column "id".When I connect to the parent server and select from the viewSelect id, col1, col2, …. From item where id =‘280A33E0-5B61-4194-B242-0E184C46BB59'The query is distributed to the children "correctly" (meaning itexecutes entirely (including the where clause) on the children serverand one row is returned to the parent).However, when I select based on a list of idsSelect id, col1, col2, …. From item where id in(‘280A33E0-5B61-4194-B242-0E184C46BB59',‘376FA839-B48A-4599-BC67-25C6820FE105')the plan shows that the entire contents of both children item tables(millions of rows each) are pulled from the children to the parent,and THEN the where criteria is applied.Oddly enough, if I put the list of id's I want into a temp tableselect * from #bv1id------------------------------------280A33E0-5B61-4194-B242-0E184C46BB59376FA839-B48A-4599-BC67-25C6820FE105and thenSelect id, col1, col2, …. From item where id in (select * from #bv1)the query executes with the where criteria applied on the childrendatabases saving millions of rows being copied back to the parentserver.So, I have a hack that works (using the temp table) for this case, butI really don't understand the root cause. After reading online books,in a way I am confused why ANY of the processing is done on thechildren servers. I quote:================================================Remote Query ExecutionSQL Server attempts to delegate as much of the evaluation of adistributed query to the SQL Command Provider as possible. An SQLquery that accesses only the remote tables stored in the provider'sdata source is extracted from the original distributed query andexecuted against the provider. This reduces the number of rowsreturned from the provider and allows the provider to use its indexesin evaluating the query.Considerations that affect how much of the original distributed querygets delegated to the SQL Command Provider include:•The dialect level supported by the SQL Command ProviderSQL Server delegates operations only if they are supported by thespecific dialect level. The dialect levels from highest to lowest are:SQL Server, SQL-92 Entry level, ODBC core, and Jet. The higher thedialect level, the more operations SQL Server can delegate to theprovider.Note The SQL Server dialect level is used when the providercorresponds to a SQL Server linked server.Each dialect level is a superset of the lower levels. Therefore, if anoperation is delegated to a particular level, then Queries involvingthe following are never delegated to a provider and are always it isalso delegated to all higher levels.evaluated locally:•bit•uniqueidentifier================================================This suggests to me that any query having where criteria applied to adatatype uniqueidentifier will have the where criteria applied AFTERdata is returned from the linked server.Any ideas on the root problem, and a better solution to get the queryand all the where criteria applied on the remoted linked server?Thanks,Bernie
View 5 Replies
View Related
Jan 24, 2008
Is it possible to query linked servers without the Distributed Transaction Coordinator service enabled or allowing network access?
Is that ONLY for transactions? What if I just wanted to read the data and nothing else?
View 13 Replies
View Related
Nov 14, 2006
I have installed SQL 2005 (X64) on 64-bit Windows 2003 server (AMD). One of my databases need to connect several Oracle databases on 32 bit servers.
I managed to install Oracle 32 bit Client (was unsuccessful with 64 bit installation) and could set up the odbc (32). But I coul'nt find Oracle under linked servers!
Does anyone know what I need to do?
View 3 Replies
View Related
Aug 17, 2006
Is it possible to link to an SQL 2005 server db from a SQL 2000 server? Is there a driver for this?
View 1 Replies
View Related
Apr 5, 2008
can anybody tell me about Linked Servers and their uses and how to add a linked server to my Sql Server 2005.
any help on this would be highly appreciated.
View 3 Replies
View Related
Jun 10, 2004
Hello All,
I have been trying to Link two sql servers on two different machines over the Internet without any luck. Can someone point me to information about doing this with good examples?
Thanks
View 2 Replies
View Related
Jan 22, 2002
I currently have a main SQL Server which had a column on the majority of the tables. This column also had a check contraint on it. I dropped the column and the constraint and I now get the following message when trying to query the tables through a linked server 'OLE DB provider 'SQLOLEDB' supplied inconsistent metadata. An extra column was supplied during execution that was not found at compile time.'.
Can anyone help
View 1 Replies
View Related
May 10, 2001
Hi All,
I have successfully linked a server and had SQL queries running
OK for a few weeks, but today I get the following message....
"OLE DB provider 'SQLOLEDB' supplied inconsistent metadata. An extra column was supplied during execution that was not found at compile time."
(Server: Msg 7353, Level 16, State 1, Line 1)
I've dropped the link and recreated it but I still get the same error
message. Can anyone help???
Thanks
David.
View 2 Replies
View Related
Jun 25, 2001
Hi,
I'm using SQL Server 7.0 SP1.
I have a DTS package that requires me to link 2 servers
and query both in order to get my set of records that I
want to Transform. My query runs fine in QA. I copy and
paste it into the SQL Query window of the transformation.
I click Preview and all that happens is a quick flicker of
the DTS window. When I go to Transformations, there are
no source columns.
Here is a copy of my code that I'm trying to use:
-------------------------------------------------------
declare@start_dteas datetime,
@end_dteas datetime
SELECT@start_dte = date_data_range_start_dte,
@END_DTE = date_data_range_END_dte
FROMSIDDEV.SID_DEVELOPMENT.DBO.T901_RUN
WHERERUN_ID = (selectmax(run_id)
from
SIDDEV.SID_DEVELOPMENT.DBO.t901_run
whererun_type_cd = 'M')
SELECTRUN_ID
INTO#RUNS
FROMCRMDEV02.MDCORE.DBO.T901_RUN
WHEREDATE_DATA_RANGE_START_DTE >= @START_DTE
ANDDATE_DATA_RANGE_END_DTE <= @END_DTE
SELECTT70.*
FROM
CRMDEV02.MDCORE.DBO.T70_MD_UNIVERSE_RELATIONSHIP_M A
NGT AS T70 JOIN #RUNS
ONT70.RUN_ID = #RUNS.RUN_ID
DROP TABLE #RUNS
------------------------------------------------------
Is there an issue with Linked Servers and DTS?
Any help would be greatly appreciated.
Thanks in advance,
Darrin
View 1 Replies
View Related
Aug 7, 2001
FOLKS
I GET THE FOLLOWING ERROR WHEN I RUN A SELECT FROM THE LINKED SERVER
THE LINKSERVER EXISTS AND THERE IS A RECORD IN SYSSERVERS TOO.
Could not initialize data source object of OLE DB provider 'Microsoft.Jet.OLEDB.4.0'
I AM NOT SURE WHERE TO GO FROM HERE.
REGARDS
GIRISH
View 1 Replies
View Related
Aug 10, 2001
Hi Gurus,
I am looking for literature to find out Pros and Cons of Using DTS Verses
Linked Servers in SQL 7.0.
My requirement can be done by either DTS or Linked servers. But I would like to know more about resorce utilisation of these tecniques before making a decission.
Can somebody suggest where to look for.
Thanks
sekhar
View 1 Replies
View Related
Sep 18, 2001
When I create linked server using integrated security and <they will be impersonated> option I get this:
Server: Msg 18456, Level 14, State 1, Line 1 Login failed for user ''
MS Technet says that SQL Server 7 doesn't allow double hops and to use mappings to standard security login to work around. @#%%~~@@#@#&^%@#
Impersonating to version 6.5 works fine.
Is there any way to link servers using ONLY integrated security?
Any help would be much appreciated.
Thanks
View 1 Replies
View Related
Jul 18, 2000
I have setup a linked Informix server in SQL7.0 and I am trying to create a
simple View with the following SQL statement
SELECT doc_code
FROM FOURSITE.foursite.informix.watdoc
WHERE (order_no = [PV01963B ])
I am getting the following error:
---------------------------
SQL Server Enterprise Manager
---------------------------
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid use of schema and/or
catalog for OLE DB provider 'MSDASQL'. A four-part name was supplied, but
the provider does not expose the necessary interfaces to use a catalog
and/or schema.
---------------------------
OK Help
---------------------------
Can any one tell me how to correct this problem?
Thanks
Peter
pczurak@bigfoot.com
View 5 Replies
View Related
Apr 17, 2000
Is it possible to add a 6.5 database as a linked server to a 7.0 database and query from it ?
View 6 Replies
View Related
Nov 3, 2000
Hi.
I'm working with SQL Server 7.0 and I've been reading about the linked server option. My question is, if it's possible to link a server with a SQLBase database 7.01.
I have download some drivers to my computer, the odbc driver for sqlbase and the sqlbase ole db driver as well, but when I try to add a new linked server in the enterprise, in the provider name option I can not see any provider name addressing to Centura or Sql base driver.
Has anybody an idea how to make this connection then? or the right way for doing it??
Thanks for any help can be offered.
Regards, Elvia.
:)
View 2 Replies
View Related
Feb 10, 2000
I have two 7.0 servers and I want to run a distributed query.
I did sp_addlinkedserver to link server B to server A
and I did sp_addlinkedsrvlogin for a specific login.
After adding login when I try to access a table on server B it
says login failed
All the logins are NT authenticated (in both the servers, So
both servers have same logins.)
but it is not working.
View 1 Replies
View Related
Sep 1, 2000
Hi all
I try to configure Linked Servers from the query analyze , and have some troubles.
My steps are :
1. from the enterprise mennenger , add 2 new servers : srv1 , srv2 (both SQL7)
2. from the query analyzer of srv1.master: "sp_addlinkedserver 'srv2'"
3. from the query analyzer of srv1.master:
"sp_addlinkedsrvlogin 'srv2',false,null,'sa','myPass'"
4. select * from srv2.pubs.srv2Table
5. I get the next error :"error 6 : Specified SQL srever not found."
What do I do wrong ?
Eyal
View 2 Replies
View Related
Apr 19, 2000
Anyone out there use linked servers? I just tried this SQL7 feature and am very impressed. Are there any pitfalls to watch out for? Comments?
Thanks in advance.
Steve
View 1 Replies
View Related
Feb 14, 2002
I have created link servers a few time within SQL2000 and pulled data from Oracle. How would an Oracle Server or Application pull / See from SQL?
Anyone have any experience?
Thanks,
David
View 1 Replies
View Related
May 11, 2002
How to determine by means of T-SQL
if a linked server is available(for processing with its tables)?
View 1 Replies
View Related
