Hey Imar,

I just noticed something... the timestamp field in the "message" table was empty. I updated the timestamp field with data (within 1 hour) and it's showing up now in the chat screen. So I guess the problem must be in saving the message with the timestamp in SQL Server.

Thought I'd let you know.

Doug

www.fire-church.org

That explains a lot. The timestamp column has GetDate() as the default value. So, whenever you add a record, the column is automatically filled with today's date (provided the insert / update statement doesn't provide a different value).

Other columns have defaults too, so be sure to check those out. To do this, locate your database in the Server Explorer / Database panel. Then right click the table and choose Open Table Definition.
Then for each column check out the "Default value or databinding" option in the Column Properties grid.

With the defaults setup, things should work. If not, let me know, and I'll look into it again and make a SQL 2000 script if necessary and possible.

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
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.

Hi Imar,

Everything is working fine now with 1 exception. It's not grabbing all of the chat rooms from the table because of the "CASE" statment I mentioned that was in the "viewAllChatRoomsWithActiveNotation". If you could let me know how to translate that for SQL Server that would be great! :) SQL Server didn't allow case statements within the view.

Thanks for all of the help!
Doug

www.fire-church.org

I had no problem running the view in a SQL Server 2000 database.

Here's what I did you make it work:

1. Change the following columns from nvarchar(max) to something like nvarchar(50) or nvarchar(100), depending on the type of data the column is to store:

Room.Name
Room.Description
Category.Name
Category.Description
User.Email

2. Changed all remaining nvarchar(max) to ntext

3. Changed all occurences of

SELECT TOP (100) PERCENT

to

SELECT TOP 100 PERCENT

as SQL server 2000 doesn't seem to like the parentheses.

From there, the views could be created successfully...

Hope this helps,


Imar
---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
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.

Ok, I see my problem. It says my "Query Designer" does not support the case statement even though it is valid. Grrr... How can I create it outside of my query designer?

www.fire-church.org

Although it doesn't give you design time support, I think it still saves the query / view just fine.

Otherwise, use the Query Analyzer (choose Tools | Query Analyzer in Enterprise Manager) and execute your CREATE statement from there.

Hope this helps,

Imar
---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
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: Inamorala by VAS (Track 2 from the album: Sevdance) What's This?