Sửa lỗi phân trang trong module VirtueMart pagination

That's relatively easy to get rid of:
File \administrator\components\com_virtuemart\classes\pageNavigation.class.php
Add in line 118: if ($total_pages==1) {return "";};

Now I'm working on how to always show the limit box, because on the top it disappears when there's only one page.

EDIT: To always show the top navigation / results per page for was slightly more difficult:
In \components\com_virtuemart\themes\ja_larix\templates\browse\includes\browse_orderbyform.tpl.php
line change line 36 to
if( true ) {

(or remove "if" completely, of course ...)

How to Shrink Database File ?

SQL Script to shrink a unused space from a database file.

-- ***********************************************************************************/
-- Description  :    SQL Script to shrink a non used space from a database
-- Compatibility  :     7.0+
-- This Script should be executed in master
-- ***********************************************************************************/
use <database name>
go
-- Knowing information about database file
sp_helpfile
go
--- Shrinking a particular database file
dbcc shrinkfile (name= <datafile name>, <target percent>)
go

Where

• file_name, Is the logical name of the file to be shrunk.
• target_size, Is the size for the file in megabytes, expressed as an integer. If not specified, DBCC SHRINKFILE reduces the size to the default file size. The default size is the size specified when the file was created.

Example, to shrink the database file
First, I need to get the get the information about the current database file, specially the logical file name, which should be shrunk by executing sp_helpfile stored procedure.
In my example, I executed sp_helpfile system stored procedure in pubs database to get current database files information (logical file name and size is important). In my case file index_alter, is of 204800 KB (200 MB), which I wanted to shrunk, as show in below screen shot.

Shrinking index_alter file size to 20 MB, using the following script.
-- ***********************************************************************************/ -- Description : SQL Script to shrink a non used space from a database -- Compatibility : 7.0+ -- This Script should be executed in master -- ***********************************************************************************/ use pubs go sp_helpfile go DBCC SHRINKFILE ( 'index_alter',20) go OUTPUT

Once the File is shrunk and you recheck the file status by running sp_helpfile, you will see the new size, which 20480 in this case, as shown below.

Script to shrink a database non used space

HOW To SHRINK a Database?
SQL Script to shrink a non used space from a database

-- ***********************************************************************************/
-- Description  :    SQL Script to shrink a non used space from a database
-- Compatibility  :     7.0+
-- This Script should be executed in master
-- ***********************************************************************************/
DBCC shrinkdatabase ( <database name>, <target_percent>)
go

Where

• database_name Is the name of the database to be shrunk. If 0 is specified, the current database is used.
• target_percent, is the percentage of free space that you want left in the database file after the database has been shrunk.

OUTPUT

In above example, you see the result only for those database files, which are being shrunk, or in other words SQL Server does not display rows for those files which are not shrunk.

SQL Script to kill all blocked processes

-- ******************************************** */
-- Description                                  */
-- ============                                 */
-- Script to kill all blocked processes         */
-- =============================================*/
-- Compatibility  :     2000+                   */
-- ******************************************** */
declare @max_count int, @count int, @sqlstring varchar(100)
declare @spid_table table (spid int NOT NULL)

INSERT @spid_table
select spid
from master.dbo.sysprocesses
where spid in (select blocked from master.dbo.sysprocesses where blocked <> 0) and blocked = 0

select @max_count = MAX(spid) FROM @spid_table
select top 1 @count = spid from @spid_table

while @count <= @max_count
begin
    select @sqlstring = 'kill ' + CONVERT(varchar(4), @count)
exec(@sqlstring)
print @sqlstring 

IF @count = @max_count
  begin
    break
  end
ELSE
  BEGIN
    select top 1 @count = spid FROM @spid_table where spid > @count
  end
end

This script will kill all blocked process inside the SQL Server instance 

SQL Script to List all Linked Server Logins and their properties

During migration, there was an requirement to list down all link Server with respective properties to figure out what all objects will be required in new Server. Thus a Script is required is required, which can list down all linked Servers and associated properties.
The properties listed are

• Server
• Product 
• Provider
• Catalog 
• Local Login
• Remote Login Name
• RPC out Enabled and
• Data Access Enabled
• Object creation date

Also this lists the local login and remote login. This query result may have repeated linked server name if it has more than one associated remote login.

SELECT ss.server_id
          ,ss.name
          ,'Server ' = Case ss.Server_id
                            when 0 then 'Current Server'
                            else 'Remote Server'
                            end
          ,ss.product
          ,ss.provider
          ,ss.catalog
          ,'Local Login ' = case sl.uses_self_credential
                            when 1 then 'Uses Self Credentials'
                            else ssp.name
                            end
           ,'Remote Login Name' = sl.remote_name
           ,'RPC Out Enabled'    = case ss.is_rpc_out_enabled
                                   when 1 then 'True'
                                   else 'False'
                                   end
           ,'Data Access Enabled' = case ss.is_data_access_enabled
                                    when 1 then 'True'
                                    else 'False'
                                    end
           ,ss.modify_date
      FROM sys.Servers ss
 LEFT JOIN sys.linked_logins sl
        ON ss.server_id = sl.server_id
 LEFT JOIN sys.server_principals ssp
        ON ssp.principal_id = sl.local_principal_id

OUTPUT

 Note : SQL script will get the list of Linked server with its properties and its associated local/Remote login list.

SQL Script to Compare SQL Server Settings

How to Compare SQL Server Settings ?
This is pretty simple, we can run sp_configure on individual server and can do the comparison manually but what if we can have a script where we just need to supply the servername, that that script will automatically do a comparison and let you know the results.
At times while debugging an issue or setting up a environment we have to compare the server configurations across the SQL Servers. So this script made that pretty easy.

-- Enable Server Remote adhoc queries at server level
sp_configure 'show advanced options', 1
reconfigure
go
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure
go
-- End of Server settings

      declare @ServerNames VARCHAR(1000)
set @ServerNames = '<Enter Server Names here> and use comma (,)as server delimiter'

/*
Parameters
@ServerNames = The servers configurations to compared should be passed in the parameter as a comma seperated value as shown in the below usage.
               This parameter cannot be blank. 

Usage : set @ServerNames ='Server1,Server2,Server3'

*/

   SET NOCOUNT ON

   IF LTRIM(RTRIM(@ServerNames)) = ''
   BEGIN
        RAISERROR('ServerNames cannot be empty',16,1)
        RETURN
   END

    DECLARE @ServerTbl TABLE
    (
     ServerName SYSNAME
    ,LocalServer bit DEFAULT 0
    ,Processed   bit DEFAULT 0
    )

    DECLARE @SQL         VARCHAR(MAX)
    DECLARE @BaseSQL     VARCHAR(MAX)
    DECLARE @Server         SYSNAME
    DECLARE @LocalServer BIT
    DECLARE @PivotServer VARCHAR(4000)

    SELECT @SQL = 'SELECT ''' + REPLACE (@ServerNames,',',''' UNION SELECT ''') + ''''

    INSERT INTO @ServerTbl
    (ServerName)
    EXEC (@SQL)

    UPDATE s
       SET s.LocalServer = 1
      FROM @ServerTbl s
     WHERE S.ServerName = @@SERVERNAME

    IF OBJECT_ID('tempdb.dbo.##ServerConfigTmp') IS NOT NULL
        DROP TABLE ##ServerConfigTmp

    CREATE TABLE ##ServerConfigTmp
    (
     Rnk          VARCHAR(500)
    ,ServerName      SYSNAME
    ,ConfigName   VARCHAR(200)
    ,Description  VARCHAR(500)
    ,Minimum      VARCHAR(500)
    ,Maximum      VARCHAR(500)
    ,Value          VARCHAR(500)
    )

    WHILE EXISTS(SELECT 1 FROM @ServerTbl WHERE Processed = 0)
    BEGIN

       SELECT TOP 1 @Server = ServerName,@LocalServer=LocalServer
         FROM @ServerTbl
        WHERE Processed = 0
        ORDER BY LocalServer DESC

        PRINT REPLICATE ('-',200)
        PRINT 'Processing ' + @server
        PRINT REPLICATE ('-',200)

       SELECT @SQL = 'EXEC (''SELECT Rnk=''''8'''',Servername=''''' + @Server + ''''',Name,description,CONVERT(VARCHAR(500),minimum),CONVERT(VARCHAR(500),maximum),CONVERT(VARCHAR(500),value_in_use)  FROM master.SYS.configurations
                    UNION
                    SELECT ''''1'''',''''' + @Server + ''''',''''ServerName'''' ,''''Server Name'''' ,'''''''','''''''',CONVERT(VARCHAR(500),SERVERPROPERTY(''''ServerName''''))
                    UNION
                    SELECT ''''2'''',''''' + @Server + ''''',''''InstanceName'''' , ''''Instance Name'''' ,'''''''','''''''',CONVERT(VARCHAR(500),SERVERPROPERTY(''''InstanceName''''))
                    UNION
                    SELECT ''''3'''',''''' + @Server + ''''',''''ProductVersion'''' , ''''Product version'''' ,'''''''','''''''',CONVERT(VARCHAR(500),SERVERPROPERTY(''''productversion''''))
                    UNION
                    SELECT ''''4'''',''''' + @Server + ''''',''''ProductLevel'''' , ''''Product Level'''' ,'''''''','''''''',CONVERT(VARCHAR(500),SERVERPROPERTY(''''productlevel''''))
                    UNION
                    SELECT ''''5'''',''''' + @Server + ''''',''''Edition'''' , ''''Edition'''' ,'''''''','''''''',CONVERT(VARCHAR(500),SERVERPROPERTY(''''edition''''))
                    UNION
                    SELECT ''''6'''',''''' + @Server + ''''',''''MachineName'''' , ''''Machine Name'''' ,'''''''','''''''',CONVERT(VARCHAR(500),SERVERPROPERTY(''''MachineName''''))
                    UNION
                    SELECT ''''7'''',''''' + @Server + ''''',''''LicenseType'''' , ''''License Type'''' ,'''''''','''''''',CONVERT(VARCHAR(500),SERVERPROPERTY(''''LicenseType''''))
                      '') '

        DECLARE @OpenRowsetSQL VARCHAR(MAX)

        SELECT @OpenRowsetSQL = 'SELECT a.*
                                 FROM OPENROWSET(''SQLNCLI'', ''Server=' + @Server + ';Trusted_Connection=yes;'',
                                ''' + replace(@SQL,'''','''''') + ''') AS a;'

        INSERT INTO ##ServerConfigTmp
        (Rnk,ServerName,ConfigName,Description,Minimum,Maximum,Value)
        EXEC(@OpenRowsetSQL)

        UPDATE @ServerTbl
           SET Processed = 1
         WHERE ServerName = @Server

    END

    SELECT @PivotServer = ''

    SELECT @PivotServer = @PivotServer + QUOTENAME(ServerName) + ','
      FROM @ServerTbl
     ORDER BY LocalServer DESC

    SELECT @PivotServer = SUBSTRING(@PivotServer,1,LEN(@PivotServer)-1)

    SELECT @SQL ='SELECT *
                    FROM ( SELECT rnk,Servername,Configname,Description,Value
                             FROM ##ServerConfigTmp) src
                             PIVOT (MAX(Value) FOR Servername IN (' + @PivotServer + ')) AS pvt
                            ORDER BY Rnk'

      EXEC(@SQL)

GO

-- Disable Server Remote adhoc queries at server level
sp_configure 'Ad Hoc Distributed Queries', 0
go
reconfigure
go
sp_configure 'show advanced options', 0
reconfigure
-- End of Server settings

OUTPUT Screenshot

 

Note : NULL will be displayed if config is not available in the server, in the value field.

How to Give Read Definition Permission to a User for all Procedures and Functions

One of the developer complaint that he is getting a following error message while issuing sp_help, sp_helptext or using the object_definition() function to view the definition of object
EXEC sp_help Sales
Msg 15009, Level 16, State 1, Procedure sp_help, Line 66
The object ‘Sales’ does not exist in database ‘TEST’ or is invalid for this operation.
This error happens because user does not have permissions to the see the object metadata.  To resolve this issue, we need to explicitly give view definition permission to desired login.
The following SQL statement will built text, which you can select to execute to give permissions, to give view definition permission to a User for all Procedures and Functions.

SELECT 'grant VIEW DEFINITION on' + QUOTENAME(ROUTINE_SCHEMA) + '.' +
QUOTENAME(ROUTINE_NAME) + ' TO [<<user_name>>]' FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0
and ( ROUTINE_TYPE='PROCEDURE'  or ROUTINE_TYPE='function'  )

• INFORMATION_SCHEMA.ROUTINES gives all the information about procedures and functions on current database.
• OBJECTPROPERTY function returns property of the given objectid and propertyname.
• OBJECT_ID gives the object id of given object name.
• IsMSShipped property determines whether object created during installation of SQL Server. By checking IsMSShipped property to 0 reveals that object are created by user and not system objects.
• QUOTENAME returns a Unicode string with the delimiters added to make the input string a valid identifier which should used when contructing dynamic SQL as above from user input.

Or we can permit all users to have view definition permission by running the following code in a specific database

GRANT VIEW Definition TO PUBLIC

How to optimize SQL Server Performance Video

This video tutorial will help you to understand :

• Identify the root cause of bottlenecks that hinder performance of your SQL Server environment
• Diagnose and fix T-SQL errors in development before code goes into production
• Learn new best practices for project planning and formatting your code
• Ensure that your code is scalable, optimized and validated
• Resolve 60-80% of the performance bottlenecks attributed to poorly written SQL
  
  
  
  
  

How to Move SQL Server database files to other drive ?

This question is being asked by one of the training participants. As per the his scenario, Initially this database server was set up and all database data files are being hosted on drive G:\  and transaction log was on F:\.
Now the drive, F:\ is full and there are some IO contentions as well, so he wanted move a particular database from a existing drive to a new drive.
I told the steps but he asked me the step by step procedure, so I promised him that I am going to write a short note on this.
Moving databases could be required because of

• Running out of space in the current disk drive.
• Moving to a faster disk (e.g. moving from SATA to SSD).
• Replacing the current disk drive and many more

Here is step by step procedure, how you can move you database / database files from a exiting drive to a new drive.
Step 1. Run the following script to get the database file list

USE TEST;  -- please replace "TEST" with your database name  
    GO
    SELECT
        DB_NAME(database_id) AS Database_Name,
        name AS Logical_Name,
        physical_name AS Physical_Name,
        type_desc AS Type_Description
    FROM sys.master_files WHERE database_id = DB_ID()
    GO

Step 2. Run the following script to take the database offline

USE master;
    GO
    ALTER DATABASE TEST SET OFFLINE;   -- please replace "TEST" with your database name  
    GO

Step 3. Manually move the database file(s) listed in Step 1 to the new location
Step 4. Run the following script for each file that was moved

USE master;
    GO
    ALTER DATABASE TEST MODIFY FILE (NAME = TEST_Data, FILENAME = 'C:\newlocation\TEST_Data.mdf';  -- please replace AdventureWorks2008R2_Data with your database file name, and replace "C:\newlocation\TEST_Data.mdf" with the new file location in your environment.  
    GO 

Step 5. Run the following script to take the database online.

USE master;
    GO
    ALTER DATABASE TEST SET ONLINE;  -- please replace AdventureWorks2008R2 with your database name.  
    GO

Step 6. Run the script in Step 1 again to verify the database file list  

SQL Script to check space used by all log files

Following script helps us to get

1. Current total Log File size occupied by every database
2. How much space is free inside a log file

-- ***********************************************************************************/
-- Description  :    Script to check space used by all log files  
--                        space used for all log files
-- Compatibility  :     7.0+
-- This Script should be executed in master
-- ***********************************************************************************/
use master
go
Dbcc sqlperf(logspace)
go

SQL Script to Find SQL Server Error Log location

-- ******************************************** */
-- Description                                  */
-- ============                                 */
-- Script to find SQL Server Error Log file location
-- This Script will help you to find currently used SQL Server Error Log file.
-- =============================================*/
-- Compatibility  :     2000+                   */
-- ******************************************** */

SELECT SERVERPROPERTY('ErrorLogFileName')