Database

Export 50,000 records to a Text File.

8-Jan-06 16:14

If you don't know a username and password with enough rights to stop the database, let alone connect to it, your question is pointless. There is nothing that will tell you what any username or password is. You'll have to get this from the database administrators.

RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome

kakarato6-Jan-06 7:52

kakarato

6-Jan-06 7:52

I'm using a smart device which run Win CE witn SQL CE 2.0. Would like to know what is the best way to export a table in the device with 50,000 records to a text file.

Should i use a SQL SELECT statement like "SELECT * FROM TABLE" into a data set and later loop the DataTable in the DataSet and export write it to a text file ?
Or select in it into a DataReader and loop the DataReader? Which one will provide a better performance ?

I try to to use method 1 (loop the DataTable in DataSet, but the proble is it take a long time to select all the record into the dataset, and it look like hang over there to the user Sleepy | :zzz:

, because i can't update the status and show to the user.

However method 2 (loop DataReader) seen like bit better at least i was able to update the status to the user and let user know the program is running.

Please kindly advise. Thank in advance.

Re: Export 50,000 records to a Text File.

Colin Angus Mackay6-Jan-06 12:00

Re: Export 50,000 records to a Text File.

6-Jan-06 12:00

kakarato wrote:
into a data set and later loop the DataTable in the DataSet and export write it to a text file ?
Or select in it into a DataReader and loop the DataReader? Which one will provide a better performance ?

If you use a DataAdapter to fill a DataSet then the entire contents are read with a DataReader (that is how a DataAdapter gets the information from the database) and the DataTable is populated with the data, you have to wait for this operation to complete before you can access even one row. Then you can write it to your file.

With a DataReader you can write the rows directly to the file as you get them. You do not have to wait for a DataTable object to be filled and you do not waste that memory either.

As you have discovered already that the user is left hanging around for a long time while the DataSet is filled, while the DataReader allows you to give feedback on progress immediately.

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

kakarato7-Jan-06 6:22

kakarato

7-Jan-06 6:22

Thanks Colin. Smile | :)

increasing size of LDF file

Member 20731416-Jan-06 2:52

Member 2073141

6-Jan-06 2:52

Hi All,
In my project database is SQL server.
The LDF file size is increasing considerably(now it is 16 gb).
It may be due to unCommited transactions.
My question is that if I close the connection after each query execution will it commit the transaction so that the file size will be small.?
or is there any other solution to decrease the file size?
Thanks

Dave Kreskowiak6-Jan-06 5:17

6-Jan-06 5:17

anant.awadhut wrote:
My question is that if I close the connection after each query execution will it commit the transaction

In general terms, no, you have to commit the transaction yourself, provided you actually started one. If you start a transaction and don't explicitly commit it, then, upon disconnection from the database, it assumes the transaction is not complete and any changes within that transaction are rolled back.

You might find this[^] MS KB article useful in helping you determine where the problem is.

RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome

Dave Kreskowiak6-Jan-06 7:05

6-Jan-06 7:05

OK. This has been bugging me for a little while.

I assumed you where doing the basics:
Are you backing up the database regularly?
Are you backing up the Transaction Log, and truncating the log to remove old, unneeded transactions?

If not, you might want to go into the Enterprise Manager and setup a Maintenance Plan....

RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome

Member 20731416-Jan-06 17:47

Member 2073141

6-Jan-06 17:47

Yes I am backing up the database daily.
I am taking the backup of complete database not just a ldf or MDF file.
But I don't know how to truncate the log to remove old,unneeded transactions.
How this can be done?
Thanks

Dave Kreskowiak7-Jan-06 6:30

7-Jan-06 6:30

Well, you can read the docs on the BACKUP sql statement here[^].

If you're doing a Full backup of the database, and the log doesn't get truncated already, you can follow it up with the SQL statement

BACKUP LOG databaseName TRUNCATE_ONLY

to dump the old transactions in the log that are no longer required to rebuild the database.

What you're trying to maintain is a backup of the data, which you're doing daily, and a backup of the transactions in the log since the backup of the data was last done.

RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome

-- modified at 12:31 Saturday 7th January, 2006

SQL Sample Databases

Brendan Vogt6-Jan-06 0:34

Brendan Vogt

6-Jan-06 0:34

Hi,

I have to develop a complex database, but I need to look and work through sample databases. Ideally I would like to look at the eBay database because it comes with a lot of features such as product listings in various countries, shipping, etc. I had a look at the eBay API at http://developer.ebay.com, but can't seem to find the database. I'm not sure if it is available for download.

Does anyone know if the website, or part of the website is available for download with the Windows API code?

If anyone knows of good sample databases I can use for my project, please let me know. It is an e-commerce application that has shipping, products listings in various "country-based websites", loyalty point system, displays what other products were bought etc.

Regards
ma se Poke tongue | ;-P

Re: SQL Sample Databases

Colin Angus Mackay6-Jan-06 1:24

6-Jan-06 1:24

ma se wrote:
Ideally I would like to look at the eBay database because it comes with a lot of features such as product listings in various countries, shipping, etc. I had a look at the eBay API at http://developer.ebay.com, but can't seem to find the database. I'm not sure if it is available for download.

I would seriously doubt it would be available - That is proprietary information and companies guard that kind of thing jelously because it is the key into their business.

ma se wrote:
If anyone knows of good sample databases I can use for my project, please let me know.

The Advernture Works database that comes with SQL Server 2005 is a good sample database to get you started.

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

Re: SQL Sample Databases

Brendan Vogt6-Jan-06 1:29

Brendan Vogt

6-Jan-06 1:29

Thanks for the reply.

I have SQL Server 2005 Express. I miss the query analyser in this version as what I had in SQL Server 2000. Everything seems to work through C# Express, or Visual Web Developer Express. Is there such a product available for SQL Server Express edition? I would like to view the Advernture Works database? Where can I download this database if it does not come with the express edition?

Re: SQL Sample Databases

gharry6-Jan-06 5:44

6-Jan-06 5:44

You wil find sample database at these links:

http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034&DisplayLang=en

to SQL server 2000 sample databases that can be used with the 2005 versions and here

http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en

for 2005 specifik database examples. Thoose the correct platform and install.

SQL Doubt.

<color>Aljechin 5-Jan-06 19:32

<color>Aljechin

5-Jan-06 19:32

I have one small program which uses a database (MDB) i can copy both the exe and mdb and run on another machine. But if I use MS SQL then how can I ship the data with the program? Its because SQL is more powerful I want to do this. My program should query what file? Please explain me. I am very new to SQL Server. Just starting. Confused | :confused:

Re: SQL Doubt.

nguyenvhn5-Jan-06 20:48

nguyenvhn

5-Jan-06 20:48

Because MS Access is file database so you can copy it beside application. But MS SQL Server, as it named, is a database server, you have to connect application to such server that running SQL Database engine. If another computer is on a LAN, you could config connection string which point to database server. If another computer do not reside on LAN or in others networks, you have to re-config another database server and point connection string to that server.

Hope that help

Re: SQL Doubt.

Colin Angus Mackay5-Jan-06 20:49

5-Jan-06 20:49

Normally what I do is ship a backup file and have the installer restore the backup onto the target SQL Server. I then write scripts to reconnect all the security back (because things like the logins are handled in the master database, and you can't restore over your user's master database because they'd lose all their other server level settings)

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

DAO Notification?

lynchspawn5-Jan-06 9:51

lynchspawn

5-Jan-06 9:51

I have an .mdb file (MS Access) that each employee will be using to tell who is in or out of the office. The .mbd file will be located on one of our servers. A windows program currently polls at a certain interval to refresh the status.

Is there some built-in way to have notifications sent from the DAO objects instead of polling?

I was also considering writing a server app and have each users program register with the server and handle it that way.

If there is a better way, please let me know.

Re: DAO Notification?

Dave Kreskowiak6-Jan-06 5:05

6-Jan-06 5:05

No. Access doesn't support triggers or notification. SQL Server 2000 and above does. SQL Server 2000 needs a Notification Services extension installed. Search MS's site. SQL 2005 comes with it standard.

A workaround method would be to implement something like a service, or .NET Remoting singleton, that manages access to the database, updating required tables and whatnot, but also exposing events that the clients subscribe to. When one client requests a change to the database, it does so through this service or object, which then fires your Changed event so the other clients get notified.

RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome

-- modified at 11:06 Friday 6th January, 2006

Partial name

gharry5-Jan-06 9:47

5-Jan-06 9:47

If i want to search for a name in a table i use Select * from Tabel where Column = name

But how do i proceed if there are names like na, nam, name, and names.
The reason is that i would like to search for partial names if the full name is not found or more than one starting with nam

I hope the explanation is clear

Re: Partial name

Colin Angus Mackay5-Jan-06 11:01

5-Jan-06 11:01

This will search for anything containing "name"

SELECT * FROM MyTable WHERE column LIKE '%name%'

The % is a wildcard meaning zero or more characters.

However, overuse of wildcards screws indexing and reduces the performance of the SQL Server.

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

Re: Partial name

gharry5-Jan-06 22:46

5-Jan-06 22:46

Thanks thats where i was looking for.
Is there a simple way to determ how many records are returend so i can either pace them in a list ore in a label

Re: Partial name

Colin Angus Mackay6-Jan-06 1:45

Reading CSV file into DataGrid

6-Jan-06 1:45

gharry wrote:
Is there a simple way to determ how many records are returend so i can either pace them in a list ore in a label

That depends on how you are extracting the data from the database.

If you are using a Data Reader then you just have to keep reading until you have all the rows. If you use a Data Adapter then fill the DataSet and find the Rows.Count from the DataTable.

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

gharry5-Jan-06 5:32