How To Retrieve @@Rowcount Variable

Feb 14, 2005

I'm trying to output the number of rows that were effected by my stored proc.





Here is the stored proc:











ALTER Proc Update_IndividualMoves_GTPhone_NCOAPhone_Differ


AS


Update Results


SET Results.home_phone = Results.NEWPhone,


Results.Address1 = Results.NCOAADDRESS1,


Results.CITY = Results.NCOACITY,


Results.ST = Results.NCOAST,


Results.ZIP_OUT = Results.NCOAZIP5,


Results.ZIP4_OUT = Results.NCOAZ4


Where AddressServiceStatus = 'I' AND home_phone IS NOT NULL AND NEWPhone IS NOT NULL AND home_phone <> NEWPhone


Return @@Rowcount











Here is the code from the DAL class that I'm calling the stored procedure from (I'm using the SQL Helper Class.)











Public Shared Function GetAddressIncorrect_HH_GTPhone_NCOAPhone_Differ()





Dim Rowcount As Integer


Dim GlobalConnString As String = AppSettings("ConnectionString")


''Put proc in that gets this data out for household moves that have both


''GTPro and NCOA update phone numbers however they differ. Does not apply


''to DRC donors / < 12 month donors. Update to latest and greatest phone number


''from NCOA listing.





Try


Return ExecuteDataset(GlobalConnString, CommandType.StoredProcedure, "Update_HouseholdMoves_GTPhone_NCOAPhone_Differ", New SqlParameter("@@Rowcount", Rowcount))





Catch ex As Exception


Throw New ApplicationException("An error occured when calling this stored proc out Update_HouseholdMoves_GTPhone_NCOAPhone_Differ")





End Try


End Function











I want to post how many rows were effected in a label that is located on my aspx page through referencing the function above:





What I'm doing is activating the function through an asp:button control and then I want to display the @@Rowcount result in the label next to it.








Here is what I have now:





<code>





Private Sub cmdHouseholdMove2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdHouseholdMove2.Click


'Dim rowcount As Integer


GetAddressIncorrect_HH_GTPhone_NCOAPhone_Differ(New SqlParameter("@@rowcount", lblHouseholdMoves2.Text))








End Sub





If anyone knows how to do this please let me know:





Thanks in advance everyone.





Regards,


RB

View 1 Replies


ADVERTISEMENT

Use Of @@ROWCOUNT Variable

Oct 8, 2007

Hi all,
I am using @@ROWCOUNT variable in one of my stored procedures to get the count of rows affected. This stored procedure is invoked in a web application which is hosted on a server. This web application invokes many other stored procedures and is used simultaneously by many users.
Since @@ROWCOUNT is a global variable which is updated each time a select/insert/delete statement is run on the server, I have a doubt whether using this varible will result in any synchronization issues. I would like to know more about the scope of this variable and the impact of using it in a web application where multiple users can run different stored procs concurrently.

View 1 Replies View Related

Writing RowCount Variable To Table

Dec 5, 2006

I have a series of tasks in a Sequence Container. One of them is a Data Flow task, and inside that task is a Row Count transformation that counts the number of rows I add to a table. The Row Count transformation was added to record the number of rows written to a table in a log table.

When I try to retrieve the resulting variable (RowCount) in the Data Flow task, I get the default value (0). When I try to retrieve it in a subsequent task, I get the value 1. When I try to consume it in the OnPostExecute Event Handler of the original Data Flow task, I get a value of NULL.

1) When is the appropriate time to call the variable assigned to a Row Count transformation so it can be written to a log table?

2) Is there a way during debugging to see the set value of the RowCount variable?

I am using Microsoft SQL Server Integration Services Designer Version 9.00.2047.00 in Visual Studio 2005 Version 8.0.50727.42 (RTM.050727-4200)

Thank you in advance for your posts.

View 5 Replies View Related

To Get Drop Down Menu In VAriable Name Of RowCount Task

Mar 5, 2008

HI

I coulnd't Able to see Drop Down Menu of Any Variables in Variable Name of RowCount Task

Flow:

Aggregation Task--------> RowCount Task------------------->Flat FileConnection Task
|
|
In this Task Variable Name Column Doesn't Show Me Drop Down List
of Available variables.

View 1 Replies View Related

Rowcount - Returning Rowcount From SSIS To A Vb.net App Executing The Dtsx Package

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

How To Retrieve Value From A Table Type Variable?

Dec 19, 2007

 Hi Forum,I have a split function.CREATE FUNCTION dbo.Split2(@List nvarchar(2000),@SplitOn nvarchar(5)) RETURNS @RtnValue table (Id int identity(1,1),Value nvarchar(100)) AS BEGINWhile (Charindex(@SplitOn,@List)>0)Begin Insert Into @RtnValue (value)Select Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1))) Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))End Insert Into @RtnValue (Value) Select Value = ltrim(rtrim(@List)) ReturnEND Courtesy p2p.wrox.com  It returns a TableHow to take the return into a temporary table?I want it inside an SP.Because i have a varchar field named occasionTime (varchar) in a tablea sample data inside it is "January,21,EveryWeek"In my SP i have to get either "January" and compare it to something or "21" or "EveryWeek" for comparison.How to do it?Regards,Naveen 

View 1 Replies View Related

SQL LOJ Rowcount &&> SSIS MergeJoin Rowcount. Why?

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

Retrieve Data From Table Type Variable

Dec 5, 2014

Can we retrieve data from table type variable as an array in oracle key values..

E.g.

ALTER PROCEDURE prn1( @p_prn as KeyValuePair readonly)
-- proc which having parameter table type as parameter
AS
declare @v_formatted_str varchar(50)
BEGIN
set @v_formatted_str = v_formatted_str + @p_prn(1)(1)
END

View 3 Replies View Related

Can I Retrieve A Result Set From A Sp Into A Variable Within A Execute SQL Task?

Jan 25, 2007

Can I retrieve a result set from a sp into a variable within a Execute SQL Task?

View 23 Replies View Related

How Can You Use A Variable Tablename And Retrieve The Output From The Insert?

Aug 28, 2007

We are trying to create a unique key from a table with indentity set in the table. We will have a number of these tables. Therefore, we will be creating a stored procedure and passing the table as a parameter. In this example we are setting the table.

When we run the the script, the output clause from the insert should give us a unique number from the given table in the temporary table. This example stores the output in a temporary table @tTemp.

How can you use a variable table name and retrieve the output from the Insert?


declare @tTestTable varchar (20)

set @tTestTable = 'mis.test_sequence'


--DECLARE @tTestTable TABLE ( sqVal [int] IDENTITY(1,1) NOT NULL, add_date datetime)
declare @testsql varchar (4000), @testseq int

DECLARE @tTemp table (mainpk int)

set @testsql = 'DECLARE @tTemp table (mainpk int) INSERT ' + @tTestTable + ' OUTPUT INSERTED.sqVal into @tTemp VALUES (getdate() ) SELECT @testseq=mainpk FROM @tTemp'

select @testsql

EXECUTE sp_executesql @testsql, N'@testseq int output,@tTemp table (mainpk int),@tTemp table (mainpk int) ',@tTemp,@tTemp,@testseq output,@tTemp

SELECT * FROM @tTemp



Please help
Thanks Tim.

View 3 Replies View Related

Data Access :: How To Retrieve Remote Database Table Value To String Variable

May 28, 2015

I am using C# in  Visual Studio 2008 and remote database as sql server 2008 R2. I want to read remote database table's field value and i have to move that read value to string variable. how to do it. 

And my code is :

string sql = "Select fldinput from tmessage_temp where fldTo=IDENT_CURRENT('tmessage_temp')";
SqlCommand exesql = new SqlCommand(sql, cn);
exesql.CommandType = CommandType.Text;
SqlDataReader rd1 = default(SqlDataReader);
rd1 = exesql.ExecuteReader();

View 6 Replies View Related

Rowcount

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

SET ROWCOUNT

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

@@Rowcount

Feb 8, 2001

Can @@rowcount be used with a regular select statement?

Thanks,
Ninel

View 2 Replies View Related

Rowcount

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

RowCount VS TOP

Jul 25, 2005

Is Set RowCount @RowCountMore efficient than simply using TOP?Thanks for any input.

View 5 Replies View Related

Rowcount Help

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

Getting The Value Of SET ROWCOUNT

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

Rowcount

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

RowCount

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

Getting A Rowcount

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

Rowcount And SQLDataReader

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

How To Get Rowcount From Table

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

Top N[Percent] VS Set ROWCOUNT N

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

If @@Rowcount = .... Problem

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

Problems With @@ROWCOUNT

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

@@ROWCOUNT AND TRANSACTION

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

Rowcount Of Views

Mar 26, 2001

I want to create a stored procedure where I pass the name of a view, the number of rows in the view is determined. If the number of rows is zero, I want the sp to fail with an error code that would bring the DTS that executed the sp down. Here is the sp so far. Note that I am EXECuting a sql string because the FROM clause cannot accept a passed variable. Problem is I need the value of my counter variable(@resultcount) to be available after the EXEC statement. Any ideas on how to do this?


CREATE PROCEDURE CheckForEmpty
@tablename varchar(50)
AS
declare @resultcount integer
declare @sSql varchar(255)
--SP to check for rowcount in passed table name. Useful in DTS steps to stop load process if no rows in sending table.

set @sSql = 'set @resultcount = (select count(*) from ' + @tablename + ')'

exec (@sSql)

if @resultcount = 0 RETURN(-99) else return(0)

View 1 Replies View Related

@@rowcount In Xp_sendmail

Aug 17, 2000

I am trying to put the result of the number of lines of data transferred, in a mail message. In a query, i get the result but i am having trouble displaying the result in xp_sendmail. Any help and/or code will be appreciated.
Thanks

View 2 Replies View Related

Rowcount Of Al Tables In Db

Aug 2, 2002

Is there a script someone knows of which could give me the rowcounts of all the tables in a database?

thanks a lot!

View 2 Replies View Related

Rowcount In Tables

Jul 2, 2004

Can someone throw light on how to get the rowcount of a table that is stored in any system tables? I want to get tablename and rowcount for all user tables in a database in a query. Is there anyway other than count(*)?

Thanks
Vinnie

View 10 Replies View Related

Max Rowcount On Fields

Jan 21, 2005

I have an sp that returns data to a client application. I wonder how I'd go about doing the following: I need to implement a maximum amount of rows on a combination of several columns that are part of the resultset.

fe; Col001, Col002, Col003 are the result, the unique count of Col001 and Col002 are two;

Col001, Col002, Col003
1, 'aa', 'someOtherValue'
1, 'aa', 'someThingElse'
1, 'aa', 'ratherSillyActually'
2, 'aa', '...'

I'd return:
1, 'aa', 'someOtherValue'
1, 'aa', 'someThingElse'
2, 'aa', '...'

Any idea's?

View 14 Replies View Related

@@RowCount Problem

Mar 9, 2004

Hi I'm opening a RecordSet using the following code :

declare tcrl cursor FAST_FORWARD for SELECT TSample.ISmpCode
FROM TCertResults TSample
WHERE (TSample.ISmpShortCode ='24/12359')

Open trcl

What I want to be able to get a count of this recordset. Been thru the TSQL help and it's pointing me toward the @@RowCount command but I can't seem to get this working.

Any help would be apprieciated

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved