Problem med stored procedure, som skal opdatere i en anden db
Jeg sidder og bøvler med en stored procedure, som skal opdatere noget i vores webshop modul, når der sker ændringer i den oprindelige tabel.Den data jeg skal have ind får jeg via et view, som jeg tidligere har oprettet i min database.
Mit view hedder: WWW_Varegruppe_view
Det er opgygget som følger:
GrouplanguageID - fast værdi på 1
Groupname - varchar
Groupnumber - varchar
GroupLargeImage - varchar
GroupDescription - varchar
her lige lavet en "create view" til nyt query window - det giver flg.:
USE [F0001]
GO
/****** Object: View [dbo].[WWW_Varegruppe_view] Script Date: 08/01/2007 14:19:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[WWW_Varegruppe_view]
AS
SELECT 44 AS GroupLanguageID, ISNULL
((SELECT TOP (1) Txt
FROM dbo.Txt AS Txt_1
WHERE (Lang = 45) AND (TxtTp = 145) AND (TxtNo = dbo.ProdCat.PrCatNo)), '') AS GroupName, PrCatNo AS GroupNumber, 1 AS GroupLargeImage,
ISNULL
((SELECT TOP (1) Txt
FROM dbo.Txt AS Txt_1
WHERE (Lang = 45) AND (TxtTp = 145) AND (TxtNo = dbo.ProdCat.PrCatNo)), '') AS GroupDescription
FROM dbo.ProdCat
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties =
Begin PaneConfigurations =
Begin PaneConfiguration = 0
NumPanes = 4
Configuration = "(H (1[40] 4[20] 2[20] 3) )"
End
Begin PaneConfiguration = 1
NumPanes = 3
Configuration = "(H (1 [50] 4 [25] 3))"
End
Begin PaneConfiguration = 2
NumPanes = 3
Configuration = "(H (1 [50] 2 [25] 3))"
End
Begin PaneConfiguration = 3
NumPanes = 3
Configuration = "(H (4 [30] 2 [40] 3))"
End
Begin PaneConfiguration = 4
NumPanes = 2
Configuration = "(H (1 [56] 3))"
End
Begin PaneConfiguration = 5
NumPanes = 2
Configuration = "(H (2 [66] 3))"
End
Begin PaneConfiguration = 6
NumPanes = 2
Configuration = "(H (4 [50] 3))"
End
Begin PaneConfiguration = 7
NumPanes = 1
Configuration = "(V (3))"
End
Begin PaneConfiguration = 8
NumPanes = 3
Configuration = "(H (1[56] 4[18] 2) )"
End
Begin PaneConfiguration = 9
NumPanes = 2
Configuration = "(H (1 [75] 4))"
End
Begin PaneConfiguration = 10
NumPanes = 2
Configuration = "(H (1[66] 2) )"
End
Begin PaneConfiguration = 11
NumPanes = 2
Configuration = "(H (4 [60] 2))"
End
Begin PaneConfiguration = 12
NumPanes = 1
Configuration = "(H (1) )"
End
Begin PaneConfiguration = 13
NumPanes = 1
Configuration = "(V (4))"
End
Begin PaneConfiguration = 14
NumPanes = 1
Configuration = "(V (2))"
End
ActivePaneConfig = 0
End
Begin DiagramPane =
Begin Origin =
Top = 0
Left = 0
End
Begin Tables =
Begin Table = "ProdCat"
Begin Extent =
Top = 6
Left = 227
Bottom = 114
Right = 378
End
DisplayFlags = 280
TopColumn = 0
End
End
End
Begin SQLPane =
End
Begin DataPane =
Begin ParameterDefaults = ""
End
Begin ColumnWidths = 9
Width = 284
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
End
End
Begin CriteriaPane =
Begin ColumnWidths = 11
Column = 7815
Alias = 2730
Table = 1170
Output = 720
Append = 1400
NewValue = 1170
SortType = 1350
SortOrder = 1410
GroupBy = 1350
Filter = 1350
Or = 1350
Or = 1350
Or = 1350
End
End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'WWW_Varegruppe_view'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'WWW_Varegruppe_view'
Min stored procedure ser således ud:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Martin Christoffersen
-- Create date: 31072007
-- Description: Til import af varegrupper til DynamicWeb
-- =============================================
ALTER PROCEDURE [dbo].[WWW_Varegruppe_SP]
-- Add the parameters for the stored procedure here
@PrCatNo int = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
-- hvis gruppen allerede er oprettet
If exists (select * from [dynamicweb].[dbo].[EcomGroups] Where GroupNumber = @PrCatNo)
begin
Declare @GroupLanguageID nvarchar
Declare @Groupname nvarchar
Declare @GroupNumber nvarchar
Declare @GroupLargeImage nvarchar
Declare @GroupDescription nvarchar
Select @GroupLanguageID = GroupLanguageID from [F0001].[dbo].[WWW_Varegruppe_view] where GroupNumber = @PrCatNo
Select @GroupName = GroupName from [F0001].[dbo].[WWW_Varegruppe_view] where GroupNumber = @PrCatNo
Select @GroupNumber = GroupNumber from [F0001].[dbo].[WWW_Varegruppe_view] where GroupNumber = @PrCatNo
Select @GroupLargeImage = GroupLargeImage from [F0001].[dbo].[WWW_Varegruppe_view] where GroupNumber = @PrCatNo
Select @GroupDescription = GroupDescription from [F0001].[dbo].[WWW_Varegruppe_view] where GroupNumber = @PrCatNo
UPDATE [dynamicweb].[dbo].[EcomGroups]
set GrouplanguageID = @GroupLanguageID,
GroupName = @GroupName,
GroupLargeImage = @GroupLargeImage,
GroupDescription = @GroupDescription
Where Groupnumber = @PrCatNo
end
else
-- hvis gruppen ikke er oprettet
begin
Declare @HighID nVarChar
-- find nuværende højeste GroupID
select top(1) @HighID = GroupID from [dynamicweb].[dbo].[EcomGroups] Order by GroupID desc
-- Sæt variablen @HighID til en højere end den fundne, for at få nyt unikt ID
set @HighID = @HighID + 1
Select @GroupLanguageID = GroupLanguageID from [F0001].[dbo].[WWW_Varegruppe_view] where GroupNumber = @PrCatNo
Select @GroupName = GroupName from [F0001].[dbo].[WWW_Varegruppe_view] where GroupNumber = @PrCatNo
Select @GroupNumber = GroupNumber from [F0001].[dbo].[WWW_Varegruppe_view] where GroupNumber = @PrCatNo
Select @GroupLargeImage = GroupLargeImage from [F0001].[dbo].[WWW_Varegruppe_view] where GroupNumber = @PrCatNo
Select @GroupDescription = GroupDescription from [F0001].[dbo].[WWW_Varegruppe_view] where GroupNumber = @PrCatNo
-- Insert into dbo.dynamicweb.EcomGroups (GroupID, GroupLanguageID, GroupName, GroupNumber, GroupLargeImage, GroupDescription) Values (@HighID, @GroupLanguageID, @GroupName, @GroupNumber, @GroupLargeImage, @GroupDescription)
INSERT INTO [dynamicweb].[dbo].[EcomGroups]
([GroupID]
,[GroupLanguageID]
,[GroupName]
,[GroupNumber]
,[GroupLargeImage]
,[GroupDescription]
)
VALUES
(@HighID
,@GroupLanguageID
,@GroupName
,@GroupNumber
,@GroupLargeImage
,@GroupDescription
)
end
END
Min tabel i databasen Dynamicweb, som hedder EcomGroups ser således ud:
GroupID - nvarchar
GroupLanguageID - nvarchar
GroupName - nvarchar
GroupNumber - nvarchar
GroupPageIDRel - int
GroupParagraphIDRel - int
GroupSmallImage -nvarchar
GroupDescription - nvarchar
GroupAssortment - bit
GroupIcon - nvarchar
Jeg har indsat min stored procedure i en trigger, så jeg er sikker på at den bliver kørt, når der sker ændringer.
Mit problem er at jeg får fejl, når jeg kører den.
er der nogle af jer derude, som kan gennemskue fejlen?
Fejlmeldingerne kan ses her:
<center>
<a href="http://i160.photobucket.com/albums/t190/vb-fan/diverse/fejl2.jpg" target="_blank">
<img src="http://i160.photobucket.com/albums/t190/vb-fan/diverse/th_fejl2.jpg"></a>
<a href="http://i160.photobucket.com/albums/t190/vb-fan/diverse/fejl1.jpg" target="_blank">
<img src="http://i160.photobucket.com/albums/t190/vb-fan/diverse/th_fejl1.jpg"></a>
</center>
