December 7, 2010 at 12:54 pm
I am a Newbi DBA and I manage a db that requires that i run two update queries around the first of every month. I have both queries saved (a senior dba wrote them) and all i do is copy and paste the queries one at at time into a query window and run them. The only glitch is that for both i have to add the the current Period (Period = YYYYMM example for December 2010 it would be 201012) to the query before i run it.
Is there a way i can configure it so that i can run something and a box would pop up asking for the period, we input the period and click ok and then the queries run. I beleive i would need a Stored Procedure, but not sure and i dont know how to get the Period in.
I could grab the period from the PERIOD table which alway has the most current PERIOD as the last record. In other words it would be Current period = 201012.
I can provide you the full query if needed.
Please let me know
Jeff
December 7, 2010 at 1:00 pm
Don't think we'll need the full query for this Jay.
You've got a two step process here that you're dealing with. First, you want to get rid of the need for the messagebox to enter in the value. You definately want to connect off the table with your current period since it's already available. This will make your life much easier.
The second piece you're looking for to do scheduling is SQL Agent. This will allow you to create a job with a task for Execute SQL. In there, put the call to your procedures, and set it up to run when you need it to.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 7, 2010 at 2:12 pm
Thanks for your reply
So you are saying that i would create an SQL agent job to run on the first of every month and put the query in the job. i would also get the period from the PERIOD table as well.
I can understand how to create the Job, but how would i Pull the period from the PERIOD table. The Period is at the end of the where clause.
I may get the query and put it in just for you to see what it does.
Jeff
December 7, 2010 at 2:17 pm
jayoub1 (12/7/2010)
Thanks for your replySo you are saying that i would create an SQL agent job to run on the first of every month and put the query in the job. i would also get the period from the PERIOD table as well.
I can understand how to create the Job, but how would i Pull the period from the PERIOD table. The Period is at the end of the where clause.
I may get the query and put it in just for you to see what it does.
Use a JOIN in your query to the table for the proper field instead of a parameter in the where clause.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 7, 2010 at 2:39 pm
I understand how to join tables, but dont know how it would help. Once the tables are joined what then.
Sorry, I am a newbi
Jeff
December 7, 2010 at 2:43 pm
jayoub1 (12/7/2010)
I understand how to join tables, but dont know how it would help. Once the tables are joined what then.Sorry, I am a newbi
Alright, let's see what you got. If you need some help check out the first link in my signature. I'll need more details to help you specifically.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 7, 2010 at 6:47 pm
The following are the two queries that i run after the accounting staff closes the books for the month/period. the queries where written by a DBA to change the check number for all the transactions that occured that month. There are two transaction type and one query for each type. So what i do is put in the PERIOD = 201011 for November that just closed and execute. The database is then updated.
I wonder how i can automate this process or make is so that the new network admin (who has no db experience) can just click and do it.
Let me know what you think.
•CHK2DATE:
update cmledg set chkdesc=right('0'+rtrim(convert(char,datepart(mm,trandate))),2) + right('0'+rtrim(convert(char,datepart(dd,trandate))),2) + right('0'+rtrim(convert(char,datepart(yyyy,trandate))),2) where chkdesc ='0000000' and PERIOD = ‘YYYYMM’
I change the period to the period just closed in both queries
DEBIT2DATE
update cmledg set chkdesc='d' + right('0'+rtrim(convert(char,datepart(mm,trandate))),2) + right('0'+rtrim(convert(char,datepart(dd,trandate))),2) + right('0'+rtrim(convert(char,datepart(yyyy,trandate))),2) where chkdesc like 'd%' and period='YYYYMM'
Jeff
December 8, 2010 at 11:23 am
You'd do something like this:
update c
set chkdesc = right('0'+rtrim(convert(char,datepart(mm,trandate))),2) + right('0'+rtrim(convert(char,datepart(dd,trandate))),2) + right('0'+rtrim(convert(char,datepart(yyyy,trandate))),2)
FROM
cmledge AS c
JOIN
<periodTable> AS pt
ON c.period = pt.period
where chkdesc ='0000000'
Note there's some psuedocode in there so you'll have to edit it a bit for the periodtable component.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 8, 2010 at 12:59 pm
I see and will look closer at it.
Thanks for your help
Jeff
December 9, 2010 at 8:35 am
Another option that will allow you to keep your current update statement unchanged is to declare a variable for 'YYYYMM'.
DECLARE @YYYYMM varchar (6)
--This is where you set the variable equal to the last record in the "periods" lookup table
Select Top 1 @YYYYMM = CurrentPeriod
From LookupTable L
Order by CurrentPeriod Desc
--Update Statement
update cmledg set chkdesc=right('0'+rtrim(convert(char,datepart(mm,trandate))),2) + right('0'+rtrim(convert(char,datepart(dd,trandate))),2) + right('0'+rtrim(convert(char,datepart(yyyy,trandate))),2) where chkdesc ='0000000' and PERIOD = @YYYYMM
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply