How Much Can Sql 2005 Handle
Aug 23, 2007
I have a friend who is doing a voting application for one of his customers and they are concerned about the volume that sql server can handle. He's looking a single sql server 2005 with plenty of hd space and 4gb of memory. The app will look to see if you voted and then insert a record accordingly.
Are there any papers out there or apps that can show the amount a server can handle?
thanks.
View 8 Replies
ADVERTISEMENT
Mar 6, 2007
Are there any function in SQL Server 2005 which can help to calculate the total no. of days and months? Let's said if I provide 2 dates, 28-Feb-2001 and 01-Mar-2004, it can return 36 Months and 2 Days. The concept is like the function months_between in Oracle. Are there any function in SQL Server 2005 can achieve this?
View 3 Replies
View Related
Dec 23, 2005
It's to slow when I handle XML field in SQL 2005!
I find that it's slow when I handle XML field (maybe only stored 200kb date) in SQL 2005! Especially when I insert a sub tree to XML field !I have indexed the XML field.
Is it possible that the XML field is not ripe in SQL 2005 database?
View 4 Replies
View Related
Jul 5, 2007
I have a web application. In some instances, I have a need to send guid parameter as null while making a sql query to SQL 2005. My question is as how to handle this null guid on .net and sql side.
Thanks! in advance.
View 2 Replies
View Related
Aug 7, 2006
Can the SQL 2005 install upgrade from SQL 2000 handle multiple instances?
View 1 Replies
View Related
Nov 8, 2006
Hello,
I have a stored procedure that creates a temporary table, and then populates it using an INSERT and then a series of UPDATE statements. The procedure then returns the temporary table which will contain data in all of its columns.
When I call this procedure from SSRS 2005, the rowset returned contains data in only those columns that are populated by the INSERT statement. The columns that are set via the UPDATE statements are all empty. I read (in the Hitchhikers Guide to Reporting Services) that SSRS will only process the first rowset in a stored procedure that generates multiple rowsets. Is this true? Is this why SSRS does not retrieve data for the columns that are populated by the UPDATE statements?
Here is the stored procedure:
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- File: sp_GetProgramsWatchedByDateRange.sql
-- Desc: Returns EDP program and related channel (i.e., provider) information from the IPTV Data warehouse.
-- Note that some of that data used by this procedure are obtained from the RMS_EPG database
-- which is created by an application (loadEPG) that loads the EPG data from a GLF format XML file.
-- Auth: H Hunsaker
-- Date: 11/07/2006
-- Example invocation
-- EXEC dbo.sp_GetProgramsWatchedByDateRange ...
-- Arguments/Parameters:
-- Parameter Name Type Description
-- 3. StartDate datetime First date of reporting period
-- 4. EndDate datetime Last date of reporting period
-- TerseMode bit Return all columns? (1 = no, 0 = yes)
-- 5. AsXML bit Resultset format (0 = standard, 1 = XML)
-- 6. Debug bit Debug mode (0 = off, 1 = on). Currently disabled
IF OBJECT_ID (N'dbo.sp_GetProgramsWatchedByDateRange') IS NOT NULL
DROP PROCEDURE dbo.sp_GetProgramsWatchedByDateRange
GO
CREATE PROCEDURE dbo.sp_GetProgramsWatchedByDateRange
@StartDate datetime = NULL,
@EndDate datetime = NULL,
@TerseMode bit = 0,
@AsXML bit = 0,
@Debug bit = 0
AS
-- Notes: Much of the program content (roles, flags, etc.) that we want is not stored in the IPTV data warehouse.
-- So I am going to the RMS_EPG database to obtain that information.
-- We will have to ensure that the 2 databases are generated at the same or a matching time
-- in order to to ensure that all programID values in the data warehouse can be located in the RMS_EPG database.
-- Debug code for testing
-- DECLARE @StartDate datetime
-- DECLARE @EndDate datetime
-- DECLARE @TerseMode bit
--SET @StartDate = NULL
--SET @EndDate = NULL
--SET @TerseMode = 1
SET NOCOUNT ON
CREATE TABLE #programWatched
(
--IPTV device ID
tdeviceId uniqueidentifier NULL,
taccountId uniqueidentifier NULL,
-- Basic program information
tprogram int NULL, -- programID from EPG XML, needed to access program data in the RMS_EPG db.
tprogramId uniqueidentifier NULL, -- programID generated by IPTV
tprogramTitle varchar(150) NULL,
tprogramEpisodeTitle varchar(100) NULL,
tprogramDescription varchar(500) NULL,
toriginDateTime datetime NULL,
tduration bigint NULL,
tprogramType nvarchar(100) NULL,
tchannelCallName nvarchar(20) NULL,
--Rating
programMPAARating varchar(50) NULL,
programVCHIPRating varchar(50) NULL,
programMPAARatingVal smallint NULL,
programVChipRatingVal smallint NULL,
-- Categories
programGenre varchar(50) NULL,
programCategory1 varchar(50) NULL,
programCategory2 varchar(50) NULL,
programCategory3 varchar(50) NULL,
programCategory4 varchar(50) NULL,
-- Roles
programActor1FirstName varchar(50) NULL,
programActor1LastName varchar(50) NULL,
programActor1 varchar(100) NULL,
programActor2FirstName varchar(50) NULL,
programActor2LastName varchar(50) NULL,
programActor2 varchar(100) NULL,
programActor3FirstName varchar(50) NULL,
programActor3LastName varchar(50) NULL,
programActor3 varchar(100) NULL,
programActor4FirstName varchar(50) NULL,
programActor4LastName varchar(50) NULL,
programActor4 varchar(100) NULL,
programActor5FirstName varchar(50) NULL,
programActor5LastName varchar(50) NULL,
programActor5 varchar(100) NULL,
programActor6FirstName varchar(50) NULL,
programActor6LastName varchar(50) NULL,
programActor6 varchar(100) NULL,
programActor7FirstName varchar(50) NULL,
programActor7LastName varchar(50) NULL,
programActor7 varchar(100) NULL,
programActor8FirstName varchar(50) NULL,
programActor8LastName varchar(50) NULL,
programActor8 varchar(100) NULL,
programDirectorFirstName varchar(50) NULL,
programDirectorLastName varchar(50) NULL,
programDirector varchar(100) NULL,
programWriterFirstName varchar(50) NULL,
programWriterLastName varchar(50) NULL,
programWriter varchar(100) NULL,
programProducerFirstName varchar(50) NULL,
programProducerLastName varchar(50) NULL,
programProducer varchar(100) NULL,
-- Flags
ClosedCaption bit NULL,
InStereo bit NULL,
Repeats bit NULL,
New bit NULL,
Live bit NULL,
Taped bit NULL,
Subtitled bit NULL,
SAP bit NULL,
ThreeD bit NULL,
Letterbox bit NULL,
HDTV bit NULL,
Dolby bit NULL,
DVS bit NULL,
FlagOrdinalValue smallint NULL,
-- Channel
tchannelId int NULL,
callLetters varchar(20) NULL,
displayName varchar(50) NULL,
type varchar(50) NULL,
networkAffiliation varchar(50) NULL
)
-- I store the program watching data in a temp table because
-- data from the VIL and the Sandbox that were used to test this procedure were either incomplete or invalid.
-- Use of a temp table with a series of updates allow me more control over the result set.
IF @StartDate IS NOT NULL AND @EndDate IS NOT NULL
INSERT INTO #programWatched (
tdeviceId,
tprogramId,
--tprogramTitle,
--tprogramEpisodeTitle,
toriginDateTime,
tduration,
--tprogramType,
--tchannelCallName,
ClosedCaption,
InStereo,
Repeats,
New,
Live,
Taped,
Subtitled,
SAP,
ThreeD,
Letterbox,
HDTV,
Dolby,
DVS
)
SELECT pw.DeviceID,
pw.programID,
--epg.program,
--epg.programTitle,
--epg.programEpisodeTitle,
pw.originTime AS 'When Watched',
pw.Duration AS 'Duration Seconds',
--epg.programType,
--epg.channelCallName,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 -- program flag values default to zero, as we do not want NULL values.
FROM DW_EventClientProgramWatched pw
WHERE programID IS NOT NULL AND programID != '00000000-0000-0000-0000-000000000000' -- These values should not occur, but they did in the test system
AND originTime BETWEEN @StartDate AND @EndDate
ELSE
INSERT INTO #programWatched (
tdeviceId,
tprogramId,
--tprogramTitle,
--tprogramEpisodeTitle,
toriginDateTime,
tduration,
--tprogramType,
--tchannelCallName,
ClosedCaption,
InStereo,
Repeats,
New,
Live,
Taped,
Subtitled,
SAP,
ThreeD,
Letterbox,
HDTV,
Dolby,
DVS
)
SELECT pw.DeviceID,
pw.programID,
--epg.program,
--epg.programTitle,
--epg.programEpisodeTitle,
pw.originTime AS 'When Watched',
pw.Duration AS 'Duration Seconds',
--epg.programType,
--epg.channelCallName,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 -- program flag values default to zero, as we do not want NULL values.
FROM DW_EventClientProgramWatched pw
WHERE programID IS NOT NULL AND programID != '00000000-0000-0000-0000-000000000000' -- These values should not occur, but they did in the test system
-- AccountId/SubscriberId
UPDATE #programWatched
SET taccountId = (SELECT accountId
FROM DW_BRDB_bm_device d
WHERE d.deviceId = tdeviceId)
-- program (this is the integer program ID stored in the EPG XML, not to be confused with the IPTV programId)
-- a program can occur on multiple channels, so we filter channels where scheduleTime <= originTime <= scheculeTime + durationSecs
UPDATE #programWatched
SET tchannelCallName = (SELECT TOP 1 channelCallName
FROM DW_EPG EPG
WHERE tprogramId = EPG.programId AND toriginDateTime BETWEEN scheduleTime AND DATEADD(s, epg.durationSecs, epg.scheduleTime))
UPDATE #programWatched
SET tprogram = (SELECT TOP 1 program FROM DW_EPG EPG WHERE tprogramId = EPG.programId AND tchannelCallName = channelCallName),
tprogramTitle = (SELECT TOP 1 programTitle FROM DW_EPG EPG WHERE tprogramId = EPG.programId AND tchannelCallName = channelCallName),
tprogramEpisodeTitle = (SELECT TOP 1 programEpisodeTitle FROM DW_EPG EPG WHERE tprogramId = EPG.programId AND tchannelCallName = channelCallName),
tprogramType = (SELECT TOP 1 programType FROM DW_EPG EPG WHERE tprogramId = EPG.programId AND tchannelCallName = channelCallName)
-- Rating (otained from programValues, can also be obtained from programFlags)
UPDATE #programWatched
SET programMPAARating = (SELECT TOP 1 programValue
FROM RMS_EPG..programValue pv
WHERE tprogram = pv.programID AND pv.programValueTypeId = 9)
UPDATE #programWatched
SET programMPAARatingVal = CASE programMPAARating
WHEN 'G' THEN 10
WHEN 'PG' THEN 25
WHEN 'PG-13' THEN 30
WHEN 'R' THEN 35
WHEN 'NC-17' THEN 50
WHEN 'NRAO' THEN 60
WHEN 'NR' THEN 0
ELSE 0
END
UPDATE #programWatched
SET programVChipRating = (SELECT TOP 1 programValue
FROM RMS_EPG..programValue pv
WHERE tprogram = pv.programID AND pv.programValueTypeId = 8)
UPDATE #programWatched
SET programVChipRatingVal = CASE programVChipRating
WHEN 'TV-Y' THEN 10
WHEN 'TV-Y7' THEN 20
WHEN 'TV-G' THEN 35
WHEN 'TV-PG' THEN 40
WHEN 'TV-14' THEN 45
WHEN 'TV-MA' THEN 60
ELSE 0
END
-- Genre
UPDATE #programWatched
SET programGenre = (SELECT TOP 1 programCategoryTypeValue
FROM RMS_EPG..programCategory pc
INNER JOIN RMS_EPG..programSubCategoryType psct ON psct.programSubCategoryTypeId = pc.programCategoryId
INNER JOIN RMS_EPG..programCategoryType pct ON pct.programCategoryTypeId = psct.programCategoryTypeId
WHERE tprogram = pc.programID)
-- Categories
UPDATE #programWatched
SET programCategory1 = (SELECT TOP 1 programSubCategoryTypeValue
FROM RMS_EPG..programCategory pc
INNER JOIN RMS_EPG..programSubCategoryType pct ON pct.programSubCategoryTypeId = pc.programCategoryId
WHERE tprogram = pc.programID)
UPDATE #programWatched
SET programCategory2 = (SELECT TOP 1 programSubCategoryTypeValue
FROM RMS_EPG..programCategory pc
INNER JOIN RMS_EPG..programSubCategoryType pct ON pct.programSubCategoryTypeId = pc.programCategoryId
WHERE tprogram = pc.programID AND programSubCategoryTypeValue NOT IN (programCategory1))
UPDATE #programWatched
SET programCategory3 = (SELECT TOP 1 programSubCategoryTypeValue
FROM RMS_EPG..programCategory pc
INNER JOIN RMS_EPG..programSubCategoryType pct ON pct.programSubCategoryTypeId = pc.programCategoryId
WHERE tprogram = pc.programID AND programSubCategoryTypeValue NOT IN (programCategory1, programCategory2))
UPDATE #programWatched
SET programCategory4 = (SELECT TOP 1 programSubCategoryTypeValue
FROM RMS_EPG..programCategory pc
INNER JOIN RMS_EPG..programSubCategoryType pct ON pct.programSubCategoryTypeId = pc.programCategoryId
WHERE tprogram = pc.programID AND programSubCategoryTypeValue NOT IN (programCategory1, programCategory2, programCategory3))
-- Roles
UPDATE #programWatched
SET programDirectorFirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 2)
UPDATE #programWatched
SET programDirectorLastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 2)
UPDATE #programWatched
SET programDirector = programDirectorLastName + ' , ' + programDirectorFirstName
UPDATE #programWatched
SET programWriterFirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 7)
UPDATE #programWatched
SET programWriterLastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 7)
UPDATE #programWatched
SET programWriter = programWriterLastName + ' , ' + programWriterFirstName
UPDATE #programWatched
SET programProducerFirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 6)
UPDATE #programWatched
SET programProducerLastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 6)
UPDATE #programWatched
SET programProducer = programProducerLastName + ' , ' + programProducerFirstName
UPDATE #programWatched
SET programActor1FirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1)
UPDATE #programWatched
SET programActor1LastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1)
UPDATE #programWatched
SET programActor1 = programActor1LastName + ' , ' + programActor1FirstName
UPDATE #programWatched
SET programActor2FirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName))
UPDATE #programWatched
SET programActor2LastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleLastName NOT IN (programActor1LastName) AND programRoleLastName NOT IN (programActor1LastName))
UPDATE #programWatched
SET programActor2 = programActor2LastName + ' , ' + programActor2FirstName
UPDATE #programWatched
SET programActor3FirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName))
UPDATE #programWatched
SET programActor3LastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleLastName NOT IN (programActor1LastName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleLastName NOT IN (programActor2LastName) AND programRoleLastName NOT IN (programActor2LastName))
UPDATE #programWatched
SET programActor3 = programActor3LastName + ' , ' + programActor3FirstName
UPDATE #programWatched
SET programActor4FirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName))
UPDATE #programWatched
SET programActor4LastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName))
UPDATE #programWatched
SET programActor4 = programActor4LastName + ' , ' + programActor4FirstName
UPDATE #programWatched
SET programActor5FirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)
AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName))
UPDATE #programWatched
SET programActor5LastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)
AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName))
UPDATE #programWatched
SET programActor5 = programActor5LastName + ' , ' + programActor5FirstName
UPDATE #programWatched
SET programActor6FirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)
AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName)
AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName))
UPDATE #programWatched
SET programActor6LastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)
AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName)
AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName))
UPDATE #programWatched
SET programActor6 = programActor6LastName + ' , ' + programActor6FirstName
UPDATE #programWatched
SET programActor7FirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)
AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName)
AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName)
AND programRoleFirstName NOT IN (programActor6FirstName) AND programRoleLastName NOT IN (programActor6LastName))
UPDATE #programWatched
SET programActor7LastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)
AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName)
AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName)
AND programRoleFirstName NOT IN (programActor6FirstName) AND programRoleLastName NOT IN (programActor6LastName))
UPDATE #programWatched
SET programActor7 = programActor7LastName + ' , ' + programActor7FirstName
UPDATE #programWatched
SET programActor8FirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)
AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName)
AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName)
AND programRoleFirstName NOT IN (programActor6FirstName) AND programRoleLastName NOT IN (programActor6LastName)
AND programRoleFirstName NOT IN (programActor7FirstName) AND programRoleLastName NOT IN (programActor7LastName))
UPDATE #programWatched
SET programActor8LastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)
AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName)
AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName)
AND programRoleFirstName NOT IN (programActor6FirstName) AND programRoleLastName NOT IN (programActor6LastName)
AND programRoleFirstName NOT IN (programActor7FirstName) AND programRoleLastName NOT IN (programActor7LastName))
UPDATE #programWatched
SET programActor8 = programActor8LastName + ' , ' + programActor8FirstName
-- Channel (provider) Call Letters, Display Name and Type
-- Is this correct? Should we get the channelId from the schedule table?
-- Is this efficient? View execution plan
UPDATE #programWatched
SET tchannelId = (SELECT TOP 1 c.channelId
FROM RMS_EPG..channel c
INNER JOIN RMS_EPG..schedule s on s.channelID = c.channelID
WHERE s.programId = tprogram)
UPDATE #programWatched
SET callLetters = (SELECT TOP 1 c.channelCallLetters
FROM RMS_EPG..channel c
INNER JOIN RMS_EPG..schedule s on s.channelID = c.channelID
WHERE s.programId = tprogram and s.channelId = tchannelId)
UPDATE #programWatched
SET displayName = (SELECT TOP 1 c.channelDisplayName
FROM RMS_EPG..channel c
JOIN RMS_EPG..schedule s on s.channelID = c.channelID
WHERE s.programId = tprogram and s.channelId = tchannelId)
UPDATE #programWatched
SET type = (SELECT TOP 1 c.channelType
FROM RMS_EPG..channel c
INNER JOIN RMS_EPG..schedule s on s.channelID = c.channelID
WHERE s.programId = tprogram and s.channelId = tchannelId)
UPDATE #programWatched
SET networkAffiliation = (SELECT TOP 1 c.channelNetworkAffiliation
FROM RMS_EPG..channel c
INNER JOIN RMS_EPG..schedule s on s.channelID = c.channelID
WHERE s.programId = tprogram and s.channelId = tchannelId)
IF @TerseMode = 0
SELECT *
FROM #programWatched
ORDER BY toriginDateTime
ELSE
-- Get only Genre, title, show date/time, rating, call letters
SELECT tDeviceId, tprogramTitle, tprogramEpisodeTitle, programGenre, toriginDateTime, programMPAARating, programVCHIPRating, tchannelCallName
FROM #programWatched
ORDER BY toriginDateTime
DROP TABLE #programWatched
SET NOCOUNT OFF
GO
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
I also tried a query that populates some of its columns via subqueries. The query works fine when executed by the SQL Sevrer Query Analyzer,
meaning that all columns contain values, but when executed from SSRS, the columns that are poulated by the subqueries are empty, and only the columns that are not set by subqueries contain values:
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT PW.DeviceID,
PW.originTime AS 'When Watched',
PW.programID,
PW.Duration AS 'Duration Seconds',
(SELECT TOP 1 programTitle FROM DW_EPG WHERE DW_EPG.programId = PW.programId AND PW.originTime BETWEEN DW_EPG.scheduleTime
AND DATEADD(second, durationSecs, DW_EPG.scheduleTime)) AS Title,
(SELECT TOP 1 program FROM DW_EPG WHERE DW_EPG.programId = PW.programId AND PW.originTime BETWEEN DW_EPG.scheduleTime AND
DATEADD(second, durationSecs, DW_EPG.scheduleTime)) As program,
(SELECT TOP 1 programCategoryTypeValue
FROM RMS_EPG..programCategory PC
INNER JOIN RMS_EPG..programSubCategoryType PSCT ON psct.programSubCategoryTypeId = PC.programCategoryId
INNER JOIN RMS_EPG..programCategoryType PCT ON PCT.programCategoryTypeId = PSCT.programCategoryTypeId
WHERE PC.programID = (SELECT TOP 1 program FROM DW_EPG WHERE DW_EPG.programId = PW.programId AND PW.originTime BETWEEN
DW_EPG.scheduleTime AND DATEADD(second, durationSecs, DW_EPG.scheduleTime))) AS Genre,
(SELECT TOP 1 programSubCategoryTypeValue
FROM RMS_EPG..programCategory PC
INNER JOIN RMS_EPG..programSubCategoryType PSCT ON psct.programSubCategoryTypeId = PC.programCategoryId
INNER JOIN RMS_EPG..programCategoryType PCT ON PCT.programCategoryTypeId = PSCT.programCategoryTypeId
WHERE PC.programID = (SELECT TOP 1 program FROM DW_EPG WHERE DW_EPG.programId = PW.programId AND PW.originTime
BETWEEN DW_EPG.scheduleTime AND DATEADD(second, durationSecs, DW_EPG.scheduleTime))) AS Category
FROM DW_EventClientProgramWatched PW
ORDER BY DeviceId, programId, originTime
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Any help is appreciated
View 3 Replies
View Related
Dec 3, 2007
Hi
I'm using service broker and keep getting errors in the log even though everythig is working as expected
SQL Server 2005
Two databases
Two end points - 1 in each database
Two stored procedures:
SP1 is activated when a message enters the sending queue. it insert a new row in a table
SP2 is activated when a response is sent from the receiving queue. it cleans up the sending queue.
I have a table with an update trigger
In that trigger, if the updted row meets a certain condition a dialogue is created and a message is sent to the sending queue.
I know that SP1 and SP2 are behaving properly because i get the expected result.
Sp1 is inserteding the expected data in the table
SP2 is cleaning up the sending queue.
In the Sql Server log however i'm getting errors on both of the stored procs.
error #1
The activated proc <SP 1 Name> running on queue Applications.dbo.ffreceiverQueue output the following: 'The conversation handle is missing. Specify a conversation handle.'
error #2
The activated proc <SP 2 Name> running on queue ADAPT_APP.dbo.ffsenderQueue output the following: 'The conversation handle is missing. Specify a conversation handle.'
I would appreceiate anybody's help into why i'm getting this. have i set up the stored procs in correctly?
i can provide code of the stored procs if that helps.
thanks.
View 10 Replies
View Related
Jan 18, 2008
We have implemented our service broker architecture using conversation handle reuse per MS/Remus's recommendations. We have all of the sudden started receiving the conversation handle not found errors in the sql log every hour or so (which makes perfect sense considering the dialog timer is set for 1 hour). My question is...is this expected behavior when you have employed conversation recycling? Should you expect to see these messages pop up every hour, but the logic in the queuing proc says to retry after deleting from your conversation handle table so the messages is enqueued as expected?
Second question...i think i know why we were not receiving these errors before and wanted to confirm this theory as well. In the queuing proc I was not initializing the variable @Counter to 0 so when it came down to the retry logic it could not add 1 to null so was never entering that part of the code...I am guessing with this set up it would actually output the error to the application calling the queueing proc and NOT into the SQL error logs...is this a correct assumption?
I have attached an example of one of the queuing procs below:
Code Block
DECLARE @conversationHandle UNIQUEIDENTIFIER,
@err int,
@counter int,
@DialogTimeOut int,
@Message nvarchar(max),
@SendType int,
@ConversationID uniqueidentifier
select @Counter = 0 -- THIS PART VERY IMPORTANT LOL :)
select @DialogTimeOut = Value
from dbo.tConfiguration with (nolock)
where keyvalue = 'ConversationEndpoints' and subvalue = 'DeleteAfterSec'
WHILE (1=1)
BEGIN
-- Lookup the current SPIDs handle
SELECT @conversationHandle = [handle] FROM tConversationSPID with (nolock)
WHERE spid = @@SPID and messagetype = 'TestQueueMsg';
IF @conversationHandle IS NULL
BEGIN
BEGIN DIALOG CONVERSATION @conversationHandle
FROM SERVICE [InitiatorQueue_SER]
TO SERVICE 'ReceiveTestQueue_SER'
ON CONTRACT [TestQueueMsg_CON]
WITH ENCRYPTION = OFF;
BEGIN CONVERSATION TIMER ( @conversationHandle )
TIMEOUT = @DialogTimeOut
-- insert the conversation in the association table
INSERT INTO tConversationSPID
([spid], MessageType,[handle])
VALUES
(@@SPID, 'TestQueueMsg', @conversationHandle);
SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE [TestQueueMsg] (@Message)
END
ELSE IF @conversationHandle IS NOT NULL
BEGIN
SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE [TestQueueMsg] (@Message)
END
SELECT @err = @@ERROR;
-- if succeeded, exit the loop now
IF (@err = 0)
BREAK;
SELECT @counter = @counter + 1;
IF @counter > 10
BEGIN
-- Refer to http://msdn2.microsoft.com/en-us/library/ms164086.aspx for severity levels
EXEC spLogMessageQueue 20002, 8, 'Failed to SEND on a conversation for more than 10 times. Error %i.'
BREAK;
END
-- We tried on the said conversation, but failed
-- remove the record from the association table, then
-- let the loop try again
DELETE FROM tConversationSPID
WHERE [spid] = @@SPID;
SELECT @conversationHandle = NULL;
END;
View 2 Replies
View Related
Mar 30, 2004
Do I have other option beside using IF..ELSE IF? TIF
-- GET INFORMATION OF THE JOB
DECLARE @JOBIDAS Char(10)
DECLARE @VRUSERVICEHRSAS Decimal(18,2)
DECLARE @VRUSERVICEMINAS Decimal(18,2)
DECLARE @BILLEDFLATAS Decimal(18,2)
DECLARE @BILLREGRATEAS Decimal(18,2)
DECLARE @MIN_HRSAS Decimal(18,2)
DECLARE @COUNT_GREATER_MINTinyInt
DECLARE @COUNT_LESS_MINTinyInt
SET @VRUSERVICEMIN = 46
SET @BILLEDFLAT = 0
-- PROCESS ONLY RECORDS WHERE THERE IS NO FLAT FEE
IF @BILLEDFLAT = 0
BEGIN
IF @VRUSERVICEMIN BETWEEN 0 AND 15
BEGIN
SET @VRUSERVICEMIN = .25
END
ELSE IF @VRUSERVICEMIN = 15
BEGIN
SET @VRUSERVICEMIN = .25
END
ELSE IF @VRUSERVICEMIN BETWEEN 15 AND 30
BEGIN
SET @VRUSERVICEMIN = .5
END
ELSE IF @VRUSERVICEMIN = 30
BEGIN
SET @VRUSERVICEMIN = .5
END
ELSE IF @VRUSERVICEMIN BETWEEN 30 AND 45
BEGIN
SET @VRUSERVICEMIN = .75
END
ELSE IF @VRUSERVICEMIN = 45
BEGIN
SET @VRUSERVICEMIN = .75
END
ELSE IF @VRUSERVICEMIN > 45
BEGIN
SET @VRUSERVICEMIN = 1
END
END
PRINT @VRUSERVICEMIN
View 6 Replies
View Related
Apr 1, 2007
Is it possible to catch and error and then keep the process going in a stored procedure?
So if an update encounters a primary key violation on a row, is it possible to skip that row and keep the process going?
View 4 Replies
View Related
Oct 21, 2007
Hi! I have some try .. catch block trying to insert some data into database. During its action duplicate key row insert error could raise, for example. The question is how could I know distinguish it from other sql errors? Object ex (Catch ex As Exception) has only message property '{"Cannot insert duplicate key row in object 'dbo.Group_Courses' with unique index 'IX_Group_Courses'.The statement has been terminated."}' and type System.Data.SqlClient.SqlException. Knowing the type of error is not enough, because there are different SqlExceptions. Even the message is not unique for this error, because now i deal with 'dbo.Group_Courses' and then it could be other table. Is there something that unique identifies each error? For example error code. If it exists, where could I get it?
Thanks in advance!
View 2 Replies
View Related
Mar 13, 2004
I test my the now function and it is getting the right date. When I try to send that to the sql database I have it turns it into 1/1/1900. Does anyone know why this is happening, I have tried everything Here is my code:
sql = "Insert into tblguestbook(date, name, city, state, email, Url, Comments)Values ('"
sql = sql & Request.Form(Now) & "','"
sql = sql & Request.Form("nametxt") & "','"
sql = sql & Request.Form("citytxt") & "','"
sql = sql & Request.Form("statetxt") & "','"
sql = sql & Request.Form("emailtxt") & "','"
sql = sql & Request.Form("urltxt") & "','"
sql = sql & Request.Form("commentstxt") & "');"
View 1 Replies
View Related
Dec 31, 2004
Hi,
I have a table with one field set at nvarchar (4000)
This is sometimes not big enough and I get an error that the max row size has been reached.
How do people handle the error when the max row size is reached and gracefully inform the user?
Also, should I really be using Ntext instead, would this be better - is there a performance penalty?
Much obliged.
RG
View 1 Replies
View Related
Jan 13, 2006
Hi,
I would like to handle a sql error in t-sql and return a certain value in case error occurs. For example if I would like to add a record I want to return a certain identity value or maybe a status of transaction (0 for incomplete, 1 for succesfull trans).
If error occurs in sql I cannot return any values back to asp.net because of What I am doing at the moment is catching an error in asp.net and then displaying an error message. Is there a way to return only a return value to asp.net and somehow handle the error in t-sql?
Thanks
View 1 Replies
View Related
Feb 28, 2001
I don't know what's wrong with SQL2000 setup, the problem is:
whenever I execute a query with a date/time such as 02/02/200,
SQL give me an error message saying that the date is "Out of Range".
Any ideas, thanks in advance.
View 4 Replies
View Related
May 20, 2002
I'm having trouble with something and I was hoping that you could point me in the right direction.
Here's the scenario:
I have a VB application that clients use to add records to a SQK 2K DB. The info they have added that day is shown in a grid. They have the ability to edit items in the grid, and then update those changes to the database. The problem is that sometimes they change the values to something they shouldn't. To combat this I've started experimenting with check constraints. In query analyzer I test the constraint by trying to update an entry to an 'illegal' value. When I do this, I get an error saying: "Server: Msg 547, Level 16, State 1, Line 1" and the change is not made. What I'd like to do is to give the user a dialog box notifying him of the error. Is there a way to have a sub-routine or stored procedure be triggered when a message of this type is generated?
My specs are: W2K pro clients, SQL2K on an NT4 sp6a server. Application is written in VB6.
Any help is greatly appreciated.
thanks,
-scott
View 1 Replies
View Related
May 15, 2001
I have a select statement that works, but I know there has to be a better
way( I apologize for being TSQL brain dead today). Here is the statement
SELECT PATIENT_ACPT_STATUS, DISP_NOTES, RECORD_ID, modified_by, last_name, ddate
FROM PATIENT_MEDICATION_DISPERSAL_
Where (ddate = convert(char(10),getdate(),101) and
(MODIFIED_BY = CURRENT_USER) and
rec_status = 1) or
(ddate = convert(char(10),getdate(),101) and
(PATIENT_ACPT_STATUS = 1)) OR
(ddate = convert(char(10),getdate(),101) and
(MODIFIED_BY = 'open'))
View 1 Replies
View Related
Jul 9, 2006
Hi All,
We have got a new HP server and SQL is preinstalled, But when we are trying to start SQL, it gives an error "The handle is invalid".
We tried to reinstall the SQL, but , being a server t doesn't allow to do so
Please assist.
regards,
Jatin
View 1 Replies
View Related
Sep 10, 2006
I have a need when a Update, Insert or Delete is done to a record in DB "A", it will send the appropriate UID to a different table in different DB "B".
My first thought was to have a trigger on the table in DB "A" simply call a stored procedure on DB "B" and do the UID.
However - my question is what is the best approach and what's the best way to establish the connection to DB "B" for the UID from within DB "A"? We can't use linked servers - DNSLESS string would be the preferred connect way. Not sure how to execute it within a trigger.
Is even using a Trigger to Stored Proc the best way?
What about Transaction Replication - which I've never attempted - is that a better way?
Just looking for some guidance here so I don't needlessly burn time down a path that isn't recommended or simply won't work.
Thanks as always for your input,
Peter
View 5 Replies
View Related
May 4, 2004
Hi,
I build a local cube from a relation database. In the database there are 1:n relations.
Is there a way to handle 1:n relations?
For example:
I have a table LOGGEDFLAW and a table LOGGEDREASON with a 1:n relation between them. We create a select statement of these tables and as an result we get duplicate records of LOGGEDFLAW each time more than 1 record of LOGGEDREASON are associated to 1 record of LOGGEDFLAW - this is the standard result I get with an relational JOIN operation. Now I want to count the LOGGEDFLAWs without the duplicates generated by the 1:n relationship.
Best regards,
Thorsten
View 2 Replies
View Related
May 7, 2008
Hi All,
Please help me out how to implement the locking in below scenario
Req -
There are two tables Table1 & Table2
If I will insert in table1 then related data fields will be auto updated in table2 , similarly based on the data in table2 table1 data needs to be updated.
Now the sync of table1 & table2 is working fine.
My prob is we are handling the updation/insertion from the UI screens . Two separate screen for each table. When we have multiple user accessing the screens say - User1 updates table1 and User2 updates table2 then we need to implement the locking so that at one time one screen will allow updation in the table1 and hence table2.
The other screen shouldnt allow updation in table2 and hence in table1.
This is very common locking functionality ...but am not getting any way to implement it , Please advise.
Srain.
View 3 Replies
View Related
Jul 23, 2005
I already asked this question; however, I am giving all the detailsnow:We get large files(millions of records) and we need to load it into ourtables using import export wizard. Some of the fields in the file canbe Null and so we are forced to create table with fields that allowNulls with default ''. However when we insert data into these tablesit puts Null in those fields even though we have a default '' (I do notthink we have any work around for that; do we?)Finally we need to go through each field and update it to '' if it is aNull and that takes LOT OF TIME.If (select count (*) from <tablename> where <columname> is Null) >0BeginUpdate <tablename>set <columnName> = ''where <columnName> is NullendPlease let me know if there are any work arounds for this crisis ?Thank you very much in advance!
View 1 Replies
View Related
Jul 20, 2005
I need to know if there is a better way to construct this SQL statement.(Error handling is omitted)MS SQL Server 2000Insert into FSSUTmpSelect a.acct_no, a.ac_nm, a.ac_type, 10, -1,-1 * sum(CHARINDEX(convert(char(4), b.post_yr), @post_yr) * CHARINDEX('-',CONVERT(char(2), b.post_prd - @post_prd - 1)) * b.prd_trn_amt),-1 * sum(CHARINDEX(convert(char(4), b.post_yr), @post_yr) * CHARINDEX('0',CONVERT(char(1), b.post_prd)) * b.prd_trn_amt)FROM GLAccounts a, GLBalances bWHERE b.cmpny_cd = a.cmpny_cdAND b.acct_no = a.acct_noAND a.cmpny_cd = @cmpny_cdAND ac_ctrl_type between '200' and '219'Group by a.acct_no, a.ac_nm, a.ac_typeThe part Im wondering about is the 2 sum sections.The GLBalances table has following important fields:Post_yr -- the posting yearPost_prd the posting periodPrd_trn_amt The beginning balances if the period is 0, or the nettransactions for periods 1 through 12.The first sum gives the current balance as of the period @post_prd by addingall of the periods from 0 to @post-prdThe second sum is just the beginning balance.It is doing a conditional sum by using CHARINDEX to be 0 if the recordshould not be added and 1 if it should.There is a problem as it stands when you are looking for the balances whenthe @post_prd is 9 or greater because b.post_prd - @post_prd 1 willbe 10 or smaller. Then the CONVERT(char(2)
.. is an error, so CHARINDEXis 0 when it needs to be 1.I can fix that by using SIGN and it will work fine. What I what to know, isthere a better way to populate the table, where one of the values is aconditional sum?This is a STORED PROCEDURE from a commercial product, so I cant changeanything else other than the STORED PROCEDURE.
View 1 Replies
View Related
Jul 20, 2005
Hi,What exactly does the above (subject) error mean? I'm getting it from anadp file when used by a few people at the same time (each user has thefile in their own filespace though). Access is through windowsauthentication and it only seemed to occur during an update of aspecific table.. The problem is it didn't happen to everyone and Ican't recreate it at all on my own, so am wondering if it was somethingto do with the level of traffic to/from the server at that specific time.Any clues?Cheers,Chris
View 4 Replies
View Related
Jun 21, 2006
Does anyone knows of a tool that will help me manage my queries? I have 100's of them and all scattered. On my PC at home, some in my laptop, some of them at work, some in memory sticks. Then, when I need them I can't find the one I'm looking for so I end up writing the query again.
Any ideas?
View 4 Replies
View Related
Feb 19, 2008
Hello
I am developing a smart device application using Visual Studio 2005 and SQL Server Compact Edition.
Since we cannot write stored procedures, functions, views what is the best way to write/read to/from the database tables other than writing inline sql.
Any ideas and suggestions regarding this would be helpful.
Thanks
View 1 Replies
View Related
Apr 29, 2008
Here is the basic situation.
(1) I have 2 packages that run independently of eachother.
(2) Both are scheduled in SQLServer Agent Jobs as separate jobs.
(3) Job A is scheduled to run every 12 hours and Job B is scheduled to run every 10 minutes.
(4) However, I want to prevent Job B from running if Job A happens to be running.
(5) It is unknown exactly how long Job A will take to finish so I can't schedule Job B around it.
The way I wanted to approach this situation is as follows.
Within Job A's package, create a "marker" file when the package starts and delete it when the package finishes. So the existence of this marker file will tell Job B's package if it should run or not.
The concept is simple, but I'm not sure how to implement this.
For example, to create the marker file, I would use a File System Task, but I don't see an option in there to "create file". (However, I do see an option for "delete file".) Also, what task would I use in Job B's package to check if the marker file exists.
Lastly, If you have better approach, I would like to hear about it.
Thank you.
View 11 Replies
View Related
Oct 16, 2006
My way:
add column (boolean) to speicify whether is null or not in drived column component, i feel that's a little difficult
any better ideas? thanks
View 6 Replies
View Related
Nov 11, 2006
Using asp.net 2.0 and visual studio 2005. The question is regarding the following ER diagram: I've made Firstname, lastname, buildingID and RoomNum all required fields. I've got a modified GridView that displays all of the table Faculty columns. It's been modified so the BuildingID and DepID are resolved to their actual field names and displayed in a DropDownList. In the dropdown list I used for inserting (a seperate DetailsView control), I manually inserted an item into the Department dropdownlist which had the text "-- Select a Department --" with a value of -1. MS SQL didn't like that -1 value so I wrote the following code to fix it: protected void dsFaculty_Inserting(object sender, SqlDataSourceCommandEventArgs e)
{
if (e.Command.Parameters["@DeptID"].Value.ToString() == "-1")
{
e.Command.Parameters["@DeptID"].Value = null;
}
} That means of course DeptID is null, which is ok. The problem arises is when I try to edit that row in the GridView. I get the an error 'ddlDepartment' has a SelectedValue which is invalid because it does not exist in the list of items.Parameter name: valueIdeally, I'd like to make the dropdown list in the GridView show "-- None --" for the DeptID if it comes across a null value. I already tried playing around with the Command.Parameters in the dsFaculty_Selected function, but it didn't work. Ideas?
View 1 Replies
View Related
Mar 2, 2007
I'm trying to learn how to handle a payment schedule for small loan payments. I have the following structure:
tblClient, ClientID
tblLoan, LoanID, ClientID
tblPayments, PayID, PayDate, PayAmt, LoanID
tblLoanPaymentSchedule, ScheduleID, LoanID, PayDueDate, PayDueAmt, ReminderPrintDate, ReminderSentDate, PaymentRecDate
I have a page that batches (holds) all the payments in a work table and I can post them to the correct loan record but I'm just not sure how I would ask a stored procedure to mark the tblLoanPaymentSchedule with the PaymentRecDate for each payment posted. The schedule is pregenerated so that a payment due reminder can be sent out.
I appreciate any suggestions,
View 1 Replies
View Related
Oct 31, 2007
I have a webform that lists all items (codes) on the left and selected items (codes) on the right. A user selects an item on the left and clicks a button to move it to the right side. An update changes the bit from 0 to 1. This uses the bit column in the table to determine what is listed on the left (0) or right (1) sides.
Then I can filter in my stored procedure on the bit column WHERE (dbo.tblCodes.CodeSelect = 1)
My problem with this is that if two or more users are doing this process on different sessions, they can trip over each others selections.
I'm hoping someone has a suggestion on how I might avoid the users having this problem.
View 3 Replies
View Related
Mar 13, 2008
Hi, I am using transaction that inserts records on two tables TableA and TableB. TableA primary key is used as foreign key in TableB. now problem is that how can i get the primary key of tableA new inserted row so that i can use it as foreign key in TableB.What i am doing that i got a collectionA that contains records of TableA. When new row inserted in TableA how can i update my Collection A without committing the transaction. The main purpose to update CollectionA is only to get primary key of new inserted row. thanx
View 9 Replies
View Related
Dec 8, 2004
Hi!
My problem/"goal to achieve" is the following..
Users can insert orders in a db-table. The date on which they've been created is stored.
I want the server to insert a new record in another table (accounts) for every order which is older than 2 weeks.
How should I do that?
With a custom action which checks all orders (a customer must press some button every day).
Or a static "update-class" with a kind of timer which does the action every 24 hours?
Of course an automatic solution will be the best! Another question is: how can i ensure that an instance of this "update-class" will never die?
Thanks so far...
View 2 Replies
View Related