June 14, 2006 at 1:44 pm
Hi,
We just installed 64-bit SQL Server 2005 on our new Server having Windows Server 2003 x64 Enterprise Edition and having 2 AMD Opteron processors.
I detached our database from SQL Server 2000 to SQL Server 2005. It did some upgradation from version 539 to 611.
But the problem started now. One of our SPs which was executing perfectly fine in SQL 2000 has started giving this error in SQL 2k5:
Server: Msg 682, Level 22, State 153, Procedure _agr_Activity_view_insert, Line 10
Internal error. Buffer provided to read column value is too small. Run DBCC CHECKDB to check for any corruption.
Can someone help us here?
Regards,
RSingh
June 19, 2006 at 8:00 am
This was removed by the editor as SPAM
June 28, 2007 at 1:59 am
I had this error message for a SQLCommand I passed to a SQLDependency. The problem only occurred every time when some clients (always the same clients, but not for all clients) where logged into the database /SQL Server 2005 Express). The 2nd client connecting got the error when trying to read the result set.
The fix for the problem was to NOT pass any parameters to the SQLCommand (Instead I just parse the SQL statement the old fashioned way). I don't know why but since this change, we haven't seen this problem anymore.
December 17, 2007 at 6:06 pm
This is a bit of an old message but I was getting the same error after recently migrating to SQL2005 x64. It's apparently a problem to do with caching of temp tables and is fixed in the cumulative update package 2 for SP2
Here is a link with the details ... http://support.microsoft.com/kb/936305/LN/
Doug
August 20, 2008 at 2:00 pm
Even after applying cumulative hotfix #2 (post SP2) you still might see this error if you are using SELECT INTO to create some temp tables. If you instead create the table and merely perform an insert into, the error is not generated.
-Tony Clark-
June 9, 2009 at 11:14 am
I'm still getting this error. We're using SQL Dependency to cache some pretty straightfoward queries:
SELECT [t0].[AgentID] AS [AgentCode], [t1].[AgentID] AS [AgentGroupCode], UPPER([t0].[AgentName]) AS [AgentName], [t0].[AffiliateID] AS [affiliateId], [t0].[CashBackAgent] AS [IsCashbackAgent], [t0].[ShowMarketingCodeBox] AS [value], [t0].[MarketingCodeBoxShowPage] AS [value2]
FROM [dbo].[tblAgents] AS [t0]
INNER JOIN [dbo].[AgentGroup] AS [t1] ON ([t0].[AgentID]) = [t1].[AgentID]
WHERE [t0].[AgentName] = @p0
with this parameter begin passed through:
@p0 [String]: 'test'
This is the method doing the work, it's part of a LinqToSql extension method:
private static SqlCacheDependency RegisterCacheDependency(DataContext context, IQueryable query)
{
SqlCacheDependency dependency;
string[] tableNames = context.Mapping.GetTables().Select(t=>t.TableName).ToArray();
using (SqlConnection cn = new SqlConnection(context.Connection.ConnectionString))
{
cn.Open();
DbCommand dbCommand = context.GetCommand(query);
using (SqlCommand cmd = new SqlCommand(dbCommand.CommandText, cn))
{
foreach (DbParameter parameter in dbCommand.Parameters)
{
SqlParameter sparameter = new SqlParameter();
sparameter.DbType = parameter.DbType;
sparameter.Direction = parameter.Direction;
sparameter.IsNullable = parameter.IsNullable;
sparameter.ParameterName = parameter.ParameterName;
sparameter.Size = parameter.Size;
sparameter.SourceColumn = parameter.SourceColumn;
sparameter.SourceColumnNullMapping = parameter.SourceColumnNullMapping;
sparameter.SourceVersion = parameter.SourceVersion;
sparameter.Value = parameter.Value;
cmd.Parameters.Add(sparameter);
}
cmd.Notification = null;
cmd.NotificationAutoEnlist = true;
SqlCacheDependencyAdmin.EnableNotifications(context.Connection.ConnectionString);
foreach (var tableName in tableNames)
if (
!SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(
context.Connection.ConnectionString).Contains(tableName))
SqlCacheDependencyAdmin.EnableTableForNotifications(context.Connection.ConnectionString,
tableName);
dependency = new SqlCacheDependency(cmd);
cmd.ExecuteNonQuery();
}
}
return dependency;
}
It fails on executing the query with the buffer error, AFAIK everything seems OK on the C# side, everything works if I take out the code relating to the dependency. Any ideas on where I can go with this one? Just some ideas for investigation would be useful - the only suggestions I can find on Google are apply hotfix (SQL Server is on SP3) or not use parameters (not an option).
Cheers
Finn
June 10, 2009 at 7:01 am
I've made a little progress with this - it seems that when a NVarChar parameter is passed in the length of that parameter fixes the maximum length of the parameter. So for example, if I pass in a parameter with the value "test" then "tast" would work but not "testt".
So...I added a check in the code to see if the parameter is NVarChar and then add 100 to the parameter size and it goes through once I cycle SQL Server to clear its cache (is there a t-sql command to do this?). I also tried not setting the DbParameter.Size value but that had no effect.
Clearly this is a hack so does anyone know if I'm doing something wrong, is it a bug or am I missing something here?
Cheers
Finn
September 22, 2009 at 5:40 am
Hi all,
I'm having the same issue as finn.newick-948975. Any progress with this?
Many thanks,
Tim
January 3, 2010 at 9:17 pm
Try to rebuild the indexes ...
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply