Fatal Error 682 and SqlCacheDependency

Hello,

I have tried two ways for executing a query and creating a dependency
on it, one using plain Sql commands, the other using Enterprise
Library wrapped commands.

I keep getting:

Warning: Fatal error 682 occurred at Oct 12 2007 11:01AM. Note the
error and time, and contact your system administrator.

string xml = cmd.ExecuteScalar() as string;

When I execute it. Does anyone know what could cause this.

I have read both of these posts and have not yet been able to
investigate the SQL machine's event viewer though:

http://forums.asp.net/p/959871/1188606.aspx#1188606

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1203630&SiteID=1

The "value" column is an XML datatype field.

Thanks,
Josh

public static ContentXmlUtilResult GetContentXml(
string key,
bool createDependency)
{
ContentXmlUtilResult result = new ContentXmlUtilResult();

string connString = Global.Current.ConnectionString;

using (SqlConnection conn =
new SqlConnection(connString))
{
SqlCommand cmd =
new SqlCommand(
"SELECT Value FROM dbo.ContentXml WHERE [Key]
= '" + key + "'",
conn);

if (createDependency)
{
System.Web.Caching.SqlCacheDependency dependency =
new SqlCacheDependency(cmd);

result.Dependency = dependency;
}

conn.Open();

string xml = cmd.ExecuteScalar() as string;

conn.Close();

result.Content = xml;
}

return result;

//SqlCommand cmd = (SqlCommand)
// Global.Current.Database.GetSqlStringCommand(
// //@."SELECT Value FROM dbo.ContentXml WHERE [Key] =
@.Key");
// @."SELECT Value FROM dbo.ContentXml WHERE [Key] = '"
+ key + "'");

////cmd.Parameters.Add("@.Key", SqlDbType.VarChar).Value =
key;

//if (createDependency)
//{
// System.Web.Caching.SqlCacheDependency dependency =
// new System.Web.Caching.SqlCacheDependency(cmd);

// result.Dependency = dependency;
//}

//string xml = Global.Current.Database.ExecuteScalar(cmd)
as string;

//result.Content = xml;

//return result;
}

However bizarre this may sound, I solved this problem by changing the length of the key to 15 charcters. I was using SystemStatusContent as the value, so the SQL statement was:

SELECT Value FROM dbo.ContentXml WHERE [Key] = 'SystemStatusContent'

But, when I changed the key to a smaller value, it now works fine!

SELECT Value FROM dbo.ContentXml WHERE [Key] = 'SystemStatus'

I do not know why this fixed it. I'm just glad it did.

Josh

Failure Workflow Does Not Fire

Hello,
I have a SQL Server 2000 DTS package in which the first step executes a batch file. The batch file contains FTP commands that log into an FTP server, and pull down whatever file is there.

I set up a failure workflow to send an email if the step fails. When I have a SQL Server job run this package, and there is no file to dowload, the whole package fails without the failure workflow result firing.

For the step (DTSStep_DTSCreateProcessTask_1), I have the 'FailPackageOnError' property set to -1. In the package properties, I have the check box for 'Fail Package on First Error' cleared.

What do I need to do so that the failure workflow occurs when the step fails?

Thank you for your help!

cdun2Are you sure the fact that no file is there will cause the step to error out?|||u need to trap errors from external programs like bat files to have the failed workflow activated. aslo not all external programs returns error code to the calling application. for bat files, u will have to set errorlevel to make the calling program understand the success/failure

for example when using xp_cmdshell, this will call the failure workflow, if present

declare @.err int
exec @.err = master..xp_cmdshell 'C:\xx.bat'
if @.err = 1
RAISERROR ('err',16,1)

the above code will not fire the failure workflow if u just execute
exec master..xp_cmdshell 'C:\xx.bat'
and even if the xx.bat is not present in folder C:\|||Thank you for your help!
cdun2|||Another option is to create an operator when you scheduled the job and an e-mail will be sent out if the job fails.

Good luck