All Forums Tools
toadrw 69 posts Joined 07/04
20 Mar 2015
TPT Stream Loader - Create Macro Access error on target database (not working database).

Hello:
When trying to use TPT to stream data from SQL Server we're running into the following error.
'The user does not have CREATE MACRO access to database TargetDatabase'.
The TargetDatabase in this case is different from the WorkingDatabase below.  My understanding is that the default macro database is the restart table database which is the working database.  If the working database is defined then the logs and error tables should be qualified.
So does the target database need create macro access for the user when the working database has been defined?  We can set the MacroDatabase attribute, but I didn't think it was necessary.
Thank you for your help.  This is TPT 15.  The database version is 14.10. Below is the stream operator we are using.

DEFINE OPERATOR StreamOperator

TYPE Stream

SCHEMA *

ATTRIBUTES (

VARCHAR Tdpid = 'MyDSN'

,VARCHAR UserName = 'user'

,VARCHAR UserPassword = 'password'

,VARCHAR LogonMech = 'ldap'

,VARCHAR WorkingDatabase = 'MyWorkingDatabase'

,VARCHAR ErrorTable = 'MyWorkingDatabase.TargetTable_ET'

,VARCHAR LogTable = 'MyWorkingDatabase.TargetTable_tlog'

,VARCHAR DateForm = 'integerDate'

);

 

 

 

 
 

feinholz 1234 posts Joined 05/08
24 Mar 2015

You might want to look at the Stream operator information in the TPT reference manual for the use of the MacroDatabase attribute.

--SteveF

toadrw 69 posts Joined 07/04
13 Apr 2015

Hello Steve:
Thank you for the reply!
The documentation says "the default macro database is the restart log table database" which I believe is the working database correct?  
We had the working database set, however the CREATE MACRO access error occured on another database.  If I can remember correctly this database was the database which held the table we were loading into.
That being said, we were able to get around the issue by adding the MacroDatabase attribute and setting it to the same database as the working database. This worked for what we were trying to do.
Thanks again!

feinholz 1234 posts Joined 05/08
13 Apr 2015

The restart log table database is not the working database (thus the confusion).
 
The behavior comes from the old TPump utility, whose restart log table name was specified prior to the .logon command. And it was handled before any SQL statements (including the DATABASE command) were executed.
 
When developing the Stream operator, we had to keep the same functionality as TPump so that customers moving from TPump to Stream would not encounter different behavior.
 

--SteveF

toadrw 69 posts Joined 07/04
16 Apr 2015

So I guess the restart log table's database will be the target database.  Is this correct?  I think it is making more sense now (unless my previous statement is incorrect).  Thank you!

feinholz 1234 posts Joined 05/08
16 Apr 2015

The restart log table database (unless the restart log table name is fully qualified) will be the logon database.
 

--SteveF

bhartiya007 29 posts Joined 03/14
22 Apr 2015

Hi All,
I have one question here. Appreciate any reply:
1)TPT does not create any control/loader log for detailed error description when used with informatica. What are the options for troubleshooting any errors?
2) How can we specify more than one operator for a particular connection in informatica.
 
Regards,
Amit Bhartiya
 

@Amit

You must sign in to leave a comment.