473,732 Members | 2,210 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Copying XLS (local) directly to SQL (remote) Table without Link srver and BCP

13 New Member
I have a XLS file on local machine and would like to copy all the columns to a new SQL table on the 'remote' (not local sql server) using SQL scripts.

I tried following :
select * into TEMP_PLAN_CODE FROM OPENROWSET('Mic rosoft.Jet.OLED B.4.0',
'Excel 10.0;Database=D :\Placodes.xls; HDR=YES',
'SELECT * FROM [Sheet1$]')

but it's giving me an error :
Msg 7415, Level 16, State 1, Line 1
Ad hoc access to OLE DB provider 'Microsoft.Jet. OLEDB.4.0' has been denied. You must access this provider through a linked server.

I can't create linked server as well and can't run BCP (no authority)

What are my available choices for copying XLS (on local) to SQL (not local) tales using SQL scripts and not using any .NET code.

Thanks in advance
Mar 31 '08 #1
1 2226
ck9663
2,878 Recognized Expert Specialist
I have a XLS file on local machine and would like to copy all the columns to a new SQL table on the 'remote' (not local sql server) using SQL scripts.

I tried following :
select * into TEMP_PLAN_CODE FROM OPENROWSET('Mic rosoft.Jet.OLED B.4.0',
'Excel 10.0;Database=D :\Placodes.xls; HDR=YES',
'SELECT * FROM [Sheet1$]')

but it's giving me an error :
Msg 7415, Level 16, State 1, Line 1
Ad hoc access to OLE DB provider 'Microsoft.Jet. OLEDB.4.0' has been denied. You must access this provider through a linked server.

I can't create linked server as well and can't run BCP (no authority)

What are my available choices for copying XLS (on local) to SQL (not local) tales using SQL scripts and not using any .NET code.

Thanks in advance
You can create a client-side apps that will read the excel file and connect to your SQL-Server and INSERT your excel columns into that table. I have tried with EXCEL before but that was a much earlier version. Since Microsoft products have built-in OLE's in them, you can use Excel's VBA to connect to your sql server and populate your table.

Actually whatever tool (maybe C#) that can read excel files and can connect and run t-sql to a sql-server can do this task.

-- CK
Mar 31 '08 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

3
3651
by: Robert Tarantino | last post by:
Hello, I am trying to find a way to create a scheduled task or service that will copy my local profile folders under "Documents and settings" to a network drive. This would allow me to restore my settings if my profile became tampered with or corrupt. Is there any sample code available out there? -Robert
4
4758
by: oceanhai | last post by:
We currently have a PPTP connection set up for our developers to access our development SQL server through a VPN tunnel. When they need to copy tables up to the dev SQL from their local machine they simply do a DTS copy. However, we are now moving to a thin client solution where they will be working on a terminal server. They will have access to the development SQL servers and SQL tools such as EM and QA. However, they will not have...
5
6062
by: Ian Davies | last post by:
Hello I am trying to use php to automate the copying of a file from my remotely hosted directory to my computer. I have put together the following but it will only do the copying from one directory to another on the remote server. Reading the postings on these functions they suggest that it can be done but it appears to be the case if your are hosting your site on the same machine you want to copy the files to. Does anyone know if there...
6
10229
by: Access Newbie | last post by:
I'm using Access 2000 and I'm trying to create a pass-through query to append all the data from a local table to a remote table. I'm using the SQL query editor in MS Access to create the query (I don't want to do this in VB code). I've set up the ODBC connection string in the query properties, which has been tested and works: ODBC;DSN=dev001 student99;Description=dev001...
1
2752
by: Matt Walker | last post by:
I decided to flesh out my php skills by building a web application which could scan through local files on my intranet (specifically mapped drives on my server) and generate a list of Movies or pictures or music, etc. Which worked fine, I created the code to do that and to store the results in a mysql database. I can even print it out to a ..php page and sort it however I like. What I can't seem to do is to create links to the data...
4
4973
by: Jim Devenish | last post by:
I wish to copy a table on a SQL Server backend to a table on an Access (.mdb) front end in the simplest way. I have the following to get the recordset but am seeking something easier. Dim theConnectionString theConnectionString = "Driver=SQL Server; Server=Server;Database=myData;Trusted_Connection=Yes"
0
1394
by: mlfblom | last post by:
Hi, I am running visual studio 2005 on a Vista Ultimate client. I have created a remote site on a Windows 2003 R2 server. This site has about 100 aspx, many App_Code files and several references to DLL's. When I open the remote site within VS 2005 on the client machine, I takes very, very long before the site is opened and the solutione explorer shows all files. In the mean time the status bar shows a message like it is copying files...
4
1816
by: quincy451 | last post by:
I am running MS SQL 2000 server. The table involved is only about 10,000 records. But this is the behavior I am seeing. The local machine is querying the table looking for a particular record. Everything works fine. The remote amchine goes to clear the table and then insert all 10,000 records, into the table the following happens. 1) the local machines queries do not compilete until the remote machine is done.
2
2387
by: troy_lee | last post by:
What is the best way of copying all the records from a linked table into a replica table that is local on my computer? This is for development work at home where I can not access the main table. Is a macro the best way to handle this? Thanks in advance. Troy Lee
0
8774
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9447
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
9235
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
6735
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6031
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4550
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4809
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3261
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2721
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.