Generate System Column ID based on Format Required in SQL

If we want to make a generate id column in table which format is 'TR-091214-001' where,
- tr is define transaction
- 091213 is define yymmdd
- 001 is define increment number of transaction each day

we can make a simple logic and validation for that.

here the steps :

-- step 1 create table
create table testgenid
(
tr_id varchar(30) not null,
primary key(tr_id)
)

-- step 2 generate id
declare @gen_id varchar(30)
set @gen_id = (
select 'TR-'+ right(convert(varchar,getdate(),112),6) + '-' + right('00' + cast((cast(right(isnull(max(tr_id),'000'),3) as int) + 1) as varchar),3)
from testgenid
where tr_id like '%' + right(convert(varchar,getdate(),112),6) + '%')

print @gen_id

insert into testgenid values(@gen_id)

-- step 3 see the result
select * From testgenid

the result is : TR-091214-001

happy coding ^^

Data Dictionary Using Query SQL

When we want to summarize all the tables on database from SQL we need the query to make it simple and fast... here the query :

select distinct b.name, a.name, c.name, a.max_length,c.precision, c.scale
from sys.columns a left join sys.objects b
on a.object_id = b.object_id
left join sys.types c
on c.system_type_id = a.system_type_id
and c.user_type_id = a.user_type_id

Having A Nice Try… ^^

Good Luck

Row Number Function in SQL 2005

A new function in sql server 2005. It's function returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

Syntax :

ROW_NUMBER ( ) OVER ( [] )

Example :

create table Testing ( Kd varchar(10) not null, Name varchar(10) )

insert into Testing values('K01','Siji')
insert into Testing values('K02','Loro')
insert into Testing values('K03','Telu')
insert into Testing values('K04','Opat')

select ROW_NUMBER() OVER(ORDER BY Kd) AS 'No.' , Kd , Name from Testing

Output :

Easy Right!!!

Happy Code \^_^/

Find Text In SQL Server

Sometimes we want to check where the certain text could appear on the store procedure or trigger we made... Here is the query provide to maximize the searching :


declare @text varchar(max)
set @text = 'Wihemdra'

select name from sys.objects
where object_id in
(
select id from syscomments
where text like '%'+@text+'%'
)


Regards,
Wihemdra

Example Duwamish Architecture with ASP.NET C#

Above is the screen capture for duwamish layer template.

The Duwamish 7.0 architecture is divided into four logical layers:

Web Layer
The Web layer provides access for clients to the application. This layer is implemented as the Web project in the Duwamish.sln solution file. The Web layer consists of ASP.NET Web Forms and code-behind files. The Web Forms simply provide the user experience in HTML, while the code-behind files implement event handling for the various controls.

Business Facade Layer
The Business Facade layer provides interfaces to the Web layer to handle accounts, category browsing, and book purchasing. This layer is implemented as the BusinessFacade project in the Duwamish.sln solution file. The Business Facade layer serves as an isolation layer, segregating the user interface from the implementation of the various business functions. Apart from low-level system and support functions, all calls to database servers are made through this assembly.

Business Rules Layer
The Business Rules layer, which is implemented as the BusinessRules project in the Duwamish.sln solution file, contains the implementation of the various business rules and logic. Business rules do tasks such as the validation of customer accounts and book orders.

Data Access Layer
The Data Access layer provides data services to the Business Rules layer. This layer is implemented as the DataAccess project in the Duwamish.sln solution file.

Download Template

Reference :

MSDN

Design Dynamic Structure Table

Design table for meet the requirement is not so simple. Sometimes rule and process of business change rapidly. We must predict the future before we start to create the table. The table structure we made could impact anywhere which related it. As you could see the logic for application , reports, calculation, batch, etc depend on the design of the table. Sometimes we think the table is unpowerfull or too much field unrequired...

One example to handle this requirement for current and future development :

The requirement is : every product have own rate accumulated per year, and not every year is at the same rate but we assumed that the maximum rate until 100 years or less. So the simulation is like this :

--bad design table
--take too much columns
create table mst_rate_product
(
code_product nvarchar(20) not null,
rate_year01 decimal(10,4),
rate_year02 decimal(10,4),
rate_year03 decimal(10,4),
rate_year04 decimal(10,4),
rate_year05 decimal(10,4),
rate_year06 decimal(10,4),
rate_year07 decimal(10,4),
rate_year08 decimal(10,4),
rate_year09 decimal(10,4),
rate_year10 decimal(10,4),
--...
rate_year100 decimal(10,4)
primary key (code_product)
)

--recommended design table structured
--powerfull on data requirements and saving data
create table mst_rate_product_recommended
(
code_product nvarchar(20) not null,
year int,
rate decimal(10,4),
primary key (code_product)
)

We don't have to affraid to make a complicated report by logic because sql server 2005 have pivot table to make a report based on column(if needed) not by rows.

Regards,
Wihemdra

Using Begin Tran, Rollback Tran and Commit Tran on SQL Server

Sometimes, we're using too much windows document in sql server.
To prevent the mass Execute query on one window we didn't want, better we use query "begin tran" in every windows document.
So, if we're executing the windows document by accident, we could rollback all the transaction we made ^^".

-----------------------------Assumed this is the windows document used
Begin Tran

select * from nametable

insert into nametable values('string value')
insert into nametable values('string value')
insert into nametable values('string value')
insert into nametable values('string value')
insert into nametable values('string value')
insert into nametable values('string value')

update nametable
set namecolumn = 'asdf'

/*
When we Execute execute for the second time, but we want to cancel it. Just use rollback tran. But otherwise use commit tran
*/

Regards,
Wihemdra

varchar(max) in sql server 2005

Varchar(max) is one of the new type data of sql server 2005.

Usually we use it when we didn't know the maximum length of string or we couldn't expect how long the string is.

As you know the length of varchar(max) is -1. We'll see when we're using ALT+F1 at table name. Or we could using this query to figure it out :

---------------------------------------------------------------------------
drop table test1

create table test1
(
strMax varchar(max)
)

select distinct b.name, a.name, c.name, a.max_length,c.precision, c.scale
from
sys.columns a left join sys.objects b
on a.object_id = b.object_id
left join sys.types c
on c.system_type_id = a.system_type_id
and c.user_type_id = a.user_type_id
where b.name = 'test1'
----------------------------------------------------------------------------

Here the results :

name name name max_length precision scale
test1 strMax varchar -1 0 0


Regards,

Wihemdra

String or binary data would be truncated. Error Message @ SQL

Error Message:
Msg 8152, Level 16, State 14, Line 5
String or binary data would be truncated.

Severity level:
16.

Description:
This error message appears when you try to insert a string with more characters than the

column can maximal accommodate.

Consequences:
The T-SQL statement can be parsed, but causes the error at runtime.

Resolution:
Errors of the Severity Level 16 are generated by the user and are corrigible by the user.

The statement cannot be executed this way. You must either shorten the string to be isnerted

to widen the column.

Versions:
All versions of SQL Server.

Example(s):
USE tempdb;
IF OBJECT_ID('tempdb.#t') > 0
DROP TABLE #t
GO
CREATE TABLE #t
(
c1 CHAR
);
INSERT INTO #t SELECT 'abc'
GO

Remarks:
In the above example we try to insert a string 'abc' with a length of 3 into the column c1 of the table #t. Because c1 is of the data type CHAR(1), the error is raised.

String Connection To Upload Data From Excel To SQL Using ASP.NET VB

Sometimes we would like to upload mass data from excel to SQL 2005.
Here the connection string :

Dim FileLocation As String = "D:\Wihemdra\Book1.xls"

' Create connection string variable. Modify the "Data Source" parameter as' appropriate for your environment.

Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _"Data Source=" & FileLocation & ";" & _"Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""

' Create the connection object by using the preceding connection string.
Dim objConn As New OleDbConnection(sConnectionString)

' Open connection with the database.
objConn.Open()

' The code to follow uses a SQL SELECT command to display the data from the worksheet.
' Create new OleDbCommand to return data from worksheet.
Dim objCmdSelect As New OleDbCommand("SELECT * FROM [Sheet1$]", objConn)

' Create new OleDbDataAdapter that is used to build a DataSet' based on the preceding SQL SELECT statement.
Dim objAdapter1 As New OleDbDataAdapter()

' Pass the Select command to the adapter.
objAdapter1.SelectCommand = objCmdSelect

' Create new DataSet to hold information from the worksheet.
Dim objDataset1 As New Data.DataSet()

' Fill the DataSet with the information from the worksheet.
objAdapter1.Fill(objDataset1, "XLData")

' Clean up objects.
objConn.Close()

Make excel report from query option of SQL Server 2005

Sometimes we want to check data from our database or make temporary report using excel format.
Here the step to resolve your problem.
It is faster way to copy data from sql to excel.
I'm using Query options facility.

Please Follow this instruction :

1. Make a short sample query. Example :

USE [AdventureWorks]
Go

select * from HumanResources.Department

2. Choose the file option like this :

Query -> Query Options
Text -> Output format : Tab delimited
OK

3. Choose "Results to Text" (in toolbars)

4. Press F5

5. Drag / "Copy and Paste" the results from sql to Excel.

See what happen. It's cool right... ^^"

Best Regards,

Wihemdra