From MS-SQL Query (without using xp_CmdShell & LINKED Server) how can we insert / update records from Local Excel file to a Remote MS-SQL Server database Table
-
Dear All,
Hi! I am searching for a query based solution (without using xp_CmdShell & LINKED Server) to insert / update records from Local EXCEL file to Remote MS-SQL server table.
For example, we have an Excel file on our local system hard drive (C:\MyExcel.XLS) & in this Excel file we have a sheet named "TNR". On "TNR" sheet we have 8 rows with data in 2 columns.
Now We need to insert or update these 8 Records on REMOTE MS-SQL Server (i.e. on 84.84.84.1) "TEST" database "DatafromEXCEL" table without DTS, without xp_CmdShell & without LINKED Server).
If possible, kindly solve this issue.
-
OPENROWSET command could do it, but for me that's just another flavor of a linked server; only difference is whether you devclare the server(that excel spreadsdheet) on the fly, or saved as a linked server.
probably a better way to do what you want, why do you have to update an exisitng file instead of creating a new one and sending someone a link to the new file?
SELECT * FROM OPENROWSET('MSDASQL',
'DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb);
UID=admin;
UserCommitSync=Yes;
Threads=3;
SafeTransactions=0;
ReadOnly=1;
PageTimeout=5;
MaxScanRows=8;
MaxBufferSize=2048;
FIL=excel 12.0;
DriverId=1046;
DefaultDir=C:\Data\BlockGroups_2010;
DBQ=C:\Data\BlockGroups_2010\AKblockgroup.xls',
'SELECT * FROM [AK$]')
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Dear Lowell,
Thanks for the reply.
But, in OpenRowset again without LINKED Server we can't to this process. I need Query based solution to perform this process without using XP_CMDSHELL & Without using LINKED Server.
-
bharat sethi (7/29/2011)
Dear Lowell,Thanks for the reply.
But, in OpenRowset again without LINKED Server we can't to this process. I need Query based solution to perform this process without using XP_CMDSHELL & Without using LINKED Server.
WHY?
Pretty much only 2 option left and 1 of them's real ugly... and the other one needs to work client side.
Option C would be to build and SSRS report, schedule it to run on a schedule and e-mail the results (or send the shared folder).
-
i'd step completely outside of SQL server and write something in a programming language instead.
trying to do everything from within SQL is the old "Everything is a nail" tunnel vision. a different tool than the hammer in your hand might be a better solution.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Can you make that image a little larger. That text looks like TSQL but I'm not even sure... and yes I have good eye-sight!
-
Ninja's_RGR'us (7/29/2011)
Can you make that image a little larger. That text looks like TSQL but I'm not even sure... and yes I have good eye-sight!Tx.
-
there you go!
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell (7/29/2011)
there you go!re-tx 😀
-
Lowell (7/29/2011)
i'd step completely outside of SQL server and write something in a programming language instead.trying to do everything from within SQL is the old "Everything is a nail" tunnel vision. a different tool than the hammer in your hand might be a better solution.
+1MM
You made my morning Lowell 😛
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
opc.three (7/29/2011)
Lowell (7/29/2011)
i'd step completely outside of SQL server and write something in a programming language instead.trying to do everything from within SQL is the old "Everything is a nail" tunnel vision. a different tool than the hammer in your hand might be a better solution.
+1MM
You made my morning Lowell 😛
Morning?? It's almost time to go home!
No, IT IS time to go home.
BYE. 😛
-
What time zone are you in? The lunch whistle just went off here (Denver, CO).
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
opc.three (7/29/2011)
What time zone are you in? The lunch whistle just went off here (Denver, CO).Montréal (New-York).
-
Cool, that must explain the French in the images of dialog boxes in your posts. I wish I was in NY right now...I am going to get a slice for lunch but there isn't anything close to NY pie around here 🙂
Have a good weekend Ninja's!
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
opc.three (7/29/2011)
Cool, that must explain the French in the images of dialog boxes in your posts. I wish I was in NY right now...I am going to get a slice for lunch but there isn't anything close to NY pie around here 🙂Have a good weekend Ninja's!
Sure will be.
Only 5 hours from (hit the preview button middle-top right) > http://www.cirquedusoleil.com/en/shows/totem/tickets/montreal.aspx
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply