Check DB Table Size (sp_spaceused)

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

Example
EXEC sp_spaceused N'Sales.SalesPerson';

Result

Limit - MySQL Command

Limit is used to limit your MySQL query results to those that fall within a specified range. It is use for select number of row of record from your query.

your table record : 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15

SELECT * FROM 'YourTable' Limit 10
- It will select 10 rows of record from the table.

Result : 1,2,3,4,5,6,7,8,9,10

SELECT * FROM 'YourTable' LIMIT 0, 3 

- It will return 3 rows of record and start from index 0

Result : 1,2,3

SELECT * FROM 'YourTable' LIMIT 2, 3 

- It will return 3 rows of record and start from index 2

Result : 3,4,5

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator

SQL Server Error Message :
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

In order to compare or sort text/ntext, you need to convert it to varchar (or similar datatype that can allow compare/sort). Note, text/ntext often has a large capacity for data than varchar.

Example SQL Code 1:
[...] ORDER BY TableColumn
change to
[...] ORDER BY cast(TableColumn as varchar(500))


Example SQL Code 2:
[...] GROUP BY TableColumn
change to
[...] GROUP BY cast(TableColumn as varchar(500))

Saving changes is not permitted in SQL 2008 Management Studio

When you design a table in a database and then try to make a change to a table structure that requires the table to be recreated, the Database Management Studio will not allow you to save the changes.

This is caused by a configuration setting that default results in the following dialog:

Error Screen

Solutions:

This is by design and can be quickly fixed in Management Studio by unchecking a property.
To fix this in Management Studio, go to Tools -> Options then go to the Designer Page and uncheck "Prevent saving changes that require table re-creation

                                                                     

SharePoint webparts AJAX enabled?

The web parts in SharePoint 2007 are NOT Ajax enabled.  AJAX support didn't come along for 2007 until SP1.  The web parts weren't re-written to add AJAX to them.  Some of the SharePoint 2010 web parts are AJAX enabled, but not in 2007.

Add Linked server using Command

A linked server configuration allows Microsoft® SQL Server™ to execute commands against OLE DB data sources on different servers. Linked servers offer these advantages:

• Remote server access.
• The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
• The ability to address diverse data sources similarly

sp_addlinkedserver

Creates a linked server. A linked server allows for access to distributed, heterogeneous queries against OLE DB data sources. After a linked server is created by using sp_addlinkedserver, distributed queries can be run against this server. If the linked server is defined as an instance of SQL Server, remote stored procedures can be executed.

Command:

EXEC sp_addlinkedserver @server= SERVER NAME

eg, your server name is myDBserver then 

EXEC sp_addlinkedserver @server = 'myDBserver'

sp_addlinkedsrvlogin

Creates or updates a mapping between a login on the local instance of SQL Server and a security account on a remote server.

Command:

EXEC sp_addlinkedsrvlogin  @rmtsrvname , 'TRUE' | 'FALSE' | NULL, @locallogin, @rmtuser, 

@rmtpassword 

eg,

EXEC sp_addlinkedsrvlogin 'myDBserver' , 'false', NULL, 'sa', 'password'

Sharepoint - SPSecurityTokenServiceConfig is not recognized

i try to run SET-SPSecurityTokenServiceConfig command in powershell, but it return me error message

" SET-SPSecurityTokenServiceConfig recognized as the name of cmdlet, function..."

Solution
Add-PSSnapin Microsoft.SharePoint.Powershell


After this, sharepoint commands will be available in PowerShell.

Microsoft.Ace.OLEDB.12.0 and OPENROWSET Errors

If you imports excel file into database using sql query 


INSERT INTO newtable
select  * FROM
OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\temp.xls;HDR=YES', 'SELECT * FROM [sheet$]')

and get below error message

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Ace.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "Microsoft.Ace.OLEDB.12.0" for linked server "(null)".





please run 2 sql below to solve it


EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered

If you run below SQL statement
---------------------------------------------------------
Select * into DBTable FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\myExcelFile.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]')




INSERT INTO DBTable select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\myExcelFile.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]')
--------------------------------------------------------


and you get an error message like below
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered


Solution
you need to download 
"Data Connectivity Components for 2007 office system Driver".
after you download, please restart your server.


you can click link here to download from microsoft site.


you will able to saw your microsoft excel driver 12.0 in your Data Sources(ODBC) in control panel

DTS not support in SQL Server 2008 R2

Limited SQL Server 2000 DTS Functionality on 64-bit Operating Systems

SQL Server 2008 does not include support for DTS in the following circumstances:

• There is no 64-bit design-time or run-time support for DTS packages. On a 64-bit computer, DTS packages, and Integration Services packages that run DTS packages, can run only in 32-bit mode. For more information, see How to: Install Support for Data Transformation Services Packages.
• There is also no 32-bit design-time or run-time support for DTS packages on Itanium-based operating systems. Therefore, you cannot create, view, modify, or run DTS packages on Itanium-based operating systems.
  

Supported SQL Server 2000 DTS Functionality

SQL Server 2008 includes support for the following DTS features:

• The DTS runtime, the object model that it exposes, and the dtsrun.exe command prompt utility.
• The Execute DTS 2000 Package task, for executing DTS packages within Integration Services packages.
• The ActiveX Script task, for backward compatibility only.
• The DTS Package Migration Wizard, for migrating DTS packages to the Integration Services package format.
• The Upgrade Advisor rules for DTS packages, for identifying potential issues that may be encountered when migrating packages.

more details on http://msdn.microsoft.com/en-us/library/bb500440.aspx