SQL Server and Dynamic SQL

Published 8/20/2005 by Henry in SQL
HTML Source EditorWord wrap

'EXEC()', 'sp_executesql' and 'EXEC('EXEC sp_executesql ...')'

There are two ways to invoke dynamic SQL in MS SQL Server, 'EXEC()' and 'sp_executesql'. And you can use a combination of the two 'EXEC('EXEC sp_executesql ...')'.
EXEC() is the most simple, as illustrated by this example:

   1:  SELECT @table = 'sales' EXEC('SELECT * FROM ' + @table) 

The permissions of the current user always apply, even if the statement appears in a stored procedure. 'EXEC(''')' is similar to the EXEC of a stored procedure. But rather than calling a stored procedure, you invoke a batch of single SQL statements.

sp_executesql has the advantage that it allows you to pass parameters to the dynamic query, both for input and output.
Here is an example with an output parameter:

   1:  DECLARE @sql nvarchar(4000) -- nvarchar(MAX) on SQL 2005. DECLARE @col sysname 
   2:  DECLARE @min varchar(20) 
   3:  SELECT @col = N'au_fname' 
   4:  SELECT @sql = N'SELECT @min = convert(varchar(20), MIN(' + @col + N')) FROM authors' 
   5:  EXEC sp_executesql @sql, N'@min varchar(20) OUTPUT', @min OUTPUT SELECT @min 

Thus, you can much easier get a value from your dynamic SQL statement into a local variable than with EXEC().

These points apply to EXEC() and sp_executesql as well:

  • The SQL code is it in own scope, and you have no access to the variables in the calling stored procedure;
  • The permissions of the actual user apply;
  • Any USE statement will not affect the calling stored procedure;
  • Temp tables created in the SQL batch will not be available to the calling procedure;
  • SET statements only affects the SQL batch, not the caller;
  • Termination of the batch executed by sp_executesql terminates the calling procedure too;
  • @@error reports the status of the last statement in the dynamic SQL code;
  • According to Books Online the return value from sp_executesql is either 0 for success or 1 for failure. However it appears that the return value is the final value of @@error, at least in SQL 2000.

EXEC('EXEC sp_executesql ....
By using 'EXEC('EXEC sp_executesql ....' it is possible to use statement parameters with the dynamic query string. The sql query must be in unicode string , in nvarchar the MAX characters is 4000. By using EXEC('EXEC sp_executesql ...., multiple parameters can be concatenated, which results in the possibility to use more than 4000 characters in the dynamic query (the point with dynamic ofcourse is that you can not be sure what length the query will be).


   1:  DECLARE @query nvarchar(4000)
   2:  DECLARE @@queryDynamic nvarchar(4000)
   3:  DECLARE @l_Startdate datetimeDECLARE @l_Enddate datetimeDECLARE @dbName varchar(50)
   4:  DECLARE @dbName2 varchar(50)
   6:  SET @dbName = '[SQLServer_instance].[Databasename]'
   7:  SET @dbName2 = '[SQLServer_instance2].[Databasename2]' 
   8:  SET @query = 'SELECT * FROM ' + @dbName + '.dbo.tblTable ' + 'WHERE startDate >= @pl_Startdate AND Enddate < @pl_Enddate '
   9:  SET @@queryDynamic = 'UNION ' + 'SELECT * FROM ' + @dbName2 + '.dbo.tblTable ' + 'WHERE startDate >= @pl_Startdate AND Enddate < @pl_Enddate ' 
  11:  EXEC('EXEC sp_executesql N''' + @query + @queryDynamic + ''', N''@pl_Startdate datetime ,
 12:                              @pl_Enddate datetime'', @pl_Startdate = ''' + @l_Startdate + ''', @pl_Enddate = ''' + @l_Enddate + '''')

Henry Cordes
My thoughts exactly...

Microsoft Enterprise Library 2005

Published 8/16/2005 by Henry in EntLib

A sort off add-on for Visual Studio, this Application Blocks that are in the Library are released by the Patterns and Practices Group within Microsoft. They are reusable components that encapsulate Microsoft best practices in each area.

The Application blocks:

  • Caching
  • Configuration
  • Cryptography
  • Data Access
  • Exception Handling
  • Logging and Instrumentation
  • Security

The overall goals of the Enterprise Library are the following:

  • Consistency. All Enterprise Library application blocks feature consistent design patterns and implementation approaches.
  • Extensibility. All application blocks include defined extensibility points that allow developers to customize the behavior of the application blocks by adding in their own code.
  • Ease of use. Enterprise Library offers numerous usability improvements, including a graphical configuration tool, a simpler installation procedure, and clearer and more complete documentation and samples.
  • Integration. Enterprise Library application blocks are designed to work well together and are tested to make sure that they do. It is also possible to use the application blocks individually (except in cases where the blocks depend on each other, such as on the Configuration Application Block).

A graphical configuration tool (Enterprise Library Configuration Console), provides a way to change and validate application block settings without having to manually edit the XML configuration files where they are stored. The Configuration Console displays the settings and also supplies default values that you can change. Because the configuration settings are presented in a graphical tool, along with default values and information about what each setting means, the task of configuring the application blocks can be handled by developers and system administrators.

The Configuration Console 
The Configuration Console

Each block is well-documented and includes complete source code. In fact, when you download the library you will find that no compiled assemblies are included. In order to implement these products, you must compile each one from the included source code. Fortunately, there is an included batch file that will do all the work for you. In addition, the documentation clearly covers installation, implementation and deployment for all blocks.


Henry Cordes
My thoughts exactly...

Crystal Reports XI Datasource

Published 8/15/2005 by hendaman

In Crystal Reports Server XI it is possible to schedule reports. Also users can be given rights to browse reports and run them ad hoc. The problem is that datasources are defined in the binary .rpt file. If you don's use Business Views it is not possible to define a datasource at a higher level. In the Management Console it is possible to change the datasource for a report, but in reality this does not always work and a nasty 'dataconnector error' is the result, instead of a report.

The workaround we found is to change the 'hosts file' on the developing machine. The 'hosts file' can be found in '<WINDIR>\System32\etc', (<WINDIR> stands for the directory where Windows is installed "C:\WINDOWS"). In the 'hosts file' IP-Adresses are being pointed to hostnames, so the (local) machine knows which IP-Adress to look for if we use the hostname.

Example:     rhino.acme.com          # sourceserver      x.acme.com              # x clienthost        localhost

In the hosts file we changed the developing SQL Server instance IP- adres in the production SQL Server instance name.
If on the development network the SQL Server instance name is "Develop" with ip adress and in production the SQL Server instance name is "Production\Instance" than we would add this in the hosts file:        Production\Instance

This way the names are the same as on the production machine and the reports work without having to change the datasource.
I realize this is a work around, but we were happy we found it, because the only other way we could think of was installing a SQL Server instance with the production names.

Henry Cordes
My thoughts exactly...

Recently Microsoft launched the first new Windows Operating System Vista as a Beta for the developing community.
Here you can find more information.
Microsoft only released this Beta to MSDN subscription members and TechNet members.
At Erwyn van der Meer's blog  there is a lot of info about Vista, in Juli 2005 he experimented a lot with this new OS and found some interesting things he put on his blog.

Henry Cordes
My thoughts exactly...