Applying edits in GUI not working without "dbo" in table name

[craigshaynak]

Describe the bug When I execute a SELECT on a table ('Shows' for example), I used to able to edit a row using "Edit selected row" and edit a field using the GUI to enter a new value and then clicking the "Apply" button. I must now include the full table name ('dbo.Shows') to allow this functionality.

To Reproduce

1. EDIT RECORD BUG
2. Click 'New Query'
3. Enter a command: SELECT * FROM Shows Where ShowID = 8111;
4. Right Click on the query results and choose "Edit selected row..."
5. A blank edit grid appears (not allowing an edit)
6. EDIT FIELD BUG
7. Edit the field 'ShowCompanyID'
8. I get this error: WARNING: Could not find column 'ShowCompanyID' on table 'Shows'. Does your query include aliases? That can cause this issue.

NOTE: When I use the full table name "dbo.Shows" the edits work fine.

Expected behavior I used to be able to use the raw table name for these edit functions. I expect to be able to use "SELECT FROM Shows" rather than "SELECT FROM dbo.Shows" as I used to be able to in previous versions.

Screenshots

Environment details (please complete the following information):

• Device: MacBook Pro 16" 2021 / Apple M1 Pro / 16 GB RAM
• OS: Ventura 13.4.1
• SQLPro Studio Version 2023.43 (Build 111043.5)
• Installation source: App Store
  • Target database server: Microsoft SQL Server 2019 RTM

Additional context I LOVE this software and have used it for years... While this is not "broken," the behavior has changed at some point. Is there a setting I need to change to allow using the table names without "dbo."?

Issues will be closed until environmental details are provided.

If the above template is not completed, issues with be closed with the statement Issue has been closed. Please edit the initial post (or create a new issue) and follow the template. Once completed, the issue may be reopened.

[hankinsoft]

Investigating now.

[hankinsoft]

What do you get when you run the query:

SELECT
    default_schema_name 
FROM
    sys.database_principals 
WHERE 
    name = CURRENT_USER

UNION

SELECT 
    SCHEMA_NAME()

On that database?

[hankinsoft]

Also, What do you get when running this query:

SELECT
    DISTINCT c.name 'Column Name',
    ISNULL(t.Name, 'UNKNOWN') 'Data type',
    c.max_length 'Max Length',
    c.precision,
    c.scale,
    c.is_nullable,
    (
        SELECT
            TOP 1 is_primary_key
        FROM
            sys.index_columns ic
            LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id
            AND ic.index_id = i.index_id
            AND i.is_primary_key = 1
        WHERE
            ic.object_id = c.object_id
            AND ic.column_id = c.column_id
    ) 'Primary Key',
    OBJECT_DEFINITION(c.default_object_id) AS columnDefault,
    c.is_identity,
    CASE c.is_identity WHEN 1 THEN ident_seed('Shows') ELSE NULL END AS seed,
    CASE c.is_identity WHEN 1 THEN ident_incr('Shows') ELSE NULL END AS increment,
    cc.definition AS computedDefinition,
    c.column_id,
    t.is_user_defined
FROM
    sys.columns c
    INNER JOIN sys.types t ON (c.user_type_id = t.user_type_id OR c.system_type_id = t.user_type_id)
    LEFT OUTER JOIN sys.computed_columns cc ON cc.name = c.name
    AND cc.object_id = OBJECT_ID('Shows')
WHERE
    c.object_id = OBJECT_ID('Shows')
ORDER BY
    c.column_id ASC

On that database? I would like results from both this query and the one above please.

[craigshaynak]

Thanks for looking into this... Here is what I get:

I just realized, I MOVED this SQL Server database from one server to another last year! Maybe the default owner is NOT "dbo" anymore... Could that be the problem? I will send the results of the other query you asked me to run in reply to the other email. Thanks again! Again, I LOVE your software and use it ALL the time!

Craig Ricci Shaynak US Mobile: +1 213 804 6066 Skype: cshaynak  www.craigriccishaynak.com  This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, distributing or taking any action in reliance on the contents of this information is strictly prohibited.

On Tuesday, July 25, 2023 at 09:44:31 AM PDT, Kyle Hankinson ***@***.***> wrote:  

What do you get when you run the query: SELECT default_schema_name FROM sys.database_principals WHERE name = CURRENT_USER

UNION

SELECT SCHEMA_NAME()

On that database?

— Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you authored the thread.Message ID: @.***>

[craigshaynak]

Kyle, here are the results of the second query you sent: Thanks again for looking into this. Based on the first query, I think the issue is that the owner of my tables is not "dbo" anymore after I copied it from another SQL server...

Craig Ricci Shaynak US Mobile: +1 213 804 6066 Skype: cshaynak  www.craigriccishaynak.com  This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, distributing or taking any action in reliance on the contents of this information is strictly prohibited.

On Tuesday, July 25, 2023 at 10:34:05 AM PDT, Kyle Hankinson ***@***.***> wrote:  

Also, What do you get when running this query: SELECT DISTINCT c.name 'Column Name', ISNULL(t.Name, 'UNKNOWN') 'Data type', c.max_length 'Max Length', c.precision, c.scale, c.is_nullable, ( SELECT TOP 1 is_primary_key FROM sys.index_columns ic LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id AND i.is_primary_key = 1 WHERE ic.object_id = c.object_id AND ic.column_id = c.column_id ) 'Primary Key', OBJECT_DEFINITION(c.default_object_id) AS columnDefault, c.is_identity, CASE c.is_identity WHEN 1 THEN ident_seed('Shows') ELSE NULL END AS seed, CASE c.is_identity WHEN 1 THEN ident_incr('Shows') ELSE NULL END AS increment, cc.definition AS computedDefinition, c.column_id, t.is_user_defined FROM sys.columns c INNER JOIN sys.types t ON (c.user_type_id = t.user_type_id OR c.system_type_id = t.user_type_id) LEFT OUTER JOIN sys.computed_columns cc ON cc.name = c.name AND cc.object_id = OBJECT_ID('Shows') WHERE c.object_id = OBJECT_ID('Shows') ORDER BY c.column_id ASC

On that database? I would like results from both this query and the one above please.

— Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you authored the thread.Message ID: @.***>

[hankinsoft]

Hi, Unfortunately neither screenshot showed up. Attachments don't work when replying via email. But from what it sounds like the problem is probably your default owner.

Cheers!

[craigshaynak]

Thanks for all the hard work researching that… I’m sorry to send you on a wild goose chase!I didn’t think about the fact that I copied this database until you asked me to run those queries… you’re a great detective!I tell everybody to get your software… it’s been a lifesaver time and time again!Craig Ricci Shaynak(213) 804-6066 mobile www.craigshaynak.comOn Jul 26, 2023, at 5:06 AM, Kyle Hankinson @.***> wrote: Hi, Unfortunately neither screenshot showed up. Attachments don't work when replying via email. But from what it sounds like the problem is probably your default owner. Cheers!

—Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you authored the thread.Message ID: @.***>

[hankinsoft]

No worries, please let me know if you run into anything else.