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

ASP Jscript to obtain Recordset & o/p params

Does anyone know how to obtain the returned recordsets and output parameters
from a stored procedure by using ASP Jscript?
e.g.: I've got a sproc,
create proc testsp
@.strSN varchar(30), --<-- as i/p param for serial #
@.iProdNum int output --<-- as o/p param for a specific reason
as
select * from Inventory
...
set @.iProdNum= (an integer for o/p param)
Therefore, I wrote:
<%@.LANGUAGE="JAVASCRIPT"%>
<!--#include virtual="/Connections/cnInv.asp" -->
<%
var cmdGetItem = Server.CreateObject("ADODB.Command");
cmdGetItem.ActiveConnection = MM_cnInv_STRING;
cmdGetItem.CommandText = "dbo.testsp";
cmdGetItem.CommandType = 4;
cmdGetItem.CommandTimeout = 0;
cmdGetItem.Prepared = true;
cmdGetItem.Parameters.Append(cmdGetItem.CreateParameter("@.RETURN_VALUE",
3, 4,4));
cmdGetItem.Parameters.Append(cmdGetItem.CreateParameter("@.strSN", 200,
1,30, String(Request.Form("txtSN"))));
cmdGetItem.Parameters.Append(cmdGetItem.CreateParameter("@.iProdNum", 3,
2,4));
var oRst=cmdGetItem.Execute();
// now --> oRst <-- holds the returned recordset by (select * from
Inventory)
// but neither --> cmdGetItem.Parameters.Item("@.RETURN_VALUE").Value <--
// nor --> cmdGetItem.Parameters.Item("@.iProdNum").Value <-- contains
nothing
%>
However, I only get the recordset returned by (select * from inventory) but
nothing from @.iProdNum by my ASP Jscript.
Thanks,
LeonardPADO will return output parameters in a separate recordset. You'll need to
use the NextRecordset method after retrieving the SELECT results. I don't
know JScript but try something like the following after processing the query
results:
oRst = oRst.NextRecordset;
Hope this helps.
Dan Guzman
SQL Server MVP
"Leonard Poon" <leonardpoon@.hotmail.com> wrote in message
news:eEEeVsqNFHA.3760@.TK2MSFTNGP12.phx.gbl...
> Does anyone know how to obtain the returned recordsets and output
> parameters
> from a stored procedure by using ASP Jscript?
> e.g.: I've got a sproc,
> create proc testsp
> @.strSN varchar(30), --<-- as i/p param for serial #
> @.iProdNum int output --<-- as o/p param for a specific reason
> as
> select * from Inventory
> ...
> set @.iProdNum= (an integer for o/p param)
> Therefore, I wrote:
> <%@.LANGUAGE="JAVASCRIPT"%>
> <!--#include virtual="/Connections/cnInv.asp" -->
> <%
> var cmdGetItem = Server.CreateObject("ADODB.Command");
> cmdGetItem.ActiveConnection = MM_cnInv_STRING;
> cmdGetItem.CommandText = "dbo.testsp";
> cmdGetItem.CommandType = 4;
> cmdGetItem.CommandTimeout = 0;
> cmdGetItem.Prepared = true;
> cmdGetItem.Parameters.Append(cmdGetItem.CreateParameter("@.RETURN_VALUE",
> 3, 4,4));
> cmdGetItem.Parameters.Append(cmdGetItem.CreateParameter("@.strSN", 200,
> 1,30, String(Request.Form("txtSN"))));
> cmdGetItem.Parameters.Append(cmdGetItem.CreateParameter("@.iProdNum", 3,
> 2,4));
> var oRst=cmdGetItem.Execute();
> // now --> oRst <-- holds the returned recordset by (select * from
> Inventory)
> // but neither --> cmdGetItem.Parameters.Item("@.RETURN_VALUE").Value <--
> // nor --> cmdGetItem.Parameters.Item("@.iProdNum").Value <-- contains
> nothing
> %>
> However, I only get the recordset returned by (select * from inventory)
> but
> nothing from @.iProdNum by my ASP Jscript.
> Thanks,
> LeonardP
>|||Dan Guzman wrote:
> ADO will return output parameters in a separate recordset.
Really? I thought you had to use the Parameters collection ...
Are you sure?
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||You are right that the parameters collection contains the output values.
What I mean is that the preceding recordset(s) need to be processed before
output parameter values can be retrieved from the collection.
Hope this helps.
Dan Guzman
SQL Server MVP
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:ORIZDhsNFHA.3704@.TK2MSFTNGP12.phx.gbl...
> Dan Guzman wrote:
> Really? I thought you had to use the Parameters collection ...
> Are you sure?
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>|||Leonard Poon wrote:
> Does anyone know how to obtain the returned recordsets and output
> parameters from a stored procedure by using ASP Jscript?
>
ADO is ADO - whether it's being called by vbscript or jscript.
To retrieve output or return parameters, you need to either close the
recordset that is returned by your procedure, or retrieve the last record in
that recordset. The output and return parameter values are not sent until
the resultset is completely sent. My practice is to use GetRows to read the
data into an array and close the recordset, allowing me to access the output
and return parameter values. But this is slightly awkward in jscript, whose
arrays are not multidimensional. There is a a way to use GetRows in
jscript - do a google search for jscript and getrows to see how this is
done.
To suppress any extra resultsets from being generated by informational
messages, it is a good practice to use "SET NOCOUNT ON" at the beginnning of
all your procedures.
Bob Barrows
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

ASP error with stored procedure

SQL Server 2000 on Windows 2003 SP1 and IIS6
I'm getting the following error from my ASP page:
Microsoft OLE DB Provider for SQL Server error '80040e14'
Formal parameter '@.mname' was defined as OUTPUT but the actual parameter
not declared OUTPUT.
However, the formal parameter '@.mname' was used in a previous stored
procedure accessed by the same command object and that parameter has been
been deleted prior to the call.
Here's the DDL for the procedure:
CREATE PROCEDURE InsertAndIdentifyNewStarter
@.peopleID INTEGER,
@.newstarterID BIGINT OUTPUT
AS
INSERT INTO newstarter (peopleID)
VALUES (@.peopleID)
SELECT @.newstarterID = SCOPE_IDENTITY()
GO
And the code snippet that's giving the problem:
Dim cmd, ln
Set cmd = Server.CreateObject("ADODB.Command")
With cmd
.ActiveConnection = "Provider=" & DBPROVIDER & ";" & CONNECTIONSTRING
.CommandType = adCmdStoredProc
.CommandText = "InsertAndIdentifyPerson"
.Parameters.Append .CreateParameter("@.fname", adVarChar, _
adParamInput, 100, firstname)
.Parameters.Append .CreateParameter("@.mname", adVarChar, _
adParamInput, 100, middlename)
.Parameters.Append .CreateParameter("@.sname", adVarChar, _
adParamInput, 100, lastname)
.Parameters.Append .CreateParameter("@.fullname", adVarChar, _
adParamInput, 200, firstname & " " & lastname)
.Parameters.Append .CreateParameter("@.datestarted", adDBDate, _
adParamInput, 10, DBStartDate)
.Parameters.Append .CreateParameter("@.enabled", adInteger, _
adParamInput, 10, 1)
.Parameters.Append .CreateParameter("@.existsinaccounts", _
adInteger, adParamInput, 10, 0)
.Parameters.Append .CreateParameter("@.loggedon", adInteger, _
adParamInput, 10, 0)
.Parameters.Append .CreateParameter("@.itstatus", adInteger, _
adParamInput, 10, 5) ' 5 = New Starter
.Parameters.Append .CreateParameter("@.pID", adInteger, _
adParamOutput, 10)
.Execute ln, , adExecuteNoRecords
pID = .Parameters("@.pID")
' Delete the parameters so that we can reuse the command object
While .Parameters.Count > 0
.Parameters.Delete 0
Wend
' Create a newstarter record for this person
.CommandText = "InsertAndIdentifyPerson"
.Parameters.Append .CreateParameter("@.peopleID", adBigInt, _
adParamInput, 10, pID)
.Parameters.Append .CreateParameter("@.newstarterID", _
adBigInt, adParamOutput, 10)
.Execute ln, , adExecuteNoRecords
' ** The preceding line produces the error **
newstarterID = .Parameters("@.newstarterID")
End With
Set cmd = Nothing
Any ideas what I'm doing wrong or what the problem is?
TIA,
Geoff> CREATE PROCEDURE InsertAndIdentifyNewStarterd">
> snip
> .CommandText = "InsertAndIdentifyPerson"
It looks to me like you are executing the wrong proc.
Hope this helps.
Dan Guzman
SQL Server MVP
"Geoff Lane" <geoff@.nospam.gjctech.co.uk> wrote in message
news:Xns97EA7C83661FBgjctcswxnsrt@.207.46.248.16...
> SQL Server 2000 on Windows 2003 SP1 and IIS6
> I'm getting the following error from my ASP page:
> Microsoft OLE DB Provider for SQL Server error '80040e14'
> Formal parameter '@.mname' was defined as OUTPUT but the actual parameter
> not declared OUTPUT.
> However, the formal parameter '@.mname' was used in a previous stored
> procedure accessed by the same command object and that parameter has been
> been deleted prior to the call.
> Here's the DDL for the procedure:
> CREATE PROCEDURE InsertAndIdentifyNewStarter
> @.peopleID INTEGER,
> @.newstarterID BIGINT OUTPUT
> AS
> INSERT INTO newstarter (peopleID)
> VALUES (@.peopleID)
> SELECT @.newstarterID = SCOPE_IDENTITY()
> GO
> And the code snippet that's giving the problem:
> Dim cmd, ln
> Set cmd = Server.CreateObject("ADODB.Command")
> With cmd
> .ActiveConnection = "Provider=" & DBPROVIDER & ";" & CONNECTIONSTRING
> .CommandType = adCmdStoredProc
> .CommandText = "InsertAndIdentifyPerson"
> .Parameters.Append .CreateParameter("@.fname", adVarChar, _
> adParamInput, 100, firstname)
> .Parameters.Append .CreateParameter("@.mname", adVarChar, _
> adParamInput, 100, middlename)
> .Parameters.Append .CreateParameter("@.sname", adVarChar, _
> adParamInput, 100, lastname)
> .Parameters.Append .CreateParameter("@.fullname", adVarChar, _
> adParamInput, 200, firstname & " " & lastname)
> .Parameters.Append .CreateParameter("@.datestarted", adDBDate, _
> adParamInput, 10, DBStartDate)
> .Parameters.Append .CreateParameter("@.enabled", adInteger, _
> adParamInput, 10, 1)
> .Parameters.Append .CreateParameter("@.existsinaccounts", _
> adInteger, adParamInput, 10, 0)
> .Parameters.Append .CreateParameter("@.loggedon", adInteger, _
> adParamInput, 10, 0)
> .Parameters.Append .CreateParameter("@.itstatus", adInteger, _
> adParamInput, 10, 5) ' 5 = New Starter
> .Parameters.Append .CreateParameter("@.pID", adInteger, _
> adParamOutput, 10)
> .Execute ln, , adExecuteNoRecords
> pID = .Parameters("@.pID")
> ' Delete the parameters so that we can reuse the command object
> While .Parameters.Count > 0
> .Parameters.Delete 0
> Wend
> ' Create a newstarter record for this person
> .CommandText = "InsertAndIdentifyPerson"
> .Parameters.Append .CreateParameter("@.peopleID", adBigInt, _
> adParamInput, 10, pID)
> .Parameters.Append .CreateParameter("@.newstarterID", _
> adBigInt, adParamOutput, 10)
> .Execute ln, , adExecuteNoRecords
> ' ** The preceding line produces the error **
> newstarterID = .Parameters("@.newstarterID")
> End With
> Set cmd = Nothing
> Any ideas what I'm doing wrong or what the problem is?
> TIA,
> --
> Geoff|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in
news:elcHsWflGHA.5044@.TK2MSFTNGP02.phx.gbl:

> It looks to me like you are executing the wrong proc.
D'Oh! That'll teach me to cut-and-paste code :(
Thanks - you are correct!
Geoff

ASP - Stored Procedure best practice?

Hello. I was wondering if anyone would recommend or point me to a best
practice method in programming a search field on an .asp page that calls a
SQL Server stored procedure to do 'LIKE' searches? The only method that
seems to accommodate this that comes to my mind as of now is using dynamic
sql in the stored procedure but hear that it's not good practice using
dynamic sql.
Thanks in advance.
JYou can pass the comparison string in as a parameter in a parameterized
query. If you want a "wild-card search", just append '%' to the end of the
string before you pass it in. Also it's a good idea to *not* pre-pend '%'
to the front of the search string.
"J" <IDontLikeSpam@.Nowhere.com> wrote in message
news:%23otfgI$OHHA.4484@.TK2MSFTNGP02.phx.gbl...
> Hello. I was wondering if anyone would recommend or point me to a best
> practice method in programming a search field on an .asp page that calls a
> SQL Server stored procedure to do 'LIKE' searches? The only method that
> seems to accommodate this that comes to my mind as of now is using dynamic
> sql in the stored procedure but hear that it's not good practice using
> dynamic sql.
> Thanks in advance.
> J
>|||Hi Mike.
Do you know of a recommended method in doing this for a form with multiple
fields which allows a user to specify more criteria on multiple fields?
Seems like building the dynamic sql statement in the stored procedure with
the specified field parameters is the only method I can think of to do
multiple criteria searching but I guess I still would like to avoid.
Thanks for your info and reply. Much appreciated.
J
"Mike C#" <xyz@.xyz.com> wrote in message
news:ee266d$OHHA.1248@.TK2MSFTNGP02.phx.gbl...
> You can pass the comparison string in as a parameter in a parameterized
> query. If you want a "wild-card search", just append '%' to the end of
> the string before you pass it in. Also it's a good idea to *not* pre-pend
> '%' to the front of the search string.
> "J" <IDontLikeSpam@.Nowhere.com> wrote in message
> news:%23otfgI$OHHA.4484@.TK2MSFTNGP02.phx.gbl...
>|||J (IDontLikeSpam@.Nowhere.com) writes:
> Do you know of a recommended method in doing this for a form with multiple
> fields which allows a user to specify more criteria on multiple fields?
> Seems like building the dynamic sql statement in the stored procedure with
> the specified field parameters is the only method I can think of to do
> multiple criteria searching but I guess I still would like to avoid.
I discuss a whole bunch of solutions in both dynamic and static SQL, as
well as some very interesting hybrids in an article on my web site:
http://www.sommarskog.se/dyn-search.html.
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|||Gonna take a look at this. Thanks Mike. Have a good weekend.
J
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns98BDEE90C4CB3Yazorman@.127.0.0.1...
>J (IDontLikeSpam@.Nowhere.com) writes:
> I discuss a whole bunch of solutions in both dynamic and static SQL, as
> well as some very interesting hybrids in an article on my web site:
> http://www.sommarskog.se/dyn-search.html.
> --
> 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|||Gonna take a look at this. Thanks Mike. Have a good weekend.
J
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns98BDEE90C4CB3Yazorman@.127.0.0.1...
>J (IDontLikeSpam@.Nowhere.com) writes:
> I discuss a whole bunch of solutions in both dynamic and static SQL, as
> well as some very interesting hybrids in an article on my web site:
> http://www.sommarskog.se/dyn-search.html.
> --
> 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|||Sorry Erland. Didn't notice it was from someone else. Much thanks to you
also.
Take care.
J
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns98BDEE90C4CB3Yazorman@.127.0.0.1...
>J (IDontLikeSpam@.Nowhere.com) writes:
> I discuss a whole bunch of solutions in both dynamic and static SQL, as
> well as some very interesting hybrids in an article on my web site:
> http://www.sommarskog.se/dyn-search.html.
> --
> 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

Asking for User input mid-execution

Is it possible to ask for user input in the middle of a stored procedure execution?

What I have is a single procedure that is going to do some deletes, and I want to receive confirmation from the user that they're ok with actually going through with the delete.

The procedure is being run from a Query Analyzer-type interface, and I have no access to tie proper code into the application this is being run from (hence why I'm trying to add some user-based conditional logic).

Thanks in advance for any assistance!1

No.

ascii values

hi guys,

i want to know hoe to get ascii values in a astored procedure.

the senario is i am reading a character and checking whether it is a special character or not.

if it is a special character the database does the further operations.

i have written the program in C#, but not able to use it in stored procedure

i do not know how to read the "ascii value" in Transact sql.

coding is all what i do, coding is all what i want to do.

Use the ascii function:

declare @.char char(1)

set @.char = 'A'

select ascii(@.char)

set @.char = ''

select ascii(@.char)

Returns:

--
65

--
233

ASC/DESC as SP Keywords?

Can I do something like this:
CASE WHEN @.orderBy = 'ASC' THEN ASC ELSE DESC END
So I can order by asc or desc depending on a stored procedure parameter?"Chris Ashley" <chris.ashley2@.gmail.com> wrote in message
news:1137076942.622670.97210@.g44g2000cwa.googlegroups.com...
> Can I do something like this:
> CASE WHEN @.orderBy = 'ASC' THEN ASC ELSE DESC END
> So I can order by asc or desc depending on a stored procedure parameter?
Unless someone can see something wrong with this:
declare @.orderBy char(3)
set @.orderBy = 'asc'
select .. from table order by
case @.orderBy when 'asc' then colName end asc,
case @.orderBy when 'des' then colName end desc|||"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:O5vTIi4FGHA.3976@.TK2MSFTNGP11.phx.gbl...
> "Chris Ashley" <chris.ashley2@.gmail.com> wrote in message
> news:1137076942.622670.97210@.g44g2000cwa.googlegroups.com...
> Unless someone can see something wrong with this:
> declare @.orderBy char(3)
> set @.orderBy = 'asc'
> select .. from table order by
> case @.orderBy when 'asc' then colName end asc,
> case @.orderBy when 'des' then colName end desc
This article explains it a lot better:
http://www.aspfaq.com/show.asp?id=2501

AS400 stored procedure

Has anyone been able to call an AS400 stored procedure from Reporting
Services?On Apr 17, 11:15 pm, "John Doe" <j...@.msn.com> wrote:
> Has anyone been able to call an AS400 stored procedure from Reporting
> Services?
Does AS400 support ODBC? If so, you could create an ODBC connection
via a datasource and access it that way.
Regards,
Enrique Martinez
Sr. Software Consultant|||Of course the AS400 supports ODBC. (more to the point, DB2 does)
It also supports OLE-DB, and there are some interesting differences between
the drivers. The "base" driver set will be IBM's Client Access.
One answer for Reporting Services in particular *might* be web service calls
into the AS400. However, I've always done it using linked servers, and I
link twice (once with each driver), using whichever one appears to be better
for the task.
However there are definitely some problems with calling an RPG stored proc
using the drivers -- see
http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=65&threadid=47240&enterthread=y
The solution appears to be as follows (quoting from that thread):
>>on iSeries, always worked for me to have RPG program execute the SP, have
>>MSSQL statement execute the RPG pgm with a 'CALL rpgPgmName' always used
>>client access oledb driver
Note: he doesn't execute the stored proc directly *AND* he uses OLE-DB,
*NOT* ODBC. The ODBC driver -- even if patched -- tends to be less
up-to-date and capable. OTOH the ODBC sometimes does a better job of
"transparent" translation between charsets <shrug>. That is why I said
"interesting".
Note also that other people make drivers besides IBM and some of them (while
expensive) may be better able to do both these things.
I would expect the .NET-specific driver to be even more up-to-date, and
possibly better behaved. To find out about accessing AS400 data from
different environments, see:
http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/sqlp/rbafydynamicsqlclient.htm
HTH,
>L<
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1176899717.222877.18910@.n59g2000hsh.googlegroups.com...
> On Apr 17, 11:15 pm, "John Doe" <j...@.msn.com> wrote:
>> Has anyone been able to call an AS400 stored procedure from Reporting
>> Services?
>
> Does AS400 support ODBC? If so, you could create an ODBC connection
> via a datasource and access it that way.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||This is how I called the AS400 stored procedure in the Query string :
CALL GSSSQLLIB.SPTEST2 ('01', 'SUBCAT', '1070401', '1070410')
I get the following error:
An error occurred while retrieving the parameters in the query.
SQL0104: Token 01 was not valid. Valid tokens: FOR WITH FETCH ORDER UNION
EXCEPT OPTIMIZE.....
"Lisa Slater Nicholls" <lisa@.spacefold.com> wrote in message
news:%23UDEWjcgHHA.3412@.TK2MSFTNGP02.phx.gbl...
> Of course the AS400 supports ODBC. (more to the point, DB2 does)
> It also supports OLE-DB, and there are some interesting differences
> between the drivers. The "base" driver set will be IBM's Client Access.
> One answer for Reporting Services in particular *might* be web service
> calls into the AS400. However, I've always done it using linked servers,
> and I link twice (once with each driver), using whichever one appears to
> be better for the task.
> However there are definitely some problems with calling an RPG stored proc
> using the drivers -- see
> http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=65&threadid=47240&enterthread=y
> The solution appears to be as follows (quoting from that thread):
>>on iSeries, always worked for me to have RPG program execute the SP, have
>>MSSQL statement execute the RPG pgm with a 'CALL rpgPgmName' always used
>>client access oledb driver
> Note: he doesn't execute the stored proc directly *AND* he uses OLE-DB,
> *NOT* ODBC. The ODBC driver -- even if patched -- tends to be less
> up-to-date and capable. OTOH the ODBC sometimes does a better job of
> "transparent" translation between charsets <shrug>. That is why I said
> "interesting".
> Note also that other people make drivers besides IBM and some of them
> (while expensive) may be better able to do both these things.
> I would expect the .NET-specific driver to be even more up-to-date, and
> possibly better behaved. To find out about accessing AS400 data from
> different environments, see:
> http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/sqlp/rbafydynamicsqlclient.htm
>
> HTH,
>
>>L<
>
> "EMartinez" <emartinez.pr1@.gmail.com> wrote in message
> news:1176899717.222877.18910@.n59g2000hsh.googlegroups.com...
>> On Apr 17, 11:15 pm, "John Doe" <j...@.msn.com> wrote:
>> Has anyone been able to call an AS400 stored procedure from Reporting
>> Services?
>>
>> Does AS400 support ODBC? If so, you could create an ODBC connection
>> via a datasource and access it that way.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
>|||I finally figured it:
1) Use ODBC Connection string:
DSN=iSeries;database=S100E37D;DBQ=GSSSQLLIB;SYSTEM=10.0.0.7. Couldn't
figure how to work with OLEDB.
2) Use Command type "Text" and not "Stored Procedure" with the following
Query string: CALL GSSSQLLIB.SPTEST2('01', 'SUBCAT', '1070401', '1070407')
"John Doe" <jdoe@.msn.com> wrote in message
news:OZ$89AXgHHA.4916@.TK2MSFTNGP06.phx.gbl...
> Has anyone been able to call an AS400 stored procedure from Reporting
> Services?
>|||Yup, when you do a CALL it's not a stored proc any more. That makes sense.
And, like I said, you pays your money and you takes your choice between the
OLEDB and ODBC drivers! I can never tell which one is going to be the better
"buy" for a given situation until I try both <g>.
Unless you're saying that you couldn't get the OLEDB connection to work at
all? In which case, I can tell you how I do it, if that will help. I have
to look at the settings every time, I can never remember them <g>.
First I should say that I do everything with linked servers, so I'm coming
across from TSQL code, not .NET code. I wrap the procedure I want in TSQL
(in 2005 you can do this with either a view or a sproc, if I remember
correctly in 2000 I could only use linked servers in sprocs but I might be
wrong).
The performance is often far better if you do it this way. It's just a
little more work to use OPENQUERY() -- you have to double the argument
delimiters, etc -- but along with better performance I think it's a bit more
maintainable.
>L<
"John Doe" <jdoe@.msn.com> wrote in message
news:eUm%23bqegHHA.3852@.TK2MSFTNGP04.phx.gbl...
>I finally figured it:
> 1) Use ODBC Connection string:
> DSN=iSeries;database=S100E37D;DBQ=GSSSQLLIB;SYSTEM=10.0.0.7. Couldn't
> figure how to work with OLEDB.
> 2) Use Command type "Text" and not "Stored Procedure" with the following
> Query string: CALL GSSSQLLIB.SPTEST2('01', 'SUBCAT', '1070401',
> '1070407')
>
> "John Doe" <jdoe@.msn.com> wrote in message
> news:OZ$89AXgHHA.4916@.TK2MSFTNGP06.phx.gbl...
>> Has anyone been able to call an AS400 stored procedure from Reporting
>> Services?
>|||Just a heads up for lurkers about linked databases. The four part naming
under SQL Server 2000 is very dangerous and pretty much useless. It takes
almost nothing for it to decide to bring the whole table local for
processing. Openquery works well and as it should. Four part naming has
improved substantially in SQL 2005, still be careful though. Looking at the
query plan will tell you what percentage is taking place remotely.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lisa Slater Nicholls" <lisa@.spacefold.com> wrote in message
news:eZcVsw1gHHA.4992@.TK2MSFTNGP06.phx.gbl...
> Yup, when you do a CALL it's not a stored proc any more. That makes
> sense.
> And, like I said, you pays your money and you takes your choice between
> the OLEDB and ODBC drivers! I can never tell which one is going to be the
> better "buy" for a given situation until I try both <g>.
> Unless you're saying that you couldn't get the OLEDB connection to work
> at all? In which case, I can tell you how I do it, if that will help. I
> have to look at the settings every time, I can never remember them <g>.
> First I should say that I do everything with linked servers, so I'm coming
> across from TSQL code, not .NET code. I wrap the procedure I want in TSQL
> (in 2005 you can do this with either a view or a sproc, if I remember
> correctly in 2000 I could only use linked servers in sprocs but I might be
> wrong).
> The performance is often far better if you do it this way. It's just a
> little more work to use OPENQUERY() -- you have to double the argument
> delimiters, etc -- but along with better performance I think it's a bit
> more maintainable.
>>L<
> "John Doe" <jdoe@.msn.com> wrote in message
> news:eUm%23bqegHHA.3852@.TK2MSFTNGP04.phx.gbl...
>>I finally figured it:
>> 1) Use ODBC Connection string:
>> DSN=iSeries;database=S100E37D;DBQ=GSSSQLLIB;SYSTEM=10.0.0.7. Couldn't
>> figure how to work with OLEDB.
>> 2) Use Command type "Text" and not "Stored Procedure" with the following
>> Query string: CALL GSSSQLLIB.SPTEST2('01', 'SUBCAT', '1070401',
>> '1070407')
>>
>> "John Doe" <jdoe@.msn.com> wrote in message
>> news:OZ$89AXgHHA.4916@.TK2MSFTNGP06.phx.gbl...
>> Has anyone been able to call an AS400 stored procedure from Reporting
>> Services?
>>
>|||Thanks for the tip! I have almost never used 4-part naming, and I can't
remember if that was because of performance testing or because it just
didn't buy me anything.
(FWIW I don't use OPENDATASOURCE either, except for to solve one specific
problem involving linking directly to an Excel spreadsheet -- the current
loc of the spreadsheet is stored in a table, etc.)
I don't use aliases much (yet) in 2005, either. Any feelings either way on
perf there?
>L<
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:epB7e11gHHA.3852@.TK2MSFTNGP04.phx.gbl...
> Just a heads up for lurkers about linked databases. The four part naming
> under SQL Server 2000 is very dangerous and pretty much useless. It takes
> almost nothing for it to decide to bring the whole table local for
> processing. Openquery works well and as it should. Four part naming has
> improved substantially in SQL 2005, still be careful though. Looking at
> the query plan will tell you what percentage is taking place remotely.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Lisa Slater Nicholls" <lisa@.spacefold.com> wrote in message
> news:eZcVsw1gHHA.4992@.TK2MSFTNGP06.phx.gbl...
>> Yup, when you do a CALL it's not a stored proc any more. That makes
>> sense.
>> And, like I said, you pays your money and you takes your choice between
>> the OLEDB and ODBC drivers! I can never tell which one is going to be the
>> better "buy" for a given situation until I try both <g>.
>> Unless you're saying that you couldn't get the OLEDB connection to work
>> at all? In which case, I can tell you how I do it, if that will help. I
>> have to look at the settings every time, I can never remember them <g>.
>> First I should say that I do everything with linked servers, so I'm
>> coming across from TSQL code, not .NET code. I wrap the procedure I want
>> in TSQL (in 2005 you can do this with either a view or a sproc, if I
>> remember correctly in 2000 I could only use linked servers in sprocs but
>> I might be wrong).
>> The performance is often far better if you do it this way. It's just a
>> little more work to use OPENQUERY() -- you have to double the argument
>> delimiters, etc -- but along with better performance I think it's a bit
>> more maintainable.
>>L<
>> "John Doe" <jdoe@.msn.com> wrote in message
>> news:eUm%23bqegHHA.3852@.TK2MSFTNGP04.phx.gbl...
>>I finally figured it:
>> 1) Use ODBC Connection string:
>> DSN=iSeries;database=S100E37D;DBQ=GSSSQLLIB;SYSTEM=10.0.0.7. Couldn't
>> figure how to work with OLEDB.
>> 2) Use Command type "Text" and not "Stored Procedure" with the following
>> Query string: CALL GSSSQLLIB.SPTEST2('01', 'SUBCAT', '1070401',
>> '1070407')
>>
>> "John Doe" <jdoe@.msn.com> wrote in message
>> news:OZ$89AXgHHA.4916@.TK2MSFTNGP06.phx.gbl...
>> Has anyone been able to call an AS400 stored procedure from Reporting
>> Services?
>>
>>
>|||I haven't used aliases either.
If you are in SQL 2005 you might want to take a look at 4 part naming. It is
soooo much easier than handling all those quotes. It is actually useable in
SQL 2005 (as I stated earlier, stay away from 4 part with SQL 2000).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lisa Slater Nicholls" <lisa@.spacefold.com> wrote in message
news:uABKI%231gHHA.1312@.TK2MSFTNGP03.phx.gbl...
> Thanks for the tip! I have almost never used 4-part naming, and I can't
> remember if that was because of performance testing or because it just
> didn't buy me anything.
> (FWIW I don't use OPENDATASOURCE either, except for to solve one specific
> problem involving linking directly to an Excel spreadsheet -- the current
> loc of the spreadsheet is stored in a table, etc.)
> I don't use aliases much (yet) in 2005, either. Any feelings either way
> on perf there?
>>L<
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:epB7e11gHHA.3852@.TK2MSFTNGP04.phx.gbl...
>> Just a heads up for lurkers about linked databases. The four part naming
>> under SQL Server 2000 is very dangerous and pretty much useless. It takes
>> almost nothing for it to decide to bring the whole table local for
>> processing. Openquery works well and as it should. Four part naming has
>> improved substantially in SQL 2005, still be careful though. Looking at
>> the query plan will tell you what percentage is taking place remotely.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Lisa Slater Nicholls" <lisa@.spacefold.com> wrote in message
>> news:eZcVsw1gHHA.4992@.TK2MSFTNGP06.phx.gbl...
>> Yup, when you do a CALL it's not a stored proc any more. That makes
>> sense.
>> And, like I said, you pays your money and you takes your choice between
>> the OLEDB and ODBC drivers! I can never tell which one is going to be
>> the better "buy" for a given situation until I try both <g>.
>> Unless you're saying that you couldn't get the OLEDB connection to work
>> at all? In which case, I can tell you how I do it, if that will help.
>> I have to look at the settings every time, I can never remember them
>> <g>.
>> First I should say that I do everything with linked servers, so I'm
>> coming across from TSQL code, not .NET code. I wrap the procedure I
>> want in TSQL (in 2005 you can do this with either a view or a sproc, if
>> I remember correctly in 2000 I could only use linked servers in sprocs
>> but I might be wrong).
>> The performance is often far better if you do it this way. It's just a
>> little more work to use OPENQUERY() -- you have to double the argument
>> delimiters, etc -- but along with better performance I think it's a bit
>> more maintainable.
>>L<
>> "John Doe" <jdoe@.msn.com> wrote in message
>> news:eUm%23bqegHHA.3852@.TK2MSFTNGP04.phx.gbl...
>>I finally figured it:
>> 1) Use ODBC Connection string:
>> DSN=iSeries;database=S100E37D;DBQ=GSSSQLLIB;SYSTEM=10.0.0.7. Couldn't
>> figure how to work with OLEDB.
>> 2) Use Command type "Text" and not "Stored Procedure" with the
>> following Query string: CALL GSSSQLLIB.SPTEST2('01', 'SUBCAT',
>> '1070401', '1070407')
>>
>> "John Doe" <jdoe@.msn.com> wrote in message
>> news:OZ$89AXgHHA.4916@.TK2MSFTNGP06.phx.gbl...
>> Has anyone been able to call an AS400 stored procedure from Reporting
>> Services?
>>
>>
>>
>|||>>is
> soooo much easier than handling all those quotes.
Yeah, but. As I said, anybody do any perf comparisons?
As far as how hard it is to handle the quotes... I'd still have to do it
with aliases, just one layer less. Because I have to decide at runtime
which alias (or linked server) to use, and build an "outer" statement from
there...
>L<
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23GpRMK2gHHA.4064@.TK2MSFTNGP02.phx.gbl...
>I haven't used aliases either.
> If you are in SQL 2005 you might want to take a look at 4 part naming. It
> is soooo much easier than handling all those quotes. It is actually
> useable in SQL 2005 (as I stated earlier, stay away from 4 part with SQL
> 2000).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Lisa Slater Nicholls" <lisa@.spacefold.com> wrote in message
> news:uABKI%231gHHA.1312@.TK2MSFTNGP03.phx.gbl...
>> Thanks for the tip! I have almost never used 4-part naming, and I can't
>> remember if that was because of performance testing or because it just
>> didn't buy me anything.
>> (FWIW I don't use OPENDATASOURCE either, except for to solve one specific
>> problem involving linking directly to an Excel spreadsheet -- the current
>> loc of the spreadsheet is stored in a table, etc.)
>> I don't use aliases much (yet) in 2005, either. Any feelings either way
>> on perf there?
>>L<
>>
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:epB7e11gHHA.3852@.TK2MSFTNGP04.phx.gbl...
>> Just a heads up for lurkers about linked databases. The four part naming
>> under SQL Server 2000 is very dangerous and pretty much useless. It
>> takes almost nothing for it to decide to bring the whole table local for
>> processing. Openquery works well and as it should. Four part naming has
>> improved substantially in SQL 2005, still be careful though. Looking at
>> the query plan will tell you what percentage is taking place remotely.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Lisa Slater Nicholls" <lisa@.spacefold.com> wrote in message
>> news:eZcVsw1gHHA.4992@.TK2MSFTNGP06.phx.gbl...
>> Yup, when you do a CALL it's not a stored proc any more. That makes
>> sense.
>> And, like I said, you pays your money and you takes your choice between
>> the OLEDB and ODBC drivers! I can never tell which one is going to be
>> the better "buy" for a given situation until I try both <g>.
>> Unless you're saying that you couldn't get the OLEDB connection to
>> work at all? In which case, I can tell you how I do it, if that will
>> help. I have to look at the settings every time, I can never remember
>> them <g>.
>> First I should say that I do everything with linked servers, so I'm
>> coming across from TSQL code, not .NET code. I wrap the procedure I
>> want in TSQL (in 2005 you can do this with either a view or a sproc, if
>> I remember correctly in 2000 I could only use linked servers in sprocs
>> but I might be wrong).
>> The performance is often far better if you do it this way. It's just a
>> little more work to use OPENQUERY() -- you have to double the argument
>> delimiters, etc -- but along with better performance I think it's a bit
>> more maintainable.
>>L<
>> "John Doe" <jdoe@.msn.com> wrote in message
>> news:eUm%23bqegHHA.3852@.TK2MSFTNGP04.phx.gbl...
>>I finally figured it:
>> 1) Use ODBC Connection string:
>> DSN=iSeries;database=S100E37D;DBQ=GSSSQLLIB;SYSTEM=10.0.0.7. Couldn't
>> figure how to work with OLEDB.
>> 2) Use Command type "Text" and not "Stored Procedure" with the
>> following Query string: CALL GSSSQLLIB.SPTEST2('01', 'SUBCAT',
>> '1070401', '1070407')
>>
>> "John Doe" <jdoe@.msn.com> wrote in message
>> news:OZ$89AXgHHA.4916@.TK2MSFTNGP06.phx.gbl...
>> Has anyone been able to call an AS400 stored procedure from Reporting
>> Services?
>>
>>
>>
>

Arrays in a Stored Procedure

Can any one help me with a sample code, which can take an array of

elements as one of it's parameters and get the value inserted into a table in a

stored procedure.

Thanks in advance

vnswathi.

SQL dosen't support array, so how do you want to passing an 'array' of elements as parameters to a stored procedure? Maybe you can use a string that delimits the elements of an array by some character(s) and then split the string into a set of values that can be inserted into a table. If so, the key point is how to split the string with specific delimitor, and you can take a look at this post:

http://forums.asp.net/thread/1300724.aspx

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 Parameter

Is there any way to make a CLR stored procedure that accepts an array style set of data? I want to make a stored procedure that accepts to parameters of type int, and then one more that is an array of name/value pairs. Is it possible to do something like this?

Yes, there is. In fact there is a sample at http://www.codeplex.com/MSFTEngProdSamples called "Array Parameter" which you can browse or download along with the other engine database samples. This sample contains code for passing an array to a CLR stored procedure using a CLR UDT. But be careful in SQL Server 2005 as you are limited in size to 8000 bytes. A future version of SQL Server is expected to relax that constraint. The other option which doesn't have that problem is to encode your data in XML and pass it using an XML parameter and rehydrate the objects in the CLR stored procedure.

array in store procedure

How to send an array list as an input variable into store procedure?
I have a list of UserName witch I would like to store into table through
store procedure.
HrckoYou can passed it as a string and get the parts out of the string in the SP.
There is no array or such type in TSQL. (AFAIK)
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"Hrvoje Voda" <hrvoje.voda@.luatech.com> schrieb im Newsbeitrag
news:d4011j$9lf$1@.ss405.t-com.hr...
> How to send an array list as an input variable into store procedure?
> I have a list of UserName witch I would like to store into table through
> store procedure.
> Hrcko
>|||http://www.sommarskog.se/arrays-in-sql.html
Jacco Schalkwijk
SQL Server MVP
"Hrvoje Voda" <hrvoje.voda@.luatech.com> wrote in message
news:d4011j$9lf$1@.ss405.t-com.hr...
> How to send an array list as an input variable into store procedure?
> I have a list of UserName witch I would like to store into table through
> store procedure.
> Hrcko
>|||Hrcko,
Here is an example that may help. ListTable() is an efficient
table-valued function that can take your list of UserName values
and return a table, so you can do something like
insert into X(UserName)
select Item from ListTable('Hrvoje, Kresimir, Josipa, Barbara, Vladen,
Vilko, Ksenija')
-- Definition and notes for ListTable()
/*
A table-valued function with one parameter, a delimited list,
that returns the separate distinct items of the list.
Steve Kass, Drew University
Thanks to MVPs Linda Wierzbicki and Umachandar Jayachandran
for help and helpful discussions on this.
*/
--A table of integers is needed
create table Seq (
Nbr int not null
)
insert into Seq
select top 4001 0
from Northwind..[Order Details]
cross join (select 1 as n union all select 2) X
declare @.i int
set @.i = -1
update Seq
set @.i = Nbr = @.i + 1
alter table Seq add constraint pk_Seq primary key (Nbr)
--table Seq created
go
--This makes things more readable. The list is easier
--to process if it begins and ends with a single comma
--As it turns out also, list items cannot
--have leading or trailing spaces (here any leading spaces
--in the first item or trailing spaces in the last are
--eliminated)
create function RegularizedList (@.List varchar(8000))
returns varchar(8000) as begin
return replace(rtrim(','+ltrim(@.List))+',', ',,', ',')
end
go
--This function returns a table containing one column, commaPos,
--of integers, the positions of each comma in @.List, except the last
--This function returns a table containing the items in the list.
--The items are extracted by selecting those substrings of
--the list that begin immediately after a comma and end
--immediately before the next comma, then trimming spaces on
--both sides.
create function ListTable (@.List varchar(8000))
returns table as return
select
ltrim(rtrim(
substring(regL,
commaPos+1,
charindex(',', regL, commaPos+1) - (commaPos+1))))
as Item
from (
select Nbr as commaPos
from Seq, (
select dbo.RegularizedList(@.List) as regL
) R
where substring(regL,Nbr,1) = ','
and Nbr < len(regL)
) L, (
select dbo.RegularizedList(@.List) as regL
) R
go
--examples
declare @.x varchar(1000)
set @.x = 'Hrvoje, Kresimir, Josipa, Barbara, Vladen, Vilko, Ksenija'
select * from ListTable(@.x)
set @.x = 'Hrvoje|Kresimir|Josipa|Barbara|Vladen|V
ilko|Ksenija'
declare @.s varchar(1000)
set @.s = replace(@.x,'|',',')
select * from ListTable(@.s)
--Note, if a list contains a non-comma delimiter, and contains no
--commas within items, this replacement allows the function to
--handle it. If a comma appears in an item, but some other non-
--delimiter is absent from the list, a three-step replacement can
--be made:
-- replace all commas with new character not in list
-- replace all delimiters with comma
-- Use (select replace(Item,<new>,<comma> ) from ListTable(@.List)) LT
-- where the list table is used.
go
--Since this is a repro script, delete everything!
--Keep them around if they are helpful, though.
DROP FUNCTION RegularizedList
DROP TABLE Seq
DROP FUNCTION ListTable
-- Steve Kass
-- Drew University
Hrvoje Voda wrote:

>How to send an array list as an input variable into store procedure?
>I have a list of UserName witch I would like to store into table through
>store procedure.
>Hrcko
>
>|||You can pass the list as a comma separated list of values in a string, and
use a split technique to break them apart using a function similar to this
one:
-- Populate an auxiliary table of numbers
SET NOCOUNT ON
USE tempdb
GO
IF OBJECT_ID('Nums') IS NOT NULL
DROP TABLE Nums
GO
CREATE TABLE Nums(n INT NOT NULL PRIMARY KEY)
DECLARE @.max AS INT, @.rc AS INT
SET @.max = 8000
SET @.rc = 1
BEGIN TRAN
INSERT INTO Nums VALUES(1)
WHILE @.rc * 2 <= @.max
BEGIN
INSERT INTO Nums
SELECT n + @.rc FROM Nums
SET @.rc = @.rc * 2
END
INSERT INTO Nums
SELECT n + @.rc FROM Nums
WHERE n + @.rc <= @.max
COMMIT TRAN
GO
-- Create function
CREATE FUNCTION fn_SeparateElements
(@.arr AS VARCHAR(7999)) RETURNS TABLE
AS
RETURN
SELECT n - LEN(REPLACE(LEFT(arr, n), ',', '')) + 1 AS pos,
SUBSTRING(@.arr, n, CHARINDEX(',', @.arr + ',', n) - n) AS element
FROM (SELECT @.arr AS arr) AS A JOIN Nums
ON n <= LEN(@.arr) AND SUBSTRING(',' + @.arr, n, 1) = ','
GO
-- Test
SELECT * FROM fn_SeparateElements('user1,user2,user3')
pos element
-- --
1 user1
2 user2
3 user3
BG, SQL Server MVP
www.SolidQualityLearning.com
"Hrvoje Voda" <hrvoje.voda@.luatech.com> wrote in message
news:d4011j$9lf$1@.ss405.t-com.hr...
> How to send an array list as an input variable into store procedure?
> I have a list of UserName witch I would like to store into table through
> store procedure.
> Hrcko
>|||
"Hrvoje Voda" wrote:

> How to send an array list as an input variable into store procedure?
>
Definitely more than one way here to skin that cat. :-)
Yet another solution not using a UDF utilizing SQL Server's own
master..spt_values. You can easily modify it to use your own numbers table.
DECLARE @.strComma VARCHAR(1000)
SET @.strComma = 'Hrvoje, Kresimir, Josipa, Barbara, Vladen, Vilko, Ksenija'
SET @.strComma = REPLACE(@.strComma,' ', '')
SELECT
CAST(RIGHT(LEFT(@.strComma,Number-1)
, CHARINDEX(',',REVERSE(LEFT(','+@.strComma
,Number-1)))) AS CHAR(30)) as
User__Name
FROM
master..spt_values
WHERE
Type = 'P' AND Number BETWEEN 1 AND LEN(@.strComma)+1
AND
(SUBSTRING(@.strComma,Number,1) = ',' OR SUBSTRING(@.strComma,Number,1) = '')
User__Name
--
Hrvoje
Kresimir
Josipa
Barbara
Vladen
Vilko
Ksenija
(7 row(s) affected)
--
Frank Kalis
SQL Server MVP
http://www.insidesql.de

Array in Store procedure

Dear all,

Sometimes I happened the require that the number of input parameters
of SP is not fixed, Can sql T-SQL handle the array (dynamic array)?
Does anybody ever used an array name as the input parameter to call
the SP?

thanks,

RobertSQL doesn't have arrays but see this article for some alternatives:

http://www.sommarskog.se/arrays-in-sql.html

--
David Portas
SQL Server MVP
--|||Robert Song wrote:

> Dear all,
> Sometimes I happened the require that the number of input parameters
> of SP is not fixed, Can sql T-SQL handle the array (dynamic array)?
> Does anybody ever used an array name as the input parameter to call
> the SP?
> thanks,
> Robert
I had this problem a year ago and solved it using a udf named fn_Split
I found it pretty fast.
You give it a list of parameters as '1,2,3,4,5,6' and it returns a temp
table
so you can select from fn_Split() and have something like
1
2
3
4
5
6

i found a version of this udf at
http://www.umachandar.com/technical...tyFns/Main7.htm

hth

f.|||Hi David,

Thank you so much for your reply, i will read the article later on.

Cheers,

Robert Song

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Array as procedure parameter

I have a shift definition table with the columns:

shift_id: shift's id

shift_name: shift's name

shift_number_of_day: shift's "position" on the day

initial_hour: shift's initial hour

final_hour: shift's final hour

The shift definition depends on the company: company A may have 2 shifts, and company B may have 3 shifts, for example.
I need to load a dimension table, dim_time, that should have a row for each hour of each day of a specific year. I would have

alternate_time_key ... shift_name ...
1/1/2006 01:00:00 GraveYard
1/1/2006 02:00:00 GraveYard

and so on, until it reaches the end of the year.

So in my procedure to load the dimension table, I would have something like

IF (@.alternateTimeKeyHour >= @.paramShift1InitialHour) AND (@.alternateTimeKeytHour <= @.paramShift1FinalHour)
BEGIN
SET @.shiftName = @.paramFirstShiftName;
SET @.shiftNumberOfDay = 1;
END
ELSE IF (@.alternateTimeKeyHour >= @.paramShift2InitialHour) AND (@.alternateTimeKeyHour <= @.paramShift2FinalHour)
BEGIN
SET @.shiftName = @.paramSecondShiftName;
SET @.shiftNumberOfDay = 2;
END
.
.
.

The problem is that I would have a variable number of shifts (variable number of parameters!)...
The only solution I could think was using an array, but as far as I could see it's not possible to pass
an array as a parameter to a procedure. Is this right? Is there a better solution to do this? Can anyone help me please?

Thank you!

Personally, I always use XML for this type of thing. You can then use OPENXML in the stored procedure to turn the xml into a table that you can join to.

Here is a great article about your choices for passing an array to a stored procedure.

http://www.sommarskog.se/arrays-in-sql.html

|||Thank you Ryan!

Array As parameters to procedure

Hi friends,
Please help me I want to pass an Aarry parameters from asp.net to sqlserver stored procedure. Is it possible, if yes how.

regards,
Asad Mahmood

Hi,

SQL Server doesn't seem to have any array-like parameter so what you can do is that create a CSV (comma separated values) out from your data and pass that to the procedure (which could take that as varchar(8000) etc depending on the needed length).

In the proc you could parse this CSV into say a temp table containing values as integers, if you use a function.Here is an example of such function.

|||It will be easier if you are using Arraylist but try this link for how to do it. Hope this helps.
http://www.sommarskog.se/arrays-in-sql.html

Array / Table As return Type

Hello All,

I have a scenario in which my stored procedure has to return few
variables with their value and also the collection. Now in SQL their is
no such as array, so the best is to return the table in place of.

I am execting the stored procedure by having sql command in place.
and created the various parameters(variables) those i need the values
of and secondly wondering how should i be creating the parameter as a
table returntype.

Any help on this would be a million worth useful.

I can excerpt the code if required.

Regards
Sandesh KadamSandesh (sandesh27uk@.gmail.com) writes:

Quote:

Originally Posted by

I have a scenario in which my stored procedure has to return few
variables with their value and also the collection. Now in SQL their is
no such as array, so the best is to return the table in place of.
>
I am execting the stored procedure by having sql command in place.
and created the various parameters(variables) those i need the values
of and secondly wondering how should i be creating the parameter as a
table returntype.

You cannot pass table variables as parameters.

I have an article on my web site that discusses a couple of ways to
skin the cat: http://www.sommarskog.se/share_data.html.

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

Array (or similar thing ) in sql server

I would like to write a fun or stored procedure to
do some operation.
It require me to know that what category is currently belong to certain people
(people_table: category_table
1 to Many)
However, when i use the select statement in stored proc, it return a set of result, not a scalar , therefore, i cannot use the variable to hold it. In addition, there are no array in SQL server.
Question:
1. Is there any way to hold the collection of result(like array)?
2. Also, how to determine to use fun or stored procedure?
(Since a integer is need to return by them)
Thx

When you perform a select statement you're going to get back records in a tabular format. The easiest way to do what you want is to use the select statement and in your code load that into a datatable and if you'd like put that into an array. I guess you could use an ExecuteReader function and then load it into your array... but that sound like a lot of code. If you just need to return one value then use the ExecuteScaler and assign that to your variable. Hope this helps.|||Thx!
However, I want to do this job inside the db, but not using executereader
First, the performance is better as it is operated in db, not need to take data outside
Second, the arhcit design require me to encapsulate the some logic in DB, I am sure there are some way to deal with this problem.
I have think to create a temp table or similar method. However, I am new to db programe. Can anyone give me some idea?
|||Try this link do Arrays in SQL Server. Hope this helps.
http://www.sommarskog.se/arrays-in-sql.html|||Great. Thx

Arithmetic overflow error when executing Stored Procedure

When I execute a stored procedure it outputs the expected value, but also throws the following exception.

Arithmetic overflow error converting expression to data type int.
The 'usp_TicketCreate' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.

Any help would be appreciated, at the moment I am just catching the exception in my code and ignoring it, but I would like to get rid of the exception all together.

Thanks for the help.

SBProgrammerchange the data type of that column to bigint which is presently integer..
integer data type can only stored data upto 32k and if the value exceeds this limit then the sql server's error is generated..

i hope this will solve you problem

Mukund Tambe|||Check the source code of your stored procedure. You are probably returning a variable that is NULL, in other words, something like:CREATE PROCEDURE myProcedure
AS

DECLARE @.badVariable INT -- Declare, but leave NULL

RETURN @.badVariable-PatP|||The code for the Stored Procedure is as follows:

ALTER PROCEDURE dbo.usp_TicketCreate
(
@.IsActive bit = true,
@.TicketStatusId int = 0,
@.TicketTypeId int = 0,
@.TicketTypeGroupId int = 0,
@.TicketPriorityId int = 0,
@.LocationId int = 0,
@.BuildingId int = 0,
@.RoomId int = 0,
@.RoomOther nvarchar(100) = '',
@.AssetFound bit = false,
@.AssetId int = 0,
@.SerialNumber nvarchar(50) = '',
@.InventoryTag nvarchar(20) = '',
@.AssetTagNumber nvarchar(10) = '',
@.ManufacturerId int = 0,
@.Model nvarchar(50) = '',
@.AssignedToUserId int = 0,
@.EndUserId int = 0,
@.EndUser_Name nvarchar(50) = '',
@.EndUser_Email nvarchar(50) = '',
@.EndUser_Phone nvarchar(50) = '',
@.EnteredByUserId int = 0,
@.EnteredTimeStamp datetime,
--@.ProblemDescription nvarchar(2000) = '',
@.TicketId bigint = 0 OUTPUT
)
AS

/* SET NOCOUNT ON */

EXEC @.TicketId = ufx_TicketNewId @.EnteredTimeStamp

-- Update Table TicketId with the NextId
UPDATE
TicketId
SET
NextId = NextId + 1

INSERT INTO Tickets
(
TicketId,
IsActive,
TicketStatusId,
TicketTypeId,
TicketTypeGroupId,
TicketPriorityId,
LocationId,
BuildingId,
RoomId,
RoomOther,
AssetFound,
AssetId,
SerialNumber,
InventoryTag,
AssetTagNumber,
ManufacturerId,
Model,
EndUserId,
EndUserName,
EndUserEmail,
EndUserPhone,
EnteredByUserId,
EnteredTimeStamp
--ProblemDescription
)
VALUES
(
@.TicketId,
@.IsActive,
@.TicketStatusId,
@.TicketTypeId,
@.TicketTypeGroupId,
@.TicketPriorityId,
@.LocationId,
@.BuildingId,
@.RoomId,
@.RoomOther,
@.AssetFound,
@.AssetId,
@.SerialNumber,
@.InventoryTag,
@.AssetTagNumber,
@.ManufacturerId,
@.Model,
@.EndUserId,
@.EndUser_Name,
@.EndUser_Email,
@.EndUser_Phone,
@.EnteredByUserId,
@.EnteredTimeStamp
--@.ProblemDescription,
)

RETURN @.TicketId

--------------
The Function is as follows:

ALTER FUNCTION dbo.ufx_TicketNewId
(
@.DateNow DateTime
)
RETURNS bigint
AS
BEGIN

Declare @.TicketId nvarchar(5)
Declare @.Month nvarchar(2)
Declare @.Day nvarchar(2)
Declare @.Year nvarchar(4)
Declare @.ReturnValue bigint

SELECT @.Year = DatePart(Year, @.Datenow)
SELECT @.Month = DatePart(Month, @.Datenow)
SELECT @.Day = DatePart(Day, @.Datenow)

IF LEN(@.Month) = 1
SELECT @.Month = '0' + @.Month

IF LEN(@.Day) = 1
SELECT @.Day = '0' + @.Day

SELECT @.TicketId = [NextId] FROM TicketId

SELECT @.ReturnValue = @.Year + @.Month + @.Day + @.TicketId

RETURN @.ReturnValue

END

-------------

The Output after executing the Stored Procedure is as follows:

Running [dbo].[usp_TicketCreate] ( @.IsActive = True, @.TicketStatusId = 0, @.TicketTypeId = 1, @.TicketTypeGroupId = 1, @.TicketPriorityId = 1, @.LocationId = 111, @.BuildingId = 111, @.RoomId = 111, @.RoomOther = , @.AssetFound = False, @.AssetId = 0, @.SerialNumber = , @.InventoryTag = , @.AssetTagNumber = , @.ManufacturerId = 1, @.Model = , @.AssignedToUserId = 0, @.EndUserId = 131, @.EndUser_Name = Test User, @.EndUser_Email = Test.User@.TestEmail.com, @.EndUser_Phone = x, @.EnteredByUserId = 131, @.EnteredTimeStamp = 3/14/2007, @.TicketId = 0 ).

Arithmetic overflow error converting expression to data type int.
The 'usp_TicketCreate' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.
(2 row(s) affected)
(0 row(s) returned)
@.TicketId = 20070314107
@.RETURN_VALUE = 0
Finished running [dbo].[usp_TicketCreate].

Thanks again for all your help.

SBProgrammer|||SubProgrammer,

It's like Pat Said

Oh, and you should never use RETURN with a value...SQL Server can overwrite it..use an output variable|||Brett,

Thanks for your reply. How should you return a value than, I am just doing as my manager had done before me. I would appreciate a sample of how to correctly implement this.

SBProgrammer|||Pat,

I have posted code and not sure where my variable is null, since I initiate all of the parameters. If you could help point out what I am missing, or whether or not I am doing this correctly. I would appreciate it.

Thanks so much for your time and information.

SBProgrammer|||The return value from a stored procedure must be an INT value, and 20070314107 is too big to be an INT. This will raise the overflow error, and return a NULL value to the caller.

As Brett pointed out, the @.ticketID OUTPUT variable in your procedure certainly can be used by the calling code. You just can't use the INT return value from the procedure like you can use BIGINT one from the function.

Note that playing character conversion games (like you do for the values in the function) will hurt you at some point in the future. Guaranteed, no question in my mind. It isn't a question of if, but only a question of when it will hurt you.

-PatP