Snapshot replication trick

Regarding how to select a proper replication type on SQL Server, many article gave many opinions, also tons of “best practice” may help as well. The official rules may be referenced to Microsoft MSDN: Selecting the appropriate type of replication

Recently we found an issue on our website, the web pages will be broken sometimes, it occur rarely but it did happen now and then which is annoying. By further investigation, I found the issue is due to replication, whenever the replication starting, some web pages which reference to the tables which involved in the replication will be broken. Our replication is snapshot replication, which I thought it should change to transactional replication anyway, just because some application needs to trigger the replication by clicking button.

However, when I troubleshooting the configuration of snapshot, just found under articles–>”Destination object” –> “Action if name is in use”, the default setting is “drop existing object and create a new one”, that’s the root reason of the problem. No surprise the web pages will broke as whenever the replication in progress, those tables are dropped, the query cannot refer to the tables and throw some wired errors. Also as this is not a heavy duty replication, so clients rarely meet this issue unless they just click the page at the replication time(5-10 seconds).

The solution is easy, just change the default setting to “Truncate all data in the existing object”

How to use cookie

In web application, sometimes, you have to use cookie no matter client cookie or server cookie to store values you want to save. I summarised as below.

Usage 1: Create per cookie per value
Initialize cookie:
HttpCookie cookie = new HttpCookie(“UserName”);
cookie.Value = “admin”;
Response.AppendCookie(cookie );

Retrieve cookie:
HttpCookie cookie = Request.Cookies(“UserName”);
cookieValue = cookie.Value;
Response.AppendCookie(cookie );

If add cookie from server side:
Response.Cookies.Add(new HttpCookie(“UserName”, “admin”)); //add particular cookie into cookie colliections
Retrieve cookie:
cookieValue = Response.Cookies[“UserName”].Value;

Usage 2: Add all values to one cookie object

HttpCookie cookie = Request.Cookies(“Default”);
cookie.Values.Add(“userid”, “1”); || cookie[“userid”] = “1”; //both syntax work
cookie.Values.Add(“username”, “admin”);
Response.AppendCookie(cookie);

Retrievment:
HttpCookie cookie = Request.Cookies(“Default”);
cookieValue = cookie.Values[“userid”];

Basic cookie operations: (create, add value, retrieve and delete)

1. js store cookie

<script type="javascript">
function setCookie(name,value){ //name is cookie's name, value is name's value
         var days = 10; //stored days and can passed by parameters
         var expires = new Date(); //create date variable
         expires.setTime(expires.getTime() + days * 30 * 24 * 60 * 60 * 1000); //expires time = current time + expired time (seconds)
         var str = name + '=' + value +';expires=' + expires.toGMTString(); //store value and expire date time to the cookie(need GMT format to display date string)
         var str = name + '=' + escape(value) +’;expires=’ + expires.toGMTString();                  document.cookie = str; }
</script>

The following is the generic cookie

<script type="text/javascript" language="JavaScript">//
function addCookie(name,value,expireHours){
           var cookieString=name+"="+escape(value);
           if(expireHours>0){ //check if setup the date time
           var date=new Date(); 
           date.setTime(date.getTime+expireHours*3600*1000); 
           cookieString=cookieString+"; expire="+date.toGMTString(); 
         } 
         document.cookie=cookieString; 
} 
</script>

Notes: Should use escape() function to encode, it could encode special characters to HEX, such as space will compiled to “20%” and able to save into cookie, nevertheless, it could avoid issues in multiple language environment
example:
document.cookie=”str=”+escape(“I love js”);
after encode:
document.cookie=”str=I%20love%20js”;
But after encode by escape(), the value needs to revoke unescape()to decode and retrieve the original cookie value

2. js retrieve cookie value

a.general loop characters retrieve

function getCookie(name){
var strcookie = document.cookie;// get cookie string
var arr = strcookie.split(‘;’); //split cookie
for(var i = 0;i&lt;arr.length;i++){
var arrStr = arr.split(‘=’); //split specific cookie
if(arrStr[0] == name) return arrStr[1]; //check if cookie name existing and output
}
return "";
}

b.use unescape to get cookie value

function getCookie(name){
var _password = document.getElementById(‘password’);
var arr = document.cookie.match(new RegExp("(^| )"+name+"=([^;]*)(;|$)")); //use regular expression to get cookie to be a name array
if(arr!=null){
return unescape(arr[2]); //
}
return ”;
}

3. delete cookie


<script type="text/javascript" language="JavaScript">
function deleteCookie(name){
            var date=new Date();
            date.setTime(date.getTime()-10000);
            document.cookie=name+"=v; expire="+date.toGMTString();  }  
</script>

the more details of cookie usage could be referenced by http://love2java.iteye.com/blog/208031

VBA execute stored procedure gave “Operation is not allowed when the object is closed”

Recently we have a requirement which use macro to print some recordset value from a database table. Basically the VBA code which reference to DB part in Word Macro is as below


    Dim strSQL As String
    Dim rs As ADODB.Recordset
    Dim totalcount As Integer

    strConn = "Provider=SQLOLEDB.1; Data Source= myserver\myinstance;Initial Catalog=mydb; Integrated Security=SSPI;"
    Set conn = New ADODB.Connection
    conn.Open strConn

    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = conn
    cmd.CommandText = "SpGetSomethingFromSomeDbTable"
    cmd.CommandType = adCmdStoredProc

    cmd.Parameters.Refresh
    cmd.Parameters(1).Value = CInt(divisionid)
    cmd.Parameters(2).Value = 1
    Set rs = cmd.Execute()

    totalcount = 0
    Do While Not rs.EOF
        ReDim Preserve membersFor(totalcount) As memberinfo
        membersFor(totalcount).field1 = RemoveQCInLastName(rs("fieldname1"))
        membersFor(totalcount).field2 = RemoveQCInLastName(rs("fieldname2"))
        membersFor(totalcount).field3 = RemoveQCInLastName(rs("fieldname3"))
        rs.MoveNext
        totalcount = totalcount + 1
    Loop

And part of the stored procedure is as below

if object_id('tempdb..#mytemptable') IS NOT NULL 
drop table #mytemptable

;with table1_CTE 
as(SELECT * FROM
	(SELECT *, ROW_NUMBER() OVER(PARTITION BY field1, field2 ORDER BY FieldDateTime DESC) AS rn
		FROM AnotherDatabase.dbo.table1 WHERE fieldDateTime < @specificdate) as T
 WHERE rn = 1  AND IsFlag = 1
 )
SELECT  m.field3, a.field4, a.field5
into #mytemptable
FROM table1_CTE  m JOIN someothertable a ON m.samefield = a.samefield
WHERE m.field1 = @currentvalue 

When I run macro, the error “Operation is not allowed when the object is closed” popup and stop at line
Set rs = cmd.Execute()

Actually this issue is not due to temp table or CTE table but the insert/delete/update DDL operation in stored procedure. The root reason is the count of the number of rows affected by those DDL statements will return as part of the result set. So the solution is easy and simple just need to add the following behind “AS” before the “BEGIN” in the stored procedure.

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

It took me 2 hours to figure out, hope it is helpful.

Command batch file can upload database backup file to ftp server

This script implement the following requirements.

1. Find files from specific folder and subfolder

2. Find files which has specific extension name

3. Filter the files modification date greater than yesterday

4. Filter the files by the file size greater than a specific size (10M etc.)

There are a few tricks for this script.

1. “Setlocal EnableDelayedExpansion”  this declaration is must be set part, as if you use loop in the batch, and the variable inside the loop will only keep the first round value or the last round value because batch variable didn’t get at run time, so if you want to get it at run time, the top words need to be declared, and when try to reference the variable, ensure use !myvariable! not %myvariable%. More details can reference to http://ss64.com/nt/delayedexpansion.html

2. How to get the file properties, reference the following
FOR %%W IN (file_to_be_queried) DO (
ECHO File Name Only : %%~nW
ECHO File Extension : %%~xW
ECHO Name in 8.3 notation : %%~snW
ECHO File Attributes : %%~aW
ECHO Located on Drive : %%~dW
ECHO File Size : %%~zW
ECHO Last-Modified Date : %%~tW
ECHO Parent Folder : %%~dpW
ECHO Fully Qualified Path : %%~fW
ECHO FQP in 8.3 notation : %%~sfW
ECHO Location in the PATH : %%~dp$PATH:W
)
more details can reference to http://www.robvanderwoude.com/batchfiles.php

3. Batch for ftp. In batch file to run ftp command, either use vbscript or run a ftp command file. My script use the second way, “>” will overwrite the whole file, and “>>” will append text in the last line, so the ftp command file will be overwritten every time the batch run and generate the latest.

4. Batch command use byte to calculate the file size, so if the file size is 100GB, that number would exceed the batch int limit and complain errors. So if it does need to reference the big file size number, you can divide the file size by 1024*1024, then it should reduce the number.

The following is the script .

@ECHO OFF
Setlocal EnableDelayedExpansion
SET constvalue=1000
SET /A FileSizeLimit=10
SET Filter=*.rar
SET StartFolder=c:\myfolder\test_code
ECHO Looking for files which size is less than %FileSizeLimit%M, file extention name is %Filter%, folder is %StartFolder%…..
SET FTPServer=ftpserver.mydomain.ca
SET FTPUserName=_myusername
SET FTPPassword=_mypassword
SET FTPPath=/ftprootfolder/subfolder1/subfolder2
SET FTPCommandFile=uploadcommand.ftp

SET m=%date:~-7,2%
SET /A m -= 1
SET yesterdaydate=%date:~-10,2%/%m%/%date:~-4,4%

ECHO %yesterdaydate%
ECHO %FTPUserName%> %FTPCommandFile%
ECHO %FTPPassword%>> %FTPCommandFile%
ECHO binary >> %FTPCommandFile%
ECHO cd %FTPPath% >> %FTPCommandFile%

FOR /R “%StartFolder%” %%F IN (%Filter%) DO (

SET /a realsize=%%~zF
SET /a filesizeinMB = !realsize! / %constvalue% / %constvalue%
ECHO filesize in MB: !filesizeinMB!
SET cfile=%%F
ECHO file name is: %%F
IF !filesizeinMB! GTR %FileSizeLimit% (
FOR %%p IN (%%F) DO (
SET filedatetime=%%~tp
IF !filedatetime! GEQ %yesterdaydate% (
ECHO put %%F >> %FTPCommandFile%
)
)
)
)

ECHO close >> %FTPCommandFile%
ECHO bye >> %FTPCommandFile%

FTP -d -i -s:%FTPCommandFile% %FTPServer%

SQL Server Transaction Logs Shrink

As a DBA, I watched our database transaction log file, sometimes it grows really crazy and occupied even the full hard drive.  How to resolve this issue.

1. Check if there is a effective transaction log backup.

We all know that there are two database backup mode, full recovery mode and simple mode. Under Full mode, the transaction log won’t shrink till there is transaction log backup happens. But there is a trick, If you just switch into Full Recovery mode, but never take an initial Full Backup, SQL Server will not honor your request to be in Full Recovery model. Your transaction log will continue to operate as it has in simple until you switch to Full Recovery Model AND Take your first Full Backup.

2. Other concerns my cause the trans log keep growing

  • Uncommitted Transactions
  • Extremely Large Transactions
  • Operations: DBCC DBREINDEX and CREATE INDEX
  • While Restoring from Transaction Log Backups
  • Client Applications Do Not Process All Results
  • Queries Time Out Before a Transaction Log Completes the Expansion and You Receive False ‘Log Full’ Error Messages
  • Un-replicated Transactions

3. If it still doesn’t work

There are maybe two solutions you can consider.

a. setup a limit of the transaction log size

b. force the log file to shrink either manually or by script/schedule job

Back up your database! 

Execute the following, substituting <log_file_name_Log> with the appropriate logical name of the database log file, no quotes needed:

  1. DBCC SHRINKFILE(<log_file_name_Log>)
  2. BACKUP LOG <database> WITH TRUNCATE_ONLY
  3. DBCC SHRINKFILE(<log_file_name_Log>)

Afterwords, perform a full backup of the database.

The file should shrink to a ridiculously small shadow of its former self, most likely it could be 1MB.

Recommend an article for T-SQL ranking usage

Just saw an article explain the t-sql ranking usage very clearly, it is written by famous Pinal Dave, the author of http://blog.sqlauthority.com

SQL SERVER – 2005 – Sample Example of RANKING Functions – ROW_NUMBER, RANK, DENSE_RANK, NTILE

Another article also explains one of the new feature of SQL 2008 R2–>group sets

http://www.grapefruitmoon.net/diving-into-t-sql-grouping-sets/

 

SSRS report url examples

Recently, one report project required to use url to show a SSRS report directly from a third party application, it may need to open a pdf directly, or hide the toolbar, parameter etc.

PDF version:
http://Server/ReportServer/Pages/ReportViewer.aspx?/My Reports Folder/My report&parameter1=5&parameter2=2002/01/01&rs:Command=Render&rs:Format=pdf

Report open without toolbar and parameter
http://Server/ReportServer/Pages/ReportViewer.aspx?/My Reports Folder/My report&parameter1=5&parameter2=2002/01/01&rs:Command=Render&rs:Command=Render&rc:Toolbar=False&rc:Parameters=false