How to create Batch file to execute sql scripts ?

Question :

Hi friends,  

      i want to create batch file to execute all my .sql scripts.

I have all table ( all table scripts in single file ) ,Udds ( all udds in single file ) ,Stored procedures( separate file for each SPs ),Functions ( Separate file for each Functions ),Triggers and views scripts in .SQL file.  

can anybody tell me how to create batch file for executing all these scripts in sql server ?.  

   while executing, it should ask Database name,server name, password. if these details are given then it should execute my all scripts in given database

, if any error thrown then that error and procedure name alone have to move to separate log file..

Answer :  

1. Open New notepad 

2. Paste the below code and change content as per your requirement

@echo off
cls

echo *******************************************************************************
echo *                     DATABASE DEPLOYMENT                    *
echo *******************************************************************************
echo *                     WARNINGS                                        *       
echo *******************************************************************************
echo *  1. You can give some warnings like take backup before executing             *
echo *******************************************************************************


set /p SName=Server Name :
set /p UName=User Name :
set /p Pwd=Password :
set /p DbName=Database Name :


set /p choice=ARE YOU SURE TO EXECUTE SCRIPTS in %DbName% (y/n) ?

if '%choice%'=='y' goto begin
goto end

:begin
if exist _Deploy.txt del _Deploy.txt

@echo on



@echo UDDs >>_Deploy.txt
@echo ******************* >>_Deploy.txt
sqlcmd -S %SName% -U %UName% -P %Pwd% -d %DbName% -I -i "UDDs"\UDDs.sql >> _Deploy.txt 2>&1


@echo TABLES >>_Deploy.txt
@echo ******************* >>_Deploy.txt
sqlcmd -S %SName% -U %UName% -P %Pwd% -d %DbName% -I -i "Tables"\TABLE_SCRIPT.sql >> _Deploy.txt 2>&1
sqlcmd -S %SName% -U %UName% -P %Pwd% -d %DbName% -I -i "Tables"\Insert_Script.sql >> _Deploy.txt 2>&1


@echo FUNCTIONS >>_Deploy.txt
@echo ******************* >>_Deploy.txt
sqlcmd -S %SName% -U %UName% -P %Pwd% -d %DbName% -I -i "Functions"\TEST_FN.sql >> _Deploy.txt 2>&1
sqlcmd -S %SName% -U %UName% -P %Pwd% -d %DbName% -I -i "Functions"\TEST1_FN.sql >> _Deploy.txt 2>&1


@echo STORED PROCEDURES >>_Deploy.txt
@echo ***************** >>_Deploy.txt
sqlcmd -S %SName% -U %UName% -P %Pwd% -d %DbName% -I -i "Stored Procedures"\CheckInternalmachineType_SP.sql >> _Deploy.txt 2>&1
sqlcmd -S %SName% -U %UName% -P %Pwd% -d %DbName% -I -i "Stored Procedures"\CheckLineType_SP.sql >> _Deploy.txt 2>&1


@echo TRIGGERS >>_Deploy.txt
@echo ***************** >>_Deploy.txt
sqlcmd -S %SName% -U %UName% -P %Pwd% -d %DbName% -I -i "Triggers"\Master_TR.sql >> _Deploy.txt 2>&1
sqlcmd -S %SName% -U %UName% -P %Pwd% -d %DbName% -I -i "Triggers"\Price_Master_TR.sql >> _Deploy.txt 2>&1


@echo VIEWS >>_Deploy.txt
@echo ***************** >>_Deploy.txt
sqlcmd -S %SName% -U %UName% -P %Pwd% -d %DbName% -I -i "Views"\Gate_Pass_Rpt_VW.sql >> _Deploy.txt 2>&1
sqlcmd -S %SName% -U %UName% -P %Pwd% -d %DbName% -I -i "Views"\GatePass_VW.sql >> _Deploy.txt 2>&1

@notepad _Deploy.txt

:end

 

 3. then Save this file as filename.bat

4. now execute this by giving correct server details . if any error then it would write in _Deploy.txt.

 

How to restore the database through Query

How to restore the SQL SERVER database through Query ?

Question :


Hi,
     Everybody knows that restoring the database from backup file through sql server restore wizard.  but sometimes we may get error while restoring through sql server restore wizard like 'failed,Reached the end of the file'.

Now you can get success restore by restoring through query.


Answer : 

Restore the database through query by following,


1. JUST TO GET HEADER DETAILS

    RESTORE HEADERONLY
    FROM DISK = N'D:\DB\SPICE.bak'
    WITH NOUNLOAD;
    GO

2. TO KNOW THAT BACKUP FILE IS CORRUPTED

    RESTORE VERIFYONLY
    FROM DISK = N'D:\DB\SPICE.bak'
    GO

3. EXECUTE THIS AND YOU WILL GET LOGICAL NAME FOR TYPE D AND L (i,e DATA FILE AND LOGICAL FILE )

    RESTORE FILELISTONLY
    FROM DISK = N'D:\DB\SPICE.bak'


4. TO RESTORE BAK FILE IN NEW DATABASE

    RESTORE DATABASE NEWDB
    FROM DISK = N'D:\DB\SPICE.bak'
    WITH REPLACE ,
    MOVE 'MDF_FILENAME'  
-- SPECIFY LOGICAL NAME OF DATAFILE HERE FROM THE ABOVE FETCH  (ie where type = D)
    TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Spice.mdf',     -- SPECIFY NEW DATAFILE NAME
    MOVE 'LDF_FILENAME'   
-- SPECIFY LOGICAL NAME OF LOGICALFILE FROM THE ABOVE FETCH   (ie where type = L)
    TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Spice_log.ldf'     -- SPECIFY NEW LOGICALFILE NAME
   
   
5. Now you will get success restore message.


    

Converting Timestamp value to string in SQL Server

Converting Timestamp value to string/varchar in SQL Server


Question :

   How to Convert SQL Server Timestamp Column to VARCHAR ?


Answer : 

SELECT CONVERT(VARCHAR(MAX), CONVERT(VARBINARY,Timestamp_Column_Name), 1) FROM Table 

How to Alter View / Table Function dynamically in sql server

Question :

Hi friends,

[ SQL SERVER 2008 ]

 I'm using UDDs ( User Defined DataType ) for my tables and created lot of views. Same UDD would refer muliple tables..

Now i changed my UDD in table by increasing size but its not reflecting in View.  if i alter that view then its reflecting..

so altering each view and table function manually is very difficult..

Answer :

              EXEC sp_refreshsqlmodule @View_Name/@Function_Name 

 

Getting Calling Class name in called class in .NET

Getting Calling Class name in called class in .NET

Question :

  I want to get the calling class name from Called class.

here calling class is Form1 and Called Class is Class1. 

I'm calling Test() function of Class1 from Form1, now in test() function i want to get the calling function that is Form1 .

Important thing is that I want to get without passing any parameters that is i don't want to change any signature of the function because its already completed projects,so this function has lot of references. so i want to get calling class name without changing / adding additional parameters..

ex :

Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim cls As New Class1
        cls.test()
    End Sub
End Class

Public Class Class1
    Public Sub test()
        '' HERE I WANT TO GET CALLING CLASS NAME i.e Form1
    End Sub
End Class

Answer :

Public Class Class1
    Public Sub test()
        '' HERE I WANT TO GET CALLING CLASS NAME i.e Form1

    Dim stackTractObject As New StackTrace

    Msgbox(stackTractObject.GetFrame(1).GetMethod.ReflectedType.Name)

    End Sub
End Class

Reading Data Byte wise from File in .Net

Reading Data Byte wise from File in .Net

Question :

Actually i have one Import screen in my application which has to read data from .txt file.. .txt data like this..
あ絵ddえｂだあ   . . . .            ( which is Row 1 )
.
.
.
 and Client has given one structure to read that txt file that is
----------------------------------------------------------------------------------
Fields Name            Starting Byte Position                        Byte Length
----------------------------------------------------------------------------------
CustCode               1                                                     4
Part No                   5                                                    10
likewise...
so here.. first 4 bytes are customer code and next 10 bytes are part no..
if i split this by character which will be wrong..
Example :         CustCode     will be     "あ絵dd"
                        Part No        will be    "えｂだあ"
which is completely wrong...
but i want result as
                       CustCode     will be     "あ絵"
                        Part No        will be    "ddえｂだあ"
because characters used in English are considered as 1 byte and characters used in chinese or japanese  are considered as 2 byte.
This is what result i want..

Answer :

Dim Sr As New System.IO.StreamReader(FileName)
While Not Sr.EndOfStream
    Dim LineValue = Sr.ReadLine

    Dim _CustCode=GetStringByBytesLineValue,1,4)
    Dim _PartNo=GetStringByBytesLineValue,5,10)
End
Sr.Close()
Sr.Dispose()

Public Function GetStringByBytes(ByVal pSourceValue As String, ByVal pStartingBytePos As Integer, ByVal pByteLength As Integer) As String
            Dim _DesArr(pByteLength - 1) As Byte
            Dim _SplitedString As String

            Dim _byt() = GetBytes(pSourceValue)
            System.Array.Copy(_byt, pStartingBytePos, _DesArr, 0, pByteLength)
            _SplitedString = GetString(_DesArr)
            Return _SplitedString
End Function

Public Function GetBytes(ByVal pSourceValue As String) As Byte()
            Dim utfobj = System.Text.Encoding.GetEncoding("GB18030")   // Based on Language this Encoding Format would change
            Return utfobj.GetBytes(pSourceValue)
End Function

Public Function GetString(ByVal pByteValue() As Byte) As String
            Dim utfobj = System.Text.Encoding.GetEncoding("GB18030")    // Based on Language this Encoding Format would change
            Return utfobj.GetString(pByteValue)
End Function