Generate Stored procedures scripts for SP-s modified after a specific date

When I am installing my changes in an other environment (test envir in my case) i need to get scripts for the modified stored procedures only. When you have a lot of sp-s, and you only need scripts for 3-4 of them, then you should use a script like this one:

CREATE PROCEDURE [dbo].[GenerateLatestProcedures]
@DateFrom DATETIME

AS
BEGIN

DECLARE @spName NVARCHAR(128), @object_id INT
DECLARE myCursor CURSOR FOR
SELECT name
FROM sys.procedures
WHERE modify_date >= @DateFrom OR create_date >= @DateFrom
ORDER BY modify_date DESC

OPEN myCursor
FETCH NEXT FROM myCursor INTO @spName
WHILE @@fetch_status = 0
BEGIN

PRINT 'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[' + CONVERT(VARCHAR(255), @spName) + ']'') AND type in (N''P'', N''PC''))
DROP PROCEDURE [' + CONVERT(VARCHAR(255), @spName) + ']'

SELECT @object_id = object_id FROM sys.procedures WHERE NAME = @spName
PRINT OBJECT_DEFINITION(@object_id)

FETCH NEXT FROM myCursor INTO @spName
END
CLOSE myCursor
DEALLOCATE myCursor

END

The ConnectionString property has not been initialized.

The ConnectionString property has not been initialized.

????

:D

it is sooo easy. but you can waste a lot of time on this one.

Here is what was my problem:
1.) web.config:

< appSettings >
< add key="aaa" value="server=swoosh\sqlserver2k5;database=swdb;uid=sa;password=;" />
< / appSettings >

2.) accessing:
string strConnRSS = System.Configuration.ConfigurationManager.AppSettings["bbb"];

yes.
aaa <> bbb

Hope this'll help someone :)
don't look for anything too complicated :)

Monthname in C# (C Sharp)

I think there's no Monthname (like is asp for ex. - which is weird)

But this is easy:

DateTime swTest = new DateTime(2000, 11, 1);
Page.Response.Write(swTest.ToString("MMMM"));

SQL - add zeros to the left

Ever needed to add zeros to the left of your string?
For example: instead of "1", you need "0001" displayed.
All this from SQL ...

I think this is an easy way of doing that:

select replicate('0', 6-len(convert(varchar(50), 'aaa'))) + convert(varchar(50), 'aaa')

You need to change the '6' if longer, and 'aaa' is your string ...

what do you think?
comment!

Calling a server side from client's Javascript

Did you ever need something like calling a server side thing from client's? Javascript ... ?

I think this is an easy way of doing that:

Response.Write ( " < script language="javascript"> function SetPmoR_Id() { document.all('" + lkbPmoRepAdd.ClientID + "').click(); } < / script>")

//or:

ClientScript.RegisterClientScriptBlock(this.GetType(), "somethn", " < script type='text/javascript'> function whatdoyouwannacalit() { try {document.all('" + yourlinkbutton.ClientID + "').click();} catch(e){} } < / script>");

what do you think?

you think there's an easier way? comment!

DataTable C# Primary Key column .Contains

I needed to do something like use DataTable in C#, set Primary Key column, and later on maybe a "contains" ... to see if my set of ID is inside the DataTable
Anyways: this is how I did it:

DataTable myFilteredTerms = objSomethn.getresultsinDT ...
DataColumn[] myPrimaryKeyColumn = new DataColumn[1];
myPrimaryKeyColumn.SetValue(myFilteredTerms.Columns[0], 0);
myFilteredTerms.PrimaryKey = myPrimaryKeyColumn;

...

if (myFilteredTerms.Rows.Contains(intTheIdYouAreLookingFor))
{
...
}

(first column is the one with the ID-s ...)

as always, if you got pro or contra ... tell me bout it

How to count enum elements in C#?

If you need to count the enum elements ...

this is how you do it:

int nYourNumber = Enum.GetValues(typeof(YourEnumName)).Length;

as always: if you have something neater ;) comment ... I'll update the post if needed ;)

Parse html table cells

I wanted to parse a html table and color the TD - s (background).
All of them.
I have a column that has links in it. My basic Idea was to change the background color of the TD when you click the link inside that. That's easy, but without some global (js?) variable, you don't know what was previously clicked, and you might want to turn that "off" :)
Anyways: this is how I did it:

function ParseTableAndColorTheBackground(objTbl, WhatColorShouldItBe)
{
var intNrOfTrs = objTbl.tBodies[0].rows.length;
for (var i=0; i < intNrOfTrs; i++)
{
objTbl.tBodies[0].rows[i].cells[1].style.backgroundColor = WhatColorShouldItBe;
}
}

you have a better way to do this?

comment me pls
;)

Ajax Databind – javascript error “unknown runtime error”

I had this error a few minutes ago. It was the weirdest thing. On a Save button (linkbutton) I had a gridview repopulated using Ajax. The Save linkbutton was also added as a trigger for the Ajax’s UpdatePanel. The save went well, saved in the database, but when it came to refresh the gridview, this javascript error popped up.

The fix was move the stuff around. Scriptmanager, UpdatePanel, ObjectDataSource … all of them inside the form … Anyways … if you have this error, you don’t necessarily have a big problem, you just need to figure out what did you put in the wrong place.

< form id="form1" runat="server">

< ajax:ScriptManager runat="server" ID="ScriptManager1" EnablePartialRendering="true">
< Services>
< ajax:ServiceReference Path="../Include/WebService.asmx" InlineScript="true" />
< /Services>
< /ajax:ScriptManager>
< ajax:UpdatePanel ID="up2" runat="server">
< ContentTemplate>
< asp:GridView>
...
< /asp:GridView>
< /ContentTemplate>
< Triggers>
< ajax:AsyncPostBackTrigger ControlID="lbnSave" EventName="Click" />
< /Triggers>
< /ajax:UpdatePanel>

< asp:ObjectDataSource ID="ods" ...
< SelectParameters>
< asp:QueryStringParameter ...
< /SelectParameters>
< /asp:ObjectDataSource>

< /form>

see ya

C# convert mm/dd/yyyy string to "5 minute(s) ago"

this is how utube does it:



For a forum-kinda thing I wanted to (C#) convert mm/dd/yyyy string to "5 minute(s) ago" - like Youtube does.

Here's how u I did it:
(please feel free to comment, get me a better way to do this :D)

/// < summary>
/// transform into "9 hours ago" or "8 days ago" etc ...
/// < /summary>
/// < param name="makeThisNiceDateFormat">some date string ... "mm/dd/yyyy"< /param>
/// < returns >example: "5 minute(s) ago"< /returns>
public static string ConvertDateToHoursAgo(string makeThisNiceDateFormat)
{
if (!IsDate(makeThisNiceDateFormat))
{
return makeThisNiceDateFormat;
}
else
{
DateTime dtOriginal = DateTime.Parse(makeThisNiceDateFormat);
//check if we need to display minutes
if (Math.Abs(DateDiff(DateInterval.Minute, dtOriginal, DateTime.Now)) < 60)
{
if (DateDiff(DateInterval.Minute, dtOriginal, DateTime.Now) == 0)
{
return "just now";
}
return (Math.Abs(DateDiff(DateInterval.Minute, dtOriginal, DateTime.Now)) + " minute(s) ago");
}
//check if we need to display hours
if (Math.Abs(DateDiff(DateInterval.Hour, dtOriginal, DateTime.Now)) < 24)
{
return Math.Abs(DateDiff(DateInterval.Hour, dtOriginal, DateTime.Now)) + " hour(s) ago";
}
//check if we need to display days
if (Math.Abs(DateDiff(DateInterval.Day, dtOriginal, DateTime.Now)) < 31)
{
return Math.Abs(DateDiff(DateInterval.Day, dtOriginal, DateTime.Now)) + " day(s) ago";
}
//check if we need to display months
if (Math.Abs(DateDiff(DateInterval.Month, dtOriginal, DateTime.Now)) < 12)
{
return Math.Abs(DateDiff(DateInterval.Month, dtOriginal, DateTime.Now)) + " month(s) ago";
}
return Math.Abs(DateDiff(DateInterval.Year, dtOriginal, DateTime.Now)) + " year(s) ago";
}
}

thoughts?
Note: I got an IsDate function in there which returns true/false ... in case someone tries to send me something that is not a date ...

nHibernate - a thing i like

I have a table: Act, and an other table Service Name
between them, there's an other table, that has actId and ServicenameId too ...

I needed somehow for the act object to see all the servicenames. somehow ... in a list maybe? or an ilist?

Here's how u do it:

< bag name="ServiceNameList" lazy="false" table="BOMMSS_ServiceNameAct">
< key column="TARFSS_ActId"/>
< many-to-many class="Atlas.Tariff.DataAccess.ServiceName,Atlas.Tariff.DataAccess" column="ServiceNameId"/>
< /bag>

in the mapping file (xml), and add something like this:

public IList ServiceNameList
{
get { return m_servicenamelist; }
set { m_isChanged |= (m_servicenamelist != value); m_servicenamelist = value; }
}

in the class def.

SQL and Underscore

Recently one of our QA told me that searching for a record with _ in it, won't work.
Actually it works, but the thing is that the '_' ( underscore ) is a reserved SQL character, and you have to know how to use it.

Originally the '_' ( underscore ) was designed to:
Let's say you want to find the word 'synopsys' in your table. But you don't if it was 'synopsys' or 'sinopsys' :)
In this case you should do this:

select Title from YourTable where title like 's_nopsys'

And this will find them both. (if any)

Back to Our case.
What our code looked like was something like this:

select Title from YourTable where title like '%_%'

Of course it returned everything from the table ... so QA was kinda right ... filter was not working.
But if QA would've known to search for '[_]' ... then it would've worked ...


It is doable, of course, not to have to add the '[' and the ']' in the search box ... but ... does it worth it?
If you can explain your 'client', how to use the '_' ( underscore ), and how to use the % sign ... in the search query ...
well maybe you don't have to change any of your code, because your 'client' might be happy to use these extra features.

If you can't talk your 'client' into this, you can try something like SQL's escape:

select Title from YourTable where title like '%\_%' escape '\'

or, add some extra character, because next time they'll search for '\' :)
so maybe do something like this:

select Title from YourTable where title like '%' + char(13) + '_%' escape char(13)

If you have some thoughts on this ... tell me about it ...

Youtube "bug" ?

Ever since the new youtube-player is in, i am experiencing this problem
frecvently. My colleagues can see a video , I get a (youtube) link from them, and
I can't see anything.

I re-installed flash player too.

If the videos are available in poor and also better quality one of them
it loads forever and never starts, the other one has this error.

Browser: ie6
allvideos: no
conntype: lan
errormsg: We're sorry, this video is no longer available
os: xp_pro

I wrote youtube. They told me that "Google Web Accelerator" might be the problem. That is right ... I uninstalled ... and it works ...

I thought I'd write down, maybe it'll help some1

;)

cheers

SQL Generate Insert script for pretty much any table ...

I am pretty proud of this code ... I made it a while ago, but still use it pretty often.
What does it do?
generates insert script for each row in a table ...
Let's say you create a table, you insert some data, and you'd like to generate a script to put the table and the data in an other database ... well ... you first script your table ... and then you run: sp_GenerateInsert 'YourTableNameHere'

(this is a stored procedure, and the result is only printing a string on the screen - you can run it whenever you want - won't harm your database structure OR data)

and that is it !

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GenerateInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GenerateInsert]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO


CREATE procedure sp_GenerateInsert
@TableName varchar(50)
as
Begin

-- Utility stored Procedure
-- Returns the list with inserts (re-create the table with all the data)

print '-- Swoosh 2004'
print ''
print 'SET IDENTITY_INSERT ' + @TableName + ' ON'
print 'GO'
print ''

declare @strSQL varchar(8000)
declare @ColumnList varchar(8000)
declare @ColumnListInOne varchar(8000)
declare @ColumnName varchar(8000)
declare @ColumnType int
declare @ID as integer

select @ID = id from sysobjects where type = 'U' and name = @TableName
set @ColumnList = ''
set @ColumnListInOne = ''

declare ccursor cursor for
select Name, Xtype
from syscolumns
where id = @ID

open ccursor
fetch next from ccursor into @ColumnName, @ColumnType
while @@FETCH_STATUS = 0
begin

Select @ColumnList = @ColumnList + @ColumnName + ', '

if (@ColumnType = (select xtype from systypes where name = 'int')) or (@ColumnType = (select xtype from systypes where name = 'float'))
begin
Select @ColumnListInOne = @ColumnListInOne
Select @ColumnListInOne = @ColumnListInOne + ' ltrim(rtrim(replace(isnull(convert(varchar(3000), ' + @ColumnName + '),''{|SWOOSH_NULL_REPLACER|}''),'''''''','''''''''''')))'
end
else
begin
Select @ColumnListInOne = @ColumnListInOne + '''''''''' + ' + '
Select @ColumnListInOne = @ColumnListInOne + ' ltrim(rtrim(replace(isnull(convert(varchar(3000), ' + @ColumnName + '),''''),'''''''','''''''''''')))'
end

if (@ColumnType = (select xtype from systypes where name = 'int')) or (@ColumnType = (select xtype from systypes where name = 'float'))
begin
Select @ColumnListInOne = @ColumnListInOne
end
else
begin
Select @ColumnListInOne = @ColumnListInOne + ' + ' + ''''''''''
end

Select @ColumnListInOne = @ColumnListInOne + ' + '', '' + '

fetch next from ccursor into @ColumnName, @ColumnType
end --while @@FETCH_STATUS = 0
close ccursor

deallocate ccursor

set @ColumnList = Left(@ColumnList,len(@ColumnList)-1)
set @ColumnListInOne = Left(@ColumnListInOne,len(@ColumnListInOne)-9)

set @strSQL = ''
set @strSQL = @strSQL + ' declare @AllValues varchar(8000)'
set @strSQL = @strSQL + ' set @AllValues = '''''
set @strSQL = @strSQL + ' declare ccursor cursor for '
set @strSQL = @strSQL + ' select ' + @ColumnListInOne + ' from ' + @TableName
set @strSQL = @strSQL + ' open ccursor '
set @strSQL = @strSQL + ' fetch next from ccursor into @AllValues '
set @strSQL = @strSQL + ' while @@FETCH_STATUS = 0 '
set @strSQL = @strSQL + ' begin '
set @strSQL = @strSQL + ' print ''insert into ' + @TableName + ' (' + @ColumnList + ') values ('' + replace(@AllValues, ''{|SWOOSH_NULL_REPLACER|}'', ''NULL'') + '')'' '
set @strSQL = @strSQL + ' fetch next from ccursor into @AllValues '
set @strSQL = @strSQL + ' end '
set @strSQL = @strSQL + ' close ccursor '
set @strSQL = @strSQL + ' deallocate ccursor '
exec (@strSQL )

print ''
print 'SET IDENTITY_INSERT ' + @TableName + ' OFF'
print 'GO'

End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Have fun with it ...
and if you come up with some cool "extension" / "upgrade" to it ... tell me about it ;)
p.s. there is an other version, that doesn't include the first column (generates the insert script without the Identity column ... which is also usable ...)

Google Analytics

Google Analytics - This is a screenshot:
(site traffic for one of my sites)



Question ...
Can anyone pls tell me what is "other" ?

nHibernate vs. Active Record

I have used nHibernate in 3 projects

read more on it here and here

I didn't like it because of the XML stuff.

next time i'll use Active Record (read more about that here), to see how that behaves, maybe i'll post something new afterwards ;)

Code review

I just had to code review a project i have never seen before.
The result has to be a Word document, it's nice if it is not only one page :)

I copy - pasted a checklist from an other document, with questions like:
"Is the code easy to read easy to understand?"
"Is the code properly commented?"
etc.
etc.

Then I tried to find stuff that I didn't consider ok to be there.
I found a few stuff ... code (variables, functions) that was declared over and over again, instead of using one public instance.
Yeah. I know. And it is boring too.

Have you ever done anything like this before?
Do you have any ideas how to make something like this?

Basically the main idea is this:
The client would like to have an "outsider"-s review of the code (because of course the ones that are building the project - they think it's perfect).
The client will probably not understand a thing of it, but it will probably have a clue about what's what.

Tell me if you have something like a checklist, or a list with common mistakes.
I'd like to see new things sometimes.

SQL server 2005 - generate scripts. Can i do this faster?

i am preparing scripts from DEV server, to an other environment.
step 1. script all the objects (drop script)
- 1.1. right click on tasks > select "Generate script"
- 1.2. select database click next
- 1.3. script behaviour: "Generate DROP statements only." also select
false for all the table/view options from bottom side, click Next
- 1.4. select stored procedures, user defined functions, views (this
way i won't forget any of them ... might have been modified by other
developer). click next
- 1.5. click "Select All", then Next (sps)
- 1.6. click "Select All", then Next (func)
- 1.7. click "Select All", then Next (views)
- 1.8. select script to file (any other option would be ok), change
file name to "drop scripts.sql" ? (doesn't matter) and click next
- 1.9. click finish
- 1.10 WAIT a LOT :)
then comes step 2 (2.1 - 2.10) with the only difference that at step
2.3. i'll select "Generate CREATE statements only."

i'll have 2 scripts.
in sql 2000 it was easier.

and now, the question:
can i make this somehow faster?
is there a script I could run and have the same result ...
or is there at least a way to have drop AND create-s generate all in
the same time?

I'll post an answer when i find a good one.

If you can help, just comment here pls.

Thanks

GridView Id column

If I make a "BoundColumn" for the Id, with visible - false, I can't get the Id ...

This one works:
(rowdatabound)

e.Row.Cells["mycolumn"].Style.Add("display", "none");

this way the Id is still there, you just cannot see it...

Gridview rowcount from Js

I needed to get some rowcont in a Js variable, to have a google-like "select all" (all from all pages) button, that i didn't want to show up if there is only one page in my gridview.

Radu helped me out with this idea:
(I am using this on rowDataBound, because I didn't want to use an other event, I already had this one ...)

ScriptManager.RegisterClientScriptBlock(grvAgentCommissionFeePercentage, this.GetType(), "ScriptIDAndTicksForRefreshnumber" + DateTime.Now.Ticks.ToString(), "multiplePages = " + grvAgentCommissionFeePercentage.PageCount.ToString() + ";", true);

if you need the whole nine yards, just comment ... i'll get back to you

Programming Languages and their Celebrity Equivalents

I found this "article" and i think it is very funny
:)

click here

Could not load file or assembly AjaxControlToolkit

I had this error today, after I installed my app on a server (wix installer btw)

what did i do?
googled :)
and then ...

delete all the data from
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\"my prj"\

and reinstalled the app

it worked

Javascript - properties for any HTML object

Sometimes you need to see what properties can you use for any given object.
Just copy-paste this:

var stuff = '';
for (p in document.Form1.elements)
stuff = stuff + ' ' + p;
alert(stuff);

( ... and replace the document.form1.elements with anything you'd like. For example document.body, or document.getElementById("someTDfromYourTable"), etc. ...)

Try-catch in SQL

Ever wanted Try-catch in SQL?
Here's your chance!
2005 only

BEGIN TRY
SELECT convert(smallint, '2003121')
END TRY
BEGIN CATCH
select 'errno: ' + ltrim(str(error_number()))
select 'errmsg: ' + error_message()
END CATCH

SQL Cursor Sample

I need this piece of code pretty often
A clean, almost empty, sql cursor example
Would be a lot better if you'd have a query, select it, right click, and "surround with: cursor" :)
But cannot be done just yet

DECLARE @CPilot INT

DECLARE cursorServProvDefServ CURSOR FOR
SELECT Company
FROM Company

OPEN cursorServProvDefServ
FETCH NEXT FROM cursorServProvDefServ INTO @CPilot
WHILE @@FETCH_STATUS = 0
BEGIN

PRINT @CPilot

FETCH NEXT FROM cursorServProvDefServ INTO @CPilot
END --while @@FETCH_STATUS = 0
CLOSE cursorServProvDefServ

DEALLOCATE cursorServProvDefServ