dropping functions

Bonjour,

I'm as green as can be...and I created a function but could not drop it when
things went south....

create or replace function available(client_id, property_id)
return number
is
ret number := 0;
begin
begin
select nvl2(c.client_id,1,0)
into ret
from client c, property p
where p.property_id = property
and p.asking_price <= c.max_rent(+)
and c.client_id (+) = client;
exception
when no_data_found then
ret := 0;
end;
return ret;
end;
/

:eek:Did you try:
DROP FUNCTION AVAILABLE;
:rolleyes:

DROP USER

Hi,

I’m having problems dropping a user this is my code:

Public Function DropUser() As Boolean

Dim conn As New ServerConnection("STATION01\SQLEXPRESS", wContainer.Username, wContainer.Password)

Dim myServer As New Server(conn)

Dim myDatabase As Database = myServer.Databases("VideoDB")

If myServer.Logins.Contains(“username”) Then

Dim db_user As New User(myDatabase, “username”)

db_user.Login = “username”

db_user.Drop()

Dim db_login As New Login(myServer, “username”)

db_login.Drop()

Return True

Else

Return False

End If

End Function

OK, whats the error message ? For the case that the user has a schema assigned you will first have to drop the schema or put in another owner for the schema.

HTH, Jens K. Suessmeyer.

http:://www.sqlserver2005.de|||

If you want to ensure you've got the user/login out of all databases try this code (after you instantiate myServer):

Dim dbColl As DatabaseCollection
Dim dbCurrent As Database
Dim schColl As SchemaCollection
Dim schClean As Schema
Dim usrColl As UserCollection
Dim usrClean As User
Dim logColl As LoginCollection
Dim logClean As New Login

dbColl = myServer.Databases
For Each dbCurrent In dbColl
If Not dbCurrent.IsDatabaseSnapshot Then
schColl = dbCurrent.Schemas
schClean = schColl.Item("username")
If Not (schClean Is Nothing) Then
schClean.Drop()
End If
usrColl = dbCurrent.Users
usrClean = usrColl.Item("username")
If Not (usrClean Is Nothing) Then
usrClean.Drop()
End If
End If
Next
logColl = srvMgmtServer.Logins
logClean = logColl.Item("username")
If Not (logClean Is Nothing) Then
logClean.Drop()
End If

This will remove all the schema and user from every database used by this login, then drop the login.

|||

Hi,

The error is:

Drop failed for User ‘username’

|||Thanks a lot....

Drop function

Hey
I got this noob problem, I cannot drop a function.
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'NONUMBER')
DROP FUNCTION NONUMBER
This works fine on one of my other servers, first thing I though is
security. Since this code works on another server.
My permissions are the same, dbadmin , it's sa role. Both servers were
installed the same way so I can't see how I'm having this problem. So it's
not security my I think it can be those DB options where you specify with
sp_configure.
Ok, few minutes past I tried the sp_configure.
Still can't drop function. I'm sure it's just small setting.
Thanks in advance.
Mal> Hey
> I got this noob problem, I cannot drop a function.
> IF EXISTS (SELECT *
> FROM sysobjects
> WHERE name = N'NONUMBER')
> DROP FUNCTION NONUMBER
> This works fine on one of my other servers, first thing I though is
> security. Since this code works on another server.
> My permissions are the same, dbadmin , it's sa role. Both servers were
> installed the same way so I can't see how I'm having this problem. So
> it's not security my I think it can be those DB options where you
> specify with sp_configure.
> Ok, few minutes past I tried the sp_configure.
> Still can't drop function. I'm sure it's just small setting.
>
Do you get an error? If so, what error?
Or does nothing happen? Modify your script to this:
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'NONUMBER')
BEGIN
print 'Dropping function'
DROP FUNCTION NONUMBER
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'NONUMBER')
'Could not drop fucntion'
ELSE
'Function has been dropped'
END
ELSE
print 'Function Does Not Exist'
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Mal,
What is the error you are getting?

> IF EXISTS (SELECT *
> FROM sysobjects
> WHERE name = N'NONUMBER')
> DROP FUNCTION NONUMBER
The previous statement is checking for the existence of an object named
N'NONUMBER', but it is not checking that this object is a user defined
function. Check the diff with the following statement.
if exists (select * from information_schema.routines where routine_name =
N'nonumber' and routine_type = 'function)
...
AMB
"Mal" wrote:

> Hey
> I got this noob problem, I cannot drop a function.
> IF EXISTS (SELECT *
> FROM sysobjects
> WHERE name = N'NONUMBER')
> DROP FUNCTION NONUMBER
> This works fine on one of my other servers, first thing I though is
> security. Since this code works on another server.
> My permissions are the same, dbadmin , it's sa role. Both servers were
> installed the same way so I can't see how I'm having this problem. So it's
> not security my I think it can be those DB options where you specify with
> sp_configure.
> Ok, few minutes past I tried the sp_configure.
> Still can't drop function. I'm sure it's just small setting.
> Thanks in advance.
> Mal|||Sorry for not pasting ddl
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = 'NONUMBER')
begin
DROP FUNCTION NONUMBER
end
Code again, Error to follow
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'FUNCTION'.
Also tried grant access to my user to create function
UserName GroupName LoginName DefDBName UserID SUserID
-- -- -- -- -- --
dbo db_owner sa master 1 1
Grant create function to dbo
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'function'.
Same problem.
This is error desc. from BOL
170 15 Line %d: Incorrect syntax near '%.*ls'.
Thanks in advance .
"Alejandro Mesa" wrote:
> Mal,
> What is the error you are getting?
>
> The previous statement is checking for the existence of an object named
> N'NONUMBER', but it is not checking that this object is a user defined
> function. Check the diff with the following statement.
> if exists (select * from information_schema.routines where routine_name =
> N'nonumber' and routine_type = 'function)
> ...
>
> AMB
> "Mal" wrote:
>|||For starters, I would suggest two things. First, check the compatibility of
the database, using sp_dbcmptlevel; Only databases with level 80 or above
can do any function related DDLs. Second, check the owner of the fuction to
see if the DROP is executed by a user/ role having same or higher
previleges.
Anith|||Hey thanks for everyone's help
I did post a thanks but I used curseword in it so it got blocked I think.
Here what the results are.
sp_dbcmptlevel ->
Valid values of database compatibility level are 60, 65, or 70.
Thanks for help I'll have a look at compatibility in BOL and online .
Thanks
"Anith Sen" wrote:

> For starters, I would suggest two things. First, check the compatibility o
f
> the database, using sp_dbcmptlevel; Only databases with level 80 or above
> can do any function related DDLs. Second, check the owner of the fuction t
o
> see if the DROP is executed by a user/ role having same or higher
> previleges.
> --
> Anith
>
>

drilldown in excel and PDF

I have a report that has the drilldown function in it with the +/-
sign. When I export the report to excel or PDF, the drilldown
functionality is lost and I am not able to view the info at the lower
level's.
If I expand all the drilldown's and export it, then all the info shows
but the +/- still doesn't show up in excel or PDF.
I am currently running RS SP2.
does anyone know the reason why it's doing this?That is the way those types are rendered. They do not have the drill down
capability.
"bevarg" <benovarghese@.gmail.com> wrote in message
news:1162502355.922494.29120@.f16g2000cwb.googlegroups.com...
>I have a report that has the drilldown function in it with the +/-
> sign. When I export the report to excel or PDF, the drilldown
> functionality is lost and I am not able to view the info at the lower
> level's.
> If I expand all the drilldown's and export it, then all the info shows
> but the +/- still doesn't show up in excel or PDF.
> I am currently running RS SP2.
> does anyone know the reason why it's doing this?
>|||Something has to be wrong...
For starters:
PDF files will ONLY show the report exactly how it looks - i.e. if some
components are collapsed, and others expanded, then that is EXACTLY what you
get when you export to PDF.
EXCEL on the other hand WILL show drill-downs and carry that
functionality into the excel workbook itself. I.e. You can after exporting
into Excel drill-down into your data or collapsed as if you were on the
report server web page viewing this report.
One of the issues with Excel drill downs is when people use a SubReport.
Subreports do not render into Excel at all. (Pisses me off of course) :) Are
you using a subreport?
=-Chris
"bevarg" <benovarghese@.gmail.com> wrote in message
news:1162502355.922494.29120@.f16g2000cwb.googlegroups.com...
>I have a report that has the drilldown function in it with the +/-
> sign. When I export the report to excel or PDF, the drilldown
> functionality is lost and I am not able to view the info at the lower
> level's.
> If I expand all the drilldown's and export it, then all the info shows
> but the +/- still doesn't show up in excel or PDF.
> I am currently running RS SP2.
> does anyone know the reason why it's doing this?
>|||no, all the data is part of the same report.
basically, the root level shows a customer's name and their financial
info and when the user clicks on the drilldown +/- it's supposed to
show the customer's address info. All of the info come from the same
dataset or query.
I wonder if it has anything to do with SP2.
Chris Conner wrote:
> Something has to be wrong...
> For starters:
> PDF files will ONLY show the report exactly how it looks - i.e. if some
> components are collapsed, and others expanded, then that is EXACTLY what you
> get when you export to PDF.
> EXCEL on the other hand WILL show drill-downs and carry that
> functionality into the excel workbook itself. I.e. You can after exporting
> into Excel drill-down into your data or collapsed as if you were on the
> report server web page viewing this report.
> One of the issues with Excel drill downs is when people use a SubReport.
> Subreports do not render into Excel at all. (Pisses me off of course) :) Are
> you using a subreport?
> =-Chris
> "bevarg" <benovarghese@.gmail.com> wrote in message
> news:1162502355.922494.29120@.f16g2000cwb.googlegroups.com...
> >I have a report that has the drilldown function in it with the +/-
> > sign. When I export the report to excel or PDF, the drilldown
> > functionality is lost and I am not able to view the info at the lower
> > level's.
> > If I expand all the drilldown's and export it, then all the info shows
> > but the +/- still doesn't show up in excel or PDF.
> > I am currently running RS SP2.
> >
> > does anyone know the reason why it's doing this?
> >|||Has chris said it works fine with excel. Sometimes we miss out simple things,
Just in case , in excel the + sign ie grouping happens and it is extreme left
side of the sheet or the screen itself. hope you noticed this. If it is still
not there.
Amarnath
"bevarg" wrote:
> no, all the data is part of the same report.
> basically, the root level shows a customer's name and their financial
> info and when the user clicks on the drilldown +/- it's supposed to
> show the customer's address info. All of the info come from the same
> dataset or query.
> I wonder if it has anything to do with SP2.
>
> Chris Conner wrote:
> > Something has to be wrong...
> >
> > For starters:
> > PDF files will ONLY show the report exactly how it looks - i.e. if some
> > components are collapsed, and others expanded, then that is EXACTLY what you
> > get when you export to PDF.
> >
> > EXCEL on the other hand WILL show drill-downs and carry that
> > functionality into the excel workbook itself. I.e. You can after exporting
> > into Excel drill-down into your data or collapsed as if you were on the
> > report server web page viewing this report.
> >
> > One of the issues with Excel drill downs is when people use a SubReport.
> > Subreports do not render into Excel at all. (Pisses me off of course) :) Are
> > you using a subreport?
> >
> > =-Chris
> >
> > "bevarg" <benovarghese@.gmail.com> wrote in message
> > news:1162502355.922494.29120@.f16g2000cwb.googlegroups.com...
> > >I have a report that has the drilldown function in it with the +/-
> > > sign. When I export the report to excel or PDF, the drilldown
> > > functionality is lost and I am not able to view the info at the lower
> > > level's.
> > > If I expand all the drilldown's and export it, then all the info shows
> > > but the +/- still doesn't show up in excel or PDF.
> > > I am currently running RS SP2.
> > >
> > > does anyone know the reason why it's doing this?
> > >
>