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

learn more… | top users | synonyms (3)

5
votes
1answer
35 views

Find the source of a recurrent mass SQL edit on a server

I'll try to explain my problem as clear as possible. The server of a company I support runs many websites. This server runs Windows Server 2012 with Microsoft SQL Server 2014. Almost all of the ...
2
votes
1answer
45 views

Advice on how to improve the efficiency of a stored procedure using lots of joins

I have a stored procedure that is returning search results for items in a database grouped by customers who own them. The user performing the search is restricted as to what items they can see by ...
-1
votes
1answer
36 views

SQL Server installation location [on hold]

What is the best practice recommendations for installing SQL Server 2012 or 2014 with regards the installation files location. Can the installation files location resides on the system drive or should ...
4
votes
2answers
35 views

SSIS OLEDB Command not firing triggers

Got a bit of a strange one that I can't seem to work out. We have a vendor database ( so we have no control over the table structure, foreign keys, etc...) that's on SQL Server 2012 SP2. We have a ...
1
vote
0answers
35 views

What is the correct way to rename/swap a table in production

I have two tables, tblCode and tblCodeAlternative tblCode has about 4100 rows and is used in production. tblCodeAlternative has about 4200 rows and is in the production database but not used ...
4
votes
2answers
80 views

Unable to create restore plan due to break in LSN chain

My backup plan is: Full backup - once a day Differential backup - every four hours Transaction Log backup - every 30 minutes. All database backup are stored on my computer. I add some wrong ...
4
votes
1answer
71 views

There is insufficient system memory in resource pool to run this query

Over the past year we have been suffering a situation where many different queries at the same time will fail with the following error (or a variation in a different resource Governor group): There is ...
1
vote
1answer
26 views

Do I need different linked server remote users?

I have inherited a project that uses a local Microsoft SQL Server 2012 Express Edition and a central SQL Server 2012 as a backup. There may be multiple local "machines"/databases that all are linked ...
1
vote
1answer
39 views

Get start time and end time of a trip in SQL Server

I have a table that contains zone in and out information about a bus, and its structure is like this: id zoneid status datetime 1 35 IN 2016-02-02 11:36:39 2 35 OUT 2016-02-02 ...
1
vote
0answers
29 views

Could you please explain clustering in SQL server? [on hold]

I'm new to clustering concepts. I would like to know more parallel processing with multiple DB nodes to have load balancing for the incoming requests. Our main goal is to achieve performance and lot ...
1
vote
1answer
28 views

Get Table Name from Transaction Log query

I can't figure out why this join isn't working to return the table name when querying the transaction log file. SELECT [Transaction ID], [Current LSN], PartitionId, sp.object_id, ...
6
votes
2answers
118 views

Selecting Distinct Rows but Counting All Rows

I have the following table: ---------------------------------------------- | ID | interestingData | timestamp | ---------------------------------------------- | 1 | 400 | ...
4
votes
1answer
233 views

'CONCAT' is not a recognized built-in function name

A client reported that they were running on SQL Server 2012, and we delivered some test queries for testing prior to a final delivery, however: 'CONCAT' is not a recognized built-in function name. ...
3
votes
1answer
59 views

Select data divided in groups evenly distributed by value

I would like to select into 4 groups the data from a table having the sum of a values in the groups as evenly distributed possible. I am sure that I am not explaining it clear enough so I will try to ...
-1
votes
0answers
46 views

Updating next record [on hold]

Our SQL Server 2012 application creates a routing for parts. When work is done we subtract the value from QTYREM_14 and add it to QTYCOM_14. The amount in QTYCOM_14 is mirrored to QTYCOM_14 on the ...
4
votes
1answer
93 views

Datawarehouse indexing

I have a table with nearly 6m records. The business key that identifies a unique row is quite large. Our update processing is now taking a lot longer since I added this new table for a new cube. I ...
9
votes
5answers
258 views

Getting a scan though I expect a seek

I need to optimize a SELECT statement but SQL Server always does an index scan instead of a seek. This is the query which, of course, is in a stored procedure: CREATE PROCEDURE dbo.something ...
0
votes
1answer
26 views

Generic stored procedure for results sets comparison

Some of the tasks in my team are related to stored procedure optimization. I am mainly a .NET developer, but I thought of writing a generic procedure for data comparison while procedures are being ...
5
votes
1answer
79 views

First-In and Last-Out times for Night Shift Employees

I can get FirstIn - LastOut out of Day shift employee using min and max: select UserId, [date], min(convert(char(5), [Login], 108)) as FirstIN, max(convert(char(5), LogOut,108)) as ...
1
vote
2answers
165 views

How to use SET operation in CASE clause in SQL?

I have the following structure, and I'm doing a SET operation in a CASE clause but it is producing an error: Incorrect syntax near SET Query: DECLARE @tbl_Result TABLE ( RowID INT ...
-2
votes
1answer
34 views

I have created a zip file using 7z for backup but its taking time to open [closed]

I have created a zip file using 7z for backup but its taking time to open. Any possibility of making fast. Please suggest me.
0
votes
1answer
40 views

SQL Server 2012 Remote Access to Certain Users [closed]

Recently, I've started working on SQL Server 2012. As there is a risk in allowing anyone to access SQL Server remotely, I'm trying enable remote connections only to specific users within my developer ...
-1
votes
2answers
65 views

Calculating multiple medians

I have defined function with table value parameter which returns median as per values passed to it. Function defined as: CREATE FUNCTION [dbo].[fn_GetMedian](@List TypeMedian READONLY) ...
10
votes
1answer
153 views

Does CREATE TABLE SomeSchema.#TempTableName have a bug?

Simple test-bed: USE tempdb; GO /* This DROP TABLE should not be necessary, since the DROP SCHEMA should drop the table if it is contained within the schema, as I'd expect it to be. */ ...
2
votes
1answer
72 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
40 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
338 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
13 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
52 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
40 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
15 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 ...
7
votes
2answers
222 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
400 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
53 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
36 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
43 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
36 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 ...
4
votes
2answers
121 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
159 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
64 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 ...
0
votes
1answer
45 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
37 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
12 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
141 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
49 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
20 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
39 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 ...
1
vote
1answer
112 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
10 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 ...