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
0answers
3 views

Microsoft SQL Server 14

I am running out of space in the C drive and I have plenty in the D or E drive. How to I move the Data from the Database from C to D or E?
0
votes
0answers
4 views

String or separate Table

I'm creating the DB for a system and in it is an alert system, where, when a certain action happens, or when certain information is present, an alert (via email) is triggered. I'm allowing users to ...
0
votes
0answers
17 views

Denying DDL & DML Privileges Based On HostName In Sql Server

Developers must not use DML and DDL commands in my company. So, i revoked their authority like update,delete or drop commands.But, they are able to alter or update some stored procedures or records by ...
0
votes
1answer
11 views

Merging table records across TabName for each PersonID

Consider the following table, CREATE TABLE temp ( [TabName] VARCHAR(255), [PersonID] VARCHAR(255), [FirstName] VARCHAR(255), [Gender] VARCHAR(255), [BenefitType] VARCHAR(255), ...
0
votes
0answers
8 views

MDX - Sum values by keys across tables

I currently have two tables in my database, which can be simplified as follows: GoodsStatus GoodsPrice ID|status ID|price --------- -------- 1 | 2 ...
1
vote
1answer
32 views

INSERT EXEC statement cannot be nested

I have a stored proc spGetSites that returns three columns for SiteName, SiteId and UnitCount. The stored proc runs perfectly fine. I'm trying to store the results of the stored proc to a temp table @...
0
votes
0answers
33 views

What preventive measures can be taken to get over this scenario with minimal data loss

I am SQL server dba and came across weird scenario our cluster which had 10 nodes 5 primary 5 secondary each nodes had SQL role.... example Ams1pd11 to Ams1pd15 are primary.. and Ams3 side from pd11 ...
0
votes
0answers
29 views

SSRS Tries to Upgrade Itself every morning at 2 am

I have a Windows 2019 Server running both SQL Server 2017 Enterprise (14.0.3238.1) and SQL Reporting Services 2017 (14.0.600.1109). In noticed in the RSManagement logs (located here: C:\Program Files\...
0
votes
1answer
30 views

How to write a query to identify common combinations of parameters used to call a stored proc?

recently I deployed a change into our production environment that ALTER'd an existing stored proc and essentially all the addition did was INSERT into a table I created the parameters passed in by the ...
0
votes
0answers
21 views

Error while deploying package to catalog

I am getting below error while deploying package to SSIS catalog on the last step, I am sysadmin and have access to the below views. Can someone help please. Let me know if you need more info. Any ...
1
vote
1answer
23 views

Need to create batches of 2000 records within a subset of records

I have a table with 200,000 records. One of the columns is a location ID. I need to break down the entire table in batches of 2000 but group them by the LocationID column. I have about 200 locations ...
2
votes
1answer
97 views

SQL Deadlocks - Same Stored Procedure Called Concurrently

I am working with a relatively complex system, where static tables are updated from multiple data sources as they are needed, because dynamically loading the data takes 4-5 seconds and we prefer to ...
-1
votes
2answers
43 views

Sql Server Standard 2014: Reindex job locked by other transactions

On a customer of ours we have Microsoft SQL Server 2014 - 12.0.4100.1 (X64) installed. It's a heavy transactions schema (especially during nights) and we have a scheduled REINDEX maintenance job that ...
0
votes
1answer
54 views

Nasty Lookups in Query plan

I've been trying to get rid of expensive lookups in my query plan but cannot seem to get my head around it. I understand the idea of creating a covering index to eliminate the need for lookups but I ...
1
vote
2answers
23 views

Failing MERGE Statement: How to monitor with XEvents and specifically get parameter values for a prepared statement?

My problem I am running SQL Server 2017 and would like to investigate an error with a MERGE statement like the following MERGE INTO table USING (SELECT ? AS Search_Col) AS SRC ON table....
1
vote
1answer
31 views

SQLCMD external editor

I have set the environment variable SQLCMDEDITOR=gvim to invoke an vim with the ED command of SQLCMD. From my old Windows 7 computer, it used to work. I have now a new laptop (windows 10) and, when ...
1
vote
1answer
60 views

How to optimize this Select MAX() SQL query?

In the following select query, it take almost 1 min to return the result, seems the table has over 2 millions record and growing. There are no index i can use from the table. How do I optimize this ...
1
vote
3answers
63 views

NULL as a @variable within WHERE clasue

I would like to SET 'IS NULL' as a variable and use it in a WHERE clause. I will be unioning many tables together that carry the same WHERE clause and would like flexibility to use a variable for my ...
0
votes
0answers
31 views

MSSQL - Can not start snapshot agent

I'm trying to setup a replication, but the snapshot agent will not start. It tells me Error messages: Message: Data source name not found and no default driver specified Stack: at ...
0
votes
0answers
19 views

ACE.OLEDB.12.0 is visible on Linked Servers Provider, still can't import EXCEL file

I just don't know what to do anymore. I've been searching in a LOT of topics. I"ve downloaded tons of providers from microsoft. I'm trying to import an Excel file to a database. I've been having this ...
0
votes
2answers
41 views

SQL Server Setup Error

I'm getting the following error at the very end of SQL Server 2016 setup. Updating permission setting for file 'Local Machine' failed. Principal 'SYSTEM\CurrentControlSet\Services\Eventlog\...
2
votes
1answer
35 views

What's the best way to concatenate a multi-column foreign key?

We are expanding our data warehouse to include a new source system. In the new system, we want a single column for a primary key, but in the old system it's two columns. For exmaple: create table #...
1
vote
2answers
43 views

What realistic issues can occur from shrinking the log files on my database (either through the SSMS UI or issuing a DBCC ShrinkFile command)?

I've heard talk of potential corruption or data loss occurring from shrinking the database data or log files. I'm curious in particular about only shrinking just the log files, what are some real ...
0
votes
1answer
34 views

SQL Server 2014 unable to connect on virtual machine

I have a problem, where I installed SQL Server Advanced Express 2014. Everything seems fine, I checked all I thought could be the issue, and everything that needs to be installed is there, but when ...
0
votes
0answers
32 views

MSSQL - Setting up simple Replication fails

Heads up: I'm a rookie when it comes to SQL Administration. Im seting up a simple MSSQL Replication running server '19 (developer edition). The server and agent services run as local admin on the ...
0
votes
0answers
24 views

Getting extra " double quotes while downloading report to TEXT or CSV

Recently developers created a report where they are concatenating each value of each column with " (double quote) in between. Like select '"'+first_name+'"'+last_name+'"' from table_name; On the ...
0
votes
0answers
33 views

Error when deleting file using xp_delete_file on SQL Server 2005

I'm trying to delete old backups with a scheduled job in SQL Server 2005, running on Windows Vista. Here's my script: DECLARE @BackupPath NVARCHAR(1000) DECLARE @DeleteDate DATETIME SELECT @...
0
votes
0answers
25 views

SSIS to import tables from Access DB to SQL

im trying to design an SSIS package to import 80 tables from Access DB into SQL DB, I have done it through the export and import wizard then saved the SSIS package but i didnt like the way the package ...
2
votes
1answer
56 views

Extended Events Collation resolution error

When trying to expand the sessions under extended events I am getting a Collation Resolution error in SSMS 18.2 (15.0.18142): Looking at a trace I have found that this query is what is giving me the ...
0
votes
0answers
33 views

Login failed for user 'domain\user'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>] [closed]

We have added a AD group which has sysadmin access to the instance, but when I try to connect I get this error message. Not sure what went wrong. Tried deleting group and adding it back to sql ...
-2
votes
2answers
42 views

Column 'CUSTOMERS.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause [closed]

I'm trying this simple query below but getting error. Please let me know what is wrong... SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS GROUP BY age HAVING COUNT(age) >= 2;
2
votes
3answers
60 views

Is it possible to add a DB to an Always On AG and setup replicas at different time?

I have a 3-node SQL Server Always On availability group. 2 nodes are on the same LAN, and the third one is on a remote site with high latency. I will add a huge DB (10TB) to the AG, so I would like ...
1
vote
1answer
44 views

Why can't the query processor produce a query plan with a FORCESEEK hint when I'm INNER JOINing by the clustered index on both indexed views?

I'm getting the following error when I try to use the FORCESEEK hint with the query below: Msg 8622, Level 16, State 1, Line 96 Query processor could not produce a query plan because of the hints ...
2
votes
0answers
22 views

A curious discrepancy between DBCC PAGE and %%physloc%% for System Base Tables

During an attempt to dig into some odd system behavior I've come up against a scenario where I'm using the undocumented routine DBCC PAGE to find some information within a page, but I'm not getting ...
0
votes
1answer
24 views

Why when I JOIN to a casted field in an index view, the execution plan warns about “type conversion in expression”?

I thought an indexed view was materialized and therefor stored on disk. If that's the case, I would've thought that any type conversions I'm doing in the view would materialize as that datatype on ...
1
vote
1answer
42 views

Do filtered indexed views cause contention on the underlying table when rows are added to the table that fall outside the filter?

If I create an indexed view on a table and the query on that table includes a filter, when new records are added to that table that don't fall into that filter (and therefor wouldn't show in the ...
1
vote
1answer
55 views

SQL Server experiencing THREADPOOL waits while still having plenty of available workers

I recently took a look at my production SQL Server's cumulative threadpool waits, and it's literally days worth of waits. So I started doing some digging. I'm on a 32 core, 64 bit server (running on ...
11
votes
1answer
2k views

Why is casting a DATE field to VARCHAR datatype non-deterministic and is there a way to make it deterministic?

I'm trying to create an indexed view where the index is on a field in the view which is a hash of a bunch of the columns of the underlying table in the view. Here's an example: CREATE VIEW ...
1
vote
4answers
93 views

What should I ask my DBA in order to diagnose a slow application?

I am working in the IT department of an industrial firm. The IT department is a handful of people, mainly project managers, and everything else is outsourced. I have been managing a project, where a ...
2
votes
1answer
97 views
+50

SQL Server 2017 Stored Proc & TVF Performance Problem

I have a SQL Server 2017 Enterprise Edition instance where a stored procedure was taking approx. five minutes to execute. After reviewing the stored procedure code, I could see that there was an ...
1
vote
1answer
31 views

Identify changes across records for the field ID

Consider the following table TabName ID Gender FirstName Tab1 C1 M John Tab2 C1 M John Tab3 C1 F John Tab1 C2 ...
3
votes
1answer
295 views

Optimize a slow nested loops join

I have this query running around 15 seconds on my system. The query plan is here. I believe the main problem is around here, where the optimizer chooses nested loops and is mistaken with cardinality: ...
0
votes
1answer
30 views

Design question: join data

I have this simple tables (from Northwind database) Orders-->[Order Details]<---Products with a query can see the order and the products using join very simple SELECT Orders.OrderID, ...
2
votes
1answer
90 views

Is there an easy way to SELECT FROM another session's temp table?

Is there an easy way to select the data from another session's temp table? I need to do so ad-hocly to debug an issue that I have limited visibility on otherwise. E.g. if I have access to TEMPDB, is ...
-3
votes
2answers
43 views

Multiple statement conditions using OR AND within the query

I have this survey table and I'm trying to gather the information of userid who answer Strongly agree to the question of I'm trying to watch my weight OR I love drinking Coffee Black AND must have ...
1
vote
2answers
32 views

SQL Help - Exclude data with just one distinct value

I have the following query: select agentname,CallCenterID, count(leadid) Dials from LeadCallHistory where agentname not in ('vdad','system') and cast(callCenterStartTime as date) between cast(...
-1
votes
1answer
58 views

Securing data from unwanted users [closed]

I inherited a DBA position not so long ago, and even having worked with SQL for the past 20 years it is a different ball game. Anyway, here is my pain :-) We need to start hiding sensitive data ...
4
votes
2answers
104 views

Does the command SET STATISTICS…ON turn statistics information on for all queries running throughout the server or just the current connection?

In regards to SET STATISTICS TIME ON and SET STATISTICS IO ON I noticed in the remarks of the Microsoft doc, it says the following: When STATISTICS IO is ON, statistical information is displayed, ...
4
votes
2answers
55 views

Checking the data latency on an Always On Availability Group in ASYNC mode

We have a number of SQL Servers with Always on Availability Groups in asynchronous mode between a primary and secondary server with Manual fail over. I created a latency report utilizing the below ...
-1
votes
1answer
44 views

how can I solve RAID 5 corrupted data? [closed]

I installed Windows Server 2012 on my server using RAID 5. But a problem occurred on my server. I extracted the data from 10 hard disks with data recovery. When I attached the extracted data files in ...