Hi y'all I have a problem in my query.
This is my query
SELECT dbo.BOS_GL_Workplace.WpszState, Emp2.szName, Emp1.szName AS szEmpName, Emp1.szEmployeeId,
dbo.BOS_PI_Division.szName AS Divisi, dbo.BOS_PI_Team.szDescription,dbo.BOS_SD_Route.szRouteId, dbo.BOS_SD_Route.szDescription, dbo.BOS_SD_Route.szScheduleId,
dbo.BOS_SD_RouteItem.szCustId, dbo.BOS_SD_Route.szOpUserId,dbo.BOS_AR_Customer.szCustId ,dbo.BOS_AR_Customer.szName, dbo.BOS_AR_Customer.CustszAddress_1 AS AlamatKirim, dbo.BOS_AR_Customer.szTaxId,
dbo.BOS_AR_Customer.CustszAddress_1 AS AlamatTagih, dbo.BOS_AR_Customer.CustszCity, dbo.BOS_AR_Customer.CustszZipCode,
dbo.BOS_AR_Customer.szStatus, dbo.BOS_AR_CustSales.szPaymentTermId, dbo.BOS_AR_CustSales.decCreditLimit,BOS_AR_Customer.dtmStart,BOS_AR_Customer.dtmLastUpdated
FROM dbo.BOS_PI_Employee Emp1 LEFT OUTER JOIN
BOS_PI_Employee Emp2 on Emp1.szSupervisorID = Emp2.szEmployeeID Left outer join
dbo.BOS_GL_Workplace ON Emp1.szWorkplaceId = dbo.BOS_GL_Workplace.szWorkplaceId LEFT OUTER JOIN
dbo.BOS_PI_Division ON Emp1.szDivisionId = dbo.BOS_PI_Division.szDivisionId LEFT OUTER JOIN
dbo.BOS_PI_Team ON Emp1.szTeamId = dbo.BOS_PI_Team.szTeamId LEFT OUTER JOIN
BOS_SD_Route on Emp1.szEmployeeId = BOS_SD_route.szOPuserId LEFT OUTER JOIN
BOS_SD_RouteItem on BOS_SD_Route.szRouteId = BOS_SD_RouteItem.szRouteID LEFT OUTER JOIN
BOS_AR_Customer on BOS_SD_RouteItem.szCustID = BOS_AR_Customer.szCUstID LEFT OUTER JOIN
BOS_AR_CustSales on BOS_AR_Customer .szCustId = BOS_AR_CustSales.szCustID
When I run that query, it was working fine the result was correct. Then I tried to add the where clause, like this:
WHERE BOS_AR_Customer.dtmLastUpdated >= '8/1/2006' AND BOS_AR_Customer.dtmLastUpdated <='9/21/2007'
And it was also working fine, the result was also correct--pay attention dudes the date format is mm/dd/yyyy
Then I try to change the date format to dd/mm/yyyy in the where clause like this (because later I realized that, this is the query that gonna be used):
WHERE BOS_AR_Customer.dtmLastUpdated >= '1/8/2006' AND BOS_AR_Customer.dtmLastUpdated <='21/9/2007'
The result was an error message saying:
"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."
Can you tell me whats wrong, please. I appreciate anykinds of help guys, thanks.
I'm having trouble getting a query to run that contains an IF statement: ***************************************** DECLARE @lid int, @ptf varchar(50) SET @lid = 3 set @ptf = '05-00005'
SELECT tblInfoData.PTFileNumber AS [PT File Number],
IF @lid > 3 BEGIN tblInfoData.OrigGrantor AS [Original Grantor], END
tblPropertyData.propStreet AS [Property Address], tblPropertyData.propCity AS City, tblPropertyData.propState AS State, tblPropertyData.propZip AS Zip, tblSaleData.OrigSaleDate AS [Original Sale Date], tblInfoData.Status AS Status FROM tblInfoData INNER JOIN tblPropertyData ON tblInfoData.PTFileNumber = tblPropertyData.PTFileNumber INNER JOIN tblSaleData ON tblInfoData.PTFileNumber = tblSaleData.PTFileNumber WHERE CONTAINS(tblInfoData.PTFileNumber, @ptf)
*********************************************
This throws the following error in Query Analyser:
Server: Msg 156, Level 15, State 1, Line 12 Incorrect syntax near the keyword 'IF'. Server: Msg 170, Level 15, State 1, Line 14 Line 14: Incorrect syntax near 'tblInfoData'.
SELECT Count(*) FROM Incidents I WHERE (Priority = 1) AND (Time_First_Unit_On_Scene IS NOT NULL) AND (DateDiff(s, Time_ClockStart, Time_First_Unit_On_Scene) <= 480) AND (Response_Date BETWEEN '1-Apr-2004') AND ('31-Mar-2005 23:59:59') AND (I.Disposition_ID <> 9 )
...and I get the following error message...
System.Data.OleDb.OleDbException: Difference of two datetime columns caused overflow at runtime.
Hey :)I'm facing a lot of troubles trying to create a new pause/break-system. Right now i'm building up the query that counts how many records that is inside 2 fields. Let me first show you my table: ID (int) | stamp_start (Type: DateTime) | stamp_end (Type: DateTime) | Username (varchar)0 | 17-03-07 12:00:00 | 17-03-07 12:30:00 | Hovgaard The client will enter a start time and a end time and this query should then count how many records that are inside this periode of time. Example: The client enter starttime: 12:05 and endtime: 12:35.The query shall then return 1 record found. The same thing if the user enters 12:20 and 12:50.My current query looks like this:SELECT COUNT(ID) AS Expr1 FROM table WHERE (start_stamp <= @pausetime_start) AND (end_stamp >= @pausetime_end)But this will only count if I enter the exact same times as the one inside the table.Any ideas how I can figure this out?Thanks for your time so far :)/Jonas Hovgaard - Denmark
Locally I have a table named 'countrydetails' in sql server which is created by dbo. I create asp.net page trying to show the details of the country. I write a query in vb.net page and build the project and try to run it, its giving me results.
The same vb.net project is uploaded and put online and when I try to see the details of the country online inspite of having records i m getting no results. In this case the table which was created is having username other than dbo.
I want my query written in vb.net page to run irrespective of the different users who have the database table. The page should run for both dbo and other users also. How do I achieve this? Please advise.
hi, im working on this for a long time. i'm using MSsql-server2000i have a table [visits] that records users visits to rooms. the columns areroom_id, user_id, visits.i want to write a query that can calculate the top 10 rooms that are relatedto any given room. i was thinking of firstly making a function that countshow many users visited both room A and room B, and then running thisfunction on A and all other rooms, and order by the result. i keep gettingweird errors when doing that. please elaborate.
Hello all, I have a job in sqlserver 2005 that has 2 steps. step 1 actualy verifies the netbackup files and step2 deletes those files from the disk if it is already being backed in netbackup. But this job is failing and in the job history i am seeing the errors "The job was stopped prior to completion by User "username". The Job was invoked by Schedule 2 (Schedule1). The last step to run was step 1 (NetbackupStatus)." This is master slave job and this job is working fine in all the other servers except this one. The sql server service and the agent are running on the acct. which is an admin on that box. Can someone help me out please.
Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed. This is the error i get every now and then when i attempt to browse to a page. It seems that i am getting this error as its trying to connection to a sql express database??? Only i have set my project up to use sql server 2000 and i am using that DB but every now and then i get this error. Im not sure what else i need to do? Any ideas?
While MOM processes are running at some point, a process goes into deadlock and uses up all existing CPUs.
Sysprocesses shows this it opened up 4 threads and program name is Microsoft® Reliability Analysis Service.
Profiler doesn't show which command it was trying to execute, but last notable command which has started was MRAS_pcLoad EXECUTE @i_Return_Code = sp_getapplock @Resource = N'MOM.Datawarehousing.DTSPackageGenerator.exe', @LockMode = N'Exclusive', @LockOwner = N'Session', @LockTimeout =
Can you please help us, what could be the problem. It has been running fine till couple of days back.
I have created 2 tables in a database which are mostly similar, the table1 will execute with more speed (take only less than or equal to 1 sec) but the table2 will take 4 or 5 secs to execute the query,moreover the similar datas was presented in both the tables. the eg:- query that i have executed is select max(c_code) from table1 and select max(c_code) from table2, the first one take less than 1 sec and the second one take more than 4 or 5 secs, also there is a procedure i hve written to update both the tables, and i got the time out error, if the table1 alone is updated using the procedure it is OK but the table2 alone is updated using the procedure the time out error will be shown, pls reply the reason for this problem as early as possible, it will be a grateful if anybody reply for this trouble?
Hi Group,My apologies for the lengthy post, but here goes...I have the following tables:TABLE Vehicles([ID] nvarchar(5),[Make] nvarchar(20),[Model] nvarchar(20),)TABLE [Vehicle Status]([ID] int, /* this is an auto-incrementing field*/[Vehicle ID] nvarchar(5), /* foriegn key, references Vehicles.[ID] */[Status] nvarchar(20),[Status Date] datetime)Here's my problem...I have the following data in my [Vehicles] and [Vehicle Status] tables:[ID] [Make] [Model]----------------------H80 Nissan SkylineH86 Toyota Aristo[ID] [Vehicle ID] [Status] [Status Date]----------------------------------------1 H80 OK 2006-10-012 H80 Damage 2006-10-053 H86 OK 2006-10-134 H86 Dent 2006-10-155 H86 Scratched 2006-10-16I need a query that will join the two tables so that the most recentstatus of each vehicle can be determined. I've gotten as far as:SELECT Vehicle.[ID], Make, Model, [Status], [Status Date] FROM[Vehicles] INNER JOIN [Vehicle Status] ON [Vehicles].[ID] = [VehicleStatus].[Vehicle ID]Of course this produces the following results:[ID] [Make] [Model] [Status] [Status Date]--------------------------------------------H80 Nissan Skyline OK 2006-10-01H80 Nissan Skyline Damage 2006-10-05H86 Toyota Aristo OK 2006-10-13H86 Toyota Aristo Dent 2006-10-15H86 Toyota Aristo Scratched 2006-10-16How do I filter these results so that I get only the MOST RECENT vehiclestatus?i.e:[ID] [Make] [Model] [Status] [Status Date]--------------------------------------------H80 Nissan Skyline Damage 2006-10-05H86 Toyota Aristo Scratched 2006-10-16Thanks in advance,Rommel the iCeMAn*** Sent via Developersdex http://www.developersdex.com ***
I am really stuck here, I hope to get some helpful answers on this forum.
Ok, I have three four tables in my db, 1- Stages 2- Activities 3- Tasks 4- Subtasks Structure is like: Stages 1- SrNo (Unique) 2- Stage 3- StartDate 4- FinishDate
Now what i want is to update Tasks, StartDate and FinishDate according the Min(StartDate) and Max(FinishDate) of related Subtasks and same for Activities and Stages. I have tried following query to Update tasks, StartDate and FinishDate
Update Tasks Set Tasks.StartDate=(Select Min(Subtasks.StartDate) from Subtasks,Tasks where Tasks.SrNo=Subtasks.SrNo1) from Subtasks,Tasks where Tasks.SrNo=Subtasks.SrNo1
But this query updates all Tasks with Min and Max date from Subtasks regardless of their relation.
Can anyone explain what's wrong with this code which is supposed to populate a dropdownlist using a parametised stored procedure and SqlDataAdapter? objCmd = new SqlCommand("bs_GetActivityValueTypes", objConn.GetConnection()); objCmd.Parameters.Add("@scid", SqlDbType.Int);objCmd.Parameters["@scid"].Value = Convert.ToInt32(Request.QueryString["scorecard"].ToString()); objAdapter = new SqlDataAdapter(objCmd);objAdapter.Fill(dsInitData, "tblValueTypes"); comboResultType.DataSource = dsInitData.Tables["tblValueTypes"]; comboResultType.DataValueField = "Type_ID";comboResultType.DataTextField = "Type_Desc";comboResultType.DataBind(); comboResultType.Items.Insert(0, new ListItem("- Select a value type -", ""));Basically, if I remove the parameter from the code and the stored procedure it works fine, but when I add the parameter back I get an "Incorrect syntax near 'bs_GetActivityValueTypes'" error at line: objAdapter.Fill(dsInitData, "tblValueTypes"); This makes no sense to me because I know the stored procedure is well formed and I've used almost identical code elsewhere. All variables are declared further up the code. Someone help please!
OK heres the situation, I have a Categories table and a Products table, each Category can have one or many Products, but a product can only belong to one Category hence one-to-many relationship.
Now I want to do a SELECT query that outputs all of the Categories onto an ASP page, but also displays how many Products are in each category eg.
CatID | Name | Description | No. Products
0001 | Cars | Blah blah blah | 5
etc etc
At the moment I'm doing nesting in my application logic so that for each category that is displayed, another query is run that returns the number of products for that particular category. It works ok!
However, is there a way to write a SQL Statement that returns all the Categories AND number products from just the one SELECT statement, rather than with the method I'm using outlined above? The reason I'm asking is that I want to be able to order by the number of products for each category and my method doesn't allow me to do this.
Is it possible to get data-type,default-value, etc. (basically schema information) of columns in a particular query.This is something similar to what we get when we execute the followingÂ
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Employees'
But instead of TABLE_NAME i would like to give a query like
SELECT [EmployeeID],[LastName],[FirstName] from [dbo].[Employees] order by [EmployeeID].
i am testing some encryption scenarios ,in profiler the statements like "OPEN KEY" and all "Encrypt" and "Decrypt" functions are removed automaticly from the trace and replaced with a comment ,create a trace and try the code i attached , you will see in profiler trace that that all encryption related commands are commented out ,this is what expected.
but now go to the batch and comment out the "SELECT @rrr' statment, and run the batch ,this batch will fail beacuse "@rrr" is not declared, now go back to profiler and you will see that for the failed batch all the encryption command are NOT COMMENTED OUT !!! esspecially important is the visibility of the password of the open key command.
seems like a very dangerous bug to me!!!
CREATE CERTIFICATE test1 ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y' WITH SUBJECT = 'Sammamish Shipping Records', EXPIRY_DATE = '10/31/2009'; GO
CREATE SYMMETRIC KEY Key09 WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE test1; GO declare @Str nvarchar(100) declare @Enc varbinary(max) set @Str = 'encrypt this' OPEN SYMMETRIC KEY Key09 decryption by CERTIFICATE test1 WITH PASSWORD = 'pGFD4bb925DGvbd2439587y'
SET @Enc = EncryptByKey(Key_GUID('Key09'), @Str); ---select @rrr select CONVERT(nvarchar(100), DecryptByKey(@Enc)) go
I am doing a distinct count on a related table's column, but get an out of memory error if I run it for the entire table (works great for just a few rows when filtered down).The error I get is: "We couldn't get data from the external source.The operation has been cancelled because there is not enough memory available for the application. If using 32-bit version of the product consider upgrading.
I know I can add a related column and that works fine...but that seems to me like I've defeated the purpose, I have a good and proper lookup table, and should be able to run my query against its relationship.Here is the query and details below *Note I supplied a scaled down sample, on my actual model I receive these errors, not in the sample as it has only a few rows
List Workers Distinct Project Customers:=CALCULATE(DISTINCTCOUNT(Projects[CustomerID]),'WorkersToProjects') Other measure which returns no errors, but included for completeness: List Workers Projects:=CALCULATE(DISTINCTCOUNT([ProjectID]),ISBLANK(WorkersToProjects[ProjectID])=FALSE())
My goal here is to allow the user to view the workers assigned to a project, but also get counts of the workers assigned to the CUSTOMER of a project. For example, suppose we lose a customer, we want to see how many workers would be impacted by that, so a count of projects per worker is not useful there, we need to see a count of workers per project's customer (owner of project whom project work is being done for)The question being: How can I accomplish this:
1. WITHOUT adding a calculated column to WorkersToProjects (of Projects.CustomerID) 2. WITH better performance?
There must be a better way to write this DAX to still get the correct answer?*Pic of pivot table, again, the numbers are accurate but the formula used to List Workers Distinct Project Customers measure does NOT scale :( 3 count for red , the number of Projects John has and 2 count for blue, the unique customers/owners of those projects "Veridian Dynamics" and "Massive Dynamic". URL....
We recently upgrade to 2005(sp). We have one report that ran fine in 2000 but leaves out data from certain columns (date related) in the results, so we chalked it up to being a non compatiable issue. So, I decided to try and switch the DB back to 2000 compatibility (in our test env) and then back to 2005. After that the report started returning the proper data. We can€™t really explain why it worked but it did. So we thought we would try it in prod (we knew it was a long shot) and it didn€™t work. So the business needs this report so we thought we would refresh the test system from prod, but now we are back to square one. I was wondering if anyone else has heard or seen anything like this. I am open to any idea€™s, no matter how crazy. J The systems are configured identically. Let me know if you need more information.
i have migrated a DTS package wherein it consists of SQL task.
this has been migrated succesfully. but when i execute the package, i am getting the error with Excute SQL task which consists of Store Procedure excution.
But the SP can executed in the client server. can any body help in this regard.
Hi All, can someone help me, i've created a stored procedure to make a report by calling it from a website. I get the message error "241: Syntax error converting datetime from character string" all the time, i tryed some converting things but nothig works, probably it is me that isn't working but i hope someone can help me. The code i use is:
CREATE proc CP_Cashbox @mID varchar,@startdate datetime,@enddate datetime as set dateformat dmy go declare @startdate as varchar declare @enddate as varchar
--print "query aan het uitvoeren"
select sum(moneyout) / sum(moneyin)*100 as cashbox from dbo.total where machineID = '@mID' and njdate between '@startdate' and '@enddate' GO
update tblPact_2008_0307 set student_dob = '30/01/1996' where student_rcnumber = 1830when entering update date in format such as ddmmyyyyi know the sql query date format entered should be in mmddyyyy formatis there any way to change the date format entered to ddmmyyyy in sql query?
Hi, This is my code: SqlCommand getPreviousDateCmd = new SqlCommand("SELECT Top 1 Open_date FROM Counter WHERE (Open_date <= @todays_date) ORDER BY Open_date DESC", myConnection); //Get previous date getPreviousDateCmd.Parameters.AddWithValue("@todays_date", DateTime.Now.ToString("dd/MM/yyyy")); DateTime previousDateTime = Convert.ToDateTime(getPreviousDateCmd.ExecuteScalar().ToString()); //previousDate = previousDateTime.ToString("dd/MM/yyyy"); LabelToday.Text = previousDateTime.ToString("dd/MM/yyyy"); //If previous date is same as today's date (Get only date and year)
I get a huge unstoppable(?) error message when it converts STRING to DATETIME. What I want to do is get DATETIME object and change the format to "dd/MM/yyyy" Could someone give me some ideas? Thankx!
Hi I am trying to write a query involve parameters. For example, the query: Select * from myTable where myDateTime=@dt; If I run the query, I was asked to enter value for the parameter. The query can be generated, however I can't save it, the error message says: Must declare the variable @dt. When I tried to declare it, the system doesn't support it. I am using SQL Server Managerment Studio 2005. I also tried the query without the parameter: Select * from myTable where myDateTime=31/07/2007; But it didn't return record for any datetime format. Could anyone help please? I just want to get some records filtered by a certain DateTime. Claire
Hey gang, I need a little help creating a Query. I have a table storing info about visitors to our company. The name of the table is "visitors" and it has an "arrivalDate" field and a "departureDate" field. Both of these fields are of DateTime data type and both fields are populated prior to the visitors arrival, neither field will accept a null value. I need to run a report each day to see which visitors are currently on site. I am by no means a SQL expert and I am drawing a blank on how to extract this info?? Entry Example: name: John DoearrivalDate: 1/16/08departureDate: 1/20/08So every morning between 1/16/08 and 1/20/08 John Doe must show up on the report I was trying to do something like this with my code (using todays date): SELECT name FROM visitors WHERE ((MONTH(arrivalDate) = 1 AND DAY(arrivalDate) >= 15 AND YEAR(arrivalDate) = 2008) AND (MONTH(departureDate) ?????
my database contains 1 field with "datetime" in sql serverhw can i query the database to just compare the date section of that field ??? and not the time
e.g. 3/7/2005 4:24:01 AM My query is below :- -- select a.date, b.useruri as 'FROM', c.useruri as 'TO', a.body as 'MESSAGE' from messages as a inner join users as b on a.fromid = b.userid inner join users as c on a.toid = c.userid where a.date like '%2005-03-01%' order by a.date
Hey gang, I need a little help creating a Query. I have a table storing info about visitors to our company. The name of the table is "visitors" and it has an "arrivalDate" field and a "departureDate" field. Both of these fields are of DateTime data type. I need to run a report each day to see which visitors are currently on site. For a partial example, I will use today's date of 1/16/08:
SELECT name FROM visitors WHERE ((MONTH(arrivalDate) = 1 AND DAY(arrivalDate) >= 15 AND YEAR(arrivalDate) = 2008) AND (MONTH(departureDate) ?????
It is the second part of the query that I am having trouble with. Am I going about this all wrong? Is there a way to extract this information when all I know is the persons arrival date, departure date, and the current days date? Any help would be greatly appreciated.
select indxid, indxname, max(createddate) from outmailtab where indxname like 'update%' group by indxid, indxname
but still its getting me same results..i think because of datetime..date is same for different indxid but time is different for all...so can anyone help me to figure it out with datetime function..
Hi,I have a table called Bookings which has two important columns;Booking_Start_Date and Booking_End_Date. These columns are both of typeDATETIME. The following query calculates how many hours are availablebetween the hours of 09.00 and 17.30 so a user can see at a glance how manyhours they have unbooked on a particular day (i.e. 8.5 hours less the timeof any bookings on that day). However, when a booking spans more than oneday the query doesn't work, for example if a user has a booking that startson day one at 09.00 and ends at 14.30 on the next day, the query returns 3.5hours for both days. Any help here would be greatly appreciated.SELECT 8.5 - (SUM(((DATE_FORMAT(B.Booking_End_Date, '%k') * 60 ) +DATE_FORMAT(B.Booking_End_Date, '%i')) - ((DATE_FORMAT(B.Booking_Start_Date,'%k') * 60 ) + DATE_FORMAT(B.Booking_Start_Date, '%i'))) / 60) ASAvailable_Hours FROM WMS_Bookings B WHERE B.User_ID = '16' ANDB.Booking_Status <> '1' AND NOT ( '2003-10-07' <DATE_FORMAT(Booking_Start_Date, "%Y-%m-%d") OR '2003-10-07' >DATE_FORMAT(Booking_End_Date, "%Y-%m-%d") )Thanks for your help
Hello ,I have a table field named BookedDateTime and its date type is DateTime. It contains the order booked date and time. I have a query to list all the orders which happens on specific date, no matter what time during the day.Because the BookedDateTime combines the date and time, I cannot get rid of the time. So each time when I do the query , I get nothing. How can I solve the problem?Thanks,
