SQL Server - Server64-bit OPENROWSET csv

Asked By Madiso

15-May-08 11:23 AM

Hi there,
We just moved SQL Server 2005 to run with Server 64-bit this weekend after
that the stored-procedure to insert data from csv file is not working any
more. This stored procedure was working fine in the server 32-bit. Here is
the coding

INSERT INTO Report_MISO
SELECT  *
FROM OPENROWSET ('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=\\myServer\Program Files\Microsoft SQL Server\MSSQL\Jobs\;
Extensions=csv;',  'SELECT * FROM myReport.csv')

We checked Ad Hoc Distributed Queries has 0 1 1 1 values. We can not compile
the stored procedure any more we got the message said

OLE DB provider "MSDASQL" for linked server "(null)" returned message
driver specified".

Msg 7303, Level 16, State 1, Procedure p_MISO_save_data, Line 31
Cannot initialize the data source object of OLE DB provider "MSDASQL" for
linked server "(null)".

Any ideas?

Thanks.

15-May-08 01:43 PM

Madison,

Sorry if this is an obvious question:  Did you get the 64-bit MSDASQL driver
or are you still running the 32-bit version?
http://blogs.msdn.com/data/archive/2008/04/07/64-bit-oledb-provider-for-odbc-msdasql-is-now-available-for-windows-server-2003.aspx

A 64-bit SQL Server needs to talk to a 64-bit driver.

RLF

15-May-08 03:38 PM

Thank you for your reply.
I asked our database admin about 64-bit MSDASQL driver. He did not know any
thing about it. I changed my code not to use OPENROWSET and 'MSDASQL'. I
create temporary table and BULK insert to temp table and insert data from
temp table to real table. I think is not fair that Microsoft release
something with incompleted. We spent a lot of time try to figure when move to
64-bit server.
Thanks again for you help.

15-May-08 05:36 PM

Madison,

Sorry that you had this frustrating problem.  My experience is that 64-bit
works fine as long as everything being used is inside SQL Server.  But when
it goes outside the SQL Server, such as your MSDASQL driver, is where the
problem lies.

Other things to look out for include making sure that full-text indexing
iFilters are 64-bit or they will not work.  Any SQL Agent job steps that are
dependent on 32-bit cscript or vbscript needs should specifically address
the C:\WINDOWS\SysWOW64 folder containing 32-but EXEs instead of the default
System32 (which on a 64-bit server has the 64-bit EXEs).

RLF

15-May-08 06:06 PM

Russell,

Thank you very much for your advise.  Yes, we have a lot of work to go after
this one. All our stored procedures for sending email need to change. We need
to set IIS to run with 32-bit.

Madison.

16-May-08 01:27 AM

Hi Madison,
I would like to add some comments to explain the reason for you. Actually
MSDASQL is a data access component shipped with Windows operating system.
When SQL Server 2005 X64 RTM was released, even SQL Server 2005 SP2, the
64-bit OLEDB Provider for ODBC (MSDASQL ) had not been formally produced in
the latest Windows operating system. Windows Server 2008 & Windows Vista
SP1 are the first Windows releases to include a 64-bit version of the
technology. Then after some time, 64-bit MSDASQL for Windows Server 2003
64-bit SP1/SP2 was officially produced.

If you open SQL Server Management Studio, expand Server Objects->Linked
Servers->Providers, you will find that MSDASQL is not listed in a SQL
Server 2005 (x64) instance, while it exits in a SQL Server 2005 (x86)
instance.

Before the 64-bit provider released, to work around this issue, we often
recommend that you create a linked server to a 32-bit SQL Server instance
and then create a stored procedure in the 32-bit SQL Server instance to
query the plain-text or .csv file.

I had seen that 64-bit MSDASQL provider was produced, however I have not
had a time to try it before until I saw your post. To my surprise, I
reproduced your issue even I installed the 64-bit Provider:
64-Bit OLEDB Provider for ODBC (MSDASQL)
http://www.microsoft.com/downloads/details.aspx?FamilyID=000364db-5e8b-44a8-
b9be-ca44d18b059b&displaylang=en

Now I am trying to contact our product team to see why it does not work for
SQL Server 2005 64-bit edition. The process may need a little long time. I
appreciate your patience. If this issue is urgent to your business, I
recommend that you try the workaround mentioned above. Once I got our
product team's response, I will post back immediately. If you have any
other questions or concerns, you can also directly send me
(changliw_at_microsoft_dot_com) an email.

Have a nice day!

Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@microsoft.com.
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

16-May-08 11:38 AM

Charles,

Thank you very much for your reply.

I changed my coding from using OpenRowSet to create temporary table and Bulk
insert then Insert to the real table later. Thank you again for your concern.

Madison.

21-May-08 08:15 AM

Hi Madison,
I am very glad to see you found the workaround by yourself.
Regarding 64-bit OLEDB Provider for ODBC (MSDASQL), I just get the
confirmation from product team. The reason why it did not work is because
that connectivity to csv file requires a 64-bit asam odbc driver (in
addition to msdasql) and that currently there is no 64-bit Microsoft Text
Driver released yet. Our product team told me that they planned to produce
a KB article to clarify this issue in the near future.

Please feel free to let us know if you have any other questions or
concerns. Have a nice day!

Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@microsoft.com.
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

21-May-08 09:18 AM

Charles,

Thank you very much for your follow up with the issue. I am glad that MS will
produce  KB article.

Madison.

14-Jun-08 11:45 AM

Thanks for the info, product team. I know you guys are working hard, and you have created some AWESOME software! So, please take what I say below in kind, and with no malice.



Unfortunately, we've too have been hit by this "sorry, you developed in 32-bit and deployed to a 64-bit server" issue, and the documentation from Microsoft has been lackluster at best.



I have a full deployment that works great on all the 32-bit systems we have, but a common format for getting data, CSV, appears to be a low priority issue at Microsoft.



If you're going to convince all of us to move to your 64-bit OSes, since you're dropping support for all 32-bit OSes as of Server 2008 R2, you had better make sure all the technologies that worked in the 32-bit versions work on the 64-bit side. Or, well, repeat the Windows Vista / Windows XP upgrade mess...



Best,



Auri Rahimzadeh

Engineer, Author

Contributor, Consultants Corner, ASP.NET Pro Magazine

15-Jun-10 10:51 PM

Hi all,



Wondering if anyone has found the KB mentioned in 2008 as "not far away" and could post it here.



I hope to find the MSDASQL driver for Windows server 2008, so far I can only find it for 2003.



Any ideas?



Thanks



C