FOLLOWING SQL BLOCK WORKS IN SSMS But not in the T

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

FOLLOWING SQL BLOCK WORKS IN SSMS But not in the T

Author

Topic

rv
Starting Member

11 Posts

Posted - 2008-07-22 : 11:28:52

Hi,

Here is the sql block I am using in an Indtead of Insert trigger............ the following code works in SSMS but not in trigger.

The data is getting inserted in ConfigurationDetails table but not in CSTICS_DW_DATA when this code executes in a trigger , It works fine in SSMS otherwise..... Any Suggestions ?

BEGIN

SET ARITHABORT ON

SET CONCAT_NULL_YIELDS_NULL ON

SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

SET NUMERIC_ROUNDABORT OFF

SET NOCOUNT ON;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE @BomId VARCHAR(30)

DECLARE @ConfigId VARCHAR(30)

DECLARE @ItemNo VARCHAR(10)

DECLARE @x XML

Set @x = '<QUOTE configID="ASHASH_0X7f257d8711515242">

<CSTICS>

<ORDER_CFGS_VALUE>

<Field Name="CONFIG_ID" Value="ASHASH_0X7f257d8711515242" />

<Field Name="INST_ID" Value="3" />

<Field Name="CHARC" Value="A7JUS_XFMR_ALARM_CONTACTS_LG" />

<Field Name="VALUE" Value="FALSE" />

</ORDER_CFGS_VALUE>

<ORDER_CFGS_VALUE>

<Field Name="CONFIG_ID" Value="ASHASH_0X7f257d8711515242" />

<Field Name="INST_ID" Value="3" />

<Field Name="CHARC" Value="A7JUS_XFMR_ALARM_CONTACTS_TG" />

<Field Name="VALUE" Value="FALSE" />

</ORDER_CFGS_VALUE>

<ORDER_CFGS_VALUE>

<Field Name="CONFIG_ID" Value="ASHASH_0X7f257d8711515242" />

<Field Name="INST_ID" Value="3" />

<Field Name="CHARC" Value="A7JUS_XFMR_CONTROL_CABINET" />

<Field Name="VALUE" Value="FALSE" />

</ORDER_CFGS_VALUE>

<ORDER_CFGS_VALUE>

<Field Name="CONFIG_ID" Value="ASHASH_0X7f257d8711515242" />

<Field Name="INST_ID" Value="3" />

<Field Name="CHARC" Value="A7JUS_XFMR_RAPID_RISE_RELAY" />

<Field Name="VALUE" Value="FALSE" />

</ORDER_CFGS_VALUE>

<ORDER_CFGS_VALUE>

<Field Name="CONFIG_ID" Value="ASHASH_0X7f257d8711515242" />

<Field Name="INST_ID" Value="3" />

<Field Name="CHARC" Value="A7JUS_XFMR_PRIMARY_CONN_LOC" />

<Field Name="VALUE" Value="LEFT" />

</ORDER_CFGS_VALUE>

<ORDER_CFGS_VALUE>

<Field Name="CONFIG_ID" Value="ASHASH_0X7f257d8711515242" />

<Field Name="INST_ID" Value="3" />

<Field Name="CHARC" Value="A7JUS_CONFIGURATION_TYPE" />

<Field Name="VALUE" Value="XSGD" />

</ORDER_CFGS_VALUE>

<ORDER_CFGS_VALUE>

<Field Name="CONFIG_ID" Value="ASHASH_0X7f257d8711515242" />

<Field Name="INST_ID" Value="3" />

<Field Name="CHARC" Value="A7JUS_FINANCIAL_REPORTING" />

<Field Name="VALUE" Value="SWITCHGEAR" />

</ORDER_CFGS_VALUE>

</CSTICS>

</QUOTE>

'

-- Insert into ConfigurationDetails (ConfigId,ConfigSaveStatus,QuoteXML,ConfigViewXML,ConfigXML,CreatedDate)

-- Select * From INSERTED

--

-- SELECT @x = Cast(QuoteXML AS XML) From INSERTED

-- INSERT INTO CSTICS_DW_DATA (SOURCE_ID,BomID,ItemNo,ConfigId,INST_ID,CHARC,VALUE)

SELECT DISTINCT 'SIPAC',

ID.BomId,

ID.ItemNo,

x.value('(Field[@Name="CONFIG_ID"]/@Value)[1]','VARCHAR(20)') ,

x.value('(Field[@Name="INST_ID"]/@Value)[1]','VARCHAR(10)'),

x.value('(Field[@Name="CHARC"]/@Value)[1]','VARCHAR(20)') ,

x.value('(Field[@Name="VALUE"]/@Value)[1]','VARCHAR(10)')

FROM @x.nodes('/QUOTE/CSTICS/ORDER_CFGS_VALUE') d(x)Cross Apply ItemDetails ID

WHERE ID.ConfigId like x.value('(Field[@Name="CONFIG_ID"]/@Value)[1]','VARCHAR(20)')+'%'

--Insert statements for trigger here

SET ARITHABORT OFF

END

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-07-22 : 12:38:58

Perhaps because the INSERT into CSTICS_DW_DATA is commented out?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-22 : 13:59:27

Are you getting some error? or is it that code runs but insertion not happening?Also have you checked whether you're xml value correctly in trigger?

rv
Starting Member

11 Posts

Posted - 2008-07-22 : 14:26:43

The code is working fine when I run it on SSMS but the insertion is not happening with trigger.

However , I am unable to trace after the first insertion happens in the trigger... One thing I am sure of , and that is XML is correct and works fine on SSMS.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-22 : 14:32:17

rv, please post your exact code that works in SSMS. The code that you posted above does not insert into CSTICS_DW_DATA as that part is commented out. Also post the exact trigger.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rv
Starting Member

11 Posts

Posted - 2008-07-22 : 15:53:00

Hi, Here is the working on SSMS

BEGIN
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE @BomId VARCHAR(30)
DECLARE @ConfigId VARCHAR(30)
DECLARE @ItemNo VARCHAR(10)
DECLARE @x XML

Set @x = '<QUOTE configID="ASHASH_0X7f257d8711515242">
<CSTICS>
<ORDER_CFGS_VALUE>
<Field Name="CONFIG_ID" Value="ASHASH_0X7f257d8711515242" />
<Field Name="INST_ID" Value="3" />
<Field Name="CHARC" Value="A7JUS_XFMR_ALARM_CONTACTS_LG" />
<Field Name="VALUE" Value="FALSE" />
</ORDER_CFGS_VALUE>
<ORDER_CFGS_VALUE>
<Field Name="CONFIG_ID" Value="ASHASH_0X7f257d8711515242" />
<Field Name="INST_ID" Value="3" />
<Field Name="CHARC" Value="A7JUS_XFMR_ALARM_CONTACTS_TG" />
<Field Name="VALUE" Value="FALSE" />
</ORDER_CFGS_VALUE>
<ORDER_CFGS_VALUE>
<Field Name="CONFIG_ID" Value="ASHASH_0X7f257d8711515242" />
<Field Name="INST_ID" Value="3" />
<Field Name="CHARC" Value="A7JUS_XFMR_CONTROL_CABINET" />
<Field Name="VALUE" Value="FALSE" />
</ORDER_CFGS_VALUE>
<ORDER_CFGS_VALUE>
<Field Name="CONFIG_ID" Value="ASHASH_0X7f257d8711515242" />
<Field Name="INST_ID" Value="3" />
<Field Name="CHARC" Value="A7JUS_XFMR_RAPID_RISE_RELAY" />
<Field Name="VALUE" Value="FALSE" />
</ORDER_CFGS_VALUE>
<ORDER_CFGS_VALUE>
<Field Name="CONFIG_ID" Value="ASHASH_0X7f257d8711515242" />
<Field Name="INST_ID" Value="3" />
<Field Name="CHARC" Value="A7JUS_XFMR_PRIMARY_CONN_LOC" />
<Field Name="VALUE" Value="LEFT" />
</ORDER_CFGS_VALUE>
<ORDER_CFGS_VALUE>
<Field Name="CONFIG_ID" Value="ASHASH_0X7f257d8711515242" />
<Field Name="INST_ID" Value="3" />
<Field Name="CHARC" Value="A7JUS_CONFIGURATION_TYPE" />
<Field Name="VALUE" Value="XSGD" />
</ORDER_CFGS_VALUE>
<ORDER_CFGS_VALUE>
<Field Name="CONFIG_ID" Value="ASHASH_0X7f257d8711515242" />
<Field Name="INST_ID" Value="3" />
<Field Name="CHARC" Value="A7JUS_FINANCIAL_REPORTING" />
<Field Name="VALUE" Value="SWITCHGEAR" />
</ORDER_CFGS_VALUE>
</CSTICS>
</QUOTE>
'

-- Insert into ConfigurationDetails (ConfigId,ConfigSaveStatus,QuoteXML,ConfigViewXML,ConfigXML,CreatedDate)
-- Select * From INSERTED
--
-- SELECT @x = Cast(QuoteXML AS XML) From INSERTED

-- INSERT INTO CSTICS_DW_DATA (SOURCE_ID,BomID,ItemNo,ConfigId,INST_ID,CHARC,VALUE)

SELECT DISTINCT 'SIPAC',
ID.BomId,
ID.ItemNo,
x.value('(Field[@Name="CONFIG_ID"]/@Value)[1]','VARCHAR(20)') ,

x.value('(Field[@Name="INST_ID"]/@Value)[1]','VARCHAR(10)'),

x.value('(Field[@Name="CHARC"]/@Value)[1]','VARCHAR(20)') ,

x.value('(Field[@Name="VALUE"]/@Value)[1]','VARCHAR(10)')

FROM @x.nodes('/QUOTE/CSTICS/ORDER_CFGS_VALUE') d(x)Cross Apply ItemDetails ID
WHERE ID.ConfigId like x.value('(Field[@Name="CONFIG_ID"]/@Value)[1]','VARCHAR(20)')+'%'

--Insert statements for trigger here
SET ARITHABORT OFF
END

**********************************************************************
HERE IS THE TRIGGER CODE..

ALTER TRIGGER [dbo].[ConfiDetails_Trg_Insert]
ON [dbo].[ConfigurationDetails]
INSTEAD OF INSERT ---,UPDATE
AS
BEGIN
SET ARITHABORT ON
-- SET CONCAT_NULL_YIELDS_NULL ON
-- SET QUOTED_IDENTIFIER ON
-- SET ANSI_NULLS ON
-- SET ANSI_PADDING ON
-- SET ANSI_WARNINGS ON
-- SET NUMERIC_ROUNDABORT OFF
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE @BomId VARCHAR(30)
-- DECLARE @ConfigID VARCHAR(35)
-- DECLARE @ConfigSaveStatus VARCHAR(10)
-- DECLARE @ConfigViewXML Image
-- DECLARE @ConfigXML Image
-- DECLARE @CreatedDate Datetime
DECLARE @ItemNo VARCHAR(10)
DECLARE @x XML

Insert into ConfigurationDetails (ConfigId,ConfigSaveStatus,QuoteXML,ConfigViewXML,ConfigXML,CreatedDate)
SELECT * FROM INSERTED

SELECT @x = Cast(QuoteXML AS XML) From INSERTED
EXEC QXMLInsert_SP @x
INSERT INTO CSTICS_DW_DATA (SOURCE_ID,BomID,ItemNo,ConfigId,INST_ID,CHARC,VALUE)

SELECT DISTINCT 'SIPAC',
ID.BomId,
ID.ItemNo,
x.value('(Field[@Name="CONFIG_ID"]/@Value)[1]','VARCHAR(20)') ,

x.value('(Field[@Name="INST_ID"]/@Value)[1]','VARCHAR(10)'),

x.value('(Field[@Name="CHARC"]/@Value)[1]','VARCHAR(20)') ,

x.value('(Field[@Name="VALUE"]/@Value)[1]','VARCHAR(10)')

FROM @x.nodes('/QUOTE/CSTICS/ORDER_CFGS_VALUE') d(x)Cross Apply ItemDetails ID
WHERE ID.ConfigId like x.value('(Field[@Name="CONFIG_ID"]/@Value)[1]','VARCHAR(20)')+'%'

-- Insert statements for trigger here
SET ARITHABORT OFF
END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-22 : 15:54:55

When you say it's working in SSMS, what do you mean as that script is not inserting data?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rv
Starting Member

11 Posts

Posted - 2008-07-22 : 16:29:22

Yes the script is inserting data in CSTICS_DW_DATA table as you see the code i have assigned a small piece of XML to the variable @x with datatype XML.
On the otherhand when the same @x is passed as xml to section inserting data in CSTICS_DW_DATA in the trigger , it doesent work ?

Thanks in advance for the help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-22 : 16:58:20

You are missing our point completely. Look at your script. It has the insert commented out.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rv
Starting Member

11 Posts

Posted - 2008-07-22 : 17:28:48

The below 3 statement will be commented because they are not used in SSMS , and not the 4th statement (insert statement) as I have assigned a piece of XML to the variable @x.
-- Insert into ConfigurationDetails (ConfigId,ConfigSaveStatus,QuoteXML,ConfigViewXML,ConfigXML,CreatedDate)
-- Select * From INSERTED
--
-- SELECT @x = Cast(QuoteXML AS XML) From INSERTED

INSERT INTO CSTICS_DW_DATA (SOURCE_ID,BomID,ItemNo,ConfigId,INST_ID,CHARC,VALUE)

rv
Starting Member

11 Posts

Posted - 2008-07-23 : 11:50:53

Please see my last quote

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-23 : 13:07:40

You still are missing the point.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rv
Starting Member

11 Posts

Posted - 2008-07-23 : 13:36:08

Dear Tara,

OK , Here is the script I am Using on SSMS and Not in Trigger...

BEGIN
DECLARE @x XML

Set @x = '<QUOTE configID="ASHASH_0X7f257d8711515242">

<CSTICS>

<ORDER_CFGS_VALUE>

<Field Name="CONFIG_ID" Value="ASHASH_0X7f257d8711515242" />

<Field Name="INST_ID" Value="3" />

<Field Name="CHARC" Value="A7JUS_XFMR_ALARM_CONTACTS_LG" />

<Field Name="VALUE" Value="FALSE" />

</ORDER_CFGS_VALUE>

<ORDER_CFGS_VALUE>

<Field Name="CONFIG_ID" Value="ASHASH_0X7f257d8711515242" />

<Field Name="INST_ID" Value="3" />

<Field Name="CHARC" Value="A7JUS_XFMR_ALARM_CONTACTS_TG" />

<Field Name="VALUE" Value="FALSE" />

</ORDER_CFGS_VALUE>

<ORDER_CFGS_VALUE>

<Field Name="CONFIG_ID" Value="ASHASH_0X7f257d8711515242" />

<Field Name="INST_ID" Value="3" />

<Field Name="CHARC" Value="A7JUS_XFMR_CONTROL_CABINET" />

<Field Name="VALUE" Value="FALSE" />

</ORDER_CFGS_VALUE>

<ORDER_CFGS_VALUE>

<Field Name="CONFIG_ID" Value="ASHASH_0X7f257d8711515242" />

<Field Name="INST_ID" Value="3" />

<Field Name="CHARC" Value="A7JUS_XFMR_RAPID_RISE_RELAY" />

<Field Name="VALUE" Value="FALSE" />

</ORDER_CFGS_VALUE>

<ORDER_CFGS_VALUE>

<Field Name="CONFIG_ID" Value="ASHASH_0X7f257d8711515242" />

<Field Name="INST_ID" Value="3" />

<Field Name="CHARC" Value="A7JUS_XFMR_PRIMARY_CONN_LOC" />

<Field Name="VALUE" Value="LEFT" />

</ORDER_CFGS_VALUE>

<ORDER_CFGS_VALUE>

<Field Name="CONFIG_ID" Value="ASHASH_0X7f257d8711515242" />

<Field Name="INST_ID" Value="3" />

<Field Name="CHARC" Value="A7JUS_CONFIGURATION_TYPE" />

<Field Name="VALUE" Value="XSGD" />

</ORDER_CFGS_VALUE>

<ORDER_CFGS_VALUE>

<Field Name="CONFIG_ID" Value="ASHASH_0X7f257d8711515242" />

<Field Name="INST_ID" Value="3" />

<Field Name="CHARC" Value="A7JUS_FINANCIAL_REPORTING" />

<Field Name="VALUE" Value="SWITCHGEAR" />

</ORDER_CFGS_VALUE>

</CSTICS>

</QUOTE>

'
SET ARITHABORT ON
-- SET CONCAT_NULL_YIELDS_NULL ON
-- SET QUOTED_IDENTIFIER ON
-- SET ANSI_NULLS ON
-- SET ANSI_PADDING ON
-- SET ANSI_WARNINGS ON
-- SET NUMERIC_ROUNDABORT OFF
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
INSERT INTO CSTICS_DW_DATA (SOURCE_ID,BomID,ItemNo,ConfigId,INST_ID,CHARC,VALUE)
SELECT DISTINCT 'SIPAC',
ID.BomId,
ID.ItemNo,
x.value('(Field[@Name="CONFIG_ID"]/@Value)[1]','VARCHAR(20)') ,

x.value('(Field[@Name="INST_ID"]/@Value)[1]','VARCHAR(10)'),

x.value('(Field[@Name="CHARC"]/@Value)[1]','VARCHAR(20)') ,

x.value('(Field[@Name="VALUE"]/@Value)[1]','VARCHAR(10)')

FROM @x.nodes('/QUOTE/CSTICS/ORDER_CFGS_VALUE') d(x)Cross Apply ItemDetails ID
WHERE ID.ConfigId like x.value('(Field[@Name="CONFIG_ID"]/@Value)[1]','VARCHAR(20)')+'%'
SET ARITHABORT OFF
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources