Global Execution
Feb 5, 2007
hey there
Reporting services - page footer
I have got this off the ssw.com.au
Execution Time
="Execution Time: " +
IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).TotalSeconds < 1, "0 seconds",
(
IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours & " hour(s), ", "") +
IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes & " minute(s), ", "") +
IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds & " second(s)", ""))
)
and it is supposed to show like this
Execution time: 1 minute, 10 seconds
but all I get is
Execution Time: 0 Seconds on all reports
Can someone tell me what is wrong please?
jewel
View 3 Replies
ADVERTISEMENT
Jun 17, 2008
The format of execution time is as below: 6/17/2008 4:43:07PM. Is it possible to change the format of execution time into the following format: 17/06/2008 16:43:07 ????
Thanks....
View 4 Replies
View Related
Oct 9, 2006
I have the following execution of a global temporary stored procedure on a remote SQL 2000 server:
insert into targetTable
exec remoteServer.master.dbo.sp_MSforeachdb ' ', @precommand = 'exec ##up_fetchQuery'
This is an ugly duck query but it seems to work fine. when I try to directly execute the remote stored procedure such as with
insert into query_log exec remoteServer.master.dbo.##up_fetchQuery
I get execution error
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure '##up_xportQueryLog'.
Database name 'master' ignored, referencing object in tempdb.
When I try
insert into query_log exec remoteServer.tempdb.dbo.##up_fetchQuery
I get
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure '##up_xportQueryLog'.
Database name 'tempdb' ignored, referencing object in tempdb.
with
insert into query_log exec remoteServer..dbo.##up_fetchQuery
or
insert into query_log exec remoteServer...##up_fetchQuery
I get
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure '##up_xportQueryLog'.
I guess the remote server has trouble resolving the name of the global temp stored procedure when its reference comes in as a remote stored procedure calls. Is there any way to directly call a global temp stored procedure from a remote server or do I need to stick with this goofy-looking work-around?
Dave
View 3 Replies
View Related
Aug 23, 2007
after moving off VS debugger and into management studio to exercise our SQLCLR sp, we notice that the 2nd execution gets an error suggesting that our static SqlCommand object is getting reused from the 1st execution (of the sp under mgt studio). If this is expected behavior, we have no problem limiting our statics to only completely reusable objects but would first like to know if this is expected? Is the fact that debugger doesnt show this behavior also expected?
View 4 Replies
View Related
Dec 7, 2005
Hi I am slowly getting to grips with SQL Server. As a part of this, I have been attempting to work on producing more efficient queries. This post is regarding what appears to be a discrepancy between the SQL Server execution plan and the actual time taken by a query to run. My brief is to produce an attendance system for an education establishment (I presume you know I'm not an A-Level student completing a project :p ). Circa 1.5m rows per annum, testing with ~3m rows currently. College_Year could strictly be inferred from the AttDateTime however it is included as a field because it a part of just about every PK this table is ever likely to be linked to. Indexes are not fully optimised yet. Table:CREATE TABLE [dbo].[AttendanceDets] ([College_Year] [smallint] NOT NULL ,[Group_Code] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[Student_ID] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[Session_Date] [datetime] NOT NULL ,[Start_Time] [datetime] NOT NULL ,[Att_Code] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY]GO CREATE CLUSTERED INDEX [IX_AltPK_Clust_AttendanceDets] ON [dbo].[AttendanceDets]([College_Year], [Group_Code], [Student_ID], [Session_Date], [Att_Code]) ON [PRIMARY]GO CREATE INDEX [All] ON [dbo].[AttendanceDets]([College_Year], [Group_Code], [Student_ID], [Session_Date], [Start_Time], [Att_Code]) ON [PRIMARY]GO CREATE INDEX [IX_AttendanceDets] ON [dbo].[AttendanceDets]([Att_Code]) ON [PRIMARY]GOALL inserts are via an overnight sproc - data comes from a third party system. Group_Code is 12 chars (no more no less), student_ID 8 chars (no more no less). I have created a simple sproc. I am using this as a benchmark against which I am testing my options. I appreciate that this sproc is an inefficient jack of all trades - it has been designed as such so I can compare its performance to more specific sprocs and possibly some dynamic SQL. Sproc:CREATE PROCEDURE [dbo].[CAMsp_Att] @College_Year AS SmallInt,@Student_ID AS VarChar(8) = '________', @Group_Code AS VarChar(12) = '____________', @Start_Date AS DateTime = '1950/01/01', @End_Date as DateTime = '2020/01/01', @Att_Code AS VarChar(1) = '_' AS IF @Start_Date = '1950/01/01'SET @Start_Date = CAST(CAST(@College_Year AS Char(4)) + '/08/31' AS DateTime) IF @End_Date = '2020/01/01'SET @End_Date = CAST(CAST(@College_Year +1 AS Char(4)) + '/07/31' AS DateTime) SELECT College_Year, Group_Code, Student_ID, Session_Date, Start_Time, Att_Code FROM dbo.AttendanceDets WHERE College_Year = @College_YearAND Group_Code LIKE @Group_CodeAND Student_ID LIKE @Student_IDAND Session_Date <= @End_DateAND Session_Date >=@Start_DateAND Att_Code LIKE @Att_CodeGOMy confusion lies with running the below script with Show Execution Plan:--SET SHOWPLAN_TEXT ON--Go DECLARE @Time as DateTime Set @Time = GetDate() select College_Year, group_code, Student_ID, Session_Date, Start_Time, Att_Code from attendanceDetswhere College_Year = 2005 AND group_code LIKE '____________' AND Student_ID LIKE '________'AND Session_Date <= '2005-11-16' AND Session_Date >= '2005-11-16' AND Att_Code LIKE '_' Print 'First query took: ' + CAST(DATEDIFF(ms, @Time, GETDATE()) AS VarCHar(5)) + ' milli-Seconds' Set @Time = GetDate() EXEC CAMsp_Att @College_Year = 2005, @Start_Date = '2005-11-16', @End_Date = '2005-11-16' Print 'Second query took: ' + CAST(DATEDIFF(ms, @Time, GETDATE()) AS VarCHar(5)) + ' milli-Seconds'GO --SET SHOWPLAN_TEXT OFF--GOThe execution plan for the first query appears miles more costly than the sproc yet it is effectively the same query with no parameters. However, my understanding is the cached plan substitutes literals for parameters anyway. In any case - the first query cost is listed as 99.52% of the batch, the sproc 0.48% (comparing the IO, cpu costs etc support this). BUT the text output is:(10639 row(s) affected) First query took: 596 milli-Seconds (10639 row(s) affected) Second query took: 2856 milli-SecondsI appreciate that logical and physical performance are not one and the same but can why is there such a huge discrepancy between the two? They are tested on a dedicated test server, and repeated running and switching the order of the queries elicits the same results. Sample data can be provided if requested but I assumed it would not shed much light. BTW - I know that additional indexes can bring the plans and execution time closer together - my question is more about the concept. If you've made it this far - many thanks.If you can enlighten me - infinite thanks.
View 10 Replies
View Related
Aug 3, 2007
Hello :
How to execute a procedure stored during execution of the report, that is before the poster the data.
Thnak you.
View 4 Replies
View Related
Nov 29, 2004
Here's my case, I have written a stored procedure which will perform the following:
1. Grab data from a table using cursor,
2. Process data,
3. Write the result into another table
If I execute the stored procedure directly (thru VS.NET, or Query Analyser), it will run, but when I tried to execute it via a scheduled job, it fails.
I used the same record, same parameters, and the same statements to call the stored procedure.
Any idea?
View 3 Replies
View Related
Feb 26, 2006
Is there a way to write a SP for all database.
e.g. I see that I can use sp_databases SP in all databases.
How do I write my own SP which will be available in all databases?
The SP that I have is in a particular database, I cannot use it in other databases unless I manually write it in that too. Can I avoid this?
Thanks
View 1 Replies
View Related
Apr 23, 2007
Hi All,I tried to get a global variable in my task scritp by using "Dts.Variables("myVar").Value", every time I've got an errorThe element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there. I've seen some examples online to get global varaibles in task script and all of them display the same code Any idea Franck
View 1 Replies
View Related
Feb 11, 2008
I would like to create a global DB connection. I am going to be using this string throughout my program and want to be able to call the string that references my connection.
Thanks in advance.
View 1 Replies
View Related
Nov 10, 2004
Hello,
In the properties for a DTS package, I have a string variable. I need to execute an update statement based on this variable. I was going to use Execute SQL, but I didn't know now to get a value from the variables section.
I store the value there because I will be using dtsrun or the DTS library to execute the package, which I can change this value upon execution. How do I execute an update based on the value?
Brian
View 3 Replies
View Related
Apr 19, 2001
How to create my own global variable and set its value. In another word, i want to set the value of a variable in one sp and want another sp to see its value.
thanks
Michael
View 2 Replies
View Related
Aug 6, 2001
Have anyone encountered a problem with DTS Global variables. What I am trying to do is keep a counter in a global variable. It works fine when I run the job manually but if I schedule the job it doesn't increment the counter. Any ideas?
Thanks in advance!
Nancy
View 1 Replies
View Related
Feb 20, 2003
Is there a way to declare a global variable that can be used in seperate stored procedures?
I have a statistics table that tracks the inserts and updates for several stored procedures.
I need to have the procedure_A run and insert a count of inserted and updated rows.
Procedure_B needs to identify the row inserted by procedure_A and update the columns with a count of how many it inserted and updated.
Procedure_C needs to do the same as procedure_b.
I want to pass a global variable with the id of the row inserted by procedure_A to the rest of the procedures so that they will know which row to update. How do I do this? Can I?
Thanks.
View 3 Replies
View Related
May 10, 2004
cani define a global time in sql server2000, which all tables in DIFFERENT databases can use. I need this in order to INNER JOIN all tables in SAME/DIFFERENT DBs on basis of this datetime field.
View 7 Replies
View Related
May 27, 1999
Hi all,
I have a user using this tool, which creates a temporrary storeproc in tempdb.
when I don't give him permission on temp_db, it errors out and says you should create a system variable to point to a different database to create this sp.
Can anyone kindly explain to me how could this be done?
I appreciate any comment on this thread.
Thanks in advance.
Jay
View 1 Replies
View Related
Aug 22, 2006
Dear All!
I want to know that how can i declare a global variable in database, assign some value to it, then using it in multiple triggers and procedure then deallocating that.
Please provide a smal example.
Regards,
Shabber.
View 6 Replies
View Related
Feb 17, 2004
Hi,
Is there a way to declare a persistent global variable in SQL Server?
I'd like my stored procs to fetch data in a different source depending on a debug (or development) variable.
For example, I'd like to be able to set a variable to either 0 or 1 (true or false) and have a static SP defined as:
IF @MYVARIABLE = 1
SELECT * FROM Openquery(Server1, 'SELECT * FROM Table1")
ELSE
SELECT * FROM Openquery(Server2, 'SELECT * FROM Table2')
What do you think? Since these SPs should be called a lot, I don't want to store the info in a table, I want it as a global variableso it will be as fast as possible.
Any other suggestions are also welcomed.
Thanks,
Skip.
View 3 Replies
View Related
Jan 31, 2007
If following code, it is ok if I execute part1 and q1 together.
but if I try to execute Q2, I got error
Server: Msg 137, Level 15, State 2, Line 4
Must declare the variable '@str'.
I guess I have to execute part1 and Q2 at same time.
Is there a way to avoid that. I mean after I execute part1 @str will be kept in memory, and I can execut q2 without a problem? (like in SAS)
Thank
/* Part1*/
/* how to make @str global*/
declare @str nvarchar(20);
set @str='%subway%';
/*Q1*/
select *, case regionname when 'telesales' then 't' else 'o' end as rn
from dbo.RPT_ContractDetails
where businessname like @str
/* Q2*/
select contracttypename,regionname, count(*)as cou, sum(fundingamount)as Dollar
from dbo.RPT_ContractDetails
where businessname like @str
group by contracttypename,regionname
View 3 Replies
View Related
Jul 23, 2005
Using SQL2000, I have a DTS that takes data from MySQL to sqlserver, thecatch is I want to specify a specific range of dates.How to use a global variable? At the moment I manually changes the dates andjobs run on a daily basis.sample sql statement from Mysql connection:select *from Table1where date between '1/1/2004' and '6/30/2004'CHANGE TO:select *from Table1where date between @fromdate and @todateTIABob
View 1 Replies
View Related
Jul 20, 2005
Hi all, I need to update some data in a table, based on some criteria.In this case we are talking about the stamping of a price against a job.The update table holds the jobs, and the update_details table holds theactivities performed on each job and the cost for each activity. If ipull back this information using the following codeselect t1.reference,t1.update_id, t2.*from update t1, update_details t2where left(t1.reference,2) in ('EA','ND','SD','ST')and t1.update_id = t2.update_idI get something likeEA 1883 Act1 4.20EA 1883 Act2 3.00EA 1883 Act3 7.50EA 2444 Act1 4.20SD 5433 Act1 5.60I need to update the cost for everything pulled back using the abovesql, to a price determined in another table (activities)the activities table would look something likeActivity_Code Cost_London Cost_RocAct1 5.60 4.20Act2 4.00 3.00Act3 6.20 5.60in a nutshell i need to update the cost in the update details fromCost_roc to Cost_london for all activities for all jobs in the updatetable that have a referance starting with specific letters. The newprices need to be obtained from the activities table.Would be very gratefull for any help on this matterRegards,Ian Selby*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Nov 1, 2007
I have several stored procedures and UDFs that have code that convert between GMT timezone and other U.S. timezones.
for example,
to convert from GMT to CT I use this:
@dtmCentral = dateadd(hour, -5,@GMTdtm)
However, come Nov 4th after 2AM, when DST ends, I need to change this to
@dtmCentral = dateadd(hour, -6,@GMTdtm)
Assuming that I can store this -5 or -6 value in a global variable, I want to have a stored procedure that updates it when DST starts and ends.
Is there a better way of doing this other than the option of going through the entire calculations of figuring out whether it's DST or not each time ?
Thanks.
View 6 Replies
View Related
Jul 7, 2006
The benefit of the actual execution plan is that you can see the actual number of rows passing through each step - compared to the estimated number of rows.But what about the "cost percentages" ?I believe I've read somewhere that these percentages is still just an estimate and is not based on the real execution.Does anyone know this and preferable have a link to something that documents it?Thanks
View 1 Replies
View Related
Jan 12, 2007
Hello guys! Is it possible to declare global sql commands and call it in a rowcommand_function?
Here's what I did...
Dim p_s_syounin2 As New SqlCommand
Dim cnn As New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("StrConn").ConnectionString)
Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)
If (Session("syozokubu_id") = 20) And (Session("syozokuka_id") = 21) And ((Session("kaikyuu_id") = 23)) Then
p_s_syounin2.CommandText= ("UPDATE TE_shounin_zangyou SET p_s_syounin2=syain_hnm FROM TR_syainID WHERE syozokubu_id=20 AND syozokuka_id=21 AND kaikyuu_id=23")
'''' connection string is not placed here acc. to my research
End If
End Sub
Protected Sub my_gridview_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles my_gridview.RowCommand
If e.CommandName = "Approve" Then
cnn.Open()
p_s_syounin2.ExecuteNonQuery()
cnn.Close()
End If
End Sub
I get an error message that says " ExecuteNonQuery: Connection property has not been initialized. "
Please help me.
Thanks guys.
Audrey
View 1 Replies
View Related
Dec 17, 2007
Hi,
Can we create our own system global variable using @@ in Sql server 2000?
If yes, can you please post in the sql code with it?
Alos, is the system global variables are kwywords in Sql or they can be updated?
Thanks in Advance!!!
Shobana
View 1 Replies
View Related
Apr 1, 2004
Is there a way in SQL Server to setup Global Server Variables or Constants
We are working with an Off the Shelf Constituent Management application based on SQL Server. We can only read from the App's DB. So we setup another DB to run SP to access the data in the main DB. One problem we have is that there codes that the app DB uses that we need to reference as criteria in our SP. Example: Code for a phone type of email is 731. So if we want to pull email addresses we need to Select where PhoneType = 731. We found out that each time the main db is rebuilt those codes change. That means finding everytime we used that code and changing it.
It would be great to be able to set a global variable and use it anytime that code is needed.
Any ideas.
View 2 Replies
View Related
May 12, 2004
A sample connection string is something like this....
"Server=sql.company.com;Database=myDatabase;Trusted_Connection=True;"
My question is, since the actual .com must go into the string, if you change host companies,
you will have to go through all pages and also change the connection string.
I know there must be a way to declare the string once in like the global.asax and then
refer each page to that string.
I've been hunting around for a sample on how to do this.
Anyone know a good link to refer to?
Thanks,
Zath
View 2 Replies
View Related
Oct 25, 1999
How can I set a global var in another DTS package from inside the one I'm excuting. An example would be that I have in package #1 an ID that gets assigned, and it executes package #2 which needs the ID from the previous package.
Thanks,
Todd
View 1 Replies
View Related
Dec 7, 2000
A short explanation: I have 4 locations that will import from text files.
These text files will need to be 'stamped' with a location_id. I'd like to pass the location_id to the dts package via a VB program. The reason for the VB program is I'd like to keep users from the Enterprise Manager. Is this possible to accomplish? Thanks for your help.
View 1 Replies
View Related
Feb 23, 2001
I am executing:
--------------------------------
DECLARE @x AS int
SET @lcQuery = 'UPDATE ... WHERE .... ' + ' SET @x = @@ROWCOUNT'
EXEC (@lcQuery)
PRINT @x
--------------------------------
@x does NOT return a value, because it is "local" to the lcQuery execution. As a matter of fact, to execute it, I have to write:
SET @lcQuery = 'DECLARE @x AS int ' +
'UPDATE ... WHERE .... ' +
'SET @x = @@ROWCOUNT'
EXEC (@lcQuery)
How can I pass the variable @x to the progam from within EXEC (@lcQuery)?
How does EXEC (@lcQuery) execute? In a different space?
Thank you.
View 1 Replies
View Related
Mar 24, 2003
How to set and reference Global Variable in a SQL Task (MS SQL 2000)
:)
View 1 Replies
View Related
Feb 24, 2005
Hi:
A regular permanent table is not an option:
Need to exec a procA which at the end also exec procB.
procA will insert to table A and also generate a intermidate result set to a ##A global table, it works fine at this point. However, when it exec the procB at the end of exec procA, the ##A global table is empty when entering procB.
The key is I want to pass a records set to the procB without using a tableTempB. Does a ## global table should be still alive until procB execution is done?
I also tried use table variable, but it does not look like to accept @tableA
as part of the procB's parameters.
Also tried function, but it could not support a #tempTable within it which will do a dynamic query insertion.
thanks
David
View 4 Replies
View Related
Aug 11, 1998
Hi everyone:
I am creating an sp, in which I check for the existence of a global temp table (using the exists)
statement. If the Exists returns a false, I move on to processing without the temp table. If it
returns a true, I utilize the temp table to do some inserts. I create the temp table when my
application first starts up. The problem that I am facing is that the check for the temp table`s
existence seems to be failing. Is there any other way to check for the existence of a global
temp table??
Any info really appreciated
Thanks
Nisha
View 1 Replies
View Related
