Friday, March 23, 2012

OpenDataSource Quandary

I am trying to open and query a Microsoft Access "like" database from SQL Server 2000 using OpenDataSource. This will work for a little while, but if I am to update my ASP.NET code base I have to completely reboot the machine for these queries to work again. I also have two reboot the machine periodically because this functionality decides to crap out. I am doing this from a stored procedure. My code looks like this:

DECLARE @.SelectCode varchar(5000)
SET @.SelectCode =
'SELECT CONVERT(varchar, Value) AS UnitName FROM
OPENDATASOURCE(''Microsoft.Jet.OLEDB.4.0'', ''Data Source = "' + @.Path + @.DataFile + '.fcd";User ID=Admin;Password=;'')...Properties WHERE Property LIKE ''UnitName'''

Now, when I am developing on my local machine that uses Windows Authentication, I have absolutely no problems, unless the source file has a schema error.

Any ideas?

Hi jmace,

I've checked your code and didn't find any problems. One doubt here is that what a FCD file format is?(i've searched through google while didn't get enough valuable information).

It sounds to me that you are accessing this stored procedure from code, so, my suggestion is, can you try to execute it from your Management Studio directly and see if your problem still can be reproduced? This will help limit this problem. Also, check if it's the database itself's issue. Again, seems your code is fine.

Hope my suggestion helps


No comments:

Post a Comment