Linked Server To Pervasive 8 - Connection Error

Aug 7, 2006

Hello,

I am currently migrating from SQL 7.0 to SQL2005. So far everything is going well except for one thing, my linked server to a Pervasive 8.6 server database. I've done the same configurations as with the SQL 7.0 server to create the linked server. I've created an Pervasive ODBC Client DSN system connection on the server called PervasiveDB pointing to the DataDB catalog, using TCP/IP as transport and the server name "PervasiveDB" as the server address. From this point, on the SQL2005 server, the DSN connection is successfull and I can see the different catalog names of the PervasiveDB server. When I create the Linked server on the SQL2005, I create exactly as done on the SQL7.0 which is :

SQL7.0 server : OS is Windows 2000 all service packs applied for both OS and SQL7.0

SQL2005 server : OS is Windows2003 standard edition all service packs applied for both OS and SQL2005.

Linked server name : TestPervasive

Provider : Microsoft OLE DB Provider for ODBC Drivers

Product name: Pervasive

Data source: PervasiveDB

Location: PervasiveDB

Catalog : DataDB

I get the following error when I try to query the data to test if the linked server I just created is successful:

query : SELECT * FROM testpervasive.DataDB..schedule

error message:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "MSDASQL" for linked server "TestPervasive" 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 "MSDASQL" for linked server "TestPervasive".

Anybody can help me on this one? I would greatly appreciate it !!

View 3 Replies


ADVERTISEMENT

Error Expanding Tables In Linked Server To Pervasive

Feb 1, 2005

Pleeeease help? I get an error when expanding the Tables object under linked servers in Enterprise Manager on a MSSQL2000 server.

I have a standard install of the Pervasive.SQL V8 data engine and DemoData db on my local PC. The SQL Server is also on my local PC.

I manage to create the Linked Server using the MSSQL Enterprise Manager. I used the following configuration:

Provider Name: Pervasive.SQL V8 OLE DB Provider
Product Name: Pervasive.SQL V8
Data source: DemoData
Provider string: <blank>
Location: workstation03
Catalog: <blank>

However when I try to expand the tables object under Linked Servers in the Enterprise manager I get the following error.

Error 7311: Could not obtain the schema rowset for the OLE DB provider 'UNKNOWN'. The provider supports the interface, but retuns a failure code when it is used. OLE DB error trace [OLE/DB Provider 'UNKNOWN' IDBSchemaRowset::GetRowset returned 0x80040155 ].

How would I fix this?

Thank you.

View 1 Replies View Related

OLE DB Connection Error; Provider Becomes Unavailable To Linked Server

Apr 24, 2008

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

Linked Server W/ Text Field Gets Connection Broken Error

Sep 5, 2006

We have a stored proc on Server B called:

my_sp_server_b it takes 1 parameter a text field as a parameter, with default set to NULL

this proc calls:

my_sp_server_a through a linked server (which happens to be the same server, different DB), it has two parameters: my_id int, my_text text w/ my_text having a default set to NULL

This second stored procedure just selects back an ID that is passed to it (to keep things simple).

If we pass any string value to my_sp_server_b we get the appropriate hardcoded ID passed to my_sp_server_a. If we pass NULL to my_sp_server_b we get the following error:

[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.

Connection Broken

If we remove the linked server, and just reference my_sp_server_a via the scoped DB, we do not get an error. If we change the data type in both procs to varchar(50) we do not get an error. If we change the data type to nText we still get an error. If we put IF logic into stored procedure: my_sp_server_b to check for NULL in the input parameter and if it true then to pass NULL explicitly to my_sp_server_a we do not get an error.

It seems to be a combination of using a linked server and trying to pass a text (or nText variable) with a NULL value to stored procedure. Sometimes the error changes based on which scenario I described above - but we consistantly receive an error unless we do some of the workarounds described above.

Any ideas?

View 2 Replies View Related

Oracle Linked Server Connection Error After Upgrading To SQL 2005

Mar 14, 2007

Hello all.

I upgraded a SQL 2000 installation to SQL 2005 this past weekend. Minimal issues overall, but one that did crop up relates to a linked server to an Oracle database. The linked server worked before the upgrade, and stopped working immediately after.

We're running on Windows 2003 x64, latest service pack. The linked server is set up using the MSDAORA provider. The error I'm getting post-upgrade is:

=====================

OLE DB provider "MSDAORA" for linked server "PROD" returned message "ORA-06413: Connection not open.".

Msg 7303, Level 16, State 1, Procedure RDM_GET_REP_LIST_SP, Line 12

Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "PROD".

=====================

A tnsping from the server works fine. I've also tried recreating the linked server. Any help would be appreciated. Let me know what other information would be useful.

Thanks,

Adam

View 3 Replies View Related

Named Pipe Provider/Linked Server Connection Error

Aug 21, 2007

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

SSIS Errors During Connection To Pervasive V9.11

May 23, 2007

We just upgraded our accounting packages database from Pervasive 8.1 to V9.11. MY SSIS extract packages were working just fine. Now I am getting several erros stating that some columns cannot be found:



For example, in the [sysdtslog90] table, I have these errors:



Column "CreditLimitAmt" cannot be found at the datasource. or

Column "RsrvRptky" cannot be found at the datasource. (from a different pkg)



The table schemas did not change, the columns still exist. The SQL I use also runs just fine in the client interface.



Has anyone experienced this? The bottom line is that now the extract packages are broken.



TIA.

View 7 Replies View Related

Help. When Using Profile GetPropertyValue, I Get The Following Connection Error: 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 Sett

May 3, 2008

 Hi,I need some help. I am getting this error after I complete the asp.net register control and click on the continue button. It crashed when it tries to get it calls this Profile property((string)(this.GetPropertyValue("Address1")));When I look at the stack, it is coming from my ProfileWrapper class which adds user address, city, etc.. from a class which inherits fromSystem.Web.Profile.ProfileBase. From the stack, it is calling the System.Web.Profile and crashed when it tries to open a connection atSystem.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject). I already migrated all aspnet_user, aspnet_roles, etc.. and don't get any connection errors. It is only when I try to get the profile data. This works on  my pc, but throws an error on my lunarpage website.Any help is greatly appreciated.Thanks,AJAn 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.Exception Details: 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)Source Error:Line 100:    public virtual string Address2 {Line 101:        get {Line 102:            return ((string)(this.GetPropertyValue("Address2")));Line 103:        }Line 104:        set {Source File: c:windowsMicrosoft.NETFrameworkv2.0.50727Temporary ASP.NET Files
oot021d50639a6858cApp_Code.54nvluyo.1.cs    Line: 102Stack Trace:[SqlException (0x80131904): 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)]   System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +435   System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82   System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105   System.Data.SqlClient.SqlConnection.Open() +111   System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) +84   System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +197   System.Web.Profile.SqlProfileProvider.GetPropertyValuesFromDatabase(String userName, SettingsPropertyValueCollection svc) +782   System.Web.Profile.SqlProfileProvider.GetPropertyValues(SettingsContext sc, SettingsPropertyCollection properties) +428   System.Configuration.SettingsBase.GetPropertiesFromProvider(SettingsProvider provider) +404   System.Configuration.SettingsBase.GetPropertyValueByName(String propertyName) +117   System.Configuration.SettingsBase.get_Item(String propertyName) +89   System.Web.Profile.ProfileBase.GetInternal(String propertyName) +36   System.Web.Profile.ProfileBase.get_Item(String propertyName) +68   System.Web.Profile.ProfileBase.GetPropertyValue(String propertyName) +4   ProfileCommon.get_Address2() in c:windowsMicrosoft.NETFrameworkv2.0.50727Temporary ASP.NET Files
oot021d50639a6858cApp_Code.54nvluyo.1.cs:102   ProfileWrapper..ctor() in d:inetpubvhostsjavcentral.comhttpdocsApp_CodeProfileWrapper.cs:242   ProfileDataSource.GetData() in d:inetpubvhostsjavcentral.comhttpdocsApp_CodeProfileDataSource.cs:17[TargetInvocationException: Exception has been thrown by the target of an invocation.]   System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) +0   System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) +72   System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) +308   System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) +29   System.Web.UI.WebControls.ObjectDataSourceView.InvokeMethod(ObjectDataSourceMethod method, Boolean disposeInstance, Object& instance) +480   System.Web.UI.WebControls.ObjectDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1960   System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17   System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149   System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70   System.Web.UI.WebControls.FormView.DataBind() +4   System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82   System.Web.UI.WebControls.FormView.EnsureDataBound() +163   System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69   System.Web.UI.Control.EnsureChildControls() +87   System.Web.UI.Control.FindControl(String id, Int32 pathOffset) +21   System.Web.UI.Control.FindControl(String id) +9   CustomerDetailsEdit.OnPreRender(EventArgs e) in d:inetpubvhostsjavcentral.comhttpdocsUserControlsCustomerDetailsEdit.ascx.cs:60   System.Web.UI.Control.PreRenderRecursiveInternal() +86   System.Web.UI.Control.PreRenderRecursiveInternal() +170   System.Web.UI.Control.PreRenderRecursiveInternal() +170   System.Web.UI.Control.PreRenderRecursiveInternal() +170   System.Web.UI.Control.PreRenderRecursiveInternal() +170   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2041 

View 1 Replies View Related

Pervasive SQL V8 Vs SQL Server 2000

Dec 11, 2006

My company is trying to determine if they need to upgrade from our current Platinum accounting application that uses pervasive SQL V8 to another version of Platinum that uses SQL server 2000.

My manager wants a in-depth analysis comparing pervasive SQL V8 to SQL server 2000.

I have searched the web extensively but information is scarce.

Are there any articles or case studies that do an indepth analysis comparing both databases? Can someone please help me. It would be greatly appreciated.

View 2 Replies View Related

Link Pervasive SQL Server - How ?

Jul 20, 2005

Hiis anybody know how to lin Pervasiva/bitrive SQL server on MS SQL server ?ThanksMarcin Sobolewski

View 1 Replies View Related

SQL Server Versus Pervasive SQL/2000

May 25, 2007

Hi there,



Don't know if anyone can help,



There used to be a Whitepaper on the Microsoft website comparing SQL server (2000?) against Pervasive SQL or 2000, the link was http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/.

But it's not working anymore.



Does anyone have a copy of this paper, or know where it's now located? I've been looking for hours and can't find it.



Thanks in advance.



Jezza

View 3 Replies View Related

How To Set Linked Server Connection To Db2?

Oct 1, 2007

What is the correct way to set a linked server pointing to a DB2 database?

I've been trying for hours with "Microsoft OLE DB Provider for ODBC Drivers" [MSDASQL] and "IBM OLE DB Provider for DB2" [DB2OLEDB] and I still cant setup a correct connection.

I already have a ODBC connection pointing succesfully to my DB2 database, but I can't make MSSQL to use it.

Should I created the connection using sp_addlinkedserver OR the "new Linked Server" under "linked Servers" on Management Studio?

Any help is welcome.

[]s

View 2 Replies View Related

How To Import Pervasive Database Into SQL Server 2005

Jun 9, 2006



Hi

I have tried to import Pervasive Database to SQL Server 2005.

I am not getting any option in Data Source for Pervasive So I tried following

I have Provided the following Connection String to SQL server Import and Export Wizard (in .net framework data provider for ODBC)

Connectio String: Dsn=DEMODATA;Driver={Pervasive ODBC Engine Interface}

DSN: DEMODATA

Driver:Pervasive ODBC Engine Interface

but i m getting error that "Can not get supported data types from th database connection."

Can you please look in to it and tell me possible ways by which I can import pervasive Database into SQL server 2005.

and let me know if there is any other tool by which we can see "Pervasive ODBC Engine Interface"

in Data Source

Thanks,

Arun









View 18 Replies View Related

Testing Linked Server Connection

Jan 16, 2001

I have a server with several linked servers. Before I execute sql against any of these linked servers I want to check to make sure the connection is active.

I created a small stored procedure that takes the server name. I am trying to get it to run a simple select statement. If the select statement runs without errors (meaning the linked server is active) I want to return a value for success. If the select statement fails I want to return a value for a failure.

The problem is that I am having trouble with the error. When I shut down the linked server and run the select statement I get the following error returned:

Server: Msg 11, Level 16, State 1, Line 1
General network error. Check your network documentation.

The stored procedure I have only returns this message and does not send the return value that I set. How do I get my procedure to return a failure value instead of the following error above? Is there a better way for me to check for this type of error?

Thanks

View 1 Replies View Related

Linked Server Connection To Sybase

Mar 11, 2004

Has anyone had problems using an OLEDB linked server connection to Sybase ASE 12.5? I'm having major performance problems when I use string criteria in the where clause. Queries that return 1 row and execute in less than a second using a native Sybase connection take 40 seconds to run using the linked server OLEDB connection. If I use ints in the where clause performance is almost exactly the same between native and linked connections.

Any ideas???

View 9 Replies View Related

SSMS Linked Server Connection For DB2

Mar 19, 2008

I am trying to establish a OLEDB connection for a db2 server from SSMS adn some one tell me the settings. I haver tried different options but failed.
This is my connection string from SSIS
"Data Source=DB2W;User ID=myid;Provider=IBMDADB2.1;Persist Security Info=True;"

I gave
Linked Server: DB2W
Provider: IBM OLEDB provider for DB2
Product name: ???
DataSource: ???
Provider string:???
Location: ???

View 3 Replies View Related

Is It Possible To Open A Connection To The Linked Server In C#

Jan 17, 2008



Hi Gurus,

In my code, I need to update the table on the linked server according to the XML input. A table variable is used to store the value from XML.

But the collation of the linked database doesn't match with the default collation used by the table variable, so the table join fails.

I am thinking about to open the connection to the linked server directly, and use the target database. So the table variable collation should be as same as the target database collation.

Is it possible to open the connection to the linked server in c# ?


Thanks and best regards,

Jennifer Zhao

View 5 Replies View Related

Linked Server Connection String ??

Apr 22, 2008



What is the connection string for access a database of a linked server in sqlserver

Thanks

I add a linked server (another sqlserver server2) to my sqlserver(server1) , I need make a sqlconnection to the linked server connecting my sqlserver (server1)

View 7 Replies View Related

Establishing An Access Linked Server Connection On SS7

May 11, 2000

Hi:

No matter how I configure my link going thru EM I get a 7399 or 7303 error. First I tried to link to an Access 2000 db on a Windows 98 station. Next I installed Access 2000 on the test server with SS7, SP 2 (Windows NT 4, SP 5) and still the same errors. Is it possible to set up Access 2000 as a linked server on SS7? Can you give me the brainless man's walk-thru so I can get over this hump?

Thanks in advance,
Gary

View 3 Replies View Related

SQL 2005 Linked Server Connection Issues

Sep 24, 2007

Hi,


I am trying to connect to a sql 2005 SP2 windows auth server through linked server from a sql 2005 SP2 mixed mode. I am using the "use current users credentials" option for authentication. I am getting the error below. Any thoughts to why this is happening would be appreciated.

TITLE: Microsoft SQL Server Management Studio
------------------------------
"The linked server has been updated but failed a connection test. Do you want to edit the linked server properties?"
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Login failed for user 'NT AUTHORITYANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456)

View 4 Replies View Related

Test Linked Server Connection SQL 2000

Feb 27, 2007

I€™m trying to resolve an issue and wonder if anybody has ever run into and possibly had a resolution for.

I wrote a custom conflict resolvers to manage data at the column level. What I needed to do to achieve this was to use a remote query to the mobile subscriber that raised the conflict. I€™m trying to put in a rule that if there are any issues connecting to the subscriber I want to use the default conflict resolver and move forward. Here is my issue, it seems there is no way I can test a connection without getting a critical error that I can€™t seem to trap. Example

I was trying to do

Select * from [linkedserver].[database].[dbo].[incident]
If @@error <> 0
use default conflict resolver

What I did to test this was I changed the linked server authentication to an incorrect password to get the following error that there is no way to trap.

Server: Msg 18456, Level 14, State 1, Line 1
Login failed for user 'sa'.

Does anybody have an idea how to trap this? In SQL 2005 there is a stored procedure

sp_testlinkedserver to handle this but I need this to work in SQL 2000 also, it seems many people have ran into this issue on the web and I tried to get the one where sqldmo is used to test connection but that will not work since I wont always know the password. I would like to just check the @@error value after trying to run a remote query.

Thank you,
Pauly C

View 3 Replies View Related

Connect From A SQL Server 2005 Db To A SQL Server 2000 Db, Without Linked Server Connection

Apr 18, 2007

Can I connect from a SQL Server 2005 database to a SQL Server 2000 database, without establishing a linked server connection.



I need to fire a SELECT query on a SQL Server 2000 database, but don't want to add it as a linked server. Is there any way I can do this or its not possible??

View 1 Replies View Related

Slow Connection. ASP Page Querying A Linked Server On MSSQL 2005

Dec 28, 2006

Creating a web application.  Running a simple query "SELECT username FROM vwCustomer"

vwCustomer is a view built on top of an Access DB which is set up as a linked server.  Within SQL Server that view responds immediately.  But when I try to access it from an ASP page it takes over 20 seconds to respond.

Used the following to create the linked server:

EXEC sp_addlinkedserver 'DBName', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', 'c:databasesdatabase.mdb'

GO

EXEC sp_addlinkedsrvlogin 'DBName', FALSE, NULL, 'Admin', ''

GO

The view is simply(this works fine in SQL Server):

SELECT * FROM DBName.db.dbo.customer

The ASP page:

SELECT * FROM vwCustomer

Can anyone give me a hint as to where the bottleneck is?  Or how I can figure that out using SQL Profiler or something?

Please help!

View 3 Replies View Related

Linked Server Authentication Error - [SQLSTATE 42000] (Error 7303)

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

Linked Servers Error Microsoft SQL Server, Error: 7411

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

Integration Services :: Need To Create SSIS CONNECTION Manager Specifically For Linked Server?

Oct 1, 2015

I have a linked server setup in dev environment which is pointing to ORACLE server of a third-party. I m on  sql server 2008 r2 and   using BIDS 2008 for building an SSIS package which would pull data from oracle and do a look up in SQL server table to get a final  extract. Now the query which pulls data from a single table from linked server is like the one below

select * from [oracle linked server ]..dbo.tablename a
left join  mySQLSERVERtable b
on a.id=b.id 

Note:I can execute this query on SSMS as  the linked server is setup -[oracle linked server ] and I have sql  oledb connection manager for dev environment already configured in my ssis package .

My questions are do I need to create a SSIS CONNECTION manager specifically for linked server in my ssis package ?

Are there any best practices which we follow when using linked servers in this context ? I am thinking will the join suffer performance issue and what kind of exceptions I need to write when the linked server connection is broken.

View 4 Replies View Related

Error While Establishing A Connection To The SQL Server 2005 - Could Not Open A Connection To SQL Server

Apr 12, 2007

Hi, I had an old web application created during asp.net 1.1 and it have a connection problem with the sql server 2005 when it is mirgrated to a new webserver with dotnet framework 2.0 platform. I have enabled the remote access(TCP/IP and named pipes) in sql server 2005, did all the neccessary things, check whether the TCP/IP is enabled, named pipe is enabled...  I created another web application using VS 2005. The database connection works perfectly well.This are the connectionString from the old web application.<appSettings>    <add key="ConnectionString" value="Server=127.0.0.1;Database=somedb;User id=user; Password=somepassword; Trusted_Connection=False; POOLING=FALSE"/></appSettings>  Thankyou in advance! 

View 4 Replies View Related

Rounding Error: Between Flat File Connection Manager Source &&amp; OLE DB Connection Destination (SQL Server 2005)

Jun 22, 2006

I have a Rounding error: Between flat file connection manager Source & OLE DB Connection Destination (SQL Server 2005) in my Dataflow.

File looks like this lets call column names Col A,B,C,D

70410000 RD1 1223631.92 196042.42
70329000 ICD 11025.84 3353.88
71167300 COL 104270.59 24676.96

flat file connection manager settings: first row Column names then Advanced tab Col A float , Col B float , Col C string ,Col D float ,

OLE DB Connection Destination (SQL Server 2005)

CREATE TABLE [dbo].[PT_CUST_ABR](

[PARTY_NO] [float] NULL,

[PARTY_NAME] [varchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[TELECOMABR] [float] NULL,

[GENIABR] [float] NULL,



Problem: ColA (Source) Rounding error to PARTY_NO (Destination)
I have a field of text of in a flat file that the flat file connection manager Source picks up correctly €œ70000893€?
However when it gets the OLE DB Connection Destination the data has changed to 70000896. That€™s before its even Written to the database.
The only clue that something is wrong in the middle is the great Data viewer shows the number as 7.000009E+07
Other clues looking at the data it appears there is a rounding error on only the number that dont end in 00
ColA (Source) PARTY_NO (Destination)
71167300 71167296
70329000 70329000
70410000 70410000
Any ideas people?
Thanks in advance
Dave



View 3 Replies View Related

Linked Server Error

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

Error With Linked Server

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

Linked Server Error !!!

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

Linked Server Error

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

Error With Linked Server

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







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