@@rowcount Lost By Trigger Beeing Fired
May 23, 2002
Hi,
We have an asp web aplication using COM on a SQL Server 2000 database. The application uses COM to perform the database access. On altering data, the code checks the @@rowcount to see if the intented insert or update was succesfull - this is done on changes in various colums in various locations in the code.
Now we need to add a trigger to update changed data into other tables. This is because we start migrating to a different data structure - while keeping most of the original code intact.
The problem is now, that the trigger beeing fired erases the @@rowcount. I can imagine others to have experienced the same problem. So, the big question is here:
Is there a solition/workaround to keep the @@rowcount on update/insert/deletes on a table evn if a trigger fires?
Thank you,
Marco van Schagen,
Crexx, the Netherlands
View 1 Replies
ADVERTISEMENT
Jul 20, 2005
Are there any limitations or gotchas to updating the same table whichfired a trigger from within the trigger?Some example code below. Hmmm.... This example seems to be workingfine so it must be something with my specific schema/code. We'reworking on running a SQL trace but if anybody has any input, fireaway.Thanks!create table x(Id int,Account varchar(25),Info int)GOinsert into x values ( 1, 'Smith', 15);insert into x values ( 2, 'SmithX', 25);/* Update trigger tu_x for table x */create trigger tu_xon xfor updateasbegindeclare @TriggerRowCount intset @TriggerRowCount = @@ROWCOUNTif ( @TriggerRowCount = 0 )returnif ( @TriggerRowCount > 1 )beginraiserror( 'tu_x: @@ROWCOUNT[%d] Trigger does not handle @@ROWCOUNT[color=blue]> 1 !', 17, 127, @TriggerRowCount) with seterror, nowait[/color]returnendupdate xsetAccount = left( i.Account, 24) + 'X',Info = i.Infofrom deleted, inserted iwhere x.Account = left( deleted.Account, 24) + 'X'endupdate x set Account = 'Blair', Info = 999 where Account = 'Smith'
View 1 Replies
View Related
Jul 10, 2001
Hi,
I have an Access frontend and an SQL Server backend. I use a Query (in Access frontend) to insert in two tables simultaneously. I have a trigger in one of the involved table which update the foreign key in another involved table.
I've built an SQL View with exacly!!! the same syntax as my Query in Access.
And when I insert a new entry through the SQL View the trigger fires(the foreign key is updated), but when I insert through my Access Query the trigger doesn't fires (the foreign key is not updated).
Any idea ?
Thanks in advance
View 1 Replies
View Related
Mar 4, 2008
Hi,
I've created the following trigger:
CREATE TRIGGER myTriggerServer
ON ALL SERVER
FOR CREATE_LOGIN, ALTER_LOGIN, DROP_LOGIN
AS
BEGIN
EXEC myDatabase.dbo.myProcedure
END
The trigger is NOT fired (and procedure not executed) when I create a new login or alter an existing login.
Only Drop Login fires the trigger.
Please tell if it's a problem with SQL Server 2005 or am I making some mistake here?
-Anshul
View 4 Replies
View Related
Sep 12, 2007
if service broker is not an option, how can the trigger and sp(s) called by our trigger act asynchronously to the event that fired the trigger in the first place. We are more concerned with the original event being committed than the actions that follow via the trigger.
View 4 Replies
View Related
Jul 6, 2004
Hi,
I'm using SQL Server 7. I created an insert trigger on a table, and it is working correctly. But how do I now edit it in enterprise manager?
If I right-click on the table, all tasks, manage triggers, all I get is <new> in the name dropdown at the top. Why isn't my trigger there?!?
I must be doing something stupid... I'd appreciate it if someone pointed it out because I don't get it!
:confused: Rob
View 3 Replies
View Related
Jul 7, 2006
I have a vb.net application that executes a simple flat file to sql table dtsx package. I want to capture the rowcount to display back to the user to verify the number of rows that were inserted or updated to the table. I have a Row Count component placed between the flat file source(without errors) and the destination component. I have assigned a variable named RecordCount to the Row Count component. So far so good I hope : )
Now, I also use a variable to "feed" the package the flat file source. This works fine, but I cannot figure out how to retrieve the row count information and how to assign that to the variable RecordCount.
Also, if anyone has any insight on the way to work with the OnProgress method in SSIS I would appreciate that as well. In SQL 2000 using DTS I create a "PackageEventsSink" that I had found online, and it worked great for monitoring the progress of the DTS. Can't seem to figure out how to get it to work in SSIS.
Thanx,
Mike
View 11 Replies
View Related
Mar 5, 2007
Hello
I am developing a windows forms app in Visual C# Express edition, it has a database, and when I am debugging, nothing is being done in the database, and I am sure it is nothing to do with the code. Because all I am doing is creating a database with one table, and just dragging the table (with the datagridview) onto a form, then it runs perfectly, but when I add or delete records, and exit the program, it is not reflected on my DB (which is a SQL Server express DB by the way),
I was looking everywhere, and all I could find was to change the "copy to output directory" propierty of the DB to "copy if newer" but still it doesn't work,
Any ideas why this may be happening??
thanks a lot
Andrés
View 3 Replies
View Related
Jul 25, 2007
In sql I perform the following
SELECT * FROM
xlsdci x LEFT OUTER JOIN fffenics f ON f.[derived deal code] = x.[manual dcd id]
which gives me a row count of 2709 rows
In SSIS I have a merge join component (left outer)
left input = xlsdci with a sort order of 1 ASC on [manual dcd id] (OLE DB source component)
right input = fffenics with a sort order of 1 ASC on [derived deal code] (OLE DB source component)
which when run in the IDE gives me a rowcount of only 2594 rows
Why is this so?
Also if I change the join to INNER in the merge join, the number of rows drops dramatically to only 802.
Fair enough, I hear you cry, maybe there are IDs in the 'xlsdci' table that are not in the 'fffenics' table. Ok. But the following SQL reveals that there are only 14 rows(IDs) in 'xlsdci' that are not in 'fffenics'
SELECT * FROM xlsdci
WHERE [manual dcd id] NOT IN (SELECT [derived deal code] FROM dbo.fffenics)
What is going on here?
View 5 Replies
View Related
Apr 19, 2006
By Database is heavily used , we use Transaction log backups ever 10 minuts, now someone fired a wrong query , that updated a particular table , the DB is still in use, there are some users working on it , I cant get the DB offline , what can I do to correct the mistake , please advice.
View 7 Replies
View Related
Sep 25, 2006
Hi everyone,
I was wondering when events inside IDTSComponentEvents interface are called when you throw a SSIS package? I've got a private class which implements IDTSEvents and along with that got another one that implements the IDTSComponentEvents. I see how neither of them are fired when I debug the code by using F11, and I don't know what is it for.
Let me know your comments or come back to me if you need further details on that.
Thanks in advance,
View 4 Replies
View Related
Mar 21, 2007
Is it possible to only handle the outter-most event? For example, I have an email that goes out in response to OnPreExecute and OnPostExecute, but I only want it to go out once, instead of for every tasks/object.
Thanks,
Rick
View 1 Replies
View Related
Jan 8, 2007
Hi,
How can you get the Query Statement of the last executed SQL command.
I am not quite sure but I do remember coming across such a command ( maybe an undocumented one).
Thanks for your help and pointers.
My Best wishes for the new year to all the folks in the forum. Wishing you greater days ahead.
Warm Regards,
Ranjit S Hans.
---------------------------------------------------------------------
Everywhere is a walking distance if you have the time - Steven Wright
View 4 Replies
View Related
Apr 16, 2014
Our company purchased a app. Is there any way to find out which store procedures were fired once a button was clicked?
View 3 Replies
View Related
Jul 10, 2007
Hello all,
I'm trying to cath content of PRINT statements through InfoMessage event. I have subscribed on InfoMessage event through ConnectionEventsVt interface. The problem that event fired only once for statement. For example i have following T-SQL script:
Code Snippet
PRINT 'Message1'
PRINT 'Message2'
GO
PRINT 'Message3'
PRINT 'Message4'
GO
When i execute this script InfoMessage event fired twice. First time Error object Description property contains "Message1" text. Second time it contains "Message3" text.
What i'm doing wrong?
Server: MS SQL Sever 2005
OS: Windows XP SP2
IDE: Delphi 7
View 1 Replies
View Related
Jul 17, 2007
Hi all,
I am investigating using Query Notifications - a great addition BTW. I have built a test app - loosely based on MSDN example - and am running against a SQL Express 2005 server. I have the following problem:
I have start/stop buttons to enable change checking, and a hardcode query that I am using for testing. If I stop and restart I now recieve duplicate notifcations, a single change causing the OnChange event to fires twice with two different ids Repeat this and the event will fires 3 times for each change and so on.
This only happens with a running app, if I restart the app I only get a single notification so I assume I have missed a step in stopping query notifcations or reinitialisation. I have include code below:
private bool Start()
{
try
{
// Remove any existing dependency connection, then create a new one.
SqlDependency.Stop(_currentConnectionString);
if (SqlDependency.Start(_currentConnectionString))
{
if (connection == null)
{
connection = new SqlConnection(_currentConnectionString);
}
if (command == null)
{
command = new SqlCommand(_sqlQueryString, connection);
}
return (true);
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
return false;
}
private void Stop()
{
SqlDependency.Stop(_currentConnectionString);
if (command != null)
{
command.Notification = null;
command = null;
}
if (connection != null)
{
connection.Close();
connection = null;
}
}
private void GetDataSnapshot()
{
// Empty the dataset so that there is only
// one batch of data displayed.
dataToWatch.Clear();
// Make sure the command object does not already have
// a notification object associated with it.
command.Notification = null;
// Create and bind the SqlDependency object
// to the command object.
SqlDependency dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(dataToWatch, tableName);
}
}
private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
// This event will occur on a thread pool thread.
// Updating the UI from a worker thread is not permitted.
// The following code checks to see if it is safe to
// update the UI.
ISynchronizeInvoke i = (ISynchronizeInvoke)this;
// If InvokeRequired returns True, the code
// is executing on a worker thread.
if (i.InvokeRequired)
{
// Create a delegate to perform the thread switch.
OnChangeEventHandler tempDelegate = new OnChangeEventHandler(dependency_OnChange);
object[] args = { sender, e };
// Marshal the data from the worker thread
// to the UI thread.
i.BeginInvoke(tempDelegate, args);
return;
}
// Remove the handler, since it is only good
// for a single notification.
SqlDependency dependency = (SqlDependency)sender;
dependency.OnChange -= dependency_OnChange;
// At this point, the code is executing on the
// UI thread, so it is safe to update the UI.
++changeCount;
lblStatus.Text = String.Format(statusMessage, changeCount);
// Add information from the event arguments to the list box
// for debugging purposes only.
//lbChanges.Items.Clear();
lbChanges.Items.Add("+++++++++++++++++");
lbChanges.Items.Add("Id: " + dependency.Id);
lbChanges.Items.Add("Info: " + e.Info.ToString());
lbChanges.Items.Add("Source: " + e.Source.ToString());
lbChanges.Items.Add("Type: " + e.Type.ToString());
// Reload the dataset that is bound to the grid.
if (e.Info != SqlNotificationInfo.Error)
this.GetDataSnapshot();
}
Thanks, Nick
View 3 Replies
View Related
Jul 12, 2007
Hi,
Is there any way that I can know the count of queries and queries fired to the database after running a ssis package?
can any one help me out?
Thanks in advance.
View 3 Replies
View Related
Feb 14, 2002
Can anybody help ?
I need to get a limited set of records from sorted record set. For example, I have a table called Contacts which contains about 2000 records and I need to show records from 11 to 20 in the sorted order.
In Oracle I could use this query.
SELECT name FROM
(SELECT name, ROWNUM rnum from Contacts order by Name)
WHERE rnum between 11 and 20
Is anything equivalent available in SQL Server ? My need is to show
records page by page ( as in google/yahoo search ) in my asp page.
Thanks
Subhash
View 1 Replies
View Related
Nov 13, 2000
Does anybody know how to get at the value set by a SET ROWCOUNT statement from within a Stored Procedure. e.g. If I executed the following:-
SET ROWCOUNT 50
EXEC Proc1
How could Proc1 find out that the User had done a SET ROWCOUNT 50.
Any ideas would be appreciated
View 1 Replies
View Related
Feb 8, 2001
Can @@rowcount be used with a regular select statement?
Thanks,
Ninel
View 2 Replies
View Related
Aug 15, 2002
Can someone send me the script that counts the rows of the table within
a database?
Thanks,
Dianne
View 3 Replies
View Related
Jul 25, 2005
Is Set RowCount @RowCountMore efficient than simply using TOP?Thanks for any input.
View 5 Replies
View Related
May 20, 2006
i'm trying to get total rows found by query that uses top clause...for example:select top 10 myTable.* from myTable where myTable.number > 200let's say there are 13 rows matching that condition, and by using@@rowcount my result would be: 10.is there any way to get total row count, without affecting the TOPclause??? i believe that the mysql equivalent would beSQL_CALC_FOUND_ROWS().tnx...
View 1 Replies
View Related
Sep 7, 2006
I'm writing an INSERT, UPDATE and DELETE trigger on table A that needs to insert rows into a table B.
When a user issues a "SET ROWCOUNT" command to limit the number of rows, then does an insert, update or delete, the trigger is being limited to that number.
It is important that the trigger NOT be limited to ANY specific number of records, but it is undesireable for me to just wipe out the current "SET ROWCOUNT" setting for the user without their knowledge.
How do I get and preserve the current "SET ROWCOUNT" value from within my trigger, so I can cancel the limitation and then re-implement the setting once my work is done? I cannot find any documentation specifying where the ROWCOUNT value is set (I initially thought maybe the SYSPROCESSES table, but that doesn't seem to be it).
Example:
set rowcount 1
update TABLEA set val=val+1
<the TABLEA trigger fires, trying to insert multiple rows into TABLEB but is limited to a single row>
Ideas?
View 1 Replies
View Related
Sep 5, 2007
Hi,
I am using ssis to import .csv files into sql server tables.
How do I get the count of the records imported?
Thanks
View 3 Replies
View Related
Jun 26, 2006
Hi,
want to get the number of rows i'm retrieving from a source. This count should be written as " No: of roes retrieved" + varname
I have used OleDbSource, RowCount,Script [ To write in a file ]. Rows is the package level variable name used in rowcount. when i do this way it always writes as 0 in the file.
[code in Script]
Dim sw As New StreamWriter("D:Vijay1.txt")
s = Variables.Rows
sw.WriteLine(s.ToString)
sw.close
[/Code]
Can anyone help on this
View 12 Replies
View Related
Dec 17, 2007
I have a data flow task which contains an XML Source, The XML Source puts data into two OLEDB Destination tasks. What i need to do is check that the number of rows inserted in to the two data bases . Can anyone suggest the easiest way as i need to check before i can commit the transactions. Any help would be most appriciated
Thanks
View 1 Replies
View Related
Aug 29, 2006
Hi, from what I can find, there isn't a way to get the number of rows returned from a SQLDataReader command. Is this correct? If so, is there a way around this? My SQLDataReader command is as follows:Dim commandInd As New System.Data.OleDb.OleDbDataAdapter(strQueryCombined, connInd)Dim commandSQL As New SqlCommand("GetAssetList2", connStringSQL)Dim resultDS As New Data.DataSet()'// Fill the dataset with valuescommandInd.Fill(resultDS)'// Get the XML values of the dataset to send to SQL server and run a new queryDim strXML As String = resultDS.GetXml()Dim xmlFileList As SqlParameterDim strContainsClause As SqlParameter'// Create and execute the search against SQL ServerconnStringSQL.Open()commandSQL.CommandType = Data.CommandType.StoredProcedurecommandSQL.Parameters.Add("@xmlFileList", Data.SqlDbType.VarChar, 1000).Value = strXMLcommandSQL.Parameters.Add("@strContainsClause", Data.SqlDbType.VarChar, 1000).Value = strContainsConstructDim sqlReaderSource As SqlDataReader = commandSQL.ExecuteReader()results.DataSource = sqlReaderSourceresults.DataBind()connStringSQL.Close()And the stored procedure is such:DROP PROC dbo.GetAssetList2;GOCREATE PROC dbo.GetAssetList2(@xmlFileList varchar(1000),@strContainsClause varchar(1000))ASBEGINSET NOCOUNT ONDECLARE @intDocHandle intEXEC sp_xml_preparedocument @intDocHandle OUTPUT, @xmlFileListSELECT DISTINCTAssetsMaster.AssetMasterUID,SupportedFiles.AssetPath,FROM AssetsMaster, OPENXML (@intDocHandle, '/NewDataSet/Table',2) WITH (FILENAME varchar(256)) AS x,SupportedFilesWHEREAssetsMaster.AssetFileName = x.FILENAMEAND AssetsMaster.Extension = SupportedFiles.Extension UNIONSELECT DISTINCTAssetsMaster.AssetMasterUID,SupportedFiles.AssetPath,FROM AssetsMaster, OPENXML (@intDocHandle, '/NewDataSet/Table',2) WITH (FILENAME varchar(256)) AS x,SupportedFilesWHEREAssetsMaster.AssetFileName <> x.FILENAMEAND CONTAINS ((Description, Keywords), @strContainsClause)AND AssetsMaster.Extension = SupportedFiles.ExtensionORDER BY AssetsMaster.Downloads DESCEXEC sp_xml_removedocument @intDocHandle ENDGOHow can I access the number of rows returned by this stored procedure?Thanks,James
View 3 Replies
View Related
Aug 16, 2007
Hi guys, can anybody help to solve this problem.
set @count=0Insert into User_t (userid, counter) select userid, count+1 from resultset is not working
0/p: bhasker 1 bhanu 1 kishore 1
but o/p must be bhasker 1 bhanu 2 kishore 3
View 5 Replies
View Related
Nov 17, 2007
Hi all
whether using TOP clause in SELECT statement or [SET ROWCOUNT n] before SELECT statement, I want to know how SqlServer Behave?
whether Fetching data and then choosing n record of them or as soon as fetching n records , Sql Server Stops retrieving the rest of the data?
Thanks in advance.
Regards.
View 1 Replies
View Related
Jun 16, 2004
Hello again everyone.....
Ok here's my problem.... This is definetly the strangest problem Ive had yet in my coding career..... anyways here it is:
I have a stored procedure which keeps a total number of hits for specific pages:
Procedure CMRC_Hits_Pages_Temp_Update
@Transaction nvarchar(20),
@Hits int = NULL,
@Page nvarchar(50) = NULL
AS
IF @Transaction = 'Delete'
BEGIN
DELETE FROM CMRC_Hits_Pages_Temp
END
IF @Transaction = 'Add'
BEGIN
CREATE TABLE #TempTableUpdate
(
Hits int
)
INSERT INTO #TempTableUpdate
(
Hits
)
SELECT
Hits
FROM
CMRC_Hits_Pages_Details
WHERE
Page = @Page
SELECT
Hits
FROM
#TempTableUpdate
IF @@Rowcount > 0
BEGIN
UPDATE CMRC_Hits_Pages_Temp
SET
Hits = Hits + @Hits
WHERE
Page = @Page
END
IF @@Rowcount < 1
BEGIN
INSERT INTO CMRC_Hits_Pages_Temp
(
Hits,
Page
)
VALUES
(
@Hits,
@Page
)
END
END
I've written it so if there hasn't been an entry for @Page, make a new one..... And if there is an entry allready for @Page, add @Hits to Hits.
Here's the strange part. When I run it in Query Analyzer (so I know there isn't a problem with my pages code), it works fine when I send @Page a value of 'Default' (As in my default page). But when I put any other value (ei. 'ProductsList', ProductDetails', 'test', 'Defauls') it doesn't work. It creates a new record even if there was a record for that page allready. I've tried erasing everything from the table over and over to give it a fresh start and it still only works for 'Default.'
I've tried every length of string possible thinking it may be the length, same problem.
It makes no sense to me why specific letters could make any difference in what this procedure does. A string is a string, right? Why should one string be more recognizable than another? Again, the most confusing thing I've enountered yet in my coding career.
I seem to always run into problems and think "this makes no sense" and then come to figure "Ohhh.... thats whats wrong..." But this problem here is definetely the cream.... It makes NO sense.....
Thank you to whomever can solve this mystery..... (If it is much of one....)
Just incase the Table info is important:
I have two Columns: Hits, int (4) & Page, nvarchar (50)
-Alec
View 2 Replies
View Related
Nov 1, 2005
Hi,I am just starting sql and have a stored proc which does a simple select and should return 2 when no rows match the selection criteria . The problem is that it always returns 2, even where there are rows which match the selection criteria and when there are no rows.CREATE PROCEDURE dbo.SelectSomething @a INT, @b INTAS SET NOCOUNT ON SELECT a, b, c, d FROM dbo.SomeTable WITH (READUNCOMMITTED) WHERE a = @a AND b = @b IF @@ERROR<>0 RETURN 1 IF @@ROWCOUNT=0 RETURN 2 -- Always Returns 2RETURN 0GOThanks for any help.
View 2 Replies
View Related
Jan 11, 2006
I was testing some checking techniques to add to my SPs while retreiving data or inserting a series of reocords to a series of tables.
Check this testing small piece of code:begin transaction t1
select * from roles where rolename = 'student'
if @@rowcount = 0
goto cleanup
select * from users
if @@rowcount = 0
goto cleanup
commit transaction t1
goto endup
cleanup:
rollback transaction t1
SET ROWCOUNT 0
endup:
print @@rowcount
On one of the testing cases: I made the first query return 1 record and the second returned 5 records, so we ended up at the label "endup", I printed out the RowCount it was 0, does the commit transaction reset that variable?
In addition, are there smart and effective techniques to check against validity of the query statements inside the SP, for example, I can use @@ERROR and @@ROWCOUNT in a transaction based SP to know if I proceed with sub queries, are there more stuff like that?
Regards
View 5 Replies
View Related
