How to transfer the data from Excel to SQL2005

Question

How to transfer the data from Excel to SQL2005

josephptran2002

Hall of Fame

Points: 3275
More actions
May 18, 2009 at 12:00 pm

#200649

Hi All,
I wrote this code and hope that I can transfer the data of one column field name in Excel to another column field name of SQL 2005 (of course, the column field name in SQL must be matched with column field name of Excel file).
Insert into ROCAPData
Select SocialSecurityNumber
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','DataSource=P:\TranAssignments\ROCAPData\ROCAP.xls;Extended Properties="Excel 8.0;IMEX=1" ')...Sheet1$
it gives me a syntax error:
"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."
Then I search for the hints of why I got the error message, I found one answer and that answer is to:
Go to Start Menu, Point to all program, Microsoft SQL Server 2005, Configuration Tools, and then Click SQL Server Surface Area Configuration. Click on Surface Area Configuration for Features, Then Turn on the check box Displayed : "Enable OPENROWSET and OPENDATASOURCE support" , Click OK.
After done the check box as it stated above, I run my query again but It still gives me the same error :
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 restarted the computer and re-run my query, but it still gives the same syntax error.
Does anyone know why I could not transfer the data of one column of Excel file to SQL Database? Please look at my code or else to tell me what wrong with my codes or alternative way to solve this issue.
Thanks

Viewing 15 posts - 1 through 15 (of 49 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95409 More actions · Answer 1

josephptran2002 (5/18/2009)
Hi All,
it gives me a syntax error:
"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.

see if this helps you out:

execute sp_configure 'show advanced options', 1

reconfigure

execute sp_configure 'ad hoc distributed queries', 1

reconfigure

execute sp_configure 'show advanced options', 1

reconfigure

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

vick.ram79 SSCommitted Points: 1637 More actions · Answer 2

You want to do this via script only?

I normally do this via DTS menu in SQL 2005....

How To Post[/url]

josephptran2002 Hall of Fame Points: 3275 More actions · Answer 3

Hi There,

Thanks, I did it and run the same codes that you suggested me to do

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'Ole Automation Procedures', 1;

GO

RECONFIGURE;

GO

it still gives me the same error.

Thanks

josephptran2002 Hall of Fame Points: 3275 More actions · Answer 4

josephptran2002

Hall of Fame

Points: 3275

May 18, 2009 at 12:35 pm

#996562

Hi There,

Can you show your way?

Thanks

josephptran2002 Hall of Fame Points: 3275 More actions · Answer 5

I tried to copy your codes and run it, somehow it works...but it gives me a small error. Do you know why?

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Could not find installable ISAM.".

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Thanks

SmartDBA Valued Member Points: 50 More actions · Answer 6

Check this article http://www.sql-server-performance.com/articles/biz/How_to_Import_Data_From_Excel_2007_p1.aspx

WayneS SSC Guru Points: 95409 More actions · Answer 7

josephptran2002 (5/18/2009)
I tried to copy your codes and run it, somehow it works...but it gives me a small error. Do you know why?
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Could not find installable ISAM.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
Thanks

Is your server 64-bit? There are no 64-bit drivers for JET:(

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

josephptran2002 Hall of Fame Points: 3275 More actions · Answer 8

josephptran2002

Hall of Fame

Points: 3275

May 18, 2009 at 2:13 pm

#996623

Hi There,

the server that I use is 32 bits

josephptran2002 Hall of Fame Points: 3275 More actions · Answer 9

josephptran2002

Hall of Fame

Points: 3275

May 18, 2009 at 2:14 pm

#996624

So What command should I use?

WayneS SSC Guru Points: 95409 More actions · Answer 10

josephptran2002 (5/18/2009)
Hi There,
Thanks, I did it and run the same codes that you suggested me to do
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
it still gives me the same error.
Thanks

You don't want to do OLE... You need to set the Ad-hoc query.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

josephptran2002 Hall of Fame Points: 3275 More actions · Answer 11

josephptran2002

Hall of Fame

Points: 3275

May 19, 2009 at 6:23 am

#996866

Hi Wayne,

How do you set adhoc query?

Thanks

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 12

Check if the following query works for you.

SELECT *

FROM OPENROWSET

(

'Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;IMEX=1;Database=P:\TranAssignments\ROCAPData\ROCAP.xls',

'SELECT * FROM [Sheet1$]'

)

--Ramesh

josephptran2002 Hall of Fame Points: 3275 More actions · Answer 13

josephptran2002

Hall of Fame

Points: 3275

May 19, 2009 at 6:52 am

#996896

Hello Ramesh,

Thanks for the comments

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 14

josephptran2002 (5/19/2009)
Hello Ramesh,
Thanks for the comments

Is the solution working for you?

--Ramesh