When working with large SQL Server databases, DBAs often need to execute DDL scripts containing thousands of lines of code. While SQLCMD provides a straightforward way to execute these scripts, its error reporting can be deficient when troubleshooting failures in large scripts. Recently, I encountered this issue while executing a large DDL file. The standard SQLCMD output only showed generic errors like “Function ‘fn_xyz’ already exists with the same name” without providing context about which specific statement caused the error. The line number displayed usually refers to the line number in the DDL statement and not the line number in the context of the larger DDL file.
For effective troubleshooting, I needed to see the SQL (DDL) statement that was executed followed by the error. Having these elements together would allow me to quickly correlate errors with their corresponding SQL statements, significantly reducing debugging time. To fulfill this need, I wrote a Python solution that provides granular execution control and comprehensive output logging. This approach offers several advantages:
Individual statement execution
Paired output of SQL and results
Clear separation between statements
The python code and sample successful and unsuccessful results are below.
Python code
import pyodbc
import os
from datetime import datetime
def execute_sql_file(input_file, output_file, server, user, password):
# Get password from environment variable for security
connection_string = f'DRIVER={{SQL Server}};SERVER={server};DATABASE=master;UID={user};PWD={password}'
try:
# Establish connection
conn = pyodbc.connect(connection_string)
cursor = conn.cursor()
current_batch = []
with open(input_file, 'r') as infile, \
open(output_file, 'w') as outfile:
# Write header with timestamp
outfile.write(f"SQL Execution Results - {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}\n")
outfile.write("-"*80 + "\n\n")
for line in infile:
# line = line.strip()
line = line.rstrip('\n\r') # removes both \n and \r
# Skip empty lines
# if not line:
# continue
# If we hit a GO statement, execute the batch
test_line = line
if test_line.strip().lower() == 'go':
if current_batch:
sql_statement = '\n'.join(current_batch)
# Write the input SQL to output file
outfile.write("Input SQL:\n")
outfile.write("-"*11 + "\n")
outfile.write(sql_statement + "\n\n")
try:
# Execute the SQL batch
results = cursor.execute(sql_statement)
# Write results header
outfile.write("Results:\n")
outfile.write("-"*8 + "\n")
# Fetch and write column names if available
if cursor.description:
columns = [column[0] for column in cursor.description]
outfile.write(" | ".join(columns) + "\n")
outfile.write("-" * (len(" | ".join(columns))) + "\n")
# Fetch and write results
for row in results:
outfile.write(" | ".join(str(value) for value in row) + "\n")
conn.commit()
except pyodbc.Error as e:
# Seems to return error 'Protocol error in TDS stream' if the object exists
# so ignoring
if e.args[0] == 'HY000' and 'Protocol error in TDS stream' in str(e):
outfile.write(f"Ignoring TDS Protocol error: {str(e)}\n")
outfile.write("Possibly caused by object already existing\n")
pass
else:
outfile.write(f"Error executing SQL: {str(e)}\n")
conn.rollback()
outfile.write("\n" + "="*100 + "\n\n")
current_batch = []
else:
current_batch.append(line)
except pyodbc.Error as e:
print(f"Database connection error: {str(e)}")
finally:
if 'conn' in locals():
conn.close()
def main():
# Configuration
input_file = "input.sql"
output_file = "sql_results.txt"
server = "my_server"
user = "my_user"
password = "my_password"
execute_sql_file(input_file, output_file, server, user, password)
print("SQL execution completed. Check output file for results.")
if __name__ == "__main__":
main()
Successful execution
SQL Execution Results - 2025-01-29 20:02:42 -------------------------------------------------------------------------------- Input SQL: ----------- USE [dcdb01] Results: -------- ===================================================================================== Input SQL: ----------- use [dcdb01] Results: -------- ===================================================================================== Input SQL: ----------- CREATE TABLE [dbo].[dc_test_01]( [id_01] [bigint] NOT NULL, [data_01] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [id_01] PRIMARY KEY CLUSTERED ( [id_01] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] Results: -------- ===================================================================================== Input SQL: ----------- CREATE TABLE [dbo].[dc_test_02]( [id_02] [bigint] NOT NULL, [data_02] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [id_02] PRIMARY KEY CLUSTERED ( [id_02] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] Results: -------- =====================================================================================
Unsuccessful execution
SQL Execution Results - 2025-01-29 20:01:38
--------------------------------------------------------------------------------
Input SQL:
-----------
USE [dcdb01]
Results:
--------
=====================================================================================
Input SQL:
-----------
use [dcdb01]
Results:
--------
=====================================================================================
Input SQL:
-----------
CREATE TABLE [dbo].[dc_test_01](
[id_01] [bigint] NOT NULL,
[data_01] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [id_01] PRIMARY KEY CLUSTERED
(
[id_01] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Ignoring TDS Protocol error: ('HY000', '[HY000] [Microsoft][ODBC SQL Server Driver]Protocol error in TDS stream (0) (SQLExecDirectW)')
Possibly caused by object already existing
=====================================================================================
Input SQL:
-----------
CREATE TABLE [dbo].[dc_test_02](
[id_02] [bigint] NOT NULL,
[data_02] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [id_02] PRIMARY KEY CLUSTERED
(
[id_02] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Ignoring TDS Protocol error: ('HY000', '[HY000] [Microsoft][ODBC SQL Server Driver]Protocol error in TDS stream (0) (SQLExecDirectW)')
Possibly caused by object already existing
=====================================================================================