Recurring Appointments Calendar Design (was The Most Challenging Project)
Jan 14, 2005
Hello,
This is my first project at the company I recently joined. It is the most challenging project I ever had.
It is a kind of web-scheduler.
it let you save appointments which could be one time or periodic.
For example, it could be like every monday, every other monday, first tuesday every monday, and so on.
once they are saved, it should be able to display only those appointments the user have on a specific day.
I'd like to know how database has to be designed to meet such a challenging requirement?
I'd like to hear anything from you if you have had similar project before.
Thank you,
Charlie
View 2 Replies
ADVERTISEMENT
Feb 29, 2008
I am trying to load Project Real. I get warnings when opening the RecurringETL package. However I created the two environment variables it's looking for and have confirmed these variables are functional.
I am unsure how to troubleshoot this and am afraid to do anything given these warnings. Where is the configurations collection??????:
Warning 1 Warning loading TestHarness.dtsx: The configuration environment variable was not found. The environment variable was: "REAL_Configuration". This occurs when a package specifies an environment variable for a configuration setting but it cannot be found. Check the configurations collection in the package and verify that the specified environment variable is available and valid. C:Microsoft Project REALETLTestHarness.dtsx 1 1
Warning 2 Warning loading TestHarness.dtsx: The configuration environment variable was not found. The environment variable was: "REAL_Root_Dir". This occurs when a package specifies an environment variable for a configuration setting but it cannot be found. Check the configurations collection in the package and verify that the specified environment variable is available and valid. C:Microsoft Project REALETLTestHarness.dtsx 1 1
Warning 3 Warning loading TestHarness.dtsx: Failed to load at least one of the configuration entries for the package. Check configurations entries and previous warnings to see descriptions of which configuration failed. C:Microsoft Project REALETLTestHarness.dtsx 1 1
Thanks
View 8 Replies
View Related
May 15, 2015
how to design a database for this king of booking?
View 5 Replies
View Related
Nov 29, 2006
Hi,
I am very new to this form and I need urgent help and I hope someone can provide forme some light to my problem.
The college has a soccer team. And the application I am going to develope needs to use MS SQL Server 2000 Developer or Student edition.
I only mange to create these below table and I am not sure if this is the correct way to doing it.
The tables name:
tbl_TEAM: ID, StudentNumber, Name, Surname, Active
tbl_LEAGUE: ID, LeagueName, Active
tbl_WEEKS: ID, WeeksName, Active
tbl_FIXTURE: GameID, HomeTeam, VisitorTeam, Active
tbl_REFFERE: ReferreID, ReffereName, ReffereSurname, Active
tbl_OBSERVER: ObserverID, ObserverName, ObserverSurname, Active
The apliccation will create a fixture via user and then must assign reffere to each game. After the game the observer sends in the game report and user will use the report and assign the points for reffere.
Then Reffere sends the their reports and the application will assign the Goal, yellow card and Red card points to team member.
If any team member recevice 4 yellow card in total and not in one game then application will producea report to say this member team reach the point and have to sit one week and makes his yellow card points to zero. But in the end I have to know how much each team member had yellow card or red car in tottal.
If team member has goal he get 1 point and must show where is the goal. Because sometime they can make mistake and they have goal to their own teams net.
I am not sure if I can explaind correctly so I need help to develope the application and I am very new to soccer game as well as I don't know how to design the database.
Can anyone help me out to over come my SQL problem so I can finish my project before this christmas.
Thank you in advance.
Kind Regards,
Niyazi
View 2 Replies
View Related
Jan 16, 2008
Ok, I'm using vb2005 express and I know that the reportviewer control has to be added and reports can't be designed from the IDE. So, I downloaded the business intelligence studio and I can design a report that looks like just what I want. What I can't seem to do is add that report to the application I'm developing. I don't want to use a report server - I want to use the report with client side processsing. I'm wondering if this is possible and if so how? When I try to add that .rdl file or rename it to a .rdcl file to my project I'm not allowed to do so. Any thoughts?
View 4 Replies
View Related
Dec 17, 2007
Hi,
I have an appointment table as follows:
AppID AppTeam AppStart AppFinish
1 Team1 01/11/2007 10:00 01/11/2007 11:00
2 Team1 01/11/2007 11:01 01/11/2007 12:00
3 Team1 01/11/2007 12:01 01/11/2007 13:00
4 Team1 01/11/2007 15:00 01/11/2007 15:30
5 Team1 01/11/2007 17:00 01/11/2007 17:30
If someone needs a half hour slot for Team1,I need to show all the current appointments for Team 1 that a new half hour appointment can come after but not overlap any other appointment.
So for the example given I would show a list of:
3
4
5
As a half hour appointment could be put in after each of these and not overlap the next appointment.
Hope this makes sense and someone can help
Thanks
View 4 Replies
View Related
Nov 15, 2006
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.
View 3 Replies
View Related
May 29, 2008
For some reason in a Team Foundation Team Project that has multiple project types (SSRS, SSIS, WebSite, C# Business DLL...), the SSIS project makes itself the startup project to the team project. If I explicitly set another project as the startup project to the team project and then select an SSIS package in the SSIS project in the team project, the SSIS project becomes the startup project automatically.
I am using Visual Studio 2005 SP1.
View 3 Replies
View Related
Jan 5, 2007
Guys can you help please...
At the moment I have an unknwon number of ID's and need their related info in another table (1,2,3,4,5 for example) and I'm looping and merging to get them as one table:
For i = 1 to however many...
Dataset = Select where ID = array(i)
FinalDataset.Merge(Dataset)
Next
How can I write a stored proceedure or something where the loop is happening at the database end, to avoid going back and forth?
I was thinking dynamically generating the sql statement:
Select * from table where ID = 1 or ID =2 or ID =3......
But this could become huge if you had a couple of hundred to do...there must be a more efficient way to do it...
View 2 Replies
View Related
Dec 17, 2004
Here's my problem:
I have a date that is entered into the field, 12/17/2004. My client wants the site set up so that when after the date is entered, it automatically updates itself every week. So the date represents an event, and he wants to update the event date automatically instead of having a user login and update the date manually. So 12.17/2004 shoudl turn into 12/24/2004, and then 12/31/2004 and so forth.
One brilliant guy told me that if you entered a start date of and an end date then some how that might me be a little easier.
Any thoughts good people?
View 6 Replies
View Related
Feb 28, 2007
I have a situation where a common table within multiple databases across multiple servers is randomly getting corrupt. The database schema is identical for each database. There are approx. 200 copies of the database on each server. I am seeing one or two errors per week. There doesn't seem to be any similarities between the databases that are effected... It is always the same table that gets corrupted. The mulitple servers are using a SAN but since it is different databases / common table I have a hard time believing it is hardware related.
The error that the application throws is:
Microsoft OLE DB Provider for ODBC Drivers (-2147467259) [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find the index entry for RID '16b700000000000000030002' in index page (1:5366), index ID 0, database 'DISOPT'.
When running a DBCC on the table the following is returned:
Database DISOPT consistency errors in table TF_FILE_METADATA_VALUES fixed no data loss reported.
DBCC results for 'TF_FILE_METADATA_VALUES'.
The error has been repaired.
Clustered index successfully restored for object 'dbo.TF_FILE_METADATA_VALUES' in database 'MUTGEN'.
There are 277 rows in 2 pages for object 'TF_FILE_METADATA_VALUES'.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'TF_FILE_METADATA_VALUES' (object ID 1332199796).
CHECKDB fixed 0 allocation errors and 1 consistency errors in table 'TF_FILE_METADATA_VALUES' (object ID 1332199796).
This corrects the problem and the app functions as normal. The errors are random across databases but ALWAYS the same table.
Any thoughts?
View 7 Replies
View Related
Sep 13, 2006
Is it possible to display when a scheduled job is next due to run ineither a view or a table?If so, how?thanksBryan
View 3 Replies
View Related
Dec 8, 2004
Hi!
My problem/"goal to achieve" is the following..
Users can insert orders in a db-table. The date on which they've been created is stored.
I want the server to insert a new record in another table (accounts) for every order which is older than 2 weeks.
How should I do that?
With a custom action which checks all orders (a customer must press some button every day).
Or a static "update-class" with a kind of timer which does the action every 24 hours?
Of course an automatic solution will be the best! Another question is: how can i ensure that an instance of this "update-class" will never die?
Thanks so far...
View 2 Replies
View Related
Nov 10, 2004
Ugh!
I need to create some SQL that will generate all the dates for a recurring meeting on multiple days per week.
For example:
a meeting on Monday , Wednesday and Friday at 10:00 am - 10:30 am from December 1st 2004 to Feb 28th 2005.
I'm not sure what to do here.
Any Ideas?
Regards
Russ
View 1 Replies
View Related
Jan 4, 2008
Hi,
I have been asked to add a feature to some software I have written which needs to send reminder emails.
The reminders can have a recurrence, for example it might recur every 3 days or every 2 weeks. The reminders also have a start and end date. I have created a sql table with these fields:
Reminder ID
RecurrenceType (e.g. Daily, Weekly, Monthly)
RecurrenceAmount (e.g. 1, 2 etc.)
StartDate
EndDate
ReminderText
AddedBy
DateTimeStamp
I now need to write a stored procedure that will run every morning and send the reminders. I have no problem with sending emails from sql, the trouble I am having is I cannot figure out how to get the recurrences for the current day.
Hope this makes sense!
Sorry there is no code sample, I have been thinking about this one for a couple of days but I still don't know where to begin!
If anyone could offer any help it would be greatly appreciated!
Thanks
Jimbo
View 7 Replies
View Related
Jun 21, 2007
I downloaded VisualBasic 2005 Express Edition recently, and the installation went flawlessly, but when I tried to download the optional component SQL server express, the installation failed. When two subsequent installations failed, I decided I'd get along without it, until such time as I discovered I *needed* it. Well, I'm here, and the installation is still failing. I've tried both installing it though the VBstudio setup, and downloading it separately, but both fail at roughly the same point. The error from the standalone installation is more descriptive:
TITLE: Microsoft SQL Server 2005 Setup
------------------------------
SQL Server Setup Failed to compile the Managed Object Format (MOF) file c:Program FilesMicrosoft SQL Server90Sharedsqlmgmproviderxpsp2up.mof. To proceed, see "Troubleshooting an Installation of SQL Server 2005" or "How to: View SQL Server 2005 Setup Log Files" in SQL Server 2005 Setup Help documentation.
For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=setup.rll&EvtID=29513&EvtType=sqlca%5csqlmofca.cpp%40Do_sqlMofcomp%40Do_sqlMofcomp%40x80041002
The link leads to an apology - they have no information on this subject. Of the help files, the troubleshooting page contains nothing (noticeably) relevant, and the log files have nothing obvious either, at least as far as I could tell... but then, I have no idea, which is why I'm here
View 1 Replies
View Related
Jun 24, 2007
Hi,
I have been working on some SSIS packages for a while now and today while i was working i was trying to create a new connection and in the process there was an error and it said the BIDS has to be closed and i closed it but later when i open BIDS and try to open my project(.sln) from the file menu to work on the half done package it pops up an error which shows the path to my project location on the first line and next statement on the pop up error box says:
"Make Sure the application for the project type (.dtproj) is installed."
I tried to check some forums in which some suggested to try installing SP1 which i tried but ..i dont know why but the SP1 fails to install (i dont know if its causing problem becoz i already installed SP2 as i had some other problem before for which the cure was to install SP2).
Did anyone here face such a problem before ?
I'd really appreciate if the experts here can tell a cure for this problem.
thanks,
Ravi
View 4 Replies
View Related
Feb 23, 2007
I have a Visual Studio 2005 solution which contains a Sql Server Integration Services (SSIS) project.
In this solution I have explicitly set a Web application project as startup project, but whenever I edit a DTS package within the SSIS project, VS automatically sets the SSIS project as startup project and the package I edit as startup object.
Needless to say, this may cause some really unwanted incidents when running the solution - thinking that you're about to run the Web application project (that was explicitly set as startup project), but instead, you run the edited package in the SSIS project.
Is there any way to avoid having the SSIS project automatically setting itself as startup project, any workaround here at all? :)
Thanks.
View 4 Replies
View Related
Jun 16, 2015
in order to maintain a deployed project into an Integration Services Catalog I'd like to know if it is possible to import it into a new project inside SSDT.
View 5 Replies
View Related
May 6, 2007
Hi,
I want to update value of a custom field for a perticular project in Project Server 2007 using PSI.
I have created 5 enterprise custom fields(A,B,C,D,E) through PWA/Server Settings.
I want to search all Projects on Server. If any project is having value for custom field A then I want to update rest of the custom fields(B,C,D,E) for that perticular project.
I dont know how to do it please help.
Thanks in Advance
Madhukar
View 5 Replies
View Related
Feb 3, 2007
I have a very small project written in VB.Net 2005 using the SQL Server 2005 SSiS DTSx package.
I took a SQL Server 2000 dts package and using the SQL Server 3005 legacy tools migrated it so I could still use the package withing SQL 2005 until I can build one using BI/SSIS.
Anyway,I added the reference Microsoft.SqlServer.ManagedDTS so I could then use the Microsoft.SqlServer.Dts.Runtime so I can execute the commands:
Dim oApp As New Application
Dim oPkg As New Package
oPkg = oApp.LoadPackage(g_DTSx_Directory & "AOC copy Generic1 CSV to AOC_verify_file_1.dtsx", Nothing)
Dim oResults As DTSExecResult
oResults = oPkg.Execute
Ok. That works fine. Executes without a hitch. So now I try and create a setup project for this and I use the setup wizard.
During the creation of the setup project I get a message that states:
The following files may have dependencies that cannot be determined automatically. Please confirm that all dependencies have been added to the project.
C:windowssystem32msxml6.dll
OK. The dll is part of the reference I mentioned above and I have no idea what other dependencies it may have.
How do I find this out?
Has anyone else created a project like this and experenced the same?
I am on a clean build running WinXP Pro with SP2 - VS2005 with SP1 and the SQL Server 2005 tools.
View 1 Replies
View Related
Apr 20, 2015
I've got an SSIS solution file with project deployment model in VS 2013 and would like to deploy that to SSISDB on different environments.All these days I followed the regular way to create a project in SSISDB and deploy it to that. Now want to find out if i can automate this process and so got some questions
1. Can we automate the process of creating a project on SSISDB based on our SSIS project name? This will be like when we do a deployment it should check if the project exists or not on SSISDB based on our SSIS project name, if the project exists we just deploy the packages in the project and if the project does not exists in SSISDB it will create that project and deploy the packages.
2. Can we also automate the process of creating environments? In traditional way we manually create the environment variables under environment tab of SSISDB, but can we make that also as part of deployment? Like when we are releasing to Dev server we look if that particular Dev variable exists on that server, if it exists we just update the existing stuff and if it does not exists we just create it.
View 2 Replies
View Related
Mar 31, 2008
I have visual studio 2005 and sql server 2005 with integration service installed on my machine. Couple of days ago, I installed visual studio 2008 professional. When I go to create SSIS project I dont see it in visual studio 2008. What do I have to do to make it appear in visual studio 2008 so that I can create SSIS projects.
Thanks.
View 7 Replies
View Related
Sep 15, 2006
We have some SSIS packages that are running on a recurring SQL Job. They work and play fine. They ran for about three iterations and then decided to stop....they don't report errors, they just don't do anything. They sit as if they were disabled (but they aren't disabled, I checked the MSDB database).
You can manually execute the packages and they work fine.
I have dumped and reloaded these packages and that hasn't fixed the issue.
If I setup a simple recuring job that executes a sql script every few minutes that works fine and you can see that it executes every few minutes. But these SSIS packages just sit....and do nothing. But they were working for the first few iterations.
I have also redeployed the SSIS packages and that hasn't fixed it either.
Anybody seen anything like it?
View 1 Replies
View Related
Aug 11, 2007
Hi guys, wonder if you could help.
Ok, basically, my SSIS flow gets data from different excel worksheets and puts in the db.
For some reason its being very incosistent. I fixed all the problems I was having but one component keeps coming up with the [ component "Excel Source" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA" ] error.
So, I click on the component, it asks if i want to fix the metadata stuff, I say yes, it fixes it, I run the app, everything's cool.
The I run the app again and again it comes up with this f** error?
Is there anyway SSIS can't just ignore this metadata stuff??
thanks!
View 5 Replies
View Related
Jun 22, 2007
Hi All,
I have created a SSIS package which calls child packages internally. In other words there is hierarchy of packages. I am using For Loop Container with certain check conditions to execute whole set of packages repeatedly. I have to execute this set for almost 5000 times. But my problem is this set fails after every 50 and sometimes 55 cycles. Can Anybody let me know how to get solution for such a problem?
Regards,
Prash
View 1 Replies
View Related
Jul 23, 2007
I fail to use project professional 2003 to access to the project server 2003 using MSDE 2000 in local area network, following message was shown,
Connection failed:
SQLState: '01000' SQL Server Error 1326 [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect())
Connection failed:
SQLState '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
I have seen these pages with similiar cases but can't help.
http://support.microsoft.com/kb/818047/
http://support.microsoft.com/kb/837653/en-us
Can anyone help me?? Thanks a lot!
View 1 Replies
View Related
Apr 18, 2008
Hi,I need help to generate following query....------------------------------------------------------------------------------------------------------------- Tables and their columns:Table name: Employee Columns for Employee Table : (1) EmpID (2) EmpName (3) EcatID (4) ProjectIDSecond Table Name: EmpskillColumns for Empskill Table: (1) EmpID (2) SelfRating (3) EvalRatingInformation: (1) If EcatID = 1 means he is Project Manager [if not then he is a software Engineer] (2) Both Project Manager and his subordinates have the same ProjectID------------------------------------------------------------------------------------------------------------- Result Needed:-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- PM Name Software Enggs Nme under that PM No of Self Rated Projects (for SEs) No of Evaluator rated Projects (for SEs) -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Rajesh 10 8 Suguna Mahesh 5 5 Roopesh 3 3--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------In Short: List the Project Mangers name, in the next column list their subordinates (for one PM there are many sub ords), in the next column list the no of self rated projects of the sub ordinates, in the last column, list the No fo evaluator rated projects Can Some One Help me ??Thanks in AdvanceHave a nice day
View 20 Replies
View Related
Jun 14, 2005
I am a bit into this one and it is kind of bugging me as i have not figured it out yet after 1 hour. I have a web task in a Sql Server 2K that generates ASP code and ftps it to a remote location. This is nothing fancy: selects a few fields from a few tables and creates an HTML page into a file with a *.asp extension. It is much like a report that gets ship from the intranet to the Internet side of our business. You can do something similar by using the Tools>>Wizards>>Management>>Web Assistant Wizard... inside Enterprise Manager. The page looks fine but the problem (my) is that the rows all have the same color - white the default. I am using a template [*.tpl] and the alternating color for the rows <tr> has to come from the data in the query. Now what I would like to do is something like this to get alternating colors in my table rows: declare @bgcolor nvarchar(12) set @bgcolor = 'white' select @bgcolor = CASE @bgcolor WHEN 'white' THEN 'gray' WHEN 'gray' THEN 'white' ELSE 'white' END, , field1 , field2 , field3 from table1 inner join table2.... where .... And, maybe get results like these: 'white', 'field1res', 2, 3 'gray', 'field1res, 3, 3 'white', ....etc. The above DOES NOT WORK. Maybe because T-SQL is a set based language trying to do something like this is not appropriate - aka procedural. It does, however, still bugs the hell out of me. :) Maybe I should investigate using Reporting Services to handle this requirement. If anyone has any suggestions, please let me know.
View 6 Replies
View Related
Apr 15, 2005
Dear all, i have a challenging problem in writing a trigger here,,
here is the database script
-----------------------SCRIPT------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Categories]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Categories]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Categories_Properties]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Categories_Properties]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Properties]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Properties]
GO
CREATE TABLE [dbo].[Categories] (
[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ParentID] [int] NULL ,
[CategoryDescription] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MainCategory] [bit] NOT NULL ,
[HavePhoto] [bit] NOT NULL ,
[MaxPhotos] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[Categories_Properties] (
[Categories_PropertiesID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryID] [int] NOT NULL ,
[PropertyID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Properties] (
[PropertyID] [int] IDENTITY (1, 1) NOT NULL ,
[PropertyName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
------------------------END SCRIPT--------------------------
Categories and Categories_Properties are joined by CategoryID
Properties and Categories_Properties are joined by PropertyID
there is a relation between the table Categories and itself, the PrimaryKey is CategoryID, the ForiegnKey is ParentID
the problem is i need a trigger to fire when adding a record in Categories_Properties to add this property to all the children for this Category, for eexample if the categories tree
Autos >> Cars >> FIAT
when adding the Property BRAND to to Autos, this Property had to be inserted to Cars and FIAT too, i wrote some trigger but it adds this property to just the direct children, just to cars,
How to update it to include all the children in all levels till the leaf ones??
here is what i wrote
-----------------------Trigger-------------------------
CREATE TRIGGER [inherete_property] ON [dbo].[Categories_Properties]
FOR INSERT
AS
declare @CatID integer,@PropID integer
--set @CatID=select CategoryID from inserted
set @PropID=(select top 1(PropertyID) from inserted)
DECLARE cat_cursor CURSOR FOR
select CategoryID from Categories where ParentID in (select CategoryID from inserted)
open cat_cursor
fetch next from cat_cursor
into @CatID
WHILE @@FETCH_STATUS = 0
BEGIN
insert into categories_Properties(CategoryID,PropertyID) values (@CatID,@PropID)
FETCH NEXT FROM cat_cursor
INTO @CatID
END
CLOSE cat_cursor
DEALLOCATE cat_cursor
-----------------------End Trigger---------------------
thank you all
View 1 Replies
View Related
Mar 12, 1999
This problem occurs in a Visual Basic 5 application which accesses the SQL Server 6.5 database through Jet Engine(DAO).
If a user starts a transaction and then his application terminates abnormally (before rolling back or committing), say due to power failure or if the user kills the application with Ctrl+Alt+Delete, then the transaction started by him is not terminated (rolled back) by SQL Server. Even the process cannot be killed using the "Kill" command and the user keeps holding all the locks that he has already acquired. Also nobody can use Enterprise Manager. This happens when you have lock on more than 8 tables including tables in tempdb. The only way to solve this problem is to restart the server which is never expected in a multiuser system.
One can know the threadID of the process that needs to be killed using "Sysprocesses" system table. Is it possible to kill the thread in Windows NT?
Is there any setting due to which SQL SERVER will automatically kill a process or rollback a transaction started by a disconnected client ?
Is it possible that a process or transaction will have to release lock after the specified time ?
Is there any other way to tackle this problem?
-Gunvant Patil
gunvantp@mail.usa.com
View 2 Replies
View Related
Feb 19, 2006
How does one figure out, I mean compare the results after some changes have been done to the SQL Server, I mean the method in which the comparision is done, lets say I have a query I ran it it gave me the results in 40 seconds , I ran it again this time it took 30 seconds, then I again ran it it took 35 seconds, I then created an index , this time the query ran in 30 seconds ... how does one compare such things , I mean i need to give stats as too what kind of performace has takern place ... please helpo , I need to knw as a DBa how would you convince your maanger that becasue of some changes the performance has improved, cause when you ask the users they say its ok , we dont see the differnece & stuff , please help.
View 4 Replies
View Related
Jul 23, 2005
Table DDL:create table test(inId int primary key identity,inFK int not null,inSeq int not null,dtDate datetime)Data Insert:insert into testselect 1,1, getdate()WAITFOR DELAY '000:00:01'insert into testselect 1,1, getdate()WAITFOR DELAY '000:00:01'insert into testselect 1,1, getdate()WAITFOR DELAY '000:00:01'insert into testselect 2,1, getdate()WAITFOR DELAY '000:00:01'insert into testselect 2,1, getdate()WAITFOR DELAY '000:00:01'insert into testselect 3,1, getdate()WAITFOR DELAY '000:00:01'insert into testselect 4,1, getdate()If we select on this table:inId inFK inSeq dtDate----------- ----------- -----------------------------------------------------------------1 1 1 2005-02-01 12:54:40.9672 1 1 2005-02-01 12:54:41.9673 1 1 2005-02-01 12:54:42.9674 2 1 2005-02-01 12:54:43.9675 2 1 2005-02-01 12:54:44.9676 3 1 2005-02-01 12:54:45.9837 4 1 2005-02-01 12:54:47.077(7 row(s) affected)Problem:What I would like to do (using SQL and not a cursor) is to update thevalue of inSeq to its ordinal position, this will be based on the # ofoccurences of inFK. For Example, I would like to run a sql statementthat would transform the data to:update testset inSEQ = (some sql)select * from test - This would then produce:inId inFK inSeq dtDate----------- ----------- -----------------------------------------------------------------1 1 1 2005-02-01 12:54:40.9672 1 2 2005-02-01 12:54:41.9673 1 3 2005-02-01 12:54:42.9674 2 1 2005-02-01 12:54:43.9675 2 2 2005-02-01 12:54:44.9676 3 1 2005-02-01 12:54:45.9837 4 1 2005-02-01 12:54:47.077(7 row(s) affected)Any help would be greatly appreciated.TFD
View 17 Replies
View Related
