Transact SQL :- sys.dm_os_enumerate_filesystem – Error – 0x8007007a

Background Still stuck on sys.dm_os_enumerate_filesystem. Want to touch on a couple of diagnostic steps. Lineage Posts along same subject. Transact SQL :- sys.dm_os_enumerate_filesystem Date Published :- 2020-04-23 Link Usage Syntax SQL Sample Sample -01 SQL Output Issues Issue - "Internal error. The string routine in file sql\ntdbms\storeng\dfs\alloc\storagedmv.cpp, line 799 failed with HRESULT 0x8007007a." Error Message … Continue reading Transact SQL :- sys.dm_os_enumerate_filesystem – Error – 0x8007007a

Transact SQL :- sys.dm_os_enumerate_filesystem

Background Will like to take a few minutes to discuss an un-documented Transact SQL function. The function's name is sys.dm_os_enumerate_filesystem. Usage Syntax SQL Sample Sample -01 SQL Output Issues Issue - Parameter is incorrect Error Message Textual Image Explanation We passed a null or an empty string as the file pattern to look for. Remediate … Continue reading Transact SQL :- sys.dm_os_enumerate_filesystem

Transact SQL – STRING_AGG – Error – “Incorrect syntax near ‘within'”

Background Working on a script, but noticed an error when I tried to sort the data using the String_Agg within group clause. Code Let us reproduce using a sample data set. SQL Sample -01 SQL Output Sample -02 Outline If one reviews our output, one will notice that the cities are listed based on the … Continue reading Transact SQL – STRING_AGG – Error – “Incorrect syntax near ‘within'”

ANSI Standards – Database – Substring

Background Reviewing some SQL and wanted to point out a tiny dissimilarity between database platforms. Data Our test table is Oracle's SCOTT.EMP Here is what the data looks like :- Substring/Substr The HIREDATE is the only well formatted fixed length string. Its format is DD-MMM-YY. Query Oracle SQL Output Explanation Oracle relies on Substr to … Continue reading ANSI Standards – Database – Substring

SQL Server, SSMS, and Unicode characters

Background Here I am fraught with insomnia. Tried to review statistics on a table, but getting an error. Error Cannot find a table or object with the name Text Msg 2501, Level 16, State 45, Line 3 Cannot find a table or object with the name "[dbo].[M?_TEST]". Check the system catalog. Trouble Shooting Review Tables … Continue reading SQL Server, SSMS, and Unicode characters

DBeaver – Generate SQL Insert Statements from Data

Script SQL Insert Statement from Data Using SQL Server Management Studio ( SSMS), I entered quite a bit of data into a new table. Not wanting to ever repeat that arduous process, I sought out tools that will allow me to script out the data.   Script Data Yes, BCP will work, but I will … Continue reading DBeaver – Generate SQL Insert Statements from Data

Database – Create Table Syntax in different Databases

Preface Wanted to cover a couple of databases and show by example how the "Create table" implementation differ.   Databases Couple of unfortunates: Will only cover two of the many database flavors Syntactically create table is very broad and so will not cover the many options We will only cover what it took to convert … Continue reading Database – Create Table Syntax in different Databases

Transact SQL – Error – “Arithmetic overflow error converting numeric to data type numeric”

Background Revising a SQL Code snippet for finding Bookmark Lookups and ran into an error stating: Msg 8115, Level 16, State 1 Arithmetic overflow error converting numeric to data type numeric Code Original Code Here is the original code ...   Error Message Remediation To remediate please try one of the options stated below: Set … Continue reading Transact SQL – Error – “Arithmetic overflow error converting numeric to data type numeric”

SQL Server – Data Sampling – TABLESAMPLE

Background A quick follow-up to a couple of blog posts on pruning.  We have a requirement to prune about a dozen log tables.  The lone decisive factor is the number of months elapsed. After experiencing deadlocks and the like took to the .Net and re-discovered that it might be better to use the Clustering ID … Continue reading SQL Server – Data Sampling – TABLESAMPLE

SQL Server – Set Options – Detecting Current Settings

Background Set options can have devastating effect on SQL Server Operations.  I am too shamed to talk about once it bit me terribly. Other times, setting one or a couple of them helped us. Setting XACT_ABORT on is very important when changing data within Stored Procedure. Setting TRANSACTION ISOLATION LEVEL  to "read uncommitted" could possibly help … Continue reading SQL Server – Set Options – Detecting Current Settings