scheduling - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

scheduling

Author

Topic

barflyz
Starting Member

47 Posts

Posted - 2010-05-19 : 14:20:00

Hi all, I am connecting to a SQL server 2005 DB with a free tool I use to write the SQL. It is called WINSQL. Is there a way to schedule my SQL statement to run nightly? It has 4 different statements (drop table, re-create, set index etc) with a "GO" between each one. I can run manually easy but want to schedule every night at 2am.

thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-19 : 15:08:18

Yes you can do that via creating a job in the SQL Agent. Just put your script in a job step and schedule the job to run.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

barflyz
Starting Member

47 Posts

Posted - 2010-05-19 : 16:22:25

I dont have that so I need to get it. Can I just paste my sql into the step command box?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-19 : 16:33:48

What don't you have? The SQL Agent? If so, are you running Express version?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

barflyz
Starting Member

47 Posts

Posted - 2010-05-20 : 08:06:13

I am running a tool called WINSQL

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-20 : 13:09:51

That's now what I meant. I mean the edition of SQL Server 2005. In SQL Server 2005, you can create jobs to run things on a schedule via the SQL Agent.

I have no experience with WINSQL, I haven't even heard of it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

barflyz
Starting Member

47 Posts

Posted - 2010-05-21 : 15:25:39

I know have the agent but have no idea how to call out some simple SQL like on a schedule.

drop table dbo.bds_diabetic_values_tmg;

barflyz
Starting Member

47 Posts

Posted - 2010-05-21 : 15:30:18

Here is the full query I want to run nightly. I want to keep this qry as is also.

thanks

--first drop the existing table--

drop table dbo.bds_diabetic_values_tmg

go

--2nd, create the table dbo.bds_diabetic_values_tmg with one query--
--the diabetic view is not needed as everything was accompished with one qry below--
--this qry ends at line 321--

select a.ID as PID, a.last as LASTNAME,a.first as FIRSTNAME, a.middle as MIDDLENAME,a.phone1 AS PHONE1,a.phone2 AS PHONE2,a.dob AS DATEOFBIRTH,a.sex AS SEX,
(x.First + ' ' + x.Last) AS RESPONSIBLE_PROVIDER,

z.obsvalue as A1C_VALUE1,
z.obsdate as A1C_OBSDATE1,

v.obsvalue as A1C_VALUE2,
v.obsdate as A1C_OBSDATE2,

u.obsvalue as LDL_VALUE1,
u.obsdate as LDL_OBSDATE1,

w.obsvalue as LDL_VALUE2,
w.obsdate as LDL_OBSDATE2,

s.obsvalue as DLDL_VALUE1,
s.obsdate as DLDL_OBSDATE1,

t.obsvalue as DLDL_VALUE2,
t.obsdate as DLDL_OBSDATE2,

p.obsvalue as POLDL_VALUE1,
p.obsdate as POLDL_OBSDATE1,

q.obsvalue as POLDL_VALUE2,
q.obsdate as POLDL_OBSDATE2,

m.obsvalue as MICRO_VALUE1_1,
m.obsdate as MICRO_OBSDATE1,

n.obsvalue as MICRO_VALUE2_1,
n.obsdate as MICRO_OBSDATE2,

k.obsvalue as EYEEXAM_DATA1,
k.obsdate as EYEEXAM_OBSDATE1,

l.obsvalue as EYEEXAM_DATA2,
l.obsdate as EYEEXAM_OBSDATE2,

i.obsvalue as FOOTEXAM_DATA1,
i.obsdate as FOOTEXAM_OBSDATE1,

j.obsvalue as FOOTEXAM_DATA2,
j.obsdate as FOOTEXAM_OBSDATE2,

e.InsuredId AS PINSID,e.Name as PINSCO,
d.InsuredId AS SINSID,d.Name as SINSCO,r.InsuredId AS OINSID,r.Name as OINSCO,

g.obsvalue as OPTHTHALMOLOGIST,

f.diagnosis_codes AS DIAGNOSISCODES

into dbo.bds_diabetic_values_tmg

from
(select distinct (patientprofile.pid) as ID,
patientprofile.birthdate AS dob,
patientprofile.last,
patientprofile.first,
patientprofile.middle,
patientprofile.phone1,
patientprofile.phone2,
patientprofile.phone3,
patientprofile.sex,
CASE
WHEN DATEPART(day, patientprofile.birthdate) > DATEPART(day, getdate())
THEN DATEDIFF(month, patientprofile.birthdate, getdate()) - 1
ELSE DATEDIFF(month, patientprofile.birthdate, getdate())
END / 12 as AGE
from patientprofile INNER JOIN PROBLEM ON patientprofile.PID = PROBLEM.PID
where patientprofile.ISPATIENT = 'Y' AND patientprofile.PSTATUS='A' and
problem.stopreason is null and
patientprofile.LAST not in ('Test', 'Testing') and
PROBLEM.CODE like '%250.%' or PROBLEM.CODE like '%357.2'
or PROBLEM.CODE like '%362.0' or PROBLEM.CODE like '%366.41'
or PROBLEM.CODE like '%648.0')a

left outer join

--a1c results 1--
(Select pid,obsvalue,obsdate
from
(Select Dense_Rank() Over(Partition by pid Order by [obsdate]desc, [OBSVALUE])
as ROWID,* from obs where hdid = 28)Z
Where Z.ROWID =1
group by pid,obsvalue,obsdate)z
on a.id=z.pid

left outer join

--a1c results 2--
(Select pid,obsvalue,obsdate
from
(Select Dense_Rank() Over(Partition by pid Order by [obsdate]desc, [OBSVALUE])
as ROWID,* from obs where hdid = 28)Z
Where Z.ROWID =2
group by pid,obsvalue,obsdate)v
on a.id=v.pid

left outer join

--ldl results 1--
(Select pid,obsvalue,obsdate
from
(Select Dense_Rank() Over(Partition by pid Order by [obsdate]desc, [OBSVALUE])
as ROWID,* from obs where hdid = 3891)Z
Where Z.ROWID =1
group by pid,obsvalue,obsdate)u
on a.id=u.pid

left outer join

--ldl results 2--
(Select pid,obsvalue,obsdate
from
(Select Dense_Rank() Over(Partition by pid Order by [obsdate]desc, [OBSVALUE])
as ROWID,* from obs where hdid = 3891)Z
Where Z.ROWID =2
group by pid,obsvalue,obsdate)w
on a.id=w.pid

left outer join

--dldl results 1--
(Select pid,obsvalue,obsdate
from
(Select Dense_Rank() Over(Partition by pid Order by [obsdate]desc, [OBSVALUE])
as ROWID,* from obs where hdid = 21893)Z
Where Z.ROWID =1
group by pid,obsvalue,obsdate)s
on a.id=s.pid

left outer join

--dldl results 2--
(Select pid,obsvalue,obsdate
from
(Select Dense_Rank() Over(Partition by pid Order by [obsdate]desc, [OBSVALUE])
as ROWID,* from obs where hdid = 21893)z
Where Z.ROWID =2
group by pid,obsvalue,obsdate)t
on a.id=t.pid

left outer join

--poldl results 1--
(Select pid,obsvalue,obsdate
from
(Select Dense_Rank() Over(Partition by pid Order by [obsdate]desc, [OBSVALUE])
as ROWID,* from obs where hdid = 30)Z
Where Z.ROWID =1
group by pid,obsvalue,obsdate)p
on a.id=p.pid

left outer join

--poldl results 2--
(Select pid,obsvalue,obsdate
from
(Select Dense_Rank() Over(Partition by pid Order by [obsdate]desc, [OBSVALUE])
as ROWID,* from obs where hdid = 30)z
Where Z.ROWID =2
group by pid,obsvalue,obsdate)q
on a.id=q.pid

left outer join

--micro results 1--
(Select pid,obsvalue,obsdate
from
(Select Dense_Rank() Over(Partition by pid Order by [obsdate]desc, [OBSVALUE])
as ROWID,* from obs where hdid = 13557)Z
Where Z.ROWID =1
group by pid,obsvalue,obsdate)m
on a.id=m.pid

left outer join

--micro results 2--
(Select pid,obsvalue,obsdate
from
(Select Dense_Rank() Over(Partition by pid Order by [obsdate]desc, [OBSVALUE])
as ROWID,* from obs where hdid = 13557)z
Where Z.ROWID =2
group by pid,obsvalue,obsdate)n
on a.id=n.pid

left outer join

--eye results 1--
(Select pid,obsvalue,obsdate
from
(Select Dense_Rank() Over(Partition by pid Order by [obsdate]desc, [OBSVALUE])
as ROWID,* from obs where hdid = 2000009)Z
Where Z.ROWID =1
group by pid,obsvalue,obsdate)k
on a.id=k.pid

left outer join

--eye results 2--
(Select pid,obsvalue,obsdate
from
(Select Dense_Rank() Over(Partition by pid Order by [obsdate]desc, [OBSVALUE])
as ROWID,* from obs where hdid = 2000009)z
Where Z.ROWID =2
group by pid,obsvalue,obsdate)l
on a.id=l.pid

left outer join

--foot results 1--
(Select pid,obsvalue,obsdate
from
(Select Dense_Rank() Over(Partition by pid Order by [obsdate]desc, [OBSVALUE])
as ROWID,* from obs where hdid = 2541)Z
Where Z.ROWID =1
group by pid,obsvalue,obsdate)i
on a.id=i.pid

left outer join

--foot results 2--
(Select pid,obsvalue,obsdate
from
(Select Dense_Rank() Over(Partition by pid Order by [obsdate]desc, [OBSVALUE])
as ROWID,* from obs where hdid = 2541)z
Where Z.ROWID =2
group by pid,obsvalue,obsdate)j
on a.id=j.pid

left outer join

--optham results 1--
(Select pid,obsvalue,obsdate
from
(Select Dense_Rank() Over(Partition by pid Order by [obsdate]desc)
as ROWID,* from obs where hdid = 54169)Z
Where Z.ROWID =1
group by pid,obsvalue,obsdate)g
on a.id=g.pid

left outer join

-- select for CONCATENATE PROBLEM CODES--
(SELECT a.pid, dbo.Concat(pid) as diagnosis_codes
FROM problem a
GROUP BY a.pid)f
on a.id=f.pid

left outer join

(SELECT a.First, a.Last,b.pid
FROM PatientProfile b LEFT JOIN DoctorFacility a ON b.DoctorId = a.DoctorfacilityID
)x
on a.id=x.pid

left outer join

(SELECT c.Name, a.InsuredId, b.PId
FROM PatientInsurance a INNER JOIN PatientProfile b
ON a.PatientProfileId = b.PatientProfileId
INNER JOIN InsuranceCarriers c ON a.InsuranceCarriersId = c.InsuranceCarriersId
WHERE a.Inactive=0 AND a.OrderForClaims=1)e
on a.id=e.pid

left outer join

(SELECT c.Name, a.InsuredId, b.PId
FROM PatientInsurance a INNER JOIN PatientProfile b
ON a.PatientProfileId = b.PatientProfileId
INNER JOIN InsuranceCarriers c ON a.InsuranceCarriersId = c.InsuranceCarriersId
WHERE a.Inactive=0 AND a.OrderForClaims=2)d
on a.id=d.pid

left outer join

(SELECT c.Name, a.InsuredId, b.PId
FROM PatientInsurance a INNER JOIN PatientProfile b
ON a.PatientProfileId = b.PatientProfileId
INNER JOIN InsuranceCarriers c ON a.InsuranceCarriersId = c.InsuranceCarriersId
WHERE a.Inactive=0 AND a.OrderForClaims=3)r
on a.id=r.pid

where a.AGE between 18 and 75
group by a.ID, a.first,a.last,a.middle,a.phone1,a.phone2,
a.dob,a.sex,z.obsdate,v.obsdate,u.obsdate,w.obsdate,s.obsdate,t.obsdate,p.obsdate,q.obsdate,
m.obsdate,n.obsdate,k.obsdate,l.obsdate,i.obsdate,j.obsdate,f.diagnosis_codes,(x.First + ' ' + x.Last),
e.Name, e.InsuredId,d.Name, d.InsuredId,r.Name, r.InsuredId,z.obsvalue,v.obsvalue,u.obsvalue,w.obsvalue,
s.obsvalue,t.obsvalue,p.obsvalue,q.obsvalue,m.obsvalue,n.obsvalue,
k.obsvalue,l.obsvalue,i.obsvalue,j.obsvalue,g.obsvalue

--set constraints on table-

go

ALTER TABLE dbo.bds_diabetic_values_tmg
ALTER column pid numeric(19,0) NOT NULL;

--set pkey on table--

go

ALTER TABLE dbo.bds_diabetic_values_tmg ADD PRIMARY KEY(pid);

barflyz
Starting Member

47 Posts

Posted - 2010-05-21 : 15:41:12

How can I just call out the .sql file and run the query at 2am every night?

please help!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-21 : 16:34:13

There are two ways to handle this:
1. Create a job, add a job step, specify "Operating System" for type, and call your sql file via sqlcmd.exe in the command field, add a job schedule for 2am daily.
2. Create a job, add a job step, specify "Transact-SQL script for type, copy/paste the contents of the sql file into the command field, add a job schedule for 2am daily..

Here's an example sqlcmd call:
sqlcmd.exe -Sserver1\instance1 -E -iC:\Temp\SomeFile.sql -Ddatabase1

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources