Creating UDF?

I find PLENTY of examples of User Defined functions. However, I
struggle to see how/where to SAVE this function! I can go under the
database, Programmability, Functions, Scalar Functions, and create a
scalar function. However, when I click SAVE I get prompted to save the
.sql file to disk. If I close that (query) tab, then the function is
gone as well.
How do I persist the function in a database?Once you have coded your function, you need to execute it. When you use
Programmability > Functions > New > Scalar-valued Function, what SSMS is
doing is just giving you a template that you fill out. Once done, you need
to execute it (using the Execute button on the toolbar). This will then
persist it. The "Save" option is to save what you have written into disk.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
<mikes@.vmsmailingservices.com> wrote in message
news:1132627531.702420.199000@.z14g2000cwz.googlegroups.com...
>I find PLENTY of examples of User Defined functions. However, I
> struggle to see how/where to SAVE this function! I can go under the
> database, Programmability, Functions, Scalar Functions, and create a
> scalar function. However, when I click SAVE I get prompted to save the
> .sql file to disk. If I close that (query) tab, then the function is
> gone as well.
> How do I persist the function in a database?
>

Creating UDF In SQL Server 2005?

I find PLENTY of examples of User Defined functions. However, I
struggle to see how/where to SAVE this function! I can go under the
database, Programmability, Functions, Scalar Functions, and create a
scalar function. However, when I click SAVE I get prompted to save the
.sql file to disk. If I close that (query) tab, then the function is
gone as well.
How do I persist the function in a database? I should note that this is
for SQL Server 2005.<mikes@.vmsmailingservices.com> wrote in message
news:1132627572.039473.90980@.g47g2000cwa.googlegroups.com...
> I find PLENTY of examples of User Defined functions. However, I
> struggle to see how/where to SAVE this function! I can go under
the
> database, Programmability, Functions, Scalar Functions, and create
a
> scalar function. However, when I click SAVE I get prompted to save
the
> .sql file to disk. If I close that (query) tab, then the function
is
> gone as well.
> How do I persist the function in a database? I should note that
this is
> for SQL Server 2005.
>
mikes,
The .sql files are just text files with another extension. Of
course you want to save them as your scripts, but if you want to
check out your stored procedures and functions in the database,
then:
SELECT R1.*
FROM INFORMATION_SCHEMA.ROUTINES AS R1
Sincerely,
Chris O.|||Hi Mike,
When you run the text that you have just created then teh SQL server creates
the object in the database. The text is no longer needed by the SQL Server.
You should however save this text to a file and that file can be stored
anywhere you like
kind regards
Greg O
--
Looking to use CLR in SQL 2005. Try some pre-build CLR Functions and SP
AGS SQL 2005 Utilities, over 20+ functions
http://www.ag-software.com/?tabid=38
<mikes@.vmsmailingservices.com> wrote in message
news:1132627572.039473.90980@.g47g2000cwa.googlegroups.com...
>I find PLENTY of examples of User Defined functions. However, I
> struggle to see how/where to SAVE this function! I can go under the
> database, Programmability, Functions, Scalar Functions, and create a
> scalar function. However, when I click SAVE I get prompted to save the
> .sql file to disk. If I close that (query) tab, then the function is
> gone as well.
> How do I persist the function in a database? I should note that this is
> for SQL Server 2005.
>|||Why save it when sql will give you the create statement any time you need
it?
William Stacey [MVP]
"GregO" <grego@.community.nospam> wrote in message
news:e8rKpwx7FHA.2816@.tk2msftngp13.phx.gbl...
> Hi Mike,
> When you run the text that you have just created then teh SQL server
> creates the object in the database. The text is no longer needed by the
> SQL Server. You should however save this text to a file and that file can
> be stored anywhere you like
> kind regards
> Greg O
> --
> Looking to use CLR in SQL 2005. Try some pre-build CLR Functions and SP
> AGS SQL 2005 Utilities, over 20+ functions
> http://www.ag-software.com/?tabid=38
> <mikes@.vmsmailingservices.com> wrote in message
> news:1132627572.039473.90980@.g47g2000cwa.googlegroups.com...
>|||Hello mikes@.vmsmailingservices.com,

> I find PLENTY of examples of User Defined functions. However, I
> struggle to see how/where to SAVE this function! I can go under the
> database, Programmability, Functions, Scalar Functions, and create a
> scalar function. However, when I click SAVE I get prompted to save the
> .sql file to disk. If I close that (query) tab, then the function is
> gone as well.
> How do I persist the function in a database? I should note that this
> is for SQL Server 2005.
Rephrasing some of the answers you've gotten thus far, as soon as you execut
e
the CREATE FUNCTION statement, a "complied" version of the function is commi
tted
to the database. There's no need to save the text version of it, since, when
you issued that same CREATE FUNCTION statement, SQL Server also saved that
to the database.
Make sense?
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Hi William
Seriously?
What happens when some deletes it or changes the structure. You need
something to compare it to. There so many reasons for keeping the command
syntax and next to none for not keeping it. When in doubt keep the command.
kind regards
Greg O
--
Looking to use CLR in SQL 2005. Try some pre-build CLR Functions and SP
AGS SQL 2005 Utilities, over 20+ functions
http://www.ag-software.com/?tabid=38
"William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
news:OVdiJWy7FHA.472@.TK2MSFTNGP15.phx.gbl...
> Why save it when sql will give you the create statement any time you need
> it?
> --
> William Stacey [MVP]
> "GregO" <grego@.community.nospam> wrote in message
> news:e8rKpwx7FHA.2816@.tk2msftngp13.phx.gbl...
>|||>
> Rephrasing some of the answers you've gotten thus far, as soon as you exec
ute
> the CREATE FUNCTION statement, a "complied" version of the function is com
mitted
> to the database. There's no need to save the text version of it, since, wh
en
> you issued that same CREATE FUNCTION statement, SQL Server also saved that
> to the database.
> Make sense?
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
This helps a lot, thanks everyone!
a couple more related questions, based on your responses...
How does the DB engine manage a new function of the same name? Prompted
to override?
How can one determine a list of the functions in a database?
How is (function) debugging done, if the function is internal to the DB
Engine?|||Hello mikes@.vmsmailingservices.com,

> How does the DB engine manage a new function of the same name?
> Prompted to override?
No, unlike other development tools, most of the SQL Server tools *trusts*
the operator to know what he or she is doing. Most shops have a CVS (or simi
lar)
set up for storing scripts for objects. MS is offering one with team system
too. Native integration of that into SQL Server is probably a couple of vers
ions
away I suspect.

> How can one determine a list of the functions in a database?
select * from sys.objects where type_desc like '%_FUNCTION'

> How is (function) debugging done, if the function is internal to the
> DB Engine?
Like any other environment, the execution engine know that it has breakpoint
s
that it yields control to another process. It just so happens that SQL Serve
r's
engine uses a common Visual Studio API for debugging. No magic.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

creating trigger to auto set create/modify dates

Hi,

I'm a newbie to sql server and this may be a really dumb question for
some you. I'm trying to find some examples of sql server triggers that
will set columns (e.g. the created and modified date columns) if the row
is being inserted and set a column (e.g. just the modified date column)
if the row is being updated.

I know how to do this in oracle plsql. I would define it as a before
insert or update trigger and reference old and new instances of the
record. Does sql server have an equivalent? Is there a better way to do
this in sql server?

Thanks
eric

this is what i do in oracle that i'm trying to do in sqlserver...

CREATE OR REPLACE TRIGGER tr_temp_biu
before insert or update
on temp
referencing old as old new as new
for each row
begin
if inserting then
:new.created_date := sysdate;
end if;
:new.modified_date := sysdate;
end tr_temp_biu;On Thu, 07 Oct 2004 13:02:41 -0400, efinney wrote:

>Hi,
>I'm a newbie to sql server and this may be a really dumb question for
>some you. I'm trying to find some examples of sql server triggers that
>will set columns (e.g. the created and modified date columns) if the row
>is being inserted and set a column (e.g. just the modified date column)
>if the row is being updated.
>I know how to do this in oracle plsql. I would define it as a before
>insert or update trigger and reference old and new instances of the
>record. Does sql server have an equivalent? Is there a better way to do
>this in sql server?
>Thanks
>eric
>this is what i do in oracle that i'm trying to do in sqlserver...
(snip)

Hi Eric,

Don't try to do a one on one translation from Oracle to SQL Server. The
differences are too big (especially when it comes to triggers and other
non-ANSI-standard syntax).

For the creation date, no trigger is needed. Simply use a default. Use the
same default for the modified date if you want that set at the time a row
is inserted as well. If you rather leave the moodified_date NULL until the
row actually is updated, remove the default and change NOT NULL to NULL.

CREATE TABLE MyTable (KeyCol1 int NOT NULL,
KeyCol2 char(6) NOT NULL,
DataCol1 varchar(130) NULL,
DataCol2 datetime NOT NULL,
Created_Date datetime NOT NULL
DEFAULT (CURRENT_TIMESTAMP),
Modified_Date datetime NOT NULL
DEFAULT (CURRENT_TIMESTAMP),
PRIMARY KEY (KeyCool1, KeyCol2)
)

There are no BEFORE triggers in SQL Server. Only AFTER and INSTEAD OF
triggers are supported. In your case, I'd use an AFTER trigger. A very
fundamental difference that you should really be aware of, is that SQL
Server executes a trigger once per update (insert, delete) statement, with
all affected rows in the inserted and deleted pseudo-tables. All Oracle
triggers I've seen so far are processed for each individual row - a very
big difference that can lead to spectacular errors.

CREATE TRIGGER MyTrigger
ON MyTable
AFTER UPDATE
AS
-- Prevent recursion!
IF NOT UPDATE(Modified_Date)
BEGIN
UPDATE MyTrigger
SET Modified_Date = CURRENT_TIMESTAMP
WHERE EXISTS (SELECT *
FROM inserted AS i
WHERE i.KeyCol1 = MyTable.KeyCol1
AND i.KeyCol2 = MyTable.KeyCol2)
END

(untested)
UPDATE MyTable

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Creating Subscriptions

Does anyone have any good examples of programmatically creating
subscriptions using the SQLRS web service ?
rgds,
JayHere is an example for RS2000.
http://www.odetocode.com/articles/114.aspx
It gets pretty complicated if you're trying to offer all the
functionalitysql