Another Function question

I hope I am not resubmitting this question, but I can't find my original submittal...

Here is another function problem that I have (luckily, the last). I hope there is someone out there who is bored and might want to give this one a try. If not, that's okay, I think completing 4 out of 5 questions is pretty good. hehe

Question:

Create a function (EMP_SAL_STATS) that computes the maximum salary for employees in the EMPLOYEES table. Note: do not use the built in function MAX. You must create your own MAX function.

In addition, create a driver program that runs the function. Show all work including the output from running the procedure.

My two lines of work (haha):
CREATE OR REPLACE FUNCTION emp_sal_stats
(p_employee_id employees.empoyee_id%TYPE)My one line of hint: Maybe you could order the data descending and then fetch one row?|||Thank you very much. I will give it a try.

Cyndi

Anonymous Pull using FTP - still insists on using original UNC for file share

I've been trying to set up an anonymous pull subscription for merge
replication - SQL 2000 on both sides, post SP3 & same on both machines.
However, when I set up the replication and try to do the initial sync, the
subscription insists on trying to use the original UNC path where I stored the
files from the publisher. Copying the files locally doesn't seem to help as
it still tries to access the UNC. Same with FTP - I set up the site &
specified to use it, but it still tries to access the UNC that way. I
verified that it can access the files via FTP and even copied the files
locally to specify a local path.
I tried reading through the BOL, but didn't come up with much to guide me on
what to try next to get it to read the local files. Any suggestions? This is
the only subscriber I'll have, but it's not on the domain and in our DMZ.
Thanks for any ideas you may have.
-Pete Schott
the connection information on how the subscriber connects with the publisher
to pull the subscription is stored on the publisher.
Do a sp_helpmergepublication 'PublicationName' in your publication database
to verify the publication is 1) enabled for the internet 2) has an alternate
snapshot folder that maps to the root or a virtual directory on your ftp
server, 30 has an ftp address
I take it when you created your pull subscription you chose to download your
files using ftp?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Peter A. Schott" <pschott@.no.spamm.hear.drivefinancial.com> wrote in
message news:1qrpc01577kh0g5vsfcvl7r00ioc4d6scg@.4ax.com...
> I've been trying to set up an anonymous pull subscription for merge
> replication - SQL 2000 on both sides, post SP3 & same on both machines.
> However, when I set up the replication and try to do the initial sync, the
> subscription insists on trying to use the original UNC path where I stored
the
> files from the publisher. Copying the files locally doesn't seem to help
as
> it still tries to access the UNC. Same with FTP - I set up the site &
> specified to use it, but it still tries to access the UNC that way. I
> verified that it can access the files via FTP and even copied the files
> locally to specify a local path.
> I tried reading through the BOL, but didn't come up with much to guide me
on
> what to try next to get it to read the local files. Any suggestions?
This is
> the only subscriber I'll have, but it's not on the domain and in our DMZ.
> Thanks for any ideas you may have.
> -Pete Schott
|||Well, I did that - it's enabled for Internet, has an alternate snapshot
folder, and the folder maps to the root of the virtual/ftp directory. It's
able to pull several of the files, but then dies trying to get to the full UNC
path.
It does the same thing if I specify to use an alternate/local path instead. I
can't seem to get away from that UNC. at all.
Any ideas? Has anyone got some steps that work all of the time to apply an
initial snapshot anonymously over FTP?
TIA,
-pete
"Hilary Cotter" <hilaryk@.att.net> wrote:

> the connection information on how the subscriber connects with the publisher
> to pull the subscription is stored on the publisher.
> Do a sp_helpmergepublication 'PublicationName' in your publication database
> to verify the publication is 1) enabled for the internet 2) has an alternate
> snapshot folder that maps to the root or a virtual directory on your ftp
> server, 30 has an ftp address
> I take it when you created your pull subscription you chose to download your
> files using ftp?
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Peter A. Schott" <pschott@.no.spamm.hear.drivefinancial.com> wrote in
> message news:1qrpc01577kh0g5vsfcvl7r00ioc4d6scg@.4ax.com...
> the
> as
> on
> This is
>

Andrew J. Kelly please

Since you are so helpful, here is another question regarding my original
post yesterday:
********************************************************************
My database has one data file only, and the data file was 900M of data after
I ran DBCC SHRINKDATABASE(DBName). But I wanted to give it 10% free space so
it does not have to expand in a while; I ran DBCC
DBREINDEX(TableName,N'',90) on all of my user tables (all tables have
clustered index). I was expecting the database to expand 900 X 10% = 90M.
However, when it completed, the data file became 1.9G in total. When I view
the database from EM (taskpad view), looks like 50% free space. Why? Thanks.
********************************************************************
Question:
I actually have 100+ such databases on the server and it is running out of
space. I would like to somehow get some free space back to OS (leaving
10-20% of free space in data files instead of 50% right now). And we also
want to re-org data files as we have lots of insert/delete/updates going on
daily. Since DBCC DBREINDEX will not release free space, what are my
options? I don't care about log file growth as they are in seperate RAID
with plenty of spaces. Thank you very much.
JohnIm not Andrew Kelly, but up until he replies, take a loot at DBCC Shrinkfile
and have your data files in there as parameters.
"john" <john@.aic.com> wrote in message
news:un9R5ZabEHA.3752@.TK2MSFTNGP12.phx.gbl...
> Since you are so helpful, here is another question regarding my original
> post yesterday:
> ********************************************************************
> My database has one data file only, and the data file was 900M of data
after
> I ran DBCC SHRINKDATABASE(DBName). But I wanted to give it 10% free space
so
> it does not have to expand in a while; I ran DBCC
> DBREINDEX(TableName,N'',90) on all of my user tables (all tables have
> clustered index). I was expecting the database to expand 900 X 10% = 90M.
> However, when it completed, the data file became 1.9G in total. When I
view
> the database from EM (taskpad view), looks like 50% free space. Why?
Thanks.
> ********************************************************************
> Question:
> I actually have 100+ such databases on the server and it is running out of
> space. I would like to somehow get some free space back to OS (leaving
> 10-20% of free space in data files instead of 50% right now). And we also
> want to re-org data files as we have lots of insert/delete/updates going
on
> daily. Since DBCC DBREINDEX will not release free space, what are my
> options? I don't care about log file growth as they are in seperate RAID
> with plenty of spaces. Thank you very much.
> John
>
>|||Well to shrink only the data file you should use DBCC SHRINKFILE and specify
the appropriate parameters. But as noted in the earlier posts this will
undoubtedly fragment your existing tables again. If you shrink it to only
have 10 - 20% free space when you run DBREINDEX it will most likely expand
again. Remember the rule is 1.2 times the size of the object in free space.
Ideally you want that free space to be one big contiguous space on the drive
but it is unlikely to be that way with minimal free space all the time.
Even with 50% free it is most likely scattered and interleaved amongst all
the objects throughout the file. So the less free space you have the more
likely the newly defragmented object will still be interleaved amongst the
other objects but you will cut down on Logical fragmentation. If you have a
lot of Inserts and Updates that cause page splits and fragmentation you
should consider lowering the fill factor to minimize this effect and
increase the time between reindexing. What your asking for is your cake and
the ability to eat it too<g>. Having lots of free space in lots of DB's
does add up to lots of disk space and I can see why you want to cut it down.
But the less free space you have the more likely you will eventually wind up
with no room on the disk when you need to reindex and the file is forced to
grow. The real answer is not what anyone wants to hear and that is to get
more disk space. But in the mean time you can do one of several things to
see if it works for you. One is to only reindex your crucial tables or ones
that have high fragmentation to begin with vs. all the tables. You can also
try using DBCC INDEXDEFRAG instead of REINDEX.
--
Andrew J. Kelly SQL MVP
"john" <john@.aic.com> wrote in message
news:un9R5ZabEHA.3752@.TK2MSFTNGP12.phx.gbl...
> Since you are so helpful, here is another question regarding my original
> post yesterday:
> ********************************************************************
> My database has one data file only, and the data file was 900M of data
after
> I ran DBCC SHRINKDATABASE(DBName). But I wanted to give it 10% free space
so
> it does not have to expand in a while; I ran DBCC
> DBREINDEX(TableName,N'',90) on all of my user tables (all tables have
> clustered index). I was expecting the database to expand 900 X 10% = 90M.
> However, when it completed, the data file became 1.9G in total. When I
view
> the database from EM (taskpad view), looks like 50% free space. Why?
Thanks.
> ********************************************************************
> Question:
> I actually have 100+ such databases on the server and it is running out of
> space. I would like to somehow get some free space back to OS (leaving
> 10-20% of free space in data files instead of 50% right now). And we also
> want to re-org data files as we have lots of insert/delete/updates going
on
> daily. Since DBCC DBREINDEX will not release free space, what are my
> options? I don't care about log file growth as they are in seperate RAID
> with plenty of spaces. Thank you very much.
> John
>
>|||Thank you very much. Really appreciated.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ekL2m$cbEHA.2520@.TK2MSFTNGP12.phx.gbl...
> Well to shrink only the data file you should use DBCC SHRINKFILE and
specify
> the appropriate parameters. But as noted in the earlier posts this will
> undoubtedly fragment your existing tables again. If you shrink it to only
> have 10 - 20% free space when you run DBREINDEX it will most likely expand
> again. Remember the rule is 1.2 times the size of the object in free
space.
> Ideally you want that free space to be one big contiguous space on the
drive
> but it is unlikely to be that way with minimal free space all the time.
> Even with 50% free it is most likely scattered and interleaved amongst all
> the objects throughout the file. So the less free space you have the more
> likely the newly defragmented object will still be interleaved amongst the
> other objects but you will cut down on Logical fragmentation. If you have
a
> lot of Inserts and Updates that cause page splits and fragmentation you
> should consider lowering the fill factor to minimize this effect and
> increase the time between reindexing. What your asking for is your cake
and
> the ability to eat it too<g>. Having lots of free space in lots of DB's
> does add up to lots of disk space and I can see why you want to cut it
down.
> But the less free space you have the more likely you will eventually wind
up
> with no room on the disk when you need to reindex and the file is forced
to
> grow. The real answer is not what anyone wants to hear and that is to get
> more disk space. But in the mean time you can do one of several things to
> see if it works for you. One is to only reindex your crucial tables or
ones
> that have high fragmentation to begin with vs. all the tables. You can
also
> try using DBCC INDEXDEFRAG instead of REINDEX.
> --
> Andrew J. Kelly SQL MVP
>
> "john" <john@.aic.com> wrote in message
> news:un9R5ZabEHA.3752@.TK2MSFTNGP12.phx.gbl...
> > Since you are so helpful, here is another question regarding my original
> > post yesterday:
> >
> > ********************************************************************
> > My database has one data file only, and the data file was 900M of data
> after
> > I ran DBCC SHRINKDATABASE(DBName). But I wanted to give it 10% free
space
> so
> > it does not have to expand in a while; I ran DBCC
> > DBREINDEX(TableName,N'',90) on all of my user tables (all tables have
> > clustered index). I was expecting the database to expand 900 X 10% =90M.
> > However, when it completed, the data file became 1.9G in total. When I
> view
> > the database from EM (taskpad view), looks like 50% free space. Why?
> Thanks.
> > ********************************************************************
> >
> > Question:
> >
> > I actually have 100+ such databases on the server and it is running out
of
> > space. I would like to somehow get some free space back to OS (leaving
> > 10-20% of free space in data files instead of 50% right now). And we
also
> > want to re-org data files as we have lots of insert/delete/updates going
> on
> > daily. Since DBCC DBREINDEX will not release free space, what are my
> > options? I don't care about log file growth as they are in seperate RAID
> > with plenty of spaces. Thank you very much.
> >
> > John
> >
> >
> >
> >
>