Database Discussion Boards

Re: EF Core 6 Question

Kevin Marois2-Feb-23 6:45

Kevin Marois

2-Feb-23 6:45

Awesome, thanks!

If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.

execute an update using --> "Exec sp_executesql @Sql1"

Member 115338921-Feb-23 5:55

Member 11533892

1-Feb-23 5:55

hi,
i have a problem while executing updates using the update script inside the variable, it gives the error "String or binary data would be truncated";
I realized that if reduce the number of fields to update, it no longer gives the error!
But I didn't want to divide the execution of the update into 2 variables!!! Can you help?
Thanks!

Re: execute an update using --> "Exec sp_executesql @Sql1"

Victor Nijegorodov1-Feb-23 6:48

Victor Nijegorodov

1-Feb-23 6:48

https://stackoverflow.com/questions/5591473/error-string-or-binary-data-would-be-truncated-when-trying-to-insert
String or binary data would be truncated - Google Search

Re: execute an update using --> "Exec sp_executesql @Sql1"

David Mujica2-Feb-23 5:31

David Mujica

2-Feb-23 5:31

How is the @Sql1 variable defined ?
Ex: Declare @Sql1 Varchar(200)

Re: execute an update using --> "Exec sp_executesql @Sql1"

Eddy Vluggen10-Mar-23 13:17

Eddy Vluggen

10-Mar-23 13:17

After thinking about this a bit more than required;

Member 11533892 wrote:
But I didn't want to divide the execution of the update into 2 variables!!!

You mean you don't want to execute two update-statements for the same record. Two variables cost nothing nowadays, so that doesn't make any sense. Two updates must be atomic, or you get concurrency issues. Could be simply solved by using a transaction.

You may find the error to be the design of the table, not the update-statement. I'm going to guess a lot of those fields can be "NULL" or revert to some preset default value.

..but, let me give you a solution from a VB6 programmer view; you can rename all columns into numbers. So if column one is named [Name], you replace that with [1]. You can keep a list in Excel to look up the column names. That way, the update-statement is shorted a lot, and a huge lots of them would fit in a VARCHAR(MAX) command.

Roll eyes | :rolleyes:

Bastard Programmer from Hell Suspicious | :suss:

"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

Question On SQL Query Result

crmfghtr20-Jan-23 6:13

crmfghtr

20-Jan-23 6:13

I Have 3 Tables Orders, OrderItems, OrderPymts. For this query, I only need records from the Orders and OrderPymts Tables.
The Order Table Contains Fields: OrderId, ClientId, SubTotal, Tax, OrderTotal
The OrderPymts Table Contains Fields: PaymentId, OrderId, PaymentDate, Terms, PrevBal, Payment, Balance.
I'm trying to query records only on the Last PaymentDate for each Order (To show the Outstanding Balance of Each Order).
I tried This:

SQL

SELECT Orders.OrderId, Orders.ClientId, Orders.OrderTotal, OrderPymts.Terms, (SELECT MAX(OrderPymts.PaymentDate)) AS PaymentDate, OrderPymts.PrevBal, OrderPymts.Payment, OrderPymts.Balance 
					FROM OrderPymts
                    JOIN Orders ON OrderPymts.OrderId = Orders.OrderId 
                    WHERE (Balance >= 0.01)
					ORDER BY Orders.OrderId, PaymentDate

To just get the last payment but it still returns all the payments.
Example Result of My query:
OrderId |ClientId |OrderTotal|Terms |PaymentDate|PrevBal|Payment|Balance|
1001 | 1 | 500 |Credit| 1/1/2023 | 500 | 100 | 400 |
1002 | 2 | 800 |Cash | 1/9/2023 | 800 | 200 | 600 |
1002 | 2 | 800 |Cash | 1/11/2023 | 600 | 100 | 500 |

Above is an example of the data my query is showing. I only want to get the bolded records in the query. But my code above is pulling all the records with a balance greater than 0.01. As you can see, I tried getting the Max date but that didn't work. I also tried Group By but I get the same results as above. I need someone smarter than me, to help me with this.
Thanks for your help.

modified 20-Jan-23 11:34am.

Re: Question On SQL Query Result

Richard Deeming20-Jan-23 6:33

Richard Deeming

20-Jan-23 6:33

Assuming Microsoft SQL Server, ROW_NUMBER is your friend. Smile | :)

SQL

WITH ctePayments As
(
    SELECT 
        OrderId, 
        PaymentDate,
        Terms, 
        PrevBal, 
        Payment, 
        Balance,
        ROW_NUMBER() OVER (PARTITION BY OrderId ORDER BY PaymentDate DESC) As RN
    FROM 
        OrderPymts
)
SELECT
    O.OrderId,
    O.ClientId,
    O.OrderTotal,
    P.Terms,
    P.PaymentDate,
    P.PrevBal,
    P.Payment,
    P.Balance
FROM
    Orders As O
    INNER JOIN ctePayments As P
    ON P.OrderId = O.OrderId And P.RN = 1
WHERE
    P.Balance >= 0.01
ORDER BY
    O.OrderId
;

ROW_NUMBER (Transact-SQL) - SQL Server | Microsoft Learn[^]

Alternatively, you could use a correlated sub-query:

SQL

SELECT
    O.OrderId,
    O.ClientId,
    O.OrderTotal,
    P.Terms,
    P.PaymentDate,
    P.PrevBal,
    P.Payment,
    P.Balance
FROM
    Orders As O
    CROSS JOIN
    (
        SELECT TOP 1 Terms, PaymentDate, PrevBal, Payment, Balance
        FROM OrderPymts
        WHERE OrderPymts.OrderId = O.OrderId
        ORDER BY PaymentDate DESC
    ) As P
WHERE
    P.Balance >= 0.01
ORDER BY
    O.OrderId
;

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: Question On SQL Query Result

crmfghtr20-Jan-23 6:41

crmfghtr

20-Jan-23 6:41

Thank You Richard. I'm coming From VB.Net learning C#, and SQL, Didn't think about a cross Join.

modified 20-Jan-23 18:43pm.

Re: Question On SQL Query Result

Mycroft Holmes20-Jan-23 12:34

Mycroft Holmes

20-Jan-23 12:34

On a different note - it is probably not a good idea to link payments to an order, or at least make it a secondary connection. A client should have an account, orders place a debit on the account and payments credit the account. You may then want to link 1 or more payments to an order or 1 or more orders to a payment.

Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP

Re: Question On SQL Query Result

crmfghtr20-Jan-23 13:41

crmfghtr

20-Jan-23 13:41

I needed it to show the last payment made on an invoice. But thanks for your advice.

EF Core 6 Exclude Columns

Kevin Marois19-Dec-22 9:47

Kevin Marois

19-Dec-22 9:47

I have an entity table which is begin queried into an entity:

UserEntity? user = dc.Users
                     .Where(x => x.UserName.CompareTo(entity.UserName) == 0)
                     .FirstOrDefault();

Now, if I add a property to the entity that does NOT exist in the underlying table, it throws an exception:

Microsoft.Data.SqlClient.SqlException: 'Invalid column name 'FullName'.'

FullName if not a table field. How can I prevent this exception for properties that don't exist in the data?

If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.

Re: EF Core 6 Exclude Columns

Dave Kreskowiak19-Dec-22 16:47

Dave Kreskowiak

19-Dec-22 16:47

If I remember correctly, you add the [NotMapped] attribute to the property in your model class.

public class User
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    [NotMapped]
    public string FullName { get; set; }  // or whatever your property definition is
}

You could easily find this yourself just by Googling for "Entity Framework ignore property".

Asking questions is a skill
CodeProject Forum Guidelines
Google: C# How to debug code
Seriously, go read these articles.

Dave Kreskowiak

Re: EF Core 6 Exclude Columns

Kevin Marois23-Dec-22 8:36

Kevin Marois

23-Dec-22 8:36

Thanks.

I spent time on Google looking around, but I wasn't really sure what I was looking for.

If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.

Using SQL Native Backup URL, multiple databases to Azure Blob Storage

Jaime Maccou9-Dec-22 2:00

Jaime Maccou

9-Dec-22 2:00

How to backup multiple database to Azure blob storage using native SQL Server Backup to URL option.

I have used the Ola Backup scripts with success but will like another option.

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES',
@BackupType = 'FULL',
@Compress='Y',
@copyonly ='Y',
@Url='https://development.blob.core.windows.net/backups',
@BlockSize=65536,
@MaxTransferSize=4194304

--script to modify for multiple database
DECLARE @name VARCHAR(100) -- database name
Declare @url as Varchar(max) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup Wink | ;)

DECLARE @fileDate VARCHAR(20) -- used for file name

-- specify database backup directory e.g. 'https://development.blob.core.windows.net/backups'
SET @url = 'https://development.blob.core.windows.net/backups'
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')

DECLARE db_cursor CURSOR READ_ONLY FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN ('master','model','msdb') -- exclude these databases
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO URL = @URL

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

-- Any suggestions that does not include having to execute the backup command for each database will be helpful

Re: Using SQL Native Backup URL, multiple databases to Azure Blob Storage

jschell12-Dec-22 11:33

jschell

12-Dec-22 11:33

Myself I would first look for a commercial solution. Rolling your own requires dealing with all of the possible error scenarios and notifications that must occur when an error happens.

But other than that there are two problems not one. So there should be two solutions.
1. Do the back up
2. Move the back up to the remote location.

Entity Framework Core 6 VARCHAR(MAX)

Kevin Marois8-Dec-22 13:54

Kevin Marois

8-Dec-22 13:54

Using EF Core 6 Code First.

I'm trying to create a Notes field.

entity.Property(e => e.Notes)
        .HasColumnName("Notes")
        .HasColumnType("varchar");

This becomes varchar(1) in the DB. How do I create a column with varchar(max)?

If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.

Re: Entity Framework Core 6 VARCHAR(MAX)

Victor Nijegorodov8-Dec-22 19:24

Victor Nijegorodov

8-Dec-22 19:24

See https://stackoverflow.com/questions/24224442/what-is-the-proper-usage-of-hascolumntype-and-database-generated

Re: Entity Framework Core 6 VARCHAR(MAX)

Kevin Marois9-Dec-22 12:47

Kevin Marois

9-Dec-22 12:47

Your post did not answer the question

If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.

Re: Entity Framework Core 6 VARCHAR(MAX)

jschell12-Dec-22 11:35

jschell

12-Dec-22 11:35

When I look at the posted link it certainly looks to me like it provides an answer to the question asked.

So perhaps you might want to explain how that does not answer your question.

SOLVED - Pivot a result set

Richard Andrew x6428-Nov-22 6:46

Richard Andrew x64

28-Nov-22 6:46

I need to create a subquery that pulls detail records for the header records in the main query.

But I'd like to display values from the detail records in the form of a comma separated list, like so, "value1, value2, value3". And this list should appear as an extra column in the result set.

Is there any way to achieve this in SQL Server?

SOLUTION: Use the STRING_AGG function! Cool.

The difficult we do right away...
...the impossible takes slightly longer.

Re: SOLVED - Pivot a result set

Richard Deeming28-Nov-22 22:24

Richard Deeming

28-Nov-22 22:24

STRING_AGG is the best approach for SQL Server 2017 or later.

If you need to support earlier versions, you'll need an alternative:
Concatenating Row Values in Transact-SQL - Simple Talk[^]

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

TSQL - JOIN cancels out my date filter

jkirkerx27-Nov-22 14:53

jkirkerx

27-Nov-22 14:53

Well today was a crappy day, thinking I can hit and run this small web page and clean it up. I spent hours modeling this in SQL Manager finally getting it to work, and then used it my PHP code, it worked but fell apart. Maybe I have it backwards, and I should call "Project" first, and then join "commission_Summary"

The first example works, but when I join the "project" table, it cancels out my "commission_summary.startup_check_date" filter. I get that or think that my JOIN is wrong, or that my JOIN is calling up all records and overriding my date filter. I tried different types of JOINs, but with no success.

SELECT 
    commission_summary.project_no,
    commission_summary.EC, 
    commission_summary.TC, 
    CONVERT(CHAR(10), commission_summary.startup_check_date, 120) AS StartDate, 
    CONVERT(CHAR(10), commission_summary.finished_check_date, 120) AS StopDate      
FROM commission_summary
WHERE CAST(commission_summary.startup_check_date AS SMALLDATETIME) >= CAST('2022-01-01 00:00:00' AS SMALLDATETIME)
ORDER BY commission_summary.startup_check_date DESC

I tried adding one join at a time, and it worked or produced my desired results using just JOIN. I don't understand why this returns all the records, well I know it's JOIN related, just not sure why. My theory, is that the "Project" JOIN may have a duplicate column name. I wonder if I should of used a wildcard for testing, like SELECT * FROM. This returns all the records

SELECT 
    commission_summary.project_no,
    commission_summary.EC, 
    commission_summary.TC, 
    CONVERT(CHAR(10), commission_summary.startup_check_date, 120) AS StartDate, 
    CONVERT(CHAR(10), commission_summary.finished_check_date, 120) AS StopDate,
    project.Status, 
    project.Sales_no,
    project.swan_job, 
    employee.Employee_ID, 
    employee.FName, 
    employee.LName, 
    customer.Customer_no, customer.LName, 
    customer.FName, 
    customer.City 
FROM commission_summary 
JOIN project on commission_summary.project_no = project.project_no 
JOIN employee on commission_summary.employee_id = employee.Employee_Id 
JOIN customer on customer.Customer_No = project.Customer_no 
WHERE CAST(commission_summary.startup_check_date AS SMALLDATETIME) >= CAST('2022-01-01 00:00:00' AS SMALLDATETIME)
AND project.status = 'construction' OR project.status = 'finished' 
ORDER BY commission_summary.startup_check_date DESC

I'm confused by how this works, because it produces my desired results, of about 12 records in 2022.

SELECT 
commission_summary.project_no, 
commission_summary.EC, 
commission_summary.TC, 
CONVERT(CHAR(10), 
commission_summary.startup_check_date, 120) AS StartDate, 
CONVERT(CHAR(10), commission_summary.finished_check_date, 120) AS StopDate, 
project.Status, 
project.Sales_no, 
project.swan_job, 
employee.Employee_ID, 
employee.FName, 
employee.LName, 
customer.Customer_no, 
customer.LName, 
customer.FName, 
customer.City 
FROM commission_summary 
JOIN project on commission_summary.project_no = project.project_no 
JOIN employee on commission_summary.employee_id = employee.Employee_Id 
JOIN customer on customer.Customer_No = project.Customer_no 
WHERE (CAST(commission_summary.startup_check_date AS SMALLDATETIME) >= CAST('2022-01-01 00:00:00' AS SMALLDATETIME)) 
AND project.status = 'construction' OR project.status = 'finished' 
AND commission_summary.employee_id = '91' 
ORDER BY commission_summary.startup_check_date DESC

Any point in the right direction would be appreciated, or even a simple explanation.

If it ain't broke don't fix it
Discover my world at jkirkerx.com

Re: TSQL - JOIN cancels out my date filter

Graham Breach27-Nov-22 22:38

Graham Breach

27-Nov-22 22:38

You have an OR in there without any parentheses, that might be the problem. Try this:

SQL

AND (project.status = 'construction' OR project.status = 'finished')

Both sides of the OR are using the same table column, so you could use IN instead

SQL

AND project.status IN ('construction', 'finished')

Re: TSQL - JOIN cancels out my date filter

jkirkerx28-Nov-22 7:32

jkirkerx

28-Nov-22 7:32

That seems to be it. Wow!
I didn't catch that yesterday.
Thanks

If it ain't broke don't fix it
Discover my world at jkirkerx.com

Re: TSQL - JOIN cancels out my date filter

jschell28-Nov-22 11:44

jschell

28-Nov-22 11:44

I always use parens. Removes the question for me an future maintenance programmers as to what I thought the precedence was.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.