0

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

1
Contributor
1
Reply
2
Views
7 Years
Discussion Span
Last Post by bubberz
0

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

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.