microsoft.ace.oledb.12.0 is not registered in 64 bit windows OS

When exe is giving error in 64bit windows OS. we should rebuild the exe targeting 32bit OS.
see the attachment

Read More

Could not load file or assembly 'System.Data.SQLite' or one of its dependencies. An attempt was made to load a program with an incorrect format.

IIS ERROR:

If above type of error occur then there is one solution mentioned below

Reason could be 64 bit IIS.

turn the Application pool to 32 bit may solve your problem

Read More

Downloading Excel From Web Application Open Blank

Excel Downloading From any Web Application which is Opening Blank

I Think This Issue is Happening in Mostly in Office 2010 Or Above 

Actually , Problem in Protected View..

Sometimes You can see when you Open excel Protected View...... Is display

Let See How we Can Solve ...

There is Some Setting We Have to do in excel

Uncheck Enable Protected View for Files Originating From internet

Read More

How to SQL Server database AutoBackup and Zip or .Rar (.bak) File and Transfer using FTP?

You have to follow these three steps to back up your SQL Server databases by using Windows Task Scheduler:

Step A: Use SQL Server Management Studio Express or Sqlcmd to create the following stored procedure in your master database:

USE [master] 

GO 

/****** Object:  StoredProcedure [dbo].[sp_BackupDatabases] ******/ 

SET ANSI_NULLS ON 

GO 

SET QUOTED_IDENTIFIER ON 

GO 

-- ============================================= 

-- Author: Microsoft 

-- Create date: 2010-02-06

-- Description: Backup Databases for SQLExpress

-- Parameter1: databaseName 

-- Parameter2: backupType F=full, D=differential, L=log

-- Parameter3: backup file location

-- =============================================

CREATE PROCEDURE [dbo].[sp_BackupDatabases]  

            @databaseName sysname = null,

            @backupType CHAR(1),

            @backupLocation nvarchar(200) 

AS 

       SET NOCOUNT ON; 

           

            DECLARE @DBs TABLE

            (

                  ID int IDENTITY PRIMARY KEY,

                  DBNAME nvarchar(500)

            )

           

             -- Pick out only databases which are online in case ALL databases are chosen to be backed up

             -- If specific database is chosen to be backed up only pick that out from @DBs

            INSERT INTO @DBs (DBNAME)

            SELECT Name FROM master.sys.databases

            where state=0

            AND name=@DatabaseName

            OR @DatabaseName IS NULL

            ORDER BY Name

           

            -- Filter out databases which do not need to backed up

            IF @backupType='F'

                  BEGIN

                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','AdventureWorks')

                  END

            ELSE IF @backupType='D'

                  BEGIN

                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')

                  END

            ELSE IF @backupType='L'

                  BEGIN

                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')

                  END

            ELSE

                  BEGIN

                  RETURN

                  END

           

            -- Declare variables

            DECLARE @BackupName varchar(100)

            DECLARE @BackupFile varchar(100)

            DECLARE @DBNAME varchar(300)

            DECLARE @sqlCommand NVARCHAR(1000) 

        DECLARE @dateTime NVARCHAR(20)

            DECLARE @Loop int                  

                       

            -- Loop through the databases one by one

            SELECT @Loop = min(ID) FROM @DBs

      WHILE @Loop IS NOT NULL

      BEGIN

-- Database Names have to be in [dbname] format since some have - or _ in their name

      SET @DBNAME = '['+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+']'

-- Set the current date and time n yyyyhhmmss format

      SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') + '_' + REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')  

-- Create backup filename in path\filename.extension format for full,diff and log backups

      IF @backupType = 'F'

            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_FULL_'+ @dateTime+ '.BAK'

      ELSE IF @backupType = 'D'

            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_DIFF_'+ @dateTime+ '.BAK'

      ELSE IF @backupType = 'L'

            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_LOG_'+ @dateTime+ '.TRN'

-- Provide the backup a name for storing in the media

      IF @backupType = 'F'

            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' full backup for '+ @dateTime

      IF @backupType = 'D'

            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' differential backup for '+ @dateTime

      IF @backupType = 'L'

            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' log backup for '+ @dateTime

-- Generate the dynamic SQL command to be executed

       IF @backupType = 'F' 

                  BEGIN

               SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'

                  END

       IF @backupType = 'D'

                  BEGIN

               SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH DIFFERENTIAL, INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'        

                  END

       IF @backupType = 'L' 

                  BEGIN

               SET @sqlCommand = 'BACKUP LOG ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'        

                  END

-- Execute the generated SQL command

       EXEC(@sqlCommand)

-- Goto the next database

SELECT @Loop = min(ID) FROM @DBs where ID>@Loop

END

Step B: In a text editor, create a batch file that is named Sqlbackup.bat, and then copy the text from one of the 

following examples into that file, depending on your scenario:

Example1: Full backups of all databases in the local named instance of SQLEXPRESS by using Windows Authentication

// Sqlbackup.bat

 sqlcmd -S .\EXPRESS –E -Q "EXEC sp_BackupDatabases
 @backupLocation='D:\SQLBackups\', @backupType='F'" 

 Example2: Differential backups of all databases in the local named instance of SQLEXPRESS by using a SQLLogin and its password

// Sqlbackup.bat

sqlcmd -U SQLLogin -P password -S .\SQLEXPRESS -Q "EXEC sp_BackupDatabases  @backupLocation ='D:\SQLBackups', @BackupType=’D’"

 Note: The SQLLogin shouldhave at least the Backup Operator role in SQL Server.

Example 3: Log backups of all databases in local named instance of SQLEXPRESS by using Windows Authentication

 // Sqlbackup.bat

sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\',@backupType='L'"

Example 4: Full backups of the database USERDB in the local named instance of SQLEXPRESS by using Windows Authentication

// Sqlbackup.bat

 sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @databaseName=’USERDB’, @backupType='F'"

Similarly, you can make a differential backup of USERDB by pasting in 'D' for the @backupType parameter and a log backup of USERDB by pasting in 'L' for the @backupType parameter.

Step c: Set .Rar or .zip File Name as Current Date


for /F "tokens=1-4 delims=/ " %%A in ('date/t') do (
set DateDay=%%C
set DateMonth=%%B
set DateYear=%%D
)

set CurrentDate=%DateDay%-%DateMonth%-%DateYear%

step D: Create .Rar File


set path="C:\Program Files (x86)\WinRAR\";%path%

winrar.exe a -ep1 C:\backup\%CurrentDate%.rar C:\backup\


Step E:Transfer .Rar File using FTP


echo user Username>> ftpcmd.dat
echo Password>> ftpcmd.dat
echo cd backup_Folder>> ftpcmd.dat
echo put "C:\backup\*.rar">> ftpcmd.dat
echo quit>> ftpcmd.dat
ftp -n -s:ftpcmd.dat  255.255.255.255


Example:Full Script


DEL /F /S /Q /A "C:\backup\*.bak"
DEL /F /S /Q /A "C:\backup\*.rar"

sqlcmd -S  Server\SQLEXPRESS -U Sa -P server -Q "EXEC sp_BackupDatabases @backupLocation='C:\backup\', @backupType='F'"

@REM ------- BEGIN xpi.bat ----------------
@setlocal
@echo off


for /F "tokens=1-4 delims=/ " %%A in ('date/t') do (
set DateDay=%%C
set DateMonth=%%B
set DateYear=%%D
)

set CurrentDate=%DateDay%-%DateMonth%-%DateYear%

set path="C:\Program Files (x86)\WinRAR\";%path%

winrar.exe a -ep1 C:\backup\%CurrentDate%.rar C:\backup\

echo user Username>> ftpcmd.dat
echo Password>> ftpcmd.dat
echo cd backup_Folder>> ftpcmd.dat
echo put "C:\backup\*.rar">> ftpcmd.dat
echo quit>> ftpcmd.dat
ftp -n -s:ftpcmd.dat  255.255.255.255


REM ------- END xpi.bat ------------------

Read More

How to Increasing “MaxStringContentLength” for connecting to webservice(WCF) ?

Today I discovered the some configuration impacts from my choice to return the XML document as a string property.  Because I am using a string property to return the XML generated by my service. I had to adjust three properties in the service and client configuration.

MaxBufferSize property - (From MSDN) Gets or sets the maximum size of the buffer to use. For buffered messages this value is the same as MaxReceivedMessageSize. For streamed messages, this value is the maximum size of the SOAP headers, which must be read in buffered mode.

MaxReceivedMessageSize must also match what you put in the MaxBufferSize.  Default is 65536.

MaxStringContentLength - (From MSDN) Gets and sets the maximum string length returned by the reader. Default is 8192.

for example:

System.ServiceModel.BasicHttpBinding ad = new System.ServiceModel.BasicHttpBinding();
            ad.ReaderQuotas.MaxStringContentLength = 2147483647;
            ad.MaxReceivedMessageSize = 2147483647;
            ad.MaxBufferSize = 2147483647;
        

  XYZervices.XYZServiceClient proxy = new XYZServices.XYZServiceClient(ad, new
System.ServiceModel.EndpointAddress("http://localhost:58297/XYZService.svc"));

            var Data = proxy.GetBookingData("ABX", "TTT", 900);

Read More

How To split string in Sql Server?

Split String Function

Sometime we need to split string in sql  server like Comma sparated(,) or colon(:) or dot(.) or (#)

For examples:


declare @Pname Nvarchar(MAX)
SET @Pname ='XZY,TTT,YYY,UUU'

select *
    FROM [dbo].[SplitString] (@Pname,',')

You can Use Following Function to Split Sting in Sql Sever


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
ALTER Function [dbo].[SplitString](@text varchar(8000), @delimiter varchar(1) = ',')
-- This function splits a string of CSV values and creates a table variable with the values.
-- Returns the table variable that it creates
RETURNS @Strings TABLE
(
    position int IDENTITY PRIMARY KEY,
    member_id varchar(8000)
)

AS

BEGIN
    Declare @index int  
    Set @index = -1  
 
    WHILE (LEN(@text) > 0)  
       BEGIN
        SET @index = CHARINDEX(@delimiter , @text)
        IF (@index = 0) AND (LEN(@text) > 0)
               BEGIN
               INSERT INTO @Strings VALUES (@text)
            BREAK
           END
 
        IF (@index > 1)
                 BEGIN
            INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
            SET @text = RIGHT(@text, (LEN(@text) - @index))
           END
            ELSE  
            SET @text = RIGHT(@text, (LEN(@text) - @index))
 
       END  
    RETURN
END

Read More

Closing Stock Store Procedure

Friends Today i share With You Closing Stock Report Example


ALTER PROCEDURE [dbo].[ClosingStock]

      -- Add the parameters for the stored procedure here

      @startdate datetime,

      @enddate datetime,

      @than nvarchar(1)='<'

AS

BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from

      select itemname ,opening,inwardqty,outwardqty ,

(isnull(opening,0) + isnull(inwardqty,0) - isnull(outwardqty,0)) as closing

from (

select m.Itemname as itemname,sum(isnull(openinward,0)-isnull(openoutward,0)) as opening,

isnull((inwardqty),0)as inwardqty,isnull((outwardqty),0)as outwardqty

from itemmaster m

--------Opening---------------------------------------

left outer join(select id.itemid, isnull(sum(id.qty),0) as openinward  from inwarddetail id

left outer join inward i on i.inwardid = id.inwardid

where  inwarddate < @startdate

 group by itemid) k

on m.itemid = k.itemid left outer join

( select od.itemid, isnull(sum(od.qty),0) as openoutward  from outwarddetail od

left outer join outward i on i.outwardid = od.outwardid

where  outwarddate < @startdate

 group by itemid

) oo

on  m.itemid = oo.itemid

-----------Inward qty---------------------------------------

left outer join(

select itemid, sum(qty) as inwardqty  from inwarddetail id

left outer join inward i on i.inwardid = id.inwardid

where inwarddate >= @startdate  and inwarddate <= @enddate

 group by itemid

) i

 on m.itemid = i.itemid

------------------Outward qty--------------------------------

left outer join( select itemid, sum(qty) as outwardqty  from outwarddetail od

left outer join outward i on i.outwardid = od.outwardid

where outwarddate >= @startdate and outwarddate <= @enddate 

 group by itemid

) o

on  m.itemid = o.itemid

group by m.Itemname,inwardqty,outwardqty ) t

where

(case when @than ='<' Then (isnull(opening,0) + isnull(inwardqty,0) - isnull(outwardqty,0))End) <= 0 

or

(case when @than ='>' Then (isnull(opening,0) + isnull(inwardqty,0) - isnull(outwardqty,0))End) > 0

End

Read More