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 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 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)