Solved

From SQL-server to Excel

Posted on 2001-09-17
6
346 Views
Last Modified: 2008-02-01
Hi All,

I am trying to write an SQL statement in the format:

SELECT * INTO <Target> FROM <Source>

which will run against an ADODB.Connection to a SQL server database.

For my Target, I am trying to use the string:

[Excel 8.0;HDR=Yes;DATABASE=C:\Temp.xls].[Sheet1]

but I'm always getting the error:

[Microsoft][ODBC SQL Server Driver][SQL Server]Specified owner name 'Excel 8.0;HDR=Yes;DATABASE=C:\Temp.xls' either does not exist or you do not have permission to use it.

There is no question that the Spreadsheet exists, and at present it's just a new workbook created on my machine by me with field names in it, so there shouldn't be any problem with permissions.

The source is just a table name in the SQL server database.

Any thoughts?

#D
0
Comment
Question by:batdan
  • 3
  • 3
6 Comments
 
LVL 9

Expert Comment

by:miron
ID: 6487266
I am not sure about writing it as a single line syntax for an OPNEQUERY provider. I usually use
execute xp_addlinkedserver ...
-- when I do so I am required to create a user for that linked server ( when I forget to do it I get exactly same error as I read in the questiong you posted )

execute sp_addlinkedsrvlogin ...

and than using
OPENQUERY()

I am able to manipulate files using SQL Server.

Thanks
0
 
LVL 1

Author Comment

by:batdan
ID: 6487432
miron:
Thanks for the input, I am new to SQL server though and have not used anything like this before.  If you could give me an example of adding the server and using it in a query I would be grateful.

Thanks

#D
0
 
LVL 9

Expert Comment

by:miron
ID: 6487705
Actually OpenQuery is not needed. Sorry, I was thinking about index server.

sp_addlinkedserver N'Excel', N'Jet 4.0',
                   N'Microsoft.Jet.OLEDB.4.0',
                   N'C:\test.xls',
                   NULL,
                   N'Excel 8.0'
GO

sp_addlinkedsrvlogin N'Excel', false, N'sa', N'Admin', Null
GO
 
select * from Excel...sheet1$


0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 1

Author Comment

by:batdan
ID: 6487894
Thanks miron.  I have this working well now.  Full points for a decent alternative (which I shall award shortly)...although unfortunately I have now another problem.  I wanted to use this other server to insert data straight into the table using :

SELECT * INTO Excel...Sheet1$ FROM Customer

I don't seem to be able to do this (although I can use a normal INSERT for just one row) due to an object qualifier limitation (of 2).  I am now thinking that this is probably tough luck, and can't be done...have you come across this before, or know any way around it?

Obviously extra points will be awarded for anything useful.

Cheers

#D
0
 
LVL 9

Accepted Solution

by:
miron earned 150 total points
ID: 6488203
Have you tried it

insert into Excel...Sheet1$ SELECT * FROM Customer

or with complete insert / select list

insert into Excel...Sheet1$ ( col1, col2, ... coln )SELECT col1, col2, ... coln FROM Customer
0
 
LVL 1

Author Comment

by:batdan
ID: 6489826
Aha!

That's solved it.  Many thanks miron.

#D
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now