Using SCOPE_IDENTITY Inside A COM+ Component (Trigger Involved)
Jan 7, 2008
Hi,
I'm experiencing a problem I think I should not in my COM+ application. I will describe the setup first and then will expose the problem.
It's a simple COM+ application (dll). Inside it, there's a method to save an object A. Object A is persisted in a table in SQL Server 2000 that uses an identity field for the primary key. What this method does is the following:
1) Insert the record for Object A via ADO
2) Retrieve the Id for the object using SCOPE_IDENTITY via ADO and set it on the object
3) Execute an UPDATE statement based on a certain condition via ADO (this UPDATE statement will fire a trigger, however the trigger will not do anything since the record does not answer the criteria for the trigger)
4) Insert a record for another Object A via ADO
5) Retrieve the Id for the object using SCOPE_IDENTITY via ADO and set it on the object
When I get to step (5), an error is raised because SCOPE_IDENTITY returns NULL. This is as if it was returning the Identity value for the trigger that did not cause any INSERT on the UPDATE statement in (3). All the steps are performed using a single connection.
The trigger will duplicate the updated record in another table if a certain flag is set, so in my case, it was not set yet.
It's just weird that this would happen. If I delete the trigger, everything works fine. @@IDENTITY gives me the same problem. It's really as if the trigger was taking over or something and unless I put something between the two steps I get this error. There's one thing though. In step (3), I was using the adCmdText flag for the ADO statement. If I use adExecuteNoRecords it works fine. However I don't understand why it would be this way, I'm trying to understand why it's not working to begin with, even though the sequence of the steps performed should.
Any idea why this would happen?
Thanks,
Greg
View 3 Replies
ADVERTISEMENT
Mar 16, 2007
In a Data Flow, I have the necessity to use a SSIS variable of type €œObject€? inside Script Component and assign to it the content of 'n' variables of string type.
On exiting from the script the variable of type object should contain something like in the following lines:
AAAAAAAAAAAAAAAAAAAAAAAAAAAAA
BBBBBBBBBBBBBBBBBBBBBBBBBBBBB
CCCCCCCCCCCCCCCCCCCCCCCCCCCCC
DDDDDDDDDDDDDDDDDDDDDDDDDDDDD
€¦€¦€¦€¦€¦€¦€¦.
€¦€¦€¦€¦€¦€¦€¦.
On exiting from the data flow I will use the variable of type Object in a Script Task, by reading each element in a cyclic fashion.
Is there anyone who have experienced something like this? Could anyone provide any example of that?
Thanks in advance!
View 3 Replies
View Related
Nov 27, 2007
No idea where this bug crept in from. Have been using SSIS for 1.5 years now without hitting this problem.
I had a script component opening an XML document and parsing it using XPATH. I added some code that uses StreamReader / Streamwriter (closing one stream before starting the other). The code works without issue in my C# app.
And it ran without issue 2-3 times in SSIS. Then suddenly after running my package again, the script component says it completes successfully, yet nothing happens. I set a breakpoint on the first line of code - it never hits it. I add a msgbox as the first line of code - and it never displays.
I then close my package / exit out of ssis ... and then re-open it. When i open my script component, all of my code is GONE. All references that I added are gone.
I tried adding the streamreader/writer process to a dll I created from my c# app ... and added the DLL to the package -- same result.
I can reproduce this on 2 different computers.
Anyone experience this problem ? Any idea how to stop it ? Or debug it ?
Here is a slimmed down code sample of what causes the error :
Public Class ScriptMain
Public Sub Main()
Try
Dim xmlDoc As New XmlDocument
xmlDoc.Load("c:ulkasync_86281519_20070628045850225_4.xml")
MsgBox("xmlLoaded") --this doesn't display once the package starts "acting up"
Catch ex As Exception
MsgBox(ex.Message)
UpdateXML("c:ulkasync_86281519_20070628045850225_4.xml", ex.Message)
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
Private Sub UpdateXML(ByVal fileName As String, ByVal message As String)
Try
Dim invalidChar As String = message.Trim().Substring(message.Trim().IndexOf("0x"), 4)
Dim rd As StreamReader = New StreamReader(fileName)
Dim xml As String = rd.ReadToEnd()
Xml = Xml.Replace(invalidChar, String.Empty)
xml = xml.Replace("", String.Empty)
xml = xml.Replace("<![CDATA[<![CDATA[", "<![CDATA[")
xml = xml.Replace("]]>]]>", "]]>")
MsgBox("replaced")
rd.Close()
Dim wr As StreamWriter = New StreamWriter(fileName)
wr.Write(xml)
wr.Close()
Dim xdoc As XmlDocument = New XmlDocument()
xdoc.Load(fileName)
Catch ex As Exception
UpdateXML(fileName, ex.Message)
End Try
End Sub
End Class
View 4 Replies
View Related
Apr 10, 2007
We have setup a replication in SQL2000:
We have DTS package automatically pouring data into the publishing database(source tables). During this process, we want to temporary disable certain triggers. However, the command
Alter table 'tbl' disable trigger 'abc' errored out. The error message said:
''Cannot alter the table 'tbl' because it is being published for replication."
I've digged more into this and found although it's not allowed to disable a triggers,
the SQLServer do allow delete the trigger and recreate them.
Is there any way to disable the trigger directly?
Thanks in advance,
Don
BTW:
I've used the following sql directly, however the trigger still fires.
UPDATE
sysobjects
SET
status = status|2048
WHERE
type = 'TR'
AND
parent_obj = OBJECT_ID (@table_name)
The only other way around now is to create stored procedures that dynamically create the trigger. Because our trigger is normmally larger than 8000 bytes. We have to create one stored procedure per trigger. This option is not acceptable because not only it takes quite a time, but also a maintainance nightmare.
View 8 Replies
View Related
Jul 22, 2007
Hi,
I have a script component and under input columns, I checked one of the fields I want to use inside the script. It's under the Input 0 in the hierarchical tree inside the inputs and outputs.
How do i use this inside the script?
cherriesh
View 3 Replies
View Related
Jun 15, 2006
how the hell you allocate a null value for a smalldatetime sql field?
Now, I'm putting a false date because of I'm stuck with this f.. and then I do an update:
.Parameters("@FecEnajenacion").Value = "1999-01-01"
error:
.Parameters("@FecEnajenacion").Value = vbNull
.Parameters("@FecEnajenacion").Value = Null
.Parameters("@FecEnajenacion").Value = SqlDbType.???????
View 4 Replies
View Related
Apr 25, 2006
Hi all,
I have a class that utilizes the ThreadPool together with a ManualResetEvent to queue up work items (in this case, FTP downloads) and then waits until all work items have completed before proceeding.
I then use this class inside a Script Component. However, I am getting a "remote procedure call failed" every single time the FTP component starts a download. This same class works perfectly in a WinForm app that I created as a test harness. Also, the synchronized version of the FTP download component works perfectly well from the Script Task -- it's only when I "wrap" the synchronous version in a threaded version that it fails.
Also, when the "remote procedure call failed" exception occurs, sqldumper.exe runs for a split second and creates a minidump. I, unfortunately, cannot inspect the dump because VS2k5 throws a native error when I try to "debug" the dump.
Is it possible to utilize multithreaded code inside SSIS? I noticed that SQL Server prohibits the use of threading as part of its hosting requirements (http://msdn2.microsoft.com/en-us/library/ms172338.aspx) but I'm not sure how that would impact (if at all) the SSIS host.
Thanks,
Vitaly
View 8 Replies
View Related
Feb 14, 2006
Within a data flow I have a script component that I want to perform some data calls from. I want to execute SQL commands to connections already defined in the package's connection manager.
Is there a way to set my script's connection variable to the connection manager connection? Or, can I use the connection manager connections directly from within the script?
View 5 Replies
View Related
Oct 26, 2006
Hi
I am developing custom dataflow component ,I need to get the of variables of the current package in the component , how can i get it?
Thanks
Mani
View 6 Replies
View Related
Feb 6, 2007
I have a requirement to access a lookup table from within an SSIS Transform Script Component
The aim is to eliminate error characters from within the firstname, lastname, address etc. fields by doing a lookup of an ASCII code reference table and making an InStr() type comparison.
I cannot find a way of opening the reference data set from withing the transform.
Regards
Tim
View 3 Replies
View Related
Dec 9, 2007
I created the following trigger to ensure that Mastercard or Visa are entered as the credit card type is a credit card table. The trigger works fine when I use one type, but when I add or, it doesn't work. Any advice? Thanks
/* THIS TRIGGERS VERIFIES THAT A VALID CREDIT CARD TYPE IS ENTERED*/
CREATE TRIGGER trg_accepted_credit_card
on credit_card_payments
for insert
as
begin
declare @card_type varchar(50)
select @card_type = (select card_type from inserted)
if exists(select 1 from inserted where @card_type != 'Mastercard' OR @card_type != 'Visa')
begin
print 'Invalid Credit Card Type.'
rollback transaction
end
end
View 2 Replies
View Related
Mar 27, 2006
I'm trying to handle some user management inside a trigger. When I call sp_addrolemember I get this error.
sp_addrolemember cannot be used inside a user-defined transaction
Is there any way to get around this error? I need to assign a custom role based on a variable inside this trigger.
Any help is appreciated.
View 5 Replies
View Related
Oct 25, 2001
I have an insert trigger which has to insert new records into 2 other databases. The first thing it has to do is get a maxid from a table in each of the databases before the insert. i was going to use a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statment before getting the ID and then do my insert. My question is two-fold, first is this the best way to do it. And since I have 2 separate selects to get the 2 maxids in the other 2 databases and tables, do I need 2 of these statements inside the trigger, before each insert, or a separate one for each select?
Thanks
View 2 Replies
View Related
Jun 2, 2008
Have the following trigger:
CREATE TRIGGER [UPDATEEGBKMUTREBATERECORDS] ON [dbo].[GBKMUT]
after INSERT
AS
begin
DECLARE@Sum Float
SELECT@Sum = SUM(bdr_hfl)
FROMinserted
WHEREfreefield3 = 'Rebate'
SET@Sum = COALESCE(@Sum, 0)
UPDATEgbkmut
SETbdr_hfl = bdr_hfl - @Sum
WHEREreknr = ' 1040'
end
I need @Sum to reset to zero if the ord_no changes. I'm inserting discount records that need to subtract from another account's amount.
As records are inserted for one ord_no and Inv_no I need to sum bdr_hfl field. When the ord_no and Inv_no change, I need to subtract that from the amount in the bdr_hfl where the account number is 1040 and the inserted records ord_no and Inv_no match the ord_no and Inv_no where the account is 1040. Then I need the @sum to reset to zero and start summing again.
View 5 Replies
View Related
May 16, 2008
Hi All,
I am using a trigger. I want to get the data of a row before updating inside this trigger and insert it into a backup table. Please anybody help me. Example with code is highly appreciated.
Thanks in advance.
View 3 Replies
View Related
Feb 17, 2014
How the procedure will be called inside the trigger,whether first procedure followed by second or parallel it will execute?
CREATE TRIGGER [dbo].[InsertDatFXActualStaging]
ON [dbo].[DatFXActualStaging]--change this table to DatFXActualStaging
for INSERT
AS
BEGIN
SET NOCOUNT ON;
[Code] ....
View 1 Replies
View Related
Aug 11, 2015
I'm updating one column using trigger but i am getting below error .
UPDATE failed because the following SET options have incorrect settings: 'NUMERIC_ROUNDABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
View 2 Replies
View Related
Feb 15, 2007
Hi,
I'm having trouble with a Script Component in a data flow task. I have code that does a SqlCommand.ExecuteReader() call that throws an 'Object reference not set to an instance of an object' error. Thing is, the SqlCommand.ExecuteReader() call is already inside a Try..Catch block. Essentially I have two questions regarding this error:
a) Why doesn't my Catch block catch the exception?
b) I've made sure that my SqlCommand object and the SqlConnection property that it uses are properly instantiated, and the query is correct. Any ideas on why it is throwing that exception?
Hope someone could help.
View 3 Replies
View Related
Oct 26, 2007
Hello,
I have a package that has a data lfow task. this task imports data from a db2 database (using the IBM Ole DB provider fro db2) and adds it to sql server database table. This package was created on the server. then though version control (using TFS source control) I check out the package on my local machine. and when I open the package I get the foll 3 errors.
Error 1 Validation error. Import Account Num from BMGP_BDR: DTS.Pipeline: The component metadata for "component "DataReader Source" (1113)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
Error 2 Error loading BMAG Download Xref Tables - bmag.dtsx: Microsoft.SqlServer.Dts.Pipeline.ComponentVersionMismatchException: The version of component "DataReader Source" (1113) is not compatible with this version of the DataFlow. [[The version or pipeline version or both for the specified component is higher than the current version. This package was probably created on a new version of DTS or the component than is installed on the current PC.]] at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostCheckAndPerformUpgrade(IDTSManagedComponentWrapper90 wrapper, Int32 lPipelineVersion)
Error 3 Error loading BMAG Download Xref Tables - bmag.dtsx: The component metadata for "component "DataReader Source" (1113)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
Please advice.
Thank you.
View 7 Replies
View Related
Jan 23, 2007
Hi,
I have a package which reads an Access file from a folder. My connection manager to this file is .NET providers for OledbMicrosoft Jet 4.0 OLE DB Provider.
Package works from my computer. But when I execute it on the server as a SQL Agent job, I get
The component metadata for "component "DataReader Source" (1) could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
I copied the mdb file to a folder on the server which my packages have no problem reading data from.
My packages run under the same domain account as defined in proxies.
Appreciate a help.
Gulden
View 4 Replies
View Related
Sep 12, 2007
For inserting current date and time into the database, is it more efficient and performant and faster to do getDate() inside SQL Server and insert the value
OR
to do System.DateTime.Now in the application and then insert it in the table?
I figure even small differences would be magnified if there is moderate traffic, so every little bit helps.
Thanks.
View 9 Replies
View Related
Nov 16, 2007
I'm trying to execute a stored procedure within the case clause of select statement.
The stored procedure returns a table, and is pretty big and complex, and I don't particularly want to copy the whole thing over to work here. I'm looking for something more elegant.
@val1 and @val2 are passed in
CREATE TABLE #TEMP(
tempid INT IDENTITY (1,1) NOT NULL,
myint INT NOT NULL,
mybool BIT NOT NULL
)
INSERT INTO #TEMP (myint, mybool)
SELECT my_int_from_tbl,
CASE WHEN @val1 IN (SELECT val1 FROM (EXEC dbo.my_stored_procedure my_int_from_tbl, my_param)) THEN 1 ELSE 0
FROM dbo.tbl
WHERE tbl.val2 = @val2
SELECT COUNT(*) FROM #TEMP WHERE mybool = 1
If I have to, I can do a while loop and populate another temp table for every "my_int_from_tbl," but I don't really know the syntax for that.
Any suggestions?
View 8 Replies
View Related
Jun 17, 2014
Say I have app, I want to know when I perform any operation like logging, changing username etc, can I get all the tables involved for that particular operation.
View 2 Replies
View Related
Mar 24, 2004
Hi all
I have restored a db from a live environment to my dev envorinment. This db on live is a replication publisher.
My dev environment has no replication, but when I try to modify the schema in the db I restored I get the following error
"Server: Msg 4932, Level 16, State 1, Line 441
ALTER TABLE DROP COLUMN failed because 'url_web1' is currently replicated."
Does anyone know what I have to do to restore the database on my de box so it 'forgets' its live replication settings?
On my dev server I have no items in the replication folder in Enterprise Manager but it seems replication details are stored somewhere, perhaps in the backup itself?
Matt
View 10 Replies
View Related
Apr 2, 2008
Hi,
I have done a stored procedure but happend to know that people calling my database cannot access procedures only views. But my select query requires to parameters (two dates) in order to get the correct data.
How can I solve the problem?
View 6 Replies
View Related
Jan 18, 2006
Hi,The following request select a constraint from TABLE_CONSTRAINT withthe name specified in the where clause:select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS whereconstraint_name = 'FK__51OtherParties__51Claims'It returns:http://graphicsxp.free.fr/constraint.JPGSo I have TABLE_NAME that correspond to the first table involved in theconstraint, but how do I get 51Claims ????Thank you
View 2 Replies
View Related
Dec 5, 2003
Do we need to disable replication to do a restore? Any help on this?
View 1 Replies
View Related
Dec 17, 2014
i have 3 tables having refrential integrity on id on column on each table
TABLEA contains 5 million rows
TABLEB contains 50 thousand rows
TABLEC contails 5 thousand rows
i have a proc that have 3 in parameter that are actually values of some of the columns in that table one parameter for each table.what is the optimized way to write query to get records on the basis of in parameters from these tables.
View 7 Replies
View Related
Jun 13, 1999
i have 2 questions,
1. cause there is no record of my old SQL 6.5 installation leaft for me,so i try to find the character set and sort order of my old SQL 6.5 installation, and in the MSSQLlog directory the errlog.? file there are these lines:
99/06/11 12:59:24.29 spid1 SQL Server's default sort order is:
99/06/11 12:59:24.29 spid1 'nocase' (ID = 52)
99/06/11 12:59:24.29 spid1 on top of default character set:
99/06/11 12:59:24.29 spid1 'iso_1' (ID = 1)
so my old installation's character set and sort order is as above,am i right?
2. cause i am in chinese so any good idea for me to upgrade this old SQL 6.5 to use chinese character set (cp936) , is there anyway to transfer the old data?
thanks if any suggestions!
welcome to send me mail about the question.
View 1 Replies
View Related
Sep 30, 2013
I have a query written that filters on joined table data. The SELECT looks like this:
SELECT *
FROM tbl_bol AS a LEFT OUTER JOIN
bol_status AS b ON b.bol_status_id = a.bol_status_id LEFT OUTER JOIN
tbl_carrier AS c ON c.carrier_id = a.carrier_id
WHERE (a.carrier_name LIKE 'five%') AND
(a.accrueamt = 0) AND
(a.imported = 1) AND
(b.description = 'tendered') AND
(a.ship_date BETWEEN '9/1/13' AND '9/30/13')
ORDER BY a.bol_number DESC
If I want to do an UPDATE query that uses those filters in the WHERE clause, how do I go about doing that? It doesn't look like you can used joined tables in the UPDATE line like this:
UPDATE tbl_bol AS a LEFT OUTER JOIN
bol_status AS b ON b.bol_status_id = a.bol_status_id LEFT OUTER JOIN
tbl_carrier AS c ON c.carrier_id = a.carrier_id
SET accrueamt='1348'
WHERE (a.carrier_name LIKE 'five%') AND
(a.accrueamt = 0) AND
(a.imported = 1) AND
(b.description = 'tendered') AND
(a.ship_date BETWEEN '9/1/13' AND '9/30/13')
View 4 Replies
View Related
Mar 13, 2008
Hi Guys,
We already have work around when it comes to read xml data type from remote servers
remote server (eurodata3) table
create table temp_asaf
(
xmlCol XML
)
SELECT
Cast(a.XML_Data as XML) as XML_Data
FROM
OPENQUERY(eurodata3,'
SELECT TOP 10
Cast(xmlCol as Varchar(MAX)) as XML_Data
FROM
em_port.dbo.temp_asaf'
) a
Here is a question for you. How do I insert data into remote server when data type for a column is xml?
INSERT OPENQUERY (eurodata3, '
SELECT Cast(xmlCol as Varchar(MAX)) AS xmlCol
FROM em_port.dbo.temp_asaf')
VALUES ('<html><body>MS Sql Server</body></html>');
Running above code would give me the following error:
OLE DB provider "SQLNCLI" for linked server "eurodata3" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Msg 7344, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "eurodata3" could not INSERT INTO table "[SQLNCLI]" because of column "xmlCol". The user did not have permission to write to the column.
Location: memilb.cpp:1493
Expression: (*ppilb)->m_cRef == 0
SPID: 59
Process ID: 1660
Don't be misled by permission statement. I could successfully run similar query as long as the column is not xml on remote server. I would appreciate some ideas to get around with it.
View 3 Replies
View Related
Aug 3, 2015
I have a list of mdx queries extracted by sql server trace file. I want to get list of attributes and measures involved in each query.
View 6 Replies
View Related
Jun 14, 2007
I have created an SSIS package which, as its 2nd step, picks up a .dbf table and does a Data Flow Task of putting the .dbf table into a SQL Server 2005 table (which was just truncated in Step 1).
The Connection Manager that works on the .dbf table is Native OLE DBMicrosoft Jet 4.0 OLE DB Provider.
The entire SSIS package runs perfectly when i run it from my development environment.
I then assigned this Package to a SQL Server 2005 Job. The entire package is the sole step of the job.
But when I run the SQL Job, the Job fails immediately on Step 2.
Here is the error message in View History of the SQL Job:
-----------------------------------------------------------------------------
Executed as user: DBASE01SQLExec. ...on 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:48:37 AM Progress: 2007-06-14 11:48:37.91 Source: DBF to ScanUS_Process Validating: 0% complete End Progress Progress: 2007-06-14 11:48:37.91 Source: DBF to ScanUS_Process Validating: 33% complete End Progress Error: 2007-06-14 11:48:37.91 Code: 0xC0202009 Source: DBF to ScanUS_Process ScanUS DBF Output File [1] Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37. End Error Error: 2007-06-14 11:48:37.91 Code: 0xC02020E8 Source: DBF to ScanUS_Process ScanUS DBF Output File [1] Description: Opening a rowset for "scanus_process" failed. Check that the object exists in the database. End Error Error: 2007-06-14 11:48:37.95 Code: 0xC004706B Source: DBF to ScanUS_Process DTS.Pipeline Description: "component "ScanUS... The package execution fa... The step failed.
-----------------------------------------------------------------------------
Could someone suggest why this works OK within SSIS but not when the SSIS Pakcage is called from SQL?
thx very much/spirits.
seth j hersh
View 7 Replies
View Related