ForceExecutionResult Doesn't Do What Its Supposed To.
Jul 17, 2007
[Microsoft follow-up]
All,
According to BOL the ForceExecutionResult property can be used to imitate real-time failure (http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.dtsforcedexecresult.aspx).
However, I've just done a very simple test and this appears to not be the case. Here's the repro:
Start a new package
Drag on a Sequence Container
Set the sequence container property ForceExecutionResult='Failure'
When you run the package the sequence container will complete successfully. As far as I can see its not supposed to.
Any comments?
Thanks
Jamie
View 10 Replies
ADVERTISEMENT
Nov 19, 2007
Hi,
I have a package with For Loop container.
It looks like this:
FOR LOOP (until @IsCurrent < 1) [
[Execute Package Task 1] --> [Execute Package Task 2] --> [Execute SQL Task] --expression--> [Script Task]
]
So, in the [Execute SQL Task], it runs a stored procedure, which returns a 0 or 1 value, which is bound to the variable @IsCurrent. The variable then gets evaluated in the "expression." If @IsCurrent is 0, execution continues to the [Script Task]. That part works fine.
The problem is occurring in the [Script Task] portion. All the Script Task does is "sleep" for 3 minutes. Here is an excerpt of the code:
Dts.Variables("sleepCounter").Value = CInt(Dts.Variables("sleepCounter").Value) + 1
If CInt(Dts.Variables("sleepCounter").Value) = 20 Then
Dts.TaskResult = Dts.Results.Failure
Exit Sub
End If
So, as you can see, if "sleepCounter" = 20, it fails the task, which should then fail the package, right??
However, the package keeps running, even after sleepCounter has exceeded 20. The MaximumErrorCount on the For Loop container is set to 20 also. So after 20 failures, this package should fail, but it isn't!
Any ideas?
Thanks
View 9 Replies
View Related
Jun 29, 2004
here's my code...
DECLARE @Output FLOAT
SELECT @Output=(@Part/@Whole)*100
When I substitute my parameters like this:
SELECT @Output=(5/20)*100
The answer is always 0.0!
But if I do this:
SELECT @Output=(5*100)/20
I get the correct answer.
Why is this so?
What is the correct way in SQL to calculate percentage??!!!
View 3 Replies
View Related
May 29, 2006
I€™m importing a Flat file (delimited text) into a sql server database table and trying to trap any import errors in another output Flat file.
I create a Flat File Source task. After tweaking the task, the Source €śError Output€? tab shows all the input columns and all €śError€? and €śTruncation€? values are set to €śRedirect row€?
Next I add a €śSQL Server Destination€? task and connect the Source green arrow to it.
Finally I add a Flat File Destination task (error Output task), connect the Source red arrow to it, click €śOK€? in the €śConfigure Error Output€?, and finally add a connection manager to the Error output task. When I look at the Mappings tab of the Error Output file it shows only three available input columns: €śFlat File Error Output Column€?, ErrorCode, and ErrorColumn
I€™m not sure where they came from but that info is not very useful to me. I want to know which line the error occurred on as well as the bad column(s). If nothing else, I need to see at least the actual row that was bad. How can I get that information?
Barkingdog
View 8 Replies
View Related
Jul 18, 2007
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!
View 1 Replies
View Related
Feb 6, 2008
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>
View 6 Replies
View Related
May 11, 2005
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?
View 27 Replies
View Related
Aug 27, 2001
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
View Related
Sep 13, 1999
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.
View 1 Replies
View Related
Sep 15, 2005
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?
View 3 Replies
View Related
Nov 10, 2004
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
View 5 Replies
View Related
Aug 27, 2004
Hi all,
I create and schedule a SQL job to run every minute to update a table base on certain condition but it doesn't work. Job history says successful every time but the table doesn't get updated.
However if I move it to Query Analyzer and run it under dba, it will work. Thinking that it may have to do with the user the job run as, I then change run as user from self to dba. But still SQL job won't update my table.
Anything about user permission or security that I can check? Or it there any other possibility?
TIA
View 1 Replies
View Related
Apr 26, 2007
When I run the select its fine but I cannot delete..... i have done this many times and it has worked.... I cannot see the error what am i missing
select
eqnow.empnumber,
eqnow_names.empnumber,
eqnow_names.names
--delete
from
eqnow
inner join eqnow_names
on eqnow.empnumber = eqnow_names.empnumber
where
eqnow_names.names is null
i get this error
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'inner'.
View 3 Replies
View Related
May 12, 2004
Hi, all.
I tried to get result of sp.
Dim rst As Recordset
Set rst = New ADODB.Recordset
rst.Open sp, cnn
doesn't get result.
when i call rst.EOF, it thows error: Can do this since rst is closed...
I found it's problem of sp which is little complex.
But, still I think it should work!
My question is how can I get the returned value from following sp in VB?
--- return list of tables that needed to update
--- list is one string separated by '&' delimeter
--- @listOfUpdateTime: [TableName=UpdateTime]&[...] eg) tblDeptList=12/25/2004&tblHoliday=12/24/2004&...
CREATE procedure spGetListOfTableToDownLoad
@listOfUpdateTime varchar(500)
as
SET NOCOUNT ON
Declare @listOfTable varchar(300), @item varchar(300)
Declare @tbl varchar(50), @uptime datetime
Declare @list varchar(500)
Declare @sep varchar(1)
SET @list = ''
SET @sep = '&'
DECLARE cur CURSOR FAST_FORWARD FOR
SELECT * FROM fnSplit(@listOfUpdateTime, @sep)
OPEN cur
FETCH NEXT
FROM cur
INTO @item
Declare @re bit, @tp varchar(50)
WHILE @@FETCH_STATUS = 0
BEGIN
-- get tablename, update time
Declare cur2 CURSOR FAST_FORWARD FOR
SELECT * FROM fnSplit(@item, '=')
OPEN cur2
FETCH NEXT FROM cur2 INTO @tbl
Print @tbl
FETCH NEXT FROM cur2 INTO @tp
print 'tp:' + @tp
SET @uptime = CAST(@tp as datetime)
print @uptime
-- @re =1: true, 0: false
EXEC spIsUpdate @tbl, @uptime, @re output
IF @re = 1
SET @list = @list + @tbl + @sep
CLOSE cur2
DEALLOCATE cur2
FETCH NEXT
FROM cur
INTO @item
END
if LEN(@list) > 0
SET @list = LEFT(@list, Len(@list)-Len(@sep))
CLOSE cur
DEALLOCATE cur
SELECT @list as Result
SET NOCOUNT OFF
GO
__________________
--- PARAM:: @tbl: table name,
--- @uptime : update time (passed from local db) that will be compared on HQ table
--- return 1 if Max(UpdateTime) of @table > @uptime
--- otherwise return 0
CREATE Procedure spIsUpdate
@tbl varchar(50), @uptime datetime, @result bit output
as
BEGIN
Declare @bit bit
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
declare @uptimeHQ datetime
/* Build the SQL string once.*/
SET @SQLString = N'SELECT @tp = MAX(UpdateTime) FROM ' + @tbl
SET @ParmDefinition = N'@tp datetime OUTPUT'
EXECUTE sp_executesql @SQLString, @ParmDefinition
,@uptimeHQ OUTPUT
If @uptimeHQ > @uptime
SET @result = 1
ELSE
SET @result = 0
--RETURN @bit
END
GO
View 5 Replies
View Related
Dec 6, 2007
This problem is on SQL Server development box.
SS 2K standard edition
SP3
3071 MB
2 processors
Windows NT 5.0
SQL job doesn't start nor write on the view history. I have comfirm this by creating a new job with query and run it manually or schedule automatic run.
select * from sysdatabases
It points to database master and using the same account sql server agent uses it to start. This account has sys admi rights. I have check for blocking but thre is not.
Anyone has ever has a similar issue like this
http://www.sqlserverstudy.com
View 2 Replies
View Related
Oct 21, 2007
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 78
The configuration option 'user instances enabled' does not exist, or it may be an advanced option.
Valid configuration options are
View 1 Replies
View Related
May 3, 2008
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]
View 4 Replies
View Related
Apr 19, 2008
Hi to all, I'm building (and learn) an application with VB Express. In "edit dataset with designer" I've build this sql query:
SELECT tbl_soggetto.[ID Soggetto], tbl_soggetto_tipo.Tipo, tbl_soggetto.[Cognome/Denominazione], tbl_soggetto.Nome, tbl_soggetto.Indirizzo, tbl_soggetto.CAP, tbl_soggetto.CittĂ , tbl_soggetto.Provincia, tbl_soggetto.[Telefono 1], tbl_soggetto.[Telefono 2], tbl_soggetto.[Telefono 3], tbl_soggetto.[Telefono 4], tbl_soggetto.[eM@il 1], tbl_soggetto.[eM@il 2], tbl_soggetto.Note
FROM tbl_soggetto INNER JOIN tbl_soggetto_tipo ON tbl_soggetto.[ID Tipo] = tbl_soggetto_tipo.[ID Tipo]
WHERE (tbl_soggetto.[Cognome/Denominazione] LIKE '%' + @Testo + '%')
The LIKE doesn't work!
I call the query with Me.griglia.DataSource = Me.TA_tbl_soggetto_ricerca.Search_Cognome(Me.txt_trova.Text.Trim)
But with LIKE '%ABC%' work!
Me.griglia.DataSource = Me.TA_tbl_soggetto_ricerca.Search_Cognome()
Someone can help me? Thanks...
View 12 Replies
View Related
May 9, 2006
Hello,
I have a Job that runs a DTS package. This package in turns call several other packages. All individual packages run fines. When the main package is run manually it also runs fine. The jobs that calls the main package should be runnning for at least a couple of Hours maybe more and updtae several tables. It actually run nightly for only 20 minutes and doesn't update any tables. Yet it is in "successful"? Any thougts?
View 1 Replies
View Related
Dec 11, 2006
When I try to install the problem I get the following error.
The SQL Server service failed to start. For more information, see the SQL Server Books Online topics, "How to: View SQL Server 2005 Setup Log Files" and "Starting SQL Server Manually."
The log tells me nothing useful. I can't start the thing manually because after clicking cancel on the error message, the installer proceeds to roll back the installation.
How do I fix this problem?
View 3 Replies
View Related
Sep 28, 2006
Hi all, I am using SQLEXPRESS 2005 and can't figure out why my SQL query is not working.Select DISTINCT ads.clientid, ads.ad, ads.adid, ads.cost, ads.description, ads.bold, ads.info,
ads.contact, ads.coupon, ads.logo, ads.map, ads.webpage, ads.weburl, tclients.clientname,
tclients.clientphone, tclients.addNumber, tclients.addStreet, tclients.addsuite, tclients.addcity,
tclients.addstate, tclients.addzip from tclients, ads where tclients.clientid=ads.clientid
AND AdActive=1 ORDER BY COST DESC I expect only one row to be returned for each ads.clientid. However, every single record that matches the query is being returned, which results in multiple records with the same clientid.This happens if it is ads.clientid or tclients.clientid. Martin
View 5 Replies
View Related
Dec 13, 2006
I have formview and I have a SqlDatasource for it.I have few textboxes in the edit mode and bind it to the data columns or fields in the database.If the data for all those fields have content in it, then it will update just fine. However, if one of the text field is null or empty, the formview can't be updated When i try to update with empty data in one textboxData field allows null value, and type are varchar.I am suspecting it's throwing an internal exception somewhere. However, since all the operations are handled by the asp.net. I have no idea what's going on internally. Does anyone have an idea what's causing this error and how to fix it?
View 2 Replies
View Related
Apr 26, 2008
Hi all,
When I execute a stored procedure that returns a single value using a TableAdapter, I always get error message "Object reference not set to an instance of an object"
I followed all the steps in microsoft help center:http://msdn2.microsoft.com/en-us/library/37hwc7kt.aspx
but the problem still unsolved.
here are my simple stored procedure:create PROCEDURE dbo.StoredProcedure1
AS
RETURN 4
and here are the simple code in my asp.net webpage that should display the number "4": protected void Button1_Click(object sender, EventArgs e)
{
DataSet1TableAdapters.QueriesTableAdapter TA
= new DataSet1TableAdapters.QueriesTableAdapter();
int i = (int) TA.StoredProcedure1();
Response.Write(i.ToString());
}
so, please could any one help me solve this problem?
View 3 Replies
View Related
Jun 6, 2008
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
View 1 Replies
View Related
Jun 8, 2005
This is the autogenerated code from the SelectCommand of my DataAdapter, except the red text. This DataAdapter is used to fill a DataGrid. What I want to do, is to calculate the total memory (4 slots) / PC.This code makes the sum of all memory of all PC's together.I'm not sure if the group by clause is needed here ...Me.OleDbSelectCommand1.CommandText = "SELECT PC.ID, PC.Nummer, PC.Netwerknaam, Case_Type.Type AS Case_Type, Processor_T" & _"ype.Type AS Processor_Type, Processor_Snelheid.Snelheid AS Processor_Snelheid, " & _"(SELECT SUM(Memory) FROM Memory, PC, RAM WHERE RAM.PcID = PC.ID AND RAM.GrootteID = Memory.ID)" & _"AS Memory, OS.Naam AS OS, OS_SP.Nummer AS OS_SP, Gebru" & _"iker.Naam AS Gebruiker_Naam, Status.Status, PC.Tagged FROM (Status RIGHT OUTER J" & _"OIN ((((((((PC LEFT OUTER JOIN (RAM LEFT OUTER JOIN Geheugen ON RAM.GrootteID = " & _"Geheugen.ID) ON PC.ID = RAM.PcID) LEFT OUTER JOIN Case_Type ON PC.Case_TypeID = " & _"Case_Type.ID) LEFT OUTER JOIN OS_SP ON PC.OS_SpID = OS_SP.ID) LEFT OUTER JOIN Ge" & _"bruiker ON PC.GebruikersID = Gebruiker.ID) LEFT OUTER JOIN Processor_Snelheid ON" & _" PC.Processor_SnelheidID = Processor_Snelheid.ID) LEFT OUTER JOIN Processor_Type" & _" ON PC.Processor_TypeID = Processor_Type.ID) LEFT OUTER JOIN OS ON PC.OsID = OS." & _"ID) LEFT OUTER JOIN Switchbox_Details ON PC.ID = Switchbox_Details.PcID) ON Stat" & _"us.ID = PC.StatusID) GROUP BY PC.ID, PC.Nummer, PC.Netwerknaam, Case_Type.Type, " & _"Processor_Type.Type, Processor_Snelheid.Snelheid, OS.Naam, OS_" & _"SP.Nummer, Gebruiker.Naam, Status.Status, PC.Tagged"I would like to know how to calculate the total memory for each separate PC.Hope you can help me.
View 5 Replies
View Related
Mar 31, 2006
I have a stored procedure with a where clause like this:
WHERE
Q.EffectiveDate >= @FromEffectiveDate AND Q.EffectiveDate <= @ToEffectiveDate AND I.InsuredName LIKE '%' + isnull(@PreQuoteDesc,I.InsuredName) + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBProperty,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBGeneralLiability,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBInlandMarine,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBMotorTruckCargo,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBOwnersContractorsProtective,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBSpecialEvents,Q.LineOfBusinessDescription,'') + '%' AND rsu.FirstName LIKE '%' + isnull(@OwnerFirstName, rsu.FirstName) + '%' AND rsu.LastName LIKE '%' + isnull(@OwnerLastName, rsu.LastName) + '%' AND Q.quoteID = isnull(@quoteID,Q.QuoteID) AND Q.QuoteStatusID = isnull(@quoteStatusID, Q.QuoteStatusID) AND rsu.AspNetUserID = isnull(@ASPNetUserID, rsu.AspNetUserID)
-------------------------------------------------------------------
All is working well except for the line of business:
------------------------------------------------------------
AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBProperty,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBGeneralLiability,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBInlandMarine,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBMotorTruckCargo,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBOwnersContractorsProtective,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBSpecialEvents,Q.LineOfBusinessDescription,'') + '%'
---------------------------------------------------------------------------------
If the user checks just 'Property' results look like:
Property
Property
Property, General Liability
If the user checks just 'General Liability' the resultes look like:
Genral Liablility
General Liability
General Liability, Inland Marine
If the user checks both Property and General Liability all they get back is:
Property, General Liability
They should get back everything including just Property or just General Liability or both.
So I tried to change the ANDs to ORs and it doesn't work.
-----------------------------------------
AND ( isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBProperty,Q.LineOfBusinessDescription,'') + '%' OR isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBGeneralLiability,Q.LineOfBusinessDescription,'') + '%' OR isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBInlandMarine,Q.LineOfBusinessDescription,'') + '%' OR isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBMotorTruckCargo,Q.LineOfBusinessDescription,'') + '%' OR isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBOwnersContractorsProtective,Q.LineOfBusinessDescription,'') + '%' OR isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBSpecialEvents,Q.LineOfBusinessDescription,'') + '%'
)
I know this is incredibly hard to follow because its incredibly hard to write out.
Is there anyone smart out there who can figure this out?
Thanks
View 2 Replies
View Related
Jul 31, 2000
Hi Everyone,
I have a SQLServer 7.0 conversion db. Data file is 89 mg. The log has grown into 238 mg. Db and log are backuped nightly. Optimization and integrity jobs run weekly. The transaction log keeps growing! Truncate log on checkpoint is on and autoshrink is on.
What else can I do to truncate my log?
LT
View 3 Replies
View Related
Sep 20, 2000
I had a SQL Server falure. I rebiuld Master and tried to attach my database
with sp_attach_db? but get an error
Location: pageref.cpp:3931
Expression: rowLog.RowCount () == 1 || pPage->IsEmpty ()
SPID: 10
Process ID: 119
Connection Broken
View 1 Replies
View Related
Mar 1, 2001
I try to copy a DB from one server to another. On the target server an older version of the DB has been deleted and I now try to attach the new version using "sp_attach_db DBname, Filelocation", but I always get an error "Device Activation error. The physical file name 'D:mssql7dataAgency_log.ldf' may be incorrect"
"Database 'Agency' cannot be Created"
To me it seems that the database is looking for the log files (now deleted).
I've tried forcing a new log file I created using the same locations for the mdfs. I've tried using create a new database and replace the mdf file, but nothing works.
View 3 Replies
View Related
Mar 31, 2003
Hi,
I wanted to create a new trigger, but Enterprise Manager tells me about an "Incorrect syntax near @UpdatedByID, line 28". I double-checked everything, but it still does not work :mad: .
Any hints :confused: ?
TIA,
-Gernot
Here is the statement (line 28 is marked with ***):
CREATE TRIGGER TransferToABII ON [dbo].[CALGeneral]
FOR INSERT
AS
BEGIN TRANSACTION
BEGIN
DECLARE @Event varchar(255),
@BBaseUID int,
@StartDate smalldatetime,
@EndDate smalldatetime,
@Details varchar(255),
@AddressID int,
@ProjectID int,
@UpdatedByID int,
@ActID int,
@EventID int
SELECT @Event = Event,
@BBaseUID = BBaseUID,
@StartDate = StartDate,
@EndDate = EndDate,
@Details = Details,
@AddressID = AddressID,
@UpdatedByID = UpdatedBy,
@ProjectID = ProjectID
FROM INSERTED
BEGIN
EXEC BrainBase.dbo.BB_NEW_CREATE_NoteTask_Ret *** (@UpdatedByID,
@AddressID,
@ProjectID,
@BBaseUID,
@StartDate,
GetDate(),
@Event,
NULL,
NULL,
NULL,
NULL,
@Details text,
@ActID = @ActID OUTPUT,
@EventID = @EventID OUTPUT)
END
BEGIN
UPDATE CALGeneral SET ActID = @ActID WHERE ID = INSERTED.ID
END
END
IF @@ERROR <> 0
BEGIN
RAISERROR('Error occured',16,1)
ROLLBACK TRANSACTION
END
COMMIT TRANSACTION
View 4 Replies
View Related
Nov 11, 1999
I'm working with mssql 6.5
I have an primary key column with Identity property.
And at the moment server doesn't insert the proper value to this column.
Error message is following
Msg 2601, Level 14, State 3
Attempt to insert duplicate key row in object 'Spot' with unique index 'XPKSpot'
Command has been aborted.
The datatype of this column is int, and number of rows ~17000.
If I execute select @@identity it returns null.
View 4 Replies
View Related
Jun 4, 2004
Hi Guys,
Tempdb in one of our SQLSERVER is standing at 5GB. I try to shrink it to 2GB, using following commands.
DBCC shrinfile(2,2000)
DBCC SHRINKDATABASE (tempdb, 2000)
It doesn't want to shrink at all... I ran sp_spaceused it give following results
Reserved data indx_size unused.
808 KB 384 KB368 KB 56 KB
I dont understand why tempdb doesnt want to shrink...?? help is greatly appericated.. thanks..
View 4 Replies
View Related
Aug 29, 2001
hi i am running this program
and its giving some problem while execution.
When the program tries to create the table expt1..its says
Msg 2714, Level 16, State 1
There is already an object named 'expt1' in the database.
and if i try to drop the table expt1 it says
Msg 3701, Level 11, State 1
Cannot drop the table 'expt1', because it doesn't exist in the system catalogs.
How should I resolve this problem...?
==========================
Declare @cmdstring varchar(255)
Declare @filename varchar(32)
DECLARE @v datetime
SELECT @v = GetDate()
Create table expt1(GMSKEY char,CONTRACT char,ORIGINALCONTRACT char,STATEMENTID char,
STATEMENTDATE char ,partner char ,ADDRESSSEQ char,BOOKINGAMOUNT char,
BOOKINGCURRENCY char,TAXEDIND char,TAXTYPE char,
DUEPERIOD char,ACCOUNTNUMBER char,PRODMONTH char,PRODYEAR char,BOOKINGUNIT char,
BOOKINGQUANTITY char,STATEMENTSEQ char,COSTID char,DELIVERYMETHOD char)
SELECT
DETAILTRANS.GMSKEY,
DETAILTRANS.CONTRACT,
DETAILTRANS.ORIGINALCONTRACT,
DETAILTRANS.STATEMENTID,
convert(varchar,DETAILTRANS.STATEMENTDATE,106) statementdate,
DETAILTRANS.partner,
DETAILTRANS.ADDRESSSEQ,
DETAILTRANS.BOOKINGAMOUNT,
DETAILTRANS.BOOKINGCURRENCY,
DETAILTRANS.TAXEDIND,
DETAILTRANS.TAXTYPE,
TITLECT.DUEPERIOD,
DETAILTRANS.ACCOUNTNUMBER,
DETAILTRANS.PRODMONTH,
DETAILTRANS.PRODYEAR,
DETAILTRANS.BOOKINGUNIT,
DETAILTRANS.BOOKINGQUANTITY,
DETAILTRANS.STATEMENTSEQ,
DETAILTRANS.COSTID,
TITLECT.DELIVERYMETHOD,
'' RECEND
INTO expt1
FROM DETAILTRANS,TITLECT
where DETAILTRANS.TITLEID=TITLECT.TITLEID
AND DETAILTRANS.CONTRACT IS NOT NULL
AND DETAILTRANS.CONTRACT!='N/A'
AND DETAILTRANS.EXPREV='E'
AND DETAILTRANS.PRELIMORFINAL='F'
AND DETAILTRANS.postedDATE IS NULL
ORDER BY DETAILTRANS.GMSKEy
select @filename = 'XAPIALTMKT'+CONVERT(varchar, @v, 112)+ SUBSTRING(CONVERT(varchar, @v, 108), 1, 2)
+ SUBSTRING(CONVERT(varchar, @v, 108), 4, 2)+ SUBSTRING(CONVERT(varchar, @v, 108), 7, 2) + '.TXT'
Select @cmdstring = 'bcp GM_PROD..expt1 out d:est' + @filename + ' -c -t"|" -r -S -Usa -Psa'
exec master..xp_cmdshell @cmdstring
drop table expt1
===========
Thanks
Harish
View 1 Replies
View Related
