I'm trying to perform a group by action on an aliased column (example below) but can't determine the proper syntax. SELECT LastName + ', ' + FirstName AS 'FullName' FROM customers GROUP BY 'FullName' What is the correct syntax? EDIT Extending the question further (I had not expected the answers I had received) wou...
Are there any inherent problems with calling an ole object from tsql? I have been posed with a problem that I would like to solve solely using a stored procedure, is there anything I need to worry about with using a ole object as opposed to writing it in a clr based language? ...
I'd like to update a set of rows based on a simple criteria and get the list of PKs that were changed. I thought I could just do something like this but am worried about possible concurrency problems: SELECT Id FROM Table1 WHERE AlertDate IS NULL; UPDATE Table1 SET AlertDate = getutcdate() WHERE AlertDate IS NULL; If that is wrapped i...
I created a table in my SQL Server 2005 database and populated it with summary and calculated values. The purpose is to avoid extensive joins and groupings on every call to the database. I would like this table to refresh every hour, but I am not sure the best way to do this while the website is under load. If I delete every record an...
Hi folks, i'm trying to do a spatial query in sql 2008 -> for a given list of POI's (point of interest, long/lat GEOGRAPHY data), which postcodes do they exist in (multipolygon GEOGRAPHY data). So this is the query i tried, but it's syntactically incorrect:- SELECT PostCodeId, ShapeFile FROM Postcodes a WHERE a.ShapeFile.STIntersects(...
Hello Consider this example schema: Customer ( int CustomerId pk, .... ) Employee ( int EmployeeId pk, int CustomerId references Customer.CustomerId, .... ) WorkItem ( int WorkItemId pk, int CustomerId references Customer.CustomerId, null int EmployeeId references Employee.EmployeeId, .... ) Basical...
I know there have been questions in the past about SQL 2005 versus Lucene.NET but since 2008 came out and they made a lot of changes to it and was wondering if anyone can give me pros/cons (or link to an article). ...
Hi everyone, I would really appreciate help with this, I am seriously stuck. Basically I have a table that looks like this: SSS_DOWID Name Mon Tue Wed Thu Fri Sat Sun Description 2 M Y N N N N N N Monday 3 MF Y N N N Y N N Monday, Friday ....... 18 ...
And what would you recommend for an ASP Net web application, with a not so large SQL server database (around 10Gb)? I was just wondering, is that a good idea to have an Amazon EC2 instance configured ready to host your app in an emergency? In this scenario, what would be the best approach to keep the database updated (log shipping? man...
SELECT * FROM employee A WHERE 3=(select count(*) +1 from employee B where B.salary > A.salary) This gets the 3rd highest salary; can somebody explain the logic behind this query and how it works. ...
Has anyone successfully installed SQL Server Express 2008 with Advanced Services? I've tried several times and half way through the installation I always get the error 'The downloaded file appears to be corrupt'. I found this thread on MSDN but it seems that noone there could get it to work. They were only successful in installing th...
I am working on a web API for the insurance industry and trying to work out a suitable data structure for the quoting of insurance. The database already contains a "ratings" table which is basically: sysID (PK, INT IDENTITY) goods_type (VARCHAR(16)) suminsured_min (DECIMAL(9,2)) suminsured_max (DECIMAL(9,2)) percent_premium (DECIMAL(9,...
Is there any solution other than the ugly ADO as it is not good and badly documented? ...
I'm using MS SQL 2005 and when I create a function I need to put the schema name to call it: select dbo.MyFunc All my tables are also in "dbo" schema and I don't need the schema name to reference it, so I'd like to know if I'm missing some configuration that could do the same to functions. ...
Hi guys, I'm having trouble getting in touch with SQL Server Managemen Studio 2008! I want to create a link-table that will link an Event to many Audiences (EventAudience). An example of the data that could be contained: EventId | AudienceId 4 1 5 1 4 2 However, I don't want this: Even...
I have a TSQL script that is used to set up a database as part of my product's installation. It takes a number of steps which all together take five minutes or so. Sometimes this script fails on the last step because the user running the script does not have sufficient rights to the database. In this case I would like the script to fail ...
I'm writing a SQL query in SQL Server in which I need to replace multiple string values with a single string value. For example Product Quantity ------- -------- Apple 2 Orange 3 Banana 1 Vegetable 7 Dairy 6 would become Product Quantity ------- -------- Fruit 2 Fruit 3 Fruit ...
I am using MS SQL Server 2005 (9.0.4035) and trying to find rows that contain the same data in a nvarchar(4000) field. The field contains xml that has both opening and closing square parentheses. Here is sample data: DataID Data 1 1 2 1 3 2] 4 2] 5 3[ 6 3[ Using th...
MS SQL Server doesn't have row level triggers, correct? If I needed to insert a row from within a trigger and then insert another row, based on the result of the first insert, would a cursor be the best solution? For example, is there a better way to do this: CREATE TABLE t1 (foo int) CREATE TABLE t2 (id int IDENTITY, foo int) CREATE T...
Hello all, we have an application running on an IIS 6/ASP.NET 2.0 backed by a SQL Server 2005 STD edition X64. From time to time, the application crashes with some silly messages (some of the fileds are not found into a "select firled1, filed2.. from mytable"). I obtained a trace of the activity from the sql server taken while the appli...