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

learn more… | top users | synonyms (3)

0
votes
1answer
39 views

Flagging duplicates

I have a huge table with a couple of duplicates: I want to add a new column that shows some value when duplicates occur, something like: case when fist.firstname = last.firstname then 'yes' else ...
0
votes
2answers
23 views

Source of network_name for distributor

So, I have a really weird case. Was doing a migration from sql server 2005 to 2012. Built the server, and at the last minute we found that the server was called abc/xyz. We just wanted abc, so the DBA ...
0
votes
0answers
32 views

Is it possible, when using LPIM, for one database to allocate the majority of the memory?

Windows Server 2012 R2 SQL Server 2012 Enterprise (maximum server memory: 200,000MB) 224GB memory LPIM enabled physical_memory_in_use_kb 204703788 large_page_allocations_kb 1128448 ...
1
vote
0answers
26 views

Joining tables and seeing Duplicate Records

I Use the query below to produce values from a table (BSCV1), based on results from another table (MshiftV1) using an inner Join on names and timestamps. I call the output "Correlation". WITH ...
0
votes
1answer
18 views

How do I allow users to add members to a specific database role?

I want to GRANT permissions to a new database role so that defined users can add and remove members from that role WITHOUT granting ALTER ANY ROLE permissions. Here's how my query runs/dies: CREATE ...
5
votes
1answer
48 views

T-SQL (SQL Server 2012) Update based on lookup table

I have the following tables related to this question: CREATE TABLE tblTitan( titanId int IDENTITY (15483,1) PRIMARY KEY, userId int UNIQUE NOT NULL, titanName varchar(15) NOT NULL, titanExperience ...
0
votes
1answer
77 views

SQL Server BACKUP WITH FORMAT

When doing a SQL Server database backup using the T-SQL BACKUP statement, does WITH FORMAT only wipe out the specified Media Set or does it wipe out all Media Sets 'associated' with the database? ...
4
votes
0answers
36 views

Large Variation in Bulk Insert time

So I have a simple Bulk Insert process to take data from our staging table and move it into our datamart. The process is a simple data flow task with default settings for "Rows per batch" and the ...
0
votes
2answers
57 views

Linked Server Error on SQL Agent Job

I get the below error from a SQL Server Agent Job. However, if I run the query in the job manually it works fine. Why is this? Is it just SQL Agent that requires MSDTC? I don't believe it is ...
0
votes
0answers
41 views

Importing Legacy Files directly into SQL Server database

I have a bunch of flat files, mostly Excel files, which I am trying to import directly into a SQL Server 2012 database. Is there a way to do this? I assume first the columns in the Excel files must ...
1
vote
0answers
41 views

Backup fails unless compression is enabled

I'm trying to back up a DB to a network share. The backup fails every time with the same error: BackupIoRequest::ReportIoError: write failure on backup device '\\share_name\Backups\filename.bak'. ...
1
vote
1answer
57 views

SSMS connects, Linked server fails

There's a SQL Server 2012 machine which I have login and password. It's a SQL Server login, not an AD one. I'm able to login using SSMS. But when I configure a linked server in my SQL Server I get ...
2
votes
0answers
44 views

where to add a column with low cardinality, decimal type into an index?

I got a question regarding the best way to put a bad column for an index in an index, so that I can satisfy my query below, and similar others that follow the same pattern. on the query below, all ...
0
votes
0answers
68 views

SQL Server could not retrieve the list of publications for 'XYZ'

First of all please note the following carefully: I have a source database named S_DB on one SQL Server hosted on a machine named S_SRV, on a domain named S_DOM. On a different domain named D_DOM, ...
0
votes
1answer
46 views

SQL Server Backup - move to new server - Media Failure

I've got a server in the UK with SQL Server 2008. It's very locked down firewall-wise. We want a local duplicate of the DB to test and build a new report. Backing up the relevant DB, and then ...
1
vote
0answers
16 views

I only have the original files of database but no .bak file, how do I restore the database? [duplicate]

We were upgrading a server from SQL Server 2012 to 2014 at work and it was giving us trouble so I passed it off to someone else who, when backing up the SQL Server database, didn't create a .bak file. ...
1
vote
0answers
42 views

Multiple conditional counts in the select clause - what's the best way?

(Bit of a long one, sorry) To set the scene: N.b. This is on SQL SERVER 2012 We have a table [CardLog] in an access control system database. Every time an ID card is printed, a row is added to this ...
1
vote
2answers
45 views

Find times when table is least frequently used

I would like to analyse performance of one of the big tables in the database. There are only two types of operations on it. Inserts and reads. Once data is entered it is never updated. I need to ...
4
votes
2answers
46 views

A lot of ad-hoc queries impact on server

We have a third party application which generates a lot of ad-hoc statements (our server has 16GB of memory and there are 140000 ad-hoc queries present in the cache).I've enabled 'Optimize for Ad-hoc ...
1
vote
1answer
98 views

Cannot install database engine services, replication and SQL writer

I am trying to install SQL Server 2012 Express, but I get this error (I try to uninstall and delete registry key, try to change account for database engine to NT Authority\System, but reinstall still ...
0
votes
1answer
31 views

Restore Database Wizard not launching

I'm using SQL Server Management Studio 2012 and trying to use the 'Restore' functionality to copy an existing database into a new one. This is something I've done a dozen times before, but I'm ...
3
votes
2answers
44 views

Can I tell SQL Server not to place data in the buffer cache?

One of the servers I support has an ETL job that runs every morning. This isn't an SSIS package, just a series of stored procedures executed via SQL Server Agent. The job takes several hours to run, ...
0
votes
0answers
12 views

Diagnosing SSBDIAGNOSE?

When trying to connect SSBDIAGNOSE from a command shell to a named instance on the same server(to which my Windows Login has SA), I receive an error e.g. SSBDIAGNOSE CONFIGURATION FROM SERVICE ...
0
votes
1answer
23 views

Create a job that sets Auto Update Statistics on

I am using SQL Server 2012. The vendor wants Auto Update Statistics set to false, but my boss wants it set to true. If I set it to true, after a while it goes back to false by itself. I think the ...
1
vote
1answer
54 views

Forcing correct execution plan when passing a table UDT to a table function

I have a scalar function that returns a big XML, created over a bunch of invoices. The exact list of invoices to feed to the function can be calculated using several different ways, but the function ...
1
vote
1answer
35 views

Merge Two Group By Rows SQL

How do I merge two rows returned by a Group By statement: Select SiteId, SiteName, EventCount = Count(*) From #MyLogs GROUP BY EventCode, SiteId, ...
1
vote
3answers
37 views

Which one to consider for upgrade SQL Server 2008 vs SQL Server 2014 or Compatibility level 100 vs 110

I need to upgrade the SQL Server 2008 to SQL Server 2014 . SQL Server 2008 is currently running on compatibility level 100. I need to upgrade to SQL Server 2014 with compatibility level 100 this year ...
1
vote
1answer
78 views

What is the role of eager writer?

What is the role of eager writer? How this process works internally in SQL server? What is the major difference between eager writer and lazy writer/checkpoint? My understanding here is, that both ...
3
votes
0answers
63 views

SQL Server 2012; Incorrect PLE buffer manager value for NUMA server

We are receiving PLE alerts from SCOM for several instances. These instances are on hardware servers, and use NUMA nodes. SQL Server 2012 SP1 CU7 Enterprise Edition is installed. Memory is fixed and ...
0
votes
0answers
50 views

Set database offline with rollback immediate takes an hour to complete

Every night we take our production database offline, detach it, and then re-create it to sever any connections as the start of our backup procedures. The first command is: USE master ALTER DATABASE ...
1
vote
2answers
61 views

SOS_SCHEDULER_YIELD - Prevent a Proc from giving up the Processor Time

I've got a process which about once a month at times unrelated (or as far as we can tell) goes into SOS_SCHEDULER_YIELD State. This process blocks our main data entry pipeline and brings the entire ...
0
votes
1answer
40 views

Using Partial String match from one query results in where clause for another SQL Server 2012

I need results field of query1 to drive I have one query that produces a table of several columns, namely "Time_stamp", "Value", and "Name" from two existing tables. Select TOP 20 ...
1
vote
1answer
22 views

Multi-Server Extended Events investigations

Do you happen to know if there's a method of centralizing the Extended Events information of many servers in one place (for easier administration and diagnostic). Ideally I would like to have ...
0
votes
0answers
33 views

AlwaysOn Error and Redo Queue

Recently one of our synchronous AlwaysOn Groups stopped replicating data and went into a Non Synchronising state. In the dashboard on the primary everything was showing as OK, but on the secondary ...
-2
votes
1answer
52 views

Trigger is not working in SQL Server 2012 on runtime? [closed]

In SQL Server 2012, I create two tables TABLE1 and TABLE2 After that, I created a trigger for TABLE1 Trigger name: PUSH_TABLE2 After creating the trigger PUSH_TABLE2, I test with below insert ...
1
vote
1answer
73 views

Ola Hallengren's Backup solution error message after moving files using cmdexec stops job, this disagrees with faq

I am using Ola Hallengren's Maintenance script, and I am having an issue which seems documented in the FAQ. For context, there was an issue where the existing SQL Server backups had to be moved to ...
1
vote
1answer
53 views

Transaction log rollback broke Log Shipping chain?

Is it correct that performing a transaction log restore to a previous point in time will break the log shipping chain? If this is correct, is there a way to recover from this situation without having ...
2
votes
1answer
47 views

I want to find out in SQL SERVER 2012, who is creating SQL Logins and on which date

I was trying to find out in sql server 2012, who created login and which date. Can any body help on this thanks,
7
votes
2answers
372 views

Average of last 4 runs of a product

I need to show the average weight of a product over its last 4 production runs. I'm not sure how best to describe it other than an example: Let's imagine I have the following table that lists a ...
1
vote
2answers
63 views

I need to log activity of the sa account

I need to log the activity of the 'sa' database account via Microsoft SQL Server 2012 so I can review the activity periodically and look for strange behaviour for audit purposes. Unfortunately I'm ...
5
votes
1answer
30 views

Statistics on filestream column

When I try to create a statistics on a filestream column, I get the following error message: Column 'MyColumn' in table 'MyTable' is of a type that is invalid for use as a key column in an index ...
1
vote
2answers
34 views

How to return all the unique column values on a per column basis for all metadata tables?

assume multiple tables of various sizes table one col one, col two, col three,... a,d,j a,e,k a,e,m b,e,o c,e,o d,e,o f,e,o return (preferably in this format) col one, col two, col three a,d,j ...
3
votes
1answer
120 views

Split Date/Time value for Night Shift Employee

This is a follow up to my previous question: First-In and Last-Out times for Night Shift Employees I am a beginner in SQL. I tried this query and CTE, but I cannot get my desired output: SELECT ...
1
vote
1answer
89 views

Why SQL function always returning empty result set?

Following function I have write which accept xml and returns table as result. CREATE FUNCTION FunctionTest(@ID INT,@XML_Details xml) RETURNS @RESULT TABLE ( Value1 INT, Value2 INT ) AS BEGIN ...
5
votes
3answers
552 views

Find all IP's or users who access a specific SQL Server

Let's say I found a SQL Server and I have no idea which applications connect to it. Maybe I find one application but I am not sure if it is the only one using it. Is there a nice way of finding all ...
-2
votes
1answer
108 views

Error 35338: Clustered columnstore index is not supported [closed]

On SQL Server 2012, I want to index a table with a columnstore index. But an error message apears: Msg 35338, Level 16, State 1, Line 1 Clustered columnstore index is not supported. My code ...
2
votes
1answer
69 views

Column Name in separate table SQL Server

I am working with an accounting package backend. In SQL Server 2012 there is a custom fields table with Values (see sample below) and another table with custom header names (see sample below). I ...
1
vote
0answers
29 views

What's the deal with transactions in a stored procedure called by an SSRS report?

Consider the following: sp_a contains an sp_executesql call to exec sp_b sp_b begins and commits a transaction, then immediately logs @@TRANCOUNT. When I call sp_a manually from within SSMS, the ...
6
votes
1answer
106 views

Why would a procedure sometimes hang executing SELECT…INTO #Temp2 FROM #Temp1 JOIN LocalTable…?

We have several stored procedures, called by jobs, that follow a specific pattern (designed to minimize blocking). Some of these procs tend to hang on occasion, running up the CPU, tempdb allocations, ...
3
votes
1answer
146 views

How many rows can a SQL Server 2012 table hold, and when should I partition it?

Say a table stores granular data about some event. It has the date of the event, a type dimension with around 30K types, and a category dimension with around 100 categories, plus some numeric facts. ...