Brian Laws

Hello!

I've got a TSD3025 cross-database dependency question. I know how to deal with this for a user database, but how can I deal with this for MSDB Database references have been implemented now, but I can't figure out how to do it for a system database. As far as I can tell, you can only have a reference for a source-controlled database. There's only the two options of a DB in the current solution or a dbmeta file. I can't see creating an msdb project, so this two options won't work. And I'm really getting tired of seeing those warnings.

I'm also getting the warning for an sp_executesql statement. Specifically, it's:

"Warning 2 TSD3012: The following dependencies are missing from your database project: [dbo].[sp_executesql].[@RecordCount]." The code is this (condensed):

Code Block

declare @RecordCount int

set @SQLToRun = N'select @RecordCount = [RecordCount] from openrowset(''' + @Provider + N''', ''' + @ConnectionString + N''', ''' + @SQLToRun + N''')'

set @ParmDefinition = N'@RecordCount int output';

exec dbo.sp_executesql @SQLToRun, @ParmDefinition, @RecordCount = @RecordsAffected OUTPUT

If nothing else, is there a way to get rid of these warnings

Thanks a lot!

Brian



Re: Visual Studio Team System - Database Professionals TSD3025 Cross-Database Dependency for MSDB and sp_executesql

Pavel Kaufman

Hi,

we've got the same problem, I had to create fake msdb database with all procedures to get msdb.dbmeta. If you want, I can send you this file, no prob.

Warning about sp_executesql could be solved by replacing the statement with EXEC(@sql), if possible (although your example seems to be too compicated to do that).

Regards, Pavel





Re: Visual Studio Team System - Database Professionals TSD3025 Cross-Database Dependency for MSDB and sp_executesql

wBob

Have you tried the 'Suppress Warnings' box at the bottom of the Properties-Build tab





Re: Visual Studio Team System - Database Professionals TSD3025 Cross-Database Dependency for MSDB and sp_executesql

wBob

see link for more info:

http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1020316&SiteID=1

Basically enter the warning numbers without the prefix ( eg 3025, not TSD3025 ). Comma separate multiple warnings.





Re: Visual Studio Team System - Database Professionals TSD3025 Cross-Database Dependency for MSDB and sp_executesql

Brian Laws

Thanks, wBob. I'll go ahead and do that. I'm not a big fan of ignoring warnings, since I may be missing something important in the future, but I think it's fine in this case.

And thanks, Pavel, for your help and offer! I think importing msdb into the project would be a bit much. And I'm using the sp_executesql to try and get the performance benefits of the query plan reuse. But again, thanks for the help!

Brian





Re: Visual Studio Team System - Database Professionals TSD3025 Cross-Database Dependency for MSDB and sp_executesql

Gert Drapers - MSFT

We will make dbmeta files available for MSDB to resolve these references in the next PowerTools drop.

If you need these files now, send me an email via my blog link.

http://blogs.msdn.com/gertd

-GertD





Re: Visual Studio Team System - Database Professionals TSD3025 Cross-Database Dependency for MSDB and sp_executesql

Gert Drapers - MSFT

Also the sp_executesql problem will get fixed in the next QFE release.

-GertD





Re: Visual Studio Team System - Database Professionals TSD3025 Cross-Database Dependency for MSDB and sp_executesql

Scott Herbert

Email duly sent to you, Gert, regarding msdb. What about references to tempdb I imagine they might be a different kettle of fish





Re: Visual Studio Team System - Database Professionals TSD3025 Cross-Database Dependency for MSDB and sp_executesql

Scott Herbert

Having added the mdsb90CI meta file to my references file and changing references to a variable which represents the msdb, i'm getting the following error in the proc which references msdb:

"Error 2 TSD2010: Incorrect syntax near d6. C:\Development\Solutionname\Projectname\Schema Objects\Functions\schemaname.functionname.function.sql 29 49 databasename"

The line that the error is referring to is in bold in the below code sample:

Code Block

SELECT

@RunTime = ISNULL(max($(msdb).dbo.sysjobhistory.[run_duration]), 0)

FROM

$(msdb).dbo.sysjobhistory

JOIN

$(msdb)..sysjobs

ON $(msdb)..sysjobhistory.[job_id] = $(msdb)..sysjobs.[job_id]

JOIN

$(msdb)..[sysjobsteps]

ON $(msdb)..sysjobs.[job_id] = $(msdb)..[sysjobsteps].[job_id]

WHERE

$(msdb)..sysjobhistory.[run_date] >= @FinalDate

and $(msdb)..sysjobs.[name] = 'some job name'

and $(msdb)..sysjobhistory.[step_id] = 0

and $(msdb)..sysjobhistory.[sql_severity] = 0

The error makes no sense to me, as the code quoted ("d6") in the error does not exist.





Re: Visual Studio Team System - Database Professionals TSD3025 Cross-Database Dependency for MSDB and sp_executesql

Gert Drapers - MSFT

Variables can only be used when placed between [] or "" or '' otherwise they interfere with the parser since it is illegal syntax.

Like this:

Code Block

CREATE FUNCTION [dbo].[function]

(

@FinalDate datetime

)

RETURNS INT

AS

BEGIN

DECLARE @RunTime int

SELECT @RunTime = ISNULL(max([$(MSDB)].[dbo].[sysjobhistory].[run_duration]), 0)

FROM [$(MSDB)].[dbo].[sysjobhistory]

JOIN [$(MSDB)].[dbo].[sysjobs]

ON [$(MSDB)].[dbo].[sysjobhistory].[job_id] = [$(MSDB)].[dbo].[sysjobs].[job_id]

JOIN [$(MSDB)].[dbo].[sysjobsteps]

ON [$(MSDB)].[dbo].[sysjobs].[job_id] = [$(MSDB)].[dbo].[sysjobsteps].[job_id]

WHERE [$(MSDB)].[dbo].[sysjobhistory].[run_date] >= @FinalDate

and [$(MSDB)].[dbo].[sysjobs].[name] = 'some job name'

and [$(MSDB)].[dbo].[sysjobhistory].[step_id] = 0

and [$(MSDB)].[dbo].[sysjobhistory].[sql_severity] = 0

RETURN @RunTime

END