SQL2k, sp_indexes - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

SQL2k, sp_indexes

Author

Topic

dpaulsmith
Starting Member

21 Posts

Posted - 2004-01-15 : 11:25:12

I'm having a strange issue with using sp_indexes. I've been searching hi and lo, and I haven't come up with anything. Maybe you can help.

Background:

MS SQL2K, sp3a. Running on Win XP (for development and testing purposes. All the defaults were chosen during the SQL Server install. And the db owner is SA (and I am using that
login for testing purposes (until I get through this issue).

I am trying to use sp_indexes (the server has been added to the linked table list).

Upon executing sp_indexes (with the proper syntax), the error that is returned is:

Server: Msg 7346, Level 16, State 2, Procedure sp_indexes, Line 9

> Could not get the data of the row from the OLE DB provider 'UNKNOWN'.

> [OLE/DB provider returned message: Requested conversion is not supported]

Any ideas?

Thanks for the help,
Doug Smith

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2004-01-15 : 11:48:54

Can you access the remote table by other means, such as a select statement?

Jonathan
Gaming will never be the same

dpaulsmith
Starting Member

21 Posts

Posted - 2004-01-15 : 12:00:31

I can select any of the tables. In fact I can do just about everything BUT execute that statement. Luckily, I have amother server, so on the off change I screwed up, I tried just executing sp_indexs on the authors table in the pubs db. I got the same message.

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2004-01-15 : 12:03:34

Can you execute sp_help on the remote table? Perhaps its output is all you need.

Jonathan
Gaming will never be the same

dpaulsmith
Starting Member

21 Posts

Posted - 2004-01-15 : 12:05:54

What I really need to be able to do is get a list of components in a key (like the output of sp_indexes).

So if I want to inspect a key that has three components. I need to get a rowset of three rows, each row having the specifics about the component of the overall key.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-15 : 12:19:48

It could be a limitation of linked servers. You will probably have to create your own result set using sysindexes and sysobjects.

Tara

dpaulsmith
Starting Member

21 Posts

Posted - 2004-01-15 : 12:21:42

You could be right, but sp_indexes is supposed to return that kind of info....and it may not be the procedure itself but this funky ole/db stuff. What's annoying is that I can't find anything that suggests what to do with that type of error.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-15 : 12:24:33

I just ran sp_indexes over a linked server and it worked. How was the linked server created? If through EM, please let us know what options are selected.

Tara

dpaulsmith
Starting Member

21 Posts

Posted - 2004-01-15 : 12:28:52

I've tried it both through EM as well as using a query. Through EEM all I have checked is the SQL Server Option, and on the security side I'm using the 3 option.

When I've executed it via a query, I've used the following statement:

exec sp_addlinkedserver 'DSMITH-LAPTOP', 'SQL Server'

TIA,
Doug
dsmith@kda.state.ks.us

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-15 : 12:30:59

What options are checked on the third tab in EM? I've got the 2nd, 3rd, and 4th options checked, which is the default.

Tara

dpaulsmith
Starting Member

21 Posts

Posted - 2004-01-15 : 12:32:29

Ditto...the same for me

Doug Smith
dsmith@kda.state.ks.us

dpaulsmith
Starting Member

21 Posts

Posted - 2004-01-15 : 12:34:11

Can you tell me which version of SQL2K you are running? Mine is: 8.00.760.

Doug Smith
dsmith@kda.state.ks.us

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-15 : 12:35:15

8.00.760 on the server where the linked server is configured. 8.00.818 on server where the linked server is pointing to.

Tara

dpaulsmith
Starting Member

21 Posts

Posted - 2004-01-15 : 12:37:06

Tara-

There is only one server involved in this case, and it's the .760, basically I'm having to put the server in the linked server list (by default its included as a remote server during setup), so that I can use sp_indexes.

Doug Smith
dsmith@kda.state.ks.us

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-15 : 12:44:23

So your linker server is pointing to itself? A remote server is different than a linked server. Linked servers probably weren't designed for what you are trying to accomplish.

Why can't you use sp_help? If it doesn't give you what you want, take a look at the code for sp_indexes. Then create a version that'll work without a linked server.

Tara

dpaulsmith
Starting Member

21 Posts

Posted - 2004-01-15 : 12:45:41

Yes it is. However, I have tried doing that to a true linked server, and got the same problem.

Doug Smith
dsmith@kda.state.ks.us

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-15 : 12:47:53

For the QA way of creating the linked server, did you run sp_addlinkedsrvlogin after you ran sp_addlinkedserver?

Tara

dpaulsmith
Starting Member

21 Posts

Posted - 2004-01-15 : 12:52:39

Yup sure did

Doug Smith
dsmith@kda.state.ks.us

dpaulsmith
Starting Member

21 Posts

Posted - 2004-01-15 : 12:58:19

Tara-

Here's an oddball question, is there any reason why it might behave differently if SQL2K is installed on a server vs. a workstation?

Doug Smith
dsmith@kda.state.ks.us

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-15 : 12:58:38

So how about sp_help?

Also, pull the SELECT statement out of sp_indexes and run that into QA (with the appropriate values of course). See what you get without the stored procedure. Also, what command are you running for sp_indexes? Does the example in Books Online work (change LONDON1 to the linked server name):

EXEC sp_indexes @table_server = 'LONDON1',
@table_name = 'Employees',
@table_catalog = 'Northwind',
@is_unique = 0

Tara

dpaulsmith
Starting Member

21 Posts

Posted - 2004-01-15 : 13:01:01

sp_help only tells you a bit about an index, unlike sp_indexes. And yes, I've tried running the examples (obviously modifying for the server name), but I get the same result.

Doug Smith
dsmith@kda.state.ks.us

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources