No table names appear (only Stored Procs) when connected to SQLServer w/dbo
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.
Don Williams replied
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
[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.