Strange MS SQL happenings

irlrobins

Hey all,

I've been trying to fix this problem in some existing code for a couple of days and it's really got me stumped. So I'm hoping someone will be able to spot what the problem is.

Setup in production is a website running aspx connecting to a MS SQL server to return data according to the actions of the user. All was working fine until last couple of weeks when we were unable to run the end of month procedure. No changes had been made to the code in the meantime.

I set up the following development environment locally to test and replicate the problem.

SQL: MS SQL Server 2005
IDE: Visual Studio .Net 2003

Aspx pages are run off ISS component of XP Pro SP2.

The error that the aspx page is returning is this:

System.Data.SqlClient.SqlException: Error converting data type nvarchar to numeric. 
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) 
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() 
at Admin.Monthend.BtnSubmit_Click(Object sender, EventArgs e) in c:\inetpub\wwwroot\Admin\Monthend.aspx.vb:line 493

The relevant piece of code where the exception is being thrown is this:

[PHP]Dim strConn As String = ConfigurationSettings.AppSettings("connectionstring")
Dim con As SqlConnection = New SqlConnection(strConn)
Dim SQL As String = "sp_Monthendclose"
Dim Cmd As New SqlCommand(SQL, con)
Cmd.CommandType = CommandType.StoredProcedure

Dim param As SqlParameter


Dim paramOut As New SqlParameter("@Output", SqlDbType.Int)
paramOut.Direction = ParameterDirection.Output
Cmd.Parameters.Add(paramOut)

param = Cmd.Parameters.Add("@ReportArray", SqlDbType.Text)
Cmd.Parameters("@ReportArray&quot.Value = strtemp


Try
con.Open()
Cmd.ExecuteNonQuery()
result = paramOut.Value
If result = 1 Then
alert("1")
ElseIf result = 2 Then
alert("2")
ElseIf result = 3 Then
alert("3")
ElseIf result = 0 Then
alert("The month was successfully completed")
End If

Catch ex As Exception
Response.Write(ex)
Finally
con.Close()
End Try[/PHP]

with line 493 being Cmd.ExecuteNonQuery().

The code for the stored procedure being called is this:

[PHP]USE [DB]
GO
/****** Object: StoredProcedure [dbo].[sp_MonthEndClose] Script Date: 08/28/2007 08:43:44 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER procedure [dbo].[sp_MonthEndClose]
(
@ReportArray text,
@output int output
) as

begin tran
DECLARE @intPointer int
DECLARE @FS int, @OLEResult int, @FileID int

EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0 PRINT 'Scripting.FileSystemObject'

EXECUTE @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, 'd:\testsql.txt', 8, 1
IF @OLEResult <> 0 PRINT 'OpenTextFile'

EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @ReportArray
IF @OLEResult <> 0 PRINT 'WriteLine'

EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS


exec sp_xml_preparedocument @intPointer OUTPUT, @ReportArray
Insert into tblmonthenddetails


Select * from OpenXml(@intPointer,'/Details/LineItem',1)
With (Registrationno int,Detailid int,Monthend int, Total decimal(18,2),Weight decimal(18,5), Quantity int)

exec sp_xml_removedocument @intPointer


@error <> 0
begin
rollback
set @output= 3
return @output
--'Error: Unable to update Month End Details Table'
end
set @output= 0
commit tran
RETURN @output
[/PHP]

Table structure for tblmonthenddetails is as follows:

[PHP]USE [DB]
GO
/****** Object: Table [dbo].[TblMonthEndDetails] Script Date: 08/28/2007 09:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblMonthEndDetails](
[Registrationno] [int] NOT NULL,
[Detailid] [int] NOT NULL,
[Monthend] [int] NOT NULL,
[Total] [decimal](18, 2) NULL,
[Weight] [decimal](18, 5) NULL,
[Quantity] [int] NULL,
CONSTRAINT [PK_TblMonthEndDetails] PRIMARY KEY CLUSTERED
(
[Registrationno] ASC,
[Detailid] ASC,
[Monthend] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[TblMonthEndDetails] WITH CHECK ADD CONSTRAINT [FK_TblMonthEndDetails_TblMonthEnd] FOREIGN KEY([Monthend])
REFERENCES [dbo].[TblMonthEnd] ([DateIndex])
GO
ALTER TABLE [dbo].[TblMonthEndDetails] CHECK CONSTRAINT [FK_TblMonthEndDetails_TblMonthEnd]
GO
ALTER TABLE [dbo].[TblMonthEndDetails] WITH CHECK ADD CONSTRAINT [FK_TblMonthEndDetails_TblRegister] FOREIGN KEY([Registrationno])
REFERENCES [dbo].[TblRegister] ([RegistrationNo])
GO
ALTER TABLE [dbo].[TblMonthEndDetails] CHECK CONSTRAINT [FK_TblMonthEndDetails_TblRegister][/PHP]


The error I'm 99.9% certain is occuring on the "Insert into tblmonthenddetails
Select * from OpenXml(@intPointer,'/Details/LineItem',1)
With (Registrationno int,Detailid int,Monthend int, Total decimal(18,2),Weight decimal(18,5), Quantity int)" line.

Now a couple of things occurred to me that might be causing the problem:
1) The data being passed to the SP (@ReportArray) contains non-numeric characters. But I've examined what is being passed (I print ReportArray to the debug window) and it doesn't contain any non numeric characters
2) The data is badly formed. Again on examination the data is in proper xml syntax.
3) The data is being truncated on being passed. As you can see from the SP code I write the input to the SP to a file and on comparasion they match.
4) SqlDbType.Text is too small to hold ReportArray. I don't believe this to be true as ReportArray is <300,000 characters and the size limit of SqlDbType.Text is ~ 2,000,000.

So I'm stumped as to how this convert error is occuring! I've attached what ReportArray in a text file.

Any help or tips welcome!

seamus

That is a big file

Have you tried shortening the input to just one or a handful of items? i.e.

<Details><LineItem Registrationno="169" Detailid="56427" Monthend="25" Total="0" Weight="7696.97" Quantity="9213"></LineItem></Details>

If it'll parse one or two items, then it's likely there's a problem in the input.

irlrobins

Yes, I can run the code when I have ~75 LineItems. After that, it craps out. But from reading up on OpenXML, people seem to indicate it can handle files of size > 10Mb. My xml is only ~250Kb.

amen

a few things
1: you shouldn't be naming your sps with a sp_. These are normally system sps and may cause confusion
2: what service pack are you running
3: When you say you can process ~75 lines when you have processed these have ~75 lines have you addedd an additional ~75 lines and see if the processess? If so keep adding till you get a problem.
Then take the last added lines and process on their own if there is still a problem then you know its data related and you can narrow it down to the individual line.

If the problem doesn't occur then remove the last 75 and add one line a time and process after each. This way you should get the error when you added n lines.

At this point create another file with n lines and see if you get the same error.
If so you now have a reproducable error and prehaps you could report to MS.

I will try and have a look at this later tonight.

irlrobins

1) Noted, but I didn't create any of this code, so naming conventions or lack thereof not my fault.

2) Net Studio is SP1, SQL Server is SP2

3) As far as I can remember, it worked when I had 77 lines, but 78 would break it. And that seemed to occur regardless of what 78 lines I used. I can't see any reason why one particular line could be at fault as the xml is built up by code so if one line was improperly formatted I'd expected every line to be the same.

Thanks for your help, I'll look fwd to see how u get on tonight.

seamus

Yeah, amen's no. 3 was pretty much what I was going to suggest. I've managed to recreate the issue as exactly you described, but the number of lines is giving the management studio hassle, so it's hard to do anything quickly. I might take it 200 lines at a time and see if that works for me.

Edit: Just saw your post there. Hmm.

Ginger

First off..

Run a trace on the DB using the SQL Profiler and see what is been sent to the db and what the db is returning... It may be a case of an error happening and the error code cannot be converted..

Use the DB id as the selecting factor so that you dont have to trawl through the output.

As for Amens naming scheme, I wouldnt agree with that, i would always say go with company policy :P

The output is in an int format so that possibly is where its going wrong...

irlrobins

I'm thinking that possibly OpenXML can't handle such a large XML structure. So I'm going to see if I can get the ASP to write the XML data to a file and then get the stored procedure to open this file and use Bulk Load to load it into the table.

Ginger:

I'll try DB trace now and see.

The output is in an int format so that possibly is where its going wrong...

Not quite sure why you think this, can you explain?

Ginger

I am looking at why you would get a conversion issue on a proc that normally works...

What i am trying to determine is what the db is spitting back from the stored proc..

seamus

I've figured out that it's the "Weight" attribute that's causing the problem - the error is occuring on the INSERT (the data is being extracted from the XML file fine).

What I did was modify the table so that all columns were varchar. Then I ran the stored procedure for the full XML input. Presto, went in fine. Then I altered each column in turn, to the correct data type. It all went fine till I hit the Weight column, then it went pear-shaped. I couldn't see any reason why it would, but it just did. I was getting the same error - "cannot convert nvarchar to numeric".

Through all sorts of messing, it turned out, that this statement managed to cast the data into the correct data type:

SELECT Weight, CAST(CAST(Weight as float) as decimal(18,5))
FROM TblMonthEndDetails

So for some reason or other, you can't convert the column to decimal(18,5), but you can convert it to float, and from there, you can convert from float to decimal(18,5).

Odd. If you modify your stored procedure to account for this (extract Weight as float instead of decimal), then MSSQL will perform the cast when it inserts the value into the DB. Really odd, but I tested it here, and it works.

irlrobins

seamus wrote:

If you modify your stored procedure to account for this (extract Weight as float instead of decimal), then MSSQL will perform the cast when it inserts the value into the DB.

Cheers for taking the time to look into this. As I said it had me stumped.

So what you're saying is use

[PHP]Insert into tblmonthenddetails
Select * from OpenXml(@intPointer,'/Details/LineItem',1)
With (Registrationno int,Detailid int,Monthend int, Total decimal(18,2),Weight float(24), Quantity int)[/PHP]

in place of what I had above?

seamus

Just
Weight float
Should work. Can you specify a length for float values?

I'm not an expert on SQL data types - maybe someone could advise if there's any chance of losing precision/digits from casting to float before decimal?

irlrobins

Fecking works and all.

Float == Float(53). At first glance doesn't appear to be any loss in accuracy. But I'll check. Thanks a million for this!!!

Now lets hug.

From MDSN:

Syntax

float [ ( n ) ]

Where n is the number of bits that are used to store the mantissa of the float number in scientific notation and, therefore, dictates the precision and storage size. If n is specified, it must be a value between 1 and 53. The default value of n is 53.
n value Precision Storage size

1-24 7 digits 4 bytes

25-53 15 digits 8 bytes

mewso

Ginger wrote:

As for Amens naming scheme, I wouldnt agree with that, i would always say go with company policy :P

It's not good practice to name procedures with sp_ because sql server will assume it's a system procedure and search the master db for it before looking at your customer procedures. So it's not a naming convention thing it's a performance issue.

seamus

musician wrote:

It's not good practice to name procedures with sp_ because sql server will assume it's a system procedure and search the master db for it before looking at your customer procedures. So it's not a naming convention thing it's a performance issue.

^^ What he said.

You may also get some inexperienced coder or DBA who attempts to port your code or database somewhere and can't figure out why the sp_ procedure won't work for him.

irlrobins

Point taken in regards the stored procedure naming. But as I said, none of this code was created by myself.

Someone in work asked the question why did it work previously as Decimal(18.5) until now and how does using float(24) make a difference. The only thing that I can think of now is that a decimal would require 8bytes while a float would only require 4 bytes (from what I've read). SO if this months data was abnormally large the OpenXMl just couldn't handle it.

Anyone else got an idea?