T-SQL (SS2K8) :: Query To Predict Future Job Schedules From MSDB Tables
May 8, 2014
I want to query my msdb job and jobschedule related tables to generate a list of runtimes for each of these jobs for the next day or any future date. This query should output JobID, Run_Date(YYYYMMDD), and Run_Time(HHMMSS).
If I have 3 jobs with...
Job#1 scheduled to run once every 4 hours between 6 AM and 10 PM
Job# 2 scheduled to run every 15 minutes between 11 AM and 1 PM
Job# 3 scheduled to run every minute between 4 PM and 4:15 PM
Table 1 has "Gender" field with "Male" and "Female" in it, table 2 has "Gender" field with "M" and "F" in it. a query to compare data and list the differences.
I have a sql server express 2005 database with a table named Events with a column named Date (datetime datatype). I want a query that will display all rows that are either current or future but not past. I suspect there is a simple way of doing this. As a Newbie searching archived threads this is what I have come up with so far. I determine the number of days from present:SELECT DATEDIFF(day, Date, GETDATE()) AS NumberOfDays FROM EventsThis yields number of days from present with positive numbers in the past and negative numbers in the future. Thus setting a WHERE clause to <= 0 would limit my results to present or future events. Something like this:SELECT * FROM EventsWhere DATEDIFF(day, Date, GETDATE()) AS NumberOfDays <= 0The error message states: "Incorrect syntax near the keyword 'AS'"This feels like a clumsy way to approach this problem, but I have to start where I am.Any suggestions on how to proceed will be greatly appreciated.
I have three tables EmpIDs,EmpRoles and LatestRoles. I need to write a select Query to get roles of all employees present in EmpIDs table by referring EmpRoles and LatestRoles.
Where I stuck : The condition is first look into table EmpRoles and if it has more than one entry for a particular Employee ID than only need to get the Role from LatestRoles other wise consider the role from EmpRoles .
Employee ID 2 is having two roles defined in EmpRoles so for EmpID 2 need to fetch Role from LatestRoles table and for remaining ID's need to fetch from EmpRoles .
My Final Output of select query should be like below.
Currently I have a standard query with a join to several tables. There are two additional tables MAS_CTB and MAS_STB. I would like to do a union between those two tables to get FIELDVALUE which will exist in either the CTB table or the STB table and then have that value be returned with the results of the original query.
I can of course write a UNION from the main query to the CTB and then to the STB table, however it's about 80 dummy fields I would have to replicate in the union which is why I was wondering if there was a more simple way.
Main Query: SELECT Field1, Field2...Field80 From Table1 Join Table2
Union Portion: Select FieldValue <------ From MAS_CTB UNION Select FieldValue <-----Return to main query above From MAS_STB
I am trying to develop a custom algorithm. I have implemented and tested training methods, however I fail at prediction phase. When I try to run a prediction query against a model created with my algorithm I get:
Executing the query ... Obtained object of type: Microsoft.AnalysisServices.AdomdClient.AdomdDataReader COM error: COM error: DMPluginWrapper; Object reference not set to an instance of an object.. Execution complete
I know this is not very descriptive, but I have seen that algorith doesn't even executes my Predict(..) function (I can test this by logging to a text file) So the problem is this, when I run prediction query DMPluginWrapper gives exception -I think- even before calling my custom method.
As I said it is not a very descriptive message but I hope I have hit a general issue.
I was wondering if there was another method to determine when a database was last backed up without using the backup history tables in msdb? whether using DBCC DBINFO, DBCC PAGE on a specific database page...etc.
Also, when restoring a database, is there a trace flag you can use to force restore details to be written to the error log?
I installed the bike buyer example and i am learning the DMX language. Now i wrote the following query (using MS decision trees):
SELECT T.[Last Name], [Bike Buyer], PredictProbability(Predict([Bike Buyer])) AS [Probability] From [v Target Mail] PREDICTION JOIN OPENQUERY (....... And so on..)
Now the result is surprising to me. In the resulttabel all the probabilities are equal.
Bike Buyer Probability 1 0.99994590500919611 0 0.99994590500919611 0 0.99994590500919611 0 0.99994590500919611 0 0.99994590500919611 1 0.99994590500919611
and so on.
Now i am wondering what predictProbability means. I thought that PredictProbability meant the probability that the prediction is correct. Now all the probabilities are the same and the input is different. Can somebody tell me what PredictProbability means or am I using it wrong?
I'm having a little trouble with a large query I have written to be sent as an email.
I want to send it as a csv so I've added commas in between each value in the query.
This is causing an error though, I think the ' that surround the commas are closing the query early. Is there any way around this? like encapsulating the query?
Can someone please assist? I have no problem using the provided Algorithms (NaiveBayes, Decision Tree, etc) from SQL Server 2005 Data Mining. For example: If I want to predict whether the customers want to buy bike from the following data, then I use Age, Salary, Gender as input/attribute/feature selection and BuyBike column as "Predict" column.
Table Age Salary Gender BuyBike ------------------------------------
However, say that I have 10,000 types of bikes to predict. How to do that? Age Salary Gender BuyBike1 BuyBike2 BuyBike3 ...... BuyBike10000 ------------------------------------------------------------------------------------
Are there any online resources discussing this issue? I am desperately try to solve this problem. Please assist!
Generally, what would be a good start to model and make predictions based on the following.
Even Dollar Transactions €“ Including transactions that end in $0 or $50 with a Total Transaction Amount between $400 and $5000.
I have a table called Transaction with a BillingAmount column.
I've gone through the SQL Server 2005 Data Mining Tutorial (which uses a discrete prediction BikeBuyer yes or no) and read a lot of Data Mining with SQL Server 2005. Neither of these give good, complete, methodical examples of selecting inputs and targets and making predictions; especially for continuous columns.
I think I'm generally struggling with the concepts of selecting (continuous?) predictable attributes and calculating predictions based on the results. Are there any examples of a scenario similar to the above that I can reference? or just some advise.
If the Loc_ID and trny_dow is the same for mutiple rows, I need to concatenate the trny_time of those recoreds into a string and not return the individual times.
I can't figure out how to return the final recordset as:
Could anyone tell me how to create a scheduled job in sql server 2005, I have a bunch of queries thaty i want to run on a table and i want to automate it, does anyone know of any tutorials on the internet or any helpful links
Whenever I set a schedule on any given package, it executes OK, but I'm not able to bring back those settings for review. Instead, it always starts with the blank schedule form. Any hints? ** Thanx in advance ** P.S. I log in NT as an Administrator.
I just created a job with more than one schedule. Specifically, one schedule for the 2nd Sunday and the other for the 4th Sunday. Has anyone ever created multiple schedules for a job?
I have set up a DTS to import data from an Access database to a server which is running SQL. It works alright if I execute the DTS, but if I set it to run as a scheduled task, it tells me that the path to the access db is not valid, and that I should make sure the path name is spelled correctly and that I am connected to the server on which the file resides... so obviously it's not a spelling issue (since when I execute it, it works), but a security issue. Anyone have any ideas?
I have another question I'm just looking for some opinions on this. I created a database through sql (sql engine, access 2000 client side) now this database has Hyperlinks in it which lead to the employee's documents which on in acrobat pdf. Does anyone for see an problems that might occur wth that???
for rows having no start and endtime assume it as regular intervals.
So i need to show available appointment with duration one hour with the available schedule which is for every five minutes
Like My first appointment for today starts at 8:30 but 8- 8:30 is unblock so there could be an appointment but as this chunk is less than 60 so need to create it
For Schedule table below is what i've to create for temporary basis as this will be available in nightly load in a table.
DECLARE @num int=5 ,@LASTtime TIME =CAST('23:55' as TIME) ,@Time TIME =CAST('00:00' as TIME) ,@Timeprev TIME =CAST('00:00' as TIME) WHILE ( @Time<>@LASTtime) BEGIN
I need to search all tables in my user database to find a value. I don't know what the column name is.
I know of one table that contains the value, but I need to look through all the tables and all there columns to see if I can find if this value exists in more than one place. It is an int value - 394789.
I am having trouble with joining 3 tables.1 table is the primary call it jobs, second is material, third is called labor...job will have all jobs, SOMETIMES the job will have material, sometimes job will have ONLY labor, BUT most of the time it will have job, material and labor...
I've having difficulty joining two tables and getting the results I want. What I'm trying to accomplish is an Health Savings Account (HSA) upload file to go to our Insurance vendor. Here's a summary
Table1: PS_AL_CHK_DED
Description: This table stores all deductions in an employee's paycheck. I'm specifically looking for a field in that table called AL_DEDCD where it equals "H". This is the deduction from the employee's paycheck.
Description: This table stores the employer contribution based on their deduction. For example, we match 50% of their contribution. It is possible for the employee to have a deduction (stored in the ps_al_chk_ded table) but it is not possible for them to have an employer contribution (stored in ps_al_chk_memo) without a deduction (stored in the ps_al_chk_ded).
What I want in my output is a combination of these tables that looks at the employee in the ps_al_chk_ded table, takes only the records where al_dedcd = H and returns the corresponding employer contribution (memo_cd =H in al_chk_memo). So you almost have to join the two tables on all three of those fields and I can't get it to work. Here is what I would like the data to look like:
This didn't seem like it was too complicated but no matter how I do it, I can never get the appropriate amount from the employer contribution. I attached a screenshot that shows my queries and result set. In the screenshot, it returned the employee deduction just fine but then returns 48.25 for the employer contribution. However, the MEMO_CD in that specific record is equal to T. In this case, it should return 0.00 for the employer contribution. If I try and add something like d.al_dedcd = c.memo_cd on the join, I barely get any results. The key is matching on those fields and then returning 0.00 when there is no record of MEMO_CD = H in the al_chk_memo table for the deduction in ps_al_chk_memo.
We have a NT service built via Visual Basic 6 which fires dts processes on demand according some criteria. Well, on the whole and basically only for the following rule: -dts process is executed when our service recognize a concrete plain file leaved in a folder. It€™s generally generated for a mainframe JCL along with a activator file (.nul file, empty). Each application own a folder and each folder have lots of files as the aforementioned ones. Scheduler for that is an ASP application that lives within a intranet. It also is a monitor and logger
In that schema, keeping on mind that eight out of ten are ETL processes, critical ones and on daily-basis, our question is, how to handle on with this stuff using by sql25k and its powerful SSIS?? Migrating such dts packages to SSIS packages is a piece of cake (now no before; thanks Jamie, Michael and other guys for your awesome help) because of its tipology. However our main concern is what the hell to use. Centralized dtsx package running all day long as a service, listening and checking for files? .Net windows application either vb or c#? aspx solution? Set of .vbs interlinked among them?
This is a old post/goal. In spite of we have not defined any deadline for that, what the heck as time goes by is more urgent for us. A restriction: we can€™t to programme nothing from our sql25k production cluster physically. Any stuff will be done on workstations.
In terms of cost, no problem at all. We are interested in hear thoughts and point of view about which is the most effective way
I am doing some general housekeeping for a couple of our SQL boxes in the Development environment. All the databases are set to Simple recovery mode. No need in anything else for these boxes. I have a database on all the boxes named "DatabaseMaintenance" Keeps things like all the sprocs for any type of database maintenance, etc....
I would like to schedule a single sproc that is located in the DatabaseMaintenance database to shrink the Transaction logs on a set schedule. They sometimes grow quite large while testing and developing. The thing that I cannot seem to get around, is when using the ShrinkFile command, one must use the Log Name. If this code is in a sproc that is located in the DatabaseMaintenance database, it will fail when attempting to call out to a different database. Because the Log does not exist on the database that the sproc is located.
How can I get around this small dilemma? There are only about 10 databases per box. To a point we really do not care what happens to them. They are on a Full backup schedule daily, just to keep the objects. As I stated previously, the logs will still grow huge at times while pumping data.
Is there a way to create a piece of code that will run against each database on the server, and be stored in a single database? Other than the system databases of course.
