What is the best/most efficient way to store old dates (pre-1753) in SQL Server 2005? I am not concerned with storing times - just dates. SQL Server's datetime data type can only hold dates back to January 1, 1753. The MSDN documentation states that there are date and datetime2 data types, but SQL Server Management Studio does not seem t...
I'm having a heck of a time with this one. Might be the lack of sleep... or maybe I'm just getting dumb. I have 2 tables: a) Person {Key, Name, LastName} b) LogEntry {Key, PersonKey, Log, EntryTime} I'm trying to get a join of Person and LogEntry where LogEntry is the latest LogEntry for the given Person. I hope I don't go "duh..." i...
How do you raise a SQL Server error event (similar to RAISERROR) from within a SQL CLR routine? ...
I have several Multi-Select parameters in my report. I am trying to find a way to pass in multiple values for a single parameter in the web query string? If I pass in a single value, it works fine. The report runs fine selecting multiple choices for a single param. My trouble lies in the web query string. ...
$sql = "SELECT count(u_id) AS num_replies FROM `replies` WHERE `u_id`='".$uid."'"; $res = mysql_query($sql) or die(myqsl_error()); Will that return the number of replies a user with id $uid has made? If not, can anyone suggest something that will? Thx for the help. ...
I have a List<string> of variable count, and I want to query (via LINQ) a table to find any items that contain any of those strings in the Text column. Tried this (doesn't work): items = from dbt in database.Items where (stringList.FindAll(s => dbt.Text.Contains(s)).Count > 0) select dbt; Query would be something li...
I am making an app (asp.net/c#) that will autosuggest a couple fields that users type in. Each user will end up building their own auto-suggest list. Every time they add an item, if it's a new word, it will get added to their list of auto-suggests, just like gmail. I was wondering how most people go about this? Making a call to the s...
This question was suggested by Kyralessa in the What is your most useful sql trick to avoid writing more sql?. I got so many good ideas to try from the last question, that I am interested to see what comes up with this question. Once again, I am not keeping the reputation from this question. I am waiting 7 days, for answers, then mar...
I'm currently working on a solution that involves the following work flow: System sends out an email which includes some kind of identifier/sessionID. User replies to email. System receives reply, and parses email for sender, identifier, and user response. System queries a sql database to retrieve some information based on the user res...
I'm adding a new, "NOT NULL" column to my Postgresql database using the following query (sanitized for the Internet): ALTER TABLE mytable ADD COLUMN mycolumn character varying(50) NOT NULL; Each time I run this query, I receive the following error message: ERROR: column "mycolumn" contains null values I'm stumped. Where am I go...
I have a requirement in a SQL environment that under specific circumstances, all references to table (or view) A in a procedure actually use table (or view) B. On the iSeries I would have used the OVRDBF command to override references to table A with table B: OVRDBF FILE(A) TOFILE(B). What would be the equivalent to this in SQL? Is th...
Multiple clients are concurrently accessing a JAX-JWS webservice running on Glassfish or some other application server. Persistence is provided by something like Hibernate or OpenJPA. Database is Microsoft SQL Server 2005. The service takes a few input parameters, some "magic" occurs, and then returns what is basically a transformed ver...
I have a master table A, with ~9 Million rows. Another table B(same structure) has ~28K rows from table A. What would be the best way to remove all contents of B from table A. The combination of all columns(~10) are unique. Nothing more in the form a of a unique key ...
I have a nvarchar(max) column in a sql server 2005 table which is used for storing string representations of .NET TimeSpan objects. Sometimes the table is manually edited. I want to add a check constraint to verify that the string can be parsed by TimeSpan.Parse(). How should I do this? I guess I could use one of the methods for enabling...
We have the following example table (actually taken from another example here on stackoverflow...) CREATE TABLE example ( id integer primary key, name char(200), parentid integer, value integer); And given a specific child we want to get the top Parent. I know of the tablefunc connectby function but that is for getting a pa...
PHPWiki has a 5 second slow query each time you save a page edit. The query caught often in the "mysql-slow.log" is: INSERT INTO wikiscore SELECT w1.topage, COUNT(*) FROM wikilinks AS w1, wikilinks AS w2 WHERE w2.topage=w1.frompage GROUP BY w1.topage; The current indexes are as follows: table "wikilinks" has a primary index on "...
Hi, I have a question regarding the performance of SQL. I will illustrate my problem with pseudocode. I am wondering which will preform better and by how much? Say for 10 items, on each page load. In .NET. Is is a lot faster? a little faster? A difference not noticable on SQL? foreach(item in mylist) { CallSQLStoredProc(item.id); }...
Hello. I am using SQL and I have a table with three colums: account, transaction_date, Points. Each account will have multiple transaction_dates and Points earned for each transaction. How do I return the transaction_date when each account reached a certain threshold (i.e. accumulatee 100 Points). Say the first account has 2000 transac...
I've had a bit of a search, but didn't find anything quite like what I'm trying to achieve. Basically, I'm trying to find a similarity between two users' voting habits. I have a table storing each individual vote made, which stores: voteID itemID (the item the vote is attached to) userID (the user who voted) direction (whethe...
My table, for example: Table money _uid____cash_ 0 | 500 1 | 740 2 | 800 In MySQL, is there a way to write a statement that adds 100 to every entry in the cash column? Thanks in advance. ...