I'm a developer at heart - but every now and then, a customer doesn't have a decent DBA to deal with these issues, so I'm called in to decide.... What are your strategies / best practices when it comes to dealing with a reasonably sized SQL Server database (anything larger than Northwind or AdventureWorks) - do you use multiple filegrou...
Is it possible to restore a backup of a SQL Server 2008 database onto an instance of SQL Server 2005? I need to work on an sample application for which database backup is in sql server 2008. But I'll not be able to install 2008. So is it possible to restore that back up in 2005? ...
I have a SQL Server 2008 table with a column of the geography datatype. The value is a point (latitude and longitude). How do I query the table to return all rows where the location is within a 10 kilometer radius of a given coordinate? ...
I have a stored procedure that takes a comma-delimited string of IDs. I split them and put them into a temporary table and pull out records from another table using where id IN [table] Is it ok to use this same procedure when only one id is passed in for the param? I could write a second stored procedure that would do exactly the sameth...
I have a medical database that keeps different types of data on patients: examinations, lab results, x-rays... each type of record exists in a separate table. I need to present this data on one table to show the patient's history with a particular clinic. My question: what is the best way to do it? Should I do a SELECT from each table w...
I'm designing a table in SQL Server 2008 that will store a list of users and a Google Maps co-ordinate (longitude & latitude). Will I need two fields, or can it be done with 1? What's the best (or most common) data-type to use for storing this kind of data? ...
I'm being driven to insanity trying to figure out why Intellisense just fails to work at all. The server I'm using is local and is 2008, the database is set to 2008 compatibility, Intellisense is on in every menu I can find, and yet no member list will pop up even with a CTRL-J. Has anyone experienced something like this and found a way...
One of my clients wants me to perform a periodic "real" destruction of some of his old data, and I'm evaluating the best way to do it. The data is in a table, and I want to destroy some of the rows contained in it. I could do it manually by deleting/exporting the database on another computer/degaussing the hard drive/reimporting the sa...
What would be the best tool to view and edit an SQL Server 2008 database "directly". I am thinking more or less about a very friendly interface like "Microsoft Access" where I can open a table, insert new rows, remove rows or change data in a row/column. With also a prompt where I can launch store procedures, run SQL commands and view ...
We are trying to create a feature (layer) in GeoServer on top of a view in SQL Server 2008, rather than a table. We are able to create the feature, but it won't display any data because GeoServer (JDBC) can't detect a primary key to use as the FID. To the best of my knowledge, it is not possible to make a view column appear to be a prima...
Hi, I would like to save my backups from my SQL 2008 server to another server location. We have 2 servers: Deployment server File Server The problem is that the deployment server doesn't have much space. And we keep 10 days backups of our databases. Therefore we need to store our backups on an external "file server". The problem is ...
I have two servers running Windows 2008 Web Edition and SQL 2008 Standard Edition, how would be the best approach to load balance them? Should I use database mirroring or log shipping? Is Win2008 NLB easy to configure by a developer, since I'm not a sys admin? What I'm looking for is to be able to have my sites running in case of a si...
To my knowledge SQL Server 2008 will only allow one clustered index per table. For the sake of this question let's say I have a list of user-submitted stories that contains the following columns. ID (int, primary key) Title (nvarchar) Url (nvarchar) UniqueName (nvarchar) This is the url slug (blah-blah-blah) CategoryID (int, FK to Cate...
I would like to set up a SQL Server 2008 Alert to notify me when any procedure executes for 1 second or longer (just an example). Any ideas? EDIT: Okay, it seems this is not possible. But, just to nudge you in another direction, I know that there are statistics tables in the master database which hold compilation counts, number of cal...
I have a genealogical database (about sheep actually), that is used by breeders to research genetic information. In each record I store fatherid and motherid. In a seperate table I store complete 'roll up' information so that I can quickly tell the complete family tree of any animal without recursing thru the entire database... Recently...
Hi, If I have tables like this: ImportSet1.Users ImportSet2.Users ImportSet3.Users Then in my sproc I do this: CREATE PROCEDURE [ImportSet2].[UpdateUsers] ... UPDATE Users set blah = 234 WHERE id = 234 This seems to work, but why? Since when did that prefix [ImportSet2] signify part of the table name? (in this case ImportSet2...
Hi. I've encountered this problem (while trying to add SQL Server Database (.mdf) file to my asp.net mvc project): Connections to SQL Server files (.mdf) require SQL Server Express 2005 to function properly. Please verify the installation of the component or download from the URL: http://go.microsoft.com/fwlink/?LinkId=49251* I have ...
With SQLServer 2008 I'm trying to transform two LineStrings to a multiple LineStrings (preferably a MultiLineString), by splitting them up in part based on each other. L1 |---------------| L2 |----| = |----|----|-----| Thinking in generic terms I can get the centerpart by using L1.STIntersection(L2). The two other parts I can ...
Can someone please outline the process of building a SQL Server Reporting Services 2008 report based on a ADO.Net Entity Framework Entity Data Model? If you know of any example, please point me to it. Thank you! ...
I am in the process of simplifying a complicated select statement, so thought I would use common table expressions. Declaring a single cte works fine. WITH cte1 AS ( SELECT * from cdr.Location ) select * from cte1 Is it possible to declare and use more than one cte in the same SELECT? ie this sql gives an error WITH cte1 a...