Selecting First 10 Addresses In Each City In Each State

Oct 4, 2007

Greetings:

I read with great interest a post on getting hte top 75 per cent by month, but still get through this problem. I have a table containing addresses, city names and state names, Each row has a unique address. I want to get the first ten addresses for each city in each state. I've tried something like the following which didn't work. Suggestions on how to solve the (simple) problem would be appreciated.
Select top (10) city,state,address
from address_list
alan

View 3 Replies

Country State And City Table - Best Practices

Oct 2, 2007

Quick questiom, I need to Import a table with City and State Information

What are your recomendations? Should I create a Unique Key for them?
CityID, CityName, StateID?
StateID, StateName?

Or can I just store them in a table with one column?
CityName, State

Thanks for your help/

View 2 Replies View Related

Combine City Field With Prov/State

Jun 4, 2007

Hello I am new to SQL Server 2005 and am designing my first database.

In the AdventureWorks sample database the city field is included with the street address where it repeats for each row.

Why would you not put it in a separate table with state/province or a separate table all togeather?

Thanks for your help.

View 5 Replies View Related

Transact SQL :: Server Script For List Of All Country State And City

Aug 20, 2014

I am searching for the Sql Server Script of all country State and City with the Following type..

For the Country only 2 Columns are there i.e. CountryId and CountryName(CountryId is primary Key)

For the State 3 Columns are there i.e. CountryId, StateId and StateName(StateId is Primary Key and CountryId is Foreign Key)

For the City 3 Columns are there i.e. StateId ,CityId and CityName(CityId is PrimaryKey and StateId is foreign Key).

I need this type of Script with Column name.

View 7 Replies View Related

Selecting Nearby Distinct City/zipcode Issue

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

Load A Text File With Email Addresses And Compare Against A Database Table That Has Email Addresses And User_id

Jul 12, 2007

Hello ALL

what I want to achieve is to load a text file that has email addreses from disk and using the email addresses in the text file look it up against the email addresses in the database table then once matched delete all the users in the table whose email address were in the text file.

I also want to update some users using a different text file.

Please help me with the best way to do this

Thanks in advance

View 6 Replies View Related

List A City Only Once

Nov 13, 2006

Hi...I want one listbox showing cities but I dont want to list a city more than one time....

I know that DISTINCT maybe could work...But I dont get it to work correctly....

The code:

<asp:ListBox ID="ListBox1" runat="server" AutoPostBack="True" DataSourceID="DataSource1" DataTextField="City" DataValueField="City"></asp:ListBox>

<asp:SqlDataSource ID="DataSource1" runat="server"

ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT DISTINCT [City] FROM [Location]">

</asp:SqlDataSource>

I got the message:

The text data type cannot be selected as DISTINCT because it is not comparable

The database have a table called "Location" with the column "ID" (int) as primary key and the column "City" (text)

View 14 Replies View Related

Transact SQL :: Convert City Name To Upper / Lower

Jul 7, 2015

In the database, most of our cities are stored in all upper case. For reporting purposes, I need to have them returned as upper/lower. I used the below function, which works great for one word cities. However I can’t figure out how to get it to capitalize the 1st letter of each word, for addresses containing multiple names such as Rancho Santa Margarita.

Upper(left(CR_MEMBER_ALLMEMBERDETAILS.ADDRESSCITY,1))+lower(substring(CR_MEMBER_ALLMEMBERDETAILS.ADDRESSCITY, 2, LEN(CR_MEMBER_ALLMEMBERDETAILS.ADDRESSCITY)))As ADDRESSCITY

This returns back: ‘Rancho santa margarita’; I need it to return ‘Rancho Santa Margarita’. Is this possible to do at the query level?

View 10 Replies View Related

Store Procedure To Find City And Zip Code By Radius

Mar 20, 2007

Hi,

Is anyone has store procedure that find city and zipcode by radius (10 miles, 30 miles, 50 miles.......)
and also table that has all zipcodes. I finded one, but it's look like very old one, not updated since 2005

Thanks
Kate

View 1 Replies View Related

Need To Produce A Query That Show The Cities And The Number Of People Per City?

Jan 31, 2008

Please help!!!!
Need to produce a query that show the cities and the number of people per city?
simple table
firstname
lastname
age
location

location shows the dif cities and the sql query will show cities will a count of people per city

how do you do this?

View 3 Replies View Related

T-SQL (SS2K8) :: Returning Rows Within Certain Distance In Miles From City Using Longitude And Latitude

Nov 19, 2014

I've got a working query which returns all leads within a supplied proximity to a city. I followed a tutorial I googled a couple months ago (can't find it now). It works, but would love others to look the query over (provided DDL and sample data) and tell me if it's as it should be.

Two things I don't like about query:

1. I have to do a UNION to another query that retrieves everything that is in the same city in order to have complete results.
2. very slow to retrieve results (> 1 minute)

Sample DDL: 2 tables
create table dim_lead
(
date_created datetime,
[contact_first_name] varchar(20),
[contact_last_name] varchar(20),
lead_id int,

[Code] .....

View 9 Replies View Related

IP Addresses

Sep 7, 2005

I'm trying to connect to a local instance of SQL Server using an IP address and its not working. When I looked up the connect string, it shows only the IP address replacing the Server name, not the instance name, i.e. Server=127.0.0.1 - how does it know which instance to use?
Can I use the 127.0.0.1 loopback address for the server (as its on a local machine)?
Do I have to use 'Network Library = DBMSSOCN' to tell the client to use TCP/IP?
(TCP/IP is showing as an enabled protocol in the Client Network Utility)

View 6 Replies View Related

Selecting The Most Recently Edited Item AND Selecting A Certain Type If Another Doesn't Exist

Sep 20, 2007

I've got a big problem that I'm trying to figure out:
I have an address table out-of-which I am trying to select mailing addresses for companies UNLESS a mailing address doesn't exist; then I want to select the physical addresses for that company. If I get multiple mailing or physical addresses returned I only want the most recently edited out of those.

I don't need this for an individual ID select, I need it applied to every record from the table.

My address table has some columns that look like:
[AddressID] [int]
[LocationID] [int]

[Type] [nvarchar](10)
[Address] [varchar](50)
[City] [varchar](50)
[State] [char](2)
[Zip] [varchar](5)
[AddDate] [datetime]
[EditDate] [datetime]

AddressID is a primary-key non-null column to the address table and the LocationID is a foreign key value from a seperate Companies table.
So there will be multiple addresses to one LocationID, but each address will have it's own AddressID.

How can I do this efficiently with perfomance in mind???

Thank you in advance for any and all replies...

View 2 Replies View Related

Many Addresses In A Database

Mar 15, 2004

I need to allow for many addresses in my database for each client that is entered. I'm looking for suggestions on how I should handle this. I will need to do mailings to the clients and will need a way to select the address record that the client indicates as the mailing place.

Thanks for any thoughts.

View 2 Replies View Related

SQL Server 7 - IP Addresses

Feb 16, 2001

Morning all!

A quick question for you all, We will be changing the IP address of our SQL 7's server sometime next week. Does any1 know of any issues/problems that could occur if we do so?

Thanks in advance for any and all help

Gurmi

View 2 Replies View Related

Update Bad Addresses

Jan 27, 2007

I got got a pile of bad email addresses to update in our SQL database.I know how to do this for individual records using the update command.Is there a way to execute an update using the list of addresses in anexcel spreadsheet or some other form of list? This kind of scriptingis new territory for me.

View 5 Replies View Related

Replication And IP Addresses

Jan 5, 2007

I want to replicate between 2 servers not on the same network. One has to be addressed by IP. Currently replication does not support IPs - anyone have a workaround. Will this be resolved in the future? Seems this is a pretty common scenario if your db is on a hosted site.

-cg

View 1 Replies View Related

SQL Server Session State - Using A 1.1 Database Schema For 2.0 Session State Storage

Aug 3, 2006

The 2.0 version of ASPSTATE is slightly different than the 1.1 version in that one table has one additional column and another table uses a different data type and size for the key. The 2.0 version also has a couple additional stored procedures.

We'd like to manage just one session state database if possible so we're trying to figure out if Microsoft supports using the new schema for 1.1 session state access (it seems to work, but our testing has been very light).

Is there any official support line on this? If not, can anyone comment on whether or not you'd expect it to work and why?

Thanks.

View 1 Replies View Related

Registering Servers With IP Addresses

Apr 27, 2000

I hope someone can help me with this problem.
I need to replicate some tables from our store servers
to our corporate server. All of them are 6.5, and
the corporate server will subscribe and the stores
will publish. I have registered
all of our stores on the corporate server so that I can
enable them for replication.

The problem is that to be able to register them I had to create
a system DSN for ODBC, but that would only work if I used the
IP addresses. Unfortunately, then when I try to enable them
on the corporate server I get an error stating that name (which
is the IP address) is not a valid object identifier.

In ODBC I did actually use a real name (not an IP) for the
name, a normal description, and the IP is in the Server
field. Is there some way to use an alias? What am I doing wrong?

View 2 Replies View Related

Remove Duplicate Addresses For Each IDs

Mar 7, 2014

Below is a raw data with SQL script to create the temp table. This table is a scale down version of what I am working with. I am taking the addresses from several different sources and putting them in a temp table. Now, I need to remove the duplicate addresses for each ID. Duplicate addresses are okay for different IDs.

Raw Data
IDAddr1 CityStateZip
26205 N Main STChicagoIL52147
26205 N Main STChicagoIL52147
2685 Park AveChicagoIL52147
3535 Main StAustinTX78715
35976 Ponco StDallasTX79757
359587 MopacAustinTX78715
4558741 Len LnDaytonFL74717
455518 Spring DrDaytonFL74717
45585 Park AveChicagoIL52147

Desired Result
IDAddr1 CityStateZip
26205 N Main STChicagoIL52147
2685 Park AveChicagoIL52147
3535 Main StAustinTX78715
35976 Ponco StDallasTX79757
359587 MopacAustinTX78715
4558741 Len LnDaytonFL74717
455518 Spring DrDaytonFL74717
45585 Park AveChicagoIL52147

CREATE TABLE #tmpTable(
[ID] Int,
[Addr1] [varchar](15) NULL,
[City] [varchar](9) NULL,
[State] [varchar](2) NULL,

[Code] .....

View 5 Replies View Related

Email Addresses To Outlook

Mar 23, 2007

Is there a way to export a list of email addresses to outlook based on certain query conditions? So for example, lets say using pubs, I want to send out an email to a group of authors who have published only one book. So could I essentailly query for the authors and then have those corresponding authors emails sent to outlook so that I could send out a generic email asking them if they are going to write another book or something like that?

The Yak Village Idiot

View 1 Replies View Related

Changing IP Addresses Of The Cluster

Jan 18, 2008

Hello!

We will be moving our two cluster running SQL Server 2005 64-bit SP2 on Windows 2003 to the different datacenter. IP addressed of both nodes will be changed by DNS names remain the same. I was wondering if anyone had issues with this. Out thought is that we just need to update IPs in Cluster Manager.

Comments are welcome.
Igor

View 2 Replies View Related

Two Different Addresses In The Navigation Pane?

Sep 14, 2007

I have a report with a Matrix table. When it��s fully populated I have 5 rows. These rows are different categories in a CRM system. In these categories there are a lot of Opportunities listed. The thing here is that category 1-4 exist in one table, really a view, and category 5 exist in another different view. So to gather all the information into one single Matrix table to later have subtotals and stuff I just made a little Union of the two select queries.

Now one the column in the table is of course Topic. And from here my client wants to be linked to the right CRM card. The problem I have encountered is that how can I have to different addresses on one single cell in the table?

In the Navigation pane in the cell that corresponds to the Topic, for now I have an address like this:
=string.Format("http://{0}/SFA/opps/edit.aspx?id={1}", Parameters!srv.Value, Fields!objectid.Value)

What I need to change when it��s a category 5 Opportunity is ��opps�? to ��cat5�?and add ��&etc=10008�? to the end of the address. Any ideas?

PS. {0} and {1} are parameters, one server parameter and one opportunity ID parameter.

Kind Regards

View 3 Replies View Related

Remote Connections From Specified Ip Addresses

Oct 18, 2007

Hi,

Is there any way to ony allow remote access to an instance of SQL Server 2005 from a specific set of ip adresses? (and How To..)

My problem is i only want certain machines to access the server. Any other suggestions to achive this are most welcome.

Thanks for your time

Leroy

View 4 Replies View Related

Select Bad Email Addresses From Table

May 31, 2006

Hi all,
I'm having a bit of an issue with this query. I'm not that familiar with MS SQL so please forgive me.
I'm trying to select the bad email addresses in a table so I can remove them or correct them but I'm not getting any results that are helpfull. Bellow are some of the queries I've tried

select email from person where email != "*@*"
returned all addresses plus null fields.

select email from person where email not like "%@%" (this didn't work at all)

And a few other that are similar
Could somebody please kick me in the right direction.

TIA

Jason

View 10 Replies View Related

Parse Out All Email Addresses In A String

Jul 23, 2013

I have a string like this one in my column

Mike@yahoo.com, Bill@aol.com, Dan@yahoo.com, Frank@gmail.com

In my result set I need to display all email addresses that do not have the @yahoo.com domain.

View 7 Replies View Related

Changing TCP/IP IP Addresses Under Protocols For SQLExpress

Jun 16, 2007

SQL Server Configuraton Manager,

Protocols for sqlexpress,

TCP/IP

When I initially installed SQL Server 2005 Express, it automatically picked up all the IP addresses (60 of them) I had on the server, and filled them in under TCP/IP - IP adresses Tab.

I recently took those IPs off the server and replaced them with 30 new IP addresses. Is there a way to have SQL Server 2005 Express read the Network configuration on the server and take the old IPs off the TCP/IP - IP addresses tab and put the new one's there?

Thanks,

Bill

View 3 Replies View Related

Locking SQL Express To Recieve FROM Certain IP Addresses?

Oct 4, 2007

Hi All,

A client of mine has an update to the software they use which requires SQL ports to be pinholed on the
router to allow direct connctions from point of sale terminals. I'm not happy with the idea but thats not my call.
Is there a way to restrict IP addresses that can connect to SQL Express ? (without havng to get them to buy
a new router)

Cheers.

Irish

View 1 Replies View Related

Select Email Addresses From Html Source

Jan 28, 2014

I have dumped html in a table and am looking to extract email addresses from it. Within the html, the email addresses are followed and preceded by blank spaces. I am playing around with the following code:

SELECT RIGHT(@email, LEN(@email) - CHARINDEX('@', @email)) as Domain

but have been unable to extract whole email addresses.

View 4 Replies View Related

TCP Port Addresses With Multiple Names Instances.

Sep 20, 2007

I have several 64 bit SQL 2005 servers that up to 4 named instances residing on them. Before going into production I need to change the port addressing to use fixed TCP ports. I have SQL Browser running to enbale me to see the named instances.

What steps are required to change to using specific TCP port addresses and is there a particular range to use ?

View 1 Replies View Related

Principal Db In Principal, Synchronizing State, The Mirror In Restoring State

Sep 26, 2007

We have mirroring setup for 5 dbs, 4 of which are synchronized and 1 which is in "synchronizing" state on the principal and "restoring" state on the mirror. Mirroring for all dbs has been working fine for the past several months and we have a witness that has allowed automatic failover in the past without problems.

This database has several bulk inserts performed throughout the day and am sure there is some latency due to the size of these transactions.

Not sure as to why this is happening all of a sudden, but the db in question has been in this state for the past 12 hrs. I checked the mirroring status on the principal and it states that it is "synchronizing: data is being transferred from principal to mirror", but the mirror server states that db is in "restoring" state. Can anyone suggest as to how I can get the database on the mirroring server to get back to "mirror, synchronizing/restoring..." state? Or suggest on how I can troubleshoot this?

Thanks in advance.

View 3 Replies View Related

Sending Emails With System.Net.Mail To Addresses From A SQL Query

Sep 13, 2007

I have the following code on a form:
<asp:SqlDataSource ID="building_a" runat="server" ConnectionString="<%$ ConnectionStrings:vol1ConnectionString4 %>"
SelectCommand="SELECT [E_MAIL] FROM [Sheet4$] WHERE ([BUILDING] LIKE '%' + @BUILDING+ '%')" OnSelecting="building_a_Selecting">
<SelectParameters>
<asp:Parameter DefaultValue="a" Name="BUILDING" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="building_b" runat="server" ConnectionString="<%$ ConnectionStrings:vol1ConnectionString4 %>"
SelectCommand="SELECT [E_MAIL] FROM [Sheet4$] WHERE ([BUILDING] LIKE '%' + @BUILDING+ '%')" OnSelecting="building_b_Selecting">
<SelectParameters>
<asp:Parameter DefaultValue="b" Name="BUILDING" Type="String" />
</SelectParameters></asp:SqlDataSource>
<asp:TextBox ID="subj" runat="server" Width="500px">Subject</asp:TextBox> 
<asp:TextBox ID="messg" runat="server" Rows="20" TextMode="MultiLine" Width="500px">Message</asp:TextBox> 
<asp:Button ID="bldga" runat="server" Text="Building A" OnClick="Button1_Click" /> <asp:Button ID="bldgb" runat="server" Text="Building B" OnClick="Button2_Click" /> 
<asp:Label ID="resultLabel1" runat="server" ForeColor="red"></asp:Label>
And I have the following code behind:protected void Button1_Click(object sender, EventArgs e)
{SmtpClient smtpClient = new SmtpClient();
MailMessage message = new MailMessage();
try
{MailAddress fromAddress = new MailAddress("me@mydomain.com", "My Name");MailAddress toAddress = new MailAddress("them@theirdomain.com");
message.From = fromAddress;
message.To.Add(toAddress);
message.Subject = subj.Text;message.IsBodyHtml = false;
message.Body = messg.Text;smtpClient.Host = "myhost";
smtpClient.Send(message);resultLabel1.Text = "Email sent!";
}catch
{resultLabel1.Text = "Send failure";
}
}
The queries get e-mail addresses from a SQL database for all people who work in a particular building.
The buttons will send an email if I give a To address.
How do I write the code so that clicking on the "Building A" button will send the e-mail to the addresses from the query results individually?
Any ideas?

View 4 Replies View Related

SQL Server Agent Notifications To Multiple (three-plus) Addresses Fail

Jul 20, 2005

We have SQL Server 2000 Standard and recently moved from Exchange 5.5to Exchange 2003. This problem occurred both before and after thee-mail change.We would like to send notifications of failed jobs to multiple peoplein case one person happens not to be in on the day there is a problem.Setting up an operator with an e-mail name that combines twoaddresses seems to work ok as long as we use a format like thefollowing:smith_john; jones_maryorJoin Bytes!; Join Bytes!However, when we add a third name, we start getting problems. Thefirst and third accounts listed get the message but the second onedoes not -- apparently because the address gets corrupted. Forexample (based on the message received at one of the good addresses),a message sent to:Join Bytes!; Join Bytes!;Join Bytes!goes to Smith and Jackson successfully but Jones' address is corruptedto:Join Bytes!omand, of course, fails.From some other testing, it appears that the last three characters ofthe first address always get appended to the end of the secondaddress.Has anyone else run across this? If so, is there a known fix? I hadhoped Exchange 2003 would fix the problem. Most likely, we will trysetting up a personal distribution list under the account from whichthe messages are sent. I just wanted to give one more shot to findingthe problem -- both because I would like to know and because beingable to string together addresses is a little easier than having to gothrough the person who can give us access to the account to set thePDL.Thanks.Jonathan LevyChicago Department of Public Health

View 1 Replies View Related