May 11, 2015 at 2:50 am
Hi Guys,
I have create a batch file to execute a stored proc to import data.
When I run it from the server (Remote Desktop) it works fine, but if I share the folder and try to run it from my pc, it doesn't do anything. I don't get an error, it just doesn't do anything. My windows user has admin rights in SQL. Why is it not executing from my PC?
Any advise would be great.
May 11, 2015 at 2:53 am
Kinda need to see the batch file to say anything useful.
Bear in mind that if you use remote desktop, the batch file is executing on the server, whereas if you share a folder and execute, it's executing on your machine.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 11, 2015 at 3:31 am
The only thing in the batch file is
-S Servername\InstanceName -i D:\Path\SQLCMDImport.sql
And then to move the file to an archive folder.
SQLCMDImport.sql executes a stored proc that looks like the following:
INSERT INTO Table
SELECT
column1,
column2,
column3,
FROM OPENDATASOURCE( 'Microsoft.ACE.OLEDB.12.0', 'Data Source="D:\path\importfile.xlsx";
Extended properties=Excel 8.0')...sheet1$
Should I just execute as and then my windows account?
May 11, 2015 at 3:43 am
And does the folder "D:\Path\" exist on your machine?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 11, 2015 at 4:10 am
No, it is a shared folder on my machine. Should I change it to the shared folder address and then try to run it from my machine?
May 11, 2015 at 5:17 am
If you run the batch file from your machine, it's looking for files and folders on your machine. Hence D:\Path, means it's looking for the drive on your machine mapped to D and the path specified. If that drive or folder doesn't exist, then there's no way the batch file could possibly run.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 11, 2015 at 5:38 am
Take a look at what Gail said in her first post on this thread. When you remote into the server, the batch file executes on the server. When you use your desktop to access the file and execute it, it executes on your desktop. You need to write the batch file to run from your desktop.
Edit: Sorry, Gail. Didn't see your recent post until I posted.
May 11, 2015 at 6:56 am
I should have explained from the start what my idea is, sorry. I want to place a batch file on a users PC, that does not have access to SQL or the server, to run the batch file to import the data. Is this plan possible or won't I be able to do it like this?
May 11, 2015 at 7:07 am
No. How could something running on their machine be able to access resources that can't be accessed from their machine?
If a user runs a batch file on their machine, regardless of where that batch file is located, it runs on their machine and uses their local resources. It's the same as any application running on their machine.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 11, 2015 at 7:38 am
Thanks.. I was just wondering, so that won't happen then.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply