Thursday, May 28, 2009

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

Wednesday, May 27, 2009

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

Tuesday, May 26, 2009

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.

Sunday, May 24, 2009

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()

Saturday, May 23, 2009

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