MS SQL Server

Hola,

I'm totally a beginner. I already know how to administer SQL database from my computer. How does it work to administer SQL database to your hosting company?

Air :-(This would depend on what your hosting company allows you to do...|||Normally it works in the same way, only that you do not have access to anything but your own database and that your rights are limited to creating objects in your database ...
So you will not have access to logins and stuff like that ...
Your ISP will give you all the needed login info, with that you can connect to your database|||Depends very much on what "hosting" means. The answers above give you some options, but if you are doing something like renting a cabinet in you hosting company's environment, then the server is yours to do with as you please and you should even be able to use terminal services to connect to it. Another option, which I like to use in our case is the Microsoft Web Data Administrator which gives you web access to certain SQL server admin functions.|||Here is how my company does it. We will set you up just like "Foefie" said. When ever I set up a client that orders our package that has SQL. I just give them the domain "sql.washoetech.net" and a username and password then they can log into the sql server with the sql client network utility and then they can use the manager to access the DB.

J

MS SQL Basics: Insert into multiple tables at once.

I'm trying to do something very basic here but I'm totally new to MS SQL Server Manager and MS SQL in general.

I'm using the Database Designer in MS SQL Server Manager. I created two tables with the following properties:

Schedule (ScheduleID as UniqueIdentifier PK, Time as dateTime)

Course (CourseID as UniqueIdentifier PK, Name as VarChar(50))

I create a relationship by dragging the PK from the first table over to the second and I link on ScheduleID to CourseID columns (I'm not certain what type of relationship is created here N:N?). It appears to work, I can do a Select * and join the two tables to get a joined query.

The problem starts when I try to populate the tables: a course will have a schedule. I can't seem to get the rows to populate across both tables. I try to select the pk from the first table and insert it into the second but it complians about it not being a uniqueidentifier. I know this is very basic but I can't seem to find this very basic tutorial anywhere.

I come from the Oracle world of doing DB's so if you have some examples that relate across that would be great or better yet if you can point me to a good reference for doing M$ DB stuff that would be great.

Thanks.

Is Schedule supposed to represent timeslots for when Courses can take place (so that a Course has a Schedule)? Or is Schedule a collection of Courses (so that a Schedule has a Course)?

Either way, I would think that you need to insert a Foreign Key into the child table that contains the Primary Key value from the parent table.

For instance, let's assume that you intend for Course for have a Schedule:

Schedule
================================
ScheduleID uniqueidentifier (PK)
Time datetime

Course
================================
CourseID uniqueidentifier (PK)
ScheduleFK uniqueidentifier
Name varchar(50)

In this case, multiple courses can be assigned to the same schedule. You would use the "Relationships" functionality of the design mode for either table to create the foreign key relationship. From queries, you would use an INNER JOIN:

select * from course inner join schedule on course.schedulefk=schedule.scheduleid

(btw--this concept is shared across all relational databases, not just SQL Server)

|||

Sorry I should have been a bit more clear. Course holds golf course information and the schedule holds the tee times for a golf course. So one golf course has one schedule.

I understand the PK FK relationship. I assumed, incorrectly I guess in this case, that the FK is created automatically when I create the relationship. I also understand the inner join.

I'll give this a try.

|||If you have a one-to-one relationship between the two tables it may be advisable to normalise them into a singular table therefore reducing the complexity|||

Ya I was out to lunch on the one to one relationship, it is one to many; sorry I was rushing out the door this morning when I wrote this.

Couple more questions:

Weird thing happens sometimes when I make a column the PK. I get an error message when I try to save it, the system says that the column cannot be null. I have not put any data into the table yet so I don't understand where it is getting this null thing from. If I del and create the row again there is no problme, is this is bug?

Secondly, do I need to check of 'identity' in the PK column? Do the values automatically go from the PK column to the FK column or do I have to mannualy tell it to do that as the rows are being inserted?

Thanks.

|||

*I meant to say table not row for the null error part.

The error message is this:

'Course' table
- Unable to modify table.
Cannot insert the value NULL into column 'CourseID', table 'GolfPlanner.dbo.Tmp_Course'; column does not allow nulls. INSERT fails.
The statement has been terminated.

|||

I've been playing with this a bit today. What I don't understand is how to generate the PK's. Do I have to call a counter to do it?

When I try to add stuff now it tells me that courseID cannot be null, but I can't make that column into an identity because the datatype is uniqueIdentifier...??

|||

NewID() as in:

INSERT INTO MyTable(Col1,Col2,Col3) VALUES (NewID(),@.Col2,Col3)

|||Great thanks, that solved one problem. Now how to I get taht PK to become the FK in the second table. I tried to do a select inside the insert but it's telling me I'm not allowed to do that?|||

I got it to work, probably not the proper solution but here it is for anyone having the same issue:

-- =============================================

ALTER

PROCEDURE [dbo].[PopulateDatabase]-- Add the parameters for the stored procedure here

AS

declare @.CourseIDuniqueIdentifier

BEGIN

SETNOCOUNTON;INSERTINTO

Course

(CourseID,Name, Address, PhoneNumber)VALUES (NewID(),'Prospect Lake','123 Prospect St', 2508129832)

SET @.CourseID=(SELECT CourseIDFROM CourseWHEREName='Prospect Lake')

INSERTINTO

Schedule

(ScheduleID, Course_FK, Date, TeeTime, NumberOfPlayers)VALUES (NewID(), @.CourseID,getDate(),'06:00','2')

MS SQL + ADO + VB6, Which is the best method ?

First, sorry for my english

In vb.6 I use 2 DataCombo and 1 DataGrid in 1 Form, totally I need 3 recordset on 1 form.

All 3 recordset is from MS SQL Server in one database.

Which is the best method ?

1. To place 3 ADODC control on the form and for each

ADODC1.ConnectionString="......................"
ADODC1.RecordSource="SELECT * FROM Table1"

ADODC2.ConnectionString = the same as ADODC 1
ADODC2.RecordSource="SELECT * FROM Table2"

ADODC3.ConnectionString = the same as ADODC 1
ADODC3.RecordSource="SELECT * FROM Table3"

OR

2. I don't use ADODC control, I use a single connection and 3 Recordset

Set cn = New ADODB.Connection

With cn
.ConnectionString = ".........."
.Open
End With

Dim Rec1 As New ADODB.Recordset
With Rec1
.Open "SELECT * FROM Table1, cn, adOpenStatic, adLockReadOnly
End With

Dim Rec2 As New ADODB.Recordset
...........................
Dim Rec3 As New ADODB.Recordset
..............................The single connection object with multiple recordset objects is the better choice. Also, make sure you use the SQLOLEDBXX provider in your connection string.|||Another comment - declare your objects at the top of your code so

Dim cn as adodb.connection
dim rs1 as adodb.recordset
dim rs2 as adodb.recordset ...

Then instantiate the recordset object as late as possible in your code (stay away from dim as new) and destroy the object as soons as possible:

set rs1 = new adodb.recordset
'start recordset manipulation
'end recordset manipulation
set rs1 = nothing