Last week I ran into an issue when creating a stored procedure in a SAP PowerDesigner Physical Data Model. After creating the stored procedure and viewing it in the preview window I noticed something was looking odd: the create procedure statement was showing a pair of empty brackets after the procedure name.
After running the create statement in SSMS, SQL Server confirmed my suspicion by throwing a syntax error:
Next, I investigated the stored procedure body in the editor and found that apparently, PowerDesigner’s default SQL Server template for stored procedures features a pair of brackets around the stored procedure parameters.
Of course, I tried manually deleting them but this was no use because after clicking the “Apply” button the default line reappeared.
After doing lots of Googling and asking around I finally found the location where you can edit the default SQL Server object templates (not just stored procedures). Just go to the menu bar, select Database–>Edit Current DBMS..
The template we are looking for is located somewhere in the Script folder:
One caveat: you are not allowed to edit the default templates, so you will have to save a copy of the properties file to a different location. One solution I find the easiest is to embed the database settings in your database model to enable you to edit it:
After this, edit and save the properties. Notice this time around the DBMS Properties are specific to the model you are working on:
Finally, re-open the stored procedure you are working on and edit something. On pressing the Apply button the new create statement will be generated:
I hope this has been helpful and that you haven’t spent hours Googling this. Of course, feel free to share this post!
Comments are closed.