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
0
votes
1answer
36 views

Create limited admin role in SQL Server that cannot alter row level security

I would like to know if there is a practical way to create an admin role in SQL Server that can essentially do anything except alter row level security policy. It is simple enough to create row level ...
1
vote
1answer
32 views

Subtracting With GetDate()

I am trying to convert a postgresql query to ms sql query. This is what I have in postgresql round(cast((Current_Date - emp.hiredate) As Decimal)/365,1) now I thought it would be a simple ...
0
votes
2answers
41 views

Performing Sum in Case Statement

I am trying to get a sum inside a case statement, but I am getting the error Cannot perform an aggregate function on an expression containing an aggregate or subquery This is my query syntax ...
2
votes
1answer
42 views

Finding stored procedures with XACT ABORT ON OR OFF

I am trying to troubleshoot blocking problems related to active open transaction with sleeping status and probably have found issues related to XACT ABORT option missing or as OFF read from many blog ...
0
votes
1answer
22 views

Copy all agent jobs to separate files based on date and keep five copies

Looking for a crazy script to run after database backup ... Want to look at all agent jobs and if modified date is equal to today save it to a shared drive, if not skip. Also want to delete the ...
3
votes
1answer
60 views

Is Memory Grant size 'cached' for subsequent executions of a cached plan?

I hope the title of the question is clear. I am a DB developer as one of my job roles. I recently asked a question regarding index statistics and I have spent a lot of time reading about statistics, ...
1
vote
1answer
29 views

Audit logins and logouts [duplicate]

I need to trace and monitor client login connections (successful and failure) to our important SQL servers (20 MSSQL Server). What method do you recommend? Note: I think enabling "both failed and ...
1
vote
1answer
231 views

Job waiting on CXCONSUMER

We have got a procedure on SQL Server agent jobs and this job started to hang on transaction for many hours with wait stats CXCONSUMER and never complete. The problem is after we noticed that it was ...
-1
votes
0answers
35 views

Select Max from individual JOINs

I was wondering whether someone could help me out with the following query. I am looking to select the max process id from the same table but with different joins (mentioned in bold) SELECT * FROM [...
0
votes
1answer
61 views

SQL Server take an unencrypted full backup

I have to take an unencrypted full backup of a database so that it can be sent over to other team after which they can restore it in their own environment. The other team has a different environment ...
0
votes
1answer
26 views

Migrate servers involved in replication

We are planning to migrate two servers with MSSQL 2012 servers with configured transactional replication. New servers will have new names, ip addresses and MSSQL 2014. Subscriber is used for reporting ...
2
votes
1answer
54 views

Are stored procedure query texts stored in any tables?

I don't readily see anything in the sys schema objects, other than the DM_EXEC_SQL_TEXT function that requires a plan handle, but the procedures I'm trying to get their query texts from aren't ...
3
votes
1answer
110 views

Exactly same query and plan but different duration and total reads. I Know parameter sniffing but

I know what is parameter sniffing but this seems different, I have this simple query: select * from A400RDATA This table has only a cluster index and approximately 25000 rows. The query usually ...
3
votes
0answers
174 views

In the context of another principal what permissions hinge on the identity of the database owner?

By "database owner", here, I mean the principal whose SID is sys.databases.owner_sid. This answer to the question of best practices for database ownership includes the following: The answer here ...
0
votes
0answers
23 views

Using WSUS to Patch SQL Server

So, it's been a while since the discussions about patching changes to SQL Server by Microsoft in these links (WSUS for SQL server patching & https://www.brentozar.com/archive/2016/08/windows-...
0
votes
1answer
43 views

Same session ID(with same SQL text) but 3 different connection ids

I was listing out all sessions open in my sql server 2016 production server. I have issues with slow performance and i have been investigating it. I see that i have several same sessions(session_id) ...
0
votes
1answer
90 views

What are the risks if you rename a server that hosts a named instance of SQL Server (2005) and skip updating the system metadata?

My apologies as this is the last SQL 2005 instance we have and it's pretty urgent or I wouldn't go through all this trouble asking. Questions What is the risk to leave things as they currently are, ...
0
votes
0answers
29 views

SSIS OData Package Fails on SQL Server 2017

Awhile back, I created several SSIS packages, in SSDT 2015, that were deployed to a SQL Server 2016 SSIS instance. They were scheduled with agent jobs and run using basic auth. Currently, in both ...
1
vote
1answer
46 views

The best practice for SQL + NoSQL database design [closed]

I’m in process of designing database architecture for my diploma project and I have second thoughts about different solutions. I know and understand all my objectives can be done within relational ...
13
votes
2answers
2k views

Why is database ownership by groups not allowed? Which user should own a database?

Per microsoft "ALTER AUTHORIZATION for databases for SQL Server": Requirements for the new owner: The new owner principal must be one of the following: A SQL Server authentication ...
1
vote
3answers
34 views

How to quantify or locate command timeouts in Azure SQL

I am trying to if confirm query timeout in Azure. Our frontdoor is reporting a 503 response at 30 Seconds response, as its the SQL server default value, I am trying to confirm if these are indeed ...
5
votes
1answer
244 views

SQL Server Deadlock between two selects and one update on one table

Our application encounters a deadlock every now and again (about once a week). The main culprit seems to be a query with two selects. One of them is to fill a temp table for performance reasons, the ...
2
votes
1answer
67 views

Breaking point for SQL server database based on connections from app to SQL server [closed]

We have been asked to confirm on some capacity test for one of our SQL server. Application team is building their new app servers with better infrastructure to connect to SQL server. Currently with ...
-1
votes
2answers
64 views

Sql Server roles [closed]

Recently I was asked question - what is the difference between Standard Server roles and Fixed server roles. I know Fixed Server roles include: Sysadmin, Securityadmin, etc. I am not sure about ...
0
votes
3answers
34 views

Handling .bak and .dif files to optimize storage usage

I'm working with a number of different servers that are severely lacking in backups. I want to implement a back up plan using simple mode with full backups weekly followed by a series of ...
0
votes
3answers
94 views

How did you split strings before string_split()

I have some strings I need to split on a SQL Server 2014 instance. I would normally do it something like this: declare @bk nvarchar(1000) set @bk = 'SIS5.0~person_id~696969' select value from ...
1
vote
2answers
74 views

Is it normal to UPDATE STATISTICS this frequently? [duplicate]

I have a stored procedure that processes data from a source table and is executed many times (about 20,000 executions) throughout the day. I believe it's getting choked up sometimes, due to parameter ...
2
votes
2answers
162 views

One database listening on two database names

I know this is a sort of odd request. I myself have never had to even think about a scenario like this until today. I'll go straight to the point to make the question easy to read. I suspect the right ...
0
votes
3answers
63 views

Is there a way to use a Case statement to read from one field and change another field? [closed]

I have a simple MS SQL database created from an import from a CSV file. I need to check a field to see if a word is in that field, and if it is, change the contents of another field. Like this: When [...
5
votes
1answer
183 views

After SQL restart, CTE causes invalid object name error for a short period in SQL Server 2019

We have some SQL code that has been in production for some time, in a SQL Server 2016 database; but it is raising an error in a SQL Server 2019 database for the first hour or so after restarting SQL ...
0
votes
1answer
37 views

Improve CASE select performance on view creation

I am new to SQL, and the below view query is taking a long time to return values because of the case statement, can someone help in identifying how I can speed things up? CREATE OR REPLACE FORCE VIEW ...
0
votes
0answers
29 views

How to configure transactional replication for managed instance as a publisher and a subscriber for two different sql server at the same time?

The scenario is such that a subscriber is subscribed to transaction replication based publisher (distributor running on publisher). I then want this subscriber to be a publisher to another subscriber....
-1
votes
2answers
46 views

What are the differences between a table type and a table variable in MS SQL [duplicate]

Are there differences between a table variable and a table type in Microsoft SQL Server? Or is a table type just a table variable that is defined once for broad usage?
0
votes
1answer
39 views

Interpret the CPU usage in SQL trace file

During our performance evaluation, we run one load simulation with SQL trace on. And while analyzing the trace file I want to calculate the CPU usage. How to do it? (Please refer the screenshot ...
1
vote
1answer
43 views

How to compare and MERGE two SQL Server environments (not just databases)

Many similar questions were asked before, and mostly about comparing database schemas and data. That's all fine, but what about all the other SQL Server objects in the system overall? This is the ...
0
votes
1answer
60 views

how to find min max of sum?

hello everyone i try to find minimum and maximum value of SUM(units_sold) i test some ways but i can't find best solution please help me ! SELECT TOP 1 dp.product_name, SUM(fs.units_sold) AS ...
0
votes
0answers
21 views

What happens to the transactional replication if azure sql Vm(publisher and distributor) is stopped and restarted from portal?

I am a newbie...I have azure sql vm and sql managed instance. I have setup transactional replication between them. Now if I stop the vm from the portal will the replication still continue after ...
1
vote
0answers
34 views

why does datetime not change in this dbfiddle?

dbfiddle Code: CREATE TABLE mytable (id_log int, dt datetime); INSERT INTO mytable VALUES (1,getdate()); INSERT INTO mytable select id_log+1,dateadd(HH,2,getdate()) from mytable; INSERT INTO ...
0
votes
1answer
28 views

Copy last modified files from network path into SQL server box and rename them using Agent job

I have to copy latest backup from a network path and bring that to my SQL server physical drive for restore, I have to do that using agent job. This is what i am doing. Created a SQL server agent ...
0
votes
1answer
21 views

SQL To Return True False base on condition where it either matches the condition or is completely null

I have written an SQL query which I am thinking might not be right. I have Table A(Column A_PK, B, C, D), Table B(Column A_PK, C_PK), Table C(C_PK,..) Table B is associative entity for A and C. i,e ...
0
votes
1answer
29 views

Performance impact of a group by function call

I have the following query, which for simplicity sake I use the simple example format below: SELECT col1, col2, dbo.fun3(col3) GROUP BY col1, col2, dbo.fun3(col3) This took 30 seconds to run and ...
0
votes
1answer
29 views

Update query race condition

Consider this simple query UPDATE [SomeTable] SET Value = Value + 1 WHERE Id = 1 For almost any programming language this has a potential risk of race condition if it is executed by more than one ...
1
vote
2answers
59 views

SSMS How do I open a new Query Window using Same Connection Instance of current Window

Coming from Oracle I have discovered that SSMS connects slightly different than Oracle's SQL Developer. Oracle you click ONE connection and then all tabs you open then use that Connection. SSMS ...
1
vote
1answer
49 views

Storing linked server in stored procedure variable

I have following setup: Server 1: CGENMONOSAMP01 Server 2: CGENMONOSAMP02 I have Server 2 linked to Server 1 and link works, I can do queries like this on Server 1: SELECT * FROM [CGENMONOSAMP02]....
-1
votes
0answers
53 views

Query optimization (Index spool)

I have a query that need to be optimized. You can see the query plan here There you can see that 85% of the cost belows to an Index Spool but I cannot find the way to optimize it. There is a ...
1
vote
1answer
50 views

sql server in docker using 15gb of disk space [closed]

I'm following the instructions here to create my own docker container containing a sql server instance with databases and data configured. I've added two databases, a few tables containing at most ...
1
vote
1answer
35 views

Elastic queries across subscriptions in Azure SQL

Is it possible to run cross-database, actually cross-subscription, in Azure SQL? I have three subscriptions (Dev, Test and Prod) and I need to need to keep Test up to date with Prod. Unless I'm not ...
0
votes
1answer
34 views

How to get one row out of many rows with same column id

I have a use case in which I have three tables. Courses Table Course_ Id Name 1 MathMatics 2 Physics Teacher Table Teacher_ID Teacher_Name 1 AAA 2 BBB TeacherVsCourses ...
0
votes
2answers
88 views

SELECT last date from multiples values

I have this table: I want to make a query that allows me to show the last value in fecha_gestion grouped by NitSec. It would be something like this: SELECT id_log, fecha_gestion, nitsec FROM table ...
0
votes
2answers
41 views

Copy SQL database with encryption from one Azure subscription to another

I need to copy an entire Azure SQL Database from one subscription to another. The challenge I have is that it has a certificate with symmetrical key and if I try to export it to a .bacpac file I get ...