Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 393
  • Last Modified:

From SQL-server to Excel

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
batdan
Asked:
batdan
  • 3
  • 3
1 Solution
 
mironCommented:
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
 
batdanAuthor Commented:
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
 
mironCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
batdanAuthor Commented:
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
 
mironCommented:
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
 
batdanAuthor Commented:
Aha!

That's solved it.  Many thanks miron.

#D
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now