customize template.ini to upgrade from msde to express edition?

I need to create the script to upgrade the current named instance of sql 2000 desktop engine to sql server 2005 express edition. I am having difficulty preparing the template.ini and would appreciate help.

[Options]
INSTANCENAME=MY_INSTANCE
SQLBROWSERACCOUNT=NT AUTHORITY\NETWORK SERVICE
SECURITYMODE=SQL
SAPWD=music
UPGRADE=SQL_Engine
SQLBROWSERAUTOSTART=1
RSCANINSTALLDEFAULT=0
RSCONFIGURATION=FilesOnly
RSSQLLOCAL=0

I get an error that this file is invalid..Any clues? I am trying to upgrade existing instance of msde 2k to sql express 2005 and uses sql authentication

http://www.microsoft.com/sql/editions/express/upgrade.mspx

http://www.microsoft.com/technet/prodtechnol/sql/2005/msde2sqlexpress.mspx

http://msdn2.microsoft.com/en-us/ms143491.aspx

|||

When a parameter includes space characters, you need quote with " or '. For example, the browser account should be specified as SQLBROWSERACCOUNT="NT AUTHORITY\NETWORK SERVICE " or SQLBROWSERACCOUNT='NT AUTHORITY\NETWORK SERVICE'

Note, this account is localized on some localized operating system. You need to use the localized version if required. In addition, MSDE already has its SP4. So please use MSDE SP4 directly.

The following are two command lines that may be useful to you.

1. Install MSDE SP4.

start /wait setup.exe /qb INSTANCENAME=msde4instance SAPWD="<password>" SECURITYMODE=SQL

2. Upgrade to SQL 2005 SP1.

start /wait setup.exe UPGRADE=SQL_Engine INSTANCENAME=msdesp4instance SAPWD=""<password>" SECURITYMODE=SQL

Custom Groups

I'm wondering if there is any way in RS to create what Crystal reports calls Custom or Named Groups. This functionality allows you to define groups based on the values found in one of the fields on your report. For example, if I were grouping on a person's last name and I want 3 groups based on the first letter: A-G, H-P, Q-Z. I would want to keep it contained in one table.RS also has a concept of groups, and the construction of groups is very flexible. Groups are usedion DataRegion, such as Table, Matrix and Lists. Groups can be be grouped, filtered, sorted using multiple expressions. These expression can be just a field value, or they can be more complex.

In your case, you could create a group that uses an expression to return 1 if the value starts with A-G, return 2 if the value starts with H-P, and so on. The expression would look something like this, it assumes only upper case ASCII characters.

=Switch("ABCDEFGH".Contains(Fields!Name.Value.Substring(0,1)), 1, "IJKLMNOP".Contains(Fields!Name.Value.Substring(0,1)), 2, "QRSTUVWXYZ".Contains(Fields!Name.Value.Substring(0,1)), 3)

or

(use 64 since Choose uses the 1-based index into the array)

=Choose(Convert.ToInt32(Fields!Name.Value.Chars(0)) - 64, {1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,3,3,3,3,3,3,3,3,3,3})

If you want something a little cleaner, then I would use a custom function that takes the first character as an argument and determines the appropriate value using regular control statements.|||

Thanks Ian!

Instead of editing the report, I decided to just add a field to my SQL statement to group on. It looks something like this:

select case
when left(lname,1) between 'a' and 'h' then 'A-H'
when left(lname,1) between 'i' and 'p' then 'I-P'
when left(lname,1) between 'q' and 'z' then 'Q-Z'
end as NameType from employee

I then created a group on the NameType field.

This worked great and my group headers were named properly as well.

|||I am trying something similar, but for some reason it does not like the syntax near AS. I see that it creates field called "NameType"...but does 'Employee' refer to the table the lname is coming from?|||Yes, the 'AS NameType' clause specifies the name for the result of the case statement, and 'lname' is a field in the 'Employee' table.

What is your query? Can you post it? I'm happy to take a look at it and see if there is something I can spot that would result in the syntax error.

Ian

Custom Groups

I'm wondering if there is any way in RS to create what Crystal reports calls Custom or Named Groups. This functionality allows you to define groups based on the values found in one of the fields on your report. For example, if I were grouping on a person's last name and I want 3 groups based on the first letter: A-G, H-P, Q-Z. I would want to keep it contained in one table.RS also has a concept of groups, and the construction of groups is very flexible. Groups are usedion DataRegion, such as Table, Matrix and Lists. Groups can be be grouped, filtered, sorted using multiple expressions. These expression can be just a field value, or they can be more complex.

In your case, you could create a group that uses an expression to return 1 if the value starts with A-G, return 2 if the value starts with H-P, and so on. The expression would look something like this, it assumes only upper case ASCII characters.

=Switch("ABCDEFGH".Contains(Fields!Name.Value.Substring(0,1)), 1, "IJKLMNOP".Contains(Fields!Name.Value.Substring(0,1)), 2, "QRSTUVWXYZ".Contains(Fields!Name.Value.Substring(0,1)), 3)

or

(use 64 since Choose uses the 1-based index into the array)

=Choose(Convert.ToInt32(Fields!Name.Value.Chars(0)) - 64, {1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,3,3,3,3,3,3,3,3,3,3})

If you want something a little cleaner, then I would use a custom function that takes the first character as an argument and determines the appropriate value using regular control statements.|||

Thanks Ian!

Instead of editing the report, I decided to just add a field to my SQL statement to group on. It looks something like this:

select case
when left(lname,1) between 'a' and 'h' then 'A-H'
when left(lname,1) between 'i' and 'p' then 'I-P'
when left(lname,1) between 'q' and 'z' then 'Q-Z'
end as NameType from employee

I then created a group on the NameType field.

This worked great and my group headers were named properly as well.

|||I am trying something similar, but for some reason it does not like the syntax near AS. I see that it creates field called "NameType"...but does 'Employee' refer to the table the lname is coming from?|||Yes, the 'AS NameType' clause specifies the name for the result of the case statement, and 'lname' is a field in the 'Employee' table.

What is your query? Can you post it? I'm happy to take a look at it and see if there is something I can spot that would result in the syntax error.

Ian

Custom Assembly config file and named instances of Reporting Services

We have a custom assembly that is using a config file. We park the config
file in the instance folder of RS along with the dll. We look in the
registry to find the instance. When there are multiple instances of RS I am
not identifying the correct instance to locate the correct custom assembly
config file. I think what I need to do is use Reflection or Process
libraries to identify the running instance, but haven't been able to figure
this out yet. Anyone using config files for their custom assemblies? We
are using a resource file and could place the config information in there
but that seems wrong. I could use the RS web.config...maybe but don't know
how to do that yet and what the security issues will be.
Thanks for helping,
Steve MunLeeuwI could do two things that I can think of.
1. bad: Pass in the ReportServerURL and loop through the RS instances to
find the one the custom assembly is loaded in and use that to find the
config file.
2. good: Just add the settings to the RS web.config instance. I still need
to test if this will work when running reports from rs.exe utility.
Add the following to web.config:
<appSettings>
<add key="connString" value="connection string" />
</appSettings>
Add the following to custom assembly:
using System.Configuration;
ConfigurationSettings.AppSettings["connString"]
Steve MunLeeuw
"Steve MunLeeuw" <smunson@.clearwire.net> wrote in message
news:e$ys4gs9GHA.4224@.TK2MSFTNGP02.phx.gbl...
> We have a custom assembly that is using a config file. We park the config
> file in the instance folder of RS along with the dll. We look in the
> registry to find the instance. When there are multiple instances of RS I
> am not identifying the correct instance to locate the correct custom
> assembly config file. I think what I need to do is use Reflection or
> Process libraries to identify the running instance, but haven't been able
> to figure this out yet. Anyone using config files for their custom
> assemblies? We are using a resource file and could place the config
> information in there but that seems wrong. I could use the RS
> web.config...maybe but don't know how to do that yet and what the security
> issues will be.
> Thanks for helping,
> Steve MunLeeuw
>|||Ooops, used deprecated method. Here's the updated code:
http://smunson.blogspot.com/
Steve MunLeeuw
"Steve MunLeeuw" <smunson@.clearwire.net> wrote in message
news:%23QsUD%23t9GHA.4408@.TK2MSFTNGP02.phx.gbl...
>I could do two things that I can think of.
> 1. bad: Pass in the ReportServerURL and loop through the RS instances to
> find the one the custom assembly is loaded in and use that to find the
> config file.
> 2. good: Just add the settings to the RS web.config instance. I still
> need to test if this will work when running reports from rs.exe utility.
> Add the following to web.config:
> <appSettings>
> <add key="connString" value="connection string" />
> </appSettings>
> Add the following to custom assembly:
> using System.Configuration;
> ConfigurationSettings.AppSettings["connString"]
>
> Steve MunLeeuw
>
> "Steve MunLeeuw" <smunson@.clearwire.net> wrote in message
> news:e$ys4gs9GHA.4224@.TK2MSFTNGP02.phx.gbl...
>> We have a custom assembly that is using a config file. We park the
>> config file in the instance folder of RS along with the dll. We look in
>> the registry to find the instance. When there are multiple instances of
>> RS I am not identifying the correct instance to locate the correct custom
>> assembly config file. I think what I need to do is use Reflection or
>> Process libraries to identify the running instance, but haven't been able
>> to figure this out yet. Anyone using config files for their custom
>> assemblies? We are using a resource file and could place the config
>> information in there but that seems wrong. I could use the RS
>> web.config...maybe but don't know how to do that yet and what the
>> security issues will be.
>> Thanks for helping,
>> Steve MunLeeuw
>

cursors in named query

hi...

In DSV, i am creating a "named Query", can we use 'cursors' while creating a named query ?

Thanks in advance ..

I don't believe so. Essentially the rule for named queries is that it has to be valid SQL that we can execute arbitrarily and append an ORDER BY clause at the end.