SQL Build failed in Views/SP due to missing tables and types

ntkhanh_tma

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:


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&nbsp;</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!

Kurt_M

.

Kurt_M

Hi all,

This issue can be resolved via checking the log files of SCA (Click here for a link to the documentation page about logs) and finding the culprits that are causing the build process to break.

In the above example, there were a couple of instances that the process had failed on. One was a credential issue with the username and password inside of the SCA build. 

The second issue was related to a stored procedure that had some invalid columns in it. 

These issues will usually be outlined within the log files, so it is suggested to consult these to find what may be the cause of the problem. 

After these issues were resolved, the build process went through.