I've created a Stored Procedure which performs a Select against my table, and displays the rows returned via these stmts -
@RowCount int Output
SELECT @rowcount = @@RowCount
This Works fine when Executed from SQL Server, but when trying to invoke the SP from my ASP page it complains that the SP expects parameter '@RowCount' which was not supplied.
I don't need to supply it when invoking the SP directly, why do I need to supply it from ASP?
I tried defining it as NULL within my SP, but can't seem to get it to accept both the NULL & Output parms.
And while I'm at it, how do I get my ASP page to display this @RowCount value?
Can anyone help me in modifying this select statement in order to add two more columns to see if the column is identity and if the column is indexed.
SELECT SUBSTRING(SYSOBJECTS.NAME, 1, 45) AS TABLE_NAME, SUBSTRING(SYSCOLUMNS.NAME, 1, 40) AS COLUMN_NAME, SUBSTRING(SYSCOLUMNS.NAME, 1, 15) AS DATA_TYPE, SYSCOLUMNS.LENGTH, SYSCOLUMNS.XPREC AS PRECISIONS, SYSCOLUMNS.XSCALE AS SCALE, SYSCOLUMNS.ISNULLABLE AS ALLOW_NULLS
FROM SYSREFERENCES RIGHT OUTER JOIN SYSCOLUMNS INNER JOIN SYSTYPES ON SYSCOLUMNS.XTYPE = SYSTYPES.XTYPE INNER JOIN SYSOBJECTS ON SYSCOLUMNS.ID = SYSOBJECTS.ID ON SYSREFERENCES.CONSTID = SYSOBJECTS.ID WHERE SYSOBJECTS.XTYPE <> 'S' and SYSOBJECTS.XTYPE <> 'V' AND SYSOBJECTS.XTYPE <> 'P' ORDER BY TABLE_NAME
How do I use the select statement to create a blank field in a temp table, so I can use the update statement to populate the data later.
I tried [Select ' ' as field] and then tried the update statement to later populate data and I am getting the following error: Msg 8152, Level 16, State 14, Line 1 String or binary data would be truncated. The statement has been terminated.
select uid, OrderID, Count(OrderID)As DupCnt from OrdDetails group by uid, OrderID having count(OrderID) > 1
this returns no rows, can't I show another column to identify which uid goes with the dups, I did it before and now it doesn't work, probably something silly I'm missing.
How to get recordset from only last SELECT stmt executed???
this is part of my code.. in T-Sql
Create proc some mySp ... AS ... set nocount on if (@SelUserID is null) select 0 else if (@SelUserID = @userID)
select 1
else begin
select * -- select a. from dms_prsn_trx_log where @incNo = ult_incid_no and prsn_trx_no = 10 and trx_log_txt = @logText if (@@rowcount != 0) set @isForwardedByUser = 1 select 2 -- select b. I NEED This value.
end set nocount off GO
here it executes select a, b. But, I want mySp return last executed select result. which is here "select 2"
I thought set nocount ON does that, but it doesn't. How can I do???
For my application, i am writing a generalized Sp for the Select Stmt.
I have started with as shown below,
Create procedure proSelect @TabName varchar(1000), @ColName varchar(1000), @ConName varchar(1000) As Begin Declare @str Varchar(8000) If @ColName = '' Set @ColName = '* ' else Set @ColName = @ColName + ' ' Set @str = 'Select ' +@ColName+ 'From ' +@tabname If @ConName <> '' Set @str = @str + ' Where '+ @ConName exec (@str) End;
I wan to more generalize it.. so that all the functionalities of select stmt can be accomplished with this sp...
Can anyone help ???
Thanks in advance...
one more small doubt(personal): All these days i was a starting member of this forum,but today it has changed to 'Yak Veteran Posting'.. What does that mean.. If not to be disclosed in forum, Do mail me at satishr@kggroup.com
Regards, satish.r "Known is a drop, Unknown is an Ocean"
Hello friends , I have table (MoneyTrans) with following structure [Id] [bigint] NOT NULL, [TransDate] [smalldatetime] NOT NULL, [TransName] [varchar](30) NOT NULL, -- CAN have values 'Deposit' / 'WithDraw' [Amount] [money] NOT NULL I need to write a query to generate following output <br> Trans Date, total deposits, total withdrawls, closing balance <br> i.e. Trans Date, sum(amount) for TransName='Deposit' and Date=TransDate , sum(amount) for TransName=Withdraw and Date=TransDate , Closing balance (Sum of deposit - sum of withdraw for date < = TransDate ) I am working on this for past two days with out getting a right solution. Any help is appreciated Sara
Hello Everybody,I have a problem, with select stmt:SELECT TOP 15 *FROM oaVIEW_MainData AS TOP_VIEW,oaLanguageData_TAB AS RwQualifierJoin with (nolock)WHERE (c_dateTime>='2007.01.10 00:00:00' AND c_dateTime<='2007.01.1023:59:59')AND RwQualifierJoin.text_id = c_cfgRegPointAND (((RwQualifierJoin.local1 LIKE N'Position of any bubu')))AND TOP_VIEW.c_dateTime=(SELECT MAX(SUB_VIEW.c_dateTime)FROM oaVIEW_MainData AS SUB_VIEW,oaLanguageData_TAB ASRwQualifierJoin1 with (nolock)WHERE (c_dateTime>='2007.01.10 00:00:00' AND c_dateTime<='2007.01.1023:59:59')AND RwQualifierJoin1.text_id = c_cfgRegPointAND (((RwQualifierJoin1.local1 LIKE N'Position of any bubu')))AND TOP_VIEW.c_dsmIdent=SUB_VIEW.c_dsmIdent)order by c_dateTime descPlease consider:- top doesn't metter, if I will use one or 10000 result is always thesame.- oaVIEW_MainData, is a view on major big table, holding lot of recordsjoinden with small table containing configuration data, over left outerjoin; both tables are with nolock option,- quersy supose to return last record from major table/view, in giventime, additionaly, with other where conditions (like in this case withtext),- on major table, are indexes which one is on id field (not used inthis query at all), which is a pk clustered, and other is on dateEvt(c_dateTime) which is a desc index with fill level 90%- table has also other indexes, on three different fields, one oftheses is dsmIdent,Now, if I'm using max(id) works very fast, and ok for me, but theproblem is, I should not use id, because might be, that the recordswill be written in the table with random order, so the only one sayingwhich is newest, will be dateEvt.Using dateEvt as max(), dramaticly slows query, so I'm acctualy unableto get result. What is much more funny, server is totaly busy with thisquery, and it's procesor jumps on 100%.Now, because the query is builded dynamicly, by a user selections,that's why we decided on such a parser ... problem is, it is notworking :(Can I change index on dateEvt somehow, to sped this up?Maybe construct query somehow different, to get this over max() date?Please helpMatik
hi, how to retrieve row count from an insert statement in dynamic sql?
CREATE PROCEDURE [dbo].[procTest] AS BEGIN SET NOCOUNT ON;
DECLARE @nsql NVARCHAR(MAX) DECLARE @i INT SET @nsql = 'select * into tbl_user_test from tbl_user; select @i = @@rowcount;' exec sp_executesql @nsql; END
I have a table that contains 10 millions records. The following 2 statements, which one provide better performance? Frankly, i have no idea how to compare the execution plan...
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)
I ran the following query in Query Analyzer on a machine running SQL Server 2000. I'm attempting to delete from a linked server running SQL Server 2005:
DELETE FROM sql2005.production.dbo.products WHERE vendor='Foo' AND productId NOT IN ( SELECT productId FROM sql2000.staging.dbo.fooProductList )
The status message (and @@ROWCOUNT) told me 8 rows were affected, but nothing was actually deleted; when I ran a SELECT with the same criteria as the DELETE, all 8 rows are still there. So, once more I tried the DELETE command. This time it told me 7 rows were affected; when I ran the SELECT again, 5 of the rows were still there. Finally, after running this exact same DELETE query 5 times, I was able to remove all 8 rows. Each time it would tell me that a different number of rows had been deleted, and in no case was that number accurate.
I've never seen anything like this before. Neither of the tables involved were undergoing any other changes. There's no replication going on, or anything else that should introduce any delays. And I run queries like this all day, involving every thinkable combination of 2000 and 2005 servers, that don't give me any trouble.
Does anyone have suggestions on what might cause this sort of behavior?
Hello, I am very new to SQL and ran into a problem using Access. I hope you can help me here. The question is the following: I have to tables: Table 4x4: CommodityCode(string)/NeedofBU(high,low) Table ASDBComplete: CommodityCode/Manufacturer/Rating(red,green,yellow)
What I want to get as a result is to display all ComodityCodes that have a 'red' rating and a 'high' NeedOfBU. So far no problem. But now there usually is more than one manufacturer for one CommodityCode. What I need now is that the CommodityCode is not displayed if there actually exist a Manufacturer with 'green' or 'yellow' rating. How would you do that? What I have so far is:
SELECT [4x4].[CommodityCode], [4x4].NeedofBU, ASDBComplete.CommodityCode, ASDBComplete.Rating, ASDBComplete.Manufacturer FROM 4x4 LEFT JOIN ASDBComplete ON [4x4].[CommodityCode] = ASDBComplete.CommodityCode WHERE ((([4x4].NeedofBU)="high") AND ((ASDBComplete.Rating)="red"));
But this gives me all the Commodities with red ratings. Even if there is a supplier with a green or yellow rating.
I would need somting like: if exists 'commodityCode.XY with 'manufacuturer rating = green OR yellow' do not display commodityCode.xy
The stored procedure takes ~ 10 seconds every call. The execution plan is Index seek or clustered index scan almost everywhere . No table spools or lazy loads or key lookups. Cannot share the code or execution plan due to company rules and regulations.Issue is that i am dealing with result set of around 50,000 records . Out of these i have to return 20 records at a single time (which is also customizable i.e 40 / 60/ 150 records in a page). Application cannot handle all 50k records so i have to return 20 records for every stored procedure call.
The result set changes as per the start date and end date which i recieve as parameters. In application there are few Column filters namely- Country(around 50 countries), Outcome(around 6 to 10 values ) . These filters will values in drop down( as excel ) depending on the distinct values in that columns. These filters will be populated on every page, if no filter value is selected.Issue is if user does sorting or filtering any records , this stored procedure is called and every time i have to deal with ~50000 records.
Current Code : Step 1 ) Get the required result set in temp table . Step 2) Compute the results on some business rules . (Outcome and SharesAvailable calculation - see attachment) Step 3) populate filter Columns (Country,Outcome) these values will be comma seperated. Step 4) Dynamic query to get required result set i.e if user wants only 10 records in single page then TOP 10 . Sorting can be applied on any column mentioned in screenshot.
This is probably an easy one, but its been bugging me.
If I have no rows for a particular table, is there any way that I can simply display some text saying something like 'no data available for report' ?
Similarly, I was thinking I could hider certain controls on the report if there are no rows available by using an expression in the Visible property of the control.
SSMS 2012: when you open up many sql files in the IDE, it starts hiding some tabs and you have to click on the drop down at the right to navigate to the tab you want. Is there a way to make it display more than one row of tabs, so that tabs are not hidden and always displayed?
I have a report that is built using the report builder and the report model that was created. With the fields that are displayed in the report, it could be possible for the database to have multiple rows with the same values for all the fields (that are displayed in the report). The PK will be different, but this is not displayed in the report as the PK won't make sense in the report. So what happens is that the report displays just 1 record even though the database has multiple records because all the fields (that are displayed in the report) are identical. Is there a way to make the report display all the rows irrespective of duplicates?
I have a report that displays many duplicate rows where all fields in several rows are exact duplicates. I would like to know how to make it so that the entire row does not show up if it is an exact duplicate of another row. Any ideas? Thanks.
SELECT NON EMPTY { [Measures].[SUM_COUNT] } ON COLUMNS, TopCount ( Filter ( {[DIM].[NAME].[NAME]}, [Measures].[SUM_COUNT] <> 0 ) , 10, [Measures].[SUM_COUNT] ) ON ROWS FROM [USAGE]
where <criteria>
I want to show the topmost 10 records. For some criteria I get the results in the chart.
But for some criteria or say for wrong criteria, there are no records. In such a case the X-axis contains all values for {[DIM].[NAME].[NAME] and value for the Y-axis is all 0. Its kind of blank report it will restrict to 10 records.
In such a scenario I want to show a message to the user saying "No records found", which I have set in the No Rows property of the chart.
If I remove the TopCount clause then I get the above message, which is obvious.
So how do I acheive the same message but at the same time limiting the records to 10?
How can I acheive this in my scenario? Can something be done at the query end?
I have a dataset that when run returns 270 rows. The table using the dataset in the report only prints the first row. I have the table grouped by a status type, but this is for when I can get multi-select paramenters installed and working. For now I just need the report to print all the returned rows. Help!!
(I moved this thread from datagrid area) I have a sql database that has individual records consisting of name, date, hours worked among other fields.Date and name is part of a unique identifier, so there can NOT be two records for the same person for the same date. My users need a grid view that displays days worked in ONE LINE per user. I have gotten close, but can't quite get the last part. Ive tried group by, distinct, and with rollup and no luck.TABLE:dan 12/13/2012 12:00:00 AM9.123dan 12/14/2012 12:00:00 AM3.123123cara 12/12/2012 12:00:00 AM4.222cara 12/16/2012 12:00:00 AM3.3333cara 12/17/2012 12:00:00 AM2 CODE: Select distinct(name), (select (y.hours) from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours and datename(dw, date)='Sunday')as Sunday, (select (y.hours) from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours and datename(dw, date)='Monday')as Monday, (select (y.hours) from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours and datename(dw, date)='Tuesday')as Tuesday, (select (y.hours) from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours and datename(dw, date)='Wednesday')as Wednesday, (select (y.hours) from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours and datename(dw, date)='Thursday')as Thursday, (select (y.hours) from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours and datename(dw, date)='Friday')as Friday, (select (y.hours) from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours and datename(dw, date)='Saturday')as Saturday,(select sum(hours)from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours) as Totalfrom dbo.testtime YTgroup by date, name, hours RESULTS: cara NULL NULL NULL 4.222 NULL NULL NULL 4.222cara NULL 2 NULL NULL NULL NULL NULL 2cara 3.3333 NULL NULL NULL NULL NULL NULL 3.3333dan NULL NULL NULL NULL NULL 3.123123 NULL 3.123123dan NULL NULL NULL NULL 9.123 NULL NULL 9.123 Like I said, I am SO close, I just need it to look like;NAME SUN MIN TU WED TH FR SA TOTAL cara 3.333 2 4.222 9.555 dan 9.125 3.125 12.5 TIAdan
SELECT PR.WBS1, PR.WBS2, ledgerar.invoice, SUM(CASE WHEN ledgerar.transtype = 'in' AND ledgerar.period = '200405' THEN ledgerar.amount * - 1 END) AS amount FROM PR LEFT OUTER JOIN LedgerAR ON LedgerAR.WBS1 = PR.WBS1 AND LedgerAR.WBS2 = PR.WBS2 AND LedgerAR.WBS3 = PR.WBS3 WHERE (LedgerAR.wbs1 = '001-333') AND ledgerar.period = '200405' GROUP BY pr.wbs1, pr.wbs2, ledgerar.invoice
But I don't know how to get the query to display all of wbs2 whether it is null or not. So, for each invoice number there should be five records according to the wbs2 codes (500, 100, 140, 150, 160)
I am interested in changing the way that data is displayed in my result set.Essentially I want to display a selection of rows (1 to n) as columns, the following diagram explains my intentions.Perhaps one of the greatest challenges here is the fact that I do not have a concrete number of rows (or BIN numbers). Each stock item could be stored in one or more BINS, which I will not know until running my query.
There is a valuable script out there that will take the rows from a table and display INSERT STATEMENTS.
Good thing is this script converts the data to HEXADECIMAL ( or some other ) and we don't have to worry about dealing with apostrophies embedded in varchar fields.
The following is a simplified version of my SQL statement. I am attempting to do a simple count(*) with two groupings and a where clause. This is Select command for a GridView. However, I am unable to display zeros. The rows are completely missing and I would greatly appreciate someone's help. I have already tried Group By All, but that, unfortunately, does not work. Here is my SQL statement:
"SELECT [GENDER], [RACE], COUNT(*) FROM [TABLE] WHERE ([COLUMNNAME] ='SOMETHING') GROUP BY [GENDER], [RACE]"
