sql-server-2008

How to use CONTAINS with inline queries in SQL Server 2008?

I have this sql query where I'm trying to use CONTAINS to search the title field. But I get this error. "Cannot use a CONTAINS or FREETEXT predicate on column 'Title' because it is not full-text indexed." The Titles table has been indexed and a CONTAINS works fine with a simple search. Does anyone know what I'm doing wrong? Are CONTA...

Join won't do it, and sub query sucks, then what?

Hello all, first of all, sorry for the non descriptive title, I'm just too rushed so I couldn't come up with a better one. Second: I have a portion of my database the looks like the following diagram: I have contributors on the system, each write to many sources, and a source can have many working contributors. Users can subscribe...

Can a SQL Server Trigger send me an email?

I wish to send an email from a Trigger, on my SQL Server 2008 machine. The data of the email will be, basically, some of the Trigger information. Can someone provide some simple/sample code on how to do this, please? E.g. what's the system stored procedure called? Etc. I've not set up any SQL mail and stuff, so I'm guessing it's built ...

Sql Recursive query to identify relation between 2 users of family tree

Users Data UserId DisplayName 12 Rahul 13 Anjali 14 Faruk 15 Shabina 16 Shakira 17 Ali 18 Ronak 19 Dali 20 Bali 21 Kali 22 Katrina 23 Sita 24 Gita 25 Ram 26 Shyam 27 Suhana 28 Suhas 29 Raj 30 Taslim 31 Ritik 32 Tejas 33 Dipika 34 Bush 35 Dyna 36 Bushiar 37 Salman 38 Ruksana 39 Khushi 40 Tazz 41 Miki 42...

SQL Server Query Execution Plan Rebuild

I have this query that gets executed though Linq to Entities. First time the query runs it generates the execution plan which takes just under 2 minutes. After the plan is cached the query takes 1 or 2 seconds. The problem I have is that the plan keeps getting rebuild every few hours and I am not sure why that would be? This is the linq...

Microsoft's StreamInsight - Experiences & SQL Server 2008 R2

Microsoft's Complex Event Processing (CEP) offering StreamInsight was released in tandem with SQL Server 2008 R2. I am wondering: if StreamInsight is in any way tied to the SQL Server 2008 R2 database and if it can be run separately. (From scanning over the documentation it looks like StreamInsight can be run separately but c...

SQL Server 2005/2008 Group By statement with parameters without using dynamic SQL?

Is there a way to write SQL Server Stored Procedure which to be strongly typed ( i.e. returning known result set of columns ) and having its group statement to be dynamic. Something like: SELECT SUM( Column0 ) FROM Table1 GROUP BY @MyVar I tried the following also: SELECT SUM( Column0 ) FROM Table1 GROUP BY CASE @MyVar WHEN 'Column...

MDX Calculated Member

I am trying to create a TFS report which requires MDX. I am making a report that requires a calculated member. I am not 100% sure how this is done. The member i am trying to do is a difference calculation. For example: Table Sept 1 Sept 2 Sept 3 Actual 0 32 58 Remaining 163 140 ...

Configuration error description: Could not continue scan with NOLOCK due to data...

Hi, I am trying to install SQL Server 2008 Developer Edition on Windows Server 2008 R2 x64. The preliminary configuration check only warns me about opening ports in Windows Firewall, but when I complete the installation, the following fails: database engine services sql server replication full-text search reporting services Reason: C...

Fix SQL Assemblies after moving a database (HRESULT: 0x80131050)

It is well documented here that when you update an assembly (that is not on the approved list) in the GAC that happens to be inside SQL 2005/2008, the SQLCLR will complain that could not load file or assembly. This is also a problem when moving a database to a new machine (that could have different versions of the assembies). Is there a...

Generating large numbers of unique random codes

I've got a project where we'll need to generate a lot of fixed-length random codes (Read: Millions) from a set of characters (EG: 12 digit alpha-numeric or 9 digit alpha-numeric lower-case only without the l character). We're going to then store these codes in an MSSQL database (SQL Server 2008). The language we're using is C#. We also ...

Sync Framework Handling ClientInsert ServerInsert Conflict

Hi, I have a application that uses LocalDataCache to synchronise a SQL Server 2008 Express database to a client database (.sdf file). This works great and I am now managing the conflicts. I am using this Microsoft resource as a guide: How to: Handle Data Conflicts and Errors The conflict I am particularly interested in is ConflictT...

Transact-sql insert in two tables at once?

Ok, this will sound weird, but can you do an insert in a join of two tables? I have a table A and a table B with a foreign key to A. Now I want to copy multiple records of table A in one query + a copy of the records in table B with foreign key to the records being copied in table A (but as foreign key the new identities). Hope somebod...

Check if Stored procedures have syntax errors

I have tons of stored procedures in my database. We are constantly changing the data structure (we are in development) Is there a tool that will tell me which stored procedures won't compile? When you create a stored procedure it prevents you if there is an invalid table or column, but if you change the column name after the stored pro...

SQL Server - Size Based Trigger

Is there a way to have a trigger fire when a table hits a specified size? I am doubtful, but I thought I would ask. It would be useful for logging tables that I want to keep under a specific size. (I would delete older entries when the trigger fired.) ...

SQL Query - Get Most Recent Revision

Say I have parent table Projects: ProjectID ProjectNam 1 Test Project 1 2 Test Project 2 and child table ProjectRevisions: ProjectRevID ProjectID DateCreated 11 1 10/15/2009 12 1 10/19/2009 13 1 10/25/2009 21 2 10/05/2009 How do I end up wi...

SQL Server 2008 Performance Question

I have a table with 30 columns and about 3.4 million records. Is it reasonable for SELECT * FROM [Table]; to take 8 to 12 minutes to return all 3.4 million results? If not, where's a good place/resource to begin diagnosing my problem? ...

Debugging Stored Procedure in SQL Server 2008

Is there any way to debug a stored procedure on SQL Server 2008? I have access to use SQL Server Management Studio 2008 and Visual Studio 2008 (not sure whether either provides this functionality). Generally I use the SQL profiler to find the parameters passed to the stored proc, however would like to be able to step through the proce...

Inserting 1000s of rows to SQL table using SSMS

Hello Everyone, I have a sql script with insert statements to insert 1000s of rows (12000 approx.). When i try running the script in SSMS, it throws 'Out of memory' exception after a while. "An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown." I have SQL Server 2008 o...

SQL Server: Logical equivalent of ALL query

I have a following query (simplified): SELECT Id FROM dbo.Entity WHERE 1 = ALL ( SELECT CASE WHEN {Condition} THEN 1 ELSE 0 END FROM dbo.Related INNER JOIN dbo.Entity AS TargetEntity ON TargetEntity.Id = Related.Ta...