Monday, May 21, 2012

sub columns for each column of a database

Sub Columns in a Main Column in a table of a database??
Interesting concept but not sure if this can be implemented...

eg: my db name is db1
my table name is table1
columns are like this:
1) Book Name - ColA
2) Author Name - Colb
3) Location details - ColC

Sub Columns:
for ColA the sub columns are something like:
a) Edition number
b) published date

for ColB the sub columns are something like:
a) about the Author
b) Author career

The idea is to store all the sub columns in separate pages other than the data page where the main column data is stored. 
This concept serves for the only reason where we want to keep all that less important data somewhere in our tables but never really use it on a day to day basis.
I'll write more info if I get more.

Monday, May 7, 2012

No SQL and Open source

The list goes on and on.

There are so many open source no sql databases these days that its difficult to choose one unless you have specific requirements.
I never thought the open source database systems would be part of so many applications.
Here is some I know:

couchdb
mongodb
leveldb
simpledb
mariadb
memcachedb
ravendb
geniedb
extremedb
tokutek
googlesql
queplix
eyedb
sterling
AvocadoDB
Voltdb

There are so many and many of them you can find @ http://nosql-database.org/

some more here:
CrowdDB
Shark

Sunday, May 6, 2012

SSIS Package migration issue with the password field encryption

The below link is a lifeline to anyone stuck with SSIS packages migrations/installations.

http://msdn.microsoft.com/en-us/library/dd440760.aspx

If you set your SSIS packages with "EncryptSensitiveWithUserKey" then you would face issues when you migrate this package from one environment to the other.

The packages would defnitely run good with "dtexec" or "dtsrun" commands from the command line but the issue is only when you want to schedule your package using sql server agent.
Because sql server agent account doesn't have sufficient permissions to decrypt the password field in the package, it fails to make the connection to the specified data source and hence the package would eventually fail.

Carla's link mentioned above in MSDN clearly explains this and gives three solutions to fix the problem.

the solutions are:
1) move the permissions and controls of the package to DB roles
2) change the protection level with no encryption
3) keep the protection level with a password so you can decrypt the password in your sql server agent job