I am transferring data from SQl Server 2000 to Oracle through Linked
Servers.It takes considerable amount of time while transferring data
from SQL Server to oracle.Both these databases are at remote
locations.I am executing a Stored procedure on SQL Server and what it
does is,It has an Insert statement which reads the data from a table in
SQL Server and inserts it into the Oracle table. It is a very time
consuming process.Vice-Versa it takes very less time for Oracle-SQL
Server transfer.If anybody has a clue towards reducing the time in SQL
Server-ORACLE transfer.
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
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.
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?
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.'.
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???
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
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.
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.
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
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 ---------------------------
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. :)
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.)
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."
This is my problem I have a Server A and Server B. There is a master table in Table A. I link Server A and Server B. When I look in Under Linked Server tables of B I can only C tables in Server A not the Server B 's Tables. Dpn't know what is happening. Could SOmebody help
I have two servers and they are linked servers. When I query a table on ON Box2 from Box1 The distributed query works fine but when I tried the oppisite it says 'SQL Server does not exist or access denied.' What could be the reason.
Recently, I had Microsoft review why our SQL Db was unexpectedly terminating. They pointed to our Oracle Linked servers. They recommended that we try to uncheck the enable allow in process option on the ODBC driver. We are currently using the Microsoft OLE Db provider for our Oracle connnections as we were unable to get the Oracle ones to work with a linked server. When we unchecked this allow in process option near the end of the day our website began getting out memory errors from ODBC. It appeared to have been because of this change. Does anyone have any insight as to why changing this option could have caused this?
Is there any limit to the number of linked servers i can add via EM?. where can i find more info on adding/configuring linked servers in MSSQL2k and the limitations/drawbacks if any?
We have a new 2005 server, and I am trying to link our old 2000 server so I can use the old databases in new server.
This is what I done: In 2005: EXEC sp_addlinkedserver oldServer --completed fine then: EXEC sp_addlinkedsrvlogin oldServer, 'false', NULL, 'Administrator', password --completed fine But whenI run the query I get the error:
OLE DB provider "SQLNCLI" for linked server "oldServer" 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 'Administrator'.
I have to link servers using sql server 2000 standard edition.... for anyone who has done this..... upon linking the servers using the other options radio button choice.... is it suppose to show all the databases for that server under the drop down.... its only showing tables and views... which i get an error 7339 or error 17:
can anyone assist...or lead me in the right direction..... thanks, jonathan
Disclaimer: I am not a DBA so my attempts may not make sense. :)
I have a win 2003 server running sql server 2005. I am trying to create a 'linked server' with an oracle 8i database at a remote site. I have tried using the 'add linked server' option from sql server enterprise manager and I have also tried to create it using the sp_addlinkedserver command. i tried using a dsn as the datasource and I have also tried using a dsnless connection using a TNS entry as the provider string. The Oracle client is on the box as can be confirmed both by the dropdown in the 'add linked server' dialog in sql server enterprise manager and also by going into the oracle enterprise manager and actually sending oracle data. I don't really have a preference on how to create the 'Linked server', I just need to get it to work.
I have a linked server connection on a SQL 2k server connected to a Sybase ASE 12.5 server via Sybase OLE DB connection. To this point everything has been working great for months. Now we're encountering a problem where new tables created on the Sybase db won't show up in the linked server connection. I've checked permissions and the object owners and everything is exactly the same as the pre-existing tables except the table's new.
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
TCP Provider: An existing connection was forcibly closed by the remote host.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. OLE DB provider "SQLNCLI" for linked server "10.50.40.157" returned message "Communication link failure". (Microsoft SQL Server, Error: 10054)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=10054&LinkId=20476
------------------------------ BUTTONS:
&Yes &No ------------------------------
User exists on both SERVERS the linked one and the one im setting u the linked server with.
Okay, so i have two sql servers on the same network. i want to be able to have them linked. so i went into management studio, and did the New Linked Server Wizard.
i typed in the box "Server2" (name of the server i want to link to) and selected SQL CLIENT bullet
now the sa logins are the same, and so are the windows logins for the server, so security shouldnt really be an issue.
when i run: EXEC sp_tables_ex Server2
i get the following error: OLE DB provider "SQLNCLI" for linked server "Server2" returned message "Invalid authorization specification". Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 41 The OLE DB provider "SQLNCLI" for linked server "Server2" reported an error. Authentication failed. Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41 Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "Server2".
is this a username/password issue? the windows administrator accounts are the same, and so are the sa logins, is there something i can do to get past this?
small update i tried: USE master; GO EXEC sp_addlinkedserver 'Server2', N'SQL Server' GO
BEGIN TRY EXEC sp_testlinkedserver N'Server2'; END TRY BEGIN CATCH PRINT 'Linked Server not available'; RETURN; END CATCH
ang got: OLE DB provider "SQLNCLI" for linked server "Server2" returned message "Login timeout expired". OLE DB provider "SQLNCLI" for linked server "Server2" 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.". Linked Server not available
I need help creating a linked server between two SQL Server 2005 databases. What I need to be able to do is query Table A from Database1 on Server1 from Database1 on Server2. Do I create a DSN? Would I create it on Server1 or Server2? I've looked at examples online and the linked server template, but I'm very new at this.
I am developing a WM5 program (C#.NET) using SQL Server Mobile, using RDA to pull and push data from/to SQL2005.
The question/issue is this: Does RDA work with linked servers, more specifically, if the RDA table is an Oracle table accessed through a linked server? I have a linked server defined in SQL2005, using the .NET Oracle provider (MSDAORA) linked to Oracle 10g. When I try to perform an RDA PULL operation from Windows Mobile 5 (Smartphone emulator) I get the following error:
€œThe query cannot be tracked. There might not be a primary key, or the query might involve multiple tables. [ Query string = SELECT * FROM DATABASE_NAME..SCHEMA_NAME.TABLE_NAME ]€? I have used at least 3 different test tables in Oracle, trying different options. The error message above implies that the table may not have a primary key but it does (constraint and index) and I have tried a few options with this. The message also implies that multiple tables may be involved but this is not the case either. I have tried variations on the select statement including specifying the columns. I tried building the CREATE TABLE statements with the PK using SQLServer to generate the SQL DDL to see if SQLServer might build the table, constraint, index with options the way it wants to see them €“ same result. I can run most any queries (select, insert, etc.) from SQLServer using the linked server and the queries work. The only problems involve RDA to the linked server. I tried turning tracking off on RDA and was able to pull the entire table from the linked server. However, RDA requires that you can only push a tracked table (change tracking on). I tried to push a non-tracked table and it fails.
I have two databases on the same server (SQL Server 2005). I need to query data from table1 in database1 in database2. Do I need a linked server. I've had a look at the "Add Linked Server Simple" template, but I don't know what criteria to enter in the template?!
For simplicity sake, say my database1 is called DB1 with the table TBL1. Where does this go below and what else do I need?
-- =====================================
-- Add Linked Server Simplet template
-- =====================================
-- Create linked server reference to Database Engine instance on same machine
SELECT * FROM [<server_name,sysname,(local)><instance_name,sysname,SQLEXPRESS>].<database_name,sysname,pubs>.<schema_name,sysname,dbo>.<table_name,sysname,authors>
