secure-file-priv CSV import HELL - partial workaround

Running MySQL on Windows Import a csv file using Load Data InFile "{my path here}"...

Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

so tedious NO going to the secure-file-priv path and dropping your CSV in there DOES NOT WORK "C:\ProgramData\MySQL\MySQL Server 8.0\Uploads" NO it does not work when you open up all privs to "Everybody". NO it does not work if you edit the ini file and set it to the folder I actually want to use C:\ProgramData\MySQL\MySQL Server 8.0\my.ini

# Secure File Priv. secure-file-priv="C:\folder\folder\folder"

YES I restarted MySQL Services and I said NO it didn't work. What does work? This crap. Set the ini file to the C:\ drive

# Secure File Priv. secure-file-priv="C:\"

If you run your import now with ONLY C: and the csv filename in the script like so

LOAD DATA INFILE "C:\myCSV.csv"

You will get THIS nonsense error:

Error Code: 29. File 'C:\ProgramData\MySQL\MySQL Server 8.0\Data\customers_2020.csv' not found (OS errno 2 - No such file or directory)

So yeah, I'd call that a bug. I say tomato you say supercalifrackiforniaspinyourmommaspaceshuttle Anyway, I didn't want my CSV files in the Data folder, but you can just use that and be done if you want. I wanted to use something similar to the Uploads folder the developers want you to use, but that's still up a level from here. I just split the difference and moved the "Uploads" folder to C:\ProgramData\MySQL\MySQL Server 8.0\Data\Uploads\ then dropped my CSV filed in Uploads, then executed this script to make it ACTUALLY work

LOAD DATA INFILE "C:\Uploads\\MyCSVFile.csv" REPLACE INTO TABLE customers CHARACTER SET latin1 FIELDS TERMINATED BY ',' ENCLOSED BY '"'LINES TERMINATED BY '\n';

*yes I DID use two back wacks in that spot "\\". If I didn't I would get the error: Error Code: 29. File 'C:\ProgramData\MySQL\MySQL Server 8.0\Data\UploadsMyCSVFile.csv' not found (OS errno 2 - No such file or directory) dumb.

SSRS - dynamically hide and show subreports - WITHOUT them generating while hidden

Easy. Add a tablix to your report. Delete extra columns and rows so you have just one cell.. one row, one column. Put your subreport in the cell. Tablixes need data - but it doesn't have to be real data. If needed, you can create a dummy data source that just has something like "Select 1 as ID" Right click on the corner of your tablix and go to tablix properties, select the data source of your choice. Visibility will hide or show your tablix, which is needed. But your subreport will still hit the database and generate the visuals, and this is a massive performance killer. Go to the FILTER tab. Create a filter expression that returns false to prevent the report from generating and true when you want your report to generate.

SIMPLE T-SQL to generate create table scripts

just run this in the database you want to script out --- use "Results to Text" mode: EXEC sp_msforeachtable 'SELECT ''Create table ? ('' UNION ALL SELECT CASE WHEN rn > 1 THEN '' ,'' ELSE '' '' END + x FROM (SELECT TOP 100 percent ''[''+COLUMN_NAME + ''] '' + DATA_TYPE + CASE WHEN DATA_TYPE IN (''varchar'',''char'') THEN ''('' + CAST(CHARACTER_MAXIMUM_LENGTH as varchar) + '')'' ELSE '''' END + '' ''+ case when IS_NULLABLE = ''YES'' THEN ''NULL'' ELSE ''NOT NULL'' END x ,ROW_NUMBER() OVER(ORDER BY COLUMNS.ORDINAL_POSITION) rn FROM INFORMATION_SCHEMA.columns WHERE COLUMNS.TABLE_NAME = replace(replace(''?'',''[dbo].['',''''),'']'','''') ) x UNION ALL SELECT '')'' ' outputs runnable create table script

turn any list of text into a table

If you're like me and you regularly get people emailing you long lists of .. whatever.. zip codes, addresses, VIN numbers, etc .. and they want you to do something with each item in the list, but the lists are always inconsistent and you spend a lot of time just cleaning up the list so you can move forward with the task, here's a handy quick cleaner upper I made. it will turn this:

lkfdjhhdf 2356345 dfsdsf dfds df fd dsffd sad,dsa,,DS,F,,FSADFSDF, DF

into a nice table:

first of all, you need this function. Split parameter string from comma seperator in SQL IN clause then we do this:

DECLARE @item_list VARCHAR(4000)
SET @item_list = 
'
lkfdjhhdf
2356345

dfsdsf

dfds

df
fd

dsffd
sad,dsa,,DS,F,,FSADFSDF,
DF
'

DECLARE @c VARCHAR(4000) = REPLACE(REPLACE(REPLACE(REPLACE(@item_list,CHAR(13)+CHAR(10),','),',,',','),',,',','),',,',',')
IF RIGHT(@c,1) = ',' SET @c = LEFT(@c,LEN(@c)-1)
IF LEFT(@c,1) = ',' SET @c = RIGHT(@c,LEN(@c)-1)

IF RIGHT(@c,1) = ',' SET @c = LEFT(@c,LEN(@c)-1)
IF LEFT(@c,1) = ',' SET @c = RIGHT(@c,LEN(@c)-1)

set nocount on
DECLARE @list AS TABLE (item VARCHAR(100))

INSERT @list
SELECT [sID] FROM redbumperdb.dbo.UF_CSVToTable(@c)

SELECT * FROM @list AS l

Set Based way to order items with SQL

so you have a list of rules or something and you want your users to be able to change the processing order

DECLARE @new_item_priority INT = 1
DECLARE @reorder_priority AS TABLE (item_id INT, item_priority INT )

INSERT @reorder_priority 
SELECT item_id, ROW_NUMBER() OVER(ORDER BY rn) AS item_priority
FROM  (
    SELECT item_id, 
    CASE 
    WHEN item_id = @p_item_id AND @new_item_priority < item_priority THEN CAST(@new_item_priority AS FLOAT) -.1
    WHEN item_id = @p_item_id AND @new_item_priority > item_priority THEN CAST(@new_item_priority AS FLOAT) +.1
    ELSE item_priority
    END rn
    FROM dbo.items
    -- WHERE clause if needed..
) x
ORDER BY rn


UPDATE i
    SET item_priority = rp.item_priority
FROM dbo.items i
JOIN @reorder_priority rp ON rp.item_id = i.item_id

SQL Agent Job Logging To Job History Using TSQL

Simple! Use a print statement! Try it! In SQL Server Management Studio (SSMS), in Object Explorer, right click the JOBS folder and click NEW JOB name it TEST click STEPS on the left hand pane click the NEW button name the step TEST STEP make sure the type is Transact-SQL script(TSQL) database can be anything, master is fine. in the big COMMAND box type: Print 'test test test' click OK twice and run the job Right click the job and click VIEW HISTORY click the + to open the list of steps, click on step on and look in the grey pane at the bottom for the details. It will look something like this:

Message Executed as user: sqluser. test test test [SQLSTATE 01000] (Message 0). The step succeeded.

that bolded text is your message! ta da!

eShop WordPress plugin - fix when Shipping Rates aka Shipping Class disappears

It's just a setting, no big deal. Go to SETTINGS >> eSHOP Scroll all the way down to the bottom of the page. --in the section CHECKOUT OPTIONS there are four drop-downs The drop-down labelled "Minimal checkout form. (this also disables shipping costs)" needs to be set to "NO". If you set this option to "yes" is takes the Shipping Rates selector off of the page and post edit screens