execute codes below - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

execute codes below

Author

Topic

adamlee
Starting Member

9 Posts

Posted - 2006-04-14 : 22:09:48

Hi i got the following from this site and I can execute it in the SQL Query Analyzer:

Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [SheetName$]')

However, how can I run this code in the client side etc adocommand or adoquery? or can I put it into a stored procedure in server? how?

I tried these ways, but failed with some errors.

Would appreciate it if you could help me out!!!!

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-14 : 22:33:30

Put it in a stored procedure and call that from the client.

create proc s_ImportSpreadsheet
as
Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [SheetName$]')
go

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

a_r_satish
Yak Posting Veteran

84 Posts

Posted - 2006-04-15 : 00:20:02

I think u have to establish the linked server before the execution of that query.

satish.r
"Way to success is always under Construction"

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-15 : 09:00:54

quote:
Originally posted by a_r_satish

I think u have to establish the linked server before the execution of that query.

satish.r
"Way to success is always under Construction"

Nope - openrowset contains the connection string so there's no need to create a linked server.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

adamlee
Starting Member

9 Posts

Posted - 2006-04-15 : 09:11:19

nr, I will try your codes.
But I don't think it is a good way, excel filename and sheet name are not dynamic, is it possible to let usrs select excel file , then will show all sheets names for user to select, then all columns for the selected sheet will be displayed for user to select? I think that will be better.
Anyone can help? Thanks in advance.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-15 : 09:29:03

That's not the same question as you originally asked.
Where are these excel files? Are they on the server or the client?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

adamlee
Starting Member

9 Posts

Posted - 2006-04-15 : 09:38:12

At the client side. Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-15 : 10:10:11

In that case you would probably be better off doing this from the client rather than the server.
What is the client written in? It probably has better access to the excel object model than t-sql.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

adamlee
Starting Member

9 Posts

Posted - 2006-04-16 : 22:34:18

My SQL server won't let me save this stored procedure,it says something like 'ANSI_NULL AND ANSI_WARNING MUST BE SET FOR THE CONNECTION', can you help more?
thanks.

quote:
Originally posted by nr

Put it in a stored procedure and call that from the client.

create proc s_ImportSpreadsheet
as
Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [SheetName$]')
go

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

a_r_satish
Yak Posting Veteran

84 Posts

Posted - 2006-04-17 : 02:30:07

quote:
Originally posted by nr
Nope - openrowset contains the connection string so there's no need to create a linked server.

But when i try the following query, i get the error stating, you have to establish a linked server!!!!

Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\sample.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')
Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.

satish.r
"Way to success is always under Construction"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-17 : 04:29:01

quote:
Originally posted by a_r_satish

quote:
Originally posted by nr
Nope - openrowset contains the connection string so there's no need to create a linked server.

But when i try the following query, i get the error stating, you have to establish a linked server!!!!

Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\sample.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')
Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.

satish.r
"Way to success is always under Construction"

Refer this
http://support.microsoft.com/default.aspx?scid=kb;en-us;327489

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources