Solved

Database script and bat/perl script

Posted on 2014-12-08
14
83 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 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 49

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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
LVL 49

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 49

Expert Comment

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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Table where row act as column 11 71
Many to one in one row 2 40
Regarding Disk IO 3 49
How to trim a value in SQL 2 28
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

726 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