Database script and bat/perl script

Hello,

I would like to write a bat or perl script to connect to SQL database table and change one of the field.

Is there way I can do via bat or perl script?
mokkanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steve WalesSenior Database AdministratorCommented:
The sqlcmd utility can be used to process SQL commands from a batch file.

Refer to the SQL Server Documentation for full details on how to make that work:

http://msdn.microsoft.com/en-us/library/hh213540.aspx - sqlcmd - How To Topics
http://msdn.microsoft.com/en-us/library/ms180944.aspx - Use the sqlcmd utility
http://msdn.microsoft.com/en-us/library/ms162773.aspx -  the sqlcmd utility
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
With a batch file you just need to call osql utility.
Here's an example with Integrated Security (-E parameter) i.e. runs with the current user credential:
osql -S Servername -E -i C:\MyScript.sql -o C:\Output.txt

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Steve WalesSenior Database AdministratorCommented:
Be aware, osql has been deprecated as of (I believe 2005).  It may still be there for backward compatibility in later versions, but as far as I'm aware it's no longer supported.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Not really deprecated. In SQL Server 2014 release they sent a warning that osql might be deprecated in future version of SQL Server. Here's the article from Microsoft.
But you are right. It's better to use sqlcmd. The parameters are the same for this solution.
0
mokkanAuthor Commented:
I only need to do the following action.

Update [Database_Table].[dbo].[sc_tasks] SET machine='prodserver1'

Can I do it via perl script or bat file?  Can you tell me how exactly it would be? I am new to MSSQL server.

First connect to database? And slect the table? And update?
0
mokkanAuthor Commented:
I would like to know via,  how to connect to database and update the tabel.
0
Steve WalesSenior Database AdministratorCommented:
If you can login to the database using your windows credentials as a trusted login:

sqlcmd -E -Q "Update [Database_Table].[dbo].[sc_tasks] SET machine='prodserver1'"

If you need to use a sql server account:

sqlcmd -U username -P password  -Q "Update [Database_Table].[dbo].[sc_tasks] SET machine='prodserver1'"

If you're on the machine, you could also just use SQL Server Management Studio to do it.
0
mokkanAuthor Commented:
thank you so much. If I want to execute through window bat file, how do i do that?  Just cut and past in bat file and execute?
0
Steve WalesSenior Database AdministratorCommented:
Correct.  Pick the one that matches your login requirements and paste into a .bat/.cmd file and you'd be set to go.

Usual disclaimers about "test before doing in prod" apply.
0
mokkanAuthor Commented:
Thank you very much. How do  I verify if that command is successfull or not in bat?
0
Steve WalesSenior Database AdministratorCommented:
Change: Update [Database_Table].[dbo].[sc_tasks] SET machine='prodserver1'

To: select machine from [Database_Table].[dbo].[sc_tasks]
0
mokkanAuthor Commented:
Thank you guys,  one more quick quesiton.  Can I uset encrypted passsword?
0
Steve WalesSenior Database AdministratorCommented:
Sorry for the delay is responding, missed your reply.

To the best of my knowledge, no.  If you're invoking via command like it is what it is - you are stuck with clear text.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Mokkan, why the grade B?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.