SQL Server 2012 (major build version 11.00.xxxx). Please also tag sql-server.

learn more… | top users | synonyms (3)

4
votes
2answers
122 views

Will the Query Optimizer Ignore A Fragmented Index?

Scenario: I have a heavy OLTP table with an index. I see many inserts, updates and deletes and the index fragments heavily within a day or less. While on day one of the index build, the optimizer ...
1
vote
3answers
162 views

Improve a query using cursor to SET Based approach

I am still new to Query optimisation, and I have a Stored Procedure which uses a Cursor to go through each row within the Table, and performs the following operations: Calculate the Time Difference ...
0
votes
1answer
64 views

How to write function in sql which accept table as input and return result back as table?

I want to pass a table variable to a fuction (which would contains different value at different time) as input, returning a calculated result in the form of another table, which would be declared in ...
-1
votes
0answers
32 views

Get the sum of Total Churn_MTD(Current+2015+<2015) [duplicate]

how to declare 8 variables and get the total Sum,as you can see from the picture i created the 3 store procedures but dont know how to do the ...
7
votes
0answers
166 views

SQL Server 2012 Compatibility with New TLS 1.2 Standards [duplicate]

I'm trying to switch off TLS 1.0 on my SQL Server 2012 server in order to comply with PCI standards. Initially I had some trouble with the SQL Server service not starting. I found help for this ...
4
votes
1answer
86 views

ORDER BY and comparison of mixed strings of letters and numbers

We need to do some reporting on values that are usually mixed strings of numbers and letters that need to be sorted 'naturally'. Things like, e.g. "P7B18" or "P12B3". @The strings will mostly be ...
2
votes
2answers
38 views

How do I create a SQL Agent Alert on a custom error message in SQL Server 2012?

I want operators to be notified when I throw a custom error message. My understanding is that I need to add a message to sys.messages, then I can either RAISERROR or THROW that error ID. Creating an ...
2
votes
1answer
52 views

Is there a query to see which stored procedures are using a specific symmetric key?

Is there a query to see which stored procedures are using a specific symmetric key? Or a built in feature of SQL to see this information. The stored procedures are using: OPEN SYMMETRIC KEY !@#$%^ ...
0
votes
0answers
12 views

bcp copy from snapshot failed due to snap being in transition

We have a SQL job that runs each night to perform a data extract and import function. The job performs against a snapshot and copies from several tables in a serial loop. Today the job reported the ...
6
votes
1answer
5k views

Error 18456 error severity 14 state 5 SQL Server 2012 SCCM2012 SP1

We had a primary site for SCCM 2012 SP1 running fine. I then installed CAS for SCCM on another server. The prerequisites required me to change the collation on the existing server. I did this and ran ...
1
vote
1answer
2k views

SSIS package hits “login failed for user” when simultaneous operations occur against the same target database

Quick summary We have been working on migrating our library of SSIS packages from SQL Server 2008 hosted on physical Windows Server 2003 to SQL Server 2012 hosted on virtual Windows Server 2012. The ...
1
vote
0answers
22 views

Change default value for Job Activity Monitor

Is there a way for me to programmatically (some configuration setting in a table / registry entry etc, I'm hoping) change the default value in the Auto Refresh setting to 5 seconds instead of 60, and ...
2
votes
2answers
39 views

Select ID from costumers that do not have contacts last 7 days and 2 times in the month

I have 2 tables: custumer, c_contact. c_contact is all the e-mails I send to my custumers. From now on I need to put a new rule that the costumer can't receive a new e-mail if: 1) It received an ...
3
votes
1answer
60 views

Is replication a suitable method to move a SQL Server database to new hardware?

I need to move a live database with about 100 GB of data to a new machine. As I understand the easiest way to do that would be to take it offline, move the files and bring it back online. But: The ...
1
vote
1answer
114 views

Conversion fails when converting date average to date in sql

I have take date average from date and trying to get result of average date but it raising error like: Conversion failed when converting date and/or time from character string. sql query executed as: ...
8
votes
2answers
6k views

SQL Server auto-trim of varchar value in equal comparison but not like comparison

I came across some interesting behaviour on SQL Server (observed in 2005 and 2012) today that I was hoping someone could explain. A query doing a comparison using = on an NVARCHAR field ignored the ...
0
votes
1answer
10 views

MS SQL Server 2012 Data Quality Services (DQS) installation failure

I tried to install MS SQL Server 2012 DQS using DQSInstaller.exe, but installation failed. In the console output started by the install tool (DQSInstaller.exe) I found this line: Password validation ...
0
votes
1answer
37 views

Unable to connect SQL Server 2012

I am facing an issue in connecting to SQL Server 2012 with a .net application. SQL Server 2012 database and code are deployed on the same server. Connection string: <add key="umbracoDbDSN" ...
-2
votes
0answers
42 views

Data file NDF Vs MDF and performance overhead

We have database with multiple data files (*.ndf) and one of the data files we made to have static data tables i.e. not a transnational data (LibraryPrimary.ndf). Here is the load test result with ...
5
votes
0answers
50 views

SQL Server Agent initiating self-termination

From past 1 month one of my server is facing issues with SQL Server Agent. It stops automatically with below error and happens randomly. Sql Agent Output file [097] Memory leak detected [204 ...
4
votes
1answer
255 views

CPU Schedulers Offline

I logged onto a new clients systems and ran sp_blitz to see what's shaking. It reports back that "CPU Schedulers Offline" which is a new one for me. Some CPU cores are not accessible to SQL ...
2
votes
2answers
61 views

Patching strategy when Windows and SQL Server patches not applied for over 3 years

The company I'm at hasn't patched their production servers for a while, neither SQL 2012 or Windows 2012. When I say "for a while" I mean, well, actually "never" - no patches or updates since the ...
-1
votes
1answer
34 views

AlwaysOn Multi-Subnet Setup

Without using SAN-to-SAN replication, how does data synchronization occur across subnets in a multi-subnet AlwaysOn (AVG) configuration? I want to setup a separate VLAN (replica lan) to synchronize ...
1
vote
0answers
33 views

Backup creating fork_point

I have a powershell script that is restoring from production into a test availability group database. It copies the full and t-log backups from production to test, takes the target db out of the ...
1
vote
1answer
143 views

SQL Server 2012 enlarge database space

I tried to enlarge a database size of database instance on my SQL Server 2012. I manually added a new .MDF file under database instance After restart on shrink database dialog I can see available ...
6
votes
1answer
571 views

Why does database recovery take so long?

On my SQL Server 2012 x64 Std instance I had a large transaction fail due to the transaction log filling up yesterday, it couldn't be rolled back and SQL Server has restarted the specific DB to ...
1
vote
1answer
58 views

Why SQL Server Express 2012 take automatically backup in “Virtual Device” without virtual device configuration?

I have noticed that after migration of SQL Server 2012 Enterprise to SQL Server 2012 Express, every day it shows "spurious" virtual backup after taking manual backup of database. When I execute this ...
3
votes
3answers
125 views

Is Policy-Based Management dead? Is it scalable?

Policy-Based Management was introduced in SQL Server 2008. I'm looking for software/tool I can use to enforce configuration standards across a SQL Server 2012 /2014 database environment. Instances ...
0
votes
1answer
38 views

SSDT Not Dropping all Foreign Keys before Drop Table

SSDT V11.1.50730.0 Using Schema compare to make changes from my development environment to a test environment (a copy of Production). Part of the changes is the addition of columns to an existing ...
2
votes
1answer
38 views

SQLCLR assembly throws error when multiple queries run simultaneously

I have been using SimMetrics to implement the QGramsDistance search in SQL Server. The SimMetrics is used as a SQL CLR assembly. The search results are promising and everything was well, until of ...
2
votes
1answer
556 views

Report Builder cannot publish to report server

This is currently in a cloud lab environment. We're not allowed to join any AD domains. We have a linux server (RHEL 6) running MySQL 5.3 and a Windows SQL Server 2012 on Win 2k8 R2. Our laptop is ...
3
votes
0answers
35 views

High paging and memory consumption in SQL Server [duplicate]

This is my first question about SQL Server, I'm new to SQL Server and I don't understand few things, so hope you can help me. We got a SQL Server 2008 with 2 instances and also with Analysys Services ...
2
votes
2answers
908 views

Can I use a script to restore multiple SQL databases from .bak files?

I'm migrating 100 plus SQL databases over to a new server in a new location. I have the maintenance plan in place to take full back ups and then a script to copy over the .bak files to a temp ...
-1
votes
2answers
37 views

because of Date format im not able to get few records [closed]

For example declare @string varchar(30) = 'dec. 24, 2015' select substring(@string, 10, 4) declare @output date select @output = case when substring(@string, 1, 3) = 'Dec' ...
6
votes
2answers
81 views

list of batch aborting errors in SQL server

In SQL Server, if XACT_ABORT is off then some errors will terminate the current statement (for example supplying the incorrect number of parameters to a stored procedure that takes some parameters) ...
0
votes
1answer
121 views

SQL Server Express 2012 Repair

I have a copy of SQL server Express 2012 database which may be corrupt. When I tried to run a repair on it I was asked for the original installation media. The Options were for an MSI or ISO image. ...
3
votes
1answer
3k 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
1answer
254 views

Error message - view server state permission was denied - when connecing to SQL Server 2014 using SQL Server 2012 Management Studio

When right-clicking on a table and selecting "select top 1000 rows", I get this error: Is there any workaround, other than the obvious upgrade to SQL Server 2014?
3
votes
2answers
709 views

Using sp_BlitzIndex - Diagnosed as “Index Hoarder: Addicted to nulls”

Context: MS SQL Server 2012 In using Kendra Little's awesome tool, sp_BlitzIndex, a number of tables were diagnosed as "Index Hoarder: Addicted to nulls". I'm not looking for a debate over NULLs and ...
6
votes
1answer
100 views

How to better use CLR Function from performance point of view (repeat inside each DB or have general function)?

I asked a question about validation of XML using XSD schema inside SQL Server 2012 (see link). I understand (as I suspected) that I need to use CLR Function. The function will get XSD schema text and ...
0
votes
0answers
11 views

Optimizing the UPDATE For MSdistribution_history

One of the major headaches in my replication is the following MS procedure (over 55% usage), updating the distribution history. It currently has one index on columns agent_id, timestamp, runstatus, ...
22
votes
4answers
1k views

Copying (hundreds of) tables from one server to another (with SSMS)

I have several hundred (currently 466, but ever growing) tables I have to copy from one server to another. I have never had to do this before, so I'm not sure at all on how to approach it. All the ...
7
votes
3answers
18k views

Can SQL Server Management Studio Express be installed standalone?

I was wondering if the SQL Server Management Studio Express (mainly for Microsoft SQL Server) be installed on a standalone machine (without any of the SQL Services and stuff) to connect to a remote ...
4
votes
3answers
20k views

Management Studio System.OutOfMemoryException

I'm using Microsoft SQL Server 2012 and trying to run a simple query against it within Management Studio. I'm getting the following error: An error occurred while executing batch. Error message ...
8
votes
1answer
459 views

Are the cost percentages in this SQL Server plan over 100% for a valid reason?

I'm looking through the plan cache, looking for low-hanging optimization fruit and came across this snippet: Why are many of the costs listed above 100% ? Shouldn't that be impossible?
7
votes
1answer
2k views

How does SQL Server generate a query execution plan that adds up to 6,000%? [duplicate]

Today I was on The Heap and was looking at a query plan for something I thought could be improved. However, it created something that shook my belief in the SQL Server query optimizer. Can I still ...
0
votes
3answers
12k views

Cant see SQL Server Agent in SSMS [duplicate]

I have SSMS 2012 installed on my laptop, and SQL Server 2012 on my hosting, but when i try to schedule a sql job i didn't find "SQL Server Agent" in the left side list!!?
2
votes
2answers
2k views

Error creating database in SQL Server Management Studio. (Could not load file or assembly 'Microsoft.SqlServer.Sqm)

I'm trying to create a new database in SSMS, but I get this error: Could not load file or assembly 'Microsoft.SqlServer.Sqm, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or ...
3
votes
1answer
68 views

Kick a single user out of the database

I've seen multiple answers about kicking all of the other users but is there a way to stop the process id and kick just one user out of the database. Eg. user A B and C are connected to the database ...
2
votes
1answer
153 views

SQL Server 2012 - Dropping Snapshots takes hours

We recently did a SQL Server upgrade from 2005 to 2012. We have a nightly job that drops a snapshot of an active database. The snapshots gets created again later in the day. Before upgrading, in SQL ...