A Fine Slice Of SQL Server
Nicky van Vroenhoven troubleshoots an error:
2 weeks ago I talked about A No-Code Method to Refresh One Table From a Power BI Dataset in the Service. I recently ran into an error using this method, so I thought it was worth sharing this error, and the solution 😀, with you.
Read on to see what causes the error and how you can sidestep it.
Comments closedBut interestingly enough – we would be getting an added benefit here. Even if there is no SQL injection attack, it’s still possible that such errors would be raised by the application – simply due to bugs.
Furthermore, these errors in the database may be happening without anyone even noticing! How could that be, you ask? Well, it could be due to bad error handling that “swallows” the error entirely, or because the errors are logged but no one is bothering to look at the logs, or maybe because the errors are caught but an undetailed error message is logged/displayed to the user (I can’t even count how many times I encountered “general database error” messages in applications), or because the developers simply decided to mark this as a “known issue” that they didn’t bother to fix and they didn’t think to ask their DBA about it… The reasons are numerous and varying.
Click through for the scripts. I had built something similar about a decade ago, a simple WPF app which watched for errors. I messaged him with something like “You missed a comma in that IN clause” and I saw him pop up from his cubicle and look around, trying to figure out how I could peek over his shoulder and see the query.
1 CommentCosima Meyer explains how debugging works in R with RStudio:
Three basic commands in RStudio let you do the debugging:
debug(function_name),browser(), andundebug(function_name).With
debug(function_name)you start the debugging of your function – it’s basically like a mole that digs in. When you’re in debug mode, you can also call the objects in your function.
Read the whole thing to learn the power of debugging beyond the print() statement. H/T R-Bloggers.
I had an inquiry about dbcc dumptrigger today and realized that state filtering was added to dbcc dumptrigger but not well known.
You can collect a process dump (SQL Dumper) when a specific error occurs using XEvent (error_reported/ex_raise2 events with dump capture action) or dbcc dumptrigger.
The common use of dbcc dumptrigger is: dbcc dumptrigger(‘set’, 208) to produce a dump when error 208 is encountered.
This is pretty useful, especially if you’re troubleshooting a bug in the database engine.
Comments closedGabi Lehner announces an upcoming bugfix:
This is a Breaking change when using window functions inside partition operator sub-query scope.
Window functions require a serialized/ordered input.
There is a bug in Azure Data Explorer logic that allows using window functions inside the partition operator when the ordering is performed outside the partition query scope like this:
Click through for what currently works as well as its future replacement.
Comments closedErik Darling points out an important concern with GENERATE_SERIES:
These table valued functions of the built-in variety have this problem.
This one is no exception. Well, it does throw an exception. But you know.
That’s not exceptional.
Click through for a demonstration of the problem. Hopefully this gets sorted out prior to GA.
Comments closedDavid Klee loops us in on a tricky-to-catch problem:
We’ve been tracking a weird state with SQL Server virtual machines on VMware and possible warnings on database corruption while VM backups are running, largely centered around (but not isolated to) the tempdb database.
TLDR: We’ve now got a VMware KB article on this situation that you and your VM admins should read if you hit the condition and fall into the specifics listed below. Reference VMware KB 88201 for more details.
Read on for David’s thoughts and what to do if you hit this problem.
2 CommentsNathan Schoenack troubleshoots an error:
When trying to query an external table created for a generic ODBC external data source, the following error can be observed:
Message 7320, level 16, state 110, line 87
Unable to execute query “Remote Query” against OLE DB provider “MSOLEDBSQL” on link server “(null)”. 105082; Generic ODBC error: OdbcBufferReader.ReadBuffer, error in OdbcReadBuffer: SqlState: IM001, NativeError: 0, ‘Error calling: SQLSetDescRec(this->GetHdesc(), (SQLSMALLINT)column->idxServerCol, (SQLSMALLINT)column->odbcReadType, 0, column->valueLength, (SQLSMALLINT)column->precision, (SQLSMALLINT)column->scale, (SQLPOINTER)(pBuffer + column->valueOffset), (SQLLEN *)indPtr, (SQLLEN *)indPtr), SQL return code: -1 | SQL Error Info: Error <1>: ErrorMsg: [Microsoft][ODBC Driver Manager] The driver does not support this function. | Error calling: pReadConn->ReadBuffer(pBuffer, bufferOffset, bufferLength, pBytesRead, pRowsRead) | state: FFFF, number: 239, active connections: 9’, Connection String: Dsn={DSN Name};Driver={Driver Name};uid=root;server=xxxxx;port=xxxx;database=xxxx.
Read on for a viable workaround.
Comments closedBob Dorr shows off Arvind’s SQL Call Stack Resolver:
Some outputs, such as the XEvent call stack action output the raw stack frame information and require a rebase to loaded module information in order to symbolize. The security feature for random address virtualization loads images at different addresses each time the image is loaded. This requires the module base address and the raw address in order to calculate the relative virtual address for symbolization.
Click through for more information.
Comments closedDavid Alcock reviews the latest SQL Server 2019 cumulative update:
Microsoft recently released Cumulative Update 15 for SQL Server 2019. It contains a bunch of fixes and some improvements, I get a bit geeky with updates like this and love to have a look through the different fixes to see “Physical reads for read-ahead reads are counted incorrectly (two times) when you run queries. Therefore, the information in sys.query_store_runtime_stats and sys.dm_exec_query_stats shows incorrect values.”
Read on to see what this means and a quick test to see if it works as expected.
Comments closed