Questions tagged [sql-server-2008]
Use this tag for questions specific to the 2008 version of Microsoft's SQL Server.
53,799
questions
2181
votes
47
answers
3.7m
views
How to return only the Date from a SQL Server DateTime datatype
SELECT GETDATE()
Returns: 2008-09-22 15:24:13.790
I want that date part without the time part: 2008-09-22 00:00:00.000
How can I get that?
- 34.4k
2172
votes
33
answers
1.7m
views
How to check if a column exists in a SQL Server table
I need to add a specific column if it does not exist. I have something like the following, but it always returns false:
IF EXISTS(SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE ...
- 22.1k
1739
votes
14
answers
2.1m
views
How can I delete using INNER JOIN with SQL Server?
I want to delete using INNER JOIN in SQL Server 2008.
But I get this error:
Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'INNER'.
My code:
DELETE
FROM WorkRecord2
INNER ...
- 18k
1139
votes
41
answers
445k
views
Parameterize an SQL IN clause
How do I parameterize a query containing an IN clause with a variable number of arguments, like this one?
SELECT * FROM Tags
WHERE Name IN ('ruby','rails','scruffy','rubyonrails')
ORDER BY Count ...
- 63.7k
1042
votes
24
answers
3.1m
views
How can I get column names from a table in SQL Server?
I want to query the name of all columns of a table. I found how to do this in:
Oracle
MySQL
PostgreSQL
But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?
- 26k
1011
votes
25
answers
1.7m
views
Reset identity seed after deleting records in SQL Server
I have inserted records into a SQL Server database table. The table had a primary key defined and the auto increment identity seed is set to “Yes”. This is done primarily because in SQL Azure, each ...
- 18.5k
953
votes
15
answers
694k
views
Sql Server 'Saving changes is not permitted' error ► Prevent saving changes that require table re-creation
When I create a table in SQL Server and save it, if I try to edit the table design, like change a column type from int to real, I get this error:
Saving changes is not permitted. The change you ...
- 9,945
803
votes
11
answers
1.5m
views
Rename column SQL Server 2008
I am using SQL Server 2008 and Navicat. I need to rename a column in a table using SQL.
ALTER TABLE table_name RENAME COLUMN old_name to new_name;
This statement doesn't work.
- 9,512
625
votes
7
answers
1.6m
views
How to turn IDENTITY_INSERT on and off using SQL Server 2008?
Why am I getting an error doing an insert when IDENTITY_INSERT is set to OFF?
How do I turn it on properly in SQL Server 2008? Is it by using SQL Server Management Studio?
I have run this query:
...
- 29k
613
votes
9
answers
1.3m
views
UPDATE and REPLACE part of a string
I've got a table with two columns, ID and Value. I want to change a part of some strings in the second column.
Example of Table:
ID Value
---------------------------------
1 c:...
- 6,693
585
votes
29
answers
1.4m
views
Delete duplicate rows keeping the first row
How can I delete duplicate rows where no unique row id exists?
My table is
col1 col2 col3 col4 col5 col6 col7
john 1 1 1 1 1 1
john 1 1 1 1 1 1
sally 2 2 2 ...
- 10.9k
538
votes
8
answers
306k
views
Why use the INCLUDE clause when creating an index?
While studying for the 70-433 exam I noticed you can create a covering index in one of the following two ways.
CREATE INDEX idx1 ON MyTable (Col1, Col2, Col3)
-- OR --
CREATE INDEX idx1 ON MyTable (...
- 12.6k
496
votes
5
answers
1.4m
views
Efficiently convert rows to columns in sql server
I'm looking for an efficient way to convert rows to columns in SQL server, I heard that PIVOT is not very fast, and I need to deal with lot of records.
This is my example:
Id
Value
ColumnName
1
John
...
- 5,003
469
votes
14
answers
896k
views
How to set a default value for an existing column
This isn't working in SQL Server 2008:
ALTER TABLE Employee ALTER COLUMN CityBorn SET DEFAULT 'SANDNES'
The error is:
Incorrect syntax near the keyword 'SET'.
What am I doing wrong?
- 25.8k
463
votes
19
answers
1.2m
views
How to avoid the "divide by zero" error in SQL?
I have this error message:
Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered.
What is the best way to write SQL code so that I will never see this error message again?
I could ...
- 13.5k
429
votes
35
answers
1.4m
views
How can I select the first day of a month in SQL?
How can one select the first day of the month of a given DateTime variable?
I know it's quite easy to do using this kind of code:
select CAST(CAST(YEAR(@mydate) AS VARCHAR(4))
+ '/' + CAST(MONTH(@...
- 32k
429
votes
9
answers
802k
views
Select statement to find duplicates on certain fields
Can you help me with SQL statements to find duplicates on multiple fields?
For example, in pseudo code:
select count(field1,field2,field3)
from table
where the combination of field1, field2, ...
- 8,030
422
votes
15
answers
814k
views
SQL Server - Return value after INSERT
I'm trying to get a the key-value back after an INSERT-statement.
Example:
I've got a table with the attributes name and id. id is a generated value.
INSERT INTO table (name) VALUES('bob');
...
- 12.1k
413
votes
14
answers
1.1m
views
SQL Server Insert if not exists
I want to insert data into my table, but insert only data that doesn't already exist in my database.
Here is my code:
ALTER PROCEDURE [dbo].[EmailsRecebidosInsert]
(@_DE nvarchar(50),
@_ASSUNTO ...
- 4,261
409
votes
21
answers
893k
views
How do I find a stored procedure containing <text>?
I need to search a SQL server 2008 for stored procedures containing where maybe
the name of a database field or variable name.
- 17.4k
406
votes
8
answers
1.5m
views
How do I see active SQL Server connections?
I am using SQL Server 2008 Enterprise. I want to see any active SQL Server connections, and the related information of all the connections, like from which IP address, connect to which database or ...
- 45.2k
378
votes
10
answers
373k
views
Connect different Windows User in SQL Server Management Studio (2005 or later)
Is there a way in SQL Server Management Studio 2005 (or later) to change the Windows Authentication user (as you could in SQL Server 2000 and older)?
This is the general connection properties dialog(...
- 3,781
355
votes
3
answers
411k
views
Can I use multiple "with"?
Just for example:
With DependencedIncidents AS
(
SELECT INC.[RecTime],INC.[SQL] AS [str] FROM
(
SELECT A.[RecTime] As [RecTime],X.[SQL] As [SQL] FROM [EventView] AS A
CROSS ...
- 33.2k
337
votes
7
answers
1.0m
views
SQL Server SELECT INTO @variable?
I have the following code in one of my Sql (2008) Stored Procs which executes perfectly fine:
CREATE PROCEDURE [dbo].[Item_AddItem]
@CustomerId uniqueidentifier,
@Description ...
- 9,837
336
votes
17
answers
220k
views
Entity Framework: There is already an open DataReader associated with this Command
I am using Entity Framework and occasionally i will get this error.
EntityCommandExecutionException
{"There is already an open DataReader associated with this Command which must be closed first."}
...
- 24.4k
334
votes
16
answers
765k
views
SQL Server add auto increment primary key to existing table
As the title, I have an existing table which is already populated with 150000 records. I have added an Id column (which is currently null).
I'm assuming I can run a query to fill this column with ...
- 52.9k
331
votes
5
answers
445k
views
SET versus SELECT when assigning variables?
What are the differences between the SET and SELECT statements when assigning variables in T-SQL?
- 5,703
324
votes
12
answers
308k
views
SQL Server principal "dbo" does not exist,
I am getting the following error
Cannot execute as the database principal because the principal "dbo"
does not exist, this type of principal cannot be impersonated,
or you do not have permission.
I ...
- 9,304
323
votes
15
answers
801k
views
Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)
I have a development database that re-deploy frequently from a Visual Studio Database project (via a TFS Auto Build).
Sometimes when I run my build I get this error:
ALTER DATABASE failed because a ...
- 80.3k
309
votes
2
answers
206k
views
How do I move a table into a schema in T-SQL
I want to move a table into a specific Schema using T-SQL? I am using SQL Server 2008.
- 8,790
307
votes
9
answers
444k
views
How can I group by date time column without taking time into consideration
I have a bunch of product orders and I'm trying to group by the date and sum the quantity for that date. How can I group by the month/day/year without taking the time part into consideration?
3/8/...
- 19.8k
294
votes
4
answers
285k
views
Unique constraint on multiple columns
CREATE TABLE [dbo].[user](
[userID] [int] IDENTITY(1,1) NOT NULL,
[fcode] [int] NULL,
[scode] [int] NULL,
[dcode] [int] NULL,
[name] [nvarchar](50) NULL,
...
- 12.8k
294
votes
21
answers
794k
views
How to update Identity Column in SQL Server?
I have SQL Server database and I want to change the identity column because it started
with a big number 10010 and it's related with another table, now I have 200 records and I want to fix this issue ...
- 4,941
293
votes
3
answers
290k
views
How to write UPDATE SQL with Table alias in SQL Server 2008?
I have a very basic UPDATE SQL -
UPDATE HOLD_TABLE Q SET Q.TITLE = 'TEST' WHERE Q.ID = 101;
This query runs fine in Oracle, Derby, MySQL - but it fails in SQL server 2008
with following error:
"...
- 5,139
290
votes
4
answers
502k
views
What is the maximum characters for the NVARCHAR(MAX)? [duplicate]
I have declared a column of type NVARCHAR(MAX) in SQL Server 2008, what would be its exact maximum characters having the MAX as the length?
- 3,539
284
votes
18
answers
481k
views
How to drop all tables in a SQL Server database?
I'm trying to write a script that will completely empty a SQL Server database. This is what I have so far:
USE [dbname]
GO
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'
EXEC ...
- 2,843
281
votes
8
answers
236k
views
SQL Server Management Studio, how to get execution time down to milliseconds
When I submit a batch (e.g., perform a query) in SSMS, I see the time it took to execute in the status bar. Is it possible to configure SSMS to show the query time with millisecond resolution?
Here ...
- 73.1k
277
votes
2
answers
111k
views
SQL Server. How to refresh the intellisense? [duplicate]
Possible Duplicate:
Lost the IntelliSense
SQL Server 2008 Intellisense problem
In SQL Server 2008 R2, I've just imported a new data table and/or renamed fields in an existing table (it happens either ...
- 11.7k
276
votes
6
answers
1.3m
views
How to create a table from select query result in SQL Server 2008 [duplicate]
I want to create a table from select query result in SQL Server, I tried
create table temp AS select.....
but I got an error
Incorrect syntax near the keyword 'AS'
- 3,010
273
votes
4
answers
506k
views
How to wait for 2 seconds?
How does one cause a delay in execution for a specified number of seconds?
This doesn't do it:
WAITFOR DELAY '00:02';
What is the correct format?
- 24.2k
273
votes
5
answers
186k
views
"Prevent saving changes that require the table to be re-created" negative effects
Preamble
I was modifying a column in SQL Server 2008 today, changing the datatype from something like currency(18,0) to (19,2).
I got the error "The changes you have made require the following ...
- 2,980
272
votes
16
answers
923k
views
sql query to return differences between two tables
I am trying to compare two tables, SQL Server, to verify some data. I want to return all the rows from both tables where data is either in one or the other. In essence, I want to show all the ...
- 12.2k
272
votes
19
answers
1.2m
views
SQL Server query to find all permissions/access for all users in a database
I would like to write a query on a sql 2008 that will report all the users that have access to a specific database, or objects within the database such as tables, views, and stored procedures, either ...
- 45.6k
271
votes
12
answers
621k
views
Check if a row exists, otherwise insert
I need to write a T-SQL stored procedure that updates a row in a table. If the row doesn't exist, insert it. All this steps wrapped by a transaction.
This is for a booking system, so it must be atomic ...
Robert
266
votes
14
answers
438k
views
SQL Server Configuration Manager cannot be found
After installing SQL Server 2008, I cannot find the SQL Server Configuration Manager in Start / SQL Server 2008 / Configuration Tools menu.
What should I do to install this tool?
- 3,605
264
votes
11
answers
242k
views
How do you view ALL text from an ntext or nvarchar(max) in SSMS?
How do you view ALL text from an NTEXT or NVARCHAR(max) in SQL Server Management Studio? By default, it only seems to return the first few hundred characters (255?) but sometimes I just want a quick ...
- 9,625
264
votes
12
answers
996k
views
SQL-script: How to write ALTER statements to set Primary key on an existing table?
I have an existing table called Person which already has existing 1000 rows of data. In this table I have 5 fields:
personId
Pname
PMid
Pdescription
Pamt
The table already has an existing compound-...
- 3,789
260
votes
10
answers
463k
views
How to get a float result by dividing two integer values using T-SQL?
Using T-SQL and Microsoft SQL Server I would like to specify the number of decimal digits when I do a division between 2 integer numbers like:
select 1/3
That currently returns 0. I would like it to ...
- 15.1k
254
votes
10
answers
453k
views
How to restore to a different database in SQL Server?
I have a backup of Database1 from a week ago. The backup is done weekly in the scheduler and I get a .bak file. Now I want to fiddle with some data so I need to restore it to a different database - ...
- 6,427
253
votes
10
answers
574k
views
Changing the maximum length of a varchar column?
I'm trying to update the length of a varchar column from 255 characters to 500 without losing the contents. I've dropped and re-created tables before but I've never been exposed to the alter statement ...
- 9,700