Page 1 of 1
problem with metadata in SQL 2008 using sdac 4.7
Posted: Wed 25 Jul 2012 05:57
by masi56
when i use the query below in sql 2000 using sdac 4.7 everything is ok, i mean the metadata for fields is ok, i can find key fields... DetectIdentityField works fine, where field1 is autoincrement:
select field1, field2
from table1
where condition1
union all
select field1, field2
from table1
where condition2
but when i use this query in sql 2008, i face problem and sdac cannot find autoincrement field...
so, is there any problem with 2008 or i should change any configuration?
Re: problem with metadata in SQL 2008 using sdac 4.7
Posted: Wed 25 Jul 2012 10:43
by AndreyZ
Hello,
The point is that in this case SQL Server 2008 doesn't return information about IDENTITY fields (autoincrement). We cannot influence such SQL Server behaviour. As a solution, you can use the following SQL statement:
Code: Select all
select field1, field2
from table1
where (condition1) or (condition2)
Re: problem with metadata in SQL 2008 using sdac 4.7
Posted: Wed 25 Jul 2012 11:00
by masi56
ok
but it's not possible to use operator OR always..
such as
Code: Select all
select top 100 field1, field2
from table1
where condition1
union all
select field1, field2
from table1
where condition2
where i need to fetch 100 top records according to condition1 plus one extra record according condition2 !
is there any solution ?
Re: problem with metadata in SQL 2008 using sdac 4.7
Posted: Wed 25 Jul 2012 13:47
by AndreyZ
Unfortunately there is no way to obtain the result of this query as one resultset and still obtain the information about IDENTITY fields. That is the SQL Server 2008 behaviour.
As a workaround, you can split two SQL statements with the ';' symbol, like this:
Code: Select all
select top 100 field1, field2
from table1
where condition1;
select field1, field2
from table1
where condition2
, and sequentially work with two result sets:
Code: Select all
MSQuery1.Open; // here the result of the first statement (before the ';' symbol) is obtained
while not MSQuery1.Eof do begin
// some work
MSQuery1.Next;
end;
MSQuery1.OpenNext; // here the result of the second statement (after the ';' symbol) is obtained
while not MSQuery1.Eof do begin
// some work
MSQuery1.Next;
end;