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....
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?
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?
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.
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.
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
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.
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?
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.
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?
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?
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.
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.
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.
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.
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
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
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!
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
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. "
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
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.
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.
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.
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.
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?
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.
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??
