error when changing database catalog, with same user credentials and db properties

[yetanotherlogonfail]

as reported here

https://stackoverflow.com/questions/55632576/f-type-providers-2-databases-one-can-access-metadata-one-cant

db Schema permissions are identical. VS community 2017.

F# .net core 2.1 Project, using type providers specifically

SQLProvider - 1.1.16 Fsharp.Core 4.5.2 Fsharp.Data 3.0.1 System.Data.SqlClient 4.6.0 Yes Linq is open too, or the first db would fail .

I have a few databases on one MS SQL server 2017 (140) I access one database just fine but not the other

when the connection string points to First database the intellisence works just fine and brings back a list of objects in the Dbo. Schema and allows me to get results of any query.

When switching to the other database the intellisence works for ctx.functions, ctx.procedures just fine, when trying ctx.Dbo. it displays (red circle) and won’t go any further?

Exit VS and same error, the error is pages long so too big to post here. What is going on, credentials are good and I have compared all of the database properties they look the same. And besides it’s getting some metadata from the other db. Any thoughts anyone? Thanks in advance

let [<Literal>] Conn_str = @"Data Source=MYCOdbServer;Initial Catalog=MyCompDB1;Integrated Security=SSPI; User ID=validid;Password=validpass; Connection Timeout=900"

type dbProv = SqlDataProvider<ConnectionString = Conn_str, DatabaseVendor = Common.DatabaseProviderTypes.MSSQLSERVER>

let ctx = dbProv.GetDataContext()

let test_func_connect =

    query { for x in ctx.Dbo.DimDate do

                    select x }

[Thorium]

Hmm, the stack trace in stackoverflow doesn't seem at all familiar. You listed using SQLProvider 1.1.16. Are you using so old version, or did you mean 1.1.61?

[yetanotherlogonfail]

Tuomas

yes thanks 1.1.61

On Fri, 12 Apr 2019 at 14:41, Tuomas Hietanen [email protected] wrote:

Hmm, the stack trace in stackoverflow doesn't seem at all familiar. You listed using SQLProvider 1.1.16. Are you using so old version, or did you mean 1.1.61?

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/fsprojects/SQLProvider/issues/610#issuecomment-482578785, or mute the thread https://github.com/notifications/unsubscribe-auth/AGIVWkh1bofUFlnne8_rXxJeKTEvFB_4ks5vgI0PgaJpZM4csBWW .

-- Regards, Carl

"Chance has put in our way a most singular and whimsical problem, and its solution is its own reward."

[Thorium]

Are you able to login to the SQL Sever with the user account you are using and run the following queries:

select TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE from INFORMATION_SCHEMA.TABLES

and this by replacing 'dbo' with your schema:

SELECT c.COLUMN_NAME,c.DATA_TYPE, c.character_maximum_length, c.numeric_precision, c.is_nullable
            ,CASE WHEN pk.COLUMN_NAME IS NOT NULL THEN 'PRIMARY KEY' ELSE '' END AS KeyType
            ,COLUMNPROPERTY(OBJECT_ID(c.TABLE_SCHEMA + '.' + c.TABLE_NAME), c.COLUMN_NAME, 'IsIdentity') AS IsIdentity, 
            case when COLUMN_DEFAULT is not null then 1 else 0 end as HasDefault
FROM INFORMATION_SCHEMA.COLUMNS c
LEFT JOIN (
            SELECT ku.TABLE_CATALOG,ku.TABLE_SCHEMA,ku.TABLE_NAME,ku.COLUMN_NAME
            FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
            INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS ku
                ON tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
                AND tc.CONSTRAINT_NAME = ku.CONSTRAINT_NAME
        )   pk
ON  c.TABLE_CATALOG = pk.TABLE_CATALOG
            AND c.TABLE_SCHEMA = pk.TABLE_SCHEMA
            AND c.TABLE_NAME = pk.TABLE_NAME
            AND c.COLUMN_NAME = pk.COLUMN_NAME
WHERE c.TABLE_SCHEMA = 'dbo' 
ORDER BY c.TABLE_SCHEMA,c.TABLE_NAME, c.ORDINAL_POSITION

edit: and this one:

SELECT  KCU1.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME
    ,KCU1.TABLE_NAME AS FK_TABLE_NAME
    ,KCU1.COLUMN_NAME AS FK_COLUMN_NAME
    ,KCU1.ORDINAL_POSITION AS FK_ORDINAL_POSITION
    ,KCU2.CONSTRAINT_NAME AS REFERENCED_CONSTRAINT_NAME
    ,KCU2.TABLE_NAME AS REFERENCED_TABLE_NAME
    ,KCU2.COLUMN_NAME AS REFERENCED_COLUMN_NAME
    ,KCU2.ORDINAL_POSITION AS REFERENCED_ORDINAL_POSITION
    ,KCU1.CONSTRAINT_SCHEMA AS FK_CONSTRAINT_SCHEMA
    ,KCU2.CONSTRAINT_SCHEMA AS PK_CONSTRAINT_SCHEMA
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU1
    ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
    AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
    AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU2
    ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG
    AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA
    AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
    AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION

...just to see that is not a permission issue.

[yetanotherlogonfail]

Tuomas these both run fine

On Fri, 12 Apr 2019 at 18:06, Tuomas Hietanen [email protected] wrote:

Are you able to login to the SQL Sever with the user account you are using and run the following queries:

select TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE from INFORMATION_SCHEMA.TABLES

and this by replacing 'dbo' with your schema:

SELECT c.COLUMN_NAME,c.DATA_TYPE, c.character_maximum_length, c.numeric_precision, c.is_nullable ,CASE WHEN pk.COLUMN_NAME IS NOT NULL THEN 'PRIMARY KEY' ELSE '' END AS KeyType ,COLUMNPROPERTY(OBJECT_ID(c.TABLE_SCHEMA + '.' + c.TABLE_NAME), c.COLUMN_NAME, 'IsIdentity') AS IsIdentity, case when COLUMN_DEFAULT is not null then 1 else 0 end as HasDefaultFROM INFORMATION_SCHEMA.COLUMNS cLEFT JOIN ( SELECT ku.TABLE_CATALOG,ku.TABLE_SCHEMA,ku.TABLE_NAME,ku.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS ku ON tc.CONSTRAINT_TYPE = 'PRIMARY KEY' AND tc.CONSTRAINT_NAME = ku.CONSTRAINT_NAME ) pkON c.TABLE_CATALOG = pk.TABLE_CATALOG AND c.TABLE_SCHEMA = pk.TABLE_SCHEMA AND c.TABLE_NAME = pk.TABLE_NAME AND c.COLUMN_NAME = pk.COLUMN_NAMEWHERE c.TABLE_SCHEMA = 'dbo' ORDER BY c.TABLE_SCHEMA,c.TABLE_NAME, c.ORDINAL_POSITION

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/fsprojects/SQLProvider/issues/610#issuecomment-482650446, or mute the thread https://github.com/notifications/unsubscribe-auth/AGIVWswhNNuMMIkX15rZ57sn_7fG0veGks5vgL0ZgaJpZM4csBWW .

-- Regards, Carl

"Chance has put in our way a most singular and whimsical problem, and its solution is its own reward."

[Thorium]

So those are the queries SQLProvider does behind the scenes to get the schema. So now we know it isn't the database permission, if the connection user is the same. And you said restarting VS doesn't help. Is that the full stacktrace there?

A few other questions:

• Have you added a reference to System.Data to your project? (You should...)
• What OS?
• Are you running on full .NET-framework or .NET core / Standard?

[yetanotherlogonfail]

Hi Tuomas

full stack trace, didn't want to publish all of it here. https://pastebin.com/bKNpKmTJ

System.Data - Yes its there Windows 10 .NetCore v2.1

BR

On Mon, 15 Apr 2019 at 14:11, Tuomas Hietanen [email protected] wrote:

So those are the queries SQLProvider does behind the scenes to get the schema. So now we know it isn't the database permission, if the connection user is the same. And you said restarting VS doesn't help. Is that the full stacktrace there?

A few other questions:

• Have you added a reference to System.Data to your project? (You should...)
• What OS?
• Are you running on full .NET-framework or .NET core / Standard?

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/fsprojects/SQLProvider/issues/610#issuecomment-483245236, or mute the thread https://github.com/notifications/unsubscribe-auth/AGIVWqzWcLMvj8IXe38H-5PH-xS99N2mks5vhHqRgaJpZM4csBWW .

-- Regards, Carl

"Chance has put in our way a most singular and whimsical problem, and its solution is its own reward."

[Thorium]

The only line related to SQLProvider is coming from ProvidedTypes.fs file which is directly copied from FSharp.TypeProviders.SDK :-(