Single quote vs. double quote

I think I've read somewhere about the differences between single quote and double quote in a sql query. I happened to see at least 1 of the differences between them today when I was playing pivot with my sql server 2008. I simplified the table as below, just to make your life easier. CREATE DATABASE [BugTracker] Go Use [BugTracker] Go CREATE TABLE [reports]( [rp_id] [int] IDENTITY(1,1) NOT NULL, [rp_chart_type] [varchar](8) NOT NULL, CONSTRAINT [pk_reports] PRIMARY KEY CLUSTERED ( [rp_id] ASC )) insert into reports values ('pie') insert into reports values ('pi

deadlocks

Hi guys, Under SQL-Server2005 (native ODBC), I have 2 statements and a beautiful deadlock 1205 message when I do this: statement 1 : BEGIN TRAN UPDATE mytable SET field=NULL where x=45 statement 2: UPDATE mytable SET field=NULL where x=45 -- here i'm locked, sounds good... but now statement 1: UPDATE mytable SET field=NULL where x=45 statement 2: Transaction (Process ID xxx) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. If I try this under Sybase 15.0, I don't have this me

Subselect in a grouping query

I have the following SELECT...GROUP BY code that is giving me the error "Column 'dbo.PayChecks.CheckID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." which I understand but not sure how to fix it. In this query I want to get a total of a column (GrossPay) from a related Timesheet table. I have done this before in a non-grouped query. Any help is appreciated. SELECT C.PeopleLinkID, MIN(P.LastName) AS LastName, MIN(P.FirstName) AS FirstName, D.DedCode, SUM(D.DedAmount) AS Dues, (SELECT

usign inner join in subselect

The following SQL gives me different results than if I use "=" rather than "inner join" in the 2 subselects in the select statement. The inner join sub selects give me one blanket value for the whole column whereas the "=" subselects give me distinct values for each row. I've tried putting an alias on the purchase table however then the subselect gives an error message of "invalid object name" when I refer to the alias. If I jsut replace inner join with = it works fine but I was wondering if anyone had an explanation? thanks Jane SELECT purchase.po_id, address_a.a

subquery returned more than one value

When I run this query: update a set a.[UPC] = b.[IDSCE], a.[AltDescription] = b.[IDESC], a. [Weight] = b.[IMNNWU], from [ProductVariant] a inner join [IIMWEB] b on a. [SKUSuffix]=b.[IPROD] I receive an error: The statement has been terminated. Msg 512, Level 16, State 1, Procedure trig_NotificationProduct, Line 19 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

How to get 2 Max values?

I have following 2 tables (T1, T2) and rows: T1 (Column: Id) ---- 1 2 3 4 T2 (Columns: Id, Value) ---- 1, 1 1, 2 1, 3 1, 4 2, 1 2, 2 2, 3 2, 4 3, 1 3, 2 3, 3 3, 4 4, 1 4, 2 4, 3 4, 4 The result-set I need is: --------------------------------------- 1, 3 1, 4 2, 3 2, 4 3, 3 3, 4 4, 3 4, 4 i.e for each Id from T1, I need 2 max values from T2. How do I write the query?

SQL Express 2008: Select User Name to Login

Hello: I am working through the tutorials. I have created user Mary. The next bit is about how she can not access anything yet. Oh, really? I want to see what happens if she does. I try to login under Mary and can not. The User name field is disabled. The docs says that it is only available when logging in using Window Authentication, but that is exactly when it is not available. The Connect to Server form comes up with: Server type: Database Engine Server name: Loop\SQLEXPRESS Authentication: Windows Auth

tsql help - get change or created line item

Hi All, I have an order history file. It has an Action column to store action performed on the order. Order Id, Action, Date 1, Created, 8/1/10 1, Changed, 8/15/10 1, Changed, 9/1/10 What is the best t-sql to say Give me the most recent changed date for all orders And if there is no Changed status just give me the Created date. Now I can imagine doing this by stringing the sql together and then executing the sql string but is that the best way? Thanks, rod.

Subquery returned more than 1 value

When I run this query: update a set a.[UPC] = b.[IDSCE], a.[AltDescription] = b.[IDESC], a. [Weight] = b.[IMNNWU], from [ProductVariant] a inner join [IIMWEB] b on a. [SKUSuffix]=b.[IPROD] I receive an error: The statement has been terminated. Msg 512, Level 16, State 1, Procedure trig_NotificationProduct, Line 19 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. How do i go about fixing this issue.

Help Optimize Code

I have created t-sql script listed below to create t0, t1, t2 ,and t3 user accounts (first cursor) with there corresponding passwords with only processadmin server role. Next cursor is used for all user accounts (t0, t1, t2, t3) have already been created. Need to make sure only processadmin server role is applied to the user account. It's important that if the user accounts already is created that they are not created and ensure they have the only have processadmin server role permission. Please help me optimize the code listed below. Thanks so much for the help.

Update trigger

Could somebody show me how to update a trigger. I havent created any trigger but it exists in one of the databases we bought for shopping cart. I have to make some modifications to that.

Query to find all zip4's inside polygon

I need to find a way to run a SQL query to find all lat/long points in a table which are within a polygon which is defined by several lat/long points. For example, I have this table which includes ALL ~700,000,000 possible Zip+4 positions with the corresponding lat/long values. CREATE TABLE [dbo].[Zip4Coordinates]( [Zip4] [varchar](10) NOT NULL, [Latitude] [decimal](12, 9) NOT NULL, [Longitude] [decimal](12, 9) NOT NULL) And say I have 4 lat/long points (moving in clockwise pattern on map) which make up my polygon. In this case, it's 4 lat/long points, but could be up t

can TIME values be aggregated?

Hello, Can time values be aggregated? I'm putting together a summary report that will need to show the sum time that something happened. (Employee breaks, for instance.) If break times for 3 employees were "01:00:00", "00:15:00", and "00:12:30" in length, how do I aggregate them to get "01:27:30"? Simply treating them like char values so they can be parsed into integers and added and then divided by 60 doesn't seem like the best way. Thanks, Eric

Can't trim white space from large varchar field

Hi, I am reading two very large varchar fields (2600) from the sql server 2005 database of our ERP. This reading is being done by a cursor. In the cursor select statement I rtrim and ltrim the field. No matter what I do the length of the field is always 2600. I can see that there is some sort of white space to the right of the text. Whatever that white space is, it can't be trimmed by ltrim or rtrim. I tried running the sql from the cursor as a query and had the same problem. Any help would be appreciate it. fig000

Need help to search for extra space in all tables.

I try to search for data which has some extra spaces in ALL TABLES. Does any one know if there a tool that can do that or the scripts can return all the data (see desire results below). Thank you in advance. SQL2008. IF OBJECT_ID('Tempdb.dbo.#T1', 'u') IS NOT NULL DROP TABLE #T1 GO CREATE TABLE #T1 ( FullName VARCHAR(30) ) GO INSERT INTO #T1 VALUES ('Joe Smith') INSERT INTO #T1 VALUES ('April Smith ') INSERT INTO #T1 VALUES ('Peter Chen') INSERT INTO #T1 VALUES ('Kristine Nguyen') GO IF OBJECT_ID('Tempdb.dbo.#T2', 'u') IS NOT NULL DROP TABLE #T2 GO CREA

SELECT * in Programs

As far as I can remember, this has been a "no no". But what would you answer developers who say " We want the program to bomb when the table is out of sync with program definiton"? Thanks in advance

How to write this query?

Hi, I'm using SQL Server 2005. I have a query that returns multiple rows in which all the column values are the same except one ... Results id text category ===================== 2 Hello ABC 2 Hello DEF Does anyone know how I would write this query such that I could combine into a comma-separated list, the "category" column, so that the results would return as a single row -- e.g. id text category ===================== 2 Hello ABC, DEF Thanks, - Dave

sql help required

sql 2000 i have following 2 tables CREATE TABLE [idtab] ( [uid] [int] NOT NULL , [uid2] [int] NOT NULL , [system] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , CONSTRAINT [PK_idtab] PRIMARY KEY CLUSTERED ( [uid], [uid2], [system] ) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [sectab] ( [id1] [int] NULL , [id2] [int] NULL , [system] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO i m populating values from sectab to idtab as idtab contains composite primary key i only wants to insert those values which doe

Energy Saving Tips

1. Reduce heater's room temperature For each extra degree (Celsius) of temperature, your heater will consume 7 to 11% more energy. Adjust it to 18degrees which is also better for your health. 2. Install a programmable thermostat You will be able to control the heater's temperature during specific hours every day. 3. Insulate your house Loss of heat is loss of energy. 4. Fix air leaks 5. Close the chimney of the fireplace when you don't use it It is designed to drive smoke (hot air) out of the house! 6. Get an energy audit to advice you about the necessary fixes and insulation 7.

table error problem

Hello there I have procedure who create temp table #tmp, store a lot of data inside it for moving it to another database. on any 5 rerun of the procedure in job i'm getting an error like: Attempt to fetch logical page (1:73868) in database 2 failed. It belongs to allocation unit 72057594108903424 not to 72057594124763136 I have recreated all the used tables. and it still happen. What can cause it?

Dynamic sql

When executing dynamic sql, how can you avoid issues if delimiters are in the data ? Assume I have a list box in an applicaion with the following values.... ABC 3' 879 Plus many more they may select... If I do something like.... where Item = 'ABC'or Item='3'' or Item ='879' I've got a problem... how might I avoid this ?

SQL server on virtual machine on network

I can ping the server, but I do not see the SQL server from Management Studio although through RDP I can connect to it and work directly on the server. Furthermore, I cannot create an ODBC driver to access data from it. Could I have some clues how to solve this? Thank You

freight class

SQL 2005 Assuming the below PCF and Class columns were in a table, how would I create a query, with PCF known, to find the proper freight class? I started with SWITCH, but that just goes on forever. Thanks! FREIGHT CLASS CHART The first column shows the pounds per cubic foot (PCF). The second column shows freight class. So when the PCF is greater than the number in the first column, than the class will likely be the number in the second column. PCF Class 50 ... 50 35 ... 55 30 ... 60 22.5 ... 65 15 ... 70 13.5 ... 77 12 ... 85 10.5 ... 92 9 ... 100 8 ... 110

Case in Order By clause

Can you use multiple columns in an ORDER BY clause with a CASE statement. I can do: SELECT * FROM TableA ORDER BY CASE @SortByLevel WHEN 1 THEN Level ELSE Name END But I want to use both columns in the ORDER BY but in reverse order.l SELECT * FROM TableA ORDER BY CASE @SortByLevel WHEN 1 THEN Level, Name ELSE Name, Level END I get an error on the comma. Is there a way to do this without Dynamic Sql? Thanks, Tom

Inserting data not found

Hi, I'd like to know how can I insert on another table the records that were not found on the another table. Example: I've a table: tbl1: CodeBIN (int) 2222 3333 4444 5555 .... .... I'd like to know the records that DON'T exists on a second table: tbl2: CodeBin (int) 2222 4444 6666 .... In this case will insert the 3333, 5555 because they dont exists on the tbl2 tblInsertedNotFound CodeBin (int) 3333 5555 .... .... using MS-SQL2000 thank you!