calling an external procedure - 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

SQL Server 2005 Forums

Transact-SQL (2005)

calling an external procedure

Author

Topic

Guestuser18
Starting Member

34 Posts

Posted - 2010-04-23 : 06:27:04

Hi

I need to write a script to call an external procedure and load it into SQL Server.

What is the correct syntax to call it.

I know how to call a procedure if its already in sql server but not how to do it if its external

Any help would be great

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 06:30:44

by external do you mean its in some other server? if yes then use openrowset
(see method 2 below)

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/26/select-columns-from-exec-procedure-name-is-this-possible.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Guestuser18
Starting Member

34 Posts

Posted - 2010-04-23 : 06:34:02

Hi

No it will be on the sql server but I need to call it to load it into the actual database.

Hence it could be in C:/Document/etc

I then need to load that into the database using a script.

Thanks for the suggestion anyway.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 06:42:49

use sqlcmd

http://msdn.microsoft.com/en-us/library/ms180944.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Guestuser18
Starting Member

34 Posts

Posted - 2010-04-23 : 06:47:19

Thanks for the reply

I'm afraid that suggestion doesnt help

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 08:11:39

seems like what you need is a .bat file which does creation of procedure from .sql file and then executes this. i cant understand the reason for such a reqmnt though

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Guestuser18
Starting Member

34 Posts

Posted - 2010-04-23 : 08:15:24

Yeah that would be perfect what you suggested.

We are doing this because we will hand over to a customer and all they need to do is then run 1 file which then adds the stored procedures to the database, that is what we would like.

ideally EXEC sp_xyz(c:\myScript.SQL)

but if I cant do that I would settle for a batch file

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 08:19:10

then isnt it better to take a backup of db with all objects/data and telling them to restore it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Guestuser18
Starting Member

34 Posts

Posted - 2010-04-23 : 08:22:51

I would prefer to what I have suggested.

If you have an idea of how to do it, that would be great. :-)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 08:26:54

just right click on databse and choose backup from sql mgmnt studio giving paths.then send across mdf,ldf files and tell them to restore

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Guestuser18
Starting Member

34 Posts

Posted - 2010-04-23 : 08:30:25

Hi

I dont want to restore...

I want to try and run the file in using a script.

Someone suggested this code but it doesnt accept the word 'GO' in the file:

declare @the_script varchar(max)

select @the_script = f.BulkColumn
from openrowset
(
bulk 'D:\filename.prc',
single_clob
) f

exec (@the_script)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources