Member Avatar for fx.eko

how to create a report with data from duatabase on the same server or different servers, using SQL Server 2000, using the crystal report 8.5 or **data report of VB6 **

For example I have table A and table B with the same table structure, table A is stored in the database with the name DataB1 and stored in a database table B DataB2

Table A
code name

Jon X1
Jim X2

Table B
code name

Yana Y1
Y2 yono

I want to create a report showing the contents of both tables A and B
Dapa contained two different databases, which ultimately results

Combined table A and table B into
code name

Jon X1
Jim X2
Yana Y1

Y2 yono

Best regards,

  • 2 Contributors
  • 3 Replies
  • 309 Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by fx.eko

Dani AI

Generated

Short answer: your server-side UNION is fine (as pointed out) but the VB6 Data Environment sometimes cannot retrieve design‑time column metadata when the SQL references remote servers, linked servers or nontrivial resultsets — so the fields do not appear in the Data Environment. This is a common DataEnvironment/ADO design‑time limitation. (stackoverflow.com)

A reliable, low‑risk fix is to put the union work on the SQL Server side and expose a single, simple resultset that VB6 can introspect. Create a linked server on one SQL instance, build a view (or a single SELECT object) that returns the combined rows with consistent column names and types, test that view in Query Analyzer, then point the VB6 Data Environment at SELECT * FROM YourView. That keeps all distributed‑query complexity on SQL Server and makes the schema visible to the designer. See Microsoft guidance on linked servers and using server‑side views. (learn.microsoft.com)

If you cannot change the server, bind the report at runtime instead of relying on the designer. Open an ADODB connection, run the combined query, and set the DataReport to use the returned recordset. Example pattern:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim rpt As New rptCombined

Set cn = New ADODB.Connection
cn.Open "<your connection string>"

Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM dbo.vCombinedView", cn, adOpenForwardOnly, adLockReadOnly

Set rpt.DataSource = rs
rpt.DataMember = ""
rpt.Show

rs.Close
cn.Close

This runtime binding approach is commonly used when the designer can not obtain metadata. (daniweb.com)

Quick troubleshooting checklist: make column names and datatypes identical across the two selects; test the combined SELECT in SQL tools first; ensure linked‑server login mappings and RPC/out are configured; avoid putting ORDER BY inside unioned subqueries; if you try a stored procedure and the designer still fails to learn columns, use a simple server view or one of the FMTONLY/top‑0 metadata tricks carefully (these have gotchas). (vb-net.com)

If needed, add what you did when you tested the query in Query Analyzer and which server you connect to from VB6 — that will help pinpoint whether the issue is metadata discovery, permissions, or connection configuration.

Recommended Answers

All 3 Replies

Member Avatar for rch1231

Hello,

What you need to do is use UNION to merge the output from the two databases and if needed you can sort the out put into one large table intermixed:

Select * 
from 
(select table1.data_field as result1
from table1
UNION
select table2.data_field as result1
from table2) as data1
order by data1.result1

Or something roughly like that depending on the database you are using. UNION puts the results from one query together with the results from a second query provided they have the same names for the result data fields.

Member Avatar for fx.eko

@ rch1231, thank you very much for your answer and it can work well.

Member Avatar for fx.eko

@ rch1231, I have a query as follows:
select a.A, a.B from server1.dbo.table1 a
UNION
select b.A, b.B from server2.dbo.table1 b

but if the query is why I enter in the data environment on the current VB6 will create a data report, the fields that should appear in the data environment did not appear.
Can you help me @ rch1231

best regards,

Edited by fx.eko because: wrong in write
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.