Data Flow Task Problem: project

Showing posts with label project. Show all posts

Sunday, March 25, 2012

Data Encryption: Searching Encrypted Data

Hey there,
I am beginning a new project that will require data to be encrypted and
that data is obviously going to be stored within a Sql 2000 database.
The majority of the data is basically OCR text and will be required to
be searched via a web application via calling a Stored Procedure, with
the rest being Customer Order information. From what I can gather, I
will not be able to store my data encrypted if I want to search it.
Therefore, does anyone have a suggestion on possibly how to go about
this differently, if there even is an option. Anyone have a good
suggested read on how to stored, say credit card information encrypted,
as well as how to search that encrypted data?
Thanks in advance for any suggestions you may have.
XVYou can start by reviewing the encryption section in this
security FAQ. It lists some third party providers that offer
encryption solutions. Some of the sites have white papers on
encryption as well consulting services:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22
-Sue
On 26 Jan 2005 10:31:04 -0800, xanthviper@.xanthviper.com
wrote:

>Hey there,
>I am beginning a new project that will require data to be encrypted and
>that data is obviously going to be stored within a Sql 2000 database.
>The majority of the data is basically OCR text and will be required to
>be searched via a web application via calling a Stored Procedure, with
>the rest being Customer Order information. From what I can gather, I
>will not be able to store my data encrypted if I want to search it.
>Therefore, does anyone have a suggestion on possibly how to go about
>this differently, if there even is an option. Anyone have a good
>suggested read on how to stored, say credit card information encrypted,
>as well as how to search that encrypted data?
>Thanks in advance for any suggestions you may have.
>XV|||Thank you Sue. I appreciate your input.

Thursday, March 22, 2012

Data Driven Subscription Disabled

Hi All,

We are using SQL Server 2005 enterprise edition for our project with SSRS 2005.

We are not getting the Data driven subscription option enabled. That is we see the tab in Report Manager but its disabled.

We have ensured the following:

1- The server is enterprise edition with SP1

2- The users are part of local admin group on the report server

3- The report credentials are stored in the database.

Is there anything missed out?

Thank

Manish

Did you ever get an answer to this? We are having the same thing going on. Are you using integrated security or just Windows?|||

I guess Data Driven Subscription button is not disabled but you're getting error message if it's pressed. Correct? Make sure your report does not contain expressions depending on user like User!UserID. See http://msdn2.microsoft.com/en-us/library/ms156012.aspx for all DD subscription creation guidelines.

|||

No, the Data Driven Subscription button is disabled. I have through the tutorial about4 times. We use the Developer edition and the SQL Server Agent is running. I have also set up the datasource as instructed.

Any other ideas?

Thanks,

|||

I know we installed the Developer edition, but when I use select @.@.version, it says only standard edition. We installed it through the MSDN subscription CD Disc 3097.1

Is there another way to tell what version you have?

Thanks,

Data Driven Subscription Disabled

Hi All,

We are using SQL Server 2005 enterprise edition for our project with SSRS 2005.

We are not getting the Data driven subscription option enabled. That is we see the tab in Report Manager but its disabled.

We have ensured the following:

1- The server is enterprise edition with SP1

2- The users are part of local admin group on the report server

3- The report credentials are stored in the database.

Is there anything missed out?

Thank

Manish

Did you ever get an answer to this? We are having the same thing going on. Are you using integrated security or just Windows?|||

|||

Any other ideas?

Thanks,

|||

I know we installed the Developer edition, but when I use select @.@.version, it says only standard edition. We installed it through the MSDN subscription CD Disc 3097.1

Is there another way to tell what version you have?

Thanks,

Data Driven Subscription Disabled

Hi All,

We are using SQL Server 2005 enterprise edition for our project with SSRS 2005.

We are not getting the Data driven subscription option enabled. That is we see the tab in Report Manager but its disabled.

We have ensured the following:

1- The server is enterprise edition with SP1

2- The users are part of local admin group on the report server

3- The report credentials are stored in the database.

Is there anything missed out?

Thank

Manish

Did you ever get an answer to this? We are having the same thing going on. Are you using integrated security or just Windows?|||

|||

Any other ideas?

Thanks,

|||

I know we installed the Developer edition, but when I use select @.@.version, it says only standard edition. We installed it through the MSDN subscription CD Disc 3097.1

Is there another way to tell what version you have?

Thanks,

Wednesday, March 21, 2012

Data Dictionary project

We have several databases on SQL 2005 which were built seperately. Ive
been asked to come up with a plan for making sure that data elements are
consistent across all of them, and conform to an outside standard
definition which I have a document for. Ive also been asked to make it
easy for any changes in the external definition to be quickly
transferred to all databases. e.g. If the field for Surname changes to
Varchar(55) we should eb able to change it on all databases without
hunting around for places it occurs.
One idea Ive had is to create our own internal set of User Defined Data
Types matching the external dictionary. But ive then got to somehow map
this to all our databases and change them.
Has anyone out there been involved in anything similar or have any ideas?
tia, Matt"Matt" <ma77g@.clara.co.uk> wrote in message
news:1162363143.30942.0@.iris.uk.clara.net...
> We have several databases on SQL 2005 which were built seperately. Ive
> been asked to come up with a plan for making sure that data elements are
> consistent across all of them, and conform to an outside standard
> definition which I have a document for. Ive also been asked to make it
> easy for any changes in the external definition to be quickly transferred
> to all databases. e.g. If the field for Surname changes to Varchar(55) we
> should eb able to change it on all databases without hunting around for
> places it occurs.
> One idea Ive had is to create our own internal set of User Defined Data
> Types matching the external dictionary. But ive then got to somehow map
> this to all our databases and change them.
> Has anyone out there been involved in anything similar or have any ideas?
RedGate software's SQLCompare application does database schema comparisons
and helps keep schemas in sync. Another option is to script out all your
changes ahead of time, apply strict QA and source control policies to them,
and make sure you apply any change scripts to all databases as necessary
(probably a good idea if you're not doing it already anyway). After all, in
a properly normalized database how many different places does Surname
actually occur? (My guess would be once, maybe twice if you have some sort
of pre-load "work table" in place). I'm not too big a fan of the old-style
User-Defined Types, but to each his/her own...|||> If the field for Surname changes to Varchar(55) we should eb able to
> change it on all databases without hunting around for places it occu
I wouldn't use user-defined datatypes for this requirement. Although they
may facilitate a standard definition for types don't change, UDTs complicate
changes to the type after implementation.
Most data modeling tools have features that allow you to address your
dictionary requirements and create change scripts as well. You can also
store dictionary meta-data using extended properties. Also, VS 2005 Team
Edition for Database Professionals (currently in beta) provides refactoring
features and related tools.
Hope this helps.
Dan Guzman
SQL Server MVP
"Matt" <ma77g@.clara.co.uk> wrote in message
news:1162363143.30942.0@.iris.uk.clara.net...
> We have several databases on SQL 2005 which were built seperately. Ive
> been asked to come up with a plan for making sure that data elements are
> consistent across all of them, and conform to an outside standard
> definition which I have a document for. Ive also been asked to make it
> easy for any changes in the external definition to be quickly transferred
> to all databases. e.g. If the field for Surname changes to Varchar(55) we
> should eb able to change it on all databases without hunting around for
> places it occurs.
> One idea Ive had is to create our own internal set of User Defined Data
> Types matching the external dictionary. But ive then got to somehow map
> this to all our databases and change them.
> Has anyone out there been involved in anything similar or have any ideas?
> tia, Matt

Data Dictionary project

We have several databases on SQL 2005 which were built seperately. Ive
been asked to come up with a plan for making sure that data elements are
consistent across all of them, and conform to an outside standard
definition which I have a document for. Ive also been asked to make it
easy for any changes in the external definition to be quickly
transferred to all databases. e.g. If the field for Surname changes to
Varchar(55) we should eb able to change it on all databases without
hunting around for places it occurs.
One idea Ive had is to create our own internal set of User Defined Data
Types matching the external dictionary. But ive then got to somehow map
this to all our databases and change them.
Has anyone out there been involved in anything similar or have any ideas?
tia, Matt
"Matt" <ma77g@.clara.co.uk> wrote in message
news:1162363143.30942.0@.iris.uk.clara.net...
> We have several databases on SQL 2005 which were built seperately. Ive
> been asked to come up with a plan for making sure that data elements are
> consistent across all of them, and conform to an outside standard
> definition which I have a document for. Ive also been asked to make it
> easy for any changes in the external definition to be quickly transferred
> to all databases. e.g. If the field for Surname changes to Varchar(55) we
> should eb able to change it on all databases without hunting around for
> places it occurs.
> One idea Ive had is to create our own internal set of User Defined Data
> Types matching the external dictionary. But ive then got to somehow map
> this to all our databases and change them.
> Has anyone out there been involved in anything similar or have any ideas?
RedGate software's SQLCompare application does database schema comparisons
and helps keep schemas in sync. Another option is to script out all your
changes ahead of time, apply strict QA and source control policies to them,
and make sure you apply any change scripts to all databases as necessary
(probably a good idea if you're not doing it already anyway). After all, in
a properly normalized database how many different places does Surname
actually occur? (My guess would be once, maybe twice if you have some sort
of pre-load "work table" in place). I'm not too big a fan of the old-style
User-Defined Types, but to each his/her own...
|||> If the field for Surname changes to Varchar(55) we should eb able to
> change it on all databases without hunting around for places it occu
I wouldn't use user-defined datatypes for this requirement. Although they
may facilitate a standard definition for types don't change, UDTs complicate
changes to the type after implementation.
Most data modeling tools have features that allow you to address your
dictionary requirements and create change scripts as well. You can also
store dictionary meta-data using extended properties. Also, VS 2005 Team
Edition for Database Professionals (currently in beta) provides refactoring
features and related tools.
Hope this helps.
Dan Guzman
SQL Server MVP
"Matt" <ma77g@.clara.co.uk> wrote in message
news:1162363143.30942.0@.iris.uk.clara.net...
> We have several databases on SQL 2005 which were built seperately. Ive
> been asked to come up with a plan for making sure that data elements are
> consistent across all of them, and conform to an outside standard
> definition which I have a document for. Ive also been asked to make it
> easy for any changes in the external definition to be quickly transferred
> to all databases. e.g. If the field for Surname changes to Varchar(55) we
> should eb able to change it on all databases without hunting around for
> places it occurs.
> One idea Ive had is to create our own internal set of User Defined Data
> Types matching the external dictionary. But ive then got to somehow map
> this to all our databases and change them.
> Has anyone out there been involved in anything similar or have any ideas?
> tia, Matt

Data Dictionary project

data dictionary

Hello,

I am working on a project to reverse engineer requirements for a
database. The database is sitting on a MS SQL Server. How would I
get a listing of all tables and columns within each table to help
create a data dictionary.

I have developed a script in Oracle to do this, but I am just not
familiar with MS SQL Server synthax.

Thanks,

HiteshHi Hitesh

I'm an Oracle developer as well, so sorry for the vagueness...

I think you want to use some stored procedures. sp_tables and sp_columns
are system stored procedures that return the same info as the User_Tables
and User_Tab_Columns views in Oracle.

Do you have the SQL Server client tools installed? Books Online
explains the use of these procedures.

Joe

"Hitesh" <zerocoo_@.hotmail.com> wrote in message
news:30ca6fbb.0405180920.93a5e7c@.posting.google.co m...
> Hello,
> I am working on a project to reverse engineer requirements for a
> database. The database is sitting on a MS SQL Server. How would I
> get a listing of all tables and columns within each table to help
> create a data dictionary.
> I have developed a script in Oracle to do this, but I am just not
> familiar with MS SQL Server synthax.
> Thanks,
> Hitesh|||On 18 May 2004 10:20:24 -0700, Hitesh wrote:

>Hello,
>I am working on a project to reverse engineer requirements for a
>database. The database is sitting on a MS SQL Server. How would I
>get a listing of all tables and columns within each table to help
>create a data dictionary.
>I have developed a script in Oracle to do this, but I am just not
>familiar with MS SQL Server synthax.
>Thanks,
>Hitesh

Hi Hitesh,

All information about all tables:

SELECT * FROM INFORMATION_SCHEMA.TABLES

All information about all columns:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||If you have Enterprise Manager, you can also create Database Diagrams
containing some or all of the tables.

/*P = Stored Procedure
U = Table
PK = Primary Key
V = View
TR = Trigger
the 'xtype' is the type of object it is */

select so.name as obj_name,
sc.name as colm_name,
so.xtype
from sysobjects so (nolock),
syscolumns sc (nolock)
where so.id *= sc.id
order by so.name, sc.name

the above command seems to be more comprehensive than using
information_schema. when run the above query, i get hidden system
tables and approximately 600 rows of data, where as the
information_schema comman queries 250+ rows. anyone know what is the
difference between these two? thanks

SELECT * FROM INFORMATION_SCHEMA.TABLES
SELECT * FROM INFORMATION_SCHEMA.COLUMNS

Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message news:<9enka0p0p32mn2smhjp5rgmc9an0c973af@.4ax.com>...
> On 18 May 2004 10:20:24 -0700, Hitesh wrote:
> >Hello,
> >I am working on a project to reverse engineer requirements for a
> >database. The database is sitting on a MS SQL Server. How would I
> >get a listing of all tables and columns within each table to help
> >create a data dictionary.
> >I have developed a script in Oracle to do this, but I am just not
> >familiar with MS SQL Server synthax.
> >Thanks,
> >Hitesh
> Hi Hitesh,
> All information about all tables:
> SELECT * FROM INFORMATION_SCHEMA.TABLES
> All information about all columns:
> SELECT * FROM INFORMATION_SCHEMA.COLUMNS
>
> Best, Hugo|||On 19 May 2004 09:16:57 -0700, Hitesh wrote:

>thanks...someone also suggested using the following
>/*P = Stored Procedure
>U = Table
>PK = Primary Key
>V = View
>TR = Trigger
>the 'xtype' is the type of object it is */
>select so.name as obj_name,
>sc.name as colm_name,
>so.xtype
>from sysobjects so (nolock),
>syscolumns sc (nolock)
>where so.id *= sc.id
>order by so.name, sc.name
>
>the above command seems to be more comprehensive than using
>information_schema. when run the above query, i get hidden system
>tables and approximately 600 rows of data, where as the
>information_schema comman queries 250+ rows. anyone know what is the
>difference between these two? thanks
>SELECT * FROM INFORMATION_SCHEMA.TABLES
>SELECT * FROM INFORMATION_SCHEMA.COLUMNS

Hi Hitesh,

The query you issued against the system tables will also include stored
procedures, constraints, triggers etc. That should explain the different
number of rows.

There is nothing wring with directly querying the system tables if you
really know what you're doing and if you're not bothered by upward
compatibility. The INFORMATIION_SCHEMA views conform to the ANSI standard
for SQL. If the structure of the system tables is changed in a future
version, the INFORMATION_SCHEMA views will be changed as well, to ensure
ANSI compliance. You'll have to modify your queries against the system
tables yourself.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||thanks for clarifying

Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message news:<hgfna09uu6p3q9l5h1u4shr8uvq250pm4k@.4ax.com>...
> On 19 May 2004 09:16:57 -0700, Hitesh wrote:
> >thanks...someone also suggested using the following
> >/*P = Stored Procedure
> >U = Table
> >PK = Primary Key
> >V = View
> >TR = Trigger
> >the 'xtype' is the type of object it is */
> >select so.name as obj_name,
> >sc.name as colm_name,
> >so.xtype
> >from sysobjects so (nolock),
> >syscolumns sc (nolock)
> >where so.id *= sc.id
> >order by so.name, sc.name
> >the above command seems to be more comprehensive than using
> >information_schema. when run the above query, i get hidden system
> >tables and approximately 600 rows of data, where as the
> >information_schema comman queries 250+ rows. anyone know what is the
> >difference between these two? thanks
> >SELECT * FROM INFORMATION_SCHEMA.TABLES
> >SELECT * FROM INFORMATION_SCHEMA.COLUMNS
> Hi Hitesh,
> The query you issued against the system tables will also include stored
> procedures, constraints, triggers etc. That should explain the different
> number of rows.
> There is nothing wring with directly querying the system tables if you
> really know what you're doing and if you're not bothered by upward
> compatibility. The INFORMATIION_SCHEMA views conform to the ANSI standard
> for SQL. If the structure of the system tables is changed in a future
> version, the INFORMATION_SCHEMA views will be changed as well, to ensure
> ANSI compliance. You'll have to modify your queries against the system
> tables yourself.
> Best, Hugo

Monday, March 19, 2012

data design question - record versioning

Wanted to get feedback from everyone. Current project requires the
need on certain key tables to keep versions of records, and to
possibly use those older versions to selectively rollback. The
business needs the ability to selectively rollback everything that
happened in the data load last Wednesday, for example, but keep all
data loads since then. So I'm exploring history/auditing tables,
filled by triggers, but it seems like a huge pain. I also saw an
article by Ben Allfree ( http://www.codeproject.com/useritems/LisRecordVersioning.asp
) on codeproject that was interesting, but was conceptually quite
different, with some detractors in the feedback. Surely this is a
common issue. What are everyone's thoughts?Common issue, no common answer. I'd say it is dependent on your application
(just like we DBA like to put it). The ratio of active and inactive records
plays an important role in here.
You may also consider set up (indexed) view to help address the issue.
Quentin
"CoreyB" <unc27932@.yahoo.com> wrote in message
news:1180029403.355004.23520@.q69g2000hsb.googlegroups.com...
> Wanted to get feedback from everyone. Current project requires the
> need on certain key tables to keep versions of records, and to
> possibly use those older versions to selectively rollback. The
> business needs the ability to selectively rollback everything that
> happened in the data load last Wednesday, for example, but keep all
> data loads since then. So I'm exploring history/auditing tables,
> filled by triggers, but it seems like a huge pain. I also saw an
> article by Ben Allfree (
> http://www.codeproject.com/useritems/LisRecordVersioning.asp
> ) on codeproject that was interesting, but was conceptually quite
> different, with some detractors in the feedback. Surely this is a
> common issue. What are everyone's thoughts?
>|||Hmmmm - that's what I was afraid the answer would be. I was hoping
there was a magical, documented, easy to implement solution.
There will be one and only one active record at any given time. There
may be 1-n history records. But realistically I doubt there would be
less than 15 history records associated with any given record. And
probably < 10% of all records would have any history at all. Majority
would be fresh inserts.
On May 25, 12:10 pm, "Quentin Ran" <remove_this_qr...@.yahoo.com>
wrote:
> Common issue, no common answer. I'd say it is dependent on your application
> (just like we DBA like to put it). The ratio of active and inactive records
> plays an important role in here.
> You may also consider set up (indexed) view to help address the issue.
> Quentin
> "CoreyB" <unc27...@.yahoo.com> wrote in message
> news:1180029403.355004.23520@.q69g2000hsb.googlegroups.com...
>
> > Wanted to get feedback from everyone. Current project requires the
> > need on certain key tables to keep versions of records, and to
> > possibly use those older versions to selectively rollback. The
> > business needs the ability to selectively rollback everything that
> > happened in the data load last Wednesday, for example, but keep all
> > data loads since then. So I'm exploring history/auditing tables,
> > filled by triggers, but it seems like a huge pain. I also saw an
> > article by Ben Allfree (
> >http://www.codeproject.com/useritems/LisRecordVersioning.asp
> > ) on codeproject that was interesting, but was conceptually quite
> > different, with some detractors in the feedback. Surely this is a
> > common issue. What are everyone's thoughts... Hide quoted text -
> - Show quoted text -|||I'd go with the active/passive flag. Sounds at most the inactive records
would be as many as the active records, if not drastically less. Putting
adequate index (on the columns PK and that states "same group") will almost
eliminate any access contention problems due to this active/passive issue.
<unc27932@.yahoo.com> wrote in message
news:1180110598.547933.90790@.o5g2000hsb.googlegroups.com...
> Hmmmm - that's what I was afraid the answer would be. I was hoping
> there was a magical, documented, easy to implement solution.
> There will be one and only one active record at any given time. There
> may be 1-n history records. But realistically I doubt there would be
> less than 15 history records associated with any given record. And
> probably < 10% of all records would have any history at all. Majority
> would be fresh inserts.
> On May 25, 12:10 pm, "Quentin Ran" <remove_this_qr...@.yahoo.com>
> wrote:
>> Common issue, no common answer. I'd say it is dependent on your
>> application
>> (just like we DBA like to put it). The ratio of active and inactive
>> records
>> plays an important role in here.
>> You may also consider set up (indexed) view to help address the issue.
>> Quentin
>> "CoreyB" <unc27...@.yahoo.com> wrote in message
>> news:1180029403.355004.23520@.q69g2000hsb.googlegroups.com...
>>
>> > Wanted to get feedback from everyone. Current project requires the
>> > need on certain key tables to keep versions of records, and to
>> > possibly use those older versions to selectively rollback. The
>> > business needs the ability to selectively rollback everything that
>> > happened in the data load last Wednesday, for example, but keep all
>> > data loads since then. So I'm exploring history/auditing tables,
>> > filled by triggers, but it seems like a huge pain. I also saw an
>> > article by Ben Allfree (
>> >http://www.codeproject.com/useritems/LisRecordVersioning.asp
>> > ) on codeproject that was interesting, but was conceptually quite
>> > different, with some detractors in the feedback. Surely this is a
>> > common issue. What are everyone's thoughts... Hide quoted text -
>> - Show quoted text -
>|||Quentin Ran wrote:
> I'd go with the active/passive flag. Sounds at most the inactive records
> would be as many as the active records, if not drastically less. Putting
> adequate index (on the columns PK and that states "same group") will almost
> eliminate any access contention problems due to this active/passive issue.
I would also consider having a separate history table instead of
active/inactive flag.
Since the rollover/rollback operations are expected to happen
significantly less often than active record access/update, keeping the
history in a separate table may make sense.
Y.S.
> <unc27932@.yahoo.com> wrote in message
> news:1180110598.547933.90790@.o5g2000hsb.googlegroups.com...
>> Hmmmm - that's what I was afraid the answer would be. I was hoping
>> there was a magical, documented, easy to implement solution.
>> There will be one and only one active record at any given time. There
>> may be 1-n history records. But realistically I doubt there would be
>> less than 15 history records associated with any given record. And
>> probably < 10% of all records would have any history at all. Majority
>> would be fresh inserts.
>> On May 25, 12:10 pm, "Quentin Ran" <remove_this_qr...@.yahoo.com>
>> wrote:
>> Common issue, no common answer. I'd say it is dependent on your
>> application
>> (just like we DBA like to put it). The ratio of active and inactive
>> records
>> plays an important role in here.
>> You may also consider set up (indexed) view to help address the issue.
>> Quentin
>> "CoreyB" <unc27...@.yahoo.com> wrote in message
>> news:1180029403.355004.23520@.q69g2000hsb.googlegroups.com...
>>
>> Wanted to get feedback from everyone. Current project requires the
>> need on certain key tables to keep versions of records, and to
>> possibly use those older versions to selectively rollback. The
>> business needs the ability to selectively rollback everything that
>> happened in the data load last Wednesday, for example, but keep all
>> data loads since then. So I'm exploring history/auditing tables,
>> filled by triggers, but it seems like a huge pain. I also saw an
>> article by Ben Allfree (
>> http://www.codeproject.com/useritems/LisRecordVersioning.asp
>> ) on codeproject that was interesting, but was conceptually quite
>> different, with some detractors in the feedback. Surely this is a
>> common issue. What are everyone's thoughts... Hide quoted text -
>> - Show quoted text -
>

Data Design Issues

I am working on a Project Management application, and I have two data design issues I am debating.

The key element of this app, as you might expect, is Project. The project will proceed through many phases, from Planning, to Pre-Design, Design, Bid, Construction and Post Construction. All along the way there are a number of discrete tasks that must be performed and tracked.

I bounce back and forth in my mind between a single Project table that encapsulates all of these tasks, but am hesitant because I'm not a big fan of large monolithic tables. Alternatively, I could logically create separate tables for the various phases. However, this would create a series of one-to-one relationships between Project and the Phase tables, and require extra joins. The performance hit would probably not be too bad, but I would need to add extra code in either the app code or stored procedures to create an empty record in each of the phase tables when a new project is added. (Obviously, projects in planning or design will not have active records in the Construction and Post Construction tables). What are your thoughts about these choices?

Secondly, I have to manage data for a lot of individuals, which basically break down into two groups. First are internal employees who will have tasks routed to them, be invited to meetings, etc. Second are external vendors, basically contractors and consultants, who will be performing work and also be invited to meetings and such. I need to track the participants in meetings and inspections, so will have a Meeting Participant table to capture the many-to-many relationship. My issue is structuring the handling of the people. One option is to have a Person table, which basically includes everybody, with a flag field for internal or external people, and categories for their roles. Second is a table for internal folks and a table for external, or separate tables for Consultants, Contractors, and internal Employees. However, this makes capturing the meeting participants more cumbersome.

What is the collective wisdom on these? Thanks!

Jeff LittleThe place to start is the Time Tracker starter kit table design and make modification as needed. The second place is to test drive Enterprise Project Server it comes with Database templates for OLTP and OLAP to build cubes for the project. Check the link below for a demo I attended a while back with OLAP cubes, you can also search the TechNet site for more Project demos. Hope this helps.
http://www.microsoft.com/technet/community/events/project/tnt1-64.mspx

Kind regards,
Gift Peddie|||Here's my suggestion:

Project (ProjectID, ...)
Task (ProjectID, PhaseID, TaskID, TaskTypeID, TaskName, ...)
Phase (PhaseID, Name, ...)
TaskType (TaskTypeID, Name, ...)

Person (PersonID, PersonTypeID, PersonName, Username, ...)
PersonType (PersonTypeID, Name, Internal Bit, ...)|||Hi Jeff,

First off I would go with the idea of multple tables for the project instead of one. I have seen way to many applications where the number of fields and record length is outrageous given that most of the time a third to a half of the fields aren't populated. As for your concerns about adding an empty record, why? Simply do an outer join, where no matching record exists the field values from the applicable table will be null.

Lastly, people are people unless there is some compelling reason such as significant data requirements for internal as opposed to external they belong in the same table.

Cheers

Data Cubes using MS Analysis Services

I need to use cubes in VS 2005 web project. I have no clue how to create cubes or use them in my web page. I am familiar with Reporting Services but not cubes.

thanks in advance!

In SQL Server 2005 it depends on where your data is comming from, if you have a Data Warehouse running you can create the Cubes in Analysis Service but SQL Server 2005 also comes with UDM(unified dimension modeling) stripping the Algebra and moving to calculus in one step, some thing you need tools like Hyperion to do in SQL Server 2000. Try the link below for some SQL Server 2005 Analysis resources including UDM. Hope this helps.

http://www.mosha.com/msolap/yukon.htm

|||Thanks Caddre. I am sure I will find lots of info there. I also found this link: http://www.microsoft.com/technet/prodtechnol/sql/default.mspx.|||

Hi,

I have created a cube using MS Analysis Services 2005. I have a fact table that contains 6 different measures (actualUSrevenue, actualEURrevenue,Qty) and (scheduledUSrevenue,scheduledEURrevenue,scheduledQty). How do I break these up into two separate groups of measures.

I will be taking these separate measure groups to build a stacked column chart with both actual and scheduled in the same column but each in a separate series.

thanks,

marilyn

marilyn.beaudreau@.philips.com

|||

There is very good sample code from Microsoft in the file below and the second link is a tutorial to get you started. Hope this helps.

http://www.microsoft.com/downloads/details.aspx?FamilyID=790d631b-bff9-4f4a-b648-e9209e6ac8ad&DisplayLang=en

http://www.databasejournal.com/article.php/1459531

Thursday, March 8, 2012

data cleansing and translation tools for relational databases

Hello All
We have a data migration project. This is basically what we plan to
achieve we have a legacy system which as some data with German long
texts like material texts,etc
We want to take data coming in flat files which as this german texts
and convert this to English text the texts are primarily products
names please advise what tools external or whatever is available to do
this translation to english the texts are about 15 to 20 characters
max.
Next we have data coming from legacy systems that we want to cleanse
like identify duplicates based on things like addresses:
eq: we might have a customer called ABC one at address 118 Main
Street, Nashua and another same customer defined in our another legacy
system called ABC Inc but address at Main Street, Nashua
We want a data cleansing tool or library that works on top of SQL
Server that can do this kind of data pattern identification,etc
Please advice free tools and tools within SQL if I am not aware of
that can do this as well good 3rd party tools that can do this.
Thanks
KarenHi
I don't know of any data cleansing tools that may perform all the functions
that you require. Even if you employed a company to manually clean the data
there is likely to be some degree of error at th e nd of the exercise.
I you carried out the changes yourself it may be possible to clean the data
using a translation table and/or ad-hoc queries.
John
"Karen Middleton" <karenmiddleol@.yahoo.com> wrote in message
news:a5fd468a.0409032125.35235ea@.posting.google.com...
> Hello All
> We have a data migration project. This is basically what we plan to
> achieve we have a legacy system which as some data with German long
> texts like material texts,etc
> We want to take data coming in flat files which as this german texts
> and convert this to English text the texts are primarily products
> names please advise what tools external or whatever is available to do
> this translation to english the texts are about 15 to 20 characters
> max.
> Next we have data coming from legacy systems that we want to cleanse
> like identify duplicates based on things like addresses:
> eq: we might have a customer called ABC one at address 118 Main
> Street, Nashua and another same customer defined in our another legacy
> system called ABC Inc but address at Main Street, Nashua
> We want a data cleansing tool or library that works on top of SQL
> Server that can do this kind of data pattern identification,etc
> Please advice free tools and tools within SQL if I am not aware of
> that can do this as well good 3rd party tools that can do this.
> Thanks
> Karen|||There are many companies providing software and services to help clean up
name and address databases. These methods are usually specific to particular
postal systems or geographical areas so I suggest you Google for something
that meets your requirements in the locales of interest to you.
--
David Portas
SQL Server MVP
--|||Try the following link :
http://www.itcg.nl/
From their site :
CLUE
Have you ever experienced problems with duplicate customers,
addresses, products or any other information?
CLUE®, the CLUster Engine from ITCG is a generic component that uses
fuzzy matching logic to match data that is nearly the same. As a generic
component CLUE® is useful in many ways.
Few years ago they were specialising in cleaning data and
merging data from several sources into one database.
They had some fancy algoritms to detect double entries in
the database. They were very convincing in their presentation,
but I have no experience with their range of products.
good luck,
ben brugman
"Karen Middleton" <karenmiddleol@.yahoo.com> wrote in message
news:a5fd468a.0409032125.35235ea@.posting.google.com...
> Hello All
> We have a data migration project. This is basically what we plan to
> achieve we have a legacy system which as some data with German long
> texts like material texts,etc
> We want to take data coming in flat files which as this german texts
> and convert this to English text the texts are primarily products
> names please advise what tools external or whatever is available to do
> this translation to english the texts are about 15 to 20 characters
> max.
> Next we have data coming from legacy systems that we want to cleanse
> like identify duplicates based on things like addresses:
> eq: we might have a customer called ABC one at address 118 Main
> Street, Nashua and another same customer defined in our another legacy
> system called ABC Inc but address at Main Street, Nashua
> We want a data cleansing tool or library that works on top of SQL
> Server that can do this kind of data pattern identification,etc
> Please advice free tools and tools within SQL if I am not aware of
> that can do this as well good 3rd party tools that can do this.
> Thanks
> Karen|||Another thing you might look at for address cleaning is CASS certification.
This is a standard set by the US Postal service, and there is much software
around to help identify bad addresses, but it does not address your other
data quality problems.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Karen Middleton" <karenmiddleol@.yahoo.com> wrote in message
news:a5fd468a.0409032125.35235ea@.posting.google.com...
> Hello All
> We have a data migration project. This is basically what we plan to
> achieve we have a legacy system which as some data with German long
> texts like material texts,etc
> We want to take data coming in flat files which as this german texts
> and convert this to English text the texts are primarily products
> names please advise what tools external or whatever is available to do
> this translation to english the texts are about 15 to 20 characters
> max.
> Next we have data coming from legacy systems that we want to cleanse
> like identify duplicates based on things like addresses:
> eq: we might have a customer called ABC one at address 118 Main
> Street, Nashua and another same customer defined in our another legacy
> system called ABC Inc but address at Main Street, Nashua
> We want a data cleansing tool or library that works on top of SQL
> Server that can do this kind of data pattern identification,etc
> Please advice free tools and tools within SQL if I am not aware of
> that can do this as well good 3rd party tools that can do this.
> Thanks
> Karen|||I would suggest calling a company like www.listadvantage.com and have
the names cleaned and cass certified.
they r self serve and very easy to work with
"ben brugman" <ben@.niethier.nl> wrote in message news:<OlCP4zAlEHA.536@.TK2MSFTNGP11.phx.gbl>...
> Try the following link :
> http://www.itcg.nl/
> From their site :
> CLUE
> Have you ever experienced problems with duplicate customers,
> addresses, products or any other information?
> CLUE®, the CLUster Engine from ITCG is a generic component that uses
> fuzzy matching logic to match data that is nearly the same. As a generic
> component CLUE® is useful in many ways.
>
> Few years ago they were specialising in cleaning data and
> merging data from several sources into one database.
> They had some fancy algoritms to detect double entries in
> the database. They were very convincing in their presentation,
> but I have no experience with their range of products.
> good luck,
> ben brugman
>
> "Karen Middleton" <karenmiddleol@.yahoo.com> wrote in message
> news:a5fd468a.0409032125.35235ea@.posting.google.com...
> > Hello All
> >
> > We have a data migration project. This is basically what we plan to
> > achieve we have a legacy system which as some data with German long
> > texts like material texts,etc
> >
> > We want to take data coming in flat files which as this german texts
> > and convert this to English text the texts are primarily products
> > names please advise what tools external or whatever is available to do
> > this translation to english the texts are about 15 to 20 characters
> > max.
> >
> > Next we have data coming from legacy systems that we want to cleanse
> > like identify duplicates based on things like addresses:
> >
> > eq: we might have a customer called ABC one at address 118 Main
> > Street, Nashua and another same customer defined in our another legacy
> > system called ABC Inc but address at Main Street, Nashua
> >
> > We want a data cleansing tool or library that works on top of SQL
> > Server that can do this kind of data pattern identification,etc
> >
> > Please advice free tools and tools within SQL if I am not aware of
> > that can do this as well good 3rd party tools that can do this.
> >
> > Thanks
> > Karen

Data Cleansing

We have just started a new data cleansing project. One of the aims of the
project is to identify similar records so that we can eliminate them. The
problem lies in how to identify those similar records.
Consider a table that has the columns: (ID, name …). A record whose name is
“XYZ” is similar to “XY”. Moreover “XYZ Company” should be similar to “XYZ
Co”.
We have to ideas:
1) For each record in the table, get the records that match it based on a
function, candidate function. Then for each match get a percentage that
indicate how close the 2 records are, scoring function.
2) For each record create a function that gives a score to a record, Sum of
its name characters ASCII codes + any other function, items with nearby
scores are considered similar. There could be more than one function; 2
functions means search in 2D, 3 functions means search in 3D.
Can you lead me to ideas/articles/books that show how to identify the
columns that will be included in the scoring functions, or that illustrate a
better way of searching?
Note:
Matreials needn't be targeted at SQL Server, we can do some part in SQL
Server and the other in C#.
On 28 Apr, 09:52, Shehab Kamal <ShehabKa...@.discussions.microsoft.com>
wrote:
> We have just started a new data cleansing project. One of the aims of the
> project is to identify similar records so that we can eliminate them. The
> problem lies in how to identify those similar records.
> Consider a table that has the columns: (ID, name ...). A record whose name is
> "XYZ" is similar to "XY". Moreover "XYZ Company" should be similar to "XYZ
> Co".
> We have to ideas:
> 1) For each record in the table, get the records that match it based on a
> function, candidate function. Then for each match get a percentage that
> indicate how close the 2 records are, scoring function.
> 2) For each record create a function that gives a score to a record, Sum of
> its name characters ASCII codes + any other function, items with nearby
> scores are considered similar. There could be more than one function; 2
> functions means search in 2D, 3 functions means search in 3D.
> Can you lead me to ideas/articles/books that show how to identify the
> columns that will be included in the scoring functions, or that illustrate a
> better way of searching?
> Note:
> Matreials needn't be targeted at SQL Server, we can do some part in SQL
> Server and the other in C#.
Data cleansing is supported by Integration Services using the Fuzzy
Lookup feature:
http://msdn.microsoft.com/msdnmag/issues/05/09/sqlserver2005/default.aspx
You'll find that other integration tools offer the same kind of
functionality.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||The ETI idea is brilliant. Thanks for the link

Data Cleansing

We have just started a new data cleansing project. One of the aims of the
project is to identify similar records so that we can eliminate them. The
problem lies in how to identify those similar records.
Consider a table that has the columns: (ID, name â?¦). A record whose name is
â'XYZâ' is similar to â'XYâ'. Moreover â'XYZ Companyâ' should be similar to â'XYZ
Coâ'.
We have to ideas:
1) For each record in the table, get the records that match it based on a
function, candidate function. Then for each match get a percentage that
indicate how close the 2 records are, scoring function.
2) For each record create a function that gives a score to a record, Sum of
its name characters ASCII codes + any other function, items with nearby
scores are considered similar. There could be more than one function; 2
functions means search in 2D, 3 functions means search in 3D.
Can you lead me to ideas/articles/books that show how to identify the
columns that will be included in the scoring functions, or that illustrate a
better way of searching?
Note:
Matreials needn't be targeted at SQL Server, we can do some part in SQL
Server and the other in C#.On 28 Apr, 09:52, Shehab Kamal <ShehabKa...@.discussions.microsoft.com>
wrote:
> We have just started a new data cleansing project. One of the aims of the
> project is to identify similar records so that we can eliminate them. The
> problem lies in how to identify those similar records.
> Consider a table that has the columns: (ID, name ...). A record whose name is
> "XYZ" is similar to "XY". Moreover "XYZ Company" should be similar to "XYZ
> Co".
> We have to ideas:
> 1) For each record in the table, get the records that match it based on a
> function, candidate function. Then for each match get a percentage that
> indicate how close the 2 records are, scoring function.
> 2) For each record create a function that gives a score to a record, Sum of
> its name characters ASCII codes + any other function, items with nearby
> scores are considered similar. There could be more than one function; 2
> functions means search in 2D, 3 functions means search in 3D.
> Can you lead me to ideas/articles/books that show how to identify the
> columns that will be included in the scoring functions, or that illustrate a
> better way of searching?
> Note:
> Matreials needn't be targeted at SQL Server, we can do some part in SQL
> Server and the other in C#.
Data cleansing is supported by Integration Services using the Fuzzy
Lookup feature:
http://msdn.microsoft.com/msdnmag/issues/05/09/sqlserver2005/default.aspx
You'll find that other integration tools offer the same kind of
functionality.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||The ETI idea is brilliant. Thanks for the link

Data Cleansing

We have just started a new data cleansing project. One of the aims of the
project is to identify similar records so that we can eliminate them. The
problem lies in how to identify those similar records.
Consider a table that has the columns: (ID, name …). A record whose name i
s
“XYZ” is similar to “XY”. Moreover “XYZ Company” should be simil
ar to “XYZ
Co”.
We have to ideas:
1) For each record in the table, get the records that match it based on a
function, candidate function. Then for each match get a percentage that
indicate how close the 2 records are, scoring function.
2) For each record create a function that gives a score to a record, Sum of
its name characters ASCII codes + any other function, items with nearby
scores are considered similar. There could be more than one function; 2
functions means search in 2D, 3 functions means search in 3D.
Can you lead me to ideas/articles/books that show how to identify the
columns that will be included in the scoring functions, or that illustrate a
better way of searching?
Note:
Matreials needn't be targeted at SQL Server, we can do some part in SQL
Server and the other in C#.On 28 Apr, 09:52, Shehab Kamal <ShehabKa...@.discussions.microsoft.com>
wrote:
> We have just started a new data cleansing project. One of the aims of the
> project is to identify similar records so that we can eliminate them. The
> problem lies in how to identify those similar records.
> Consider a table that has the columns: (ID, name ...). A record whose name
is
> "XYZ" is similar to "XY". Moreover "XYZ Company" should be similar to "XYZ
> Co".
> We have to ideas:
> 1) For each record in the table, get the records that match it based on a
> function, candidate function. Then for each match get a percentage that
> indicate how close the 2 records are, scoring function.
> 2) For each record create a function that gives a score to a record, Sum o
f
> its name characters ASCII codes + any other function, items with nearby
> scores are considered similar. There could be more than one function; 2
> functions means search in 2D, 3 functions means search in 3D.
> Can you lead me to ideas/articles/books that show how to identify the
> columns that will be included in the scoring functions, or that illustrate
a
> better way of searching?
> Note:
> Matreials needn't be targeted at SQL Server, we can do some part in SQL
> Server and the other in C#.
Data cleansing is supported by Integration Services using the Fuzzy
Lookup feature:
http://msdn.microsoft.com/msdnmag/i...05/default.aspx
You'll find that other integration tools offer the same kind of
functionality.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||The ETI idea is brilliant. Thanks for the link

Saturday, February 25, 2012

Data Access Pages and SQL-Server

Dear All,

I have developed a Data Access Page with Access as a project. It is
connected to a SQL-Server database. I try to edit the data in the table and
am not able to do so. I might be missing some permissions. I have changed
a number. What am I missing?

Thanks in advance.

Jeff MagouirkIf the SQL table does not have a primary index (key), your Access program
can NOT update the table. I am 90% sure this is your problem.

Oscar...

"Jeff Magouirk" <magouirkj@.njc.org> wrote in message
news:10psm909drc4ea6@.corp.supernews.com...
> Dear All,
> I have developed a Data Access Page with Access as a project. It is
> connected to a SQL-Server database. I try to edit the data in the table
and
> am not able to do so. I might be missing some permissions. I have
changed
> a number. What am I missing?
> Thanks in advance.
> Jeff Magouirk

Tuesday, February 14, 2012

CustomReportItem in Local Mode

Iâ've developed a CustomReportItem using VS 2005 RC and SQL Sept CTP. All work
great if I use a Report project in Server Mode i.e. I can drag & drop the
control onto a report and test it.
Sadly, when I try to create a report in LOCAL mode, Iâ'm unable to drag &
drop the control onto the report. Is this behavior by design or what?
Thanks in advance.
GabrielAre you doing this in a .rdl file, in a .rdlc file or both?
Andrew Watt
MVP - InfoPath
On Wed, 12 Oct 2005 05:51:10 -0700, "Ing. Gabriel Fogante"
<IngGabrielFogante@.discussions.microsoft.com> wrote:
>I?ve developed a CustomReportItem using VS 2005 RC and SQL Sept CTP. All work
>great if I use a Report project in Server Mode i.e. I can drag & drop the
>control onto a report and test it.
>Sadly, when I try to create a report in LOCAL mode, I?m unable to drag &
>drop the control onto the report. Is this behavior by design or what?
>Thanks in advance.
>Gabriel|||Andrew,
My control works great with .rdl files i.e. in Server Mode.
If I use it in .rdlc files i.e. in Local Mode, then the control is "ignored"
when I show it with Report Viewer controls.
Regards,
Gabriel
"Andrew Watt [MVP - InfoPath]" wrote:
> Are you doing this in a .rdl file, in a .rdlc file or both?
> Andrew Watt
> MVP - InfoPath
> On Wed, 12 Oct 2005 05:51:10 -0700, "Ing. Gabriel Fogante"
> <IngGabrielFogante@.discussions.microsoft.com> wrote:
> >Iâ've developed a CustomReportItem using VS 2005 RC and SQL Sept CTP. All work
> >great if I use a Report project in Server Mode i.e. I can drag & drop the
> >control onto a report and test it.
> >Sadly, when I try to create a report in LOCAL mode, Iâ'm unable to drag &
> >drop the control onto the report. Is this behavior by design or what?
> >
> >Thanks in advance.
> >
> >Gabriel
>|||Sorry, CustomReportItems are not supported in local mode. They are only
supported for report designer preview and report server.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ing. Gabriel Fogante" <IngGabrielFogante@.discussions.microsoft.com> wrote
in message news:48D77694-BAC3-4425-84AF-B5CDA5B525B3@.microsoft.com...
> Andrew,
> My control works great with .rdl files i.e. in Server Mode.
> If I use it in .rdlc files i.e. in Local Mode, then the control is
> "ignored"
> when I show it with Report Viewer controls.
> Regards,
> Gabriel
> "Andrew Watt [MVP - InfoPath]" wrote:
>> Are you doing this in a .rdl file, in a .rdlc file or both?
>> Andrew Watt
>> MVP - InfoPath
>> On Wed, 12 Oct 2005 05:51:10 -0700, "Ing. Gabriel Fogante"
>> <IngGabrielFogante@.discussions.microsoft.com> wrote:
>> >I've developed a CustomReportItem using VS 2005 RC and SQL Sept CTP. All
>> >work
>> >great if I use a Report project in Server Mode i.e. I can drag & drop
>> >the
>> >control onto a report and test it.
>> >Sadly, when I try to create a report in LOCAL mode, I'm unable to drag &
>> >drop the control onto the report. Is this behavior by design or what?
>> >
>> >Thanks in advance.
>> >
>> >Gabriel
>>

Customizing the connection string to the server where sp will be created?

When you create a SQL server project, it asks you for the connection string to the database. Can I change this connection string at run time?. By the way it is included in the project properties at desing time.I don't really understand the question - the connection string you mention is the string which tells VS where the assembly and procs etc will be deployed, when you do Deployment - at wich stage would you like to change the string? When your code is invoked eventually, the connection string is not used.

Niels

Sunday, March 25, 2012

Data Encryption: Searching Encrypted Data

Thursday, March 22, 2012

Data Driven Subscription Disabled

Data Driven Subscription Disabled

Data Driven Subscription Disabled

Wednesday, March 21, 2012

Data Dictionary project

Data Dictionary project

Data Dictionary project

data dictionary

Monday, March 19, 2012

data design question - record versioning

data design question - record versioning

data design question - record versioning

Data Design Issues

Data Cubes using MS Analysis Services

Thursday, March 8, 2012

data cleansing and translation tools for relational databases

Data Cleansing

Data Cleansing

Data Cleansing

Saturday, February 25, 2012

Data Access Pages and SQL-Server

Tuesday, February 14, 2012

CustomReportItem in Local Mode

Customizing the connection string to the server where sp will be created?

Data Flow Task Problem

Blog Archive

About Me