Dynamic Message Box In Report

Hello Sir,

I m working in Microsoft SQL Server Analysis Services Designer
Version 9.00.1399.00 for creating reports .

Now, I m facing a problem to view a run time dialog box at time of my report.
Means ,
In my report procedure , i have fired 2 delete queries after firing of these queries i wanna show a message box with Message "Some Records are Deleted". and one Ok Button !!

and after showing this dialog box i wanna view my report output !!

The procedure of my report is :
--
create procedure MyTemp
as

-- Query1
delete from myTempTable1 where Id = 5

-- Query2
delete from myTempTable2 where Id = 10

-- After exectuing above two queries Query1 and Query2.
-- here i want to view amessage dialog box with message "Some Records are Deleted"

select Id, name from myTempTable1,myTempTable2
--

So, how can i do this !!
I will change in my proceudre or report !!
Please suggest me about solution of this problem .

If u dont have any solution regaring this then please suggest me where i will get
the solution of this problem .

There is no messagebox in reporting services. Yout will have to give the results back as a dataset and render it in the report if your want to display it.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Dynamic From clause?

How can I get an SP to use one table in the From clause if a condition is true and another table if false? I'd like to avoid creating a "dynamic query," one in which the whole query is built in a string then executed (exec str).

Thank you.

There isn't a really good way.

To avoid Dynamic SQL, the only option is to use an IF statement in your stored procedure to send processing to one select or the other.

If the tables are very similar, you can combine them into a single VIEW and query against that, adjusting the WHERE clause instead (which supports variables).

-Ryan / Kardax

|||That's what I was afraid of... Thank you.|||

You can use 2 separate queries for each condition and combine them with UNION. The fields in the SELECT statement must be similar in this case. Here is an example:

Code Snippet

SELECT col1, col2, col3

FROM Table1

WHERE Condition1

UNION

SELECT col1, col2, col3

FROM Table2

WHERE Condition2

I hope this answers your questions.

Best regards,

Sami Samir

Dynamic DSN in Report

I am creating a dynamic DSN in a report to pick which database to run a query against. I have a fairly simple expression,

="Data Source=MYSQLSERVER;Initial Catalog=ADV_" & Parameters!DBNum.Value

When I try to preview the report, I get the following error

An unexpected error occured while compling expressions. Native complier return value: '[BC32017] Comma, ')', or a valid expression continuation expected.'.

I have also tried it without the parameter,

="Data Source=MYSQLSERVER;Initial Catalog=ADV_1"

with the same result. When I use the exact same static DSN it works fine. Anyone have any idea what I might try to get it to work next?

R

Hi Ron,

I hate to reply when I don't have an actual solution but oh well. I too am having this exact same problem. I am using OLE DB with provider=MSOLAP.3. I had no problem doing this when I was using SQL Server Analysis Services 9 connection.

Did you ever solve this problem?

Brian Welcker blogged about this at http://blogs.msdn.com/bwelcker/archive/2005/04/29/413343.aspx#470856 and also another similar thing at http://blogs.msdn.com/bwelcker/archive/2005/07/03/435130.aspx. Neither was very helpful to our problem though.

I found it interesting that he showed the actual XML from the RDL file in the first post. He was using & instead of & which may be a hint - perhaps our connection strings contain a character which is confusing the native compiler? But your connection string above, and the one I am using do not contain any abnormal characters... where do we find the list of invalid XML characters?.

|||

Yes, I did get it to work. I messed around with alot of different ways, but ended up using String.Format. It worked quite well.

R

|||

Hi Ron,

I'm having the same error, may you explain a bit how did you manage to make it work using String.Format?

|||

I used

=String.Format("Data Source=MYSQLSERVER;Initial Catalog={0};", Parameters!DBNAME.Value)

The parameter DBNAME had the database name I wanted to use. The downside is that you can no longer run data queries directly in VBSTUDIO. You have to actually preview the report.

If you have troubles, take it one step at a time. First try,

="Data Source=MYSQLSERVER;Initial Catalog=DBNAME;"

then go one more step,

=String.Format("Data Source=MYSQLSERVER;Initial Catalog=DBNAME;",String.Empty)

then,

=String.Format("Data Source=MYSQLSERVER;Initial Catalog={0};","DBNAME")

lastly,

=String.Format("Data Source=MYSQLSERVER;Initial Catalog={0};", Parameters!DBNAME.Value)

Whenever I have trouble with these strings (DSN and dynamic SQL) I always back up and take baby steps forward. I find when I get impatient and just jump to the last step, I have missed something.This usually catches it.

R

|||

Thanks a lot Ron,

I was tryng your baby steps method but I got stuck on the very first one.

If I put

="Data Source=MYSQLSERVER;Initial Catalog=DBNAME;"

in the connection string field of the dataset, it ends up with the same error ([BC32017] Comma..)

Are you entering your connection string somewhere else than the specific field of the dataset? For example in the custom code field, as I noticed that I can't use String.Format in there.

|||

Update:

It looks like that is a problem connecting to Analysis Services, in fact if I try to use the string to connect to SQL Server, it works fine.

Any idea to connect to AS using a dynamic connection string?

|||

Found out that the problem was a space in the Datasource Name.

Now it works fine with AS datasource as well.

Dynamic DSN in Report

I am creating a dynamic DSN in a report to pick which database to run a query against. I have a fairly simple expression,

="Data Source=MYSQLSERVER;Initial Catalog=ADV_" & Parameters!DBNum.Value

When I try to preview the report, I get the following error

An unexpected error occured while compling expressions. Native complier return value: '[BC32017] Comma, ')', or a valid expression continuation expected.'.

I have also tried it without the parameter,

="Data Source=MYSQLSERVER;Initial Catalog=ADV_1"

with the same result. When I use the exact same static DSN it works fine. Anyone have any idea what I might try to get it to work next?

R

Hi Ron,

I hate to reply when I don't have an actual solution but oh well. I too am having this exact same problem. I am using OLE DB with provider=MSOLAP.3. I had no problem doing this when I was using SQL Server Analysis Services 9 connection.

Did you ever solve this problem?

Brian Welcker blogged about this at http://blogs.msdn.com/bwelcker/archive/2005/04/29/413343.aspx#470856 and also another similar thing at http://blogs.msdn.com/bwelcker/archive/2005/07/03/435130.aspx. Neither was very helpful to our problem though.

I found it interesting that he showed the actual XML from the RDL file in the first post. He was using & instead of & which may be a hint - perhaps our connection strings contain a character which is confusing the native compiler? But your connection string above, and the one I am using do not contain any abnormal characters... where do we find the list of invalid XML characters?.

|||

Yes, I did get it to work. I messed around with alot of different ways, but ended up using String.Format. It worked quite well.

R

|||

Hi Ron,

I'm having the same error, may you explain a bit how did you manage to make it work using String.Format?

|||

I used

=String.Format("Data Source=MYSQLSERVER;Initial Catalog={0};", Parameters!DBNAME.Value)

The parameter DBNAME had the database name I wanted to use. The downside is that you can no longer run data queries directly in VBSTUDIO. You have to actually preview the report.

If you have troubles, take it one step at a time. First try,

="Data Source=MYSQLSERVER;Initial Catalog=DBNAME;"

then go one more step,

=String.Format("Data Source=MYSQLSERVER;Initial Catalog=DBNAME;",String.Empty)

then,

=String.Format("Data Source=MYSQLSERVER;Initial Catalog={0};","DBNAME")

lastly,

=String.Format("Data Source=MYSQLSERVER;Initial Catalog={0};", Parameters!DBNAME.Value)

Whenever I have trouble with these strings (DSN and dynamic SQL) I always back up and take baby steps forward. I find when I get impatient and just jump to the last step, I have missed something.This usually catches it.

R

|||

Thanks a lot Ron,

I was tryng your baby steps method but I got stuck on the very first one.

If I put

="Data Source=MYSQLSERVER;Initial Catalog=DBNAME;"

in the connection string field of the dataset, it ends up with the same error ([BC32017] Comma..)

Are you entering your connection string somewhere else than the specific field of the dataset? For example in the custom code field, as I noticed that I can't use String.Format in there.

|||

Update:

It looks like that is a problem connecting to Analysis Services, in fact if I try to use the string to connect to SQL Server, it works fine.

Any idea to connect to AS using a dynamic connection string?

|||

Found out that the problem was a space in the Datasource Name.

Now it works fine with AS datasource as well.

Dynamic DSN in Report

I am creating a dynamic DSN in a report to pick which database to run a query against. I have a fairly simple expression,

="Data Source=MYSQLSERVER;Initial Catalog=ADV_" & Parameters!DBNum.Value

When I try to preview the report, I get the following error

An unexpected error occured while compling expressions. Native complier return value: '[BC32017] Comma, ')', or a valid expression continuation expected.'.

I have also tried it without the parameter,

="Data Source=MYSQLSERVER;Initial Catalog=ADV_1"

with the same result. When I use the exact same static DSN it works fine. Anyone have any idea what I might try to get it to work next?

R

Hi Ron,

I hate to reply when I don't have an actual solution but oh well. I too am having this exact same problem. I am using OLE DB with provider=MSOLAP.3. I had no problem doing this when I was using SQL Server Analysis Services 9 connection.

Did you ever solve this problem?

Brian Welcker blogged about this at http://blogs.msdn.com/bwelcker/archive/2005/04/29/413343.aspx#470856 and also another similar thing at http://blogs.msdn.com/bwelcker/archive/2005/07/03/435130.aspx. Neither was very helpful to our problem though.

I found it interesting that he showed the actual XML from the RDL file in the first post. He was using & instead of & which may be a hint - perhaps our connection strings contain a character which is confusing the native compiler? But your connection string above, and the one I am using do not contain any abnormal characters... where do we find the list of invalid XML characters?.

|||

Yes, I did get it to work. I messed around with alot of different ways, but ended up using String.Format. It worked quite well.

R

|||

Hi Ron,

I'm having the same error, may you explain a bit how did you manage to make it work using String.Format?

|||

I used

=String.Format("Data Source=MYSQLSERVER;Initial Catalog={0};", Parameters!DBNAME.Value)

The parameter DBNAME had the database name I wanted to use. The downside is that you can no longer run data queries directly in VBSTUDIO. You have to actually preview the report.

If you have troubles, take it one step at a time. First try,

="Data Source=MYSQLSERVER;Initial Catalog=DBNAME;"

then go one more step,

=String.Format("Data Source=MYSQLSERVER;Initial Catalog=DBNAME;",String.Empty)

then,

=String.Format("Data Source=MYSQLSERVER;Initial Catalog={0};","DBNAME")

lastly,

=String.Format("Data Source=MYSQLSERVER;Initial Catalog={0};", Parameters!DBNAME.Value)

Whenever I have trouble with these strings (DSN and dynamic SQL) I always back up and take baby steps forward. I find when I get impatient and just jump to the last step, I have missed something.This usually catches it.

R

|||

Thanks a lot Ron,

I was tryng your baby steps method but I got stuck on the very first one.

If I put

="Data Source=MYSQLSERVER;Initial Catalog=DBNAME;"

in the connection string field of the dataset, it ends up with the same error ([BC32017] Comma..)

Are you entering your connection string somewhere else than the specific field of the dataset? For example in the custom code field, as I noticed that I can't use String.Format in there.

|||

Update:

It looks like that is a problem connecting to Analysis Services, in fact if I try to use the string to connect to SQL Server, it works fine.

Any idea to connect to AS using a dynamic connection string?

|||

Found out that the problem was a space in the Datasource Name.

Now it works fine with AS datasource as well.

Dynamic destination address in SSIS packages

Hi,

I am using VS.net 2003 as a front end and SQL server 2005 backend.

i am creating SSIS packages for Datatransformation programically in .NET.

but the package created is compatible to the previous version of SQL server ie SQL server 2000.

So i need to migrate it in SSIS package compatible to SQL server 2005.

it is migrate also using Data Transformation migration wizard.

But i want to migrate my DTS package programically or by using stored procedure.

Is there any stored procedure or any code is there from which i can migrate DTS into SSIS ?

Thank you

Hi Sanjay,

For what I've read from microsofts webpages about migrating from DTS to SSIS it is far from a simpel process that can be done 100% automatic - some task (especially activex tasks) can not be migrated without human interviention - there is a migration tool that can help you identify what problems you will have and how to solve them. The program is called "Microsoft SQL Server 2005 Upgrade Advisor"

Regards
Simon
|||

Thank you Simon for the replay,

Is there any process from which i can change the desitination address in the package dynamically,If i create my package using business development intelligent studio integrated service.

Every time the destination changes by any means when we require.

or should i am able to create SSIS packages using VS.NET 2003 compatible to SQL server 2005?

Thank you

Sanjay

|||

Hi sanjay,

In Visual Studio 2003, We cannot create or even open SSIS Packages.

I do not understand what do you mean by "change the desitination address in the package dynamically"

Thanks

Subhash Subramanyam

|||

i dont create or even open SSIS packages in VS 2003,

But i am able to run SSIS in SQL server 2005 by scheduling him in SQL jobs..

As i am using SQL server 2005 so i am able to create the SSIS package in SQL server 2005 Integration service.

I am creating it for the data transformation task, but my destination changes.

ie. i want send data from one server to multiple server.

But the data sent through the server is different.ie, no server are getting the same data.

ie. i want to desgin one to many tronsformation in SSIS.

My source server is constant always but the destination server address is saved dynamically from the programme in made in VS2003.

it means the address of destination are multiple and changes whenever it required to.

how i can do it?

I hope you understand my problem.

Thank you.

|||

Sanjay wrote:

i dont create or even open SSIS packages in VS 2003,

But i am able to run SSIS in SQL server 2005 by scheduling him in SQL jobs..

As i am using SQL server 2005 so i am able to create the SSIS package in SQL server 2005 Integration service.

This is not quite right - you are able to RUN SSIS packages from within SQL Server 2005 Intergration Services. If you want to create packages that are not trivial (like the export function in Management Studio) you need to use Visual Studio 2005 (VS2005). If you don't have it, you should be able to install it from the "Clients Components" along with Management Studio.

Sanjay wrote:

I am creating it for the data transformation task, but my destination changes.

ie. i want send data from one server to multiple server.

This is possible with VS2005. You can use dynamically destinations - ex. get values from a table or flat file.

Sanjay wrote:

My source server is constant always but the destination server address is saved dynamically from the programme in made in VS2003.

How is the address saved?
|||

should i am able to run DTS package without migrating into SSIS by scheduling him in SQL jobs in SQL server 2005?

|||

Sanjay wrote:

should i am able to run DTS package without migrating into SSIS by scheduling him in SQL jobs in SQL server 2005?

You can create a SQL Agent Job with a CmdExec (command line) step type. You just need to provide the command line to execute your DTS package.

|||

Sanjay wrote:

should i am able to run DTS package without migrating into SSIS by scheduling him in SQL jobs in SQL server 2005?

Under "SQL Server Management Studio" if you connect to the SQL server you will find: Management>Legacy>Data Transformation Services

Right click the folder at choose import - if you have it as a file.

If you can run it directly from the filesystem I don't remember but you can try it out.

Dynamic Database Connection

Hi,

I done some Crystal reports(10.0) in asp .net using SQL server 2000(DB). While creating the reports, OLE DB Connection Information is must. Now i want to change the Source and Database for all the Reports. I want to do it Dynamically. Could I ?. I want to create reports without giving these(Source and Database) all. That is for each reports I dont want to give Source,Database,User Id and Password.
Pls help me in this regard.almost same problem with mine..
but mine is in report environment in vb6.

i'm not sure, but in vb6 (it think its not quite different :) )
u can do such this ..
firstly u must make a reference to the crystal report.
next...
--create a new Crystal Report object ...
it may looks like this

dim cr as new crystal report object

i think there must be a method of that CR object that could add a new report

hope this gonna help at least give a clue.. since i'm not that expert in .NET tech ..

good luck|||Hi szpilman,
The Problem is while creating the reports we have to give the Source and Database, But we should not do in this method. While creating the reports we should point one thing( It may be DSN).For this , All the reports should point out one (DSN(applies for VB .NET)). Here I want to point out all the reports to one( like DSN). Whenever I want to Change i should change in this Pointed one( like DSN ). Like DSN "i want one", where i can change the Source and Database dynamically.
Thank U

Dynamic crosstab query in MS SQL Server 2000

Hello all!
I have a problem with creating crosstab query in MS SQL Server 2000. I
spent 8 hours on searching internet to achieve my succes but without
result. I would like to transform such data:
MRPController WK Value
C01 200505 1
C01 200505 1
C02 200505 2
C03 200506 4
C03 200506 7
C04 200505 1
C04 200507 5
into:
MRPController 200505 200506 200507
C01 2
C02 2
C03 4
C04 1 5
The data are updated once a w, that`s why I need a dynamic crosstab
query which let me receive such query in MS SQL Server 2000. I found out
that it is no so easy to create such cross tab query in MS SQL Server
2000, but I am wondering why it is so easy even in MS Access 1997 and
Excel 1997, and it is so tough case in MS SQL Server released in 2000. I
have search newsgroups, but I didn`t find anything whcih could help me.
I found some SQL procedures but they didn`t work. I heard that in MS SQL
Server 2005 there is a special function who let do it, but I have MS SQL
Server 2000 and I need to do this in this version on SQL Server. Is it
possible to do it? Is it some correct method to do it. Please be so kind
and help, but I already don`t know what to do and it is very wanted
query in my company. I didn`t think that I stuck on such query.
Thank you in advance for your help
I really apprieciate it
Marcin from Poland
*** Sent via Developersdex http://www.examnotes.net ***Take a look at this link
[url]http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21205811.html[/ur
l]
I use the transform proc just as you described with great results. You
will have to make a few small modifications so the date is labeled to
your likeing. I have mine labled (Month Year i.e. March 2005).
GL|||Dynamic Crosstab Queries
http://www.windowsitpro.com/SQLServ...5608/15608.html
Dynamic Cross-Tabs/Pivot Tables
http://www.sqlteam.com/item.asp?ItemID=2955
AMB
"Marcin Zmyslowski" wrote:

>
> Hello all!
> I have a problem with creating crosstab query in MS SQL Server 2000. I
> spent 8 hours on searching internet to achieve my succes but without
> result. I would like to transform such data:
> MRPController WK Value
> C01 200505 1
> C01 200505 1
> C02 200505 2
> C03 200506 4
> C03 200506 7
> C04 200505 1
> C04 200507 5
> into:
> MRPController 200505 200506 200507
> C01 2
> C02 2
> C03 4
> C04 1 5
> The data are updated once a w, that`s why I need a dynamic crosstab
> query which let me receive such query in MS SQL Server 2000. I found out
> that it is no so easy to create such cross tab query in MS SQL Server
> 2000, but I am wondering why it is so easy even in MS Access 1997 and
> Excel 1997, and it is so tough case in MS SQL Server released in 2000. I
> have search newsgroups, but I didn`t find anything whcih could help me.
> I found some SQL procedures but they didn`t work. I heard that in MS SQL
> Server 2005 there is a special function who let do it, but I have MS SQL
> Server 2000 and I need to do this in this version on SQL Server. Is it
> possible to do it? Is it some correct method to do it. Please be so kind
> and help, but I already don`t know what to do and it is very wanted
> query in my company. I didn`t think that I stuck on such query.
> Thank you in advance for your help
> I really apprieciate it
> Marcin from Poland
> *** Sent via Developersdex http://www.examnotes.net ***
>