Paging in SQL Server

declare @pagesize int
set @pagesize =25

declare @pagenumber int
set @pagenumber =2

declare @from int
set @from = (@pagenumber-1)*@pagesize+1
declare @to int
set @to = @from+@pagesize-1

select @from ,@to
SELECT * FROM ( SELECT * from Employee_tbl ,ROW_NUMBER() OVER (ORDER BY EmployeeID) as rownum FROM Employee_tbl )
seq WHERE seq.rownum BETWEEN @from AND @to ORDER BY seq.rownum

Advertisements

SQL SERVER – Create Script to Copy Database Schema and All The Objects – Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects

After reading a post from Pinal I am proposing another solution here to automate SQL backup infrastructure. The dditional advantage here in this approach is , the SQL objects are always created in a seperate file so this can be really helpful to compare your chages in DB from your last version.

Steps :

1. Get file 0g_SqlExtract.vbs  or from attachement to this post.

2. Create a small batch file to call this script. Its handy to call it via batch file I guess. Ofcourseyou can use it   directly.

3. Pass the required parameter to the batch file

SET SERVER=%1
SET DB=%2
SET OUTPUT_FOLDER=%3
SET LOGIN=%4
SET PASSWORD=%5

cscript 0g_sqlextract.vbs %SERVER% %DB% %OUTPUT_FOLDER% %LOGIN% %PASSWORD%

4. Execute your batch file .

 

Download scripts from here

Thats it.

Have a great day

Nhibernate error :Incorrect syntax near the keyword ‘Function’.

In our database sometimes we have column names which are actually the database keywords . If we want to access such columns from Nhibernate session  , it throws error message “Incorrect syntax near the word :’SOME DB KEYWORD’

 

Mapping :Before :

Mapping :After

Keeping database base reserve keyword in “[ ]”  brackets will fix the error message.

 

You can have look on all my previous Nhibernate posts

 

 

 

 

 

 

 

 

 

 

 

Automate SQL Server operations by batch file

Many times we need to perform many repetitive actions on our sql server . Many of those are literaaly repetitve or many of them we really do not do every day but whenever it is , it a collection of steps .

Today we will see how easily we can automate our repetitive work by batch file so that we always donot have to open SQL Server Management Studio and conect to our DB etc….

 

You can also have a look how to create a log file from batch file in my previous post

Create a batch file :

1. Open your favourite text edidor and write below lines :

 @echo off
SET SERVER=YOURSERVERNAME
SET DB=YOURDATABSENAME

SET Logfile=runscripts.log
SET InvokeSQL=sqlcmd -S %SERVER% -d %DB%  -E -b -i
echo %DATE% %TIME% %USERNAME% > %Logfile%
echo calling sql-scripts with: %InvokeSQL% %%scriptfile%% >> %Logfile%
echo. >> %Logfile%
REM START trace32.exe %Logfile%
echo.
CALL :RUNProgram 001.sql || GOTO :FAILED
CALL :RUNProgram 002.sql || GOTO :FAILED
echo no errors found – logfile = %Logfile%
GOTO :EOF
:RUNProgram
echo running %1%
echo.>> %Logfile%
echo ===================   running %1%     %     %TIME%  =======================%  %>> %Logfile%
%InvokeSQL% %1% %>> %Logfile% 2>&1
GOTO :EOF
:FAILED
echo errors found – logfile = %Logfile%

2. Save file as batch file “myFirstSQLBatch.cmd”

3. Run it !!!!

Thats it !!!

Generic Permission/Security Model

permission Model
permission Model
In most of the application , we need permission model.
Here is a simple generic model where action level permissions can be handled easily .
 
The database design is self explainatory . But ofcourse let me know if someone need more help on it .
 
Suggestions are more welcomed to add improvements in this approach.
 
🙂
Take care .