Get Time Of Querry??
is there a way to get the time that it takes a querry to run like down to the millsecond
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Sql Sub Querry
I have a single table and I want to check for the duplicates. On the bases of two column. Namley HiNum, Procode. so if there are duplicates I want to print out those records How I can do that in a script. I tried the following codes but I am getting an error. how I can fix it. Or this is completly wrong? . Select * from Claims Where HiNum in (Select HiNum,Procode,Count(*) as NumberOfRows From Claims Group by HiNumber, Procode Having Count(*) > 1)
View Replies !
View Related
I Need Help With A New Querry Again
Hello. I know, you think I'm totally clueless, and you're right. SQL is the most confusing thing for me but I must learn it. So, I am running a query to find out the name of the person who sold the most stoves with the least color purchased. I have ran that query and it works . My problem is that I have to add to that query another column that counts the number of stoves sold, instead of having them displayed one by one. My query is as follows: SELECT EMPLOYEE.Name,STOVE.Color FROM INV_LINE_ITEM, STOVE, EMPLOYEE, INVOICE WHERE INV_LINE_ITEM.FK_Stovenbr=STOVE.SerialNumber AND INV_LINE_ITEM.FK_InvoiceNbr=INVOICE.InvoiceNbr AND INVOICE.FK_EmpID=EMPLOYEE.EmpID AND STOVE.Color IN (SELECT CAST(MIN(Color) AS CHAR(5)) 'Min Color' FROM STOVE) AND EMPLOYEE.Name IN (Select CAST(MAX(Name) AS CHAR(15)) 'Employee Name' FROM EMPLOYEE) ORDER BY EMPLOYEE.Name, STOVE.Color Thank you
View Replies !
View Related
Querry Help!!!
Is there a way I can frame the querry for the following scenario?? column1 -------- item1 item2 item3 ... .. Querry: Select datediff(month,item1,item2) and next for item2 and item3... Thanks
View Replies !
View Related
2 Updates In The Same Querry!
Hi! I 'd like to update the database..I 'd like to update the same field, the first update would set all to 'No' and the second update woyuld set specific records to 'Yes': 1) "Update tblDept SET IsTop ='No' 2) "Update tblDept SET IsTop = 'Yes' WHERE id = 200 " Cheers!
View Replies !
View Related
Querry Question
I have created a small querry and for some reason my container (datalist) is not reconizing the company name This is also the first time that i have created a querry with two select statements in the sproc.... Does it make a difference if there is two select statements in a querry?? When populating a datalist?Not sure if i need to seperate the two statements some kind of way.. Is this ok to use with datalist? Erik.. ALTER PROCEDURE Get_Information_For_Datalist_Global_ @IDProduct AS INT, @IDCompany AS INT AS SELECT Product.ProductName, Product.ProductID,Product.ProductImage, Product.ProductDescription, SprocsTable.* FROM Product INNER JOIN SprocsTable ON Product.ProductID = SprocsTable.ProductID WHERE Product.ProductID = @IDProduct SELECT Company.CompanyID, Company.CompanyName FROM COMPANY WHERE COMPANY.COMPANYID = @IDCompany
View Replies !
View Related
Need Help To Build Querry
I have two tables linked with the colunm name systemId and I need to get all the systems responding to the condition (t1.software & 0000000001) = 1 and if they have any contacts I would like to see them. Here is what I have for the moment however my querry do not return the system info if no contact exist and that is wrong. select customers.customerId,customers.systemId,customers.country from customers,contacts where (customers.software & 0000000001) = 1 and customers.SystemId = contacts.SystemId order by customerid Systems -SystemId -CustomerId -Name ... Contacts -ContactId -SystemId -Name -Tel ...
View Replies !
View Related
Search Querry
hi. i want to have a text box and command button. i will be entering the clients id in the text box and press the command button to search the clients record. this will be search query. i have done that previously in vb6. but dont know what query to write in vb08. Any help. thks.
View Replies !
View Related
Enterprise Mgr Querry Error
There are 3 servers in our group which the Local Enterprise Manager Tool is giving us an error when we try to view local DB table data by right-clicking on the table and selecting return all rows... we get this error I thought this was a permissions error but as far as I know we haven't played with the permissions. This seems to be a common ailment here... does anyone have a fix for the EM tool. An unexpected error happened during this operation. [Query] - Query Designer encountered a Query error: Unspecified error
View Replies !
View Related
Generating A Querry Parameters
Hello, Need a bit of help here. In one table i have my production data on some wheels. Each time the wheel is touched, it gets smaller. Then in another table I have the primary data of that wheel, ie price vendor, make model and scrap diameter. What I'd like to figure out is, In one month, what was the wheel size to start off, what is the end size to give me a total size used during that month. If i could get that, then I think I would be able to figure out what the price cost for useage of that wheel was for the month. Any help? Thanks Brian
View Replies !
View Related
Editing In Querry Analyzer Not In EM
Hello, In SQL if we want to edit a row in a table then we have two ways to do the same, 1. Execute a command to Update the records in sql through querry analyzer. 2. Go to enterprise manager and then open the table and update a new row. But can't we do the editing in the querry analyzer is there any way to do this. That means i select all the records from my table using the following querry. Select * from MyTable and then performs editing in the querry analyzer without executing any update command and without using the Enterprise manager. Can we do this???????? Bye and TC
View Replies !
View Related
Problem With Select Into Querry
I have two database on same server. I want write a querry, witch qering the data from 1st database, than create a new table on the 2nd database. But the in clause after the into clause permanently generate error. Here is the query€™s little part: Select €¦.. into Backup in €™Test.mdb€™ from €¦ I try wrote other ways in €™Test€™ in €™dbo.Test€™ in dbo.Test but none of them work its only work, when i not want pass for the second database How can i pass the result for the 2nd Database
View Replies !
View Related
Simple Querry Question ??
Dear all, I have 2 SQL store procedure which are suppose to return similar number of data. For an unknown reason one of the procedure is returning lasse data than the other. in order to dig out the reason for that I need to compare the two set of data return by both procedure. one by one is a huge woek has I have more than 1000 records. So my question is that what is the easy way to extract data which has been forget or miss ? For example my procA return 500 rows my procB return 400 rows I would like to identify the 100 records which are in ProcA results but not in ProcB results In other words extr^ct the difference between A and B thnaks for your help serge
View Replies !
View Related
Running DTS From Querry Analyzer
Hi,How to run a DTS from query analyzer using some t-sql commands??? Is it possible?? What is the command to run a DTS from QA???and What are the possible ways of running a DTS?? from how many types we can execute a DTS like 2 of it i know from EM directly and from the help of Schedule jobs.
View Replies !
View Related
Date Picker And Querry
hi. i have created a database with a table called customer. hav a field which displays the date. how can i add a query on the date picker so that when i choose a date on the calendar i can view the customers details. in other display the record by choosing a particular date. thx.
View Replies !
View Related
Querry 2 Detect Doubles In A Table
Hi, I have a table which contain a field called 'company' of type 'int'. I want to make a querry which returns all doubles ... for example: I have: after querry the return must be: company company: 1 1 3 3 3 3 11 3 7 2 3 1 2 2 1 4 2 5 Thank you, Sebastian
View Replies !
View Related
Problem With Ative Directory Querry
Hi, I linked my Active Directory catalog on my local machine as linked server. Next I querry this server, and it works pretty. I have problem in similar situation when I linked AD as linked server on other machine and when I querry AD it yields error: Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing the query " SELECT cn, mail, sn, co, distinguishedName, displayName FROM 'LDAP://DC=<domain>,DC=com' WHERE objectClass = 'user' AND objectCategory = 'Person' " for execution against OLE DB provider "ADSDSOObject" for linked server "(null)". What is wrong?
View Replies !
View Related
Use The Reference Column Name In The Same SELECT Querry
Hi, I am writing the following querry. Code Snippet Select dbo.fn_EnGiftId(116) AS 'EnGiftId', dbo.fn_DeGiftId(''+ EnGiftId +'') AS 'DeGiftId' But the above querry giving me the following error When i am executing the querry: Code Snippet Server: Msg 207, Level 16, State 3, Line 1 Invalid column name 'EnGiftId'. The scenario is I have created two functions 1st is for encryption of GiftId and 2nd is for Decryption of the Encrypted Giftid. But in the same select querry i want to use the name of column which i have given to the encrypted data in the selct querry i want to use that same column name in the decryption function uin the same querry. When i am executing the below querry it gives me the required output. So Why not above querry. Code Snippet Select dbo.fn_EnGiftId(116) AS 'EnGiftId', dbo.fn_DeGiftId(''+ dbo.fn_EnGiftId(116) +'') AS 'DeGiftId' i d't want to write the encryption function again and again. Is theer any way to avoid this.?????
View Replies !
View Related
Select +replace Querry Problem..
Hi.every one. I have a problem when I use this querry select replace(Title,'.','')AS Title1, ID from tbl_Sim Where Title1 LIKE '%377%' And here is my record Title : 0988.613.775 ID : 2 and here is the error: Invalid column name 'Title1'. Can any one show me how to run this querry, I want to TRIM the 'Title' before it can be compared with another condition I am a beginner. Thanks alots!
View Replies !
View Related
How To Creat A Database Via SQL Querry Analyzer To A Web Server
I am new to ASP.NET and met a problem about access a database on a Web Server (www.myserver.com). My meaning here that I created a .sql from my localhost to test, but when to upload to my web server on the internet, an error occured: "System.Data.SqlClient.SqlException: SQL Server does not exist or access denied". Anyone here can help me to solve my problem. Waiting for your reply soon. Thanks
View Replies !
View Related
Report Designer : BIDS Seems To Hang After Querry
hi, I'm creating my first report. I do "new project" > "report server project". Thereafter I create my shared datasource. Thereafter I try to add a new report. It ask to enter my querry. I've built up my querry previously with the querry analyser in SSMS, and there it runs quite fast. But when i copy-paste the querry in the reporting designer, and i choose to preview it, it takes more then 20 minutes. however, my querry it quite big, but i've written querries ten times larger in my life. (it's about 50lines: 10 querries, union-ed together and in each one i have 4 innerjoins.... all tables are even almost empty, so it's not that indexes are missing or whatever)... is it known that in the report designer querries are much more slower????
View Replies !
View Related
How To Convert UTC Time (retrieved From SQL) To Local Time In Reporting Services Based On Time Zone
Hi all, I have created a report in SSRS 2005 which is being viewed by users from different Time Zones. I have a dataset which has a field of type datetime (UTC). Now I would like to display this Date according to the User Time Zone. For example if the date is August 07, 2007 10:00 AM UTC, then I would like to display it as August 07, 2007 03:30 PM IST if the user Time Zone is IST. Similarly for other Time Zones it should display the time accordingly. Is this possible in SSRS 2005? Any pointers will be usefull... Thanks in advance sudheer racha.
View Replies !
View Related
How To Create SELECT QUERRY FOR &"A CHANGE ORDER FORM&" In ASP?
Hello. Hope you are all well and having a good day. I've got a problem i would like you to help me out with. My company has got a client who sells meat and my senior tech lead has customized the cart to fit their needs and have created a despatch table with a "simple asp form" that allows them to scan their products in with a wi-fi scanner. The despatch table has the following fields: 1. OrderID 2. Product 3. Quantit 4. sellbydate 5. traceabilitycode and 6. Weight Question 1: how do i create a form in asp that allow a user to view the order no, product and quantity celibate,traceabilitybycode and Weight after scanning the details into the simple form created? Question 2: How can i create an asp form that allows users to search for the Order that populates the orderid, Product, quantity? Question 3: How can i create an asp for that allows users to search for Product with orderid,traceabilitycode and quantity? Question 4: How can i create an asp form that allows users to Trace: Orderid, traceabilitycode, sellbydate and Quantity? Please find below the source code of both the: despatchLotsRowsSimpleForm.asp that allow them to scan details into the table and the despatchLotsExec.asp: THE FORM: <!#include file="../includes/settings.asp"> <!#include file="../includes/getSettingKey.asp"> <!#include file="../includes/sessionFunctions.asp"> <!#include file="../includes/databaseFunctions.asp"> <!#include file="../includes/screenMessages.asp"> <!#include file="../includes/currencyFormat.asp"> <% on error resume next dim mySQL, connTemp, rsTemp ' get settings pStoreFrontDemoMode = getSettingKey("pStoreFrontDemoMode") pCurrencySign = getSettingKey("pCurrencySign") pDecimalSign = getSettingKey("pDecimalSign") pCompany = getSettingKey("pCompany") pAuctions = getSettingKey("pAuctions") pAllowNewCustomer = getSettingKey("pAllowNewCustomer") pNewsLetter = getSettingKey("pNewsLetter") pStoreNews = getSettingKey("pStoreNews") pSuppliersList = getSettingKey("pSuppliersList") pRssFeedServer = getSettingKey("pRssFeedServer") %> <b><%=getMsg(10021,"despatch")%></b> <br> <%=getMsg(10024,"Enter despatch ...")%> The despatchExec.asp Page <% ' WebShop 3.0x Shopping Cart ' Developed using ASP ' August-2002 ' Email(E-Mail address blocked: See forum rules) for further information ' (URL address blocked: See forum rules) ' Details: add e-mail to newsletter list %> <!--#include file="../includes/settings.asp"--> <!--#include file="../includes/getSettingKey.asp"--> <!--#include file="../includes/databaseFunctions.asp"--> <!--#include file="../includes/stringFunctions.asp"--> <!--#include file="../includes/miscFunctions.asp"--> <!--#include file="../includes/screenMessages.asp"--> <!--#include file="../includes/sendMail.asp"--> <% on error resume next dim mySQL, connTemp, rsTemp, pEmail ' get settings pStoreFrontDemoMode = getSettingKey("pStoreFrontDemoMode") pCurrencySign = getSettingKey("pCurrencySign") pDecimalSign = getSettingKey("pDecimalSign") pCompany = getSettingKey("pCompany") pEmailSender= getSettingKey("pEmailSender") pEmailAdmin= getSettingKey("pEmailAdmin") pSmtpServer= getSettingKey("pSmtpServer") pEmailComponent= getSettingKey("pEmailComponent") pDebugEmail= getSettingKey("pDebugEmail") pGiftRandom= getSettingKey("pGiftRandom") pPercentageToDiscount= getSettingKey("pPercentageToDiscount") pStoreLocation = getSettingKey("pStoreLocation") ' form pidOrder = lcase(getUserInput(request.form("idOrder"),50)) pProduct = lcase(getUserInput(request.form("product"),50)) pQuantity = lcase(getUserInput(request.form("quantity"),50)) pPriceToDiscount = 0 ' insert despatch mySQL="INSERT INTO despatch2 (idOrder, product, quantity ) VALUES ('" &pidOrder& "', '" &pProduct& "', '" &pQuantity& "')" call updateDatabase(mySQL, rstemp, "despatchExec") pBody=getMsg(10025,"Despatch confirmed...") &VBcrlf& getMsg(311,"To opt-out click at:") & " (URL address blocked: See forum rules)="&pEmail response.redirect "redit_message.asp?message="&Server.Urlencode(getMsg(10025,"Despatch confirmed")) call closeDb() %> Thank you for your help in advance.. Regards, philip
View Replies !
View Related
(long) Querry Doesn't Fit In &&"query-string&&" Window
hi, i have worked three days on a query to display all my results in a beautiful report. The query is fine because when i execute it in Query Analyzer i have all results i want to see in my statistics-table in my report... One thing: it's contains about 100 unioned statements, which results in a super-long query. Performance is OK because it are all 100 very easy statements that are union-ed together. But, when I copy-paste it in my query-string window/textbox of the report designer, I see that there's a maximum on that textbox lenght, which results in the fact that my long query suddenly stops. Any solutions????
View Replies !
View Related
Problem With Getdate() In Transaction Takes The Insert Time Instead Of The Commit Time
Hi, We need to select rows from the database that have been recently inserted/updated. We have a main primary table (COMMIT_TEST) and a second update table (COMMIT_TEST_UPDATE). The update table contains the primary key and a LAST_UPDATE field which is a datetime (to tell us when an update occurred). Triggers on the primary table are used to populate the update table. If we insert or update the primary table in a transaction, we would expect that the datetime of the insert/update would be at the commit, however it seems that the insert/update statement is cached and getdate() is executed at the time of the cache instead of the commit. This causes problems as we select rows based on LAST_UPDATE and a commit may occur later but the earlier insert timestamp is saved to the database and we miss that update. We would like to know if there is anyway to tell the SQL Server to not execute the function getdate() until the commit, or any other way to get the commit to create the correct timestamp. We are using default isolation level. We have tried using getdate(), current_timestamp and even {fn Now()} with the same results. SQL Queries that reproduce the problem are provided below: /* Different functions to get current timestamp €“ all have been tested to produce the same results */ /* SELECT GETDATE() GO SELECT CURRENT_TIMESTAMP GO SELECT {fn Now()} GO */ /* Use these statements to delete the tables to allow recreate of the tables */ /* DROP TABLE COMMIT_TEST DROP TABLE COMMIT_TEST_UPDATE */ /* Create a primary table and an UPDATE table to store the date/time when the primary table is modified */ CREATE TABLE dbo.COMMIT_TEST (PKEY int PRIMARY KEY, timestamp) /* ROW_VERSION rowversion */ GO CREATE TABLE dbo.COMMIT_TEST_UPDATE (PKEY int PRIMARY KEY, LAST_UPDATE datetime, timestamp ) /* ROW_VERSION rowversion */ GO /* Use these statements to delete the triggers to allow reinsert */ /* drop trigger LOG_COMMIT_TEST_INSERT drop trigger LOG_COMMIT_TEST_UPDATE drop trigger LOG_COMMIT_TEST_DELETE */ /* Create insert, update and delete triggers */ create trigger LOG_COMMIT_TEST_INSERT on COMMIT_TEST for INSERT as begin declare @time datetime select @time = getdate() insert into COMMIT_TEST_UPDATE (PKEY,LAST_UPDATE) select PKEY, getdate() from inserted end GO create trigger LOG_COMMIT_TEST_UPDATE on COMMIT_TEST for UPDATE as begin declare @time datetime select @time = getdate() update COMMIT_TEST_UPDATE set LAST_UPDATE = getdate() from COMMIT_TEST_UPDATE, deleted, inserted where COMMIT_TEST_UPDATE.PKEY = deleted.PKEY end GO /* In our application deletes should never occur so we don€™t log when they get modified we just delete them from the UPDATE table */ create trigger LOG_COMMIT_TEST_DELETE on COMMIT_TEST for DELETE as begin if ( select count(*) from deleted ) > 0 begin delete COMMIT_TEST_UPDATE from COMMIT_TEST_UPDATE, deleted where COMMIT_TEST_UPDATE.PKEY = deleted.PKEY end end GO /* Delete any previous inserted record to avoid errors when inserting */ DELETE COMMIT_TEST WHERE PKEY = 1 GO /* What is the current date/time */ SELECT GETDATE() GO BEGIN TRANSACTION GO /* Insert a record into the primary table */ INSERT COMMIT_TEST (PKEY) VALUES (1) GO /* Simulate additional processing within this transaction */ WAITFOR DELAY '00:00:10' GO /* We expect at this point that the date is written to the database (or at least we need some way for this to happen) */ COMMIT TRANSACTION GO /* get the current date to show us what date/time should have been committed to the database */ SELECT GETDATE() GO /* Select results from the table €“ we see that the timestamp is 10 seconds older than the commit, in other words it was evaluated at */ /* the insert statement, even though the row could not be read with a SELECT as it was uncommitted */ SELECT * FROM COMMIT_TEST GO SELECT * FROM COMMIT_TEST_UPDATE Any help would be appreciated, we understand we could make changes to the application/database to approximate what we need, but all the solutions have identified suffer from possible performance issues, or could still lead to missing deals (assuming the commit time is larger than some artifical time window). Regards, Mark
View Replies !
View Related
It Takes A Long Time To Insert The First Record Each Time When The Program Start
I am using VS2005 (VB) to develop a PPC WM5.0 Program. And I am using SQLCE 3.0. My PPC Hardware is in 400MHz. The question is when the program try to insert the first record into sdf database after each time the program started. It takes a long time. Does anyone know why and how can I fix it? I will load the whole database into a dataset when the program start and do all the "Insert", "Update", "Delete" in this dataset and fill it into database after each action. cn.Open() sda = New SqlCeDataAdapter(SQL, cn) 'SQL = Select * From Table scb = New SqlCeCommandBuilder(sda) sda.Update(dataset) cn.Close() I check the sda.update(), it takes about 0.08s for filling one record into database normally. But: 1. Start the PPC Program 2. Load DB into dataset 3. Create a ONE new record in dataset 4. Fill back to DB When I take this four steps everytime, the filling time is almost 1s or even more! Actually, 0.08s is just a normal case. Sometimes, it still takes over 1s to filling back a dataset which only inserted one record when the program is running. (Even all inserted records are exactly the same in data jsut different in the integer key) However, when I give up the dataset and using the following code: cn.Open() Dim cmd As New SqlCeCommand(SQL, cn) ' I have build the insert SQL before (Insert Into Table values(XXXXXXXXXXXXXXX All field) cmd.CommandType = CommandType.Text cmd.ExecuteNonQuery() cn.Close() StartTime = Environment.TickCount I found that it is still the same that the first inserted record takes more time, but just about 0.2s. And the normal insert time is around 0.02s. It is 4 times faster!!!
View Replies !
View Related
A Time Series Prediction Was Requested With A Start Time Further In The Past Than The Internal Models Of The Mining Model
Hi all, I have a very simple time series model which processing works fine without any problem. However when I run the following query SELECT [TimeSeries].[PriceChange], [TimeSeries].[Symbol], PredictTimeSeries(PriceChange, -3, 2) From [TimeSeries] WHERE [TimeSeries].[Symbol] = 'x' I get the following error: TITLE: Microsoft SQL Server 2005 Analysis Services ------------------------------ Error (Data mining): A time series prediction was requested with a start time further in the past than the internal models of the mining model, TimeSeries, specified in the HISTORIC_MODEL_GAP and HISTORIC_MODEL_COUNT parameters can process. The following is the excerpt of the minding model script related to the two parameters: <AlgorithmParameters> <AlgorithmParameter> <Name>MISSING_VALUE_SUBSTITUTION</Name> <Value xsi:type="xsdtring">Previous</Value> </AlgorithmParameter> <AlgorithmParameter> <Name>HISTORIC_MODEL_GAP</Name> <Value xsi:type="xsd:int">1</Value> </AlgorithmParameter> <AlgorithmParameter> <Name>HISTORIC_MODEL_COUNT</Name> <Value xsi:type="xsd:int">10</Value> </AlgorithmParameter> </AlgorithmParameters> These HISTORIC_MODEL_GAP (1) and HISTORIC_MODEL_COUNT (10) should accommodate PredictTimeSeries(PriceChange, -3, 2). Could anyone shed some light on this?
View Replies !
View Related
Optimization Advice (CPU Time = 15203 Ms, Elapsed Time = 8114 Ms.)!!
Ok, so I have some horribly convuluted SQL that I would love to optomize. I'm not happy leaving it in it's current state, that's for sure! I'm currently working on our test bed servers, so obviously my stats are out because of the "crap-ness" (yes, that's the technical term) of the hardware, but still, it should NEVER need to take this long!! Basically, the issue arises in the nasty join to the career table (one employee can have multiple career lines). Just to make things complicated, employees can have any number of career records on any given date, these can even be input for future career events. The following SQL picks out the latest-current career date for each employee based on the career_date being <= GetDate() and the date of entry for this date being the greatest. E.g. career_date | datetime_created 2009-01-01 | 2006-05-05 13:55:21.000 2007-01-01 | 2006-05-05 13:54:18.000 2007-01-01 | 2006-05-05 13:52:55.000 From the above we want to return 2007-01-01 | 2006-05-05 13:54:18.000 SET STATISTICS IO ON SET STATISTICS TIME ON SELECT a.sAMAccountNameAs 'sAMAccountName' , a.userPrincipalNameAs 'userPrincipalName' , 'TRUE'As 'Modify' , RTRIM(e.unique_identifier)As 'employeeID' , RTRIM(e.employee_number)As 'employeeNumber' , RTRIM(e.known_as) + CASE WHEN RTRIM(e.surname) IS NOT NULL THEN ' ' + RTRIM(e.surname) ELSE NULL ENDAs 'displayName' , RTRIM(e.known_as)As 'givenName' , RTRIM(e.surname)As 'sn' , RTRIM(c.job_title)As 'title' , RTRIM(c.division)As 'company' , RTRIM(c.department)As 'department' , RTRIM(l.description)As 'physicalDeliveryOfficeName' , RTRIM(REPLACE(am.dn,'\',''))As 'manager' , t.full_mobile + CASE WHEN RTRIM(t.mobile_number) IS NOT NULL THEN ' (DD: ' + RTRIM(t.mobile_number) + ')'ELSE NULL END As 'mobile' , t.mobile_numberAs 'otherMobile' , ad.address_ad_countryAs 'c' , ad.address_ad_address1 + CASE WHEN ad.address_ad_address2 IS NOT NULL THEN ', ' + ad.address_ad_address2 ELSE NULL END + CASE WHEN ad.address_ad_address3 IS NOT NULL THEN ', ' + ad.address_ad_address3 ELSE NULL END + CASE WHEN ad.address_ad_address4 IS NOT NULL THEN ', ' + ad.address_ad_address4 ELSE NULL END + CASE WHEN ad.address_ad_address5 IS NOT NULL THEN ', ' + ad.address_ad_address5 ELSE NULL ENDAs 'streetAddress' , ad.address_ad_poboxAs 'postOfficeBox' , ad.address_ad_cityAs 'l' , ad.address_ad_CountyAs 'st' , ad.address_ad_postcodeAs 'postalCode' , RTRIM(ad.address_ad_telephone) + CASE WHEN RTRIM(a.othertelephone) IS NOT NULL AND RTRIM(ad.address_ad_telephone) IS NOT NULL THEN ' (Ext: ' + RTRIM(a.othertelephone) + ')' ELSE CASE WHEN RTRIM(a.othertelephone) IS NOT NULL AND RTRIM(ad.address_ad_telephone) IS NULL THEN 'Ext: ' + RTRIM(a.othertelephone) ELSE NULL END ENDAs 'telephoneNumber' FROM employee e LEFT JOIN career c ON c.parent_identifier = e.unique_identifier AND c.career_date =( SELECTmax(c2.career_date) FROMpwa_master.career c2 WHEREc2.parent_identifier = c.parent_identifier ANDc2.career_date <= GetDate() ) AND c.datetime_created =( SELECT max(c3.datetime_created) FROMpwa_master.career c3 WHEREc3.parent_identifier = c.parent_identifier ANDc3.career_date = c.career_date ) LEFT OUTER JOIN AD_Import am ON am.employeeNumber = c.manager_number INNER JOIN AD_Import a ON a.employeeID = e.unique_identifier LEFT JOIN AD_Telephone t ON t.unique_identifier = e.unique_identifier LEFT JOIN AD_Address ad ON ad.address_pwa_location = e.location LEFT JOIN xlocat l ON l.code = c.location WHERE (a.employeeNumber IS NOT NULL OR a.employeeID IS NOT NULL) SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. (1706 row(s) affected) Table 'AD_Import'. Scan count 4, logical reads 106, physical reads 0, read-ahead reads 0. Table 'AD_Address'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0. Table 'AD_Telephone'. Scan count 2, logical reads 10, physical reads 0, read-ahead reads 0. Table 'Worktable'. Scan count 868, logical reads 956, physical reads 0, read-ahead reads 0. Table 'xlocat'. Scan count 2, logical reads 8, physical reads 0, read-ahead reads 0. Table 'career'. Scan count 5088, logical reads 2564843, physical reads 0, read-ahead reads 0. Table 'people'. Scan count 1697, logical reads 5253, physical reads 0, read-ahead reads 0. Table 'Worktable'. Scan count 826, logical reads 914, physical reads 0, read-ahead reads 0. SQL Server Execution Times: CPU time = 15203 ms, elapsed time = 8114 ms. Any advice on what I can do to optomize? Oh judt to point out that "employee" is a view on the "Table 'people'." EDIT: I know it's pointing out the obvious, but I'm pulling out the managers "DN" from AD_Import based on the manager_number and employeeNumber matching.
View Replies !
View Related
Help Writing Query (find The Entry With The Closest Time Given A Time)
Hi, I have a table which has a few fields, one being "datetime_traded". I need to write a query which returns the row which has the closest time (down to second) given a date/time. I'm using MS SQL. Here's what I have so far: Code: select * from TICK_D where datetime_traded = (select min( abs(datediff(second,datetime_traded , Convert(datetime,'2005-05-30:09:31:09')) ) ) from TICK_D) But I get an error - "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.". Does anyone know how i could do this? Thanks a lot for any help!
View Replies !
View Related
Calculate Elapsed Time Between Dates And Exclude A Time Span.
I need a formula to calculate the time (let's say in minutes) between two dates/times. The problem is that I have to exclude the time between 06 PM and 06 AM and also exclude the time in the weekend (Saturday and Sunday). I will use this in a couple of reports made in Reporting Services. If anyone have an algoritm that could be modified for this and is willing to share this I would be very grateful. Many thanks! /Per Lissel
View Replies !
View Related
Global Temp Tables Getting Dropped Form Time To Time
Hi all, I have created several global temp tables to cache some intermediate results ... However, it seems that after a while those tables will be dropped by SQL Server 2005 automatically (I have not restarted the server and no drop table statement ever executed against those tables). Is this a feature by design? How to make those global temp tables persistence to next service restart? Thanks, Ning
View Replies !
View Related
Problems With Time In Visual Studio; Daylight Saving Time??
After a long period of testing with another problem (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1460100&SiteID=1), I discovered a strange problem with my time notations. It is very strange and I don't know where it's coming from. The next is the problem. I have a table with some item numbers. Besides that field (and a lot of others) I have a timefield and a datefield. The time- and datefield are also combined in another field : "Time/-datefield". It is a combined field of the time- and the datefield. When look directly into my table I see (for example): ItemNo. Date Time Time-/Date 001 07-05-07 8:00 0705-07 8:00 When I build a report with Visual Studio with this simple query: SELECT [Item No_], [Time], [Date], [DateTime] FROM [table$Sales Price] When I run the query, I see: ItemNo. Date Time Time-/Date 001 07-05-07 8:00 0705-07 10:00 The query counts up 2 hours..... When I run the query for a date before 25th of March (starting the daylight saving time), the query counts up only 1 hour. I was thinking about a daylight saving time-problem, but I don't know how to set up my settings on the right way. Anyone any idea?? If more information is needed, please ask.... Thx anyway for helping me out.....
View Replies !
View Related
Access Time Format For SQLS Time Queries
I have Access users linked to SQLS 7 tables. When they do a time query in an Access query window using the same format thats in the SQLS table, they get no return. If they use quotes and wildcards, ("23*") they get returns. What time format in Access do they nesd to use in order to get results from SQLS? The time field in SQLS is "datetime" and looks like this-"23:00:00". The Access users are trying to retrieve all records between midnight (00:00:00) and 7am (07:00:00).
View Replies !
View Related
Web Sync And Partitioned Snapshot - Failed From Time To Time. Why?
Hi! Well.. There's operable web sync with parameterized filter. But sometimes the strange errors appear. Below the list of the errors I got from ComErrorCollection property of MergeSynchronizationAgent instance: ERROR: -2147199433 SOURCE: Merge Replication Provider(Web Sync Server) TEXT: The Merge Agent was unable to start the SQL Server Agent job to generate the partitioned snapshot for this subscription. Verify that the SQL Server Agent service is running at the Distributor. ERROR: 22022 SOURCE: HOST3MAIN TEXT: SQLServerAgent Error: Request to run job dyn_HOST3MAIN-Customers-Main-2__20061014_14 (from User distributor_admin) refused because the job is already running from a request by User distributor_admin ERROR: 20633 SOURCE: HOST3MAIN TEXT: Failed to start the dynamic snapshot job. Verify that the SQLServerAgent service is running on the distributor. ERROR: 20628 SOURCE: HOST3MAIN TEXT: Failed to generate dynamic snapshot. I'm sure that SQL Server Agent is running... What is the reason and how it can be solved? Thanks Paul
View Replies !
View Related
A Key Time As A Split Variable In Microsoft Time Series?
Hello, I was working with Microsoft Time Series model (MTS) with some data, when in the mining model viewer, decision tree tab, I realized that the key time variable that I define, it was acting like a split variable. So, I ask you, this is possible?, because, for me, this should not happen€¦. After, I review the Data Mining Tutorial by Seth Paul, Jamie MacLennan, Zhaohui Tang and Scott Oveson, and I found, in the Forecasting part, that the key time variable (Time Index) it was acting like a split variable too, in for example, M200 pacific:Quantity and R250 Europe:Quantity. So people, it€™s possible that a key time variable act like a split variable in a MTS model? Thanks
View Replies !
View Related
Printing Local Time Zone In Time Format
I am in need of a format string or simple vb code that can add the local time zone to the end of a time field.. Something like: 9:36 AM EST 9:36 AM PST This timezone will just come from the machine that the reporting server is on. I don't see this listed as a standard format, and I have come up empty so far in my research - anyone got a solution for this? Thanks a bunch!
View Replies !
View Related
Snapshot Agent Failed From Time To Time
Hello ! I just encountered a problem with snapshot replication or, if more detailed, with snapshot agent. My environment consist of W2K Server (sp1) and SQL Server 7.0 (sp2) The issue is when I create publication and then start snapshot agent for initial creation of schema and articles, it fail with the error: "An exception occurred in the Snapshot subsystem. The step failed." Event Log registers this error with the following message: ------------------- "Step 2 of job --- () has caused an exception in the Snapshot subsystem and has been terminated" ------------------- the step 2 in REPL-Snapshot job is "Run Agent" Once more detail. The metter is that this occurs not consistently but from time to time. Some publications succeed in snapshot process but others don't. And else, if I want to manually run snapshot agent for the second time after successful completion for the first, I get the same error. Any idea????? Thanks in advance Vyacheslav
View Replies !
View Related
|
TRACKER
