I need help getting the database items copied over to my Sql Server 2000 database. Using Sql Express 2005 isn't an option for me :/

I'm getting this error:

Server Error in '/BugBase-Modified' Application.
--------------------------------------------------------------------------------

Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
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: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.



Here is my connection string:

  <connectionStrings>
    <add name="BugBase" connectionString="Data Source=xxx;AttachDbFilename=App_Data\BugBase.mdf;I ntegrated Security=True;User Instance=True;User ID=xxx;Password=xxx" providerName="System.Data.SqlClient" />
  </connectionStrings>


How can I make this AttachDbFilename work? (this is the problem I suspect, my connection string works with another app perfectly without the AttachDbFilename directive)

Thanks for the help in advance

Hi there,

The AttachDbFilename feature is new to SQL Server 2005, so it won't work with SQL Server 2000. Check out this article for more info about using SQL Server 2000:

http://Imar.Spaanjaars.Com/QuickDocId.aspx?quickdoc=395

And check out this site for a proper connection string for SQL Server 2000: www.connectionstrings.com

You should realize that the databases that come with the book are meant for SQL Server 2005. You can't just attach them to SQL Server 2000....

HtH,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
While typing this post, I was listening to: 03 - Leg Of Lamb by Queens of the Stone Age (Track 3 from the album: Rated R) What's This?

Is there anyway you can provide the scripts to create the tables, stored procs and triggers in a zip file?

s there anyway you can provide the scripts to create the tables, stored procs and triggers in a zip file?

--------------------------
Yes, i too need it. This would be great to have it.

Thanks in advance

Hi there,

I can provide the scripts for the database for a SQL Server 2005 installation. Not right now, because I don't have the source here, but I can create the scripts over the weekend and post them here.

Just realize, like I said earlier, you can't just directly use the 2005 scripts with a 2000 database. There are some differences that don't transfer directly, like the new nvarchar(MAX) datatype. So, if you want to run the app against 2000, you need to change the scripts and parts of the application as well.

I'll be happy to provide hints and tips for the transition process, but unfortunately, I don't have the time to make the changes myself.

Out of curiosity: why is SQL Server 2005 not an option?

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

That would be greatly appreciated. And I can post the code with changes when I'm done implementing it to work with SQL Server 2000.

Hi again,

I created the SQL Scripts for the BugBase database. Please note that the scripts are for SQL Server 2005!

http://Imar.Spaanjaars.Com/Downloads...erver_2005.zip

This means you'll need to change them to work with SQL Server 2000. Note that this is not pretty easy, as some SQL Server 2005 specific features are used. Here are the most obvious problems you'll run into:

1. You'll need to remove "WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]" from every table create statement.

2. The database uses varchar(max) and nvarchar(max) which do no exist in SQL Server 2000. The most obvious substitution would be text and ntext.
However, when you use ntext, you'll run into problems with the Bug table, as some queries use DISTINCT against this table. DISTINCT is allowed with nvarchar(max) but not with ntext. Possible work arounds:
  a) Change the offending queries
  b) Change the Description column from the Bug table from ntext to varchar(8000). Note that this limits the amount of text a user can enter for a bug.

3. In the code of the application, you'll need to rewrite some stuff that uses the database caching features of ASP.NET 2.0.

This list is probably not complete, but I think it covers the biggest issues.

Why is Sql Express 2005 not an option?

Good luck and have fun....

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

SQL Express 2005 is not an option for me because I work in a SQL Server 2000 shop.

We've updated to VS 2005, but not SQL Server 2005. None of my peers are actually using SQL Server 2005 in production yet. My opinion is that it is commonplace to more to the latest, greatest, sexiest product one item at a time, take time to see how it works, then implement the next software upgrade.

I don't think we are at a time of development where it should be assumted that everyone can/is using SQL Server 2005. And providing code that only works on that platform is difficult to work with.

I wish this would be considered and code/SQL Server code would be offered for 2005 and 2005, and some instructions for using either or.

Just my 2 cents.

Oh, I agree with all of that. In my company, we haven't switched to SQL 2005 either. We have a large number of applications built for SQL 2000, and we'll be building those kind of applications for the foreseeable future.

However, IMO, books are a bit different. Books are supposed to be about new stuff. Even books about new stuff get outdated much sooner than you'd think (or like), so books about old stuff are even worse... ;)

So, ASP.NET 2 was released together with SQL Server 2005. The latter product has a large number of new and compelling features. So, obviously, a book about new technology should focus on the new features as much as possible, don't you think? We used a number of features that are not (easily) backwards compatible. Is that wrong? I don't think so. We used those features because they contributed something useful to the final solution; not just because they exist or were thought of as "cool".

I guess we all have to live with the fact that not all of those applications are backwards compatible with SQL Server 2000. This is a book about new technology, so it tries to use that new technology as much as possible. Isn't this what I have been doing in this thread?

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

I meant that I wish solutions for things for Sql Server 2000 & 2005 would be provided, for now until more migration towards SQL Server 2005 occurs, without having to go to a publisher's forum and ask for it. I know I can't be "the only one" stuck with Sql Server 2000 because I have no choice.

I really do understand about the book teaching new technologies and using them. I guess just something is different with this one because you're supposed to be able to use the projects out of the box, and no where in the book description did it say Sql Server 2005 Express was required and the only database solution for these projects. Honestly I wouldn't have purchased the book had I known (no discredit to the book itself, I really do like the source material inside a great deal).

Here's to moving onto SQL Server 2005 sometime soon :P