|
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 
|
|
|
|
|
i create trigger in mainserver and connect it throuth localserver using linked server but if my mainserver is off then how can i run the trigger in local server.
|
|
|
|
|
Hi all,
I have a problem with executing function in Oracle 10g.
Below mentioned is the function :-
FUNCTION UnpackArray
(
Source IN VARCHAR2 DEFAULT NULL,
Delimiter IN CHAR DEFAULT ','
)
RETURN reSourceArray0 PIPELINED
IS
SourceArray00 SourceArray0:=SourceArray0(NULL);
TYPE REFCURSOR IS REF CURSOR;
CURSOR0 REFCURSOR;
DelLen int;
Pos int;
Cnt int;
str int;
LEN int;
Holder VARCHAR2(255);
BEGIN
IF Source is null or Delimiter is null THEN
Return;
END IF;
IF RTRIM(LTRIM(Source)) = ' ' THEN
Return;
END IF;
SELECT LENGTH(RTRIM(Delimiter)) INTO DelLen FROM DUAL;
SELECT INSTR(UPPER(Source), UPPER(Delimiter)) INTO Pos FROM DUAL;
IF Pos = 0 THEN
BEGIN
INSERT INTO UnpackArray_TBL
( Data )
VALUES ( Source );
return;
OPEN CURSOR0 FOR SELECT * FROM UnpackArray_TBL;
END;
END IF;
SELECT 1 INTO str FROM DUAL;
<< LABEL4 >>
WHILE Pos > 0
LOOP
BEGIN
SELECT Pos - str INTO len FROM DUAL;
SELECT SUBSTR(Source, str, len) INTO Holder FROM DUAL;
INSERT INTO UnpackArray_TBL
VALUES ( Holder );
SELECT Pos + DelLen INTO str FROM DUAL;
SELECT INSTR(UPPER(Source), UPPER(Delimiter), str) INTO Pos FROM DUAL;
OPEN CURSOR0 FOR SELECT * FROM UnpackArray_TBL;
END;
END LOOP;
SELECT SUBSTR(Source, str, length(RTRIM(Source))) INTO Holder FROM DUAL;
IF length(RTRIM(Holder)) > 0 THEN
INSERT INTO UnpackArray_TBL
VALUES ( Holder );
OPEN CURSOR0 FOR SELECT * FROM UnpackArray_TBL;
END IF;
Return; LOOP
FETCH CURSOR0 INTO
SourceArray00.Data;
EXIT WHEN CURSOR0%NOTFOUND;
PIPE ROW(SourceArray00);
END LOOP;
CLOSE CURSOR0;
RETURN;
END;
While executing this am getting the error like:
Running "EDOCS"."UNPACKARRAY" ( SOURCE = admin, DELIMITER = ).
Parameter 'RETURN_VALUE': No size set for variable length data type: String.
can anybody help me to solve this...?
Thanks in advance.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
