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
2answers
39 views

Attach MDF File To SSMS [closed]

I am attempting to attach a .mdf file to my SSMS install. I go through the steps and at the very end I get this message. What is causing the .mdf file to not attach? Also, I have verified that the ....
-4
votes
1answer
55 views

Earliest version of SQL Server with OPENJSON

What is the earliest Microsoft SQL-Server version having the T-SQL OPENJSON function? As far as I could see before asking this question, the Microsoft manual shows the function's documentation for ...
9
votes
2answers
140 views

Prevent SSMS from seeing the server's file system

I have several users who share an MS SQL server 2017 under my administration. They should not see (or even be aware) of the other users and their data on that server. Each user has his own database. ...
3
votes
1answer
180 views

Order by inside subquery

Suppose that i select into a temptable as follow by performing grouping from an existing table, and this is current temptable value: Title Count ------------- Sr. 11 Mrs. 2 Sra. 3 Ms. 415 Mr. ...
8
votes
2answers
312 views

Why doesn’t this recursive CTE with a parameter use an index when it does with a literal?

I am using a recursive CTE on a tree structure to list all the descendants of a particular node in the tree. If I write a literal node value in my WHERE clause, SQL Server seems to actually apply the ...
1
vote
1answer
33 views

Selecting specific table for each subscriber when using merge replication in SQL Server with multiple subscribers

I am using merge replication with SQL Server 2017. I have one publisher, and two subscribers (all in all 3 database servers). I need to replicate different tables to each subscriber. Is this ...
0
votes
2answers
53 views

Sync database table schema and stored procedures

We have 2 databases. One is at our server, the other is local. We need to sync them both in a manner that local database has all data and server database has no data only empty tables and stored ...
0
votes
1answer
49 views

How do I recover deleted rows from one table while having Recovery SIMPLE Model? I am using Microsoft SQL Server 2014 [duplicate]

How do I recover deleted rows from a table while having Recovery SIMPLE Model? Database is in Microsoft SQL Server 2014. I have managed to find them in fn_dblog table before data is overwritten with a ...
0
votes
1answer
37 views

Is It Okay to Leave the Connection Open and Set CommandTimeout for Long Durations?

I have to process daily files and it's a long process that deletes, imports and updates processes. Like literally 20 to 30 minutes per file for several files a day. ** EDIT: I also have some ...
1
vote
1answer
78 views

RESTORE database now in Emergency

We have a database that was basically used for application purposes but it was using "master" - bad indeed. We created a new database called school that is now being used (same structure as how master ...
0
votes
1answer
22 views

Trigger: Insert Into For Update IF Update - but with custom insert based on what type of update?

CREATE TRIGGER [dbo].[Logging] ON [dbo].[TriggerTest] For UPDATE AS BEGIN IF UPDATE (ColumnName) Begin INSERT INTO [dbo].Logging( CorrelationID, DateTimeStamp, ...
0
votes
1answer
79 views

SQL query stuck in suspended state

I have a query that is run as part of a larger transaction. The query usually completes in under .5 seconds. It looks like it became stuck in a suspended state indefinitely until I killed the query. ...
6
votes
1answer
130 views

Inserting with implicit type conversion causes warning for cardinality estimates

I noticed this while doing some performance testing recently. When I insert a value into a column that will require an implicit conversion (e.g. bigint into nvarchar), I get a warning: Type ...
0
votes
0answers
39 views

substitute a value in one record with a value in another record based on a value comparison across the records

Generally, I want to substitute a value in one record with a value in another record based on a value comparison across the records. More specifically, I want to assign a name value to the records ...
2
votes
0answers
110 views

Sporadic SSRS Report Performance

I have an SSRS instance with a dashboard report that frequently runs 100-200 times per minute throughout the organization. Most of the time, the report runs quickly with consistent times for data ...
-2
votes
2answers
38 views

Select data based on XML attributes

My table contain two columns: Branch and data Here's an example: Branch having value finance Data having value xml packet i.e. ...
6
votes
2answers
158 views

Eliminate key lookup in execution plan

I have the following query: DECLARE @p__linq__0 UNIQUEIDENTIFIER SET @p__linq__0 = '... some guid ...' SELECT TOP 1 [EventId] AS [EventId], [DateCreated] AS [DateCreated], ...
10
votes
1answer
198 views

What is a “hashed transaction” in SQL Server Replication terminology?

sys.dm_repl_tranhash contains information about the hashing of transactions, but I have difficulties understanding the terminology. What is a "hashed transaction"?
8
votes
2answers
194 views

Lob Logical Read and lob read-ahead reads in NCCI [duplicate]

I have a test table with following structure. CREATE TABLE [dbo].[DW_test]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [CourtCaseID] [int] NOT NULL, [ActionID] [int] NOT NULL, PRIMARY KEY CLUSTERED([ID] ...
-1
votes
1answer
61 views

Prepared statements and terrible performances

There was an application that was changed, so that all queries are performed using prepared statements, rather than regular statements. What happens is that, for every query, the prepared statement is ...
-2
votes
1answer
55 views

Operand type clash: int is incompatible with datetime2

Having issues with the code below. ERROR: Msg 206, Level 16, State 2, Procedure usp_TRAC_PSE_Step_12_Resource_Request_OBJECT-Production-Part1, Line 93 Operand type clash: int is incompatible with ...
4
votes
1answer
76 views

Always On Primary Replica On Prem, Secondary Replica On Azure

We want to create an Always On Availability group where the primary replica will be On-premises and the secondary replica will be on an Azure VM. I have researched and I know it's possible to do this, ...
13
votes
1answer
367 views

Forced plans on readable secondaries

If a plan is forced on the primary in an Availability Group, is it applied to queries run on a secondary? I'm looking for answers that cover both possibilities for plan forcing: Plan Guides Query ...
3
votes
2answers
98 views

Is it normal to share CPUs on a VM of SQL Server?

Our IT set up a SQL Server as a VM on a big VMWare box that contains other VMs. The CPUs are setup as shared. As a result, any queries that might need multiple CPUs take 30x longer than if I limited ...
0
votes
0answers
28 views

Trigger to insert new row into second table causes “Connection is busy with results for another command”

I have created this trigger to insert new row into table @AL_ORDR_TRACKER after new row is inserted in table ORDR. CREATE TRIGGER dbo.trgAfterInsertORDR ON dbo.ORDR AFTER INSERT AS BEGIN INSERT INTO ...
-1
votes
1answer
51 views

Postgres vs SQL Server [closed]

Can anyone share proper advantages-disadvantages? If possible, I am trying to search for really great benchmarks between both, so please share it with me. Also, which will be best between them for ...
0
votes
0answers
52 views

Get the scope role for each employee

I have the following query, for each employee I get the list of his different roles : SELECT e.EmployeeId,r.EntityRoleId, r.Label,er.ValueId FROM Employee e LEFT JOIN Employee_Role er ON e.EmployeeId ...
4
votes
1answer
63 views

Will SQL Server “int” datatype reliably truncate (and not round) decimal values when they are input?

I have a user with software that sometimes sends back a UNIT's ID as an integer(ex. 1000), and sometimes with a small decimal value appended (ex. 1000.001). The software automatically generates MSSQL ...
2
votes
0answers
56 views

BulkAdmin Role Doesn't Work

There is a query that our BI team use. They can not execute the query because of the error below. Ad hoc access to ole db provider 'MSDASQL' has been denied. You must access this provider ...
2
votes
1answer
39 views

Azure Managed Instance - Is it possible to send Sql Logs To Log Analytics?

Is it possible to stream a Managed Instances SQL logs into log analytics? I need a single location for all Azure based monitoring so that multiple teams have visibility/access to log information for ...
0
votes
2answers
57 views

Performance of a linked server between two instances of SQL Server on the same virtual machine

I would like to set up log shipping between SQL Server 2014 (main) and SQL Server 2016 (secondary). The thing is I want to set the secondary DB in STANDBY. Unfortunalely this setup is not supported by ...
1
vote
1answer
72 views

Database Design - hierarchical structure

We are trying to redesign a database that currently holds thousands of parameters for different datatypes and currently has a flat data structure. We generate XML files from the database, which our ...
0
votes
1answer
57 views

SQL Server - Get a new columns with name of the columns have the values X base on rows

I want to get a new columns on table and this new columns need to contain another columns name separate by - example: (field_1-field_2) where the values = X I can not hard code the column names ...
0
votes
1answer
46 views

Failed to insert records into table in different server using sql server agent

My idea is to insert records from a table into another one in different server using T-SQL. I've made maintenance plan along with its job and found below error. Executed as user: NT SERVICE\...
1
vote
2answers
56 views

How to create a SQL Server user that can restore databases to a database server?

I want to create a new user that can create new databases on my database server. I've tried: CREATE LOGIN [my_db_restore_user] WITH PASSWORD = 'passwordhere'; GRANT ALTER ON ROLE::[dbcreator] TO [...
4
votes
2answers
116 views

Search for values in xml data

I have a table of names: Table A John Jim Jason and a table of xml strings: Table B Example 1 <Show Title="" ShowTitle="=False" ShowLine="=False" ShowDescription="=False" ...
2
votes
1answer
77 views

Connect to SQL Server 2008 R2 from Windows 98

Is it possible to connect to SQL Server 2008 R2 from a Windows 98 machine? If it is possible, what would I use? I have an old testing machine that currently writes to an access database. This ...
2
votes
1answer
30 views

Troubleshooting high network traffic between primary and secondary replicas

We have two SQL server hosts. There are about 12 availability groups, where the primary replica is on the "primary" host and the secondary replicas are on the "secondary" host. Now, for one reason or ...
5
votes
1answer
58 views

Performance Metrics After Enabling Auto-Stats

Short Version After enabling AUTO_CREATE_STATISTICS & AUTO_UPDATE_STATISTICS_ASYNC on a database, what performance metric will best display the changes? Long Version We just realized that the ...
4
votes
2answers
59 views

Display local variable in while loop

I have a while loop that is running for days (bydesign) see related Is there a limit to how many backups can be appended to a single file? I have a local variable that displays the loop count, when I ...
4
votes
1answer
37 views

How to return 0 for record count when nothing returns for that hour using datepart?

In my sample query below, I am pulling the count by hour correctly, but I need it to return all hours of the day and display 0 for the hours that have no data. Is there an easy way for me to do this? ...
14
votes
4answers
4k views

RAID0 instead of RAID1 or 5, is this crazy?

I'm considering using a RAID0 setup for one of our SQL Server clusters. I'll outline the situation and am looking for why this may be a bad idea. Also if someone you have use cases, white papers or ...
2
votes
2answers
49 views

SQL Native Client 11 - SQL Server 2017

We currently connect to SQL Server 2014 with the Native Client without any issues. However, the SQL Server is being upgraded to SQL 2017. I have been told that SQL Native Client will no longer work ...
5
votes
1answer
159 views

SQL Server - How to achieve READCOMMITED and NOLOCK at the same time?

If I have a User table: id | name | age 1 | Mateus | 27 The first transaction executes an update, and leaves the transaction open, without committing or rolling back: update User set name = 'John'...
0
votes
1answer
49 views

SQL Cannot patch the latest CU4 for SQL 2014 SP3

I cannot patch the latest CU4 for SQL 2014 SP3. Checked "view installed program", 4500181 is already installed. but when I use "Select @@version" against my SQL instance, it still shows "12.0.6259.0"...
3
votes
2answers
107 views

Can subqueries produce different execution plans than CTE's?

I was talking with the bossman, and he prefers subqueries over CTE's. Personally, I loathe subqueries. He mentioned that subqueries can be faster, but I am not convinced. I ran this short test: ...
2
votes
1answer
51 views

How to remove Alter, Drop, Create SQL from deployment script created in VS 2015 using SSDT 14.06

I need to remove the below auto-generated deployment script for my SQL project. I am using VS 2015 with SSDT 14.06. Would this be something that can be set in the Advanced Deployment settings under ...
-1
votes
1answer
45 views

Creating database with only ldf file created 2 .ldf file

When I run this query without specifying the ldf file it automatically creates the ldf file. Declare @DBname nvarchar(50) = 'Test' Declare @path nvarchar(50) = 'D:\DB' Declare @query nvarchar(max) ...
0
votes
1answer
48 views

Where clause in select does not work with Inequality of guid

I want to query our CRM Db. I have a backup of CRM Db and the existing one. There is a custom entity,in both of them I have created. Some of my custom entity records has deleted from the existing db. ...
-3
votes
1answer
43 views

SQL to update part of data field

I am looking to put an SQL update statement together to remove an ampersand sign (&) for customer names - I need to replace it with the word 'and'. This will be for multiple customer records. ...