Questions tagged [sql-server-2008-r2]
Use this tag for questions specific to the 2008 R2 version of Microsoft's SQL Server.
sql-server-2008-r2
11,306
questions
418
votes
26
answers
1.3m
views
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated
I have many users on my web site (20000-60000 per day), which is a download site for mobile files. I have remote access to my server (windows server 2008-R2). I've received "Server is ...
- 20.2k
327
votes
15
answers
815k
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 ...
- 81k
276
votes
20
answers
467k
views
Exit single-user mode
Currently, my database is in Single User mode. When I try to expand me database, I get an error:
The database 'my_db' is not accessible.(ObjectExplorer)
Also, when I try to delete the database, I ...
- 16.2k
268
votes
14
answers
452k
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,625
258
votes
1
answer
692k
views
CREATE TABLE IF NOT EXISTS equivalent in SQL Server [duplicate]
CREATE TABLE IF NOT EXISTS works on mysql but fails with SQL Server 2008 R2.
What is the equivalent syntax?
- 4,705
230
votes
11
answers
191k
views
Error on renaming database in SQL Server 2008 R2
I am using this query to rename the database:
ALTER DATABASE BOSEVIKRAM MODIFY NAME = [BOSEVIKRAM_Deleted]
But it shows an error when excuting:
Msg 5030, Level 16, State 2, Line 1
The database ...
- 3,327
224
votes
9
answers
655k
views
How to check date of last change in stored procedure or function in SQL server
I need to check when function was changed last time. I know how to check creation date (it is in function properties window in SQL Server Management Studio).
I found that in SQL Server 2000 it wasn't ...
- 9,694
211
votes
13
answers
794k
views
Select SQL Server database size
how can i query my sql server to only get the size of database?
I used this :
use "MY_DB"
exec sp_spaceused
I got this :
database_name database_size unallocated space
My_DB 17899....
- 3,464
190
votes
8
answers
372k
views
SQL Server - Create a copy of a database table and place it in the same database?
I have a table ABC in a database DB. I want to create copies of ABC with names ABC_1, ABC_2, ABC_3 in the same DB. How can I do that using either Management Studio (preferably) or SQL queries ?
This ...
- 3,761
184
votes
33
answers
658k
views
A connection was successfully established with the server, but then an error occurred during the login process. (Error Number: 233) [closed]
I am having error while connecting to SQL Server:
Details in Stack Trace are:
===================================
Cannot connect to ServerName.
===================================
A connection ...
- 19.3k
164
votes
11
answers
305k
views
SQL Update with row_number()
I want to update my column CODE_DEST with an incremental number. I have:
CODE_DEST RS_NOM
null qsdf
null sdfqsdfqsdf
null qsdfqsdf
I would like to update it to be:
CODE_DEST ...
- 4,171
161
votes
9
answers
537k
views
Check if table exists and if it doesn't exist, create it in SQL Server 2008
I am writing a Stored procedure in SQL Server 2008.
I need to check if a table exists in the database. If it doesn't then I need to create it.
How do I do this?
- 11.2k
135
votes
15
answers
371k
views
SQL Server 2008: How to query all databases sizes?
I have MS SQL 2008 R2, 500 databases.
What is the most efficient, easiest and 'modern' way to query all databases sizes.
The output should have columns:
DatabaseName
DataFilesSize
LogFilesSize
- 77.8k
128
votes
11
answers
465k
views
Search of table names
I use the following to search for strings within the stored procedures of a specific database:
USE DBname
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%xxx%'
Is it easy to ...
- 35.2k
122
votes
12
answers
259k
views
Could not load file or assembly Microsoft.SqlServer.management.sdk.sfc version 11.0.0.0
I have installed MS SQL Server 2008 R2 and when I try to update model from database under EDMX file I am facing that error.
Could not load file or assembly Microsoft.SqlServer.management.sdk.sfc
...
- 37.1k
121
votes
6
answers
282k
views
Partition Function COUNT() OVER possible using DISTINCT
I'm trying to write the following in order to get a running total of distinct NumUsers, like so:
NumUsers = COUNT(DISTINCT [UserAccountKey]) OVER (PARTITION BY [Mth])
Management studio doesn't seem ...
- 35.2k
110
votes
6
answers
274k
views
Calculating distance between two points (Latitude, Longitude)
I am trying to calculate the distance between two positions on a map.
I have stored in my data: Longitude, Latitude, X POS, Y POS.
I have been previously using the below snippet.
DECLARE @orig_lat ...
- 1,823
109
votes
4
answers
332k
views
How to query for Xml values and attributes from table in SQL Server?
I have a table that contains a Xml column:
SELECT *
FROM Sqm
A sample of the xml data of a row would be:
<Sqm version="1.2">
<Metrics>
<Metric id="TransactionCleanupThread....
- 253k
108
votes
10
answers
241k
views
Rebuild all indexes in a Database
I have a very large SQL Server 2008 R2 database (1.5TB) and will be copying some data from column to column within the same table. I've been told that the schema has a large number of indexes and was ...
- 1,199
108
votes
7
answers
93k
views
Cannot use UPDATE with OUTPUT clause when a trigger is on the table
I'm performing an UPDATE with OUTPUT query:
UPDATE BatchReports
SET IsProcessed = 1
OUTPUT inserted.BatchFileXml, inserted.ResponseFileXml, deleted.ProcessedDate
WHERE BatchReports.BatchReportGUID = @...
- 253k
106
votes
21
answers
364k
views
Select query to remove non-numeric characters
I've got dirty data in a column with variable alpha length. I just want to strip out anything that is not 0-9.
I do not want to run a function or proc. I have a script that is similar that just ...
- 1,251
89
votes
6
answers
290k
views
SQL Server procedure declare a list
My SQL code is fairly simple. I'm trying to select some data from a database like this:
SELECT * FROM DBTable
WHERE id IN (1,2,5,7,10)
I want to know how to declare the list before the select (in a ...
- 1,031
86
votes
3
answers
30k
views
Is a single SQL Server statement atomic and consistent?
Is a statement in SQL Server ACID?
What I mean by that
Given a single T-SQL statement, not wrapped in a BEGIN TRANSACTION / COMMIT TRANSACTION, are the actions of that statement:
Atomic: either all ...
- 253k
83
votes
15
answers
155k
views
How do I find the data directory for a SQL Server instance?
We have a few huge databases (20GB+) which mostly contain static lookup data. Because our application executes joins against tables in these databases, they have to be part of each developers local ...
- 26.4k
81
votes
5
answers
313k
views
How to solve SQL Server Error 1222 i.e Unlock a SQL Server table
I am working in a database where I load data in a raw table by a data loader. But today the data loader got stuck for unknown reasons. Then I stopped the data loader from windows task manager. But ...
- 936
77
votes
11
answers
165k
views
DateTime fields from SQL Server display incorrectly in Excel
Countless times during the day I am copying and pasting records from SQL Server Management Studio to Excel.
My problem is that a DateTime value such as 8/23/2013 4:51:02 PM does not display ...
- 1,882
74
votes
10
answers
209k
views
"This SqlTransaction has completed; it is no longer usable."... configuration error?
I've been working on this for about a day and a half now, and searched numberous blogs and help articles on the Web. I found several questions on SO related to this error, but I didn't think they ...
- 2,681
73
votes
7
answers
68k
views
Import and Export Wizard with Identity Column
I want to move data from one DB to another, while keeping the identity column values in the old DB. When I run the wizard I get a validation error "Failure inserting into the read-only column "...
- 10.5k
73
votes
3
answers
173k
views
Connecting to MS SQL Server with Windows Authentication using Python?
How do I connect MS SQL Server using Windows Authentication, with the pyodbc library?
I can connect via MS Access and SQL Server Management Studio, but cannot get a working connection ODBC string for ...
- 2,030
70
votes
5
answers
306k
views
CASE WHEN statement for ORDER BY clause
I am using SQL Server 2008 R2.
I want the priority based sorting for records in a table.
So that I am using CASE WHEN statement in ORDER BY clause. The ORDER BY clause is as below :
ORDER BY
...
- 6,680
69
votes
8
answers
339k
views
Using IF ELSE statement based on Count to execute different Insert statements
While I am searching through my database, I run an INSERT statement if I find that a particular item does not exist, and I run a different INSERT statement if I find one or more of this item.
I am ...
- 737
69
votes
6
answers
155k
views
SQL list of all the user defined functions in a database
I am looking for a SQL query that outputs the function definitions for all of the user defined functions in a database catalog.
I have found as far as
SELECT OBJECT_DEFINITION (OBJECT_ID(N'dbo....
- 2,162
66
votes
10
answers
27k
views
How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM in SQL Server 20008R2?
How do you rewrite expressions containing the standard IS DISTINCT FROM and IS NOT DISTINCT FROM operators in the SQL implementation in Microsoft SQL Server 2008R2 that does not support them?
- 16.3k
66
votes
5
answers
245k
views
What is the syntax meaning of RAISERROR()
I just created a Instead After Trigger whose syntax is given below:
Create trigger tgrInsteadTrigger on copytableto
Instead of Insert as
Declare @store_name varchar(30);
declare @sales int;
...
- 717
65
votes
20
answers
135k
views
IntelliSense is not working in SQL Server Management Studio
We use SQL Server Management Studio 2008 R2. IntelliSense works with SA account perfectly. But it is not working with Windows Authentication user. The user has access master db with db_owner role but ...
- 783
64
votes
10
answers
254k
views
Create a view with ORDER BY clause
I'm trying to create a view with an ORDER BY clause. I have create it successfully on SQL Server 2012 SP1, but when I try to re-create it on SQL Server 2008 R2, I get this error:
Msg 102, Level 15,...
- 891
64
votes
6
answers
305k
views
Get last 30 day records from today date in SQL Server
I have small question about SQL Server: how to get last 30 days information from this table
Sample data:
Product:
Pdate
----------
2014-11-20
2014-12-12
2014-11-10
2014-12-13
2014-10-12
2014-11-15
...
- 967
63
votes
6
answers
68k
views
Getting error while running 50 MB script on SQL Server 2008 R2
I am using SQL Server 2008 R2, I have a script to update the DB, that script is approx
50 MB in size and contains some about 800,000 lines.
Error:
TITLE: Microsoft SQL Server Management Studio
...
- 5,362
62
votes
11
answers
139k
views
Remote Procedure call failed with sql server 2008 R2
I am working with SQL Server 2008 R2. I am unable to connect to my database remotely. I got the following error.
A network-related or instance-specific error occurred while establishing a ...
- 1,841
61
votes
9
answers
417k
views
How to find out what is locking my tables?
I have a SQL table that all of a sudden cannot return data unless I include with (nolock) on the end, which indicates some kind of lock left on my table.
I've experimented a bit with sys....
- 8,707
61
votes
3
answers
94k
views
How to use a CTE statement in a table-valued function in SQL Server
I have come to understand that some versions of Microsoft OLE DB Provider for SQL Server (mostly on Windows XP) do not support WITH statement. So, I decided to move my SQL statement into a table-...
- 4,726
60
votes
7
answers
36k
views
Unique key vs. unique index on SQL Server 2008
I have a table called countries and I define the country_name column to be unique by creating a “Index/Key” of type “Unique Key” on SQL Server 2008 R2.
But I have the following questions:
will ...
59
votes
9
answers
280k
views
T-SQL split string based on delimiter
I have some data that I would like to split based on a delimiter that may or may not exist.
Example data:
John/Smith
Jane/Doe
Steve
Bob/Johnson
I am using the following code to split this data into ...
- 3,390
58
votes
1
answer
64k
views
Error Invalid prefix or suffix characters in SQL Server Management Studio
When I try to edit table data in SQL Server Management Studio 2008 R2 (right click in table object, Edit Top 200 rows) I receive this error: "Invalid prefix or suffix characters. (MS Visual Database ...
- 3,826
57
votes
2
answers
95k
views
The specified schema name either does not exist or you do not have permission to use it
I am trying to create replica of my database from SQL server to another.
For that I am generating script from original server and trying to run in another server. I've created database manually with ...
- 3,707
56
votes
4
answers
280k
views
sql server convert date to string MM/DD/YYYY
I am using SQL Server 2008.
I have the following:
select convert(varchar(20),fmdate) from Sery
How do I convert the date to string such that it show as MM/DD/YYYY
- 45.6k
56
votes
6
answers
139k
views
Get AVG ignoring Null or Zero values
How can I get the AVG of a column ignoring NULL and zero values?
I have three columns to get their average, I try to use the following script:
SELECT distinct
AVG(cast(ISNULL(a.SecurityW,0) as ...
- 951
55
votes
5
answers
66k
views
How to cast datetime to datetimeoffset?
How can i convert an SQL Server datetime value to a datetimeoffset value?
For example, an existing table contains datetime values that are all in "local" server time.
SELECT TOP 5 ...
- 253k
54
votes
2
answers
249k
views
what is numeric(18, 0) in sql server 2008 r2
I found a table with this a column from this data type
numeric(18, 0)
what is that please? and why does 0 and 18 mean
I already check this question Difference between numeric, float and decimal ...
- 1,501
54
votes
10
answers
421k
views
How to get last 7 days data from current datetime to last 7 days in sql server
Hi I am loading table A data from sql server to mysql using pentaho
when loading data i need to get only last 7 days data from sql server A table to mysql
In sql server createddate column data type is ...
- 571