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

learn more… | top users | synonyms (2)

1
vote
1answer
612 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
73 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
31 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
90 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 ...
2
votes
2answers
1k 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
39 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
194 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
668 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
540 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
488 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
4k 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
83 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
112 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
42 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
795 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
49 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
161 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
93 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
1k 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
67 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
98 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
119 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 ...
3
votes
2answers
818 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
131 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 ...
3
votes
2answers
989 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
39 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
64 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
60 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
70 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
63 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
1k 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
365 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
73 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
226 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
177 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
812 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
116 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
94 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
130 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
40 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
105 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
369 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
141 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
326 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
299 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
466 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
557 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 ...
4
votes
1answer
152 views

Page compression not working?

I have created a table where I have around 100 partitions (per month), all of them are configured to use page compression. After that I'm inserting data to this table month by month. I now would ...