I have two named instances of SQL Server 2008 and am trying to set static ports for each instance. I open the SQL Server Configuration Manager _> expand "SQL Server Network Configuration" -> click the instance I want to change -> and select TCP/IP. From here any configuration changes that I make are not persisted after I hit OK. I've ...
Take a look at these two queries: -- #1 SELECT * FROM my_table WHERE CONTAINS(my_column, 'monkey') -- #2 SELECT * FROM my_table WHERE CONTAINS(my_column, 'a OR monkey') -- "a" is a noise word Query #1 returns 20 rows when I run it in Management Studio. Query #2 returns the same 20 rows, but I also see the following in the Messages t...
This table gets hit with this query the most, so I want add a index to speed things up, this table will have 5 million rows it in. My query looks like this: SELECT someID FROM someTable WHERE myVarChar = @myVarChar AND MyBit = 0 AND MyBit2 = 1 AND MyBit3 = 0 myVarChar is unique also. What would the best index be for this t...
Is there a way to turn OFF the DML tracking for SQL Server 2008 Change Data Capture? I'd like to only track all DDL statements. Thanks! ...
I'm trying to backup a database in SQL Server 2008 and have the database compressed using the new compression feature. However, when I run the following code, I get a weird error message: Backup Database <Database> To Disk 'C:\Backup' With Compression I get this error message: Backup Database With Compression is not supported on S...
I've just recently upgraded to SQL Server 2008 from 2005 and when I attempt to restore a database I get the following error... "Could not load file or assembly 'Microsoft.SqlServer.Sqm, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" ...
What are the recommendations of software products for creating automated backups of SQL Server 2008 databases? The backup should happen without taking the database offline/detatching. ...
What are the different editions of SQL Server 2008 and how do they differ? ...
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...
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(...
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). ...
I am trying to use the aspnet___regsql utility to install all the membership tables in a database on my local SQL Express 2008 instance. I can connect without problems in Management Studio, but when I try to list the databases in the aspnet_regsql interface i get an error message saying: Failed to query a list of database names from the...
During SSAS 2008 installation the Account provisioning tool comes up and you can select the current Windows user (or other windows user). However that account was deleted prior to adding another user as the administrator. In 2005 the tool existed on the server and could be run manually, however I’m not seeing it in 2008. I assume I ca...
Hi all! When I attended a presentation of SQL Server 2008 at Microsoft, they did a quick gallup to see what features we were using. It turned out that in the entire lecture hall, my company was the only one using the Service Broker. This surprised me a lot, as I thought that more people would be using it. My experience with SB is that ...
I'd like to be able to run an already parameterized query from within the SSMS: select name from aTable where id = @id I know that other IDEs (e.g. TOAD) allow for parameter binding - is this available in SSMS 2008? Thanks! ...
The following code tries to create a new record and then modify it after it has been committed to the database. The last SubmitChanges() call throws a ChangeConflictException. ItemA itemA = new ItemA(); itemA.Foo = "a"; itemA.Created = DateTimeOffset.Now.UtcDateTime; ItemAs.InsertOnSubmit(itemA); SubmitChanges(); itemA.Foo = "b"; Subm...
I have a case where i need to translate (lookup) several values from the same table. First way I wrote it, was using subqueries: SELECT (SELECT id FROM user WHERE user_pk = created_by) AS creator, (SELECT id FROM user WHERE user_pk = updated_by) AS updater, (SELECT id FROM user WHERE user_pk = owned_by) AS owner, [name] FRO...
In SQL 2005 Reporting services we were able to spoof a user when calling a report. This doesn't seem to work in 2008 and the technet articles that appear relevant seem to be implying that you need to create a seperate security extension( Technet Article )if you're wanting to expose reports to the internet. Is this what people are using i...
Hi! I'm inserting a large amount of rows into an empty table with a primary key constraint on one column. If there is a duplicate key error, is there any way to find out the value of the key (or row) that caused the error? Validating the data prior to the insert is sadly not something I can do right now. Using SQL 2008. Thanks! ...
When using multivalue parameters in sql reporting services is it more appropriate to implement the list filter using a filter on the dataset itself, the data region control or change the actual query that drives the dataset? SSRS will support any scenario, so then I ask, is there a reason beyond the obvious of why this should be done a...