by Contributed | Dec 30, 2022 | Technology
This article is contributed. See the original author and article here.
Today, I worked on a service request that our customer got several issues that I would like to share with you my findings here.
1) pyodbc.Error: (‘HY000’, ‘[HY000] [Microsoft][ODBC Driver 17 for SQL Server]Connection is busy with results for another command (0) (SQLExecDirectW)’)
- This error ocurrs when the Python code is trying to open a new cursor when we have a previous one with results.
import os
import pymssql
import pyodbc
conn = pyodbc.connect("DRIVER={ODBC Driver 17 for SQL Server};server=servername.database.windows.net,1433;UID=username;PWD=Password;database=dbName;Mars_Connection=no");
cursor = conn.cursor()
cursor.execute('select * from sys.databases')
row = cursor.fetchone()
print(f"row={row}")
cursor3 = conn.cursor()
cursor3.execute('select * from sys.databases')
cursor3.close()
row = cursor3.fetchone()
print(f"row={row}")
conn.close()
- As we mentioned in our previous article enabling Mars we could fix this issue.
2) pyodbc.ProgrammingError: Attempt to use a closed cursor.
import os
import pymssql
import pyodbc
conn = pyodbc.connect("DRIVER={ODBC Driver 17 for SQL Server};server=servername.database.windows.net,1433;UID=username;PWD=Password;database=dbName;Mars_Connection=no");
cursor = conn.cursor()
cursor.execute('select * from sys.databases')
cursor.close()
row = cursor.fetchone()
print(f"row={row}")
cursor3 = conn.cursor()
cursor3.execute('select * from sys.databases')
cursor3.close()
row = cursor3.fetchone()
print(f"row={row}")
conn.close()
- In this situation, the issue is regarding in the line 11 that the cursor is closed before executing it.
3) pyodbc.ProgrammingError: The cursor’s connection has been closed.
import os
import pymssql
import pyodbc
conn = pyodbc.connect("DRIVER={ODBC Driver 17 for SQL Server};server=servername.database.windows.net,1433;UID=username;PWD=Password;database=dbName;Mars_Connection=no");
cursor = conn.cursor()
cursor.execute('select * from sys.databases')
conn.close()
row = cursor.fetchone()
print(f"row={row}")
cursor3 = conn.cursor()
cursor3.execute('select * from sys.databases')
cursor3.close()
row = cursor3.fetchone()
print(f"row={row}")
conn.close()
- This situation is happening when the connection is closed before obtaining the data or run the cursor.
4) ERROR:asyncio:Unclosed connection – connection: <aioodbc.connection.Connection object at 0xXXX
This is a not ODBC driver code, this error message is coming from an exception captured by aioodbc/connection.py at master · aio-libs/aioodbc · GitHub because our customer is using this library aioodbc/aioodbc at master · aio-libs/aioodbc · GitHub for async ODBC calls.
def __del__(self):
if not self.closed:
# This will block the loop, please use close
# coroutine to close connection
self._conn.close()
self._conn = None
warnings.warn("Unclosed connection {!r}".format(self),
ResourceWarning)
context = {'connection': self,
'message': 'Unclosed connection'}
if self._source_traceback is not None:
context['source_traceback'] = self._source_traceback
self._loop.call_exception_handler(context)
So, all these errors above points to either the connection was closed before executing next query or the connection is busy processing the previous query which could cause bottleneck.
Enjoy!
by Contributed | Dec 30, 2022 | Technology
This article is contributed. See the original author and article here.
In our daily work we constantly encounter what I like to call ‘The Time Thieves.’ These are the things that take away our ability to be proactive in our day-to-day job. They are the reasons we fall behind in our work and can’t always meet delivery dates. I’ve listed several of the time thieves that all of us can relate to:
- Unknown dependencies
- Too much work (WIP)
- Neglected Work
- Conflicting priorities
- Unplanned work
We are all too familiar with these items listed above. While I wish I had a magic bullet, I don’t, but we’re going to look at some ways in which we can help alleviate these. There very much needs to be a cultural change within your organization, but we can use tooling to assist us with this in our projects going forwards. I am going to focus on Azure Boards, which is part of the more widely known tool, Azure DevOps.
Firstly, we cannot plan for and prioritize what we cannot see. When starting a new project, we need to define our definite of done (DoD). A definition of done is a confirmed set of conditions that a project must complete in order to be accepted by the end users. A DoD is often made up of acceptance criteria, that ensure we complete our tasks, delivering higher quality work and eliminating the need to ‘re-do’ our work.
For example:
Definition of Done: In 3 months we will deploy the new website into Azure for the company global event.
Acceptance Criteria: The infrastructure is deployed using infrastructure as code (IaC) into Azure, with a resilient and scalable system in place, meeting our defined SLA. The website will be operational in time for end users to be able to register via the website and navigate all aspects successfully, also addressing all inclusion requirements for visibility.
While I just gave a high-level example, we now need to figure HOW we are going to achieve this in 3 months. This begins the planning phase where it is critical for all stakeholders to be involved. This includes, but not limited to: the operations team, the development team, the security team, project managers, etc. No one likes to get that email request or tap on the shoulder asking for a task to be done yesterday. We need everyone involved in this planning piece; it is crucial to the success of the project. This is a great time to bring in those subject matter experts who know what dependencies might exist, but this also allows for all of the teams to be involved and to prevent chasing them down and causing them to incur unplanned work.
We need to clearly define what we are trying to deliver, and we must delineate on what are the ‘must haves’ and the ‘nice to haves’ that can be fulfilled in the designated time frame. As a team, we are aware that we have 3 months to deliver our definition of done. We need to develop an agile methodology that sets milestones into our plan, providing checkpoints for the stakeholders.
Agile Methodology Planning
Now, we need to figure out HOW we get there. This is the planning phase where we need all stakeholders involved. We need to determine the ‘must haves’ and the ‘nice to haves’. As a team, we know that we have 3 months to achieve our definition of done, the planning part is key. We need to set some milestones in our planning as check in points for our stakeholders. From there we can fill in the tasks for each milestone.
Structure of work items and tasks
Defining the milestones and tasks do not require you to be a project manager, instead it’s something that ALL of your technical team should be involved in. Give them ownership in their tasks, this also allows for them to bring forward their existing work in progress and what other conflicting priorities might exist. Bring in those subject matter experts to help pick apart and identify known dependencies that exist (remember that time thief, unknown dependencies?), giving your teams the best chance of success. Nobody likes a surprise, give your team a head start by addressing the issues in the planning phase.
When planning, you often hear ‘I have too much work to do and can’t be involved.’ Find the time. Whether it’s a time block of 30 mins, or even 15, get those folks involved. Help them become vested in the project and more importantly, align your priorities. Carving out even just 30 mins in the beginning can save hours, days and weeks in delays later on. Prioritization and involvement of the tasks need to be planned out. We constantly have too much work because priorities in our companies and in projects are not set. Everything is always on fire, and everyone always wanted it done yesterday. This is where we see constant conflicts of interest amongst various teams in projects.
The time thief conflicting priorities exist because teams are not incentivized to help on your project, or they don’t see the value that it will drive.
Using the planning time to align priorities across teams will break down those silos. Take the time to understand each others deliverables and priorities. This is a great opportunity to uncover common ground, understand the other teams’ pain points and maybe how you can help each other better. How we incentivize our teams cause these silos within our organizations. The operational teams are expected to keep the lights on 24/7, probably even delivering against an SLA. The development teams are expected to push new features, which in the past has hurt the operational teams’ SLA. These are conflicting priorities. This is a great opportunity in this project to come together to find better ways of working and deliver value together. We’ve talked about these time thieves and how they stop us from delivering successful projects. Now let’s focus on Azure Boards, how the tool itself can up us organize and plan our projects.
I LOVE Azure Boards because it is the key to organizing your project, it enables you to plan and track your work. It also improves communication within a project by providing visibility as to what is happening on the project, allowing your engineers to do their work, knowing what tasks are being worked on and who they are assigned to, as well as give visibility and full traceability of all the work being undertaken in a project.
Azure Boards allows you to organize your work using a triage and prioritization system. Remember those time thieves of ‘unplanned work’, ‘neglected work’ and ‘WIP’? Boards uses a Kanban style view to help you organize all the tasks in each milestone, also giving you the ability to triage incoming work and finding a place to tackle those tasks. Giving you the ability to prioritize all work items, with reporting that can visualize how the team is progressing on the project.
Azure Boards is part of Azure DevOps which has so many features and capabilities baked in. You can watch the video that myself and Nana from Techworld with Nana put together on Azure Boards.
Azure Boards – The Features
Azure Boards allows teams to organize their projects by enabling an agile process. It uses a Kanban style view, that includes calendar views, configurable dashboards, and integrated reporting. These tools can be used from any small project and easily scales to use with much larger projects.
Project Delivery Analytics
Azure Boards enables your teams to track their work and track issues (such as tasks, bugs, dependency issues, etc) in a very easy to read format. The stakeholders can pull reports of how the project is progressing, it can also help the stakeholders to see how the teams tasks are prioritized. Boards holds your engineers accountable for their work but also giving insight to where those time thieves are interfering in the project delivery. Making it easier to evolve your project delivery methods and adjust timings to reach those milestones.
Looking at our project we outlined above, we can set up a delivery plan in Azure Boards that displays a calendar view of when our deliverables are due as well assisting us in planning the timings required to reach our milestones.
Delivery Plan View
We can also track dependencies and add items to the backlog when they crop up, addressing unplanned work. For example, we notice a performance issue with the website. The development team is blaming the infrastructure team, but the infrastructure team does not see any performance issues. Instead of watching each team blame each other, we can the issues together, encouraging both teams to tackle the issue. In the end, they did find a dependency on the code and the scalability in the infrastructure. This dependency was marked and going forward both teams can work together to resolve these issues.
In Azure Boards we can setup Sprints, a set time (let’s say 2 weeks) where we deliver a specific part of our wider project. Several sprints make up a milestone (let’s assume 2 sprints per milestone in our existing project – assuming a milestone is a month long). Each engineer is responsible for creating and assigning their tasks for the sprint. This gives the engineers ownership of what is to be delivered, but also allows for them to plan around their existing priorities and other known dependencies that need to be addressed. Giving your engineers ownership over their tasks and priorities incentivizes them to deliver better quality work, but also addresses their many time thieves, giving them control over their work.
Planning work is critical to project success, it enables organization and visibility from every single stakeholders, creating a culture within the teams that they are also involved in the project and not brought in when everything is on fire and due yesterday.
Azure Boards – Tracking Work and Deployments
Automation removes human error, let’s face it, as humans, we make a lot of mistakes. The website that we’re building in our example is deployed with infrastructure as code (IaC) into Azure. Whether we choose Bicep, Terraform or Ansible, is negligible, Azure DevOps can support deployment of any IaC tool into Azure. All of the work creating infrastructure deployments (writing the IaC), maintaining the infrastructure and on-going monitoring is managed as tasks in Azure Boards. The operations team can be proactive around maintenance, issues or any other items that crop up. Giving the engineers full traceability into their work, enabling them to recover faster from mistakes.
Azure Boards and GitHub
Let’s take a scenario where your development team stores all of their source code in GitHub. That’s okay! Azure DevOps and GitHub work seamlessly together. GitHub and Azure DevOps integrate to support using both platforms, even if the existing code resides in GitHub, the ability to use Azure Boards to plan and track your work can still be configured and managed from Azure Boards! You can also link any commits made to GitHub to your work items in Azure Boards, giving you full visibility of the work being undertaken.
Azure DevOps and Microsoft Teams
Communication is crucial for any project. Giving status updates, reporting issues, progress and everything else can be time consuming, but also time prohibitive as we often spend a lot of time in meetings. With Azure Boards and Microsoft Teams we can enable the two to communicate. You can set up a channel in Teams and configure what types of notifications you want to receive.
One example, a user reports an issue with our project. We can easily create a work item directly from the Teams channel based on that conversation with our colleague. We can also configure Teams to subscribe to certain types of work items in Boards, or monitor work item activity from our Teams channel.
Create a work item from a Teams channel
Traceability of how the work item was created
There are many ways to integrate the two tools, increasing visibility of project work and keeping an open line of communication with your stakeholders and other technical teams.
Azure Boards – In Summary
Azure Boards has so many features built into it. Most importantly, it helps your team to get organized for any size project and implement better working practices. Let’s face it, Azure DevOps is just a tool, there needs to be a culture change that takes place to help your teams achieve more.
Watch the full Azure DevOps Zero to Hero video on YouTube. Good luck with your next project and tackle those time thieves!
Recent Comments