February 25, 2016 at 11:44 am
Hi
I have a SQL Server driven website and I have a very strange data issue I can't understand that I wonder if anyone can help me with.
My website was built years ago and worked with SQL Server 2000 and the host upgraded to SQL Server 2008 in the middle of last year, and it has worked fine since the upgrade.
I need to replace some of the data in my DB - the first time I've needed to do this since the SQL Server upgrade. How I've always done this is to prepare the data in an Access database and then use DTS to upload the new data table (Table A) from Access including the 2 fields (Company Identifier and Date) that are the key in the SQL Server table (Table B) in which the data is to be replaced. I then run a query to delete the data from Table B that is in Table A and then copy the new data from Table A to Table B. This has never ever given any problems at all.
I have followed this same procedure using SQL Server 2014 Import and Export Data. I encountered 2 problems. Firstly, this is time series data for companies so the key on the Access table is Date and Company. The import works fine if there is just one company in the Access table but I get an error if there is more than one. Part of the error info returned is
Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Destination - Histore.Inputs[Destination Input]" failed because error code 0xC020907B occurred
(Histore is Table A btw)
If I just have one company in the table the import works absolutely fine with no error so I think this may be something to do with keys. I only have 7 companies to upload so I can work round this for now doing one company at a time, but I'd be grateful for any thoughts on curing this as life isn't always so convenient..
My main problem though is that if I just upload one company and run the delete/copying procedure above and run a select query I can see the replaced data in the SQL Server table absolutely fine. However, for some strange reason the replaced data is not appearing in the chart on my website.
This is time series data for companies and the select query I'm running interactively simply selects based on the company identifier (CompID) and sorts by date. The data displays absolutely fine for the particular company (CompID 18006) and in date order. The query for the website selects by company identifier (CompID) and by date (greater than a particular date) but this is the only difference. To check out adding a date selection I ran this query in Query Analyzer to include a date selection (My PC is UK btw so hence CONVERT(DATETIME, '2015-04-30 00:00:00', 102).
SELECT dbo.TableB.*
FROM dbo.TableB
WHERE dbo.TableB.[Date] > CONVERT(DATETIME, '2015-04-30 00:00:00', 102) AND dbo.TableB.CompID = 18006
ORDER BY [DATE] DESC
and all the data displayed fine including the new data added and in the right order.
I wondered if maybe it was something to do with differences in the date fields in the 2 tables so I checked out the properties of each. The only differences are that in Table A after the upload, Date is not a primary key and Nulls are allowed whereas in Table B Date is a primary key and Nulls are not allowed. Also in Table B there is a default date value of (convert(char(10),getdate(),101)) but I think this is irrelevant as all rows in Table A have dates. I also checked out the CompID field in the 2 tables. They have identical properties with the exception that in Table A after the upload, CompID is not a primary key and Nulls are allowed whereas in Table B CompID is a primary key and Nulls are not allowed. Both Table A and Table B are data type smallint.
I'm at a loss to understand why a process which I've done dozens of times before with no problem at all should now be causing problems since the upgrade from SQL Server 2000 to 2008. Why would the data be appearing and be selectable in the database but not be selected by the query for the website (which triggers a query in the database btw)?
One minor wrinkle on this is that when I set this up about 16 years ago I didn't appreciate the dangers of using system variables to name fields in the table - we all learn(!) - so one of the fields in the destination table is called "Date" but in the query to copy I explicitly reference the field name with square brackets so I think that should be ok. Certainly, the data seems to copy across absolutely fine. In the uploaded Table A, the date field referred to above is actually called MyDate
Other angles (maybe useful background) while struggling with the SQL Server 2014 Import and Export Data error, I tried uploading the data from an Excel spreadsheet. This worked successfully and when I copied the data into Table B I got this identical problem of the replaced data not being displayed. Another thing is that the PC I work on is UK date format and I'm uploading the data from a PC with Canada as region.
Does anyone have any thoughts on this? Thanks for any help, I've pretty much run out of ideas here..
Sorry it's such a long post!
Andrew
February 25, 2016 at 12:17 pm
Just read my post through again and I should have added that the error referenced in the part about importing data with SQL Server 2014 Import and Export Data only happens if there is more than one company in Table A!
February 25, 2016 at 12:34 pm
The data exists in the SQL Server database since you can query it okay. Is that correct?
If so, then the issue isn't with inserting the data. It's there, it's just that you can't view it via the interface you are using.
That indicates one of three things (at least that I can think of).
First possibility: Your interface isn't querying the table. It might be querying a view instead. If so, views can get out of sync with the table(s). You should rebuild the view...this is something that is often overlooked:
From the Books On Line: If the new table or view structure changes, the view must be dropped and re-created.
Second possibility: Your interface has the data cached and is displaying old data.
Third possibility: Your interface isn't connecting to the database you think it is....do you have a dev or qa or a data warehouse server with the same database on it that might have the old data and that your interface is pointed to?
-SQLBill
February 25, 2016 at 12:36 pm
As for the inserting of data....is there a trigger on the table that might be keeping you from inserting more than one row at a time?
-SQLBill
February 25, 2016 at 1:30 pm
Hi SQLBill
thanks very much for your thoughts on my problems..
I think I have to agree with you that the data is in the database as I can query it. My only thought was that there might be some formatting issue that is misleading. Eg/ a company ID of 18006 might appear that way, but in fact is stored as 18005.9996 but just displayed as 18006. This couldn't be the case as I did a select query on the DB for CompID 18006 and date >30th April 2015 and the data all displayed. I checked out the specs of the Date and CompID tables and they're pretty much identical in tables A and B. I think this has to mean that the data is copied unadulterated into Table B. I think the other thing that says the data uploaded is unadulterated is that I was able to use the CompID and Date in Table A in a query to delete from Table B so for that to find data to delete they would have to be identical, right?
You mention that the interface may not be querying the table but a view instead. I can add a piece of evidence here that other companies whose data has not been replaced display absolutely fine. Everything updates daily on the site and for other companies all the data displays perfectly right up to yesterday. If there was a problem with the view wouldn't that affect companies that hadn't had the data replaced too? I haven't actually changed anything in the structure of Table B, I just deleted data and replaced it.
I checked out whether my browser was maybe caching the page data by using another computer to go to the website and there's still the problem.
I'm going to contact my host and ask about your second and third possibilities. With so much on the cloud these days I wonder if somehow the SQL Database is "virtualized" and dispersed in the cloud and the website maybe isn't seeing the refreshed new data somehow.
February 25, 2016 at 1:44 pm
Hi SQLBill
I looked through the database and there are no views referenced by the website, it uses queries.
February 25, 2016 at 4:54 pm
I got a reply from my host's tech support and they told me they couldn't think of any caching issue that could be causing the problem.
Regarding referring to a different database, he asked me what connection string IP I was using to connect the website to the DB and it turned out to be different from the one I use to manage the DB. Anyway, it turns out that they migrated to a new IP on the DB but left the old one in place as a legacy so the website does point to the same DB as I'm managing.
February 26, 2016 at 6:08 am
Hi
I have another thought about my "invisible data" problem..
My DB had to be restored about 10 years ago and I found that objects created were no longer dbo. but the login name, eg/
MyLogin.MyTable
rather than
dbo.MyTable
I had to do a bit of rushing around before I could get the website working again. But it's worked fine since.
I just moved to SSMS and I noticed that all the objects it creates, including the upload of Table A are all dbo's. So I wonder if my data is invisible to my website because it's set up to use the MyLogin schema whereas the data was uploaded under the dbo schema and is therefore invisible to the MyLogin schema?
Dunno... long shot, but everything else is weird!
Thanks for any feedback.
February 26, 2016 at 11:40 am
Andrew Bagley (2/26/2016)
HiI have another thought about my "invisible data" problem..
My DB had to be restored about 10 years ago and I found that objects created were no longer dbo. but the login name, eg/
MyLogin.MyTable
rather than
dbo.MyTable
I had to do a bit of rushing around before I could get the website working again. But it's worked fine since.
I just moved to SSMS and I noticed that all the objects it creates, including the upload of Table A are all dbo's. So I wonder if my data is invisible to my website because it's set up to use the MyLogin schema whereas the data was uploaded under the dbo schema and is therefore invisible to the MyLogin schema?
Dunno... long shot, but everything else is weird!
Thanks for any feedback.
That could very well be the issue.. dbo.testtable and sqlbill.testtable are two different objects. That is why a 'best-practice' is to always include the schema when creating objects and when referencing them.
-SQLBill
February 26, 2016 at 12:36 pm
Hi SQLBill
thanks for getting back on this.
To put some precision around this..it isn't actually that there are 2 tables with identical names under different schema.
As I mentioned, before the DB was restored about 10 years ago, any user-created object was a dbo. So a new table would be
dbo.MyNewTable
After it was restored, new tables created would be
MyLogin.MyNewTable
The tables that are accessed by the website are all dbo.* tables created before the restore.
My database takes automated daily uploads from a local access database using DTS and all uploads go into MyLogin.* tables. Data is then copied manually with stored procedures from the MyLogin.* tables into the dbo.* tables. This has all worked extremely reliably for many years now.
The SSMS and SQL Server 2014 Import and Export Data create dbo.* objects and it is in copying from these that I get my data invisibility problem. Note the distinction, uploading with dts through the MyLogin.* tables works fine but using SQL Server 2014 Import and Export Data create dbo.* gives me the invisible data problem. In fact, as a workaround on this problem I sort of rehashed the data I'm trying to replace and pushed it through the DTS/MyLogin.* tables route that I normally use for the automated data uploads and the data appears absolutely fine (took me about 100 times as long as usual though and I'm still not finished!).
So my point is that when the data is uploaded into MyLogin.* tables and copied from there into the dbo.* tables used by the website, the data is visible, but when I use SQL Server 2014 Import and Export Data it is loaded into new dbo.* tables and when I copy from there into the dbo.* tables used by the website it is invisible.
I wonder if maybe I can get SSMS and SQL Server 2014 Import and Export Data to sign onto schema MyLogin rather than dbo and maybe that will cure my problem. Is that possible?
I hope that's all clear, it's bizarre and confusing!
February 29, 2016 at 9:10 am
Bottom line...no. DBO schema is the 'everyone' schema. Mylogin schema is owned by Mylogin (so the sqlbill schema is owned by me). For anyone else to accessw mylogin schema, you have to grant them permission to so, but they can't log in as mylogin. They can only access it by using the two part naming convention mylogin.objectname. That would require you to update any objects that use a o schema'd object. Otherwise it will default to dbo schema. For example, call a store proc and if it only has select * from tablename...it will select it from dbo.tablename.
I think the easiest solution would be to rename the objects to the dbo schema.
-SQLBill
February 29, 2016 at 9:14 am
Hi SQLBill
thanks for the thoughts. Hmm, I need to mull this over. My concern is that I don't want to disable the website's access to the tables!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply