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
12 views

Patching SQL Server 2014 Versus SQL Server 2014 Express

Need to apply a Service Pack to the following SQL Express install: Installed version : 12.0.2000.8 (2014) Express Edition Not sure if I can apply the latest SQL 2014 Service Pack SP4 or stick to the ...
4
votes
1answer
20 views

Truncate Table locking sys views

We have a proc that runs Truncate Table inside an Snapshot transaction. This seems to be causing a LOCK_M_S lock that blocks the sys view sys.partitions. Is there a convenient work around for this?...
1
vote
1answer
13 views

SMO, Powershell, & Failed Logins

Background At my current organization, we've got a SQL Agent Job running a Powershell script, originally based on Script All Server Level Objects to Recreate SQL Server. This job writes a bunch of ...
0
votes
1answer
30 views

SSIS Slow Data Transfer between Oracle and SQL Server 2016

I am trying to design a process that transfers data from Oracle to SQL Server 2016 by using SSIS. It is done with the aim of removing productive processes from an SQL Server 2000 instance, where the ...
0
votes
1answer
26 views

Command runs fine in elevated command prompt, but fails when run in a Windows Task Scheduler task

Here's my predicament: The version on the server is SQL Server Express. And no, we cannot use any other version. (Long story, but: vendor will not allow without $ome...) From 1, you can deduct that ...
1
vote
2answers
341 views

SQL Server Management Studio DAC connection error

I'm trying to connect to SQL Server 2012 via DAC using Management Studio. I type admin: before my server name, yet I keep getting the same error: A network-related or instance-specific error ...
0
votes
3answers
26 views

Could not allocate space for object sql server

Can anyone give a solution for this error. Could not allocate space for object ‘’ in database ‘’ because the ‘PRIMARY’ filegroup is full. Create disk space by deleting unneeded files, dropping ...
3
votes
1answer
533 views

Table size increases after setting nullable column to null

I'd like to reduce database size by removing some data that is not useful anymore from a SQL Server Express database - 10 GB database size limit. So I have a table that is 5783 MB and has 4 columns ...
-1
votes
1answer
21 views

Matching records that fall into same date range

I've a sales table with multiple prices for the same product. Each with a range for when that price is active. From and a To. I need to find if they are any Prices that overlap for the same product ...
2
votes
1answer
476 views

Tracking Down Plan Cache Clearing

I'm dealing with a database whose plan cache seems to be periodically clearing out by itself. As an example, in the span of about 2 minutes, the SQL plan cache went from 12.5k MB to 3.1k MB. At this ...
1
vote
2answers
24 views

MS SQL Server Error 18456 after database move

I have a windows service connecting to a SQL database. Recently, I was told to change the DB name in the connection string, all else was the same. When I attempted to connect, it threw Error 18456. ...
0
votes
1answer
101 views

SQL Server Change Alias Not Recognized in SSMS

I created an alias named "ProductServer" to point to a SQL Server 2008 R2 named instance on my local machine. I'm trying to upgrade that to point to SQL Server 2014 named instance. I used SQL Server ...
2
votes
1answer
30 views

Which are the downsides of not deleting the CDC records in change tables?

If I do something like this: USE AdventureWorks2012; GO EXECUTE sys.sp_cdc_change_job @job_type = N'cleanup', @retention = 52494800; -- (100 years) GO The records in the change ...
3
votes
2answers
9k views

SSISDB master key

I recently had to recover my ssisdb databases, along with all other user databases to a new server because of a hardware failure. I am now seeing the following errors when trying to run ssis jobs ...
20
votes
2answers
146k views

How to insert or update using single query?

I have a table test having columns id which primary key and auto incremented and name. I want to insert a new record if annd only if there are no records.For example input is id=30122 and name =john ...
0
votes
2answers
477 views

performance benefits of shrinking a database

I have a database of 1.5 TB, which had only 200 GB free space before.Due to performance issues, business decided to reduce 1 month data retention policy to 1 week.Due to this 3 weeks data was deleted ...
5
votes
2answers
94 views

IndexOptimize - Configuration

We recently switched to Ola Hallengren's maintenance script and automated the deployment of MaintenanceSolution.sql to our customers's SQL Server instance. We need to set these parameters for the job ...
0
votes
0answers
24 views

Dimension attribute with one-to-many relationship

I have a request for an attribute in a dimension that has a one-to-many relationship with the lower level of the dimension. Here is the case: Dimension: Employee Attribute: Immatriculation I have ...
13
votes
1answer
254 views

Why does this RX-X lock not appear in Extended Events?

The Problem I have a pair of queries that, under serializable isolation, cause an RX-X lock. However, when I use Extended Events to watch lock acquisition, the RX-X lock acquisition never appears, it ...
14
votes
0answers
231 views

Implementation and contraindications for OPTIMIZE_FOR_SEQUENTIAL_KEY

SQL Server 2019 CTP3.1 introduced an optimization to address last-page insert contention. This takes the form of an index option called OPTIMIZE_FOR_SEQUENTIAL_KEY. One imagines this could be an ...
0
votes
1answer
26 views

Which rights to grant for controlling an SQL Server Availability Group?

We are having the following setting: For an ERP Test / Accept SQL Cluster we want to follow the least privilige principle to give the external engineers rights on the SQL server. We are running SQL ...
0
votes
1answer
157 views

How do you use pvkconverter.exe - not working for me?

I tried to following this link to convert a .pfx file to the .cer and .pvk file pair. When I run this command, "C:\Program Files (x86)\Microsoft\PVKConverter\PVKConverter.exe" -i TDE_Cert.pfx -o ...
3
votes
1answer
212 views

Why can't SQL Server optimizer use foreign key [duplicate]

I have the following tables(See below for complete reproducible SQL): ToBeTransferredTemp (ID varchar(100),type varchar(100) NOT NULL, other...) CODES(Key1,Key2,Key3, IDSTRING varchar(100), other...) ...
1
vote
0answers
38 views

Tool to migrate data to another database with different object names [on hold]

We are working on a project to migrate our Oracle database to SQL Server. There are many off the shelf products that seem to do this such as Microsoft SQL Server Migration Assistant & Navicat ...
0
votes
2answers
25 views

Multiple AGs on same Windows Failover cluster the Listeners don't work properly

Here is my case. I have Server1 and Server2, which were installed 4 SQL instances as follows: Server1 Server1\SQL1 Server1\SQL2 Server2 Server2\SQL1 Server2\SQL2 The Server1\SQL1 and ...
0
votes
1answer
45 views

Why are execution plans wildly different when selecting 1000 rows vs 2000?

Consider the queries below. The only difference is 1000 vs 2000 rows returned. SELECT TOP 1000 lwa.Message INTO #foo FROM dbo.LogWidgetsAPI lwa (NOLOCK) ORDER BY lwa.TimeStamp vs SELECT TOP 2000 ...
3
votes
0answers
2k views
+50

“Server local connection provider has stopped listening” errors in SQL Server error log

We have started seeing the following errors appearing intermittently (between 1 and 5 a day on average) in the error logs on one of our SQL 2016 servers: Error: 26050, Severity: 17, State: 1. Server ...
0
votes
2answers
294 views

Data Warehouse: Auto Generate Slowly Changing Dimension Code For Any Table with Dynamic Sql

Can someone code review to take OLTP/Stage data and transform it into Data Warehouse Kimball Slowly Changing Dimension Type 2? This code should be dynamic, and should be able to utilize any table as ...
1
vote
2answers
43 views

Correct data types for my database

I have to make Movies database with the following tables: • Directors (Id, DirectorName, Notes) • Genres (Id, GenreName, Notes) • Categories (Id, CategoryName, Notes) • Movies (Id, Title, ...
3
votes
1answer
58 views

Constant Scan joining

In preparation of my previous Constant Scan question I was experimenting with VALUES in various ways and came across the thing regarding joining of VALUES that looks odd to me. Setup is simple ...
0
votes
1answer
41 views

Specifying Order of Executing Stored Procedures [on hold]

Issue: The order in which my stored procedures are called produces no errors if run independently, but when run as once, yields the following error: Msg 2627, Level 14, State 1, Procedure ...
9
votes
1answer
399 views

Hash join between master/detail tables produces too-low cardinality estimate

When joining a master table to a detail table, how can I encourage SQL Server 2014 to use the cardinality estimate of the larger (detail) table as the cardinality estimate of the join output? For ...
6
votes
2answers
346 views

Turn TDE off when restoring SQL databases

We are just getting started with TDE. Eventually it will be in our Prod SQL Server environment and two major non-prod environments, but currently it is only in one non-prod system. My question is ...
1
vote
0answers
32 views

How to make Indexed Views work for SQLCLR?

In MS SQL, the Indexed View is a hellscape of limitations. But I need one. I have a formatter SQLCLR function that creates a prettified version of a domain key - users want to be able to search on ...
0
votes
1answer
1k views

Persistent Data Collector Collection_Set_3_Upload Job Failure with AlwaysOn Groups Enabled

I have a two node SQL 2012 AlwaysOn HADR cluster (v11.0.3412) with 3 availability groups configured. The AG groups are set to synchronous mode and the secondary is not readable (we do not want the ...
4
votes
2answers
130 views

How can I run SQL Server Vulnerability Assessment from a SQL Job?

I want to run SQL Server Vulnerability Assessment from a SQL Server Agent Job. Currently, I am attempting a job with a PowerShell script and am running a command like the one below. Invoke-...
2
votes
1answer
23 views

Saved state for SQL Server Always On on Hyper-V

For our SQL DEV/QA environment our system-engineers did setup an Hyper-V enviroment - 3 hosts - 2 VM on each host (1 for DEV an 1 for QA) Patching for the hosts is scheduled once a month but I ...
-4
votes
0answers
48 views

how can implement the subquery that shows me one table exactly like this picture

How can implement the subquery that shows me one table exactly like this picture? I try this query. I want separate month and year and bring month into the separate column just like the pic Q 3ai ...
0
votes
1answer
99 views

LUN for Database / Instance [on hold]

Is it possible to get the total LUN's for a instance - database and logs separate? If not, would adding up the individual results of the query below per database yield that result? SELECT ...
2
votes
1answer
55 views

Optimize query that only returns when the number of rows to be returned is less than an amount

I have a query that first counts the number of rows to be returned and, if it's below a limit, it returns those rows, else it returns nothing. For context: I have a map that displays some points ...
9
votes
2answers
9k views

How to trace SQL queries which are crashing SQL Server

We have a SQL Server 2008 database server (it happens to be running under MS Failover Clustering, but I don't think that is relevant here). Our application runs Hibernate for DB access, and since we ...
0
votes
0answers
43 views

A Procedure Causes Some Blockings In SQL Server [on hold]

There is a stored procedure in my sql server database and it causes so much blockings for itself. Here is the code. How to solve this issue? USE [FATURA] GO /****** Object: StoredProcedure [dbo]....
0
votes
1answer
20 views

How to change the reporting services report service mode from Native to integrated?

In SSRS-2016, I am trying to change the Reporting services report service mode from Native to integrated. I added 2 new shared features to existing installation (See in the Pic) I am trying to ...
0
votes
1answer
27 views

SQL Server - Log file not shrinking in AlwaysOn group

We have three-node AO setup in SQL Server. Primary node, one secondary node in Sync mode and another secondary node in Async mode. Main Database on Primary node, log file-size is 434GB, Used 99.91%. ...
-1
votes
1answer
40 views

How can I achieve the below case in marketing cloud without using loop? [on hold]

I have a table like this and in the table you can see 123 losing to 456, 456 losing to 789 and 789 losing to 101112 and in the same fashion for ID's 5 and 6. In this case I want to replace winner ...
1
vote
1answer
31 views

Is there a way to group the output of a query based on two rows

I'm not sure my question is asking the right question, so i will be as specific as possible in here. I have the following data which i have changed the values of. Below is the code I am using to ...
9
votes
4answers
1k views

How do I intentionally fragment a SQL Server Index?

I would like to create bad indexes conditions intentionally on a SQL Server 2017 test database I have, just to better understand these maintenance scripts? SQL Server Index and Statistics Maintenance ...
0
votes
1answer
26 views

declare table variable inside function

In my stored procedure, I have table variable with a lot of columns. Because I want to simplify the stored procedure I thought i could declare table variable in function like CREATE FUNCTION [dbo].[...
0
votes
0answers
53 views

SQL stored procedure returning 0x000000000

I have a stored procedure that executes a number of selects for variable setting ,update and delete queries etc. The stored procedure executes manually without error but in the messages shows ...
-1
votes
0answers
26 views

SQL Server crash? [on hold]

My Application on .Net framework using entity framework. I am using stored procedures also sometimes SQL server crash and need to restart server.how to trace why this problem occur?