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


ADVERTISEMENT

MS SQL 7.0 To Sybase Via Linked Server With OLE DB

Jul 5, 2001

We have created linked servers that connect to Sybase databases using the Sybase OLE DB provider and the Sybase ODBC provider.

The OLE DB provider does not allow the execution of a stored procedure. The message sounds like a permissions issue, but the id specified in the linked server has execute priv. for the stored procedure. It also does not allow the fully qualified name <server>.<db>.<owner>.<obj> to be used.

There are other issues with OLD DB and ODBC.

Anyone have any experiences making MS SQL to Sybase work smoothly?

View 3 Replies View Related

Linked Server To Sybase 15.5

Jun 1, 2015

Cannot create an instance of OLE DB provider "ASEOLEDB" for linked server "[SERVER_NAME]"

The current setup: Our primary database is on Sybase, I needed to extract hierarchical (nested) XML from multiple tables on Sybase. Although Sybase ASE 15.5 has `FOR XML` query capability it does now allow nested XML format. So we used the 'FOR XML', XML functionality in SQL Server (as we had a SQL Server 2008 database available which is used for a smaller system) to query Sybase tables through a Linked Server connection.Our sample nested XML Format is similar to:

<personCol>
<person>
<id>1111</id>
<name>John</name>
<age>21</age>
<sex>M</sex>

[code]....

View 2 Replies View Related

Creating Linked Server To Sybase IQ

Nov 10, 2006

I would like to create a linked server from SQL Server to Sybase IQ. I have created linked servers before so I know how to do that. However, I dont know the specifics of creating a linked server to Sybase IQ.

What are the parameters that are necessary to link IQ to SQL Server. Which Provider do I use? Do I need to install a special driver?

View 2 Replies View Related

Linked Server To Sybase 11 Problem

Jun 2, 2005

Attempting to create a Linked Server to a Sybase 11 db. Did the following

View 4 Replies View Related

Create Linked Server To SYBASE ASA 9.0

Apr 2, 2008


Hi all,

I´m trying to create a linked server to a Sybase database (.db file) so i can create some reports in sql 2005 (with Reporting Services and Report Designer). After reading all articles I did the following.

1) Installe Adaptive Server Anyhwere (Interactive Sql) 9.0.2 on the server.
2) Created a USER DSN to the Db file (Control Panel - odbc connections) and it works fine: The Settings for the ODBC are:


ODBC Tab


Data Source Name: dbNOM

Login Tab


Supply userID and Password: (selected)

User ID: DBA

Password: SQL

Database Tab


Server Name: dbNOM

StartLine: C:Program FilesSybaseSQL Anywhere 9win32dbeng9.exe

Database Name: (blank)

Database File: c:Project FolderDataBase.db
As I said, when i go for "test connection", works fine.

3) Sql Management Studio - Object Explorer - Server Objects - New Linked Server, with settings as following:


General


Linked Server: lkDbNom

Server Type: Other Data Source

Provider: Sybase Adaptive Server Anywhere Provider 9.0

Product Name: Sybase

Product Name: dbNom (the dsn name, right?)

Security


Be made using this security context.- Remote Login: DBA; Password: SQL (same as DSN)
All other settings, as default, click in OK and shows no errors (aparently it creates the linked server successfully). But when i try to query the linked server with:


SELECT * FROM OPENQUERY ('SYBASE', 'SELECT * FROM nom_Robot')

And i get the following message:


Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'SYBASE'.

Even better, if i go to the Object Explorer - Server Objects, i test the connection and it´s ok, but when i try to retrieve the catalog an error displays:

TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The OLE DB provider "ASAProv.90" for linked server "SYBASE" reported an error. Access denied.
Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB provider "ASAProv.90" for linked server "SYBASE". (Microsoft SQL Server, Error: 7399)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3054&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476


So, i really don´t understand what is missing in my linked server, i tried with MDASQL (OLEDB for ODBC) but it doesn´t even completes to create the linked server.

The database file is ok because, because in another server (one that doesn´t have SQL, only visual studio) i did the following:

1) Installe Adaptive Server Anyhwere (Interactive Sql) 9.0.2 on the server.
2) Created a DSN to the Db file, and it works fine. (copied the db file, so is local, no remote access), exactly the same setting as i did on the sql 2005 server.
3) Created a connection in visual studio 2008 (server explorer - data connections), i can retrieve sdata...but of course the provider is .NET Framework Data Provider for ODBC and i cannot use the same connection string.

Any ideas?

P.S.

View 7 Replies View Related

Linked Server - Sybase - Error 7399

Jun 26, 2000

I am having trouble linking to a Sybase server using the ODBC OLEDB provider, with either the sp_addlinkedserver stored proc, or the "linked servers" wizard in Enterprise manager.

The SQL I am executing is:

/* add server */
exec sp_addlinkedserver
@Server = 'Linked Server Name',
@srvproduct = 'Sybase System 11',
@Provider = 'MSDASQL',
@provstr = 'Driver={INTERSOLV 3.11 32-BIT Sybase};SRVR=servername;DB=targetdatabase;UID=bob; PWD=bob;'

/* create security mapping */
exec sp_addlinkedsrvlogin
@rmtsrvname = 'Linked Server Name',
@useself = 'false',
@rmtuser = 'bob',
@rmtpassword = 'bob'


(Note that this does not use a DSN - I have tried it both ways, but with no joy).

The SQL executes without any errors or warnings. However, when I try to use the linked server connection (eg., visually list the tables available by double clicking the linked server in the Enterprise Manager, or Select * From a table in the Query Analyser) it give the following error:

"Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. The provider did not give any information about the error."
(That is - generic error)


It just seems that I am not putting the connect string components in the right places.

Does anyone have any suggestions on how to set up a linked server to Sybase in this this way?

Many TIA

View 1 Replies View Related

Inconsistent Result Of Sybase Linked Server

May 4, 2008

help...


i successfully linked the sybase database to sqlserver. at first i can select a record, after i execute the same statement the result become different. i was looking for answer in the web but i can't find one. can somebody help me...

i suspect that sqlserver cache the result.. so i try to create a view for sybase database table. when i select the view it gave me the complete result but when i rerun the statement again it became inconsistent.

is there a cache or something in sqlserver. if there is how can i clear it.. so i will always get the fresh copy of the result.

View 6 Replies View Related

Update In Sybase 11 Linked Server Problem

Jul 27, 2005

When trying to update records in a Sybase 11 ODBC linked server got this error:

View 5 Replies View Related

Urgent SQL Sybase Linked Server Insert Problem

Nov 30, 2007

I am getting error when I try Inserting data in sybase 12.5 using linked server from SQL2K5

I am able to select

Following is the code i am using.error is same for both stmts
insert into l_syb_ibt.ibtqa.dbo.rajtest (id)values (1)
insert openquery(l_syb_ibt, 'select id from rajtest where 1=0') values (1000)

please help.thanks in advance

following is the error
OLE DB provider "MSDASQL" for linked server "l_syb_ibt" returned message "Transaction cannot have multiple recordsets with this cursor type. Change the cursor type, commit the transaction, or close one of the recordsets.".
Msg 7343, Level 16, State 2, Line 1
The OLE DB provider "MSDASQL" for linked server "l_syb_ibt" could not INSERT INTO table "[l_syb_ibt].[ibtqa].[dbo].[rajtest]".

View 5 Replies View Related

Issue With Sql Server To Sybase Linked Server Using Stored Procedure

Sep 27, 2007

I have a linked server from Sql Server 2000 to Sybase Adaptive Server 12.5.1.

When i try to call a stored procedure on Sybase from Sqlserver i get the following message:

"could not execute procedure sp_who on remote server 'linked server name'(42000,7212)

command executed from sql server:
exec <linked_server>.<database>..sp_who


i am able to user open openquery for selects and inserts, successfully

Help appreciated

Thanks.

View 4 Replies View Related

Instructions For Setting Up A Sybase ASE Server As A SQL Server 2005 Linked Server?

Dec 28, 2005

I have a Sybase Adaptive Server Enterprise server which I need to set up as a linked server in SQL Server 2005.  The Sybase server is version 12.5.2, and the Sybase ODBC driver version is 4.20.00.67.  I have already installed the Sybase client software on the server.


I also created a SystemDSN on the SQL Server to connect to the Sybase server.  I tested the connection and it was able to connect.

I ran the following code to create the linked server:

<code>

EXEC master.dbo.sp_addlinkedserver @server = N'LinkedServerName', @srvproduct=N'Sybase', @provider=N'MSDASQL', @datasrc=N'Sybase System DSN', @provstr=N'"Provider=Sybase.ASEOLEDBProvider;Server Name=servername,5000;Initial Catalog=databasename;User Id=username;Password=password"'

</code>

I then ran sp_tables_ex to make sure I could view the tables in the Sybase database.  Here is the error message I get:

<code>

OLE DB provider "MSDASQL" for linked server "LinkedServerName" returned message "[DataDirect][ODBC Sybase Wire Protocol driver]Error parsing connect string at offset 13. ".

Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "LinkedServerName".

</code>

Any ideas what is happening here?

View 10 Replies View Related

Linked Server From SQL Server 2000 To Sybase 12

Apr 26, 2004

:confused:
How do I tap advanced functionality like #temptable and @var from openquery? There is a linked server from SQL Server 2000 to Sybase 12 using Sybase.ASEOLEDBProvider.
Executing an openquery statement from MS SQL will retrieve Sybase data; however, I have no advanced functionality available. When I try to use temporary #tables or declare @vars it returns this error...
Server: Msg 7357, Level 16, State 2, Line 1, Could not process object '…OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='Sybase.ASEOLEDBProvider', Query=…

Linked Server configuration:
Provider options: AllowInProcess is checked
Server options: Collation Compatible, Data Access, RPC and RPC Out are checked

Does anyone have a suggestion or alternative for this functionality? Thank you very much for your input.

View 6 Replies View Related

Linked Server From SQL Server 2005 (x64) To Sybase

May 24, 2006

Hi,

We have been accessing databases on ASE 12.5 (on HPUX) from MS SQL Server 2000 (x32) successfully for years. We do this via linked servers we create on MS SQL Server. When creating the linked server, we would specify the Microsoft OLE Provider for ODBC as the provider, and the data source is the name of a DSN created with the Sybase ASE ODBC driver (4.20.00.67) that points the the HPUX box on which are the Sybase databases.

We have also gotten this to work just fine on MS SQL Server 2005 (x32) Standard Edition SP1. However, we cannot seem to get this to work on MS SQL Server 2005 (x64) Enterprise Edition SP1. The Microsoft OLE DB Provider is not available as an option when creating the linked server on MS SQL Server 2005 (x64). We have tried specifying the SQL Native Client as the provider, but this results in no connection being made and reports the following error:

OLE DB provider "SQLNCLI" for linked server "GCMTESTSQLNATIVE" returned message "Login timeout expired".

OLE DB provider "SQLNCLI" for linked server "GCMTESTSQLNATIVE" 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 53, Level 16, State 1, Line 0

Named Pipes Provider: Could not open a connection to SQL Server [53].

Do we need to use a different ASE driver when accessing Sybase databases fom the MS SQL Server (x64) platform? Or is there a way to successfully create a linked server on MS SQL Server 2005 (x64) that can communicate with the 32-bit ASE ODBC driver?

Thanks,

Rocco M.

View 7 Replies View Related

SSIS Project Failing To Run Even Though I Verified Remote Sybase Connection Via Connection Manager

Apr 7, 2008



When running the project in debug mode or non-debug mode, I get the following error from MS Visual Studio:

TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Package [Connection manager "SYBASE_CONNECTION"]: The connection manager failed to defect from the transaction.
(Microsoft.DataTransformationServices.VsIntegration)
------------------------------
BUTTONS:
OK
------------------------------

Any ideas???

View 8 Replies View Related

Problem With Sybase Linked Servers

Jan 25, 2008

I have two linked servers configured on my server(SQLServer 2000, Win2003). Both are configured to use Sybase ASE OLE DB driver, version 12.5. I have two differen OLE DB Obects configured in Sybase Configuration Manager. One object is configured to point to a Sybase 12.5 server in Boston, the other is configured for a server(same version) in Westboro. I can test connect from the SQLServer machine on each of these, and see that I am connected to each of the Sybase servers. However, my linked servers are only able to connect to the first OLE DB data source listed in the Sybase Configuration Manager. The linked servers are configured to use different OLE DB data sources, and no errors are thrown, but I can see that both actually go to the same data source. If I rename/re-order the OLE DB Data Sources, and make the second one the first one, I can connect to the other server.
Is this a known issue?
We are currently switching over to Sybase ODBC driver(not sure why we used the OLE DB driver in the first place), via which we can connect to both Sybase servers.

View 5 Replies View Related

Performance Problem With Linked Sybase Tables

Jul 20, 2005

We have a need to retrieve Sybase data within a MS SQL Serverapplication. We are using SQL Server's linked database feature withthe Sybase 12.0 OLE DB driver. It takes 5 minutes to run a query thattakes 2 seconds from isql.Any suggestions?Thanks

View 1 Replies View Related

Sybase And SQLServer2005 Connection Problem

Aug 6, 2007

Dear all,

Installed sybase adaptive server Enterprize version 12.5.1 on Win2k3.

Database is working fine and started on Port No 2638.

While connecting to Sybase from Diver Sybase ASE ODBC Driver i am not able to connect to the database and getting error "connection time out" and "Verify Host Name and Port Number".

When i create a Connection from Integration Services 2005 i can connect to the Sybase Database and retive the table name but when i try to preview the table information i get the following error.

"Unable to retrieve column information from the DataSource".

Do i need to any other ODBC Driver to connect to sybase database.


regards,

sufian

View 2 Replies View Related

How To Create A Connection Into Sybase Database

Sep 12, 2007

Hi, i already create a connection to sybase(ASA) with odbc from ssis (from "data sources" section). but when i try to make data flow source it cann't. so i try to make a connection using "data source views" but when the process comes to end, it becomes error. from my analysis, the Sybase db cannot accept sql command with "[]", because in the fase "data source views" i have to select one or more table, and the default microsoft is using "[]", like: select * from [dbo].[customer], but sybase db cannot handle this. does anyone have the solution for this? Thanks.

Sincerely

Yugicm

View 1 Replies View Related

Sybase Connection In Xml Config File - Help

Jan 31, 2007

Hi all

I'm trying to connect to a Sybase DB using an OLE DB connection. Now, it works fine when I run the project without the package config enabled, but when I add the Sybase connection to the xml config file, & enable it the connection fails.

This is the error I see in the progress Tab

[Connection manager "AcidPrd.basketUser"] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "ASE OLE DB Provider" Hresult: 0x80004005 Description: "Login Failed. Check for valid user ID, server name and password.". An OLE DB record is available. Source: "ASE OLE DB Provider" Hresult: 0x80004005 Description: "[Native Error code: 4002] [DataDirect ADO Sybase Provider] Login failed. ".

Can anyone help.

Thanks,

Jon

View 6 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

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

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

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 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

Sybase 15.xxx Version (linking Sybase To Sqlserver 2005)

May 18, 2008

hi,

i tried the document below pertaining to linking sybase 12.5 to sqlserver 2000 and sqlserver 2005

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=311875&SiteID=17
http://www.sybase.com/content/1029118/1029118.doc

but when our company upgrade the sybase to 15.x i can't find the oledb configuration stated on the document.



so i can't linked the sybase 15.x now to sqlserver 2005. can somebody help me.!!!please...

where i can find the oledb configuration in sybase 15.xx..

tnx..

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

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







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