Django migrate Sqllite to Mysql

Steps to migrate Django Sqllite DB to MySql DB

1. python manage.py dumpdata  -o datadump.json
2. Change settings.py to your mysql

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'database',
        'USER': 'username',
        'PASSWORD': 'password',
        'HOST': 'localhost',   # Or an IP Address that your DB is hosted on
        'PORT': '3306',
    }
}

3. Check you have mysqlclient, else run below command: 

       apt-get install python-dev libmysqlclient-dev 

       pip install mysqlclient

4. python manage.py migrate --run-syncdb5. python manage.py loaddata datadump.json

Google BigQuery - Calculating Minutes and Hours Differences

Assume you have a date column "OrderedDate", then we can calculate the total difference in minute and hour with respect to current date time:

SELECT
(CURRENT_TIMESTAMP() - TIMESTAMP_TO_USEC(OrderedDate)) / 1000000/ 3600 AS Diff_Hour,
 (CURRENT_TIMESTAMP() - TIMESTAMP_TO_USEC(OrderedDate)) / 1000000/ 60 AS Diff_Minute
FROM [Project:Dataset.Table]

General Copy Command to load S3 file data to Redshift

General Copy Command 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;

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

T-SQL to Diable all Jobs in SQL Agent

USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE Enabled = 1;
GO

ODBC Connectivity Issue - Missing System DSN connection in SSIS

We often come across this issue when our working server and source server have different bits 32 or 64.

You might have created System DSN in your system, but when try to create connection manager, the DSN will be missing in ODBC list. To overcome this you need to create DSN connection in appropriate ODBC (32/64).

Perform the followings:

Open command window:


1. Navigate to C:\Windows\Sysos64\Odbacd32.exe

2. ODBC Connection wizard will appear.
3. Create a new system DSN there.
4. Now try creating Connection Manager in SSIS package

Attach MDF file to a Database

Below is the query to attach MDF file to a database:

CREATE DATABASE AdventureWorks2008DWR2 ON

( FILENAME = N'C:\Users\mvaradhan\Downloads\AdventureWorksDW2008R2.mdf')

FOR ATTACH

GO

Attaching MDF file to a Database

Below is the query to attach mdf file to a database:

CREATE DATABASE AdventureWorks2008DWR2 ON

( FILENAME = N'C:\Users\mvaradhan\Downloads\AdventureWorksDW2008R2.mdf')

FOR ATTACH

GO

Attaching .MDF file to a Database

Below is the script to attach AdventureWorks.mdf file to a newly created database:

CREATE DATABASE AdventureWorks2008DWR2 ON

( FILENAME = N'C:\Users\mvaradhan\Downloads\AdventureWorksDW2008R2.mdf')

FOR ATTACH

GO

Call SSRS Report in ASP .NET with Parameters

The below steps help to call an SSRS report in Web Page designed using ASP.NET. The practice the below code we should install AJAX toolkit.

1. Create anew ASP wep page project.
2. Add a new web form.
3. Add th ebelow scripts in the WEB for designer:

 <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="SSRS2.aspx.cs" Inherits="SSRS_WEB.WebForm2" %>
<%@ Register assembly="Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" namespace="Microsoft.Reporting.WebForms" tagprefix="rsweb" %>
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <table>
       <tr>
           <td>Start Date: </td>
           <td><asp:TextBox Width="180" runat="server" ID="StartDatePr"/></td>
           <td><asp:CalendarExtender runat="server" TargetControlID="StartDatePr"/></td>
           <td>End Date: </td>
           <td><asp:TextBox Width="180" runat="server" ID="EndDatePr"/></td>
           <td><asp:CalendarExtender ID="CalendarExtender1" runat="server" TargetControlID="EndDatePr"/></td>
           <td><asp:Button Text="Show Report" ID="btnSubmit" runat="server" onclick="btnSubmit_Click" /></td>
       </tr>
   </table>
    </div>
    <asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">
    </asp:ToolkitScriptManager>
    <rsweb:ReportViewer ID="MyReportViewer" runat="server" Font-Names="Verdana"
        Font-Size="8pt" InteractiveDeviceInfos="(Collection)" ProcessingMode="Remote"
        WaitMessageFont-Names="Verdana" WaitMessageFont-Size="14pt" Height="800px"
        Width="1000px">
        <ServerReport ReportServerUrl="" />
    </rsweb:ReportViewer>
    </form>
</body>
</html>

4. Add below code in respective .CS file:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Microsoft.Reporting.Common;
using Microsoft.Reporting.WebForms;
namespace SSRS_WEB
{
    public partial class WebForm2 : System.Web.UI.Page
    {
       protected void Page_Load(object sender, EventArgs e)
        {
          
        }
        protected void btnSubmit_Click(object sender, EventArgs e)
       
        {
            //First
            MyReportViewer.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote;
            MyReportViewer.ServerReport.ReportServerUrl = new Uri("http://localhost/reportserver_Sathya"); // Report Server URL
            MyReportViewer.ServerReport.ReportPath = "/SQLSSRS/Dashboard"; // Report Name
            MyReportViewer.ShowParameterPrompts = false;
            MyReportViewer.ShowPrintButton = false;
            ReportParameter[] parameters = new ReportParameter[2];
            parameters[0] = new ReportParameter("StartDate", StartDatePr.Text);
            parameters[1] = new ReportParameter("EndDate", EndDatePr.Text);
            MyReportViewer.ServerReport.SetParameters(parameters);
            MyReportViewer.ServerReport.Refresh();


        }
    }
}

On executing the page, you can see SSRS reoport called in Web page.

SQl query to database backup

The below query helps to take a backup of a DB:


DECLARE @name VARCHAR(50) -- database name

DECLARE @path VARCHAR(256) -- path for backup files

DECLARE @fileName VARCHAR(256) -- filename for backup

DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'D:\Backup\' SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR SELECT name FROM MASTER.dbo.sysdatabases WHERE name IN ('SalesDW')

OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0

BEGIN

SET @fileName = @path + @name + '_' + @fileDate + '.BAK'

BACKUP DATABASE @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name

END CLOSE db_cursor

DEALLOCATE db_cursor

SQL Query to drop all constraints in a table

DECLARE @sql nvarchar(255), @DB nvarchar(50), @Table nvarchar(50)SET @DB = 'source_1'SET @Table = 'Product'WHILE
EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog = @DB AND table_name = @Table)BEGIN


SELECT @sql = 'ALTER TABLE ' + @table + ' DROP CONSTRAINT ' + CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog = @DB AND table_name = @tableEXEC sp_executesql @sqlEND

Fastest Performing Rowcount SQL Query

Try executing the below queries in a table containing millins of records. Both the queries yields row count of the table, but the performance of Query 1 is more.

Query 1:


SELECT SUM(row_count) FROM Sys.dm_db_partition_stats
WHERE OBJECT_ID = OBJECT_ID('AccountCommunications')
AND (index_id =0 or index_id =1);

Query 2:
Select COUNT(*) From dbo.AccountCommunications

Query to Backup and Restore Database

Below is the query to backup and restore a database:

BACKUP DATABASE [Source_1] TO DISK = N'E:\Backup\Source_1.bak' WITH
NOFORMAT
, COMPRESSION
.NOINIT
,NAME  = N'Full bakup of Source_1'
, SKIP, STATS = 10
GO

--2.Optionally, determine the logical and physical names of the files in the backup set that contains the full database backup that you want to restore.
RESTORE FILELISTONLY FROM DISK = N'E:\Backup\Source_1.bak'
-- 3. Restore
RESTORE DATABASE [Source_3]FROM DISK = N'E:\Backup\Source_1.bak' WITH FILE = 1,
MOVE 'Source_1' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Source_3.mdf'
, MOVE 'Source_1_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Source_3_log.ldf',
NOUNLOAD, REPLACE,STATS = 10
GO;