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

Import Excel Data into Mssql using SQL Statement

you need to run one line per line, if you run all SQL together.
you will get below error message:
Incorrect syntax near 'sp_configure' 


Please Follow below Step 
Step 1: sp_configure 'show advanced options', 1
Output Message: Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.


Step 2: reconfigure
Output Message: Command(s) completed successfully.


Step 3: sp_configure 'Ad Hoc Distributed Queries', 1
Output Message: Configuration option 'Ad Hoc Distributed Queries' changed from 1 to 1. Run the RECONFIGURE statement to install.


Step 4: reconfigure
Output Message: Command(s) completed successfully.


Step 5: Run your SQL to import Excel Files


Insert Excel Data into New Table (Create New Table)

INSERT INTO myTableName

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\Staffs.xls', 'SELECT * FROM [Sheet1$]')

Insert Excel Data into Existing Table

SELECT * INTO  myTableName FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\Staffs.xls', 'SELECT * FROM [Sheet1$]')

SQL Code to import Excel Data into New Table in Database


sp_configure 'show advanced options', 1
reconfigure
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure

SELECT * INTO  myTableName FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\Staffs.xls', 'SELECT * FROM [Sheet1$]')

SQL Code to import Excel Data into Existing Table in Database

sp_configure 'show advanced options', 1
reconfigure
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure

SELECT * INTO  myTableName FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\Staffs.xls', 'SELECT * FROM [Sheet1$]')

Database owner is already a user in the database

Error : Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 46
The proposed new database owner is already a user in the database

If you are getting above error message while changing the database owner. ‘DBuser’ cannot become the owner of the current database if it already has access/dbo access to the database through an existing alias or user security account within the database. To avoid this, drop the alias or ‘user’ within the current database first. here is the solution.


Use Database_Name;
sp_dropuser ‘DBUser’
sp_changedbowner ‘DBUser’

Back Up the Transaction Log When the Database Is Damaged

To create a backup of the currently active transaction log

1. Execute the BACKUP LOG statement to back up the currently active transaction log, specifying:
   
   • The name of the database to which the transaction log to back up belongs.
   • The backup device where the transaction log backup will be written.
   • The NO_TRUNCATE clause.
     
     This clause allows the active part of the transaction log to be backed up even if the database is inaccessible, provided that the transaction log file is accessible and undamaged.
2. Optionally, specify:
   
   • The INIT clause to overwrite the backup media, and write the backup as the first file on the backup media. If no existing media header exists, one is automatically written.
   • The SKIP and INIT clauses to overwrite the backup media, even if there are either backups on the backup media that have not yet expired, or the media name does not match the name on the backup media.
   • The FORMAT clause, when you are using media for the first time, to initialize the backup media and rewrite any existing media header.
     
     The INIT clause is not required if the FORMAT clause is specified.
     
     
     
     
     
     

This example backs up the currently active transaction log for the MyAdvWorks_FullRM database even though MyAdvWorks_FullRM has been damaged and is inaccessible. However, the transaction log is undamaged and accessible:

BACKUP LOG DBNAME

   TO MyAdvWorks_FullRM_log1

   WITH NO_TRUNCATE

GO

10 reasons why go for SQL Server 2008

10.  Plug-in model for SSMS.   SSMS 2005 also had a plug-in model, but it was not published, so the few developers that braved that environment were flying blind.  Apparently for 2008, the plug-in model will be published and a thousand add-ins will bloom. 

9.  Inline variable assignment.  I often wondered why, as a language, SQL languishes behind the times.  I mean, it has barely any modern syntactic sugar.  Well, in this version, they are at least scratching the the tip of the iceberg. 

Instead of:

DECLARE @myVar int 
SET @myVar = 5

you can do it in one line:

DECLARE @myVar int = 5

Sweet. 

8.  C like math syntax.  SET @i += 5.  Enough said.  They finally let a C# developer on the SQL team. 

7.  Auditing.  It's a 10 dollar word for storing changes to your data for later review, debugging or in response to regulatory laws.  It's a thankless and a mundane task and no one is ever excited by the prospect of writing triggers to handle it.  SQL Server 2008 introduces automatic auditing, so we can now check one thing off our to do list.

6.  Compression.  You may think that this feature is a waste of time, but it's not what it sounds like.  The release will offer row-level and page-level compression.  The compression mostly takes place on the metadata.  For instance, page compression will store common data for affected rows in a single place. 

The metadata storage for variable length fields is going to be completely crazy: they are pushing things into bits (instead of bytes).  For instance, length of the varchar will be stored in 3 bits. 

Anyway, I don't really care about space savings - storage is cheap.  What I do care about is that the feature promised (key word here "promises") to reduce I/O and RAM utilization, while increasing CPU utilization.  Every single performance problem I ever dealt with had to do with I/O overloading.  Will see how this plays out.  I am skeptical until I see some real world production benchmarks.

5.  Filtered Indexes.  This is another feature that sounds great - will have to see how it plays out.  Anyway, it allows you to create an index while specifying what rows are not to be in the index.  For example, index all rows where Status != null.  Theoretically, it'll get rid of all the dead weight in the index, allowing for faster queries. 

4.  Resource governor.  All I can say is FINALLY.  Sybase has had it since version 12 (that's last millennium, people).  Basically it allows the DBA to specify how much resources (e.g. CPU/RAM) each user is entitled to.  At the very least, it'll prevent people, with sparse SQL knowledge from shooting off a query with a Cartesian product and bringing down the box.

Actually Sybase is still ahead of MS on this feature.  Its ASE server allows you to prioritize one user over another - a feature that I found immensely useful.

3.  Plan freezing.  This is a solution to my personal pet peeve. Sometimes SQL Server decides to change its plan on you (in response to data changes, etc...).  If you've achieved your optimal query plan, now you can stick with it.  Yeah, I know, hints are evil, but there are situations when you want to take a hammer to SQL Server - well, this is the chill pill.

2.  Processing of delimited strings.   This is awesome and I could have used this feature...well, always.  Currently, we pass in delimited strings in the following manner:

exec sp_MySproc 'murphy,35;galen,31;samuels,27;colton,42'

Then the stored proc needs to parse the string into a usable form - a mindless task.

In 2008, Microsoft introduced Table Value Parameters (TVP). 

CREATE TYPE PeepsType AS TABLE (Name varchar(20), Age int) 
DECLARE @myPeeps PeepsType 
INSERT @myPeeps SELECT 'murphy', 35 
INSERT @myPeeps SELECT 'galen', 31 
INSERT @myPeeps SELECT 'samuels', 27 
INSERT @myPeeps SELECT 'colton', 42

exec sp_MySproc2 @myPeeps 

And the sproc would look like this:

CREATE PROCEDURE sp_MySproc2(@myPeeps PeepsType READONLY) ...

The advantage here is that you can treat the Table Type as a regular table, use it in joins, etc.  Say goodbye to all those string parsing routines.

1. Intellisense in the SQL Server Management Studio (SSMS).  This has been previously possible in SQL Server 2000 and 2005 with use of 3rd party add-ins like SQL Prompt ($195).  But these tools are a horrible hack at best (e.g. they hook into the editor window and try to interpret what the application is doing).  

Built-in intellisense is huge - it means new people can easily learn the database schema as they go.

LINQ - Aggregate Operators

Below code is to show the unique value:


Public Sub LinqSample1()
    Dim arrDistinct() = {1, 1, 1, 2, 2, 3, 4, 4, 5, 5}

    Dim strUniqueFactors = arrDistinct.Distinct().Count()

    Console.WriteLine(strUniqueFactors & " is unique value.")
End Sub


Result:
3 is unique value.

mysql - sql injection prevention

If you have ever taken raw user input and inserted it into a MySQL database there's a chance that you have left yourself wide open for a security issue known as SQL Injection.


SQL injection is someone inserting a SQL statement to be run on your database without your knowledge. Injection usually occurs when you ask a user for input, like their name, and instead of a name they give you a MySQL statement that you will unknowingly run on your database.


for PHP users, All you need to do is use the function mysql_real_escape_string.


echo "Escaped Evil Injection:";
$name_evil = "'; DELETE FROM customers WHERE 1 or username = '"; 
$name_evil = mysql_real_escape_string($name_evil);
$query_evil = "SELECT * FROM customers WHERE username = '$name_evil'";


Result
Escaped Bad Injection:
SELECT * FROM customers WHERE username = '\'; DELETE FROM customers WHERE 1 or username = \''


           

Microsoft Distributed Transaction Coordinator May Stop Responding in a Low Memory Situation

When a server is in low memory situation, the Microsoft Distributed Transaction Coordinator (MS DTC) process (Msdtc.exe) may stop responding (crash).


When MS DTC tries to manage new transactions, the attempt fails because of a lack of resources.


Workaround
To work around this problem, verify that the memory configuration of the computer is correct, and then correct the memory configuration if it is not.


Microsoft Fix
To resolve this problem, obtain the latest service pack for Windows 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

260910  How to Obtain the Latest Windows 2000 Service Pack

Pro LINQ - Language Integrated Query in C# - 2010

Pro LINQ - Language Integrated Query in C# - 2010

I found LINQ tutorials for C#, here i just share to who is interest

Download It Now

RESTORE DATABASE using command

I found this error message when i restore the database using the GUI and it appear this error message "Error 3154: The backup set holds a backup of a database other than the existing database"
It because of trying to restore database on an existing active database.

Solution:
RESTORE DATABASE DatabaseName
FROM DISK = 'C:\myDatabase.bak'
WITH REPLACE

Use WITH REPLACE when using RESTORE command when u saw above error message "Error 3154: The backup set holds a backup of a database other than the existing database"

Tested in MSSQL 2005

SQL @@ROWCOUNT

Returns the number of rows affected by the last statement. It will let you to do a checking on the record you updated.

If the number of rows is more than 2 billion, use ROWCOUNT_BIG.

Example
USE DB2008;
GO
UPDATE User
SET JobTitle = 'Manager'
WHERE UserID = 'u10021'
IF @@ROWCOUNT = 0
PRINT 'Warning: No rows were updated';
GO

SQL Injection

SQL injection is an attack in which malicious code is inserted into strings that are later passed to an instance of SQL Server for parsing and execution. Any procedure that constructs SQL statements should be reviewed for injection vulnerabilities because SQL Server will execute all syntactically valid queries that it receives. Even parameterized data can be manipulated by a skilled and determined attacker.

The primary form of SQL injection consists of direct insertion of code into user-input variables that are concatenated with SQL commands and executed. A less direct attack injects malicious code into strings that are destined for storage in a table or as metadata. When the stored strings are subsequently concatenated into a dynamic SQL command, the malicious code is executed.

Example
UserID = Request.form ("userid");
var sql = "select * from UserTable where ID= '" + UserID + "'";

The user is prompted to enter the User ID. If she or he enters "jack", the query assembled by the script looks similar to the following:

select * from UserTable where ID = 'jack'

However, assume that the user enters the following:

jack; drop table UserTable--

In this case, the following query is assembled by the script:

select * from UserTable where ID = 'jack''; drop table UserTable--'

The semicolon (;) denotes the end of one query and the start of another. The double hyphen (--) indicates that the rest of the current line is a comment and should be ignored. If the modified code is syntactically correct, it will be executed by the server. When SQL Server processes this statement, SQL Server will first select all records in UserTable where ID is jack. Then, SQL Server will drop UserTable.

Input character	Meaning in Transact-SQL
;	Query delimiter.
'	Character data string delimiter.
--	Comment delimiter.
/* ... */	Comment delimiters. Text between /* and */ is not evaluated by the server.
xp_	Used at the start of the name of catalog-extended stored procedures, such as xp_cmdshell.

Select Statement for Different Database

Database SQL Syntax
- Different databases using different sql statement

DB2
select * from table fetch first 10 rows only

Informix
select first 10 * from table

Microsoft SQL Server and Access
select top 10 * from table

MySQL and PostgreSQL
select * from table limit 10

Oracle 8i
select * from (select * from table) where rownum <= 10

Linked Servers

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

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

When setting up a linked server, register the connection information and data source information with SQL Server. After registration is success, that data source can always be referred to with a single logical name.

You can manage a linked server definition with stored procedures or through SQL Server Enterprise Manager:

With stored procedures:

- Create a linked server definition using sp_addlinkedserver. To view information about the linked servers defined in a given instance of SQL Server, use sp_linkedservers. For more information, see sp_addlinkedserver and sp_linkedservers.

- Delete a linked server definition using sp_dropserver. You can also use this stored procedure to remove a remote server. For more information, see sp_dropserver.

With SQL Server Enterprise Manager:

- Create a linked server definition using the SQL Server Enterprise Manager console tree and the Linked Servers node (under the Security folder). Define the name, provider properties, server options, and security options for the linked server. For more information about the various ways a linked server can be set up for different OLE DB data sources and the parameter values to be used, see sp_addlinkedserver.

- Edit a linked server definition by right-clicking the linked server and clicking Properties.

- Delete a linked server definition by right-clicking the linked server and clicking Delete.

How Long Stored Procedure stay in Sql Server 2000 cache?

SQL Server 2000

once the execution plan is generated for a Stored Procedure, it stays in the procedure cache. Lazy writer only keep looking and throwing out unused plans out of the cache "only when space is needed in cache".

Below are some documented and undocumented DBCC commands available in SQL Server 2000 to deal and find more information about SQL Server cache.

To Monitor the cahce:

DBCC SQLPERF (LRUSTATS)
DBCC CACHESTATS
DBCC MEMORYSTATUS
DBCC PROCCACHE
To clean the cache:
DBCC FLUSHPROCINDB
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

You can read more from SQL 2000 Topic under "Lazy Writer", 'Freeing and Writing Buffer Pages' at: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa_8unn.asp

awe enabled Option - MsSql 2005

In Windows Server 2003, you able change the Address Windowing Extensions (AWE) API to provide access to physical memory in excess of the limits set on configured virtual memory. The specific amount of memory you can use depends on hardware configuration and operating system support.

• Windows Server 2003, Standard Edition supports physical memory up to 4 gigabytes (GB).
• Windows Server 2003, Enterprise Edition supports physical memory up to 32 GB.
• Windows Server 2003, Datacenter Edition supports physical memory up to 64 GB.

Extra Note

• You are not running Express or Workgroup version of SQL Server. Please check here for further details about limitations in different versions of SQL Server.
• you have enabled awe enabled option and set max server memory to the maximum memory you can allocate to SQL Server. This is applicatiable for 32 but versions of OS and not required in 64 bit version of Windows servers. Please check here for further details and how to configure the memoryfor SQL Server

COMPUTE (Transact-SQL) - SQL Server 2005

Generates totals that appear as additional summary columns at the end of the result set. When used with BY, the COMPUTE clause generates control-breaks and subtotals in the result set.

USE Database;
GO
SELECT CustomerID, OrderDate, SubTotal, TotalDue
FROM Sales.SalesOrderHeader
WHERE ID = 1
ORDER BY OrderDate
COMPUTE SUM(SubTotal), SUM(TotalDue);