I am using TOP 3.1.3 on SQL Server 2008 on windows XP Column analysis has been fine however I attempted to run a table analysis with a data quality rule and got an invalid object name, I have previewed the SQL for both column analysis and table analysis on my database and the difference seems to be that the format of the sql that is being run for column analysis is different to the table analysis format. The format for column analysis is select .... from "database"."schema"."table" where .............. however when I hover over the DQ rule in the table analysis window to preview the SQL the format is select ..... from "table" where ........ I guess that is why it is failing as the table isn't being fully qualified in the sql statement for the table analysis hence why I am getting the invalid object error?
my DQ rule for trying to find invalid names is:
Client_Name like '%DUP%' or Client_Name like 'Invalid%' or Client_Name like '%Invalid%' or Client_Name like '%Test%' or Client_Name like '%Blah%'
or Client_Name like '%tbc%' or Client_Name like '%tba%' or Client_Name like '%TBA%' or Client_Name like '%-%' or Client_Name like '%..%' or Client_Name like '%Unknown%'
So I have added this DQ rule to the appropriate table 'Client' in the table analysis editor, when I right click on the DQ rule in the table analysis editor and click on view executed query I get:
SELECT COUNT(*) FROM "Client" WHERE (Client_Name like '%DUP%' or Client_Name like 'Invalid%' or Client_Name like '%Invalid%' or Client_Name like '%Test%' or Client_Name like '%Blah%' or Client_Name like '%tbc%' or Client_Name like '%tba%' or Client_Name like '%TBA%' or Client_Name like '%-%' or Client_Name like '%..%' or Client_Name like '%Unknown%')
when I try and run this table analysis in TOP I get the invalid object error, when I run this same SQL statement in TOAD I also get the invalid object error.
However in TOAD if i change the query so that the table name is qualified by the schema name it returns results as expected
SELECT COUNT(*) FROM "ClientSchema"."Client" WHERE (Client_Name like '%DUP%' or Client_Name like 'Invalid%' or Client_Name like '%Invalid%' or Client_Name like '%Test%' or Client_Name like '%Blah%' or Client_Name like '%tbc%' or Client_Name like '%tba%' or Client_Name like '%TBA%' or Client_Name like '%-%' or Client_Name like '%..%' or Client_Name like '%Unknown%')
If I do a simple column analysis on the same client table, if i right click on 'row count' within the analysed columns table in the column analysis editor and select 'view executed query' this is the result
SELECT COUNT(*) FROM "ClientDB"."ClientSchema"."Client"
This simple column analysis runs without error in TOP, and the sql runs without error in TOAD or SQL server management studio, although when connecting via JDBC in TOP I gave a database name so strictly speaking the 'ClientDB" part of the above statement wouldn't have been strictly necessary.
The four-part naming syntax in SQL Server 2005/2008 for referring to objects specifies the schema name i.e:
Server.Database.DatabaseSchema.DatabaseObject
If a table in SQL Server is part of the default (dbo) schema, then only the table name would be required, so in this case the table analysis in TOP using my DQ rule would NOT fail (I have tested this against another sql server database where all the tables are owned by the dbo schema and the table analysis functionality works)
The way TOP is building the SQL statements appears to be different in the column analysis and the table analysis which is why I believe i am getting the invalid object error when trying to run TOP table analysis against a SQL Server DB where the tables are owned by schemas other than the default (dob) schema.
Changing a schema owner of a table wouldn't not be possible in the database I am using, it could break our applications, the whole point of the schema being the owner rather than users is that you can now drop users without affecting objects within a SQL server DB.
I had a quick look at the link you provided, that seems to be setting default values for attributes, I don't think it is related to the issues I am having.
In case others find this same issue here is the only way round I have found.... if you don't want to move all your tables to the dbo schema:
1. Must have a user with the same name as the schema where your tables are (this is related to the issues with the JDBC driver i mentioned in my previous post )
2. That user must have the default schema property set to the schema where your tables are.
3. You have to connect to the DB as that user, which in my case rules out connnecting via window authentication, so I had to get our DBA to change the SQL server to mix authentication
This is definetly a bug as if the table analysis was qualifying the table name as per the column analysis functionality then this wouldn't be an issue. I have raised a bug 8502
