Database

Re: Is there a way to efficiently load the cities of US into the database

11-Dec-07 23:17

When I needed to load a list of all the countries in the world I used an XMLDataSource and found an XML file with all the countries in it. I think I found the file at the US Census Bureau web site if I'm not mistaken.

I'm sure you could find an XML list with all the cities and then you could load it and save it to the db.

And how do you manage to submit a post on the 13th of December? Pretty crazy.

"When you have made evil the means of survival, do not expect men to remain good. Do not expect them to stay moral and lose their lives for the purpose of becoming the fodder of the immoral. Do not expect them to produce, when production is punished and looting rewarded. Do not ask, `Who is destroying the world?' You are."

-Atlas Shrugged, Ayn Rand

Rocky#11-Dec-07 23:24

Rocky#

11-Dec-07 23:24

ok that's gr8 I'll chk it out to see what I can find on google.

Rohde wrote:
And how do you manage to submit a post on the 13th of December? Pretty crazy.

That one was over the top Sir!! Big Grin | :-D

Didnt get it????

Rocky

Success is a ladder which you can't climb with your hands in your pockets.

Re: Is there a way to efficiently load the cities of US into the database

Rohde11-Dec-07 23:32

Re: Is there a way to efficiently load the cities of US into the database

11-Dec-07 23:32

Rocky# wrote:
That one was over the top Sir!! Didnt get it????

Heh

When I reply to your post the beloved CP displays:

Reply to Message header:
Forum: SQL / ADO / ADO.NET
Subject: Re: Is there a way to efficiently load the cities of US into the database
Sender: Rocky#
Date: Thursday, December 13, 2007 2:24:00 AM

Rocky#11-Dec-07 23:39

Rocky#

11-Dec-07 23:39

oh wow! that mean ATTN CP web masters!! There's a little issue around here. I wonder where did that come from

Rocky

Success is a ladder which you can't climb with your hands in your pockets.

how select row ?

azumike11-Dec-07 22:34

azumike

11-Dec-07 22:34

I have a problem in selecting one row of a table
1. Could you help me to select only one row without querying by the data inside the table, please ?
2. How i can identify the row ?

thanks and regards,
azumike

Rohde11-Dec-07 23:19

11-Dec-07 23:19

You use a primary key.

azumike11-Dec-07 23:32

azumike

11-Dec-07 23:32

thanks for your inquiry,I have it, but, i want to select row without key on the table,
i mean that i want to select 1st row, 2nd row etc.

example i have table:
table name is: "person"

No | property
----------------------
1 | house
1 | name
1 | age

i want to select 2nd row but i don't wanna use :
select * from person
where property='name'

any other way ?

regards,
azumike

Rohde11-Dec-07 23:39

11-Dec-07 23:39

You cannot select a row from a database table without a unique identifier, i.e. a key. How else should the database know what to give you?

CONCATENATION problem!!?? (SQL 2000)

azumike12-Dec-07 23:25

azumike

12-Dec-07 23:25

ok thank you very much

anthoy11-Dec-07 22:04

anthoy

11-Dec-07 22:04

help anyone!! phaleezz!!

i just want to combine the returned values(both varchar types)of my two 'user defined function' as one field in my sql statement

my codes like this..

<br />
select cast (UDF_getCellNos  + '/' + UDF_GetLandLines as varchar(100)) as Contact_Info from tblCrewInfo<br />

if both my UDF's returns a value..

my column 'Contact_Info' returns '09179673815 / (02)9614337'

works out fine..

but if either of my UDF's does not return a value...

my column 'Contact_Info' returns 'null'

it's supposed to return the value of the other UDF..
and thats my problem D'Oh! | :doh:

..is there any other way i can get my UDF's returned values as one column, even if one doesn't have a return value?

any help will be appreciated...
thanks in advance

Re: CONCATENATION problem!!?? (SQL 2000)

pmarfleet11-Dec-07 22:15

pmarfleet

11-Dec-07 22:15

Concatenating a NULL value with a string will always return NULL. Use the ISNULL function to convert NULL values to empty strings to prevent this happening.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Re: CONCATENATION problem!!?? (SQL 2000)

anthoy11-Dec-07 22:21

anthoy

11-Dec-07 22:21

woahhh you're a god man thanks for a fast reply
thanks pmarfleet

damn i really love this site!!

Re: CONCATENATION problem!!?? (SQL 2000)

pmarfleet11-Dec-07 22:33

pmarfleet

11-Dec-07 22:33

anthoy wrote:
damn i really love this site!!

Yep, CodeProject rocks Smile | :)

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Connect sql server 2000 by ADO.NET on two VLAN???

Doan Quynh11-Dec-07 20:23

Doan Quynh

11-Dec-07 20:23

I want to connect to sql server on other VLAN (C# 2005 ).

Exam:
Client IP : 192.168.10.100
SQL server IP : 192.168.20.200

Is it possible???

QuynhTD

SQL Perfomance severe decline, why?

alexvw11-Dec-07 16:05

11-Dec-07 16:05

Hi everybody,

I wrote an app that processes a list of invoices and for each one fires a stored procedure against a SQL2000 DB. The workload is usually of about 4 to 6 thousand items. All the store procedure does is insert a record in the transactions table and updates the customer's balance in a second table.

It appears to work great, but suddenly the SP's performance drops down, for a number of records it works at closely to 200 items/sec and then 1 or 2 items/sec. This performace decay lasts for about 10 secs and then regains it original performance. Monitoring the CPU usage I noticed that it is somewhere around 60-70% and then drops to 2%.

I already tried eliminating the pre-instaled DEMO Alerts and I have no tasks scheduled in the SQLAgent.

I also ran a trace using the Profiler and the delay does never occure in the same part the SP. The truth is that I do not know what to look for or where to look for. Can anybody offer guidance? Thanks a lot. OH! and HAPPY HOLIDAYS!!!
I wish I could post answers rather than questions Frown | :(

SimulationofSai11-Dec-07 21:55

SimulationofSai

11-Dec-07 21:55

alexvw wrote:
for a number of records it works at closely to 200 items/sec and then 1 or 2 items/sec

alexvw wrote:
Monitoring the CPU usage I noticed that it is somewhere around 60-70% and then drops to 2%.

Looking at the above, I can only guess that SQL Server is flushing dirty pages to disk.

Whatever modifications there are, it's done in memory(RAM). And when SQL Server runs out of allocated RAM, it has to flush all the dirty pages in memory to disk, and free up the RAM for more records. Most likely that's what is happening.
Check your I/O rate when the SP slows down, you'll more likely see an increase in I/O. And there's not much work for the processor at this time(and hence the drop in CPU)

The SP runs fast for sometime because all records are in memory. Then the dirty pages need to flushed and new pages need to be loaded. This usually happens in different non-overlapping threads. But the fact that all these threads have to wait for the same resource(Disk Drive), slows them down at some point.

Try allocating more memory to SQL Server(usually means "add more RAM"). But I believe that this problem will be solved to an extent when you move to production servers with multiple processors and drives.

Good Luck!

alexvw12-Dec-07 2:07

12-Dec-07 2:07

Hi SimulationofSai,

Please forgive my ignorance. I have no idea how to "Check my I/O rate". Confused | :confused:

About the RAM, well it is true that I am using a single processor machine as a server with 1.5GB of RAM and SQL it is set up to suck it all up if needs to, but during the execution of this process, RAM consumption never surpasses 710MB (including RAM allocated for the OS, etc.) How does this fit into then "flushing all the dirty pages in memory to disk to free up the RAM for more records."?

I do not know if it helps to know this. When looking in detail at the server trace, the dealy sometimes occures at the "Insert" command of the SP and sometimes at the "Commit Tran" call. Remember that the SP does these two actions on separere tables; that's why the "Commit Tran".

Thanks.
Sai Ram

Paddy Boyd12-Dec-07 5:39

Paddy Boyd

12-Dec-07 5:39

Do you have a number of users accessing these tables through transactions at the same time? You could have some locking of tables occurring.

alexvw12-Dec-07 8:53

12-Dec-07 8:53

No, There are no other users or processes accesing those two tables at all. Did trace the Locks and there were none.

This seems to be a tougth cookie, doesn't it?

Also, there are no triggers in those tables!

Thanks. I am open to more ideas.

Paddy Boyd12-Dec-07 21:18

Paddy Boyd

12-Dec-07 21:18

Have you tried posting this at a more SQL server centric site (www.sqlservercentral.com) as an example? You might get some more specific knowledge there.

alexvw13-Dec-07 0:41

13-Dec-07 0:41

Hi Paddy,

I just did that a few hours ago! Thanks for the tip.

Have a Merry Chrismast!

Alex.

Mike Dimmick13-Dec-07 5:08

Mike Dimmick

13-Dec-07 5:08

My guess is that you have automatic growth turned on. The stall happens when you run out of capacity in the database and the server grows the data file.

In any production server you should dedicate a disk (array) to the database, set the file to the entire size of the disk (array), and turn off automatic growth. You should then monitor the usage of the file to determine when to expand onto more disks, and to ensure that your archiving and cleanup solution - you do have one, right? - is running sufficiently frequently.

Further, you should dedicate another disk (array) to the transaction log in the same way. Ensure that no other I/Os target this drive - for best performance, you need the disk head to remain in the correct position for writing the next log record. You should ensure that you back up the transaction log regularly so that there is spare log capacity. Not backing up the log will eventually cause the database to fail as there is no more space for the log to expand into. For a production system I would advise NOT using the Simple recovery model - this model does not offer recovery to point of failure in the case of a lost or corrupted data file. If you're going to use a RAID array for failover, stick with RAID 1 (mirroring) - you get no performance benefit with striping. Particularly avoid RAID 5 which has nasty overhead.

DoEvents: Generating unexpected recursion since 1991

alexvw13-Dec-07 14:18