Script Out The Entire Database Objects - Is There A Good Way?
Jul 27, 1998
After found out the `transfer object` command in MS SQL EM didn`t work well
in my databases, I started code by myself to generate the `Create xxx` statements to copy objects across databases (structure only) using SQL.
I`ve coded out most of them, logins, defaults, UDT, tables, stored procedures,
but then I started to have problems with primary keys and foreign key const.
Has anyone tried to do the same thing before or is there some SQL scripts created to handle this already? Please advise me.
I am thinking to use SQL-DMO with VBscript now. But I still prefer a good
SQL script (old thought, the DB-Lib based `isql` is more reliable than the
SQL-OLE).
Thanks for any suggestion.
- Ken
View 3 Replies
ADVERTISEMENT
Aug 1, 2007
At my current employer we are struggling with the best way to manage security and deployment of a project that contains databases, SSIS, SSAS and SSRS components, using configurations.
Environment (Dev):
3 SQL Server databases, all using mixed-mode security, using SQL Server security credentials.
12 SSIS packages; one master package, eleven child packages, 3 shared data sources
1 SSAS database; one cube, 15 dimensions, three referenced data sources from the SSIS project (in same solution)
6 SSRS reports, one data source to cube (not shared- doesn't appear SSRS can share datasources among other projects in the solution? Why?)
Everything runs fine in development. Now comes the tricky part.
Deploying SSIS and SSAS into production environments:
-Packages use XML config files for connection strings to three relational data sources.
-Deploy to SQL Server storage. Deploy wizard copies package dependencies (including XML config files) to default location set in INI file. When I do this, no config file shows up in remote server (remote server not set up identical to local, so directory does not exist. Need UNC path?) So, being a developer with no "special" permissions on the PROD server, what security permissions is allowing the deployment wizard from copying files to this location on a production server?
-Using a deploy script using dtutil doesn't copy the SSIS dependencies. Is this matter of using COPY or XCOPY to copy the configuration files to the dependency location? Again, in real-world practice, do developers typically change this location in the INI file to another location, or stick with the default. In either case, how does security work that allows files to get copied to the remote folder? (i.e. manual, or SQL Server manages this file folder permission through some other magic)
When using SSMS and running the package after being deployed on the remote server, if the config path is the default (e.g. C:program filesMicrosoft SQL Server90DTSPackages...) it appears to be read from the local machines directory rather than the remote machines directory path (do I need to use UNC paths? The wizard doesn't give this option it seems)
-When scheduling the job from SQL Agent, does the proxy account need permissions to the folder the config files sit in?
-What about the roles security on the packages themselves? Where does the server roles come into play (dtsltuser, dtsadmin)
-Because the SSAS project uses connection references to the SSIS project in BIDS, and SSIS project uses configurations, will SSAS pick up on these connections?
-What about impersonation levels for SSAS? Leave all data sources set to default, and set the database impersonation level to "UseServiceAccount"? What if the developer is not the same as the OLAP administrator on the production server? In this case, Use Service Account isn't an option, and neither is the current users credentials.
-SSAS database also has security for Full Control, but still doesn't prevent security at the data source level within the database (talking about impersonation level, not source db credentials)
-How can SSRS connections leverage other shared connections?
As you can see, there are a ton of security considerations, none of which are intuitive and can be configured multiple ways and actually work (and a ton of ways that won't work).
I need a simple cheat-sheet about each step to take to configure this so multiple developers can work without interruption, hot-deploying SSIS, SSAS, and SSRS changes into different environments (QA, PROD).
-Kory
View 2 Replies
View Related
Oct 11, 2006
I'm sure this has been asked plenty of times before, so I'm after a link to a good answer.
I have tens of thousands of milk crates, holding dozens of different types of milk in hundreds of locations. I am used to working with objects but not databases. For this situation however I want the security of SQLServer transactions to track, for example, when a robot moves a crate from one location to another.
I am thinking of using SQLServer as a store. On startup I want to get my ecosystem of objects out of the store. While I am running, I'll just use objects. When I change an object property I want it to securely persist. I don't want to snapshot the whole menagerie of object states, just update the values that changed. Which will sometimes include the addition or deletion of objects. How do I do this? Is there an example somewhere that does this (or approximately this)?
I use VB and have Visual Studio 2005. (Which, by the way, is stunning. I thought all that "you will use less time and code more and better" talk was just hype. But its for real. Amazing product.)
tia
John
View 1 Replies
View Related
Jul 31, 2007
What is the best way to create a blank copy of my database? I really want some of the tables to keep the data in them, these are some of the lookup table.
I appreciate your help.
View 4 Replies
View Related
Jul 12, 2005
Hi, I'm a noob who sucks at programming, and sucks even more when it comes to database.
I'm operating a small website with an SQL Server database that drives my shopping cart. I want to close this website down, but I don't want to lose all the data in the database in case I want to do something in the future.
How do you backup the entire database into a file so i might be able to import it at later times? Do I have to do this with SQL, T-SQL, or the manager?
View 3 Replies
View Related
Jul 23, 2004
I have built a template database which I'm finally pleased with, however I want to periodically duplicate the design - not data into a new database. How can I duplicate a database? I was hoping to right mouse, copy, then right mouse, paste, and then be prompted for the new name but no such luck.
View 6 Replies
View Related
Mar 11, 2004
Is there any way to do a complete database search in SQL server? For instance, if I have a criteria "DBFORUMS", I would like to scan through all user tables in my database to get all records with the word "DBFORUMS" stored, just like want we are doing in "Quick Search" in dbforums site.
Any ideas?
Thanks in advance.
View 6 Replies
View Related
Aug 28, 2005
Hi friends
Suppose i have a table of 100 cols and 10000 rows i want to search a particular field called 'Newyork' . I dont no what the col is ?
Can anyone tell me how can i search that
Vicky
View 2 Replies
View Related
Dec 28, 2005
Hi, this is my first post on these forums, so please excuse me if this topic has already been covered.
I'm currently working in a power station for student vacation work placement. I need to export data from a database that gets it's data from machinery and inputs out in the plant. The machines that provide this input put it into a database, and I need to find the relevant data to export.
My problem is that, in some cases, the sample data that i'm given may be under different field names, in a completely unrelated table. I was looking for a way to search the entire database (250+ tables) for a certain string, so I can find where it is in the database, and run queries on the table it originates from. For example:
My sample data shows me that I have an object with the ID Y03A3DEA_TH1. I know this ID will occur somewhere else in the database, but i'm just not sure where.
If anyone knows of any way that I can search the entire database for specific data, either using tools in MS SQL 2000, or 3rd party apps, i would greatly appreciate their help.
Thanks a lot,
Jack Smith
View 2 Replies
View Related
Feb 4, 2008
Hi,
Is it possible to copy a database from the existing database. I mean to say creating a duplicating database.
Note: I can do it by using "Generate SQL Server Script Wizard" or "Database Publishing Wizard" which saves a some filename.sql and later executing this will create another database by assigning some xyzdatabase name.
My main motive is copying a database in a single line script without using this lengthy sciprt.
Example
FirstDatabase (contains 10 tables 10 stored procedures)
SecondDatabase (also contains 10 tables 10 stored procedures)
If possible, can you write a script that attach a database from the C:MyTemporaryDatabase
Because this i need through programming Visual Basic using SQL Server. I want to create some temporary database.
Waiting for your reply.
Thanks.
Best Regards
Kashif Chotu
View 5 Replies
View Related
Jan 20, 2007
Im building a site on my pc using SQLExpress and i have a question about something. The database im working on has data inside the tables and i was wondering how i would move that to a different server though a script with all the data in the table.
View 3 Replies
View Related
Apr 1, 2004
I'm running the below query for listing all the primary key in a database but it is retiving 0 rows.
DECLARE @cmdstr varchar(100)
CREATE TABLE dbo.TempTable
(TABLE_QUALIFIER varchar(10),
TABLE_OWNER varchar(5),
Table_Name varchar(10),
COLUMN_NAME varchar(15),
KEY_SEQ int,
PK_NAME varchar(50),
)
--Create Stored Procedure String
SELECT @cmdstr = 'sp_msforeachtable ''sp_pkeys "?"'''
INSERT INTO TempTable EXEC(@cmdstr)
SELECT Table_Name,COLUMN_NAME,PK_NAME FROM TempTable ORDER BY Table_Name
DROP TABLE TempTable
Any body can give advice regarding this.
Thanks,
Ravi
View 14 Replies
View Related
Nov 21, 2013
I have to replace gl_number 25050-80 with gl_number 25050-80 in the entire database. I know how to do that in a table but not in all tables.
View 2 Replies
View Related
Apr 10, 2014
I want to replace every single 1/1/1900 date I have in my system with null, on an entire database level. I never want them, they cause multiple issues, and I need them gone.
I have made a trigger on a test table that works for inserts, but not updates:
CREATE TRIGGER UpdateDate ON test FOR INSERT AS
IF EXISTS (select * FROM inserted WHERE CheckDate ='1/1/1900')
BEGIN
UPDATE test SET CheckDate=Null
FROM test T INNER JOIN inserted I ON T.AutoID=I.AutoID
WHERE I.CheckDate='1/1/1900'
END
Also, it only works on this table, and the specific field mentioned.
I am looking for a global, databse level trigger that will replace 1/1/1900 with null whenever it appears from an insert, or an update statement.
View 5 Replies
View Related
Mar 17, 2008
I have saved a word somewhere in somefield which i can't remember..
is there any way to search for the word in the entire datebase and show in which table and field it is stored?
View 3 Replies
View Related
Jan 14, 2005
Hi,
I'm found this sample for BACKUP DATABASE statement in SQL Server 2000 Book
Online, but is seemed that only save file to server. How can I save backup
file to local computer? or there is other way as easy as this statement for
backup/restore database?
-- Create a logical backup device for the full MyNwind backup.
USE master
EXEC sp_addumpdevice 'disk', 'MyNwind_1',
DISK ='c:Program FilesMicrosoft SQL ServerMSSQLBACKUPMyNwind_1.dat'
-- Back up the full MyNwind database.
BACKUP DATABASE MyNwind TO MyNwind_1
Thanks,
Tien,
View 5 Replies
View Related
Jan 14, 2005
Hi,
I'm found this sample for BACKUP DATABASE statement in SQL Server 2000 Book
Online, but is seemed that only save file to server. How can I save backup
file to local computer? or there is other way as easy as this statement for
backup/restore database?
-- Create a logical backup device for the full MyNwind backup.
USE master
EXEC sp_addumpdevice 'disk', 'MyNwind_1',
DISK ='c:Program FilesMicrosoft SQL ServerMSSQLBACKUPMyNwind_1.dat'
-- Back up the full MyNwind database.
BACKUP DATABASE MyNwind TO MyNwind_1
Thanks,
Tien,
View 4 Replies
View Related
Apr 4, 2012
I am attempting to only query the lowest year, and month from my entire production database. I am using the following query and when I do it individually it will bring back the lowest year but also with additional years. How can I remove those other years so I can only query the lowest year and month from the entire database?
Code:
select distinct RRCID, MIN(YEAR)as YEAR, MONTH
from Gas_Prodv1
where RRCID = 1221376
group by RRCID, MONTH
order by YEAR, MONTH
Results:
122137620071 <----- Only want this record
122137620072
122137620073
122137620075
122137620076
122137620077
122137620078
122137620079
1221376200710
1221376200711
1221376200712
View 12 Replies
View Related
Jul 15, 2007
This problem may appear trivial to you guys but is troubling me since quite some time now! The problem is that I created a website using express studio happily and it worked flawlessly on the local host. Now I want to move it to a remote server and my host created a database name ASPNETDB for me on the server. The problem is that that ASPNETDB database is virtually empty and I want to copy all my tables,stored procedures etc etc(complete database) to the remote ASPNETDB database. How can I do that? In the management studio express edition there is no command available to copy paste all contents of the database to another database. Please help I am quite confused!
View 4 Replies
View Related
Jul 16, 1999
I need to create some kind of log file or table that will record whenever an insert, update or delete is made to any table in a database. I have seen triggers that do this kind of thing on a table level. Can this be done with a trigger or a stored procedure on a database level? If so some kind of example or syntax would be great.
TIA.
Mike
View 1 Replies
View Related
Aug 3, 2012
Any command which will make create script for my entire database including all sp's , tables, functions..i dont want to backup the db ..i want to create scripts for creating an entire database schema (only the structure and not data)
View 1 Replies
View Related
Aug 28, 2015
I have Sql server 2008 database with 200 GB in production.But It will not support multilanguage Since all the tables and procs is having varchar datatype.
I need to change the Entire Tables and procs to support Multilanguage(nvarchar,ntext) .
I have Many Huge data tables with numerous index .ALso I could not afford too much down Time. How can I perform.
View 8 Replies
View Related
May 14, 2014
how to copy one database tables, columns, store-procedures. to another database without any error
View 2 Replies
View Related
Sep 18, 2013
I'm trying to create a query that searches an entire database for keywords inside of the columns for each table within the database. For instance my tables have 2 columns one named ID and the other Permission, I'd like it be able to return all the lines that are associated with that keyword. So if I search "Schedule" it returns all the lines containing that word in it within that database.
View 6 Replies
View Related
Dec 14, 2007
I have read the previous threads on the bugs with this task mainly: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1438968&SiteID=1 . These are great posts that helpmed me avoid wasting time. I haven't seen one yet that addresses copying an entire database including the sql server logins.
I would like to import the ENTIRE database from one (2005) server to another(2005) using the SSIS Transfer SQL Object task (not just sprocs,tables,views and functions). I have figured out how to pull the tables,views,sprocs and functions ... by using an execute sql task to drop these objects. But I cannot get this to work for users since the user dbo cannot be dropped and guest can only be disabled. I am creating a new database (this is the database where the sql objects will be copied to) via management studio to test this. There has to ba a way to get this working ... Microsoft must have published some sort of KB article on this task or a Script Task using SMO object calls. If need be I can drop the entire database on the target machine and have SSIS recreate it.
The only reason I'm willing to take a risk with SSIS rather than backup and restore is because of time constraints (I assume the SSIS task is faster) and backup storage administration.
declare @name varchar(200)
declare @object varchar(200)
DECLARE object_cursor CURSOR READ_ONLY FORWARD_ONLY FOR
select table_name,table_type from INFORMATION_SCHEMA.TABLES
union
Select name,'SPROC' table_type from sys.procedures Where [type] = 'P' and is_ms_shipped = 0 and [name] not like 'sp[_]%diagram%'
union
select name,'FN' from sys.objects where type_desc like '%FUNCTION'
OPEN object_cursor
FETCH NEXT FROM object_cursor INTO @name,@object
IF @@FETCH_STATUS <> 0
PRINT ' <<None>>'
WHILE @@FETCH_STATUS = 0
BEGIN
if @object = 'BASE TABLE'
begin
exec ('drop table ' + @name)
end
else if @object = 'VIEW'
begin
exec ('drop view ' + @name)
end
else if @object = 'SPROC'
begin
exec ('drop procedure ' + @name)
end
else if @object = 'FN'
begin
exec ('drop function ' + @name)
end
FETCH NEXT FROM object_cursor INTO @name,@object
END
CLOSE object_cursor
DEALLOCATE object_cursor
View 4 Replies
View Related
Apr 25, 2008
I've had issues where backup up and restoring data from sqlserver2005 does not reattach the data to the correct users. Any tips on how to best accomplish full database moves where data is owned by different security users?
thanks,
View 2 Replies
View Related
Dec 29, 2007
Hey I was wondering if you
all could help me with my database. I am very noobish as databasing and I am
not sure what I have is good or bad. This is very long since I tell you basically
what my whole site does/will do. This is in hopes that it will give you a
better understanding of how many database will work.
Japanese WebSite
Purpose of the site:
This sites intention is to help people learn the 2 basic
Japanese character sets Hiragana and Katakana.
Hiragana is usually used for Japanese words and contains 46 basic characters,
plus a set of special characters. Katakana is usually used for foreign words
and also contains 46 basic characters plus a special set of characters.
Hiragana and Katakana are written differently when they are written with Japanese
characters. However if you right them in romaji (this is basically writing words
as if you where writing English and does not involve using characters. This is
used for foreigners while they are learning how to write the Japanese
characters).
Hiragana and Katakana are pronounced exactly the same.
This site will use a quiz style format. The user will first choose from the set
of Hiragana characters then the Katakana characters. Once chosen the website
will randomly choose a character and display the Japanese symbol of it. The user will then write the romaji
representation in the text box provided.
It will be then checked
and if the user is right they will get a new symbol. If they are wrong they
will have try again. The purpose for
this is for the user to get use to see how the character looks like but at the
same time writing in romaji what will be easier for them. This should make it
easier for them the next time they see the character to know how to say it.
Version 1
The site will go through many versions. This version I am
focusing on using C#,asp and ms sql. Later on in future versions ajax and
javascrip will be introduced and parts will be changed. Version 1 also uses CSS
and html.
Version 1 will contain these features.
1. A
practice section. A user will go through a wizard like setup that will first
let them choose from the basic 46 Hiragana characters (the other special
characters will be added later). They will then go to the next setup and choose
from the 46 basic Katanana characters. After they go to another section that
asks them how many repetitions they want to go through. If the user is a member(free membership) they
will have the option to save their settings that will be stored and later
available on the front page called quick links. The next page of the wizard is
actually going through the quiz. Once they are done they will see a summary of
how many they got right,wrong and how many they needed assistance on. At this
point they will be able to view a chart to see their progress(current chart
showing there last attempt, a week one
and a month one). These charts will be either made using c# or ms sql(I saw a
something that says you can make something like that but have not confirmed
it). I may do both ways for a learning experience.
2. A Registration form, login and logout.
3. Quick
link. Quick link will be for registered members so they don’t have to go
through the wizard every time to make their practice quiz up. A user will go
through the wizard once and choose what they want and save it. Quick link will be on all the pages in the
form of mostly likely a grid view. A user will be able to store 3-5 different quiz’s
(in the future maybe more). A user can then click on the quick link and it will
just take them to the quiz. The user will also will be able to update their
quiz through the grid view. This will take them to the start of the wizard and
they can go through it again and then just save over it. They will also be able
to delete there quick links through the grid view.
Currently done
Half of step 1 has been completed. Currently a user can
go through the wizard and select what they want and choose how many repetitions
and go through the quiz. However the summary and charts are not done.
Future versions.
Design will be a major part of it since I have not
focused on this at all. Also since I wanted to experiment with dynamic controls
I made all the checkboxes for the selections dynamically. This caused a problem
that a user must hit a save button since the controls have to get recreated and
updated.
I think normally this might not have been a problem but
since I since I am using a multiview(to create a effect that the wizard is all
on the same page and not the whole page has to be reloaded) it caused some
problems(I can’t really remember since it was a couple months ago when I
started but had to stop due to the amount of school work). This is why I want
to go back later and make them with ajax. This also makes another learning
experience.
Where I am at now.
When I started this I really did not think much of my
database mainly because I am not good at data basing and don’t really like it that
much. I have had a course in data basing (was with oracle though)that was part
of my program. At the time I did not understand very much since I found the
teacher going too fast and he started at chapter 8….. Also when you got 7 other
courses with it it’s hard to learn lots.
I have gone through some tutorials and stuff. I am not
very keen on reading a database book since I just don’t have the time since I
want to also read a book on c#, javascript, ajax and so forth. Also I don’t
know how much I will retain/learn from a book. With some of the stuff I just
find its better to try to something where you will be interested in and then
when needed read chapters of a book or ask questions. This is what I done for my c# stuff like I
had a c# with asp course and 2 months after the courses I started this site and
I had to ask myself what the heck did I learn in that course since it seemed I
knew nothing(and I was close to top in my class). With pounding it out and
reading I was able to accomplish what I needed. Like I still want to read a
book on C# but for now it’s just not going to happen same with Data basing.
All the pervious information was so you can understand
what my website is trying to do so you can better evaluate my database design
and answer my questions.
First Design of my
database.
This is when I just made it up on the spot and did not do
any database design or anything.
Question 1: I
forgot how to limit select query results. Like If I just want to show 10 query
results how do I do this?
I have currently 2 tables How one called Hiragana and the
other Called Katakana
HiraganaID = PK
HiraganaID HiraganaCharacter HiraganaImage
----------- -----------------
--------------------------------------------------
1 a Images/hiragana/a-o/a.jpg
2 i Images/hiragana/a-o/i.jpg
3 u Images/hiragana/a-o/u.jpg
4 e Images/hiragana/a-o/e.jpg
5 o Images/hiragana/a-o/o.jpg
6 ka Images/hiragana/ka-ko/ka.jpg
Katakana
KataKanaID = PK
KatakanaID KatakanaCharacter KatakanaImage
-----------
-------------------------------------------------- --------------------------------------------------
1 chk_Kata_a
Images/katakana/a-o/a.jpg
2 chk_Kata_i
Images/katakana/a-o/i.jpg
3 chk_Kata_u Images/katakana/a-o/u.jpg
4 chk_Kata_e
Images/katakana/a-o/e.jpg
5
chk_Kata_o
Images/katakana/a-o/o.jpg
I quickly found that this design was very bad since I was
unable to add new rows very easily. Each character set has each of its characters
in rows of 5. Some rows however have only 3 romaji characters so what they do
is leave the other cells blank representing that they don’t exist. I wanted to
do this too so I figured the easier way would be to have just null rows of
data. So say for “ya,yu,yo� it would be like this “ya,null,yu,null,o�. That’s
how it would appear in the database so when my c# would read it in it would not
have to account for rows that had less than 5 characters since it would have
already the nulls for it.
You can check a chart out here: http://www.alpha.ac.jp/japanese/img/moji.gif
So with this poor design it would result me in basically
rewriting the database.
I have thought of 2 possible ways to. I am not sure if
either way is good but I will try to explain my reasoning as much as I can.
Possible Way 1
In this approach I have 5 tables.
Hiragana
This table stores all the information about Hiragana
Characters
CREATE TABLE [dbo].[Hiragana](
[HiraganaID] [int] IDENTITY(1,1) NOT NULL,
[HiraganaCharacter] [varchar](5) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL,
[HiraganaImage] [varchar](50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL,
[SortOrder] [numeric](3, 0) NOT NULL,
CONSTRAINT [PK_Hiragana] PRIMARY
KEY CLUSTERED
Katakana
This table stores all the information about Katakana
Characters
CREATE TABLE [dbo].[Katakana](
[KatakanaID] [int] IDENTITY(1,1) NOT NULL,
[KatakanaCharacter] [varchar](50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL,
[KatakanaImage] [varchar](50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL,
[SortOrder] [numeric](3, 0) NOT NULL,
CONSTRAINT [PK_Katakana] PRIMARY
KEY CLUSTERED
Quick Links
This table will store the users quick link information.
CREATE TABLE [dbo].[QuickLinks](
[QuickLinkName] [varchar](50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL,
[UserID] [int] NOT NULL,
[SavedCharacters] [varchar](200) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SavedImagePaths] [varchar](200) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
Users
This will store login information.
CREATE TABLE [dbo].[Users](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[UserName] [varchar](30) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EmailAddress] [varchar](50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Password] [varchar](50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DateTimeStamp] [smalldatetime] NOT
NULL,
CONSTRAINT [PK_Users] PRIMARY
KEY CLUSTERED
Charts
This will store all information that will be used for
charts.
CREATE TABLE [dbo].[Charts](
[PracticeNum] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NOT NULL,
[Correct] [numeric](3, 0) NOT NULL,
[Wrong] [numeric](3, 0) NOT NULL,
[AssitanceNeeded] [numeric](3, 0) NOT NULL,
[TimeDateStamp] [smalldatetime] NOT
NULL,
CONSTRAINT
[PK_Charts] PRIMARY KEY
CLUSTERED
(
Relationships
Users and QucikLinks have a PK to FK relationship this is
because quicklinks is dependent on the user having a user account. It also away
to identify which QuickLinks will belong to who.
Question 2: Currently I don’t have a primary Key for this
table what would be a good primary key for this table?
QuickLinks also does not have any relationship to either
to HiraganaCharacters or Katakana Characters. This is because I did not feel it
was necessary to grab the information from here. I simply could just grab it
from the array that holds this information from my c# code.
Question 3: How can I make this table for all users? Like
Say they choose 5 characters and save it as QuickLink1(as the
QuickLinkName). Should each of these 5
characters gets its own line or should it all be saved in one row? Or should I
even have another table to hold this data?
Option 1
QuickLinkName UserID
SavedCharacters SavedImagePaths
---------------- ----------- ------------------- --------------------------
QuickLink1 1 a image/a.jpg
QuickLink1 1 i image/i.jpg
QuickLink1 1 u image/u.jpg
QuickLink1 1 e image/e.jpg
QuickLink1 1 o image/o.jpg
QuickLink1 2 a image/a.jpg
QuickLink1 2 ya image/ya.jpg
QuickLink1 2 ki image/ki.jpg
QuickLink1 2 yo image/yo.jpg
QuickLink1 2 n image/n.jpg
Option 2
QuickLinkName UserID
SavedCharacters SavedImagePaths
---------------- ----------- ------------------- --------------------------
QuickLink1 1 a,i,u,e,o image/a.jpg, image/i.jpg,
image/u.jpg, image/e.jpg,
image/o.jpg,
QuickLink2 2 a,ya,ki,yo,n image/a.jpg,
image/ya.jpg,
image/ki.jpg, image/yo.jpg,
image/n.jpg,
Charts table also has a Relationship with Users
The thinking for this one is that Charts will use the
UserID to tell who this data belongs too. It will store the number of correct
answers, wrong answers and AssitanceNeed. This table will also hold a timeDateStamp.
The reason for this is because I want to have a chart that displays the last
Quiz they did and also the stats of a week’s worth of quizzes, and month’s
worth of quiz’s. With a timeDateStamp I should be able to add up all those columns
and then display them for whatever period I choose.
Second Possible
Way.
Characters
CREATE TABLE [dbo].[Chracters](
[CharacterID] [int] NOT NULL,
[CharacterName] [varchar](5) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL,
[CharacterPath] [varchar](50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL,
[SortOrder] [numeric](3, 0) NOT NULL,
CONSTRAINT [PK_Chracters] PRIMARY
KEY CLUSTERED
This way uses 4 charts instead of 5. The main difference
is that the Hiragana Charts and Katakana Charts have been merged into one
table.
This way has another relationship between Characters and
Quick Links. This will store the Character
ID instead of the actual Characters and Path through the use of C# code. When a
user selects a box it will make a note of the CharacterID. When it is time for
the user to use the QuickLink it will join the tables together and filter out
the CharacterIDs. Giving me the CharacterName and Path.
The problem with this way is that well the code I written
will need to be changed since currently it grabs stuff from 2 tables into one.
I don’t know if this would save anytime over the first way either.
I hope these are sort of close to an alright design since
I did try to think about how my website would interact with the database.
Sorry for it being so long.
Summary of Questions
Question 1: I
forgot how to limit select query results. Like If I just want to show 10 query
results how do I do this?
Question 2: Currently I don’t have a primary Key for this
table what would be a good primary key for this table?
Question 3: How can I make this table for all users? Like
Say they choose 5 characters and save it as QuickLink1(as the
QuickLinkName). Should each of these 5
characters gets its own line or should it all be saved in one row? Or should I
even have another table to hold this data?
View 9 Replies
View Related
Mar 5, 2008
hi experts,
i have a postcode database that i need to update. the database cnotains of 6tables, the file i ahev has all the information at once, so i have to organize it and insert records into the appropriate tables.
this is the first time i'm doign this so i would like to know what the best way to do? do i need to create a stored procedure or a script, or may be something special and efficient that i do not know yet.
any advise will be very appreciated
thanks in advance
View 1 Replies
View Related
Sep 12, 2007
I have inherited some responsibilities for which I'm not really qualified, so I'll push on through and maybe not totally fall down.
Assume 10 50GB databases, each in a single MDF file. All these MDF files reside on the C drive (the only drive on the system), running SQL 2005 in a 32-bit Windows 2003 or later, 8GB RAM.
The C drive is 6 physical disks in RAID 5, say about 1.0 TB or so. We have 4 dual-core processors on the box.
We have limited simultaneous users, initally about 8 users doing very heavy write on all tables in any one database. Later, we have about 15 users connecting via Web interface, and doing very heavy read and light writing. Each of the 10 or so database has this lifecycle: Heavy write for about 2 weeks (load data) then heavy read for about 1 month (research and search data), then nothing ever again (db is taken offline).
Of course, this is not enough information to go on, but let's just go on it anyway.
My TempDB, Log (simple recovery), Index etc is all on the same RAID 5 drive (C).
I have two basic questions I'd love to hear feedback on:
1. Is there any real advantage to creating 8 Data files for my database (one per processor core)?
2. Given that the hardware people here REALLY don't want to change anything, what should I fight for first:
a. Separate drive for LOG files?
b. Separate drive for TempDB?
c. Something else
Thanks in advance.
View 1 Replies
View Related
Nov 20, 2006
Hello,
I need a tool that will let me model a SQL Server 2005 database and then generate the tables, constraints, etc. from the model. I've never used a modeling tool so my knowledge is quite limited. I don't need to model or reverse engineer an application - my sole concern is on the SQL Server database side. I'm not concerned if the tool integrates with Visual Studio. And, of course, price is one consideration.
Are there any good tools that I should look at?
Thanks,
BCB
View 3 Replies
View Related
Mar 13, 2008
I am actually a newbie to asp.net and i m using ASP.net 3.5 i.e, VWD 2008. i am using it for the first time as my tool to develop a website for my final year project. i am planning to develop an online job recruitment site like www.monster.com. Rigth now i am confused how will i manage my database. i've learned to use databinding concept of SQL SERVER in VWD 2008 but will it be enough to handle such huge # of Job postings and employers and as well as Resumes in pdf of word format? or do i have to create a separate databse in SQL Server and to connect it with my website? i am confused at the moment. please help me in this matter.
Regards,
Jigzy
View 6 Replies
View Related
Aug 18, 2005
I want to create a duplicate database in sql 2000 using asp.net from a webform
I created a database using CREATE DATABASE .......
But how to copy tables, views, stored procedures to newly created
database from old using asp.net from webform
Is there any another method to create a duplicate database with another name
from existing database on same server ?
View 5 Replies
View Related
Jun 20, 2008
1) I can't get the 'copy database' function to work from SQL Server 2008 to SQL Server 2005. I connect ok. Everything goes to the last step and then it fails.2) I cant get a SQL server 2008 backup to restore on SQL SEerver 2005 either.
The only way I know that works is to script the creation of all tables then export and import. This does work.
How can I get the 'entire' database, structure and data, from 2008 to 2005?
ThanksSQL newbie.
View 2 Replies
View Related
