Sql Calculated: 2012

Thursday, March 29, 2012

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)

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

"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

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 with parameter

Does anyone know how to pass a parameter from a stored
procedure to a DataAdapter?
the following is my code:
i used the DataAdapter wizard to set its properties with
my stored procedure.
//create a data adapter
SqlDataAdapter da = sqlDataAdapter1;
//create a data set and fill it by
calling Fill method
DataSet ds = new DataSet("Cust");
da.Fill(ds,"Customers");
//attach data set's default view
to the data grid control
DataGrid1.DataSource = ds;
DataGrid1.DataMember = "Customers";
DataGrid1.DataBind();
**When i run it, it asks for the stored procedure's
parameter which is the value of a text field in my asp.net
application.For performance reasons, you don't want to use the wizards to generate
code in ADO.NET -- you'd be much better off just deleting all the
wizard-generated stuff and starting from scratch. I'd recommend
getting a copy of ADO.NET by David Sceppa, Microsoft Press.
-- Mary
MCW Technologies
http://www.mcwtech.com
On Wed, 12 Nov 2003 14:33:58 -0800, "zoe"
<anonymous@.discussions.microsoft.com> wrote:
>Does anyone know how to pass a parameter from a stored
>procedure to a DataAdapter?
>the following is my code:
>i used the DataAdapter wizard to set its properties with
>my stored procedure.
> //create a data adapter
> SqlDataAdapter da =>sqlDataAdapter1;
> //create a data set and fill it by
>calling Fill method
> DataSet ds = new DataSet("Cust");
> da.Fill(ds,"Customers");
>
> //attach data set's default view
>to the data grid control
> DataGrid1.DataSource = ds;
> DataGrid1.DataMember = "Customers";
> DataGrid1.DataBind();
>
>**When i run it, it asks for the stored procedure's
>parameter which is the value of a text field in my asp.net
>application.

Calling Stored Procedure to get results into another stored procedure

I have a complex stored procedure which in the end, has a select statement and returns a number of rows... such as: Select * from Temp_Table.

I want to access these records in another stored procedure and do some maniulation on the rows, such as sum them or perform other calculations.

How can I access these rows via a select statement or just insert them into a temp table in the 2nd stored procedure?

Thanks so much

Stored Procedures can't be referenced in the FROM clause.

But you can use them as input to an INSERT statement.

Code Snippet

createtable #t1(a int, b int, c int)

insertinto #t1

exec dbo.myproc

|||

One way to do this is to create the temp table in the calling procedure and then load it in the called procedure:

ifobject_id('spy')isnotnulldropproc spy

createproc spy as

insert #x values(1)

return

ifobject_id('spx')isnotnulldropproc spx

createproc spx as

createtable #x(x1 int)

exec spy

select*from #x

return

exec spx

Ron Rice

|||

Thanks for the response...

I converted the Stored Proc to an inline table function. This seems to do the trick.

Tks

calling stored procedure in VC++

Hi,

dont know whether it is the correct forum.

i have a stored procedure to get the user pin.
the stored procedure works perfectly as i tested it both in VB and ASP,
but when i try to call stored procedure from VC++, then i get a
Idispatch error #3092.
here is the code:

BSTR newpin;
_bstr_t qsql = "{call user.getpin(?,{resultset 100,pin})}";
command->PutActiveConnection(conn);
command->PutCommandText(qsql);
command->Parameters->Refresh();
command->Parameters->Item[_variant_t((short)0)]->Value = userid;
rst = command->Execute(NULL,NULL,adCmdText);
if(rst->RecordCount > 0)
{
newpin = rst->Fields->GetItem((short)0)->GetValue();

}

is there any problem with this code.
thanks

It might be a better bet to expose this issue to the SQL users since they're more likely to have encountered this scenario. Try one of the SQL forums. e.g. http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=87&SiteID=1

calling stored procedure in VC++

}

is there any problem with this code.
thanks
Can you provide more complete sample including code for creating stored procedure? Are you sure about syntax "{call user.getpin(?,{resultset 100,pin})}";? Also which line of code throws the exception? BWT RecordCount may be 0 but

rst->Fields->GetItem((short)0)->GetValue();

may still return correct value. Also have you tried PutRefActiveConnection instead? It's difficult to say without more information.

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 in Microsoft SQL server

Hi all ,
I am using VB6 SP 4 .
Need to call stored procedure from Microsoft SQL Server in VB 6 .
The stored procedure is show below :
update_carrier (user_id, carrier_no, card_lost)" where card_lost = N
Anyone have any sample code that i can take a look is much appreciated .
Thankshttp://msdn.microsoft.com/library/d...
cfn.asp
The fragment you posted is not a valid statement or stored procedure by
itself. It may be part of something else - although it does look a
little strange.
David Portas
SQL Server MVP
--|||Yes . I am new in SQL Stored Procedure .
BTW , how to create stored procedure Microsoft SQL Stored Procedure ?
Any tutorial links is much help to me .
Thanks
David Portas wrote:
> http://msdn.microsoft.com/library/d...r />
_9cfn.asp
> The fragment you posted is not a valid statement or stored procedure by
> itself. It may be part of something else - although it does look a
> little strange.
>|||Lookup CREATE PROCEDURE in Books Online.
The link I posted before is to the web version of Books Online - the
SQL Server documentation set. Books Online (BOL) should be your first
point of reference for SQL Server questions. It should be installed on
your machine as part of your SQL installation, or you can get it here:
http://www.microsoft.com/sql/techin...00/default.mspx
David Portas
SQL Server MVP
--

Calling stored procedure in Microsoft SQL server

Hi all ,
I am using VB6 SP 4 .
Need to call stored procedure from Microsoft SQL Server in VB 6 .
The stored procedure is show below :
?update_carrier (user_id, carrier_no, card_lost)" where card_lost = ?N?
Anyone have any sample code that i can take a look is much appreciated .
Thankshttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/adosql/adoprg02_9cfn.asp
The fragment you posted is not a valid statement or stored procedure by
itself. It may be part of something else - although it does look a
little strange.
--
David Portas
SQL Server MVP
--|||Yes . I am new in SQL Stored Procedure .
BTW , how to create stored procedure Microsoft SQL Stored Procedure ?
Any tutorial links is much help to me .
Thanks
David Portas wrote:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adosql/adoprg02_9cfn.asp
> The fragment you posted is not a valid statement or stored procedure by
> itself. It may be part of something else - although it does look a
> little strange.
>|||Lookup CREATE PROCEDURE in Books Online.
The link I posted before is to the web version of Books Online - the
SQL Server documentation set. Books Online (BOL) should be your first
point of reference for SQL Server questions. It should be installed on
your machine as part of your SQL installation, or you can get it here:
http://www.microsoft.com/sql/techinfo/productdoc/2000/default.mspx
--
David Portas
SQL Server MVP
--

Calling stored procedure in Microsoft SQL server

Hi all ,
I am using VB6 SP 4 .
Need to call stored procedure from Microsoft SQL Server in VB 6 .
The stored procedure is show below :
update_carrier (user_id, carrier_no, card_lost)" where card_lost = N
Anyone have any sample code that i can take a look is much appreciated .
Thanks
http://msdn.microsoft.com/library/de...prg02_9cfn.asp
The fragment you posted is not a valid statement or stored procedure by
itself. It may be part of something else - although it does look a
little strange.
David Portas
SQL Server MVP
|||Yes . I am new in SQL Stored Procedure .
BTW , how to create stored procedure Microsoft SQL Stored Procedure ?
Any tutorial links is much help to me .
Thanks
David Portas wrote:
> http://msdn.microsoft.com/library/de...prg02_9cfn.asp
> The fragment you posted is not a valid statement or stored procedure by
> itself. It may be part of something else - although it does look a
> little strange.
>
|||Lookup CREATE PROCEDURE in Books Online.
The link I posted before is to the web version of Books Online - the
SQL Server documentation set. Books Online (BOL) should be your first
point of reference for SQL Server questions. It should be installed on
your machine as part of your SQL installation, or you can get it here:
http://www.microsoft.com/sql/techinf...0/default.mspx
David Portas
SQL Server MVP

Calling Stored Procedure fromanother Stored Procedure

Hi,

I am getting error when I try to call a stored procedure from another. I would appreciate if someone could give some example.

My first Stored Procedure has the following input output parameters:

ALTER PROCEDURE dbo.FixedCharges

@.InvoiceNo int,

@.InvoiceDate smalldatetime,

@.TotalOut decimal(8,2) output

AS ...

I have tried using the following statement to call it from another stored procedure within the same SQLExpress database. It is giving me error near CALL.

CALL FixedCharges (

@.InvoiceNo,

@.InvoiceDate,

@.TotalOut )

Many thanks in advance

James

I believe you want to use 'EXEC'|||

abadincrotch:
I believe you want to use 'EXEC'

You should use System stored procedure sp_executesql, it will take care of the dependcy chain for. Try the link below for details.

http://msdn2.microsoft.com/en-us/library/ms188001.aspx

|||

Caddre:
abadincrotch:
I believe you want to use 'EXEC'
You should use System stored procedure sp_executesql, it will take care of the dependcy chain for. Try the link below for details.
http://msdn2.microsoft.com/en-us/library/ms188001.aspx

you'll still need to EXECUTE (EXEC) sp_executesql to begin with.

|||Exec is not the reason it works sp_executesql takes care of the dependecy chain because all stored procedures are recorded in the sysdepends table, if you run both directly in SQL Server it will give the error the second stored proc is not in sysdepends. So the job of sp_executesql is to register the second stored proc with sysdepends in the Master database.|||

Caddre:
Exec is not the reason it works sp_executesql takes care of the dependecy chain because all stored procedures are recorded in the sysdepends table, if you run both directly in SQL Server it will give the error the second stored proc is not in sysdepends. So the job of sp_executesql is to register the second stored proc with sysdepends in the Master database.

I'm not sure you're understanding his question or my responses to begin with -- he needs to know how to execute a stored procedure, from within another stored procedure. this is done using the EXEC TSQL statement.

|||

(I am getting error when I try to call a stored procedure from another. I would appreciate if someone could give some example.)

That error is because of the broken dependency chain and one of sp_executesql job is to provide the dependency chain.

|||never encountered that problem when using EXEC, and from the look of his syntax, it doesn't seem as though CALL is required.|||

abadincrotch:
never encountered that problem when using EXEC, and from the look of his syntax, it doesn't seem as though CALL is required.

Exec works some times but not always but sp_executesql is the main way to do it, now read what Microsoft says about Exec.

http://msdn2.microsoft.com/en-us/library/ms188332.aspx

|||

Caddre:
abadincrotch:
never encountered that problem when using EXEC, and from the look of his syntax, it doesn't seem as though CALL is required.
Exec works some times but not always but sp_executesql is the main way to do it, now read what Microsoft says about Exec.
http://msdn2.microsoft.com/en-us/library/ms188332.aspx

rather than waste my time re-reading msdn/sqlbol, if you have a point to make there, please make it -- that entry is lengthy.

|||

abadincrotch:
Caddre:
abadincrotch:
never encountered that problem when using EXEC, and from the look of his syntax, it doesn't seem as though CALL is required.
Exec works some times but not always but sp_executesql is the main way to do it, now read what Microsoft says about Exec.
http://msdn2.microsoft.com/en-us/library/ms188332.aspx

rather than waste my time re-reading msdn/sqlbol, if you have a point to make there, please make it -- that entry is lengthy.

and like I said, you still need to CALL or EXEC sp_executesql to begin with, so your point is ... ?

|||

(I'm not sure you're understanding his question or my responses to begin with -- he needs to know how to execute a stored procedure, from within another stored procedure. this is done using the EXEC TSQL statement.)

You said I don't understand what the user asked for when I gave the correct solution, when you gave something that works some times and not always so you have a point I don't.

The Exec works some times and not always sp_executesql works always.

|||

Thanks for your help People,

It worked!! Smile

James

|||

JamesNZ:
Thanks for your help People,
It worked!!
James

out of curiosity, which?

|||

Caddre:
The Exec works some times and not always sp_executesql works always.

somehow I fail to see where it says EXEC doesn't always "work."

yes, the secureables need to have permissions granted to the role/login executing the statement ... that's just plain common sense ... where does it say exec doesn't always work?

Calling Stored Procedure from SSIS

I am trying to call a stored procedure which akes 1 input param from SSIS. I am using Execute SQL Task->Expressions->"exec s_Staging '"+ @.[User::tblName] +"'"

@.[User::tblName] is the variable with Data Type:String ,Value:My_table

SQLStatement->Stored Procedure Name

But It throws an error

[Execute SQL Task] Error: Executing the query "exec s_Staging 'My_Table' " failed with the following error: "Incorrect syntax near 'My_Table' ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

What connection type are you using in your SQL task? Each connection type supports a different syntax.

Use the following syntax in your task:
OLEDB: MyStoredProc ?

ODBC: {call MyStoredProc (?)}
ADO, ADO.NET: MyStoredProc

Also, make sure that you have the paramter binding set appropriately (see http://sqljunkies.com/WebLog/knight_reign/archive/2005/10/05/17016.aspx for more info)|||

Hi,

Try this out :

"exec s_Staging <ParameterName>='"+ @.[User::tblName] +"'"

Where <ParameterName> is parameter in SP whose value you are assigning. Also "IsQueryStoredProcedure" should be set to false because you are executing inline query.

Thanks

Mohit

|||

Thanks a lot now its working.

|||

I am converting a DTS into SSIS, this DTS package first truncates 5 tables and than uses 5 different transformation to insert data from ORACLE source to SQL Destination. I am using Execute SQL Task to truncate the tables and than 5 Data Flow Task to carry out the tranfer of data. My question is,is it avisable to put these Data Flow Task in one Sequence Container. Will it improve the performance of SSIS?

|||

Hi,

Using a sequence container only ensures that all the tasks in the container are executed simultaneously and only comes out of the after completing all the tasks in container. If you have any task to do only after completing all the 5 transfer task then use sequence container otherwise it's an overhead.

Thanks

Mohit

|||

We don't have any such requirement,as this DTS is used to transfer data to 5 different tables. I think it would better not to use it. I wanted to use it as the SSIS is looking very odd with 1 Execute SQl Task and on success 5 Data Flow Task.

Thanks.

|||

The only concern I have about this SSIS is, its running very slow. I just wanted to figure out something to improve the speed.

|||

Paarul wrote:

The only concern I have about this SSIS is, its running very slow. I just wanted to figure out something to improve the speed.

Have you examined the running queries using SQL Server Profiler?|||

Thanks. This works fine. I have another question related to SSIS and WSE 3.0. I have to use custom application within the script task but whenever the script tries to initialize my custom class which uses WSE policy, it fails. What i need to know is if there is any adaptor for WSE 3.0 for SSIS? if not then how can you load wse3policyCache.config file within SSIS so that the custom dll can refer to policy?

Any help will be appreciated.. My email id is gau2902@.yahoo.com

Thanks

Gaurav Gupta

|||

You can provide a config file to the SSIS package host, in which you can inlcude WSE settings. If you just use the MS tools to run packages then target the folowing with a config file including the WSE 3 stuff-

DTExec.exe

DtsDebugHost.exe

dtshost.exe|||

I have tried adding the following code in all 3 config files you have mentioned

<microsoft.web.services3>
<security>
<x509 allowTestRoot="true" revocationMode="Offline" />
</security>
<policy fileName="wse3policyCache.config" />
</microsoft.web.services3>

and have the wse3policyCache.config under the same location. When i run the task SSIS is not able to load the cache config file. Then i have even tried copying policies segment from config file and replacing the policay element mentioned above, but this again didn't work.

Note: The policy and config files work fine in .net wrapper application outside SSIS

sql

Calling Stored Procedure from SSIS

I am trying to call a stored procedure which akes 1 input param from SSIS. I am using Execute SQL Task->Expressions->"exec s_Staging '"+ @.[User::tblName] +"'"

@.[User::tblName] is the variable with Data Type:String ,Value:My_table

SQLStatement->Stored Procedure Name

But It throws an error

What connection type are you using in your SQL task? Each connection type supports a different syntax.

Use the following syntax in your task:
OLEDB: MyStoredProc ?

ODBC: {call MyStoredProc (?)}
ADO, ADO.NET: MyStoredProc

Also, make sure that you have the paramter binding set appropriately (see http://sqljunkies.com/WebLog/knight_reign/archive/2005/10/05/17016.aspx for more info)|||

Hi,

Try this out :

"exec s_Staging <ParameterName>='"+ @.[User::tblName] +"'"

Where <ParameterName> is parameter in SP whose value you are assigning. Also "IsQueryStoredProcedure" should be set to false because you are executing inline query.

Thanks

Mohit

|||

Thanks a lot now its working.

|||

Hi,

Thanks

Mohit

|||

Thanks.

|||

The only concern I have about this SSIS is, its running very slow. I just wanted to figure out something to improve the speed.

|||

Paarul wrote:

The only concern I have about this SSIS is, its running very slow. I just wanted to figure out something to improve the speed.

Have you examined the running queries using SQL Server Profiler?|||

Any help will be appreciated.. My email id is gau2902@.yahoo.com

Thanks

Gaurav Gupta

|||

DTExec.exe

DtsDebugHost.exe

dtshost.exe|||

I have tried adding the following code in all 3 config files you have mentioned

<microsoft.web.services3>
<security>
<x509 allowTestRoot="true" revocationMode="Offline" />
</security>
<policy fileName="wse3policyCache.config" />
</microsoft.web.services3>

Note: The policy and config files work fine in .net wrapper application outside SSIS

Calling Stored Procedure from SSIS

I am trying to call a stored procedure which akes 1 input param from SSIS. I am using Execute SQL Task->Expressions->"exec s_Staging '"+ @.[User::tblName] +"'"

@.[User::tblName] is the variable with Data Type:String ,Value:My_table

SQLStatement->Stored Procedure Name

But It throws an error

What connection type are you using in your SQL task? Each connection type supports a different syntax.

Use the following syntax in your task:
OLEDB: MyStoredProc ?

ODBC: {call MyStoredProc (?)}
ADO, ADO.NET: MyStoredProc

Also, make sure that you have the paramter binding set appropriately (see http://sqljunkies.com/WebLog/knight_reign/archive/2005/10/05/17016.aspx for more info)|||

Hi,

Try this out :

"exec s_Staging <ParameterName>='"+ @.[User::tblName] +"'"

Where <ParameterName> is parameter in SP whose value you are assigning. Also "IsQueryStoredProcedure" should be set to false because you are executing inline query.

Thanks

Mohit

|||

Thanks a lot now its working.

|||

Hi,

Thanks

Mohit

|||

Thanks.

|||

The only concern I have about this SSIS is, its running very slow. I just wanted to figure out something to improve the speed.

|||

Paarul wrote:

The only concern I have about this SSIS is, its running very slow. I just wanted to figure out something to improve the speed.

Have you examined the running queries using SQL Server Profiler?|||

Any help will be appreciated.. My email id is gau2902@.yahoo.com

Thanks

Gaurav Gupta

|||

DTExec.exe

DtsDebugHost.exe

dtshost.exe|||

I have tried adding the following code in all 3 config files you have mentioned

<microsoft.web.services3>
<security>
<x509 allowTestRoot="true" revocationMode="Offline" />
</security>
<policy fileName="wse3policyCache.config" />
</microsoft.web.services3>

Note: The policy and config files work fine in .net wrapper application outside SSIS

Tuesday, March 27, 2012

Calling Stored Procedure from COM+

Dear All,
We're trying to resolve a rather odd performance issue on one of our
servers.
We have a COM+ component calling a stored sprocedure in a SQL Server
database. The COM+ component is on a different machine, so the call is
going across a network. The number of logical reads on the database
from this stored procedure is in the gazillions, but when we call the
stored procedure "manually", so to speak, through Query Analyser on our
desktops, the logical reads drop to almost nothing in comparison.
Does anyone have any idea why this would be?
AaronUpon further investigation, we've found that the stored procedure when
called by the COM+ component uses a different execution plan from that
when it's called manually. It uses a different index which uses a
bookmark lookup, with all the attending perfomance hits. We've now
changed the stored procedure to force it to use a specific index which
doesn't use a bookmark lookup and we're testing it to see if that
improves the performance. I'd still like to know why it does this.|||Hi Aaron,
Please post your stored procedure here and importantly how you are calling
it from the application and through query analyser.
It could be something to do with parameterisation / parameter sniffing.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
<aaron@.castle-cadenza.demon.co.uk> wrote in message
news:1148031447.871209.218660@.38g2000cwa.googlegroups.com...
> Upon further investigation, we've found that the stored procedure when
> called by the COM+ component uses a different execution plan from that
> when it's called manually. It uses a different index which uses a
> bookmark lookup, with all the attending perfomance hits. We've now
> changed the stored procedure to force it to use a specific index which
> doesn't use a bookmark lookup and we're testing it to see if that
> improves the performance. I'd still like to know why it does this.
>|||In addition to Tony's thoughts:
IT could be because of different SET options, for instance isolation level.
COM+ defaults to
serializable, where others defaults to READ COMMITTED.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<aaron@.castle-cadenza.demon.co.uk> wrote in message
news:1148031447.871209.218660@.38g2000cwa.googlegroups.com...
> Upon further investigation, we've found that the stored procedure when
> called by the COM+ component uses a different execution plan from that
> when it's called manually. It uses a different index which uses a
> bookmark lookup, with all the attending perfomance hits. We've now
> changed the stored procedure to force it to use a specific index which
> doesn't use a bookmark lookup and we're testing it to see if that
> improves the performance. I'd still like to know why it does this.
>

Calling Stored Procedure from COM+

Dear All,
We're trying to resolve a rather odd performance issue on one of our
servers.
We have a COM+ component calling a stored sprocedure in a SQL Server
database. The COM+ component is on a different machine, so the call is
going across a network. The number of logical reads on the database
from this stored procedure is in the gazillions, but when we call the
stored procedure "manually", so to speak, through Query Analyser on our
desktops, the logical reads drop to almost nothing in comparison.
Does anyone have any idea why this would be?
AaronUpon further investigation, we've found that the stored procedure when
called by the COM+ component uses a different execution plan from that
when it's called manually. It uses a different index which uses a
bookmark lookup, with all the attending perfomance hits. We've now
changed the stored procedure to force it to use a specific index which
doesn't use a bookmark lookup and we're testing it to see if that
improves the performance. I'd still like to know why it does this.|||Hi Aaron,
Please post your stored procedure here and importantly how you are calling
it from the application and through query analyser.
It could be something to do with parameterisation / parameter sniffing.
Tony.
--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
<aaron@.castle-cadenza.demon.co.uk> wrote in message
news:1148031447.871209.218660@.38g2000cwa.googlegroups.com...
> Upon further investigation, we've found that the stored procedure when
> called by the COM+ component uses a different execution plan from that
> when it's called manually. It uses a different index which uses a
> bookmark lookup, with all the attending perfomance hits. We've now
> changed the stored procedure to force it to use a specific index which
> doesn't use a bookmark lookup and we're testing it to see if that
> improves the performance. I'd still like to know why it does this.
>|||In addition to Tony's thoughts:
IT could be because of different SET options, for instance isolation level. COM+ defaults to
serializable, where others defaults to READ COMMITTED.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<aaron@.castle-cadenza.demon.co.uk> wrote in message
news:1148031447.871209.218660@.38g2000cwa.googlegroups.com...
> Upon further investigation, we've found that the stored procedure when
> called by the COM+ component uses a different execution plan from that
> when it's called manually. It uses a different index which uses a
> bookmark lookup, with all the attending perfomance hits. We've now
> changed the stored procedure to force it to use a specific index which
> doesn't use a bookmark lookup and we're testing it to see if that
> improves the performance. I'd still like to know why it does this.
>

Calling Stored Procedure from COM+

Calling Stored Procedure from ASP-Problem

I am having a problem to execute a stored procedure from ASP.

I have created several stored procedure(SP) and everything works fine from ASP except 3 of my StoredProcedures.

This SP has an INSERT Statement where the SELECT query has couple of joins to master tables. When I run this StoredProcedure (SP) in the server using Queryanalyser, it takes nearly 30 seconds to complete, and it runs without any problems.

When I execute this SP from ASP like any other SPs, procedure starts and it doesnot complete the INSERT statement. ASP dosnot raise any errors, i checked for error messages and no errors. I don't know whats going on..?
I also observerd that, SPs which was failing was taking more than 30 seconds to complete when I run from the Server, and all other SPs which was running ok was taking less than 20 seconds. So is there any settings in the ADODB connection? or any other settings in ASP?

Please help

-IshBy default ASP times out after 30 seconds. Talk to your webmaster or network admin about increasing the timeout period. Better yet, if you can, optimize the stored procedure to run faster.

Richard

Originally posted by Ishwara Marakaj
I am having a problem to execute a stored procedure from ASP.

I have created several stored procedure(SP) and everything works fine from ASP except 3 of my StoredProcedures.

This SP has an INSERT Statement where the SELECT query has couple of joins to master tables. When I run this StoredProcedure (SP) in the server using Queryanalyser, it takes nearly 30 seconds to complete, and it runs without any problems.

When I execute this SP from ASP like any other SPs, procedure starts and it doesnot complete the INSERT statement. ASP dosnot raise any errors, i checked for error messages and no errors. I don't know whats going on..?
I also observerd that, SPs which was failing was taking more than 30 seconds to complete when I run from the Server, and all other SPs which was running ok was taking less than 20 seconds. So is there any settings in the ADODB connection? or any other settings in ASP?

Please help

-Ish|||Originally posted by ambroser
By default ASP times out after 30 seconds. Talk to your webmaster or network admin about increasing the timeout period. Better yet, if you can, optimize the stored procedure to run faster.

Richard

ambroser,

Thanks for your reply. Are you talking about script timeout or session timeout? I already increased this timeout values in the server, but no luck.
When these stored procedures fails they don't raise any erros, they just don't execute, ASP continues and processes the next stored procedure. Basically i don't get any timeout errors.

Thanks
Ish

Calling Stored Procedure from asp without waiting

Hi,
I'm developping a asp application that somestimes needs some heavy
synchronisation done. I wrote a stored procedure for that purpose witch
takes approximatly 15 minutes. Right now I run this directly on the
server (using MS SQL Server management studio). But I would love to be
able to call it from a asp-page. But I don't want the asp script to
wait for 15 minutes before returning. I figured, it should be possible
to tell the stored procedure call that it should run on the server on
its own. Is this possible? And how? Are there better/other solutions?
Thnx for your time,
Anne SchuthIs this done once a day?
Maybe you should create a scheduled job
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thnx for your reply.
No, I should have mentioned that. It's not done with a regular interval
at all.
Anne|||In ASP.Net you could use an asynchronous call.
If you create a job in SQL Server Agent, you could call a stored procedure
that starts the job. The stored procedure does not wait for the job to
finish, so neither does your ASP page.
"Anne" <anne.schuth@.gmail.com> wrote in message
news:1145632601.510533.45240@.z34g2000cwc.googlegroups.com...
> Hi,
> I'm developping a asp application that somestimes needs some heavy
> synchronisation done. I wrote a stored procedure for that purpose witch
> takes approximatly 15 minutes. Right now I run this directly on the
> server (using MS SQL Server management studio). But I would love to be
> able to call it from a asp-page. But I don't want the asp script to
> wait for 15 minutes before returning. I figured, it should be possible
> to tell the stored procedure call that it should run on the server on
> its own. Is this possible? And how? Are there better/other solutions?
> Thnx for your time,
> Anne Schuth
>|||is it asp.NET - in which case you want to use an asynchronous thread to
fire off the sp|||I would build an ASP page.. which called a stored procedure.
And that stored procedure would schedule a job (like 10 seconds into the
future) to run.. and that job would run a stored procedure which did your
15min of work.
uspFireOffTheJobWhichRunsTheMainStoredPr
ocedure
-- the contents of this stor proc would be
dbo.uspJobScheduleUpdate ( 'MyLongJob1' , 'JobSchedule1ForMyLongJob1' , 20
uspLongRunningStoredProcedure
--the contents of this proc would be ... your code to do the 15 minutes or
work.
..
That was about 2 w

s of my time (the stuff above and below), so post a
thank you... if you go this route.
Here is some code to help ,,, this would programmatically CREATE the job.
The 2 procedure above ... esp the uspFireOffxxxxx would be needed to
SCHEDULE the job
declare @.jobName varchar(128)
declare @.jobStepName varchar(128)
declare @.jobScheduleName varchar(128)
declare @.uspName varchar(128)
declare @.uuid uniqueidentifier
select @.jobName = 'MyLongJob1'
select @.uspName = 'dbo.uspLongRunningStoredProcedure'
select @.jobStepName = 'JobStep1For' + @.jobName
select @.jobScheduleName = 'JobSchedule1For' + @.jobName
select @.uspName = 'EXEC ' + @.uspName
declare @.serverName varchar(128)
select @.serverName = CONVERT(varchar(128) , SERVERPROPERTY('servername') )
--print @.serverName
declare @.dbName varchar(128)
select @.dbName = DB_NAME()
EXEC msdb.dbo.sp_add_job @.job_name = @.jobName,
@.enabled = 1,
@.description = @.jobName,
@.owner_login_name = 'sa' -- << THIS ONE YOU NEED TO WATCH .. and use
the loginname that your asp page uses to connect to the db()
EXEC msdb.dbo.sp_add_jobstep @.job_name = @.jobName,
@.step_name = @.jobStepName,
@.subsystem = 'TSQL',
@.database_name = @.dbName ,
@.command = @.uspName
EXEC msdb.dbo.sp_add_jobserver @.job_name = @.jobName,
@.server_name = @.serverName
-- END create jobs programatically
here is a final script.. I created to update the schedule on a job .. by
using its name, schedueName and delay seconds.
in my testing I found 10 seconds was the smallest increment I could use.
if exists (select * from sysobjects
where id = object_id('dbo.uspJobScheduleUpdate') and sysstat & 0xf = 4)
drop procedure dbo.uspJobScheduleUpdate
GO
CREATE Procedure dbo.uspJobScheduleUpdate ( @.jobName varchar(128) ,
@.jobScheduleName varchar(128) , @.delaySeconds int )
AS
SET NOCOUNT ON
declare @.minimumTimeDelaySeconds int
Select @.minimumTimeDelaySeconds = 10
if @.delaySeconds < @.minimumTimeDelaySeconds
BEGIN
select @.delaySeconds = @.minimumTimeDelaySeconds
END
declare @.now datetime
select @.now = GETDATE()
Select @.now = DATEADD(s , @.delaySeconds , @.now)
--print @.now
--select DATEPART(s , @.now)
--select DATEPART(hh , @.now)
--select DATEPART(mi , @.now)
/*
[@.active_start_date =] active_start_date
Is the date on which execution of the job can begin. active_start_date is
int,
with a default of NULL. Values must be formatted as YYYYMMDD.
If active_start_date is not NULL, the date must be greater than or equal to
19900101.
*/
declare @.year varchar(4)
declare @.month varchar(2)
declare @.day varchar(2)
declare @.yearint int
declare @.monthint int
declare @.dayint int
declare @.hour varchar(2)
declare @.minute varchar(2)
declare @.second varchar(2)
declare @.hourint int
declare @.minuteint int
declare @.secondint int
--year does not need prefixed "0"
select @.yearint= DATEPART(yyyy , @.now)
select @.year = convert(varchar(4) , @.yearint )
select @.monthint= DATEPART(m , @.now)
if @.monthint < 10
BEGIN
select @.month = '0' + convert(varchar(1) , @.monthint )
END
else
BEGIN
select @.month = convert(varchar(2) , @.monthint )
END
select @.dayint= DATEPART(d , @.now)
if @.dayint < 10
BEGIN
select @.day = '0' + convert(varchar(1) , @.dayint )
END
else
BEGIN
select @.day = convert(varchar(2) , @.dayint )
END

select @.hourint= DATEPART(hh , @.now)
if @.hourint < 10
BEGIN
select @.hour = '0' + convert(varchar(1) , @.hourint )
END
else
BEGIN
select @.hour = convert(varchar(2) , @.hourint )
END
select @.minuteint= DATEPART(mi , @.now)
if @.minuteint < 10
BEGIN
select @.minute = '0' + convert(varchar(1) , @.minuteint )
END
else
BEGIN
select @.minute = convert(varchar(2) , @.minuteint )
END
select @.secondint= DATEPART(s , @.now)
if @.secondint < 10
BEGIN
select @.second = '0' + convert(varchar(1) , @.secondint )
END
else
BEGIN
select @.second = convert(varchar(2) , @.secondint )
END
--print '*' + @.hour + '*'
--print '*' + @.minute + '*'
--print '*' + @.second + '*'
declare @.derivedstartdate varchar(12)
select @.derivedstartdate = @.year + @.month + @.day
declare @.derivedstarttime varchar(12)
select @.derivedstarttime = @.hour + @.minute + @.second
--print @.derivedstartdate
--print @.derivedstarttime
declare @.alreadyExists bit
declare @.enabled bit
declare @.active_start_date int
declare @.active_start_time int
EXEC dbo.uspJobScheduleExists @.jobName , @.jobScheduleName , @.alreadyExists
output , @.enabled output , @.active_start_date output , @.active_start_time
output
/*
print 'Debugging Values'
print '@.jobName=' + @.jobName
print '@.jobScheduleName=' + @.jobScheduleName
print '@.alreadyExists=' + convert(varchar(8) , @.alreadyExists)
print '@.enabled=' + convert(varchar(8) , @.enabled)
print '@.active_start_date=' + convert(varchar(16) , @.active_start_date)
print '@.active_start_time=' + convert(varchar(16) , @.active_start_time)
print '/Debugging Values'
*/
declare @.jobSchedulingCheck int
if @.alreadyExists = 0 --FALSE
BEGIN
EXEC @.jobSchedulingCheck = msdb.dbo.sp_add_jobschedule
@.job_name = @.jobName,
@.name = @.jobScheduleName,
@.enabled = 1,
@.freq_type = 1 ,
@.freq_interval = 1 , --once
@.active_start_date = @.derivedstartdate ,
@.active_start_time = @.derivedstarttime--'153000' --(3:30 pm) 24hr HHMMSS.
END
else
BEGIN
declare @.allClearToUpdateTheJob bit
select @.allClearToUpdateTheJob = 1 -- default to true
if @.enabled <> 0 -- The job is enabled,,but how far in the future'
BEGIN
--most times, if its enabled..then its set to fire pretty quickly in the
future..so set it to false..but also check a "too far in the future" date
select @.allClearToUpdateTheJob = 0 -- false
--/*
if @.active_start_date - @.derivedstartdate >= 0 -- the date is in the future
..or today
begin
if @.active_start_time - @.derivedstarttime > @.delaySeconds -- the time is
greater in the future than the delay period
begin
--print 'too far into the future for the job!'
select @.allClearToUpdateTheJob = 1-- while it was enabled..it was too far
into the future
end
end
--*/
END
--if its not enabled, then the @.allClearToUpdateTheJob value will not
changed..thus it will be updated
if @.allClearToUpdateTheJob <> 0
BEGIN
EXEC @.jobSchedulingCheck = msdb.dbo.sp_update_jobschedule --0 (success) or 1
(failure)
@.job_name = @.jobName,
@.name = @.jobScheduleName,
@.enabled = 1,
@.freq_type = 1 ,
@.freq_interval = 1 , --once
@.active_start_date = @.derivedstartdate ,
@.active_start_time = @.derivedstarttime--'153000' --(3:30 pm) 24hr HHMMSS.
END
END
--Return Code Values
--0 (success) or 1 (failure)
if (@.jobSchedulingCheck<>0)
BEGIN
INSERT INTO dbo.tblAuditTrail (SubjectID , ObjectID , ShortDescription)
VALUES (@.jobName , @.jobScheduleName, 'sp_add_jobschedule or
sp_update_jobschedule FAILED.')
END
SELECT( @.jobSchedulingCheck )
if (@.jobSchedulingCheck IS NOT NULL)
begin
return @.jobSchedulingCheck
end
SET NOCOUNT OFF
GO
"Anne" <anne.schuth@.gmail.com> wrote in message
news:1145633124.959348.250700@.g10g2000cwb.googlegroups.com...
> Thnx for your reply.
> No, I should have mentioned that. It's not done with a regular interval
> at all.
> Anne
>|||Yeah, that does deserve a THANK YOU!
I will need some time to get it all together and running but this
certainly points me in the right direction! I've never used jobs
before, so din't think of it.
Thnx all for the quick replies!
(NP: I don't use asp.net)|||wow'
all that hour minute second stuff can be replace with
declare @.now datetime
select @.now = getdate()
select
convert(varchar,@.now,112),replace(conver
t(varchar,@.now,108),':','')
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||If you are on 2005, consider using Service Broker for this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anne" <anne.schuth@.gmail.com> wrote in message
news:1145632601.510533.45240@.z34g2000cwc.googlegroups.com...
> Hi,
> I'm developping a asp application that somestimes needs some heavy
> synchronisation done. I wrote a stored procedure for that purpose witch
> takes approximatly 15 minutes. Right now I run this directly on the
> server (using MS SQL Server management studio). But I would love to be
> able to call it from a asp-page. But I don't want the asp script to
> wait for 15 minutes before returning. I figured, it should be possible
> to tell the stored procedure call that it should run on the server on
> its own. Is this possible? And how? Are there better/other solutions?
> Thnx for your time,
> Anne Schuth
>|||
Thanks,
I felt like what I was doing was too much drama when I wrote it.
I appreciate the convert tip.
SQL wrote:
> wow'
> all that hour minute second stuff can be replace with
> declare @.now datetime
> select @.now = getdate()
> select
> convert(varchar,@.now,112),replace(conver
t(varchar,@.now,108),':','')
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/

Calling stored procedure from another stored procedure

Is it possible to call one sp from another sp?
I've been hunting around for an example to do this and just can't seem to find one.
Anyone have a link for this or a sample?
Thanks all,
Zath

Yes, you can. Just use EXEC usp_secondStoredProc @.params inside your first SP.

Nick

Calling Stored Procedure from an ActiveX task

I'm attempting to call a storedprocedure from within an ActiveX task in
a DTS and am getting a "Command Text was not set for the command
object" error. I have no problem if I replace the stored procedure call
with the actual SQL.

Is it possible to directly call a SP via an ActiveX task? If yes, could
somebody help me with the syntax please?

This is what I have currently have

Set Conn = CreateObject("ADODB.Connection")
SQL = "exec (MySP)"
set RLoop=conn.Execute(SQL)

do while (NOT RLoop.EOF)
msgbox RLoop(1)
RLoop.MoveNext

Loop

TIA

KarthikKarthik (karthiksmiles@.gmail.com) writes:
> I'm attempting to call a storedprocedure from within an ActiveX task in
> a DTS and am getting a "Command Text was not set for the command
> object" error. I have no problem if I replace the stored procedure call
> with the actual SQL.
> Is it possible to directly call a SP via an ActiveX task? If yes, could
> somebody help me with the syntax please?
> This is what I have currently have
> Set Conn = CreateObject("ADODB.Connection")
> SQL = "exec (MySP)"
> set RLoop=conn.Execute(SQL)

Cmd = CreateObject("ADODB.Command")
Cmd.ActiveConnection = Conn
Cmd.CommandType = adStoredProcedure
Cmd.CommandText = "MySP"
Set rs = cmd.Execute

Not that I have ever done it in DTS, but this should be like ADO anywhere
else.

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

Calling Stored Procedure from a stored procedure on where clause (URGENT!)

I need a stored procedure that include dynamic where clause. so I
created these two sp. GetCampaigns calls Checkvalues sp to complete
where clauseb but it doesn't work. any idea ?
CREATE PROCEDURE GetCampaigns (@.CampaignTypeId int,@.CardTypeId int)
AS
SELECT Campaigns.* FROM Campaigns
WHERE (CheckValues 'Campaigns.CampaignTypesId',@.CampaignTypeId) AND
(CheckValues 'Campaigns.CardTypesId',@.CardTypeId)
GO
CREATE PROCEDURE CheckValues(@.InitialStr nvarchar(30),@.Type int)
AS
IF((@.Type = 1) OR (@.Type = 0))
SELECT @.InitialStr + '!=0'
ELSE
SELECT @.InitialStr + '=' + @.Type
GOUse a function and not a procedure for CheckValues
Daniel EYER
"ONEY" <ozcankanbur@.gmail.com> a crit dans le message de
news:c16976dd.0502220317.735fa507@.posting.google.com...
> I need a stored procedure that include dynamic where clause. so I
> created these two sp. GetCampaigns calls Checkvalues sp to complete
> where clauseb but it doesn't work. any idea ?
>
> CREATE PROCEDURE GetCampaigns (@.CampaignTypeId int,@.CardTypeId int)
> AS
> SELECT Campaigns.* FROM Campaigns
> WHERE (CheckValues 'Campaigns.CampaignTypesId',@.CampaignTypeId) AND
> (CheckValues 'Campaigns.CardTypesId',@.CardTypeId)
> GO
> CREATE PROCEDURE CheckValues(@.InitialStr nvarchar(30),@.Type int)
> AS
> IF((@.Type = 1) OR (@.Type = 0))
> SELECT @.InitialStr + '!=0'
> ELSE
> SELECT @.InitialStr + '=' + @.Type
> GO|||Oney
Don't use dynamic WHERE condition ,instead read this great article
http://www.sommarskog.se/dyn-search.html
"Daniel Eyer" <daniel.eyer@.free.fr> wrote in message
news:421b2391$0$1226$8fcfb975@.news.wanadoo.fr...
> Use a function and not a procedure for CheckValues
> Daniel EYER
> "ONEY" <ozcankanbur@.gmail.com> a crit dans le message de
> news:c16976dd.0502220317.735fa507@.posting.google.com...
>sql

Calling stored procedure from a C application

I have an ODBC/C program and I want to call the stored procedures
sp_addlogin and sp_adduser in it. Can someone please provide me with
some sample code showing the best way to do this.[posted and mailed, please reply in news]

Jocky MacLochray (ian.lochray@.ssi-world.com) writes:
> I have an ODBC/C program and I want to call the stored procedures
> sp_addlogin and sp_adduser in it. Can someone please provide me with
> some sample code showing the best way to do this.

http://www.microsoft.com/downloads/...&DisplayLang=en

These samples were also in the original Books Online that came with SQL
Server, but was removed from later versions. The above link gives you
revised samples. I believe the main difference is that the revised samples
uses Windows authentication.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns955E841276F14Yazorman@.127.0.0.1>...
> [posted and mailed, please reply in news]
> Jocky MacLochray (ian.lochray@.ssi-world.com) writes:
> > I have an ODBC/C program and I want to call the stored procedures
> > sp_addlogin and sp_adduser in it. Can someone please provide me with
> > some sample code showing the best way to do this.
> http://www.microsoft.com/downloads/...&DisplayLang=en
> These samples were also in the original Books Online that came with SQL
> Server, but was removed from later versions. The above link gives you
> revised samples. I believe the main difference is that the revised samples
> uses Windows authentication.

Hi,

Did you try using EXEC sp_addlogin from C program.

Thursday, March 29, 2012

Calling stored procedures from Access 2000

Calling stored procedures from Access 2000

Calling stored procedures from Access 2000

Calling Stored Procedures

calling stored procedure with parameter

Calling Stored Procedure to get results into another stored procedure

calling stored procedure in VC++

calling stored procedure in VC++

calling stored procedure in project

Calling stored procedure in Microsoft SQL server

Calling stored procedure in Microsoft SQL server

Calling stored procedure in Microsoft SQL server

Calling Stored Procedure fromanother Stored Procedure

Calling Stored Procedure from SSIS

Calling Stored Procedure from SSIS

Calling Stored Procedure from SSIS

Tuesday, March 27, 2012

Calling Stored Procedure from COM+

Calling Stored Procedure from COM+

Calling Stored Procedure from COM+

Calling Stored Procedure from ASP-Problem

Calling Stored Procedure from asp without waiting

Calling stored procedure from another stored procedure

Calling Stored Procedure from an ActiveX task

Calling Stored Procedure from a stored procedure on where clause (URGENT!)

Calling stored procedure from a C application

Sql Calculated

Blog Archive

About Me