Configure ODBC Macro to run without ODBC prompt!

[rickb]

When I try to run a Macro with ODBC Connect as the first step to execute a dlink from Oracle Instance to Analyst Cube, the ODBC Logon Screen appears despite the fact that the Userid and Password have been input into the macro (uid=xxx;pwd=yyy;). When I then manually input the exact Userid and Password into the now opened ODBC Logon screen the macro runs ok. The DLink was set up with the EXACT same paramaters.

Can anyone advise how to configure the macro to run without the ODBC prompt?

Many thanks.

[praveennb]

I'm faced with the exact same problem with Planning 8.3, using SQL server Native client odbc

[rturpyn]

Along with having the ODBC Connect in the macro, you also need to embed the userID and Account in the Dlink.

Open the Dlink
Click the Source button
Select 'Edit (SQL) Source/Statement'
Click 'New Source'

When the ODBC Login appears, highlight the connection you want.  Then in the lower portion of the window enter the login credentials.

Save the Dlink and try executing it in your macro.

[rickb]

Thanks guys for your response.

The solution was to use the “alternative” data source name provided by Oracle, known as “The Transparent Network Substrate (TNS)”.  This is found in a tnsnames.ora file and provides the ability to reference oracle databases by a simple alias. When the alias data source name is used in the dlink and the @odcbconnect statement the macro runs without the odbc logon prompt.

[praveennb]

Along with having the ODBC Connect in the macro, you also need to embed the userID and Account in the Dlink.

Open the Dlink
Click the Source button
Select 'Edit (SQL) Source/Statement'
Click 'New Source'

When the ODBC Login appears, highlight the connection you want.  Then in the lower portion of the window enter the login credentials.

Save the Dlink and try executing it in your macro.

That's what i tried earlier , it doesn't work for me. Thanks

[cbrandt]

Guys,

There was a post a while ago to the same topic, where I posted one solution. Couldn't find it myself, though.
Anyway, Planning can be a bit tricky with the ODBC connect if you have set it up in the wrong order. If you have set up the Link (or DList Update) and Macro "wrong" in the first place, you may still get prompted although you fixed it. Took me a while to figure that out but the following worked for me:

1. Delete the relevant step from the Macro (i.e. DList update).
2. Save the Macro
3. Open the related DList or DLink that is currently pointing to the ODBC source.
4. Point it to any other source (None, DCube, txt file, doesn't matter)
5. Save it
6. Now point it again to the ODBC source using the credentials in the option box, as in the picture above.
7. Save and close.
8. Add the ODBC connect in the Macro (if not already in there)
9. Add the relevant DList or DLink step in the Macro.
10. Don't forget an ODBC close if applicable.

That should work.
Let me know, how you go.
Cheers
Carsten

[ajarpit]

i am having the same problem with sql server 2000 as database.

please how to avoid this odbc logon manually,coz im taking data from sql server 2000 to contributor through a batch file cantaining macros i.e. everything is automated except this odbc logon.

what are the settings for @odbcconnect?

[esso6919]

Along with having the ODBC Connect in the macro, you also need to embed the userID and Account in the Dlink.

Open the Dlink
Click the Source button
Select 'Edit (SQL) Source/Statement'
Click 'New Source'

When the ODBC Login appears, highlight the connection you want.  Then in the lower portion of the window enter the login credentials.

Save the Dlink and try executing it in your macro.

This help me a lot !, like martians in Toy Story said "You save us , and I we are grateful"....

However is necessary to follow also the advice of write the odbconnect command and the close odbc at the end of the macro

[mr j]

I borrow this thread and expand the subject a bit,

There's a job in Cognos Connection containing Contributor macros, data movement tasks and Contributor macros which run Analyst macros as the first step. This is scheduled to run by a system user with all the possible rights. I can run the job manually with only Planning Admin rights plus admin rights to servers. However a user with less rights but with his user group Planning Contributor Users and another custom group assigned in CAC to execute macros and with admin rights to servers fails to run the steps where Contributor macros launch Analyst macros. These Analyst macros open ODBC to SQL database, the user id and pw for the database are there in the macro.

The error in Cognos Connection is "Failed to receive response over socket", in planningerrorlog "Unable to execute macro 'MacroNamet'. The return code was 1107."

Support sites suggest it might be UNC problem, but it must be something with the rights since other users are ok.

Any ideas what to check? I hope I gave enough details..

[Danno]

I borrow this thread and expand the subject a bit,

There's a job in Cognos Connection containing Contributor macros, data movement tasks and Contributor macros which run Analyst macros as the first step. This is scheduled to run by a system user with all the possible rights. I can run the job manually with only Planning Admin rights plus admin rights to servers. However a user with less rights but with his user group Planning Contributor Users and another custom group assigned in CAC to execute macros and with admin rights to servers fails to run the steps where Contributor macros launch Analyst macros. These Analyst macros open ODBC to SQL database, the user id and pw for the database are there in the macro.

The error in Cognos Connection is "Failed to receive response over socket", in planningerrorlog "Unable to execute macro 'MacroNamet'. The return code was 1107."

Support sites suggest it might be UNC problem, but it must be something with the rights since other users are ok.

Any ideas what to check? I hope I gave enough details..

Did you solve this? We use Oracle but quite a while ago we had a similar error and I resolved it using two steps: edited permissions on the UNC mappings so that the service user also had permissions; started using ODBC close at the beginning of a macro to ensure that any ODBC connections were closed prior to opening a connection.

If you solved it I would be interested to know what steps you took.

Danno

[mr j]

Nope, this is not solved yet. Thanks for the tip, Danno, I did what you suggested and added ODBC close in the beginning but it didn't help.

It must be something with the rights I just haven't noticed.

[Danno]

I just reviewed everything we did to make this happen and our method was to create an AD group called Cognos Developers, which included all developers and any service users for Cognos, and give them Full Control with the ability to Take Ownership for that path and we made the permissions inheritable to all subfolders and objects below the path. This was done at the SAN level. We also made each Developer, and service user (per IBM), a local admin on the server as well.

Hopefully this helps you think of something that solves the issue.

[mr j]

A veeery late thank you, Danno, for your input. I'll take a note of this. There has not been time to do anything about it. Currently the only nuisance out of this is an error report in the email every time the job is run by the user. I could actually just modify the job to leave out the steps which fail as they're not that critical. You know, don't fix it today if you can leave it unfixed tomorrow  ;)

However, I'm sure that something similar will appear in the future..

[Danno]

A veeery late thank you, Danno, for your input. I'll take a note of this. There has not been time to do anything about it. Currently the only nuisance out of this is an error report in the email every time the job is run by the user. I could actually just modify the job to leave out the steps which fail as they're not that critical. You know, don't fix it today if you can leave it unfixed tomorrow  ;)

Too funny! Let us know if it gets resolved. I'm always making note of the solutions posted here.