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

Cannot use 'STWithin' as it can't be found for type 'Microsoft.SqlServer.Types.SqlGeography' (SQL Server V14)

I am currently using the STIntersects method to spatially match a geographic point to a geographic area. I am working with Geography types here not Geometry. Historic code from the place I work shows ...
1
vote
1answer
23 views

Select distance between cities in two directions

I have a distance table and a cities table. I have joined them in a way where i now have: cityA_id,city_A,cityB_id,cityB, distance in km i need to get the distance betweens cityA and CITY B and then ...
0
votes
1answer
45 views

I am wanting to use UDTT in my database, but we have CLR turned off, and according to the docs for create type, all types require a CLR assembly

Is this true? Or can I use User defined Table Types with having CLR off? (I.e. they don't require the use of CLR) If they do require CLR, is there an alternative I can use for my stored procedure ...
4
votes
3answers
488 views

MS SQL Server 2014: which stored procedure is my error in?

As a general question or three on Microsoft SQL Server 2014, that perhaps I should know the answer to already, or even perhaps did once - When my stored procedures run other stored procedures, how can ...
0
votes
2answers
45 views

Best way to determine Dev/Test/Prod environment in MS SQL Server 2012+?

We have 3 MS SQL Environments: Dev, Test, and Prod. There are many Dev servers, and 1 each in Test and Prod. What is the best way to determine the "Environment" from a query? For instance, ...
0
votes
1answer
21 views

Transaction Log backup in MSSQL's HADR

I'm a resident developer who is voluntold to be a database admin of MSSQL 2016. Please bear with me as I'm still trying to self taught the art of DB admin. I recently setup a DR solution using the ...
0
votes
1answer
55 views

Powershell script executed by SQL Server doesn't take effect

I'm doing some testing and I want to execute a PowerShell script through xp_cmdshell. Below is the PowerShell script (c:\temp\sqltotext.ps1). echo "haha" | Out-File "c:\temp\haha.txt&...
0
votes
2answers
60 views

SELECT DISTINCT in extra space data from a single column ( no duplicates )

Hello i am having problems with this column data cities Charlotte Cincinnati Cleves Columbus Fairfield Fresno Los Angeles -- HERE IS THE PROBLEM Los Angeles Madison Manhatttan Beach New York Orange ...
0
votes
0answers
45 views

Sql Server can't stop

Today I had a problem with the SQL Server. The SQL Server has crashed and I can't connect to it. When I try to restart the SQL Server it remained in the STOP_Pending state for 30 minutes, then I ...
0
votes
1answer
35 views

Optimize BETWEEN Query in SQL Server Azure

I have a table that stores time-series (5-minute) data and am trying to run a query that has a BETWEEN clause included in it. Below is the table structure:- Timestamp | ComponenentID | Parameter1 | ...
1
vote
1answer
28 views

How is data of multi valued attribute say phone number of person relation is stored in table?

I have searched for the answers referencing my books as well as web but i could not find one. So here i am. I am not sure what this question is trying to ask so help if anyone could.
5
votes
2answers
380 views

What happens when you change a heap into a clustered table in SQL Server?

As I understand, a heap is an unordered object. When you insert a record, SQL Server use IAM page to get the pages that belong to this heap, and use PFS page to find a particular one which has enough ...
-1
votes
1answer
30 views

How to view size of clustered index for a given table? [duplicate]

Inherited the following audit table: Table UserActions AppID int ActionTime datetime Action varchar(25) UserID int Description text Clustering key is on AppID, ActionTime, Action columns. RowCount = ...
13
votes
1answer
2k views

Is this an error in SQL Server's documentation for ROLLBACK?

This is the SQL Server's document talking about the ROLLBACK statement. On that page it states that the syntax of it is as follows: ROLLBACK { TRAN | TRANSACTION } [ transaction_name | @...
0
votes
1answer
26 views

SQL Server cross-forest migrate tasks

I’ve been doing cross forest migrations now. We have stand-alone some SQL Server machines. How can we migrate my database machines to target forest ? Is there any pre-migrate / post-migrate tasks ? ...
2
votes
3answers
105 views

Urgent help with TempDB log file growth mystery

I need some urgent guidance for a tempdb issue for past last week: The tempDB log file is growing starting a specific time, lets say 2 PM. And almost continue to grow upto 150 GB and then comes down ...
1
vote
4answers
98 views

Why can't you add an Identity to an existing colum in SQL Server?

What is so special about an Identity column? I am pretty sure there is a valid reason, but I'm trying to get a better understanding of SQL. So what is so special about an Identity column that this can'...
2
votes
3answers
94 views

Table scan instead of index seeks happening when where clause filters across multiple tables in join using OR

We have an application generated query using a view that has two tables joined on a LEFT OUTER join. When filtering by fields from just one table (either table) an index seek happens and it's ...
0
votes
1answer
42 views

How do you handle maintenance window for DDL commands on Primary replicating to readable secondary AGs

We are currently running into this issue for one of our availability group design:- On Primary :- Nightly Table switch/Purges are happening (DDL) for approx. 2-3 hours On Secondary which is configured ...
0
votes
1answer
41 views

INSERT-ing to a table if the the data does not already exits - how do indexes peform in this scenario?

When you are in the position of inserting data into a table if the data does not already exist ...what is the best way to go with indexes? I'm specifically thinking of SQL Server here but I assume the ...
0
votes
0answers
19 views

Scope of memory optimized table variable

I have a stored procedure Schm.tmp_Sproc where I'm using memory optimized table variable @memTableVariable CREATE PROCEDURE Schm.tmp_Sproc @user INT, @data VARCHAR(100) AS BEGIN DECLARE @...
0
votes
2answers
33 views

Backup and restore SQL Server database with encrypted columns: what should I backup along with database?

Assume I've backed up a SQL Server database with Management Studio, and that database have some encrypted columns. Now I want to restore this database into another fresh-enrolled MSSQL server. To use ...
1
vote
1answer
26 views

How to apply Static Data Masking With Replication of Database

We used to have multiple environments for applications such as Production, Staging, UAT, Dev, and Sandbox. It is a regular task for the DBA to refresh the lower (staging, test, UAT etc.) environments ...
1
vote
1answer
24 views

Selecting data from a mapping table using a foreign key

I'm trying to make a simple Login sys where each user should be shown data from a list depending on their user ID Using MS SQL. After searching for how to do it best i came across Mapping (pretty much ...
1
vote
2answers
38 views

SQL SERVER ERRORLOG FILE UTILIZING THE FREE SPACE

I'm stuck up in one of the issue. I'm Using SQL SERVER 2008 R2. The SQL SERVER Log file is utilizing the disk space. When ever I clear the space in the DRIVE, the SQL SERVER Error log file starts to ...
-2
votes
0answers
53 views

the Shore to Shore Shipping case study for SQL [closed]

1 Find all manufacturers who have built fewer than 2 ships that have shipped Concrete Forms and have never shipped Toyota Camrys. 2.List out the total of each item carried by a shipment piloted by ...
0
votes
1answer
56 views

What is the fastest way to make a copy of a 5TB SQL Server DB on the same server without having to fully reinitialize the DB to add it to the AG

I have a 3 node Always On AG that hosts a 5TB database. Due to business requirements, at 12:01am on the first day of every month, I need to create a copy of that database on the 3rd node (replica) ...
0
votes
1answer
38 views

Take a “slice” of a SQL Server database: for all tables, get only the rows that join (directly or indirectly) to a specific row

For audit purposes, I'm trying to obtain a copy/export of a DB but including only the rows from each table that have relevance to a small collection of rows in some table. So every row of every table ...
-2
votes
1answer
45 views

Would like to see what Credit Card Numbers have changed [duplicate]

Using the following SELECT Statement, I see all active drivers with a Credit Card Number : SELECT top 1000 * FROM cashcard WHERE crd_status = 'A' Based on that query, I get a table with a bunch of ...
0
votes
1answer
38 views

sp_Blitz - RegOpenKeyEx() returned error 2

I'm running the latest sp_Blitz script on a SQL 2016 Std server on a Windows Server 2016 physical box. I'm getting the following error. What could be causing this and how do I troubleshoot it? ...
0
votes
1answer
26 views

The Windows Server Failover Clustering (WSFC) resource control API returned error code 19

What is root cause of this error. I couldn't find any related information. There are two nodes on Availability Group and if you reboot second node, database wont up.
0
votes
0answers
42 views

Readers blocking reader on readable secondaries

We recently migrated to AG setup for one of our VLDB's around 20+TB. Current setup is Multi subnet AG with primary accepting mix workload while heavy reporting offloaded to readable secondary replicas....
0
votes
0answers
24 views

SSAS Clustered Instance - no connection could be made because the target machine actively refused it

I have a clustered SSAS Tabular named instance (TAB_PROD) running on SQL Server 2016 SP2/Windows 2012 R2. When the instance is running on Node1 in the cluster I can connect to it using SQL Network ...
0
votes
0answers
31 views

How to migrate a SQL Server Erwin Mart to database to Aurora (Amazon RDS) [closed]

I want to migrate a SQL Server Erwin data Mart database to Aurora and trying to figure out what the easiest/quickest way to do that is. Options to me seem to be: Saving models to the file system, ...
1
vote
3answers
110 views

Is it worth adding or rebuilding an index on a table already involved in an active query

I've kicked off an SQL Server query that has been going for 12 hours. Annoyingly I forgot to rebuild my indexes on one of the tables involved, I needed to do this as they are over 95% fragmented due ...
0
votes
1answer
21 views

How to configure account to be able to create/delete folders and files?

I have a newly created domain account (domainname\user1). This will be used by SQL server agent job proxy credential to run a SSIS package that creates/deletes files and folders on the same computer ...
-3
votes
1answer
52 views

MS SQL Server 2008r2 not starting. Error 3414 [closed]

Unfortunately I do not have recent backup.... Sql service is not starting. I am unable to connect to database from management studio. After power failure sql server 2008 production system not starting ...
1
vote
1answer
36 views

How to get an actual execution plan from the linked server?

There is a "main" SQL Server to which I have full access and I can connect to it via SSMS. Its version is: Microsoft SQL Server 2016 (SP1-CU15-GDR) (KB4505221) - 13.0.4604.0 (X64) Jun ...
6
votes
2answers
1k views

How can I eliminate this scalar function or make it faster?

I'm working on pretty lengthy view and one of SQL statements calls a scalar function, which really degrades the performance. The following is the function that is called in a select statement. (sql ...
0
votes
0answers
19 views

SQL Server 2019 on Ubuntu Throwing security.crypto.symkey errors

I'm repeatedly seeing the following error, seemingly for every connection to SQL: 04/06/2021 16:39:25.890647613 Error [security.crypto.symkey] <0000004241/0x00000c34> Setting Variable Length ...
0
votes
2answers
58 views

Query that returns a unique ID for each different clause matched?

I'm in a situation where I'm being submitted a (potentially quite long) list of "match entities", each of which contains user data to match, along with a unique ID for that match information....
0
votes
0answers
51 views

The SQL Scripter library is running a SELECT COUNT(*), how do I stop it?

I'm using the Microsoft.SqlServer.Management.Smo.Scripter library from within a PowerShell script to pull SQL object creation scripts from a DB as part of an automated process. The user account used ...
3
votes
2answers
88 views

Blocking on readable secondary replica

We recently migrated from LogShipping standby/read-only setup to Multi Subnet AG setup with readable secondaries. Generally on old setup we have select queries running for longer duration as the ...
0
votes
1answer
28 views

IP Addresses tab is not showing a specific IP Address

I have SQL SERVER 2008 R2 installed on windows Server 2016, it runs smoothly on a LAN network interface. I Added a USB MODEM (4G) to the server and went to the IP Addresses tab in network ...
3
votes
1answer
85 views

SQL Server sp_describe_first_result_set: find user defined types

I need to find the column definitions from a stored procedure.The docs for sp_describe_first_result_set show an entry for user_type_name where I'd expect to see my User Defined Types. Sadly, data for ...
1
vote
1answer
154 views

Clone a database from SQL Server 2019 to 2017 [duplicate]

I try to clone a database from an Sql Server on my local machine v15 (2019) to a server machine v14(2017). I did a .bak file and when I tried to import on the server side it says: System.Data....
-1
votes
2answers
43 views

How to combine multiple columns values into one column value?

I have few columns which has values either 1 or 0. Final result i have to store in one column(F), Which should perform OR operation.
0
votes
0answers
47 views

Update query performance improvement (MS-SQL)

UPDATE M SET ARCHIVE_FLAG = 'N' FROM MARKPOSTED_TMP M WHERE EXISTS (SELECT 1 FROM SHFMKPSTMM S INNER JOIN SHIFT_TMP S1 ON M.MARKID = S.MARKID AND S.SHIFTID = S1.SHIFTID AND S1.ARCHIVE_FLAG <> '...
0
votes
2answers
34 views

Read Committed Shared Lock

I wanted to know when you are at read committed isolation level and in one session someone started an explicit transaction which has a select statement, say it takes 20 seconds. Can someone from a ...
1
vote
1answer
19 views

Where does kerberos tool need running and settings configured?

I am reading this article on how to link 2 sql server instances using kerberos: https://www.sqlshack.com/how-to-link-two-sql-server-instances-with-kerberos/ I am not able to understand which computer ...