Calling stored procedures from Access 2000

The information here has been great and accurate.
How do I execute a SQL-Server 2000 stored procedure from Access 2000.
The procedure has three parameters (P1, P2, P3).
Thanks in advance,From an mdb or an adp? you can execute from a pass-through query or
VBA/ADO using a Connection or Command object.
--Mary
On Fri, 15 Oct 2004 11:55:05 -0700, bnhcomputing
<bnhcomputing@.discussions.microsoft.com> wrote:
>The information here has been great and accurate.
>How do I execute a SQL-Server 2000 stored procedure from Access 2000.
>The procedure has three parameters (P1, P2, P3).
>Thanks in advance,
>

Calling stored procedures from Access 2000

The information here has been great and accurate.
How do I execute a SQL-Server 2000 stored procedure from Access 2000.
The procedure has three parameters (P1, P2, P3).
Thanks in advance,From an mdb or an adp? you can execute from a pass-through query or
VBA/ADO using a Connection or Command object.
--Mary
On Fri, 15 Oct 2004 11:55:05 -0700, bnhcomputing
<bnhcomputing@.discussions.microsoft.com> wrote:

>The information here has been great and accurate.
>How do I execute a SQL-Server 2000 stored procedure from Access 2000.
>The procedure has three parameters (P1, P2, P3).
>Thanks in advance,
>

Calling stored procedures from Access 2000

The information here has been great and accurate.
How do I execute a SQL-Server 2000 stored procedure from Access 2000.
The procedure has three parameters (P1, P2, P3).
Thanks in advance,
From an mdb or an adp? you can execute from a pass-through query or
VBA/ADO using a Connection or Command object.
--Mary
On Fri, 15 Oct 2004 11:55:05 -0700, bnhcomputing
<bnhcomputing@.discussions.microsoft.com> wrote:

>The information here has been great and accurate.
>How do I execute a SQL-Server 2000 stored procedure from Access 2000.
>The procedure has three parameters (P1, P2, P3).
>Thanks in advance,
>

Calling Stored Procedures

Hi All

I was wondering if there is a way to call a stored procedure from inside
another stored procedure. So for example my first procedure will call a
second stored procedure which when executed will return one record and i
want to use this data in the calling stored procedure. Is this possible ?

Thanks in advance"Jarrod Morrison" <jarrodm@.ihug.com.au> wrote in message news:<bvl7me$4tf$1@.lust.ihug.co.nz>...
> Hi All
> I was wondering if there is a way to call a stored procedure from inside
> another stored procedure. So for example my first procedure will call a
> second stored procedure which when executed will return one record and i
> want to use this data in the calling stored procedure. Is this possible ?
> Thanks in advance

There are several options - see here:

http://www.sommarskog.se/share_data.html

If by "one record" you mean a scalar value, then an OUTPUT parameter
would work; if you mean a result set of one row, then you would need
one of the other approaches.

Simon|||Jarrod,
There are 2 ways to do this. (probably more, but these are the 2 most
common ways). Both of these use the Northwind database, so you can test
yourself, if needed:

WAY 1 (this is my favorite because it lets you return multiple values):

create procedure sp_test1
as begin
select top 1 orderID from orders
where customerID = 'tomsp'
end

create procedure sp_test2
as begin
declare @.my_value varchar(20)
exec @.my_value = sp_test1
print @.my_value
end

exec sp_test2

WAY 2 (this is probably more common, but the syntax is a little strange.
Note BOTH places where the keyword OUTPUT is used. Both are necessary):

create procedure sp_test1a @.@.outparam varchar(20) OUTPUT
as begin
select top 1 @.@.outparam = orderID from orders
where customerID = 'tomsp'
end

create procedure sp_test2a
as begin
declare @.my_value varchar(20)
exec sp_test1a @.my_value OUTPUT
print @.my_value
end

exec sp_test2a

You can find these and many more questions answered at
www.TechnicalVideos.net
Best regards,
Chuck Conover
www.TechnicalVideos.net

"Jarrod Morrison" <jarrodm@.ihug.com.au> wrote in message
news:bvl7me$4tf$1@.lust.ihug.co.nz...
> Hi All
> I was wondering if there is a way to call a stored procedure from inside
> another stored procedure. So for example my first procedure will call a
> second stored procedure which when executed will return one record and i
> want to use this data in the calling stored procedure. Is this possible ?
> Thanks in advance|||Chuck Conover (cconover@.commspeed.net) writes:
> create procedure sp_test1

Don't your technical videos tell people to stay away from the sp_
prefix? This prefix is reserved from system procedures, and SQL Server
first looks for these in master. There is a slight performance penalty,
and if MS ships a new system procedure, you might be in for a surprise.

> as begin
> select top 1 orderID from orders
> where customerID = 'tomsp'
> end
> create procedure sp_test2
> as begin
> declare @.my_value varchar(20)
> exec @.my_value = sp_test1
> print @.my_value
> end

I don't know what is supposed to look like, but it won't fly. sp_test1
does not have a RETURN statement, so it will always return 0. sp_test1
will also produce a result set, which will go to the client. In sp_test2
you are receiving the return value in a varchar(20), but the return
value from a stored procedure is an integer value.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Mon, 2 Feb 2004 23:13:55 +0000 (UTC) in
comp.databases.ms-sqlserver, Erland Sommarskog <sommar@.algonet.se>
wrote:

>Chuck Conover (cconover@.commspeed.net) writes:
>> create procedure sp_test1
>Don't your technical videos tell people to stay away from the sp_
>prefix? This prefix is reserved from system procedures, and SQL Server
>first looks for these in master. There is a slight performance penalty,
>and if MS ships a new system procedure, you might be in for a surprise.

On that note, is it good/bad practice (or even possible, I haven't
tried) to write some sp_whatever procedures and dump them into master?
Or should one create a common database for that stuff and call it like
exec common..sp_myproc, I get visions of invalid table name messages
if putting sps into a common database that would probably have no
tables.

--
A)bort, R)etry, I)nfluence with large hammer.|||Trevor Best (bouncer@.localhost) writes:
> On that note, is it good/bad practice (or even possible, I haven't
> tried) to write some sp_whatever procedures and dump them into master?
> Or should one create a common database for that stuff and call it like
> exec common..sp_myproc, I get visions of invalid table name messages
> if putting sps into a common database that would probably have no
> tables.

And there those days when the manuals, at least those from Sybase,
almost encouraged people to write their own system procedures.

But those says are long gone by. Today, writing and installing your
own system procedures is not supported.

There are sometimes questions in the newsgroups on how to have stored
procedures in a common database, but these questions typically relate
to applications where you have multiple copies of the schema, and the
answer to these questions is that they need to learn release management.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Simon

Thanks for the link, it did explain what i was trying to do but im not sure
if im going about it the right way, ive posted below the procedure im using
and it works correctly under sql query analyzer but not in VB, im assuming
that this is because im using a temp table and then deleting the temp table
afterwards. The reason im using a temp table is because there isnt always
going to be just one record returned from some of the searches so im
inserting the records into a temp table then one by one adding them to the
search string and deleting each record and finally deleting the table. Is
there a better way that i should be doing this ? Thanks for all your help

-- Stored Procedure Code ---

/*
** Determine Entity Launcher Items
*/

CREATE PROCEDURE [dbo].[EntityLauncherItems]

@.UserName VarChar(50),
@.MachineName VarChar (50),
@.EntityLocationID VarChar(3)

AS

DECLARE @.SqlStr VarChar(500) /* SQL Search String */
DECLARE @.SrchInt VarChar(3) /* Search Integer */
DECLARE @.IdCount Int /* ID Count */

SET @.SrchInt = '1'

/* SELECT Public Items */

SET @.SqlStr = 'SELECT AppID, Path, Name FROM Launcher_Items WHERE IsPub =
''' + '1' + ''''

/* Create Temporary Application ID Table */

CREATE TABLE #Id (AppID VarChar(4))

/* SELECT Single Machine Items */

INSERT INTO #Id (AppId) SELECT AppID FROM Launcher_MachineAssoc WHERE
MachineName = @.MachineName

/* SELECT Group Machine Items */

INSERT INTO #Id (AppId) SELECT AppID FROM Launcher_LocationAssoc WHERE
LocationID = @.EntityLocationId

/* SELECT UserName Items */

INSERT INTO #Id (AppId) SELECT AppId FROM Launcher_UserAssoc WHERE
UserName = @.UserName

/* Combine Non Public Applications Into Sql Search String */

SET @.IdCount = (SELECT COUNT(AppId) FROM #Id)

WHILE @.SrchInt <= @.IdCount

BEGIN

IF @.SrchInt = 1

BEGIN
SET @.SqlStr = @.SqlStr + ' UNION SELECT AppId, Path, Name FROM
Launcher_Items WHERE AppId = ''' + (SELECT TOP 1 AppId FROM #Id) + ''''
DELETE #Id FROM (SELECT TOP 1 * FROM #Id) AS t1 WHERE #Id.AppId =
t1.AppID
END

IF @.SrchInt > 1

BEGIN
SET @.SqlStr = @.SqlStr + ' OR AppID = ''' + (SELECT TOP 1 AppId FROM
#Id) + ''''
DELETE #Id FROM (SELECT TOP 1 * FROM #Id) AS t1 WHERE #Id.AppId =
t1.AppID
END

SET @.SrchInt = @.SrchInt + 1

END

DROP TABLE #Id

EXEC (@.SqlStr)
GO

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:60cd0137.0402020643.520ad289@.posting.google.c om...
> "Jarrod Morrison" <jarrodm@.ihug.com.au> wrote in message
news:<bvl7me$4tf$1@.lust.ihug.co.nz>...
> > Hi All
> > I was wondering if there is a way to call a stored procedure from inside
> > another stored procedure. So for example my first procedure will call a
> > second stored procedure which when executed will return one record and i
> > want to use this data in the calling stored procedure. Is this possible
?
> > Thanks in advance
> There are several options - see here:
> http://www.sommarskog.se/share_data.html
> If by "one record" you mean a scalar value, then an OUTPUT parameter
> would work; if you mean a result set of one row, then you would need
> one of the other approaches.
> Simon|||"Jarrod Morrison" <jarrodm@.ihug.com.au> wrote in message
news:bvqfpj$c3p$1@.lust.ihug.co.nz...
> Hi Simon
> Thanks for the link, it did explain what i was trying to do but im not
sure
> if im going about it the right way, ive posted below the procedure im
using
> and it works correctly under sql query analyzer but not in VB, im assuming
> that this is because im using a temp table and then deleting the temp
table
> afterwards. The reason im using a temp table is because there isnt always
> going to be just one record returned from some of the searches so im
> inserting the records into a temp table then one by one adding them to the
> search string and deleting each record and finally deleting the table. Is
> there a better way that i should be doing this ? Thanks for all your help

<snip
If you're getting the right results in QA, then you should be able to
retrieve them in VB - you'd need to explain what you mean by "not working"
when you run it from VB, and which client library you use. If it's ADO, then
one common piece of advice is to put SET NOCOUNT ON at the start of your
procedure:

http://www.aspfaq.com/show.asp?id=2246

Simon|||Jarrod Morrison (jarrodm@.ihug.com.au) writes:
> WHILE @.SrchInt <= @.IdCount
> BEGIN
> IF @.SrchInt = 1
> BEGIN
> SET @.SqlStr = @.SqlStr + ' UNION SELECT AppId, Path, Name FROM
> Launcher_Items WHERE AppId = ''' + (SELECT TOP 1 AppId FROM #Id) + ''''
> DELETE #Id FROM (SELECT TOP 1 * FROM #Id) AS t1 WHERE #Id.AppId =
> t1.AppID
> END
> IF @.SrchInt > 1
> BEGIN
> SET @.SqlStr = @.SqlStr + ' OR AppID = ''' + (SELECT TOP 1 AppId FROM
> #Id) + ''''
> DELETE #Id FROM (SELECT TOP 1 * FROM #Id) AS t1 WHERE #Id.AppId =
> t1.AppID
> END
> SET @.SrchInt = @.SrchInt + 1
> END

I might be missing something here, but why the dynamic SQL?

Why can't you just say:

SELECT AppID, Path, Name FROM Launcher_Items WHERE IsPub = '1'
UNION
SELECT AppID, Path, Name
FROM Launcher_Items l
WHERE EXISTS (SELECT *
FROM #Id i
WHERE l.AppId = i.AppId)
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hey Simon

Your a champ, thanyou, it fixed it straight away. To answer your question
about VB when i tried to look at the data in the recordset i get an EOF
message. But after putting the SET NOCOUNT ON it fixed that straight away.
Thanks again for you help

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:40213aa5$1_1@.news.bluewin.ch...
> "Jarrod Morrison" <jarrodm@.ihug.com.au> wrote in message
> news:bvqfpj$c3p$1@.lust.ihug.co.nz...
> > Hi Simon
> > Thanks for the link, it did explain what i was trying to do but im not
> sure
> > if im going about it the right way, ive posted below the procedure im
> using
> > and it works correctly under sql query analyzer but not in VB, im
assuming
> > that this is because im using a temp table and then deleting the temp
> table
> > afterwards. The reason im using a temp table is because there isnt
always
> > going to be just one record returned from some of the searches so im
> > inserting the records into a temp table then one by one adding them to
the
> > search string and deleting each record and finally deleting the table.
Is
> > there a better way that i should be doing this ? Thanks for all your
help
> <snip>
> If you're getting the right results in QA, then you should be able to
> retrieve them in VB - you'd need to explain what you mean by "not working"
> when you run it from VB, and which client library you use. If it's ADO,
then
> one common piece of advice is to put SET NOCOUNT ON at the start of your
> procedure:
> http://www.aspfaq.com/show.asp?id=2246
> Simon|||Hi simon

Just one other quick question, this isnt really important but is there a way
to find out how many records have been returned in the stored procedure from
vb ? If i use the .recordcount function with the object it returns -1
regardless of how many records there may be.

Thanks

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:40213aa5$1_1@.news.bluewin.ch...
> "Jarrod Morrison" <jarrodm@.ihug.com.au> wrote in message
> news:bvqfpj$c3p$1@.lust.ihug.co.nz...
> > Hi Simon
> > Thanks for the link, it did explain what i was trying to do but im not
> sure
> > if im going about it the right way, ive posted below the procedure im
> using
> > and it works correctly under sql query analyzer but not in VB, im
assuming
> > that this is because im using a temp table and then deleting the temp
> table
> > afterwards. The reason im using a temp table is because there isnt
always
> > going to be just one record returned from some of the searches so im
> > inserting the records into a temp table then one by one adding them to
the
> > search string and deleting each record and finally deleting the table.
Is
> > there a better way that i should be doing this ? Thanks for all your
help
> <snip>
> If you're getting the right results in QA, then you should be able to
> retrieve them in VB - you'd need to explain what you mean by "not working"
> when you run it from VB, and which client library you use. If it's ADO,
then
> one common piece of advice is to put SET NOCOUNT ON at the start of your
> procedure:
> http://www.aspfaq.com/show.asp?id=2246
> Simon|||Jarrod,
Look at CursorType and CursorLocation in ADO. You are probably using a
combination which does not give you the recordcount (and then ADO indicates
this by returning -1). ForwardOnly is the default CursorType and it does not
give you the recordcount...
--
Lars Broberg
Elbe-Data AB
http://www.elbe-data.se
Remove "nothing." when replying to private e-mail!

"Jarrod Morrison" <jarrodm@.ihug.com.au> wrote in message
news:bvt14m$cct$1@.lust.ihug.co.nz...
> Hi simon
> Just one other quick question, this isnt really important but is there a
way
> to find out how many records have been returned in the stored procedure
from
> vb ? If i use the .recordcount function with the object it returns -1
> regardless of how many records there may be.
> Thanks
>
> "Simon Hayes" <sql@.hayes.ch> wrote in message
> news:40213aa5$1_1@.news.bluewin.ch...
> > "Jarrod Morrison" <jarrodm@.ihug.com.au> wrote in message
> > news:bvqfpj$c3p$1@.lust.ihug.co.nz...
> > > Hi Simon
> > > > Thanks for the link, it did explain what i was trying to do but im not
> > sure
> > > if im going about it the right way, ive posted below the procedure im
> > using
> > > and it works correctly under sql query analyzer but not in VB, im
> assuming
> > > that this is because im using a temp table and then deleting the temp
> > table
> > > afterwards. The reason im using a temp table is because there isnt
> always
> > > going to be just one record returned from some of the searches so im
> > > inserting the records into a temp table then one by one adding them to
> the
> > > search string and deleting each record and finally deleting the table.
> Is
> > > there a better way that i should be doing this ? Thanks for all your
> help
> > > <snip>
> > If you're getting the right results in QA, then you should be able to
> > retrieve them in VB - you'd need to explain what you mean by "not
working"
> > when you run it from VB, and which client library you use. If it's ADO,
> then
> > one common piece of advice is to put SET NOCOUNT ON at the start of your
> > procedure:
> > http://www.aspfaq.com/show.asp?id=2246
> > Simon|||Hi Lars

Yes i am using the default cursor type in my vb code, which type of cursor
should i be using to return the record count ? Should i also be changing the
lock type as well ?

Thanks

"Lars Broberg" <lars.b@.elbe-data.nothing.se> wrote in message
news:NyrUb.81644$dP1.211699@.newsc.telia.net...
> Jarrod,
> Look at CursorType and CursorLocation in ADO. You are probably using a
> combination which does not give you the recordcount (and then ADO
indicates
> this by returning -1). ForwardOnly is the default CursorType and it does
not
> give you the recordcount...
> --
> Lars Broberg
> Elbe-Data AB
> http://www.elbe-data.se
> Remove "nothing." when replying to private e-mail!
>
> "Jarrod Morrison" <jarrodm@.ihug.com.au> wrote in message
> news:bvt14m$cct$1@.lust.ihug.co.nz...
> > Hi simon
> > Just one other quick question, this isnt really important but is there a
> way
> > to find out how many records have been returned in the stored procedure
> from
> > vb ? If i use the .recordcount function with the object it returns -1
> > regardless of how many records there may be.
> > Thanks
> > "Simon Hayes" <sql@.hayes.ch> wrote in message
> > news:40213aa5$1_1@.news.bluewin.ch...
> > > > "Jarrod Morrison" <jarrodm@.ihug.com.au> wrote in message
> > > news:bvqfpj$c3p$1@.lust.ihug.co.nz...
> > > > Hi Simon
> > > > > > Thanks for the link, it did explain what i was trying to do but im
not
> > > sure
> > > > if im going about it the right way, ive posted below the procedure
im
> > > using
> > > > and it works correctly under sql query analyzer but not in VB, im
> > assuming
> > > > that this is because im using a temp table and then deleting the
temp
> > > table
> > > > afterwards. The reason im using a temp table is because there isnt
> > always
> > > > going to be just one record returned from some of the searches so im
> > > > inserting the records into a temp table then one by one adding them
to
> > the
> > > > search string and deleting each record and finally deleting the
table.
> > Is
> > > > there a better way that i should be doing this ? Thanks for all your
> > help
> > > > > > <snip>
> > > > If you're getting the right results in QA, then you should be able to
> > > retrieve them in VB - you'd need to explain what you mean by "not
> working"
> > > when you run it from VB, and which client library you use. If it's
ADO,
> > then
> > > one common piece of advice is to put SET NOCOUNT ON at the start of
your
> > > procedure:
> > > > http://www.aspfaq.com/show.asp?id=2246
> > > > Simon
> >|||Jarrod Morrison (jarrodm@.ihug.com.au) writes:
> Yes i am using the default cursor type in my vb code, which type of
> cursor should i be using to return the record count ? Should i also be
> changing the lock type as well ?

In most cases you probably want a client-side cursor, but server-side
is the default. Set .CursorLocation to adUseClient. Then you only have
one cursor type to choose from, Static.

The reason you cannot get a record count with forward only, is that
you get the rows as soon as SQL Server finds them, so you have no idea
how many there will be until you're through.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Jarrod,
As Erland said, but beware that you will get a "disconnected" recordset that
not (automatically) will reflect any changes done on the server. If you
shall change the lock type depends on your own application logic. How do you
update? If you do it by stored procedures your recordset can use
adLockReadOnly, but if you update via the recordset you need
adLockPessimistic, adLockOptimistic or adLockBatchOptimistic.
--
Lars Broberg
Elbe-Data AB
http://www.elbe-data.se
Remove "nothing." when replying to private e-mail!d

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns948881D4E980EYazorman@.127.0.0.1...
> Jarrod Morrison (jarrodm@.ihug.com.au) writes:
> > Yes i am using the default cursor type in my vb code, which type of
> > cursor should i be using to return the record count ? Should i also be
> > changing the lock type as well ?
> In most cases you probably want a client-side cursor, but server-side
> is the default. Set .CursorLocation to adUseClient. Then you only have
> one cursor type to choose from, Static.
> The reason you cannot get a record count with forward only, is that
> you get the rows as soon as SQL Server finds them, so you have no idea
> how many there will be until you're through.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Erland

Thanks for the reply, the way you explained it sounds pretty straight
forward so i change the cursor type and location in my code but i still
recieve -1, could this be because the stored procedure has set nocount on ?
This is the code im using in the vb app

Dim PtrlCmd As New ADODB.Command

PtrlCmd.ActiveConnection = CPDBase
PtrlCmd.CommandText = "sp_EntityMemberShips"
PtrlCmd.CommandType = adCmdStoredProc
PtrlRst.CursorType = adOpenStatic
PtrlRst.CursorLocation = adUseClient

PtrlCmd.Parameters.Append PtrlCmd.CreateParameter("MachineName", adVarChar,
adParamInput, 50, frmLoading.lblMachine)
PtrlCmd.Parameters.Append PtrlCmd.CreateParameter("UserName", adVarChar,
adParamInput, 50, frmLoading.lblUserName)

Set PtrlRst = PtrlCmd.Execute

after this line i break and try to get the recordcount and still get a -1 ?

Thanks for your help

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns948881D4E980EYazorman@.127.0.0.1...
> Jarrod Morrison (jarrodm@.ihug.com.au) writes:
> > Yes i am using the default cursor type in my vb code, which type of
> > cursor should i be using to return the record count ? Should i also be
> > changing the lock type as well ?
> In most cases you probably want a client-side cursor, but server-side
> is the default. Set .CursorLocation to adUseClient. Then you only have
> one cursor type to choose from, Static.
> The reason you cannot get a record count with forward only, is that
> you get the rows as soon as SQL Server finds them, so you have no idea
> how many there will be until you're through.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Jarrod Morrison (jarrodm@.ihug.com.au) writes:
> Thanks for the reply, the way you explained it sounds pretty straight
> forward so i change the cursor type and location in my code but i still
> recieve -1, could this be because the stored procedure has set nocount on
?
> This is the code im using in the vb app
> Dim PtrlCmd As New ADODB.Command
> PtrlCmd.ActiveConnection = CPDBase
> PtrlCmd.CommandText = "sp_EntityMemberShips"
> PtrlCmd.CommandType = adCmdStoredProc
> PtrlRst.CursorType = adOpenStatic
> PtrlRst.CursorLocation = adUseClient

But since you are using cmd.Execute, you should set the cursor location
and cursor type on PtrlCmd. Setting the properties in PtrlRst is the
thing to do if you open the record set with rs.Open.

ADO is indeed very confusing...

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog (sommar@.algonet.se) writes:
> Jarrod Morrison (jarrodm@.ihug.com.au) writes:
>> Thanks for the reply, the way you explained it sounds pretty straight
>> forward so i change the cursor type and location in my code but i still
>> recieve -1, could this be because the stored procedure has set nocount on
> ?
>> This is the code im using in the vb app
>>
>> Dim PtrlCmd As New ADODB.Command
>>
>> PtrlCmd.ActiveConnection = CPDBase
>> PtrlCmd.CommandText = "sp_EntityMemberShips"
>> PtrlCmd.CommandType = adCmdStoredProc
>> PtrlRst.CursorType = adOpenStatic
>> PtrlRst.CursorLocation = adUseClient
> But since you are using cmd.Execute, you should set the cursor location
> and cursor type on PtrlCmd. Setting the properties in PtrlRst is the
> thing to do if you open the record set with rs.Open.
> ADO is indeed very confusing...

Indeed it is, and on top of that I am only an occasional ADO programmer,
which may explain my incorrect suggestions above.

You don't set the CursorLocation on the Command object; the place for
this is the Connection object. The CursorType property is only available
on the Recordset object, but the good news is that once you have gone
for client-side, there is only one cursor type available and that is
static.

Sorry for any confusion.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Erland

Works a treat once i changed the cursor location for the connection. Thanks
once again for all your help and for explaining the reason why it wasnt
working

Thanks again

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns9489CB4E35C6Yazorman@.127.0.0.1...
> Erland Sommarskog (sommar@.algonet.se) writes:
> > Jarrod Morrison (jarrodm@.ihug.com.au) writes:
> >> Thanks for the reply, the way you explained it sounds pretty straight
> >> forward so i change the cursor type and location in my code but i still
> >> recieve -1, could this be because the stored procedure has set nocount
on
> > ?
> >> This is the code im using in the vb app
> >>
> >> Dim PtrlCmd As New ADODB.Command
> >>
> >> PtrlCmd.ActiveConnection = CPDBase
> >> PtrlCmd.CommandText = "sp_EntityMemberShips"
> >> PtrlCmd.CommandType = adCmdStoredProc
> >> PtrlRst.CursorType = adOpenStatic
> >> PtrlRst.CursorLocation = adUseClient
> > But since you are using cmd.Execute, you should set the cursor location
> > and cursor type on PtrlCmd. Setting the properties in PtrlRst is the
> > thing to do if you open the record set with rs.Open.
> > ADO is indeed very confusing...
> Indeed it is, and on top of that I am only an occasional ADO programmer,
> which may explain my incorrect suggestions above.
> You don't set the CursorLocation on the Command object; the place for
> this is the Connection object. The CursorType property is only available
> on the Recordset object, but the good news is that once you have gone
> for client-side, there is only one cursor type available and that is
> static.
> Sorry for any confusion.
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.aspsql

calling stored procedure in project

hey all

I am very new to stored procedures and would like some clarification please

I have a stored procedure written by someone else

I generally use SQL Server Business Intelligence Studio.

I am designing a report and have called this sp

exec GetTicketsSnapshot (being the name of sp)

1 window only is populated?

if I run this in sql Server Management Studio it populates 3 windows which is what I would expect to see

can someone explain?

thanks

Jewel,

Do you mean that you have 3 different resultset in the same stored procedure? I'm just trying to get a clearer picture of what your issue is.

Ham

|||

hey Ham

Yes that's exactly what I mean.

But it isn't even giving the full result of the first resultset? either

thanks

|||

hey Ham or someone else

Any clues on what I can do here

I have updated since eg I have in my stored procedure above each resultset @.resultset = '0' (// '1' // '2')

I have made three separate datasets to refer to these - and if I run them I get the three separate results

but in my properties of my datasets - the fields are all the same eg they match resultset1

therefore I error in preview - as fields are not there as such.

cheers

|||

Hi,

ok, first of all, only 1 recordset is supported for the reporting services. If you are relying on the logic on the procedure and will not be able to split up the one procedure into three, you will probably have to do the approach you mentioned with providing something like an indexer and giving back only the relevant information to the client / Reporting services. The reporting services interface cannot populate the information for the procedure as it is not aware of the logic that you built in (with selecting one resultset of the three, so for the "discovery of the expected" resultset, the best thing would be to comment out the relevant parts that you do not want to have in the resultset, querying the information from the reporting services interface (that you will have the fields available in the reporting services intefaces) and then set back the procedure to the generic solution. Unfortunately this is the only approach I know. In a former project I had to deal with dynmamic SQL which led me to write the queries in the procedure (without dynamic SQL) querying the meta data of the resultset and the commenting this out to leave the dynamic SQL solution.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

sql

calling stored procedure

hey all

I am very new to stored procedures and would like some clarification please

I have a stored procedure written by someone else

I generally use SQL Server Business Intelligence Studio front

I am designing a report and have called this sp

exec GetTicketsSnapshot (being the name of sp)

1 window only is populated? with half a result set

if I run this in sql Server Management Studio it populates 3 resultsets which is what I would expect to see

can someone explain?

thanks

I'm assuming the Dataset is correct...The 'Command Type' should be 'StoredProcedure' and the Query string should be only the name of the stored procedure. So your Query string should be: GetTicketsSnapshot

The stored proceduremust only returnone result set.

Calling stored proc B from stored proc A

Hi all

I have about 5 stored procedures that, among other things, execute exactly the same SELECT statement

Instead of copying the SELECT statement 5 times, I'd like each stored proc to call a single stored proc that executes the SELECT statement and returns the resultset to the calling stored proc

The SELECT statement in question retrieves a single row from a table containing 10 columns.

Is there a way for a stored proc to call another stored proc and gain access to the resultset of the called stored proc?

I know about stored proc return values and about output parameters, but I think I am looking for something different.

ThanksYou can create a table variable and return it to the outer proc ... i assume thats what you are looking for

:)|||instead of procedure create a table-value udf|||If SQLServer 2000, use the @.table datatype
If SQLServer 7.0, create a temp table in proc 1 and populate it in proc 2 (just be aware of that proc 2 is gonna recompile each time its run)|||If it's Always just 1 row (are you sure) wht not pass back OUTPUT variables?|||Originally posted by Jonte
If SQLServer 2000, use the @.table datatype
If SQLServer 7.0, create a temp table in proc 1 and populate it in proc 2 (just be aware of that proc 2 is gonna recompile each time its run)

Thanks to Enigma, ms_sql_dba and Jonte. A table variable is exactly what I was searching for.|||Originally posted by Brett Kaiser
If it's Always just 1 row (are you sure) wht not pass back OUTPUT variables?

Hi

I was looking for a solution where I didnt have to declare 10+ output variables, or one that would work on occassions where there is more than 1 row returned

However, I understand that output variables are generally better for performance that using a table variable?|||Originally posted by mattkrevs
Hi

I was looking for a solution where I didnt have to declare 10+ output variables, or one that would work on occassions where there is more than 1 row returned

However, I understand that output variables are generally better for performance that using a table variable?

Well, obviously, if you had a table variable with 15 columns or you had 15 output parameters, then the 15 output parameters would have less overhead.

Oh, and don't use UDF's when you can use SP's. UDF don't get precompiled.|||Originally posted by mattkrevs
Hi

I was looking for a solution where I didnt have to declare 10+ output variables, or one that would work on occassions where there is more than 1 row returned

However, I understand that output variables are generally better for performance that using a table variable?

I like lazy...

But it seems misguided...you still have to code the local table variables anyway...

And in what instances will you get more than 1 row...

what would you do wiuth them...sounds like you need to make a decision anyway...why not do it in the sproc?|||Originally posted by beyond cool
Oh, and don't use UDF's when you can use SP's. UDF don't get precompiled.

Can you explain in more detail what you mean by that?|||Originally posted by Brett Kaiser
I like lazy...

But it seems misguided...you still have to code the local table variables anyway...

And in what instances will you get more than 1 row...

what would you do wiuth them...sounds like you need to make a decision anyway...why not do it in the sproc?

Hmmm. You know me too well. Yes its a bit lazy I guess. The stored proc I want to create and call checks for the existance of a row in a table and creates it if it doesnt find it.
Since the calling stored procs only needs info on 2 of the columns of the created row at present then I guess output params might be the best option.

I am interested in the table variable as it is a little more flexible and future proof as I can make all of the columns in the created row available to all calling stored procs.|||Originally posted by mattkrevs
Hmmm. You know me too well. Yes its a bit lazy I guess. The stored proc I want to create and call checks for the existance of a row in a table and creates it if it doesnt find it.
Since the calling stored procs only needs info on 2 of the columns of the created row at present then I guess output params might be the best option.

I am interested in the table variable as it is a little more flexible and future proof as I can make all of the columns in the created row available to all calling stored procs.

Brett - I think I'll go with your output parameters option. I found 2 problems with using table variables

1. I didnt realise you had to fully declare table variables including the table structure. Kind of defeats the purpose of 'lazy' coding

2. I tried this and I got the error "EXECUTE cannot be used as a source when inserting into a table variable." so it looks like it doesnt work anyway. I found other posters had the same problem and they ended up using temp tables instead.|||Hey, don't get me wrong...lazy is a good thing...

It means you're trying to find more effecient ways to do something, rather than the long way around...

Can you posted what you tried though? I'm curious as to why it didn't work for you...|||I kinda like the concept of lazy (http://www.lazydba.com/lazydba_info.html) coding.
Its fun and its challenging!!!!

I believe the laziest i've encountered has to be this one (http://www.dbforums.com/showthread.php?threadid=975995)|||Good site, Enigma

Calling Store Procedures

Hi,

I am new to SSIS and just want to know how I can call a store procedure for each record during transformation since my destination table ID is generated by a Store procedure and I must store it also in my integration temp table.

Thanks

Yes, you can call a sproc for each record in the pipeline. Thomas explains how here: http://sqljunkies.com/WebLog/tpagel/archive/2006/01/03/17744.aspx

-Jamie

Calling Oracle Procedures

I've seen some threads on this issue, but no clear resolution. Here is my
procedure declare:
PROCEDURE get_releases (
p_CNUMBER IN VARCHAR2,
p_HIDDENSTATE IN NUMBER,
p_COMPLIANCE IN NUMBER,
p_OBSOLETE IN NUMBER,
p_CORE IN NUMBER,
p_REL_CUR OUT RelCurTyp);
RelCurTyp is defined as an out cursor in the package definition.
I've created the report and data source and I'm trying to execute this text
command type:
get_releases :cnumber, :hiddenState, :compliance, :obsolete
It then asks me to enter the values for the parameters, but I then get
ORA-00900: invalid SQL statement.
What is the correct way to call this procedure? Will the oledb automatically
handle the returned cursor?I wanted to know how can we call Oracle stored procedures from Microsoft
Reporting Services. I could pass a sql text and get a record set, but I want
to execute a store proc and get the record set, and kindly let me know how to
pass a refcursor to get the record set into the reporting services.
Tom, please let me know when you have your answer. Thanks!
"Tom" wrote:
> I've seen some threads on this issue, but no clear resolution. Here is my
> procedure declare:
> PROCEDURE get_releases (
> p_CNUMBER IN VARCHAR2,
> p_HIDDENSTATE IN NUMBER,
> p_COMPLIANCE IN NUMBER,
> p_OBSOLETE IN NUMBER,
> p_CORE IN NUMBER,
> p_REL_CUR OUT RelCurTyp);
> RelCurTyp is defined as an out cursor in the package definition.
> I've created the report and data source and I'm trying to execute this text
> command type:
> get_releases :cnumber, :hiddenState, :compliance, :obsolete
> It then asks me to enter the values for the parameters, but I then get
> ORA-00900: invalid SQL statement.
> What is the correct way to call this procedure? Will the oledb automatically
> handle the returned cursor?|||I tried everything I could think of, and just before the laptop was about to
take a flying leap out of the window, I decided to re-do the report from
scratch.
I added a new report in the solution, copied all of the controls from the
old report to the new one and re-created the Data object as follows:
- Command type = Storedprocedure
- The only text to be added is : COMPLIANCE_PKG.get_releases
- Added the parameters by clicking the "..." next to the Dataset, and set
the parameters in the parameters tab(the out param is not required).
The DataSource is pointing to MS OLE DB Provider for Oracle and the Type of
the data source is set to Oracle, so the connection string should only have
"data source={your DS}"
It now works like a charm.|||Hi Tom,
I am working on the similar situation and call the following Oracle proc
from a MSRS report in MS Visual Studio:
MPC.Report_Performance_Main_5 :Response_Interval, :Launch_List
Response_Interval and Launch_List are both query parameters and report
parameters. The proc is:
Report_Performance_Main_5 (ip_response_interval IN VARCHAR2,
ip_launch_list IN VARCHAR2,
results_cur OUT T_RESULT_CURSOR)
When I pushed the preview button, I got the following error:
ORA-00972:identifier is too long
ORA-06512:at "SYS.DBMS_UTILITY", line 114
ORA-06512: at line 1
The stored proc looks fine. It seems there is a problem from MSRS to pass
the input parameters.
Any clues would be very appreciated!
James
"Tom" wrote:
> I tried everything I could think of, and just before the laptop was about to
> take a flying leap out of the window, I decided to re-do the report from
> scratch.
> I added a new report in the solution, copied all of the controls from the
> old report to the new one and re-created the Data object as follows:
> - Command type = Storedprocedure
> - The only text to be added is : COMPLIANCE_PKG.get_releases
> - Added the parameters by clicking the "..." next to the Dataset, and set
> the parameters in the parameters tab(the out param is not required).
> The DataSource is pointing to MS OLE DB Provider for Oracle and the Type of
> the data source is set to Oracle, so the connection string should only have
> "data source={your DS}"
> It now works like a charm.|||TOM!!
you are great man!! thank you soooooo much,,, i have been trying to do that
for 3 !! and your posting solved my problem...
thaaaaaaaaaaaaaaank you
"Tom" wrote:
> I tried everything I could think of, and just before the laptop was about to
> take a flying leap out of the window, I decided to re-do the report from
> scratch.
> I added a new report in the solution, copied all of the controls from the
> old report to the new one and re-created the Data object as follows:
> - Command type = Storedprocedure
> - The only text to be added is : COMPLIANCE_PKG.get_releases
> - Added the parameters by clicking the "..." next to the Dataset, and set
> the parameters in the parameters tab(the out param is not required).
> The DataSource is pointing to MS OLE DB Provider for Oracle and the Type of
> the data source is set to Oracle, so the connection string should only have
> "data source={your DS}"
> It now works like a charm.|||Where do I set 'Command type = Storedprocedure'? or How do I recreate the
dataset?
"Tom" wrote:
> I tried everything I could think of, and just before the laptop was about to
> take a flying leap out of the window, I decided to re-do the report from
> scratch.
> I added a new report in the solution, copied all of the controls from the
> old report to the new one and re-created the Data object as follows:
> - Command type = Storedprocedure
> - The only text to be added is : COMPLIANCE_PKG.get_releases
> - Added the parameters by clicking the "..." next to the Dataset, and set
> the parameters in the parameters tab(the out param is not required).
> The DataSource is pointing to MS OLE DB Provider for Oracle and the Type of
> the data source is set to Oracle, so the connection string should only have
> "data source={your DS}"
> It now works like a charm.

Calling normal stored procedures from Extended Procedures

Hi

I want to call a database stored procedure from an extended procedure. Can some one provide snippet of code (either in C or C++) to achieve the same ?

Thanks & Regards
ImtiazYou have to make connection to server using ODBC or OLEDB to do this. Please take a look at the call escape sequence topic in Books Online. The url is mk:@.MSITStore:D:\Program%20Files\Microsoft%20SQL%20Server%20Books%20Online\1033\odbcsql.chm::/od_6_035_5dnp.htm. You may have to change the path depending on your installation. You can also find samples in the SQL Server CD.

Calling nested stored proedure many times

There are two stored procedures. One calls the other one time. It works.
create procedure nestedSP
@.p float
as
declare @.q float
set @.q = @.p
select @.q
go
create procedure CallingNestedSP
@.p float
as
exec nestedSP @.p
go
exec CallingNestedSP 111.11
How do I get the resultset as 111.11, 111.11 by calling the nexted twice.
Please advise. Thanks
PeterHi Peter
There is no best way of joining the two results from SPs ...
You can maybe try to create the called procedure as a UDF and
call it twice.Or if you only have to use a SP,
then insert the result into a temp table. Your calling SP will have the code
like this..
create procedure CallingNestedSP
@.p float
as
begin
create table #temp (a decimal(...))
INsert into #temp
exec nestedSP @.p
select a,a from #temp
end..
Though I would prefer a udf for a situation similar to yours.
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/

calling multiple stored procedures from a stored procedure

Hi.

I'm quite sure that it's just my search methods that suck, but I can't find any good examples of how to call multiple stored procedures from a stored procedure.

The thing is that I have a whole bunch of tables, each with auto generated stored procedures for get by id, get all, insert, delete, update...
Since I want avoid multiple calls to the database to fill my business objects I thought I'd make a sp that gathers data from multiple tables, using the standard get sp's...

the structure looks like this

base (baseID, baseCol1, baseCol2)
base_elementContainer (base_elementContainerID, baseID, elementContainerID)
elementContainer (elementContainerID, elementContainerCol1, elementContainerCol2)
elementContainer_element (elementContainer_elementID, elementContainerID, elementID)
element (elementID, elementCol1, elementCol2)
element_objectOne (element_objectOneID, elementID, objectOneID)
objectOne (objectOndeID, objectOneCol1, objectOneCol2)

element_objectTwo (element_objectTwoID, elementID, objectTwoID)
objectTwo (objectTwoID, objectTwoCol1, objectTwoCol2)

and what I need to do is to fetch all objectOne and objectTwo by baseID... and collect a few things on the way...

would be happy to get a few quick pointers or a link to some good example codes or a tutorial...

thanks...

You can get results from multiple procs into local tables. Refer to this post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1016268&SiteID=1

calling DB2 store procedures from SQL Server 2000

I am trying to call DB2 stroe procedure from within SQL server 2000 using DTS. I have the IBM odbc driver installed on the server. I have created an ACtiveX script to run in DTS and it fails staing it could not findor load the DB2 store procedure.

Has anyone come across doing this and how they did it?

THanks for the help...How did you connect to DB2?|||I am using ODBC to connect to the DB2 database. I have setup DTS scripts to do inserts into DB2 database with success using the Other ODBC connection.|||Did you try setting up a linked server to DB2?

What platform is DB2 on?

O/S 390? AS400? UNIX?

What version of DB2 (not important, just curious)

You mention stored procedure and DTS...which one are we talking about?

Can you select data from DB2 in QA?|||Yes I do have linked server to the DB2 database.

I am not sure the platform but I believe it is Unix.

The version of DB2 is 8.x

I can select data from DB2 using the OPENROWQUERY statement.

I am using DTS to execute the DB2 Store Procedure.

I also tried to do through an ACtiveX Script:

Here is the script:

'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

Function Main()

Dim con, rs, connectstr

Set con = CreateObject ("ADODB.Command")
Set connect1 = CreateObject("ADODB.Connection")

connectstr = "Provider=IBMDADB2.1;Password=xxxxx;User ID=xxxxxx;Data Source=TWDLBS1D;Persist Security Info=True"
'connectstr = "Provider=MSDASQL.1;Persist Security Info=True;User ID=xxxxxx;Password=xxxxxx;Data Source=TWDLBS1D;Mode=ReadWrite"

connect1.open connectstr
con.ActiveConnection = connect1
con.CommandText = "LAWSON.LBS_BATCH_VALID"
con.CommandType = 4 'adCmdStoredProc
'con.Parameters.Append con.CreateParameter (adInteger,adParamInput ,258)
'con.Parameters.Append con.CreateParameter (adDate,adParamInput, "2004/02/04")
'con.Parameters.Append con.CreateParameter (adChar,adParamInput, "NP")
Set rs = con.Execute

Main = DTSTaskExecResult_Success
End Function

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 sql job synchronously

Hi,
My customer has a complex system that is made of jobs, procedures and scripts that
should run almost every day.
The problem is that sometimes, some job can run "a little longer" and theother
procedures and scripts (and even jobs) should wait for jobs to end beforestarting.
Is there a way to call a sql job synchronously from T-SQL, from a dll or from some
language? Are there any sample code?
TIA,
Clment
No, jobs start asynchronously. You can have a bit in a table that gets
flipped to 1 when the job gets invoked (which only happens if the bit is
currently 0), and then flipped back upon successful completion.
"Clment Doss" <cdoss@.nospam.dhs.com.br> wrote in message
news:%23iivexDkIHA.4396@.TK2MSFTNGP04.phx.gbl...
Hi,
My customer has a complex system that is made of jobs, procedures and
scripts that
should run almost every day.
The problem is that sometimes, some job can run "a little longer" and the
other
procedures and scripts (and even jobs) should wait for jobs to end before
starting.
Is there a way to call a sql job synchronously from T-SQL, from a dll or
from some
language? Are there any sample code?
TIA,
Clment
|||Aaron Bertrand [SQL Server MVP] wrote:
> No, jobs start asynchronously. You can have a bit in a table that gets
> flipped to 1 when the job gets invoked (which only happens if the bit is
> currently 0), and then flipped back upon successful completion.
>
I could just check the job status every second. Not very "elegant" solution, but
something like:
startjob("JobName");
while checkjob("JobName")== __running__ {
sleep( 1000 ); // in ms
}
return checkjob("JobName")== __success__;
For optimal perfomance, is there a DLL I can use, or should I use T-SQL?
TIA,
Clment
|||You could also simply have each job in succession start the next job that is
supposed to fire as it's last step.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eh%23DT2DkIHA.2276@.TK2MSFTNGP05.phx.gbl...
> No, jobs start asynchronously. You can have a bit in a table that gets
> flipped to 1 when the job gets invoked (which only happens if the bit is
> currently 0), and then flipped back upon successful completion.
>
>
> "Clment Doss" <cdoss@.nospam.dhs.com.br> wrote in message
> news:%23iivexDkIHA.4396@.TK2MSFTNGP04.phx.gbl...
> Hi,
> My customer has a complex system that is made of jobs, procedures and
> scripts that
> should run almost every day.
> The problem is that sometimes, some job can run "a little longer" and the
> other
> procedures and scripts (and even jobs) should wait for jobs to end before
> starting.
> Is there a way to call a sql job synchronously from T-SQL, from a dll or
> from some
> language? Are there any sample code?
> TIA,
> Clment
>
|||>>
I could just check the job status every second.[vbcol=seagreen]
Ugh. If the job takes 20 minutes to run, then that is a LOT of wasted
resources. Why not update a flag somewhere only when the job has completed
(with a job you can mark this whether or not the originating step
succeeded)?

Calling a sql job synchronously

Hi,
My customer has a complex system that is made of jobs, procedures and scr= ipts that should run almost every day.
The problem is that sometimes, some job can run "a little longer" and the= other procedures and scripts (and even jobs) should wait for jobs to end before= starting.
Is there a way to call a sql job synchronously from T-SQL, from a dll or = from some language? Are there any sample code?
TIA,
Cl=E9mentNo, jobs start asynchronously. You can have a bit in a table that gets
flipped to 1 when the job gets invoked (which only happens if the bit is
currently 0), and then flipped back upon successful completion.
"Clément Doss" <cdoss@.nospam.dhs.com.br> wrote in message
news:%23iivexDkIHA.4396@.TK2MSFTNGP04.phx.gbl...
Hi,
My customer has a complex system that is made of jobs, procedures and
scripts that
should run almost every day.
The problem is that sometimes, some job can run "a little longer" and the
other
procedures and scripts (and even jobs) should wait for jobs to end before
starting.
Is there a way to call a sql job synchronously from T-SQL, from a dll or
from some
language? Are there any sample code?
TIA,
Clément|||Aaron Bertrand [SQL Server MVP] wrote:
> No, jobs start asynchronously. You can have a bit in a table that gets= > flipped to 1 when the job gets invoked (which only happens if the bit i=s > currently 0), and then flipped back upon successful completion.
>
I could just check the job status every second. Not very "elegant" soluti=on, but something like:
startjob("JobName");
while checkjob("JobName")=3D=3D __running__ {
sleep( 1000 ); // in ms
}
return checkjob("JobName")=3D=3D __success__;
For optimal perfomance, is there a DLL I can use, or should I use T-SQL?
TIA,
Cl=E9ment|||You could also simply have each job in succession start the next job that is
supposed to fire as it's last step.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eh%23DT2DkIHA.2276@.TK2MSFTNGP05.phx.gbl...
> No, jobs start asynchronously. You can have a bit in a table that gets
> flipped to 1 when the job gets invoked (which only happens if the bit is
> currently 0), and then flipped back upon successful completion.
>
>
> "Clément Doss" <cdoss@.nospam.dhs.com.br> wrote in message
> news:%23iivexDkIHA.4396@.TK2MSFTNGP04.phx.gbl...
> Hi,
> My customer has a complex system that is made of jobs, procedures and
> scripts that
> should run almost every day.
> The problem is that sometimes, some job can run "a little longer" and the
> other
> procedures and scripts (and even jobs) should wait for jobs to end before
> starting.
> Is there a way to call a sql job synchronously from T-SQL, from a dll or
> from some
> language? Are there any sample code?
> TIA,
> Clément
>|||>>
I could just check the job status every second.
Ugh. If the job takes 20 minutes to run, then that is a LOT of wasted
resources. Why not update a flag somewhere only when the job has completed
(with a job you can mark this whether or not the originating step
succeeded)?