|
Can anyone offer their thoughts/suggestions on how to best breakup scripts for managing a db? For instance, I'm starting a new project, and have already used SQL Server Manager to create the tables, pk's, indexes and the relationship between tables. Does this need to all be scripted in one large script?
Moving forward, any changes that I may apply to the db (e.g. new table, new index) will have to be saved to the db. Should I create scripts for any incremental changes? What is the best way to capture these changes? Is there something in sql server manager that will say "capture changes" and script just the changes??
I guess the advantage of scripting all of your incremental scripts would be to easily back out changes that may have caused problems with your code.
Do you create one giant script with a bunch of ALTER statements for your VIEWS, SP's, FUNCTIONS, etc...?
Thanks
|
|
|
|
|
You may want to look into SQL Triggers 
|
|
|
|
|
You should think of your db scripts like source code and create a SQL folder in your source structure that you check in and out of your version control system. (You are using one ? Right ?) You can then see what the differences are between your 1.0 and 1.1 create table scripts are.
Then consider this:
1) Create a base set of scripts called "create_XXX" where the "XXX" is the database object like tables, views, trigger, sproc, index. These scripts should be designed so that you could create an entire database schema from a blank database instance.
2) Create a script called "upgradeSchema" which will contain all of the alter table, alter index, create view, etc that may have changed from one release to another. You should design this upgradeSchema script in such a way that it could be run more than once on a database and have no undesireable effects. In other words, make no assumptions about the current schema state, test to see if a column already exists, is the data in the column null, etc. Lots of error checking going on here ...
Also you may want to consider 2 other areas:
System data - This script would insert values into lookup tables in order for the system to function.
Sample data - This might be enough data in tables so that you can publish a student guide showing some basic operations of your application.
What I've presented isn't a complete solution, but it should give you something to think about ...
If you are not using a version control system, your asking for trouble. Look into Subversion http://subversion.tigris.org[^]
Or if you a small Windows shop, you could always use Visual Source Safe, from Microsoft.
|
|
|
|
|
Wow...you've definitely provided great examples!
1) Should the base set of scripts (Create_xxx) pretty much create the 'version 1.0' db? Then, I imagine I would run my alter scripts to get it updated to the current version?
2) Should I create one or multiple ALTER scripts for each release? For example, if my project has 8 releases, I probably should have 8 alter scripts right? Or should I simply have one version for my ALTER script and place everything in that one script.
3) Is there a way I can take my existing db and script it out within SQL Server mgr?
THanks!
|
|
|
|
|
1) Yes, the base set of scripts would be the 1.0 version. However, you can also think about the 2.0 version as being a new set of "create" scripts. Each time you have a major release (Ver x.0), the create scripts would be able to create a fresh schema. In a major release, you could have a conversion script like UpgradeSchema_v1_v2, which would take the system from any v1.x release and get them right up to a 2.0 release.
2) In the past, I have created 1 large ALTER script. I prefer to call it something like UpgradeSchema.sql for clarity. In this script you need to design it so that it can take a database from a v1.0 to a v1.1, v1.2, v1.3, etc in one shot. The script has to be designed so that it doesn't care which database version the system is at, perform enough checks so that nothing errors out. For example, if you need to add a column to a table, then check to see if the column already exists before adding it. You will become very familiar with the SYSOBJECTS table when writing these types of upgradeSchema scripts.
3) You can use the SQL server Management Studio to create your base scripts by highlighting each table, right click, choose "Script Table As, Create to, New query editor Window", this will give you a jump on creating your base set of scripts. Remember to create a separate set of scripts for each database object; one script creates all tables, another one creates views, etc ... You will see that running these scripts requires that they be run in a certain order otherwise they won't execute. For example, you can't create views unless you have already created table. 
More to think about.
|
|
|
|
|
Great..thanks for all your help!
|
|
|
|
|
I am new to SQL Server 2005...and was reviewing the following msdn url for creating logins and users: msdn.microsoft.com/en-us/library/aa337552.aspx
Can someone explain what the difference is between a db login and a db user?
From my understanding, you use the db login within SQL Server manager, or with your .NET code, correct? Then, you create a user and associate it to a login? Why? What's the relevance of knowing or having a user associated to a login, when the login is sufficient alone? Would I use the user logging into sql server mgr or within my .net code?
Do I need to associate a login with a user?
thanks!
|
|
|
|
|
A login connects you to the SQL Server instance. A database user defines what privileges a login has within a particular database. If you have multiple databases on your server, you probably don't want everyone who can connect to the server (login) have some kind of access to every database. So you could have a login associated with one database or with several.
Someone's gotta be the last to know, but why is it always me?
|
|
|
|
|
I am currently working in a MSSQL 2000 version and all of the paginated results for various scripts are done using 3 or more SELECT sub-queries.
Unlike MySQL which only requires a single query --- I cannot see this being very efficient, especially the larger your database grows?
I have done some googling and it turns out that row_count() is MSSQL answer since 2005 -- can anyone confirm whether this is the case and better yet refer me to some resources which I can read over the weekend to freshen up on MSSQL?
Cheers,
Alex
|
|
|
|
|
Correct, ROW_COUNT() is a very efficient way to get a row count in SQL 2005.
|
|
|
|
|
Do you happen to know of any benchmarks that compare the old style to the new?
|
|
|
|
|
I am using a windows forms app vb.net 3.5 SP1 w/VS 2008. I have a DS set up. I have about 4 DateTime Columns in the DS in a particular table that are defined in the SQL2005 server as DateTime NULLable. I've extended the DateTimePicker class to be able to accept a Date? value. The problem is that the DS does not believe that the date columns that are nullable, really are. It always wants to throw an exception. Per someone elses suggestion i set the date fields in the DS designer to DataType "System.Data.SqlTypes.SqlDateTime" with DefaultValue "Null", and AllowDBNull "True", yet the NullValue Property *only* allows "(Throw Exception)" and I think to fix my issue I need it to be "Null" however if I do that, the compiler says "Null is not a valid AllXsd value", which is where I'm stuck and I'm afraid is a little beyond my understanding... are there any suggestions you could make to me? I'd really appreciate it!! Thanks in advance.
|
|
|
|
|
Hey there gang,
Hopefully, a simple question. I've written an Access mdb app with windows forms on the front end (for user input and so forth). It compiled just fine. Now when I run it, and I go to the 'reports' section (via Crystal Reports), it asks for login name and pwd for the mdb. I looked into the .xml file for the connection string and there's nothing there with respect to a UID or pwd. Any idea on how to remedy that? Thanks everybody I really appreciate your help.
trev_7777@hotmail.com
|
|
|
|
|
How are you connecting to the MDB ? Using ODBC, etc ?
Have you tried leaving the username and password blank ?
What version of MS-Access are you using ?
What version of Crystal Reports are you using ?
What version of Visual Studio are you using ?
This might help us in finding an answer to your problem.
|
|
|
|
|
Hi there.... I'm connecting via OLEDB 4.0; Username and PWD are blank (in fact, they're not listed in the xml file that was generated, merely a "Persist Security Info=True" line there; MS-Access ver 2003; Crystal Reports ver 10; VS 2008 Pro. Many thanks.
|
|
|
|
|
Oops, almost forgot. I've tried leaving the username and password blank when prompted.
|
|
|
|
|
Can you try this one?
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;User Id=admin;Password=;
I are troll 
|
|
|
|
|
Hey there.... tried and still get the "login failed" ... I can't find anything else in the connection
string that causes it to do that...I'll admit it's interesting
|
|
|
|
|
I'm gonna try rewriting the connection string and see what I get....
|
|
|
|
|
Have you considered reinstalling the drivers[^]?
Can you post an example of your connectionString?
I are troll
|
|
|
|
|
Hey there,
Sorry for the late reply and I very much appreciate the help. I decided to use an SQL db instead and haven't had any problems (though I admittedly haven't been able to figure out what the problem was with the Access db). Anywho, thanks everybody for the ideas!!!!
trev
|
|
|
|
|
Month before, One of my database size was 15GB. But today i have looked it , it grows to 35GB.
In which MDF(Data file) Size is 13603584 KB and LDF(Log File) sixe is 39793536 KB.
after analyzing it for a few hours i had seen growth of Log files is so fast.
i ran
sp_dboption '<dbname>', 'trunc. log on chkpt.', 'TRUE'
CHECKPOINT
use master
Go
EXEC sp_configure 'show advanced option', '1'
RECONFIGURE EXEC sp_configure
USe <dbname>
GO
DBCC SHRINKFILE (<dbname>_LOG, 1)
GO
But it does not help me.
kindly let me know how to truncate it or How to reduce log file size.??
thanks in advance.
|
|
|
|
|
SQL Server tries to protect you, by remembering what you did to it's databases. Or your databases, depending on your point of view
Read this[^] to understand why this happens. Read on here[^] for some remedies.
I are troll
|
|
|
|
|
Hi friends, I am using asp.net 2.0 application & ms server 2005. Using sp_addlogin,sp_adduser and sp_password I create a login and set username and password for particular database.While i connect my sql server i give login(not default login sa) name example itect and give password ****. I set permission to user for particular database. While i login i didnt access all the db.I can accessed only that what permission i gave for db.Its working fine. What my question is while i login at a particular login name and password i need to show in my ms server 2005 as a particular db only. suppose i give permission user 'Guvera' to db 'dbmap' means can i show only dbmap??? Unfortunately in my ms server i see all db but i cant access. Shall i show only my access db ?? Is it possible?? Please clear my doubts. If any mistake in my doubts, plear tell what is it??
Every Successful Person Have A Painful Story
|
|
|
|
|
We've had a view working fine in SQL 2000 which has one field (used for sorting) which is defined by calling a function that returns a text string. In SQL 2000 you can either run the query in Design View or select Open View and it works the same in both cases. In SQL 2005 if you run the query in Design view it works fine and the data is sorted correctly, but if you just do Open View to see the data then the data isn't sorted.
Any thoughts? TIA.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
