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

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

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