New to SQL and Web development

I have been in systems for many years on the admin side, and have had experience working with developers, however I am attempting to start the learning process for creating a data store with a web based UI. I have an understanding of the theories of what
is possible, but I don't know where to start. I have experience with HTML coding and static pages, but nothing that interfaces with a back end.
What are your suggestions of where to start.
This is a great site with lots of good answers to many questions:
http://www.aspfaq.com/
You will find good examples and information out on =
http://www.asp101.com/
--=20
Keith
"Otts73" <sh_tmail@.charter.net> wrote in message =
news:EFF6B423-F1FA-41D1-9139-3929979CCB3F@.microsoft.com...
> I have been in systems for many years on the admin side, and have had =
experience working with developers, however I am attempting to start the =
learning process for creating a data store with a web based UI. I have =
an understanding of the theories of what is possible, but I don't know =
where to start. I have experience with HTML coding and static pages, =
but nothing that interfaces with a back end.
>=20
> What are your suggestions of where to start.
|||As well as Keith's references, you might like to look at
http://www.w3schools.com/ado/default.asp
Regards,
Paul Ibison

New to SQL and Web development

I have been in systems for many years on the admin side, and have had experi
ence working with developers, however I am attempting to start the learning
process for creating a data store with a web based UI. I have an understand
ing of the theories of what
is possible, but I don't know where to start. I have experience with HTML c
oding and static pages, but nothing that interfaces with a back end.
What are your suggestions of where to start.This is a great site with lots of good answers to many questions:
http://www.aspfaq.com/
You will find good examples and information out on =
http://www.asp101.com/
--=20
Keith
"Otts73" <sh_tmail@.charter.net> wrote in message =
news:EFF6B423-F1FA-41D1-9139-3929979CCB3F@.microsoft.com...
> I have been in systems for many years on the admin side, and have had =
experience working with developers, however I am attempting to start the =
learning process for creating a data store with a web based UI. I have =
an understanding of the theories of what is possible, but I don't know =
where to start. I have experience with HTML coding and static pages, =
but nothing that interfaces with a back end.
>=20
> What are your suggestions of where to start.|||As well as Keith's references, you might like to look at
http://www.w3schools.com/ado/default.asp
Regards,
Paul Ibison

New to SQL and Web development

I have been in systems for many years on the admin side, and have had experience working with developers, however I am attempting to start the learning process for creating a data store with a web based UI. I have an understanding of the theories of what is possible, but I don't know where to start. I have experience with HTML coding and static pages, but nothing that interfaces with a back end
What are your suggestions of where to start.This is a great site with lots of good answers to many questions:
http://www.aspfaq.com/
You will find good examples and information out on =http://www.asp101.com/
-- Keith
"Otts73" <sh_tmail@.charter.net> wrote in message =news:EFF6B423-F1FA-41D1-9139-3929979CCB3F@.microsoft.com...
> I have been in systems for many years on the admin side, and have had =experience working with developers, however I am attempting to start the =learning process for creating a data store with a web based UI. I have =an understanding of the theories of what is possible, but I don't know =where to start. I have experience with HTML coding and static pages, =but nothing that interfaces with a back end.
> > What are your suggestions of where to start.|||As well as Keith's references, you might like to look at
http://www.w3schools.com/ado/default.asp
Regards,
Paul Ibisonsql

new to sql - trying to convert vb function to udf

Hi

I'm an Access/VB coder by experience and trying to move apps to SQL Server 2000.
I have got to grips with the basics of DTS and tables and views and Stored Procedures (to an extent) but now need to upgrade an app that uses a vb function to produce a phased value for a set of budgets.

The VB function looks like this...

----------
Function calcPercentOfBudget(datFromDate As Date, datToDate As Date, iMonth As Integer, cBudget As Currency) As Currency

Dim iDuration As Integer
iDuration = DateDiff("d", datFromDate, datToDate) + 1

' if either date not in current year then 0 (both dates should be in same year)
If Year(datFromDate) <> Year(Now) Or Year(datToDate) <> Year(Now) Then
calcPercentOfBudget = 0
End If

Dim sRatio As Single, idaysInMonth As Integer, idaysInYear As Integer

' if passed month outside of period then 0
If Not (iMonth >= Month(datFromDate) And iMonth <= Month(datToDate)) Then
calcPercentOfBudget = 0
Exit Function
End If

idaysInMonth = daysInMonth(iMonth, Year(Now))


'if from date and to date in same month then 100% of budget
If Month(datFromDate) = Month(datToDate) Then
calcPercentOfBudget = cBudget
Exit Function
End If

' if passed month in From month then ratio of passed month (caters for 1st day of month - 100%)
If Month(datFromDate) = iMonth Then
'calcPercentOfBudget = (idaysInMonth + 1 - Day(datFromDate)) / idaysInYear * cBudget
calcPercentOfBudget = (idaysInMonth + 1 - Day(datFromDate)) / iDuration * cBudget
Exit Function
End If

'if passed month in To month then ratio of passed month (caters for last day of month - 100%)
If Month(datToDate) = iMonth Then
'calcPercentOfBudget = Day(datToDate) / idaysInYear * cBudget
calcPercentOfBudget = Day(datToDate) / iDuration * cBudget
Exit Function
End If

' if passed month within period then 100%
If iMonth > Month(datFromDate) And iMonth < Month(datToDate) Then
'calcPercentOfBudget = idaysInMonth / idaysInYear * cBudget
calcPercentOfBudget = idaysInMonth / iDuration * cBudget
Exit Function
End If

End Function
----------
Function daysInMonth(iMonth As Integer, iYear As Integer) As Integer

Dim datTemp As Date

datTemp = CDate("1/" & CStr(iMonth) & "/" & CStr(iYear))
datTemp = DateAdd("m", 1, datTemp)
datTemp = DateAdd("d", -1, datTemp)

daysInMonth = Day(datTemp)

End Function
----------

I have a UDF function LastDayInMonth to replace the daysInMonth vb function, and that works fine.

However I'm starting to get frustrated in trying to convert the main VB funciton to a UDF function. This is what I have got to, and as you'll see its totally wrong!...

CREATE FUNCTION [dbo].[calcPercentOfBudget] (@.datFrom as datetime, @.datTo as datetime , @.iMonth as int, @.cBudget as money, @.GetDate as datetime)
RETURNS money AS
BEGIN
declare @.iDuration int
declare @.returnvalue money
declare @.sRatio decimal
declare @.iDaysInMonth int

set @.iDuration = datediff(d, @.datFrom, @.datTo)
set @.iDaysinMonth = dbo.LastDayInMonth('1/' + cast(@.iMonth as varchar) + '/' + cast(year(@.getdate) as varchar))

case
when year(@.datFrom) <> year(@.getdate) or year(@.datTo) <> year(@.getdate)
set @.returnvalue = 0
when not(@.iMonth >= Month(@.datFrom) and @.iMonth <= month(@.datTo)
set @.returnvalue 0
when month(@.datFrom) = month(@.datTo)
@.set @.returnvalue = @.cBudget
when month(@.datFrom) = @.iMonth
set @.returnvalue = (@.iDaysInMonth + 1 - day(@.datFrom)) / @.iDuration * @.cBudget
when month(@.datTo) = @.iMonth
set @.returnvalue = Day(datToDate) / iDuration * cBudget
when @.iMonth > month(@.datFrom) and @.iMonth < month(@.datTo)
set @.returnvalue = @.iDaysInMonth / @.iDuration * @.cBudget
end

return @.returnvalue
END

This is my first post to this forum - so any constructive criticism will be welcomed.

Basically, where am I going wrong? - have I got the wrong end of the stick? Have I got the wrong stick? Have I got a stick of dynamite?!

All help greatfully received,
Paul:eek:Case statements work a little differently in T-SQL. Try this:

CREATE FUNCTION [dbo].[calcPercentOfBudget] (@.datFrom as datetime, @.datTo as datetime , @.iMonth as int, @.cBudget as money, @.GetDate as datetime)
RETURNS money AS
BEGIN
declare @.iDuration int
declare @.returnvalue money
declare @.sRatio decimal
declare @.iDaysInMonth int

set @.iDuration = datediff(d, @.datFrom, @.datTo)
set @.iDaysinMonth = dbo.LastDayInMonth('1/' + cast(@.iMonth as varchar) + '/' + cast(year(@.getdate) as varchar))

select @.returnvalue =
case when year(@.datFrom) <> year(@.getdate) or year(@.datTo) <> year(@.getdate) then 0
when not(@.iMonth >= Month(@.datFrom) and @.iMonth <= month(@.datTo) then 0
when month(@.datFrom) = month(@.datTo) then @.cBudget
when month(@.datFrom) = @.iMonth then (@.iDaysInMonth + 1 - day(@.datFrom)) / @.iDuration * @.cBudget
when month(@.datTo) = @.iMonth then Day(datToDate) / iDuration * cBudget
when @.iMonth > month(@.datFrom) and @.iMonth < month(@.datTo) then @.iDaysInMonth / @.iDuration * @.cBudget
end

return @.returnvalue
END

Case in T-SQL returns a value, rather than executes a block of code.|||fantastic, after a couple of other debugs from translation I have a result that works!

Many thanks :)

New To Replication

I am a software developer who has been tasked with setting up replication for one of our clients. I have about 1 week of experience working with replication, so any help would be appreciated.
My situation:
5 databases:
Production - All sales transactions go into here
Corporate - Used to report on everything in the Production DB
Site A - Filtered for Site A data
Site B - Filtered for Site B data
Site C - Filtered for Site C data
All connections must be two-way (Data changed at Site A most propagate back through Corporate to Production, data entered at Production -> Corporate -> Site A\B\C)
I want the Corporate DB to be the publisher and Production, Site A\B\C to be subscribers to keep the load off Production.
The issues I have are:
Filtering the data to Sites A\B\C
Default values are being stripped off columns during replication
Data is not being propagated accuratly (Data entered at production replicated to Corporate, then the data is deleted at Corporate and at Production).
I have tried several combinations of Merge and Transactional Replication and nothing works as needed.
Thanks,
David
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
David,
one topology that should solve your issues:
Production publishes everything to Corporate using merge.
Corporate publishes to SitesA-C (3 subscribers) using merge with dynamic
filters.
This setup is known as republishing, as Corporate is both a publisher and a
subscriber.
HTH,
Paul Ibison

new recovery model in SQL Server 2005

It seems that SQL Server 2005 is changing the recovery model. I have experience with Oracle and *I think* this is the same model. One thing I noticed in Oracle was sometimes this fast recovery model helped me, and sometimes it didn't. It helped when perhaps a large table was undergoing recovery and I could give users access to the other tables. It hurt me when recovery wasn't localised like that, and all the users ended up submitting the same TX that they issued when the outage occurred. So what happened, the recovery process thrashed as it tried to recover the pages that the users requested which needed recovery in the first place.

Does SQL Server 2005 new model work the same way. Is there a switch for me to use to let me choose the recovery model I want based on the application perhaps?Just to clarify, like SQL2000, the SQL2005 supports the same three recovery models: FULL, BULK_LOGGED and SIMPLE. May be the point you are referring to is that the database is made available immediately after REDO phase is completed in SQL2005. However, the transactions that are being rolled back during UNDO phase acquire the same locks as they had before the crash and will block/allow concurrent access based on lock compatibility as described in BOL. I am not sure what you meant by 'recovery process thrashed'.

Thanks|||Sunil - what I meant that if it is like Oracle, then the recovery process will go through and recovery pages in a certain order. But if a user wants a page, the engine will go and retrieve that one, even if it is out of order. Is that what SQL Server does? That way users can access pages that don't need recovery as it works through the ones that do, and if a user requests a page that needs recovery, it will go and get it. If 100 users all ask for pages and the engine goes to recover them, it kind of thrashes the recovery process. In some apps in my experience this feature is great, others, no so great. I wanted to know if I could turn this off or on per the database level too (doubt it)(. Thanks!|||That is not quite what SQL does.
SQL 2005 completes the redo phase prior to bringing the database online.
At this point, the database is in the state it was in when the backup was taken, including any transactions which were in progress.

Those transactions may hold locks, as Sunil noted.
At this point, users may access any page in the database which does not conflict with a lock.

In the background, the engine performs the Undo phase to roll back any transactions which were incomplete when the backup was taken, releasing locks as it goes.

None of the background operations are done out of order as a result of client query activity, so we do not thrash the recovery process.

Is this more clear?

Kevin