Stack Overflow
Stack Overflow

Questions tagged [sql-server-2005]

Use this tag for questions specific to the 2005 version of Microsoft's SQL Server.

SQL Server 2005 (codename Yukon, version 9.00), released in October 2005, is the successor to SQL Server 2000. It included native support for managing XML data, in addition to relational data.

For this purpose, it defined an XML data type that could be used either as a data type in database columns or as literals in queries. XML columns can be associated with XSD schemas; XML data being stored is verified against the schema. XML is converted to an internal binary data type before being stored in the database. Specialized indexing methods were made available for XML data. XML data is queried using XQuery; Common Language Runtime (CLR) integration was a main feature with this edition, enabling one to write SQL code as Managed Code by the CLR. SQL Server 2005 added some extensions to the T-SQL language to allow embedding XQuery queries in T-SQL.

In addition, it also defines a new extension to XQuery, called XML DML, that allows query-based modifications to XML data. SQL Server 2005 also allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests. When the data is accessed over web services, results are returned as XML.

Resources

18391 questions
8
votes
2 answers

How to create a table type in Sql Server 2005

I'm trying to create a table type in sql server 2005. Here is what my code looks like: CREATE TYPE NameResourceType AS TABLE ( ID int, [Value] Varchar(256) ) GO I receive the following error: Incorrect syntax near the keyword 'AS'.
Nikola Stjelja
  • 3,659
  • 9
  • 37
  • 45
8
votes
5 answers

sql server : get default value of a column

I execute a select to get the structure of a table. I want to get info about the columns like its name or if it's null or if it's primary key.. I do something like this ....sys.columns c... c.precision, c.scale, c.is_nullable as…
luke
  • 235
  • 2
  • 3
  • 7
8
votes
5 answers

Computed column should result to string

Here is a snap of my database. Both col1 and col2 are declared as int. My ComputedColumn currently adds the Columns 1 and 2, as follows... col1 col2 ComputedColumn 1 2 3 4 1 5 Instead of this, my ComputedColumn should join the…
OrElse
  • 9,709
  • 39
  • 140
  • 253
8
votes
5 answers

Insert or update if record is in table

I have a tables Cars and CarDescriptions cars: IDCar(int, PK, autoincrement) carsDesciptions(IDDescription, Header(nvarchar),Content(nvarchar),idCar(int,FK) In application I am adding cars and editing existing ones. My problems: 1.How to save…
user278618
  • 19,306
  • 42
  • 126
  • 196
8
votes
1 answer

Retaining NULLs in numerical columns using SSIS Import/Export Wizard?

I am having a problem uploading data from tab-delimited flat files (TSV files) into SQL Server 2005 using the SSIS Data Import wizard. I did not experience this problem using the equivalent procedure in SQL Server 2000, and I have checked that the…
Lethanta
  • 113
  • 1
  • 6
8
votes
4 answers

Separate tables/databases for reporting and CRUD operations

Periodically users running reports are blocking users doing CRUD operations and causing time-outs. I'd like to create duplicate locations of the current tables for the report users. I was thinking of creating a job that backs-up my app's database,…
Chris Burgess
  • 5,787
  • 13
  • 54
  • 69
8
votes
1 answer

Paginated query using sorting on different columns using ROW_NUMBER() OVER () in SQL Server 2005

Let's suppose I'm using the Northwind database and I would like to run a query via a stored procedure that contains, among other parameters, the following: @Offset to indicate where the pagination starts, @Limit to indicate the page…
Leandro López
  • 2,185
  • 1
  • 15
  • 17
8
votes
5 answers

How to find what Stored Procedures are using what indexes?

I am trying to determine what indexes are no longer used in my Database. I have had great luck using the following query: SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], I.[NAME] AS [INDEX NAME], i.Type_Desc as [Index…
Abe Miessler
  • 82,532
  • 99
  • 305
  • 486
8
votes
3 answers

Using case statement in update query

Is it possible to use case statement within an update query? I need to do something like this: If person name starts with 'S' then append '1', else append '2'. I tried this in sql server and it didn't work UPDATE PERSON CASE WHEN NAME LIKE 'S%'…
Prabhu
  • 3,434
  • 8
  • 40
  • 48
8
votes
5 answers

How do I calculate the SIZE of stored procedures in SQL Server 2005?

I was asked for a comprehensive breakdown on space used within a specific database. I know I can use sys.dm_db_partition_stats in SQL Server 2005 to figure out how much space each table in a database is using, but is there any way to determine the…
BradC
  • 39,306
  • 13
  • 73
  • 89
8
votes
1 answer

Renamed a table to sp_help and cannot rename it back

Somehow one of our tables was accidentally renamed to 'sp_help'. We have tried to rename it back to the previous table name using sp_rename but we are getting the following error: Msg 15225, Level 11, State 1, Procedure sp_rename, Line 332 No item…
spfuller
  • 178
  • 8
8
votes
3 answers

selecting latest rows per distinct foreign key value

excuse the title, i couldn't come up with something short and to the point... I've got a table 'updates' with the three columns, text, typeid, created - text is a text field, typeid is a foreign key from a 'type' table and created is a timestamp. A…
John Beynon
  • 37,398
  • 8
  • 88
  • 97
8
votes
4 answers

Best Practice for Database Encryption in SQL Server 2005

I need to develop an application which stores data in a SQL Server 2005 database (the app itself will be either a WCF Service or an Asp.Net Web Service). Now, this data is supremely confidential, and I need to have it stored in an encrypted form in…
Vaibhav
  • 11,310
  • 11
  • 51
  • 70
8
votes
4 answers

Query on datetime fields with milliseconds gives wrong result in SQL Server

I'm running into an odd bug using datetime fields in SQL Server 2005. The datetime field shows up with millisecond-level accuracy, but it looks like the milliseconds are not always used. Here's my test query: SELECT col1, YEAR(col1) AS yr,…
Jenni
  • 1,668
  • 4
  • 20
  • 28
8
votes
2 answers

How to show value printed by sql query in message box

I want to print a value that is returned by SQL Server. If NOT Exists(SELECT * FROM ItemList WHERE ItemName='txtItemNama') BEGIN INSERT INTO ItemList (ItemName) VALUES('txtItemNamea') END ELSE BEGIN Print 'Duplicate' …
Shantanu Gupta
  • 20,688
  • 54
  • 182
  • 286
1 2 3
…
99
100