Share via

tmp_ms_ getting appended in temp table while creating deployment script from SSDT

  • Article

I am Sandeep Chalke from Microsoft Data Access Support Team. My team deals with issues that arise while accessing data from SQL Server.

I hope many of you should be acquainted with SSDT (SQL Server Data Tools).  If not, please visit https://msdn.microsoft.com/en-us/data/tools.aspx  for details.

Two weeks back I faced an intriguing issue related to SSDT. One of my customer opened a support ticket  in regards to poor performance with SSDT while deploying the database to SQL Server using the SSDT generated deployment script.

The customer was using SSDT from Visual Studio 2013. When SSDT created the deployment script, a lot of temporary tables with suffix "tmp_ms_XXX_" were seen.

For example:                                                                                        

CREATE TABLE [dbo].[tmp_ms_xx_DBP_Table]

INSERT INTO [dbo].[tmp_ms_xx_DBP_Table]

SELECT [col1],[col2]....

FROM [dbo].[DBP_Table]

DROP TABLE [dbo].[DBP_Table]

EXECUTE sp_rename N'[dbo].[tmp_ms_xx_DBP_Table]', N'DBP_Table';

The above script was seen for every table in the database. Now, this behavior is expected, if the schema of the underlying table is changed. But, in this scenario that was not the case. Even though the underlying table was unchanged, SSDT would create script with tmp tables.

After spending lot of time experimenting with the properties of SSDT project, I could nail down the issue.

Every SQL file included in the SSDT project has few properties, like "ANSI Null", "Quoted Identifier" etc. which are set to "Project Default" by default.

File Properties in the SSDT project for the SQL file

clip_image002

When we try to "Genearte Script", the same properties are seen in the "Advanced Properties". If the value of the properties set for the SQL file is different than the one in the "Advanced Properties", SSDT will create tmp tables.

Database in SQL Server

Advanced Properties while Publish/Generate Script of SSDT project.

clip_image004

clip_image006

Author : Sandeep(MSFT), Support escalation engg, Microsoft

Reviewed by : Snehadeep(MSFT), SQL Developer Technical Lead, Microsoft

Comments

  • Anonymous
    September 30, 2014
    Snehadeep, We have an issue with one of our customers that need immediate attention. I'm working with Microsoft point of contact for our account. I requested to speak to you to help with an issue that you resolved with an MSDN article (title :SQL BULK COPY ERROR (“Operating system error code 5(Access is denied.) Is there a way to contact you?  My email tocertain at gmail com _Uday

  • Anonymous
    May 12, 2015
    Good article