Showing posts with label SQL Query. Show all posts
Showing posts with label SQL Query. Show all posts

2008-01-15

Check Database Memory

When I use the TaskPad in Enterprise Manager to check a database's allocated, used, and free space (as reported on the General tab), the values I see don't match the values I get when I use the sp_HelpDB and sp_Spaceused stored procedures. For example, when I checked one of my databases today, I got the following information:

  • In TaskPad, Enterprise Manager reported that the database had 211549.75MB of allocated space, 110294.44MB of used space, and 101255.31MB of free space.
  • For the same database, the sp_Spaceused stored procedure told me I had 212113.50MB of allocated space and 19541.14MB of unallocated space.
  • The sp_helpDB stored procedure reported that I had an MDF file size of 211549.75MB.

Can I use T-SQL to return the same information that I see when I use the TaskPad?

To generate the information on the TaskPad, Enterprise Manager uses a combination of the following four queries.

EXEC sp_spaceused
SELECT fileid, name, filename, size, growth, status, maxsize
FROM dbo.sysfiles WHERE (status & 0x40) <>0
DBCC sqlperf(logspace)
DBCC showfilestats

Enterprise Manager uses SQL-DMO to retrieve the information. . . .

Content by Microsoft's SQL Server Development Team Ask Microsoft

2007-09-17

How to get table sizes for the database

sp_spaceused (Transact-SQL)

You can use this stored procedure (T-SQL) to get a report of the table sizes for the database.

Displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or SQL Server 2005 Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.

Sample Code

1. One table
exec sp_spaceused [tableName]

2. All tables in your database

DECLARE @@TableName Nvarchar(100)

DECLARE my_cursor CURSOR FOR
Select sysobjects.name From sysobjects Where xtype='u' OPEN my_cursor

FETCH NEXT FROM my_cursor INTO @@TableName
WHILE @@FETCH_STATUS = 0
BEGIN

exec sp_spaceused @@TableName

FETCH NEXT FROM my_cursor INTO @@TableName
END
CLOSE my_cursor
DEALLOCATE my_cursor
GO


Result value

Column name Data type Description

name

nvarchar(128)

Name of the object for which space usage information was requested.

The schema name of the object is not returned. If the schema name is required, use the sys.dm_db_partition_stats or sys.dm_db_index_physical_stats dynamic management views to obtain equivalent size information.

rows

char(11)

Number of rows existing in the table. If the object specified is a Service Broker queue, this column indicates the number of messages in the queue.

reserved

varchar(18)

Total amount of reserved space for objname.

data

varchar(18)

Total amount of space used by data in objname.

index_size

varchar(18)

Total amount of space used by indexes in objname.

unused

varchar(18)

Total amount of space reserved for objname but no yet used.

2007-07-18

Create Data Dictionary By Query

This is a query that use for generate your simple data
dictionary.



Sample

select distinct syscolumns.name,systypes.name as Type,
syscolumns.length,sysproperties.value

From syscolumns
inner join sysobjects on sysobjects.id=syscolumns.id
left outer join sysproperties on
sysproperties.smallid=syscolumns.colid
and sysproperties.id = syscolumns.id
inner join systypes on
syscolumns.xtype = systypes.xtype
where sysobjects.name='TableName'

Solving The Multiple Inheritance Issue Under .NET Platform

.NET : Solving The Multiple Inheritance Issue Under .NET Platform


.NET platform does not support multiple inheritance. Do not confuse multilevel inheritance with multiple inheritance. With multiple inheritance we can have a subclass that inherits from two classes at the same time.

Let's suppose we have an application that has a class Customers and another class Vendors. If you wanted to combine these two classes into one CustomerVendor class it would be a combination of Customers and Vendors just like the diagram below.

Please copy the following link into a new browser windor to view the diagram: http://www.vbprofs.com/images/Article Images/VBNETinheritance.gif

In the above diagram we see how the CustomerVendor class inherits from both of those classes.

Multiple inheritance is complex and can be dangerous. The advantages of code re-usage prevail over complexity is up to your choice.

Multiple inheritance is not supported by VB.NET or .Net platform. Instead of multiple inheritance we can use multiple interfaces to achieve similar effect to multiple inheritance.

In VB.NET all objects have a primary or native interface, which is composed of properties, events, methods or member variables declared using Public keyword. Objects can implement also secondary interfaces by using Implement keyword.

Sometimes it is helpful for an object to have more than one interface, allowing us to interact with the object in different ways. Inheritance allow us to create subclasses that are a specialized case of the base class.

Example

Sometimes we have a group of objects that are not the similar, but we want to handle them the same manner. We want all the objects to act as if they are the same, even though they are different.

We can have some different objects in an application, such as customer, product, invoice etc. Each object would have a default interface appropriate to each individual object, and each of them is a different class. No natural inheritance is implied between these classes.

Let's suppose we want to print a document for each type of object. In this case we'd like to make them all act as printable object. To accomplish this we can define a generic interface that would enable generating a printed document. By implementing a common interface we are able to write a routine that accepts any object that implements a printed document.

To conclude, by implementing multiple interfaces in VB.NET, we can achieve a similar effect to that of multiple inheritance.


About the Author:

Thomas is an experienced Visual Basic developer, with expertise of 7+ years developing financial applications. His main IT skills are VB, SQL, Crystal Reports - should you need a VB developer for your projects feel free to contact Thomas through his personal website at http://www.Kaloyani.com or through http://www.VBprofs.com

2007-07-15

Will Microsoft Kill Free Email on the Net?

Spam has been such a problem that email filters are now widespread on the Net. These filters are a necessary response to the menace of spam. However, will the excuse of spam be used by companies such as Hotmail to charge for email?

Disturbing news that Hotmail (owned by Microsoft) is blocking perfectly legitimate emails because they have been caught by their anti-spam filters is increasing these fears.

Allan Gardyne of Associateprograms.com has been commenting recently about the difficulties SiteSell (owner of the website building package SiteBuildIt) has had with Hotmail.

http://associateprograms.com/discus/viewtopic.php?t=2847

When SiteSell complained to Hotmail they did not get a helpful response. Microsoft suggested that they use the services of a company called Bonded Sender which would ensure that SiteSell’s legitimate non-spam emails would reach their customers. It just happens that one of the owners of Bonded Sender is a former employee of Microsoft!

Many other companies have had similar problems with Hotmail. Bill Gates is on record as wishing to charge for email as a method of preventing spam. Unfortunately, when the big companies on the Net gang up like this, a sort of inevitability about charging for email creeps in. Nearly 60% of email is handled by Hotmail, AOL, and Yahoo combined. These three companies could have the power to force customers to pay for their email.

However, customers still have bargaining power. They may decide to stop using Hotmail and opt for a genuine free service. The founding fathers of the Internet had a public service attitude to the free dissemination of information. If the big companies charge for email, this egalitarian ideal will be lost.

The most appealing aspect of the Internet for many people was precisely this level-playing field that it created. The hobbyist in any part of the world could communicate and pass information (through email!) to other enthusiasts. The small entrepreneur could set up a website and make money without reference to the multinationals. However, this freedom will be lost if customers are not vigilant. The big companies like Microsoft are businesses concerned with profit. The freedom of access that the Internet gives to the small person in any part of the world is not a priority of the multinationals.


© John Lynch
==================================================
For Free Internet marketing E-books on Net Writing, Web Mastering, Net Auctions, Affiliate help go to: http://www.merchant-account-service.com/free_courses_online.html
==================================================


Read more articles by: John Lynch
.
Article Source: www.iSnare.com

2007-07-13

Change All Column's Collation By Sql Script

Change All Column's Collation By Sql Script

You can change your database collation from enterprise manager. But your columns in table did not change accordingly.
Here is show how to change all collation columns in database. (You can change Thai_CI_AS to your collation.)


Sample Script
DECLARE @@TableName Nvarchar(100)
DECLARE @@ColumnName Nvarchar(100)
DECLARE @@ColumnType NvarChar(100)
DECLARE @@ColumnLengh FLOAT
DECLARE
@@SQL NvarChar(1000)
DECLARE
@@IsNullAble NvarChar(50)

DECLARE
my_cursor CURSOR FOR
Select sysobjects.name From sysobjects Where xtype='u' OPEN my_cursor

FETCH NEXT FROM my_cursor INTO @@TableName
WHILE
@@FETCH_STATUS = 0
BEGIN

DECLARE my_column CURSOR FOR

select syscolumns.name,systypes.name as Type, syscolumns.length ,syscolumns.isnullable

From syscolumns
inner join sysobjects on sysobjects.id=syscolumns.id

left outer join sysproperties on
sysproperties.smallid=syscolumns.colid and sysproperties.id = syscolumns.id

inner join systypes on
syscolumns.xtype = systypes.xtype

where sysobjects.xtype='u' And sysobjects.name=@@TableName And (systypes.name='nvarchar' or systypes.name='varchar')


OPEN my_column

FETCH NEXT FROM my_column INTO @@ColumnName,@@ColumnType,@@ColumnLengh,@@IsNullAble


WHILE @@FETCH_STATUS = 0

BEGIN

IF (@@IsNullAble=1)

BEGIN

Select @@SQL =( 'ALTER TABLE ' + @@TableName + ' ALTER COLUMN ' + @@ColumnName + ' ' + @@ColumnType +
'(' + CAST(@@ColumnLengh as
NVARCHAR) + ') COLLATE ' + ' Thai_CI_AS NULL')

END

ELSE

BEGIN

Select @@SQL =( 'ALTER TABLE ' + @@TableName + ' ALTER COLUMN ' + @@ColumnName + ' ' +
@@ColumnType +
'(' + CAST(@@ColumnLengh as NVARCHAR) + ') COLLATE ' + ' Thai_CI_AS NOT NULL')
END


PRIN(@@SQL)
--EXEC
(@@SQL)


FETCH NEXT FROM my_column INTO @@ColumnName,@@ColumnType,@@ColumnLengh,@@IsNullAble

END

CLOSE my_column

DEALLOCATE my_column


FETCH NEXT FROM my_cursor INTO @@TableName

END
CLOSE my_cursor
DEALLOCATE my_cursor
GO
.

2007-07-11

VB.Net: Dynamic Usage Of Eventhandlers

VB.Net: Dynamic Usage Of Eventhandlers

WithEvents and Handles clause requires form us to declare the object variable and the event handler as we write our code, so linkage is created upon compilation. On the other hand, with AddHandler and RemoveHandler, linkage is created and removed at runtime, which is more flexible.

Let's assume that we want to load several MDI child forms, allowing each of them to be loaded only once, and of course to know when one of the child forms is closed. Since we have several forms to load we would like to use the AddHandler and RemoveHandler keywords so we can be flexible and write the minimal code we can.

Let's get dirty.

1. In each MDI child form we have to declare a public event.
Public Event FormClosed(ByVal f As Form)

2. In each MDI child form we have to use the Form_Closed method which handles the MyBase.Closed class and raise the FormClosed event.

Private Sub Form1_Closed(ByVal sender As Object, ByVal e As System.EventArgs) _
Handles MyBase.Closed
RaiseEvent FormClosed(Me)
End Sub


3. On our MDI form we need to declare two member variables. The first's of type Form and the second's type is ArrayList.
Private m_f(0) as Form
Private m_sLoadedChildForms As New ArrayList


4. We need to implement a method the will search the MDI child forms that are loaded. We'll also use this method when we unload the MDI child forms.

Private Function SearchChildForm(ByVal strSearchForm As String, _Optional ByVal idxEventHandler As Long = -1) As Long
Dim i As Long = 0
For i = 0 To m_sLoadedForms.Count - 1
If m_sLoadedForms.Item(i) = strSearchForm Then
Dim j As Long = 0
For j = m_f.GetLowerBound(0) To m_f.GetUpperBound(0)
If m_f(j).Name = strSearchForm Then idxEventHandler = j
Next j
Return i
End If
Next
Return -1
End Function


5. We need to implement a method to load the mdi child forms and use the SearchChildForm method in order not to load the same mdi child form second time.

Private Sub LoadChildForms(ByVal f As Form)
If m_f.GetUpperBound(0) > 0 Then
ReDim Preserve m_f(m_f.GetUpperBound(0) + 1)
End If
m_f(m_f.GetUpperBound(0)) = f I
f Not SearchChildForm(m_f(m_f.GetUpperBound(0)).Name()) >= 0 Then
m_f(m_f.GetUpperBound(0)).MdiParent = Me
AddHandler m_f(m_f.GetUpperBound(0)).Closed, _
AddressOf UnloadChildForm
m_f(m_f.GetUpperBound(0)).Show()
m_sLoadedChildForms.Add(m_f(m_f.GetUpperBound(0)).Name)
Else
If m_f.GetUpperBound(0) > 0 Then
ReDim Preserve m_f(m_f.GetUpperBound(0) - 1)
End If
End If
End Sub


6. At last we need to implement a method to take out our mdi child form from the array list so we can load it again if we want.

Private Sub UnloadForm(ByVal sender As System.Object, ByVal e As System.EventArgs)
Dim i As Long
Dim s As String = sender.GetType().Name
Dim IndexForEventHandler = -1
i = SearchChildForm(s, IndexForEventHandler)
If i >= 0 Then m_sLoadedForms.RemoveAt(i)
If IndexForEventHandler >= 0 Then
RemoveHandler m_f(IndexForEventHandler).Closed, AddressOf UnloadForm
m_f(IndexForEventHandler) = Nothing
End If
End Sub


About the Author:

Thomas is an experienced Visual Basic developer, with expertise of 7+ years developing financial applications. His main IT skills are VB, SQL, Crystal Reports - should you need a VB developer for your projects feel free to contact Thomas through his personal website at http://www.Kaloyani.com or through http://www.VBprofs.com