When connecting to a database on SQL Server 2005 Enterprise I can correctly see all the tables/columns but when I try to create forms SERVOY complains that there are no Primary Keys for the table even when they are clearly defined. Dataproviders also shows no PK’s.
This appears to be an issue relating to tables belonging to non dbo schemas. I believe that I have correctly set up the necessary permissions with SS2005 to access all the schemas on the database that are used. I have tried both the MS and JTDS drivers to eliminate that possibility.
Has anyone else come across a similar problem ?
Currently using…
Servoy 2.2.7
Java 1.4.2
Windows 2003 Server for Data/Repository and XP for Developer
For the most part I use MySQL for my servoy solutions but i needed some data from one of our SQL2000 databases.
When I initially set up the databases as data providers, I also got this message. I solved it by going into the dataproviders dialog and setting the Row Ident flag for the pk fields to ‘row_ident’. This allows me to access the tables normally.
It is a bit laborious however, if you have a lot of tables you want to access.
Sounds like Servoy has problems locating the PK.
You can fix this inside Servoy, but you’ll get hit by the same problem again the next time you import.
With PostgreSQL I have to add a Primary Key constaint to table before I try using it in Servoy. This is good practice anyway.
Maybe this applies to MSSQL as well?
We are using SQL Server 2000 for years with Servoy and never had that problem. We did have all kinds of little glitches when we demoed SQL Server 2005, although I don’t remember if the one described was among them…
This sounds to me to be very much a JDBC driver issue than a Servoy issue.
Make sure you have the latest version of the JDBC driver for that specific version of the RDBMS. This pretty much goes for any RDBMS.
This sounds to me to be very much a JDBC driver issue than a Servoy issue
I am using the latest MS 1.1 JDBC Driver, and have tried the JTDS Driver as well but that is older. I’m not convinced that the driver is at fault judging by the testing that I’ve done and info read on the net.
With PostgreSQL I have to add a Primary Key constaint to table before I try using it in Servoy
Yes, I agree. All the data tables (approx 70 across 5 Schemas in a single DB) are all constrained by PK’s and FK’s already pre-defined in MSSQL.
I solved it by going into the dataproviders dialog and setting the Row Ident flag for the pk fields to ‘row_ident’
I tried this but then SERVOY strips out the ‘ID’ suffix from the end of the column names (as defined in MSSQL) and off course the result is that the column does not then exist when I try to use it on the form. Unfortunately I will have no control over the column names.
SERVOY seems to be only picking up the PK’s from the tables that belong to the schema nominated as the default schema in MSSQL. This is set as dbo by default. If I change the default to one of the other schemas that are used, then those tables belonging to that schema are picked up OK. It seems to me to be a SERVOY issue…or have I missed something here ?
I have done more testing on this issue of keys in SQL Server 2005. It appears to me that although SERVOY can correctly see all columns, in all tables, in all schemas belonging to a pre-defined database but that it will only ‘recognise’ pre-defined primary/foreign key information for the DEFAULT schema (dbo), even though the permissions on the server are the same for all the other schemas (sales, production, humanresources etc.). If I change the default schema then SERVOY can see the key information for the tables belonging to the new default schema.