Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
The results are in! See what nearly 90,000 developers picked as their most loved, dreaded, and desired coding languages and more in the 2019 Developer Survey.

Questions tagged [sql-server]

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.

2
votes
0answers
45 views

How to prevent SQL files opening new instances of SSMS 17.9.1?

Synopsis This is not a new problem and there are several solutions floating around on Internet. Problem When you double click on a .sql file, a new instance of SSMS is opened. I did not have this ...
2
votes
1answer
185 views

Table Fragmentation

If I execute the following: SELECT dbschemas.[name] as 'Schema', dbtables.[name] as 'Table', dbindexes.[name] as 'Index', indexstats.alloc_unit_type_desc, indexstats.avg_fragmentation_in_percent as ...
0
votes
1answer
34 views

How to get MSSQL Type definition from system tables?

If I have a user table type (like CREATE TYPE ...), how do I get to it's definition in the system tables? I know I can see it through SSMS through GUI, but I would like to have a query to retrieve ...
-2
votes
0answers
61 views

Tempdb expands without limit and remains blocked [closed]

I have a problem on a production server, this problem started a few days ago: the tempdb database expands out of proportion, until the disk is saturated. The server started to have this problem ...
1
vote
1answer
41 views

How to use single domain name to connect to multiple database servers?

We have multiple Windows server 2016 machines hosting single SQL server instance on each server. Every server hosts ~100 databases. We have a master table in which has a record for each database & ...
3
votes
1answer
61 views

SSIS : Null value for date keeps failing

I have a sequence container and in that sequence container I have select that is passing through data into a foreach loop container. The problem arises with one of the columns that's a date datatype. ...
0
votes
1answer
36 views

Setup Multiple SQL Server Tree Structure similar as RDC based on Enviornments

I want to setup multiple SQL Servers like a tree structure similar to Remote Desktop connection Manager, where we can setup multiple servers based on environments we support like Test and Prod.
-1
votes
2answers
28 views

How to convert DB2 database to Microsoft SQL database [closed]

Is it possible to convert databases from DB2 at Linux to Microsoft SQL at windows ? and what is the correct steps ?
-3
votes
3answers
60 views

if statement in a query [closed]

I am getting an error while running this code: Incorrect syntax near the keyword 'IF' I need the output through function only with table return . CREATE FUNCTION [dbo].[...
0
votes
1answer
29 views

how to write this query accordingly

i need this query to run against 20 instances from main server that have linked server to those 20 instances.i want to change the [@server] to the right instance via cursor and make the SELECT ...
1
vote
2answers
75 views

SQL Server Index and Statistics Maintenance performance question

I tried to use IndexOptimize stored procedure on a database with a very high number of indexes (> 250.000). The initial step where stored procedure collects data what needs to be processed takes hours,...
2
votes
0answers
30 views

TEMP Database is not showing Encrypted in TDE in SQL Server 2008R2

As per my knowledge, when we encrypt any user database in SQL Server through TDE, the TEMP database automatically gets encrypted. I have done TDE in SQL Server 2016, and when I check through SELECT ...
0
votes
0answers
15 views

SSIS DB issues when trying to run Package from SSISDB to getch ADO directoty users list

I am working on task that involves using Dev server to fetch ADO directory user information. I am connecting through SQL script i Script task and passing my User authentication. This is working fine ...
-1
votes
1answer
32 views

use a table as a variable [duplicate]

I was wondering if there was a way to set a table name in SQL to be a variable. I have a list of queries that hit the same table and didn't want to have to update them all so was hoping to declare ...
0
votes
2answers
25 views

sql server: get all the databases in which a login is being used

I'm creating a few migration scripts to generate a sql server database from scratch. In my '0005-create_login.up.sql' script I have something like: CREATE LOGIN [mylogin] WITH PASSWORD=N'xxx=', GO ...
-3
votes
1answer
39 views

Fastest Aggregate function when you have series of duplicate values

Frequently, I'll need to crunch down a table into a series of aggregates. And frequently, it will look like this. John, Smith, Houston, Texas, Order 55, Balloon, 99 dollars John, Smith, Houston, ...
-1
votes
0answers
25 views

Time out for a session with high ms/reads on C: drive

For one of our SQL servers we had time out notified from app: While troubleshooting we see that there was a spike in C:\ as ms/Read in that fraction of X min when time out happened the number for C:\ ...
2
votes
1answer
46 views

What are the potential issues after data or log disk failure?

Scenario: A client recently had an issue with an Azure VM where the (non-OS) 'premium' storage disks were erroneous removed without warning by Microsoft. This is the virtual equivalent to the hard ...
5
votes
2answers
90 views

Why is Query Store missing details?

This is slightly complex to explain, but very easy to reproduce. Short version: For about 10%-30% queries (by count) significant amounts of Query Store data is not available. I have looked at SQL ...
1
vote
2answers
44 views

SQL Server cursor and foreachdb loop

I want to create a cursor (without creating a temp table) to get all orphan users in my SQL Server instances. Here is what I've come up with: Declare @Orphan_users table (username sysname) DECLARE @...
2
votes
0answers
34 views

Connection to availability group listener on non-default port doesn't require port to be specified

I inherited some 2016 SQL Servers with an availability group and listener. The SQL instances are set up to use port 30001, and the AG listener is also set up to use port 30001. When I connect using ...
1
vote
1answer
74 views

Redundant fields in ORDER BY for efficient sorting?

Suppose I have a table SortTest with fields Data1, Data2, Sort1, and Sort2, Key1 and Key2 need to perform the following query: SELECT TOP 1 Data1, Data2 FROM SortTest WHERE Key1 = @key1 AND ...
0
votes
1answer
17 views

SQL Connector for Azure Key Vault - multiple key vaults and keys per instance

In SQL Server 2016 Enterprise Edition, is it possible to link a single instance to more than one Azure key vault / EKM provider? If so, how would I add an additional provider? Any security ...
0
votes
1answer
16 views

enable the data access for the current server - is this operation logged somewhere?

I have a similar question related to the same subject: security concerns over enabling data access for the current server BEGIN TRY PRINT @@SERVERNAME PRINT DB_NAME() PRINT 'enable the ...
2
votes
0answers
19 views

Why execution plan could be skipped from XE session?

Got an issue, similar to this one: Why does SQL Server Profiler trace not capture SELECT operator in execution plans?. There was an advice to use XE instead of Trace, but it does not help. Thing is, ...
0
votes
1answer
44 views

SQL - Update certain database records with a loop

I have a certain IDs in a table that I would like to change a column value from false to true. How can I do a loop that goes through the specific IDs given and changes a column value from false to ...
0
votes
1answer
31 views

Will a Transactional Replication break the LSN chain in my backups

I need to create a fairly up to date read only copy of parts of a production database for reporting purposes. I did look at log shipping to keep the copy up to date but this will break the LSN and ...
0
votes
0answers
20 views

Problem with sql configuration Manager which has only 32bits options

hello i am MasterTech and i am new in SQL DataBase Management. I am trying to connect my database over the internet and i watched a video which i tried to follow the steps but i quikly discorvered so ...
2
votes
1answer
26 views

Copying the value of ROWVERSION to another column in same row on insert

While pondering this question Is there a function like min_active_rowversion for Identity?, I thought about another possible solution for my problem - using ROWVERSION as primary key (and ...
5
votes
1answer
295 views

Must numeric JSON keys be quoted?

I've noted that key-value values may be unquoted when numeric in TSQL JSON strings, but it seems the key component must always be quoted. select 1, isjson(''), 'empty string' union select 2, isjson('{...
3
votes
0answers
61 views

Is there a function like min_active_rowversion for Identity?

I'm storing a queue of events in Sql Server with concurrent writers and I'm assigning a sequence number using an IDENTITY column. I'm willing to accept the gaps that might occur, but I would like ...
1
vote
1answer
36 views

Delete orphaned SQL Users

I have a Microsoft SQL Server that had a database called SQLDATABASE. It also had a user called SQLUSER. The SQLDATABASE was deleted but not SQLUSER. SQLUSER only belonged to SQLDATABASE. I want to ...
3
votes
5answers
414 views

Order table by two columns

I would like to order this table but I can not find a way to make it work. Can you help me? I have this table: I need this: I have a table where documents are listed, the first field is the id of ...
1
vote
1answer
44 views

Conversion of Row into Column

I'm in very weird situation and i have researched a lot but not able to find anything similar to this scenarios. Input looks something link this. Where Each patient has Primary Dx code, Admit Dx ...
0
votes
3answers
40 views

exec master..xp_cmdshell

Can anyone help me resolve the syntax in the following script? Here @Name and @DBname are declared above. exec master..xp_cmdshell 'echo 'alter login QUOTENAME(select @Name) with default_database = ...
2
votes
1answer
43 views

t-sql to create tables using a declared variable

I am attempting to create multiple tables in multiple databases using declared variables for the db_name, table_name , column_name and column_type. Is this feasible ? I have to create the same tables ...
0
votes
1answer
37 views

Unable to edit some records in SQL

I have a table we just transferred from MS Access to SQL Server 2016. It's a simple table for creating load numbers for shipping products. Using access we link to the SQL Server and we are able to ...
-3
votes
0answers
66 views

same database, same schema and data, same query but different execution plans [on hold]

I executed the same query against the same databases with exactly the same schema and data but it yields a completely different execution plan. Only difference I wanted to point out is in regards to ...
0
votes
0answers
18 views

What are SSIS ,SSRS and DB engine SQL server 2008R2 to 2012 IN PLACE UPGRADE steps and cautions? [duplicate]

I am trying to find, SSIS ,SSRS and DB engine SQL server 2008R2 to 2012R2 IN PLACE UPGRADE steps and cautions ? Thanks, DL
0
votes
0answers
34 views

HyperV VM Disk Perfromance on SQL Server with SSD SAN [closed]

Having real performance issues in on a production SQL server enterprise (2014). SQL is on a VM that is connected to a SAN SSD array via 10gb/sec connection. When one of the 3rd party apps we use ...
0
votes
0answers
18 views

Database design for multilingual tags

I'm looking for a simple database design for tags, where the tag-label can be translated (multilingual). I'm currently looking at Toxi, which is a very simple and clean way of creating a tagging ...
1
vote
1answer
68 views

Why Snapshots of the model, master, and tempdb databases are prohibited

I have been researching on snapshots and found at many places including Microsoft Snapshots of the model, master, and tempdb databases are prohibited. My question is why? Edit: Actually because ...
1
vote
1answer
37 views

Query does nothing except context switches

I run Adam Mechanic's WhoIsActive every so often on my server and log results to a table. I noticed a peculiar case while looking at some of the results and would like help interpreting what is ...
1
vote
1answer
24 views

SQL to find nearest location in one table from locations in another

I have a table like this, that contains a few million locations (POINT): CREATE TABLE NewLocations( [NewName] [varchar](20), [NewLocation] [geometry] ) I have another table like this, that ...
4
votes
3answers
1k views

Deadlock Graph and Interpretation, solution to avoid

I am supporting vendor based application, which is filled with blocking and deadlock. Deadlocks occur mostly involving two or three processes however I noticed yesterday, it was involving 9 SPIDs. ...
-2
votes
0answers
46 views

How many cores SQL server is using [duplicate]

Morning, I am planning on migrating a couple of databases from SQLServer2008R2 to SQL Server 2016 Enterprise and Standard Edition. I was ask by my infrastructure team to gather/ advised on the number ...
-3
votes
1answer
61 views

Setting Cardinality Estimator in SQL Server 2016 for performance & using old CE of SQL Server 2012

Post upgrade to SQL Server 2016, we saw a lot of I/O issues followed by slowness which affected the whole system. Upon investigation, we found that top queries are delete statements. Execution plans ...
0
votes
1answer
63 views

Are deadlocks always a symptom of design flaws or are they an unavoidable occurrence even in well designed systems with high data writes/reads? [closed]

A complete redesign proposal is experiencing deadlocks in its prototype. I am of the opinion that deadlocks are always the symptom of design flaws. I do not want to go into the specifics of our system;...
2
votes
2answers
29 views

Database Merging / Re-integration

I am a SQL Server DBA. I recently had the misfortune of losing a database due to drive failure. I was able to restore the data but for whatever reason, it stopped at a certain time and not all of the ...
0
votes
0answers
21 views

Find closed sub directory with full text search

I have a simple table with registered directory names inside a full text indexed varchar column. For example: 'c:\Dir1\' 'c:\Dir1\Sub1\' 'c:\Dir1\Sub2\' 'c:\Dir2\' 'c:\Dir3\' I now need to find the ...