SQL Server Connection not showing table schemas/columns

[bobpeers]

I have a connection to SQL Server that successfully shows the database tables. However I am unable to either view the table columns or change the permissions to make the data source public.

When I select the table nothing happens and it continues to show the inbuilt DB Users table.

However I have one table in my database that it is able to see the schema for. It's the only table without a space in the name but this might be coincidence.

When previewing the app I can see data in the tables but cannot make a public app since there's no way to make the source public. All tables have identical permissions.

[melohagan]

Hey @bobpeers

The space in the table name could possibly be an issue. Would you be able to check the console (Fn+F12 in Chrome) and share screenshots of any errors that you see when viewing one of your SQL tables that does not return data please?

If there's no console errors then check the Network tab for errors.

[bobpeers]

This is trying to load the table DateDimension.

Result, not loaded

[melohagan]

So those tables listed in the error as having No primary key constraint won't load because they don't have a primary key.

For tables to function properly in Budibase, they must have a Primary key defined in the SQL Schema.

[bobpeers]

Understood and that's fine. The problem is that if I have any tables without a primary key it causes everything else to fail as well. Many tables do have primary keys but these also don't work.
For example the table [NN07 PowerApp Item] has a pk but doesn't work

[melohagan]

And to double check, if you expand the Constraints folder, do you see a primary key as well?

Would you be able to share the CREATE script for the table?

[bobpeers]

Sure, glad to help :)

SQL script:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[NN07 PowerApp Item](
	[Master No_] [varchar](255) NOT NULL,
	[Description] [varchar](255) NOT NULL,
	[Description 2] [varchar](255) NOT NULL,
	[EAN] [varchar](255) NOT NULL,
	[Colour Code] [varchar](255) NOT NULL,
	[Colour] [varchar](255) NOT NULL,
	[Starting Date] [datetime] NULL,
	[Ending Date] [datetime] NULL,
	[DKK] [int] NULL,
	[EUR] [int] NULL,
	[GBP] [int] NULL,
	[NOK] [int] NULL,
	[SEK] [int] NULL,
	[AUD] [int] NULL,
	[CAD] [int] NULL,
	[CHF] [int] NULL,
	[USD] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
ALTER TABLE [dbo].[NN07 PowerApp Item] ADD  CONSTRAINT [pk_powerapp] PRIMARY KEY CLUSTERED 
(
	[EAN] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

[melohagan]

Hey @bobpeers

Thank you for the schema. I was able to fetch the table, and insert rows okay.

What was the issue you were seeing?

[bobpeers]

I can fetch the table OK

But selecting the table does not show the schema. It just redisplays whatever object was previously selected. in this case the Budibase Users table

[bobpeers]

I feel sure it's somehow connected to the errors generated with the tables without a primary key which then breaks the tables that are loaded.
I plan to test this by creating a new database schema with only one table to see if it works. You could also test by creating another table with no primary key and see if you can still see the schema of the PowerApp item table

[melohagan]

Thanks @bobpeers that's a good idea. I'll add another table without a primary key to my schema and see if I can reproduce!

[bobpeers]

I just tested this by creating a new schema call App with this table as the only table in the schema. The same problem persists. I can load the table with no errors this time about primary keys but still cannot load the schema of the table.

I'm stuck what to do now 🤷‍♂️

[melohagan]

Hmm that's interesting @bobpeers

Maybe the app itself has become corrupted. Could you try creating a brand new app and then fetching the SQL Server table in it.

Also which version of Budibase are you running?

[bobpeers]

Latest version

Created a new app but it behaves in exactly the same way as the existing one.

[bobpeers]

@melohagan I see you published an update and I can now fetch the schema of all my tables. Many thanks for this, appreciate the quick fix!

[bobpeers]

Ahh. Broken again now. Finds the primary key 4 times and can no longer see the table schema like before

[bobpeers]

@melohagan Just to add the details it's broken on version 2.4.40 and I tried downgrading to 2.4.39 but that's also broken. I have two app in development and both are broken in the same way, unable to see table schemas. Both apps are using different schemas as well.
Not sure how you fixed it before but something has reverted.

[melohagan]

Hey @bobpeers

Are you cloud or self-hosted?

[bobpeers]

I'm on the cloud and I've tried re-adding the server many times to the same result.

[bobpeers]

@melohagan I believe you just pushed an update as it works again now :) Here's hoping it stays fixed this time.