Hi,

I am writing an installer for a database driver application. The installer
is supposed to create the DB, tables and populate the tables with sample
data from an excel file.

The excel file containing the sample is generated via the DTS wizard from an
existing production DB.

The application will be installed on a system different fromt he one where
the SQL server is installed.

At this point I am able to create the DB and the Table using vbscript. I am
howerver not able to impor the data from the excel sheet.

It seems the excel sheet needs to be stored locally on the SQL server
machine for import to work .. is this correct ?

Is there any other way to populate the remote DB using an excel file on my
machine.

- Rohit

Hi Rohit,

You may want to add a linked server points to the Excel
file on your machine. To do this you need to place the
Excel in a shared folder on your machine and run a
command similar to the following against the SQL Server:

sp_addlinkedservr @server='LinkedExcel', @srvproduct='',
@provider='Microsoft.Jet.OLEDB.4.0',
@datasrc='\\yourmachinename\sharedfolder\excelfile name.xl
s', @provstr='Excel 5.0'

Then you consider LinkedExcel to be a SQL Server and run
a INSERT...SELECT to populate the data. Please refer to
the following topic in BOL for more information:

http://msdn.microsoft.com/library/en...ata/ac_8_md_03
_8vhw.asp

If anything is unclear, feel free to let me know.

Sincerely,

William Wang
Microsoft Online Partner Support

Get Secure! - <www.microsoft.com/security>

================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and
confers no rights.

--------------------
hx.gbl!TK2MSFTNGP10.phx.gbl

Hi William,
Thanks for your suggestion. I am also working with Rohit on the same
problem.

For populating a database from an Excel file, Does a linked server
really require?. Can't we use distributed queries such as

SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]

But for this, also the file should be on the server. The workaround, you
were suggesting will also involve domain username and password, if the server
is not on the same domain. Probably we will not be allowed to create a share.
Are there any other workarounds for such a problem. Excel file is not a
constraint. We can use any other type of file
or any other way of getting data from the Server.

We have come up with a solution in which we will create the DTS package
manually and ship the Excel file and DTS package both. We know, how to run a
DTS package from vbscript. But we are not happy with this solution, it seems
as if we unnessasorily shipping the DTS package also.

Are there any other ways. Your suggestions will be most welcome.

Thanks in advance,

- Avadhesh


"William Wang[MSFT]" wrote: