All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag like sql-server-2016 if that is relevant to the question.

learn more… | top users | synonyms (2)

0
votes
1answer
112 views

execution plan question

I have this query running for long time (@P0 nvarchar(4000)) select emaildmo0_.Id as Id7_ ,emaildmo0_.CreatedDate as CreatedD2_7_ ,emaildmo0_.EmailAddress as EmailAdd3_7_ ,...
0
votes
1answer
103 views

Multiple Cascade Paths (Not really) - Sql Server

In my database I have a table with 2 columns referencing the same column in a separate table. I know this creates a race condition, but I have a check in place to make sure they don't point to the ...
3
votes
1answer
45 views

Execution plan for COMPUTE BY

I understand that the COMPUTE BY feature is deprecated. Out of curiosity I would like to know how the query processor executes a COMPUTE BY query. The execution plan does not contain any hint: How ...
2
votes
1answer
318 views

Update one column based on count of records of grouped value in another column

Given a table CREATE TABLE TABLE_ITEMS ( serial_num int null, item_group int null ); INSERT INTO TABLE_ITEMS VALUES (0,1), (1,1), (2,1), (3,1), (4,1), (0,2), (1,2), (2,2), (3,2), (4,2), ...
1
vote
0answers
2k views

view server state permission was denied

The strangest thing is happening on my SQL Server Management Studio. When I right-click any table, I get a "VIEW SERVER STATE permission was denied on object 'server', database 'master'. (.Net ...
2
votes
1answer
45 views

Local SSMS client is not connecting one particular server

My SSMS is not connecting to one particular server say X except X it is connecting to all the other servers. Could not find the root cause for this problem do any one have idea here? Guy sitting next ...
8
votes
1answer
453 views

sp_cursoropen and parallelism

I'm running into a performance problem with a query that I can't seem to get my head around. I pulled the query out of a cursor definition. This query takes seconds to execute SELECT A.JOBTYPE FROM ...
6
votes
1answer
1k views

Adding a local user account into SQL Server

I have a (developer) instance of SQL Server 2014 running in my machine. For quick background I'm on a small network like this: Workgroup: HOME Users HOME\PCA HOME\PCB HOME\PCC I'm HOME\PCA. Now ...
1
vote
4answers
130 views

SQL Server : auditing AD Users - how can we identity AD User account from AD Groups

I would like to identity which accounts on our servers belong to AD Users. Currently, I find a plethora of accounts from users that should in fact be part of AD Groups registered on the same SQL ...
1
vote
3answers
47 views

SSL to individual database

Is there a way to setup an SSL connection to an individual database, or does it have to be done at the instance level?
3
votes
1answer
794 views

Replace NULL with empty string [closed]

Due to precedence set within my organization, I need to replace NULL values in certain columns (roughly 15-20) with the empty string. While the solution comes quite easily in more traditional ...
0
votes
1answer
54 views

How would I grant select on a table being used in a different database to a user role in SQL?

I have a view that's selecting rows from a table on a different database. When calling this view from my application I get an inner exception thrown saying the application user is not able to access ...
2
votes
2answers
94 views

CLR/C# equivalent to XACT_STATE()

I am porting a set of T-SQL stored procedures to C#/CLR. In the T-SQL, we use XACT_STATE() to determine if an error requires rolling back to a savepoint or rolling back the transaction entirely. I ...
2
votes
1answer
66 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, ...
-1
votes
2answers
2k views

Deadlock from select statements

I am getting deadlock from 2 select statements. What is the reason for that? And how can I fix this issue? Deadlocked processes: 11278 Victim process: 5185 Lock type: Index/(Key) Both ...
2
votes
2answers
106 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. ...
0
votes
1answer
30 views

How do I figure TOTAL DURATION for specific group

I have a table with CARD # and REDEEMED DATE. I am trying to figure out the total duration (in minutes) for each CARD #. I am using Microsoft SQL Server 2014
0
votes
2answers
79 views

Extrapolating gaps in hourly data

I have data in the form of DateTime -- Status -- Duration Seconds 12:00:00, Available, 10800 15:00:00, Unavailable, 460 I need to translate that into DateTime -- Status 12:00:00, ...
-1
votes
2answers
27 views

Users unable to access Database

we have an SQL Server called sql01. On this,we have attached a number of databases, including the SSRS report server, which people can access. I have created a new database called ToolingSystem and ...
1
vote
2answers
60 views

Restrict Full access to certain User of Database

The scenario is the following: Given SQL Server Instance created and handled not by me Creation of Database and User on given Instance by me Question: To my limited understanding, the sa login ...
4
votes
1answer
695 views

Hiding a SQL Server vs Turning Off SQL Browser

Can anyone explain the difference between turning off the SQL Browser service and Hiding an instance through the SQL Server Configuration Manager? I've always assumed they do the same thing and have ...
7
votes
1answer
119 views

Two servers, different execution plans. Several orders of magnitude difference in run time

Two servers, Development and Live. Live server is an Amazon RDS SQL Server Web instance. Both servers have identical schema and data. There is a good spatial index on the geometry column. On my ...
2
votes
1answer
300 views

How to resolve foreign key references across different databases (iSeries and SQL Server)

I've got a system that has a DB2 iSeries database. We are rewriting a portion of the system and implementing it using SQL Server and Entity Framework. Originally, I was lead to believe that there ...
1
vote
1answer
46 views

MS SQL Server Incremental(differential) backup interrupted due to Full backup

I have a manual full backup happening every night, post which I am taking differential backup after every hour but after three to four differential backups a full backup happened through third party ...
4
votes
1answer
515 views

Service Credentials to use in AlwaysOn Availability Groups?

When using AlwaysOn Availability Groups does each SQL Instance need to use the same service credentials? We are setting up a new SQL2014 deployment and will be using AlwaysOn. If I have 2 SQL servers ...
2
votes
1answer
1k views

Running SSRS and SSAS from different server than the primary database instance(SQL Server Engine)

Right now both SSRS, SSAS and SQL Server Engine are on the same server. We are using 2008R2 Enterprise. We are thinking to upgrade to SQL Server 2012 or 2014. and We want put on separate serve the ...
3
votes
1answer
198 views

Excessive CPU use in vendor SQL Server DB

Background: I've got a SQL Server 2012 DB provided by a vendor so modification of queries and tables is limited. We do own the DB though, so we can add and maintain indexes. Indexes haven't been ...
2
votes
1answer
46 views

Update query could not be bound

I'm trying to do a simple update query and I'm getting this error: The multi part identifier "customers.customer_name" could not be bound Query: Update Suppliers Set City = (select s.city ...
1
vote
2answers
45 views

Custom ORDER BY

Are there other ways to use ORDER on my results? 20150507094958A DISPOSED 2015-05-07 09:50:00.853 Samuel MONEY 20150507094958A DISPOSED 2015-05-22 09:25:00.000 Jeff AUTOMOBILES ...
1
vote
4answers
91 views

Grouping Data Using Joined Tables with Certain Distinct Values

I am struggling to get my head around how to do this so please bare with me while I explain. We have an order system which records orders, order lines, items, users etc. What I am interested in in ...
3
votes
2answers
2k views

How to separate name string by the spaces in the name in T-SQL

I have a list of names, such as: Doe John James, Doe Jane and Doe Henry Albert Sr I need to split those out into the four component parts of the name (if the name has four parts) to put each of them ...
5
votes
2answers
11k views

How do I import XML to a SQL Server table?

Can anyone help me work towards fixing my solution to import my XML data into a SQL Server Table? I have done my research, but this task has been very hard for me to accomplish. Everything I have ...
1
vote
1answer
32 views

Index design question

This query runs for 60 seconds: SELECT mychecklis0, .... FROM dbo.MyChecklistStatus mychecklis0_ WHERE mychecklis0_.AppFormSubSectionId=@P0 AND mychecklis0_.ProfileId=@P1 There is an index created ...
1
vote
1answer
39 views

Problem locating rows with dynamic SQL

I'm new at SQL in general, so I'm have a few problems with some of my stored procedures I'm designing. My procedure takes in 3 parameters. A location (or table name basically, a varchar), a room ...
0
votes
1answer
106 views

SSAS (Analysis Services) 2014 - Cumulative Update 1 for SP1

I just recently applied CU1 for SQL Server 2014 SP1 (http://support.microsoft.com/en-us/kb/3067839) to my development workstation and one PROD server. Analysis Services continues to report itself as ...
4
votes
2answers
973 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
vote
1answer
986 views

How to do a SELECT inside of a PIVOT [duplicate]

I am trying to SELECT values from a column in my database in order to perform a PIVOT on those values without hard-coding them. SELECT * FROM (SELECT ItemType, [Status] FROM [EVAULTTEST].[dbo]....
0
votes
0answers
211 views

Property error when linking SQL Servers

I'm running into an issue linking SQL servers. The servers I have are: Server A - SQL Server 2008 R2 Server B - SQL Server 2014 I can set B as a linked server on A with no problem, but setting up A ...
2
votes
1answer
1k views

Missing Index Not Displaying In Execution Plan

I recently installed SQL Server Management Studio 2014 (Developer Edition), and I noticed that I am no longer seeing the missing index feature. What I have done: Ran the workload through Database ...
1
vote
1answer
27 views

Database engine installation failed

Being asked to implement an application for purchasing management, the database managing system which I have to install, is SQL Server 2008. I am having some issues while installing the main ...
1
vote
0answers
23 views

Create new server in SQL Server instance [closed]

In our office we have two servers (development and Test). I need to create a new server for practice purpose. How can do it?
2
votes
1answer
511 views

should we keep the log file or the data file on the fastest drive?

SSD = solid state drive We have a specific question about tempDB performance on server CRM01 (Marketing CRM server - running large queries): On CRM01, tempDB data and log files reside on the solid ...
6
votes
2answers
821 views

Does .bak file also contain backup of ldf file?

I am running MS SQL Server, which is holding mdf and ldf files of sizes 110 GB and 245 GB respectively. My question is, if I take .bak backup of said database, will it result in 355 GB (110+245) ...
0
votes
0answers
97 views

Cannot upgrade from SQL Server 2008 R2 SP2 standard edition to SQL Server 2014

When I run the upgrade wizard, it says "the specified edition upgrade is not supported". I have SQL Server 2008 R2 standard edition (SP2) and it has been added in the supported list on MSDN website. ...
2
votes
1answer
33 views

Pivots giving too many results

I'm trying to learn how this pivot thing works. I don't think I'm understanding it quite right. I just want to do a simple count for a bunch of item types that have some statuses. The table I'm ...
4
votes
1answer
143 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
97 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 ...
1
vote
0answers
35 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 $ 123,...
1
vote
1answer
66 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 ...
2
votes
1answer
85 views

Which version of AdventureWorks has a column called CustomerId on sales.vIndividualCustomer? [closed]

The SQL Native Client MOF Files here come with a Visual Studio Solution that queries adventure works for testing purposes. One of the first queries that application performs when it is compiled and ...