Change Filename creating database

I wish to change filename parameter depending on the root path of SQL Server :

C:\Program Files\Microsoft SQL Server\MSSQL.X\MSSQL where X>=1

I can find this root path by the following querie :

select SUBSTRING (physical_name,1,len(physical_name)-11) from sys.database_files where type_desc like 'LOG')

But i can't include the result in the create database command to be independant of the new root path in a the case of a deployment :

CREATE DATABASE [ACS] ON PRIMARY
(
NAME = N'ACS',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ACS.mdf',
SIZE = 5120KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB
)
LOG ON
(
NAME = N'ACS_log',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ACS_log.ldf',
SIZE = 3840KB,
MAXSIZE = 2048GB,
FILEGROWTH = 10%
)

Somebody can help me please ?

You will have to compose your SQlString first, then executing it with the help of sp_executesql or EXEC.

HTH; Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Following your clear but short instruction, i write the next queries that don't work, have a correction ?

DECLARE @.prmname nvarchar(500)
DECLARE @.prmname2 nvarchar(500)
DECLARE @.prmpath nvarchar(500)
DECLARE @.prmpath2 nvarchar(500)
DECLARE @.SQLString nvarchar(500)
DECLARE @.ParmDefinition nvarchar(500);

/* Build the SQL string */
SET @.SQLString =
N'CREATE DATABASE [ACS] ON PRIMARY
(
NAME = @.name,
FILENAME = @.path,
SIZE = 5120KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB
)
LOG ON
(
NAME = @.name2,
FILENAME = @.path2,
SIZE = 3840KB,
MAXSIZE = 2048GB,
FILEGROWTH = 10%
)
COLLATE Latin1_General_BIN2';
SET @.ParmDefinition = N'@.path varchar(500),@.path2 varchar(500), @.name varchar(500), , @.name2 varchar(500)';

/* Execute the string with the parameter value. */
SET @.prmname = N'ACS';
SET @.prmpath = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ACS.mdf';
SET @.prmname2 = N'ACS_log';
SET @.prmpath2 = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ACS_log.ldf';

EXECUTE sp_executesql
@.SQLString,
@.ParmDefinition,
@.name= @.prmname,@.path=@.prmpath, @.name2= @.prmname2,@.path2=@.prmpath2;

|||

DECLARE @.prmname nvarchar(500)

DECLARE @.prmname2 nvarchar(500)

DECLARE @.prmpath nvarchar(500)

DECLARE @.prmpath2 nvarchar(500)

DECLARE @.SQLString nvarchar(500)

DECLARE @.ParmDefinition nvarchar(500);

/* Execute the string with the parameter value. */

SET @.prmname = N'ACS';

SET @.prmpath = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ACS.mdf';

SET @.prmname2 = N'ACS_log';

SET @.prmpath2 = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ACS_log.ldf';

/* Build the SQL string */

SET @.SQLString =

N'CREATE DATABASE [ACS] ON PRIMARY

(

NAME = ' + @.prmname + ',

FILENAME = ' + @.prmpath + ',

SIZE = 5120KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 1024KB

)

LOG ON

(

NAME = ' + @.prmname2 + ',

FILENAME = ' + @.prmpath2 + ',

SIZE = 3840KB,

MAXSIZE = 2048GB,

FILEGROWTH = 10%

)

COLLATE Latin1_General_BIN2'

EXECUTE sp_executesql @.SQLString

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

It's not working.

Incorrect Syntax for 'C' for DECLARE @.prmpath2 nvarchar(500)

Change Color of Textbox Depending on Value

He is my question depending on the code field from my database I need to display a different color. The field is a detail in my table with just text displaying the title of the report. I have the actual code value in another column. I then go to the background color expression and am using this code:

=Iif(First(Fields!Code.Value, "CodeDataset") = 3, "Green", Iif(First(Fields!Code.Value, "CodeDataset") = 4, "Blue", "Red" ))

They all work if you pull the report up one at a time, but when selecting multi-values you get the color from the first record on each report page no matter what the second, third or so on values are. The code field does display each correct code. Is this being hard-coded to the first record?

Any Idea's ?|||

if your statement:

=Iif(First(Fields!Code.Value, "CodeDataset") = 3, "Green", Iif(First(Fields!Code.Value, "CodeDataset") = 4, "Blue", "Red" ))

You are evaluting the First Fields value "Literally" if you change it to

=Iif(Fields!Code.Value= 3, "Green", Iif(Fields!Code.Value = 4, "Blue", "Red" )) - red will be the default value I think you will see the proper results.

|||

Great thanks it was simple good I tried it with out the first,but I did still have the "DataSet" in there. What is first called is it a function so I can do some research. Also how do you do is null in an expression, and convert data types for a value? Thanks so much for your help!!!

=Iif(Fields!Days.Value is Null, "AliceBlue", "White")

|||

I tried this code for my returntime field: Do I need to do a data conversion?

=Iif(Fields!ReturnTime.Value <= 0, "AliceBlue", "Transparent")

Build complete -- 0 errors, 0 warnings

[rsRuntimeErrorInExpression] The BackgroundColor expression for the textbox ‘Days’ contains an error: Operator '<=' is not defined for type 'Date' and type 'Integer'.

Preview complete -- 0 errors, 1 warnings

|||

Humm,

I think its the "<=" should work try Fields!ReturnTime.Value < 0 or Fields!ReturnTime.Value = 0 instead.

Change Color of Textbox Depending on Value

I have the following:

=IIF( Sum(Fields!Percentile.Value, "RegionalSnaps") < 10.0, "Yellow", "Black")

Doesnt appear to be changing color to the numbers, actually its just putting the work black in all the cells in the report, the nubmer are % numbers, like 10.53% of 3.45%, and if its great than 10.00 % I want the textbox to show up yellow, if its >10% i want it to show up normal. Any help would be greatly appreciated.

Hi Duane,

You have the right idea, but the comparison may be not what you are looking for. I think a link to RS Expressions will help you out a good bit here. The link should explain better then I can how to use different expresssions.

http://msdn2.microsoft.com/en-us/library/ms157328.aspx

|||

Hi Duane! Are you putting your expression in the BackgroundColor property of the textbox. From what you described, it sounds like you are putting it in the value property.

Jennifer

|||

Getting much closer, not it changes all the textboxes to yellow:

=IIF( Sum(Fields!Percentile.Value, "RegionalSnaps") >= 10, "Yellow", "White")

I only want the ones above 10% to be yellow, otherwise white.

|||

I was placing it in the wrong place @. first, now I realized it, and am getting closer if you read my previous post.

Thanks

|||

Duane Haas wrote:

Getting much closer, not it changes all the textboxes to yellow:

=IIF( Sum(Fields!Percentile.Value, "RegionalSnaps") >= 10, "Yellow", "White")

I only want the ones above 10% to be yellow, otherwise white.

Any ideas anyone on why the entire range of cells are yellow and not just the one above 10%?

|||

Getting much closer, not it changes all the textboxes to yellow:

=IIF( Sum(Fields!Percentile.Value, "RegionalSnaps") >= 10, "Yellow", "White")

I only want the ones above 10% to be yellow, otherwise white.

Any ideas anyone on why the entire range of cells are yellow and not just the one above 10%?

|||

Duane,

Could it be summing this value for all rows in dataset and since the total value is greater than 10, all detail rows will be yellow? Maybe remove the sum function and only evaluate the value itself for each row.

T

|||

I tried that and than this is what I get:

h:\sqlreports\snaps\Report1.rdl The background color expression for the textbox ‘textbox2’ references a field outside an aggregate function. Value expressions in matrix cells should be aggregates, to allow for subtotaling.

h:\sqlreports\snaps\Report1.rdl The background color expression for the textbox ‘textbox2’ contains an error: [BC30455] Argument not specified for parameter 'FalsePart' of 'Public Function IIf(Expression As Boolean, TruePart As Object, FalsePart As Object) As Object'.

Not sure where to go from here, this is a matrix style report

|||

Sorry Duane, I have yet to build a matrix style report, so I have no more suggestions..

T

|||anybody have any ideas?

Change Body Size

Is there a way to change the report body size in execution time(from the
template),
depending on the result of an expression?.
--
Eduardo FonsecaTo decrease body size, decrease input, increase output, and increase
exercise time aka execution time aka CPU ;). In other words, do not
modify your data and do a lot of selects with CPU-hungry UDFs in WHERE
clause. Add WITH RECOMPILE to your stored procedures to further up
execution time. Just kidding. Sorry for the off topic. ;)

Change Body Size

Is there a way to change the report body size in execution time(from the
template),
depending on the result of an expression?.
--
Eduardo FonsecaTo decrease body size, decrease input, increase output, and increase
exercise time aka execution time aka CPU ;). In other words, do not
modify your data and do a lot of selects with CPU-hungry UDFs in WHERE
clause. Add WITH RECOMPILE to your stored procedures to further up
execution time. Just kidding. Sorry for the off topic. ;)