Database Help

I have am MSSQL Server.
I have a Database that has a table in it that I have been trying to access
with 3rd Party software. Well do to the way that the DB is setup I cannot
get to the data in the table how I want. so my thoughts were to have only
the parts I want go into another (local) MSSQL Database. Can I do this? If
so how do I set it up? I have the new Database created but I don't know how
to do a live copy of the data from the existing database.
Any help you could provide would really be appreciated.> I cannot get to the data in the table how I want.
Can you be more specific? What does the data look like, and how do you want
it to look?|||Sure
The data I am Looking to access is in a table called dbo.reading
The data looks like this
Device ID Sequence# Time
reg_seq tier data_desc sensor data_register
time_stamp status
052461368 0 4/14/2008 7:50:40 PM 0 0 3 NULL 2 NULL
8
052461368 0 4/14/2008 7:50:40 PM 1 0 1000001 036497.900543861091 NULL
8
052461368 0 4/14/2008 7:50:40 PM 2 0 4000001 128.900000430643559 NULL
8
052461368 0 4/14/2008 7:50:40 PM 3 0 6000001 1 614.4500091560185NULL8052461368 0 4/14/2008 7:50:40 PM 4 0 2000001 1
585.54998779296875 NULL 8
Each Device ID is a meter. However there are 4 sets of data (reg_seq)
associated with each reading per meter and I want to get the data that
corresponds with reg_seq being 1 for each data point
I created a new table called dbo.kw in the same global database
The problem I am having is I am not sure how to link the reading table to
the kW table to get what I want or set anything up so that I can do a live
output of dbo.reading.
Does this help?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:8535B201-2D67-4EF1-B81F-A7833CD03E1E@.microsoft.com...
>> I cannot get to the data in the table how I want.
> Can you be more specific? What does the data look like, and how do you
> want it to look?|||> Does this help?
Not really. Instead of:
> Each Device ID is a meter. However there are 4 sets of data (reg_seq)
> associated with each reading per meter and I want to get the data that
> corresponds with reg_seq being 1 for each data point
Can you SHOW what you want the actual output to look like, based on the
sample rows you provided?|||Sorry about that when I pasted it all the tables lined up.
What I am looking to do is take the table dbo.reading and
export/link/synch/output the table dbo.kW
In dbo.reading I have several columns of information what I want is
Device_ID
Time
data_register (where data_desc. = 1)
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:CA7EDCBA-FC7D-422F-9A29-8013CBDECF4D@.microsoft.com...
>> Does this help?
> Not really. Instead of:
>> Each Device ID is a meter. However there are 4 sets of data (reg_seq)
>> associated with each reading per meter and I want to get the data that
>> corresponds with reg_seq being 1 for each data point
> Can you SHOW what you want the actual output to look like, based on the
> sample rows you provided?|||Anyone help?
"Rodney James" <rmjames007@.carolina.rr.com> wrote in message
news:emI0viXpIHA.4620@.TK2MSFTNGP06.phx.gbl...
> Sorry about that when I pasted it all the tables lined up.
> What I am looking to do is take the table dbo.reading and
> export/link/synch/output the table dbo.kW
> In dbo.reading I have several columns of information what I want is
> Device_ID
> Time
> data_register (where data_desc. = 1)
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:CA7EDCBA-FC7D-422F-9A29-8013CBDECF4D@.microsoft.com...
>> Does this help?
>> Not really. Instead of:
>> Each Device ID is a meter. However there are 4 sets of data (reg_seq)
>> associated with each reading per meter and I want to get the data that
>> corresponds with reg_seq being 1 for each data point
>> Can you SHOW what you want the actual output to look like, based on the
>> sample rows you provided?
>|||I was trying to help. I asked for specific information and got more vague
information. <shrug>
"Rodney James" <rmjames007@.carolina.rr.com> wrote in message
news:elMy0fhpIHA.1164@.TK2MSFTNGP04.phx.gbl...
> Anyone help?
> "Rodney James" <rmjames007@.carolina.rr.com> wrote in message
> news:emI0viXpIHA.4620@.TK2MSFTNGP06.phx.gbl...
>> Sorry about that when I pasted it all the tables lined up.
>> What I am looking to do is take the table dbo.reading and
>> export/link/synch/output the table dbo.kW
>> In dbo.reading I have several columns of information what I want is
>> Device_ID
>> Time
>> data_register (where data_desc. = 1)
>>
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> message news:CA7EDCBA-FC7D-422F-9A29-8013CBDECF4D@.microsoft.com...
>> Does this help?
>> Not really. Instead of:
>> Each Device ID is a meter. However there are 4 sets of data (reg_seq)
>> associated with each reading per meter and I want to get the data that
>> corresponds with reg_seq being 1 for each data point
>> Can you SHOW what you want the actual output to look like, based on the
>> sample rows you provided?|||I apologize Aaon. Let me phrase it this way. What specific information are
you asking for? I am sorry for not being clearer.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:#IBvj6ipIHA.2256@.TK2MSFTNGP05.phx.gbl...
> I was trying to help. I asked for specific information and got more vague
> information. <shrug>
>
>
> "Rodney James" <rmjames007@.carolina.rr.com> wrote in message
> news:elMy0fhpIHA.1164@.TK2MSFTNGP04.phx.gbl...
>> Anyone help?
>> "Rodney James" <rmjames007@.carolina.rr.com> wrote in message
>> news:emI0viXpIHA.4620@.TK2MSFTNGP06.phx.gbl...
>> Sorry about that when I pasted it all the tables lined up.
>> What I am looking to do is take the table dbo.reading and
>> export/link/synch/output the table dbo.kW
>> In dbo.reading I have several columns of information what I want is
>> Device_ID
>> Time
>> data_register (where data_desc. = 1)
>>
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> message news:CA7EDCBA-FC7D-422F-9A29-8013CBDECF4D@.microsoft.com...
>> Does this help?
>> Not really. Instead of:
>> Each Device ID is a meter. However there are 4 sets of data (reg_seq)
>> associated with each reading per meter and I want to get the data that
>> corresponds with reg_seq being 1 for each data point
>> Can you SHOW what you want the actual output to look like, based on the
>> sample rows you provided?
>

database encryption

Hi,

I am trying to encrypt my database using some softwares, For that i have to install the software and encrypt database accordingly, i have a querry that will my database remain encrypted if i uninstall the encryption software??
The software i am using are dbEncrypt,Netlib,XP_Crypt,Rsa Bsafe.

Thanx

DivyaI am trying to encrypt my database using some softwares, For that i have to install the software and encrypt database accordingly, i have a querry that will my database remain encrypted if i uninstall the encryption software??
The software i am using are dbEncrypt,Netlib,XP_Crypt,Rsa Bsafe.
Most likely yes, and depending on how "safe" the program is, you might actually end up loosing all the data, though I don't think it's very likely.

However, I would like you to think a bit whether you actually need to encrypt the database or not. Most of the time I see people ask for encryption, it's not really neccessary. One of there rare instances where I can see the use for it is databases installed on laptops, and in that case I recommend using EFS. I know that SQL Server 2005 supports EFS, and as it should be transparent to the application it should work in SQL Server 2000 as well, though I have not tested it.

database encryption

Hi,

i am trying to encrypt my database using dbEncrypt software.The encryption provides role based access ot the database.In that case developer can not call any stored procedure of that database. These two are conflicting things. Can anyone suggest me a way so that i can access the stored procedure through my code by checking the roles from the front end. is it possible??

Please reply soon.

Application Security, INC. The manufacturer of that product, will most likely have specific methods and methodologies for connecting and querying a DB that has been encrypted using their software. I would recomend you ask them how to accomplish this.

http://www.appsecinc.com/support/index.shtml

Database dump and load script

Hi,
Is anyone using a custom script, or anything else that helps allow software
developer's do their own database dump and loads rather than having to ask a
DBA every time? What are people using to allow this functionality?
We are looking to move this task from a DBA responsibility to allow for the
users to do their own database dumps and loads in the test and development
environments. The one problem we have is that the databases were not all
standardized to have the exact file path names when they are created. For
example, F:\MSSQL\data\<filename>.mdf. Some of them might be
F:\MSSQL\default\data\<filename>.mdf, etc.
Does anyone have this capability automated for what I am looking for?
Thanks for any feedback.
CynthiaWe wrote some scripts that perform these operations. Essentially, a control
table is created and populated for each DB. When a restore need to be
performed, a row is inserted into a table where a recurring job picks it up
and looks into the control tables for the necessary information. The job
generates on OSQL script and prevents the need for the developers to have
those sorts of rights necessesary to perform restores(may not be an issue for
you)
Jeff
"Cynthia" wrote:
> Hi,
> Is anyone using a custom script, or anything else that helps allow software
> developer's do their own database dump and loads rather than having to ask a
> DBA every time? What are people using to allow this functionality?
> We are looking to move this task from a DBA responsibility to allow for the
> users to do their own database dumps and loads in the test and development
> environments. The one problem we have is that the databases were not all
> standardized to have the exact file path names when they are created. For
> example, F:\MSSQL\data\<filename>.mdf. Some of them might be
> F:\MSSQL\default\data\<filename>.mdf, etc.
> Does anyone have this capability automated for what I am looking for?
> Thanks for any feedback.
> Cynthia

Database Design

I am creating a knowledge base. I want the user to be able to choose a category such as Hardware, Software, Etc. then I want them to be able to choose what type of software such as word, excel, ets.

It will then display in a gridview problems, solutions, submitted by, last updated, and review date.

The gridview will allow users to update solutions and I would also like the ability for technicians to be able to add new Problems/solutions.

What would be the best way to handle this?

Any tips are appreciated.

Rick

As far as the database design goes, I would make it relational using the structure similar to the one below.

Table: Categories
Columns: CatID, Category

Table: CatTypes
Columns: CatTypeID, CatID, CatType

Table: KB_FAQ
Columns: KB_ID, Problem, Solution, UserID, LastUpdated, LastReviewed

Table: KB_CatTypes
Columns: KB_ID, CatTypeID

Table: Users
Columns: UserID, UserName, FirstName, LastName, Password

|||In Visual Studion Web Developer express, where do I setup relationships?|||I figured it out. Thanks for all your help.

Database Design

I am designing a WEB BASED Accounting Software with ASP and SQL
Server. For this I need some help for the Database design. My design
is as follows.

I)User table: User_id, UserName....
Users (e.g. John Smith) Each User would contain a following Group of
tables

a)Customers
b)Suppliers
c)Bank Accounts
d)Transactions

Tables under :
User_FinYear_Customers (e.g JohnSmith_02_03_Customers)
User_FinYear_Suppliers (e.g JohnSmith_02_03_Suppliers)
User_FinYear_BankAccounts (e.g JohnSmith_02_03_BankAccounts)
User_FinYear_Transactions (e.g JohnSmith_02_03_Transactions)

As new user is created all the above tables are created at run time.
These tables are created for each and every user. There can be more
than 4 tables (as mentioned above) for one user. These tables will
increase as more users are added. Only thing in support of this design
is that, the record fetching time for a particular user would be
minimum and the table for a particular user will only load in Memory.

IS IT FEASIBLE TO CREATE ABOUT 20 TABLES FOR EACH NEW USER ADDED TO
THE DATABASE? WHICH MEANS IF THERE ARE 1000 USERS THERE WOULD BE 20000
TABLES IN THE DATABASE. THIS CASE CAN GO WORSE IF THERE ARE MORE THAN
1000 USERS. WHAT IS BETTER DATABASE DESIGN, MORE TABLES WITH LESS
RECORDS OR LESS TABLES WITH MORE NO.OF RECORDS?

An alternative design can be as follows

Tables:
Users, Customers, Suppliers, BankAccounts, Transactions ....and so
on.

User: User_Id, UserName, .....
Customers: User_Id, Customer_Id,.....
Suppliers: User_Id, Supplier_Id,....
BankAccounts: User_Id, BankAc_Id,....
Transactions: User_Id, Trans_Id.....
..
..
..
..

All these tables would be created at the design time only and as a new
user is created a record is added to the users table. When the user
adds Customer the record is added to the Customers table... and so
on... The problem with this design is that Customers,Suppliers,
BankAccounts... etc tables would contain records for all the users
and thus the record fetching time for a particular user increases as
many times as there are users in the Database. Another problems with
this design is that more than one user would be connected at run time
will access the same tables, and for even a single user the complete
table will be loaded in memory.

WHICH DESIGN SHOULD BE USED AS FAR AS SPEED OF SERVER IS CONCERNED?
PLEASE HELP WITH CONVINCING REASONS.Clearly #2, less maint and if you ever need over all queries or a customer
gets combined it should be a lot easier. Also use Oracle not the other one
since this is an Oracle newsgroup.
Jim
"Rushikesh" <rbaiwar@.sify.com> wrote in message
news:2b29906c.0307142123.1d020801@.posting.google.c om...
> I am designing a WEB BASED Accounting Software with ASP and SQL
> Server. For this I need some help for the Database design. My design
> is as follows.
>
> I)User table: User_id, UserName....
> Users (e.g. John Smith) Each User would contain a following Group of
> tables
> a)Customers
> b)Suppliers
> c)Bank Accounts
> d)Transactions
>
> Tables under :
> User_FinYear_Customers (e.g JohnSmith_02_03_Customers)
> User_FinYear_Suppliers (e.g JohnSmith_02_03_Suppliers)
> User_FinYear_BankAccounts (e.g JohnSmith_02_03_BankAccounts)
> User_FinYear_Transactions (e.g JohnSmith_02_03_Transactions)
> As new user is created all the above tables are created at run time.
> These tables are created for each and every user. There can be more
> than 4 tables (as mentioned above) for one user. These tables will
> increase as more users are added. Only thing in support of this design
> is that, the record fetching time for a particular user would be
> minimum and the table for a particular user will only load in Memory.
> IS IT FEASIBLE TO CREATE ABOUT 20 TABLES FOR EACH NEW USER ADDED TO
> THE DATABASE? WHICH MEANS IF THERE ARE 1000 USERS THERE WOULD BE 20000
> TABLES IN THE DATABASE. THIS CASE CAN GO WORSE IF THERE ARE MORE THAN
> 1000 USERS. WHAT IS BETTER DATABASE DESIGN, MORE TABLES WITH LESS
> RECORDS OR LESS TABLES WITH MORE NO.OF RECORDS?
>
> An alternative design can be as follows
> Tables:
> Users, Customers, Suppliers, BankAccounts, Transactions ....and so
> on.
> User: User_Id, UserName, .....
> Customers: User_Id, Customer_Id,.....
> Suppliers: User_Id, Supplier_Id,....
> BankAccounts: User_Id, BankAc_Id,....
> Transactions: User_Id, Trans_Id.....
> .
> .
> .
> .
> All these tables would be created at the design time only and as a new
> user is created a record is added to the users table. When the user
> adds Customer the record is added to the Customers table... and so
> on... The problem with this design is that Customers,Suppliers,
> BankAccounts... etc tables would contain records for all the users
> and thus the record fetching time for a particular user increases as
> many times as there are users in the Database. Another problems with
> this design is that more than one user would be connected at run time
> will access the same tables, and for even a single user the complete
> table will be loaded in memory.
> WHICH DESIGN SHOULD BE USED AS FAR AS SPEED OF SERVER IS CONCERNED?
> PLEASE HELP WITH CONVINCING REASONS.|||"Rushikesh" <rbaiwar@.sify.com> wrote in message
news:2b29906c.0307142123.1d020801@.posting.google.c om...
> I am designing a WEB BASED Accounting Software with ASP and SQL
> Server. For this I need some help for the Database design. My design
> is as follows.

You may need a lot more than just these tables.

> and thus the record fetching time for a particular user increases as
> many times as there are users in the Database.

Oh no, it doesn't!

> will access the same tables, and for even a single user the complete
> table will be loaded in memory.

It is ridiculous if you write your code to do that.

> WHICH DESIGN SHOULD BE USED AS FAR AS SPEED OF SERVER IS CONCERNED?
> PLEASE HELP WITH CONVINCING REASONS.

Second.
Read a few texts about database design and normalization.

--
Cheers
Nuno Souto
wizofoz2k@.yahoo.com.au.nospam|||Rushikesh (rbaiwar@.sify.com) writes:
> I am designing a WEB BASED Accounting Software with ASP and SQL
> Server. For this I need some help for the Database design. My design
> is as follows.

As whether you should use SQL Server or Oracle, I don't have an opinion.
I come from the SQL Server side, but these questions have the same answer
for any enterprise DBMS.

> I)User table: User_id, UserName....
> Users (e.g. John Smith) Each User would contain a following Group of
> tables
> a)Customers
> b)Suppliers
> c)Bank Accounts
> d)Transactions

This is a completely unacceptable solution, and in completely violation
of the relational model. Just forget about it.

> All these tables would be created at the design time only and as a new
> user is created a record is added to the users table. When the user
> adds Customer the record is added to the Customers table... and so
> on... The problem with this design is that Customers,Suppliers,
> BankAccounts... etc tables would contain records for all the users
> and thus the record fetching time for a particular user increases as
> many times as there are users in the Database. Another problems with
> this design is that more than one user would be connected at run time
> will access the same tables, and for even a single user the complete
> table will be loaded in memory.

Your assumptions here are entirely correct. Or to be less polite: they
are flat wrong in places.

An enterprise DBMS are built for implementing this kind of solution.
With proper indexes, the difference in access time to a certain row
if you have 100 rows or million rows in the table is neglible. Or if
you for that matter have 100 million rows.

Neither does an enterprise DBMS load an entire table into memory, because
there is an access to a single row. I cannot speak for Oracle, but SQL
Server will read the pages you access into memory, and if one user is
very active, all his pages may be in cache, whereas the pages for a user
who is on vacation are only on disk. Pages per users? Ah, didn't I mention
indexes? It does seem reasonable from you mentioned to have clustered
indexes on user ids.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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