problems connection to Informix database

Hi,

I have a MS SQL 7 running on one machine and want to connect to a Informix database on another machine.

But I always get the error
Server: Msg 7319, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' returned a 'NON-CLUSTERED and NOT INTEGRATED' index 'ind_new_id' with incorrect bookmark ordinal 0.

I have installed the newest version of "Microsoft Data Access Components" and I have the latest Informix odbc driver

When I try to test the connection in the ODBC Data Source Administrator the test returns fine.

Anyone knows what my problem might be?One of the post in this forum refers
The problem is caused because most providers including SQL Server OLE DB provider do
not support index seek.

The 'index as access path' should not be checked when creating a linked server using
the SQL OLEDB. There is a provider level option which is read by the SQL Server from
the following registry.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Providers\SQLOLEDB\IndexAs
Accesspath. Please check whether the value of the above registry key is 0. If it
isn't , please change it to 0 and try the remote query again.

If it doesn't work, I suggest you rebuild a linked server using the SQL OLEDB without
checking 'index as access path'.

Problem with WHERE clause...

Hi All,
I keep getting the following error with my WHERE clause.
Msg 102, Level 15, State 1, Line 47
Incorrect syntax near '-'.
We've found that the code works if the segment " -
(CONVERT(Int,dbo.ReorderItemsTotals.OnPurchaseOrder))" is removed. So
it's clear that the error is within this segment.
The object "dbo.ReorderItemsTotals" is a view and uses a UNION command
in the SELECT statement. It joins two fields and outputs it as one
field - "OnPurchaseOrder". The two original fields are smallints and
the resulting UNION'd field is a varchar 10 - however I don't know why
this is.
Can anyone help fix this error? My code is as follows.
Many thanks in advance,
Rype
WHERE
(dbo.ProductAssembly.SubProduct IS NULL) AND
((((0.5 * dbo.SoldLast30TotalWith0Total.Despatched +
dbo.ReorderItemsTotals.SumOfSalesBackOrder) + (0.5 / 4 *
dbo.SoldLast120TotalWith0Total.Despatched) *
dbo.ReorderItemsTotals.ReorderLevel * 120) - (CONVERT(Int,
dbo.ReorderItemsTotals.Stock)) -
(CONVERT(Int,dbo.ReorderItemsTotals.OnPurchaseOrder)) +
dbo.ReorderItemsTotals.SumOfSalesBackOrder) > 0) OR
(dbo.ProductAssembly.SubProduct IS NULL) AND
(dbo.ProductDetails.Product1 < dbo.ReorderItemsTotalsSets.StockCode)
AND
(dbo.ProductDetails.Product1 + dbo.productdetails.product2 <
dbo.ReorderItemsTotalsSets.ReorderQuantity)This is a multi-part message in MIME format.
--=_NextPart_000_0007_01C804CC.9AEE5810
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Everything seems to check out using SSMS to balance the parentheses. I =do question if the logic is as you intend, given the groupings. Having =the OR out by itself like that is often not what is desired. Also, do =yourself a favor and start using aliases!! :-)
WHERE dbo.ProductAssembly.SubProduct IS NULL
AND ((((0.5 * dbo.SoldLast30TotalWith0Total.Despatched + =dbo.ReorderItemsTotals.SumOfSalesBackOrder) + (0.5 / 4 * dbo.SoldLast120TotalWith0Total.Despatched) * =dbo.ReorderItemsTotals.ReorderLevel * 120) - (CONVERT(Int, dbo.ReorderItemsTotals.Stock)) - =(CONVERT(Int,dbo.ReorderItemsTotals.OnPurchaseOrder)) +
dbo.ReorderItemsTotals.SumOfSalesBackOrder) > 0)
OR
(dbo.ProductAssembly.SubProduct IS NULL) AND (dbo.ProductDetails.Product1 < =dbo.ReorderItemsTotalsSets.StockCode)
AND (dbo.ProductDetails.Product1 + dbo.productdetails.product2 < =dbo.ReorderItemsTotalsSets.ReorderQuantity)
<ryanwpenfold@.gmail.com> wrote in message =news:1191330035.815038.230250@.n39g2000hsh.googlegroups.com...
> Hi All,
> > I keep getting the following error with my WHERE clause.
> > Msg 102, Level 15, State 1, Line 47
> Incorrect syntax near '-'.
> > We've found that the code works if the segment " -
> (CONVERT(Int,dbo.ReorderItemsTotals.OnPurchaseOrder))" is removed. So
> it's clear that the error is within this segment.
> > The object "dbo.ReorderItemsTotals" is a view and uses a UNION command
> in the SELECT statement. It joins two fields and outputs it as one
> field - "OnPurchaseOrder". The two original fields are smallints and
> the resulting UNION'd field is a varchar 10 - however I don't know why
> this is.
> > Can anyone help fix this error? My code is as follows.
> > Many thanks in advance,
> > Rype
> > > > WHERE
> (dbo.ProductAssembly.SubProduct IS NULL) AND
> ((((0.5 * dbo.SoldLast30TotalWith0Total.Despatched +
> dbo.ReorderItemsTotals.SumOfSalesBackOrder) + (0.5 / 4 *
> dbo.SoldLast120TotalWith0Total.Despatched) *
> dbo.ReorderItemsTotals.ReorderLevel * 120) - (CONVERT(Int,
> dbo.ReorderItemsTotals.Stock)) -
> (CONVERT(Int,dbo.ReorderItemsTotals.OnPurchaseOrder)) +
> dbo.ReorderItemsTotals.SumOfSalesBackOrder) > 0) OR
> (dbo.ProductAssembly.SubProduct IS NULL) AND
> (dbo.ProductDetails.Product1 < dbo.ReorderItemsTotalsSets.StockCode)
> AND
> (dbo.ProductDetails.Product1 + dbo.productdetails.product2 <
> dbo.ReorderItemsTotalsSets.ReorderQuantity)
>
--=_NextPart_000_0007_01C804CC.9AEE5810
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Everything seems to check out using SSMS to balance the =parentheses. I do question if the logic is as you intend, given the groupings. =Having the OR out by itself like that is often not what is desired. Also, =do yourself a favor and start using aliases!! :-)
WHERE dbo.ProductAssembly.SubProduct IS NULL AND ((((0.5 * dbo.SoldLast30TotalWith0Total.Despatched + dbo.ReorderItemsTotals.SumOfSalesBackOrder) + (0.5 / 4 * dbo.SoldLast120TotalWith0Total.Despatched) * =dbo.ReorderItemsTotals.ReorderLevel * 120) - =(CONVERT(Int, dbo.ReorderItemsTotals.Stock)) - (CONVERT(Int,dbo.ReorderItemsTotals.OnPurchaseOrder)) + dbo.ReorderItemsTotals.SumOfSalesBackOrder) > =0) OR (dbo.ProductAssembly.SubProduct IS =NULL) AND (dbo.ProductDetails.Product1 < dbo.ReorderItemsTotalsSets.StockCode) AND =(dbo.ProductDetails.Product1 + dbo.productdetails.product2 < dbo.ReorderItemsTotalsSets.ReorderQuantity)
wrote in message news:=1191330035.815038.230250@.n39g2000hsh.googlegroups.com...> Hi All,> > I keep getting the following error with my =WHERE clause.> > Msg 102, Level 15, State 1, Line 47> =Incorrect syntax near '-'.> > We've found that the code works if the =segment " -> (CONVERT(Int,dbo.ReorderItemsTotals.OnPurchaseOrder))" is removed. So> it's clear that the error is within this segment.> > The object "dbo.ReorderItemsTotals" is a view =and uses a UNION command> in the SELECT statement. It joins two =fields and outputs it as one> field - "OnPurchaseOrder". The two =original fields are smallints and> the resulting UNION'd field is a =varchar 10 - however I don't know why> this is.> > Can anyone =help fix this error? My code is as follows.> > Many thanks in =advance,> > Rype> > > > =WHERE> (dbo.ProductAssembly.SubProduct IS NULL) AND> ((((0.5 * dbo.SoldLast30TotalWith0Total.Despatched +> dbo.ReorderItemsTotals.SumOfSalesBackOrder) + (0.5 / 4 *> dbo.SoldLast120TotalWith0Total.Despatched) *> dbo.ReorderItemsTotals.ReorderLevel * 120) - (CONVERT(Int,> dbo.ReorderItemsTotals.Stock)) -> (CONVERT(Int,dbo.ReorderItemsTotals.OnPurchaseOrder)) +> dbo.ReorderItemsTotals.SumOfSalesBackOrder) > 0) OR> (dbo.ProductAssembly.SubProduct IS NULL) AND> (dbo.ProductDetails.Product1 < =dbo.ReorderItemsTotalsSets.StockCode)> AND> (dbo.ProductDetails.Product1 + =dbo.productdetails.product2 <> =dbo.ReorderItemsTotalsSets.ReorderQuantity)>

---

--=_NextPart_000_0007_01C804CC.9AEE5810--

Problem with User Defined Function

I wrote a UDF to get a rolling average based upon a date passed to the UDF. The error I get is:

Server: Msg 102, Level 15, State 1, Procedure fn_RollAverage, Line 26
Incorrect syntax near ')'.

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE FUNCTION fn_RollAverage(@.CURVE_DATE AS SMALLDATETIME)
RETURNS @.TBLRESULT TABLE
(US0001M_Index AS FLOAT,
US0003M_INDEX AS FLOAT,
US0006M_INDEX AS FLOAT,
US0012M_INDEX AS FLOAT,
usswap2_index AS FLOAT,
usswap3_index AS FLOAT,
usswap4_index AS FLOAT,
usswap5_index AS FLOAT,
usswap6_index AS FLOAT,
usswap7_index AS FLOAT,
usswap8_index AS FLOAT,
usswap9_index AS FLOAT,
usswap10_index AS FLOAT,
usswap11_index AS FLOAT,
usswap12_index AS FLOAT,
usswap13_index AS FLOAT,
usswap14_index AS FLOAT,
usswap15_index AS FLOAT,
usswap20_index AS FLOAT,
usswap25_index AS FLOAT,
usswap30_index AS FLOAT)

AS BEGIN

DECLARE @.BEG_DATE AS SMALLDATETIME

SET @.BEG_DATE = (@.CURVE_DATE - 30)

INSERT @.TBLRESULT(
US0001M_Index,
US0003M_INDEX,
US0006M_INDEX,
US0012M_INDEX,
usswap2_index,
usswap3_index,
usswap4_index,
usswap5_index,
usswap6_index,
usswap7_index,
usswap8_index,
usswap9_index,
usswap10_index,
usswap11_index,
usswap12_index,
usswap13_index,
usswap14_index,
usswap15_index,
usswap20_index,
usswap25_index,
usswap30_index)

SELECT
AVG(US0001M_Index) AS US0001M_Index,
AVG(US0003M_INDEX) AS US0003M_INDEX,
AVG(US0006M_INDEX) AS US0006M_INDEX,
AVG(US0012M_INDEX) AS US0012M_INDEX,
AVG(usswap2_index) AS usswap2_index,
AVG(usswap3_index) AS usswap3_index,
AVG(usswap4_index) AS usswap4_index,
AVG(usswap5_index) AS usswap5_index,
AVG(usswap6_index) AS usswap6_index,
AVG(usswap7_index) AS usswap7_index,
AVG(usswap8_index) AS usswap8_index,
AVG(usswap9_index) AS usswap9_index,
AVG(usswap10_index) AS usswap10_index,
AVG(usswap11_index) AS usswap11_index,
AVG(usswap12_index) AS usswap12_index,
AVG(usswap13_index) AS usswap13_index,
AVG(usswap14_index) AS usswap14_index,
AVG(usswap15_index) AS usswap15_index,
AVG(usswap20_index) AS usswap20_index,
AVG(usswap25_index) AS usswap25_index,
AVG(usswap30_index) AS usswap30_index

FROM dbo.LiborSwap
WHERE CURVE_DATE BETWEEN @.BEG_DATE AND @.CURVE_DATE

RETURN
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

DECLARE @.TBLRESULT TABLE
(US0001M_Index FLOAT,
US0003M_INDEX FLOAT,
US0006M_INDEX FLOAT

...)

|||

The " AS " keyword is not valid in the @.TBLRESULT table declaration.

|||Thanks thanks a thousand thanks