Im using vb code to generate a SQL query to return a recordset. The follwing
query returns 2 records as follows.
Select a.BHYEAR_MOVEDATE,
Case b.BTYear_TransCode
WHEN '17' THEN 'DD'
WHEN '01' THEN 'DD'
WHEN '18' THEN 'DD'
Else 'AUD' END AS 'TYPE'
from dbo.BacsHdrYearly as a
LEFT JOIN dbo.BacsTrnYear as b
on a.BHYear_LedgerKey = b.BTYear_LedgerKey
Where (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '128')
OR (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '134')
OR (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '135')
OR (a.BHYEAR_LICENCE = '217001' AND a.BHYEAR_SERIALNUMBER = '136')
GROUP BY a.BHYEAR_MOVEDATE , b.BTYear_TransCode
BHYEAR_MOVEDATE TYPE
--- --
2005-04-21 00:00:00 DD
2005-04-21 00:00:00 DD
I need it to only return one record as for each date and type ( may be
multiple dates and types ).
BHYEAR_MOVEDATE TYPE
--- --
2005-04-21 00:00:00 DD
any suggestions on how to rephrase this query ?What ABout DISTINCT ?
> Select DISTINCT a.BHYEAR_MOVEDATE,
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Peter Newman" <PeterNewman@.discussions.microsoft.com> schrieb im
Newsbeitrag news:5AEEE5BB-67B2-4DEC-A918-F6C32E79312C@.microsoft.com...
> Im using vb code to generate a SQL query to return a recordset. The
> follwing
> query returns 2 records as follows.
> Select a.BHYEAR_MOVEDATE,
> Case b.BTYear_TransCode
> WHEN '17' THEN 'DD'
> WHEN '01' THEN 'DD'
> WHEN '18' THEN 'DD'
> Else 'AUD' END AS 'TYPE'
> from dbo.BacsHdrYearly as a
> LEFT JOIN dbo.BacsTrnYear as b
> on a.BHYear_LedgerKey = b.BTYear_LedgerKey
> Where (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '128')
> OR (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '134')
> OR (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '135')
> OR (a.BHYEAR_LICENCE = '217001' AND a.BHYEAR_SERIALNUMBER = '136')
> GROUP BY a.BHYEAR_MOVEDATE , b.BTYear_TransCode
>
> BHYEAR_MOVEDATE TYPE
> --- --
> 2005-04-21 00:00:00 DD
> 2005-04-21 00:00:00 DD
>
> I need it to only return one record as for each date and type ( may be
> multiple dates and types ).
> BHYEAR_MOVEDATE TYPE
> --- --
> 2005-04-21 00:00:00 DD
> any suggestions on how to rephrase this query ?|||or
GROUP BY clause
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OoaGSMNRFHA.204@.TK2MSFTNGP15.phx.gbl...
> What ABout DISTINCT ?
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Peter Newman" <PeterNewman@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:5AEEE5BB-67B2-4DEC-A918-F6C32E79312C@.microsoft.com...
>|||Of couse ;-)
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:e$TxrUNRFHA.3076@.TK2MSFTNGP14.phx.gbl...
> or
> GROUP BY clause
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in
> message news:OoaGSMNRFHA.204@.TK2MSFTNGP15.phx.gbl...
>|||Peter,
Your statement is correct, except that in the GROUP BY clause you have to us
e:
...
GROUP BY
a.BHYEAR_MOVEDATE,
Case b.BTYear_TransCode
WHEN '17' THEN 'DD'
WHEN '01' THEN 'DD'
WHEN '18' THEN 'DD'
Else 'AUD' END;
AMB
"Peter Newman" wrote:
> Im using vb code to generate a SQL query to return a recordset. The follwi
ng
> query returns 2 records as follows.
> Select a.BHYEAR_MOVEDATE,
> Case b.BTYear_TransCode
> WHEN '17' THEN 'DD'
> WHEN '01' THEN 'DD'
> WHEN '18' THEN 'DD'
> Else 'AUD' END AS 'TYPE'
> from dbo.BacsHdrYearly as a
> LEFT JOIN dbo.BacsTrnYear as b
> on a.BHYear_LedgerKey = b.BTYear_LedgerKey
> Where (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '128')
> OR (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '134')
> OR (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '135')
> OR (a.BHYEAR_LICENCE = '217001' AND a.BHYEAR_SERIALNUMBER = '136')
> GROUP BY a.BHYEAR_MOVEDATE , b.BTYear_TransCode
>
> BHYEAR_MOVEDATE TYPE
> --- --
> 2005-04-21 00:00:00 DD
> 2005-04-21 00:00:00 DD
>
> I need it to only return one record as for each date and type ( may be
> multiple dates and types ).
> BHYEAR_MOVEDATE TYPE
> --- --
> 2005-04-21 00:00:00 DD
> any suggestions on how to rephrase this query ?sql
Showing posts with label recordset. Show all posts
Showing posts with label recordset. Show all posts
Friday, March 23, 2012
Help converting procedural VB code to SQL
I am at the last hurdle on converting a very large chunk of VB code that
massages a recordset to produce a report.
This question relates to my previous question from 3/2 and the ddl that I
posted for that question.
I need to replace the following vb code with SQL and I think I can do it
with a Case statement but would really appreciate some input on this. The V
B
code follows the url for the original message.
http://msdn.microsoft.com/newsgroup...r />
4F24A2-F7
1F-425F-AC2B-DC48AB0DA5C9&dglist=&ptlist=&exp=&sloc=en-us
'***********Code Start************
Dim TempValue As Single
If iFactor <> 0 And iFactor <> 1 Then ' Pursue adjustment
If iGreenRpt Then ' A green report has been requested
If iRunInData Then 'Data or spec is not green so make adjustment
TempValue = iValue * iFactor
Else ' Take data as is "Green"
TempValue = iValue
End If
statAdjustData = TempValue
Else 'Non-green or Runin/"Market Rating" report has been requested
If Not iRunInData Then 'Data or spec is green so make adjustment
TempValue = iValue / iFactor
Else 'Take data as is "Runin"
TempValue = iValue
End If
statAdjustData = TempValue
End If ' Green or Runin/Market data report requested
Else ' iFactor = 1 or 0 therefore no need to adjust
statAdjustData = iValue
End If ' iFactor = To Or <> 1 or 0
'********Code End*****************--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Perhaps:
DECLARE @.True TINYINT, @.False TINYINT
SET @.True = 1
Set @.False = 0
SELECT ... ,
CASE WHEN iFactor Not In (0,1)
THEN CASE WHEN iGreenReport = @.True
THEN CASE WHEN iRunInData = @.True
THEN iValue * iFactor
ELSE iValue
END
WHEN iGreenReport = @.False
THEN CASE WHEN iRunInData = @.False
THEN iValue / iFactor
ELSE iValue
END
END
ELSE iValue
END As statAdjustData
FROM ...
WHERE ...
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBRAzCQoechKqOuFEgEQJEMwCePBFQCl7kq6CW
NHM1LTWmKqgLGf8An29S
Yk4XGtIPaWOgPNdJC8g+Zz1r
=ZTUB
--END PGP SIGNATURE--
StvJston wrote:
> I am at the last hurdle on converting a very large chunk of VB code that
> massages a recordset to produce a report.
> This question relates to my previous question from 3/2 and the ddl that I
> posted for that question.
> I need to replace the following vb code with SQL and I think I can do it
> with a Case statement but would really appreciate some input on this. The
VB
> code follows the url for the original message.
> http://msdn.microsoft.com/newsgroup...76C%2C774F24A2-
F71F-425F-AC2B-DC48AB0DA5C9&dglist=&ptlist=&exp=&sloc=en-us
> '***********Code Start************
> Dim TempValue As Single
> If iFactor <> 0 And iFactor <> 1 Then ' Pursue adjustment
> If iGreenRpt Then ' A green report has been requested
> If iRunInData Then 'Data or spec is not green so make adjustme
nt
> TempValue = iValue * iFactor
> Else ' Take data as is "Green"
> TempValue = iValue
> End If
> statAdjustData = TempValue
> Else 'Non-green or Runin/"Market Rating" report has been requested
> If Not iRunInData Then 'Data or spec is green so make adjustme
nt
> TempValue = iValue / iFactor
> Else 'Take data as is "Runin"
> TempValue = iValue
> End If
> statAdjustData = TempValue
> End If ' Green or Runin/Market data report requested
> Else ' iFactor = 1 or 0 therefore no need to adjust
> statAdjustData = iValue
> End If ' iFactor = To Or <> 1 or 0
> '********Code End*****************|||Thanks for the reply.
I ended up doing this as a function and it seems to work very well and is
fast.
Stvjston
CREATE FUNCTION dbo.StatAdjustData ( @.IVal as FLOAT, @.iFactor as FLOAT,
@.iGreen as BIT, @.iRunnin as bit)
RETURNS FLOAT
BEGIN
DECLARE @.RetVal as FLOAT
IF @.iFactor <> 0 and @.iFactor <> 1
BEGIN
IF @.iGreen = -1
if @.iRunnin = -1
BEGIN
SET @.RetVAL = @.iVal * @.iFactor
END
ELSE
BEGIN
SET @.RetVAl = @.iVal
END
ELSE
IF @.iRunnin <> -1
BEGIN
SET @.RetVal = @.iVal / @.iFactor
END
ELSE
BEGIN
SET @.RetVal = @.iVal
END
END
ELSE
BEGIN
SET @.RETVAL = @.IvAL
END
RETURN (@.RetVal)
END
"MGFoster" wrote:
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> Perhaps:
> DECLARE @.True TINYINT, @.False TINYINT
> SET @.True = 1
> Set @.False = 0
> SELECT ... ,
> CASE WHEN iFactor Not In (0,1)
> THEN CASE WHEN iGreenReport = @.True
> THEN CASE WHEN iRunInData = @.True
> THEN iValue * iFactor
> ELSE iValue
> END
> WHEN iGreenReport = @.False
> THEN CASE WHEN iRunInData = @.False
> THEN iValue / iFactor
> ELSE iValue
> END
> END
> ELSE iValue
> END As statAdjustData
> FROM ...
> WHERE ...
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
> --BEGIN PGP SIGNATURE--
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
> iQA/ AwUBRAzCQoechKqOuFEgEQJEMwCePBFQCl7kq6CW
NHM1LTWmKqgLGf8An29S
> Yk4XGtIPaWOgPNdJC8g+Zz1r
> =ZTUB
> --END PGP SIGNATURE--
>
> StvJston wrote:
2-F71F-425F-AC2B-DC48AB0DA5C9&dglist=&ptlist=&exp=&sloc=en-us
>
massages a recordset to produce a report.
This question relates to my previous question from 3/2 and the ddl that I
posted for that question.
I need to replace the following vb code with SQL and I think I can do it
with a Case statement but would really appreciate some input on this. The V
B
code follows the url for the original message.
http://msdn.microsoft.com/newsgroup...r />
4F24A2-F7
1F-425F-AC2B-DC48AB0DA5C9&dglist=&ptlist=&exp=&sloc=en-us
'***********Code Start************
Dim TempValue As Single
If iFactor <> 0 And iFactor <> 1 Then ' Pursue adjustment
If iGreenRpt Then ' A green report has been requested
If iRunInData Then 'Data or spec is not green so make adjustment
TempValue = iValue * iFactor
Else ' Take data as is "Green"
TempValue = iValue
End If
statAdjustData = TempValue
Else 'Non-green or Runin/"Market Rating" report has been requested
If Not iRunInData Then 'Data or spec is green so make adjustment
TempValue = iValue / iFactor
Else 'Take data as is "Runin"
TempValue = iValue
End If
statAdjustData = TempValue
End If ' Green or Runin/Market data report requested
Else ' iFactor = 1 or 0 therefore no need to adjust
statAdjustData = iValue
End If ' iFactor = To Or <> 1 or 0
'********Code End*****************--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Perhaps:
DECLARE @.True TINYINT, @.False TINYINT
SET @.True = 1
Set @.False = 0
SELECT ... ,
CASE WHEN iFactor Not In (0,1)
THEN CASE WHEN iGreenReport = @.True
THEN CASE WHEN iRunInData = @.True
THEN iValue * iFactor
ELSE iValue
END
WHEN iGreenReport = @.False
THEN CASE WHEN iRunInData = @.False
THEN iValue / iFactor
ELSE iValue
END
END
ELSE iValue
END As statAdjustData
FROM ...
WHERE ...
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBRAzCQoechKqOuFEgEQJEMwCePBFQCl7kq6CW
NHM1LTWmKqgLGf8An29S
Yk4XGtIPaWOgPNdJC8g+Zz1r
=ZTUB
--END PGP SIGNATURE--
StvJston wrote:
> I am at the last hurdle on converting a very large chunk of VB code that
> massages a recordset to produce a report.
> This question relates to my previous question from 3/2 and the ddl that I
> posted for that question.
> I need to replace the following vb code with SQL and I think I can do it
> with a Case statement but would really appreciate some input on this. The
VB
> code follows the url for the original message.
> http://msdn.microsoft.com/newsgroup...76C%2C774F24A2-
F71F-425F-AC2B-DC48AB0DA5C9&dglist=&ptlist=&exp=&sloc=en-us
> '***********Code Start************
> Dim TempValue As Single
> If iFactor <> 0 And iFactor <> 1 Then ' Pursue adjustment
> If iGreenRpt Then ' A green report has been requested
> If iRunInData Then 'Data or spec is not green so make adjustme
nt
> TempValue = iValue * iFactor
> Else ' Take data as is "Green"
> TempValue = iValue
> End If
> statAdjustData = TempValue
> Else 'Non-green or Runin/"Market Rating" report has been requested
> If Not iRunInData Then 'Data or spec is green so make adjustme
nt
> TempValue = iValue / iFactor
> Else 'Take data as is "Runin"
> TempValue = iValue
> End If
> statAdjustData = TempValue
> End If ' Green or Runin/Market data report requested
> Else ' iFactor = 1 or 0 therefore no need to adjust
> statAdjustData = iValue
> End If ' iFactor = To Or <> 1 or 0
> '********Code End*****************|||Thanks for the reply.
I ended up doing this as a function and it seems to work very well and is
fast.
Stvjston
CREATE FUNCTION dbo.StatAdjustData ( @.IVal as FLOAT, @.iFactor as FLOAT,
@.iGreen as BIT, @.iRunnin as bit)
RETURNS FLOAT
BEGIN
DECLARE @.RetVal as FLOAT
IF @.iFactor <> 0 and @.iFactor <> 1
BEGIN
IF @.iGreen = -1
if @.iRunnin = -1
BEGIN
SET @.RetVAL = @.iVal * @.iFactor
END
ELSE
BEGIN
SET @.RetVAl = @.iVal
END
ELSE
IF @.iRunnin <> -1
BEGIN
SET @.RetVal = @.iVal / @.iFactor
END
ELSE
BEGIN
SET @.RetVal = @.iVal
END
END
ELSE
BEGIN
SET @.RETVAL = @.IvAL
END
RETURN (@.RetVal)
END
"MGFoster" wrote:
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> Perhaps:
> DECLARE @.True TINYINT, @.False TINYINT
> SET @.True = 1
> Set @.False = 0
> SELECT ... ,
> CASE WHEN iFactor Not In (0,1)
> THEN CASE WHEN iGreenReport = @.True
> THEN CASE WHEN iRunInData = @.True
> THEN iValue * iFactor
> ELSE iValue
> END
> WHEN iGreenReport = @.False
> THEN CASE WHEN iRunInData = @.False
> THEN iValue / iFactor
> ELSE iValue
> END
> END
> ELSE iValue
> END As statAdjustData
> FROM ...
> WHERE ...
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
> --BEGIN PGP SIGNATURE--
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
> iQA/ AwUBRAzCQoechKqOuFEgEQJEMwCePBFQCl7kq6CW
NHM1LTWmKqgLGf8An29S
> Yk4XGtIPaWOgPNdJC8g+Zz1r
> =ZTUB
> --END PGP SIGNATURE--
>
> StvJston wrote:
2-F71F-425F-AC2B-DC48AB0DA5C9&dglist=&ptlist=&exp=&sloc=en-us
>
Monday, March 19, 2012
HELP : Remote query from 2005 to 2000 returns wrong recordset when
Hi there
I came across this bug and could not find a solution, and I'm blocked.
Can anyone help ?
I have a table with TABLE CONSTRAINT, located at a SQL 2000 (SP3 or
SP4) server.
When I make a remote query from a (linked) SQL 2005 server (SP1 or
SP2), the returned recordset is totally wrong (rows are missing).
The problem does not occur between two SQL 2000, nor between two SQL
2005.Eric,
Can you define the condition of the missing rows better? That might give a
clue.
One suggestion is to compare the collations of the SQL 2000 server and
database with the collations of the SQL 2005 server and database. If that
is a problem, you might be able to resolve it with a COLLATE clause in the
query.
RLF
<EricBello@.eurofins.com> wrote in message
news:9c42bd9d-8d35-4370-bcd5-293830ac27f7@.l1g2000hsa.googlegroups.com...
> Hi there
> I came across this bug and could not find a solution, and I'm blocked.
> Can anyone help ?
> I have a table with TABLE CONSTRAINT, located at a SQL 2000 (SP3 or
> SP4) server.
> When I make a remote query from a (linked) SQL 2005 server (SP1 or
> SP2), the returned recordset is totally wrong (rows are missing).
> The problem does not occur between two SQL 2000, nor between two SQL
> 2005.
>|||Here is the script to reproduce the problem
At SQL 2000 side (SP3 or SP4) :
--cleanup
--DROP TABLE MyPackagesTranslations
--DROP TABLE MyPackages
GO
USE pubs
GO
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
-- Create the table MyPackages,
CREATE TABLE [dbo].[MyPackages](
[packageIncId] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[packageSqlId] [smallint] NOT NULL CONSTRAINT
[DF_MyPackages_packageSqlId] DEFAULT (1121),
[packageName] [nvarchar](60) NULL,
[isDeleted] [bit] NOT NULL CONSTRAINT
[DF_MyPackages_isDeleted_Default] DEFAULT (0x00),
CONSTRAINT [PK_MyPackages] PRIMARY KEY CLUSTERED
(
[packageIncId] ASC,
[packageSqlId] ASC
)
)
GO
-- Create linked table MyPackagesTranslations
CREATE TABLE [dbo].[MyPackagesTranslations](
[packageTranslationIncId] [int] IDENTITY(1,1) NOT FOR REPLICATION
NOT NULL,
[packageTranslationSqlId] [smallint] NOT NULL CONSTRAINT
[DF_MyPackagesTranslations_packageTranslationSqlId] DEFAULT (1121),
[packageIncId] [int] NOT NULL,
[packageSqlId] [smallint] NOT NULL,
[packageTranslationName] [nvarchar](60) NULL,
CONSTRAINT [PK_MyPackagesTranslations] PRIMARY KEY CLUSTERED
(
[packageTranslationIncId] ASC,
[packageTranslationSqlId] ASC
)
)
GO
-- ... with a foreign key between them
ALTER TABLE [dbo].[MyPackagesTranslations] WITH NOCHECK ADD CONSTRAINT
[FK_MyPackagesTranslations_MyPackages]
FOREIGN KEY([packageIncId], [packageSqlId])
REFERENCES [dbo].[MyPackages] ([packageIncId], [packageSqlId])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[MyPackagesTranslations] CHECK CONSTRAINT
[FK_MyPackagesTranslations_MyPackages]
GO
-- Insert some data with "packageSqlId = 1"
INSERT MyPackages (packageSqlId, packageName) VALUES (1, 'Foo')
INSERT MyPackages (packageSqlId, packageName) VALUES (1, 'Bar')
INSERT MyPackages (packageSqlId, packageName) VALUES (1, 'Cat')
INSERT MyPackagesTranslations (packageIncId, packageSqlId,
packageTranslationName)
SELECT packageIncId, packageSqlId, 'Translated ' + packageName
FROM dbo.MyPackages
GO
-- Now Add a CHECK CONSTRAINT for MyPackages.packageSqlId = 1121
ALTER TABLE [dbo].[MyPackages] WITH NOCHECK ADD CONSTRAINT
[CK_MyPackages] CHECK NOT FOR REPLICATION
(([packageSqlId] = 1121))
GO
ALTER TABLE [dbo].[MyPackages] CHECK CONSTRAINT [CK_MyPackages]
GO
=> And now at SQL 2005 side (SP1 or SP2)
Execute the query :
-- Now connect to another server with SQL 2005 SP1 or SP2, and launch
the remote query
-- (first verify you have a linked server)
SELECT count(*)
from <remoteserver>.pubs.dbo.MyPackages pack
INNER JOIN <remoteserver>.pubs.dbo.MyPackagesTranslations trans
ON (pack.packageIncId=trans.packageIncId and
pack.packageSqlId=trans.packageSqlId)
where pack.isDeleted=0x0
--=> returns "0" (KO) : should be "3"|||The collations are the same.
I really suspect a bug like the one on the optimizer that considered
some constraints trustworthy whereas they were "not for replication".|||I drilled down to the execution plan, and I can clearly see the
optimizer including the condition "where ...packageSqlId = 1121",
which shows it actually considers the table constraint
"trustworthy" (or sth alike) and restricts the recordset accordingly.|||The missing rows are those that don't comply with the TABLE CONSTRAINT.|||Eric,
Sorry, I really don't know. I used your repro script and get the same
results that you do.
Other things that I tried, on the SQL 2000 server create the RemoteServer
linked server pointing back to itself. Here were my results trying this:
SQLOLEDB connection: Returned 3 as desired.
SQLNCLI connection: I get the following error
OLE DB error trace [Non-interface error: Column 'packageTranslationName'
(compile-time ordinal 5) of object '"pubs"."dbo"."MyPackagesTranslations"'
was reported to have changed. The exact nature of the change is unknown].
Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'SQLNCLI' supplied inconsistent metadata for a column.
Metadata information was changed at execution time.
And, no matter what I try from the SQL 2005 server, I always get 0 returned.
In addition to SQLNCLI connect, I tried to script in a SQLOLEDB connect, but
it converted to SQLNCLI. I tried creating the remote server using the SQL
Server radio button, also. No change in query result.
Is SQLNCLI at the root of the problem? I don't know enough to say.
So, I would say that you have probably found a bug that you could choose to
report to Microsoft. At least a bit of googling did not help me find an
answer.
RLF
<erbellico@.gmail.com> wrote in message
news:06af6c0c-6f36-4e3d-b671-64a27caa12d2@.s12g2000prg.googlegroups.com...
> The missing rows are those that don't comply with the TABLE CONSTRAINT.
I came across this bug and could not find a solution, and I'm blocked.
Can anyone help ?
I have a table with TABLE CONSTRAINT, located at a SQL 2000 (SP3 or
SP4) server.
When I make a remote query from a (linked) SQL 2005 server (SP1 or
SP2), the returned recordset is totally wrong (rows are missing).
The problem does not occur between two SQL 2000, nor between two SQL
2005.Eric,
Can you define the condition of the missing rows better? That might give a
clue.
One suggestion is to compare the collations of the SQL 2000 server and
database with the collations of the SQL 2005 server and database. If that
is a problem, you might be able to resolve it with a COLLATE clause in the
query.
RLF
<EricBello@.eurofins.com> wrote in message
news:9c42bd9d-8d35-4370-bcd5-293830ac27f7@.l1g2000hsa.googlegroups.com...
> Hi there
> I came across this bug and could not find a solution, and I'm blocked.
> Can anyone help ?
> I have a table with TABLE CONSTRAINT, located at a SQL 2000 (SP3 or
> SP4) server.
> When I make a remote query from a (linked) SQL 2005 server (SP1 or
> SP2), the returned recordset is totally wrong (rows are missing).
> The problem does not occur between two SQL 2000, nor between two SQL
> 2005.
>|||Here is the script to reproduce the problem
At SQL 2000 side (SP3 or SP4) :
--cleanup
--DROP TABLE MyPackagesTranslations
--DROP TABLE MyPackages
GO
USE pubs
GO
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
-- Create the table MyPackages,
CREATE TABLE [dbo].[MyPackages](
[packageIncId] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[packageSqlId] [smallint] NOT NULL CONSTRAINT
[DF_MyPackages_packageSqlId] DEFAULT (1121),
[packageName] [nvarchar](60) NULL,
[isDeleted] [bit] NOT NULL CONSTRAINT
[DF_MyPackages_isDeleted_Default] DEFAULT (0x00),
CONSTRAINT [PK_MyPackages] PRIMARY KEY CLUSTERED
(
[packageIncId] ASC,
[packageSqlId] ASC
)
)
GO
-- Create linked table MyPackagesTranslations
CREATE TABLE [dbo].[MyPackagesTranslations](
[packageTranslationIncId] [int] IDENTITY(1,1) NOT FOR REPLICATION
NOT NULL,
[packageTranslationSqlId] [smallint] NOT NULL CONSTRAINT
[DF_MyPackagesTranslations_packageTranslationSqlId] DEFAULT (1121),
[packageIncId] [int] NOT NULL,
[packageSqlId] [smallint] NOT NULL,
[packageTranslationName] [nvarchar](60) NULL,
CONSTRAINT [PK_MyPackagesTranslations] PRIMARY KEY CLUSTERED
(
[packageTranslationIncId] ASC,
[packageTranslationSqlId] ASC
)
)
GO
-- ... with a foreign key between them
ALTER TABLE [dbo].[MyPackagesTranslations] WITH NOCHECK ADD CONSTRAINT
[FK_MyPackagesTranslations_MyPackages]
FOREIGN KEY([packageIncId], [packageSqlId])
REFERENCES [dbo].[MyPackages] ([packageIncId], [packageSqlId])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[MyPackagesTranslations] CHECK CONSTRAINT
[FK_MyPackagesTranslations_MyPackages]
GO
-- Insert some data with "packageSqlId = 1"
INSERT MyPackages (packageSqlId, packageName) VALUES (1, 'Foo')
INSERT MyPackages (packageSqlId, packageName) VALUES (1, 'Bar')
INSERT MyPackages (packageSqlId, packageName) VALUES (1, 'Cat')
INSERT MyPackagesTranslations (packageIncId, packageSqlId,
packageTranslationName)
SELECT packageIncId, packageSqlId, 'Translated ' + packageName
FROM dbo.MyPackages
GO
-- Now Add a CHECK CONSTRAINT for MyPackages.packageSqlId = 1121
ALTER TABLE [dbo].[MyPackages] WITH NOCHECK ADD CONSTRAINT
[CK_MyPackages] CHECK NOT FOR REPLICATION
(([packageSqlId] = 1121))
GO
ALTER TABLE [dbo].[MyPackages] CHECK CONSTRAINT [CK_MyPackages]
GO
=> And now at SQL 2005 side (SP1 or SP2)
Execute the query :
-- Now connect to another server with SQL 2005 SP1 or SP2, and launch
the remote query
-- (first verify you have a linked server)
SELECT count(*)
from <remoteserver>.pubs.dbo.MyPackages pack
INNER JOIN <remoteserver>.pubs.dbo.MyPackagesTranslations trans
ON (pack.packageIncId=trans.packageIncId and
pack.packageSqlId=trans.packageSqlId)
where pack.isDeleted=0x0
--=> returns "0" (KO) : should be "3"|||The collations are the same.
I really suspect a bug like the one on the optimizer that considered
some constraints trustworthy whereas they were "not for replication".|||I drilled down to the execution plan, and I can clearly see the
optimizer including the condition "where ...packageSqlId = 1121",
which shows it actually considers the table constraint
"trustworthy" (or sth alike) and restricts the recordset accordingly.|||The missing rows are those that don't comply with the TABLE CONSTRAINT.|||Eric,
Sorry, I really don't know. I used your repro script and get the same
results that you do.
Other things that I tried, on the SQL 2000 server create the RemoteServer
linked server pointing back to itself. Here were my results trying this:
SQLOLEDB connection: Returned 3 as desired.
SQLNCLI connection: I get the following error
OLE DB error trace [Non-interface error: Column 'packageTranslationName'
(compile-time ordinal 5) of object '"pubs"."dbo"."MyPackagesTranslations"'
was reported to have changed. The exact nature of the change is unknown].
Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'SQLNCLI' supplied inconsistent metadata for a column.
Metadata information was changed at execution time.
And, no matter what I try from the SQL 2005 server, I always get 0 returned.
In addition to SQLNCLI connect, I tried to script in a SQLOLEDB connect, but
it converted to SQLNCLI. I tried creating the remote server using the SQL
Server radio button, also. No change in query result.
Is SQLNCLI at the root of the problem? I don't know enough to say.
So, I would say that you have probably found a bug that you could choose to
report to Microsoft. At least a bit of googling did not help me find an
answer.
RLF
<erbellico@.gmail.com> wrote in message
news:06af6c0c-6f36-4e3d-b671-64a27caa12d2@.s12g2000prg.googlegroups.com...
> The missing rows are those that don't comply with the TABLE CONSTRAINT.
Subscribe to:
Posts (Atom)