April 12, 2012 at 9:43 am
phood (4/12/2012)
Okay, I've done so many different things I'm starting to get myself confused so let me make sure I have it down.here is my select command with the join:
select [sqlaliasname].meter_data_dmd_time, [sqlaliasname].meter_data1, [sqlaliasname].station, [sqlaliasname].meter_data_meter_id
FROM [SQL Real Table Name] as [sqlaliasname]
LEFT JOIN [Access Real Table Name] [accessaliasname]
ON ([accessaliasname].station=[sqlaliasname].station)
I just used the [name] to keep things straight so I hope it helps.
First, do I have the naming correct for the Join? Second, should I join on both PK columns or is one sufficient?
By doing it this way, I am rid of the ambiguos column name errors, however, the application hangs and I have let it go for as long as 2 or 3 minutes before stopping it. I queried my SQL table and no records were entered. The SQL table is currently empty so importing new records (about 3000) should take a second or two at best. If all the rows were duplicates then it was taking about 20 seconds to "skip over" the duplicates doing it the other way. I think you're getting there and likely the problem is my own confusion and I appreciate your patience. :hehe:
You should definitely join on the entire PK and not part of it. You should also add a where clause
where RealTable.AnyNonNullableColumn is null
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 12, 2012 at 9:45 am
And if you are doing this as a single insert and you cancelled it, the implicit transaction would have been rolled back so there would be no data. If you want to see what queries are running you should fire up a sql trace. If you are not familiar with that you can use SQL Profiler.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 12, 2012 at 9:57 am
When I add the NULL part I get this:
The multi-part identifier "meter_data_tablev2.meter_data_dmd_time" could not be bound.
I tried both REAL names for Access and SQL and get the same error. When I use either alias name I get an application hang.
Here is what I have:
SELECT sqlalias.meter_data_dmd_time, sqlalias.meter_data1, sqlalias.station, sqlalias.meter_data_meter_id FROM meter_data_tablev2 AS sqlalias LEFT JOIN meter_data_table accessalias ON (accessalias.station=sqlalias.station and accessalias.meter_data_dmd_time = sqlalias.meter_data_dmd_time) WHERE [tried 'em all].meter_data_dmd_time IS NULL
I removed all references to other string variables of sql tables and wheres and just made my entire sql command strSQL = to the above statement.
April 12, 2012 at 10:16 am
Looked at the profiler, lots of stuff going on in there with my web pages I had not realized. Anyway, I saw repeated rows of BatchStarting and BatchCompleted with some text data that really didn't identify an "issue" it seems. When I let the app go it finally timed out or whatever and got this message:
The CLR has been unable to transition from COM context 0x1b75f0 to COM context 0x1b7760 for 60 seconds. The thread that owns the destination context/apartment is most likely either doing a non pumping wait or processing a very long running operation without pumping Windows messages. This situation generally has a negative performance impact and may even lead to the application becoming non responsive or memory usage accumulating continually over time. To avoid this problem, all single threaded apartment (STA) threads should use pumping wait primitives (such as CoWaitForMultipleHandles) and routinely pump messages during long running operations.
Here is what I was getting in the trace:
SET FMTONLY OFF; SET NO_BROWSETABLE ON; SET FMTONLY ON;select sqlalias.meter_data_dmd_time, sqlalias.meter_data1, sqlalias.station, sqlalias.meter_data_meter_id from meter_data_tablev2 as sqlalias left join meter_data_table accessalias on (accessalias.station=sqlalias.station and accessalias.meter_data_dmd_time = sqlalias.meter_data_dmd_time) where sqlalias.meter_data_dmd_time is null SET FMTONLY OFF; SET NO_BROWSETABLE OFF;
Sean Lange (4/12/2012)
And if you are doing this as a single insert and you cancelled it, the implicit transaction would have been rolled back so there would be no data. If you want to see what queries are running you should fire up a sql trace. If you are not familiar with that you can use SQL Profiler.
April 12, 2012 at 10:50 am
I had forgotten that I had continueonupdateerror=true in there so I turned that off and maybe now we can see the issue. I tried it and quickly got this error:
System.InvalidOperationException: Dynamic SQL generation is not supported against multiple base tables.
Any ideas on this one?
April 12, 2012 at 10:55 am
Are you calling the Update method of your sqladapter? The update will not work when there is more than 1 base table. You have to execute your dynamically built sql directly instead of using the sqladapter.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 12, 2012 at 12:56 pm
Yes the Update is what I have been using. It was part of the original stuff I copied and it's purpose according to the author was to get it to build the insert command automatically. When I used a text box to see what that insert command is, it was something like "insert blah values @param_1, @param_2, @param_3, @param_4 blah blah blah" What I couldn't figure out, to do it manually myself, was how the heck this Update thing came up with parameters. So to do it manually I would need to do an sqldataadapter.insertcommand = ...something and do that "for each row" I assume? I tried working with that sometime earlier, but I hadn't tried real hard to get it working.
Sean Lange (4/12/2012)
Are you calling the Update method of your sqladapter? The update will not work when there is more than 1 base table. You have to execute your dynamically built sql directly instead of using the sqladapter.
April 12, 2012 at 12:58 pm
Well it sounds like you have now done all the work to create the sql. Give it a try, I think you are closer than you think.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply