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?
View 1 Replies
Hello everybody,
I'm developing a report using the following structure :
declare @sql as nvarchar(4000)
declare @where as nvarchar(2000)
set @sql = 'select ....'
If <conditional1>
begin
set @where = 'some where'
end
If <conditional2>
begin
set @where = 'some where'
end
set @sql = @sql + @where
exec(@sql)
I run it in query analyser and works fine, but when I try to run in Reporting Services, Visual studio stops responding and the cpu reaches 100 %.
I realize that when I cut off the if clauses, then it works at Reporting services.
Does anybody know what is happening?
Why the query works in query analyser and doesn't work in Reporting Service ?
Thanks,
MaurÃcio
Hi Guys,
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.
Any idea what might be going on here?
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
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.
View 1 Replies View Relatedhow do i query a table and if it doesn't have a value for a field return something anyway?
e.g.
(table 1)
id title
--------
1 a
2 b
3 c
4 (null)
e.g. if null return 'not assigned'
?
Hi!
Do you know why this query is ok on SQL 2005 and not on SQL ce ??
Code Snippet
DELETE ProfilesGroups
FROM ProfilesGroups pg
INNER JOIN Groups g
ON tpGroupID = g.gId
WHERE pg.tpProfileID = '7df60fae-a026-4a0b-878a-0dd7e5308b09'
AND g.gProfileID = '8a6859ce-9f99-4aaf-9ed6-1af66cd15894'
Thx for help ;-).
PlaTyPuS
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..
SQL Server 2000 Enterprise
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.
Ideas please.
SQL Server 2000 Enterprise
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.
Ideas please.
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.
Good morning all,
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?
Thanks in advance,
Chris
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?
hi all
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
end
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.
SELECT Orders.[Orders ID],
[Orders Subtable].ID,
[Orders Subtable].Quantity,
Receiving.Quantity,
Receiving.[Component #]
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"));
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????
SELECT favorites.FID, favorites.filename, favorites.username, files.status, files.private, files.views, files.title FROM favorites INNER JOIN files ON favorites.filename = files.filename WHERE (favorites.username = @username) AND (files.status IS NULL) AND (files.private = @private)@private is manually set to 'no'@username is set to profile.usernamewhen I run the above query in microsoft sql server mgmt studio express on the database, it presents all the information i'm asking for very easily. however when i try to implement this query on a aspx .net2.0 page with teh sqldatasource and a gridview, no data is displayed.anyone know what my problem is?
View 1 Replies View RelatedHello. I'm having troubles with a query that (should) return all therecords between two dates. The date field is a datetime type. The db isSQL Server 2000. When I try thisSELECT RESERVES.RES_ID, PAYMENTS_RECEIVED.PYR_ID,PAYMENTS_RECEIVED.PYR_VALUE, PAYMENTS_RECEIVED.PYR_DATE,CUSTOMERS.CUS_NAMEFROM RESERVES LEFT OUTER JOINPAYMENTS_RECEIVED ON RESERVES.RES_ID =PAYMENTS_RECEIVED.RES_ID LEFT OUTER JOINCUSTOMERS ON RESERVES.CUS_ID = CUSTOMERS.CUS_IDWHERE (PAYMENTS_RECEIVED.PYR_DATE >= '2006-03-20 00:00:00') AND(PAYMENTS_RECEIVED.PYR_DATE < '2006-03-27 00:00:00')on a "query builder" in visual studio, I get the results that I want.But when I use exactly the same query on an asp 3 vbscript script, Iget no results (an empty selection).I've done everything imaginable. I wrote the date as iso, ansi, britishformat using convert(,103) (that's how users will enter the dates),i've used cast('20060327' as datetime), etc. But I can't still get itto work. Other querys from the asp pages work ok. Any ideas?thanks a lot in advance
View 1 Replies View RelatedI am trying to execute an SSIS package from an MS Access 2003 database that imports a table from the Access database into a target table in SQL 2005. I saved the package in SQL 2005 and tested it out. If I run it from the Management Studio Console with Run->Execute ... everything works just fine. However, if I try to run it using the following line command "Exec master.dbo.xp_cmdshell 'DTExec /SER DATAFORCE /DTS SQL2005TestPackage /CHECKPOINTING OFF /REPORTING V'" the execution will always fail when the Access database is open (shared mode). The connection manager looks like this: "Data Source=E:Test.mdb;Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Jet OLEDB:Global Bulk Transactions=1". The error is listed below:
Code: 0xC0202009
Source: NewPackage Connection manager "SourceConnectionOLEDB"
Description: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Could not use ''; file already in use.".
What am I doing wrong?
I am trying to load all the MDX queries that run on a Analysis Server instance into a database for further analysis. A SQL Profiler is setup which captures the MDX queries, and when I am loading the Profiler info to database, some of the queries are not coming up in full length.The TextData field doestn't show full MDX query. When loading to the database, the field is next data type. Is there any workaround to get the complete MDX query?Â
View 2 Replies View RelatedHi,
I'm using MSSQL 2005 JDBC driver version 1.2 with MSSQL Server 2005. Getting parameter's metadata doesn't seem to be working properly there. PreparedStatement#getParameterMetaData() works fine if PreparedStatement is created for some SELECT but fails for any INSERT statements. The same code works fine with other DBs (I checked with HSQLdb and PostgreSQL). See sample below
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String connectionUrl = "jdbcqlserver://mssql2005\mssql2005;DatabaseName=alexdtms;user=xxx;password=xxx";
// I don't care about non-closed statements, it's just sample code
Connection con = DriverManager.getConnection(connectionUrl);
con.createStatement().executeUpdate("CREATE Table Table1 (RECORD_ID varchar, TgtQty float)");
PreparedStatement ps = con.prepareStatement("INSERT INTO Table1 VALUES ( ?,? )");
ParameterMetaData pmd = ps.getParameterMetaData();// <-- !!!! Exception is thrown here
for (int i = 1, count = pmd.getParameterCount(); i <= count; i++)
{
System.err.println(">>>>AD_TEMP " + pmd.getParameterType(i) + " " + pmd.getParameterTypeName(i) + " " + pmd.getParameterMode(i));
}
Thanks,
Alexander Dolgin
Environment:Server1 (Local)OS Windows 2000 ServerSQL Server 2000Server2 (Remote)OS Windows 2003 ServerSQL Server 2000(Both with most recent service packs)Using Enterprise Manager, we have set up the Link Server (LINK_A) inthe Local Server 1 to connect to Server 2.The SQL we need to run is the following:INSERT INTO table1(column1,column2)SELECT A.column1, A.column2FROM LINK_A.catalog_name.dbo.table2 AS AWHERE A.column1 xxxx;When we run this from the Query Analyzer, it completes with no problemsin a few seconds.Our problem:When we add the DTS Package as the ActiveX Script (VB Script) to theLocal Package, it times out at "obj_Conn.Execute str_Sql"Dim Sql, obj_ConnSet obj_Conn = CreateObject("ADODB.Connection")obj_Conn.Open XXXXobj_Conn.BeginTransstr_Sql = "INSERT INTO table1("str_Sql = str_Sql & "column1"str_Sql = str_Sql & ", column2"str_Sql = str_Sql & ")"str_Sql = str_Sql & " SELECT Ａ.column1"str_Sql = str_Sql & ", A.column2"str_Sql = str_Sql & " FROM LINK_A.catalog_name.dbo.table2 AS A"str_Sql = str_Sql & " WHERE A.column1 0"str_Sql = str_Sql & ";"obj_Conn.Execute str_Sql----------------------------------------------------------When we make a Stored Procedure and run the following SQL, it freezes.INSERT INTO table1(column1,column2)SELECT A.column1, A.column2FROM LINK_A.catalog_name.dbo.table2 AS AWHERE A.column1 xxxxWe've also tried the following with the same results;INSERT INTO table1(column1,column2)SELECT A.column1, A.column2FROM [LINK_A].[catalog_name].[dbo].[table2] AS AWHERE A.column1 xxxxThe same thing happens when we try to run the "SELECT" by itself.SELECT TOP 1 @test=A.column1FROM LINK_A.catalog_name.dbo.table2 AS AWHERE A.column1 xxxxORDER BY A.column1What is going wrong here, and how do we need to change this so that itruns without timing out or freezing?
View 2 Replies View RelatedI have a a Group By query which is working fine aggregating records by city. Â Now I have a requirement to focus on one city and then group the other cities to 'Other'. Â Here is the query which works:
Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars'Â
From [Output-MarketAnalysis]
Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active'
Group by [City]
Here is the result:
St. Louis 1000
Kansas City 800
Columbia 700
Jefferson City 650
Joplin 300
When I add this Case When statement to roll up the city information it changes the name of the city to 'Other Missouri City' however it does not aggregate all Cities with the value 'Other Missouri City':
Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars'Â
From [Output-MarketAnalysis]
Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active'
Group by [City]
Here is the result:
St. Louis 1000
Other Missouri City 800
Other Missouri City 700
Other Missouri City 650
Other Missouri City 300
What I would like to see is a result like:
St. Louis 1000
Other Missouri City 2450
While using Aggregate Transformation to group one column,the rows of output sometimes larger than the rows returned by a T-SQL statement via SSMS.
For example,the output of the Aggregate Transformation may be 960216 ,but the
'Select Count(Orderid) From ... Group By ***' T-SQL Statement returns 96018*.
I'm sure the Group By of the Aggregate Transformation is right!
But ,when I set the "keyscale" property of the transformation,the results match!
In my opinion,the "keyscale" property will jsut affects the performance of the transformaiton,but not the result of the transformation.
Thanks for your advice.
Hi,
I have an application that uses following code:
Code Snippet
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Collections;
namespace TimeTracking.DB
{
public class sql
{
OleDbConnection conn;
//
//the constructor for this class, set the connectionstring
//
public sql()
{
DBConnectionstring ConnectToDB = new DBConnectionstring();
conn = ConnectToDB.MyConnection();
}
//
//
//
public void UpdateEntry(int ID, string Week, string Year, string Date, string Project, string Action, string Time, string Comment)
{
int m_ID = ID;
int m_Week = (Convert.ToInt32(Week));
int m_Year = (Convert.ToInt32(Year));
string m_Date = Date;
string m_Project = Project;
int m_ProjectID = new int();
string m_Action = Action;
int m_ActionID = new int();
Single m_Time = (Convert.ToSingle(Time));
string m_Comment = Comment;
//
//get the project ID from the database and store it in m_ProjectID
//
OleDbCommand SelectProjectID = new OleDbCommand("SELECT tblProject.ProjectID FROM tblProject"
+ " WHERE (((tblProject.Project) LIKE @Project))", conn);
SelectProjectID.Parameters.AddWithValue("@Project", m_Project);
try
{
//open the connection
conn.Open();
OleDbDataReader Dataset = SelectProjectID.ExecuteReader();
while (Dataset.Read())
{
m_ProjectID = (int)Dataset["ProjectID"];
}
Dataset.Close();
}
//Some usual exception handling
catch (OleDbException e)
{
throw (e);
}
//
//get the action ID from the database and store it in m_ActionID
//
OleDbCommand SelectActionID = new OleDbCommand("SELECT tblAction.ActionID FROM tblAction"
+ " WHERE (((tblAction.Action) LIKE @Action))", conn);
SelectActionID.Parameters.AddWithValue("@Action", m_Action);
try
{
OleDbDataReader Dataset = SelectActionID.ExecuteReader();
while (Dataset.Read())
{
m_ActionID = (int)Dataset["ActionID"];
}
Dataset.Close();
}
//Some usual exception handling
catch (OleDbException e)
{
throw (e);
}
//
//
//
OleDbCommand Query = new OleDbCommand("UPDATE [tblEntry] SET [tblEntry].[Weeknumber] = @Week,"
+ " [tblEntry].[Year] = @Year, [tblEntry].[Date] = @Date, [tblEntry].[Project] = @ProjectID, [tblEntry].[Action] = @ActionID,"
+ " [tblEntry].[Hours Spent] = @Time, [tblEntry].[Comments] = @Comment WHERE (([tblEntry].[ID]) = @ID)", conn);
Query.Parameters.AddWithValue("@ID", m_ID);
Query.Parameters.AddWithValue("@Week", m_Week);
Query.Parameters.AddWithValue("@Year", m_Year);
Query.Parameters.AddWithValue("@Date", m_Date);
Query.Parameters.AddWithValue("@ProjectID", m_ProjectID);
Query.Parameters.AddWithValue("@ActionID", m_ActionID);
Query.Parameters.AddWithValue("@Time", m_Time);
Query.Parameters.AddWithValue("@Comment", m_Comment);
try
{
Query.ExecuteNonQuery();
}
//Some usual exception handling
catch (OleDbException e)
{
throw (e);
}
finally
{
//close the connection
if (conn != null)
{
conn.Close();
}
}
}
}
}
Code Snippet
The update statement is not working in my application, no error in C# and no error in ms-access. When I paste the update query into the ms-access query tool and replace the parameter values (@....) with real values, is will update the record.
What am I overseeing here?
--Pascal
I setup a Sql Server Agent job to run daily every 15 minutes from 11:15PM to 10:45PM. I set this up about noon, and the job and schedule were both Enabled.
But the job would not run. I sat and watched it for some time but it just sat there. What do I have to do to get the job to run?
Sorry! I forgot that Recurring schedules do not work until the start-time is reached. If you change the start-time the schedule becomes inactive. So I created a second schedule to cover the hole!
Anybody see a reason why this list won't populate?
<asp:DropDownList ID="ddlState" DataSourceID="srcStates" DataTextField="StateName" DataValueField="StateName" runat="server"> <asp:ListItem>Select State</asp:ListItem></asp:DropDownList><asp:SqlDataSource ID="srcStates" runat="server" ConnectionString="<%$ ConnectionStrings:webConn %>" SelectCommand="sp_States" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:ControlParameter ControlID="ddlState" DefaultValue="Select State" Name="State" PropertyName="SelectedValue" Type="String" /> </SelectParameters></asp:SqlDataSource>
I'm having a tough time understanding my current problem and why I'm having it.
I'm using an ODBC db connection to connect to my sql server and trying
to insert data into a table I've created, but I keep getting an error
saying that the table doesn't exist. This is the first time I've
ever used ODBC and DSN to connect to a db so I don't konw if there are
qerks associated with this, but I've tried everything I can think
of. I've changed the name of the table, placed it in brackets,
placed "dbo" infront of the name, and still I get the same error about
the table not existing. My "Intial Catalog" in my connection
string is the correct db, I just can't seem to get it going.
Any suggestions?
Hi there,
Since I have upgraded to SQL 7 I'm exeriencing a problem with SQL Server Agent. I have a job scheduled to run every hour between 8:30AM and 5:30 PM during weekdays. Every Monday for the last several weeks I check the job status and it says it is enabled, runnable, and scheduled. Last job was run at 5:30PM on Friday, next run time is Monday 8:30AM. The only thing is that it is after 9:00AM. What gives? Then I have to run the job manually for the rest of the day. It usually starts working the next day. Anybody has similar problem?
Regards,
P.S. The agent service is running.
I'm pretty new to DTS, so forgive me if this is basic. I created a simple DTS package to run a query and export it to a text file. I can execute the package fine from my workstation through EM, but when I try to execute the job to run the package I get this error:
Error = -2147467259 (80004005) Error string: Error opening datafile: Access is denied.
I think that maybe SQL Agent doesn't have the right permissions to write to that network drive. What should the permissions be?
This is probably very simple, but I can't get passed this problem.
I have a report in MS Access that uses info generated by a query. One of the text fields in the query contains either the word 'Select' or the name of a course.
The report should display a space if the value is 'Select', or the actual value of the field in any other case. The field can never contain a null value.
I've used:
=IIf([optVoc1]="Select","",[optVoc1])
in the text box on the report, but this only returns #error regardless of the actual content of the field.
What am I doing wrong?
Regards,
BD