Is there any difference in the way Query Analyser will look at a table when you use a 'select * from table' statement compared to using the Open Table, then Return Top from within Enterprise Manager?
We have a 41MB table which contains 50,000 rows. When I try 'select * ...' in QA it seems to run and run (over 5 mins before I have to stop it). One of my users seems to think that using the Open Table, Return Top 50,000 in EM gives pretty immediate results. I'm not so sure however, since if you then attempt to scroll down to the last row it appears to take just as long as the above select statement.
I have a stored procedure that selects the unique Name of an item from one table.
SELECT DISTINCT ChainName from Chains
For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.
SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName
Each row of the result set returned by the stored procedure would contain:
ChainName, Array of StoreNames (or comma separated strings or whatever)
Hi...I have table who contain words in Hebrew (right to left language), and when i try to select:WHERE (Topic LIKE '%Hebrew word%')I get no rows...Any idea? thanks...
I have table that I need to retrieve the top 2 records, the issue is I have 3 records with the same date, but I only want the first 2. Each record looks something like this.
I have to be able to add the series up of only the first two records for each id based on date. Here is a sample query
select sum(series), date from table group by date order by sum(series) desc
This gives me the total for all three and gives it to me in descending order. I need the records for set 1 and 2 of each of the Id. There are many records but the date and the setnr doesn't duplicate.
I am new to SQL and I am trouble-shooting a problem with a home-grown app someone else wrote using PERL. It has a web interface with names of boards. I found the program where i need to add the board names into and did that. The new board names show up in the drop-down list in the Web page for the app. Alerts are sent to the new board names and show up on the new boards. Users are granted access to the new boards and they can clear items off the new boards. Yet when i try to use a report function for the new boards i added, nothing is returned. I even ran a simple SQl select statement specifying the new board names and nothing is returned. The older board names, some of which i added myself, return values. I don't know what is going on. Any help is appreciated.
Not sure if this is possible, but maybe. I have a table that contains a bunch of logs. I'm doing something like SELECT * FROM LOGS. The primary key in this table is LogID. I have another table that contains error messages. Each LogID could have multiple error messages associated with it. To get the error messages. When I perform my first select query listed above, I would like one of the columns to be populated with ALL the error messages for that particular LogID (SELECT * FROM ERRORS WHERE LogID = MyLogID). Any thoughts as to how I could accomplish such a daring feat?
I have 2 tables, Jobs and Categories.Each job belongs to a category. At present, I am returning all categories as follows:SELECT categoryID, categoryName FROM TCCI_CategoriesWhat I'm trying to do, is also return the number of jobs assigned to each category, so in my web page display, it would show something like this:Engineering(5)Mechanical(10) etc.My db currently has 5 categories, with only one job assigned to a category. I tried the following sub-query, but instead of returning all the categories with their job counts, it just returns the category that has a job assigned to it:SELECT c.categoryID, c.categoryName, COUNT(j.jobID)FROM TCCI_Categories c, (SELECT jobID, categoryID FROM TCCI_Jobs) jWHERE j.categoryID = c.categoryIDGROUP BY c.categoryID, c.categoryName, j.jobIDThis is the output when I run the query:categoryID categoryName Column1 ---------------- ---------------------- ------------------------------32 Engineering 1 How would I fix this?
Can someone look at this and tell me where I went wrong? I'm trying to return all duplicate rows that have the same lastName and Address. It returns rows but they don't look like dups.SELECT TOP (100) PERCENT dbo.tblClient.LastName, dbo.tblClientAddresses.Address FROM dbo.tblClient INNER JOIN dbo.tblClientAddresses ON dbo.tblClient.Client_ID = dbo.tblClientAddresses.Client_ID GROUP BY dbo.tblClient.LastName, dbo.tblClientAddresses.Address HAVING (COUNT(dbo.tblClientAddresses.Address) > 1) ORDER BY dbo.tblClientAddresses.Address
Hi, nice to meet you all. I'm new developer and need help. Normally i use "sqldatasource1.select()", but need to fill argument, what is argument? Actually the main point i wan to return a value from stored procedure or got any methods can do this way? Hope you all can help me.... Thank
Hello. I a SP with 2 select statements. If the first returns no results it does the second select. This works fine if I test it on the sql server. But when I try it in a webform using a sqldatasource it only returns a result if the first Select returns rows. If not It is an empty set (where I know it should not be. It returned resultrs on the SQL server). This is the code:1 ALTER PROCEDURE [SPName] 2 3 @SearchFor varchar(150) -- search string to compare with 4 5 AS 6 7 SELECT p.UserName, 8 9 (Select ImageName from ProfileImages Where IsMainImage = 1 And ProfileID = p.ProfileID) as ImageName, 10 11 (SELECT COUNT(*) FROM auditions WHERE UserID = p.ProfileID) AS AuditionCount 12 13 FROM Profiles p WHERE UserName = @SearchFor 14 15 IF (@@ROWCOUNT = 0) 16 17 Begin 18 19 SELECT p.UserName, 20 21 (Select ImageName from ProfileImages Where IsMainImage = 1 And ProfileID = p.ProfileID) as ImageName, 22 23 (SELECT COUNT(*) FROM auditions WHERE UserID = p.ProfileID) AS AuditionCount, 24 25 DIFFERENCE(p.UserName, @SearchFor) as Similarity 26 27 FROM profiles p WHERE SOUNDEX(p.UserName) = SOUNDEX( @SearchFor ) 28 29 ORDER BY Similarity 30 31 END 32 33 RETURN Now If i change the SP to test it to the code below, it does work in both the webform and on the sql server: 1 ALTER PROCEDURE [SPName] 2 3 @SearchFor varchar(150) -- search string to compare with 4 5 AS 6 7 DECLARE @RowCount int 8 SET @RowCount = 0 9 10 IF (@ROWCOUNT = 0) 11 12 Begin 13 14 SELECT p.UserName, 15 16 (Select ImageName from ProfileImages Where IsMainImage = 1 And ProfileID = p.ProfileID) as ImageName, 17 18 (SELECT COUNT(*) FROM auditions WHERE UserID = p.ProfileID) AS AuditionCount, 19 20 DIFFERENCE(p.UserName, @SearchFor) as Similarity 21 22 FROM profiles p WHERE SOUNDEX(p.UserName) = SOUNDEX( @SearchFor ) 23 24 ORDER BY Similarity 25 26 END 27 28 RETURNOnce again, Everything works as it should (for both versions), if I am testing it directly on the SQL server. Can anyone Help me on this? I am stumped and cannot find any answers about this.
I have an unusual problem. I am using VB.Net 2003 and sqlexpress using .NET dataset to insert records into an timecards table. After inserting several records I tried a 'Select * from timecards' and the inserted records where not selected. if I 'select * from timecards order by employee' ( or any other field) the inserted records are selected! The table was created by an Access Upsize command.
I used Express Manager ( XM ) to try the select statements. That is how I isolated the problem. Even using a "Select * from timecards where employee = 'test' " returns the inserted test records. I found that if I use a WHERE or ORDER BY clause in the SELECT statement to .fill the .net dataset, all records are returned.
I am familiar with DB2 but I am a newbie at VB.NET and MSSQL
It's a very strange thing! I havea a table called invoices, and a table calle customer payments which has the invoiceID of the payment.
I have many invoices that haven't been paid (so they don't have a record on the customer payments). I know this, as i can for example do: select * from invoices where invoiceID = 302247 (and i'll get one result) select * from customer_payments where invoice = 302247 (and i'll get none results)
however, if i do the following: select * from invoices where invoice_id not in (select invoice_id from customer_payments) I get nothing!!!???
It doesn't make any sense, as I should get at least 300 (including the 302247) - both invoiceids fields are int... so i just don't understand what's wrong?
I need to access a table and return 3 values from it in the samerecordset - ie one iteration of the recordset will have 3 values fromthe same database, I have looked at sub queries but they dont seem tobe able to do what i want.I would be grateful for any guidanceS
Can anyone just point me in the right direction. All I want to do is add some T-SQL to an existing stored procedure to return the number of rows selected into a return value.Does anyone know how to do this?
Hello all, I have been working with a DetailsView control for the past week and it is a great control, but also lacks on some departments. Anyhow I need to know what the best approach for this scenerio would be? I have a SqlDataSource" <asp:SqlDataSource ID="SqlUpsertAffiliateDetails" runat="server" ConnectionString="<%$ ConnectionStrings:connectionstring %>" SelectCommand="SELECT am.affiliate_id AS AffiliateId, am.member_id AS MemberId, m.First_Name, m.Last_Name, am.category_id AS CategoryId, ac.category_name, am.profile_web_address AS WebAddress, am.profile_email_1 AS Email, am.comments AS Comments, am.date_modified FROM tAffiliateMaster AS am WITH (NOLOCK) INNER JOIN tAffiliateCategories AS ac WITH (NOLOCK) ON am.category_id = ac.category_id INNER JOIN rapdata..Member AS m WITH (NOLOCK) ON am.member_id = m.Member_Number WHERE (am.affiliate_id = @AffiliateId)" UpdateCommand="spUpsertAffiliateProfile" UpdateCommandType="StoredProcedure"> <SelectParameters> <asp:QueryStringParameter Name="AffiliateId" QueryStringField="affiliate_id" /> </SelectParameters> <UpdateParameters> <asp:Parameter Name="Action" Type="Byte" DefaultValue="2" /> </UpdateParameters> </asp:SqlDataSource> And my SP:/* 09-19-07 Used to update affiliate profile */
CREATE PROCEDURE spUpsertAffiliateProfile @Action tinyint, @AffiliateId int, @MemberId int = -1, @CategoryId int, @WebAddress varchar(50), @Email varchar(50), @Comments varchar(1500) AS
SET NOCOUNT ON
-- Find errors first, check is not needed if deleting IF @Action <> 3 IF NOT EXISTS (SELECT Member_Number FROM rapdata..Member_Association WHERE Member_Number = @MemberId AND Status = 'A' AND Association_ID = 'TRI' AND Bill_Type_Code LIKE '%AF%') BEGIN SELECT retval = 'A qualified member ID was NOT found. Action Failed.', errorcount = 1, 0 AS affiliate_id RETURN END IF @Action = 1 IF EXISTS (SELECT member_id FROM tAffiliateMaster WHERE member_id = @MemberId) BEGIN SELECT retval = 'This member has already been listed. Action Failed.', errorcount = 1, 0 AS affiliate_id RETURN END
IF @Action = 1 AND @AffiliateId = 0-- insert BEGIN INSERT INTO tAffiliateMaster (member_id, category_id, profile_web_address, profile_email_1, comments) VALUES (@MemberId, @CategoryId, @WebAddress, @Email, @Comments)
SELECT retval = 'Record Entered', errorcount = 0, @@IDENTITY AS affiliate_id RETURN END
ELSE IF @Action = 2 AND @AffiliateId > 0-- update BEGIN UPDATE tAffiliateMaster
SET category_id= @CategoryId, profile_web_address=@WebAddress, profile_email_1=@Email, comments=@Comments
WHERE affiliate_id = @AffiliateId AND member_id = @MemberId
SELECT retval = 'Record Updated', errorcount = 0, @AffiliateId AS affiliate_id RETURN END
ELSE IF @Action = 3 AND @AffiliateId > 0-- delete BEGIN DELETE tAffiliateMaster
WHERE affiliate_id = @AffiliateId
SELECT retval = 'Record Deleted', errorcount = 0, 0 AS affiliate_id RETURN END GO
My question is how will I be able to return the retval? Will I need to do it within the code behind of the SqlDataSource Updated Event? Thanks!
I m writing a stored procedure to query a table Population that has the following fields - CityId, CityName, Plus0, Plus10, Plus20, Plus30, Plus40, Plus50, Plus60, Plus70, Plus80. The field Plus0 contains the number of people of age > 0 living in the city, Plus10 contains the number of people of age > 10 living in the city and so on. Given the city id and age groups like 20To40, 50To60, 40Plus, etc., I should be able to query the number of people in the city corresponding to the requested age group. Note that if the requested age group was 20To60, I need to make use of only 2 fields Plus20 and Plus60 in the table to compute this value. And if the requested age group was 40Plus, then I need only the value in the field Plus40. The problem is that a wide variety of age groups can be requested like 0Plus, 10Plus, ... , 80Plus, 0To10, 0To20, 0To30, .... 70To80.
Which is the most effecient way to handle this ?
1. Have a stored procedure that returns all the fields even though only 1 or 2 of them would be actually used ?
In this case, if I returned data for a large number of cities then there would be a lot of unnecessary information that was returned by the query. Lots of data would be passed through the network though most of it would not be used.
2. Have a stored procedure that takes in parameters @Plus0, @Plus10, @Plus20, .. @Plus80 that are bits indicating whether the field was required or not and then using a CASE statement to return values for a field only if the corresponding bit parameter was set, and returning NULL if the corresponding bit paramter was not set ?
In this case, I would be returning NULL for all those fields that were not required for a particular age group. This would save some network bandwidth, wouldn't it ?
3. Pass in the age group itself (ex: 0To20) as a parameter to the stored procedure and have lots of IF statements, one for each age group, that return only the fields that are needed for that age group.
This leads to a lot of code repitition.
4. Use a similar approach as above but use dynamic SQL queries to avoid code repitition.
But using dynamic SQL queries can affect the performance of the stored procedure as they need to be compiled each time.
ALTER PROCEDURE [dbo].[sp_SelectMostRecentArticle]
AS BEGIN
DECLARE @article_id INT SELECT @article_id = ( SELECT TOP 1 article_id FROM article ORDER BY article_id DESC )
DECLARE @comment_count INT SELECT @comment_count = ( SELECT COUNT(comment_id) FROM comment JOIN article ON article_id = comment_article_id GROUP BY article_id HAVING article_id = @article_id )
SELECT TOP 1 article_id, article_author_id, article_title, article_body, article_post_date, article_edit_date, article_status, article_author_id article_author_ip, author_display_name, category_id, category_name--, comment_count AS @comment_count
FROM article
JOIN author ON author_id = article_author_id JOIN category ON category_id = article_category_id
GROUP BY article_id, article_title, article_body, article_post_date, article_edit_date, article_status, article_author_ip,article_author_id, author_display_name, category_id, category_name
HAVING article_id = @article_id
END GO
as you can see, im trying to return a comment_count value, but the only way I can do this is by defining the variable.
I have had to do it this way, because I cannot say COUNT(comment.comment_id) AS comment_count or it returns an error that it cant reference the comment.comment_id.
But when change it to FROM article, comment; I get errors about the article_author_id and article_comment_id.
And i cant add a join, because it would return the amount of rows of the comment...
unless someone could help with what i Just decribed (as i would prefer to do it this way), how would i return the variable value as part of the select statement?
I have the following query:SELECT Month, Sum(Hits) AS Hits FROM tblHits GROUP BY Month ORDER BYMonthUnfortunately it only returns rows for months that have data assignedto them.How can I tweak this so that months 1-12 are returned, and Hits = 0 formonths with no data in the base table?Thanks.
I was curious...Is there a way to select distinct on a combination of some fields andthe for each record returned also get the other fields of anarbitrarily chosen record matching the fields in the distinct record.For example, if I have a select distinct on say three fields:SELECT DISTINCT Code1, Code2, Code3but the table also has other fields, maybe Foo1 and Foo2, and I wantFoo1 and Foo2 to also be displayed. Since there may be multiplerecords that match a particular Code1, Code2, Code3, then I just wantone of those to be arbitrarily chosen.
So, really the key is the PROJID & the ACTIVITY_NAME (really, there's a unique column ID, but for this question, I'll leave it at that).
(Though this should be much easier to accomplish in code, the system is not built that way so) Is there a good way that I could return a status for a given PROJID based on whether a row exists for a given PROJID). In other words, ultimately, I would like to return something like this:
where the activity order (in this case) is Prepro, QA, Delivery. So because a Delivery row exists for PROJID 2, then the LAST_ACTIVITY would return "Delivery" and because only Prepro exists for PROJID 1, the LAST_ACTIVITY returned would be Prepro
If I try to run the code below, and even one of the values in the INNER JOIN statements is NULL, the DataReader ends up with zero rows. What I need is to see the results even if one or more of INNER JOIN statements has a NULL value. For example, if I want info on asset# 2104, and there's no value in the DriverID field, I need the rest of the data to display and just have the lblDriverName by blank. Is that possible?
<code> Sub BindSearchGrid() Dim searchUnitID As String Dim searchQuery As String searchUnitID = tbSearchUnitID.Text lblIDNum.Text = searchUnitID searchQuery = "SELECT * FROM Assets " & _ "INNER JOIN Condition ON Condition.ConditionID = Assets.ConditionID " & _ "INNER JOIN Drivers ON Drivers.DriverID = Assets.DriverID " & _ "INNER JOIN Departments ON Departments.DepartmentID = Assets.DepartmentID " & _ "INNER JOIN AssetCategories ON AssetCategories.AssetCategoryID = Assets.AssetCategoryID " & _ "INNER JOIN Store ON Store.[Store ID] = Assets.StoreID WHERE RTRIM(Assets.[Unit ID]) = '" & searchUnitID & "'"
Dim myReader As SqlDataReader myReader = Data.queryDB(searchQuery) While myReader.Read If Not IsDBNull(myReader("Store Name")) Then lblStrID.Text = myReader("Store Name") If Not IsDBNull(myReader("AssetCategory")) Then lblAsstCat.Text = myReader("AssetCategory") If Not IsDBNull(myReader("Condition Description")) Then lblCondID.Text = myReader("Condition Description") If Not IsDBNull(myReader("DepartmentName")) Then lblDepID.Text = myReader("DepartmentName") If Not IsDBNull(myReader("Unit ID")) Then lblUnID.Text = myReader("Unit ID") If Not IsDBNull(myReader("Year")) Then lblYr.Text = myReader("Year") If Not IsDBNull(myReader("Make")) Then lblMk.Text = myReader("Make") If Not IsDBNull(myReader("Model")) Then lblMod.Text = myReader("Model") If Not IsDBNull(myReader("Mileage")) Then lblMile.Text = myReader("Mileage") If Not IsDBNull(myReader("Vin Number")) Then lblVinNum.Text = myReader("Vin Number") If Not IsDBNull(myReader("License Number")) Then lblLicNum.Text = myReader("License Number") If Not IsDBNull(myReader("Name")) Then lblDriverName.Text = myReader("Name") If Not IsDBNull(myReader("DateAcquired")) Then lblDateAcq.Text = myReader("DateAcquired") If Not IsDBNull(myReader("DateSold")) Then lblDtSld.Text = myReader("DateSold") If Not IsDBNull(myReader("PurchasePrice")) Then lblPrPrice.Text = myReader("PurchasePrice") If Not IsDBNull(myReader("NextSchedMaint")) Then lblNSM.Text = myReader("NextSchedMaint") If Not IsDBNull(myReader("GVWR")) Then lblGrVWR.Text = myReader("GVWR") If Not IsDBNull(myReader("GVW")) Then lblGrVW.Text = myReader("GVW") If Not IsDBNull(myReader("Crane Capacity")) Then lblCrCap.Text = myReader("Crane Capacity") If Not IsDBNull(myReader("Crane Certification")) Then lblCrCert.Text = myReader("Crane Certification") If Not IsDBNull(myReader("Repair Cost")) Then lblRepCost.Text = myReader("Repair Cost") If Not IsDBNull(myReader("Estimate Replacement")) Then lblEstRep.Text = myReader("Estimate Replacement") If Not IsDBNull(myReader("SalvageValue")) Then lblSalVal.Text = myReader("SalvageValue") If Not IsDBNull(myReader("CurrentValue")) Then lblCurVal.Text = myReader("CurrentValue") If Not IsDBNull(myReader("Comments")) Then lblCom.Text = myReader("Comments") If Not IsDBNull(myReader("Description")) Then lblDesc.Text = myReader("Description")
I am writing some functions that work on a time series database of prices, ie volatility, correlation. I need to use the SELECT TOP syntax, but cannot do this with a variable, ie 'SELECT @x TOP * from prices'. My solution is to simply have a function for each potential period that will be looked at - 30day_volatility, 60day_volatility, etc. I looked at setting the ROWCOUNT variable but this is not allowed in functions. I haven't posted any DDL because I think the question is general enough - How do I return n ordered rows from a function without using SELECT TOP, or is there a way to use SELECT TOP with a variable that I am not aware of.
I have a table in my MS SQL 2000 database called News which has a field caled NewsDate. This is a standard Date field which stores the info in this format: 3/1/2001.
I want to create a query that returns one row for each year that there is a story.
For example, if I had this data... 3/1/2001, 6/27/2003. 9/17/2003, 1/1/2006, 4/5/2006
the query would return this result:
2001 2003 2006
This is the query I've started with:
SELECT DISTINCT NewsDate FROM News ORDER BY NewsDate DESC
What modifier can I apply to the NewsDate field to extract JUST the year from the table? If this were ASP I would try something like Year(Date), but, of course, I can't do that here.
Is this even possible? I've been looking up date functions, but haven't found anything that will work in a select statement. ANY and ALL advice will be greatly appreciated.
Using TSQL, I have a table that holds filenames of Pictures for products. Different products can be using the same picture. I need to select the filenames for a single product only if it does not exists for a different product.I have tried Where Exists (select FileName From Tbl where
Prod_Id = @var) AND NOT EXISTS(select FileName From Tbl where Prod_Id != @var) In the Select Statement.
I have a SP that return a dataset and I was thinking to execute that SP inside of other SP then catch the dataset to put into a variable or put into a temp table. What I know is you can not use recordset on output and input parameter in SP correct me if im wrong. I'm just wondering if I there is a work around in this scenario.
