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

learn more… | top users | synonyms (3)

1
vote
1answer
41 views

Cannot connect to SQL Server database regardless of connection string

I'm having a tough time to connect to a local SQL Server instance within a C# app. I can connect to external databases on other servers, but I need to be able to use my own local database. The ...
4
votes
1answer
70 views

Why are these two HADR DMVs reporting different states?

SQL Server 2012 (11.0.5058.0) Enterprise Edition We have 8 Availability Groups in a 2(HA)+1(DR) cluster and our monitoring DMVs are reporting results that confuse me. 6 Availability Groups are ...
1
vote
1answer
55 views

No rows in sys.dm_exec_procedure_stats for a particluar stored procedure

I am running DMV queries to get stats for my stored procedure, but I am not finding any rows against this SP under sys.dm_exec_procedure_stats. Please suggest what could be various reason for this ...
0
votes
1answer
42 views

Need to make sense of FileTable permissions

I'm setting up a FileTable to allow users to quickly stuff some PDF report files into a database for later searching via an intranet application (to be built later). Sorting out the necessary ...
2
votes
2answers
120 views

Why can't user with SQLAgentOperatorRole delete his own SQL jobs

I created a login and a user. I assigned the user as the db owner of the database "test". Then I granted him the role SQLAgentOperatorRole so that he can delete/create/modify the jobs belonging to the ...
2
votes
1answer
36 views

What should be the command for following role creation requirement?

I need to create a role where the assigned user can access only the 'AdventureWorksDB" where he will have execute permission to all tables,views and stored procedures in the database. I wrote the ...
-2
votes
1answer
36 views

when adding a table to my dbo.view I don't get the entire view

here is the code for my view that calls all rows from the tables: SELECT dbo.Purchase_Order.Purchase_Order_ID, dbo.Purchase_Order.Purchase_Order_Number, dbo.Purchase_Order.Purchase_Order_Date, ...
1
vote
1answer
55 views

Identity column value falling behind randomly

We are having a strange intermittent issue where the identity column value falls behind the MAX(ID) for the table. Each customer gets their own table that stores statistics to the tune of ...
13
votes
2answers
109 views

Index on a persisted computed column not seekable?

I have table, called Address, that has a persisted computed column called Hashkey. The column is deterministic but not precise. It has a unique index on it that is not seekable. If I run this query, ...
2
votes
1answer
36 views

How to add new named instance in SQL Server 2012 [closed]

We have already installed SQL Server 2012 with required instance and collation name. But now as per the project needs we have need to add one more instance name. Can anyone knows how to add new ...
1
vote
1answer
34 views

How to group data and write it's group id for each row?

I have sample data: CREATE TABLE #T (Name varchar(5), GroupId int NULL) INSERT INTO #T (Name) VALUES ('A'), ('A'), ('A'), ('B'), ('B'), ('C'), ('D'), ('D') Name GroupId ----- ----------- A ...
1
vote
2answers
71 views

How can I run a SQL job every 1 second? [duplicate]

I have an SQL Job which processes minimum amount of rows at a given time. Currently its running every 10 seconds which is the minimum available in Job Scheduler. But because of that the table used ...
1
vote
2answers
50 views

When to prefere a truncate over a batch-delete?

When I browse DBA-Reactions I understand nearly all the .gifs but with this one I have trouble: https://twitter.com/DBAReactions/status/613840893322338304 I mean truncate obviously doesn't create ...
1
vote
0answers
36 views

SSRS puts excessive load on SQL server

I have this bit of code: With ab as (Select distinct d.Code from d join a on d.code = a.Code where a.text like '%AutoPost%' ), sd as (Select distinct m.ref, m.Number, m.Completed, s.ID, ...
1
vote
2answers
44 views

Automating an SQL task on a monthly basis

At the start of each month I have to give a team a copy of the live database which is held in a backup from 1am each morning (D:\Backup) - We then copy this database elsewhere onto a dev server and ...
0
votes
1answer
16 views

Correct way of configuring remote users to connect to Microsoft SQL Server 2012

I have 2 Servers, a web server and a database server. Web server runs a number of .net sites that need to connect to the database server. Currently, I have a configuration string for each ...
3
votes
1answer
41 views

Determine Gap Records, Fill and Return Result

The following working code targets SQL-Server 2012 and I have distilled the actual problem into a manageable scenario with the code example shown below also presented on SQL Fiddle. I seek feedback ...
1
vote
1answer
32 views

SQL Availability Group restore database to standalone server

Running: SQL Server 2012, Windows Server 2008 R2 I want to test a backup by restoring onto an isolated server. The backup was taken from an Availability Group (HADR) configuration, but the target ...
1
vote
0answers
50 views

A significant part of sql server process memory has been paged out. Lots of free memory though

Having a problem where my SQL Server 2012 SP2 Enterprise server says that most of SQL Server's memory has been paged out to disk but there's over 100GB free (of 256GB). From there system performance ...
1
vote
1answer
23 views

Attach Database is disabled in Microsoft SQL Server 2012 Management Studio

I have buy from Godadday hosting and domain, after that I have create database in godaddy and the connect to this db using my authentication. I have .mdf, .ldf that need to attach to the connected ...
3
votes
1answer
59 views

How to create a login and user that can only access one schema in one database

I have executed the following: USE [MyDatabase]; GO CREATE LOGIN [MyDomain\MyAccount] FROM WINDOWS WITH DEFAULT_DATABASE=[MyDatabase]; CREATE USER [MyDomain\MyAccount] FOR LOGIN [MyDomain\MyAccount]; ...
1
vote
2answers
86 views

SQL Server 2012 and DBM

Why would MSFT would deprecate mirroring from 2012.. I understand alwaysON will replace it but then SE users are left with nothing..no alwaysON or dbm from future releases.. Just analyzing from end ...
7
votes
4answers
2k views

How can I convert a key in a SQL Server deadlock report to the value?

I have a deadlock report that tells me that there was a conflict involving waitresource="KEY: 9:72057632651542528 (543066506c7c)" and I can see this: <keylock hobtid="72057632651542528" dbid="9" ...
1
vote
1answer
34 views

SQL Server full text search query takes way too long to execute

I've got query snippet as follows: SELECT * FROM CONTAINSTABLE(dbo.Analysis, *, 'FORMSOF(FREETEXT, "historic year-on-year exchange rates forecast year-on-year exchange rates")'); I'm running it on ...
2
votes
0answers
39 views

SQL Server 2012 replication setup on routed network

I'm trying to setup replication between two SQL Server 2012 instances. I set up a publication successfully on server1, but then I had a problem when trying to access it from server2, because the only ...
0
votes
1answer
23 views

SQL Server not using all cores

I have a server with 6 cores, I've installed SQL Server 2012 web edition, based on core licensing, allowing up to 16 cores. However, it only uses 4 cores. In the error log file: 2015-07-09 ...
1
vote
1answer
50 views

Problem trying to force the use of a filtered index

I was trying to do some testing on filtered indexes yesterday and created a fairly simple situation: CREATE TABLE IndexTest ( ID INT NOT NULL IDENTITY(1,1) CONSTRAINT pk_IndexTest PRIMARY KEY, ...
2
votes
2answers
75 views

Execution plan has TOP operator for query without TOP or ORDER BY

I have product sales by month temporary table.(subset of much larger historical table) I'm running query to find new product not previously sold in the previous months for a specified 6 month range. ...
3
votes
2answers
106 views

Unexpected gaps in IDENTITY column

I'm trying to generate unique purchase order numbers that start at 1 and increment by 1. I have a PONumber table created using this script: CREATE TABLE [dbo].[PONumbers] ( [PONumberPK] [int] ...
-1
votes
2answers
63 views

how can I delete records when my disk drive is full?

My data folder of SQL as filled up the space of my local drive. I have 150KB free space left in the drive. So I have to delete some records to regain space. But when I run the delete query it says ...
1
vote
3answers
58 views

Installing SQL server 2008R2/2012 on windows server 2012 box

we are trying to install SQL2k8R2 and SQL server 2012(Standard edition) on the windows server 2012 box (Standard 64 Bit). For SQL 2k8r2, i used slipstream method so as to bring set up to atleast SP1 ...
4
votes
1answer
65 views

MSSQL suggestion to create nonclustered index - on 2 columns, reversed

I have a query, where MSSQL 2012 suggested a create a nonclustered index as follows: CREATE NONCLUSTERED INDEX [<name>] ON [dbo].[tblUserCourses] ([courseID]) INCLUDE ([userID]) I created the ...
0
votes
0answers
25 views

Troubleshooting Memory-Pressure on SQL Server with 80+Gb RAM

I have a SQL Server cluster with 80+Gb of RAM and multiple production enterprise databases and applications running. We are encountering memory-pressure errors and are trying to troubleshoot what's ...
5
votes
1answer
187 views

Efficiently storing sets of key-value pairs with wildly different keys

I inherited an application that associates many different types of activities with a site. There are roughly 100 different activity types, and each one has differing set of 3-10 fields. However, all ...
0
votes
0answers
13 views

remove numbers from full text index

Is there any way to make SQL Server not full-text index numbers and currency values? According to sys.dm_fts_index_keywords the index has a very large number of unique values such as $ ...
1
vote
1answer
57 views

Upgrade to SQL Server 2012 [closed]

We are currently running SQL Server 2008 Standard edition, 4 processors / 16-core and 32 GB RAM. In the last 3 months number of requests per week raised from 11.1 millions to 72.1 million and the ...
0
votes
0answers
17 views

Data Warehouse for reporting - 6 Dimension tables,or 1 table and 6 views?

Problem: I have been asked to add a Dimension table to our data warehouse and create the ETL tasks to populate it. Lets call the table "DimInternalFault". The source table for DimInternalFault is ...
2
votes
0answers
42 views

SSRS - ERROR NUMBER:7357 while passing multiple value parameters using comma delimited string to stored procedure

I am working on a SSRS report to display the logins permissions in a set of databases on a specific server. the server, the logins and the databases are all parameters. NULL shows them all (logins ...
1
vote
1answer
72 views

My SQL Server table identity is incrementing way too fast

I have a table with pretty basic design: 1 primary key (id), 1 unique column (filename) (the filename of the file I am tracking) The primary key is an identity column (bigint) (I had to make it ...
0
votes
1answer
56 views

Why can't I get the available free space after shrinking?

My disk drive was full. So I deleted some records. Then I tried to shrink and it indicated available free space of around 5GB as indicated in the below image. Once I ran the shrink for data file, ...
9
votes
2answers
159 views

SARG cardinality estimate, why not full-scan?

Why there is no full-scan (On SQL 2008 R2 and 2012)? Test data: DROP TABLE dbo.TestTable GO CREATE TABLE dbo.TestTable ( TestTableID INT IDENTITY PRIMARY KEY, VeryRandomText VarChar(50), ...
1
vote
0answers
24 views

How Columns_update() Function Works in Trigger To Find Updated Columns Name

I am in confuse about trigger function Columns_Update(). i Have Basic Idea about this function that it returns a varbinary and it is use to get updated Columns in Trigger even i have Find The code ...
3
votes
1answer
54 views

SQL Server: UPDATE permission for table variable

SQL Server 2012 I have a maintenance script that I wrote for some of my operators. They are members of the fixed db_datareader and db_datawriter roles. Part of the script creates a table variable, ...
2
votes
0answers
47 views

Trigger/Event that fires when a Transaction finishes

I have a query that pulls from about 15 different tables. I am looking to materialize this into a table that stores it in xml/json. (To improve performance.) The problem I have is that these tables ...
1
vote
1answer
88 views

Failed to deploy the project. Try again later. (Microsoft SQL Server, Error: 27118)

I try to deploy new version of existing SSIS project into Intergration Service Catalog and getting error: "Failed to deploy the project. Try again later. (Microsoft SQL Server, Error: 27118)" ...
4
votes
1answer
85 views

How do I troubleshoot error 26: “Error Locating Server/Instance Specified”

I'm trying to configure TCP access to my SQL Server named instance (named SQL2012). I use management studio to connect to the database engine. I type in the server name: "tcp:localhost\SQL2012" and I ...
1
vote
1answer
51 views

Alter of stored procedure and execution performance

I would like to understand how an alter of a stored procedure without modifications can impact performance. I had a situation where a production environment had its database schema updated and a ...
4
votes
1answer
116 views

Find rows where data resets

So I have a table tbl where I'm trying to pull data that increments at a variable rate. I need to identify when the data resets so I can make a cumulative value. Here's an example table: ID VAL ...
-9
votes
1answer
132 views

Stored Procedure which INSERT, UPDATE, DELETE any table(for any table) where table name and its values are passed as parameter [closed]

Stored procedure which performed operations like inserting data to table, updating data, and deleting data of table where table name is passed by parameter. Parameters of stored procedure: table name ...
3
votes
1answer
29 views

How to change location of SQL Server 2012 database which is already configured with a merge replication

I have SQL Server 2012 configured with merge replication. currently the location of the replication database and the .mdf and .ldf files reside in C:\ drive. The C:\ drive is now full due to that ...