1

I have a windows service application that imports a CSV file. During the import process, the application runs a series of queries to the database. During stress testing, we tried importing a CSV with 40k records. That means at least 40k queries to the database. Normally the queries run fine but there are times that the application would randomly get an error from the db " Invalid object name 'TableName' ".

Any idea what could be causing the error? I know that it's not in the query since the query executes successfully most of the time.

This is the actual query that I captured using the Text Visualizer. This runs successfully in SSMS

SELECT c.MemberInternalKey, 
       c.ClubCardId, 
       c.RestrictionId, 
       c.ExpirationDate, 
       m.ExternalMemberKey, 
       m.BuyingUnitInternalKey, 
       b.ExternalBuyingUnit,  
       b.PostalCode, 
       b.Country, 
       b.Street1, 
       b.City, 
       b.HomePhone, 
       b.EmailAddress, 
       b.SendEmail, 
       m2.ExternalMemberKey as OldestExternalMemberKey, 
       m2.BirthDate, 
       m2.MobilePhoneNumber, 
       m2.WorkPhoneNumber, 
       m2.Gender 
FROM dbo.CRM_Clubcard c 
INNER JOIN dbo.CRM_Member m ON c.MemberInternalKey = m.MemberInternalKey 
INNER JOIN dbo.CRM_BuyingUnit b ON m.BuyingUnitInternalKey = b.BuyingUnitInternalKey 
INNER JOIN dbo.CRM_Member m2 ON m2.BuyingUnitInternalKey = m.BuyingUnitInternalKey 
WHERE c.ClubInternalKey = 2 
AND c.ClubCardId  = '1004303119' 
AND m2.IsMainMember = 1
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
darwin25
  • 11
  • 4
  • What is the timeframe in which these 40k queries are executed? – Daniel Sep 01 '16 at 06:45
  • Hi Daniel. It's about 2 minutes – darwin25 Sep 01 '16 at 06:46
  • Are you getting error each time when you try to import same 40k records, or only sometimes? – Low Flying Pelican Sep 01 '16 at 06:49
  • Only sometimes. When we tried importing the same 40k records again, We did not get any error. It just happens randomly – darwin25 Sep 01 '16 at 06:51
  • Did you try to run Profiler and see what's happening? – Anton Sep 01 '16 at 06:52
  • Can you post example of a query? – Anton Sep 01 '16 at 06:53
  • I will ask the db admin to do this. Thanks Anton – darwin25 Sep 01 '16 at 06:54
  • @Anton, I have edited my original question to post the actual query that I got from the Text Visualizer. This query runs successfully in SSMS – darwin25 Sep 01 '16 at 07:05
  • I'm confused. I thought that as it is import process, it should INSERT records, not select. Also it's good if you find the query that is failing, it can be captured by profiler. – Anton Sep 01 '16 at 07:15
  • sorry for the confusion. The application is actually connecting to 2 separate databases. 1 database is used to query some values before importing into another database. – darwin25 Sep 01 '16 at 07:25
  • Thanks for the tip. I already asked the client to run profiler in their db server. – darwin25 Sep 01 '16 at 07:26
  • Thanks for the edit @Zohar Peled – darwin25 Sep 01 '16 at 07:57
  • Is it possible that somebody else from your org drops and recreates the `TableName` for their own purpose? – Anton Gogolev Sep 01 '16 at 08:16
  • I'm not sure. This database that the application is querying is from another vendor of the client. A remote possibility and I don't know why would it be the case, but maybe that is a possibility. I'm gonna ask the client about this just to rule this out – darwin25 Sep 01 '16 at 08:55
  • I finally got access to the server and ran the profiler on both databases and see what is actually happening. Somehow, a query intended for one database get's sent to the other database and vice versa. WEIRD. I checked the connection strings and they are both correct. The profiler shows the query being executed in the correct database most of the time but curiously get's sent to the other database causing the error 208 – darwin25 Sep 02 '16 at 01:51
  • I assume, the CSV contains a list of tables and maybe columns and so on required for your queries? My first guess would be, that the CSV contains a Header Row and when the Query tries to read from the table "TableName", the query Fails... – Tyron78 Sep 02 '16 at 07:00

1 Answers1

0

I have fixed the issue by re-initializing the connection string for every method call / query to the database. Running the profiler was a big help.

darwin25
  • 11
  • 4