MS SQL

A DSN-less connection avoids the overhead of a datasource/DSN connection. It is a faster and recommended way to access your database. Please refer to this article on ASPFAQ.com for detailed information.   <!-- -->

Step 1 The following stored procedure can be very handy when you need to change the ownership of a table or any other object on your MS SQL database: 1 EXEC sp_changeobjectowner   Step 2 Here is the Syntax: 1 EXEC sp_changeobjectowner 'USERname.OBJECTname', 'dbo' This example changes the owner of the TEST table owned by [...]

Step 1 You will need to create the following stored procedure: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 [...]

The easiest way to convert an Access database to SQL Server is to use the Upsizing Wizard. Step 1 Preserves database structure, including data, indexes, and default settings.  Automatically converts Access validation rules and default settings to the appropriate SQL Server equivalents.  Maintains table relationships and referential integrity after you upsize.   Step 2 To [...]

Step 1 Because of the inserts, updates and deletes, the data and the index pages can get fragmented (just as your PC). The follwoing script can help you defrag your database:   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 [...]

This script returns the main properties for all data files and log files in the current database including logical and physical file names, size, used space, and file growth parameter.   1 2 3 4 5 6 7 8 9 SELECT fileid, sf.groupid, grp=LEFT([groupname],20), lname=LEFT([name],20), size_mb=[size]/128 ,used_mb=FILEPROPERTY([name], 'SpaceUsed')/128 ,file_growth=case when (sf.status&0x100000) > 0 then str(growth)+' [...]

The following code will display User Tables by SIZE in DESCENDING ORDER:   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 [...]

You can query the sysobjects system table or the INFORMATION_SCHEMA views by running the following stored procedure: 1 EXEC sp_tables This will output all the objects in your database. <!-- -->

    When you perform any data modification operations (INSERT, UPDATE, or DELETE statements) table fragmentation can occur. When changes are made to the data that affect the index, index fragmentation can occur and the information in the index can get scattered in the database. Fragmented data can cause SQL Server to perform unnecessary data [...]

By default SQL Express does not accept any remote connections. You won't be able to connect to it with SQL Management Studio, or an ODBC connection for example until you enable  it to accept connections. If you plan to do all SQL management while connecting to the server with Remote Desktop, then leave the SQL TCP/IP option [...]