Linked Server Update Error
Jan 20, 2004
Does Anyone know what this means?
I run the query in the attached file on multiple servers with success but some of them refuse to cooperate. I've tried recreating the linked server on the problem servers but this doesn't help. All I want to do is check db file size and free space and get it to work. Why does it have to be so damn complicated?
Any ideas welcome - including an entirely different way of doing this.
The script and table definitions in question are attached
Could not open table '"Helpdesk_New"."dbo"."LogStats"' from OLE DB provider 'SQLOLEDB'. The provider could not support a row lookup position. The provider indicates that conflicts occurred with other properties or requirements.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT VALUE=600 STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID VALUE=True STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate VALUE=True STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetChange VA...
View 1 Replies
ADVERTISEMENT
Jul 31, 2015
We have 2 instances. One is sqlgpscluster and another is [sqlcdsclustersqlcds]. We have created linked server between sqlgpscluster and [sqlcdsclustersqlcds]. We run the below query on sqlgpscluster instance
UPDATE [sqlcdsclustersqlcds].CDSBusiness.dbo.tblInsertNowAppMonitoring
SET NoCabsDateTime = GETDATE(),City=@City
,Area = dbo.fn_FindAreaSubAreaBasedOnLatLong(@PickUpLat,@PickUpLng)
WHERE CustomerMobileNo=@CustomerMobileNo
We are seeing the below error:
The OLE DB provider "SQLNCLI" for linked server "sqlcdsclustersqlcds" could not UPDATE table "[sqlcdsclustersqlcds].[CDSBusiness].[dbo][tblInsertNow AppMonitoring]". The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.
View 16 Replies
View Related
Jan 2, 2008
What's up with this?
This takes like 0 secs to complete:
update xxx_TableName_xxx
set d_50 = 'DE',modify_timestamp = getdate(),modified_by = 1159
where enc_id in
('C24E6640-D2CC-45C6-8C74-74F6466FA262',
'762E6B26-AE4A-4FDB-A6FB-77B4782566C3',
'D7FBD152-F7AE-449C-A875-C85B5F6BB462')
but From linked server this takes 8 minutes????!!!??!:
update [xxx_servername_xxxx].xxx_DatabaseName_xxx.dbo.xxx_TableName_xxx
set d_50 = 'DE',modify_timestamp = getdate(),modified_by = 1159
where enc_id in
('C24E6640-D2CC-45C6-8C74-74F6466FA262',
'762E6B26-AE4A-4FDB-A6FB-77B4782566C3',
'D7FBD152-F7AE-449C-A875-C85B5F6BB462')
What settings or whatever would cause this to take so much longer from the linked server?
Edit:
Note) Other queries from the linked server do not have this behavior. From the stored procedure where we have examined how long each query/update takes... this particular query is the culprit for the time eating. I thought it was to do specefically with this table. However as stated when a query window is opened directly onto that server the update takes no time at all.
2nd Edit:
Could it be to do with this linked server setting?
Collation Compatible
right now it is set to false? I also asked this question in a message below, but figured I should put it up here.
View 5 Replies
View Related
Sep 4, 2003
Updating the sp's fixed the error I was getting but the update takes forever on the linked server. Any way to speed things up?
I just reference the server.db.dbo.table in the update statement.
TIA
View 1 Replies
View Related
Feb 6, 2002
I know how to do a select statement using linked servers but I need to update a table in Oracle using SQL Server.
For example:
SELECT * FROM OPENQUERY(Oracle, 'SELECT * FROM TEST')
I now need to update TEST using a table in SQL Server. Can someone help?
View 1 Replies
View Related
May 16, 2000
Hi!
Please
I got the following Error Message when trying to update a AS400 Database
"(Table Name) on (Data Source) not valid for operation"
My question is? Does Sql Server require journaling the files? How Should I change this option?. Please Help!
I did try to fix the above error, but then I got a new message:
OLE DB provider 'MSDASQL' supplied inconsistent metadata. The object '(user generated expression)' was missing expected column 'Bmk1000'.
Could you help me?
I will appreciate your help.
Thanks in advance
Ivette V
View 3 Replies
View Related
Mar 15, 2004
I am getting an error trying to update a table in DB2 via SQL Server using a Linked Server. I can query the table using OPENQUERY but not via four-part name.
I can do this...
SELECT
Select * from OpenQuery(db2link, 'Select frst_nm from yccfssc9.person where id_prsn = 2')
When I try to Query using four-part names I get the following error...
7399 OLE DB Provider 'MSDASQL' reported an error. The provider does not support the necessary method.
UPDATE
When I try to update using this...
Update OpenQuery(db2link, 'Select frst_nm from yccfssc9.person where id_prsn=2') Set frst_nm = 'Fred'
I get the following error...
The OLE DB provider 'MSDASQL' indicates that the object has no columns.
When I try to update using four part names I get the error that says that the provider does not support the necessary method.
OTHER INFO
I was originially using version 6.1 of the db2 client (db2odbc.dll) but tried the 7.1 client and got the same error.
I believe we are using version 6.1 of db2 connect.
The mainframe db2 is version 7.0 release 1.0
Any help would be greatly appreciated. I referenced Microsoft Knowledge Base Article #270119 to get the workarounds that I attempted...
Gary
View 2 Replies
View Related
Nov 28, 2014
How to write this query so that I can pass a variable (from a cursor) for the were ORDER_ID =.
UPDATE OPENQUERY ([DISPATCHER_LIVE], 'select * from DCSDBA.ORDER_HEADER where ORDER_ID = ''405119-RM18''')
SET CONSIGNMENT = 'UNROUTED'
View 2 Replies
View Related
Jul 23, 2005
Hi,I'm using sql server 2000 sp4.I've 2 databases linked, an instance and my local.I'm getting two different errors when trying to update the remote table(local server) from the instance.There is only one row of data in the table with an identity field.1st sql:-UPDATE [local].[database].dbo.NUMBERS SET [f 1]=3This gives me the error:-Server: Msg 8180, Level 16, State 1, Line 1Statement(s) could not be prepared.Server: Msg 170, Level 15, State 1, Line 1Line 1: Incorrect syntax near '1'.If I was to remove the space from [f 1] and use [f1] it would workfine."select [f 1] from [dev001].[fashion Master].dbo.numbers"will return the correct valueAny Ideas ?2nd sql:-UPDATE [local].[database].dbo.NUMBERS SET [field1]=isnull([field1],0)+1This gives me the error:-Server: Msg 7306, Level 16, State 2, Line 1Could not open table '"fashion Master"."dbo"."NUMBERS"' from OLE DBprovider 'SQLOLEDB'. The provider could not support a row lookupposition. The provider indicates that conflicts occurred with otherproperties or requirements.[OLE/DB provider returned message: Multiple-step OLE DB operationgenerated errors. Check each OLE DB status value, if available. No workwas done.]OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowsetreturned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=TrueSTATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUTVALUE=600 STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyIDVALUE=True STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocateVALUE=True STATUS=DBPROPSTATUS_CONFLICTING],[PROPID=DBPROP_IRowsetChange VA...If I was to remove the isnull part, then it will work okAny ideas
View 3 Replies
View Related
Sep 13, 2007
I have 2 database servers ( 2 hardware-servers : A and B ) and I ve written a trigger for update in server A to execute insert statement in server B through linked servers, is there other way to achieve this without linked servers?? All using T-SQL.
Best Regards
Joseph
View 4 Replies
View Related
May 11, 2007
I have a linked server set up on my local SQL 2000 instance. I try and run an update against an SQL 2005 database and it take 29 seconds. I checked the execution plan and it says it takes the entire time on the Remote Scan. Is there something I need to do to speed this up? There is an index on the PK that I am searching against.
View 2 Replies
View Related
Jan 11, 2008
I've got the output from a cursor that generates the following sql statement.
Update SERVERNAME.DATABASENAME.dbo.TABLENAME set [update] = 'Y', status = NULL, completed = NULL where trigger_id = 10255
The statement generates the following error if the statement is run remotely
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'update'.
But the statement executes without error as formatted if run directly on the linked server.
Both servers are running 2000 sp 3a. The servers will be converted to 2005/2008 without patching to sp4.
View 5 Replies
View Related
Jul 20, 2005
Hi there,I'm pretty new to SQL and am having some porblems with a linked server.I have a table on a SQL server which stores employee information.I also have a view on a linked server which stores the same information.What I would like to happen is, whenever the view changes on the linkedserver I want the information to be changed in the table on my server.I've been trying to write a trigger to do this, but have had noluck so far.Can anyone help me?ThanksSimon--Posted via http://dbforums.com
View 1 Replies
View Related
Jul 27, 2005
When trying to update records in a Sybase 11 ODBC linked server got this error:
View 5 Replies
View Related
Oct 17, 2006
UPDATE CD SET col1=SR.col1,col2=SR.col2,col3=SR.col3,col4=SR.col4,col5=SR.col5,col6=SR.col6,col7=SR.col7,
col8=SR.col8,col9=SR.col9,col10=SR.col10
FROM LNKSQL1.db1.DBO.Table1 CD
join Table2 USRI on USRI.col00 = CD.col00
join table3 SR on USRI.col00 = SR.col00
Here, I'm trying to tun this from an instance and do a remote update. col00 is a primary key and there is a clustered index that exists on this column. When I run this query, it does a 'select * from tabl1' on the remote server and that table has about 60 million rows. I don't understand why it would do a select *... Also, we migrated to SQL 2005 a week or so back but before that everything was running smooth. I dont have the execution plan from before but this statement was fast. Right now, I can't run this statement at all. It takes about 37 secs to do one update. But if I did the update on a local server doing remote joins here, it would work fine. When I tried to show the execution plan, it took about 10 mins to show up an estimated plan and 99% of the time was spent on Remote scan. Please let me know what I can do to improve my situation. Thank you
View 4 Replies
View Related
Aug 14, 2001
In an ASP, I have a dynamically created SQL statement that amounts to "SELECT * FROM Server1.myDB.dbo.myTable WHERE Col1 = 1" (Col1 is the table's primary key). It returns the data immediately when executed.
However, when the same record is updated with "UPDATE Server1.myDB.dbo.myTable SET Comments = 'blah blah blah' WHERE Col1 = 1", the page times out before the query can complete.
I watched the program in Profiler, and I saw on the update that sp_cursorfetch was being executed as an RPC once per each row in the table. In a table of 78000 records, the timeout occurs well before the last record is fetched, and the update bombs.
I can run the same statements in Query Analyzer from a linked server and have the same results. The execution plan shows that a Remote Query is occurring on the select that returns 1 row, and a Remote Scan is taking place on the update scanning 78000 rows (I guess this is where all the sp_cursorfetch calls are happening...?).
How can I prevent the Remote Scan? How can I prevent the execution of the RPC sp_cursorfetch for each row in the remote table?
Thank you!
View 2 Replies
View Related
May 5, 2008
Hi Guys:
Our company wanted to try out SQL Server 2005 Enterprise Edition (64 Bit). So, we were on free trial of the Enterprise Edition for past 5 months. After which we decided to go for SQL SERVER 2005 Standard Edition (64 Bit). And, Last week, we installed the SQL Server Standard Edition (64 Bit) on our server. After installation, everything was restored as before.
The version we are on right now is:
Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
Strangely, a job which ran fine till then is failing with the following error: And, to be more specific, when this job is run manually in the form of a stored procedure using the query optimizer it runs fine. But, when its executed as a scheduled job on SQL Agent, it fails. The History logs record the following error.
Error :
The OLE DB provider "SQLNCLI" for linked server "LV-SQL2" reported an error. Authentication failed. [SQLSTATE 42000] (Error 7399) Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "LV-SQL2". [SQLSTATE 42000] (Error 7303) OLE DB provider "SQLNCLI" for linked server "LV-SQL2" returned message "Invalid authorization specification". [SQLSTATE 01000] (Error 7412).
I tried everything possible, even recreated the job, but, no avail.
I also considered the possibility if SQL Agent login account did not have enough permissions. So, I changed the SQL Agent login to windows authentication, but the job still fails. So, its the problem has nothing to do with login accounts either.
For the record, I have cross checked all these too.
1. Enabled the remote connections in Surface Area Config
2. Added local server login to remote server login mappings.
3. Checked 'Rpc' and 'Rpc Out' under server options. Also, the Connection Timeout and Query Timeout have been set to zero (0).
Any suggestions pointing towards problem solution appreciated.
Thank you.
View 4 Replies
View Related
Mar 2, 2007
1. Replication Transactional between two servers
-- SQL Server 2005 as Distributors and Subscribers
---SQL Server 2000 as Publishers
2. Linked Servers errors:
" Server (Publication Server) is not configured for DATA ACCESS. (Microsoft SQL Server, Error: 7411)"
Did anyone familiar with this problem?
Thankssss
TJ_1
View 4 Replies
View Related
Mar 8, 2001
could someone please explain the following error msg! the code seg that is gen the error is as follows, followed by the err msg. I"m trying to update a table on a linked server (paeddb1.gold). both servers are running MS SQL7
update paeddb1.gold.dbo.controls_peg
set amt = t.amt
from #temp_peg_control t, paeddb1.gold.dbo.controls_peg p
where t.peg = p.peg
and t.cntl_type = p.cntl_type
and t.fy = p.fy
Could not open table '"gold"."dbo"."controls_peg"' from OLE DB provider 'SQLOLEDB'. The provider could not support a row lookup position. The provider indicates that conflicts occurred with other properties or requirements.
[OLE/DB provider returned message: Errors occurred]
View 2 Replies
View Related
Sep 19, 2005
I have a "linked server" configured in my SQL Server 2000 (SP4) server, which used to work correctly. However, I had to reinstall SQL Server (I backed up and restored the master/model/etc databases, so all my settings stayed the same). Since then, I've been getting this error when I try to use the linked server:
Invalid schema or catalog specified for provider 'MSDASQL'.
OLE DB error trace [Non-interface error: Invalid schema or catalog specified for the provider.].
The linked server is a FoxPro database, which does not use catalog or schema names. So, my select syntax looks like this:
SELECT * FROM Server...Table
SQL Server is aparently expecting something like this:
SELECT * FROM Server.Catalog.Schema.Table
Does anyone know how I can fix it so that it allows the "empty dot" method to work like it used to?'
Thanks!
Josh
View 9 Replies
View Related
Apr 10, 2008
Hi Pals,
We have an SSIS package within which we are calling a stored procedure which eventually call a sql server dbo.fn() which contains code to lookup data inside oracle using Linked Server for Oracle.
We are calling the Package dynamically from the stored procedure by creating a SQL Server Agent Job, I am getting the below error very often. Can we fix the error in any way?
“EXEC sp_UpdateTname 369,'939390',2008 " failed with the following error: "Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "ORATEST".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
When I hard code global variables and ran the package I am able to see the successful execution of the package.
Can anybody point out where could the error lies?
View 1 Replies
View Related
Feb 20, 2008
I got the following error when using linked server:
OLE DB provider "SQLNCLI" for linked server "SACPANRPT" returned message "Cannot start more transactions on this session.".
Msg 7395, Level 16, State 2, Line 1
Unable to start a nested transaction for OLE DB provider "SQLNCLI" for linked server "SACPANRPT". A nested transaction was required because the XACT_ABORT option was set to OFF.
View 1 Replies
View Related
Jul 20, 2005
Hiwhen i try to run a query using linked servers, i get the followingerror.Server: Msg 125, Level 15, State 1, Line 1Case expressions may only be nested to level 10.I do have more than 10 case statements, it works fine when it is lessthan 10. can anyone tell me if there is a way to have more than 10case statements. thanks alot.Jaymy querySelect category, val, Sum(QTY) As QTY , yrFrom(Select val, QTY2 As QTY,KEEP = CaseWhen code = '004' And ((YR > 2003) Or (YR = 2003 And MON > 12))Then 'N'When CODE = '005' And ((YR > 2003) Or (YR = 2003 And MON > 12))Then 'N'When CODE = '003' And ((YR > 2003) Or (YR = 2003 And MON > 12))Then 'N'When CODE = '017' And ((YR > 2003) Or (YR = 2003 And MON > 12))Then 'N'When CODE = '007' And ((YR > 2003) Or (YR = 2003 And MON > 12))Then 'N'When CODE = '008' And ((YR > 2003) Or (YR = 2003 And MON > 12))Then 'N'When CODE = '009' And ((YR > 2003) Or (YR = 2003 And MON > 11))Then 'N'When CODE = '010' And ((YR > 2003) Or (YR = 2003 And MON > 12))Then 'N'When CODE = '038' And ((YR > 2003) Or (YR = 2003 And MON > 12))Then 'N'When CODE = '032' And ((YR > 2003) Or (YR = 2003 And MON > 12))Then 'N'When CODE = '030' And ((YR > 2003) Or (YR = 2003 And MON > 12))Then 'N'When CODE = '018' And ((YR > 2003) Or (YR = 2003 And MON > 12))Then 'N'Else 'Y' EndFromamf a Join linkedserver.source.dbo.table2 b On a.COM = b.COMWhere CATEGORY In ('1') And CODE In ('001','003','004','005')And b.YR Between 2003 And 2004 And b.MON <= 1) xWhere KEEP = 'Y'Group By CATEGORY, YR
View 2 Replies
View Related
Nov 23, 2007
HI
I have win 2003 64 sp2 SQL 2005 sp2. I downloaded an informix 64bit driver 3.00FC then set system dsn which works fine when i apply and test the connection.
I tried to create a new linked server using Microsoft OLE simple provider against the odbc when I get this error
Does anyone have any suggestions??
Many thanks
Robert
TITLE: Microsoft SQL Server Management Studio
------------------------------
"The test connection to the linked server failed."
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot initialize the data source object of OLE DB provider "MSDAOSP" for linked server "CERP". (Microsoft SQL Server, Error: 7303)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3186&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
View 28 Replies
View Related
Oct 30, 2007
Hello,
SQL Server version is Enterprise Edition 8.00.2039 SP4
and one oracle database linked via Microsoft OLe DB provider for Oracle. Oracle db name: SCP.
One of the job inserts data to sql database from querying oracle database; randomly fails with the error message below.
**************************
INSERT INTO mom_services ('SQL TABLE')
SELECT *
FROM OPENQUERY(SCP,'SELECT SERIAL_NO_ FROM SCADMIN.DEVICEM1 WHERE CONTAINER=''MOM''')
***************************
Server: Msg 7399, Level 16, State 1, Line 3
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: Oracle error occurred, but error message could not be retrieved from Oracle.]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: ].
What I have done;
Oracle client uninstalled server restarted and oracle client installed .
Linked server deleted and recreated but nothing changed.
Any ideas ?
Osman
View 5 Replies
View Related
Oct 17, 1999
I'm trying to execute commands on a SQL Server on the same domain. Following the instructions, I called sp_addlinkedserver N'LinkSQLSrvr', ' ', N'SQLOLEDB', N'NetSQLSrvr' then sp_addlinkedsrvlogin N'LinkSQLSrvr', false, N'Joe', N'Visitor', N'VisitorPwd'. No complaints. When I execute a select statement, however (eg. select top 10 colname from LinkSQLSrvr.dbname.dbo.tblname) I get:
Server: Msg 7353, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' supplied inconsistent metadata. An extra column was supplied during execution that was not found at compile time.
Tried it on different combinations of local and remote servers and I get exactly the same error each time. Help!
View 3 Replies
View Related
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
May 19, 2004
Server: Msg 7356, Level 16, State 1, Line 1 OLE DB provider 'SQLOLEDB' supplied inconsistent metadata for a column. Metadata information was changed at execution time.
OLE DB error trace [Non-interface error: Column 'customerID' (compile-time ordinal 1) of object '"ABCDB"."dbo"."vwCustomer_xxxxk"' was reported to have a DBCOLUMNFLAGS_ISNULLABLE of 0 at compile time and 32 at run time].
above error messages occurs when I try to run a proc which inside a query statement of select to a linked SQL6.5 database. The thing puzzled me is that
linked query to run outside the proc is just fine, but errors within the proc. the running server is MSDE 2k with sp3. Look at knowledgebase about "
DBCOLUMNFLAGS_ISNULLABLE" is only related to DB2 or SQL7 or SQL2000 before sp3.... man...
thanks
View 3 Replies
View Related
Apr 30, 2002
I'm running SQL Server 2000 and I have a linked server setup to another SQL Server 2000 box. I've got a stored procedure that creates a temporary table and is pulling information from local tables and from tables located on the linked server. When I run this stored procedure it bombs on me when it hits the linked server part returning an error message of:
Server: Msg 7391, Level 16, State 1, Procedure app_GetOfficeOrders, Line 29
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.
Now I can run a query against a table located on the linked server that returns just a table count and everything works fine. I'm just having problems with that one stored procedure. Any suggestions would be appreciated.
P.S. I have verified that it isn't a time-out error and it isn't a permissions issues.
Thanks
View 1 Replies
View Related
Jan 16, 2004
I am trying to call an RPC from SQL2KSP3 to Sybase ASE 12.5. SQL2K is on an IBM Intel Server running Win2KSP4, ASE is on an IBM RS6000 w/ AIX 5.2.
I have the ASE server set up as a linked server in SQL 2000, and doing queries is working fine, i.e. I can
SELECT * FROM asesrvr.testdb.dbo.tablename
and get back all the data in tablename from the testdb database on the ASE server asesrvr.
However, when I try to run an RPC using the same syntax, I get a generic 7212 error (Could not execute procedure 'procname' on remote server 'asesrvr'. I am calling the rpc with
DECLARE @output char(1)
EXEC asesrvr.testdb.dbo.procname @output output
I'm using Sybase's ASE OLEDB provider; on the Linked Server Properties, I have
General Tab
Product name: asesrvr
Data source: devprod
Provider string: <empty>
Location: greyed out
Catalog: <empty>
Security Tab
One local login, sa, which has impersonate checked as the sa passwords on the two servers are the same (I want to eliminate security as an issue while I try to get this working).
Server Options Tab
Collation Compatible: checked
Data Access: checked
RPC: checked
RPC Out: checked
Use Remote Collation: unchecked
Collation Name: <empty>
Connection Timeout: 0
Query Timeout: 0
SQL2K's @@version is
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Any ideas on how I can troubleshoot this further? It does not appear to be limited to this one procedure (this one only has one output parameter and one line of code, so it's about as simple as it can get for testing purposes). Again, queries work fine, so the linked server itself is connected and working, at least for queries.
Thanks very much for your help,
Vince
View 2 Replies
View Related
Mar 15, 2004
I have setup a linked server to a Sybase database using the ODBC driver (which in turn uses the Sybase OLE DB provider). Logged onto the box as the SQL Service account everything is good. Using Enterprise Manager from any machine to the SQL Server box using an account that is a local administrator on the SQL Server box everything is good. However, if I attempt to make a connection via Enterprise Manager with an account that does not have local administrator rights on the server I get an error. I get the same error if I attempt an OPENQUERY against a table in the Linked server database. The error I receive is as follows:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [SYBASE][ODBC Sybase driver]Allocation of a Sybase Open Client Context failed. Sybase normally generates a SYBINIT.ERR file contianing more specific reasons for failing.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].
The drives are secured so that only local administrators have rights to them.
I found in the Microsoft KB the follownig article:
http://support.microsoft.com/?kbid=814398
In this article it is suggesting that I need to open up the server from this lockdown. So, just to test this out without having to restart SQL Server I first allowed for full access to the C drive to "Everyone" and everything was good.
My question then is, has anybody else ran into this sort of thing? It doesn't seem right that I have to open up some folder on the database server to everyone in order to get a linked server going.
Thanks
View 2 Replies
View Related
May 7, 2015
We are currently having the following problem. When trying to connect using "Be made using the login's current security context" I am getting the following error message "Login Failed for NTAuthorityAnonLogin". Yesterday this worked. The setup is as follows
SQL2008R2, nine instances on a cluster. The instance can use "current security context" when both instances are on the same node ie instance 1 node a to instance 2 node a , but when one is on node a and the other is on node b, i.e., instance 1 node 1 to instance 2 node b, we get the error above.
View 4 Replies
View Related
Oct 25, 2007
I am trying to use a linked server and it works as long as I do not specify the sp_addlinkedserver @provstr parameter. If I specify that parameter I always get a 7416 "Access to the remote server is denied because no login-mapping exists" error. I have tried adding the logins various ways but it's very specific to the @provstr parameter, and it doesn't even matter what I put in that parameter. As soon as I put something in there whether it is valid or invalid, I get the error.
Anyone else seen this? There is an amazing lack of any discussion about the error when I search for it.
View 6 Replies
View Related
