October 1, 2009 at 1:02 pm
Can someone please advise on the following:
DECLARE @Database varchar(250)
SET @Database = 'MBX_AE'
use @Database;
GO
DECLARE @DependentObject varchar(250)
SET @DependentObject = 'usp_stars_expand_sup_payout_matrix'
DECLARE @Database varchar(250)
SET @Database = 'MBX_AE'
DECLARE @ThreePartIdentifier varchar(250)
SET @ThreePartIdentifier = @Database + '.dbo.' + @DependentObject
use @Database;
GO
SELECT b.xtype,
b.name,
a.definition
FROM MBX_AE.sys.sql_modules a
JOIN MBX_AE.sys.sysobjects b
ON a.[object_id]
= b.id
AND b.name = @DependentObject
exec sp_depends @ThreePartIdentifier
Error Messages
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near '@Database'.
Msg 137, Level 15, State 2, Line 9
Must declare the scalar variable "@DependentObject".
Msg 137, Level 15, State 2, Line 12
Must declare the scalar variable "@ThreePartIdentifier".
October 2, 2009 at 1:30 am
I think batch seperator is what is causing the problem and the USE statement, try this,
DECLARE @ChangeDatabase nvarchar(250)
Declare @database nvarchar(100)
SET @database = 'SQLSERVERCENTRAL'
SET @ChangeDatabase = 'USE ' + @database
exec sp_executesql @ChangeDatabase;
DECLARE @DependentObject nvarchar(250)
SET @DependentObject = 'sp_addalias'
DECLARE @ThreePartIdentifier nvarchar(250)
SET @ThreePartIdentifier = @Database + '.sys.' + @DependentObject
SELECT b.xtype,
b.name,
a.definition
FROM SQLSERVERCENTRAL.sys.sql_modules a
JOIN SQLSERVERCENTRAL.sys.sysobjects b
ON a.[object_id]
= b.id
AND b.name = @DependentObject
exec sp_depends @ThreePartIdentifier
---------------------------------------------------------------------------------
October 2, 2009 at 2:55 pm
what PP- said.. you can't USE a variable.. you have to build it into a string and execute the string..
annoying from time to time.. but oh well 🙂
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply