All Questions
Tagged with sql sql-server-2012
8,161
questions
247
votes
18
answers
387k
views
Recover unsaved SQL query scripts
How to recover the unsaved scripts if the SSMS crashes / unsaved tab gets accidentally closed?
- 10.6k
153
votes
6
answers
93k
views
Identity increment is jumping in SQL Server database
In one of my tables Fee in column "ReceiptNo" in SQL Server 2012 database identity increment suddenly started jumping to 100s instead of 1 depending on the following two things.
if it is 1205446 it ...
- 3,773
131
votes
3
answers
115k
views
SQL Server 2012 column identity increment jumping from 6 to 1000+ on 7th entry [duplicate]
I have a strange scenario in which the auto identity int column in my SQL Server 2012 database is not incrementing properly.
Say I have a table which uses an int auto identity as a primary key it is ...
- 3,433
129
votes
4
answers
303k
views
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions
The ORDER BY clause is invalid in views, inline functions, derived
tables, subqueries, and common table expressions, unless TOP, OFFSET
or FOR XML is also specified.
I am getting the above said ...
- 12.1k
124
votes
18
answers
336k
views
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0
I have an Insert stored procedure which will feed data to Table1 and get the Column1 value from Table1 and call the second stored procedure which will feed the Table2.
But when I call The second ...
- 28.1k
120
votes
13
answers
162k
views
Cannot execute script: Insufficient memory to continue the execution of the program
I have a 123MB sql file which I need to execute in my local PC. But I am getting
Cannot execute script: Insufficient memory to continue the execution of the program
How to solve this issue?
110
votes
8
answers
63k
views
Is there a way to persist a variable across a go?
Is there a way to persist a variable across a go?
Declare @bob as varchar(50);
Set @bob = 'SweetDB';
GO
USE @bob --- see note below
GO
INSERT INTO @bob.[dbo].[ProjectVersion] ([DB_Name], [Script]) ...
- 5,091
101
votes
6
answers
72k
views
Sequence vs identity
SQL Server 2012 introduced Sequence as a new feature, same as in Oracle and Postgres. Where sequences are preferred over identities? And why do we need sequences?
- 23.1k
90
votes
6
answers
603k
views
SQL Server IF EXISTS THEN 1 ELSE 2
Using Sql Server 2012. I have a stored procedure and part of it checks if a username is in a table. If it is, return a 1, if not, return a 2. This is my code:
IF EXISTS (SELECT * FROM ...
- 2,567
90
votes
4
answers
335k
views
How to subtract 30 days from the current date using SQL Server
I am unable subtract 30 days from the current date and I am a newbie to SQL Server.
This is the data in my column
date
------------------------------
Fri, 14 Nov 2014 23:03:35 GMT
Mon, 03 Nov 2014 ...
- 667
73
votes
4
answers
202k
views
How can I tell if a VARCHAR variable contains a substring?
I thought it was CONTAINS, but that's not working for me.
I'm looking to do this:
IF CONTAINS(@stringVar, 'thisstring')
...
I have to run one select or another, depending on whether that variable ...
- 13.6k
72
votes
19
answers
136k
views
New Line Issue when copying data from SQL Server 2012 to Excel
I recently upgraded to SQL2012 and am using Management Studio. One of my columns in the database has a CHAR(13) + CHAR(10) stored in it.
When I was using SQL Server 2008, this would copy and paste ...
- 1,353
66
votes
5
answers
78k
views
What is (are) difference between NOLOCK and UNCOMMITTED
I use SQL Server 2012.
I write two queries but what is a different between NOLOCK and UnCommitted ?
SELECT lastname, firstname
FROM HR.Employees with (READUNCOMMITTED)
SELECT lastname, firstname
...
- 12.2k
66
votes
6
answers
127k
views
What is the difference between a primary key and a surrogate key?
I googled a lot, but I did not find the exact straight forward answer with an example.
Any example for this would be more helpful.
- 781
64
votes
12
answers
1.0m
views
Convert Date format into DD/MMM/YYYY format in SQL Server
I have a query in SQL, I have to get a date in a format of dd/mm/yy
Example: 25/jun/2013.
How can I convert it for SQL server?
- 1,044
64
votes
2
answers
142k
views
Possible to restore a backup of SQL Server 2014 on SQL Server 2012?
I know that you can't (at least not easily) restore a SQL Server 2012 backup on SQL Server 2008. But how does it work for SQL Server 2014 to SQL Server 2012 ?
On database level there is the property ...
- 4,154
62
votes
4
answers
65k
views
Scope of temporary tables in SQL Server
I wrote a stored procedure to import and transform data from one database to another. Each import would take a single company ID and import all data related to this company.
To help with the ...
- 2,603
59
votes
3
answers
64k
views
Identity column value suddenly jumps to 1001 in sql server [duplicate]
I am using Sql server 2012(Denali). I wonder why all identity column values start from 1001 and so on. At the beginning Identity column starts from 1,2 and so on and adding identity smoothly, but ...
- 7,747
56
votes
2
answers
198k
views
Cannot Resolve Collation Conflict
I have moved one of our databases (DB1) from SQL Server 2008 to 2012 and when I run the stored procedures I get the following error
Cannot resolve the collation conflict between "...
- 2,975
56
votes
3
answers
66k
views
Count Number of Consecutive Occurrence of values in Table
I have below table
create table #t (Id int, Name char)
insert into #t values
(1, 'A'),
(2, 'A'),
(3, 'B'),
(4, 'B'),
(5, 'B'),
(6, 'B'),
(7, 'C'),
(8, 'B'),
(9, 'B')
I want to count consecutive ...
- 6,497
52
votes
3
answers
48k
views
You can only create a user with a password in a contained database
I am using a contained database, after creating the database I attempted to create a user but I got the error:
You can only create a user with a password in a contained database
My code is:
...
- 2,636
51
votes
1
answer
27k
views
Can't save database default locations in SQL Server
I'm trying to change the default directories for data and logs using SQL Server Management Studio. I change the path then click ok but it always reverts back to the old directory in Program Files. ...
- 625
51
votes
2
answers
330k
views
Sql server - log is full due to ACTIVE_TRANSACTION [duplicate]
I have a very large database (50+ GB). In order to free space in my hard drive, I tried deleting old records from one of the tables . I ran the command:
delete from Table1 where TheDate<'2004-01-...
- 11.1k
50
votes
6
answers
180k
views
Get previous and next row from rows selected with (WHERE) conditions
For example I have this statement:
my name is Joseph and my father's name is Brian
This statement is split by word, like this table:
ID
word
1
my
2
name
3
is
4
Joseph
5
and
6
my
7
father's
8
...
- 847
50
votes
2
answers
55k
views
SQL Server 2012 installation Reporting Services Catalog error
I'm installing SQL Server 2012 at the moment and when I was about to run the installation, this error pops up:
On clicking the first failed test, which is "Reporting Services Catalog Database File ...
- 733
48
votes
2
answers
96k
views
Export from SQL Server 2012 to .CSV through Management Studio
I have a SQL Server 2012 database that I am trying to export to .CSV. My knowledge of SQL is very basic and my question is, I'm assuming it would be done in Management Studio, how do I do this?
- 1,010
48
votes
4
answers
149k
views
SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures'
SQL Server blocked access to procedure sys.sp_OACreate of component
'Ole Automation Procedures' because this component is turned off as
part of the security configuration for this server. A system
...
- 548
47
votes
5
answers
76k
views
Subquery v/s inner join in sql server
I have following queries
First one using inner join
SELECT item_ID,item_Code,item_Name
FROM [Pharmacy].[tblitemHdr] I
INNER JOIN EMR.tblFavourites F ON I.item_ID=F.itemID
WHERE F.doctorID = @...
- 11.6k
42
votes
2
answers
82k
views
what is logical reads in sql server? how to reduce no of logical?
After doing my research, on how to speed up queries executed in SQL server, the majority of resources suggesting to reduce logical reads, by using the proper where clause.
What I am really interested ...
- 685
42
votes
3
answers
68k
views
Stored procedure EXEC vs sp_executesql difference?
I've written two stored procedure one with sp_executesql and other doesn't have sp_executesql
both are executing properly same results, I didn't get what is the difference here between
EXEC (@SQL) ...
- 1,564
41
votes
6
answers
8k
views
Calculation in Sql Server
I trying to perform following calculation
Sample data:
CREATE TABLE #Table1
(
rno int identity(1,1),
ccp varchar(50),
[col1] INT,
[col2] INT,
[col3] INT,
col4 as ...
- 92.9k
40
votes
12
answers
68k
views
Cannot start LocalDB
Cannot start LocalDB instance, I have installed and re-installed over and over.
Keep getting this error :
Start of LocalDB instance "v11.0" failed because of the following error:
Error ...
- 8,022
39
votes
2
answers
98k
views
There are no Primary or Candidate Keys in the referenced table
Error: There are no Primary or Candidate Keys in the referenced table
'dbo.Customers' that match the referencing column list in the foreign
key 'FK_Reservation_Customers_FrstNme FOREIGN KEY'
DROP ...
- 569
37
votes
3
answers
22k
views
I need a check constraint on two columns, at least one must be not null
I have a table in SQL Server with two numeric columns. At least one of these numeric fields must be filled. How do I write a check constraint to verify this?
- 6,925
37
votes
4
answers
107k
views
Do I need to specify ON DELETE NO ACTION on my Foreign Key?
I have the following DDL that I am using with SQL Server 2012:
CREATE TABLE Subject (
[SubjectId] INT IDENTITY (1, 1) NOT NULL,
[Name] NVARCHAR (50) Not NULL,
CONSTRAINT [PK_Subject] PRIMARY ...
user1943020
37
votes
1
answer
30k
views
Getting offset of datetimeoffset in SQL Server
In SQL Server I need to find the offset of a given datetimeoffset(7).
I have researched the documentation and there are all ways to change the offset but not a way to know the offset of a particular ...
- 495
35
votes
3
answers
56k
views
CASE Statement for Order By Clause with Multiple Columns and Desc/Asc Sort
Following on from my earlier question here Case statement for Order By clause with Desc/Asc sort I have a statement like this:
SELECT
*
FROM
TableName
WHERE
ORDER BY
CASE @OrderByColumn ...
- 7,221
34
votes
8
answers
35k
views
Try_Convert for SQL Server 2008 R2
I'm using SQL Server 2008 R2 and have a VARCHAR column I want to convert to DECIMAL(28,10) using CONVERT. But many of those rows are badly formatted, so it is not possible to parse them to a number. ...
- 5,136
34
votes
3
answers
34k
views
Can SQL Server Express LocalDB be connected to remotely?
I am looking into using the new SQL Server Express LocalDB (I think it is code named "Denali") for a desktop application.
It is currently running with SQL Compact, but the user is wanting to share ...
- 6,738
32
votes
7
answers
131k
views
Joining two tables with specific columns
I am new to SQL, I know this is really basic but I really do not know how to do it!
I am joining two tables, each tables lets say has 5 columns, joining them will give me 10 columns in total which I ...
- 657
32
votes
4
answers
46k
views
What are the main differences between OPTION(OPTIMIZE FOR UNKNOWN) and OPTION(RECOMPILE)?
I run into the classic Parameter Sniffing issues in SQL Server 2012. Based on some research I found multiple options around this problem. The two options that I need to understand the difference ...
- 11.8k
31
votes
4
answers
60k
views
ANSI_NULLS and QUOTED_IDENTIFIER killed things. What are they for?
NOTE: I checked Understanding QUOTED_IDENTIFIER and it does not answer my question.
I got my DBAs to run an index I made on my Prod servers (they looked it over and approved it).
It sped up my ...
- 80.3k
31
votes
3
answers
117k
views
Correct way to select from two tables in SQL Server with no common field to join on
Back in the old days, I used to write select statements like this:
SELECT
table1.columnA, table2.columnA
FROM
table1, table2
WHERE
table1.columnA = 'Some value'
However I was told that having ...
- 7,221
30
votes
11
answers
114k
views
STRING_SPLIT in SQL Server 2012
I have this parameter
@ID varchar = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20'
I want to do something to split the comma-separated values.
The string_split function doesn't work and I ...
- 313
30
votes
10
answers
129k
views
How to restore a SQL Server 2012 database to SQL Server 2008 R2?
I am trying to restore the backup taken from a SQL Server 2012 to SQL Server 2008 R2, and it giving an error
Specified cast is not valid. (SqlManagerUI)
If you have any solution to this please ...
- 309
30
votes
5
answers
61k
views
Replace first occurrence of substring in a string in SQL
I have to fetch data from a @temp table which has something like "or ccc or bbb or aaa" I want to replace the first occurrence into space to get something like this " ccc or bbb or aaa". I am trying ...
- 413
30
votes
5
answers
107k
views
How do I import a .bak file into Microsoft SQL Server 2012?
Been Googling this for awhile and no answer....can anyone help?
- 11.2k
30
votes
2
answers
61k
views
SQL Update - Multiple Columns
I would like to update multiple columns in a table based on values from a second table using a Select statement to obtain the values like this:
UPDATE tbl1
SET (col1, col2, col3) = (SELECT colA, ...
- 3,086
29
votes
13
answers
61k
views
SQL Server Management Studio 2012 hangs
When I click on the "Databases" node in "Object Explorer" it just keeps on "Loading items" until at some point it just hangs.
This happens only when connecting to a remote server, not when accessing ...
- 27.2k
29
votes
1
answer
30k
views
Special characters displaying incorrectly after BULK INSERT
I'm using BULK INSERT to import a CSV file. One of the columns in the CSV file contains some values that contain fractions (e.g. 1m½f).
I don't need to do any mathematical operations on the ...
- 315