Problem with the Legacy ASP files and the Sql Server Express

[Light]

Hi all,

I posted this question in the sqlserver.newusers group but I am not getting
any response there so I am going to try it on the fine folks here.

I inherited some legacy ASP codes in my office. The original code's backend
is using the SQL Server 2000 and I am testing to use it on the Express
edition.

And I run into the following problem.

One of the pages is using a stored procedure to create a new table and
return the value of the new table ID.

The codes are written in VBScript, briefly, it is as follows:

set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection= dbConnection
cmd.CommandText = "NewCompany"
cmd.CommandType = adCmdStoredProc

cmd.Parameters("@varCompanyName") = Request.Form("COMPANYNAME")
:
:
cmd.Parameters("@RETURNVALUE") = 0

Set rsID = cmdCom.Execute
Set rsID = rsID.NextRecordset
ID = cmdCom.Parameters("@RETURNVALUE")

On the SQL Server 2000, the ID is returned correctly, but the Express
doesn't. In fact, it didn't fill in the vat all. For example, If I set the
RETURNVALUE to -5 before the execute,
the new value remains -5. And I could see the table is created correctly
with the proper id. It is just the return value received at the client web
browser is never set.

So my question is, is there any change I need to make for the Express's
stored procedure?

Any suggestion is highly appreciated. This problem is kind of important as a
lot of our customers are migrating their SQL servers from 2000 to the 2005.

TIA

 

[Cowboy \(Gregory A. Beamer\)]

More likely, you have to set the Express product to accept non-local
connections and enable some other connection protocol than shared memory.
After that, examine the connection string.

Then, check the stored procedure and make sure it is using RETURN @value, or
similar. Otherwise the return value is returning nothing.

Therer is probably more I could do, but would need a deeper dive into the
sproc code.

 

[Light]

Hi Gregory,

Thanks for the prompt response.

Just for trying, I turned on all the protocols (like tcp/ip) and I couldn't
find the configuration for non-local conenctions. Anyway, it doesn't work.

The connection string is fine as I could make connection to the DB and
create the new table.

And yes, the stored procedure did do a
return @RETURNVALUE or the SQL Server 2000 version won't work either.

Anyway, I also see another post by David Lozzi (Returning SCOPE_IDENTITY
from SQLDataSource and DetailsView) in this newsgroup yesterday , which
actually is the same problem as mine (except his is using ASP.Net, so my
problem is not related to legacy ASP), so this problem is pretty common?

Thanks again..

 

[bruce barker]

as you are using the com based adodb library, its not asp.net based. you
are only reading one result set before trying to access the return
parameter values. check that your 2005 sp's only return one, though a
better approach is to actually read all result (NextResult) sets before
accessing parameter values.

you should switch to ado.net, as you current code a bunch of memory
leaks (you are not releasing com objects).


-- bruce (sqlwork.com)

 

[Norman Yuan]

Although this is not a NG for COM/ADO, but...

Could you post your store procedure and the whole portion of VBScript code
for ccreating ADODB.Command and its parameters (you omitted some key part of
code in your first post), so that one could not tell how the parameter is
created for the ADODB.Command object. Of course this is assume that you do
have a connection that can reach SQL Server Express. I do not think your
problem is due to difference of SQL Server2000 and SQL Server2005, unless
your stored procedure has some thing that only works in SQL Server2000, not
SQL Server 2005.

 

[Light]

Thanks Norman.

Since it is too late for me to move this post to the other NG, I'l keep
it here for now.

The new company table is created correctly.

The connection string:


Here is the stored procedure, with some mod to make it short:

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[NewCompany]') and OBJECTPROPERTY(id, N'IsProcedure')=1)
drop procedure [dbo].[NewCompany]
GO

CREATE Procedure NewCompany
(
@varCompanyName varchar (50) = null,
:
:
@RETURN_VALUE int OUTPUT
)
As
INSERT INTO tblCompany(varCompanyName)
values
(@varCompanyName )
SELECT @RETURN_VALUE = @@identity
return @RETURN_VALUE

 

[Norman Yuan]

From your SP, I do not see your issue is caused by this SP, however, the SP
is not well written, IMO.

Firstly, do not use @@Idehtity, use SCOPE_IDENTITY() instead. There are
quite some discussion on this topic. In your case, @@Identity may not be
guranteed as the identity of the record you just inserted into Compony
table, if another inserting occurs at the nearly exactly the same moment,
say right after your inserting but before your SELECT @Return_Value is
executed.

Secondly, you have already define @return_Value as OUTPUT paramter, there is
no need to pass it as return value. in SP, RETURN somevalue is meant for
return a value to indicate the SP execution status (succeeded, failed, or
partially executed...). Do not confuse it with OUTPUT parameter. In most SP,
you can simply leave it as

Create Procedure
As
....
....
RETURN

SQL Server will pass it a default value.

However, these should not affect your VBScript getting unexpected result, as
your OP said.

As I mentioned, you need post the VBScript code to show how do you create
ADODB.Command and its parameter, which you omitted in your OP. I strongly
suspect your VB code is wrong on how you build ADODB.Command, its Parameters
and how you get the OUTPUT value from the parameters. Until seeing that part
of VB code, I can say more.

Thanks Norman.

Since it is too late for me to move this post to the other NG, I'l keep it
here for now.

The new company table is created correctly.

The connection string:


Here is the stored procedure, with some mod to make it short:

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[NewCompany]') and OBJECTPROPERTY(id, N'IsProcedure')=1)
drop procedure [dbo].[NewCompany]
GO

CREATE Procedure NewCompany
(
@varCompanyName varchar (50) = null,
:
:
@RETURN_VALUE int OUTPUT
)
As
INSERT INTO tblCompany(varCompanyName)
values
(@varCompanyName )
SELECT @RETURN_VALUE = @@identity
return @RETURN_VALUE

Although this is not a NG for COM/ADO, but...

Could you post your store procedure and the whole portion of VBScript
code for ccreating ADODB.Command and its parameters (you omitted some key
part of code in your first post), so that one could not tell how the
parameter is created for the ADODB.Command object. Of course this is
assume that you do have a connection that can reach SQL Server Express. I
do not think your problem is due to difference of SQL Server2000 and SQL
Server2005, unless your stored procedure has some thing that only works
in SQL Server2000, not SQL Server 2005.

 

[Light]

Hi Norman,

Again, thank.

Sp aside, I thought the 1st post already give you my VBScript.

I tried the Scope_Identify(), but that didn't help. As for the return,
Gregory in his reply also said it needed.

If the problem is not with the SQL server Exp, then how come the SQL
Sever 2000 works?

That is somethng I can't understand.

From your SP, I do not see your issue is caused by this SP, however, the SP
is not well written, IMO.

Firstly, do not use @@Idehtity, use SCOPE_IDENTITY() instead. There are
quite some discussion on this topic. In your case, @@Identity may not be
guranteed as the identity of the record you just inserted into Compony
table, if another inserting occurs at the nearly exactly the same moment,
say right after your inserting but before your SELECT @Return_Value is
executed.

Secondly, you have already define @return_Value as OUTPUT paramter, there is
no need to pass it as return value. in SP, RETURN somevalue is meant for
return a value to indicate the SP execution status (succeeded, failed, or
partially executed...). Do not confuse it with OUTPUT parameter. In most SP,
you can simply leave it as

Create Procedure
As
...
...
RETURN

SQL Server will pass it a default value.

However, these should not affect your VBScript getting unexpected result, as
your OP said.

As I mentioned, you need post the VBScript code to show how do you create
ADODB.Command and its parameter, which you omitted in your OP. I strongly
suspect your VB code is wrong on how you build ADODB.Command, its Parameters
and how you get the OUTPUT value from the parameters. Until seeing that part
of VB code, I can say more.

Thanks Norman.

Since it is too late for me to move this post to the other NG, I'l keep it
here for now.

The new company table is created correctly.

The connection string:


Here is the stored procedure, with some mod to make it short:

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[NewCompany]') and OBJECTPROPERTY(id, N'IsProcedure')=1)
drop procedure [dbo].[NewCompany]
GO

CREATE Procedure NewCompany
(
@varCompanyName varchar (50) = null,
:
:
@RETURN_VALUE int OUTPUT
)
As
INSERT INTO tblCompany(varCompanyName)
values
(@varCompanyName )
SELECT @RETURN_VALUE = @@identity
return @RETURN_VALUE

Although this is not a NG for COM/ADO, but...

Could you post your store procedure and the whole portion of VBScript
code for ccreating ADODB.Command and its parameters (you omitted some key
part of code in your first post), so that one could not tell how the
parameter is created for the ADODB.Command object. Of course this is
assume that you do have a connection that can reach SQL Server Express. I
do not think your problem is due to difference of SQL Server2000 and SQL
Server2005, unless your stored procedure has some thing that only works
in SQL Server2000, not SQL Server 2005.




Hi Gregory,

Thanks for the prompt response.

Just for trying, I turned on all the protocols (like tcp/ip) and I
couldn't find the configuration for non-local conenctions. Anyway, it
doesn't work.

The connection string is fine as I could make connection to the DB and
create the new table.

And yes, the stored procedure did do a
return @RETURNVALUE or the SQL Server 2000 version won't work either.

Anyway, I also see another post by David Lozzi (Returning SCOPE_IDENTITY

from SQLDataSource and DetailsView) in this newsgroup yesterday , which

actually is the same problem as mine (except his is using ASP.Net, so my
problem is not related to legacy ASP), so this problem is pretty common?

Thanks again..



message

More likely, you have to set the Express product to accept non-local
connections and enable some other connection protocol than shared
memory. After that, examine the connection string.

Then, check the stored procedure and make sure it is using RETURN
@value, or similar. Otherwise the return value is returning nothing.

Therer is probably more I could do, but would need a deeper dive into
the sproc code.




Hi all,

I posted this question in the sqlserver.newusers group but I am not
getting any response there so I am going to try it on the fine folks
here.

I inherited some legacy ASP codes in my office. The original code's
backend
is using the SQL Server 2000 and I am testing to use it on the Express
edition.

And I run into the following problem.

One of the pages is using a stored procedure to create a new table and
return the value of the new table ID.

The codes are written in VBScript, briefly, it is as follows:

set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection= dbConnection
cmd.CommandText = "NewCompany"
cmd.CommandType = adCmdStoredProc

cmd.Parameters("@varCompanyName") = Request.Form("COMPANYNAME")
:
:
cmd.Parameters("@RETURNVALUE") = 0

Set rsID = cmdCom.Execute
Set rsID = rsID.NextRecordset
ID = cmdCom.Parameters("@RETURNVALUE")

On the SQL Server 2000, the ID is returned correctly, but the Express
doesn't. In fact, it didn't fill in the vat all. For example, If I set
the
RETURNVALUE to -5 before the execute,
the new value remains -5. And I could see the table is created
correctly
with the proper id. It is just the return value received at the client
web
browser is never set.

So my question is, is there any change I need to make for the Express's
stored procedure?

Any suggestion is highly appreciated. This problem is kind of important
as a lot of our customers are migrating their SQL servers from 2000 to
the 2005.

TIA

 

[Norman Yuan]

Here is your code in OP (note what I added):

set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection= dbConnection
cmd.CommandText = "NewCompany"
cmd.CommandType = adCmdStoredProc

'=============================
'There MUST be some code in this part to add ADODB.Parameters to the cmd
object!
In your case, you MUST create two parameters and append them to the cmd
Object.
'This code would be like:

Set pmt=cmd.CreateParameter("@varCompanyName",adVarChar,
adParameterInput,100)
pmt.Value=Request.Form("COMPANYNAME")
cmd.Parameter.Append pmt

Set pmt=cmd.CreateParameter("@Return_Value",adInteger, adParameterOutput)
cmd.Parameter.Append pmt

'Then you can execute the command and then retrieve the ID
cmd.Execute
ID=cmd.Paramerters("@Return_Value")

I am 100% sure this code works against any version of SQL Server (6.5, 7,
2000, 2005), as long as the connection is OK and the SP has two parameters
@varCompany as Input and @Return_Value as Output (not Return, though).

'=============================

'So, the code between "******" lines should be removed
'***************************
cmd.Parameters("@varCompanyName") = Request.Form("COMPANYNAME")
:
:
cmd.Parameters("@RETURNVALUE") = 0
'***************************************

'I do not know what rsID is (a RecordSet?) and why you call
rsID.NextRecordset
'Your SP does not return a set record, nor more than one set data is queried
in the SP, so why?
Set rsID = cmdCom.Execute
Set rsID = rsID.NextRecordset
ID = cmdCom.Parameters("@RETURNVALUE")

Hi Norman,

Again, thank.

Sp aside, I thought the 1st post already give you my VBScript.

I tried the Scope_Identify(), but that didn't help. As for the return,
Gregory in his reply also said it needed.

If the problem is not with the SQL server Exp, then how come the SQL Sever
2000 works?

That is somethng I can't understand.

From your SP, I do not see your issue is caused by this SP, however, the
SP is not well written, IMO.

Firstly, do not use @@Idehtity, use SCOPE_IDENTITY() instead. There are
quite some discussion on this topic. In your case, @@Identity may not be
guranteed as the identity of the record you just inserted into Compony
table, if another inserting occurs at the nearly exactly the same moment,
say right after your inserting but before your SELECT @Return_Value is
executed.

Secondly, you have already define @return_Value as OUTPUT paramter, there
is no need to pass it as return value. in SP, RETURN somevalue is meant
for return a value to indicate the SP execution status (succeeded,
failed, or partially executed...). Do not confuse it with OUTPUT
parameter. In most SP, you can simply leave it as

Create Procedure
As
...
...
RETURN

SQL Server will pass it a default value.

However, these should not affect your VBScript getting unexpected result,
as your OP said.

As I mentioned, you need post the VBScript code to show how do you create
ADODB.Command and its parameter, which you omitted in your OP. I strongly
suspect your VB code is wrong on how you build ADODB.Command, its
Parameters and how you get the OUTPUT value from the parameters. Until
seeing that part of VB code, I can say more.

Thanks Norman.

Since it is too late for me to move this post to the other NG, I'l keep
it here for now.

The new company table is created correctly.

The connection string:


Here is the stored procedure, with some mod to make it short:

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[NewCompany]') and OBJECTPROPERTY(id,
N'IsProcedure')=1)
drop procedure [dbo].[NewCompany]
GO

CREATE Procedure NewCompany
(
@varCompanyName varchar (50) = null,
:
:
@RETURN_VALUE int OUTPUT
)
As
INSERT INTO tblCompany(varCompanyName)
values
(@varCompanyName )
SELECT @RETURN_VALUE = @@identity
return @RETURN_VALUE


Norman Yuan wrote:

Although this is not a NG for COM/ADO, but...

Could you post your store procedure and the whole portion of VBScript
code for ccreating ADODB.Command and its parameters (you omitted some
key part of code in your first post), so that one could not tell how the
parameter is created for the ADODB.Command object. Of course this is
assume that you do have a connection that can reach SQL Server Express.
I do not think your problem is due to difference of SQL Server2000 and
SQL Server2005, unless your stored procedure has some thing that only
works in SQL Server2000, not SQL Server 2005.




Hi Gregory,

Thanks for the prompt response.

Just for trying, I turned on all the protocols (like tcp/ip) and I
couldn't find the configuration for non-local conenctions. Anyway, it
doesn't work.

The connection string is fine as I could make connection to the DB and
create the new table.

And yes, the stored procedure did do a
return @RETURNVALUE or the SQL Server 2000 version won't work either.

Anyway, I also see another post by David Lozzi (Returning
SCOPE_IDENTITY

from SQLDataSource and DetailsView) in this newsgroup yesterday ,
which

actually is the same problem as mine (except his is using ASP.Net, so
my problem is not related to legacy ASP), so this problem is pretty
common?

Thanks again..



in message

More likely, you have to set the Express product to accept non-local
connections and enable some other connection protocol than shared
memory. After that, examine the connection string.

Then, check the stored procedure and make sure it is using RETURN
@value, or similar. Otherwise the return value is returning nothing.

Therer is probably more I could do, but would need a deeper dive into
the sproc code.




Hi all,

I posted this question in the sqlserver.newusers group but I am not
getting any response there so I am going to try it on the fine folks
here.

I inherited some legacy ASP codes in my office. The original code's
backend
is using the SQL Server 2000 and I am testing to use it on the
Express
edition.

And I run into the following problem.

One of the pages is using a stored procedure to create a new table
and
return the value of the new table ID.

The codes are written in VBScript, briefly, it is as follows:

set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection= dbConnection
cmd.CommandText = "NewCompany"
cmd.CommandType = adCmdStoredProc

cmd.Parameters("@varCompanyName") = Request.Form("COMPANYNAME")
:
:
cmd.Parameters("@RETURNVALUE") = 0

Set rsID = cmdCom.Execute
Set rsID = rsID.NextRecordset
ID = cmdCom.Parameters("@RETURNVALUE")

On the SQL Server 2000, the ID is returned correctly, but the Express
doesn't. In fact, it didn't fill in the vat all. For example, If I
set the
RETURNVALUE to -5 before the execute,
the new value remains -5. And I could see the table is created
correctly
with the proper id. It is just the return value received at the
client web
browser is never set.

So my question is, is there any change I need to make for the
Express's
stored procedure?

Any suggestion is highly appreciated. This problem is kind of
important as a lot of our customers are migrating their SQL servers
from 2000 to the 2005.

TIA

 

[Cowboy \(Gregory A. Beamer\)]

Did you turn on the SQL Browser service? That is part of the requirement. By
default, SQL Server, except, I think, Enterprise (perhaps Standard) installs
only allowing local connections. You need both a protocol and the browser
service to attach to it.

 

[Light]

Thanks a billion, I'll try your codes once I got back to the office
tomorrow.

As to why rsID.NextRecordset was executed, I haven't a clue. As I said
earlier, I inherited this asp application from someone. And I could see
the codes I showed so far are all over the places (at least some 20
files). It looks like it is not going to be an easy job getting it to
work on the SQL Server 2005 and Express .

Again, thanks for your effort and times!

Here is your code in OP (note what I added):

set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection= dbConnection
cmd.CommandText = "NewCompany"
cmd.CommandType = adCmdStoredProc

'=============================
'There MUST be some code in this part to add ADODB.Parameters to the cmd
object!
In your case, you MUST create two parameters and append them to the cmd
Object.
'This code would be like:

Set pmt=cmd.CreateParameter("@varCompanyName",adVarChar,
adParameterInput,100)
pmt.Value=Request.Form("COMPANYNAME")
cmd.Parameter.Append pmt

Set pmt=cmd.CreateParameter("@Return_Value",adInteger, adParameterOutput)
cmd.Parameter.Append pmt

'Then you can execute the command and then retrieve the ID
cmd.Execute
ID=cmd.Paramerters("@Return_Value")

I am 100% sure this code works against any version of SQL Server (6.5, 7,
2000, 2005), as long as the connection is OK and the SP has two parameters
@varCompany as Input and @Return_Value as Output (not Return, though).

'=============================

'So, the code between "******" lines should be removed
'***************************
cmd.Parameters("@varCompanyName") = Request.Form("COMPANYNAME")
:
:
cmd.Parameters("@RETURNVALUE") = 0
'***************************************

'I do not know what rsID is (a RecordSet?) and why you call
rsID.NextRecordset
'Your SP does not return a set record, nor more than one set data is queried
in the SP, so why?
Set rsID = cmdCom.Execute
Set rsID = rsID.NextRecordset
ID = cmdCom.Parameters("@RETURNVALUE")

Hi Norman,

Again, thank.

Sp aside, I thought the 1st post already give you my VBScript.

I tried the Scope_Identify(), but that didn't help. As for the return,
Gregory in his reply also said it needed.

If the problem is not with the SQL server Exp, then how come the SQL Sever
2000 works?

That is somethng I can't understand.

From your SP, I do not see your issue is caused by this SP, however, the
SP is not well written, IMO.

Firstly, do not use @@Idehtity, use SCOPE_IDENTITY() instead. There are
quite some discussion on this topic. In your case, @@Identity may not be
guranteed as the identity of the record you just inserted into Compony
table, if another inserting occurs at the nearly exactly the same moment,
say right after your inserting but before your SELECT @Return_Value is
executed.

Secondly, you have already define @return_Value as OUTPUT paramter, there
is no need to pass it as return value. in SP, RETURN somevalue is meant
for return a value to indicate the SP execution status (succeeded,
failed, or partially executed...). Do not confuse it with OUTPUT
parameter. In most SP, you can simply leave it as

Create Procedure
As
...
...
RETURN

SQL Server will pass it a default value.

However, these should not affect your VBScript getting unexpected result,
as your OP said.

As I mentioned, you need post the VBScript code to show how do you create
ADODB.Command and its parameter, which you omitted in your OP. I strongly
suspect your VB code is wrong on how you build ADODB.Command, its
Parameters and how you get the OUTPUT value from the parameters. Until
seeing that part of VB code, I can say more.



Thanks Norman.

Since it is too late for me to move this post to the other NG, I'l keep
it here for now.

The new company table is created correctly.

The connection string:


Here is the stored procedure, with some mod to make it short:

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[NewCompany]') and OBJECTPROPERTY(id,
N'IsProcedure')=1)
drop procedure [dbo].[NewCompany]
GO

CREATE Procedure NewCompany
(
@varCompanyName varchar (50) = null,
:
:
@RETURN_VALUE int OUTPUT
)
As
INSERT INTO tblCompany(varCompanyName)
values
(@varCompanyName )
SELECT @RETURN_VALUE = @@identity
return @RETURN_VALUE


Norman Yuan wrote:


Although this is not a NG for COM/ADO, but...

Could you post your store procedure and the whole portion of VBScript
code for ccreating ADODB.Command and its parameters (you omitted some
key part of code in your first post), so that one could not tell how the
parameter is created for the ADODB.Command object. Of course this is
assume that you do have a connection that can reach SQL Server Express.
I do not think your problem is due to difference of SQL Server2000 and
SQL Server2005, unless your stored procedure has some thing that only
works in SQL Server2000, not SQL Server 2005.





Hi Gregory,

Thanks for the prompt response.

Just for trying, I turned on all the protocols (like tcp/ip) and I
couldn't find the configuration for non-local conenctions. Anyway, it
doesn't work.

The connection string is fine as I could make connection to the DB and
create the new table.

And yes, the stored procedure did do a
return @RETURNVALUE or the SQL Server 2000 version won't work either.

Anyway, I also see another post by David Lozzi (Returning
SCOPE_IDENTITY

from SQLDataSource and DetailsView) in this newsgroup yesterday ,

which

actually is the same problem as mine (except his is using ASP.Net, so
my problem is not related to legacy ASP), so this problem is pretty
common?

Thanks again..



in message


More likely, you have to set the Express product to accept non-local
connections and enable some other connection protocol than shared
memory. After that, examine the connection string.

Then, check the stored procedure and make sure it is using RETURN
@value, or similar. Otherwise the return value is returning nothing.

Therer is probably more I could do, but would need a deeper dive into
the sproc code.





Hi all,

I posted this question in the sqlserver.newusers group but I am not
getting any response there so I am going to try it on the fine folks
here.

I inherited some legacy ASP codes in my office. The original code's
backend
is using the SQL Server 2000 and I am testing to use it on the
Express
edition.

And I run into the following problem.

One of the pages is using a stored procedure to create a new table
and
return the value of the new table ID.

The codes are written in VBScript, briefly, it is as follows:

set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection= dbConnection
cmd.CommandText = "NewCompany"
cmd.CommandType = adCmdStoredProc

cmd.Parameters("@varCompanyName") = Request.Form("COMPANYNAME")
:
:
cmd.Parameters("@RETURNVALUE") = 0

Set rsID = cmdCom.Execute
Set rsID = rsID.NextRecordset
ID = cmdCom.Parameters("@RETURNVALUE")

On the SQL Server 2000, the ID is returned correctly, but the Express
doesn't. In fact, it didn't fill in the vat all. For example, If I
set the
RETURNVALUE to -5 before the execute,
the new value remains -5. And I could see the table is created
correctly
with the proper id. It is just the return value received at the
client web
browser is never set.

So my question is, is there any change I need to make for the
Express's
stored procedure?

Any suggestion is highly appreciated. This problem is kind of
important as a lot of our customers are migrating their SQL servers

from 2000 to the 2005.

TIA

 

[Light]

Did you turn on the SQL Browser service? That is part of the
requirement. By default, SQL Server, except, I think, Enterprise
(perhaps Standard) installs only allowing local connections. You need
both a protocol and the browser service to attach to it.

Yes, I did.

Anyway, Bob Barrows on the ado NG solved the problem.

It turned out I need to add "SET NOCOUNT ON" to the stored procedure,
not sure about the reason though, as the option means to return the
number of affected rows.

Anyway a big thank you to you and Norman for putting in the times for me.

Cheers.

 

[Norman Yuan]

It implies there is problem with the VBScript code: SET NOCOUNT ON will
affect the returned RecordSet. If the purpose of the VBScript code is to
create a table, add a record and then return the new record's ID, then why
use a RecordSet? The whole thing should be: sending two parameters (one
Input, and one Output), executing the command, retrieving the output
parameter. In this case, you minimize the data access trafiic and SET NO
COUNT ON has no effect.

Anyway, the code may work OK, but there is possible issue, at least, it is
not optimized.