Comments (6)
@gflpcantor31 thanks for reporting this. It didn't come up in any of my testing.
It's sort of an unfortunate state of things with dynamic SQL where at different times in different places string concatenation sometimes implicitly converts to lower byte lengths, causing these truncation errors. There's no great workaround for it other than to add a convert to nvarchar max around every string fragment, which is pretty tedious.
In the meantime, if you find which concatenation point in the dynamic SQL causes this, please let me know.
from darlingdata.
@gflpcantor31 can you try the version in the dev branch and let me know if that resolves the error for you?
from darlingdata.
Hello @erikdarlingdata
I've just tested the latest version, I'm sorry, but the error still occurs identically.
from darlingdata.
Hello @erikdarlingdata
To correct the error, I had to make the following two types of changes:
1 - Because of data type precedence (1) , I added the following:
@table_sql = CAST(N'' AS nvarchar(max)) +
....
....
2 - Again because of data type precedence and doubts about the data types returned by CASE expressions (?), as a precaution I applied a CAST to CASE expressions like this:
CAST ( CASE
WHEN ….
WHEN …
ELSE …. END AS nvarchar(max))
(1) Data type precedence https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-ver16
(2)
sp_HumanEvents__#273_gflpCantor31.zip
Maybe I've missed a few, but the only type 1/ change I made didn't resolve the error. That's why, to remove any doubts, I applied type 2/ modifications
Attached you'll find the script sp_HumanEvents__#273_gflpCantor31.zip where I've applied these modifications. With these modifications, I no longer reproduce the error. I'll leave it up to you to check and validate these modifications.
from darlingdata.
@gflpcantor31 If you open a proper pull request with these changes, I'm happy to take a look. It's a bit much to ask me to scroll through 3000 lines looking for your changes and then copying them over into my local file. Thanks!
from darlingdata.
Bonjour @erikdarlingdata
As desired, I've opened a pull request incorporating the changes.
I went back to your last CONVERT modifications (nvarchar(max), ..) and replaced them with a CAST, but only on the CASE expressions that caused the problem.
I've run a few tests. As far as I'm concerned, everything's OK and I don't see the error anymore.
I hope I haven't forgotten anything. I'm not very familiar with Git and Github procedures :)
from darlingdata.
Related Issues (20)
- sp_HealthParser - Pending Tasks threshold
- sp_QuickieStore - Add parameters to search for plan feedback and variants
- sp_PressureDetector - Arithmetic overflow HOT 11
- sp_QuickieStore - add a parameter to ignore all plans for a stored procedure HOT 1
- sp_PressureDetector - deltas HOT 1
- Is this an appropriate way to find all references to a database with sp_QuickieStore? HOT 6
- Running sp_QuickieStore with @get_all_databases = 1 and @escape_brackets = 1 adds extra escapes to the query_text_search string. HOT 3
- My favorite [sp_QuickieStore] is giving an error when used with Azure SQL Database HOT 6
- Running sp_QuickieStore @include_plan_ids = 2, @include_query_ids = 2 seems to fail absolutely everywhere HOT 1
- Question regarding sp_pressure detector sp HOT 5
- sp_QuickieStore: @get_all_databases = 1 and @procedure_name seem incompatible.
- sp_QuickieStore - Ignore disagreeable state when it's a readable secondary HOT 1
- sp_QuickieStore - Bug in determining workdays
- @get_all_databases = 1 vs @get_all_databases = 0 result in different periods of 8 days HOT 3
- sp_HumanEventsBlockViewer limited to Blocked Process Threshold = 5 HOT 3
- sp_QuickieStore -- Add ignore function to query text search HOT 1
- sp_QuickieStore: @get_all_databases = 1 seems to ignore the time part of @start_date and @end_date HOT 1
- sp_QuickieStore: Make it possible to hide the "all done" table.
- sp_QuickieStore: Should the bottom table give datetimes rather than dates? HOT 2
- sp_QuickieStore: Mixing @query_text_search and @query_text_search_not created primary key violations in #query_text_search HOT 2
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from darlingdata.