I'm trying to get a return value (MyResult) from a stored procedure
that's called from within an ASP page. Both the codes in the Stored
Procedure and in the ASP page are rather simple and straightforward.
Any help available?
The error code says:
Microsoft VBScript compilation error '800a03ee'
Expected ')'
/ics/test1.asp, line 23
Set rs = ADODBCon.Execute(sSql, out MyResult)
--^
CREATE PROCEDURE usp_test @.Input int, @.Result int OUTPUT AS
set @.Result = 2 * @.Input
GO
---
<HTML>
<HEAD><TITLE>test</TITLE></HEAD>
<BODY>
<%
Set ADODBCon = Server.CreateObject("ADODB.Connection")
Dim StrConn, rs, MyInput, MyResult
MyInput = 123
ADODBCon.Provider = "SQLOLEDB"
strConn = "Data Source=MySQLServer;"
strConn = strConn & "Initial Catalog=MyDatabase;"
strConn = strConn & "User Id=JohnD;"
strConn = strConn & "Password=MyPwd;"
ADODBCon.Connectionstring = strConn
ADODBCon.open
sSql = "usp_test " & MyInput
Set rs = ADODBCon.Execute(sSql, out MyResult)
%>
</BODY>
</HTML>"ab" <absmienk@.hotmail.com> wrote in message
news:1154434778.587929.301980@.b28g2000cwb.googlegroups.com...
> I'm trying to get a return value (MyResult) from a stored procedure
> that's called from within an ASP page. Both the codes in the Stored
> Procedure and in the ASP page are rather simple and straightforward.
> Any help available?
>
TM for your R-ing pleasure:
Calling a Stored Procedure with a Command
http://windowssdk.msdn.microsoft.co...y/ms676516.aspx
David|||ab,
Use a command object instead.
How to call SQL Server stored procedures from ASP
http://support.microsoft.com/kb/q164485/
AMB
"ab" wrote:
> I'm trying to get a return value (MyResult) from a stored procedure
> that's called from within an ASP page. Both the codes in the Stored
> Procedure and in the ASP page are rather simple and straightforward.
> Any help available?
>
> The error code says:
> Microsoft VBScript compilation error '800a03ee'
> Expected ')'
> /ics/test1.asp, line 23
> Set rs = ADODBCon.Execute(sSql, out MyResult)
> --^
>
> CREATE PROCEDURE usp_test @.Input int, @.Result int OUTPUT AS
> set @.Result = 2 * @.Input
> GO
> ---
> <HTML>
> <HEAD><TITLE>test</TITLE></HEAD>
> <BODY>
> <%
> Set ADODBCon = Server.CreateObject("ADODB.Connection")
> Dim StrConn, rs, MyInput, MyResult
> MyInput = 123
> ADODBCon.Provider = "SQLOLEDB"
> strConn = "Data Source=MySQLServer;"
> strConn = strConn & "Initial Catalog=MyDatabase;"
> strConn = strConn & "User Id=JohnD;"
> strConn = strConn & "Password=MyPwd;"
> ADODBCon.Connectionstring = strConn
> ADODBCon.open
> sSql = "usp_test " & MyInput
> Set rs = ADODBCon.Execute(sSql, out MyResult)
> %>
> </BODY>
> </HTML>
>|||Thanks david, it worked.|||Alejandro, thank you. It worked.
Showing posts with label page. Show all posts
Showing posts with label page. Show all posts
Thursday, March 29, 2012
Get a return value when calling a SP from within ASP
I'm trying to get a return value (MyResult) from a stored procedure
that's called from within an ASP page. Both the codes in the Stored
Procedure and in the ASP page are rather simple and straightforward.
Any help available?
The error code says:
Microsoft VBScript compilation error '800a03ee'
Expected ')'
/ics/test1.asp, line 23
Set rs = ADODBCon.Execute(sSql, out MyResult)
--^
CREATE PROCEDURE usp_test @.Input int, @.Result int OUTPUT AS
set @.Result = 2 * @.Input
GO
---
&
test"ab" <absmienk@.hotmail.com> wrote in message
news:1154434778.587929.301980@.b28g2000cwb.googlegroups.com...
> I'm trying to get a return value (MyResult) from a stored procedure
> that's called from within an ASP page. Both the codes in the Stored
> Procedure and in the ASP page are rather simple and straightforward.
> Any help available?
>
TM for your R-ing pleasure:
Calling a Stored Procedure with a Command
http://windowssdk.msdn.microsoft.com/en-us/library/ms676516.aspx
David|||ab,
Use a command object instead.
How to call SQL Server stored procedures from ASP
http://support.microsoft.com/kb/q164485/
AMB
"ab" wrote:
> I'm trying to get a return value (MyResult) from a stored procedure
> that's called from within an ASP page. Both the codes in the Stored
> Procedure and in the ASP page are rather simple and straightforward.
> Any help available?
>
> The error code says:
> Microsoft VBScript compilation error '800a03ee'
> Expected ')'
> /ics/test1.asp, line 23
> Set rs = ADODBCon.Execute(sSql, out MyResult)
> --^
>
> CREATE PROCEDURE usp_test @.Input int, @.Result int OUTPUT AS
> set @.Result = 2 * @.Input
> GO
> ---
> &
> test
>
> Set ADODBCon = Server.CreateObject("ADODB.Connection")
> Dim StrConn, rs, MyInput, MyResult
> MyInput = 123
> ADODBCon.Provider = "SQLOLEDB"
> strConn = "Data Source=MySQLServer;"
> strConn = strConn & "Initial Catalog=MyDatabase;"
> strConn = strConn & "User Id=JohnD;"
> strConn = strConn & "Password=MyPwd;"
> ADODBCon.Connectionstring = strConn
> ADODBCon.open
> sSql = "usp_test " & MyInput
> Set rs = ADODBCon.Execute(sSql, out MyResult)
> %>
>
>
>|||Thanks david, it worked.|||Alejandro, thank you. It worked.
that's called from within an ASP page. Both the codes in the Stored
Procedure and in the ASP page are rather simple and straightforward.
Any help available?
The error code says:
Microsoft VBScript compilation error '800a03ee'
Expected ')'
/ics/test1.asp, line 23
Set rs = ADODBCon.Execute(sSql, out MyResult)
--^
CREATE PROCEDURE usp_test @.Input int, @.Result int OUTPUT AS
set @.Result = 2 * @.Input
GO
---
&
test"ab" <absmienk@.hotmail.com> wrote in message
news:1154434778.587929.301980@.b28g2000cwb.googlegroups.com...
> I'm trying to get a return value (MyResult) from a stored procedure
> that's called from within an ASP page. Both the codes in the Stored
> Procedure and in the ASP page are rather simple and straightforward.
> Any help available?
>
TM for your R-ing pleasure:
Calling a Stored Procedure with a Command
http://windowssdk.msdn.microsoft.com/en-us/library/ms676516.aspx
David|||ab,
Use a command object instead.
How to call SQL Server stored procedures from ASP
http://support.microsoft.com/kb/q164485/
AMB
"ab" wrote:
> I'm trying to get a return value (MyResult) from a stored procedure
> that's called from within an ASP page. Both the codes in the Stored
> Procedure and in the ASP page are rather simple and straightforward.
> Any help available?
>
> The error code says:
> Microsoft VBScript compilation error '800a03ee'
> Expected ')'
> /ics/test1.asp, line 23
> Set rs = ADODBCon.Execute(sSql, out MyResult)
> --^
>
> CREATE PROCEDURE usp_test @.Input int, @.Result int OUTPUT AS
> set @.Result = 2 * @.Input
> GO
> ---
> &
> test
>
> Set ADODBCon = Server.CreateObject("ADODB.Connection")
> Dim StrConn, rs, MyInput, MyResult
> MyInput = 123
> ADODBCon.Provider = "SQLOLEDB"
> strConn = "Data Source=MySQLServer;"
> strConn = strConn & "Initial Catalog=MyDatabase;"
> strConn = strConn & "User Id=JohnD;"
> strConn = strConn & "Password=MyPwd;"
> ADODBCon.Connectionstring = strConn
> ADODBCon.open
> sSql = "usp_test " & MyInput
> Set rs = ADODBCon.Execute(sSql, out MyResult)
> %>
>
>
>|||Thanks david, it worked.|||Alejandro, thank you. It worked.
Monday, March 26, 2012
Generic question about leaf-level of non-clustered indexes
In the case of a heap, I understand the leaf-level contains some sort
of RowId which points directly to the right page in the table. Good,
pretty quick.
If the table contains a clustered index, I understand this is what's
used as bookmark in all the non-clustered indexes of that table.
Question:
Couldn't this be extremely inefficient if the clustered index is not
unique?
If I created a non unique clustered index on a (small) column with low
selectivity, I might end up with thousands of rows for each value in
the clustered index.
Then a bookmark on such a value does not seem too good, does it?
I suppose there would be several pages read before reaching the actual
data page...
Thanks
EricThis is a multi-part message in MIME format.
--=_NextPart_000_00F4_01C3777E.0F0200C0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Under the covers, it builds a "uniqueifier", so that all "keys" in the =clustered index are unique, even if you created it on a non-unique =column.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Eric Mamet" <eric_mamet_test@.yahoo.co.uk> wrote in message =news:11269dcb.0309100520.6d6a4e1f@.posting.google.com...
In the case of a heap, I understand the leaf-level contains some sort
of RowId which points directly to the right page in the table. Good,
pretty quick.
If the table contains a clustered index, I understand this is what's
used as bookmark in all the non-clustered indexes of that table.
Question: Couldn't this be extremely inefficient if the clustered index is not
unique?
If I created a non unique clustered index on a (small) column with low
selectivity, I might end up with thousands of rows for each value in
the clustered index.
Then a bookmark on such a value does not seem too good, does it?
I suppose there would be several pages read before reaching the actual
data page...
Thanks
Eric
--=_NextPart_000_00F4_01C3777E.0F0200C0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Under the covers, it builds a ="uniqueifier", so that all "keys" in the clustered index are unique, even if you created =it on a non-unique column.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Eric Mamet" wrote in message news:11269d=cb.0309100520.6d6a4e1f@.posting.google.com...In the case of a heap, I understand the leaf-level contains some sortof =RowId which points directly to the right page in the table. Good,pretty quick.If the table contains a clustered index, I understand this =is what'sused as bookmark in all the non-clustered indexes of that table.Question: Couldn't this be extremely inefficient if =the clustered index is notunique?If I created a non unique clustered =index on a (small) column with lowselectivity, I might end up with =thousands of rows for each value inthe clustered index.Then a bookmark on =such a value does not seem too good, does it?I suppose there would be =several pages read before reaching the actualdata page...ThanksEric
--=_NextPart_000_00F4_01C3777E.0F0200C0--|||This is a multi-part message in MIME format.
--=_NextPart_000_0037_01C3782F.68E72280
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
To add to Tom's comments, From memory this will add four bytes to the =size of your field for the subsequent non unique values. Plus an =addition two bytes because you now have a non fixed length column. As =you can see the extra overhead can be huge on something like an INT. If =there is another column you can add to the clustered index that can make =it unique, then I would consider this as it may actually use less space. = I.e. two INTs unique may use less space that one INT with low =selectivity.
-- Barry McAuslin
Crucis Technical Solutions Limited
support@.sqlfe.com
http://www.sqlfe.com
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:exfAK%235dDHA.568@.TK2MSFTNGP11.phx.gbl...
Under the covers, it builds a "uniqueifier", so that all "keys" in the =clustered index are unique, even if you created it on a non-unique =column.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Eric Mamet" <eric_mamet_test@.yahoo.co.uk> wrote in message =news:11269dcb.0309100520.6d6a4e1f@.posting.google.com...
In the case of a heap, I understand the leaf-level contains some sort
of RowId which points directly to the right page in the table. Good,
pretty quick.
If the table contains a clustered index, I understand this is what's
used as bookmark in all the non-clustered indexes of that table.
Question: Couldn't this be extremely inefficient if the clustered index is not
unique?
If I created a non unique clustered index on a (small) column with low
selectivity, I might end up with thousands of rows for each value in
the clustered index.
Then a bookmark on such a value does not seem too good, does it?
I suppose there would be several pages read before reaching the actual
data page...
Thanks
Eric
--=_NextPart_000_0037_01C3782F.68E72280
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
To add to Tom's comments, From memory =this will add four bytes to the size of your field for the subsequent non unique =values. Plus an addition two bytes because you now have a non fixed length =column. As you can see the extra overhead can be huge on something like an =INT. If there is another column you can add to the clustered index that can =make it unique, then I would consider this as it may actually use less =space. I.e. two INTs unique may use less space that one INT with low selectivity.
-- Barry McAuslin
Crucis Technical Solutions Limited
support@.sqlfe.comhttp://www.sqlfe.com">http://www.sqlfe.com
"Tom Moreau"= wrote in message news:exfAK%235dDHA.5=68@.TK2MSFTNGP11.phx.gbl...
Under the covers, it builds a ="uniqueifier", so that all "keys" in the clustered index are unique, even if you created =it on a non-unique column.
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Eric Mamet" wrote in message news:11269d=cb.0309100520.6d6a4e1f@.posting.google.com...In the case of a heap, I understand the leaf-level contains some =sortof RowId which points directly to the right page in the table. Good,pretty quick.If the table contains a clustered index, I understand =this is what'sused as bookmark in all the non-clustered indexes of that table.Question: Couldn't this be extremely inefficient if =the clustered index is notunique?If I created a non unique =clustered index on a (small) column with lowselectivity, I might end up with =thousands of rows for each value inthe clustered index.Then a bookmark on =such a value does not seem too good, does it?I suppose there would be =several pages read before reaching the actualdata page...ThanksEric =
--=_NextPart_000_0037_01C3782F.68E72280--|||This question bothered me and it makes much more sense now.
Thanks very much to both of you.
of RowId which points directly to the right page in the table. Good,
pretty quick.
If the table contains a clustered index, I understand this is what's
used as bookmark in all the non-clustered indexes of that table.
Question:
Couldn't this be extremely inefficient if the clustered index is not
unique?
If I created a non unique clustered index on a (small) column with low
selectivity, I might end up with thousands of rows for each value in
the clustered index.
Then a bookmark on such a value does not seem too good, does it?
I suppose there would be several pages read before reaching the actual
data page...
Thanks
EricThis is a multi-part message in MIME format.
--=_NextPart_000_00F4_01C3777E.0F0200C0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Under the covers, it builds a "uniqueifier", so that all "keys" in the =clustered index are unique, even if you created it on a non-unique =column.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Eric Mamet" <eric_mamet_test@.yahoo.co.uk> wrote in message =news:11269dcb.0309100520.6d6a4e1f@.posting.google.com...
In the case of a heap, I understand the leaf-level contains some sort
of RowId which points directly to the right page in the table. Good,
pretty quick.
If the table contains a clustered index, I understand this is what's
used as bookmark in all the non-clustered indexes of that table.
Question: Couldn't this be extremely inefficient if the clustered index is not
unique?
If I created a non unique clustered index on a (small) column with low
selectivity, I might end up with thousands of rows for each value in
the clustered index.
Then a bookmark on such a value does not seem too good, does it?
I suppose there would be several pages read before reaching the actual
data page...
Thanks
Eric
--=_NextPart_000_00F4_01C3777E.0F0200C0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Under the covers, it builds a ="uniqueifier", so that all "keys" in the clustered index are unique, even if you created =it on a non-unique column.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Eric Mamet"
--=_NextPart_000_00F4_01C3777E.0F0200C0--|||This is a multi-part message in MIME format.
--=_NextPart_000_0037_01C3782F.68E72280
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
To add to Tom's comments, From memory this will add four bytes to the =size of your field for the subsequent non unique values. Plus an =addition two bytes because you now have a non fixed length column. As =you can see the extra overhead can be huge on something like an INT. If =there is another column you can add to the clustered index that can make =it unique, then I would consider this as it may actually use less space. = I.e. two INTs unique may use less space that one INT with low =selectivity.
-- Barry McAuslin
Crucis Technical Solutions Limited
support@.sqlfe.com
http://www.sqlfe.com
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:exfAK%235dDHA.568@.TK2MSFTNGP11.phx.gbl...
Under the covers, it builds a "uniqueifier", so that all "keys" in the =clustered index are unique, even if you created it on a non-unique =column.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Eric Mamet" <eric_mamet_test@.yahoo.co.uk> wrote in message =news:11269dcb.0309100520.6d6a4e1f@.posting.google.com...
In the case of a heap, I understand the leaf-level contains some sort
of RowId which points directly to the right page in the table. Good,
pretty quick.
If the table contains a clustered index, I understand this is what's
used as bookmark in all the non-clustered indexes of that table.
Question: Couldn't this be extremely inefficient if the clustered index is not
unique?
If I created a non unique clustered index on a (small) column with low
selectivity, I might end up with thousands of rows for each value in
the clustered index.
Then a bookmark on such a value does not seem too good, does it?
I suppose there would be several pages read before reaching the actual
data page...
Thanks
Eric
--=_NextPart_000_0037_01C3782F.68E72280
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
To add to Tom's comments, From memory =this will add four bytes to the size of your field for the subsequent non unique =values. Plus an addition two bytes because you now have a non fixed length =column. As you can see the extra overhead can be huge on something like an =INT. If there is another column you can add to the clustered index that can =make it unique, then I would consider this as it may actually use less =space. I.e. two INTs unique may use less space that one INT with low selectivity.
-- Barry McAuslin
Crucis Technical Solutions Limited
support@.sqlfe.comhttp://www.sqlfe.com">http://www.sqlfe.com
"Tom Moreau"
Under the covers, it builds a ="uniqueifier", so that all "keys" in the clustered index are unique, even if you created =it on a non-unique column.
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Eric Mamet"
--=_NextPart_000_0037_01C3782F.68E72280--|||This question bothered me and it makes much more sense now.
Thanks very much to both of you.
Sunday, February 19, 2012
generate a xml file from sql server 2000?
hi there.
i'm using asp.net 2 page i'm accessing a table consists of 100 thousands rows and its slow and i'm wondering instead of accessing directly to the table how about if i access via xml ?
generate xml and cache it and use the xml file rather going to sql server database?
has anybody have any help on this?
the steps invloved:
1) first generate a xml file from table something like this:
select * from dbo.LOOK_UP FOR XML AUTO, XMLDATA ?
SELECT * FROM dbo.LOOK_UP FOR XML RAW, ELEMENTS ?
SELECT * FROM dbo.LOOK_UP FOR XML AUTO ?
which one should i use and how do i access after i gnerate a xml file
thanks.
Do you have a PrimaryKey (or clustered index) on that table? Yes, access such a large table in SQL2000 database would be slow, but you can speed up query by using clustered index seek, which I believe should be much quicker than accessing XML file. You can take a look at this link to learn more about clustered index:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_5h6b.asp
|||i do have primary keys and culstered index
but is that true that, if i access xml file it will boost my access speed?
i can cache my xml file and use from cache?
|||My recomendation is run some test and look for these two System stored procs in the Master database sp_xml_preparedocument and sp_xml_removedocument because you can use them to do a lot of things with XML in SQL Server 2000. The links below show you several options including the Dataset.ReadXml method. Hope this helps.
http://msdn.microsoft.com/msdnmag/issues/05/06/DataPoints/default.aspx
http://forums.asp.net/thread/1024186.aspx
|||
after i run the below code:
DECLARE @.hdoc int
DECLARE @.doc varchar(2000)
SET @.doc = '
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE root
[<!ELEMENT root (Customers)*>
<!ELEMENT Customers EMPTY>
<!ATTLIST Customers CustomerID CDATA #IMPLIED ContactName CDATA #IMPLIED>]>
<root>
<Customers CustomerID="ALFKI" ContactName="Maria Anders"/>
</root>'
EXEC sp_xml_preparedocument @.hdoc OUTPUT, @.doc
what do i do after that?
|||The second page of the thread I gave include a sample using repeater and the ReadXml method of the dataset. Hope this helps.
Subscribe to:
Posts (Atom)