We are in the process of porting a legacy system to .NET, both to clean up architecture but also to take advantage of lots of new possibilities that just aren't easily done in the legacy system. Note: When reading my post before submitting it I notice that I may have described things a bit too fast in places, ie. glossed over details. I...
In this query: SELECT COUNT(*) AS UserCount, Company.* FROM Company LEFT JOIN User ON User.CompanyId = Company.Id WHERE Company.CanAccessSystem= true AND(User.CanAccessSystem IS null OR User.CanAccessSystem = true) GROUP BY Company.Id I want to query a list of companies that can access a particular system as well as the number of user...
Is there any way to import a database backup from 2005 into 2008 express edition. What I've had to resort to is doing a script the database, then import all the data through DTS. Whenever I tried to import straight from a backup file it says something about not being to import into a new version of sql server or I'll get the below error....
Hello Perl, DBD, and Oracle Masters: I have a Perl web application that is behaving oddly. I am using it to read some stuff from an Oracle DB and report. I have version 11.1.0.6.0 of Oracle's Instant Client installed. I'm running on WinXP and have the PATH environment variable set to the instant client location. I have Apache2 for my...
I have a main table that I must get data from. I have a left outer join where the fields will match 40% of the time. And then I have another join where I need to match the data from table A with. This is the SQL in pseudo code. This query won't work. -- This is the part I want to do but doesn't work. AND H.COL3 = A.STATE???? I am ...
I have an enumeration: ENUM( 'alpha', 'beta', 'gamma', 'delta', 'omega' ) If I sort my table by this column I get them in the correct order defined above. However, I can't find a way to select a subset of these, e.g. everything before delta. Using WHERE status < 'delta' only returns alpha and beta, not gamma. It seems MySQL uses a stri...
I'm interested in measuring and improving (where necessary) my team's SQL-92 skills. Can anyone recommend an appropriate on-line course and/or examination? Ideally it would be vendor-neutral, but it could also be MSSQL/Oracle specific, as long as the proprietary bits were flagged as such. ...
I have two tables: articles and articletags articles: id, author, date_time, article_text articletags: id, tag (article.id == articletags.id in a many-to-many relationship) I am after the last time that something was published under each tag. To put it another way, for every tag, look through all the articles it is related to and fin...
Please excuse my lack of knowledge... I know there is a lot of documentation on the internet related to this but I still don't understand. My situation is this: I have an XML file that I need import and eventually replace daily with. <item> <model>AA311-Pink</model> <title>1122</title> <price>19.43</price> <category>cat</cate...
I have a couple of tables in a SQL 2008 server that I need to generate unique ID's for. I have looked at the "identity" column but the ID's really need to be unique and shared between all the tables. So if I have say (5) five tables of the flavour "asset infrastructure" and I want to run with a unique ID between them as a combined gr...
I am working on an application that will be used as an extensible framework for other applications. One of the fundamental classes is called Node, and Nodes have Content. The SQL tables look like this: TABLE Node ( NodeId int, .... etc ) TABLE NodeContentRelationship ( NodeId int, ContentType string, ContentId int) It will be up to t...
I'm dealing with an Oracle DBA at the moment, who has sent me some profiling he's done. One of the terms in his report is 'Buffer Gets', any idea what this actually means? My guess is bytes retrieved from a buffer, but I have no idea really. Here is some sample output: Buffer Gets Executions Gets per Exec %Total Time (s) Time (s...
is there any known pattern/algorithm on how to perform sorting or filtering a list of records (from database) in the correct way? My current attempt involves usage of a form that provides some filtering and sorting options, and then append these criteria and sorting algorithm to my existing SQL. However, I find it can be easily abused th...
I have a large table with list of scores like this, one score per user: user,score ------ matt,10 john,20 bill,30 I want to make a query that answers to question: "John, you are faster than xx percent of others" How to make such a query for Microsoft SQLServer 2000 with optimal performance. ...
I'm currently doing the following to use typed datasets in vs2008: Right click on "app_code" add new dataset, name it tableDS. Open tableDS, right click, add "table adapter" In the wizard, choose a pre defined connection string, "use SQL statements" select * from tablename and next + next to finish. (I generate one table adapter fo...
I have a result set in MS-SQL within a stored procedure, and lets say it has one VARCHAR column, but many rows. I want to create a comma separated string conataining all these values, Is there an easy way of doing this, or am I going to have to step through each result and build the string up manually? Preferably I'd like to do this in ...
Hi all, I have 2 tables event + event_artist event eventId | eventName 1 , gig1 2, gig2 event_artist eventId, artistName 1, Led Zip 1, The Beatles ie Led Zep and the Beatles are both playing @ Gig1 I need to create the SQl to bind to a gridview ( you necessarily need to know about gridviews to answers this ) The results that i ...
I have a SQL SELECT query which has a LIKE clause containing an underscore, which should specifically look for an underscore, not treat it as a wildcard: SELECT * FROM my_table WHERE name LIKE '_H9%'; I understand that I can change the actual clause to '[_]H9%' for this to work as I expect, but the problem is that this clause is bein...
Since I'm sure many people have different standard, I've made this post a community wiki. My question is, what's a good naming scheme for table aliases? I've been using the first letter of every word from the table name, but it's been getting quite unreadable. Here's a quick example. FROM incidents i FROM cause_attack ca FROM obscure_t...
My simplified and contrived example is the following:- Lets say that I want to measure and store the temperature (and other values) of all the worlds' towns on a daily basis. I am looking for an optimal way of storing the data so that it is just as easy to get the current temperature in all the towns, as it is to get all the temperature...