Help With The Design/creation Of A Database For My Application. [Experts Needed]
Apr 17, 2004
Hello, I was wondering if any of you experts might assist me in properly creating a database for my application. I've been pondering for a few days on how to accomplish this, but it seems that it doesnt have to be as complex as I am thinking...I just have to know how.
First off, I want to populate a dataset from a database in which I can databind to a datalist or repeater control. My datalist or repeater will have the following information in the ItemTemplate. Each ItemTemplate will consist of a table with 1 row with 3 columns cells. In each cell, the data will be laid out as accordingly.
[Unique Number] [Description ][Price ]
1000 XYZ $100.00
1001 ZXY $250.00
When the datalist or repeater is populated, it will need to be tried against a value that the user selects. For instance, the user selects 100,000 from a listbox and fetches the next page which will show the diagram above...he/she will see those results. However, if the user selects 110,000, the diagram above will have for the most part the same Unique Number and Descriptions, but the prices will vary. The reason I say for the most part with the Unique Number and Descriptions, is because I want to later be able to add admin access to add additional rows and appropriate prices to each Unique Number and Description.
Now the tricky part is, I could just add the listbox values to a table as a unique key and associated them with the Unique Number, Description and Price, however there are about 50 different options the user can select from and approx 50 different rows of Unique Numbers, Descriptions and Price . So you can see, I would have to set up the diagram each time for every possible selection from the listbox, which wouldnt really be efficient I presume.
I want to be able to populate the datalist or repeater so it could have say 50 Unique Numbers with Descriptions and Price at selection 100,000. And also that it might only populate with 45 Unique Numbers and Descrptions and Price at selection 110,000 because the 5 missing dont pertain to the selection of 110,000. I am trying to do it this way because at sometime, I want the admin to be able to add/delete a Unique Number, a Description for it and a corresponding price that correlates to the selection from the listbox.
Thanks for all your help guys. I really appreciate it. For the most part I understand what Im doing, I just need to be walked thru it a bit. Thanks again!
Hi, I have 2 design related questions. Q1: We are developing a huge .NET e-commerce web application with a number of modules - Shopping, 'For Sell' , 'For Rent', News, Jobs, Community, Matchmaking etc. These modules will store data into SQL server 2000 database server. 'For Sell' module will be used for all user ADs for selling items(addupdatesearch), similarly 'For Rent' module will be for Rentals ADs. The site will be open for 20+ different countries initially and will store unlimited ADs (eg. 200,000 For Sell ADs), Shopping Catalog (100,000 items). We have some tables shared by all modules: module, module_category, module_subcategory, country, users, user_group etc. Some tables are module specific: forsell, forsell_attributes, forsell_att_values, shopping, shopping_review etc. The big design question that our team is facing is whether to make one single huge database and create all associated tables for all modules in it VS create separate database for each modules and have a central database for common tables. Q2: Will it be better to create a single web application or different web application for each module? Please give us your expert inputsuggestions ips that will guide our team to the right direction. Thanks Jennifer
Hi Friends, I need to use UML for designing the database in my application. I am using sqlserver2005 database. can somebody help me in this regard? thanks in advance.
QUESTIONs:What schema is the best for high speed search for a classified web application? Is our schema design looks OK? It is a STAR schema and will be used for OLTP type app. Is this OK? or Are we missing something? Please let me know if you need more diagramatic description. BACKGROUND:Our group is making a classified website (like classified.yahoo.com) where people can place online ad to sell items. like cars, computers, electronics etc. Users will fill out webform for each category(car or computers) with all attributes of the item forsell to post an add.
Main 3 operations the web users will perform: Quick Searching(most frequent): category=car, subcategory=sedan, country=USA city=LosAngeles Zip=empty Advanced Search(less frequent): User can include all fieldsattributes or then can user a subset to query to do advanced search like : category=car, subcategory=sedan, country=USA, city=Los Angeles, Year= in(00, 03), transmission=Auto, engine=V6, Maker=Honda, Model=Accord, color=Red and Price < 10000 category=car, subcategory=SUV, country=USA, city=NY, Year= in(99, 00, 03), transmission=Auto, Maker=Toyota, and Price between 11000 and 14500
Insert(least frequent): By filling out web form. For Car, the form will have different dimensionsattributes (year, make, model, transmission, mileage, color, price etc)
Current Schema design: Set of core dimensionlookup tables: stores corecommon attributes for lookups eg. status={open, new, expired}, country={USA, Canada, India,...} One custom Lookup table: for all custom attribute lookup: transmission{auto, manual}, engine type{V4, V6} for car, processor{PII, PIII, PIV), RAM(512MB, 1GB, 2GB} for Computer & so
3 Fact Tables: Main factPivot table that stores all sell common attributes eg. price, title, year_made, post_date, expire_date, user_name, description etc. One fact table to store custom string, int, float, date field values of ads. One fact table to store custom dropdown field selection values
Concerns and issues: Looking at the schema, it seems to be a STAR schema with multiple fact tables where all core lookup tables connected to the main Pivot table and custom lookup table connected to the 2nd and 3rd fact table.
Quick search only queries the Pivot fact table. While Advanced search query requires to join 3 fact tables. Both query requires to join 3 fact tables with all dimension tables(15 to 20 each having avg of 20 values) to get the look up names so that users sees text instead of ids. Search speed is the Main concerns. Insertionupdate speed doesn't matter that much as that is less frequently done.
We have a Silverlight based application which currently supports only one production version. Idea is to support three concurrent versions of the same application and user will switch to the newer versions based on their interest or they can still continue with the older version.
We still have to use the existing database for all these three versions.
What is the best way to architect this so that we can differentiate the code between the versions and still keep the data in sync and run all the versions in parallel.
Hi, I have a Users table that I use for membership. Now I am using username varchar(30) as the primary key for this table since username will always be unique.I am storing username in UsersInRole table so that I can find what roles each user has.The question I have is regarding how SQL Server actually stores data:I see that when I add users, they are always stored alphabetically sorted on username. I was expecting that all users will appear on the users table in the order they were added.Example: I have 3 users (john, jonah, wilson). Now I added 4 user with username='bob'If I execute select * from users, it returns me (bob, john, jonah, wilson). Look bob is has become the first row of the table.My question: Is Sql server moving 3 older rows to make room for 'bob' and it is also rebuilding part of the index due this new username 'bob'?If this is the case, then it will have big impact if I have 100K users and I add one user that becomes firstrow. In that case huge other rows will have to move.Bottom line, insert, delete will be expensive. I know sql server keeps data physically sorted on PK. But I am concerned here since rows are losing the order in which they were inserted.Thanks
I am trying to insert a value into a field in a database named ASPNETDB.MDF. The table name is "profiles_BasicProperties" and the field name is "UserID". I get an error when I attempt to do this. See the code I am using to try to do this below...and then the error that I get which is further down in this post. Note...both the code and the database are on my laptop. I can connect to the database just fine using Server Explorer in MS VS 2005. Thanks in advance for any help anybody can offer... Here is the code I am using: <%@ Page Language="VB" MasterPageFile="~/Master02.master" Title="Create Your Free Account" Debug="true"%><%@ Import Namespace="System.Data.SqlClient" %><%@ Import Namespace="System.Web.Configuration" %> <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder_Main" Runat="Server"> <script runat="server"> Sub CreateUserWizard_CreatedUser(ByVal sender As Object, ByVal e As EventArgs) Dim CWZ As CreateUserWizard CWZ = CType(Me.LoginView1.FindControl("CreateUserWizard"), Wizard) CreateUserProfile(CWZ.UserName) Private Sub CreateUserProfile(ByVal UserName As String) Dim conString As String = WebConfigurationManager.ConnectionStrings("Main").ConnectionString Dim con As New SqlConnection(conString) Dim cmd As New SqlCommand("INSERT profiles_BasicProperties (UserName) VALUES (@UserID)", con) cmd.Parameters.AddWithValue("@UserID", UserName) Using con con.Open() cmd.ExecuteNonQuery() End Using End Sub </script> ...and here is the error and stack trace (the offending Line 49 is in bold): Server Error in '/Site_Dev' Application.-------------------------------------------------------------------------------- An attempt to attach an auto-named database for file ~App_DataASPNETDB.MDF failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: An attempt to attach an auto-named database for file ~App_DataASPNETDB.MDF failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share. Source Error: Line 47: cmd.Parameters.AddWithValue("@UserID", UserName)Line 48: Using conLine 49: con.Open()Line 50: cmd.ExecuteNonQuery()Line 51: End Using Source File: C:UsersmdcraggDocumentsWebsiteSite_DevUser_Create.aspx Line: 49 Stack Trace: [SqlException (0x80131904): An attempt to attach an auto-named database for file ~App_DataASPNETDB.MDF failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +736211 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1959 System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33 System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +237 System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +374 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +192 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 ASP.user_create_aspx.CreateUserProfile(String UserName) in C:UsersMatthewDocumentsGroup 02 - PoliticoreSite_DevUser_Create.aspx:49 ASP.user_create_aspx.CreateUserWizard_CreatedUser(Object sender, EventArgs e) in C:UsersMatthewDocumentsGroup 02 - PoliticoreSite_DevUser_Create.aspx:30 System.Web.UI.WebControls.CreateUserWizard.OnCreatedUser(EventArgs e) +105 System.Web.UI.WebControls.CreateUserWizard.AttemptCreateUser() +341 System.Web.UI.WebControls.CreateUserWizard.OnNextButtonClick(WizardNavigationEventArgs e) +105 System.Web.UI.WebControls.Wizard.OnBubbleEvent(Object source, EventArgs e) +453 System.Web.UI.WebControls.CreateUserWizard.OnBubbleEvent(Object source, EventArgs e) +149 System.Web.UI.WebControls.WizardChildTable.OnBubbleEvent(Object source, EventArgs args) +17 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
--------------------------------------------------------------------------------Version Information: Microsoft .NET Framework Version:2.0.50727.312; ASP.NET Version:2.0.50727.312
Hi, hoping someone can give me a heads up on best sysyem to employ to manage this scenario.Sorry if this is not the correct forum to post in.Our primary software is a standalone VB6 app used by some 50 clients, each with thier own MSDE backend. Their business basically is Packing and distribution of fresh produce. Each of our clients have multiple suppliers on which they have data stored. I have decided to write a web app (VS2005) that will allow the supplier to enter critical information about produce quality, testing etc. This data will be kept on our local webserver database.My use case scenario is that when a supplier enters a new record via web page I can somehow update the Packers SQL database via internet, http,tcp.Ideally I imagine a trigger on our server db can connect to their databaseover WAN. HOW??The packer must also be able to add suppliers to our server db. As they already have that data stored stored at their end I was hoping to have a web page with drop down lists that accesses their local db and, upon Save, updates our webserver ddb. Thereby saving them the need to manually enter the data twice. I know this is a little long winded but I'm new to .Net and web programming so have no idea on the simplest and best method to do this.Any help greatly appreciated.
Hi there everyone, I am starting to develop a new internal website for my company, for logging calls, reports and billings. I am still busy looking at the best way to design the layout of the DB. My biggest concern is a billings table design. What it should do is, that when work was completed, the consultant must add his billings to the logged call. My problem is that i will never know how many individual items will be billed for. I was thinking of just creating a table with [id, call_id, item_1, cost_1, quan_1, item_2, cost_2, quan_2, item_3, cost_3, quan_3...........] but i think (hope) that there must be a better way of doing this. RegardsJacques Thomas
I am a new comer to the internet e-commerce and ebusiness service. I have limited budget for my startup. I would like to know whether the SQL Server 2005 standard x32 edition (5 client), as this verion is still relative affordable, could be good enough to support an ebusiness intertnet site. When an internet broswer access to my site, would that access be counted as one client?
Also, I would like to know whether anyone there have experience of using SQL express edition to support an internet site, that potentially need roburst data transaction.
I'm designing an application that will be deployed to 500 users. It is very database intensive (selects, inserts, updates, deletes). One select statement could possibly have up to 20,000 records returned. My question is, where should I put the database? Is this a bad candidate to have the database on one remote server? Or should I install sql server express on each users desktop? Any design issues I should consider?
Hi to everyone,My problem is, that I'm not so quite sure, which way should I go.The user is inputing by second part application a long string (let'ssay 128 characters), which are separated by semiclon.Example:A20;BU;AC40;MA50;E;E;IC;GREENNow: each from this position, is already defined in any other table, asa separate record. These are the keys lets say. It means, a have someproperities for A20, BU, aso.Because this long inputed string, is a property of device (whih alsohas a lot of different properities) I could do two different ways ofstoring data:1. By writing, in SP, just encapsulate each of the position separatedby semicolon, and write into a different table with index of device,and the position in long stirng nearly in this way:Major device data tableID AnyData1 AnyData2 ... AnyData3123 MZD12 XX77 .... any comment text124 MZD13 XY55 ... any other commentString data Tablefk_deviceId position value123 1 A20123 2 BU123 3 AC40.....123 8 GREENThe device table, contains also a pointer (position), which mightchange, to "hglight" specified position.Then, I can very easly find all necessary data. The problem is, I needto move the device record data (from other table) very often into otherhistory table (by each update). That will mean, that I also need tomove all these records from 1 -8 for example to a separate historytable, holding the index for a history device dataset. This is a littleinconvinience in this, and in my opinion, it will use to much storagedata, and by programming, I need always to shift this properities intohistory table, whith indexes to a history table of other properities.2. Table will be build nearly in this way:Major device data tableID AnyData1 AnyData2 ... AnyData3 stringProperty pointer123 MZD12 XX77 .... any comment text A20;BU;AC40;MA50;E;E;IC;GREEN 3124 MZD13 XY55 ... any other comment A20;BU;AC40;MA50;E;E;IC;GREEN 2By writng into device table, there will be just a additional field forthis string, and I will have a function, which according to specifiedpointer, will get me the string part on the fly, while I need it.This will not require the other table, and will reduce the amout ofdata, not a lot ... but always.This solution, has a inconvinance, that it will be not so fast doing asearch over the part of this strings, while there will be no real indexon this.If I woould like to search all devices, by which the curent pointervalue is equal GREEN, then I need to use function for getting thevalue, and this one will be not indexed, means, by a lot amount ofdata, might be slow.I would like to know Your opinion about booth solutions.Also, if you might point me the other problems with any of thissolution, I might not have noticed.With Best RegardsMatik
EDIT: Please skip over this and go to the 2nd post. After writing this, I realized it may be too complicated to comprehend, so I tried to simplify with another example which is found in the 2nd post. Or if you want you can read this one and try to understand it.
I would like to go over a design we came up with to handle a certain reporting requirement from an OLAP cube to see if this is a good approach. Also, I would like to hear about other designs that we can consider.
Here is the condensed version of the requirement.
We are measuring the performance of a bunch of portfolios. So among other dimensions, we have a portfolio dimension that holds the list of portfolios we are tracking. In a typical portfolio analysis, an analyst almost always looks at a portfolio's measures along side a benchmark portfolio's measures. A benchmark is another portfolio against which a selected portfolio is compared. So, for a given portfolio, there is a determined benchmark portfolio.
For example, an analyst might want to look at a measure called Annual_Yield for a portfolio called Portfolio_A. There will be an attribute on the member Portfolio_A that tells us what its benchark portfolio is. That attribute will basically be another member from the portfolio dimension. So, as a function of what an analyst chooses as the first portfolio (the subject portfolio), the cube should be smart enought to know what to select as the second portfolio (the benchmark portfolio).
To implement this, I am proposing the following structure for the table that will create the portfolio dimension.
So, the above data says that Portfolios A, B, and D uses Portfolio C as their benchmarks. And Portfolio E uses Portfolio F as its benchmark. Lastly, if a portfolio is used as a benchmark for other portfolios, then its BenchmarkID is null, meaning it doesn't get compared to anything, thus Portfolios C and F have null BenchmarkIDs. With this data, I would create a hierarchy in the portfolio dimension that has two levels. At the top level, there will be a portfolio and if that portfolio has a benchmark, then it will drill down to it.
With a portfolio dimension designed as such, I envision a fact table that looks like the following.
I am helping a friend with a gamming website. There are pages which displays data from other clans.
Members need to register and login to view full clan details. User who have not logged in can only view partial data about a clan.
I need to keep track of the kind of hits each page received. That is I want to tell say "Clan X" that these particular members viewed your page these many times and on these dates and these members who have not registered have viewed your page these many times and on these dates.
I am using ASP with MS SQL.
I would like some help on designing the table layout that is efficient for keeping track of the visitors for each page.
I am a developer and I have a problem trying to design a system to manage data coming from web surveys. On average each subject will take part in more than one survey and each survey may potentially have 100+ questions.
I am focusing here only on the table(s) that will hold the survey data.
I'm thinking about having a wide table storing each question in the survey(s) as a column against the subjects. My main concerns with this approach:
- the "yuckiness" and potentially other performance issues(?) associated with a table with 100s of columns - the 8kb size limit per row (unlikely to touch it, but possible) - 1024 column limit (unlikely to touch it, but possible)
Another approach obviously is to have a single table where we have, in a simplified version, 3 columns: person_id, question_id and data/ answer. The problem with this is that the "data" column will have to accommodate all types of data, from bits to varchars, and that field validation seems now impossible jeopardizing data integrity, and most importantly, you can't easily work with the data in filtering/ reporting etc.
What do knowledgeable designers come up with in this case? Can someone point me in the right direction?
I am new to Reporting Services, and I would like to serve up SQL 2005 Reporting Services reports via an ASP.NET web app (possibly using Report Viewer controls in Server mode). The web app will be running on an IIS server outside the firewall. I would prefer to have the Reporting Services server be inaccessible outside the firewall, but it seems that the client browsers need direct URL access to the Reporting Services server. I guess this means either locating the Reporting Services server outside the firewall, or opening a hole in the firewall for access to the Reporting Services server. Is there a Microsoft best-practice methodology/white-paper that addresses this scenario (especially as regards security concerns)? Alternately, would it be possible, and would the same report presentation functionality be available (reports with expanding/collapsing sections, choice of export formats, etc), by calling the Reporting Services web service directly from the ASP.NET app, and manually populating the Response object? Thanks. Any help would be appreciated.
We are planning to move all of our System Center Databases that reside on front end servers to each system center application to a centrally located SQL 2012 server. We'd like to centralize everything and have our DBA care for the server. here is our setup:
SCOM has 1 monitoring and 1 Data warehouse server. SCCM has 1 server with all roles on it. DPM database is on the same server as Application. Same with SCVMM. I have 2 questions regarding this move:
1. Can I have all these databases running on 1 SQL instance?
2. Is there a best practice document that highlights steps and "gotchyas"
I don't know what category would be appropriate for this question but security seems to be close enough.
I have this case scenario: I am running an automated application that extracts data from a web site and stores the data into a table on SQL server 2005. This information is not confidential in the extreme of social insurance #'s, bank account #s, but should not be seen by a typical employee (it has no use for them). After the data has been stored, it retrieves the data from the same table, processes it, and updates the same table. This application runs every hour infinitely.
Should all the insert, update, and select queries be stored under a stored procedure? I am not concern with performance. My concern would fall under design and security.
Is it worth to hide the details of inserting/updating/selecting behind a stored procedure? Or should I just allow the program to send select/update/insert SQL queries?
No employee (other then the developer and the DB admin) or customer ever access this table (They do not have permission from SQL). The username and passwords were created with security in mind.
Ok, I'm doing a football database for fixtures and stuff. The problem I am having is that in a fixture, there is both a home, and an away team. The tables as a result are something like this:
It's not exactly like that, but you get the point. The question is, can I do a fixture query which results in one record per fixture, showing both teams details. The first in a hometeam field and the second in an away team field.
Fixture contains the details about the fixture like date and fixture id and has it been played
Team contains team info like team id, name, associated graphic
TeamFixture is the table which links the fixture to it's home and away team.
TeamFixture exists to prevent a many to many type relationship.
Make sense? Sorry if this turns out to be really easy, just can't get my head around it at the mo!
Hi, I am not sure is this a proper place for my question. All we know that creating a database can be done as the following. 1) Create a project named DBExample 2) Click Add->New Item. 3) Select SQL Databse and type a name.(Ex: Database1.mdf) A database is added to the project 4) Then add table and input values. 5) Set the primary key.... My question is I parsed a text file to form a 2d string array x. How can I convert x into the database?
Hello... I want to develop a web site having two features 1. Online Shopping2. Forums Im using SQL Server, ASP.NET and C#. Now the problem is that how do I configure the Databases. Whether I create new database for each or I marge the both things into one database. if i create saperate databases for each of the feature then users have to register for two times, first for forums and second for shopping. I dont want to do this...! I want users to register just for once. ____________Thanks in advNauman Ahmed
I downloaded the sample database AdventureWorks from microsoft . When I run the download it created the data and log files under data folder in MSQLalong with other data and log files of master databases etc. However I do not see this database in SQLserver management studio. Can some one help me how to fix this so that I will be able to see this database in management studio.
I am downloading this for SQL server reporting purpose
if i open the management studio express and trying to create a database by right clicking the server name and trying in the new query window,im getting error as
CREATE DATABASE permission denied in database 'master'.
I tried finding any info regarding this approach and I can't find any, so here I am (again!).
I'm trying to create a database using SMO on a freshly installed SQL Server Express (not going through the advanced install, not going through the surface area configuration, so a lot of things are "wrong").
I get an error
- use SMO to try and create a database; this is the pseudo-code combo used:
1. create new Smo.Database object;
2. create new primary file group and data file for that database (using Smo.FileGroup and Smo.DataFile); add them to the database.
3. invoke Create(). catch exception with the following details:
Exception details:
Microsoft.SqlServer.Management.Smo.FailedOperationException was caught HelpLink="http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Database&LinkId=20476" Message="Create failed for Database 'NewDatabase'. " Source="Microsoft.SqlServer.Smo" Operation="Create" StackTrace: at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl() at Microsoft.SqlServer.Management.Smo.Database.Create() at Penta.Core.Library.Sql.Classes.Penta_SqlDatabase.Create() in C:Documents and Settings[...]
InnerException:
{"Directory lookup for the file "C:\Documents and Settings\[...]\NewDatabase.mdf" failed with the operating system error 5(Access is denied.). CREATE DATABASE failed. Some file names listed could not be created. Check related errors."}
Seems like user rights error.
Long story short, the only way the creation step works is if I change the Log on for the SQL Express service from the default Network Service to Local System (either through code or not).
Now, I know LocalSystem is not a wise idea, but it seems to be the only way.
I know it's probably something stupid, but what am I missing?
Hi All, I have a web site where the user can select from a list of databases (they're actually all the same structure, but have data unique to each client). Each database is a SQL 2005 database held in the App_Data folder. This all works a treat. Each database is simply named after the client it is holding data for, and the connection string for each is stored in a database that is accessed However, what I need is a form where a database name can be entered, and I can then create a new database with the same structure as the others - including tables, indexes and stored-procedures. I know I can use the copy database wizard in the Management Studio, but that isn't an option to the users of this system (it's to be used on an intranet, and the page I'm trying to create will only be used by project managers - but these people won't have a clue when it comes to configuring databases themselves). I have scripted a database, but have no idea on how I can actually use this script from within Visual Studio and more importantly, don't know how to configue it to have the database name as a parameter. I've also seen some examples that suggest using SQL-DMO, but haven't been able to work out how to use it and according to Microsoft it shouldn't be used for new developments (http://msdn2.microsoft.com/en-us/library/ms132924.aspx). Anyway, I really hope someone is able to offer some assistance on this one as I've spent a whole day trawling forums and various web-sites trying to find a solution to this. Thanks & regards, Paul
I would like to know the sql command to generate all the scripts of a database so that I can replicate the same database for testing purpose. For instance, I need those scripts to help me re-create the database itself and indexes, triggers, and stored procedures of it if possible. I remember there was a command to display all these scripts in Sql but I forgot it. If someone knows it, please help. Thanks in advance. blumonde
I've never scripted a creation of a database and it's schema,I have started to put the script together and wondered if the following two scenarios were possible and if so could the following be possible and if so could you show me.
Scenario 1: Before the script runs it checks to see if a database of the same name exists. In this example the database name is "database1". If it does the script will stop and display a message along the lines of "You are trying to create a database, which name already exists. Please either drop or rename the current database and rerun this script."
Scenario 2:The more difficult one I think. Is that it checks to see if "database1" exists. If it does it displays a message along the lines of "The database you are trying to create already exists. Please confirm that you either want to Drop the Database" You then press a key on the keyboard to continue the script and the database is dropped. It will then tell you to rerun the script in order to create the "database1" database. Or you could even give them the option to either drop the database or rename it with "_todatdate" at the end of it, but if you can get the script to do different things with different keyboard strikes?
DATABASE1 CREATION SCRIPT Author: Ryan Keast
The following script creates a DATABASE1 database. */ /*DATABASE1 database creation */
the following msg appears whn i try to create a new database in Microsoft Sql Server Management Studio Express. I m on Vista Basic and using a administrator account...plz help.... i have my project whose backend is SQL 2005.
------------------------------------------------
TITLE: Microsoft SQL Server Management Studio Express ------------------------------
Create failed for Database 'timepass'. (Microsoft.SqlServer.Express.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Database&LinkId=20476
Hi all :)My apologies if I posted in the wrong groups, but I just jumpedin MS SQL waters, so any guidance will be appreciated.What I'm trying to do is the following process:[1] present operator with a web page (script)[2] once filled with db name and username, script will create..sql file[3] osql.exe will be called with, i presume, -i file.sql andcreate a databaseI have limited SQL knowledge, but I got the information fromEnterprise Manager when I ran 'All Tasks -Generate SQLScript' on how the .sql file should look like.I realized what are the commands that would create a fresh DB(I ran this for newly created DB), and figured my .php scriptshould create such a file.It's fairly basic, and I'm almost sure all of you know howoutputed .sql file looks like, but anyway here it is:Script is called with parameters 'six' as database name and'magarac' as user name:---------------------------------------------------------------IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name= N'six')DROP DATABASE [six]GOCREATE DATABASE [six] ON (NAME = N'six_Data', FILENAME = N'E:Databasepathsix_Data.MDF' , SIZE = 1, MAXSIZE = 20,FILEGROWTH = 10%) LOG ON (NAME = N'six_Log', FILENAME = N'E:Databasepathsix_Log.LDF' , SIZE = 1, MAXSIZE = 20, FILEGROWTH= 10%)COLLATE SQL_Latin1_General_CP1_CI_ASGOexec sp_dboption N'six', N'autoclose', N'false'GO....use [six]GOif not exists (select * from dbo.sysusers where name =N'guest' and hasdbaccess = 1)EXEC sp_grantdbaccess N'guest'GOif not exists (select * from dbo.sysusers where name =N'sinisam')EXEC sp_grantdbaccess N'magarac', N'magarac'GOexec sp_addrolemember N'db_owner', N'magarac'GO---------------------------------------------------------------I managed to get an exact replica of .sql file that EnterpriseManager created. It leads me to believe that this way ofautomated database creation is indeed possible.Really sorry for making you to go through all this text, butafter I get a green light on this from you guys, I'll have abit more problematic question.Is there any reason why this should not be used, or would fail?Thanks in advance :)P.S.Just as a heads-up, next part of my problem is automatedcreation of new MS SQL server login to use with new DB.
