sql-server-2005

SQL 2005 For XML Explicit - Need help formatting

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...

Paging SQL Server 2005 Results

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...

SQL Server 2005 and 2008 on same developer machine?

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...

Conditional Visibility and Page Breaks with SQL Server 2005 Reporting Services

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...

How do you kill all current connections to a SQL Server 2005 database?

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? ...

SQL Server 2008 vs 2005 Linq integration

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? ...

How to create a new instance of 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? ...

SQL2005: Linking a table to multiple tables and retaining Ref Integrity?

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...

Diagnosing Deadlocks in SQL Server 2005

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...

SQL Server 2005 - Export table programatically (run a .sql file to rebuild it)

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...

Cannot Add a Sql Server Login

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...

How do I create a mapping table in SQL Server Management Studio?

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 ...

SQL 2005 Book For Optimization Techniques

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...

What's the fastest way to bulk insert a lot of data in SQL Server (C# client)

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. ...

Create a database from another database?

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...

Can you perform an AND search of keywords using FREETEXT() on SQL Server 2005?

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...

What's the simplest way to execute a query in Visual C++

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...

Upgrading Sharepoint 3.0 to SQL 2005 Backend?

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? ...

What are the benefits of using partitions with the Enterprise edition of SQL 2005

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...

Can I maintain state between calls to a SQL Server UDF?

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....