Re: Problem with the Legacy ASP files and the Sql Server Express

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)

Light wrote:
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..



"Cowboy (Gregory A. Beamer)" <NoSpamMgbworld@xxxxxxxxxxxxxxxxxx> wrote in message news:48E04A5E-FE31-4A58-83AC-A8283AE582AA@xxxxxxxxxxxxxxxx
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.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com

*********************************************
Think outside the box!
*********************************************
"Light" <a@xxxxx> wrote in message news:exNvKMvcHHA.3644@xxxxxxxxxxxxxxxxxxxxxxx
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



.

Relevant Pages

  • Re: Problem with the Legacy ASP files and the Sql Server Express
    ... table, if another inserting occurs at the nearly exactly the same moment, ... SQL Server will pass it a default value. ... assume that you do have a connection that can reach SQL Server Express. ... unless your stored procedure has some thing that only works ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Problem with the Legacy ASP files and the Sql Server Express
    ... 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. ... return @RETURNVALUE or the SQL Server 2000 version won't work either. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Problem with the Legacy ASP files and the Sql Server Express
    ... set cmd = Server.CreateObject ... 2000, 2005), as long as the connection is OK and the SP has two parameters ... If the problem is not with the SQL server Exp, then how come the SQL Sever ... unless your stored procedure has some thing that only ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: ANSI_WARNINGS error in Stored Procedure
    ... The error says to set these options for your _connection_ not for the query. ... > I have a linked Server on my regular SQL server, and when I try to create ... a stored procedure that reads from the linked server, ...
    (microsoft.public.sqlserver.security)
  • Re: How to get list of EventClasses in MSSQLServer2000
    ... statement inside a stored procedure has completed.') ... SQL Server statement or stored procedure.') ... Plan','Displays the plan tree of the Transact-SQL statement executed.') ... Login','Occurs when a SQL Server login is added or removed; ...
    (microsoft.public.sqlserver.security)