servers, catalog property and four part naming?

I've finaly gotten a Linke Server up and running and been playing around with the settings, and one question keeps bugging me.

What is the point of the Catalog property value when you always have to put that database in the four part naming anyways?

Thanks.

I believe it is for linked server configuration and testing...

If you don't want use four part name all the time, you can create Synonyms in sql server 2005

Check BOL for topic 'Synonyms '

|||

Flip wrote:

What is the point of the Catalog property value when you always have to put that database in the four part naming anyways?

The Catalog property does a couple of things for you. First it allows you to not have to specify the actual database name.

Code Snippet

SELECT *

FROM SERVER..dbo.sysfiles

Also, when you execute code against a linked server if you look at sp_who2 on the remote server it will always show the logins default database for the database which you are executing code against. If you set the Catalog field this will show as the database which is being used for the connection default.

|||

Thank you for the response. Have you tried this exact code before? In my testing, I was not able to get this to work (as much as I hoped it would :>).

When I try it out (like you said, with the Catalog set to the desired db and without the db name in the query), I get the following error.

OLE DB error trace [Non-interface error: Invalid schema or catalog specified for the provider.].

Msg 7313, Level 16, State 1, Line 1

Invalid schema or catalog specified for provider 'SQLOLEDB'.

Am I missing something, or maybe I have a bad setting/property?

Thanks.

servers, catalog property and four part naming?

I've finaly gotten a Linke Server up and running and been playing around with the settings, and one question keeps bugging me.

What is the point of the Catalog property value when you always have to put that database in the four part naming anyways?

Thanks.

I believe it is for linked server configuration and testing...

If you don't want use four part name all the time, you can create Synonyms in sql server 2005

Check BOL for topic 'Synonyms '

|||

Flip wrote:

What is the point of the Catalog property value when you always have to put that database in the four part naming anyways?

The Catalog property does a couple of things for you. First it allows you to not have to specify the actual database name.

Code Snippet

SELECT *

FROM SERVER..dbo.sysfiles

Also, when you execute code against a linked server if you look at sp_who2 on the remote server it will always show the logins default database for the database which you are executing code against. If you set the Catalog field this will show as the database which is being used for the connection default.

|||

Thank you for the response. Have you tried this exact code before? In my testing, I was not able to get this to work (as much as I hoped it would :>).

When I try it out (like you said, with the Catalog set to the desired db and without the db name in the query), I get the following error.

OLE DB error trace [Non-interface error: Invalid schema or catalog specified for the provider.].

Msg 7313, Level 16, State 1, Line 1

Invalid schema or catalog specified for provider 'SQLOLEDB'.

Am I missing something, or maybe I have a bad setting/property?

Thanks.

sql

servers Issues

I have a NAMES server and APPLICATION sever. I created a linked server
reference on APPPLICATION to point to NAMES. I did map the APPLICATION
user/password to that on NAMES
Issue : when I click on TABLES in the link server tree all I can see is
tables in the master database. The user/password I pass to the linked server
has rights to multiple databases on NAMES. The user in NAMES default databas
e
is master, but I would think I could see all db on NAMES that the user I
passed had access to.
The .NET query designer just tells me that the login failed when I try to
save the SP or on some occasions that it failed to start a distributed
transaction b/c login failed.
Ive tried reading up on linked servers on MS website and there seem to be so
many retractions and configurations that need to be made Im starting to get
. I thought this was going to be easy or am i misunderstanding. Also
the MSDN page for linked servers seems to imply that you can only do select
and updates from a linked server and not inserts. Is this true? Our NAMES
database is used by multiple applications and multiple databases. Because of
performance I realize that NAMES database would work better on its own serve
r.
JP
.NET Software DevelperJP,
Change the default database for the linkedserver login on the linked server
to database on which you'd like to see the tables listed. You can use EM or
sp_defaultdb.
HTH
Jerry
"JP" <JP@.discussions.microsoft.com> wrote in message
news:22FE02D9-8576-43E5-9EA7-AEB55C5E5704@.microsoft.com...
>I have a NAMES server and APPLICATION sever. I created a linked server
> reference on APPPLICATION to point to NAMES. I did map the APPLICATION
> user/password to that on NAMES
> Issue : when I click on TABLES in the link server tree all I can see is
> tables in the master database. The user/password I pass to the linked
> server
> has rights to multiple databases on NAMES. The user in NAMES default
> database
> is master, but I would think I could see all db on NAMES that the user I
> passed had access to.
>
> The .NET query designer just tells me that the login failed when I try to
> save the SP or on some occasions that it failed to start a distributed
> transaction b/c login failed.
> Ive tried reading up on linked servers on MS website and there seem to be
> so
> many retractions and configurations that need to be made Im starting to
> get
> . I thought this was going to be easy or am i misunderstanding.
> Also
> the MSDN page for linked servers seems to imply that you can only do
> select
> and updates from a linked server and not inserts. Is this true? Our NAMES
> database is used by multiple applications and multiple databases. Because
> of
> performance I realize that NAMES database would work better on its own
> server.
>
> --
> JP
> .NET Software Develper|||Is there no way to see multiple databases on the linked server? In addtion
can I also see views and tables and not SPs?
JP
.NET Software Develper
"Jerry Spivey" wrote:

> JP,
> Change the default database for the linkedserver login on the linked serve
r
> to database on which you'd like to see the tables listed. You can use EM
or
> sp_defaultdb.
> HTH
> Jerry
> "JP" <JP@.discussions.microsoft.com> wrote in message
> news:22FE02D9-8576-43E5-9EA7-AEB55C5E5704@.microsoft.com...
>
>|||JP,
The treeview in EM shows the tables and views for the default database of
the LS login on LS. EM is not customizable but you could use .NET and
SQL-DMO to create your own SQL admin/treeview application. Optionally, if
you're just trying to view the objects on the LS and the LS is a SQL Server,
why not just register it in EM. Outside of that you'd have to run T-SQL to
get what you're looking for qualifying the database in the OPENQUERY
statement or the 4-part name. i.e., you could use sp_stored_procedures or
some custom script to show the procs.
HTH
Jerry
"JP" <JP@.discussions.microsoft.com> wrote in message
news:3DF957D0-E428-48F2-ADC8-B756B8156E8C@.microsoft.com...
> Is there no way to see multiple databases on the linked server? In addtion
> can I also see views and tables and not SPs?
> --
> JP
> .NET Software Develper
>
> "Jerry Spivey" wrote:
>

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

servers

Hello All,

I have been trying to Link two sql servers on two different machines over the Internet without any luck. Can someone point me to information about doing this with good examples?

ThanksI am trying to determine how to point to the remote server over the Internet.

Any ideas?|||You need to communicate with it over TCP/IP. You should test that you can talk to it using only TCP/IP.