Friday, November 03, 2006

How to find database connection strings

When ever we create an application which has to be connected to a database, it needs a connection string. We all know the connection strings for widely used databases like SQL Server, MS Access, Oracle. If not we will find it in web. Then, how to find connection strings for less popular or rarely used databases. Yes you can.

Step 1: Open a notepad application
Step 2: Save the empty file with any name with udl extension (*.udl). Type the filename with extension with in quotes. Step 3: Double click the saved file.
Step 4: It will have 4 tabs (Provider, Connection, Advanced, All). Select "Provider" tab
Step 5: Select the provider. (To connect a database we need a driver or provider. If it is installed in the machine, it will be displayed in the Provider tab) and click Next. For Ex: Microsoft OLE DB for SQL Server
Step 6: Fill the appropriate information in the Connection tab, such as server name, username, password, database. Check the "Allow saving password" option. After filling the information click "Test Connection". It should pop up with the information"Test Connection succeeded".
Step 7: Click "Ok" to close the window. Click "Yes" for the next dialog.
Step 8: Now open the file in notepad. You will get the connection string.

Wednesday, November 01, 2006

When to use SQL Server Cursor

We mostly use Cursors when there is a need for row by row operation, which cannot by replaced by a nested queries. The greatest disadvantage in Cursors is it stores the result set in an memory and processes one by one. Suppose for 1000,00 records it occupies huge memory and performance is will very poor. In those cases it is always better to go for While Loop instead of a cursor. This improves the performance drastically.

declare @loopvar int
declare @maxcnt int
declare @cnter int
declare @outermax int
create table #temptable (

ROWID INT IDENTITY(1,1),
productgp int )

Insert into #temptableselect distinct id from products where status = 1 order by ID
set @outermax = @@ROWCOUNT

set @cnter = 1
While (@cnter <= @outermax)
Begin
// Do your process here

// Do your process here
set @cnter = @cnter +1
end

Use Cursors only if resultset is less in number, say less than 5000 records.