I am using SQL 2005 SP2. I have a table partitioned on date range. I am trying to SWITCH, MERGE and SPLIT partitions.
My SWITCH and MERGE work great. When the SPLIT query is executed, an error 9002 is thrown....
"The transaction log for database is full. To find out why space in log cannot be resued, see log_reuse_wait_desc column in sys.databases."
Below are more details...
- All SWITCH, MERGE and SPLIT are executed in one TRANSACTION.
- After SWITCH and MERGE, I execute a query set the partition scheme "NEXT USED [PRIMARY]".
- Finally i execute SPLIT statement.
I am trying to implement a sliding window on a table in SQL Server 2005 but i am having some problem. I have two tables, "Letture" and "LettureStorico". The first one receives data on a few seconds basis, some thousands of rows each day. The second is the historical record and should store all the records till midnigh of two days before, that is, if today is November 21st, LettureStorico stores rows till November 19th 23.59:59.997.
At some time during morning of each day i want to run a stored procedures that takes the records older than midnight of two days before in "Letture" and switch them as a partition in "LettureStorico"
Here's what i do:
/*----------------------------------------------------------*/ CREATE PARTITION FUNCTION [partizioneLive](datetime) AS RANGE LEFT FOR VALUES (N'2006-11-15 00:00:00')
CREATE PARTITION FUNCTION [partizioneStorico](datetime) AS RANGE LEFT FOR VALUES (N'2006-11-15 00:00:00')
CREATE PARTITION SCHEME [schemapartizioneLive] AS PARTITION [partizioneLive] ALL TO ([PRIMARY]) ALTER PARTITION SCHEME [schemapartizioneLive] NEXT USED [PRIMARY];/*(1)*/
CREATE PARTITION SCHEME [schemapartizioneStorico] AS PARTITION [partizioneStorico] ALL TO ([PRIMARY]) ALTER PARTITION SCHEME [schemapartizioneStorico] NEXT USED [PRIMARY]; /*(1)*/
CREATE TABLE [dbo].[Letture]( [IdLettura] [bigint] IDENTITY(1,1) NOT NULL, [IdTag] [int] NOT NULL, [IdGatewayBox] [int] NOT NULL, [IsEntrata] [bit] NOT NULL, [Data] [datetime] NOT NULL, [IsRettifica] [bit] NOT NULL, CONSTRAINT [PK_Letture] PRIMARY KEY CLUSTERED ( [Data], [IdLettura] ASC )WITH (IGNORE_DUP_KEY = OFF) ON schemaPartizioneLive(data) ) ON schemaPartizioneLive(data)
ALTER TABLE [dbo].[Letture] WITH CHECK ADD CONSTRAINT [CK_Letture] CHECK (([Data]>='20061115 00:00'))
CREATE TABLE [dbo].[LettureStorico]( [IdLettura] [bigint] IDENTITY(1,1) NOT NULL, [IdTag] [int] NOT NULL, [IdGatewayBox] [int] NOT NULL, [IsEntrata] [bit] NOT NULL, [Data] [datetime] NOT NULL, [IsRettifica] [bit] NOT NULL, CONSTRAINT [PK_LettureStorico] PRIMARY KEY CLUSTERED ( [Data], [IdLettura] ASC )WITH (IGNORE_DUP_KEY = OFF) ON schemaPartizioneStorico(data) ) ON schemaPartizioneStorico(data)
ALTER TABLE [dbo].[LettureStorico] WITH CHECK ADD CONSTRAINT [CK_LettureStorico] CHECK (([Data]<'20061115 00:00'))
Every morning i run a stored procedure that, after dropping the check constraints (i'll recreate the at the end), does the following:
/*-----------------------------------------------------------*/ SET @NewBoundary = dateadd(dd,-1, @dateOfToday)
--this new partition contains the rows i want to switch ALTER PARTITION FUNCTION PartizioneLive() SPLIT RANGE (@NewBoundary) --this new partition is empty ALTER PARTITION FUNCTION PartizioneStorico() SPLIT RANGE (@NewBoundary)
--this works fine, rows are moved ALTER TABLE Letture SWITCH PARTITION 2 TO LettureStorico PARTITION 2
--these two merges lead to two tables partitioned in two partitions each ALTER PARTITION FUNCTION PartizioneLive() MERGE RANGE (@OldBoundaryLive) ALTER PARTITION FUNCTION PartizioneStorico() MERGE RANGE (@OldBoundaryStorico)
Till now, everything is working as expected. Now, when i try to run the same Stored Procedure " a day later" (NewBoundary moved on 1 day) i receive, when i do the "ALTER PARTITION FUNCTION PartizioneLive() SPLIT RANGE (@NewBoundary)" i receive a 7707 error message: "Msg 7707, Level 16, State 1, Line 1 The associated partition function 'PartizioneLive' generates more partitions than there are file groups mentioned in the scheme 'schemapartizioneLive'."
How is this possible if i used the "ALL TO [PRIMARY]" and specified which file to use next as in (1) ? Why all this succeeds the first time (when i have 3 partitions) but not the second (again i have just three partitions, i checked) ?
Hi,I need to create a partition table but the column on which I need tocreate a partition may not have any logical ranges. So while creatingor defining partition function I can not use any range.likeCREATE PARTITION FUNCTION my_part_func (NUMERIC(7)) AS RANGE LEFT FORVALUES (1,100,1000);Is there any way to define partition function in SQL Server somethinglike Oracle HASH partitions where logical range is unkown?ThanksSameer
Is it possible to show the number of rows and the range for each partition in a table ?
This shows me the range but not the row count per partition
SELECT sprv.value AS [Value], sprv.boundary_id AS [ID] FROM sys.partition_functions AS spf INNER JOIN sys.partition_range_values sprv ON sprv.function_id=spf.function_id WHERE (spf.name=N'myDateRangePF') ORDER BY [ID] ASC
I'm trying to create a report and chart for a a manufacturing resource's activity for a given period (typically 30-90 days)
Jobs are created for the length of the run (e.g. 4 days). If the weekend is not worked and the above jobs starts on a Friday, the resource's activity needs to show 1 day running, 2 days down, 3 days running without the production scheduler having to make it two jobs. (A job can have multiple interruptions due to downtime). I have the jobs' schedules in one table and the downtimes in another (so think of the downtime as a calendar table--non working hours). Unusually, the end time is supplied with the downtime factored in.
So I need the query to create 3 datetime ranges for this job: Fri running, Sat,Sun down, Mon,Tues,Wed Running. Been going round in circles on this for a while. i'm sure there's an elegant way to do it: I just can't find it. I've found several similar post, but can't apply any to my case (or at least can;t get them to work)
Below is some sample date and expected results. I hope the explanation and example data is clear.
-- Create tables to work with / Source and Destination CREATE TABLE #Jobs ( ResourceID int ,JobNo VARCHAR(10) ,startdate SMALLDATETIME ,enddate SMALLDATETIME
[Code] ....
Below is some sample data
|--------------------------J1------------------------------------| running |----D1-----| |-------D2-------| down |--J1--|----D1-----|-------J1------|-------D2-------|-----J1-----| result
|-----------------J1-----------------------| running |----D1-------| down |-----------------J1-----------------------| |----D1-------| result
Let's say if the date is 01/01/2015 till 01/01/2016
I want split these dates monthly format wise and then use them in variable in cursors to loop
For an example Monthly date should be 01/01/2015 and 01/31/2015 and use these two values in 2 variables and make a loop until it never ends whole date range which is 01/01/2016
Msg 156, Level 15, State 1, Line 12 Incorrect syntax near the keyword 'over'.
What am I missing? The max() over statement looks just like the statement in the documentation.
select RegistrationId, OrderId, Sequence, Title, InformalFirstName, FirstName, MiddleName, Lastname, EntryDate, max(Sequence) over(partition by RegistrationId) as 'maxsequence' from registration where OrderId = '68379449583' and Year = '2008' and Active = 'Yes'
1 HIS_HTTP_LOG a partition table2 REL_HTTP_LOG not a partition table,the same structure of HIS_HTTP_LOGï¼›3 When HIS_HTTP_LOG doesn't exist any index the following executed succeed ALTER PARTITION SCHEME PS_HIS_HTTP_LOG NEXT USED [FG_03] ALTER PARTITION FUNCTION PF_HIS_HTTP_LOG() SPLIT RANGE ('20070331 23:59:59.997') ALTER TABLE TMP_HTTP_LOG SWITCH TO HIS_HTTP_LOG PARTITION 3 4 However when I added the index in HIS_HTTP_LOG and execute the step 3,It made error: a) CREATE INDEX IDX_HIS_HTTP_LOG_001 ON HIS_HTTP_LOG(USERID)ON PS_HIS_HTTP_LOG (STARTIME) b) ALTER PARTITION SCHEME PS_HIS_HTTP_LOG NEXT USED [FG_03] ALTER PARTITION FUNCTION PF_HIS_HTTP_LOG() SPLIT RANGE ('20070331 23:59:59.997') ALTER TABLE TMP_HTTP_LOG SWITCH TO HIS_HTTP_LOG PARTITION 3 ========================= Error messages================================================"ALTER TABLE SWITCH statement failed. There is no identical index in source table 'TMP_HTTP_LOG SWITCH ' for the index 'IDX_HIS_HTTP_LOG_001' in target table 'HIS_HTTP_LOG' ." When I added index in REL_HTTP_LOG ,it gave me the same error message Could you tell me how can I solve the problem !
I am trying to make a custom task. The custom task has one input, which i map to externalmetadata column in the task and one output.
When i run the task it fails with this error ( I am putting the whole SSIS message)
SSIS package "Package.dtsx" starting. Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning. Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning. Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning. Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning. Information: 0x402090DC at Data Flow Task, Flat File Destination [1855]: The processing of file "C:ole db eft data.txt" has started. Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning. Error: 0xC0047062 at Data Flow Task, Lib [2387]: System.ArgumentException: Value does not fall within the expected range. at Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSBuffer90.DirectRow(Int32 hRow, Int32 lOutputID) at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.DirectRow(Int32 outputID) at Lib1.LibPM.ProcessInput(Int32 inputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper90 wrapper, Int32 inputID, IDTSBuffer90 pDTSBuffer, IntPtr bufferWirePacket) Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: The ProcessInput method on component "Lib" (2387) failed with error code 0x80070057. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0x80070057. Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread1" has exited with error code 0xC0047039. Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning. Information: 0x402090DD at Data Flow Task, Flat File Destination [1855]: The processing of file "C:ole db eft data.txt" has ended. Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning. Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "Flat File Destination" (1855)" wrote 0 rows. Task failed: Data Flow Task Warning: 0x80019002 at Package: The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package "Package.dtsx" finished: Failure. ----------------------------
This is my piece of code which is trying to put the data in the output buffer (ProcessInput function).
I'm getting an error that reads "The conversion of a char data type to a datetime data type resulted in an out of range value data time value" for the following view sql: CONVERT (datetime, System . dbo.zStatus.MyDate)
Getting the below error msg on my conditional split. I changed the error output to ignore errors and that keeps the error msg from appearing (and everything seems to work normally), but why would it evaluate to NULL?
Thanks
[Conditional Split - Find rows with balances [3412]] Error: The expression "FINDSTRING(Column0,"OPENING",1) > 0 || FINDSTRING(Column0,"CLOSING",1) > 0" on "output "Balance Rows" (3415)" evaluated to NULL, but the "component "Conditional Split - Find rows with balances" (3412)" requires a Boolean results. Modify the error row disposition on the output to treat this result as False (Ignore Failure) or to redirect this row to the error output (Redirect Row). The expression results must be Boolean for a Conditional Split. A NULL expression result is an error.
A little puzzle with this Error - Status and PropertyType fields do not allow nulls while other columns do ( there are about 20 columns in original expression but left out for simplicity - these are created the same way ). Any ideas how to solve it?
[Conditional Split Filter Changes [813]] Error: The expression
on "output "Listing Changed" (885)" evaluated to NULL, but the "component "Conditional Split Filter Changes" (813)" requires a Boolean results. (My wish for next version is to have better expression editor and be able to see the output for those in dataflow transforms with lets say sample 200 rows)
I am trying to update a database that has decimal(4,1) in a field. It can hold 100, but when I change a value in a gridview to 100 and update it, I get the error below. Any reason wny? Parameter value '100.0' is out of range.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.ArgumentException: Parameter value '100.0' is out of range.
Source Error:
Line 1841: int returnValue;Line 1842: try {Line 1843: returnValue = command.ExecuteNonQuery();Line 1844: }Line 1845: finally {
Source File: c:WINDOWSMicrosoft.NETFrameworkv2.0.50727Temporary ASP.NET Filesmyprofile45e0ea66e9c9d282App_Code._wfrgryw.27.cs Line: 1843
I am attempting to write a SQL query that retrieves info processed between two times (ie. 2:00 pm to 6:00 pm) during a date range (ie. 8/1/06 to 8/14/06)... I am new to SQL and am perplexed... I have referenced several texts, but have not found a solution. Even being pointed in the right direction would be greatly appreciated!!
I have a report that is using the following expression for one of the fields:
=Split(Fields!FromAccount.Value, " ")(1)
The format of the number is xxxxx-xxx xxxxxxxxxxxx
So the function is to grab the second section of numbers after the space. The query for this report can bring back 1 to many results which equals one to many pages.
I have an issue where the query returns three results. The first page will display the correct number, the second page displays #Error and the third displays the number.
When I run the query from Management Studio I see the numbers are as follows:
Note the first and and last lines have extra spaces which I thought would be the cause of the problem though I would've expected the #Error to appear on the first and third page rather than the second one.
When I remove the extra space for the first and third number all three pages display their values correctly.
However, there are many, many numbers in the table that have one to two spaces. These numbers are from a spreadsheet that is imported biweekly. So it was either I fix the spreadsheet before every import or I come up with a new expression to check for one or two spaces. This is what I came up with:
Now, the first page displays the number correctly while the second and third page displays with the #Error. Basically I want to say if the value contains two spaces then split at the two spaces otherwise split at the one space.
hello,everyone,I also met a problem. I stoped the SQL Server Analysis Services first, and registered the GAC successfully, then modified the msmdsrv.ini manually.In fact,I found the format of my msmdsrv.ini is diffirent from the one mentioned in the tutorial,it just like below:
when i restarted the the AS, some error information displayed in event viewer, " can't load MyCompany_Mamaged_Plugin_Algorithm data mining Algorithm provider(ProgID, can't create Algorithm Provider program for MyCompany_Managed_Plugin_Algorithm, ProgID does not exist in the server configuration file"
I am confused by it and have not any idea to solve it. some one can give tips? thanks.
Hi all i have setup merge replication with one publisher and 3 subscribers
The replication worked fine for about 2 months then i recieved the following error on all my subscriptions
The Publisher failed to allocate a new set of identity ranges for the subscription. This can occur when a Publisher or a republishing Subscriber has run out of identity ranges to allocate to its own Subscribers or when an identity column data type does not support an additional identity range allocation. If a republishing Subscriber has run out of identity ranges, synchronize the republishing Subscriber to obtain more identity ranges before restarting the synchronization. If a Publisher runs out of identit (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199417) Get help: http://help/MSSQL_REPL-2147199417
I have updated my sql server to service pack 2
The publisher is sql Standard edition and the subscribers are Express edition
I am embedding the ReportViewer in an aspx page and the report comes up fine, but when I press the "Next Page" or "Last Page" buttons, I get the error: "Page navigation is out of range".
The application has session state turned off. The problem occurs whether or not the viewstate for ReportViewer is turned on or off.
Hi all,I am getting this error when insert values from one table to another inthe first table the values are varchar (10). In the second they aredatetime. The format of the data is mm/dd/yyyy to be easily convertedto dates. The conversion in this case is implicit as indicated in SQLServer documentation. Here is my query:INSERT INTO Campaign (CampaignID, Name, DateStart, DateEnd, ParentID,ListID)SELECT mysqlfactiva.dbo.campaigns.campaign_id AS CampaignID,mysqlfactiva.dbo.campaigns.campaign_name AS Name,MIN(mysqlfactiva.dbo.programs.start_date) AS DateStart,MIN(mysqlfactiva.dbo.programs.end_date) AS DateEnd,NULL AS ParentID,NULL AS ListIDFROM mysqlfactiva.dbo.campaigns, mysqlfactiva.dbo.programsWHERE mysqlfactiva.dbo.campaigns.campaign_id =mysqlfactiva.dbo.programs.campaign_idGROUP BY mysqlfactiva.dbo.campaigns.campaign_id,mysqlfactiva.dbo.campaigns.campaign_name,mysqlfactiva.dbo.campaigns.descriptionUNIONSELECT program_id + 100000, program_name, start_date, end_date,campaign_id AS ParentID, NULL AS ListIDFROM mysqlfactiva.dbo.programsUNIONSELECT execution_id + 200000, execution_name, start_date,end_date, program_id + 100000 AS ParentID, NULL AS ListIDFROM mysqlfactiva.dbo.executionsUNIONSELECT wave_id + 300000, wave_name, start_date, end_date,mysqlfactiva.dbo.waves.execution_id + 200000 AS ParentID, NULL ASListIDFROM mysqlfactiva.dbo.waves, mysqlfactiva.dbo.executionsWHERE mysqlfactiva.dbo.waves.execution_id =mysqlfactiva.dbo.executions.execution_idI am referencing programs table two times. If I just select this all Iget all data I need. When doing insert I get a message:Server: Msg 242, Level 16, State 3, Line 1The conversion of a char data type to a datetime data type resulted inan out-of-range datetime value. The statement has been terminated.If I execute just first part of the query before first union, I insertdata fine:INSERT INTO Campaign (CampaignID, Name, DateStart, DateEnd, ParentID,ListID)SELECT mysqlfactiva.dbo.campaigns.campaign_id AS CampaignID,mysqlfactiva.dbo.campaigns.campaign_name AS Name,MIN(mysqlfactiva.dbo.programs.start_date) AS DateStart,MIN(mysqlfactiva.dbo.programs.end_date) AS DateEnd,NULL AS ParentID,NULL AS ListIDFROM mysqlfactiva.dbo.campaigns, mysqlfactiva.dbo.programsWHERE mysqlfactiva.dbo.campaigns.campaign_id =mysqlfactiva.dbo.programs.campaign_idGROUP BY mysqlfactiva.dbo.campaigns.campaign_id,mysqlfactiva.dbo.campaigns.campaign_name,mysqlfactiva.dbo.campaigns.descriptionAs soon as I use union I get the above error. This is very strangesince even when I execute the query using first union where the datescome from the same table 'programs' I get the error. Why I can insertfrom programs first time and can's second time?Any help will be appreciated.Thanks,Stan
I am unable to migrate any DTS packages, from a SQL Server 2000 package, from a structured storage file, or from a DTS package imported into SQL2005 (Developer Edition, 32 bit). Running the Package Migration Wizard, every time I get to the List Package screen, i get the error below. I haven't found any other mention of this so far. Anyone else seen this error or have suggestions?
I've a report whose columns are returned from a stored procedure. Now I want to display the report based on a date range. The date field is Received. It's in dbo.master. I added 2 parameters start date and end date. When I check the condition if dbo.master.Received>StartDate and dbo.master.Received < EndDate directly I'm getting error. Could someone tell me what mistake I'm doing? Thanks for your help!ALTER Procedure [dbo].[USP_Reports_NewTier1]
@ClientCode VARCHAR(7) = '',@UserID INT = 0 ,@OrderID INT =0 ,@StartDate datetime,@EndDate datetime
IF @ClientCode <> '' and dbo.master.Received > StartDate and dbo.master.Received<EndDateBEGIN SELECT --Root Select --ClientName @ClientName = (Select Name FROM dbo.customer c WHERE c.Customer = @ClientCode) ,@TotalDollarValue = (SELECT SUM(m.current1-m.paid1) FROM dbo.master m WHERE phase=1 AND m.Customer = @ClientCode AND M.Status <> 'PIE') ,@AverageAge = ISNULL((select avg(age) from (select datediff(day,Received,CASE WHEN clidlp>clidlc then clidlp else clidlc END)* -1 as age from dbo. master M WHERE phase=1 AND customer = @ClientCode AND M.Status <> 'PIE') x),0)END
I have a execute sql task which insert data into a table, but before that I need to make sure no data is available for particular date. we have a table where we are defining date for which date we want to data, and we are fetching that particular date using ssis variable 'Date'. today I was trying to load data dor '2015-05-10'. But it gave me error like '[Execute SQL Task] Error: "Value does not fall within the expected range.'. Below are the my execute sql task query:
delete from STG_Shipped_Invoiced where Transaction_Date=? INSERT INTO STG_Shipped_Invoiced (div_Code, inv_inv_id, tot_Net_Amt, trans_date, trans_type, Created_date, Transaction_Date) select inv.DIV_CODE as Div_Code, inv.INV_ID as inv_inv_id, inv.TOT_NET_AMT as Tot_Net_Amt,
[Code] ....
Here I have defined a variable 'Date', where we are passing the date. Am i doing anything wrong with the delete query?
I'm running a pre-defined script which used to work fine on a file which was resupplied every month. below is the script used and the error message. looking at the error I assume that there is a rouge record within the file but have looked in Textpad and cannot find it.
UPDATE [matching].[dbo].[hot_nov] SET [AOV] = (CAST([Demand] AS DECIMAL)/CAST([Orders] AS INT)) WHERE [Demand] <> '';
UPDATE [matching].[dbo].[hot_nov] SET [POST2] = left([PostCode], PATINDEX('%[0-9]%', [PostCode] + '1') - 1) ; UPDATE [matching].[dbo].[hot_nov] SET [POST4] = left([PostCode],LEN([PostCode])-4); UPDATE [matching].[dbo].[hot_nov] SET [POST6] = left([PostCode],LEN([PostCode])-2);
error message
(1000 row(s) affected) Msg 245, Level 16, State 1, Line 181 Conversion failed when converting the varchar value '2014-09-03 00:00:00' to data type int.
I have 2 tables, one is table A which stores Resources Assign to work for a certain period. The structure is as below
Name StartDate EndDate Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000 Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000 Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000
The table B stores the item process time. The structure is as below
Item ProcessStartDate ProcessEndDate V 2015-04-01 09:30:10.000 2015-04-01 09:34:45.000 Q 2015-04-01 10:39:01.000 2015-04-01 10:41:11.000 W 2015-04-01 11:44:00.000 2015-04-01 11:46:25.000 A 2015-04-01 16:40:10.000 2015-04-01 16:42:45.000 B 2015-04-01 16:43:01.000 2015-04-01 16:45:11.000 C 2015-04-01 16:47:00.000 2015-04-01 16:49:25.000
I need to select the item which process in 2015-04-01 16:40:00 and 2015-04-01 17:30:00. Beside that I need to know how many resource is assigned to process the item in that period of time. I only has the start date is 2015-04-01 16:40:00 and end date is 2015-04-01 17:30:00. How I can select the data from both tables. There is no need for JOIN, just seperate selections.
Another item process time is in 2015-04-01 10:00:00 and 2015-04-04 11:50:59.
The result expected is
Table A
Name StartDate EndDate Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000
Table B
Item ProcessStartDate ProcessEndDate A 2015-04-01 16:30:10.000 2015-04-01 16:32:45.000 B 2015-04-01 16:33:01.000 2015-04-01 16:35:11.000 C 2015-04-01 16:37:00.000 2015-04-02 16:39:25.000
Scenario 2 expected result
Table A
Name StartDate EndDate Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000 Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Hello,I'm getting the following error message when I try add a row using aStored Procedure."The identity range managed by replication is full and must be updatedby a replication agent".I read up on the subject and have tried the following solutionsaccording to MSDN without any luck.(http://support.Microsoft.com/kb/304706 )sp_adjustpublisheridentityrange (http://msdn2.microsoft.com/en-us/library/aa239401(SQL.80).aspx ) has no effectFor Testing:I've reloaded everything from scratch, created the pulications from byrunning the sql scripts generated,created replication snapshots andstarted the agents.I've checked the current Identity values in the Agent Table:DBCC CHECKIDENT ('Agent', NORESEED)Checking identity information: current identity value '18606', currentcolumn value '18606'.I check the Table to make sure there will be no conflicts with theprimary key:SELECT AgentID FROM Agent ORDER BY AgentID DESC18603 is the largest AgentID in the table.Using the Table Article Properties in the Publications PropertiesDialog, I can see values of:Range Size at Publisher: 100,000Range Size at Subscribers: 100New range @ percentage: 80In my mind this means that the Publisher will assign a new range whenthe Current Indentity value goes over 80,000?The Identity range for this table cannot be exhausted! I'm not surewhat to try next.Please! any insight will be of great help!Regards,Bm
I have a need to select rows where values in a column are within a range AND ALSO the first row that is highest (MAX) but just UNDER the range. For example:
NAME VAL Jim 4 John 5 Tom 6 Julie 7 Rich 8 Rob 9
RANGE TO SELECT is 6-8 (inclusive)
Rows with a value in the range are returned (6, 7, & 8) as well as the row with a highest value that is JUST BELOW the range (5)
