Open yetanotherlogonfail opened 5 years ago
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?
Tuomas
yes thanks 1.1.61
On Fri, 12 Apr 2019 at 14:41, Tuomas Hietanen notifications@github.com 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."
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.
Tuomas these both run fine
On Fri, 12 Apr 2019 at 18:06, Tuomas Hietanen notifications@github.com 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."
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:
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 notifications@github.com 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."
The only line related to SQLProvider is coming from ProvidedTypes.fs file which is directly copied from FSharp.TypeProviders.SDK :-(
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 [] Conn_str = @"Data Source=MYCOdbServer;Initial Catalog=MyCompDB1;Integrated Security=SSPI; User ID=validid;Password=validpass; Connection Timeout=900"
type dbProv = SqlDataProvider
let ctx = dbProv.GetDataContext()
let test_func_connect =