Attach database to SQL2005 from old MSDE database

I have a medical records system, SoapWare v4.90, that uses MSDE (SQL2000) databases. Due to the 2gb limitation, I am trying to migrate over to SQL 2005 (Standard or Express) which the company says works fine. The SoapWare has a datamanager that allows me to log in to the MSDE instance, detach the SoapWare databases from msde (as well as do backups, etc) which I can confirm are detached.

Then I log back into a SQL2005 database instance using the datamanager and try to attach the database. This is what their pictured instructions demonstrate. However, I get an error:

Database 'sw_charts' cannot be upgraded because it is read-only or has read-only files. Make the database or files writeable, and rerun recovery.

Or... is there a way to attach the databases to SQLExpress manually?

Help pls?The instance of SQL Express needs to be run as "Local system" which is not the default. Once this is changed, the SQL Express database attaches to the old MSDE SQL 2000 database just fine, and the program appears to run without problems.

Thanks to anyone looking at my post, and hopefully, this will help others.

David|||1> use master
2> go

** close the database johandb

1>exec sp_dboption N'johandb',N'autoclose',N'false'
2>go

1>checkpoint
2>go

** detach database johan
1> exec sp_detach_db johandb, true
2> go

** attach with files

1> exec sp_attach_db @.dbname = 'johandb',
2> @.filename1='d:\mssql7\data\johandb_data.mdf',
3> @.filename2='d:\mssql7\data\johandb_log.ldf'
4> go

Regards,
Johan|||Does anyone use Lytec and SOAPware together?

MD in Texassql

Attach And Detach Replication

I would like to attach and detach my database due to change the log file
since that logfile already contain too large data.
But the problem occur, It's said that must drop the replication before do
any attach on that data .But i can't drop the replication since i still need
replicate the data from the other server back to local server.
so, anyone can give me any suggestion to do the attach withour drop the
replication?
For a supported solution, you'll need to disable replication before the
detach and attach
(http://msdn2.microsoft.com/en-us/library/ms188769.aspx). Ideally script out
the publication and do a nosync initialization if you can prevent any data
access.
For an unsupported solution that doesn't involve dropping the publication,
search this newsgroup for Ponnu...
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
"sam" wrote:

> I would like to attach and detach my database due to change the log file
> since that logfile already contain too large data.
> But the problem occur, It's said that must drop the replication before do
> any attach on that data .But i can't drop the replication since i still need
> replicate the data from the other server back to local server.
> so, anyone can give me any suggestion to do the attach withour drop the
> replication?
>
sql

Assistance with Trigger

Hi,

Hopefully this will be painless for you guys/gals - however due to my lack of skills/knowledge I need some clarification.

I have table_X which I have a trigger on INSERT setup.
This trigger updates Field_2 = '1' and inserts some rows in another table.

Is there some way that I can restrict this trigger to only run when Field_1 = "BLAH"
So essentially I am trying to find out how I can pull information/data from the record that fired the trigger and use this in the trigger? (ie to check if Field_1 = "BLAH" and to use Field_3 to further restrict the underlying triggers' updates and inserts)

Hopefully I have given enough information on this one - if not please let me know any points that I should need to clarify.

Thanks in advance for your help!!!

Cheersnow, you have to make sure you understand that no matter how many rows you insert, the trigger will fire only once per batch, ok?

if exists (select * from inserted where field_a = 'blah')
-- do your stuff|||Now that is a prompt response - thanks!

Yeah I understand that the trigger will only fire once per batch

So here is another stupid question - where you say select * from inserted. Is inserted a key word here or where you using that as my table name?
Because basically what I am after is (if it is possible) to have (for example) a record inserted with field_3 = 1234, and field_1 = "BLAH", then the trigger to go "okay" field_1 = BLAH and then carry on and use the field_3 value (ie 1234) in the remaining query to build other records in other tables.

If what I think is correct - the "inserted" word is actually just my table name - then the trigger will still be firing a lot of times because there is already a lot of records in the table with Field_1 = BLAH....

I fear I might be going round in circles and not making a hell of a lot of sense....*argh*|||no, inserted is an internal table visible only for the trigger itself. that's where new values resulting from your insert statement are stored.|||Sorry to keep asking a million and one questions, but so this table "inserted" which can be queried from the trigger (eg select * from updated), does this have the same structure (ie fields and field names) as the actual table which the data is going to be inserted into?

And is there any way where I can pull values from that table and use them as variables in the trigger?|||the answer is YES, except there are only 2 internal table, inserted and deleted. when the number of rows in both is the same it's an update operation, if deleted table is empty then it's an insert. and again, the structure of both is identical to the structure of the table for which the trigger was created.

edited:

and yes, you can store all values into variables from inserted, or reference that table throughout the execution of the trigger.|||Brilliant!!

Thanks heaps for your help!

asp/net sql update against ntext datatype

Guys Ihave a table that among the other columns has a column of ntext.

I also have another table that has another ntext column...
due to normalizations I need to merge the tables and set the column on the first table plus the value from the second table...(based on some where clause...

How can this be done?
if data type is varchar it is no problem..but due to text datatype I am unable to perform this update...

please help.

FrankUnfortunately you cannot concatenate an ntext field. I think the best you are going to be able to do is something like this:

SELECT
CAST(CAST(myText AS nVarchar(4000)) + CAST(myText AS nVarchar(4000)) AS ntext)
FROM
Test

However this will obviously truncate both columns at 4000 before putting them together.

Terri|||but the length exceeds 8000.

that is actually the problem.|||My only suggestion then is to run a VB.NET (or C#) program that selects in the data from both sources, concatenates the nText data, then updates the nText field in the desired database table. I don't think you are going to be able to do this within just Transact-SQL itself.

Terri|||well that requires application code..it must be a way of doing it too...in sql..
thanks anyway.|||There is no way to concatenate nText fields to each other. If you find a way around it, let us know.

Quoting directly from the Books Online:

+ (String Concatenation)
An operator in a string expression that concatenates two or more character or binary strings, columns, or a combination of strings and column names into one expression (a string operator).

Syntax
expression + expression

Arguments
expression

Is any valid Microsoft® SQL Server? expression of any of the data types in the character and binary data type category,except the image, ntext, or text data types. Both expressions must be of the same data type, or one expression must be able to be implicitly converted to the data type of the other expression.

Terri

Asp.net DataSet Object

I've been using the asp.net's DataSet and it seems to be leaving connections open, I'm not entirly sure if its due to the DataSet or some random code I forgot to close its connection, but I just wanted to cover all of my Basis. Is it possible though, that this DataSet object is leaving connections open?

Thanks for reading!

-Teenprogrammer

Hey,

A dataset works in disconnected mode; it wouldn't be the dataset that might be the problem, but the object querying it, like the command/dataadapter. Could you post any code? That would be helpful. As far as the ADO.NET base objects, I have not heard of that being a problem by someone else.