Re: Modifications Date - Database Discussion Boards

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.

<italic>Work hard, Work effectively.
Own business (desain web) ,Stock Pick Guide.

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??

Sorry, I don't see it in sysobjects, not that it doesn't exists somewhere.

--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'

Is there any way by which I can run script stored in .sql file on Oracle
using .net code?

Nana

Hi,
Any Sql query for Export data from MS SQL SERVER to abc.txt file

two way this can be done one wizard / command

1. from enterprise manager use import/export
2. sql command "bcp" (for details on syntax & usage you can refer SQL Server Online help)

Regards
KP

command to import sql tables to cd.

bhawna_k wrote:
command to import sql tables to cd.

??

printf("Navaneeth!!")

www.w3hearts.com

You can copy the database files and attach that in another server. Or use import export wizard and copy the tables from one server to the destination file you want. You can use csv, access files. Then copy the file to CD Mad | :mad:

printf("Navaneeth!!")

www.w3hearts.com

two way this can be done one wizard / command

1. from enterprise manager use import/export
2. sql command "bcp" (for details on syntax & usage you can refer SQL Server Online help)

Regards
KP

You can create script files if you just want to export the structure. If you want data also, then either you can copy the database files or take a backup, run database create scripts on target and restore backup on target.

"A good programmer is someone who looks both ways before crossing a one-way street." -- Doug Linder

coolestCoder

What is your opinion about using temporary tables in SQL Server stored procedure. Is it a good practice ?

If yes consider the following

1. I am using temporary tables in stored procedure and accessing this procedure from ASP.NET page. If more than one user came to the page at same time, it is showing table already exist error. But I have read that SQL Server will attach some timestamp or some session value with the temporary table name what we given. Then there is no chance or getting this error. But still I am getting the error. Where am I going wrong ?

To solve this error I am passing the session id from page and appending that with the table name. Is it the rite way to overcome this problem ?

If no

1. Then what is the alternate solution for temporary tables ?

I am totally Confused | :confused:

printf("Navaneeth!!")

www.w3hearts.com

Hi
After using Temporary table, you need to drop the table before using it again. Then you will not get that error.

Harini

Harini Krishnaswamy wrote:
After using Temporary table, you need to drop the table before using it again. Then you will not get that error.

It's not like that harini, Actually I am dropping the table after use

Take the following situation

1. I created temporary table in stored procedure
2. If two users are accessing the procedure at same time it will show error. Because the first called procedure already created temporary table and it is not yet finished it's execution to drop the table. At the same time second procedure is also trying to create the same table and it will show the error. I need to overcome this.

printf("Navaneeth!!")

www.w3hearts.com

Hope you are trying to create a table not in tempdb.

Check whether the tbale is already present before executing "CREATE TABLE"
i.e.
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'tblA') CREATE TABLE tblA (col1 varchar(4), col2 ........)

Regards
KP