|
Blog > Databases June 07, 2010
Posted on June 07, 10 @ 02:02 am under Databases and has no comments.
Working in PHP frameworks is very fun and interesting. Just define controller, view and woooo.. page appears. URL rewriting is also very easy. After reading reviews on Kohana, I want to give a try to my website. Let me see how well I can use Kohana in improving my website. 
Renaming a database in sql server
Posted on September 04, 08 @ 09:34 pm under Databases and has no comments.
The following article shows how to rename the database in sql server as well as address the issues that arise while renaming.
http://www.sqlservercentral.com/articles/Administration/63891/
Resetting Identity column value in SQL Server table
Posted on June 04, 08 @ 03:28 am under Databases and has no comments.
To reset identity column value for a table in the SQL Server, execute the folllowing query.
DBCC CHECKIDENT('<TABLE_TO_RESET>', RESEED, 0)
This will set the Identity back to 0, and the next record getting inserted will start from 1 and proceeds to 2, 3, 4 .. and so on.
Error logging in SQL Server
Posted on December 04, 07 @ 04:27 am under Databases and has no comments.
Error Logging in SQL Server procedures
SQL server offers a programming base, called Stored Procedures, where in you can execute a sequence. But some times, there will be errors when executing statements in bulk. So we need to track those errors too.
Error logging involves 3 steps:
- Creating a table to log errors (one time activity)
- Creating a seperate procedure to log necessary information
- Using that procedure, at appropriate stages, by passing necessary parameters
———————————
Creating a table to log errors
——————————–
Let us first figure out the data we needed to track errors. We need to know the name of the Procedure; in that, the sql statement which gave the error; the error code; the message related to the error code; and time at which the error occured. So our database table can be
CREATE TABLE [Error_Log](
[Record] [bigint] IDENTITY(1,1) NOT NULL, –for serial numbers
[Error_Object] [varchar](50), –the name of the stored procedure
[Execution_Pointer] [varchar](100) NULL, –the stage at which the error occured
[Error_Code] [int] NULL, — the error code generated
[Error_Message] [varchar](1000), –the message of the error
[Error_DateTime] [datetime] NULL, –the moment the error occured
CONSTRAINT [PK_Error_Log] PRIMARY KEY CLUSTERED ( [Record] ASC ) ON [PRIMARY]
) ON [PRIMARY]
Execution_Pointer is a marker used after sql statements. Tracking this will help the programmers to trace the sql statements. We will see the implementation shortly.
—————————————————-
Creating a seperate procudure to log necessary information
—————————————————-
Now we need to write a generic procedure to log errors.
create procedure ksp_log_error
@p_procedure_name varchar(50),
@p_pointer varchar(100),
@p_error_code bigint
as
begin
declare
@v_error_message varchar(1000)
–know the error message from sysmessages table
select @v_error_message=description from master..sysmessages
where error= @p_error_code
–if no message exists, use error code
if @v_error_message is null
set @v_error_message=@p_error_code
–insert into table
insert into Error_Log(Error_Object, Execution_pointer, Error_Code, Error_message, Error_Datetime)
values(@p_procedure_name, @p_pointer, @p_error_code, @v_error_message, getdate())
end
————————–
Using the above procedure
————————–
Finally the procedure should be called. For this, I will give you an example. Suppose, we need to register a user into the database. For this we will use transactions and @@ERROR variable. Transactions are useful, especially when dealing with data. @@ERROR has the last occured error number. Using these two I will construct a procedure. I have written the comments where ever necessary.
create procedure register_user
@p_user_id varchar(50),
@p_password varchar(50),
@p_name varchar(100),
@p_email_id varchar(150)
as
begin
declare
@v_count int,
@v_proc_name varchar(50),
@v_pointer varchar(50),
–set primary variables
set @v_proc_name=’register_user’ –name of this procedure
–check if the user is already registered. This is useful to avoid primary key conflicts while inserting
select @v_count=count(*) from Users where User_ID=@p_user_id
–if the user is already registered, stop proceeding
if(@v_count<>0)
begin
select ‘User already registered’ as [Error]
return –stop the execution
end
–if the user is not registered, register him/her.
begin tran
–issue the insert statement
insert into Users values(@p_user_id, @p_password, @p_name, @p_email_id)
set @v_pointer=’insert statement -1′
–check if there is some error
if(@@ERROR<>0)
begin
rollback tran
–call the error logging procedure and pass parameters
exec ksp_log_error @v_proc_name, @v_pointer, @@ERROR
–stop
return
end
–if ok, commit tran
commit tran
–send back the message
select ‘User added successfully’ as [Message]
end
As a programmers perspective, I think this explanation would be enough for another programmer to understand. If you are a beginner, I strongly recommend searching information further till you understand.
If you want to know about Error Logging futher, go to http://www.sommarskog.se/error-handling-I.html
MSSQL: Constants
Posted on October 20, 07 @ 01:12 am under Databases and has no comments.
@@IDENTITY - returns last inserted identity value
(Use SCOPE_IDENTITY() as alternative)
@@ERROR - last error number
@@ROWCOUNT - returns the number of rows returned from the last SELECT statement.
sql server 2000: Generate insert scripts for data
Posted on October 20, 07 @ 01:11 am under Databases and has no comments.
For generating sql scripts for data, download an app from http://www.sqlscripter.com/
You can also have a storedprocedure doing that for you. Download it from http://www.robgonda.com/blog/files/robGonda/UserFiles/File/generate_inserts.txt
or in my other blog http://mandaksk.wordpress.com/sql-procedure-generate-insert-scripts-for-data/
This procedure takes table name as parameter, and insert statements are displayed as a table. There should be a provision to generate scripts for all tables, by checking the dependancies and constraints.
Pages: [1]
|
