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 + ')'
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.
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)
Fellow database developers,I would like to draw on your experience with views. I have a databasethat includes many views. Sometimes, views contains other views, andthose views in turn may contain views. In fact, I have some views inmy database that are a product of nested views of up to 6 levels deep!The reason we did this was.1. Object-oriented in nature. Makes it easy to work with them.2. Changing an underlying view (adding new fields, removing etc),automatically the higher up views inherit this new information. Thismake maintenance very easy.3. These nested views are only ever used for the reporting side of ourapplication, not for the day-to-day database use by the application.We use Crystal Reports and Crystal is smart enough (can't believe Ijust said that about Crystal) to only pull back the fields that arebeing accessed by the report. In other words, Crystal will issue aSelect field1, field2, field3 from ReportingView Where .... eventhough "ReportingView" contains a long list of fields.Problems I can see.1. Parent views generally use "Select * From childview". This meansthat we have to execute a "sp_refreshview" command against all viewswhenever child views are altered.2. Parent views return a lot of information that isn't necessarilyused.3. Makes it harder to track down exactly where the information iscoming from. You have to drill right through to the child view to seethe raw table joins etc.Does anyone have any comments on this database design? I would love tohear your opinions and tales from the trenches.Best regards,Rod.
Newbie here. I've only been using SQL for about a year now and have some minor questions about sql objects that reference other objects.
We have some views which reference other views in the joins. I will call one the primary view and the one being referenced in the joins as the secondary view.
Recently we made changes to the secondary view.
After which the primary views which referenced it would not work because of this change and had to be 'refreshed' by using drop/create scripts which essentially just dropped it and recreated the exact same view. I do not recall the exact error message that was returned other than it seemed to suggest that it could no longer see the secondary view since it had been changed. Nothing in the primary view was changed in any way, just the secondary.
Some here where I work have suggested off hand that this was a recompile of the primary view because the contents of the secondary changed.
My questions are:
1. Exactly why did this happen and is there a proper name for it when it does?
2. The same problem does not seem to occur when we have stored procedures referencing views in the joins which had just been changed. Why is that?
Thanks for any help on the matter. I greatly appreciate it.
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.
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...
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>
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.
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
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
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
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.
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
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)
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.
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?
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
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(*)?
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;
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.
Hi, I ran the following code and got @rows to display the value 1 - I guess it should display 0 because the IF statement returns no rows of course - So why is it displaying 1?
DECLARE @ERRORS INT SELECT 5,10,15 IF @@ERROR > 0 SET @ERRORS=1 ELSE SET @ERRORS=0 SELECT 'ROW_Count', @@ROWCOUNT --> shows 1 !!
begin print '' set @sql = 'use [' + @db + ']' print @sql exec (@sql)
declare b cursor for select table_name from information_schema.tables where table_type = 'base table' and table_schema = 'dbo' and table_name not like 'dbo.%'
open b fetch next from b into @table_name while @@fetch_status = 0 begin
I'm coming from Oracle world and my knowledge about SQL Server is quite limited so I apriori apologize for probably stupid questions
DB version is SQL Server 2005. The business scenario is - there is search form with many criteria. User may enter very unrestrictive criteria matching probably millions of rows. To prevent that we'd like to show him no more than N rows (N ~200). Any rows matching criteria are good enough, however these FOUND rows we'd like to sort. I do not want to find all potentially X million rows, then sort them and only then show forst N rows. So in Oracle I know how to do that. I just find N rows in subquery, and then in outer query sort them. So I avoid to find all rows and then sorting millions of rows. Here is an example:
Code Block SQL> create table t (id number, data varchar2 Table created. SQL> insert into t values (1, 'aaa'); 1 row created. SQL> insert into t values (2, 'bbb'); 1 row created. SQL> insert into t values (3, 'ccc'); 1 row created. SQL> select * from ( 2 select * from t where rownum <=2) 3 ; ID DATA ---------- -------------------- 1 aaa 2 bbb SQL> ed Wrote file afiedt.buf 1 select * from ( 2 select * from t where rownum <=2) 3* order by data desc SQL> / ID DATA ---------- -------------------- 2 bbb 1 aaa
However how can I avoid sort of potential big result in SQL Server? I've searched google but unfortunately found nothing. I've tried to use both TOP and SET rowcount without success i.e. from these examples I assume that DB will find ALL rows matching where clause then sort them keeping only first N. It seems that order by clause in outer query is pushed into inner query both for top and set rowcount.
Code Block create table t (id integer, data varchar(20)); insert into t values (1, 'aaa'); insert into t values (2, 'bbb'); insert into t values (3, 'ccc');
select * from ( select top 2 * from t) as q
1 aaa 2 bbb
select * from ( select top 2 * from t) as q order by data desc
3 ccc 2 bbb
set rowcount 2 select * from ( select * from t) as q
1 aaa 2 bbb
set rowcount 2 select * from ( select * from t) as q order by data desc
3 ccc 2 bbb
And I'd like to avoid sort because of two reasons: 1) I predict that generally finding all rows will be much more costly than finding just any no more than N 2) Sorting all found rows also probably will be slower than just N (however DB has to find only first N rows, so not ALL rows should be sorted/kept sorted)
Are my concerns reasonable? If yes what can I do to just find N rows and sort only these?
I have a query that returns a set of rows - sorted by part#. On the report I can hide the duplicates (part#). How can I test the part# so that whenever a new part# starts I can reverse image the whole l line. I have not defined any groups. Is this a must?
