Multiple Steps For Each Query
Jun 12, 2015
My end game is to automate some of my monthly queries in a Job in SQL Server Agent. Right now I have two metric tables. One table is the name and comment with the PK. The secondary table is attributes/detail, such as reporting month, target and actuals.
I am currently running all different types of queries to get the aggregates. I'd like to get these into a job so it would run automatically and update the reporting table.
Would you recommend making one step or multiple steps for each query? I am trying to use an intelligent approach to begin to load the tables.
View 2 Replies
ADVERTISEMENT
Jun 6, 2001
Hi,
I have two job J1 and J2, each one has 10 steps. Now I want J2 to be the 11th step of J1 and I did not want manually type all the steps of J2 to be 11-20 step of J2. Is there an easy way through TSQL to do this ?
sp_add_jobstep only works when the step is OS command or a script but not a job
View 2 Replies
View Related
Dec 17, 2004
Hi,
Previously I was performing the process of populating my DataMart and then processing my cubes via 2 separate job scheduled one after the other and with about 30 minutes idle time in-between (just in case).
I now have tried to make a single job with both of the above as 2 steps (one after the other) of a single job.
However when I was done, I did a right click and tried to run the newly created job but instead of what I was hoping (that the job would start, would perform the 1st step, followed by the 2nd), it instead popped up a dialog box asking (I think) which step needs to be executed :(
Is there a solution so that when the job is scheduled to run, it should start performing the steps in the order rather than waiting for someone to select which step to perform.
Will really appreciate urgent reply.
View 2 Replies
View Related
Dec 17, 2004
Hi,
Previously I was performing the process of populating my DataMart and then processing my cubes via 2 separate job scheduled one after the other and with about 30 minutes idle time in-between (just in case).
I now have tried to make a single job with both of the above as 2 steps (one after the other) of a single job.
However when I was done, I did a right click and tried to run the newly created job but instead of what I was hoping (that the job would start, would perform the 1st step, followed by the 2nd), it instead popped up a dialog box asking (I think) which step needs to be executed :(
Is there a solution so that when the job is scheduled to run, it should start performing the steps in the order rather than waiting for someone to select which step to perform.
Will really appreciate urgent reply.
View 2 Replies
View Related
Dec 17, 2004
How can I make this work?
I have two process steps in a package and one failure step. Both process steps have On Failure workflows to the failure step. The problem I'm having is that it seems if two or more workflows go to the same failure step then neither go to the failure step, but it does go if only one process step is attached to it.
I know I can create duplicate steps for each process step, but I was hoping to be able to do it this way FMI.
Thanks for your assistance.
View 1 Replies
View Related
Mar 11, 2014
I have the following:
create table testdata
(
StepName varchar(25)
,StepStatus char(25)
,EntryDate datetime)
insert testdata values ('Step1','Starting',getdate())
insert testdata values ('Step2','Starting',getdate())
insert testdata values ('Step3','Starting',getdate())
insert testdata values ('Step4','Starting',getdate())
insert testdata values ('Step1','Finished',getdate())
insert testdata values ('Step2','Finished',getdate())
insert testdata values ('Step3','Finished',getdate())
I need a query that will basically return Step 4 hasn't finished.
I have tried a few ways but can't get it to work...
View 5 Replies
View Related
Apr 21, 2015
I have a table with single row like below
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
Column0 | Column1 | Column2 | Column3 | Column4|
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Value0 | Value1 | Value2 | Value3 | Value4 |
Am looking for a query to convert above table data to multiple rows having column name and its value in each row as shown below
_ _ _ _ _ _ _ _
Column0 | Value0
_ _ _ _ _ _ _ _
Column1 | Value1
_ _ _ _ _ _ _ _
Column2 | Value2
_ _ _ _ _ _ _ _
Column3 | Value3
_ _ _ _ _ _ _ _
Column4 | Value4
_ _ _ _ _ _ _ _
View 6 Replies
View Related
Sep 8, 2006
I have just taken over the job of sorting out a rather poorly designed database. It looks like it was 'upsized' from an access database to the SQL server. The SQL server is the 2000 version.
Now I am trying to generate a report of what the students in the database are owing by referencing the Receipt table and then all the available payment methods and allocations. I was wondering if there was anyway to work out data being displayed twice (Let me demonstrate)
Note1: All the tables are linked by a key of ReceiptNo. From what I can see there is a table for every payment type and allocation but no link between the two other then the receipt number.
Using the query:
SELECT T_Receipt.ReceiptNo, T_cheque.Amount AS Chq_Amount, T_credit.Amount AS Cre_Amount, StandingOrder.Amount AS Stn_Amount,
T_BankTransfer.amount AS Bnk_Amount, T_cash.TotalAmount AS Cas_Amount, T_RentPayment.AmountPayed AS Ren_Paid,
T_AdminPayment.AmountPaid AS Adm_Paid, T_InternetBilling.Total AS Int_Paid, T_Utilities.AmountPaid AS Util_Amount,
T_InvoicePayment.amountPaid AS Inv_Paid, T_OtherPayments.paymentAmount AS Oth_Paid, T_parkingBill.paymentAmount AS Prk_Paid,
T_TelephoneBills.TelephoneCredit AS Tel_Paid, T_DepositPayment.[Deposit payment] AS Dep_Amount, T_Receipt.cancelled AS Canceled,
T_Receipt.RemittanceReceiptNo AS Rec_Ref, T_Receipt.Student
FROM T_Receipt INNER JOIN
T_DepositPayment ON T_Receipt.ReceiptNo = T_DepositPayment.receiptNo LEFT OUTER JOIN
T_RentPayment ON T_Receipt.ReceiptNo = T_RentPayment.RentPaymentNo LEFT OUTER JOIN
StandingOrder ON T_Receipt.ReceiptNo = StandingOrder.ReceiptNo LEFT OUTER JOIN
T_TelephoneBills ON T_Receipt.ReceiptNo = T_TelephoneBills.ReceiptNo LEFT OUTER JOIN
T_parkingBill ON T_Receipt.ReceiptNo = T_parkingBill.ReceiptNo LEFT OUTER JOIN
T_OtherPayments ON T_Receipt.ReceiptNo = T_OtherPayments.ReceiptNo LEFT OUTER JOIN
T_InvoicePayment ON T_Receipt.ReceiptNo = T_InvoicePayment.receiptNo LEFT OUTER JOIN
T_cash ON T_Receipt.ReceiptNo = T_cash.ReceiptNo LEFT OUTER JOIN
T_AdminPayment ON T_Receipt.ReceiptNo = T_AdminPayment.ReceiptNo LEFT OUTER JOIN
T_BankTransfer ON T_Receipt.ReceiptNo = T_BankTransfer.receiptNo LEFT OUTER JOIN
T_Utilities ON T_Receipt.ReceiptNo = T_Utilities.receiptNo LEFT OUTER JOIN
T_credit ON T_Receipt.ReceiptNo = T_credit.ReceiptNo LEFT OUTER JOIN
T_cheque ON T_Receipt.ReceiptNo = T_cheque.ReceiptNo LEFT OUTER JOIN
T_InternetBilling ON T_Receipt.ReceiptNo = T_InternetBilling.ReceiptNo
GROUP BY T_Receipt.Student, T_Receipt.ReceiptNo, T_cheque.Amount, T_credit.Amount, StandingOrder.Amount, T_BankTransfer.amount, T_cash.TotalAmount,
T_AdminPayment.AmountPaid, T_InternetBilling.Total, T_Utilities.AmountPaid, T_InvoicePayment.amountPaid, T_OtherPayments.paymentAmount,
T_parkingBill.paymentAmount, T_TelephoneBills.TelephoneCredit, T_Receipt.cancelled, T_Receipt.RemittanceReceiptNo,
T_DepositPayment.[Deposit payment], T_RentPayment.AmountPayed, T_Receipt.Student
HAVING (T_Receipt.Student LIKE N'06%')
Which gives a result of:
RecNo.
30429
Cheque
250
Deposit
250
30429
679.98
250
This is fine but when I do analysis on this it appears as though the student has paid two deposit payments. I was wondering with out querying each table independently from an application if there was a criteria to specify that I only get one deposit result.
So as such say, give me all the payments but I only want one result from the other tables. I though about discrete but that wouldn't work here.
View 3 Replies
View Related
Mar 17, 2006
Howdy folks. I have a SELECT question for you. Or maybe a WHERE question. That I am not sure is part of the problem. My application is ASP.NET 2.0, and I’d like to avoid getting into stored procedures right now, if I could.
I am trying to summarize the order status for each customer in the database (SQL Server 2005, by the way). I want to provide two counts: the number of open jobs per customer, and the number of rush jobs per customer. Something like this:
CustID CustName JobOpen JobRush
----------------------------------------------------------------
22 John Deere 47 3
24 MCP 32 7
37 BlueON 6 0
In my noobness, I developed this:
SELECT CustID, MAX(CustName) AS CustName,
COUNT (*) AS JobOpen
FROM Customers
INNER JOIN Jobs ON JobCustID = CustID
WHERE (JobDone = 0)
GROUP BY CustID
As you can see, it finds from the Jobs table all jobs that are not done (JobDone is a T/F field), joins the Jobs and Customers tables, groups by CustID, and counts the totals in each CustID group. It works great in outputting the first three columns that I am looking for.
But I cannot come up with a simple way to add JobRush (also a T/F field), because it needs a different WHERE clause than the one JobOpen uses. It would need
WHERE (JobRush = 1)
So maybe my question should be: how do I use multiple WHERE clauses, each with its own COUNT?
I did mess around with Common Table Expressions, and managed to build two CTEs (one with JobOpen results and the other with JobRush results) that I joined together. It worked in Studio Manager, but my ASP.NET page didn’t like it. I guess that means I could learn stored procedures, but wow I’d love to just have a nice complete SELECT command for my page.
Thanks for reading all this. Any input is greatly appreciated.
Matt
View 3 Replies
View Related
Oct 6, 2015
SQL Server 2012 Performance Dashboard Main advices me this:
Since the application is from a vendor and I have no control over its code, how can improve this sitation?
View 3 Replies
View Related
Oct 30, 2015
When viewing an estimated query plan for a stored procedure with multiple query statements, two things stand out to me and I wanted to get confirmation if I'm correct.
1. Under <ParameterList><ColumnReference... does the xml attribute "ParameterCompiledValue" represent the value used when the query plan was generated?
<ParameterList>
<ColumnReference Column="@Measure" ParameterCompiledValue="'all'" />
</ParameterList>
</QueryPlan>
</StmtSimple>
2. Does each query statement that makes up the execution plan for the stored procedure have it's own execution plan? And meaning the stored procedure is made up of multiple query plans that could have been generated at a different time to another part of that stored procedure?
View 0 Replies
View Related
Nov 7, 2007
I have two tables .. in one (containing user data, lets call it u).The important fields are:u.userName, u.userID (uniqueidentifier) and u.workgroupID (uniqueidentifier)The second table (w) has fieldsw.delegateID (uniqueidentifier), w.workgroupID (uniqueidentifier) The SP takes the delegateID and I want to gather all the people from table u where any of the workgroupID's for that delegate match in w. one delegateID may be tied to multiple workgroupID's. I know I can create a temporary table (@wgs) and do a: INSERT INTO @wgs SELECT workgroupID from w WHERE delegateID = @delegateIDthat creates a result set with all the workgroupID's .. this may be one, none or multipleI then want to get all u.userName, u.userID FROM u WHERE u.workgroupIDThis query works on an individual workgroupID (using another temp table, @users to aggregate the results was my thought, so that's included) INSERT INTO @users SELECT u.userName,u.userID FROM tableU u LEFT JOIN tableW w ON w.workgroupID = u.workgroupID WHERE u.workgroupID = @workGroupIDI'm trying to avoid looping or using a CURSOR for the performance hit (had to kick the development server after one of the cursor attempts yesterday)Essentially what I'm after is: SELECT u.userName,u.userID
FROM tableU u
LEFT JOIN tableW w ON w.workgroupID = u.workgroupID
WHERE u.workgroupID = (SELECT workgroupID from w WHERE delegateID = @delegateID) ... but that syntax does not work and I haven't found another work around yet.TIA!
View 1 Replies
View Related
Jul 24, 2000
Hi,
I'm using SQL Server 7.0. I have a job which runs DTS packages (1 package per step). When a task fails within my DTS package, I'd like an error returned for that step in the job thus stopping the job and not starting up the next step (DTS package) in the job. As it stands right now, if a task fails within the DTS package, that step in the job still returns a successful completion. Has anyone seen this before and is there something I can do to get the DTS to send a failure for that step in the job?
Thanks in advance,
Darrin
View 1 Replies
View Related
Jan 12, 2007
I am going to be moving multiple databases to a new server. Everything should go smooth, but I need to change a lot of the DTS packages that reference the old servername and replace it with the databases DNS record.
Is there an easy way to get a list of which dts reference the old server explicitly (not using database DNS)?
Thanks.
View 7 Replies
View Related
Jun 23, 2005
hi !!!i try to connect to my sql server local instance but it is always failed ..... can you please tell me the step by steps and options to use to install sql server on my machine and i think i need to use he personal copy rather than the standard as it will be on my machne not in the server??? please help
View 6 Replies
View Related
Jun 20, 2002
Hello,
Can anybody tell me how many steps it's possible to put in one job.
The reason I ask is that we have a job that has over 500 steps (import data from Excel file into SQl table) and every time it runs we have different steps failures.
Does fact, that excel file was dropped and recreated, change DTS Id ?
Thank you in advance
View 4 Replies
View Related
Apr 27, 2001
Hi,
I am new to replication.
I have to replicate a db on SQL7.0 sp5 .
It's going to be transactionol.
Is there any article which explains everything - where to start from and where to end?
I mean everything step by step.....
TIA.
View 2 Replies
View Related
Apr 20, 2000
hello!
A job as a whole can be scheduled.But can a individual job step be scheduled?
thankfully in advance,
Rams.
View 1 Replies
View Related
Apr 27, 2006
Here is an interesting problem I can't figure out. I have a job with 6 steps as follows:
Step 1 - Import text file 1
Step 2 - Import text file 2
Step 3 - Delete all data from address tables 1 and 2
Step 4 - Copy data from imported table 1 to address table 1
Step 5 - Copy data from imported table 2 to address table 2
Step 6 - Delete imported taxt file table 1 and 2
Now when I run each of these steps individually, like running the dts packages and stored procedures my self it all works fine and the data in my tables appears to update. Then, when I set the job to run automatically, it says completed and no errors but my data hasn't updated. The job must be doing what it is meant to as it took about 40 seconds which is normal.
Ever seen this problem before?
View 1 Replies
View Related
Jul 2, 2007
Hello,
SQL Server 2005 Enterprise and new hardware have been ordered for our department. We currently run SQL Server 2000 (sp4). We have almost 500 DTS packages, 293 Jobs, and 14 user databases with hundreds of objects within.
Is there any documentation out there on how to scrutenize a current system? I have searched, and most of what I can find addresses migration planning with the assumption that the databases, packages, jobs, security, etc are ready to move over. We have a lot to think about before we can do that. We know we have redundancy problems (like View proliferation), table schema issues, obsolete DTS packages and Jobs, and otherwise a host of opportunities to 'clean house' and/or improve. We would really like to get a handle on what we are migrating before we migrate.
If you have any ideas or resources to you feel would be worth looking at, please share.
Thank you for your help!
cdun2
View 4 Replies
View Related
Mar 30, 2008
Generally speaking when you want to optimise an application that relies on a database which is the order of the following optimization techniques
a) optimizing the spread of the pysichal elements of the database on different disks of the server
b) optimizing the use ot the RAM
c) optimizing the SQL
d) opimizing the OS
Thank you,
Ronnyy
View 4 Replies
View Related
Apr 15, 2008
I've created SQL Server Agent jobs through management studio on SQL Server 2005. I can view and edit these jobs when I am logged into the server via remote desktop, but when trying to administer these jobs through Management Studio on a different machine, the steps do not appear in the job properties window.
Anybody else see this behavior? Know why it occurs? Is it a bug, or another wonderful "feature" of Manglement Studio?
View 10 Replies
View Related
Apr 22, 2008
i have setup a sql job with 4 steps
1-checkdb
2-indexdefrag
3-sp_updatestats
4-email notification.
if 1,2 success go next step
if 1,2,3 fails go step 4
if 3, 4 success quit with success
if 4 fails quit with failure
i created a script and pasted it in production server and i get this msg:
Warning: Non-existent step referenced by @on_fail_step_id.
Warning: Non-existent step referenced by @on_fail_step_id.
Warning: Non-existent step referenced by @on_fail_step_id.
how can i fix it? I think the error is due to step 4 not executing before calling it from stepe 1,2,3
View 1 Replies
View Related
Nov 14, 2007
welcome everybody
i want to publish my sql2005 server through my isa2004
so i do the following steps and i want to know if there is wrong in it or if
there is another step is missing or not?
1-i make editing in router configuration file to natting requests on my real
ip to the external interface of my isa
2-at isa i make sql publishing rule to forword requests to the ip of sql
server
(from:anywhere to: ip of sql server listner:external protocol:microsoft
sql server requests:appear from isa not original client ports:default
ports1433)
3-at sql server i enable allow remote and local connection over tcp only
4-at sql server i enable allow remote desktop
5-at sql server i enable firewall and in exception tab i add remotedesktop
and 1433 port
but still when i try to connect from internet using the studio managment
express tool using the real ip address(tcp:{my real ip address}) and login information of sql still
error occure and no connection opened....
note:scw was installed and i uninstall it
so what is the problem
why sql can't published
also i make at isa another rule to allow remote desktop to my sql server using rdp protocol but when i try to connect using remote connection to sql server it failed but when connect to any other internal server it work succesfully
View 4 Replies
View Related
May 22, 2008
I am migrating the SQL Server 2K instance from Windows 2000 server to Windows 2003 Server on a new box.
After restoring the database is there any tasks to be done to make sure the performance is not compromised.
For example... updating the statistics.....
Likewise are there any tasks that I need to run after the successful migration.
Thank you,
Gish
View 4 Replies
View Related
Dec 7, 2007
What in SSIS replaces DTS Task Steps? In DTS you could build tasks and assign them an order in which to execute. How is this replaced in the SSIS Control Flow. Thanks.
JGL
View 3 Replies
View Related
Dec 3, 2006
I am trying to create a SQL Agent job with 3 steps.
I want to delete three tables.
Step 1 ...delete table "X"
Step 2 ...delete table "X"
Step 3 ...delete table "X"
problem is that afer i create the three steps and start the job it never seems to finish the first steps and non of the other steps run, the job looks like is executing and never finishes. I break the job into three jobs each completes fine. I need this to runs one job, any ideas?
perplexed for sure.....
View 1 Replies
View Related
Sep 6, 2006
I want to update a char column and remove all the periods (.) - middle initial data. What are the basic steps in SSIS?
Source Table/Column --> Transform of some kind --> ? --> ?
I've never had to massage data and replace in the same table in my brief career so far but I see a lot more ahead.
There isn't a simple sql statement to do this, is there?
TIA
View 1 Replies
View Related
Jun 9, 2008
Hi,
I remember seeing a fancy query that checked for multiple fields in a table (I think using a select statement in the where clause but not sure), but can't remember how to do it... here is what I want to do (and maybe there is a much easier way). Thanks!
Table1
id item color
1 shoe red
2 shoe blue
3 coat green
4 coat black
Table2
item color
shoe red
coat green
I want everything in Table1 where item and color are not a match.
So my results should be:
2 shoe blue
4 coat black
I'm sorry if this is a dumb question... it's been that kind of a day!
Thanks!
View 1 Replies
View Related
Mar 5, 2006
Hello,
Thanks for reviewing my question. I am trying to install SQL Server 2005 but I keep running into the same error:
SQL Server Setup failed to obtain system account information for the ASPNET account. To proceed, reinstall the .NET Framework, and then run SQL Server Setup again.
The only documentation I find on this is configuring the issolation mode in IIS 6.0.
Any help on is will be appreciated.
Peter
View 5 Replies
View Related
Feb 4, 2000
Hi All,
In creating 'steps' in JOBS, is it possible to execute many DOS CmdExec in
one step, instead of creating several steps with a single DOS-cmd in each.
For example:
Step1: bcp sourcedb..sourcetbl out source.dat -n -T -Ssourceserver
Step2: isql -T -Stargetserver -Q "truncate table targettbl"
Step3: bcp targetdb..targettbl in source.dat -n -T -Stargetserver
If I created a job executing those 3 functions in 3 separate steps then it works fine. But if I put all those 3 DOS command in one step, it won't work. Somehow,
SQL doesn't 'understand' it should execute after the end of each command OR
I missed something here (apparently so!).
I know if I put all those 3 DOS commands into a DOIT.BAT and execute it, it will work. But I want to use SQL Job to schedule it to run on a regular basis.
Anyone has run into this same problem? Thanks in advance.
David Nguyen.
View 1 Replies
View Related
Dec 22, 2006
Hi all,
Can any one help me in this issue?I am new for this SQL server.By creating scripts from server system.I got the tables without data in my system.But i want to get the full database as usual there in server.
I have to convert my existing MS Access frontend and SQL backend app into ASP.NET web app with sql server backend.The version is 2000.Pls it's urgent?
Kranthi
View 2 Replies
View Related
Jun 20, 2007
Hi. I have an 'Attendance' table like this:PIN Year Category Days1 2006 Authorized 11 2006 Available 21 2006 Personal 32 2006 Authorized 42 2006 Available 52 2006 Personal 63 2006 Authorized 73 2006 Available 83 2006 Personal 94 2006 Authorized 104 2006 Available 114 2006 Personal 121 2007 Authorized 131 2007 Available 141 2007 Personal 152 2007 Authorized 162 2007 Available 172 2007 Personal 183 2007 Authorized 193 2007 Available 203 2007 Personal 214 2007 Authorized 224 2007 Available 234 2007 Personal 24I need to sum the days by PIN, Year and Category (that's easy...) ANDobtain a layout like this:PIN Auth 2006 Avail 2006 Pers 2006 Auth2007 Avail 2007 Pers 20071 1 23 13 14 152 4 56 16 17 183 7 89 19 20 214 10 1112 22 23 24How can I do this by queries without writing too many intermediatesteps ?What I have done is this (5 queries, 2, 3, and 4 building on top of1,and 5 building on 2, 3, 4).1 = Table1_Crosstab:TRANSFORM Sum(Table1.Days) AS SumOfDaysSELECT Table1.PIN, Table1.YearFROM Table1GROUP BY Table1.PIN, Table1.YearPIVOT Table1.Category;Then, based on that,2 = Authorized:TRANSFORM First([1 = Table1_Crosstab].Authorized) ASFirstOfAuthorizedSELECT [1 = Table1_Crosstab].PINFROM [1 = Table1_Crosstab]GROUP BY [1 = Table1_Crosstab].PINPIVOT [1 = Table1_Crosstab].Year;3 = Available:TRANSFORM First([1 = Table1_Crosstab].Available) AS FirstOfAvailableSELECT [1 = Table1_Crosstab].PINFROM [1 = Table1_Crosstab]GROUP BY [1 = Table1_Crosstab].PINPIVOT [1 = Table1_Crosstab].Year;and4 = Personal:TRANSFORM First([1 = Table1_Crosstab].Personal) AS FirstOfPersonalSELECT [1 = Table1_Crosstab].PINFROM [1 = Table1_Crosstab]GROUP BY [1 = Table1_Crosstab].PINPIVOT [1 = Table1_Crosstab].Year;and finally5 = AllSELECT [2 = Authorized].PIN, [2 = Authorized].[2006] AS [Auth 2006],[3 = Available].[2006] AS [Avail 2006], [4 = Personal].[2006] AS[Pers2006], [2 = Authorized].[2007] AS [Auth 2007], [3 = Available].[2007]AS [Avail 2007], [4 = Personal].[2007] AS [Pers 2007]FROM ([2 = Authorized] INNER JOIN [3 = Available] ON [2 =Authorized].PIN = [3 = Available].PIN) INNER JOIN [4 = Personal] ON[3= Available].PIN = [4 = Personal].PIN;It works, but... I am sure that this is an awkward way of doing it.Isthere any other, more elegant, way, please ? Besides, what if I hadnot 3, but 15 categories, for example ????Thanks a lot for your time reading this, Alex
View 4 Replies
View Related
