I have been searching this topic on and for quite some time and can't seem to find a decent answer. Is it feasible to do your paging strictly from a SQL query? Pass the query the pagesize, what page to return and what to sort by?
View 4 RepliesHi,
I have a web form that lets users search for people in my database they wish to contact. The database returns a paged set of results using a CTE, Top X, and Row_number().
I would like to give my users to option of removing individual people from this list but cannot find a way to do this.
I have tried creating a session variable with a comma delimited list of ID's that I pass to my sproc and use in a NOT IN() statement. But I keep getting a "Input string was not in a correct format." Error Message.
Is there any way to do this? I am still new to stored procedures so any advice would be helpful.
Thanks
I am trying to move my application (asp.net) from a non-paged
select to a paged query.
I am having a problem. Below is my Stored Procedure. The
first Query in the procedure works...the rest (which are commented
out ALL work interactively, but fail when the program tries to
access....The ONLY THING I change is the stored procedure
I switch the comment lines to the non-paged procedure and it
works, I try to use the any of the paged procedures and it
fails with the same error (included below)
I can't see where any of the queries are returning
different results. I have also included the program abort
that happens, it is the same for all of the paged queries.
ALTER PROCEDURE dbo.puse_equipment_GetAllTypedEquipment
(
@EquipmentTypeId int,
@StartRowIndex int,
@MaximumRows int
)
AS
-- ************************************************************************************************
-- Non-Paged OUTPUT
-- THIS WORKS!!!!!
SET NOCOUNT ON
SELECT Equipment.*,
EquipmentType.Name as EquipmentType,
EquipmentCategory.Name as Category
FROM Equipment INNER JOIN EquipmentCategory ON EquipmentCategory.CategoryId = Equipment.CategoryId
INNER JOIN EquipmentType ON EquipmentType.EquipmentTypeId = Equipment.EquipmentTypeId
where Equipment.EquipmentTypeId = @EquipmentTypeId AND
Equipment.IsDeleted = 0
/*
-- ************************************************************************************************
-- Using a Temp Table
--THIS WORKS INTERACTIVELY, But NOT When Called by the program
SET NOCOUNT ON
create table #PagedEquipment
(
IndexId int IDENTITY(1,1) Not NULL,
EquipId int
)
-- Insert the rows from Equipment into the PagedEquipment table
Insert INTO #PagedEquipment (EquipId)
select EquipmentId From Equipment
WHERE IsDeleted = 0
SELECT #PagedEquipment.IndexId, *,EquipmentType.Name as EquipmentType, EquipmentCategory.Name as Category
FROM Equipment
INNER JOIN #PagedEquipment with (nolock) on Equipment.EquipmentId = #PagedEquipment.EquipId
INNER JOIN EquipmentCategory ON EquipmentCategory.CategoryId = Equipment.CategoryId
INNER JOIN EquipmentType ON EquipmentType.EquipmentTypeId = Equipment.EquipmentTypeId
Where #PagedEquipment.IndexId Between (@StartRowIndex) AND (@StartRowIndex + @MaximumRows +1)
*/
/*
-- **********************************************************************************************
--Using the With to create a temp table (in memory)..works interactively but fails when
--called by the application..
--THIS WORKS INTERACTIVELY, But NOT When Called by the program
Set NOCOUNT ON;
With PagedEquipment AS
(
SELECT EquipmentId,
ROW_NUMBER() OVER (Order by Equipment.EquipmentId) AS RowNumber
FROM Equipment
WHERE EquipmentTypeId = @EquipmentTypeId AND
IsDeleted = 0
)
SELECT RowNumber, Equipment.*, EquipmentCategory.Name as Category, EquipmentType.Name as EquipmentType
FROM PagedEquipment
INNER JOIN Equipment ON Equipment.EquipmentId = PagedEquipment.EquipmentId
INNER JOIN EquipmentCategory ON Equipment.CategoryId = EquipmentCategory.CategoryId
INNER JOIN EquipmentType ON Equipment.EquipmentTypeId = EquipmentType.EquipmentTypeId
WHERE PagedEquipment.RowNumber Between (@StartRowIndex+1) AND (@StartRowIndex+1+@MaximumRows)
return
-- *******************************************************************************************
*/
/*
-- ********************************************************************************************
--nested selects
--THIS WORKS INTERACTIVELY, BUT NOT WHEN CALLED FROM THE PROGRAM
SET NOCOUNT ON
Select * From
(
Select Row_Number() OVER (Order By Equipment.EquipmentId) as RowNumber,
Equipment.*,
EquipmentType.Name as EquipmentType,
EquipmentCategory.Name as Category
FROM Equipment INNER JOIN EquipmentCategory ON EquipmentCategory.CategoryId = Equipment.CategoryId
INNER JOIN EquipmentType ON EquipmentType.EquipmentTypeId = Equipment.EquipmentTypeId
where Equipment.EquipmentTypeId = @EquipmentTypeId AND
Equipment.IsDeleted = 0
) equip
Where equip.RowNumber between (@StartRowIndex+1) AND (@StartRowIndex + 1 + @MaximumRows )
-- ************************************************************************************************
*/
Server Error in '/pUse' Application.
--------------------------------------------------------------------------------
Arithmetic overflow error converting expression to data type int.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Arithmetic overflow error converting expression to data type int.
Source Error:
Line 148: {
Line 149: List<EquipmentDetails> equipment = new List<EquipmentDetails>();
Line 150: while (reader.Read())
Line 151: equipment.Add(GetEquipmentFromReader(reader));
Line 152: return equipment;
Source File: c:Documents and SettingsBrianDesktoppuseApp_CodeDALEquipmentEquipmentProvider.cs Line: 150
Stack Trace:
[SqlException (0x80131904): Arithmetic overflow error converting expression to data type int.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +925466
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800118
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1932
System.Data.SqlClient.SqlDataReader.HasMoreRows() +150
System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) +212
System.Data.SqlClient.SqlDataReader.Read() +9
PredominantUse.DAL.Equipment.EquipmentProvider.GetEquipmentCollectionFromReader(IDataReader reader) in c:Documents and SettingsBrianDesktoppuseApp_CodeDALEquipmentEquipmentProvider.cs:150
PredominantUse.DAL.Equipment.SqlClient.SqlEquipmentProvider.GetTypedEquipmentList(Int32 EquipmentTypeId, Int32 StartRowIndex, Int32 MaximumRows) in c:Documents and SettingsBrianDesktoppuseApp_CodeDALEquipmentSqlClientSqlEquipmentProvider.cs:103
PredominantUse.BLL.Equipment.GetTypedEquipment(Int32 EquipmentTypeId, Int32 StartRowIndex, Int32 MaximumRows) in c:Documents and SettingsBrianDesktoppuseApp_CodeBLLEquipmentEquipment.cs:259
PredominantUse.BLL.Equipment.GetTypedEquipment(Int32 EquipmentTypeId) in c:Documents and SettingsBrianDesktoppuseApp_CodeBLLEquipmentEquipment.cs:238
[TargetInvocationException: Exception has been thrown by the target of an invocation.]
System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) +0
System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) +72
System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) +371
System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) +29
System.Web.UI.WebControls.ObjectDataSourceView.InvokeMethod(ObjectDataSourceMethod method, Boolean disposeInstance, Object& instance) +480
System.Web.UI.WebControls.ObjectDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1960
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
System.Web.UI.WebControls.GridView.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69
System.Web.UI.Adapters.ControlAdapter.CreateChildControls() +12
System.Web.UI.Control.EnsureChildControls() +128
System.Web.UI.Control.PreRenderRecursiveInternal() +50
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2041
--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433
hi, like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right? so, is there something that i can use to hold those records so that i can do the delete and update just on those records and don't need to query twice? or is there a way to do that in one go ?thanks in advance!
View 1 Replies View RelatedHello. I currently have a website that has a table on one webpage. When a record is clicked, the primary key of that record is transfered in the query string to another page and fed into an sql statement. In this case its selecting a project on the first page, and displaying all the scripts for that project on another page. I also have an additional dropdownlist on the second page that i use to filter the scripts by an attribute called 'testdomain'. At present this works to an extent. When i click a project, i am navigated to the scripts page which is empty except for the dropdownlist. i then select a 'testdomain' from the dropdownlist and the page populates with scripts (formview) for the particular test domain. what i would like is for all the scripts to be displayed using the formview in the first instance when the user arrives at the second page. from there, they can then filter the scripts using the dropdownlist.
My current SQL statement is as follows.
SelectCommand="SELECT * FROM [TestScript] WHERE (([ProjectID] = @ProjectID) AND ([TestDomain] = @TestDomain))"
So what is happening is when testdomain = a null value, it does not select any scripts. Is there a way i can achieve the behaivour of the page as i outlined above? Any help would be appreciated.
Thanks,
James.
Can anybody shed some light on my issue? I'm reviewing my client's event log and finding the repetitive warning "A significant part of sql server process memory has been paged out. This may result in a performance degradation." This has been an ongoing issue that I've searched high and low to try and resolve.
The system is Windows Server 2003 Standard x64 Edition - SQL Server 2005 SP2 - 2.33 GHz - 4 GB Ram
Thanks in advance for any help!
Hi all
I frequently see the following message on SQL Server log
2008-06-09 07:46:18.17 spid3s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 1079156, committed (KB): 17156388, memory utilization: 6%.
What does it indicates and what appropriate action has to be taken to fix it.
The database runs on
SQL 2005 Dev 64-bit SP2 9.00.3042.00
Win 2003 standard x64 SP2 16GB RAM
Thanks.
Is there a way, other than simply changing the format of the SRS report, to create an old fashond Paged style report - similar to the ones you can get from Crystal Reports?
View 6 Replies View RelatedWhat is the recommended mechanism for selecting paged results from SQL.
Presently I pass various params including the request Max Items Per Page and the requested page.
The I execute the query as a count with the search params.
Then comes the paging logic, which validates the page number against the request page and number of hits etc.
Then a temp table and record variables are created for the results.
Then I run the query again with a cursor and select the appropriate Items into the temp table based on the paging values (First Item and Last Item).
Then I return the temp table & some additional return params with the Total Hits etc.
The Stored procedure is accessed via an ADO.Net client and the system.data.IDBReader populates a .Net strongly typed collection and is for read only display.
Thanks for any input,
Martin.
I have a paging query that uses OpenQuery to access the MSIDXS indexing service and return records where text is matched. The query looks like this:
select top 25 * from
OpenQuery(FileSystem, 'SELECT DocTitle, FileName, Rank, Size, Create from Scope() where contains(''report'') ORDER BY Rank DESC')
as Q, LookupDocuments_dbv AS v where Q.FileName=v.Loc_cst and catname_cst='Main'
and (v.docid_cin not in (select top 600 v.docid_cin from
OpenQuery(FileSystem, 'SELECT DocTitle, FileName, Rank, Size, Create from Scope() where contains(''report'') ORDER BY Rank DESC')
as Q, LookupDocuments_dbv AS v where Q.FileName=v.Loc_cst order by v.docid_cin)) order by v.docid_cin
Not the exact query, but it is just as slow as the query in my program. This query will retrieve the top 25 records starting at record 601 where 'report' is found in a document and where the category = 'Main'
What is really weird about this is that in management studio this query will sometimes return in like 3 seconds, while the exact same query will take 30 seconds using ASP.Net / System.Data.SqlClient (and I mean it actually sits on sqlCommand.ExecuteReader() for a long period of time). Also, does management studio cache the table randomly, because sometimes it'll take forever and other times it'll be fast.
I do not have response issues with this paging query in non-MSIDXS queries.. I can for example return the 5,000th - 5,025th of 50,000 records in less than a second while the OpenQuery manages to get progressively slower very quickly (like 3 extra seconds per page of 25 records, so it takes like 20 seconds to return 150-175 of 600).
I have implemented the Stored Procedure "RowCount" method of controllingpaged recordsets in ASP as shown in this pagehttp://www.aspfaq.com/show.asp?id=2120.I have had to make heavy alterations to the code in order for it to workwith my application.It all worked fine until I tried to sort my data by a field which containeda NULL value (even in just one record). Before I added additional criteriafor the Unique_ID field the stored procedure returned zero records whensorting by such a field. I then added the Unique_ID criteria to the ORDER BYand WHERE clauses (as seen below) and it excludes records.In short obviously what I want this to do is order by fields that containNULL values (e.g. fld4 in sample data below). It currently pages and ordersperfectly in fields which have data in every row.If anyone reads this and helps me, thanks *very* much in advance.(P.S. sorry about the horrible state of my code but I've been messing withit trying to get this fixed for ages)(P.P.S the reason for passing in the long SQL string as a parameter in theSP is because the string is generated using complicated loops and I foundthis much easier in ASP than doing it in SQL)******************************************Here is an example of a call to the procedure from ASP:EXEC st_paging_rowcount 7, 1, 50, 'SET ROWCOUNT 50 SELECT utbl7.*,utbl6_1.fld1 AS fld3_Name FROM utbl7 LEFT OUTER JOIN utbl6 utbl6_1 ONutbl7.fld3 = utbl6_1.Unique_ID WHERE utbl7.fld5 >= @fld1val ORutbl7.Unique_ID >= @uniqueid ORDER BY utbl7.fld5',5******************************************Here is the SQL SP:CREATE PROCEDURE st_paging_rowcount@tableid INT,@pagenum INT = 1,@perpage INT = 50,@finalselect NVARCHAR(1500),@sortfield INTASBEGINSET NOCOUNT ONDECLARE@ubound INT,@lbound INT,@pages INT,@rows INT,@querystring1 NVARCHAR(200),@querystring2 NVARCHAR(200),@querystring3 NVARCHAR(200)SELECT @querystring1 = N'SELECT @rows = COUNT(*),@pages = COUNT(*) / @perpageFROM utbl' + CAST(@tableid AS NVARCHAR(15)) + ' WITH (NOLOCK)'--SELECT GOGOGO = @querystring1EXEC sp_executesql@querystring1,N'@rows INT OUTPUT, @pages INT OUTPUT, @perpage INT',@rows OUTPUT, @pages OUTPUT, @perpageIF @rows % @perpage != 0 SET @pages = @pages + 1IF @pagenum < 1 SET @pagenum = 1IF @pagenum > @pages SET @pagenum = @pagesSET @ubound = @perpage * @pagenumSET @lbound = @ubound - (@perpage - 1)SELECTCurrentPage = @pagenum,TotalPages = @pages,TotalRows = @rows-- this method determines the string values-- for the first desired row, then sets the-- rowcount to get it, plus the next n rowsDECLARE @fld1val NVARCHAR(64)DECLARE @uniqueid INTSELECT @querystring2 = N'SET ROWCOUNT ' + CAST(@lbound AS NVARCHAR(15))+ 'SELECT@fld1val = fld' + CAST(@sortfield AS NVARCHAR(15)) + ', @uniqueid= Unique_IDFROMutbl' + CAST(@tableid AS NVARCHAR(15)) + ' WITH (NOLOCK)ORDER BYfld' + CAST(@sortfield AS NVARCHAR(15)) +', Unique_ID 'SELECT test0 = @querystring2EXEC sp_executesql@querystring2,N'@fld1val NVARCHAR(64) OUTPUT, @uniqueid INT OUTPUT',@fld1val OUTPUT, @uniqueid OUTPUT--SELECT test = @finalselectEXEC sp_executesql@finalselect,N'@fld1val NVARCHAR(64), @uniqueid INT',@fld1val, @uniqueidSET ROWCOUNT 0END*********************************************Here is some sample data from the table (e.g. would like to sort by fld4):Unique_ID Date_Added Who_Added Locked fld1fld2 fld3 fld4----------- --------------------------- ----------- ----------- ------------------------------------------------------------3 2005-09-16 16:12:30.200 1 0 SmithJohn 1 NULL4 2005-09-16 16:12:41.013 1 0 JonesChris 1 NULL6 2005-09-16 16:13:10.187 1 0 StamovStilian 1 NULL7 2005-09-16 16:19:15.437 1 0 LewickiSteve 1 Colchester8 2005-09-16 16:19:36.937 1 0 JamesPhil 1 NULL9 2005-09-16 16:20:35.327 1 0 LeroyDidier 1 NULL
View 3 Replies View RelatedWe are running SQL 2005 SP2 x64 on Windows 2003 SP2 X64.
Server specs:
Quad Core, each core runs at 2.33Ghz.
4GB memory
OS and SQL on RAID 1 set
Transaction log on RAID 1 set
Databases on RAID 5 set
-The SQL service account is a domain account with the "lock pages in memory" rights.
-The min memory use for sql server is 3000MBand the maximum is 2147483647MB.
For whatever reason, the sqlsrvr.exe process uses much less physical memory than the Virtual Memory (VM). When I look at the Task manager, I see 230MB for Mem Usage column and 300MB for VM size column under full workload. Why is the SQL server process being paged out? I have 3GB for it to use. Should I be concerned about the my SQL server?
I didn't configure the AWE as MS doesn't recomment using it on x64 bit systems. When I do "dbcc memorystatus", I get the following:
VM Reserved 4269536
VM Committed 196680
AWE Allocated 0
Reserved Memory 1024
Reserved Memory In Use 0
I thought granting "lock pages in memory" rights to the sql service account automatically enables AWE in x64 bit version of SQL 2005 standard server.
Thanks
Hi All
I see the following message in SQL Server logs. What does this indicates. What should I do to avoid this.
2008-05-20 01:25:02.12 spid2s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 33920, committed (KB): 15142988, memory utilization: 0%.
The server configuration is
SQL 2005 Dev edition SP2 64bit
Win 2003 R2 SP2 Standard X64 editioin
RAM size is 16GB
Thanks.
ok can someone tell me why i get two different answers for the same query. (looking for last day of month for a given date)
SELECT DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(m, 0, CAST('12/20/2006' AS datetime)) + 1, 0)) AS Expr1
FROM testsupplierSCNCR
I am getting the result of 01/01/2007
but in query analizer I get the result of
12/31/2006
Why the different dates
Hi, I am trying to implement filtering on a custome paged stored Procedure, here is my curent Stored Procedure which doesn't error on complie or run but returns no records. Anyone got any ideas on how to make this work???<Stored Procedure>set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo-- =============================================-- Author: Peter Annandale-- Create date: 22/10/2006-- Description: Get Filtered Names-- =============================================ALTER PROCEDURE [dbo].[proc_NAMEFilterPaged] -- Add the parameters for the stored procedure here @startRowIndex int, @maximumRows int, @columnName varchar(20), @filterValue varchar(20)ASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;SELECT CODE, LAST_NAME, Name, TYPE, NUMBER FROM (SELECT n.CODE, n.LAST_NAME, n.FIRST_NAME + ' ' + n.MIDDLE_NAME AS Name, nt.TYPE, f.NUMBER, ROW_NUMBER() OVER(ORDER BY n.LAST_NAME) as RowNum FROM dbo.NAME n LEFT OUTER JOIN NAMETYPE nt ON n.NAME_TYPE = nt.NAME_TYPE LEFT OUTER JOIN FUNERAL f ON n.CODE = f.DECEASED WHERE @columnName LIKE @filterValue ) as NameInfo WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) -1END </Stored Procedure> Any assistance would be greatly appreciated.. Regards..Peter.
View 1 Replies View RelatedIn my SQL Server Errorlog, I see the below error. The system has 8 GB of RAM with enough free RAM, something I can do to prevent this alert? (Note: I have no MIN/MAX memory set on this Instance)
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 328 seconds. Working set (KB): 76896, committed (KB): 167628, memory utilization: 45%.
On a SQL Server 2005 x64 Standard Edition cluster I get the error listed below and then the SQL server service restarts. The SQL server is unavailable for 5-10 minutes during that time. Any ideas?
Error:
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 647 seconds. Working set (KB): 11907776, committed (KB): 28731732, memory utilization: 41%%.
-- The 3rd query uses an incorrect column name in a sub-query and succeeds but rows are incorrectly qualified. This is very DANGEROUS!!!
-- The issue exists is in 2008 R2, 2012 and 2014 and is "By Design"
set nocount on
go
if object_id('tempdb.dbo.#t1') IS NOT NULL drop table #t1
if object_id('tempdb.dbo
[code]....
This succeeds when the invalid column name is a valid column name in the outer query. So in this situation the sub-query would fail when run by itself but succeed with an incorrectly applied filter when run as a sub-query. The danger here is that if a SQL Server user runs DML in a production database with such a sub-query which then the results are likely not the expected results with potentially unintended actions applied against the data. how many SQL Server users have had incorrectly applied DML or incorrect query results and don't even know it....?
For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?
I want my output to be
CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc
Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.
I want to do it using SELECT. Is it possible?
When I run the following query from Query Analyzer in SQL Serer 2005, I get a message back that says.
Command(s) completed successfully.
What I really need it to do is to display the results of the query. Does anyone know how to do this?
declare @SniierId as uniqueidentifierset @SniierId = '85555560-AD5D-430C-9B97-FB0AC3C7DA1F'declare @SniierAlias as nvarchar(50)declare @AlwaysShowEditButton as bitdeclare @SniierName as nvarchar (128)/* Check access for Sniier */SELECT TOP 1 @SniierName = Sniiers.SniierName, @SniierAlias = Sniiers.SniierAlias, @AlwaysShowEditButton = Sniiers.AlwaysShowEditButtonFROM SniiersWHERE Sniiers.SniierId=@SniierId
linking two tables together to get an end result
find below the code i have used
The first part of the query provides me with the info i need
SELECT sub.*,
case when rm_sales_band = '2M to 4M' then 'Kirsty' else RM end as rm
into #rmtmp
[Code].....
I have a query that performs a comparison between 2 different databases and returns the results of the comparison. It returns 2 columns. The 1st column is the value of the object being compared, and the 2nd column is a number representing any discrepancies.What I would like to do is use the results from this 1st query in the where clause of another separate query so that this 2nd query will only run for any primary values from the 1st query where a secondary value in the 1st query is not equal to zero.I was thinking of using an "IN" function in the 2nd query to pull data from the 1st column in the 1st query where the 2nd column in the 1st query != 0, but I'm having trouble ironing out the correct syntax, and conceptualizing this optimally.
While I would prefer to only return values from the 1st query where the comparison value != 0 in order to have a concise list to work with, I am having difficulty in that the comparison value is a mathematical calculation of 2 different tables in 2 different databases, and so far I've been forced to include it in the select criteria because the where clause does not accept it.Also, I am not a DBA by trade. I am a system administrator writing SQL code for reporting data from an application I support.
Hi,New to .Net and SQL. I have two tables that I have joined together. RentalControl_Main has the rental informationd and an Adjuster ID that links to the ADjuster table and the adjusters name. I am trying to create a report that gives the "Single" adjuster name and the totals for all of their contracts. I have a details report that gives each contract info. for each specific adjusters rentals. However, I want to just list the adjuster once and give all of their totals. In my SQL statement I have all of it written out and just need to knowwhat to do in place of 'Alex Early' that will give me all of the distinct adjusters.Do I need to code this on the page with a do while loop?Appreciate any help.SELECT SUM(dbo.RentalControl_Main.Rate) / COUNT(dbo.RentalControl_Main.Rate) AS AmtAvg, SUM(dbo.RentalControl_Main.DaysBilled) / COUNT(dbo.RentalControl_Main.DaysBilled) AS DayAvg, SUM(dbo.RentalControl_Main.Rate * dbo.RentalControl_Main.DaysBilled) / COUNT(dbo.RentalControl_Main.Rate) AS TotAvgFROM dbo.RentalControl_Main INNER JOIN dbo.RentalControl_Adjuster ON dbo.RentalControl_Main.AdjusterID = dbo.RentalControl_Adjuster.AdjusterIDWHERE (dbo.RentalControl_Adjuster.AdjusterName = 'Alex Early' AND (dbo.RentalControl_Main.DateClose IS NOT NULL) AND (dbo.RentalControl_Main.AgencyID = '2')
View 1 Replies View RelatedI was wondering if there was a keyword that would allow you to return the number of results from a query such as (this is fake just giving an example so that someone can give me the real answer)
select TotalReturned
from table
where field=""
I am a beginner with SQL, so please excuse any ignorance here.
Under SQL 6.5, when executing a SELECT DISTINCT query against a table in a certain database, the results were returned alphabetized. Now, after migrating the database to SQL 7.0, the same query does not return the results alphabetized. If I add an index for the column specified in the query to the table, the results come back alphabetized. Is there any way to correct this other than adding indexes for all columns in all tables that are queried in this manner?
TIA,
Paige
I am running the same exact query in SQL 2000 and I get different results. Should I try running DBCC CHECKDB and look for errors?
View 5 Replies View Related
Hi,
I have this line of code that when run in the query doesn't return anything:
srv.AGENT_S_ACC_CODE IN ('" & Replace(Replace(Parameters!arcode.Value," ",""),",","','") & "')
The parameter is setup and the query works when I hardcode it with a value but will not return anything with this, does anyone know why?
Thanks,
Rhonda
Hi guys, say i wrote a query that returns 1,000 records.. what kinda query could i write that only returns say the first 50 records of the 1,000 recs..
View 4 Replies View RelatedHello,
I want to select all the customerIDs where an email address exists. Easy: select customerid from customers where emailaddress = @emailaddress
Now I want to use the resulted customerIDs from the above query as a parameter to select all the email addresses with emailstatus equal to 3.
How do I create this type of while statement in a stored procedure? I prefer to not create a temporary table.
My idea was to do it like this:
select emailaddress from emailaddresseswhere emailstatus = 3 and customerid = (select customerid from customers where emailaddress = @emailaddress)
This doesn't seems to work. I get the error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
thanks
I have a pretty big SQL query with a one to many sort of relationship...
There are client accounts that we're reporting on. Each account has four different historical categories attached. Each historical category can have maybe fifty entries in each, sorted by date.
I need to figure out how to grab the unique accounts and show only the three most recent results per each historical subcategory with the account... the three most current results from each of the four subcategories, displayed by the parent client account number.
I've created four views to isolate the subcategories as a start, thinking I could bring them into a parent query ...
but my question would be... how do I generate JUST the top three historical transactions by account number? If I select TOP 3, I get only the 3 newest in the MASTER LIST, not per client account, which is what I need to do.
Does this make sense? I could really use a good SQL helping hand with this one.
Thank you!
Can anyone give me the basics of speeding up reports that use queries or views or nested views?
Current reports are now taking over 2 minutes to show.We have thousands and sometimes even millions of records to report against.Queries have 4 and 5 table joins etc.
We are using ASP.NET 2.0 in Visual Studio 2005 and Crystal Reports.
Thanks
Hi,
I would like to use the result table of the first query in a number of other queries. How do I do this ?
Thanks.
I have a database with some over normalized tables in it. The best I can do with one query is get the file ID. In the second query I want to get all the file names, based on all the fileID's I got from the first query. How would I go about doing this?
View 5 Replies View Related