ISQL Utility - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

ISQL Utility

Author

Topic

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-08-09 : 13:01:44

hi,

I'm trying to call an SQL (.sql) file using ISQL from another one.

sample of the statement that I have inside the first file:
exec master..xp_cmdshell 'isql /Usa /P /iprint.sql /n'

This fine when you call the file from cmd line windows and you are inside the folder where print.sql is stored.
But the issue emerged when you are not in the folder where print.sql is stored.

This is what I want to achieve:

exec master..xp_cmdshell 'isql /Usa /P /ic:\myfolder\print.sql /n'

any resolution to this please.
Thanks

--------------------------
Joins are what RDBMS's do for a living

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-08-09 : 13:05:33

What version of SQL Server are you using?

C:\myfolder would need to be on the database server and not your client machine. xp_cmdshell operates from the perspective of the database server as it runs server-side and not client-side. So you'll need to use an UNC path instead: \\yourmachinename\sharename\...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-08-09 : 14:13:56

version is SQL Server 2000

I'm running the xp_cmdshell from within the cmd line of the server itself. I know the error is emerging only if i'm in a different folder
eg:
print.sql is saved under C:\temp
cmd execution eg:
c:\temp> isq /Usa /P /iprint.sql /n

this will run successfully

however,
eg:
print.sql is saved under C:\temp
cmd execution eg:
c:\> isq /Usa /P /iprint.sql /n

this will not recognize the file because it is not saved under C:

Thanks

--------------------------
Joins are what RDBMS's do for a living

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-08-09 : 15:29:59

I'd recommend using osql.exe instead of isql.exe. isql.exe is for versions older than 2000, although it does still work in 2000 just isn't as good as osql.exe.

I don't understand your last post. What do you have against putting the full path with the sql file? Specify c:\temp\print.sql and not just print.sql.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-08-09 : 15:51:45

Hey Tara, thanks for the support

I ran the following in SQL, it DOES NOT work

exec master..xp_cmdshell 'osql -Usa -P -i c:\temp\audits\print.sql /n'

Error: NO such file or Directory

Instead when I run the following from cmd line it works

osql -Usa -P -i c:\temp\audits\print.sql /n

--------------------------
Joins are what RDBMS's do for a living

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-08-09 : 17:15:30

You need to read my first post about the path needing to be on the database server and not your client machine.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-08-10 : 08:33:54

I think I understand what you referring to!
Yes, in both cases I'm running all command from a server. Nothing from a client machine. All the test are done locally in the server. Is this what you refer to?

--------------------------
Joins are what RDBMS's do for a living

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-08-10 : 15:12:09

Run this then:

exec master..xp_cmdshell 'dir c:\temp\audits\'

What do you get?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-08-10 : 15:25:40

when I ran this

exec master..xp_cmdshell 'dir c:\temp\audits\'

this is what I got.

 The system cannot find the file specified.
NULL

I'm sure there Folders/files with audits
It is weird, right!

--------------------------
Joins are what RDBMS's do for a living

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-08-11 : 12:53:39

Then you aren't local to the server.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-08-13 : 08:51:01

this command was ran from SQL2000

exec master..xp_cmdshell 'dir c:\temp\audits\'

how am I NOT local to the server?
thanks

--------------------------
Joins are what RDBMS's do for a living

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-08-16 : 14:25:48

My point is that c:\temp\audits is not a valid path on the server. It's probably on your client desktop, but xp_cmdshell runs from the server and thus needs a UNC path to get to a client path.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources