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)

3
votes
1answer
687 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
53 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
84 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
65 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
29 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
78 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
58 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
645 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
113 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
289 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
468 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
194 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
88 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
10k 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
100 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
861 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
871 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
192 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
22 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
487 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
731 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
92 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
140 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
86 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
33 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
65 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 ...
-3
votes
1answer
60 views

SQL Server questions (for masters) [closed]

How to rename sa to new_sa in SQL Server 2000? How to do and what is the impact SA rename impact in SQL Server 2005, 2008, 2008R2, 2012, 2014, Express Edition How to recreate sa login if deleted How ...
5
votes
1answer
79 views

query to determine forceencryption state

I'm trying to confirm whether or not force encryption is currently set and/or if the current connection is secure or not. Is there a SQL query I can run that will give me that information or do I ...
2
votes
2answers
64 views

Trigger Gives an Error on Multiple Rows SQL SERVER

I have a problem when I create a trigger which is giving me this error: Msg 512, Level 16, State 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <...
1
vote
1answer
421 views

SET ANSI_NULLS at the database level versus setting it at the table level

I have generated schema-only script for my sql server 2008 R2, here is part of it:- USE [master] GO /****** Object: Database [****] Script Date: 03/25/2015 12:17:09 ******/ CREATE DATABASE [****] ...
0
votes
1answer
41 views

Unexpected SELECT permissions

My user WebApp seems to have SELECT permissions on every table in every schema and I can't figure out why. WebApp is only in the Public role. Public doesn't own any schema. Explicit permissions ...
5
votes
2answers
433 views

Optimize a SQL query

I have to select 2 values that were less that the given id and greater than the given id. I have tried this query but is there any better way to do it Fiddle Sql Begin declare @rootValue int ...
1
vote
1answer
135 views

What is the best SQL Server field type to cast to when storing a UNC path stored in a sql_variant field?

What is the best choice of field type to cast to when storing a UNC path in a sql_variant in Microsoft SQL Server 2008? It appears that I could store up to 4000 characters according to Microsoft ...
3
votes
2answers
104 views

How to create a custom map report

not sure if this is the right forum. But just wondering is there a way one can make a map report out of an image or a map made on excel. I have the data and I've looked around and found that its ...
0
votes
1answer
25 views

Access a DB through Another DB ONLY

I have a DB [MainData] with the following data in [dbo].[Table1] Customer Value1 Value2 -------- ------ ------ CustA Foo 1 CustA Bar 25 CustB Foo ...
2
votes
2answers
914 views

Creating a stored procedure and adding date manually to filename

I am new to SQL administration and have been tasked with creating some nightly jobs that send an email with certain details contained within a spreadsheet and so far I have the following: EXEC msdb....