Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Error SQL2k5sp2_64bit to SQL2ksp4_32bit

Author  Topic 

shawn.cox
Starting Member

9 Posts
Posted - 2008-08-05 : 11:54:05
Hi, We have got us a monster of a problem after updating our Production db servers to SQL 2005 sp2 64bit from SQL 2005 sp2 32bit.

The gist of the problem is that we receive the following error:

Receiving error: Msg 682, Level 22, State 148, Line 29
Internal error. Buffer provided to read column value is too small. Run DBCC CHECKDB to check for any corruption.

Executing t-sql update code across linked servers;
Code executing on sql 2005 sp2 cum7 x64
Updating table on sql 2000 sp4 x32


Copy of testing code:

UPDATE linkedserver.linkedtable.dbo.arcust
SET balance = balance
FROM dbo.tblInvoiceSummary AS wis WITH (NOLOCK)
INNER JOIN dbo.tblInvoiceCharges AS wic WITH (NOLOCK)
ON wic.whse_code = wis.whse_code
AND wic.invoice_no = wis.invoice_no
INNER JOIN dbo.tblWarehouse AS w WITH (NOLOCK)
ON w.ups_user_no = wis.whse_code
WHERE (w.group_code = ‘xxxx’ or wis.whse_code = 929929)
AND wis.invoice_posted_batch_no Is Null
AND wis.customer_no not in (500,510)
AND IsNull(wis.invoice_voided_batch_no, 0) <> IsNull(wis.invoice_batch_no, 1)
AND custno = library.dbo.padnumber(cast(wis.customer_no as varchar(6)),6)
and id_col = 1000

All tables have a clustered index.


-------------------------------------------------------------
It is important to note that this error does not occur if there is no data returned, only if there is at least one or more records returned.

We have confirmed this error occurs on several different SQL 2005 sp2 64bit servers to several different SQL 2000 sp3 and sp4 servers. The error occurs on SQL 2005 sp2 64bit CumUpdate 0, CumUpdate 2 and Cumupdate 7.

We have opened a case with Microsoft and they have no idea. They are attempting to recreate the issue in their labs but we get the feeling that they have seen the error before and are uninterested in working on it due to SQL 2000. We have found some reference to this error in google searches, but all those were resolved by CumUpdate 2 but it has not resolved our issue.

We are stuck with these SQL 2000 servers as it is the backend for our corporate accounting package Sage AccPac Pro Series. Believe me we wish they were gone, but so it goes with third party restraints.

We are able to work around the issue by hand executing the updates, but as you can imagine this has taken our batch processing from minutes to 3+ hours.

If anyone has any ideas, man we could really use them.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts
Posted - 2008-08-05 : 11:58:32
Longshot, but check the compatability level of the database, see if there is something for x64.

If you are using SSMS, right click on the DB, go to properties then select options. Set it to the correct version, then retry.

Otherwise I would start eliminating tables from your query to see if you could pinpoint which one is causing the problem. Once you can isolate which table, it may be easier to figure out.
Go to Top of Page

shawn.cox
Starting Member

9 Posts
Posted - 2008-08-05 : 12:06:10
Changing the compatibility mode on the sql2005 db's unfortunately isn't an option due to other interactions.

This accounting system is only a tiny part of our over all db system. Thus the reason we didn't back out to 32bit, opting instead to fight through the issue.

Thanks so much for the suggestion.
Go to Top of Page

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts
Posted - 2008-08-05 : 12:14:58
I would try hard coding a custno instead of using the function in order to test, and see if that works, if it does then the issue is likly related to what's being returned from the function, and we can take a look at that. Let me know if that works. Since the error only occurs during this update query, it makes me think that it's going to be a minor workaround (Like defining the custno field on the join with a cast or convert.)


UPDATE a 
SET a.balance = wis.balance
FROM 
	linkedserver.linkedtable.dbo.arcust a
Inner Join
	dbo.tblInvoiceSummary AS wis WITH (NOLOCK)
ON a.custno = library.dbo.padnumber(cast(wis.customer_no as varchar(6)),6)
--This line is also a likly cause to the issue
--Try this as well
-- ON a.custno = 'HARDCODE A CUSTNO'
--If it goes through, then we have just pinpointed the issue -
--and can probably resolve with
--looking into the function 
library.dbo.padnumber(cast(wis.customer_no as varchar(6)),6)
INNER JOIN 
	dbo.tblInvoiceCharges AS wic WITH (NOLOCK)
ON		wic.whse_code = wis.whse_code
AND		wic.invoice_no = wis.invoice_no
INNER JOIN 
		dbo.tblWarehouse AS w WITH (NOLOCK)
ON		w.ups_user_no = wis.whse_code
WHERE 
(w.group_code = ‘xxxx’ or wis.whse_code = 929929)
AND wis.invoice_posted_batch_no Is Null
AND wis.customer_no not in (500,510)
AND Coalesce(wis.invoice_voided_batch_no, 0) <> Coalesce(wis.invoice_batch_no, 1)
and a.id_col = 1000
Go to Top of Page

shawn.cox
Starting Member

9 Posts
Posted - 2008-08-05 : 12:21:55
I think that is just due to the DBA sanitizing it for me to post it in public. I'll see if I can get the actual code for you. This code has worked flawlessly on 32bit SQL 2005 for nearly two years. Only difference seems to be the 64bit version of SQL 2005.
Go to Top of Page

shawn.cox
Starting Member

9 Posts
Posted - 2008-08-05 : 12:34:08
Thanks I will get this to the DBA straight away and see what he thinks.

Thank You so much.
Go to Top of Page

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts
Posted - 2008-08-05 : 12:38:33
No problem, it sounds like a bug, but here's a guy that has a similar error and has found a solution (Sort Of)

http://www.shaunc.com/static/Internal-error--Buffer-provided-to-read-column-value-is-too-small_505.html
Go to Top of Page

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts
Posted - 2008-08-05 : 12:42:36
just curious, what happens if you run

Select *
FROM
linkedserver.linkedtable.dbo.arcust a
Inner Join
dbo.tblInvoiceSummary AS wis WITH (NOLOCK)
ON a.custno = library.dbo.padnumber(cast(wis.customer_no as varchar(6)),6)
library.dbo.padnumber(cast(wis.customer_no as varchar(6)),6)
INNER JOIN
dbo.tblInvoiceCharges AS wic WITH (NOLOCK)
ON wic.whse_code = wis.whse_code
AND wic.invoice_no = wis.invoice_no
INNER JOIN
dbo.tblWarehouse AS w WITH (NOLOCK)
ON w.ups_user_no = wis.whse_code
WHERE
(w.group_code = ‘xxxx’ or wis.whse_code = 929929)
AND wis.invoice_posted_batch_no Is Null
AND wis.customer_no not in (500,510)
AND Coalesce(wis.invoice_voided_batch_no, 0) <> Coalesce(wis.invoice_batch_no, 1)
and a.id_col = 1000


Does it work without issue?
Go to Top of Page

shawn.cox
Starting Member

9 Posts
Posted - 2008-08-05 : 12:51:24
Here is the straight unaltered code which fails.

Declare @batch_no32 varchar(6),
@arsess32 varchar(6),
@batch_no31 varchar(6),
@arsess31 varchar(6),
@batch_no44 varchar(6),
@arsess44 varchar(6),
@pcca_batchno int,
@group_code VARCHAR(4)

SET @group_code = 'PCCA'

UPDATE lbksvtestacct.CorporateSBT31.dbo.arcust
SET balance = balance + 0
FROM Balemaster.dbo.flWhseInvoiceSummary AS wis WITH (NOLOCK)
INNER JOIN Balemaster.dbo.flWhseInvoiceCharges AS wic WITH (NOLOCK)
ON wic.whse_code = wis.whse_code
AND wic.invoice_no = wis.invoice_no
INNER JOIN CustomerMaster.dbo.flWarehouse AS w WITH (NOLOCK)
ON w.ups_user_no = wis.whse_code
WHERE (w.group_code = @group_code or wis.whse_code = 929929)
AND wis.invoice_posted_batch_no Is Null
AND wis.customer_no not in (500,510)
AND IsNull(wis.invoice_voided_batch_no, 0) <> IsNull(wis.invoice_batch_no, 1)
AND custno = library.dbo.padnumber(cast(wis.customer_no as varchar(6)),6)
and id_col = 1000
Go to Top of Page

shawn.cox
Starting Member

9 Posts
Posted - 2008-08-05 : 12:53:29
quote:
Originally posted by Vinnie881

No problem, it sounds like a bug, but here's a guy that has a similar error and has found a solution (Sort Of)

http://www.shaunc.com/static/Internal-error--Buffer-provided-to-read-column-value-is-too-small_505.html


Yeah, we saw this one. We are pretty sure it's a bug too(and a bug that MS may not plan to fix), we are just scrambling looking for a way to work around it.
Go to Top of Page

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts
Posted - 2008-08-05 : 13:03:27
Can you try this and let me know if it runs without error


Select a.*
FROM 
lbksvtestacct.CorporateSBT31.dbo.arcust a
inner join
Balemaster.dbo.flWhseInvoiceSummary AS wis WITH (NOLOCK)
on a.custno = library.dbo.padnumber(cast(wis.customer_no as varchar(6)),6)
INNER JOIN Balemaster.dbo.flWhseInvoiceCharges AS wic WITH (NOLOCK)
ON wic.whse_code = wis.whse_code
AND wic.invoice_no = wis.invoice_no
INNER JOIN CustomerMaster.dbo.flWarehouse AS w WITH (NOLOCK)
ON w.ups_user_no = wis.whse_code
WHERE (w.group_code = 'PCCA' or wis.whse_code = 929929)
AND wis.invoice_posted_batch_no Is Null
AND wis.customer_no not in (500,510)
AND IsNull(wis.invoice_voided_batch_no, 0) <> IsNull(wis.invoice_batch_no, 1)
and id_col = 1000
[code]

if it does try

[code]
Select a.*, a.Balance + 0
FROM 
lbksvtestacct.CorporateSBT31.dbo.arcust a
inner join
Balemaster.dbo.flWhseInvoiceSummary AS wis WITH (NOLOCK)
on a.custno = library.dbo.padnumber(cast(wis.customer_no as varchar(6)),6)
INNER JOIN Balemaster.dbo.flWhseInvoiceCharges AS wic WITH (NOLOCK)
ON wic.whse_code = wis.whse_code
AND wic.invoice_no = wis.invoice_no
INNER JOIN CustomerMaster.dbo.flWarehouse AS w WITH (NOLOCK)
ON w.ups_user_no = wis.whse_code
WHERE (w.group_code = 'PCCA' or wis.whse_code = 929929)
AND wis.invoice_posted_batch_no Is Null
AND wis.customer_no not in (500,510)
AND IsNull(wis.invoice_voided_batch_no, 0) <> IsNull(wis.invoice_batch_no, 1)
and id_col = 1000
Go to Top of Page

shawn.cox
Starting Member

9 Posts
Posted - 2008-08-05 : 14:01:53
quote:
Originally posted by Vinnie881

Can you try this and let me know if it runs without error


Select a.*
FROM 
lbksvtestacct.CorporateSBT31.dbo.arcust a
inner join
Balemaster.dbo.flWhseInvoiceSummary AS wis WITH (NOLOCK)
on a.custno = library.dbo.padnumber(cast(wis.customer_no as varchar(6)),6)
INNER JOIN Balemaster.dbo.flWhseInvoiceCharges AS wic WITH (NOLOCK)
ON wic.whse_code = wis.whse_code
AND wic.invoice_no = wis.invoice_no
INNER JOIN CustomerMaster.dbo.flWarehouse AS w WITH (NOLOCK)
ON w.ups_user_no = wis.whse_code
WHERE (w.group_code = 'PCCA' or wis.whse_code = 929929)
AND wis.invoice_posted_batch_no Is Null
AND wis.customer_no not in (500,510)
AND IsNull(wis.invoice_voided_batch_no, 0) <> IsNull(wis.invoice_batch_no, 1)
and id_col = 1000
[code]

if it does try

[code]
Select a.*, a.Balance + 0
FROM 
lbksvtestacct.CorporateSBT31.dbo.arcust a
inner join
Balemaster.dbo.flWhseInvoiceSummary AS wis WITH (NOLOCK)
on a.custno = library.dbo.padnumber(cast(wis.customer_no as varchar(6)),6)
INNER JOIN Balemaster.dbo.flWhseInvoiceCharges AS wic WITH (NOLOCK)
ON wic.whse_code = wis.whse_code
AND wic.invoice_no = wis.invoice_no
INNER JOIN CustomerMaster.dbo.flWarehouse AS w WITH (NOLOCK)
ON w.ups_user_no = wis.whse_code
WHERE (w.group_code = 'PCCA' or wis.whse_code = 929929)
AND wis.invoice_posted_batch_no Is Null
AND wis.customer_no not in (500,510)
AND IsNull(wis.invoice_voided_batch_no, 0) <> IsNull(wis.invoice_batch_no, 1)
and id_col = 1000



Our DBA reports that both of these queries run just fine.
Go to Top of Page

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts
Posted - 2008-08-05 : 14:07:50
One final test to pinpoint


Update a
set a.Balance = a.Balance
FROM 
lbksvtestacct.CorporateSBT31.dbo.arcust a
inner join
Balemaster.dbo.flWhseInvoiceSummary AS wis WITH (NOLOCK)
on a.custno = library.dbo.padnumber(cast(wis.customer_no as varchar(6)),6)
INNER JOIN Balemaster.dbo.flWhseInvoiceCharges AS wic WITH (NOLOCK)
ON wic.whse_code = wis.whse_code
AND wic.invoice_no = wis.invoice_no
INNER JOIN CustomerMaster.dbo.flWarehouse AS w WITH (NOLOCK)
ON w.ups_user_no = wis.whse_code
WHERE (w.group_code = 'PCCA' or wis.whse_code = 929929)
AND wis.invoice_posted_batch_no Is Null
AND wis.customer_no not in (500,510)
AND IsNull(wis.invoice_voided_batch_no, 0) <> IsNull(wis.invoice_batch_no, 1)
and id_col = 1000



The above update will not change any data in your fields but it is still best to run it on sample data (It is updating it's value with it's value, so it shouldn't change anything). If the above update statement works, then your issue can likly be fixed with a
Convert statment.

Please provide me with the Column type of (i.e. Varchar(10), Money, Decimal(12,4), etc)

lbksvtestacct.CorporateSBT31.dbo.arcust.Balance

and I can provide you with the syntax that should resolve (Hopefully).
Go to Top of Page

shawn.cox
Starting Member

9 Posts
Posted - 2008-08-05 : 15:11:05
quote:
Originally posted by Vinnie881

One final test to pinpoint


Update a
set a.Balance = a.Balance
FROM 
lbksvtestacct.CorporateSBT31.dbo.arcust a
inner join
Balemaster.dbo.flWhseInvoiceSummary AS wis WITH (NOLOCK)
on a.custno = library.dbo.padnumber(cast(wis.customer_no as varchar(6)),6)
INNER JOIN Balemaster.dbo.flWhseInvoiceCharges AS wic WITH (NOLOCK)
ON wic.whse_code = wis.whse_code
AND wic.invoice_no = wis.invoice_no
INNER JOIN CustomerMaster.dbo.flWarehouse AS w WITH (NOLOCK)
ON w.ups_user_no = wis.whse_code
WHERE (w.group_code = 'PCCA' or wis.whse_code = 929929)
AND wis.invoice_posted_batch_no Is Null
AND wis.customer_no not in (500,510)
AND IsNull(wis.invoice_voided_batch_no, 0) <> IsNull(wis.invoice_batch_no, 1)
and id_col = 1000



The above update will not change any data in your fields but it is still best to run it on sample data (It is updating it's value with it's value, so it shouldn't change anything). If the above update statement works, then your issue can likly be fixed with a
Convert statment.

Please provide me with the Column type of (i.e. Varchar(10), Money, Decimal(12,4), etc)

lbksvtestacct.CorporateSBT31.dbo.arcust.Balance

and I can provide you with the syntax that should resolve (Hopefully).





Vinnie,
Our DBA reports that this single row update worked, but notes that we have done several things this afternoon from re-registering dll's to running some 2000 sql scripts a second time. We are still failing on multiple row updates.

balance is defined as such:
balance] [money] NULL CONSTRAINT [df_arcust_BALANCE] DEFAULT (0),

--Shawn
Go to Top of Page

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts
Posted - 2008-08-05 : 15:20:28
let's rule out all scenerios
try this query. THis is using the assumption you want to update the linked table with the balance from wis.balance

I am not certain why the "+ 0" was in the original code, if there is a null value returned, it would error it out if the column didn't accept nulls. if you want to use that still (Not sure why you would want to), but you can replace the below update query set line with this part below.


coalesce(wis.Balance,0) + 0, but it will not do anything.


Otherwise run this
Let me know if this works.

Update a
set a.Balance = cast(coalesce(wis.Balance,0) as money)
FROM 
lbksvtestacct.CorporateSBT31.dbo.arcust a
inner join
Balemaster.dbo.flWhseInvoiceSummary AS wis WITH (NOLOCK)
on a.custno = library.dbo.padnumber(cast(wis.customer_no as varchar(6)),6)
INNER JOIN Balemaster.dbo.flWhseInvoiceCharges AS wic WITH (NOLOCK)
ON wic.whse_code = wis.whse_code
AND wic.invoice_no = wis.invoice_no
INNER JOIN CustomerMaster.dbo.flWarehouse AS w WITH (NOLOCK)
ON w.ups_user_no = wis.whse_code
WHERE (w.group_code = 'PCCA' or wis.whse_code = 929929)
AND wis.invoice_posted_batch_no Is Null
AND wis.customer_no not in (500,510)
AND IsNull(wis.invoice_voided_batch_no, 0) <> IsNull(wis.invoice_batch_no, 1)
and id_col = 1000
and isnumeric(wis.Balance) = 1
Go to Top of Page

shawn.cox
Starting Member

9 Posts
Posted - 2008-08-05 : 20:39:14
We found resolution to this problem. In our attempts to resolve we only applied some of the scripts referenced in this kb articlehttp://support.microsoft.com/kb/906954

When we went back and applied the entire install script set the error was resolved.

I appreciate all your help in the code samples. You have given our developers some good ideas to go on. Thanks Again,
--Shawn
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts
Posted - 2008-08-05 : 20:46:35
You have to install instcat.sql or use openquery.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site