I inherited MYSQL database that has lots of tables with data like CREATE TABLE IF NOT EXISTS `ejl_registration` ( `id` int(11) NOT NULL auto_increment, `team_id` int(11) default NULL, `start_date` date default NULL, `end_date` date default NULL, PRIMARY KEY (`id`), ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=88668...
What's the best way to check if a table exists in a Sql database in a database independant way? I came up with: bool exists; const string sqlStatement = @"SELECT COUNT(*) FROM my_table"; try { using (OdbcCommand cmd = new OdbcCommand(sqlStatement, myOdbcConnection)) { ...
This could be the userid's or number of users. Thanks in advance. ...
Our product leverages Analysis Services combined with Reporting Services, we recently made some changed to add compatability for SQL2008. Everything works great except with certain dimensions, drillthroughs or measures added to a report we suddenly get this following error: Server: The operation has been cancelled. Errors in the hi...
When writing destructive queries (e.g., DELETE or UPDATE) in SQL Server Management Studio I always find myself wishing I could preview the results of the query without actually running it. Access very conveniently allows you to do this, but I prefer to code my SQL by hand which, sadly, Access is very poor at. So my question is twofold: ...
I am implementing a small queue to handle which process gets to run first. I am using a table in a database to do this. Here is the structure of the table (I'm mocking it up in SQLite): "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , "identifier" VARCHAR NOT NULL , "priority_number" INTEGER DEFAULT 15, ...
I have a sparsely populated matrix that's the result of a series of left joins. I'd like to collapse it down to a single row (see below). The only solution I've seen to this is a GROUP BY on the PK and a MAX() on ColA, ColB etc. Performance is a huge issue here, so I'd like to know if anyone has a better solution. The columns ColA, ColB ...
How do I count the number of messages where my body length is between 0 and 25 characters long? Message.countBy('from Message m where m.body.length <= 25') Unfortunately for me, countBy does not take a string parameter. ...
Question Hello All, I've had some confusion for quite some time with essentially flooring a DateTime SQL type using T-SQL. Essentially, I want to take a DateTime value of say 2008-12-1 14:30:12 and make it 2008-12-1 00:00:00. Alot of the queries we run for reports use a date value in the WHERE clause, but I either have a start and en...
Hi I have an XML column in my table which contains this xsd snippet: <xsd:element name="Postcode" minOccurs="0"> <xsd:simpleType> <xsd:restriction base="xsd:string"> <xsd:pattern value="^[0-9]{4}$" /> </xsd:restriction> </xsd:simpleType> </xsd:element> The regular expression should require a string...
I have a table with one numeric value (n) and three string values (a,b,c). How do I query this table so that I get only distinct values of (a,b,c) and if there are duplicates, take the maximum of the corresponding set of n values? ...
I need to read reports in Java from Paradox but I don't know how to do this. Can reports be imported through netbeans using a working driver? I have linked tables to Java but not the reports. I need to apply some Rich Text (ie. making a sentence BOLD - thus the need for the connection). Do you have any suggestions as to how I can pri...
Today again, I have a MAJOR issue with what appears to be parameter sniffing in SQL Server 2005. I have a query comparing some results with known good results. I added a column to the results and the known good results, so that each month, I can load a new months results in both sides and compare only the current month. The new column...
Hi anybody, a month ago i searched for some tools that will generate c# classes out for my sql database/tables. so i don't have to write DAL classes manually and to save a lot of time. i came across "ORM" and subsonic. i watched the webcasts on the homepage http://subsonicproject.com/ and was pretty impressed by it. but i am still miss...
I've been doing a little work with SQL Server and C# and every time I run the darn thing the connection times out unless I start services.msc and manually restart the service (SQL Browser Service). It's already set to automatic, does anyone know why it would do this? I'm not an advanced programmer, more of an intermediate one. Once I do ...
I have a MySql database with three tables I need to combine in a query: schedule, enrolled and waitlist. They are implementing a basic class enrollment system. Schedule contains the scheduled classes. When a user enrolls in a class, their user account id and the id of the scheduled class are stored in enrolled. If a class is at capacity...
What is the best way of taking a query and transforming it into a nested class list without doing a subselect for each row? eg. 1 aaaa 1 bbbb 1 cccc 2 dddd 3 eeee into 1 aaaa bbbb cccc 2 dddd 3 eeee ...
Has someone experience with database comparison tools? Which one you would recommend? We are currently using "SQLCompare" from Redgate, but I am curious to know if there are better tools on the market. The main requirement is that they should be able to compare scripts folder against a live database. Thanks, Dimi ...
How to create a Stored procedure (SQL 2005/2000) to swap 2 records excluding few columns, it has to get "table name", "record id (2 id)" and "column to exclude" as a parameter. Here columns are dynamic as we get 'table' as a parameter and number of columns may be more than 50. Here I believe, "update [table name] set [field] = [value...
Does it make sense to use .NET DataSets even in applications that use a local in-process database for application data persistence? If DataSets are mainly meant for in-memory caching of database results, it sounds like they're not so beneficial when using something like SQL Server Compact local database that runs in the same process as ...