OLAP Connection error

Below code at web.config file, when i change Data Source to other pc name....an error occur 'The data provider didn't supply any further error information'...
let said change the datasource 'localhost' to 'webserver' error will occur...so i cannot run the program at my pc and link the olap database at server...

<add key="OLAPConnectionString" value="Provider=MSOLAP.2;Data Source=localhost;Initial Catalog=Foodmart 2000;" />The other PC that you are trying to connect should have the same users and user rights as the first one.

In addition to that, the Analysis Server on your second PC should know about these users as well.

I found that security with Analysis Server is very tight.

Good Luck

OLAP / Analysis services version checking by client

The problem is that i would like to integrate a code to the file in net where MS Office Web Components and OLAP services are beeing used.
Code should automatically detect a version of olap client and office web service components (OWC) and if newer version is needed, then it should download it.
Good instructions were @. http://support.microsoft.com/default.aspx?scid=kb;en-us;312876
where ptssetup.exe included all necessary information, but it was applied to Microsoft SQL Server 2000 Analysis Services. Now what i need to know is how to write a code which is working on Microsoft SQL Server 2005. Or how the old code can be changed to get it work on sql server 2005?

Hello,

I'm going to move your question to one of the SQL Server forums were they should be able to help you.

Daniel Roth

|||

Erkki wrote:

The problem is that i would like to integrate a code to the file in net where MS Office Web Components and OLAP services are beeing used.
Code should automatically detect a version of olap client and office web service components (OWC) and if newer version is needed, then it should download it.
Good instructions were @. http://support.microsoft.com/default.aspx?scid=kb;en-us;312876
where ptssetup.exe included all necessary information, but it was applied to Microsoft SQL Server 2000 Analysis Services. Now what i need to know is how to write a code which is working on Microsoft SQL Server 2005. Or how the old code can be changed to get it work on sql server 2005?

I would like to know that too. Anyway, how is it possible to verify the version of OLAP ? How can it be done? Maybe to somehow connect to server where the olap is being used?

OK, Another Problem

Thanks for all the assistance with the time issue I was having. It looks like I can make the last code work somehow. I'll have to figure out how to incorporate it in, but I think I can probably manage that.

Here's my next question! I have a field in my source table that contains a range of values, separated by a dash. I need to parse this out into two separate destination table fields. The lengths before and after the dash will vary, so I need to be able to read up to the character, then from the other side of the character. Any suggestions on parsing? Books on line has not provided me with the answer (yet).

For example:

0.1-3.7 needs to be 0.1 (Field1) and 3.7 (Field2)
12.5-27
100-199

Thanks! This board has been extremely helpful for me (the pseudo-DBA).
Dirkcheckout CHARINDEX and PATINDEX either one of these should work for you.|||PATINDEX works perfectly for what I need.

Thanks again!
Dirk

OK code experts....

Hi all,
I have been working on this report for a while and I am lost. I am getting
#'s correctly but I am getting too many part numbers. I was wondering, with a
union all query if i can put the where clause on the outside of the union all
statement like the order by clause? And if not does anyone have a suggestion
on what I should do? The first select statement is my problem because I can
not put the where clause of the other two select statements. As soon as I
join all or a combination of tables I get duplicate rows of data.
Here is the syntax...
SELECT *
FROM(
SELECT
PART.PART_NBR AS PART_NBR
,PART.PART_DESC AS PART_DESC
,PART.PART_TYPE AS PART_TYPE
,NULL AS ORD_STAT
,NULL AS ORD_TYPE
,NULL AS RQMT_CODE
,INVD.QTY_ON_HAND AS QTY_ON_HAND
,TO_NUMBER(NULL) AS DEMAND_OVERDUE
,TO_NUMBER(NULL) AS DEMAND_0TO20DAYS
,TO_NUMBER(NULL) AS DEMAND_21TO40DAYS
,TO_NUMBER(NULL) AS DEMAND_41TO60DAYS
,TO_NUMBER(NULL) AS DEMAND_61TO90DAYS
,TO_NUMBER(NULL) AS DEMAND_91TO180DAYS
,TO_NUMBER(NULL) AS DEMAND_BEYOND
,TO_NUMBER(NULL) AS ORDERS_OVERDUE
,TO_NUMBER(NULL) AS ORDERS_0TO20DAYS
,TO_NUMBER(NULL) AS ORDERS_21TO40DAYS
,TO_NUMBER(NULL) AS ORDERS_41TO60DAYS
,TO_NUMBER(NULL) AS ORDERS_61TO90DAYS
,TO_NUMBER(NULL) AS ORDERS_91TO180DAYS
,TO_NUMBER(NULL) AS ORDERS_BEYOND
,TO_DATE(NULL) AS PLAN_RECV_DATE
,TO_DATE(NULL) AS DATE_REQD
,TO_NUMBER(NULL) AS QTY_REQD
,TO_NUMBER(NULL) AS OTY_ISSUED
,TO_NUMBER(NULL) AS DEMAND_DUE
,NULL AS ORD_NBR
,NULL AS LINE_NBR
,NULL AS STATUS
,CASE WHEN
INVD.LOCATION_KEY LIKE '%SCRAP%' OR INVD.LOCATION_KEY LIKE '%DROP%'
THEN 0
ELSE INVD.QTY_ON_HAND
END AS AVAIL_INVENTORY
,CASE WHEN
INVD.LOCATION_KEY LIKE '%LINE%'
THEN INVD.QTY_ON_HAND
ELSE 0
END AS LINE_INVENTORY
,CASE WHEN
INVD.LOCATION_KEY LIKE '%SCRAP%'
THEN INVD.QTY_ON_HAND
ELSE 0
END AS SCRAP_INVENTORY
,CASE WHEN
RPAD(TO_CHAR(INVD.LOCATION_KEY),2) = 'RD' OR RPAD(TO_CHAR(INVD.LOCATION_KEY)
,2) = 'RI'
THEN INVD.QTY_ON_HAND
ELSE 0
END AS RECV_INVENTORY
,CASE WHEN
INVD.LOCATION_KEY LIKE '%DROP%'
THEN INVD.QTY_ON_HAND
ELSE 0
END AS DROP_INVENTORY
,TO_NUMBER(NULL) AS QTY_ON_ORD
,TO_NUMBER(NULL) AS QTY_RECVD
,NULL AS HORIZON
FROM
PART
,INVD
WHERE
PART.PART_NBR = INVD.PART_NBR (+) AND
PART.PART_TYPE = 'P'
UNION ALL
SELECT
PART.PART_NBR AS PART_NBR
,PART.PART_DESC AS PART_DESC
,PART.PART_TYPE AS PART_TYPE
,NULL AS ORD_STAT
,NULL AS ORD_TYPE
,RQMT.RQMT_CODE AS RQMT_CODE
,TO_NUMBER(NULL) AS QTY_ON_HAND
,TO_NUMBER(CASE WHEN
RQMT.DATE_REQD < SYSDATE
THEN (RQMT.QTY_REQD - RQMT.QTY_ISSUED)
ELSE 0
END) AS DEMAND_OVERDUE
,TO_NUMBER(CASE WHEN
RQMT.DATE_REQD >= SYSDATE AND RQMT.DATE_REQD <= (SYSDATE + 20)
THEN (RQMT.QTY_REQD - RQMT.QTY_ISSUED)
ELSE 0
END) AS DEMAND_0TO20DAYS
,TO_NUMBER(CASE WHEN
RQMT.DATE_REQD >= (SYSDATE + 21) AND RQMT.DATE_REQD <= (SYSDATE + 40)
THEN (RQMT.QTY_REQD - RQMT.QTY_ISSUED)
ELSE 0
END) AS DEMAND_21TO40DAYS
,TO_NUMBER(CASE WHEN
RQMT.DATE_REQD >= (SYSDATE + 41) AND RQMT.DATE_REQD <= (SYSDATE + 60)
THEN (RQMT.QTY_REQD - RQMT.QTY_ISSUED)
ELSE 0
END) AS DEMAND_41TO60DAYS
,TO_NUMBER(CASE WHEN
RQMT.DATE_REQD >= (SYSDATE + 61) AND RQMT.DATE_REQD <= (SYSDATE + 90)
THEN (RQMT.QTY_REQD - RQMT.QTY_ISSUED)
ELSE 0
END) AS DEMAND_61TO90DAYS
,TO_NUMBER(CASE WHEN
RQMT.DATE_REQD >= (SYSDATE + 91) AND RQMT.DATE_REQD <= (SYSDATE + 180)
THEN (RQMT.QTY_REQD - RQMT.QTY_ISSUED)
ELSE 0
END) AS DEMAND_91TO180DAYS
,TO_NUMBER(CASE WHEN
RQMT.DATE_REQD >= (SYSDATE + 181)
THEN (RQMT.QTY_REQD - RQMT.QTY_ISSUED)
ELSE 0
END) AS DEMAND_BEYOND
,TO_NUMBER(NULL) AS ORDERS_OVERDUE
,TO_NUMBER(NULL) AS ORDERS_0TO20DAYS
,TO_NUMBER(NULL) AS ORDERS_21TO40DAYS
,TO_NUMBER(NULL) AS ORDERS_41TO60DAYS
,TO_NUMBER(NULL) AS ORDERS_61TO90DAYS
,TO_NUMBER(NULL) AS ORDERS_91TO180DAYS
,TO_NUMBER(NULL) AS ORDERS_BEYOND
,TO_DATE(NULL) AS PLAN_RECV_DATE
,RQMT.DATE_REQD AS DATE_REQD
,RQMT.QTY_REQD AS QTY_REQD
,RQMT.QTY_ISSUED AS OTY_ISSUED
,(RQMT.QTY_REQD - RQMT.QTY_ISSUED) AS DEMAND_DUE
,NULL AS ORD_NBR
,NULL AS LINE_NBR
,NULL AS STATUS
,TO_NUMBER(NULL) AS AVAIL_INVENTORY
,TO_NUMBER(NULL) AS LINE_INVENTORY
,TO_NUMBER(NULL) AS SCRAP_INVENTORY
,TO_NUMBER(NULL) AS RECV_INVENTORY
,TO_NUMBER(NULL) AS DROP_INVENTORY
,TO_NUMBER(NULL) AS QTY_ON_ORD
,TO_NUMBER(NULL) AS QTY_RECVD
,NULL AS HORIZON
FROM
PART
,RQMT
WHERE
PART.PART_NBR = RQMT.COMP_PART_NBR AND
PART.PART_TYPE = 'P' AND
(RQMT.RQMT_CODE <> 'CL' AND
RQMT.RQMT_CODE <> 'IS')
UNION ALL
SELECT
PART.PART_NBR AS PART_NBR
,PART.PART_DESC AS PART_DESC
,PART.PART_TYPE AS PART_TYPE
,OORD.ORD_STAT AS ORD_STAT
,OORD.ORD_TYPE AS ORD_TYPE
,NULL AS RQMT_CODE
,TO_NUMBER(NULL) AS QTY_ON_HAND
,TO_NUMBER(NULL) AS DEMAND_OVERDUE
,TO_NUMBER(NULL) AS DEMAND_0TO20DAYS
,TO_NUMBER(NULL) AS DEMAND_21TO40DAYS
,TO_NUMBER(NULL) AS DEMAND_41TO60DAYS
,TO_NUMBER(NULL) AS DEMAND_61TO90DAYS
,TO_NUMBER(NULL) AS DEMAND_91TO180DAYS
,TO_NUMBER(NULL) AS DEMAND_BEYOND
,CASE WHEN
OORD.PLAN_RECV_DATE < SYSDATE
THEN (OORD.QTY_ON_ORD - OORD.QTY_RECVD)
ELSE 0 END AS ORDERS_OVERDUE
,CASE WHEN
OORD.PLAN_RECV_DATE >= SYSDATE AND OORD.PLAN_RECV_DATE <= (SYSDATE + 20)
THEN (OORD.QTY_ON_ORD - OORD.QTY_RECVD)
ELSE 0 END AS ORDERS_0TO20DAYS
,CASE WHEN
OORD.PLAN_RECV_DATE >= SYSDATE AND OORD.PLAN_RECV_DATE >= (SYSDATE + 21)
AND
OORD.PLAN_RECV_DATE <= (SYSDATE + 40)
THEN (OORD.QTY_ON_ORD - OORD.QTY_RECVD)
ELSE 0 END AS ORDERS_21TO40DAYS
,CASE WHEN
OORD.PLAN_RECV_DATE >= SYSDATE AND OORD.PLAN_RECV_DATE >= (SYSDATE + 41)
AND
OORD.PLAN_RECV_DATE <= (SYSDATE + 60)
THEN (OORD.QTY_ON_ORD - OORD.QTY_RECVD)
ELSE 0
END AS ORDERS_41TO60DAYS
,CASE WHEN
OORD.PLAN_RECV_DATE >= SYSDATE AND OORD.PLAN_RECV_DATE >= (SYSDATE + 61)
AND
OORD.PLAN_RECV_DATE <= (SYSDATE + 90)
THEN (OORD.QTY_ON_ORD - OORD.QTY_RECVD)
ELSE 0
END AS ORDERS_61TO90DAYS
,CASE WHEN
OORD.PLAN_RECV_DATE >= SYSDATE AND OORD.PLAN_RECV_DATE >= (SYSDATE + 91)
AND
OORD.PLAN_RECV_DATE <= (SYSDATE + 180)
THEN (OORD.QTY_ON_ORD - OORD.QTY_RECVD)
ELSE 0
END AS ORDERS_91TO180DAYS
,CASE WHEN
OORD.PLAN_RECV_DATE >= SYSDATE AND OORD.PLAN_RECV_DATE >= (SYSDATE + 181)
THEN (OORD.QTY_ON_ORD - OORD.QTY_RECVD)
ELSE 0
END AS ORDERS_BEYOND
,OORD.PLAN_RECV_DATE AS PLAN_RECV_DATE
,TO_DATE(NULL) AS DATE_REQD
,TO_NUMBER(NULL) AS QTY_REQD
,TO_NUMBER(NULL) AS OTY_ISSUED
,TO_NUMBER(NULL) AS DEMAND_DUE
,OORD.ORD_NBR AS ORD_NBR
,OORD.SUB_ORD_NBR AS LINE_NBR
,OORD.ORD_STAT AS STATUS
,TO_NUMBER(NULL) AS AVAIL_INVENTORY
,TO_NUMBER(NULL) AS LINE_INVENTORY
,TO_NUMBER(NULL) AS SCRAP_INVENTORY
,TO_NUMBER(NULL) AS RECV_INVENTORY
,TO_NUMBER(NULL) AS DROP_INVENTORY
,OORD.QTY_ON_ORD AS QTY_ON_ORD
,OORD.QTY_RECVD AS QTY_RECVD
,CASE WHEN PLAN_RECV_DATE < SYSDATE THEN 'OVERDUE'
WHEN PLAN_RECV_DATE >= SYSDATE AND PLAN_RECV_DATE <= (SYSDATE + 20) THEN
'0 To 20 Days'
WHEN PLAN_RECV_DATE >= (SYSDATE + 21) AND PLAN_RECV_DATE <= (SYSDATE +
40) THEN '21 To 40 Days'
WHEN PLAN_RECV_DATE >= (SYSDATE + 41) AND PLAN_RECV_DATE <= (SYSDATE +
60) THEN '41 To 60 Days'
WHEN PLAN_RECV_DATE >= (SYSDATE + 61) AND PLAN_RECV_DATE <= (SYSDATE +
90) THEN '61 To 90 Days'
WHEN PLAN_RECV_DATE >= (SYSDATE + 91) AND PLAN_RECV_DATE <= (SYSDATE +
180) THEN '91 To 180 Days'
ELSE 'BEYOND' END AS HORIZON
FROM
PART
,OORD
WHERE
PART.PART_NBR = OORD.PART_NBR (+) AND
PART.PART_TYPE = 'P' AND
OORD.ORD_TYPE = 'PO' AND
(OORD.ORD_STAT <> '--' AND
OORD.ORD_STAT <> 'OR' AND
OORD.ORD_STAT <> 'FP' AND
OORD.ORD_STAT <> 'PL' AND
OORD.ORD_STAT <> 'CL' )
)
ORDER BY 1 ,25,28,29
Any suggestions will be appreciated.
Thanks in advance!!!!
Kerrie
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200602/1Hi,
"UNION ALL" will include all records ie even duplicate records. Try putting
"Union"
It will eliminate duplicate records.
Regards
Amarnath
"Ksorrell via SQLMonster.com" wrote:
> Hi all,
> I have been working on this report for a while and I am lost. I am getting
> #'s correctly but I am getting too many part numbers. I was wondering, with a
> union all query if i can put the where clause on the outside of the union all
> statement like the order by clause? And if not does anyone have a suggestion
> on what I should do? The first select statement is my problem because I can
> not put the where clause of the other two select statements. As soon as I
> join all or a combination of tables I get duplicate rows of data.
> Here is the syntax...
> SELECT *
> FROM(
> SELECT
> PART.PART_NBR AS PART_NBR
> ,PART.PART_DESC AS PART_DESC
> ,PART.PART_TYPE AS PART_TYPE
> ,NULL AS ORD_STAT
> ,NULL AS ORD_TYPE
> ,NULL AS RQMT_CODE
> ,INVD.QTY_ON_HAND AS QTY_ON_HAND
> ,TO_NUMBER(NULL) AS DEMAND_OVERDUE
> ,TO_NUMBER(NULL) AS DEMAND_0TO20DAYS
> ,TO_NUMBER(NULL) AS DEMAND_21TO40DAYS
> ,TO_NUMBER(NULL) AS DEMAND_41TO60DAYS
> ,TO_NUMBER(NULL) AS DEMAND_61TO90DAYS
> ,TO_NUMBER(NULL) AS DEMAND_91TO180DAYS
> ,TO_NUMBER(NULL) AS DEMAND_BEYOND
> ,TO_NUMBER(NULL) AS ORDERS_OVERDUE
> ,TO_NUMBER(NULL) AS ORDERS_0TO20DAYS
> ,TO_NUMBER(NULL) AS ORDERS_21TO40DAYS
> ,TO_NUMBER(NULL) AS ORDERS_41TO60DAYS
> ,TO_NUMBER(NULL) AS ORDERS_61TO90DAYS
> ,TO_NUMBER(NULL) AS ORDERS_91TO180DAYS
> ,TO_NUMBER(NULL) AS ORDERS_BEYOND
> ,TO_DATE(NULL) AS PLAN_RECV_DATE
> ,TO_DATE(NULL) AS DATE_REQD
> ,TO_NUMBER(NULL) AS QTY_REQD
> ,TO_NUMBER(NULL) AS OTY_ISSUED
> ,TO_NUMBER(NULL) AS DEMAND_DUE
> ,NULL AS ORD_NBR
> ,NULL AS LINE_NBR
> ,NULL AS STATUS
> ,CASE WHEN
> INVD.LOCATION_KEY LIKE '%SCRAP%' OR INVD.LOCATION_KEY LIKE '%DROP%'
> THEN 0
> ELSE INVD.QTY_ON_HAND
> END AS AVAIL_INVENTORY
> ,CASE WHEN
> INVD.LOCATION_KEY LIKE '%LINE%'
> THEN INVD.QTY_ON_HAND
> ELSE 0
> END AS LINE_INVENTORY
> ,CASE WHEN
> INVD.LOCATION_KEY LIKE '%SCRAP%'
> THEN INVD.QTY_ON_HAND
> ELSE 0
> END AS SCRAP_INVENTORY
> ,CASE WHEN
> RPAD(TO_CHAR(INVD.LOCATION_KEY),2) = 'RD' OR RPAD(TO_CHAR(INVD.LOCATION_KEY)
> ,2) = 'RI'
> THEN INVD.QTY_ON_HAND
> ELSE 0
> END AS RECV_INVENTORY
> ,CASE WHEN
> INVD.LOCATION_KEY LIKE '%DROP%'
> THEN INVD.QTY_ON_HAND
> ELSE 0
> END AS DROP_INVENTORY
> ,TO_NUMBER(NULL) AS QTY_ON_ORD
> ,TO_NUMBER(NULL) AS QTY_RECVD
> ,NULL AS HORIZON
> FROM
> PART
> ,INVD
> WHERE
> PART.PART_NBR = INVD.PART_NBR (+) AND
> PART.PART_TYPE = 'P'
> UNION ALL
> SELECT
> PART.PART_NBR AS PART_NBR
> ,PART.PART_DESC AS PART_DESC
> ,PART.PART_TYPE AS PART_TYPE
> ,NULL AS ORD_STAT
> ,NULL AS ORD_TYPE
> ,RQMT.RQMT_CODE AS RQMT_CODE
> ,TO_NUMBER(NULL) AS QTY_ON_HAND
> ,TO_NUMBER(CASE WHEN
> RQMT.DATE_REQD < SYSDATE
> THEN (RQMT.QTY_REQD - RQMT.QTY_ISSUED)
> ELSE 0
> END) AS DEMAND_OVERDUE
> ,TO_NUMBER(CASE WHEN
> RQMT.DATE_REQD >= SYSDATE AND RQMT.DATE_REQD <= (SYSDATE + 20)
> THEN (RQMT.QTY_REQD - RQMT.QTY_ISSUED)
> ELSE 0
> END) AS DEMAND_0TO20DAYS
> ,TO_NUMBER(CASE WHEN
> RQMT.DATE_REQD >= (SYSDATE + 21) AND RQMT.DATE_REQD <= (SYSDATE + 40)
> THEN (RQMT.QTY_REQD - RQMT.QTY_ISSUED)
> ELSE 0
> END) AS DEMAND_21TO40DAYS
> ,TO_NUMBER(CASE WHEN
> RQMT.DATE_REQD >= (SYSDATE + 41) AND RQMT.DATE_REQD <= (SYSDATE + 60)
> THEN (RQMT.QTY_REQD - RQMT.QTY_ISSUED)
> ELSE 0
> END) AS DEMAND_41TO60DAYS
> ,TO_NUMBER(CASE WHEN
> RQMT.DATE_REQD >= (SYSDATE + 61) AND RQMT.DATE_REQD <= (SYSDATE + 90)
> THEN (RQMT.QTY_REQD - RQMT.QTY_ISSUED)
> ELSE 0
> END) AS DEMAND_61TO90DAYS
> ,TO_NUMBER(CASE WHEN
> RQMT.DATE_REQD >= (SYSDATE + 91) AND RQMT.DATE_REQD <= (SYSDATE + 180)
> THEN (RQMT.QTY_REQD - RQMT.QTY_ISSUED)
> ELSE 0
> END) AS DEMAND_91TO180DAYS
> ,TO_NUMBER(CASE WHEN
> RQMT.DATE_REQD >= (SYSDATE + 181)
> THEN (RQMT.QTY_REQD - RQMT.QTY_ISSUED)
> ELSE 0
> END) AS DEMAND_BEYOND
> ,TO_NUMBER(NULL) AS ORDERS_OVERDUE
> ,TO_NUMBER(NULL) AS ORDERS_0TO20DAYS
> ,TO_NUMBER(NULL) AS ORDERS_21TO40DAYS
> ,TO_NUMBER(NULL) AS ORDERS_41TO60DAYS
> ,TO_NUMBER(NULL) AS ORDERS_61TO90DAYS
> ,TO_NUMBER(NULL) AS ORDERS_91TO180DAYS
> ,TO_NUMBER(NULL) AS ORDERS_BEYOND
> ,TO_DATE(NULL) AS PLAN_RECV_DATE
> ,RQMT.DATE_REQD AS DATE_REQD
> ,RQMT.QTY_REQD AS QTY_REQD
> ,RQMT.QTY_ISSUED AS OTY_ISSUED
> ,(RQMT.QTY_REQD - RQMT.QTY_ISSUED) AS DEMAND_DUE
> ,NULL AS ORD_NBR
> ,NULL AS LINE_NBR
> ,NULL AS STATUS
> ,TO_NUMBER(NULL) AS AVAIL_INVENTORY
> ,TO_NUMBER(NULL) AS LINE_INVENTORY
> ,TO_NUMBER(NULL) AS SCRAP_INVENTORY
> ,TO_NUMBER(NULL) AS RECV_INVENTORY
> ,TO_NUMBER(NULL) AS DROP_INVENTORY
> ,TO_NUMBER(NULL) AS QTY_ON_ORD
> ,TO_NUMBER(NULL) AS QTY_RECVD
> ,NULL AS HORIZON
> FROM
> PART
> ,RQMT
> WHERE
> PART.PART_NBR = RQMT.COMP_PART_NBR AND
> PART.PART_TYPE = 'P' AND
> (RQMT.RQMT_CODE <> 'CL' AND
> RQMT.RQMT_CODE <> 'IS')
> UNION ALL
> SELECT
> PART.PART_NBR AS PART_NBR
> ,PART.PART_DESC AS PART_DESC
> ,PART.PART_TYPE AS PART_TYPE
> ,OORD.ORD_STAT AS ORD_STAT
> ,OORD.ORD_TYPE AS ORD_TYPE
> ,NULL AS RQMT_CODE
> ,TO_NUMBER(NULL) AS QTY_ON_HAND
> ,TO_NUMBER(NULL) AS DEMAND_OVERDUE
> ,TO_NUMBER(NULL) AS DEMAND_0TO20DAYS
> ,TO_NUMBER(NULL) AS DEMAND_21TO40DAYS
> ,TO_NUMBER(NULL) AS DEMAND_41TO60DAYS
> ,TO_NUMBER(NULL) AS DEMAND_61TO90DAYS
> ,TO_NUMBER(NULL) AS DEMAND_91TO180DAYS
> ,TO_NUMBER(NULL) AS DEMAND_BEYOND
> ,CASE WHEN
> OORD.PLAN_RECV_DATE < SYSDATE
> THEN (OORD.QTY_ON_ORD - OORD.QTY_RECVD)
> ELSE 0 END AS ORDERS_OVERDUE
> ,CASE WHEN
> OORD.PLAN_RECV_DATE >= SYSDATE AND OORD.PLAN_RECV_DATE <= (SYSDATE + 20)
> THEN (OORD.QTY_ON_ORD - OORD.QTY_RECVD)
> ELSE 0 END AS ORDERS_0TO20DAYS
> ,CASE WHEN
> OORD.PLAN_RECV_DATE >= SYSDATE AND OORD.PLAN_RECV_DATE >= (SYSDATE + 21)
> AND
> OORD.PLAN_RECV_DATE <= (SYSDATE + 40)
> THEN (OORD.QTY_ON_ORD - OORD.QTY_RECVD)
> ELSE 0 END AS ORDERS_21TO40DAYS
> ,CASE WHEN
> OORD.PLAN_RECV_DATE >= SYSDATE AND OORD.PLAN_RECV_DATE >= (SYSDATE + 41)
> AND
> OORD.PLAN_RECV_DATE <= (SYSDATE + 60)
> THEN (OORD.QTY_ON_ORD - OORD.QTY_RECVD)
> ELSE 0
> END AS ORDERS_41TO60DAYS
> ,CASE WHEN
> OORD.PLAN_RECV_DATE >= SYSDATE AND OORD.PLAN_RECV_DATE >= (SYSDATE + 61)
> AND
> OORD.PLAN_RECV_DATE <= (SYSDATE + 90)
> THEN (OORD.QTY_ON_ORD - OORD.QTY_RECVD)
> ELSE 0
> END AS ORDERS_61TO90DAYS
> ,CASE WHEN
> OORD.PLAN_RECV_DATE >= SYSDATE AND OORD.PLAN_RECV_DATE >= (SYSDATE + 91)
> AND
> OORD.PLAN_RECV_DATE <= (SYSDATE + 180)
> THEN (OORD.QTY_ON_ORD - OORD.QTY_RECVD)
> ELSE 0
> END AS ORDERS_91TO180DAYS
> ,CASE WHEN
> OORD.PLAN_RECV_DATE >= SYSDATE AND OORD.PLAN_RECV_DATE >= (SYSDATE + 181)
> THEN (OORD.QTY_ON_ORD - OORD.QTY_RECVD)
> ELSE 0
> END AS ORDERS_BEYOND
> ,OORD.PLAN_RECV_DATE AS PLAN_RECV_DATE
> ,TO_DATE(NULL) AS DATE_REQD
> ,TO_NUMBER(NULL) AS QTY_REQD
> ,TO_NUMBER(NULL) AS OTY_ISSUED
> ,TO_NUMBER(NULL) AS DEMAND_DUE
> ,OORD.ORD_NBR AS ORD_NBR
> ,OORD.SUB_ORD_NBR AS LINE_NBR
> ,OORD.ORD_STAT AS STATUS
> ,TO_NUMBER(NULL) AS AVAIL_INVENTORY
> ,TO_NUMBER(NULL) AS LINE_INVENTORY
> ,TO_NUMBER(NULL) AS SCRAP_INVENTORY
> ,TO_NUMBER(NULL) AS RECV_INVENTORY
> ,TO_NUMBER(NULL) AS DROP_INVENTORY
> ,OORD.QTY_ON_ORD AS QTY_ON_ORD
> ,OORD.QTY_RECVD AS QTY_RECVD
> ,CASE WHEN PLAN_RECV_DATE < SYSDATE THEN 'OVERDUE'
> WHEN PLAN_RECV_DATE >= SYSDATE AND PLAN_RECV_DATE <= (SYSDATE + 20) THEN
> '0 To 20 Days'
> WHEN PLAN_RECV_DATE >= (SYSDATE + 21) AND PLAN_RECV_DATE <= (SYSDATE +
> 40) THEN '21 To 40 Days'
> WHEN PLAN_RECV_DATE >= (SYSDATE + 41) AND PLAN_RECV_DATE <= (SYSDATE +
> 60) THEN '41 To 60 Days'
> WHEN PLAN_RECV_DATE >= (SYSDATE + 61) AND PLAN_RECV_DATE <= (SYSDATE +
> 90) THEN '61 To 90 Days'
> WHEN PLAN_RECV_DATE >= (SYSDATE + 91) AND PLAN_RECV_DATE <= (SYSDATE +
> 180) THEN '91 To 180 Days'
> ELSE 'BEYOND' END AS HORIZON
> FROM
> PART
> ,OORD
> WHERE
> PART.PART_NBR = OORD.PART_NBR (+) AND
> PART.PART_TYPE = 'P' AND
> OORD.ORD_TYPE = 'PO' AND
> (OORD.ORD_STAT <> '--' AND
> OORD.ORD_STAT <> 'OR' AND
> OORD.ORD_STAT <> 'FP' AND
> OORD.ORD_STAT <> 'PL' AND
> OORD.ORD_STAT <> 'CL' )
> )
> ORDER BY 1 ,25,28,29
> Any suggestions will be appreciated.
> Thanks in advance!!!!
> Kerrie
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200602/1
>|||Thanks for replying!!!!!!
I finally figured it out last night. I had to group by one of the select
statements (INVD Table) and do complex where statements on the INVD and OORD
selects'. For example...
FROM
INVD
WHERE
INVD.PART_NBR IN (SELECT
PART.PART_NBR AS PART_NBR
FROM
PART
,RQMT
WHERE
PART.PART_NBR = RQMT.COMP_PART_NBR AND
PART.PART_TYPE = 'P' AND
(RQMT.RQMT_CODE <> 'CL' AND
RQMT.RQMT_CODE <> 'IS'))
I only wanted the information coming out of the RQMT table (requirements).
Thanks for your help, I do appreciate it.
Kerrie
Amarnath wrote:
>Hi,
>"UNION ALL" will include all records ie even duplicate records. Try putting
>"Union"
>It will eliminate duplicate records.
>Regards
>Amarnath
>> Hi all,
>[quoted text clipped - 253 lines]
>> Kerrie
--
Message posted via http://www.sqlmonster.com

Odd: Inserts and updates happen before running code in debugger

Hi,

Before stepping into ado.net code to perform an insert or update, the insert / update has already taken place, just on starting the debugger. I use VS 2005 on SQL Server 2000. This did not happen with VS 2003 and SQL Server 2000.

Anyone else encountered this?The VS 2005 Development Server caused the problem.

On changing to IIS 5 for execution, when debugging, the ado.net code only executed when I had stepped through it. Therefore if I stopped debugging before I stepped through the ado.net code, the database was not updated. However, in using VS 2005 Development Server, the database was already updated, even before I had stepped through the first line of code!

Can someone try this finding out?|||On some apps, I continue to see database changes when no ado.net code has been run in the debugger. This is using IIS or the built in server.

On others, no database changes take place when no ado.net code has been run in the debugger. There must be a bug.

Odd sqldatasource insert behavior

I have a sqldatasource (code listed below) whose insert Paramaters are control parameters. My aspx page has a textbox and a submit button. the button onclick runs the sqdatasource1.insert.

What I get is every other insert inserts the text in textbox2 and every other insert enters nothing for the namecust value. I have a required field validator which correctly prevents submission if textbox2 is empty.

How do I fix this?

:<code>

<asp:PanelID="Panel1"runat="server"Height="50px"Width="548px">

<asp:ButtonID="Button1"runat="server"Text="New Prospect"ValidationGroup="insertCust"/>

<asp:RequiredFieldValidatorID="RequiredFieldValidator1"runat="server"ControlToValidate="TextBox2"

ErrorMessage="Prospect Name can not be blank"ValidationGroup="insertCust"></asp:RequiredFieldValidator>

<asp:TextBoxID="TextBox2"runat="server"Width="330px"ValidationGroup="insertCust"></asp:TextBox></asp:Panel>

<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:AccPac2ConnectionString %>"

SelectCommand="SELECT DISTINCT CODETERR FROM dbo.F_arcus() AS F_arcus_1 WHERE (DATEINAC = 0) AND (rtrim(CODETERR) <>'')">

</asp:SqlDataSource>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:AccPac2ConnectionString %>"

InsertCommand="INSERT INTO dbo.BudgetProspects(NameCust, CodeTerr) VALUES (@.Namecust, @.codeterr)"

SelectCommand="SELECT CustomerID, NameCust FROM dbo.BudgetProspects WHERE (CodeTerr = @.codeterr)"

UpdateCommand="UPDATE dbo.BudgetProspects SET NameCust = @.namecust">

<UpdateParameters>

<asp:ParameterName="namecust"/>

</UpdateParameters>

<SelectParameters>

<asp:ControlParameterControlID="RadioButtonList1"Name="codeterr"PropertyName="SelectedValue"/>

</SelectParameters>

<InsertParameters>

<asp:ControlParameterControlID="textbox2"Name="Namecust"PropertyName="text"/>

<asp:ControlParameterControlID="RadioButtonList1"Name="codeterr"PropertyName="SelectedValue"/>

</InsertParameters>

</asp:SqlDataSource>

</code>

codebehind button_click:

<code>

ProtectedSub Button1_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles Button1.Click

IfNot TextBox2.TextIsNothingThen

SqlDataSource1.Insert()

TextBox2.Text =""

EndIf

EndSub

</code>

Never mind, complete stupidity on my part the altenate row style had white background and white text.

Odd issue with bound columns when fetching data.

I'm using ODBC 3.0 in code written in C.

I have an service that connects using a system DSN using the SQL Server 2000 driver. On my development system with SQL Server 2005 Express installed, the queries work fine. I prepare a statement and then bind the columns that will be in the result set. On my system, I get all the data as it should be but on a live system, I do not get the proper data for the last three rows. I don't get any error messages from the query or the column binding and there are also no extra information messages that I can retrieve.

The table is something like the following. The names are changed to protect the guilty.

CREATE TABLE DOWNLOAD

(

FIELD_1 NUMERIC( 6,0 ),

ID CHAR( 15),

FNAME( 30 ),

LNAME( 40 ),

CLIENT_ID NUMERIC( 9,0),

CLIENT_NAME CHAR( 50 ),

CODE NUMERIC( 4,0 ),

PHONE CHAR( 10 )

)

go

On my system, running the exact same binary code as the client machine, I get all of the data from all of the columns as it should be. The problem is when I run on a client's system, the CLIENT_NAME, CODE, and PHONE columns return null values even when there is data there. On my development system the SQL Server instance runs on the same machine. On the client's sytem I am connecting to a remote instance of SQL Server 2000 on another system on the network.

My quandry is what could be different between the two systems that is causing me the problems?

Can you ran SQL Profiler on both machines and see if there is any difference?|||I've turned the logging on at the client side. The logs don't show any errors and it looks like the column binding in SQLBindCol is correct. I haven't seen the server side logs but the gurus on the server side say that they can't see anything wrong there. I've played around with the SQL statement and tried using CONVERT() to change the CHAR to VARCHAR but that doesn't seem to help.

I'm at a total loss why the same code running on my development system runs fine but fails on another system. My guess is that there is some configuration difference but I don't know what that is because I am using the samve version of the SQL Server 2000 ODBC driver on both machines. Its not like there is anything odd or non standard in the table definition either.

|||Just as an update and possibly more information.

I've got it working now but I don't like the solution. What I ended up doing is not binding the columns and calling SQLGetData() after the fetch to get the column data. I don't like this because it costs me about 500 ms for the each time I call the function.

I'd still like to find a solution for why the bound columns don't work.
|||1) I would still recommend you to run SQL Profiler on the servers.
2) How do you bind exactly? Is it possible for you to write (and post the source here) a small ODBC application which demonstrates this problem?|||This is the code I use to bind the columns:

BOOL BindRecord( SQLHSTMT hStmt, PACCDATA_FIELD *pRecord, PINT piRecCount )
{
BOOL bRval = FALSE;
PACCDATA_FIELD pField;
SQLRETURN r;
INT iCnt;
CHAR szBuf[128];
SQLINTEGER len;

if( pRecord && *pRecord )
{
for( iCnt = 0; iCnt < *piRecCount; iCnt++ )
{
pField = *pRecord + iCnt;
len = pField->nLen;

switch( pField->iType )
{
case SQL_CHAR:
r = SQLBindCol( hStmt, pField->iColNum, SQL_C_CHAR, pField->pcVal, pField->nDataSize, &len );
break;

case SQL_DATETIME:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->ucVal, pField->nDataSize, &len );
break;

case SQL_DECIMAL:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->ucVal, pField->nDataSize, &len );
break;

case SQL_NUMERIC:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->lVal, pField->nDataSize, &len );
break;

case SQL_INTEGER:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->lVal, pField->nDataSize, &len );
break;

case SQL_SMALLINT:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->sVal, pField->nDataSize, &len );
break;

case SQL_DOUBLE:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->dVal, pField->nDataSize, &len );
break;

case SQL_FLOAT:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->dVal, pField->nDataSize, &len );
break;

case SQL_REAL:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->fVal, pField->nDataSize, &len );
break;

case SQL_VARCHAR:
r = SQLBindCol( hStmt, pField->iColNum, SQL_C_CHAR, pField->pvcVal, pField->nDataSize + 1, &len );
break;

case SQL_UNKNOWN_TYPE:
ST_WriteLog( "st_ctimpact", "BindRecord", "Field data type is SQL_UNKNOWN_TYPE", ST_LOG_DEBUG );
default:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, pField->ptr, pField->nDataSize, &len );
break;
}

if( r == SQL_SUCCESS_WITH_INFO )
{
lstrcpy( szBuf, "Field name: " );
lstrcat( szBuf, pField->szColName );
ST_WriteLog( "st_AcceleratedData", "BindRecord", szBuf, ST_LOG_WARNING );
ShowSQLMessages( "BindRecord", SQL_HANDLE_STMT, hStmt );
bRval = TRUE;
}
else if( r != SQL_SUCCESS )
{
lstrcpy( szBuf, "Field name: " );
lstrcat( szBuf, pField->szColName );
ST_WriteLog( "st_AcceleratedData", "BindRecord", szBuf, ST_LOG_WARNING );
ShowSQLErrors( "BindRecord", SQL_HANDLE_STMT, hStmt );
}
else
bRval = TRUE;
}
}
return( bRval );
}

A key to remember is that other columns that are bound in this way, and have the same data types, are returning data just fine. I've looked at the logs on the client side where this code works, and there are no errors and the prameters look correct as far as I can tell. Also this code works as advertized on my system but not on the live system. Right now, I don't think my customer will be willing to let me mess around on their system trying to come up with an application that would recreate the problem. Also note that I cannot recreate the problem on my own system. I had hoped that it would be broken on my system so I could debug and find out what was the problem but because it works, I can't.
|||I do not think you have to mess around with your customer system.
I would suggest you write a straightforward ODBC application from scratch just going against this very table.
Use some static arrays and bind directly with SQLBindCol, so to exclude any dependecies on how PACCDATA_FIELD or other parts of your custom code are written.
Then when you are sure it works as supposed on your machine, try it against your customer's.|||Then I guess you didn't read the thread all that well. I don't have to write a test app. The live code works fine on my system on the same table definition in the same database name as the live system. The problem is when I deployed the code, some of the bound columns don't return data. Since I can't recreate the problem on my system, I'm at a loss as to what the problem is.
|||I'm not sure if it is possilbe to resolve your issue without actual experimentation. Your system is not identical to the live system, is it? So your argument that the code works on your system is not helpful to you. If you really want to find out where the problem is you might try and start narrowing down on the issue. That is what I suggested to you.|||It just seemed to me that you asking me to make a test case that is exactly what I have running. I think I might have it working now and it must have something to do with my adding RTRIM() and CONVERT() functions in the select statement. I removed them and it seems to work better now.

Odd issue with bound columns when fetching data.

I'm using ODBC 3.0 in code written in C.

I have an service that connects using a system DSN using the SQL Server 2000 driver. On my development system with SQL Server 2005 Express installed, the queries work fine. I prepare a statement and then bind the columns that will be in the result set. On my system, I get all the data as it should be but on a live system, I do not get the proper data for the last three rows. I don't get any error messages from the query or the column binding and there are also no extra information messages that I can retrieve.

The table is something like the following. The names are changed to protect the guilty.

CREATE TABLE DOWNLOAD

(

FIELD_1 NUMERIC( 6,0 ),

ID CHAR( 15),

FNAME( 30 ),

LNAME( 40 ),

CLIENT_ID NUMERIC( 9,0),

CLIENT_NAME CHAR( 50 ),

CODE NUMERIC( 4,0 ),

PHONE CHAR( 10 )

)

go

On my system, running the exact same binary code as the client machine, I get all of the data from all of the columns as it should be. The problem is when I run on a client's system, the CLIENT_NAME, CODE, and PHONE columns return null values even when there is data there. On my development system the SQL Server instance runs on the same machine. On the client's sytem I am connecting to a remote instance of SQL Server 2000 on another system on the network.

My quandry is what could be different between the two systems that is causing me the problems?

Can you ran SQL Profiler on both machines and see if there is any difference?|||I've turned the logging on at the client side. The logs don't show any errors and it looks like the column binding in SQLBindCol is correct. I haven't seen the server side logs but the gurus on the server side say that they can't see anything wrong there. I've played around with the SQL statement and tried using CONVERT() to change the CHAR to VARCHAR but that doesn't seem to help.

I'm at a total loss why the same code running on my development system runs fine but fails on another system. My guess is that there is some configuration difference but I don't know what that is because I am using the samve version of the SQL Server 2000 ODBC driver on both machines. Its not like there is anything odd or non standard in the table definition either.

|||Just as an update and possibly more information.

I've got it working now but I don't like the solution. What I ended up doing is not binding the columns and calling SQLGetData() after the fetch to get the column data. I don't like this because it costs me about 500 ms for the each time I call the function.

I'd still like to find a solution for why the bound columns don't work.
|||1) I would still recommend you to run SQL Profiler on the servers.
2) How do you bind exactly? Is it possible for you to write (and post the source here) a small ODBC application which demonstrates this problem?|||This is the code I use to bind the columns:

BOOL BindRecord( SQLHSTMT hStmt, PACCDATA_FIELD *pRecord, PINT piRecCount )
{
BOOL bRval = FALSE;
PACCDATA_FIELD pField;
SQLRETURN r;
INT iCnt;
CHAR szBuf[128];
SQLINTEGER len;

if( pRecord && *pRecord )
{
for( iCnt = 0; iCnt < *piRecCount; iCnt++ )
{
pField = *pRecord + iCnt;
len = pField->nLen;

switch( pField->iType )
{
case SQL_CHAR:
r = SQLBindCol( hStmt, pField->iColNum, SQL_C_CHAR, pField->pcVal, pField->nDataSize, &len );
break;

case SQL_DATETIME:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->ucVal, pField->nDataSize, &len );
break;

case SQL_DECIMAL:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->ucVal, pField->nDataSize, &len );
break;

case SQL_NUMERIC:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->lVal, pField->nDataSize, &len );
break;

case SQL_INTEGER:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->lVal, pField->nDataSize, &len );
break;

case SQL_SMALLINT:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->sVal, pField->nDataSize, &len );
break;

case SQL_DOUBLE:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->dVal, pField->nDataSize, &len );
break;

case SQL_FLOAT:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->dVal, pField->nDataSize, &len );
break;

case SQL_REAL:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->fVal, pField->nDataSize, &len );
break;

case SQL_VARCHAR:
r = SQLBindCol( hStmt, pField->iColNum, SQL_C_CHAR, pField->pvcVal, pField->nDataSize + 1, &len );
break;

case SQL_UNKNOWN_TYPE:
ST_WriteLog( "st_ctimpact", "BindRecord", "Field data type is SQL_UNKNOWN_TYPE", ST_LOG_DEBUG );
default:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, pField->ptr, pField->nDataSize, &len );
break;
}

if( r == SQL_SUCCESS_WITH_INFO )
{
lstrcpy( szBuf, "Field name: " );
lstrcat( szBuf, pField->szColName );
ST_WriteLog( "st_AcceleratedData", "BindRecord", szBuf, ST_LOG_WARNING );
ShowSQLMessages( "BindRecord", SQL_HANDLE_STMT, hStmt );
bRval = TRUE;
}
else if( r != SQL_SUCCESS )
{
lstrcpy( szBuf, "Field name: " );
lstrcat( szBuf, pField->szColName );
ST_WriteLog( "st_AcceleratedData", "BindRecord", szBuf, ST_LOG_WARNING );
ShowSQLErrors( "BindRecord", SQL_HANDLE_STMT, hStmt );
}
else
bRval = TRUE;
}
}
return( bRval );
}

A key to remember is that other columns that are bound in this way, and have the same data types, are returning data just fine. I've looked at the logs on the client side where this code works, and there are no errors and the prameters look correct as far as I can tell. Also this code works as advertized on my system but not on the live system. Right now, I don't think my customer will be willing to let me mess around on their system trying to come up with an application that would recreate the problem. Also note that I cannot recreate the problem on my own system. I had hoped that it would be broken on my system so I could debug and find out what was the problem but because it works, I can't.
|||I do not think you have to mess around with your customer system.
I would suggest you write a straightforward ODBC application from scratch just going against this very table.
Use some static arrays and bind directly with SQLBindCol, so to exclude any dependecies on how PACCDATA_FIELD or other parts of your custom code are written.
Then when you are sure it works as supposed on your machine, try it against your customer's.|||Then I guess you didn't read the thread all that well. I don't have to write a test app. The live code works fine on my system on the same table definition in the same database name as the live system. The problem is when I deployed the code, some of the bound columns don't return data. Since I can't recreate the problem on my system, I'm at a loss as to what the problem is.
|||I'm not sure if it is possilbe to resolve your issue without actual experimentation. Your system is not identical to the live system, is it? So your argument that the code works on your system is not helpful to you. If you really want to find out where the problem is you might try and start narrowing down on the issue. That is what I suggested to you.|||It just seemed to me that you asking me to make a test case that is exactly what I have running. I think I might have it working now and it must have something to do with my adding RTRIM() and CONVERT() functions in the select statement. I removed them and it seems to work better now.

Odd issue with bound columns when fetching data.

I'm using ODBC 3.0 in code written in C.

I have an service that connects using a system DSN using the SQL Server 2000 driver. On my development system with SQL Server 2005 Express installed, the queries work fine. I prepare a statement and then bind the columns that will be in the result set. On my system, I get all the data as it should be but on a live system, I do not get the proper data for the last three rows. I don't get any error messages from the query or the column binding and there are also no extra information messages that I can retrieve.

The table is something like the following. The names are changed to protect the guilty.

CREATE TABLE DOWNLOAD

(

FIELD_1 NUMERIC( 6,0 ),

ID CHAR( 15),

FNAME( 30 ),

LNAME( 40 ),

CLIENT_ID NUMERIC( 9,0),

CLIENT_NAME CHAR( 50 ),

CODE NUMERIC( 4,0 ),

PHONE CHAR( 10 )

)

go

On my system, running the exact same binary code as the client machine, I get all of the data from all of the columns as it should be. The problem is when I run on a client's system, the CLIENT_NAME, CODE, and PHONE columns return null values even when there is data there. On my development system the SQL Server instance runs on the same machine. On the client's sytem I am connecting to a remote instance of SQL Server 2000 on another system on the network.

My quandry is what could be different between the two systems that is causing me the problems?

Can you ran SQL Profiler on both machines and see if there is any difference?|||I've turned the logging on at the client side. The logs don't show any errors and it looks like the column binding in SQLBindCol is correct. I haven't seen the server side logs but the gurus on the server side say that they can't see anything wrong there. I've played around with the SQL statement and tried using CONVERT() to change the CHAR to VARCHAR but that doesn't seem to help.

I'm at a total loss why the same code running on my development system runs fine but fails on another system. My guess is that there is some configuration difference but I don't know what that is because I am using the samve version of the SQL Server 2000 ODBC driver on both machines. Its not like there is anything odd or non standard in the table definition either.

|||Just as an update and possibly more information.

I've got it working now but I don't like the solution. What I ended up doing is not binding the columns and calling SQLGetData() after the fetch to get the column data. I don't like this because it costs me about 500 ms for the each time I call the function.

I'd still like to find a solution for why the bound columns don't work.
|||1) I would still recommend you to run SQL Profiler on the servers.
2) How do you bind exactly? Is it possible for you to write (and post the source here) a small ODBC application which demonstrates this problem?|||This is the code I use to bind the columns:

BOOL BindRecord( SQLHSTMT hStmt, PACCDATA_FIELD *pRecord, PINT piRecCount )
{
BOOL bRval = FALSE;
PACCDATA_FIELD pField;
SQLRETURN r;
INT iCnt;
CHAR szBuf[128];
SQLINTEGER len;

if( pRecord && *pRecord )
{
for( iCnt = 0; iCnt < *piRecCount; iCnt++ )
{
pField = *pRecord + iCnt;
len = pField->nLen;

switch( pField->iType )
{
case SQL_CHAR:
r = SQLBindCol( hStmt, pField->iColNum, SQL_C_CHAR, pField->pcVal, pField->nDataSize, &len );
break;

case SQL_DATETIME:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->ucVal, pField->nDataSize, &len );
break;

case SQL_DECIMAL:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->ucVal, pField->nDataSize, &len );
break;

case SQL_NUMERIC:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->lVal, pField->nDataSize, &len );
break;

case SQL_INTEGER:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->lVal, pField->nDataSize, &len );
break;

case SQL_SMALLINT:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->sVal, pField->nDataSize, &len );
break;

case SQL_DOUBLE:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->dVal, pField->nDataSize, &len );
break;

case SQL_FLOAT:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->dVal, pField->nDataSize, &len );
break;

case SQL_REAL:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->fVal, pField->nDataSize, &len );
break;

case SQL_VARCHAR:
r = SQLBindCol( hStmt, pField->iColNum, SQL_C_CHAR, pField->pvcVal, pField->nDataSize + 1, &len );
break;

case SQL_UNKNOWN_TYPE:
ST_WriteLog( "st_ctimpact", "BindRecord", "Field data type is SQL_UNKNOWN_TYPE", ST_LOG_DEBUG );
default:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, pField->ptr, pField->nDataSize, &len );
break;
}

if( r == SQL_SUCCESS_WITH_INFO )
{
lstrcpy( szBuf, "Field name: " );
lstrcat( szBuf, pField->szColName );
ST_WriteLog( "st_AcceleratedData", "BindRecord", szBuf, ST_LOG_WARNING );
ShowSQLMessages( "BindRecord", SQL_HANDLE_STMT, hStmt );
bRval = TRUE;
}
else if( r != SQL_SUCCESS )
{
lstrcpy( szBuf, "Field name: " );
lstrcat( szBuf, pField->szColName );
ST_WriteLog( "st_AcceleratedData", "BindRecord", szBuf, ST_LOG_WARNING );
ShowSQLErrors( "BindRecord", SQL_HANDLE_STMT, hStmt );
}
else
bRval = TRUE;
}
}
return( bRval );
}

A key to remember is that other columns that are bound in this way, and have the same data types, are returning data just fine. I've looked at the logs on the client side where this code works, and there are no errors and the prameters look correct as far as I can tell. Also this code works as advertized on my system but not on the live system. Right now, I don't think my customer will be willing to let me mess around on their system trying to come up with an application that would recreate the problem. Also note that I cannot recreate the problem on my own system. I had hoped that it would be broken on my system so I could debug and find out what was the problem but because it works, I can't.
|||I do not think you have to mess around with your customer system.
I would suggest you write a straightforward ODBC application from scratch just going against this very table.
Use some static arrays and bind directly with SQLBindCol, so to exclude any dependecies on how PACCDATA_FIELD or other parts of your custom code are written.
Then when you are sure it works as supposed on your machine, try it against your customer's.|||Then I guess you didn't read the thread all that well. I don't have to write a test app. The live code works fine on my system on the same table definition in the same database name as the live system. The problem is when I deployed the code, some of the bound columns don't return data. Since I can't recreate the problem on my system, I'm at a loss as to what the problem is.
|||I'm not sure if it is possilbe to resolve your issue without actual experimentation. Your system is not identical to the live system, is it? So your argument that the code works on your system is not helpful to you. If you really want to find out where the problem is you might try and start narrowing down on the issue. That is what I suggested to you.|||It just seemed to me that you asking me to make a test case that is exactly what I have running. I think I might have it working now and it must have something to do with my adding RTRIM() and CONVERT() functions in the select statement. I removed them and it seems to work better now.

Odd behavior when calling a user function within an aggregation function

While debugging a code on the sql server 2000 debugger, I have encountered a
strange behavior while calling a user function from whitin the sum
aggregation function.
I have reproduced this behavior with the following code:
CREATE FUNCTION Test1 (@.prm decimal(18,2))
RETURNS decimal(18,2)
AS
BEGIN
return @.prm
END
---
CREATE PROCEDURE Test2
AS
declare @.var decimal(18,2)
set @.var=0
select @.var=sum(dbo.test1(Col1))
from Tbl1
print @.var
---
Col1 is a decimal(18,2) column on table Tbl1.
Now, when debuggin the sp 'Test2', it enters the function 'Test1' *twice*
for each row of Tbl1, but the sum result is ok.
For example if I had 2 rows on Tbl1 with values 1,2 on Col1, then i see it
enters twice to function 'Test2' with prm=1 and then twice with prm=2, but
the result will be 3 as it should be.
On my original code I am using a recursive function that is running on a
tree and not a simple one like in this example, so in my case it harms the
performance quite a lot (actually any node on the tree will be executed 2^h
when h is the height of the node).
I replaced the sum function with the variable that sums itself the values as
followed:
select @.var = @.var + dbo.test1(Col1)
But still i'm not sure if there's no a better idea to solve it.
What is the cause of this behavior? Is it only while debugging?
Any ideas and thoughts will be appreciated.
Thanks, apscalar function gets invoked once per row. this is by design, i'm afraid.
-oj
<ap> wrote in message news:%23blucZYRFHA.2736@.TK2MSFTNGP09.phx.gbl...
> While debugging a code on the sql server 2000 debugger, I have encountered
> a strange behavior while calling a user function from whitin the sum
> aggregation function.
> I have reproduced this behavior with the following code:
> CREATE FUNCTION Test1 (@.prm decimal(18,2))
> RETURNS decimal(18,2)
> AS
> BEGIN
> return @.prm
> END
> ---
> CREATE PROCEDURE Test2
> AS
> declare @.var decimal(18,2)
> set @.var=0
> select @.var=sum(dbo.test1(Col1))
> from Tbl1
> print @.var
> ---
> Col1 is a decimal(18,2) column on table Tbl1.
> Now, when debuggin the sp 'Test2', it enters the function 'Test1' *twice*
> for each row of Tbl1, but the sum result is ok.
> For example if I had 2 rows on Tbl1 with values 1,2 on Col1, then i see it
> enters twice to function 'Test2' with prm=1 and then twice with prm=2, but
> the result will be 3 as it should be.
> On my original code I am using a recursive function that is running on a
> tree and not a simple one like in this example, so in my case it harms the
> performance quite a lot (actually any node on the tree will be executed
> 2^h when h is the height of the node).
> I replaced the sum function with the variable that sums itself the values
> as followed:
> select @.var = @.var + dbo.test1(Col1)
> But still i'm not sure if there's no a better idea to solve it.
> What is the cause of this behavior? Is it only while debugging?
> Any ideas and thoughts will be appreciated.
> Thanks, ap
>
>|||>> What is the cause of this behavior?
In the topic
http://msdn.microsoft.com/library/e...des_08_460j.asp
they say:
"The number of times that a function specified in a query is actually
executed can vary between execution plans built by the optimizer."
This behaviour can also be revealed by using the "Show Server Trace"
option in Query Analyzer or using Profiler, so it seems that it does
not happen only while debugging.
You can use this statement, instead:
SELECT @.var=SUM(X) FROM (
select top 100 percent dbo.test1(Col1) X
from Tbl1 order by X
) A
It seems that this way the function is called only once for each row.
Razvan

Odd ASP buffer/cache problem?

I am working with code I did not write for a web app that used to run
on an Access database. Due to performance problems I'm trying to move
the app to a different server running SQL Server 2000 SP4, running on
Server 2003 with MDAC 2.8 SP2.
On the new server, one of the queries is failing in an odd way.
Roughly, the query is
select * from a, b where a.1 = b.1 and b.2 = x
This is old fashioned ASP code using ADO.Recordsets. One of the columns
I should be getting from the query is called "Number_Sections".
sql = the query
set cRS = dbCon.exectue( sql )
somevar = cRS("Number_Sections")
cRS("Number_Sections") is returning NULL even though the column in the
database is not null. If I run the query using the SQL Analyer on the
server itself, the column is indeed populated as I expect.
cRS.Fields("Number_Sections") and cRS.Fields.Item("Number_Sections")
also return NULL.
However, while trying to debug I threw in this code:
For zz = 0 to cRS.Fields.Count-1
response.write cRS.Fields.Item(zz).Name & " = " &
cRS.Fields.Item(zz).value & "<br>"
Next
After running through this loop, which prints out all 47 column names
and values *correctly*, cRS("Number_Sections") suddenly works. Take out
the loop and it stops working again.
This sounds like some kind of buffering problem to me, but I'm not
familiar enough with Microsoft products to know where I should be
looking for some setting to fix the problem. Is it ASP and IIS? Is it
SQL Server? I haven't been able to find any settings that look like
they address this issue and searching the MS Knowledge base hasn't
turned up anything helpful either.
Has anyone else seen this behavior? Can anyone point me in the right
direction to find a more graceful solution that leaving the for loop in
and setting the string to some dummy variable instead of sending it to
the response object?
Thanks.
-SeanStop using SELECT *. Do you really need 47 columns? If this column exists
in both a and b, then either alias it or only include it once. Another
thing you can try is somevar = cRS(n) where n is the 0-based ordinal
position of that column (which will be fun to figure out if you continue to
insist using SELECT *).
I have never seen this issue but without a better example, DDL/sample
data/code and a repro, it's tough to guess at what is happening.
<usenet@.dezynworks.com> wrote in message
news:1138651333.119837.167610@.f14g2000cwb.googlegroups.com...
>I am working with code I did not write for a web app that used to run
> on an Access database. Due to performance problems I'm trying to move
> the app to a different server running SQL Server 2000 SP4, running on
> Server 2003 with MDAC 2.8 SP2.
> On the new server, one of the queries is failing in an odd way.
> Roughly, the query is
> select * from a, b where a.1 = b.1 and b.2 = x
> This is old fashioned ASP code using ADO.Recordsets. One of the columns
> I should be getting from the query is called "Number_Sections".
> sql = the query
> set cRS = dbCon.exectue( sql )
> somevar = cRS("Number_Sections")
> cRS("Number_Sections") is returning NULL even though the column in the
> database is not null. If I run the query using the SQL Analyer on the
> server itself, the column is indeed populated as I expect.
> cRS.Fields("Number_Sections") and cRS.Fields.Item("Number_Sections")
> also return NULL.
> However, while trying to debug I threw in this code:
> For zz = 0 to cRS.Fields.Count-1
> response.write cRS.Fields.Item(zz).Name & " = " &
> cRS.Fields.Item(zz).value & "<br>"
> Next
> After running through this loop, which prints out all 47 column names
> and values *correctly*, cRS("Number_Sections") suddenly works. Take out
> the loop and it stops working again.
> This sounds like some kind of buffering problem to me, but I'm not
> familiar enough with Microsoft products to know where I should be
> looking for some setting to fix the problem. Is it ASP and IIS? Is it
> SQL Server? I haven't been able to find any settings that look like
> they address this issue and searching the MS Knowledge base hasn't
> turned up anything helpful either.
> Has anyone else seen this behavior? Can anyone point me in the right
> direction to find a more graceful solution that leaving the for loop in
> and setting the string to some dummy variable instead of sending it to
> the response object?
> Thanks.
> -Sean
>|||First, what you are describing sounds like an ASP coding issue, and not a
SQL Server issue. You would have better luck posting on an ASP newsgroup.
However, if you post more of your code, starting with where you define
dbCon(less the actual connection string of course), and ending with closing
your connection, folks will be able to offer more advice.
It sounds to me like you are not properly looping through your dataset, but
I can only guess without seeing the actual code.
<usenet@.dezynworks.com> wrote in message
news:1138651333.119837.167610@.f14g2000cwb.googlegroups.com...
> I am working with code I did not write for a web app that used to run
> on an Access database. Due to performance problems I'm trying to move
> the app to a different server running SQL Server 2000 SP4, running on
> Server 2003 with MDAC 2.8 SP2.
> On the new server, one of the queries is failing in an odd way.
> Roughly, the query is
> select * from a, b where a.1 = b.1 and b.2 = x
> This is old fashioned ASP code using ADO.Recordsets. One of the columns
> I should be getting from the query is called "Number_Sections".
> sql = the query
> set cRS = dbCon.exectue( sql )
> somevar = cRS("Number_Sections")
> cRS("Number_Sections") is returning NULL even though the column in the
> database is not null. If I run the query using the SQL Analyer on the
> server itself, the column is indeed populated as I expect.
> cRS.Fields("Number_Sections") and cRS.Fields.Item("Number_Sections")
> also return NULL.
> However, while trying to debug I threw in this code:
> For zz = 0 to cRS.Fields.Count-1
> response.write cRS.Fields.Item(zz).Name & " = " &
> cRS.Fields.Item(zz).value & "<br>"
> Next
> After running through this loop, which prints out all 47 column names
> and values *correctly*, cRS("Number_Sections") suddenly works. Take out
> the loop and it stops working again.
> This sounds like some kind of buffering problem to me, but I'm not
> familiar enough with Microsoft products to know where I should be
> looking for some setting to fix the problem. Is it ASP and IIS? Is it
> SQL Server? I haven't been able to find any settings that look like
> they address this issue and searching the MS Knowledge base hasn't
> turned up anything helpful either.
> Has anyone else seen this behavior? Can anyone point me in the right
> direction to find a more graceful solution that leaving the for loop in
> and setting the string to some dummy variable instead of sending it to
> the response object?
> Thanks.
> -Sean
>

ODBC/OLE DB Error Code 0

Hey All:
Getting Code 0 errors in ActiveX scripts within DTS & can't trap the decription(s).
I remember seeing an OLD Thread on expanding the Code 0 errors but can't find it.
RobbieDcopy the contents of your dts into a notepad, save it as vbs file, after putting msgbox in places where you know or think errors occur, and go get them, tiger! happy debugging :)|||I did something similar. Copied the code into InterDev & got what I was looking for.

Also, realized that the procedure was flawed (BAD Code, BAD!) in that I was attempting to capture the error from one ActiveX module in a SECOND on Failure. Obviously, the err.Description would no longer exist!!!

ODBC, VB, SQL Authentication

How do I connect to SQL Server using VB6? I will be setting up the ODBC for
SQL using SQL Authentication. Is there a way that I do not hard code the
username and password in the VB program? I wouldn't like to have login
screen to ask for the user's credentials. Thanks
Hi NS
You can very well prompt users to key in user name and pwd instead of
hardcoding. Infact it is the best technique to do that.
do not provide username and pwd in the connection string, try to pass
parameters that were entered in the popped up text box.
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
"NS" wrote:

> How do I connect to SQL Server using VB6? I will be setting up the ODBC for
> SQL using SQL Authentication. Is there a way that I do not hard code the
> username and password in the VB program? I wouldn't like to have login
> screen to ask for the user's credentials. Thanks
|||NS,
I would recommend using Windows Authentication instead of SQL Server
Authentication if that is at all possible - more secure.
HTH
Jerry
"NS" <NS@.discussions.microsoft.com> wrote in message
news:076393DE-4439-4169-87E5-595A545226DC@.microsoft.com...
> How do I connect to SQL Server using VB6? I will be setting up the ODBC
> for
> SQL using SQL Authentication. Is there a way that I do not hard code the
> username and password in the VB program? I wouldn't like to have login
> screen to ask for the user's credentials. Thanks
|||I was thinking after setting up the DSN for SQL with SQL Authentication, you
could simply point to the dsn in your visual basic code when connecting to
the database (ADODB.Connection...). I thought one need not provide the user
and password again. So, that isn't true?
"Chandra" wrote:
[vbcol=seagreen]
> Hi NS
> You can very well prompt users to key in user name and pwd instead of
> hardcoding. Infact it is the best technique to do that.
> do not provide username and pwd in the connection string, try to pass
> parameters that were entered in the popped up text box.
> please let me know if u have any questions
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
>
> "NS" wrote:
|||You can also can force the prompt in code - it's a property
that's exposed for the connection object in ADO.
YourConnection.Properties("Prompt") = adPromptAlways
YourConnection.Open ...etc just specifying driver, server
and database.
-Sue
On Tue, 25 Oct 2005 07:37:12 -0700, "NS"
<NS@.discussions.microsoft.com> wrote:

>How do I connect to SQL Server using VB6? I will be setting up the ODBC for
>SQL using SQL Authentication. Is there a way that I do not hard code the
>username and password in the VB program? I wouldn't like to have login
>screen to ask for the user's credentials. Thanks
|||"NS" <NS@.discussions.microsoft.com> wrote in message
news:1E3777C8-3B00-4A83-8C63-7DBD9D3CE30A@.microsoft.com...
> I was thinking after setting up the DSN for SQL with SQL Authentication,
you
> could simply point to the dsn in your visual basic code when connecting to
> the database (ADODB.Connection...). I thought one need not provide the
user
> and password again. So, that isn't true?
Yes, it is true. You need to declare the following in your VB app:
Private Declare Function SQLReadFileDSN Lib "ODBCCP32.DLL" _
(ByVal lpszFileName As String, _
ByVal lpszAppName As String, _
ByVal lpszKeyName As String, _
ByVal lpszString As String, _
ByVal cbString As Long, _
ByRef pcbString As Long) As Long
Lookup the function SQLReadFileDSN in MSDN for examples of how to use it and
what the parameters mean.
[vbcol=seagreen]
> "Chandra" wrote:
ODBC for[vbcol=seagreen]
the[vbcol=seagreen]
login[vbcol=seagreen]

ODBC, VB, SQL Authentication

How do I connect to SQL Server using VB6? I will be setting up the ODBC for
SQL using SQL Authentication. Is there a way that I do not hard code the
username and password in the VB program? I wouldn't like to have login
screen to ask for the user's credentials. ThanksHi NS
You can very well prompt users to key in user name and pwd instead of
hardcoding. Infact it is the best technique to do that.
do not provide username and pwd in the connection string, try to pass
parameters that were entered in the popped up text box.
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"NS" wrote:

> How do I connect to SQL Server using VB6? I will be setting up the ODBC f
or
> SQL using SQL Authentication. Is there a way that I do not hard code the
> username and password in the VB program? I wouldn't like to have login
> screen to ask for the user's credentials. Thanks|||NS,
I would recommend using Windows Authentication instead of SQL Server
Authentication if that is at all possible - more secure.
HTH
Jerry
"NS" <NS@.discussions.microsoft.com> wrote in message
news:076393DE-4439-4169-87E5-595A545226DC@.microsoft.com...
> How do I connect to SQL Server using VB6? I will be setting up the ODBC
> for
> SQL using SQL Authentication. Is there a way that I do not hard code the
> username and password in the VB program? I wouldn't like to have login
> screen to ask for the user's credentials. Thanks|||I was thinking after setting up the DSN for SQL with SQL Authentication, you
could simply point to the dsn in your visual basic code when connecting to
the database (ADODB.Connection...). I thought one need not provide the user
and password again. So, that isn't true?
"Chandra" wrote:
[vbcol=seagreen]
> Hi NS
> You can very well prompt users to key in user name and pwd instead of
> hardcoding. Infact it is the best technique to do that.
> do not provide username and pwd in the connection string, try to pass
> parameters that were entered in the popped up text box.
> please let me know if u have any questions
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "NS" wrote:
>|||You can also can force the prompt in code - it's a property
that's exposed for the connection object in ADO.
YourConnection.Properties("Prompt") = adPromptAlways
YourConnection.Open ...etc just specifying driver, server
and database.
-Sue
On Tue, 25 Oct 2005 07:37:12 -0700, "NS"
<NS@.discussions.microsoft.com> wrote:

>How do I connect to SQL Server using VB6? I will be setting up the ODBC fo
r
>SQL using SQL Authentication. Is there a way that I do not hard code the
>username and password in the VB program? I wouldn't like to have login
>screen to ask for the user's credentials. Thanks|||"NS" <NS@.discussions.microsoft.com> wrote in message
news:1E3777C8-3B00-4A83-8C63-7DBD9D3CE30A@.microsoft.com...
> I was thinking after setting up the DSN for SQL with SQL Authentication,
you
> could simply point to the dsn in your visual basic code when connecting to
> the database (ADODB.Connection...). I thought one need not provide the
user
> and password again. So, that isn't true?
Yes, it is true. You need to declare the following in your VB app:
Private Declare Function SQLReadFileDSN Lib "ODBCCP32.DLL" _
(ByVal lpszFileName As String, _
ByVal lpszAppName As String, _
ByVal lpszKeyName As String, _
ByVal lpszString As String, _
ByVal cbString As Long, _
ByRef pcbString As Long) As Long
Lookup the function SQLReadFileDSN in MSDN for examples of how to use it and
what the parameters mean.
[vbcol=seagreen]
> "Chandra" wrote:
>
ODBC for[vbcol=seagreen]
the[vbcol=seagreen]
login[vbcol=seagreen]

ODBC, VB, SQL Authentication

How do I connect to SQL Server using VB6? I will be setting up the ODBC for
SQL using SQL Authentication. Is there a way that I do not hard code the
username and password in the VB program? I wouldn't like to have login
screen to ask for the user's credentials. ThanksHi NS
You can very well prompt users to key in user name and pwd instead of
hardcoding. Infact it is the best technique to do that.
do not provide username and pwd in the connection string, try to pass
parameters that were entered in the popped up text box.
please let me know if u have any questions
--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"NS" wrote:
> How do I connect to SQL Server using VB6? I will be setting up the ODBC for
> SQL using SQL Authentication. Is there a way that I do not hard code the
> username and password in the VB program? I wouldn't like to have login
> screen to ask for the user's credentials. Thanks|||NS,
I would recommend using Windows Authentication instead of SQL Server
Authentication if that is at all possible - more secure.
HTH
Jerry
"NS" <NS@.discussions.microsoft.com> wrote in message
news:076393DE-4439-4169-87E5-595A545226DC@.microsoft.com...
> How do I connect to SQL Server using VB6? I will be setting up the ODBC
> for
> SQL using SQL Authentication. Is there a way that I do not hard code the
> username and password in the VB program? I wouldn't like to have login
> screen to ask for the user's credentials. Thanks|||I was thinking after setting up the DSN for SQL with SQL Authentication, you
could simply point to the dsn in your visual basic code when connecting to
the database (ADODB.Connection...). I thought one need not provide the user
and password again. So, that isn't true?
"Chandra" wrote:
> Hi NS
> You can very well prompt users to key in user name and pwd instead of
> hardcoding. Infact it is the best technique to do that.
> do not provide username and pwd in the connection string, try to pass
> parameters that were entered in the popped up text box.
> please let me know if u have any questions
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "NS" wrote:
> > How do I connect to SQL Server using VB6? I will be setting up the ODBC for
> > SQL using SQL Authentication. Is there a way that I do not hard code the
> > username and password in the VB program? I wouldn't like to have login
> > screen to ask for the user's credentials. Thanks|||You can also can force the prompt in code - it's a property
that's exposed for the connection object in ADO.
YourConnection.Properties("Prompt") = adPromptAlways
YourConnection.Open ...etc just specifying driver, server
and database.
-Sue
On Tue, 25 Oct 2005 07:37:12 -0700, "NS"
<NS@.discussions.microsoft.com> wrote:
>How do I connect to SQL Server using VB6? I will be setting up the ODBC for
>SQL using SQL Authentication. Is there a way that I do not hard code the
>username and password in the VB program? I wouldn't like to have login
>screen to ask for the user's credentials. Thanks|||"NS" <NS@.discussions.microsoft.com> wrote in message
news:1E3777C8-3B00-4A83-8C63-7DBD9D3CE30A@.microsoft.com...
> I was thinking after setting up the DSN for SQL with SQL Authentication,
you
> could simply point to the dsn in your visual basic code when connecting to
> the database (ADODB.Connection...). I thought one need not provide the
user
> and password again. So, that isn't true?
Yes, it is true. You need to declare the following in your VB app:
Private Declare Function SQLReadFileDSN Lib "ODBCCP32.DLL" _
(ByVal lpszFileName As String, _
ByVal lpszAppName As String, _
ByVal lpszKeyName As String, _
ByVal lpszString As String, _
ByVal cbString As Long, _
ByRef pcbString As Long) As Long
Lookup the function SQLReadFileDSN in MSDN for examples of how to use it and
what the parameters mean.
> "Chandra" wrote:
> > Hi NS
> >
> > You can very well prompt users to key in user name and pwd instead of
> > hardcoding. Infact it is the best technique to do that.
> >
> > do not provide username and pwd in the connection string, try to pass
> > parameters that were entered in the popped up text box.
> >
> > please let me know if u have any questions
> >
> > --
> > best Regards,
> > Chandra
> > http://chanduas.blogspot.com/
> > http://www.SQLResource.com/
> > ---
> >
> >
> >
> > "NS" wrote:
> >
> > > How do I connect to SQL Server using VB6? I will be setting up the
ODBC for
> > > SQL using SQL Authentication. Is there a way that I do not hard code
the
> > > username and password in the VB program? I wouldn't like to have
login
> > > screen to ask for the user's credentials. Thanks

ODBC SQLFetch problem

Hi,

I have ODBC code reading data from a XLS file.

It opens the file, reads the sheets and columns available, then presents a dialog to allow the user to select which columns are to be used to load the data.

All of this works, almost.

It is a simple "Select A,B,C FROM Sheet1" using SQLExecDirect

When using SQLFetch it always skips the first row of data.

Thanks, any ideas would be great.

Keith

Keith,

If you are using Fast Forward Only AutoFetch cursors, then as an optimization the driver might fill the SQL_ATTR_ROW_ARRAY_SIZE number of rows to your application buffers as part of SQLExecDirect execution. I do know that this is indeed the case with SQL Native Client (SQLNCLI) ODBC driver, not sure about the excel driver though. Can you check to see if that is the case in your scenario. Hope this helps.

Thanks

Waseem

|||

Thanks for the response,

I have played with different row sizes does not change the results.

I have also tried different files with different row counts, It always miss only the first row.

I have also added blank rows above the first and it still misses the first row of data.

Keith

|||

Does SQLExecDirect fetch the 1st row for you? Can you check the bind buffers to see they are filled with column data from the 1st row after SQLExecDirect before calling SQLFetch?

One more thing you might try is to move SQLBindCol after SQLExecDirect to see if that changes anything.

|||

Hi,

SQLExecDirect does not change the bind buffers.

Have moved the SQLBindCol calls before and and after the SQLExecDirect call both have the same effect. I also used SQLGetData instead of SQLBindCol and they also always miss the first row of data.

Keith

|||

Turns out that the Excel driver expects the first row to be the column names. There is supposed to be an option to turn this behavior off, but it has not worked in the past. I am not sure if it has been fixed. Here is the link to more information. http://support.microsoft.com/kb/288343/

If you have a row with column names in it, you can just include that in your range and you will be all set, otherwise, you should try the option, if that doesn't work, use the Jet engine as suggested in the above link.

Thanks

|||Thank you, adding headers does make it read all the lines. I had tried blank rows that has no effect. This is a reader for a user supplied file I have no control over.

ODBC SQLFetch problem

Hi,

I have ODBC code reading data from a XLS file.

It opens the file, reads the sheets and columns available, then presents a dialog to allow the user to select which columns are to be used to load the data.

All of this works, almost.

It is a simple "Select A,B,C FROM Sheet1" using SQLExecDirect

When using SQLFetch it always skips the first row of data.

Thanks, any ideas would be great.

Keith

Keith,

If you are using Fast Forward Only AutoFetch cursors, then as an optimization the driver might fill the SQL_ATTR_ROW_ARRAY_SIZE number of rows to your application buffers as part of SQLExecDirect execution. I do know that this is indeed the case with SQL Native Client (SQLNCLI) ODBC driver, not sure about the excel driver though. Can you check to see if that is the case in your scenario. Hope this helps.

Thanks

Waseem

|||

Thanks for the response,

I have played with different row sizes does not change the results.

I have also tried different files with different row counts, It always miss only the first row.

I have also added blank rows above the first and it still misses the first row of data.

Keith

|||

Does SQLExecDirect fetch the 1st row for you? Can you check the bind buffers to see they are filled with column data from the 1st row after SQLExecDirect before calling SQLFetch?

One more thing you might try is to move SQLBindCol after SQLExecDirect to see if that changes anything.

|||

Hi,

SQLExecDirect does not change the bind buffers.

Have moved the SQLBindCol calls before and and after the SQLExecDirect call both have the same effect. I also used SQLGetData instead of SQLBindCol and they also always miss the first row of data.

Keith

|||

Turns out that the Excel driver expects the first row to be the column names. There is supposed to be an option to turn this behavior off, but it has not worked in the past. I am not sure if it has been fixed. Here is the link to more information. http://support.microsoft.com/kb/288343/

If you have a row with column names in it, you can just include that in your range and you will be all set, otherwise, you should try the option, if that doesn't work, use the Jet engine as suggested in the above link.

Thanks

|||Thank you, adding headers does make it read all the lines. I had tried blank rows that has no effect. This is a reader for a user supplied file I have no control over.

ODBC SQLBindColumn to a T-SQL variable not working

Hello,

I am trying to get the count of records in a file in a single round trip to the server. I tried T-SQL code similar to below and used SQLBindColumn to bind to argument #1. The code executes without error, but the result is never returned.

Any ideas what I am doing wrong?

Thanks!
CB

DECLARE @.i AS INTEGER
SELECT @.i COUNT (*) FROM A_4000.dbo.ADRE_ADU WITH (NOLOCK)

This code generates an error for me, but that is because you are not actually assigning the value of the count to the variable. Try this:

Code Snippet

DECLARE @.i AS INTEGER

SELECT @.i = COUNT (*) FROM A_4000.dbo.ADRE_ADU WITH (NOLOCK)

|||Sorry that was a typo. I do have the equals sign. When it does run, the C variable that I bind to the SQL variable i with SQLBindColumn() doesn't contain the value after SQLExecute().

ODBC SQLBindColumn to a T-SQL variable not working

Hello,

I am trying to get the count of records in a file in a single round trip to the server. I tried T-SQL code similar to below and used SQLBindColumn to bind to argument #1. The code executes without error, but the result is never returned.

Any ideas what I am doing wrong?

Thanks!
CB

DECLARE @.i AS INTEGER
SELECT @.i COUNT (*) FROM A_4000.dbo.ADRE_ADU WITH (NOLOCK)

This code generates an error for me, but that is because you are not actually assigning the value of the count to the variable. Try this:

Code Snippet

DECLARE @.i AS INTEGER

SELECT @.i = COUNT (*) FROM A_4000.dbo.ADRE_ADU WITH (NOLOCK)

|||Sorry that was a typo. I do have the equals sign. When it does run, the C variable that I bind to the SQL variable i with SQLBindColumn() doesn't contain the value after SQLExecute().

ODBC SQL Server: Invalid attribute/option identifier

Hi,
I am using MFC class with ODBC to connect to SQL Server. Below is my
partial code, when I do update(), I get an error "Invalid attribute/option
identifier".
---
.Open(CRecordset::dynaset,"SELECT * FROM tblcomponentsInStream WHERE
FLUIDSTREAMSID = '233' ",CRecordset::none);
.Edit();
.Update(); //I get below error
---
"Invalid attribute/option identifier"
{" State:S1092,Native:0,Origin:[Microsoft][
ODBC SQL Server Driver]
"}
Does any one have any clue about this error.
Thanks in Advance,
BhavinHi
It is not a good idea to use SELECT * in production code. You may want to
see if there is a Primary key on tblcomponentsInStream and check the
canUpdate member function to make sure that it is updatable. As there has
not been any change to the record so you would not want to update the record
at that point.
John
"Bhavin Patel" <bpatel@.epcon.com> wrote in message
news:OLVtBuXuFHA.360@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am using MFC class with ODBC to connect to SQL Server. Below is my
> partial code, when I do update(), I get an error "Invalid attribute/option
> identifier".
> ---
> .Open(CRecordset::dynaset,"SELECT * FROM tblcomponentsInStream WHERE
> FLUIDSTREAMSID = '233' ",CRecordset::none);
> .Edit();
> .Update(); //I get below error
> ---
> "Invalid attribute/option identifier"
> {" State:S1092,Native:0,Origin:[Microsoft][
ODBC SQL Server Driver]
> "}
>
> Does any one have any clue about this error.
> Thanks in Advance,
> Bhavin
>|||John:
There is a primary key in that table an it is FLUIDSTREAMSID. Also, I
have to SELECT * since I need all the fields. The other things, this code
already works greate with ORACLE, but I am trying to move to SQL server, and
it is giving me this kind of surprises.
Thank you in Advance
Bhavin
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:eicVhhiuFHA.3104@.TK2MSFTNGP10.phx.gbl...
> Hi
> It is not a good idea to use SELECT * in production code. You may want to
> see if there is a Primary key on tblcomponentsInStream and check the
> canUpdate member function to make sure that it is updatable. As there has
> not been any change to the record so you would not want to update the
> record at that point.
> John
> "Bhavin Patel" <bpatel@.epcon.com> wrote in message
> news:OLVtBuXuFHA.360@.TK2MSFTNGP12.phx.gbl...
>|||Hi
This is more to do with the ODBC driver than SQL Server itself, you may want
to check what version of MDAC you are running and that it is consistent
http://www.microsoft.com/downloads/...&displaylang=en
by using the MDAC component checker.
Even if you are returning all columns you should avoid SELECT * as there are
circumstances where you will have problems if the column order changes.
John
"Bhavin Patel" <bpatel@.epcon.com> wrote in message
news:ePrJ6ytuFHA.728@.TK2MSFTNGP10.phx.gbl...
> John:
> There is a primary key in that table an it is FLUIDSTREAMSID. Also, I
> have to SELECT * since I need all the fields. The other things, this code
> already works greate with ORACLE, but I am trying to move to SQL server,
> and it is giving me this kind of surprises.
> Thank you in Advance
> Bhavin
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:eicVhhiuFHA.3104@.TK2MSFTNGP10.phx.gbl...
>|||Hi
I am using
* MDAC 2.8 SP1 on Windows XP SP2
* ODBC Driver Manager (ODBC.dll) Version 3.525.1117.0
I think I am using very latest MDAC but I am not sure about my ODBC. If it
is old How can I upgrade it to latest version.
On more thing, I ran ODBC trace and I don't understand why my cursor type is
being changed.
--trace file--
PRV d50-6ec ENTER SQLExecute
HSTMT 0185EC08
PRV d50-6ec EXIT SQLExecute with return code 1
(SQL_SUCCESS_WITH_INFO)
HSTMT 0185EC08
DIAG [01S02] [Microsoft][ODBC SQL Server Driver]Cursor type changed (0)
DIAG [01S02] [Microsoft][ODBC SQL Server Driver]Cursor concurrency changed
(0)
--Trace file--
PRV d50-6ec EXIT SQLSetPos with return code -1 (SQL_ERROR)
HSTMT 0185EC08
SQLSETPOSIROW SQLSETPOSIROW UWORD 2
<SQL_UPDATE>
BOOL 0 <SQL_LOCK_NO_CHANGE>
DIAG [S1092] [Microsoft][ODBC SQL Server Driver]Invalid attribute/option
identifier (0)
PRV d50-6ec ENTER SQLErrorW
HENV 01851A10
HDBC 01852F28
HSTMT 0185EC08
WCHAR * 0x0012D9AC (NYI)
SDWORD * 0x0012DC64
WCHAR * 0x0012D5AC
SWORD 511
SWORD * 0x0012DC60
PRV d50-6ec EXIT SQLErrorW with return code 0 (SQL_SUCCESS)
HENV 01851A10
HDBC 01852F28
HSTMT 0185EC08
WCHAR * 0x0012D9AC (NYI)
SDWORD * 0x0012DC64 (0)
WCHAR * 0x0012D5AC [ 70] "[Microsoft][ODBC SQL Server
Driver]Invalid attribute/option identifier"
SWORD 511
SWORD * 0x0012DC60 (70)
Thank you,
Bhavin
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:ebe0tBvuFHA.3048@.TK2MSFTNGP10.phx.gbl...
> Hi
> This is more to do with the ODBC driver than SQL Server itself, you may
> want to check what version of MDAC you are running and that it is
> consistent
> http://www.microsoft.com/downloads/...&displaylang=en
> by using the MDAC component checker.
> Even if you are returning all columns you should avoid SELECT * as there
> are circumstances where you will have problems if the column order
> changes.
> John
> "Bhavin Patel" <bpatel@.epcon.com> wrote in message
> news:ePrJ6ytuFHA.728@.TK2MSFTNGP10.phx.gbl...
>|||Hi
This is probably all within the OPEN method if you used the default opentype
of Recordset::snapshot it would probably not appear.
From:
http://msdn.microsoft.com/library/d...pplications.asp
For SQLSetpos If the Operation argument (in your case SQL_UPDATE) is not
SQL_ADD, the driver returns SQLSTATE HY092 (Invalid attribute/option
identifier).
Why this is occuring I am not sure, you may want to try different Opentypes,
removing the where clause (and setting up m_strFilter!) or just specifying
the tablename.
You have not said what CanUpdate is!
It may be worth posting the DDL for your table.
John
"Bhavin Patel" <bpatel@.epcon.com> wrote in message
news:elH76d0uFHA.3400@.TK2MSFTNGP14.phx.gbl...
> Hi
> I am using
> * MDAC 2.8 SP1 on Windows XP SP2
> * ODBC Driver Manager (ODBC.dll) Version 3.525.1117.0
> I think I am using very latest MDAC but I am not sure about my ODBC. If
> it is old How can I upgrade it to latest version.
> On more thing, I ran ODBC trace and I don't understand why my cursor type
> is being changed.
> --trace file--
> PRV d50-6ec ENTER SQLExecute
> HSTMT 0185EC08
> PRV d50-6ec EXIT SQLExecute with return code 1
> (SQL_SUCCESS_WITH_INFO)
> HSTMT 0185EC08
> DIAG [01S02] [Microsoft][ODBC SQL Server Driver]Cursor type changed (0)
> DIAG [01S02] [Microsoft][ODBC SQL Server Driver]Cursor concurrency
> changed (0)
> --Trace file--
> PRV d50-6ec EXIT SQLSetPos with return code -1 (SQL_ERROR)
> HSTMT 0185EC08
> SQLSETPOSIROW SQLSETPOSIROW UWORD 2
> <SQL_UPDATE>
> BOOL 0 <SQL_LOCK_NO_CHANGE>
> DIAG [S1092] [Microsoft][ODBC SQL Server Driver]Invalid attribute/option
> identifier (0)
> PRV d50-6ec ENTER SQLErrorW
> HENV 01851A10
> HDBC 01852F28
> HSTMT 0185EC08
> WCHAR * 0x0012D9AC (NYI)
> SDWORD * 0x0012DC64
> WCHAR * 0x0012D5AC
> SWORD 511
> SWORD * 0x0012DC60
> PRV d50-6ec EXIT SQLErrorW with return code 0 (SQL_SUCCESS)
> HENV 01851A10
> HDBC 01852F28
> HSTMT 0185EC08
> WCHAR * 0x0012D9AC (NYI)
> SDWORD * 0x0012DC64 (0)
> WCHAR * 0x0012D5AC [ 70] "[Microsoft][ODBC SQL Server
> Driver]Invalid attribute/option identifier"
> SWORD 511
> SWORD * 0x0012DC60 (70)
>
> Thank you,
> Bhavin
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:ebe0tBvuFHA.3048@.TK2MSFTNGP10.phx.gbl...
>