none
Reason for Syntax of openrowset? RRS feed

  • Question

  • Can anyone offer the reasoning behind the design of the openrowset construct? Specifically, consider the following example:

    select BulkColumn from openrowset(BULK 'C:\SomeFile.txt', SINGLE_BLOB) as X

    Here, the name of the file must be literal string. This means that something entirely reasonable like

    declare @filename varchar(260) = 'C:\SomeFile.txt';
    select BulkColumn from openrowset(BULK @filename, SINGLE_BLOB) as X;
    will not execute. From an API design perspective, I cannot fathom a legitimate reason why one cannot open a rowset by passing the filename as a variable. The only workaround really, at least that I'm aware of, is to build up a dynamic SQL string, based on the contents of the variable, and execute that.  Why should this be necessary? Is there a reason why the openrowset api is designed this way?

    Saturday, August 25, 2012 6:41 PM

Answers

  • What should I say? That Microsoft's efforts to make life easier for developers that we know so well from the Visual Studio environment all but vanishes into thin air when we enter the database?

    There are things that from an API perspective looks bad that makes perfect sense. There are very good reasons why the query argument in OPENQUERY must be a string literal and not a variable: the optimizer must at compile time be able to figure out the resultset.

    But OPENROWSET(BULK)? Well, there is some compilation going on. If you try:

      BEGIN TRY
         SELECT * FROM OPENROWSET(BULK 'nosuchfile', SINGLE_BLOB) AS t(c)
      END TRY
      BEGIN CATCH
         PRINT 'This does not print'
      END CATCH

    You will get an error message saying that the file does not exist. This is congruent with deferred name resolution. That is, first compilation of the statement fails, because the file is not there, but this error is suppresed, and then you get the error at run-time. But since it is a compilation error, the error is not caught by the CATCH handler.

    But why read the data file at compile-time? Beats me. And the Microsoft developer I spoke to just smiled embarassed. The format file is a different matter; if you have a format file, it must be known at compile time, because the format file determines the colunm names.

    In the end, it just one of these things that Microsoft has not come around to. There is a Connect item you can vote for:
    http://connect.microsoft.com/SQLServer/feedback/details/295626/more-flexibility-in-bulk-insert
    Although Microsoft has closed it as Won't Fix!


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, August 25, 2012 7:49 PM