DLL Memory Management

I have been a good Visual Basic Programmer during my early days in undergraduate course. VB6 is used extensively to develop desktop (windows) based applications. DLLs here mean Dynamic Link Libraries. DLLs generally store function definitions. So, when called by an application by passing input parameters, it executes function stored in.

 

So, what is the benefit of DLLS? A normal programmer would say that it helps in modularization (i.e. it helps to separate business logics). Yes, that is absolutely true. I also remember developing DLLs for printing, reporting and handling common functions. That is not enough. I have noticed that applications using DLLs are more efficient that those not using it. They consume less time and memory. May be windows itself helps in memory management of applications using DLLs.

 

One major benefit of DLL becomes that the running application (EXE) can be less in size. It is because most of its functionalities are stored in DLLs. And DLLs can be called upon requirement basis and similarly freed after use. Thus, it helps to reduce memory consumption by the Application EXE.

Read more...

How to block built in windows administrators from using your SQL database?

The problem is terrible. You are not the windows administrator. But you have to create a database in the machine without allowing built in administrator of windows to access. Either you install the SQL Server in Mixed Mode or Windows Authentication mode, by default, built-in administrators becomes SQL Server Administrator. So, they will have access to each and every SQL database in that machine.

If you really want to block, then in my opinion, only two options are remained.

1. To remove, built-in administrator login from SQL Server Administrator role. Yes, it woks fine. But, it will block the administrator from creating new databases as well and also from other rights. That may be problematic.
2. By completing denying access to built-in administrator to SQL Server. This may also be problematic as the administrator will not even able to login to SQL.

 Both the things can be done from SQL Server Login Properties of SQL Server Enterprise Manager.

Read more...

Default user of login mapped to role SQL Server Administrator

The post I m writing is slightly confusing. If a SQL server login is assigned to System Administrator Server role of SQL Server 2000, then it will have rights to perform any activity in SQL Server Installation. That is what, SQL Server says. This may be problematic some times.

 

Now, Lets say there is a login stcsa with mapped to role System Administrator. First, you login to ISQLW with login stcsa and create a table with name tbl_test as below.

 

create table tbl_test (t varchar(10))

 

Now, check the owner of tbl_test, it will be dbo not stcsa. Suprising Not? This is because stcsa is System Adminitrator. But, you can call the table as simply tbl_test without "dbo." in front of the object name. The following select query works.

 

select    * from            tbl_test

 

Now, lets say, you again created a table with same name but like below.

 

create table stcsa.tbl_test ( t varchar(200))

 

Then, there will be two tables with name tbl_test and owners as dbo and stcsa. Now, if you use simply tbl_test, which one object do you think will it call? No not, it will call dbo.tbl_test not stcsa.tbl_test despite you are logged in as stcsa. This is where problem comes. In normal case, if you are logged in with a sql login and that login is mapped with user of same name, then if you call sql objects without prefix username, then it will search for username.objectname.

 

I am not sure that whether this can be called a bug in SQL Server or not. I just want to specify that, it's always useful to call sql objects by using username.objectname. It becomes more useful, when the login used is mapped to the role like SQL Server Administrator.

 

Read more...

Remove Single Line Comments from SQL Server Procedure

If you follow this blog regularly, you would have noticed that I have written logic as well as code to remove multi-line comments from SQL Server Procedure. You also can follow the link to get to that. Here, my concern is to remove single line comment from SQL Server procedure. It's a much simple compared to removing multi-line comment.

 

Single line comment in SQL Server is preceded by double dash like "—". So, you just consider following two points.

1.      You need to remove the lines beginning with double dash.

2.      If a line contains double dash at the middle, then remove part of line after the double dash.

 

An example will clarify more on this. First, I created a test procedure with name sp_helptext.

 

create proc sp_proc1  

as   

begin   

 create table #tt  

 (  

  col1 varchar(200)  

 )  

 

-- insert into ##tt values ('test data 1')

insert into #tt values ('test data 2')  -- test comments

-- insert into ##tt values ('test data 3')

  -- test comments

 

select *

from #tt 

end   

 

Then, I created a temporary table with name #tbl_sp_text to populate the help text of procedure on that. Finally, I applied above mentioned logic to retrieve the lines without any single line comments. Here is the complete code.

 

create table #tbl_sp_text ( id int identity(1,1), sp_text varchar(8000))

insert into #tbl_sp_text

exec sp_helptext  sp_proc1 /* procedure name */

 

 

select      case when charindex('--',sp_text) > 0 then substring(sp_text,0,charindex('--',sp_text))

      else sp_text end sp_text

from  #tbl_sp_text

 

Read more...