Deserialization failed: error message

This is the message I get when I try to create a report that has a lot of fields, I'm guessing that might be the problem.

Warning 1 Deserialization failed: The value must be between 0cm and 406.34921cm.
Parameter name: Width Line 10, position 30. 10 0

When you say lots of field how many are you talking about I wrote one that generated 165 valid pages with many concatenated columns.

|||

Thanks for the reply.

54 fields so doubt that is the issue.

|||sorry wrong query; the query involved returns 623 fields lol I think I will have to cut it down

|||

I don't think you are getting that many fields from a database, if you are then you need to design it because if you do files and association the core of relational design 623 can be reduced to less than 100 fields. Hope this helps.

|||

Not my database and yes I am getting that many fields as I counted them.

Thanks for the replies I have sent the query back to the provider, will see what happens.

|||

I think you are getting Excel files in database files because relational design reduces such bloat to manageable tables and columns. Post again later because with design you can move the columns to a view and just call the view for that report.

sql

Deserialization failed

I am installing SSRS for the first time. I tried to create a simple report and I am getting the following error:

Deserialization failed: The type initializer for 'Microsoft.ReportDesigner.Drawing.RptStyleConstValue' threw an exception. Line 2, position 2.

Does anyone know how to fix this error?

Thanks,

Patrick

You have a problem http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=811976&SiteID=1 sql

Descending keys in MSSQL7

I try the following index in SQL7
create index i1 on tab1 (f1 asc, f2 desc)
and I get an error. If I type the following:
create index i1 on tab1 (f1, f2 desc)
the index is created but the descending index is ignored. Can anyone point me in the right direction ?Features introduced with SQL2K, not supported in version 7,
"Lawrence" <lawrence@.magicsoftware.com> wrote in message
news:BB978427-F24F-4D10-9BA9-EC7D21B89CD9@.microsoft.com...
> I try the following index in SQL7
> create index i1 on tab1 (f1 asc, f2 desc)
> and I get an error. If I type the following:
> create index i1 on tab1 (f1, f2 desc)
> the index is created but the descending index is ignored. Can anyone point
me in the right direction ?

Desc as column name

Hi,
When I run the following sql statement
CREATE TABLE t1 (Desc varchar(50))
I get an error saying Incorrect syntax near keyword Desc.
However, I can run the same command on other database engines.
Where can I find situations like this where an sql statement will work
on other databases but not SqlServer?
The statement
CREATE TABLE t1 ([Desc] varchar(50))
works fine.
Do you suggest that I enclose column names in square brackets ([]) for
all sql statements (select, insert, update, create, delete etc)?
Regards,
PrakashSince you mention other database engines, you should get into the habit of using the ANSI SQL
compliant way to delimit identifiers: double-quotes and not square brackets.
There's a list in Books Online of all the reserved keywords.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<prakash_bande@.hotmail.com> wrote in message
news:1189098048.395246.253570@.w3g2000hsg.googlegroups.com...
> Hi,
> When I run the following sql statement
> CREATE TABLE t1 (Desc varchar(50))
> I get an error saying Incorrect syntax near keyword Desc.
> However, I can run the same command on other database engines.
> Where can I find situations like this where an sql statement will work
> on other databases but not SqlServer?
> The statement
> CREATE TABLE t1 ([Desc] varchar(50))
> works fine.
> Do you suggest that I enclose column names in square brackets ([]) for
> all sql statements (select, insert, update, create, delete etc)?
>
> Regards,
> Prakash
>|||Desc is a reserved keyword in SQL Server. It's short for 'descending' and is
used in syntax such as ORDER BY column_name DESC. If you want to use
reserved words as object or column names, you must delimit the name by
either using [ ] as you show below are by using double quotes " ". Keep in
mind that you will need to delimit this name in every statement in which you
reference the name. You might consider just choosing a longer (and more
descriptive) name such as "Description".
For a list of reserved keywords, see this Books Online topic:
http://msdn2.microsoft.com/en-us/library/ms189822.aspx
For more information about delimiting names see this Books Online topic
http://msdn2.microsoft.com/en-us/library/ms176027.aspx
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
<prakash_bande@.hotmail.com> wrote in message
news:1189098048.395246.253570@.w3g2000hsg.googlegroups.com...
> Hi,
> When I run the following sql statement
> CREATE TABLE t1 (Desc varchar(50))
> I get an error saying Incorrect syntax near keyword Desc.
> However, I can run the same command on other database engines.
> Where can I find situations like this where an sql statement will work
> on other databases but not SqlServer?
> The statement
> CREATE TABLE t1 ([Desc] varchar(50))
> works fine.
> Do you suggest that I enclose column names in square brackets ([]) for
> all sql statements (select, insert, update, create, delete etc)?
>
> Regards,
> Prakash
>sql

Derived column not processing any records

Hi,

I have built a package in which i use a derived column to create a new set of columns and then pass the same to another target transformation.

The issue now what I am facing is, the re are certain number of records coming from source(16 rows) and gets processed before the Derived Column transformation, but after that, no records gets processed after the derived column transformation.

The package status shows as Success, but there is no records being written in the target table.

Any idea what could be the issue here?

Thanks,

Manish

Have you used data viewers on the data flow to see why records are not "passing" through? The records HAVE to go through the derived column -- it can't stop them.

Are you positive the records are not coming out of the derived column transformation?|||

Are no records being written to your destination, or you're just missing the derived values?

If the latter, I'd also check your column mappings in your destination (or any transformations downstream of your Derived Column Trx and upstream of your destination) to make sure your new columns are mapped. This is particularly important if you added the Derived Column Trx later in the development of your data flow. The mappings won't automatically appear.

|||

Hi Phil,

Thanks for the response.

Yes, I have put data viewers before and after the derived column transformation. The data viewer before the transformation shows the 13 records being passed, but the data viewer after the transformation doesnt show up any record, it is blank. I have tried many option in the mapping, but no luck.

I am now re-creating the mapping to check if the issue persists.

THough, let me know if you can think of any other scenario which could help in resolving this.

Thanks,

Manish

|||

ManishSingh wrote:

Hi Phil,

Thanks for the response.

Yes, I have put data viewers before and after the derived column transformation. The data viewer before the transformation shows the 13 records being passed, but the data viewer after the transformation doesnt show up any record, it is blank. I have tried many option in the mapping, but no luck.

I am now re-creating the mapping to check if the issue persists.

THough, let me know if you can think of any other scenario which could help in resolving this.

Thanks,

Manish

What mapping? There are no mappings in the derived column transformation.|||By mapping I was referring to the package. I tried various options to delete transformation and add another etc in the package to check for the data flow|||

ManishSingh wrote:

By mapping I was referring to the package. I tried various options to delete transformation and add another etc in the package to check for the data flow

Make a copy of your package for this test.

Leave everything as is, but remove EVERYTHING AFTER the derived column transformation and add a row count transformation. Hook it up to the derived column transformation. This will be your new destination. You'll need to add a variable for the row counter, of course.

Run the package. What happens? Does the derived column pass all rows through now?|||

Phil,

Thanks for your response. I was out of office, so couldnt reply back.

I tried adding the row count as the destination after the derived column transformation, and it was running successfully. Then again, I removed the row count and added the transformation again, and it started working. I am not sure, what could be the issue, but now its running fine.

Thanks for all your help. Really appreciate it!!!!!!!!

sql

Derived column not processing any records

Hi,

I have built a package in which i use a derived column to create a new set of columns and then pass the same to another target transformation.

The issue now what I am facing is, the re are certain number of records coming from source(16 rows) and gets processed before the Derived Column transformation, but after that, no records gets processed after the derived column transformation.

The package status shows as Success, but there is no records being written in the target table.

Any idea what could be the issue here?

Thanks,

Manish

Have you used data viewers on the data flow to see why records are not "passing" through? The records HAVE to go through the derived column -- it can't stop them.

Are you positive the records are not coming out of the derived column transformation?|||

Are no records being written to your destination, or you're just missing the derived values?

If the latter, I'd also check your column mappings in your destination (or any transformations downstream of your Derived Column Trx and upstream of your destination) to make sure your new columns are mapped. This is particularly important if you added the Derived Column Trx later in the development of your data flow. The mappings won't automatically appear.

|||

Hi Phil,

Thanks for the response.

Yes, I have put data viewers before and after the derived column transformation. The data viewer before the transformation shows the 13 records being passed, but the data viewer after the transformation doesnt show up any record, it is blank. I have tried many option in the mapping, but no luck.

I am now re-creating the mapping to check if the issue persists.

THough, let me know if you can think of any other scenario which could help in resolving this.

Thanks,

Manish

|||

ManishSingh wrote:

Hi Phil,

Thanks for the response.

Yes, I have put data viewers before and after the derived column transformation. The data viewer before the transformation shows the 13 records being passed, but the data viewer after the transformation doesnt show up any record, it is blank. I have tried many option in the mapping, but no luck.

I am now re-creating the mapping to check if the issue persists.

THough, let me know if you can think of any other scenario which could help in resolving this.

Thanks,

Manish

What mapping? There are no mappings in the derived column transformation.|||By mapping I was referring to the package. I tried various options to delete transformation and add another etc in the package to check for the data flow|||

ManishSingh wrote:

By mapping I was referring to the package. I tried various options to delete transformation and add another etc in the package to check for the data flow

Make a copy of your package for this test.

Leave everything as is, but remove EVERYTHING AFTER the derived column transformation and add a row count transformation. Hook it up to the derived column transformation. This will be your new destination. You'll need to add a variable for the row counter, of course.

Run the package. What happens? Does the derived column pass all rows through now?|||

Phil,

Thanks for your response. I was out of office, so couldnt reply back.

I tried adding the row count as the destination after the derived column transformation, and it was running successfully. Then again, I removed the row count and added the transformation again, and it started working. I am not sure, what could be the issue, but now its running fine.

Thanks for all your help. Really appreciate it!!!!!!!!

Derived Column in CREATE TABME

Hi champs,
I am kicking my self for the syntax for a derived column in a CREATE TABME
-statement.
I just wnat a extra colum that is the result of colum1+colum2
..
CREATE TABLE [dbo].[test](
[colum1] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[colum2] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[colum3] = [colum1] + [colum2] [nchar](10)
) ON [PRIMARY]
what is the correct syntax?
/many thanksKurlan
CREATE TABLE [dbo].[test](
[colum1] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[colum2] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[colum3] AS [colum1] + [colum2]
) ON [PRIMARY]
"Kurlan" <Kurlan@.discussions.microsoft.com> wrote in message
news:7D8CBBB0-4538-47C9-833F-450D689879E2@.microsoft.com...
> Hi champs,
> I am kicking my self for the syntax for a derived column in a CREATE TABME
> -statement.
> I just wnat a extra colum that is the result of colum1+colum2
> ..
> CREATE TABLE [dbo].[test](
> [colum1] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [colum2] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [colum3] = [colum1] + [colum2] [nchar](10)
> ) ON [PRIMARY]
> what is the correct syntax?
>
> /many thanks|||CREATE TABLE [dbo].[test](
[colum1] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[colum2] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[colum3] as [colum1] + [colum2]
) ON [PRIMARY]
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Kurlan" wrote:

> Hi champs,
> I am kicking my self for the syntax for a derived column in a CREATE TABME
> -statement.
> I just wnat a extra colum that is the result of colum1+colum2
> ..
> CREATE TABLE [dbo].[test](
> [colum1] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [colum2] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [colum3] = [colum1] + [colum2] [nchar](10)
> ) ON [PRIMARY]
> what is the correct syntax?
>
> /many thanks|||Kurlan a écrit :
> Hi champs,
> I am kicking my self for the syntax for a derived column in a CREATE TABME
> -statement.
> I just wnat a extra colum that is the result of colum1+colum2
> ..
> CREATE TABLE [dbo].[test](
> [colum1] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [colum2] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
==> [colum3] = [colum1] + [colum2] [nchar](10)
> ) ON [PRIMARY]
> what is the correct syntax?
>
> /many thanks
The SQL type must not be explicit. It will be implicit by calculating
the SQL type needed by the expression.
Remember that this type of columns are computed while running the SELECT.
A +
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||>> CREATE TABLE [dbo].[test](
>==> [colum3] = [colum1] + [colum2] [nchar](10)
>The SQL type must not be explicit. It will be implicit by calculating
>the SQL type needed by the expression.
If, for some reason, there is a requirement for an explicit data type,
this can be achieved by adding a CONVERT to the expression:
[colum3] = CONVERT(nchar(10), [colum1] + [colum2])
Roy Harvey
Beacon Falls, CTsql

Derived column based on result of Oracle query

Hi,

I need to create a derived column for each row in a SQL dataset.

This derived column needs to be created by passing across two values from the SQL dataset and querying an Oracle table based on those parameters. If the Oracle query returns a record(s) then the derived column should be set to 1 otherwise leave it as default (0).

One of these parameters needs to check a date range so I can't use a Lookup Transformation...any ideas how I can accomplish this ?

Thanks

Here is one way to do this...

-Have one source for the sql source and another source for the oracle source.

-Send them both to a merge join, choose a left outer join with the sql source as the left input, choose one column from the oracle source.

-After the merge join use a derived column transformation. Use an expression to say that if the field from the oracle source is null then 0 else 1.

edit: After re-reading your post, the date range makes things much more difficult. It may be best to send both sources to staging tables in sql server, and then write run a query to join them together perhaps a (correlated subquery with a case statement in the select clause).

|||

Hi,

I am not sure if this will solve your problem completely:

In a Data Flow Task pull the two values from the SQL dataset into the Recordset Destination.

Now, in the Control Flow Task, use a For Each Loop Container to store the Recordset into a variable (you will have to create variables for each field that you want to write to the destination as well as one for the recordset).

Loop through the recordset and for each pair of values, use a Excecute SQL Task to execute a SQL query or a Stored Procedure which will return matching record(s). Now based on whether a record was returned or not, write all the fields, plus the derived column value into the destination.

Regards,

B@.ns

|||

Thanks guys - I actually used a combination of your techniques to get the result.

|||I just wanted to point out that you can use a Lookup to do this. You just need to go the the advanced tab, enable memory restriction, and you can alter the query used for the lookup to include ranges.sql

Derived Column

I have two columns made up of 4 digits numbers eg col1 1234, col2 9876

I want to create a derived column so i get a eight digit column, eg col3 12349876

I cannot seem to get the expression right, I seem to be always adding the value which i do not want, can someone help me out with the expression

Thanks

Robbie

if you just use the '+' operator, the values would get added. To fix that just cast the values to string before concatenating them.

something like:

(DT_STR,4,1252)col1 + (DT_STR,4,1252)col2

|||

Worked like a dream thanks,

One day I will understand all this !!!!

|||test|||test agauin|||test gain, this must be my last|||

Please mark the thread as answered...

thanks

|||

Note that if your 4-digit numbers could begin with one or more zeros, you would need to do a bit more to ensure the result is 8 digits. For example, 1234 and 0056 would result in 123456 using the above expression. If you actually want 12340056, you would need to do something like this:

RIGHT("0000" + (DT_WSTR,4)col1, 4)) + (RIGHT("0000" + (DT_WSTR,4)col2, 4)

Wrap that whole expression in a cast back to an integer type if that is what you need.

|||

Rafael Salas wrote:

Please mark the thread as answered...

thanks

This is done how?

|||

Mark Durley wrote:

Note that if your 4-digit numbers could begin with one or more zeros, you would need to do a bit more to ensure the result is 8 digits. For example, 1234 and 0056 would result in 123456 using the above expression. If you actually want 12340056, you would need to do something like this:

RIGHT("0000" + (DT_WSTR,4)col1, 4)) + (RIGHT("0000" + (DT_WSTR,4)col2, 4)

Wrap that whole expression in a cast back to an integer type if that is what you need.

Thanks mark this is great stuff, I have another problem but will create a new thread

|||

Personally, I would use

(col1 * 10000) + col2

- If you want to keep the leading zero's, that is.

Pipo1

|||

Pipo1 wrote:

Personally, I would use

(col1 * 10000) + col2

- If you want to keep the leading zero's, that is.

Pipo1

A much simpler (and more efficient) solution, especially if you want to end up with a number still :)

|||

Mark Durley wrote:

Pipo1 wrote:

Personally, I would use

(col1 * 10000) + col2

- If you want to keep the leading zero's, that is.

Pipo1

A much simpler (and more efficient) solution, especially if you want to end up with a number still :)

I may be wrong...but that expression will keep leading zeros for col2 only...what if you want to keep the leading zeros of col1?

|||

Rafael Salas wrote:

I may be wrong...but that expression will keep leading zeros for col2 only...what if you want to keep the leading zeros of col1?

You'll lose them. That's why I like using the right() function.|||

Phil Brammer wrote:

Rafael Salas wrote:

I may be wrong...but that expression will keep leading zeros for col2 only...what if you want to keep the leading zeros of col1?

You'll lose them. That's why I like using the right() function.

Thanks Phil for confirming that. It is good to know I am not crazy

Derived attributes in a dimension

SQL 2005 SSAS - is it possible to create a derived attribute in a Dimension or does it have to be done in the underlying table.

Specifically, I have a geographic location Dimension where the top level is "Area" (USGulf, USWestCoast, Caribbean etc). Now there is one part of the company that likes to group these together into Zones. I can see how to do this in SQL in the underlying table or in reports in SSRS, but ideally would like to do this in the cube.

Thanks

You could add a Named Calculation for Zone to the dimension table in the Data Source View. This would be a SQL expression - the underlying table structure wouldn't change.

Deprecation: What does it mean?????????

(-)CREATE DATABASE with FOR LOAD
I get this exemption with this rule:
Rule: CREATE DATABASE with FOR LOAD
Category
Deprecation
Description
This rule checks stored procedures, functions, views and triggers for use of
CREATE DATABASE statement specifying the FOR LOAD clause.
FOR LOAD clause has been deprecated and will not be supported in a future
release of SQL Server. RESTORE statement provides option to recreate a
database.
This rule:
?Only scans databases specified in the Database List of a registered SQL
Server instance.
?Scans trace files associated with a registered SQL Server instance.
?Does not check databases that are not online.
Parameters
Deprecated features are those that are slated to be unsupported in a future
release.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Garry D" <GarryD@.discussions.microsoft.com> wrote in message
news:CF76BC86-FAA8-4EAA-B60E-C215BF7675E7@.microsoft.com...
(-)CREATE DATABASE with FOR LOAD
I get this exemption with this rule:
Rule: CREATE DATABASE with FOR LOAD
Category
Deprecation
Description
This rule checks stored procedures, functions, views and triggers for use of
CREATE DATABASE statement specifying the FOR LOAD clause.
FOR LOAD clause has been deprecated and will not be supported in a future
release of SQL Server. RESTORE statement provides option to recreate a
database.
This rule:
? Only scans databases specified in the Database List of a registered SQL
Server instance.
? Scans trace files associated with a registered SQL Server instance.
? Does not check databases that are not online.
Parameters

Deploying to subfolders from VS

I've got a report project with a few reports in I want in the root target directory. I also want to create several folders and put reports in those. The problem is I don't see any way to create a report folder in VS. I also don't see any way to pick the deployment path of a single reprot in VS. This makes me think I'll have to create a different Report Project in VS for each folder!! (this sucks). Does anyone have an idea or suggestion here?

Sorry, creating subfolder hierarchies directly from within a VS report project is currently not supported.

The closest you can get is to write a little application using the RS SOAP API to deploy your reports - you will then have full control over the folder hierarchy.

-- Robert

|||

This is highly unfortunate.

It makes deploying reports a real pain
because of the manual nature of having
to organize and overwrite older reports
(after deployment) that are existing in
sub-folders.

Can anyone provide some sources on
where to get started if one were to try
to write a custom deployment app?

|||

This link is to a tool that will give you some insight on how to deploy reports programmatically. It is not an automated process itself, but will give you an idea how it could be done.

http://www.sqldbatips.com/showarticle.asp?ID=62

I am creating a C# application that will use a small database for deployment data. With 3K+ remote servers, we have the same issue.

I originally thought I would use a method described elsewhere in the forum where you always deploy to one hidden folder and link all of your reports to their visible spots. I am not yet sure of the ramifications of that yet. If anyone has done deployments in this method, please let me know.

R

|||

Well, if you have any linked reports with landscape orientation, they will, more than likely, insist on printing portrait. Just something to consider. Another thread covers this:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=137986&SiteID=1

Even with the small glitches, SRS is a pleasure to work with.

|||So far the easiest way I've found to deal with this problem is to simply deploy all the reports in a project to the report server in a single place. Then, using the sql server management studio I can create folders and move reports into the proper location. It is not the best solution, but it works. It is a real hassle when updating reports. I just don't understand why microsoft did not make it possible to have report folders inside of visual studio. It would have taken an extra 10 minutes to program!
|||I agreed! Logical grouping is the most fundamental user function that you can find in all other MS or non MS products, but why not this one? We have 100+ reports, this has forced me to use some sort of special naming convention (Initiative - Category - subcategory - report name) for report name so that it's easier for me to find the reports in a flat list! Painful is to say the least...

Deploying to subfolders from VS

I've got a report project with a few reports in I want in the root target directory. I also want to create several folders and put reports in those. The problem is I don't see any way to create a report folder in VS. I also don't see any way to pick the deployment path of a single reprot in VS. This makes me think I'll have to create a different Report Project in VS for each folder!! (this sucks). Does anyone have an idea or suggestion here?

Sorry, creating subfolder hierarchies directly from within a VS report project is currently not supported.

The closest you can get is to write a little application using the RS SOAP API to deploy your reports - you will then have full control over the folder hierarchy.

-- Robert

|||

This is highly unfortunate.

It makes deploying reports a real pain
because of the manual nature of having
to organize and overwrite older reports
(after deployment) that are existing in
sub-folders.

Can anyone provide some sources on
where to get started if one were to try
to write a custom deployment app?

|||

This link is to a tool that will give you some insight on how to deploy reports programmatically. It is not an automated process itself, but will give you an idea how it could be done.

http://www.sqldbatips.com/showarticle.asp?ID=62

I am creating a C# application that will use a small database for deployment data. With 3K+ remote servers, we have the same issue.

I originally thought I would use a method described elsewhere in the forum where you always deploy to one hidden folder and link all of your reports to their visible spots. I am not yet sure of the ramifications of that yet. If anyone has done deployments in this method, please let me know.

R

|||

Well, if you have any linked reports with landscape orientation, they will, more than likely, insist on printing portrait. Just something to consider. Another thread covers this:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=137986&SiteID=1

Even with the small glitches, SRS is a pleasure to work with.

|||So far the easiest way I've found to deal with this problem is to simply deploy all the reports in a project to the report server in a single place. Then, using the sql server management studio I can create folders and move reports into the proper location. It is not the best solution, but it works. It is a real hassle when updating reports. I just don't understand why microsoft did not make it possible to have report folders inside of visual studio. It would have taken an extra 10 minutes to program!
|||I agreed! Logical grouping is the most fundamental user function that you can find in all other MS or non MS products, but why not this one? We have 100+ reports, this has forced me to use some sort of special naming convention (Initiative - Category - subcategory - report name) for report name so that it's easier for me to find the reports in a flat list! Painful is to say the least...

Deploying to subfolders from VS

I've got a report project with a few reports in I want in the root target directory. I also want to create several folders and put reports in those. The problem is I don't see any way to create a report folder in VS. I also don't see any way to pick the deployment path of a single reprot in VS. This makes me think I'll have to create a different Report Project in VS for each folder!! (this sucks). Does anyone have an idea or suggestion here?

Sorry, creating subfolder hierarchies directly from within a VS report project is currently not supported.

The closest you can get is to write a little application using the RS SOAP API to deploy your reports - you will then have full control over the folder hierarchy.

-- Robert

|||

This is highly unfortunate.

It makes deploying reports a real pain
because of the manual nature of having
to organize and overwrite older reports
(after deployment) that are existing in
sub-folders.

Can anyone provide some sources on
where to get started if one were to try
to write a custom deployment app?

|||

This link is to a tool that will give you some insight on how to deploy reports programmatically. It is not an automated process itself, but will give you an idea how it could be done.

http://www.sqldbatips.com/showarticle.asp?ID=62

I am creating a C# application that will use a small database for deployment data. With 3K+ remote servers, we have the same issue.

I originally thought I would use a method described elsewhere in the forum where you always deploy to one hidden folder and link all of your reports to their visible spots. I am not yet sure of the ramifications of that yet. If anyone has done deployments in this method, please let me know.

R

|||

Well, if you have any linked reports with landscape orientation, they will, more than likely, insist on printing portrait. Just something to consider. Another thread covers this:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=137986&SiteID=1

Even with the small glitches, SRS is a pleasure to work with.

|||So far the easiest way I've found to deal with this problem is to simply deploy all the reports in a project to the report server in a single place. Then, using the sql server management studio I can create folders and move reports into the proper location. It is not the best solution, but it works. It is a real hassle when updating reports. I just don't understand why microsoft did not make it possible to have report folders inside of visual studio. It would have taken an extra 10 minutes to program!
|||I agreed! Logical grouping is the most fundamental user function that you can find in all other MS or non MS products, but why not this one? We have 100+ reports, this has forced me to use some sort of special naming convention (Initiative - Category - subcategory - report name) for report name so that it's easier for me to find the reports in a flat list! Painful is to say the least...

Deploying SQL Database

Hi All,

Can anyone tell me,is it possible to create an exe or msi for sql stored procedures,tables and triggers?I want to deploy the database objects(stored proc,tables,views and functions) as an exe file..just like publishing and deploying the asp.net application.Is it possible for sql server database objects.Pls,let me know.

Thank U[:)]Usually database with all objects gets backed up and then restored on another Server. It is the best and safest way to do it.

You also can extract scripts of all objects and then run them on another Server using exe or any means which can connect to that Server.

Good Luck.

Deploying reports on 2000 Reporting Serve

We have RS 2000 runing. Can we create reports in RS 2000 using Visual Studio 2005?No, the editor in VS 2005 uses the new RDL schema. VS 2003 and 2005 should run side by side.

Deploying report into a folder

Hello,

I deploy my report through deploy command in the menu. If I create a folder in the report manager how can deploy my report into the folder?

Thanks,

Go to Project Prperties in that you have to type in the newly createdfolder name in the 'TargetFolder' also check the TargetServerURL.
This would solve your issue.

Deploying RDL file Programatically

Hi,

I am able to create a rdl file programatically.I want to deploy the RDL file programatically in Report Server.Any help is appreciated.

Thanks,

Prabu

Hi Prabu,

I came across this link previously concerning about your question. It is writtent in c# though, so you might need some time to "translate" it to VB. Other than that, it will teach ya to do what you need to do.

http://www.codeproject.com/sqlrs/SQLReportDeploy.asp

Good luck !

|||

Hi,

Thanks for you help.

I am using web application.Using that I am creating an RDL file.And I want to deploy the RDL file using Web application not through console application.

The link is having the information to deploy using Windows/Console application.

Please help me in this Regard.

Thanks,

Prabu

|||

Hi Prabu

Actually the link i posted here should show you the basics on deploying the report

regardless of what application you are writing.

Look under the function deploy and you will see what i mean. YOu will definitely need to modify some parts so that it points to the webserver. that link will help to get the information you need about the datasource and then deploy it from there. Other than that, it should work for what you are doing.

Just take a bit of tracing. good luck

|||

Hi,

Fine.I tried that code.If anyone is having code sample in web application is really helpfull.

Urgent!

Thanks,

Prabu

|||

Hi,

You can use the RS.EXE for deploying your reports programmatically. You have to create a .RSS file [Reporting Services Script

file] and use the functions available to deploy your reports.

Vasant.

|||

Hi,

I tried and how to call a exe in web application.If anyone is having any Idea about to deploy Report using webservice in Web application ...

I tried a lot and I need it urgent.

Thanks,

Prabu

|||Which of the web application are you using, It might have the functionality to execute a command. The rs.exe file is located in the

c:\program Files\Microsoft SQL Server\90\BINN\

folder. There are various parameters to the rs command. Just for your knowledge see below. At the bottom you will see how rs.exe is used. Njoy!!

Syntax

rs {-?}
[-iinput_file=]
[-sserverURL]
{-uusername}
{-ppassword}
{-ltime_out}
{-bbatchmode}
{-vglobalvars=}
{-eendpoint}
{-ttrace}
Arguments
-?
(Optional) Displays the syntax of rs arguments.
-i input_file
(Required) Specifies the .rss file to execute. This value can be a relative or fully qualified path to the .rss file.
-s serverURL
(Required) Specifies the Web server name and report server virtual directory name to execute the file against. An example of a report server URL is http://examplewebserver/reportserver. The prefix http:// or https:// at the beginning of the server name is optional. If you omit the prefix, the report server script host attempts to use https first, and then uses http if https does not work.
-u [domain\]username
(Optional) Specifies a user account used to connect to the report server. If -u and -p are omitted, the current Windows user account is used.
-p password
(Required if -u is specified) Specifies the password to use with the -u argument. This value is case-sensitive.
-l time_out
(Optional) Specifies the number of seconds that elapse before the connection to the server times out. The default is 60 seconds. If you do not specify a time-out value, the default is used. A value of 0 specifies that the connection never times out.
-b
(Optional) Specifies that the commands in the script file run in a batch. If any commands fail, the batch is rolled back. Some commands cannot be batched, and those run as usual. Only exceptions that are thrown and are not handled within the script result in a rollback. If the script handles an exception and returns normally from Main, the batch is committed. If you omit this parameter, the commands run without creating a batch. For more information, see Batching Methods.
-e
(Optional) Specifies the SOAP endpoint against which the script should run. Valid values include: Exec2005, Mgmt2005, and Mgmt2000. If a value is not specified, the Mgmt2005 endpoint is used. Note that the Mgmt2000 value is deprecated in this release. For more information about the SOAP endpoints and deprecated features, see Report Server Web Service Endpoints and Deprecated Features in SQL Server 2005 Reporting Services.
-v globalvar
(Optional) Specifies global variables that are used in the script. If the script uses global variables, you must specify this argument. The value that you specify must be valid for global variable defined in the .rss file. You must specify one global variable for each –v argument.
The -v argument is specified on the command line and is used to set the value for a global variable that is defined in your script at run time. For example, if your script contains a variable named parentFolder, you can specify a name for that folder on the command line:
rs.exe -i myScriptFile.rss -s http://myServer/reportserver -v parentFolder="Financial Reports"
Global variables are created with the names given and set to the values supplied. For example, -v a="1" -v b="2" results in a variable named a with a value of "1" and a variable b with a value of "2".
Global variables are available to any function in the script. A backslash and quotation mark (\") is interpreted as a double quotation mark. The quotation marks are required only if the string contains a space. Variable names must be valid for Microsoft Visual Basic; they must start with alphabetic character or underscore and contain alphabetic characters, digits, or underscores. Reserved words cannot be used as variable names. For more information about using global variables, see Using Global Collections in Expressions (Reporting Services).
-t
(Optional) Outputs error messages to the trace log. This argument does not take a value. For more information, see Reporting Services Trace Logs.
Permissions
To run the tool, you must have permission to connect to the report server instance you are running the script against. You can run scripts to make changes to the local computer or a remote computer. To make changes to a report server installed on a remote computer, specify the remote computer in the -s argument.
Examples
The following example illustrates how to specify the script file that contains Visual Basic .NET script and Web service methods that you want to execute.

Copy Code

rs –i c:\scriptfiles\script_copycontent.rss -s http://localhost/reportserver

Deploying multi-user sql express database to SQL Express Server

Okay, I've read massive topics on ClickOnce, and embedded database applications, etc, etc.

I need a handful of good suggestions how I can create a deployment package, to get my sql express database onto a SQL Express server.

The database will need to be multi-user, because I am also developing a WinForms application to connect to the SQL Express database.

I'm thinking I need to use some automated scripting features, to generate scripts for the database once I am done (unless you have a better suggestion).

After that, I need to know what to do with those scripts, and how can I create a setup / deployment packages to run those scripts against a SQL Express server.

The other alterntative obviously is to copy my sql express database, and run an attach command. This will work the first time, only because this will have been the first deployment of this database.

Please keep in mind when answering this question, that I will not be embedding the database, and as far as I understand ClickOnce is a feature for use with embedded databases or user instance databases (not sure).

Daniel Crane

Ping!

Is anybody reading these posts?

|||

Dan,

You may find these sources useful.

SQL Server 2005 UnAttended Installations
http://msdn2.microsoft.com/en-us/library/ms144259.aspx
http://msdn2.microsoft.com/en-us/library/bb264562.aspx
http://www.devx.com/dbzone/Article/31648

|||

Hmmm... Thanks Arnie this information is helpful to a degree.

I was hoping to get more information on how to create an automated installation, like Microsoft does, when distributing database samples. Something I could just run on a SQL Server, and have the installation "install" the database.

Right now, if I want to do something similar with an application installation. I would create a VB.net project, write the code, test, etc. Then I would create a deployment package.

What would I do if I want to have a "database" project. Am not referring to the actual Database project in VS.NET Pro. I want to be able to create a database, probably script it out entirely, then create an "installation" package.

Right now all the info I have says buy a 3rd party tool, because Microsoft stuff doesn't give us the ability to do this, unless it's a user-instance express database (which is not the same as multi-user).

that's a bit frustrating, because I am a one man shop, and would like to be able to manage larger projects for my customers. Without having to buy Team Enterprise for Database Developers (or whatever it's called). I love Microsoft, but they seem to keep the single person, or small team from having access to the tools that we really need, unless we are willing to pay the same price as huge corporations.

I've found a relatively low cost tool called EasySql Deployment, but I was hoping to do it "the Microsoft way". Please let me know if you have any additional suggestion, because I do appreciate the input!!

Thanks again!

|||

OK, digging a little deeper, perhaps these are more to your needs.

Deploy Database with MSI
http://msdn.microsoft.com/msdnmag/issues/04/09/customdatabaseinstaller/

Deploy Database with Application

http://www.codeproject.com/useritems/Deploy_your_database.asp

|||

Thanks for that extra info! The CodeProject article really helps!

Deploying multiple projects

Is it possible to deploy multiple Analysis Services projects into one OLAP database, or you have to create all cubes into same project?

Thanks from answers!

Peter

Hello. The problem is that you will overwrite the existing cubes and other objects with the latest project that you deploy to an existing database. I think the answere is no.

Regards

Thomas Ivarsson

|||

Jeah, that is problem, but I thought there is trick, so unfortunately there isn't..

Thanks.

|||

If it would work you could have problems with name collisions like dimensions with the same name. I also tried copy paste from one project in BIDS to another, still without any luck.

Regards

Thomas Ivarsson