Help if possible

I have very limited skill in Access, but am working on a project for work. I have a database file that includes fields like item number and ticket number. There are currently identical records for both of these fields. I'm trying to create a query that dislplays a specific item only if it has the same ticket number as any other item.

I'm not sure if I explained this the best, but any help would be greatly appreciated.select t1.itemnumber
, t1.ticketnumber
from yourtable as t1
inner
join yourtable as t2
on t1.ticketnumber
= t2.ticketnumber
and t1.itemnumber
<> t2.itemnumber|||I tried that code and it didn't work right, but maybe it's because I forgot to explain something in full detail. I'm looking to display the ticket number in which an itemnum 8999 was sold, but another itemnum was sold on that same ticket.|||select t2.itemnumber
, t1.ticketnumber
from yourtable as t1
inner
join yourtable as t2
on t1.ticketnumber
= t2.ticketnumber
and t1.itemnumber
<> t2.itemnumber
where t1.itemnumber = 8999sql

Help has anyone come across a way to automatic db creation.

Hi I've been given a project that requires me to produce a users friendly web base administarion menu that creates a new customer for one of our systems.

The problem is this Project it needs to create a database for each user, in SQL server 2000. This has me stumpted, has anybody ever done this before and could they point me in the correct direction! The majority of our administration menu are written in ASP but any help automating this procedure would be useful.

Thanks a lot, EdYou can create a database simply using the T-SQL command

CREATE DATABASE

that can be invoked as any other command from your client.

You can also use it in a Stored Procedure.|||Originally posted by manowar
You can create a database simply using the T-SQL command

CREATE DATABASE

that can be invoked as any other command from your client.

You can also use it in a Stored Procedure.

Excellent, thanks

Help for thin SQL Server

Hi,everyone
I am a newbie to the SQL server.
In my project,I need a SQL server in minimal size to meet the XPE
environment.
How can I get the goal mentioned above?
Where can I find the technical articles or files needed for building minimal
SQL?
Any hints are appreciated!
Best regards!
MerryMerry.Win wrote:
> Hi,everyone
> I am a newbie to the SQL server.
> In my project,I need a SQL server in minimal size to meet the XPE
> environment.
> How can I get the goal mentioned above?
> Where can I find the technical articles or files needed for building minimal
> SQL?
> Any hints are appreciated!
> Best regards!
> Merry`
A good place to start is the SQL Server Home Page:
http://www.microsoft.com/sql/default.mspx
Check out SQL Server Mobile:
http://www.microsoft.com/sql/editions/sqlmobile/default.mspx
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Help for thin SQL Server

Hi,everyone
I am a newbie to the SQL server.
In my project,I need a SQL server in minimal size to meet the XPE
environment.
How can I get the goal mentioned above?
Where can I find the technical articles or files needed for building minimal
SQL?
Any hints are appreciated!
Best regards!
MerryMerry.Win wrote:
> Hi,everyone
> I am a newbie to the SQL server.
> In my project,I need a SQL server in minimal size to meet the XPE
> environment.
> How can I get the goal mentioned above?
> Where can I find the technical articles or files needed for building minim
al
> SQL?
> Any hints are appreciated!
> Best regards!
> Merry`
A good place to start is the SQL Server Home Page:
http://www.microsoft.com/sql/default.mspx
Check out SQL Server Mobile:
http://www.microsoft.com/sql/editio...le/default.mspx
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Help for Newbie: How to create an autonumber

Hi guys/gals. An intro, I've just pick up some database basic skills and currently doing a simple project.

I really need advise on how do I create a field that can generate autonumbering for eg. scenario

There are 3 types of service_id like CTXXXX, GPXXXX, and STXXXX (where XXXX are running numbers). What I need is to have the XXXX auto generate and running. Also if I were to delete an old record for example CT0033, the latest number eg. CT1111 will not be changed to cover up for the missing CT0033.

I apologize if the description is a bit improper, but I'm new to this. Really appreciate anyone that can help. Thanks!! :oI'd suggest the IDENTITY property when you create the table. You'll have to "decorate" the resulting number with the letters you need, but that's trivial.

-PatP|||my money's on access

and what do you mean by basic skills?

can you spell dba?

Is this homework?|||Sarcasm, Brett? How unlike you!

NOT!

:)

If all three of these services are stored in the same table, then you will run into difficulties with IDENTITY unless you don't mind each service being numbered consecutively. IDENTITY will autonumber for the entire table, but not for individual services within the table.

Your best bet is to store the last ID number used for each service in a separate table, and then use a trigger on your data table that looks up the last code used, increments it by 1, and creates the new services codes.

But this is not simple SQL.

An alternative would be to denormalize your data and store the services in separate tables each with their own IDENTITY property, but this is frowned upon for many good reasons.

Actually, your best course of action would be to drop the idea of creating these codes in the first place, since such pseudo-surrogate keys violate several principles of database application design. That is why they are difficult to code.

Help for database schema

Hello All,

My project uses MS SQL server database and is not too big database (have aound 200 table).

Now I have to create Database schema for my database as my project needs to be integrated with some other product.

I don't know much about database schema and how to start with it.

Can someone please give me some inputs on following:

1) What exactly database schema should include?

2) How should I start creating the schema for my database?

3) Are there any tools for doing this?

Thanks in advance1) CREATE TABLE statements, plus INSERT statements
2) Enterprise Manager will script the CREATE TABLE statements (but not the INSERT statements, i'm afraid)
3) tons of 'em -- do a search for SQL Server admin tools|||if your database is already built and you are looking to reverse engineer it, check out the link in my sig.|||My project uses MS SQL server database and is not too big database (have aound 200 table).I've never seen a database with 200 tables that needed 200 tables. It is indicative of poor design.|||I've never seen a database with 200 tables that needed 200 tables. It is indicative of poor design.That's a bit absolute isn't it?

EDIT - misinterpreted indicative on my first pass.|||Well, he states my project, and a one mans project with 200 tables? Well that clearly indicates poor design.|||Over egging it a bit aren't you? Do you even know what he is modelling?

BTW - I doubt (but do not know) that this has been his project from inception. I suspect it is purchased\ inherited. But as I say I don't know so I won't go so far as to say it is "clearly indicated".|||here's a project i worked on (as the sole data modeller) for an insurance company

i'm happy to report that it came in at just under 200 tables

thank $deity, eh, because i know now that as soon as it goes over the 200 table limit, it's b0rked|||Yeah, I know about data modelling. And I do know that a project with some 200 tables is quite complex, and I do think that more than one person should be involved in the data modelling. In smaller projects, one man can do the whole job, but in projects of the size we are talking about here, I would definitely not be comfortable as the only modeller. With so high complexity i feel pretty safe when I state that the project should involve at least two persons working as a team. One single man increases the chance of some issues being overlooked.

Edit: As I understand the word indicates, it does not mean it has to be that way, just that it's very likely. If you would use a different word for this, please let me know, as English is not my native language :)|||That's a bit absolute isn't it?
I have absolutely never seen a 200 table database that was a good design. I'm merely stating a fact.|||Thanks for all your comments.

Ya i do agree that its a poor design.

Anyways I have created the schema for the DB.

Thanks.|||Ya i do agree that its a poor design.

but how and why do u all of a sudden agree that the design was poor?|||Well, he has the advantage of being able to look at it.sql

Help developing this project.

Hi,
I'm about to start working on a new project and I would like to have
some ideas from you guys. I already got an idea how to do it, but I'm
not sure how good it is.
This is the problem:
We have about 20 tables with data. We have Persons, Addresses, Vehicles,
Properties, Phones...
The problem that we have is they want to have everything related (many
to many) For example, we may have a Person related with an address, or a
Vehicle with an address, a property with 2 persons... any kind of
combination that you may think.
How to design that? First I thought in having 20 tables and creating one
link table between every two types. As you can see, that wouldn't be a
good solution, because we would have too many of these intermediate tables.
Then I thought that maybe I could have only 1 table with the data of the
20 types and have an extra field that tell us what type it is (if it is
a Person, an Address, ... )
The table would be like this:
Table A
--
PK (autoincrement)
RecordType (from 1 to 20)
Custom1
Custom2
Custom3
Custom4
...
Once I have that, I would add an extra table that give us the relations
between 2 records of Table A. Something Like this:
Table B
--
FK_1 (Foreign key to a record in Table A)
FK_2 (Foreign key to a record in Table A)
What do you guys think? I know it's not really nice but I cannot find a
better solution for that.
Thanks a lot!No - do not have one table for everything - this is very bad. Might as
well use text files to store your data, because you wouldn't need a
relational database.
The correct solution is your first instinct : linking tables that link
one entity to one other entity.
I suspect that everything doesn't truly relate to everything else in a
many-many relation. (e.g., Addresses-Properties).
Once you determine the actual many-many relations, why would having it
done right result in "too many tables?" [sounds like the emporer in
"Amadeus" telling Mozart his music has "too many notes"]
Star wrote:
> Hi,
> I'm about to start working on a new project and I would like to have
> some ideas from you guys. I already got an idea how to do it, but I'm
> not sure how good it is.
> This is the problem:
> We have about 20 tables with data. We have Persons, Addresses, Vehicles,
> Properties, Phones...
> The problem that we have is they want to have everything related (many
> to many) For example, we may have a Person related with an address, or a
> Vehicle with an address, a property with 2 persons... any kind of
> combination that you may think.
> How to design that? First I thought in having 20 tables and creating one
> link table between every two types. As you can see, that wouldn't be a
> good solution, because we would have too many of these intermediate tables
.
> Then I thought that maybe I could have only 1 table with the data of the
> 20 types and have an extra field that tell us what type it is (if it is
> a Person, an Address, ... )
> The table would be like this:
> Table A
> --
> PK (autoincrement)
> RecordType (from 1 to 20)
> Custom1
> Custom2
> Custom3
> Custom4
> ...
> Once I have that, I would add an extra table that give us the relations
> between 2 records of Table A. Something Like this:
> Table B
> --
> FK_1 (Foreign key to a record in Table A)
> FK_2 (Foreign key to a record in Table A)
>
> What do you guys think? I know it's not really nice but I cannot find a
> better solution for that.
> Thanks a lot!
>|||Star wrote:
> Hi,
> I'm about to start working on a new project and I would like to have
> some ideas from you guys. I already got an idea how to do it, but I'm
> not sure how good it is.
> This is the problem:
> We have about 20 tables with data. We have Persons, Addresses, Vehicles,
> Properties, Phones...
> The problem that we have is they want to have everything related (many
> to many) For example, we may have a Person related with an address, or a
> Vehicle with an address, a property with 2 persons... any kind of
> combination that you may think.
> How to design that? First I thought in having 20 tables and creating one
> link table between every two types. As you can see, that wouldn't be a
> good solution, because we would have too many of these intermediate tables
.
>
Not too many. Just as many as are needed - no more and no less. I
suspect it isn't as complicated as "they" think Probably the customer
is just being lazy about the specs - which is normal - but it's the
developer / analyst or PM's job to deal with that and to elicit proper
specs from the users or sponsors.
Take a look at:
http://www.intelligententerprise.com/010101/celko.jhtml
David Portas
SQL Server MVP
--|||"Star" <star@.nospam.com> wrote in message
news:%23RPA0n29FHA.2816@.tk2msftngp13.phx.gbl...
> Hi,
> I'm about to start working on a new project and I would like to have some
> ideas from you guys. I already got an idea how to do it, but I'm not sure
> how good it is.
> This is the problem:
> We have about 20 tables with data. We have Persons, Addresses, Vehicles,
> Properties, Phones...
> The problem that we have is they want to have everything related (many to
> many) For example, we may have a Person related with an address, or a
> Vehicle with an address, a property with 2 persons... any kind of
> combination that you may think.
> How to design that? First I thought in having 20 tables and creating one
> link table between every two types. As you can see, that wouldn't be a
> good solution, because we would have too many of these intermediate
> tables.
> Then I thought that maybe I could have only 1 table with the data of the
> 20 types and have an extra field that tell us what type it is (if it is a
> Person, an Address, ... )
> The table would be like this:
> Table A
> --
> PK (autoincrement)
> RecordType (from 1 to 20)
> Custom1
> Custom2
> Custom3
> Custom4
> ...
> Once I have that, I would add an extra table that give us the relations
> between 2 records of Table A. Something Like this:
> Table B
> --
> FK_1 (Foreign key to a record in Table A)
> FK_2 (Foreign key to a record in Table A)
>
> What do you guys think? I know it's not really nice but I cannot find a
> better solution for that.
> Thanks a lot!
I agree with Trey on this one.
One thing that you mentionned "or a Vehicle with an address".
It sounds silly to me asking "what's the address of the vehicule".
The vehicle should be linked to a person.
(I'm not sure if the vehicle can be owned or registered by more than one
person.)
Once you have the owner, it's easy to find the address or addresses.|||Hey folks,
Thanks for you answers.

> It sounds silly to me asking "what's the address of the vehicule".
Unfortunately not on this system. This systems tracks all kind of
information. Let's say that for example there was a robbery and the
vehicle was found at a certain location. That's why we may have all kind
of weird relationships.
I agree with you guys in having a relationship table for each pair that
we may think that are going to be related, but as I said our users may
have at any moment the need of adding a relation between two very
different kind of data.
If we have one relationship table for each pair, we would have 380
(19*20) relationship tables, and I don't think that would be a good idea.
I will try to convince my supervisors of only having the relationships
that we really know that are going to exist (that's what I have always
wanted), but that's not going to be easy.
By the way, I thought about another solution. Not very good either, but
I would like to have your opinion.
Here it is:
We do have the 20 tables. Persons, Addresses, ...
Each one of these tables look like this:
Table X
--
PK (autoincrement)
[Rest of the fields]
We assign an ID to each one of these tables. For example, for Persons is
1, for Addresses is 2 and so on
Now we have only 1 relationship table and it looks like this:
Table Relations
--
SourceID
FK_Source
DestinationID
FK_Dest
[other fields]
The PK of this table is (SourceID,FK_Source,DestinationID,FK_Des
t)
SourceID and DestinationID are going to have the ID of one of the 20
tables respectively. FK_Source and FK_Dest are the PKs of one of the 20
tables respectively
So if we have in that Relations table something like (1,32123,2,543543)
We know that we have a relation between a person and a address. The PK
of this person is 32123 and the PK of the address is 543543.
The relation between 2 tables changes depending on the type of data.
Not really a relational database...
What do you guys think?
Thanks a lot!!

Raymond D'Anjou wrote:
> "Star" <star@.nospam.com> wrote in message
> news:%23RPA0n29FHA.2816@.tk2msftngp13.phx.gbl...
>
>
> I agree with Trey on this one.
> One thing that you mentionned "or a Vehicle with an address".
> It sounds silly to me asking "what's the address of the vehicule".
> The vehicle should be linked to a person.
> (I'm not sure if the vehicle can be owned or registered by more than one
> person.)
> Once you have the owner, it's easy to find the address or addresses.
>|||"Star" <star@.nospam.com> wrote in message
news:eVN1el39FHA.2184@.TK2MSFTNGP10.phx.gbl...

> If we have one relationship table for each pair, we would have 380 (19*20)
> relationship tables, and I don't think that would be a good idea.
I've developed databases with more tables than that. The limit in SQL Server
is more than 2 billion objects.
An alternative may be the classic "type/supertype" model. See the following
example. Notice that the keys ensure each entity is of exactly one type.
This can get much more sophisticated by adding the entity_types to the
joining table (entity_references) so that you can enforce rules allowing
only certain combinations of references. Also you may want to add a column
to determine what kind of reference is defined in each case.
CREATE TABLE entities (entity_code INTEGER NOT NULL PRIMARY KEY, entity_type
CHAR(2) NOT NULL CHECK (entity_type IN ('AD','VH','PR') /*
Address/Vehicle/Person */), UNIQUE (entity_code, entity_type)) ;
CREATE TABLE entity_references (entity_code_1 INTEGER NOT NULL REFERENCES
entities (entity_code), entity_code_2 INTEGER NOT NULL REFERENCES entities
(entity_code)) ;
CREATE TABLE addresses (entity_code INTEGER NOT NULL, entity_type CHAR(2)
NOT NULL DEFAULT 'AD' CHECK (entity_type = 'AD'), FOREIGN KEY (entity_code,
entity_type) REFERENCES entities (entity_code, entity_type), /* address
attributes . . . */);
CREATE TABLE vehicles (entity_code INTEGER NOT NULL, entity_type CHAR(2) NOT
NULL DEFAULT 'VH' CHECK (entity_type = 'VH'), FOREIGN KEY (entity_code,
entity_type) REFERENCES entities (entity_code, entity_type), /* vehicle
attributes . . . */);
CREATE TABLE persons (entity_code INTEGER NOT NULL, entity_type CHAR(2) NOT
NULL DEFAULT 'PR' CHECK (entity_type = 'PR'), FOREIGN KEY (entity_code,
entity_type) REFERENCES entities (entity_code, entity_type), /* person
attributes . . . */);
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:OPUHrE49FHA.1028@.TK2MSFTNGP11.phx.gbl...
Oops. I missed out a key:
CREATE TABLE entity_references (entity_code_1 INTEGER NOT NULL REFERENCES
entities (entity_code), entity_code_2 INTEGER NOT NULL REFERENCES entities
(entity_code), PRIMARY KEY (entity_code_1, entity_code_2)) ;
David Portas
SQL Server MVP
--|||... in fact I missed out nearly all the keys! For completeness, here it is
again:
CREATE TABLE entities (entity_code INTEGER NOT NULL PRIMARY KEY, entity_type
CHAR(2) NOT NULL CHECK (entity_type IN ('AD','VH','PR') /*
Address/Vehicle/Person */), UNIQUE (entity_code, entity_type)) ;
CREATE TABLE entity_references (entity_code_1 INTEGER NOT NULL PRIMARY KEY
REFERENCES entities (entity_code), entity_code_2 INTEGER NOT NULL REFERENCES
entities (entity_code)) ;
CREATE TABLE addresses (entity_code INTEGER NOT NULL PRIMARY KEY,
entity_type CHAR(2) NOT NULL DEFAULT 'AD' CHECK (entity_type = 'AD'),
FOREIGN KEY (entity_code, entity_type) REFERENCES entities (entity_code,
entity_type), /* address attributes . . . */);
CREATE TABLE vehicles (entity_code INTEGER NOT NULL PRIMARY KEY, entity_type
CHAR(2) NOT NULL DEFAULT 'VH' CHECK (entity_type = 'VH'), FOREIGN KEY
(entity_code, entity_type) REFERENCES entities (entity_code, entity_type),
/* vehicle attributes . . . */);
CREATE TABLE persons (entity_code INTEGER NOT NULL PRIMARY KEY, entity_type
CHAR(2) NOT NULL DEFAULT 'PR' CHECK (entity_type = 'PR'), FOREIGN KEY
(entity_code, entity_type) REFERENCES entities (entity_code, entity_type),
/* person attributes . . . */);
Hope this helps.
David Portas
SQL Server MVP
--|||A few things:
* Nothing wrong with 380 tables, if you need them all.
* Anything that doesn't strongly define and relate entities is going to
lead to problems.
* However your users think things should be related will drive design,
of course, but their definition of "related" could be different than a
rdbms definition. There will almost certainly be more attributes to
these n-n relations than simply x.id(n)--y.id(n) [ simple example:
person-phone may have phone number type (home,work,mobile,fax,etc.)
which truly belongs with this relationship and not the phone number
itself ]. From your one example, Address-Vehicle still seems to not be a
true n-n relationship - it seems more like "address found" is an
attribute of the relationship of "vehicles involved in a case" (which
itself is an n-n relation between cases-vehicles) - after all, what use
is "address where vehicle found" if it doesn't relate to a case?
(assuming there is such entity).
Only thorough business analysis will help you determine this.
Star wrote:
> Hey folks,
> Thanks for you answers.
>
> Unfortunately not on this system. This systems tracks all kind of
> information. Let's say that for example there was a robbery and the
> vehicle was found at a certain location. That's why we may have all kind
> of weird relationships.
> I agree with you guys in having a relationship table for each pair that
> we may think that are going to be related, but as I said our users may
> have at any moment the need of adding a relation between two very
> different kind of data.
> If we have one relationship table for each pair, we would have 380
> (19*20) relationship tables, and I don't think that would be a good idea.
> I will try to convince my supervisors of only having the relationships
> that we really know that are going to exist (that's what I have always
> wanted), but that's not going to be easy.
> By the way, I thought about another solution. Not very good either, but
> I would like to have your opinion.
> Here it is:
> We do have the 20 tables. Persons, Addresses, ...
> Each one of these tables look like this:
> Table X
> --
> PK (autoincrement)
> [Rest of the fields]
> We assign an ID to each one of these tables. For example, for Persons is
> 1, for Addresses is 2 and so on
> Now we have only 1 relationship table and it looks like this:
> Table Relations
> --
> SourceID
> FK_Source
> DestinationID
> FK_Dest
> [other fields]
> The PK of this table is (SourceID,FK_Source,DestinationID,FK_Des
t)
> SourceID and DestinationID are going to have the ID of one of the 20
> tables respectively. FK_Source and FK_Dest are the PKs of one of the 20
> tables respectively
> So if we have in that Relations table something like (1,32123,2,543543)
> We know that we have a relation between a person and a address. The PK
> of this person is 32123 and the PK of the address is 543543.
> The relation between 2 tables changes depending on the type of data.
> Not really a relational database...
> What do you guys think?
> Thanks a lot!!
>
>
>
>
>
>
>
> Raymond D'Anjou wrote:
>|||Thanks a lot, David!
I haven't tried it yet, I will be doing some testing through the
wend. My first impression is your idea is something similar to my
second solution, but will all the relations defined correctly. Please
correct me if I'm wrong.
I'll get back to you in a couple of days, when I do some other testing.
Thanks again.
David Portas wrote:
> ... in fact I missed out nearly all the keys! For completeness, here it is
> again:
> CREATE TABLE entities (entity_code INTEGER NOT NULL PRIMARY KEY, entity_ty
pe
> CHAR(2) NOT NULL CHECK (entity_type IN ('AD','VH','PR') /*
> Address/Vehicle/Person */), UNIQUE (entity_code, entity_type)) ;
> CREATE TABLE entity_references (entity_code_1 INTEGER NOT NULL PRIMARY KEY
> REFERENCES entities (entity_code), entity_code_2 INTEGER NOT NULL REFERENC
ES
> entities (entity_code)) ;
> CREATE TABLE addresses (entity_code INTEGER NOT NULL PRIMARY KEY,
> entity_type CHAR(2) NOT NULL DEFAULT 'AD' CHECK (entity_type = 'AD'),
> FOREIGN KEY (entity_code, entity_type) REFERENCES entities (entity_code,
> entity_type), /* address attributes . . . */);
> CREATE TABLE vehicles (entity_code INTEGER NOT NULL PRIMARY KEY, entity_ty
pe
> CHAR(2) NOT NULL DEFAULT 'VH' CHECK (entity_type = 'VH'), FOREIGN KEY
> (entity_code, entity_type) REFERENCES entities (entity_code, entity_type),
> /* vehicle attributes . . . */);
> CREATE TABLE persons (entity_code INTEGER NOT NULL PRIMARY KEY, entity_typ
e
> CHAR(2) NOT NULL DEFAULT 'PR' CHECK (entity_type = 'PR'), FOREIGN KEY
> (entity_code, entity_type) REFERENCES entities (entity_code, entity_type),
> /* person attributes . . . */);
> Hope this helps.
>sql

Help Designing an App. to be Run from a Job

Hello,

I'm working on a project that uses SQL Server 2005 and C# 2.0. I need some ideas of the best way to design/implement an application that will be executed from a SQL Server job.

The general idea is: a SQL Server job will call [something] and pass a couple of parameters to [something], then [something] will query a database and gather other information, then [something] will do some processing of data, and finally [something] will output the results for end user consumption.

The problem is that I don't know what [something] should be. Should it be a C# executable (but it can have no UI)? Should it be a web service? Should it be a console application (but, again, it cannot have a UI)? Should the job call a stored procedure, which in turn calls a C# assembly?

Basically, I'm just trying to get some ideas of the best way to design/implement a solution to my situation.

Thanks.

Hi,

without further information, I would suggest to use a c# console app called from the job.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Thanks for the input.

What other information could I provide to help choose the best solution?

|||

Best thing is to call a Stored Proc from the Sql job

In the Stored proc gather all the parameters that you need and use BCP out to keep that data in a flat file.

There is no need to use Webservice or other C# program as this would cause an additional overhead.

|||

There are a number of things your description suggests, and others that it leaves open...

1 - from what you say, it looks like the something does not need access to the resources of the server (as a computer) and only needs access to data inside SQL Server. If this is true, then the use of a SQL Server job to invoke a stored procedure is the way to go (and in SQL Server 2005 you can write your stored procedure in C# if you like).

If, on the other hand, you need access to information outside SQL Server (files, I/O, Active Directory, other PCs, ...) you'll need an app running outside SQL Server (yes - you can invoke an app from inside SQL Server, but why jump through hoops when you don't need to?). If this is the road to take, use Windows' scheduler instead of SQL Server's scheduler.

If you're running inside SQL Server, as one of the other posts suggested, you'll have a Stored Proc to gather parameters, that likely will call another (or more) to do the work. If you're doing stuff outside the database server the app will likely invoke a stored proc to do the 'inside' work - so you're just partitioning where you do what work.

When returning the data to the user - there are a number of options that depend on various factors you've not mentioned as to which is better:

> simply write the data to an output table (with a timestamp on each row if you need to hold multiple day's / run's worth of output) - and the use can have a simple reporting app to read from it.

> If the volume of data is small SQL Server can email the recipient(s) - again, this is probably better if the number of recipients is small. You may also need to consider the security of the data as it travels as email.

> If you need to store the data outside the server (in a dated file, for example) you have to get it out, somehow. If your app is already running outside the server that's a no brainer; if everything is running inside the server I think you'll have to trigger some outside app to pull the data (i.e. I don't think there's a way for a SQL Server job to dump data to the outside world). DO NOT TAKE MY WORD for this - DTS may be invokable as a SQL Server job and may server to store data outside the server, or there may be other capabilities of SQL Server 2005 that I've not encountered yet.

> Worst comes to worst - you have a simple outside app that polls a table in the server for a 'ready' flag and then pulls the data. But that's ugly.

HTH

Griffin

sql

Help connecting ole/db linked server to msaccess database in a different machine than sql server

Hi,

I have a msaccess linked server that I use to execute sql 2000 stored procedures from a front end in adp (access data project) format without any problem, if it is used on the same machine where the sql server resides (with any user logged on). In any other machine on the local network where I also need to use it, I get an ole/db error message saying that the microsoft jet database engine can not open the file because it's allready opened exclusivly or because it do not has permissions. I created the linked server with both UNC and normal path with the same result.

Thank's for all the help/clues you can give me.

Hi!

This is usually a permissions issue. The account that the SQL Server runs under needs to have NT permissions to the directory where the MDB is stored.

|||

Thank's for your help Cindy, but the case is that I can't figure out what to do to solve the problem.

I am sorry but I don't know exactly what do you mean by "the account that SQL Server runs".

The users that are running the sp that reports the error all have rights in the local network directory where the mdb is stored.

Could you please be kind enough to continue helping me on this?

|||Running SQL Server Service needs a service account to run with. The account can be determined using e.g. the Service Control manager. The access for file access is impersonated using the service account which is running SQL Server. If this has no access to the UNC share (e.g. System Account) you won′t be able to access the file / establish a link to the "server" / Access database. In addition to the explanation you should not use a mapped drive letter always use UNC paths working with SQL Server. it cannot be guranteed that the account which is running the process has also the mapped drived letter assigned to his profile.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

The service account SQL Server is using is the network login account of the users.
All of them have access to the network drive that is referenced in the linked server by the UNC path.

This is the code I used to set the linked server:

USE master
GO
EXEC sp_addlinkedserver
@.server = 'CRED85', -- Name of the linked Server
@.provider = 'Microsoft.Jet.OLEDB.4.0', -- Access Provider
@.srvproduct = 'dbaccess', -- may be anything
@.datasrc = '\\Gcxncliflsv301h\Aplic\AplicDep\DAI\APLIC\Produtos\Cred85.mdb' -- UNC path + Access db name
GO

and after that:

exec sp_addlinkedsrvlogin 'dbaccess', false, 'GRUPOCGD\c008645', 'Admin', NULL

So, my question remains:
- Why does it work only when the front end is executed in the machine where resides the server (with any user)
and NOT in any other machine? What's wrong?

Help connecting ole/db linked server to msaccess database in a different machine than sql server

Hi,

I have a msaccess linked server that I use to execute sql 2000 stored procedures from a front end in adp (access data project) format without any problem, if it is used on the same machine where the sql server resides (with any user logged on). In any other machine on the local network where I also need to use it, I get an ole/db error message saying that the microsoft jet database engine can not open the file because it's allready opened exclusivly or because it do not has permissions. I created the linked server with both UNC and normal path with the same result.

Thank's for all the help/clues you can give me.

Hi!

This is usually a permissions issue. The account that the SQL Server runs under needs to have NT permissions to the directory where the MDB is stored.

|||

Thank's for your help Cindy, but the case is that I can't figure out what to do to solve the problem.

I am sorry but I don't know exactly what do you mean by "the account that SQL Server runs".

The users that are running the sp that reports the error all have rights in the local network directory where the mdb is stored.

Could you please be kind enough to continue helping me on this?

|||Running SQL Server Service needs a service account to run with. The account can be determined using e.g. the Service Control manager. The access for file access is impersonated using the service account which is running SQL Server. If this has no access to the UNC share (e.g. System Account) you won′t be able to access the file / establish a link to the "server" / Access database. In addition to the explanation you should not use a mapped drive letter always use UNC paths working with SQL Server. it cannot be guranteed that the account which is running the process has also the mapped drived letter assigned to his profile.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

The service account SQL Server is using is the network login account of the users.
All of them have access to the network drive that is referenced in the linked server by the UNC path.

This is the code I used to set the linked server:

USE master
GO
EXEC sp_addlinkedserver
@.server = 'CRED85', -- Name of the linked Server
@.provider = 'Microsoft.Jet.OLEDB.4.0', -- Access Provider
@.srvproduct = 'dbaccess', -- may be anything
@.datasrc = '\\Gcxncliflsv301h\Aplic\AplicDep\DAI\APLIC\Produtos\Cred85.mdb' -- UNC path + Access db name
GO

and after that:

exec sp_addlinkedsrvlogin 'dbaccess', false, 'GRUPOCGD\c008645', 'Admin', NULL

So, my question remains:
- Why does it work only when the front end is executed in the machine where resides the server (with any user)
and NOT in any other machine? What's wrong?

help a newb with excel and sql server

hello everyone.
I recently changed the database of a project from Ms Access to SQL
server and had to make a lot of adjustments but there's one here
where
I can't figure it out how to do this in SQL server.
I was using the following to create an excel file based on the select
from Ms Access.
"SELECT * INTO [Excel 8.0;Database=" & server.mapPath("../documents/
example.xls].[sheet1]")&" FROM (SELECT * FROM TABLE1)"
This was working fine until the bd was changed to Ms Server 2005
where
it occurs an error. I will post the error even if its kind useless.
Error:
Microsoft OLE DB Provider for SQL Server error '80040e14'
Incorrect syntax near ')'.
As I said, the exact same code works fine in access. Any sugestion
how
to get this solved'
I know some turn arounds like create a content/type x-excel page and
this allow the user to save the file, but I really need is to create
the file .XLS on the disk to allow the user to download whenever they
want.
Any help is highly appreciated.
Thanks in advance
JoaoHi
You can create a linked server to EXCEL file.BOL has pretty good explanation
about the subject among with examples
<joaotsetsemoita@.gmail.com> wrote in message
news:1191855011.546016.198450@.r29g2000hsg.googlegroups.com...
> hello everyone.
> I recently changed the database of a project from Ms Access to SQL
> server and had to make a lot of adjustments but there's one here
> where
> I can't figure it out how to do this in SQL server.
>
> I was using the following to create an excel file based on the select
> from Ms Access.
>
> "SELECT * INTO [Excel 8.0;Database=" & server.mapPath("../documents/
> example.xls].[sheet1]")&" FROM (SELECT * FROM TABLE1)"
>
> This was working fine until the bd was changed to Ms Server 2005
> where
> it occurs an error. I will post the error even if its kind useless.
>
> Error:
> Microsoft OLE DB Provider for SQL Server error '80040e14'
> Incorrect syntax near ')'.
>
> As I said, the exact same code works fine in access. Any sugestion
> how
> to get this solved'
>
> I know some turn arounds like create a content/type x-excel page and
> this allow the user to save the file, but I really need is to create
> the file .XLS on the disk to allow the user to download whenever they
> want.
>
> Any help is highly appreciated.
>
> Thanks in advance
>
> Joao
>

Help : Font Deserialization Failed!

Help! I have a project that has been completed and tested fully and today
I was ready to move it to production. When I tried to open it,
I received the error:
Microsoft Development Environment is unable to load this document.
Deserialization Failed: Font 'Arial' doesn't support style 'Regular.'
I restored the .rdl from a backup, but got the same error.
Whats going on here? Is there any way to recover it? I opened the
.rdl with notepad and searched for the strings 'Arial' and 'Regular'
and neither turned up.
TIA,
Marc MillerI really need some help on this....my report development has stopped.
Additional Info:
I just tried creating a new report project and when I tried adding
a table to the layout I got the same error:
Font 'Arial' doesn't support style 'Regular'
The only this different since I created my last reports was that I
installed SQL Server SP3 on both my SQL Server 2000 and Analysis
Services (locally).
Marc|||FYI..
After uninstalling reporting services and re-installing I finally discovered
that my Arial font had become corrupted. I re-installed the font
and the error disappeared.
I don't know if the sp3 for SQL Server and Analysis Services corrupted the
font, but it remains suspect.
Marc

Help ... new to SQL

Hi,

I am working on a school project ...

I have the following schema:
<b>
DIVISION (dvname, manager)
DEPT (dname, parent-dname/parent-dvname, manager, floor#)
EMP (ename, salary, dname/dvname)
ITEM (iname, color, price, type)
SELL (dname, iname)
SUPPLY (sname, iname, dname)
</b>
Each of the first fields is the PKey.

I have to figure out the SQL for the following statement:

<b>List the items supplied by all companies that supply all items of type A.</b>

I have gotten this far, but do not understand division in SQL well enough .. I have a relational algebra solution that works ... but am having a hell of a time with a SQL solution ... Please help,

This is what I have in SQL:
SELECT SE.iname FROM SUPPLY SE WHERE NOT EXISTS
(SELECT I.iname FROM ITEM I WHERE I.type='A' AND NOT EXISTS
(SELECT S.iname FROM SUPPLY S WHERE S.iname=I.iname AND S.dname=SE.dname))

I have not work with SQL much so please help out ... I am using SQL Server if that matters ... Thanks--should give you the supply name where type = 'A'

select s.sname, i.iname, i.color, i.price, i.type
from supply s, item, i
where s.iname = i.iname and i.type = 'A'

if there is more to it let me know, but that should give you the items by supplier, if you want just distinct iname then just put

select distinct i.iname
from supply s, item, i
where s.iname = i.iname and i.type = 'A'

have fun

HELP - SQL Server 2000 Install Files needed urgently

Hi!

I have a large project that is due to complete this week. In order to
complete it I need SQL Server 2000 installed on a remote server. My
disk is corrupt and to order another media disk would damage my
deadline. I have the licence and serial key, but now need good install
files. I am even ready to buy another retail box, if I can find a
supplier that would give me a download site for the media, while I wait
for the shipment!

Please PLEASE help!

Regards,

BarryHi,

Sorry if i'm stating the obvious - but it wasn't commented on in your
post.. If you have the licence and serial key shouldn't you be
contacting Microsoft?

Greg|||Hi Greg. They will send me another media disk, but that doesnt help me
getting it installed for tomorrow.

Thanks for your reply.

Barry|||"TheFoot" <workshop@.carib-sys.com> wrote in message
news:1112918510.565481.327210@.g14g2000cwa.googlegr oups.com...
> Hi Greg. They will send me another media disk, but that doesnt help me
> getting it installed for tomorrow.

Unfortuantely I doubt most anyone here would be comfortable putting binaries
out there to be copied. Even if you're totally legit, MS would have a field
day if they found out.

Sorry.

> Thanks for your reply.
> Barry|||"TheFoot" <workshop@.carib-sys.com> wrote in message
news:1112918510.565481.327210@.g14g2000cwa.googlegr oups.com...
> Hi Greg. They will send me another media disk, but that doesnt help me
> getting it installed for tomorrow.
> Thanks for your reply.

Oh, one other thought.. may be too late by an hour or two, but a couple of
places like PC Connection on the East coast used to have a "order by
midnight and get it the next day" policy. MIGHT have luck with them.

> Barry|||I agree with Greg, nobody's going to "lend" you the SQL Server binaries
based on the premise that you're entitled to them - too much personal
risk.

I see two options:

1. Delay the project and wait for the shipment
2. Incur the extra cost for the "retail box" and hopefully charge it
back to the business|||Download the eval and then upgrade later.

http://www.microsoft.com/sql/evalua...ial/default.asp

--
David Portas
SQL Server MVP
--

HELP - Need a very quick code sample for calling report

Hi - any help would be tremendously appreciated.

I have been asked to create a quick project that calls a report from a hyperlink and need just a very basic way to do so.

The report is published to a SRS Server so all I need to be able to do is have a hypelink that will show the report on screen

Thanks in advance for any help

Rendering a Report using URL Access

If the info above is not sufficient you can google for the above keywords and am sure you will find what you need.

|||

If you need more detail, the full syntax is available athttp://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_urlaccess_7kbq.asp

The basic syntax is

http://server/virtualroot?[/pathinfo]&prefix:param=value[&prefix:param=value]...n]

sohttp://reportbox/reportserver?/CorpReports/SalesSummary&rs:Command=Render&rs:format=HTML4.0

wouldrender theSalesSummary report in theCorpReports folder on thereportserver vroot on thereportbox server inHTML 4.0 format

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.