Is it a good idea to use SQL2005 MDF for audit, exception tracking

I am writing a web application that uses a Teradata database as the primary data source. While Teradata is great as a data warehouse and managing Terabytes of information it doesn't do as well when update or inserting. I was thinking of using a local SQL2005 MDF file to hold a few reference tables and an audit table to collect usage information and exception database to capture any errors.

There could be a few thousand users of the web application but no more than a couple hundred at a time.

I just trying to get some opinions on these technique. I am open to all comments and suggestions.

Thank You

Hi John,

If you're using the SQL Server as the auditing database and exception tracking, I think it will be fine.

Although there will be hundreds of connections to the main app, the exception will not be much and audit data size will not be huge then. Just connect the audit and exception handling module to your SQL Server database, and it will be OK.

HTH. If this does not answer you question, please feel free to mark it as Not Answered and post your reply. Thanks!

|||

Kevin

Thank you. I'm wondering if it is acceptable practice to run SQL Server 2005 express rather than installing a full SQL Server 2005 on the Advanced Server for doing the Audit tracking and exception reporting. The MDF file should never reach the 4GB limit of Express. Thank you again for your answer.

is it a bug?

it is the second time i am writing this problem.
I have a view named x_vw and while ? exec it as
select * from x_vw where field1='a1'
it returns the results which are field1='a1' but when I exec the query as
select * from x_vw
it returns all results but eccept the results which are field1='a1'
select * from X_VW where SiparisNo='a1'
returns data I want but the same data doesnt exists in the resultset of
select * from X_VW
its funny that I find a stupid solution for a stupid problem:))
select * from X_VW where SiparisNo like '%%'
I'm starving for explanation
is it a bug?
if so how can I report a bug to Microsoft?
CREATE view X_VW
as
SELECT sd.Sirketkod AS 'sirket',c.HesapKodu AS 'Sat?c?', c.Unvan,
sd.EvrakNo AS 'SiparisNo',
sd.MalKodu,
s.MalAdi,
SUM(sd.Miktar) AS 'Siparis',
dbo.DMGetIthSipIptalMikgamet(sd.SirketKod,sd.EvrakNo,sd.MalKodu) as
'iptalMiktar',
dbo.DMGetIthSipFatMikgamet(sd.SirketKod,sd.EvrakNo,sd.MalKodu) as
'Kars?lanan',
SUM(sd.Miktar)-dbo.DMGetIthSipFatMikgamet(sd.SirketKod,sd.EvrakNo,sd.MalKodu
) as 'Kalan',
dbo.DMGetIthStokGirMikgamet(sd.SirketKod,sd.EvrakNo,sd.MalKodu)as 'StokGiris
',
sh.EkSipNo,sh.aciklama,sh.SiparisTip,
s.kod1,
s.kod2,
s.kod3,
s.kod4,
s.kod5,
gamet2003.dbo.DMGetStokMik(sd.Sirketkod,sd.Malkodu)as 'stokmiktar',
Isnull(f.fiyat,0) as 'Ithalat_fiyati', Isnull(f.dovizkod,'yok') as 'Dovizkod
',
sd.Fiyat as 'SipFiyat', Isnull(sh.dovizkod,'yok') as 'SipDovizkod'
FROM gamet2003..SIP_D sd
inner join gamet2003..SIP_H sh on sh.SirketKod = sd.SirketKod AND sh.EvrakNo
= sd.EvrakNo
inner join gamet2003..CHK c on sd.SirketKod = c.SirketKod AND sd.Chk =
c.HesapKodu
inner join gamet2003..STK s on s.SirketKod = sd.SirketKod AND s.MalKodu =
sd.MalKodu
left join gamet2003..FIYATLIST f on f.sirketkod=sd.sirketkod and
s.malkodu=f.malkodu and f.Fiyatkod='ITHALAT'
WHERE sd.SirketKod='gamet' and sh.EvrakNo like 'IAS%'
and exists(select 1 from
(
select x.sirketkod,x.evrakno
from
(
select th.sirketkod,th.evrakno from gamet2003..ITH_SIP_D th,
gamet2003..SIP_D sp
where sp.sirketkod=th.sirketkod and sp.evrakno=th.evrakno and
sp.oldsirano=th.sirano
and (sp.beklet is null or sp.beklet=0)
group by th.sirketkod,th.evrakno
having abs(sum(sp.kalanmiktar-th.karsilananmiktar))>0
union all
select td.sirketkod,td.oldevrakno as evrakno from gamet2003..ITH_D td
left join gamet2003..STI_H sh on sh.sirketkod=td.sirketkod and
sh.Irsaliyeno=td.Evrakno and (sh.beklet is null or sh.beklet=0)
left join gamet2003..STI_D sd on sd.sirketkod=td.sirketkod and
sh.evrakno=sd.evrakno and (sd.beklet is null or sd.beklet=0)
where (td.beklet is null or td.beklet=0)
group by td.sirketkod,td.oldevrakno
having Isnull(sum(td.miktar),0)>Isnull(sum(sd.miktar),0)
)X
group by x.sirketkod,x.evrakno
)y
where y.sirketkod=sd.sirketkod and y.evrakno=sd.evrakno)and (sd.beklet is
null or sd.beklet=0)
group by sd.SirketKod,c.HesapKodu, c.Unvan, sd.EvrakNo,
sd.MalKodu,s.MalAdi,sh.EkSipNo,sh.aciklama,sh.SiparisTip,s.kod1,s.kod2,s.kod
3,s.kod4,s.kod5,f.fiyat,f.dovizkod,sd.Fiyat,sh.dovizkodHi
Call Microsoft PSS.
http://support.microsoft.com/common/international.aspx
You will be expected to be able to give them sufficient data to reproduce
the problem.
If it is not a bug, you will be charged for the support.
Regards
Mike
"POKEMON" wrote:

> it is the second time i am writing this problem.
> I have a view named x_vw and while ? exec it as
> select * from x_vw where field1='a1'
> it returns the results which are field1='a1' but when I exec the query as
> select * from x_vw
> it returns all results but eccept the results which are field1='a1'
> select * from X_VW where SiparisNo='a1'
> returns data I want but the same data doesnt exists in the resultset of
> select * from X_VW
> its funny that I find a stupid solution for a stupid problem:))
> select * from X_VW where SiparisNo like '%%'
> I'm starving for explanation
> is it a bug?
> if so how can I report a bug to Microsoft?
>
> CREATE view X_VW
> as
> SELECT sd.Sirketkod AS 'sirket',c.HesapKodu AS 'Sat?c?', c.Unvan,
> sd.EvrakNo AS 'SiparisNo',
> sd.MalKodu,
> s.MalAdi,
> SUM(sd.Miktar) AS 'Siparis',
> dbo.DMGetIthSipIptalMikgamet(sd.SirketKod,sd.EvrakNo,sd.MalKodu) as
> 'iptalMiktar',
> dbo.DMGetIthSipFatMikgamet(sd.SirketKod,sd.EvrakNo,sd.MalKodu) as
> 'Kars?lanan',
> SUM(sd.Miktar)-dbo.DMGetIthSipFatMikgamet(sd.SirketKod,sd.EvrakNo,sd.MalKo
du) as 'Kalan',
> dbo.DMGetIthStokGirMikgamet(sd.SirketKod,sd.EvrakNo,sd.MalKodu)as 'StokGir
is',
> sh.EkSipNo,sh.aciklama,sh.SiparisTip,
> s.kod1,
> s.kod2,
> s.kod3,
> s.kod4,
> s.kod5,
> gamet2003.dbo.DMGetStokMik(sd.Sirketkod,sd.Malkodu)as 'stokmiktar',
> Isnull(f.fiyat,0) as 'Ithalat_fiyati', Isnull(f.dovizkod,'yok') as 'Dovizk
od',
> sd.Fiyat as 'SipFiyat', Isnull(sh.dovizkod,'yok') as 'SipDovizkod'
> FROM gamet2003..SIP_D sd
> inner join gamet2003..SIP_H sh on sh.SirketKod = sd.SirketKod AND sh.Evrak
No
> = sd.EvrakNo
> inner join gamet2003..CHK c on sd.SirketKod = c.SirketKod AND sd.Chk =
> c.HesapKodu
> inner join gamet2003..STK s on s.SirketKod = sd.SirketKod AND s.MalKodu =
> sd.MalKodu
> left join gamet2003..FIYATLIST f on f.sirketkod=sd.sirketkod and
> s.malkodu=f.malkodu and f.Fiyatkod='ITHALAT'
> WHERE sd.SirketKod='gamet' and sh.EvrakNo like 'IAS%'
> and exists(select 1 from
> (
> select x.sirketkod,x.evrakno
> from
> (
> select th.sirketkod,th.evrakno from gamet2003..ITH_SIP_D th,
> gamet2003..SIP_D sp
> where sp.sirketkod=th.sirketkod and sp.evrakno=th.evrakno and
> sp.oldsirano=th.sirano
> and (sp.beklet is null or sp.beklet=0)
> group by th.sirketkod,th.evrakno
> having abs(sum(sp.kalanmiktar-th.karsilananmiktar))>0
> union all
> select td.sirketkod,td.oldevrakno as evrakno from gamet2003..ITH_D td
> left join gamet2003..STI_H sh on sh.sirketkod=td.sirketkod and
> sh.Irsaliyeno=td.Evrakno and (sh.beklet is null or sh.beklet=0)
> left join gamet2003..STI_D sd on sd.sirketkod=td.sirketkod and
> sh.evrakno=sd.evrakno and (sd.beklet is null or sd.beklet=0)
> where (td.beklet is null or td.beklet=0)
> group by td.sirketkod,td.oldevrakno
> having Isnull(sum(td.miktar),0)>Isnull(sum(sd.miktar),0)
> )X
> group by x.sirketkod,x.evrakno
> )y
> where y.sirketkod=sd.sirketkod and y.evrakno=sd.evrakno)and (sd.beklet is
> null or sd.beklet=0)
> group by sd.SirketKod,c.HesapKodu, c.Unvan, sd.EvrakNo,
> sd.MalKodu,s.MalAdi,sh.EkSipNo,sh.aciklama,sh.SiparisTip,s.kod1,s.kod2,s.k
od3,s.kod4,s.kod5,f.fiyat,f.dovizkod,sd.Fiyat,sh.dovizkod
>|||Hi POKEMON,
Your current view is very complex, too complex to analyse over a
newsgroup (and missing DDL and sample data). But still: It sounds like a
bug. The big question is: is this a known / documented bug or a new bug.
There are some known issues relating to parallellism. I would start by
adding the query hint OPTION (MAXDOP 1) when querying the view. So for
example:
select * from X_VW option (maxdop 1)
And of course, make sure you are running the latest SQL-Server service
pack (3a).
If money is not an issue, then you can contact Microsoft Support (as
posted by Mike). Otherwise, it makes sense to isolate the problem. You
can do this by removing all columns, UDFs, joins, etc. that do not
influence the problem.
For example: does the problem still remain if all UDFs are removed? Does
the problem still remain if the entire EXISTS subquery is removed? Does
the problem still remain if all outer joins are removed? Does the
problem still remain if you rewrite "gamet2003..ITH_SIP_D th,
gamet2003..SIP_D sp" as an INNER JOIN? Does the problem still remain if
you remove all OR operators by rewriting all "<something> is null or
<something> = 0" to "<something> is null"?
In the end, Microsoft will need a script to reproduce the problem.
HTH,
Gert-Jan

is it a bug

I am using SQL Server 2000 8.00.760 SP3
I am writing a TSQL Script to compare two databases and report any difference.
I am modifying the script sp_comparedb originally written by Viktor Gorodnichenko.
His script only compares the table schema. I am adding ability to compare indexes,
Primary Keys and Foreign keys. All is working well except at one place.
DESC Key. If an index is declared with a DESC column, then the following two
cases behave differently.
use database_name
select INDEXKEY_PROPERTY(id,indid,colid,'IsDescending')
from dbo.sysindexkeys
where id = object_id('any_table_name')
returns 1 for those columns of an index which has DESC clause.
Now the problem is that I don't use the database. Since the script
compares two different databases, it runs in the master databases
and loads all information from the databases_to_be_compared
into temp tables using EXEC call. So this is what I do
use master
set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
'select INDEXKEY_PROPERTY(id,indid,colid,''IsDescending'') from ' +
@.dbname + '.dbo.sysindexkeys'
When this query runs, it returns null for all columns.
This can easily be tested in Query Analyser as follows
use master
select INDEXKEY_PROPERTY(id,indid,colid,'IsDescending')
from different_database.dbo.sysindexkeys
where id = object_id('different_database.dbo.any_table_name')
Compare the results with the first query. While the first will return 0 or 1 for descending,
the second will always return NULL.
INDEXKEY_PROPERTY (and all similar functions) still works locally to the
database you are in, and in your case that is master.
What you can do is use sp_executesql to get the proper database context:
set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
'EXEC ' +
@.dbname + '..sp_executesql ''select
INDEXKEY_PROPERTY(id,indid,colid,''''IsDescending' ''') from
dbo.sysindexkeys'''
(Not sure if I did all the quotes right there)
Jacco Schalkwijk
SQL Server MVP
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3gthaoFea9jbU1@.individual.net...
>I am using SQL Server 2000 8.00.760 SP3
> I am writing a TSQL Script to compare two databases and report any
> difference.
> I am modifying the script sp_comparedb originally written by Viktor
> Gorodnichenko.
> His script only compares the table schema. I am adding ability to compare
> indexes,
> Primary Keys and Foreign keys. All is working well except at one place.
> DESC Key. If an index is declared with a DESC column, then the following
> two
> cases behave differently.
> use database_name
> select INDEXKEY_PROPERTY(id,indid,colid,'IsDescending')
> from dbo.sysindexkeys
> where id = object_id('any_table_name')
> returns 1 for those columns of an index which has DESC clause.
> Now the problem is that I don't use the database. Since the script
> compares two different databases, it runs in the master databases
> and loads all information from the databases_to_be_compared
> into temp tables using EXEC call. So this is what I do
> use master
> set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
> 'select
> INDEXKEY_PROPERTY(id,indid,colid,''IsDescending'') from ' +
> @.dbname + '.dbo.sysindexkeys'
> When this query runs, it returns null for all columns.
> This can easily be tested in Query Analyser as follows
> use master
> select INDEXKEY_PROPERTY(id,indid,colid,'IsDescending')
> from different_database.dbo.sysindexkeys
> where id = object_id('different_database.dbo.any_table_name')
> Compare the results with the first query. While the first will return 0 or
> 1 for descending,
> the second will always return NULL.
>
>
|||Thanks. Your suggestion works.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote in message
news:eVkQtYcbFHA.2980@.TK2MSFTNGP10.phx.gbl...
> INDEXKEY_PROPERTY (and all similar functions) still works locally to the database you are
> in, and in your case that is master.
> What you can do is use sp_executesql to get the proper database context:
> set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
> 'EXEC ' +
> @.dbname + '..sp_executesql ''select
> INDEXKEY_PROPERTY(id,indid,colid,''''IsDescending' ''') from dbo.sysindexkeys'''
> (Not sure if I did all the quotes right there)
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Data Cruncher" <dcruncher4@.netscape.net> wrote in message
> news:3gthaoFea9jbU1@.individual.net...
>

is it a bug

I am using SQL Server 2000 8.00.760 SP3
I am writing a TSQL Script to compare two databases and report any differenc
e.
I am modifying the script sp_comparedb originally written by Viktor Gorodni
chenko.
His script only compares the table schema. I am adding ability to compare in
dexes,
Primary Keys and Foreign keys. All is working well except at one place.
DESC Key. If an index is declared with a DESC column, then the following two
cases behave differently.
use database_name
select INDEXKEY_PROPERTY(id,indid,colid,'IsDesc
ending')
from dbo.sysindexkeys
where id = object_id('any_table_name')
returns 1 for those columns of an index which has DESC clause.
Now the problem is that I don't use the database. Since the script
compares two different databases, it runs in the master databases
and loads all information from the databases_to_be_compared
into temp tables using EXEC call. So this is what I do
use master
set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
'select INDEXKEY_PROPERTY(id,indid,colid,''IsDes
cending'') from ' +
@.dbname + '.dbo.sysindexkeys'
When this query runs, it returns null for all columns.
This can easily be tested in Query Analyser as follows
use master
select INDEXKEY_PROPERTY(id,indid,colid,'IsDesc
ending')
from different_database.dbo.sysindexkeys
where id = object_id('different_database.dbo.any_table_name')
Compare the results with the first query. While the first will return 0 or 1
for descending,
the second will always return NULL.INDEXKEY_PROPERTY (and all similar functions) still works locally to the
database you are in, and in your case that is master.
What you can do is use sp_executesql to get the proper database context:
set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
'EXEC ' +
@.dbname + '..sp_executesql ''select
INDEXKEY_PROPERTY(id,indid,colid,''''IsD
escending'''') from
dbo.sysindexkeys'''
(Not sure if I did all the quotes right there)
Jacco Schalkwijk
SQL Server MVP
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3gthaoFea9jbU1@.individual.net...
>I am using SQL Server 2000 8.00.760 SP3
> I am writing a TSQL Script to compare two databases and report any
> difference.
> I am modifying the script sp_comparedb originally written by Viktor
> Gorodnichenko.
> His script only compares the table schema. I am adding ability to compare
> indexes,
> Primary Keys and Foreign keys. All is working well except at one place.
> DESC Key. If an index is declared with a DESC column, then the following
> two
> cases behave differently.
> use database_name
> select INDEXKEY_PROPERTY(id,indid,colid,'IsDesc
ending')
> from dbo.sysindexkeys
> where id = object_id('any_table_name')
> returns 1 for those columns of an index which has DESC clause.
> Now the problem is that I don't use the database. Since the script
> compares two different databases, it runs in the master databases
> and loads all information from the databases_to_be_compared
> into temp tables using EXEC call. So this is what I do
> use master
> set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
> 'select
> INDEXKEY_PROPERTY(id,indid,colid,''IsDes
cending'') from ' +
> @.dbname + '.dbo.sysindexkeys'
> When this query runs, it returns null for all columns.
> This can easily be tested in Query Analyser as follows
> use master
> select INDEXKEY_PROPERTY(id,indid,colid,'IsDesc
ending')
> from different_database.dbo.sysindexkeys
> where id = object_id('different_database.dbo.any_table_name')
> Compare the results with the first query. While the first will return 0 or
> 1 for descending,
> the second will always return NULL.
>
>|||Thanks. Your suggestion works.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message
news:eVkQtYcbFHA.2980@.TK2MSFTNGP10.phx.gbl...
> INDEXKEY_PROPERTY (and all similar functions) still works locally to the d
atabase you are
> in, and in your case that is master.
> What you can do is use sp_executesql to get the proper database context:
> set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
> 'EXEC ' +
> @.dbname + '..sp_executesql ''select
> INDEXKEY_PROPERTY(id,indid,colid,''''IsD
escending'''') from dbo.sysindexke
ys'''
> (Not sure if I did all the quotes right there)
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Data Cruncher" <dcruncher4@.netscape.net> wrote in message
> news:3gthaoFea9jbU1@.individual.net...
>

is it a bug

I am using SQL Server 2000 8.00.760 SP3
I am writing a TSQL Script to compare two databases and report any difference.
I am modifying the script sp_comparedb originally written by Viktor Gorodnichenko.
His script only compares the table schema. I am adding ability to compare indexes,
Primary Keys and Foreign keys. All is working well except at one place.
DESC Key. If an index is declared with a DESC column, then the following two
cases behave differently.
use database_name
select INDEXKEY_PROPERTY(id,indid,colid,'IsDescending')
from dbo.sysindexkeys
where id = object_id('any_table_name')
returns 1 for those columns of an index which has DESC clause.
Now the problem is that I don't use the database. Since the script
compares two different databases, it runs in the master databases
and loads all information from the databases_to_be_compared
into temp tables using EXEC call. So this is what I do
use master
set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
'select INDEXKEY_PROPERTY(id,indid,colid,''IsDescending'') from ' +
@.dbname + '.dbo.sysindexkeys'
When this query runs, it returns null for all columns.
This can easily be tested in Query Analyser as follows
use master
select INDEXKEY_PROPERTY(id,indid,colid,'IsDescending')
from different_database.dbo.sysindexkeys
where id = object_id('different_database.dbo.any_table_name')
Compare the results with the first query. While the first will return 0 or 1 for descending,
the second will always return NULL.INDEXKEY_PROPERTY (and all similar functions) still works locally to the
database you are in, and in your case that is master.
What you can do is use sp_executesql to get the proper database context:
set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
'EXEC ' +
@.dbname + '..sp_executesql ''select
INDEXKEY_PROPERTY(id,indid,colid,''''IsDescending'''') from
dbo.sysindexkeys'''
(Not sure if I did all the quotes right there)
--
Jacco Schalkwijk
SQL Server MVP
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3gthaoFea9jbU1@.individual.net...
>I am using SQL Server 2000 8.00.760 SP3
> I am writing a TSQL Script to compare two databases and report any
> difference.
> I am modifying the script sp_comparedb originally written by Viktor
> Gorodnichenko.
> His script only compares the table schema. I am adding ability to compare
> indexes,
> Primary Keys and Foreign keys. All is working well except at one place.
> DESC Key. If an index is declared with a DESC column, then the following
> two
> cases behave differently.
> use database_name
> select INDEXKEY_PROPERTY(id,indid,colid,'IsDescending')
> from dbo.sysindexkeys
> where id = object_id('any_table_name')
> returns 1 for those columns of an index which has DESC clause.
> Now the problem is that I don't use the database. Since the script
> compares two different databases, it runs in the master databases
> and loads all information from the databases_to_be_compared
> into temp tables using EXEC call. So this is what I do
> use master
> set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
> 'select
> INDEXKEY_PROPERTY(id,indid,colid,''IsDescending'') from ' +
> @.dbname + '.dbo.sysindexkeys'
> When this query runs, it returns null for all columns.
> This can easily be tested in Query Analyser as follows
> use master
> select INDEXKEY_PROPERTY(id,indid,colid,'IsDescending')
> from different_database.dbo.sysindexkeys
> where id = object_id('different_database.dbo.any_table_name')
> Compare the results with the first query. While the first will return 0 or
> 1 for descending,
> the second will always return NULL.
>
>|||Thanks. Your suggestion works.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote in message
news:eVkQtYcbFHA.2980@.TK2MSFTNGP10.phx.gbl...
> INDEXKEY_PROPERTY (and all similar functions) still works locally to the database you are
> in, and in your case that is master.
> What you can do is use sp_executesql to get the proper database context:
> set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
> 'EXEC ' +
> @.dbname + '..sp_executesql ''select
> INDEXKEY_PROPERTY(id,indid,colid,''''IsDescending'''') from dbo.sysindexkeys'''
> (Not sure if I did all the quotes right there)
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Data Cruncher" <dcruncher4@.netscape.net> wrote in message
> news:3gthaoFea9jbU1@.individual.net...
>>I am using SQL Server 2000 8.00.760 SP3
>> I am writing a TSQL Script to compare two databases and report any difference.
>> I am modifying the script sp_comparedb originally written by Viktor Gorodnichenko.
>> His script only compares the table schema. I am adding ability to compare indexes,
>> Primary Keys and Foreign keys. All is working well except at one place.
>> DESC Key. If an index is declared with a DESC column, then the following two
>> cases behave differently.
>> use database_name
>> select INDEXKEY_PROPERTY(id,indid,colid,'IsDescending')
>> from dbo.sysindexkeys
>> where id = object_id('any_table_name')
>> returns 1 for those columns of an index which has DESC clause.
>> Now the problem is that I don't use the database. Since the script
>> compares two different databases, it runs in the master databases
>> and loads all information from the databases_to_be_compared
>> into temp tables using EXEC call. So this is what I do
>> use master
>> set @.sqlstring = 'insert into #tmp_sysindexkeys ' +
>> 'select INDEXKEY_PROPERTY(id,indid,colid,''IsDescending'') from '
>> +
>> @.dbname + '.dbo.sysindexkeys'
>> When this query runs, it returns null for all columns.
>> This can easily be tested in Query Analyser as follows
>> use master
>> select INDEXKEY_PROPERTY(id,indid,colid,'IsDescending')
>> from different_database.dbo.sysindexkeys
>> where id = object_id('different_database.dbo.any_table_name')
>> Compare the results with the first query. While the first will return 0 or 1 for
>> descending,
>> the second will always return NULL.
>>
>>
>