Trying to call SPROC w/ temp table from web application

[codecomm]

I'm trying to call a linked server, SS2000 that has NText field and instert that data into an nvarchar(120) field on SS2008.

If I execute the SPROC from SS Management Studio, it works fine.
If I try to call the SPROC from my web application, I don't get any errors, but the data doesn't get updated.

I'm using the following SPROC:
CREATE PROCEDURE [dbo].[usp_UpdateMilestoneDescription]
@UID int
AS
BEGIN
SET NOCOUNT ON;

IF EXISTS
(
SELECT *
FROM tempdb.dbo.sysobjects
WHERE ID = OBJECT_ID(N'tempdb.#tmpTextTransfer')
)
BEGIN
DROP TABLE #tmpTextTransfer
END

CREATE TABLE #tmpTextTransfer(textCol nvarchar(max))
INSERT INTO #tmpTextTransfer (textCol)
SELECT [Milestone Description]
FROM [linkedserver].tableMile.dbo.WP_Milestones inner join [Dashboard].[dbo].[Reporting_MS_Dels]
ON
tableMile.WP_Package_Identifier = [Reporting_MS_Dels].WP_Package_Identifier and
tableMile.P3ActivityID = [Reporting_MS_Dels].ActivityId
where UID = @UID;

Declare @strData as nvarchar(120)
Select @strData = textCol from #tmpTextTransfer

Update [Reporting_MS_Dels] Set ActivityTitle = @strData where UID = @UID;


IF EXISTS
(
SELECT *
FROM tempdb.dbo.sysobjects
WHERE ID = OBJECT_ID(N'tempdb.#tmpTextTransfer')
)
BEGIN
DROP TABLE #tmpTextTransfer
END

END



GO

 

[ca8msm]

How are you calling the procedure from ASP.NET? Can you verify what command is being sent to the sql server via the sql profiler tool?

Mark,

Darlington Web Design[tab]|[tab]Experts, Information, Ideas & Knowledge[tab]|[tab]ASP.NET Tips & Tricks

 

[codecomm]

try
{


conn.Open();
SqlCommand MyCmd = new SqlCommand("usp_UpdateMilestoneDescription", conn);
MyCmd.CommandType = CommandType.StoredProcedure;

MyCmd.Parameters.Add("@UID", SqlDbType.Int);
MyCmd.Parameters[0].Value = activeItem["UID"];

MyCmd.ExecuteNonQuery();
conn.Close();

 

[ca8msm]

And what does the profiler say?

Mark,

Darlington Web Design[tab]|[tab]Experts, Information, Ideas & Knowledge[tab]|[tab]ASP.NET Tips & Tricks

 

[jmeckley]

I don't think you include the @ sybmol when defining the parameter.
MyCmd.Parameters.Add("UID". SqlDbType.Int) should work.
Also, confirm that activeItem["UID"] has the correct value.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[codecomm]

jmeckley,

I've got the @ symbol in other DB calls, and it's working fine...no errors. The activeItem["UID"] is correct b/c I was writing it to the page to make sure it was the checked item's Primary Key.

 

[codecomm]

So, in SQL Profileer, I to see this:

EventClass -- RPC:Completed
TextData -- exec usp_UpdateMilestoneDescription @UID=3796
ApplicationName -- .Net SqlClient Data Provider
NTUserName -- no value shown...it's blank
LoginName -- MyUser
CPU -- 0
Reads -- 702
Writes -- 1
Duration -- 32
ClientProcessID -- 5732
SPID -- 58
StartTime -- 2010-07-26 08:57:56.083
EndTime -- 2010-07-26 08:57:56.117


...this is from the web application button click. I don't see any errors from what I have above. Only thing is the NTUserName being blank.

If I run the query from SSMS, I don't see it in the profiler:

USE [db]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[usp_UpdateMilestoneDescription]
@UID = 3798


GO

 

[ca8msm]

I'd be concerned with two things from your testing then:

1. The fact that one query doesn't show up in the profiler; this is very strange as everything is logged against the database when using the profiler so it suggests that one isn't being executed against that database.

2. That you appear to be using different ids (3796 in one and 3798 in the other); for testing purposes I'd stick to using the same test data to verify that it isn't a problem with a particular record.

Mark,

Darlington Web Design[tab]|[tab]Experts, Information, Ideas & Knowledge[tab]|[tab]ASP.NET Tips & Tricks

 

[codecomm]

Sorry...I was just trying different records...same results w/ same ID.

Do I need to do a print or something to get the SSMS "Execute Stored Procedure" to show up in the TextData column of the profiler?

If I include the [Use DB] statement, I see that, but....that's all that gets written. It works, b/c if I do a new select query on that row, the data does get updated from the linked server.

 

[jbenson001]

The fact that you are using a linked server could be the problem. This could be a security issue, meaning that the account you are exectuing the SP from, may not have rights on the linked server.

 

[codecomm]

I've put the same user on both server and it's got dbo rights on each...same results.