Import Ninja DB into SQL Server 2005 Express

[Turtle Trader]

Hello,

I would like to bring the daily transaction data from the Ninja DB and store it in SQL Server 2005 Express.

I scalp a lot and want to capture all this data in a database where I can produce analytical reports to aggregate the results, to make my work more efficient.

Is anyone doing this? Any information will be useful.

Would love to automate the process so it will run at the end of each day and pull the day's data into SQL Server.

Thanks

[NinjaTrader_JoshP]

Unfortunately this is not supported by us. What you could try to do though is export your performance at the end of the day from the Account Performance to Excel and probably run a macro in Excel to get it into SQL? Hopefully someone who has worked with this before can help you out.

[Turtle Trader]

success with 6.5 database, in entirety

I was just now able to import the complete 6.5 MDB database into SQL Server with the Microsoft DTS (Data Transformation) utility.

The next step will be to figure out how to get the daily transactions out, on a daily, not bulk basis.

I had a question to tech support about the 7.0 database. It will not be an MDB database, but I'm hoping it will at least have an ODBC capability where I can still get to the data.

If I can capture the complete daily transactions that will really going to help my record keeping.

[MicroTrends]

SQLSERVER DTS , SSIS, MSAGENT - yep 15 years of it

Hi

I have many years of blue chip Enterprise clustered SQL exeperience...
It ought to be very simple - once you got the job to run once
you script it and run it from MSAgent
or run OSQL from the NT scheduler if you dont have that...
Look at bcp as well.

Let me know if i can be anymore help....

The question is though: How are you going to read it? Is it not compressed/ encrypted within the mdb?

Exporting your daily data out on session first bar to CSV or excel is better as suggested below:

Take away the balance at the last first bar:

Here is some code for you

using System.Data.OleDb;

Connect to excel via oledb:
privatestring m_sConn1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\mybook.xls;Extended Properties=\"Excel 8.0;HDR=YES;\"";

OleDbConnection oc = new OleDbConnection(m_sConn1);
oc.Open();
if (oc.State != ConnectionState.Open)
{
thrownew Exception("Cannot open connection " + m_sConn1);
}


SQL = "INSERT INTO [Sheet1$] ([ID],[Instrument],[data series], Win, Loss) values ('ABCEDf123123...','ES','89 tick',1000,900)";

dc = new System.Data.OleDb.OleDbCommand(SQL, oc);
dc.ExecuteNonQuery();
oc.close ... clearup etc



You may find the challenging part the performance data - as this is a collection - perhaps you can write linq and query transacations for the session - using some date as a filter or:

on first session bar get the balance - take that off the current day when you iterate to the next first session bar...

[Turtle Trader]

DTS works

Hello,

Did I mention the DTS ported the table structure and all the data from the MDB to SQL Server?

I haven't checked yet to see if there is a key structure that would tie all the transaction data into a group that could be easily selected.

Because I could get the data out with DTS the MDB data isn't encrypted...which actually is good...if I wanted it encrypted I would put it inside an encryption wrapper like Truecrypt or something like that.

Also found out that the database in Ninja 7.0 is going to be the MS SQL Server Compact version. This is great news...hope it is not encrypted and that the table structure easily allows one to pull the transaction data by date and time.

[NinjaTrader_JoshP]

This is beyond the level of support we can offer. Suggest you give it a try when it is out.

[MicroTrends]

The next step will be to figure out how to get the daily transactions out, on a daily

Hi i thought it was already encrypted inthe mdb so it was unreadable...

Use Transact SQL Step:

if you send me the mdf file schema it created i can write you a query..

You would use an upsertdelete pattern

--deletes first optional

--insert data from my dataset where it does not exist in the target table
not sure how you will get that dataset without it being in bulk but no matter...



Example:
import the data into a stagingtable either in bulk or by filtering for today etc

peform a selective insert after


CREATEPROCEDURE [dbo].[insertData]
AS
BEGIN
SETTRANSACTIONISOLATIONLEVELSERIALIZABLE
SETNOCOUNTON;

declare @StagingTable table([userid] [nvarchar](128)NULL, [Email] [nvarchar](128)NULL)
--#1 get new data
INSERTinto @StagingTable(userid,Email)
SELECT userid,email from datatable
where DATECreate = [today] etc - maybe jsut get the whole lot...
--#2 insert new data where not exists in one go...
--ADD
INSERTINTO subscribers(userid,Email)
SELECT S.[userid],S.[Email]
FROM @StagingTable S
LEFTOUTERJOIN Subscribers S2
ON S.userid = S2.userid
WHERE S2.userid ISNULL
IF(@@ERROR<> 0 )
BEGIN
GOTO Cleanup
END
COMMITTRANSACTION
RETURN 0
Cleanup:
ROLLBACKTRANSACTION
RETURN-1
END

[Turtle Trader]

private email

Hi Microtrends....lets pursue this in a private message if you wish...not sure how to send them. Please send one to me if you are interested.

I also used to develop TSQL in a corporate environment. All back end stored procedures.

Perhaps we can collaborate.

Thanks...

[zweistein]

Some time ago I looked into the sdf file stuff.

If I remember well then Ninja Objects are first mapped the WilsonOrMapper and then stored in the sdf file.

I remember that from within Ninja you could not access the .sdf file directly.

No more info because I solved my issues in another way.


regards
andreas

www.zweisteintrading.eu

[jericmiles]

Hello,

I would like to bring the daily transaction data from the Ninja DB and store it in SQL Server 2005 Express.

For $50 you can get the SQL Server Developer edition and use "transactional replication" to pull the changes from the NT database.

[zweistein]

Hello and happy new year!

Are the sql ce database objects contained in the .sdf files of any use?

I had the understanding that you need to unmap the database objects back to Ninja classes first.
There is a Ninja global object "NinjaTrader.Db.Globals.Engine" that is helpfull here.

Best regards

Andreas
www.zweisteintrading.eu
NinjaTinyMobileTrader (access Ninjatrader remotely via your mobile phone)


Here a (older) code sample on how I read the Session Templates contained in the database.

a List of Session names is returned:

List<string> templates = new List<string>();
try {
Wilson.ORMapper.ObjectSpace objspace = NinjaTrader.Db.Globals.Engine;
Wilson.ORMapper.ObjectReader cursor = objspace.GetObjectReader(typeof(Session),String.Em pty);
while (cursor.Read())
{
Session A= (Session) cursor.Current();
if (!templates.Contains(A.TemplateName)) templates.Add(A.TemplateName);
}
cursor.Close();

}
catch{}

return templates;