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.

6
votes
3answers
161 views

Converted a Scalar function to a TVF function for parallel execution-Still running in Serial mode

One of my query in was running in serial execution mode after a release and i noticed that two new functions were used in a view which is referenced in the LINQ to SQL Query generated from the ...
1
vote
1answer
32 views

Database recovery pending on external drive

I have database with mdf and ldf file residing on external drive. There was a long powercut and the database restarted. Now the database has gone for recovery pending. The database is around 600 gb. ...
1
vote
0answers
25 views

is it possible to trim the table output for a specific job?

I have a routine to save the logins and apply them on a different server , and I run this routine from a job that I have scheduled to run everyday. On that job I have specified these 3 things: Log ...
1
vote
2answers
25 views

SQL Job Agent Service Account - Access is denied to run Powershell

I am trying to run a PowerShell script via the command line via SQL Job. "C:\Windows\System32\WindowsPowerShell\v1.0" "C:\Test\TestScript.ps1" I am running the SQL Job from a SQL Server 2008 R2 ...
4
votes
2answers
1k views

SQL Server Replication across firewall

We would like to set up replication between two SQL Server 2012 instances. Server "Internal" can see and make connections to server "External". External is completely firewalled from the internal ...
3
votes
1answer
4k views

Sync postgreSQL with SQL Server database- on data change event

I have one PostgreSQL database and its data are being changed regularly. I want to sync all its data with my SQL Server database. One thing I can do is use query to check all required tables and ...
1
vote
0answers
15 views

SQL Server replication - Could not add new article to publication

I have following script for replication that I am running in source database (subscription is not added yet, target database does not exist yet in target server): use [myDatabase] exec ...
0
votes
0answers
53 views

Differences between CTE and subquery select tables

Recently, I had to overcome a slow executing query, basically a job of transferring entries, in my organization. The query was in a format similar to the below select <some columns> from ...
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 ...
3
votes
3answers
68 views

SQL Server Internal Memory Error

I am using SQL Server 2016 Standard Edition that shows the following errors on DBCC CHECKDB and also on updating statistics. Version: Microsoft SQL Server 2016 (SP2-CU2) (KB4340355) - 13.0.5153.0 (...
1
vote
4answers
2k views

STRING_SPLIT and ordered results

Can you assume that the result of the string_split function returns elements in order? In short, is the following code correct? DECLARE @tags NVARCHAR(400) = 'clothing,road,touring,bike' DECLARE @...
1
vote
1answer
107 views

Compilation of T-SQL oddities and pitfalls

Like all programming languages, T-SQL has its oddities when compared to a generic programming language. Some of these have tripped us up. It would be nice if there were an already-compiled list so ...
0
votes
0answers
20 views

Create indexed view with fluctuating data behind

I´ve heard about indexed-views in MSSQL which can increase the performance. My problem is that I want to create a view which joins round about 5 tables. The tables are constantly updated and are ...
0
votes
0answers
17 views

SQLServer - Multiple PIVOT on same column

I have a table data like the below with Type Regular/Irregular from January to December. I need a query to do this data like the below table: January with below divided into two-jRegular & ...
14
votes
4answers
845 views

What is the appropriate index architecture when forced to implement IsDeleted (soft deletes)?

Currently, we have an existing database and application that is fully functional. I do not have the ability to change the architecture at this point. Today, each table in the database has an "...
4
votes
1answer
552 views

mssql server set up fail with errror on debian linux

I was trying to install SQL server on my Debian Linux. Installation part went well but when I tried to run setup file I am getting following error message. Is there any way to fix this issues. Thanks ...
14
votes
2answers
739 views

Is it possible for SQL statements to execute concurrently within a single session in SQL Server?

I have written a stored procedure which makes use of a temporary table. I know that in SQL Server, temporary tables are session-scoped. However, I have not been able to find definitive information on ...
-5
votes
1answer
34 views

SQL operators Doubt

I have a doubt about SQL. Sometimes is necessary to have some sort of ifs in queries, however, there are no ifs in SQL right? So it seems that we should handle these cases with logic operators but I'm ...
0
votes
2answers
2k views

SQL agent jobs hung state (SSIS jobs hung)

For a couple of weeks I have been experiencing a weird issue on SQL Server 2016 SP1. We migrated a critical server from SQL Server 2008 R2 to SQL Server 2016 with SP1 using side-by-side. We migrated ...
6
votes
1answer
76 views

Understanding Plan Choice When Query Store Doesn't Respect Forced Plan

I'm trying to figure out a situation that I encountered last week where, in an attempt to temporarily fix a plan regression during an incident, I forced an execution plan using Query Store, but found ...
24
votes
6answers
113k views

SQL Server database size didn't decrease after deleting large number of rows.

I'm no good in SQL, but I've got a database to maintain. There's almost no place left for it, so I've decided to delete all the data for, let's say, year 2008. After executing delete query (had ...
0
votes
0answers
29 views

My Temp db is full [duplicate]

What to do when tempdb is full ? I would like to know if tempdb has become full and what to do if you can't truncate the log? I want to understand the process why you can't truncate it.
0
votes
0answers
20 views

pivot vs multiple columns

I have a table that I've been tasked to look at for optimization (both for entering data through stored procedure, and for views based off the table). The table has (currently) 115 columns, 2 make up ...
5
votes
1answer
8k views

How does the operator “&” work in sql server?

I was running a trace on one of our test servers when someone did this: and one of the queries I could catch in the trace was: declare @UserOption int select @UserOption=convert(int, c.value) from ...
2
votes
3answers
84 views

Is there a way to retrieve the logical filename from a backup file?

I would like to write an automation script that restores a SQL Server DB from its backup file. However, doing this is not a straightforward procedure in SQL because the main query needs additional ...
3
votes
2answers
47 views

Merge Only Events that Start immediately after the Previous Event without any Gap

How can i merge only the dates occur subsequently.? (i.e, The Next Event begins immediately after the previous event completed). Here, we need to consider some other Columns also for getting the ...
17
votes
2answers
31k views

Tracking stored procedure usage

Besides using SQL Server Profiler, is there any way to track which stored procedures are being used, or at least when they were last executed?
3
votes
1answer
446 views

Why Does SQL Server OPENQUERY To Oracle Return Different Column Types On SQL Server 2016 to 2008

I have two SQL Server Instances on the same machine, one 2008 and one 2016. Both instances have a linked server connection to the same Oracle 12 Server. If I run the following query 2008 returns 1.23 ...
1
vote
2answers
50 views

Query takes 10+ minutes to execute

I'm supposed to find out why this query takes more than 10 minutes to execute. Things is SQL is not my strong suit. I know it has to do with all the group by in part... But what approach am I supposed ...
0
votes
1answer
11 views

Multiple inserts using select row and variable combination with one SQL statement

I'm working with SQL 2008 R2 and have a need to find a specific row in a table that has three fields and then insert multiple rows into the same table with each insert using two values from the found ...
2
votes
1answer
35 views

How can you have a Buffer cache hit ratio of 100% with PLE of 103? [duplicate]

I found What does a "Buffer cache hit ratio" of 9990 mean? (I was getting result of 9000+ from a blog that was wrong) and used the solution by LowlyDBA/Denis Gobo combined with the PLE ...
11
votes
1answer
645 views

If a VARCHAR(MAX) column is included in an index, is the entire value always stored in the index page(s)?

I'm asking this out of curiosity, being inspired by this question. We know that VARCHAR(MAX) values longer than 8000 bytes are not stored in rows, but in separate LOB pages. Subsequently retrieving a ...
0
votes
1answer
84 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 ...
1
vote
0answers
20 views

How to setup encryption on a column that joins two tables in two different databases

What is the best method to encrypt a column that exists in two tables (BUID) in two different database on a 2012 server, at the end should be able to join on the encrypted columns between two tables ...
0
votes
2answers
577 views

Statistics update on Azure SQL DB

New to Azure SQL DB. I was wondering what is the best practices for scheduling jobs like update statistics in Azure SQL DB. Googling got a couple of blogs that uses Azure automation. https://blogs....
1
vote
1answer
45 views

I want to change to Dynamic SQL Query instead of CASE statement

I have two tables as below and description is given in below Employee table id EmployeeID departmentid department1 department2 department3 1 300 100 IT Finance ...
0
votes
1answer
14 views

SSIS script task - Execute Dataflow task based on variable value

I have been trying to create a package which executes a couple of dataflow tasks based on the available value in one of the variables. How can I achieve this? The sample scenario: Variable param may ...
0
votes
1answer
43 views

Drop the user from Read only Secondary database Always ON server

How to drop a user from the secondary database in an AG when it is in read only mode and synchronizing in SQL Server 2012? The login and user have been deleted from the primary database, and we are ...
4
votes
3answers
67 views

Is there a problem creating only differential backups every hour?

I have three databases which are not big. Every differential backup is roughly 50MB, then we have a full backup at midnight. Is there any problem doing this? I could take the full backup then logs, ...
1
vote
1answer
19 views

Is there any way to find information of index fragmentation, Statistics information with the help of system tables in SQL Server

I am working on a report which will give information for different segments like blocking process, slow running queries, disk space etc. I also want to add one more segment where I can get information ...
0
votes
2answers
65 views

Issue migrating from SQL Server 2008 R2 to SQL Server 2017 Standard (on Linux)

I am currently trying to carry out a database migration from SQL Server 2008 to SQL Server 2017 (Linux) using the DMA tool. I get the all green after running the assessment, however I get hit with ...
47
votes
2answers
67k views

How to join a table with a table valued function?

I have a user defined function: create function ut_FooFunc(@fooID bigint, @anotherParam tinyint) returns @tbl Table (Field1 int, Field2 varchar(100)) as begin -- blah blah end Now I want to join ...
2
votes
3answers
16k views

How to generate list of dates — ie every day for the past 2 years?

We're using a business intelligence system and need to load a list of dates so we can flag them as 'last week' or 'last 12 months' or some dynamic value. I'm wondering what's the simplest way to ...
20
votes
3answers
18k views

Blue icon with question mark - what does it mean?

The SQL Server instance is accessible and seems to be fine. Microsoft SQL Server 2016 (SP1-CU2) (KB4013106) - 13.0.4422.0 (X64) Mar 6 2017 14:18:16 Copyright (c) Microsoft Corporation ...
3
votes
1answer
687 views

Shirking of Log file on Primary Server of Transaction Log shipping

I have a database with size 200 GB Total(mdf+ldf) and Log file(ldf) with 50 GB and 99% unallocated space in ldf file i.e. not being used at all. We had setup log backups for every 5 minutes, so there ...
1
vote
1answer
22 views

Powershell Invoke-Sqlcmd Replacing £ with?

When I create a SQL Script that inserts the value '£' or '€' into a table the value on the table is a question mark (?) Though, when I insert '$' it inserts a dollar sign. Anyone know what is going ...
2
votes
1answer
355 views

Has anyone connected to Oracle from SSIS using Oracle Driver ( 12.1.0 ) from VS 2017 Data Tools SSIS

I have installed Oracle Driver ( 12.1.0 ) and I have VS 2017 Data Tools SSIS . The connection to Oracle Service from local machine via ODBC 64 bit on my machine is working. Connection to Oracle ...
10
votes
3answers
2k views

How does one investigate the performance of a BULK INSERT statement?

I am mainly a .NET developer using Entity Framework ORM. However, because I don't want to fail in using the ORM, I am trying to understand what happens within the data layer (database). Basically, ...
2
votes
1answer
78 views

An error occurred on the always on dashboard

I have a setup of Windows Server Failover Cluster and i am using SQL Server Service with one DC, One Primary Server and One Secondary Server. During Using SQL Server Management Studio, when i click ...
0
votes
2answers
27 views

ssms does not let me change to server authentication mode and I can not log in

I have been using SSMS and logging in through server authentication for a week now and yesterday when I tried to login it suddenly told me "no process on the other end of the pipe". I researched ...