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)

10
votes
3answers
123 views
+250

Database stored procedure with a “preview mode”

A fairly common pattern in the database application I work with is a need to create a stored procedure for a report or utility that has a "preview mode". When such a procedure does updates, this ...
1
vote
1answer
107 views

Install SQL Server 2014 in windows 10 not installing database engine and some features?

Overall summary: Final result: Failed: see details below Exit code (Decimal): -2068054016 Start time: 2016-03-27 17:00:55 End time: ...
8
votes
3answers
710 views

SSMS Registered Servers with ReadOnly Application Intent

We are working on SQL Server 2014 POC testing with AlwaysOn and one of the user asked about saving the configuration of SSMS with ReadOnly Intent using the registered servers in Local server group. ...
1
vote
1answer
25 views

Pivot three fields into one category

I have data in this format Skill Min Ideal Max ========================== C++ 0 1 1 I want to display it like this: Criteria C++ ================= Min ...
2
votes
0answers
30 views

Can SSMS Object Explorer Details configurations be exported?

A number of employees make use of SQL Server Management Studio (SSMS). I have a configuration file for all new users for Environment (etc) configurations. I want to deliver a uniform view for each ...
0
votes
1answer
56 views

Query for a single unique record takes about 1.5 seconds

I have a simple table with a 13 byte unique key (indexed unique, non-clustered) and about 6 other small fields of no more than 10 bytes each. The table has about 8 million records. I have rebuilt the ...
3
votes
1answer
117 views

Why can a differential backup not be copy-only?

I have noticed that copy only backups does not allow for differential backups. What is the reason the copy-only backup option is disabled for differential backups?
2
votes
2answers
46 views

Completion of SQL Job triggers start of job on a different SQL Server

I want the completion of a SQL Server agent job to trigger the start of a job on another server. More specifically, I want my SSRS server to email a subscription report whenever an FTP/Import job ...
14
votes
5answers
318 views
+250

Group daily schedule into [Start date; End date] intervals with the list of week days

I need to convert data between two systems. First system stores schedules as a plain list of dates. Each date that is included in the schedule is one row. There can be various gaps in the sequence ...
4
votes
0answers
16 views

In SSMS, is “Navigate to” (Ctrl+,) of any use?

In standard Visual Studio 2012 or higher, IDE window called Navigate to... (Ctrl+,) offers all useful objects in scope of currently open project (see samples) getting increasingly filtered as I am ...
-3
votes
0answers
17 views

i need to know some advantages of field size [on hold]

Can you explain some advantages of field size. I have already tried to change the number of characters. This is because changing the number of characters reduces the amount of space wasted in the ...
2
votes
1answer
177 views

SQL Server 2012 - Dropping Snapshots takes hours

We recently did a SQL Server upgrade from 2005 to 2012. We have a nightly job that drops a snapshot of an active database. The snapshots gets created again later in the day. Before upgrading, in SQL ...
2
votes
2answers
12k views

How to fix “The OLE DB provider ”MSOLAP“ has not been registered”?

Here's the code I'm using to register a linked server to SSAS: USE [master]; GO EXEC master.dbo.sp_addlinkedserver @server = N'ServerAlias', @srvproduct = N'', @provider= N'MSOLAP', ...
13
votes
10answers
1k views

What are the top 3 performance Issues that you encounter with your SQL Servers?

I am a student from Fontys University in Eindhoven, and I am currently carrying out a series of interviews to help with the development of a SQL Server tool and I would like to get feedback from the ...
5
votes
1answer
73 views

Conditionally add an index to a temp table

I have a stored procedure that looks something like this: SELECT columnlist INTO #temptable FROM Table JOIN lotsofothertables WHERE severalconditions UPDATE anothertable SET column = ...
0
votes
3answers
62 views

Get all records from inner joined tables if second table does not have data

I follow below syntax by which I achieve the result. but it takes time IF NOT EXISTS(select * from #tblxyz) BEGIN INSERT INTO #tblxyz select NULL END Select a.* FROM tblabc a ...
0
votes
1answer
133 views

Return List of Database Users and Server Logins

How can I return a complete listing of all server logins and all database users across my enterprise? Need to include the remote server name, database name, AD account and SQL users.
1
vote
1answer
119 views

Can scheduled and continous replication configurations exist side-by-side on the same master/slave servers?

Environment We have a core sql server cluster. This cluster contains some databases that get replicated to a load-balanced sql cluster of currently 3 servers. These databases are replicated each 12 ...
0
votes
1answer
131 views

How to verify if a SQL Account is still used?

We have a SQL Server Login on a SQL Server 2008, and no one seems to know if the account is actually being used or not. It is assumed it is being used, as it is configured in a Linked_Server ...
0
votes
0answers
34 views

To find SQL Server is actually used or not?

I have an SQL Server 2012 and a 2008R2 running in my environment. I need to decommission the server if the server is not actually being used. I did go through this and this where connection count is ...
4
votes
1answer
61 views

When executing a command via xp_cmdshell what user issues the command to the system?

I'm executing the following command to stop a service on a remote server from within a stored procedure in SQL Server. ---------------- -- Stop Service ---------------- SET @Command = 'sc \\' + ...
1
vote
1answer
96 views

How can I speed up a stored procedure? [on hold]

Here is an interview question I just had. I would like to know what a good answer to this would have been and what the different options would be. We have a stored procedure that is taking 26 ...
1
vote
1answer
42 views

Unable to set user/group as principal on a proxy

I'm organizing permissions on our SQL Server instance. I want to stop using a SQL Server account, and instead grant permissions to Active Directory groups, and then add user and service accounts to ...
0
votes
0answers
27 views

Migrate SQL server data to new schema

I have a database that was originally created by a couple of people for a small business unit (about 8 staff and 10 customers) in MS Access. The original 'developer' was a non-geek who had been sent ...
1
vote
3answers
409 views

shrink database in always on availability

hi i was using this query for shrinking the database log file USE Tfs_CoE; GO ALTER DATABASE Tfs_CoE SET RECOVERY SIMPLE; GO DBCC SHRINKFILE (Tfs_CoE_Log, 1); GO ALTER DATABASE Tfs_CoE SET RECOVERY ...
0
votes
1answer
29 views

How to find the AD user that logged in to SQL Server using SQL Login [duplicate]

Some users are logging into database servers locally, opening SSMS and logging in to SQL Server using SQL logins to run queries. How can I find the AD user that logged in to SQL Server using a SQL ...
4
votes
2answers
6k views

Removing secondary data files. DBCC SHRINKFILE: Page could not be moved because it is a work table page

I have too many secondary data files (.ndf) created for tempdb. To remove the excess files, I need to empty the file (content will be moved to other files): DBCC SHRINKFILE('tempdbfile8', EMPTYFILE); ...
0
votes
0answers
15 views

MS SQL data synchronization question

I'm in charge of modifying an application which was intended to run Microsoft SQL Server Compact 3.5. The application must run on a local database during fieldwork when there is no network ...
0
votes
2answers
167 views

problems when importing csv files into SQL using SSIS when the columns are not consistent from quarter to quarter

I am using SSIS to upload a bunch of csv tables into SQLexpress. The csv files are quarterly data files. They are largely consistent but every couple of years a column may be added or an existing ...
0
votes
0answers
59 views

Trying to find a MIN() workaround for an indexed view

I'm trying to speed up a SQL Server query using indexed views and I have one slow element which I think is due to the use of GUIDs for the particular comparison column. The GUID can be mapped to an ID ...
0
votes
1answer
22 views

Restore a database with compression on a non-enterprise edition SQL Server

Is it possible to restore a compressed an object from backup without the Enterprise Edition? I'm getting the following error after a RESTORE DATABASE operation. Msg 3013, Level 16, State 1, Line 2 ...
2
votes
2answers
36 views

Define a proper index for a table

I have a table with millions of rows and something like 20 columns. This table have an unique identifier as primary key, but the most likely query is based on three varchar columns (col1 and col2 of ...
0
votes
2answers
32 views

How to separate values from one column into two columns in SQL Server [on hold]

I have this dataset as input: Name Contact No ------- ---------- Deepika 75865558 Deepika 207586558 Rnveer 746655558 Rnveer 946655558 vaibhav 2356841 vaibhav 6655558 I want to get ...
7
votes
2answers
256 views

SQL Server not using all memory

I have SQL Server 2014 with max memory set to 6GB (physical memory is 8GB). The Target Server Memory is sometimes 6GB and then drops back to Total Server Memory (approx 5.3GB, never reaches 6GB). I ...
4
votes
2answers
228 views

Ola Hallengren index maintenance - long periods of time between commands?

I run the Ola Hallengren scripts on all of my servers for index and statistics maintenance. When I look through the command log table, I notice long periods of time between a command ending, and the ...
0
votes
1answer
41 views

Same query same data different server different query plan

I have a query that hits a view, which references other views etc...several levels down. The query plan is a bit complex. I run the query on 2 different servers. On both servers, the number of rows ...
4
votes
1answer
462 views

Does a single rollback affect all transactions?

When I run the following script: begin transaction select XACT_STATE() select @@TRANCOUNT begin transaction select XACT_STATE() select @@TRANCOUNT begin transaction ...
-1
votes
0answers
24 views

How to configure table with BLOBS (type image)?

I am about to overwork a table storing BLOBs in an IMAGE column combined with those file's metadata during a database maintenance. Lot's of the BLOBS will be removed during this process. We are ...
7
votes
0answers
70 views
+100

Keeping (Partial) Backups Small when using SQL Server FILESTREAM

I have a database with nearly 1TB of FILESTREAM data that I don't need backed up (if the data was deleted it'd be recreated automatically in a couple of hours, so it just isn't important). Most of the ...
0
votes
0answers
20 views

SQL Server Job does not execute when scheduled, but does execute manually, how do I get it to run as scheduled? [duplicate]

I am running SQL Server 2014 SP 1 CU 5. The job did work in the past. The schedule is enabled. The account it is running under is an ADS account which has sysadmin rights and has delete rights to the ...
0
votes
0answers
18 views

Can't connect to Database in Network using MS SQL 2016

I'm completely new to MS SQL Server and trying to connect to a Database in my network. I can find it, but everytime I am trying to connect it says username or password would be wrong. What I have ...
0
votes
0answers
8 views

Is this work flow suitable for Amazon RDS?

I am considering to set up a MySQL database via amazon web services (AWS). Consider the following steps which characterize my workflow: There is a daily data inflow into several AWS EC2 instances On ...
15
votes
4answers
4k views

Anybody using HierarchyId in production? Is it reliable?

Is anybody using HierarchyId in real production with tables of reasonable size, more than a few thousand rows? Is it reliable/performant? So far I have not found anyone not affiliated with the vendor ...
1
vote
2answers
28 views

SQL Server — More efficient alternatives to repeating an (almost) identical subquery

I need to improve the efficiency of multiple subqueries within a WHERE clause. The context is a report generator on an existing product. The only input I can provide is the WHERE clause. I have 3 ...
1
vote
1answer
19 views

Constraint column value based on another column? SQL Server

i am trying to set my defaults column Y or N based on membershipType column. There can be only one Y in defaults column based on the 3 different value in membershipType column. Other than that, others ...
0
votes
1answer
28 views

Returning only the row with maximum value from related rows

In the example below how do I get the rows where the swap id is the maximum value in relation to the reservationId? I am currently joining multiple tables to get customer, reservation, room etc ...
0
votes
1answer
130 views

SSRS - Data missing when running report from server

I have an extremely strange issue with an SSRS report i'm working on. We use SQL Server 2012 availability groups and as number of groups and the nodes they run on has swelled, confusion on the part of ...
0
votes
0answers
22 views

Querying Special Characters in SQL server

A MS Access user is trying to query a table with special characters in certain columns. The symbols show fine in the DB with default collation, whereas they show differently in non default collation. ...
5
votes
2answers
73 views

What sort of exec requests don't have a sql handle? (sys.dm_exec_requests)

I have crafted a query against SQL Server 2012 that looks like the following: SELECT r.session_id, r.sql_handle, t.text -- more details excluded for brevity FROM sys.dm_exec_requests r CROSS APPLY ...
1
vote
2answers
103 views

Maintain Up-To-Date Copy of Very Large Table if Clustering/Partitioning/Moving Operation Needs to be Killed/Rollback

I've appended the following information in a previous post but I asked the 'why' instead of the 'how'. So here's the question... SQL Server 2014, CU6. 4.5 TB database, Simple recovery. The most ...