|
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.
?? 
|
|
|
|
|
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 
|
|
|
|
|
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 
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
are you creating the temporary in "tempdb" ?
Local temporary (#table_name) tables are for current session; global temporary (##table_name)tables for all sessions.
Temporary tables are automatically dropped when they go out of scope.
A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table.
All other local temporary tables are dropped automatically at the end of the current session.
Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.
Regards
KP
|
|
|
|
|
Thanks Krish
I am using ##temp. So as you told it will be treated as global temporary table. Is it ? From my procedure I am calling 10 sub procedures. In all these procedures I am using this temporary table. So when will it's scope ends ? After finishing the procedure execution, it's not dropping the table automatically.
|
|
|
|
|
If you call the 10 procedures for the main procedure you don't need the ##temp a local #temp will work and will fix your problem. This assuming that the connection/ session is new on each page view or call to the main procedure.
|
|
|
|
|
DooDooo wrote: What is your opinion about using temporary tables in SQL Server stored procedure. Is it a good practice ?
They can be. Once, by the introduction of temporary tables, reduced the query time from 20 minutes down to 7 seconds.
DooDooo wrote: If more than one user came to the page at same time, it is showing table already exist error.
Are you using the same connection for all pages in you ASP.NET application. You should not share a connection between page requests. A connection object should be created, opened, the query performed then the connection closed and allowed to go out of scope. The connection should not be stored anywhere for later use - only the connection string is needed.
|
|
|
|
|
Colin Angus Mackay wrote: re you using the same connection for all pages in you ASP.NET application
Never. I use separate connection. What I mean is when more than one users coming to same page it is showing error.
Like
I have one aspx page, let it be a.aspx. If you and me are accessing the page at same time it is showing the error. In page load I am calling one procedure that will make temporary table. Then I am getting error. What's your opinion on passing the session id from page to storedprocedure and creating a table in that name. ?
|
|
|
|
|
DooDooo wrote: What's your opinion on passing the session id from page to storedprocedure and creating a table in that name. ?
I don't have an opinion on that topic. I've never tried it. Off the top of my head I can't see any difficulties with it.
|
|
|
|
|
Colin Angus Mackay wrote: Off the top of my head I can't see any difficulties with it.
Thank you
|
|
|
|
|
You can try the same thing with table variables and see if you are having the same problem. At times the table variables are also part of best practice on top of temporary tables.
Sharp
Happy Programming
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
