SQL Unique constraint

ALTER TABLE [CMST_Country]
ADD CONSTRAINT uc_CountryCode UNIQUE (Code)

SQL split

found this on the net

i like this very much

CREATE TABLE #t (UserName VARCHAR(50))

DECLARE @sql VARCHAR(MAX)
SELECT @sql = 'INSERT INTO #t SELECT ''' + REPLACE(@UserList, ',', ''' UNION SELECT ''') + ''''
PRINT (@sql)
EXEC (@sql)

SELECT * FROM #t

IF OBJECT_ID('tempdb..#t') IS NOT NULL BEGIN DROP TABLE #t END

you can feedback also :)

Disable browser's back button ?

you can disable the back button, they say.

didn't test the code yet, will be back with feedback

< script type="text/javascript">

function noBack(){window.history.forward()}
noBack();
window.onload=noBack;
window.onpageshow=function(evt){if(evt.persisted)noBack()}
window.onunload=function(){void(0)}

< / script>

you can feedback also :)

Session state can only be used when enablesessionstate is set to true

I had a JavaScript function, that called a WebService, and I had this "Session state can only be used when enablesessionstate is set to true ..." error over and over again.

Everything was all right, in the web.config and in the "< % @ Page " too.

It was still giving me the error.

I made it work doing this:
start -- control panel -- administrative tools -- services -- asp.net state server

by default it is stopped. i started it.
that's it.

Select all from all tables all columns

I sometimes need to search for something that I know I added in one of the tables in a database, but i just don't know where I added it.
So for this one, I would need something to search in all the tables.
This case, only 'char', 'varchar', 'nchar', 'nvarchar' columns.

I searched on the internet, and found this guy.
The stored procedure is pretty cool, it works just the way I wanted.
I only added a SOUNDEX to it, so it can find even if you misspell the word.

it takes a while (around 10 seconds on a 400 tables database), but it is very cool.

I am thinking about how nice would it be to implement a search like this in your application (linking to the right screen, that might need a lot of parameters is probably the hard part)

So here's the code:

CREATE PROC ADMNSP_HotSearch
(
@SearchStr NVARCHAR(100)
)
AS
BEGIN
CREATE TABLE #Results (ColumnName NVARCHAR(370), ColumnValue NVARCHAR(3630), Accuracy INT)

SET NOCOUNT ON

DECLARE @TableName NVARCHAR(256), @ColumnName NVARCHAR(128), @SearchStr2 NVARCHAR(110), @mySQL VARCHAR(8000)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
SET @mySQL = 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) , 1
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE SOUNDEX(' + @ColumnName + ') = SOUNDEX(''' + @SearchStr + ''') AND ABS( LEN(' + @ColumnName + ') - LEN(''' + @SearchStr + ''') ) < 10 '

/*PRINT (@mySQL)*/

INSERT INTO #Results
EXEC (@mySQL)
END
END
END

UPDATE #Results SET Accuracy = 0 WHERE CHARINDEX(@SearchStr, ColumnValue) > 0

SELECT DISTINCT ColumnName, ColumnValue, Accuracy, CHARINDEX(@SearchStr, ColumnValue) AS [CHARINDEX] FROM #Results Order by Accuracy, ColumnValue
DROP TABLE #Results
END

Float vs. Decimal

I used float instead of decimal, I'll never do that again.
I found some stored procedures, that had numeric parameters used as varchar. I'll never do that either.
Check out why:

1st case:

declare @a as float
set @a = 13705.05
select @a

declare @b as varchar(100)
set @b = @a
select @b

2nd case:

declare @a as float
set @a = 137.86
select @a

declare @b as varchar(100)
set @b = @a
select @b

Run them, You'll get this:
1st case:

13705.05
13705

2nd case:

137.86
137.86

see my point?

How to add a constraint on a table that has 2 iD columns

How to add a constraint on a table that has 2 iD columns
The idea is that both id columns have allow nulls, but you would like to have an ID at least in one of them all the time.

This is how you do it:

ALTER TABLE [PTCTSS_FactorGroup] ADD
CONSTRAINT [CK_PTCTSS_FactorGroup_2cols] CHECK (((not([ContractSectionId] is null))) or ((not([CustomModuleId] is null))))
GO

Thanks 2 Cris ;)

Dos - glue (sql) files together

I wanted to concatenate files together
I had like 20 sql files
I knew there had to be a way ... other than the "hard" way.

Romica helped me out ;)

this is what he did:
opened a CMD
and go:

type *.sql >> t.sql

done

innerHTML not working in ie7

I had a bug
It looked like innerHTML is not working in ie7.
It was somthing like this:
function ShowLblError(msg)
{
document.getElementById("<%=lblError.ClientID%>").innerHTML = msg;
}

It worked fine in IE6, but not in IE7.
Finally I found out that it is working in IE7 too, I just had a panel with some weird height (it was ok in in IE6, ugly in IE7), and I just couldn't see the Label.

So innerHTML IS working in ie7
:)

Yahoo Messenger disappears

A friend of mine had this problem, and asked me to try to help him out.
After he logged on to Yahoo Messenger, the Messenger disappeared. Just plain and simple "closed himself" :)

I looked into it, even opened the Task Manager, and saw that the application just vanishes from there too.

I had no idea what to do at first, but I saw some forums saying that it was a virus, or something like this, related to the PC-s safety.
People was saying about using Hijackthis and stuff like that.

What I did was: installed Spybot Search and Destroy. Ran it once, found 18 problems. I never even checked what was all that, I just clicked on "Fix selected problems". Problem fixed ;)