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
Solved

Database script and bat/perl script

Posted on 2014-12-08
14
82 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
  • 6
  • 5
  • 3
14 Comments
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 374 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 48

Accepted Solution

by:
Vitor Montalvão earned 126 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 374 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 48

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 126 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 374 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 374 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 374 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 374 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 48

Expert Comment

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

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
OSQL to execute sql command 26 24
SSRS: Why is Visual Studio stripping these properties? 2 22
xml files 7 25
MS SQL Delete Duplicate Rows Only 2 19
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

828 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