dynamic report header and detail

I am building a report which has 7 columns. Mon thru Sun. The header and data columns are dynamic

For ex the report looks like

Mon Tue Wed Thu Fri Sat Sun

-- - -- - --

2 2 4 2 4 5 6

0 7 6 7 9 4 2

The report header and data are dynamic. The seq of columns,data is not always the same. It could be Tue thru Mon., or Fri thru Thru...etc...

Please advice how to program this dynamic nature of the report

This is a matrix report with a group on day of the week.

dynamic report header and detail

I am building a report which has 7 columns. Mon thru Sun. The header and data columns are dynamic

For ex the report looks like

Mon Tue Wed Thu Fri Sat Sun

-- - -- - --

2 2 4 2 4 5 6

0 7 6 7 9 4 2

The report header and data are dynamic. The seq of columns,data is not always the same. It could be Tue thru Mon., or Fri thru Thru...etc...

Please advice how to program this dynamic nature of the report

This is a matrix report with a group on day of the week.sql

Dynamic OLE DB Source and Destination

Hi,

I am building SSIS for 3 different files that have identical
schema and mapping logic.

In my OLE DB Source (object name - "OLEDBSource_SourceTable")
Data Access mode is "Variable name".
As soon as I swithced to this Data Acces mode
it started to give me an error:

[OLEDBSource_SourceTable [1]] Warning: The external metadata column collection is out of synchronization
with the data source columns.

The column "DEAL_NUM" needs to be updated in the external metadata column collection.
The "external metadata column "DEAL_NUM_Flag" (34529)" needs to be removed
from the external metadata column collection.
The "external metadata column "recordID" (33740)"
needs to be removed from the external metadata column collection.
Meta data CANNOT change. You cannot setup an OLE DB source and have columns mapped and then just change the underlying source table. How can you imagine that working?

Search this forum for dynamic source/destinations and you'll get plenty of conversations on this topic.

Bottom line, SSIS relies on meta data. If you change it, how do you expect it to operate?|||

Hi Riga,

To make the data source dynamic you need to make the ConnectionString dynamic.

Hope this helps,

Andy

|||Everything turned out to be much easier, gentlemen!
As soon as I set in my OLE DB Source Properties
"ValidateExternalData" to False
it starts working.
I guess when your source is a variable it should be this way?

Robert
|||

Robert,

Could you please provide more details about your package? what is exactly variable on it? is the name of the table?

Phil is right, the number and data type of the columns cannot change in a dataflow. So, I guess that is not your case; but I am curious about your specific scenario.

|||I have a variable [SourceTable] that keeps a table name.
I am using it in my OLE DB Source Data Access Mode.
But with ValidateExternalMetadata=True it didn't work.
so I changed it to ValidateExternalMetadata=False
and it works now.

I don't know why Phil says it can not be done.
Even if I hold a table name in a variable
the bottom line is that all the tables have identical schema.

So as long as you set a default value of {SourceTable}
let's say to "Table1" it will work when
{SourceTable} changes to "Table2","Table3" and so on.

Robert
sql

Dynamic OLE DB Source and Destination

Hi,

I am building SSIS for 3 different files that have identical
schema and mapping logic.

In my OLE DB Source (object name - "OLEDBSource_SourceTable")
Data Access mode is "Variable name".
As soon as I swithced to this Data Acces mode
it started to give me an error:

[OLEDBSource_SourceTable [1]] Warning: The external metadata column collection is out of synchronization
with the data source columns.

The column "DEAL_NUM" needs to be updated in the external metadata column collection.
The "external metadata column "DEAL_NUM_Flag" (34529)" needs to be removed
from the external metadata column collection.
The "external metadata column "recordID" (33740)"
needs to be removed from the external metadata column collection.Meta data CANNOT change. You cannot setup an OLE DB source and have columns mapped and then just change the underlying source table. How can you imagine that working?

Search this forum for dynamic source/destinations and you'll get plenty of conversations on this topic.

Bottom line, SSIS relies on meta data. If you change it, how do you expect it to operate?|||

Hi Riga,

To make the data source dynamic you need to make the ConnectionString dynamic.

Hope this helps,

Andy

|||Everything turned out to be much easier, gentlemen!
As soon as I set in my OLE DB Source Properties
"ValidateExternalData" to False
it starts working.
I guess when your source is a variable it should be this way?

Robert|||

Robert,

Could you please provide more details about your package? what is exactly variable on it? is the name of the table?

Phil is right, the number and data type of the columns cannot change in a dataflow. So, I guess that is not your case; but I am curious about your specific scenario.

|||I have a variable [SourceTable] that keeps a table name.
I am using it in my OLE DB Source Data Access Mode.
But with ValidateExternalMetadata=True it didn't work.
so I changed it to ValidateExternalMetadata=False
and it works now.

I don't know why Phil says it can not be done.
Even if I hold a table name in a variable
the bottom line is that all the tables have identical schema.

So as long as you set a default value of {SourceTable}
let's say to "Table1" it will work when
{SourceTable} changes to "Table2","Table3" and so on.

Robert

Dynamic graphs in SSRS

Hello,

Im building a horizontal bar graph, but according to the parameters it could have a different number of bars, how can i make the SSRS resize the graph?

Or how can i make ssrs to give the same size as a table right next to it?

Thank you

You may need to create a couple of separate graphs with different sizes and show-hide based on # of rows in the dataset.

There is an article here which may also help, though it is more for working inside the chart rather than sizing the object itself. http://www.ssw.com.au/ssw/Standards/Rules/RulesToBetterSQLReportingServices.aspx#maxminvalues

A more flexible approach may be available in Dundas Charts for Reporting Services.

More info here:
http://msdn2.microsoft.com/en-us/library/aa964128.aspx

cheers,
Andrew

Dynamic For Loop

My below code returns a union fine in northwind. However, I'd like add some
FOR LOOP's while building the SELECT statements to cut down on the redundant
code. As you can see, each @.SQLx statement has a variable from 1 to 3. I
created this exmple to get an idea of using a FOR LOOP to cut down on code.
CODE:
declare @.SQL varchar(4000), @.SQL1 varchar(4000), @.SQL2 varchar(4000), @.SQL3
varchar(4000)
declare @.dtYear1 int, @.dtYear2 int, @.dtYear3 int, @.dtStartDate1 datetime,
@.dtEndDate1 datetime
declare @.dtStartDate2 datetime, @.dtEndDate2 datetime, @.dtStartDate3
datetime, @.dtEndDate3 datetime
declare @.typeID int, @.debug int
set @.typeID = '1'
set @.dtStartDate1='19960101'
set @.dtEndDate1='19961231'
set @.dtStartDate2='19970101'
set @.dtEndDate2='19971231'
set @.dtStartDate3='19980101'
set @.dtEndDate3='19981231'
set @.dtYear1='1996'
set @.dtYear2='1997'
set @.dtYear3='1998'
SET @.SQL1 = ' SELECT ' + '''Year $$@.dtYear1$$''' + ' AS Date, '
SET @.SQL2 = ' SELECT ' + '''Year $$@.dtYear2$$''' + ' AS Date, '
SET @.SQL3 = ' SELECT ' + '''Year $$@.dtYear3$$''' + ' AS Date, '
set @.SQL1 = REPLACE(@.SQL1,'$$@.dtYear1$$',@.dtYear1)
set @.SQL2 = REPLACE(@.SQL2,'$$@.dtYear2$$',@.dtYear2)
set @.SQL3 = REPLACE(@.SQL3,'$$@.dtYear3$$',@.dtYear3)
set @.debug = 0
IF @.typeID = 1
BEGIN
SET @.SQL1 = @.SQL1 + 'MAX(Orders.Freight) AS MaxOfFreight '
SET @.SQL2 = @.SQL2 + 'MAX(Orders.Freight) AS MaxOfFreight '
SET @.SQL3 = @.SQL3 + 'MAX(Orders.Freight) AS MaxOfFreight '
END
IF @.typeID = 2
BEGIN
SET @.SQL1 = @.SQL1 + 'COUNT(*) AS SalesCount '
SET @.SQL2 = @.SQL2 + 'COUNT(*) AS SalesCount '
SET @.SQL3 = @.SQL3 + 'COUNT(*) AS SalesCount '
END
SET @.SQL1 = @.SQL1 + 'FROM Customers INNER JOIN Orders ON
Customers.CustomerID = Orders.CustomerID WHERE '
SET @.SQL2 = @.SQL2 + 'FROM Customers INNER JOIN Orders ON
Customers.CustomerID = Orders.CustomerID WHERE '
SET @.SQL3 = @.SQL3 + 'FROM Customers INNER JOIN Orders ON
Customers.CustomerID = Orders.CustomerID WHERE '
SET @.SQL1 = @.SQL1 + 'Orders.OrderDate >= ''' + convert(char(8),
@.dtStartDate1, 112) + '''' + ' AND ' + '' + 'Orders.OrderDate <= ''' +
convert(char(8), @.dtEndDate1, 112) + ''''
SET @.SQL2 = @.SQL2 + 'Orders.OrderDate >= ''' + convert(char(8),
@.dtStartDate2, 112) + '''' + ' AND ' + '' + 'Orders.OrderDate <= ''' +
convert(char(8), @.dtEndDate2, 112) + ''''
SET @.SQL3 = @.SQL3 + 'Orders.OrderDate >= ''' + convert(char(8),
@.dtStartDate3, 112) + '''' + ' AND ' + '' + 'Orders.OrderDate <= ''' +
convert(char(8), @.dtEndDate3, 112) + ''''
SET @.SQL = @.SQL1 + ' UNION ALL ' + @.SQL2 + ' UNION ALL ' + @.SQL3
IF @.debug = 1
PRINT @.SQL
ELSE
EXEC(@.SQL)Scott
SQL Server does not have FOR LOOP , bit it does have WHILE LOOP
See soem examples
DECLARE @.i INT
SET @.i=1
WHILE @.i<100
BEGIN
INSERT INTO Table VALUES (@.i)
SET @.i=@.i+1
END
"Scott" <sbailey@.mileslumber.com> wrote in message
news:eR6CT9EBGHA.272@.TK2MSFTNGP09.phx.gbl...
> My below code returns a union fine in northwind. However, I'd like add
> some FOR LOOP's while building the SELECT statements to cut down on the
> redundant code. As you can see, each @.SQLx statement has a variable from 1
> to 3. I created this exmple to get an idea of using a FOR LOOP to cut down
> on code.
> CODE:
> declare @.SQL varchar(4000), @.SQL1 varchar(4000), @.SQL2 varchar(4000),
> @.SQL3 varchar(4000)
> declare @.dtYear1 int, @.dtYear2 int, @.dtYear3 int, @.dtStartDate1 datetime,
> @.dtEndDate1 datetime
> declare @.dtStartDate2 datetime, @.dtEndDate2 datetime, @.dtStartDate3
> datetime, @.dtEndDate3 datetime
> declare @.typeID int, @.debug int
> set @.typeID = '1'
> set @.dtStartDate1='19960101'
> set @.dtEndDate1='19961231'
> set @.dtStartDate2='19970101'
> set @.dtEndDate2='19971231'
> set @.dtStartDate3='19980101'
> set @.dtEndDate3='19981231'
> set @.dtYear1='1996'
> set @.dtYear2='1997'
> set @.dtYear3='1998'
> SET @.SQL1 = ' SELECT ' + '''Year $$@.dtYear1$$''' + ' AS Date, '
> SET @.SQL2 = ' SELECT ' + '''Year $$@.dtYear2$$''' + ' AS Date, '
> SET @.SQL3 = ' SELECT ' + '''Year $$@.dtYear3$$''' + ' AS Date, '
> set @.SQL1 = REPLACE(@.SQL1,'$$@.dtYear1$$',@.dtYear1)
> set @.SQL2 = REPLACE(@.SQL2,'$$@.dtYear2$$',@.dtYear2)
> set @.SQL3 = REPLACE(@.SQL3,'$$@.dtYear3$$',@.dtYear3)
> set @.debug = 0
> IF @.typeID = 1
> BEGIN
> SET @.SQL1 = @.SQL1 + 'MAX(Orders.Freight) AS MaxOfFreight '
> SET @.SQL2 = @.SQL2 + 'MAX(Orders.Freight) AS MaxOfFreight '
> SET @.SQL3 = @.SQL3 + 'MAX(Orders.Freight) AS MaxOfFreight '
> END
> IF @.typeID = 2
> BEGIN
> SET @.SQL1 = @.SQL1 + 'COUNT(*) AS SalesCount '
> SET @.SQL2 = @.SQL2 + 'COUNT(*) AS SalesCount '
> SET @.SQL3 = @.SQL3 + 'COUNT(*) AS SalesCount '
> END
> SET @.SQL1 = @.SQL1 + 'FROM Customers INNER JOIN Orders ON
> Customers.CustomerID = Orders.CustomerID WHERE '
> SET @.SQL2 = @.SQL2 + 'FROM Customers INNER JOIN Orders ON
> Customers.CustomerID = Orders.CustomerID WHERE '
> SET @.SQL3 = @.SQL3 + 'FROM Customers INNER JOIN Orders ON
> Customers.CustomerID = Orders.CustomerID WHERE '
> SET @.SQL1 = @.SQL1 + 'Orders.OrderDate >= ''' + convert(char(8),
> @.dtStartDate1, 112) + '''' + ' AND ' + '' + 'Orders.OrderDate <= ''' +
> convert(char(8), @.dtEndDate1, 112) + ''''
> SET @.SQL2 = @.SQL2 + 'Orders.OrderDate >= ''' + convert(char(8),
> @.dtStartDate2, 112) + '''' + ' AND ' + '' + 'Orders.OrderDate <= ''' +
> convert(char(8), @.dtEndDate2, 112) + ''''
> SET @.SQL3 = @.SQL3 + 'Orders.OrderDate >= ''' + convert(char(8),
> @.dtStartDate3, 112) + '''' + ' AND ' + '' + 'Orders.OrderDate <= ''' +
> convert(char(8), @.dtEndDate3, 112) + ''''
> SET @.SQL = @.SQL1 + ' UNION ALL ' + @.SQL2 + ' UNION ALL ' + @.SQL3
> IF @.debug = 1
> PRINT @.SQL
> ELSE
> EXEC(@.SQL)
>|||A better way would be something like this:
SELECT 'Year '+DATENAME(YEAR,MIN(orderdate)) AS [date],
MAX(O.Freight) AS maxoffreight
FROM Customers AS C
JOIN Orders AS O
ON C.customerid = O.customerid
WHERE O.orderdate BETWEEN @.startdate AND @.enddate
GROUP BY YEAR(O.orderdate) ;
David Portas
SQL Server MVP
--|||I guess what I'm asking is there a way in SQL to create dynamic variables?
So for example, my code would loop 3 times and each time my variables like
@.dtStartDate1 would increase from @.dtStartDate1 to @.dtStartDate2 and then
@.dtStartDate3.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23aBO7zFBGHA.2512@.TK2MSFTNGP09.phx.gbl...
> Scott
> SQL Server does not have FOR LOOP , bit it does have WHILE LOOP
> See soem examples
> DECLARE @.i INT
> SET @.i=1
> WHILE @.i<100
> BEGIN
> INSERT INTO Table VALUES (@.i)
> SET @.i=@.i+1
> END
>
> "Scott" <sbailey@.mileslumber.com> wrote in message
> news:eR6CT9EBGHA.272@.TK2MSFTNGP09.phx.gbl...
>|||I would do it like that except we have custom year begin and end dates and i
didn't want to take time to write a custom function.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1134997569.867513.55250@.f14g2000cwb.googlegroups.com...
>A better way would be something like this:
> SELECT 'Year '+DATENAME(YEAR,MIN(orderdate)) AS [date],
> MAX(O.Freight) AS maxoffreight
> FROM Customers AS C
> JOIN Orders AS O
> ON C.customerid = O.customerid
> WHERE O.orderdate BETWEEN @.startdate AND @.enddate
> GROUP BY YEAR(O.orderdate) ;
> --
> David Portas
> SQL Server MVP
> --
>|||scott wrote:
> I would do it like that except we have custom year begin and end dates and
i
> didn't want to take time to write a custom function.
>
Are your annual begin and end dates fixed in the Calendar year or are
they based on some specific non-Gregorian calendar rules? If it's a
Calendar year, just use DATEADD to offset the date by the required
number of days. If it's specific company calendar logic, create a
calendar table and populate it with the information about your
customer's calendar. Join the calendar table into the query. Either
way, you don't need dynamic SQL.
David Portas
SQL Server MVP
--

Dynamic Database Source Changing

Hi,

I am building a data warehouse for a customer who has systems located in two different countries.

I need to import that data from four seperate databases, which all share the same structure.

To do this i have created 20 packages to import that data from the source database. What i would like to do, is at run time set which database the SSIS package should get its data from.

In sql 2k this was easy with a global variable that was set, then use a dynamic properties task to set the data source.

How can i achieve the same result in SSIS? the data source is an ODBC connection, with the four ODBC connections having similar names, eg ABC_NZ, ABC_AU

Thanks in Advance!

Truby

Use a ForEach Loop Container to loop over your collection of ODBC connection names.

Upon each iteration, set the connection string of the connection manager. This technique is described here: http://blogs.conchango.com/jamiethomson/archive/2005/05/30/1489.aspx although in this exampe it talks about using a flat file conneciton manager which is not what you want. The principle is the same though.

-Jamie

|||

Hi Jamie,

Thanks for that, it would be perfect if i could run the database extracts from each system at the same time, but i need to be able to schedule the extracts at different times due to different time zones.

what i really want to be able to do is specify the connection at run time.

eg set variable "datasource" to be "ABC_NZ", and that will point at the ABC_NZ ODBC.

Truby

|||

AHA. You can supply this information on the command-line. use the /SET option of dtexec.exe

And use dtexecui.exe to build the command-line for you.

-Jamie

|||

Hi,

I had a similar problem like you. I recognized that it is helpful to use one or more global variables which hold the infomation about the data source. The variable(s) could be set during runtime (i.e. from a db table) and finally you can dynamically change a connection in the connection manager when you click on the connection, properties, expressions. Under expressions you might use your variables to set up a new connection string dynamically.

Example for OLE DB:

"Data Source="+@.[User::Address]+";User ID="+@.[User::UserID]+";Initial Catalog="+@.[User::CatalogName]+";Provider=SQLOLEDB.1;Password="+@.[User::PWD]+";"

If you are not sure about the structure of your connection strings then have a look under:

http://www.connectionstrings.com/

Hope that helps.

Regards,

Stefan

|||

Hi,

You can create a package configuration file and specify Connections being set dynamically from SQL AGent or a schedule job.

Follow

In the Integration serivice screen select package configuration Create a file and select the ODBC connection items as configurible. Remember to copy the .dtsconfig file in the place where ur deloying the package.

Once you have done this.

Create a Schedule job under the steps u select the pakage. After setting the package you can go to the connection tab and then change the datasource and the connection strings to what ever you want and leave it.

Like that you can create multiple scheuler for the same dts package and make it run in different time zones according to your requirment.

Hope this helps a bit

Mani

Dynamic Data/Groups/Page breaks

I am absolutely new to SSRS (about a day into it). I am trying to figure out
the reporting methodology to use for building custom reports and would really
like some suggestions on the reporting architecture that I should use.
The base functionality is
1) Provide a c# UI to allow end-user to set report options (described below).
2) Use a stored procedure to generate dynamic sql based on user selection in
(1).
2) Show the report in the report viewer control (for now, we might develop
our own custom report viewer control later).
Almost all our report options are highly dynamic in nature. These include
ability to include/exclude some data, dynamic grouping on certain data,
dynamic page breaks as requested by the user and a whole lot more.
The Report Designer seems almost like a non-starter for us, based on our
requirements.
From what I've read so far, the best option seems to be to dynamically
generate the RDL, from our c# application, to be used in our reports. This
means we won't have to publish our reports to the ReportServer.
Is this the best approach based on the above requirements? Are there any
other options available? Also, what might be the best resources for dynamic
RDL generation on the web or books?
Any inputs will be greatly appreciated.
Thanks,
NaveenOn Feb 21, 11:06 am, Naveen <Nav...@.discussions.microsoft.com> wrote:
> I am absolutely new to SSRS (about a day into it). I am trying to figure out
> the reporting methodology to use for building custom reports and would really
> like some suggestions on the reporting architecture that I should use.
> The base functionality is
> 1) Provide a c# UI to allow end-user to set report options (described below).
> 2) Use a stored procedure to generate dynamic sql based on user selection in
> (1).
> 2) Show the report in the report viewer control (for now, we might develop
> our own custom report viewer control later).
> Almost all our report options are highly dynamic in nature. These include
> ability to include/exclude some data, dynamic grouping on certain data,
> dynamic page breaks as requested by the user and a whole lot more.
> The Report Designer seems almost like a non-starter for us, based on our
> requirements.
> From what I've read so far, the best option seems to be to dynamically
> generate the RDL, from our c# application, to be used in our reports. This
> means we won't have to publish our reports to the ReportServer.
> Is this the best approach based on the above requirements? Are there any
> other options available? Also, what might be the best resources for dynamic
> RDL generation on the web or books?
> Any inputs will be greatly appreciated.
> Thanks,
> Naveen
I would have to say that dynamically generated RDLs via C# would
probably be the best route to take. Just an FYI, you can dynamically
control data (i.e., filtering, including, excluding data) w/a
combination of report parameters tied to report expressions. Hope this
helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||I just about discovered that using expressions might be worth a try before
attempting to generate RDL dynamically. Your response seems to confirm that.
If that doesn't work, maybe dynamic RDL generation would be the way to go.
Thanks for your reply.
Naveen
"EMartinez" wrote:
> On Feb 21, 11:06 am, Naveen <Nav...@.discussions.microsoft.com> wrote:
> > I am absolutely new to SSRS (about a day into it). I am trying to figure out
> > the reporting methodology to use for building custom reports and would really
> > like some suggestions on the reporting architecture that I should use.
> >
> > The base functionality is
> > 1) Provide a c# UI to allow end-user to set report options (described below).
> > 2) Use a stored procedure to generate dynamic sql based on user selection in
> > (1).
> > 2) Show the report in the report viewer control (for now, we might develop
> > our own custom report viewer control later).
> >
> > Almost all our report options are highly dynamic in nature. These include
> > ability to include/exclude some data, dynamic grouping on certain data,
> > dynamic page breaks as requested by the user and a whole lot more.
> >
> > The Report Designer seems almost like a non-starter for us, based on our
> > requirements.
> >
> > From what I've read so far, the best option seems to be to dynamically
> > generate the RDL, from our c# application, to be used in our reports. This
> > means we won't have to publish our reports to the ReportServer.
> >
> > Is this the best approach based on the above requirements? Are there any
> > other options available? Also, what might be the best resources for dynamic
> > RDL generation on the web or books?
> >
> > Any inputs will be greatly appreciated.
> >
> > Thanks,
> > Naveen
>
> I would have to say that dynamically generated RDLs via C# would
> probably be the best route to take. Just an FYI, you can dynamically
> control data (i.e., filtering, including, excluding data) w/a
> combination of report parameters tied to report expressions. Hope this
> helps.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>

Dynamic building of reports in C# for ASP.NET

I saw a post earlier referencing a book on how to dynamically building reports. But does anybody know of a web site I can review to simply get an idea of how to do it to see if this is what we want to do vs simply using a data grid for reporting.

Thanks

We are currently doing this for an enterprise solution, however we have hit a couple of snags. See my other posts. As far as materials for this, there is very little. The best idea is to generate a few reports, and look at the RDL files that are generated. All you then have to do is create an engine to dynamically generate this content, which can then be executed.

Unfortuantely I am unable to share with you are current application code as it is confidential however I would be more than happy to answer a few help questions.

Matthew Christopher
.Net Developer / Consultant
Edmonton, AB, Canada
matt_chrs@.hotmail.com(nospam)

|||It's a lot of work. This article is a start.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/AdHocRepSr.asp

Dyanamic stored procedures

Hi all,
I'm building a search function and I need do it using stored procedures. I'm searching a table on 'Name' 'email address' and 'key words'. (basically 3 columns). The user must be able to use it as
SELECT ......... WHERE (NAME LIKE @.name) AND/OR (Email LIKE @.email) AND/OR ..........
Can some one tell me how to handle this in a stored procedure. By the way is this approch correct. Please tell me if there is a better method to tackle this situation.
Thanks,
-VJ

I always generate a dynamic SQL statement based on what the user has entered and then execute it or use sp_executesql to run it. Some will say that dynamic SQL is bad because the execution plan doesn't get cached like normal procs, but you can get better performance with dynamic SQL sometimes because since the query criteria can change dramtically, the cached execution plan may be a bad one. Always bets to time and view the execution plan in QA, but my experience is that dynamic SQL within the stored proc works best when there are varying search parameters.

|||If it's just 3 columns you're validating, you should just use case statements instead of utilizing dynamic sql.
selelct * from table where -- normal stuff
Name Like (
CASE
WHEN @.name IS NOT NULL THEN @.name
ELSE Name
END
)

-----
A lot of times, I'll validate a string for nulls and empty strings, so I'll usually have this function in most of my databases.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION [dbo].[IsEmptyString]
(
@.String VarChar(2000)
)
RETURNS BIT AS
BEGIN
DECLARE @.ReturnVar BIT
IF (@.String IS NOT NULL AND @.String <> '') -- you can trim if you are avoiding whitespaces.
SET @.ReturnVar = 0
ELSE
SET @.ReturnVar = 1

RETURN @.ReturnVar
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

|||Test it both ways. I've done it with case statements and gotten terrible performance compared with dynamic SQL. It all depends on the situation. Having 3 or 6 or 20 columns really isn't the issue; it's how the procedure performs when varying numbers or combinations of the criteria are entered. I've seen demos where the dynamic SQL blew away using case or isnull.|||You're right that it's completely situational in terms of performance,and many will do their best to keep the actual logic in code as much aspossible. From personal experience, iterations through decentsized databases (2 million records in each of the major tables), a casestatement will perform fairly well as compared to utilizing dynamicsql. It helps further when you require calculations to be madeper record to assist in it's selection criteria (Distance for example),and the one of the factors is based on the parameters. In caseslike this, a case statement tends to outweigh a dump of string to beexecuted in a stored procedure.
Create Procedure KraGiE.ExecuteString
@.SQL varchar (2000)
AS
EXECUTE( @.SQL )
GO
For some reason, that makes me cringe, and I'm sure many database administrators will plainly say "no."

Dyanamic Sql

Hi I was building a dynamic sql with output please tell me where I am wrong.
Or Tell me right way to do it

My table name is variable (Stock name)
I want close price for given Stock

CREATE PROCEDURE SP_GET_CLOSE
@.TableName as varchar(50),
@.close_price as varchar(50) output

AS
Declare @.SQL NVarChar(1000)
Declare @.high varchar(50)
SET @.SQL = 'Select top 1 @.close_price = [close] from '+ @.TableName+ ' order by trade_date desc'

print @.SQL
Exec sp_executesql @.SQL, N'@.close_price varchar(50) output' , @.close_price
GOWaht abotu this idea?

CREATE PROCEDURE SP_GET_CLOSE
@.TableName as varchar(50),
@.close_price as varchar(50) output

AS
Declare @.SQL NVarChar(1000)
Declare @.high varchar(50)
SET @.SQL = 'Select top 1 [close] from '+ @.TableName+ ' order by trade_date desc'

create table #tmp(ret varchar(450))

insert #tmp
exec(@.SQL)

select @.close_price=cast(ret as money) from #tmp|||Yes That seems a good idea but I have some 200 trading Scripts in month and this would create lot of #tmp tables I thought it should be possible to send dynamic SQl a output variable.
Besides I am having lot of similar quiries in mind for my project

Thanks|||Originally posted by shriya
Yes That seems a good idea but I have some 200 trading Scripts in month and this would create lot of #tmp tables I thought it should be possible to send dynamic SQl a output variable.
Besides I am having lot of similar quiries in mind for my project

Thanks

Temporary table will dropped after procedure execution. BTW, sql server is using temporary objects during any operations (even like select from with order by) so do not worry about this at all.

DWH problem: updating a table with every 1000 records a checkpoint

Hi,

Currently we're a building a metadatadriven datawarehouse in SQL
Server 2000. We're investigating the possibility of the updating
tables with enormeous number of updates and insert and the use of
checkpoints (for simple recovery and Backup Log for full recovery).

On several website people speak about full transaction log and the
pace of growing can't keep up with the update. Therefore we want to
create a script which flushes the dirty pages to the disk. It's not
quite clear to me how it works. Questions we have is:
* How does the process of updating, insert and deleting works with SQL
Server 2000 with respect to log cache, log file, buffer cache, commit,
checkpoint, etc?
What happens when?
* As far as i can see now: i'm thinking of creating chunks of data of
1000 records with a checkpoint after the Query. SQL server has the
default of implicit transactions and so it will not need a commit.
Something like this?
* How do i create chunks of 1000 records automatically without
creating a identity field or something. Is there something like SELECT
NEXT 1000?

Greetz,

Hennie> * How does the process of updating, insert and deleting works with SQL
> Server 2000 with respect to log cache, log file, buffer cache, commit,
> checkpoint, etc?
> What happens when?

There's a discussion of this in the Books Online
<architec.chm::/8_ar_sa_8unn.htm>. In summary, all data modifications are
written to both the buffer cache and transaction log buffer cache. The log
buffer is written first. Modified buffer cache pages are written to disk by
either worker threads, the lazy writer or the checkpoint process. Worker
threads write data when they need to wait for a read i/o anyway. The lazy
writer process wakes up periodically and writes data in order to maintain a
reasonable number of free cache buffers. The checkpoint process writes all
dirty pages to disk periodically (about a minute by default) in order to
reduce startup recovery time.

Log buffers are written as they become full, when you issue a COMMIT and
during a checkpoint.

> * As far as i can see now: i'm thinking of creating chunks of data of
> 1000 records with a checkpoint after the Query. SQL server has the
> default of implicit transactions and so it will not need a commit.
> Something like this?
> * How do i create chunks of 1000 records automatically without
> creating a identity field or something. Is there something like SELECT
> NEXT 1000?

I'm not sure I understand what you are asking here. In the default
autocommit mode, each statement is a separate transaction.

It seems you are mostly concerned with transaction log space management.
The main consideration is to keep your transactions reasonably sized since
only committed data can be removed from the log during a log backup (FULL or
BULK_LOGGED recovery model) or checkpoint (SIMPLE model).

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Hennie de Nooijer" <hdenooijer@.hotmail.com> wrote in message
news:191115aa.0402090341.23275aae@.posting.google.c om...
> Hi,
> Currently we're a building a metadatadriven datawarehouse in SQL
> Server 2000. We're investigating the possibility of the updating
> tables with enormeous number of updates and insert and the use of
> checkpoints (for simple recovery and Backup Log for full recovery).
> On several website people speak about full transaction log and the
> pace of growing can't keep up with the update. Therefore we want to
> create a script which flushes the dirty pages to the disk. It's not
> quite clear to me how it works. Questions we have is:
> * How does the process of updating, insert and deleting works with SQL
> Server 2000 with respect to log cache, log file, buffer cache, commit,
> checkpoint, etc?
> What happens when?
> * As far as i can see now: i'm thinking of creating chunks of data of
> 1000 records with a checkpoint after the Query. SQL server has the
> default of implicit transactions and so it will not need a commit.
> Something like this?
> * How do i create chunks of 1000 records automatically without
> creating a identity field or something. Is there something like SELECT
> NEXT 1000?
> Greetz,
> Hennie|||Hennie de Nooijer (hdenooijer@.hotmail.com) writes:
> * How does the process of updating, insert and deleting works with SQL
> Server 2000 with respect to log cache, log file, buffer cache, commit,
> checkpoint, etc?
> What happens when?
> * As far as i can see now: i'm thinking of creating chunks of data of
> 1000 records with a checkpoint after the Query. SQL server has the
> default of implicit transactions and so it will not need a commit.
> Something like this?
> * How do i create chunks of 1000 records automatically without
> creating a identity field or something. Is there something like SELECT
> NEXT 1000?

I hope Dan's reply shed some light on how the log works.

As for breaking up in chucks there is nothing built-in like:

UPDATE tbl
SET yadayada
...
WITH COMMIT EACH 1000 ROW

For DML statements you will have to find out how to do the batching best
from your data. Note there is no reason to keep a strict batch size, but
if you have some column that divides the data nicely that can be used.
For instance, if you are handling order data, you can take one day or
month at a time.

The one case where you can tell SQL Server to commit by each n row is
when you bulk load with BCP or BULK INSERT.

I also like to point out that selecting the batch size, is a trade-off
between log size and speed. Particularly if the search condition to
identify a batch takes time to execute, too small batch sizes can be
costly. A little depending on row size, but 100000 rows at time is not
an unreasonable number. 1000 is by far to small.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> > * How does the process of updating, insert and deleting works with SQL
> > Server 2000 with respect to log cache, log file, buffer cache, commit,
> > checkpoint, etc?
> > What happens when?
> There's a discussion of this in the Books Online
> <architec.chm::/8_ar_sa_8unn.htm>.
How does this works?

> In summary, all data modifications are
> written to both the buffer cache and transaction log buffer cache. The log
> buffer is written first. Modified buffer cache pages are written to disk by
> either worker threads, the lazy writer or the checkpoint process. Worker
> threads write data when they need to wait for a read i/o anyway. The lazy
> writer process wakes up periodically and writes data in order to maintain a
> reasonable number of free cache buffers. The checkpoint process writes all
> dirty pages to disk periodically (about a minute by default) in order to
> reduce startup recovery time.
Ok clear..

> Log buffers are written as they become full, when you issue a COMMIT and
> during a checkpoint.
It thought that a commit is not neccessary a write to the disk. A
commit is just a end mark of a transaction as far i can see in the
documentation. A checkpoint will write the transactions to the
database.

> > * As far as i can see now: i'm thinking of creating chunks of data of
> > 1000 records with a checkpoint after the Query. SQL server has the
> > default of implicit transactions and so it will not need a commit.
> > Something like this?
> > * How do i create chunks of 1000 records automatically without
> > creating a identity field or something. Is there something like SELECT
> > NEXT 1000?
> I'm not sure I understand what you are asking here. In the default
> autocommit mode, each statement is a separate transaction.
> It seems you are mostly concerned with transaction log space management.
> The main consideration is to keep your transactions reasonably sized since
> only committed data can be removed from the log during a log backup (FULL or
> BULK_LOGGED recovery model) or checkpoint (SIMPLE model).
Yup this is what i meant. Only the question is what is a reasonable
chunk? Perhaps i will create parameter for this so i can tune this.|||> I hope Dan's reply shed some light on how the log works.
Yup

> As for breaking up in chucks there is nothing built-in like:
> UPDATE tbl
> SET yadayada
> ...
> WITH COMMIT EACH 1000 ROW
> For DML statements you will have to find out how to do the batching best
> from your data. Note there is no reason to keep a strict batch size, but
> if you have some column that divides the data nicely that can be used.
> For instance, if you are handling order data, you can take one day or
> month at a time.
Yeah well i have identity column which i could use for chunking the
records. Disadvantage is that it is an implementation depending on the
functionality of a column in the table. It's not what i want but i'll
have to deal with it.

> The one case where you can tell SQL Server to commit by each n row is
> when you bulk load with BCP or BULK INSERT.
Well this is not possible. I'm reading and insert/updating from other
tables in SQL server and BCP reads only from files.

> I also like to point out that selecting the batch size, is a trade-off
> between log size and speed. Particularly if the search condition to
> identify a batch takes time to execute, too small batch sizes can be
> costly. A little depending on row size, but 100000 rows at time is not
> an unreasonable number. 1000 is by far to small.
Well 1000 was just a hunch (is this rightly written). I thinking of
creating a parameter from which we can tune the chunks..

Thx you all for your answers

Greetz
Hennie|||> > There's a discussion of this in the Books Online
> > <architec.chm::/8_ar_sa_8unn.htm>.
> How does this works?

From the Books Online, you can go directly to the referenced topic by
clicking Go-->URL and pasting in the URL 'architec.chm::/8_ar_sa_8unn.htm'.

> > Log buffers are written as they become full, when you issue a COMMIT and
> > during a checkpoint.
> It thought that a commit is not neccessary a write to the disk. A
> commit is just a end mark of a transaction as far i can see in the
> documentation. A checkpoint will write the transactions to the
> database.

A COMMIT ensures all committed data are written to the log file so that
committed data are permanently persisted to the database. Modified data may
or may not have been written to data files because dirty data pages are
written separately by worker threads, the lazy writer or checkpoint process.
This is transparent to your application because data are always retrieved
from buffer cache.

Only committed data before the oldest uncommitted transaction can be removed
from the log. This occurs during a log backup in the FULL or BULK_LOGGED
recovery model or during a checkpoint in the SIMPLE model. Rather than
trying to take control of the checkpoint frequency, all you really need to
do is ensure your transactions are reasonably short and small.

> Only the question is what is a reasonable
> chunk? Perhaps i will create parameter for this so i can tune this.

If you are running in the SIMPLE recovery model and have many small
insert/update/delete transactions, I suggest you simply let SQL Server
truncate the log automatically. However, if you modify a lot of data (e.g.
millions of rows) in a single statement or transaction, you can use the
techniques Erland mentioned to keep the log size reasonable. In my
experience, the optimal amount is somewhat less that the amount of memory
allocated to SQL Server.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Hennie de Nooijer" <hdenooijer@.hotmail.com> wrote in message
news:191115aa.0402110050.7797d139@.posting.google.c om...
> > > * How does the process of updating, insert and deleting works with SQL
> > > Server 2000 with respect to log cache, log file, buffer cache, commit,
> > > checkpoint, etc?
> > > What happens when?
> > There's a discussion of this in the Books Online
> > <architec.chm::/8_ar_sa_8unn.htm>.
> How does this works?
> > In summary, all data modifications are
> > written to both the buffer cache and transaction log buffer cache. The
log
> > buffer is written first. Modified buffer cache pages are written to
disk by
> > either worker threads, the lazy writer or the checkpoint process.
Worker
> > threads write data when they need to wait for a read i/o anyway. The
lazy
> > writer process wakes up periodically and writes data in order to
maintain a
> > reasonable number of free cache buffers. The checkpoint process writes
all
> > dirty pages to disk periodically (about a minute by default) in order to
> > reduce startup recovery time.
> Ok clear..
> > Log buffers are written as they become full, when you issue a COMMIT and
> > during a checkpoint.
> It thought that a commit is not neccessary a write to the disk. A
> commit is just a end mark of a transaction as far i can see in the
> documentation. A checkpoint will write the transactions to the
> database.
> > > * As far as i can see now: i'm thinking of creating chunks of data of
> > > 1000 records with a checkpoint after the Query. SQL server has the
> > > default of implicit transactions and so it will not need a commit.
> > > Something like this?
> > > * How do i create chunks of 1000 records automatically without
> > > creating a identity field or something. Is there something like SELECT
> > > NEXT 1000?
> > I'm not sure I understand what you are asking here. In the default
> > autocommit mode, each statement is a separate transaction.
> > It seems you are mostly concerned with transaction log space management.
> > The main consideration is to keep your transactions reasonably sized
since
> > only committed data can be removed from the log during a log backup
(FULL or
> > BULK_LOGGED recovery model) or checkpoint (SIMPLE model).
> Yup this is what i meant. Only the question is what is a reasonable
> chunk? Perhaps i will create parameter for this so i can tune this.|||[posted and mailed, please reply in news]

Hennie de Nooijer (hdenooijer@.hotmail.com) writes:
> Yeah well i have identity column which i could use for chunking the
> records. Disadvantage is that it is an implementation depending on the
> functionality of a column in the table. It's not what i want but i'll
> have to deal with it.

One thing that I should have added is that you should see if the
clustered index is good for chunking. That can speed up the time
it takes for SQL Server to locate the rows quite a bit.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

DW design question - Dimension for currency

Hi everybody,
I struggle with a DW design issue that I hope somebody can provide a
solution for?
I have a traditional Star Schema, and are building an OLP cube on top. The
development is taken place using SQL Server 2005.
For the purpose of this question, the layout is the following:
Dimension tables:
Time: Transaction date, week, month, etc.
Product: Product Category, Product Line, Product.
Customer: Customer, Customer Segment, etc.
Fact table:
Sales: Transaction date, Customer, Product, Price, Currency, price in
currency, Exchange rate.
Requirement:
I am looking for sales by product by customer/product etc. (This is not a
problem). However, each of the sales transactions is made in a particular
currency. For example is sales transaction 1 in Euro and transaction 2 is in
British Pounds. Both transactions are for Product A. On top of the existing
dimensions, I need to see each of the sales transactions in a given currency
and exchange rate (for example Actual or budgeted exchange rate).
One scenario could be to see the value of sales by product (Product A) in
Euro. In this case will transaction 2 that was made in GBP have to be
converted to EUR, so I can see total sales for product A in Euro.
Solution so far:
So far, have I have added the combination of exchange rate and currency to
the fact table. For example, the transaction in currency "GBP" and "EUR" for
both the exchange rate "Actual" and "Budget2005". This gives 4 entries for
each sales transaction in the fact table. This does NOT work... Selecting on
the dimension tables, for example on the product lead to the value of the 4
transactions to be added together. :-( Where in fact I only want the unique
transaction based on dimension and chosen Currency and Exchange rate.
I have considered making another dimension for Currency and Exchange rate,
but it is not possible to create a relationship to the fact table as the
values Currency and Exchange rate can not make a unique relation to the fact
table.
So far my best bet is only to have one unique entry of the sales transaction
in the original currency in the Fact table. But how do I then make it
possible to see the sales transaction by relevant dimension in the chosen
Currency and Exchange rate?
Look forward to hearing from you.
Best Regards,
Bigalexx
Hi Bigalexx,
Thanks for your post.
This question appears to be consulting in nature. We would also like to
introduce you to the CSS Advisory Services team.
Advisory Services is a remotely delivered, hourly fee-based, consultative
support option that provides a comprehensive result beyond your break-fix
product maintenance needs. It is an hourly fee-based, consultative
support option that provides proactive support beyond your break-fix
product maintenance needs. This support option includes working with the
same technician for assistance with issues like product migration, code
review, or new program development.
For more info in the US and Canada:
http://support.microsoft.com/default...dvisoryService
Outside of the US/Canada:
http://support.microsoft.com/default...rnational.aspx
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Bigalexx,
>From your question I am guessing you are a beginner....
I have a beginners page on my web site www.peternolan.com with links on
the books to read to learn data modeling for BI systems......if you
are struggling with the multi-country, multi-currency pieces of a model
you would greatly improve your skills by reading some of ralphs books
and looking at the databases on the CD in the back.....
My personal home page is intended as a resource for beginners...so if
you (or others here) have opinions on what else is useful for beginners
I'd be happy for feedback and to put things onto my list of things to
publish...
Good luck!!
Peter
www.peternolan.com

DW design question - Dimension for currency

Hi everybody,
I struggle with a DW design issue that I hope somebody can provide a
solution for?
I have a traditional Star Schema, and are building an OLP cube on top. The
development is taken place using SQL Server 2005.
For the purpose of this question, the layout is the following:
Dimension tables:
Time: Transaction date, week, month, etc.
Product: Product Category, Product Line, Product.
Customer: Customer, Customer Segment, etc.
Fact table:
Sales: Transaction date, Customer, Product, Price, Currency, price in
currency, Exchange rate.
Requirement:
I am looking for sales by product by customer/product etc. (This is not a
problem). However, each of the sales transactions is made in a particular
currency. For example is sales transaction 1 in Euro and transaction 2 is in
British Pounds. Both transactions are for Product A. On top of the existing
dimensions, I need to see each of the sales transactions in a given currency
and exchange rate (for example Actual or budgeted exchange rate).
One scenario could be to see the value of sales by product (Product A) in
Euro. In this case will transaction 2 that was made in GBP have to be
converted to EUR, so I can see total sales for product A in Euro.
Solution so far:
So far, have I have added the combination of exchange rate and currency to
the fact table. For example, the transaction in currency "GBP" and "EUR" for
both the exchange rate "Actual" and "Budget2005". This gives 4 entries for
each sales transaction in the fact table. This does NOT work... Selecting on
the dimension tables, for example on the product lead to the value of the 4
transactions to be added together. :-( Where in fact I only want the unique
transaction based on dimension and chosen Currency and Exchange rate.
I have considered making another dimension for Currency and Exchange rate,
but it is not possible to create a relationship to the fact table as the
values Currency and Exchange rate can not make a unique relation to the fact
table.
So far my best bet is only to have one unique entry of the sales transaction
in the original currency in the Fact table. But how do I then make it
possible to see the sales transaction by relevant dimension in the chosen
Currency and Exchange rate?
Look forward to hearing from you.
Best Regards,
BigalexxHi Bigalexx,
Thanks for your post.
This question appears to be consulting in nature. We would also like to
introduce you to the CSS Advisory Services team.
Advisory Services is a remotely delivered, hourly fee-based, consultative
support option that provides a comprehensive result beyond your break-fix
product maintenance needs. It is an hourly fee-based, consultative
support option that provides proactive support beyond your break-fix
product maintenance needs. This support option includes working with the
same technician for assistance with issues like product migration, code
review, or new program development.
For more info in the US and Canada:
http://support.microsoft.com/defaul...AdvisoryService
Outside of the US/Canada:
http://support.microsoft.com/defaul...ernational.aspx
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Bigalexx,
>From your question I am guessing you are a beginner....
I have a beginners page on my web site www.peternolan.com with links on
the books to read to learn data modeling for BI systems......if you
are struggling with the multi-country, multi-currency pieces of a model
you would greatly improve your skills by reading some of ralphs books
and looking at the databases on the CD in the back.....
My personal home page is intended as a resource for beginners...so if
you (or others here) have opinions on what else is useful for beginners
I'd be happy for feedback and to put things onto my list of things to
publish...
Good luck!!
Peter
www.peternolan.com