stored procedure issue.. - 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 2005 Forums

Transact-SQL (2005)

stored procedure issue..

Author

Topic

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2007-05-28 : 07:34:04

Hi,

I need help from u guys.

Using a stored procedure can i get connected to a remote Appilication server and in that server,
in d:\dataloads there is a excel file with name "data.xls".Now my Database server is located on a different
machine.Now i have written a stored procedure which has to get connected to remote Appilcation server and picks up
the excel file and loads the data into the sql server 2005 table.

The logic is somewhat like this

Insert into TEST select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=d:\DATALOADS\SAMPLE.xls;HDR=YES','SELECT * FROM [Sheet1$]')

how to modify the below part in the above stmt,if my application server is a different machine.
"Database=d:\DATALOADS\SAMPLE.xls"

Waiting for a reply.

Regards,
franky

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-28 : 08:25:15

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-28 : 08:25:30

You can keep the database portion into a variable and use dynamic sql to construct the valid OPENROWSET query.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2007-05-29 : 03:11:46

Hi Harsha,

That's ok.But will be the possible value for DATABASE="".
Do u give any IP addr or so.

Regards,
frank

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources