Showing posts with label codes. Show all posts
Showing posts with label codes. Show all posts

Monday, March 19, 2012

Best Practice to update SQL Server Database Tables & Procedures

We will going to have no remote access to the SQL Server 2005… we as
developers can build and test in our place.
After successfully testing the codes… web pages and SQL Server Database new
tables and stored procedures has to be carried physically in the USB Flash
disk and required to go to the host company server location and update the
web pages and SQL Server 2005 Database tables.
What is the best practice … if the situation is that we have to carry the
table and its data inside table physically to the host location and login to
the server and connect USB flash drive and update tables in SQL Server..
What is the best practice to perform update by going physical to the host
company for make SQL Server 2005 Database changes?
> What is the best practice … if the situation is that we have to carry the
> table and its data inside table physically to the host location and login
> to
> the server and connect USB flash drive and update tables in SQL Server..
> What is the best practice to perform update by going physical to the host
> company for make SQL Server 2005 Database changes?
In both cases, the normal approach is to perform new installations and
upgrades using SQL scripts. You can use a tool like SQLCMD to execute the
scripts from a command file. For upgrades, it is important to test against
a production database replica to ensure the database is properly upgraded.
Hope this helps.
Dan Guzman
SQL Server MVP
"TalalSaleem" <TalalSaleem@.discussions.microsoft.com> wrote in message
news:373C5072-E4D4-4F7C-A827-266DAA35C4E4@.microsoft.com...
> We will going to have no remote access to the SQL Server 2005… we as
> developers can build and test in our place.
> After successfully testing the codes… web pages and SQL Server Database
> new
> tables and stored procedures has to be carried physically in the USB Flash
> disk and required to go to the host company server location and update the
> web pages and SQL Server 2005 Database tables.
> What is the best practice … if the situation is that we have to carry the
> table and its data inside table physically to the host location and login
> to
> the server and connect USB flash drive and update tables in SQL Server..
> What is the best practice to perform update by going physical to the host
> company for make SQL Server 2005 Database changes?
|||On Tue, 15 Jan 2008 07:04:25 -0600, "Dan Guzman"
<guzmanda@.nospam-online.sbcglobal.net> wrote:

>In both cases, the normal approach is to perform new installations and
>upgrades using SQL scripts. You can use a tool like SQLCMD to execute the
>scripts from a command file. For upgrades, it is important to test against
>a production database replica to ensure the database is properly upgraded.
I think he's asking more about data. Say you need to send someone 1gb
of data, to populate a table, to update a database, etc.
I'd say you can use a good old ASCII CSV or flat file, but of course
you need some kind of import logic, typically some staging tables and
an SSIS package, to do the work.
Josh

Wednesday, March 7, 2012

Best method for query against large database(zip codes)

I am a little unsure of the best way of doing things. I am creating a dealer locator module. I have the database which contains all necessary info such as zipcode, latitude, longitude called ZipCode. I obviously have a Dealers table which has dealer info, including zipcode. My question for the community is if there is a better way to do this process, because we in the usa alone there is over 65k zipcodes, and large chains can have thousands of locations.

Currently, i grab the zipcode and the radius the want results on from a form. I then take the zipcode and query the database, returning the lat and long of the zip the user entered on the form.

I pass this lat and long to the next procedure which incorporates the radius taken in on the form. This calculates all the lats and longs i will use to "box" off my area to perform a query on areas located within the box.

I then query Zipcodes (select * zipcodes where...) using <= and >= for all sides of the "box"(high and low latitude, and high and low longitude) at this point an inner join is done by zipcode with the zipcode on the dealers table.

Lastly, I do a distance calculation on the returned data dropping all those not within the radius.

Besides anyone have a better way to do this, does anyone have a suggestion about this:
Do I use a inner join for the dealer locator, or should I just add the lat and long to the dealers table automatically upon dealer registration of a cite?


CREATE PROCEDURE dbo.sp_GetDistanceByZip
(
@.StartZip char(5),
@.EndZip char(5)
)
AS
SET NOCOUNT ON
--DECLARE @.StartZip char(5)
--DECLARE @.EndZip char(5)

--SET @.StartZip = '92833'
--SET @.EndZip = '90005'

DECLARE @.LatA float
DECLARE @.LongA float
DECLARE @.LatB float
DECLARE @.LongB float
DECLARE @.Distance float

SET @.LatA = pi() * (Select Top 1 Latitude From zips Where ZipCode = @.startZip) / 180
SET @.LongA = pi() * (Select Top 1 Longitude From zips Where ZipCode = @.startZip) / 180
SET @.LatB = pi() * (Select Top 1 Latitude From zips Where ZipCode = @.endZip) / 180
SET @.LongB = pi() * (Select Top 1 Longitude From zips Where ZipCode = @.endZip) / 180

SET @.Distance = ACOS(
SIN
(
convert(Float,(@.LatA))
)
*SIN
(
convert(Float,(@.LATB))
)
+COS
(
convert(Float,(@.LatA))
)
*COS
(
convert(Float,(@.latB))
)
*COS
(
convert(Float,((@.longa) - (@.LongB)))
)
) * 3963.1

SELECT @.Distance, @.lata, @.longa, @.latb, @.longb
GO

My suggestion for you is to use many short selects rather than a huge join. It'll run much faster, and index it properly.|||Thank you for the reply.
Question:
If I have narrowed down the amount of possible zip codes by my "box"(only the zips, lats, and longs are going to be returned that fall within this "box"), and I then do the join, is this more taxing on the system than your suggestion?|||It is less. you should try to stay away from processing in the SQL part as much as possible.

I would also have gone with a "box" solution.|||Thanks for the input.

I would strongly suggest for anyone interested in this, to purchase the solution along with a subscription for the database. For me, this is more of a matter of being able to do it rather than saving money. If it were a matter of dollars and cents, this solution would cost me roughly forty dollars for a site, and I spent an entire day on it. I may not be the best programmer, but I am worth more than 40 a day.|||Honestly, I have the entire US/Canada, and I've run that procedure with 10 threads. Each returned in less than 2 seconds. I've condensed it to specific regions and such, but for the most part, I'm very much ok with it's performance.

Explain your "boxing" method. That interests me|||Draw a cricle, now draw a box around it, the square should touch the box at four points, all other points contained within the circle are also contained within the box. Why should we query the entire continent or world if we can calculate that box from the radius using latitude and longitude. This drastically limits the number of records that we have to due our distance calculation on. We just get the zipcode and radius from the user, pull the latitude and longitude from the database by doing a select statement of the row containing that zipcode input by the user, next we do some calculations that determine our box from the radius where the centerpoint is the zipcode. Then we select only those records whose latitude and longitude fall within our box. Then we calculate distance based on only those in the box instead of the entire country, continent, etc. Everything outside of our radius distance, gets dropped(these would have still fallen within the box). The results are returned to the user.

I hope I am clear and this explains the "box" technique. Any questions, just post back. There is a little more than what I said which is involved in this, i just tried to explain it as simple as I could. Much easier to draw something like this than use words.|||Hi, I was just wondering...did you ever happen to find a fast solution to your problem?

I am currently working on a project that involved US AND Canada zip codes.

Seeing as Canada has over 700,000 post codes I really need to find the fastest way to accomplish returning results.

The way my app will need to work is pass 1 postal code...and return all records within a 50 mile radius.

Can you please let me know how your project went? Can you please share with me how you were able to achieve this?|||This really sholud not be an issue. We have databases of 300 million records that can be parsed quickly. It is down to your indexing strategies.

For retrieving areas within a circle, use a bit of pythagoras to work out the distances. Job's done pretty easy :)|||i am also about to take on this type of project with the exact same concept...I need to retrieve a business located within a 50 mile radius from a zip code. So any input on this would be great. I am assuming I need to buy a zip code database so if anyone knows of one that will accomplish this please let me know.

if anyone is interested in building it or has the code let me know what you charge to do it.
contact me at rpanek90@.hotmail.com

Thursday, February 16, 2012

Begins With

I am trying to reduce the number of fax numbers I have in my access database querying out all numbers except 6 area codes.

I have tried to do:

SELECT FaxNumbers
FROM FAXList
WHERE FaxNumbers BEGINS WITH 972 OR 817 OR 214;

but it doesn't work, what am I doing wrong?Hi
Try the following query

select phone from users where phone like '301%' or phone like '201%' or phone like '212%';
Thanx and Regards
Aruneesh|||I have tried both the command BEGINS WITH 972 or 817 or 214 or 903 and LIKE 972% or 817% or 214% or 903%, and neither work. I get no results even though I know that there are some in there.

Can anybody help me?
Gurka|||Could u post the command u r using to query the DB.|||I think I got it to work.
I used * instead of %.

Gurka|||Gurka
Good luck with your query.
Aruneesh