Jorgen, as usual... Thanks for the clear explanation :) Getting all the required rows after following your suggestion.

Hello In the query below, since I am using a LEFT JOIN I would expect all rows from podetm to be returned. This is true as long as the line that is commented out is not included. As soon as I include a predicate from the joined table, I seem to get only rows that exist in stockm in the result set. Why?

select * from scheme.podetm d
left join scheme.stockm s on s.warehouse+s.product = d.warehouse+d.product
where d.qty_received >0 and d.inv_value_posted =0
--and s.analysis_a not in ( 'LNG', 'INFANTRUST')

Thanks for your reply Richard. From W3.org it seems the file is possibly not 100% compliant

xml:stylesheet

should be

xml-stylesheet

But even so, I eventually got hold of the stylesheet which did not have any Schema info. Also tried DataSet.ReadXml as many posts on the net suggested where a schema was not available with no luck. Ended up reading the file with StreamReader.ReadLine and picking out the values I needed with SubString(...)

A few things: 1) Do you want to loop until (Bit + Ask) = 22? 2) The value of Bit,Ask and Bar1 are not changing from 0 3) If you want to display the value of Bar1 then you should put Console.WriteLine(Bar1.ToString()); (Console.WriteLine("Bar1") will display 'Bar1' and not the value of the variable Bar1)

for (int i = 0; (Bit + Ask) < 22; i++)
{
//Do something here to change the value of Bit and Ask - else infinite loop!!!
Console.WriteLine(Bar1.ToString());
}

Hi I need to populate a datatable with two fields from an xml file. and The file was obtained from a report that opens in a web browser, and the file saved from there.

DataTabel.ReadXml(XMLFilename)

fails (complains about the ':' character in Line 2 of the xml file.) I thought about using something like below, since I do have a complete list of the in another table, but the is not a child node of , and this also fails (complains about the ':' character in Line 2 of the xml file.)

public string GetKeyValue(string key)
{
System.Xml.XmlDocument xd = new System.Xml.XmlDocument();
xd.Load(_XMLFileName);
System.Xml.XmlNode Node = xd.DocumentElement.SelectSingleNode("/REPORTTABLE/REPORTTABLEDETAILS/PARTNUM add[@key=\"" + key + "\"]");

        if ((Node != null))
        {
            return Node.Attributes.GetNamedItem("value").Value;
        }
        else
        {
            return null;
        }
    }

Any idea how I can get a list of PARTNUM with the corresponding ACTUALQTYNUM?

2014/07/11 13:36:44
Fujiuser
Week_23_2014

260-C536000-PACE - Top_TLine 1 / Line 1 / Top / 1
260-C536000-PACE - Top_BLine 2 / VEK Line 2 / Bottom / 1
38
938-1030507938-1030507938-1030507
2
2
0
4
6249
6245

948-1025627948-1025627948-1025627
2
1
-1
40
6720
6680

Hi Nico Sorry that this is not an answer, but more out of curiosity.. Lets say a man joins the company. He gets an address ID. His wife who lives at the same address joins the company. With your app, you want to assign the same address ID to his wife. They get divorced. The man moves to another address, and tells the company his new address. You update that address ID with the new address and then his wife will have moved back in with him since they share and address ID?

Sorry - never read your question properly... (Not sure about DB2) but in any case... Perhaps UNION ALL is what you are looking for. (Columns in both selects must be the same) select p, q, r from XXX5T.MyDB5T.MyTable UNION ALL select p, q, r from XXX5T.MyDB5P.MyTable also see Linked Servers http://msdn.microsoft.com/en-us/library/ms188279.aspx[^]

Hi Richard Thanks for your reply. In my case here, a customer was requesting a Purchase Order in pdf format, as well as Excel format. I was doing the rounding with a formula in Excel, but the formula did not always fill down to all the rows of the table which is why I decided to do the rounding in the query. Also, quite often, in Winform apps, I pull a query into a DataTable, and set the DataTable as a DataGridView DataSource, in which case it seems more practical to do the rounding in the query as opposed to looping through the DataTable, and adding the rows with the rounded value to the DataGridView?

Hi Andrius Thanks So much. Tested below with the same random sample of 300 values, and all agreed with the Excel Values!

select price
, qty
, price*qty as Val
, round(CONVERT(decimal(12,4),price)* CONVERT(decimal(12,4),qty),3) as RoundVal
from dbo.[round]

What mis-lead me into thinking the problem was with the type of rounding was that from my sample of 300 values, all the values that differed had a 5 in the 4th decimal place, and all the errors were not rounded as expected. In the link you poseted: Using float and real Data The float and real data types are known as approximate data types. The behavior of float and real follows the IEEE 754 specification on approximate numeric data types. ... The IEEE 754 specification provides four rounding modes: round to nearest, round up, round down, and round to zero. Microsoft SQL Server uses round up. All are accurate to the guaranteed precision but can result in slightly different floating-point values. Because the binary representation of a floating-point number may use one of many legal rounding schemes, it is impossible to reliably quantify a floating-point value. Thanks for your help!

Hi I need to multiply two columns (float) each of which can have a max of 4 decimal places and round the result to 3 decimal places. It seems the ROUND(val,decimal_places) uses bankers rounding? (SQL Sever 2008R2) - Although I have read it uses Symmetric Arithmetic Rounding? Doing the same calculation in Excel gives me the result I need, but Excel uses normal Arithmetic rounding. How can I do a query to use arithmetic rounding (like Excel) Examples of the errors:

Price Qty Val ExcelRound SQLRound
7.5169 745 5600.0905 5600.091 5600.090
4.3465 463 2012.4295 2012.430 2012.429
1.6401 125 205.0125 205.013 205.012
0.0395 369 14.5755 14.576 14.575
5.2349 955 4999.3295 4999.330 4999.329
9.0285 141 1273.0185 1273.019 1273.018
9.0899 645 5862.9855 5862.986 5862.985
3.6167 215 777.5905 777.591 777.590
3.1145 135 420.4575 420.458 420.457
7.4115 105 778.2075 778.208 778.207
7.8675 313 2462.5275 2462.528 2462.527
8.8405 227 2006.7935 2006.794 2006.793
5.4269 55 298.4795 298.480 298.479
1.8833 445 838.0685 838.069 838.068
9.7349 655 6376.3595 6376.360 6376.359
8.6487 365 3156.7755 3156.776 3156.775
1.9033 125 237.9125 237.913 237.912
4.8197 545 2626.7365 2626.737 2626.736

PS: how do you post a table with nicely formatted columns?

Thanks Jorgen Unfortunately it not my database to add columns. I tried your query, but it was also slow, however that got me thinking more about the data I am querying. The vektronix table is actually quite large already after only a few months of production (about 8 rows for each unit produced) so I tried limiting the rows from vektronix in the where clause - that worked!

;WITH Dates AS
(
SELECT GETDATE()as DateValue
UNION ALL
SELECT DateValue -1
FROM Dates
WHERE DateValue -1 >= dateadd(d,-90,GETDATE())
)

SELECT convert(date,DateValue) as ProdDate , coalesce(trk.Tot,0)as ProdQty
FROM Dates D
left join (select convert(date,DateCreated) as TrkDate ,SUM(allocated) as Tot from vektronix.[dbo].[Tracking] t
where [TrackingTypeId] = '1' and DateCreated >= dateadd(d,-90,GETDATE())
group by convert(date,DateCreated))
as trk on trk.TrkDate = convert(date,DateValue)
group by DateValue,trk.Tot
order by datevalue desc
OPTION (MAXRECURSION 32747)

Thanks for your help!

Thanks Mycroft = a bit faster but still slow for a mere 90 rows. Bellow returns 10000 rows in under as second - so the problem must be in the second part

;WITH Dates AS
(
SELECT GETDATE()as DateValue
UNION ALL
SELECT DateValue -1
FROM Dates
WHERE DateValue -1 >= dateadd(d,-10000,GETDATE())
)
select convert(date,DateValue) from Dates
OPTION (MAXRECURSION 32747)

This executes in under a second (This is basically the output I want, but I also want days where there was zero production hence the CTE)

select convert(date,DateCreated) as TrkDate ,SUM(allocated) as Tot from vektronix.[dbo].[Tracking] t
where [TrackingTypeId] = '5'
group by convert(date,DateCreated)

With the change to date as opposed to a datestring nvarchar(10) as you suggested, below has reduced from about 17 second to 5 seconds. But to be that seems terribly slow for 90 rows?

;WITH Dates AS
(
SELECT GETDATE()as DateValue
UNION ALL
SELECT DateValue -1
FROM Dates
WHERE DateValue -1 >= dateadd(d,-90,GETDATE())
)

SELECT convert(date,DateValue) as ProdDate , coalesce(trk.Tot,0)as ProdQty
FROM Dates D
left join (select convert(date,DateCreated) as TrkDate ,SUM(allocated) as Tot from vektronix.[dbo].[Tracking] t
where [TrackingTypeId] = '5'
group by convert(date,DateCreated))
as trk on trk.TrkDate = convert(date,DateValue)
group by DateValue, trk.Tot
order by datevalue desc
OPTION (MAXRECURSION 32747)

Hi Mycroft. The dates are stored as dates in the database. How can I do the joins eliminating the time component because that is the reason I did all that converting to nvarchar(10) was to eliminate the time part of the dates

Hi Below query is taking 10 seconds to return 90 rows. Using cte to get the dates of the last 90 days, then summing the production for each day (Alloocated is production qty) and DateCreated is a DateTime value that the units were produced. So the output is the date of the last 90 days, and the total production for each day Can anyone point out what I am doing wrong?

;WITH Dates AS
(
SELECT GETDATE()as DateValue
UNION ALL
SELECT DateValue -1
FROM Dates
WHERE DateValue -1 >= dateadd(d,-90,GETDATE())
)
SELECT convert(nvarchar(10),DateValue,121) as ProdDate , coalesce(trk.Tot,0)as ProdQty
FROM Dates D
left join (select convert(nvarchar(10),DateCreated,121) as TrkDate ,SUM(allocated) as Tot from vektronix.[dbo].[Tracking] t
where [TrackingTypeId] = '5'
group by convert(nvarchar(10),DateCreated,121))
as trk on trk.TrkDate = convert(nvarchar(10),DateValue,121)
group by DateValue, trk.Tot
order by datevalue desc
OPTION (MAXRECURSION 32747)

Hi I had installed SSE2008 on a Small business 2008 server. There were two of these servers on the domain. The server I installed on was not the main server (if that makes sense) In any case, they reinstalled the main server, but now I cannot start the SQLSERVER agnet service. The way I understand it, when you install an instance of SSE, there are certain windows permissions set up, and now since they re-installed Small Business server, thers permissions are no longer valid. I can go into the SSE server using Management Studio, but my appplication cannot see the server. Previously whet the server was rebooted, my application also could not see the server, but then by starting the service: SQLAgent$SQLEXPRESS it worked. Any ideas how I can fix this? (I assume re-installing a new instance of SSE woulod work, since it would create new windows accounts? But this seems like the long way around and not a very elegant solution?)

GuyThiebaut wrote:

I take it there there is one central instance of an SQL Server rather than an instance on each workstation.
If there is one central instance then try to get it off the OS drive(probably the C: drive) so that disk access is not competing between OS calls and SQL Server calls - does that make sense?

Yes - one server instance on the server. Any yes I see what you mean. It makes sense, and I do remember reading something like that, but when I installed, I seemed to have missed where I could specify a different install location, so it installed on the C: drive (default).. At the moment there are way more serious performance hits in the app itself (populating too many datagrid rows at a time etc) - Only realized this when I got a copy of their data a year after the initial installation... I'd been testing with a bare bones DB on my machine - so yeah what those poor users have been going though - aw shame!

GuyThiebaut wrote:

I restore the backup to a test system(so yes a duplicate database) then I test a series of .Net apps

This should be easy enough to implement... I think.... (Well at least a duplicate database on the same server instance)

GuyThiebaut wrote:

As a DBA you want to make sure that when the SHTF you can get the systems back to working within an agreed SLA

Good point - the guy is a mate, but he should know what is a reasonable recovery time...

GuyThiebaut wrote:

I would strongly recommend an image backup every month with daily incremental image backups - disk space is cheap and rebuilding systems is expensive.

I will suggest that he implements this (I think he brings in a consultant to help out with servers etc)

GuyThiebaut wrote:

Another tip - if you are not running this all on a virtual machine, ensure that the SQL Server install is on a different disk to the software accessing it(this will further ensure its integrity and speed up access).

Not entirely sure what you mean here? The app is a standalone windows forms app, installed on the workstations, so I guess that should be fine. Thanks for the links - will have a look shortly

Hi Guy Thanks for your reply. I'm not working for the company, I only developed an app for their stock control. They don't have IT staff on site, and I'm really not sure what sort of backups they do on the servers. (They have two boxes both running Small Business Server - and I installed and instance of SSE for my app on one of them) All I want is to be able to restore the data for my application. Even if that means I install a new instance of SSE on another machine, and restore the data that was backed up. When you say you restore a backup weekly to test, where do restore the backup to? Have you got a duplicate database that you use to test the restore? What sort of things do you check to see that restore was successful - If it restores without Errors, check row counts on the tables?

Hi I have just changed the db for a small app from using an MS ACCESS db (terrible thing!) to SSE 2008 R2 DB. Previously, they used to just copy the ACCESS db file each evening to a flash drive, and keep that off site. They have been using the app for a year now, and the SSE backup file is only about 20MB - in other words not that much data to backup. The server instance only has one database, and one additional user, no views or stored procedures or anything like that 1) What exactly do I need to back up? The database, system databases (master, model, msdb etc) log files? 2) How should I do the backup? (Been thinking along the lines of a backup command that can be run from the app (SMO), or possibly having a table in the DB with last backup date/time, and if a user logs on, and the last backup is older that a day, it makes a new backup. Or is there a better way? Can you schedule a job on SSE to do this daily? 3) I'm also not sure what options to use with respect to: a) Backup Type (Full or Incremental?) b) Copy-only Backup (No?) c) Backup to existing medias set (yes) - Overwrite or Append? 4) The code I've tested using SMO Backup bkp = new Backup(); bkp.Action = BackupActionType.Database; bkp.Database = databaseName; bkp.Devices.AddDevice(fileName, DeviceType.File); bkp.Incremental = chkIncremental.Checked; bkp.SqlBackup(srv); Basically I want to end up with a backup (that can possibly be copied to some or other storage device as well for off site storage - flashdisk or portable drive) that in that case of the sever crashing completely, that I could restore to at worst a new instance of SSE? Am i on the right track here? Any other suggestions welcome!

Mycroft, I see original poster did not respond, but really curious - what does J-script convert '12r' to???