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