Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Trying to call SPROC w/ temp table from web application

Status
Not open for further replies.

codecomm

Programmer
Feb 14, 2007
121
US
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
 

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();
 
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
 
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.
 
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
 
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
 
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.
 
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.
 
I've put the same user on both server and it's got dbo rights on each...same results.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top