No table names appear (only Stored Procs) when connected to SQLServer w/dbo

[Former Member]

Greetings,

I'm using Crystal Reports 11, connecting to a databse on SQL Server 2005.

I am on Windows 7. I also have the problem on Windows Server 2003

Starting from a blank database, I create my connection through ODBC, and when I expand my database, all I get is a list of stored procedures, no tables.

I have isolated this to whether I have dbo authority on this particular database or not.

i.e. I have a user with dbo authority, and they see nothing but stored procedure names, I take dbo authority away, and they see tables, views, stored procs, all as would be expected.

I have several other SQL Server databases that do not share this issue. Users with dbo authority can see tables as expected.

I have compared the settings on the databases, and nothing appears to be different (the database with the issue is considerably larger, but I can't see where that would be an issue)

I will probably also open this issue with Microsoft, to see what they say from their end

If anyone has seen this before, or would have any suggestions they would be greatly appreciated. Seems very strange that giving MORE authority would be affecting what the user can see.

[Former Member · Accepted solution]

Don,

Thank you for your response.

I checked the version of MDAC on my machine and the registry shows the following:

FullInstalVer 6.1.7600.16385

Version 2.0.0

I also checked it on our TS box (running Windows Server 2003), where I was also seeing the issue and it shows:

FullInstallVer 2.82.3959.0

Version 2.0.0

Using the OLE DB connection did make a difference. I see the tables as I would normally expect to see them.

I guess I'm not sure where that leaves me (other than that this probably isn't a Crystal Reports problem)

If you have any other suggstions or insight, it would be greatly appreciated, otherwise I suppose it's off to Microsoft to see what they can tell me (or just talk the user into using the 'work around' of creating the reports with a generic user ID that doesn't have dbo authority)

Thanks again

[former_member11696 · Accepted solution]

Hello,

Not sure what those numbers mean but they appear to be quite different, could be their MDAC has not been updated in a long time.

Try this, download [SQLCON32|https://smpdl.sap-ag.de/~sapidp/012002523100006252882008E/sqlcon32.zip] and run it on your PC and then connect using your DSN and then on their PC. Copy the connection info and paste it in here.

Then using that same tool you can get table info from both. If it returns info for both then it's a CR issue, if it doesn't then it's likely an ODBC driver issue. SQLCON32 doesn't use any CR components.

And you did not answer my question of which SQL Driver did they select when they created the DSN?

Thanks

Don

[Former Member · Accepted solution]

Don

Below are the results from the SQLCON program you suggested. It is returning Table info... (truncated most of it, as I assume it's not all that pertinent)

SQLConnect Successful

[Microsoft][ODBC SQL Server Driver][SQL Server]Changed database context to 'eas'.

[Microsoft][ODBC SQL Server Driver][SQL Server]Changed language setting to us_english.

ODBC Version is : 03

SQL Driver Name is : SQLSRV32

SQL Driver Version is : 06.01.7600

SQL Driver Supported ODBC Version is : 03

SQL DBMS Name is : Microsoft SQL Server

SQL DBMS Version is : 09.00.4035

SQLTables Successful

[Row, Database, Owner, Name, Type]

1 eas, dbo, ABC_ACCOUNT, TABLE,

2 eas, dbo, ABC_BUSINESS_UNIT, TABLE,

3 eas, dbo, ABC_COMPANY, TABLE,

Here is the listing from the TS server (also seeing the issue):

SQLConnect Successful

[Microsoft][ODBC SQL Server Driver][SQL Server]Changed database context to 'eas'.

[Microsoft][ODBC SQL Server Driver][SQL Server]Changed language setting to us_english.

ODBC Version is : 03

SQL Driver Name is : SQLSRV32

SQL Driver Version is : 03.86.3959

SQL Driver Supported ODBC Version is : 03

SQL DBMS Name is : Microsoft SQL Server

SQL DBMS Version is : 09.00.4035

SQLTables Successful

[Row, Database, Owner, Name, Type]

1 eas, dbo, ABC_ACCOUNT, TABLE,

2 eas, dbo, ABC_BUSINESS_UNIT, TABLE,

3 eas, dbo, ABC_COMPANY, TABLE,

I apologize for not answering your previous question, I think the SQL Driver Name will answer that? If not, let me know and I'll get you the information you're looking for. Thank you again for your time on this.

[Former Member · Accepted solution]

Any chance this could be due to Crystal not being able to handle listing the number of objects in the database.

There are almost 9,000 stored procs, and over 600 tables - it would at least make some sense that since the non-dbo user doesn't have access to see the stored procs, crystal isn't trying to list them, and thus ok handling the tables?

Just brainstorming, so if that's a crazy theory, feel free to say so.

[former_member11696 · Accepted solution]

Hi,

That is an old version of MDAC on the Server:

SQL Driver Version is : 03.86.3959

SQL DBMS Version is : 09.00.4035

I am using 09.00.1399 so you must have applied a patch that I'm not using on SQL Server 2005.

In either case though neither work so I really don't know what is causing this.

My SQLCON32 looks like this:

ODBC Version is : 03

SQL Driver Name is : SQLSRV32

SQL Driver Version is : 06.01.7600

SQL Driver Supported ODBC Version is : 03

SQL DBMS Name is : Microsoft SQL Server

SQL DBMS Version is : 09.00.1399

SQLTables Successful

[Row, Database, Owner, Name, Type]

1 xtreme, dbo, Credit, TABLE,

2 xtreme, dbo, CREXPORT, TABLE,

3 xtreme, dbo, CrystalTest_05012011_MD, TABLE,

4 xtreme, dbo, Customer, TABLE,

....

So we both see tables, that takes the ODBC drivers out of the picture. Why CR can't read them is odd.

Turn on ODBC Tracing, it's a pain to get working but once you do then without being connected to anything else that uses ODBC start tracing and then start CR up and connect to the DB and get as far as wanting to select tables. Make sure the connection is selecting only tables, right click on the Option and check off everything except Tables in the Connection UI of Cr.

Then stop tracing and close CR. I don't have a log handy but you should see it go through all of the connections and then it should use SQLExecute and get a list of tables once you are connected. the result is 0 is it worked otherwise it should log another value if it fails. If it does fail then likely the cause is permissions. You'll have to go into SQL Manager to check permissions for your log on info and all tables.

Thanks again

Don

[Former Member · Accepted solution]

Don,

I used the options to unselect the stored procs when CR grabs info - left tables and views checked, and this seems to have resolved my problem. (probably still an issue with CR somewhere on handling that number of objects, but as far as me and my users are concered this will be an acceptable work around)

Thank you so much for your help on this!!!

[former_member11696 · Accepted solution]

Cool.. It's not an issue, CR needs to load all of them into memory so it can display them for you. I've never seen this being a problem with SQL Server in 14 years I've been here, Oracle yes because they have so many but not MS. You must have an extreme number of SP's, tables and Views in your DB.....

Thanks again

Don

[Former Member · Accepted solution]

the database is a vendor solution, and yes, there is an indordinate amount of stored proc, etc --- thank you again, I really appreciate it!

[former_member11696]

Hello,

I've never heard of this from a CR side. Very strange.

Could be the version of MDAC you are using. Try setting up a DSN using the various MS driver, Native and OLE DB for SQL Server to see if that helps.

Thanks

Don