MSSQLWIKI

Karthick P.K on SQL Server

Archive for July, 2010

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created

Posted by Karthick P.K on July 31, 2010

Error

Saving changes is not permitted. The changes you have made require the following
tables to be dropped and re-created. You have either made changes to a table that
cant be re-created or enabled the option Prevent saving changes that require the
table to be re-created.

Resolution

SQL Server management studio—> Tools –>Options—> Designers –>Table and database designers –>Uncheck “Prevent saving changes that require table recreation”

image

Note: Some operations such as alter column will drop and recreate table. Ensure you do not abort SSMS while operation in progress. You might loose the table. I would recommend

script the changes and and run script in query window (or) At least review the script before you save changes using SSMS.

Thanks

Karthick P.K

Posted in SQL Server Tools, SSMS | Tagged: , , | 2 Comments »

Runtime error: ActiveX component can’t create object: ‘SQLDMO.SQLServer’

Posted by Karthick P.K on July 28, 2010

 

When you use SQLDMO ???

Install SQL Server2005 Backward compatibility

http://download.microsoft.com/download/3/1/6/316FADB2-E703-4351-8E9C-E0B36D9D697E/SQLServer2005_BC.msi

Also remember if you have installed 32-Bit SQL Server on 64-Bit you have to use Cscript.exe from SysWOW64

Posted in Configuration, SQL Server Setup | 1 Comment »

How to check if local system is connected to a network and identify the type of network connection

Posted by Karthick P.K on July 26, 2010

#include <windows.h> 
#include <iostream> 
using namespace std;
#pragma comment(lib, "Sensapi.lib")
#include <Sensapi.h>

void main()
{
    
bool a; 
LPDWORD lpdwFlags;
lpdwFlags = new DWORD;
a=IsNetworkAlive( lpdwFlags);

        if(GetLastError()!=0)
        {
            cout<<"IsNetworkAlive failed:%d"<<GetLastError();
        }
        else if(GetLastError()==0 & (!a) )
        {
            cout<<"Network is not connected";
        }
        else if(GetLastError()==0 & (a))
        {
            cout<<"Network is connected.Type: "<< *lpdwFlags;  //1=Lan and 2=WAN
        }

}

Regards

Karthick P.K

Posted in Programming, SQL General | 2 Comments »

I get Exception when i open SQL Server management studio

Posted by Karthick P.K on July 23, 2010

 

I get below exception when i open SSMS or register SQL Server

Error:

Microsoft .NET framework: Unhandled exception has occurred in a component in
your application. If you click continue the application will ignore this
error and attempt to continue. Unable to read the list of previously
registered servers on this system. Re-register your servers in the
‘Registered Servers’ window.

 

When you click to register new server you see

serialization output is invalid (Microsoft.SqlServer.Management.Sdk.Sfc)
Additional information: Unable to generate a temporary cl*** (result = 1)
error cs2001: Source file ‘C:\Windows\Temp\tt3xxx-n.0.cs’ could not be found.
error cs2008: no inputs specified
(system.xml)

 

Resolution: TEMP is pointing to invalid location.  Check if TEMP and TMP is defined properly in environment variables.

 

Regards

Karthick P.K

Posted in SQL Server Tools, SSMS | Tagged: , , , , | 2 Comments »

How to check if my account has LPM privilege

Posted by Karthick P.K on July 18, 2010

#include <windows.h> 
#include <string> 
#include <winbase.h> 
#include <iostream> 
using namespace std;
#include <psapi.h>
#pragma comment(lib,"psapi.lib")

void main()
    {

    long int s=0;
    printf("\nThis program will allocate memory using AWE allocator API's");
    printf("\nEnter the size of memory to be allocated using AWE API's:");
    scanf("%d",&s);
    LPVOID lpaddress=NULL; 
    SIZE_T size=s;   
    LPVOID ADD;
    BOOL bResult= FALSE;
    BOOL bResult2= FALSE;
    ULONG_PTR sizemap= (size)*1024*1024/4096;
    
                if( ! LoggedSetLockPagesPrivilege( GetCurrentProcess(), TRUE ) )  //. The SeLockMemoryPrivilege privilege must be enabled in the caller's token or the function will fail with ERROR_PRIVILEGE_NOT_HELD
                  {
                    printf("\n No Previledge");
                    printf("\n Error: %u", GetLastError() );
                      return;
                  }


    ULONG_PTR * aRAMPages = new ULONG_PTR[sizemap];


    ADD=VirtualAlloc(lpaddress,(size*1024*1024),MEM_RESERVE | MEM_PHYSICAL,PAGE_READWRITE);

    
    if (ADD==0)

    {
    printf ("allocation failled");
    printf("\n %u", GetLastError() );
        return;
    }



    bResult=AllocateUserPhysicalPages(GetCurrentProcess(),&sizemap,aRAMPages);

            if( bResult != TRUE ) 
            {
            printf("\n %uError in AllocateUserPhysicalPages", GetLastError() );
            return;
            }

     bResult2=MapUserPhysicalPages(ADD,sizemap,aRAMPages);

            if( bResult != TRUE ) 
            {
            printf("\n %uError in MapUserPhysicalPages", GetLastError() );
            return;
            }
printf("\nAllocated %d MB using AWE allocator API's which SQLServer uses when LPM is enabled.",s  );
system("pause");
}





 
BOOL LoggedSetLockPagesPrivilege ( HANDLE hProcess,BOOL bEnable)
{
  struct {
    DWORD Count;
    LUID_AND_ATTRIBUTES Privilege [1];
  } Info;

  HANDLE Token;
  BOOL Result;

  // Open the token.

  Result = OpenProcessToken ( hProcess,
                              TOKEN_ADJUST_PRIVILEGES,
                              & Token);

  if( Result != TRUE ) 
  {
    printf( "Cannot open process token.\n" );
    return FALSE;
  }

  // Enable or disable?

  Info.Count = 1;
  if( bEnable ) 
  {
    Info.Privilege[0].Attributes = SE_PRIVILEGE_ENABLED;
  } 
  else 
  {
    Info.Privilege[0].Attributes = 0;
  }

  // Get the LUID.

  Result = LookupPrivilegeValue ( NULL,
                                  SE_LOCK_MEMORY_NAME,
                                  &(Info.Privilege[0].Luid));

  if( Result != TRUE ) 
  {
    printf( "Cannot get privilege for %s.\n", SE_LOCK_MEMORY_NAME );
    return FALSE;
  }

  // Adjust the privilege.

  Result = AdjustTokenPrivileges ( Token, FALSE,
                                   (PTOKEN_PRIVILEGES) &Info,
                                   0, NULL, NULL);

  // Check the result.

  if( Result != TRUE ) 
  {
    printf ("Cannot adjust token privileges (%u) Error:", GetLastError() );
    return FALSE;
  } 
  else 
  {
    if( GetLastError() != ERROR_SUCCESS ) 
    {
      
    printf ("\nCannot enable the SE_LOCK_MEMORY_NAME privilege; ");
     printf ("\nPlease check the local policy.\n");
      return FALSE;
    }
  }

  CloseHandle( Token );

  return TRUE;
};




Regards
Karthick P.K

Posted in Programming | Tagged: , , , | 1 Comment »

How to run checkdb on all databases ?

Posted by Karthick P.K on July 15, 2010

 EXEC sp_MSforeachdb 'DBCC CHECKDB(?)' 

 

 

Thanks

Karthick P.K

Posted in DBCC | Tagged: | Leave a Comment »

Using DMVs to find out the index usage history- SQL Server Index Usage

Posted by Karthick P.K on July 6, 2010

SQL Server 2005 ships with a set of DMVs that can help you identify the missing indexes for your workload, Analyze the effectiveness of the existing ones and help find out index fragmentation.

Using DMVs to find out the index usage history

Over a period of time, you could create a lot of indexes on your tables and modify existing ones. However, in SQL 2000, you couldn’t estimate how effective were each of these indexes. Poorly design indexes could lead to performance overhead instead of enhancing performance.

In SQL 2005, you can query the sys.dm_db_index_usage_stats DMV to find out the indexes that have NEVER been used since the last start of SQL Server. You can use the following query to find that out:

 

select object_name(i.object_id) as ObjectName,

 

i.name as IndexName, s.user_updates, s.user_seeks, s.user_scans, s.user_lookups

 

from sys.indexes i

 

left join sys.dm_db_index_usage_stats s

 

on s.object_id = i.object_id and i.index_id = s.index_id and s.database_id = <dbid>

 

where objectproperty(i.object_id, 'IsIndexable') = 1 and

 

-- index_usage_stats has no reference to this index (not being used)

 

s.index_id is null or

 

-- index is being updated, but not used by seeks/scans/lookups

 

(s.user_updates > 0 and s.user_seeks = 0

 

and s.user_scans = 0 and s.user_lookups = 0)

 

order by object_name(i.object_id) asc

In the output, you will ALL the indexes that have never been used by any sort of workload on your server, since the last start of SQL Server.

For indexes that have NEVER been used (either for a SELECT or a DML statement), all columns will be NULL

For indexes that have NEVER been used (for a SELECT), but had to be updated due to a DML statement, the user_updates column will be >0, while other columns will be 0. It is these indexes that could cause severe performance overhead for your DML statements and might be worth dropping.

Give ample time for SQL Server to get exposed to all the workload after a restart, before running this query.

Using DMVs to find out missing indexes

When the query optimizer generates a query plan, it analyzes what are the best indexes for a particular filter condition. If the best indexes do not exist, the query optimizer generates a suboptimal query plan, but still stores information about these indexes. The missing indexes feature enables you to access information about these indexes so you can decide whether they should be implemented.

For more information on how to use this feature, please visit the following link

http://msdn2.microsoft.com/en-us/library/ms345417.aspx

Using DMVs to find out index fragmentation

The sys.dm_db_index_physical_stats dynamic management function replaces the DBCC SHOWCONTIG statement.

You can learn more about using this DMV to identify fragmentation, correcting it and possibly automating this activity for your server by visiting the following link:

http://msdn2.microsoft.com/en-us/library/ms188917.aspx

Posted in SQL Query, SQL Server Engine | Tagged: , , , , | 1 Comment »