A SQL VIEW is a global, logical table that may or may not be persisted. But it's still a table. Therefore, should a VIEW always adhere to first normal form (1NF)? i.e. no duplicate rows, scalar types only, no top-to-bottom or left-to-right ordering, etc. What about the higher normal forms? For me, my applications 'consume' the results o...
I need to change the primary key of a table to an identity column, and there's already a number of rows in table. I've got a script to clean up the IDs to ensure they're sequential starting at 1, works fine on my test database. What's the SQL command to alter the column to have an identity property? ...
I wrote a little C++/MFC application that allows me to connect to an ODBC database and execute SQL statements. I'm using SQLGetDiagField / SQLGetDiagRec to display meaningful error messages to the user in case of any errors. Is there a way to receive and disply the database confirmation messages ("table created", "role set", "user dropp...
Is there a way to see what SQL objects from one data base use a certain index. But I do not want to have to see the execution plan for every object because I have a lot of stored procedures and views PS. It's for SQL 2005 ...
I have a table that has a forced auto increment column and this column is a very valuable ID that is retained through out the entire app. Sorry to say it was poor development on my part to have this be the auto incrementing column. So, here is the problem. I have to insert into this table an ID for the column that has already been cre...
There is frequently the need to synchronize data from master tables in one database to clone tables in other databases, often on other servers. For example, consider the case where a backend system manages inventory data and that inventory data ultimately must be pushed to one or more databases that are part of a web site application. T...
I've got a query that looks a bit like this: select records.id, contacts.name + ' (' + contacts.organization + ')' as contact, from records left join contacts on records.contact = contacts.contactid Problem is - contacts.organization is frequently empty, and I get contacts like "John Smith ()". Is there a way to only concatenate the o...
I am creating a status board module for my project team. The status board allows the user to to set their status as in or out and they can also provide a note. I was planning on storing all the information in a single table ... and example of the data follows: Date User Status Notes -----------------------------------...
How do I see what the character set that a MySQL database, table and column are in? Is there something like SHOW CHARACTER SET FOR mydatabase; and SHOW CHARACTER SET FOR mydatabase.mytable; and SHOW CHARACTER SET FOR mydatabase.mytable.mycolumn; ...
I've been thinking about the modified preorder tree traversal algorithm for storing trees within a flat table (such as SQL). One property I dislike about the standard approach is that to insert a node you have to touch (on average) N/2 of the nodes (everything with left or right higher than the insert point). The implementations I've...
I have two tables, one for routes and one for airports. Routes contains just over 9000 rows and I have indexed every column. Airports only 2000 rows and I have also indexed every column. When I run this query it can take up to 35 seconds to return 300 rows: SELECT routes.* , a1.name as origin_name, a2.name as destination_name FROM ro...
I have a table products where one of the columns are relatedproducts. relatedproducts contains strings of concatenated product IDs (column productid) separated by colon, e.g. abc-123:foo-prod:ada69, etc. Due to some bad design, there is the case that a product may be removed from products table and still be referenced in the relatedprodu...
myTable UNID 9 10 UNID is a VARCHAR2 select max(UNID) from myTable returns the UNID with the highest lexicographic value (9). how do i select the UNID with the highest integer value (10)? ...
Is there a way to add a deployment administrator to Microsoft CRM 4.0 using a sql query against the CRM database? The person that setup the CRM was the only one who was the Deployment Administrator and he has left and his account was deactivated before another Deployment Administrator could be added using the deployment manager tool. O...
Hello sql gurus, Is there a way to limit the number of rows in a SQL Server 2000 database, such that older rows are deleted when new ones come in? I have some data that I'd like to keep around for a about thirty days - after that, I don't care whether the data lays around or is deleted - as long as the table doesn't become huge. Any o...
Hi, Simple query, possibly impossible but I know there are some clever people out there :) Given a boolean parameter, I wish to define my where clause to either limit a certain column's output - or do nothing. So, given parameter @bit = 1 this would be the result: where column = 1 given parameter @bit = 0 this would be the result: ...
I have 3 tables bl_main (bl_id UNIQUE, bl_area) bl_details (bl_id UNIQUE, name) bl_data(bl_id, month, paper_tons, bottles_tons) bl_id is not unique in the last table. There will be multiple rows of same bl_id. I am trying to retrieve data in the following way bl_id | name | bl_area | sum(paper_tons) | sum (bottles_tons) | paper_to...
Is there a string format to represent a datetime that SQL will be able to parse and convert into another offset (EST -> UTC for example). I have a string from the user such as: declare @p1 varchar(50); declare @utcDateTime datetime; set @p1 = "2009-06-26 14:30:00.000Z-4:00"; -- could be ISO8601 -- what do I do here to convert @p1? ...
I found a way to extract the contents of a MS Sql table directly (faster) to excel. But I do not know how to do this with a stored procedure that requires parameters. Is it possible to extract, directly, to an Excel File the results of a stored procedure? I know how to do it indirectly (using a data table) but it is too slow. Thank you v...
I'm trying to copy the contents of a column in one mysql database to an identical table in another mysql database. I'm using: UPDATE db1.table SET db1.table.name = db2.table.name, db1.table.address = db2.table.address WHERE db1.table.id = db2.table.id; I'm getting the error 1054: Unknown column 'db2.table.id' in 'where cl...