adding or updaing 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 2000 Forums

MSDE (2000)

adding or updaing procedure

Author

Topic

hornet
Yak Posting Veteran

96 Posts

Posted - 2006-03-22 : 05:24:48

How I can add or update stored procedure by command line or a script. I have MSDE, but i haven't got SQL Server. Thanks a lot

Kristen
Test

22859 Posts

Posted - 2006-03-22 : 05:53:08

There is a command line utility called OSQL that can run scripts against SQL Server (MSDE or the "full blown" version).

Alternatively if you have the Client Tools installed (i.e. form a "Full" installation) they will be able to connect also to the MSDE database.

Kristen

hornet
Yak Posting Veteran

96 Posts

Posted - 2006-03-22 : 06:16:57

Could you give me example how i can add procedure witn name "prTest"?

Kristen
Test

22859 Posts

Posted - 2006-03-22 : 07:40:07

If you install the Client Utilities (which come with the full SQL Server install) you will get the "Books Online" = known as "BoL".

As you are asking about MSDE I presume you haven't got these, however BoL can be downloaded, for free, from Microsoft's web site. BoL is an exceptionally good resource.

In there have a look under the Index heading "osql utility" and you'll see plenty of information about how to connect to your MSDE database. In particular note the "-i" parameter which lets you "input" the script from a text file.

Also check out "CREATE PROCEDURE" in the Index for syntax and example for creating a Stored Procedure.

Note: I f you need to be doing some serious development and have not got access to the CLient Utilities via a full-blown install CD, Microsoft sell a "Developers Version" very (relatively speaking anyway!) cheaply. This is the complete Enterprise Version of SQL Server (normally cira $20,000 but yours for around $50!) with the restriction that you cannot use it for deployment of your applications, only development.

Kristen

celsius
Starting Member

26 Posts

Posted - 2006-03-23 : 03:34:13

Example:

C:\> osql -S ComputerName\MSDEInstanceName -d DatabaseName -E -i C:\Script.sql

WHERE Script.sql contains the following text:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[prTest]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[prTest]
GO

CREATE PROCEDURE dbo.prTest

AS

SELECT Name FROM dbo.Table

GO

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-23 : 03:40:17

Alternatively google for free Query Analyser tool. Like this

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

celsius
Starting Member

26 Posts

Posted - 2006-03-23 : 03:42:47

^ Cool!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources