Dynamic Security and Rool ups

Hi

I thought I had this dynamic security worked out but I guess not.

this is on AS 2000.

I have a Fact table - one of the columns is a companyid, this is joined to the company dimension.

For security I have another table "empcompany" which lists all the users (ntusername) and the company codes they can access. I created a member property against the company code and used MDX similar to this:

filter([Companycode].[Companycode].members,([Companycode].CurrentMember.Properties("ntusername") = username))

The problem is that when I join the "empcompany" table to either the fact or to the company dimension instead of getting 1000 rows I get 80000+ rows and my numbers are wrong. Most of the users have access to more than 1 company.

Any one have any ideas?

Thanks

Steve

Hi Steve,

My suggestion would be to try the "Security Fact Table" approach with the "empcompany" table, as discussed in slides 18-32 of this webcast deck (you're using the "Member Property" approach above). In the 2nd approach, you don't need to join "empcompany" to the fact table - rather, it becomes the fact table for a "Permissions" cube, which is combined in a virtual cube:

http://support.microsoft.com/kb/828343/

>>

Support WebCast: Dynamic Dimension Security in Microsoft SQL Server 2000 Analysis Services

...

Dynamic Security
The Three Basic Approaches

Member property approach

Permission data (e.g. UserName) is stored at the desired dimension level

Security fact table approach

A permissions cube is combined with original source cube using a virtual cube

Filter members at the leaf level

Filter members at the non-leaf level

User-defined function callout

Call a user-defined function to reference an outside source (e.g. external RDBMS)

Dynamic Query

Hi!

I am trying to dynamically modify my pass-through query containing a
procedure call with 2 parameters.

When I run my access app, I get this error: "Object or provider is not
capable of performing reuqested operation."

Below is my access code:

Dim varItem As Variant
Dim strSQL As String
Dim cat As ADOX.Catalog
Dim cmd As ADODB.Command
Dim strMyDate As String, dtMyDate As Date

dtMyDate = CDate([Forms]![ySalesHistory]![Start Date])
strMyDate = Format(dtMyDate, "yyyymmdd")

strSQL = "procCustomerSalesandPayments '" & strMyDate & "', '" &
[Forms]![ySalesHistory]![Customer Number] & "'"

Set cat = New ADOX.Catalog
Set cat.ActiveConnection = CurrentProject.Connection

'= = >NOTE: THIS IS WHERE THE ERROR POPS OUT!
Set cmd = cat.Procedures("Ben_CustomerSalesandPayments").Command

cmd.CommandText = strSQL
Set cat.Procedures("Ben_CustomerSalesandPayments").Command = cmd

DoCmd.OpenReport stDocName, acViewPreview

Set cat = Nothing
Set cmd = Nothing

Can anyone help me out?

Thanks.Ben (pillars4@.sbcglobal.net) writes:

Quote:

Originally Posted by

I am trying to dynamically modify my pass-through query containing a
procedure call with 2 parameters.
>
When I run my access app, I get this error: "Object or provider is not
capable of performing reuqested operation."

ADOX is nothing I have experience of, but I found in MSDN under the Command
property in ADOX that it says:

An error will occur when getting and setting this property if the
provider does not support persisting commands.

Which provider are you using? How does your connection string look like?

--
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|||Below is the connection string:

ODBC;DSN=YES2;DATABASE=YES100SQLC;

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns99621E8AFE47Yazorman@.127.0.0.1...

Quote:

Originally Posted by

Ben (pillars4@.sbcglobal.net) writes:

Quote:

Originally Posted by

>I am trying to dynamically modify my pass-through query containing a
>procedure call with 2 parameters.
>>
>When I run my access app, I get this error: "Object or provider is not
>capable of performing reuqested operation."

>
ADOX is nothing I have experience of, but I found in MSDN under the
Command
property in ADOX that it says:
>
An error will occur when getting and setting this property if the
provider does not support persisting commands.
>
Which provider are you using? How does your connection string look like?
>
--
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

|||Ben (pillars4@.sbcglobal.net) writes:

Quote:

Originally Posted by

Below is the connection string:
>
ODBC;DSN=YES2;DATABASE=YES100SQLC;

And what is in that DSN?

Particular which OLE DB provider do you use? I had a look in a book on
ADO, and it said that the only two providers to support ADOX are the
Jet provider and SQLOLEDB. The book is a bit old, but if ODBC means that
you are using MSDASQL, then we have the answer to your problem. Change
to use SQLOLEDB instead.

--
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

dynamic pivot table

hi

i am trying to create a dynamic sql statement so i can pivot information...

my problem is that when i run this query i get the error information below. i am running sql server 2000... can this run in 2005?

this runs perfectly when only one record is returned in the sub query...

declare @.query varchar(300)
select @.query = 'Select '+ char(10) + (
select Code
+'case when Code = '''+ Code +''' then count(Code) else 0 end as '+Code+''+char(10)
as [text()]

from WipAvailable WA
)+ char(10) +
' from WipMaster group by Code'

select @.query
exec (@.query)

Server: Msg 512, Level 16, State 1, Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

(1 row(s) affected)

u have given the answer..."this runs perfectly when only one record is returned in the sub query...", in this case the outer query just expects one result to be returned by the subquery... and it'll give the same error in 2005 as well... u need to look at the logic...

i dont know ur exact aim..if pivot is the aim , there is a pivot operator u can use in sql server 2005, to make row data into columns...

|||

if you are using the sql server 2005 then use Pivot operator,

example,

SELECT
[1] as [Code-1],
[2] as [Code-2],
[3] as [Code-3]
FROM
(SELECT Code from WipAvailable) Master
PIVOT
(Count(Code) for Code in ([1],[2],[3]))
AS pvt

to achive the above query dynamically use the following statement,

Declare @.Columns as varchar(1000);
Declare @.Values as varchar(1000);
Declare @.Query as varchar(3000);
select
@.Columns = Isnull(@.Columns,'') + '[' + Code + '] as [Code-' + Code + '],' ,
@.Values = Isnull(@.Values,'') + '[' + Code + '],'
From WipMaster

select @.Columns = Substring(@.Columns,1,Len(@.Columns)-1),
@.Values = Substring(@.Values,1,Len(@.Values)-1)

select @.Query = 'Select ' + @.Columns + ' From (Select Code From WipAvailable) Master'
+ ' Pivot (Count(Code) for Code in (' + @.values + ')) as Pvt'

Exec (@.Query)

|||

To work on SQL Server 2000,

Use the following query

Select
(Select count(Code) from WipAvailable where Code=1) as [1]
,(Select count(Code) from WipAvailable where Code=2) as [2]
,(Select count(Code) from WipAvailable where Code=3) as [3]

To achive the above query dynamically use the following statement

Declare @.query varchar(8000)
select @.query = Isnull(@.query,'') + '(Select count(Code) '
+ char(10) +
' from WipAvailable where Code=' + Code + ') as [' + Code + '],'
from Wipmaster Master
Order By Code
select @.query = 'Select ' + Substring(@.query,1,Len(@.query)-1)
Exec (@.query)

Dynamic goal for a KPI?

Hi!

I'm new in SSAS and I'm stuck. Here's my problem :
The goal of my KPI has to change (every month or every year). I could enter these different values with an MDX expression such as

CASE WHEN <date condition>
THEN goal-1-value
...
END

but i'd like it (different values of the goal) to be stored in a database.
So my question is : Is it possible to get back these values from an MDX expression?
If not is there another way to get these values from database in SSAS?

Thanks in advance,

Philippe
Assuming that you do store the goal values in a table, you coud add a measure group, with a measure for the goal value, to the cube. Then this "goal" measure can be directly accessed in MDX.

Dynamic File Name for Attachment

hi

I am encountering the same problem above and I did exacly as described . But it is not working.

I must send emails every month with dynamically named files as attachments.
The files are named according to the date on which they are generated.
For example on the first of November 2007, the file will be named myfile_1_11_2007.

I have created a variable called DynamicFileName with package scope, data type string and default value: d:\\tests\\

In "Send Mail Task Editor" Dialog Box, I have specified the following:

smtpConnection: smtptest.server.com
From :nemo@.smtptest.server.com
To: nemo@.smtptest.server.com
Subject: Dynamic File Email
MessageSourceType: Variable
MessageSource: blank
Priority: blank
Attachments: blank

In Expressions, I have specified:

FileAttachments: @.[User:ynamicFileName] + "myfile_" + (DT_STR, 4, 1252) DAY( GETDATE() ) + "_" + (DT_STR, 4, 1252) MONTH( GETDATE() ) + "_" + (DT_STR, 4, 1252) YEAR ( GETDATE() ) + ".csv"

When I execute the package, I get the following errors:
--
Error at Send Mail Task [Send Mail Task]: Either the file "d:\\tests\\myfile_1_7_2007.csv" does not exist or you do not have permissions to access the file.

Error at Send Mail Task: There were errors during task validation.

Of course, the file does not exist. It will exist at tun-time. How can I tell the Send Mail Task to use a filename that is dynamic ?

By the way, once I have specified the code for FileAttachments, on trying to edit the Send Mail Task Properties, I can see that the Atachments field has been set to "d:\tests\myfile_1_7_2007.csv by itself: I never typed it there !! It seems that the task executes the code even before it is run. If I remove the attachment path manually, on running the dts, I get an error saying that "either the file does not exist or you do not have permission to access the file.

I would be most grateful if anyone could be of help

thanks

I am trying to work this out here. Have you checked that the file gets dynamically created BEFORE your Send Main task?

Sounds silly I know, but put a break point on the component/container that is generating the file and double check on your C drive to ensure it does actually exist.

Also d:\\tests\\myfile_1_7_2007.csv doesn't look right to me.

The UNC syntax for Windows systems is : \\computername\sharedfolder\resoyrce

Therefore I think you need to check your expression evaluates correctly!

Good luck!

|||

Another thing to think about is, does the account you are running the package from have access to the folder you are trying to write to? (I also agree with the statement above that you should be using the UNC path)

Have you tried checking the delayvalidation property for the send mail task?

|||EWishdahl, let me give u a kiss!!! Muah! Muah!The delayvalidation property was the answer!!!I hope you are a girl!!Thanks !!!!!|||

Negative... no man kisses please ...

Glad I could help though (remember to mark all applicable answers)

Eric Wisdahl

Dynamic feed of table name to Transfer SQL Server Object Task

Hi
I would like to be able to feed the List of tables to the Transfer SQL Server Object Task dynamically.
I have got a foreachloop container which it feeds the table names into a variable @.table_name (string).

Transfer SQL Server Object Task is with in foreachloop container

I did add an expression into the property of Transfer SQL Server Object Task and assign the tablelist property to @.table_name

I would be grateful if you can give me any hint.
Thanks
S

The logic looks right to me...are you seeing any error?

I have a blog post that explains how to iterate through a SQL result set (in your case to get the list of tables) using a foreach loop conatiner.

I hope that hepls you

|||

Hi,

I have faced this issue where TablesList property of Transfer SQL Server Object task takes in list of tables to transfer. There is no way to dynamically set the property through SSIS variable because this property expects StringCollection object. If you declare an SSIS variable as Object and assign it to TablesList property of the task thru Expression, it will not work because expressions cannot evaluate Object data type

I did a workaround by creating a child package in ScripTask and adding a TransferSQL server object task programmatically and assigning the TableList property as StringCollection object in VB.NET

Thanks

Mohit

|||Thanks Rafael,
I feed the list of tables (as object) to foreach Loop Container and the loop will put them in to a string variable.
I have got other tasks in the foreach Loop Container as well as transfer SQL Server Object tasks and they do use the table name (the string variable with out the problem)
the problem starts when I try to feed this table name to Tablelist properties of transfer SQL Server Object tasks which it complians saying that I it can not assign the string value to the tablelist property.
I have even tried to feed a dataset(object -list of tables ) to that property and even that didn't work.

|||Thanks Mohit for your reply.
so in the child Script task you are populating the table collation the way it should be but how are you feeding it to TabeList Propery.
would you be able to attach the VB code please.
Many Thanks
|||

This is how to do it...

'create sql server object task to move tables

Dim MoveTable As Executable = Child.Executables.Add("STOCK:TransferSqlServerObjectsTask")

Dim MoveTableTask As TaskHost = CType(MoveTable, TaskHost)

'set properties

MoveTableTask.Properties("CopySchema").SetValue(MoveTableTask, True)

MoveTableTask.Properties("CopyData").SetValue(MoveTableTask, True)

'create a stringcollection of tables

Dim Tables As StringCollection = New StringCollection()

Tables.Add('Table1')

Tables.Add('Table2')

Tables.Add('Table3')

'create string collection for tables to transfer

MoveTableTask.Properties("TablesList").SetValue(MoveTableTask, Tables))

'set the source and destination connections

......................

'execute package and dispose

Thanks

Mohit

|||thanks|||I manage to use this method and copy all the tables.
The problem is if I tables belong to a schema this method won't work.
I am using version variable as schema name.
sample
version ="VT.1"
Table_name="Sample"

sc.Add("[" + Version + "]." + Dts.Variables("Table_Name").Value.ToString)

so I am expecting to add [VT.1].sample to the TableList collection.
but when it is trying to populate tablelist property from the sc (collection) variable it complains that table does not exist at source!
which I know for the fact it does

Any ideas?|||

Kolf wrote:

I manage to use this method and copy all the tables.
The problem is if I tables belong to a schema this method won't work.
I am using version variable as schema name.
sample
version ="VT.1"
Table_name="Sample"

sc.Add("[" + Version + "]." + Dts.Variables("Table_Name").Value.ToString)

so I am expecting to add [VT.1].sample to the TableList collection.
but when it is trying to populate tablelist property from the sc (collection) variable it complains that table does not exist at source!
which I know for the fact it does

Any ideas?

Can you try with any table with dbo schema wether you are able to transfer. Also check whether you have permission on the schema to access the table.

Thanks

Mohit

|||it does work with dbo schema
and I do have permission on the schemas
it seems like the object copy task doesn't understand the collection of the table names with the schema.
any solution?
thanks|||

Kolf wrote:

it does work with dbo schema
and I do have permission on the schemas
it seems like the object copy task doesn't understand the collection of the table names with the schema.
any solution?
thanks

It seems it does not understand schemas. Bcoz even creating the Transfer SQL server Object task in design time , I selected one table of schema1. I had same tablename for schema2. When i select one table from schema1 for table list property and close the task and edit again, I see both the tables of different schema selected automatically. Seems there is a problem. Suggest you to use tablename to maintain versions like tablename + "_" + VersionName.

Thanks

Mohit

|||

Thanks for the quick reply.

so it seems like this is a bug as the functionality is there but it doesn't work.

I have to be able to copy tables in a schema to the destination DB

I have even tried to create the schema manually at destination

let's say

I have a table

schema:[Dt.1]

table:test

then I've got [Dt.1].test as my source table

I've created schema [Dt.1] at the destination database but still SQL object copy task complains that task can't see test table in the source database.

this is even the case if I hardcode the name of the tables in the task!

|||

Kolf wrote:

Thanks for the quick reply.

so it seems like this is a bug as the functionality is there but it doesn't work.

I have to be able to copy tables in a schema to the destination DB

I have even tried to create the schema manually at destination

let's say

I have a table

schema:[Dt.1]

table:test

then I've got [Dt.1].test as my source table

I've created schema [Dt.1] at the destination database but still SQL object copy task complains that task can't see test table in the source database.

this is even the case if I hardcode the name of the tables in the task!

Just check in your code for this:

MoveTableTask.Properties("CopySchema").SetValue(MoveTableTask, True) to copy schemas thru Task and

MoveTableTask.Properties("SchemaList").SetValue(MoveTableTask,sc) is assigned list of schemas to transfer .

If does not work and you want to stick to schemas you can try dataflow tasks

Thanks

Mohit

|||Dataflow task can't be used as I have different tables with different table columns
would you be able to create a sample dtsx based on adventureworks please
thanks|||Thanks Mohit
the problem with dataflow tasks is that (my table names are dynamic and I have got a foreach Loop that feed the table names to a dataflow
and in dataflow I have got a
* OLE DB source ( which the data access methode has been set to Variable) and that's how I feed my table name (variable called User::source_table ) and an example value for it will be
[DT.1].TableA
which is pointing to [DT.1] and with in the OLEDB Source I can preview the data

* also I have got an OLE DB Destination which same as OLE DB source is reading the table name from a variable ( which I'm using the same variable name , as the table name and the schema on both servers are the same)

problem: in order for this to work I have to manually click on the column map section in OLEDB Destinaiton section and save the package(manually) as the tables and they columns are changing this method won't be possible.

I hope I 've explained it properly.
Many Thanks

Dynamic feed of table name to Transfer SQL Server Object Task

Hi
I would like to be able to feed the List of tables to the Transfer SQL Server Object Task dynamically.
I have got a foreachloop container which it feeds the table names into a variable @.table_name (string).

Transfer SQL Server Object Task is with in foreachloop container

I did add an expression into the property of Transfer SQL Server Object Task and assign the tablelist property to @.table_name

I would be grateful if you can give me any hint.
Thanks
S

The logic looks right to me...are you seeing any error?

I have a blog post that explains how to iterate through a SQL result set (in your case to get the list of tables) using a foreach loop conatiner.

I hope that hepls you

|||

Hi,

I have faced this issue where TablesList property of Transfer SQL Server Object task takes in list of tables to transfer. There is no way to dynamically set the property through SSIS variable because this property expects StringCollection object. If you declare an SSIS variable as Object and assign it to TablesList property of the task thru Expression, it will not work because expressions cannot evaluate Object data type

I did a workaround by creating a child package in ScripTask and adding a TransferSQL server object task programmatically and assigning the TableList property as StringCollection object in VB.NET

Thanks

Mohit

|||Thanks Rafael,
I feed the list of tables (as object) to foreach Loop Container and the loop will put them in to a string variable.
I have got other tasks in the foreach Loop Container as well as transfer SQL Server Object tasks and they do use the table name (the string variable with out the problem)
the problem starts when I try to feed this table name to Tablelist properties of transfer SQL Server Object tasks which it complians saying that I it can not assign the string value to the tablelist property.
I have even tried to feed a dataset(object -list of tables ) to that property and even that didn't work.

|||Thanks Mohit for your reply.
so in the child Script task you are populating the table collation the way it should be but how are you feeding it to TabeList Propery.
would you be able to attach the VB code please.
Many Thanks
|||

This is how to do it...

'create sql server object task to move tables

Dim MoveTable As Executable = Child.Executables.Add("STOCK:TransferSqlServerObjectsTask")

Dim MoveTableTask As TaskHost = CType(MoveTable, TaskHost)

'set properties

MoveTableTask.Properties("CopySchema").SetValue(MoveTableTask, True)

MoveTableTask.Properties("CopyData").SetValue(MoveTableTask, True)

'create a stringcollection of tables

Dim Tables As StringCollection = New StringCollection()

Tables.Add('Table1')

Tables.Add('Table2')

Tables.Add('Table3')

'create string collection for tables to transfer

MoveTableTask.Properties("TablesList").SetValue(MoveTableTask, Tables))

'set the source and destination connections

......................

'execute package and dispose

Thanks

Mohit

|||thanks|||I manage to use this method and copy all the tables.
The problem is if I tables belong to a schema this method won't work.
I am using version variable as schema name.
sample
version ="VT.1"
Table_name="Sample"

sc.Add("[" + Version + "]." + Dts.Variables("Table_Name").Value.ToString)

so I am expecting to add [VT.1].sample to the TableList collection.
but when it is trying to populate tablelist property from the sc (collection) variable it complains that table does not exist at source!
which I know for the fact it does

Any ideas?|||

Kolf wrote:

I manage to use this method and copy all the tables.
The problem is if I tables belong to a schema this method won't work.
I am using version variable as schema name.
sample
version ="VT.1"
Table_name="Sample"

sc.Add("[" + Version + "]." + Dts.Variables("Table_Name").Value.ToString)

so I am expecting to add [VT.1].sample to the TableList collection.
but when it is trying to populate tablelist property from the sc (collection) variable it complains that table does not exist at source!
which I know for the fact it does

Any ideas?

Can you try with any table with dbo schema wether you are able to transfer. Also check whether you have permission on the schema to access the table.

Thanks

Mohit

|||it does work with dbo schema
and I do have permission on the schemas
it seems like the object copy task doesn't understand the collection of the table names with the schema.
any solution?
thanks|||

Kolf wrote:

it does work with dbo schema
and I do have permission on the schemas
it seems like the object copy task doesn't understand the collection of the table names with the schema.
any solution?
thanks

It seems it does not understand schemas. Bcoz even creating the Transfer SQL server Object task in design time , I selected one table of schema1. I had same tablename for schema2. When i select one table from schema1 for table list property and close the task and edit again, I see both the tables of different schema selected automatically. Seems there is a problem. Suggest you to use tablename to maintain versions like tablename + "_" + VersionName.

Thanks

Mohit

|||

Thanks for the quick reply.

so it seems like this is a bug as the functionality is there but it doesn't work.

I have to be able to copy tables in a schema to the destination DB

I have even tried to create the schema manually at destination

let's say

I have a table

schema:[Dt.1]

table:test

then I've got [Dt.1].test as my source table

I've created schema [Dt.1] at the destination database but still SQL object copy task complains that task can't see test table in the source database.

this is even the case if I hardcode the name of the tables in the task!

|||

Kolf wrote:

Thanks for the quick reply.

so it seems like this is a bug as the functionality is there but it doesn't work.

I have to be able to copy tables in a schema to the destination DB

I have even tried to create the schema manually at destination

let's say

I have a table

schema:[Dt.1]

table:test

then I've got [Dt.1].test as my source table

I've created schema [Dt.1] at the destination database but still SQL object copy task complains that task can't see test table in the source database.

this is even the case if I hardcode the name of the tables in the task!

Just check in your code for this:

MoveTableTask.Properties("CopySchema").SetValue(MoveTableTask, True) to copy schemas thru Task and

MoveTableTask.Properties("SchemaList").SetValue(MoveTableTask,sc) is assigned list of schemas to transfer .

If does not work and you want to stick to schemas you can try dataflow tasks

Thanks

Mohit

|||Dataflow task can't be used as I have different tables with different table columns
would you be able to create a sample dtsx based on adventureworks please
thanks|||Thanks Mohit
the problem with dataflow tasks is that (my table names are dynamic and I have got a foreach Loop that feed the table names to a dataflow
and in dataflow I have got a
* OLE DB source ( which the data access methode has been set to Variable) and that's how I feed my table name (variable called User::source_table ) and an example value for it will be
[DT.1].TableA
which is pointing to [DT.1] and with in the OLEDB Source I can preview the data

* also I have got an OLE DB Destination which same as OLE DB source is reading the table name from a variable ( which I'm using the same variable name , as the table name and the schema on both servers are the same)

problem: in order for this to work I have to manually click on the column map section in OLEDB Destinaiton section and save the package(manually) as the tables and they columns are changing this method won't be possible.

I hope I 've explained it properly.
Many Thanks

Dynamic derivation of Heading - is it possible

Hi
I have a query which produces effectively a pivottable. Is there any way I can dynamically assign the column headings ie the code on each line after AS rather than hard coded as I have currently

Extract of Current SP

CREATE PROC dbo.FairValeSummaryPivot
@.BatchRunID INT
AS
SET NOCOUNT ON
SELECT
MIN(CASE WHEN Tn = '1' THEN PVBalance ELSE 0 END) AS 'Tn1 - Tn0' ,
MIN(CASE WHEN Tn = '0' THEN PVBalance END) AS 'Tn0 - Tn-1' ,
MIN(CASE WHEN Tn = '-1' THEN PVBalance END) AS 'Tn-1 - Tn-2',
MIN(CASE WHEN Tn = '-2' THEN PVBalance END) AS 'Tn-2 - Tn-3',
MIN(CASE WHEN Tn = '-3' THEN PVBalance END) AS 'Tn-3 - Tn-4',
MIN(CASE WHEN Tn = '-4' THEN PVBalance END) AS 'Tn-4 - Tn-5',
-- and so on
FROM FVSummary
WHERE BatchRunID = @.BatchRunID
GO

what I would like would be along the lines of

MIN(CASE WHEN Tn = '1' THEN PVBalance ELSE 0 END) AS 'Tn' + Tn + ' - Tn' + Tn-1, ,

Hope this is clear
CheersDynamic SQL is all that comes to my mind from the SQL perspective, but this is really a presentation issue so I think it should be handled at the client rather than in the SQL itself.

-PatP|||Dynamic SQL is the only method of assigning variable column headers. But I would discourage you from doing this because no reporting application (Crystal, Access, Active Reports...) is going to be able to deal with output that has a different layout for each result set.
There is (almost) never a good reason for doing what you are trying to do, and in essence that is why it is difficult to do.|||Do a google on ags crosstab. Also (if it's still out there) RAC for SQL.

Regards,

hmscott

dynamic action in sql server

Hi
I was wondering if someone could help me here. With the use of some action
whether it be SQL DMO or a command propt i need to be able to disable TCP/IP
in SQL Server Network Utility
I know you can call up SQL Server Network Utility with the use of
svrnetcn,is there anyway you can disable the TCP/IP
Thanks
Aido123
Hi
Network settings for SQL Server are stored in the registry. You can modify
it there. For any changes to take effect, SQL Server needs to be restarted.
Regards
Mike
"aido123" wrote:

> Hi
> I was wondering if someone could help me here. With the use of some action
> whether it be SQL DMO or a command propt i need to be able to disable TCP/IP
> in SQL Server Network Utility
> I know you can call up SQL Server Network Utility with the use of
> svrnetcn,is there anyway you can disable the TCP/IP
> Thanks
> Aido123

dynamic action in sql server

Hi
I was wondering if someone could help me here. With the use of some action
whether it be SQL DMO or a command propt i need to be able to disable TCP/IP
in SQL Server Network Utility
I know you can call up SQL Server Network Utility with the use of
svrnetcn,is there anyway you can disable the TCP/IP
Thanks
Aido123Hi
Network settings for SQL Server are stored in the registry. You can modify
it there. For any changes to take effect, SQL Server needs to be restarted.
Regards
Mike
"aido123" wrote:

> Hi
> I was wondering if someone could help me here. With the use of some action
> whether it be SQL DMO or a command propt i need to be able to disable TCP/
IP
> in SQL Server Network Utility
> I know you can call up SQL Server Network Utility with the use of
> svrnetcn,is there anyway you can disable the TCP/IP
> Thanks
> Aido123