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

learn more… | top users | synonyms (3)

2
votes
1answer
116 views

How to select a subset from a query result

I have a query which calculates the Time Difference and Distance Between the Next and Previous Row: SELECT * FROM ( SELECT iVehicleMonitoringId AS PreviousId, ...
-2
votes
1answer
79 views

why unable to execute table-valued function?

Here is table-value function I have declared like: CREATE FUNCTION fn_GetMedian(@List TypeMedian READONLY) RETURNS INT AS BEGIN RETURN ( Select ( ( ...
4
votes
2answers
417 views

How can I pass column to function in sql?

I'm calculating median as: DECLARE @TEMP TABLE ( ID INT ) Select ( ( Select Top 1 ID From ( Select Top 50 Percent ID ...
0
votes
0answers
39 views

Can I install 32-bit SQL Server 2008R2 drivers on Windows Server 2012?

We have a 32-bit app built using Powerbuilder that needs to connect to SQL Server 2012, but the version of Powerbuilder used by the application can only connect to SQL Server up to version 2008R2. We ...
3
votes
1answer
139 views

Multi Subnet Availability Group IP Requirement for Windows Failover Cluster

Does the Windows Failover Cluster for a multi-subnet SQL Server Availability Group require a static IP entry for each subnet? The instructions I am finding for a Failover Cluster Instance (FCI) in a ...
1
vote
0answers
75 views

SQL Server 2012 - log shipping issue

I'm sort of new to SQL Server so bear if the doubts are silly. I have configured log shipping for my DR purpose. SQL Server 2012 is the version. Today I encountered the following error in my log ...
3
votes
1answer
72 views

SQL Server Service Broker - CHECKDB found consistency errors, how could this happen?

I encountered a weird SQL Server Service Broker consistency problem, namely I have a queue that holds messages (status=1, Ready to receive) which are lying there for a very long time. In the ...
8
votes
2answers
410 views

Shrinking a SQL Server Transaction Log

I’m currently having to deal with a SQL Server transaction log that has got out of control. Disclaimer: I’m not a dba and this isn’t my area of expertise so please bear with me. Currently I have a ...
4
votes
1answer
823 views

Getting error “must declare the scalar variable” in SQL function even though it is declared

I am altering function like: ALTER FUNCTION [dbo].[fn_CalculateListing](@Listing TypeListingDatePrice READONLY) RETURNS TypePreviousListingResult AS BEGIN DECLARE @tbl_ListingDateDetails ...
0
votes
1answer
268 views

Difference installing SQL Server 2014 on stand alone vs. VM Cluster [closed]

I have installed SQL Server many times on standalone servers with DASD and SAN storage allocated to it. We have a new Hyper-V Virtual machine Cluster set up for us by a third party IT shop. I am ...
0
votes
1answer
48 views

If my Data, Log and Tempdb drives are on the same LUN, are they using 1 physical drive?

My SQL Server Data, Log and TempDB resides on different logical drives. But when I check the drive properties under Hardware tab, their location are located on the same Port, Bus, Target ID and LUN. ...
5
votes
1answer
115 views

How to run SQL services on NT SERVICE\MSSQLSERVER account if it is running earlier on LocalSystem

Recently just for experimental puprose, I have relocated the default (C:...) data\log file paths in server settings to new location (G:\DBA). Then I've moved the previously existing db's data\log ...
1
vote
1answer
130 views

SQL Server 2012 Express Service Pack 2 - installation error 0x84B40001

I'm trying to install SQL Server 2012 Express Service Pack 2 through command line: SQLEXPR_x86_ENU.exe /ACTION=Install /HIDECONSOLE /FEATURES=SQL /INSTANCENAME=SQLEXPRESS /SECURITYMODE=SQL /SAPWD="...
4
votes
2answers
150 views

Will the Query Optimizer Ignore A Fragmented Index?

Scenario: I have a heavy OLTP table with an index. I see many inserts, updates and deletes and the index fragments heavily within a day or less. While on day one of the index build, the optimizer ...
1
vote
3answers
243 views

Improve a query using cursor to SET Based approach

I am still new to Query optimisation, and I have a Stored Procedure which uses a Cursor to go through each row within the Table, and performs the following operations: Calculate the Time Difference ...
0
votes
1answer
88 views

How to write function in sql which accept table as input and return result back as table?

I want to pass a table variable to a fuction (which would contains different value at different time) as input, returning a calculated result in the form of another table, which would be declared in ...
-1
votes
0answers
32 views

Get the sum of Total Churn_MTD(Current+2015+<2015) [duplicate]

how to declare 8 variables and get the total Sum,as you can see from the picture i created the 3 store procedures but dont know how to do the rest(Churn_MTD_Current_Year+Churn_MTD_Previous_Year+...
0
votes
1answer
54 views

SQL Server 2012 Transactional Replication - twin subscriptions on same machine

Server A publishes Pub1, Server B subscribes to Pub1. Server A and Server B are both SQL Server 2012 Standard Edition, default instances. When I use Replication Monitor to drop-in a Tracer Token, ...
2
votes
2answers
211 views

How do I create a SQL Agent Alert on a custom error message in SQL Server 2012?

I want operators to be notified when I throw a custom error message. My understanding is that I need to add a message to sys.messages, then I can either RAISERROR or THROW that error ID. Creating an ...
0
votes
0answers
19 views

bcp copy from snapshot failed due to snap being in transition

We have a SQL job that runs each night to perform a data extract and import function. The job performs against a snapshot and copies from several tables in a serial loop. Today the job reported the ...
9
votes
1answer
197 views

Why does SQL Server refuse to update these statistics with anything but fullscan?

I noticed a relatively long running (20 min+) auto update statistics operation in a daily datawarehouse build. The table involved is CREATE TABLE [dbo].[factWebAnalytics]( [WebAnalyticsId] [...
-1
votes
1answer
55 views

What is the optimized way to fill table values based on it's previous columns values and from other table column values?

CREATE TABLE TEST2 ( RowID INT IDENTITY(1,1), Ratio INT ) CREATE TABLE TEST ( RowID INT IDENTITY(1,1), Col1 INT, Col2 INT, Col3 INT ) Now, I want to insert values in table ...
1
vote
0answers
43 views

Change default value for Job Activity Monitor

Is there a way for me to programmatically (some configuration setting in a table / registry entry etc, I'm hoping) change the default value in the Auto Refresh setting to 5 seconds instead of 60, and ...
2
votes
2answers
53 views

Select ID from costumers that do not have contacts last 7 days and 2 times in the month

I have 2 tables: custumer, c_contact. c_contact is all the e-mails I send to my custumers. From now on I need to put a new rule that the costumer can't receive a new e-mail if: 1) It received an e-...
1
vote
1answer
129 views

Conversion fails when converting date average to date in sql

I have take date average from date and trying to get result of average date but it raising error like: Conversion failed when converting date and/or time from character string. sql query executed as: ...
0
votes
1answer
53 views

MS SQL Server 2012 Data Quality Services (DQS) installation failure

I tried to install MS SQL Server 2012 DQS using DQSInstaller.exe, but installation failed. In the console output started by the install tool (DQSInstaller.exe) I found this line: Password validation ...
6
votes
1answer
142 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 [...
4
votes
1answer
329 views

CPU Schedulers Offline

I logged onto a new clients systems and ran sp_blitz to see what's shaking. It reports back that "CPU Schedulers Offline" which is a new one for me. Some CPU cores are not accessible to SQL ...
-1
votes
1answer
42 views

AlwaysOn Multi-Subnet Setup

Without using SAN-to-SAN replication, how does data synchronization occur across subnets in a multi-subnet AlwaysOn (AVG) configuration? I want to setup a separate VLAN (replica lan) to synchronize ...
2
votes
1answer
55 views

Is there a query to see which stored procedures are using a specific symmetric key?

Is there a query to see which stored procedures are using a specific symmetric key? Or a built in feature of SQL to see this information. The stored procedures are using: OPEN SYMMETRIC KEY !@#$%^ ...
3
votes
2answers
165 views

Patching strategy when Windows and SQL Server patches not applied for over 3 years

The company I'm at hasn't patched their production servers for a while, neither SQL 2012 or Windows 2012. When I say "for a while" I mean, well, actually "never" - no patches or updates since the ...
1
vote
0answers
39 views

Backup creating fork_point

I have a powershell script that is restoring from production into a test availability group database. It copies the full and t-log backups from production to test, takes the target db out of the ...
4
votes
1answer
180 views

ORDER BY and comparison of mixed strings of letters and numbers

We need to do some reporting on values that are usually mixed strings of numbers and letters that need to be sorted 'naturally'. Things like, e.g. "P7B18" or "P12B3". @The strings will mostly be ...
0
votes
1answer
70 views

Unable to connect SQL Server 2012

I am facing an issue in connecting to SQL Server 2012 with a .net application. SQL Server 2012 database and code are deployed on the same server. Connection string: <add key="umbracoDbDSN" ...
4
votes
1answer
107 views

How to read multiple record from xml to insert in table in SQL?

DECLARE @tbl_XMLResult table ( Status varchar(50), Address varchar(100), ListPrice varchar(25), SoldPrice varchar(25), YearBuilt varchar(25), SF varchar(25), Acres varchar(...
0
votes
1answer
58 views

How to read and process xml data from stored procedure in sql server?

I am calling a stored procedure like this: usp_ReportResults @query = '759,905,1048,170,725,80129', @ReportName = 'GenRepot' where usp_ReportResult is defined as CREATE ...
6
votes
1answer
823 views

Why does database recovery take so long?

On my SQL Server 2012 x64 Std instance I had a large transaction fail due to the transaction log filling up yesterday, it couldn't be rolled back and SQL Server has restarted the specific DB to ...
0
votes
1answer
85 views

SSDT Not Dropping all Foreign Keys before Drop Table

SSDT V11.1.50730.0 Using Schema compare to make changes from my development environment to a test environment (a copy of Production). Part of the changes is the addition of columns to an existing ...
3
votes
0answers
36 views

High paging and memory consumption in SQL Server [duplicate]

This is my first question about SQL Server, I'm new to SQL Server and I don't understand few things, so hope you can help me. We got a SQL Server 2008 with 2 instances and also with Analysys Services ...
2
votes
0answers
14 views

Installing SQL Server 2012 Standard on a machine with more than 64GB RAM

I’m in the process of doing the specification for a new server that will host SQL Server 2012 including SSIS, SSRS and SSAS I’m aware SQL Server 2012 Standard has a limit of 64GB. However from what ...
1
vote
1answer
422 views

MS SQL Server 2012 Express - install service pack 3

Is it possible to install service pack 3 to an existing instance of MS SQL Server 2012 Express? The only install files I found are: MS SQL Server 2012 and Service Pack 3 for MS SQL Server 2012 not ...
2
votes
1answer
58 views

SQLCLR assembly throws error when multiple queries run simultaneously

I have been using SimMetrics to implement the QGramsDistance search in SQL Server. The SimMetrics is used as a SQL CLR assembly. The search results are promising and everything was well, until of late,...
6
votes
2answers
163 views

list of batch aborting errors in SQL server

In SQL Server, if XACT_ABORT is off then some errors will terminate the current statement (for example supplying the incorrect number of parameters to a stored procedure that takes some parameters) ...
2
votes
1answer
111 views

Why SQL Server Express 2012 take automatically backup in “Virtual Device” without virtual device configuration?

I have noticed that after migration of SQL Server 2012 Enterprise to SQL Server 2012 Express, every day it shows "spurious" virtual backup after taking manual backup of database. When I execute this ...
3
votes
1answer
69 views

Is replication a suitable method to move a SQL Server database to new hardware?

I need to move a live database with about 100 GB of data to a new machine. As I understand the easiest way to do that would be to take it offline, move the files and bring it back online. But: The ...
0
votes
0answers
22 views

Optimizing the UPDATE For MSdistribution_history

One of the major headaches in my replication is the following MS procedure (over 55% usage), updating the distribution history. It currently has one index on columns agent_id, timestamp, runstatus, ...
1
vote
2answers
77 views

Ordering of a dimension in sql server datawarehouse

Built and deployed a simple cube with a date dimension. The dimension has things like day of month. How do i sort it? it always shows in the reports as 1, 10, 11, 12, ... 19, 2, 20, 21 Design. ...
3
votes
1answer
73 views

Kick a single user out of the database

I've seen multiple answers about kicking all of the other users but is there a way to stop the process id and kick just one user out of the database. Eg. user A B and C are connected to the database ...
-1
votes
2answers
44 views

because of Date format im not able to get few records [closed]

For example declare @string varchar(30) = 'dec. 24, 2015' select substring(@string, 10, 4) declare @output date select @output = case when substring(@string, 1, 3) = 'Dec' ...
1
vote
1answer
186 views

SQL Server Management Studio 2012 Application Cannot Start

I need someone's help. I'm trying to install SQL Server Management Studio on my Laptop Operating System: Windows 7 Service Pack 1 But I got an error message saying: The application cannot start. ...