ASP, AS400 (iSeries), and stored procedures.

Does anyone have any info on how to call a stored procedure with asp classic
? Is it possible? I'm running client access v5r2.
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...Yes it's possible. It's pretty much like calling any stored procedure. I've
done this using both the IBM Client Access ODBC driver as well as the IBM
Client access OLE DB Provider. The Data Access components need to be
installed on the system running the ASP scripts.
Mike O.
"ryadex" <ryadex@.hotmail.com> wrote in message
news:eHQZfJt1DHA.4032@.tk2msftngp13.phx.gbl...

quote:

---

> Does anyone have any info on how to call a stored procedure with asp

---

classic? Is it possible? I'm running client access v5r2.

quote:

---

>
> ****************************************
******************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &

---

ASP.NET resources...sql

array with sql server 2000

hello
I have created one store procedures that return a table variable

'CREATE PROCEDURE sptcondconsiglieri @.immobile_id varchar(6)
as
DECLARE @.tbl table(condomio_id Varchar(6),titlo varchar(5),nominativo varchar(256),stato int)
DECLARE @.colA nvarchar(50)
DECLARE @.MyCursor CURSOR
/*declare @.mycursor1 cursor*/

SET @.MyCursor = CURSOR FAST_FORWARD
FOR
Select nome_consigliere
From t_immoconsiglieri
where immobile_id=@.immobile_id
order by posizione
OPEN @.MyCursor
FETCH NEXT FROM @.MyCursor
INTO @.ColA
WHILE @.@.FETCH_STATUS = 0
BEGIN

Insert @.tbl
SELECT dbo.T_Condomini.Condomino_id,dbo.T_Condomini.titolo,dbo.T_Condomini.Nominativo,dbo.T_UniCond.StCon_id
FROM dbo.T_Condomini INNER JOIN
dbo.T_UniCond ON dbo.T_Condomini.Condomino_id = dbo.T_UniCond.Condomino_id INNER JOIN
dbo.T_Unita ON dbo.T_UniCond.Unita_id = dbo.T_Unita.Unita_id
WHERE (dbo.T_Condomini.Nominativo = @.ColA) AND (dbo.T_UniCond.Dta_fine = '21001231') AND (dbo.T_Unita.Immobile_id =@.immobile_id) and dbo.T_UniCond.StCon_id<>3
FETCH NEXT FROM @.MyCursor
INTO @.ColA
END

CLOSE @.MyCursor
DEALLOCATE @.MyCursor

select * from @.tbl

/*SET QUOTED_IDENTIFIER OFF*/
GO
,
When i call store procedure with vb6
Dim rs as new adodb.recordset
Set cmd = New ADODB.Command
Dim pm As New ADODB.Parameter
' conn.BeginTrans
Set cmd.ActiveConnection = conn
cmd.CommandType = adCmdStoredProc
Set pm = cmd.CreateParameter("immobile_id", adVarChar, adParamInput, 6, immobile_id)
cmd.Parameters.Append pm
cmd.CommandText = "sptcondconsiglieri"
Set rs = cmd.Execute
If Not rs.EOF Then
'
Rs is close
I dont undestand why
Tank you

Could you please provide more details?

Thanks

|||

CREATE PROCEDURE sptcondconsiglieri @.immobile_id varchar(6)
as
SET NOCOUNT ON

-- Original text followed

|||I guess using SET NOCOUNT ON resolved the problem. Several TSQL statements can produce results or messages. So to suppress some of the unwanted messages and read just the SELECT statement output for example you need to SET NOCOUNT ON in the SP. This will eliminate the DONE messages send to the client after the SELECT @.d = ... statement for example. See Books Online for more details on SET NOCOUNT ON effect on returning resultsets.

array with sql server 2000

hello
I have created one store procedures that return a table variable

'CREATE PROCEDURE sptcondconsiglieri @.immobile_id varchar(6)
as
DECLARE @.tbl table(condomio_id Varchar(6),titlo varchar(5),nominativo varchar(256),stato int)
DECLARE @.colA nvarchar(50)
DECLARE @.MyCursor CURSOR
/*declare @.mycursor1 cursor*/

SET @.MyCursor = CURSOR FAST_FORWARD
FOR
Select nome_consigliere
From t_immoconsiglieri
where immobile_id=@.immobile_id
order by posizione
OPEN @.MyCursor
FETCH NEXT FROM @.MyCursor
INTO @.ColA
WHILE @.@.FETCH_STATUS = 0
BEGIN

Insert @.tbl
SELECT dbo.T_Condomini.Condomino_id,dbo.T_Condomini.titolo,dbo.T_Condomini.Nominativo,dbo.T_UniCond.StCon_id
FROM dbo.T_Condomini INNER JOIN
dbo.T_UniCond ON dbo.T_Condomini.Condomino_id = dbo.T_UniCond.Condomino_id INNER JOIN
dbo.T_Unita ON dbo.T_UniCond.Unita_id = dbo.T_Unita.Unita_id
WHERE (dbo.T_Condomini.Nominativo = @.ColA) AND (dbo.T_UniCond.Dta_fine = '21001231') AND (dbo.T_Unita.Immobile_id =@.immobile_id) and dbo.T_UniCond.StCon_id<>3
FETCH NEXT FROM @.MyCursor
INTO @.ColA
END

CLOSE @.MyCursor
DEALLOCATE @.MyCursor

select * from @.tbl

/*SET QUOTED_IDENTIFIER OFF*/
GO
,
When i call store procedure with vb6
Dim rs as new adodb.recordset
Set cmd = New ADODB.Command
Dim pm As New ADODB.Parameter
' conn.BeginTrans
Set cmd.ActiveConnection = conn
cmd.CommandType = adCmdStoredProc
Set pm = cmd.CreateParameter("immobile_id", adVarChar, adParamInput, 6, immobile_id)
cmd.Parameters.Append pm
cmd.CommandText = "sptcondconsiglieri"
Set rs = cmd.Execute
If Not rs.EOF Then
'
Rs is close
I dont undestand why
Tank you

Could you please provide more details?

Thanks

|||

CREATE PROCEDURE sptcondconsiglieri @.immobile_id varchar(6)
as
SET NOCOUNT ON

-- Original text followed

|||I guess using SET NOCOUNT ON resolved the problem. Several TSQL statements can produce results or messages. So to suppress some of the unwanted messages and read just the SELECT statement output for example you need to SET NOCOUNT ON in the SP. This will eliminate the DONE messages send to the client after the SELECT @.d = ... statement for example. See Books Online for more details on SET NOCOUNT ON effect on returning resultsets.

Array params to stored procedures?

This is not obvious to me...

As far as i can tell, you cannot pass an array (or structured) parameter to a stored procedure...

Ok, this meanswhen you have to store data for an item and its sub-items (e.g. a product and its - say- version specific infos)you cannot code all the logic into a single procedure. You need to code it into your DAL, where you first insert then loop to sub-insert...

Is this correct?
Is there any other way to approach the problem?

Thanks a lot. -julioCorrect. There is no such things as array in TSQL|||You can easily get arround this by passing in XML|||Thanks pkr, i'll look at that...

Cheers to both. -julio

Array parameters in stored procedures in SQL 2005?

Hi,
I am not sure if I have understood what is happening with the new SQL server
2005.
Will we be using .Net classes instead of stored procedures?
Will I be able to use an array or list type of parameter for my queries with
SQL server 2005? If yes, how would I do this?
Thanks,
Morten> Will we be using .Net classes instead of stored procedures?
Well this is an option available with us. Not that this is the only way. The
T-SQL style still exists nevertheless.

> Will I be able to use an array or list type of parameter for my queries
with
> SQL server 2005? If yes, how would I do this?
AFAIK, this is still not possible.
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Morten" <morten@.imano.nospam> wrote in message
news:%23L9KEr6AFHA.2180@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am not sure if I have understood what is happening with the new SQL
server
> 2005.
> Will we be using .Net classes instead of stored procedures?
> Will I be able to use an array or list type of parameter for my queries
with
> SQL server 2005? If yes, how would I do this?
>
> Thanks,
> Morten
>|||>> Will I be able to use an array or list type of parameter for my
queries with SQL server 2005? <<
The short answer is no. But the real qustion is why do you want to
make SQL less relational instead of more standardized?
The way this is done in Standard SQL is with a table constructor:
BEGIN
DELETE FROM Parmlist;
INSERT INTO Parmlist
VALUES (a1), (a2), .., (an);
CALL Foobar (...);
END:
Then you use the parameter table in the query or other statement in the
usual manner.

Arkhan:Re: putting dbo explicitly in select staetement

I have witten a lot of stored procedures in my project where I did not put dbo before the user tables.My colleague told me that I have to put dbo for all statements other there could be a problem.

Any thought?,

Please assist.

Arkhan:

There are at least a couple of places in which the owner name prefix -- dbo -- is required including (1) naming of a scalar functions and (2) objects used with schemabinding. In addition, if your "shop standards" are to always designate object names with the owner name then you need to do so.

I can think of at least one situation in which I prefer that objects NOT be owned by dbo. This is at least somewhat controversial so take it with a grain of salt. In DTS "staging" tables I like to have a designated table owner so that that owner has the right to truncate the table without needing the database owner privilege. This does not figure to be relevant to your problem. It is good practice to include the owner name as part of your qualified name. One thing that worries about your question has to do with the practice of deployment of privileges.

I do not like it when I see scores of tables or other database objects that are owned by many different database users. When I see this type of stuff my knee-jerk reaction is that privileges are beging deployed to liberally. And I would guess that if you and your colleagues are seeing many problems from NOT including the owner name that you likely have this privilege problem.

Short answer: Include the dbo portion of the name.

Dave

|||

I've heard it said that not including the schema owner (usually DBO) on your object prefix can result in a "Compile lock" against your stored procedure while the client determines whether there is an object in existence for its own schema. In this situation, multiple users executing the same stored procedure would suffer from a queuing effect as each would place a compile lock on the procedure (or statement).

That being said, I've never been able to repro this is a testing environment and I have yet to see any white papers or KB articles that discuss this so I'd love it if somebody could chime in on this.

|||

Specifying the owner can help the system find the stored procedure faster. It also prevents issues if someone creates the same name procedure with a different owner (or schema in 2005) by mistake. At least if everyone uses two-part names, they will be less likely to make mistakes.

As for the compile locks, I have reproduced recompile locks in my stored procedures. I'm not 100% how I did it, but it seemed to be with temporary tables stored procedures. I added dbo in the front of each stored procedure and table name within the stored procedure and it elliminated most of the problems we had.

ARITHABORT and Indexed Views

I've just implemented indexed views to increase the performance of one of my
stored procedures. However,
it turns out that you cannot insert into a table that is referenced by an
index view unless the ARITHABORT is set to ON.
You cannot set it to ON inside the procedure. (see
http://support.microsoft.com/default.aspx?kbid=305333).
The recommendation from Microsoft is to turn this on from the
application. -- Unfortunately, I don't have control over that. I did find a
solution: "ALTER DATABASE nwind SET ARITHABORT ON".
I'm a little hesitant to use this in production, as many apps use this
particular database. Does anybody know of any potential gotchas? It seems
to me that if my apps were generating divide-by-zero or overflow errors,
they wouldn't be working anyway.The chances of this causing a problem are pretty slim but you should try it
on a backup copy of the database to make sure there aren't some unforeseen
issues.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"MAS" <mark_stricker@.hotmail.com> wrote in message
news:eKGlalNLGHA.2392@.TK2MSFTNGP09.phx.gbl...
> I've just implemented indexed views to increase the performance of one of
> my stored procedures. However,
> it turns out that you cannot insert into a table that is referenced by an
> index view unless the ARITHABORT is set to ON.
> You cannot set it to ON inside the procedure. (see
> http://support.microsoft.com/default.aspx?kbid=305333).
> The recommendation from Microsoft is to turn this on from the
> application. -- Unfortunately, I don't have control over that. I did find
> a solution: "ALTER DATABASE nwind SET ARITHABORT ON".
> I'm a little hesitant to use this in production, as many apps use this
> particular database. Does anybody know of any potential gotchas? It
> seems to me that if my apps were generating divide-by-zero or overflow
> errors, they wouldn't be working anyway.
>
>

ArithAbort and ArithIgnore Options

Hi,

I was recently experiencing a slowness when executing stored procedures from a .NET Application, but it went fast when executing from Query Analyzer. Research led me to find that by turning ArithAbort ON that it forces the SQL Server to use the same Execution plan whether the request is coming from Query Analyzer or the Application.

My concern now is the effect of ArithAbort. I understand what turning this option does, but I am trying to think of a scenario where turning it on could be bad. Does anyone have any suggestions on what I should be aware of when disabling/enabling ArithAbort or ArithIgnore?

Thanks.

-Brian

The rules have changed in SQL Server 2005 the links below explains in detail. Hope this helps.

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

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

Are we not able to edit stored procedures in SQL express?

Have sql 2005 express installed.

Running a database for a DotNet Nuke site

Opened Microsoft SQL Server Management Studio Express.

Navigated to CASPORTAL\Databases\DotNetNuke\Programmability\Stored Procedures\dbo.AddUser

Right clicked dbo.AddUser and selected "modify"

This allowed me to paste the additional code into the right hand window/pane, however when I try and save this it wants to save it a seperate file /query. Is there something I don't understand?

am I not able to edit the original stored procedure?

Hi,

thats a common minsunderstanding. Hitting the disc symbol will save the data, what you will have to do is to execute the stored procedure within this pane. There is a button for this reading "Execute", this will execute the current query presented in your pane (which is an ALTER PROCEDURE statement). This will apply your changes to the database.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Are stored procedures more performant than on the fly sql?

Hello.

I've read the other day (don't remember where :( ) that there is no performance advantages on executing stored procedures than executing sql strings made "on the fly". It said that both operations are precompiled on first execution, and then cached to posterior executions... Is that correct?

Thanks in advance,
Matias

Yes they are as compared to the normal TSQL statements provided the stored procedures a recompiled whenever they are modified for update of cache plan.

You need to refer to the books online and these links http://www.sql-server-performance.com/tn_stored_procedures.asp and http://www.awprofessional.com/articles/article.asp?p=25288 to know more about SPs.

|||

Satya,

your answer is a bit confusing....

are you saying that Stored Procedures ARE faster then on the fly SQL ? or are you saying they are NOT ?

my understanding is that they ARE especially when the SQL is more complex.

This is not the only reason to use sprocs however, other reasons are improved security, improved maintainability and modularity, etc.

cheers,

|||

Yes, I thought that too. Does sql 2005 precompiles and caches text t-sql statements, making them as performant as stored procedures on subsequent calls? I'm not discussing all the advantages of using stored procedures vs on the fly sql, I just want to know if there is any performance penalty using on the fly sql.

Thanks,
Matias

|||

as per this article, "ALL" Sql Statements are cached\precompiled.

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

so that would suggest that there may NOT be a huge performance issue in using sprocs.

The argument to use sprocs for Security and maintainability is more viable.

It would be fairly easy to test this use SQL Profiler......

cheers

Are Stored Procedures bad?

I have always thought that you should use stored procedures when ever
possible. But, after reading this:
http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx
My world view is shackled.
Are stored procedures good or bad?application programmers and database programmers have different goals.
database people need to have the bigger picture in mind. Sure, our data
is ONLY manipulated by one front end. Except for reporting. and OLAP
in 3 years.
And, oh, by the way, we need a NEW front end for part of the data. and
we want to start importing/exporting the data to different sources.
Oh, did I mention the business rules changed again?
My experience is that I need to make it EASY for the app programmers to
use my sprocs.
OTOH, I have to also keep in mind I need to maintain data integrity,
whatever that means for that week. So, in general I control DML into
stored procedures.
If an app programmer wants to create his own select statements, I
usually let them. If a programmer wants to do his own inserts/deletes,
we schedule the security changes for sometime after hell has a frost
depth of 53 inches.
If they go over my head, then I get the app owner to sign in ink they
no longer need the dba to maintain data integrity, and give real world
examples of duplicate customers, or duplicate orders, or deleting
orders and all their history out of the system.
from there the argument digresses down to "dba's can use foreign keys"
blah blah blah, and I digress down to "inclusionary vs exclusionary"
and I repeat my offer to make the tools for the programmer, and the
programmer won't take ownership of data integrity, and the the schedule
gets pushed back even further.
stored procedurs offer a common interface for the data, and some
business rules for a variety of front end apps. they can also offer
significant performance advantages because the dba can make changes to
take advantage fo data layout without changing the various front end
applications.

Are Stored Procedures bad?

I have always thought that you should use stored procedures when ever possible. But, after reading this:http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspxMy world view is shackled.Are stored procedures good or bad?

Fran is an object developer who uses ORM(object relational mapping) to pass the structs to the relational model of the application and sells one ORM product so his opinion is biased. But some T-SQL and PL/SQL writers write code because it works and don't know why it works which can create problems if your application is using an ORM tool you can avoid those problems. But if you write T-SQL and PL/SQL because you know the language and functions, it is said there is no difference in performance.

Are Stored Procedures bad?

I have always thought that you should use stored procedures when ever
possible. But, after reading this:
http://weblogs.asp.net/fbouma/archi...1/18/38178.aspx
My world view is shackled.
Are stored procedures good or bad?application programmers and database programmers have different goals.
database people need to have the bigger picture in mind. Sure, our data
is ONLY manipulated by one front end. Except for reporting. and OLAP
in 3 years.
And, oh, by the way, we need a NEW front end for part of the data. and
we want to start importing/exporting the data to different sources.
Oh, did I mention the business rules changed again?
My experience is that I need to make it EASY for the app programmers to
use my sprocs.
OTOH, I have to also keep in mind I need to maintain data integrity,
whatever that means for that week. So, in general I control DML into
stored procedures.
If an app programmer wants to create his own select statements, I
usually let them. If a programmer wants to do his own inserts/deletes,
we schedule the security changes for sometime after hell has a frost
depth of 53 inches.
If they go over my head, then I get the app owner to sign in ink they
no longer need the dba to maintain data integrity, and give real world
examples of duplicate customers, or duplicate orders, or deleting
orders and all their history out of the system.
from there the argument digresses down to "dba's can use foreign keys"
blah blah blah, and I digress down to "inclusionary vs exclusionary"
and I repeat my offer to make the tools for the programmer, and the
programmer won't take ownership of data integrity, and the the schedule
gets pushed back even further.
stored procedurs offer a common interface for the data, and some
business rules for a variety of front end apps. they can also offer
significant performance advantages because the dba can make changes to
take advantage fo data layout without changing the various front end
applications.