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
I am a beginner with regard to SQL Server, having previously used Access 2000 databases in my programs.
Recently I installed VB 2005 Express and SQL Server 2005 Express on both my desktop computer and my laptop and have been working at migrating one of my earlier programs, written in VB6 /Access 2000, to VB Express 2005.net/SQL Server 2005.net.
Whilst my desktop version works well, I found that, for some unknown reason, SQLEXPRESS refuses to start automatically on my laptop, despite the fact that it is set that way. (It will start if I open the Configuration Manager and start it from there.) Since the configuration settings on my laptop are the same as those that work on my desktop, I cannot understand why the laptop version doesn't work.
My custom data component is an error handler that processes data and then calls
ComponentMetaData.FireError in ProcessInput() to send the error data to the event handler per row. Everything works fine during debug/release mode. However when I run it using "Start without Debug" no errors are thrown. My event handler still catches stock component errors, so something must be wrong in this custom component. Am I suppose to set a property or something?
Got following error in windows app event log when tried to start SSIS after applying sql2k5 sp1, event id is 260:
Microsoft SSIS Service failed to start. Error: Method 'GetVersionInfo' in type 'Microsoft.SqlServer.Dts.Server.DtsServer' from assembly 'MsDtsSrvr, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' does not have an implementation.
i had installed Sqlserver on drive E: before but i changed the drive letter to D: yesterday and now i can't start my sql server. how can i fix this problem?
I've exported surface area configuration settings from a server and tried importing them onto another server. The remote connection settings are imported but the SQLBrowser service remains Disabled. Am I doing something wrong?
The command line command I ran (in the C:Program FilesMicrosoft SQL Server90Shared directory) is "sac.exe in sacconfig.xml".
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!
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.
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 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,
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.
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?
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'.
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
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]
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?
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.
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
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?
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?
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
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.
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
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?
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.
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
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
