openquery from excel to sql 2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

openquery from excel to sql 2005

Author

Topic

NickBloodworth
Starting Member

3 Posts

Posted - 2010-02-19 : 10:03:54

Hi,

I'm trying to import 600 records from a customer list in Excel into a SQL table and am having issues.

In SQL server mgnt express I've created a linked server to the excel file and can run queries against it using "select * from openquery(excel, 'select * from [Sheet1$]').

How can I use this to import the data? The customers table and the excel worksheet have the same columns in the same order.

Thanks,
Nick

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-19 : 10:08:06

just use like


insert into openquery(.....)
select columns... from table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-19 : 10:28:18

or try

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

NickBloodworth
Starting Member

3 Posts

Posted - 2010-02-19 : 10:42:35

I get an error when I use it. Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

I'm using SQL express 2005

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-19 : 10:45:43

quote:
Originally posted by NickBloodworth

I get an error when I use it. Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

I'm using SQL express 2005

The error message is descriptive. As suggested go to Surface Area Configuration and enable Ad Hoc Distributed Queries or use sp_configure system sp

http://www.kodyaz.com/articles/enable-Ad-Hoc-Distributed-Queries.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

NickBloodworth
Starting Member

3 Posts

Posted - 2010-02-19 : 11:51:11

Now I enter ...Insert into hELLO_TELECOM.DBO.Customers
Select * FROM OPENquery(excel, 'SELECT customernumber,customername,contactname,address1,address2,address3,address4,postcode,phone1,email FROM [Sheet1$]')
and get an error OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "excel" returned message "No value given for one or more required parameters.".
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "SELECT customernumber,customername,contactname,address1,address2,address3,address4,postcode,phone1,email FROM [Sheet1$]" against OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "excel".

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-19 : 11:54:18

Make sure that the number of columns are same in both the tables and excel

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources