Questions tagged [sql-server]

All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag, like sql-server-2016, since it is often relevant to the question.

Filter by
Sorted by
Tagged with
-2
votes
1answer
34 views

Select the site where the employee have reported more compare to other sites

I have 4 columns in my table. emp_name | emp_id | site | date_of_work blhf | 2323 | NAM | Jan-01-2020 blhf | 2323 | NAM | Jan-02-2020 blhf | 2323 | NAM | Jan-03-2020 blhf | 2323 | NAM | Jan-04-2020 ...
6
votes
1answer
156 views

SSRS error “something went wrong please try again later” in web portal

I have a brand new installation of SQL Server Reporting Services 2019 on top of MSSQL 2019 Enterprise CU1. SSRS has been installed and configured using default settings, apart from the service account ...
1
vote
1answer
34 views

Adding a date and the time portion of a datetime2 together [duplicate]

I have a date field [load_date] and a datetime2 field [load_time] that I want to combine into a datetime2. I want to combine load_date with the time portion of load_time and create a datetime2. It's ...
5
votes
1answer
890 views

Why am I getting nondeterministic results with the deterministic function STDEV()?

This is type of query I'm trying to run: WITH CTE_Ordered AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY PartitionField ORDER BY DateField) AS PartitionRowId FROM SourceTable ), ...
0
votes
0answers
69 views

String or binary data would be truncated, with an nvarchar(max)

I'm facing a well known error, but this time I cannot figure out the problem. I'm working with SQL 2014 and I need to replace some values inside an NVARCHAR(MAX) column. To do this I retrieve the ...
0
votes
2answers
64 views

Are key lookups from non-clustered indexes always slower than a second query that does the lookup?

I've noticed in my system, whenever a non-clustered index is used in a query that has to also do a key lookup to get the additional fields being selected, it's faster for me to instead do two queries. ...
0
votes
1answer
37 views

Query for: view users and permissions (roles) from root security folder (MS SQL)

On the Internet I found a lot of scripts how to display users and their permissions under a particular database (Security folder under table) - for example sp_helpuser. Currently, however, I need to ...
0
votes
3answers
42 views

Side by Side upgrade and migrate SQL

Here is the story: We want to do side by side SQL upgrade from 2016 to 2019 with a new windows of 2016. My SA wants to robocopy all drives except drive C from the old server to the new server. Do I ...
0
votes
0answers
26 views

What Visual Studio version should I use when I have SQL Mangement Studio v18.3.1 already installed?

I really like using SQL Server Management Studio v18.3.1 but I want to be able to step through code for troubling shooting. Since this version of SSMS does not have the "Debug" button or "Debug" pull-...
3
votes
2answers
81 views

Regarding Sql server 2014 express edition

I need to know if adding another data file in a database (let say MYDB) in express edition will have another 10 GB size or 10 GB will be the total size of all the data files we add in a database. ...
-1
votes
0answers
20 views

Services with min date having different set of dates

I have a customer with many services with different dates. | Customer ID | Service Date | Service Id | +-------------+------------+---------------+ | 1 | 2012-04-30 | 1 | | ...
2
votes
1answer
35 views

Error while creating Extended Events on 2008 SQL Server

I am trying to create an extended event to capture the login information of my server. While creating an extended event on SQL 2008 using the below query CREATE EVENT SESSION [SA_Monitor] ON SERVER ...
0
votes
0answers
28 views

FIll missing history records

I have the following issue: I have two tables Article id state Guid int and Article_history id state datechange Guid int datetime First table store porduct information, second ...
1
vote
2answers
398 views

Downgrading SQL Server database and moving it to another computer

I have created database on my computer using SQL Server 2019, and now i want to move it to another computer that has SQL Server 2014 installed on it. Do I have to download SQL Server 2014 and ...
0
votes
2answers
44 views

MSSQL NULL's Between specific dates

I have a TSQL script that counts transactions for companies based on all approved, declined and failed trans in the previous month(s). I also show companies that have 0 approved, declined and failed ...
0
votes
0answers
38 views

How to compress a query to get distinct COUNT and SUM aggregate information on joined rows with sub-queries in Microsoft SQL

I have a query that counts some incidents that happen based on joining some groups of companies and regions. SELECT RC.RegionId, RC.CompanyId, CC.Name, Quantity = SUM(CASE WHEN II....
8
votes
1answer
231 views

How is the new system stored procedure sys.xp_delete_files different from sys.xp_delete_file?

SQL Server 2019 introduced a new (and undocumented) system stored procedure: sys.xp_delete_files What, exactly, does this new extended stored procedure do? How does it differ from xp_delete_file ...
0
votes
0answers
15 views

FTIndex in MSSQL Server remains fragmented until I shrink the file? Reorganization stalls?

Well, this is more of an intrigue - something that's been fixed but struck me as odd because I don't understand SQL server as well as I should. Situation: We have a fairly large database (3TB) that ...
0
votes
2answers
32 views

Order by date then Group By by Name / Type before proceeding with the rest of results

I have a data like this that came from two tables. FirstName LastName LeaveType LeaveDateFrom LeaveDateTo Jane Doe VL 2019/01/02 2019/01/02 Joe Armstrong ...
0
votes
3answers
65 views

MS SQL 2008 R2 - How to use a production database through dev/test environments or how to copy a SQL schema and all its data to another

We currently have 3 Windows Server 2008 R2 VM identically build for Sage x3 v6.5, one for production/formation/budget database, a second one for QA (qualification) and a third one for dev & tests. ...
0
votes
1answer
23 views

Pattern for copying hierarchical data from one table to another?

Imagine a table with three columns: an ID primary key, a nullable ParentID referencing the ID column on that same table, and a TreeID to indicating the records that make up a particular tree. A basic ...
0
votes
4answers
56 views

How to visualize joins between inner queries to their respective outer queries?

I seem to always have trouble visualizing what the meaning is when an inner query is joined to it's outer query. Here's an example: SELECT NM.MovieId, NM.MovieTitle FROM NinjaMovies AS NM WHERE ...
0
votes
0answers
25 views

Why would sys.dm_exec_requests show a query in Database A blocking a query in Database B when neither query references the same tables?

I have a query I use in-house to monitor slow running processes in the data layer. It essentially looks at a few fields from the following system tables and views joined in the following way: SELECT ...
5
votes
2answers
323 views

What is controlling SQL Server's page file usage? SQL Server or Windows?

If Lock Pages In Memory is not enabled and SQL Server data is paged out to the page file does SQL Server decide what data gets paged out? Or is this handled by the OS? If Windows is handling this ...
1
vote
1answer
50 views

Nested stored procedures with use of transactions

I have a question regarding this tsql-code found in this thread regarding "nested transactions" (unfortunately my reputation is not large enough to comment in that thread): In what cases a transaction ...
0
votes
0answers
54 views

How to stuff together earliest date and or text

I'm trying to write a query and output to an excel file which outputs all of a members mortgages into a cell, but grabbing their earliest maturity date first. Table Creation CREATE TABLE mortgages ( ...
3
votes
1answer
123 views

Running sys.dm_db_index_physical_stats in parallel

I am trying to get fragmentation information on a few tables which are over 500 GB and I am using the ...DETAILED option of sys.dm_db_index_physical_stats. I am doing this on a restore copy of the ...
0
votes
0answers
35 views

SQL Server Mirroring Many DBs

We have two identical servers running SQL Server 2017. Let's call them A and B. A is hosting 12 databases and B is hosting 6 databases. Each server is synchronously mirroring its DBs on the other ...
1
vote
2answers
31 views

SQL Server Logon Auditing and Reporting the data from a DB Table

Is it possible to configure a SQL Server database to write audit information for logons (success and failure) to a database table that can be queried? The requirement is to be able to generate a ...
1
vote
1answer
48 views

SQLPackage resets database recovery model

I am using SQLPackage.exe with database publish profiles to deploy database changes to DEV and QC instances. I have databases in simple recovery mode. But when I deploy changes using SQLPackage, it ...
0
votes
0answers
37 views

How should I design a multi-purpose table?

Let's suppose I have the following schema: Table A has a 0 to N relationship with table B Table B has a 0 to N relationship with table C Each item on A, B or C may have comments from multiple users (...
0
votes
1answer
23 views

Microsoft Master Data Services (MDS) Migration | 2017 to 2019

We're in the process of upgrading our MDS instance, from 2017 to 2019, by means of a migration. I can't find any good documentation online on how to do this in an efficient way. Has anyone performed ...
0
votes
1answer
32 views

Is each query batch in a dynamic SQL statement executed sequentially or can it be parallelized, when using sp_ExecuteSql?

I have about 500 databases that are all structured the same but the data is unique in each database. Sometimes I write dynamic SQL statements to hit all (or a subset) of those databases and will ...
0
votes
1answer
41 views

SQL Agent using domain user for Jobs with TSQL

I have tried every option but i still cant find out why my sql agent is using a domain user when executing job steps with TSQL and keep getting the following error: Executed as user: RESOURCE\...
1
vote
0answers
17 views

Migrating TDE Protected Database - Cert Migrated, Manual Copy Works, But Not Copy Wizard

I'm attempting to migrate several DB from an SQL 2014 to 2016 server. They all have TDE enabled, and are protected by a certificate. I successfully copied the certificate, and I am able to manually ...
-1
votes
0answers
35 views

Recover Lost or replaced database

I have got in a big trouble ! I was trying to restore a database in an empty one. I put the restore files in D directory of Windows, then create a new empty database and tried to restore that files in ...
1
vote
0answers
41 views

Repro for missed rows during Allocation Order Scan + Page Splits

In Read Uncommitted, an index scan can execute as an allocation order scan. If a page split occurs during the scan, it is possible that a row will be read twice if it moves to a later position in the ...
0
votes
1answer
49 views

SQL Record Transfer from one db to another instance having same table structure

I want to move data from server db table to local db table but need to make sure that the Ids of records won't change while copying from main Server db table. like The issue I am having is when I ...
0
votes
1answer
17 views

Wrong result for FileTableRootPath Function of SQL Server

I just restored a database backup and now the result of the function FileTableRootPath() is incorrect. Where is the place that I can change to change the result of this function?
1
vote
1answer
63 views

Can I create an AlwaysOn replication of a DB where some tables don't have a primary key?

Using SQL Server 2016 and I have a 3rd party application that uses a large database on my SQL Server for data storage, processing, everything. I have a BI team that needs access to this data to run ...
1
vote
1answer
64 views

For a complex query how do i increase the time SQL Server searches for the best execution plan

Our system generates SQL with a very large number of tests. I suspect on some occasions it is taking too long to find the ideal execution plan. So SQL just picks the best plan it has found so far ...
0
votes
1answer
46 views

How can I keep the user permission on a table that is dropped and recreated? [duplicate]

I drop, create and bulk insert a table weekly for a user, but the user's permission is removed from the table each time I do that. I right click on the table name and add them each time. Is there a ...
0
votes
2answers
36 views

Is it possible to tell if a database has AUTOMATIC_TUNING switched on?

In SQL Server 2017 is it possible to tell if automatic tuning is switched on? This relates to tuning of the plan. So the database automatically monitors itself and if the plan gets worse it goes back ...
-1
votes
0answers
39 views

I have two triggers, after insert and after update, they are populating 'null' rows in the trigger table

I have two triggers, after insert and after update, they are populating 'null' rows in the trigger table My Trigger Code is USE [KFC_Japan_Utility] GO /****** Object: Trigger [dbo].[trgAfterInsert]...
0
votes
0answers
44 views

QueryParenthesisExpression, is it the correct syntax, or old syntax?

Is there a difference between using Insert tableName (ColumnName) Select ColumnName From Table and Insert tableName (ColumnName) (Select ColumnName From Table) I found it in some cases, and didn'...
2
votes
1answer
127 views

Freeze I/O on purpose like a vss snapshot

I want to freeze the I/O of a SQL Server instance like the shadow copy does. The result should look like this: 2020-01-19 23:11:40.90 spid73 I/O is frozen on database prod. No user action is ...
0
votes
3answers
41 views

Does SQL Server 2017 Express install on Dual CPU server?

A customer wants to rent a dedicated Server for their site. They do not need more than SQL Express 2017, for the time being, but they have been offered a dual Xeon system. Does SQL Express install ...
0
votes
1answer
63 views

Is “SQL Server Auditing” what I'm looking for?

Man... this feels like a broad question, so apologies in advance. The word "auditing" seems to have some depth in the DBA world, and I may not be asking the right question when I google. I'm trying ...
0
votes
1answer
33 views

Azure Sql Server VPN connection

I saw a couple of questions already submitted 1 or 2 year ago, without a clear answer. I'd like to know if I can connect my private Network, or whatelse to an Azure Sql Server. Point-to-Site VPN I'd ...
0
votes
1answer
47 views

SQL Server Automate refresh of one table only from one environment to another

I'm looking for a best practice/option to refresh one table only from Prod to Staging environment. Technical Details: The table in Source is loading daily with new data. The table in destination ...

1 3 4 5 6 7 548