Hi all,
I'm running a SQL Automation Build step with on a empty database to be used as the baseline for comparison.
There are 1000+ objects to run and it failed on 38 objects which are mostly views and some stored procedure. I have some questions:
1. I assume that the build will run through each types of schema in an order (such as table, then view, then stored procedure). Is this the case? This is the repository:
![]()
![]( "Image: /hc/user_images/01JKDGMCHAMH35MTH3V0T0XFPK.png")
2. The execution for the views complains about the missing table randomly. Sometimes it says cannot find the table without schema, the other times it was about the schema included. Example (the part with [...] is my own edit to trim those off):
The error 'Invalid object name 'dbo.MyDrug'.' occurred when executing the following SQL:<br><div>CREATE VIEW [dbo].[vw_MyDrug]</div><div>AS</div><div>SELECT</div><div> [...]</div><div>FROM [dbo].[MyDrug]</div><div>WHERE Id IS NULL </div>
Or
<div>The error 'Invalid object name 'DrugPbs'.' occurred when executing the following SQL:</div><div>CREATE VIEW [dbo].[vw_FullViewPbs] AS</div><div> Select DrugId, pbs.* from DrugPbs pbs</div><div> Inner Join</div><div>[...]</div>
Or sometimes a view is relying on another view cannot be built.
Do we have an option to just ignore the error and keep the process going?
I'm aware that we can adopt Pre-scripts for this case but I'm not sure how to generate one without manually going through each object in the errors (and in case there is new object added in the future).
3. I have user-defined types in the source control folder as well but the build process can't pick them up when building stored procedure. I assume this is because the scripts in Stored Procdures folder are run BEFORE those in Types folder. This would be solved with some stubs creation in Pre-scripts as well, is it the correct way?
I think I have seen in SQL Compare when generating deployment script, it will automatically check and create the stubs for objects. It would be great if we have similar option in SAC Build to get around the errors. Or I hope there is a configuration to define the order of the folders to be run (is adding a number in front of them help and they are still recognized by source control?)
Thanks!
.
I'm running a SQL Automation Build step with on a empty database to be used as the baseline for comparison.
There are 1000+ objects to run and it failed on 38 objects which are mostly views and some stored procedure. I have some questions:
1. I assume that the build will run through each types of schema in an order (such as table, then view, then stored procedure). Is this the case? This is the repository:
2. The execution for the views complains about the missing table randomly. Sometimes it says cannot find the table without schema, the other times it was about the schema included. Example (the part with [...] is my own edit to trim those off):
Or sometimes a view is relying on another view cannot be built.
Do we have an option to just ignore the error and keep the process going?
I'm aware that we can adopt Pre-scripts for this case but I'm not sure how to generate one without manually going through each object in the errors (and in case there is new object added in the future).
3. I have user-defined types in the source control folder as well but the build process can't pick them up when building stored procedure. I assume this is because the scripts in Stored Procdures folder are run BEFORE those in Types folder. This would be solved with some stubs creation in Pre-scripts as well, is it the correct way?
I think I have seen in SQL Compare when generating deployment script, it will automatically check and create the stubs for objects. It would be great if we have similar option in SAC Build to get around the errors. Or I hope there is a configuration to define the order of the folders to be run (is adding a number in front of them help and they are still recognized by source control?)
Thanks!