Solved

Database script and bat/perl script

Posted on 2014-12-08
14
85 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 50

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
Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

 
LVL 50

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 50

Expert Comment

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

Featured Post

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

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

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.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

695 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