I’m trying to call a linked server, SS2000 that has NText field and insert 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