Connection Shortcut failing BO4.1 (IDT)

[abdussamad_peera]

I have BO 4.1 SP3 and using IDT to create an ODBC connection to MS SQLServer using MSSQLServer 2012 ODBCdriver.

I can create the connection and it is also successful when testing it but when I publish it to the repository

and double-click it to open it in IDT it fails with the following message when I click on the TEST button.

And when I click on the EDIT button it comes up with a window with a TEST Connection button and when I click

this TEST button it is successful!!  Why does it fail from the first TEST button?

Also when you publish the conn to the repository it prompts you to create a shortcut and I click Yes but when I test

this shortcut it also fails.

This is the error message - [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

To be sure Data Source name is there both on my machine and on the BO server and the connection works both on my machine

and after publishing it to the server except for the instances mentioned above.

I have tried changing the connection of the DF to the newly created shortcut I get the same error as mentioned above however when

I use the connection itself (not the shortcut) in the DF it connects.  But using the conn and not the shortcut isn't the right practice.

I am not sure if the DSN was created using use c:\windows\sysWOW64\odbcad32.exe or \system32\odbcad32.exe

but I am assuming DSN is not the issue since it does work in the situation mentioned above.

Any guidance from the experts will be appreciated.

Thanks

Abdul

[former_member185603 · Accepted solution]

Did you create ODBC connection in client PC also?

[abdussamad_peera · Accepted solution]

Yes, Jawahar, its there on my machine (client) and the BO server - as I had mentioned that

the connection gets created on IDT and works but its the shortcut that fails.

[former_member185603 · Accepted solution]

Whne you connect from the server (Connection shows in the repository location), it uses Server DSN. When you use the shortcut , it uses your local connection. make sure you have both the DSN created 32 bit and 64 bit.

To manage a data source that connects to a 32-bit driver under 64-bit

platform, use c:\windows\sysWOW64\odbcad32.exe. To manage a data source

that connects to a 64-bit driver, use c:\windows\system32\odbcad32.exe.

[abdussamad_peera · Accepted solution]

According your statement "When you use the shortcut , it uses your local connection",

my shortcut shouldn't fail as the original Conn on my machine using IDT works fine.

and I know my machine only has c:\windows\system32\odbcad32.exe (and not c:\windows\sysWOW64\odbcad32.exe), Can I safely assume my data source connects to 64-bit driver?

And if this is the case then I should use the same driver on the BO server, correct?

[former_member191664 · Accepted solution]

I have seen this kind of connection error before on BI4.1 SP03.  The relational connection is created in universe designer and published to CMS repository. Then, open the same relational connection in IDT, and TEST fails while TEST in edit works.  My workaround is simply recreate this relation connection in IDT, and it now works for both universe designer and IDT.  I think I posted this question before as to find out if anyone knows of this kind of problem? Hope this helps, Jin-Chong

[abdussamad_peera · Accepted solution]

Jin,

You suggested using IDT but this is what I am using to create connection from the start.

I have not used univ designer.

[abdussamad_peera · Accepted solution]

Jawahar/Jin,

In this case after I restarted a stale ConnectionServer connection shortcut started working!

Appreciate your help, guys.

Abdul