LinkedServer Problem
Nov 6, 2000
Hi Everybody,
I have 2 servers one has SQL 7.0 running on NT4.0 and another has SQL 2000 running on W2K Server. I have established a linked server for SQL 2000 from SQL 7.0.
I don't have any problem of creating linked servers for sql 2000 from sql 7.0. I am also able to create linked server logins to access the sql2000 tables. Even I am able to see the tables on the Databases.
EXEC sp_addlinkedserver 'SQL2K', '','SQLOLEDB','GENOMESQL2000'
sp_addlinkedsrvlogin @rmtsrvname = 'sql2k' ,@useself = 'false' ,@locallogin = 'genomeACCT1',@rmtuser = 'test2k',@rmtpassword = 'test2k'
exec sp_tables_ex N'sql2k' (Upto this I don't have any problem)
When I tried to execute a particular table from the database, I am getting the following error.
Select * From sql2k.northwind.test2k.payroll
Error:
******
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' does not contain table '"master"."test2k"."ven"'.
Can anybody give suggestions to solve this problem.
tks in advance,
Vasu
View 1 Replies
ADVERTISEMENT
Mar 21, 2007
Hi
Im sorry if im in the wrong section. My problem is a very common one and it has been hard for me to find the fix.
Ive gone thru Component Services, Firewall settings, Registry and Microsoft Sites, but to avail.
"The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a]."
My Begin Tran doesnt start at all.
begin tran
use smartpos
go
set identity_insert smartpos.dbo.smp_product on
insert into smartpos.dbo.smp_product
(prodId
,[Description]
,barcode
,BuyPriceExclVat
,BuyPriceVatTYpeId
,point
,SupplierID
,Ref
,QtyType
,QtyFactor
,MinStock
,MaxStock
,Active
,ItemType
,ClientLastUpdate
)
select prodId
,[Description]
,barcode
,BuyPriceExclVat
,BuyPriceVatTYpeId
,point
,SupplierID
,Ref
,QtyType
,QtyFactor
,MinStock
,MaxStock
,Active
,ItemType
,LastUpdate
from server.smartpos.dbo.smp_product svr
where not exists (select prodid from smartpos.dbo.smp_product where prodid=svr.prodid)
set identity_insert smartpos.dbo.smp_product off
Can anyone help me please.
Thanks
View 2 Replies
View Related
Mar 21, 2007
I added a Linked Server, like this:
EXEC sp_addlinkedserver
@server = 'ServidorPrincipal',
@srvproduct = 'SQLEXPRESS',
@provider = 'SQLNCLI',
@datasrc = 'TIC-IISQLEXPRESS',
@catalog = 'BDPrincipal'
GO
But now I can't do a selection:
SELECT * FROM ServidorPrincipal.BDPrincipal.dbo.tblCADENA;
Because it sends this message:
Server: Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'SQLNCLI' supplied inconsistent metadata for a column. Metadata information was changed at execution time.
OLE DB error trace [Non-interface error: Column 'ID_CADENA' (compile-time ordinal 1) of object '"BDPrincipal"."dbo"."tblCADENA"' was reported to have changed. The exact nature of the change is unknown].
The linked Server is called "ServidorPrincipal".
The database is: BDPrincipal.
And the table is dbo.tblCADENA.
"changed at execution time" ¿What does it mean?
What is happening here?
View 4 Replies
View Related
May 31, 2007
Hi all,
I'm trying to change the datasource of a SQL Server LinkedServer using SMO
I've got the relevant linkedserver as an object, changed the DataSource property, and selected it again to confirm the change.
However, the change is lost as soon as the object is destroyed.
Looking at BoL I think I need to be using alter() method of the linkedserver class but I just get an error
Alter failed for LinkedServer '<servername>'.
any ideas how I should be using this class to do what I want?
I can post my script (PowerShell) if it would help.
Thanks, Robin.
View 2 Replies
View Related
Jun 23, 2008
I'm copying almost all contents of one table from one server/db to another. In relation to speed, what is the difference of using SSIS vs a linked server and sp? I'm going to do the benchmarks, but I'm curious about behind the scenes kinda stuff, theoretically which one if any should be faster and why?
View 5 Replies
View Related
Aug 29, 2007
Hello All,
I created two database instances in the same MS SQL SERVER 2005 named hafeez and local. I created a linked server from hafeez database to local named HTOL
I created a dummy table in local database named samplocal.
Now i am able to list the records using the linked server, for this i used the following query.
SELECT * FROM OPENQUERY(HTOL,'SELECT nameandval FROM SAMPLOCAL');
Now the problem is, i am not able to insert the rows into the samplocal table using linkedserver. I am using the following query to insert the rows and getting the following errro.
INSERT INTO OPENQUERY(HTOL,'SELECT nameandval FROM SAMPLOCAL') VALUES('tertertetttwertw');
Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "HTOL" supplied inconsistent metadata for a column. The column "nameandval" (compile-time ordinal 1) of object "SELECT nameandval FROM SAMPLOCAL" was reported to have a "Incomplete schema-error logic." of 0 at compile time and 0 at run time.
Is the above query correct? or shall i miss anything?
Please guide me.
Thanks in Advance
Hafeez Shaik.
View 3 Replies
View Related
Nov 23, 2005
I have a question when I work on LinkedserverThe Linkedserver name is [Hp-server],the Datebase name isNewexec,the Table name is Customers_CoypTestThe SQLScript is below:Update [Hp-server].Newexec.dbo.Customers_CoypTestset Unitname=b.Unitnamefrom [Hp-server].Newexec.dbo.Customers_CoypTest a joinNewexec.dbo.Customers_CoypTest bon a.Cid=b.Cid and b.Cid='Tony'The server returns ERROR like this:a) can not open this table '"Newexec"."dbo"."Customers_CoypTest"'(come from OLE DB provide server 'SQLOLEDB'). provide server do notsupport index scan on the data source.b) [OLE/DB provider returned message:Error occured whenmulti-operate.If possible, please check each OLE DB status value.Nowork had been completed.]c) OLE DB Error trace[OLE/DB Provider 'SQLOLEDB'IOpenRowset::OpenRowset returned 0x80040e21:[PROPID=DBPROP_COMMANDTIMEOUT VALUE=600 STATUS=DBPROPSTATUS_OK],[PROPID=Unknown PropertyID VALUE=True STATUS=DBPROPSTATUS_OK],[PROPID=DBPROP_IRowsetIndex VALUE=TrueSTATUS=DBPROPSTATUS_NOTSUPPORTED]].
View 5 Replies
View Related
Sep 14, 2006
Hi,
I am not expert on SQL server, i need to be able to access different server from withing sproc,
1. Is Linked Server is the best aproach.
2. If yes then how i set it up that it take all authenticated users of this server to remote, i tried with impersonate but it gives error that NT authority dont hae access etc etc (when i try run a sql that accesses remote server)
3. The reason i need to access remote server is ... one of the that table in my db (logTable) is getting to big it is 10 times the size of rest of the db altogether so i though i move it out not just from same db actully put it on a seprate server. so that all the tasks about main db would become easy etc.
any idea help
Sajjad
View 1 Replies
View Related
Jun 15, 2007
Hi,
We currently have an application that primarily sits on SQL server and needs to access some legacy data in a DB2 database. The solution we are using is to use a linked server between the two servers. The problem with this is that complex queries occasionally cause the driver to fetch a complete table and filter that table locally. I wondered whether service broker (which I have never used) could allow me to access the DB2 database using MQSeries and thus speed up the application as our ODBC calls have low priority on our mainframe.
Thanks and sorry if this sounds a stupid use of service broker.
View 1 Replies
View Related
Apr 27, 1999
When running a query with a table from another SQL7 Server that is linked, if only 1 table from each server is specified, the query runs fast and returns only the data rows needed from the linked server. When another table is added on to query (on the server running the query) the query processor tries to run a remote query and return over 400,000 rows over the network. Any suggestions?
View 1 Replies
View Related
Sep 23, 2004
After running the following OpenQuery...
UPDATE OPENQUERY(LINKEDSERVER, 'SELECT START_ORDER_NO FROM "OECTLFIL" WHERE "FILE_KEY" = 1')
SET START_ORDER_NO = 0
----The START_ORDER_NO field contains a 48
SET START_ORDER_NO = 1~9
----The START_ORDER_NO field contains 49~57 respectively.
SET START_ORDER_NO = 10
---The START_ORDER_NO field contains 12337
SET START_ORDER_NO = 11
---The START_ORDER_NO field contains 12593
SET START_ORDER_NO = 12
---The START_ORDER_NO field contains 12849
incrementing by 256 as I increase the value passed...
ASCII 48-57 are the characters 0-9. The string '10' consists of the two bytes with the values 49 (0x31) and 48 (0x30). It is being viewed in reverse byte order as the value 0x3031 which equals 12337 (48*256 + 49).
The LinkedServer is Pervasive SQL 2000i using 'OLE DB Provider for ODBC'
The START_ORDER_NO field is a Numeric(8,0)
I'm thinking some kind of Unicode, or translation or code page issue, but I haven't had any luck yet.
I'm not sure how difficult this is, I don't think I'm a neophyte but I'm feeling like one...
View 1 Replies
View Related
