How do I create an SQL database for use with Portfolio (using MS SQL 7).
To create a new SQL database:
- Open the SQL Server Enterprise Manager (Start->Programs->Microsoft SQL Server 7.0->Enterprise Manager). This is the admin utility for MS SQL and is similar in design to Windows Explorer or the Registry Editor.
- In the Left hand pane of the Enterprise Manager you'll see a folder called Console Root, which is the top level of the SQL Server folder tree. Expand this hierarchy until you see the following sub-folders: Databases, Data Transformation Services, Management, Security, and Support Services. For our purposes, the only subfolders we're concerned about are Databases and Security. You might want to expand these two folders as well.
- To create a new SQL database, right click on the Databases folder and select New Database. This will open up a Database Properties dialog. Give the database a name and click OK. You don't need to adjust any of the other options. Your database will now appear under the Databases folder.
- Now you need to assign a user as the Data Base Owner (dbo) for the new database. To do this highlight "Logins" under the Security folder (you'll then see all of the Logins for the server listed in the right hand pane). At this point you can either make an existing user the dbo or create a new user. In either case, this user must be using SQL Server authentication, not NT authentication (you can right-click on an existing user, select Properties and look in the General tab to verify this).
- To make a new Login, right-click on Logins and select New Login. On the General tab of the resulting dialog, enter a name for the new login, then select the radio button for "SQL Server Authentication" and enter a password for the new login. Then under the Defaults section of the General tab, choose a default language and database (probably the new one you've created). Then go to the Database Access tab, put a check next to the databases you want this user to have access to (these will appear in the top window of the dialog) and then, with that database highlighted, put a check next to Public and db_owner in the Database Roles window at the bottom of the screen. Click OK to finish.
- To use an existing Login, right click on it and select Properties. Then go to the Database Access tab, put a check next to the databases you want this user to have access to (these will appear in the top window of the dialog) and then, with that database highlighted, put a check next to Public and db_owner in the Database Roles window at the bottom of the screen. Click OK to finish.
- At this point the database should be ready to go as far as the SQL side of things in concerned. To start using the database, open Portfolio and go to File->Administer servers, highlight the appropriate server and click Administer. Then, from the Server Admin screen, click the Serve SQL button. This will open the Serve SQL Database dialog. If the window is blank, then your SQL server needs to be added (see first bullet point below). If your SQL server has already been added to Portfolio, then you just need to serve the SQL database (jump down to the second bullet). NOTE: All of this information is also covered on page 271 of the v5 Portfolio manual.
- To add your SQL server to the list, click the Add Server button. In the Add Server dialog, select SQL Server from the ODBC Driver drop-down menu, then enter a name for your server (it doesn't make a difference what you call it) and enter the IP address for you SQL Server. You'll then need to enter a username and password. This can be the username and password for the dbo or you can use the standard "sa" account. Once you've done this click OK. Your server should now appear in the list
- To serve an SQL Catalogue, highlight the name of the database in the list that appears under your sever name and hit the Serve SQL button. You will then see a prompt that says "Open at Server Startup?", go ahead and say "Yes" (if for some reason you receive an error at this point, try this again, but this time answer No). You'll then be prompted for a username and password - enter the username and password for the dbo. At this point Portfolio should open up the new, blank Catalogue. It can then be accessed as normal by going to File->Connect to Server.
Question: How do I create an SQL database for use with Portfolio (using MS SQL 7). [FAQ00143.htm]
Last Update:- 05 February 2007
