I have a table with a structure like the following: ------------------------------ LocationID | AccountNumber ------------------------------ long-guid-here | 12345 long-guid-here | 54321 To pass into another stored procedure, I need the XML to look like this: <root><clientID>12345</clientID><clientID>54321</clientID></root> The...
How do I page results in SQL Server 2005? I SQL Server 2000, there was no reliable way todo this but I'm now wondering if SQL Server 2005 has any built in method. What I mean by paging is, for example, if I list users by their username, I want to be able to only return the first 10 records, then the next 10 records and so on. Any help...
Has anyone tried installing SQL Server 2008 Developer on a machine that already has 2005 Developer installed? I am unsure if I should do this, and I need to keep 2005 on this machine for the foreseeable future in order to test our application easily. Since I sometimes need to take backup files of databases and make available for other p...
I know there's a bug with conditional visibility and page breaks with SQL 2005, but I wonder if anyone has come up with a work around. I have a table that has a conditional visibility expression, and I need a page break at the end of the table. If I set the PageBreakAtEnd property to true. It is ignored no matter what. Remove the visi...
I want to rename a database, but keep getting the error that 'couldn't get exclusive lock' on the database, which implies there is some connection(s) still active. How can I kill all the connections to the database so that I can rename it? ...
Linq To SQL or Entity framework both integrate nicely with SQL Server 2005. The SQL Server 2008 spec sheet promises even better integration - but I can't see it. What are some examples of what you can do Linq-wise when talking to a 2008 server that you can't when talking to SQL Server 2005? ...
I forgot my password for Sql Server 2005. Windows Authentication is not enabled so I cannot login. How can I remove the current instance and create a new db instance? Or is there a better solution exists? ...
Howdy, Here is a simplification of my database: Table: Property Fields: ID, Address Table: Quote Fields: ID, PropertyID, BespokeQuoteFields... Table: Job Fields: ID, PropertyID, BespokeJobFields... Then we have other tables that relate to the Quote and Job tables individually. I now need to add a Message table where users can recor...
We're seeing some pernicious, but rare, deadlock conditions in the Stack Overflow SQL Server 2005 database. I attached the profiler, set up a trace profile using this excellent article on troubleshooting deadlocks, and captured a bunch of examples. The weird thing is that the deadlocking write is always the same: UPDATE [dbo].[Posts] S...
I have a database with a table Customers that have some data I have another database in the office that everything is the same, but my table Customers is empty How can I create a sql file in SQL Server 2005 (T-SQL) that takes everything on the table Customers from the first database, creates a, let's say, buildcustomers.sql, I zip that f...
When I try to create a SQL Server Login by saying CREATE LOGIN [ourdomain\SQLAccessGroup] FROM WINDOWS; I get this error The server principal 'ourdomain\SQLAccessGroup' already exists. However, when I try this code DROP LOGIN [ourdomain\SQLAccessGroup] I get this error Cannot drop the login 'ourdomain\SQLAccessGroup', because it...
Hi - I'm learning about table design in SQL and I'm wonder how to create a mapping table in order to establish a many-to-many relationship between two other tables? I think the mapping table needs two primary keys - but I can't see how to create that as it appears there can only be 1 primary key column? I'm using the Database Diagrams ...
My knowledge of SQL has been mostly gathered through immediate need as opposed to formal training. The project I'm working on now requires the next level of SQL (specifically SQL Server 2005) knowledge. That is, I need to know techniques for optimizing the schema, writing optimized queries and even some information about replication an...
I am hitting some performance bottlenecks with my C# client inserting bulk data into a SQL Server 2005 database and I'm looking for ways in which to speed up the process. I am already using the SqlClient.SqlBulkCopy (which is based on TDS) to speed up the data transfer across the wire which helped a lot, but I'm still looking for more. ...
Is there an automatic way in SQL Server 2005 to create a database from several tables in another database? I need to work on a project and I only need a few tables to run it locally, and I don't want to make a backup of a 50 gig DB. UPDATE I tried the Tasks -> Export Data in Management studio, and while it created a new sub database wi...
There is a request to make the SO search default to an AND style functionality over the current OR when multiple terms are used. The official response was: not as simple as it sounds; we use SQL Server 2005's FREETEXT() function, and I can't find a way to specify AND vs. OR -- can you? So, is there a way? There are a number of re...
I'm using Visual C++ 2005 and would like to know the simplest way to connect to a MS SQL Server and execute a query. I'm looking for something as simple as ADO.NET's SqlCommand class with it's ExecuteNonQuery(), ExecuteScalar() and ExecuteReader(). Sigh offered an answer using CDatabase and ODBC. Can anybody demonstrate how it would b...
We're trying to get rid of all of our SQL Server 2000 databases to re purpose our old DB server... Sharepoint 3.0 is being a showstopper. I've looked at a lot of guides from Microsoft and tried the instructions in those. I've also just tried the good ol' exec sp_detach_db / sp_attach_db with no luck. Has anyone actually done this? ...
I'm comparing between two techniques to create partitioned tables in SQL 2005. Use partitioned views with a standard version of SQL 2005 (described here) Use the built in partition in the Enterprise edition of SQL 2005 (described here) Given that the enterprise edition is much more expensive, I would like to know what are the main be...
I have a SQL script that inserts data (via INSERT statements currently numbering in the thousands) One of the columns contains a unique identifier (though not an IDENTITY type, just a plain ol' int) that's actually unique across a few different tables. I'd like to add a scalar function to my script that gets the next available ID (i.e....