The OLE DB Provider &&"SQLNCLI&&" For Linked Server &&"SQLSERVER&&" Returned A &&"NON-CLUSTERED..

 

Hi
 
i am try to execute a command " SELECT * FROM [SQLSERVER].[DATABASENAME].[DBO].[TABLE] ".. but i am receiving a error message

 
Msg 7319, Level 16, State 1, Line 19

The OLE DB provider "SQLNCLI" for linked server "SQLSERVER" returned a "NON-CLUSTERED and NOT INTEGRATED" index "MSmerge_index_1348915877" with the incorrect bookmark ordinal 0.

 
but when i try to check the linked server it's shows me the linked is okay..  The following command i use to check wethare the link is fine or not..
 

CREATE TABLE #foo

(

pingResult SYSNAME NULL

);



INSERT #foo

EXEC master..xp_cmdshell

'ping SQLSERVER;



IF EXISTS

(

SELECT 1

FROM #foo

WHERE pingResult LIKE '%TTL%'

)

BEGIN

PRINT 'Feel free to use linked server.';

 

END

ELSE

BEGIN

PRINT 'Linked server not available.';

END



DROP TABLE #foo;

 
 
and Following result i receive..
 

(13 row(s) affected)

Feel free to use linked server.

 
 
Can any One tells me where i m making mistake or how can i resolve this problem



ADVERTISEMENT

Transaction Scope - The Operation Could Not Be Performed Because OLE DB Provider &"SQLNCLI&" For Linked Server &"XXX_LINKED_SERVER&" Was Unable To Begin A Distributed Transaction. OLE DB Provider &"SQLNCLI&a

Hello, I've a problem with a software developed in C# with the framework 2.0. This is the error I receive : The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "XXX_LINKED_SERVER" was unable to begin a distributed transaction. OLE DB provider "SQLNCLI" for linked server "XXX_LINKED_SERVER" returned message "No transaction is active.". If I try directly to restart the process, it works fine. Is there someone who can help me ? This is the process 1. In C# --> Call of a Query : select from the linked server (db in sql 2005) and insert into a table SQL 2005 2. In the C# --> using (TransactionScope scope = new TransactionScope()) and insert in a table in SQL 2005 which is link server Thank in advance.

View Replies View Related

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

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 Replies View Related

Can't Connect To Linked SQL Server: Cannot Initialize The Data Source Object Of OLE DB Provider &&"SQLNCLI&&"

I find this most perplexing.

 

I have two servers, DEV and PROD.  Now my DEV server works just great, I can connect to the linked server, query, etc... all is well.

 

So I'm setting up my PROD server and when I go to add the linked server I get:

Cannot initialize the data source object of OLE DB provider "SQLNCLI".... and Unable to complete login process due to delay in opening server connection.

 

Now I am running SQL Server 2005 and connecting to an SQL 2000 server.

 

The odd part is that this works just fine on DEV.

 

When I go to create the linked server I set:

Linked Server: "LinkedServerName"

Server Type: "SQL Server"

 

and that's it.

 

I go to Security and enter my DOMAINUSER.ACCOUNT and then enter the login creds for the linked server.

 

When I click "OK" I get the above mentioned error code.

 

Any thoughts?

 

View Replies View Related

Linked Server Issue: OLE DB Error Trace [OLE/DB Provider 'SQLOLEDB' IUnknown::QueryInterface Returned 0x80004005

Window Server 2003 R2 Standard Edition (x64) SP1
Sql Server 2000 8.00.2039 SP4 Enterprise Edition (32 bit version)
 
A linked server is configured to a sql 2000 server and when I execute sql statement SELECT * FROM [LinkedServer].[Database].[dbo].[TableName] it gives following error message: -
 
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IUnknown::QueryInterface returned 0x80004005:  The provider did not give any information about the error.].
 
In case if anyone has a solution to it, please let me know.

View Replies View Related

Cannot Create An Instance Of OLE DB Provider &&"SQLNCLI&&" For Linked Server &&"SERVERNAME&&", Error: 7302

 

I just installed a SQL Server 2005 Express SP2 instance on a server with an existing SQL Server 2000 SP3 installation.  (I need SQL Server 2005's INSERT from an EXEC capability).  It's working great now except for one thing: I can't link any other SQL servers!  I've already successfully added and queried a linked Oracle server, but attempting to add a linked SQL server gives me the following error, no matter which SQL provider I try to use:
 



Code Snippet
"The linked server has been created but failed a connection test.  Do you want to keep the linked server?"
Additional information:
  --> An exception occured while executing a Transact-SQL statement or batch (Microsoft.SqlServer.Express.ConnectionInfo)
    --> Cannot create an instance of OLE DB provider "SQLNCLI" for linked server "SERVERNAME". (Microsoft SQL Server, Error: 7302)
 
 
The technical details reveal the error source as "sp_testlinkedserver".



 
I've scoured the net and premier support for advice on this problem, but the little I found wasn't helpful.  I've tried reinstalling the SQL Native Client, but it didn't help.  I've tried uninstalling SQL Server 2005 Express completely (including management studio and native client), rebooting, and reinstalling everything, with no luck.  The server (obviously) does not have a firewall enabled.  I've tried stopping the SQL 2000 Server installed on the system, no help.  If I create a test .UDL file on the system, pointing to any of the SQL servers I'm trying to link, clicking the "Test Connection" button returns successful.  I'm also able to connect to and query the servers directly from the SQL 2005 Express Management Studio.  I just can't add them as linked servers.
 
The server is running Windows 2003 SP1, SQL Server 2000 SP3, and SQL Server 2005 Express SP2 (the default "SQLExpress" named instance).  I've tried setting up SQL 2005 Express to run under the network service account and under a domain account used by the other SQL 2000 servers.
 
From other SQL 2000 servers, I can connect and link to the SQL Server 2005 Express instance successfully.   I can also successfully add linked SQL servers in SQL Server 2000 instance installed on the same server.  Only adding linked SQL servers in 2005 Express seems to be broken.
 
Anyone have any other suggestions?  I'm totally baffled.  Thank you so much for any helpful advice.

View Replies View Related

OLE DB Provider &&"MSDAORA&&" For Linked Server &&"DBNAME&&" Returned Message &&"ORA-01476: Divisor Is Equal To Zero

 
 

Hi All,

 

I have a strange problem with one of my Linked server connections. I am connecting from
SQL Server 2005 Standard Edition (9.00.3042.00) to and Oracle 10g database using the Microsoft OLE DB Provider for Oracle. The connection works and I am able to select from all the Oracle tables except for 1 table. When selecting from this table I get the following error:
 
 
OLE DB provider "MSDAORA" for linked server "DBNAME" returned message "ORA-01476: divisor is equal to zero".
 
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "MSDAORA" for linked server "DBNAME".
 
 
 
This probem happens if the table has rows or has no rows (empty table).
 
I have tried SELECT * and count(*) along with selecting a specific column from the table.
 
I can use SQL*plus from the system that the SQL database server running on it and select from the table using the same login/password that the linked server uses to  rule out a permissions problem.
 
Any suggestions on what I can try next?
 
 
Thanks.
 

View Replies View Related

SQLNCLI Linked Server Error

I've been assigned with finding the issue behind this error on one of our production systems:




TCP Provider: The semaphore timeout period has expired. [SQLSTATE 07008] (Error 121) OLE DB provider "SQLNCLI" for linked server "SERVER1" returned message "Communication link failure". [SQLSTATE 01000] (Error 7412). The step failed.




I've Googled the error to death, searched the forums here and at other SQL sites as well and have found nothing that points me at the cause.  I've found the individual parts of the error but not the whole.  Maybe I've just been looking at it too long and am missing something simple.  Anyone have any ideas?





These are two linked SQL2005 64bit enterprise Win2k3 servers.  The error comes when an automated daily job is run.  Sometimes it happens at once, sometimes after 5 minutes, sometimes after an hour.  I can see nothing wrong with the server itself or the network.




Thanks in advance.

View Replies View Related

SQLNCLI Error After Column Resize On Linked Server

 

I am linking to another SQL Server database to extract data into my own system.  Following an increase in size on one of the columns in a table I am extracting, I am getting the following error:
 
'OLE DB provider 'SQLNCLI' for linked server 'gsc-rpt.svr.bankone.net' returned data that does not match expected data length for column '[gsc-rpt.svr.bankone.net].[Peregrine].[dbo].[scProblem].short_description'. The (maximum) expected data length is 100, while the returned data length is 148'
 
The column short_description has been increased from 100 to 1024.  Writing a simple query, SELECT (max(len(short_description)) FROM dbo.Peregrine_Problem will generate the error, but if I embed the same SQL into an Excel VBA macro and connect to the server, it happily returns the result 1024.  Our Access databases also link to the table on the remote server and handle the increased column size properly.
 
I have done everything I can think of, including deleting the linked server within SQL Server MS, and recreating it, but I can't override the setting of 100.
 
Any ideas?

View Replies View Related

Accesssing Mirrored Databases Via SQLNCLI Linked Server?

Does anyone know how to configure a mirrored pair as a linked server on a 3rd instance?


Say I have a mirrored database on two servers: PRIMARY and SECONDARY.


I want to create a linked server on a 3rd machine that allows me to access the database on the mirrored pair.


This is what I'm using:


EXEC master.dbo.sp_addlinkedserver
 @server = N'MIRROR',
 @srvproduct=N'',
 @provider=N'SQLNCLI',
 @provstr=N'Server=PRIMARY;FailoverPartner=SECONDARY;'

select count (*) from mirror.pubs.dbo.authors




and it works fine if the database on PRIMARY is alive. however when the mirror has failed over to SECONDARY and PRIMARY is no longer available, I get the following when I try to query the database via the linked server:


OLE DB provider "SQLNCLI" for linked server "MIRROR" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "MIRROR" 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.".
Msg 10061, Level 16, State 1, Line 0
TCP Provider: No connection could be made because the target machine actively refused it.


As far as I can tell, it doesn't try to contact SECONDARY at all. It seems like SQL Server is ignoring the FailoverPartner attribute.

If I switch PRIMARY and SECONDARY in the connection string (ie @provstr=N'Server=SECONDARY;FailoverPartner=PRIMARY;') then it works when SECONDARY is online, but not when the mirror has failed back to PRIMARY.

Any ideas?

Piers.

View Replies View Related

Accesssing Mirrored Databases Via SQLNCLI Linked Server?

Does anyone know how to configure a mirrored pair as a linked server on a 3rd instance?


Say I have a mirrored database on two servers: PRIMARY and SECONDARY.


I want to create a linked server on a 3rd machine that allows me to access the database on the mirrored pair.


This is what I'm using:


EXEC master.dbo.sp_addlinkedserver
 @server = N'MIRROR',
 @srvproduct=N'',
 @provider=N'SQLNCLI',
 @provstr=N'Server=PRIMARY;FailoverPartner=SECONDARY;'

select count (*) from mirror.pubs.dbo.authors




and it works fine if the database on PRIMARY is alive. however when the mirror has failed over to SECONDARY and PRIMARY is no longer available, I get the following when I try to query the database via the linked server:


OLE DB provider "SQLNCLI" for linked server "MIRROR" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "MIRROR" 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.".
Msg 10061, Level 16, State 1, Line 0
TCP Provider: No connection could be made because the target machine actively refused it.


As far as I can tell, it doesn't try to contact SECONDARY at all. If I switch PRIMARY and SECONDARY in the connection string (ie @provstr=N'Server=SECONDARY;FailoverPartner=PRIMARY;') then it works when SECONDARY is online, but not when the mirror has failed back to PRIMARY.

Any ideas?


Piers.

View Replies View Related

Provider=SQLNCLI.1

hi guys,

I'm using MS SQL Server 2005 with MDAC 2.8. When I test my project into TCP/IP, the project does not pick the records completely.

My connection string is using Provider=SQLNCLI.1

Do I need to install SQL Native Client to work-stations ?
Do I need to remove the MDAC ?

Than you.

View Replies View Related

Out-of-process Use Of OLE DB Provider &&"SQLNCLI&&" With SQL Server Is Not Supported. MSG 7430

I am trying to create a Linked Server Connection from SQL2005 64 bit Developer edition SP2 (3054)  to a SQL Express SP2 instance (3042) and have received the above error message. It works perfectly when connecting the SQL Express box to the Developer edition. Just Not the other way. I have seen this error listed inconjunction to SSAS but not to express.
Any Clues??
Chris

View Replies View Related

Data Provider Or Other Service Returned An E_Fail Status In Sql Server 2005

after converting database from sql server 2000 to sql server 2005. my program on vb6 is facing runtime error. i.e.data provider or other service returned an E_Fail status .

how can i get rid this problem

View Replies View Related

SQL Server 2005 && Data Provider Or Other Service Returned An E_FAIL Status

Dear all,

 

I am running an Access adp application with SQL Server 2005 as back end database. I run a query by using Management Studio query window, and it returned correct results with some columns containing NULL value. But when I run this query through MS Access client side, popup an error "Data provider or other service returned an E_FAIL status" and crash the Access application. I moved the database back to SQL Server 2000, and it runs perfect on both client side and server side returning the correct result. This query is important for the application. Please help!!!!

 

Query as followed:

 

 SELECT     TOP (100) PERCENT dbo.VWINFO312FYTRStreamEnrolments.StudentID, dbo.RequiredStreams.StreamType,
                      dbo.VWINFO312FYTRStreams.StreamCode + CAST(dbo.VWINFO312FYTRStreams.StreamNo AS varchar) AS FullStreamCode,
                      dbo.DaysOfWeek.DayCode, dbo.VWINFO312FYTRClasses.StartTime, dbo.VWINFO312FYTRClasses.EndTime, dbo.VWINFO312FYTRClasses.Room,
                      dbo.Tutors.TutorName, dbo.Tutors.PhoneExtn, dbo.Tutors.OfficeHours, dbo.DaysOfWeek.DaySequence, dbo.RequiredStreams.StreamOrder
FROM         dbo.RequiredStreams INNER JOIN
                      dbo.VWINFO312FYTRStreams ON dbo.RequiredStreams.PaperID = dbo.VWINFO312FYTRStreams.PaperID AND
                      dbo.RequiredStreams.StreamCode = dbo.VWINFO312FYTRStreams.StreamCode INNER JOIN
                      dbo.VWINFO312FYTRStreamEnrolments ON dbo.VWINFO312FYTRStreams.PaperID = dbo.VWINFO312FYTRStreamEnrolments.PaperID AND
                      dbo.VWINFO312FYTRStreams.StreamCode = dbo.VWINFO312FYTRStreamEnrolments.StreamCode AND
                      dbo.VWINFO312FYTRStreams.StreamNo = dbo.VWINFO312FYTRStreamEnrolments.StreamNo LEFT OUTER JOIN
                      dbo.DaysOfWeek INNER JOIN
                      dbo.VWINFO312FYTRClasses ON dbo.DaysOfWeek.DayCode = dbo.VWINFO312FYTRClasses.DayofWeek ON
                      dbo.VWINFO312FYTRStreams.PaperID = dbo.VWINFO312FYTRClasses.PaperID AND
                      dbo.VWINFO312FYTRStreams.StreamCode = dbo.VWINFO312FYTRClasses.StreamCode AND
                      dbo.VWINFO312FYTRStreams.StreamNo = dbo.VWINFO312FYTRClasses.StreamNo LEFT OUTER JOIN
                      dbo.Tutors ON dbo.VWINFO312FYTRClasses.ResponsibleTutor = dbo.Tutors.TutorID

 

View Replies View Related

Data Provider Or Other Service Returned An E_Fail Status In Sql Server 2005

Main_Module.STRCNNN1 = "driver={SQL Server};server=" & Main_Module.Server_Name & ";" & _
                    "uid=SA;pwd=;database=" & Main_Module.Common_Database
       error msg is

runtime error

data provider or other service returned an E_Fail status in sql server 2005

 

when i am fetching record from view using order by clause in select statement then error is coming

as

select * from vew_emp where grade='C' order empno

 

at the first time it fetching record with order by

but when i am using recordset.Requiry

it cud not fetch the record

i think when the recordset is open with the record of view

i am deleting ,inserting record in the view base table then the main recordset is not working

without using order by it is running and working well
plz reply me quickly

 

View Replies View Related

Solution To: Ad Hoc Access To OLE DB Provider Has Been Denied. You Must Access This Provider Through A Linked Server

Hope this helps someone.SQL server 2000. I imported some text into a table."insert into tbltst (field1, field2) SELECT field1, field2 FROMOpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source=c: emp;Extendedproperties=Text')...tstfile#txt"It worked great. As long as I was logged in as user sa. When I finishedtesting, I began to receive the error:"Ad hoc access to OLE DB provider [Microsoft.Jet.OLEDB.4.0] has been denied.You must access this provider through a linked server"This solves the problem. Put it in a .reg and execute it:Windows Registry Editor Version 5.00[HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSSQLServer ProvidersMicrosoft.Jet.OLEDB.4.0]"AllowInProcess"=dword:00000001"DisallowAdhocAccess"=dword:00000000The last line does the trick. If DisallowAdhocAccess is absent, itapparently defaults to 1./jim

View Replies View Related

SQLNCLI Gives Wrong Column Data From Linked Svr

I am running an SSIS package to extract data from a source SQL Server database to our own database. The source is on a linked server. The package worked until a column size was increased on a table on the source.

If I run a query against the linked server table I get the following error:

'OLE DB provider 'SQLNCLI' for linked server 'GSC-RPT.SVR.BANKONE.NET' returned data that does not match expected data length for column '[GSC-RPT.SVR.BANKONE.NET].[Peregrine].[dbo].[scProblem].short_description'. The (maximum) expected data length is 100, while the returned data length is 160.'

I've tried deleting the links and recreating them. Nothing works. I don't understand why my database query is expecting a data length of 100. Why does it not pick up the definition of the field length from the linked server? How has it retained this expectation when I have deleted the linkage and recreated it?

View Replies View Related

Installing SQLServer 2000 Service Packs On Clustered Server

Hi,I am planning to install sql server 2000 sp4 on my clustered serverrunning in active/passive mode.I plan to stop all sql services using cluster administrator, installthe service pack from the primary node and then reboot all nodes.Are there any missing steps that need to be done or problems thatanyone has come across?ThankLyn

View Replies View Related

SQLSERVER 2005 X64 Linked Server To SQLSERVER 7.0

Hello people.

I am in the process of planning a server upgrade to sql2005 x64.

I created 2 linked servers: one to a SQL2000 sp4 server and one to a SQL7.0 SP3.

I have the following error when I query the linked servers.
OLE DB provider "SQLNCLI" for linked server "IVDM2K" returned message "Unspecified error".
OLE DB provider "SQLNCLI" for linked server "IVDM2K" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "IVDM2K". The provider supports the interface, but returns a failure code when it is used.

I am aware of KB 906954.
http://support.microsoft.com/default.aspx?scid=kb;en-us;906954

I applied the instcat.sql on the SQL2000SP4 server and my linked server issues for that one are gone.

However, I ran the instcat.sql script on the SQL7.0 sp3 server and the linked server is still giving me an issue.

Can someone help me find a solution to this?

View Replies View Related

Linked Server To Excel - Columns Returned Alphabetically?

I have an existing SP which returns a set of results for a report.
I am trying to insert the resultset from the SP into an excel spreadsheet which is attached as a linked server, so that our users can manipulate the results further themselves.

My problem is that the OLE DB provider seems to sort the columns in the excel
spreadsheet into alphabetical order.

Anyone have any idea what is causing this????

e.g Columns in the spreadsheet are:
SupplierDescription, Cost, Quantity, Price

If you execute "SELECT * FROM xlsDailyPurchaseOrder...[Sheet1$]" the
resultset is ...
Cost Price Quantity SupplierDescription
==== ===== ======== ================

This causes havoc with my statement
INSERT INTO xlsDailyPurchaseOrder...[Sheet1$] EXEC
KNM_spSELRPTDailyPurchaseOrder
because although the fields from the stored procedure are in the same order as the columns in the spreadsheet, the order is shuffled somehow???

Anyone tried this?

View Replies View Related

Linked Server - OLE/DB Provider 'MSDAORA'

We have created linked server between SQL Server 2000 and Oracle 10g in offshore environment as well as onsite environment,both the environments are almost IDENTICAL.Basically, the whole execution happens on Oracle and the output is passed to SQL Server.When the below query is executed in offshore environment it is returning values perfectly.

SELECT * FROM OPENQUERY(ORACLE_LINK,'{Call TEST.IVR.lo_Dlr_GetInfo(55,{resultset 2,p_ReturnVal})}')

Here: ORACLE_LINK - is the Linked Server Name,
TEST - Oracle Schema Name
IVR - Oracle Package Name

But when the same query is executed on onsite environment it is raising the following error.

Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB provider 'MSDAORA'.
OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandPrepare::Prepare returned 0x80040e14].
The environment details are:

Offshore Environment(Successfully returning values):

1)Operating System - Windows 2000 Advanced Server
2)Service pack on Operating System - SP4
3)Version on MSDORA.dll(C:ProgramFilesCommonFilesSystemOLED B) - 2.81.1117.0
4)Version on tstbestsql1---SQL Database - SQL Server 2000 Enterprise Edition
5)ServicePack on tstbestsql1---SQL Database - 8.00.2040 (SP3a)


Onsite Environment:

1)Operating System - Windows 2000 Advanced Server
2)Service pack on Operating System - SP4
3)Version on MSDORA.dll(C:ProgramFilesCommonFilesSystemOLED B) - 2.81.1117.0
4)Version on tstbestsql1---SQL Database - SQL Server 2000 Enterprise Edition
5)ServicePack on tstbestsql1---SQL Database - 8.00.2040 (SP4)

We are able to execute if we are executing the query as

SELECT * FROM OPENQUERY(ORACLE_LINK,'select column1 from table1') on the Onshore server and it is perfectly returning the value.I believe the problem might be with "Call" if anyone can help with it that will be great.

Thanks,
Raahul

View Replies View Related

Doing Transactions With Linked Server(ole Db Provider)

Hi all,

When i begin a distributed transaction..., do i need to always
set xact_abort on?

But when i tried to put custom error handling to roll back a transaction with a linked server , it seems its impossible (as set xact_abort on, already automatically rolls back the transaction)

Does anyone have any ideas on this?

Thanks in advance

Benny

View Replies View Related

ODBC Provider For Linked Server.

Gurus,

I cant find any odbc provider from my linked server.(SQL 2005)
I am using MDAC 2.8 SP1 ON WINDOWS XP SP2 on my machine.
What can i do to get odbc provider..?

Please help me to do this

Thanks
Krishna

View Replies View Related

How To Determine, Inside A Function, If A Linked-server-query Returned Results

Hi, have configured an ODBC linked server for an Adaptive Server Anywhere (ASA6.0) database.
I have to write a function (not a procedure) that receives a number (@Code) and returns 1 if it was found on a table in the linked server, or 0 if not. Looks very simple...
One problem, is that the queries on a linked-server must be made through the OPENQUERY statement, which doesen't support dynamic parameters. I've solved this making the whole query a string, and executing it, something like this:

SET @SQL='SELECT * FROM OPENQUERY(CAT_ASA, ''SELECT code FROM countries WHERE code=' + @Code + ''')'
EXEC sp_executesql @SQL

(CAT_ASA is the linked-server's name)

Then, i would use @@ROWCOUNT to determine if the code exists or not. But before this, a problem appears: sp_executesql is not allowed within a function (only extended procedures are allowed).
Does somebody know how to make what i want?? I prefer to avoid using temporary tables.
Thanks!

View Replies View Related

ORA-03113 Via Linked Server With Ole DB Provider For ORacle

Hi all,

I am using MSSQL2005 and created a linked server with Ole DB provider for ORacle to connect to Oracle.

sp_tables_ex linked_server  

It shows the tables in oracle, however, it show error with selecting the oracle tables via the linked server,

Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'MSDAORA'
reported an error. 
[OLE/DB provider returned message: Unspecified error] [OLE/DB provider returned message: ORA-03113: end-of-file on communication channel ] OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBSchemaRowset::GetRowset
returned 0x80004005:   ].

Any Idea?  Thanks a advance.

 

View Replies View Related

Test Connection Failed Because Of Error Initializing Provider. The HTTP Server Returned The Following Error : Not Found

 

Hi All,
 
I am using windows 2003 server and i have installed SSAS 2005 and configured http request for AS 2005 with this below url : http://www.microsoft.com/technet/prodtechnol/sql/2005/httpasws.mspx.  I had tried all the possiblities given in this url.  But i am getting like "Test connection failed because of error initializing provider.  The HTTP Server returned the following error : Not found" when i create udl file.  Moreover i have installed MSOLAP 3.0 and OLAP 9.0 provider and MSXML 6.0 Parser.
 
Can you anyone please provide solution for this?
 
Thanks in advance,
Anand Rajagopal

View Replies View Related

OLE DB Connection Error; Provider Becomes Unavailable To Linked Server

We have a SQL2005 server install running on a Win2003 Server.  It has a linked server to iHistorian using the iHOLEDB provider for iHistorian. Randomly and after a few execution to this provider, we start getting errors

Msg 7303, Level 16, State 1, Line 2

Cannot initialize the data source object of OLE DB provider "IhOLEDB.iHistorian" for linked server "fspheafhs1".I created a data source in VS2005 for BI on the SQL server using the iholedb provider.  This was successful.  However, all queries or OPENQUERY statements to the linked server fail.  This seems to continue until SQL Server is restarted. Does anyone know how to reset a provider in SQL Server or tell if the SQL Server is having trouble with the provider?  There are no message in the SQL Error Log. Again the provider continues to work outside of SQL Server, but no longer can be accessed from within SQL Server. Thanks in advance.

View Replies View Related

How To Create A Linked Server In SQL2k5 To A SQL2k That Uses The SQL OLEDB Provider

I have a SQL Server 2005 Express SP2 Server. By using the SQL Server Management Studio Express, I want to create a Linked Server pointing to a SQL Server 2000 SP4 Server. But I want the configuration to use the OLEDB Provider for SQL instead of the SQL Native Client:
Linked Server: MyRemoteServer
Provider: Microsoft OLE DB Provider for SQL Server
Product Name: SQL Server
Datasource: MyRemoteServer
Provider String: "Provider=SQLOLEDB; Persist Security Info=True; Integrated Security=SSPI; Trusted_Connection=Yes;"

However, when I press "Ok" the Linked server just created always uses the SQL Native Client.

Is there a way to create a linked server to a SQL Server 2000 that uses the SQL OLEDB Provider?

View Replies View Related







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