Read multiple .sql files having issues with .cmd script :(

Question

Read multiple .sql files having issues with .cmd script :(

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110639 More actions · Answer 1

You may also need to check the actual scripts themselves to be sure you don't have things that require a different execution context....

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Siten0308 SSCarpal Tunnel Points: 4404 More actions · Answer 2

Hey sgmunson,
yes I definitely made sure of that, i made sure to change to username and password and to use a domain account only, we use windows authentication only and not mixed mode for security reasons. stupid question, for the password, should I put quotations around the password? or is this exactly how it should be:
@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
OR:
@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

thanks for all your help, i know its something really small and stupid is just a minor thing, i made sure the user account exists and even gave the user account sysadmin rights and share folder read and write... just weird 🙁

sgmunson SSC Guru Points: 110639 More actions · Answer 3

Siten0308 - Tuesday, February 14, 2017 2:11 PM
Hey sgmunson,
yes I definitely made sure of that, i made sure to change to username and password and to use a domain account only, we use windows authentication only and not mixed mode for security reasons. stupid question, for the password, should I put quotations around the password? or is this exactly how it should be:
@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
OR:
@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
thanks for all your help, i know its something really small and stupid is just a minor thing, i made sure the user account exists and even gave the user account sysadmin rights and share folder read and write... just weird 🙁

You shouldn't need to quote the password unless it contains spaces. Remind me again exactly how this job executes? I'm wondering about execution context.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Siten0308 SSCarpal Tunnel Points: 4404 More actions · Answer 4

All good, i sadly didnt mention how it was executed, this is in a SQL agent Job that would be executed once a week, ideally.

sgmunson SSC Guru Points: 110639 More actions · Answer 5

Siten0308 - Tuesday, February 14, 2017 2:30 PM
All good, i sadly didnt mention how it was executed, this is in a SQL agent Job that would be executed once a week, ideally.

And perhaps finally, it may make sense that if the SQL Agent runs as a Windows domain account, that you not specify the userid or password, and instead specify -E to use a "Trusted Connection". It's more secure because the password for that account is not just sitting there in the batch file in plain text. Might solve the problem.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

sgmunson SSC Guru Points: 110639 More actions · Answer 6

And, ... you'll need to be sure there is a SQL Login in place for the Windows domain account on the server that is the target of SQLCMD.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Siten0308 SSCarpal Tunnel Points: 4404 More actions · Answer 7

YOU SIR!!! Are you genius! i replaced -U and -P with -E like you said, THEN BAM!! WORKED... OH SO BEAUTIFUL 🙂

sgmunson SSC Guru Points: 110639 More actions · Answer 8

Glad I could help...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jeff Moden SSC Guru Points: 1004250 More actions · Answer 9

The next level of simplification exists in the following command... have a look.
https://technet.microsoft.com/en-us/library/cc753551(v=ws.11).aspx

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sgmunson SSC Guru Points: 110639 More actions · Answer 10

Jeff Moden - Tuesday, February 14, 2017 6:26 PM
The next level of simplification exists in the following command... have a look.
https://technet.microsoft.com/en-us/library/cc753551(v=ws.11).aspx

Yep... know about it, but as I haven't had time to track down every single option, my quick scan suggests that at least most of that functionaility exists in Windows 7 and above, if not identical functionality. I had to research the FOR command already today and I just don't have the time to add this one. I do admit, however, that it probably looks better whenever the more arcane options of FOR are used, based on using FORFILES instead.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)