Last day of the holiday

 

As I’m musing what is my new job going to be like, how would the job be? how is the working environment? Are the people over there helpful? can I survive? What is my strategy? the journey to workplace is quite far too and will this job affect my study? Is this the right time to move to a new company?  Seem like more and more things were added into my thought. I’m not sure what my future is. How to proceed with my life as I’m already nearly half way through and things are getting tougher. Continue reading

Sending email through store procedure

Database email setup

  • Double click ‘Databse Mail’ to start setup

e1

Create mail configuration

  • select as below

e2

  • key in the profile name and click ‘Add’

e3

  • key in email address and the server ip address

e4

  • add the SMTP email address and server ip address

e5

  • click ‘Next’

e6

  • click ‘Finish’

e7

 

Store Procedure

Parse in the profile to be use to send email, recipient email, email body message and email subject.

e8

 

Selecting data from MySql in MSSQL

Setup Link Server

  • right click to add new linked server. Fill in all the appropriate details accordingly. Follow the database name in mysql to be use as data source over here.
  • Provider string: DRIVER={MySQL ODBC 5.3 ANSI Driver};SERVER=ip address;PORT=port number;DATABASE=mt4_demo;USER=username;PASSWORD=password;Option=3

l2

  • test connection and save it.

l6

 

  • after setup

l4

  • To query the data from the table

select * FROM OPENQUERY(MT4DBDEMONew, ‘SELECT * FROM mt4_trades’)

 

 

 

Running SSIS package in a database scheduler

Copy excel file/ csv file into the table daily repetitiously.

Below is the guide to set the SSIS and use the generate package file in the job scheduler in MSSQL.

SSIS Setup

  •  Tasks -> Import data

s1

  • client ‘Next’

s2

  • select ‘Flat file source’

s3

  • Select the file, change text qualifier to ‘ ” ‘, uncheck the check box if first row is data

s4

  • click ‘Next’

s5

  • Specify the database server name, type of authentication and database name

s6

  • assign the destination table where data copy into. Click ‘Edit Mapping’ to set the mapping column

s7.png

  • map the column in csv with the column in the table

s8

  • check and click ‘Next’

s12

  • Only Enterprise version allow to save package (Express version cannot). check the Save SSIS package.

s9

  • click ‘Next’

s10

  • specify the package name and click ‘Next’

s11

  • click ‘Finish’

s13

Database Job Scheduler

  • Create a new job under SQL Server Agent

s14

  • Set the step to execute the package

s15

  • set the package file to be executed

s16

  • set the scheduler time

s17

Job done!! you can test it before leave it for everyday process.