loading a stored procedure into sql server using a .sql script

Question

Post reply

loading a stored procedure into sql server using a .sql script

guestuser31

Old Hand

Points: 336
More actions
April 23, 2010 at 5:37 am

#223746

Hi
I need a .sql script which someone can open in SQL Management Studio and then run it and it then calls some stored procedures which arent in the database yet but are sitting in somewhere like c:/ProgramFiles etc.
Can anyone suggest how to do this?
thanks

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

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

guestuser31 Old Hand Points: 336 More actions · Answer 1

Surely I cant be the first ever person to want to do this....

I just need to execute a stored procedure which is sitting in a file outside of sql server.

if anyone has te slightest idea, anything that would be great.

Toby White SSCertifiable Points: 6187 More actions · Answer 2

You cannot do this. You will first need to create the other stored procedures in the database before they can be called. Stored procedures must exist on SQL Server before they can be called.

There is a way to do what needs to be done. In your case it's just a matter of thinking about it a little differently. If you could speak more about why you want to do this we could help you do it the best way.

guestuser31 Old Hand Points: 336 More actions · Answer 3

Ah someone replied

Ok this is what Ive got

exec master.dbo.xp_cmdshell 'osql -E -SERVERNAME -d DATABASE -i ..\Procedures\PROCEDURE.prc'

Now how do I shorten the directory location so that I dont have to specifiy the full path?

The above code doesnt work because of the file location specified. However if I specifiy the full path it works

Thanks for the reply.

Toby White SSCertifiable Points: 6187 More actions · Answer 4

Your new question:

Now how do I shorten the directory location so that I dont have to specifiy the full path?

doesn't have anything to do with your original question:

I just need to execute a stored procedure which is sitting in a file outside of sql server.

To answer you new question - you would have to add the directory to the path. However, I would only do this if I thought I was going to be using this directory for CMD calls often.

Toby White SSCertifiable Points: 6187 More actions · Answer 5

It just occurred to me that you might not know how to add a directory to the path variable. Let me know if you don't and I will tell you.

guestuser31 Old Hand Points: 336 More actions · Answer 6

I guess you're talking about the Environment Variable Path.

I'm not sure that is correct as I have another batch file which uses ..\file location and this works fine.