January 2, 2007 at 2:21 pm
Hi - my problem is as follows: I'm writing an ASP.Net 2.0 app with C#. From the app, I call a stored procedure that updates some data in the app's database (SQL Server 2000). This stored procedure then declares and assigns a value to a variable of type nvarchar(4000) and calls a 2nd stored procedure, passing it, among other things, the newly created variable. The 2nd stored procedure performs some updates to a different SQL Server 2000 database, one which comes with a 3rd party app we're using. The variable that's passed to the 2nd stored procedure is supposed to be inserted into a text field in a table in the 3rd party database. If I run the stored procedure(s) from Query Analyzer, there's no problem. When the sp's run from my .Net app, though, the text field doesn't get inserted if it's longer than about 90 characters. This happens whether I try to perform the insert with an INSERT, a WRITETEXT or an UPDATETEXT statement. The INSERT statement isn't failing because it inserts more than this one variable; it succesfully creates a new record and inserts various other variable values into various other fields (not of type text). But the text field in the new record always shows blank if it's over 90 characters long. Since the variable is created in the 1st sp, I don't think this is a problem with my .Net app (which, according to the debugger, is passing the 1st sp's parameters correctly), but I'm wondering if there may be a SQL setting that's different between the .Net framework and what's native to Query Analyzer. I've set TEXTSIZE to 2GB in both sp's, with no difference in result. Are there any other settings that could affect how much text can be put into a text field? Also, is there a way I can step through these sp's as they run to see what the sp's think this variable value is as it's running? I'm stumped.
Thanks!
January 2, 2007 at 6:13 pm
You can debug your SPs thru Visual Studio. You can then see the values of the variables being passed.
January 2, 2007 at 6:23 pm
Looks like some implicit conversion involved, e.g. when using ISNULL or CHARINDEX.
But without some relevant information cannot make any conclusions.
_____________
Code for TallyGenerator
January 3, 2007 at 7:41 am
What kind of information would you like? I can send you the scripts, but not sure that's what you're looking for. Please advise; I'm desperate!
Thanks!
January 3, 2007 at 3:56 pm
Can you post the definiton of the first stored procedure and the .Net code that calls it ?
* Noel
January 4, 2007 at 8:23 am
Here's the first stored procedure (look for highlighting for where the problem variable gets created and then passed to the second stored procedure):
-----------------------------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE [dbo].[modParams]
@OrderID as int,
@LastName as nvarchar(50),
@FirstName as nvarchar(50),
@Phone as nvarchar(50),
@BLastName as nvarchar(50),
@BFirstName as nvarchar(50),
@BPhone as nvarchar(50),
@EmployeeID as nvarchar(6),
@EFirstName as nvarchar(50),
@ELastName as nvarchar(50),
@EDepartment as nvarchar(30),
@ETitle as nvarchar(30),
@EStatus as nvarchar(50),
@EPosition as nvarchar(50),
@EDateBegin as nvarchar(15),
@ELocation as nvarchar(30),
@OpenBy as nvarchar(6)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
--UPDATE REMS side
DECLARE @userid AS int
SET @userid = (SELECT UserID FROM Employee WHERE EmployeeID = @EmployeeID)
UPDATE [ParameterValue]
SET ParameterValue = @FirstName
WHERE OrderID = @OrderID
AND PreReqID = 10
UPDATE [ParameterValue]
SET ParameterValue = @LastName
WHERE OrderID = @OrderID
AND PreReqID = 11
UPDATE [ParameterValue]
SET ParameterValue = @Phone
WHERE OrderID = @OrderID
AND PreReqID = 12
UPDATE [ParameterValue]
SET ParameterValue = @BFirstName
WHERE OrderID = @OrderID
AND PreReqID = 13
UPDATE [ParameterValue]
SET ParameterValue = @BLastName
WHERE OrderID = @OrderID
AND PreReqID = 14
UPDATE [ParameterValue]
SET ParameterValue = @BPhone
WHERE OrderID = @OrderID
AND PreReqID = 15
--UPDATE Trackit side
DECLARE @TCount AS int
SET @TCount = (SELECT COUNT(*) FROM TRACKIT65_DATA_2..Tasks WHERE UserID = @userid AND Task LIKE '%Secretarial Assignment')
IF @TCount > 0
BEGIN
EXEC TRACKIT65_DATA_2..rems_modTask @OpenBy, @LastName, @FirstName, @Phone, @BLastName, @BFirstName, @BPhone, @userid
END
ELSE
BEGIN
DECLARE @Task AS nvarchar(100)
DECLARE @Request As nvarchar(50)
DECLARE @Priority AS nvarchar(30)
DECLARE @Descript AS nvarchar(4000)
DECLARE @TicketNum AS int
SET @Task = 'New Employee Setup - Secretarial Assignment'
SET @Request = UPPER(@ELastName) + ', ' + UPPER(@EFirstName)
SET @Priority = 'MEDIUM'
IF @FirstName IS NULL
BEGIN
SET @FirstName = ''
END
IF @LastName IS NULL
BEGIN
SET @LastName = ''
END
IF @Phone IS NULL
BEGIN
SET @Phone = ''
END
IF @BFirstName IS NULL
BEGIN
SET @BFirstName = ''
END
IF @BLastName IS NULL
BEGIN
SET @BLastName = ''
END
IF @BPhone IS NULL
BEGIN
SET @BPhone = ''
END
SET @Descript = '--- Requestor ---' + char(13) + char(10) + char(13) + char(10) + 'Employee Number: ' + @EmployeeID + char(13) + char(10) + 'Employee Name: ' + @ELastName + ', ' + @EFirstName + char(13) + char(10) + 'Department: ' + @EDepartment + char(13) + char(10) + 'Title: ' + @ETitle + char(13) + char(10) + 'Position: ' + @EPosition + char(13) + char(10) + 'Status: ' + @EStatus + char(13) + char(10) + 'Date Hired: ' + @EDateBegin + char(13) + char(10) + 'Office Location: ' + @ELocation + char(13) + char(10) + 'Phone Extension: ' + char(13) + char(10) + + char(13) + char(10) + + char(13) + char(10) + '--- Requested Item(s) ---' + char(13) + char(10) + 'Secretary Name: ' + @FirstName + ' ' + @LastName + char(13) + char(10) + 'Phone: ' + @Phone + char(13) + char(10) + 'Backup Secretary Name: ' + @BFirstName + ' ' + @BLastName + char(13) + char(10) + 'Backup Secretary Phone: ' + @BPhone
EXEC TRACKIT65_DATA_2..rems_AddTask @WO_Num = @TicketNum OUTPUT, @Task=@Task, @Request=@Request, @OpenBy=@OpenBy, @Priority=@Priority, @Descript=@Descript, @userid=@UserID, @OrderID=@OrderID
/*DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR(Descript)
FROM TRACKIT65_DATA_2..Tasks
WHERE WO_NUM = @TicketNum
WRITETEXT TRACKIT65_DATA_2..Tasks.Descript @ptrval @Descript*/
UPDATE Request
SET TicketNum = @TicketNum
WHERE OrderID = @OrderID AND RequestItemID = 7 AND RequestTypeID = 1
END
IF @@ERROR = 0
BEGIN
COMMIT TRANSACTION
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
END
ELSE
BEGIN
ROLLBACK TRANSACTION
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
RETURN (1)
END
SET NOCOUNT OFF
RETURN(0)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
------------------------------------------
Here's the .Net code that calls the above sp (called when OnUpdateCommand is fired from a DataList):
-------------------------------------------
protected
void UpdateSecy(object sender, DataListCommandEventArgs e)
{
int OrderID = Convert.ToInt32(Request["oid"]);
if (!IsEmptyControl3("tbFirstName", e) // If all fields are filled
&& !IsEmptyControl3(
"tbLastName", e)
&& !IsEmptyControl3(
"tbPhone", e)
&& !IsEmptyControl3(
"tbBFirstName", e)
&& !IsEmptyControl3(
"tbBLastName", e)
&& !IsEmptyControl3(
"tbBPhone", e))
{
//set statusid = 3 (Entry Completed) for OrderID = oid in Request table;
DataGateway.modRequest(OrderID, 7, 3);
}
else
//set statusid = 1 (Pending Information) for OrderID = oid in Request table;
DataGateway.modRequest(OrderID, 7, 1);
DataGateway.setCompleteDate(OrderID);
ParameterCollection PC = SetUpdateParams(sender, e);
DataGateway.modParams(PC);
dlSecInfo.EditItemIndex = -1;
getData();
} //UpdateStatus
From DataGateway():
public static DataSet modParams(ParameterCollection Params)
{
SqlParameter[] sqlParams = new SqlParameter[17];
SqlParameter param = new SqlParameter("@OrderID", SqlDbType.Int);
param.Value = Params[0].DefaultValue;
sqlParams[0] = param;
param = new SqlParameter("@LastName", SqlDbType.NVarChar, 50);
param.Value = Params[1].DefaultValue;
sqlParams[1] = param;
param = new SqlParameter("@FirstName", SqlDbType.NVarChar, 50);
param.Value = Params[2].DefaultValue;
sqlParams[2] = param;
param = new SqlParameter("@Phone", SqlDbType.NVarChar, 50);
param.Value = Params[3].DefaultValue;
sqlParams[3] = param;
param = new SqlParameter("@BLastName", SqlDbType.NVarChar, 50);
param.Value = Params[4].DefaultValue;
sqlParams[4] = param;
param = new SqlParameter("@BFirstName", SqlDbType.NVarChar, 50);
param.Value = Params[5].DefaultValue;
sqlParams[5] = param;
param = new SqlParameter("@BPhone", SqlDbType.NVarChar, 50);
param.Value = Params[6].DefaultValue;
sqlParams[6] = param;
param = new SqlParameter("@ELastName", SqlDbType.NVarChar, 6);
param.Value = Params[7].DefaultValue;
sqlParams[7] = param;
param = new SqlParameter("@EFirstName", SqlDbType.NVarChar, 50);
param.Value = Params[8].DefaultValue;
sqlParams[8] = param;
param = new SqlParameter("@EmployeeID", SqlDbType.NVarChar, 50);
param.Value = Params[9].DefaultValue;
sqlParams[9] = param;
param = new SqlParameter("@EDepartment", SqlDbType.NVarChar, 50);
param.Value = Params[10].DefaultValue;
sqlParams[10] = param;
param = new SqlParameter("@ETitle", SqlDbType.NVarChar, 50);
param.Value = Params[11].DefaultValue;
sqlParams[11] = param;
param = new SqlParameter("@EStatus", SqlDbType.NVarChar, 50);
param.Value = Params[12].DefaultValue;
sqlParams[12] = param;
param = new SqlParameter("@EPosition", SqlDbType.NVarChar, 50);
param.Value = Params[13].DefaultValue;
sqlParams[13] = param;
param = new SqlParameter("@ELocation", SqlDbType.NVarChar, 50);
param.Value = Params[14].DefaultValue;
sqlParams[14] = param;
param = new SqlParameter("@EDateBegin", SqlDbType.NVarChar, 50);
param.Value = Params[15].DefaultValue;
sqlParams[15] = param;
param = new SqlParameter("@OpenBy", SqlDbType.NVarChar, 50);
param.Value = Params[16].DefaultValue;
sqlParams[16] = param;
return Data.getDataSet(DSN_REMS, "modParams", sqlParams);
}
From Data():
public
static DataSet getDataSet(string DSN, string SqlCmdText, SqlParameter[] SqlParams)
{
string DBString = ConfigurationManager.AppSettings[DSN].ToString();
SqlConnection conn = new SqlConnection(DBString);
conn.Open();
DataSet ds = null;
try
{
ds =
SqlHelper.ExecuteDataset(conn,
CommandType.StoredProcedure,
SqlCmdText,
SqlParams);
}
catch (Exception e)
{
throw e;
}
finally
{
conn.Close();
}
return ds;
}
// end of getDataSet
THANK YOU!!
January 4, 2007 at 1:43 pm
Try this...
SET @Descript =
'--- Requestor ---' + char(13) + char(10) + char(13) + char(10) +
'Employee Number: ' + ISNULL(@EmployeeID, '') + char(13) + char(10) +
'Employee Name: ' + ISNULL(@ELastName, '') + ', ' + ISNULL(@EFirstName, '') + char(13) + char(10) +
'Department: ' + ISNULL(@EDepartment, '') + char(13) + char(10) +
'Title: ' + ISNULL(@ETitle, '') + char(13) + char(10) +
'Position: ' + ISNULL(@EPosition, '') + char(13) + char(10) +
'Status: ' + ISNULL(@EStatus, '') + char(13) + char(10) +
'Date Hired: ' + ISNULL(@EDateBegin, '') + char(13) + char(10) +
'Office Location: ' + ISNULL(@ELocation, '') + char(13) + char(10) +
'Phone Extension: ' + char(13) + char(10) + + char(13) + char(10) + + char(13) + char(10) +
'--- Requested Item(s) ---' + char(13) + char(10) +
'Secretary Name: ' + ISNULL(@FirstName, '') + ' ' + ISNULL(@LastName, '') + char(13) + char(10) +
'Phone: ' + ISNULL(@Phone, '') + char(13) + char(10) +
'Backup Secretary Name: ' + ISNULL(@BFirstName, '') + ' ' + ISNULL(@BLastName, '') + char(13) + char(10) +
'Backup Secretary Phone: ' + ISNULL(@BPhone, '')
--Print @Descript
January 12, 2007 at 3:32 pm
Thanks - your syntax is better than mine, but that doesn't seem to make a difference. I'm stumped and have rewritten all my code using an ASP DataList instead of the new 2.0 DetailsView, and everything's working fine. I'm suspecting a bug in .Net 2.0, but who knows?
Anyway - thanks everyone for all their help!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply