servers

I have a linked server connection on a SQL 2k server connected to a Sybase ASE 12.5 server via Sybase OLE DB connection. To this point everything has been working great for months. Now we're encountering a problem where new tables created on the Sybase db won't show up in the linked server connection. I've checked permissions and the object owners and everything is exactly the same as the pre-existing tables except the table's new.

Anyone have any ideas wht's up?Describe "won't show up" in a bit more detail. What exactly happens when you try to issue a SELECT statement against a new table?

-PatP|||When I say "won't show up" I mean you can't see the table in the table list in enterprise manager when I open the linked server. Here's the SQL and error message:

select i_con_contract from openquery([32tlsql2-dreamdb],
'Select i_con_contract From dbo.temp_client_3' )

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Sybase.ASEOLEDBProvider' reported an error.
[OLE/DB provider returned message: [Native Error code: 208]
[DataDirect ADO Sybase Provider] dbo.temp_client_3 not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).
]
OLE DB error trace [OLE/DB Provider 'Sybase.ASEOLEDBProvider' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].|||What happens in Query Analyzer?

I suspect that this is another example of EM (SQL Enterprise Mangler) caching information, but never refreshing the cache.

-PatP|||The SQL and error message posted are from Query Analyzer.

I've even created a new linked server connection and the tables still won't show up. So I don't think it's a caching issue in EM.

Any other ideas? I appreciate the help.|||I was a bit corn-fused when you were talking about EM and posting SQL in the same message. I figured that somehow I must have "missed a meeting" in there somewhere.

Is there any chance that the Sybase objects are owned by a non-dbo user? Does the user being used by OPENQUERY have access to those objects when you use Sybase tools (like ISQL) to try to access them?

-PatP|||Nope, they're owned by dbo and every user and group in the Sybase db have select, insert, update, and delete permissions on the tables.|||Well, at least for now I'm stumped. I'm sure that come 03:30 I'll have a bright idea, but right now I'm fresh out. Sorry.

-PatP|||Originally posted by peterlemonjello
Nope, they're owned by dbo and every user and group in the Sybase db have select, insert, update, and delete permissions on the tables.

What's the linked server login?

Did you grant right to it on the new tables?|||Yep, the login the linked server is using has explicit permissions set on every table in the db. I've even changed to login to sa and still can't see the tables.|||I'm stuck...did you stop and restart EM?

Can you query them in QA?

Hey Pat, it's past 3:30...EST

Is there a way to see the linked server catalogs?|||Yep, I've rebooted the SQL Server. I can see the catalog using the sp_tables_ex proc and by looking in the sysremote_tables table in the master db. They're missing the new tables too. I have no freakin idea what's going on. I've turned the trace on in the OLEDB properties to try and isolate how SQL Server gets the table list from Sybase. Unfortuantely, when I refresh the tables in EM nothing appears in the OLEDB trace output.|||OK, let's get stupid (since I'm already there)

Can you create a new linked server with the same code?

Did you do it with code or through EM?|||I did it through EM. I have two SQL Server instances on seperate servers that are having the same problem. The only common denominator is the target Sybase server. I can't duplicate the problem against any other Sybase servers. I'm going to reboot the Sybase server tonight to see if that clears anything up. I'll let you know how it goes.|||Good Luck...

Time for a 'rita...

Later...|||Originally posted by Brett Kaiser
Hey Pat, it's past 3:30...EST

Is there a way to see the linked server catalogs? Not hardly, it was just past 15:30 EST when you posted! I really meant 03:30!

-PatP|||all righty then...

Hey Peter, did bouncing the box help?|||OK, I found the problem. There's a bug in Sybase's OLEDB provider. Luckily there's a patch out for the bug.

If you've setup more than one OLEDB profile in the Configuration Manager the first profile that's setup will apply to all additional profiles. No matter what properties are specified in the additional profiles settings.

For example create Profile1 that connects to 10.5.1.4 port 7682.
Cretae Profile2 and specify server 10.5.1.5 port 7680. Eventhough the properties are set correctly on Profile2 it will always connect to 10.5.1.4 port 7682. Also, any addiotional profiles will connect the profile that was created first in the Configuration Manager.

I couldn't see the new tables (in my test db) because my OLEDB connection was logging into a different server (production). Which happen to have the same schema except for the new tables.|||Wow! That's a pretty good one.

-PatP

server-New tables missing

I have a linked server to Sybase ASE 12.5 that has been working great. However, now when new tables are created in the Sybase db they can't be found using the linked server connection. The login the linked server is using has all permissions granted on the new tables too. Any ideas?Sounds like a re-fresh problem, disconnect and reconnect
the server.
hth
>--Original Message--
>I have a linked server to Sybase ASE 12.5 that has been
working great. However, now when new tables are created
in the Sybase db they can't be found using the linked
server connection. The login the linked server is using
has all permissions granted on the new tables too. Any
ideas?
>.
>|||I've tried that and I've even created a new linked server. They still don't show up. I tried executing a query using Query analyzer and here's the SQL and error message I get
select i_con_contract from openquery([32tlsql2-dreamdb],
'Select i_con_contract From dbo.temp_client_3' )
Server: Msg 7399, Level 16, State 1, Line
OLE DB provider 'Sybase.ASEOLEDBProvider' reported an error.
[OLE/DB provider returned message: [Native Error code: 208
[DataDirect ADO Sybase Provider] dbo.temp_client_3 not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output)
OLE DB error trace [OLE/DB Provider 'Sybase.ASEOLEDBProvider' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].

server-New tables missing

Sounds like a re-fresh problem, disconnect and reconnect
the server.
hth
>--Original Message--
>I have a linked server to Sybase ASE 12.5 that has been
working great. However, now when new tables are created
in the Sybase db they can't be found using the linked
server connection. The login the linked server is using
has all permissions granted on the new tables too. Any
ideas?
>.
>
I've tried that and I've even created a new linked server. They still don't show up. I tried executing a query using Query analyzer and here's the SQL and error message I get.
select i_con_contract from openquery([32tlsql2-dreamdb],
'Select i_con_contract From dbo.temp_client_3' )
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Sybase.ASEOLEDBProvider' reported an error.
[OLE/DB provider returned message: [Native Error code: 208]
[DataDirect ADO Sybase Provider] dbo.temp_client_3 not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).
]
OLE DB error trace [OLE/DB Provider 'Sybase.ASEOLEDBProvider' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].

server-New tables missing

Sounds like a re-fresh problem, disconnect and reconnect
the server.
hth
>--Original Message--
>I have a linked server to Sybase ASE 12.5 that has been
working great. However, now when new tables are created
in the Sybase db they can't be found using the linked
server connection. The login the linked server is using
has all permissions granted on the new tables too. Any
ideas?
>.
>I've tried that and I've even created a new linked server. They still don't
show up. I tried executing a query using Query analyzer and here's the SQL
and error message I get.
select i_con_contract from openquery([32tlsql2-dreamdb],
'Select i_con_contract From dbo.temp_client_3' )
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Sybase.ASEOLEDBProvider' reported an error.
[OLE/DB provider returned message: [Native Error code: 208]
[DataDirect ADO Sybase Provider] dbo.temp_client_3 not found. Specify ow
ner.objectname or use sp_help to check whether the object exists (sp_help ma
y produce lots of output).
]
OLE DB error trace [OLE/DB Provider 'Sybase.ASEOLEDBProvider' IColumnsIn
fo::GetColumnsInfo returned 0x80004005: ].

server to sybase database

Hi
I created linked server to sybase database using adaptive server odbc driver.
But the performance sucks while loading large tables even after manipulating
performance tab in odbc driver.
Can anyone suggest some tuning work on linked server environment to sybase.
Thanks for your wonderful help
Praveen Mohan
Hi
What do you regard as "suck"?
If you are pulling 1'000'000 records, you are limited by your network
performance.
How many records are you dealing with? is the linked server a part of a JOIN
query?
Regards
Mike
"PraveenMohan" wrote:

> Hi
> I created linked server to sybase database using adaptive server odbc driver.
> But the performance sucks while loading large tables even after manipulating
> performance tab in odbc driver.
> Can anyone suggest some tuning work on linked server environment to sybase.
> Thanks for your wonderful help
> Praveen Mohan
|||Also, it can be really revealing to look at the query plan when you go against a linked server. Some
manual optimization can be applied using OPENQUERY instead of 4-part table naming. For example,
pushing a WHERE predicate inside the OPENQUERY function to you know for sure the filtering is
performed at the remote site.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:3D332524-E849-4449-B2BD-15BB443BE6F1@.microsoft.com...[vbcol=seagreen]
> Hi
> What do you regard as "suck"?
> If you are pulling 1'000'000 records, you are limited by your network
> performance.
> How many records are you dealing with? is the linked server a part of a JOIN
> query?
> Regards
> Mike
> "PraveenMohan" wrote:
|||Praveen
Can you describe how did you do that?
I am having the problem with setting up my connection so your input will be
very helpfull.
Thank you, Gene.
"PraveenMohan" wrote:

> Hi
> I created linked server to sybase database using adaptive server odbc driver.
> But the performance sucks while loading large tables even after manipulating
> performance tab in odbc driver.
> Can anyone suggest some tuning work on linked server environment to sybase.
> Thanks for your wonderful help
> Praveen Mohan

server to sybase database

Hi
I created linked server to sybase database using adaptive server odbc driver.
But the performance sucks while loading large tables even after manipulating
performance tab in odbc driver.
Can anyone suggest some tuning work on linked server environment to sybase.
Thanks for your wonderful help
Praveen MohanAlso, it can be really revealing to look at the query plan when you go against a linked server. Some
manual optimization can be applied using OPENQUERY instead of 4-part table naming. For example,
pushing a WHERE predicate inside the OPENQUERY function to you know for sure the filtering is
performed at the remote site.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:3D332524-E849-4449-B2BD-15BB443BE6F1@.microsoft.com...
> Hi
> What do you regard as "suck"?
> If you are pulling 1'000'000 records, you are limited by your network
> performance.
> How many records are you dealing with? is the linked server a part of a JOIN
> query?
> Regards
> Mike
> "PraveenMohan" wrote:
>> Hi
>> I created linked server to sybase database using adaptive server odbc driver.
>> But the performance sucks while loading large tables even after manipulating
>> performance tab in odbc driver.
>> Can anyone suggest some tuning work on linked server environment to sybase.
>> Thanks for your wonderful help
>> Praveen Mohan|||Hi
What do you regard as "suck"?
If you are pulling 1'000'000 records, you are limited by your network
performance.
How many records are you dealing with? is the linked server a part of a JOIN
query?
Regards
Mike
"PraveenMohan" wrote:
> Hi
> I created linked server to sybase database using adaptive server odbc driver.
> But the performance sucks while loading large tables even after manipulating
> performance tab in odbc driver.
> Can anyone suggest some tuning work on linked server environment to sybase.
> Thanks for your wonderful help
> Praveen Mohan|||Praveen
Can you describe how did you do that?
I am having the problem with setting up my connection so your input will be
very helpfull.
Thank you, Gene.
"PraveenMohan" wrote:
> Hi
> I created linked server to sybase database using adaptive server odbc driver.
> But the performance sucks while loading large tables even after manipulating
> performance tab in odbc driver.
> Can anyone suggest some tuning work on linked server environment to sybase.
> Thanks for your wonderful help
> Praveen Mohan

server to Sybase ASE 12.5.3 via Sybase OLE DB provider 2.70.00.74 doesn' work.

Hi,
I tried to define a linked server from SQL Server 2005 SP 1 to ASE 12.5.3 via Sybase OLE DB provider 2.70.00.74 (written by DataDirect) as well as Sybase OLE DB provider 12.5.1.510 (written by Sybase). Both linked server doesn't work.
But I am able to define linked server if I use an old version of Sybase OLE DB provider 2.70.00.24.

Here are the error messages I get:

1. ASEOLEDBProvider, first try:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Sybase.ASEOLEDBProvider" for linked server "BUGATTI" reported an error. The provider reported an unexpected catastrophic failure.
Msg 7306, Level 16, State 2, Line 1
Cannot open the table "kunktest1.dbo.kunktable" from OLE DB provider "Sybase.ASEOLEDBProvider" for linked server "BUGATTI". Unknown provider error.
2. ASEOLEDBProvider, second try:
Msg 7301, Level 16, State 2, Line 1
Cannot obtain the required interface ("IID_IGetDataSource") from OLE DB provider "Sybase.ASEOLEDBProvider" for linked server "BUGATTI".
3. ASEOLEDB
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "ASEOLEDB" for linked server "BUGATTI2" reported an error. The provider did not give any information about the error.
Msg 7304, Level 16, State 2, Line 1
Cannot connect using OLE DB provider "ASEOLEDB" to linked server "BUGATTI2". Verify the connection parameters or login credentials associated with this linked server.

Has anybody else the same problems ? Is there a solution ?

Could you check if this is a permission problem. Please try to
create a linked server with sa login and check to see if the same error
persists.

Thanks

|||I don't think that it is a permission problem, because I created the 'linked server' with the sa login.
And as I said before I was able to connect to a linked server with an older version (OLE DB provider 2.70.00.24) of the same OLEDB provider and the same properties!
But there we have problems with updates on tables with datetime columns which contains millisecond fractions.

By the way, in the meantime I was able to define a linked server over Sybase OLEDB provider 'Sybase OLEDB Provider for ASE 12.5.1.510'. Instead of using a udl-file (don't know how they should be used, found no docu) I wrote <host>, <port> directly to the datasource field in the 'create linked server' dialog of management studio.
But when I execute
select * from BUGATTI4.kunktest1.dbo.kunktable
(BUGATTI4 is the linked server)
I got following error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "ASEOLEDB" for linked server "BUGATTI4" reported an error. The provider did not give any information about the error.
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "ASEOLEDB" for linked server "BUGATTI4". The provider supports the interface, but returns a failure code when it is used.

Auditing on Sybase site shows the following statements:
extrainfo
Login ....
Command Text exec sp_oledb_tables @.table_catalog = "kunktest1", @.table_schema = "dbo", @.table_name = "kunktable"
Logout ....

server to Sybase 11 problem

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

- Used DSEDIT to create a data source. Ping(Test) works
- Created a System DSN using the Sybase System 11 driver
- Created a Linked Server with following options
General Tab
- Provider as - MS OLE DB Provider for ODBC Drivers
- Data source as the DSN name created above
- No provider string
- Put in the Catalog name
Security Tab
- No local login
- Selected "be made using this security context"
- Specified remote login and password as the Sybase "sa" and pwd
Server Options Tab
- Checked only Collation compatible and Data Access checkboxes

On trying to see the list of tables, got the following error

Error 7399: OLE DB Provider 'MSDASQL' reported an error.
The provider reported an unexpected catastrophic failure.
SqlDumpExceptionHandler: Process 52 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION.SQL Server is termintating this process.

OLE DB error trace[OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x8000ffff: The provider
reported an unexpected catastrophic failure.].

Any solution to this?

HI Mat,

Any response to you query? Even I am facing the same problem. Can you help me out if you have a solution?..

Pls respond to ansathya@.yahoo.com id..

thanks in advance.

Sathya|||We looked into this at is seems like an issue in the sybase provider itself. I'm trying to get a contact there...you folks should try contacting them as well.|||We have got a solution for this from some sources.
Syabase has a known issue on this.

Here are the steps to resolve the problem.

1. In OBDC, while creating the DSN (with the Sybase server name as DSN name) the database name should not be given.

2. In the Linked server properties only the Data source should be given. Remote login (sa and pwd) and in server options only Collation compatible and Data Access are to be selected.

3. The default database for sybase 'sa' login was taken as the user database for which the tables are to be linked.

The above solution works perfectly without issues.

Hope it is helpful to all.
Regards
Geeta