This tag is for SQL Server 2012, major build version 11.00.xxxx.

learn more… | top users | synonyms (2)

3
votes
2answers
2k views

“No row was updated error” in SQL 2012 - worked fine in SQL 2005 and 2008

I have a database that currently lives in SQL Server 2005. We are currently looking at upgrading to SQL 2012. I installed 2012 in a test environment (Windows Server 2012 virtual machine). Backed ...
0
votes
0answers
22 views

What is the appropriate value for DBCC CLEANTABLE batch size?

I have removed to NVARCHAR(MAX) columns from a table and what to calculated how exactly the table size was reduced. Usually, I am rebuilding the clustered indexes but the table has more then 1.6 ...
1
vote
0answers
39 views

AlwaysOn Availability Groups go to Resolving status

We have a two-node cluster set up running SQL Server 2012 on Windows Server 2012 R2. The base cluster consists of only those two nodes. The quorum is set up as node + disk majority, with a shared ...
0
votes
2answers
124 views

Stored proc getting ANSI_NULLS and ANSI_WARNINGS error when executed by application

I have a stored procedure that is getting the following error when executed via an application : Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the ...
1
vote
0answers
47 views

Renaming a column with sp_rename hangs SQL Server 2012

I was attempting to remove a column from a table within SQL Server 2012 for one of our developers. As a precaution, before dropping the column, we rename them to make sure that everything continues ...
0
votes
0answers
31 views

Scheduled Job Succeeds, Does Nothing; Manual Job Succeeds, Performs Requirements

I have a job in SQL Server Agent (2012 Instance) that deletes rows from a table. When I manually run the job, it succeeds without issue and in reviewing the table, does exactly what I want it to do. ...
1
vote
1answer
187 views

Error when trying to deploy SSIS package

I am trying to deploy my 2012 SSIS project to the Integration Services catalog on the SSIS server (also 2012). I am getting the error below. I checked that there is plenty of space in SSISDB and ...
-1
votes
0answers
21 views

Reverse Log shipping on Alwayson Database

Based on the Client Requirement, we have configured Logshipping on AlwaysOn Database using the ALWAYSON Listener Ip in the configuration. In case DR scenario, We would require to configure reverse ...
2
votes
1answer
281 views

Performance impact of Extended Events filter [like_i_sql_unicode_string]

I have created an extended events session in which I wanted to see all queries using nolock hint. I have decided to use it instead of sql trace in order to minimaze possible performance impact. ...
1
vote
1answer
98 views

Sql Server 2012 Change Data Capture weird behaviour

In my Sql Server 2012 I have the following table in a database CREATE TABLE [dbo].[HumanResource]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](50) NOT NULL, [IsHired] [bit] ...
1
vote
2answers
242 views

Targeting MSX with job on Multi-Server Administration

I am using Multi-Server Administration to execute jobs on multiple targets. This works well, but I am not able to enlist the master as a target. I receive the following error when attempting to enlist ...
1
vote
1answer
18 views

Remote Access to SQL Server

I have tried everything to be able to connect to my MS SQL Server 2012 remotely. I have read and setup everything like Kyralessa wrote in his answer HERE. Enable TCP/IP done Add 1433 TCP Port ...
1
vote
1answer
475 views

Backup/restore of a database that has a filestream

I have a SQL DB to which I have recently added Filestream data. Now we have a daily backup/restore to a QA server, and since I added the Filestream data, this has started failing. So I modified the ...
11
votes
1answer
1k views

SQL Server 2012 Standard server with 50 instances won't uninstall instances

I suspect this is an issue because we hit the 50 instance max limit of SQL Server. At the limit it won't install more instances, naturally. However, it seems it won't UNinstall them either. That's ...
8
votes
2answers
222 views

Are “out of the row” fields read when a clustered index is used?

I know that when VARCHAR(MAX)/NVARCHAR(MAX) columns are used the data is stored out of the row - the data row will have a pointer to another location where the 'large value' is stored. I have the ...
9
votes
2answers
161 views

Understanding statistics, execution plans, and 'ascending key problem'

I'm trying to better understand (conceptually) the relationship between statistics, execution plans, stored procedure execution. Am I correct in saying that statistics are only used when creating the ...
4
votes
2answers
1k views

Join on dm_db_index_physical_stats very slow

I'm running a small query below in a MS Dynamics AX 2012 database and it runs more than 5 minutes which I had to cancel and it is showing PAGEIOLATCH_SH wait type. The database data file is 560GB and ...
2
votes
1answer
690 views

Inline edit SQL Server database rows from Visual Studio

I'm pretty sure Microsoft have pulled one of the most useful features for performing quick edits on a SQL Server Database within the Visual Studio IDE. It seems to have only affected SQL 2012 ...
1
vote
1answer
539 views

Removing Secondary Replica from SQL Availability Group Causs Large Log File

Due to an unforeseen hardware error, I had to take our secondary replicas down for maintenance. This was really due to a storage issue and, it was decided at a higher level, to purchase a new storage ...
2
votes
1answer
84 views

Why can't trigger be dropped from database?

One of the databases I manage is provided by my company's vendor which we restore in order to pull data out for our data warehouse. I'm writing a stored procedure to go through the database users and ...
2
votes
2answers
118 views

Multiple named Instances with Always On

We have a two-node multi-subnet Windows Server 2012 R2 cluster in two regions. We have three named SQL Server 2012 standalone instances on each node and are in the process of setting up Always On ...
1
vote
3answers
78 views

Building year to date aggregations using SQL

I need some help with building a monthly aggregation using only SQL. Imagine the following table: TranID DateCode Account Value 1 20140101 1 5 2 20140106 1 -3 3 ...
1
vote
2answers
3k views

Create database fails with Microsoft SQL Server Error 5133, Operating syste, error 5 (Access is denied) [closed]

I just installed SQL server 2012 on Windows 2012 today. After the install, I changed the database settings to create database files in f:\mssql instead of the default. i verified the confired and ...
3
votes
2answers
3k views

Greyed out menu options, broken activity monitor

I recently installed SP1 on my 2012 SQL Server. Ever since then SSMS has behaved a little weirdly when I connect from my pc rather than from the local server itself. I no longer see the green ...
3
votes
0answers
96 views

What is the cause of this problem with CONVERT()?

Consider the following two statements: PRINT CONVERT(NUMERIC(38, 0), 0x0100000001, 0); PRINT CONVERT(NUMERIC(38, 0), 0x0100010001, 0); Both statements return -1; isn't that incorrect since the ...
3
votes
2answers
2k views

SQL Server Concise List Of Trace Flags

I did my 5 minute research and have not come up with a concise list, or even a list really except for Trace Flags (2012) which is a bit lacking. Looking at this query which uses 8691, I was wondering ...
2
votes
1answer
714 views

Troubleshooting Mirroring Configuration

I am configuring database mirroring on SQL Server 2012. After configuration I am getting the following error when attempting to start the mirroring session: tcp server cannot be reached or does ...
2
votes
2answers
209 views

Listener pointing to incorrect AlwaysOn Availability Group

All, Thanks in advance I have two instances of SQL Server 2012 SP1 on a three node WSFC with a HAdr group on each instance with a corresponding listener. Instance 1's listener points to Instance 1, ...
0
votes
1answer
332 views

How i can create backup script from diagram in SQL Server?

I use SQL Server 2012 and SQL Server 2008 R2. I create a script from all object (tables / trigger / stored procedure / function ...) in my database. I generated this script from SQL Server ...
1
vote
0answers
27 views

Can I revert to local logging in IIS 7.5 if ODBC fails? [closed]

Environment I have 3 Servers running instances of IIS 7.5 hosting different websites. 1 Server is on Domain A, 2 Servers are on Domain B. I have a one way trust setup from Domain A to Domain B. SQL ...
0
votes
0answers
30 views

My database engine cannot see one of the drives

I installed SQL Server 2012 on a VM that has two harddrives, C and E. I can log into the Database Engine, but when I attempt to attach a database that is on E, Drive E is not available. Only Drive C ...
1
vote
0answers
28 views

How to modify an existing AlwaysOn Availability Group IP

I have an unusual question regarding AlwaysOn Availability Group configuration. I have built out an AOAG cluster on a VM. The build out did not use a Heartbeat network as I had originally requested ...
1
vote
1answer
34 views

Pivoting with NULL Dates

I'm attempting the pivot below: SELECT EmpID as ID, [1] as January, [2] as February, [3] as March, [4] as April, [5] as May, [6] as June, [7] ...
4
votes
5answers
3k views

Is it advantageous to scale out using multiple SQL Server instances on the same server?

For a use-case of 9 databases spread across 3 time zones, is it advantagous to spread the databases onto multiple SQL Server instances on the same server? What will I gain and what will I lose? ...
2
votes
1answer
950 views

Connection to local SQL Server 2012 can be established from SSMS 2008 but not from SSMS 2012

I have two local SQL Server instances running on my local machine. The first is SQL Server 2008 R2 Enterprise Edition (named MSSQLSERVER) and the 2nd is SQL Server 2012 Business Intelligence Edition. ...
8
votes
2answers
237 views

Improving the performance of STIntersects

Table T_PIN has 300,000 pins and T_POLYGON has 36,000 polygons. T_PIN has this index: CREATE SPATIAL INDEX [T_PIN_COORD] ON [dbo].[T_PIN] ( [Coord] )USING GEOGRAPHY_GRID WITH (GRIDS =(LEVEL_1 = ...
5
votes
2answers
444 views

SQL Server Full Text Search - .rtf files incorrectly indexed by rtf tags

I've set up a full-text index on my SQL Server DB on a varbinary(max) column. I have a type column specified, which contains the extension of the file, such a ".doc", ".pdf" etc. However, I have ...
0
votes
1answer
30 views

delete WSFC 2012 cluster role/group fails

PS C:\Windows\system32> get-cluster Name ---- sql2012poc_cl1 PS C:\Windows\system32> get-clustergroup Name OwnerNode State ...
3
votes
1answer
553 views

Installing SQL Server 2012 - Wait on the Database Engine recovery handle failed

I am installing SQL Server 2012 SP1 on a new Windows 8.1 machine. During installation I get this error: (Screenshots similar to this question) TITLE: Microsoft SQL Server 2012 Service Pack 1 ...
1
vote
1answer
36 views

Shrinking old partitions

I have a large partitioned time series table where each partition is stored in it's own file. Since it's time series based partitioning divided by week and I'm only storing data that is new I have ...
0
votes
0answers
15 views

Efficient settings for linked server to SSAS database

We are using OPENQUERY scripts to run MDX from sql-server. We're experiencing efficiency issues when we first run these queries each morning. The linked server objects which link to our SSAS ...
1
vote
3answers
42 views

Can I install SQL server 2012 on a same machine with SQL server 2005?

I have been using SQL Server 2005 standard for quite a while, and it is working great. Now I need to use SQL Server 2012. Will this effect my existing databases? How much risk is involved in ...
8
votes
2answers
24k views

SQL server databases stuck in restoring state

I have a Sharepoint server. I guess our network administrator has miss configured his back up tool (I guess he uses Symantec backup exec) and now some of my databases are stuck in restoring state! ...
0
votes
1answer
35 views

SQL Server Chain Permission

We have the 2 databases. Reporting HR There are some users who want to extract data from HR database. But we don't want to give direct access to that HR database. So, we created the special ...
4
votes
1answer
373 views

SQL Server 2012 Standard using too much memory

For the past few months I have had a SQL Server 2012 Standard edition installation using up all the RAM in the server, up to 190GB (I'm not kidding). About every 3 to 4 days I end up having to restart ...
9
votes
3answers
6k views

Can I connect to a SQL Server 2000 instance from SQL Server Management Studio 2012?

I would like to update my development machine to SQL Server 2012 but I still manage some (very old) SQL Server 2000 machines. Wil my 2012 SSMS be able to connect to those SQL Server 2000 machines? ...
1
vote
2answers
230 views

Can't create table in database with db_owner role (SQL Server 2012)

I have database in SQL Server 2012 SP2 Enterprise. I created new SQL Login and mapped to X database with db_owner role. Now I can't create table on this database. When I run simple create table script ...
3
votes
0answers
36 views

SQL Server Management Studio 2012 separated toolbars

In SQL Server Management Studio 2012, is there a way to get the toolbars separated, so there is space between them? I can do this with 2008 R2 by just dragging the toolbar handle. But in 2012, I can ...
1
vote
0answers
29 views

“Skipping the default startup of database” SQL notification for AlwaysOn Cluster

So I set up our SQL servers to notify us of alerts at severity 17 or higher (as advised by various SQL Server articles). I've done this to each node of a SQL Server Always On Availability Cluster, ...
1
vote
0answers
627 views

SQL Server logs error

There are two SQL Server 2012 instances on the server. Named instance gives following error when Management > SQL Server Logs is clicked. Default instance works OK and opens Log file viewer. ...