473,326 Members | 2,102 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

LINQ to SQL + SQL Server 7

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<CUSTOMERCUSTOMERs;

[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?
Jun 27 '08 #1
6 1532
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
Jun 27 '08 #2
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...
Jun 27 '08 #3
Dmitry Perets wrote:
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<CUSTOMERCUSTOMERs;

[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#)
------------------------------------------------------------------------
Jun 27 '08 #4
Marc Gravell wrote:
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#)
------------------------------------------------------------------------
Jun 27 '08 #5
On Apr 13, 10:50*am, "Frans Bouma [C# MVP]"
<perseus.usenetNOS...@xs4all.nlwrote:
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<CUSTOMERCUSTOMERs;
* *[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...
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#)
------------------------------------------------------------------------- Hide quoted text -

- Show quoted text -
Jun 27 '08 #6
Dmitry Perets wrote:
On Apr 13, 10:50*am, "Frans Bouma [C# MVP]"
<perseus.usenetNOS...@xs4all.nlwrote:
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<CUSTOMERCUSTOMERs;
* *[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#)
------------------------------------------------------------------------
Jun 27 '08 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Dave Johnson | last post by:
Greetings, i want to be able to use linq new technology with sql server. the senario i am not able to do so far is as follow: 1- i program with linq 2- be able to generate and manipulate...
7
by: Ronald S. Cook | last post by:
I've always been taught that stored procedures are better than writing SQL in client code for a number of reasons: - runs faster as is compiled and lives on the database server - is the more...
4
by: =?Utf-8?B?V2lsc29uIEMuSy4gTmc=?= | last post by:
Hi Experts, I am doing a prototype of providing data access (read, write & search) through Web Service. We observed that the data storing in SQL Server 2005, the memory size is always within...
4
by: cj | last post by:
Can I use LINQ in VB .Net 2008 to get to a Visual FoxPro Table? I've seen demos of it with SQL Server but they've used it with a server browser and I don't think that'd work when I need to see VFP...
2
by: Andy B | last post by:
Hi... I have an sql server 2005 database that has a table called TestXml in it. The table has the columns ID (identity, int) and MyXml(xml). I created a stored proc on the server to insert a row...
2
by: =?Utf-8?B?UGFvbG8=?= | last post by:
Learning C# and SQL Server. Just wondered what are the pros and cons of either approach to using SQL Server databases. (It may be 'how long is a piece of string'!) Thanks
9
by: Cirene | last post by:
I'm about to begin a brand new, big, ASP.NET project (using 3.5 .net fw), VS 2008. I'm using MySQL as the backend (customer request.) I have absolutely no experience with LINQ and/or the Entity...
3
by: bob laughland | last post by:
Hi All, I am using a combination of LINQ to SQL and bulk insert. In the process of performing 'one unit of work' I will be doing things like reading, and deleting records using LINQ to SQL and...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.