?
Solved

Database script and bat/perl script

Posted on 2014-12-08
14
Medium Priority
?
91 Views
Last Modified: 2015-03-20
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?
0
Comment
Question by:mokkan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 3
14 Comments
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 1122 total points
ID: 40487065
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
 
LVL 51

Accepted Solution

by:
Vitor Montalvão earned 378 total points
ID: 40487077
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
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 1122 total points
ID: 40487082
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 378 total points
ID: 40487094
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
 

Author Comment

by:mokkan
ID: 40487138
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
 

Author Comment

by:mokkan
ID: 40487226
I would like to know via,  how to connect to database and update the tabel.
0
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 1122 total points
ID: 40487325
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
 

Author Comment

by:mokkan
ID: 40487401
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
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 1122 total points
ID: 40487410
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
 

Author Comment

by:mokkan
ID: 40487604
Thank you very much. How do  I verify if that command is successfull or not in bat?
0
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 1122 total points
ID: 40487793
Change: Update [Database_Table].[dbo].[sc_tasks] SET machine='prodserver1'

To: select machine from [Database_Table].[dbo].[sc_tasks]
0
 

Author Comment

by:mokkan
ID: 40535839
Thank you guys,  one more quick quesiton.  Can I uset encrypted passsword?
0
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 1122 total points
ID: 40623218
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
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40677467
Mokkan, why the grade B?
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Windows 8 came with a dramatically different user interface known as Metro. Notably missing from that interface was a Start button and Start Menu. Microsoft responded to negative user feedback of the Metro interface, bringing back the Start button a…

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question