Problems using osql in a shell context

[BobRodes]

Hello everyone.

I'm trying to automate the initial installation of a sql database from a VB program. I have an osql command that works fine from a command line: osql -E -iMyFileName.sql.
Now, Shell "osql -E -iMyFileName.sql" doesn't work, and neither does opening an ADO connection cn and doing cn.execute "xp_cmdshell 'osql -E -iMyFileName.sql'". So, I opened the query analyzer and gave it the command xp_cmdshell 'osql -E -iMyFileName.sql'
which gave me the output "Cannot open file - MyFileName.sql", No such file or directory, NULL, on lines 1, 2 and 3 respectively.

Why can't the shell context find the file, when the command line prompt can, assuming that's what's really happening? If there's a better way to run an .sql input file automatically, can someone tell me?

Thanks,

Bob Rodes

 

[JayKusch]

May need to give the full path to the "MyFileName.sql" file. Maybe like "c:\MyFileName.sql " or whereever the file resides.



Thanks

J. Kusch

 

[BobRodes]

Thanks Jay,

But I did that. Any other ideas out there?

Bob

 

[JamesLean]

Are you trying to run all this from the server, or are you using a seperate client machine?

What is the exact location of the sql file?

--James

 

[BobRodes]

Thanks for your pertinent question, James. In response, I set up a file Test.sql in C:\temp, which had some simple TSQL. This I was able to execute properly from query analyzer using xp_cmdshell. I was using a mapped pathname N:, and I'm thinking that the shell commands can't see this. Anyway, I can troubleshoot from here.

Thanks very much,

Bob Rodes

 

[JayKusch]

Hmmm ... there may lie your problem. SQL Server cannot read from mapped drives.

Thanks

J. Kusch

 

[BobRodes]

That seems to be the problem, Jay. I substituted the real pathname and was able to run everything fine.

Thanks everyone,

Bob