sql server import data from excel file

first of all, need enable the features by running following query:

sp_configure 'show advanced options', 1;

RECONFIGURE;

sp_configure 'Ad Hoc Distributed Queries', 1;

RECONFIGURE;

command "reconfigure" is use to install the configuration settings u changed.

After that, run the following query to import data from excel file:

SELECT *

INTO databasename.schema.tablename

FROM OPENROWSET('MSDASQL',

'Driver={Microsoft Excel Driver (*.xls)};DBQ=filepath',

'SELECT * FROM [sheet1$]')

This query will automatically help to create the table with given table name.

drop tablespace when datafiles accidentally deleted

1. Run SQL*Plus.
2. Connect to database as SYSDBA with this query:

   CONNECT / AS SYSDBA

3. Mount the database instead of starting it up:

   STARTUP MOUNT;

4. Issue the following command to bring the missing datafile offline so that Oracle won’t trying to connect and access the datafile anymore:

   ALTER DATABASE DATAFILE ‘’ OFFLINE DROP;

   Repeat the command for every datafiles that unaccounted for.

5. Now start the database proper:

   ALTER DATABASE OPEN;

6. As the tablespace has damaged, drop it to recreate from fresh backup.

   DROP TABLESPACE INCLUDING CONTENTS;

create new domain in window server 2003

go to run command run:

DCPROMO

follow the instruction.

vb.net datetime to string format

Custom Format String

DateTime now = new DateTime(2006, 9, 07, 15, 06, 01, 08,DateTimeKind.Local);

now.ToString();      //"09/27/2006 15:06:01"

 

Year

now.ToString("%y");   //"6"

now.ToString("yy");   //"06"

now.ToString("yyy");  //"2006"

now.ToString("yyyy"); //"2006"

 

Month

now.ToString("%M");    //"9"

now.ToString("MM");    //"09"

now.ToString("MMM");   //"Sep"

now.ToString("MMMM");  //"September"

 

Day

now.ToString("%d");    //"7"

now.ToString("dd");    //"07"

now.ToString("ddd");   //"Thu"

now.ToString("dddd");  //"Thursday"

 

Hour

now.ToString("%h");    //"3"

now.ToString("hh");    //"03"

now.ToString("hhh");   //"03"

now.ToString("hhhh");  //"03"

now.ToString("%H");    //"15"

now.ToString("HH");    //"15"

now.ToString("HHH");   //"15"

now.ToString("HHHH");  //"15"

 

Minutes

now.ToString("%m");    //"3"

now.ToString("mm");    //"03"

now.ToString("mmm");   //"03"

now.ToString("mmmm");  //"03"

 

Seconds

now.ToString("%s");    //"1"

now.ToString("ss");    //"01"

now.ToString("sss");   //"01"

now.ToString("ssss");  //"01"

 

Milliseconds

now.ToString("%f");    //"0"

now.ToString("ff");    //"00"

now.ToString("fff");   //"008"

now.ToString("ffff");  //"0080"

now.ToString("%F");    //""

now.ToString("FF");    //""

now.ToString("FFF");   //"008"

now.ToString("FFFF");  //"008"

 

Kind

now.ToString("%K");    //"-07:00"

now.ToString("KK");    //"-07:00-07:00"

now.ToString("KKK");   //"-07:00-07:00-07:00"

now.ToString("KKKK");  //"-07:00-07:00-07:00-07:00"

 

DateTime unspecified = new DateTime(now.Ticks,DateTimeKind.Unspecified);

unspecified.ToString("%K");   //""

 

DateTime utc = new DateTime(now.Ticks, DateTimeKind.Utc);

utc.ToString("%K");           //"Z"

 

TimeZone

now.ToString("%z");     //"-7"

now.ToString("zz");     //"-07"

now.ToString("zzz");    //"-07:00"

now.ToString("zzzz");   //"-07:00"

 

Other

now.ToString("%g");    //"A.D."

now.ToString("gg");    //"A.D."

now.ToString("ggg");   //"A.D."

now.ToString("gggg");  //"A.D."

 

now.ToString("%t");    //"P"

now.ToString("tt");    //"PM"

now.ToString("ttt");   //"PM"

now.ToString("tttt");  //"PM" 

Prepared code 

Year Month Day Patterns:

d      = "MM/dd/yyyy"
D      = "dddd, dd MMMM yyyy"
M or m = "MMMM dd"
Y or y = "yyyy MMMM"

Time Patterns:
t      = "HH:mm"
T      = "HH:mm:ss"

Year Month Day and Time without Time Zones:
f      = "dddd, dd MMMM yyyy HH:mm"
F      = "dddd, dd MMMM yyyy HH:mm:ss"
g      = "MM/dd/yyyy HH:mm"
G      = "MM/dd/yyyy HH:mm:ss"

Year Month Day and Time with Time Zones:
o      = "yyyy'-'MM'-'dd'T'HH':'mm':'ss.fffffffK"
R or r = "ddd, dd MMM yyyy HH':'mm':'ss 'GMT'"
s      = "yyyy'-'MM'-'dd'T'HH':'mm':'ss"
u      = "yyyy'-'MM'-'dd HH':'mm':'ss'Z'"
U      = "dddd, dd MMMM yyyy HH:mm:ss"

Unable to instantiate XML DOM document, please verify

Once i meet an error as stated in subject.
It was cause by the office 2007
Some able to fix it by going to add/remove program and repair the MSXML6 parser but i unable to fix the error.
At the end, i solved it with a easy method.
Here is a simplest way to fix the error:
go to command prompt and type regsvr32 msxml6.dll
:)

use relative path for ssis packages dtsConfig files

to make the ssis package portable. A dynamic variable is important.

in the ssis, we use the dtsConfig file to store those dynamic variable.

however, the file path of the dtsConfig is absolute.

After seaching the internet. I get a method to make it become relative.

scenario 1: run from the command prompt

enter the following command at cmd:

dtexec /File Packagepath.dtsx /Conf configurationpath.dtsConfig

scenario 2: run from the SQL Server Agent

create a new job and insert a new step, select the type as "Operating system (CmdExec)"

enter the above command into the command box