I have a business user who tried his hand at writing his own SQL query for a report of project statistics (e.g. number of tasks, milestones, etc.). The query starts off declaring a temp table of 80+ columns. There are then almost 70 UPDATE statements to the temp table over almost 500 lines of code that each contain their own little set o...
I have two simple tables in my database. A "card" table that contains Id, Name, and text of a card, and a "rulings" table which contains the Id of the card, and text detailing the rulings for the card. Often enough in the ruling text, there is a reference to another card in the database. It is easy enough to find this in the text becaus...
So I have a stored procedure that accepts a product code like 1234567890. I want to facilitate a wildcard search option for those products. (i.e. 123456*) and have it return all those products that match. What is the best way to do this? I have in the past used something like below: *SELECT @product_code = REPLACE(@product_code, '*', '...
I've seen a number of people claim that you should specifically name each column you want in your select query. Assuming I'm going to use all of the columns anyway, why would I not use SELECT *? Even considering the question from 9/24, I don't think this is an exact duplicate as I'm approaching the issue from a slightly different persp...
Apology for a lengthy post but I needed to post some code to illustrate the problem. Inspired by the question What is the reason not to use select * ? posted a few minutes ago, I decided to point out some observations of the select * behaviour that I noticed some time ago. So let's the code speak for itself: IF EXISTS (SELECT * FROM...
I currently working on an issue tracker for my company to help them keep track of problems that arise with the network. I am using C# and SQL. Each issue has about twenty things we need to keep track of(status, work loss, who created it, who's working on it, etc). I need to attach a list of teams affected by the issue to each entry in ...
I'm writing an application where the user will create an appointment, and instantly get an email confirming their appointment. I'd also like to send an email the day of their appointment, to remind them to actually show up. I'm in ASP.NET (2.0) on MS SQL . The immediate email is no problem, but I'm not sure about the best way to addre...
This seems to me to be the kind of issue that would crop up all the time with SQL/database development, but then I'm new to all this, so forgive my ignorance. I have 2 tables: CREATE TABLE [dbo].[Tracks]( [TrackStringId] [bigint] NOT NULL, [Id] [bigint] IDENTITY(1,1) NOT NULL, [Time] [datetime] NOT NULL, CONSTRAINT [PK_Tra...
How to query to get count of matching words in a field, specifically in MySQL. simply i need to get how many times a "search terms"appear in the field value. for example, the value is "one two one onetwo" so when i search for word "one" it should give me 3 is it possible? because currently i just extract the value out of database and d...
Hi, I found a few articles online providing examples of how to model graphs of various kinds (DAGs, in particular) in SQL, but they all seemed enormously complex, given the relative simplicity of what they're modeling. Is there a best / standard way of doing this? My current thinking is something like this: create table node ( id in...
The developer environment db server is SqlServer 2005 (developer edition) Is there any way to make sure my SQL Queries will run in SqlServer 2000? This database is set to Compatibility level "SQL Server 2000 (80)" but some queries that run without problems in the development system can not run in the Test Server (SqlServer). (The prob...
Does anyone know of a definitive list of LINQ to SQL query limitations that are not trapped at compile time, along with (where possible) workarounds for the limitations? The list we have so far is: Calling methods such as .Date on DateTime no workaround found string.IsNullOrEmpty simple, just use == "" instead .Last() we used .Or...
Hello, This SQL query was generated by Microsoft Access 2003, and works fine when run, but fails when trying to run from a Macro. Is there any obvious error within the query, or any reason it would not work? SELECT tblAuction.article_no, tblAuction.article_name, tblAuction.subtitle, tblAuction.current_bid, tblAuction.start_price, tblAu...
HI, im having trouble trying to optimize the following query for sql server 2005. Does anyone know how could i improve it. Each one of the tables used there have about 40 million rows each. I've tried my best trying to optimize it but i manage to do the exact opposite. Thanks SELECT cos , SIN FROM ConSisHis200...
We are deciding the naming convention for tables, columns, procedures, etc. at our development team at work. The singular-plural table naming has already been decided, we are using singular. We are discussing wether to use a prefix for each table name or not. I would like to read suggestions about using a prefix or not, and why. Does i...
We are looking at various options in porting our persistence layer from Oracle to another database and one that we are looking at is MS SQL. However we use Oracle sequences throughout the code and because of this it seems moving will be a headache. I understand about @identity but that would be a massive overhaul of the persistence code....
I have clustered applications that requires one of the nodes to be designated as the master. The cluster nodes are tracked in a table with nodeID, isMaster, lastTimestamp columns. Each node in the cluster will try to become a master every X seconds. Node can only become a master if either there is no other master nodes the lastTimest...
I'm trying to use mysql to create a view with the "with" clause with authorRating(aname, rating) as select aname, avg(quantity) from book group by aname but it doesn't seem like mysql supports this. I thought this was pretty standard and I'm sure Oracle supports this. Is there anyway to force mysql to use the "with" clause?...
In SQL, How we make a check to filter all row which contain a column data is null or empty ? For examile Select Name,Age from MEMBERS We need a check Name should not equal to null or empty. ...
Hi, I'm trying to do a simple "Select Count(*) from PRODUCTS where date > xxx" with Castle on NHibernate. If I was directly using NHibernate, I could reuse this question answers but unfortunately I see no easy way to access the Current NHibernate session from Castle Records. I obviously don't want to retrieve all my objects and do a C...