|
Using trigger in stored procedure. When insert occurred, edit the companyID.
|
|
|
|
|
Hi
Use a trigger, so that the values in the company id are autoincremented/updated/kept same, according to the changes made in the company name.
Keshav Kamat 
India
|
|
|
|
|
 can any one give me a link to tutorials bout ADO.Net or explain to me with examples of codes
vuyiswamb@yahoo.com
Vuyiswa Maseko
South Africa
|
|
|
|
|
|
http://www.devcity.net/Articles/14/start_ado_net.aspx
A quick and easy VB.NET example. Are you looking for vb.net or c#?
Visual Studio can't evaluate this, can you?
public object moo<br />
{<br />
__get { return moo; }<br />
__set { moo = value; }<br />
}
|
|
|
|
|
|
I have table customermaster which has a around 200000 records.
Problem is this that there are same customers on different customerids
for ex.
customerid 1 : ABC
customerid 2 : ABC
that there are duplicate records.....
I have tried several things but i am not getting any result.....
Can any one help to get rid of this duplicacy.
Ahmad
|
|
|
|
|
I am not very clear about your problem.....when inserting record in table you have no checking mechanism to prevent duplicacy.....if you dont want to insert duplicate record check for duplicacy first and then insert.....
moreover if it is not possible then create a AutoNumber field in your table.....which will not be duplicated while inserting records....you can have ,in this way,a unique ID to identify customers....
if the case is different please clearly point your problem....
Tirtha
"A man can ride on your back only when it is bent....."
|
|
|
|
|
Hi dear ,
you can override this problem by making CustomerID and CustomerName as primary key. This will check for both the Column before inserting
Develop2Program & Program2Develop
|
|
|
|
|
Or making CustomerID only as primary key.
|
|
|
|
|
dear,
If we give primary key only to Id then Same answer becomes the question.
this guy also want that his name also not be duplicated, and hence to avoid this occurance we consider Id and Name both as PK.
I may be mistaken to get your view you posted above.
Develop2Program & Program2Develop
|
|
|
|
|
Correct me if I'm wrong. If CustomerID and CustomerName as primary key, it wouldn't change anything.
CustomerID CustomerName
1 ABC
2 ABC
I think it will still go into the database.
|
|
|
|
|
dear ,
Yes I am wrong in that prespective , I really left the Autogenerated Id , yes you are right.
Develop2Program & Program2Develop
|
|
|
|
|
I don't get it. What's the problem of having two record with the same customer name? What are you trying to do? Many people often have the same name.
|
|
|
|
|
These ar not persons name //customers here are organizations....
so i have same organizations(e.g. Microsoft Corp.) with same contact details
on two different customerids
Thx and rgds
Ahmad
|
|
|
|
|
look Osama its preety simple if u are workin in SQL Server 2000, just open the properwties of that table and go to the index/Keys tab ___> in that click on New and click the Check box create UNIQUE and select the customerName column. You will have it constrained to be unique ok.
Rocky
|
|
|
|
|
How do I find out the Modification Dates of my Views on a Database SQL 2000?
Thanks in advanced for your help
|
|
|
|
|
SELECT Name, CRDate FROM SysObjects
WHERE Name LIKE '%YourView%'
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
Thanks for your answer
But this is the Creation Date, I'm looking for the
Modify Date.
Do you have an idea to find de Modify Date??
|
|
|
|
|
--create
alter view vw_Junk
as
SELECT * from tempdb..tblJunk
select * from sysobjects where type = 'v'
name id xtype uid info status base_schema_ver replinfo parent_obj crdate ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate version deltrig instrig updtrig seltrig category cache
-------------------------------------------------------------------------------------------------------------------------------- ----------- ----- ------ ------ ----------- --------------- ----------- ----------- ------------------------------------------------------ ------- ----------- ---------------- ---- -------- ------- -------- ------------------------------------------------------ ----------- ----------- ----------- ----------- ----------- ----------- ------
vw_Junk 872153252 V 1 36 1610612736 0 0 0 2007-03-23 07:59:21.897 0 0 0 V 0 2 0 2007-03-23 07:59:21.897 0 0 0 0 0 0 0
vw_Junk 872153252 V 1 36 1610612736 16 0 0 2007-03-23 07:59:21.897 0 16 0 V 0 2 0 2007-03-23 07:59:21.897 0 0 0 0 0 0 0
vw_Junk 872153252 V 1 36 1610612736 32 0 0 2007-03-23 07:59:21.897 0 32 0 V 0 2 0 2007-03-23 07:59:21.897 0 0 0 0 0 0 0 There is a schema_ver & base_schema_ver that might be helpful.
The dates might exist in other sys tables but not sure off of the top of my head.
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
Thanks for your help.
I found a different way.
I made a back up from the database and restored it on a SQL 2005, in the SQL 2005 I have the System View sys.views whit the Creation Date and Modify Data!
It’s not a very beautiful way, but it works.....
select * from sys.views order by modify_date DESC
|
|
|
|
|
Hi all.
I have one problem what have source in another problem.
I want to write an stored procedure what insert records in the tables bulky by using OPENXML.
Suppose have two tables:
<br />
Parent****|***Child<br />
----------|---------<br />
Id********|***Id<br />
Name******|***Name<br />
**********|***ParentId<br />
'Child' table have FK to Parent [Child.ParentId(0..*)->Parent.Id(1)]
<br />
<br />
----<br />
Sql procedure:<br />
<br />
....<br />
set @insertText = '<root><br />
<US type="Insert"><br />
< Parent Name="Fifth"><br />
<Child Name="Child5_C1"/><br />
<Child Name="Child5_C2"/><br />
</Parent><br />
< Parent Name="Sixth"><br />
<Child Name="Child6_C1"/><br />
<Child Name="Child6_C2"/><br />
</Parent><br />
</US><br />
</root>'<br />
<br />
---When i insert some rows in one table there is no problems<br />
INSERT INTO Parent([Name])<br />
SELECT * FROM OPENXML (@hdoc, 'root/US[@type="Insert"]/Parent', 0) WITH<br />
(<br />
[ChName] nvarchar(20) '@Name')<br />
)<br />
<br />
--But when i need to insert new rows in child data table appear problem, i need to have<br />
-- id of parent column in parent table, for every row what i insert in children<br />
<br />
INSERT INTO Container ([Child], [ParentID])<br />
SELECT * FROM OPENXML (@hdoc, 'root/US[@type="Insert"]/Parent/Child', 0) WITH<br />
(<br />
[ChName] nvarchar(20) '@Name',<br />
[ParentId] int --what i should write here:confused:<br />
)<br />
<br />
<br />
--- After easy consideration i got supposed solution just add to parent table temporary column,
like this:
<br />
<br />
Sql procedure:<br />
<br />
....<br />
<big>ALTER TABLE Container ADD TmpID int NULL<br />
GO<br />
</big><br />
<br />
<br />
....<br />
<br />
set @insertText = '<root><br />
<US type="Insert"><br />
< Parent Name="Fifth"><br />
<Child Name="Child5_C1"/><br />
<Child Name="Child5_C2"/><br />
</Parent><br />
< Parent Name="Sixth"><br />
<Child Name="Child6_C1"/><br />
<Child Name="Child6_C2"/><br />
</Parent><br />
</US><br />
</root>'<br />
<br />
-- NOW PROBLEM IS HERE - SQL CENSURE WHAT [TmpID] NOT EXIST!!!<br />
INSERT INTO Container ([Name], [TmpID]) <br />
SELECT * FROM OPENXML (@hdoc, 'root/US[@type="Insert"]/Parent', 0) WITH<br />
(<br />
[ChName] nvarchar(20) '@Name'),<br />
[TmpID] '@mp:id'<br />
)<br />
<br />
.....just use @mp:parentid and TmpID for find out what Parent.Id correspond to inserted child row ....<br />
<br />
ALTER TABLE DROP COLUMN TmpID <br />
<br />
Please help.
-- modified at 5:17 Friday 23rd March, 2007
|
|
|
|
|
hi every one,
well I'm havig a strange bug here.. I made a form in C# 2.0 Windows application. Its a simple form based on one table named
Manufacturer (manID PK, ManName, logo, Discription)
I made it by binding the data with the binding source and apprantly its working fine but when I try to save changes by clicking on save Button on the binding Navigator it gives an error as
System.Data.SqlCLient.Sqlexception: length of text, ntext or image data (129712) to be replicated exceeds configured maximum 65536
now u guys must be thinking that its nota relevant question but I've just found out in the book of SQL Server that a create table statement taht includes a Sql_varient column can generate the following error
The total row size (xx) for tabole 'yy' exceeds the maximum number of bytes per row(8060). Rows that exceed the maximum number of bytes will not be added.
So heres the Script for the table as well,
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_CatMan_Manufacturer]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[CatMan] DROP CONSTRAINT FK_CatMan_Manufacturer
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Manufacturer]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Manufacturer]
GO
CREATE TABLE [dbo].[Manufacturer] (<br />
[ManID] [numeric](18, 0) IDENTITY (600, 6) NOT FOR REPLICATION NOT NULL ,<br />
[ManName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,<br />
[Logo] [image] NULL ,<br />
[Disc] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />
[msrepl_tran_version] [uniqueidentifier] NOT NULL <br />
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]<br />
GO
and I'm not using SP4 of SQL Server Right now. So can u plz give me some idea as to how to solve this thing. I really have v short time..
thanks in advance
Rocky
|
|
|
|
|
Do you have replication configured for this database server?
|
|
|
|
|
yes and infact I've got the solution as well. here it is:
CREATE PROC usp_CONFIGURE_ReplicationSizeForBlobs
@NewSize int = 100000000
/*
* Sets the 'max text repl size' instance wide configuration setting
* that governs the maximum size of an image, text, or ntext column
* in a replicated table.
*
* Example:
exec usp_CONFIGURE_ReplicationSizeForBlobs default
**********************************************************************/
AS
print 'Old size'
exec sp_configure 'max text repl size'
print ' Setting new size'
exec sp_configure 'max text repl size', @NewSize
print 'Reconfiguring'
RECONFIGURE WITH OVERRIDE
print 'New size'
exec sp_configure 'max text repl size'
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
