Solved

Stored Procedure only partially works???  ASP-> SQL Server

Posted on 2004-09-24
19
527 Views
Last Modified: 2008-01-09
I have a rather difficult question...at least everyone around here makes it sound like it is...that and they have no solutions for me.

I have a SQL Server 2000 database setup, and am trying to call a stored procedure (sp_updateInvcID) from an ASP page.  I am going to cross post this in the ASP forum as well, as I'm not certain where the problem lies (ie. database problem or asp problem).

Table assets
---------------
asst_id identity
asss_id decimal(17)
invc_id decimal(17)
mnfc_id decimal(17)
modl_id decimal(17)
ascr_id decimal(17)
serial_number varchar(30)
asset_number varchar(30)

There are more columns, but these are the ones I am working with.  

sp_updateInvcID
--------------------
CREATE PROCEDURE sp_updateInvcID
     @asstID int,
     @invcID int,    
     @ascrID int,
     @assetNum varchar(30),
     @serial varchar(30)
 AS
     update assets set asss_id=4, invc_id=@invcID, serial_number=@serial,
     asset_number=@assetNum, received_dt=getDate(), paying_ascr_id=@ascrID
     where asst_id=@asstID
GO

ASP JScript to call stored Procedure
------------------------------------------
function updateAssetInvcID(asstID, invcID, ascrID, assetNum, serial) {
     cmd = getStoredProc("sp_updateInvcID");
     cmd.Parameters.Append(cmd.CreateParameter("@asstID", 3, 1, 7, asstID));
     cmd.Parameters.Append(cmd.CreateParameter("@invcID", 3, 1, 7, invcID));    
     cmd.Parameters.Append(cmd.CreateParameter("@ascrID", 3, 1, 7, ascrID));          
     cmd.Parameters.Append(cmd.CreateParameter("@assetNum", 200, 1, 30, assetNum));
     cmd.Parameters.Append(cmd.CreateParameter("@serial", 200, 1, 30, serial));          
     cmd.Execute();    
}

function getStoredProc(name) {
     conn = getConn();
     cmd = Server.CreateObject("ADODB.Command");
     cmd.ActiveConnection = conn;
     cmd.CommandText = name;
     cmd.CommandType = 4;
     cmd.Prepared = true;
     return cmd;
}

function getConn() {
     conn = Server.CreateObject("ADODB.Connection");
     conn.Open = Application("conn");
     return conn;
}

ASP JScript which makes function call
--------------------------------------------
try {
   updateAssetInvcID(asstID, invcID, ascrID, assetNum, serial);
} catch (e) {
  Session("Error") = e.message;
  Response.Redirect("/webapp/error/error.asp");
}          

I have tested all the variables in the try / catch block, and they are all of the appropriate types (ie. strings for assetNum and serial, and integers for the rest).

Here is where things get 'interesting' :(  The way the app works, you may end up updating the invcID of a number of records.  I use a for loop to go through each of the records, calling the same function for each of the records.  For small numbers of records (1-5) the function works MOSTLY fine...but I'll get to that in a second.

For large numbers of function calls, the application fails saying "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another."

Further, for the 1-5 rows in the table that WERE effected, the asss_id is NOT updated!  I've been struggling with this one for a while now, and asked a lot of people a lot of questions...any further information / suggestions anyone can give are GREATLY appreciated at this point.  

Further info
--------------
IIS 5.0 with appropriate updates / patches
MDAC 2.whatever it's up to
Connection to database is using:
"Provider=sqloledb; Data Source=;User ID=READ;Password=READ" (** Permissions on the database have been set for the read user to execute this stored procedure**)

If anyone needs further information, let me know, and I'll try as best I can to provide it.
0
Comment
Question by:ShaymusBane2
  • 11
  • 7
19 Comments
 
LVL 8

Expert Comment

by:a_twixt_in_the_tale
Comment Utility
> Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
asLockOptimistic?

> the asss_id is NOT updated
pardone moi if im wrong. is the procedure ok.

:)
Don
0
 

Author Comment

by:ShaymusBane2
Comment Utility
The procedure works fine in the SQL analyzer if I run it...and if I copy and paste the 'sql' part out of the stored procedure and run it in SQL analyzer it also works....it's only when I'm calling the store procedure from ASP that it does the partial work.

As for the adLockOptimistic...is that a concern when opening a connection for executing a stored procedure with no return value?

I had been lead to believe that utilizing different locking mechanisms and cursor types is only important for returning recordsets?  This is what I use...

function getRecordSet() {
      conn = getConn();
      rs = Server.CreateObject("ADODB.Recordset");
      rs.CursorLocation = 3; // adUseClient
      rs.CursorType = 3; //adOpenStatic
      rs.ActiveConnection = conn;
      return rs;
}

Not that I'm using this in this instance, but this is what I've got setup for recordset creation and it works exactly how I want it....but if you could elaborate further on the reference to adLockOptimistic and whether or not this would apply for stored procedure calls, that would be greatly appreciated...
0
 

Author Comment

by:ShaymusBane2
Comment Utility
I just updated the getConn() function to reflect the following, based on your suggestion

function getConn() {
      conn = Server.CreateObject("ADODB.Connection");
      conn.Mode = 3; // adModeReadWrite
      conn.CursorLocation = 2; // adUseServer
      conn.Open = Application("conn");
      return conn;
}

It still returns / causes the same "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another." error...
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
Comment Utility
The only obvious problem I can see is that you have defined integers to be 7 bytes, when in fact they are 4 bytes.  So try your code as follows:

function updateAssetInvcID(asstID, invcID, ascrID, assetNum, serial) {
     cmd = getStoredProc("sp_updateInvcID");
     cmd.Parameters.Append(cmd.CreateParameter("@asstID", 3, 1, 4, asstID));
     cmd.Parameters.Append(cmd.CreateParameter("@invcID", 3, 1, 4, invcID));    
     cmd.Parameters.Append(cmd.CreateParameter("@ascrID", 3, 1, 4, ascrID));          
     cmd.Parameters.Append(cmd.CreateParameter("@assetNum", 200, 1, 30, assetNum));
     cmd.Parameters.Append(cmd.CreateParameter("@serial", 200, 1, 30, serial));          
     cmd.Execute();    
}

However, I can see how this error could have crept in:  The corresponding columns for the parameters @invcID and @ascrID are decimal (although this last may have a typo as "paying_ascr_id" probably should be "ascr_id").

Also, make sure none of the parameters have a null value.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>but if you could elaborate further on the reference to adLockOptimistic and whether or not this would apply for stored procedure calls<<
You are right, recordsets (and adLockOptimistic) are irrelevant with stored procedures that do not return resultsets.
0
 

Author Comment

by:ShaymusBane2
Comment Utility
acperkins, thanks for the comments, and for clarifying the adLockOptimistic query...

I'll give changing the integers to 4 a try, but I had them at 5 length before and it was returning an error because some of the identifiers I was passing were 5 charcters or longer (ie. id=12345, 123456).  I had read this parameter as indicating the number of 'spaces' the value would contain?  I didn't realize it referred to bytes...

Sorry for no follow ups over the weekend, but I'm a mon-fri worker ;)  So for anyone tracking this thread, activity will be picking up now.
0
 

Author Comment

by:ShaymusBane2
Comment Utility
acperkins...just tried changing to '4' in place of the '7' I had...similar error occurs...although when I dig deeper into this error, it relates more to the parameters I'm sending it being 'too big' for the place holders I've allocated.  

I took this to mean that it's trying to fit four bit 5 and 6 bit characters into a 4 bit placeholder...?  When I encountered this same error (different than the original problem) in regards to a 'notes' field, all I did was increase the place holder to fix it...ie. for a notes field that allows 200 characters...

cmd.Parameters.Append(cmd.CreateParameter(@notes, 200, 1, 201, notes));

This fixed the problem...
0
 

Author Comment

by:ShaymusBane2
Comment Utility
Forgot to post the link to the other topic heading I have this same posted on...

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21144232.html

Maybe something someone has posted there will tweak someone's memory?  I know I'm at a loss on this one :(
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Integers are always 4 bytes regardless if the value is 1, 2, 3, ... 9 or 10 digits.

If you feel that my other points and I quote:
<quote>
However, I can see how this error could have crept in:  The corresponding columns for the parameters @invcID and @ascrID are decimal (although this last may have a typo as "paying_ascr_id" probably should be "ascr_id").

Also, make sure none of the parameters have a null value.
</quote>

Are addressed than I have no idea.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:ShaymusBane2
Comment Utility
acperkins: I've altered the data type to integer in the database, and witnessed the same error.  I've also changed the ASP to utilize the decimal type.  The error remains in both cases.  And paying_ascr_id isn't a typo...there are two columns (managing_ascr_id and paying_ascr_id) that assets has...the specific procedure is only working with the paying reference.  From doing Response.write() statements all over the code, all of the parameters are populated with values as well.  I've taken those same values, and using Query Analyzer, ran the stored procedure with those same values, and they work 100%.  I've used the query debug tool with the values to see what occurs, and it seems to work as well.  

After reading over ala frosty's post in the other thread, regarding the unique constraints on the assets table, I started 'digging' into some of the other things related to Assets.

The DBA has written the following trigger on assets though, which I was unaware of:

CREATE TRIGGER asset_number_trig ON dbo.ASSETS
FOR INSERT, UPDATE, DELETE
AS
  if update(ASSET_NUMBER)
  begin
    if ( select count(*)
           from assets a, inserted i
          where i.ASSET_NUMBER is not null and
                a.ASSET_NUMBER = i.ASSET_NUMBER )  <>
      ( select count(*)
        from inserted
        where asset_number is not null)                                              
    begin
      rollback transaction
      raiserror( 'A duplicate asset number has been detected.  Transaction failed.',11,1)
    end
  end

I was told that this is what he's using to get around one of the 'features' of SQL server 2000...The original application was hosted in Oracle.  The asset_number column allowed multiple instances of 'null' as a unique column...what this yields, is that null!=null.  Apparantly, SQL server doesn't follow SQL standard (can't recall the specifics).   In SQL server, null = null.  So you cannot declare a column unique, and have multiple null values.  The business procedure of the application which the database is for, must have multiple null values allowable for asset_number.

Long winded update, but there it is.  I've already removed the trigger and tried the same procedure call with the same results though...so the trigger doesn't SEEM to be the issue...but I don't know enough about SQL triggers to say whether this is the case or not.  

Any further thoughts?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>I've also changed the ASP to utilize the decimal type.<<
Post your code.  If you are in fact using Decimal (you were using integer in your original code) you must specify the Precision and Scale.  

>>And paying_ascr_id isn't a typo<<
Since you had not included it in your table structure I could only assume it was a typo. And yes, I read "There are more columns, but these are the ones I am working with."

>>I've taken those same values, and using Query Analyzer, ran the stored procedure with those same values, and they work 100%.<<
This is directly unrelated to SQL Server and has to do with how you have defined the Stored Procedure in ASP

>>In SQL server, null = null.<<
In SQL Server Null is never equal to Null.  Just try this:
If Null = Null
  Print 'Null = Null'
Else
  Print 'Null <> Null'
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Also post your stored procedure if it has changed.
0
 

Author Comment

by:ShaymusBane2
Comment Utility
Sorry if my responses sounded 'short'...it was far from my intent to do so...I greatly appreciate the time and effort you are giving me with this...here are the items you requested

function updateAssetInvcID(asstID, invcID, ascrID, assetNum, serial) {
     cmd = getStoredProc("sp_updateInvcID");
     cmd.Parameters.Append(cmd.CreateParameter("@asstID", 14, 1, 7, asstID)); //adDecimal = 14;
     cmd.Parameters.Append(cmd.CreateParameter("@invcID", 14, 1, 7, invcID)); //adDecimal = 14;    
     cmd.Parameters.Append(cmd.CreateParameter("@ascrID", 14, 1, 7, ascrID)); //adDecimal = 14;      
     cmd.Parameters.Append(cmd.CreateParameter("@assetNum", 200, 1, 30, assetNum));
     cmd.Parameters.Append(cmd.CreateParameter("@serial", 200, 1, 30, serial));          
     cmd.Execute();    
}

I also tried doing the alternate, ie. change the assets table to take integers, rather than decimal values, with a precision matching that listed in the SQL Enterprice Manager.

asst_id identity
asss_id integer(7)
invc_id integer(7)
mnfc_id integer(7)
modl_id integer(7)
ascr_id integer(7)
serial_number varchar(30)
asset_number varchar(30)

I'm not sure I follow your comment about Query Analyzer not being related...our DBA had me run execute commands on my stored procedure, then view the database to make sure that the values I was submitting were actually being updated in the database.  Using the Response.Write() statements, I took the values that were being generated, and 'cut and paste' them into the corresponding parameter locations, to perform the stored procedure test.  This is what I meant when I said I'd used Query Analyzer...I'm more familiar with Oracle, so to use the Oracle equivalent, I ran the stored procedure using SQL Plus, to make sure that the stored procedure was doing what it was supposed to...

The comment I made about 'null = null' refers to the Microsoft white paper published in 2000 which talks about migrating and oracle application into SQL Server 2000.  

Under the topic of 'Primary Keys and Unique Columns' in the following link, is where I encountered the situation I was talking about...if you do a find for the quoted text, it covers the specifics in the fourth paragraph.

http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part2/c0761.mspx

The business logic for the application requires asset_number to be unique, but it also requires you to be able to add assets into the database without entering in any asset_number.  The trigger was what the DBA implemented to recreate this functionality.

The stored procedure I ended up using looked as follows:

CREATE PROCEDURE sp_updateInvcID
     @asstID decimal(17,0),
     @invcID decimal(17,0),    
     @ascrID decimal(17,0),
     @assetNum varchar(30),
     @serial varchar(30)
 AS
     update assets set asss_id=4, invc_id=@invcID, serial_number=@serial,
     asset_number=@assetNum, received_dt=getDate(), paying_ascr_id=@ascrID
     where asst_id=@asstID
GO

I got the precision values to try / use from the assets table.

To clarify, I tried changing the ASP code to mirror the database datatypes, removing the possibility of the implicit datatype conversion being at fault.  I also tried changing the database to mirror the ASP code for the same reason.  The result turned out to be the same in both cases.  

I'm waiting on a more detailed analysis of all the relational joins for the assets table, as per the suggestion from ala frosty to check for constraints being at fault...but so far I havn't been able to get access to the database or the dba today...I will try again tomorrow.

Once again, I will apologize if my previous comments came across as being short...perhaps I've read into your comments something that wasn't there in that regards, but I would like to stress how much I do appreciate any and all suggestions you are providing me with this problem.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>Sorry if my responses sounded 'short'...<<
Not my intention.  I trust that I did not give you that impression.

>>The comment I made about 'null = null'<<
I understand now what you are saying.  What I was getting at is that:
If Null Is Null        -- Is true

If Null = Null        -- Is false

>>perhaps I've read into your comments something that wasn't there in that regards<<
I believe you did.

>>I'm not sure I follow your comment about Query Analyzer not being related<<
Simply put:  The Stored Procedure can run flawlessly in SQL Query Analyzer, but fail from ASP, because it has not been declared correctly.  This is just as true for Oracle, as well.

But to your question.  As I mentioned previously, in order to use decimal values you must define the precision and scale for each parameter. So while I am not advocating that you use decimal(17,0), since you are now using decimal parameters you must define the precision of 17 and the scale of 0 (this last may not be entirely necessary, but I have never used a decimal with a scale of 0, prefering to use integers, instead)

As an aside, and for the record:   Integer data type would be sufficient if your maximum size is 2,147,483,647 or bigint if it is
9,223,372,036,854,775,807 and still use less bytes than decimal(17, 0) which uses 9 bytes.

So your ASP code (with the current stored proc and assuming it works correctly in SQL Analyzer) should look like this (untested):

function updateAssetInvcID(asstID, invcID, ascrID, assetNum, serial) {
     cmd = getStoredProc("sp_updateInvcID");
     cmd.Parameters.Append(cmd.CreateParameter("@asstID", 14, 1, 9, asstID)); //adDecimal = 14;
     cmd.Parameters("@asstID").Precision = 17;
     cmd.Parameters("@asstID").NumericScale = 0;
     cmd.Parameters.Append(cmd.CreateParameter("@invcID", 14, 1, 9, invcID)); //adDecimal = 14;    
     cmd.Parameters("@invcID").Precision = 17;
     cmd.Parameters("@invcID").NumericScale = 0;
     cmd.Parameters.Append(cmd.CreateParameter("@ascrID", 14, 1, 9, ascrID)); //adDecimal = 14;      
     cmd.Parameters("@ascrID").Precision = 17;
     cmd.Parameters("@ascrID").NumericScale = 0;
     cmd.Parameters.Append(cmd.CreateParameter("@assetNum", 200, 1, 30, assetNum));
     cmd.Parameters.Append(cmd.CreateParameter("@serial", 200, 1, 30, serial));          
     cmd.Execute();    
}

Notice, how I have also changed the size to the correct 9 bytes.  Incidently, Decimal (18, 0) also uses 9 bytes.

I hope this helps.  If not please advise error message that occurred and any changes to the code.
0
 

Author Comment

by:ShaymusBane2
Comment Utility
K...this is just getting frustrating now!

acperkins: I've blanket changed all of the decimal values in the database to integers...reverted all my asp code to integers, changed the precision of my stored procedure parameters to 4 as you recommended....and I'm STILL getting the same error?!?!

Whilst banging my head against the monitor, I happened to see something (in Access of all things) that I thought kinda 'odd'...when i'm working at my desk, rather than on the server,  I use MS Access to view the data, run simple queries, etc. etc...one of the things I noticed though, is the identity field for asset status (asss_id) comes up as a 'Long Integer' autonumber when I view the table design view...is it possible that THIS is why I'm getting all these errors???  Is it possible that the autonumber requires something different?  I'm truly at my wits end on this one :(

The same goes for invc_id...more background here...I have a stored procedure to create an invoice that is called before the aforementioned problematic stored procedure is called...

sp_createInvoice
--------------------
CREATE PROCEDURE sp_createInvoice
     @invoice varchar(30),
     @notes varchar(30),
     @invcID int output
 AS
     insert into invoices (invoice, notes) values (@invoice, @notes)
     select @invcID = @@identity
GO

ASP JScript to call stored Procedure
------------------------------------------
function createInvoice(invoice, notes) {
      cmd = getStoredProc("sp_createInvoice");
      cmd.Parameters.Append(cmd.CreateParameter("@invoice", 200, 1, 30, invoice));
      cmd.Parameters.Append(cmd.CreateParameter("@notes", 200, 1, 200, notes));
      cmd.Parameters.Append(cmd.CreateParameter("@invcID", 3, 2, 4));
      cmd.Execute();      
      return cmd.Parameters("@invcID");
} // *** Done and tested ***

ASP JScript which makes function call
--------------------------------------------
try {
   var invcID = receiveContract(cotrID, invoice, notes);
} catch (e) {
   Session("Error") = e.message;
   Response.Redirect("/miams/error/error.asp");
}

Is it possible that invcID is the problem?  Due to it being an autonumber or something??  As I said before, asss_id doesn't update either, but the asset status table (asss_id) also uses autonumber...?  Any thoughts?
0
 

Author Comment

by:ShaymusBane2
Comment Utility
Whoops...typo...in the stored procedure, I am accepting varchar(200) for notes...not (30)...
0
 

Author Comment

by:ShaymusBane2
Comment Utility
http://support.microsoft.com/?kbid=165156

Found this link...here are the results for the first stored procedure..

sp_updateInvcID
-------------------
PARAMETER NAME    DATA-TYPE    DIRECTION    DATA-SIZE
@RETURN_VALUE     3                   4                  0
@asstID                   3                   1                  0
@invcID                   3                   1                  0
@ascrID                   3                   1                  0
@assetNum             200                 1                 30
@serial                   200                 1                 30

Is it normal for the data-size to be 0?  I've ran this on a few of my stored procedures and they all claim a 0 data-size?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Yes, you can set integer size to 0.  But I suspect that is not the problem.  Even after changing it to 0 you still get the same problem, don't you?
0
 

Author Comment

by:ShaymusBane2
Comment Utility
See my solution on the other board...for the effort though, 500 pts to acperkins

Thanks again for helpin out!
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now