Help ! Nested Stored Procedure, is this the best way???

Hi all,
Excuse my lack of knowledge in this area but what I am trying to do is
1) The user requests that say 4 tickets are to be held
2) The VB program calls a sproc that executes and tries to update the
status of each ticket in turn
3) If 4 are available then all is dandy
4) If 4 are not then any updates are rolled back and the user notifed
Note this is a true multi user environment, the tickets don't have to
be locked in sequence, they are just places on an event
Tried this but got odd rollback messages like
Server: Msg 266, Level 16, State 2, Procedure BIP_Tickets, Line 23
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
TRANSACTION statement is missing. Previous count = 6, current count =
7.
Line 23 is the return statement
SQL 2000 is the beast in use
HELP!! Ideas
Sproc is
CREATE PROCEDURE BIP_Tickets @.UserId Integer,
@.EventId Integer,
@.NumberReqd as Integer,
@.Resp Integer OUTPUT AS
SET NOCOUNT ON
Declare @.CurrentTicket Integer
Declare @.TicketRef Integer
SET @.CurrentTicket = 1
BEGIN TRAN BIP
WHILE @.CurrentTicket < @.NumberReqd
BEGIN
IF (SELECT COUNT(*) FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event
Tickets].[Event Id] = @.EventId) < 1
ROLLBACK TRAN BIP
SET @.Resp = 1
RETURN
SET @.TicketRef = (SELECT TOP 1 [Ticket Ref]
FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event
Tickets].[Event Id] = @.EventId)
UPDATE [Event Tickets]
SET [Ticket status] = 1,
[User Id] = @.UserId,
[Status Updated] = GetDate()
WHERE [Ticket Ref] = @.TicketRef
SET @.CurrentTicket = @.CurrentTicket + 1
END
COMMIT TRAN BIP
SET @.Resp = 0
GO
"Shaun" <shaunsizen@.msn.com> wrote in message
news:4a2f9143.0411020824.24ac7479@.posting.google.c om...
> Hi all,
> Excuse my lack of knowledge in this area but what I am trying to do is
> 1) The user requests that say 4 tickets are to be held
> 2) The VB program calls a sproc that executes and tries to update the
> status of each ticket in turn
> 3) If 4 are available then all is dandy
> 4) If 4 are not then any updates are rolled back and the user notifed
> Note this is a true multi user environment, the tickets don't have to
> be locked in sequence, they are just places on an event
> Tried this but got odd rollback messages like
> Server: Msg 266, Level 16, State 2, Procedure BIP_Tickets, Line 23
> Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
> TRANSACTION statement is missing. Previous count = 6, current count =
> 7.
> Line 23 is the return statement
> SQL 2000 is the beast in use
> HELP!! Ideas
> Sproc is
>
Your immediate problem is that TSQL's IF only affects the next statement.
You wrote
IF (SELECT COUNT(*) FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event
Tickets].[Event Id] = @.EventId) < 1
ROLLBACK TRAN BIP
SET @.Resp = 1
RETURN
which is equivilent to
IF (SELECT COUNT(*) FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event Tickets].[Event
Id] = @.EventId) < 1
BEGIN
ROLLBACK TRAN BIP
END
SET @.Resp = 1
RETURN
So you are always returning at line 23.
You probably have some more concurrency problems after fixing this, so test
and post again when you get deadlocks or double bookings.
David
|||Cheers David, good point, not used the IF too much and it was always
single statement before
I'll rework, give it a go and post back if necessary
Thanks again
Shaun
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in message news:<OqHo#4PwEHA.3108@.TK2MSFTNGP14.phx.gbl>...
> "Shaun" <shaunsizen@.msn.com> wrote in message
> news:4a2f9143.0411020824.24ac7479@.posting.google.c om...
> Your immediate problem is that TSQL's IF only affects the next statement.
> You wrote
>
> IF (SELECT COUNT(*) FROM [Event Tickets]
> WHERE [Event Tickets].[Ticket Status]='0' AND [Event
> Tickets].[Event Id] = @.EventId) < 1
> ROLLBACK TRAN BIP
> SET @.Resp = 1
> RETURN
> which is equivilent to
>
> IF (SELECT COUNT(*) FROM [Event Tickets]
> WHERE [Event Tickets].[Ticket Status]='0' AND [Event Tickets].[Event
> Id] = @.EventId) < 1
> BEGIN
> ROLLBACK TRAN BIP
> END
> SET @.Resp = 1
> RETURN
> So you are always returning at line 23.
> You probably have some more concurrency problems after fixing this, so test
> and post again when you get deadlocks or double bookings.
> David

Help ! Nested Stored Procedure, is this the best way???

Hi all,
Excuse my lack of knowledge in this area but what I am trying to do is
1) The user requests that say 4 tickets are to be held
2) The VB program calls a sproc that executes and tries to update the
status of each ticket in turn
3) If 4 are available then all is dandy
4) If 4 are not then any updates are rolled back and the user notifed
Note this is a true multi user environment, the tickets don't have to
be locked in sequence, they are just places on an event
Tried this but got odd rollback messages like
Server: Msg 266, Level 16, State 2, Procedure BIP_Tickets, Line 23
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
TRANSACTION statement is missing. Previous count = 6, current count = 7.
Line 23 is the return statement
SQL 2000 is the beast in use
HELP!! Ideas :)
Sproc is
CREATE PROCEDURE BIP_Tickets @.UserId Integer,
@.EventId Integer,
@.NumberReqd as Integer,
@.Resp Integer OUTPUT AS
SET NOCOUNT ON
Declare @.CurrentTicket Integer
Declare @.TicketRef Integer
SET @.CurrentTicket = 1
BEGIN TRAN BIP
WHILE @.CurrentTicket < @.NumberReqd
BEGIN
IF (SELECT COUNT(*) FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event
Tickets].[Event Id] = @.EventId) < 1
ROLLBACK TRAN BIP
SET @.Resp = 1
RETURN
SET @.TicketRef = (SELECT TOP 1 [Ticket Ref]
FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event
Tickets].[Event Id] = @.EventId)
UPDATE [Event Tickets]
SET [Ticket status] = 1,
[User Id] = @.UserId,
[Status Updated] = GetDate()
WHERE [Ticket Ref] = @.TicketRef
SET @.CurrentTicket = @.CurrentTicket + 1
END
COMMIT TRAN BIP
SET @.Resp = 0
GO"Shaun" <shaunsizen@.msn.com> wrote in message
news:4a2f9143.0411020824.24ac7479@.posting.google.com...
> Hi all,
> Excuse my lack of knowledge in this area but what I am trying to do is
> 1) The user requests that say 4 tickets are to be held
> 2) The VB program calls a sproc that executes and tries to update the
> status of each ticket in turn
> 3) If 4 are available then all is dandy
> 4) If 4 are not then any updates are rolled back and the user notifed
> Note this is a true multi user environment, the tickets don't have to
> be locked in sequence, they are just places on an event
> Tried this but got odd rollback messages like
> Server: Msg 266, Level 16, State 2, Procedure BIP_Tickets, Line 23
> Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
> TRANSACTION statement is missing. Previous count = 6, current count => 7.
> Line 23 is the return statement
> SQL 2000 is the beast in use
> HELP!! Ideas :)
> Sproc is
>
Your immediate problem is that TSQL's IF only affects the next statement.
You wrote
IF (SELECT COUNT(*) FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event
Tickets].[Event Id] = @.EventId) < 1
ROLLBACK TRAN BIP
SET @.Resp = 1
RETURN
which is equivilent to
IF (SELECT COUNT(*) FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event Tickets].[Event
Id] = @.EventId) < 1
BEGIN
ROLLBACK TRAN BIP
END
SET @.Resp = 1
RETURN
So you are always returning at line 23.
You probably have some more concurrency problems after fixing this, so test
and post again when you get deadlocks or double bookings.
David|||Cheers David, good point, not used the IF too much and it was always
single statement before
I'll rework, give it a go and post back if necessary :)
Thanks again
Shaun
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in message news:<OqHo#4PwEHA.3108@.TK2MSFTNGP14.phx.gbl>...
> "Shaun" <shaunsizen@.msn.com> wrote in message
> news:4a2f9143.0411020824.24ac7479@.posting.google.com...
> > Hi all,
> > Excuse my lack of knowledge in this area but what I am trying to do is
> >
> > 1) The user requests that say 4 tickets are to be held
> > 2) The VB program calls a sproc that executes and tries to update the
> > status of each ticket in turn
> > 3) If 4 are available then all is dandy
> > 4) If 4 are not then any updates are rolled back and the user notifed
> >
> > Note this is a true multi user environment, the tickets don't have to
> > be locked in sequence, they are just places on an event
> >
> > Tried this but got odd rollback messages like
> > Server: Msg 266, Level 16, State 2, Procedure BIP_Tickets, Line 23
> > Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
> > TRANSACTION statement is missing. Previous count = 6, current count => > 7.
> >
> > Line 23 is the return statement
> >
> > SQL 2000 is the beast in use
> >
> > HELP!! Ideas :)
> >
> > Sproc is
> >
> >
> Your immediate problem is that TSQL's IF only affects the next statement.
> You wrote
>
> IF (SELECT COUNT(*) FROM [Event Tickets]
> WHERE [Event Tickets].[Ticket Status]='0' AND [Event
> Tickets].[Event Id] = @.EventId) < 1
> ROLLBACK TRAN BIP
> SET @.Resp = 1
> RETURN
> which is equivilent to
>
> IF (SELECT COUNT(*) FROM [Event Tickets]
> WHERE [Event Tickets].[Ticket Status]='0' AND [Event Tickets].[Event
> Id] = @.EventId) < 1
> BEGIN
> ROLLBACK TRAN BIP
> END
> SET @.Resp = 1
> RETURN
> So you are always returning at line 23.
> You probably have some more concurrency problems after fixing this, so test
> and post again when you get deadlocks or double bookings.
> David

Help ! Nested Stored Procedure, is this the best way???

Hi all,
Excuse my lack of knowledge in this area but what I am trying to do is
1) The user requests that say 4 tickets are to be held
2) The VB program calls a sproc that executes and tries to update the
status of each ticket in turn
3) If 4 are available then all is dandy
4) If 4 are not then any updates are rolled back and the user notifed
Note this is a true multi user environment, the tickets don't have to
be locked in sequence, they are just places on an event
Tried this but got odd rollback messages like
Server: Msg 266, Level 16, State 2, Procedure BIP_Tickets, Line 23
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
TRANSACTION statement is missing. Previous count = 6, current count =
7.
Line 23 is the return statement
SQL 2000 is the beast in use
HELP!! Ideas
Sproc is
CREATE PROCEDURE BIP_Tickets @.UserId Integer,
@.EventId Integer,
@.NumberReqd as Integer,
@.Resp Integer OUTPUT AS
SET NOCOUNT ON
Declare @.CurrentTicket Integer
Declare @.TicketRef Integer
SET @.CurrentTicket = 1
BEGIN TRAN BIP
WHILE @.CurrentTicket < @.NumberReqd
BEGIN
IF (SELECT COUNT(*) FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event
Tickets].[Event Id] = @.EventId) < 1
ROLLBACK TRAN BIP
SET @.Resp = 1
RETURN
SET @.TicketRef = (SELECT TOP 1 [Ticket Ref]
FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event
Tickets].[Event Id] = @.EventId)
UPDATE [Event Tickets]
SET [Ticket status] = 1,
[User Id] = @.UserId,
[Status Updated] = GetDate()
WHERE [Ticket Ref] = @.TicketRef
SET @.CurrentTicket = @.CurrentTicket + 1
END
COMMIT TRAN BIP
SET @.Resp = 0
GO"Shaun" <shaunsizen@.msn.com> wrote in message
news:4a2f9143.0411020824.24ac7479@.posting.google.com...
> Hi all,
> Excuse my lack of knowledge in this area but what I am trying to do is
> 1) The user requests that say 4 tickets are to be held
> 2) The VB program calls a sproc that executes and tries to update the
> status of each ticket in turn
> 3) If 4 are available then all is dandy
> 4) If 4 are not then any updates are rolled back and the user notifed
> Note this is a true multi user environment, the tickets don't have to
> be locked in sequence, they are just places on an event
> Tried this but got odd rollback messages like
> Server: Msg 266, Level 16, State 2, Procedure BIP_Tickets, Line 23
> Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
> TRANSACTION statement is missing. Previous count = 6, current count =
> 7.
> Line 23 is the return statement
> SQL 2000 is the beast in use
> HELP!! Ideas
> Sproc is
>
Your immediate problem is that TSQL's IF only affects the next statement.
You wrote
IF (SELECT COUNT(*) FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event
Tickets].[Event Id] = @.EventId) < 1
ROLLBACK TRAN BIP
SET @.Resp = 1
RETURN
which is equivilent to
IF (SELECT COUNT(*) FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event Tickets].&#
91;Event
Id] = @.EventId) < 1
BEGIN
ROLLBACK TRAN BIP
END
SET @.Resp = 1
RETURN
So you are always returning at line 23.
You probably have some more concurrency problems after fixing this, so test
and post again when you get deadlocks or double bookings.
David|||Cheers David, good point, not used the IF too much and it was always
single statement before
I'll rework, give it a go and post back if necessary
Thanks again
Shaun
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in message news:<OqHo#4P
wEHA.3108@.TK2MSFTNGP14.phx.gbl>...
> "Shaun" <shaunsizen@.msn.com> wrote in message
> news:4a2f9143.0411020824.24ac7479@.posting.google.com...
> Your immediate problem is that TSQL's IF only affects the next statement.
> You wrote
>
> IF (SELECT COUNT(*) FROM [Event Tickets]
> WHERE [Event Tickets].[Ticket Status]='0' AND [Event
> Tickets].[Event Id] = @.EventId) < 1
> ROLLBACK TRAN BIP
> SET @.Resp = 1
> RETURN
> which is equivilent to
>
> IF (SELECT COUNT(*) FROM [Event Tickets]
> WHERE [Event Tickets].[Ticket Status]='0' AND [Event Tick
ets].[Event
> Id] = @.EventId) < 1
> BEGIN
> ROLLBACK TRAN BIP
> END
> SET @.Resp = 1
> RETURN
> So you are always returning at line 23.
> You probably have some more concurrency problems after fixing this, so tes
t
> and post again when you get deadlocks or double bookings.
> David

HELP ! - How can a run a sql script to install a DB in a MS SQL Server?

Hello everyone,
I am new in the area of developing and I want to run a couple of sql scripts, what is the procedure to run them if I am using a MS SQL Server?
Thanks,
ArmandoUse Query Analyzer.

Check out the Books On Line.

Cheers,

hmscott

HELP - Absurd project on a deadline!

Hi,
Someone from our headquarters wants a report of how many customers per
"Metropolitation Statistical Area" (MSA) we have in our database. See
http://www.census.gov/population/estimates/metro_general/List4.txt for
the complete list of MSA's.
Problem is, each MSA only lists one, two or three cites per area. For
example, "Boston-Cambridge-Quincy". Which leaves out EVERY other city
in that area, such as Roxbury, etc. (However, these four cities all
fall into zip codes either starting with 021 or 022).
So, doing a search by MSA city is impossible. It would miss a lot of
customers whose cities aren't listed in a given MSA, for example,
Roxbury.
Alternatively, I could use the first few digits of the zip codes. For
example, Boston zip codes start with 021 or 022. That would capture all
other cities within that area, I am thinking, or close enough.
However, the MSA website does not have any zip codes listed.
And futhermore, I am the one who has to find these zip codes... seems
like it's going to be very aggravating, long day having to look up
these zip codes.
Please, please please someone tell me there is a listing of zip codes
per MSA... or has ANYONE had to do a similar project - and can give me
some advice?
THANKS
A quick GOOGLE with the words MSA ZIP turned up commercial databases
of zip data that includes the MSA code. The price for the one I
looked at was something like $80, and it is a download so you can
don't have to wait. I know nothing other than that I found it just
now, but $80 seems pretty reasonable. The link to the page I found,
to which I have no connection, is
http://www.zip-codes.com/zip-code-database.asp
Roy Harvey
Beacon Falls, CT
On 13 Dec 2006 11:53:10 -0800, tootsuite@.gmail.com wrote:

>Hi,
>Someone from our headquarters wants a report of how many customers per
>"Metropolitation Statistical Area" (MSA) we have in our database. See
>http://www.census.gov/population/estimates/metro_general/List4.txt for
>the complete list of MSA's.
>Problem is, each MSA only lists one, two or three cites per area. For
>example, "Boston-Cambridge-Quincy". Which leaves out EVERY other city
>in that area, such as Roxbury, etc. (However, these four cities all
>fall into zip codes either starting with 021 or 022).
>So, doing a search by MSA city is impossible. It would miss a lot of
>customers whose cities aren't listed in a given MSA, for example,
>Roxbury.
>Alternatively, I could use the first few digits of the zip codes. For
>example, Boston zip codes start with 021 or 022. That would capture all
>other cities within that area, I am thinking, or close enough.
>However, the MSA website does not have any zip codes listed.
>And futhermore, I am the one who has to find these zip codes... seems
>like it's going to be very aggravating, long day having to look up
>these zip codes.
>Please, please please someone tell me there is a listing of zip codes
>per MSA... or has ANYONE had to do a similar project - and can give me
>some advice?
>THANKS
|||> And futhermore, I am the one who has to find these zip codes...
No you don't, as long as you have the ability to spend money on software
and/or a database that does it for you.
We're using one of MaxMind's databases for exactly this purpose: mapping
DMAs to zip codes and then reporting aggregates under each DMA. They have
free versions of the database too (just not as current / complete) and I
think they support web service calls for some of it too (but our volume
couldn't be supported by that).
It sounds like you need MSA and not DMA, though I confess I am not clear on
the differences. So, assuming DMA is not appropriate, some other
alternatives, some of which are free:
http://www.zipdatafiles.com/data/
http://www.zipinfo.com/products/products.htm
http://www.zipcodeworld.com/zipcodepremium.htm
http://www.freedownloadmanager.org/downloads/latitude_longitude_software/
http://www.softjamboree.com/article/zip-code-database.html
http://www.zip-codes.com/zip-code-database.asp
One of these will hopefully meet your requirements and budget.
A
|||You could also determine approximate ZIP code to MSA mappings using the U.S.
Census Bureau's free Tiger database online. Although the ZIP codes in the
Tiger database are actually "ZIP Code Tabulation Areas" and not true ZIP
codes. This inaccuracy and the amount of work involved in downloading and
cleaning up the data would make me recommend buying a commercial product.

HELP - Absurd project on a deadline!

Hi,
Someone from our headquarters wants a report of how many customers per
"Metropolitation Statistical Area" (MSA) we have in our database. See
http://www.census.gov/population/estimates/metro_general/List4.txt for
the complete list of MSA's.
Problem is, each MSA only lists one, two or three cites per area. For
example, "Boston-Cambridge-Quincy". Which leaves out EVERY other city
in that area, such as Roxbury, etc. (However, these four cities all
fall into zip codes either starting with 021 or 022).
So, doing a search by MSA city is impossible. It would miss a lot of
customers whose cities aren't listed in a given MSA, for example,
Roxbury.
Alternatively, I could use the first few digits of the zip codes. For
example, Boston zip codes start with 021 or 022. That would capture all
other cities within that area, I am thinking, or close enough.
However, the MSA website does not have any zip codes listed.
And futhermore, I am the one who has to find these zip codes... seems
like it's going to be very aggravating, long day having to look up
these zip codes.
Please, please please someone tell me there is a listing of zip codes
per MSA... or has ANYONE had to do a similar project - and can give me
some advice?
THANKSA quick GOOGLE with the words MSA ZIP turned up commercial databases
of zip data that includes the MSA code. The price for the one I
looked at was something like $80, and it is a download so you can
don't have to wait. I know nothing other than that I found it just
now, but $80 seems pretty reasonable. The link to the page I found,
to which I have no connection, is
http://www.zip-codes.com/zip-code-database.asp
Roy Harvey
Beacon Falls, CT
On 13 Dec 2006 11:53:10 -0800, tootsuite@.gmail.com wrote:
>Hi,
>Someone from our headquarters wants a report of how many customers per
>"Metropolitation Statistical Area" (MSA) we have in our database. See
>http://www.census.gov/population/estimates/metro_general/List4.txt for
>the complete list of MSA's.
>Problem is, each MSA only lists one, two or three cites per area. For
>example, "Boston-Cambridge-Quincy". Which leaves out EVERY other city
>in that area, such as Roxbury, etc. (However, these four cities all
>fall into zip codes either starting with 021 or 022).
>So, doing a search by MSA city is impossible. It would miss a lot of
>customers whose cities aren't listed in a given MSA, for example,
>Roxbury.
>Alternatively, I could use the first few digits of the zip codes. For
>example, Boston zip codes start with 021 or 022. That would capture all
>other cities within that area, I am thinking, or close enough.
>However, the MSA website does not have any zip codes listed.
>And futhermore, I am the one who has to find these zip codes... seems
>like it's going to be very aggravating, long day having to look up
>these zip codes.
>Please, please please someone tell me there is a listing of zip codes
>per MSA... or has ANYONE had to do a similar project - and can give me
>some advice?
>THANKS|||> And futhermore, I am the one who has to find these zip codes...
No you don't, as long as you have the ability to spend money on software
and/or a database that does it for you.
We're using one of MaxMind's databases for exactly this purpose: mapping
DMAs to zip codes and then reporting aggregates under each DMA. They have
free versions of the database too (just not as current / complete) and I
think they support web service calls for some of it too (but our volume
couldn't be supported by that).
It sounds like you need MSA and not DMA, though I confess I am not clear on
the differences. So, assuming DMA is not appropriate, some other
alternatives, some of which are free:
http://www.zipdatafiles.com/data/
http://www.zipinfo.com/products/products.htm
http://www.zipcodeworld.com/zipcodepremium.htm
http://www.freedownloadmanager.org/downloads/latitude_longitude_software/
http://www.softjamboree.com/article/zip-code-database.html
http://www.zip-codes.com/zip-code-database.asp
One of these will hopefully meet your requirements and budget.
A|||You could also determine approximate ZIP code to MSA mappings using the U.S.
Census Bureau's free Tiger database online. Although the ZIP codes in the
Tiger database are actually "ZIP Code Tabulation Areas" and not true ZIP
codes. This inaccuracy and the amount of work involved in downloading and
cleaning up the data would make me recommend buying a commercial product.

HELP - Absurd project on a deadline!

Hi,
Someone from our headquarters wants a report of how many customers per
"Metropolitation Statistical Area" (MSA) we have in our database. See
http://www.census.gov/population/es...neral/List4.txt for
the complete list of MSA's.
Problem is, each MSA only lists one, two or three cites per area. For
example, "Boston-Cambridge-Quincy". Which leaves out EVERY other city
in that area, such as Roxbury, etc. (However, these four cities all
fall into zip codes either starting with 021 or 022).
So, doing a search by MSA city is impossible. It would miss a lot of
customers whose cities aren't listed in a given MSA, for example,
Roxbury.
Alternatively, I could use the first few digits of the zip codes. For
example, Boston zip codes start with 021 or 022. That would capture all
other cities within that area, I am thinking, or close enough.
However, the MSA website does not have any zip codes listed.
And futhermore, I am the one who has to find these zip codes... seems
like it's going to be very aggravating, long day having to look up
these zip codes.
Please, please please someone tell me there is a listing of zip codes
per MSA... or has ANYONE had to do a similar project - and can give me
some advice?
THANKSA quick GOOGLE with the words MSA ZIP turned up commercial databases
of zip data that includes the MSA code. The price for the one I
looked at was something like $80, and it is a download so you can
don't have to wait. I know nothing other than that I found it just
now, but $80 seems pretty reasonable. The link to the page I found,
to which I have no connection, is
http://www.zip-codes.com/zip-code-database.asp
Roy Harvey
Beacon Falls, CT
On 13 Dec 2006 11:53:10 -0800, tootsuite@.gmail.com wrote:

>Hi,
>Someone from our headquarters wants a report of how many customers per
>"Metropolitation Statistical Area" (MSA) we have in our database. See
>http://www.census.gov/population/es...neral/List4.txt for
>the complete list of MSA's.
>Problem is, each MSA only lists one, two or three cites per area. For
>example, "Boston-Cambridge-Quincy". Which leaves out EVERY other city
>in that area, such as Roxbury, etc. (However, these four cities all
>fall into zip codes either starting with 021 or 022).
>So, doing a search by MSA city is impossible. It would miss a lot of
>customers whose cities aren't listed in a given MSA, for example,
>Roxbury.
>Alternatively, I could use the first few digits of the zip codes. For
>example, Boston zip codes start with 021 or 022. That would capture all
>other cities within that area, I am thinking, or close enough.
>However, the MSA website does not have any zip codes listed.
>And futhermore, I am the one who has to find these zip codes... seems
>like it's going to be very aggravating, long day having to look up
>these zip codes.
>Please, please please someone tell me there is a listing of zip codes
>per MSA... or has ANYONE had to do a similar project - and can give me
>some advice?
>THANKS|||> And futhermore, I am the one who has to find these zip codes...
No you don't, as long as you have the ability to spend money on software
and/or a database that does it for you.
We're using one of MaxMind's databases for exactly this purpose: mapping
DMAs to zip codes and then reporting aggregates under each DMA. They have
free versions of the database too (just not as current / complete) and I
think they support web service calls for some of it too (but our volume
couldn't be supported by that).
It sounds like you need MSA and not DMA, though I confess I am not clear on
the differences. So, assuming DMA is not appropriate, some other
alternatives, some of which are free:
http://www.zipdatafiles.com/data/
http://www.zipinfo.com/products/products.htm
http://www.zipcodeworld.com/zipcodepremium.htm
http://www.freedownloadmanager.org/...itude_software/
http://www.softjamboree.com/article...e-database.html
http://www.zip-codes.com/zip-code-database.asp
One of these will hopefully meet your requirements and budget.
A|||You could also determine approximate ZIP code to MSA mappings using the U.S.
Census Bureau's free Tiger database online. Although the ZIP codes in the
Tiger database are actually "ZIP Code Tabulation Areas" and not true ZIP
codes. This inaccuracy and the amount of work involved in downloading and
cleaning up the data would make me recommend buying a commercial product.