While testing an update script I found that a number of data rows are inaccessible using my query, though the data does exist in the table. I can browse records to find the data or query the record by some column names, but not by others.
For example:
SELECT name FROM tblPersonalInformation WHERE [ID Number] = 2358899;
would not find the data sought. However, if I put in:
SELECT address FROM tblPersonalInformation WHERE name = ‘Doe, John’;
the query yields the desired data. Many of the records queried display the specified data with no problem. I have found this problem to exist on a number of data rows, but can’t figure out a reason. The front-end application displays the data without any apparent problems.
While testing an update script I found that a number of data rows are inaccessible using my query, though the data does exist in the table. I can browse records to find the data or query the record by some column names, but not by others.
For example:
SELECT name FROM tblPersonalInformation WHERE [ID Number] = 2358899;
would not find the data sought. However, if I put in:
SELECT address FROM tblPersonalInformation WHERE name = ‘Doe, John’;
the query yields the desired data. Many of the records queried display the specified data with no problem. I have found this problem to exist on a number of data rows, but can’t figure out a reason. The front-end application displays the data without any apparent problems.
I need to make a selection on join datasets with 2 conditions and populate the results in another dataset(Report).It is working with the fist condition "AccountingTypeCharacteristicCodeId = 3"...
INSERT INTO SurveyInterface.tblLoadISFNotification (OperatingEntityNumber, SDDS, SurveyCodeId, QuestionnaireTypeCodeId, ReferencePeriod, DataReplacementIndicator, PrecontactFlag, SampledUnitPriority) SELECT ISF.OperatingEntityNumber       ,[SDDS]       ,[SurveyCodeId]       ,[QuestionnaireTypeCodeId]       ,[ReferencePeriod]       ,[DataReplacementIndicator]       [code]....
Know I also want to add in that new dataset(report) all the duplicates of concatenated variables
ISF.OperatingEntityNumber/ISF.QuestionnaireTypeCodeId GROUP BY ISF.OperatingEntityNumber, ISF.QuestionnaireTypeCodeId
We have records in one table that are marked as accepted/rejected based on eligibility start and end dates in another table. We're loading new eligibility data into an ETL table and if the start or end date is going to change, I want to report any records that need to be reviewed to see if their status should change. The new dates could be before or after the existing dates, and the new or existing end date could also be NULL. Currently I'm using 4 > < statements and it seems to catch any scenario, but I'm wondering if there's a better way:
DECLARE @RECORDS TABLE(RecordDate date,ID varchar(8)) INSERT INTO @RECORDS(RecordDate, ID)VALUES('20100101','99'),('20110101','99'),('20120101','99'),('20130101','99'),('20140101','99') DECLARE @ORIGINALDATES TABLE(StartDate date,EndDate date,ID varchar(8)) INSERT INTO @ORIGINALDATES(StartDate,EndDate, ID)VALUES('20100101',NULL,99)
I have created this query in SQL Server 2005 which uses an MS SQL Server data source - I've tested it and it perfroms as it should:
SELECT DISTINCT WIP.R0 AS [Job No], WIP_R23.R0 AS [Pack No], PlanningM2.R1 AS [Part No], WIP.R17 AS FKF, WIP2.R17 AS FKF2, SUBSTRING(PlanningM2.R0, 1, 3) AS Ref, PlanningM2.R5 AS Qty, LTRIM(RTRIM(WIP_R23.R23)) AS Shortages, LEN(PlanningM2.R1) AS StrLen, Stock.R2 AS [Stock Qty], WIP.R10 AS Status, WIP2.R10 AS [Pack Status], WIP.R15 AS [Qty UC] FROM C001_UNIDATA_WIP_NF AS WIP INNER JOIN C001_UNIDATA_PLANNINGM_NF AS PlanningM ON WIP.R0 = PlanningM.ASSY INNER JOIN C001_UNIDATA_PLANNINGM_NF AS PlanningM2 ON PlanningM.R1 = PlanningM2.ASSY INNER JOIN C001_UNIDATA_WIP_R23 AS WIP_R23 ON PlanningM.R1 = WIP_R23.R0 INNER JOIN C001_UNIDATA_WIP_NF AS WIP2 ON WIP_R23.R0 = WIP2.R0 INNER JOIN C001_UNIDATA_STOCK_NF AS Stock ON PlanningM2.R1 = Stock.R0 WHERE (WIP_R23.R23 IS NULL) AND (WIP.R0 LIKE N'%' + @RP1 + N'%') OR (WIP.R0 LIKE N'%' + @RP1 + N'%') AND (PlanningM2.R1 = RIGHT(LTRIM(RTRIM(WIP_R23.R23)), LEN(PlanningM2.R1))) ORDER BY [Job No], [Pack No]
I am now trying to recreate this query using ODBC and am having one helluva problem with it as it doesn't seem to like many of the functions I've used, such as LTRIM, RTRIM, LEN, RIGHT and won't accept the parameter I've included, or any other parameter come to think of it.
Has anyone else had a similar problem at all, and could you direct me to a solution if you have?
Hi all, We have a table that is full text enabled and it is working fine, but the full text search doesn't returns any record for the following case
select * from let_catalog_search where contains(search_field,'"Bulk Process 1*"') even though there exist records that satisfy the condition in the table, the record that i am talking abt is "bulk process 1 with price bp100-ilt1-00200136 bp100-ilt1"
If I remove the last 1 from the search string i get lot of records, Can anybody help me out.
How can I find calls which do not exist in stored procedures and functions?We have many stored procedures, sometimes a stored procedure or function which is called does not exist. Is there a query/script or something that I can identify which stored procedures do not 'work' and which procedure/ function they are calling?I am searching for stored procedures and functions which are still called, but do not exist in the current database.
I've got a stored proc used for order generation which runs long sometimes when called from within our app. A normal run will complete within 20s, a long run will get terminated by the app at the 6 minute mark.
When it runs long once, repeated attempts will also do so until I execute the same query the app did, but from within Query Analyzer. At which time the problem will disappear for a day or two. The app connects to the SQL Server 200 SP4 database using ADO.
I suspected statistics might be at fault here but have tried both "UPDATE STATISTICS table WITH FULLSCAN" and "DBCC DBREINDEX('table') to no avail. This issue has occurred and been worked around in this manner a few dozen times.
I'm pulling data from Oracle 8.05 using Oracle ODBC with DTS to SQL Server 7 sr2. If I choose my columns only, I have no problem. When I try to edit the SQL statement either in the Import Wizard or the DTS Designer, the package won't run. I am selecting on a date column. I corrected the TSQL using the "convert" statement. Does someone know what might be happening?
Hi can any one see why this will now run when sent to the server. Don't worry about the parameters as these are replaced at runtime in a c# app I have created.
It is not the easiest to read but your help is appreciated.
Code Snippet /* Create new table of dates using the Week Ending date selected by the user This is a list of week ending dates between the start and end dates selected by the user */ DECLARE @Temp AS DATETIME DECLARE @result table(WeekEndDate datetime) SET @Temp = @Start + ((@WeekEnding - (DATEPART(w, @Start) - 1)) % 7) + CASE WHEN (@WeekEnding - (DATEPART(w, @Start) - 1)) < 0 THEN 7 ELSE 0 END
WHILE @Temp <= @End BEGIN INSERT INTO @result values(@Temp) SET @Temp = @Temp + 7 END
/* Aggregate data by Week ending date so that we can see the Metrics for that particular week*/ SELECT dbo.MSP_WEB_PROJECTS.PROJ_NAME AS Project, EV_View.[Week Ending], EV_View.BAC, EV_View.EV, EV_View.PV, EV_View.AC, EV_View.FV, EV_View.FC, EV_View.[Baseline Work], EV_View.[Work], EV_View.[Actual Work], EV_View.[ETC], CASE WHEN EV_View.[Week Ending] BETWEEN dbo.MSP_VIEW_PROJ_PROJECTS_STD.ProjectStartDate AND dbo.MSP_VIEW_PROJ_PROJECTS_STD.ProjectFinishDate THEN 'YES' ELSE 'NO' END AS [1 Project Duration] FROM ( SELECT ProjectID, WeekEndDate AS [Week Ending], SUM(BAC) / @TimeUnits AS BAC, SUM(EV) / @TimeUnits AS EV, SUM(PV) / @TimeUnits AS PV, SUM(AC) / @TimeUnits AS AC, SUM(FV) / @TimeUnits AS FV, SUM(FC) / @TimeUnits AS FC, SUM([Baseline Work]) / @TimeUnits AS [Baseline Work], SUM([Work]) / @TimeUnits AS [Work], SUM([Actual Work]) / @TimeUnits AS [Actual Work], (SUM([Work]) - SUM([Actual Work])) / @TimeUnits AS ETC FROM ( SELECT ProjectID, WeekEnds.WeekEndDate, BAC, CASE WHEN derivedtbl_1.[Baseline Finish] BETWEEN WeekEnds.WeekEndDate - 6 AND WeekEnds.WeekEndDate THEN derivedtbl_1.PV END AS PV, CASE WHEN derivedtbl_1.[Actual Finish] BETWEEN WeekEnds.WeekEndDate - 6 AND WeekEnds.WeekEndDate THEN derivedtbl_1.EV END AS EV, CASE WHEN derivedtbl_1.[Actual Finish] BETWEEN WeekEnds.WeekEndDate - 6 AND WeekEnds.WeekEndDate THEN derivedtbl_1.AC END AS AC, CASE WHEN (derivedtbl_1.Finish BETWEEN WeekEnds.WeekEndDate - 6 AND WeekEnds.WeekEndDate) THEN derivedtbl_1.[Baseline Work] END AS FV, CASE WHEN (derivedtbl_1.Finish BETWEEN WeekEnds.WeekEndDate - 6 AND WeekEnds.WeekEndDate) THEN derivedtbl_1.[Estimate at Complete] END AS FC, CASE WHEN (TP.AssignmentTimeStart BETWEEN WeekEnds.WeekEndDate - 6 AND WeekEnds.WeekEndDate) THEN TP.[Baseline Work] END AS [Baseline Work], CASE WHEN (TP.AssignmentTimeStart BETWEEN WeekEnds.WeekEndDate - 6 AND WeekEnds.WeekEndDate) THEN TP.[Work] END AS [Work], CASE WHEN (TP.AssignmentTimeStart BETWEEN WeekEnds.WeekEndDate - 6 AND WeekEnds.WeekEndDate) THEN TP.[Actual Work] END AS [Actual Work] FROM ( SELECT dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.WPROJ_ID, dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeStart, SUM(ISNULL(dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeBaselineWork, 0)) AS [Baseline Work], SUM(ISNULL(dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeWork, 0)) AS [Work], SUM(ISNULL(dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeActualWork, 0)) AS [Actual Work], SUM(ISNULL(dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeActualWorkProtected, 0)) AS [Actual Work Protected] FROM dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY INNER JOIN dbo.MSP_VIEW_PROJ_ASSN_STD ON dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.WPROJ_ID = dbo.MSP_VIEW_PROJ_ASSN_STD.WPROJ_ID AND dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentUniqueID = dbo.MSP_VIEW_PROJ_ASSN_STD.AssignmentUniqueID WHERE (dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeStart <= @End) AND (dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeStart >= @Start) ) AS TP INNER JOIN ( SELECT dbo.MSP_WEB_PROJECTS.WPROJ_ID AS ProjectID, dbo.MSP_WEB_PROJECTS.PROJ_NAME AS Project, dbo.MSP_VIEW_PROJ_TASKS_STD.TaskName AS [Task Name], DATEADD(day, 0, DATEDIFF(day, 0, dbo.MSP_VIEW_PROJ_TASKS_STD.TaskBaselineStart)) AS [Baseline Start], DATEADD(day, 0, DATEDIFF(day, 0, dbo.MSP_VIEW_PROJ_TASKS_STD.TaskBaselineFinish)) AS [Baseline Finish], DATEADD(day, 0, DATEDIFF(day, 0, dbo.MSP_VIEW_PROJ_TASKS_STD.TaskActualFinish)) AS [Actual Finish], dbo.MSP_VIEW_PROJ_TASKS_STD.TaskBaselineWork AS [Baseline Work], dbo.MSP_VIEW_PROJ_TASKS_STD.TaskWork AS [Estimate at Complete], dbo.MSP_VIEW_PROJ_TASKS_STD.TaskActualWorkProtected AS [Actual Work], dbo.MSP_VIEW_PROJ_TASKS_STD.TaskRemainingWork AS [Estimate to Complete], dbo.MSP_VIEW_PROJ_TASKS_STD.TaskBaselineWork AS BAC, CASE WHEN dbo.MSP_VIEW_PROJ_TASKS_STD.TaskPercentComplete >= 100 THEN dbo.MSP_VIEW_PROJ_TASKS_STD.TaskBaselineWork END AS EV, dbo.MSP_VIEW_PROJ_TASKS_STD.TaskBaselineWork AS PV, CASE WHEN dbo.MSP_VIEW_PROJ_TASKS_STD.TaskPercentComplete >= 100 THEN dbo.MSP_VIEW_PROJ_TASKS_STD.TaskActualWorkProtected ELSE 0 END AS AC, dbo.MSP_VIEW_PROJ_TASKS_STD.TaskPercentComplete AS [% Complete], DATEADD(day, 0, DATEDIFF(day, 0, dbo.MSP_VIEW_PROJ_TASKS_STD.TaskFinish)) AS Finish FROM dbo.MSP_VIEW_PROJ_TASKS_STD INNER JOIN dbo.MSP_VIEW_PROJ_TASKS_ENT ON dbo.MSP_VIEW_PROJ_TASKS_STD.WPROJ_ID = dbo.MSP_VIEW_PROJ_TASKS_ENT.WPROJ_ID AND dbo.MSP_VIEW_PROJ_TASKS_STD.TaskUniqueID = dbo.MSP_VIEW_PROJ_TASKS_ENT.ENT_TaskUniqueID INNER JOIN dbo.MSP_WEB_PROJECTS ON dbo.MSP_VIEW_PROJ_TASKS_STD.WPROJ_ID = dbo.MSP_WEB_PROJECTS.WPROJ_ID INNER JOIN dbo.MSP_VIEW_PROJ_PROJECTS_ENT ON dbo.MSP_VIEW_PROJ_TASKS_STD.WPROJ_ID = dbo.MSP_VIEW_PROJ_PROJECTS_ENT.WPROJ_ID WHERE (dbo.MSP_VIEW_PROJ_TASKS_STD.TaskSummary = 0) ) AS derivedtbl_1 ON TP.WPROJ_ID = derivedtbl_1.ProjectID CROSS JOIN @result AS WeekEnds WHERE (DATEADD(day, 0, DATEDIFF(day, 0, [Baseline Finish])) >= @Start AND DATEADD(day, 0, DATEDIFF(day, 0, [Baseline Finish])) <= @End) OR (DATEADD(day, 0, DATEDIFF(day, 0, [Actual Finish])) >= @Start AND DATEADD(day, 0, DATEDIFF(day, 0, [Actual Finish])) <= @End) OR (DATEADD(day, 0, DATEDIFF(day, 0, Finish)) >= @Start AND DATEADD(day, 0, DATEDIFF(day, 0, Finish)) <= @End) ) AS WithWeekEnds WHERE (BAC IS NOT NULL) OR (EV IS NOT NULL) OR (PV IS NOT NULL) OR (AC IS NOT NULL) OR (FV IS NOT NULL) OR (FC IS NOT NULL) GROUP BY WeekEndDate, ProjectID ) AS EV_View INNER JOIN dbo.MSP_WEB_PROJECTS ON EV_View.ProjectID = dbo.MSP_WEB_PROJECTS.WPROJ_ID INNER JOIN dbo.MSP_VIEW_PROJ_PROJECTS_STD ON EV_View.ProjectID = dbo.MSP_VIEW_PROJ_PROJECTS_STD.WPROJ_ID INNER JOIN dbo.MSP_VIEW_PROJ_PROJECTS_ENT ON EV_View.ProjectID = dbo.MSP_VIEW_PROJ_PROJECTS_ENT.WPROJ_ID ORDER BY dbo.MSP_WEB_PROJECTS.PROJ_NAME, EV_View.[Week Ending]
Hi I have written a query for viewing the results of an on-line survey. I have three tables involved in this query: answers, answerpossibilities and users. So I use a few joins and made this query: ALTER PROCEDURE dbo.GeefAntwoordenMeerkeuze ( @question_id int ) AS SET NOCOUNT ON; SELECT answerpossibilities.answerpossibility_content AS[Answerpossiblity], COUNT(answers.answers_id) AS [Times chosen] FROM answers right OUTER JOIN answerpossibilities ON answers.answerpossibility_id = answerpossibilities.answerpossibility_id left join users on answers.user_id = users.user_id WHERE ((answerpossibilities.question_id = @question_id AND nswerpossibilities.answerpossibility_content!='-- choose answer --')) GROUP BY nswerpossibilities.answerpossibility_content ORDER BY [Times chosen] desc The above query works fine. The data returned by this query is shown in a gridview. When an answerpossibilty was never chosen it shows 0 as times chosen. So that's fine. But the problem is, only answers of users who completed the survey should be shown. In the users table there's a field user_completed. So the query should check whether this field is 1 (true). ALTER PROCEDURE dbo.GeefAntwoordenMeerkeuze ( @question_id int ) AS SET NOCOUNT ON; SELECT answerpossibilities.answerpossibility_content AS[Answerpossiblity], COUNT(answers.answers_id) AS [Times chosen] FROM answers right OUTER JOIN answerpossibilities ON answers.answerpossibility_id = answerpossibilities.answerpossibility_id left join users on answers.user_id = users.user_id WHERE ((answerpossibilities.question_id = @question_id AND nswerpossibilities.answerpossibility_content!='-- choose answer --') and users.user_completed = 1) GROUP BY nswerpossibilities.answerpossibility_content ORDER BY [Times chosen] desc Using this query only answers of users who completed the survey are shown but answer possibilities that were never chosen are no longer shown with 0 as times chosen. The gridview simply doesn't show them anymore. Thanks for helping me!
Something went wrong by posting this message I guess, all blank lines were gone.. maybe because I used Safari on my iMac
I have a package which loads data from a flat file (csv) to 4 tables in a database. Now, the load is incremental.
I want to clear the data of all 4 tables(in the database) before loading the data from flat file everytime.How can i do this? Iam using 4 Oledb Destinations, 1 multicast, 1 source component to do this. Also can it happen like a transaction? because if it deletes the existing data and couldnt load new data there will be a problem!.how to avoid this?
Hi, I have the following query:UPDATE lista SET audio='3847f5e9-4ef7-42d7-9e57-e5cbad9131b1.jpg' WHERE id='13';If the id already exists it'll modify the row correctly as expected. But if the id doesn't exist at the table I want the row to be inserted anyway, but this is not happening. The same query works well in mysql + php. The table has an identity increment of 1 for the primary key also. Any ideas?Thanks in advance.
ID Order Date ..... 1 Bla 2015-01-13 12:00:45.2713558 . 2 Mio 2015-01-13 12:05:45.2713558 . 3 Tala 2015-01-13 14:00:45.2713558 . 4 Bla 2015-01-13 15:00:45.2713558 . 5 Mio 2015-01-13 20:00:45.2713558 . 6 Bla 2015-01-15 17:00:45.2713558 . 7 Bla 2015-01-15 19:00:45.2713558 .
[code]...
I tryed several things with CASE, IF, EXISTS,... but I had no success.
Hi all: for example: testDataContext db=new testDataContext();var res=db.tables.single(p=>p.columnName=="hi"); if there is a record in the database, it works well, but if there isn't , it will throw an exception, then , How could I know the record exists or not ? I don't think exception is a resonable way. and in my opinion, there should be--------even must be ------a resonable way , to evaluate the query result to a bool variable, then program could judge the bool variable like : if(bExist) show("yes, I find it");else show("sorry, the record doesn't exist in the database"); I can't imagine I got the bool variable via exception... thanks to all..
In my BM_Maps subform, there is a combo box called called Borders. I want the borders to be filtered based on product series, so if the user picks "City Guide", they only get city guide borders, so I have added a table called Product_Series_X_Border which has the product series and border id.
I have a query which joins two tables, and I've ordered it by the border name, which works fine, until I add a where clause for the product series from the other table. I have attached a screengrab which I hope works. I have tried joining other tables but it still doesn't work. Is there any other way I can order by border name with the where clause? I've tested with individual product series as the where clause with the same result.
I am trying to write a SQL query looking for a specific file in a specific directory. If the File does not exist, then I want it to display.
here is the Code that I have for it that shows that the file exists... how do I make it show that the file doesn't exist?... what am I doing wrong?
Thanks for all your help in advance!
**********************************
SELECT TOP 100 PERCENT SYS.Netbios_Name0, SYS.User_Name0, SF.FileName, SF.FileVersion, SF.FileSize, SF.FileModifiedDate, SF.FilePath, SYS.Operating_System_Name_and0 FROM dbo.v_GS_SoftwareFile SF INNER JOIN dbo.v_R_System SYS ON SYS.ResourceID = SF.ResourceID WHERE (SF.FileName = 'UdaterUI.exe') AND (SF.FilePath = 'C:Program FilesMcAfeeCommon Framework') OR (SF.FileName = 'UdaterUI.exe') AND (SF.FilePath = 'c:Program FilesNetwork AssociatesCommon Framework') ORDER BY SF.FileVersion, SYS.Netbios_Name0
I was asked to add an additional column to an existing query. I'm using Microsoft Query with a MS SQL 2000 server, and don't have much knowledge of SQL in general. Here's the existing query:
SELECT A.COMPANYCODE, A.INVOICENUMBER, A.LINENUMBER, A.SONUMBER, A.CUSTOMERCODE, A.SHIPPERNUMBER, A.INVOICEDATE, A.ITEMCODE, A.QUANTITYINVOICED, A.UNITPRICE AS 'InvPrice', A.QUANTITYINVOICED * A.UNITPRICE AS 'ExtInvPrice', INVENTORY.UNITPRICE AS 'StdPrice', INVENTORY.STANDARDCOST, A.QUANTITYINVOICED * INVENTORY.STANDARDCOST AS 'ExtCost', (A.QUANTITYINVOICED * A.UNITPRICE) - (A.QUANTITYINVOICED * INVENTORY.STANDARDCOST) AS 'GM$', (INVENTORY.UNITPRICE - A.UNITPRICE) * -1 AS 'PriceListDiff' FROM ABW.DBO.SALESANALYSISHISTORY A, ABW.DBO.INVENTORY INVENTORY WHERE INVENTORY.COMPANYCODE = A.COMPANYCODE AND INVENTORY.ITEMCODE = A.ITEMCODE AND ((A.COMPANYCODE = 'csp') AND (A.QUANTITYINVOICED <>$ 0) AND (A.INVOICEDATE BETWEEN '03/1/08' AND '03/31/08')) ORDER BY A.INVOICEDATE, A.ITEMCODE
They want a column added to the current query where if A.Unitprice is greater than or equal to Inventory.UnitPrice then populate the column with A.QuantityInvoiced*A.UnitPrice. I posted on another forum, and the advice I got was to add this:
SELECT NewColumn = CASE WHEN A.Unitprice >= Inventory.UnitPrice THEN A.QuantityInvoiced * A.Unitprice ELSE 'null' END, FROM ABW.DBO.SALESANALYSISHISTORY A, ABW.DBO.INVENTORY INVENTORY
I'm not sure how to integrate it to the current query, so I've tried running it by itself, and I get the error: Didn't expect 'A' after the SELECT column list.
Any help would be greatly appreciated to modify the current query to display the new column.
Hi,I need to output result of my query to txt file. So I'm using -oparameter, for example:osql.exe -s (local) -d database1 -U sa -P sa -i 'c:\queryFile.sql' -o'c:\output.txt'But it clears existing output.txt file first and then outputs theresult, while I need to append the result of my query without clearingexisting file content. Is it possible ?
Using SQL Server Management Studio Express 2005 for the first time and have a question. After creating a query using the Design Query, can the same query be visually modified using the Design Query again?
Seems whenever the Design Query is used again with an open query, the tool opens empty ready for a new query instead of the open query.
I went to look at the connection string previously entered for a dataset created in a new report, and am not seeing anything intuitive for bringing up the associated datasource dialog box that was used to enter name, type and connection string. I'm also noticing nothing intuitive for deleting an existing dataset. How do you do these two very simple things in an existing project? I dont see the dataset in solution explorer, I see it only in the text box on the data tab and in a limited kind of way on the dataset view where the columns show and maint is allowed mostly on the columns only. I tried hilighting the dataset here and hitting the delete key to no avail.
I have a simple data flow inside of a ForEach container. The ForEach container uses an ADO recordset. Suddenly it seem it isn't running. The first two SQL tasks go green, and then the for each container itself turns green, but nothing happens to the data flow task inside of the loop, but I get the message saying it is done.
I have a simple Sybase ODBC source, which when I preview the data in the source object itself it previews fine. When I try to preview the data in the destination it is blank. I have linked up the source and destination objects, and I haven't disabled anything. The mappings in the source and destination look ok--I"m moving the data here as is, nothing fancy.
Not much to go on I know, but how do I troubleshoot this? The execution results seem all o.k.-it runs the query against Sybase and then just says it was finished. But no data was moved.
I'm working in a ASP.NET 2.0 application with a SQL Server 2000 database on the back end. I have a strongly typed dataset in the application that calls a stored procedure for the select. I'm having trouble filling the dataset at runtime though. I am trying to use a character string query because I setup different columns to be pulled from a table each time and in a different order so my T-SQL looks like this: set @FullQuery = 'Select ' + @FieldsinOrder + ' from tblExample'exec (@FullQuery) This works fine in query analyzer. The results return and display correctly. However, when I run the application, the dataset does not get filled. It is like the results do not output to the application. If I change the query to be a normal select it works. For example: select * from tblEmample That works fine. What is it about a select query setup as a character string and then executed that ASP.NET doesn't like?
I have the following query which works fine when it's executed as a single query. but when i union the result of this query with other queries, it returns a different set of data.
any one know why that might be the case??
select top 100 max(contact._id) "_id", max(old_trans.date) "callback_date", 7 "priority", max(old_trans.date) "recency", count(*) "frequency" --contact._id, contact.callback_date from topcat.class_contact contact inner join topcat.MMTRANS$ old_trans on contact.phone_num = old_trans.phone where contact.phone_num is not null and contact.status = 'New Contact' group by contact._id order by "recency" desc, "frequency" desc
i've included the union query here for completeness of the question
begin declare @current_date datetime set @current_date = GETDATE()
select top 100 _id, callback_date, priority, recency, frequency from ( ( select top 10 _id, callback_date, 10 priority, @current_date recency, 1 frequency --, DATEPART(hour, callback_date) "hour", DATEPART(minute, callback_date) "min" from topcat.class_contact where status ='callback' and (DATEPART(year, callback_date) <= DATEPART(year, @current_date)) and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @current_date)) -- all call backs within that hour will be returned and (DATEPART(hour, callback_date) <= DATEPART(hour, @current_date)) and (DATEPART(hour, callback_date) <> 0) order by callback_date asc --order by priority desc, DATEPART(hour, callback_date) asc, DATEPART(minute, callback_date) asc, callback_date asc ) union ( select top 10 _id, callback_date, 9 priority, @current_date recency, 1 frequency from topcat.class_contact where status = 'callback' and callback_date is not null and (DATEPART(year, callback_date) <= DATEPART(year, @current_date)) and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @current_date)) and (DATEPART(hour, callback_date) <= DATEPART(hour, @current_date)) and (DATEPART(hour, callback_date) = 0) order by callback_date asc ) union ( select top 10 _id, callback_date, 8 priority, @current_date recency, 1 frequency from topcat.class_contact where status = 'No Connect' and callback_date is not null and (DATEPART(year, callback_date) <= DATEPART(year, @current_date)) and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @current_date)) and (DATEPART(hour, callback_date) <= DATEPART(hour, @current_date)) order by callback_date asc ) union ( select top 100 max(contact._id) "_id", max(old_trans.date) "callback_date", 7 "priority", max(old_trans.date) "recency", count(*) "frequency" --contact._id, contact.callback_date from topcat.class_contact contact inner join topcat.MMTRANS$ old_trans on contact.phone_num = old_trans.phone where contact.phone_num is not null and contact.status = 'New Contact' group by contact._id order by "recency" desc, "frequency" desc ) ) contact_queue order by priority desc, recency desc, callback_date asc, frequency desc
I made a page for a baseball league a couple of years ago using PHP and MySQL, and decided this winter to switch it to ASP.Net and SQL Server Express that comes with VWD 2005 Express Edition.
Anyways, I ran into a problem when trying to convert the query I made with MySQL into SQL Server.
My old code was something like this:
SELECT homeScore, visitorScore, ( homeScore > visitorScore ) AS homeWins, ( visitorScore > homeScore ) AS visitorWins FROM Schedule
This worked fine in MySQL, and returned the home team's score, visiting team's score, and then returned a 1 in homeWins if the home team won. Now, when I try running this query in SQL Server Express, I get an error saying "Invalid column name 'visitorScore > homeScore' ".
Can anyone please help me with what I should be doing differently so this works in SQL Server Express?! Thanks in advance!!!
Problem is that if the [Receiving] table doesn't have a match then no records are return. I want all matches from the [Orders Subtable] and any matches from the [Receiving] Table. If no [Receiving] table matches then I still want all matches from the [Orders Subtable]. Attached is the query.
Note: The query has to run in Access 2000 and I will be coding it in VB.
FROM (Orders LEFT JOIN Receiving ON Orders.[Orders ID] = Receiving.[Orders ID]) INNER JOIN [Orders Subtable] ON Orders.[Orders ID] = [Orders Subtable].[Orders ID]
GROUP BY Orders.[Orders ID], [Orders Subtable].ID, [Orders Subtable].Quantity, Receiving.Quantity, Orders.[Project #], [Orders Subtable].On_Order, [Orders Subtable].[Component #], Receiving.[Component #]
HAVING (((Orders.[Project #])="Speed1aaaaa") AND (([Orders Subtable].On_Order)=True) AND (([Orders Subtable].[Component #])="R02101A") AND ((Receiving.[Component #])="R02101A"));
