|
Hi,
Actually i want rows in the table in an order. How can i force the tables to keep that order? is there any command for that. Actually i am inserting the data row from asp.net front end.
|
|
|
|
|
srikantha_nagaraj wrote: Actually i want rows in the table in an order.
Sort the table.
srikantha_nagaraj wrote: How can i force the tables to keep that order?
By definition, you can't. A table is a collection of rows that's saved in a random order. You can read them back and order by any field that's present within that selection. You could also add a custom field to indicate the order of the rows, but you'd still need to 'order by' that particular field.
You can, alternatively, create a stored procedure that selects all your rows and sort them. Next, hide the table and give the user only access to your sproc. That would not stop anyone from re-sorting the dataset though.
I are Troll
|
|
|
|
|
Thanks for the answer.
|
|
|
|
|
Eddy Vluggen wrote: srikantha_nagaraj wrote:
How can i force the tables to keep that order?
By definition, you can't.
That's not strictly true, Tables will be stored in the order of their clustered index. This then forms the "default" order.
|
|
|
|
|
|
I added a five, that should even it out.
J4amieC wrote: That's not strictly true, Tables will be stored in the order of their clustered index. This then forms the "default" order
The rows in a RDBMS are not interdependent, same goes for the columns. They might not be returned in the order that they're saved. Because there is no relation between the rows themselves (except that they belong to the same table) there is no order in the collection. This may vary depending on each implementation of course.
Now, that's just theory that I swallowed a long time ago. I found that the most simple table in Access has a storage-order, and I have seen people abuse it. Most of the time, it's easier to add a custom column and sort on that.
I've been reading up after your comment, and a clustered index[^] seems indeed to be the solution
Yes?
|
|
|
|
|
That's not strictly true either! The only way to guarantee output order is using an order by clause.
|
|
|
|
|
That makes it also not strictly false
public enum strictBool { true, false, EFileNotFound }
|
|
|
|
|
Use Order By clause of SQL. It orders the output.
|
|
|
|
|
You have already asked this question and had correct replies. A database does NOT guarantee the order records are returned in UNLESS you have an order by clause. If there is a clustered index or primary key then this is the default order, but even then it is NOT guaranteed (certanly by SQL Server) as when you have multiple worker processe, unless an order by is specified he results are returned as each worker process completes, which MAY not be the expected order.
If you hav any further doubts try reading up on it, there are thousands of articles on google
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Dear All,
I have created replication in a single Database more than 20 times, since three years.
now i have seen some thing amazing,
In every replication the size of log file increases double the size of original file.
as If the first log file is 1 GB then the new one is 2 GB
I used to shrink this too, but i could not get any success, why its like this?
what should i use in order to decrease this log.
Note: I am using SQL 2005
Abdul Rahaman Hamidy
Database Developer
Kabul, Afghanistan
|
|
|
|
|
Abdul Rahman Hamidy wrote: I used to shrink this too, but i could not get any success, why its like this?
It's there for your own security[^], I guess that the Recovery Model for the database is set to "Full". (Right click database, properties, options)
There's an article here[^] that might help in shrinking it
I are Troll
|
|
|
|
|
thx, i think link can help me alot
Abdul Rahaman Hamidy
Database Developer
Kabul, Afghanistan
|
|
|
|
|
|
I don't think you can call them "Urgent!"
However you could call them "Urgent1", "Urgent2", "Urgent3", etc.
A database wouldn't be very useful if you couldn't name your tables, would it?
Luc Pattyn
Have a look at my entry for the lean-and-mean competition; please provide comments, feedback, discussion, and don’t forget to vote for it! Thank you.
Local announcement (Antwerp region): Lange Wapper? Neen!
|
|
|
|
|
Hi,
I have a master DB server, I need to update 4 more DB servers with data from 10 tables on the master. All tables and DB structures are the same on all servers. We need 4 because of traffic load.
So....
I was thinking of setting up a SSIS .DTSX package. Easy for 1 server to another. How do I get it look at 3 more ? Configuration XML file? Can I put in 4 database names, username and passwords and get it to FORLOOP? Do i have the data in a separate table, load it into an object in the package and FORLOOP that?
Or do I create 4 packages and schedule them next to each other ! That would be the easiest.
Any help would be welcome.
Harvey
|
|
|
|
|
I would go with 1 package, and configure it to run 4 times with different configurations for the destinations.
|
|
|
|
|
The 4 different configurations can all be put in 1 XML file? Any ideas would be gladly received.
|
|
|
|
|
Here's the code I can run with no problems:
declare @AnneeSelectionnee numeric
select @AnneeSelectionnee = 2009
declare @DateDernierImport datetime
select @DateDernierImport = 'Nov 14 2008 12:00AM'
SELECT
LTRIM(RTRIM(T_ASFSTAG0.STANS1)),
LTRIM(RTRIM(T_ASFSTAG0.STHFO1)), LTRIM(RTRIM(T_ASFSTAG0.STRSP1))
FROM T_ASFSTAG0
WHERE (T_ASFSTAG0.STANS1=@AnneeSelectionnee) AND
((isdate(T_ASFSTAG0.STCRT1)=1 and convert(datetime,convert(varchar,T_ASFSTAG0.STCRT1,103),103)>=convert(datetime,convert(varchar,@DateDernierImport,103),103)) or
(isdate(T_ASFSTAG0.STMAJ1)=1 and convert(datetime,convert(varchar,T_ASFSTAG0.STMAJ1,103),103)>=convert(datetime,convert(varchar,@DateDernierImport,103),103)))
Please note that T_ASFSTAG0 is a view that is partly defined as follows:
FROM OPENQUERY(ISERIES_SQLDBF, 'SELECT * FROM tstadvdbf.ASFSTAG0') AS DerivedTable1
And the following is the relevant code from the stored procedure that fails:
print @AnneeSelectionnee--2009
print @DateDernierImport--Nov 14 2008 12:00AM
print '------before the select-----------'
print convert(datetime,convert(varchar,@DateDernierImport,103),103)
print '------right before the select-----------'
SELECT --line 60--
LTRIM(RTRIM(T_ASFSTAG0.STANS1)),
LTRIM(RTRIM(T_ASFSTAG0.STHFO1)), LTRIM(RTRIM(T_ASFSTAG0.STRSP1))
FROM T_ASFSTAG0
WHERE (T_ASFSTAG0.STANS1=@AnneeSelectionnee) AND
((isdate(T_ASFSTAG0.STCRT1)=1 and convert(datetime,convert(varchar,T_ASFSTAG0.STCRT1,103),103)>=convert(datetime,convert(varchar,@DateDernierImport,103),103)) or
(isdate(T_ASFSTAG0.STMAJ1)=1 and convert(datetime,convert(varchar,T_ASFSTAG0.STMAJ1,103),103)>=convert(datetime,convert(varchar,@DateDernierImport,103),103)))
print '------after the select and before the insert-----------'
That select statement (on "line 60") reports an error. We never see the "after the select and before the insert" print message. I thought it might have been a conversion problem but as long as an AND statement works like && does in C# (and, AFAIK, it does) then we should have no problems with the (isdate(x) AND convert(datetime(x)).
This is the error I receive:
Server: Msg 7399, Level 16, State 1, Procedure P_IMPORT_STAGES_PREPA, Line 60
OLE DB provider 'MSDASQL' reported an error. One or more arguments were reported invalid by the provider.
OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransactionJoin::JoinTransaction returned 0x80070057: One or more arguments were reported invalid by the provider.].
Any help or hints is greatly appreciated.
Alex
|
|
|
|
|
IMHO the only way to debug this stuff is hack it down to its simplest level and start adding columns until the error occurs. You will either find a mysterious undocumented bug or an obvious headslapping one.
So maybe progressively alter your SP as follows:
SELECT 'A' As C1, 'B' As C2, 'C' As C3
FROM T_ASFSTAG0
WHERE (T_ASFSTAG0.STANS1=@AnneeSelectionnee)
/*
AND ((isdate(T_ASFSTAG0.STCRT1)=1 and convert(datetime,convert(varchar,T_ASFSTAG0.STCRT1,103),103)>=convert(datetime,convert(varchar,@DateDernierImport,103),103)) or (isdate(T_ASFSTAG0.STMAJ1)=1 and convert(datetime,convert(varchar,T_ASFSTAG0.STMAJ1,103),103)>=convert(datetime,convert(varchar,@DateDernierImport,103),103)))
*/
Does that work? keep adding bits in till it breaks.
Also having a select * anywhere in code makes me nervous so maybe it could help things if you alter your view to select specific columns from the table.
|
|
|
|
|
I went one higher and did the following:
print '------right before the select-----------'
--2009
--Nov 14 2008 12:00AM
SELECT --line 67--
LTRIM(RTRIM(T_ASFSTAG0.STANS1)),
LTRIM(RTRIM(T_ASFSTAG0.STHFO1)), LTRIM(RTRIM(T_ASFSTAG0.STRSP1))
FROM T_ASFSTAG0
print '------after the select and before the insert-----------'
Essentially, it's just the referencing of the view that makes it fail.
By the way, the view is defined with specific fields...
SELECT STANS1, STSTA1--,... snip many others
FROM OPENQUERY(ISERIES_SQLDBF, 'SELECT * FROM tstadvdbf.ASFSTAG0') AS DerivedTable1
The error I got was:
Server: Msg 7399, Level 16, State 1, Procedure P_IMPORT_STAGES_PREPA, Line 67
OLE DB provider 'MSDASQL' reported an error. One or more arguments were reported invalid by the provider.
OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransactionJoin::JoinTransaction returned 0x80070057: One or more arguments were reported invalid by the provider.].
Alex
|
|
|
|
|
... I meant this star:
'SELECT * FROM tstadvdbf.ASFSTAG0'
maybe if you reduce the fields it will cut out the field causing trouble.
Some other ideas:
1. Compare the query plan for the working and failing case and see if there is any difference (for the linked server part of it)
2. try select * into table from yourview (I know know its a star ) and see what data types you get in the new table.
Maybe this will shed some light on it.
|
|
|
|
|
i create trigger in mainserver and connect it throuth local using linked server but if my main server is off then how can i run the trigger in local server without connecting main server
|
|
|
|
|
Please post your question only once. If you make a mistake then just edit the original.
|
|
|
|
|
rupal2 wrote: trigger in mainserver
+
rupal2 wrote: main server is off
=
No chance to fire the trigger. The local server isn't going to clone everything that the mainserver has. If the linked server is down, then everything within that server is down too.
Your options are relative limited; you could try to move the trigger to your local database, or turn on the main-server.
I are Troll
|
|
|
|
|