Sunday, November 11, 2012

access windows registry from sql server

This feature is really good
I got this from a post in a website.

use the following External Stored Procedures to access Windows Registry:

xp_Regread '<key>','<key value>','<string value>',@out output

xp_RegWrite
xp_RegDeletevalue
xp_regdeletekey
xp_regenumkeys

eg:

declare @out varchar(100)

EXECUTE master..xp_regread 'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\Microsoft SQL Server\Services\SQL Server',
'Name', @out OUTPUT

select @out

Thursday, September 13, 2012

startup stored procedures in sql server

if you want to run some stored procedures during sql server startup you need to enable the following configuration option and then restart sql server instance.

sp_configure 'scan for startup procs',1
go
reconfigure

This is an advanced startup option so you need to first enable the 'advanced options' configuration parameter and then enable this.

once this is enabled, sql server scans for stored procedures if any to run whenever it is started.

To configure the stored procedure which you want to run it at startup, do this:
sp_procoption @procname,'startup', @optionvalue (on or off)

The SP should be in master database if you want to configure it for start up and it should not have any input/output parameters.

Monday, August 27, 2012

Querying Active Directory from SQL Server


This is quite easy except the restriction on the number of records fetched.
After sql server 2008, the limit is 901, so 

a simple query to AD from sql is

select * from
openquery(ADSI,'select  samaccountname, cn, employeeID,mail,distinguishedname  from ''LDAP://DC=prod,DC=companydomain,DC=com''
where objectClass = ''person'' and objectClass = ''user'' and objectClass = ''organizationalPerson''
and objectClass <> ''contact'' and msExchHideFromAddressLists <> TRUE and accountExpires <> 0
')
but this fails with the below error, though it displays the 901 records.

Cannot fetch a row from OLE DB provider "ADsDSOObject" for linked server "ADSI".

The same query using openrowset is:

SELECT * FROM OPENROWSET('ADSDSOObject', '','SELECT cn, ADsPath FROM ''LDAP://DC=prod,DC=mycompany,DC=com'' ')

I read somewhere that the restriction in sql 2000 is 1000 records
This limit is defined by the "maxpagesize" in Active Directory.

If you want to view this value.
logon to a windows 2003 server, run-> ntdsutil.exe
and you can see this value using the command "show values".

you can use the same query from the command prompt using "dsquery" and get the records
the query is something like this:
dsquery * dc=prod,dc=companydomain,dc=com -scope -base -attr sAMAccountname employeeid department cn employeetype

using dsquery you can get all the data with a "-limit" parameter and dump it into a text file.
this is one way of overcoming the 1000 record limit in sql server.

To add the linked server in sql:
eXEC sp_addlinkedserver @server = 'ADSI', @srvproduct = 'Active Directory Services 2.5', 
@provider = 'ADSDSOObject', @datasrc = 'adsdatasource'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,@rmtuser=N'dd\sssss',@rmtpassword='ddddd'

sample queries:
SELECT top 900 * FROM OpenQuery
(ADSI, 'SELECT Name,SN,ST,cn,adspath FROM ''LDAP://OU=Users,OU=CSC-Managed,DC=au,DC=amp,DC=local''
where objectCategory=''Person'' and objectClass=''User''   ')

Monday, August 20, 2012

SAP got its own DB - MAXDB

yes SAP's MAXDB is the official SAP's DBMS.
I think SAP is now giving a strong competition to all other ERP vendors by having its own DB instead of depending on Oracle, Microsoft and other vendors.

looks like in the years to come all successful businesses would develop their own tools and products to support their IT systems instead of depending on an external vendor.
In such case it saves millions of dollars to the companies in license fees and support fees but of course they have to bear a one time investment to develop their products.

Wednesday, August 15, 2012

julian date conversion script


--date to julian 
(select (100+year('05-01-2010')-2000)*1000+ datediff(d, convert(datetime,('01/01/'+convert(char(4),year('05-01-2010')))),'05-01-2010')+1) 


-- julian to date format
select DATEADD(day, CAST(RIGHT(112228,3) AS int)-1,
dateadd(yy, 112228/1000, 0))

Tuesday, July 31, 2012

Databases and their types

How many types of databases one can imagine?

Chemical Databases
Financial Databases
Biological Databases
Kinematic Databases
Astronomical Databases
Neurological Databases
Defense databases
Astrological Databases
Astro physical databases
medical databases
demographic databases
geographic databases
Information Technology databases
Art databases

Is this an exhaustive list or can we imagine even more?
I'll wait for few more days to get more thoughts

Monday, July 30, 2012

Genes and Databases

I never heard about these and I'll post soon on all the below:


DDBJ, EMBL, GenBank,Ensembl,Uniprot,Swiss-prot

DDBJ is Japanese DNA Data Bank.

EMBL is Europe's primary nucleotide sequence resource

GenBank  is the NIH genetic sequence database, an annotated collection of all publicly available DNA sequences 

Ensembl: The Ensembl project produces genome databases for vertebrates and other eukaryotic species 
Uniprot: Universal Protein Resource

Swiss-Prot is the manually annotated and reviewed section of the UniProt Knowledgebase (UniProtKB)

Tuesday, June 26, 2012

FullText Catalog

If you want to enable a fulltext catalog then here is the statement:

use <dbname>
go
sp_fulltext_database 'enable' 

to stop/start population, rebuild or drop use this statement

use <dbname>
go
sp_fulltext_catalog '<catalog name>','create/drop/rebuild/stop', '<path if you are creating new>'

to get  all fulltext catalog information for that database


use <dbname>
go 
EXEC sp_help_fulltext_catalogs '<catalog name>'
 
to get fulltextcatalog property
 
use dbname
go 
select FULLTEXTCATALOGPROPERTY('catalog name','status')

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

Sunday, April 29, 2012

Script to capture Database growth

Lets say you have 10 sql servers in your environment and you have to capture growth for all the databases in all your sql servers.
Here are simple steps to do this:

1) create linked servers for all those sql servers
2) create an empty database and an empty table like this:
CREATE TABLE [dbo].[DB_size](
    [Instance_Name] [varchar](100) NULL,
    [DB_Name] [varchar](50) NULL,
    [Datafile_size_In_MB] [float] NULL,
    [updated_on] [datetime] NULL
) ON [PRIMARY]
 
3) create a job under sql server agent jobs and add the script as below

insert into dbo.db_size
select (select name from [instance1].MASTER.sys.servers where server_id=0) 'Instance' ,
(select name from [instance1].master.dbo.sysdatabases where dbid=a.dbid)   'database',
cast(SUM(size*8)as float(4))/1024 'Datafile Size in MB' ,GETDATE() 'date'
from [instance1].master.dbo.sysaltfiles a where dbid not in (1,2,3,4,32767) and fileid<>2
group by dbid,size
go
if you have 5 sql servers then prepare 5 insert statements (one for each instance) and add to the job step

4) Schedule your job to run once a week/month 

That's it

Monday, March 26, 2012

some important system tables and procedures

some system tables

select * from sys.database_recovery_status

select * from DBROLES
--gives info about the database roles in each database

select * from sys.identity_columns
-- lists down all the identity columns in your database

select * from information_schema.KEY_COLUMN_USAGE
-- lists down all the primary keys in that database

select * from information_schema.ROUTINES
-- lists down all the SPs and functions accessed by the current user in that database

select * from information_schema.SCHEMATA
-- lists all the schemas in the database

 Name: sp_add_data_file_recover_suspect_db 
 Purpose: Adds a data file to a suspect database and runs  recovery on the database.  This SP should only be used   on databases that have been marked suspect due to  insufficient data (error 1105) or log (error 9002) space. 

Name: sp_add_log_file_recover_suspect_db 


--sp_attach_single_file_db
used to attach databases when there is no log file and only data file exists.

sp_changegroup
-- used to move a user from one role to another

sp_clean_db_file_free_space
this internally executes dbcc cleanpage(dbid,fileid,pageno) for each page.
This procedure frees up each page in the specificed file and in the specified database.

sp_clean_db_free_space


sp_databases
lists all the databases, this is similar to sp_helpdb

sp_depends <objectname>
this is used to get dependencies of a particular object in that database.
you can also get this information from the below table
select * from sysdepends


sp_helpsort
this will give the collation setting

sp_helpstats
stats for the particular object

sp_server_info

Database versions in sql server

This is something interesting that I noticed recently.

select name,cmptlevel,version from sysdatabases
the above query shows how the versions are defined between sql server 2000, 2005, 2008, 2008 R2

in 2000 - version is 539 in sysdatabases sytem table
in 2005 - version is 611 in sysdatabases sytem table
in 2008 - version is 655 in sysdatabases sytem table
in 2008R2 - version is 661 in sysdatabases sytem table

When you restore a sql server 2000 backup on to a 2008R2 server, here is the what it shows in the output file:

Database 'database_test' running the upgrade step from version 539 to version 551.
Database 'database_test' running the upgrade step from version 551 to version 552.
Database 'database_test' running the upgrade step from version 552 to version 611.
Database 'database_test' running the upgrade step from version 611 to version 621.
Database 'database_test' running the upgrade step from version 621 to version 622.
Database 'database_test' running the upgrade step from version 622 to version 625.
Database 'database_test' running the upgrade step from version 625 to version 626.
Database 'database_test' running the upgrade step from version 626 to version 627.
Database 'database_test' running the upgrade step from version 627 to version 628.
Database 'database_test' running the upgrade step from version 628 to version 629.
Database 'database_test' running the upgrade step from version 629 to version 630.
Database 'database_test' running the upgrade step from version 630 to version 631.
Database 'database_test' running the upgrade step from version 631 to version 632.
Database 'database_test' running the upgrade step from version 632 to version 633.
Database 'database_test' running the upgrade step from version 633 to version 634.
Database 'database_test' running the upgrade step from version 634 to version 635.
Database 'database_test' running the upgrade step from version 635 to version 636.
Database 'database_test' running the upgrade step from version 636 to version 637.
Database 'database_test' running the upgrade step from version 637 to version 638.
Database 'database_test' running the upgrade step from version 638 to version 639.
Database 'database_test' running the upgrade step from version 639 to version 640.
Database 'database_test' running the upgrade step from version 640 to version 641.
Database 'database_test' running the upgrade step from version 641 to version 642.
Database 'database_test' running the upgrade step from version 642 to version 643.
Database 'database_test' running the upgrade step from version 643 to version 644.
Database 'database_test' running the upgrade step from version 644 to version 645.
Database 'database_test' running the upgrade step from version 645 to version 646.
Database 'database_test' running the upgrade step from version 646 to version 647.
Database 'database_test' running the upgrade step from version 647 to version 648.
Database 'database_test' running the upgrade step from version 648 to version 649.
Database 'database_test' running the upgrade step from version 649 to version 650.
Database 'database_test' running the upgrade step from version 650 to version 651.
Database 'database_test' running the upgrade step from version 651 to version 652.
Database 'database_test' running the upgrade step from version 652 to version 653.
Database 'database_test' running the upgrade step from version 653 to version 654.
Database 'database_test' running the upgrade step from version 654 to version 655.
Database 'database_test' running the upgrade step from version 655 to version 660.
Database 'database_test' running the upgrade step from version 660 to version 661.

 



 

Sunday, March 25, 2012

how to capture object permissions in the database in sql server

Here is the correct one:

select * from sys.database_permissions would give the correct permissions for all the users and all the objects.
Here is a sample query to list all the permissions;
i got this from the following url:
http://consultingblogs.emc.com/jamiethomson/archive/2007/02/09/SQL-Server-2005_3A00_-View-all-permissions--_2800_2_2900_.aspx


select USER_NAME(p.grantee_principal_id) AS principal_name,
        dp.type_desc AS principal_type_desc,
        p.class_desc,
        OBJECT_NAME(p.major_id) AS object_name,
        p.permission_name,
        p.state_desc AS permission_state_desc
from    sys.database_permissions p
inner   JOIN sys.database_principals dp
on     p.grantee_principal_id = dp.principal_id


The below one isn't the correct one, as per MSDN:
 http://msdn.microsoft.com/en-us/library/ms186233.aspx

"Returns one row for each table privilege that is granted to or granted by the current user in the current database"

use dbname
go
select * from information_schema.TABLE_PRIVILEGES

GRANTOR    GRANTEE    TABLE_CATALOG    TABLE_SCHEMA    TABLE_NAME    PRIVILEGE_TYPE    IS_GRANTABLE
dbo          user1         db1                  schema1        table1             DELETE        NO
dbo          user2         db2                  schema2        table2             INSERT         NO

object ids of system objects in sql server

Try this:
in sql server 2005
use master
go
select * from sysobjects where id<0
total 1773 rows returned and the ids are like the below: But why do the system objects have -ve ids???
-1042000603
-1041222755
-1037786908
..
..
Also fire the below query
select xtype,count(xtype) from sysobjects where id<0 group by xtype
the result is:
xtype    (No column name)
FN    27
IF    19
P     1277
PC    3
TF    12
V     286
X     149

in sql server 2008
select * from sysobjects where id<0
total rows returned are 1927

select xtype,count(xtype) from sysobjects where id<0 group by xtype
xtype    (No column name)
AF    1
FN    38
FS    4
IF    30
P     1348
PC    3
TF    13
V     354
X     136
 
in sql server 2008R2

select * from sysobjects where id<0 
 total rows returned are 1928

select xtype,count(xtype) from sysobjects where id<0 group by xtype
order by xtype
 
xtype    (No column name)
AF    1
FN    38
FS    4
IF    30
P     1348
PC    3
TF    13
V     354
X     137

how to refresh a database in sql server

Simple steps:

1) Take the backup of the source db
2) copy the backup
3) refresh the db at the destination; while refreshing if you haven't taken a backup of the destination db, it would prompt you that the tail of the log for this db hasn't been backed up and wouldn't allow you to restore; In that case use "replace" option in your restore database script.
4) After the restoration fix the logins:
use <dbname>
sp_change_users_login 'report'
go
once you get the logins which need to be fixed use this:
sp_change_users_login 'auto_fix','loginname'

If you don't have logins for the db users then they couldn't be fixed until  you create the logins.
Also you can't fix logins for windows users, you have to manually do it.