It will have different issues.
Your best bet is to make sure you install it to use Unicode. But watch out, PostGreSQL uses UTF8 while dotnet uses UTF16 internally. So you might still encounter odd issues.
MySQL supports UTF16 from version 5.5 IIRC.
I have the following query that is created by MS Access while creating report. But the query has lots of inner queries and joins. Can anybody please help in making this big query simple and by removing all the possible brackets and make it clean. Any type is very helpful. This query really taking a lot of time to understand it. Any link, tool name or way to solve it any help is very much helpful. Thanks in advance.
Here is the query.
SELECT so.EntityId, s.SponsorId, so.OrderId, so.ProgramId, so.ProgramYear, s.SponsorNbr,
s.SponsorNme, s.VendorNbr, s.MailFdpCde, dbo.Reference.RefCde, dbo.Reference.ExtCde, dbo.FDPSponsorApp.CurrentInd,
dbo.DeliveryPeriod.BegDte, dbo.DeliveryPeriod.EndDte, dbo.vwRef_fdpprogram.RefDsc, dbo.vwRef_fdpprogram.RefCde AS ProgramCde, dbo.Product.ProductCde,
dbo.Product.ShortDsc as ProductDsc, sod.QtyInv AS QtyRcv, dbo.Product.NetPackWt,
(CASE WHEN ((dbo.ProductCost.FixedCost IS NULL) OR (dbo.ProductCost.EntDrawInd= 653)) THEN ISNULL(dbo.ProductCost.WghtAvgCost, 0)
ELSE ISNULL(dbo.ProductCost.FixedCost,0) END) AS UnitCost,
[QtyInv]*(CASE WHEN ((dbo.ProductCost.FixedCost IS NULL) OR (dbo.ProductCost.EntDrawInd= 653)) THEN ISNULL(dbo.ProductCost.WghtAvgCost, 0)
ELSE ISNULL(dbo.ProductCost.FixedCost,0) END) AS ValueRcvd,
[NetPackWt]*[QtyInv] AS [Total Pounds], dbo.Contact.FirstNme, dbo.Contact.LastNme, dbo.Address.Addr1, dbo.Address.Addr2,
dbo.Address.City, dbo.Address.State, dbo.Address.ZipCde, dbo_Reference_1.RefDsc AS DeliveryType, so.DeliveryTypeId,
dbo.RefYearDefaults.DefValue AS BrownBoxFee, dbo_RefYearDefaults_1.DefValue AS DirectDiversionFee, dbo.Product.NetPackWt, s.VendorNbr,
FROM dbo.RefYearDefaults AS dbo_RefYearDefaults_1
INNER JOIN (dbo.RefYearDefaults
INNER JOIN ((dbo.Reference
INNER JOIN ((dbo.Contact
INNER JOIN (dbo.Address
INNER JOIN (dbo.ProductCost
INNER JOIN (dbo.Sponsor s
INNER JOIN (dbo.FDPSponsorApp
INNER JOIN ((((dbo.FDPEntity
INNER JOIN dbo.SponsorOrder so ON dbo.FDPEntity.EntityId = so.LocationId)
INNER JOIN dbo.SponsorOrderDetail sod ON so.OrderId = sod.OrderId)
INNER JOIN dbo.Product ON sod.ProductId = dbo.Product.ProductId)
INNER JOIN dbo.DeliveryPeriod ON (so.DeliveryPeriodId = dbo.DeliveryPeriod.DeliveryPeriodId)
AND (so.ProgramYear = dbo.DeliveryPeriod.ProgramYear)) ON (dbo.FDPSponsorApp.EntityId =
so.EntityId) AND (dbo.FDPSponsorApp.ProgramYear = so.ProgramYear)) ON s.SponsorId = dbo.FDPSponsorApp.SponsorId) ON
(dbo.ProductCost.ProgramYear = dbo.FDPSponsorApp.ProgramYear) AND (dbo.ProductCost.ProductId = dbo.Product.ProductId)) ON dbo.Address.AddrId =
dbo.FDPSponsorApp.MailAddrId) ON dbo.Contact.ContactId = dbo.FDPSponsorApp.FdpCtcId) INNER JOIN dbo.vwRef_fdpprogram ON so.ProgramId =
dbo.vwRef_fdpprogram.RefId) ON dbo.Reference.RefId = s.SFMSObjectTypeId) INNER JOIN dbo.Reference AS dbo_Reference_1 ON so.DeliveryTypeId =
dbo_Reference_1.RefId) ON dbo.RefYearDefaults.ProgramYear = so.ProgramYear) ON dbo_RefYearDefaults_1.ProgramYear = so.ProgramYear
WHERE (((so.ProgramYear)= 2014) AND --so.EntityId = @SponsorEntityId AND
((dbo.FDPSponsorApp.CurrentInd)=1) AND ((sod.QtyInv)>0) AND ((dbo.RefYearDefaults.DefNme)='BrownBoxFee')
AND ((dbo_RefYearDefaults_1.DefNme)='DirectDiversionFee')) ORDER BY so.EntityId, s.SponsorNme, so.OrderId, Product.ShortDsc
I would start from scratch using the SQL Server view builder to get the table in and the joins. SSMS may create something similar but it may create the joins in a non nested structure which is what you are looking for to make it a supportable syntax.
If nothing else works then winkle out the table hierarchy from the syntax and build it manually by putting the main transaction table into the from and manually adding te rest of the table required.
I would also consider creating some views to simplify the structure EG your SponsorOrderDetail could be turned into a view to include the product and sponsor details required for the query.
Never underestimate the power of human stupidity
Audience on my site is approximately 2-10 users online, database size 170MB, request 5-20/s. Does anyone advise me please, why the tempdb nonstop read / write to HDD for about 2-4MB/sec. Why, or what you need to set or to my tempdb. Thank you.
I have created a stored procedure with a while loop using a hard coded date 31-12-2014 to compare against current date produced in a while loop. I need to make the hard coded date reflect every two years i.e. 31-12-2016, 31-12-2018 etc.
How can the date be automatic instead of hard coded please?
i.e While (currdate <= '31-12-2014')
Second issue, how do I make the stored procedure to run every two years as well?
SQL Agent Job doesn't have a yearly frequency at all.
Can someone please offer an example coding and suggestions to those two issues which I will be grateful please.
As for the job not offering year - you are building a test method so a proc will run under only defined circumstances, so run the job every month, if it does not meet your criteria then let it wait for next month.
Never underestimate the power of human stupidity
DECLARE @LastDayOfYear Date, @GetHour INT , @NextNewDate DateTime
SET @LastDayOfYear = DATEADD(yy, DATEDIFF(yy,0,GETDATE()) + 1, -1)
SET @GetHour = datepart(hour, GETDATE()) -- returns hour
--Get Today date to compare to the last day of December in current year
IF (FORMAT(GETDATE(), 'yyyy-MM-dd') = FORMAT(@LastDayOfYear, 'yyyy-MM-dd')) AND @GetHour >= 20
--If Equal - then increase the next 2 years
SET @NextNewDate = FORMAT(DATEADD(YEAR, 2, @LastDayOfYear), 'yyyy-MM-dd HH:mm:ss')
SET @NextNewDate = FORMAT(@LastDayOfYear, 'yyyy-MM-dd HH:mm:ss')
WHILE (CONVERT(DATE,@currDate, 101) <= @NextNewDate)
Second Issue - "so run the job every month, if it does not meet your criteria then let it wait for next month."
How do you make it meet your criteria i.e. if it falls on 31st December of each year?
Ah I think I understand what you are saying, have the SQL Job Agent set up and each month - when it calls the stored procedure - if the dates don't match up - it needs to exit out of the SQL Job Agent?
I have also been googling and there were some examples where you could implement a job step code to do it? Not sure if this is feasible or a good idea?
Huge thanks to you for your patience. I know I am a bit persistent on this matter
Last Visit: 31-Dec-99 18:00 Last Update: 24-Jun-22 19:24