Transact SQL :: Running Query For Multiple Times Through The Day?
Jun 12, 2015
I want the below query to run 24 hours ..once the insert is complete, run again , so on for 24 hours .
there is a way to run every second in as job but i want to run only after run complete ..is there a way to run the query after every complete run ? and keep in job
INSERT INTO [dbo].[Audit_Active]
([SPID],[LoginName],[HostName],[ProgramName],[Command],[LastQuery],[DBName],[ServerName])
SELECT
--DISTINCT
p.SPID,
p.LogiName,
p.HostName,
[Code] ....
View 8 Replies
ADVERTISEMENT
Mar 2, 2005
Hi,
I was wondering if this can be done...
I have a complex query which has to do a few calculations. I'm using subqueries to do the calcs, but most of the calcs have to use a value gotten from the first subquery. I don't want to have to type the subquery out each time, so is there a way of assigning it to a variable or putting it in a UDF or SP?
E.g.
I have a table with 2 cols - amount, date.
SELECT total_amount, closing_amount,
FROM table1
GROUP BY month(date)
Total amount is the SUM(amount) for the month.
Closing amount is the Total Amount plus the amounts for the current month with a few extra calcs.
As I have to use SUM(amount) in the second subquery, is there a way I can do it without having to type hte subquery out again?
This is only a basic example, what I'm trying to do will invovle a lot more calcultions.
Hope someone can help,
Thanks,
Stuart
View 5 Replies
View Related
Jun 25, 2007
I’m binding the distinct values from each of 9 columns to 9 drop-down-lists using a stored procedure. The SP accepts two parameters, one of which is the column name. I’m using the code below, which is opening and closing the database connection 9 times. Is there a more efficient way of doing this?
newSqlCommand = New SqlCommand("getDistinctValues", newConn)newSqlCommand.CommandType = CommandType.StoredProcedure
Dim ownrParam As New SqlParameter("@owner_id", SqlDbType.Int)Dim colParam As New SqlParameter("@column_name", SqlDbType.VarChar)newSqlCommand.Parameters.Add(ownrParam)newSqlCommand.Parameters.Add(colParam)
ownrParam.Value = OwnerID
colParam.Value = "Make"newConn.Open()ddlMake.DataSource = newSqlCommand.ExecuteReader()ddlMake.DataTextField = "distinct_result"ddlMake.DataBind()newConn.Close()
colParam.Value = "Model"newConn.Open()ddlModel.DataSource = newSqlCommand.ExecuteReader()ddlModel.DataTextField = "distinct_result"ddlModel.DataBind()newConn.Close()
and so on for 9 columns…
View 7 Replies
View Related
Mar 26, 2008
Is it possible to run through a buffer multiple times in an asynchronous script?
Let say I have a data set and I want to get the max value and then compare/subtract each row in the data set to that max value and add that as a new column - is that possible in the asynchronous script?
Basically I would need to run through the buffer once and pull out the value for the max, and then go through the buffer again pushing to the output buffer the row with the new column "DiffFromMax".
I already tried adding an asynchronous script to pull the max and put into a variable and downstream add a derived column which subtract from the variable but it doesn't work as the variable cannot be assigned till the postexecute() so its always too late.
I've tried having an asynchronous script that has 2 output, one containing the max and the other the rest of the data, however there is no way to subtract without spoofing a cross join which is really slow becuase of the sort required (I still can't believe msft rejected my request for adding a cross join, it had lots of votes and it should be easy to add... I'd code it myself if they let me have a script transformation with multiple inputs)
View 5 Replies
View Related
Nov 9, 2015
I am trying to update the same row of the table multiple times. in other words, i am trying to replace the two stings on same row with two different values.
Example: if the column has a string "b" then replace with "B" and if the column has a string "d" then replace with "D" . I can write multiple updates to update it but i was just wondering if it can be done with single UPDATE statement
column before the update : bcdxyz
after the update: BcDxyz
Following is the sample data
declare @t1 table
(
c1 varchar(5),
c2 varchar(10),
c3 varchar(5)
)
insert into @t1 values ('a','bcdxyz','efg')
[Code] ....
View 5 Replies
View Related
Jan 22, 2008
I have multiple SQLDatasources on multiple pages. When I call the Select command for the datasource the query is run multiple times. I was wondering if anyone had any problems like this. The data is being read into listboxes. If you need any more info or have any specific questions, feel free to ask.
View 6 Replies
View Related
Nov 19, 2015
There are 3 tables Property , PropertyExternalReference , PropertyAssesmentValuation which are common for 60 business rule
SELECT
PE.PropertyExternalReferenceValue [BAReferenceNumber]
, PA.DescriptionCode
[PSDCode]
, PV.ValuationEffectiveDate
[EffectiveDate]
, PV.PropertyListAlterationDate
[ListAlterationDate]
[code]....
Can we push the data for the above query in a physical table and create index to make the query fast rather than using the same set tables multiple times
View 11 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
Jun 18, 2015
I have a table that will be loaded over night everyday and I need to write a query on running value difference ?
List of Columns (ID, Branch ,Group, Date, Value)
ID Branch Group Date Value
1 A C 2015-06-01 10
2 A C 2015-06-02 15
3 A C 2015-06-03 25
4 A C 2015-06-04 20
5 B D 2015-06-01 20
6 B D 2015-06-02 25
7 B D 2015-06-03 10
8 B D 2015-06-04 20
I want the Output like below with a Running value difference in comparison to previous day.
ID Branch Group Date Value Running Value
1 A C 2015-06-01 10 10
2 A C 2015-06-02 15 05
3 A C 2015-06-03 25 10
4 A C 2015-06-04 20 -5
5 B D 2015-06-01 20 20
6 B D 2015-06-02 25 05
7 B D 2015-06-03 10 -15
8 B D 2015-06-04 20 10
Basically I need to compare the previous day and show the difference. How can I do this in SQL 2008 r2?
View 6 Replies
View Related
Jan 8, 2008
I have several SQL files that make several different table updates and data changes.
How would I create something (maybe an SSIS package?) or even write a T-SQL script that runs through these files and executes the queries contained in them?
(I put this thread in SQL General also because I wasnt sure if this could be implemented in T-SQL or not.)
Right now what I have is a ForEachLoop Container with an ExecuteSQL task component inside of it. I'm thinking that the contents of each of the sql files would be copied over to a "Temp.sql" file and then executed in the loop. So I've created a Temp.sql file for this purpose and the properties of the ExecuteSQLTask component are setup as so:
(I've created my connection managers appropriately with an OLEDB source and a File Connection pointing to Temp.SQL)
ConnectionType OLEDB
Connection servertest
SQLSourceType File connection
FileConnection Temp.sql
And for my ForEachLoop container, my properties have been set as:
Collection
--> Enumerator Foreach File Enumerator
--- Enumerator Configurations ---
Folder: C:lahblahblah
Files: *.sql
with retrieve file name set as Fully Qualified
Have I set this up properly? (obviously not, because it's not working )
View 7 Replies
View Related
Nov 28, 2012
I have a sales tables which looks as below.
DEPARTMENT
Barnd_Name
Item_Group
S_DATE
S_AMOUNT
Administration
IBM
[code]....
Now i need Month Wise Running Totals.but i should check the following group as show below i that order
1) DEPARTMENT
1) Brand
3) Item Group
4) Month
View 12 Replies
View Related
May 28, 2008
Hello,
I see the following problem regarding a SQL function that returns a value.
We have the following query
SELECT c.Id_Customer, c.Name
FROM t_Customers c
WHERE c.Id_Status = fn_GetParameter('ID_Status.Active')
The idea is not hardcoding the status and other values on each query, and, since SQL Server does not support the definition of constants, we have a table with many parameters and we search them.
We defined the function "WITH SCHEMABINDING" and the SQL Server recognizes it as DETERMINISTIC, so I do not understand why it executes the function as many times as records in the t_Customers table, since every time it is executed it returns the same value.
We could define a variable, assign the value returned by this function to this variable and then use it on the SELECT, but this approach is useless if we use SQL instead of stored procedures (for example, in reports from reporting / BI tools).
Any explanation about why SQL chooses to execute the function many times, and any hint regarding how to make SQL Server execute only once the function will be very appreciated.
Thanks in advance,
Lisandro.
View 3 Replies
View Related
May 31, 2007
I am trying to query the Topics in my discussion forum...The Topic contains a "last_poster_id" and a "author_id" I need the username and userid for both "last_poster_id" and "author_id" in the table "aspnet_Users"How do I do this?I would guess I need to use sub select statements. Can someone help me?
View 12 Replies
View Related
Jul 26, 2007
I created an SSIS Package and once complete there is 4 instances of the same data. I checked the source and it only has 1 instance. Why is it pulling the data 4 times? It will first show 3 instances and then another will show up before the package finishes running. Is there a bug in the program or is there a place I'm not aware of where it sets how many times the package should run.
Thank you,
Cindy
View 3 Replies
View Related
Jan 4, 2007
Hi Guys!
I have created a big list of packages, some calling others. They all work fine from my computer using Visual Studio.
When I try to deploy them (building them with deployment turned on and running them either directly from Management Studio or as a job) I get the errors with the password of connection strings. From what I read so far its the encryption process that kills it.
I have tried to add a password to some packages, but it still didnt work (only when run directly on my computer in management studio after deploying to SQL Server, but not as a job).
I have tried to change ProtectionLevel to SecurityStorage, wouldnt let me save in Visual Studio (I understand it is ot allowed in VS because you are saving to File System, how the hell am I supposed to save it to anything else? why is it showing there if its not even valid?).
If anyone can please give me the steps to doing it properly, that would be awesome. I simply need to run the packages from SQL Server! thats all! I have no idea why it has to be soooo difficult :/
View 8 Replies
View Related
Jun 1, 2015
I have following query. Now I want to change this in such a way that if Condition 1 satisfies then it should not go to next condition ,if not then should go to next. Similarly the same format till condition 4.
select * from table where
IND = 'Y'
and
(
(STATE = @STATE and LOB =@LOB and COMPANY=@COMPANY)--Condition 1
OR
(STATE = @STATE and LOB =@LOB)--Condition 2
OR
(STATE = @STATE and COMPANY=@COMPANY)--Condition 3
OR
(STATE = @STATE)--Condition 4
)
View 4 Replies
View Related
Jun 17, 2015
I have a query where i would calculate the counts of three Servers
declare @Managed float
declare @Active float
DECLARE @Values varchar(1000)
SET @Values = 'WE,EE,CO'
select @Managed = count(Name0) from v_R_System where Operating_System_Name_and0 like '%Workstation%'
[Code] ...
Here i need output like below
Workgroup Managed Active
'WE' 255 ,400
'EE' 300 ,450
'CO' 155, 600
So how to use these three values in the where condition when i use the where clause i have put in condition it will give me the subquery returns more than one value,so how should i use this scenario to accomplish this output?
View 3 Replies
View Related
Apr 27, 2015
Is there any way to update multiple tables in a single query. I know we can write triggers. Apart from triggers, is there any other way available in SQL Server. I am using 2008R2.
View 8 Replies
View Related
May 13, 2015
I have created approximately 200lines of multiline table function. getting table variable values as what I need satisfying all our business rules.
my question : is it okay in performance wise using loop i executed this function 100000 times and it took around 16mins.
View 10 Replies
View Related
Sep 4, 2015
What is a more efficient way of doing the following such that DATEDIFF() does not have to calculated numerous times?
CASE
WHEN DATEDIFF(DD, @Today, COALESCE(POS.[PurchaseDate], POS.[FinalizedDate])) <= 0 THEN '<= 0D'
WHEN DATEDIFF(DD, @Today, COALESCE(POS.[PurchaseDate], POS.[FinalizedDate])) > 0 AND DATEDIFF(DD, @Today, COALESCE(POS.[PurchaseDate], POS.[FinalizedDate])) <= 7 THEN '> 0D AND <= 7D'
WHEN DATEDIFF(DD, @Today, COALESCE(POS.[PurchaseDate], POS.[FinalizedDate])) > 7 AND DATEDIFF(DD, @Today, COALESCE(POS.[PurchaseDate], POS.[FinalizedDate])) <= 30 THEN '> 7D AND <= 30D'
[code]....
View 5 Replies
View Related
Nov 9, 2015
I am doing some analysis on shifts and what I want to do is say if the time from a timestamp field is between 00:00 and 05:59 then the figures belong to the day before. Eg. We have a night shift that work 22:00 - 06:00.
Here is my coding which logically looks sound to me but when I run it, it has an error:
SELECTe.ID,
CAST(e.TimestampasDate)asEventDate,
CAST(e.TimestampasTime)asEventTime,
CASE
WHENCAST(e.TimestampasTime)BETWEEN'00:00:01'and'05:59:59'
THENCAST(e.TimestampasDate)-1
ELSECAST(e.TimestampasDate)
END
FROMIntouch.Event
View 5 Replies
View Related
May 28, 2015
I have a column with XML data stored in it. I need to update that column several times with new values for different nodes. I've written a CLR function to update the XML quickly but the update is always based on the initial value of the xmlData column. I was hoping that the subsequent updates would be based on the new data from the prior update (each xmlTable has several newData rows). Do I have to make this a table valued function and use cross apply?
UPDATE xmlTable
SET xmlTable.xmlData = Underwriting.UpdateByDynamicValue(xmlTable.xmlData,newData.NodeID,newData.NewValue)
FROM xmlTable
JOIN newData
ON xmlTable.ID = newData.fkXmlTableID
View 2 Replies
View Related
Jul 10, 2015
How can I write it in SQL? I have a table that column A has two values of Product and Cost...Where value of A is Product, I have to add 6 more rows, and add column b with values from 1 to 7 for each rows ( counter)Where value of A is Cost, I have to add 4 more rows, and add column b with values from 1 to 5 for each rows ( counter).
View 3 Replies
View Related
Oct 20, 2006
I have a situation where I run the same taks multiple times during the execution. I would like to have one task which runs every time, instead of duplicating the task over and over 14 times in my script.
Basically, it is an audit log, which I set variables and then insert into a SQL table the variables.
I would like to do this:
Task1 ------Success-----> Set Vars -----Success--> Log
|
Task2 ------Success-----> Set Vars -----Success-| (do the Log task again)
|
Task3 -------Success-----> Set Vars -----Success-| (do the Log task again)
|
etc
This works, however, I have to duplicate Log over and over and over. No OR does not work, because it still only executes the Log task once.
Another option I thought of, but cannot find a way to implement is: Make the Log task "disabled" with no dependencies, then in the Set Vars script, enable and execute Log and disable again.
Any ideas?
View 8 Replies
View Related
Oct 14, 2007
Hello,
After doing some research it seems like you can only push the same table once using rda.push -- is this correct? If yes, are there any other alternatives for saving changes to the table back to SQL Server aside from merge replication?
One idea I am toying with is to pull the tracked table with 0 records, save changes to the tracked table, push, drop table and pull, repeating this process everytime I push the data. Wondering is somebody has any advice?
Thanks!
View 1 Replies
View Related
Oct 16, 2015
I have a query to check the records of job has received after 4pm Monday to Friday and it has completed before 9am next day and also weekend it should be Friday after 4pm and before Monday 10am for particular financial year period. I have my job table which has full date but it doesn't show the date exactly Monday to Friday it shows only as 12-12-2014 like that.
View 2 Replies
View Related
Dec 21, 2007
Hey all,
I am still pretty new to all of this and I am having problems accessing a the same DB twice in my page. I want to pull information once in one spot, but then pull different information in a different spot on the page. Anyway, in the first spot, I have the following code:
Dim conString As String = WebConfigurationManager.ConnectionStrings("DataConn").ConnectionString
Dim con As New SqlConnection(conString)Dim cmd As New SqlCommand("Select Team1.TeamID as Team1ID, Team1.TeamName as HomeTeam, Team2.TeamID as Team2ID, Team2.TeamName as AwayTeam, Football_Schedule.ScheduleID as ScheduleID from (Select * from Football_Teams) as Team1, (Select * from Football_Teams) as Team2, Football_Schedule where Football_schedule.team1 = Team1.teamid AND Football_Schedule.team2 = Team2.teamid order by Football_Schedule.WeekNum, Team1.TeamName, Team2.Teamname", con)Using con
con.Open()
Dim RS As SqlDataReader = cmd.ExecuteReader()
While RS.Read()
blah blah blah
End While
End Using
Then in my other spot on the page I have the following:
Dim cmdresults As New SqlCommand("Select Users.Firstname, sum(PointsID) as TotalPoints from Football_Input, Football_Schedule, Users where Football_Input.TeamID = Football_Schedule.winID and users.userid = Football_Input.UserID Group by Users.firstname")
Using con
con.Open()Dim RD As SqlDataReader = cmdresults.ExecuteReader()
While RD.Read()%>
<tr>
<td><%=RD%>
</td>
</tr>
<%End While
End Using
When I try to execute I get this error, "ExecuteReader: Connection property has not been initialized." on the following line, "
RD As SqlDataReader = cmdresults.ExecuteReader()" Any ideas? If possible a little explanation on how multiple connections to the same database work would be nice just for future reference.
Thanks in advance!!,
Chris
View 5 Replies
View Related
May 15, 2000
I need to create a linked server that can access more than one database.
I assume, the only wat this can be done by creating two separate links from the local server using Microsoft OLE DB Provider for SQL server. But the
problem is the Server Name. I cannot use the same servername twice and
if I use a name that is not an exisiting SQL server name I get an error
that "server not found".
So, how do I addlink the same server a mulitple times to access different databases in the server?
Thanks.
Ranjit
View 3 Replies
View Related
Oct 18, 2004
To All--
First off, I'm new to SQL Server and apologize if this is a trivial question.
What I'm trying to do is alias the same table with 2 different names so that I may join them on two different fields.
Table1
Emp_Number
Emp_Code_1
Emp_Code_2
Table2 (Contains a list of codes and their related descriptions)
Emp_Code
Long_Desc
Short_Desc
I'm trying to query Table1 for the Emp_Number but I want to get the Short_Desc from Table2 for both Emp_Code1 and Emp_Code2. I'm using Microsoft Access as the front end (using Pass Through Queries) and SQL Server on the back end.
Hope this makes sense.
C
View 1 Replies
View Related
Apr 4, 2008
I'm new to SQL Reporting Services but have made some decent headway. I'm stumped by one issue though: How do I repeat a field over and over on a single line?
To elaborate:
I have a table of backup job failures, with a reason and the time it failed.
ID | Reason | Time
1 | No tape | 3/13/3008
2 | Bad drive | 3/14/2008
I'm trying to create a summary, and I want to list the information as follows:
There were 2 failed backup jobs on 3/13/2008, 3/14/2008
My SQL query is as follows:
SELECT Time, COUNT(*) AS NumFailures FROM FailedBackups GROUP BY Time ORDER BY Time ASC
How do I "loop" the Time field on a single line in Reporting Services?
View 6 Replies
View Related
Jun 22, 2015
How is the best way to make a function for summing an arbitrary number of times values (table parm?)- I 've read it's necessary to convert to seconds, sum then convert back, but Im' wondering if there's an alternative.
Here's the example I want to sum:
00:02:01:30
00:01:28:10
00:01:01:50
00:06:50:30
00:00:01:50
View 8 Replies
View Related
Sep 27, 2005
Lets say I have a User Defined Function. I need to call the UDF a number of times within the same View. However, the results are not as expected. The only way (I know of) is to have seperate instances on the same function saved under different names. Is there a way to call a single UDF multiple times within a View without the returned values being messed-up?
View 3 Replies
View Related
Feb 1, 2008
I have basic SQL query that returns a one column result set. For each row returned in this result set, I need to pass the value in the column to a stored procedure and get back a result set.
I have 2 solutions, neither of which are very elegant. I'm hoping someone can point me in a better direction.
Solution 1:
Use a cursor. The cons here, are the SP returns a result set on each pass which generates multiple result sets overall. If there is a way to combine these result sets, I think this solution might work.
Solution 2:
Use a temp table or table variable.
The cons here are, if the schema of the result set returned from the stored procedure changes, the table variable will have to change to accommodate it. This is a dependency I'd rather not create.
Any help is very much appreciated.
View 2 Replies
View Related
