Questions tagged [sql-server]
Microsoft's SQL Server is a relational database management system (RDBMS) that runs as a server providing multi-user access to a number of databases. It originated from the Sybase SQL Server codebase, which is why both products use the extension of SQL called Transact-SQL (T-SQL).
550
questions
3
votes
0answers
41 views
Creating SQL Server Database Users Dynamically from User Input
The Application
I have an application in testing that runs off of an MS SQL Server database. The app allows users to authenticate using Windows authentication (that is, using the Windows username the ...
5
votes
1answer
67 views
Excel to SQL server
I am self-taught when it comes to coding. I want to improve, so please give me advice if I have some bad habits or if there is in general a better way of doing this.
The idea of this code is to look ...
5
votes
2answers
72 views
NewSequentialId - pure net core 2.2 implementation of sql server function
i'm currently trying to rewrite sql server newSequentialId() function in pure c# that is performing as fast as possible and eats minimum memory.
Question is can I it be done better - current ...
1
vote
0answers
39 views
Is there a cleaner way of writing this dplyr sql server query
This is the first time I am writing R code to access/manipulate and save data. The SQL query I would normally run then save to a spreadsheet, read it into R, save to another sheet then copy and paste ...
0
votes
0answers
35 views
Chart.js - Getting data from database using sql server and asp.net mvc5
I displayed and styled the chart.js and am also using mock data in the array, I just can't get my head around how to populate the data in the chart.js using MVC 5. I have tried lots of different ...
2
votes
1answer
52 views
Database Model for Feedback Module
I wish to generate an online feedback system. Requirements are
Admin can enter feedback questions
Admin will select these questions and create a feedback session
One Question can be used in many ...
0
votes
1answer
123 views
Cancel Long Running Query using Cancellation Token AND SqlCommand.Cancel()
I need to be able to cancel a long running query programmatically through our application. The code below will kick off a long running query and give control back to the main thread. At any ...
2
votes
0answers
45 views
SQL Code That Looks for Dupes with Confidence Score, 3hr / 1.2 BILLION Rows Returned. Optimize?
OK, I have a customer list that has approx 80k records. I need to search and compare looking for duplicates, but duplicates could be an exact match, or a similar match (only some data matches) so it ...
1
vote
0answers
25 views
Searching for Duplicates in SQL Table with Confidence Score
Sometimes I'm not sure if my questions should be here or StackOverflow, so I appologize if this is the wrong place.
I have a SQL (MS SQL 2016) stored procedure that is evaluating data looking for ...
0
votes
1answer
71 views
database connection, fetching rows and handling error in csharp
Fetching database records and displaying as JSON. Asking for revision to ensure if everything's ok, i.e. connections are properly handled and closed in case of error.
...
0
votes
0answers
22 views
SQL Server - Flagging overlapping time entries and calculating time without overlaps
Here's my example set up in rextester.
https://rextester.com/LROA87361
I have the following table in gray (the rightmost 2 columns are for explanation).
As you can see an employee's time entries ...
5
votes
2answers
74 views
Query with multiple foreign keys to the same table
I have a primary table, REP_ASSIST.ES_LOG with multiple columns that are foreign keys to another table, ...
3
votes
2answers
154 views
Function to get current value or default from a string of two values
I have SQL scripts that I want to be executable directly in the editor (like DataGrip or SSMS) and also in automated integration tests. However, in each use-case they require different environment and ...
6
votes
1answer
66 views
Zombie killers ranking
Code Review has a Zombie Problem and, in comparison to The Walking Dead TV Show, our survival doesn't depend on killing them all day every day.
As a potential solution, I though it would be ...
3
votes
1answer
58 views
SQL Sub-query with running total optimization
The codes below get the rows of data until it reaches the total running quantity limit.
Notice that the SQL uses sub-query. ...
1
vote
1answer
22 views
T-SQL Getting Sequential events with first even criteria
I have a query I am writing where I want output if a person has some service provided, then I want all the services they had provided after that and I don't want the individual returned if that is the ...
3
votes
1answer
67 views
Daily SQL job to delete records older than 24 hours
Below is a delete job to delete 1M+ records daily and takes 13 hours to complete and sometimes more than that. I need to optimize this.
The table tblcalldatastore is being inserted 24*7 through a ...
3
votes
1answer
80 views
Deleting millions of logs in batches
I have a fast growing table with logs that I frequently have to delete (the server doesn't have much resources). It grows by at least two million entries every day. The database is a SQL Server 2012.
...
1
vote
1answer
33 views
Updating field in Temporary table
I have a more massive sp, in which I use some temporary tables, below is a part of the sp where I am updating a field in a temporary table two times.
...
3
votes
1answer
60 views
Calculating all flight connections
I am working on an API to return all the possible flight route between point A and B. I have a set of data which includes the flight number, airline, departure airport, destination airport, departure ...
1
vote
1answer
171 views
Do these BLL-DAL comply with the Dependency-Inversion Principle and is this valid Dependency Injection?
I am working on a very simplified proof of concept (POC).
Separation of concerns should be achieved in this project, so that in the future we have flexibility in respect of what data source we use (...
11
votes
1answer
183 views
Safe dynamic SQL for generic search
Prompted by discussion about SQL injection, I wanted to put a proof of concept forward to get feedback about whether this is in fact safe and protected against SQL injection or other malicious use. ...
2
votes
1answer
81 views
My first c# program: Get Data from Sql Server and show it in Textboxes
So I have been programming in VB.net and want to learn C#.
I "successfully" created a simple program that retrieves data from Sql Server during Form1_Load. The ...
1
vote
1answer
78 views
T-SQL query Choose name of clients, concluded more than one loan agreement in 2010
QUESTION: Choose the name of clients,
concluded more than one loan agreement in 2010,
as the numbers and dates of issue of these contracts.
Sort by name of the client and date of issue of the loan.
...
0
votes
0answers
48 views
Parties and Messages in SQL e-Commerce db design
I'm posting a follow-up question based on my first question. My goal is to, as an exercise for myself, create a complex (in my opinion) e-commerce application. The explanation of the application can ...
3
votes
1answer
32 views
T-SQL Query remove duplicates except for original row from the table
Question:
There is an Exam_Merge table into which to import
records from a similarly structured table in another database.
In this case, the Exam_Merge table contains the values of the primary key ID
...
4
votes
1answer
43 views
JSON from SQL Server: Reduce lists of single dicts to list of values
SQL Server 2016 introduced some JSON features, but they're far from robust. As far as I can tell, there's no way yet to output a simple array; everything must be in ...
4
votes
1answer
260 views
SQL e-commerce database design
I want as an exercise to create an e-commerce application from bottom up. My main goal here is to gain a lot of knowledge. I have experience in web development, but never have been there from the ...
1
vote
1answer
54 views
Query to generate payroll based on attendance for a month
I have written a query to generate payroll based on attendance for a month, for about 4000 employees. It takes quite a long time to execute. Basically, what I am doing is to joining two different ...
1
vote
1answer
35 views
Insert multiple rows without using CURSOR SQL Server 2014
I have a fairly complex query which insert multiple rows into a table from multiple tables. Its works fine but I really don't want to use cursor as some blog says cursor took long time for execution (...
1
vote
0answers
24 views
Log table with a summary of claims
I'm writing a log table which contains the summary of the claims. The log table logs the data whenever there is an upload.
I have written the following SQL query. Though it serves the purpose, I'm ...
0
votes
0answers
25 views
Ensuring referential integrity in child, group relationship
I'm not going to lie - Sql Server is not in the top 10 of my skill set, but it's something that I have to do from time to time.
What's more, I'm using Entity Framework, so there is a workaround ...
2
votes
1answer
84 views
Web Server handler class to pass IIS http requests to SQL Server stored procedures
This is a c# class I use to implement web database applications with Microsoft IIS and Microsoft SQL server. It passes the http context to a selected SQL stored procedure, and writes a response based ...
2
votes
1answer
239 views
1
vote
1answer
48 views
Read comma separated values and insert in SQL Server 2016 table using String_Split
I know that this question has been asked many times over many years and I have read every one of them but I wanted to get a modern solution since software has changed and ...
3
votes
2answers
104 views
SQL Query - records within the last year
Bit of a complicated one for me.
I have a database full of hundreds of thousands of records, many of which are duplicated.
I need to get all records within the last year but making sure every ...
2
votes
1answer
51 views
Determining daylight-saving time in Central European Time
(We are on SQL Server 2008).
The following constellation:
Our applications passes date&time around (in the most horrible way possible).
We want to simplify this. Instead of a culture-specific ...
6
votes
1answer
107 views
Retrieving the IDs of entries to be deleted from a table
Business logic
It would take us too far to explain the whole business case behind this code, so I'll try to be as succinct as possible.
There are Persons,
and ...
0
votes
2answers
62 views
Bash Script for file auditing, push information to server and be able to view in a web page
Edit: The goal of this Question was only seeking code review and guidance in learning or tips for improvement. I apologize if that was not clear or aligned properly with the goals of this forum.
...
0
votes
0answers
20 views
Dynamic “compilation” of SQL stored procedures
I have a stored procedure which is called to create ( "compile" ) temporary stored procedures, code below, based on whether a flag in a database record is set - the various sub-procedures such as ...
0
votes
0answers
26 views
Tracking when a column was last modified using a trigger (in SQL Server)
How can I improve the following code to track when an Azure SQL Database column value was last modified?
...
1
vote
1answer
86 views
Upload csv file to sqlserver with python
I have to weekly upload a csv file to sqlserver, and I do the job using python 3.
The problem is that it takes too long for the file to be uploaded (around 30 minutes), and the table has 49000 rows ...
4
votes
1answer
44 views
Get local datetime from UTC time
One of the app is inserting date in UTC format (column CreateDate). When reporting from this table, I used a difference in hours between the inbuilt ...
0
votes
1answer
95 views
Modify a column in 2 tables based on trigger
This is SQL Server 2008r2. First, here are some tables and data for test:
...
1
vote
0answers
22 views
Calculate BACKLOG_M_1
I need to calculate the BLACKLOG_M value corresponding to the Snapdate of the first of the month. It becomes a constant value repeated for each line of the month
As it is explained in the picture:
<...
1
vote
0answers
122 views
Syncing a table to a remote database
I want to sync the table every few minutes to a remote database. I want it to be as efficient as possible. I wrote this stored procedure that will only return rows that are added or removed after the ...
2
votes
1answer
59 views
Validating region/state/province codes for different countries
I have a large table in which I store region/state/province codes (two letters) from different countries. I use these region codes further downstream for multiple process. One of the steps I do is a ...
0
votes
1answer
43 views
Quickly check whether file name is in column in an SQL Table and move to new folder
I have a table that in one of the columns exists a list of file names. The names comes from other tables and the files in a folder.
Not all the files are used, may be attached to parcels that no ...
3
votes
1answer
134 views
SQL SP creating certificates for shipping
We ship large spools of copper wire and pallets of copper rod that have to meet specific chemical and mechanical requirements, this is what must be put on the certifications. I created an SP which ...
1
vote
0answers
23 views
Activity grouping and selection according to order and completion requirements
The problem consists of two categories of events (Work and Rest) which together form a complete set.
The number of Work events is open-ended.
The number of Rest events is fixed at three (3).
The ...
