Hi,
I am in the process of upgrading from SQL 2000 on Win Server 2000 to SQL 2008 R2 Express on a Win Server 2008 R2. I did a backup and restore of the Data and Stored procedures and I am now trying to run Crystal Reports against the server.

I am running the crystal reports from a Windows 2003 server that uses an ODBC connection into the 2008 database, I am using the 2008 native ODBC Client.

At first the error message was I don’t have the rights to execute a stored procedure. So I gave my self the rights.

Then it said that I could not run Ad hoc access to OLE DB provider has been denied, so I added the DWORD DisallowAdhocAccess with value of 0. (see link below)

I found the below on the internet with a guy that had a similar problem to me do you know what they are talking about regarding the TCP/IP provider?

From the answer by Christof Wollenhaupt on a similar question

----------------------------------

yes, VFP 9 runs on a 64 bit system. There are a few things you need to be aware of, though:

- The ODBC driver cannot access a local MS SQL server using the memory provider. You need to select the TCP/IP provider if server and app run on the same system.

- The 32 bit versions of all tools are in C:\Windows\SysWoW64 whereas the 64 bit versions are in C:\Windows\System32. Don't know who came up with these absurd names. When you launch tools such as REGEDIT, the ODBC Panel, etc. from Explorer, you always end up with the 64 bit version. Same happens when you double click a REG file, for instance. For 32 bit applications you have to run the EXE right from the SysWoW64 directory. That's more of a support issue, though.

I don't know all that much about databases so forgive me if I sound a bit thick but I installed the SQL Native Client 10.0 on the server and I can access the data using the ODBC link I created but I just can't run the stored procedures.

The stored procedures were written for sql 2000 and I did a backup and restore to transfer them to the SQL 2008 R2 database.

Also one thing to note is that the stored procedures are pulling data from a FoxPro Database through an ODBC connection which I have test by ruining a crystal report with just FoxPro data being accessed and it worked.

Obviously a problem with the ODBC driver at some point but seems like you have 2 to me.. 1 from Crystal to DB and another from DB to FoxPro? You could turn on ODBC Trace from the ODBC Data Source Administrator and take a look at the log it produces.

A linked server can mean any external (to SQL Server) data source even if it resides on the same machine. It depends on how your stored procs are designed to access the data. SELECT * FROM [Server\Instance].Northwind.dbo.Pubs would indicate a linked server would need to exist. Can you check on your SQL 2000 if there were linked servers?

A linked server can mean any external (to SQL Server) data source even if it resides on the same machine. It depends on how your stored procs are designed to access the data. SELECT * FROM [Server\Instance].Northwind.dbo.Pubs would indicate a linked server would need to exist. Can you check on your SQL 2000 if there were linked servers?

I justed checked and there are linked servers on the sql 2000 to a FoxPro database stored on that machine locally.

I tried to re-create it on the 2008 server but it have not got the option for FoxPro on the drop down menu. See attached

A linked server can mean any external (to SQL Server) data source even if it resides on the same machine. It depends on how your stored procs are designed to access the data. SELECT * FROM [Server\Instance].Northwind.dbo.Pubs would indicate a linked server would need to exist. Can you check on your SQL 2000 if there were linked servers?

I justed checked and there are linked servers on the sql 2000 to a FoxPro database stored on that machine locally.

I tried to re-create it on the 2008 server but it have not got the option for FoxPro on the drop down menu. See attached

I would immagine you chose not to install SQL Server Integration Services? I believe the driver is included in that.

My apologies that doesn't state Windows Server 2008. Is it 32 bit or 64 bit by the way? You need to install the provider on the machine, this is normally achieved via a download as before, MDAC (doesn't include FoxPro) or installing FoxPro on the server (or if there is a Data Access package that may do it). Did your Windows 2000 box have FoxPro installed on it?

The 2008 win serv is 64bit and the database 2008 r2 is also 64bit. The win 2000 box had the MDAC driver on it but the FoxPro was open tables so I don't think it actually had to have the whole program installed.

I've only had chance to have a quick look last night but I couldn't easily find a 64bit OLE DB provider for FoxPro which doesn't bode well.

You may be able to create an ODBC DSN and then create the linked server using the Microsoft OLE DB Provider for ODBC (I'm not sure if this picks up 32bit DSNs created by ODBCAD32 from the Windows\SysWOW64 direcotry, you may need to Google or just try it).

I think this is where you hit a problem... It would appear that FoxPro doesn't have 64bit drivers. 64bit systems can use the 32bit ODBC drivers by setting them up using ODBCAD32 but it would appear that you cannot create linked servers on them, using SQL Server 64bit.

I found the below on the internet with a guy that had a similar problem to me do you know what they are talking about regarding the TCP/IP provider?

From the answer by Christof Wollenhaupt on a similar question

----------------------------------

yes, VFP 9 runs on a 64 bit system. There are a few things you need to be aware of, though:

- The ODBC driver cannot access a local MS SQL server using the memory provider. You need to select the TCP/IP provider if server and app run on the same system.

- The 32 bit versions of all tools are in C:\Windows\SysWoW64 whereas the 64 bit versions are in C:\Windows\System32. Don't know who came up with these absurd names. When you launch tools such as REGEDIT, the ODBC Panel, etc. from Explorer, you always end up with the 64 bit version. Same happens when you double click a REG file, for instance. For 32 bit applications you have to run the EXE right from the SysWoW64 directory. That's more of a support issue, though.