Enjoy everyday in our life

I started to use information_schema related views quite often now, so I think it’s about time for me to write a blog about the most common way I use it.

1. Get list of table name from current login database:
   select table_name from INFORMATION_SCHEMA.tables where table_type = ‘BASE TABLE’ order by table_name
    
2. Get list of view name from current login database:
   select table_name from INFORMATION_SCHEMA.tables where table_type = ‘VIEW’ order by table_name
    
3. Get the current login database name:
   select distinct table_catalog from INFORMATION_SCHEMA.tables
    
4. Get list of column name and some important attributes (like data type, nullable) belong to a specific table:
   select table_name,column_name,data_type,is_nullable from INFORMATION_SCHEMA.COLUMNS where table_name =‘fnd_tbl_version’
    
5. Get the detail of a foreign key or primary key for a specific table:
   SELECT k.table_name,k.column_name field_name,c.constraint_type,CASE c.is_deferrable WHEN ‘NO’ THEN 0 ELSE 1 END ‘is_deferrable’,
   CASE c.initially_deferred WHEN ‘NO’ THEN 0 ELSE 1 END ‘is_deferred’, rc.match_option ‘match_type’,rc.update_rule ‘on_update’,rc.delete_rule ‘on_delete’,ccu.table_name ‘references_table’, ccu.column_name ‘references_field’,k.ordinal_position ‘field_position’ FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE k LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS c ON k.table_name = c.table_nameAND k.table_schema = c.table_schema AND k.table_catalog = c.table_catalog AND k.constraint_catalog = c.constraint_catalog AND k.constraint_name = c.constraint_name LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc ON rc.constraint_schema = c.constraint_schema AND rc.constraint_catalog = c.constraint_catalog AND rc.constraint_name = c.constraint_name LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON rc.unique_constraint_schema = ccu.constraint_schema AND rc.unique_constraint_catalog = ccu.constraint_catalog AND rc.unique_constraint_name = ccu.constraint_name WHERE k.constraint_catalog = DB_NAME() AND k.table_name = ‘fnd_tbl_user_role_link’
   ORDER BY k.constraint_name,k.ordinal_position
   – I found this query on http://www.alberton.info/sql_server_meta_info.html
   

• Read More »

SQLCMD is a command line query tool shipped with SQL Server 2005. If you need to run a batch file to call a query or procedure, and then retrieve the output in a file, then it’s a very useful tool to use.

Basic syntax can be found in http://msdn2.microsoft.com/en-us/library/ms162773.aspx but let me show you few example of how to use it.

Find out the available parameters available to use:
C:\sqlcmd -?

By the way, keep in mind that all parameters pass in are CASE SENSITIVE!

To run in an interactive mode by passing login information and then you can type in sql query and see the result like how you use oracle sqlplus. Use -S for server name, -d for database name, -U for DB user name, -P for password:
C:\sqlcmd -S YourServerName -d YourDBName -U DbUserName -P password
1> select first_name from Users
2>go
first_name
---------------
Jane Doe
John Smith(2 rows affected)
1> exit
C:\

With trusted connection to run a query and save output into a text file; -E for trusted connection, -o for output file name:
C:\sqlcmd -E -S YourServerName -d YourDBName -q "select count(*) from Users" -o C:\output1.txt

To get rid of the header, pass in “-h -1″:
C:\sqlcmd -E -S YourServerName -d YourDBName -q "select count(*) from Users" -o C:\output1.txt -h -1

To pass in an sql script file and generate output; -i for input file:
C:\sqlcmd -E -S YourServerName -d YourDBName -i user_data.sql -o C:\output1.txt

Enjoy and hope this help!

Tracy

• Read More »

Datediff is the function for you if you want to calculate different between 2 dates, and make sure you supply the unit you would like to measure :

select ‘Year’, datediff(yyyy, ‘2005-01-22 10:20:00′, ‘2008-01-22 16:20:00′)
=> 3

select ‘Month’, datediff(m, ‘2008-01-22 10:20:00′, ‘2008-12-22 16:20:00′)
=> 11

select ‘Day’, datediff(d, ‘2008-01-22 10:20:00′, ‘2008-01-29 16:20:00′)
=> 7

select ‘Hour’, datediff(hh, ‘2008-01-22 10:20:00′, ‘2008-01-22 16:20:00′)
=> 6

select ‘Minute’, datediff(mi, ‘2008-01-22 10:20:00′, ‘2008-01-22 16:20:00′)
=> 360

select ‘Second’, datediff(s, ‘2008-01-22 10:20:00′, ‘2008-01-22 10:20:30′)
=> 30

• Read More »

This is just a quick reference about something very handy when you are doing development for data loading. Do you ever have the table design done, have all your foreign key set, and then you load the test data… over and over again, now you start with all those high number key in your table?

Yes, you can *truncate* table and it will all start new! But wait… if you have foreign key, you will get this error:
Server: Msg 4712, Level 16, State 1, Line 1
Cannot truncate table '[Table Name]' because it is being referenced by a FOREIGN KEY constraint.


Why? Because *trucate* first drop the table, then recreate the table!
Sure I can use delete from [table name], but the identity number just keep increasing!

Here is one easy command to reset the identity seed start number:
dbcc checkident ([table name], reseed, 1)


Keep in mind that please do not ever do this if your table is full of valuable data! If you reseed a identity column which happen to be your primary key column, guess what happen? next insert you are going to get a big fat error!
Therefore, use it intelligently!

viagra cialis canada
keywords tramadol
order valium
www cialis
no perscription xanax
valium overnight
www adipex com
tramadol drug admin
viagra doses
tramadol florida pharmacy
tramadol dose rate
soma drug toxicity
in ingredient tramadol
lexapro and phentermine
identify real cialis
soma tri
flomax viagra
viagrafix corporation
viagra buy viagra
discountusdrugs.com hepsera soma
tramadol adverse reactions
soma music
snort xanax
generika levitra
free viagra samples
tramadol hydrochloride ultracet
phentermine.html
viagra indication
phosphodiesterase inhibitors viagra
overnight adipex
phentermine ionamin
levitra viagra compare
qoclick tramadol
tramadol norting
herb viagra
tramadol click here
adipex diet pills
tramadol 100 tablets
precaution soma
soma studios chicago
tramadol thomas method
raja soma
poker valium
line pharmacy phentermine
generic tramadol 377
phentermine buy wholesale
get prescribed xanax
discount generic viagra
cheap keyword tramadol
sertraline tramadol and
350mg soma weblog.ro
viagra discussion
levitra message board
valium online canada
tramadol and dentistry
valium addiction
without prescription tramadol
watson soma online
generic cialis overnight
phentermine diet pills
female viagra drug
tramadol 120 quantity
diet levitra
pharmacy soma
generic cialis cheap
drug screening tramadol
generic viagra cialis
medicine called tramadol
levitra link spammers.chongqed.org
shooting up xanax
phentermine shipped cod
viagra warning

• Read More »

I set up sqlmail in our SQL Server 2000 server in a Windows 2000 machine to send out email reminder. I have a daily schedule job to run a stored procedure that I created and execute xp_sendmail to send out the email reminder.
One day it just stopped working…

I went to enterprised manager to check on the job history and found the job is still in “executing” phases and never ended, it has been running for more than 4 hours! I tried restart sqlserver agent, killed the hung process and the process just didn’t go away(stay at “KILLED\ROLLBACK” status even the job is just a simple xp_sendmail task). I tried xp_stopmail and got the message:
xp_stopmail: Failed to stop SQL Mail session.

Many posts I found online recommended to restart the SQL Server services but I really didn’t want to have downtime at all for my users, so I continue reading and search online…

Fortunately, I came across a post regarding killing the “MAPISP32.EXE” from the Task Manager. Running out of choice so I gave it a try, and right after I killed the “MAPISP32.EXE” process my “KILLED\ROLLBACK” processes went away(finally! after hung there for 8 hours)! I then went to query analyzer and issued
xp_stopmail
xp_startmail


After that I tried to send out a test email using xp_sendmail and everything just work fine!

I came across the problem again this morning and here are the steps I did to make the hung SQLMail go away:

Step 1: login to the Server, opened up Task Manager, find MAPISP32.EXE and ended the process

Step 2: open up Query Analyzer, execute
xp_stopmail
xp_startmail
to restart SQLMail

Step 3: Test the SQLMail by execute
exec master.dbo.xp_sendmail @recipients = 'youremail@yourdomain.com',
@subject = 'test sqlmail after restart',
@message = 'test sqlmail after restart - email body'


Good luck and hope this help.

• Read More »

I used convert() function a lot since SQL Server does not have “Date” data type so all date you stored in system are always have a time attached to it. However, a lot of time when I created the report, I want to group the data by date, then the convert() function become a very handy function to help me to “trunc” (if you are oracle user then you know what I’m talking about) the datetime column.

Example #1: differences between before/after using the convert

select convert(varchar, getdate(),101) as date_only, getdate() as full_date_time

date_only full_date_time
----------- -------------------------
05/23/2007 2007-05-23 18:03:20.450


Example #2: Group result by day — e.g.: how many users registered each day

select convert(varchar(10), registered_date, 101) as registration_date, count(*) as user_count
from user_registration
group by convert(varchar(10), registered_date, 101)

registration_date user_count
---------------- -----------
05/15/2007 2
05/17/2007 5
05/18/2007 1
05/19/2007 10
05/20/2007 8


Example #3: includes all data in a specify date

select count(*) as user_count
from user_registration
where convert(varchar(10), registered_date, 101) = '05/15/2007'

user_count
-----------
2

Note that if you write your query as
select count(*) as user_count
from user_registration
where registered_date= '05/15/2007'

user_count
-----------
0

You are very highly possible to get zero row count, because the user have to be registered at exactly ‘2007-05-15 00:00:00′ to be able to qualify for your query criteria! See the difference?

• Read More »

This is a test post, I’ll start thinking about what to write soon…

Tracy

• Read More »

• Read More »