I have a mixture of Oracle 10g and SQL Server 2005 databases. All my SQL Server Bob universes did have ODBC connections, which were clunky as they had to be set up on the servers for webi access. I recently changed one of them to an OLEDB while I was playing around with a different issue. The universe verified and tested fine and I exported it and migrated it to our production environment.
Today a user was having problems with odd error messages. I checked the universe and several objects now fail to parse. One gives me an “unexpected error” error message when parsing the object and then crashes Designer completely. Interestingly it’s on a LONG data type object.
If I flip the connection back to ODBC, the universe verifies with no problems and the offending object now parses with no errors.
Does anyone have any ideas? I’m not a techie - as those of you who have been kind enough to help me in the past will know! I can just about get to grips with different connection types.
What are the other objects that are causing the error?
You may also wish to see whether you are using a BO compliant MDAC on the server, we have discussed this before.
It’s a LONG data type. I can cast it as something else. I was just curious as to why it parses as ODBC but not as OLEDB.
But when I verify the universe under OLEDB, many of the objects fail with error “object is open” whatever that means. The same objects parse individually with no errors.
I’ll remind the techies upstairs to check the servers for me. I don’t have the network rights even if I knew what I was doing.
Different drivers work differently and sometimes things supported with one do not work with the other.
This can even happen with different versions of the same middleware.
SQL Server does not have an actual long data type, anyway, I usually asscociate this type with Oracle, amongst others.
I have had to cast objects before, to get them to work, big int, being an example.
What is the code of the, other, failing objects and their type?
Do they work in a query?
Its good you are checking the MDAC, this can cause issues, both, with ODBC and OLEDB.
You’re quite right (as usual). Bob displays it as an L, but when I check the the table in Server Management Studio, it’s a text.
The objects that fail with “object was open” are character, date and number. Some have simple case statements, others have no formulae - just simple representation of the column data. They work in queries outside of Bob (and inside Bob with an ODBC connection).
You’re very patient. You’re probably muttering “silly cow” under your breath by now…
As I often say to clients who, unfortunately enough, have to work with me, “you are not paying me to be wrong” - .
My patience is infinite - sometimes, although, you should see me when working on my own technical problems .
The ones that display object open, do not run I take it? I have seen this before, but my memory escapes me, as to the cause. It could be MDAC related as I said before.
What happens, when you recreate the troublesome syntax in a new object, does that work?
When you say the code works outside BO, is that using an OLEDB connection?
Resurrecting this old chestnut and hoping Mark is still blessed with infinite patience, knowledge and time…
With a SQL Server 2005 database, if I use an OLEDB connection, the SQL generated by Bob fires US style dates (mm-dd) to the database. If I use an ODBC connection, it fires UK style dates (dd-mm).
Is there any way of telling the connection what date format to use?
Have a look at the date input parameter for SQL Server OLEDB prm file under the data access folder on your BOXI server.
I believe you have to restart the connection sever / CMS after making this change.
See? I knew you’d know where to look… yet another one for our tech guys as they won’t let me near the servers. And I’m not sure the amount of change-control required to restart the CMS is worth the effort!
But thank you. I will go and bat my eyelashes down the other end of the room!
Debbie
Edited to add: thanks for the links too. That side I managed to sort back in Bob 6.5 when I first started playing with SQL Server universes.
(BOB member since 2005-03-01)
(BOB member since 2005-03-01)
.