executing script from .txt file

Question

executing script from .txt file

Benjamin Kovac

Right there with Babe

Points: 718
More actions
August 6, 2007 at 3:15 am

#373130

Hi everyone,
Is there any chance to run scripts files on SQL server from a .txt file,ie:
I have a source.txt file inside with path references on sql script files which I want to execute?????????
Any suggestion?
Thanks
Ben

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

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

Crispin Proctor SSCoach Points: 18161 More actions · Answer 1

You could use a combination of xp_CMDShell and oSQL to execute the file.

Another option would be, and I have not tried this, to use openQuery to load the contents of the file into a variable and execute that.

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

Benjamin Kovac Right there with Babe Points: 718 More actions · Answer 2

Crispin,

thanks for your suggestion, I have tried to do it , but got an error which seems to say nothing to me:

I executed this script:

exec master.dbo.xp_cmdshell 'osql -E -iC:\usr\endentities.sql'

and got the error:

Cannot open input file - C:\usr\endentities.sql

No such file or directory

NULL

Any idea what does this mean?My sql script file works OK.

Thanks

Ben

Crispin Proctor SSCoach Points: 18161 More actions · Answer 3

I would imagine it might have something to do with access.

Can you run xp_CMDShell 'MD C:\usr\xxx'

Does it create the directory?

If you run the oSQL from CMD with the asme syntax, does that work?

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

If the C:\User directory is on a machine other than the server, you must use the UNC format for the directory name... for example...

\\machinename\sharename\endentities.sql

... where "sharename" might be "usr" if that's how you named it.

Your server must also be logged in as something that can "see" that machine and sharename.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Brandie Tarvin SSC Guru Points: 173068 More actions · Answer 5

And don't forget, in SQL Server 2005, xp_cmdshell is disabled by default. If you haven't configured it to be active, check your SQL Server Surface Configuration. BOL also has a T-SQL command to active xp_cmdshell, but I don't remember what it is.

FYI: xp_cmdshell is listed as a security hole. That's why it is disabled by default. Only use this if you have no other way to do what you need.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Benjamin Kovac Right there with Babe Points: 718 More actions · Answer 6

Cool,

Thank you guys for all your suggestions, will try and will be back with the result.

Cheers

Ben