sql

SQL SELECT: "Give me all documents where all of the documents procedures are 'work in progress'"

This one really has me stumped. I have a documents table which hold info about the documents, and a procedures table, which is kind of like a revisions table for each document. What I need to do is write a select statement which gives me all of the documents where all of the procedures have the status "work_in_progress". Here's an exa...

Help needed on an SQL configuration problem.

I have been banging my head with this one more the two weeks, and still don't know what the problem is ( I can't narrow it down). The problem is the following. I have a solution with 3 project in it all written in c# and I with LINQ. One project is the main web site, the other is the data layer (communication with the database) and the t...

Attaching databases of sql 2000

I have few databses of sql 2000 on windows 2000. Can I attach these databases to another instance of SQL Server 2000 on anothermachine having windows 2003 installed?? Does attach and detach of databases are platform independent?? ...

Cannot resolve collation conflict in Union select

Hi, I've got tqo queries: First doesn't work: select hotels.TargetCode as TargetCode from hotels union all select DuplicatedObjects.duplicatetargetCode as TargetCode from DuplicatedObjects where DuplicatedObjects.objectType=4 because I get error: Cannot resolve collation conflict for column 1 in SELECT statement. Second works: ...

select records from table in the order in which i inserted

consider a tale is as follows, EmployeeId | Name | Phone_Number Now, i insert 10 records... When i query them back, select * from myTable they are not selected in the order i inserted. I can obviously keep an autoincrement index and ORDER BY index. But i dont want to alter the table. How can i do this without altering the table? ...

How can I select values from different rows depending on the most recent entry date, all for the same employee ID?

Basically I have a table which is used to hold employee work wear details. It is formed of the columns: EmployeeID, CostCentre, AssociateLevel, IssueDate, TrouserSize, TrouserLength, TopSize & ShoeSize. An employee can be assigned a pair of trousers, a top and shoes at the same time or only one or two pieces of clothing. As we all k...

MySQL: Is it possible to return a "mixed" dataset?

Hi, I'm wondering if there's some clever way in MySQL to return a "mixed/balanced" dataset according to a specific criterion? To illustrate, let's say that there are potential results in a table that can be of Type 1 or Type 2 (i.e. a column has a value 1 or 2 for each record). Is there a clever query that would be able to directly ret...

How to tell if a record is still in a SQL table - using linq maybe ?

Hi, I have an SQL database, which is a "feeder" table. I put records in said table, a 3rd party package consumes (and deletes) them. All hunky dory - until the 3rd party package isn't running. In thinking about how to detect that, I thought to myself... "well... what if I read all the keys in the table (its not very big - max a few doze...

How can I concisely copy multiple SQL rows, with minor modifications?

I'm copying a subset of some data, so that the copy will be independently modifiable in future. One of my SQL statements looks something like this (I've changed table and column names): INSERT Product( ProductRangeID, Name, Weight, Price, Color, And, So, On ) SELECT @newrangeid AS ProductRangeID, Name, Weight, Price, Color, And...

Help understand difference in sql query

Select user_name [User Name], first_name [First Name], last_name [Last Name] From tab_user ORDER BY user_name Select user_name [User Name], first_name [First Name], last_name [Last Name] From tab_user ORDER BY User Name Above are the two queries, Is there any difference because of the user_name used instead of U...

Select fields containing at least one non-space alphanumeric character

(Sorry I know this is an old chestnut; I have found similar answers here but not an exact answer) I frequently hand type this kind of query from a console so I am always looking for easier to type solutions SELECT * FROM tbl_loyalty_card WHERE CUSTOMER_ID REGEXP "[0-9A-Z]"; -- exact but tedious to type or SELECT * FROM tbl_l...

What is the best way to store a date without a year in SQL database?

I am building an app that has a daily quote that should be stored in the database. Each quote is assigned with a day of the year, including one for Feb 29th. Since the quote only cares about the day not the year should I still use smalldatetime type? Please let me know your opinions, thanks!! ...

Provider information is not pulling from the MVC project's Web.Config

I'm working on branching my services of my MVC solution to a separate project, which includes a MembershipProvider wrapper. The problem is that when doing my unit testing, the provider information being used isn't coming from the MVC project's Web.Config file (e.g. the connection string), but some default settings. I have the feeling, th...

How to use correctly the Query Window in SQL Server 2008

Hello, What should I do to avoid that commands be executed each time I hit 'Execute !. icon' I mean this USE master; GO CREATE DATABASE Sales GO USE Sales; GO CREATE TABLE Customers( CustomerID int NOT NULL, LName varchar (50) NOT NULL, FName varchar (50) NULL, Status varchar (10), ModifiedBy varchar (30) NULL ) GO When...

DateTimeOffset Support in Compact Framework 3.5

Does anyone know if the data type "DateTimeOffset" is supported in the .NET Compact Framework 3.5? ...

SQL Recursive Calculated Date

I have a sql server table: CREATE TABLE [Workflow].[MilestoneDate]( [MilestoneDateId] [int] IDENTITY(1,1) NOT NULL, [SpecifiedDate] [datetime] NULL, [RelativeDays] [int] NULL, [RelativeMilestoneDateId] [int] NULL, CONSTRAINT [PK_MilestoneDate] PRIMARY KEY CLUSTERED ( [MilestoneDateId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NOREC...

SQL query duration is longer for smaller dataset?

I received reports that a my report generating application was not working. After my initial investigation, I found that the SQL transaction was timing out. I'm mystified as to why the query for a smaller selection of items would take so much longer to return results. Quick query (averages 4 seconds to return): SELECT * FROM Payroll WH...

SQL MIN in Sub query causes huge delay

I have a SQL query that I'm trying to debug. It works fine for small sets of data, but in large sets of data, this particular part of it causes it to take 45-50 seconds instead of being sub second in speed. This subquery is one of the select items in a larger query. I'm basically trying to figure out when the earliest work date is that ...

SQL: How can i update a value on a column only if that value is null?

Hey, I have an SQL question which may be basic to some but is confusing me. Here is an example of column names for a table 'Person': PersonalID, FirstName, LastName, Car, HairColour, FavDrink, FavFood Let's say that I input the row: 121312, Rayna, Pieterson, BMW123d, Brown, NULL, NULL Now I want to update the values for this person, b...

paste(1) in SQL

How in SQL could you "zip" together records in separate tables (à la the UNIX paste(1) utility)? For example, assuming two tables, A and B, like so: A B ======== ==== Harkness unu Costello du Sato tri Harper Jones How could you produce a single result set NAME | NUM =============== Harkness | unu Cost...