Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







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


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
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
Named Pipe Provider/Linked Server Connection Error
Hello, everyone!

I'm currently having an issue with a linked server. Here's the surrounding information:
 
A) I have a clustered SQL Server 2005 Instance (A) and a SQL Server 2000 instance (B).
B) There is a linked server on A to B. When I set it up, I did run the fix to ensure A could talk to B (There was an issue with communication between 2005 and 2000 servers). It has been there since I installed A, and has worked fine.. Until last week.
C) This linked server uses static credentials to connect to B.
D) Named Pipes are enabled on both servers to listen to both connections on both A and B in cliconfg. So A has a named pipe listening for B, and B has a named pipe listening for A.
E) A has a view  that looks at a table on B - It's a table view, very simple, just pulls in all the data from the table on B.
 
 
So, I go to do a select statement from the view, connected as a user other then 'sa'.  I then get the error:
 
Named Pipes Provider: Could not open a connection to SQL Server Linked Server
Error Source: ncli Client (Paraphrasing, didn't copy that down, but it was the ODBC connector)
 
Well, that's odd. It's been working fine for months now..

I then go and connect as sa on A to query B. It works! Mind you, absolutely NO QUERY from A to B will run again until I run SOME kind of query has been run as sa on A.
 
Mind you, this is not a credential issue. Every user who connects to the linked server to B uses a stored credential that is DBO to the database on B. The same User Name and Password exists on both A and B.
 
Also of note, I check the activity monitor. There's a process that is "dormant" every time I run a query against the view. The details of this connection are:

sp_reset_connection;1
 
My question is, why is it resetting the connection on B when being queried from A? Why is it "all of a sudden" a problem? Are there any changes that coudl ahve been made that would cause this?

Any help with this confusing issue would be appreciated.

Thanks!
 
 

View Replies !   View Related
VFPOLEDB Provider Access Denied Error On Linked Server
Hallo
i'm trying to connect a VFP DBC as a linked server on MSSQL2000sp3a, using following parameters:


Product name: VFP

Data source: \networkpath odbc

Provider: VFPOLEDB.1

No security context
I'm trying the same operation both from a W2000sp4 server (+ MDAC 2.8sp1 ?) and a WXPPsp2, against two identical copies of the DBC that resides on two different path (one on the final path and one on a test path).
From WXPPsp2 everything runs ok, from W2000 i'm always getting this error:
Error 7399: OLE/DB Provider 'VFPOLEDB' IUnknown::QueryINterface returned 0x80070005: Access Denied
(already reported in this thread, http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1832362&SiteID=1 but it didn't helped me)
 
The error happens independently from the user the query runs under (basicly AD account, both domain admin and normal user).
MSSQL runs under a domain account which have full permissions (for test purpose) on the directories where DBC reside.

I did no further modifications on the WXP machine in order to have the linked server running, simply it ran smoothly from the beginning.
 
Clearly the difference rely on the different OS... but what else?

i ran component checker to determine the MDAC version but some components version numbers slightly differs from the expected ones (eg. resulting version is 2.0.1064 versus 2.0.1022 expected in MDAC 2.8) so i suppose to have MDAC2.8 sp1 on the server.
thanks in advance

View Replies !   View Related
&<[OLE/DB Provider Returned Message: New Transaction Cannot Enlist In The Specified Tr
Hi,
I'm new to this grp. when i come across the problem with linked server while running stored procedure saying "<[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]>" I googled that message and got the solution from msdn.
All we have to do to get rid of that message is change security configuration of MSDTC of Win XPsp2 or Win2003 server and also we need to enable port 135 to allow dtc to work using RPC. Now it seems the problem is resolved. But still the problem is occuring once every a while.
I'm scraching my brain wht could be the reason. i've tried all the ways but still the problem exists.

Hope i'm clear in explaining my problem. Am i?
Please help me

Thanks
Ramesh

View Replies !   View Related
An Error When Instantiating C#-based COM Object In OLE DB Provider Linked To SQL Server Express
Hi,

 
I'm working on an OLE DB provider. This provider is supposed to retrieve data from managed application running in background. The data retrieval is handled by C# component communicating with source application via remoting, the data are then exposed via component's COM interface. OLE DB provider itself instantiates the communication component.
 
Now, the problem: this worked ok in SQL server 2000, but it doesn't seem to work in SQL server express edition. Concretely, the communication component fails to get created (CoCreateInstance ends with 'class not registered') and SQL server logs this:
 

2007-08-24 14:48:49.42 spid51      Error: 6511, Severity: 16, State: 20.
2007-08-24 14:48:49.42 spid51      Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x80131022. You may fix the problem and try again later.
 

I've googled a bit and found out someone else getting this message, and he was told something like 'trying to load CLR in some cases is disabled in newer (>2000) versions of SQL server'.
 
Now, I have very little knowledge of SQL server, CLR integration etc., so I'm not sure whether this applies to my case as well, but I think so - that problematic COM object is C#-based.
 
I'd be glad for any hints, ideas how to solve this issue, I just need the OLE DB provider to be able to access that managed application.

 
Thanks,L.

View Replies !   View Related
Provider 'MSOLAP.2' ICommandPrepare::Prepare Returned 0x80004005
Hi,I have a stored procedure that makes an MDX query for me, on SQL 2000, service pack 3 it works fine, but on service pack 4 it stops working with the error:Server: Msg 7399, Level 16, State 1, Line 1OLE DB provider 'MSOLAP.2' reported an error. The provider did not give any information about the error.OLE DB error trace [OLE/DB Provider 'MSOLAP.2' ICommandPrepare::Prepare returned 0x80004005:  The provider did not give any information about the error.].and i'f i call @@Error I get the error number 7399.Any ideas as to what might be going on? The stored procedure which worked prior to service pack 4 is as follows:CREATE PROCEDURE MDXTester
(
@CustId Varchar(4)
)
AS
IF NOT EXISTS(SELECT * FROM master..sysservers where srvname = 'CZVCube')
BEGIN
EXEC sp_addlinkedserver 'CZVCube',
'',
'MSOLAP.2',
'10.0.41.128',
'CZV'
END

DECLARE @OPENQUERY nvarchar(4000), @MDX nvarchar(4000), @LinkedServer nvarchar(4000)
SET @LinkedServer = 'CZVCube'
SET @OPENQUERY = 'SELECT * FROM OPENQUERY('+ @LinkedServer + ','''
SET @MDX = 'WITH
MEMBER [Measures].[YTD NV] AS ''''Sum(YTD(),[Net Value])''''
MEMBER [Measures].[YTD Prev] AS ''''Sum(YTD(),([Measures].[Net value], ParallelPeriod([Fiscal year], 1, [FiscalYear].CurrentMember)))''''
MEMBER [Measures].[YTD Change] AS ''''[Measures].[YTD NV] - [Measures].[YTD Prev]'''', FORMAT_STRING = ''''###,###.00''''
MEMBER [Measures].[YTD Change Perc] AS ''''[Measures].[YTD Change] / [Measures].[YTD Prev]'''', FORMAT_STRING = ''''###,##0.0%''''
MEMBER [Measures].[Monthly Change] AS ''''[Net Value] - ([Net Value],FiscalYear.PrevMember)'''', FORMAT_STRING = ''''###,###.00''''
MEMBER [Measures].[Monthly Change Perc] AS ''''([Monthly Change] / ([Net Value],FiscalYear.PrevMember))'''', FORMAT_STRING = ''''###,##0.0%''''
MEMBER [Measures].[Annual Change] AS ''''[Net Value] - ([Measures].[Net value], ParallelPeriod([Fiscal year], 1, [FiscalYear].CurrentMember))'''', FORMAT_STRING = ''''###,###.00''''
MEMBER [Measures].[Annual Change Perc] AS ''''([Annual Change] / ([Measures].[Net value], ParallelPeriod([Fiscal year], 1, [FiscalYear].CurrentMember)))'''', FORMAT_STRING = ''''###,##0.0%''''
MEMBER [Measures].[12 mth mov av] AS ''''Avg([FiscalYear].CurrentMember.Lag(11):[FiscalYear].CurrentMember, [Measures].[Net Value])''''
SELECT {[Measures].[Net Value] , [Measures].[YTD NV], [Measures].[YTD Prev],[Measures].[Monthly Change], [Measures].[Monthly Change Perc], [Measures].[Annual Change], [Measures].[Annual Change Perc], [Measures].[YTD Change], [Measures].[YTD Change Per
c], [Measures].[12 mth mov av]} ON COLUMNS,
LastPeriods(12, [FiscalYear].[Apr 08]) ON ROWS
FROM CZV
where [C_CRMID].[' + @CustId + ']'
EXEC(@OPENQUERY + @MDX + ''')')

  

View Replies !   View Related
OLE/DB Provider Returned Message: Deferred Prepare Could Not Be Completed
I have 2 SQL servers. And in the first one I have added the second SQL as a Link Server. When I run an SQL statement on the linked server I get the following message.
Server: Msg 7202, Level 11, State 1, Line 1Could not find server 'PROD' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.[OLE/DB provider returned message: Deferred prepare could not be completed.]
The SQL statement that I am runnins is
Select * from openquery(PROD,'Select * from PROD.GMS.dbo.qryDispCL')
But when I run only the SQL statement "Select * from PROD.GMS.dbo.qryDispCL" it works perfect. But I need to have the first statement running.
Please help. Your valuable feedback is greatly appriciated.
 

View Replies !   View Related
Error: A Rowset Based On The SQL Command Was Not Returned By The OLE DB Provider.
I have a dataflow task. On which I have OLEDB as my source. I connect to my database and execute a stored proc. the stored proc results in a result set with only one row and two columns. First Column is an integer and the second row is a varchar(max) with xml script in it. Not that it should matter because it is in varchar(max).

Anyway, it give me an error

[OLE DB Source [321]] Error: A rowset based on the SQL command was not returned by the OLE DB provider.

 

What am I doing wrong?

Can I not have a stroed proc that returns a result set as my data source?

 

View Replies !   View Related
Missing Data Mining Provider In Management Studio Linked Server Definition
 

I'm trying to define a linked server using Microsoft OLE DB Provider for Data Mining Services. MSDMine data provider is missing. Do you know where I can get this? Thanks in advance.

I'm have a Xeon 64bit proc. using SP2. And had installed:

SQLServer2005_ADOMD_x64.msi, SLQServer2005_ASOLEDB9_x64.msi,SQLServer2005_OLAPDM_x64.msi, and SQLServer2005_XMO_x64.msi.

View Replies !   View Related
Linked Server Issue: The Operation Could Not Be Performed Because The OLE DB Provider 'SQLOLEDB' Was Unable To Begin A Distribut
Hi,
I was making a linked server connection between 2 SQL Server 2000 servers (both running on Windows 2003 Server), and I was creating an insert trigger in one of the SQL Server table to insert the same value to the other SQL table. But when I try to make an insert to the table, the following error occurred :

Server: Msg 7391, Level 16, State 1, Procedure <triggername>, Line xx
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].
 
I tried to change the trigger into a SELECT statement only and the error still occurred. But when I tried to insert a row directly to the linked server via Query Analyzer, it succeeded.
I have read KB 280106, KB 306212, and KB 329332, and I've tried SET XACT_ABORT ON statement, but none of them can solve the problem.
If there's anyone who has idea on what's wrong or has a suggestion what I should try please help.
Thank you in advance.
 

View Replies !   View Related
SQLServer 2005, Linked Server, Authentication
Hi all!!!

I have the following problem:

I used JTDS to connect to SQL Server 2005 SP1(with Windows Authentication) from a Tomcat DataSource, using your usefull ntlmauth.dll, so I can use Users from Active Directory.
All is ok until I must call a Stored procedure that excecute queries from a linked server table. 
I have the following exception message "Login failed for user 'NT AUTHORITYANONYMOUS LOGON'"

I make a lot of test, I make a Java main program invoking the sotred procedure that excecute queries on linked server using JTDS and then using Microsoft SQL Server 2005 JDBC Driver 1.2(April 2007)sqljdbc.jar and Ihave the same problem, but if we use the bridge JDBC-ODBC all works OK.


Please help!!

thanks

Lore

View Replies !   View Related
Create Clustered Or Non-clustered Index On Large Table ( SQL Server 7 )
I have large table with 10million records. I would like to create clustered or non-clustered index.

What is the quick way to create? I have tried once and it took more than 10 min.

please help.

View Replies !   View Related
Restore A Database On Clustered Server From A Non-clustered Backup File.
Hello,

How do I restore a sql database that is on a clustered server from a sql database backup file that is on a non_clustered server?

Thanks,

 

Serey

View Replies !   View Related
SQLServer Invokes Trigger Even If There Is No Returned From Insert Subquery
 

Please try the following, see that trigger is getting invoked, even if there is no row inserted into trigger table.
 
drop TABLE X
GO
CREATE TABLE X (
 x1 bigint NOT NULL ,
 x2 nvarchar(40)
)
GO
drop TABLE Y
CREATE TABLE Y (
 y1 bigint NOT NULL ,
 y2 nvarchar(40)
)
GO
CREATE TRIGGER trg1
ON X
FOR INSERT AS
BEGIN
DECLARE
 @prfirststatus   INTEGER,
 @newcontextid     NCHAR
 SELECT @newcontextid = x2,
        @prfirststatus = x1
 FROM inserted
 PRINT 'See the trigger getting invoked without even a single ' +
       ' row being inserted in table X and values passed to this ' +
       ' triggers are inserted.x2 = ' + @newcontextid
 insert into Y values (@prfirststatus,@newcontextid)
END
GO
insert into X  SELECT  y1, 'x' from Y where y2 = 'DOESNTEXIST'
 

View Replies !   View Related
Cannot Fetch A Row Using A Bookmark From OLE DB Provider &&"OraOLEDB.Oracle&&" For Linked Server
I have SQL Server 2005 loaded on a 64bit Windows 2003 machine.  Additionally, I have created a linked server using the OraOLEDB.Oracle driver (10.2.0.1) to a 32bit Windows 2003 machine that hosts an Oracle 9 db.
 
I am executing a SQL Server 2005 store procedure that updates the Oracle DB.  This procedure previously worked 100% of the time on SQL Server 2000 using the Microsoft OLEDB driver for Oracle.  We were force to use the OraOLEDB.Oracle driver because SQL Server 2005 no longer supports the MSOLEDB driver for 64 bit machines.
 
Below is an example of the code I am trying to execute:
 

Update LINKED SERVER..SCHEMA.EMPCOMP

Set EMPLOY_TYPE = ED.Value_Chg, EMPLOY_TYPE_DATE = getdate()

--Select *

From LINKED_SERVER..ORACLE_SCHEMA.ORACLE_TABLE C

Inner Join SQLSERVER_REPOSITORY_DB.dbo.EMPLOYEE_TABLE E On E.Person_Id = C.Person_Id

Inner Join SQLSERVER_TABLE_CURRENT_DB ED on ED.EmpNo = E.EmpNo

Where ED.Cat_Id = 3 and

ED.HR_Approved_Fl = -1 and

ED.Proc_Fl = -99
 
The following Error occurred 100% of the time until I loaded the oracle  Patch 5043675: "Cannot fetch a row using a bookmark from OLE DB provider "OraOLEDB.Oracle" for linked server" 
 
After the patch was loaded some of my update statements suceeded and some did not.  There seemed to be no rhyme nor reason as to why they failed. 
 
Is there something else I need to do to make these updates work from SQL Server?  If not, I am considering creating procedures in Oracle to pull the data instead of pushing it from SQL Server.
 
Any help would be greatly appreciated.
 
 
 
 
 

View Replies !   View Related
OLE DB Provider &&"Microsoft.Jet.OLEDB.4.0&&" For Linked Server Error
Hello all,
 
I want to create a table in MS-Access. So I have written the following query.
 



Code Snippet
select * from Openquery(KKACC, 'select * Into Temp from Table select 1 as col1')
 
 



But I am getting the following error.
 
 



Code Snippet
 
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "KKACC" returned message "Syntax error in FROM clause.".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "select * Into Temp from Table select 1 as col1" for execution against OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "KKACC".
 
 




How can I resolve this error. Does any one have any work arround for this..?
 
Thanks
-- Krishna

View Replies !   View Related
Cannot Create An Instance Of OLE DB Provider &&"IBMDADB2&&" For Linked Server
System:  Win 2003, SQL Server 2005, Using an AD win account that is not a member of the Admin group on the server.
 
Error message from Management Studio query window:
 
Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "IBMDADB2" for linked server "Sname".
 
Event messages associated with this error:
 
App Event ID: 19036
The OLE DB initialization service failed to load. Reinstall Microsoft Data Access Components. If the problem persists, contact product support for the OLEDB provider.
 
Sys Event ID: 10016
The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID {2206CDB0-19C1-11D1-89E0-00C04FD7A829}
 to the user domainuser SID (S-1-5-21-126051702-1034962659-2130403006-7826).  This security permission can be modified using the Component Services administrative tool.
 
I€™m getting this error message when trying to run an openquery statement through a linked server to DB2. (SELECT * FROM OPENQUERY(Sname, 'SELECT * FROM tablename€™))
The linked server has a remote login and password that it uses to connect to DB2.
 
I found this from another post on how to fix this error:
Expand Component Services - Computers - My Computer - DCOM Config Select MSDAINITIALIZE Right Click properties then security
Under Security - Launch Permission: enable Local Launch and Local Activation for your SQL Service account
Under Security - Access permissions: Allow System: Local Access and Remote Access.
 
After completing these steps I still get the error message but the events are no longer generated.  I€™ve also tried different variations of these steps.  The only way I can get this to work is to either make the AD user a member of the Admin group on the server or by putting in a user account that has admin rights to the server in the MSDAINITIALIZE properties €“ Identity Tab €“ Run this App as this User.
 
Can someone please tell me the steps that I€™m missing?
 

View Replies !   View Related
Cannot Fetch A Row From OLE DB Provider &&"BULK&&" For Linked Server
I have an SSIS job that is pumping to a SQL Server Destination, hundreds of gigabytes of raw text files.  Today I received this strange error - does anyone have insight?  Also, how would I make the data tasks more stable and robust so that this doesn't cause package failure (retries, or something?)

[SQL Server Destination [4076]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80040E14  Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80040E14  Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80040E14  Description: "Reading from DTS buffer timed out.".

View Replies !   View Related
Linked Server To MYSQL Using OLEDB Provider For MYSQL Cherry
Good Morning

Has anyone successfully used cherry's oledb provider for MYSQL to create a linked server from MS SQLserver 2005 to a Linux red hat platform running MYSQL.

I can not get it to work.

I've created a UDL which tests fine. it looks like this

[oledb]

; Everything after this line is an OLE DB initstring

Provider=OleMySql.MySqlSource.1;Persist Security Info=False;User ID=testuser;

Data Source=databridge;Location="";Mode=Read;Trace="""""""""""""""""""""""""""""";

Initial Catalog=riverford_rhdx_20060822

Can any on help me convert this to corrrect syntax for sql stored procedure

sp_addlinkedserver

 

I've tried this below but it does not work I just get an error saying it can not create an instance of OleMySql.MySqlSource.

I used SQL server management studio to create the linked server then just scripted this out below.

I seem to be missing the user ID, but don't know where to put it in.

EXEC master.dbo.sp_addlinkedserver @server = N'DATABRIDGE_OLEDB', @srvproduct=N'mysql', @provider=N'OleMySql.MySqlSource', @datasrc=N'databridge', @catalog=N'riverford_rhdx_20060822'

GO

EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'collation compatible', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'data access', @optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'dist', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'pub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'rpc', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'rpc out', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'sub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'connect timeout', @optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'collation name', @optvalue=null

GO

EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'lazy schema validation', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'query timeout', @optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'use remote collation', @optvalue=N'false'

 

Many Thanks

 

David Hills

 

 

View Replies !   View Related
Trouble With: Linked Server To Oracle Using OraOLEDB.ORacle Provider
Hi--

 

I am running SQL Server 2005 on Win2k3:

Microsoft SQL Server Management Studio      9.00.2047.00
Microsoft Analysis Services Client Tools      2005.090.2047.00
Microsoft Data Access Components (MDAC)      2000.086.1830.00 (srv03_sp1_rtm.050324-1447)
Microsoft MSXML      2.6 3.0 4.0 6.0
Microsoft Internet Explorer      6.0.3790.1830
Microsoft .NET Framework      2.0.50727.42
Operating System      5.2.3790


I have the OraOLEDB.Oracle provider installed to the (C:oraclexe) directory.

I am having problems querying from linked oracle server.  When i setup oracle as a linked server and purposely enter an incorrect password the query i run tells me i have an incorrect password.   So it at least knows that.  when i set the correct password and run a query I get this error:

(i replaced the real server name with "someServer".)

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "OraOLEDB.Oracle" for linked server "SomeServer" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "SomeServer".

 

This is how I set up my Linked server:

Provider: "Oracle Provider for OLE DB"

Product Name: SomeServer

Data Source: SomeServer

Provider String:  "Provider=OraOLEDB.Oracle;Data Source=SomeServer;User Id=MyLogin;Password=MyPassword"

 

 

The query I run is:

Select * from [Someserver].[schema or database]..[tbl_name]

 

Any help???  What am i missing?

View Replies !   View Related
Default Settings SQL Server Does Not Allow Remote Connections. (provider: Named Pipes Provider, Error: 40 - Could Not Open A Connection To SQL Server
Hi I always get good reply from u all, thank you,
 I have copied my asp.net website from one server to another. they administrator made necessary modification on IIS manager . and able to see the website on browser. but now i can't loging to system using old password.
I tryed to create new password then also it gives error
System.Data.SqlClient.SqlException: 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. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
Here I have used asp.net login control and membership class. Do I need to make nay changes in code.
I have already modified server name in connection string in web config file.
any one can say what is the problem and how to solve this.
thanks
Pat
 
 

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved