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

learn more… | top users | synonyms (2)

2
votes
1answer
132 views

Failover Cluster Diagnostic Logs

I know there must be a simple answer to this question, but I cannot find it anywhere. Our SQL 2012 FCI have SQLDIAG .XEL files in their MSSQL\LOG folder. In some instances, these files are often ...
1
vote
1answer
135 views

Linked Server and MSDTC on cluster

I'm trying to set up a linked server between two instances we have on our network. I've done this many times before without an issue, but this time I repeatedly get the following error: OLE DB ...
0
votes
0answers
50 views

Is it best practice to include partition keys within foreign keys when referencing partitioned tables?

The large tables in our database are partitioned by an id in order to separate data and speed up IO. The table of the data is partitioned by the partition key, but the primary key is only on an ...
1
vote
0answers
30 views

Does the order of columns in a non-clustered ColumnStore index matter? [duplicate]

I'm reading about creating a non-clustered ColumnStore index. I noticed that when I modify an already created non-clustered ColumnStore in SQL Server Management Studio I have an option to change the ...
0
votes
0answers
58 views

SQL SERVER 2012 Express - Can't change Thesaurus Location

I have a requirement for an application to change the location where SQL SERVER looks for the Global Thesaurus file. According to the official documentation: "You can change the location and names ...
2
votes
0answers
28 views

How to add a missing database to an AlwaysOn Availability Group [duplicate]

Setup: Microsoft SQL Server 2012, and we have 3 production servers(prod1, prod2, prod3) with AlwaysOn. We recently added a new database and the new database was added to our Availability Group(named ...
1
vote
2answers
396 views

SQL Server 2012 Can't log in with machine account

We're migrating an IIS/SQL Server site from 2008 to 2012, and I'm having a problem with authentication. We use Windows Authentication to authenticate as DOMAIN\COMPUTER$, but this isn't working, ...
0
votes
0answers
32 views

Is it possible for a SELECT-only stored procedure to deadlock on itself?

Environment is Microsoft SQL Server 2012. Is it possible for a stored procedure consisting exclusively of SELECT statements to deadlock on itself? That is the report I am getting from a vendor for ...
2
votes
1answer
28 views

Obtaining the next partition number

For a partitioned table, how do I determine via T-SQL what is the next partition number that will be used? For example, the switch-in today went into partition number 50. I want to build the new ...
4
votes
2answers
125 views

Cannot remove SQL Server 2012

I was in the process of installing SQL Server 2012 x64 Express Management Studio and encountered one heck of an issue. I was most of the way through the installation process when my computer locked ...
0
votes
1answer
232 views

Change code editor variable highlighting colour

Within the SSMS Code Editor (for SQL Server 2012) I have this code written within the editor, that has a Dark theme applied. When you click on a variable, SSMS highlights all the other occurrences of ...
1
vote
1answer
177 views

SSAS : An error occurred when instantiating a metadata object

Aside from installing SSAS, I have no experience using it. Our developers are saying that it is no longer possible to create a new aggregation, whatever the cube. The error occurs when hitting ...
0
votes
1answer
240 views

How to get SQL Server filetable path locator for subfolder by its literal path

I have a filetable which have several subtables. For example FileTable ->subtable1 ->->subtable1.1 ->->subtable1.2 ->subtable2 ->subtable3 etc How to get SQL Server ...
0
votes
1answer
1k views

Shrink Transaction Log While Using AlwaysOn Availability Group

We are using AlwaysOn Availability Group feature of SQL Server 2012. Regular full database backups and transaction log backups are done every day on the secondary database. I have read here doing the ...
0
votes
1answer
58 views

Can anyone recommend a beginner DBA course in London [closed]

My organisation is in need of a SQL Server DBA and as the companies only "SQL Guy" my boss has suggested me to take SQL Server DBA course. My general T-SQL knowledge is fairly good, I can write ...
2
votes
2answers
80 views

SQL Server: Apply Custom Server Role afterwards for already existing databases

I needed a custom SQL Server role which has readonly access to all databases, regardless of whether a database is existing or is newly created. The role is not a problem. The problem is that the ...
0
votes
1answer
41 views

Select rows other than first selected row in a table

My table looks like id ledgerID Amount Debit or credit TransactionType -------------------------------------------------------------- 1 1 500 dr ...
1
vote
1answer
326 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 ...
1
vote
1answer
40 views

PowerShell 'get-ClusterQuorum' output is not showing 'QuorumType' column

PowerShell cmdlet 'get-ClusterQuorum' output is not showing "QuorumType" column. Has anybody seen this before? Thanks
1
vote
2answers
99 views

What happens when Core Cluster Group is failed over to another node? Will it take the whole cluster down? Single point of failure?

"Cluster group" fail-over will cause cluster go offline. Really? Single point of failure? Am I missing something? What happens when Core Cluster resources (Cluster Group) are failed over from one node ...
0
votes
2answers
83 views

How secure is a linked server in SQL Server

I am in a situation in which I should doubt everyone (even my developer team and database admin) so I want to Log all the logons made to my server. I have two SQL Servers, say A and B. I want to log ...
0
votes
1answer
734 views

Script that alters all stored procedure on the database

I have a database with thousands of stored procedures and I need to alter hundreds of them. In order to do it faster I thought of a way to write a script that runs over all stored procedures and adds ...
0
votes
2answers
62 views

Order by for 2 row result causes 800% performance hit

The following query, run against a table of about 6 mil rows takes 25 seconds. When I remove the final order by, it runs in 3 seconds. There are no indexes on the table (it's an intermediate SSIS ETL ...
2
votes
0answers
87 views

Why does Recursive CTE estimate just 1 row?

Given two cascading, self-contained (no real tables) recursive CTE's: create view NumberSequence_0_100_View as with NumberSequence as ( select 0 as Number union all select Number + 1 ...
1
vote
1answer
87 views

Windows SQL Server Express 2012 and LDAP external server integration

I have a new installation of Windows server 2012 with SQL Express 2012 with the management tools. I'm planning to create there a simple synchronization that will make queries twice per day to an ...
2
votes
2answers
329 views

Should I check for existence of temp tables in Stored Procedures?

Is there any edge case in which it would be recommended to explicitly check for ,drop and create temp tables at the start of a Stored Procedure instead of just creating them? Similarly, is there a ...
3
votes
1answer
90 views

Microsoft SQL Server Connection Coloring

I've been using the Redgate Query Tab Coloring feature (under SQL Prompt's Experimental Features) and I really like it. Somehow I've never known until today that this feature is built in to SSMS for ...
2
votes
2answers
271 views

Filtered index not chosen, and rejected with hint

I'm trying to understand why a non-clustered index is not available for a given query when filtered. The relevant part of my (large) query is this: ) results JOIN BE_Insurance ins ON results.PayorId ...
1
vote
1answer
38 views

Combine 2 tables

I am wanting to combine 2 tables. until recently the column names were different , and one had fewer columns than the other, however it was deemed unnecessary to have them as two tables. I have ...
3
votes
1answer
49 views

AlwaysOn Secondary Replica DB-size

I just noticed something peculiar. We're using AlwaysOn availability in one of our environments with a read-only secondary replica. However, checking the .mdf / .ldf files for the secondary replica, ...
1
vote
1answer
57 views

add a column to an existing table

I'm wanting the best way to add a column to an existing table in my database. I'm using SQL Server 2012. I don't want the data that is in the table to be affected, I just want to add a new column ...
0
votes
1answer
55 views

change column name

I'm needing to update a few column names in some tables I have in my database. I'm using SQL Server 2012. I'm aware of using the RENAME ability: ALTER TABLE table_name RENAME COLUMN old_name to ...
0
votes
0answers
41 views

Setup for Availability Group with Analysis Services

We currently have one server with the Database Engine, SSAS, SSRS and SSIS all running on it. We are moving to a two server Availability Group setup. Currently I am not too worried about resources ...
1
vote
0answers
482 views

Is it possible to use a temp table in a data flow source?

I have a data flow in a 2012 SSIS package where I'm trying to use a temp table as the data source. I'm using an OLE DB data source with a SQL Command Data access mode. My code looks like this: ...
1
vote
3answers
177 views

check if tables has same columns

I have a Database that creates day tables and some of the tables don't have the same number of columns and in some cases the columns aren't the same. What I'm needing to do is consolidate all the ...
-1
votes
1answer
47 views

Transferring Data from SQL Server to SQL Server Express? [duplicate]

How do I transfer a database from a physical server running SQL Server 2012 to another server running SQL Server 2008 Express? The database was created by sa under SQL Server 2012. Thank you.
5
votes
3answers
211 views

Rebuilding an entire Microsoft SQL Server Database

Is there a suggested way to rebuild an entire SQL Server database from another database in SQL Server 2012 SP2? Let me explain... we have a database that we are trying to enable partial containment ...
1
vote
3answers
151 views

How to separate Stored Procedures (i.e. all the business logic) from Client Database so that it only contains client data?

Scenario: Our Application Database (in SQL Server 2012) contains entire business logic in the Stored Procedures (SPs). Everytime we have to publish the DB to the client, it unnecessarily results in ...
3
votes
1answer
363 views

Execute T-SQL Only If AlwaysOn Database Is Primary

Little background on the system. It is SQL Server 2012 AlwaysOn Availability Group with 1 primary and 1 secondary. I have been following this article (SSIS with AlwaysOn) to make SSIS work with ...
2
votes
1answer
81 views

Table design for child tables with multiple many-to-many relationships

We are working on designing tables for a new Networking application, and have been having some problems with finding the best design for the top level tables. Network is the top level table. Under ...
1
vote
1answer
88 views

Query Plan Different When Using Compiled Values

I have pulled out an execution plan from the plan cache and from the XML pulled out the compiled values. Then I have run the query in SSMS using the compiled values and the query plans are different ...
0
votes
1answer
75 views

How can I restore ##MS_SSISServerCleanupJobLogin##?

At some point the SSIS cleanup job login (##MS_SSISServerCleanupJobLogin##) got deleted from my sql server (2012). I'm trying to upgrade to SP2 and I'm getting an error message I believe is related to ...
0
votes
0answers
36 views

Inserting INTO XML Column shows nothing at all

I am trying to populate xml in table by, Declare @docy xml = N'<elements>..... 6000 character file </elements>' SET IDENTITY_INSERT TableName ON INSERT TableName(Id, ID_A, ID_B, Xml) ...
2
votes
1answer
71 views

What is FT BATCH CMPLETE in the cmd column for sysprocesses?

I was trying to figure out what was causing high cpu usage in our sql server and found two processes that were consuming high CPU. When I queried sysprocesses to see what the spid was doing, it had ...
5
votes
2answers
275 views

SQL Server: Create New GUID Values for Existing Index

I have an existing table with about 6 million rows. The relevant columns are: ID int not null PK Key uniqueidentifier not null Read/write on this table by Key lookup is probably something like 100 ...
4
votes
1answer
121 views

Incorrect result when you execute a query that uses WITH RECOMPILE option in SQL Server 2012 [closed]

I just read the Brent Ozar Weekly Links email (July 28th Edition), and noticed the "Kendra's Link" talking about "Fix: Incorrect result when you execute a query that uses WITH RECOMPILE option in SQL ...
4
votes
2answers
143 views

Sql Server Management Studio login prompt will never forget other sql user than SA

When I login to a database, using some other user than SA, that username will stick the next time I login. If I then input the username SA and password and logs in, the next time I open the login ...
2
votes
2answers
167 views

Help reading deadlock XML

Even with reading the Bart Duncan blog about reading deadlocks I still struggle with this. I have included the XML from a particular deadlock. <?xml version="1.0" encoding="utf-8" ?> - ...
1
vote
2answers
184 views

Transaction Log Shipping across multiple SQL Server versions

I'm lead to believe that TLS is only appropriate/supported across the same versions of SQL Server. What I'm trying to do is to maintain a reporting (read only) version of a production transactional ...
0
votes
2answers
239 views

SQL Server 2012 Browser Service doesn't appear [closed]

I've installed SQL Server 2012 and Management Studio Express. I attached the database that belongs my previous Project in Visual Studio. But I am having SqlException Error , I think the reason of the ...