RevNumber, CureTime, Bump1, Bump1Interval, Bump2,
Bump2Interval, DwellTime, LateToDrop, EarlyToDrop,
OverTime, CycleTime, ChangeTime, Date
FROM HeatInfo
WHERE (ShiftNumber = @ShiftNumber)
AND (Date >= @StartDate) AND (Date <= @EndDate)
This correctly returns 4 rows, which is great except that I would like to have these records Sorted by the Date. So I added the OrderBy command to the bottom of the query, like this
RevNumber, CureTime, Bump1, Bump1Interval, Bump2,
Bump2Interval, DwellTime, LateToDrop, EarlyToDrop,
OverTime, CycleTime, ChangeTime, Date
FROM HeatInfo
WHERE (ShiftNumber = @ShiftNumber)
AND (Date >= @StartDate) AND (Date <= @EndDate)
ORDER BY Date
This query returns 181 rows! whis is not great. What am I doing wrong here, I suppose that it is something extreamly obvious or I would probably have figured it out.
I am getting a SQLExcepetion error near , in this query string...so obvicously my query string is wrong... could someone help me get this query string right please...
Thanks "Select OrgID, OrgName From aspnet_OrgNames Where UserID = @UserID, OrgID = @OrgID"
Set @complete = (SELECT Count(pass) as num FROM tblAttempts WHERE userID = @userID AND pass = 1)
If (@complete = 24) Begin Set @status = 'OK' End Else Begin Set @status = 'NOT OK' End GO
CREATE PROCEDURE [dbo].[checkComplete] @userID int,@status varchar (6) output AS DECLARE @complete int SET @complete = (SELECT COUNT(pass) AS num FROM tblAttempts WHERE userID = @userID AND pass = 1) IF (@complete = 24) BEGIN SET @status = 'OK' ENDELSE BEGIN SET @status = 'NOT OK' ENDGO
For reference, I have tried changing intUserID to be a different value (e.g. 13) to ensure it is not that '4' in question - likewise for @complete = 24 (e.g. 23).
Any ideas where the 4 is coming from and what is wrong here?
I was doing a demo last night, something that I've done hundreds of times already. Last night was the first time that it has failed to work. I was trying to show what the sys.dm_db_missing_index_* DMVs can provide.
AdventureWorks database
I'm running the following query:
select city from person.address where city like 'A%'
This is supposed to produce a table scan which in turn will obviously cause SQL Server to detect that an index could be beneficial. However, it does a clustered index scan (yes, I know, basically the same thing) instead and I see absolutely nothing appear in the DMVs. I pulled the data out into a dummy table that did not have a primary key either using the following: select * into person.tmpaddress from person.address
I then execute the same query and get a table scan which is expected:
select city from person.address where city like 'A%'
However, it does not matter how much I execute that query or any other permutation of explicit query, absolutely nothing at all gets logged into the sys.dm_db_missing_index_* DMVs. I have also tried this same type of thing with several other tables in the AW database and can not find a single query which will cause anything to be logged to these DMVs. It seems that something is broken, but for the life of me, I can't figure out what is wrong. No weird settings, I'm running as sa, etc.
I can run queries like this in other databases and stuff gets immediately logged to the DMVs as expected. Any ideas?
/* Test table */ create table test (c1 char(1), c2 varchar(1)); insert into test values ('','');
/* Query */ select c1, len(c1) len_c1, c2, len(c2) len_c2 from test
The result of the len(c1) expression is 0. I would expect the correct result to be 1, since "c1" is a fixed-length character string type and the values are right-padded with spaces to fit the defined length, in this case 1.
I am getting an error from the case part of the select statement below which reads 'Incorrect Column Expression' then it quotes the case statement. All I am trying to do is convert and return the weight value to kilos if it was entered in pounds.
I am working with Excel, then within Excel I am using MS Query to query a database. I am trying to use the CAST function on a field with numbers (1,2 or 3 digits) so I can convert it to a text value with three digits, i.e. 1 would read 001, 12 would read 012, etc.
I am not using CAST in the design grid. Is this even possible?
I am modifying the underlying SQL code. Here is the line that is giving me trouble:
CAST(GL02GLF.GLF_SEQ_NUM as CHAR(3)) as “Sequence”
It’s coming along but there are some problems, ASP.NET actually has 2 posts not 1. And the java totals are correct but it should be Java, 3, 10 (all in one line) Category.....Topics...Posts ASP.NET.........2........1 C#................1........1 Java..............1........1 Java..............1........2 Java..............1........7
Overview: use category id to get count of topics then use the topic id to get the count of posts.
SELECT C.CategoryName, T.ThreadCount AS Threads, T.PostCount AS Posts FROM Category AS C LEFT OUTER JOIN (SELECT tt.CategoryID, PostID.PostCount, COUNT(tt.ThreadName) AS ThreadCount FROM Thread AS tt LEFT OUTER JOIN (SELECT ThreadID, COUNT(PostID) AS PostCount FROM Post AS P GROUP BY ThreadID) AS PostID ON tt.ThreadID = PostID.ThreadID GROUP BY tt.CategoryID, PostID.PostCount) AS T ON C.CategoryID = T.CategoryID WHERE (C.CategoryID = T.CategoryID) GROUP BY C.CategoryName, T.ThreadCount, T.PostCount ORDER BY C.CategoryName
I'm seeing some change in behavior for a query in SQL Server 2005 (compared to behavior in SQL Server 2000). The query is as follows: ------------ create table #projects (projectid int) insert into #projects select projectid from tblprojects where istemplate = 0 and projecttemplateid = 365
Select distinct tblProjects.ProjectID from tblProjects WITH (NOLOCK) inner join #projects on #projects.projectid = tblprojects.projectid Inner join tblMilestones WITH (NOLOCK) ON tblProjects.ProjectID = tblMilestones.ProjectID and tblProjects.projectID in ( select projectid from tblMilestones where (parent = 683691 AND PrimaryDate between '4/15/2006' and '4/22/2006' ) and enabled = 1 ) ------------ This is dynamic SQL generated by the application when a user requests a report with variable parameters. It works fine in SQL Server 2000. It outputs 47 records which is correct.
In SQL Server 2005, for some reason, the DISTINCT keyword is behaving as a TOP operator and outputs just 1 record. (Results of Showplan Text at the end of this post).
If I modify the query even the slightest bit by: 1) Changing "where (parent = 683691 AND PrimaryDate between '4/15/2006' and '4/22/2006' ) and enabled = 1 )" To " where (parent = 683691 AND PrimaryDate between '4/15/2006' and '4/22/2006' ) ) and enabled = 1 "
3) Removing the Distinct keyword, storing into a Temp table, then performing a distinct on the temp table
4) Adding: OPTION (FORCE ORDER)
5) OR completely fixing the query (remove redundant loops, etc)
...it works fine (outputs 47 records). It also works if I created new tables (eg. tMilestones instead of tblMilestones) and inserted about 10 records into each and ran the query referencing these new tables.
I reindexed the tables, updated stats, updated usage, ran DBCC FREEPROCCACHE, changed MaxDOP settings...nothing makes the query behave the way it does in SQL Server 2000 without modifying the query/adding the query hint.
Have you come across this? Any ideas on what might be causing the "TOP" operation. (Somewhat resembles the bug mentioned in this article: http://www.kbalertz.com/Feedback_910392.aspx - but this was apparently fixed POST-SQL Server 2000 SP4 - so has it not made it into SQL Server 2005 yet?).
I will appreciate any new insights you might have on this issue. Thanks much, Smitha
P.S. Results of Showplan Text:
StmtText ------------------------------ SET STATISTICS PROFILE ON
(1 row(s) affected)
StmtText ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Select distinct tblProjects.ProjectID from tblProjects WITH (NOLOCK) inner join #projects on #projects.projectid = tblprojects.projectid Inner join tblMilestones WITH (NOLOCK) ON tblProjects.ProjectID = tblMilestones.ProjectID and tblProjects.projectID in ( select tblMilestones.projectid from tblMilestones where (parent = 683691 AND tblMilestones.PrimaryDate between '4/15/2006' and '4/22/2006' ) and tblMilestones.enabled = 1 )
I am a newcomer to Microsoft Database Technology and have appeared to come across an issue with the SUM function in SQL Server Mobile Edition.
I am running Visual Studio 2005 and have created 2 tables:
Orders and OrderLines which are set up in master detail fashion.
The SQL Statement I create in the Query Builder is as follows:
SELECT Orders.OrderNo, Orders.OrderDate, Orders.Priority, Orders.Address, SUM(OrderLines.Quantity * OrderLines.QualityReference) AS Total FROM Orders, OrderLines WHERE Orders.OrderNo = OrderLines.OrderNo GROUP BY Orders.OrderNo, Orders.OrderDate, Orders.Priority, Orders.Address
Now, the SUM returns a total for all records in the OrderLines table, and not just the records whose OrderNo is the same as Orders.OrderNo
Can someone out there please clarify whether its an issue with my code or a bug with SQL Server Mobile???
Here are a couple of screenshots which will demonstrate what I mean.
Here is the contents of the Orders and OrderLines tables. Each order has only 1 line item in it.
When performing the summation over the OrderLines table, it produces the SUM of the all records in OrderLines and not according to the GROUP BY clause. See the following screen shot.
I have a parameter value as shown below and this is dynamic and can grow
Example : 101-NY, 102-CA, 165-GA 116-NY, 258-NJ, 254-PA, 245-DC, 298-AL How do I get the values in the below format NY,CA,GA --- each state to be followed with comma and the next state NY,NJ,PA,DC,AL --- each state to be followed with comma and the next state
correct query that will fetch only state names and not the numbers.
I have a partitioned view containing 4 tables (example follows at end)
The query plan generated on a select correctly accesses just one of the tables
The query plan generated on an update always accesses all four of the tables. I thought that it should only access the partition required to satisfy the update. Can anyone please advise whether: a) Is this is expected behaviour? b) Is the partitioned view incorrectly configured in some way? c) Is there is a known bug in this area
Note that the behaviour is the same with SP1 on SQL2000
I would be very grateful for any advice
Thanks
Stefan Bennett
Example follows
--Create the tables and insert the values CREATE TABLE Sales_West ( Ordernum INT, total money, region char(5) check (region = 'West'), primary key (Ordernum, region) ) CREATE TABLE Sales_North ( Ordernum INT, total money, region char(5) check (region = 'North'), primary key (Ordernum,region) ) CREATE TABLE Sales_East ( Ordernum INT, total money, region char(5) check (region = 'East'), primary key (Ordernum,region) ) CREATE TABLE Sales_South ( Ordernum INT, total money, region char(5) check (region = 'South'), primary key (Ordernum,region) ) GO
--create the view that combines all sales tables CREATE VIEW Sales_National AS SELECT * FROM Sales_West UNION ALL SELECT * FROM Sales_North UNION ALL SELECT * FROM Sales_East UNION ALL SELECT * FROM Sales_South GO
--Look at execution plan for this query -- This correctly only accesses the South partition SELECT * FROM sales_national WHERE region = 'south'
-- Look at execution plan for update -- This accesses all partitions - Why? update sales_national set total = 100 where ordernum = 23456;
I get the following error when running a report in report builder using the Adventureworks sample model: Semantic query execution failed. Incorrect syntax near 'NULLAND'.----------------------------Query execution failed for data set 'dataSet'.----------------------------An error has occurred during report processing.
Here is how to replicate the error:
Create a table report using the Adventure Works model. Select Product entity, add Product Category and #Products to the table. Edit formula for #Products to "COUNT(Products)" and add a filter on Products for "Discontinued Date is empty". (I want a count of products that have not been discontinued). If you run the report now, it willl work as expected. Add a report filter for "Product.Color is empty" (I only want to see products that don't have a color) Run the report to get the above error. While the above is a contrived example, I am getting the same error on a data model that I am developing for a customer.
Am I missing something, or is this a bug in Report Builder?
This is the error it gives me for my code and then it calls out line 102. Line 102 is my buildDD(sql, ddlPernames) When I comment out this line the error goes away, but what I don't get is this is the same way I build all of my dropdown boxes and they all work but this one. Could it not like something in my sql select statement. thanksPrivate Sub DDLUIC_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DDLUIC.SelectedIndexChanged Dim taskforceID As Byte = ddlTaskForce.SelectedValueDim uic As String = DDLUIC.SelectedValue sql = "select sidstrNAME_IND from CMS.dbo.tblSIDPERS where sidstrSSN_SM in (Select Case u.strSSN from tblAssignedPersonnel as u " _ & "where u.bitPresent = 1 and u.intUICID in (select intUICID from tblUIC where intTaskForceID = " & taskforceID & " and strUIC = '" & uic & "'))"ddlPerNames.Items.Add(New ListItem("", "0")) buildDD(sql, ddlPerNames)
I have a problem with sql 7 full text query. Everything appears to be in place the database is enabled and the catalogue populated but a query like this select * from risks_intercat WHERE CONTAINS(RISK_NOTES, ' "bean curd" ') always returns this; Search on full-text catalog 'ftc_Risks_ic' for database ID 8, table ID 329768232 with search condition ' "bean curd" ' failed with unknown result (8bcf8cc). has anybody got any ideas why?
I'm wanting to concatenate something that doesnt exist to an already existing field.
What I need to do is add a generic email address to every record in a data view So, what it would do is take the column that does exist and add @domain.com to another column that I would call username So rather than it just appearing as 911234 it would be a derived column saying 911234@domain.com
i've tried using +''+ but I get a space after the number.
I have an SQL database with rows that have parens in the data.If I run a select statement such as:SELECT SongNameFROM SongsWHERE SongName = 'John Jacob (Jingleheimer Schmidt)'It returns zero rows. This also:SELECT SongNameFROM SongsWHERE SongName LIKE '%John Jacob (Jingleheimer Schmidt)%'returns zero rows.If I change it to this:SELECT SongNameFROM SongsWHERE SongName LIKE '%John Jacob%'Then I get the row returned.Is there a way to use the first query example above and return the row?I'm guessing it has something to do with the parenthesis...*** Sent via Developersdex http://www.developersdex.com ***
My host does allow me to create full text catalogs. I use SQL Server Management Studio Express, so I'd have to do it "by hand" as opposed to point-clicking menus and going through wizards. Where do I go to find those commands? I've seen them before but I can't remember where to get them. They're the ones that begin with sp_. I need the commands for:
Hi Everyone,My SQL Query is :"select Field1 from table1"The Field1 in Database is of Type "nvarchar" . I need to convert this Field into "integer" in the Query itself.Please Help!!I have already tried "Select convert(int, Field1 ) from table1"The field gives zero output ThanksRegardsNavdeep
I have a query (select * from table1) and I'd like to have the results of this query sent to a text file instead of the results windows when I run it from Query Analyzer.
select *,substring(stafflog,15,11) as test into #t1 from dbo.Customers where stafflog like '%armagh%' go select left(stafflog,4) as Staff,count(left(stafflog,4)) as Total from #t1 where cast(left(test,charindex(' ', test))as smalldatetime) = cast(convert(varchar(8),getdate()-1,1) as datetime) group by left(stafflog,4) go select Title,Address1,Address2,Town,County,Postcode,TelephoneDay,TelephoneWork,TelephoneEvening, MobileTelephoneNo,Contact,Mail,Telephone,Terms,StaffLog from #t1 where cast(left(test,charindex(' ', test))as smalldatetime) = cast(convert(varchar(8),getdate()-1,1) as datetime) go drop table #t1 go
i need this query to be scheduled to run at a certain time every day and the results to be put in a text file.
is there an easy way to do this or what should i be looking at doing to get it to work
I have the following table called "tests" : id WeekNbrnametest hours -------------------------------------------------------------------------- 1 2007/26John "testA"5 2 2007/26John "testB"6 3 2007/26David "testA"3 4 2007/28David "testC"2 5 2007/30Victor "testD"1
I want to write a query so that I have as a result one row per person and per week, as followed
This means that I need to concatenate the values of the test column if in the same week and same person. For now, I have only managed to do the job without the test concatenation the following way:
SELECT DISTINCT WeekNbr, name, SUM(hours) as [Total of hours] FROM tests GROUP BY WeekNbr, name
and I get the following: WeekNbrnamehours ------------------------------------------------------- 2007/26John 11 2007/26David 3 2007/28David 2 2007/30Victor 1
Hi,How would I go about writing the results of an SQL query to a textfile?I cannot find any info in the Online help files.For example, I would like the results of:SELECT * FROM TableATo be written to the file result.txtAlex
hi.coming from postgresql, i am used to textual references to most of thethings i do with the database. i feel a little lost with all the graphical.i have few questions regarding MS SQL 20001. what is the best (or easiest) way of getting a table definition in text?it could be either a CREATE TABLE sql-query or a just a definition,something like:TABLE thisTableidintegervaluevarchar(10)etc.etc.2a. how do i get a query plan and how do i get it in text.2b. are there planner modes that show more or less of what actuallyhappened, verbose mode perhaps?2c. if i ask for a query plan, will SQL server actually run the query orwill it only produce a plan. if the query is run, does it commit orrollback by default?stig
Is there something wrong with my query or my full text catalog? The first query returns data while the second one does not. Does it have something to do with it being a number? I can get the rows to return by searching on alpha text.
select * from gk_info where info_desc like '%6416%'
select * from gk_info WHERE CONTAINS (info_desc, '"6416"')
