Monday, May 19, 2014

SqlDataConnection and wasted time

Today I was trying to connect to an Azure SQL-database using F# type providers. The guide given here is great, but does not mention how to get the connection string to a database from Azure. There are some strings given inside the Azure portal, but none of them seem to match the format suggested in the guide. I spend a long time trying to construct a connection string and it just doesn't work. The connection works fine if I only specify the server, but when I also specify the database I get errors like:

Warning : SQM1012: Unable to extract table 'dbo.Board' from SqlServer. Invalid object name 'syscomments'

The table "dbo.Board" exists in the database, so somehow the connection is pointing to the right place. My code looks like this:

open System
open System.Data
open System.Data.Linq
open Microsoft.FSharp.Data.TypeProviders
open Microsoft.FSharp.Linq
 

type dbSchema = SqlDataConnection<"Server=tcp:k9qd5a64zq.database.windows.net,1433;Database=timetobeat562;User ID=johanvts@k9qd5a64zq;Password=MYPASSWORD;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;">

let db = dbSchema.GetDataContext()
db.DataContext.Log <- System.Console.Out

printfn "%s" db.DataContext.Connection.DataSource

[]
let main argv =
    printfn "%A" argv
    System.Console.ReadLine() |> ignore
    0 // return an integer exit code

Any help would be greatly appriciated.

3 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. I had a coworker express a similar problem with this and the SqlDataConnection type provider. I don't recall whether he ever go that working or not. However, we typically use the SqlCommandProvider (https://github.com/fsprojects/FSharp.Data.SqlClient). This let's you just write the SQL you want and type checks its validity, generating types for you. It has worked great with Azure SQL Database. Another you might try is the SQLProvider (https://github.com/fsprojects/SQLProvider), which is closer in concept to the SqlEntityConnection provider.

    ReplyDelete
  3. Looks like you don't have permission to access the metadata table syscomments (or it doesn't exist). I haven't used Azure unfortunately but you might try a simple select * from syscomments as that same account to verify if you can see the metadata. If you can't, the connector is going to have a hard time working out all the types.

    ReplyDelete