MS SQL Export does not create INSERT statements

[expired user #9601]'s profile image [expired user #9601] posted 8 years ago in Import/Export Permalink

Hello all,

Working to extract data from a view in MS SQL, and I can't seem to get it to create the INSERT statements that contain the data stored within the view. I have permission to view the View, and normal SELECT * FROM queries work as expected in the Query Tab. I do not, however, have permission to the Tables that the Views are created from, nor to to the View definition.

I use the following process to export the view:

  • Right-click the view
  • Export database as SQL
  • Do not check create tables or database
  • Data: INSERT; max Insert Size: 1024; Output: Single .sql file.
  • [Export]

The resulting file contains the following

-- --------------------------------------------------------
-- Host:                         my-sqlserver
-- Server version:               Microsoft SQL Server 2012 (SP2-CU8) (KB3082561) - 11.0.5634.1
-- Server OS:                    Windows NT 6.3 <X64> (Build 9600: )
-- HeidiSQL Version:             9.2.0.4947
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES  */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

I would instead expect this file to contain a series of INSERT INTO ... statements, containing the row values.

The output displayed in the log window (with Debug enabled) is:

*SELECT 'mydb' AS "Database", 'Appointment' AS "Table", -1 AS "Rows", 0 AS "Duration";
/* Result #1 fetched. */
/* Ping server ... */
/*!40101 SET @OLD_LOCAL_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;
/* Ping server ... */
SELECT 'mydb' AS "Database", 'Appointment' AS "Table", -1 AS "Rows", 0 AS "Duration";
/* Result #1 fetched. */
/* Ping server ... */
/*!40101 SET SQL_MODE=IFNULL(@OLD_LOCAL_SQL_MODE, '') */;*

The version of MS SQL is:

Microsoft SQL Server 2012 (SP2-CU8) (KB3082561) - 11.0.5634.1 (X64) 
    Sep  3 2015 18:55:08 
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: )

If anyone has an idea, or could potentially point me in a direction for troubleshooting, it would be most appreciated.

Thanks!

Please login to leave a reply, or register at first.