none
sqlmetal to generate code for linked server (Sql Server) RRS feed

  • Question

  • I get an error on trying to get C# LINQ code autogenerated--when I ask sqlmetal to generate the code for a linked server in Sql Server 2008 (it is linked to a jet mdb file).  The error is "cannot open database (name) requested by the login.  The login failed."  I don't have any problems with sqlmetal against a native (non-linked) database in Sql Server 2008, and i can access and browse the tables in the linked server from inside sql server 2008 without problems.    

     

    Any suggestions?  I thought the database parameter in sqlmetal might require special syntax for connection to a linked server (rather than just the "linked server" name in sql server 2008), but if so, I don't know what the parameter would be.  Sql Server is running on the local machine with integrated (Windows) security. 

     

    Thanks.

     

    Monday, May 26, 2008 10:23 PM

Answers

  • Thanks.  As far as I've been able to find out, the closest workaround is to build views or sprocs in the SQL database that make reference to the linked server's tables, and have sqlmetal generate code for sprocs and views (but that obviously is not ideal, extra work and an added indirection).  (caveat: I haven't tested how well that works yet, so it might have other problems, but it does produce the designer code for the views & sprocs.)   Of course, it would be good if there should be a basic sqlmetal-type generator for jet and Linq to jet, or even a more general one against odbc or oledb sources--to be able to keep the same strongly typed linq syntax on alternate relational data sources.    

     

    I also get this error when using the /sproc switch with sqlmetal against Sql Server 2008 feb ctp:

    Warning : SQM1014: Unable to extract stored procedure 'dbo.sp_upgraddiagrams' from SqlServer. Invalid object name 'dbo.dtproperties'.

     

    Saturday, June 7, 2008 4:36 AM
  • It looks like views is the only way to go at this time - I also tried one of the template-driven third-party tools that has LINQ to SQL templates but it also like many apps just wants to connect to a standard database and not a linked one.

     

    There is nothing stopping somebody creating a LINQ provider for Jet or other specific OLEDB providers however hooking it onto LINQ to SQL would not be as easy as it sounds given that LINQ to SQL uses it's knowledge of SQL Server to generate clean and optimized queries.

     

    [)amien

    Monday, June 9, 2008 5:18 PM
    Moderator

All replies

  • SQLMetal is interpreting the database parameter as a reference to a local database -- it doesn't query the remote (linked) server.

     

    Thanks,

     

    --Samir

     

    Wednesday, May 28, 2008 5:17 PM
  • Thanks. How should I fix the problem?  How should I pass in the database parameter? 

    btw, it is a local database (I mean the server is (local): it's all on one pc), but the database is a linked server because it is not a native sql 2008 database--it is linked to a jet mdb. 

     

    Or is there no way to use Linq to Sql for any sql server databases that are listed as linked servers?

     

     

     

    Thursday, May 29, 2008 3:27 AM
  • Should I assume that it is NOT possible to use sqlmetal to generate code for databases in Sql Server that are accessed as "linked servers" (linked to an mdb/jet database)?  Thanks.

     

     TechVsLife2 wrote:

    Thanks. How should I fix the problem?  How should I pass in the database parameter? 

    btw, it is a local database (I mean the server is (local): it's all on one pc), but the database is a linked server because it is not a native sql 2008 database--it is linked to a jet mdb. 

     

    Or is there no way to use Linq to Sql for any sql server databases that are listed as linked servers?

     

    Monday, June 2, 2008 9:25 PM
  • That would be appear to be the case - I am trying to see if there is a usable workaround.

    [)amien
    Friday, June 6, 2008 8:43 AM
    Moderator
  • Thanks.  As far as I've been able to find out, the closest workaround is to build views or sprocs in the SQL database that make reference to the linked server's tables, and have sqlmetal generate code for sprocs and views (but that obviously is not ideal, extra work and an added indirection).  (caveat: I haven't tested how well that works yet, so it might have other problems, but it does produce the designer code for the views & sprocs.)   Of course, it would be good if there should be a basic sqlmetal-type generator for jet and Linq to jet, or even a more general one against odbc or oledb sources--to be able to keep the same strongly typed linq syntax on alternate relational data sources.    

     

    I also get this error when using the /sproc switch with sqlmetal against Sql Server 2008 feb ctp:

    Warning : SQM1014: Unable to extract stored procedure 'dbo.sp_upgraddiagrams' from SqlServer. Invalid object name 'dbo.dtproperties'.

     

    Saturday, June 7, 2008 4:36 AM
  • It looks like views is the only way to go at this time - I also tried one of the template-driven third-party tools that has LINQ to SQL templates but it also like many apps just wants to connect to a standard database and not a linked one.

     

    There is nothing stopping somebody creating a LINQ provider for Jet or other specific OLEDB providers however hooking it onto LINQ to SQL would not be as easy as it sounds given that LINQ to SQL uses it's knowledge of SQL Server to generate clean and optimized queries.

     

    [)amien

    Monday, June 9, 2008 5:18 PM
    Moderator