LINQ to SQL + SQL Server 7

[Dmitry Perets]

Hello,

I am trying to work with MS SQL Server 7 from the release version of
Visual Studio 2008 + LINQ to SQL. And the problem is that the LINQ to
SQL designer doesn't accept my tables saying that my connection
provider is unsupported. Then I found out that LINQ to SQL officially
supports only .NET Provider for SQL Server. The problem is that SQL
Server 7 can't work with this provider, so I have to use the .NET
Provider for OLE DB. Well, at least, ServerExplorer can't connect to
SQL Server 7 using .NET Provider for SQL Server...

Now the interesting thing is that this is a designer's problem only!
Actually LINQ to SQL do can communicate with SQL Server 7 without any
problems, including inserting data. I've implemented my data context
without using the designer, as follows:

class MyDataContext : System.Data.Linq.DataContext
{
public System.Data.Linq.Table<CUSTOMER> CUSTOMERs;

[System.Diagnostics.DebuggerNonUserCodeAttribute()]
public MyDataContext() :
base(new System.Data.SqlClient.SqlConnection("Data
Source=10.1.1.1;Persist Security Info=True;Password=111;User
ID=111;Initial Catalog=DEVELOP_DEKEL_PUBLISH"))
{
}
}

Please note that I am using here System.Data.SqlClient.SqlConnection.
And this works excellent.
The only problem is that I do that manually, I can't use the
designer...

So, ServerExplorer can only connect to the db using .NET Provider for
OLE DB while LINQ itself can use the SQL Provider without any
problems. And therefore the only thing that I need to do is to
convince the LINQ designer that it actually CAN handle my db =)

Can anybody offer some idea?

 

[Marc Gravell]

I really don't recommend this. Actually, LINQ-to-SQL has a tough
enough time with SQL Server 2000 that I can't even advocate using it
on anything less that SQL Server 2005; the lack of "common table
expressions" means that you can break it easily if you do a "skip" or
"take" on a fairly trivial composite query; I posted some examples
(along with the generated SQL from 2000 and 2005) towards the end of
last year.

Even if you get it "just about" working, I strongly suspect that it
will be brittle, and you will keep hitting more and more problems as
you develop.

But isn't SQL Server 7 officially obsolete? I haven't checked, but I
would have thought it dead by now... I don't think it is unreasonable
to have a cutoff point to say: we're not going to even *try* and make
it work on this - especially with the language differences.

There are other LINQ providers that *might* work; I've never used
them, so not a direct recommendation...

LLBLGen is meant to have LINQ now or soon; DbLinq is another, but from
discussions here, I'm not sure that it is very robust yet... not that
I'd be a big fan either way (unless it did something *demonstrably*
better than LINQ-to-SQL or "EF", what is the point?)

Marc

 

[Dmitry Perets]

Hi,

Thank you for your reply. Of course, you are right, SQL Server 7 is
extremely obsolete. Unfortunately, legacy issues still exist =( And we
actually have two options: we can work with typed datasets or we can
try to work with LINQ to SQL. Well, for now I prefer the latter.
Anyway, if there are some horrible problems we can always switch to
the typed datasets. The application is multi-tier of course, so this
change will be local and minimal...

 

[Frans Bouma [C# MVP]]

Hello,

I am trying to work with MS SQL Server 7 from the release version of
Visual Studio 2008 + LINQ to SQL. And the problem is that the LINQ to
SQL designer doesn't accept my tables saying that my connection
provider is unsupported. Then I found out that LINQ to SQL officially
supports only .NET Provider for SQL Server. The problem is that SQL
Server 7 can't work with this provider, so I have to use the .NET
Provider for OLE DB. Well, at least, ServerExplorer can't connect to
SQL Server 7 using .NET Provider for SQL Server...

SqlServer 7 has a problem in the stored procedure meta data retrieval
area: it lacks certain INFORMATION_SCHEMA views. With OleDb this
meta-data is still retrievable.

It's a couple of lines of code (100 or so). I find it a bit surprising
MS couldn't find the time to write that code for sqlserver7 users.

Now the interesting thing is that this is a designer's problem only!
Actually LINQ to SQL do can communicate with SQL Server 7 without any
problems, including inserting data. I've implemented my data context
without using the designer, as follows:

class MyDataContext : System.Data.Linq.DataContext
{
public System.Data.Linq.Table<CUSTOMER> CUSTOMERs;

[System.Diagnostics.DebuggerNonUserCodeAttribute()]
public MyDataContext() :
base(new System.Data.SqlClient.SqlConnection("Data
Source=10.1.1.1;Persist Security Info=True;Password=111;User
ID=111;Initial Catalog=DEVELOP_DEKEL_PUBLISH"))
{
}
}

of course Sqlserver 7 and 2000 don't differ that much in general
usage of data with CRUD SQL only.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[Frans Bouma [C# MVP]]

I really don't recommend this. Actually, LINQ-to-SQL has a tough
enough time with SQL Server 2000 that I can't even advocate using it
on anything less that SQL Server 2005; the lack of "common table
expressions" means that you can break it easily if you do a "skip" or
"take" on a fairly trivial composite query; I posted some examples
(along with the generated SQL from 2000 and 2005) towards the end of
last year.

general #temp table paging queries are very fast as well in general
scenarios. As long as you setup the tempdb properly (which you should
anyway) it's not a problem. I don't know if Linq to sql does proper
paging at all on db's below 2005.

FB
--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[Dmitry Perets]

DmitryPeretswrote:

Hello,

I am trying to work with MS SQL Server 7 from the release version of
Visual Studio 2008 + LINQ to SQL. And the problem is that the LINQ to
SQL designer doesn't accept my tables saying that my connection
provider is unsupported. Then I found out that LINQ to SQL officially
supports only .NET Provider for SQL Server. The problem is that SQL
Server 7 can't work with this provider, so I have to use the .NET
Provider for OLE DB. Well, at least, ServerExplorer can't connect to
SQL Server 7 using .NET Provider for SQL Server...

        SqlServer 7 has a problem in the stored procedure meta data retrieval
area: it lacks certain INFORMATION_SCHEMA views. With OleDb this
meta-data is still retrievable.

        It's a couple of lines of code (100 or so). I find it a bit surprising
MS couldn't find the time to write that code for sqlserver7 users.

Now the interesting thing is that this is a designer's problem only!
Actually LINQ to SQL do can communicate with SQL Server 7 without any
problems, including inserting data. I've implemented my data context
without using the designer, as follows:

class MyDataContext : System.Data.Linq.DataContext
{
   public System.Data.Linq.Table<CUSTOMER> CUSTOMERs;

   [System.Diagnostics.DebuggerNonUserCodeAttribute()]
   public MyDataContext() :
      base(new System.Data.SqlClient.SqlConnection("Data
Source=10.1.1.1;Persist Security Info=True;Password=111;User
ID=111;Initial Catalog=DEVELOP_DEKEL_PUBLISH"))
   {
   }
}

        of course Sqlserver 7 and 2000 don't differ that much in general

Frans Bouma, thank you for your explanation. Do you know how I can
make the LINQ designer work then? Because as we see, LINQ actually can
work with SQL Server 7! So the only problem is that designer's
check...
The alternative way is to make the Server Explorer use SQL Server
Provider and not the OLE DB - I don't care about the stored
procedures, I need only tables. But again, the GUI doesn't allow me to
do that saying that SQL Server provider works for SQL Server 2000+.
And we know that this is not true actually...

 

[Frans Bouma [C# MVP]]

DmitryPeretswrote:

Hello,

I am trying to work with MS SQL Server 7 from the release version
of Visual Studio 2008 + LINQ to SQL. And the problem is that the
LINQ to SQL designer doesn't accept my tables saying that my
connection provider is unsupported. Then I found out that LINQ to
SQL officially supports only .NET Provider for SQL Server. The
problem is that SQL Server 7 can't work with this provider, so I
have to use the .NET Provider for OLE DB. Well, at least,
ServerExplorer can't connect to SQL Server 7 using .NET Provider
for SQL Server...

        SqlServer 7 has a problem in the stored procedure meta data
retrieval area: it lacks certain INFORMATION_SCHEMA views. With
OleDb this meta-data is still retrievable.

        It's a couple of lines of code (100 or so). I find it a bit
surprising MS couldn't find the time to write that code for
sqlserver7 users.

Now the interesting thing is that this is a designer's problem
only! Actually LINQ to SQL do can communicate with SQL Server 7
without any problems, including inserting data. I've implemented
my data context without using the designer, as follows:

class MyDataContext : System.Data.Linq.DataContext
{
   public System.Data.Linq.Table<CUSTOMER> CUSTOMERs;

   [System.Diagnostics.DebuggerNonUserCodeAttribute()]
   public MyDataContext() :
      base(new System.Data.SqlClient.SqlConnection("Data
Source=10.1.1.1;Persist Security Info=True;Password=111;User
ID=111;Initial Catalog=DEVELOP_DEKEL_PUBLISH"))
   {
   }
}

        of course Sqlserver 7 and 2000 don't differ that much in
general

Frans Bouma, thank you for your explanation. Do you know how I can
make the LINQ designer work then? Because as we see, LINQ actually can
work with SQL Server 7! So the only problem is that designer's
check...

I have no idea. Microsoft apparently build in some restrictions in
their designer.

So you either should use another o/r mapper or use another database.
One 'workaround' could be that you create your project on a sqlserver
2000/2005/express catalog with the same name/table layout and then use
that at runtime against the sqlserver 7 catalog.

FB


--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------