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

Benchmarking MongoDB full text search vs MSSQL full text search

I'm working on a project for comparing mongodb and mssql databases performance on full text search. I started with using YCSB benchmark but now that I have a deeper understanding about this benchmark, ...
0
votes
1answer
21 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 ...
-4
votes
0answers
31 views

So working from SSMS but does nothing when ran from application [closed]

I have a stored procedure which works fine when ran in SSMS but doesn't work when called from application. Any suggestions ?? Thanks in advance !
3
votes
1answer
62 views

Scan on trigger Seek on SSMS [on hold]

I'm having a performance issue with a trigger when insert/delete on a table, it scan an index associated to a view, but if I execute the update manually it generate a seek. I updated statistics for ...
0
votes
2answers
37 views

SQL Server — see when user/ login was last modified? And other meta-data

I know you can see when tables or views were last modifed in sys.tables or sys.views in SQL Server. It does not appear there is a table like this for users or logins --- or is there? Basically, a ...
0
votes
1answer
27 views

How to find out which table/object the KEY or PAGE or EXTENT locks in the sys.dm_tran_locks belong to?

When I query sys.dm_tran_locks, a couple of questions arise: I can see there are some locks that are placed at KEY, PAGE, EXTENT, RID level (resource_type column). How to find out to which table or ...
0
votes
1answer
26 views

adding 2nd catalog for linked server to DB2

I am unable to figure out how to add multiple catalogs for a DB2 linked server. I have an initial Linked server setup and working properly, but I am struggling to figure out how to add multiple ...
2
votes
0answers
24 views

MS SQL FileTable CreatedBy and LastModifiedBy

When using a SQL FileTable table, I would like to store additional metadata for the file such as CreatedBy and LastModifiedBy. By looking at SQL File Table Schema, this information is not available by ...
0
votes
1answer
41 views

Ola Hallengren Transaction Log Backups of System Databases

I am attempting to backup the transaction logs of master, model in addition to the msdb. MSDB transaction log backs up without any issues, but the master and model do not. Here is the script that ...
0
votes
1answer
54 views

Read Only Routing Does Not Work After Failover in SQL Server 2016 Always On [on hold]

I have 2 nodes in an Always On Cluster - Node A and Node B I have configured read only routing. When Node A is Primary and node B is secondary , the read only routing works and read only connections ...
-1
votes
1answer
26 views

How to kill a stored procedure which is in open transaction?

I have a stored procedure which is running since four hours but it's uncommitted. How to check if it's running currently and kill it.
-2
votes
0answers
30 views

Nested procedures with transactions [closed]

What happens when two nested stored procedures with transactions is wrapped up in an outer procedure ? Does the transaction might be open and stop from re executing the outer procedure again?
0
votes
1answer
50 views

Identifier that starts with is too long. Max length is 128

I referenced a few other blogs and I can't isolate the issue to this logic. Code is below. Throws the error message attached. -- 1. Import Multiple Delimited Text Files into a SQL ...
0
votes
0answers
30 views

DatabaseBackup - SYSTEM_DATABASES - LOG [closed]

I've recently started using Ola Hallengren's scripts for backups and I'm curious what others are doing for System Databases that are in Full Recovery Mode vs. Simple. A job was not created for "...
1
vote
2answers
136 views

Are views treated like a subquery in SQL SERVER?

I'm trying to optimize a view which grabs the most recent history record for an item based on some specific criteria. I wrote a query which ran fairly quickly, and then turned it into a view. The ...
2
votes
0answers
32 views

the mystery of the table showing up in the subscription, but not in the publication

TL;DR: This question refer to sql server 2016, transactional replication I've got a table that shows up in the subscription but not in the publication. Here are the details: In one of my servers ...
0
votes
0answers
37 views

How would you move data for only the tables that exist in the destination?

I have Database A (which has Tables a, b, c, d) on Server1 and Database B on Server2 (which has tables c, d, e, f). The tables are added/edited/removed at both locations randomly and may change at any ...
1
vote
1answer
57 views

How to SELECT TOP(1) with COALESCE to pick the top NOT NULL row for each column?

I have a hierarchy of settings that have a default for everyone, can be overridden by groups, or by an individual user, for a specific screen, etc... These are stored in a table with a Hirarchy-ID to ...
-1
votes
0answers
12 views

Getting function sequence error in Codeigniter [closed]

I am trying to clone a database and its working fine if I disable db_debug mode but throwing below error while running it in db_debug enable mode in Codeigniter but on another side its working fine in ...
0
votes
1answer
33 views

Comparing id s of same column and assign value of one to another

I have table with id name 1 aa 1 bb 2 ff 3 df I want to check if value is repeated in column "id" then assign the "name" value to next matching ids row Expected: id name 1 aa 1 aa 2 ...
4
votes
1answer
77 views

MSSQL - How to support aggregate pushdown on indexed view with SUM(decimal)

I have an indexed view with non-clustered columnstore index, e.g. CREATE VIEW [dbo].[SalesAggregated] with schemabinding AS SELECT BusinessDate, ItemId ,...
2
votes
0answers
22 views

Is it normal for Snapshot Replication job to be constantly running?

Our SQL Server 2016 already has Merge replication running successfully for some tables in 2 databases. We then needed to restore Snapshot replication for another set of tables between same pair of ...
1
vote
2answers
72 views

Do long-running and locking queries on subscriber databases cause performance issues on the publisher dbs?

One of our servers has a subscriber database. I've not used replication before and cannot find information on how the mechanism works. Does the publisher have to wait on the replication completing ...
0
votes
1answer
22 views

SQL Server Service Accounts Max Character Length

I've been considering a new naming convention for all SQL Server service accounts for instances in our enterprise. I ran into some issues on my lab environment. Is there a character limit to SQL ...
0
votes
0answers
23 views

Need help on designing database schema for high frequency update app

This app will call API endpoints every second and get JSON data and it needs to be saved in DB and then query it to the client. But Before saving, it needs to check if the data already exists or not, ...
0
votes
2answers
122 views

How can I use SQL Profiler to capture a single stored procedure

Using SQL Server SQL Profiler, which configuration can be used to monitor a single stored procedure? I'd like to capture all EXEC sprocName to include the parameter list. I'd like to capture this ...
0
votes
1answer
53 views

Clustered Index taking huge amount of space

I have been working on indexing a database dealing with historical data and recently encountered an issue with a clustered index that surprised me. The database is SQL Server 2018. The table itself ...
2
votes
1answer
57 views

Any benefit to having multiple filtered indexes?

I have a SQL Server database and stumbled across a table with a uniqueidentifier primary key named ActivityId and columns StateCode (int) and ActivityTypeCode (int) along with many others. The tables ...
7
votes
2answers
472 views

How can I see if the data in a SQL Server table is page-compressed?

This is a follow-on question to a question which I asked yesterday: Can I bulk insert into an empty page-compressed table and get full compression? The answer to that question (paraphrased from Randi ...
2
votes
2answers
53 views

Failover strategy for SQL Server 2016 Standard Edition

I am using SQL Server 2016 Standard Edition. I have two database servers, SQL01 and SQL02 with only one database, and I am utilizing transactional replication for bringing data from SQL01 to SQL02. ...
1
vote
1answer
67 views

Can I prevent deadlock using `WITH(NOLOCK)`?

I have a table which I'm only Insert, Delete and Select (no Updates) on Insert, Sometimes it used a session lock (WITH (TABLOCKX)) I have a program using ADO which Select from the table using the ...
10
votes
1answer
236 views

Cardinality estimate outside the histogram

Setup I'm having some trouble understanding a cardinality estimate. Here's my test setup: the 2010 version of the Stack Overflow database SQL Server 2017 CU15+GDR (KB4505225) - 14.0.3192.2 the new ...
1
vote
1answer
27 views

Configuring SQL Server for SSL Encryption

I'm new to the DBA role and I've been tasked with configuring a SQL Server install for a client, with the requirement that databases will be distributed across two instances. Connections to one of ...
3
votes
0answers
37 views

how to stop using sql server login credentials in a linked server?

I have a linked server based in Italy server_italia that connects to a server based in Oregon USA ORDB1 we are on different but trusted domains. The connection from server_italia to ORDB1 is made ...
1
vote
2answers
50 views

DBCC CHECKDB - Corrupted Pages

On my understanding, regular run of DBCC CHECKDB is a best practice in order to check integrity of the database. If there comes a time where a corrupted page existed, and no good back up is available,...
0
votes
0answers
11 views

mssql + sssd Ubuntu cannot login via AD group

I've got mssql 14.0.3192.2-2 setup unter Ubuntu; I have joined my machine to AD as described here. The Windows login via SSMS and sqlcmd works if I add invididual AD users, but not if I add groups. ...
2
votes
1answer
33 views

SQL Server Credential Password and Secret Difference?

What is the difference between SQL Server secret and password for credentials(both server and db scope)? For example while reading the BOL I can see specifying a secret. Is this just the same password?...
-2
votes
0answers
44 views

Database restore progress not showing [closed]

I have a database that has gone into restore and trying to find out the progress and im unable to see the progress. I tried running this query but im getting no results and in the object explorer i ...
1
vote
1answer
64 views

Is it useful to index a column if it is already the first column in a composite index? [closed]

Suppose I have this table (simplified from my project): create table BillTax ( BillID int not null constraint FK_BillTax_Bill foreign key references Bill(BillID), TaxCode varchar(20) not null,...
-1
votes
1answer
39 views

How to loop data month wise for the targeted dates [closed]

I need to load data from a primary table to an archive table by month or week. As an example, I have a table called transactions with a column named return_datetime where I have 5 years or data. I ...
7
votes
2answers
349 views

Can I bulk insert into an empty page-compressed table and get full compression?

I have a lot of large tables (around 10 million wide rows) which need to be regularly loaded into SQL Server 2016 for read-only reporting. I would like these tables to be as small as possible on disk, ...
0
votes
1answer
62 views

Two conversions of practically the same number giving two different results

To my surprise, I run the following queries in SQL Server 2014 and each returns a different result, as shown in this db<>fiddle... This Select convert(numeric(19,2),convert(float,9....
0
votes
1answer
17 views

Implementing gMSA account for SQL FCI

I have a question on implementing gMSA account for managing authentication to SQL server. Our goal is to use gMSA account to manage the auto password change activities for SQl server service and it's ...
0
votes
0answers
19 views

Import folder with Multiple CSV's into SSMS w/ out SSIS

I am wondering what's the best and most effective way to import csv's stored in a local folder into DB w/ in SSMS. Folder path- C:\Users\ngutierrez\Desktop\Open_Air_Tables Folder contains about 30 ...
0
votes
0answers
39 views

Find full name from AD alias in SQL Server

We have recently migrated from Sybase/SAP ASE to MSSQL Server. master..syslogins in sybase has two columns name and fullname. Name would be AD alias name and fullname is Full Name of the user. ...
0
votes
1answer
47 views

Why am i getting a truncation error when running a SSIS package on the sql server agent, but not when i manually run it

I created a SSIS package that drops then creates a table, to transfer data from Sql Server 2008 r2 to an access file. When I run it in BIDS 2008r2 it runs just fine, no truncation issues at all. When ...
8
votes
1answer
660 views

How to disable “Completion time:…” in SQL Server Messages window

Every query I run in SSMS append the annoying message: "Completion time:...". How can I disable that text?
1
vote
1answer
25 views

remove Analysis service from a SQL 2014 cluster

Is it possible to uninstall Analysis service from a SQL 2014 cluster. I’ve seen that it cannot be done in 2008/12. I’m hoping that this maybe fixed now
0
votes
2answers
68 views

Tools for SQL Deadlock Statistics [closed]

One of my company's SQL database generates a lot of deadlocks per day recently (someday >100) I'm trying to troubleshoot the causes of these deadlocks, i noticed that many deadlocks occur at the same ...
1
vote
1answer
80 views

SQL Server: I need to estimate execution time of a SELECT .. INTO .. statement

To take backup of a big table (using SELECT .. INTO .. ) took me almost 4 hours in a machine with 4 CPUs and 16 GB of RAM. No external application/process were accessing the table during the operation....