acessing ms-access problem from sql

  
Hi,
 
I am facing problem when I am trying to access ms-access file from sql server 2005.
 
I used below query
 
SELECT lastname,firstname
                   FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                                  'D:\Northwind.mdb';
                                  'admin';'',employees)
 
This query is working fine in the system with windows-xp.
 
However in the system with windows-2003 server R2, Standard x64 Edition, Service Pack2
it is giving the following error
 
“OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.”
 
I found the below link
 
"http://gorm-braarvig.blogspot.com/2005/11/access-database-from-sql-200564.html".
 
Is there any other alternative.
 
Waiting for all suggestion
 
Regards
Ravindra A
 

0
ravindra_satyam
12/3/2008 5:18:40 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

6 Replies
732 Views

Similar Articles

[PageSpeed] 35

Hi  Friend,

There is some problem with 64Bit  edition. I had faced same problem but not get any solution.

I was using excel upload. then i used DTS package for my work.

 

 


Regards,

Avinash

Please don't forget to click "Mark as Answer" on the post that helped you.
0
avinash_vns
12/26/2008 6:39:54 AM

install the OLEDB provider from Windows Server 2003 x64 from the below link

http://www.microsoft.com/downloads/details.aspx?FamilyID=000364db-5e8b-44a8-b9be-ca44d18b059b&displaylang=en


MAKMark as Answer if this reply helps you
MVP ASP/ASP.Net
MVP ASP/ASP.Net
ASP.Net Hosting : Host DepotMy Site : ASPSnippets
0
mudassarkhan
12/26/2008 6:46:20 AM

Hi,

Thanks for the replies

@MAK

I installed the required component it is still giving the same error. Kindly let me know if i have to change the query i used the below query

SELECT lastname,firstname FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'D:\Northwind.mdb'; 'admin';'',employees)

 @Avinash

How to proceed with DTS. Kindly advise

 Waiting for your response

 Regards

Ravindra A

0
ravindra_satyam
1/12/2009 10:46:48 AM

yes query should be this way

Select * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=D:\INER\Downloads\sql\Excel111.xls;Extended Properties=Excel 8.0').


MAKMark as Answer if this reply helps you
MVP ASP/ASP.Net
MVP ASP/ASP.Net
ASP.Net Hosting : Host DepotMy Site : ASPSnippets
0
mudassarkhan
1/12/2009 11:05:10 AM

ravindra_satyam:

@Avinash

How to proceed with DTS. Kindly advise

 Waiting for your response

Hi Ravindra,

Here is the steps to solve your problem :

1) Create a DTS to export data into sql table

2) Run DTS using following Command :

exec master..xp_cmdshell dtsrun /s <servername>, <username>, <password>, <package name>

3) Select your required data from the sql table.

 

If you need more help then tell me.


Regards,

Avinash

Please don't forget to click "Mark as Answer" on the post that helped you.
0
avinash_vns
1/13/2009 5:03:53 AM

Hi ,

 I followed below steps to create DTS(SSIS) package in sql server 2005. 

In SQL Server Management Studio, connect to the Database Engine server type, expand Databases, right-click a DatabaseName, point to Tasks, and then click Import Data  and then follwed the process by selecting microsoft access and then .mdb file and finally saved the package. Also checkd the option to execute immediately.

Everything went fine as the package executed immediately data moved to SQL table and it is saved(as i tried to create a package with same name and it gave warning for package already existing).

 However when i try to execute the package by the command you mentioned it is giving some syntax error.I tried the below command

exec master..xp_cmdshell dtsrun /s 10.133.16.97, sa1,admin,test_wrapper.

  Kindly let me know is this what you want me to try.

 Also i tried to schedule this package so it runs automatically but it gave error "DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER."

Kindly help me in scheduling this package as the data has to be pushed automatically from ms-access to SQL table 

Regards

Ravindra

0
ravindra_satyam
1/20/2009 1:22:52 PM
Reply:

Similar Artilces:

MS Access, MS SQL Express, MS SQL when you should use wich version?
Hi, I'm planning to make a website for a in first part a small user group. There needs to displayed some data in the database.But because the hostings party advices Acces.I like MS SQL Express more, because the size of the visitors is getting very big. The meaning of the project is to make a big website for the complete country. But i can use Access, it works, but does anyone know till how much visitors you can use access, and MS SQL Express ?Of course MS SQL is very nice, but its also very heavy to get a licence.The website is made for free and we need to run it in spare time, also for...

MS Access SQL migration to MS SQL Server TSQL
got some MS Access SQL Code that needs converting into TSQL: SELECT dbo_qryMyServices.FormsServiceID, dbo_qryMyServices.ServiceName, Sum(IIf(IsNull([CompletionDate]),0,1)) AS Completed, Count([pkServiceID])-Sum(IIf(IsNull([CompletionDate]),0,1)) AS Uncompleted, Count(dbo_MyServiceRequests.pkServiceID) AS TotalCount FROM dbo_qryMyServices LEFT JOIN dbo_MyServiceRequests ON dbo_qryMyServices.FormsServiceID = dbo_MyServiceRequests.PostType GROUP BY dbo_qryMyServices.FormsServiceID, dbo_qryMyServices.ServiceName ORDER BY dbo_qryMyServices.ServiceName; because it's Access and got V...

Problem with stored proc, migrating from MS SQL 2000 to MS SQL 2005
I've been migrating some DB from MS SQL 2000 to MS SQL 2005 all works fine but I have one problem with stored procedures. Even if there is actually no errors I get the message "The transaction ended in the trigger. The batch has been aborted.". The record is succesfully deleted but still I get the message above. I know MS SQL 2005 added the try/catch block did they change something else that cause compatibility issues with 2000? Here's a sample : *** DECLARE @Error bit DECLARE @ErrorMsg varchar (100) DECLARE @ContactIDToDelete int SELECT @ContactIDToDelete ...

FYI - Interesting difference between Oracle SQL and MS SQL
While converting some code from T-SQL to PL/SQL, I ran across an interesting bit of MS SQL code.  The code looked odd to me so I did a quick experiment to prove what it did.  I was quite surprised by the MS SQL results! In ORACLE: create table testunion (aaa number); insert into testunionselect 2 from dualunion select aaa * aaa from testunion;  select * from testunion;  -- Returns one record, as it should, "2" delete testunion; insert into testunionselect 2 from dualunion allselect aaa * aaa from testunion; select * from testunion; -- Returns one record, ...

MS SQL 2005 Locking problem with direct SQL
HI all I am using IDM 3.5.1 connecting to a MS 2005 SQL Database with the JDBC 1.2 Third party drivers. On the command trnsform channel I have the following rules. My problem is this. I can do UPDATE and INSERT commands BEFORE the current operation, i.e. I can add a record using the /nds/input/ instead of AFTER the current operation using ../ as in the rules below. However when I use th ../ as below it seems as if the table is being held open after the update (or insert), as you can not access it unles you restart the SQL server service, where the update and insert have...

MS-SQL 7 to MS-SQL 2000
With Enterprise Manager, how can I convert a database from MS-SQL 7 to MS-SQL 2000 Thanks. Do you have Books Online? There is a topic in there called "Upgrading Databases from SQL Server 7.0 (Copy Database Wizard)" which will tell you how to do it. Terri Terri MortonEngagement Manager, NeudesicHow to ask a question Create a staging database to deal with issues before moving to the destination database. We still have some 7.0 and 2000 running. The process is test a lot then deploy. Hope this helps. Kind regards, Gift PeddieKind regards,Gift Peddie thanks guys...

MS access TO SQL SERVER Problem
I always used MS Access Database In my programs, now I want to use SQL SERVER database and so I started to design one. but it’s a little bit confusing ,I don’t know what can I use in change of these access Data type : MS access // SQL SERVER -------------- -------------------- Memo >>>>>>>> ?? AutoNumber >>>>> ?? * and if you think there is some difference between Ms access and SQL in D...

Problem with MS SQL vs. Access database
In my office we have PB with MS SQL 7.0, but would like to put up a home development system with a small Access 97 database, with the same tables. Our Access ODBC driver is version 4.00.3711.08. The two things that seem to be creating a problem are "SubString and Join". Would Access 2000 make a difference. Not real experienced with this program, so I don't know if any of this I'm asking about makes a whole lot of sense. Thanks for your help. Wayne -- _____________________________________________________________ Wayne N. Gelineau, Database ...

converting sql to MS SQL
I'm trying to unload a SQL Anywhere database to a MS SQL 6.5 database. The SQL produced from SQL Anywhere is not at all compatible. Is there an easy way of moving a database from SQLA to MS SQL darrell@texas.net Look at PowerDesigner from Powersoft. I can reverse engineer SQLA and create the MSSQL Server DDL for you. Dave Wolf Sybase Professional Services Darrell Fuller wrote: > I'm trying to unload a SQL Anywhere database to a MS SQL 6.5 database. > The > SQL produced from SQL Anywhere is not at all compatible. Is there an easy > way of moving...

Paging records in SQL Server 2005, SQL Server 2000, MS Access and MySQL
As a Web developer, you should know by now that using the default paging capabilities of ASP.NET Webcontrols like  DataGrid and GridView is NOT recommended, simply because with every roundtrip to the data-server, you get ALL the records ALL of the time. This is fine perhaps for very small databases (the kind, say, a Microsoft programmer would use to develop a new feature), but in the real, ugly world outside, databases tend to have tables with several tenthousands of records. Hence the need for custom paging in a WebControl. Custom paging poses 2 problems : How to get a page of re...

Re: Problems Creating SQL for MS-Access from PDM
When attempting to run the creation VB script generated by PowerDesigner I receive SQL error: "Compile Error: Expected end of statement" My first lines of SQL Code are: Option Compare Database CreateTble C=ADP_CLAIMS N="ADP Claims"; ( What am I doing improperly? Hi, It sounds like you generated a script for ms access from your physical data model. This is not at all a script you can run in vbs window. This is usable with the tool you can find in "Tools" subdirectory of your PowerDesigner installation path. A tools.txt file may help you to find th...

problems migrating from MS Access to SQL Anywhere / ASA
Hello, I was able to migrate MS Access Data to SQL Anywhere using the migration tool but I am having the following problems with an Access front-end form having 3 subforms. The main form is linked to a "parent" table and the 3 subforms are linked to 3 "child" tables: 1. on 2 of the subforms, I can add rows but cannot modify data in those rows after they've been added. I get a message in a message box titled "Write Conflict" saying "This record has been changed by another user since you started editing it. If you save the record, you wil...

MySql Or MS Access or MS Sql
Hi, I want to know, that is MS Access or MySql more efficient and why. And i have a confusion.. is MySql db server a free server or we have to buy it to use. As i think implementing MS Sql server will cost me more, which one of the above is a better solution in that case. Thanks, imran.U and I Solutions,http://www.uandisolutions.com Hi, MySql would be ideal for you since MS Access doesn't provide all the flexibilities of relation database. Mysql can be downloaded from this <a href="http://dev.mysql.com/downloads">Link<a/> Hope it helps. Thanks....

from MS Access to MS SQL help!
hi there, i have a website that runs off of MS access, but i feel its time it is out growing it, so i want to move everything to MS SQL, how can i convert my DB (there is about 18 tables, data is not important) and then can i just switch over my data sources on my pages by adding in a line or two of code or do they all require re doing?   JezJeremy HusonSenior Network EngineerCarden Computers - PC Repair Brighton Give the new Microsoft SQL Server Migration Assistant for Access a spin: http://www.microsoft.com/sql/solutions/migration/default.mspx...

Web resources about - acessing ms-access problem from sql - asp.net.sql-datasource

Resources last updated: 1/8/2016 7:39:40 AM