Help on Updategram

I am recieveing this error.
"Ambiguous delete, unique identifier required
Transaction aborted"
I am trying to delete several rows where a column equals
a certain value like "7". THe errors occurs because
SQLXML appears to only allow from a single row to be
deleted at a time. Here is what the Profiler shows:
SET XACT_ABORT ON
BEGIN TRAN
DECLARE @.eip INT, @.r__ int, @.e__ int
SET @.eip = 0
DELETE tblNote WHERE ( pkAccountID=6 ) ; SELECT @.e__ =
@.@.ERROR, @.r__ = @.@.ROWCOUNT
IF (@.e__ != 0 OR @.r__ != 1) SET @.eip = 1
IF (@.r__ > 1) RAISERROR ( N'SQLOLEDB Error Description:
Ambiguous delete, unique identifier required Transaction
aborted ', 16, 1)
ELSE IF (@.r__ < 1) RAISERROR ( N'SQLOLEDB Error
Description: Empty delete, no deletable rows found
Transaction aborted ', 16, 1)
IF (@.eip != 0) ROLLBACK ELSE COMMIT
SET XACT_ABORT OFF
WHY?!?!?!?!?!?!
Regards,
Ron
To delete multiple rows in an Updategram, you have to specify each row to be
deleted individually in the "before" element - there's no equivalent of a
wildcard delete.
From SQLXML 3.0 BOL:
"If an element that is specified in the updategram either matches more than
one row in the table or does not match any table row, the updategram returns
an error and cancels the entire <sync> block. Only one record at a time can
be deleted by an element in the updategram."
Hope that helps,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Ron Antinori" <rantinori@.bluestoneinfo.com> wrote in message
news:264601c48e1c$9a727f80$a301280a@.phx.gbl...
I am recieveing this error.
"Ambiguous delete, unique identifier required
Transaction aborted"
I am trying to delete several rows where a column equals
a certain value like "7". THe errors occurs because
SQLXML appears to only allow from a single row to be
deleted at a time. Here is what the Profiler shows:
SET XACT_ABORT ON
BEGIN TRAN
DECLARE @.eip INT, @.r__ int, @.e__ int
SET @.eip = 0
DELETE tblNote WHERE ( pkAccountID=6 ) ; SELECT @.e__ =
@.@.ERROR, @.r__ = @.@.ROWCOUNT
IF (@.e__ != 0 OR @.r__ != 1) SET @.eip = 1
IF (@.r__ > 1) RAISERROR ( N'SQLOLEDB Error Description:
Ambiguous delete, unique identifier required Transaction
aborted ', 16, 1)
ELSE IF (@.r__ < 1) RAISERROR ( N'SQLOLEDB Error
Description: Empty delete, no deletable rows found
Transaction aborted ', 16, 1)
IF (@.eip != 0) ROLLBACK ELSE COMMIT
SET XACT_ABORT OFF
WHY?!?!?!?!?!?!
Regards,
Ron

Help on update statement

We have the table called Organization. In this table is a
column called OutlineNum that indicates the hierarchy of
the organizations. This table also includes a flag to
indicate whether or not the organization is active. A
user can inactivate an organization, but ONLY when its
child(ren) are inactivated also. Since our apps code did
not do this at the time, we now may have bad data out
there.
Can anyone out there help me out on creating an update
statement to fix the data based on the OutlineNum and
ActiveFlag values? Below is the DDL for the Organization
table.
create table Organization
(
OrganizationID nvarchar(15) not null,
OrganizationName nvarchar(30) not null,
ActiveFlag int not null
default 1
constraint CK_Organization_ActiveFlag check
(ActiveFlag in (1,0)),
OutlineNum nvarchar(60) not null,
constraint PK_Organization primary key (OrganizationID)
)
go
Below is some sample data. You will have to tweak it to
put bad data in.
insert into Organization
values ('ORG1000', 'Organization - ORG1000',1,'1')
insert into Organization
values ('ORG1002', 'Organization - ORG1002', 1, '1.1')
insert into Organization
values ('ORG1003', 'Organization - ORG1003', 1, '1.2')
insert into Organization
values ('ORG1004', 'Organization - ORG1004', 1, '1.1.2')
insert into Organization
values ('ORG1005', 'Organization - ORG1005', 1, '1.1.3')
insert into Organization
values ('ORG1006', 'Organization - ORG1006', 1, '1.1.4')
insert into Organization
values ('ORG1007', 'Organization - ORG1007', 1, '1.2.1')
insert into Organization
values ('ORG1008', 'Organization - ORG1008', 1, '1.2.2')
insert into Organization
values ('ORG1009', 'Organization - ORG1009', 1, '1.2.3')
insert into Organization
values ('ORG1011', 'Organization - ORG1011', 1, '1.1.5')
insert into Organization
values ('ORG1012', 'Organization - ORG1012', 1, '1.2.4')
insert into Organization
values ('ORG1013', 'Organization - ORG1013', 1, '1.3')
insert into Organization
values ('ORG1014', 'Organization - ORG1014', 1, '1.2.5')
insert into Organization
values ('ORG1015', 'Organization - ORG1015', 1, '1.1.1')
Thanks in advance,
DeeDee,
I just spent a few mins but don't have a concrete solution right now.
Just FYI I created 2 SQLs to get my feet wet into the direction I was going.
I'm sure you will figure out what I'm doing with these SQLs.
I'm at work right now and don't want to spend any further time on this.
Will try and do it from home for ya.
Rgds,
Harman Sahni
select m.outlinenum as moutlinenum,
c.outlinenum as coutlinenum,
c.activeflag as cactiveflag
from organization m,
organization c
where m.outlinenum = substring(c.outlinenum,1,len(m.outlinenum))
order by 1,2
select c.outlinenum as coutlinenum,
m.outlinenum as moutlinenum,
c.activeflag as cactiveflag
from organization m,
organization c
where substring(c.outlinenum,1,len(c.outlinenum)-2) = m.outlinenum and
len(c.outlinenum) > 2
order by len(c.outlinenum), len(m.outlinenum), 1,2
"dee" <anonymous@.discussions.microsoft.com> wrote in message
news:9bf801c49757$031a9840$a601280a@.phx.gbl...
> We have the table called Organization. In this table is a
> column called OutlineNum that indicates the hierarchy of
> the organizations. This table also includes a flag to
> indicate whether or not the organization is active. A
> user can inactivate an organization, but ONLY when its
> child(ren) are inactivated also. Since our apps code did
> not do this at the time, we now may have bad data out
> there.
> Can anyone out there help me out on creating an update
> statement to fix the data based on the OutlineNum and
> ActiveFlag values? Below is the DDL for the Organization
> table.
> create table Organization
> (
> OrganizationID nvarchar(15) not null,
> OrganizationName nvarchar(30) not null,
> ActiveFlag int not null
> default 1
> constraint CK_Organization_ActiveFlag check
> (ActiveFlag in (1,0)),
> OutlineNum nvarchar(60) not null,
> constraint PK_Organization primary key (OrganizationID)
> )
> go
> Below is some sample data. You will have to tweak it to
> put bad data in.
> insert into Organization
> values ('ORG1000', 'Organization - ORG1000',1,'1')
> insert into Organization
> values ('ORG1002', 'Organization - ORG1002', 1, '1.1')
> insert into Organization
> values ('ORG1003', 'Organization - ORG1003', 1, '1.2')
> insert into Organization
> values ('ORG1004', 'Organization - ORG1004', 1, '1.1.2')
> insert into Organization
> values ('ORG1005', 'Organization - ORG1005', 1, '1.1.3')
> insert into Organization
> values ('ORG1006', 'Organization - ORG1006', 1, '1.1.4')
> insert into Organization
> values ('ORG1007', 'Organization - ORG1007', 1, '1.2.1')
> insert into Organization
> values ('ORG1008', 'Organization - ORG1008', 1, '1.2.2')
> insert into Organization
> values ('ORG1009', 'Organization - ORG1009', 1, '1.2.3')
> insert into Organization
> values ('ORG1011', 'Organization - ORG1011', 1, '1.1.5')
> insert into Organization
> values ('ORG1012', 'Organization - ORG1012', 1, '1.2.4')
> insert into Organization
> values ('ORG1013', 'Organization - ORG1013', 1, '1.3')
> insert into Organization
> values ('ORG1014', 'Organization - ORG1014', 1, '1.2.5')
> insert into Organization
> values ('ORG1015', 'Organization - ORG1015', 1, '1.1.1')
> Thanks in advance,
> Dee|||Oh by the way, in my SQLs
c is for children
m is for master
"dee" <anonymous@.discussions.microsoft.com> wrote in message
news:9bf801c49757$031a9840$a601280a@.phx.gbl...
> We have the table called Organization. In this table is a
> column called OutlineNum that indicates the hierarchy of
> the organizations. This table also includes a flag to
> indicate whether or not the organization is active. A
> user can inactivate an organization, but ONLY when its
> child(ren) are inactivated also. Since our apps code did
> not do this at the time, we now may have bad data out
> there.
> Can anyone out there help me out on creating an update
> statement to fix the data based on the OutlineNum and
> ActiveFlag values? Below is the DDL for the Organization
> table.
> create table Organization
> (
> OrganizationID nvarchar(15) not null,
> OrganizationName nvarchar(30) not null,
> ActiveFlag int not null
> default 1
> constraint CK_Organization_ActiveFlag check
> (ActiveFlag in (1,0)),
> OutlineNum nvarchar(60) not null,
> constraint PK_Organization primary key (OrganizationID)
> )
> go
> Below is some sample data. You will have to tweak it to
> put bad data in.
> insert into Organization
> values ('ORG1000', 'Organization - ORG1000',1,'1')
> insert into Organization
> values ('ORG1002', 'Organization - ORG1002', 1, '1.1')
> insert into Organization
> values ('ORG1003', 'Organization - ORG1003', 1, '1.2')
> insert into Organization
> values ('ORG1004', 'Organization - ORG1004', 1, '1.1.2')
> insert into Organization
> values ('ORG1005', 'Organization - ORG1005', 1, '1.1.3')
> insert into Organization
> values ('ORG1006', 'Organization - ORG1006', 1, '1.1.4')
> insert into Organization
> values ('ORG1007', 'Organization - ORG1007', 1, '1.2.1')
> insert into Organization
> values ('ORG1008', 'Organization - ORG1008', 1, '1.2.2')
> insert into Organization
> values ('ORG1009', 'Organization - ORG1009', 1, '1.2.3')
> insert into Organization
> values ('ORG1011', 'Organization - ORG1011', 1, '1.1.5')
> insert into Organization
> values ('ORG1012', 'Organization - ORG1012', 1, '1.2.4')
> insert into Organization
> values ('ORG1013', 'Organization - ORG1013', 1, '1.3')
> insert into Organization
> values ('ORG1014', 'Organization - ORG1014', 1, '1.2.5')
> insert into Organization
> values ('ORG1015', 'Organization - ORG1015', 1, '1.1.1')
> Thanks in advance,
> Dee|||Hello Dee
Not knowing finer details of what you are wanting to achieve, I have put
togather a basic cursor that may achieve this for you. Be warned that
running the UPDATE statement(s) outside of a transaction may end up
updating data that is not intended to be updated. I would recommend for you
to back the database up before running any of the commands from the script
below.
--BEGIN TRAN
declare @.active char(1)
declare @.inactive char(1)
declare @.outline varchar(20)
set @.active = 1 --define the value for which you would like to delete
set @.inactive = 0
declare c1 cursor for
select distinct outlinenum
from organization
where activeflag = @.active and len(outlinenum) = 3 --assuming that parent
that was mistakenly updated has length of 3 eg. 1.1 is considered the
parent.
open c1
fetch next from c1 into @.outline
while @.@.fetch_status = 0
begin
print 'update organization set outlinenum = ' + '''' + @.inactive + '''' +
'where outline like ' + '''' + @.outline + '%' + '''' --check the statements
to see if this meets the goals of what you want to do
--update organization set outlinenum = @.inactive where outline like
@.outline + '%'
fetch next from c1 into @.outline
end
close c1
deallocate c1
--COMMIT TRAN
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Thank you both Harman and Pankaj! You both definitely
pointed me to the right direction. Both of your ideas
worked like a charm.
>--Original Message--
>Dee,
>I just spent a few mins but don't have a concrete
solution right now.
>Just FYI I created 2 SQLs to get my feet wet into the
direction I was going.
>I'm sure you will figure out what I'm doing with these
SQLs.
>I'm at work right now and don't want to spend any further
time on this.
>Will try and do it from home for ya.
>Rgds,
>Harman Sahni
>
>select m.outlinenum as moutlinenum,
> c.outlinenum as coutlinenum,
> c.activeflag as cactiveflag
>from organization m,
> organization c
>where m.outlinenum = substring(c.outlinenum,1,len
(m.outlinenum))
>order by 1,2
>select c.outlinenum as coutlinenum,
> m.outlinenum as moutlinenum,
> c.activeflag as cactiveflag
>from organization m,
> organization c
>where substring(c.outlinenum,1,len(c.outlinenum)-2) =m.outlinenum and
>len(c.outlinenum) > 2
>order by len(c.outlinenum), len(m.outlinenum), 1,2
>
>"dee" <anonymous@.discussions.microsoft.com> wrote in
message
>news:9bf801c49757$031a9840$a601280a@.phx.gbl...
>> We have the table called Organization. In this table
is a
>> column called OutlineNum that indicates the hierarchy of
>> the organizations. This table also includes a flag to
>> indicate whether or not the organization is active. A
>> user can inactivate an organization, but ONLY when its
>> child(ren) are inactivated also. Since our apps code
did
>> not do this at the time, we now may have bad data out
>> there.
>> Can anyone out there help me out on creating an update
>> statement to fix the data based on the OutlineNum and
>> ActiveFlag values? Below is the DDL for the
Organization
>> table.
>> create table Organization
>> (
>> OrganizationID nvarchar(15) not
null,
>> OrganizationName nvarchar(30) not
null,
>> ActiveFlag int not
null
>> default 1
>> constraint CK_Organization_ActiveFlag check
>> (ActiveFlag in (1,0)),
>> OutlineNum nvarchar(60) not
null,
>> constraint PK_Organization primary key
(OrganizationID)
>> )
>> go
>> Below is some sample data. You will have to tweak it to
>> put bad data in.
>> insert into Organization
>> values ('ORG1000', 'Organization - ORG1000',1,'1')
>> insert into Organization
>> values ('ORG1002', 'Organization - ORG1002', 1, '1.1')
>> insert into Organization
>> values ('ORG1003', 'Organization - ORG1003', 1, '1.2')
>> insert into Organization
>> values ('ORG1004', 'Organization - ORG1004', 1, '1.1.2')
>> insert into Organization
>> values ('ORG1005', 'Organization - ORG1005', 1, '1.1.3')
>> insert into Organization
>> values ('ORG1006', 'Organization - ORG1006', 1, '1.1.4')
>> insert into Organization
>> values ('ORG1007', 'Organization - ORG1007', 1, '1.2.1')
>> insert into Organization
>> values ('ORG1008', 'Organization - ORG1008', 1, '1.2.2')
>> insert into Organization
>> values ('ORG1009', 'Organization - ORG1009', 1, '1.2.3')
>> insert into Organization
>> values ('ORG1011', 'Organization - ORG1011', 1, '1.1.5')
>> insert into Organization
>> values ('ORG1012', 'Organization - ORG1012', 1, '1.2.4')
>> insert into Organization
>> values ('ORG1013', 'Organization - ORG1013', 1, '1.3')
>> insert into Organization
>> values ('ORG1014', 'Organization - ORG1014', 1, '1.2.5')
>> insert into Organization
>> values ('ORG1015', 'Organization - ORG1015', 1, '1.1.1')
>> Thanks in advance,
>> Dee
>
>.
>

Help on scheduling a data change ??

Dear all,
I have a table with a certain amount of data. One of the data column is
named STATUS. by default all entry have a status of 5 (which means for the
client application a waiting states)
I need to schedule the change of this status from 5 to 1 automatically.
For example I would like that status value for all records with 5 is chaged
to 1 on a particular date and time.
How can I do that ?
regards
sergeHi
UPDATE Table SET status=1 WHERE status =1
AND GETDATE()>='20050101' AND GETDATE() <'20050201'
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:BDA2B6B7-B9EF-4D79-A836-AAD0EE84B783@.microsoft.com...
> Dear all,
> I have a table with a certain amount of data. One of the data column is
> named STATUS. by default all entry have a status of 5 (which means for the
> client application a waiting states)
> I need to schedule the change of this status from 5 to 1 automatically.
> For example I would like that status value for all records with 5 is
chaged
> to 1 on a particular date and time.
> How can I do that ?
> regards
> serge|||Correction
Should be
UPDATE Table SET status=5 WHERE status =1
......
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OkOhaZQjFHA.3148@.TK2MSFTNGP09.phx.gbl...
> Hi
> UPDATE Table SET status=1 WHERE status =1
> AND GETDATE()>='20050101' AND GETDATE() <'20050201'
> "serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
> message news:BDA2B6B7-B9EF-4D79-A836-AAD0EE84B783@.microsoft.com...
the
> chaged
>|||May be you should check out "Scheduling Jobs" in BOL.
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:BDA2B6B7-B9EF-4D79-A836-AAD0EE84B783@.microsoft.com...
> Dear all,
> I have a table with a certain amount of data. One of the data column is
> named STATUS. by default all entry have a status of 5 (which means for the
> client application a waiting states)
> I need to schedule the change of this status from 5 to 1 automatically.
> For example I would like that status value for all records with 5 is
chaged
> to 1 on a particular date and time.
> How can I do that ?
> regards
> serge|||Thnaks for the tip.
Where should I store that SQL querry ?
Does it have to be in a store procedure and then run that store procedure
from a schedule task ?
"Uri Dimant" wrote:

> Correction
> Should be
> UPDATE Table SET status=5 WHERE status =1
> .......
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OkOhaZQjFHA.3148@.TK2MSFTNGP09.phx.gbl...
> the
>
>|||>Does it have to be in a store >procedure and then run that store >procedure
>from a schedule task ?
Yes.
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:EE63176F-C87F-4BEA-A8FB-4D18A47C7959@.microsoft.com...
> Thnaks for the tip.
> Where should I store that SQL querry ?
> Does it have to be in a store procedure and then run that store procedure
> from a schedule task ?
>
> "Uri Dimant" wrote:
>
is
for
automatically.|||Hi,
If I use the task scheduler to run the store procedure, I do not need the
GETDATE function, the UPDATE is enough right ?
"Uri Dimant" wrote:

> Yes.
> "serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
> message news:EE63176F-C87F-4BEA-A8FB-4D18A47C7959@.microsoft.com...
> is
> for
> automatically.
>
>|||You could just store the creation date in the table and then derive the
Status from the date when you query the data.
David Portas
SQL Server MVP
--|||That was the idea I get at first.
Something like storing the desire date/Time when the status needs to be
changed and then update teh status when the date/time field is corresponding
to current date.
But then if I do that I can use different methode to check this date.
Either I can do it in my client VB application or on the server itself.
But if I do it on the server side what shoudl I use to pol the current date
and check if it correspond to the one store in the table and then if it is
update the field.
Can we make some loop in server procedure ?
Sorry I am new n that and could not get the proper way
thnaks for your help
serge
"David Portas" wrote:

> You could just store the creation date in the table and then derive the
> Status from the date when you query the data.
> --
> David Portas
> SQL Server MVP
> --
>|||What do you mean by derive the status '
by the way I have try to run a script from a new job that I schedule at a
certain time. If I run the script from the querry analyser it works fine, bu
t
when I put it as a new job and load the script file to execute, the set the
time to schedule it, it is not updating or may be not runing at all.
Script is :
EXEC dbo.UpdateStatus
GO
and UpdateStatus is a store procedure
Any idea?
thaks
"David Portas" wrote:

> You could just store the creation date in the table and then derive the
> Status from the date when you query the data.
> --
> David Portas
> SQL Server MVP
> --
>

help on query

Hi,
I don't know if this is durable without cursor.
I have following record set: TABLEA have one column "Date"
All the dates are in order DESC.
Assuming every year need to have 4 quarters,
however in this example 1995 year only have 3 quarters ( missing one quarter
- 1995-06-30)
I want to write a query against this table to find out the missing quarter's
year, in this case, it's 1995
how can I do that?
Date
--
1999-12-31 00:00:00.000
1999-09-30 00:00:00.000
1999-06-30 00:00:00.000
1999-03-31 00:00:00.000
1998-12-31 00:00:00.000
1998-09-30 00:00:00.000
1998-06-30 00:00:00.000
1998-03-31 00:00:00.000
1997-12-31 00:00:00.000
1997-09-30 00:00:00.000
1997-06-30 00:00:00.000
1997-03-31 00:00:00.000
1996-12-31 00:00:00.000
1996-09-30 00:00:00.000
1996-06-30 00:00:00.000
1996-03-31 00:00:00.000
1995-12-31 00:00:00.000
1995-09-30 00:00:00.000
1995-03-31 00:00:00.000
1994-12-31 00:00:00.000
1994-09-30 00:00:00.000
1994-06-30 00:00:00.000
1994-03-31 00:00:00.000
1993-12-31 00:00:00.000
1993-09-30 00:00:00.000
1993-06-30 00:00:00.000
1993-03-31 00:00:00.000
1992-12-31 00:00:00.000
1992-09-30 00:00:00.000
1992-06-30 00:00:00.000
1992-03-31 00:00:00.000
1991-12-31 00:00:00.000
1991-09-30 00:00:00.000
1991-06-30 00:00:00.000
1991-03-31 00:00:00.000
1990-12-31 00:00:00.000
1990-09-30 00:00:00.000
1990-06-30 00:00:00.000
1990-03-31 00:00:00.000If you use a calendar table, you can join the two tables to find the
missing rows. The calendar table should have every quarter from every
year.
David Gugick
Quest Software
www.imceda.com
www.quest.com
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:eZtpTiqoFHA.1048@.tk2msftngp13.phx.gbl...
Hi,
I don't know if this is durable without cursor.
I have following record set: TABLEA have one column "Date"
All the dates are in order DESC.
Assuming every year need to have 4 quarters,
however in this example 1995 year only have 3 quarters ( missing one
quarter- 1995-06-30)
I want to write a query against this table to find out the missing
quarter's year, in this case, it's 1995
how can I do that?
Date
--
1999-12-31 00:00:00.000
1999-09-30 00:00:00.000
1999-06-30 00:00:00.000
1999-03-31 00:00:00.000
1998-12-31 00:00:00.000
1998-09-30 00:00:00.000
1998-06-30 00:00:00.000
1998-03-31 00:00:00.000
1997-12-31 00:00:00.000
1997-09-30 00:00:00.000
1997-06-30 00:00:00.000
1997-03-31 00:00:00.000
1996-12-31 00:00:00.000
1996-09-30 00:00:00.000
1996-06-30 00:00:00.000
1996-03-31 00:00:00.000
1995-12-31 00:00:00.000
1995-09-30 00:00:00.000
1995-03-31 00:00:00.000
1994-12-31 00:00:00.000
1994-09-30 00:00:00.000
1994-06-30 00:00:00.000
1994-03-31 00:00:00.000
1993-12-31 00:00:00.000
1993-09-30 00:00:00.000
1993-06-30 00:00:00.000
1993-03-31 00:00:00.000
1992-12-31 00:00:00.000
1992-09-30 00:00:00.000
1992-06-30 00:00:00.000
1992-03-31 00:00:00.000
1991-12-31 00:00:00.000
1991-09-30 00:00:00.000
1991-06-30 00:00:00.000
1991-03-31 00:00:00.000
1990-12-31 00:00:00.000
1990-09-30 00:00:00.000
1990-06-30 00:00:00.000
1990-03-31 00:00:00.000|||if you have and @.@.identity this will give you above which you are missing
quarter.
that should be in the order.Try this
SELECT p1.IDNO
FROM dbo.Table1 p INNER JOIN dbo.Table1 p1 ON p.IDNO = p1.IDNO
where DATEDIFF(MONTH,p.DATE,(SELECT p1.[DATE] FROM Table1 p1 WHERE
p.IDNO = P1.IDNO + 1 )) > 3
Regards
R.D
"David Gugick" wrote:

> If you use a calendar table, you can join the two tables to find the
> missing rows. The calendar table should have every quarter from every
> year.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
> "Britney" <britneychen_2001@.yahoo.com> wrote in message
> news:eZtpTiqoFHA.1048@.tk2msftngp13.phx.gbl...
> Hi,
> I don't know if this is durable without cursor.
> I have following record set: TABLEA have one column "Date"
> All the dates are in order DESC.
> Assuming every year need to have 4 quarters,
> however in this example 1995 year only have 3 quarters ( missing one
> quarter- 1995-06-30)
> I want to write a query against this table to find out the missing
> quarter's year, in this case, it's 1995
> how can I do that?
>
> Date
> --
> 1999-12-31 00:00:00.000
> 1999-09-30 00:00:00.000
> 1999-06-30 00:00:00.000
> 1999-03-31 00:00:00.000
> 1998-12-31 00:00:00.000
> 1998-09-30 00:00:00.000
> 1998-06-30 00:00:00.000
> 1998-03-31 00:00:00.000
> 1997-12-31 00:00:00.000
> 1997-09-30 00:00:00.000
> 1997-06-30 00:00:00.000
> 1997-03-31 00:00:00.000
> 1996-12-31 00:00:00.000
> 1996-09-30 00:00:00.000
> 1996-06-30 00:00:00.000
> 1996-03-31 00:00:00.000
> 1995-12-31 00:00:00.000
> 1995-09-30 00:00:00.000
> 1995-03-31 00:00:00.000
> 1994-12-31 00:00:00.000
> 1994-09-30 00:00:00.000
> 1994-06-30 00:00:00.000
> 1994-03-31 00:00:00.000
> 1993-12-31 00:00:00.000
> 1993-09-30 00:00:00.000
> 1993-06-30 00:00:00.000
> 1993-03-31 00:00:00.000
> 1992-12-31 00:00:00.000
> 1992-09-30 00:00:00.000
> 1992-06-30 00:00:00.000
> 1992-03-31 00:00:00.000
> 1991-12-31 00:00:00.000
> 1991-09-30 00:00:00.000
> 1991-06-30 00:00:00.000
> 1991-03-31 00:00:00.000
> 1990-12-31 00:00:00.000
> 1990-09-30 00:00:00.000
> 1990-06-30 00:00:00.000
> 1990-03-31 00:00:00.000
>|||I Mean IDENTITY COLUMN. If you dont have one, you can generate on the fly.
"R.D" wrote:
> if you have and @.@.identity this will give you above which you are missing
> quarter.
> that should be in the order.Try this
> SELECT p1.IDNO
> FROM dbo.Table1 p INNER JOIN dbo.Table1 p1 ON p.IDNO = p1.IDNO
> where DATEDIFF(MONTH,p.DATE,(SELECT p1.[DATE] FROM Table1 p1 WHERE
> p.IDNO = P1.IDNO + 1 )) > 3
> Regards
> R.D
> "David Gugick" wrote:
>

help on query

Hi,
My table has a column [account type], some rows have null value, when I
query it to eliminate some account type like accout != 'A', all the rows wit
h
null account won't showup either. I would like all the accounts other than
'A' show. How can I write it? ThanksYou could:
select * from TheTable
where isnull( [account type], ''') <> 'A'
Bryce|||...
Where account type Is Null OR account type <> 'A'
"Jen" wrote:

> Hi,
> My table has a column [account type], some rows have null value, when I
> query it to eliminate some account type like accout != 'A', all the rows w
ith
> null account won't showup either. I would like all the accounts other than
> 'A' show. How can I write it? Thanks|||Try,
select * from your_table
where [account] != 'A' or [account] is null
AMB
"Jen" wrote:

> Hi,
> My table has a column [account type], some rows have null value, when I
> query it to eliminate some account type like accout != 'A', all the rows w
ith
> null account won't showup either. I would like all the accounts other than
> 'A' show. How can I write it? Thanks

Help on Partitioning column was not found.

Hi,

I don't know if I missed anything. I have 2 member tables and one
partition view in SQL 2000 defined as following

CREATE VIEW Server1.dbo.UTable
AS
SELECT*
FROMServer1..pTable1
UNION ALL
SELECT*
FROMServer2..pTable2

CREATE TABLE pTable1 (
[ID1] [int] IDENTITY (1000, 2) NOT NULL ,
[ID2] [int] NOT NULL ,

...<other columns>.......

CONSTRAINT [PK_tblLot] PRIMARY KEY CLUSTERED
(
[ID1],
[ID2]
) ON [PRIMARY] ,
CHECK ([ID2] = 1015)
) ON [PRIMARY]

CREATE TABLE [pTable2] (
[ID1] [int] IDENTITY (1001, 2) NOT NULL ,
[ID2] [int] NOT NULL ,

...<other columns>.......

CONSTRAINT [PK_tblLot] PRIMARY KEY NONCLUSTERED
(
[ID1],
[ID2]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CHECK ([ID2] <1015)
) ON [PRIMARY]

SELECT is working fine. However, I got error message if I issue an
update command such as

UPDATE UTable
SET somecol = someval
Where somecol2 = somecond

Server: Msg 4436, Level 16, State 12, Line 1
UNION ALL view 'UTable' is not updatable because a partitioning column
was not found.

Anyone have any idea? ID2 is my partition column, why the SQL 2K
doesn't see it. It is a part of primary key, having checking
constrain, and no other constrain on it. Am I missing something?

Thanks a lot.You cannot have identity columns in an updatable partitioned view.

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Sonny" <SonnyKMI@.gmail.comwrote in message
news:1180643932.644398.247270@.g37g2000prf.googlegr oups.com...
Hi,

I don't know if I missed anything. I have 2 member tables and one
partition view in SQL 2000 defined as following

CREATE VIEW Server1.dbo.UTable
AS
SELECT *
FROM Server1..pTable1
UNION ALL
SELECT *
FROM Server2..pTable2

CREATE TABLE pTable1 (
[ID1] [int] IDENTITY (1000, 2) NOT NULL ,
[ID2] [int] NOT NULL ,

...<other columns>.......

CONSTRAINT [PK_tblLot] PRIMARY KEY CLUSTERED
(
[ID1],
[ID2]
) ON [PRIMARY] ,
CHECK ([ID2] = 1015)
) ON [PRIMARY]

CREATE TABLE [pTable2] (
[ID1] [int] IDENTITY (1001, 2) NOT NULL ,
[ID2] [int] NOT NULL ,

...<other columns>.......

CONSTRAINT [PK_tblLot] PRIMARY KEY NONCLUSTERED
(
[ID1],
[ID2]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CHECK ([ID2] <1015)
) ON [PRIMARY]

SELECT is working fine. However, I got error message if I issue an
update command such as

UPDATE UTable
SET somecol = someval
Where somecol2 = somecond

Server: Msg 4436, Level 16, State 12, Line 1
UNION ALL view 'UTable' is not updatable because a partitioning column
was not found.

Anyone have any idea? ID2 is my partition column, why the SQL 2K
doesn't see it. It is a part of primary key, having checking
constrain, and no other constrain on it. Am I missing something?

Thanks a lot.|||On May 31, 4:17 pm, "Tom Moreau" <t...@.dont.spam.me.cips.cawrote:

Quote:

Originally Posted by

You cannot have identity columns in an updatable partitioned view.
>
--
Tom
>
----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
>

In that case, how should I deal with the ID1? I need that column to
be an identity column. Thanks.|||Consider putting an INSTEAD OF trigger on the partitioned view.

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Sonny" <SonnyKMI@.gmail.comwrote in message
news:1180647134.671664.320360@.a26g2000pre.googlegr oups.com...
On May 31, 4:17 pm, "Tom Moreau" <t...@.dont.spam.me.cips.cawrote:

Quote:

Originally Posted by

You cannot have identity columns in an updatable partitioned view.
>
--
Tom
>
----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
>

In that case, how should I deal with the ID1? I need that column to
be an identity column. Thanks.|||Sonny (SonnyKMI@.gmail.com) writes:

Quote:

Originally Posted by

Anyone have any idea? ID2 is my partition column, why the SQL 2K
doesn't see it. It is a part of primary key, having checking
constrain, and no other constrain on it. Am I missing something?

Yes, <is not a permitted operator. You need to rewrite

CHECK ([ID2] <1015)

to

CHECK ([ID2] < 1015 OR [ID2] 1015)

Another story is whether this view will be very efficient. You should
probably add an index on ID2, or put it first in the primary key.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Sonny (SonnyKMI@.gmail.com) writes:

Quote:

Originally Posted by

In that case, how should I deal with the ID1? I need that column to
be an identity column. Thanks.

Oh, I should have added the the IDENTITY appears to work fine, as soon
as I had changed the CHECK constraint.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On May 31, 4:58 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Sonny (Sonny...@.gmail.com) writes:

Quote:

Originally Posted by

In that case, how should I deal with the ID1? I need that column to
be an identity column. Thanks.

>
Oh, I should have added the the IDENTITY appears to work fine, as soon
as I had changed the CHECK constraint.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thanks for all your help. I changed CHECK constraint, and now it is
not complaining about missing partition column anymore, however, when
do the Update or Insert it gives out Server: Msg 4450, Level 16, State
1, Line 1
Cannot update partitioned view 'UTable' because the definition of the
view column 'ID1' in table '[pTable1]' has a IDENTITY constraint. So
I think IDENTITY is the another issue. As Tom mentioned in his post,
using INSTEAD OF trigger, would anyone please give me an example,
never used before.

Again, thank you very much for your help.|||Check out:

http://msdn2.microsoft.com/en-us/li...18(SQL.80).aspx
--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Sonny" <SonnyKMI@.gmail.comwrote in message
news:1180703291.252760.209290@.a26g2000pre.googlegr oups.com...
On May 31, 4:58 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Sonny (Sonny...@.gmail.com) writes:

Quote:

Originally Posted by

In that case, how should I deal with the ID1? I need that column to
be an identity column. Thanks.

>
Oh, I should have added the the IDENTITY appears to work fine, as soon
as I had changed the CHECK constraint.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005
athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000
athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thanks for all your help. I changed CHECK constraint, and now it is
not complaining about missing partition column anymore, however, when
do the Update or Insert it gives out Server: Msg 4450, Level 16, State
1, Line 1
Cannot update partitioned view 'UTable' because the definition of the
view column 'ID1' in table '[pTable1]' has a IDENTITY constraint. So
I think IDENTITY is the another issue. As Tom mentioned in his post,
using INSTEAD OF trigger, would anyone please give me an example,
never used before.

Again, thank you very much for your help.|||On Jun 1, 8:12 am, "Tom Moreau" <t...@.dont.spam.me.cips.cawrote:

Quote:

Originally Posted by

Check out:
>
http://msdn2.microsoft.com/en-us/li...18(SQL.80).aspx
>
--
Tom
>
----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
>
"Sonny" <Sonny...@.gmail.comwrote in message
>
news:1180703291.252760.209290@.a26g2000pre.googlegr oups.com...
On May 31, 4:58 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:
>

Quote:

Originally Posted by

Sonny (Sonny...@.gmail.com) writes:

Quote:

Originally Posted by

In that case, how should I deal with the ID1? I need that column to
be an identity column. Thanks.

>

Quote:

Originally Posted by

Oh, I should have added the the IDENTITY appears to work fine, as soon
as I had changed the CHECK constraint.

>

Quote:

Originally Posted by

--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se

>

Quote:

Originally Posted by

Books Online for SQL Server 2005
athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000
athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

>
Thanks for all your help. I changed CHECK constraint, and now it is
not complaining about missing partition column anymore, however, when
do the Update or Insert it gives out Server: Msg 4450, Level 16, State
1, Line 1
Cannot update partitioned view 'UTable' because the definition of the
view column 'ID1' in table '[pTable1]' has a IDENTITY constraint. So
I think IDENTITY is the another issue. As Tom mentioned in his post,
using INSTEAD OF trigger, would anyone please give me an example,
never used before.
>
Again, thank you very much for your help.

Thank you so much!!sql

Help on one colum

Hi,

I have a column in SQL that needs to be created. This column needs to start with 10 zero's. If I add a number, say 135 the column needs to read : 0000000135.

Is this possible? If it is how do I need to go about to do this. If I need to create a view or something like that I do not mind. I am open to all suggestions and appreciate the help.

Use the following query..

Select Replicate('0',10-len(Convert(Varchar,ColumnName))) + Convert(Varchar,ColumnName) From TableName

|||

DECLARE @.AnyStringNo NVARCHAR(10)
SET @.AnyStringNo = '135'
SELECT REPLICATE(0,10-LEN(@.AnyStringNo)) + @.AnyStringNo
SET @.AnyStringNo = '47593'
SELECT REPLICATE(0,10-LEN(@.AnyStringNo)) + @.AnyStringNo

Will give ever output in 10 digits with replicating of 0s.

|||

Thanks Mani,

This is exactly what I need.

|||

Thanks Bhudev,

This was also very helpful!!

|||

One more question Mani,

If you have a currency field with the following : 1.35 it imports the . as well. What do I need to do to get the . out of the query?

|||This one should remove the dot and pad the necessary 0s in front.

SELECT RIGHT('0000000000' + REPLACE(CONVERT(VARCHAR(10),col1),'.', '') , 10) FROM yourTable|||

Thanks Limno,

That is exactly what I wanted!

|||You should do the formatting on the client-side. It is much easier, simpler and flexible. So return the ID as integer to the client and let it to do the formatting as required. This also provides better performance since you will be sending less data from the server to the client(s) over the network.|||

Umachandar,

Forgive me, but I am kinda new to VB programming. How do I get the client side to do the formatting?

help on multiple column updates - backtracking data for 30 days

i need to back track data should there be changes for the last 30 days.
help!
thank you!misheL wrote:

> i need to back track data should there be changes for the last 30
> days.
Are you seriously expecting many posters to respond and take the time
to answer your question considering the amount of time you have put
into formulating your question?
Kind regards,
Stijn Verrept.|||On Tue, 13 Dec 2005 17:30:02 -0800, misheL wrote:

>i need to back track data should there be changes for the last 30 days.
>help!
>thank you!
Hi misheL,
Please provide better specifications. See www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

help on insert a record on sql server with identity column as key

Hi, All:
Please help. I use sql server as back end and Access 2003 as front end
(everything is DAO).
A table on SQL server has an identity column as the key.
We have trouble on adding records to this table using the following SQL.
strSQL = "INSERT INTO myTableOnSQLServer (A, B, C, D, E) SELECT A, B, C, D,
E FROM myTableonAccessLocal"
db.execute strSQL
The schema of the table "myTableOnSQLServer" and the schema of the table
"myTableonAccessLocal" are all the same except that the "myTableOnSQLServer"
has an identity column (ID). The key of the "myTableOnSQLServer" is "ID" and
the table "myTableonAccessLocal" does not have a key.
When we try to run the query, it gives errors indicating the key is violated
or missing.
Should I figure out the autonumber for it first and then add to the SQL
server table?
Many thanks,
HS
"Hongyu Sun" wrote:

> Hi, All:
> Please help. I use sql server as back end and Access 2003 as front end
> (everything is DAO).
> A table on SQL server has an identity column as the key.
> We have trouble on adding records to this table using the following SQL.
> strSQL = "INSERT INTO myTableOnSQLServer (A, B, C, D, E) SELECT A, B, C, D,
> E FROM myTableonAccessLocal"
> db.execute strSQL
> The schema of the table "myTableOnSQLServer" and the schema of the table
> "myTableonAccessLocal" are all the same except that the "myTableOnSQLServer"
> has an identity column (ID). The key of the "myTableOnSQLServer" is "ID" and
> the table "myTableonAccessLocal" does not have a key.
> When we try to run the query, it gives errors indicating the key is violated
> or missing.
> Should I figure out the autonumber for it first and then add to the SQL
> server table?
> Many thanks,
> HS
As a common an identity column generates values by itself. If you need to
insert values into an identity column use this command:
SET IDENTITY_INSERT myTableOnSQLServer ON
After the insert has been completed issue the following statement:
SET IDENTITY_INSERT myTableOnSQLServer OFF
Good luck

help on insert a record on sql server with identity column as key

Hi, All:
Please help. I use sql server as back end and Access 2003 as front end
(everything is DAO).
A table on SQL server has an identity column as the key.
We have trouble on adding records to this table using the following SQL.
strSQL = "INSERT INTO myTableOnSQLServer (A, B, C, D, E) SELECT A, B, C, D,
E FROM myTableonAccessLocal"
db.execute strSQL
The schema of the table "myTableOnSQLServer" and the schema of the table
"myTableonAccessLocal" are all the same except that the "myTableOnSQLServer"
has an identity column (ID). The key of the "myTableOnSQLServer" is "ID" and
the table "myTableonAccessLocal" does not have a key.
When we try to run the query, it gives errors indicating the key is violated
or missing.
Should I figure out the autonumber for it first and then add to the SQL
server table?
Many thanks,
HS"Hongyu Sun" wrote:

> Hi, All:
> Please help. I use sql server as back end and Access 2003 as front end
> (everything is DAO).
> A table on SQL server has an identity column as the key.
> We have trouble on adding records to this table using the following SQL.
> strSQL = "INSERT INTO myTableOnSQLServer (A, B, C, D, E) SELECT A, B, C, D
,
> E FROM myTableonAccessLocal"
> db.execute strSQL
> The schema of the table "myTableOnSQLServer" and the schema of the table
> "myTableonAccessLocal" are all the same except that the "myTableOnSQLServe
r"
> has an identity column (ID). The key of the "myTableOnSQLServer" is "ID" a
nd
> the table "myTableonAccessLocal" does not have a key.
> When we try to run the query, it gives errors indicating the key is violat
ed
> or missing.
> Should I figure out the autonumber for it first and then add to the SQL
> server table?
> Many thanks,
> HS
As a common an identity column generates values by itself. If you need to
insert values into an identity column use this command:
SET IDENTITY_INSERT myTableOnSQLServer ON
After the insert has been completed issue the following statement:
SET IDENTITY_INSERT myTableOnSQLServer OFF
Good lucksql

Help needed regarding the procedure

Hi All

I am new to sqlserver.I got the doubt when i want to perform a task.Need yours advice

In a table i am having a column email with mailids. most of the rows are with the value email@.email.com.
My requirement is i want to change the value email@.email.com as email1@.email.com,email2@.email.com,.....like that
with autoincrement.Here primarykey attribute name is id

Waiting for valuable suggestions

Baba

I take it you don't want to update ALL emails in your table, just the duplicate ones (email@.email.com)?

If so, it depends on whether the number is important or whether you just want to make each value distinct.

To just get distinct values you could just run:

Code Snippet

UPDATE table

SET email = 'email' + CAST(id AS VARCHAR(100)) + '@.email.com'

WHERE email = 'email@.email.com'

If the number is important and you want the values to go sequentially from 1-> n then you'll probably have to use some procedural code, such as a cursor though you'll still need an update statement similar to the one above.

HTH!

|||
Thank u for u r reply.

I applied cursors concept and i updated it.

Thank u

Baba
|||

You might want to show us what you come up with when you are done so that we can verify it for you; it is always a good idea to have another set of eyes to look at something.

|||

There's a way to get the numbers to be sequential without cursors etc:

Code Snippet

UPDATE table

SET email = 'email' + Convert(varchar(100), (select count(*) from table t2 where t2.id < t1.id and t2.email = 'email@.email.com')) + '@.email.com'

FROM table t1

WHERE email = 'email@.email.com'

It's late and I haven't tested it so there may be errors, but hopefully that gives you the general idea.

Sean

Help needed- regarding datediff functio

Hi,
I have to compare every row in month column in xyz table with current month. If there is mismatch, then error message must be displayed.
I have used DATEDIFF (MM, Select getdate(), select month from xyz ), but I am getting error at MM. Can anybody fix this issue.

The syntax you have is incorrect. Try something like this:

SELECT [Month], Mismatch=CaseWHENMonth(Getdate())= [Month]then 0ELSE 1END

FROM MyTable

|||

This will work:

1SELECT MonthColumn,2 IsMatchColumn =3CaseWHENDatePart(Month,GetDate()) = MonthColumn4Then 0-- Yes (Matched)5Else 1-- No (Not Matched)6END78FROM YourTableName9

Good luck.

help needed on a silly query :P

hi ...
i m a new member
i want a help on this query(new user of sql :D )
yaa and the query is

create a rule and attach it to the service_code(column) of airline_service(Table) alow only the values 'cc','n' and 'wc' to be entered into the column

plz tell mee... :confused: ..i wlll be gr8 fulllllllllllllllllllllllllllllllll ;)I suggest you to create a CHECK CONSTRAINT instead of a rule:

ALTER TABLE dbo.airline_service
ADD CONSTRAINT chk_servicecode CHECK (service_code IN ('cc', 'n', 'wc'))

Davide Mauri
http://www.davidemauri.it|||You could create a trigger or a check. I think a check is more likely what you'd like, fe:

use monkey
go

create table table1 (mycolumn varchar(10))
go

alter table table1 with check
add constraint df__myrule check
( mycolumn not in ('yes', 'allowed'))
go

insert into table1 (mycolumn) values ('yes')
insert into table1 (mycolumn) values ('no')
go

select *
from table1

drop table table1
go

See BOL on CHECK

EDIT: sniped!|||thank youuuuuuuuuuuuuuuuuuuuuuu :o|||I hope your professor gives you full credit for that, but next time try to do your own homework first.|||I doubt if the Professor will give a credit!

He asked for a rule so it should be:

CREATE RULE list_rule
AS
@.list IN ('cc', 'n', 'wc')

nomis|||That's for sure, but rules are to be considered deprecated, only for backward compatibility. So the CHECK solution is really better. :-)

Anyway, after adding the rule it must be binded with sp_bindrule

Help needed in the calculated column on the bases of other column

select myname,mydate,myduedate,mymonthCal,mydaysleftCal,mycategoryCal,mydateyear_monthCal from tbltest

myname and mydate are in the database wh ich are value who are not allowed null.

mymonthCal,mydaysleftCal,mycategoryCal and mydateyear_monthCal are calculated column on the basis of myname and mydate

myname mydate myduedate
aa 3/31/2005 12:00:00 am 5/31/2005 12:00:00 am
12/1/2005 12:00:00 am 12/30/2006 12:00:00 am
cc 12/12/2006 12:00:00 am

expected resultset
myname mydate mymonthCal, mydaysleftCal, mycategoryCal, mydateyear_monthCal
aa 3/31/2005
bb 12/1/2005
cc

1,mymonthCal

if mydate is 12-31-2005 12:00:00 am THEN mymonth should be Dec-05

2,mydaysleftCal

if myduedate is empty it will return 39,126
other wise it will first check if myname is empty [remeber not null just ''] it will return empty string else
return the difference of current date with mydate

3,mycategoryCal

it is calculated by the excel formula on the bases mydaysleft

=IF(mydaysleftCal="","",IF(mydaysleftCal<=0,0,IF(mydaysleftCal<=10,"0 - 10",IF(mydaysleftCal<=30,"11 - 30",IF(mydaysleftCal<=60,"31 - 60",IF(mydaysleftCal<=90,"61 - 90",IF(mydaysleftCal>90,"90+","")))))))

4,mydateyear_monthCal

if mydate is 3/31/2005 12:00:00 am and month is less then 10 then mydateyear_monthCal should be like 2005_3 where 2005 is the year and 3 is the month
if month is greater then or equal to 10 then mydateyear_monthCal should be like 2005_x3

I didn't exactly understand what you were attempting to accomplish in step 4 (MyDateYear_MonthCal) -so I left it up for you to verify. However, I think that I have illustrated several concepts that 'should' help you move forward with this query. (A couple of datetime functions, a couple of string functions, CASE structures for switching, and the use of derived tables in place of an actual table.)

In the calculation of MyDaysLeftCal, since a number is being calculated, zero will display instead of NULL or empty string ''. Also, note that MyDate is NULL or empty string '', the date will appear as '01/01/1900'.

Code Snippet

DECLARE @.MyTable table
( MyName varchar(10),
MyDate datetime,
MyDueDate datetime
)

SET NOCOUNT ON

INSERT INTO @.MyTable VALUES ( 'aa', '3/31/2005', '5/31/2005' )
INSERT INTO @.MyTable VALUES ( 'bb', '', '' )
INSERT INTO @.MyTable VALUES ( 'dd', '04/12/2007', '04/30/2007' )
INSERT INTO @.MyTable VALUES ( 'ee', '1/31/2007', '3/31/2007' )
INSERT INTO @.MyTable VALUES ( '', '12/1/2005', '12/30/2006' )
INSERT INTO @.MyTable VALUES ( 'cc', '12/12/2006', '' )

SELECT
MyName,
MyDate,
MyMonthCal,
MyDaysLeftCal,
MyCategoryCal = CASE
WHEN MyDaysLeftCal = '' THEN ''
WHEN MyDaysLeftCal <= 10 THEN '0-10'
WHEN MyDaysLeftCal <= 30 THEN '11-30'
WHEN MyDaysLeftCal <= 60 THEN '31-60'
WHEN MyDaysLeftCal <= 90 THEN '61-90'
ELSE '90+'
END,
MyDateYear_MonthCal
FROM ( SELECT
MyName,
MyDate = convert( varchar(10), MyDate, 101 ),
MyMonthCal = ( left( datename( month, MyDate ), 3 ) + '-' +
( right( cast( year( MyDate ) as char(4) ), 2 ))),
MyDaysLeftCal = CASE
WHEN MyDueDate = '' THEN 39126
WHEN MyName = '' THEN ''
ELSE datediff( day, MyDate, getdate() )
END,
MyDateYear_MonthCal = CASE
WHEN month( MyDate ) > 10 THEN
( cast( year( MyDate ) as varchar(4) )) +
'_x' +
cast( month( MyDate ) as varchar(2) )
ELSE ( cast( year( MyDate ) as varchar(4) )) +
'_' + cast( month( MyDate ) as varchar(2) )
END
FROM @.MyTable
) dt

MyName MyDate MyMonthCal MyDaysLeftCal MyCategoryCal MyDateYear_MonthCal
- - - - - -
aa 03/31/2005 Mar-05 737 90+ 2005_3
bb 01/01/1900 Jan-00 39126 90+ 1900_1
dd 04/12/2007 Apr-07 -5 0-10 2007_4
ee 01/31/2007 Jan-07 66 61-90 2007_1
12/01/2005 Dec-05 0 2005_x12
cc 12/12/2006 Dec-06 39126 90+ 2006_x12

Help me with Derived Column Transformation

I have this expression
(Registered_Units == Limited_Units) ? 0 : (Painted_Units / (Registered_Units - Limited_Units)) * 100)

It gives me 0.00, although those 3 columns have values.

if I cast it like this

(Registered_Units == Limited_Units) ? 0 : ((((DT_DECIMAL,2)Painted_Units) / ((DT_DECIMAL,2) (Registered_Units - Limited_Units))) * 100)

It gives me correct answer in whole number but .00 after decimal.

Any clue how to fix it ?

Try casting the 100 to a decimal as well...

Dave Fackler

|||Hi Dave,

Thankyou for your reply.
I have changed the expression to following and it works
(Registered_Units == Limited_Units) ? 0 : ((DT_DECIMAL,2)((DT_DECIMAL,2)((Painted_Units * 100)) / ((DT_DECIMAL,2)(Registered_Units - Limited_Units))))

I appreciate your response.

Help me to create a order column

Hi all,

In Visual Foxpro i create a Order column that:
replace all myOrderColumn with recno()

[myOrderColumn]
1
2
3
4
5
6
...
EOF()

Help me to create myOrderColumn - SQL Server 2000 (by code, not use indetity column)

Thank alot!
I am wondering why this is necessary.

Bear with me here. . .

Fundamental relational database theory states that 'order' should be based on some quality of a relation. Remember, each tuple (record) in a table is a relation (Some people erroneously think that relational means referential integrity, i.e. foreign keys.) If there is some determinant that implies order, that determinant must be derivable by an examination of one or more fields of the tuple, e. g. Alphabetical Last Name, First Name or Date Of Transaction, etc. . .

You do realize that a real RDBMS makes no guarantee that a record last inserted will be the last physically in the table.

What? evertime a record is deleted/inserted you are going to change the order value? recalculate the index?

Do you need it for a surrogate key? why does it need to be ordered?

I am willing to wager my yearly salary that if you need an order column, your data model is underqualified.

I am also willing to wager that FoxPro is not a Relational Database System.|||Well, the first person that replied to your question should probably dismount his high horse a bit, as it is sometimes handy to retrieve an ordinal number corresponding to the row being returned (especially when generating output for reporting purposes or whatnot). I won't question why you are doing this, but I trust you have. a. very. good. reason.

In SQL Server 2005 :-) you can use the ROW_NUMBER() function in your SELECT list, and that will do the job. In SQL Server 2000, you have no such function. You will have to either use a cursor, number your rows in code, or insert your results into a similarly formed temp-table with an identity column on it and then return your values.

-Dave|||

Dave Markle wrote:

Well, the first person that replied to your question should probably dismount his high horse a bit, as it is sometimes handy to retrieve an ordinal number corresponding to the row being returned (especially when generating output for reporting purposes or whatnot). I won't question why you are doing this, but I trust you have. a. very. good. reason.

I wasn't trying to be arrogant I am just telling the truth.

What is he trying to model?
What is the purpose of the ordinal?
How would you determine the order?
Why would one not just index on that determinant and forget about the ordinal?

In a relational system, there is never a good reason for doing this. An arbitrary sequence number has NO intrinsic value.
Oh it isn't arbitrary?
Then how is it determined?
Index on the determinant and forget the order by column!
Oh! it is a multi-field key and you don't want to carry all the fields over to a child table?
Use an identity as a surrogate key. Primary Key on the multiple values and unique on the identity. Use the identity in the child as a foreign key to the parent.
To get the parent keys in proper order:

select identityfield from my table order by primarykeyfield1, primarykeyfield2

It matters not that the identityfield might not be in numeric sequence. . It will be in the order that is intrinsic to the data domain.

I am just making guesses as to what need there is for the order column. Again, I am sure that there is no need for it.

In SQL Server 2005 :-) you can use the ROW_NUMBER() function in your SELECT list, and that will do the job. In SQL Server 2000, you have no such function. You will have to either use a cursor, number your rows in code, or insert your results into a similarly formed temp-table with an identity column on it and then return your values.

This isn't what he is asking for. ROW_NUMBER and a temp identity have no persistence from run to run. That is, the rows returned could have a different row number or identity value if the underlying data changes via inserts or deletes - no intrinsic value. Therefore it has no use as part of a foreign key or any other use that has any real meaning.

Help me pls - with database and Insert statement

I Have an error:

Server Error in '/quanlythietbi' Application.

---

INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_yeucau_nhanvien'. The conflict occurred in database 'equipment', table 'nhanvien', column 'manv'. The statement has been terminated.

Source Error:

Line 129:mycommand.Parameters.Add(new SqlParameter("@.noidung_yc1",System.Data.SqlDbType.Text));Line 130:mycommand.Parameters["@.noidung_yc1"].Value = TextBox1.Text;Line 131:int i = mycommand.ExecuteNonQuery();Line 132:if (i>0)Line 133:{

and this is my code:

string sqlstring = "Select * from yeucau where ngayGiaiQuyetxong='"+ Label8.Text +"' and date_yc='" + Label7.Text + "' and manv_yc='"+ TextBox2.Text + "' and noidung_yc='"+ TextBox1.Text+ "'";

myconnection =new SqlConnection(stringconn);

mycommand =new SqlCommand(sqlstring,myconnection);

myconnection.Close();

myconnection.Open();

mycommand =new SqlCommand(insertquery,myconnection);

mycommand.Parameters.Add(new SqlParameter("@.ngayGiaiQuyetxong1",System.Data.SqlDbType.Char,10));

mycommand.Parameters["@.ngayGiaiQuyetxong1"].Value = Label8.Text;

mycommand.Parameters.Add(new SqlParameter("@.date_yc1",System.Data.SqlDbType.SmallDateTime));

mycommand.Parameters["@.date_yc1"].Value = Label7.Text;

mycommand.Parameters.Add("@.manv_yc1",System.Data.SqlDbType.Char,10);

mycommand.Parameters["@.manv_yc1"].Value = TextBox2.Text;

mycommand.Parameters.Add(new SqlParameter("@.noidung_yc1",System.Data.SqlDbType.Text));

mycommand.Parameters["@.noidung_yc1"].Value = TextBox1.Text;

int i = mycommand.ExecuteNonQuery();

if (i>0)

{

lbcheck.Text = "?ã C?p Nh?t Yêu C?u.";

}

---------------------------------------

I don't know what I must do to repair it :(

How does insertquery look like? The error comes from an INSERT command, so you should check the INSERT command to see whether it tries to insert a row which conficts with the FK constraint. Such error will be raised if you try to insert a row with a field value that does not exist in the PRIMARY KEY (in another table) referenced by the FOREIGN KEY. To learn more about FOREIGN KEY constraint, you can refer to:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_04_8ypg.asp

|||

thank a lot :) I will try to fix it now

Help me please i have a problem in ALTER command

Dear all

I need to add new column in table TranPay , I use ALTER command on PDA on Button Click

Dim SqlStm = "ALTER TABLE TranPay ALTER COLUMN TranKey IDENTITY (1, 1)"

Dim Dcmd As SqlCeCommand = New SqlCeCommand

Dcmd.Connection = Conn

Dcmd.CommandText = SqlStm

Dcmd.ExecuteNonQuery()

But cannot create new Field , Help me please

Brg ,

Tingnong

Try this:

ALTER TABLE TranPay ALTER COLUMN TranKey int IDENTITY(1,1)

You must specify the column data type.|||

Try this:

Code Snippet

ALTER TABLE TranPay ADD TranKey int IDENTITY (1,1)