February 13, 2017 at 12:46 pm
Hello,
this is part 2/2 of my goal, so i have created a write part and it writes to a share drive, now this location may have 1 file, or 10 files, but i want it to be able to use a .cmd/sqlcmd, to read each file and run it on 1 sql server, I found this solution:
https://sqlandme.com/2013/03/25/sql-server-executing-multiple-script-files-using-sqlcmd/
sadly, when i change the information to my server name, user account with password, and the dir to the directory where it would have all my scripts, I run it but i get the results from the output file:
The system cannot find the path specified.
any idea maybe what i am doing wrong?
here is the .cmd file script (I changed some things for security purposes):
@Echo Off
FOR /f %%i IN ('\\ServerB\Results\*.Sql /B') do call :RunScript %%i
GOTO :END
:RunScript
Echo Executing Script: %1
SQLCMD -SServerB\MSSQLSERVER -U Test -P Test -i %1
Echo Completed Script: %1
:END
in the SQL job, i have it running:
\\ServerB\RunMyScripts.cmd
thanks in advance
February 13, 2017 at 1:53 pm
Have you tested the values that come into play for the %%i variable ? Also, when the SQL job runs, it will not run under your user id unless that's how the job is set up to run. Normally, it runs under the SQL Agent service account. That account will need NTFS permissions to the network share for this to work.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 13, 2017 at 1:59 pm
Hey sgmunson, thanks for the reply, as for the sql agent job, its running under SA, though maybe i should run it in a sql account that has access to the folder share, good catch, but what do i put for the %%i to test it with, for that matter, what is the purpose of %%i for? what value should be there?
February 13, 2017 at 2:11 pm
Siten0308 - Monday, February 13, 2017 1:59 PMHey sgmunson, thanks for the reply, as for the sql agent job, its running under SA, though maybe i should run it in a sql account that has access to the folder share, good catch, but what do i put for the %%i to test it with, for that matter, what is the purpose of %%i for? what value should be there?
The %%i is part of the "DOS Command" that your job is going to run. It's a built-in command that can be run at a command prompt in Windows operating systems. The %%i variable is there to represent the filename, but you have to know what folder is the "default folder" for the command window that's going to get spawned by the agent job, as otherwise, if the only thing that comes out of %%i is the filename and not the path as well, even if execution context doesn't get you, the lack of the path will. It's been a lot of years since I used to work with those commands on a regular basis, so I don't recall exactly what the %%i will contain under those circumstances, but you can open a command prompt and find out...
Type the following command and hit enter and see what you get:
FOR /f %%i IN ('\\ServerB\Results\*.Sql /B') do @ECHO %%i
When you see what each output line looks like, you'll know what's being passed to SQLCMD.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 13, 2017 at 2:19 pm
cool thanks for your input, when i run in command prompt:
c:\users\mypc>FOR /f %%i IN ('\\ServerB\Results\*.Sql /B') do @ECHO %%i
i get: "%%i was unexpected at this time"
... sadly i have no clue what that means 🙁
just wondering, for the %%i, should i change some stuff around to:
@Echo Off
FOR /f %\\ServerB\Results\%i IN ('*.Sql /B') do call :RunScript %%i
GOTO :END
:RunScript
Echo Executing Script: %1
SQLCMD -SServerB\MSSQLSERVER -U Test -P Test -i %1
Echo Completed Script: %1
:END
sadly i did just that above and got: Executed as user: ServerB\Admin. IN was unexpected at this time. Process Exit Code 255. The step failed.
thanks for your help, any input or thoughts 🙂
February 13, 2017 at 2:31 pm
Try this instead:
FOR %f IN (\\ServerB\Results\*.Sql) DO @ECHO %f
There are a couple of problems with the original command you had. The /F option tries to parse each file that gets found in the location between the parentheses and provide the individual words in each line of each file as parameters back to the batch file. That's not what you want. I took out the /F option, and turned the double percent into a single one, as it should be for this to work. Let me know if you can't adapt this back into the actual job.
EDIT: for clarity
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 13, 2017 at 2:42 pm
ok i think that worked, it came back to command prompt, thats good right? sorry me = idiot 😛
February 13, 2017 at 2:46 pm
Siten0308 - Monday, February 13, 2017 2:42 PMok i think that worked, it came back to command prompt, thats good right? sorry me = idiot 😛
Yes, that's good! It should also have output a list of the filenames in the network share that have a .sql extension. If it did that, then all you need to do is work the updated command elements into the original command, and using the SQLCMD in place of where @ECHO is.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 13, 2017 at 3:01 pm
cool, but when you say "it should have an output a list of filenames" it didnt, instead it just returned to command prompt, did it output to file? what directory?
thanks, getting close, there is hope for me 🙂
February 13, 2017 at 3:18 pm
I am sorry, me = idiot, it does work, and it did spit out the sql scripts, BIG thanks so now all i have to do is like you said, so i should be:
@Echo Off
FOR %f IN (\\ServerB\Results\*.Sql) DO call :RunScript %%i
GOTO :END
:RunScript
Echo Executing Script: %1
SQLCMD -S ServerB\MSSQLSERVER -U Test -P Test -i %1
Echo Completed Script: %1
:END
does the code look right above? i tried to run it, sadly it failed, error message "Executed as user: ServerB\Admin. RunScript was unexpected at this time. Process Exit Code 255. The step failed."
any thoughts?
February 13, 2017 at 4:35 pm
YAY found the problem, after much time and pulling out my hair (Had to do a trump aka come over to cover the bald spot that hair use to reside). the problem was %, specifically:
@Echo Off
FOR %%f IN (\\ServerB\Results\*.Sql) DO call :RunScript %%i
GOTO :END
:RunScript
Echo Executing Script: %1
SQLCMD -S ServerB\MSSQLSERVER -U Test -P Test -i %1
Echo Completed Script: %1
:END
hopefully you see the % bigger, if you're running within a batch/cmd file, you need to double the % markers, and i only (Like an idiot) had 1, so that fixed that.... NOW i get a new error message... and why not!!! it now says:
The system cannot find the batch label specified - RunMyScripts
the SQL job is using SA, i tried to use a windows domain account to run the job, but then says
The job failed. Unable to determine if the owner (domain\user1) of job Test_Script has server access (reason: Could not obtain information about Windows NT group/user 'domain\user1', error code 0x5. [SQLSTATE 42000] (Error 15404)).
any thoughts anyone?
February 13, 2017 at 4:52 pm
Ok quick update, i further researched the error, it did recommend i use and stick with the SA, but make sure the sql service account has access to the folder, which it does, so i am back to that same error message:
The system cannot find the batch label specified - RunMyScripts
which is really really weird, all paths are good, everyone has permissions... but why its showing me that... i dont know 🙁
February 14, 2017 at 7:45 am
Siten0308 - Monday, February 13, 2017 3:18 PMI am sorry, me = idiot, it does work, and it did spit out the sql scripts, BIG thanks so now all i have to do is like you said, so i should be:
@Echo OffFOR %f IN (\\ServerB\Results\*.Sql) DO call :RunScript %%i
GOTO :END:RunScript
Echo Executing Script: %1
SQLCMD -S ServerB\MSSQLSERVER -U Test -P Test -i %1
Echo Completed Script: %1:END
does the code look right above? i tried to run it, sadly it failed, error message "Executed as user: ServerB\Admin. RunScript was unexpected at this time. Process Exit Code 255. The step failed."
any thoughts?
You're definitely close. I haven't dealt with the FOR command in so long I kind of forgot that the usual way it gets used ends up in a batch file, where you then have to use doubled percent signs instead of single. I had to go look up how to chain the DOS commands together, as it appears you want to see each SQL script starting and ending, so here's what should work, shown below. You can't use a call within a batch file and parameters too. It just doesn't quite work that way. Look at my new script and take note of the ampersand character... it's used to string multiple DOS commands on the same line, which is going to be necessary in your case.
@ECHO OFF
FOR %%f IN (\\ServerB\Results\*.Sql) DO @ECHO Executing Script: "%%f" & SQLCMD -S ServerB\MSSQLSERVER -U Test -P Test -i "%%f" & @ECHO Completed Script: "%%f"
EXIT
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 14, 2017 at 8:25 am
Awesome big thanks, i am soooo close, i am not sure its on my end, or maybe something else, but its saying:
Executing Script: "\\ServerB\Results\Test.sql" Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user 'Domain/user1'.. Completed Script: "\\ServerB\Results\Test.sql". Process Exit Code 1. The step failed.
double checked, and that user has access to that folder full read and write, checked the password, and its good... is there something else i am missing, and i checked the path, it looks good 🙁
February 14, 2017 at 2:04 pm
Siten0308 - Tuesday, February 14, 2017 8:25 AMAwesome big thanks, i am soooo close, i am not sure its on my end, or maybe something else, but its saying:Executing Script: "\\ServerB\Results\Test.sql" Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user 'Domain/user1'.. Completed Script: "\\ServerB\Results\Test.sql". Process Exit Code 1. The step failed.
double checked, and that user has access to that folder full read and write, checked the password, and its good... is there something else i am missing, and i checked the path, it looks good 🙁
Did you remember to change the actual script values for the userid and password for the SQL account that SQLCMD is going to use, and that appear in your batch file after -U and after -P, to the actual userid and password? That error says you actually have a login failure, and it says for Domain/user1. Is that the user you wanted to use?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply