SQL Server questions

[Magiaus]

1) Is there away I can get the size of my database? Maybe use the schema or something?

2) Anyone know of away to enforce relational integraty on a table where one field could be the key for multi tables? I.E. PictureOwnerGUID could be the key for a product, contact, quote, logo, message, or 00000-00... for none. I wanted to allow delte cascade and not have to worry with a transaction because I've never done a transaction in SQL Server.

3)Transactions, Rollback, and Commit. I understand the concept, but could anyone show me the SQL and asp.net code for a simple say 3 part Transaction or whatever. Nothing super complex or anything. Just a real basic one. I know it has to execute on the same connection. That if you call rollback everything goes back to before the transaction started, and when Commit is called everything is saved. I just haven't seen one for sql server or the .net code before.

Hope you guys can help. I can get a book for a few days.

[Serge]

1. Call sp_helpdb system stored procedure to get database size and it's path.
sp_helpdb 'YourDBName' will return 2 recordsets,
first recordset will return
DBName, db_size (in MB), owner, dbid, createddate, status and compatibilityLevel
second recordset will return
DBName, fileid, fileName, filegroup, size(in KB), maxsize, growth and usage

2. This should be done on the database level, meaning that you will have to setup relationship within Enterprise Manager (at least this is the easiest way)

3. You can write a stored procedure that will accept some parameters and create a transaction within that procedure:

create procedure usp_UpdateSomeData
@MyParamId int,
@MyParamValue varchar(255)

as

begin transaction


update MyTable
set 
	MyValueColumn = @MyParamValue
where
	ColumnId = @MyParamId


if @@error = 0
	begin
		commit
	end	
else
	begin
		rollback
	end	

--this will notify your application how many rows were update
return @@rowcount

[Magiaus]

Originally posted by Serge
[B]1. Call sp_helpdb system stored procedure to get database size and it's path.
sp_helpdb 'YourDBName' will return 2 recordsets,
first recordset will return
DBName, db_size (in MB), owner, dbid, createddate, status and compatibilityLevel
second recordset will return
DBName, fileid, fileName, filegroup, size(in KB), maxsize, growth and usage

okay. I need a T-SQL book. How would I call this from .net?

On #2 I had a weird problem. I had tried setting it up before I asked, you see. I get a message about the Primary key of a message not mapping to the OwnerGUID field because of a primary key issue. I don't have the ent. manager. I think if I did I would be having less trouble. vs.net does pretty well but it is lacking some things. I just went on and hard code the relational delete and update cascasde for the picture table since it's a heap and can relate to almost every other piece of data on the site..... less chance of stuff not getting deleted but the relations should work and they don't so that is troubling....

[nemaroller]

The VS professional version has the ability to create stored procs, tables, design diagrams, create views, define constraints and primary keys, etc...

[Magiaus]

Yeah. I just removed all me relations and checked all my primary keys and indexies, because I have real bug. I have a product system. The system handles the source and production of products. Well, The production section is doing great but when I add new soure(with the web site) a new product is created.

Products
ProductGUID uniqueidentifier PRIMARYKEY
ProductName varchar(300)

SourceInformation
SourceGUID ...
SourceProductGUID ....
.....

The source and production tables are about the same except production has more fields. It works fine. The ASP.Net is the same I practicly copy and pasted the Production class to make the source class. The Keys are the same and the indexies. All relations have been removed and there are no constraints.

I hadn't figured out the SQL Server tool in vs.net completely when I posted this, but I have now and it isn't SQL Server doing this and I can't find a reason my code would do it or for the sql to do it....... I think I have pulled all my hair out. I was almost done with this site @&^%#$!*@

[Magiaus]

I'm a dork sometimes. I am using a data class and I passed it the wrong guid to save with. So it did an insert instead of an update. dumb. dumb. dumb.