We have a 16 processor SQL Server 2005 cluster. When looking at CPU usage data we see that most of the time only 4 of the 16 processors are ever utilized. However, in periods of high load, occasionally a 5th and 6th processor will be used, although never anywhere near the utilization of the other 4. I'm concerned that in periods of tr...
In responses to this question, KM said if you are on or above SQL Server 2005, you can use IFs to have multiple queries in the same procedure and each will have an query plan saved for it (equiv to a procedure for each on older versions), see the article in my answer or this link to proper section: sommarskog.se/dyn-search-2005.html#...
Is anyone familiar with using the LOCATE with a passed in string varialbe? I am trying to use it to determine if there is a comma in a string that was set in the stored procedure but have not been able to get it to work properly. My code looks something like this DECLARE string VARCHAR(10); DECLARE comma_found INT; SET stri...
I am trying to return a list of dates. Each row has multiple dates concatenated as a single string. I want to order the rows by the minimum date in each row. Here is my query: SELECT br.bm_tracking_number, (SELECT TOLIST(APPT.fact_date) FROM bm_fact APPT WHERE APPT.bm_review_sk = br.bm_review_sk ...
I have a trip that has many residencies. I need a single query that returns all trips where no residency information has been specified. And all trips that match a specified residency. I can get the first from this query: SELECT * FROM `trips` WHERE (((NOT EXISTS (SELECT id FROM residencies WHERE trips.id = residencies.trip_id)) Bu...
I have a backup like this: Select * Into BACKUP From ORIGINAL Then I need to restore some data from that backup, how do I update from that table, I can't Insert Into nor Drop Table because of the Foreign Keys ...
I need query (to be more specific, it is view, not query) that will return string column. String will be code, that might be prefixed with two letters (LJ). If it is prefixed -- prefix have to be trimmed. If there is no prefix present, it should be left untouched. Is it possible to do such thing in SQL? Server is FirebirdSQL 1.5. Non...
Can someone link me to a tutorial or explain if there is a way to create some sort of batch file of mysql scripts / stored procs and run them all at the same time? I can not seem to find any documentation on this online but I feel that I might be searching using the wrong terms. ...
I want to create a search website to search docs (all kinds of formats including pdf), images, videos, and audio. I also want to be able to filter my search results based on some criteria like author name, date, etc. I'm doing this in .NET, so what's the easiest way to get up and running? SQL fulltext searching seems tempting becaus...
I have built a small forum where users can post messages. My server is in the United States, but the userbase for the forum is in Taiwan (+15hrs). When someone posts to the form, I store the time in my mySQL database in the format YYYY-MM-DD HH:MM:SS. When I look in the database, the time displays the proper time (the time that the pe...
Query: SELECT * FROM table WHERE fieldA LIKE '%%' AND fieldB LIKE '%%' AND fieldC LIKE '%%' This returns only records that have all fields completed. My thought would be that it should return all records in the table. Does the '%%' actually represent that a value is needed? UPDATE1: Thanks to some good questions the soluti...
I use a regex in my SQL statements for an app that look like this SELECT * FROM table WHERE id = {{REPLACEME}} However, sometimes I'm not giving a parameter to replace that string with. Is there a way to replace it with something that matches anything. I tried *, but that does not work. ...
Is this even possible? It seems like I should be able to. This is my issue. I need to run a web service method from a 3rd party to get a collection of available items where I need the ID and a Status property. Then I have method using LINQ to SQL that retrieves the items that are current. What I need to do is retrieve the items that ar...
I'm porting a sybase application to mysql and of course there's tons of sql compatibility issues, but one that I don't see an easy way out of without changing every single sql is that in sybase you can refer to a table by db..table notation, but in mysql it requires db.table. I'm kinda on mysql's side on this one, but this is what it is....
First of all I'm not asking if NOLOCK should or should not be used. Let's get past that. I guess the question comes down to how sql server writes data? Is an entire row written at once or does it write it a column at a time? I'm asking because the NOLOCK hint is being considered. A dirty read is fine as long as the entire row is retu...
I'm trying to export a schema in an oracle 10 database using the EXP command. Let's call the schema "myschema" and the tns name "mydb" to protect the names of the innocent. Anyway, here's the command line that I'm using exp myschema/mypassword@mydb file=myschema.dmp grants=y This works when I try to run an export on other instan...
We have updated our servers with Windows 2008 Server, but we are still using SQL Server 2005 in our production software. The problem comes in one part of the system where we store times as datetimes. Since ever, storing a time in a datetime column stored the base date as the date. It is 1900-01-01 in SQL Server 2005. Back then, IIS under...
We have a table with 17Mil rows containing product attributes, let's say they're: brandID, sizeID, colorID, price, shapeID And we need to query for aggregates by brand and size. Currently we query and filter this data by doing something like this: select brandID, sizeID, count(*) from table where colorID in (1,2,3) and price=10...
I am currently trying to write a little program to track time-off requests for employees. I'm fairly new to MYSQL and PHP, so it's a learning project for me as well. I've run into this problem which I do not seem to be able to figure out. I want to display time off requests for a given week (Mon-Fri). I've got the requests in a table 'r...
I have a large report I am running on sql server. It takes several minutes to run. I don't want users clicking run twice. Since i wrap the whole procedure in a transaction, how do I check to see if the table is locked by a transaction? If so I would want to return an error message saying "report generating, please try again in a few min...