dbo | simple PHP database objects

I have a requirement to build a SSIS package that sends HTML formatted emails and then saves the emails as tiff files. I have created a script task that processes the necessary records and then coverts the HTML code to the tiff. I have split the process into separate packages, the email send works fine the converting HTML to tiff is causing the issue.

When running the package manually it will process all files without any issues. my test currently is about 315 files this needs to be able to process at least 1,000 when finished with the ability to send up to 10,000 at one time. The problem is when I set the package to execute using SQL Server Agent it stops at 207 files. The package is deployed to SQL Server 2019 in the SSIS Catalog

What I have tried so far

I started with the script being placed in a SSIS package and deployed to the server and calling the package from a step (works 99.999999% of the time with all packages) tried both 32 and 64 bit runtime. Never any error messages just Unexpected Termination when looking at the execution reports. When clicking in the catalog and executing package it will process all the files. The SQL Server Agent is using a proxy and I also created another proxy account with my admin credentials to test for any issues with the account.

Created another package to call the package and used the Execute Package Task to call the first package, same result 207 files. Changed the execute Process task to an Execute SQL Task and tried the script that is created to manually start a package in the catalog 207 files. Tried executing the script from the command line both through the other SSIS package and the SQL Server Agent directly same results 207 files. If I try any of those methods directly outside SQL Server Agent the process runs no issues.

I converted the script task to a console application and it works processing all the files. When calling the executable file from any method from the SQL Server Agent it once again stops at the 207 files.

I have consulted with the companies DBA and Systems teams and they have not found anything that could be causing this error. There seems to be some type of limit that no matter the method of execution SQL Server Agent will not allow. I have mentioned looking at third-party applications but have been told no.

I have included the code below that I have been able to piece together. I am a SQL developer so C# is outside my knowledge base. Is there a way to optimize the code so it only uses one thread or does a cleanup between each letter. There may be a need for this to create over ten thousand letters at certain times.

Update

I have replaced the code with the new updated code. The email and image creation are all included as this is what the final product must do. When sending the emails there is a primary and secondary email address and depending on what email address is used it will change what the body of the email contains. When looking at the code there is a section of try catch that sends to primary when indicated to and if that fails it send to secondary instead. I am guessing there is a much cleaner way of doing that section but this is my first program as I work in SQL for everything else.

Thank You for all the suggestions and help.

Updated Code

I am trying to make sense of the following error that I started getting when I setup my python code to run on a VM server, which has 3.9.5 installed instead of 3.8.5 on my desktop. Not sure that matters, but it could be part of the reason.

The error

I have two procedures, one outer procedure and one inner procedure, where I would like to understand the behaviour of the error handling. The inner procedure provokes an error and is trying to insert something in the catch block into a table. After that the error is raised, passed to the outer procedure and then should roll back the transaction.

I'm trying to understand why my code is throwing the error message:

Ok, I'm totally lost on deadlock issue. I just don't know how to solve this.

I have these three tables (I have removed not important columns):

Suppose I have a table like this,

I want to select id's where tagId includes both 2 and 5. For this fake data set, It should return 1 and 3.

I tried,

In Mongodb I have this field:

I'm trying to create a procedure that will:

• take any number as an input e.g. 102
• find the sequence range it belongs e.g. 100 to 103
• return a suggested next number to the user e.g. 104

The table itself will look something like this:

Ideally the output of the query would return something like this:

I this what I'm trying to do is linked to some kind of Gap and Island technique. I had a look at trying something from here but couldn't quite get it to work. Gaps and Islands Link

This is what I tried coming up with...

I have a stored procedure it takes comma separated string as input. Which might be too large some times approximately more than 8 thousand characters or more. In that situation, query performance goes down sometimes. And I think there is a limitation for the character length inside the IN clause. For that, sometimes I get errors. Now, I need to know is it better to use a Custom TABLE TYPE as parameter and use Inner JOIN to find the result. If it is then why is it. Here are my 2 stored procedures (minimal code):

I have setup a local SPARQL endpoint with DBPedia database using Openlink Virtuoso through this guide. Then I tried to query my database through Python with the help of RDFLib and SPARQLWrapper.

Problem is the time it take for a query (through Python) is very long, usually 2 to 3 seconds before I can get a result back. But when I use my browser to query directly at the endpoint (go to localhost from Chrome), for the same type of query I get the result instantly.

I don't think it's a problem with my Python code, because if I keep the same code and just change to DBPedia endpoint, I can get a query result within 0.1 to 0.2 seconds. My database file is around 6GB and I have configured the ini file to use more memory as instructed.

Anyone can troubleshoot the problem for me? I'm thinking I need to tweak some parameter with the virtuoso server but I don't know where to start. Thanks!

Here's what my query looks like (DBPedia endpoint or local endpoint directly from Chrome: almost instant result; local endpoint through Python: 2+ seconds):