Adding "All" value to parameter field

Hi,
I just want to know if is it possible to add a "ALL" value to the drop down
list of a parameter field?
I can't seem to manually add the "ALL" value after I specify that the
parameter's values are taken from a query.
For example, I have a parameter, "Country" and its values are taken from a
query. Now I have the country values populated. How do I add the ALL value
from here?
Thanks,Use a union in the original query to add a dummy 'All' record to your
primary result set. I've used that on many similar occasions.
Julian Bowker
Marble Steps Systems
"et_ck" <etck@.discussions.microsoft.com> wrote in message
news:0971547C-4373-4AB3-9F57-F4BCFAE44756@.microsoft.com...
> Hi,
> I just want to know if is it possible to add a "ALL" value to the drop
> down
> list of a parameter field?
> I can't seem to manually add the "ALL" value after I specify that the
> parameter's values are taken from a query.
> For example, I have a parameter, "Country" and its values are taken from a
> query. Now I have the country values populated. How do I add the ALL value
> from here?
> Thanks,|||Can we take this question one step farther? Adding a UNION will make the
word "ALL" appear in the parameter drop down list, but what technique do you
use in the report dataset to implement "ALL" in the WHERE clause?
Is there a better technique than coding:
WHERE (country = @.country OR @.country = 'ALL')
I find that I loose the ability to render the query in the GUI if I use
complex AND/OR combinations in parenthesis.
Thanks in advance.
Randy Howie
"Julian Bowker" wrote:
> Use a union in the original query to add a dummy 'All' record to your
> primary result set. I've used that on many similar occasions.
> Julian Bowker
> Marble Steps Systems
> "et_ck" <etck@.discussions.microsoft.com> wrote in message
> news:0971547C-4373-4AB3-9F57-F4BCFAE44756@.microsoft.com...
> > Hi,
> >
> > I just want to know if is it possible to add a "ALL" value to the drop
> > down
> > list of a parameter field?
> >
> > I can't seem to manually add the "ALL" value after I specify that the
> > parameter's values are taken from a query.
> >
> > For example, I have a parameter, "Country" and its values are taken from a
> > query. Now I have the country values populated. How do I add the ALL value
> > from here?
> >
> > Thanks,
>
>|||That technique is much better than just about any other that I have seen.
Saying that you might prefer to use like and have the value be the wild card
for the database you are going against (for instance % if SQL Server). I.e.
Where (country like @.country)
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Randy Howie" <Randy Howie@.discussions.microsoft.com> wrote in message
news:E849778B-9215-42B2-AC0D-702E0101B6C2@.microsoft.com...
> Can we take this question one step farther? Adding a UNION will make the
> word "ALL" appear in the parameter drop down list, but what technique do
> you
> use in the report dataset to implement "ALL" in the WHERE clause?
> Is there a better technique than coding:
> WHERE (country = @.country OR @.country = 'ALL')
> I find that I loose the ability to render the query in the GUI if I use
> complex AND/OR combinations in parenthesis.
> Thanks in advance.
> Randy Howie
> "Julian Bowker" wrote:
>> Use a union in the original query to add a dummy 'All' record to your
>> primary result set. I've used that on many similar occasions.
>> Julian Bowker
>> Marble Steps Systems
>> "et_ck" <etck@.discussions.microsoft.com> wrote in message
>> news:0971547C-4373-4AB3-9F57-F4BCFAE44756@.microsoft.com...
>> > Hi,
>> >
>> > I just want to know if is it possible to add a "ALL" value to the drop
>> > down
>> > list of a parameter field?
>> >
>> > I can't seem to manually add the "ALL" value after I specify that the
>> > parameter's values are taken from a query.
>> >
>> > For example, I have a parameter, "Country" and its values are taken
>> > from a
>> > query. Now I have the country values populated. How do I add the ALL
>> > value
>> > from here?
>> >
>> > Thanks,
>>|||Hi Julian,
Thanks for the help.
"Julian Bowker" wrote:
> Use a union in the original query to add a dummy 'All' record to your
> primary result set. I've used that on many similar occasions.
> Julian Bowker
> Marble Steps Systems
> "et_ck" <etck@.discussions.microsoft.com> wrote in message
> news:0971547C-4373-4AB3-9F57-F4BCFAE44756@.microsoft.com...
> > Hi,
> >
> > I just want to know if is it possible to add a "ALL" value to the drop
> > down
> > list of a parameter field?
> >
> > I can't seem to manually add the "ALL" value after I specify that the
> > parameter's values are taken from a query.
> >
> > For example, I have a parameter, "Country" and its values are taken from a
> > query. Now I have the country values populated. How do I add the ALL value
> > from here?
> >
> > Thanks,
>
>sql

Add/Remove Column/Row Groups dynamically in Reporting Services

Hi
Presently I'm working with OLAP and Reporting Services. In which users
are allowed to drag and drop the dimensions and the measures from the
list available for the cube , On droping of the dimension/measure on
Row/Column. I need to generate the matrix. Is it possible in RS, If it
menas, give me idea on this else notes for how to proceed with this.
Regards
Santhimurthy DessingouIf you're using SQL Server 2005, you could probably do this with the new
Report Builder tool, you just need to create a Report Model with all the
dimensions and measures you need.
Alternatively, try searching for a thread called "RS/MDX - Column Parameter
/ Field name in dataset problem"
Or if you use Google groups, you find it at
http://groups.google.no/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/642b5c0d703335b6/568564e89b6bc038?lnk=st&q=Re%3A+RS%2FMDX+-+Column+Parameter+%2F+Field+name+in+dataset+problem&rnum=1&hl=no#568564e89b6bc038
It's a suggestion on how to create a dynamic report. It's limited by a given
number of columns, but it might be possible to elaborate on the ideas to get
what you want.
Kaisa M. Lindahl Lervik
<santhimurthyd@.gmail.com> wrote in message
news:1140600388.351554.135920@.g44g2000cwa.googlegroups.com...
> Hi
> Presently I'm working with OLAP and Reporting Services. In which users
> are allowed to drag and drop the dimensions and the measures from the
> list available for the cube , On droping of the dimension/measure on
> Row/Column. I need to generate the matrix. Is it possible in RS, If it
> menas, give me idea on this else notes for how to proceed with this.
> Regards
> Santhimurthy Dessingou
>

Add/Drop LinkedServers using SMO

Anyone used SMO in SQL Server 2005 to add or drop Linked Server. I couldn't
find any sample or information on that.
ThanksHello,
I've never done this, but you probably need to use the LinkedServer
class in the Microsoft.SqlServer.Management.Smo namespace. Set the
desired properties of the LinkedServer, then use the Create method. For
more informations, see:
http://msdn2.microsoft.com/microsof...rs
.aspx
http://msdn2.microsoft.com/ms162171(en-US,SQL.90).aspx
Razvan|||Thanks for the response.
I solved the problem. If anyone interested heres the code for
Adding/Dropping Linked Servers using SMO. I use this in a Class.
public bool AddLinkServer(string SourceServer, string
DestinationServer,string UID,string PWD)
{
try
{
LinkedServer DestSrv;
ServerConnection SrvConn = new ServerConnection();
SrvConn.ServerInstance = SourceServer;
if ((UID == "") && (PWD == ""))
{
SrvConn.LoginSecure = true;
}
else
{
SrvConn.LoginSecure = false;
SrvConn.Login = UID;
SrvConn.Password = PWD;
}
Server SQLServer = new Server(SrvConn);
DestSrv = new LinkedServer(SQLServer, DestinationServer);
// Add Remote user/password for linking.
LinkedServerLogin LnkLogin = new LinkedServerLogin(DestSrv, UID);
if (!SrvConn.LoginSecure)
{
LnkLogin.RemoteUser = UID;
LnkLogin.SetRemotePassword(PWD);
LnkLogin.Create();
}
DestSrv.Create();
SrvConn.Disconnect();
return true;
}
catch
{
return false;
}
}
// drop link serverss...
public bool DropLinkServer(string SourceServer,string DropLinkServerName)
{
try
{
ServerConnection SrvConn = new ServerConnection();
SrvConn.ServerInstance = SourceServer;
SrvConn.LoginSecure = true;
Server SQLServer = new Server(SrvConn);
LinkedServerCollection LnkServerList = SQLServer.LinkedServers;
foreach (LinkedServer Lnk in LnkServerList)
{
if (Lnk.Name == DropLinkServerName)
{
Lnk.Drop(true);
break;
}
}
SrvConn.Disconnect();
return true;
}
catch
{
return false;
}
}
// Load all linked servers...
public bool LoadLinkServers(string SourceServer, ArrayList LinkServerList)
{
try
{
ServerConnection SrvConn = new ServerConnection();
SrvConn.ServerInstance = SourceServer;
SrvConn.LoginSecure = true;
Server SQLServer = new Server(SrvConn);
LinkedServerCollection LnkSrvList = SQLServer.LinkedServers;
foreach (LinkedServer Lnk in LnkSrvList)
{
LinkServerList.Add(Lnk.Name);
}
SrvConn.Disconnect();
return true;
}
catch
{
return false;
}
}
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1138559727.598122.180660@.g44g2000cwa.googlegroups.com...
> Hello,
> I've never done this, but you probably need to use the LinkedServer
> class in the Microsoft.SqlServer.Management.Smo namespace. Set the
> desired properties of the LinkedServer, then use the Create method. For
> more informations, see:
> http://msdn2.microsoft.com/microsof...
rs.aspx
> http://msdn2.microsoft.com/ms162171(en-US,SQL.90).aspx
> Razvan
>

Add a coulm in a table, after a specific column

I don't think there is a quick shortcut. The way I have normally done this
is to create a new table, move the data, drop the original table and rename
the new table to the original name.

You can use SSMS to generate a script for this (that is if you make the
change via the graphical interface in SSMS). This option is available
through Generate Change Script in the Table Designer menu or if you
right-click the table editor.

HTH,

Plamen Ratchev
http://www.SQLStudio.comYou can use SSMS to generate a script for this (that is if you make the

Quote:

Originally Posted by

change via the graphical interface in SSMS). This option is available
through Generate Change Script in the Table Designer menu or if you
right-click the table editor.
>

Thanks!!!|||On Wed, 31 Oct 2007 16:56:29 +0100, D. wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>You can use SSMS to generate a script for this (that is if you make the
>change via the graphical interface in SSMS). This option is available
>through Generate Change Script in the Table Designer menu or if you
>right-click the table editor.
>>

>
>Thanks!!!
>

But please do check, double-check, and triple-check the script before
executing it on a live server. There have historically been some serious
flaws in the scripted code used, that might result in losing all your
data if you are unlucky. Maybe things have changed since the last time I
looked, but I'd never run a script generated by Enterprise Manager or
SQL Server Management Studio without prior minute inspection!

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||>

Quote:

Originally Posted by

But please do check, double-check, and triple-check the script before
executing it on a live server. There have historically been some serious
flaws in the scripted code used, that might result in losing all your
data if you are unlucky. Maybe things have changed since the last time I
looked, but I'd never run a script generated by Enterprise Manager or
SQL Server Management Studio without prior minute inspection!
>

Ok,
could you please me tell me which part do I have to check?

I though it is the same script that Enterprise Manager launch when I save
the modification in the data structure...

D.|||D. (d@.d.com) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

>But please do check, double-check, and triple-check the script before
>executing it on a live server. There have historically been some serious
>flaws in the scripted code used, that might result in losing all your
>data if you are unlucky. Maybe things have changed since the last time I
>looked, but I'd never run a script generated by Enterprise Manager or
>SQL Server Management Studio without prior minute inspection!
>>

>
Ok,
could you please me tell me which part do I have to check?
>
I though it is the same script that Enterprise Manager launch when I save
the modification in the data structure...

EM and Mgmt Studio generate the same crap.

There are many things to watch out for:

o Transsaction scope. The script has many small transactions, but there
should be one big transaction. That or just restore a backup if there
is any error.

o Constraints are restored with NOCHECK, that should be WITH CHECK.
That takes longer time, but the flip side is that the optimiser then
can trust the constraints. This can matter a lot in some cases.

o Remove all "go" in the script, and wrap most statements in EXEC.
The way script lookas as generated, if there is a batch-aborting
error, the transaction is rolled back, and the rest of the statements
will be committed. An alternative is to wrap all batches in
IF @.@.transcount 0 BEGIN END.

o Rewiew that the script only includes the changes you intend. There
are situations where EM/SSMS may include a change that you have
abandoned.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>

Quote:

Originally Posted by

o Transsaction scope. The script has many small transactions, but there
should be one big transaction. That or just restore a backup if there
is any error.

Ok, I have to execute the DDL statements via jdbc so Ill put them inside a
unique transaction.

Quote:

Originally Posted by

>
o Constraints are restored with NOCHECK, that should be WITH CHECK.
That takes longer time, but the flip side is that the optimiser then
can trust the constraints. This can matter a lot in some cases.

Ok

Quote:

Originally Posted by

>
o Remove all "go" in the script, and wrap most statements in EXEC.
The way script lookas as generated, if there is a batch-aborting
error, the transaction is rolled back, and the rest of the statements
will be committed. An alternative is to wrap all batches in
IF @.@.transcount 0 BEGIN END.

I must remove all "go" statements because I'll execute the code via jdbc
driver.
I've not understood what do you mean with the phrase "wrap most statements
in EXEC" (sorry but I'm fairly new to SQLServer)

Quote:

Originally Posted by

>
o Rewiew that the script only includes the changes you intend. There
are situations where EM/SSMS may include a change that you have
abandoned.

Ok, I'll do.

I' also noticed that SSMS, before the ALTER TABLE statement put always some
instructions (in the automated generated SQL) that I don't unserstand
well...

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE xxxxxxx

Do you think that I have to execute them from my java program or can I start
directly with ALTER TABLE instruction?

Thanks,
D.|||D. (d@.d.com) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

>o Remove all "go" in the script, and wrap most statements in EXEC.
> The way script lookas as generated, if there is a batch-aborting
> error, the transaction is rolled back, and the rest of the statements
> will be committed. An alternative is to wrap all batches in
> IF @.@.transcount 0 BEGIN END.

>
I must remove all "go" statements because I'll execute the code via jdbc
driver.
I've not understood what do you mean with the phrase "wrap most statements
in EXEC" (sorry but I'm fairly new to SQLServer)

If you from a client, it's actually easier. Don't remove the "go" in
the sense that you send the entire script at once, but execute each
batch separately, and abort the whole affair if there is an error.
Don't forget to add a IF @.@.trancount 0 ROLLBACK TRANSACTION in this case.

The full story is this: when there is an error in an SQL command, several
things can happen, depending on the error, and many of there errors
when you work with DDL abort the batch and rollback the transaction.
Batches is separated by the "go". This means that if one batch fails
and aborts the transaction, and you run the script from a query window,
the remaining statements will still be executed - but without a transaction.

The stuff about EXEC is that instead of "ALTER TABLE ...", you need

EXEC('ALTER TABLE ...')

This is because if you put all in one batch, the script may not compile,
because it may refer to columns that has not been created yet etc.

But since you run from JDBC, you should not need this.

Quote:

Originally Posted by

I' also noticed that SSMS, before the ALTER TABLE statement put always
some instructions (in the automated generated SQL) that I don't
unserstand well...
>
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE xxxxxxx
>
Do you think that I have to execute them from my java program or can I
start directly with ALTER TABLE instruction?

Include them. Some of these settings are save with the table and per
columns. Many of these settings are already in effect, but they do
not make any harm.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx