sql-server-2005

SQL Server 2005 FOR XML

Hi, I want to export data from a table to a specifically formatted XML file. I am fairly new to XML files, so what I am after may be quite obvious but I just cant find what I am looking for on the net. The format of the XML results I need are: <data> <event start="May 28 2006 09:00:00 GMT" end="Jun...

Convert datetime field in sql server 2005....

I have a datetime field which has a value like this 5/11/2010 12:04:20 PM .... I am converting this field convert(varchar, dbo.Clients.CreatedDate,103) as CreatedDate and i get the result as 11/5/2010..... But how to get 11/5/2010 12:04 PM..... ...

SQL Server: "Mostly-unique" index

In a table i want to ensure that only unique vales exist over the five-column key: Timestamp Account RatingDate TripHistoryKey EventAction ========= ======= ========== ============== =========== 2010511 1234 2010511 1 INSERT 2010511 1234 2010511 4 INSERT 2010511 1234 2010511 7 ...

Collation configuration in SQL Server 2005 Express setup

I had to re-install my SQL Server 2005 Express instance due to a conflict with collations. Now, once in the setup, I can't see the SQL_Latin1_General_CP1_CI_AI collation, I see "Latin1_General" instead, and I've been told is not the same. Could you please tell me where I can find this? ...

XML in SQL Server 2005? Better than JSON in Varchar?

What are the benefits of storing XML in SQL Server over storing JSON in a varchar field? Any tutorial available for how to use the XML data type effectively? Do I need to provide the dtd / xml schema somehow? I've heard it is optional, right? Thank you. UPDATE: here's the answer to the last part of the Q. XML schema information ...

Nested SQL Select statement fails on SQL Server 2000, ok on SQL Server 2005

Here is the query: INSERT INTO @TempTable SELECT UserID, Name, Address1 = (SELECT TOP 1 [Address] FROM (SELECT TOP 1 [Address] FROM [UserAddress] ua INNER JOIN UserAddressOrder uo ON ua.UserID = uo.UserID WHERE ua.UserID = u.UserID ORDER BY uo.AddressOrder ASC) q ...

tsql to know when a database was last shrunk

Hi, In SQL 2000, 2005 and 2008, how can I know when databases were last shrunk on a MS SQL server? I want to do this using TSQL. Regards Manjot ...

Update thousands of records in a DataSet to SQL Server

I have half a million records in a data set of which 50,000 are updated. Now I need to commit the updated records back to the SQL Server 2005 Database. What is the best and efficient way to do this considering the fact that such updates could be frequent (though concurrency is not an issue but performance is) ...

UPDATE from SELECT complains about more that one value returned

I have this data structure: request ======= building_id lot_code building ======== building_id lot_id lot === lot_id lot_code The request table is missing the value for the building_id column and I want to fill it in from the other tables. So I've tried this: UPDATE request SET building_id = ( SELECT bu.building_id FROM bui...

tsql source control using msscci

Hi All, I have the TFS MSSCCI installed, now what? How do I use it to put say like a stored procedure under source control? Thanks, rodchar ...

Sql compression and backing up in sql server 2005

Hi there I want to backup my database with compression. This is my code : BACKUP DATABASE dbbbb TO DISK = N'C:\\dbbb.bak' WITH COMPRESSION this running correctly in Sql Server 2008. But my server has Sql Server 2005 and COMPRESSION is not a recognized BACKUP option in 2005. How can i compress my backup in 2005 Thank you for your he...

NHibernate How to specify custom sql type only in production

I am saving binary files into a Sql Server 2005 Db using Fluent NHibernate. However, I am using SQLite to run my (pseudo) Unit Tests. I need to use a custom Sql type for Ms Sql, but it would throw an error on SqlLite. What strategies can I use? This is the Map file: public class BinaryFile { public BinaryFile() { ...

Update rows by separating values from one column and inserting into 2 diff. columns

For eg. I have table T1. There are 4 columns in it c1 c2 c3 and c4. I have c1 as id, c2 contains combine name and address. c3 and c4 are empty. There are multiple rows for given id. Let's say there are 10 rows for id=10. What I want is for all the rows with id=10, I want to read c2, separate values in c2 as name and address and store n...

SQL 2005 Merge / concatenate multiple rows to one column

Hi, We have a bit of a SQL quandry. Say I have a results that look like this... 61E77D90-D53D-4E2E-A09E-9D6F012EB59C | A 61E77D90-D53D-4E2E-A09E-9D6F012EB59C | B 61E77D90-D53D-4E2E-A09E-9D6F012EB59C | C 61E77D90-D53D-4E2E-A09E-9D6F012EB59C | D 7ce953ca-a55b-4c55-a52c-9d6f012ea903 | E 7ce953ca-a55b-4c55-a52c-9d6f012ea903 | F is...

Basic SQL query question

Hello, I have the following query: SELECT Base.ReportDate, Base.PropertyCode, Base.FirstName, Base.MiddleName, Base.LastName, Person.FirstName, Person.MiddleName, Person.LastName FROM LeaseT INNER JOIN Base ON LeaseT.LeaseID = Base.LeaseID INNER JOIN Person ON LeaseT.TenantID = Person.ID works fine, ...

SQL Server 2005 Data Encryption And LINQ TO SQL

I have a ASP.NET MVC application and I have used Linq-to-SQL queries to get data from my SQL Server database. Now for security purposes I need to encrypt my database. How does Linq-to-SQL work with encrypted databases? ...

To get data from table in script from sql server 2005

Hi Experts, I am using sql server 2005 I have a table [say tblHistory] and this table contains 100 rows. I have created the same table at the server, but the table doesn't have the data, I want data from tblHistory to convert into INSERT INTO tblHistory ------ so that I could run the script on the server to fill the database. Pleas...

Move row from one table to another?

I have two tables with the same column definitions. I need to move (not copy) a row from one table to another. Before I go off and use INSERT INTO/DELETE (in a transaction), is there a smarter way? SQL Server 2005 ...

Use SQL to clone a tree structure represented in a database

Given a table that represents a hierarchical tree structure and has three columns ID (Primary Key, not-autoincrementing) ParentGroupID SomeValue I know the lowest most node of that branch, and I want to copy that to a new branch with the same number of parents that also need to be cloned. I am trying to write a single SQL INSERT IN...

SQL Server 2005 config file path of databases loaded

I need to find the path of the database (MDF) or at least the database logs loaded but for some reasons I cannot login through the SQL Server so I need to know if you know any file/config file that keeps the path of the databases or log file . ...