Cursor Help Please

I have a requirement for a user to access time sheet information by accessing a SharePoint portal listing. They will be required enter a date range. The login will be based on their domain access.

Based on the query below, I can return a result set which gives the user all rows they'll need, grouped accordingly and based on a date range. Except for a required billable percentage figure. A billable percentage is defined as billable hours / total hours * 100 or based on the query below;

day1 through 7_hr1 [Worked_hrs] where the project <> admin (@.Worked_hrs_B), divided by the total [Worked_hrs](@.Total_Worked_hrs).

I'm pretty sure I need to use a cursor which will tally all the @.Worked_hrs_NB rows, and another cursor which will tally @.Total_Worked_hrs rows and then divide the two variables * 100, to return it in the @.Billable variable.

This is where I get lost. I'm ashamed to say that my TSQL is rusty & weak at best. Rather than confuse anyone with my idea of cursor syntax, I left it out of this query, & just included the variables I assumed would fit.

A down'n'dirty cursor lesson would be most appreciated (If that's what this needs). Thanks in advance for your help.

DECLARE
@.pe_date1 AS SMALLDATETIME, --Prompt
@.pe_date2 AS SMALLDATETIME, --Prompt
@.emp_id AS CHAR (30), --Login
@.Worked_hrs_B AS INT,
@.Total_Worked_hrs AS INT,
@.Precent AS INT
SET @.pe_date1 = '6/01/2004'
SET @.pe_date2 = '7/01/2005'
SET @.emp_id = 'degajx'
************************************************************************
SET @.Worked_hrs_B = '' Here's where I get lost
SET @.Total_Worked_hrs = '' with the variables & the
SET @.Precent = '' --Make Header Info cursor to populate them.
************************************************************************
SELECT
pjlabhdr.docnbr
, pjlabhdr.pe_date
, pjlabdet.project
, pjlabdet.pjt_entity
, pjlabdet.ld_desc
, (
pjlabdet.day1_hr1 +
pjlabdet.day2_hr1 +
pjlabdet.day3_hr1 +
pjlabdet.day4_hr1 +
pjlabdet.day5_hr1 +
pjlabdet.day6_hr1 +
pjlabdet.day7_hr1
) AS [Worked_hrs]
, pjemploy.manager1
, ltle.employeename AS [Manager] --Make Header Info
, SubAcct.Descr --Make Header Info ************************************************************************
, @.Percent AS [Billable %] --An accurate return, though repeating would be fine.
************************************************************************
FROM IEM_Cut.dbo.PJLABHDR pjlabhdr
INNER JOIN
IEM_Cut.dbo.PJEMPLOY
ON pjlabhdr.employee = pjemploy.employee
Inner JOIN
labortool..laboremployee ltle (NOLOCK)
ON ltle.empid = pjemploy.manager1
LEFT OUTER JOIN
IEM_Cut.dbo.PJLABDET pjlabdet
ON pjlabhdr.docnbr = pjlabdet.docnbr
LEFT OUTER JOIN
IEM_Cut.dbo.SubAcct SubAcct
ON pjemploy.gl_subacct = SubAcct.Sub
WHERE
( pjlabdet.day1_hr1 <> 0
OR pjlabdet.day2_hr1 <> 0
OR pjlabdet.day3_hr1 <> 0
OR pjlabdet.day4_hr1 <> 0
OR pjlabdet.day5_hr1 <> 0
OR pjlabdet.day6_hr1 <> 0
OR pjlabdet.day7_hr1 <> 0
)
AND pjlabhdr.CpnyID_home = 'IEM'
AND pjlabhdr.pe_date BETWEEN CONVERT (varchar, @.pe_date1 , 107) AND CONVERT (varchar, @.pe_date2 , 107)
AND pjlabhdr.employee = @.emp_id
ORDER BY
pjlabhdr.pe_date ASC --Group
, pjlabhdr.docnbr ASC --Group
, pjlabdet.project ASC --Group
, pjlabdet.pjt_entity ASC --Group

You don't really need a cursor. You can write two queries that performs the required SUM operations and divide the results. For example:

select (select sum(...) from ....)/((select sum(...) from ...)*100.0) as billable_per

See Books Online for more details on how to write scalar queries, group by, expressions etc.

sql

Current User Roles List seems Incorrect

Our app uses the ROLES column of the CATALOGS rowset from the schema, and I seem to be seeing what I think are incorrect results.

The documenation says "Username is appended to ROLES if one of the roles uses dynamic security". OK.

But I often get the username even when there are no MDX expressions used for dimension or cell access. For example, every Foodmart 2000 cube I have migrated shows the username in the role list.

What is the definition of dynamic security for the purposes of the CATALOGS rowset?

I should have noted, this is SSAS 2005.

This is generally how I am retrieving the roles list, if any one wishes to replicate.

OleDbConnection conn = new OleDbConnection( "provider=msolap;SSPI=Negotiate;Prompt=1" ); conn.Open(); DataTable schema = conn.GetSchema("Catalogs"); foreach(DataRow row in schema.Rows) { if (row["ROLES"].ToString().Length > 0) // only available for the current catalog txtRoles.Text = row["ROLES"].ToString(); } conn.Close();

Current user in SQL

Hi

Is there a variable or a possibility to use the current user in sql?

I would like to create an update trigger and write the user that made the change in the affected table!

Thanks!

Try SUSER_NAME() for the login or USER_NAME() for the database user.

|||

Pick your poison ;-)

SELECT SUSER_SNAME(),USER_NAME(),CURRENT_USER

Denis The SQL Menace
http://sqlservercode.blogspot.com
http://sqlblog.com/blogs/denis_gobo/default.aspx

Current user

Am using SQL Server 2000, and am writing some audit triggers. How do I get
the the name of the user, eg. user_id or current_user. Just spent an hour
looking through the SAMS book for this, and can't find any reference."William F. O'Neill" <wfoneill@.bellsouth.net> wrote in message
news:Clg9d.183017$Np2.110030@.bignews4.bellsouth.ne t...
> Am using SQL Server 2000, and am writing some audit triggers. How do I
> get the the name of the user, eg. user_id or current_user. Just spent an
> hour looking through the SAMS book for this, and can't find any reference.

select user_name()|||Thank you ParrotRob, but that gives me 'dbo.' If I log on to my
application, I want to be able to capture 'my' logon id, eg. wfoneill.
Remember, I want to be able to use this in my Insert, Update, & Delete
triggers.
Bill...

"ParrotRob" <parrotrob@.yahoo.com> wrote in message
news:c8udneeR2ZtXm_vcRVn-vQ@.adelphia.com...
> "William F. O'Neill" <wfoneill@.bellsouth.net> wrote in message
> news:Clg9d.183017$Np2.110030@.bignews4.bellsouth.ne t...
>> Am using SQL Server 2000, and am writing some audit triggers. How do I
>> get the the name of the user, eg. user_id or current_user. Just spent an
>> hour looking through the SAMS book for this, and can't find any
>> reference.
> select user_name()|||William F. O'Neill wrote:
> Am using SQL Server 2000, and am writing some audit triggers. How do
> I get the the name of the user, eg. user_id or current_user. Just
> spent an hour looking through the SAMS book for this, and can't find
> any reference.

You have:

CURRENT_USER
USER_NAME()
SUSER_SNAME()

If you get frustrated building audit triggers, you can look at our OmniAudit
product for doing exactly that:

http://www.krell-software.com/omniaudit

--
Steve Troxell|||The SAMS book is a good resource for learning how to user SQL Server but
isn't a great resource for looking things like this up. You can more
readily get the answer you need by getting familiar with SQL Server Books On
Line (BOL). Install or reinstall the tools on your desktop machine and load
all the documentation to your hard disk for best performance (and
availability). There is an option to use BOL off the CD but it will be
slower and whenever you want to use BOL, you'll have to remove your music CD
(and I just hate doing that).

Then, this problem will depend on how the users are connecting to the
database. If you're using an application UserID (where the credentials for
the connection are actually buried in the app and are the same for each
user), then you won't get much information about the individual who's
actually using the application. However, if you're using trusted
connections or you've set up SQL Server LogonIDs for each user, you could
see if:

select suser_sname()

- or -

select system_user

gives you the result you want. I think there are also some global variables
that you could examine to see if they'll help you.

Really, time spent in BOL getting familiar with how to find things is not
time wasted. Remember that whenever you hit a topic, you should check the
"See Also" list at the bottom of each page.

"William F. O'Neill" <wfoneill@.bellsouth.net> wrote in message
news:Clg9d.183017$Np2.110030@.bignews4.bellsouth.ne t...
> Am using SQL Server 2000, and am writing some audit triggers. How do I
get
> the the name of the user, eg. user_id or current_user. Just spent an hour
> looking through the SAMS book for this, and can't find any reference.|||Thanks for all the help, and suggestions.

"DHatheway" <dlhatheway@.mmm.com.nospam> wrote in message
news:ck67rd$qc$1@.tuvok3.mmm.com...
> The SAMS book is a good resource for learning how to user SQL Server but
> isn't a great resource for looking things like this up. You can more
> readily get the answer you need by getting familiar with SQL Server Books
> On
> Line (BOL). Install or reinstall the tools on your desktop machine and
> load
> all the documentation to your hard disk for best performance (and
> availability). There is an option to use BOL off the CD but it will be
> slower and whenever you want to use BOL, you'll have to remove your music
> CD
> (and I just hate doing that).
> Then, this problem will depend on how the users are connecting to the
> database. If you're using an application UserID (where the credentials
> for
> the connection are actually buried in the app and are the same for each
> user), then you won't get much information about the individual who's
> actually using the application. However, if you're using trusted
> connections or you've set up SQL Server LogonIDs for each user, you could
> see if:
> select suser_sname()
> - or -
> select system_user
> gives you the result you want. I think there are also some global
> variables
> that you could examine to see if they'll help you.
> Really, time spent in BOL getting familiar with how to find things is not
> time wasted. Remember that whenever you hit a topic, you should check the
> "See Also" list at the bottom of each page.
> "William F. O'Neill" <wfoneill@.bellsouth.net> wrote in message
> news:Clg9d.183017$Np2.110030@.bignews4.bellsouth.ne t...
>> Am using SQL Server 2000, and am writing some audit triggers. How do I
> get
>> the the name of the user, eg. user_id or current_user. Just spent an
>> hour
>> looking through the SAMS book for this, and can't find any reference.
>>
>>

Current User

How can get the current user name/id of the connected user in Sql
Server 2005? I am using windows authentication. If I use USER or
CURRENT_USER function it always returns DBO and I am looking for the
network sign on which in my case would be URSC. Thanks.Try the ORIGINAL_LOGIN() function.
Hope this helps.
Dan Guzman
SQL Server MVP
"S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
news:1150200242.086128.219900@.p79g2000cwp.googlegroups.com...
> How can get the current user name/id of the connected user in Sql
> Server 2005? I am using windows authentication. If I use USER or
> CURRENT_USER function it always returns DBO and I am looking for the
> network sign on which in my case would be URSC. Thanks.
>|||Try using SUSER_SNAME. See BOL for more info.
AMB
"S Chapman" wrote:

> How can get the current user name/id of the connected user in Sql
> Server 2005? I am using windows authentication. If I use USER or
> CURRENT_USER function it always returns DBO and I am looking for the
> network sign on which in my case would be URSC. Thanks.
>|||"S Chapman" <s_chapman47@.hotmail.co.uk> escribi en el mensaje
news:1150200242.086128.219900@.p79g2000cwp.googlegroups.com...
> How can get the current user name/id of the connected user in Sql
> Server 2005? I am using windows authentication. If I use USER or
> CURRENT_USER function it always returns DBO
If the user is admin, you will get dbo
Peter

current security context is not trusted (cross db ownership chaini

I cannot seem to get cross database ownership chaining to work.
Here’s what I have so far:
? I have a user in DatabaseA who is only in the public database role.
? In DatabaseB, I have created a ‘MyUsers’ database role (owned by dbo
), and
a ‘MySchema’ schema (also owned by dbo). I have granted select, execute
on
MySchema to MyUsers.
? The user in DatabaseB is in public and MyUsers database roles, and uses
MySchema as the default schema.
? I have enabled the ‘cross db ownership chaining’ option in both data
bases,
and also at the instance level.
? In DatabaseB, I have compiled MySchema.MyStoredProc which selects data
from DatabaseA.
When the user executes DatabaseB.MySchema.MyStoredProc, this error is raised
:
SELECT permission denied on object 'TableA', database 'DatabaseA', schema
'dbo'.
When MyStoredProc is recompiled WITH EXECUTE AS SELF (or OWNER), this error
is raised:
Access to the remote server is denied because the current security context
is not trusted.
Here are the particulars:
? SQL 2005 - 9.00.1399.06 (Intel X86), Build 2600: Service Pack 2
? Both databases have Compatibility Level = SQL Server 2000, although I’
ve
changed both to 2005 and the error persists.
Also, when I look at the Database Properties (Options property page), the
‘Cross-database Ownership Chaining Enabled’ property says False, and is
disabled for editing, even though sp_configure shows the value as 1.
Thanks,
Sam Tai> When the user executes DatabaseB.MySchema.MyStoredProc, this error is
> raised:
> SELECT permission denied on object 'TableA', database 'DatabaseA', schema
> 'dbo'.
Check to ensure that both DatabaseA and DatabaseB are owned by the same
login (same authorization). Although the authorization on both the
DatabaseB.MySchema and DatabaseA.dbo schema is 'dbo', these will map to
different server principals if the database owners are different and break
the ownership chain.
Also, the user in DatabaseB will need a security context in DatabaseA, even
if no permissions are granted. You'll need to either add the user or enable
the guest user in that database.
Hope this helps.
Dan Guzman
SQL Server MVP
"Sam Tai" <Sam Tai@.discussions.microsoft.com> wrote in message
news:1D3296BF-1228-4603-B433-97CC8FC53CE6@.microsoft.com...
>I cannot seem to get cross database ownership chaining to work.
> Here’s what I have so far:
> ? I have a user in DatabaseA who is only in the public database role.
> ? In DatabaseB, I have created a ‘MyUsers’ database role (owned by d
bo),
> and
> a ‘MySchema’ schema (also owned by dbo). I have granted select, execu
te
> on
> MySchema to MyUsers.
> ? The user in DatabaseB is in public and MyUsers database roles, and use
s
> MySchema as the default schema.
> ? I have enabled the ‘cross db ownership chaining’ option in both
> databases,
> and also at the instance level.
> ? In DatabaseB, I have compiled MySchema.MyStoredProc which selects data
> from DatabaseA.
> When the user executes DatabaseB.MySchema.MyStoredProc, this error is
> raised:
> SELECT permission denied on object 'TableA', database 'DatabaseA', schema
> 'dbo'.
> When MyStoredProc is recompiled WITH EXECUTE AS SELF (or OWNER), this
> error
> is raised:
> Access to the remote server is denied because the current security context
> is not trusted.
> Here are the particulars:
> ? SQL 2005 - 9.00.1399.06 (Intel X86), Build 2600: Service Pack 2
> ? Both databases have Compatibility Level = SQL Server 2000, although I
ve
> changed both to 2005 and the error persists.
> Also, when I look at the Database Properties (Options property page), the
> ‘Cross-database Ownership Chaining Enabled’ property says False, and i
s
> disabled for editing, even though sp_configure shows the value as 1.
> Thanks,
> Sam Tai|||Sam Tai (Sam Tai@.discussions.microsoft.com) writes:
> I cannot seem to get cross database ownership chaining to work.
> Heres what I have so far:
> I have a user in DatabaseA who is only in the public database role.
> In DatabaseB, I have created a MyUsers database role (owned by dbo),
> and a MySchema schema (also owned by dbo). I have granted select,
> execute on MySchema to MyUsers .
> The user in DatabaseB is in public and MyUsers database roles, and uses
> MySchema as the default schema.
> I have enabled the cross db ownership chaining option in both
> databases, and also at the instance level.
> In DatabaseB, I have compiled MySchema.MyStoredProc which selects data
> from DatabaseA.
You seem to be missing one thing: you need to do
ALTER DATABASE db SET DB_CHAINING ON
in both databases.

> When MyStoredProc is recompiled WITH EXECUTE AS SELF (or OWNER), this
> error is raised: Access to the remote server is denied because the
> current security context is not trusted.
This is because EXECUTE AS sets a database-user context, and you are
sandboxed into the current database. You make this, you need to set
the database as trustworthy. You also need to deal with certificates.
I have this in more detail on my web site in this article:
http://www.sommarskog.se/grantperm.html

> Here are the particulars:
> SQL 2005 - 9.00.1399.06 (Intel X86), Build 2600: Service Pack 2
Oh-oh, that is SQL 2005 RTM. I would recommend that you install SP2. To
make things a little more complicated there are some seroius bugs with
maintenance plans in SP2 as it was released, so make sure that you have
at least version 9.00.3054 when you are done. (The "Service Pack 2" in
the string relates to Windows.)
I should emphasize that this is not related to your issue, just a general
piece of advice.
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

Currency Conversion SSAS 2005

I would like to have calculated members in a currency dimension that will apply an exchange rate to all applicable measures based on what the user selects. All measures in the fact table are stored in one currency (USD) but need to be reported in a variety of currencies.

In AS 2000 this was accomplished via a lookup cube. I have read the documentation on books on line regarding currency conversion with the Business Intelligence Wizard in SSAS 2005 and this seems overly complicated and will not work based on our needs (since our data is stored in one currency and only the most recent exchange rate is important, no date dimension is necessary).

I have a fact table with with currency code and converstion rate. Since all of our measures are stored in USD, I do not have a currency key in the other fact tables. Is what I am trying to do possible without adding the currency key to all the fact tables?

Any suggestions are greatly appreciated.

Even in AS2000 with your setup, LookupCube wasn't the best solution. The best solution would've been to create virtual cube, and then multiply the USD measure by ValidMeasure(Rate). The same approach should work in AS2005 as well, only instead of virtual cube, you would have two measure groups in your cube.|||

Thank you for the reponse.

Can you expand upon what you mean by using the ValidMeasure function?

I have a measure group with the Converstion Rate. The only dimension related to this measure group is the currency dimension. I would like to be able to have one calculated measure per currency code that multiplies all related measures stored in USD by the corresponding conversion rate.

So that leaves me with two issues:

1) How to apply the conversion rate to the measures based on what currency is selected in the currency dimension.

2) How to filter the solution to only apply to financial measures (the count measures should not adjust)

|||

Here is an example of how MDX Script could look like to achieve both of your issues. This assumes that Currency attribte is non-aggregatable, as it makes no sense to aggregate across different currencies.

({Measures.FinancialMeasure1, Measures.FinancialMeasure2, Measures.FinancialMeasure3}, Currency.Currency.Currency.MEMBERS) = Measures.CurrentMember*ValidMeasure(Measures.ConversionRate);

(more details can be found here: http://www.sqljunkies.com/WebLog/mosha/archive/2005/12/06/multiplication_perf.aspx)

|||Works perfectly! Thanks! |||

It seems that this solution has caused another issue. The measures that this logic was applied to are not rolling up correctly. When I filter a dimension on the rows other than at the all level, the totals do not reflect the current selection of data.

For example, if I want to display the units and amount by product for all products, my result set might look something like this:

Product Units Amount

A 1 10

B 1 10

C 1 10

Total 3 30

If I want to look at only Product A, my result looks like this:

Product Units Amount

A 1 10

Total 1 30

The Total for the Units measure is correct because it's value does not change based on the currency that the user has selected, however, the Total for the Amount measure is incorrect and does not take into account what dimension filters are being applied.

Do you have any suggestions?

|||You can apply the currency conversion only at Leaves() - then they will be rolled up to the higher levels, and then visual totals should work correctly. However, be aware that the performance might become worse. Yet another approach would be to use measure expressions to do currency conversion during processing. This solution is not as flexible, but I think it will fit your scenario too.|||

I cannot use ValidMeasure with the Measure Expressions thus the conversion is inaccurrate. Can you expand on the Leaves() suggestion?

|||

I was able to use Measure Expressions to solve this problem after adding a date key to the currency conversion fact table. So now the currency conversion fact table looks like this:

Currency Key

Time Key

Conversion Rate

I am able to link the currency fact table to the other fact tables via the Time dimension and perform the currency conversion calculations using Measure Expressions based on what Currency is selected in Currency Dimension. Not as dynamic as placing this logic in an MDX Script but it works and the performance is great! Thanks Mosha!

Currency Conversion SSAS 2005

I would like to have calculated members in a currency dimension that will apply an exchange rate to all applicable measures based on what the user selects. All measures in the fact table are stored in one currency (USD) but need to be reported in a variety of currencies.

In AS 2000 this was accomplished via a lookup cube. I have read the documentation on books on line regarding currency conversion with the Business Intelligence Wizard in SSAS 2005 and this seems overly complicated and will not work based on our needs (since our data is stored in one currency and only the most recent exchange rate is important, no date dimension is necessary).

I have a fact table with with currency code and converstion rate. Since all of our measures are stored in USD, I do not have a currency key in the other fact tables. Is what I am trying to do possible without adding the currency key to all the fact tables?

Any suggestions are greatly appreciated.

Even in AS2000 with your setup, LookupCube wasn't the best solution. The best solution would've been to create virtual cube, and then multiply the USD measure by ValidMeasure(Rate). The same approach should work in AS2005 as well, only instead of virtual cube, you would have two measure groups in your cube.|||

Thank you for the reponse.

Can you expand upon what you mean by using the ValidMeasure function?

I have a measure group with the Converstion Rate. The only dimension related to this measure group is the currency dimension. I would like to be able to have one calculated measure per currency code that multiplies all related measures stored in USD by the corresponding conversion rate.

So that leaves me with two issues:

1) How to apply the conversion rate to the measures based on what currency is selected in the currency dimension.

2) How to filter the solution to only apply to financial measures (the count measures should not adjust)

|||

Here is an example of how MDX Script could look like to achieve both of your issues. This assumes that Currency attribte is non-aggregatable, as it makes no sense to aggregate across different currencies.

({Measures.FinancialMeasure1, Measures.FinancialMeasure2, Measures.FinancialMeasure3}, Currency.Currency.Currency.MEMBERS) = Measures.CurrentMember*ValidMeasure(Measures.ConversionRate);

(more details can be found here: http://www.sqljunkies.com/WebLog/mosha/archive/2005/12/06/multiplication_perf.aspx)

|||Works perfectly! Thanks! |||

It seems that this solution has caused another issue. The measures that this logic was applied to are not rolling up correctly. When I filter a dimension on the rows other than at the all level, the totals do not reflect the current selection of data.

For example, if I want to display the units and amount by product for all products, my result set might look something like this:

Product Units Amount

A 1 10

B 1 10

C 1 10

Total 3 30

If I want to look at only Product A, my result looks like this:

Product Units Amount

A 1 10

Total 1 30

The Total for the Units measure is correct because it's value does not change based on the currency that the user has selected, however, the Total for the Amount measure is incorrect and does not take into account what dimension filters are being applied.

Do you have any suggestions?

|||You can apply the currency conversion only at Leaves() - then they will be rolled up to the higher levels, and then visual totals should work correctly. However, be aware that the performance might become worse. Yet another approach would be to use measure expressions to do currency conversion during processing. This solution is not as flexible, but I think it will fit your scenario too.|||

I cannot use ValidMeasure with the Measure Expressions thus the conversion is inaccurrate. Can you expand on the Leaves() suggestion?

|||

I was able to use Measure Expressions to solve this problem after adding a date key to the currency conversion fact table. So now the currency conversion fact table looks like this:

Currency Key

Time Key

Conversion Rate

I am able to link the currency fact table to the other fact tables via the Time dimension and perform the currency conversion calculations using Measure Expressions based on what Currency is selected in Currency Dimension. Not as dynamic as placing this logic in an MDX Script but it works and the performance is great! Thanks Mosha!

Currency Conversion SSAS 2005

I would like to have calculated members in a currency dimension that will apply an exchange rate to all applicable measures based on what the user selects. All measures in the fact table are stored in one currency (USD) but need to be reported in a variety of currencies.

In AS 2000 this was accomplished via a lookup cube. I have read the documentation on books on line regarding currency conversion with the Business Intelligence Wizard in SSAS 2005 and this seems overly complicated and will not work based on our needs (since our data is stored in one currency and only the most recent exchange rate is important, no date dimension is necessary).

I have a fact table with with currency code and converstion rate. Since all of our measures are stored in USD, I do not have a currency key in the other fact tables. Is what I am trying to do possible without adding the currency key to all the fact tables?

Any suggestions are greatly appreciated.

Even in AS2000 with your setup, LookupCube wasn't the best solution. The best solution would've been to create virtual cube, and then multiply the USD measure by ValidMeasure(Rate). The same approach should work in AS2005 as well, only instead of virtual cube, you would have two measure groups in your cube.|||

Thank you for the reponse.

Can you expand upon what you mean by using the ValidMeasure function?

I have a measure group with the Converstion Rate. The only dimension related to this measure group is the currency dimension. I would like to be able to have one calculated measure per currency code that multiplies all related measures stored in USD by the corresponding conversion rate.

So that leaves me with two issues:

1) How to apply the conversion rate to the measures based on what currency is selected in the currency dimension.

2) How to filter the solution to only apply to financial measures (the count measures should not adjust)

|||

Here is an example of how MDX Script could look like to achieve both of your issues. This assumes that Currency attribte is non-aggregatable, as it makes no sense to aggregate across different currencies.

({Measures.FinancialMeasure1, Measures.FinancialMeasure2, Measures.FinancialMeasure3}, Currency.Currency.Currency.MEMBERS) = Measures.CurrentMember*ValidMeasure(Measures.ConversionRate);

(more details can be found here: http://www.sqljunkies.com/WebLog/mosha/archive/2005/12/06/multiplication_perf.aspx)

|||Works perfectly! Thanks! |||

It seems that this solution has caused another issue. The measures that this logic was applied to are not rolling up correctly. When I filter a dimension on the rows other than at the all level, the totals do not reflect the current selection of data.

For example, if I want to display the units and amount by product for all products, my result set might look something like this:

Product Units Amount

A 1 10

B 1 10

C 1 10

Total 3 30

If I want to look at only Product A, my result looks like this:

Product Units Amount

A 1 10

Total 1 30

The Total for the Units measure is correct because it's value does not change based on the currency that the user has selected, however, the Total for the Amount measure is incorrect and does not take into account what dimension filters are being applied.

Do you have any suggestions?

|||You can apply the currency conversion only at Leaves() - then they will be rolled up to the higher levels, and then visual totals should work correctly. However, be aware that the performance might become worse. Yet another approach would be to use measure expressions to do currency conversion during processing. This solution is not as flexible, but I think it will fit your scenario too.|||

I cannot use ValidMeasure with the Measure Expressions thus the conversion is inaccurrate. Can you expand on the Leaves() suggestion?

|||

I was able to use Measure Expressions to solve this problem after adding a date key to the currency conversion fact table. So now the currency conversion fact table looks like this:

Currency Key

Time Key

Conversion Rate

I am able to link the currency fact table to the other fact tables via the Time dimension and perform the currency conversion calculations using Measure Expressions based on what Currency is selected in Currency Dimension. Not as dynamic as placing this logic in an MDX Script but it works and the performance is great! Thanks Mosha!

Cube usage by users

Hi,

What would be the correct way to generate some kind of log or report that will tell me which user used which cube in which database along with the number of times the user connected to each cube each day? The number of queries sent by each user to each cube by day would be ok. I am not interested by the query content for this exercise. I just want Userid, DB name, Cube name and Hits.

Then I can take it from there and create a report for our security/sox guy with User name, Cube name, Week of year and number of hits by week, meaning if the users send 36 queries to Cube A on Monday and 2 queries on Friday, it counts for 2 cube accesses for that week.

This assume that I use only TCP/IP access but if the same mechanism would work for HTTP access that would be fine. I try not to use HTTP but I may be forced to do it.

Regardless of network transport, the only logins allowed are though Windows Domain Global Group Membership.

Seems simple but I do not find where to start.

Thanks,

Philippe

Hello,

I have found a solution to this issue.

In the Server property box, you will enable query loging to a table and set the interval to 1 instead of 10.

Stop and Start the AS Server

Create some query or cube to access your log data, you are done.

I cannot really provide a query example since it depends on your situation. Mine is linked to a Calendar and to a Users tables. I convert the query time to keep only the day and the username to keep only the userid, then I build some Cube and/or Report that joins all that stuff and do some DistinctCount on the daily access by Cube by User and voila. I keep my infosec guy happy and I know what are the cubes I can retire for cause of no use.

You know, sometimes they ask you to build a cube real quick, they use it once and then they forget about it (and do not tell you). So I am not maintaining useless cubes :-)

Regards,

Philippe

Cube process problem. Urgent

Hi,

After I changed password for my machine, the cubes process

got system error: “ The following system error occurred:Logon failure: unknown user name or bad

password. “ We have 10 more cubes and still can browse the cubes but no any cube

can processes now.

Before I changed password, everything is working.

We use ‘Windows

Authentication’ for SQL server 2005 Database and Analysis Service.

Anyone can help this problem?

Thanks.

Hello! Check this. Open the cube project in BIDS and change the impersonation mode to default in the data connection.

HTH

Thomas Ivarsson

|||

Hi,

If by any chance that does not work,

set specific credentials for the username and password in the impersonation mode tab.

Also, as a secondary check, see if the user has process permissions.

Regards

Vijay R

|||It sounds like the SSAS service on the server may be set up to use your account. Jump on to the server and check the account credentials using the SQL Server Configuration Manager application. If the service is running under your user account you will either need to update the password or preferrably set up an account for the SSAS service.|||Thank you very much Thomas and Vijay for replaying my email.

I changed the impersonation mode to default then processing works.

Thanks a lot.

Cube Browser not filtering the data for the user having only read definition permission

Hi,

I am started working with SAS2005 recently. Using Adventure works sample database.

When I login to the Management Studio as administrator and browse the cube, the browser filters all the null value columns and rows.

Steps:

1. Add Product.Product Categories to filter area and select only Bikes

2. Add Date.Calendar.Date Calendar year to filter area and select year 2005

3. Add Product.Product to Row area

4. Add Measures.Internet Sales.Internet Sales Amount in the Total or detail area

5. Add [Date].[Month of Year] in the column area.

When I logged in as administrator or the user with the role Full control (administrator) and all the nessasary permission set on all the objects. The cube browser displays the data where only it has values. If the entire row or column are null then the row and columns are not displayed.

When I do the exactly same with the user has a role instead of full control using just read definition option (I do not want the user to get full permission, like to make a role as read only) , the browse cube shows all the rows and columns, even the row and columns where all the values are null.

The empty cells are displayed as #N/A.

Is it possible to make the browser behave for the read only user as exactly like administrator user.

Any suggestions and recommendations are great.

Thanks

I am stuck with this problem. Please somebody help...

Cube backup job failed

Message
Executed as user: INTRANET\SSAgentTrendP1. ...ystem error: The following error occurred while the '\\?\H:\MSSQLDATA\TrendP1\Trend.0.db\Trend.0.cub\Claim Summary V.0.det\Claim Summary V.0.prt\8.fact.data' file was being copied to the 'G:\MSSQLDATA\TrendP1\Auto_before.abf' file: . at Microsoft.AnalysisServices.Xmla.XmlaClient.CheckForSoapFault(XmlReader reader, XmlaResult xmlaResult, Boolean throwIfError) at Microsoft.AnalysisServices.Xmla.XmlaClient.CheckForError(XmlReader reader, XmlaResult xmlaResult, Boolean throwIfError) at Microsoft.AnalysisServices.Xmla.XmlaClient.SendMessage(Boolean endReceivalIfException, Boolean readSession, Boolean readNamespaceCompatibility) at Microsoft.AnalysisServices.Xmla.XmlaClient.SendMessageAndReturnResult(String& result, Boolean skipResult) at Microsoft.AnalysisServices.Xmla.XmlaClient.Execute(String command, String properties, String& result, Boolean skipResult, Boolean propertiesXmlIsComplete) at Microsoft.SqlServer.Management.Smo.Olap.Soap. The step failed.

This is the job step definition:

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>Trend</DatabaseID>
</Object>
<File>G:\MSSQLDATA\TrendP1\Auto_before.abf</File>
<AllowOverwrite>true</AllowOverwrite>
<ApplyCompression>false</ApplyCompression>
</Backup>

This job has been running for a while. The problem started after I did a restore of the cube at one point of time.

|||

Further investigation reveals that one of the fact.data file had growed to 4G in size and the backup started to fail.

Is there a size limitation of individual partition's size in SQL Server 2005 OLAP serivice? I have searched the BOL but can't find any reference.

Thanks,

|||

according to MS it has been removed from 2 g from SSAS 2000 to no limit in SSAS 2005!! but facing same error message while backing up!!

Cube backup job failed

Message
Executed as user: INTRANET\SSAgentTrendP1. ...ystem error: The following error occurred while the '\\?\H:\MSSQLDATA\TrendP1\Trend.0.db\Trend.0.cub\Claim Summary V.0.det\Claim Summary V.0.prt\8.fact.data' file was being copied to the 'G:\MSSQLDATA\TrendP1\Auto_before.abf' file: . at Microsoft.AnalysisServices.Xmla.XmlaClient.CheckForSoapFault(XmlReader reader, XmlaResult xmlaResult, Boolean throwIfError) at Microsoft.AnalysisServices.Xmla.XmlaClient.CheckForError(XmlReader reader, XmlaResult xmlaResult, Boolean throwIfError) at Microsoft.AnalysisServices.Xmla.XmlaClient.SendMessage(Boolean endReceivalIfException, Boolean readSession, Boolean readNamespaceCompatibility) at Microsoft.AnalysisServices.Xmla.XmlaClient.SendMessageAndReturnResult(String& result, Boolean skipResult) at Microsoft.AnalysisServices.Xmla.XmlaClient.Execute(String command, String properties, String& result, Boolean skipResult, Boolean propertiesXmlIsComplete) at Microsoft.SqlServer.Management.Smo.Olap.Soap. The step failed.

This is the job step definition:

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>Trend</DatabaseID>
</Object>
<File>G:\MSSQLDATA\TrendP1\Auto_before.abf</File>
<AllowOverwrite>true</AllowOverwrite>
<ApplyCompression>false</ApplyCompression>
</Backup>

This job has been running for a while. The problem started after I did a restore of the cube at one point of time.

|||

Further investigation reveals that one of the fact.data file had growed to 4G in size and the backup started to fail.

Is there a size limitation of individual partition's size in SQL Server 2005 OLAP serivice? I have searched the BOL but can't find any reference.

Thanks,

|||

according to MS it has been removed from 2 g from SSAS 2000 to no limit in SSAS 2005!! but facing same error message while backing up!!

Cube backup job failed

Message
Executed as user: INTRANET\SSAgentTrendP1. ...ystem error: The following error occurred while the '\\?\H:\MSSQLDATA\TrendP1\Trend.0.db\Trend.0.cub\Claim Summary V.0.det\Claim Summary V.0.prt\8.fact.data' file was being copied to the 'G:\MSSQLDATA\TrendP1\Auto_before.abf' file: . at Microsoft.AnalysisServices.Xmla.XmlaClient.CheckForSoapFault(XmlReader reader, XmlaResult xmlaResult, Boolean throwIfError) at Microsoft.AnalysisServices.Xmla.XmlaClient.CheckForError(XmlReader reader, XmlaResult xmlaResult, Boolean throwIfError) at Microsoft.AnalysisServices.Xmla.XmlaClient.SendMessage(Boolean endReceivalIfException, Boolean readSession, Boolean readNamespaceCompatibility) at Microsoft.AnalysisServices.Xmla.XmlaClient.SendMessageAndReturnResult(String& result, Boolean skipResult) at Microsoft.AnalysisServices.Xmla.XmlaClient.Execute(String command, String properties, String& result, Boolean skipResult, Boolean propertiesXmlIsComplete) at Microsoft.SqlServer.Management.Smo.Olap.Soap. The step failed.

This is the job step definition:

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>Trend</DatabaseID>
</Object>
<File>G:\MSSQLDATA\TrendP1\Auto_before.abf</File>
<AllowOverwrite>true</AllowOverwrite>
<ApplyCompression>false</ApplyCompression>
</Backup>

This job has been running for a while. The problem started after I did a restore of the cube at one point of time.

|||

Further investigation reveals that one of the fact.data file had growed to 4G in size and the backup started to fail.

Is there a size limitation of individual partition's size in SQL Server 2005 OLAP serivice? I have searched the BOL but can't find any reference.

Thanks,

|||

according to MS it has been removed from 2 g from SSAS 2000 to no limit in SSAS 2005!! but facing same error message while backing up!!

CTE, I have a dream...

If they (CTE) were parameterized, I would stop working user functions
for use in only one place.
/*
* CTE, I have a dream...
*/
With TopRatedEmployees
(
@.DepartmentId int
)
RETURNS TABLE
AS
(
SELECT TOP 10 EmployeeId,
EmployeeName,
Rank
FROM Employees
WHERE DepartmentId = @.DepartmentId
ORDER BY Rank DESC
)
With EmployeesCount
(
@.DepartmentId int
)
RETURNS int
AS
(
SELECT COUNT(*)
FROM Employees
WHERE DepartmentId = @.DepartmentId
)
SELECT Departments.Id,
Departments.Name,
dbo.EmployeesCount(Department.Id),
TopRatedEmployees1.EmployeeId,
TopRatedEmployees1.EmployeeName,
FROM Departments
CROSS APPLY
dbo.TopRatedEmployees(Department.Id) AS TopRatedEmployees1
ORDER BY Departments.Name;
GO<guercheLE@.gmail.com> wrote in message
news:1151617460.266574.183380@.j72g2000cwa.googlegroups.com...
> If they (CTE) were parameterized, I would stop working user functions
> for use in only one place.
> /*
> * CTE, I have a dream...
> */
> With TopRatedEmployees
> (
> @.DepartmentId int
> )
> RETURNS TABLE
> AS
> (
> SELECT TOP 10 EmployeeId,
> EmployeeName,
> Rank
> FROM Employees
> WHERE DepartmentId = @.DepartmentId
> ORDER BY Rank DESC
> )
> With EmployeesCount
> (
> @.DepartmentId int
> )
> RETURNS int
> AS
> (
> SELECT COUNT(*)
> FROM Employees
> WHERE DepartmentId = @.DepartmentId
> )
> SELECT Departments.Id,
> Departments.Name,
> dbo.EmployeesCount(Department.Id),
> TopRatedEmployees1.EmployeeId,
> TopRatedEmployees1.EmployeeName,
> FROM Departments
> CROSS APPLY
> dbo.TopRatedEmployees(Department.Id) AS TopRatedEmployees1
> ORDER BY Departments.Name;
> GO
>
Assuming I've understood your pseudo-code correctly you can do it like this:
SELECT D.DepartmentId,
D.Name,
D.DepartmentId,
TopRatedEmployees.EmployeeId,
TopRatedEmployees.EmployeeName
FROM Departments AS D
CROSS APPLY
(
SELECT TOP 10 EmployeeId,
EmployeeName,
Rank
FROM Employees
WHERE DepartmentId = D.DepartmentId
ORDER BY Rank DESC
) AS TopRatedEmployees
CROSS APPLY
(
SELECT D.DepartmentId, COUNT(*) AS cnt
FROM Employees
WHERE DepartmentId = D.DepartmentId
) AS EmployeesCount
ORDER BY D.Name;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

CSV DeviceSettings in ReportServer.config dont take effect

I am using MS Reporting Services 2000 SP2 and I need help very urgently. my issue is that the user needs to view Text datatype columns exported to csv (basically, customer service support notes), but with the text fields in one cell. I changed the configuration settings of the CSV rendering extension as below, but but it didnt seem to make any difference. The report continues to export the csv in a haphazard format, with the newline characters in the notes spilling onto the next line, etc.

I also noticed that when I used ReportManager, the url request didnt have the rc:Encoding or rc:SuppressLineBreaks settings when I try to export the report to csv. I added these as a test in the url request directly and it worked just fine. I tried changing various settings, but none of them seem to take any effect. What am I missing here ?

I tried restarting the web server AND the ReportServer service. My config settings are as below. Also, my config file is located at:

C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\RSReportServer.config

<Render>
... ... ...
... ... ...
<Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
<Configuration>
<DeviceInfo>
<Encoding>ASCII</Encoding>
<SuppressLineBreaks>True</SuppressLineBreaks>
</DeviceInfo>
</Configuration>
</Extension>
</Render>

Any advice would be greatly appreciated. Thanks in advance.

I found the below information on another post.. Specifically, the thread "Export to an ASCII CSV" by Bruce L-C (MVP) on 4/19/2006. According to him, the settings (He speaks of only the encoding setting) work in RS 2000 only when passed in the URL explicitly. Setting it in the Config file does not have any effect in RS 2000, they are new for RS 2005.

http://msdn.microsoft.com/newsgroups/Default.aspx?query=device+settings+reporting+services&dg=&cat=en-us-msdn&lang=en&cr=US&pt=&catlist=774F24A2-F71F-425F-AC2B-DC48AB0DA5C9&dglist=&ptlist=&exp=&sloc=en-us

I hope someone can prove me wrong here.... I would love to figure out how to do this in RS 2000. Any advice would be highly appreciated. Thanks in advance.

CSV

I am attempting to exports the results of a stored Procedure into a CSV. The
table is a temp tabel named #FinalPrivate. I need to allow a user to download
the file from a .NET Web Application. Is there someone that can point me in a
direction?
This sounds like a one-time, or not frequent need. If the results are
<65000 records, it is probaby easiest to highlight the results in query
analyzer (hit CTRL - A on any cell to select all cells), hit CTRL-C to copy
to the clipboard, and then paste those values into Excel. Then, in Excel,
choose to "save as" type = comma delimited / CSV.
I do this often and it's easier than your alternatives. If you need an
automated way that works by itself every 24 hours, then that's a different
story.
"sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com>
wrote in message news:7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...
> I am attempting to exports the results of a stored Procedure into a CSV.
The
> table is a temp tabel named #FinalPrivate. I need to allow a user to
download
> the file from a .NET Web Application. Is there someone that can point me
in a
> direction?
|||Yes, this is something that needs to be done ~5 times a week by customers on
demand. I need to complete this task programable. Any other thoughts?
"HK" wrote:

> This sounds like a one-time, or not frequent need. If the results are
> <65000 records, it is probaby easiest to highlight the results in query
> analyzer (hit CTRL - A on any cell to select all cells), hit CTRL-C to copy
> to the clipboard, and then paste those values into Excel. Then, in Excel,
> choose to "save as" type = comma delimited / CSV.
> I do this often and it's easier than your alternatives. If you need an
> automated way that works by itself every 24 hours, then that's a different
> story.
> "sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com>
> wrote in message news:7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...
> The
> download
> in a
>
>
|||Why not simply creating a DTS (or SSIS if you're on 2005) package that will
create that CSV file? Then it is a one click execute process.
"sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com> a
crit dans le message de news:
7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...
>I am attempting to exports the results of a stored Procedure into a CSV.
>The
> table is a temp tabel named #FinalPrivate. I need to allow a user to
> download
> the file from a .NET Web Application. Is there someone that can point me
> in a
> direction?
|||It seems this is an ASP.NET issue, and not a TSQL issue. You simply take
the results of the stored procedure and output as comma delimited in your
..NET code. Here is a very quick example I just pulled from google; it is
not be the best for your needs. The trick for letting the user vjew the
file is the line that outputs the "contenttype" to the browser.
http://dotnet.org.za/keithrull/archi.../14/39202.aspx
That example is more meant for Excel output but I share it because it goes
into detail about things.
However, I prefer to use a method with ContentType="text/csv", which lets
the user download the file immediately as a CSV file. Look for an example
with that. To quote Jim Buyens in a google groups thread, he writes:
If you're only sending data, the easiest approach is to execute this code:
Response.ContentType = "text/csv"
Response.AddHeader "content-disposition",_
"attachment; filename=yourfile.csv"
then send the visitor a comma-separated-values file via Response.Write, then
call Response.End to make sure that no HTML or other output follows the
data.
"sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com>
wrote in message news:D9471EF6-7601-47A6-A723-5B1045B150F6@.microsoft.com...
> Yes, this is something that needs to be done ~5 times a week by customers
on[vbcol=seagreen]
> demand. I need to complete this task programable. Any other thoughts?
> "HK" wrote:
copy[vbcol=seagreen]
Excel,[vbcol=seagreen]
different[vbcol=seagreen]
news:7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...[vbcol=seagreen]
CSV.[vbcol=seagreen]
me[vbcol=seagreen]
|||Try www.sqlscripter.com to export data to text/csv.
It's free.
"sorourke1@.hotmail.com" wrote:

> I am attempting to exports the results of a stored Procedure into a CSV. The
> table is a temp tabel named #FinalPrivate. I need to allow a user to download
> the file from a .NET Web Application. Is there someone that can point me in a
> direction?

CSV

I am attempting to exports the results of a stored Procedure into a CSV. The
table is a temp tabel named #FinalPrivate. I need to allow a user to download
the file from a .NET Web Application. Is there someone that can point me in a
direction?This sounds like a one-time, or not frequent need. If the results are
<65000 records, it is probaby easiest to highlight the results in query
analyzer (hit CTRL - A on any cell to select all cells), hit CTRL-C to copy
to the clipboard, and then paste those values into Excel. Then, in Excel,
choose to "save as" type = comma delimited / CSV.
I do this often and it's easier than your alternatives. If you need an
automated way that works by itself every 24 hours, then that's a different
story.
"sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com>
wrote in message news:7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...
> I am attempting to exports the results of a stored Procedure into a CSV.
The
> table is a temp tabel named #FinalPrivate. I need to allow a user to
download
> the file from a .NET Web Application. Is there someone that can point me
in a
> direction?|||Yes, this is something that needs to be done ~5 times a week by customers on
demand. I need to complete this task programable. Any other thoughts?
"HK" wrote:
> This sounds like a one-time, or not frequent need. If the results are
> <65000 records, it is probaby easiest to highlight the results in query
> analyzer (hit CTRL - A on any cell to select all cells), hit CTRL-C to copy
> to the clipboard, and then paste those values into Excel. Then, in Excel,
> choose to "save as" type = comma delimited / CSV.
> I do this often and it's easier than your alternatives. If you need an
> automated way that works by itself every 24 hours, then that's a different
> story.
> "sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com>
> wrote in message news:7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...
> > I am attempting to exports the results of a stored Procedure into a CSV.
> The
> > table is a temp tabel named #FinalPrivate. I need to allow a user to
> download
> > the file from a .NET Web Application. Is there someone that can point me
> in a
> > direction?
>
>|||Why not simply creating a DTS (or SSIS if you're on 2005) package that will
create that CSV file? Then it is a one click execute process.
"sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com> a
écrit dans le message de news:
7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...
>I am attempting to exports the results of a stored Procedure into a CSV.
>The
> table is a temp tabel named #FinalPrivate. I need to allow a user to
> download
> the file from a .NET Web Application. Is there someone that can point me
> in a
> direction?|||It seems this is an ASP.NET issue, and not a TSQL issue. You simply take
the results of the stored procedure and output as comma delimited in your
.NET code. Here is a very quick example I just pulled from google; it is
not be the best for your needs. The trick for letting the user vjew the
file is the line that outputs the "contenttype" to the browser.
http://dotnet.org.za/keithrull/archive/2005/07/14/39202.aspx
That example is more meant for Excel output but I share it because it goes
into detail about things.
However, I prefer to use a method with ContentType="text/csv", which lets
the user download the file immediately as a CSV file. Look for an example
with that. To quote Jim Buyens in a google groups thread, he writes:
If you're only sending data, the easiest approach is to execute this code:
Response.ContentType = "text/csv"
Response.AddHeader "content-disposition",_
"attachment; filename=yourfile.csv"
then send the visitor a comma-separated-values file via Response.Write, then
call Response.End to make sure that no HTML or other output follows the
data.
"sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com>
wrote in message news:D9471EF6-7601-47A6-A723-5B1045B150F6@.microsoft.com...
> Yes, this is something that needs to be done ~5 times a week by customers
on
> demand. I need to complete this task programable. Any other thoughts?
> "HK" wrote:
> > This sounds like a one-time, or not frequent need. If the results are
> > <65000 records, it is probaby easiest to highlight the results in query
> > analyzer (hit CTRL - A on any cell to select all cells), hit CTRL-C to
copy
> > to the clipboard, and then paste those values into Excel. Then, in
Excel,
> > choose to "save as" type = comma delimited / CSV.
> >
> > I do this often and it's easier than your alternatives. If you need an
> > automated way that works by itself every 24 hours, then that's a
different
> > story.
> >
> > "sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com>
> > wrote in message
news:7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...
> > > I am attempting to exports the results of a stored Procedure into a
CSV.
> > The
> > > table is a temp tabel named #FinalPrivate. I need to allow a user to
> > download
> > > the file from a .NET Web Application. Is there someone that can point
me
> > in a
> > > direction?
> >
> >
> >|||How do you create this DTS Package for exporting into a text file? I would
like to create and copy into a flat text file, but only see Bulk Insert that
will copy into SQL Tables. Could you provide some insight into how to export
using DTS.
Thanks!
"Christian Hamel" wrote:
> Why not simply creating a DTS (or SSIS if you're on 2005) package that will
> create that CSV file? Then it is a one click execute process.
>
> "sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com> a
> écrit dans le message de news:
> 7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...
> >I am attempting to exports the results of a stored Procedure into a CSV.
> >The
> > table is a temp tabel named #FinalPrivate. I need to allow a user to
> > download
> > the file from a .NET Web Application. Is there someone that can point me
> > in a
> > direction?
>
>|||Try www.sqlscripter.com to export data to text/csv.
It's free.
"sorourke1@.hotmail.com" wrote:
> I am attempting to exports the results of a stored Procedure into a CSV. The
> table is a temp tabel named #FinalPrivate. I need to allow a user to download
> the file from a .NET Web Application. Is there someone that can point me in a
> direction?

CSV

I am attempting to exports the results of a stored Procedure into a CSV. The
table is a temp tabel named #FinalPrivate. I need to allow a user to downloa
d
the file from a .NET Web Application. Is there someone that can point me in
a
direction?This sounds like a one-time, or not frequent need. If the results are
<65000 records, it is probaby easiest to highlight the results in query
analyzer (hit CTRL - A on any cell to select all cells), hit CTRL-C to copy
to the clipboard, and then paste those values into Excel. Then, in Excel,
choose to "save as" type = comma delimited / CSV.
I do this often and it's easier than your alternatives. If you need an
automated way that works by itself every 24 hours, then that's a different
story.
"sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com>
wrote in message news:7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...
> I am attempting to exports the results of a stored Procedure into a CSV.
The
> table is a temp tabel named #FinalPrivate. I need to allow a user to
download
> the file from a .NET Web Application. Is there someone that can point me
in a
> direction?|||Yes, this is something that needs to be done ~5 times a week by customers on
demand. I need to complete this task programable. Any other thoughts?
"HK" wrote:

> This sounds like a one-time, or not frequent need. If the results are
> <65000 records, it is probaby easiest to highlight the results in query
> analyzer (hit CTRL - A on any cell to select all cells), hit CTRL-C to cop
y
> to the clipboard, and then paste those values into Excel. Then, in Exce
l,
> choose to "save as" type = comma delimited / CSV.
> I do this often and it's easier than your alternatives. If you need an
> automated way that works by itself every 24 hours, then that's a different
> story.
> "sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com>
> wrote in message news:7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com..
.
> The
> download
> in a
>
>|||Why not simply creating a DTS (or SSIS if you're on 2005) package that will
create that CSV file? Then it is a one click execute process.
"sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com> a
crit dans le message de news:
7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...
>I am attempting to exports the results of a stored Procedure into a CSV.
>The
> table is a temp tabel named #FinalPrivate. I need to allow a user to
> download
> the file from a .NET Web Application. Is there someone that can point me
> in a
> direction?|||It seems this is an ASP.NET issue, and not a TSQL issue. You simply take
the results of the stored procedure and output as comma delimited in your
.NET code. Here is a very quick example I just pulled from google; it is
not be the best for your needs. The trick for letting the user vjew the
file is the line that outputs the "contenttype" to the browser.
http://dotnet.org.za/keithrull/arch...7/14/39202.aspx
That example is more meant for Excel output but I share it because it goes
into detail about things.
However, I prefer to use a method with ContentType="text/csv", which lets
the user download the file immediately as a CSV file. Look for an example
with that. To quote Jim Buyens in a google groups thread, he writes:
If you're only sending data, the easiest approach is to execute this code:
Response.ContentType = "text/csv"
Response.AddHeader "content-disposition",_
"attachment; filename=yourfile.csv"
then send the visitor a comma-separated-values file via Response.Write, then
call Response.End to make sure that no HTML or other output follows the
data.
"sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com>
wrote in message news:D9471EF6-7601-47A6-A723-5B1045B150F6@.microsoft.com...
> Yes, this is something that needs to be done ~5 times a week by customers
on[vbcol=seagreen]
> demand. I need to complete this task programable. Any other thoughts?
> "HK" wrote:
>
copy[vbcol=seagreen]
Excel,[vbcol=seagreen]
different[vbcol=seagreen]
news:7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...[vbcol=seagreen]
CSV.[vbcol=seagreen]
me[vbcol=seagreen]|||How do you create this DTS Package for exporting into a text file? I would
like to create and copy into a flat text file, but only see Bulk Insert that
will copy into SQL Tables. Could you provide some insight into how to expor
t
using DTS.
Thanks!
"Christian Hamel" wrote:

> Why not simply creating a DTS (or SSIS if you're on 2005) package that wil
l
> create that CSV file? Then it is a one click execute process.
>
> "sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com> a
> écrit dans le message de news:
> 7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...
>
>|||Try www.sqlscripter.com to export data to text/csv.
It's free.
"sorourke1@.hotmail.com" wrote:

> I am attempting to exports the results of a stored Procedure into a CSV. T
he
> table is a temp tabel named #FinalPrivate. I need to allow a user to downl
oad
> the file from a .NET Web Application. Is there someone that can point me i
n a
> direction?