Does MSSQL support LIMIT

Hi,

I'm creating a software, and I have a dilemn... To check it I use MySQL, but I want my software to use a lot of kind of diffrent DB (oracle, DB2, informix, etc...)

But I have to know :

Does MSSQL support something like that (and if not how to do it) :

SELECT fields FROM Table LIMIT 0,500

??

thank you very much for a quick answer, because All development depend of the answer !!!

thank you,
bye.no, it doesn't

you'll have to use TOP instead, which is explained in the manual

Does MS plan a GUI

Hello all,

We are planning to base a product on Microsoft SQL-2005 Notification services. However, one of the required properties from this software is to provide the client administrator a user interface to configure the Instance Configuration File (ICF) and Application Definition File (ADF). Working with XML files will not do for us, once the responsibility is delegated from a developer to an administrator.

Before going into the process of writing such an application from scratch, I would like to know if Microsoft has such an enhancement on the SQL-NS roadmap, and if so, when it is scheduled to.

Does anyone have any thoughts or knowledge?

Thanks

Ziv G.

I don't have any inside knowledge into what Microsoft is planning for future releases. But in the meantime if you're using the SSNS 2005, you can develop your own SSNS GUI to create and manage instances and applications using the SSNS API. I've given a few examples on my blog. Go to http://www.sqlns.com and enter a search criteria of nmo in the textbox in the upper left hand corner of the form.

HTH...

Joe

|||

Ziv,

Drop me an email, I may be able to help with the GUI. BTW, do you work in Israel, if so where?

-- Shaul Dar (shauld@.medcon.com)

|||

Hi Joe,

Thanks for your (quick) response. I had a look at the API supplied with SQL-NS, it seems pretty extensive, and well designed. The thing is that it seems to me that creating an ad-hoc solution should be pretty quick and simple. On the other hand, constructing a true user application, that takes into account SQL-NS integrative capabilities (custom event provider, protocols and so on), while providing default values for the internal components (SMTP delivery protocol for example), taking into consideration robustness and so on, would take a considerable time to design and write.

The thing is that it seems to me like a general-purpose software, that any NS developer would find beneficial, not an application you would expect each developer to write on his own (unlike subscription/subscriber management, which would probably be integrated into customer application, and therefore cannot be supplied with the product).

In any case, I appreciate your response, and I really enjoy your blog.

Thanx, Ziv G.

|||

Hi Ziv -

>>The thing is that it seems to me that creating an ad-hoc solution should be pretty quick and simple.
>>On the other hand, constructing a true user application, that takes into account SQL-NS integrative
>>capabilities (custom event provider, protocols and so on), while providing default values for the internal
>>components (SMTP delivery protocol for example), taking into consideration robustness and so on,
>>would take a considerable time to design and write.

Agreed. Creating an instance/application is typically much faster with the ICF and ADF rather than with the API. I think the API was added to the product to pave the way for Microsoft and 3rd parties to create toolsets. I keep intending to do that for my own practice, but alas I just haven't done it yet.

Joe

|||

I know this isn't going to be a very satisfying answer but at this point in the product cycle, we're not discussing what features we're thinking about for the next version of SQL Server.

Over time, you'll see us come out with more detailed plans for the overall product. If I remember correctly, it wasn't until 1.5 years into SQL Server 2005 that we did our Tech Preview and started the ball rolling on what features we were thinking about. These things take a little time to settle out.

Thanks,

-Lukasz

|||

Hi Luksaz,

Thanks for your answer. You are right, I wanted to get a different answer, but your answer makes sense to me.

Thanx again,

Ziv G.

Does MS plan a GUI

Hello all,

We are planning to base a product on Microsoft SQL-2005 Notification services. However, one of the required properties from this software is to provide the client administrator a user interface to configure the Instance Configuration File (ICF) and Application Definition File (ADF). Working with XML files will not do for us, once the responsibility is delegated from a developer to an administrator.

Before going into the process of writing such an application from scratch, I would like to know if Microsoft has such an enhancement on the SQL-NS roadmap, and if so, when it is scheduled to.

Does anyone have any thoughts or knowledge?

Thanks

Ziv G.

I don't have any inside knowledge into what Microsoft is planning for future releases. But in the meantime if you're using the SSNS 2005, you can develop your own SSNS GUI to create and manage instances and applications using the SSNS API. I've given a few examples on my blog. Go to http://www.sqlns.com and enter a search criteria of nmo in the textbox in the upper left hand corner of the form.

HTH...

Joe

|||

Ziv,

Drop me an email, I may be able to help with the GUI. BTW, do you work in Israel, if so where?

-- Shaul Dar (shauld@.medcon.com)

|||

Hi Joe,

Thanks for your (quick) response. I had a look at the API supplied with SQL-NS, it seems pretty extensive, and well designed. The thing is that it seems to me that creating an ad-hoc solution should be pretty quick and simple. On the other hand, constructing a true user application, that takes into account SQL-NS integrative capabilities (custom event provider, protocols and so on), while providing default values for the internal components (SMTP delivery protocol for example), taking into consideration robustness and so on, would take a considerable time to design and write.

The thing is that it seems to me like a general-purpose software, that any NS developer would find beneficial, not an application you would expect each developer to write on his own (unlike subscription/subscriber management, which would probably be integrated into customer application, and therefore cannot be supplied with the product).

In any case, I appreciate your response, and I really enjoy your blog.

Thanx, Ziv G.

|||

Hi Ziv -

>>The thing is that it seems to me that creating an ad-hoc solution should be pretty quick and simple.
>>On the other hand, constructing a true user application, that takes into account SQL-NS integrative
>>capabilities (custom event provider, protocols and so on), while providing default values for the internal
>>components (SMTP delivery protocol for example), taking into consideration robustness and so on,
>>would take a considerable time to design and write.

Agreed. Creating an instance/application is typically much faster with the ICF and ADF rather than with the API. I think the API was added to the product to pave the way for Microsoft and 3rd parties to create toolsets. I keep intending to do that for my own practice, but alas I just haven't done it yet.

Joe

|||

I know this isn't going to be a very satisfying answer but at this point in the product cycle, we're not discussing what features we're thinking about for the next version of SQL Server.

Over time, you'll see us come out with more detailed plans for the overall product. If I remember correctly, it wasn't until 1.5 years into SQL Server 2005 that we did our Tech Preview and started the ball rolling on what features we were thinking about. These things take a little time to settle out.

Thanks,

-Lukasz

|||

Hi Luksaz,

Thanks for your answer. You are right, I wanted to get a different answer, but your answer makes sense to me.

Thanx again,

Ziv G.

doctors' appointments - please advise

i am developing a clinci software and i guess i reached to the most difficult part of it which is the appointment module.. I have made it as explaied below but not sure if i am doing it the right way or if i need to redesign my tables' structure.. please advise..

i have create doctors' duty table:

USE [shefa]
GO
/****** Object: Table [dbo].[staff_duty] Script Date: 11/16/2006 02:25:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[staff_duty](
[duty_id] [int] IDENTITY(1,1) NOT NULL,
[staff_file_no] [int] NULL,
[staff_name] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[duty_from] [datetime] NULL,
[duty_to] [datetime] NULL,
[record_locked] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_staff_duty_record_locked] DEFAULT ('N'),
[created_date] [datetime] NULL CONSTRAINT [DF_staff_duty_created_date] DEFAULT (getdate()),
[created_user] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[created_pc] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[created_version] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[created_domain] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[created_os] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[created_workingset] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_staff_duty] PRIMARY KEY CLUSTERED
(
[duty_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

and created the following appointments table:

USE [shefa]
GO
/****** Object: Table [dbo].[appointments] Script Date: 11/16/2006 02:26:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[appointments](
[appointment_id] [int] IDENTITY(1,1) NOT NULL,
[appointment_guid] [uniqueidentifier] ROWGUIDCOL NULL CONSTRAINT [DF_appointments_appointment_guid] DEFAULT (newid()),
[appointment_file_no] [int] NULL,
[appointment_telephone] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[appointment_name] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[appointment_dr_id] [int] NULL,
[appointment_dr_name] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[appointment_start] [datetime] NULL,
[appointment_end] [datetime] NULL,
[created_by_date] [datetime] NULL CONSTRAINT [DF_appointments_created_by_date] DEFAULT (getdate()),
[created_by_user] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[updated_by_date] [datetime] NULL,
[updated_by_user] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_appointments] PRIMARY KEY CLUSTERED
(
[appointment_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

now i will need in C# to list the doctor's free time (means should be between his from and to in the staff_duty table and not listed in the appointments table for the dame day).. keeping in mind that each appointment should NOT take more than 15minutes.

Jassim:

I put together a mock-up based on what I could see. The main thing that I can see is the need for a "time slot" table. Also, you might have need for a "calendar" table.

declare @.staff_duty TABLE
(
[duty_id] [int] IDENTITY(1,1) NOT NULL,
[staff_name] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[duty_from] [datetime] NULL,
[duty_to] [datetime] NULL
)

insert into @.staff_duty(staff_name, duty_from, duty_to) values ('Ben Casey', '9:00', '17:00')
insert into @.staff_duty(staff_name, duty_from, duty_to) values ('Mark Welby', '8:00', '16:00')
--select * from @.staff_duty

declare @.appointments TABLE
(
[appointment_id] [int] IDENTITY(1,1) NOT NULL,
[appointment_name] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[appointment_dr_id] [int] NULL,
[appointment_start] [datetime] NULL,
[appointment_end] [datetime] NULL
)

insert into @.appointments (appointment_name, appointment_dr_id, appointment_start, appointment_end)
values ('Scooby Doo', 1, '12/1/6 8:45', '8:58')
insert into @.appointments (appointment_name, appointment_dr_id, appointment_start, appointment_end)
values ('Wiley Coyote', 1, '12/1/6 9:00', '9:20')
insert into @.appointments (appointment_name, appointment_dr_id, appointment_start, appointment_end)
values ('Betty Rubble', 2, '12/1/6 10:00', '10:13')
insert into @.appointments (appointment_name, appointment_dr_id, appointment_start, appointment_end)
values ('Wilma Pebble', 2, '12/1/6 13:15', '13:29')

print ' '
print ' -- Appointments: -- '
print ' '
print ' '

select appointment_id,
left (appointment_name, 25) as appointment_name,
appointment_dr_id as dr_id,
convert (varchar (20), appointment_start, 100) as appointment_start,
convert (varchar (20), appointment_end, 100) as appointment_end
from @.appointments

declare @.baseTime datetime set @.baseTime = ''

-- --
-- A "Time Slot" table. It seems like the need to deal with
-- specific "time slots" is very high in an "appointment
-- tracking" application.
--
-- I used the master.dbo.spt_values table as a iteration utility
-- for burning through all of the necessary time slot. This
-- table should NOT be used as a basis for day-to-day production
-- code because the contents of this table is not guaranteed.
-- Indeed, the number range differs between SQL Server 2000 and
-- and SQL Server 2005, so be aware!
--
-- If you have a consistent need for a table as an iteration
-- utility, consider article:
--
-- http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html
--
-- It might also be a good idea to create a time slot table in
-- combination with a calendar table. A good article for
-- a calendar table is at:
--
-- http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html
--
-- --
declare @.timeSlot table
( slot_id integer not null,
fromHourMin integer not null,
toHourMin integer not null,
fromDt datetime not null,
toDt datetime not null
)

insert into @.timeSlot
select number+1,
100*(number/4) + 15*(number%4),
100*((number+1)/4) + 15*((number+1)%4),
dateadd (mi, 15*number, @.baseTime),
dateadd (mi, 15*(number+1), @.baseTime)
from master.dbo.spt_values (nolock)
where name is null
and number <= 95

declare @.dr_name varchar (255) set @.dr_name = 'Mark Welby'

print ' '
print ' -- '
print ' -- Available Time Slots: -- '
print ' -- '
print ' '

select d.duty_id,
left (d.staff_name, 25) as staff_name,
right (convert (varchar (30), s.fromDt, 100), 7) slotTime
from @.staff_duty d
inner join @.timeSlot s
on d.duty_from <= s.fromDt
and d.duty_to >= s.toDt
and d.staff_name = @.dr_name
and not exists
( select 0 from @.appointments a
where convert (datetime, convert (varchar (30), appointment_start, 108)) <= s.fromDt
and convert (datetime, convert (varchar (30), appointment_end, 108)) > s.FromDt
)
order by d.staff_name,
s.fromDt

--
-- Sample Output:
--

--
-- -- Appointments: --
--

-- appointment_id appointment_name dr_id appointment_start appointment_end
-- -- - -- -- --
-- 1 Scooby Doo 1 Dec 1 2006 8:45AM Jan 1 1900 8:58AM
-- 2 Wiley Coyote 1 Dec 1 2006 9:00AM Jan 1 1900 9:20AM
-- 3 Betty Rubble 2 Dec 1 2006 10:00AM Jan 1 1900 10:13AM
-- 4 Wilma Pebble 2 Dec 1 2006 1:15PM Jan 1 1900 1:29PM

-- --
-- -- Available Time Slots: --
-- --
-- duty_id staff_name slotTime
-- -- - --
-- 2 Mark Welby 8:00AM
-- 2 Mark Welby 8:15AM
-- 2 Mark Welby 8:30AM
-- 2 Mark Welby 9:30AM
-- 2 Mark Welby 9:45AM
-- 2 Mark Welby 10:15AM
-- 2 Mark Welby 10:30AM
-- 2 Mark Welby 10:45AM
-- 2 Mark Welby 11:00AM
-- 2 Mark Welby 11:15AM
-- 2 Mark Welby 11:30AM
-- 2 Mark Welby 11:45AM
-- 2 Mark Welby 12:00PM
-- 2 Mark Welby 12:15PM
-- 2 Mark Welby 12:30PM
-- 2 Mark Welby 12:45PM
-- 2 Mark Welby 1:00PM
-- 2 Mark Welby 1:30PM
-- 2 Mark Welby 1:45PM
-- 2 Mark Welby 2:00PM
-- 2 Mark Welby 2:15PM
-- 2 Mark Welby 2:30PM
-- 2 Mark Welby 2:45PM
-- 2 Mark Welby 3:00PM
-- 2 Mark Welby 3:15PM
-- 2 Mark Welby 3:30PM
-- 2 Mark Welby 3:45PM

|||

that's a bit complicated...

let me ask the following question..

if I have the duty_from and the duty_to form the staff_duty table..

how can I create a procedure to generate an INSERT statement to insert to timeTable start_date based on 15minutes?

|||I too am trying to develop an appointment app. What development tools are you using? Will you be connecting to SQL Server?