This post is in respose to a query by one of our fellow bloggers BlackBird
To most of us the term "Servers" elicits pictures of "Tall,Dark physically intimidatin Machines, numerous connections, cold rooms, high Racks and horn rimmed techies who cannot construct a single sentence without the word Configure followed by a string of acronyms :) ".......Ok maybe I over exaggerate but am sure u get my drift...yes?
That image aside.....it helps to see a server as nothing but a conduit for information(Requests and Outputs).
bearing this in mind following simple guidlines can point u in the right direction:
1. What will your server be used for?: This is a very fundamental question. There are many kinds of servers out there each boasting various configurations. Keep in mind that these configs are for optimising the box to perform certain tasks. It would be otherwise "not very clever" to but a high end, multi core symmetric processing beast just to type letters :)..(again I over exaggerate)
2. What will you be serving: "Stuffed Turkey? or a black tea? ok this analogy may not sit well but I am trying to put across is that you should also consider the types of applications that you are going to run on your server......ever wondered why every piece of software comes with a minimum requirements list?........yes snoop around the ReadMe(s)....before you burn or cheapskate yourself into a useless setup.
3.The machine you use for a server doesn't have to be the friendliest machine available. As wonderful as graphical interfaces are, they don't make a lot of difference to the data flowing through the box. You can always use a prickly but efficient operating system for your server and do most of your work on the client anyway. If you take this really seriously, you may not even need a monitor for your server - you can often control the server from the client.
4. Read the following: Choosing a server for your Business
Finally and most commonly done: Ignore all of the above, go out there and get yourself a "Really Mean SOB!!!" :)
Additional links you could check out:
IBM Servers
Sun Servers
HP Servers
Keep us posted on what you eventaully settle for Cheers!!!
Wednesday, November 15, 2006
Saturday, November 11, 2006
Mysql server setup
Hi guys, I have a problem, I am setting up Mysql server on the RedHat 4.1.12 enterprise edition as my database server. I want to connect client applications from the windows platform. I am using the 3.51 odbc connector on the client side. When I set up the system DSN to connect to the linux server, the connection is successful. But when I try to run the application from the client side its giving me an error the the tables in the server are not accessible. If the client machine has mysql installed, it tries to access the database on the client machine instead of the linux server. What am I not setting up right??? Pleasae help. I am using the conection string "DSN=dsnmane;DATABASE= " & Trim(mydatabse) & "; USER=username;PASSWORD=password;OPTION=3;"
Tuesday, November 07, 2006
Back to the basics
More often than not as an administrator of anything be it in the capacity of a Systems Admin or Database admin, one major concern is the system's overall performance. High latencies and large turnaround times are enough to make your life a living hell.
In most cases we want to get highly speced hardware for our systems. The higher the specs the better we always assume. Albeit this is true it is not always the case. There has to be a point at which the line is drawn.
One aspect many tend to overlook is that regardless of what hardware you have got, configuration of your system plays a key role in determining the overall performance.
For example you may have 4.0GHz Dual core procesor, 5GB of RAM on your server, You have implemented one set of RAID 10 onto which you have your databases and system file stored together ...... yet you cannot figure out why your otherwise decent machine is not performing........well helloooo your configuration is not optimal.
You simply cannot lump system file, your paging files and your database files all on one disk set stupidoh :-) !!!!
So its always good to go back to the basics.
Found the really great resource on tweaking your system's virtual memory and why you do what u do......... hope its a great read for you as it was for me :)
In most cases we want to get highly speced hardware for our systems. The higher the specs the better we always assume. Albeit this is true it is not always the case. There has to be a point at which the line is drawn.
One aspect many tend to overlook is that regardless of what hardware you have got, configuration of your system plays a key role in determining the overall performance.
For example you may have 4.0GHz Dual core procesor, 5GB of RAM on your server, You have implemented one set of RAID 10 onto which you have your databases and system file stored together ...... yet you cannot figure out why your otherwise decent machine is not performing........well helloooo your configuration is not optimal.
You simply cannot lump system file, your paging files and your database files all on one disk set stupidoh :-) !!!!
So its always good to go back to the basics.
Found the really great resource on tweaking your system's virtual memory and why you do what u do......... hope its a great read for you as it was for me :)
Saturday, November 04, 2006
SQL: VB.NET Code Generating Stored Procedure
All computer applications need data to run. Preparing your code to read from a database using stored procedures has to be one of the most common, redundant and tedious tasks. The developer must know what stored procedures are available on the target database, parameters available, their ordinal position their datatype and precision. (doesnt sound like much fun)....... atleast I dont think it is and thus I wrote this small procedure to do all the above mentioned tasks and produces the desired code. :)
Instructions:
1. Copy the code in Pink (no reason for color choice) ,paste it on your query analyser and run it. A stored procedure called
sproc_GenerateParameters is created on the target databse
2. Execute sproc_GenerateParameters as follows:
exec sproc_GenerateParameters Param1,Param2,Param3,Param4
Key
---
Param1:= Name of Procedure you wish to generate
Param2:= Name of Business object being used to call Stored Procedure
Param3:= Name of BO function through which to call procedure
Param4:= Function return type E.G Integer, String, Object
/*
===============================================================
THIS CODE WILL GENERATE VB.NET CODE FOR CREATING PARAMETERS
THE CODE SHALL BE USED BY WHILE DEVELOPING THE
COMPONENT THAT WILL USE THE SPECIFIED STORED PROCEDURE
AUTHOR: MG
DATE: 20/07/2006
==================================================================
*/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[sproc_GenerateParameters]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[sproc_GenerateParameters]
go
create proc sproc_GenerateParameters
@ProcedureName varchar (50), /*Name of Procedure you wish to generate*/
@objectName varchar (50), /*Name of Business object being used to call Stored Procedure*/
@FunctionName varchar (50), /*Name of BO function through which to call procedure*/
@returnType varchar (50) /*Function return type E.G Integer, String, Object*/
as
DECLARE @PARAM_NAME VARCHAR (100),
@DIRECTION INT,@TYPE_NAME VARCHAR(50),@LENGTH INT,
@ORDINAL_POSITION int, @isNULLABLE VARCHAR (5),
@DBtypeInt varchar (20), @DBtypeNvarchar varchar (20),
@DBTypeBit varchar (20), @DBTypeDateTime varchar (20),
@DBTypeSmallInt varchar (20),@DBTypeMoney varchar (20),
@Run int ,@@FunctionSignature varchar(4000),
@@FuncDefinition varchar (8000),@@FunctionCall varchar (8000)
set @Run = 0 /*Run counter to track number of cycles*/
set @DBtypeInt = 'SqlDbType.Int'
set @DBtypeNvarchar='SqlDbType.NVarChar'
set @DBTypeDateTime = 'SqlDbType.DateTime'
set @DBTypeBit = 'SqlDbType.Bit'
set @DBTypeMoney ='SqlDbType.Money'
set @DBTypeSmallInt='SqlDbType.SmallInt'
declare @paraString varchar(4000)
GETMETADATA:
declare ParaCur cursor fast_forward read_only
for
SELECT DISTINCT
PARAM_NAME = convert(sysname,c.name),DIRECTION = convert(smallint, 1+c.isoutparam),
TYPE_NAME = t.name,
convert(int,case
when type_name(d.ss_dtype) IN ('numeric','decimal') then /* decimal/numeric types */
OdbcPrec(c.xtype,c.length,c.xprec)+2
else
isnull(d.length, c.length)end) LENGTH,
ORDINAL_POSITION = convert(int, c.colid),IS_NULLABLE = CASE (C.ISNULLABLE) WHEN 0 THEN 'YES' WHEN 1 THEN 'NO' ELSE '' END
FROM syscolumns c
INNER JOIN sysobjects o ON o.ID=c.ID
INNER JOIN systypes t ON c.xusertype = t.xusertype
INNER JOIN master.dbo.spt_datatype_info d ON c.xtype = d.ss_dtype
WHERE o.name =@ProcedureName AND user_name(o.uid) like 'dbo' AND o.id = c.id
AND isnull(d.AUTO_INCREMENT,0) = 0 AND (o.type in ('P'))
ORDER BY ORDINAL_POSITION
if @run = 0
begin
print 'With '+@objectName+'
.CommandText ="'+@ProcedureName+'"
.CommandType = CommandType.StoredProcedure'
open paraCur
FETCH NEXT FROM ParaCur INTO @PARAM_NAME, @DIRECTION ,@TYPE_NAME, @LENGTH,@ORDINAL_POSITION, @isNULLABLE
while @@fetch_status=0
begin
/*Data Type*/
if @TYPE_NAME = 'int' set @TYPE_NAME=@DBtypeInt
else if @TYPE_NAME = 'varchar' set @TYPE_NAME=@DBtypeNvarchar
else if @TYPE_NAME = 'DateTime' set @TYPE_NAME=@DBTypeDateTime
else if @TYPE_NAME = 'Bit' set @TYPE_NAME=@DBTypeBit
else if @TYPE_NAME = 'Money' set @TYPE_NAME=@DBTypeMoney
else if @TYPE_NAME = 'Smallint' set @TYPE_NAME=@DBTypeSmallInt
else set @TYPE_NAME = 'SqlDbType.'+@TYPE_NAME
/*Direction */
set @paraString =' .Parameters.Add("'+@PARAM_NAME+'",'+@TYPE_NAME+','+cast(@LENGTH as varchar(10))+' ) '
print @paraString
if @DIRECTION = 2
begin
set @paraString = ''
set @paraString = @paraString + ' .Parameters('+cast(@ORDINAL_POSITION as varchar(10))+').Direction = ParameterDirection.InputOutput'
print @paraString
end
FETCH NEXT FROM ParaCur INTO @PARAM_NAME, @DIRECTION ,@TYPE_NAME, @LENGTH,@ORDINAL_POSITION, @isNULLABLE
set @paraString = ''
end
close paraCur
deallocate paraCur
print 'End With'
print'
===========================================================
'
set @run = 1
goto GETMETADATA /*Cannot believe I used a GOTO statement :( */
end
else
if @run = 1 /*Generate The Functions*/
begin
set @@FunctionSignature = 'Public Function '+@FunctionName+' (ByRef row As '
set @@FuncDefinition = 'With '+@objectName
set @@FuncDefinition = @@FuncDefinition+'
'
open paraCur
FETCH NEXT FROM ParaCur INTO @PARAM_NAME, @DIRECTION ,@TYPE_NAME, @LENGTH,@ORDINAL_POSITION, @isNULLABLE
while @@fetch_status=0
begin
/*Data Type*/
if @TYPE_NAME = 'int' set @TYPE_NAME= 'Integer'
else if @TYPE_NAME = 'varchar' set @TYPE_NAME='String'
else if @TYPE_NAME = 'DateTime' set @TYPE_NAME='DateTime'
else if @TYPE_NAME = 'Bit' set @TYPE_NAME='Int16'
else if @TYPE_NAME = 'Money' set @TYPE_NAME='Decimal'
else if @TYPE_NAME = 'Smallint' set @TYPE_NAME='Int16'
else if @TYPE_NAME = 'TinyInt' set @TYPE_NAME='Int16'
else set @TYPE_NAME = @TYPE_NAME
/*Signature*/
set @@FunctionSignature = @@FunctionSignature+', '+replace(@PARAM_NAME,'@','')+' as '+@TYPE_NAME
set @@FuncDefinition = @@FuncDefinition+'
.Parameters.Item("'+@PARAM_NAME+'").Value ='+replace(@PARAM_NAME,'@','')+' '
FETCH NEXT FROM ParaCur INTO @PARAM_NAME, @DIRECTION ,@TYPE_NAME, @LENGTH,@ORDINAL_POSITION, @isNULLABLE
end
close paraCur
deallocate paraCur
set @@FunctionSignature = @@FunctionSignature+' ) as '+ upper(@returnType)
set @@FuncDefinition = @@FuncDefinition+'
.ExecuteNonQuery()
If (CDbl(.Parameters.Item("@@error_value").Value) <> 0) Then Return
End With
End Function'
print @@FunctionSignature
print ''
print @@FuncDefinition
set @run = 2
print '
===========================================================
'
goto GETMETADATA /*I did it again :( */
end
else
if @run = 2 /*Generating Function Call*/
begin
print 'Generating Function Call'
print '========================'
set @@FunctionCall=@FunctionName+'(Row, '
open paraCur
FETCH NEXT FROM ParaCur INTO @PARAM_NAME, @DIRECTION ,@TYPE_NAME, @LENGTH,@ORDINAL_POSITION, @isNULLABLE
while @@fetch_status=0
begin
set @@FunctionCall=@@FunctionCall+'<'+replace(@PARAM_NAME,'@','')+'>, '
FETCH NEXT FROM ParaCur INTO @PARAM_NAME, @DIRECTION ,@TYPE_NAME, @LENGTH,@ORDINAL_POSITION, @isNULLABLE
end
close paraCur
deallocate paraCur
set @@FunctionCall=left(@@FunctionCall,len(@@FunctionCall)-1)+' )'
print @@FunctionCall
end
Example:
---------
Scripting the CustOrderHist stored procedure in the Northwind Database
exec sproc_GenerateParameters 'CustOrderHist', 'CustOrder', 'getCustomerOrder' , 'integer'
Output
-------
Visual Basic.NET code for accessing the above Stored procedure.
Remarks
--------
The Output code will need to be tweaked with the following:
1.Create a command object
2.Create a connection and pass it to the command object
3. Copy the output and paste it into your code and Viola!!! u are good to go
Known Issues
--------------
The code procedure may return an error when executed on SQL Server 2005. Reason being that the table master.dbo.spt_datatype_info is not available. You can try a DTS import of the table into sql server 2005.
Instructions:
1. Copy the code in Pink (no reason for color choice) ,paste it on your query analyser and run it. A stored procedure called
sproc_GenerateParameters is created on the target databse
2. Execute sproc_GenerateParameters as follows:
exec sproc_GenerateParameters Param1,Param2,Param3,Param4
Key
---
Param1:= Name of Procedure you wish to generate
Param2:= Name of Business object being used to call Stored Procedure
Param3:= Name of BO function through which to call procedure
Param4:= Function return type E.G Integer, String, Object
/*
===============================================================
THIS CODE WILL GENERATE VB.NET CODE FOR CREATING PARAMETERS
THE CODE SHALL BE USED BY WHILE DEVELOPING THE
COMPONENT THAT WILL USE THE SPECIFIED STORED PROCEDURE
AUTHOR: MG
DATE: 20/07/2006
==================================================================
*/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[sproc_GenerateParameters]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[sproc_GenerateParameters]
go
create proc sproc_GenerateParameters
@ProcedureName varchar (50), /*Name of Procedure you wish to generate*/
@objectName varchar (50), /*Name of Business object being used to call Stored Procedure*/
@FunctionName varchar (50), /*Name of BO function through which to call procedure*/
@returnType varchar (50) /*Function return type E.G Integer, String, Object*/
as
DECLARE @PARAM_NAME VARCHAR (100),
@DIRECTION INT,@TYPE_NAME VARCHAR(50),@LENGTH INT,
@ORDINAL_POSITION int, @isNULLABLE VARCHAR (5),
@DBtypeInt varchar (20), @DBtypeNvarchar varchar (20),
@DBTypeBit varchar (20), @DBTypeDateTime varchar (20),
@DBTypeSmallInt varchar (20),@DBTypeMoney varchar (20),
@Run int ,@@FunctionSignature varchar(4000),
@@FuncDefinition varchar (8000),@@FunctionCall varchar (8000)
set @Run = 0 /*Run counter to track number of cycles*/
set @DBtypeInt = 'SqlDbType.Int'
set @DBtypeNvarchar='SqlDbType.NVarChar'
set @DBTypeDateTime = 'SqlDbType.DateTime'
set @DBTypeBit = 'SqlDbType.Bit'
set @DBTypeMoney ='SqlDbType.Money'
set @DBTypeSmallInt='SqlDbType.SmallInt'
declare @paraString varchar(4000)
GETMETADATA:
declare ParaCur cursor fast_forward read_only
for
SELECT DISTINCT
PARAM_NAME = convert(sysname,c.name),DIRECTION = convert(smallint, 1+c.isoutparam),
TYPE_NAME = t.name,
convert(int,case
when type_name(d.ss_dtype) IN ('numeric','decimal') then /* decimal/numeric types */
OdbcPrec(c.xtype,c.length,c.xprec)+2
else
isnull(d.length, c.length)end) LENGTH,
ORDINAL_POSITION = convert(int, c.colid),IS_NULLABLE = CASE (C.ISNULLABLE) WHEN 0 THEN 'YES' WHEN 1 THEN 'NO' ELSE '' END
FROM syscolumns c
INNER JOIN sysobjects o ON o.ID=c.ID
INNER JOIN systypes t ON c.xusertype = t.xusertype
INNER JOIN master.dbo.spt_datatype_info d ON c.xtype = d.ss_dtype
WHERE o.name =@ProcedureName AND user_name(o.uid) like 'dbo' AND o.id = c.id
AND isnull(d.AUTO_INCREMENT,0) = 0 AND (o.type in ('P'))
ORDER BY ORDINAL_POSITION
if @run = 0
begin
print 'With '+@objectName+'
.CommandText ="'+@ProcedureName+'"
.CommandType = CommandType.StoredProcedure'
open paraCur
FETCH NEXT FROM ParaCur INTO @PARAM_NAME, @DIRECTION ,@TYPE_NAME, @LENGTH,@ORDINAL_POSITION, @isNULLABLE
while @@fetch_status=0
begin
/*Data Type*/
if @TYPE_NAME = 'int' set @TYPE_NAME=@DBtypeInt
else if @TYPE_NAME = 'varchar' set @TYPE_NAME=@DBtypeNvarchar
else if @TYPE_NAME = 'DateTime' set @TYPE_NAME=@DBTypeDateTime
else if @TYPE_NAME = 'Bit' set @TYPE_NAME=@DBTypeBit
else if @TYPE_NAME = 'Money' set @TYPE_NAME=@DBTypeMoney
else if @TYPE_NAME = 'Smallint' set @TYPE_NAME=@DBTypeSmallInt
else set @TYPE_NAME = 'SqlDbType.'+@TYPE_NAME
/*Direction */
set @paraString =' .Parameters.Add("'+@PARAM_NAME+'",'+@TYPE_NAME+','+cast(@LENGTH as varchar(10))+' ) '
print @paraString
if @DIRECTION = 2
begin
set @paraString = ''
set @paraString = @paraString + ' .Parameters('+cast(@ORDINAL_POSITION as varchar(10))+').Direction = ParameterDirection.InputOutput'
print @paraString
end
FETCH NEXT FROM ParaCur INTO @PARAM_NAME, @DIRECTION ,@TYPE_NAME, @LENGTH,@ORDINAL_POSITION, @isNULLABLE
set @paraString = ''
end
close paraCur
deallocate paraCur
print 'End With'
print'
===========================================================
'
set @run = 1
goto GETMETADATA /*Cannot believe I used a GOTO statement :( */
end
else
if @run = 1 /*Generate The Functions*/
begin
set @@FunctionSignature = 'Public Function '+@FunctionName+' (ByRef row As '
set @@FuncDefinition = 'With '+@objectName
set @@FuncDefinition = @@FuncDefinition+'
'
open paraCur
FETCH NEXT FROM ParaCur INTO @PARAM_NAME, @DIRECTION ,@TYPE_NAME, @LENGTH,@ORDINAL_POSITION, @isNULLABLE
while @@fetch_status=0
begin
/*Data Type*/
if @TYPE_NAME = 'int' set @TYPE_NAME= 'Integer'
else if @TYPE_NAME = 'varchar' set @TYPE_NAME='String'
else if @TYPE_NAME = 'DateTime' set @TYPE_NAME='DateTime'
else if @TYPE_NAME = 'Bit' set @TYPE_NAME='Int16'
else if @TYPE_NAME = 'Money' set @TYPE_NAME='Decimal'
else if @TYPE_NAME = 'Smallint' set @TYPE_NAME='Int16'
else if @TYPE_NAME = 'TinyInt' set @TYPE_NAME='Int16'
else set @TYPE_NAME = @TYPE_NAME
/*Signature*/
set @@FunctionSignature = @@FunctionSignature+', '+replace(@PARAM_NAME,'@','')+' as '+@TYPE_NAME
set @@FuncDefinition = @@FuncDefinition+'
.Parameters.Item("'+@PARAM_NAME+'").Value ='+replace(@PARAM_NAME,'@','')+' '
FETCH NEXT FROM ParaCur INTO @PARAM_NAME, @DIRECTION ,@TYPE_NAME, @LENGTH,@ORDINAL_POSITION, @isNULLABLE
end
close paraCur
deallocate paraCur
set @@FunctionSignature = @@FunctionSignature+' ) as '+ upper(@returnType)
set @@FuncDefinition = @@FuncDefinition+'
.ExecuteNonQuery()
If (CDbl(.Parameters.Item("@@error_value").Value) <> 0) Then Return
End With
End Function'
print @@FunctionSignature
print ''
print @@FuncDefinition
set @run = 2
print '
===========================================================
'
goto GETMETADATA /*I did it again :( */
end
else
if @run = 2 /*Generating Function Call*/
begin
print 'Generating Function Call'
print '========================'
set @@FunctionCall=@FunctionName+'(Row, '
open paraCur
FETCH NEXT FROM ParaCur INTO @PARAM_NAME, @DIRECTION ,@TYPE_NAME, @LENGTH,@ORDINAL_POSITION, @isNULLABLE
while @@fetch_status=0
begin
set @@FunctionCall=@@FunctionCall+'<'+replace(@PARAM_NAME,'@','')+'>, '
FETCH NEXT FROM ParaCur INTO @PARAM_NAME, @DIRECTION ,@TYPE_NAME, @LENGTH,@ORDINAL_POSITION, @isNULLABLE
end
close paraCur
deallocate paraCur
set @@FunctionCall=left(@@FunctionCall,len(@@FunctionCall)-1)+' )'
print @@FunctionCall
end
Example:
---------
Scripting the CustOrderHist stored procedure in the Northwind Database
exec sproc_GenerateParameters 'CustOrderHist', 'CustOrder', 'getCustomerOrder' , 'integer'
Output
-------
Visual Basic.NET code for accessing the above Stored procedure.
Remarks
--------
The Output code will need to be tweaked with the following:
1.Create a command object
2.Create a connection and pass it to the command object
3. Copy the output and paste it into your code and Viola!!! u are good to go
Known Issues
--------------
The code procedure may return an error when executed on SQL Server 2005. Reason being that the table master.dbo.spt_datatype_info is not available. You can try a DTS import of the table into sql server 2005.
Friday, November 03, 2006
Techie Humor
I came across this really funny article: How to shoot yourself in the foot
If you have written code in any of these languages u are definitely going to enjoy this
If you have written code in any of these languages u are definitely going to enjoy this
Subscribe to:
Posts (Atom)