Sql exceutes in Query Analyzer but not in .Net

[ralphtrent]

Hello
I have some sql that executes fine in Microsofts Query Analyzer, but when I execute the same sql within the SqlClient .net class, its throwing an exception. I have no clue why and it is driving me nuts. How can sql that parses and works fine in Sql 2000 tools, fail in the .net class? I would post the sql, but that is not the problem. The sql in this method is very similar to sql in other functions and they do not give me a problem. So has anyone ever experienced this before?

 

[stevexff]

What exception? It would be helpful if you posted the code, the exception, AND the SQL. Otherwise you aren't giving us much to work with here...

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[JurkMonkey]

Did you select the default schema (DB) first before calling the SQL?

 

[wawalter]

I've had that problem and found I was using different logins for QA and for the .Net program and they logins had different rights. I changed the QA login to match the .Net program login and found the problem right away.


Bill

 

[ralphtrent]

stevexff, i know where the code seems to be faulty, according to dot net but not sql, I was just wondering if anyone else has had this happen before.

Thanks to all.

 

[JurkMonkey]

We can only guess if we can't see any code.

It's like saying "I know the car doesn't go when I press the gas" but the question we need answered is, did you start the car and put it in gear?

 

[Aptitude]

I was just wondering if anyone else has had this happen before.

YES!

 

[AlexCuse]

Most likely you are not building the SQL correctly on the .net side. Why do you refuse to share it?

Ignorance of certain subjects is a great part of wisdom

 

[ralphtrent]

I do not refuse to share, I was not just in the office today so I could not get to the code. I will post it tomorrow for review and tips.

 

[ralphtrent]

Here is my code for your revie. First my method that gets the data, then the connection class method

public DataTable GetIncidentDetails_LastNotes(string UserName, DateTime StartDate, DateTime EndDate, bool PublicOnly)
{
	SqlCommand objComm = new SqlCommand();
	DataAccess da = new DataAccess();
	DataTable dtTempIDN = null;
	try
	{
		objComm.CommandText = "select 'CustomerId' = i.iOwnerId, 'IncidentId' = i.iIncidentId, 'AssignedTo' = u.chUserName, 'UIDAssignedTo' = i.chAssignedTo, 'UIDInsertedBy' = i.chInsertBy, 'IssueDescription' = isnull(i.vchDesc1,'') + isnull(i.vchDesc2,''), 'Customer' = co.vchCompanyName, 'ContactId' = i.iContactid, 'CustomerContact' = cu.vchIndividualName, 'Status' = rp1.vchParameterDesc, 'Priority' = rp2.vchParameterDesc, 'NotesSeq' = iSeqNum, 'Notes1' = isnull(iwn.vchWorkNote1,''), 'Notes2' = isnull(iwn.vchWorkNote2,''), 'Notes3' = isnull(iwn.vchWorkNote3,''), 'Notes4' = isnull(iwn.vchWorkNote4,''), 'Notes5' = isnull(iwn.vchWorkNote5,''), 'InsertDate' = i.dtInsertDate, 'UpdateDate' = i.dtUpdateDate, i.iStatusId, 'PriorityId' = i.iPriorityId, 'DaysOpen' = case when i.iStatusid not in (104,101775,101776,101587) then datediff(d,i.dtInsertDate,GetDate()) else '0' end, 'DaysLastUpdated' = case when i.iStatusid not in (104,101775,101776,101587) then datediff(d,i.dtUpdateDate,GetDate()) else '0' end, 'Resolution' = isNull(res.vchUserExtension1,'') + isNull(res.vchUserExtension2,'') + isNull(res.vchUserExtension3,'') + isNull(res.vchUserExtension4,'') + isNull(res.vchUserExtension5,''), 'ProductGroup' = i.vchUser1, 'Module' = i.vchUser2 from vIncident i, vIncidentWorkNote iwn, vUsers u, vCustomer cu, vCompany co, vReferenceParameters rp1, vReferenceParameters rp2, AATZ_vCSuExtensionRecord res " +
		"where (" + 
		"(i.dtInsertDate between @StartDate and @EndDate) " + 
		"or (i.dtUpdateDate between @StartDate and @EndDate and i.iStatusId in (104, 101776) " +
		//This is the code that is giving me the grief.  I have this sql else where in .net and it works
		"and convert(char(8), convert(datetime, i.vchUser8),112) " + 
		"<= convert(char(8), convert(datetime, i.dtUpdateDate),112)) " + 
		")"+
		"or (i.iStatusId not in (104,101776) and i.dtInsertDate < @StartDate) " + 
		") " + 
		"and u.chUserId = i.chAssignedTo and i.iContactId = cu.iCustomerId and i.iOwnerId = co.iCompanyId and rp1.iParameterId = i.iStatusId and rp2.iParameterId = i.iPriorityId and (i.chAssignedTo = @UserName or i.chInsertBy = @UserName ) "+ 
		"and iWorknoteId = (select max(iWorknoteId) from vIncidentWorkNote where iIncidentId = i.iIncidentId) and ";

		if (PublicOnly)
		{
			objComm.CommandText += " iPublishBitMask in (1,2) ";
		}
		else
		{
			objComm.CommandText += " iPublishBitMask = 0 ";
		}
		objComm.CommandText += "and i.iIncidentId *= res.iSystemId order by i.iIncidentId, iwn.iSeqNum";
	
		objComm.Parameters.Add("@UserName",UserName);
		objComm.Parameters.Add("@StartDate",_StartDate);
		objComm.Parameters.Add("@EndDate",_EndDate);
		dtTempIDN = da.GetData(objComm);
	}
	catch{throw;}
	finally{objComm = null; da = null;}
	return dtTempIDN;
}

public DataTable GetData(string sql)
{
	SqlConnection objConn = new SqlConnection(_strConnString);
	SqlCommand objComm = new SqlCommand();
	SqlDataAdapter objDA = new SqlDataAdapter();
	DataTable dt = new DataTable();
	objComm.CommandText = sql;
	objComm.CommandType = CommandType.Text;
	objComm.Connection = objConn;
	objComm.CommandTimeout = iCommandTimeout;
	objDA.SelectCommand = objComm;
	if (objConn.State == ConnectionState.Closed)
	{
		objConn.Open();
	}
	try
	{
		Onyx.currentSql = sql;
		if (bExecute)
		{
			objDA.Fill(dt);
			return dt;
		}
		else
		{
			bExecute = true;
			return new DataTable();
		}
	}
	catch (SqlException ex)
	{
		throw ex;
	}
	catch (Exception ex)
	{
		throw ex;
	}
	finally
	{
		objComm = null;
		objDA = null;
		objConn.Close();
		objConn = null;
	}
}

 

[AlexCuse]

I don't have C# at work, can you post the code you are running in SQL Server client tools though? I don't have time to build the statement you've got there.

I think you would be better served (for a variety of reasons) to put the query that works in Query Analyzer into a stored procedure saved on your database side, that can accept parameters from your front end. This gives you easier code maintenance, less network traffic, and better performance in the database.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom