Firing User defined Function from Select, without using function name, is this possib

Hi all, Can anyone tell me if it is possible to fire a user defined
function in SQL Server directly from an ordinary select function.
Example:
I have a function fx_Str_Title_Case(varchar). (change string to title
case, caps first letter of each word in sentence).
At present I call this as follows:
SELECT fx_Str_Title_Case(aColumn) AS Result
FROM aTable
I wont to know if I can call this like this:
SELECT aColumn AS Result
FROM aTable
to get the same result?
ThanksPut your function in a view and query the view.
David Portas
SQL Server MVP
--|||Thanks, I though of that, but was wondering if there is any way to
trigger the function based on constraints on the table or something
like.|||You can use a function in a constraint but constraints are referenced
only for updates, not for a SELECT.
David Portas
SQL Server MVP
--|||You can create a computed column with the UDF call.
CREATE TABLE Test(name VARCHAR(15), ProperName as (dbo.Proper(name)))
INSERT INTO Test Values('abc xyz')
INSERT INTO Test Values('abc XYZ aVC')
SELECT * FROM Test
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
<philip.mckee@.pramerica.ie> wrote in message
news:1123672753.295824.65610@.g44g2000cwa.googlegroups.com...
> Thanks, I though of that, but was wondering if there is any way to
> trigger the function based on constraints on the table or something
> like.
>|||On 10 Aug 2005 04:15:50 -0700, David Portas wrote:

>Put your function in a view and query the view.
Or in a computed column in the table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||What is wrong with specifying the function, and how would you know what
function is beging "fired"?
<philip.mckee@.pramerica.ie> wrote in message
news:1123672116.950885.206380@.g49g2000cwa.googlegroups.com...
> Hi all, Can anyone tell me if it is possible to fire a user defined
> function in SQL Server directly from an ordinary select function.
> Example:
> I have a function fx_Str_Title_Case(varchar). (change string to title
> case, caps first letter of each word in sentence).
> At present I call this as follows:
> SELECT fx_Str_Title_Case(aColumn) AS Result
> FROM aTable
> I wont to know if I can call this like this:
> SELECT aColumn AS Result
> FROM aTable
> to get the same result?
> Thanks
>

Firing subscriptions from rss scripts

I have set up a report with a standard email subscription. The report is set
up as "renderfrom snapshot", the subscription is set up with a
snapshotUpdated trigger.
When I create a snapshot by checking the "create snapshot when apply is
pressed", then the emails are generated just fine. I have other subscriptions
that run a similar basis that send emails OK.
If I create a report snapshot using a scripting file the snapshot is created
but the emails are not sent. There is nothing unusual in the trace files.
I've tried using existing subscriptions, new ones created manually or by
script, and back-dated shared schedules but still I get no emails
Has anyone else managed to do this ? And if so then how ?
I'm running W2K3, SQL 2000 SP3a, all bog standard out of the boxThere should be no difference in calling the UpdateReportExecutionSnapshot
from script or by doing it from the ReportManager. Is it possible you are
going against another server (a test server perhaps?)
You could try this. Turn off the report server service. Run your script.
See if there is a row in the event table with event type "snapshotUpdated".
Try it then from ReportManager. If they are both generating the event then
you will need to look in the log file to try and determine what is happening
to the event.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Oliver C" <Oliver C@.discussions.microsoft.com> wrote in message
news:240316BD-D0C7-434C-B35B-0B877F01B731@.microsoft.com...
>I have set up a report with a standard email subscription. The report is
>set
> up as "renderfrom snapshot", the subscription is set up with a
> snapshotUpdated trigger.
> When I create a snapshot by checking the "create snapshot when apply is
> pressed", then the emails are generated just fine. I have other
> subscriptions
> that run a similar basis that send emails OK.
> If I create a report snapshot using a scripting file the snapshot is
> created
> but the emails are not sent. There is nothing unusual in the trace files.
> I've tried using existing subscriptions, new ones created manually or by
> script, and back-dated shared schedules but still I get no emails
> Has anyone else managed to do this ? And if so then how ?
> I'm running W2K3, SQL 2000 SP3a, all bog standard out of the box|||In fact I was connected to the wrong server!. I have reporting services
running against an sql instance on my desktop, and a separate reporting
services on the server running against sql instance on the server. I was
expecting the -s parameter on the rs.exe command to point me at the server,
but in fact the script runs against the local instance. Is this a bug ?
If I run the scripts on the server itself (which is where they will run in
production) then they hit the correct instance
"Daniel Reib [MSFT]" wrote:
> There should be no difference in calling the UpdateReportExecutionSnapshot
> from script or by doing it from the ReportManager. Is it possible you are
> going against another server (a test server perhaps?)
> You could try this. Turn off the report server service. Run your script.
> See if there is a row in the event table with event type "snapshotUpdated".
> Try it then from ReportManager. If they are both generating the event then
> you will need to look in the log file to try and determine what is happening
> to the event.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Oliver C" <Oliver C@.discussions.microsoft.com> wrote in message
> news:240316BD-D0C7-434C-B35B-0B877F01B731@.microsoft.com...
> >I have set up a report with a standard email subscription. The report is
> >set
> > up as "renderfrom snapshot", the subscription is set up with a
> > snapshotUpdated trigger.
> >
> > When I create a snapshot by checking the "create snapshot when apply is
> > pressed", then the emails are generated just fine. I have other
> > subscriptions
> > that run a similar basis that send emails OK.
> >
> > If I create a report snapshot using a scripting file the snapshot is
> > created
> > but the emails are not sent. There is nothing unusual in the trace files.
> > I've tried using existing subscriptions, new ones created manually or by
> > script, and back-dated shared schedules but still I get no emails
> >
> > Has anyone else managed to do this ? And if so then how ?
> >
> > I'm running W2K3, SQL 2000 SP3a, all bog standard out of the box
>
>

Firing DTS through Window Based Forms in VB.net

Here is what I've got so far...for some reason it's not firing off:

<code>

PrivateSub Button1_Click(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles Button1.Click

Dim dtsp2AsNew DTS.Package

dtsp2.LoadFromSQLServer("jfgp34", "sa", "@.jfgp#1", DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "", "", "", "Test Northwind", "")

dtsp2.Execute()

EndSub

</code>

Any one with ideas helps out alot. Thanks in advance everyone.

RB

Check out the DTS Cookbook for .NET,http://www.sqldev.net/dts/DotNETCookBook.htm
|||This approach is more SQL server centric, and less reliant onVB.NET/C#.NET. It's basically a technique for triggering a DTS packagefrom a stored procedure, which can run from your .NET program withoutmuch effort.
http://www.mssqlcity.com/FAQ/Devel/DTSviaQA.htm
With that said, I've only used a similar technique to the one referenced in the previous post.
Jason
|||

Jason,

Thanks for the site. That looks like it is going to do the trick I'll let you know if I need any thing else.

Thanks again.

RB

Firing already made DTS Package -- from vb.net

Someone please help me with this.

I'm trying to fire off an already created DTS package. This package is stored within SQL Server's -- underneith the Data Transformation Services / Local Packages section.

HOW CAN I FIRE THIS OFF FROM A VB .NET APPLICATION

I'm familiar with strored procedures and using them in vb.net so if somone could lead me down that road I would be very much appriciated.

Thanks in advance everyone,

RB

Try this url you may find your solution. Hope this helps.

http://www.sqldts.com

|||

I have this in my stored proc already:

Here is what I am trying to run within my stored proc:

dtsrun /F'CSI_DTSPackages/GTProPaymentExtract.dts'/N'GTProPaymentExtract'/M''

PLEASE TELL ME WHAT I'M DOING WRONG!!!

Thanks in advance everyone.

RB

|||

I gave you Darren Green's site because if you can do it in DTS he may have done it. I have found another link, it may solve your problem. Hope this helps.

http://www.vbdotnetheaven.com/Code/Jun2003/2070.asp

|||

here a stored procedure I got from the DTS site. As this fires off a DTS package on the server your path will have to be a UNC path. In my case the path is source to a file I am reading data from.

CREATE PROCEDURE spExecuteDTSPKG
@.Server varchar(255),
@.PkgName varchar(255), -- Package Name (Defaults to most recent version)
@.ConnName varchar(25) = '',
@.XLSPath varchar(255) = '', -- Path to Source File
@.ServerPWD varchar(255) = Null, -- Server Password if using SQL Security to load Package (UID is SUSER_NAME())
@.IntSecurity bit = 1, -- 0 = SQL Server Security, 1 = Integrated Security
@.PkgPWD varchar(255) = '' -- Package Password
AS
SET NOCOUNT ON

DECLARE @.hr int, @.ret int, @.oPKG int, @.Cmd varchar(1000), @.Connection varchar(255)
Select @.Connection = 'Connections.Item(' + @.ConnName + ').DataSource'
Select @.ret = 0

-- Create a Pkg Object
EXEC @.hr = sp_OACreate 'DTS.Package', @.oPKG OUTPUT

SET @.Cmd = 'LoadFromSQLServer("' + @.Server +'", "", "", 256, "' + @.PkgPWD + '", , , "' + @.PkgName + '")'

EXEC @.hr = sp_OAMethod @.oPKG, @.Cmd, NULL

EXEC @.hr = sp_OASetProperty @.oPKG, @.Connection, @.XLSPath
If @.hr <> 0
BEGIN
PRINT '*** OASetProperty for Connection Failed***'
EXEC sp_OAGetErrorInfo @.oPkg
RETURN
END

-- Execute Pkg
EXEC @.hr = sp_OAMethod @.oPKG, 'Execute'

-- Unitialize the Pkg
EXEC @.hr = sp_OAMethod @.oPKG, 'UnInitialize'

-- Clean Up
EXEC @.hr = sp_OADestroy @.oPKG

RETURN @.ret
GO

firing a trigger on someone's birthday

Hi,
Could somebody help me create a trigger which should be executed when
someone's birthday in months fall in range, like between 18 months and
36 months.
If one falls in that range, the trigger should set a value for that
person to 1.I don't see how or why a trigger would be appropriate for this. Put the
birthdate in your table (and the created / updated date if that's
important) then put the range indicator in a view. You can use the
DATEDIFF function to calculate it:
CASE WHEN
DATEDIFF(MONTH, birthdate, CURRENT_TIMESTAMP)
BETWEEN -18 AND 18
THEN 1 ELSE 0
END
(or use created_date in place of CURRENT_TIMESTAMP if that was what you
meant.)
David Portas
SQL Server MVP
--|||dportas@.gmail.com wrote:
> I don't see how or why a trigger would be appropriate for this. Put the
> birthdate in your table (and the created / updated date if that's
> important) then put the range indicator in a view. You can use the
> DATEDIFF function to calculate it:
> CASE WHEN
> DATEDIFF(MONTH, birthdate, CURRENT_TIMESTAMP)
> BETWEEN -18 AND 18
> THEN 1 ELSE 0
> END
> (or use created_date in place of CURRENT_TIMESTAMP if that was what you
> meant.)
>
Hello David,
The thing is, that the value that should be set to 1 or 0 is in another
table.
I have a view which calculates one age in months. When getting the data
i split up the data in ranges uses a where clause. So i have different
recordsets of subjects where the ages are between 18 and 36, 36 and 52,
etc. What i'm trying to do is when a subject falls in the second range,
like 36 and 52 because he's getting older, i want to set a value in
another table to 1.
I hope i'm making any sense|||Hi Jason !
As the age of people hardly change during the day ;-) a trigger would
be IMHO the wrong solution. Setup a daily / nightly job which updates
the values of the table rather than doing this with a trigger. If the
nightly job is too seldom for you you can schedule the job to run more
often.
HTH, jens Suessmeyer.

Firing a java application from stored procedure

Hey all,
I've got a question and after doing some research I've found only a vague reference but no clear answer.

I have a java app that will be passing parameters to my stored procedure. I'll grab the requested info from the tables but instead of sending it back to the java app that sent the request, I need to send it to a "different" java app (the second java app will not be running at the time).

Can someone point me to a good source for executing java applications from a stored procedure?

Thanks in advance ...
tamSee this http://www.onjava.com/pub/a/onjava/2003/08/13/stored_procedures.html link is any help.

Another link http://www.sswug.org/searchresults.asp%3Fkeywordstofind%3Djava,%2520sto red%2520procedures for information.

firing a dts package's execution

Hi all,
I am using Sql Server 2000. I need to create a very simple app which when
clicked would fire the execution of a dts package on the sql server. The app
itself would sit on the client machine. Is this possible? If so, how?
Thanks in advance.someone wrote:
> Hi all,
> I am using Sql Server 2000. I need to create a very simple app which wh
en
> clicked would fire the execution of a dts package on the sql server. The a
pp
> itself would sit on the client machine. Is this possible? If so, how?
I tried this a month back. There are a number of solutions but I found
that there were alot of security/permissions issues that prevented me
from triggering a DTS package from an application. The most promising
solution I thought was the triggering of a job from within a stored
procedure using sp_start_job. I finally got the permissions worked out
to execute the job but then the job wouldn't execute the DTS.
Ultimately I gave up and went to a scheduled job solution that ran every
10 minutes. I'm sure it's possible to trigger a DTS but my
organization's security policies (which are there for a reason) made
that difficult to accomplish.
Anyway, google for sp_start_job, you should get alot of information that
will help you out.
gorf|||someone wrote:
> Hi all,
> I am using Sql Server 2000. I need to create a very simple app which wh
en
> clicked would fire the execution of a dts package on the sql server. The a
pp
> itself would sit on the client machine. Is this possible? If so, how?
> Thanks in advance.
>
Some combination of xp_cmdshell and DTSRUN would probably do it. Both
are documented in BOL.|||someone wrote:
> Hi all,
> I am using Sql Server 2000. I need to create a very simple app which wh
en
> clicked would fire the execution of a dts package on the sql server. The a
pp
> itself would sit on the client machine. Is this possible? If so, how?
> Thanks in advance.
I've just added something to my VB.Net app something that should also
work from VB 6, and anything that can use COM. Add a reference to the
Microsoft DTSPackage Object Library. Then it's a couple of lines like:
Dim pkg As New DTS.Package2
pkg.LoadFromSQLServer("SERVERNAME", "", "",
DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection, "",
"", "", "PACKAGENAME", "")
AddHandler pkg.OnError, AddressOf RecordExtractError
pkg.Execute()
If you need more/something else, it might help if you tell us what the
app is being developed in (incidentally I think the above can also be
written as a script file)