AWS Redshift: 'String contains invalid or unsupported UTF8 codepoints. Bad UTF8 hex sequence: b6

When you receive below error message from AWS redshift while executing use copy command then use 'ACCEPTINVCHARS ESCAPE' syntax in copy command:

Error Code: 'String contains invalid or unsupported UTF8 codepoints. Bad UTF8 hex sequence: b6 (error 3) '

COPY <tablename> from 's3://bucket/folder/file.txt'
CREDENTIALS '**********;aws_secret_access_key=******'
DELIMITER   '|' ACCEPTINVCHARS ESCAPE IGNOREHEADER 1;

AWS String contains invalid or unsupported UTF8 codepoints. Bad UTF8 hex sequence: b6

When you receive below error message from AWS redshift while executing use copy command then use 'ACCEPTINVCHARS ESCAPE' syntax in copy command:

Error Code: 'String contains invalid or unsupported UTF8 codepoints. Bad UTF8 hex sequence: b6 (error 3) '

COPY <tablename> from 's3://bucket/folder/file.txt'
CREDENTIALS '**********;aws_secret_access_key=******'
DELIMITER   '|' ACCEPTINVCHARS ESCAPE IGNOREHEADER 1;

General COPY command to load data from S3 file

General Copy Command trom to load S3 file data to Redshift table:
COPY <tablename> from 's3://bucket/folder/file.txt'
CREDENTIALS 'aws_access_key_id=**********;aws_secret_access_key=*********'
DELIMITER   '|' IGNOREHEADER 1;

PYODBC Connection in Python

Creating ODBC Connection in Python

STEP 1: Installing pypyodbc can be done via the commandline:

           >>C:\Python34\Scripts>pip install pypyodbc


Using the below command in your code for better naming conventions:

>> import pypyodbc as pyodbc

Python Script to Connect to SQL Server and Retrieve Date From a Table

# Python Script to Connect to SQL Server and Retrieve Date From a Table

import sys
import pypyodbc

# Create a new database:
connection_str =    """
                    Driver={SQL Server Native Client 11.0};
                    Server=localhost;
                    Database=master;
                    Trusted_Connection=yes;
                    """
db_connection = pypyodbc.connect(connection_str)
db_connection.autocommit = True
db_cursor = db_connection.cursor()
sql_command =   """
                CREATE DATABASE MYDB
                """
try:
    db_cursor.execute(sql_command)
except pypyodbc.ProgrammingError:
    print("Database 'MYDB' already exists.")
db_connection.autocommit = False

db_cursor.close()
del db_cursor
db_connection.close()

# Connect a database.
connection_str =    """
                    Driver={SQL Server Native Client 11.0};
                    Server=Localhost;
                    Database=MYDB;
                    Trusted_Connection=yes;
                    """
db_connection = pypyodbc.connect(connection_str)
db_connection.autocommit = True
db_connection.autocommit = False
db_cursor = db_connection.cursor()

# Select rows from a table:
# 1) Select all columns of all rows:
sql_command =   """
                SELECT *  FROM [ods].[MYTABLE]
                """
db_cursor.execute(sql_command)
rows = db_cursor.fetchall()
for row in rows:
      userid, fname= row[0] ,row[1]
  
      # Now print fetched result
      print (userid,fname)

SQL CLR Error: Publishing SQL Server Project from C# Visual Studio

Created new SQL Server project in Visual studio, to generate dll file having SQL function, when we tried to use published function we ended with below error message:

Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.

To fix the above issue enable CLR in sql server:


sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

After applying the code we tried to execute the created function, but ended in below error:

A .NET Framework error occurred during execution of user-defined routine or aggregate "<functionname>":
System.Security.HostProtectionException: Attempted to perform an operation that was forbidden by the CLR host.

The protected resources (only available with full trust) were: All
The demanded resources were: MayLeakOnAbort

System.Security.HostProtectionException:
   at UserDefinedFunctions.<functionname>(SqlDateTime pstTime, String Timezone)

To overcome the above issue, run below commands:

ALTER ASSEMBLY <functionname> WITH PERMISSION_SET = Safe

ALTER DATABASE <DBNAME>
SET TRUSTWORTHY ON


ALTER ASSEMBLY Timezone WITH PERMISSION_SET = UnSafe

Convert Unix Timestamp to Date Format

SELECT  DATE_FORMAT(FROM_UNIXTIME(TimeStamp), '%d-%m-%Y %h:%i:%s') AS LogDate
FROM dbo.reportlog
ORDER BY Timestamp DESC
LIMIT 10