Dynamic Page size

hi,
I am facing a problem of dynimically changing he size of a crystal report.
Let me define it like this : I have designed a report for a A3 page and now they want me design the same report to be printed in A3 and A4 . Is there any way that we can pass aurguments and change the size of data dynimicallyCant you use Printer collection property of Printers?
You need to do this in the Front End Applicationsql

Dynamic filedlength of varchar fields in source

I'm using SSIS to import data from a table (SQL) containing varchar fields. The problem is, that those varchar fields are changing over time (sometimes shrinking and sometimes expanding). I.e. from varchar(16) to varchar(20).

When I create my SSIS package, the package seem to store information about the length of each source-field. At runtime, if the field-length is larger then what the package expects an error is thown.

Is there anyway around this problem?

Oh, yeah... My destination fields are a lot wider then the source fields, so the problem is not that the varchar values doesn't fit in my destination table, but that the package expects the source to be smaller...

Regards Andreas

You cannot dynamically change the metadata in an SSIS package -- it needs to be fixed at design time.

You could try casting the source fields to the size they will be on the destination, and hope that the source fields never exceed this size. Or, you could castthe source fields to DT_TEXT so that length can be variable, but performance might suffer (there is extra processing going on for long-object types like DT_TEXT).

Thanks
Mak

|||

A. Brosten wrote:

I'm using SSIS to import data from a table (SQL) containing varchar fields. The problem is, that those varchar fields are changing over time (sometimes shrinking and sometimes expanding). I.e. from varchar(16) to varchar(20).

When I create my SSIS package, the package seem to store information about the length of each source-field. At runtime, if the field-length is larger then what the package expects an error is thown.

Is there anyway around this problem?

Oh, yeah... My destination fields are a lot wider then the source fields, so the problem is not that the varchar values doesn't fit in my destination table, but that the package expects the source to be smaller...

Regards Andreas

You can change the SSIS package so that the external metadata stored within there is large enough for all eventualities.

-Jamie

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 database in Web Service

I have a question about changing the database source dynamically in a Web Service. I have created a Web Service for a Crystal Report using VS .NET 2003. I use Crystal Reports Web Viewer to display the report in an ASP.NET page. My database is an Access database and I have a copy of the report source database for each unique user to the Web page. I read the post on changing the database dynamically but it seems to be changed on the Crystal Reports viewer and not in the Crystal report which would be done in the Web Service. I have tried setting debug stops in the code behind module in the Web Service but the debugger does not seem to reach the breakpoints. Can you offer any advice on modifying the database connections dynamically in the Web Service?I have a similar problem. I can set the viewer db properties when i declare a web service but i cant use report engine object model. Can you tell me how you have accomplished that|||I found it fairly easy to bind to a single database. I merely opened the Crystal Report in Visual Studio .NET. In the Report Design view, I right click on the Database Fields in the Field Explorer and Select "Set Location". I defined an ODBC connection to the database and bind the report to that. Turning the report into a Web Services was easy as well. I added the report to a project that I was using to define Web Services. Then right click on the project and select Publish the report as a Web Service. Visual Studio does all the rest. Unforunately all of this is a design/build time and I haven't figured out how to change the database during runtime.

Hope this helps you.|||thanks for the reply. However that is not my problem. I want to be able to export these reports as well. Exporting code only works from report engine object model and you cant use it from client application if your reports are published as web service. That is my complication. I can set parameters, db changes at runtime when i do web services. Still thanks for the reply|||I'm sorry. I guess I did not understand the question about the the report engine object model. I also am not sure what you are referring to on exporting the reports. I haven't done anything beyond the defaults that you get when Visual Studio creates the Web Service except to connect it to the Crystal Reports Web Viewer in the ASPX pages. What did you mean when you said you can set parameters, db changes at runtime through web services? My main question is how to specify a different database file(Access database) at runtime.|||This is how u do it with web services and a strored procedure as your data source. If you use tables as data source. loop through all the tables and set the propetries of the tables(uid,pwd,dbname, servername)
Hope this helps.
Exporting questions is i have to export the reports in crystal to excel, pdf, word format.That can rbe done using viewer object model and i have to do it in web services but i dont know how to modify the engine in the web service code. I have the same problem you have. U cant debug any code in web services

Dim mytablelogoninfos As New CrystalDecisions.Shared.TableLogOnInfos
Dim mytablelogoninfo As New CrystalDecisions.Shared.TableLogOnInfo
Dim myconnectioninfo As New CrystalDecisions.Shared.ConnectionInfo

With myconnectioninfo
.UserID = "uid"
.ServerName = "servername"
.Password = "pwd"
.DatabaseName = "dbname"
End With

mytablelogoninfo.TableName = "cspAppointmentsReport;1"
With mytablelogoninfo.ConnectionInfo
.UserID = "uid"
.Password = "pwd"
End With
mytablelogoninfo.ConnectionInfo = myconnectioninfo
mytablelogoninfos.Add(mytablelogoninfo)
CrystalReportViewer1.LogOnInfo = mytablelogoninfos
CrystalReportViewer1.ReportSource = New localhost.AppointmentSubReportService|||Now I understand what you mean by exporting and no I haven't tried anything like that yet. Sorry I don't think I can help you.

Thanks for the sample code. I will see if I can make it work for what I'm trying to do. I appreciate the help.

Good luck solving your problem.

Dynamic Connection String Problem

I need to be able to deploy my updated website to many customers on a monthly basis and dont want to be mucking around changing the connection strings each time. Some of my web servers have multiple copies of my site and DB so each website will need a different connection string.

The simplest method I could come up with is to use the Application Name field in IIS as it doesn't get overwritten by Visual Studio when I deploy the site.

I am trying to write some code to dynamically change the connection string in the web config but cannot find any way of reading the Application Name field in IIS to use in altering the connection string. I'm using the Global.asax file to change the connection string before the DB gets called.

I had tried embedding the DB in the website folder but it would overwrite the customers database.

Hi Matthew,

The only way that comes to me is that we can write a small tool that parses the web.config file as xml, and change the connection string accordingly.

HTH. If this does not answer you question, please feel free to mark it as Not Answered and post your reply. Thanks!

|||

I searched and searched and eventually found this snippet but it the last line doesn't work for me. For other people it has worked and I'm not sure what is wrong with my setup:

' Create a connectionn string element and add it to the connection strings section.Sub ChangeConnectionString(ByVal cs_nameAs String,ByVal cs_valueAs String)Try Dim webConfigAs System.Configuration.Configuration = WebConfigurationManager.OpenWebConfiguration("~")Dim dbConnStringAs ConnectionStringsSection = webConfig.ConnectionStrings'// To change existing connectionstring dbConnString.ConnectionStrings(cs_name).ConnectionString = cs_value webConfig.Save()Catch exAs ExceptionEnd Try End Sub

|||

Hi Matthew,

Could you show us the exception message?

|||

The exception is:

{System.Configuration.ConfigurationErrorsException}

An error occurred loading a configuration file: Access to the path 'C:\inetpub\wwwroot\flo_dev\6egcsvyu.tmp' is denied. (C:\inetpub\wwwroot\flo_dev\web.config)

Hope that spreads some light on what it might be.

|||

Hi Matthew,

If you're using an ASP.NET app to do this, please check if the account ASPNET do have permission to write to that file.

|||

I will check the rights of the ASPNET user. If the ASPNET user has rights to modify that file wont that allow anyone to alter the file? Or just server-side code?

Dynamic chart labels (changing the colors on the fly)

I have a chart that presently has two groupings on the x-axis: Year & Qtr. I'd like the Year labels to be one color, and the Qtr labels to be a different color. (All year labels should be Black, all Qtr labels should be Blue). I can see that an expression can be written to handle this dynamically, but I'm banging my head trying to figure it out...

Any help?

Thanks in advance,
Pete

1. Right click the field and select properties.

2. Click the point labels tab.

3. Click the label style button.

4. In the color expression, enter something like this:

=IIf(Fields!Year.Value = 1900, "Black", "Black")

Do the same for the Qtr field.

=IIf(Fields!Qtr.Value = " ", "Blue", "Blue")

The expressions may not be 100% accurate, but if you play around with this, you should get what you want.

Dynamic chart labels (changing the colors on the fly)

I have a chart that presently has two groupings on the x-axis: Year & Qtr. I'd like the Year labels to be one color, and the Qtr labels to be a different color. (All year labels should be Black, all Qtr labels should be Blue). I can see that an expression can be written to handle this dynamically, but I'm banging my head trying to figure it out...

Any help?

Thanks in advance,
Pete

1. Right click the field and select properties.

2. Click the point labels tab.

3. Click the label style button.

4. In the color expression, enter something like this:

=IIf(Fields!Year.Value = 1900, "Black", "Black")

Do the same for the Qtr field.

=IIf(Fields!Qtr.Value = " ", "Blue", "Blue")

The expressions may not be 100% accurate, but if you play around with this, you should get what you want.

dynamic aliases/columns

Hi
Is it possible to return a table that its aliases are changing according to
varaiables ?
For example: I would like to use something like (ofcourse it doesn't work).
declare @.p1,@.p2 varchar(30)
set @.p1 ='blhablha'
set @.p2 ='hghfg'
select field1 as @.p1, field2 as @.p2 from table1Only with dynamic SQL (sp_executesql or exec). Most of the time,
however, dynamic SQL is not really a terrific idea. Here is Erland
Sommarskog's page on dynamic SQL (a frequently referenced page):
http://www.sommarskog.se/dynamic_sql.html
I can't imagine why you'd actually want to do this. What are you trying
to achieve?
*mike hodgson*
blog: http://sqlnerd.blogspot.com
romy wrote:

>Hi
>Is it possible to return a table that its aliases are changing according to
>varaiables ?
>For example: I would like to use something like (ofcourse it doesn't work)
.
>
>declare @.p1,@.p2 varchar(30)
>set @.p1 ='blhablha'
>set @.p2 ='hghfg'
>select field1 as @.p1, field2 as @.p2 from table1
>
>|||rommy
if changing alias is only problem
you can use the trick of if condition in sp of CASE in query.Post your
script to suggest you better.
Regards
R.D
"romy" wrote:

> Hi
> Is it possible to return a table that its aliases are changing according t
o
> varaiables ?
> For example: I would like to use something like (ofcourse it doesn't work
).
>
> declare @.p1,@.p2 varchar(30)
> set @.p1 ='blhablha'
> set @.p2 ='hghfg'
> select field1 as @.p1, field2 as @.p2 from table1
>
>|||While I can't think of why you would need to do this on the server,
you could use put the results into a table, rename the columns
with sp_rename (which accepts parameters), then select the contents
of the table.
declare
@.cname1 sysname,
@.cname2 sysname
set @.cname1 = N'lName'
set @.cname2 = N'ID'
select LastName, EmployeeID
into #tmp
from Northwind..Employees
exec tempdb..sp_rename N'#tmp.LastName', @.cname1, 'COLUMN'
exec tempdb..sp_rename N'#tmp.EmployeeID', @.cname2, 'COLUMN'
select * from #tmp
drop table #tmp
Steve Kass
Drew University
romy wrote:

>Hi
>Is it possible to return a table that its aliases are changing according to
>varaiables ?
>For example: I would like to use something like (ofcourse it doesn't work)
.
>
>declare @.p1,@.p2 varchar(30)
>set @.p1 ='blhablha'
>set @.p2 ='hghfg'
>select field1 as @.p1, field2 as @.p2 from table1
>
>

Dyanamic date changing to current date when printing PDF

I have a dynamic date in my reporting services report that displays
the previous month and year we are reporting on. Here is the
expression: =MonthName(Month(Now().AddMonths(-1))) & " " &
Year(Now())
When I create a pdf from this it displays correctly, but when I print
it, it always puts the current month and year in this field. Does
anyone know why and how to prevent it from doing this when I print the
pdf?
Thanks for any help you can give.
-CarlyJust to clarify, you create a pdf then print the pdf from within Adobe
Acrobat and while the pdf file you see on the screen has September
2007, for instance, the printed pdf will have October 2007?
Is that correct? You're printing the pdf, not printing the report
directly from Report Manager?
- C
On Oct 5, 10:22 am, caroline.fe...@.sdncommunications.com wrote:
> I have a dynamic date in my reporting services report that displays
> the previous month and year we are reporting on. Here is the
> expression: =MonthName(Month(Now().AddMonths(-1))) & " " &
> Year(Now())
> When I create a pdf from this it displays correctly, but when I print
> it, it always puts the current month and year in this field. Does
> anyone know why and how to prevent it from doing this when I print the
> pdf?
> Thanks for any help you can give.
> -Carly