Execute script from stored procedure

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

SQL Server 2000 Forums

Transact-SQL (2000)

Execute script from stored procedure

Author

Topic

Frances
Starting Member

20 Posts

Posted - 2005-02-21 : 06:54:11

Hi all,

is it possible to execute a script from a stored procedure. For example:

I have a script at the location: C:\myScript.sql

Can I acces this script like this (or something like this?):

EXEC C:\\myScript.sql
GO

Thank you

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-21 : 07:09:20

take a look at osql command line utility in BOL.
you can execute that via xp_cmdshell.

Go with the flow & have fun! Else fight the flow

Frances
Starting Member

20 Posts

Posted - 2005-02-21 : 07:25:29

I have looked at that, but what I want is that the stproc is called from ASP .Net

Thank you

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-21 : 07:50:58

you can run the xp_cmdshell from your sproc.

Go with the flow & have fun! Else fight the flow

Frances
Starting Member

20 Posts

Posted - 2005-02-21 : 08:26:41

quote:
Originally posted by spirit1

you can run the xp_cmdshell from your sproc.

Go with the flow & have fun! Else fight the flow

Hopefully, my last reply for this subject

I've tried using xp_cmdshell like this:

1. xp_cmdshell 'osql -E -S serverName -d databaseName < C\test.sql'
2. xp_cmdshell 'osql -E -S serverName -d databaseName < C\\test.sql'
3. xp_cmdshell 'osql -E -S serverName -d databaseName < "C\test.sql"'
4. xp_cmdshell 'osql -E -S serverName -d databaseName < "C\\test.sql"'

And with all four I get:

"The system cannot find the path specified."

When the file really DOES exist at that path! Any ideas why?

Thank you

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-21 : 08:34:25

emm missing a :?
xp_cmdshell 'osql -E -S serverName -d databaseName -i C:\test.sql'

Go with the flow & have fun! Else fight the flow

Frances
Starting Member

20 Posts

Posted - 2005-02-21 : 09:00:04

Dôh!

Thanks Spirit, you've been a great help. Perhaps you can be one for just a little bit more... ?

Now I get this:

Line 1: Incorrect syntax near '1'.

Almost as if he reads test.sql with line-numbers. I've tried renaming the file to test.txt but then he says he can't find the path specified

Thank you

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-21 : 09:15:42

well that error gives me so much info it hurts...

can you post the code of the sproc and the sql file? shortened if it's too long.

Go with the flow & have fun! Else fight the flow

Frances
Starting Member

20 Posts

Posted - 2005-02-21 : 09:36:54

OSQL is now officially driving me mad

*Frances riding around on a uni-cycle with a flowerpot on her head*

Very simple SQl-file named createOne.sql:

USE DatabaseName
GO
CREATE TABLE [dbo].[Hrm_HourHour] (
	[hour_id] [int] IDENTITY (1, 1) NOT NULL ,
	[employee_id] [int] NOT NULL ,
	[hourType] [int] NULL ,
	[quantity] [money] NULL ,
	[remark] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[project_id] [int] NULL ,
	[specification_id] [int] NULL ,
	[FK_day_id] [bigint] NULL ,
	[FK_hourType_id] [int] NULL ,
	[remove_flag] [bit] NULL 
) ON [PRIMARY]
GO

When I type this in my command-prompt;

C:\> osql -U username -P password -S servername -i C:\test\createOne.sql

Everything goes fine, but when I dare to say in Query Analyzer:

xp_cmdshell 'osql -U username -P password -S servername -i C:\test\createOne.sql'

Then I get the message that the system cannot find the path specified... AAARGH!

Thank you

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-21 : 09:51:12

is the file on the sql server or your dev machine?
the file must be on the server or some shared path.

Go with the flow & have fun! Else fight the flow

Frances
Starting Member

20 Posts

Posted - 2005-02-21 : 10:14:40

On my dev machine, but then where is my SQL server? Even if I put the sql-file in the C:\Program Files\Microsoft SQL Server\80\Tools\Scripts folder he can't find it

Thank you

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-21 : 10:23:38

so you have the sql server on the machine you develop on?
hmmm... how do you log in? as SA or some other user name?

Go with the flow & have fun! Else fight the flow

Frances
Starting Member

20 Posts

Posted - 2005-02-21 : 10:26:29

Some other user name, but the combination username/password is correct, that I know for sure

Thank you

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-21 : 10:33:18

does that account have rights to execute xp_cmdshell?

Go with the flow & have fun! Else fight the flow

Frances
Starting Member

20 Posts

Posted - 2005-02-21 : 10:38:57

He should have, it's the admin account. Besides, when I try to execute a query using the -q option everything goes fine.

xp_cmdshell 'osql -E -S serverName -d databaseName -q "Select * From tableName"'

No problemo!

Thank you

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-21 : 10:50:55

ok then i'm stumped. i have no idea what else to tell you but check that path once more... sorry.

Go with the flow & have fun! Else fight the flow

Frances
Starting Member

20 Posts

Posted - 2005-02-21 : 10:53:59

Thanks for your help Spirit, I really appreciated it. Maybe I should try executing the script from a different angle. If it doesn't work... I'll be back!

MWUHAHAHAHAHAHAHAHA!

Thank you

Frances
Starting Member

20 Posts

Posted - 2005-03-01 : 06:54:17

Well, eventually I solved the problem... It was indeed the path. Xp_cmdshell was looking on the server when the SQL file was on my own computer. It explains why he couldn't find it

Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources