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

learn more… | top users | synonyms (2)

3
votes
1answer
157 views

Subselect of MAX(Version) takes many minutes, though there are only ~20K records

I have a pretty complicated data model. I can't understand most SQL examples without an explanation of what's being modeled, so I'll try to explain. Mainlines -> Releases -> Overlays -> Calibrations ...
0
votes
1answer
317 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 ...
0
votes
0answers
13 views

When does a port get assigned if SQL Server is set to TCP Dynamic Ports

I'm trying to determine when a dynamic port is assigned to SQL Server. If the TCP/IP Properties have the TCP Dynamic Ports enabled, does that port get assigned at the time the Server is installed or ...
0
votes
1answer
75 views

Optimizing queries for 25+ million rows

I am using MS SQL, and I have to run several queries on the same table on different criteria. At first I ran each query on the original table although they all share some filtering (i.e Date, status). ...
-1
votes
0answers
9 views

SSIS Export Ragged Right Fixed Width format File

I created an SSIS project to export a fixed width file to an external system. This file has 12 fields and they all position correctly per the external system requirements. I use Ragged Right format ...
4
votes
1answer
239 views

Should I use Database Mirroring?

So I have the following scenario on my hands: I have a product database that runs for a specific app, all inserts into the database come in through three separate connections. Recently I have ...
2
votes
1answer
1k views

SQL Server Create Access Denied even after adding MSSQLSERVER user permissions

I am aware that SQL Server instances need write access not for my user account (which is in fact irrelevant to that service's context) but for the user account or security principal under which the ...
7
votes
1answer
111 views

High PAGELATCH_* and WRITELOG waits. Are they related?

We are seeing very high PAGELATCH_EX and PAGELATCH_SH wait types along with high WRITELOG waits. I've diagnosed the query causing the PAGELATCH waits and can eliminate them by reducing the insertion ...
4
votes
3answers
642 views

Keeping default max memory 2147483647 MB value in SQL Server, a bad practice?

Based on that msdn page : server memory settings it's a good practice to keep the default values for SQL-Servers min and max memory to keep it dynamic... From what I learned through tutorials you ...
4
votes
2answers
180 views

How to configure SSRS (SQL Server 2012) to run on a different instance, but same server as SharePoint

I have an instance of SQL Server 2012 running on Windows 2008R2 along side another instance of SQL Server 2012 that is running SharePoint (I think it is SharePoint 2010). When I try to access the ...
2
votes
3answers
56 views

Why is sql server waiting for acknowledgment?

I have an application that often request a big set of rows [about 20K] from a sql view, in the mean time other applications can't update the table from where the data comes from, the update query's ...
0
votes
0answers
24 views

Peer-to-Peer Transactional Replication Load Balancing in MS SQL Server

I'm looking at Peer-to-Peer Transactional Replication and it looks like a perfect fit for my requirements, but there's one unclear part - load balancing (see picture below). How it is implemented in ...
1
vote
1answer
28 views

Finding AD group permissions source

We have an AD group as a Login in several servers, usually as sysadmin, but I've found one server that this login has no access to any DB and has a server public role only. I've talked to a member of ...
3
votes
3answers
626 views

SQL Profiler - Insert values?

I setup an SQL profiler to run, but I am not seeing any values for updates/inserts. Is there a way to see them? All I see is stuff like this: insert into some_tbl(id, col1, col2, col3) values ( @P0 ...
-4
votes
1answer
43 views

Create database unique Surrogate Keys without using GUID’s

What is the best way to get database unique surrogate keys in Microsoft SQL Server when using something like “auto increment” as opposed to GUID's. The purpose is to increase performance over using ...
1
vote
0answers
24 views

How does SIOS Data Keeper make consistent backups? [on hold]

We are looking to change our DR solution to get our RTO and RPO as close to 0 as possible. We have around 80 databases on SQL Server 2012 sp2 that come to about 1TB in total including log files. I ...
1
vote
2answers
942 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, ...
3
votes
3answers
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
0answers
41 views

How to raise Procedure Cache Hit Ratio?

I have a SQL Server 2012 instance with 96 GB of RAM that has the Buffer Cache Hit Ratio consistently at 100% and the PLE consistently over 500,000, so memory is obviously not a problem. However, my ...
1
vote
2answers
54 views

SQL Server 2012: set a column to only use a single case?

Is there a way to tell SQL Server 2012 to store both A and a as A (or both A and a as a) for some given column such that every insert statement does not need to have UPPER() in it?
0
votes
0answers
22 views

Using loops inside Openrowset

I'm trying to execute a query across around 200 servers using openrowset function. This function needs to execute in a loop - the query is sucessfull in SQL Server 2012 environments, but failing in ...
3
votes
2answers
5k views

sp_dboption is not available in SQL Server 2012

The system stored procedure sp_dboption is not available in SQL Server 2012 anymore. How could it be replaced?
1
vote
1answer
70 views

Upgrade to SQL Server from 2008 to 2012 - Developer perspective [on hold]

We are in the process of migration from SQL Server 2008 to SQL Server 2012. The entire process of migration will be taken care by dedicated DBA team. From a developer prespective, I would like to ...
4
votes
1answer
1k views

Restore master database from SQL Server 2008 R2 on SQL Server 2012

I have a process that restores all my backups on a centralized server for testing. I have recently upgraded this server to SQL server 2012, and now I am getting an error when restoring master from SQL ...
2
votes
2answers
162 views

Detached database file permissions

I have a script that detaches databases for archival when they meet certain criteria. The script is executed by SSIS under the local Administrator account by the Ghost of DBA Past. Per design it (by ...
1
vote
1answer
57 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 ...
1
vote
1answer
70 views

How to improve query to include 2 critical pieces of employee data

I’m going to preface by saying that I’m fairly green with SQL. My code below works, however I am missing two vital pieces of my employee population: (1) those employees who have been invoiced but ...
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 ...
1
vote
1answer
47 views

Using LIKE or Full-text when searching database

I am fairly new to SQL and I am currently trying to create a search function for my website, much like the one on imdb.com. I have 3 tables and I need to search two rows (nvarchar) in each table when ...
0
votes
1answer
1k views

SSIS Package Validation Error when Executed From Windows Task Scheduler

I have deployed my SSIS package using the project deployment model to the SSIS catalog and experience a strange validation error when I try to execute the package. I believe this is due to a ...
0
votes
2answers
98 views

SQL Server 2012 Very Slow to Consume Memory

I have a bit of a conundrum. I recently upgraded a ~20gb database from Window Server 2008 R2 || SQL Server 2008 R2 to Window Server 2012 || SQL Server 2012. The previous server had 32gb of memory, ...
2
votes
1answer
316 views

How to allow connections to SQL Server with 2 differents ip

I have a small issue here: I have a SQL Server instance 2012 (let's call it INSTANCE) on a cluster. I also have 2 network interfaces for this instance: SQLINSTANCE.my.metwork ...
1
vote
0answers
17 views

SQL Server Transactional Replication

I am using SQL Server 2012 and I am investigating transactional replication and had a question on SQL Versions that can be used. The Distribution and Publisher will be on SQL2012 Standard Edition, ...
4
votes
2answers
3k views

Suspended in index rebuild

In SQL Server 2012, an Index Rebuild job is taking a very long time (up to 8 hours). However, not even one Index rebuild completed, so I stopped the index job. In monitoring SQL task: state = ...
3
votes
3answers
179 views

Replication with FileStream

I need to setup database replication (for high availability) on a database that uses FileStream. This is on SQL Server 2012 Standard Edition, so Always On or Peer-to-Peer replication is not an option. ...
0
votes
1answer
118 views

SSIS 2012 metadata not refreshing on re-deploy

I have three systems: DEV, UAT, PROD, all running SQL 2012 Enterprise. I have existing packages that hit objects on these systems. Every time I have to make a change that affects metadata, it's not ...
2
votes
2answers
32 views

How do I add keyboard shortcuts in SSMS 2012 to run a specific query?

How do I add keyboard shortcuts in SSMS 2012 to select all the data from table? In SSMS 2008 we could assign a shortcut in the following way: ...
1
vote
1answer
68 views

SQL Server - Linked Server - Using OPENROWSET with windows integrated security

I have seen many articles regarding OPENROWSET using integrated security (Windows Authentication), but I could not make it work for me. It is working fine using SQL Server authentication: select * ...
5
votes
5answers
4k views

SQL Server 2012 AlwaysOn: need to automatically add databases by script- T-sql or PowerShell

I've installed and successfully configured our SQL Server 2012 AlwaysOn 2-node servers for our new "Intranet" that is coming out. I've gotten AlwaysOn working great, and our Front End servers for the ...
0
votes
1answer
548 views

DB2 Linked Server on SQL Server 2012 - Grant Access to Users

I have a linked server on SQL Server 2012 Instance to point to a DB2 database and I have Sys Admin access to that Instance. I am unable to test connection to that DB2OLEDB database as I get below ...
0
votes
0answers
16 views

Omnis ODBC reads are slow [closed]

I have an Omnis database that needs ETL'd into a SQL Server 2012 DB. Most of the tables copy fine, but one of our tables has about 2 million rows, LOB columns, and takes forever to copy (about 15 ...
2
votes
1answer
22 views

SSIS 2012: What rights are needed to connect to SSDB via DMO

I'm trying to connect to the SSDB catalog to execute a package using the .NET object model e.g. using Microsoft.SqlServer.Management.IntegrationServices; var sqlConn = new ...
1
vote
1answer
110 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 ...
1
vote
1answer
19 views

how to output 3 column into 3 element in xml format with the same name?

I have this code and I can't figure out how to get the expected result? declare @table table (TAG int, PARENT int,col1 varchar(100) ,col2 varchar(100) ...
2
votes
1answer
44 views

Tool for quickly backup and restore a table

Is there a tool for quickly backup and restore a table's data, preferably to a csv file? It would be best if I could do it using batch. Run a .cmd file and data is backed up into a file with datetime ...
1
vote
0answers
40 views

SQL Server database corruption in multi-user mode but not single-user mode

Using Microsoft SQL Server 2012 SP2 Standard Edition on a 2 node SQL Cluster. When I run dbcc checkdb on a certain database when it is in multi-user mode, it finds corruption in the clustered index ...
4
votes
2answers
130 views

Execute SP whenever any job is finished

I have many SQL Server jobs on the server. I need to execute a SP whenever any job execution is done. One hard way is to create a step on every job which calls that SP. Is there any simple way to do ...
1
vote
1answer
74 views

Work queue with complex select and long processing: Ensuring concurrency

I have a queue table from which worker processes claim records, one at a time. It is crucial that no two workers claim same item to process. Normally this kind of thing is done with select top (1) ...
3
votes
1answer
63 views

PAGELATCH_EX on 2:1:128

We are having problems on SQL Server 2012 with contention on tempdb page 2:1:128, which seems to be related with system table sysobjvalues. We have about 400 sessions waiting on this resourse ...
0
votes
1answer
18 views

Rank function creating duplicate partitions? (two rank 1's for the same key)

The below query is outputting two rows. Given that I am partitioning on ref_number and also have ref_number in the where clause, I believe I should only ever get a single row back. WITH rank_cte AS ...