Tried to create a new dataset by Database Provider. The data source is a table in SQL Server.
Tried many times, no table name can be showed.

First two settings were set as followed:
Select provider: System.Data.SqlClient
Connection string: "provider=SQLNCLI10; SERVER=Localhost; uid=sa; pwd=sa; DATABASE=wstock"

In my understanding, if it is ok, table names will be showed. However, there is nothing showed. Just by chance, I choose select provider as System.Data.Sqlclient again, it popped up a message: Error: Keyword not supported: 'provider'.
Not sure what is wrong here. Could you please help? Thanks.

QUOTE:
System.Data.Sqlclient again, it popped up a message: Error: Keyword not supported: 'provider'.

You've selected the .NET Framework Data Provider for SQL Server (System.Data.SqlClient) but you're passing it an unrecognizable parameter instructing it to connect over OLE DB ("provider=SQLNCLI10"). Naturally, it will fail.

Click "Connection string help..." in DB provider's New DataSet Wizard: it's there precisely to help with building a connection string quickly. Hope that helps.

Thanks, Eugene.

It is great now.

Thanks for your help. Eventually database was connected.
I tried to create two datasets by two tables in one sql server database, one is daily OHLCV(485MB), the other one is 5 minutes OHLCV(14GB).
In the database extension, table names showed are right. After I choose the 5 Min table, it popped out a message box as showed here.
But I still can go to next step. So I try to match the field of tables to the OHLCV. Matching security name is ok, but it stucks at matching DateTime. Even if I don't click the DateTime, it stuck sometimes. Don't know the reason. I guess it is because of the size.
If I choose Daily table, which is 485M, there is no error message showed as 5 Min. I can go to next step with a 3-5 seconds pause after I choose Daily table. After I match security name, it start to stuck again, however after 5 minutes, it is ok. After that, it is smooth.
How can I solve this problem? Thanks.
There is the other questions regarding creating dataset. It is because of categories. In both above tables, the instruments include stocks, bonds, and funds. In the stocks, there are more than 5 different categories standards, for example, provinces, industries, index stocks, etc, even there are some dynamic category for example self-pickup stocks, or the stocks I would like to monitor by Strategy Monitor. How can I show these categories as datasets? I am try to make them as separated tables, however datasets can't reflect their change in time without restarting WLD if I don't make mistake. Is there any solution for that? Thanks

Your database is not indexed (or is not properly indexed), right?

Daily table is indexed. It is the same.
5 Min table, try to make it indexed however it showed
'5Min' table
- Unable to create index 'IX_5Min'.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
It sounds the same problem.

I am trying to add an index in another way, but it took an hour already, not done yet.

14Gb should take some time to index, especially if you're not a SSD user.

In the future, you can try adding ";Connect Timeout={some_value}" to your SQL Server connection string. Of course that isn't a substitute for indexing your database, for example, on the symbol field.

not sure why I could not add an index successfully, SQL Server Management Studio shows "- Unable to create index 'IX_5Min'.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."
Do you know what's the problem of it? Thanks.

Eventually I added an index for the symbol field successfully. But the same error as #4 post showed.
As you suggested, in the connection string, I added "Connect Timeout=30", the database provider stuck again after I chose security name. It is more than two hours already. I have to stop it. Or, I could not go to sleep.
could you please help? Thanks.

Please ask on SQL Server boards like sqlservercentral.com, serverfault.com etc. Google it and you'll find lots of advices on the "Timeout expired" problem.

Timeout expired @ stackoverflow
Timeout expired @ serverfault
Timeout expired @ sqlservercentral

This morning, I tried again with Connect Timeout=30. Eventually it was ok after 4 hours stuck, but new problem was showed.
I double clicked the symbol listed in the dataset, it popped out a message as below.
Error: Incorrect syntax near '5'.
What's wrong with it please?

Might be the table name starting from a number ('5', i.e. 5Min). Something doesn't seem to "like" it. Try renaming it.

You are great, Eugene. Yes, it is because of the table name. The provider works great now.

Good for you. Here's an explanation of what happens here from a programmer's standpoint. It's a side effect of SQL restrictions that the provider must adhere to.

If your table name starts from a digit, or you use keywords (e.g. "open", "close") or special chars in the column names, the RDBMS must be made aware to avoid error. Usually, programmers just have to wrap it in square brackets e.g. "[ TableName ]". Unfortunately, some databases may have their own conventions e.g. MySql. Since the Database provider tries to support any ADO.NET compatible database, blindly using square brackets is not an option for me. And since there are many SQL 'dialects', connection string builders not always help. A workaround might be passing the table name as a parameter in a parameterized SQL query, but it's impossible: table name can't be parameterized. (Maybe dynamic SQL could be a solution but for some reasons it's not considered.)

Added a note to: Database static and streaming provider :: Limitations

Thanks so much for your detailed explanation.

I am trying to make a streaming provider. Not sure if SQL Server database is the best solution since I found it was slower than txt/csv statically. Do you have any suggestion or recommendation? Thank you.

The question seems too general to me. Database performance depends on many variables.

could you please teach me more?
As regard to hardware, my computer was built last year, CPU is AMD Phenom II X4 955 3.2 GHz, 8GB memory, 1 SSD, 2 hard disks (7200 RPM) more than 1 TB.
Tks.

Considering that this is the Wealth-Lab support board and we only support Wealth-Lab products, please ask general questions regarding SQL Server and database performance on specific resources (e.g. see my post #11).

That's fine. Thanks anyway.

In a deleted duplicate thread that essentially is a continuation of this thread, tradercn asked:

QUOTE:
Database Provider worked ok so far. But found an error, sometimes some dataset can be retrieved, sometimes an error message was popped out as below.



I guess it is because of the timeout setting in the connection string. I set timeout=30.
Not sure how to change it in WLD, you have any idea? Or, I need to build a new dataset again, I don't prefer to do that since it is time consuming. Thanks.

Next time you see a message box like this, simply strike Ctrl-C and Windows will copy the text to clipboard. Ain't that convenient?

Again, I don't have much to add to post #8. Either create a new DataSet and specify a greater timeout value, or manually edit the DataSet's XML file with WLD closed. The latter is an error-prone operation, if you don't have XML editing skills and w/o an editor able to verify resulting XML structure. If you dare, the _connString field is what you need; you'll find the location in the User Guide > Data > Where data are stored?.

Did I say you're better off asking on SQL Server boards? Your DB setup needs some adjustment, if it times out (IMHO).

You are amazing. All the time when you answer me, I got something to learn.

Thanks so much. I will go deeper for SQL.