Pros and cons of using image data type

Can anybody let me know the pros and cons of storing
images using SQL server image data type as opposed to just
storing the image file in system file directories. I'm
more concern on the efficiency and the space used if I
store images using SQL server 2000. Anything bad that
might occur?That's a sensible question to ask ... And many out there forget when they
use image datatypes ... BTW, Iam not a against image datatype but I always
prefer in storing them in the web server itself rather than passing them
around to and forth to the SQL Server. Moreover storing large files at the
sql server is also not advisable ... But if you are storing just a small
signature image for example .. It is fine to use SQL Server ...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD
www.extremeexperts.com
"twk" <twengkeat@.hotmail.com> wrote in message
news:01ce01c35d84$fce7c5e0$a001280a@.phx.gbl...
> Can anybody let me know the pros and cons of storing
> images using SQL server image data type as opposed to just
> storing the image file in system file directories. I'm
> more concern on the efficiency and the space used if I
> store images using SQL server 2000. Anything bad that
> might occur?|||The Pros of storing the images in SQL Server are pretty simple: You gain
all the "ACID" properties of a database system. The images are backed up
with the data in a coordinated fashion. All "pointers" between your
structured data and the images are maintained. No "broken link" problems,
no out of sync problems, no multiple namespaces, etc. If you store the data
in the database and the images in the file system then you have multiple
backups and various ways for the database and image information to end up
out of sync. Plus, you have to manage two different security environments.
The big negative of storing images in SQL Server is performance. There are
three issues here. The first is that SQL Server breaks images up into
chunks that fit on database pages. This makes reassembling the images
slower than if they are stored, without additional internal structure, in a
file. It also makes it impossible to use the operating system's built-in
facilities to transmit a file directly from disk out over a communications
link in kernel mode. So, from the standpoint of serving the image out onto
the web it is definitely much slower. Second, images stored in SQL Server
are returned to the application via the TDS protocol and the data access
APIs. Again, these are not optimal for image processing and impose overhead
that doesn't exist with a file. Third, most applications that process
images read and write them from the file system. So, if the image is stored
in SQL Server then you have to read the image out of the server, write it to
a temporary file, then invoke the image processing software against the
temporary file. It is these performance issues that lead many people to
store the images outside the database itself.
The real suitability of storing images inside SQL Server versus in the file
system comes down to the analysis of the application itself. For example,
if you have an HR application and one of the pieces of information that you
store about an employee is the picture that is on their id card then I think
you should store that image inside the database. Why? Well, you don't
access it very often. You aren't serving it up to the web constantly. It's
always accessed in conjunction with other employee data. You need to
protect access to the images under your HR policies. The performance hit is
thus not a significant factor when compared with the application and
operational issues.
If I had a server whose primary function was to serve up images all day,
then I'd store them in the file system. Or, at least maybe I would. I
actually have a preferred architecture for this scenario that addresses both
the management and performance issues. But it cost disk space. I would
store the authoritative copies of the images in the database, and then
create copies (a cache) in the file system. I would serve up the images
from the file system, but my recovery procedures would blast the
authoritative copies from the database into the filesystem. So I get the
best of both worlds. And with 120GB disks going for less money than I
usually carry around in my wallet, the duplication hardly seems to be a
problem for most situations.
One system that runs counter to conventional wisdom is the TerraServer
(http://terraserver-usa.com/). You can get details of how it works from
http://research.microsoft.com/research/pubs/view.aspx?msr_tr_id=MSR-TR-99-29.
Even though it serves up images all day long the images are stored in SQL
Server. This was done for a few reasons, chief among them to show that SQL
Server was capable of hosting such an application. Terraserver has been
operational since the summer of 1998, at times serving up several million
images per day.
--
Hal Berenson, SQL Server MVP
True Mountain Group LLC
"twk" <twengkeat@.hotmail.com> wrote in message
news:01ce01c35d84$fce7c5e0$a001280a@.phx.gbl...
> Can anybody let me know the pros and cons of storing
> images using SQL server image data type as opposed to just
> storing the image file in system file directories. I'm
> more concern on the efficiency and the space used if I
> store images using SQL server 2000. Anything bad that
> might occur?

Pros and Cons of starting the SQL services with a user account instead of the lsm

What are the pros and cons of starting the SQL services (MSSQLSERVER &
SQLAgent) with a domain wide user account instead of using the Local
System Account that does not have network access. To me it would seem
more secure to start the services using an account that cannot access
the network.
Thanks
MarcosI suspect that using a different account might force you to run the ole db
drivers for linked servers inside the same process as sql server, check my
earlier thread on october 21.
I am not sure about this but no one has given me a clear answer...
Otherwise we're using different accounts to run our sql servers without
problems (i think, i'm not the one that set up the servers...)
"Marcos Marrero" wrote:
> What are the pros and cons of starting the SQL services (MSSQLSERVER &
> SQLAgent) with a domain wide user account instead of using the Local
> System Account that does not have network access. To me it would seem
> more secure to start the services using an account that cannot access
> the network.
> Thanks
> Marcos
>

Pros and Cons of saving to serverstorage versus file system

What are the pros and cons of saving the SSIS package using serverstorage versus file system? It appears to me that the file system is much flexible and can be promoted anywhere without going through the hassle of exporting off from msdb etc.

Thanks,
Lito

Lito wrote:

What are the pros and cons of saving the SSIS package using serverstorage versus file system? It appears to me that the file system is much flexible and can be promoted anywhere without going through the hassle of exporting off from msdb etc.

Thanks,
Lito

I agree. The single biggest issue I have with server deployment is that you bring a whole new layer of management into play if you're using the Execute Package task (which likely many people will be).
i.e. At design time you use a file connection manager for calling other packages...at runtime you use OLE DB connection manager. So not only do you have to tell the package which environment its running in so that it knows which connection manager to use...promoting from dev-->test-->live becomes a real headache because you don't have uniformity across environments.

Just my 2 penneth worth!

-Jamie|||Kirk has blogged about some of the Pros and Cons of saving to SQL Server Vs File System. See

http://sqljunkies.com/WebLog/knight_reign/archive/2005/05/05/13523.aspx

- Ranjeeta

Pros and Cons of saving to serverstorage versus file system

What are the pros and cons of saving the SSIS package using serverstorage versus file system? It appears to me that the file system is much flexible and can be promoted anywhere without going through the hassle of exporting off from msdb etc.

Thanks,
Lito

Lito wrote:

What are the pros and cons of saving the SSIS package using serverstorage versus file system? It appears to me that the file system is much flexible and can be promoted anywhere without going through the hassle of exporting off from msdb etc.

Thanks,
Lito

I agree. The single biggest issue I have with server deployment is that you bring a whole new layer of management into play if you're using the Execute Package task (which likely many people will be).
i.e. At design time you use a file connection manager for calling other packages...at runtime you use OLE DB connection manager. So not only do you have to tell the package which environment its running in so that it knows which connection manager to use...promoting from dev-->test-->live becomes a real headache because you don't have uniformity across environments.

Just my 2 penneth worth!

-Jamie|||Kirk has blogged about some of the Pros and Cons of saving to SQL Server Vs File System. See

http://sqljunkies.com/WebLog/knight_reign/archive/2005/05/05/13523.aspx

- Ranjeeta

Programmaticaly associating a schedule with a deployed report?

I have a report deployment system (in C#) set up such that it takes all the RDL files in a folder and deploys them to the target server. Since many of these reports are computationally fairly expensive, I wanted them to display from a report snapshot, on a daily schedule. Although I know how to do this manually via Report Manager, I wasn't sure which SOAP methods to call on the ReportingServices webservice to make this happen.

Anyone have any experience with this or tips?

Thanks,
Arjun

It can be set using SetExecutionOptions method (http://msdn2.microsoft.com/en-gb/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.setexecutionoptions.aspx )

|||Ah, of course! Thank you very much.

Programmatically determine Startup Account

I have over 60 servers and am looking for some code that will allow me to
query for the Startup Account user ('system' or some domain account). Where
is this information stored?
The Spirit gives life; the flesh counts for nothing! (Jn 6:63)
There are many programming interfaces to that property. I typically like to
use command line utility to grab the information from each server, and parse
the text for the service account info. For instance, you can use NT resource
kit tool sc.exe for this purpose.
Linchi
"Blue Sky" wrote:

> I have over 60 servers and am looking for some code that will allow me to
> query for the Startup Account user ('system' or some domain account). Where
> is this information stored?
> --
> The Spirit gives life; the flesh counts for nothing! (Jn 6:63)
|||Then, this info is stored in the registry? I would prefer to stay within
SQL, where i can use the xp_regread(). Where in the registry is the
information stored?
The Spirit gives life; the flesh counts for nothing! (Jn 6:63)
"Linchi Shea" wrote:
[vbcol=seagreen]
> There are many programming interfaces to that property. I typically like to
> use command line utility to grab the information from each server, and parse
> the text for the service account info. For instance, you can use NT resource
> kit tool sc.exe for this purpose.
> Linchi
> "Blue Sky" wrote:
|||Execute something like this on your SQL Server instance:
DECLARE @.key nvarchar(100)
SELECT @.key = 'SYSTEM\CurrentControlSet\Services\' +
CASE @.@.SERVICENAME
WHEN 'MSSQLSERVER' THEN 'MSSQLSERVER'
ELSE 'MSSQL$' + @.@.SERVICENAME
END
SELECT @.key
DECLARE @.test varchar(20)
EXEC master..xp_regread @.rootkey='HKEY_LOCAL_MACHINE',
@.key= @.key,
@.value_name='ObjectName',
@.value=@.test OUTPUT
SELECT @.test
Regards
Pawel Potasinski
[http://www.potasinski.pl]
Uytkownik "Blue Sky" <BlueSky@.discussions.microsoft.com> napisa w
wiadomoci news:4A6CB5CF-2203-42B1-B209-E9EEB8125632@.microsoft.com...
>I have over 60 servers and am looking for some code that will allow me to
> query for the Startup Account user ('system' or some domain account).
> Where
> is this information stored?
> --
> The Spirit gives life; the flesh counts for nothing! (Jn 6:63)

Programmatically create DSN usind SQL Server Authentication

Hi
I am trying to programmatically create a DSN on a target system for SQL
Server using SQL Server Authentication.
There are documented solutions for using Windows authentication, but the
SQL Server authentication evades me.
1. Hard-coding in the registry:
Microsoft illustrates this in
HOWTO: Programmatically Create a DSN for SQL Server with VB
http://support.microsoft.com/default...;EN-US;q184608
KHLM/SOFTWARE/ODBC/ODBC.INI/<dsn-name> allows me to set LastUser, but I
have not found a way to specify the user's password in the registry (if at
all possible).
2. Windows API:
Microsoft's document HOWTO: Create and Remove a DSN in Visual Basic
http://support.microsoft.com/default...;EN-US;q171146
is off-topic for my purpose because it only shows Windows authentication.
ODBCCP32.DLL has a function SQLConfigDataSource, and that works fine,
provided that Windows Authentication is used. It does not work for SQL
Server Authentication, as documented in
http://www.sqlmonster.com/Uwe/Forum...1335/Problems-
setting-DSN-from-csharp-when
3. It looks like odbcconf.exe might be a possibility, with something like
odbcconf /A {CONFIGDSN "SQL Server"}
but I could not yet get it working. and I cannot find documentation or a
good working example. Adding SQL Server attributes just result in the
message "DSN Attributes are missing"
odbcconf /A {CONFIGDSN "SQL Server|DSN=Test|DATABASE=pubs|UID=nnn|PWD=xxx"}
4. I cannot use the RegisterDatabase function in VB as documented in
HOWTO: Set Up ODBC Data Sources When Distributing Apps
http://support.microsoft.com/default...b;EN-US;123008
I need a solution I can implement in C#, C++ and Installshield's
Installscript. This solution also documents only Windows authentication.
5. Letting the user configure this manually, e.g. by starting the control
panel applet, is not an option. Using DSN-less connection options is also
out.
Can anyone tell me whether it is at all possible to programmatically create
a DSN for SQL Server, using SQL Server Authentication, and how I can
specify the UID and PWD for the user?
Thanks
Willem Semmelink
Message posted via http://www.sqlmonster.com
Not when you create the DSN programmatically. You may find
some old references on how to do it creating keys in the
registry but the driver will ignore those. From the
application side, you'd have to pass in the user id and
password in the connection string or from the application
that references the DSN.
-Sue
On Wed, 02 Feb 2005 12:48:34 GMT, "Willem Semmelink via
SQLMonster.com" <forum@.SQLMonster.com> wrote:

>Hi
>I am trying to programmatically create a DSN on a target system for SQL
>Server using SQL Server Authentication.
>There are documented solutions for using Windows authentication, but the
>SQL Server authentication evades me.
>1. Hard-coding in the registry:
>Microsoft illustrates this in
>HOWTO: Programmatically Create a DSN for SQL Server with VB
>http://support.microsoft.com/default...;EN-US;q184608
> KHLM/SOFTWARE/ODBC/ODBC.INI/<dsn-name> allows me to set LastUser, but I
>have not found a way to specify the user's password in the registry (if at
>all possible).
>2. Windows API:
>Microsoft's document HOWTO: Create and Remove a DSN in Visual Basic
>http://support.microsoft.com/default...;EN-US;q171146
>is off-topic for my purpose because it only shows Windows authentication.
>ODBCCP32.DLL has a function SQLConfigDataSource, and that works fine,
>provided that Windows Authentication is used. It does not work for SQL
>Server Authentication, as documented in
>http://www.sqlmonster.com/Uwe/Forum...1335/Problems-
>setting-DSN-from-csharp-when
>3. It looks like odbcconf.exe might be a possibility, with something like
>odbcconf /A {CONFIGDSN "SQL Server"}
>but I could not yet get it working. and I cannot find documentation or a
>good working example. Adding SQL Server attributes just result in the
>message "DSN Attributes are missing"
>odbcconf /A {CONFIGDSN "SQL Server|DSN=Test|DATABASE=pubs|UID=nnn|PWD=xxx"}
>4. I cannot use the RegisterDatabase function in VB as documented in
>HOWTO: Set Up ODBC Data Sources When Distributing Apps
>http://support.microsoft.com/default...b;EN-US;123008
>I need a solution I can implement in C#, C++ and Installshield's
>Installscript. This solution also documents only Windows authentication.
>5. Letting the user configure this manually, e.g. by starting the control
>panel applet, is not an option. Using DSN-less connection options is also
>out.
>Can anyone tell me whether it is at all possible to programmatically create
>a DSN for SQL Server, using SQL Server Authentication, and how I can
>specify the UID and PWD for the user?
>Thanks
>Willem Semmelink

Programmatically create DSN usind SQL Server Authentication

Hi
I am trying to programmatically create a DSN on a target system for SQL
Server using SQL Server Authentication.
There are documented solutions for using Windows authentication, but the
SQL Server authentication evades me.
1. Hard-coding in the registry:
Microsoft illustrates this in
HOWTO: Programmatically Create a DSN for SQL Server with VB
http://support.microsoft.com/defaul...b;EN-US;q184608
KHLM/SOFTWARE/ODBC/ODBC.INI/<dsn-name> allows me to set LastUser, but I
have not found a way to specify the user's password in the registry (if at
all possible).
2. Windows API:
Microsoft's document HOWTO: Create and Remove a DSN in Visual Basic
http://support.microsoft.com/defaul...b;EN-US;q171146
is off-topic for my purpose because it only shows Windows authentication.
ODBCCP32.DLL has a function SQLConfigDataSource, and that works fine,
provided that Windows Authentication is used. It does not work for SQL
Server Authentication, as documented in
http://www.droptable.com/Uwe/Forum.../1335/Problems-
setting-DSN-from-csharp-when
3. It looks like odbcconf.exe might be a possibility, with something like
odbcconf /A {CONFIGDSN "SQL Server"}
but I could not yet get it working. and I cannot find documentation or a
good working example. Adding SQL Server attributes just result in the
message "DSN Attributes are missing"
odbcconf /A {CONFIGDSN "SQL Server|DSN=Test|DATABASE=pubs|UID=nnn|PW
D=x
xx"}
4. I cannot use the RegisterDatabase function in VB as documented in
HOWTO: Set Up ODBC Data Sources When Distributing Apps
http://support.microsoft.com/defaul...kb;EN-US;123008
I need a solution I can implement in C#, C++ and Installshield's
Installscript. This solution also documents only Windows authentication.
5. Letting the user configure this manually, e.g. by starting the control
panel applet, is not an option. Using DSN-less connection options is also
out.
Can anyone tell me whether it is at all possible to programmatically create
a DSN for SQL Server, using SQL Server Authentication, and how I can
specify the UID and PWD for the user?
Thanks
Willem Semmelink
Message posted via http://www.droptable.comNot when you create the DSN programmatically. You may find
some old references on how to do it creating keys in the
registry but the driver will ignore those. From the
application side, you'd have to pass in the user id and
password in the connection string or from the application
that references the DSN.
-Sue
On Wed, 02 Feb 2005 12:48:34 GMT, "Willem Semmelink via
droptable.com" <forum@.droptable.com> wrote:

>Hi
>I am trying to programmatically create a DSN on a target system for SQL
>Server using SQL Server Authentication.
>There are documented solutions for using Windows authentication, but the
>SQL Server authentication evades me.
>1. Hard-coding in the registry:
>Microsoft illustrates this in
>HOWTO: Programmatically Create a DSN for SQL Server with VB
>http://support.microsoft.com/defaul...b;EN-US;q184608
> KHLM/SOFTWARE/ODBC/ODBC.INI/<dsn-name> allows me to set LastUser, but I
>have not found a way to specify the user's password in the registry (if at
>all possible).
>2. Windows API:
>Microsoft's document HOWTO: Create and Remove a DSN in Visual Basic
>http://support.microsoft.com/defaul...b;EN-US;q171146
>is off-topic for my purpose because it only shows Windows authentication.
>ODBCCP32.DLL has a function SQLConfigDataSource, and that works fine,
>provided that Windows Authentication is used. It does not work for SQL
>Server Authentication, as documented in
>http://www.droptable.com/Uwe/Forum.../1335/Problems-
>setting-DSN-from-csharp-when
>3. It looks like odbcconf.exe might be a possibility, with something like
>odbcconf /A {CONFIGDSN "SQL Server"}
>but I could not yet get it working. and I cannot find documentation or a
>good working example. Adding SQL Server attributes just result in the
>message "DSN Attributes are missing"
>odbcconf /A {CONFIGDSN "SQL Server|DSN=Test|DATABASE=pubs|UID=nnn|PW
D=
xxx"}
>4. I cannot use the RegisterDatabase function in VB as documented in
>HOWTO: Set Up ODBC Data Sources When Distributing Apps
>http://support.microsoft.com/defaul...kb;EN-US;123008
>I need a solution I can implement in C#, C++ and Installshield's
>Installscript. This solution also documents only Windows authentication.
>5. Letting the user configure this manually, e.g. by starting the control
>panel applet, is not an option. Using DSN-less connection options is also
>out.
>Can anyone tell me whether it is at all possible to programmatically create
>a DSN for SQL Server, using SQL Server Authentication, and how I can
>specify the UID and PWD for the user?
>Thanks
>Willem Semmelink

Programmatically adding a user to the System User role

We have been working on an application that will be using a forms-authenticated report server (RS2005) as a reporting back-end. Using the reporting services web service I have been able to assign permissions to objects in reporting services no problem. The issue is that each user needs to be added to the System User role to be able to use the report builder properly. I can't seem to find a way to do this programmatically. Any idea?Nevermind, GetSystemPolicies() is what I was looking for.

Programatically setting the sub-report to load

My system uses a variety of invoice templates for each client, so 'Bank A' would have one invoice type sent to it, and 'Bank B' would have another etc. Each invoice template is a report, and they can vary quite substantially.

Each month the accounts team need to print out all the invoices for a month. I would like to create a report which has the invoices as a sub-report, so they can all be exported together and printed together.

However each sub-report could obviously be a different invoice-template. I notice that the sub-report selector field is one of the only fields that doesn't take an expression. Is there a way to programatically set which sub-report is used? The parameter sent to each report type is always the same (the primary key of the invoice to report on).

Any ideas? Or any other methods which would achieve the same goals?

Thanks,

Paul

One way is to add all subreport-types you could ever need to your report and hide all but the one you need dynamically via the hidden-expression..|||

Yes I could see that working, but it is pretty messy. As I would like the facility to just add extra invoice-template reports at any time. But with your method I'd have to also modify the main list report.

Thanks for your help anyway :)

PROGRAMATICALLY ELIMINATE DUPLICATE ADDRESSES

I am trying to find a way to eliminate duplicate addresses from a table. I
have a table that is imported from a seperate system so I have no control
over it but I need to be able to design a query that brings out last name,
first name, zip code, city, and state, address1 and address2 for a mailing
list.
Currently I have many people at the same address that I need to remove and
when I check the zip code against our zipcode table it removes any with a zi
p
+4 setup.
Is there any way to set this up to keep the zip+4 part and eliminate all of
the duplicate addresses?
You can modify the "double cursor" example at:
http://groups.google.com/group/micr...cbce3491367da2f
Where I have
declare curOrders cursor FAST_FORWARD for select OrderID , OrderDate
from Orders where CustomerID = @.CustomerID
put a counter there. (of course reset it for each new Customer)
if the counter > 1 (aka, #2, #3, etc , etc) you can run a DELETE.
..
Its a hack, but it works.
"Don" <djw@.nospam.postalias> wrote in message
news:D9647F2A-8402-40AB-A464-E8CCDEE5AD34@.microsoft.com...
> I am trying to find a way to eliminate duplicate addresses from a table.
I
> have a table that is imported from a seperate system so I have no control
> over it but I need to be able to design a query that brings out last name,
> first name, zip code, city, and state, address1 and address2 for a mailing
> list.
> Currently I have many people at the same address that I need to remove and
> when I check the zip code against our zipcode table it removes any with a
zip
> +4 setup.
> Is there any way to set this up to keep the zip+4 part and eliminate all
of
> the duplicate addresses?|||I am sorry, I did not state my problem completely. I also cannot delete fro
m
the original table so it must all be done in a query
"sloan" wrote:

>
> You can modify the "double cursor" example at:
> http://groups.google.com/group/micr...cbce3491367da2f
> Where I have
> declare curOrders cursor FAST_FORWARD for select OrderID , OrderDate
> from Orders where CustomerID = @.CustomerID
> put a counter there. (of course reset it for each new Customer)
> if the counter > 1 (aka, #2, #3, etc , etc) you can run a DELETE.
> ...
> Its a hack, but it works.
>
>
>
>
> "Don" <djw@.nospam.postalias> wrote in message
> news:D9647F2A-8402-40AB-A464-E8CCDEE5AD34@.microsoft.com...
> I
> zip
> of
>
>|||I also cannot affect the original table as we rerun other queries that use
the extra information for other purposes.
I am pretty weak when it comes to SQL also, sorry.
"Don" wrote:

> I am trying to find a way to eliminate duplicate addresses from a table.
I
> have a table that is imported from a seperate system so I have no control
> over it but I need to be able to design a query that brings out last name,
> first name, zip code, city, and state, address1 and address2 for a mailing
> list.
> Currently I have many people at the same address that I need to remove and
> when I check the zip code against our zipcode table it removes any with a
zip
> +4 setup.
> Is there any way to set this up to keep the zip+4 part and eliminate all o
f
> the duplicate addresses?|||Select 'distinct' (without the quotes of course). This will eliminate
duplicates as long as you have selected all the data properly. Be careful
that you are getting all the data you want though.
"Don" wrote:
> I also cannot affect the original table as we rerun other queries that use
> the extra information for other purposes.
> I am pretty weak when it comes to SQL also, sorry.
> "Don" wrote:
>|||Why not just:
select distinct
firstname, zipcode, city, and state, address1, address2
from sometable
or you want to keep only the row with the longest zipcode? If so, try:
select
firstname, max(zipcode), city, and state, address1, address2
from sometable
group by firstname, city, and state, address1, address2
"Don" <djw@.nospam.postalias> wrote in message
news:D9647F2A-8402-40AB-A464-E8CCDEE5AD34@.microsoft.com...
> I am trying to find a way to eliminate duplicate addresses from a table.
I
> have a table that is imported from a seperate system so I have no control
> over it but I need to be able to design a query that brings out last name,
> first name, zip code, city, and state, address1 and address2 for a mailing
> list.
> Currently I have many people at the same address that I need to remove and
> when I check the zip code against our zipcode table it removes any with a
zip
> +4 setup.
> Is there any way to set this up to keep the zip+4 part and eliminate all
of
> the duplicate addresses?|||I have used distinct to get rid of duplicate entries, but distinct does not
seem to help with different people at the same address.
This is where my problem is.
First, Last, Addr1, Addr2, City, State, Zip
Joe Brown PO Box 33 NULL Town ST 99999
George Brown PO Box 33 NULL TOWN ST 99999
Jay Sherman 123 Somestrret NULL TOWN ST 99999
More specifically, I want to remove the PO Box 33 or only send to one of
them not both.
"MattB" wrote:
> Select 'distinct' (without the quotes of course). This will eliminate
> duplicates as long as you have selected all the data properly. Be careful
> that you are getting all the data you want though.
> "Don" wrote:
>|||Don what is the primary key for this table?
Say you have the customerID (ideally you should be having one), So your
query should go like this (untested, of course).
SELECT LAST_NAME, FIRST_NAME, ZIP_CODE,
CITY, STATE, ADDRESS1,ADDRESS2
FROM YOUR_TABLE A
where
CustomerID in (SELECT MIN(Customer_id) from your_table group by ZIP_CODE,
CITY, STATE, ADDRESS1,ADDRESS2 )
Let me know if this is what u r looking for.|||You could use an aggregate function (such as MIN or MAX) when you select the
names. Don't change anything related to the address information.
Keith Kratochvil
"Don" <djw@.nospam.postalias> wrote in message
news:A558C92F-EE0E-4835-9C31-BAF798905A62@.microsoft.com...
>I have used distinct to get rid of duplicate entries, but distinct does not
> seem to help with different people at the same address.
> This is where my problem is.
> First, Last, Addr1, Addr2, City, State, Zip
> Joe Brown PO Box 33 NULL Town ST 99999
> George Brown PO Box 33 NULL TOWN ST 99999
> Jay Sherman 123 Somestrret NULL TOWN ST 99999
> More specifically, I want to remove the PO Box 33 or only send to one of
> them not both.
> "MattB" wrote:
>|||Omni's subquery should work, but if you want different parameters another wa
y
is;
begin
create table ##address (LAST_NAME varchar(50) null,
FIRST_NAME varchar(50) null,
ZIP_CODE varchar(10) null,
CITY varchar(50) null,
STATE varchar(50) null,
ADDRESS1 varchar(50) null,
ADDRESS2 varchar(50) null)
set nocount on
insert ##address
SELECT distinct
null,
null,
ZIP_CODE,
CITY,
STATE,
ADDRESS1,
ADDRESS2
FROM table (NOLOCK)
WHERE your_parameter
AND no po boxes, etc
--you could also use a case stmt to eliminate wanted or unwanted parameters
--this will load the addresses only into temp table.
update ##address
set LAST_NAME = a.LAST_NAME,
FIRST_NAME = a.FIRST_NAME
FROM table a (NOLOCK)
WHERE your parameter to tie to address
select LAST_NAME,FIRST_NAME,ZIP_CODE,CITY,STATE
,ADDRESS1,ADDRESS2
from ##address (nolock)
drop table ##address
end
"Don" wrote:
> I have used distinct to get rid of duplicate entries, but distinct does no
t
> seem to help with different people at the same address.
> This is where my problem is.
> First, Last, Addr1, Addr2, City, State, Zip
> Joe Brown PO Box 33 NULL Town ST 99999
> George Brown PO Box 33 NULL TOWN ST 99999
> Jay Sherman 123 Somestrret NULL TOWN ST 99999
> More specifically, I want to remove the PO Box 33 or only send to one of
> them not both.
> "MattB" wrote:
>