Showing posts with label web. Show all posts
Showing posts with label web. Show all posts

Friday, March 30, 2012

IAuthorizationExtension and RoleProvider

We have developed a custom asp.net 2.0 RoleProvider to Authorize access to
different areas of our web app so that administrators of our application
could control access rather than IT staff. The custom RoleProvider reads
user and role information from a SQLServer 2005 Database. We are not using a
custom MembershipProvider. We are now trying to integrate Reporting Services
2005 with the Web App. I want the Roles used to authorize access to reports
be the same roles that are used to authorize access to the pages in my web
app. I would like to have reporting services use my custom RoleProvider. It
is my understanding that I need to create a class that implements
IAuthorizationExtension to do this.
I have the example from the Teo Lachev book. Not exacly what i needed, but
of figre if I could get that one running, I could modify it to call my custom
RoleProvider. When I implemented the sample from the book, I get an
exceiption when it calls the BinaryFormatter Deserialize method to
deserialize SecDesc item that was passed to GetAccess. The error message i
get is 'The input stream is not in a valid binary format. The starting
contents (in bytes) are :06-05-00-9c...'
Can anyone how a custom Authorization Extension is supposed to use the
Security descriptors it gets from RS?
Has anyone managed to use asp.net 2.0 RoleProviders with RS2005? Is there
any good documentation on how to implement and deploy an
IAuthorizationExtension? Should I be taking another approach?
It Seems to me that there ought to be [supplied by microsoft] an
implementation of the IAuthorizationExtension that calls a RoleProvider to
determine what groups a user is in, but I have not been able to find it.
Without this ability, the RoleProvider functionality is useless, because IT
staff would need to maintain AD Groups to control access to reports while an
Application Administrator would need to maintain RoleProvider Groups that
control access to my pages. It sounds like if someone wants to use
Reporting services, the should not be using a custom RoleProvider.Just in case anyone else experiences this problem in the future , I thought
I'd document what I've figured out so far. This is probably all buried in
some documentation somewhere, but I've been unable to find it.
When writing my pervious post, I had installed RS2005 and then installed a
custom Authorization Extension and was getting an error saying 'The
input stream is not in a valid binary format...'. when trying to access the
root folder of the report server. I had copied much of the code from the
Lachev book.
The root folder of the report server was created at the time I installed
RS2005. My custom Authorization Extension was not in place at the time the
root folder was created , so the ACL for the folder was created serialized
using the CreateSecurityDescriptor method of the default Authorization
Extension that comes with RS2005 (i.e. the one that works with AD groups).
I could browse to the root folder of the report server at this time with no
problem.
After I installed my custom Authorization Extension and browsed to the root
folder, RS Passed the ACL created and serialized by the default
Authorization Extension to my custom Authorization Extension. This caused
the issue. The CreateSecurityDescriptor and the CheckAccess methods work
hand-in-hand. The ACL create by CreateSecurityDescriptor of the default
Authorization Extension could not be deserialized by the GetAccess method of
my Custom Authorization Extension.
Afterwards, I created a new folder. Because this new folder was created
with my extension in place, the ACL was serialized by my own
CreateSecurityDescriptor method, and thus could be deserialized by my own
GetAccess method with no problem.
I just can't access the root folder after having installedmy extension,
because the security extension was not created by my code!.
"gover" <gover@.discussions.microsoft.com> wrote in message
news:03E811C4-6811-45EB-A071-0CAF14771C28@.microsoft.com...
> We have developed a custom asp.net 2.0 RoleProvider to Authorize access
> to
> different areas of our web app so that administrators of our application
> could control access rather than IT staff. The custom RoleProvider reads
> user and role information from a SQLServer 2005 Database. We are not using
> a
> custom MembershipProvider. We are now trying to integrate Reporting
> Services
> 2005 with the Web App. I want the Roles used to authorize access to
> reports
> be the same roles that are used to authorize access to the pages in my web
> app. I would like to have reporting services use my custom RoleProvider.
> It
> is my understanding that I need to create a class that implements
> IAuthorizationExtension to do this.
> I have the example from the Teo Lachev book. Not exacly what i needed, but
> of figre if I could get that one running, I could modify it to call my
> custom
> RoleProvider. When I implemented the sample from the book, I get an
> exceiption when it calls the BinaryFormatter Deserialize method to
> deserialize SecDesc item that was passed to GetAccess. The error message
> i
> get is 'The input stream is not in a valid binary format. The starting
> contents (in bytes) are :06-05-00-9c...'
> Can anyone how a custom Authorization Extension is supposed to use the
> Security descriptors it gets from RS?
> Has anyone managed to use asp.net 2.0 RoleProviders with RS2005? Is
> there
> any good documentation on how to implement and deploy an
> IAuthorizationExtension? Should I be taking another approach?
>
> It Seems to me that there ought to be [supplied by microsoft] an
> implementation of the IAuthorizationExtension that calls a RoleProvider
> to
> determine what groups a user is in, but I have not been able to find it.
> Without this ability, the RoleProvider functionality is useless, because
> IT
> staff would need to maintain AD Groups to control access to reports while
> an
> Application Administrator would need to maintain RoleProvider Groups that
> control access to my pages. It sounds like if someone wants to use
> Reporting services, the should not be using a custom RoleProvider.
>
>|||Did you ever figure out how to cleare the old security descriptor ?
"gover" wrote:
> Just in case anyone else experiences this problem in the future , I thought
> I'd document what I've figured out so far. This is probably all buried in
> some documentation somewhere, but I've been unable to find it.
> When writing my pervious post, I had installed RS2005 and then installed a
> custom Authorization Extension and was getting an error saying 'The
> input stream is not in a valid binary format...'. when trying to access the
> root folder of the report server. I had copied much of the code from the
> Lachev book.
> The root folder of the report server was created at the time I installed
> RS2005. My custom Authorization Extension was not in place at the time the
> root folder was created , so the ACL for the folder was created serialized
> using the CreateSecurityDescriptor method of the default Authorization
> Extension that comes with RS2005 (i.e. the one that works with AD groups).
> I could browse to the root folder of the report server at this time with no
> problem.
> After I installed my custom Authorization Extension and browsed to the root
> folder, RS Passed the ACL created and serialized by the default
> Authorization Extension to my custom Authorization Extension. This caused
> the issue. The CreateSecurityDescriptor and the CheckAccess methods work
> hand-in-hand. The ACL create by CreateSecurityDescriptor of the default
> Authorization Extension could not be deserialized by the GetAccess method of
> my Custom Authorization Extension.
> Afterwards, I created a new folder. Because this new folder was created
> with my extension in place, the ACL was serialized by my own
> CreateSecurityDescriptor method, and thus could be deserialized by my own
> GetAccess method with no problem.
> I just can't access the root folder after having installedmy extension,
> because the security extension was not created by my code!.
>
> "gover" <gover@.discussions.microsoft.com> wrote in message
> news:03E811C4-6811-45EB-A071-0CAF14771C28@.microsoft.com...
> > We have developed a custom asp.net 2.0 RoleProvider to Authorize access
> > to
> > different areas of our web app so that administrators of our application
> > could control access rather than IT staff. The custom RoleProvider reads
> > user and role information from a SQLServer 2005 Database. We are not using
> > a
> > custom MembershipProvider. We are now trying to integrate Reporting
> > Services
> > 2005 with the Web App. I want the Roles used to authorize access to
> > reports
> > be the same roles that are used to authorize access to the pages in my web
> > app. I would like to have reporting services use my custom RoleProvider.
> > It
> > is my understanding that I need to create a class that implements
> > IAuthorizationExtension to do this.
> >
> > I have the example from the Teo Lachev book. Not exacly what i needed, but
> > of figre if I could get that one running, I could modify it to call my
> > custom
> > RoleProvider. When I implemented the sample from the book, I get an
> > exceiption when it calls the BinaryFormatter Deserialize method to
> > deserialize SecDesc item that was passed to GetAccess. The error message
> > i
> > get is 'The input stream is not in a valid binary format. The starting
> > contents (in bytes) are :06-05-00-9c...'
> >
> > Can anyone how a custom Authorization Extension is supposed to use the
> > Security descriptors it gets from RS?
> >
> > Has anyone managed to use asp.net 2.0 RoleProviders with RS2005? Is
> > there
> > any good documentation on how to implement and deploy an
> > IAuthorizationExtension? Should I be taking another approach?
> >
> >
> > It Seems to me that there ought to be [supplied by microsoft] an
> > implementation of the IAuthorizationExtension that calls a RoleProvider
> > to
> > determine what groups a user is in, but I have not been able to find it.
> > Without this ability, the RoleProvider functionality is useless, because
> > IT
> > staff would need to maintain AD Groups to control access to reports while
> > an
> > Application Administrator would need to maintain RoleProvider Groups that
> > control access to my pages. It sounds like if someone wants to use
> > Reporting services, the should not be using a custom RoleProvider.
> >
> >
> >
> >
>
>

Monday, March 26, 2012

I wish to Avoid ...

Hi All,
I am working on Web Application which deals with history data for
reports and keeping track of changes.
Current Solution :
1) For each Entity I am having a column TID (Tracking ID) which
keep on increasing for an instance of Entity. (so history and present
data in the same table) . It makes querying the data difficult.
2) I am also using month year table for Managing data i.e
<TableNameMMYYYY> for a given month and year
Purposed Soluion :
1) Using a seperate table so that history and present data is
placed seperately .
2) Store All data in one table <TableName>
Please guide me on advantages and divantages you pin point in the
two approaches.
With warm regards
JatinderCheck out Partitioned Views in the BOL (or google it).
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1121943363.320745.186330@.o13g2000cwo.googlegroups.com...
Hi All,
I am working on Web Application which deals with history data for
reports and keeping track of changes.
Current Solution :
1) For each Entity I am having a column TID (Tracking ID) which
keep on increasing for an instance of Entity. (so history and present
data in the same table) . It makes querying the data difficult.
2) I am also using month year table for Managing data i.e
<TableNameMMYYYY> for a given month and year
Purposed Soluion :
1) Using a seperate table so that history and present data is
placed seperately .
2) Store All data in one table <TableName>
Please guide me on advantages and divantages you pin point in the
two approaches.
With warm regards
Jatinder|||Thanks for your Time,
I wish to make the solution Portable and Easy to Manage.
With warm regards
Jatinder|||There's really no such thing as portable. Easy to manage is a relative
term, too. Sometimes, you have to go the extra mile for performance.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1121951901.658739.22330@.g47g2000cwa.googlegroups.com...
Thanks for your Time,
I wish to make the solution Portable and Easy to Manage.
With warm regards
Jatinder

I wish to Avoid ...

Hi All,
I am working on Web Application which deals with history data for
reports and keeping track of changes.
Current Solution :

1) For each Entity I am having a column TID (Tracking ID) which
keep on increasing for an instance of Entity. (so history and present
data in the same table) . It makes querying the data difficult.
2) I am also using month year table for Managing data i.e
<TableNameMMYYYY> for a given month and year

Purposed Soluion :

1) Using a seperate table so that history and present data is
placed seperately .

2) Store All data in one table <TableName
Please guide me on advantages and disadvantages you pin point in the
two approaches.

With warm regards
JatinderI'm not sure I understand your description - you say that you have
history and present data in the same table, but then you say you have
separate month/year tables. You should probably give some more
information about what your tables look like (ie a simplified CREATE
TABLE script), what data you have in each one, how many rows you have
per month etc. It's also a good idea to mention which version of MSSQL
you have.

Simon|||jsfromynr (jatinder.singh@.clovertechnologies.com) writes:
> Current Solution :
> 1) For each Entity I am having a column TID (Tracking ID) which
> keep on increasing for an instance of Entity. (so history and present
> data in the same table) . It makes querying the data difficult.
> 2) I am also using month year table for Managing data i.e
><TableNameMMYYYY> for a given month and year
> Purposed Soluion :
> 1) Using a seperate table so that history and present data is
> placed seperately .
> 2) Store All data in one table <TableName>
> Please guide me on advantages and disadvantages you pin point in the
> two approaches.

It's a little unclear what you mean, but anyway having a table for each
month is not a good idea. Well, if you need to distribute the data it
could be, but in such case you should unite the data in a partitioned
view, and all your queries should use that view.

As for having a current data in a separate table, and adding this
data to a history table at the end of a day in a maintenance job can
sometimes be useful. As a matter of fact, this is routine in our
system.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland,
Sorry If my description was not able to make the point / approach
clear. I am using a single table for a given month to hold that month's
changes/transactions
and for Enity there is no month year table

Erland I wish to ask you ;can I mail you my queries (I know posting
here on newsgroup will fetch me many answers) because most of the time
You,David Protas ,Anith and Celko are the ones who are active on these
newsgroups and provide elaborate and good answers . Everytime your
answer give something new to learn and you people bring bitter reality
to the poster especially Celko but that's what required . You all guys
doing great job.
Please keep reading my silly questions / answers
With warm regards
Jatinder

Erland Sommarskog wrote:
> jsfromynr (jatinder.singh@.clovertechnologies.com) writes:
> > Current Solution :
> > 1) For each Entity I am having a column TID (Tracking ID) which
> > keep on increasing for an instance of Entity. (so history and present
> > data in the same table) . It makes querying the data difficult.
> > 2) I am also using month year table for Managing data i.e
> ><TableNameMMYYYY> for a given month and year
> > Purposed Soluion :
> > 1) Using a seperate table so that history and present data is
> > placed seperately .
> > 2) Store All data in one table <TableName>
> > Please guide me on advantages and disadvantages you pin point in the
> > two approaches.
> It's a little unclear what you mean, but anyway having a table for each
> month is not a good idea. Well, if you need to distribute the data it
> could be, but in such case you should unite the data in a partitioned
> view, and all your queries should use that view.
> As for having a current data in a separate table, and adding this
> data to a history table at the end of a day in a maintenance job can
> sometimes be useful. As a matter of fact, this is routine in our
> system.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||jsfromynr wrote:
[snip]
> Erland I wish to ask you ;can I mail you my queries (I know posting
> here on newsgroup will fetch me many answers) because most of the time
> You,David Protas ,Anith and Celko are the ones who are active on these
> newsgroups and provide elaborate and good answers .

I'm hoping that Erland encourages you to continue posting your
questions here on the newsgroups - My first half-hour/hour of the
morning, I spend reading the newsgroups. There's lots of interesting
problems, questions and answers.

Occasionally, I try to help people using some of the knowledge I've
picked up along the way, but a lot of the time I just sit back and try
to absorb the knowledge that these people are kind enough to share - it
makes for a great learning experience. I would say that the schemas I
have worked on in the last few months are literally miles ahead of the
garbage I was producing two years ago :-)

Keep up the good work everybody, and keep the dicussions on usenet.

Just my two-penneth

Damien.|||jsfromynr (jatinder.singh@.clovertechnologies.com) writes:
> Sorry If my description was not able to make the point / approach
> clear. I am using a single table for a given month to hold that month's
> changes/transactions

As I said, that is a design that leads to problem, unless you unite
the tables in a partitioned view.

> Erland I wish to ask you ;can I mail you my queries

I prefer if you keep it to the newsgroups. Then other people can assist.
And, as Damien testified, other people can also benefit from the
exchange.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Monday, March 12, 2012

I tried to sign on to my web site using the ASPnet_membership table and got a error with m

This is the error that I got. Is it a Resevered Table name or something

Invalid column name 'LastLoginDate'.
Invalid column name 'LastLoginDate'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Invalid column name 'LastLoginDate'.
Invalid column name 'LastLoginDate'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.


What do you mean by "I tried to sign on to my web site using the ASPnet_membership table and got a error with my table."? HOW were you trying to sign in? Using a control? By entering a record into the table?

LastLoginDate IS one of the fields in the aspnet_Membership table.

Can you post the stack trace?

I think I broke a stored procedure

There is a part of our internal web page that merges duplicates together I am building version 2.0 the original developer is gone and I am not experienced enough to figure this one out. Everything works fine exept that it does not delete the merged contact I think that the problem is in the stored procedure cause it is the only place I see a delete contactID at (At the very end)

CREATE PROCEDURE dbo.usp_contact_merge
(
@.ID int,
@.ID1 int =NULL,
@.ID2 int =NULL,
@.ID3 int =NULL,
@.ID4 int =NULL,
@.ID5 int =NULL,
@.ID6 int =NULL,
@.ID7 int =NULL,
@.ID8 int =NULL,
@.ID9 int =NULL,
@.LastName varchar(50) =NULL,
@.FirstName varchar(50) =NULL,
@.Email varchar(50)= NULL,
@.Address varchar(50) =NULL,
@.City varchar(50) =NULL,
@.State varchar(50) =NULL,
@.Zip varchar(50) =NULL,
@.Country varchar(50) =NULL,
@.HomePhone varchar(50) =NULL,
@.ReferredBy varchar(50) =NULL,
@.UpdatedBy varchar(50) =NULL
)

AS
DECLARE
@.Count int,
@.ContactID int,
@.Spouse varchar(50),
@.Middle varchar(50),
@.Company varchar(100),
@.Title varchar(50),
//Deleted variables for brevity
DECLARE contact_cursor CURSOR FOR
SELECT
ContactID,
FirstName,
Spouse,
LastName,
Middle,
Email,
Company,
Title,
Address,
Address1,
Address2,
City,
State,
Zip,
HomePhone,
WorkPhone,
MobilePhone,
UserID,
LocationID,
Email2,
DateEntered,
EnteredBy,
Country,
ReferredBy,
Fax,
Salutation,
LetterDate,
Store,
PhoneExt,
LastResults,
TypeRV,
InterestedIN,
User3,
User4,
User5,
Salesperson,
Origin,
User1,
User2,
User6,
User8,
User9,
IDStatus,
CID,
Stock,
Model,
YR,
ModelNo,
Body,
VIN,
County,
SOLD,
PriceRange,
Financing,
TradeIn,
HisBirthday,
HerBirthday,
Anniversary
FROM Contacts
WHERE ContactID = @.ID
OR ContactID = @.ID1
OR ContactID = @.ID2
OR ContactID = @.ID3
OR ContactID = @.ID4
OR ContactID = @.ID5
OR ContactID = @.ID6
OR ContactID = @.ID7
OR ContactID = @.ID8
OR ContactID = @.ID9
ORDER BY Timestmp DESC

SET @.Count = 0

SET @.MergeDate = GETDATE()
SET @.LastUpdated = GETDATE()

OPEN contact_cursor

FETCH NEXT FROM contact_cursor
INTO
@.ContactID1,
@.FirstName1,
@.Spouse1,
@.LastName1,
@.Middle1,
@.Email1,
@.Company1,
@.Title1,
@.Address1a,
@.Address11,
@.Address21,
@.City1,
@.State1,
@.Zip1,
@.HomePhone1,
@.WorkPhone1,
@.MobilePhone1,
@.UserID1,
@.LocationID1,
@.Email21,
@.DateEntered1,
@.EnteredBy1,
@.Country1,
@.ReferredBy1,
@.Fax1,
@.Salutation1,
@.LetterDate1,
@.Store1,
@.PhoneExt1,
@.LastResults1,
@.TypeRV1,
@.InterestedIN1,
@.User31,
@.User41,
@.User51,
@.Salesperson1,
@.Origin1,
@.User1a,
@.User21,
@.User61,
@.User81,
@.User91,
@.IDStatus1,
@.CID1,
@.Stock1,
@.Model1,
@.YR1,
@.ModelNo1,
@.Body1,
@.VIN1,
@.County1,
@.SOLD1,
@.PriceRange1,
@.Financing1,
@.TradeIn1,
@.HisBirthday1,
@.HerBirthday1,
@.Anniversary1

WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.DateEntered1 IS NOT NULL
BEGIN
SET @.DateEntered = @.DateEntered1
END
IF @.EnteredBy1 IS NOT NULL
BEGIN
SET @.EnteredBy = @.EnteredBy1
END
IF @.FirstName IS NULL
BEGIN
SET @.FirstName = @.FirstName1
END
IF @.Spouse IS NULL
BEGIN
SET @.Spouse = @.Spouse1
END
IF @.LastName IS NULL
BEGIN
SET @.LastName = @.LastName1
END
IF @.Middle IS NULL
BEGIN
SET @.Middle = @.Middle1
END
IF @.Email IS NULL
BEGIN
SET @.Email = @.Email1
END
IF @.Company IS NULL
BEGIN
SET @.Company = @.Company1
END
IF @.Title IS NULL
BEGIN
SET @.Title = @.Title1
END
IF @.Address IS NULL
BEGIN
SET @.Address = @.Address1a
END
IF @.Address1 IS NULL
BEGIN
SET @.Address1 = @.Address11
END
IF @.Address2 IS NULL
BEGIN
SET @.Address2 = @.Address21
END
IF @.City IS NULL
BEGIN
SET @.City = @.City1
END
IF @.State IS NULL
BEGIN
SET @.State = @.State1
END
IF @.Zip IS NULL
BEGIN
SET @.Zip = @.Zip1
END
IF @.HomePhone IS NULL
BEGIN
SET @.HomePhone = @.HomePhone1
END
IF @.WorkPhone IS NULL
BEGIN
SET @.WorkPhone = @.WorkPhone1
END
IF @.MobilePhone IS NULL
BEGIN
SET @.MobilePhone = @.MobilePhone1
END
IF @.UserID IS NULL
BEGIN
SET @.UserID = @.UserID1
END
IF @.LocationID IS NULL
BEGIN
SET @.LocationID = @.LocationID1
END
IF @.Email2 IS NULL
BEGIN
SET @.Email2 = @.Email21
END
IF @.Country IS NULL
BEGIN
SET @.Country = @.Country1
END
IF @.ReferredBy IS NULL
BEGIN
SET @.ReferredBy = @.ReferredBy1
END
IF @.Fax IS NULL
BEGIN
SET @.Fax = @.Fax1
END
IF @.Salutation IS NULL
BEGIN
SET @.Salutation = @.Salutation1
END
IF @.LetterDate IS NULL
BEGIN
SET @.LetterDate = @.LetterDate1
END
IF @.Store IS NULL
BEGIN
SET @.Store = @.Store1
END
IF @.PhoneExt IS NULL
BEGIN
SET @.PhoneExt = @.PhoneExt1
END
IF @.LastResults IS NULL
BEGIN
SET @.LastResults = @.LastResults1
END
IF @.TypeRV IS NULL
BEGIN
SET @.TypeRV = @.TypeRV1
END
IF @.InterestedIN IS NULL
BEGIN
SET @.InterestedIN = @.InterestedIN1
END
IF @.User3 IS NULL
BEGIN
SET @.User3 = @.User31
END
IF @.User4 IS NULL
BEGIN
SET @.User4 = @.User41
END
IF @.User5 IS NULL
BEGIN
SET @.User5 = @.User51
END
IF @.Salesperson IS NULL
BEGIN
SET @.Salesperson = @.Salesperson1
END
IF @.Origin IS NULL
BEGIN
SET @.Origin = @.Origin1
END
IF @.User1 IS NULL
BEGIN
SET @.User1 = @.User1a
END
IF @.User2 IS NULL
BEGIN
SET @.User2 = @.User21
END
IF @.User6 IS NULL
BEGIN
SET @.User6 = @.User61
END
IF @.User8 IS NULL
BEGIN
SET @.User8 = @.User81
END
IF @.User9 IS NULL
BEGIN
SET @.User9 = @.User91
END
IF @.Stock IS NULL
BEGIN
SET @.Stock = @.Stock1
END
IF @.Model IS NULL
BEGIN
SET @.Model = @.Model1
END
IF @.YR IS NULL
BEGIN
SET @.YR = @.YR1
END
IF @.ModelNo IS NULL
BEGIN
SET @.ModelNo = @.ModelNo1
END
IF @.Body IS NULL
BEGIN
SET @.Body = @.Body1

END
IF @.VIN IS NULL
BEGIN
SET @.VIN = @.VIN1
END
IF @.County IS NULL
BEGIN
SET @.County = @.County1
END
IF @.SOLD IS NULL
BEGIN
SET @.SOLD = @.SOLD1
END
IF @.PriceRange IS NULL
BEGIN
SET @.PriceRange = @.PriceRange1
END
IF @.Financing IS NULL
BEGIN
SET @.Financing = @.Financing1
END
IF @.TradeIn IS NULL
BEGIN
SET @.TradeIn = @.TradeIn1
END
IF @.HisBirthday IS NULL
BEGIN
SET @.HisBirthday = @.HisBirthday1
END
IF @.HerBirthday IS NULL
BEGIN
SET @.HerBirthday = @.HerBirthday1
END
IF @.Anniversary IS NULL
BEGIN
SET @.Anniversary = @.Anniversary1
END
SET @.Count = @.Count + 1
IF @.ContactID1 <> @.ID
BEGIN
EXEC usp_contact_merge_delete @.ID, @.ContactID1
END
FETCH NEXT FROM contact_cursor
INTO
@.ContactID1,
@.FirstName1,
@.Spouse1,
@.LastName1,
@.Middle1,
@.Email1,
@.Company1,
@.Title1,
@.Address1a,
@.Address11,
@.Address21,
@.City1,
@.State1,
@.Zip1,
@.HomePhone1,
@.WorkPhone1,
@.MobilePhone1,
@.UserID1,
@.LocationID1,
@.Email21,
@.DateEntered1,
@.EnteredBy1,
@.Country1,
@.ReferredBy1,
@.Fax1,
@.Salutation1,
@.LetterDate1,
@.Store1,
@.PhoneExt1,
@.LastResults1,
@.TypeRV1,
@.InterestedIN1,
@.User31,
@.User41,
@.User51,
@.Salesperson1,
@.Origin1,
@.User1a,
@.User21,
@.User61,
@.User81,
@.User91,
@.IDStatus1,
@.CID1,
@.Stock1,
@.Model1,
@.YR1,
@.ModelNo1,
@.Body1,
@.VIN1,
@.County1,
@.SOLD1,
@.PriceRange1,
@.Financing1,
@.TradeIn1,
@.HisBirthday1,
@.HerBirthday1,
@.Anniversary1

END

CLOSE contact_cursor
DEALLOCATE contact_cursor

UPDATE Contacts SET
FirstName = @.FirstName,
Spouse = @.Spouse,
LastName = @.LastName,
Middle = @.Middle,
Email = @.Email,
Company = @.Company,
Title = @.Title,
Address = @.Address,
Address1 = @.Address1,
Address2 = @.Address2,
City = @.City,
State = @.State,
Zip = @.Zip,
HomePhone = @.HomePhone,
WorkPhone = @.WorkPhone,
MobilePhone = @.MobilePhone,
UserID = @.UserID,
LocationID = @.LocationID,
Email2 = @.Email2,
DateEntered = @.DateEntered,
EnteredBy = @.EnteredBy,
LastUpdated = @.LastUpdated,
UpdatedBy = @.UpdatedBy,
Country = @.Country,
ReferredBy = @.ReferredBy,
Fax = @.Fax,
Salutation = @.Salutation,
LetterDate = @.LetterDate,
Store = @.Store,
PhoneExt = @.PhoneExt,
LastResults = @.LastResults,
TypeRV = @.TypeRV,
InterestedIN = @.InterestedIN,
User3 = @.User3,
User4 = @.User4,
User5 = @.User5,
MergeDate = @.MergeDate,
Salesperson = @.Salesperson,
Origin = @.Origin,
User1 = @.User1,
User2 = @.User2,
User6 = @.User6,
User8 = @.User8,
User9 = @.User9,
IDStatus = @.IDStatus,
CID = @.CID,
Stock = @.Stock,
Model = @.Model,
YR = @.YR,
ModelNo = @.ModelNo,
Body = @.Body,
VIN = @.VIN,
County = @.County,
SOLD = @.SOLD,
PriceRange = @.PriceRange,
Financing = @.Financing,
TradeIn = @.TradeIn,
HisBirthday = @.HisBirthday,
HerBirthday = @.HerBirthday,
Anniversary = @.Anniversary,
LM = 0
WHERE ContactID = @.ID

DELETE FROM Contacts
WHERE ContactID = @.ID1
OR ContactID = @.ID2
OR ContactID = @.ID3
OR ContactID = @.ID4
OR ContactID = @.ID5
OR ContactID = @.ID6
OR ContactID = @.ID7
OR ContactID = @.ID8
OR ContactID = @.ID9

RETURN
GO

I pass all the variables in through asp
with statements like

.Parameters.Append .CreateParameter("@.RETURN_VALUE",adInteger, adParamReturnValue, 0)
.Parameters.Append .CreateParameter("@.ID",adInteger, adParamInput, 0, sid)


Any ideas.. need more info?

Thanks

Damianso, what did you modify?|||I don't know... I did'nt think that I did anything to this procedure or anything to this part of the program.

Thinking hard...

D--|||what is the value of your @.ID1 through @.ID9? if they don't contain any valid ContactID, - the delete will not occur.|||response.write the information right before the stored procedure call is as follows.

ID1 = 210494 'ID to delete
sid = 207298 'current ID

ID2 - 9 not set

I really need some advice.

Hi

Dont laugh but...

I am not entirely ignorant to web technologies, and best practices but i am having a bit of a planning dylema.

My company has a well established SQL 2000 database with windows application which has been created by myself, what i am planning on doing is creating a web site, using asp.net and publishing some of the information, so that our clients may use it, and stop pestering us on the phone. what i would like to know is what would be the best way forward, obviously i don't want to show them all our information, and don't want to put 5Gb worth of data onto a ISP website. What would you suggest i do?

Thanks in advance

BradHow important is it that the data be real-time?

You might consider implementing some DTS packages that extract key pieces of data that you want to share with your clients. The DTS package would write to a database on a separate server.

Terri

Wednesday, March 7, 2012

I need to generate the query to build a database ive designed. Please help..

I've registered with GoDaddy for my site. I've used Visual Web Dev 2005 with SQL Server Mgmt Express 2005 to create my database etc. Now with GoDaddy, I need to "recreate" the database on their server again, but I don't want to go throug the whole process again. I just want to use their query editor to generate the database online.

GoDaddy mentions a "personal-sql" file that SQL Server 2000 generates, but I can't find anything like that with what I have.

Thanks.

Did you try automatically generating the scripts?

In Studio Express, right click on your database.

Select Tasks...Generate Scripts...

You can also generate scripts on a table by table basis.

I need to connect to SQLServer 7.0 from a Linux box using php

Has anyone got experience connecting to a MS SQL Server 7.0 over the
Internet from a Linux Web Server using PHP4.3.x ?
This for a Real Estate MLS IDX feed.
I have heard I can use ADODB and another said FreeTDS (Tabular
DataStream) protocol can be used. Not sure if these people had any
experience with it.
use ODBTP (http://odbtp.sf.net).
imho it's by far the best way to get PHP and MSSQL together (on both,
Windows and Unix/Linux boxes)
"ftw" wrote:

> Has anyone got experience connecting to a MS SQL Server 7.0 over the
> Internet from a Linux Web Server using PHP4.3.x ?
> This for a Real Estate MLS IDX feed.
> I have heard I can use ADODB and another said FreeTDS (Tabular
> DataStream) protocol can be used. Not sure if these people had any
> experience with it.
>
|||Why do you like ODBTP better than ADODB?
|||Well, ADODB is a library, ODBTP a PHP extension you can't compare those two,
in fact you can use ADODB with ODBTP
The biggest plus for ODBTP compared to other extensions is it's great
Unicode support and it's easier to handle than freeTDS
"ftw" wrote:

> Why do you like ODBTP better than ADODB?
>
|||Thanks for the input Sascha!

Sunday, February 19, 2012

I need help using DTS Import/Export tool.

I am trying to use MS SQL Server 2000's Export/Import DTS tool to transfer an existing database on my pc to a remote web server. I am able to connect to the remote database that my web hosting package provided (and thanks to all of you who helped with with the connection issue). Now when I selected the option "Copy Tables and Views From Data Source" to do my transfer, the process completed successfully, but when I tried to do some queries, I get incorrect results. It seems like only the "table values" in my source database was copied. The data that describes how these tables are related (ie. constraints, primary keys, foreign keys, etc) seemed to have NOT been copied to the web database.

So I went back to do the transfer again, this time choosing the option "Copy Objects and Data Between SQL Servers". Now the transfer process proceeds smoothly for the first half (the first half seems to be the same as described above -- just copying the table values). But as it started to copy object data (around 59% done or so), it errored out with the following message:

"[Microsoft][ODBC SQL Server Driver][SQL Server]Only members of the sysadmin role or the database owner may set database options"

Then I went back to the options tab, and tried deselecting some of them. I tested a lot of various combinations, and finally I unchecked them ALL. But still I get the same error. Now, I dont know what "database options" it's referring to.

However, the odd thing is that even though the transfer process failed halfway through, my queries seem to be getting correct results now. I havent tested it much but the few that I tried out returned expected results.

My concern now is that why doesnt this tranfer process complete without errors? I'm worried that since there are still problems with the data transfer process, then there must still be (possibly hidden) problems with the "incomplete" copy of the database on the web server.

Can anyone pinpoint the cause of this?in my experience, when transferring multiple tables with DRI from one server to another, there are sometimes problems related to precedence. For example, when you tranfer relationships, keys, etc... the job might fail. What happens is that the process attempts to create the table with FK reference before the primary table. I havent seen the precise error that you are experiencing... but, would make sure that this is not happening to you...

Sometimes permissions are applied before the user has been created, resulting in an error...

you can also script the relationshipes, keys, etc... and apply them manually after all data has been copied.

I need help in purchasing sql server 2005

Hi everyone, I have a web application (ASP.NET 2.0) where user will access database informations (read, write) from the application (So the SQL server is not directly exposed to the internet). These users will be authenticated from the applications itself (Not using NT/AD authentications). My question is should I get the SQL server 2005 with license(s) processor? Or should I buy the one with 5 cals ? Thanks ...

You cannot use a User CAL for a web server that access a SQL Server - you will need a device CAL instead. You therefore need to consider whether a processor license is cheaper than a license + device CAL. Your best bet is to ring up a couple of SQL Server resellers, describe your scenario and get them to quote you some prices. Then choose the best.