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

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.

0
votes
1answer
291 views

How to add SQL Server 2014 instance?

I followed this link to install SQL Server 2014. When I clicked on "New installation or add features to an existing installation" instead of proper box, I have to choose "Browse For Folder". When I ...
0
votes
0answers
3 views

Clarifying the connection between TimePlan and the daily routine of work

I'm creating a new database and I have an uncertainty between the two tables (Timeplan and DailyRoutine), how to make a dayiluroutine connection for each day. The idea is to write in the Timeplan ...
3
votes
1answer
800 views

Recovery mode keeps reverting to full

I'm using SQL Server 2016 to manipulate some large research data-sets. I keep setting the recovery mode to simple, however it keeps reverting somehow back to full. Any guidance or advice on how to ...
1
vote
1answer
258 views

How to manually invalidate a pull merge replication snapshot from the publisher

XY Problem background info: I have a pull replication publisher in which I want to add or alter a index and have those changes be applied to the subscriber. The solution to that problem I would like ...
2
votes
2answers
296 views

Concurrent backups (full and transaction) and testing recovery model

My intent is to test my recovery model and setup log shipping to replicate a database on another server. I have a database called AdventureWorks on a primary sql server. Presently SQL Backup performs ...
0
votes
0answers
12 views

When I update end time of a tblSubTaskDetails, how to automatically update all tables with sum of ProjectID, TaskID and SubTaskID? [on hold]

When I update tblSubTaskDetails, sum of particular ProjectID, TaskID, SubTaskID should be inserted in the tblSubTask table in ActualHours. Like wise it should update tblSubTask with sum of particular ...
4
votes
1answer
855 views

SSMS Cannot show the editor for this task (Notify Operator Task)

I'm getting an error message whenever I double click on a Notify Operator Task in our Maintenance Plans in SSMS, which says "Cannot show the editor for this task." Property IsMailHost is not ...
0
votes
1answer
31 views

SQL-Server schema audit trigger - possible to get old view/sp/function definition?

I'm using the below database-level trigger for schema-change-auditing. It works fine, but if I do (drop view - create view), it only stores the sql that created the view, not the one that is dropped. ...
0
votes
1answer
17 views

What permissions are required for UPDATE STATISTICS #table WITH ROWCOUNT = xxx?

Can anyone point me to the correct documentation or additional permissions I need besides SA to do the following? When I run it, I receive the following error: Cannot find the object "#test" because ...
7
votes
2answers
17k views

SQL Server column level security

I think I need some pointers on security in SQL Server. I'm trying to restrict some of our end users from getting access to certain columns (i.e. SSN) on a table. I thought I could just use column ...
0
votes
0answers
11 views

FULLTEXT SEARCH IN MS SQL SERVER

now I am trying FULL TEXT SEARCH mechanism in MS. I load txt files to table ( one file - one record). Column is type nvarchar(MAX). I tested that i can store only about 42,6kB of data in one record, ...
2
votes
2answers
361 views

Would a date clustered index speed up queries with date ranges? (SQL Server)

I'm not the expert on indexes really - I'm trying to work my way through a few materials now. Anyway, let's say I have a table with 7 million records spanning 3 years. However the most common query ...
1
vote
2answers
49 views

Lock pages in memory for system accounts

Yes, well where to start? If you really enjoy installing and fine-tuning Microsoft SQL Server or Oracle RDBMS instances on Microsoft Windows Server, then at some point you will all come across the ...
0
votes
0answers
12 views

Is there any special configuration for SQL Server implementing Routing List in a heterogeneous enviroment (Client and Server in different domains)?

I had implemented successfully SQL Server Routing List Feature many times... but I have a very special scenario where our Reporting Services Node is located in a different Domain than SQL Server Nodes....
2
votes
1answer
28 views

Proper normalization for an Orders table--whether to split or no

Each order has a unique SalesOrderNumber. An order is made up of various PartID numbers, each with an associated OrderQuantity number. I have the following table currently in my model: I'm ...
0
votes
0answers
10 views

SQL Mirroring - Application not pointing to new Primary

Im stuck with a connection string issue this is my connection string. <add name="APP01" connectionString="Server=10.169.0.2; Failover Partner=10.166.0.2; Initial Catalog=DB2017; Integrated ...
0
votes
0answers
21 views

How can a datatimeoffset in UTC be converted to the local time?

In SQL 2012 how can I convert a UTC datetimeoffset to local time zone?
1
vote
2answers
1k views

Modify Backup Directory Path Ola Maintenance Script

I have configured backup using SQL Server Maintenance Solution by Ola (https://ola.hallengren.com). In the backup job, I specified BackupDirectory as a shared folder (eg: \comp1\path). It worked fine,...
7
votes
2answers
4k views

How to Dynamically change the database using TSQL

I'm having trouble with trying to dynamically change the context of SSMS to the database specified in dynamic SQL: EXEC sys.sp_executesql N'USE db1 ' ; It executes successfully however the database ...
65
votes
8answers
42k views

MAXDOP setting algorithm for SQL Server

When setting up a new SQL Server, I use the following code to determine a good starting point for the MAXDOP setting: /* This will recommend a MAXDOP setting appropriate for your machine's NUMA ...
0
votes
1answer
311 views

Unable to connect to Listener after rebuild

I dropped an AG with DROP AVAILABILITY GROUP [MyDb] it had a listener. I left the listener open in the object Explorer of SSMS 17.3. While the AG and Listener were down. I hit refresh on the ...
2
votes
2answers
5k views

Why do I get The transaction log for database 'X' is full due to 'ACTIVE_TRANSACTION' w/ Simple Recovery and auto grow log?

Why do I get The transaction log for database 'X' is full due to 'ACTIVE_TRANSACTION' on a database with Simple recovery model and auto-growing log file? The failing operation is executing many, ...
2
votes
1answer
57 views

Troubleshooting a slow performing encrypted stored procedure

I have a stored procedure which is part of a vendor supplied system. The stored procedure is encrypted so we can't see the code. The stored procedure has all of a sudden started to run slow. The ...
3
votes
1answer
12 views

How can I check if my user has create index permission?

I'm working on a .NET library that is used with a third-party product I support. My library may be used with many different versions of the product. I don't control the schema of that product, but I ...
11
votes
3answers
6k views

How can I reliably determine if a given SQL Server instance has the “Lock Pages in Memory” right?

The "Lock Pages in Memory" right can be granted to the service account used by SQL Server. This allows SQL Server to prevent memory being paged to disk. I've noticed several of our SQL Server ...
6
votes
2answers
2k views

How does SQL Server choose an index key for a foreign key reference?

I am working with a legacy database that was imported from MS Access. There are about twenty tables with non-clustered, unique primary keys that were created during the MS Access > SQL Server upgrade. ...
0
votes
1answer
25 views

Blocked Process Report

I have tons of BPR in .xel file (XE) and I want to be able to view them in a graph form and dump them all in SQL Server table, is there any way to do so? Instead of the 5 sec threshold is there any ...
28
votes
4answers
772 views

What is a scalable way to simulate HASHBYTES using a SQL CLR scalar function?

As part of our ETL process, we compare rows from staging against the reporting database to figure out if any of the columns have actually changed since the data was last loaded. The comparison is ...
0
votes
1answer
31 views

Prevent read access on primary AG replica, but allow it on the readable secondary

We have a unique situation where we want to allow users to query a readable secondary replica of a database with SSMS for adhoc reporting, but not allow them to ever read from the primary replica. We ...
-1
votes
2answers
131 views

Add default constraint from another table

How to add a default constraint to a table such that the value that will be used as default will be from another table's columns? i have created a table MY_TBL in which having a column CURRENCY_CD ...
0
votes
1answer
19 views

SSIS data flow gets stuck reading table with no errors

When I'm doing ETL in SSIS, the data flow task "pauses" reading from the source transactional Live IO DB (the one on the left). It stops reading the data and "pauses" on random rows and then does ...
2
votes
1answer
34 views

Why is an empty rowset not equivalent to null when assigning a query result to a variable and how best to work around?

I have a cursor. Within my loop I select a single column and assign the result to a variable. When the select returns an empty rowset, nothing is assigned to the variable. In particular, the variable ...
0
votes
1answer
39 views

How can I use Count() or Sum() with advanced criteria such as matching the most recent rows where a condition is true?

I have created a table with the following structure: | ID | RecordDate | SerialNumber | ProductID | StageID | Result | |----+------------------+--------------+-----------+---------+--------| | ...
8
votes
1answer
183 views

SQL dumping all pages from buffer cache every few minutes

I have a single SQL2012 SP4 node running several databases. The server has 20GB of memory available, 14GB allocated to SQL (nothing else running on the box). Every few minutes SQL dumps the whole ...
10
votes
3answers
330 views

Filter condition not correctly applied to Clustered Columnstore index

Using the below example, the predicates are the same, however the top statement (correctly) returns 0 rows, the bottom statement returns 1 - even though the predicates do NOT match: declare @barcode ...
-2
votes
0answers
40 views

SQL Server select query performance improvement [on hold]

We have a requirement of creating a query by joining more than 50+ tables and display the results. However it is taking more than 30 seconds to get the results. All data to be mandatorily retrieved. ...
25
votes
1answer
1k views

SQL Server 2017 crashes when backing up because filepath is wrong

I was trying to restore my database and SQL Server kept crashing. I would get a message in SSMS that said there was a network transport error (the connection dropped bc the crash). I checked the logs ...
0
votes
1answer
18 views

Errors in the OLAP storage engine: A duplicate attribute key has been found when processing

When I try to process my cube and specifically the Employee_DIM, I get the following error : Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: '...
5
votes
3answers
896 views

Do I need full recovery mode when I have multiple daily backup?

I have created 5 maintenance plans on my SQL server (say 50 !) which generate databse backup 5 times daily. As I read in this Q/A the full recovery is good when I need Point-in-time recovery. My ...
0
votes
3answers
32 views

Sum of decimal values that come in a string

Is there an easy way to get the sum of all decimal values in a string column. Example: "ABC $3.25, DEF $2.50, HIJ2 $0.25" "ABC1 $3.25, DEF $2.50, HIJ $0.25, KLM $4.50" I want to add only the values ...
3
votes
1answer
274 views

SQL Server OR operator causing massive loop join

The query below is very slow (running over a minute) and I have narrowed down the issue to the OR operator (...OR (EXISTS (SELECT...). I used live execution to verify that there is a nested loop join ...
3
votes
3answers
149 views

why it is doing index seek

I created a sample table as below CREATE TABLE [dbo].[StatisticsDemo]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](50) NULL ) ON [PRIMARY] Then I inserted below data as below: ...
4
votes
1answer
55 views

Execute Permissions for a Store Procedure that creates databases

I have a Stored Procedure that creates a Database for a specific project which needs to run every month by one of our Data Analysts. The question is how do I structure this to enable the Analyst to ...
0
votes
1answer
423 views

powershell command enable-alwayson not work for SQL Server Named instance

Hi all, I installed a SQL server 2016 default instance and a SQL Server 2017 named instance (instance name is SQL17) on my Windows 2012 Server. I opened a powershell windows with administrator mode, ...
1
vote
2answers
52 views

Query to see Index Fragmentation on all databases without using SP or temp tables

I am trying to compile a index fragmentation report on all databases, in a multi-server environment. The query will be executed via an openquery command, so I would like to make it as simple as ...
0
votes
2answers
354 views

Unique key constraint violation not escalated to parent stored procedure

I have a stored procedure which calls 2 nested sub procedures. For Example, MyProc BEGIN BEGIN TRY BEGIN TRAN EXEC UpdateProc EXEC InsertProc COMMIT TRAN END TRY ...
2
votes
1answer
286 views

Different plans on Readonly Secondary Replica

We have an AlwaysOn environment where we use the async readonly secondary replica for reporting purpose. One of the reports intermittently runs for much longer than normal. I can see when it runs ...
7
votes
3answers
294 views

Access to the path 'c:\some\path' is denied for MSSQL CLR

I think this is a permissions problem, but I'm having trouble locating it. I have a group of CLRs on one server (SQL Server 2016) and they work as they should. All are marked UNSAFE and they do ...
1
vote
1answer
75 views

Understanding MAXDOP behaviour for queries going parallel

For my current server as below 4 Sockets with 10 cores per socket and 20 logical processors per socket. Total 80 per SQL licensing: 4 NUMA nodes with HT enabled I've set CTOP 50 and MAXDOP 8 on ...
3
votes
1answer
62 views

70-761 Practice exam Question about @@Trancount final value

There is this question in the practice 70-761 exam that states after executing the following script, what is the value of @@Trancount? Exam script: CREATE PROCEDURE dbo.up_CreateSalesInvoice ( @...