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)

5
votes
2answers
834 views

Symmetric difference operation in Transact-SQL?

I have always known about the UNION operator in SQL, but only recently discovered that there were other set operators, INTERSECT and EXCEPT. I haven't been able to find an operator that does the ...
2
votes
1answer
520 views

Command fails as CmdExec job but succeeds from console

I have a SQL Server 2014 Agent Job (Type: "Operating system (CmdExec)", Run as: "SQL Server Agent Service Account", Owner: sa) that executes the following command: powershell -ExecutionPolicy Bypass ...
2
votes
1answer
75 views
+50

SQL Logins in db_owner role on database cannot create stored procedures

I started locking down permissions in our test environment by removing sysadmin permission from generic accounts. The first account, used by several developers, is a local SQL login. I gave the ...
-2
votes
0answers
6 views

show invalid object name [on hold]

when this code runs on mysql database then fine but when this code run on sql server that showing error com.microsoft sql server exception invalid object name... private void ...
2
votes
1answer
38 views

How do I generate the ENCRYPTED_VALUE for CREATE COLUMN ENCRYPTION KEY?

I can make a certificate in the windows certificate store for the master column key easily enough with: $cert = New-SelfSignedCertificate ` -Subject "Always Encrypted Sample Cert" ` ...
5
votes
2answers
8k views

Importing a large column (8000 chars) from Excel using SSIS

I'm trying to set up a regular import of an excel spreadsheet that we get from a vendor. I'm using SQL 2008 R2 SSIS to import it into a table. The problem connection manager is an OLE DB connection ...
0
votes
1answer
24 views

Powershell invoke sqlcmd

How is it possible to execute a query as a applicationintent=readonly using powershell? I have an cluster alwayson environment. I want to call a procedure with invoke sqlcmd to the readonly database ...
0
votes
2answers
14 views

Capitalize only the first letter of a name

I have a table Vendors that contains a column VendorContactFname, which is written in lowercase. I want to capitalize only its first letter. I am thinking of concatenating an Upper on the first ...
0
votes
1answer
29 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 ...
3
votes
1answer
21 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
16 views

Relating 2 tables with possible wildcards?

I am having trouble getting the desired results when trying to relate the following tables. NULL in the following table essentially means that if it is not an exact match with another row then choose ...
0
votes
1answer
107 views

Error message is causing Error Log to grow out of control

The error log is growing like crazy - in one day it has reached 32GB. There is one error that keeps being logged. I have searched online for this error, however I couldn't find any helpful ...
0
votes
0answers
11 views

Create update Trigger

I'm trying to create a trigger that whenever a software package is installed on a PC, the person who installed the software needs to provide data for pack, tagnum, and instdate, and the softcost field ...
1
vote
1answer
26 views

Attach corrupted database

In a little C# tool, I use an a mdf file to save my data. The file became inconsistent, which isn't really a problem as there only a few changes since my last backup. Anyway, I would still like to ...
5
votes
2answers
212 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 ...
16
votes
4answers
570 views

SARGable WHERE clause for two date columns

I have what is, to me, an interesting question on SARGability. In this case, it's about using a predicate on the difference between two date columns. Here's the setup! USE [tempdb] SET NOCOUNT ON ...
3
votes
2answers
46 views

SQL Server - What access do I have during a transaction rollback?

As the title says, do I have read/write access to my entire database while it is rolling back a transaction? Or read/write access to only the tables that the transaction did not write to? What is the ...
4
votes
1answer
220 views

Partition by ascending and descending performance

I have a table, and for a given set of fields a, b and c, I need to get the first and last rows ordered by d and e, and am using ROW_NUMBER to get these rows. The relevant part of the statement is... ...
2
votes
1answer
39 views

Unknown System SPID blocking user SPIDs

We are performing a migration of 3rd party data and it is hanging. When we investigate we see SPID 38 (a system/background SPID) is the blocking user SPIDs trying to do the migration. Unfortunately, ...
1
vote
1answer
103 views

How can I speed up a stored procedure?

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 ...
0
votes
0answers
10 views

Can mirror and witness have same port number?

When configuring database mirroring, the mirror and the witness have generated the same port number. When I hit next, it errors out with: End points for instances for the same server must use ...
0
votes
0answers
28 views

best practice to remove leading zeros when writing data to a destination

I have a query like in my other question DBA Question with Source Code of Select statement And i want to remove the leading zeros of the customer Number (KUNDE) with the answer of this question ...
1
vote
2answers
37 views

I'm confused about High Availability/Always On

is High Availability something to describe Log Shipping and Always On ( replica )? is Always on something to describe Log Shipping and High Availability? I'm studying to get my certification 40-762 ...
5
votes
3answers
137 views

Cardinality Estimator Question after 2014 SP1 + CU4 Upgrade

We recently upgraded from SQL Server 2008R2 to SQL Server 2014 SP1 + CU4. After a couple of weeks, there were problems with execution plans not properly estimating row counts. The problem got so bad ...
0
votes
1answer
65 views

Identity column jump in sql server restart

I had many research on identity column jump in sql server restart. I know the reason why this happen as it explained here. I know the solution how to prevent this using either manually perform ...
1
vote
2answers
40 views

Join two tables using pivot combining two columns in one table

I have two tables in this format: Job_Skill ID_Skill Min_Job Idea_Job Max_Job ===================================== 1 0 0 1 2 0 1 1 7 1 ...
0
votes
2answers
22 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 | -------------|------------| ...
1
vote
1answer
21 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 ...
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
7 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'), ...
15
votes
5answers
341 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
13 views

error in parallel query execution-single row subquery returns multiple rows [on hold]

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
44 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 ...
11
votes
3answers
147 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
109 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
712 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
29 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
59 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
166 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
49 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
21 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
74 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 ...