Developer forum

It can be missing keys or missing permissions on the db user

Can you run this on both database:

sp_help 'EcomProductCategoryFieldValue'

Ensure the SQL login has at least VIEW DEFINITION on the database

Hi, absolutely, here are screenshots comparing the sp_help output for each DB, they look the same to me, aside from some timestamps and index names.

Prod:

And here's from test

Test:

it looks the same to me, I tried creating a new user in prod to try and isolate the problem to the user but the issue persists for the new user. I used the following command to create the new user (the previous user was created following the steps here https://doc.dynamicweb.dev/documentation/fundamentals/setup/installation/install-db.html#set-the-sql-user-database-permissions)

CREATE USER [dw_test_user] FOR LOGIN [dw_test_user] WITH DEFAULT_SCHEMA = [dbo];
ALTER ROLE [db_datareader] ADD MEMBER [dw_test_user];
ALTER ROLE [db_datawriter] ADD MEMBER [dw_test_user];
ALTER ROLE [db_ddladmin] ADD MEMBER [dw_test_user];
GRANT VIEW DEFINITION TO [dw_test_user];

Well. Impossible to say...

My GPT says this....

I poked around the thread; here’s a breakdown + suggestions (and of course, caveats — I don’t have your full environment). Let’s attack this with a detective’s rigor.

What the problem is (as far as we can tell)

The original poster (Gunnar) gets this error in production when saving products:

System.InvalidOperationException: Dynamic SQL generation is not supported against a SelectCommand that does not return any base table information. (doc.dynamicweb.com)

And from the stack:

DatabaseProvider.PrepareAdapter → CreatePreparedAdapter → BuildCache → … → SaveProductValues → SaveAndConfirm → …

In short: some ADO.NET / DataAdapter internal logic is trying to auto-generate INSERT commands (or parameterized commands) based on a SelectCommand. But the SelectCommand is something that doesn’t expose “base table information” (likely missing metadata, or is a view, or is too complex), so the system says: it can’t do dynamic SQL generation here.

In other words: the data layer is trying to infer how to insert/update rows from the “shape” of the select, but it can’t because the select is not simple enough (no underlying single base table with sufficient metadata).

That’s the exception cause. The question is: why is this happening in prod but not in test, given the environments are supposed to be analogous.

The forum replies suggest (and this is a good direction) that it may be due to missing keys or missing permissions for the DB user. Nicolai suggests:

1. Run sp_help 'EcomProductCategoryFieldValue' in both DBs to compare schema. (doc.dynamicweb.com)

2. Ensure the SQL login has VIEW DEFINITION permission on the database. (doc.dynamicweb.com)

The OP responds: they did the sp_help and the outputs look “the same” (barring minor things). They also tried creating a fresh user in prod with the expected roles (db_datareader, db_datawriter, db_ddladmin) + VIEW DEFINITION. Yet the problem persists. (doc.dynamicweb.com)

So:

• The schema as seen seems to match

• The DB user permissions (on paper) seem to be correct

Therefore, the cause is more subtle. Could be:

• A difference in SQL Server version / compatibility level

• A misalignment in schema (something non-obvious) like missing constraints, triggers, computed columns, or metadata

• A difference in how the queries are executed (perhaps in one environment there’s some view, or wrapper, or alias)

• A difference in how Dynamicweb is configured (maybe in test you use a simpler adapter path, but prod uses a slightly different code path, causing SelectCommand to be non-trivial)

• Possibly permissions not just at database level, but at schema / object / extended permission level (like on views / synonyms / metadata objects)

• Some layer of abstraction (synonym or schema redirection) that hides base table info in prod

Steps to systematically troubleshoot (my “wizard checklist”)

Below is a plan. Work down the list; if one fails, go deeper. Take notes as you go — sometimes the smallest mismatch is the culprit.

Hypotheses (and what to test to validate them)

Here are some “theories” about what might be going on. Test these once you have visibility into metadata / SQL:

• Hypothesis A: The table has no primary key or the PK is composite in an unexpected way
  The dynamic command builder often needs a stable primary key (or at least unique key) to map columns. If the PK is missing, or if the user cannot see it (due to permission or schema), the metadata inference fails.

• Hypothesis B: The SELECT is too complex (in a view //from join / with computed columns) so that the underlying “base table” cannot be resolved
  In test, maybe Dynamicweb is hitting the raw table, but in prod there's a view or security wrapper. If so, the command builder says “I can’t resolve which base table(s) are involved.”

• Hypothesis C: Permissions / metadata exposure is partially blocked
  The DB user cannot “see” certain constraints, keys, or extended properties, so the command builder can’t infer them — even if they exist. The VIEW DEFINITION may not be sufficient for some objects, or may be overridden by DENY rules.

• Hypothesis D: Differences in database collation, compatibility level, or SQL Server version
  Maybe in prod some feature is disabled or metadata behavior changed, preventing type introspection.

• Hypothesis E: A misconfiguration or patch difference in Dynamicweb between test and prod
  Perhaps some updated assembly or code path in prod is more strict / newer version, invoking a different DataAdapter logic.

What I’d do right away if I were debugging this hands-on

If I were in your shoes (with access to both production and test):

1. Run full metadata dumps of EcomProductCategoryFieldValue in both environments: e.g.:

   SELECT * 
   FROM sys.columns c 
     JOIN sys.tables t ON c.object_id = t.object_id
   WHERE t.name = 'EcomProductCategoryFieldValue';
   

   Also join with sys.index_columns, sys.indexes, sys.key_constraints, etc.

2. As the problematic DB user in prod, try:

   SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('EcomProductCategoryFieldValue');
   

   If this returns no rows (or partial), that’s a red flag.

3. Trace the SQL being executed when the save is triggered (via SQL Profiler / Extended Events). Examine the initial SELECT that is used by the adapter. See if it has weird aliases, joins, views, etc.

4. Compare the connection string / provider configuration between test and prod. Maybe there’s a subtle difference: e.g. MultipleActiveResultSets, CommandTimeout, Enlist, or a different provider that changes how the DataAdapter is constructed.

5. Try writing a minimal repro: use the same user credentials and from a simple console app trigger a DataAdapter on that table to see if dynamic generation works or fails.

6. If all else fails, override the adapter behavior in your code (in Dynamicweb extension) and supply explicit SQL for inserts instead of relying on dynamic generation.

turned out it was a bug in the SQL Sever Version (I assume) since the issue was solved by getting the system guys to apply a newer CU

IT - you never know what you are gonna get :-). Good find.

Thank you for reporting back.

BR Nicolai