I need help from sql experts for the following problem
DOCTYPE DATE QTY PRD LOT
Purchase 1 jan 20+ AA 2007FW
Purchase 4 jan 50+ AA 2007SS
Purchase 9 jan 10+ AA 2007FW
Sale 3 jan 10- AA
Sale 4 jan 20- AA
Returned Good 4 feb 10 AA
As you can see I don't have the LOT code in sales records, so I must update these records with the following logic:
I have to assign LOT code to sales in FIFO order:
from the table above the 3rd of January I find the first sale of 10, take the first LOT (ascending date order), check for qty on hand (20+), consume 10 from LOT (10 remaining), update sale record with 2007FW LOT code.
Then I find next sale of 20, as before I take the first LOT with qty on hand to consume, again it's the first record with only 10 remaining so I set LOT qty on hand to zero but I have 10 more to allocate to a LOT code. So I find next available LOT of 50 and consume 10, with 40 remaining.
It's important to remember that I can sell part of a "lot", have to track the remaining goods per LOT
Is it possible in SQL to do that in batch mode? How? If I have to split sale record consuming 2 or more LOTS, how can I do? Can you show me SQL or good hints?
My buddy has an application that logs entries, and for space reasons needs to only retain a maximum N records in the log table. He wants to delete old log entries as part of the insert procedure. Here is his stab at it:CREATE PROCEDURE spInserttblLog @Message varchar(1024), @LogDate datetime, @ElapsedSeconds float, @LogLevel varchar(50), @UserName varchar(100), @ProcessName varchar(100), @MachineName varchar(100), @MaxEntries int=0 AS declare @ varchar(300) if @MaxEntries > 0 Begin set @MaxEntries = @MaxEntries -1 set @SQL='Delete From tblLog Where LogID Not In (Select Top ' + cast(@MaxEntries as varchar) + ' LogID from tblLog order by LogID desc)' execute (@SQL) End
I think this would be more efficient:--SQL Server CREATEPROCEDURE spInserttblLog @Message varchar(1024), @LogDate datetime, @ElapsedSeconds float, @LogLevel varchar(50), @UserName varchar(100), @ProcessName varchar(100), @MachineName varchar(100), @MaxEntries int=0 AS
delete fromtblLog whereLogID < (select max(LogID) from tlbLog) - @MaxEntries - 2 and @MaxEntries > 0
insert into tblLog (Message, LogDate, ElapsedSeconds, LogLevel, UserName, ProcessName, MachineName) values(@Message, @LogDate, @ElapsedSeconds, @LogLevel, @UserName, @ProcessName, @MachineName)Comments, or suggestion for an even faster method? This is a relatively high-activity table, so there are a lot of inserts.
I have a DTS that imports data from an orcle database into SQL Server. Doesn't the processing mostly occur on the SQL Server, not on the oracle database from which the data is being imported? The oracle database is vendor provieded and they are saying our SQL Server DTS package is killing their server. Any insight is appreciated. Thanks
I've got a process that creates records in my database based on XML input that I've gotten. What I am doing it giving this XML to a stored procedure to handle a specific task, then modify the XML and send it to the next stored procedure.
For instance, the XML could hold header records with detail records, I would first send the XML to a stored procedure that creates the header records, then updates the XML so the XML now knows the identity values of the header records I have just created, and then send the XML to the next stored procedure to create the details for those headers.
All works great and fine, but I have a problem with writing the identity values back to the XML. It seems I can only change one item in the XML at a time and thus need to loop this. For many records this really takes a long time.
Here is some sample code of what I'm doing (please excuse any typos, this is a simplified version of the code) :
declare @lvSeq numeric(15) declare @lvRowNo int declare @lvNumRows int
insert into myHeaderTable ( recid, recdesc ) select ref.value('@recid', 'nvarchar(25)') recid, ref.value('@recdesc', 'nvarchar(250)') recdesc from @pXML.nodes('//headers/header') R(ref)
select @lvRowNo=1, @lvNumRows = @pXML.value('count(//headers/header)', 'int') while (@lvRowNo<=@lvNnumRows) begin select @lvSeq = recseq from myHeaderTable where recid = @pXML.value('//headers/header[position()=sql:variable("@lvRowNo")]/@recid)
set @pXML.modify('replace value of (//headers/header[position()=sql:variable("@lvRowNo")]/@recseq with sql:variable("@lvSeq")')
select @lvRowNo=@lvRowNo+1 end
Obviously I am looking for a better way to update the XML with the sequences. The insert takes a second, the loop takes minutes with large XML sets. I guess MSSQL is searching the whole XML to find the item to update.
It would be nice if I didn't have to loop through the XML. One solution I was thinking off is to store the XML in a temporary table with a single record per header item. Then I could do the modify in one go and recreate the XML by simply selecting the contents of the temporary tabel. I have no idea if this is possible.
So something like this:
select ref.value('@recid','nvarchar(25)') recid, ref.value('.','XML') XMLData -- this gives an error into #TMP_XML from @pXML.nodes('//headers/header') R(ref)
insert into myHeaderTable ( recid, recdesc ) select recid, ref.value('@recdesc', 'nvarchar(250)') recdesc from #TMP_XML CROSS APPLY XMLData.nodes('/header') R(ref)
update #TMP_XML set XMLData.modify('replace ....') from myheadertable where #TMP_XML.recid = myheadertable.recid
Hello friends, I needed a suggestion, I am currently working on a reporting website that generates reports and i need to store all the reports in the database.
I usually go by row wise processing as it can be easily controlled but the problem is there will be a lot of reports, that is an estimation of 30,000 rows in a month and i m not sure if sql server can hold more than 2 billion rows per table.
I will just explain whole scenario what I m facing in tricky problem..
We have xml files coming at regular interval by some other source into sql server 2000…daily having records near @10000 to 70000…we have job scheduled to run it regular interval…we doing this by some filter criteria… suppose the flow is like staging table into secondary table and then final into primary table…. We design DTS package accordingly means take the records from staging table put into secondary table and then into primary table…(near @ 8 task involved in it…) Suppose xml file came at 8:30 am and our DTS package will run at 9:00 am…and then 11:00 am and the 1:00 pm like that….what I observing from many days is that after running job at 9:00 am successfully some good data still pending in secondary table not processed into primary table. But when again job ran at 11:00 am it processed that pending good records into primary table…some times when I ran this job manually through DTS design level the good data that pending in secondary table processed!!!
My question is that why this job not processed all the good records in single shot????
Hello everyone. I need help regarding the following:Given the following table:CREATE TABLE T1 (C1 nvarchar(10), C2 money)INSERT INTO T1 VALUES ('A',1)INSERT INTO T1 VALUES ('B',2)INSERT INTO T1 VALUES ('C',3)let's say that i have this table in a local server and i want to uploadit to a remote server and in the remote server upload it to a databasethat contains the same table.the uploading part can be done by another application in the remoteserver, but i want i need is a way to transfer the data at the fastestpossible way.what steps do i need to follow?tia,Rey Guerrero
Which component should be used to process dataset as a whole, and not on per row basis? I have need to process dataset conditionally (condition based on dataset), e.g. if a special row is present in dataset than dataset should be processed in a special way. Should I maybe use one Script Transformation to determine if dataset satisfies condition (and store that result into a variable), and then based on that condition (variable value) perform or not processing (using Conditional Split and Script Transformation)?
I am having trouble trying to construct the following process in SSIS/SQL 2005:
1. Grab a set of unprocessed rows (ProcessDT = null) in an 'Action' table 2. For each of these rows, execute multiple stored procedures base on the action type If actiontype = 1, exec spAct1a @param1, @parm2 exec spAct1b @param1, @parm2, @param3, @param4 If actiontype = 2, exec spAct2a @param1, @parm2, @param3 exec spAct2b @param1, @parm2, @param3 etc.... 3. Update ProcessDT so it's not processed again 4. Repeat until all rows are processed
Note - all sp params are contained in additional columns in the Action table. Basically the Action table is a store for post-event processing of sorts but is order dependent, hence the row by row processing. And some of my servers are 2000 so Service Broker is not an option (yet).
I first attempted to do this totally within the control flow - using an ado recordset/foreach loop control, but I could not figure out how to run conditional process paths based on the ActionTypeID. I then tried to do this within the dataflow using on OLEDB data source, a conditional split, and an oledb command control which almost got me there - the problem being for each row I need to execute multiple sp's and it appears as if the oledb command only gives me one sp.
I'm populating a new table based on information in an existing table. The new table is a list of all "items" and contains a primary key. The old table is a database of receipts where items can appear many times in any order.
I have put together the off-the-shelf components to do this, using a lookup transformation to see if the item is already in the new table. Problem is, because there's so much repetition in the old table I need to process the old table one row at a time. Batch processing is generating errors because the lookup doesn't detect duplicates within the buffer.
I tried setting the "DefaultBufferMaxRows" property of the task to 1, but that doesn't seem to have any effect.
To get the data from the old table, I'm using an OLE DB source. To get the data into the new table, I'm using the OLE DB Command transformation with parameters to execute an INSERT statement.
This is a job I have to do exactly once, so I don't care if I have to run it overnight. I'd rather have a simple, easy to understand but inefficient script so I understand what it's doing completely.
Any help on forcing SSIS to process one row at a time?
I am verifying my reports processing time. I get the information from the Reporting Service DB - [ExecutionLogs] table. I have the following information:
[TimeEnd] €“ time that reports generation ends.
[TimeStart] - time that reports generation starts.
[TimeDataRetrieval] - amount of time spent running the data sources.
[TimeProcessing] - time spent processing the report.
[TimeRendering] - time spent generating the output format.
If this information is correct the following statement should be true:
When using the AS processing task with a connection to "an Analysis Services project in this solution", only some processing options are available for processing dimensions. For instance, it is not possible to select "Process Update". Once I change the connection manager to point to the deployed cube database, I can choose from all the options. Is this by design?
I have 3 cubes in a single SSAS database and these cubes should be processed using the following schedule
Cube 1 - Every Day Cube 2 - Every Week Cube 3 - Every Month Cube 4 - Every Day
The issue that I face is that these cubes share the dimensions and so I cant do a FULL process of these SHARED Dimensions as it will affec other cubes.
I can expect additions and deletions to my dimension data , but the structure remains the same. It would be great if someone can suggest how to go about processing the dimensions. I am confused with the number of options(Process Incremental, Process Update etc.,) available for processing the dimensions.
I will creating a SSIS package to automate the processing. One more question is say, if Cube 2 fails during a day and Cube 1 has succesfully processed on the same day earlier, how do I revert back to the old state of Cube 2? Does this mean that I need to do a back up of the SSAS database before processing each cube?
I am in the midst of designing a new Data Warehouse system. As we get further into the design of the system, the more we are realising how complex our ETL is going to be and that the amount of time it will take to run could be significant i.e. a few days! My question is obviously I don't want to have a down time in my relational system for this long and prevent my users from accessing the data for days at a time each month. So what functionality should I be looking at to allow me to maintain a working copy of the data that users can query whilst I perform database updates and then perform a quick promotion of the updated data to users for querying?
If you can point me in the direction of the right functionality in SQL Server 2005 and possible some relevant white papers that cover this sort of scenario I would be grateful.
Stored procedure works: PROCEDURE dbo.VerifyZipCode@CZip nvarchar(5),@CZipVerified nvarchar(5) outputAS IF (EXISTS(SELECT ZIPCode FROM ZipCensus11202006 WHERE ZIPCode = @CZip)) SET @CZipVerified = 'Yes' ELSE SET @CZipVerified = 'Not Valid Zip'RETURN Need help calling and processing sp information:
protected void C_Search_btn_Click(object sender, EventArgs e){ SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["localhomeexpoConnectionString2"].ConnectionString); SqlCommand cmd = new SqlCommand("VerifyZipCode", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@CZip", UserZipCode_tbx.Text); . . . how do you set up output parameter, call the SP and capture output. . . ? if (@CZipVerified == "Not Valid Zip") { TextBox5.Text = "Zip code not valid please re-enter zip"; } else { Continue processing page } }
How would I go about checking incoming e-mails? For example, on a certain e-mail address, I would get e-mails formatted in a certain way. According to the response, some scripts need to run/ some sql tables updates etc. How can one do this in (ASP) .NET with SQL Server? Anyone did this kind of stuff before?
When I process a new cube I recieve an error "Error (211): Unknown dimension member ' 8'; Time: 8/11/00 1:09:45 PM". Any ideas about what this error is about ?
Is there anybody out there that can help me on how can I know the processing time taken for one transaction by using SQL Analyzer??
1)For example, I want to update using Analyzer and I would like to know time taken to do this update???
2) How to reduce processing time by using Store Procedures that using cursor?? I have add in some commit statement in my update statement.. Is there any other ways??
I've noticed lately that my queries through ADO/VB are taking alot longer to process at certain times. The query and the result information never changes, only that at certain times the query takes alot longer than usual. I thought that I possibly need more licenses, or it might be network traffic. I currently use MS SQL Server 2000 small business Ed(5 Cals).
Has anyone any information about performance problems due to licensing issues?
I need to extract a particular file from our AS 400 system on a daily basis and do some processing on it. Also I want to do the daily processing only on those records that have been added/updated since the initial load. Here is the approach and possible implementation.
Approach I have the DB2 source data containing 10 columns (Col1 to Col5 together form the key) and the rest are attributes. I am interested only in the key and two attributes. So I load my table with only Col1 to Col7 ( 5 for key and the two attributes). I then do my processing on this table.
Here is the implementation given by a member of dbforums -
You'll then have to deal with 3 potential actions,
INSERT: New records on the file. DELETES: Records that don't exists. UPDATES: Records that are on the file, but attributes have changed.
INSERT INTO myTable99(Col1,Col2,Col3,Col4,Col5) SELECT '1','1','a','b','c' UNION ALL SELECT '1','2','d','e','f' UNION ALL SELECT '1','3','g','h','i' UNION ALL SELECT '1','4','j','k','l' --DELETED
INSERT INTO myTable00(Col1,Col2,Col3,Col4,Col5) SELECT '1','1','a','b','c' UNION ALL--NO CHANGE SELECT '1','2','x','y','z' UNION ALL-- UPDATE (My comment - Instead of an update I want to insert a new record) SELECT '1','3','g','h','i' UNION ALL--NO CHANGE SELECT '2','3','a','b','c'--INSERT GO
SELECT * FROM myTable99 SELECT * FROM myTable00 GO
--DO DELETES FIRST (My comment - Before deleting, I want to copy the rows that I am going to delete on a separate table to maintain history. Then I want to delete from a). I don't get the logic. If the rows of the old extract are not in new extract then delete them. So shouldn't it be <> instead of =. why the where clause condition) DELETE FROM a FROM myTable99 a LEFT JOIN myTable00 b ON a.Col1 = b.Col1 AND a.Col2 = b.Col2 WHERE b.Col1 IS NULL AND b.Col2 IS NULL
-- INSERT (My comment - I don't get the logic of the where. If the rows of the old extract are not in new extract then delete them. So shouldn't it be <> instead of =)
INSERT INTO myTable99(Col1,Col2,Col3,Col4,Col5) SELECT a.Col1, a.Col2, a.Col3, a.Col4, a.Col5 FROM myTable00 a LEFT JOIN myTable99 b ON a.Col1 = b.Col1 AND a.Col2 = b.Col2 WHERE b.Col1 IS NULL AND b.Col2 IS NULL
-- UPDATE
UPDATE a SET Col3 = b.Col3 , Col4 = b.Col4 , Col5 = b.Col5 FROM myTable99 a INNER JOIN myTable00 b ON a.Col1 = b.Col1 AND a.Col2 = b.Col2 AND ( a.Col3 <> b.Col3 OR a.Col4 <> b.Col4 OR a.Col5 <> b.Col5) GO ------------
Can anybody look at My comments and answer them or revise this code template if need be?
Brett Kaiser - I sent you an e-mail on this. Can you respond to it when time permits.
When I try to process my mail with xp_findnextmsg or xp_readmail or xp_sendmail i get these errors:
Server: Msg 18025, Level 16, State 1, Line 0 xp_findnextmsg: failed with mail error 0x80004005 Queries processed: 0. Server: Msg 18025, Level 16, State 1, Line 0 xp_readmail: failed with mail error 0x80004005 Server: Msg 18025, Level 16, State 1, Line 0 xp_sendmail: failed with mail error 0x80004005
my configuration is: ms sql 2000 sp3 outlook 2003 as mapi client sql server is started under account for which mapi profile is configured
Does anyone know what is wrong, and how to solve this problem?
I created a DTS which does some data transformations before processing some cubes. It finished processing in abt 10mins when I run this DTS manually. However, when I schedule this DTS to run, it took around 3 over hours to run. Does anybody know where the problem lies? I have been looking for a solution for this for a long time and I'm hoping that somebody can help me...
I did Index defragmentation a week ago . for 1 database only , In the middle of rebuild I kill the process twice cause It takes more than 1 hour so I killed it and wonder how many high level fragmented indexes left ...
I am painfully learning SSIS and just when I think I have a better understanding of how things work and go down the path that I should take, I hit a wall. Frankly, discouraging and demoralizing.
I am performing a Select of rows that are then passed on to a ForEachLoop with an ADO Enumerator with a FullResultSet. Variable mappings for the columns used are:
A ScriptTask in the foreach loop allows me to confirm that I am processing rows.
A DataFlow in the ForEachLoop has in it a DERIVED COLUMN item that allows me to assign the four variable fields above to their respective DERIVED COLUMN.
An attempt to use a Flat File Destination to process the derived columns is being made. As I monitor the execution of the package I notice the following message.
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "Flat File Destination" (382)" wrote 0 rows.
Can anyone PLEASE tell me why is it that I am able to see the data being processed through the script task? And yet not see any rows being written to the Flat file. Why?
Any information you may provide would be immensely appreciated.
I run a query "BEGIN select * from NT_ins_params_S WITH (HOLDLOCK,ROWLOCK) where...... and load the result into an ADO object. I need to lock the row untill I am done processing and I thought that (HOLDLOCK,ROWLOCK) will do the job but it does not. What is the correct way for locking?
I am using sql 2000 server and interfacing with it using a program written in C#
CREATE TABLE dbo.Numbers (Number INT IDENTITY(1,1) PRIMARY KEY CLUSTERED) WHILE COALESCE(SCOPE_IDENTITY(), 0) < 5 BEGIN INSERT dbo.Numbers DEFAULT VALUES END
SELECT category, SUBSTRING( Value, Number, CHARINDEX( ',', Value + ',', Number ) - Number ) as program FROM Test inner JOIN Numbers ON SUBSTRING( ',' + Value, Number, 1 ) = ',' and CHARINDEX( ',', Value+',', Number ) - Number <> 0 where Number <= Len(Value) + 1
But I would like to Transform this table into something like the one below (where if 'Y' before 1st comma then Q1, if 'Y' Before 2nd comma then Q2 and so on
I'm working on a system right now where I have a database (two,actually, but one is discarded halfway through), but it's createdand used as part of a process (reporting), rather than as theactual production data repository. I may be keeping the databasepermanantly, but it would be completely read-only; once theprocess is complete, the database will not change again. This hasme wanting to do a few things that are rather foreign to my usualexperience, and I don't know how many of them are supported.In several cases, I'm summarizing one table into another by severalfields, and then updating the original table with an ID for thesummary row each source row was summarized into (e.g., I summarizePlaceAndProductSummary into PlaceSummary, and then populatePlaceSummaryID in PlaceAndProductSummary). The update of thesource table is much faster if the summary table has a clusteredindex on the summarized fields, but all later access will be fasterif the clustered index is on the identity column. I've beenincluding an ORDER BY the summarized fields in the original insert,so the identity column is in the same order as the summarized fields,but I don't know of any way to take advantage of that in theindexing declarations.As another approach to the above situation, if I change theclustered index on a table, and the rows happen to be in thesame order by both indexes, will the table still get rebuilt?I will never do a roll-back in the process; if an action fails, Iwant to raise an error and halt (and I haven't lost any data).Is there any way to completely turn off logging?Will I gain anything by marking the database as single-user?Any indexes that I am not using while I populate the tables, I'madding at the end with FillFactor 100, to keep any slack out.Is there a way to remove all the slack from everything else, atthe end of the process? During a backup operation would be fine.Thanks,Bill
Hi,I have just started using MSSQL and the DOS environment at work. I havea lot of experience with Sybase and the UNIX environment, but this is awhole new ball of wax.I'd like to use osql from a batch file to log into the dataserver andrun a fairly long list of SQL and then exit. I don't want to have a bunchof SQL files sitting around that I have to use the -i option to run, andI'd rather not create temporary SQL files like this.echo "exec sp_who2" >tmp.sqlecho "select * from....... " >>tmp.sqlosql -E -S <DATASERVER> -n -w999 -i tmp.sqldel tmp.sqlI can't use the -Q option, of course because as I said, I'll be writingquite a few lines of SQL, and it won't all fit on the one line, or at leastit wouldn't be pretty if I did.In UNIX, I can simply execute the following from either the command lineor ina script.isql -S<DATASERVER> -U<USER> <<-EOFsp_whogoselect * from .....goEOF --EOF is the isql session terminator exits me back to the command line.This behavior does not appear to work with OSQL. There is a -O optionwhich help mentions, and that talks about disabling the EOF terminator forbatch processing, but I wasn't able to find any usage or examples on the netwhere someone is using EOF to terminate their OSQL SQL batch.This is what help listed.[-O use Old ISQL behavior disables the following]<EOF> batch processingAuto console width scalingWide messagesdefault errorlevel is -1 vs 1Any help you could offer would be appreciated. Thanks.Darren
I have written a simple sql statement that I am running through theviews in sql manager that retrieves data from a couple of tables by daterange and a street name. I am getting the following run times forvarious date ranges.I did the above as a test because of a sudden reduction in retrievalresponse times.September data < 1 secondOctober data < 1 secondSeptember + October > 30 seconds (what could cause the jump)September 1 > October 5 < 1 secondSeptember 1 > October 6 > 30 secondsTotal records per month is less than 50Indexes just rebuiltSame results when running fron another PCResults are consistant from run to runOnly three fields returnedFast server, 4 processors, very little activity, 4 gig ramAny ideas where to look? Reboot server?Thanks.Charles*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
