4

I have a Visual Studio 2010 database project and I have imported an existing schema. There are stored procedures in this database that refer to the Integration Services system table dbo.sysssislog and is generating a number of warnings in my project.

I have tried adding the master.dbschema and the msdb.dbschema as database references and I also tried renaming the references' database names to tempdb (instead of master or instead of msdb) but the problem persists.

I opened the msdb.dbschema file and I have confirmed that the sysssislog table exists in the file.

Here is the warning:

SQL04151: Procedure: [dbo].[storedProcedureName] has an unresolved reference to object [dbo].[sysssislog].
Nick H
  • 433
  • 1
  • 3
  • 10

2 Answers2

4

The dbo.sysssislog is a user table (marked as a system table) created by SQL Server Integration Services (SSIS) automatically, when you create a package with event logging of SQL Server type. Besides this table, SSIS also creates some stored procedures (which can be ALTERed) for aiding the logging process.

It's an unresolved reference in your project, probably because you imported the database schema, which resulted in importing the stored procedures mentioned, but not importing the dbo.sysssislog table, since it's marked as a system table.

So now, what you have is a bunch of stored procedures referencing a table which you haven't imported, resulting in the warning.

What you can do to get rid of the warning(s), is to DROP and reCREATE the table manually (which is the only way to "remove" the system table mark), and importing it to your project.

gonsalu
  • 3,154
  • 2
  • 18
  • 19
  • Creating it as a user table won't make it behave any differently will it? I just want to make sure the solution will work going forward and that I don't have to maintain it as a system table on the servers but a user table in Source Control. – Nick H Apr 19 '12 at 13:07
  • No, it won't. I usually alter the table to add new columns for custom logging, and haven't had any problems doing it. – gonsalu Apr 19 '12 at 14:39
  • Nice, thanks, especially the hint about dropping/recreating was helpful. – cdonner May 07 '13 at 18:43
3

Here is an alternative and cleaner approach:

Create a new empty SSDT project, call it "sysssislog" and add a script for sysssislog table to it. Build the project to generate the dacpac file "sysssislog.dacpac".

In your SSDT project add a databasereference to the dacpac file and select "Same Database" for the database location in the "Add Database Reference" dialog.

If your project is under source control, you can add the dacpac to the project, and then add the reference, so the dacpac file is under source control too.

shapkin
  • 99
  • 5
  • I did this, which appeared to work, but (very) strangely the publish attempted to create the table (and therefore the script failed). I believe this functionality (Using same database) allows you to lock down sections of database, or to include shared source code\tables between databases (auditing or logging for example) – Trubs Dec 05 '17 at 04:21
  • Following up on the comment from Trubs, shapkin's method works to get rid of the warnings but does not allow for publishing because the publishing tool cannot detect the table when it's marked with the system attribute (presumably is_ms_shipped). The only way I've specifically found to get around the warning is to add 71502 to the "Suppress TSql Warnings" property of the specific file producing it. – efesar Feb 22 '23 at 19:20