Database

6-Dec-02 8:15

Under the Server Explorer in VS.NET, under "SQL Servers" I would like to add the Northwind database. How do I do this? I have VS.NET and SQL Server v7 installed.

Thanks in Advance!
Derek

Not Active6-Dec-02 9:12

Not Active

6-Dec-02 9:12

You add it under data connections not servers. Right click and Add Connection... Follow the instruction.

Mark Conger6-Dec-02 9:19

6-Dec-02 9:19

Um, I believe Add Connection will connect you to a new server. I haven't played much with VS.NET explorer much though.

Do you have a full version of SQl Server or just client tools installed? normally northwind is installed by default unless you tell it not to. If you install MSDE I don't think you get it either. Best case, if you don't have it on your registered SQL server, youc an install it. There is a script called instnwnd.sql that lived in the mssql/install directory under your mase Sql server directory. running that puppy will create (or refresh) Northwind for you.

Mark Conger
Sonork:100.28396

Derek Price6-Dec-02 9:23

6-Dec-02 9:23

Thanks - now onto the new problem...

Under the Connection tab:
1. Chose my server
2. Use Windows NT Integrated security
3. Attached a database file
* Name = Northwind
* Filename = path to Northwind database

When I select the Test Connection button, I get the following message:

---------------------------
Microsoft Data Link Error
---------------------------
Test connection failed because of an error in initializing provider. The header for file 'C:\Northwind.mdb' is not a valid database file header. The FILE SIZE property is incorrect.
---------------------------
OK
---------------------------

Any ideas? Is this because Northwind is an Access database?

Not Active6-Dec-02 9:34

Not Active

6-Dec-02 9:34

Are you trying to use SQL Server version of Northwind or Access.

On the Data Link Properties dialog check what provider your using.

Derek Price6-Dec-02 9:52

6-Dec-02 9:52

I'm using the Northwind database that came with Microsoft Office XP. The actual filename is "Northwind.mdb". I'm assuming it's an Access database. Can SQL Server connect to Access databases? Is there a converted Northwind database for SQL Server? Forgive my ignorance on this, I've never worked with SQL Server before.

On the Provider tab, I was selecting "Microsoft OLEDB Provider for SQL Server" - maybe that's my problem. I was trying to follow an example in the book "A Programmer's Guide to ADO.NET in C#" and the author was demonstrating an example using the VS.NET Server Explorer and his screenshots showed the Northwind database already added. That's what prompted by postings.

BTW - Thanks for all your help!

Mark Conger6-Dec-02 10:17

6-Dec-02 10:17

If you run the install script I mentioned it will create a SQL server northwind DB. The Access Northwind won't mount under SQL Server (Wrong kind of header block)not to mention SQL Server 2K is older than Office XP.

Mark Conger
Sonork:100.28396

Derek Price6-Dec-02 10:25

6-Dec-02 10:25

Unfortunately, I couldn't find any Northwind upgrade SQL script. I'm in the process of downloading SQL Server 2000 right now (I only had SQL Server 7).

Another embarrassingly very simple question - once I have the upgrade Northwind SQL script, how do I launch SQL Server to execute it?

Not Active7-Dec-02 13:29

Not Active

7-Dec-02 13:29

In your original post you said you have SQL Server 7 installed. Why are you trying to use an Access version of Northwind then? On the provider tab you need to select "Microsoft Jet 4.0 OLE DB Provider".

"Microsoft OLEDB Provider for SQL Server" is for connecting to a SQL Server database which you appear not to be doing.

Do you understand the differences between providers?

Derek Price9-Dec-02 7:48

9-Dec-02 7:48

Yes - I do understand the differences between providers. I was a little confused about the book I was following since the author used the Northwind database with SQL Server. I (brashly) assumed that Microsoft had made the Access and SQL Server database compatible.

Thanks for the help!

Steve McLenithan9-Dec-02 6:48

Steve McLenithan

9-Dec-02 6:48

use the DTS import wizard to convert the access db version of Northwind to an sql server database.

You can still use the server explorer to add a connection to an Access database if you dont want to deal with sql server.

Hope that helps.

********************
* SteveMcLenithan
* steve@steve-mac.com
* http://steve-mac.com
********************

Derek Price9-Dec-02 7:59

Triggers and Mutating Tables

9-Dec-02 7:59

If I didn't already have SQL Server 2000, could I do this with MDSE 2000? Do you know if it provides any sort of conversion wizard?

SQL Explorer Control

Paul Watson6-Dec-02 4:48

Paul Watson

6-Dec-02 4:48

Anybody know of a control, preferably .NET, which can give me a treeview display like that found in SQL Enterprise Manager?

Am about to make one for a small project (hell the treeview control will be the biggest part of the small project) and would prefer to focus on the point of the project (SP generation) than that.

ta

Paul Watson
Bluegrass
Cape Town, South Africa

Christopher Duncan wrote:
Which explains why when Santa asked, "And what do you want for Christmas, little boy?" I said, "A life." (Accesories sold separately)

Chris Meech6-Dec-02 3:58

6-Dec-02 3:58

For a new trigger I'm developing, I getting a mutating table error message. The trigger is an AFTER INSERT trigger and in the trigger, I need to query the table that the trigger is on. The reason for this is because the new record contains a reference to an already existing record and I need data from this existing record to insert new records in another table. My documentation says that this is acceptable as long as the query is a SELECT INTO type of query. I'm going to try a cursor as a replacement, even though there is only one record I'm after, but if anyone has another idea for me to try, I'd love to hear it. Thanks.

Chris Meech

"what makes CP different is the people and sense of community, things people will only discover if they join up and join in." Christian Graus Nov 14, 2002.

"AAAAAAAAAHHHHHH!!!!! Those leaks are driving me crazy! How does one finds a memory leak in a garbage collected environment ??! Daniel Turini Nov. 2, 2002.

Mark Conger6-Dec-02 4:24

6-Dec-02 4:24

Out of curiosity, why are you doing an AFTER insert? I usually do this sort of thing with a Before (ON INSERT) kind of trigger so the row is built before the insert occured. hmm, your idea should work, but I'm a little confused as to as to why using the AFTER variant.

Mark Conger
Sonork:100.28396

Chris Meech6-Dec-02 4:42

6-Dec-02 4:42

Good question, Mark. It shouldn't matter whether the new rows in the secondary table are inserted before or after, so I will also try that as well. At the end of the day, all that I need is that when one row is inserted in one table, a corresponding row is created in another. So having it happen as a BEFORE or AFTER, from an application standpoint is of no consequence. But now that you've got me really thinking about it, perhaps the mutating error I'm getting is because it is an AFTER trigger. A change to a BEFORE trigger, may not cause the mutating error. Thanks.

Chris Meech

"what makes CP different is the people and sense of community, things people will only discover if they join up and join in." Christian Graus Nov 14, 2002.

"AAAAAAAAAHHHHHH!!!!! Those leaks are driving me crazy! How does one finds a memory leak in a garbage collected environment ??! Daniel Turini Nov. 2, 2002.

Chris Meech6-Dec-02 5:39

6-Dec-02 5:39

Talk about a rock and a hard place. Mad | :mad:

When I make the trigger a BEFORE trigger, then either the SELECT INTO or the use of a cursor works without causing a mutating table error. However the inserts that the trigger tries to do, fail because the RI rule for the table I'm inserting the new records into fails since this insert is 'before' the insert in the first table.

When I make the trigger an AFTER trigger, so that the RI rule failure is avoided, then either of the SELECT INTO or use of a cursor to select, causes the mutating table error condition.

What I'm trying to accomplish is the following. I'm inserting a new record into table A. This new row contains a reference to an already existing row in the table as well. I need the data from both of these records in order to construct a new row to insert into table B. However there is a referential integrity rule, whereby a new row in table B must contain a reference to an already existing row in table A. It is because of this RI rule that I think the trigger needs to be an AFTER trigger. Thanks for any suggestions.

Chris Meech

"what makes CP different is the people and sense of community, things people will only discover if they join up and join in." Christian Graus Nov 14, 2002.

"AAAAAAAAAHHHHHH!!!!! Those leaks are driving me crazy! How does one finds a memory leak in a garbage collected environment ??! Daniel Turini Nov. 2, 2002.

Chris Meech6-Dec-02 5:55

6-Dec-02 5:55

I'll have to investigate this solution some more, but I think it will do what I need. I have to create a PL/SQL package that contains both a BEFORE trigger and an AFTER trigger. The BEFORE trigger when fired will do the query to put the data into some package variables and then the AFTER trigger will use this data in order to do the inserts. I'll let you know how it works. Thanks again for the input, though.

Chris Meech

"what makes CP different is the people and sense of community, things people will only discover if they join up and join in." Christian Graus Nov 14, 2002.

"AAAAAAAAAHHHHHH!!!!! Those leaks are driving me crazy! How does one finds a memory leak in a garbage collected environment ??! Daniel Turini Nov. 2, 2002.

Mark Conger6-Dec-02 6:20

6-Dec-02 6:20

Double yuck! You hadn't mentioned an RI forein key being involved. Trigers and foreign keys usually mean headaches. My easiest solution to something liek this iss to do the insert via a PL/SQL package or have the trigger on the main table insert the appropriate data on the other table using a BEFORE insert trigger.

and yeah, I thought the mutating error might be coming from the AFTER insert trigger holding the row in partial commit. I saw it once and recoding logic to a BEFORe usually fixed it.

Yuck Smile | :)

Mark Conger
Sonork:100.28396

Chris Meech6-Dec-02 8:43