LINQ to SQL Server 2000: "Must declare variable '@p0'" - 【StackMirror】|linq|sql-server-2000

I've created this simple LINQ query:

var result = from invoice in invoiceTable
             where invoice.Id == 1
             select invoice.Document;

It generates this SQL:

SELECT [t0].[Document]
FROM [Invoice] AS [t0]
WHERE [t0].[Id] = @p0

Whenever I run it though I get this error:

(ODBC)

ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the variable '@p0'.

(OleDb)

Must declare the variable '@p0'.

I remember reading that OleDb and ADO.NET does not support named parameters on SQL Server 2000. Is this problem related to that issue, or am I doing something else wrong?

UPDATE 1:

This does seem to be a provider issue. I tried the same query against SQL Server 2008 using the SQL Client data provider and it worked fine. Unfortunately, this provider does not work with SQL Server 2000.

When I tried using the ODBC and OLEDB providers with SQL Server 2008 I got the same errors.

Does anyone know of a suitable workaround?

UPDATE 2:

It turns out the SQL Client provider does work with SQL Server 2000. Just not from within Visual Studio 2010. I changed the provider and the query works now.

2012-04-04 17:41
by ilitirit

When you say "run it through" - you mean executing the actual query, or trying the generated SQL through a profiler - Jon Skeet 2012-04-04 18:00

Are you using Linq to Sql or Entity Framework - Phil 2012-04-04 18:14

@JonSkeet I'm executing the actual query ("though" not "through"). The SQL I pasted is what shows up in the profiler, and the debugger when I try to evaluate the result - ilitirit 2012-04-04 19:38

@Phil I'm using Linq to SQL - ilitirit 2012-04-04 19:39

It turns out the SQL Client provider does work with SQL Server 2000. Just not from within Visual Studio 2010. I changed the provider and the query works now.

2012-04-05 09:57
by ilitirit

-3

If you are using an ODBC provider, it will not work. Change to SqlClient.

2014-09-11 20:08
by gpdeveloper