I have an error occuring frequently from our community server installation whenever the googlesitemap.ashx is traversed on a specific sectionID. I suspect that a username has been amended but the posts havn't recached to reflect this. Is there a way a can check the data integruity by performing a select statement on the database, altern...
The workflow is like this: I receive a scan of a coupon with data (firstname, lastname, zip, city + misc information) on it. Before I create a new customer, I have to search the database if the customer might exist already. Now my question: What's the best way to find an existing customer, when there is no unique ID available? PS: I...
Hi, Can I configure hibernate properties to connect without using an instance name to sql server 2005? I need to force it to use localhost as the hostname and not specify the instance (same as you can do with the sql server enterprise manager). Ta! T ...
I need something in between a full text search and an index search: I want to search for text in one column of my table (probably there will be an index on the column, too, if that matters). Problem is, I want to search for words in the column, but I don't want to match parts. For example, my column might contain business names: Mi...
I have an script that falls over if any of the procedures it is trying to create already exists. How can I check/drop if this procedure is already created? ...
I made a view to abstract columns of different tables and pre-filter and pre-sort them. There is one column whose content I don't care about but I need to know whether the content is null or not. So my view should pass an alias as "true" in case the value of this specified column isn't null and "false" in case the value is null. Is this...
We have a database that we are using to store test results for an embedded device. There's a table with columns for different types of failures (details not relevant), along with a primary key 'keynum' and a 'NUM_FAILURES' column that lists the number of failures. We store passes and failures, so a pass has a '0' in 'NUM_FAILURES'. In...
This is sort of SQL newbie question, I think, but here goes. I have a SQL Query (SQL Server 2005) that I've put together based on an example user-defined function: SELECT CASEID, GetNoteText(CASEID) FROM ( SELECT CASEID FROM ATTACHMENTS GROUP BY CASEID ) i GO the UDF works great (i...
I was wondering if it is possible to not attach Excel sheet if it is empty, and maybe write a different comment in the email if empty. When I go to report delivery options, there's no such configuration. Edit: I'm running SQL Server Reporting Services 2005. Some possible workarounds as mentioned below: MSDN: Reporting Services Extens...
Hello, I need advice on how to handle relatively large set of flags in my SQL2k8 table. Two question, bear with me please :) Let's say I have 20 flags I'd like to store for one record. For example: CanRead = 0x1 CanWrite = 0x2 CanModify = 0x4 ... and so on to the final flag 2^20 Now, if i set the following combination of one recor...
I have a database in the following format: ID TYPE SUBTYPE COUNT MONTH 1 A Z 1 7/1/2008 1 A Z 3 7/1/2008 2 B C 2 7/2/2008 1 A Z 3 7/2/2008 Can I use SQL to convert it into this: ID A_Z B_C MONTH 1 4 0 ...
I'm having to do some data conversion, and I need to try to match up on names that are not a direct match on full name. I'd like to be able to take the full name field and break it up into first, middle and last name. The data does not include any prefixes or suffixes. The middle name is optional. The data is formatted 'First Middle La...
Hello I want to return all application dates for the current month and for the current year. This must be simple, however I can not figure it out. I know I have 2 dates for the current month and 90 dates for the current year. Right, Left, Outer, Inner I have tried them all, just throwing code at the wall trying to see what will stick ...
Microsoft in its MSDN entry about altering SQL 2005 partitions, listed a few possible approaches: Create a new partitioned table with the desired partition function, and then insert the data from the old table into the new table by using an INSERT INTO...SELECT FROM statement. Create a partitioned clustered index on a heap Drop and re...
I have two tables: foos and bars, and there is a many-to-one relationship between them: each foo can have many bars. I also have a view foobars, which joins these two tables (its query is like select foo.*, bar.id from foos, bars where bar.foo_id=foo.id). EDIT: You would not be wrong if you said that there's a many-to-many relationship ...
I have a table that looks like that: The rows are sorted by CLNDR_DATE DESC. I need to find a CLNDR_DATE that corresponds to the highlighted row, in other words: Find the topmost group of rows WHERE EFFECTIVE_DATE IS NOT NULL, and return the CLNR_DATE of a last row of that group. Normally I would open a cursor and cycle from top to ...
I have three tables, A, B, C, where A is many to one B, and B is many to one C. I'd like a list of all C's in A. My tables are something like this: A[id, valueA, lookupB], B[id, valueB, lookupC], C[id, valueC]. I've written a query with two nested SELECTs, but I'm wondering if it's possible to do INNER JOIN with DISTINCT somehow. SELE...
I have an MSDE2000 database which appears to be approaching it's 2Gb limit. What tools can I use to determine where all the space is being used? Ideally think TreesizePro for SQL Databases ...
I'm writing a query for an application that needs to list all the products with the number of times they have been purchased. I came up with this and it works, but I am not too sure how optimized it is. My SQL is really rusty due to my heavy usage of ORM's, But in this case a query is a much more elegant solution. Can you spot anything...
A query that is used to loop through 17 millions records to remove duplicates has been running now for about 16 hours and I wanted to know if the query is stopped right now if it will finalize the delete statements or if it has been deleting while running this query? Indeed, if I do stop it, does it finalize the deletes or rolls back? ...