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.
You might want to look at the Stream operator information in the TPT reference manual for the use of the MacroDatabase attribute.
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.
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.
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!
The restart log table database (unless the restart log table name is fully qualified) will be the logon database.
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.