| Author |
Topic |
|
macca
Posting Yak Master
146 Posts |
 Posted - 2012-09-08 : 09:39:30
|
| I am doing an Export of Data and exporting to Excel.I am using a Select Statement which is retrieving data from a number of fields within a number of tables using Joins.The problem is that in one particular field in a certain table only some of that dat from the field is being exported to Excel. Data that is actually in the field is not exported.If I run it again, some of the data that had previously not been exported is now exported but data that previously had been exported is now not exported.I can't seem to figure out why this is.Any ideas? |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-09-08 : 12:46:10
|
| If you run the query in SSMS, do you always get the same values in that field and same number of rows ? If you are using no lock hints or if it is a "hot" column (values changing frequently) you may not get the same results always.Assuming that none of that is the case and that when you run it in SSMS, you get the same rows, how are you exporting to Excel? If you are using SSIS, what is the data type of the destination column? If it is numeric and the column happens to have non-numeric data, it can end up exporting only the numeric data.If you have not specified the data type - for example if you were using import/export wizard, and if the first few rows happened to be numeric, Excel might interpret the column as being numeric (although that is a guess on my part. I have seen that happen when you IMPORT data into SQL - don't know if that same anomaly can happen when you export). |
 |
|
|
macca
Posting Yak Master
146 Posts |
Posted - 2012-09-08 : 14:35:24
|
| Thanks for the reply sunitabeck.I am using the Import/Export wizard and am not specifying the row type. The row that I am exporting is alpha characters only. I am completely stumped as to why this is happening and I can't see any pattern in teh data that is not being exported. It is just one field out of many that this seems to be happening to. No idea why though. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-09 : 13:04:48
|
| is that unicode data by any chance?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
macca
Posting Yak Master
146 Posts |
Posted - 2012-09-10 : 10:30:23
|
| Here is the code:SELECT D.Title, D.[First Name] + ' ' + D.Surname AS AppName, S.Status, S.[Status Date],R.Amount Rq, R.[Euro Amount Recouped], R.[Date Rec], R.[Difference]FROM [tblApp Details] AS D LEFT OUTER JOIN [tblApp Stage] AS S ON D.Reference = S.Reference LEFT OUTER JOIN [tblRec] As R ON D.Reference = R.ReferenceWHERE (D.Applied > CONVERT(DATETIME, '2007-01-01 00:00:00', 102))Status is a nvarchar(50)Status Date is smalldatetime |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-10 : 10:34:49
|
| did you try specifying IMEX=1 in excel connection string?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-09-10 : 10:43:23
|
Although not necessarily a fix to the problem you are experiencing, if First Name or Surname are nullable columns, you should change the select for Appname to:COALESCE(D.[First Name],'') + ' ' + COALESCE(D.Surname,'') AS AppName, |
|
|
|
macca
Posting Yak Master
146 Posts |
Posted - 2012-09-10 : 10:46:43
|
| Thanks sunita, but I got this sorted. A silly mistake on my part. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-11 : 11:16:57
|
quote:
Originally posted by macca
Thanks sunita, but I got this sorted. A silly mistake on my part.
for sake of others can you specify what it was?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
macca
Posting Yak Master
146 Posts |
Posted - 2012-09-12 : 09:58:43
|
| I'm too embarrassed Visakh16.I have two tables in the DB with very similar names(one character difference). One of the tables in my query was the incorrect table.Silly mistake. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-12 : 11:24:28
|
quote:
Originally posted by macca
I'm too embarrassed Visakh16.I have two tables in the DB with very similar names(one character difference). One of the tables in my query was the incorrect table.Silly mistake.
Oh okthats fine...dont feel embarrassedit happens to all  ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
Select not Retrieving all Data from Field