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)

4
votes
6answers
8k views

Cannot remove unused filegroups

I wanted to remove some unused filegroups/files in a SQL Server Database but am stuck because SQL Server thinks the filegroups/files are still in use. Some background: We had some partioned tables ...
0
votes
1answer
3 views

A different number of AFTER INSERT trigger alarms in SQLite and SQL Server

I have trigger CREATE TRIGGER [Foo] AFTER INSERT ON [Bar] BEGIN UPDATE Log SET count = count + 1; END When i execute query insert into Bar (ColumnName) values ('SomeValue1'), ('SomeValue2'), ...
0
votes
1answer
7 views

Security concerns on SMTP in SQL Server

In my company, while I try to get the ports opened to connect to my SMTP server from my SQL Server running on Windows Server 2012, I get a reply saying that it is a security concern to open ports ...
1
vote
1answer
17 views

sql getting sum of calculated columns

How can I get the sum of TotalAmount as single value for example Product Name UnitPrice Quantity Discount% Amount SharingCost% ShippingCost CustomsCost TotalAmount pro1 ...
0
votes
1answer
11 views

Using Select Statement to get column name and table name from the same table

I have a table containing 2 Columns : Column1 (table_name) and column2 (column_name) like this : (Column 1) (Column 2) |Column_Name | Table_Name | -------------|------------| ...
2
votes
2answers
71 views

Are cursors/While loops the only way to make administrative changes to multiple databases?

I specifically want a programmable way to change the recovery model of all the databases on a server, and i know this solution can be applied to all 'Alter Database' commands. While I know SMO in ...
15
votes
5answers
327 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 ...
-1
votes
0answers
10 views

error in parallel query execution-single row subquery returns multiple rows

I am getting the error "error in parallel query execution-single row subquery returns multiple rows " sometimes while executing an sql query, while most of the times the same query runs without any ...
1
vote
2answers
38 views

SQL Row concatenation with XML PATH and STUFF giving aggregate sql error

I am trying to query two tables and get results like the following: Section Names shoes AccountName1, AccountName2, AccountName3 books AccountName1 The tables are: CREATE TABLE ...
10
votes
3answers
134 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
108 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
711 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
27 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
33 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
57 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 ...
4
votes
1answer
163 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
47 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 ...
4
votes
0answers
20 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
18 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 ...
1
vote
0answers
41 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
62 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
98 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
30 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
33 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
257 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 ...
5
votes
1answer
464 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
21 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
19 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 ...