Monday, March 26, 2012
Need help
I tried to update a record on one server and there is an update
trigger to to update another record in another server . But I got this error
and no record is updated. Can anyone please advise me the solution and the
cause? Thanks.
The error message as follow:
update action start
Server: Msg 7391, Level 16, State 1, Procedure something_ins_upd_trg, Line
328
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Hi Eric,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you encounter error message 7391 when
executing query on Linked Server. If I have misunderstood your concern,
please feel free to point it out.
First of all, please make sure you are not encounter the scenario as the
following KB described.
You may receive a 7391 error message in SQL Server 2000 when you run a
distributed transaction against a linked server after you install Windows
Server 2003 or Windows XP Service Pack 2
http://support.microsoft.com/kb/839279
Also, make sure you have made MS DTC correctly configured for network access
You receive error 7391 when you run a distributed transaction against a
linked server
http://support.microsoft.com/kb/329332/en-us
Secondly, I have noticed you mentioned "there is an update trigger to to
update another record in another server". If you are executing the query
that won't active the trigger, will it also reproduce this error message?
Are you executing the stored procedure in a transtion? If you execute the
stored procedure directly, will it reproduce this error message?
If you have any questions or concerns, don't hesitate to let me know. We
are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Thanks for your help, Michael. I am running this trigger on Win2000 SP3 or
SP4. If I remove the trigger there is no error at all and it does involve
store procedure. The following KB apply for Win 2003 only, right? How do
you configure the MS DTC?
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:Tk7Ahv$8FHA.1236@.TK2MSFTNGXA02.phx.gbl...
> Hi Eric,
> Welcome to use MSDN Managed Newsgroup!
> From your descriptions, I understood you encounter error message 7391 when
> executing query on Linked Server. If I have misunderstood your concern,
> please feel free to point it out.
> First of all, please make sure you are not encounter the scenario as the
> following KB described.
> You may receive a 7391 error message in SQL Server 2000 when you run a
> distributed transaction against a linked server after you install Windows
> Server 2003 or Windows XP Service Pack 2
> http://support.microsoft.com/kb/839279
> Also, make sure you have made MS DTC correctly configured for network
> access
> You receive error 7391 when you run a distributed transaction against a
> linked server
> http://support.microsoft.com/kb/329332/en-us
> Secondly, I have noticed you mentioned "there is an update trigger to to
> update another record in another server". If you are executing the query
> that won't active the trigger, will it also reproduce this error message?
> Are you executing the stored procedure in a transtion? If you execute the
> stored procedure directly, will it reproduce this error message?
> If you have any questions or concerns, don't hesitate to let me know. We
> are always here to be of assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>
|||Hi Eric,
Thanks for your reply.
Does your two server on the same domain or on the trusted domains? Does the
linked server is also SQL Server or other database? Execute the query
directly on the destination server, will the query work and active the
trigger?
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
Need help
I tried to update a record on one server and there is an update
trigger to to update another record in another server . But I got this error
and no record is updated. Can anyone please advise me the solution and the
cause? Thanks.
The error message as follow:
update action start
Server: Msg 7391, Level 16, State 1, Procedure something_ins_upd_trg, Line
328
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].Hi Eric,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you encounter error message 7391 when
executing query on Linked Server. If I have misunderstood your concern,
please feel free to point it out.
First of all, please make sure you are not encounter the scenario as the
following KB described.
You may receive a 7391 error message in SQL Server 2000 when you run a
distributed transaction against a linked server after you install Windows
Server 2003 or Windows XP Service Pack 2
http://support.microsoft.com/kb/839279
Also, make sure you have made MS DTC correctly configured for network access
You receive error 7391 when you run a distributed transaction against a
linked server
http://support.microsoft.com/kb/329332/en-us
Secondly, I have noticed you mentioned "there is an update trigger to to
update another record in another server". If you are executing the query
that won't active the trigger, will it also reproduce this error message?
Are you executing the stored procedure in a transtion? If you execute the
stored procedure directly, will it reproduce this error message?
If you have any questions or concerns, don't hesitate to let me know. We
are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for your help, Michael. I am running this trigger on Win2000 SP3 or
SP4. If I remove the trigger there is no error at all and it does involve
store procedure. The following KB apply for Win 2003 only, right? How do
you configure the MS DTC?
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:Tk7Ahv$8FHA.1236@.TK2MSFTNGXA02.phx.gbl...
> Hi Eric,
> Welcome to use MSDN Managed Newsgroup!
> From your descriptions, I understood you encounter error message 7391 when
> executing query on Linked Server. If I have misunderstood your concern,
> please feel free to point it out.
> First of all, please make sure you are not encounter the scenario as the
> following KB described.
> You may receive a 7391 error message in SQL Server 2000 when you run a
> distributed transaction against a linked server after you install Windows
> Server 2003 or Windows XP Service Pack 2
> http://support.microsoft.com/kb/839279
> Also, make sure you have made MS DTC correctly configured for network
> access
> You receive error 7391 when you run a distributed transaction against a
> linked server
> http://support.microsoft.com/kb/329332/en-us
> Secondly, I have noticed you mentioned "there is an update trigger to to
> update another record in another server". If you are executing the query
> that won't active the trigger, will it also reproduce this error message?
> Are you executing the stored procedure in a transtion? If you execute the
> stored procedure directly, will it reproduce this error message?
> If you have any questions or concerns, don't hesitate to let me know. We
> are always here to be of assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>|||Hi Eric,
Thanks for your reply.
Does your two server on the same domain or on the trusted domains? Does the
linked server is also SQL Server or other database? Execute the query
directly on the destination server, will the query work and active the
trigger?
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
Need help
I tried to update a record on one server and there is an update
trigger to to update another record in another server . But I got this error
and no record is updated. Can anyone please advise me the solution and the
cause? Thanks.
The error message as follow:
update action start
Server: Msg 7391, Level 16, State 1, Procedure something_ins_upd_trg, Line
328
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].Hi Eric,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you encounter error message 7391 when
executing query on Linked Server. If I have misunderstood your concern,
please feel free to point it out.
First of all, please make sure you are not encounter the scenario as the
following KB described.
You may receive a 7391 error message in SQL Server 2000 when you run a
distributed transaction against a linked server after you install Windows
Server 2003 or Windows XP Service Pack 2
http://support.microsoft.com/kb/839279
Also, make sure you have made MS DTC correctly configured for network access
You receive error 7391 when you run a distributed transaction against a
linked server
http://support.microsoft.com/kb/329332/en-us
Secondly, I have noticed you mentioned "there is an update trigger to to
update another record in another server". If you are executing the query
that won't active the trigger, will it also reproduce this error message?
Are you executing the stored procedure in a transtion? If you execute the
stored procedure directly, will it reproduce this error message?
If you have any questions or concerns, don't hesitate to let me know. We
are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for your help, Michael. I am running this trigger on Win2000 SP3 or
SP4. If I remove the trigger there is no error at all and it does involve
store procedure. The following KB apply for Win 2003 only, right? How do
you configure the MS DTC?
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:Tk7Ahv$8FHA.1236@.TK2MSFTNGXA02.phx.gbl...
> Hi Eric,
> Welcome to use MSDN Managed Newsgroup!
> From your descriptions, I understood you encounter error message 7391 when
> executing query on Linked Server. If I have misunderstood your concern,
> please feel free to point it out.
> First of all, please make sure you are not encounter the scenario as the
> following KB described.
> You may receive a 7391 error message in SQL Server 2000 when you run a
> distributed transaction against a linked server after you install Windows
> Server 2003 or Windows XP Service Pack 2
> http://support.microsoft.com/kb/839279
> Also, make sure you have made MS DTC correctly configured for network
> access
> You receive error 7391 when you run a distributed transaction against a
> linked server
> http://support.microsoft.com/kb/329332/en-us
> Secondly, I have noticed you mentioned "there is an update trigger to to
> update another record in another server". If you are executing the query
> that won't active the trigger, will it also reproduce this error message?
> Are you executing the stored procedure in a transtion? If you execute the
> stored procedure directly, will it reproduce this error message?
> If you have any questions or concerns, don't hesitate to let me know. We
> are always here to be of assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>|||Hi Eric,
Thanks for your reply.
Does your two server on the same domain or on the trusted domains? Does the
linked server is also SQL Server or other database? Execute the query
directly on the destination server, will the query work and active the
trigger?
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
Wednesday, March 21, 2012
Need Cursor Help
Hello, I'm trying to construct a cursor that will sequentually increment a number and then update a column with the incremented number. My propblem is thatallthe rows in the table are being updated with the base number +1. So all rows are updated with 278301. BUT, what I really want is for only the items with adrscode of 'bill to' to be given an incremented number.
For example, if there are only five rows of 100 with an adrscode = 'bill to' then only five rows will be updated and the value of the custnmbr should be, 278301, 278302, 278303 ....
I could really use some help with this cursor:
Declare @.CustomerName as char (60),
@.seqno as int,
@.BaseSeqno as int
set @.Baseseqno = 278300
declare c cursor for select custnmbr from NXOFcustomers Where adrscode = 'BILL TO' order by custnmbr
open c
fetch next from c into @.CustomerName
while @.@.fetch_status=0
begin
set @.seqno = @.BaseSeqno + 1
update NXOFcustomers set custnmbr = @.seqno
Where custnmbr = @.CustomerName
fetch next from c into @.CustomerName
end
close c
deallocate c
Make sure you are dealing with the right data. Is custnmbr same as customername?
Then, you would modify your sequence generation to
set @.seqno = @.seqno + 1
and set the @.seqno to 278300. You wouldnt need the @.Baseseqno at all. Based on your current logic, you will always gett he same number since you are adding 1 to @.BaseSeqno which is 278300.
Finally, I would recommend looking at alternatives to cursors. Read up books on line regarding cursors. There are pros/cons.|||
Thanks Dinakar for helping. I change my code to what is below and thought that I had things correctly from your explaination but the update still updates all the rows with 278301. Yes, customername is the same as custnmbr so I changed the code to reflect this.
Declare @.custnmbr as char (60),
@.seqno as int
set @.seqno = 278300
declare c cursor for select custnmbr from NXOFcustomers Where adrscode = 'BILL TO' order by custnmbr
open c
fetch next from c into @.custnmbr
while @.@.fetch_status=0
begin
set @.seqno = @.seqno + 1
update NXOFcustomers set custnmbr = @.seqno
Where custnmbr = @.custnmbr
fetch next from c into @.custnmbr
end
close c
deallocate c
go
I'm a little confused with sequence generation. Isn't set @.seqno = @.segno + 1 in the correct location.
Thanks again for responding.
|||you might want to change the @.custnmbr from char to varchar. char is a fixed length data type. so it pads spaces if the string is less than the specified length. You might also want to add the condition adrscode='BILL TO' in the update statement, so if you have 2 customers with same name you have a higher chance the correct record can get updated.Monday, March 19, 2012
Need assistance on conditional update Trigger
I need some help here in creating a conditional update trigger. The purpose of this trigger would check to see if a contact already exist in the database on an insert and update only the fields that are null.
So How would I compare each field from the CONTACTS Table against my INSERTED Table?
Inserted.FirstName (COMPARE) Contacts.Firstname
Inserted.LastName (COMPARE) Contacts.LastName
Inserted.Email (COMPARE) Contacts.Email
I will be using the email address as the check for the duplicate record and if a duplicate is found... Instead of not allowing the insert I want to compare the existing record and update any fields that are NULL in Contacts with Inserted.
I have no idea on how to compare all of the fields.
Any help appreciated.
sadler_david@.yahoo.com
You could do the following in an INSTEAD OF trigger:
create trigger merge_contact
instead of insert
on dbo.Contacts
as
begin
update dbo.Contacts
set FirstName = coalesce(FirstName, i.FirstName),
LastName = coalesce(LastName, i.LastName)
from inserted as i
where i.Email = dbo.Contacts.Email
insert into dbo.Contacts (FirstName, LastName, Email)
select i.FirstName, i.LastName, i.Email
from inserted as i
where not exists(select * from dbo.Contacts as c
where c.Email = i.Email)
end
Ideally, you need to perform the operations in serializable transaction isolation level to avoid duplicate inserts.
Monday, March 12, 2012
Need advise on Insert trigger
I am trying to create a insert trigger that gets a value from the new record and use that to get additional data from other tables and update the new record
this is howfar i came:
create trigger trUpdateGEOData
on BK_Machine
after insert
as
update BK_Machine
set BK_Machine.LOC_Street = GEO_Postcode.STraatID, BK_Machine.Loc_City = GEO_Postcode.PlaatsID
from BK_Machine join GEO_Postcode on BK_Machine.loc_postalcode = GEO_Postcode.postcode, inserted
where BK_Machine.MachineID = Inserted.MachineID and BK_Machine.Loc_Postalcode = GEO_Postcode.postcode and BK_Machine.LOC_Doornumber <= GEO_Postcode.van and
BK_Machine.LOC_Doornumber <= GEO_Postcode.tem
Trigger runs fine but doesn't do a thing probably it can't find the machineid i think,
can someone help me?
Cheers WimmoSo sorry for posting such a stupid mistake from my hazy view today!!
< should be >
Trying to get awake today, sorry for the disturbance
Cheers Wimmo|||Cleaned up your code:create trigger trUpdateGEOData
on BK_Machine
after insert
as
update BK_Machine
set BK_Machine.LOC_Street = GEO_Postcode.STraatID,
BK_Machine.Loc_City = GEO_Postcode.PlaatsID
from BK_Machine
inner join GEO_Postcode on BK_Machine.loc_postalcode = GEO_Postcode.postcode
and BK_Machine.LOC_Doornumber <= GEO_Postcode.van
and BK_Machine.LOC_Doornumber <= GEO_Postcode.tem
inner join inserted on BK_Machine.MachineID = Inserted.MachineID
Now, it strikes me that the inner join on GEO_Postcode could return no records, in which case no BK_Machine records would be updated. It could also potentially return more than one record, in which case you would get unpredictable results for your update.|||Thanx Blindman,
What do you mean with the inner join: is it when there are more records inserted at the same time with the same machineid?
Wimmo
need advice on Update statement
update studemo set passwd = ident
ident is not part of the primary key but there is a unique, non-clustered index using that column
This has been running for over a hour on a table with 25K rows. Am I doing something basically wrong here?
If that is your exact statement, then what you aredoing is updating the password to ident for every row. I assume that that is the purpose, right?
If so, then the next question is a matter of hardware. Is this on a small machine, or a server class machine.
And, is the table clustered on password? ident being clustered wouldn't matter, but passwd would. Either way, if this is something you are doing "offline" then drop or disable indexes (2005 only) and you will certainly get better performance.
Basically, what I am trying to say is that there is no way to know from what you have told us so far, but you certainly do need to look at this being hardware oriented, so check perfmon and see what is being taxed.
|||SQL Server (2005) is on a server class machine. Hardware resources should not be an issue but I'll look into it.The are no indexes using the password column. I'll check perfmon.|||
Add a loop logic and commit after every 1000 rows
The trick is to add a flag to the table (call it anything like UPDATE_FLAG , set it to some value and in where clause do a not equal to the same value :
SET ROWCOUNT 1000
UPDATE
TABLE1
SET
COLUMN = WHATEVER
, UPDATE_FLAG = '1'
FROM
TABLE2 (OR) WHATEVER
WHERE
UPDATE_FLAG != '1'
You can perform the update in batches like below to reduce resource usage:
while(1=1)
begin
-- Update 500 rows at a time
update top(500) studemo set passwd = ident
if @.@.rowcount = 0 break
end
|||
Are your logs and data on the same drive? That could be a problem, especially if the rows are really big, and even worse if the value you are updating the value to is bigger than what it was.
I also should have mentioned to look for blocking :)
In perfmon I would look for large quantities of disk queuing and/or memory stress. Then implement one of the batching mechanisms (and probably drop indexes might help. This is esepecially true when you have large rows as page splits could be very frequent.) On such a small table building the indexes might take less time than maintaining them.
|||The log and data are on separate drives.When I run the script from above:
while(1=1)
begin
-- Update 500 rows at a time
update top(500) studemo set passwd = ident
if @.@.rowcount = 0 break
end
I would have expect to see approximately 50 rows indicating 500 rows
had been updated since the table only has 25K rows. Before I cancelled
the update, I had more than 1000 messages that 500 rows had been
updated.
Weird ... to me.
Thanks for all the reponses so far.
|||What messages did you see? Does the table have any triggers for update? What does (select count(*) from studemo) return?|||Count = 25704.Message: (500 row(s) affected) tr_slvswstuU fired, no change to address fields
There is one trigger that fires:
******************************************************
USE [fakeSISdata]
GO
/****** Object: Trigger [dbo].[tr_slvswstuU] Script Date: 08/30/2006 14:03:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create TRIGGER [dbo].[tr_slvswstuU] ON [dbo].[studemo]
FOR UPDATE
AS
set nocount on
--print 'tr_slvswstuU fired'
declare @.hcheck smallint,
@.mcheck smallint,
@.pcheck smallint,
@.ppass smallint
set @.hcheck = 0
set @.mcheck = 0
set @.pcheck = 0
Check for changes is Physical Address--
if exists (select i.suniq from inserted i,deleted d where
i.suniq = d.suniq and
(i.homeaddr1 <> d.homeaddr1 or
i.homeaddr2 <> d.homeaddr2 or
i.homecity <> d.homecity or
i.homezip <> d.homezip))
set @.hcheck = 1
--select @.hcheck
--Check for changes in Mailing Address-
if exists (select i.suniq from inserted i,deleted d where
i.suniq = d.suniq and
(i.mailaddr1 <> d.mailaddr1 or
i.mailaddr2 <> d.mailaddr2 or
i.mailcity <> d.mailcity or
i.mailzip <> d.mailzip))
set @.mcheck = 1
--Check for changes in Phone Number or Phone Type-
if exists (select i.suniq from inserted i,deleted d where
i.suniq = d.suniq and
(i.phnnumber <> d.phnnumber or
i.phntypec <> d.phntypec or
i.phnunlist <> d.phnunlist or
i.phnmsg <> d.phnmsg))
begin
declare @.oldphone char(10),
@.newphone char(10),
@.unlist smallint,
@.message smallint
set @.pcheck = 1
set @.oldphone = (select phnnumber from deleted)
set @.newphone = (select phnnumber from inserted)
set @.unlist = (select phnunlist from inserted)
set @.message = (select phnmsg from inserted)
--if @.oldphone is null or @.oldphone = ''
--set @.oldphone = 0
if exists(select phnnumber from inserted where (phnnumber is not null or phnnumber <> '') and phntypec = 'R')
set @.ppass = 1
if exists(select i.phnnumber from inserted i,deleted d
where i.suniq = d.suniq and
((i.phnnumber is null or i.phnnumber = '') and
(d.phnnumber is not null or d.phnnumber <> '') or added or
(d.phntypec = 'R' and i.phntypec <> 'R')))
begin
if exists(select phnnumber from deleted where phntypec = 'R')
set @.ppass = 2
end
end
-Call to siSp_syncaddr
--select @.hcheck,@.mcheck,@.pcheck
if @.hcheck = 1 or @.mcheck = 1 or @.pcheck = 1
begin
declare @.ident varchar(10)
set @.ident = (select ident from inserted)
if exists(select * from stucon where ident = @.ident and conmail11 = 1)
begin
--Call for multiple contacts--
if exists(select ident,count(*) from stucon where ident = @.ident and conmail11 = 1
group by ident having count(*) > 1)
begin
print 'tr_slvswstuU fired, called siSp_syncaddr for multiple contacts'
--select @.ident, 0, @.ppass, @.oldphone, @.newphone, @.unlist, @.message
--exec siSp_syncaddr @.ident, 0, @.ppass, @.oldphone, @.newphone, @.unlist, @.message
end
else
begin
--Call for sinlge contact-
declare @.conuniq int
set @.conuniq = (select conuniq from stucon where ident = @.ident and conmail11 = 1)
print 'tr_slvswstuU fired, called siSp_syncaddr for single contact'
--select @.ident, 0, @.ppass, @.oldphone, @.newphone, @.unlist, @.message
exec siSp_syncaddr @.ident, @.conuniq, @.ppass, @.oldphone, @.newphone, @.unlist, @.message
end
end
else
print 'tr_slvswstuU fired no stucon records where conmail11 = 1'
end
else
print 'tr_slvswstuU fired, no change to address fields'
set nocount off
**********************************************************************|||You will have to check the trigger logic. There are lot of SPs that are getting called from trigger code. It is not clear what they are doing. They could probably be adding rows to the same table. In any case, the UPDATE should affect same number of rows as those in the table if there are no new rows being added in the trigger. You can verify by accumulating the @.@.ROWCOUNT value in the batched update code and checking it at the end. You can use SET NOCOUNT ON to suppress the messages.
Need advice on Designing a VLDB OLTP Database
I am trying to design a sql2005 Database with 4 tables of 35 million
rows. we need to update fields in one of the table by joining with
other three. Also we need to delete the roughly 2 million rows daily
from these tables as new data is added. Please suggest if breaking all
these tables into different databases is better or having them all in
one single database is better?. Also the joining keys are varchar
fields. So any suggestions on indexing?
thanks
KrisPartitioning is your friend.
If your daily data sets are in different partitions, the drop can be
metadata-only. That is very fast. Essentially, you are truncating the
partition. To make this work, you have to have indexes aligned with the
partitioning function. Read all about partitioning in BOL.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
<krishnasingaraju@.gmail.com> wrote in message
news:1192490857.778064.244440@.i13g2000prf.googlegroups.com...
> Hi,
> I am trying to design a sql2005 Database with 4 tables of 35 million
> rows. we need to update fields in one of the table by joining with
> other three. Also we need to delete the roughly 2 million rows daily
> from these tables as new data is added. Please suggest if breaking all
> these tables into different databases is better or having them all in
> one single database is better?. Also the joining keys are varchar
> fields. So any suggestions on indexing?
> thanks
> Kris
>|||> Partitioning is your friend.
Yes, very well said (I like it). Also reference DPV's (Distributed Partition
Views).
In a DPV you create n databases and link them together with a view. While
I've heard of multiple partitions on one server and that t performed well, a
DPV can be split up onto multiple nodes of an active/active cluster. There
is one important point, which will be in BOL, make sure that the data is
arranged so that at least 80% of the data you need comes from one
partition/server, or performance could actually be worse.
Jay
> If your daily data sets are in different partitions, the drop can be
> metadata-only. That is very fast. Essentially, you are truncating the
> partition. To make this work, you have to have indexes aligned with the
> partitioning function. Read all about partitioning in BOL.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> <krishnasingaraju@.gmail.com> wrote in message
> news:1192490857.778064.244440@.i13g2000prf.googlegroups.com...
>> Hi,
>> I am trying to design a sql2005 Database with 4 tables of 35 million
>> rows. we need to update fields in one of the table by joining with
>> other three. Also we need to delete the roughly 2 million rows daily
>> from these tables as new data is added. Please suggest if breaking all
>> these tables into different databases is better or having them all in
>> one single database is better?. Also the joining keys are varchar
>> fields. So any suggestions on indexing?
>> thanks
>> Kris
>|||Given the size info, I'm not sure you really want to even consider DPV.
Linchi
"Jay" wrote:
> > Partitioning is your friend.
> Yes, very well said (I like it). Also reference DPV's (Distributed Partition
> Views).
> In a DPV you create n databases and link them together with a view. While
> I've heard of multiple partitions on one server and that t performed well, a
> DPV can be split up onto multiple nodes of an active/active cluster. There
> is one important point, which will be in BOL, make sure that the data is
> arranged so that at least 80% of the data you need comes from one
> partition/server, or performance could actually be worse.
> Jay
> > If your daily data sets are in different partitions, the drop can be
> > metadata-only. That is very fast. Essentially, you are truncating the
> > partition. To make this work, you have to have indexes aligned with the
> > partitioning function. Read all about partitioning in BOL.
> >
> > --
> > Geoff N. Hiten
> > Senior SQL Infrastructure Consultant
> > Microsoft SQL Server MVP
> >
> >
> >
> >
> > <krishnasingaraju@.gmail.com> wrote in message
> > news:1192490857.778064.244440@.i13g2000prf.googlegroups.com...
> >> Hi,
> >>
> >> I am trying to design a sql2005 Database with 4 tables of 35 million
> >> rows. we need to update fields in one of the table by joining with
> >> other three. Also we need to delete the roughly 2 million rows daily
> >> from these tables as new data is added. Please suggest if breaking all
> >> these tables into different databases is better or having them all in
> >> one single database is better?. Also the joining keys are varchar
> >> fields. So any suggestions on indexing?
> >>
> >> thanks
> >> Kris
> >>
> >
>
>
Friday, March 9, 2012
Need a trigger based on a row
I need to create a trigger to update a FLAG column by row modification. The table likes,
Col1 FLAG
a 0
b 0
c 0
d 0
e 0
If "a" in Col1 is changed, the "0" in same row (First row) should be changed to '1'. Other FLAG values should not be changed. The same rule for other row.
Anyhelp will be approciated.
Thanks a lot
ZYTIf col1 is a primary key, this is relatively easy. If you don't have a primary key, I don't know how to do it. Can you "fill in the blanks" a bit on your requirements?
-PatP|||Col1 doesn't have to be a primary key, but you do need a primary key. Put this in your trigger:
Update YourTable
set FLAG = 1
from YourTable
inner join Deleted on YourTable.PrimaryKey = Deleted.PrimaryKey
where YourTable.Col1 <> Deleted.Col1|||So what happens when the code that launches the trigger is something like:UPDATE yourtable SET col1 = 'a'All of the rows except for one change. Without a PK, things get ugly really fast from a SQL perspective, and I'd guess that the business rules would go berzerk!
-PatP|||All of the rows except for one change.-PatP
I think that is what he would want. All of the rows except one were modified, so set the flags on all but one record.
But yes, as I mentioned in my post, he is going to need a primary key on the table.
Wednesday, March 7, 2012
Need a SP to read Table A and update Table B
I need a SP to read table A (1000 records) and updates Table B. I think I have to use Cursor, but I'm not very good at writing SP. Any help would be appreciated. Thanks.You need to help us a little bit. What, if any relationship exists between Table A and B? Is there a column in each that links one to the other?|||The two tables (A and B) are identical and both have a primary key. What I need to do is to update all rows in Table B with the new one from Table A. This is like a batch update from A to B.
Saturday, February 25, 2012
Need 2005 Wizard to create INSERT and UPDATE stored procedures.
In SQL Server 2000, the database wizard would create insert and update stored procedures for all of my tables.
I have a large SQL Server 2005 database with many tables and columns. I need wizard support to create insert and update stored procedures for them. Typing them all in manually is out of the question.
The 2005 Generate SQL Server Scripts Wizard does not create stored procuedure scripts. It only scripts tables.
Is there some other way to make Management Studio create stored procedure scripts the way Enterprise Manager did?
Are there any plans to restore this critical functionality that was left out of SQL Server 2005 and the service pack?
Do you know of any utilities that can fill this gap?
Any help would be appreciated.
Please file a suggestion for this functionality on http://connect.microsoft.com/sqlserver. We use customer feedback to prioritize future work. Suggestions and defect reports filed on the Connect site are placed directly into our internal issue tracking system so the appropriate team will see your request.
I don't think there is an easy way to do what you want using built-in functionality in SSMS. If you are comfortable writing .NET code, you could write a small Visual Basic or C# application using the SQL Management Objects to iterate through all your tables and generate the stored procedure code you need.
Thanks,
Steve