Sql Server & SSRS Tips

Mysql SQL Error (1267): Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '=' If you face this error due to different collations on the tables you are joining, So you need to set collation like this in joins. Select * FROM data_temp d inner JOIN offices o ON d.School_Name COLLATE UTF8MB4_GENERAL_CI =o.name COLLATE UTF8MB4_GENERAL_CI  

-- To export large CSV file into MySQL database, first you need to create that table (empty). -- Remove (comment) "strict_trans_tables" sql_mode from your mysql configuration (my.cnf in linux and      my.ini in windows). --  Run MySQL command line client from the start menu. --  The console will open, provide MySQL password. --  select your database          use <name_of_your_database>; --  Execute below command:           LOAD DATA INFILE 'D:\\Data.csv' IGNORE INTO TABLE your_table_name fields terminated                     BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'  LINES TERMINATED BY '\r\n'; --  IGNORE keyword in above statement will ignore the errors and rows in csv that have errors and            export remaining rows in table. -- If your csv don't have any errors and mis...

 To load a large SQL file into the database (or as database). -- In Windows Run MySQL command line client from the start menu: -- console will open, provide MySQL password -- select your database use <name_of_your_database>; -- run below command provide your SQL file path source <path_of_your_.sql> For example: source F:\iConsult Work\Tipp - Trade Information Portal\3years_TradeStat_FullData.sql -- In linux upload that SQL file in public_html from cpanel, then copy the path of that file from cron job -- go/run terminal from cpanel, then connect the database with 'mysql -u user_name -p' command. -- then select your database with 'use your_database' command -- and then execute the below command after changing the path after source keyword. For example: source /home/dhalabga/public_html/tiger_data_backup_tiger_force_backup.sql To export / load large table data into file(csv) -- This will export large table data into CSV, but before this, you have to set secure_...

 Break comma-separated string/column/parameter into multiple rows in MYSQL. with recursive     cs_string as ( select 'a,b,c,d' as items),     cs_rowID as ( select 1 as n union select n + 1 from cs_rowID, cs_string         where n <= length(items) - length(replace(items, ',', ''))) select distinct substring_index(substring_index(items, ',', n), ',', -1)  group_name from cs_rowID, cs_string Output:

 mySQL search query on multiple columns with a single parameter. set @search='32' ; select DISTINCT p.id as product_id, p.name as title, p.sku, p.description,     p.material,c.name as color, ct.name as category from  products p inner join color_product cp on p.id=cp.product_id inner join colors c on cp.color_id=c.id inner join categories ct on p.category_id=ct.id where p.name  LIKE CONCAT("%",@search,"%") OR   p.sku  LIKE CONCAT("%",@search,"%") OR       p.description  LIKE CONCAT("%",@search,"%") OR       p.material  LIKE CONCAT("%",@search,"%") OR       c.name   LIKE CONCAT("%",@search,"%") OR       ct.name   LIKE CONCAT("%",@search,"%") ;

 To combine rows as comma separated for a column instead of multiple rows we will use GROUP_CONCAT function with that column, but all the remaining columns will be in group by clause. below is the query for products with multiple colors for a single product. we will combine colors column as comma separated in the below query. select DISTINCT p.id as product_id , p.name as title, p.sku, p.material,  p.feature_image_path, feature_image_name ,                 GROUP_CONCAT(c.name) color  -- it will combile multi values as comma seperated rather than multiple rows but remaining columns will be in group by from  products p inner join color_product cp on p.id=cp.product_id inner join colors c on cp.color_id=c.id inner join categories ct on p.category_id=ct.id where p.name  LIKE CONCAT("%",'red',"%") OR   p.sku  LIKE CONCAT("%",'red',"%") OR       p.description  LIKE CONCAT("%",'red',"%"...

If the last letter from a string is Alpha then remove it otherwise return the string as it is.  DECLARE @STR nvarchar(200) = '1641746A' if ISNUMERIC(RIGHT(@STR,1))=0 begin SET @STR = SUBSTRING(@STR, 1, (LEN(@STR) - 1)) SELECT @STR END ELSE SELECT @STR