The problem if have is this. We have taken over a website which has an active member community. We've been given the application and database dump and have the site running on a new server successfully and the DNS has been switched. The problem is that the database has come out of sync in the time it took to get the files to us and t...
I have a table which has several one to many relationships with other tables. Let's say the main table is a person, and the other tables represent pets, cars and children. I would like a query that returns details of the person,the number of pets, cars and children they have e.g. Person.Name Count(cars) Count(children) Count(pets) J...
using visual studio (database pro) 2008, i have created a unit test (using the SQL unit test) that tests several stored procedures in order. The solution is designed to call each SP after a period of time, not within the same second. Thus the unit test fail and requires a short pause between each call. Any ideas? is there a standard...
I have recently moved a database from Sql Server 2000 to Sql Server 2005. In the table designer, it insists on putting square brackets around a column named "Content" I don't see Content on the list of reserved words for Sql Server, so I don't understand why it is doing this. Is there a way I can prevent it? ...
Hi there, can't seem to find a way to do this anywhere. I have an SQL trigger that fires on an update. The trigger copies some details about the update to another table that someone elses program uses. Among the details passed is the name of the updated column. There a many, many, potential columns that have been updated (although only o...
By default Oracle uses indexes created. When I change to NLS_COMP=Linguistic and NLS_Sort=Binary_CI, I get full table scans. I'd read somewhere that creating an index using (nlssort(name, 'NLS_SORT=BINARY_CI')); Would work. As my attempt below shows, not so much. Even if I force it, the performance does not seem to be what I would e...
So let's say I have a few million records to pull from in order to generate some reports, and instead of running my reports of the live table, I create a temp where I can then create my indexes and use it for further data extraction. I know cached tables tend to be quicker / faster seeing as the data is stored in memory, but I'm curi...
Is there a list of best practice MySQL data types for common applications. For example, the list would contain the best data type and size for id, ip address, email, subject, summary, description content, url, date (timestamp and human readable), geo points, media height, media width, media duration, etc Thank you!!! ...
Hi Everyone, I'm trying to setup some data to calculate multiple medians in SQL Server 2008, but I'm having a performance problem. Right now, I'm using this pattern ([another example bottom). Yes, I'm not using a CTE, but using one won't fix the problem I'm having anyways and the performance is poor because the row_number sub-queries r...
Up until now I've been a lone wolf on my client projects. Anytime I would make changes to SQL Server: tables updates, stored procs, etc. I would generate the change script and plop it into a directory. When the application was ready for release, I would run the scripts on the live server and be done. Soon I will have another develope...
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED BEGIN TRAN DECLARE @res INT EXEC @res = sp_getapplock @Resource = 'This a Lock ID 3', @LockMode = 'Exclusive', @LockOwner = 'Transaction', @LockTimeout = 60000, @DbPrincipal = 'public' if @res < 0 begin declare @errorMessage nvarchar(200) set @errorMessage = case @r...
Active Data Structure the concept is.. You have a table (one that holds lots of records).. [data1] (call this the 'active' table) --------- Data1ID (required) SysID (required) createdDate (required) lastUpdatedDate (required) [mydata] (user's fields) you also have n tables holding the same columns as Data1. [data2] (call these th...
Hello, My problem is I try to do a simple JOIN between two tables, that both have the id field. My result is an stdClass object, as I use PDO. Does anyone know how can I make a difference between the id of the first table and the id of the second table? Code bc. $sql = "SELECT *, FROM products AS p, products_categories as c ...
Sorry about the last message. I did a paste over my question. Long question short, when using sp_GetAppLock inside of a try / catch block, should I call sp_ReleaseAppLock when an exception is caught? SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED BEGIN TRAN DECLARE @res INT EXEC @res = sp_getapplock @Resource = 'This ...
I have a table ALPHA with 2 fields GroupId,Member: GroupId | Member; A1----------A; A1----------B; A1----------C; A2----------A; A2----------B; A3----------A; A3----------D; A3----------E; Objective: Given the input of - A,B,C - I have to query the table to find if a GroupId exists for this exact set of members. So, this is what I pl...
Informix iSQL has a command "info tables;" that shows all tables. The syntax for viewing the fields and their respective data types is "info columns for table;" Is there a similar command that shows table.field for all tables and all fields? LEFFLER!!!! HELP!!!! ...
<game xmlns="http://my.name.space" ></game> This is my root element. I've written a stored procedure to insert elements into it. To sum up the stored procedure, here's the SQL UPDATE ChessGame SET GameHistory.modify('insert <move><player>black</player><piece>pawn</piece><start>E7</start><end>E6</end></move> as last into (/game)[0]') W...
How could i create a trigger that at any insertion on my table [users] will change automatically the content of its [password] field to its MD5 hash? Ps: I do not want this being done at client side. ...
I am writing an application that helps book exchange between users. I am using PHP and MySQL, and I am pretty new to them both. I have 5 tables, 3 data tables and 2 service tables: user: with user attributes (user_id, name, birth... etc). book: with book attributes (book_id, name, author, publisher... etc). copy: represents actual co...
I'm developing a high-volume web application, where part of it is a MySQL database of discussion posts that will need to grow to 20M+ rows, smoothly. I was originally planning on using MyISAM for the tables (for the built-in fulltext search capabilities), but the thought of the entire table being locked due to a single write operation m...