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
The results are in! See what nearly 90,000 developers picked as their most loved, dreaded, and desired coding languages and more in the 2019 Developer Survey.

Questions tagged [sql-server]

All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag like sql-server-2016 if that is relevant to the question.

4
votes
2answers
221 views

Extract rows of a table, that include less than x NULLs [duplicate]

I am working with a SQL Server database, which includes a lot of NULLs. To analyse my data, I want to extract all rows of the database table, that include less than x NULL marks (e.g. x=2). My ...
0
votes
1answer
42 views

Obtain SQL Login account password

Is there anyway I can obtain a SQL Login account password, I did not create the logins so I would like to see what the password is for a certain SQL login. Thanks!
0
votes
2answers
34 views

How to get list of databases that are mapped to a login?

I was trying to list all databases that are mapped to a login in sql server using T-SQL without creating a temp or any type of table. Any help will be appreciated.
8
votes
3answers
621 views

CAST throwing error when run in stored procedure but not when run as raw query [duplicate]

Something very odd is happening here. I have a query that looks like this. SELECT CAST(FT.DOP AS SMALLINT) FROM TRACKING_DATA WHERE date > @mydate and identifier = 0000000000 When run as a raw ...
0
votes
0answers
16 views

SQL Server login accounts while configuring SQL Server Reporting Services

I configured reporting server in a new app server for my .NET application and, since I am new, I looked at the old app server reporting service config and did the work. So after installation, I ...
2
votes
0answers
92 views

SQL Server buffer cache smaller after reboot with same workload [closed]

We are hosting a SQL Server 2014 SP2 CU16 in a Windows Server 2016 64-bit environment for a customer. Performance has been fine for a long while. On March 31 at midnight, the database server ...
0
votes
0answers
35 views

Loading 'Image Service Package' crashes Management Studio

I'm getting the following error on my SQL Server when I log into the system: I ran a repair on SSMS successfully, but it did not resolve the issue. The following error message is recorded in Event ...
0
votes
0answers
10 views

What are the service running for mssql server on linux, and how to check if they are running

On microsoft's site I can see the mssql-server on linux has following components Mssql server (database engine) SSIS (integration services) full text service High Availability I can only see if ...
0
votes
1answer
38 views

How to get the SUM of custom made query

I have created a relation database structure below, from this relational table i want to get the SUM of all data, below my query, SELECT ((BASE_InvoiceLine.Price - IIf(SUM(BASE_Expense.Amount) IS ...
1
vote
0answers
15 views

My Fact table has data weekly but it is not visible in SSAS cube

I have my fact table that has data in weekly basis, so I am mapping with Week dimension to pull records in Piviot table. My ssas cube doesnt show all data that is in fact. Should I add any specific ...
2
votes
1answer
70 views

Bind dll in SQL Server

I have a little side project at my work. My task is to deploy some SQLCLR code into our dev-database (SQL Server 2017) to test it. If the tests are successfull, we want to develop triggers and ...
0
votes
1answer
42 views

MSSQL Instance Alias - how does it work

Ok I'm trying to understand how a MSSQL Alias works but documentation is lacking to say the best. Most blogs/videos/documents I can find state that all you need to do is go to SQL Server ...
1
vote
2answers
110 views

Is this deadlock related to our database?

our application is running in a SQL enterprise cluster hosted at a client. We don't have administrative access. They are saying that our database is generating deadlocks, and sent us a huge trace file....
2
votes
2answers
99 views

Postgres heap vs SQL Server clustered index

I am transitioning from SQL Server to Postgres, and one of the biggest things for me to digest is the non-existence of the "clustered key" that sorts the data in Postgres. Can someone share their ...
3
votes
0answers
69 views

When using DAC, receiving the “[…] the maximum number of '1' dedicated administrator connections already exists” message in error log

I am about to escalate this to MS, but I thought I would try here first and see if anyone has any ideas as to how to prevent this faux error message... When connecting to SQL Server (2016 and 2017 ...
1
vote
0answers
85 views

Stored procedure to UPDATE row to “closest” match in one table from data in another table

I have a table called ThawLaneA that has a populated column ThawTime that has DATETIME value. The first row starts at a specific time, and each row from there "increases" by a specific amount of time (...
0
votes
0answers
52 views

Issues with a SQL Server instance alias

I have the most simple of questions: I've got a Microsoft SQL Server with two instances, default and a named instance called Instance1. The default instance is running on static TCP/IP port 1433 and ...
0
votes
2answers
17 views

Import data tier application in Managed instance

I am using SSMS 18.0 Preview 6 and wanted to import the .bacpac file we have locally stored and accessible. However, seems the function is not supported in the GUI . Any updates/suggestions are ...
0
votes
0answers
47 views

Problem With Reading SQL Server Audit Files

I have problem with reading sql server audit files when I click "Load Log" button and select any audit file. Some log files show gibberish characters, some show any information. Date 1/1/0001 ...
4
votes
3answers
231 views

Obtaining database information and values in extended properties

I need to obtain each DB (together with its information) from SQL Server and I am doing so by the below: use master; select * from sys.databases where database_id > 4 order by name I also need ...
-1
votes
2answers
71 views

Output in HTML table format

I've a below script running and i need to get the output in a table format. Can someone assist on the below. declare @xml nvarchar(max) declare @returnvalue nvarchar(max) declare @body nvarchar(max) ...
0
votes
0answers
36 views

Filegrowth recommendations for Azure Managed Instance

On Azure SQL Managed Instances, im seeing new database getting 16MB filesize growth on database and logfiles (i changed log to 128MB). I haven't been able to find online docs from MS on recommendation ...
2
votes
2answers
55 views

specify delimiter in openrowset bulk insert

I'm trying to insert data from CSV files into tables using a cursor and open rowset bulk insert. this works for me 99% of the times but if one of the fields in the csv files contains a comma( , ) the ...
2
votes
1answer
57 views

SQL Server expensive nested loops join and lazy table spool

I am trying to tune the below query which takes 15-16 seconds no matter what value is passed in as a parameter, the query is: select distinct d.documentpath as path, d.documentname as name, d....
8
votes
3answers
169 views

How can I get running totals of recent rows faster?

I'm currently designing a transaction table. I realized that calculating running totals for each row will be needed and this might be slow in performance. So I created a table with 1 million rows for ...
-1
votes
0answers
43 views

Full outer join not pulling all records from right table [closed]

I am attempting to pull all rows from the left table as well as the right table with the following example query. However, there is one row from the right table that is not being included because it ...
2
votes
2answers
103 views

Slow checkpoint and 15 second I/O warnings on flash storage

Last couple of weeks we've been working on getting to the root cause of what could likely be the cause of the occurrence of these I/O issues and slowdown of the checkpoints. At first glance it looks ...
0
votes
1answer
23 views

Resources used by ExtendedEvents

Does anyone know how to tell how many resources (CPU/MEM/Reads etc) have been used by SQL Server Extended Events? I've went through the available DMVs here But I dont see where this is tracked ...
1
vote
1answer
24 views

Migrating 2+ GB Oracle CLOB column to SQL Server VARCHAR(MAX)

I have a column on an Oracle db containing HTML data with embedded images that result in some records being over 2GB in size for that column. I have successfully migrated the rest of the database to ...
0
votes
1answer
37 views

How to deploy the SQL scripts to the local database (one-click deployment)?

How to deploy the SQL scripts to the local database (one-click deployment)? On a periodic interval, we receive .sql files from Developers. I will have to automate the process of pushing all .sql ...
10
votes
1answer
251 views

logical reads on global temp table, but not on session-level temp table

Consider the following simple MCVE: SET STATISTICS IO, TIME OFF; USE tempdb; IF OBJECT_ID(N'tempdb..#t1', N'U') IS NOT NULL DROP TABLE #t1; CREATE TABLE #t1 ( r int NOT NULL ); IF OBJECT_ID(N'...
0
votes
0answers
39 views

Transactional replication from Oracle PL/SQL to MS SQL Server broke from the data in a Date column but previous records with same date are ok?

I have a heterogenous transactional replication subscription setup between PL/SQL (12C 12.1.0.1.0) and SQL Server (2008 R2), where the Oracle PL/SQL server is the publisher and the MS SQL server is ...
0
votes
1answer
59 views

Do Indexes in the T-SQL Data Warehouse automatically go over and optimize into the SSAS Cube?

Do Indexes in the T-SQL Data Warehouse automatically go over and optimize into the SSAS Cube? I am reading that applying indexes, will transfer into SSAS. I thought SQL and SSAS are totally different ...
1
vote
0answers
52 views

Stuck Oldest unsent transaction in db mirroring

I am seeing some very strange behavior in db mirroring For one of the databases, during the weekend when rebuild index job ran on the principal, it generated around 60 GB log. Now its been 2 days ...
9
votes
2answers
1k views

How to find if SQL server backup is encrypted with TDE without restoring the backup

Is there a way to find from the SQL Server Backup file or MSDB tables if the backup is encrypted with TDE without trying to restore the backup file? Thanks
3
votes
1answer
79 views

how to prevent replication to stop if there is no activity for some time?

On one of my transaction replication publications, when there is no any activity, after some time, the replication stops. Where can I see these replication settings? what to put in place to prevent ...
1
vote
1answer
55 views

Granting permissions to a DB user - SQL Server

I need to grant select and update permissions to a DB user (TestUser) on TestDB database as per below. When I press ok and re-check the permissions, they are not visible as can be shown below: From ...
1
vote
2answers
119 views

Very slow simple INSERT in transaction

I have I problematic query on my SQL Server where I could not find the problem. I have two simple tables, one for the "Headers" and one for the "Items" (each with an PK column, total of five fields ...
0
votes
1answer
48 views

Select one column into debit and credit

SELECT TOP 1000 [LEAD_CO_MNE] ,CASE WHEN CURRENCY <> 'KHR' THEN AMOUNT_FCY ELSE AMOUNT_LCY END 'Amount' --I want to split into type of amount debit or crdit ...
0
votes
1answer
54 views

Performance Metrics: Columnstore Indexes vs SSAS Cubes [closed]

What has faster performance speed: Columnstore Indexes or SSAS Cubes? Are Columnstore indexes proven to be faster than SSAS cubes (SQL Analysis Services)? Have their been any performance studies ...
9
votes
3answers
1k views

How to get the last not-null value in an ordered column of a huge table?

I have the following input: id | value ----+------- 1 | 136 2 | NULL 3 | 650 4 | NULL 5 | NULL 6 | NULL 7 | 954 8 | NULL 9 | 104 10 | NULL I expect the following ...
0
votes
2answers
49 views

How to grant or allow a particular SQL Server Login/User to always view a Column Level Encrypted data?

I used Column Level Encryption to encrypt one of the columns in my SQL Server database. Now everytime I want to SELECT to see that encrypted data, I have to open the symmetric key to decrypt the ...
3
votes
1answer
599 views

Aggressive Under-Indexing and no data for missing index

I know that there are lot of blocking on my database and have tried my best to get this sorted by vendor as this application is supported by them and hasn't produced any successful result yet. Every ...
1
vote
1answer
69 views

Updating values in table based on ranges defined in another table

I have two tables definitions and history with the following schema: create table definitions ( id UNIQUEIDENTIFIER, revision INT, majorVersion INT ); create table history ( id ...
3
votes
1answer
74 views

INSERT to a table from a database to other (same SQL Server) using Dynamic SQL

I need to copy a record from a Database DBa, Table Tbl1 to Database DBx, Table Tbl1 and get the Scope Identity inside the same SQL Server 2005 server I have already checked this: How to Dynamically ...
0
votes
3answers
47 views

SQL Join and Count

I have 3 tables in SQLServer that I want to join so I can count the number of times each SUBJ_NAME occurs for the ITEM_IDs in the LOAN table. CREATE TABLE SUBJT ( SUBJ_ID CHAR(4) PRIMARY KEY, ...
0
votes
2answers
44 views

User and login mapping after an SQL Server restore

I need to restore a DB from an SQL Server instance to another and I am well aware that the DB users will be transferred but the logins won't. I have created a script to be able to create the logins on ...
-1
votes
1answer
39 views

Accidentally disabled windows authentication SQL server 2014 [closed]

I disabled the windows authentication in SQL server 2014. I don't know how to re-enable it. I tried to google but couldn't find the solution. It says Expand Security and then expand Login. Right-click ...
0
votes
1answer
24 views

getting the error message from another trigger in sql server

i have a table 'Event' and it contains a trigger trEvent which updates table 'Position', and table 'Position' has a trigger trPosition which validates the Position records. whenever trEvent is fired ...
2
votes
2answers
40 views

Nested stored procedures and catch blocks - ERROR_PROCEDURE() issue

I have noticed something odd, and I am hoping someone in the community has an answer for me. I have noticed that when a stored procedure is nested it seems that all the blocks prior to last catch ...