Solved

Database script and bat/perl script

Posted on 2014-12-08
14
81 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 47

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 47

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 47

Expert Comment

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

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL date incremented 11 31
SQL Server 2012 r2 - Varible Table 3 27
Show Results for Latest DateTime in a View 27 25
Rename a column in the output 3 14
When you upgrade from Windows 8 to 8.1 or to Windows 10 or if you are like me you are on the Insider Program you may find yourself with many 450MB recovery partitions.  With a traditional disk that may not be a problem but with relatively smaller SS…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

822 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