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

learn more… | top users | synonyms (3)

0
votes
1answer
17 views

SQL 2012 THROW in SP That needs to be run on SQL 2005 and 2012

Let me start of by saying that I know SQL 2005 doesn't support the THROW feature. I need a SP to be run on SQL Server 2012 and 2005. I would like to use the following section of code to allow SQL ...
7
votes
2answers
244 views

Microsoft SQL Server 2012 Mirrors Go Into Disconnected

I have some Microsoft SQL server 2012 mirrors, without a witness in asynchronous mode, that are sporadically going into disconnected. I am running SQL Server 2012 11.0.5058. Looking at the logs on ...
1
vote
2answers
39 views

Which columns to place the clustered index

I have a system that has been up-and-running for almost 3 years now. Previously (and currently) the front-end was using NHibernate for all of the database access. Right now, I'm in the process of ...
0
votes
2answers
21 views

Merge two tables or keep separate?

A little background We are adding a new feature to our software which allows users to enter text for a specific Customer or Location. The software will then read these from the database when a ...
0
votes
1answer
19 views

SQL Server Audit file

I have faced with some challenge regarding my audit. I need to save my audit events on a file, but file should be in a readable format ( like txt) which will allow to analyze it out of SSMS. XXXXX....
0
votes
1answer
27 views

How can I handle a huge amount of logging generated from frequent table rebuilds?

We have several tables which we "refresh" frequently by rebuilding them in a staging-table, then performing a metadata-switch to the production-table using the ALTER TABLE <source> SWITCH INTO &...
0
votes
0answers
22 views

Error - Error: 1204, Severity: 19, State: 4

SQL Server details: Microsoft SQL Server 2012 - 11.0.5548.0 (X64) Sep 6 2014 17:19:28 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows 6.3 <X64> (Build 9600: ) ...
1
vote
1answer
38 views

Threads being blocked when trying to access SQL Server

I'm having a rather strange problem. For some time now, I've been developing a project on SQL server and everything has been going fine. Yesterday, I was given a new .bak file to restore, containing ...
0
votes
2answers
56 views

Should I shrink the Log File

I am a developer but have to do DBA stuff now and then. We had a problem where the Transaction log files of our DBs where increasing to large size of 50GB while the data file (MDF) was only 10-15 GB. ...
1
vote
2answers
80 views

Service Packs and CUs for SQL Server, do these patches make any changes to user database files? or just instance level binaries?

I am curious about patching on SQL Server. Does anyone know if SPs and CUs make any changes to user database files or is it just the database engine and instance level binaries that get modified/...
0
votes
1answer
31 views

Query - Pivoting 2 columns into 2 rows

I am familiar with PIVOTS but have done only simple ones of one column into a row. I have this query: DECLARE @EndDate DATE SET @EndDate ='20160728' --{?EndDate} SELECT T1.ItemCode,SUM(T0.IssuedQty)...
2
votes
1answer
2k views

Inline edit SQL Server database rows from Visual Studio

I'm pretty sure Microsoft have pulled one of the most useful features for performing quick edits on a SQL Server Database within the Visual Studio IDE. It seems to have only affected SQL 2012 ...
6
votes
3answers
517 views

Does the order of columns in a columnstore index matter?

I have a table with ~200 million rows and ~15 columns in it. I am planning to create a COLUMNSTORE index on my table. Will there be any change in performance based on the order of columns that I use ...
1
vote
1answer
52 views

How expensive is a SELECT that returns an empty result set?

I'm analyzing the HTTP logs to one of our web services and I've noticed a lot of request to an endpoint such as /entities/0 which will end up effectively doing something like SELECT x, y, z FROM ...
0
votes
1answer
137 views

SQL Server : how to decide if FULL backup or DIFFERENTIAL

The following scenario (on SQL Server 2008 R2 and 2012 servers) A co-worker of mine restored another DB into the one our process was trying to create a differential backup for. As I am currently only ...
0
votes
0answers
56 views

SQL Query to calculate total time worked and total distance travelled

I am trying to write an SQL Query which calculates the Total Time worked and Total Distance Traveled by a Driver for each date. The StartOfDay is the MIN(LogDateTime) WHERE EventCode = 1 The EndOfDay ...
1
vote
1answer
18 views

How to avoid Msg 9514, Level 16, State 1, Line 3 error for linked server?

I have a query to compare data between my local SQL Server and linked SQL Server. My problem is that the table I compare contains XML column. I get: Msg 9514, Level 16, State 1, Line 3 Xml data ...
0
votes
1answer
29 views

Trigger on replicated DB

If I have database db1 with schema sch1. I want to create another schema sch2 which will be used by different application and the tables in sch2 will be populated from tables in sch1. So my approach ...
6
votes
1answer
170 views

SQL Server Agent initiating self-termination

For the past month, one of my servers is facing issues with SQL Server Agent. It stops automatically with below error, which happens randomly: Sql Agent Output file [097] Memory leak detected [...
0
votes
2answers
22 views

SQL Server - Select last value given a date

To make it easier, I have data that looks like this as an example: What I am trying to return is the Amount of the Balance Update as of a specified Date for each Investor_ID. For example, if my ...
0
votes
1answer
111 views

on every row on SELECT Query must lookup to other table

I'm working on Login/Logout System and I'm facing this problem regarding on SELECT Query. I have 2 tables namely dtr_tbl and shift_tbl. In dtr_tbl are the logs of the user(login/logout time details) ...
6
votes
1answer
2k views

Alternatives to AdeptSQL Diff (for MS SQL Server 2012)

It seems AdeptSQL Diff is not compatible with SQL Server 2012 (at this time). And it is my preferred tool for deploying changes. I realize this is a shopping list but I hope SE won't mind. I am ...
3
votes
2answers
2k views

Error message - view server state permission was denied - when connecting 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?
4
votes
0answers
54 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 ...
1
vote
1answer
21 views

Permissions for Cluster service account in SQL server

Recently i faced an issue where the SQL server 2012 being hosted in a 2 node failover cluster failed to start. And since the cluster and windows event log didn't help much, i started checking the SQL ...
0
votes
1answer
52 views

Database design considerations for unused columns with every table has same schema

I am a web developer so I don't know a lot about databases. The company I joined recently has a very mature Desktop ERP built in .NET and SQL Server, they are providing services to huge corporate ...
0
votes
2answers
1k views

SQL Server 2012: Full Text Catalog taking up all the disk space

We have created full text catalog and index for a pretty large table. The column on which the index is being created has a datatype of varchar(max). The problem started as soon as we started ...
1
vote
2answers
43 views

Execute stored procedure remotely using linked server

We have 3 SQL Server 2012 instances, 2 of them we're not db_owner. Some of our stored procedures use DMV and sys views and stored procedures that only work when executed locally. I'd like to ...
0
votes
0answers
14 views

Issue with Linked Server query

I have a job which fetches data from an Oracle 12c database and inserts into a SQL Server 2012 database, via a linked server from the SQL side. It's a simple Select * statement and just pulls in any ...
0
votes
1answer
1k views

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

I have a linked server on SQL Server 2012 instance pointing 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
105 views

Cannot Update a System Collection Set

I'm trying to tweak the "Server Activity" Data Collection set as outlined in this blog post (I wrote the post, btw. Sorry--it's kinda long). It is working in SQL 2008 R2 and also in SQL 2014. However,...
4
votes
1answer
178 views

Scenario of huge SQL Server Database backup

I use SQL Server and have a huge database with large compressed backup size (about 250 GB). The backup scenario of this database is: Create full backup at the first day of each month. Create ...
1
vote
2answers
174 views

Get first and last record for each date and calculate their difference

I need to calculate the DATEDIFF in seconds between the First record for each date with EventCode = 1 and the last record for each date with EventCode = 2. I have posted a sample data set for 2 days ...
0
votes
0answers
26 views

Finding records only when all ids present in a group

Having these two tables id | val TableA -------- 1 | A 2 | B 3 | A 4 | B 5 | A 6 | C 7 | D id_A | data TableB ----------- 1 | a 2 | b 3 | c 5 | d 7 | e First I ...
0
votes
1answer
45 views

Update empty values with null

I create a Job that runs the query below for all databases in a server in order to update empty values with null in all columns for all tables. The problem I'm facing is this query literally killed ...
2
votes
2answers
20 views

Concurrent backups (full and transaction) and testing recovery model

My intent is to test my recovery model and setup log shipping to replicate a database on another server. I have a database called AdventureWorks on a primary sql server. Presently SQL Backup performs ...
5
votes
1answer
302 views

Move content between filestream containers

I have a server running SQL Server 2012 with Multiple Filestream containers on a single table. I need to split one of the existing filestream containers into two parts by creating a new filestream ...
9
votes
1answer
444 views

Update queries slower after enabling SQL Server Full-Text Index

I have an asp.net website with many insert, update, and delete queries running against my database. A few days ago I create a Full-Text Index on two columns of one of tables. After that I realized ...
1
vote
2answers
63 views

SQL Server 2012 database in suspect mode after upgrade from Developer to Standard Edition

SQL Server database is in suspect mode after an upgrade from developer to standard edition 2012 version. In the log file there are two errors: 933 and error 905. Following error occurred when we tried ...
7
votes
2answers
4k views

AlwaysOn Availability Group Automatic Failover does not work

Playing with AG setup I have the WSFC up and configured with two nodes in one availability group called DevClusterOnline. Both nodes (DEV-AWEB5 primary, DEV-AWEB6 secondary) are running Windows Server ...
16
votes
1answer
17k views

Why does SQL Server 2012 Express use 9.5GB of RAM on my server?

I am building an application in which I plan to embed SQL Server 2012 Express as the primary datastore. When testing on my development machine (Win7-32 with 3GB RAM), I never observed the sqlservr....
1
vote
0answers
63 views

Logical read when use Offset in SQL

When I execute this script SET STATISTICS IO ON SELECT *, ROW_NUMBER() OVER (ORDER BY [Date] DESC) AS [ROW_NO] FROM vwTblTest WHERE ([Factor] >= 139501 AND [Factor] < ...
0
votes
1answer
332 views

delete WSFC 2012 cluster role/group fails

PS C:\Windows\system32> get-cluster Name ---- sql2012poc_cl1 PS C:\Windows\system32> get-clustergroup Name OwnerNode State ---...
-1
votes
1answer
36 views
1
vote
1answer
53 views

Matching empty string is not working

I have the following query SELECT Ice.IceId, Ice.Code, Box.Ice, LEN(Box.Ice) AS IceCharLength, DATALENGTH(Box.Ice) AS DataLength, Box.Ice, ASCII(Box.Ice) AS ASCII, CAST(Box.ice AS ...
0
votes
2answers
84 views

Tiny query used to be sub-second speed, now takes 60+ seconds

Ok, I've got a monitoring process that runs the following query to make sure the databases are backing up regularly. This process used to take 1-2 seconds for connection, query execution, and ...
0
votes
0answers
19 views

Querying Table from another Database using Synonyms

I have two databases(A,B). on database "A" i created synonyms CREATE SYNONYM [dbo].[Names] FOR [server1].[B].[dbo].[Names] I should now be able to query the table "Names" from within database "A" ...
0
votes
1answer
25 views

Call to sysmail_delete_mailitems_sp keeps deadlocking

I run SQL Server 2012. I have a maintenance job that runs every night. At the end of the maintenance, I do some cleanup and call this: Begin try Exec msdb.dbo.sysmail_delete_log_sp @logged_before = '...
1
vote
1answer
164 views

Indexed view doesn't exist on the subscriber

I am configuring transactional replication in SQL Server. The subscription is configured as push from 2008R2 publisher (distributor is the same server) to 2012 subscriber. The object I want to ...
3
votes
0answers
36 views

Concurrent Merge Issues

A (simplified) model of my application has several processes dumping data into the table below: CREATE TABLE [dbo].[InputData] ( [InputId] [int] NOT NULL, [Dts] [datetime] NOT NULL, [...