Professional Web Applications Themes

Object_id routine can't find my stored proc, which is prefixed with SYSDBA - Microsoft SQL / MS SQL Server

I have created a stored procedure 'CopyCategory' with prefix 'Sysdba' But why can't the following sql sentence not find it: if exists (select * from sysobjects where id = object_id(N'[CopyCategory]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [CopyCategory] GO It does not help to prefix the above objects with 'sysdba' \Anders...

  1. #1

    Default Object_id routine can't find my stored proc, which is prefixed with SYSDBA


    I have created a stored procedure 'CopyCategory' with prefix 'Sysdba'

    But why can't the following sql sentence not find it:

    if exists (select * from sysobjects where id = object_id(N'[CopyCategory]')
    and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [CopyCategory]
    GO

    It does not help to prefix the above objects with 'sysdba'

    \Anders


    Anders Vindal Eriksen Guest

  2. #2

    Default Re: Object_id routine can't find my stored proc, which is prefixed with SYSDBA

    put the owner name as well
    for example if the procedure is owned by sysdba then the statement would be
    like this.
    if exists (select * from sysobjects where id =
    object_id(N'[sysdba].[CopyCategory]')
    and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [CopyCategory]
    GO


    --
    -Vishal
    "Anders Vindal Eriksen" <a.vindaltechotel.dk> wrote in message
    news:#LSV8zWQDHA.3236TK2MSFTNGP10.phx.gbl...
    >
    > I have created a stored procedure 'CopyCategory' with prefix 'Sysdba'
    >
    > But why can't the following sql sentence not find it:
    >
    > if exists (select * from sysobjects where id =
    object_id(N'[CopyCategory]')
    > and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    > drop procedure [CopyCategory]
    > GO
    >
    > It does not help to prefix the above objects with 'sysdba'
    >
    > \Anders
    >
    >

    Vishal Parkar Guest

  3. #3

    Default Re: Object_id routine can't find my stored proc, which is prefixed with SYSDBA

    I can not guess what you are missing in the statement ( i guess it must be
    problem with ownername). A simple trick, right click on the database >> 'all
    tasks' >> 'generate sql scripts' and select the required stored procedure.
    generate the script and cross check it with the statement you are using.

    --
    -Vishal
    "Anders Vindal Eriksen" <a.vindaltechotel.dk> wrote in message
    news:OHAy#NXQDHA.1556TK2MSFTNGP10.phx.gbl...
    > Thanx for your help, but it doesnt work as I told in the first mail.....
    >
    > \anders
    >
    >
    > "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    > news:e94un4WQDHA.1552TK2MSFTNGP10.phx.gbl...
    > > put the owner name as well
    > > for example if the procedure is owned by sysdba then the statement would
    > be
    > > like this.
    > > if exists (select * from sysobjects where id =
    > > object_id(N'[sysdba].[CopyCategory]')
    > > and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    > > drop procedure [CopyCategory]
    > > GO
    > >
    > >
    > > --
    > > -Vishal
    > > "Anders Vindal Eriksen" <a.vindaltechotel.dk> wrote in message
    > > news:#LSV8zWQDHA.3236TK2MSFTNGP10.phx.gbl...
    > > >
    > > > I have created a stored procedure 'CopyCategory' with prefix 'Sysdba'
    > > >
    > > > But why can't the following sql sentence not find it:
    > > >
    > > > if exists (select * from sysobjects where id =
    > > object_id(N'[CopyCategory]')
    > > > and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    > > > drop procedure [CopyCategory]
    > > > GO
    > > >
    > > > It does not help to prefix the above objects with 'sysdba'
    > > >
    > > > \Anders
    > > >
    > > >
    > >
    > >
    >
    >

    Vishal Parkar Guest

  4. #4

    Default Re: Object_id routine can't find my stored proc, which is prefixed with SYSDBA

    Hey Vishal

    You were right.. I have no problemo now..

    \Anders

    "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    news:OPMHySXQDHA.2312TK2MSFTNGP12.phx.gbl...
    > I can not guess what you are missing in the statement ( i guess it must be
    > problem with ownername). A simple trick, right click on the database >>
    'all
    > tasks' >> 'generate sql scripts' and select the required stored procedure.
    > generate the script and cross check it with the statement you are using.
    >
    > --
    > -Vishal
    > "Anders Vindal Eriksen" <a.vindaltechotel.dk> wrote in message
    > news:OHAy#NXQDHA.1556TK2MSFTNGP10.phx.gbl...
    > > Thanx for your help, but it doesnt work as I told in the first mail.....
    > >
    > > \anders
    > >
    > >
    > > "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    > > news:e94un4WQDHA.1552TK2MSFTNGP10.phx.gbl...
    > > > put the owner name as well
    > > > for example if the procedure is owned by sysdba then the statement
    would
    > > be
    > > > like this.
    > > > if exists (select * from sysobjects where id =
    > > > object_id(N'[sysdba].[CopyCategory]')
    > > > and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    > > > drop procedure [CopyCategory]
    > > > GO
    > > >
    > > >
    > > > --
    > > > -Vishal
    > > > "Anders Vindal Eriksen" <a.vindaltechotel.dk> wrote in message
    > > > news:#LSV8zWQDHA.3236TK2MSFTNGP10.phx.gbl...
    > > > >
    > > > > I have created a stored procedure 'CopyCategory' with prefix
    'Sysdba'
    > > > >
    > > > > But why can't the following sql sentence not find it:
    > > > >
    > > > > if exists (select * from sysobjects where id =
    > > > object_id(N'[CopyCategory]')
    > > > > and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    > > > > drop procedure [CopyCategory]
    > > > > GO
    > > > >
    > > > > It does not help to prefix the above objects with 'sysdba'
    > > > >
    > > > > \Anders
    > > > >
    > > > >
    > > >
    > > >
    > >
    > >
    >
    >

    Anders Vindal Eriksen Guest

Similar Threads

  1. SQL Stored Proc question
    By Swd1974 in forum Coldfusion Database Access
    Replies: 2
    Last Post: July 27th, 09:11 PM
  2. cfc and stored proc
    By mcoop in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: April 13th, 05:09 PM
  3. ASP vs Stored Proc vs UDF
    By Brad in forum ASP Database
    Replies: 11
    Last Post: October 28th, 01:46 AM
  4. Need Help in Stored Proc Syntax
    By Mansoor Aleem in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 9th, 02:19 PM
  5. stored proc and tcp/ip
    By Helmut Wöss in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: June 30th, 08:13 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139