Archive for the ‘sql server’ Category

Report Builder With Dynamic SQL

June 11, 2012

Problem: You wish to create a report using Report Builder which utilises a text type dataset with dynamic SQL

Solution:
Declare your sql variable in the query text
Write TSQL which populates the variable using dynamic sql and execute the variable

Manually create a parameter and map this parameter within the dataset properties.

You wont be able to run this query within the query designer and it wont recognise any fields therefore you will need to manually add each field to the dataset.
Example
DECLARE @sql VARCHAR(MAX) = ‘SELECT ‘ + cast(@i as varchar(10)) + ‘ as field1’
EXEC (@sql)

Create a parameter called @i
Map @i to @i

Add a new query field called field1 to the dataset

Service Broker Across 2 instances on the same machine

May 31, 2012

Not that much more difficult than across 2 databases on the same instance.

Ive attached 6 files in a single .zip file 2 instances.zip

Service Broker Across two databases on the same instance

May 29, 2012

This post modifies the project contained in Service Broker with In a Single database by splitting the conversation out to 2 databases within the same instance. The rest of the functionality remains the same.

service_broker_across_db.sql

Service Broker In A Single Database

May 29, 2012

This post demonstrates how Service Broker can work within a single database. The example here demonstrates a conversationĀ initiatorĀ sending a message to the target, the target stores the message text in a table then the conversation is closed.

service_broker_single_database.sql