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.
26,615
questions
-1
votes
1answer
7 views
Do table locks get released after a query finishes when WAITFOR is used after that query within the same batch and transaction?
If I have a query that reads from a table, if that query is proceeded with the WAITFOR keyword (within the same batch and transaction) will the table lock of the preceding query be held until the ...
0
votes
0answers
11 views
Nullable smallint column usage in a CASE Statement nested in a STUFF function
I'm having issues writing a Stored Procedure that uses a CASE statement inside of a STUFF function. I thought my issue may have to do with Restricted being a nullable smallint. I tried converting it (...
4
votes
1answer
47 views
If a stored procedure is altered while it's currently being used in a cursor, does the cursor continue using the old query before it was altered?
If a stored procedure (or view) is currently in use within a long running cursor, and I alter that stored procedure, will the cursor continue using the old instance of the stored procedure until the ...
1
vote
0answers
20 views
PolyBase to CSV File
I'm trying to see if it's possible to create a PolyBase connection to a .CSV file. I'm using the Microsoft Access Text Driver and have set up a system DSN to the folder where the csv file is located. ...
1
vote
0answers
19 views
Imported SSIS package fails if imported with SSMS on the SQL server, but works when imported from a remote PC
I am facing a strange issue on SQL Server 2017 Enterprise edition running in AWS on EC2.
When we are on the SQL server and we import an SSIS package using the locally installed SSMS connected to ...
1
vote
1answer
39 views
SQL Server indexes and record deletion
I've inherited a large production database in which one of the tables has 72 million rows and is inserted/updated thousands of times during a day. These rows have built up over 3 years and they are ...
0
votes
3answers
58 views
How to SELECT a column based on a condition on the column's name?
I have the following table named MyTable:
+------+--------+--------+--------+
| Code | U_Col1 | U_Col2 | U_Col3 |
+------+--------+--------+--------+
| 123 | 1 | 2 | 3 |
| 456 | ...
0
votes
0answers
27 views
Prevent compilation of a stored procedure to a linked server
I have a stored procedure, that queries a view on a linked server.
This works fine on prod, but our developement server has some firewall restrictions, that prevent the compilation. This is no ...
6
votes
1answer
70 views
Why do we have Top N Sort in this example?
In the example below the result of Index Spool is already sorted, why do we have Top N Sort here instead of simple Top?
use tempdb;
go
create table dbo.t1 (id int identity primary key, v int);
...
2
votes
1answer
23 views
Permission Denied for a User that is part of AD
I have a group created in Active Directory. This Group contains 3 users. For some strange reason one of the user within the group can't select from specific type of SCHEMA whilst the 2 other users ...
0
votes
1answer
18 views
Update a record whilst creating a link to a parent record
Someones account was deleted from a system we have. They created a new account for her and moved on. But it turns out she wasnt deleted from SQL - when they delete in the app, the record is marked as '...
0
votes
2answers
30 views
Sql Server AG assigning wrong listener
I have 2 instances of SQL Server on two Windows Servers, namely:
D-MSSQL01-DE (v.2014)
D-MSSQL02-DE\SQL2019 (v.2019)
And seperate server for AGs:
D-MSSQL02-DE (v.2014)
D-MSSQL02-DE\SQL2019 (v....
3
votes
0answers
17 views
MSSQL Server XE column client_connection_id accessible through system view/table for auditing?
In our .NET application we create a SqlConnection to talk to the database.
After the connection is opened, we get the ClientConnectionId, a GUID.
Within the extended events I can manage to trace the ...
-1
votes
0answers
23 views
IF Employee Taken Leave On Saturday & Monday How To Consider Sunday As Absent in sql server
I have data in this format:
staffid AttendanceDate day DayStatus AttendanceStatus
EMP0000043 01-10-2019 1 P P
EMP0000043 02-10-2019 2 P LA
EMP0000043 03-10-2019 3 P LA
EMP0000043 ...
3
votes
3answers
223 views
Reducing table scans using group by and window functions
There is a code that I'm trying to improve that looks like this(simple example):
SELECT DISTINCT a.col_a
,COALESCE(b1.col_c, b2.col_c, b3.col_c)
FROM tab_a a
LEFT JOIN tab_b b1
...
0
votes
3answers
68 views
How to avoid tons of lower(column) in WHERE clause for several LIKE comparisons?
I've got to filter data in a string column and thus need to compare the column using NOT LIKE with a bunch of strings.
I'm using SQL Server and my code looks like this:
SELECT history.date,
...
0
votes
3answers
29 views
Adding Analysis Service to existing SQL Server 2017 Failover Cluster
I have a requirement to add Analysis Service to an existing SQL Server 2017 Failover Cluster. When trying to add it as a feature, following error comes during check.
Then I tried to skip the rule for ...
0
votes
1answer
12 views
Survey Database Structure
I am building a database to feed into a PowerBI dashboard.
The data is extracted as CSV (SurveyResults) from Survey Monkey which will be loading into the MSSQL database.
The outcome i am after is ...
0
votes
1answer
37 views
How do I force the delete of users from my database?
Background: I am making a clean .bak of our dev database for easy fresh spin-ups of the database. This database has about 200 test users that should be deleted to make it truly clean, but the problem ...
1
vote
1answer
54 views
Alternative of JOIN with BETWEEN
I have two tables to JOIN based on BETWEEN condition.
Table 1 is small is small around 1500 records and Table 2 is of 40 millions records. Table1 is having only one column with the datatype bigint ...
0
votes
2answers
34 views
comparison operator in count() is not working [on hold]
The query (SELECT 15 > 14 FROM Duel) Working in MySql, but when I tried in MS-SQL Server getting following error:
SQL Error(102): Incorrect syntax near '>'
I have a requirement where I want to ...
-1
votes
0answers
25 views
What is the most efficient and effective way to learn SQL? [on hold]
I need to learn SQL for a current project. What recommendations do you have on the fastest way to learn SQL Server Management Studio?
2
votes
0answers
7 views
SSIS package deployment. Changing protection level takes a very long time
We are in the process of migrating our servers to a new data center. Currently we are on SQL Server 2014 and SSIS packages deploy in seconds.
The new data center has several SQL server 2016 installs ...
0
votes
0answers
19 views
Is there a way to figure out an executing query's plan handle by the query text?
Is there a way to determine the plan handle for the query plan of an executing query by that query's text?
Perhaps in any of the DMVs?
(I'm trying to find the query stats on an ad-hoc query.)
0
votes
0answers
20 views
Archiving Huge table with Foreign Key dependencies
We have a huge table of ~ 1TB (data+Indexes) which has ~7 child tables with Foreign key relation ship
We have decided to archive the huge table but not the child tables(since they are not that big )
...
0
votes
0answers
21 views
SSIS Package Failing since Database failed over to Secondary (Basic Availability Group)
Our SSIs package running via SQL Agent Job on a dedicated Reporting server had been running fine since deployed more than 3 weeks ago. Due to performance issue we had to fail over the database to ...
2
votes
1answer
38 views
Variable to select database in SQL query
If I want use a variable in the FROM clause of a SQL query I need to use dynamic SQL. Which might be a pain if the query is long and complex.
There are few different ways of running a query from a ...
0
votes
1answer
44 views
Why is this datetime index not being used?
I have a table like this:
CREATE TABLE TestTable
(
[TestTableID] [int] IDENTITY(1,1) NOT NULL,
[IntField1] [int] NOT NULL,
[IntField2] [int] NOT NULL,
[IntField3] [int] NOT NULL,
[...
1
vote
0answers
28 views
Linked server in Sql Server 2014 invalid path
I am trying to create a linked server to foxpro tables using vfpoledb, but it gives me the error of "Invalid path or file name" when trying to put as source \\xxx.xxx.x.x\same_names\abc (xxx.xxx.x.x ...
4
votes
3answers
424 views
SQL Server - Snapshot is useful in production environments?
I'm reading about SQL Server high availability solutions and disaster recovery and among the available resources the SQL Server have snapshot feature. In theory all seems like beautiful.
I also ...
0
votes
0answers
21 views
Is it a problem to define a non-UTF8 collation in an NVARCHAR field?
I am working on an application I need to make cross-database.
One of the things I stumbled on is that SQL Server by default has a case-insensitive collation defined, which makes queries on NVARCHAR/...
0
votes
2answers
24 views
How to create a csv with SQLCMD which can be imported into another database system
I may be doing this all the wrong way so please point me in the right direction if I am missing something.
I have an Ubuntu server with SQLCMD installed and a Windows Server with a MSSQL database.
I ...
-1
votes
0answers
45 views
Question about the design of a database [on hold]
I'm a little new in database design, and I have a question, I guess, very simple.
I am working on a database for transformers, and I have 2 types of transformers.
I thought about storing each type ...
0
votes
1answer
25 views
Unable to Insert Casted XML Data into New Column in Temporary Table
Question
How can I insert CAST(event_data as XML) data into a newly added column in a temporary table with the following statement:
INSERT INTO #Unique_Logins (event_data_xml) SELECT CAST(...
0
votes
0answers
31 views
SQL Server replication or Integration Services [on hold]
Our team develop a web application Enterprise Resource Planning (ERP) and we install it in all the stores that we have. Which store has its own application and database to prevent the lack of ...
3
votes
1answer
58 views
Getting all undeclared variables from query
Imagine you try to run the following query:
SELECT *
FROM dbo.Customers c
WHERE id = @id
AND dateTimeCreated = @date
You'll get an error message along the lines of Must declare the scalar ...
1
vote
1answer
18 views
fn_trace_gettable() reports an error
The following code returns some rows with [Error] = 2
SELECT
v1.[name] as EventName, tr.TextData, tr.HostName, tr.ApplicationName, tr.LoginName, tr.SPID, tr.Duration,
tr.StartTime, tr.EndTime, tr....
0
votes
0answers
38 views
Select query repeatly executed in SQL Server 2016 [on hold]
Below is my activity:
Log shipped from SQL Server 2008 to SQL Server 2016.
Rebuild the index for a particular table which has more than 400 million data.
During rebuild, server has been rebooted.
...
-1
votes
1answer
25 views
How to use merge output statement to check if the condition is matched then insert record and update particular column with raw value? [on hold]
MERGE [dbo].[Department_Target] AS tar
USING [dbo].[Department_Source] AS src
ON tar.[DepartmentID] = src.[DepartmentID]
WHEN NOT MATCHED THEN
INSERT
(
[DepartmentID],
[Name],
[...
0
votes
0answers
18 views
ReFS on SQL Servers 2016 and 2017 [duplicate]
Could you please share your experience regarding ReFS for SQL Server's storage:
Is it better to use ReFS for SQL server 2016 upward?
Have you come across any issues with ReFS?
Does it recommend for ...
0
votes
1answer
27 views
Optimise query based on year month (multiple timezone)
Note: Though I am working on sql server, this solution might not need to tie to a specific database. Just need a general idea on how it could be done.
So, I have been research and found out that it ...
-2
votes
0answers
26 views
Data Model Diagram of Views [on hold]
Working in SQL Server 2017. I'm deploying some views in a new schema that will be accessible to certain users. In addition to delivering this new schema and the views, I also need to deliver my users ...
1
vote
1answer
27 views
User is in multiple Active Directory groups with multiple roles in database
I have a user that is a member of multiple Active Directory (AD) groups. Within SQL Server 2012, these AD groups have been assigned to different database roles. This user is not getting the correct ...
1
vote
1answer
35 views
Do a lot of error checks affect query performance and is it bad practice?
If I have a stored procedure with a handful of error checks that come before the queries are executed and throw exceptions as necessary, could this affect performance? Is it bad practice to do this?
...
1
vote
1answer
37 views
What is the right data modeling for this case? [on hold]
What is the right data modeling for this case?
In my case, an application has multiple editions and each edition has multiple plans. So from that, each customer has a contract to use an application.
...
2
votes
3answers
1k views
Multi-core vs Multi-CPU
I am requesting a new server from our Server team. Which is better for SQL Server performance: 2 CPUs with 8 cores per CPU or 1 CPU with 16 cores?
-1
votes
0answers
24 views
How to avoid users from connect to SQL server and link tables? [on hold]
I am with terrible situation and this question eating my mind.
we have access database with SQL server as back end all the tables were linked from the SQL server. In order access these tables from ...
0
votes
0answers
40 views
AGs or Distributed AGs for spanning a DR site replica topology [on hold]
I am working on a proposal for HA and am looking to present the best options for our situation. I've been reading a few books on Availability Groups and FCIs, and am leaning towards either a 3 node ...
2
votes
0answers
52 views
Choosing between RCSI and Snapshot Isolation [on hold]
I have researched into this and I understand the general differences between the two isolation levels. I would like to explain our scenario and need help in deciding the correct isolation level to use....
0
votes
1answer
78 views
Update 1 record across 135 tables
There is an application, it maintains delete flag column i.e. if a delete happen the column marked as 1 (in the deleted column).
To retrieve the record I must take this 1 to a NULL
There are 135 ...
