February 3, 2009 at 8:59 am
Hello
Within the SQL Profiler I see multiple entries of the same line
CREATE TABLE ##nnnnnnn (then the column names) - duplicated numerous times, when I step through each one I notice the structure of the table changes but it has the same table number ##nnnnnnn, also if I run in T-SQL - SELECT * FROM ##nnnnnnn its got the layout of the first CREATE.
can anyone suggest why it attempts to create the TEMP table numerous times also fact it attempts to create a TEMP table with a different layout using the same temp table number. Its SQL 2005 ver 9.00.3042
Thanks in advance
Malc
February 3, 2009 at 10:10 am
What code is creating this global temp table? Any process trying to create another global temp table with the same name should error out with an error like this:
Msg 2714, Level 16, State 6, Line 1
There is already an object named '##test' in the database.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 4, 2009 at 2:41 am
Jack
Its a Microsoft programming language called DEXTERITY which is used within Microsoft Dynamics software. We have 3rd party code bolted onto this. We use the same code elsewhere with no issues so it looks as thought its site specific - the application resides on CITRIX. We are also not sure if its a network style issue. When I turned on the Tracing tool within the ODBC setup this slowed down the server and the issue was not easily replicated. The issue seems to occur with Windows (forms) that contain a large amount of TEMP tables. When you quickly step through these records using the browse buttons a large volume of TEMP tables are being created and the application appears to get out of step - I think DEXTERITY expects a Temp table to be one format when its a different format and it causes a Dexterity runtime error. The error message you mentioned is not shown. In other instances the application crashes when saving a new record. We also cannot replicate this in -house using same DEXTERITY code but we are not using CITRIX.
Thanks for the reply.
Malc
February 4, 2009 at 6:18 am
I don't have any experience with Dyanamics or Dexterity so I'm not sure I can be of anymore help. I suppose it could bethe use of Citrix is causing a connection to be re-used by multiple clients, but I don't know how that would work either. Are you seeing the same SPID being used for these connections?
Are the tables really ##tablename or #tablename?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 4, 2009 at 6:38 am
Jack
thanks for the help so far. Its bthe sma SPID. Do you know how SQL 2005 allocates the temp table name - from the profiler, some appeared to end in the same last 4 digits - 4211 - time of this was 17:11:42 - not sure is this is a coincidence.
I have copied details from the profiler - first one shows the temp table being created, later on the code thinks the layout of the table is different and attempts to use the stored procedure to do an insert but its the wrong format.
CREATE TABLE ##0624211 (DivisionID char(21) NOT NULL, DepartmentID char(21) NOT NULL, MvmntInstructNo int NOT NULL, TradingLineNumber
smallint NOT NULL, CounterPartyNumber char(15) NOT NULL, TransMode smallint NOT NULL, SourceID char(11) NOT NULL, DestinationID char(11) NOT
NULL, DeparturePoint char(21) NOT NULL, ArrivalPoint char(21) NOT NULL, EstDateArrival datetime NOT NULL CHECK (DATEPART(hour,EstDateArrival)
= 0 and DATEPART(minute,EstDateArrival) = 0 and DATEPART(second,EstDateArrival) = 0 and DATEPART(millisecond,EstDateArrival) = 0),
DepartureDate datetime NOT NULL CHECK (DATEPART(hour,DepartureDate) = 0 and DATEPART(minute,DepartureDate) = 0 and
DATEPART(second,DepartureDate) = 0 and DATEPART(millisecond,DepartureDate) = 0), DepartureTime datetime NOT NULL CHECK
(DATEPART(day,DepartureTime) = 1 and DATEPART(month,DepartureTime) = 1 and DATEPART(year,DepartureTime) = 1900 ), EstTimeArrival datetime
NOT NULL CHECK (DATEPART(day,EstTimeArrival) = 1 and DATEPART(month,EstTimeArrival) = 1 and DATEPART(year,EstTimeArrival) = 1900 ),
ActDateArrival datetime NOT NULL CHECK (DATEPART(hour,ActDateArrival) = 0 and DATEPART(minute,ActDateArrival) = 0 and
DATEPART(second,ActDateArrival) = 0 and DATEPART(millisecond,ActDateArrival) = 0), ActTimeArrival datetime NOT NULL CHECK
(DATEPART(day,ActTimeArrival) = 1 and DATEPART(month,ActTimeArrival) = 1 and DATEPART(year,ActTimeArrival) = 1900 ), ContainerNo char(11)
NOT NULL, ContainerDesc char(21) NOT NULL, Volume numeric(19,5) NOT NULL, ShipmentOwner char(31) NOT NULL, RailConsignmentNote char(15) NOT
NULL, RouteNo char(31) NOT NULL, RailWagonNumber char(31) NOT NULL, RailWaybillNo char(21) NOT NULL, SealNumber char(21) NOT NULL,
VehicleNumber char(15) NOT NULL, VehicleRegistration2 char(21) NOT NULL, HaulageCompany char(31) NOT NULL, Airline char(15) NOT NULL,
AirwayBill char(31) NOT NULL, FlightNumber char(15) NOT NULL, PlaneNumber char(15) NOT NULL, HandlingCompany char(31) NOT NULL, AgentIATANo
char(31) NOT NULL, VesselNumber int NOT NULL, VesselID char(21) NOT NULL, ShippingCompany char(31) NOT NULL, PortLayDate1 datetime NOT NULL
CHECK (DATEPART(hour,PortLayDate1) = 0 and DATEPART(minute,PortLayDate1) = 0 and DATEPART(second,PortLayDate1) = 0 and
DATEPART(millisecond,PortLayDate1) = 0), Carriageby char(31) NOT NULL, ExportReference char(31) NOT NULL, RoutingReference char(31) NOT NULL,
Reference char(31) NOT NULL, LocationMovementNum char(31) NOT NULL, TransCompletedby char(21) NOT NULL, Weight numeric(19,5) NOT NULL,
MovementUnitID char(11) NOT NULL, MovementUserDefined1 char(51) NOT NULL, MovementUserDefined2 char(51) NOT NULL, MovementUserDefined3
char(51) NOT NULL, MovementUserDefined4 char(51) NOT NULL, MovementUserDefined5 char(51) NOT NULL, MovementUserDefined6 char(51) NOT NULL,
VesselBrokerID char(15) NOT NULL, VesselBrokerIndicator smallint NOT NULL, VesselBrokerAmount numeric(19,5) NOT NULL, VesselBrokerContact
char(41) NOT NULL, VesselBrokerCurrencyID char(15) NOT NULL, VesselSellCounterPartyID char(15) NOT NULL, StockCurrency char(15) NOT NULL,
WeightBasisID char(11) NOT NULL, OptionBasisID char(11) NOT NULL, CounterPartyShortname char(31) NOT NULL, VesselCharterPercentTol1
numeric(19,5) NOT NULL, VesselCharterPercentTol2 numeric(19,5) NOT NULL, VesselCharterQtyTol1 numeric(19,5) NOT NULL, VesselCharterQtyTol2
numeric(19,5) NOT NULL, VesselBrokerOnCostID char(11) NOT NULL, PortLayDate2 datetime NOT NULL CHECK (DATEPART(hour,PortLayDate2) = 0 and
DATEPART(minute,PortLayDate2) = 0 and DATEPART(second,PortLayDate2) = 0 and DATEPART(millisecond,PortLayDate2) = 0), DEX_ROW_ID int identity
NOT NULL, TransText text NOT NULL)
----------
BEGIN DECLARE @num int EXEC ##zDP_0624211SI 'FARM', 'FARM', 100007, 'CLAR199', 1, '', 'ENGLAND', 'IPSWICH', 1, 1, @num OUT SELECT @num END
Regards
Malc
February 4, 2009 at 6:51 am
Global temporary tables (##table_name) are named in the application. Temporary tables (#table_name) get the name given by the application plus an identifier by SQL Server. You can see this by running this code:
CREATE TABLE ##Test (id INT)
CREATE TABLE #test (id INT)
GO
SELECT name FROM tempdb.sys.tables
DROP TABLE ##test
DROP TABLE #test
In your Profiler trace add the Exception and User Error Messages events to your trace so you can see if the error I mentioned earlier is being created by SQL Server and then handled within the Dynamics application.
It sounds like the explicit Drop Table statement that is required when using Global Temp tables is not being created.
You probably need to get some help from a Dynamics expert as, I believe, the problem lies there and not within SQL Server.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 4, 2009 at 9:14 am
Jack
Looks like you have found the issue. I altered the SQL Profiler settings and I see the error message regarding duplicate table names. I spoke to our Dexterity developers, they dont check to see if a TEMP table has been created properly. It tries to create the table a set number of times then gives up - the temp table still remains in SQL TEMPDB as its not actually dropped - this compounds the issue. Since there are quite a few users on this system (40) and the combination of temp tables being created by the application theres bound to be a clash.
Thanks for your help.
Malc
February 4, 2009 at 9:28 am
Perhaps instead of Global Temporary Tables (##) they could use Local temporary Tables (#).
February 4, 2009 at 9:29 am
Great, glad I could help.
I kind of held back on this question, but I have to ask since your developers are doing it.
Why are they using Global Temp tables?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 4, 2009 at 9:43 am
Jack
I dont really know - should they just be using other type of temp tables ?
The developer said once they created a global table, it returned a reference (presumabley) the ##nnnnnn number so they could pass this from one form to another - not sure if this makes any sense.
I noticed tables in TEMPDB with just the one '#', I think these may be created from the Dynamics Financial standard package.
Should I suggest they dont use 'Global' tables and swap to other ones ?
Regards
Malc
February 4, 2009 at 9:52 am
Based on your last comment, local temporary tables may not work. They are only good for the current session, and if the table name is being passed back to the web app, the session to the database may not be kept.
In this case, they need to do a little more to make sure the global temporary tables have unique names across multiple executions.
February 4, 2009 at 9:59 am
Lynn Pettis (2/4/2009)
Based on your last comment, local temporary tables may not work. They are only good for the current session, and if the table name is being passed back to the web app, the session to the database may not be kept.In this case, they need to do a little more to make sure the global temporary tables have unique names across multiple executions.
Lynn is right.
It really sounds to me like they are doing something that can be done and "works", but probably is not the best way to do it. Without knowing all the needs of the application I can't really make any other suggestions.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply