Solved

Changing scripts from using isql to sqlcmd - global replace?

Posted on 2013-10-29
6
961 Views
Last Modified: 2013-11-04
I'm upgrading a sever that runs SQL server batch jobs.  (The databases are actually on a different server, but SQL server is installed on this "Scheduling" server.)   The server I'm upgrading is SQL Server 2000 and on it's replacement server I will be installing SQL Server 2008 R2 (to be like the servers where the DBs reside).

The batch jobs on the server currently run scripts like this:

isql -Usa -Ppw -dDBname -iD:\SQL\OrderHist.sql -SserverName

We also run BCPs in them that look like this:

bcp DBname..TableName in H:\download\txt\file.txt /fD:\bcp\fmt\file.fmt /Usa /Ppw /SserverName /r\n /m100

Should I be able to do a search and replace on these bat files and replace all the "isql" with "sqlcmd" and the bat jobs run just fine on the new server?

What about the BCP - will the same command format for it work on 2008?

thanks
0
Comment
Question by:sqdperu
[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
  • 2
  • 2
  • 2
6 Comments
 
LVL 8

Expert Comment

by:virtuadept
ID: 39608985
As another option just put a copy of SQLCMD.exe as ISQL.exe in the path.

then you don't have to change all your scripts.

BCP shouldn't need to change.
0
 
LVL 40

Expert Comment

by:lcohan
ID: 39609821
Obviously you will also need to change

<<
The batch jobs on the server currently run scripts like this:

isql -Usa -Ppw -dDBname -iD:\SQL\OrderHist.sql -SserverName
>>

like

sqlcmd -Usa -Ppw -dDBname -iD:\SQL\OrderHist.sql -SserverName
0
 

Author Comment

by:sqdperu
ID: 39611336
"Obviously you will also need to change ... "

That's my question.  Can I just change the "isql" to "sqlcmd" in all my jobs and the parameters that follow that worked with "isql" will work just fine using "sqlcmd"?

Is "sqlcmd" completely backwards compatible with how the parameters worked with "isql"?

I'm looking for comfirmation from someone who has actually tried this and know that it works.
0
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 
LVL 40

Accepted Solution

by:
lcohan earned 300 total points
ID: 39612197
"I'm looking for comfirmation from someone who has actually tried this and know that it works."


I work with SQL server since its 3.5 version to date - 2012 and for ISQL or OSQL vs. SQLCMD the only difference that I'm aware of is that SQLSMD returns/displays more decimals on float columns as per article below.

http://social.msdn.microsoft.com/Forums/en-US/1f30da7e-f1b0-48cd-9197-98f78f30face/isql-to-sqlcmd-conversion-format-issues

Other than that you can compare the commands at Microsoft links below:

ISQL: http://technet.microsoft.com/en-us/library/aa214007(v=sql.80).aspx
SQLCMD: http://technet.microsoft.com/en-us/library/ms162773(v=sql.100).aspx
0
 
LVL 8

Assisted Solution

by:virtuadept
virtuadept earned 200 total points
ID: 39612268
There is no way for us to guarantee that the SQL running with a different version of SQL server is going to work the same way as it did on the prior version of SQL server. But the command line parameters are the same format for ISQL or OSQL or SQLCMD.
0
 

Author Comment

by:sqdperu
ID: 39621369
"There is no way for us to guarantee that the SQL running with a different version of SQL server is going to work the same way as it did on the prior version of SQL server. But the command line parameters are the same format for ISQL or OSQL or SQLCMD. "

I plan to test all the jobs on a test server.  I was just looking for a starting point and it sounds like a global change and test would be it.  thanks.
0

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

729 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