Recommendations For Placement Of ZIPCode Table
Apr 30, 2007
We are creating a company-wide table of ZipCodes, States, GPS info, etc. This table can be used by our development and production servers (many of them.) We could place the table on a given server and use linked servers to grant access to that table to the other servers. But is there a better way to handle this globally-useful table?
Barkingdog
P.S. Clearly, we don't want to have multiple copies of this table scattered around on various servers. That introduces synchronization issues.
View 1 Replies
ADVERTISEMENT
Mar 7, 2008
I have a zipcode table that is missing the leading 0's.
Currently Need
501 00501
1001 01001
How would I go about adding the missing 0's?
View 3 Replies
View Related
Aug 16, 2004
I am having problems updating my zip code table that contains zip, city, state, long, lat, ect..
I have the latest CSV file, I tried to use the import wizard in SQL Server 2000 Enterprise Manager.
I set the ID field as <ignore> and specified the appropriate columns for the rest of the data matching from CSV to already designed and working zip code table. Also I checked the box that said "Delete Rows in Destination Table" as well as "Enable Identity Insert" was checked
I ran the wizard, and now I have empty table and it will not insert any records because the error said that the identity column can not accept NULL.
What do I do? I am not updating the identify column so Is it telling me it can't insert NULL into ID?
Any suggestions...
Thanks,
Lito
View 3 Replies
View Related
Dec 14, 2006
Anyone know what's the MS-SQL function that look at the zipcode like "30296-6912" and chop off the "-6912" suffix by just looking at the hyphen part?
Thanks...
View 6 Replies
View Related
Sep 3, 2007
I have a table with a column 'zipcode' that contains a 9 digit zipcode. I am am trying to figure out how I can create a query that returns the count of the most popular zipcodes based only on the first 5 digits.
This is what I have
Code:
SELECT Customers.Zipcode, Count(Customers.Zipcode) AS CountOfZipcode
FROM Customers
GROUP BY Customers.Zipcode
ORDER BY Count(Customers.Zipcode) DESC;
which does what I want it to do except it uses the whole 9 digits.
Any help would be appreciated
View 4 Replies
View Related
Mar 12, 2004
I have the following code, which returns a list of nearby cities based on a city name as input. Most cities have multiple zipcodes per city name, thus it can list multiple rows with the same city name, but with different zipcodes like below:
Zip | Cityname
111 belmont
112 belmont
113 belmont
114 san francisco
115 san francisco
---------------- etc----------------
I do not really care about each group of zipcodes. I only need one pair of zipcode/city name like the following:
ZIP | City name
111 belmont
114 San Francisco
How do I change my select to only return a distinct city name. I do not care which if the city/zipcodes it returns from the similar city.
The select statement is below:
CREATE PROCEDURE ZipSearchByCity
@city varchar(40),
@State varchar(5),
@distance int
AS
SELECT distinct o.City AS City, o.zip_code, o.State AS State,
(3956 * (2 * ASIN(SQRT(
POWER(SIN((z.RADlatitude-o.RADlatitude)/2),2) +
COS(z.RADlatitude) *
COS(o.RADlatitude) *
POWER(SIN((z.RADlongitude-o.RADlongitude)/2),2)
)))) dist
FROM zipcodes z,
zipcodes o,
zipcodes a
WHERE z.city = @city AND
z.State = @State AND
z.zip_code=a.zip_code AND
(3956 * (2 * ASIN(SQRT(
POWER(SIN((z.RADlatitude-o.RADlatitude)/2),2) +
COS(z.RADlatitude) *
COS(o.RADlatitude) *
POWER(SIN((z.RADlongitude-o.RADlongitude)/2),2)
)))) < @distance
GO
Thanks,
Christian
View 7 Replies
View Related
Apr 28, 2008
Hi All,
Does anyone have a Stored Procedure that works perfectly to retrieve all zipcodes within a specified zipcode and distance radius - a zipcode and radius is passed and the Store Procedure result shows all zipcodes that falls within that range.
Thanks in advance
Ade
View 5 Replies
View Related
Nov 30, 2004
Can I choose where I want to store my database using MSDE
View 3 Replies
View Related
Jul 20, 2005
Hello All,I am looking at the performance of our production database. It is40gb, and growing reasonably fast. It is placed in one file group on aRAID-5 array. The array is made up of 20 (or so) 9gb disks. The data,the indexes and the transaction log are all on the "one logical disk".My question then, is, would it be better to move the transaction logonto a separate device (with Raid-1), and then separate out theindexes and the data and to place them onto separate devices (ie.split the raid disks into 2 new drives). Or would it be better toplace the table into a larger number of smaller filegroups effectivelysplit across the raid device and to (strategically) place differenttables into the new logical disks. Does this make sense?Or, do I just leave everything as it is?CheersMike
View 1 Replies
View Related
Aug 16, 2007
Can anyone point me to any Microsoft articals giving reccomendations for file placement for SQL server? We are trying to convince our Hardware guys that we need separate disks for data/log/tempdb files and need some ammo.
Thanks,
Jason
View 4 Replies
View Related
Jul 21, 2004
Hello,
I have a question about how I can change the database placement on our HP MSA1000 SAN. Basically I'm concerned about the performance of one particular server with 40+ databases. I'm familiar with the standard recommendations such as separating data and log files onto different physical drives, etc. But how is this going to be possible when there are only 14 physical drives available in the MSA1000? I also have to be concerned about the other server that's attached. Any suggestions, besides getting additional storage... :)
Thanks.
View 1 Replies
View Related
Sep 8, 2006
I need some help understanding the benefit of creating tempdb with one file per processor. I believe the benefit has something to do with the way SQL Server utilizes processor threads, but I'm a bit weak on the details.
Thanks, Dave
View 1 Replies
View Related
Feb 22, 2007
Using SQL2000
Is it recommended to put tempdb data and logs files on different drives?
View 1 Replies
View Related
Mar 14, 2008
Hey guys
I have someone telling me that you can improve performance in SP's by placing all the DDL at the beginning of the procedure. ie. Do all your CREATE TABLE #tbl and DECLARE's before the rest of your code.
Any thoughts on this?
View 3 Replies
View Related
Feb 22, 2007
Using SQL2000
Is it recommended to put the tempdb data and log files on different drives?
View 3 Replies
View Related
Dec 21, 2006
Is there any thought going into moving these two tables to a file group that we can control? Putting this in Primary with the rest of my system tables is quite problematic, and hinders my ability to manage space usage on my files. Traditionally, we didn't have to consider a primary file group that could grow to large proportions, but now with these two tables it can. If a large volume of messages gets sent through and the system can't keep up, then these tables and my primary file group will grow sometimes enormously.
View 8 Replies
View Related
Jan 25, 2008
I have a server with 2 instances of SQL installed. There are 6 physical disks in the server which have been made into 3 mirrors.
The first mirror has the OS on it. Currently, the 2nd disk has all the database and transaction log files from both instances of SQL.
I plan to make use of the 3rd disk. My question is: is it better to move the database and transaction log files from the second instance to the new disk so that all the files for the first instance are on disk 2 and all the files for the 2nd instance are on disk 3 OR is it better to keep all the database files from both instances on disk 2 and move all the log files for both instances to disk 3?
I'm sure I have read somehwere that in this situation, the disks should be separated by instance rather than seperating by file type.
View 1 Replies
View Related
Aug 24, 2007
What is the best performance for this configuration:
Files:
Data
Log
Indexes
tempdb
Disk:
A - RAID 10
B - RAID 10 (or should this be RAID 1?)
Whats best?:
A - Data and Indexes
B - tempdb and Log
??? Thanks.
View 1 Replies
View Related
Jul 2, 2015
I've got a feeling that the answer is, "can't be done," but I'll go ahead and ask the august members of this forum, anyway. Is it possible to alter the placement of the Parameter fields when previewing a report?
At the moment, it seems that they form in a column of twos, reading from left to right. I see how the ORDER is affected, by changing the order of the parameters in the Report Data window, but can I change the number of columns?
View 2 Replies
View Related
Nov 4, 2015
I like writing concise and compact sql code without cursors if possible. My current dilemma has me stuck though.I have 3 tables, but one of them is optionally used and contains a key element of TimeOut to determine which Anesthesia CrnaID to use. It is the optionally used part that has me stumped.
Surgery table
CaseID
Patient
(Sample data: 101,SallyDoe 102,JohnDoe)
Anesthesia table
CaseID
CrnaID
(Sample data:
101,Melvin
102,Bart
102,Jack)
AnesthesiaTime table (this table is optionally used, only if the crna's take a break on long cases)
CaseID
CrnaID
TimeIn
TimeOut
(Sample data:
102,Jack,0800,1030
102,Bart,1030,1130
102,Jack,1130,1215)
Select Patient INNER JOIN Anesthesia produced too many case results. So, I figured out there is an AnesthesiaTime table that only gets used if the anesthesia guys take time-outs. That doesn't happen all the time. I could use TOP 1 on the Anesthesia table, but technically I need to read the AnesthesiaTime table and locate the last time and pull that crna, Jack. I'm not sure how to deal with an optional table. I believe the IF Exists will be pertinent, but not sure of how to build this query. I've tried subquery without success.
View 2 Replies
View Related
Apr 18, 2015
I can't seem to place the "option (recompile)" in any valid position so that the following procedure executes without a syntax error .
USE [PO]
GO
/****** Object: StoredProcedure [dbo].[npSSUserLoad] Script Date: 4/18/2015 3:57:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ...
-- Generated code - DO NOT MODIFY
-- From Object Schema: 'C:XXXXXX.NetPOPOModel\_ObjectSchema
-- To regenerate this procedure use the 'Open With' option on file _ObjectSchema and select POCodeGen.exe
Declare @SqlCmd nvarchar(max)
Declare @ParamDefinitions nvarchar(1024)
Set @ParamDefinitions = N'@UserId int,NTUser varchar(30), @XmlResult XML OUTPUT'
Set @SqlCmd = N'Set @XmlResult =
(
Select
[UserId] [a],
[UserName] [b],
[code]....
View 7 Replies
View Related
Jul 14, 2000
Hi
I've got a SQL database running on Windows NT 4 Server (P400, 256 Ram, 8Gb IDE & 18Gb SCSI HDD) and quite a few of the queries are taking a long time to run, and are also using a lot of the processor time. This affects other users who are also trying to query the db. Has anyone got any recommendations for upgrading the hardware spec to improve the overall performance? I presume just add more RAM, and get a dual processor system?
Thanks
Ben
View 1 Replies
View Related
Apr 12, 2004
I am a PHP programmer for a small startup. We are storing person records and our MS SQL Server 2000 database has grown to the point where we wish to paginate the data before returning it to my PHP scripts.
I was wondering if anyone has any recommendations on an optimal way to manage this given the following requirements.
- Data must return only X number of rows at a time (user configurable).
- Must be able to search by several diffent criteria (name, date, birthday, location, ...)
Also, I was wondering if it is possible to return the total number of existant rows of data as the first row of a MSSQL procedure.
View 2 Replies
View Related
Jun 19, 2008
I have a SQL 2005 database containing the location of graphics files. I want to start learning how to write a C# application that will get a path from the DB and display the file. Any recommendations on sites where I can start learning how to do this?
Thanks
View 1 Replies
View Related
Jul 21, 2006
Ok, let me start by saying that I already checked the FAQ. There was one link, but it just seemed to go to a review page with 5 books, with pretty specific themes. So I'm surprised that such a basic topic as book recommendations for SQL newbies wasn't covered.
In my case, I'm not a total newbie. I learned to write SQL queries for work on both Oracle and SQL Server, and I've gotten pretty good at all the basics. So I've got "SQL for Dummies" down cold, so to speak. Now I'm looking to take my query writing ability to the next level, which I guess would be the intermediate level. I'm also looking for books that are specific to just SQL Server, rather than the books about general querying on any DBMS. Speaking of which, just so you all know, "SAMS Teach Yourself SQL in 21 Days" is an Oracle book, no matter what it says on the cover.
It looks like the book "Inside Microsoft SQL Server 2005: T-SQL Querying" comes highly recommended, but I flipped through it on the shelf at a bookstore the other day, and I think it's over my head. It might be a good reference to have around, but I think I'd be totally lost trying to read it from cover to cover.
So does anyone have recommendations for books that go beyond "This is how to do a SELECT, and here's how to do a JOIN", but won't make my head explode?
Thanks in advance,
--Fromper
View 4 Replies
View Related
Jul 17, 2006
I do software support for software that works with both Oracle and SQLServer, so I mostly just write queries to look at the data related tothe software. When I first started, I bought a couple of books to learnthe basics, intentionally going with generic books that would help withboth types of databases. I've got the basics down, and now I'm lookingfor a really detailed reference book that goes really indepth intoquerying for SQL Server specifically. Currently, we only work with the2000 version, but we'll be going to 2005 soon, so I think I'd rather gowith a book on 2005, although if you know one that covers thedifferences between them, that would be great. Any recommendations?Besides just writing queries, I'd also like to learn more about SQLServer in general. DBA software support, so I'd like to head mycareer in that direction. I was looking at Microsoft's newcertification path for SQL 2005. Given that my company won't pay tosend me for training, and I really don't want to have to put down$2200+ to pay for a class, I was thinking that I might be able to learnenough on my own to pass the first test and get the MCTS title. Iordered the Training Kit from Microsoft Press for that, which comeswith a 180 day trial of SQL Server 2005 to play with, along with a hugebook. Does anyone have any other recommendations for resources to helplearn this stuff?--Richard
View 4 Replies
View Related
Jul 20, 2005
Hello -Anyone have any thoughts on which API to use - ADO or ODBC?I have a fat client written in C++ using MFC ODBC classes to access a Jetdatabase.The app is going to be modified to write to a SQL Server central databasewith multiple users accessing their local copies of the database ( usingreplication technology on the clients side).Most of the performance benchmarks give an edge to ODBC over ADO whenwriting to an Access database. Anyone know of any benchmarks for a c/senvironment?I've seen references that ADO has some client side cursor features forfilters and sorting which are a benefit over ODBC. I'd also like to usethe asynchronous fetch that OLE DB provides and am not sure if this isimplemented in ODBC.Thanks for the help.Bruce
View 1 Replies
View Related
Feb 2, 2015
Database File Placement Layout? We are planning to implement a new SQL Server 2014 OLTP Database with a 1 TB Data file and 1 TB Log File. I am looking at the possible layout of the database files and trying to determine the best possible configuration. My knowledge/research tells me that items which need separate storage due to constant simultaneous access are:
Data files – should go on the fastest reading storage.
Log files – should go on the fastest writing storage.
TempDb – involves a lot of writing at the same time the data files are being read.
Indexes - (including full text indexes) - involves a lot of writing at the same time the data files are being read.
Also, are there any benefit to having multiple OLTP Database Log files? Because SQL Server writes to the log file sequentially, I do not see any advantages to having multiple database log files. In a SQL Server 2012 Class I took last summer, under “Determining File Placement and Number of Files”, it states “Use a single log file in most situations as log files are written sequentially.”
View 9 Replies
View Related
Sep 15, 1999
Hi all,
I've recently been tasked with doing some SQL 7.0 administration and was wondering if anyone could recommend a good book to get started. The bulk of my IT experience is in SMS, IIS and messaging so my database fundamentals are pretty weak.
As I see it, it's probably a three-step process to get me at least halfway comfortable - a first book to get some solid general database / SQL-language exposure, a second book that takes the knowledge to a more advanced level and finally a MS-specific book that covers the Microsoft implementation of a SQL server.
Any comments / suggestions would be much appreciated!
Cheers,
RM
View 2 Replies
View Related
Dec 3, 1999
I've searched quite a bit, and have found several leads on schema, stored procedure, and database contents comparison scripts and tools.
I'm now looking for recommendations on which ones are best, easiest:
ObjCompare.exe
sb_ABCompareDb.sql
sp_db_comp.sql
There's a mythical script from Andrew Z <mumble> that Mike Hotek talks about...
There's a DBCompare on the Back Office Resource Kit 2 CD, which of course is not in the umpteen MSDN CDs :-(
There's some *other* command line dbcompare, or maybe db_compare.
There's a DBA Compare.
I need to be able to compare divergent schemas from two developers to integrate their changes, so need schema and stored procedures compared only, and would also like to have something to compare staging servers and production servers.
Leads on other choices also welcome. I'd be happy to summarize and post, if warranted.
View 1 Replies
View Related
Jun 29, 2004
Hi,
I've been using Sybase for some years but by employer is moving (you guessed it) to MSSQL.
Can anyone recommend a TSQL and performance tuning training course for MSSQL in the London area?
Thanks,
Rob.
View 1 Replies
View Related
Mar 22, 2006
I'm a solid SQL developer/dba and have some funds earmarked for training this year. I'd like to expand my dba skills...more specifically, I'd like to become a rock-solid enterprise level dba that has not only a solid foundation of skills but some innovative techniques for managing our corporate SQL servers.
I'm curious if anybody has recommendations on training or conferences that might help me dig into those skillsets for a couple of days.
Thanks for your insight.
alex8675
View 1 Replies
View Related
Jul 20, 2005
I am looking for a book that discusses query tuning, index tuning, executionplans, etc. Can anyone recommend something?--BV.WebPorgmaster - www.IHeartMyPond.comWork at Home, Save the Environment - www.amothersdream.com
View 1 Replies
View Related
