August 3, 2016 at 5:35 am
Lynn Pettis (8/2/2016)
Well, add another to that list. CF is a pain, especially when the developers embed the SQL code in the CF code, and build it dynamically to boot.
Yes it does. It's easy quicker and simpler to just string together a command and fire it. At least CFPARAM (I think that's it) helps with SQL injection, but stored procedures would be so much better for many reasons.
Also, watch the settings. We got inexplicable deadlocks between unrelated tables when all string values were sent to SQL Server as NVARCHAR when some of them should have been VARCHAR.
They might be due to the resulting implicit cast of the Varchar column to match the Nvarchar parameter causing excessive reads. I'm not 100% that this is it, but I've seen it happen here.
August 3, 2016 at 8:06 am
Ed Wagner (8/3/2016)
Lynn Pettis (8/2/2016)
Well, add another to that list. CF is a pain, especially when the developers embed the SQL code in the CF code, and build it dynamically to boot.Yes it does. It's easy quicker and simpler to just string together a command and fire it. At least CFPARAM (I think that's it) helps with SQL injection, but stored procedures would be so much better for many reasons.
Also, watch the settings. We got inexplicable deadlocks between unrelated tables when all string values were sent to SQL Server as NVARCHAR when some of them should have been VARCHAR.
They might be due to the resulting implicit cast of the Varchar column to match the Nvarchar parameter causing excessive reads. I'm not 100% that this is it, but I've seen it happen here.
Sure of it. When the engineer changed the CF setting to NOT make everything nvarchar, there by sending the primary key as a varchar instead of nvarchar it cleared up the deadlocks, it also made our system run much smoother as well. Since we moved to CF 11 we can control the parameter data types on a more granular level.
August 3, 2016 at 8:37 am
Lynn Pettis (8/3/2016)
Ed Wagner (8/3/2016)
Lynn Pettis (8/2/2016)
Well, add another to that list. CF is a pain, especially when the developers embed the SQL code in the CF code, and build it dynamically to boot.Yes it does. It's easy quicker and simpler to just string together a command and fire it. At least CFPARAM (I think that's it) helps with SQL injection, but stored procedures would be so much better for many reasons.
Also, watch the settings. We got inexplicable deadlocks between unrelated tables when all string values were sent to SQL Server as NVARCHAR when some of them should have been VARCHAR.
They might be due to the resulting implicit cast of the Varchar column to match the Nvarchar parameter causing excessive reads. I'm not 100% that this is it, but I've seen it happen here.
Sure of it. When the engineer changed the CF setting to NOT make everything nvarchar, there by sending the primary key as a varchar instead of nvarchar it cleared up the deadlocks, it also made our system run much smoother as well. Since we moved to CF 11 we can control the parameter data types on a more granular level.
I'll have to shoot this information over to the devs here on CF. We had a rash of deadlocks in their apps a while back, I'm wondering if they might've been running into this.
August 3, 2016 at 8:38 am
Ed Wagner (8/3/2016)
jasona.work (8/2/2016)
Even better, it's just a matter of hunting around because you tend not to delete anything!The Adobe forums topic where we found out how to get it to work:
https://forums.adobe.com/thread/1655440
At the time, we were running CF10 and the "EncryptionMethod=SSL ValidateServerCertificate=false" in the connection string worked (and bear in mind, we're using a certificate from a trusted cert authority, not a self-signed)
Thank you, Jason. I've sent it off to our CF admin. He wasn't able to make it work late last year with a new SQL Server I brought online. I know we were running an old version of CF at the time and are almost ready to bring CF 11 online and migrate things to it. I guess looking on CF sites for CF solutions isn't good enough; I'll have to tell him to start looking on SQL Server sites. 😛 Again, a huge thanks for this.
No problem, glad to help out!
August 3, 2016 at 8:49 am
jasona.work (8/3/2016)
Lynn Pettis (8/3/2016)
Ed Wagner (8/3/2016)
Lynn Pettis (8/2/2016)
Well, add another to that list. CF is a pain, especially when the developers embed the SQL code in the CF code, and build it dynamically to boot.Yes it does. It's easy quicker and simpler to just string together a command and fire it. At least CFPARAM (I think that's it) helps with SQL injection, but stored procedures would be so much better for many reasons.
Also, watch the settings. We got inexplicable deadlocks between unrelated tables when all string values were sent to SQL Server as NVARCHAR when some of them should have been VARCHAR.
They might be due to the resulting implicit cast of the Varchar column to match the Nvarchar parameter causing excessive reads. I'm not 100% that this is it, but I've seen it happen here.
Sure of it. When the engineer changed the CF setting to NOT make everything nvarchar, there by sending the primary key as a varchar instead of nvarchar it cleared up the deadlocks, it also made our system run much smoother as well. Since we moved to CF 11 we can control the parameter data types on a more granular level.
I'll have to shoot this information over to the devs here on CF. We had a rash of deadlocks in their apps a while back, I'm wondering if they might've been running into this.
Capturing the deadlock graphs and the execution plans helped. We were getting the deadlocks in a test environment running 10 users (automated testing simulating users). We were getting clustered index scans due to implicit data conversions. The index stats were exceedingly high on the scans. Sort of like chickens running around without their heads. When we eliminated the implicit conversions and allowed SQL Server to use the indexes on the tables, the deadlocks went away and indexes stats changed and SQL looked happy. Comparing counts across the board the stats actually dropped significantly even though seeks and lookups went up.
August 3, 2016 at 9:06 am
Lynn Pettis (8/3/2016)
Is the code I wrote here really that sophisticated, or is it that I have been doing this so long that for me it is a basic SQL query?
I would consider it advanced for most people using SQL Server because you use a CTE, a windowing function, and a case statement (especially since it is within the windowing function). You've combined 3 things that are not beginner or basic level SQL.
Now it is basic for most Threadizens I would guess.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 3, 2016 at 9:14 am
Jack Corbett (8/3/2016)
Lynn Pettis (8/3/2016)
Is the code I wrote here really that sophisticated, or is it that I have been doing this so long that for me it is a basic SQL query?I would consider it advanced for most people using SQL Server because you use a CTE, a windowing function, and a case statement (especially since it is within the windowing function). You've combined 3 things that are not beginner or basic level SQL.
Now it is basic for most Threadizens I would guess.
Thanks for the feedback. It just seemed so easy for me as I turned around the code in about 5 minutes.
August 3, 2016 at 9:40 am
Lynn Pettis (8/3/2016)
Jack Corbett (8/3/2016)
Lynn Pettis (8/3/2016)
Is the code I wrote here really that sophisticated, or is it that I have been doing this so long that for me it is a basic SQL query?I would consider it advanced for most people using SQL Server because you use a CTE, a windowing function, and a case statement (especially since it is within the windowing function). You've combined 3 things that are not beginner or basic level SQL.
Now it is basic for most Threadizens I would guess.
Thanks for the feedback. It just seemed so easy for me as I turned around the code in about 5 minutes.
For most threadizens that is about 5 minutes. But most people in the field they can barely understand it. We get so spoiled around here hanging out with so many people who actually understand how to manipulate data. For most of us it is a passion, for many out in the field it is a necessary part of their job which is often met with fear and confusion.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 3, 2016 at 10:38 am
Sean Lange (8/3/2016)
Lynn Pettis (8/3/2016)
Jack Corbett (8/3/2016)
Lynn Pettis (8/3/2016)
Is the code I wrote here really that sophisticated, or is it that I have been doing this so long that for me it is a basic SQL query?I would consider it advanced for most people using SQL Server because you use a CTE, a windowing function, and a case statement (especially since it is within the windowing function). You've combined 3 things that are not beginner or basic level SQL.
Now it is basic for most Threadizens I would guess.
Thanks for the feedback. It just seemed so easy for me as I turned around the code in about 5 minutes.
For most threadizens that is about 5 minutes. But most people in the field they can barely understand it. We get so spoiled around here hanging out with so many people who actually understand how to manipulate data. For most of us it is a passion, for many out in the field it is a necessary part of their job which is often met with fear and confusion.
Simple if you understand how to leverage these features together.
Great example for someone to learn from - you can easily see how it breaks the data into pieces that can be put back together to create a solution.
August 3, 2016 at 10:43 am
Greg Edwards-268690 (8/3/2016)
Sean Lange (8/3/2016)
Lynn Pettis (8/3/2016)
Jack Corbett (8/3/2016)
Lynn Pettis (8/3/2016)
Is the code I wrote here really that sophisticated, or is it that I have been doing this so long that for me it is a basic SQL query?I would consider it advanced for most people using SQL Server because you use a CTE, a windowing function, and a case statement (especially since it is within the windowing function). You've combined 3 things that are not beginner or basic level SQL.
Now it is basic for most Threadizens I would guess.
Thanks for the feedback. It just seemed so easy for me as I turned around the code in about 5 minutes.
For most threadizens that is about 5 minutes. But most people in the field they can barely understand it. We get so spoiled around here hanging out with so many people who actually understand how to manipulate data. For most of us it is a passion, for many out in the field it is a necessary part of their job which is often met with fear and confusion.
Simple if you understand how to leverage these features together.
Great example for someone to learn from - you can easily see how it breaks the data into pieces that can be put back together to create a solution.
I have to agree that it wasn't overly-complicated and would probably be about 5 minutes. To Sean's point, we do get spoiled her and learn a lot of great techniques to do things well. The cool part is that when we help others, we expose them to the same techniques, articles, etc. Then they learn and can turn around and help others.
August 3, 2016 at 1:16 pm
Ed Wagner (8/3/2016)
Greg Edwards-268690 (8/3/2016)
Sean Lange (8/3/2016)
Lynn Pettis (8/3/2016)
Jack Corbett (8/3/2016)
Lynn Pettis (8/3/2016)
Is the code I wrote here really that sophisticated, or is it that I have been doing this so long that for me it is a basic SQL query?I would consider it advanced for most people using SQL Server because you use a CTE, a windowing function, and a case statement (especially since it is within the windowing function). You've combined 3 things that are not beginner or basic level SQL.
Now it is basic for most Threadizens I would guess.
Thanks for the feedback. It just seemed so easy for me as I turned around the code in about 5 minutes.
For most threadizens that is about 5 minutes. But most people in the field they can barely understand it. We get so spoiled around here hanging out with so many people who actually understand how to manipulate data. For most of us it is a passion, for many out in the field it is a necessary part of their job which is often met with fear and confusion.
Simple if you understand how to leverage these features together.
Great example for someone to learn from - you can easily see how it breaks the data into pieces that can be put back together to create a solution.
I have to agree that it wasn't overly-complicated and would probably be about 5 minutes. To Sean's point, we do get spoiled her and learn a lot of great techniques to do things well. The cool part is that when we help others, we expose them to the same techniques, articles, etc. Then they learn and can turn around and help others.
I'll admit it took me a minute to sort out what it was doing and how, because I don't use rownumber all that often.
But once I figured it out, yes, it was quite simple.
Bonus points to the original poster as well, they provided everything people ask for, sample data with create scripts and an example of what they wanted it to look like when done that you could actually understand!
😎
August 4, 2016 at 1:18 am
Jack Corbett (8/3/2016)
Lynn Pettis (8/3/2016)
Is the code I wrote here really that sophisticated, or is it that I have been doing this so long that for me it is a basic SQL query?I would consider it advanced for most people using SQL Server because you use a CTE, a windowing function, and a case statement (especially since it is within the windowing function). You've combined 3 things that are not beginner or basic level SQL.
Now it is basic for most Threadizens I would guess.
I'm a beginner compared to most around these parts but I didn't think it was that sophisticated. It's actually an approach I use pretty frequently. That said, I wouldn't think to use a case statement in an order by. And that is why I like the Thread.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
August 4, 2016 at 4:47 am
BWFC (8/4/2016)
I'm a beginner compared to most around these parts but I didn't think it was that sophisticated. It's actually an approach I use pretty frequently. That said, I wouldn't think to use a case statement in an order by. And that is why I like the Thread.
Same here although I might use a case in a order by for a query, but would never think to use it in ROW_NUMBER
August 4, 2016 at 5:05 am
BWFC (8/4/2016)
Jack Corbett (8/3/2016)
Lynn Pettis (8/3/2016)
Is the code I wrote here really that sophisticated, or is it that I have been doing this so long that for me it is a basic SQL query?I would consider it advanced for most people using SQL Server because you use a CTE, a windowing function, and a case statement (especially since it is within the windowing function). You've combined 3 things that are not beginner or basic level SQL.
Now it is basic for most Threadizens I would guess.
I'm a beginner compared to most around these parts but I didn't think it was that sophisticated. It's actually an approach I use pretty frequently. That said, I wouldn't think to use a case statement in an order by. And that is why I like the Thread.
That is one of the key reasons I like ssc. When I first came here, there were folk writing all sorts of stuff that I'd never seen but also repeatedly using the same tool for the same type of job, because experience (and sometimes measurement) had proven them to be most appropriate. It was a real eye-opener. I found it to be great encouragement to try out new things but, oddly enough, to become a more consistent developer too.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 55,186 through 55,200 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply