How to execute a sql script file uisng OSQL in sql 2005

Question

Post reply

How to execute a sql script file uisng OSQL in sql 2005

Priya2001mu

SSC-Addicted

Points: 417
More actions
October 22, 2008 at 6:35 am

#194876

Hi
I am executing a sql script using OSQL in sql 2000. It works fine and a txt ouput file is also generated.
The script is as such:
SET @xcmd = 'osql'
+ ' -Usa -P'
+ rtrim(@LogCode)
+ ' -S '
+ rtrim(@ServerName)
+ ' -d '
+ rtrim(@DatabaseName)
+ ' -i '
+ rtrim(@ScriptDirectory)
+ '1000ResetUSERS.sql'
+ ' -o '
+ rtrim(@HomeDirOutput)
+ 't'
+ convert(char(8), getdate(), 112) -- appEND YYYYMMDD
+ substring(convert(char(8), getdate(), 108), 1, 2) -- appEND Hour
+ substring(convert(char(8), getdate(), 108), 4, 2) -- appEND minutes
+ '_' + rtrim(@DatabaseName) + '_resetusers.txt'
I have now upgraded to SQL 2005 and this part of the code no longer works and i get the following error:
Cannot open input file F:\MSSQL\DBA\SCRIPTS\1000ResetUSERS.sql
No such file or directory exists
Can someone please help me to understand why this code is not working for SQL 2005?
I really need help. Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Perry Whittle SSC Guru Points: 234013 More actions · Answer 1

use SQLCMD not OSQL, check BOL for SQLCMD help

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Priya2001mu SSC-Addicted Points: 417 More actions · Answer 2

I did i did, but i cannot seem to find anything that could help. I cannot understand the syntax well. I am very new to all this and if you could guide me that would help a lot.

Would changing to sqlcmd entail alot of change in my code? What needs to be changed really?

The small script that i included is part of a larger script that runs automatically for certain processes. So whatever i change in the code, it must still run the same way.

Thanks.

Priya

dcorbitt Ten Centuries Points: 1006 More actions · Answer 3

The error says that

F:\MSSQL\DBA\SCRIPTS\1000ResetUSERS.sql

does not exist.

When you paste that string in the start/run box, what happens? Does it have any difficulty finding the file?

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 4

Keep in mind that the "-i" file needs to be available at the server or client where you actually launch the SQLCMD (or osql) !!

edit

this also goes for the -o file !!:rolleyes:

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Perry Whittle SSC Guru Points: 234013 More actions · Answer 5

check the file and directory exists, if it does exist check the permissions on them 😉

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 6

Well run this OSQL script locally and then make sure the script is located with the same name on the said directory...

Thanks!!

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."