|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Executing Stored Procedure within TriggerHi,
The below is adapted from a trigger. When it's tested in Query Analyzer, it flags as the below error. However when I tried executing dbo.usp_Checksum 'testing', it works ! Any idea what's wrong ? Advice please. TIA ! Declare @Checksum varchar(2); declare @String varchar(160); set @String = 'testing' set @Checksum = dbo.usp_Checksum (@String); Server: Msg 208, Level 16, State 1, Line 4 Invalid object name 'dbo.usp_Checksum'. Were you in the correct db context?
-- Show quoteHide quoteAndrew J. Kelly SQL MVP "Desmond" <Desm***@discussions.microsoft.com> wrote in message news:381994F0-1B78-42E2-BD75-2F443BF7DA66@microsoft.com... > Hi, > > The below is adapted from a trigger. When it's tested in Query Analyzer, > it > flags as the below error. However when I tried executing dbo.usp_Checksum > 'testing', it works ! Any idea what's wrong ? > > Advice please. TIA ! > > Declare @Checksum varchar(2); > declare @String varchar(160); > set @String = 'testing' > set @Checksum = dbo.usp_Checksum (@String); > > Server: Msg 208, Level 16, State 1, Line 4 > Invalid object name 'dbo.usp_Checksum'. Do you mean that I'm using the wrong DB ?
Well, the DB is correct. Show quoteHide quote "Andrew J. Kelly" wrote: > Were you in the correct db context? > > > -- > Andrew J. Kelly SQL MVP > > > "Desmond" <Desm***@discussions.microsoft.com> wrote in message > news:381994F0-1B78-42E2-BD75-2F443BF7DA66@microsoft.com... > > Hi, > > > > The below is adapted from a trigger. When it's tested in Query Analyzer, > > it > > flags as the below error. However when I tried executing dbo.usp_Checksum > > 'testing', it works ! Any idea what's wrong ? > > > > Advice please. TIA ! > > > > Declare @Checksum varchar(2); > > declare @String varchar(160); > > set @String = 'testing' > > set @Checksum = dbo.usp_Checksum (@String); > > > > Server: Msg 208, Level 16, State 1, Line 4 > > Invalid object name 'dbo.usp_Checksum'. > > > Hi Desmond
Is usp_checksum a procedure or function? Your prefix makes it sound like a procedure, and dbo.usp_Checksum 'testing' is how you would call a procedure, but dbo.usp_Checksum (@String) looks like you are calling a function. So which is it? You cannot assign the 'result' of a procedure to a variable, as it doesn't have a 'result' value, it may have a result set though. Show quoteHide quote "Desmond" <Desm***@discussions.microsoft.com> wrote in message news:02B8E8B4-4B83-4B96-A7C4-E67B41397303@microsoft.com... > Do you mean that I'm using the wrong DB ? > > Well, the DB is correct. > > "Andrew J. Kelly" wrote: > >> Were you in the correct db context? >> >> >> -- >> Andrew J. Kelly SQL MVP >> >> >> "Desmond" <Desm***@discussions.microsoft.com> wrote in message >> news:381994F0-1B78-42E2-BD75-2F443BF7DA66@microsoft.com... >> > Hi, >> > >> > The below is adapted from a trigger. When it's tested in Query >> > Analyzer, >> > it >> > flags as the below error. However when I tried executing >> > dbo.usp_Checksum >> > 'testing', it works ! Any idea what's wrong ? >> > >> > Advice please. TIA ! >> > >> > Declare @Checksum varchar(2); >> > declare @String varchar(160); >> > set @String = 'testing' >> > set @Checksum = dbo.usp_Checksum (@String); >> > >> > Server: Msg 208, Level 16, State 1, Line 4 >> > Invalid object name 'dbo.usp_Checksum'. >> >> >> > Good catch Kalen. I thought from the syntax it was a function but his
subject states sp. -- Show quoteHide quoteAndrew J. Kelly SQL MVP "Kalen Delaney" <replies@public_newsgroups.com> wrote in message news:u7dogDkLGHA.3860@TK2MSFTNGP12.phx.gbl... > > Hi Desmond > > Is usp_checksum a procedure or function? Your prefix makes it sound like a > procedure, and dbo.usp_Checksum 'testing' is how you would call a > procedure, but dbo.usp_Checksum (@String) looks like you are calling a > function. So which is it? You cannot assign the 'result' of a procedure to > a variable, as it doesn't have a 'result' value, it may have a result set > though. > > -- > HTH > Kalen Delaney, SQL Server MVP > www.solidqualitylearning.com > > > "Desmond" <Desm***@discussions.microsoft.com> wrote in message > news:02B8E8B4-4B83-4B96-A7C4-E67B41397303@microsoft.com... >> Do you mean that I'm using the wrong DB ? >> >> Well, the DB is correct. >> >> "Andrew J. Kelly" wrote: >> >>> Were you in the correct db context? >>> >>> >>> -- >>> Andrew J. Kelly SQL MVP >>> >>> >>> "Desmond" <Desm***@discussions.microsoft.com> wrote in message >>> news:381994F0-1B78-42E2-BD75-2F443BF7DA66@microsoft.com... >>> > Hi, >>> > >>> > The below is adapted from a trigger. When it's tested in Query >>> > Analyzer, >>> > it >>> > flags as the below error. However when I tried executing >>> > dbo.usp_Checksum >>> > 'testing', it works ! Any idea what's wrong ? >>> > >>> > Advice please. TIA ! >>> > >>> > Declare @Checksum varchar(2); >>> > declare @String varchar(160); >>> > set @String = 'testing' >>> > set @Checksum = dbo.usp_Checksum (@String); >>> > >>> > Server: Msg 208, Level 16, State 1, Line 4 >>> > Invalid object name 'dbo.usp_Checksum'. >>> >>> >>> >> > > > Hi Kalen,
Thanks for spotting my mistake. It should be a function rather than a stored procedure. Show quoteHide quote "Kalen Delaney" wrote: > > Hi Desmond > > Is usp_checksum a procedure or function? Your prefix makes it sound like a > procedure, and dbo.usp_Checksum 'testing' is how you would call a > procedure, but dbo.usp_Checksum (@String) looks like you are calling a > function. So which is it? You cannot assign the 'result' of a procedure to a > variable, as it doesn't have a 'result' value, it may have a result set > though. > > -- > HTH > Kalen Delaney, SQL Server MVP > www.solidqualitylearning.com > > > "Desmond" <Desm***@discussions.microsoft.com> wrote in message > news:02B8E8B4-4B83-4B96-A7C4-E67B41397303@microsoft.com... > > Do you mean that I'm using the wrong DB ? > > > > Well, the DB is correct. > > > > "Andrew J. Kelly" wrote: > > > >> Were you in the correct db context? > >> > >> > >> -- > >> Andrew J. Kelly SQL MVP > >> > >> > >> "Desmond" <Desm***@discussions.microsoft.com> wrote in message > >> news:381994F0-1B78-42E2-BD75-2F443BF7DA66@microsoft.com... > >> > Hi, > >> > > >> > The below is adapted from a trigger. When it's tested in Query > >> > Analyzer, > >> > it > >> > flags as the below error. However when I tried executing > >> > dbo.usp_Checksum > >> > 'testing', it works ! Any idea what's wrong ? > >> > > >> > Advice please. TIA ! > >> > > >> > Declare @Checksum varchar(2); > >> > declare @String varchar(160); > >> > set @String = 'testing' > >> > set @Checksum = dbo.usp_Checksum (@String); > >> > > >> > Server: Msg 208, Level 16, State 1, Line 4 > >> > Invalid object name 'dbo.usp_Checksum'. > >> > >> > >> > > > > > >  
Other interesting topics
|
|||||||||||||||||||||||
