I am working on a legacy ASP application. I am attempting to insert a value (40.33) into a field in SQL Server 2000 that happens to be a float type. Every place I can see (via some logging) in the application is sending 40.33 to the Stored Procedure. When I run SQL Profiler against the database while the call is happening, the value t...
Do any queries exist that require RIGHT JOIN, or can they always be re-written with LEFT JOIN? And more specifically, how do you re-write this one without the right join (and I guess implicitly without any subqueries or other fanciness): SELECT * FROM t1 LEFT JOIN t2 ON t1.k2 = t2.k2 RIGHT JOIN t3 ON t3.k3 = t2.k3 ...
// goal: update Address record identified by "id", with new data in "colVal" string cstr = ConnectionApi.GetSqlConnectionString("SwDb"); // get connection str using (DataContext db = new DataContext(cstr)) { Address addr = (from a in db.GetTable<Address>() where a.Id == id select a).Single<Add...
Suppose I have a table. Now, I'm interested in Getting Useful Data Easily. This means I'd rather not drop it into Excel and go through contortions, nor somehow get it into CSV and then into a DB, and then into SQL. I'd like to be able to execute a SQL query directly against a table in HTML. Has anyone heard of a tool like this before? ...
Hi all. I want to do a select in MySql that combines several columns... something like this pseudocode: select payment1_paid and payment2_paid as paid_in_full from denormalized_payments where payment1_type = 'check'; Edit: payment1_paid and payment2_paid are booleans. I can't use any other language for this particular problem than ...
Hopefully I can do the problem justice, because it was too difficult to summarise it in the title! (suggestions are welcome in the comments) Right, so here's my table: Tasks task_id (number) job_id (number) to_do_by_date (date) task_name (varchar / text) status (number) completed_date (date) ...
I have a MySQL database table with a couple thousand rows. The table is setup like so: id | text The id column is an auto-incrementing integer, and the text column is a 200-character varchar. Say I have the following rows: 3 | I think I'll have duck tonight 4 | Maybe the chicken will be alright 5 | I have a pet duck now, awesome! ...
I have a table like as follows: SoftwareName Count Country Project 15 Canada Visio 12 Canada Project 10 USA Visio 5 USA How do I query it to give me a summary like... SoftwareName Canada USA Total Project 15 10 25 Visio 12 ...
I have a script to extract certain data from a much bigger table, with one field in particular changing regularly, e.g. SELECT CASE @Flag WHEN 1 THEN t.field1 WHEN 2 THEN t.field2 WHEN 3 THEN t.field3 END as field, ...[A bunch of other fields] FROM table t However, the issue is now I want to do other processing on the d...
We seem to have a few developers here who think creating stored procedures that spit out HTML or Javascript code is a legitimate thing to do. In my mind this is the ultimate abuse of the separation of concerns model. Is doing something like this people have often seen people doing? ...
How do I take an efficient simple random sample in SQL? The database in question is running MySQL; my table is at least 200,000 rows, and I want a simple random sample of about 10,000. The "obvious" answer is to: SELECT * FROM table ORDER BY RAND() LIMIT 10000 For large tables, that's too slow: it calls RAND() for every row (which al...
I want to compute for month difference of 2 dates which will return a float value. example: date1='4/23/2008' date2='12/31/2008' that will be 7.y months. I want to find the y value. can someone give me the formula to make this in sql codes? tnx.. ...
Hi, i am trying to setup SQL Reporting services on windows vista, iis7 but i keep getting this error when i try http://localhost/Reports/Pages/Folder.aspx **Server Error in '/Reports' Application. Request is not available in this context Description: An unhandled exception occurred during the execution of the current web request. Plea...
I'm currently performing a migration operation from a legacy database. I need to perform migration of millions of originating rows, breaking the original content apart into multiple destination parent / child rows. As it's not a simple 1 to 1 migration and the the resulting rows are parent / children row based on identity generated key...
Hi, I have the following sql query for transforming data but is it possible to save the value of the int in some variable to avoid casting multiple times? update prospekts set sni_kod = case when cast(sni_kod as int) >= 1000 and cast(sni_kod as int) <= 1499 or cast(sni_kod as int) >= 1600 and cast(sni_kod as int) <= 2439 then...
Is there a comprehensive online reference listing all standard SQL keywords / functions? (More helpful if they are listed together and not spread about a site.) ...
Obviously (methinks), creating an index on a BIT column is unnecessary. However, if you had a column that you need to search in which every value is likely unique, like BlogPost or StreetAddress or something, then an index seems appropriate (again, methinks). But what's the cutoff? What if you expect 10,000 rows and you'll have about 20...
Can source code examples be kept in a SQL database while retaining all formatting (tabs, newlines, etc.)? If so what data type would be used? ...
I have an SQL database with multiple tables, and I am working on creating a searching feature. Other than having multiple queries for the different tables, is there a different way to go about said searching function? I should probably add that a lot of my content is database driven to make upkeep easier. Lucene will not work for th...
I have an archiving process that basically deletes archived records after a set number of days. Is it better to write a scheduled SQL job or a windows service to accomplish the deletion? The database is mssql2005. ...