This article provides an overview for developers, on how to quickly generate a dependency Entity Relationship Diagram for a table and its immediately related tables, within SQL Server.
This article provides an overview for developers, on how to quickly generate a dependency Entity Relationship Diagram for a table and its immediately related tables, within SQL Server.
While we are more than willing to help those that are stuck, that doesn't mean that we are here to do it all for you! We can't do all the work, you are either getting paid for this, or it's part of your grades and it wouldn't be at all fair for...
I want to make a receivable aging report for a customer using MS SQL Server Database. I am using MS SQLSERVER 2017. Below the image of the ledger table is shown. tbl_Ledger Date V.Type V # Debit Credit 1-Jan-24 Sales 1 5,000 - ...
source table ---> payment source_column ---> invoicenumber datatype --> varchar Source Column Desciption --> Invoice Id Migration Rule ----> If the referenceid is NULL: Reference Id from the Invoice table, Link the payment to the...
There's a wonderful tool in SQL Server that allows you to view the e execution plan for a query. With this, you can analyse to see that you are doing things like indexed queries. We can't help you because we don't have your database schema, and...
I have a problem with my query data , it become very slower when i try to access at pag 1200 i also use case when in my query Select Reg_ID,Reg_Service_ID,Reg_Patient_ID,Patient_Name,Doctor_ID,Doctor_Name, CASE WHEN...
To add to what Pete has said: dumping a large amount of unformatted and uncommented code on a site and expecting us to sort it out and convert it for you is just plain rude: to compound that by reposting the same same demand without even showing...
There's no tool that's going to be able to reliably convert a procedure from Oracle to SQL Server. Such tools as exist may get you 60% of the way but the rest is going to be absolute garbage. You are better off working out what the old procedure...
This is not a code conversion service. Try it yourself. If you get stuck on a particular part, then ask for help on that particular part, explaining precisely what you have tried and where you are stuck. Dumping nearly 450 lines of...
You probably want to start by reading and implementing authentication using Use ASP.NET forms-based authentication - ASP.NET | Microsoft Learn[^]. To control access to web pages, and folders of pages, use Control authorization permissions in...
Hello, I want to do user-based page authorization with asp.net web forms. What I want to do is to take the role of the user while logging in from the users table in the database and control which pages he can access there, for example, let the...
Below my solution. I don't like it, because it is a way too clumsy. I would never use that in production code. But for an analysis of an existing database it is just about acceptable. Many thanks to @AndreOosthuizen[^] WITH Boms AS ( SELECT ...
The query should look like - WITH BOMGroups AS ( SELECT DISTINCT BomId, ROW_NUMBER() OVER (ORDER BY BomId) AS BOMGRP FROM BomPositions ), BOMCounts AS ( SELECT BomId, COUNT(DISTINCT MaterialId) AS BOMCOUNT FROM BomPositions ...
Based on your expected output, you don't want "distinct" records; you want the last record for each order ID. WITH cte As ( SELECT order_id, hierarchyid, itemname, Createddate, ROW_NUMBER() OVER...
You should Group By the first three columns in your results and use Max() of your last column. That should get you what I think you want. Select order_id, itemname, hierarchyid, Max(Createddate) as LastOrderDate From orders Group By order_id,...
I have a table with orders and need to join unique records from history table could you please assist on how to get unique records by create date order_id itemname hierarchyid Createddate 1 Keyboard 4000 12/11/2017 2 ...
On your localhost, SQL Server is most probably installed on your local machine, and your SQL Server connection string points to your local SQL Server. However when you deploy your application on a Server, usually SQL Server is installed on a...
error : A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow...
Guidance to the correct solutions have been provided by Gerry Schmitz[^] and Richard Deeming[^]. Please don't vote for this answer; I am merely pointing out that the solutions are in the comments on the question. If anything, you should go and...
Firstly, the "100% cost" just means that your clustered index scan/seek accounts for 100% of the cost of that query. It doesn't mean that the query is less efficient than it would be without that index. Secondly, you need to design your indexes...
If i have created cluster index it should work faster than other. But when i check execution plan it show me 100% cost for it. Below is the example Table1 Col1 Col2 Col3 CREATE CLUSTERED INDEX LK_table1 ON Table1 (Col1, Col2, Col3) But i have...
I am developing a C# WPF application using .NET 6 in Visual Studio 2022, with a SQL Server 2008 R2 database. My database contains a table GRADE_CUST_GRP with a column GCPS of type image, where files of various types are stored without any file...
this works for me... DECLARE @sd DATE = '2024-01-15' ,@ed DATE = '2028-02-05' ,@y INT ,@m INT ,@d INT ,@d01 DATE ,@d02 DATE ; SELECT @y = DATEDIFF (YEAR , @sd , @ed ) ; SELECT @d01 = DATEADD (YEAR ,...
Hello- I am trying to get a list of orders that came across each server by the minute. I am able to get that with a simple script but I have to change the WHERE clause for each minute. I'm hoping to find something that can query the range in one...
Griff's solution is the correct one. When you think about it, SQL Server isn't like other typical server applications that are designed to free memory up. SQL Server is designed to be performant, and to treat the server as its own playground to...
Hi, A customer has a server (Windows Server 2019 with 55 Gb memory) with two instances of SQLServer (SS 2019 Standard edition), one for production, one for testing. The production SQLServer instance ends up occupying almost all the available...
A better solution is to move the test instance to a separate server: that way there is less chance of "interactions" where software being tested affects the production DB. Remember, if the production server is using that much memory, it's for a...
Further to my comments above... In one sense you have already created a temporary table - a table variable scope, like a temporary table, is within the stored procedure (or batch). You cannot use it outside of that scope - just like a temporary...
Start by counting the orders for each server for each minute: DECLARE @day datetime = '2024-02-21'; SELECT c.line As SERVER, M.Minute, COUNT(DISTINCT c.id) As OrderCount FROM customer c INNER JOIN orders o ON o.id = c.id ...
need to create temporary table from department table using stored procedure What I have tried: USE [EmployeeManagementSystem] GO /****** Object: StoredProcedure [dbo].[TempDepartments] Script Date: 21-02-2024 10:22:36 AM ******/ SET...
I'm no sure what you want to achieve... I believe below example will help you to understand how to filter your data. DECLARE @cn AS INT = 253; -- 253 --594 --2132 DECLARE @ip AS INT = 1; DECLARE @pcn AS INT = 0; WITH cte AS ( SELECT...
I need to find the immediate parent id based on the below condition: |CustomerNum|ParentCustomerNum|IsPlatform| |-----------|-----------------|----------| |132 |0 |1 | |253 |132 |0 |...
Add an SQL WHERE Clause[^] Provided you have stored your dates in DATE, DATETIME, or DATETIME2 in your DB, SQL can cope with date comparisons just as it can cope with numeric ones. You may also find the SQL Server DATEADD() Function[^] useful in...
select date from Ordersdate not to display current date and next day date remaining dates needs to display. Thanks Vinoth What I have tried: select date from Ordersdate not to display current date and next day date remaining dates needs...
In place of ROW_NUMBER() use DENSE_RANK() with ordered_salary as ( SELECT name, salary, DENSE_RANK() OVER(ORDER BY salary DESC) rn FROM salary_table ) select name, salary from ordered_salary where rn = 5
Hi, Try this First, enable ad-hoc queries like so: sp_configure 'show advanced options', 1; RECONFIGURE; GO sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE; GO Then run the below query: SELECT a.* FROM...
I would use ReadAllText rather than ReadAllLines. Then I'd split it into tokens with a RegularExpression and stick them back together. string text = @" ~ A B ~ C D " ; foreach ( string s in ...
I'm delving into the intricacies of MySQL data types and have stumbled upon the distinction between CHAR and VARCHAR. These two data types are fundamental to database design, but understanding their differences and best use cases can sometimes be...
All questions you could answer for yourself by reading the documentation, rather than waiting for someone to give you the answer: MySQL :: MySQL 8.0 Reference Manual :: 11.3.2 The CHAR and VARCHAR Types[^] Quote: For example, inserting 'John'...
It seems to me that you can do the required formatting in one line of code. List lines = ["~", "A", "B", "~", "C", "D","~","E","F"]; string[] formattedLines = string.Join('~', lines).Trim('~').Split("~~~"); A call to...
Seeing that you started showing some effort, I will help. Next time post way more code and explanations please, you will get more replies that way... static void Main() { string[] lines = File.ReadAllLines("D:\\InputFile.txt"); ...
While we are more than willing to help those that are stuck, that doesn't mean that we are here to do it all for you! We can't do all the work, you are either getting paid for this, or it's part of your grades and it wouldn't be at all fair for...
I want to below output in C# or SQL Table Input File Text -------------- ~ A B ~ C D Output Text -------------- A~B C~D What I have tried: string[] lines = File.ReadAllLines("D:\\InputFile.txt"); List list = new List(); ...
Don't do that. Don't store CSV data in a single column, it's wasteful, inefficient, and leaves you with nasty problems whenever you want to use the data or change it. SQL string handling is ... um ... poor, so you have to jump through a lot of...
Storing images directly in a database can lead to increased database size, as you've noticed. To minimize database storage, a common approach is to store the images as files on the file system and store references (relative path) in the database....
Hello! Good Day to all, I have a VB.Net windows form project with MS SQL Server as back end database. I am storing images to database from the application. This is required by client, for example when a user posts a Payment Voucher then he needs...
You are performing an INNER JOIN - you will only get back results from the Addresses table which have a corresponding record in the ExtraData table. If you want all records from the Addresses table, even if they have no corresponding record in...
I have two tables in a database what i want to combine and work with that data. So i used that codesnippet: var listOfFoundRespIds = (from a in context.Adresses join e in context.ExtraData on a.LFD equals e.lfd where...
Since your repository code is swallowing all exceptions - an extremely bad idea, by the way! - the only reason for a NullReferenceException would be that the baseRepository is null. Check the constructor to make sure you've assigned the field...
in Repository Pattern System.NullReferenceException Delete Method System.NullReferenceException HResult=0x80004003 Message=Object reference not set to an instance of an object. Source=Microsoft.EntityFrameworkCore StackTrace: at...
As the error message, the issue is in Delete method. The Delete method attempts to remove an entity from the context using LapShopContext.Set().Remove(entity). However, the error you're encountering is a NullReferenceException, which suggests...
The error message is very clear. An instance of MS SQL Server named ".\MSSQLSERVER2022" does NOT exist on server machine or is NOT accessible from the WAN. You can resolve your issue by doing the following: 1) check connection string, especially...
Call your hosting provider and bring the error up with them. The error means your code cannot connect to the SQL Server because there's no server to be found. You might want to make sure the name is correct as you put ".\MSSQLSERVER2022". The "."...
Hi I create asp.net core mvc project with .net core 7. now I want put my site on host. I do this and configure the Plesk but i get this error: An unhandled exception occurred while processing the request. Win32Exception: The system cannot find...
This sort of thing is best left to the application layer rather than a trigger, but if you insist then there is a worked example here - https://stackoverflow.com/questions/4255730/create-trigger-prevent-insert[^]
suppose we want to insert a row to table but if column id > 5 then insertion don't be executed.(with using Triggers) What I have tried: create trigger trg_salesorder ON sales.orders instead of INSERT AS SET NOCOUNT ON; begin try if id > 5 ...
This is a bit interesting problem. I have created a sample solution for you that you can extend as per your need. The actual issue is 15000 courses that you can't export as columns. Practically speaking in a given date range (semester) we are...
Quote: since the actual data set have about 15000 courses it is not very feasible That means you will end up with a table with about 15002 columns - one for each course, and one each for the student ID and department code. As per the Maximum...
I have a data with same id but multiple line i am required to convert into single line and mark id as primary key in SQL Server or excel. Student_ID |Dept_Code |Course_Code |Status -------------------------------------------------- 1001 ...
In SQL Server the table name cannot be variable. I believe it is to protect from SQL injection or other attacks. The solution would be to parse your input argument yourself. Then construct the sql you want into a varchar variable. Once...
CREATE PROCEDURE Procedure1 @tableTypeItem MyTblType readonly AS UPDATE v SET ONE=vi.ONE, TWO=vi.TWO FROM Table AS v JOIN @tableTypeItem AS vi ON v.ONE=vi.ONE WHERE v.ONE=vi.ONE exec Procedure2 ONE, THREE GO How can i access ONE...
You have no [Authorize] attribute on your ValuesController or any of its actions. Unless you have some configuration that you haven't shown, all of those actions will allow anonymous callers. Simple authorization in ASP.NET Core | Microsoft Learn[^]
Ok, so first - to run some error checking outside of your app, clear your browser cache or any other caching mechanisms that might be storing the old token as this might become an issue. I have converted your code to what I think might work,...
I get tokenKey from path https://localhost:7107/api/login And when i try to test token and get data from Path https://localhost:7107/api/Values the token work good and data comes well but when i remove token the data continue come and...
To get the list of paths without including any sub-paths, you could try the following: 1. Reverse the recursion - start with the records with no NextID and work backwards. 2. Find the longest path for each "leaf" node. Eg: WITH RecursivePaths...
Id is unique. NextID stores the noxt ID value. When NextID value is Null, there the path Ends. Input: ID NextID 1 5 2 Null 3 6 4 7 5 8 6 9 7 Null 8 Null 9 10 10 Null Output: a. 1 --> 5 --> 8 b. 2 C. 3 -->6 -->9 -->10 d. 4-->7 e. 8 ...
I have below table and Data. i am trying to display ledger of a customer,with their debit and credit value,with running balance, query is below ,is giving me correct output except Head Column , Problem is that ,when Level_Five_ID_CA is null...
There's few ways to achieve that. I'd like to provide a sample with Common Table Expressions: ;WITH CTE AS ( SELECT 1 Lvl, acctnum, note, srno, note newnote FROM Table1 WHERE acctnum IS NOT NULL UNION ALL SELECT C.Lvl +1, C.acctnum,...
You haven't specified which version of SQL Server you're using. Assuming 2017 or later, the simplest solution is to use STRING_AGG[^]: SELECT acctnum, STRING_AGG(note, ' ') WITHIN GROUP (ORDER BY srno) As note FROM YourTable GROUP BY...
Below is the table t1 in SQL with column like this, srno is the sequence just use to move cursor. acctnum(c1) note (c2) srno 12 abc 1 efd 2 xyz 3 15 pqr ...
Firstly make sure you have the necessary permissions and that your SQL Server is configured to allow connections from your Xamarin Forms app. Then make sure you have the correct dependencies and configurations in your Xamarin Forms project....
I have problem with connection to database XAMARINE forms Normal C# windows form app work with this string SSMS work normal with this credential string connectionString = "Data Source=sql.bss-solution.info,1433;Initial Catalog='ABC 2023';User...
I have a controller named Categories. I have one form or One View for Create And Edit. Create Action works well, but when I click Edit, the NewRow is added in database. The problem is I want to Edit Row without adding new in database. What I...
That's not how GROUP BY works; you're grouping by every column, so unless there are two identical records in the table, it will return every record. Probably the simplest option would be to use ROW_NUMBER to partition the records: WITH cte As (...
Your code is querying a database, retrieving a row, outputting the content of a field to a file, then pointing a web browser at that file. To have a web browser open your PDF without saving it on the filesystem, you need to restructure your code...
I am using the below code to export ASP.NET grid to Excel (.xlsx format only, I don't want .xls format) entire table as well as template fields which contain link buttons and hyperlinks. I have found some code in CodeProject, but it is not...
I have a query, and it has subquery written with (IN statement) like this: Select * From Student Where IsActive=1 AND stu_Id IN (Select stu_Id From Temp) What I need is: if (IN) gets nothing, then ignore or react like this line doesn't...
To ignore the subquery if the IN clause doesn't retrieve any results, you can use an EXISTS clause. Here's an updated version of the query: SELECT * FROM Student WHERE IsActive = 1 AND EXISTS (SELECT 1 FROM Temp WHERE Temp.stu_Id =...
What you posted is incomplete. The error is saying there are a LOT more lines to what you tried to execute than what you posted here. The error is on line 27, but you only posted 7 lines of SQL. Post the rest of what you have that includes this...
Make sure that there is no SQL before the CREATE PROCEDURE statement, or if there is, it must be terminated by a GO statement, and that GO must be on a line by itself with no semicolon. We can't see how you are executing that SQL code, so we...
CREATE PROCEDURE getEmployeeInfo AS BEGIN SELECT Emp_Name, Designation FROM employee ORDER BY Emp_Name; END; What I have tried: why is this msg showing in sql sever?after i created table i write above sql query to stored procedure
While we are more than willing to help those that are stuck, that doesn't mean that we are here to do it all for you! We can't do all the work, you are either getting paid for this, or it's part of your grades and it wouldn't be at all fair for...