Calling SP on linked server inside job

I'm trying to call a SP that is an object on a linked server (server B)
from within a job step on a job on server A. The job appears to start
running, but then neither fails nor succeeds. The procedure never
executes b/c when I check the results, they aren't there. When I run
the same statement from isqlw, it succeeds. When I try to view any
errors on the job history there are none - There is no job history to
view from the job. My syntax from inside the job is below....any
ideas?
EXEC [linkedserver].[database].[dbo].usp_storedprocedure
Didn't want it to fall to far - anyone have ideas?
|||Anyone?
unc27932@.yahoo.com wrote:
> Didn't want it to fall to far - anyone have ideas?
|||Try running a trace when you execute the job to help narrow
down what the problems may be.
-Sue
On 8 Aug 2005 06:34:41 -0700, unc27932@.yahoo.com wrote:

>I'm trying to call a SP that is an object on a linked server (server B)
>from within a job step on a job on server A. The job appears to start
>running, but then neither fails nor succeeds. The procedure never
>executes b/c when I check the results, they aren't there. When I run
>the same statement from isqlw, it succeeds. When I try to view any
>errors on the job history there are none - There is no job history to
>view from the job. My syntax from inside the job is below....any
>ideas?
>EXEC [linkedserver].[database].[dbo].usp_storedprocedure
sql

Calling SP on linked server inside job

I'm trying to call a SP that is an object on a linked server (server B)
from within a job step on a job on server A. The job appears to start
running, but then neither fails nor succeeds. The procedure never
executes b/c when I check the results, they aren't there. When I run
the same statement from isqlw, it succeeds. When I try to view any
errors on the job history there are none - There is no job history to
view from the job. My syntax from inside the job is below....any
ideas?
EXEC [linkedserver].[database].[dbo].usp_storedprocedureDidn't want it to fall to far - anyone have ideas?|||Anyone?
unc27932@.yahoo.com wrote:
> Didn't want it to fall to far - anyone have ideas?|||Try running a trace when you execute the job to help narrow
down what the problems may be.
-Sue
On 8 Aug 2005 06:34:41 -0700, unc27932@.yahoo.com wrote:

>I'm trying to call a SP that is an object on a linked server (server B)
>from within a job step on a job on server A. The job appears to start
>running, but then neither fails nor succeeds. The procedure never
>executes b/c when I check the results, they aren't there. When I run
>the same statement from isqlw, it succeeds. When I try to view any
>errors on the job history there are none - There is no job history to
>view from the job. My syntax from inside the job is below....any
>ideas?
>EXEC [linkedserver].[database].[dbo].usp_storedprocedure

Calling SP on linked server inside job

I'm trying to call a SP that is an object on a linked server (server B)
from within a job step on a job on server A. The job appears to start
running, but then neither fails nor succeeds. The procedure never
executes b/c when I check the results, they aren't there. When I run
the same statement from isqlw, it succeeds. When I try to view any
errors on the job history there are none - There is no job history to
view from the job. My syntax from inside the job is below....any
ideas?
EXEC [linkedserver].[database].[dbo].usp_storedprocedureDidn't want it to fall to far - anyone have ideas?|||Anyone?
unc27932@.yahoo.com wrote:
> Didn't want it to fall to far - anyone have ideas?|||Try running a trace when you execute the job to help narrow
down what the problems may be.
-Sue
On 8 Aug 2005 06:34:41 -0700, unc27932@.yahoo.com wrote:
>I'm trying to call a SP that is an object on a linked server (server B)
>from within a job step on a job on server A. The job appears to start
>running, but then neither fails nor succeeds. The procedure never
>executes b/c when I check the results, they aren't there. When I run
>the same statement from isqlw, it succeeds. When I try to view any
>errors on the job history there are none - There is no job history to
>view from the job. My syntax from inside the job is below....any
>ideas?
>EXEC [linkedserver].[database].[dbo].usp_storedprocedure

Calling SP Inside the SP

hai guys
how should we have to cal the store procedure inside the same store procedure.
for Example
Create procedure A
as
Begin
Select * from mytable
execute A
end.
is this the correct oneyes that is correct.
but the level of nesting is 32 if u exceed this u will get this error:
"Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)."|||Got an error object does not exists, while compiling. how to compile|||can u post the actual script ur using?
and does the table u r trying to access, exist in the database?

Calling one application from SQL Server

Is it possble to automatically call an application when another application makes an INSERT from inside sql server? If its possible - how do I do that?

Thx in advance

/MiZzaYou can get SQL to make COM style calls...look up extended procedures. However, you'd still need a way to identify what the calling application was.|||Main stored procedures to use:

sp_OACreate - Creates an instance of the OLE object on an instance of Microsoft® SQL Server?. (e.g. COM component)

sp_OAMethod
sp_OADestroy

Support stored procedures to use:

sp_OAGetErrorInfo
sp_OAGetProperty
sp_OASetProperty
sp_OAStop

Calling DTS from ASP throught ISA Firewall

I am trying to execute a DTS package from our external web site. The package resides on an SQL Server inside the ISA firewall. I can connect to the SQL Server via ADO but everything else is blocked so the following code will not work since I believe it uses Windows Integrated Security:

Dim oPkg
Dim objCID
Dim objSID
Dim objOrderID

Set oPkg = CreateObject("DTS.Package")

Set objCID = oPkg.GlobalVariables.New("@.CID")
Set objSID = oPkg.GlobalVariables.New("@.SID")
Set objOrderID = oPkg.GlobalVariables.New("@.OrderID")

oPkg.LoadFromSQLServer "ServerName", "UserName", "Password", , , , , "MSIOrder"

objCID.Value = "2303"
objSID.Value = "822105614"
objOrderID.Value = "323"
oPkg.GlobalVariables.Add objCID
oPkg.GlobalVariables.Add objSID
oPkg.GlobalVariables.Add objOrderID

oPkg.Execute
Set oPkg = Nothing

Is there a way to call this DTS package via an ADO connection? And of course, to complicate things, I need to pass the DTS package three global variables.

Lastly, modifying the ISA config is not an option unless I can be absolutely sure that it would not alter our current level of security.

Thanks in advance,
JimDTS? Ick. Anyway, are you sure you have the DTS DLLs installed on the client machine (this case the web server)? I believe you need to install Enterprise Manager on the machine for those DLLs to get installed. No separate installation as far as I know.

Also, what error message are you getting?
~Mattsql

Calling a Stored Procedure inside mysql.

Hi, I am assisting someone trying to look up information for the following
question.
Please keep in mind that he is computer tech savvy and I am not. If this
question does not apply to you could you please direct me to the department
that would best handle this question.
How do I call a stored procedure inside my sql with a vba script inside
Microsoft Outlook?
Thank you for taking the time.MySQL, this forum is for MS SQL. You may need to post your question at
www.mysql.com and they have a forum there as well.
"Computer Newbie" <Computer Newbie@.discussions.microsoft.com> wrote in
message news:DB83EE5A-1D32-4043-BD31-2FB6AA228767@.microsoft.com...
> Hi, I am assisting someone trying to look up information for the following
> question.
> Please keep in mind that he is computer tech savvy and I am not. If this
> question does not apply to you could you please direct me to the
> department
> that would best handle this question.
> How do I call a stored procedure inside my sql with a vba script inside
> Microsoft Outlook?
> Thank you for taking the time.
>

Calling a stored procedure inside another stored procedure (or "nested stored procedures")

Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie

exec dbo.DeriveStatusID 'Created'

returns an int value as 1

(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')

but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:

exec dbo.AddProduct_Insert 'widget1'

which currently performs:
SET @.statusID = (SELECT statusID FROM statusList WHERE statusName='Created')
INSERT INTO Products (productname, statusID) VALUES (''widget1', @.statusID)

I want to simply the insert to perform (in one sproc):

SET @.statusID = EXEC deriveStatusID ('Created')
INSERT INTO Products (productname, statusID) VALUES (''widget1', @.statusID)

This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @.statusID in this example).

My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).

Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?

Two steps:

1. Create the SP to return new ID like:

CREATE PROCEDURE [dbo].[deriveStatusID] @.Created Varchar(100),

@.statusIDint OUTPUT

AS

SELECT@.statusID =statusID FROM statusList WHEREstatusName=@.Created

2. Modify your second SP as:

....

Declare @.NewStatusId Int

EXECderiveStatusID 'Created', @.NewStatusId OUTPUT

INSERT INTO Products (productname, statusID) VALUES (''widget1',@.NewStatusId)

Calling a SP inside a cursor loop..

I have SP, which has a cursor iterations. Need to call another SP for
every loop iteration of the cursor. The pseudo code is as follows..

Create proc1 as
Begin

Variable declrations...

declare EffectiveDate_Cursor cursor for
select field1,fld2 from tab1,tab2 where tab1.effectivedate<Getdate()
--/////Assuming the above query would result in 3 records
Open EffectiveDate_Cursor
Fetch next From EffectiveDate_Cursor Into @.FLD1,@.FLD2
begin
/*Calling my second stored proc with fld1 as a In parameter
and Op1 and OP2 Out parameters*/
Exec sp_minCheck @.fld1, @.OP1 output,@.OP2 output
Do something based on Op1 and Op2.
end
While @.@.Fetch_Status = 0
Fetch next From EffectiveDate_Cursor Into @.FLD1,@.FLD2
/* Assume If loop count is 3.
and If the Fetch stmt is below the begin Stmt, the loop iterations are
4 else the loop iterations are 2*/
begin
/*Calling my second stored proc with fld1 as a In parameter and Op1
and OP2 Out parameters*/
Exec sp_minCheck @.fld1, @.OP1 output,@.OP2 output
Do something based on Op1 and Op2.
end

The problem I had been facing is that, the when a stored proc is called
within the loop, the proc is getting into infinite loops.
Any Help would be appreciated.

Satish(satishchandra999@.gmail.com) writes:
> I have SP, which has a cursor iterations. Need to call another SP for
> every loop iteration of the cursor. The pseudo code is as follows..
> Create proc1 as
> Begin
> Variable declrations...
>...
> While @.@.Fetch_Status = 0
> Fetch next From EffectiveDate_Cursor Into @.FLD1,@.FLD2
> /* Assume If loop count is 3.
> and If the Fetch stmt is below the begin Stmt, the loop iterations are
> 4 else the loop iterations are 2*/
> begin
> /*Calling my second stored proc with fld1 as a In parameter and Op1
> and OP2 Out parameters*/
> Exec sp_minCheck @.fld1, @.OP1 output,@.OP2 output
> Do something based on Op1 and Op2.
> end
>
> The problem I had been facing is that, the when a stored proc is called
> within the loop, the proc is getting into infinite loops.

May I guess: the inner process also uses cursors?

Anyway, the proper way to program a cursor loop is:

DECLARE cur INENSITIVE CURSOR FOR
SELECT ...
-- Error handling goes here

OPEN cur

WHILE 1 = 1
BEGIN
FETCH cur INTO @.x, @.y, ...
IF @.@.fetch_status <> 0
BREAK

-- Do stuff
END

DEALLOCATE cur

By using only one FETCH statements you avoid funny errors, when you change
the cursor and forgets to change the cursor at the end of the loop. And by
checl @.@.fetch_status directly after the FETCH, you know that @.@.fetch_status
relates to that FETCH.

... and in case no one ever told you before: avoid iterations as much as
you can, and try to always work set-based. Yes, I can understand that you
want to reuse code, and if the oomplexity is high enough it may be
warranted if the number of rows in the cursor is moderate. But the cost
in performance for iterative solutions can be *enourmous*. A database
engine is simply not designed for this type of processing.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>> I have SP, which has a cursor iterations. Need to call another SP for
every loop iteration of the cursor. <<

No. You need to learn to program in SQL. All you are doing is
mimicing a 1960's 3GL magnetic tape file system . In your pseudo
code, you even refer to fields instead of columns! You put the "sp_"
prefix on procedure names!

Don't you understand that SQL is a non-procedurdal language? You
should write only a few cursors in 20 years, not two in one
application.

Your whole approach to the problem is **fundamentally** wrong.

>> The problem I had been facing is that, the when a stored proc is called within the loop, the proc is getting into infinite loops. <<

It is very hard to de-bug code that you will not show us. But when
pseudo code is this awful, I bet that the real code is a total mess.
More cursors? Dynamic SQL? Badly written procedural code with poor
coupling and cohesion?

>> Any Help would be appreciated. <<

You have no idea what you are doing. What you will get on Newsgroups
is a quick kludge to get rid of you, but not any real help. You need
to stop programming and get some education; then get some training.

Calling a script from inside another scripts

I need to call a script from inside another script. How do I do this? I don't know if it matters but I will be doing all of this from inside of isql.
Thanks for any help.Use the EXECUTE command

Example:

UPDATE tbl
SET col1 = col1 * 0.787
WHERE col2 = 'x'

IF @.@.ERROR <> 0
EXEC("isql -E -SMyServer -Q -iD:\MYScript\Error.sql")|||Where is my mind, you need to use master..xp_cmdshell

UPDATE tbl
SET col1 = col1 * 0.787
WHERE col2 = 'x'

IF @.@.ERROR <> 0
master..xp_cmdshell "isql -E -SMyServer -iD:\MYScript\Error.sql"

Calling a function inside of a function is not allowed in sql server?

We have a stored procedure that generates a unique random integer which uses
the RAND function. For data columns, you can invoke a function to create a
default value .e.x.: NEWID(). I would like to call a function that will
perform the same operations as the stored procedure.
The sql guru here says he tried to make our stored prodecure a function but
wasn't able to because sql server2k wouldn't allow him to use RAND inside
the function. Does anyone know of any workarounds, will future verions of
sql server allow this?
Thx in advance.Pass RAND() into the function (this is much the same as GETDATE()). Look up
DETERMINISTIC and NONDETERMINISTIC. See http://www.aspfaq.com/2439
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com> wrote
in message news:uK2Qeh1lEHA.3496@.TK2MSFTNGP12.phx.gbl...
> We have a stored procedure that generates a unique random integer which
uses
> the RAND function. For data columns, you can invoke a function to create a
> default value .e.x.: NEWID(). I would like to call a function that will
> perform the same operations as the stored procedure.
> The sql guru here says he tried to make our stored prodecure a function
but
> wasn't able to because sql server2k wouldn't allow him to use RAND inside
> the function. Does anyone know of any workarounds, will future verions of
> sql server allow this?
> Thx in advance.
>|||Works great, THX!!!!!!!
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uWRx%23m1lEHA.3432@.TK2MSFTNGP14.phx.gbl...
> Pass RAND() into the function (this is much the same as GETDATE()). Look
> up
> DETERMINISTIC and NONDETERMINISTIC. See http://www.aspfaq.com/2439
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com>
> wrote
> in message news:uK2Qeh1lEHA.3496@.TK2MSFTNGP12.phx.gbl...
>> We have a stored procedure that generates a unique random integer which
> uses
>> the RAND function. For data columns, you can invoke a function to create
>> a
>> default value .e.x.: NEWID(). I would like to call a function that will
>> perform the same operations as the stored procedure.
>> The sql guru here says he tried to make our stored prodecure a function
> but
>> wasn't able to because sql server2k wouldn't allow him to use RAND inside
>> the function. Does anyone know of any workarounds, will future verions of
>> sql server allow this?
>> Thx in advance.
>>
>

Calling a Stored Procedure from inside of a loop..

Hello,
I know calling a stored procedure from inside of loop is something that
we all want to aviod, and the most of times there's a better
alternative.
However, I am afraid this is one of the few exceptions that I really
have to call a stored procedure from inside of loop. Just in case you
wonder why, there is a stored procedure which takes one parameter,
namely member_ID, and I would like to test it out against about 12,000
rows in the members table.
So, basically I would like to call this stored procedures about 12,000
times, each time with a different member ID, just to make sure the
stored procedure works for each member.
I know it kinda sound really anal, but because of the complexity of the
stored procedure, mainly due to the poor database design done in years
ago (or so I blame :), despite the fact it only receives one paramter,
there have been instances that the stored procedure did not return a
row for a certain group of members.
I've been working on the stored procedure to make it handel some of
those "special" situations, so that it always returns a row, even if it
is populated with some crooked values. Now I think I've finished
taking care of all that... but, I will definitely sleep better if I can
test it out. So, here I am.
There is a While Loop in TSQL that seems like the one I should use for
this task, but the problem I am having is how to call my stored
procedure with a different member ID in each iteration.
Please remember that, for this particular situation altering the stored
procedure to query a table of member ID's instead of a single member ID
is not an option. Again, because of the complexity of the problem I
need to solve, I was not able to squeeze the query into a single Select
statement in my procedure. It contains a number of calculations, which
use different set of parameters depending on the group the member
belongs to...
Yes, this probably wasn't good idea to do all this conditional
calculations in the stored procedure, but that is how our application
works - probably a really bad design, of course which wasn't done by me
:)
All in all, it's too late to make any kind of architectural changes at
this point, and I am stuck with a task that I have to write a stored
procedure that does all the wonders and returns a row when called with
a single parameter, member ID, no matter how inefficient and slow it
gets.
Since I am trying to do this exhaustive testing for my own sake and on
my own, using a small test code outside of the database using a more
suitable programming language isn't going to be easy, either.
In other words, I need to call my stored procedure which takes one
parameter within a loop with supplying a different parameter each time,
and I need to do this in TSQL.
Thank you very much for your help in advance!Consider these options as well, if they fit your needs:
1) a user-defined function that gets called 12K times while executing a
query inside a stored proc, e.g.
SELECT MemberId, dbo.DoMembersCalc(member_ID) as CalculatedMemberValue
FROM Members
2) a calculated column
SELECT MemberId, CalculatedMemberValue
FROM Members
3) a view
SELECT MemberId, CalculatedMemberValue
FROM MemberView
You can subquery any of these statements to see if any particular
MemberId had an undesireable result in the CalculatedMemberValue
column.
jung_h_park@.yahoo.com wrote:
> Hello,
> I know calling a stored procedure from inside of loop is something that
> we all want to aviod, and the most of times there's a better
> alternative.
> However, I am afraid this is one of the few exceptions that I really
> have to call a stored procedure from inside of loop. Just in case you
> wonder why, there is a stored procedure which takes one parameter,
> namely member_ID, and I would like to test it out against about 12,000
> rows in the members table.
> So, basically I would like to call this stored procedures about 12,000
> times, each time with a different member ID, just to make sure the
> stored procedure works for each member.
> I know it kinda sound really anal, but because of the complexity of the
> stored procedure, mainly due to the poor database design done in years
> ago (or so I blame :), despite the fact it only receives one paramter,
> there have been instances that the stored procedure did not return a
> row for a certain group of members.
> I've been working on the stored procedure to make it handel some of
> those "special" situations, so that it always returns a row, even if it
> is populated with some crooked values. Now I think I've finished
> taking care of all that... but, I will definitely sleep better if I can
> test it out. So, here I am.
> There is a While Loop in TSQL that seems like the one I should use for
> this task, but the problem I am having is how to call my stored
> procedure with a different member ID in each iteration.
> Please remember that, for this particular situation altering the stored
> procedure to query a table of member ID's instead of a single member ID
> is not an option. Again, because of the complexity of the problem I
> need to solve, I was not able to squeeze the query into a single Select
> statement in my procedure. It contains a number of calculations, which
> use different set of parameters depending on the group the member
> belongs to...
> Yes, this probably wasn't good idea to do all this conditional
> calculations in the stored procedure, but that is how our application
> works - probably a really bad design, of course which wasn't done by me
> :)
> All in all, it's too late to make any kind of architectural changes at
> this point, and I am stuck with a task that I have to write a stored
> procedure that does all the wonders and returns a row when called with
> a single parameter, member ID, no matter how inefficient and slow it
> gets.
> Since I am trying to do this exhaustive testing for my own sake and on
> my own, using a small test code outside of the database using a more
> suitable programming language isn't going to be easy, either.
> In other words, I need to call my stored procedure which takes one
> parameter within a loop with supplying a different parameter each time,
> and I need to do this in TSQL.
> Thank you very much for your help in advance!|||For this sort of testing scenario, a cursor is a perfectly viable
option (of course, that goes with the usual disclaimer about avoiding
cursors in production code, they are inherently evil, and bad things
will happen to you if you forsake this advice, don't get them wet, and
never ever feed them after midnight....).
Look up cursors in the Books Online. and that should get you pointed in
the right direction.
HTH,
Stu
jung_h_park@.yahoo.com wrote:
> Hello,
> I know calling a stored procedure from inside of loop is something that
> we all want to aviod, and the most of times there's a better
> alternative.
> However, I am afraid this is one of the few exceptions that I really
> have to call a stored procedure from inside of loop. Just in case you
> wonder why, there is a stored procedure which takes one parameter,
> namely member_ID, and I would like to test it out against about 12,000
> rows in the members table.
> So, basically I would like to call this stored procedures about 12,000
> times, each time with a different member ID, just to make sure the
> stored procedure works for each member.
> I know it kinda sound really anal, but because of the complexity of the
> stored procedure, mainly due to the poor database design done in years
> ago (or so I blame :), despite the fact it only receives one paramter,
> there have been instances that the stored procedure did not return a
> row for a certain group of members.
> I've been working on the stored procedure to make it handel some of
> those "special" situations, so that it always returns a row, even if it
> is populated with some crooked values. Now I think I've finished
> taking care of all that... but, I will definitely sleep better if I can
> test it out. So, here I am.
> There is a While Loop in TSQL that seems like the one I should use for
> this task, but the problem I am having is how to call my stored
> procedure with a different member ID in each iteration.
> Please remember that, for this particular situation altering the stored
> procedure to query a table of member ID's instead of a single member ID
> is not an option. Again, because of the complexity of the problem I
> need to solve, I was not able to squeeze the query into a single Select
> statement in my procedure. It contains a number of calculations, which
> use different set of parameters depending on the group the member
> belongs to...
> Yes, this probably wasn't good idea to do all this conditional
> calculations in the stored procedure, but that is how our application
> works - probably a really bad design, of course which wasn't done by me
> :)
> All in all, it's too late to make any kind of architectural changes at
> this point, and I am stuck with a task that I have to write a stored
> procedure that does all the wonders and returns a row when called with
> a single parameter, member ID, no matter how inefficient and slow it
> gets.
> Since I am trying to do this exhaustive testing for my own sake and on
> my own, using a small test code outside of the database using a more
> suitable programming language isn't going to be easy, either.
> In other words, I need to call my stored procedure which takes one
> parameter within a loop with supplying a different parameter each time,
> and I need to do this in TSQL.
> Thank you very much for your help in advance!

Calling a Stored procedure from a UDF

Hi Everybody,
I was wondering if it is possible to call a Stored Procedure from inside a UDF. Any help is appreciated.
Thanks,
TarunI tried to create a function that called a stored procedure, it won't allow you @. execution time.

Msg 557, Level 16, State 2, Server ATLAS, Procedure fnRevenue, Line 6
Only functions and extended stored procedures can be executed from within a function.

Calling a function inside of a function is not allowed in sql server?

We have a stored procedure that generates a unique random integer which uses
the RAND function. For data columns, you can invoke a function to create a
default value .e.x.: NEWID(). I would like to call a function that will
perform the same operations as the stored procedure.
The sql guru here says he tried to make our stored prodecure a function but
wasn't able to because sql server2k wouldn't allow him to use RAND inside
the function. Does anyone know of any workarounds, will future verions of
sql server allow this?
Thx in advance.
Pass RAND() into the function (this is much the same as GETDATE()). Look up
DETERMINISTIC and NONDETERMINISTIC. See http://www.aspfaq.com/2439
http://www.aspfaq.com/
(Reverse address to reply.)
"Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com> wrote
in message news:uK2Qeh1lEHA.3496@.TK2MSFTNGP12.phx.gbl...
> We have a stored procedure that generates a unique random integer which
uses
> the RAND function. For data columns, you can invoke a function to create a
> default value .e.x.: NEWID(). I would like to call a function that will
> perform the same operations as the stored procedure.
> The sql guru here says he tried to make our stored prodecure a function
but
> wasn't able to because sql server2k wouldn't allow him to use RAND inside
> the function. Does anyone know of any workarounds, will future verions of
> sql server allow this?
> Thx in advance.
>
|||Works great, THX!!!!!!!
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uWRx%23m1lEHA.3432@.TK2MSFTNGP14.phx.gbl...
> Pass RAND() into the function (this is much the same as GETDATE()). Look
> up
> DETERMINISTIC and NONDETERMINISTIC. See http://www.aspfaq.com/2439
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com>
> wrote
> in message news:uK2Qeh1lEHA.3496@.TK2MSFTNGP12.phx.gbl...
> uses
> but
>

callin functions inside queries

im trying to make this function in tsql

create function isolatedLeptons
(@.idevent INT)
Returns TABLE
AS
Return select l.*
from lepton as l, event as e
where e.id = l.eventid and pt(l.id) > 7.0 and abs(eta(l.id))<2.4
END

GO

select * from lepton;

but it trow me this error

Msg 195, Level 15, State 10, Procedure isolatedLeptons, Line 9
'pt' is not a recognized built-in function name.

pt is defined as

create function pt
(@.idpt INT)
Returns Real
AS
BEGIN
Return ( select sqrt(a.px*a.px + a.py*a.py)
from abstractparticle as a
where @.idpt = a.id)
END

GO

Hi,

you need to use dbo.pt(l.id).

--
Regards,
Daniel Kuppitz

|||

Please do not use scalar UDFs for simple tasks like this. It is overkill in terms of performance and probably manageability too. Use the query expression inline like:

create function isolatedLeptons
(@.idevent INT)
Returns TABLE
AS
Return select l.*
from lepton as l, event as e
where e.id = l.eventid

and (select sqrt(a.px*a.px + a.py*a.py)
from abstractparticle as a
where a.id = l.id) > 7.0

and abs(eta(l.id))<2.4

Or if you want to go the UDF route use an inline TVF instead like:

create function pt
(@.idpt INT)
returns table
AS
return ( select sqrt(a.px*a.px + a.py*a.py) as val
from abstractparticle as a
where @.idpt = a.id)
GO

create function isolatedLeptons
(@.idevent INT)
Returns TABLE
AS
Return select l.*
from lepton as l, event as e
where e.id = l.eventid

and (select a.val

from pt(l.id)) > 7.0

and abs(eta(l.id))<2.4

Call stored procedure within SELECT statement

Can this be done? I want to call a stored procedure from inside a select statement. Since you can nest select statements, I thought it might be possible but I have no idea how to do it.

USE NORTHWIND
GO

CREATE TABLE tbA (
Item int NOT NULL,
Value int NOT NULL
) ON [PRIMARY]

GO

INSERT INTO tbA (Item, Value)
SELECT 1, 10 UNION ALL
SELECT 2, 5 UNION ALL
SELECT 3, 2
GO

CREATE PROCEDURE usp_SquareIt

@.iItem int

AS

declare @.iValue int
SELECT @.iValue = Value FROM tbA
SELECT @.iValue * @.iValue AS Result

GO

SELECT Item,
EXECUTE usp_SquareIt Item AS Squared -- can this be done
FROM tbA
GO

DROP TABLE tbA
GO

DROP PROCEDURE usp_SquareIt
GO

Any thoughts?

Mike Bhttp://www.sqlteam.com/item.asp?ItemID=2644 for relevance of the topic.

HTH

call function inside Stored Procedure Error

i call the function inside the stored procedure and when i execute it theres a error

ALTERPROCEDURE [dbo].[usp_LoadEmployees]

-- Add the parameters for the stored procedure here

AS

BEGIN

SELECT dbo.Employees.EmployeeID,dbo.fn_ProperEmployees(dbo.Employees.EmployeeID)as MyEmployee,

dbo.Employees.Age, dbo.Employees.Sex, dbo.Employees.PositionID, dbo.Positions.Position_Name, dbo.Employees.DepartmentID,

dbo.Departments.DepartmentName

FROM dbo.Employees INNERJOIN

dbo.Departments ON dbo.Employees.DepartmentID = dbo.Departments.DepartmentID INNERJOIN

dbo.Positions ON dbo.Employees.PositionID = dbo.Positions.PositionID

GROUPBY dbo.Employees.EmployeeID, dbo.Employees.MI,

dbo.Employees.Age, dbo.Employees.Sex, dbo.Employees.PositionID, dbo.Positions.Position_Name, dbo.Employees.DepartmentID,

dbo.Departments.DepartmentName

ORDERBY dbo.Employees.EmployeeID

END

- function is this -

ALTER FUNCTION [dbo].[fn_ProperEmployees](@.cEmployeeID varchar(50))

RETURNS TABLE

AS

RETURN

(

-- Add the SELECT statement with parameter references here

SELECT EmployeeName =(Upper(dbo.Employees.Lastname)+','+ dbo.Employees.Firstname +' '+ dbo.Employees.MI)

FROM Employees

WHERE EmployeeID = @.cEmployeeID

)

the error is

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fn_ProperEmployees", or the name is ambiguous.

The function is a inline table function, if it is table function then you can't use it as column (only scalar functions are allowed)..

Change your function as follow as ,

Code Snippet

ALTER FUNCTION [dbo].[fn_ProperEmployees](@.cEmployeeID varchar(50))

RETURNS Varchar(1000)

AS

Begin

Declare @.EmployeeName as Varchar(1000);

-- Add the SELECT statement with parameter references here

SELECT

@.EmployeeName = (Upper(dbo.Employees.Lastname)

+ ',' + dbo.Employees.Firstname

+ ' ' + dbo.Employees.MI)

FROM

Employees

WHERE

EmployeeID = @.cEmployeeID;

Return @.EmployeeName;

End

Call a Web Server or external program inside of report

How can I call a web service and/or an external program (not a
referenced assembly) from inside of Report Services 2005? Can I do
both? Thank you for your help.On Jan 31, 6:11 am, ieg...@.gmail.com wrote:
> How can I call a web service and/or an external program (not a
> referenced assembly) from inside of Report Services 2005? Can I do
> both? Thank you for your help.
I would advise building an assembly to call the web service/external
program and then referencing this assembly.

Call a store procedure from inside a function

Hello,
I am having a problem where I'm trying to call a store procedure from a
function, is it possible ?
JHello,
Sorry no can do, you can't call a store procedure from a function, sorry.
Peter
"Although prepared for martyrdom, I preferred that it be postponed."
Winston Churchill
"Julie" wrote:

> Hello,
> I am having a problem where I'm trying to call a store procedure from a
> function, is it possible ?
> J|||Dang, ok thanks Peter
J
"Peter 'Not Peter The Spate' Nolan" wrote:
> Hello,
> Sorry no can do, you can't call a store procedure from a function, sorry.
> Peter
> "Although prepared for martyrdom, I preferred that it be postponed."
> Winston Churchill
>
> "Julie" wrote:
>|||No, this is documented in Books Online. There are a number of things that yo
u aren't allowed in a
UDF. Calling regular stored procedures is one of them (xp_'s are OK, though.
.).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Julie" <Julie@.discussions.microsoft.com> wrote in message
news:8D15C2A0-4A65-47ED-BAA1-6F8814C13DFC@.microsoft.com...
> Hello,
> I am having a problem where I'm trying to call a store procedure from a
> function, is it possible ?
> J|||No, it isn't possible. How about moving the logic out of your proc into
the function itself.
David Portas
SQL Server MVP
--|||Hi,
I think you can if you call the proc from within an openquery. But thats not
really in the spirit of what funstions are supposed to be for. Still, if its
really really necessary...
N
"David Portas" wrote:

> No, it isn't possible. How about moving the logic out of your proc into
> the function itself.
> --
> David Portas
> SQL Server MVP
> --
>