When I run Integrity check with option attempt to repair
minor problem seems like there is no integrity checking on
databases whic are not in single user mode.
If I disable that option seems that integrity checking is
running.
Also Inegrity Check jobs doesn't report any error but when
I run dbcc checkdb from analyzer I can see 2-3 2511 errors.
Is the Maintenance plan realy reliable or I should
schedule job to run dbcc checkdb. We are running SQL7 sp3
on W2000.
ThanksWe are running dbcc dbreindex on every table in the
database, after Integrity Checks, Is it possible that
index corruption is generated from reindexing command?
Thanks
>--Original Message--
>It is advisable not to have the attempt to repair minor
problems option set
>in the maintenance paln and this issue with it having to
be in single user
>mode is one of the main reasons. Generally it either
can't get it into
>single user mode and fails or leaves it in single user
mode after it
>finishes, neither of which is desirable. With regard to
your specific
>errors, have your tried dropping and creating the indexes
affected
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>"milan" <mmirce01@.yahoo.ca> wrote in message
>news:034201c3507b$2a92a940$a101280a@.phx.gbl...
>When I run Integrity check with option attempt to repair
>minor problem seems like there is no integrity checking on
>databases whic are not in single user mode.
>If I disable that option seems that integrity checking is
>running.
>Also Inegrity Check jobs doesn't report any error but when
>I run dbcc checkdb from analyzer I can see 2-3 2511
errors.
>Is the Maintenance plan realy reliable or I should
>schedule job to run dbcc checkdb. We are running SQL7 sp3
>on W2000.
>Thanks
>
>.
>
Showing posts with label bug. Show all posts
Showing posts with label bug. Show all posts
Friday, March 9, 2012
Wednesday, March 7, 2012
Is it a SQL's bug?
There is not a field named as 'CRSID' in the table TMatch but the SQL Server
would return all the records in table tcrsmgr:
SELECT *
FROM dbo.TCRSMGR
WHERE (CRSID IN
(SELECT CRSID
FROM TMatch
WHERE tDATE = dbo.fDateOf('2005-7-20')))This is correct behavior because the TCRSMGR.CRSID is used in the subquery.
To avoid ambiguity, qualify column names in the subquery with the desired
table name or alias like the example below. In this case, you'll get an
error because the TMatch.CRSID column doesn't exist.
SELECT *
FROM dbo.TCRSMGR
WHERE (CRSID IN
(SELECT TMatch.CRSID
FROM TMatch
WHERE TMatch.tDATE = dbo.fDateOf('2005-7-20')))
Hope this helps.
Dan Guzman
SQL Server MVP
"Half Nitto" <mails2me@.invalidemail.com> wrote in message
news:ud6vP6XlFHA.2852@.TK2MSFTNGP14.phx.gbl...
> There is not a field named as 'CRSID' in the table TMatch but the SQL
> Server would return all the records in table tcrsmgr:
> SELECT *
> FROM dbo.TCRSMGR
> WHERE (CRSID IN
> (SELECT CRSID
> FROM TMatch
> WHERE tDATE = dbo.fDateOf('2005-7-20')))
>|||This is expected behavior. The inner reference to CRSID does
not include a table alias or table name. As a result, it is resolved
as TMatch.CRSID if that column exists, and if not, to
TCRSMGR.CRSID, if that column exists (which it does -
if it did not, you would get an error).
You now have a correlated subquery, and for each row of
TCRSMGR, that correlated subquery is
SELECT TCRSMGR.CRSID FROM TMatch
WHERE tDATE = dbo.fDateOf('2005-7-20')
So long as CRSID has at least one row for which
tDATE = dbo.fDateOf('2005-7-20'), then the WHERE
clause of the entire query is true, and so all rows of
TCRSMGR will be returned.
Outer references must always be valid in subqueries,
or it would be impossible to write a correlated subquery.
For example, no one thinks it's a bug that this works (to
select the biggest order for each employee)
select OrderID, OrderDate, OrderTotal
from Orders as O1
where OrderTotal = (
select max(OrderTotal)
from Orders as O2
where O2.EmployeeID = O1.EmployeeID
)
The reference to O1.EmployeeID is perfectly valid.
Here, the O1 alias is required to avoid ambiguity, but
aliases can be omitted when there is no chance of
ambiguity, and unfortunately in your case, omitting the
alias caused a programming error to go unnoticed.
Here's another example that might not seem so surprising
if not useful:
select * from T
where thisColumn = (
select T.thisColumn
)
You would expect this to return all rows of T with
non-null thisColumn values. Though there is not
even a table mentioned in the subquery, the reference
to T.thisColumn is valid and correlates with the rows
of the outer query. Since thisColumn would not be
ambiguous here, the same query can be written as
select * from T
where thisColumn = (
select thisColumn
)
or, if table X has at least one row,
select * from T
where thisColumn = (
select thisColumn from X
)
The moral of the story? In queries that refer to more
than one table, if not always, qualify columns with the
table you think they come from.
Had you done this here, and written
SELECT *
FROM dbo.TCRSMGR
WHERE (dbo.TCRSMGR.CRSID IN
(SELECT TMatch.CRSID
FROM TMatch
WHERE TMatch.tDATE = dbo.fDateOf('2005-7-20')))
you would have caught the programming error. Most all programming
languages are like this, in allowing inner declarations to override outer
ones, while allowing all outer declarations to be visible within sub-blocks,
if there is no shadowing inner declaration.
int i, j;
...
{
int i, k;
// you can refer to i, j and k here. j refers to the variables declared
// in the outer block, and i and k refers to the variable declared in
// the inner block.
Steve Kass
Drew University
Half Nitto wrote:
>There is not a field named as 'CRSID' in the table TMatch but the SQL Serve
r
>would return all the records in table tcrsmgr:
>SELECT *
>FROM dbo.TCRSMGR
>WHERE (CRSID IN
> (SELECT CRSID
> FROM TMatch
> WHERE tDATE = dbo.fDateOf('2005-7-20')))
>
>|||Have a look at
http://toponewithties.blogspot.com/...es_archive.html
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Half Nitto" <mails2me@.invalidemail.com> wrote in message
news:ud6vP6XlFHA.2852@.TK2MSFTNGP14.phx.gbl...
> There is not a field named as 'CRSID' in the table TMatch but the SQL
> Server would return all the records in table tcrsmgr:
> SELECT *
> FROM dbo.TCRSMGR
> WHERE (CRSID IN
> (SELECT CRSID
> FROM TMatch
> WHERE tDATE = dbo.fDateOf('2005-7-20')))
>
would return all the records in table tcrsmgr:
SELECT *
FROM dbo.TCRSMGR
WHERE (CRSID IN
(SELECT CRSID
FROM TMatch
WHERE tDATE = dbo.fDateOf('2005-7-20')))This is correct behavior because the TCRSMGR.CRSID is used in the subquery.
To avoid ambiguity, qualify column names in the subquery with the desired
table name or alias like the example below. In this case, you'll get an
error because the TMatch.CRSID column doesn't exist.
SELECT *
FROM dbo.TCRSMGR
WHERE (CRSID IN
(SELECT TMatch.CRSID
FROM TMatch
WHERE TMatch.tDATE = dbo.fDateOf('2005-7-20')))
Hope this helps.
Dan Guzman
SQL Server MVP
"Half Nitto" <mails2me@.invalidemail.com> wrote in message
news:ud6vP6XlFHA.2852@.TK2MSFTNGP14.phx.gbl...
> There is not a field named as 'CRSID' in the table TMatch but the SQL
> Server would return all the records in table tcrsmgr:
> SELECT *
> FROM dbo.TCRSMGR
> WHERE (CRSID IN
> (SELECT CRSID
> FROM TMatch
> WHERE tDATE = dbo.fDateOf('2005-7-20')))
>|||This is expected behavior. The inner reference to CRSID does
not include a table alias or table name. As a result, it is resolved
as TMatch.CRSID if that column exists, and if not, to
TCRSMGR.CRSID, if that column exists (which it does -
if it did not, you would get an error).
You now have a correlated subquery, and for each row of
TCRSMGR, that correlated subquery is
SELECT TCRSMGR.CRSID FROM TMatch
WHERE tDATE = dbo.fDateOf('2005-7-20')
So long as CRSID has at least one row for which
tDATE = dbo.fDateOf('2005-7-20'), then the WHERE
clause of the entire query is true, and so all rows of
TCRSMGR will be returned.
Outer references must always be valid in subqueries,
or it would be impossible to write a correlated subquery.
For example, no one thinks it's a bug that this works (to
select the biggest order for each employee)
select OrderID, OrderDate, OrderTotal
from Orders as O1
where OrderTotal = (
select max(OrderTotal)
from Orders as O2
where O2.EmployeeID = O1.EmployeeID
)
The reference to O1.EmployeeID is perfectly valid.
Here, the O1 alias is required to avoid ambiguity, but
aliases can be omitted when there is no chance of
ambiguity, and unfortunately in your case, omitting the
alias caused a programming error to go unnoticed.
Here's another example that might not seem so surprising
if not useful:
select * from T
where thisColumn = (
select T.thisColumn
)
You would expect this to return all rows of T with
non-null thisColumn values. Though there is not
even a table mentioned in the subquery, the reference
to T.thisColumn is valid and correlates with the rows
of the outer query. Since thisColumn would not be
ambiguous here, the same query can be written as
select * from T
where thisColumn = (
select thisColumn
)
or, if table X has at least one row,
select * from T
where thisColumn = (
select thisColumn from X
)
The moral of the story? In queries that refer to more
than one table, if not always, qualify columns with the
table you think they come from.
Had you done this here, and written
SELECT *
FROM dbo.TCRSMGR
WHERE (dbo.TCRSMGR.CRSID IN
(SELECT TMatch.CRSID
FROM TMatch
WHERE TMatch.tDATE = dbo.fDateOf('2005-7-20')))
you would have caught the programming error. Most all programming
languages are like this, in allowing inner declarations to override outer
ones, while allowing all outer declarations to be visible within sub-blocks,
if there is no shadowing inner declaration.
int i, j;
...
{
int i, k;
// you can refer to i, j and k here. j refers to the variables declared
// in the outer block, and i and k refers to the variable declared in
// the inner block.
Steve Kass
Drew University
Half Nitto wrote:
>There is not a field named as 'CRSID' in the table TMatch but the SQL Serve
r
>would return all the records in table tcrsmgr:
>SELECT *
>FROM dbo.TCRSMGR
>WHERE (CRSID IN
> (SELECT CRSID
> FROM TMatch
> WHERE tDATE = dbo.fDateOf('2005-7-20')))
>
>|||Have a look at
http://toponewithties.blogspot.com/...es_archive.html
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Half Nitto" <mails2me@.invalidemail.com> wrote in message
news:ud6vP6XlFHA.2852@.TK2MSFTNGP14.phx.gbl...
> There is not a field named as 'CRSID' in the table TMatch but the SQL
> Server would return all the records in table tcrsmgr:
> SELECT *
> FROM dbo.TCRSMGR
> WHERE (CRSID IN
> (SELECT CRSID
> FROM TMatch
> WHERE tDATE = dbo.fDateOf('2005-7-20')))
>
Is it a SQL Server 2000 bug
Hi, I'm currently using SQL Server 2000 SP3.
I'm facing some problems which make me think of a SQL Server BUG.
I'm facing some problems which make me think of a SQL Server BUG.
When executing a query, it seems that SQL Server tries to convert data
even if not in the resultset -> This leads to SQL Server error.
Here is a very easy sample to reproduce it :
SET NOCOUNT ON
create table tempdb..test ( coldate varchar(30) )
insert into tempdb..test values ( '1900/01/01 00:00:00' )
insert into tempdb..test values ( '2005/01/01 00:00:00' )
insert into tempdb..test values ( 'Invalid date' )
select * from tempdb..test
where isdate( coldate ) = 1
and convert( datetime, coldate ) > GETDATE()
SQL Server output is the following :
Server: Msg 241, Level 16, State 1, Line 10
Syntax error converting datetime from character string.
SQL Server considers as an error the 'Invalid date' even if a filter
ISDATE = 1 is applied...
I guess that I am not able to determine the order in which filters are
applied as it is SQL Server optimizer job... However, this should lead
to an error only if conversion fails on a line of the resultset after
application of all other filters...
What am I doing wrong ?
Thanks
PatrickNot a bug. The execution order is determined by the query optimizer.
There is no reason to suppose that the ISDATE expression will always
execute first.
What are you doing wrong? Firstly, you are writing queries against
dates stored as strings. If at all possible you should convert the
dates to use a proper DATETIME column. If you really cannot do that
then you should be able to rewrite your query using a derived table.
The second thing wrong here is that you are relying on an implicit
conversion from a non-standard date format. Those conversions are
sensitive to local server and connection settings so avoid them. Try
the following:
SELECT coldate
FROM
(SELECT REPLACE(REPLACE(coldate,'/','-'),' ','T') AS coldate
FROM tempdb..test
WHERE ISDATE(REPLACE(REPLACE(coldate,'/','-'),' ','T'))=1) AS T
WHERE CAST(coldate AS DATETIME) > CURRENT_TIMESTAMP
--
David Portas
SQL Server MVP
--|||Thanks for the answer.
However, I thought that the optimizer was designed only to optimize the
query but resultset was independant of the order of execution of
filters.
Here, it appears that depending on the order of application of filters,
result is not the same...
I guess that this is a special case due to the abuse of conversion
use...
Thanks
Patrick
*** Sent via Developersdex http://www.developersdex.com ***|||Hi, Patrick
For details about this problem and possible solutions, see this article
by Itzik Ben-Gan, SQL Server MVP:
http://www.windowsitpro.com/Windows...148/pg/1/1.html
Razvan|||No, an SQL Statement is a specification of the result, and
theoretically, it is created "all at once". It is not good enough that
you will get correct results if the query is executed "left to right,
top to bottom", because that is not how SQL works.
A solution for your problem could be to place the two related predicates
in a case expression. A case expression always has to be evaluated from
left to right.
select * from tempdb..test
where CASE WHEN isdate( coldate ) = 0 THEN 0
WHEN convert( datetime, coldate ) > GETDATE() THEN 1
ELSE 0 END = 1
Hope this helps,
Gert-Jan
Patrick Fiche wrote:
> Thanks for the answer.
> However, I thought that the optimizer was designed only to optimize the
> query but resultset was independant of the order of execution of
> filters.
> Here, it appears that depending on the order of application of filters,
> result is not the same...
> I guess that this is a special case due to the abuse of conversion
> use...
> Thanks
> Patrick
> *** Sent via Developersdex http://www.developersdex.com ***|||Here is another way:
select * from tempdb..test where case when isdate( coldate ) = 1
then convert( datetime, coldate ) else getdate() end > getdate()
Razvan|||Thanks all for your help.
Is it a Compatible bug in SQL Server 2005 ?
Hi, all here,
I use SQL Server 2005 standard edition ,
I choose to use table(table stored in Oracle9i) as source for mining structure, and I use
Oracle Provider for OLE DB(or Microsoft OLE DB Provider for Oracle).I set one column as logic key and this column stored chinese data.Deployment was successful. When I processed the mining structure,
an error happened:
Warning 0x80202066:Data Flow Task :Connot retieve the column code page info from OLE DB provider.If the comopenent supports the "DefaultCodePage" property,the code page from that property will be used .Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.
But I created the same mining stucture and processed it successfully in SQL Server 2000. Is it a Compatible bug in SQL Server 2005？
It looks like the OLE DB provider for Oracle requires a code page to be specified because there is locale-specific (Chinese) data in the column.
There is a similar posting on the SSIS forum which you may find useful:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PageIndex=2&SiteID=1&PostID=107027
HTH,
Akshai
|||
hi,Akshai,
thanks!
I had modified the Propertie:CheckDistinctRecordSortOrder in my SSAS,
and the mining model had been processed successfully!
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
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 have a calendar control in my report
I am clicking on it in a few times and selecting same date. After two or
three times I am getting a run-time error:
Object does not support this property or method.if it looks like a bug and it sounds like a bug
but some obsolete company sits there and says it's a feature?
it's yet another symptom you should have gone with crystal reports
Mark Goldin wrote:
> I have a calendar control in my report
> I am clicking on it in a few times and selecting same date. After two or
> three times I am getting a run-time error:
> Object does not support this property or method.
I am clicking on it in a few times and selecting same date. After two or
three times I am getting a run-time error:
Object does not support this property or method.if it looks like a bug and it sounds like a bug
but some obsolete company sits there and says it's a feature?
it's yet another symptom you should have gone with crystal reports
Mark Goldin wrote:
> I have a calendar control in my report
> I am clicking on it in a few times and selecting same date. After two or
> three times I am getting a run-time error:
> Object does not support this property or method.
Is it a bug?
Hi,
I'm running SQL 2K5 with SP1.
Whenever I try to run an import from Oracle linked server to my sql server (of course, there are no problems with the import), and if I try to expand any of the folders like Tables, Programmability -> Stored procedures, functions...............I'm not able to see any tables and SPs etc.
It times out and displays error message "Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222).
The linked server (Oracle) import takes 30 to 40 mins and each time it only touches a single table and I don't think this should cause that kind of error but it is.
I'm running this import from Management Studio.
Anybody has any thoughts?
Thanks,
Siva.
SQL Server Management Studio needs some locks to provide the listings. Certain types of data import processes can block other access to the database.
If you can use another method to import data that doesn't lock so much, you can eliminate this problem. I don't know any specific solutions, unfortunately.
-Ryan / Kardax
|||Hi Ryan,
Thanks for the info.
I did couple of tests on this. It looks like this problem is happening only when importing data but not when querying the linked server inspite of querying also takes lot of time.
Then I tried this import using SSIS and I'm not getting those problems mentioned earlier.
But to be frank, getting data from linked server is very straight forward (when you don't have to do any manipulations with data) and easy compared to SSIS (in my case).
Hopefully, Microsoft will look into this.
Thanks,
Siva.
Is it a bug of SQL Server 2000 SP4?
Database backup file:
http://www.keepmyfile.com/download/c58b2a565144
Environment:
SQL Server 2000 SP4
Problem:
The following two statements returns different number of records:
Exec GenPeriodical1 102, null, '20050601', '20050630', null, null, 0
SELECT *
FROM dbo.OtherFee (null, '20050601', '20050630', null, null, 0)
WHERE flow_id = 102
This problem wasn't found in SQL Server 2000 original version and SQL Server
2005.
Any help is appreciated!Well, seems like a bug.
You can fix it by rearranginf the FROM clause in the function in the
following manner.
FROM action_room_req2 arr
JOIN flow_action fa ON arr.flow_id = fa.flow_id
JOIN cust_action ca ON ca.valid = 0 AND fa.action_id = ca.id
JOIN action_room ar ON ar.action_id = ca.id AND ar.code = 0
JOIN customer c ON ca.customer_id = c.id
LEFT JOIN turn_rule tr ON arr.req_type = 'Mall' and arr.ref_id = tr.id
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"hghua" <hghua@.discussions.microsoft.com> wrote in message
news:A9FB112B-AD45-463A-A167-CFAF77E20B5D@.microsoft.com...
> Database backup file:
> http://www.keepmyfile.com/download/c58b2a565144
> Environment:
> SQL Server 2000 SP4
> Problem:
> The following two statements returns different number of records:
> Exec GenPeriodical1 102, null, '20050601', '20050630', null, null, 0
> SELECT *
> FROM dbo.OtherFee (null, '20050601', '20050630', null, null, 0)
> WHERE flow_id = 102
> This problem wasn't found in SQL Server 2000 original version and SQL
> Server
> 2005.
> Any help is appreciated!|||Thanks a lot! That works!
Hope Microsoft will solve the problem.
"Roji. P. Thomas" wrote:
> Well, seems like a bug.
> You can fix it by rearranginf the FROM clause in the function in the
> following manner.
> FROM action_room_req2 arr
> JOIN flow_action fa ON arr.flow_id = fa.flow_id
> JOIN cust_action ca ON ca.valid = 0 AND fa.action_id = ca.id
> JOIN action_room ar ON ar.action_id = ca.id AND ar.code = 0
> JOIN customer c ON ca.customer_id = c.id
> LEFT JOIN turn_rule tr ON arr.req_type = 'Mall' and arr.ref_id = tr.id
> --
> Regards
> Roji. P. Thomas
> http://toponewithties.blogspot.com
> "hghua" <hghua@.discussions.microsoft.com> wrote in message
> news:A9FB112B-AD45-463A-A167-CFAF77E20B5D@.microsoft.com...
>
>
http://www.keepmyfile.com/download/c58b2a565144
Environment:
SQL Server 2000 SP4
Problem:
The following two statements returns different number of records:
Exec GenPeriodical1 102, null, '20050601', '20050630', null, null, 0
SELECT *
FROM dbo.OtherFee (null, '20050601', '20050630', null, null, 0)
WHERE flow_id = 102
This problem wasn't found in SQL Server 2000 original version and SQL Server
2005.
Any help is appreciated!Well, seems like a bug.
You can fix it by rearranginf the FROM clause in the function in the
following manner.
FROM action_room_req2 arr
JOIN flow_action fa ON arr.flow_id = fa.flow_id
JOIN cust_action ca ON ca.valid = 0 AND fa.action_id = ca.id
JOIN action_room ar ON ar.action_id = ca.id AND ar.code = 0
JOIN customer c ON ca.customer_id = c.id
LEFT JOIN turn_rule tr ON arr.req_type = 'Mall' and arr.ref_id = tr.id
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"hghua" <hghua@.discussions.microsoft.com> wrote in message
news:A9FB112B-AD45-463A-A167-CFAF77E20B5D@.microsoft.com...
> Database backup file:
> http://www.keepmyfile.com/download/c58b2a565144
> Environment:
> SQL Server 2000 SP4
> Problem:
> The following two statements returns different number of records:
> Exec GenPeriodical1 102, null, '20050601', '20050630', null, null, 0
> SELECT *
> FROM dbo.OtherFee (null, '20050601', '20050630', null, null, 0)
> WHERE flow_id = 102
> This problem wasn't found in SQL Server 2000 original version and SQL
> Server
> 2005.
> Any help is appreciated!|||Thanks a lot! That works!
Hope Microsoft will solve the problem.
"Roji. P. Thomas" wrote:
> Well, seems like a bug.
> You can fix it by rearranginf the FROM clause in the function in the
> following manner.
> FROM action_room_req2 arr
> JOIN flow_action fa ON arr.flow_id = fa.flow_id
> JOIN cust_action ca ON ca.valid = 0 AND fa.action_id = ca.id
> JOIN action_room ar ON ar.action_id = ca.id AND ar.code = 0
> JOIN customer c ON ca.customer_id = c.id
> LEFT JOIN turn_rule tr ON arr.req_type = 'Mall' and arr.ref_id = tr.id
> --
> Regards
> Roji. P. Thomas
> http://toponewithties.blogspot.com
> "hghua" <hghua@.discussions.microsoft.com> wrote in message
> news:A9FB112B-AD45-463A-A167-CFAF77E20B5D@.microsoft.com...
>
>
Is it a bug of SQL Server 2000 SP4?
Database backup file:
http://www.keepmyfile.com/download/c58b2a565144
Environment:
SQL Server 2000 SP4
Problem:
The following two statements returns different number of records:
Exec GenPeriodical1 102, null, '20050601', '20050630', null, null, 0
SELECT *
FROM dbo.OtherFee (null, '20050601', '20050630', null, null, 0)
WHERE flow_id = 102
This problem wasn't found in SQL Server 2000 original version and SQL ServerIs it possible to see text for dbo.OtherFee and GenPeriodical1?|||
2005.
Any help is appreciated!
Thanks for your reply!
I've got the answer from the newsgroup. The replyer said it seems a bug of SP4 and gave a work-around. If you are interested in this issue, you can download the backup file, it's just 1.18MB.
The store proc and function call other functions, thus not convenient to paste them here.
is it a bug in SSCE OLEDB ?
i'm use this code ,in SQL2005 std and ACCESS database, it work
but if i use SSCE ,it's throw a OleDbException in ExecuteScalar()
Exception : OleDbException
0x80040E30L
DB_E_BADTYPENAME
Code Snippet
OleDbConnection od = new OleDbConnection("Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=db.sdf;SSCE:Database Password=");
od.Open();
OleDbCommand og = new OleDbCommand("INSERT INTO [bills] ([billno],[checkouttime],[finalprice],[handle],[ischeckout],[memo],[paymode],[trick]) VALUES (@.billno,@.checkouttime,@.finalprice,@.handle,@.ischeckout,@.memo,@.paymode,@.trick)", od);
og.Parameters.Add("@.billno", OleDbType.VarWChar).Value = "2007051800000000";
og.Parameters.Add("@.checkouttime",OleDbType.DBTimeStamp).Value="2007-5-18 11:55:40";
og.Parameters.Add("@.finalprice", OleDbType.Single).Value = 0.0;
og.Parameters.Add("@.handle", OleDbType.VarWChar).Value = "admin";
og.Parameters.Add("@.ischeckout", OleDbType.SmallInt).Value = 0;
og.Parameters.Add("@.memo", OleDbType.VarWChar).Value = "";
og.Parameters.Add("@.paymode", OleDbType.VarWChar).Value = "";
og.Parameters.Add("@.trick", OleDbType.VarWChar).Value = "";
og.ExecuteScalar();
od.Close();
why the same code is not work? i'm find all MSDN ,but there is no answer
Who can help me,Thanks
SSCE is not registered as a standard oledb provider. We have a *limited* support for OLEDB. It doesn't comply with tier-1 or tier-2 requirements etc. Why don't you use our managed provider? Things are much more fun. If you have to use native provider only, you will have to use standard oledb interfaces like IDBInitialize, ICommand etc.
You can even search for oledb northwind sample on msdn. If your question is answered, please mark it as answered.
Thanks
Raja
|||Have you tried using ExecuteNonQuery in stead of ExecuteScalar|||
Please try using double quotes instead of brackets to delimit identifiers. The SQL Compact Edition 3.0 query processor does not accept Access' brackets. Try this statement:
Code Snippet
INSERT INTO "bills" ("billno", "checkouttime", "finalprice", "handle", "ischeckout", "memo", "paymode", "trick") VALUES (...)
|||
Jo?o Paulo Figueira wrote:
Please try using double quotes instead of brackets to delimit identifiers. The SQL Compact Edition 3.0 query processor does not accept Access' brackets. Try this statement:
Code Snippet
INSERT INTO "bills" ("billno", "checkouttime", "finalprice", "handle", "ischeckout", "memo", "paymode", "trick") VALUES (...)
yes,it's works
but,i want to use the same code on SSEE SSCE ACCESS
so i use OLEDB parameter ,but it's error
|||
SSCE is not registered as a standard oledb provider. We have a *limited* support for OLEDB. It doesn't comply with tier-1 or tier-2 requirements etc. Why don't you use our managed provider? Things are much more fun. If you have to use native provider only, you will have to use standard oledb interfaces like IDBInitialize, ICommand etc.
You can even search for oledb northwind sample on msdn. If your question is answered, please mark it as answered.
Thanks
Raja
the SSCE managed provider is sqlceconnection ,the SSEE managed provider is sqlconnection
if i use this provider ,i will write 3 different query on SSEE SSCE ACCESS
don't have an easy Method ?
thanks
|||You could use database independent base classes in your code (e.g. DbConnection) and specific providers for each database:
DbConnection connection = new SqlConnection(...); // Or SqlCeConnection or whatever else.
connection.Open();
DbCommand command = connection.CreateCommand();
command.CommandText = "Select whatever from somewhere where some = ?";
command.Parameter.Add(...);
Usually it makes little sense because SQL dialects are slightly different and query for one database won’t work on another (unless it’s really basic).
is it a bug in SSCE OLEDB ?
i'm use this code ,in SQL2005 std and ACCESS database, it work
but if i use SSCE ,it's throw a OleDbException in ExecuteScalar()
Exception : OleDbException
0x80040E30L
DB_E_BADTYPENAME
Code Snippet
OleDbConnection od = new OleDbConnection("Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=db.sdf;SSCE:Database Password=");
od.Open();
OleDbCommand og = new OleDbCommand("INSERT INTO [bills] ([billno],[checkouttime],[finalprice],[handle],[ischeckout],[memo],[paymode],[trick]) VALUES (@.billno,@.checkouttime,@.finalprice,@.handle,@.ischeckout,@.memo,@.paymode,@.trick)", od);
og.Parameters.Add("@.billno", OleDbType.VarWChar).Value = "2007051800000000";
og.Parameters.Add("@.checkouttime",OleDbType.DBTimeStamp).Value="2007-5-18 11:55:40";
og.Parameters.Add("@.finalprice", OleDbType.Single).Value = 0.0;
og.Parameters.Add("@.handle", OleDbType.VarWChar).Value = "admin";
og.Parameters.Add("@.ischeckout", OleDbType.SmallInt).Value = 0;
og.Parameters.Add("@.memo", OleDbType.VarWChar).Value = "";
og.Parameters.Add("@.paymode", OleDbType.VarWChar).Value = "";
og.Parameters.Add("@.trick", OleDbType.VarWChar).Value = "";
og.ExecuteScalar();
od.Close();
why the same code is not work? i'm find all MSDN ,but there is no answer
Who can help me,Thanks
SSCE is not registered as a standard oledb provider. We have a *limited* support for OLEDB. It doesn't comply with tier-1 or tier-2 requirements etc. Why don't you use our managed provider? Things are much more fun. If you have to use native provider only, you will have to use standard oledb interfaces like IDBInitialize, ICommand etc.
You can even search for oledb northwind sample on msdn. If your question is answered, please mark it as answered.
Thanks
Raja
|||Have you tried using ExecuteNonQuery in stead of ExecuteScalar|||
Please try using double quotes instead of brackets to delimit identifiers. The SQL Compact Edition 3.0 query processor does not accept Access' brackets. Try this statement:
Code Snippet
INSERT INTO "bills" ("billno", "checkouttime", "finalprice", "handle", "ischeckout", "memo", "paymode", "trick") VALUES (...)
|||
Please try using double quotes instead of brackets to delimit identifiers. The SQL Compact Edition 3.0 query processor does not accept Access' brackets. Try this statement:
Code Snippet
INSERT INTO "bills" ("billno", "checkouttime", "finalprice", "handle", "ischeckout", "memo", "paymode", "trick") VALUES (...)
yes,it's works
but,i want to use the same code on SSEE SSCE ACCESS
so i use OLEDB parameter ,but it's error
|||
SSCE is not registered as a standard oledb provider. We have a *limited* support for OLEDB. It doesn't comply with tier-1 or tier-2 requirements etc. Why don't you use our managed provider? Things are much more fun. If you have to use native provider only, you will have to use standard oledb interfaces like IDBInitialize, ICommand etc.
You can even search for oledb northwind sample on msdn. If your question is answered, please mark it as answered.
Thanks
Raja
the SSCE managed provider is sqlceconnection ,the SSEE managed provider is sqlconnection
if i use this provider ,i will write 3 different query on SSEE SSCE ACCESS
don't have an easy Method ?
thanks
|||You could use database independent base classes in your code (e.g. DbConnection) and specific providers for each database:
DbConnection connection = new SqlConnection(...); // Or SqlCeConnection or whatever else.
connection.Open();
DbCommand command = connection.CreateCommand();
command.CommandText = "Select whatever from somewhere where some = ?";
command.Parameter.Add(...);
Usually it makes little sense because SQL dialects are slightly different and query for one database won’t work on another (unless it’s really basic).
Is it a bug in SQL CE?
Hi!
I use SQL CE with VS.NET. I find the following bug 2th.
The table has an "ID int IDENTITY(0,1) PRIMARY KEY,". That is my row identity.
I add rows to the table, then I realized that the ID order not in the general order (from 0 to ........)
For example: 6,7,8,0,1,2,3,4,5.
Of course row 6,7 and 8 was added the very last.
The content of each row is not mixed, only the ID order.
Is it a very confused, because we develop mobile invoice programs for PDAs.
What I did wrong?
Thank you!
Does that happen in the Query Analyzer on the PDA?
Maybe it does not order the rows by the primary key column by default (?). I don't know if that would be a bug, although it sounds more convenient if it did order on any key columns.
|||I moved this thread to the SQL Mobile's team forusm|||Neither SQL Server nor SQL Mobile guarrenty you about the physical order of the rows and you are not expected to concluded something from running multiple queries. It can always change. If you want the rows to be ordered on a column, you should ideally use ORDER BY. Your query result (with out ordering) always depends on the cursor position.
Thanks,
Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation
is it a bug ? (relationships in report builder)
Hi friends
am having some weird problem.am using sql server 2005 standard edition.
does it mean that even if some one adds a field from tab3 accidentally ,he has to create the whole report from scratch so that he can see data that matches its criteria ?|||Bob
I think ,i kind of achieved what i want. please advise if i go into any problems in future.
am having some weird problem.am using sql server 2005 standard edition.
i've a report model with tables (tab1,tab2,tab3). tab1 and tab2 (actually these are views) relate to same table but each has different columns from same table.
the third table i.e. tab3 is child of tab1.
in my report model project,i set cardinality of this role in tab3 as "one" and in tab1 as "Optionalmany".
when creating a report in reportbuilder.if select columns from either tab1 and/or tab2 i get to see 100 records which is correct.if add any column from tab3 i get to see only 1 record which also correct as i've only 1 row tab3 at the moment.
now ,fun begins , what should happen if i delete column(s) of tab3 from report designer ? i should see all 100 records ,right ? bcoz all my columns coming from either tab1 or tab3 but when i run report i get see only 1 record !!! why ? is it a bug or am i missing something.
Thanks for your help.no one ever faced this situation ?
any ideas on this one much appreciated.|||
The difference between the first report that returned 100 rows and the third report that returned 1 row is the primary entity, which changed when you added a column from tab3, but did not revert when you deleted that column.
The primary entity of the first report is tab1, which means that report is fundamentally about tab1 and the data related to it.
The primary entity of the third report is tab3, which means that report is fundamentally about tab3 and the data related to it. Basically, the third report is a summary report for the data in tab3, grouped by tab1, which explains why there is only one row.
|||Thanks for the reply Bob.does it mean that even if some one adds a field from tab3 accidentally ,he has to create the whole report from scratch so that he can see data that matches its criteria ?|||Bob
I think ,i kind of achieved what i want. please advise if i go into any problems in future.
what i did was i set cardinality for the roles on parent table side as "optionalone" and child entity side as "Optionalmany". (normally its other way round ,right?)
now my reports work just fine. i mean i get data am expecting and top of it if i remove the field from tab3 still my report worked displaying all records from tab1,tab2.
BTW when i deploy (using BI) i get a warning like below
"The Relation property of the Role 'tab3 detail' refers to the Target end of the Relation 'dd_tab1-dd_tab3', which is not bound to a set of uniquely constrained columns for the Table 'dbo.dd__tab3'. Roles with Cardinality of One or OptionalOne require relations bound to uniquely constrained columns of the table."
is it a problem ?
i checked sql the report builder making (in sql profiler) and they look fine as its placing joins correctly on both parent and child tables.
any suggestions on this much appreciated.
Thank you very much|||
No, you should not swap the cardinality of your report model roles to get different joins. RB relies on this information in many ways to provide a consistent and appropriately constrained query design experience to the user.
You are right that there is currently no way to revert the primary entity other than rebuilding your report. This feature was slated for SQL 2005 at one point, but unfortunately did not make it into this release.
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 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...
>
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.
>>
>>
>
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.
>>
>>
>
Monday, February 20, 2012
Is bug described in article 872843 really fixed?
I'm running SQL Server 2000 SP4 with the AWE hotfix (build 2040?) on Windows
Server 2003 with replication.
This morning, my log reader agents failed. In the server log, I saw the
following messages:
SQL Server Assertion: File: <logscan.cpp>, line=1985
Failed Assertion = 'startLSN >= m_curLSN'.
...
SQL Server Assertion: File: <logscan.cpp>, line=2223
Failed Assertion = 'm_noOfScAlloc == 0'.
KB article 872843 says that this is supposed to be fixed in SP4... is it?
Or did it come back in the AWE hotfix?
Regards,
Jonathan
Hello,
KB872843 is included in SQL server 2000 SP4. It appears a new issue and is
not related to the AWE hotfix. Please reInitializing the replication and
then check if the issue still exists.
To find out the cause of this issue we may need to analyze memory dumps,
this work has to be done by contacting Microsoft Customer Service and
Support (CSS). Therefore, if the issue still exists, please contact CSS for
more immediate assistance. For more information on available CSS services,
please click here:
http://support.microsoft.com/default...roPhone#faq607
Server 2003 with replication.
This morning, my log reader agents failed. In the server log, I saw the
following messages:
SQL Server Assertion: File: <logscan.cpp>, line=1985
Failed Assertion = 'startLSN >= m_curLSN'.
...
SQL Server Assertion: File: <logscan.cpp>, line=2223
Failed Assertion = 'm_noOfScAlloc == 0'.
KB article 872843 says that this is supposed to be fixed in SP4... is it?
Or did it come back in the AWE hotfix?
Regards,
Jonathan
Hello,
KB872843 is included in SQL server 2000 SP4. It appears a new issue and is
not related to the AWE hotfix. Please reInitializing the replication and
then check if the issue still exists.
To find out the cause of this issue we may need to analyze memory dumps,
this work has to be done by contacting Microsoft Customer Service and
Support (CSS). Therefore, if the issue still exists, please contact CSS for
more immediate assistance. For more information on available CSS services,
please click here:
http://support.microsoft.com/default...roPhone#faq607
Is bug described in article 872843 really fixed?
I'm running SQL Server 2000 SP4 with the AWE hotfix (build 2040?) on Windows
Server 2003 with replication.
This morning, my log reader agents failed. In the server log, I saw the
following messages:
SQL Server Assertion: File: <logscan.cpp>, line=1985
Failed Assertion = 'startLSN >= m_curLSN'.
...
SQL Server Assertion: File: <logscan.cpp>, line=2223
Failed Assertion = 'm_noOfScAlloc == 0'.
KB article 872843 says that this is supposed to be fixed in SP4... is it?
Or did it come back in the AWE hotfix?
Regards,
JonathanHello,
KB872843 is included in SQL server 2000 SP4. It appears a new issue and is
not related to the AWE hotfix. Please reInitializing the replication and
then check if the issue still exists.
To find out the cause of this issue we may need to analyze memory dumps,
this work has to be done by contacting Microsoft Customer Service and
Support (CSS). Therefore, if the issue still exists, please contact CSS for
more immediate assistance. For more information on available CSS services,
please click here:
http://support.microsoft.com/defaul...ProPhone#faq607
Server 2003 with replication.
This morning, my log reader agents failed. In the server log, I saw the
following messages:
SQL Server Assertion: File: <logscan.cpp>, line=1985
Failed Assertion = 'startLSN >= m_curLSN'.
...
SQL Server Assertion: File: <logscan.cpp>, line=2223
Failed Assertion = 'm_noOfScAlloc == 0'.
KB article 872843 says that this is supposed to be fixed in SP4... is it?
Or did it come back in the AWE hotfix?
Regards,
JonathanHello,
KB872843 is included in SQL server 2000 SP4. It appears a new issue and is
not related to the AWE hotfix. Please reInitializing the replication and
then check if the issue still exists.
To find out the cause of this issue we may need to analyze memory dumps,
this work has to be done by contacting Microsoft Customer Service and
Support (CSS). Therefore, if the issue still exists, please contact CSS for
more immediate assistance. For more information on available CSS services,
please click here:
http://support.microsoft.com/defaul...ProPhone#faq607
Is bug described in article 872843 really fixed?
I'm running SQL Server 2000 SP4 with the AWE hotfix (build 2040?) on Windows
Server 2003 with replication.
This morning, my log reader agents failed. In the server log, I saw the
following messages:
SQL Server Assertion: File: <logscan.cpp>, line=1985
Failed Assertion = 'startLSN >= m_curLSN'.
...
SQL Server Assertion: File: <logscan.cpp>, line=2223
Failed Assertion = 'm_noOfScAlloc == 0'.
KB article 872843 says that this is supposed to be fixed in SP4... is it?
Or did it come back in the AWE hotfix?
Regards,
JonathanHello,
KB872843 is included in SQL server 2000 SP4. It appears a new issue and is
not related to the AWE hotfix. Please reInitializing the replication and
then check if the issue still exists.
To find out the cause of this issue we may need to analyze memory dumps,
this work has to be done by contacting Microsoft Customer Service and
Support (CSS). Therefore, if the issue still exists, please contact CSS for
more immediate assistance. For more information on available CSS services,
please click here:
http://support.microsoft.com/default.aspx?scid=fh;EN-US;OfferProPhone#faq607
Server 2003 with replication.
This morning, my log reader agents failed. In the server log, I saw the
following messages:
SQL Server Assertion: File: <logscan.cpp>, line=1985
Failed Assertion = 'startLSN >= m_curLSN'.
...
SQL Server Assertion: File: <logscan.cpp>, line=2223
Failed Assertion = 'm_noOfScAlloc == 0'.
KB article 872843 says that this is supposed to be fixed in SP4... is it?
Or did it come back in the AWE hotfix?
Regards,
JonathanHello,
KB872843 is included in SQL server 2000 SP4. It appears a new issue and is
not related to the AWE hotfix. Please reInitializing the replication and
then check if the issue still exists.
To find out the cause of this issue we may need to analyze memory dumps,
this work has to be done by contacting Microsoft Customer Service and
Support (CSS). Therefore, if the issue still exists, please contact CSS for
more immediate assistance. For more information on available CSS services,
please click here:
http://support.microsoft.com/default.aspx?scid=fh;EN-US;OfferProPhone#faq607
Is bug 351711 fixed in MSDE 2000 SP1 or later?
I'm reading this article http://support.microsoft.com/?id=285100. I wonder
whether it is fixed or not in MSDE 2000 installation. If yes, fixed in which
SP?
The hot fix is released JAN-22-2001 and the article is Last Review : October
7, 2005. The article does not mention anything about whether the hot fix is
included in SP and it does not mention which SP does this bug applies to. By
reading the article, I will assume this only applies to MSDE 2000 without SP
and fixed in SP1. But can I assume that?Just download the latest MSDE version (Microsoft SQL Server 2000 Service Pack
4
http://www.microsoft.com/downloads/details.aspx?familyid=8E2DFC8D-C20E-4446-99A9-B7F0213F8BC5&displaylang=en
or directly from
http://www.microsoft.com/downloads/info.aspx?na=46&p=6&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=8E2DFC8D-C20E-4446-99A9-B7F0213F8BC5&u=http%3a%2f%2fdownload.microsoft.com%2fdownload%2f1%2fb%2fd%2f1bdf5b78-584e-4de0-b36f-c44e06b0d2a3%2fSQL2000.MSDE-KB884525-SP4-x86-ENU.EXE&oRef=http%3a%2f%2fwww.microsoft.com%2fdownloads%2fdetails.aspx%3fFamilyId%3d413744D1-A0BC-479F-BAFA-E4B278EB9147%26displaylang%3den
"Peter" wrote:
> I'm reading this article http://support.microsoft.com/?id=285100. I wonder
> whether it is fixed or not in MSDE 2000 installation. If yes, fixed in which
> SP?
> The hot fix is released JAN-22-2001 and the article is Last Review : October
> 7, 2005. The article does not mention anything about whether the hot fix is
> included in SP and it does not mention which SP does this bug applies to. By
> reading the article, I will assume this only applies to MSDE 2000 without SP
> and fixed in SP1. But can I assume that?
whether it is fixed or not in MSDE 2000 installation. If yes, fixed in which
SP?
The hot fix is released JAN-22-2001 and the article is Last Review : October
7, 2005. The article does not mention anything about whether the hot fix is
included in SP and it does not mention which SP does this bug applies to. By
reading the article, I will assume this only applies to MSDE 2000 without SP
and fixed in SP1. But can I assume that?Just download the latest MSDE version (Microsoft SQL Server 2000 Service Pack
4
http://www.microsoft.com/downloads/details.aspx?familyid=8E2DFC8D-C20E-4446-99A9-B7F0213F8BC5&displaylang=en
or directly from
http://www.microsoft.com/downloads/info.aspx?na=46&p=6&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=8E2DFC8D-C20E-4446-99A9-B7F0213F8BC5&u=http%3a%2f%2fdownload.microsoft.com%2fdownload%2f1%2fb%2fd%2f1bdf5b78-584e-4de0-b36f-c44e06b0d2a3%2fSQL2000.MSDE-KB884525-SP4-x86-ENU.EXE&oRef=http%3a%2f%2fwww.microsoft.com%2fdownloads%2fdetails.aspx%3fFamilyId%3d413744D1-A0BC-479F-BAFA-E4B278EB9147%26displaylang%3den
"Peter" wrote:
> I'm reading this article http://support.microsoft.com/?id=285100. I wonder
> whether it is fixed or not in MSDE 2000 installation. If yes, fixed in which
> SP?
> The hot fix is released JAN-22-2001 and the article is Last Review : October
> 7, 2005. The article does not mention anything about whether the hot fix is
> included in SP and it does not mention which SP does this bug applies to. By
> reading the article, I will assume this only applies to MSDE 2000 without SP
> and fixed in SP1. But can I assume that?
Subscribe to:
Posts (Atom)