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

Advertisements

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 .
 
 
 
 
 
 
 
 
 

Error:”Saving changes is not permitted” on SQL Server 2008

I was using sql server 2008 first time . I have created my database table in it and loked fine . Aftrer two days I needed to add some columns and change some of the properties in the tables I have already created . As usual I have done the changes and click on save table button…

ooopppss!!!

I get an error message “Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created

Possible reasons for this error :

  • You change the Allow Nullssetting for a column.
  • You reorder columns in the table.
  • You change the column data type.
  • You add a new column

(Refernce from MSDN)

reference

After analyzing I have found a fix for it :

  1. Open SQL Server Management Studio (SSMS).
  2. On the Tools menu, click Options.
  3. In the navigation pane of the Options window, click Designers.
  4. Select or clear the Prevent saving changes that require the table re-creation check box, and then click OK.

Note : In general you should keep this checkbox  checked . Alter changes via script.

 

Common mistakes done while using nhibernate

I was reading  a “nhibernate in Action” and found some interesting things. I will post more  from this book once I am through .

Common mistakes done while using nhibernate

If you read this section, you may just have receive one of these performance complains we talked
about. Here are some common mistakes you may have done and tips to help dramatically improve
your application’s performance (and make the end-users happier).


By the way, you should consider writing performance tests at an early stage to avoid waiting for the
end-user to tell you that your application is too slow. And don’t forget to also optimize your database
(adding the correct indexes, etc.).

A mistake that some new NHibernate developers commit is that they create the session factory
more than required. This is a very expensive process. Most of the time, it is done once at the start of
the application. Avoid keeping the session factory at a place that lives shorter than your application
(like keeping it in a web page request).

Another common mistake, related to the fact that NHibernate makes it so easy to load entities, is
that you may load more information than you need (without even knowing it). For example,
associations and collections are fully initialized when lazy loading is not enabled. So even when
loading a single entity, you may end fetching a whole object graph. The general advice here is to
always enable lazy loading and to carefully write your queries.

Another related problem, arising when enabling lazy loading, is the n+1 select problem. For more
details, read chapter 8, section 8.6.1, “Solving the n+1 selects problem”. By the way, a nice way to
spot this issue early is to measure the number of queries executed per page; you can easily achieve
that by writing a tool to watch logs from NHibernate.SQL at DEBUG level. If it is higher than a certain
limit, you have got a problem to solve and do it immediately, before you forget what is going on in
this page.
You may also measure other performance-killer operations (like the number of remote calls
per page) and global performance information (like the time it takes to process each page).
You should also try to load the information you need using the minimal number of queries
(however, avoid expensive queries like those involving Cartesian product). Note that it is generally
more important to minimize the number of entities loaded (row count) than the number of fields
loaded for each entity (column count).

Chapter 8 describes many features that can help you writing optimized queries.
Now, let’s talk about a less known issue. This one is related to the way NHibernate works. When
you load entities, the NHibernate session keeps a number of information about them (for transparent
persistence, dirty checking, etc.). And when committing/flushing, the session uses this information to
perform the required operations.

There is a specific situation where this process can be a performance bottleneck: When you load a
lot of entities to update only few of them, this process will be slower than it should be. The reason is
that the session will check all these entities to find those that must be updated. You should help it
avoiding this waste by evicting unchanged entities or using another session to save changed entities.
As a last resort, consider using the 2nd level cache (and the query cache) to hit the database less
often and reuse previous results. Read chapter 6, section 6.3, “Caching theory and practice”, for more details on the pros and cons of this feature.

You can download this book from here