Post Structure Alteration

I was given to do alter the structure of a database according to another one And I’ve finished writing all the scripts to turn the database B into A . But what if the database A has been altered meanwhile ? This is very annoying because constantly i have to modify the scripts in order to keep up with database A alteration. I order not to do that i had to write a small script that ad's automatically the new objects from the database A ... and there it is (for now it can only add columns but i will do it in order to add tables , triggers , stored procedures , Constraints, Indexes etc ....and even the actual data . ****************************************************************************
declare mycursor cursor
for select x1.name , x2.name , x3.name , x1.length , x1.isnullable
from [Server1].[database A].[dbo].[syscolumns] x1
inner join [Server1 ].[DataBase A].[dbo].[sysobjects] x2 on x2.id = x1.id
inner join [Server1].[DataBase A].[dbo].[systypes] x3 on x3.xtype = x1.xtype
inner join sysobjects x4 on x4.name = x2.name
where x2.xtype= 'U' and x1.name not in (select name from syscolumns)
declare @Col sysname
declare @Tab sysname
declare @Type sysname
declare @Length varchar(100)
declare @Null smallint
declare @SQL varchar(1000)
open mycursor
fetch next from mycursor into @Col , @Tab , @Type , @Length , @Null
while @@fetch_status =0
begin
SET @SQL = case when @Null =0 then 'alter table ' + @Tab + ' add ' + @Col + ' '
+ @Type + case when charindex('char', @Type) > 0 then '(' + @Length + ')'
else '' end + ' not null '
else 'alter table ' + @Tab + ' add ' + @Col + ' '
+ @Type + case when charindex('char', @Type) > 0 then '(' +
@Length + ')' else '' end + ' null '

end Exec(@SQL)
fetch next from mycursor into @Col , @Tab , @Type , @Length , @Null
end
close mycursor
deallocate mycursor *********************************************************************************** P.S. If the 2 databases are not on the same server you must link the servers (run SP_LinkedServers '[Server A]' , '[Server B]'

DATA FROM TEMPLATES

Just when i had finished the work on a project , I've got another one on the list . It is about importing data from templates and i have to do the validation part before the actual data is imported into the database. The template is actually a .doc file and i have to get the data into the right type , in order to be imported into some temporally table , where i will make the proper validation . When importing data into sql server from any supported type you must be carefully . For example importing data from a .xls file does not work as it should . For example ,lets say that on the xls rows you got the name of the objects that you want to import and the columns are populated with their values . If the first line of the column is empty , the data from that column is not imported into the database. To make it more clearly ,the first column has strings and the second xls column has integer values and if the first line from the second column is empty ...the whole column is not imported . When using Enterprise Manager to do this , you can change the col's type when importing . If the firs line from the numeric column is not empty , the sql converts the data into float and if the line is empty , it will convert the data into varchar. Obviously , Enterprise Manager will determine the type of the first line and set's the column type , whatever is find's . This is not a good idea , but even so , i should find the data imported as varchar type into the database . This is a fucking annoying bug when you have to import data very often . Finding the solution to solve this could take a lot of time .

WINDOWS

Try to create or rename a folder with the "AUX" string. You will be surprised (at least i was) ...it will not work . There are other combinations of characters with the same effect on the folder's name. I've made the test on WinXP with or without SPx and Win2k3 and i want to try this on the good old 98' .

APPLICATION'S LOG

Yesterday a had an idea to create an application log . This implies both the source code and database monitoring . If recording any user action throw the application interface is not a hard thing to do , i had no idea how to create the tool that records everything "that moves" in the database . This tool already exists (profiler and database_log ) i don't know(for the moment ) how to link the user action in the application and the log created by sql server so , i thought i can write some scripts in order to record every action that occurs in the database. I started digging the help hopping that i will find the same objects that the profiler uses , and I’ve found them. There are in fact a set of system function and procedures and using them i thought "this should do the trick". But the problem occurs with the first procedure that I’ve tried to do some testing with. It is called "sp_trace_create" and you can find it in the master database at extended system procedures. The first param that this procedure uses is the trace_id (there are other params but they are not important in this case). When i tried to execute the procedure with my trace_id i get the Error : "Formal parameter '@traceid' was defined as OUTPUT but the actual parameter not declared OUTPUT." This is a bug in the procedure source code , and is very easy to solve this ...all i had to do was to add at the parameter declaration the "output" string (declare @traceid integer output) . The Sql Server lets you to modify the system stored procedures but this is an extended system procedure and in this case access is denided . There are two types of extended system procedures in the master database , one compiled as .dll files on the disk and the other one is [server internal] . On the .dll files you can start an reverse engineering process to obtain the code in order to modify and compile it again .This is not an easy thing to do and is not recommended but it is not impossible . Bad luck occurs or shall i say "tzeapa" , because this procedure is a server internal type and i need the source code of sql server 2000 in order to solve this . I will try to lookup for a patch on the net for this problem .