I am facing a really confusing problem with our SQL Server, and I hope somebody can give me hint how to debug this issue.
Grab a cup of coffee or something, this is going to get slightly convoluted. And you may think along the way, “Well, you have been asking for this! You’ve got no one to blame but yourself!”. And you may even be right. But I promise that every step along the way, I did the best I could think of, and I do believe I had valid reasons for my decisions.
Soooo, we use a little-known ERP software and a Product/Projekt Data Management System (PDM). Actually, we are currently in the last steps (we hope) of setting up the PDM system so we can eventually introduce it to our users.
In order to be useful, the PDM system needs to have kind-of-realtime information on what goes on in the ERP system, e.g. when a project is created or various kinds of documents are created in the ERP system (orders, invoices, quotations, etc.).
The problem I face has to do with telling the PDM system about new projects from the ERP system. Both the ERP and the PDM system use MS SQL Server databases as their backends (each uses its own database, obviously). To make things more interesting, we also created a transfer database that serves as an intermediate step the data takes on its way from the ERP to the PDM database (but it only has a single table).
My first thought was to just create a trigger on the table in the ERP database where projects are stored to insert a corresponding record in the transfer database. But my research led me to think that this is generally considered a bad idea, because then any hickup in the process of transferring data would cause the initial transation that created the project to fail. Needless to say, our users would not like that very much.
I came across a suggestion, though, to use the Service Broker that comes with SQL Server and use a message queue to decouple these steps. I liked this idea, because I wanted to avoid having to write, set up and maintain yet another program to transfer the data. If I could keep everything in the database instance, I thought, things will be easier to understand. I might have been wrong about that, but I am not entirely sure what I could have done better.
So with the current setup, the data flow looks like this: – User creates a new project – A trigger on the project table in the ERP database extracts some metadata about the project, creates an XML message and writes that to the message queue. – This triggers an activation procedure to read the message from the queue, extract the metadata and insert a record into the transfer database. – A trigger on the transfer database/table reads the new record, does a little data mangling and inserts a new record into the PDM database table that stores projects on the PDM side (which in turn causes triggers to run, that cause triggers to run). This trigger was originally written by a consultant from the company that sold us the PDM software, but since that consultant is far more expensive than me, maintenance of that thing has landed in my lap. (And just to be clear, I do kind of like it. generally. It is interesting work, and I get to help to bridge the gap people sometimes see between the IT side and the business side.)
We have recreated the complete setup of ERP database, Transfer database, and PDM database on a testing system. And this is where things get really strange.
If we create a new project in the ERP testing instance, it does not arrive.
I see the XML message arrive in the message queue, and it looks pretty much the way I expect it to. But from there, things go sideways. In the ERROR log file of the SQL Server instance, many message appear that tell me: “The activated proc [dbo].[PDM_Transfer] running on queue ‘MyCompany.dbo.project_transfer_queue’ output the following: Conversion failed when converting the varchar value ‘FEHLER ‘ to data type int.” In case it matters: FEHLER is German for ERROR. We are located in Germany, so that is not surprising as such – the rest of the error message is in English, though. I find this message confusing, because the only place where my sproc attempts to convert text to an integer is when extracting fields from the XML message that have been filled with an integer value from the ERP database. So as far as I understand, it should be pretty much impossible for anything but a valid integer literal to appear there. And when I inspect the XML messages, like I said, they look the way I expect them to.
If at some point in the whole chain of triggers and sprocs, some piece of code actually tried to convert the string “FEHLER ” to an int, it is no surprise that fails. But I am at a loss where this could happen and why.
To make things more confusing, I deactivated the sproc on the message queue (in the sense that it was no longer called automatically. Then I called the sproc manually from the SSMS debugger. And it worked exactly the way it should.
At that point I began to suspect it might be a permission problem. Normally the activation procedure on the message queue runs under a dedicated user, when I call the sproc manually, it runs under my user account (obviously), which has (almost) unrestricted permissions on the test database.
So I checked the account under which the activation procedure is executed on the message queue, compared its permissions with those in the production system. I discovered a few discrepancies, but after I fixed those, the problem remained the same.
Just to be extra sure, I removed all the service broker objects and recreated them.
By now, I am at the end of my rope. Can anyone give me a hint where I can look for the root cause of my problem?
Thank you very much, Benjamin
✓ Extra quality
ExtraProxies brings the best proxy quality for you with our private and reliable proxies
✓ Extra anonymity
Top level of anonymity and 100% safe proxies – this is what you get with every proxy package
✓ Extra speed
1,ooo mb/s proxy servers speed – we are way better than others – just enjoy our proxies!
USA proxy location
We offer premium quality USA private proxies – the most essential proxies you can ever want from USA
Our proxies have TOP level of anonymity + Elite quality, so you are always safe and secure with your proxies
Use your proxies as much as you want – we have no limits for data transfer and bandwidth, unlimited usage!
Superb fast proxy servers with 1,000 mb/s speed – sit back and enjoy your lightning fast private proxies!
99,9% servers uptime
Alive and working proxies all the time – we are taking care of our servers so you can use them without any problems
No usage restrictions
You have freedom to use your proxies with every software, browser or website you want without restrictions
Perfect for SEO
We are 100% friendly with all SEO tasks as well as internet marketing – feel the power with our proxies
Buy more proxies and get better price – we offer various proxy packages with great deals and discounts
We are working 24/7 to bring the best proxy experience for you – we are glad to help and assist you!