Duplicate record question

In order to check that a new users ID does not already exist in the database I thought it would be a good idea to put the Insert into a Try Catch statement so that I can test for the duplicate record exception and inform the user accordingly. I was also trying to avoid querying the data base before executing the Insert.

The problem is what to actually test for. When the code throws the exception it is a big long string . .

"Violation of PRIMARY KEY constraint 'PK_Users_2__51'. Cannot insert duplicate key in object 'Users'"

I just thought that there has to be something simplar to test for than comparing the exception to the above string.

Can anyone tell me of a better way of doing this ?

(by the way I am only using Web Matrix and MSDE in case it matters)

MarkI would use a stored procedure, check for dups in the SP, and then return a code indicating success or failure.|||Isn't the error code that you'd compare against?

Duplicate order nums in Orders

Hi all,
We have a third party application that allows users to take
customer orders. This application has all the bells and
whistles of the Titanic. Since we wanted our customers to
be able to place orders themselves, we built a smaller
light weight application to allow them to do just that.
This was built using VB6, SP5. The back end is SQL Server
2000.
When an order is taken an OrderNumber is assigned to it.
The problem that I am having is that sometimes the order
numbers duplicate. That is, two orders have the same number.
This only happens when the the main order system and the
small order app both insert a new order at almost the same
time (normally 1 second apart).
When developing the smaller order app I built the following
TSQL transaction to avoid this from happening:
begin transaction
declare @.newConfirm int
declare @.maxNum int
--here I get the new order num
select @.newOrderNum=CurrentSeqNum,@.maxNum=Maxim
um
from SeqNumbers where Id='OrderNumber'
--the order num cycles, so if it reached the limit start over
if @.newOrderNum = @.maxNum
update SeqNumbers set CurrentSeqNum=Minimum
where Id='OrderNumber'
Else
update SeqNumbers set CurrentSeqNum=CurrentSeqNum+1
where Id='OrderNumber'
--I insert the data pertaining to the order
insert into trnOrders (AccountId,Received,Shipped,Amount,Order
Number)
values('69M033',getdate(),'2005-06-13',2300,@.newOrderNum)
--I retrieve the order number
select @.newOrderNum as OrderNum
commit transaction
The above should protect the smaller app from getting duplicate
order numbers when more than one order is placed at the same time
using the smaller app.
How can I protect against there being duplicate order numbers due
to orders being placed at the same time by the other application? Any
ideas what could be causing this? Is the process that I am following
robust enough to protect against this?
Any advice is greatly appreciated, thanks!
SagaThe first thing is to put a primary key constraint in the order table to
avoid duplicated order numbers,o no matter how many applications are they
using to enter orders.
You can create another stored procedure to get the last number to be used.
create procedure dbo.usp_next_order_number
@.next_order_number int output
as
set nocount on
update dbo.SeqNumbers
set @.next_order_number = CurrentSeqNum = CurrentSeqNum + 1
where [Id]='OrderNumber'
return @.@.error
go
and call this sp from yours.
create procedure...
@.newOrderNum int output
as
set nocount on
begin transaction
declare @.newConfirm int
--declare @.maxNum int
--here I get the new order num
-- select @.newOrderNum=CurrentSeqNum,@.maxNum=Maxim
um
-- from SeqNumbers where Id='OrderNumber'
--the order num cycles, so if it reached the limit start over
-- if @.newOrderNum = @.maxNum
-- update SeqNumbers set CurrentSeqNum=Minimum
-- where Id='OrderNumber'
-- Else
-- update SeqNumbers set CurrentSeqNum=CurrentSeqNum+1
-- where Id='OrderNumber'
declare @.rv int
declare @.error int
exec @.rv = dbo.usp_next_order_number @.newOrderNum output
set @.error = coalesce(nullif(@.rv, 0), @.@.error)
if @.@.error != 0
begin
rollback transaction
raiserror('Error getting next order number.', 16, 1)
return -1
end
--I insert the data pertaining to the order
insert into trnOrders (AccountId,Received,Shipped,Amount,Order
Number)
values('69M033',getdate(),'2005-06-13',2300,@.newOrderNum)
-- --I retrieve the order number
-- select @.newOrderNum as OrderNum
commit transaction
go
You need to put more code in your sp to check errors.
AMB
"Saga" wrote:

> Hi all,
> We have a third party application that allows users to take
> customer orders. This application has all the bells and
> whistles of the Titanic. Since we wanted our customers to
> be able to place orders themselves, we built a smaller
> light weight application to allow them to do just that.
> This was built using VB6, SP5. The back end is SQL Server
> 2000.
> When an order is taken an OrderNumber is assigned to it.
> The problem that I am having is that sometimes the order
> numbers duplicate. That is, two orders have the same number.
> This only happens when the the main order system and the
> small order app both insert a new order at almost the same
> time (normally 1 second apart).
> When developing the smaller order app I built the following
> TSQL transaction to avoid this from happening:
>
> begin transaction
> declare @.newConfirm int
> declare @.maxNum int
>
> --here I get the new order num
> select @.newOrderNum=CurrentSeqNum,@.maxNum=Maxim
um
> from SeqNumbers where Id='OrderNumber'
> --the order num cycles, so if it reached the limit start over
> if @.newOrderNum = @.maxNum
> update SeqNumbers set CurrentSeqNum=Minimum
> where Id='OrderNumber'
> Else
> update SeqNumbers set CurrentSeqNum=CurrentSeqNum+1
> where Id='OrderNumber'
> --I insert the data pertaining to the order
> insert into trnOrders (AccountId,Received,Shipped,Amount,Order
Number)
> values('69M033',getdate(),'2005-06-13',2300,@.newOrderNum)
> --I retrieve the order number
> select @.newOrderNum as OrderNum
> commit transaction
>
> The above should protect the smaller app from getting duplicate
> order numbers when more than one order is placed at the same time
> using the smaller app.
> How can I protect against there being duplicate order numbers due
> to orders being placed at the same time by the other application? Any
> ideas what could be causing this? Is the process that I am following
> robust enough to protect against this?
>
> Any advice is greatly appreciated, thanks!
> Saga
>
>|||Thank you for your reply. I will test using your idea. One thing I
cannot
do is place the constraint on the table because this db belongs to the
application that was purchased, so I can't make this kind of
modification
to it without risking the functionality of the main application. We once
added a field to one of the tables and the main application stopped
working.
Apparently, it validates the tables in some way.
Thanks again
Saga
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
message news:033FC598-10FC-45F6-B1D0-74B57BF9B329@.microsoft.com...
> The first thing is to put a primary key constraint in the order table
> to
> avoid duplicated order numbers,o no matter how many applications are
> they
> using to enter orders.
> You can create another stored procedure to get the last number to be
> used.
> create procedure dbo.usp_next_order_number
> @.next_order_number int output
> as
> set nocount on
> update dbo.SeqNumbers
> set @.next_order_number = CurrentSeqNum = CurrentSeqNum + 1
> where [Id]='OrderNumber'
> return @.@.error
> go
> and call this sp from yours.
> create procedure...
> @.newOrderNum int output
> as
> set nocount on
> begin transaction
> declare @.newConfirm int
> --declare @.maxNum int
> --here I get the new order num
> -- select @.newOrderNum=CurrentSeqNum,@.maxNum=Maxim
um
> -- from SeqNumbers where Id='OrderNumber'
> --the order num cycles, so if it reached the limit start over
> -- if @.newOrderNum = @.maxNum
> -- update SeqNumbers set CurrentSeqNum=Minimum
> -- where Id='OrderNumber'
> -- Else
> -- update SeqNumbers set CurrentSeqNum=CurrentSeqNum+1
> -- where Id='OrderNumber'
> declare @.rv int
> declare @.error int
> exec @.rv = dbo.usp_next_order_number @.newOrderNum output
> set @.error = coalesce(nullif(@.rv, 0), @.@.error)
> if @.@.error != 0
> begin
> rollback transaction
> raiserror('Error getting next order number.', 16, 1)
> return -1
> end
> --I insert the data pertaining to the order
> insert into trnOrders (AccountId,Received,Shipped,Amount,Order
Number)
> values('69M033',getdate(),'2005-06-13',2300,@.newOrderNum)
> -- --I retrieve the order number
> -- select @.newOrderNum as OrderNum
> commit transaction
> go
> You need to put more code in your sp to check errors.
>
> AMB
> "Saga" wrote:
>

Duplicate Entries Help

Hi there,
I have a table called userchartentry and it has these fields:
ID, DMCUSERID, CHARTPERIODID, TRACKID, POSITION
This table is filled with users charts, and each chart has 20 tracks, hence
the position field. The problem is our Chart Application has gone crazy and
duplicated each track up to 5 times in each chart. Sometimes a chart will
have 5 position 1 tracks, but 4 position 2 tracks.
I would like to write some SQL to delete the duplicate entries. I cant think
how to do this, if anyone can think of a solution then a cyber beer is
yours, plus lots and lots of thanks.
Cheers,
Steve
If ID is an identity column (which I assume it is):
DELETE FROM userchartentry
WHERE EXISTS(SELECT NULL FROM userchartentry u1
WHERE u1.ID < userchartentry .ID
AND u1.DMCUSERID = userchartentry .DMCUSERID
AND u1.CHARTPERIODID = userchartentry .CHARTPERIODID
AND u1.TRACKID = userchartentry .TRACKID
AND u1.POSITION = userchartentry .POSITION)
then create a primary key or unique constraint on the columns that make up
the natural key to prevent it from happening again.
Jacco Schalkwijk
SQL Server MVP
"Dooza" <steve@.whatareyoudooza.tv> wrote in message
news:u$ZirFgqEHA.708@.tk2msftngp13.phx.gbl...
> Hi there,
> I have a table called userchartentry and it has these fields:
> ID, DMCUSERID, CHARTPERIODID, TRACKID, POSITION
> This table is filled with users charts, and each chart has 20 tracks,
> hence
> the position field. The problem is our Chart Application has gone crazy
> and
> duplicated each track up to 5 times in each chart. Sometimes a chart will
> have 5 position 1 tracks, but 4 position 2 tracks.
> I would like to write some SQL to delete the duplicate entries. I cant
> think
> how to do this, if anyone can think of a solution then a cyber beer is
> yours, plus lots and lots of thanks.
> Cheers,
> Steve
>
|||Hi Jacco,
That was just about perfect. I created a test table with the same data and
ran the command, when I looked at the results I found 18 DMCUSERIDs with
more than 20 tracks. This I can handle very happily, they are easy to
delete.
Thank you very much for helping!
Steve
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:erpIzdgqEHA.1164@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> If ID is an identity column (which I assume it is):
> DELETE FROM userchartentry
> WHERE EXISTS(SELECT NULL FROM userchartentry u1
> WHERE u1.ID < userchartentry .ID
> AND u1.DMCUSERID = userchartentry .DMCUSERID
> AND u1.CHARTPERIODID = userchartentry .CHARTPERIODID
> AND u1.TRACKID = userchartentry .TRACKID
> AND u1.POSITION = userchartentry .POSITION)
> then create a primary key or unique constraint on the columns that make up
> the natural key to prevent it from happening again.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Dooza" <steve@.whatareyoudooza.tv> wrote in message
> news:u$ZirFgqEHA.708@.tk2msftngp13.phx.gbl...
will
>
|||Hi Jacco,
Before I do this to my live database, if I want to filter by CHARTPERIODID =
8414 where would that go in the statement?
Cheers!
Steve
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:erpIzdgqEHA.1164@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> If ID is an identity column (which I assume it is):
> DELETE FROM userchartentry
> WHERE EXISTS(SELECT NULL FROM userchartentry u1
> WHERE u1.ID < userchartentry .ID
> AND u1.DMCUSERID = userchartentry .DMCUSERID
> AND u1.CHARTPERIODID = userchartentry .CHARTPERIODID
> AND u1.TRACKID = userchartentry .TRACKID
> AND u1.POSITION = userchartentry .POSITION)
> then create a primary key or unique constraint on the columns that make up
> the natural key to prevent it from happening again.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Dooza" <steve@.whatareyoudooza.tv> wrote in message
> news:u$ZirFgqEHA.708@.tk2msftngp13.phx.gbl...
will
>
|||Just add it at the end:
DELETE FROM userchartentry
WHERE EXISTS(SELECT NULL FROM userchartentry u1
WHERE u1.ID < userchartentry .ID
AND u1.DMCUSERID = userchartentry .DMCUSERID
AND u1.CHARTPERIODID = userchartentry .CHARTPERIODID
AND u1.TRACKID = userchartentry .TRACKID
AND u1.POSITION = userchartentry .POSITION)
AND CHARTPERIODID = 8414
Jacco Schalkwijk
SQL Server MVP
"Dooza" <steve@.whatareyoudooza.tv> wrote in message
news:ehaP51gqEHA.1992@.TK2MSFTNGP09.phx.gbl...
> Hi Jacco,
> Before I do this to my live database, if I want to filter by CHARTPERIODID
> =
> 8414 where would that go in the statement?
> Cheers!
> Steve
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid >
> wrote
> in message news:erpIzdgqEHA.1164@.TK2MSFTNGP10.phx.gbl...
> will
>
|||Hi Jacco,
I thought as much, thanks again! I just removed 20,000 rows of duplicate
data.
Steve
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:%23Y7ZVChqEHA.1712@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Just add it at the end:
> DELETE FROM userchartentry
> WHERE EXISTS(SELECT NULL FROM userchartentry u1
> WHERE u1.ID < userchartentry .ID
> AND u1.DMCUSERID = userchartentry .DMCUSERID
> AND u1.CHARTPERIODID = userchartentry .CHARTPERIODID
> AND u1.TRACKID = userchartentry .TRACKID
> AND u1.POSITION = userchartentry .POSITION)
> AND CHARTPERIODID = 8414
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Dooza" <steve@.whatareyoudooza.tv> wrote in message
> news:ehaP51gqEHA.1992@.TK2MSFTNGP09.phx.gbl...
CHARTPERIODID[vbcol=seagreen]
crazy[vbcol=seagreen]
cant[vbcol=seagreen]
is
>
|||Hi Jacco,
I was wondering if you could help me some more?
I want to display the following data from this same table:
CHARTPERIODID and NUMBER OF UNIQUE CHARTS
This is what I first tried:
SELECT CHARTPERIODID, COUNT(DMCUSERID) AS CHARTS
FROM dbo.UserChartEntry
GROUP BY CHARTPERIODID
But due to there being more than 1 row per DMCUSERID and CHARTPERIODID the
count for CHARTS is wrong. So I then tried this:
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:%23Y7ZVChqEHA.1712@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Just add it at the end:
> DELETE FROM userchartentry
> WHERE EXISTS(SELECT NULL FROM userchartentry u1
> WHERE u1.ID < userchartentry .ID
> AND u1.DMCUSERID = userchartentry .DMCUSERID
> AND u1.CHARTPERIODID = userchartentry .CHARTPERIODID
> AND u1.TRACKID = userchartentry .TRACKID
> AND u1.POSITION = userchartentry .POSITION)
> AND CHARTPERIODID = 8414
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Dooza" <steve@.whatareyoudooza.tv> wrote in message
> news:ehaP51gqEHA.1992@.TK2MSFTNGP09.phx.gbl...
CHARTPERIODID[vbcol=seagreen]
crazy[vbcol=seagreen]
cant[vbcol=seagreen]
is
>
|||Hi Jacco,
I was wondering if you could give me some further help relating to this same
table.
I want to display the total number of charts submitted in each Chart Period.
Since there are up to 20 rows per DMCUSERID and per CHARTPERIODID I thought
using DISTINCT would help. This is what I have tried:
SELECT CHARTPERIODID, COUNT(DISTINCT DMCUSERID) AS CHARTS
FROM dbo.UserChartEntry
GROUP BY CHARTPERIODID
Unfortunatly I get a Timeout when running this. I am doing this correctly?
Is there another way to do what I want that wont Timeout?
Any help would be great.
Steve
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:%23Y7ZVChqEHA.1712@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Just add it at the end:
> DELETE FROM userchartentry
> WHERE EXISTS(SELECT NULL FROM userchartentry u1
> WHERE u1.ID < userchartentry .ID
> AND u1.DMCUSERID = userchartentry .DMCUSERID
> AND u1.CHARTPERIODID = userchartentry .CHARTPERIODID
> AND u1.TRACKID = userchartentry .TRACKID
> AND u1.POSITION = userchartentry .POSITION)
> AND CHARTPERIODID = 8414
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Dooza" <steve@.whatareyoudooza.tv> wrote in message
> news:ehaP51gqEHA.1992@.TK2MSFTNGP09.phx.gbl...
CHARTPERIODID[vbcol=seagreen]
crazy[vbcol=seagreen]
cant[vbcol=seagreen]
is
>
|||Dooza
You will be better of to use an indexed view for such kind of report
Look at below example written Steve Kass.
reate table T (
i int,
filler char(1000) default 'abc'
)
go
create view T_count with schemabinding as
select
cast(i as bit) as val,
count_big(*) T_count
from dbo.T group by cast(i as bit)
go
create unique clustered index T_count_uci on T_count(val)
go
insert into T(i)
select OrderID
from Northwind..[Order Details]
go
set statistics io on
select count(*) from T
go
select sum(T_count) from T_count with (noexpand)
go
set statistics io off
-- uses an efficient query plan on the materialized view
go
drop view T_count
drop table T
"Dooza" <steve@.whatareyoudooza.tv> wrote in message
news:%23PXLHMtqEHA.2724@.TK2MSFTNGP14.phx.gbl...
> Hi Jacco,
> I was wondering if you could give me some further help relating to this
same
> table.
> I want to display the total number of charts submitted in each Chart
Period.
> Since there are up to 20 rows per DMCUSERID and per CHARTPERIODID I
thought
> using DISTINCT would help. This is what I have tried:
> SELECT CHARTPERIODID, COUNT(DISTINCT DMCUSERID) AS CHARTS
> FROM dbo.UserChartEntry
> GROUP BY CHARTPERIODID
> Unfortunatly I get a Timeout when running this. I am doing this correctly?
> Is there another way to do what I want that wont Timeout?
> Any help would be great.
> Steve
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid >
wrote[vbcol=seagreen]
> in message news:%23Y7ZVChqEHA.1712@.tk2msftngp13.phx.gbl...
> CHARTPERIODID
make[vbcol=seagreen]
tracks,[vbcol=seagreen]
> crazy
chart
> cant
> is
>
|||Hi Uri,
Thank you for the help, but that really is beyond my abilities. I was
thinking more along the lines of using EXISTS but havent really figured it
all out yet.
Thanks anyway!
Dooza
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uHbtiVtqEHA.3464@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Dooza
> You will be better of to use an indexed view for such kind of report
> Look at below example written Steve Kass.
> reate table T (
> i int,
> filler char(1000) default 'abc'
> )
> go
> create view T_count with schemabinding as
> select
> cast(i as bit) as val,
> count_big(*) T_count
> from dbo.T group by cast(i as bit)
> go
> create unique clustered index T_count_uci on T_count(val)
> go
> insert into T(i)
> select OrderID
> from Northwind..[Order Details]
> go
> set statistics io on
> select count(*) from T
> go
> select sum(T_count) from T_count with (noexpand)
> go
> set statistics io off
> -- uses an efficient query plan on the materialized view
> go
> drop view T_count
> drop table T
> "Dooza" <steve@.whatareyoudooza.tv> wrote in message
> news:%23PXLHMtqEHA.2724@.TK2MSFTNGP14.phx.gbl...
> same
> Period.
> thought
correctly?[vbcol=seagreen]
> wrote
<jacco.please.reply@.to.newsgroups.mvps.org.invalid >[vbcol=seagreen]
> make
> tracks,
> chart
beer
>

Duplicate emails being sent for subscription

Hi folks,
We have a report that is emailed to three users (one "To" and two "Cc")
at 6:45am every weekday.
As of applying SP2 earlier this week, each user is receiving two copies
(generated about 2 seconds apart) in their inbox.
There's one curious difference between the two emails: One comes from
'sqlreports@.ourdomain.com.au' and the other comes from
'SQLReports@.ourdomain.com.au'. The case of the sender name is the only
difference.
Has anyone any clues as to where I can look to resolve this?
Cheers,
Matt HamiltonThat is certainly strange. The from user comes from the config file and
should be unique to the report server. Did you have a web farm set up? Is
it possible there is another Report Server pointed at this DB.
Things you can look at:
Check the subscription tab of the report as a user with Content Manager
permission and see if there are two subscriptions.
How many rows are in the keys table?
Check the ReportServerService<timestamp>.log file and see if the
subscription is being processed more then once.
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"mabster" <mhamilton@.qafmeats.nospam.com.au> wrote in message
news:eYIb4ZHTFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Hi folks,
> We have a report that is emailed to three users (one "To" and two "Cc") at
> 6:45am every weekday.
> As of applying SP2 earlier this week, each user is receiving two copies
> (generated about 2 seconds apart) in their inbox.
> There's one curious difference between the two emails: One comes from
> 'sqlreports@.ourdomain.com.au' and the other comes from
> 'SQLReports@.ourdomain.com.au'. The case of the sender name is the only
> difference.
> Has anyone any clues as to where I can look to resolve this?
> Cheers,
> Matt Hamilton|||Daniel Reib (MSFT) wrote:
> That is certainly strange. The from user comes from the config file and
> should be unique to the report server. Did you have a web farm set up? Is
> it possible there is another Report Server pointed at this DB.
> Things you can look at:
> Check the subscription tab of the report as a user with Content Manager
> permission and see if there are two subscriptions.
> How many rows are in the keys table?
> Check the ReportServerService<timestamp>.log file and see if the
> subscription is being processed more then once.
>
Thanks for the prompt reply, Daniel.
I'm going to go bitchslap the developer who asked me about this (and I
should have looked harder at it myself before posting, so that's one
more bitchslap).
She'd set the same subscription up on two different servers. Not a web
farm or anything, just duplicate reports each emailing themselves at the
same time.
Thanks again,
Matt|||Well, that's good to hear. Be nice to the developers!!! Occasionally (but
rarely) they do make mistakes. :)
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"mabster" <mhamilton@.qafmeats.nospam.com.au> wrote in message
news:%237Qwt$HTFHA.336@.TK2MSFTNGP09.phx.gbl...
> Daniel Reib (MSFT) wrote:
>> That is certainly strange. The from user comes from the config file and
>> should be unique to the report server. Did you have a web farm set up?
>> Is it possible there is another Report Server pointed at this DB.
>> Things you can look at:
>> Check the subscription tab of the report as a user with Content Manager
>> permission and see if there are two subscriptions.
>> How many rows are in the keys table?
>> Check the ReportServerService<timestamp>.log file and see if the
>> subscription is being processed more then once.
>>
> Thanks for the prompt reply, Daniel.
> I'm going to go bitchslap the developer who asked me about this (and I
> should have looked harder at it myself before posting, so that's one more
> bitchslap).
> She'd set the same subscription up on two different servers. Not a web
> farm or anything, just duplicate reports each emailing themselves at the
> same time.
> Thanks again,
> Matt

duplicate column data

In a table I have users registering for a mailing. The mailing should be
limited to one per household, so I would like to exclude all but 1 from each
unique home address. There is a unique id for each registrant, but its
possible another family member could have registered more than once with the
same home address. Whats the best way to do this?
TIA
VanIt depends on how the family members are represented in your schema. Is
there some kind of relationship which identifies one registrant as a family
member of another? Is so, to which registrant should the mailing be
addressed?
Please post your table schema, a few sample data & expected results so that
others can better understand your requirements.
Based on similar requests posted in this forum, the query most likely look
something along the lines of:
SELECT *
FROM tbl t1
WHERE < registrant Identifier > =
( SELECT MAX( < registrant Identifier > )
FROM tbl t2
WHERE t2. < address Identifier > = t1. < address Identifier > )
where < registrant Identifier > is the column or set of columns which
uniquely identifies a registrant while < address Identifier > is column or
set of columns which uniquely identifies an address.
Anith|||Thanks for your reply. Unfortunately I got an out of memory exception when I
tried that query.
As you suggested to post my current query looks like this:
SELECT FirstName, LastName, Email, Address1, Address2, City, State, Zip
FROM Orange_Members
INNER JOIN Orange_MembersPromo ON Orange_Members.RegId =
Orange_MembersPromo.RegId
WHERE(Orange_MembersPromo.PromoId = 600)
In the Orange_Members table there may be Address1 values that are duplicate.
There is a unique id primary key in Orange_Members called RegID.
I would like to return every unique Address1 row. In otherwords if Address1
has:
120 Greene Street twice, I would only like to have the first one that
occurs(actually I really dont care which row it is, as long as it only comes
up once) -- and of course any Address that is only in one row in the DB
should be returned by the select also.
TIA

> Based on similar requests posted in this forum, the query most likely look
> something along the lines of:
> SELECT *
> FROM tbl t1
> WHERE < registrant Identifier > =
> ( SELECT MAX( < registrant Identifier > )
> FROM tbl t2
> WHERE t2. < address Identifier > = t1. < address Identifier > )
> where < registrant Identifier > is the column or set of columns which
> uniquely identifies a registrant while < address Identifier > is column or
> set of columns which uniquely identifies an address.
> --
> Anith|||>> As you suggested to post my current query ..
Actually I asked you to post your table schema, a few sample data & expected
results. For details refer to www.aspfaq.com/5006
Based on your narrative, here is another guesswork:
SELECT *
FROM Orange_Members m1
WHERE m1.RegId = ( SELECT MAX( m2.RegId )
FROM Orange_Members m2
WHERE m2.Address1 = m1.Address1 )
AND m1.PromoId = 600 ;
Anith|||Thanks, that worked!

Dump Excel Sheets

I need to make a gigantic collection of excel sheets searchable from a web interface.
I needentire rows fetched into a webpage depending on the users query.

Mind you I'm not searching the names (filenames) of these excel files, but
the contents inside the excel file. E.g. in a file test.xls, if I search for the word
"test1" from the web interface, the entire row in the excel file containing
the word test1 should be displayed.

One way out is to dump the contents of the excel sheets into a database, and
search the database via ASP.

For this, can anyone tell me how to efficiently dump the contents of an excel
sheet into SQL server?

Or if anyone can suggest an alternate strategy for searching this mammoth
collection of excel files' contents, if would be great.

Thanks a lot.

To dump excel data into database, you have serveral options: DTS (Import/Export Wizard), bcp utility, BULK INSERT, etc. Seach with these key words in SQL Books Online, you can find how to use them.

dumb question

Hello, I'm an MSDE n00b but I do have a basic understanding of SQL Server
2000. I have used Enterprise manager to create/manage users/permissions and
replication before. I am however not familiar with MSDE. I am using MSDE
because I'm playing with asp.net. Since there is no user interface I am
lost. I created my tables and stored procedures via the Web Matrix asp.net
development tool.
1) I need to configure the MSDE 2000 installation user access. Its currently
setup for integrated windows authentication, which I want to keep. But how
do I add/manage the users and their permissions. For example with Enterprise
Manager, even with Integrated authentication, you have to add the
users/groups from your windows domain before SQL server can use them. Do I
need to do this in MSDE? how?
2) how can I view current user accounts on the system? and their
permissions?
3) while I'm at it here... how can I retrieve the currently logged in user
from my asp.net web application?
any info is appreciated. Thanks.
If you have a copy of Enterprise Manager on a different machine you should be
able to connect to the msde install and see it in the familar enterprise
manager.
"djc" wrote:

> Hello, I'm an MSDE n00b but I do have a basic understanding of SQL Server
> 2000. I have used Enterprise manager to create/manage users/permissions and
> replication before. I am however not familiar with MSDE. I am using MSDE
> because I'm playing with asp.net. Since there is no user interface I am
> lost. I created my tables and stored procedures via the Web Matrix asp.net
> development tool.
> 1) I need to configure the MSDE 2000 installation user access. Its currently
> setup for integrated windows authentication, which I want to keep. But how
> do I add/manage the users and their permissions. For example with Enterprise
> Manager, even with Integrated authentication, you have to add the
> users/groups from your windows domain before SQL server can use them. Do I
> need to do this in MSDE? how?
> 2) how can I view current user accounts on the system? and their
> permissions?
> 3) while I'm at it here... how can I retrieve the currently logged in user
> from my asp.net web application?
> any info is appreciated. Thanks.
>
>
|||hi,
txghia58 wrote:
> If you have a copy of Enterprise Manager on a different machine you
> should be able to connect to the msde install and see it in the
> familar enterprise manager.
bu only in development/test scenario... you are not licensed to use SQL
Server Client Tools in production...
you have to resort on home made tools and/or 3rd party tools to manage MSDE
in production...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Dumb Question

I know it sounds kind of dumb. Is there supposed to be a front-end portal
for "Browser" level users? Where can I find them?Yes. It is called report manager. If you have a default installation using
http://<servername>/reports should get to it.
Please see
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_webui_v1_9c4u.asp
for additional information.
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"JL" <JL@.discussions.microsoft.com> wrote in message
news:66452C1F-B8C9-472D-9FD6-5C5E55050020@.microsoft.com...
> I know it sounds kind of dumb. Is there supposed to be a front-end portal
> for "Browser" level users? Where can I find them?|||The Report Manager can be used to browse reports (//machine/Reports if you
kept the defaults) as well as just going to //<machine name>/reportserver.
Just make sure users have permissions to see the reports and folders the
reports are contained in.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"JL" <JL@.discussions.microsoft.com> wrote in message
news:66452C1F-B8C9-472D-9FD6-5C5E55050020@.microsoft.com...
> I know it sounds kind of dumb. Is there supposed to be a front-end portal
> for "Browser" level users? Where can I find them?|||Thanks for your response. I did install the RS successfully. I know there
is a report manager console. But I remember my instructor (RS class) once
showed us a screen with the url like "someServer/portal"..'.. That
interface has report links on the left and blue in the background. Could
this be another Microsoft product'
"Daniel Reib [MSFT]" wrote:
> The Report Manager can be used to browse reports (//machine/Reports if you
> kept the defaults) as well as just going to //<machine name>/reportserver.
> Just make sure users have permissions to see the reports and folders the
> reports are contained in.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "JL" <JL@.discussions.microsoft.com> wrote in message
> news:66452C1F-B8C9-472D-9FD6-5C5E55050020@.microsoft.com...
> > I know it sounds kind of dumb. Is there supposed to be a front-end portal
> > for "Browser" level users? Where can I find them?
>
>|||I have not seen something like you are describing. Perhaps this was
something they wrote?
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"JL" <JL@.discussions.microsoft.com> wrote in message
news:20D9119D-A817-407C-878E-D01B3208367D@.microsoft.com...
> Thanks for your response. I did install the RS successfully. I know
there
> is a report manager console. But I remember my instructor (RS class)
once
> showed us a screen with the url like "someServer/portal"..'.. That
> interface has report links on the left and blue in the background. Could
> this be another Microsoft product'
> "Daniel Reib [MSFT]" wrote:
> > The Report Manager can be used to browse reports (//machine/Reports if
you
> > kept the defaults) as well as just going to //<machine
name>/reportserver.
> > Just make sure users have permissions to see the reports and folders the
> > reports are contained in.
> >
> > --
> > -Daniel
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "JL" <JL@.discussions.microsoft.com> wrote in message
> > news:66452C1F-B8C9-472D-9FD6-5C5E55050020@.microsoft.com...
> > > I know it sounds kind of dumb. Is there supposed to be a front-end
portal
> > > for "Browser" level users? Where can I find them?
> >
> >
> >

DUH!

Some users would ONLY have access through an application role, while
elevated access is controlled through normal logins and roles.
"Bob Castleman" <nomail@.here> wrote in message
news:uiRahnt3EHA.3092@.TK2MSFTNGP10.phx.gbl...
> Some users would ONLY have access through an application role, while
> elevated access is controlled through normal logins and roles.
>
Exactly. ;-)
Rick Sawtell

Dual Processors Xeon Support

Dear SQL Server Users,
How do I configure Sql Server 2000 to supports dual cpu (Xeon)?
Kind Regards
Charles
By default, the installation chooses all the CPUs. You can change it to use
only 1 CPU later.
Gopi
"Charles Tam" <CharlesTam@.discussions.microsoft.com> wrote in message
news:67E77C3C-899A-47E9-AE60-EE98D32C48BC@.microsoft.com...
> Dear SQL Server Users,
> How do I configure Sql Server 2000 to supports dual cpu (Xeon)?
> Kind Regards
> Charles
|||a related question: if you have two instances and one dual cpu xeon,
can you set the processor affinity to use one processor per instance?
if so, does this buy you as much as setting the processor affinity for
two (separate) processors?
thanks,
arthur
|||Hi Rgn,
Could you point me to further information on Sql Server for DUAL CPU
environment?
Kind Regards
Charles
"rgn" wrote:

> By default, the installation chooses all the CPUs. You can change it to use
> only 1 CPU later.
> Gopi
> "Charles Tam" <CharlesTam@.discussions.microsoft.com> wrote in message
> news:67E77C3C-899A-47E9-AE60-EE98D32C48BC@.microsoft.com...
>
>
|||What kind of information? Standard edition or above will use multiple
processors by default. You don't have to do anything.
Andrew J. Kelly SQL MVP
"Charles Tam" <CharlesTam@.discussions.microsoft.com> wrote in message
news:2594534E-DF3A-448A-98DA-5D8325AEBC0C@.microsoft.com...[vbcol=seagreen]
> Hi Rgn,
> Could you point me to further information on Sql Server for DUAL CPU
> environment?
> Kind Regards
> Charles
> "rgn" wrote:
|||Arthur,
Check the Microsoft website for a whitepaper on HT processors. These were
first introduced on Xeon Server processors
and the OS (MicroSoft) does treat them as two logical CPUs even though it is
one Physical CPU.
Microsoft itself mentions that the performance gain is not that significant
(as of WIN2K/SQL2K) as the performance gain
is only about 30% when compared to two Physical CPUs and this is because
WIN2K/SQL2K is not designed to make
use of the Hyper Threading features.
Please check the Microsoft website. I cant recollect the URL as I had read
this 2 years before.
Gopi
"arthur" <alangham@.gmail.com> wrote in message
news:1108394471.350245.286640@.f14g2000cwb.googlegr oups.com...
>a related question: if you have two instances and one dual cpu xeon,
> can you set the processor affinity to use one processor per instance?
> if so, does this buy you as much as setting the processor affinity for
> two (separate) processors?
> thanks,
> arthur
>
|||Hi Andrew
I'm looking for documentation on how to tune-up SQL Server Standard Edition
on Dual CPU Xeon.
Kind Regards
Charles
"Andrew J. Kelly" wrote:

> What kind of information? Standard edition or above will use multiple
> processors by default. You don't have to do anything.
> --
> Andrew J. Kelly SQL MVP
>
> "Charles Tam" <CharlesTam@.discussions.microsoft.com> wrote in message
> news:2594534E-DF3A-448A-98DA-5D8325AEBC0C@.microsoft.com...
>
>
|||There isn't much regarding the dual cpu part. The tuning is mostly the same
as it pertains more to I/O and poor queries or schemas. Have a look at
these:
http://www.microsoft.com/sql/techinf...perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Charles Tam" <CharlesTam@.discussions.microsoft.com> wrote in message
news:B77F9EBF-ACD1-4D20-8AE6-72B80469BC62@.microsoft.com...[vbcol=seagreen]
> Hi Andrew
> I'm looking for documentation on how to tune-up SQL Server Standard
> Edition
> on Dual CPU Xeon.
> Kind Regards
> Charles
> "Andrew J. Kelly" wrote:

Dual Processors Xeon Support

Dear SQL Server Users,
How do I configure Sql Server 2000 to supports dual cpu (Xeon)?
Kind Regards
CharlesBy default, the installation chooses all the CPUs. You can change it to use
only 1 CPU later.
Gopi
"Charles Tam" <CharlesTam@.discussions.microsoft.com> wrote in message
news:67E77C3C-899A-47E9-AE60-EE98D32C48BC@.microsoft.com...
> Dear SQL Server Users,
> How do I configure Sql Server 2000 to supports dual cpu (Xeon)?
> Kind Regards
> Charles|||a related question: if you have two instances and one dual cpu xeon,
can you set the processor affinity to use one processor per instance?
if so, does this buy you as much as setting the processor affinity for
two (separate) processors?
thanks,
arthur|||Hi Rgn,
Could you point me to further information on Sql Server for DUAL CPU
environment?
Kind Regards
Charles
"rgn" wrote:

> By default, the installation chooses all the CPUs. You can change it to us
e
> only 1 CPU later.
> Gopi
> "Charles Tam" <CharlesTam@.discussions.microsoft.com> wrote in message
> news:67E77C3C-899A-47E9-AE60-EE98D32C48BC@.microsoft.com...
>
>|||What kind of information? Standard edition or above will use multiple
processors by default. You don't have to do anything.
Andrew J. Kelly SQL MVP
"Charles Tam" <CharlesTam@.discussions.microsoft.com> wrote in message
news:2594534E-DF3A-448A-98DA-5D8325AEBC0C@.microsoft.com...[vbcol=seagreen]
> Hi Rgn,
> Could you point me to further information on Sql Server for DUAL CPU
> environment?
> Kind Regards
> Charles
> "rgn" wrote:
>|||Arthur,
Check the Microsoft website for a whitepaper on HT processors. These were
first introduced on Xeon Server processors
and the OS (MicroSoft) does treat them as two logical CPUs even though it is
one Physical CPU.
Microsoft itself mentions that the performance gain is not that significant
(as of WIN2K/SQL2K) as the performance gain
is only about 30% when compared to two Physical CPUs and this is because
WIN2K/SQL2K is not designed to make
use of the Hyper Threading features.
Please check the Microsoft website. I cant recollect the URL as I had read
this 2 years before.
Gopi
"arthur" <alangham@.gmail.com> wrote in message
news:1108394471.350245.286640@.f14g2000cwb.googlegroups.com...
>a related question: if you have two instances and one dual cpu xeon,
> can you set the processor affinity to use one processor per instance?
> if so, does this buy you as much as setting the processor affinity for
> two (separate) processors?
> thanks,
> arthur
>|||Hi Andrew
I'm looking for documentation on how to tune-up SQL Server Standard Edition
on Dual CPU Xeon.
Kind Regards
Charles
"Andrew J. Kelly" wrote:

> What kind of information? Standard edition or above will use multiple
> processors by default. You don't have to do anything.
> --
> Andrew J. Kelly SQL MVP
>
> "Charles Tam" <CharlesTam@.discussions.microsoft.com> wrote in message
> news:2594534E-DF3A-448A-98DA-5D8325AEBC0C@.microsoft.com...
>
>|||There isn't much regarding the dual cpu part. The tuning is mostly the same
as it pertains more to I/O and poor queries or schemas. Have a look at
these:
http://www.microsoft.com/sql/techin.../perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Charles Tam" <CharlesTam@.discussions.microsoft.com> wrote in message
news:B77F9EBF-ACD1-4D20-8AE6-72B80469BC62@.microsoft.com...[vbcol=seagreen]
> Hi Andrew
> I'm looking for documentation on how to tune-up SQL Server Standard
> Edition
> on Dual CPU Xeon.
> Kind Regards
> Charles
> "Andrew J. Kelly" wrote:
>

Dual Processors Xeon Support

Dear SQL Server Users,
How do I configure Sql Server 2000 to supports dual cpu (Xeon)?
Kind Regards
CharlesBy default, the installation chooses all the CPUs. You can change it to use
only 1 CPU later.
Gopi
"Charles Tam" <CharlesTam@.discussions.microsoft.com> wrote in message
news:67E77C3C-899A-47E9-AE60-EE98D32C48BC@.microsoft.com...
> Dear SQL Server Users,
> How do I configure Sql Server 2000 to supports dual cpu (Xeon)?
> Kind Regards
> Charles|||a related question: if you have two instances and one dual cpu xeon,
can you set the processor affinity to use one processor per instance?
if so, does this buy you as much as setting the processor affinity for
two (separate) processors?
thanks,
arthur|||Hi Rgn,
Could you point me to further information on Sql Server for DUAL CPU
environment?
Kind Regards
Charles
"rgn" wrote:
> By default, the installation chooses all the CPUs. You can change it to use
> only 1 CPU later.
> Gopi
> "Charles Tam" <CharlesTam@.discussions.microsoft.com> wrote in message
> news:67E77C3C-899A-47E9-AE60-EE98D32C48BC@.microsoft.com...
> > Dear SQL Server Users,
> >
> > How do I configure Sql Server 2000 to supports dual cpu (Xeon)?
> >
> > Kind Regards
> > Charles
>
>|||What kind of information? Standard edition or above will use multiple
processors by default. You don't have to do anything.
--
Andrew J. Kelly SQL MVP
"Charles Tam" <CharlesTam@.discussions.microsoft.com> wrote in message
news:2594534E-DF3A-448A-98DA-5D8325AEBC0C@.microsoft.com...
> Hi Rgn,
> Could you point me to further information on Sql Server for DUAL CPU
> environment?
> Kind Regards
> Charles
> "rgn" wrote:
>> By default, the installation chooses all the CPUs. You can change it to
>> use
>> only 1 CPU later.
>> Gopi
>> "Charles Tam" <CharlesTam@.discussions.microsoft.com> wrote in message
>> news:67E77C3C-899A-47E9-AE60-EE98D32C48BC@.microsoft.com...
>> > Dear SQL Server Users,
>> >
>> > How do I configure Sql Server 2000 to supports dual cpu (Xeon)?
>> >
>> > Kind Regards
>> > Charles
>>|||Arthur,
Check the Microsoft website for a whitepaper on HT processors. These were
first introduced on Xeon Server processors
and the OS (MicroSoft) does treat them as two logical CPUs even though it is
one Physical CPU.
Microsoft itself mentions that the performance gain is not that significant
(as of WIN2K/SQL2K) as the performance gain
is only about 30% when compared to two Physical CPUs and this is because
WIN2K/SQL2K is not designed to make
use of the Hyper Threading features.
Please check the Microsoft website. I cant recollect the URL as I had read
this 2 years before.
Gopi
"arthur" <alangham@.gmail.com> wrote in message
news:1108394471.350245.286640@.f14g2000cwb.googlegroups.com...
>a related question: if you have two instances and one dual cpu xeon,
> can you set the processor affinity to use one processor per instance?
> if so, does this buy you as much as setting the processor affinity for
> two (separate) processors?
> thanks,
> arthur
>|||Hi Andrew
I'm looking for documentation on how to tune-up SQL Server Standard Edition
on Dual CPU Xeon.
Kind Regards
Charles
"Andrew J. Kelly" wrote:
> What kind of information? Standard edition or above will use multiple
> processors by default. You don't have to do anything.
> --
> Andrew J. Kelly SQL MVP
>
> "Charles Tam" <CharlesTam@.discussions.microsoft.com> wrote in message
> news:2594534E-DF3A-448A-98DA-5D8325AEBC0C@.microsoft.com...
> > Hi Rgn,
> >
> > Could you point me to further information on Sql Server for DUAL CPU
> > environment?
> >
> > Kind Regards
> > Charles
> >
> > "rgn" wrote:
> >
> >> By default, the installation chooses all the CPUs. You can change it to
> >> use
> >> only 1 CPU later.
> >>
> >> Gopi
> >>
> >> "Charles Tam" <CharlesTam@.discussions.microsoft.com> wrote in message
> >> news:67E77C3C-899A-47E9-AE60-EE98D32C48BC@.microsoft.com...
> >> > Dear SQL Server Users,
> >> >
> >> > How do I configure Sql Server 2000 to supports dual cpu (Xeon)?
> >> >
> >> > Kind Regards
> >> > Charles
> >>
> >>
> >>
>
>|||There isn't much regarding the dual cpu part. The tuning is mostly the same
as it pertains more to I/O and poor queries or schemas. Have a look at
these:
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Charles Tam" <CharlesTam@.discussions.microsoft.com> wrote in message
news:B77F9EBF-ACD1-4D20-8AE6-72B80469BC62@.microsoft.com...
> Hi Andrew
> I'm looking for documentation on how to tune-up SQL Server Standard
> Edition
> on Dual CPU Xeon.
> Kind Regards
> Charles
> "Andrew J. Kelly" wrote:
>> What kind of information? Standard edition or above will use multiple
>> processors by default. You don't have to do anything.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Charles Tam" <CharlesTam@.discussions.microsoft.com> wrote in message
>> news:2594534E-DF3A-448A-98DA-5D8325AEBC0C@.microsoft.com...
>> > Hi Rgn,
>> >
>> > Could you point me to further information on Sql Server for DUAL CPU
>> > environment?
>> >
>> > Kind Regards
>> > Charles
>> >
>> > "rgn" wrote:
>> >
>> >> By default, the installation chooses all the CPUs. You can change it
>> >> to
>> >> use
>> >> only 1 CPU later.
>> >>
>> >> Gopi
>> >>
>> >> "Charles Tam" <CharlesTam@.discussions.microsoft.com> wrote in message
>> >> news:67E77C3C-899A-47E9-AE60-EE98D32C48BC@.microsoft.com...
>> >> > Dear SQL Server Users,
>> >> >
>> >> > How do I configure Sql Server 2000 to supports dual cpu (Xeon)?
>> >> >
>> >> > Kind Regards
>> >> > Charles
>> >>
>> >>
>> >>
>>

Dual Authentication with web and rs farm using remote sql server

I am having a problem with users being prompted to authenticate twice within
an application. I know this is related to the current design but for
scalability and isolation I want to keep the infrastructure design as is.
Currently I have two IIS 6 2k3 servers in an NLB. I have a web application
installed on this which is a front end for running reports. I then have two
IIS 6 2k3 servers running sql 2000 RS in an NLB farm. Finally there is a
Clustered SQL 2000 server where the application database and RS databases
exist. The application requires Basic w/SSL at the website level. This is
the first authentication. When the users go to run a report the first report
goes to the RS Farm and the users are authenticated once again. Once they
have authenticated by the web farm and the RS farm they no longer have to
authenticate but I'm trying to get it to a SSO.
From my experience, if I try using Integrated authentication then the UN and
Pass doesn't get passed onto the sql server and they cannot authenticate to
access the app database. If I try using integrated ont he RS farm I have an
issue where the UN and pass doesn't get passed to sql there. Based on what I
am seeing the fact that it requires 2 logons, 1 per farm, actually makes
sense but I would think this would be the most scalable and isolated design
you could have so it seems to me that there should just be a way for the SSO.
I would like to be able to do this at an admin or infrastructure design
level as the application is from a third party vendor and I don't want to try
and get them to recode anything. Any help would be appreciated.The problem was with Windows 2003 SP1 and IIS6.
Had to disable loopback check and then change RS to using Integrated
authentication. After that I was able to get in locally to /reports
http://support.microsoft.com/default.aspx?scid=kb;en-us;896861
"Chris Fauver" wrote:
> I am having a problem with users being prompted to authenticate twice within
> an application. I know this is related to the current design but for
> scalability and isolation I want to keep the infrastructure design as is.
> Currently I have two IIS 6 2k3 servers in an NLB. I have a web application
> installed on this which is a front end for running reports. I then have two
> IIS 6 2k3 servers running sql 2000 RS in an NLB farm. Finally there is a
> Clustered SQL 2000 server where the application database and RS databases
> exist. The application requires Basic w/SSL at the website level. This is
> the first authentication. When the users go to run a report the first report
> goes to the RS Farm and the users are authenticated once again. Once they
> have authenticated by the web farm and the RS farm they no longer have to
> authenticate but I'm trying to get it to a SSO.
> From my experience, if I try using Integrated authentication then the UN and
> Pass doesn't get passed onto the sql server and they cannot authenticate to
> access the app database. If I try using integrated ont he RS farm I have an
> issue where the UN and pass doesn't get passed to sql there. Based on what I
> am seeing the fact that it requires 2 logons, 1 per farm, actually makes
> sense but I would think this would be the most scalable and isolated design
> you could have so it seems to me that there should just be a way for the SSO.
>
> I would like to be able to do this at an admin or infrastructure design
> level as the application is from a third party vendor and I don't want to try
> and get them to recode anything. Any help would be appreciated.

DTS will not send attachment using sp_send_cdosysmail

I have created a DTS that exports the results of a query to an excel
sheet and the delivers the xls to a group of users.
The DTS will send the the email but not the attachment; however, if I
execute the same statement using QA, the attachment works fine. I have
the DTS authenticating to the location of the file using xp_cmdshell
earlier in the process.
Below is the SQL statement, I am using:
DECLARE @.Body varchar(4000)
DECLARE @.EmailUsers VARCHAR(1024)
DECLARE @.Attmt VARCHAR(1024)
SELECT @.Body = 'Attached is the most recent CustCD customer
listing.'
SELECT @.EmailUsers = COALESCE(@.EmailUsers + ';', '') + UserEmail
FROM EmailUsers
WHERE (CustCD = 1)
SELECT @.Attmt='\\Server1\C$\c3.xls'
EXEC sp_send_cdosysmail 'abc@.abc.com', @.EmailUsers, 'CustCD
XLS',@.Body, @.Attmt
Does anyone have any ideas?
Thanks,
Chris
Assuming that the connection in your Execute SQL Task uses a database in
which the sp_send_cdosysmail is not defined, you might want to try to use a
three-part name for calling the sp_send_cdosysmail e.g. EXEC
master.dbo.sp_send_cdosysmail ...
-- Oskar
"ChrisP" wrote:

> I have created a DTS that exports the results of a query to an excel
> sheet and the delivers the xls to a group of users.
> The DTS will send the the email but not the attachment; however, if I
> execute the same statement using QA, the attachment works fine. I have
> the DTS authenticating to the location of the file using xp_cmdshell
> earlier in the process.
> Below is the SQL statement, I am using:
> DECLARE @.Body varchar(4000)
> DECLARE @.EmailUsers VARCHAR(1024)
> DECLARE @.Attmt VARCHAR(1024)
> SELECT @.Body = 'Attached is the most recent CustCD customer
> listing.'
> SELECT @.EmailUsers = COALESCE(@.EmailUsers + ';', '') + UserEmail
> FROM EmailUsers
> WHERE (CustCD = 1)
> SELECT @.Attmt='\\Server1\C$\c3.xls'
> EXEC sp_send_cdosysmail 'abc@.abc.com', @.EmailUsers, 'CustCD
> XLS',@.Body, @.Attmt
> Does anyone have any ideas?
> Thanks,
> Chris
>
|||If the suggestion below does not help you could try to set on the logging for
the package and check if the UNC path is indeed accessible by executing for
example EXEC master.dbo.xp_cmdshell 'dir \\Server1\C$\'
-- Oskar
"Oskar" wrote:
[vbcol=seagreen]
> Assuming that the connection in your Execute SQL Task uses a database in
> which the sp_send_cdosysmail is not defined, you might want to try to use a
> three-part name for calling the sp_send_cdosysmail e.g. EXEC
> master.dbo.sp_send_cdosysmail ...
> -- Oskar
> "ChrisP" wrote:

DTS will not send attachment using sp_send_cdosysmail

I have created a DTS that exports the results of a query to an excel
sheet and the delivers the xls to a group of users.
The DTS will send the the email but not the attachment; however, if I
execute the same statement using QA, the attachment works fine. I have
the DTS authenticating to the location of the file using xp_cmdshell
earlier in the process.
Below is the SQL statement, I am using:
DECLARE @.Body varchar(4000)
DECLARE @.EmailUsers VARCHAR(1024)
DECLARE @.Attmt VARCHAR(1024)
SELECT @.Body = 'Attached is the most recent CustCD customer
listing.'
SELECT @.EmailUsers = COALESCE(@.EmailUsers + ';', '') + UserEmail
FROM EmailUsers
WHERE (CustCD = 1)
SELECT @.Attmt='\\Server1\C$\c3.xls'
EXEC sp_send_cdosysmail 'abc@.abc.com', @.EmailUsers, 'CustCD
XLS',@.Body, @.Attmt
Does anyone have any ideas?
Thanks,
ChrisAssuming that the connection in your Execute SQL Task uses a database in
which the sp_send_cdosysmail is not defined, you might want to try to use a
three-part name for calling the sp_send_cdosysmail e.g. EXEC
master.dbo.sp_send_cdosysmail ...
-- Oskar
"ChrisP" wrote:

> I have created a DTS that exports the results of a query to an excel
> sheet and the delivers the xls to a group of users.
> The DTS will send the the email but not the attachment; however, if I
> execute the same statement using QA, the attachment works fine. I have
> the DTS authenticating to the location of the file using xp_cmdshell
> earlier in the process.
> Below is the SQL statement, I am using:
> DECLARE @.Body varchar(4000)
> DECLARE @.EmailUsers VARCHAR(1024)
> DECLARE @.Attmt VARCHAR(1024)
> SELECT @.Body = 'Attached is the most recent CustCD customer
> listing.'
> SELECT @.EmailUsers = COALESCE(@.EmailUsers + ';', '') + UserEmail
> FROM EmailUsers
> WHERE (CustCD = 1)
> SELECT @.Attmt='\\Server1\C$\c3.xls'
> EXEC sp_send_cdosysmail 'abc@.abc.com', @.EmailUsers, 'CustCD
> XLS',@.Body, @.Attmt
> Does anyone have any ideas?
> Thanks,
> Chris
>|||If the suggestion below does not help you could try to set on the logging fo
r
the package and check if the UNC path is indeed accessible by executing for
example EXEC master.dbo.xp_cmdshell 'dir \\Server1\C$'
-- Oskar
"Oskar" wrote:
[vbcol=seagreen]
> Assuming that the connection in your Execute SQL Task uses a database in
> which the sp_send_cdosysmail is not defined, you might want to try to use
a
> three-part name for calling the sp_send_cdosysmail e.g. EXEC
> master.dbo.sp_send_cdosysmail ...
> -- Oskar
> "ChrisP" wrote:
>

DTS will not send attachment using sp_send_cdosysmail

I have created a DTS that exports the results of a query to an excel
sheet and the delivers the xls to a group of users.
The DTS will send the the email but not the attachment; however, if I
execute the same statement using QA, the attachment works fine. I have
the DTS authenticating to the location of the file using xp_cmdshell
earlier in the process.
Below is the SQL statement, I am using:
DECLARE @.Body varchar(4000)
DECLARE @.EmailUsers VARCHAR(1024)
DECLARE @.Attmt VARCHAR(1024)
SELECT @.Body = 'Attached is the most recent CustCD customer
listing.'
SELECT @.EmailUsers = COALESCE(@.EmailUsers + ';', '') + UserEmail
FROM EmailUsers
WHERE (CustCD = 1)
SELECT @.Attmt='\\Server1\C$\c3.xls'
EXEC sp_send_cdosysmail 'abc@.abc.com', @.EmailUsers, 'CustCD
XLS',@.Body, @.Attmt
Does anyone have any ideas?
Thanks,
ChrisAssuming that the connection in your Execute SQL Task uses a database in
which the sp_send_cdosysmail is not defined, you might want to try to use a
three-part name for calling the sp_send_cdosysmail e.g. EXEC
master.dbo.sp_send_cdosysmail ...
-- Oskar
"ChrisP" wrote:
> I have created a DTS that exports the results of a query to an excel
> sheet and the delivers the xls to a group of users.
> The DTS will send the the email but not the attachment; however, if I
> execute the same statement using QA, the attachment works fine. I have
> the DTS authenticating to the location of the file using xp_cmdshell
> earlier in the process.
> Below is the SQL statement, I am using:
> DECLARE @.Body varchar(4000)
> DECLARE @.EmailUsers VARCHAR(1024)
> DECLARE @.Attmt VARCHAR(1024)
> SELECT @.Body = 'Attached is the most recent CustCD customer
> listing.'
> SELECT @.EmailUsers = COALESCE(@.EmailUsers + ';', '') + UserEmail
> FROM EmailUsers
> WHERE (CustCD = 1)
> SELECT @.Attmt='\\Server1\C$\c3.xls'
> EXEC sp_send_cdosysmail 'abc@.abc.com', @.EmailUsers, 'CustCD
> XLS',@.Body, @.Attmt
> Does anyone have any ideas?
> Thanks,
> Chris
>|||If the suggestion below does not help you could try to set on the logging for
the package and check if the UNC path is indeed accessible by executing for
example EXEC master.dbo.xp_cmdshell 'dir \\Server1\C$\'
-- Oskar
"Oskar" wrote:
> Assuming that the connection in your Execute SQL Task uses a database in
> which the sp_send_cdosysmail is not defined, you might want to try to use a
> three-part name for calling the sp_send_cdosysmail e.g. EXEC
> master.dbo.sp_send_cdosysmail ...
> -- Oskar
> "ChrisP" wrote:
> > I have created a DTS that exports the results of a query to an excel
> > sheet and the delivers the xls to a group of users.
> > The DTS will send the the email but not the attachment; however, if I
> > execute the same statement using QA, the attachment works fine. I have
> > the DTS authenticating to the location of the file using xp_cmdshell
> > earlier in the process.
> > Below is the SQL statement, I am using:
> > DECLARE @.Body varchar(4000)
> > DECLARE @.EmailUsers VARCHAR(1024)
> > DECLARE @.Attmt VARCHAR(1024)
> > SELECT @.Body = 'Attached is the most recent CustCD customer
> > listing.'
> > SELECT @.EmailUsers = COALESCE(@.EmailUsers + ';', '') + UserEmail
> > FROM EmailUsers
> > WHERE (CustCD = 1)
> > SELECT @.Attmt='\\Server1\C$\c3.xls'
> > EXEC sp_send_cdosysmail 'abc@.abc.com', @.EmailUsers, 'CustCD
> > XLS',@.Body, @.Attmt
> >
> > Does anyone have any ideas?
> > Thanks,
> > Chris
> >
> >