Field Definition report results no record

Hi guys, sorry my poor english,
I'm trying to create a simple example of a report that receive the recordset from my VB6 application. The recordset have only one field like the rpt and the ttx file.

My project use these references:
Microsoft Remote Data Object 2.0
Crystal Reports ActiveX Designer Run Time Library 10.0
Crystal Report Viewer Control

To reproduce the example in VB, create a new project, include the references, create a command button and a CRViewer and after paste the code inside the form.

--- Code ---
Option Explicit
Dim cnFin As RDO.rdoConnection
Dim Crystal As CRAXDRT.Application

Private Sub Command1_Click()
Dim rsFin As RDO.rdoResultset
Dim rpFin As Report

'Opening table
Set rsFin = cnFin.OpenResultset( _
"SELECT ALL FinCPSequencia " & _
"FROM FinMovtoPagar " & _
"WHERE CodEstab = 5", RDO.rdOpenKeyset, RDO.rdConcurRowVer)
If rsFin.RowCount > 0 Then
'Opens report
Set rpFin = Crystal.OpenReport(App.Path & "\Test.rpt")
'Defines parameters
rpFin.ParameterFields.Item(1).AddCurrentValue "Title"
'Set DataSoruce
rpFin.Database.SetDataSource rsFin
'Shows report
With CRViewer1
.ReportSource = rpFin
.ViewReport
End With
Else
MsgBox "No record"
End If
End Sub
Private Sub Form_Load()
'Connecting with database
Set cnFin = RDO.rdoEnvironments(0).OpenConnection("", , , _
"DRIVER=SQL Server;SERVER=127.0.0.1;UID=BNP;PWD=password;APP=BNPGest;WSID=NBNIETTO;DATABASE=BNPGEST_DESENV;LANGUAGE=us_english;Network=DBMSSOCN;Address=127.0.0.1,1433")
'Initiating Crystal
Set Crystal = New CRAXDRT.Application
End SubThe same example using ADO works fine, but I have to keep the project with RDO.
I found this article: http://support.businessobjects.com/library/kbase/articles/c2016624.asp?ref=devzone_xiresources_tipsandtricks , But the report yet results no records even using the batchclient. Does anyone can help me?

fetch successful resultset after exception

Ok, here is the deal. In T-SQL, an error is handled depending on its severity. For example, a severity of 15 or less (or is it 10 or less? doesn't matter) will only raise a warning that can be caught through the message event. 16 or higher (or whatever) will cause an exception to be thrown on the .NET side, and 20 or higher causes the connection to be closed.

So far so good.

Now here's my issue: I have a stored procedure that does 2 queries (inside the same SP). Sometimes, the first query will succeed, while the second one will cause an error of severity 16. Again, in .NET that will throw an exception, making me unable to fetch the first resultset (which I require for logging purpose). If the error was, let say, severity 9, I could simply subscribe to the message event to get my error, yet still be able to get the first result set. But unfortunately life isn't perfect, and the error is indeed a severity 16.

Anyone know of a way to be able to get the first result set, even if the second query (within the same SP) completly fails?Why don't you wrap the two SQL statements in a transaction, so that if one fails, all attempted changes will be rolled back. You certainly wouldn't want something to work half-way--it should be an all or nothing deal. If you don't mind it working half-way, then perhaps the SQL should be put into separate calls.|||Well, I answered my own question: nothing is stopping me from accessing the data. If using a datareader, the exception isn't thrown until you move to the resultset that errored out. If using a dataset with multiple tables, the tables that succeeded are filled just fine as normal, and the ones that failed have 0 rows. Not the behavior I expected, but it did the trick.

As to why a non-atomic process is wrapped in a single stored procedure? Its because the SP was made for a relatively limited (in features) scheduling software, and it can only call one SP on the trigger. A bit silly, but I'm not the one who set that up originally, I'm just handling a migration. The requirement here was so that we could log what succeeded so we can give the log away to a supplier or some such.

Anyway, seems like ADO.NET handles that requirement just fine after all. I'm surprised, honestly.

faster?

Hi
what is faster - LEFT JOIN or subselect
example
SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
or
SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
Mex"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:ul%23QIbyWHHA.1396@.TK2MSFTNGP05.phx.gbl...
> Hi
> what is faster - LEFT JOIN or subselect
>
> example
> SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
> or
> SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
>
> Mex
>
Best bet, run this in query analyzer and look at the execution plans. The
optimizer may end up doing the same thing in either case. If not, then you
can see which is faster and why.
--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Meelis
This is invalid in terms of syntax statement. Post the valid statements and
regarding to the question LEFT JOIN has nothing to do with SUBSELECT
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:ul%23QIbyWHHA.1396@.TK2MSFTNGP05.phx.gbl...
> Hi
> what is faster - LEFT JOIN or subselect
>
> example
> SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
> or
> SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
>
> Mex
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uxnf0zyWHHA.4668@.TK2MSFTNGP04.phx.gbl...
> Meelis
> This is invalid in terms of syntax statement. Post the valid statements
> and regarding to the question LEFT JOIN has nothing to do with SUBSELECT
Hmm, good point I didn't even actually look at what he was trying to do
there. :-)
Well I'm pretty sure the syntax error will "return" faster than the left
join, so I guess the subselect is technically faster. :-)
>
>
> "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
> news:ul%23QIbyWHHA.1396@.TK2MSFTNGP05.phx.gbl...
>> Hi
>> what is faster - LEFT JOIN or subselect
>>
>> example
>> SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
>> or
>> SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
>>
>> Mex
>
--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
or
SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
FROM TITLES T
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uxnf0zyWHHA.4668@.TK2MSFTNGP04.phx.gbl...
> Meelis
> This is invalid in terms of syntax statement. Post the valid statements
> and regarding to the question LEFT JOIN has nothing to do with SUBSELECT
>
>
> "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
> news:ul%23QIbyWHHA.1396@.TK2MSFTNGP05.phx.gbl...
>> Hi
>> what is faster - LEFT JOIN or subselect
>>
>> example
>> SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
>> or
>> SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
>>
>> Mex
>|||Meekis
Again , these queries gave different results
use Nortwind
go
SELECT o.orderid,c.customerid FROM Customers c LEFT JOIN Orders o
ON c.Customerid=o.Customerid
SELECT o.orderid,(SELECT customerid FROM Customers WHERE
o.Customerid=Customerid)
FROM Orders o
Also there are diffrerences in execution plan show that the LEFT JOIN
performed better
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:%23dNqr4yWHHA.4624@.TK2MSFTNGP03.phx.gbl...
> SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
> or
> SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
> FROM TITLES T
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uxnf0zyWHHA.4668@.TK2MSFTNGP04.phx.gbl...
>> Meelis
>> This is invalid in terms of syntax statement. Post the valid statements
>> and regarding to the question LEFT JOIN has nothing to do with SUBSELECT
>>
>>
>> "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
>> news:ul%23QIbyWHHA.1396@.TK2MSFTNGP05.phx.gbl...
>> Hi
>> what is faster - LEFT JOIN or subselect
>>
>> example
>> SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON
>> T.ID=S.TITLE_ID
>> or
>> SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
>>
>> Mex
>>
>|||hmm
thats because orders MUST have CustomerID
but in my sample
SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
SINDEXES MAY have TITLE_ID
Meelis
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uvCWU$yWHHA.4240@.TK2MSFTNGP06.phx.gbl...
> Meekis
> Again , these queries gave different results
> use Nortwind
> go
> SELECT o.orderid,c.customerid FROM Customers c LEFT JOIN Orders o
> ON c.Customerid=o.Customerid
> SELECT o.orderid,(SELECT customerid FROM Customers WHERE
> o.Customerid=Customerid)
> FROM Orders o
>
> Also there are diffrerences in execution plan show that the LEFT JOIN
> performed better
>
>
> "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
> news:%23dNqr4yWHHA.4624@.TK2MSFTNGP03.phx.gbl...
>> SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
>> or
>> SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
>> FROM TITLES T
>>
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:uxnf0zyWHHA.4668@.TK2MSFTNGP04.phx.gbl...
>> Meelis
>> This is invalid in terms of syntax statement. Post the valid statements
>> and regarding to the question LEFT JOIN has nothing to do with SUBSELECT
>>
>>
>> "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
>> news:ul%23QIbyWHHA.1396@.TK2MSFTNGP05.phx.gbl...
>> Hi
>> what is faster - LEFT JOIN or subselect
>>
>> example
>> SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON
>> T.ID=S.TITLE_ID
>> or
>> SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
>>
>> Mex
>>
>>
>|||...I'd say from the face of it the first option would be faster, the second
would be running a correlated subquery for each row returned... ...but as
Greg said check the execution plan, these things can vary based on the number
of rows returned, available indexes, etc - don't underestimate the optimizer
;-)
"Meelis Lilbok" wrote:
> SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
> or
> SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
> FROM TITLES T
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uxnf0zyWHHA.4668@.TK2MSFTNGP04.phx.gbl...
> > Meelis
> >
> > This is invalid in terms of syntax statement. Post the valid statements
> > and regarding to the question LEFT JOIN has nothing to do with SUBSELECT
> >
> >
> >
> >
> > "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
> > news:ul%23QIbyWHHA.1396@.TK2MSFTNGP05.phx.gbl...
> >> Hi
> >>
> >> what is faster - LEFT JOIN or subselect
> >>
> >>
> >> example
> >>
> >> SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
> >> or
> >>
> >> SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
> >>
> >>
> >>
> >> Mex
> >>
> >
> >
>
>|||As other have posted, SQL Server will most likely select the fastest
query plan itself, whether you have used syntax 1 or 2.
Whether loop join, merge join or hash join is fastest will depend on the
index depth, relative table size and data distribution. Of course, this
all assumes that both tables are properly indexed.
Gert-Jan
Meelis Lilbok wrote:
> SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
> or
> SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
> FROM TITLES T
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uxnf0zyWHHA.4668@.TK2MSFTNGP04.phx.gbl...
> > Meelis
> >
> > This is invalid in terms of syntax statement. Post the valid statements
> > and regarding to the question LEFT JOIN has nothing to do with SUBSELECT
> >
> >
> >
> >
> > "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
> > news:ul%23QIbyWHHA.1396@.TK2MSFTNGP05.phx.gbl...
> >> Hi
> >>
> >> what is faster - LEFT JOIN or subselect
> >>
> >>
> >> example
> >>
> >> SELECT T.TITLE,S.ID FROM TITLES T LEFT JOIN SINDEXES S ON T.ID=S.TITLE_ID
> >> or
> >>
> >> SELECT T.TITLE,(SELECT ID FROM SINDEXES WHERE T.ID=TITLE_ID)
> >>
> >>
> >>
> >> Mex
> >>
> >
> >

Fast loading relational data

I am searching for a way to fast load relation data. I know how to load data fast but how can i store relation data fast.
For example :
Table1 ( tabel1Id int identity , name varchar(255) )
Table2 ( tabel2Id int identity , table2Id , name varchar(255))
When i insert 50 records into Table1 i can't get the 50 identity fields back, to insert the related data into Table2.
I think one of the solutions could be returning a selection of Table1 joined with syslockinfo, but i have no idea how to do it.
Does anyone have an idea?

Add uniqueidentifier column to your master table.

When inserting, generate the value by newid() function and insert it alongside your 50 records. Then you can easily find all the records inserted.

|||Thank you for your reply.
I also thought of that. But i also thought that it must be possible to join with syslockinfo.
|||

I would suggest that you correlate the values in the insert. So, say you have to insert an invoice and line items.

invoiceNumber
000000333
000000334

invoiceNumber product amount
000000333 widget 10
000000334 wadget 11
then the invoice insert is easy, and the line item insert is something like:

insert into invoiceLineItem (invoiceId, product, amount)
select invoiceId, product, amount
from invoice
join (select '000000333' as invoiceNumber,'widget' as product,10 as amount
union all
select '000000334','wadget',11) as newInvoiceLines
on invoice.invoiceNumber = newInvoiceLines.invoiceNumber

Of course exactly how you get the bold stuff will depend on the format of your data (like a spreadsheet, or from a user interface, XML, etc. In reality I would have expected you to have to get a productId as well.