How to add a group header and footer for a report

I have a report that is being called via stored proc, and i want to group by contract. when report gets generated i get multiple contracts info. but will be grouped/sorted by contract.

please how can i have a group header and also a group footer to show a summary of each contract information with some calculated fields in it.

i may get 100 records related to 10 contracts , 10 rows for each contract.

as soon as the first contract info is shown on the report it has to show a summary related to the first contract in the group footer, and then continue populating the second contract info and so on.

Please i am totally new to reporting and help would be appreciated. thank you all.

Here's what you can do:

Click anywhere on your table while in Layout.
Right click on the box next to your details row (it has 3 horizontal bold bars on it), and select 'Insert Group'.
On the 'General' tab, make sure 'Include group header' and 'Include group footer' are both checked.
In the 'Group on:' section, drop down the box and select your contract field. (Or you can type: =Fields!contract.Value)
On the 'Sorting' tab, again, pick or type your contract field as above.
On the 'Visibility' tab, make sure that the 'Visible' radio button is selected.
Hit 'OK' and now you can add your fields to the group header and footer.

Hope this helps.

Jarret

|||

Did this work for you Reddymade?

Jarret

How to add a foreign key constraint using the SQL server 2000 enterprise manager

Hi,

How to add a foreign key constraint using the SQL server 2000
enterprise manager?
Not by SQL.

thanks"Mark" <mchung_pc@.hotmail.com> wrote in message
news:1117734724.479577.321970@.o13g2000cwo.googlegr oups.com...
> Hi,
> How to add a foreign key constraint using the SQL server 2000
> enterprise manager?
> Not by SQL.
> thanks

I think you have to create a database diagram, then manage relationships in
that somehow, but I don't really know - EM is not a very good tool for
database design or programming. I would do it in Query Analyzer, because
then you have complete control over what you're doing and you also have a
script you can save, put under version control, run against multiple
databases etc.

Simon|||Many thanks.

regards|||Actually, this is NOT a very complicated procedure.
Assume table1.custID = table2.custID.

In EM, open table1 in design mode. Click the menu bar icon for "Manage
Relationships". Click the "Relationships" tab. Click the "New" button.

Now you can select keys/fields from the tables. I'm sure you can figure
it out from there.

ldh

Simon Hayes wrote:
> "Mark" <mchung_pc@.hotmail.com> wrote in message
> news:1117734724.479577.321970@.o13g2000cwo.googlegr oups.com...
>>Hi,
>>
>>How to add a foreign key constraint using the SQL server 2000
>>enterprise manager?
>>Not by SQL.
>>
>>thanks
>>
>
> I think you have to create a database diagram, then manage relationships in
> that somehow, but I don't really know - EM is not a very good tool for
> database design or programming. I would do it in Query Analyzer, because
> then you have complete control over what you're doing and you also have a
> script you can save, put under version control, run against multiple
> databases etc.
> Simon
>

how to add a field in existing table database

im facing some problem.i wantto add a single field with 15varchar but it
couldnt i have tried before it works fine but know it gives me message as
(Ansi_Padding Off) i have fetch the regarding purticular message i couldnt
able to findout ecaxt solution need your suggestion with appropriate solution.
Hi
Look up the ALTER TABLE command in Books online or at
http://msdn.microsoft.com/library/de...asp?frame=true
and use the version:
ALTER TABLE MyTable ADD mycolumn verchar(15)
You may have to use an UPDATE statement afterwards to update the existing
data.
John
"shoeb" <shoeb@.discussions.microsoft.com> wrote in message
news:C3E6D6B8-2B0D-4F0F-AA3C-1F4DA38FCC04@.microsoft.com...
> im facing some problem.i wantto add a single field with 15varchar but it
> couldnt i have tried before it works fine but know it gives me message as
> (Ansi_Padding Off) i have fetch the regarding purticular message i couldnt
> able to findout ecaxt solution need your suggestion with appropriate
> solution.

how to add a field in existing table database

im facing some problem.i wantto add a single field with 15varchar but it
couldnt i have tried before it works fine but know it gives me message as
(Ansi_Padding Off) i have fetch the regarding purticular message i couldnt
able to findout ecaxt solution need your suggestion with appropriate solutio
n.Hi
Look up the ALTER TABLE command in Books online or at
http://msdn.microsoft.com/library/d...asp?frame=true
and use the version:
ALTER TABLE MyTable ADD mycolumn verchar(15)
You may have to use an UPDATE statement afterwards to update the existing
data.
John
"shoeb" <shoeb@.discussions.microsoft.com> wrote in message
news:C3E6D6B8-2B0D-4F0F-AA3C-1F4DA38FCC04@.microsoft.com...
> im facing some problem.i wantto add a single field with 15varchar but it
> couldnt i have tried before it works fine but know it gives me message as
> (Ansi_Padding Off) i have fetch the regarding purticular message i couldnt
> able to findout ecaxt solution need your suggestion with appropriate
> solution.

how to add a field in existing table database

im facing some problem.i wantto add a single field with 15varchar but it
couldnt i have tried before it works fine but know it gives me message as
(Ansi_Padding Off) i have fetch the regarding purticular message i couldnt
able to findout ecaxt solution need your suggestion with appropriate solution.Hi
Look up the ALTER TABLE command in Books online or at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_3ied.asp?frame=true
and use the version:
ALTER TABLE MyTable ADD mycolumn verchar(15)
You may have to use an UPDATE statement afterwards to update the existing
data.
John
"shoeb" <shoeb@.discussions.microsoft.com> wrote in message
news:C3E6D6B8-2B0D-4F0F-AA3C-1F4DA38FCC04@.microsoft.com...
> im facing some problem.i wantto add a single field with 15varchar but it
> couldnt i have tried before it works fine but know it gives me message as
> (Ansi_Padding Off) i have fetch the regarding purticular message i couldnt
> able to findout ecaxt solution need your suggestion with appropriate
> solution.

How to add a field containing HTML to be properly rendered

I have a database notes field, which contains the text with some html
elements (fonts, styles, etc). If I add it to the report all html tags come
in plain text. Is there a way to render the field text as html? If not is
there a simple way to strip off all html tags?There is not a way to show as html. To strip off the html I believe there is
a framework function that you could use. Set the value of the textbox to an
expression like this:
= Code.StripHTML(Fields!Fieldname.value)
You would write the function StripHTML that would return the value with the
html striped.
This link shows how to do this using regular expressions.
http://weblogs.asp.net/rosherove/archive/2003/05/13/6963.aspx
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Alex Ch" <Alex Ch@.discussions.microsoft.com> wrote in message
news:ADDDE0F6-2DD0-49EC-875B-99694EB61BC0@.microsoft.com...
>I have a database notes field, which contains the text with some html
> elements (fonts, styles, etc). If I add it to the report all html tags
> come
> in plain text. Is there a way to render the field text as html? If not is
> there a simple way to strip off all html tags?

How to add a do loop in sql? Thanks a lot!

What I am trying to do is to get balances at each month-end from Jan to
Dec 2004. Now I am doing it by manually changing the date for each
month, but I want to do all the months at one time. Is there a way to
add something like a do loop to achieve that goal? Please see my query
below. Thanks so much!

declare @.month_date_b smalldatetime
--B month beginning date
declare @.month_date_e smalldatetime
--E month ending date

select @.month_date_b='9/1/2004'
select @.month_date_e='9/30/2004'

select a.person_id, a.fn_accno, a.fn_bal, b.mm_open
from fn_mm_fnbal as a
join fn_mm_list as b
on a.person_id=b.person_id
and b.mm_open < @.month_date_e
where a.bal_date between @.month_date_b and @.month_date_e
group by a.person_id, a.fn_accno, a.fn_bal, b.mm_open
order by a.fn_accno, a.fn_balRather than a loop, you can use a join to a set of dates. Or you could
just query the dates out of a Calendar table if you have one. Calendar
tables are very useful for this sort of thing:

SELECT C.cal_date, A.person_id, A.fn_accno, A.fn_bal, B.mm_open
FROM fn_mm_fnbal AS A
JOIN fn_mm_list AS B
ON A.person_id = B.person_id
JOIN
(SELECT CAST('20040101' AS DATETIME) UNION ALL
SELECT '20040201' UNION ALL
SELECT '20040301' UNION ALL
SELECT '20040401' UNION ALL
SELECT '20040501' UNION ALL
SELECT '20040601' UNION ALL
SELECT '20040701' UNION ALL
SELECT '20040801' UNION ALL
SELECT '20040901' UNION ALL
SELECT '20041001' UNION ALL
SELECT '20041101' UNION ALL
SELECT '20041201') AS C(cal_date)
ON A.bal_date >= C.cal_date
AND A.bal_date < DATEADD(M,1,C.cal_date)
AND B.mm_open < DATEADD(M,1,C.cal_date)
GROUP BY A.person_id, A.fn_accno, A.fn_bal, B.mm_open
ORDER BY A.fn_accno, A.fn_bal

--
David Portas
SQL Server MVP
--|||Thanks David! It is a great idea, I forgot to use my calendar table.

David Portas wrote:
> Rather than a loop, you can use a join to a set of dates. Or you
could
> just query the dates out of a Calendar table if you have one.
Calendar
> tables are very useful for this sort of thing:
> SELECT C.cal_date, A.person_id, A.fn_accno, A.fn_bal, B.mm_open
> FROM fn_mm_fnbal AS A
> JOIN fn_mm_list AS B
> ON A.person_id = B.person_id
> JOIN
> (SELECT CAST('20040101' AS DATETIME) UNION ALL
> SELECT '20040201' UNION ALL
> SELECT '20040301' UNION ALL
> SELECT '20040401' UNION ALL
> SELECT '20040501' UNION ALL
> SELECT '20040601' UNION ALL
> SELECT '20040701' UNION ALL
> SELECT '20040801' UNION ALL
> SELECT '20040901' UNION ALL
> SELECT '20041001' UNION ALL
> SELECT '20041101' UNION ALL
> SELECT '20041201') AS C(cal_date)
> ON A.bal_date >= C.cal_date
> AND A.bal_date < DATEADD(M,1,C.cal_date)
> AND B.mm_open < DATEADD(M,1,C.cal_date)
> GROUP BY A.person_id, A.fn_accno, A.fn_bal, B.mm_open
> ORDER BY A.fn_accno, A.fn_bal
> --
> David Portas
> SQL Server MVP
> --

How to add a default to existing column in T-SQL?

Please help me with T-SQL sytax for adding a default constraint to existing
column. Books Online do not help at all.
Thanks!
Leon Shargorodsky
Leon,
alter table <table_name>
add constraint <constraint_name> DEFAULT (<expression>) for <column_name>
but it will not affect existing records
Thanks
Oleg
Message posted via http://www.sqlmonster.com
|||Of course, you can fix existing rows with a simple update statement (
although it may take a while for a large table. ) .
The add default statement should run very quickly however.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:68C5CD27-98EF-42B0-B025-12881DCBAA42@.microsoft.com...
> Please help me with T-SQL sytax for adding a default constraint to
> existing
> column. Books Online do not help at all.
> Thanks!
> Leon Shargorodsky

How to add a default to existing column in T-SQL?

Please help me with T-SQL sytax for adding a default constraint to existing
column. Books Online do not help at all.
Thanks!
Leon ShargorodskyLeon,
alter table <table_name>
add constraint <constraint_name> DEFAULT (<expression> ) for <column_name>
but it will not affect existing records
Thanks
Oleg
Message posted via http://www.droptable.com|||Of course, you can fix existing rows with a simple update statement (
although it may take a while for a large table. ) .
The add default statement should run very quickly however.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:68C5CD27-98EF-42B0-B025-12881DCBAA42@.microsoft.com...
> Please help me with T-SQL sytax for adding a default constraint to
> existing
> column. Books Online do not help at all.
> Thanks!
> Leon Shargorodsky

How to add a default to existing column in T-SQL?

Please help me with T-SQL sytax for adding a default constraint to existing
column. Books Online do not help at all.
Thanks!
Leon ShargorodskyLeon,
alter table <table_name>
add constraint <constraint_name> DEFAULT (<expression>) for <column_name>
but it will not affect existing records
Thanks
Oleg
--
Message posted via http://www.sqlmonster.com|||Of course, you can fix existing rows with a simple update statement (
although it may take a while for a large table. ) .
The add default statement should run very quickly however.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:68C5CD27-98EF-42B0-B025-12881DCBAA42@.microsoft.com...
> Please help me with T-SQL sytax for adding a default constraint to
> existing
> column. Books Online do not help at all.
> Thanks!
> Leon Shargorodsky

How to add a Date selector in the report?

Hi All,
How could I add a date selector in the search criteria of datetime field in
the report? It should be a basic function of any report, right?
Thanks a lot.
BillWell, you'd think. It is a highly requested item. If you want to have a
calendar to pick dates you will have to create your own asp application and
then use either web services or URL integration to integrate your
application with RS.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bill" <huangxiaohua@.hotmail.com> wrote in message
news:edCDYBZuEHA.2536@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> How could I add a date selector in the search criteria of datetime field
> in
> the report? It should be a basic function of any report, right?
> Thanks a lot.
> Bill
>|||Thanks for your reply.
Where could I find any sample for this case?
Bill
"Bruce L-C [MVP]" wrote:
> Well, you'd think. It is a highly requested item. If you want to have a
> calendar to pick dates you will have to create your own asp application and
> then use either web services or URL integration to integrate your
> application with RS.
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Bill" <huangxiaohua@.hotmail.com> wrote in message
> news:edCDYBZuEHA.2536@.TK2MSFTNGP11.phx.gbl...
> > Hi All,
> >
> > How could I add a date selector in the search criteria of datetime field
> > in
> > the report? It should be a basic function of any report, right?
> >
> > Thanks a lot.
> > Bill
> >
> >
>
>|||Read up on URL integration in the BOL and make sure you know how to do that.
Once you know how to do that then you can write your own page to pass the
parameters. But note that you can end up with dealing with security issues
if the page is on another server. I think that if it is on the same server
then integrated security should still work transparently for you.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"How to add a Date selector in the reort" <How to add a Date selector in the
reort@.discussions.microsoft.com> wrote in message
news:B3B6F726-B18B-42D8-9E86-F30CAF4FF2E4@.microsoft.com...
> Thanks for your reply.
> Where could I find any sample for this case?
> Bill
>
> "Bruce L-C [MVP]" wrote:
> > Well, you'd think. It is a highly requested item. If you want to have a
> > calendar to pick dates you will have to create your own asp application
and
> > then use either web services or URL integration to integrate your
> > application with RS.
> >
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Bill" <huangxiaohua@.hotmail.com> wrote in message
> > news:edCDYBZuEHA.2536@.TK2MSFTNGP11.phx.gbl...
> > > Hi All,
> > >
> > > How could I add a date selector in the search criteria of datetime
field
> > > in
> > > the report? It should be a basic function of any report, right?
> > >
> > > Thanks a lot.
> > > Bill
> > >
> > >
> >
> >
> >

How to add a date range of drop down list for a SQL query?

Hi All,
I'm trying to create a report that let the user select the date range (such
as previous month, YTD, Oct, Nov,Dec, etc.). I know that I need to create a
SQL query like "select * from MyTable where selectdate between @.FromDate and
@.ToDate", but there are 2 parameters, but the date range drop down list just
have one parameter (For example, @.SelectDate), how could I pass the
@.SelectDate value to the @.FromDate and @.Todate?
I tried to search the MSDN but could not get the answer? Anyone could help?
Sample are appreciated if has.
Thanks a lot.
BillUse 2 comboboxes, one for the @.From another for the @.To date each with their
proper dataset:
- For @.From use something like "select mydates from dimTime ... "
- For @.To use something like "select mydates from dimTime where dates>=@.From
..."
(pseudo-code, you get the idea)
hth,
Tom
"Bill" wrote:
> Hi All,
> I'm trying to create a report that let the user select the date range (such
> as previous month, YTD, Oct, Nov,Dec, etc.). I know that I need to create a
> SQL query like "select * from MyTable where selectdate between @.FromDate and
> @.ToDate", but there are 2 parameters, but the date range drop down list just
> have one parameter (For example, @.SelectDate), how could I pass the
> @.SelectDate value to the @.FromDate and @.Todate?
> I tried to search the MSDN but could not get the answer? Anyone could help?
> Sample are appreciated if has.
> Thanks a lot.
> Bill
>

How to add a custom sum

Say I've got a report that lists users and their "Roles"
username1 userid1 userrole
username2 userid2 adminrole
at the bottom of the page, I'd like to print a litte summary of the
report with userroles: 1, adminroles 1.
I know how I'd achieve it with a whole other query, but is there a way I
can avoid it? I know this is probably a simple answer, I just want to
try and do things RIGHT the first time =)
Thanks
Weston WeemsI usually try to push most of this stuff back into the query, but if you are
trying to create a custom sum -- try
sum(iif(Fields!.Role.Value="userrole",1,0))
etc..
Hope this helps
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Weston Weems" <wweems@.Nospamcauseitsucks.gmail.com> wrote in message
news:%23s4rQNxqFHA.3080@.TK2MSFTNGP15.phx.gbl...
> Say I've got a report that lists users and their "Roles"
> username1 userid1 userrole
> username2 userid2 adminrole
> at the bottom of the page, I'd like to print a litte summary of the report
> with userroles: 1, adminroles 1.
> I know how I'd achieve it with a whole other query, but is there a way I
> can avoid it? I know this is probably a simple answer, I just want to try
> and do things RIGHT the first time =)
> Thanks
> Weston Weems

How to Add a column by getting value from a lookup table

Hello,
I have few columns at some level, and now I need to add a column from another database table based on the values from one of the existing columns.

It seems that Lookup Transformation doesnt support parameterized query.

Any clue ?

Thanks
Fahad

Are you sure you need a aprameterized query?

in the the lookup trasnform you can write a query; the join the columns of the lookup table/query with the columns available in the data pipeline.

Keep in mind, that you could have an aditional source component and then combine them using a Merge Join transform.

|||I agree with Rafael... By joining columns to the lookup table/query, you're effectively using "parameters". Unless you need your lookup query to be dynamic for each record coming down through the data flow.|||Yes, I know, but still I need to pass some values to the stored procedure to get a huge list, and then I will select from the list.

Am I using the wrong Transform Component ?|||Maybe. Have you looked at using the OLE DB Command transform?|||Yes, and it seems it doesnt return the cardinality return by query to its output pipeline.|||

Fahad,

I still don't see how a the lookup trasnform or the 2 SOurce components plus the Merge join cannot help you. Pherhaps if you provide an example you may be more likely to receive more suggestions.

How to add a col in the fixed position?

How to add a col in the fixed position?Position of columns in a table doesn't matter. If you want the columns in a particular order then you can specify it correctly in the SELECT list. And there is no way to add a column in a fixed position without dropping and recreating the table. Why do you need this? What is the problem in column getting at the end of the existing ones?

How to add a calender with a user parameter

Hello evry one,
Can any one help me how to add a calender as a list of
values,to an user parameter in reports. I am passing a parameter
called effective date. for that parameter i need calender as a list of
value. Please any one help on this..
thanks
BalajiThis is built into RS 2005. Just go to layout view, Report Menu ->Report
Parameters and change the data type to datetime. You will now have a
calendar. If you are on RS 2000 you are out of luck.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<rampaleee@.gmail.com> wrote in message
news:1193739093.504989.145020@.o38g2000hse.googlegroups.com...
> Hello evry one,
> Can any one help me how to add a calender as a list of
> values,to an user parameter in reports. I am passing a parameter
> called effective date. for that parameter i need calender as a list of
> value. Please any one help on this..
> thanks
> Balaji
>

How to add a calculated field in query out-put?

Dear All,

I wanted include a calculated field (column) in my query output. how do i go about that?

Please help me.

SELECT amtcol1, amtcol2, amtcol1 + amtcol2 as 'Total'

or

SELECT amtcol1, amtcol2, 'Total' = amtcol1 + amtcol2

How to Add a Calculated Column in Report Builder using a Model Based on a SSAS Cube

I am trying to add a calculated field / column in Report Builder when working with a Report Model built from anAnalysis Services Cube. I can create the calculated Field/Columns, but I get an error whenever I try to use it in a report.

Is there a way to create a report builder calculated column on report models built from a SSAS cube? Is this supported?

Thanks,

Some functions are not supported for calculated fields when running against an SSAS cube, in particular the aggregate functions like Sum, Count, Average, etc. The error message should provide some details on this.

Hope that helps!

|||

The support for calculated fields in an SSAS model is better than Bob lets on. Most functions work. The trick is that you need to install Excel 2003 on the Analysis Services server. (Not sure if other versions like Excel 2007 work, but I know Excel 2003 does the trick.)

Report Builder builds MDX that use Excel functions, and for those functions to succeed, Excel has to be installed on the server.

If you agree with me that this is a terrible architecture, vote for this issue:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124864

|||Are there any online documentation about this? Our IT policy is not to install office or office application on SQL Server boxes.|||

Common policy. Vote for that issue at connect.microsoft.com and maybe MS will change this in Katmai.

As for documentation:

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

And documentation of the excel functions that are supported. (Don't know if there's a 2005 version of this page, but they're the same):

http://msdn2.microsoft.com/en-us/library/aa178231(SQL.80).aspx

I suppose one workaround is to code the Excel functions yourself and register them as specified here:

http://geekswithblogs.net/darrengosbell/archive/2006/12/13/100998.aspx

If you have interest in going that route, let me know because there are some gotchas.

How to Add a Calculated Column in Report Builder using a Model Based on a SSAS Cube

I am trying to add a calculated field / column in Report Builder when working with a Report Model built from anAnalysis Services Cube. I can create the calculated Field/Columns, but I get an error whenever I try to use it in a report.

Is there a way to create a report builder calculated column on report models built from a SSAS cube? Is this supported?

Thanks,

Some functions are not supported for calculated fields when running against an SSAS cube, in particular the aggregate functions like Sum, Count, Average, etc. The error message should provide some details on this.

Hope that helps!

|||

The support for calculated fields in an SSAS model is better than Bob lets on. Most functions work. The trick is that you need to install Excel 2003 on the Analysis Services server. (Not sure if other versions like Excel 2007 work, but I know Excel 2003 does the trick.)

Report Builder builds MDX that use Excel functions, and for those functions to succeed, Excel has to be installed on the server.

If you agree with me that this is a terrible architecture, vote for this issue:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124864

|||Are there any online documentation about this? Our IT policy is not to install office or office application on SQL Server boxes.|||

Common policy. Vote for that issue at connect.microsoft.com and maybe MS will change this in Katmai.

As for documentation:

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

And documentation of the excel functions that are supported. (Don't know if there's a 2005 version of this page, but they're the same):

http://msdn2.microsoft.com/en-us/library/aa178231(SQL.80).aspx

I suppose one workaround is to code the Excel functions yourself and register them as specified here:

http://geekswithblogs.net/darrengosbell/archive/2006/12/13/100998.aspx

If you have interest in going that route, let me know because there are some gotchas.

How to add a calcualted field in Matrix?

Hello.. I would like to know how this can be done in RS (SQL2000)...

In the dataset I have... sales, company, and year. What I want to display is such...

2005 2006 Growth

companyA $1,000,000 $500,000 100%

companyB $50,000 $100,000 -50%

How do I add a column in a Matrix to calculate the growth based on the sales? Thanks!

-Joseph

you would have to add growth to your dataset as a calculation in of the years. So for MDX you would add a calculated member to the time dimension and calculate growth in that.

How to add 3 days to the existing date

Bottom is my table structure. please some one can give me the sql code to add 3 days to the existing date.

SemesterID int
SemesterLongID varchar
SemesterIdentifier char
SemesterIdentifierName varchar
SemesterName varchar
SemesterStart datetime
SemesterEnd datetime
SemesterNameAlt varchar
EnrollmentDeadline datetime
RegistrationStart datetime
RegistrationEnd datetime
LengthInWeeks varchar
BulletinStartDate datetime
BulletinEndDate datetime
ClassroomStartDate datetime
ClassroomEndDate datetime
FacClassStartDate datetime
FacClassEndDate datetime
PlanningStart datetime
PlanningEnd datetime
GoToTeach datetime
TOButAppears datetime
TOButDisappears datetime
Signature datetime
TOButDue datetime
BookListDue datetime
ProfAccess datetime
ExamDue datetime
SCGDue datetime
GradesDue datetime
ExtGradesDue datetime
TOExt datetime
SessionPlanning datetime
TODue datetime
SCGStart datetime
TOExpire datetime
SRPTOCDue datetime
SRPCopyDue datetime
SRPCCCFormDue datetime
TOID int
SemesterIdentifierID int
Code varchar
TitleIVDescription varchar
CreatedBy varchar
ModifiedBy varchar
DateCreated datetime
DateModified datetime

Check out books online for "Datediff" and "Dateadd" functions.

how to add 2 memo fields

I have memo1 field. It has some data. I want to add another memo field
which is memo2 at the end of the memo1 after leaving couple of spaces.
I tried following but it does not work.
update test
set memo1 = memo1+' '+memo2
Can someone please help me to merge/add 2 memo fields ?
ThanksWhat are the data types for your memo columns? When you say
it doesn't work, what error do you get or what results do
you get that seem incorrect.
You should also post the DDL for your table as that helps in
addressing issues such as these.
-Sue
On Wed, 09 Mar 2005 16:41:40 GMT, "Mac" <mac@.hotmail.com>
wrote:

>I have memo1 field. It has some data. I want to add another memo field
>which is memo2 at the end of the memo1 after leaving couple of spaces.
>I tried following but it does not work.
>update test
>set memo1 = memo1+' '+memo2
>
>Can someone please help me to merge/add 2 memo fields ?
>
>Thanks
>

how to add 2 memo fields

I have memo1 field. It has some data. I want to add another memo field
which is memo2 at the end of the memo1 after leaving couple of spaces.
I tried following but it does not work.
update test
set memo1 = memo1+' '+memo2
Can someone please help me to merge/add 2 memo fields ?
Thanks
What are the data types for your memo columns? When you say
it doesn't work, what error do you get or what results do
you get that seem incorrect.
You should also post the DDL for your table as that helps in
addressing issues such as these.
-Sue
On Wed, 09 Mar 2005 16:41:40 GMT, "Mac" <mac@.hotmail.com>
wrote:

>I have memo1 field. It has some data. I want to add another memo field
>which is memo2 at the end of the memo1 after leaving couple of spaces.
>I tried following but it does not work.
>update test
>set memo1 = memo1+' '+memo2
>
>Can someone please help me to merge/add 2 memo fields ?
>
>Thanks
>

how to add 2 memo fields

I have memo1 field. It has some data. I want to add another memo field
which is memo2 at the end of the memo1 after leaving couple of spaces.
I tried following but it does not work.
update test
set memo1 = memo1+' '+memo2
Can someone please help me to merge/add 2 memo fields ?
ThanksWhat are the data types for your memo columns? When you say
it doesn't work, what error do you get or what results do
you get that seem incorrect.
You should also post the DDL for your table as that helps in
addressing issues such as these.
-Sue
On Wed, 09 Mar 2005 16:41:40 GMT, "Mac" <mac@.hotmail.com>
wrote:
>I have memo1 field. It has some data. I want to add another memo field
>which is memo2 at the end of the memo1 after leaving couple of spaces.
>I tried following but it does not work.
>update test
>set memo1 = memo1+' '+memo2
>
>Can someone please help me to merge/add 2 memo fields ?
>
>Thanks
>

How to add 2 columns together

SQL2K
I have 2 columns, one is numeric and 2nd is TEXT or memo column.
I have been trying for the last few days with no success.
Here is the statement that I have been using:
UPDATE MYTABLE
SET MYMEMO = STR(PERCENTAGE,7)+' '+MYMEMO
When I run it, I get data type error.
I would appreciate if someone please help me here.
Thx
Hi,
You need to convert the TEXT data type to Varchar for using normal updates.
UPDATE MYTABLE
SET MYMEMO = ltrim(rtrim(convert(char,PERCENTAGE,7)))+'
'+ltrim(rtrim(convert(varchar(8000),MYMEMO)))
Thanks
Hari
SQL Server MVP
"Mac" wrote:

> SQL2K
> --
>
> I have 2 columns, one is numeric and 2nd is TEXT or memo column.
> I have been trying for the last few days with no success.
> Here is the statement that I have been using:
>
> UPDATE MYTABLE
> SET MYMEMO = STR(PERCENTAGE,7)+' '+MYMEMO
> When I run it, I get data type error.
> I would appreciate if someone please help me here.
> Thx
>
>
|||Hi,
Feedback below..
SELECT MAX(DATALENGTH(mymemo)) FROM mytable
-- If the above is < 8000
UPDATE MYTABLE
SET MYMEMO = RTRIM(LTRIM(STR(PERCENTAGE,7)))+' '+
CONVERT(VARCHAR(8000), MYMEMO)
GO
-- If it isn't < 8000 lookup UPDATETEXT clause in SQL Server BOL
Greg

How to add 2 columns together

SQL2K
--
I have 2 columns, one is numeric and 2nd is TEXT or memo column.
I have been trying for the last few days with no success.
Here is the statement that I have been using:
UPDATE MYTABLE
SET MYMEMO = STR(PERCENTAGE,7)+' '+MYMEMO
When I run it, I get data type error.
I would appreciate if someone please help me here.
ThxHi,
You need to convert the TEXT data type to Varchar for using normal updates.
UPDATE MYTABLE
SET MYMEMO = ltrim(rtrim(convert(char,PERCENTAGE,7)))+'
'+ltrim(rtrim(convert(varchar(8000),MYMEMO)))
Thanks
Hari
SQL Server MVP
"Mac" wrote:
> SQL2K
> --
>
> I have 2 columns, one is numeric and 2nd is TEXT or memo column.
> I have been trying for the last few days with no success.
> Here is the statement that I have been using:
>
> UPDATE MYTABLE
> SET MYMEMO = STR(PERCENTAGE,7)+' '+MYMEMO
> When I run it, I get data type error.
> I would appreciate if someone please help me here.
> Thx
>
>|||Hi,
Feedback below..
SELECT MAX(DATALENGTH(mymemo)) FROM mytable
-- If the above is < 8000
UPDATE MYTABLE
SET MYMEMO = RTRIM(LTRIM(STR(PERCENTAGE,7)))+' '+
CONVERT(VARCHAR(8000), MYMEMO)
GO
-- If it isn't < 8000 lookup UPDATETEXT clause in SQL Server BOL
Greg

How to add 2 columns together

SQL2K
---

I have 2 columns, one is numeric and 2nd is TEXT or memo column.

I have been trying for the last few days with no success.

Here is the statement that I have been using:

UPDATE MYTABLE
SET MYMEMO = STR(PERCENTAGE,7)+' '+MYMEMO

When I run it, I get data type error.

I would appreciate if someone please help me here.

ThxHi,

Feedback below..

SELECT MAX(DATALENGTH(mymemo)) FROM mytable
-- If the above is < 8000
UPDATE MYTABLE
SET MYMEMO = RTRIM(LTRIM(STR(PERCENTAGE,7)))+' '+
CONVERT(VARCHAR(8000), MYMEMO)
GO
-- If it isn't < 8000 lookup UPDATETEXT clause in SQL Server BOL

Greg

How to add 2 columns together

SQL2K
--
I have 2 columns, one is numeric and 2nd is TEXT or memo column.
I have been trying for the last few days with no success.
Here is the statement that I have been using:
UPDATE MYTABLE
SET MYMEMO = STR(PERCENTAGE,7)+' '+MYMEMO
When I run it, I get data type error.
I would appreciate if someone please help me here.
ThxHi,
You need to convert the TEXT data type to Varchar for using normal updates.
UPDATE MYTABLE
SET MYMEMO = ltrim(rtrim(convert(char,PERCENTAGE,7)))
+'
'+ltrim(rtrim(convert(varchar(8000),MYME
MO)))
Thanks
Hari
SQL Server MVP
"Mac" wrote:

> SQL2K
> --
>
> I have 2 columns, one is numeric and 2nd is TEXT or memo column.
> I have been trying for the last few days with no success.
> Here is the statement that I have been using:
>
> UPDATE MYTABLE
> SET MYMEMO = STR(PERCENTAGE,7)+' '+MYMEMO
> When I run it, I get data type error.
> I would appreciate if someone please help me here.
> Thx
>
>|||Hi,
Feedback below..
SELECT MAX(DATALENGTH(mymemo)) FROM mytable
-- If the above is < 8000
UPDATE MYTABLE
SET MYMEMO = RTRIM(LTRIM(STR(PERCENTAGE,7)))+' '+
CONVERT(VARCHAR(8000), MYMEMO)
GO
-- If it isn't < 8000 lookup UPDATETEXT clause in SQL Server BOL
Greg

How to add 12 months to the CurrentDate

Ciao, i'm using Crystal Report 6 and I have a formula field which must show the currentdate + 12 months, i.e.: today is 16/11/2004, i need 16/10/2004.
Could you help me?
I'm trying the data functions but i'm not able to solve my problem :-\Solution:
CurrentDate + 365

Ops... :-p
Ciao :-)|||above solution works except for Leap years.....
alternative is :
formula = dateAdd("m", 12, CurrentDate)

you can do add and subtract arithmetic on dates using DateAdd.
1st paremeter = either "d", "m", or "y",
2nd parameter is amount you want to inbcrement or decrement by,
last parameter is date you want to use as basis of calculations

Dave

how to add 1 to the field

when i work with Aceess Db i have SQL statment that cout the visitor:

update Tbl1 set fldvisit = fldvisit +1 where fld2='abc'

when i move to SQL Server it's not working
what to do?Hi

This should be ok! What is the error/problem?

Posting DDL and example data http://www.aspfaq.com/etiquett*e.asp?id=5006
will help others to re-create the problem.

John

"yuval" <yuvalbra@.gmail.com> wrote in message
news:1115492012.471848.288840@.g14g2000cwa.googlegr oups.com...
> when i work with Aceess Db i have SQL statment that cout the visitor:
>
> update Tbl1 set fldvisit = fldvisit +1 where fld2='abc'
>
>
> when i move to SQL Server it's not working
> what to do?|||What does "not working" mean. Tell us the error message if any or post some
example code to reproduce the problem (CREATE, INSERT, UPDATE statements).

--
David Portas
SQL Server MVP
--

How to add (pick and choose) items from one table to another

We have two tables (one has DTS import of external spreadsheet - the other is the online table)

The scenario is that I need to add items (from the first table) to the second table, when they don't exist. However, it's harder than that - the second table, in some fields has integers (ID field)), referring to another table (let's call it the 3rd table), but the first table has text (which is in the description, in the third table).

I have to run through the first table, row by row, checking the part number if it already exists in the second table - nothing happens, and it goes to the next line. In each line, if the part number does NOT exist, I need to insert the data in that row. BUT - remember that third table, for the item(s) in the first table with text, I need to dynamically query the third table, to get the ID, in order to complete the insert correctly (Select ID from Third Table whereDescription=@.FirstTableFieldText)

I've never done anything like this, and I have no idea how to put this together - especially if it's all in one Stored Procedure. I need some help here on recommended ways to do it (and how).... Even the base structure (pseudo code- whatever) would help

I suppose that you can do it in one insert statement

Insert into SecondTable(partno,ID)

Select partno,thirdtable.ID from firsttable

LEFT JOIN thirdtable

ON thirdtable.description = firsttabledescription

where partno not in (select partno from secondtable)

I do not know exactly your database schema but in this TSQL statement you select records from first table and connect each record to its description ID ( join) and in where statement you select only records which has partno which does not exists in second table (destination).

Is it what you need?

Thanks

JPazgier

how to active a push subscriptin?

Hi all:
How to active a push subscription after created push subscription?
Cheers
nick
Drop the push subscription. At the publisher or distributor if you have a
remote distributor go to Tools, Replication, Configure Publishing,
Distributor, Subscribers. Enable your subscriber. The connect to the
subscriber, enable replication. After this go to tools, replication, create
pull subscription. Follow the prompts.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Nick" <fsheng@.ebreathe.co.nz> wrote in message
news:OnRBILjCFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Hi all:
> How to active a push subscription after created push subscription?
>
> Cheers
> nick
>
|||Thanks Hilary
can i ask one more question about push subscription?
i got two DB in my SQL server, call A, B for exampl. A is a publication DB,
B is push subscription DB, so whenever data been changed in B database that
will be updated in A database,
is that good way to manage in this situation?
Cheers
Nick
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:#Mme7OjCFHA.4036@.TK2MSFTNGP15.phx.gbl...
> Drop the push subscription. At the publisher or distributor if you have a
> remote distributor go to Tools, Replication, Configure Publishing,
> Distributor, Subscribers. Enable your subscriber. The connect to the
> subscriber, enable replication. After this go to tools, replication,
create
> pull subscription. Follow the prompts.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Nick" <fsheng@.ebreathe.co.nz> wrote in message
> news:OnRBILjCFHA.2876@.TK2MSFTNGP12.phx.gbl...
>
|||No, its not. Push and pull do not refer to the direction of data movement.
Rather they refer to whether the changes are pushed from the publisher to
the subscriber, or whether the changes are pulled from the publisher to the
subscriber.
This is only valid for transactional or snapshot replication.
If you have snapshot or transactional replication with updatable
subscribers, changes which occur on the subscriber can be pushed from the
subscriber to the publisher. With merge, if you have a push subscription,
the changes are merged at the publisher and pushed to the subscriber. If you
have a pull merge subscription, the changes are merged at the publisher and
pulled to the subscriber.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Nick" <fsheng@.ebreathe.co.nz> wrote in message
news:u4qlYujCFHA.1396@.TK2MSFTNGP14.phx.gbl...
> Thanks Hilary
> can i ask one more question about push subscription?
> i got two DB in my SQL server, call A, B for exampl. A is a publication
DB,
> B is push subscription DB, so whenever data been changed in B database
that[vbcol=seagreen]
> will be updated in A database,
> is that good way to manage in this situation?
> Cheers
> Nick
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:#Mme7OjCFHA.4036@.TK2MSFTNGP15.phx.gbl...
a
> create
>
|||Hi Hilary:
Do you mean only transactional or snapshot replication can handle this case,
but not merge replication?
Cheers
nick
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eTUf#0mCFHA.3376@.TK2MSFTNGP12.phx.gbl...
> No, its not. Push and pull do not refer to the direction of data movement.
> Rather they refer to whether the changes are pushed from the publisher to
> the subscriber, or whether the changes are pulled from the publisher to
the
> subscriber.
> This is only valid for transactional or snapshot replication.
> If you have snapshot or transactional replication with updatable
> subscribers, changes which occur on the subscriber can be pushed from the
> subscriber to the publisher. With merge, if you have a push subscription,
> the changes are merged at the publisher and pushed to the subscriber. If
you
> have a pull merge subscription, the changes are merged at the publisher
and[vbcol=seagreen]
> pulled to the subscriber.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Nick" <fsheng@.ebreathe.co.nz> wrote in message
> news:u4qlYujCFHA.1396@.TK2MSFTNGP14.phx.gbl...
> DB,
> that
have
> a
>

How to activate the MSDTC service?

Hello,

As newbie in server country I try a course example to test a distributed transaction accessing both SQL Server 2005 and Sql Server 2005 Express.

But I get an error message saying:
>MSDTC on server is not available

I Internetted somewhere that MSDTC stands for TransactionManager service.
If I look in the 'Services' list I do not see something like MSDTC, so I think it is not installed or started.

How do I install or activate the MSDTC service?

Thanks in advange,
Henk

Hi,

What OS are you using? I have Win2K3 and I do see "Distributed Transaction Coordinator" as a service.

Thanks

Waseem

|||

In the services list it should be named "Distributed Transaction Coordinator".

You should check ControlPanel->AdministrativeTools->Component Services->Computers->My Computer->Properties.

Have a look at http://msdn2.microsoft.com/en-us/library/ms684146.aspx for a general documentation.

|||

Thanks both of you!

I'm using WinXP Prof and I see a 'Distributed Transaction Coordinator' service thats waiting to be started....
After starting it, my program runs fine!

regards,

Henk

How to activate SQL server 2005

I want to know if there is a way to activate an evaluation version of SQL Server 2005 without re-installing the product completely. Running evaluation in a production environment while waiting on my purchase of Standard edition to be completed. All I have is my Microsoft Open License Order Confirmation.

Standard Edition does not allow 'activation' to upgrade from evaluation product to licensed product.

You will need to install the Standard Edition.

I suggest a side-by-side installation, moving the database files over to the Standard Edition server when installation is complete

Then when you have completed the transition, uninstall the eval version.

However, if you wish to keep the default Instance name the same as the computer name, then you will probably need to remove the eval version first.

how to activate product

hi,

I am realy sorry that I am putting this question here since I don't know where to post this question.

We have purchased

Windows server CAL 2003 English OLP NL user CAL

with 5 user license

We got the Microsoft open license agreement after a month or so.

Now I want to know how to proceed with this license.

Since I am newbie to this kindly guide me in this regard.

thanks

Wow, you're really asking in the wrong location - this is a forum for Setup of SQL Server. Still, a quick MSN search provided this link which talks about WS03 product activation:

http://technet2.microsoft.com/WindowsServer/en/Library/db506be2-34f5-4e68-b438-3f3ede902c7a1033.mspx?mfr=true

Good luck!

Paul

How to activate Mixed Mode (SECURITYMODE=SQL) after installation

Hi,
is there a way to active Mixed Mode Authentication without reinstalling
MSDE?
Thanks,
mm
As in Microsoft Knowledge Base Article - 285097
To set this key to mixed-mode, follow these steps:
1.. Stop SQL Server and all related services, such as SQLAgent, from the
Services control panel.
2.. Click Start, click Run, type regedt32, and then click OK.
3.. Find the HKEY_LOCAL_MACHINE window on the local computer.
4.. Navigate to the following registry key for the default instance:
HKEY_LOCAL_MACHINE\Software\Microsoft\MSSqlserver\ MSSqlServer
5.. Navigate to the following registry key for a named instance:
HKLM\Software\Microsoft\Microsoft SQL Server\Instance
Name\MSSQLServer\LoginMode
6.. On the right-hand pane, find the value LoginMode and double-click it.
7.. In the DWORD Editor window, set the value to 0 or 2. Also, make sure
that the Hex radio button is selected.
8.. Click OK.
Regards,
Jeyakumar
"Michael Mller" <alligator081067@.yahoo.de> wrote in message
news:c4ggt0$2fca9f$1@.ID-93095.news.uni-berlin.de...
> Hi,
> is there a way to active Mixed Mode Authentication without reinstalling
> MSDE?
> Thanks,
> mm
>
|||hi Michael,
"Michael Mller" <alligator081067@.yahoo.de> ha scritto nel messaggio
news:c4ggt0$2fca9f$1@.ID-93095.news.uni-berlin.de...
> Hi,
> is there a way to active Mixed Mode Authentication without reinstalling
> MSDE?
>
please have a look at
http://support.microsoft.com/default...b;en-us;285097 , which
provides info on how to hack the window registry to change authentication
mode..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea & Jeyakumar,

> please have a look at
> http://support.microsoft.com/default...b;en-us;285097 , which
> provides info on how to hack the window registry to change authentication
> mode..
Thanks for your help. I've found that this solution helped other people, but
it sounds mad, it doesnt work for my MSDE SP3 installation.
I have changed the registry key as described, stopped and restarted the
MSSQLSERVER service, but i still can't connect with SQL Server
authentication.
I even have reinstalled MSDE with the SECURITYMODE=SQL option, but no
success. :-(
If I use MS Products like ASP.NET WebMatrix or Access with
windows-intregrated authorization, no problem.
But when I explicitly try to connect with SQL server authentication, the
following message appears:
Error -2147467259
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server existiert nicht oder
Zugriff verweigert.(SQL connection does not exist or access refused.)
Provider=sqloledb;Data Source=edvserver;Network Library=dbmssocn;User
Id=sa;Password=;
Another tool with the same access problem is myLittleAdmin
(http://intranetserver/mla_sql_lite/default.asp).
I'm sure that the sa password is right.
This error appears in WebMatrix too, if i try to use SQL Authentication.
Any further idea?
Bye,
mm

How to activate licenses

I've just installed Workgroup Edition for the first time. I have copies on each of two servers, each ordered with five CALs.

My problem is that I don't know how to assign the licenses. I can't find anything in the documentation about it, nothing in the tools menus, and no reference to SQL Server in the Licensing application. The latter really looks like the right place, so if I had to guess, I'd think I've installed something incorrectly. Shouldn't SQL Server appear there as a product?

Of course, I'd also think this would be documented somewhere, but then it's probably one of those things that any idiot would know, so there's no need to explain it.

Once I get past that problem, I'll be setting up eight workstations. I understand from Microsoft Sales that since I've purchased a total of ten CALs, then all eight workstations will be able to access either server. Do I have to do anything special to make that work? Currently, there's no domain. The servers are just members of a workgroup.

Thanks,

Dick

There is no license 'manager', there is no 'place' to record the licenses. It is an 'honor' system. (Except for Small Business Server -which does have a license manager built it.)

Keep the license information on file in case your organization ever gets hit with a software license compliance audit.

Yes, since you now have 10 CALs, up to 10 client devices can connect to your servers.

In the future, for definitive answers to licensing questions, contact the friendly folks at:

Licensing –VL Contact
(800) 426-9400

How to achieve the last Time-selection while iterating in the time-dimension itself ?

Hello
I try to explain my problem with an example:

Page-filter Fields:
&[Year 2003].&[Jan].&[3],
&[Year 2003].&[Jan].&[4],
&[Year 2003].&[Jan].&[5]
(=Multiple Item Selections)

Range in days = 3 days

Behind the scene:

[?] [Mutation] Year 2003 Q1 Jan 1 2 3 3 (=DayRange-1+1) 5 4 2 (=DayRange-2+1) -2 5 1 (=DayRange-3+1) 3 6

I want to achieve this kind of calculation behind the scene:
(3*5)+(2*-2)+(1*3) = 14

[Measures].[MyCalc]=

Sum(
Head(Existing [Date].[Days], 1).Item(0)
:Tail(Existing [Date].[Days], 1).Item(0)
,
[Measures].[Mutation]*[Measures].[?]
)

This is what my pivottable must show me:

[MyCalc] 14

Can someone help me out with [Measures].[?] or is there an other way ?

Kind regards,
Geert.

Hi Geert,

Here's a sample Adventure Works query, using [Measures].[Order Quantity], with individual day values of 7, 8, 7:

>>

With Member [Measures].[WeightedOrders] as

Sum((Existing [Date].[Calendar].[Date].Members) as DayRange,

[Measures].[Order Quantity] *

(DayRange.Count + 1

- Rank([Date].[Calendar].CurrentMember, DayRange)))

select {[Measures].[Order Quantity],

[Measures].[WeightedOrders]} on 0

from [Adventure Works]

where {[Date].[Calendar].[Date].&[552]:

[Date].[Calendar].[Date].&[554]}

Order Quantity WeightedOrders
22 44

>>

|||

This code was very helpful

(DayRange.Count + 1 - Rank([Date].[Calendar].CurrentMember, DayRange)

Thank you very very much ,

Geert.

How to achieve rowid of Oracle in SQL Server?

Any suggessions?You need to post more description about what you need to achieve|||

If you have a suitable single column primary key in a table then you can use it instead of rowid. Otherwise consider adding a column to the table with the IDENTITY property.

This is from Books Online

IDENTITY

Indicates that the new column is an identity column. When a new row is added to the table, the Database Engine provides a unique, incremental value for the column. Identity columns are typically used with PRIMARY KEY constraints to serve as the unique row identifier for the table. The IDENTITY property can be assigned to tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) columns. Only one identity column can be created per table. Bound defaults and DEFAULT constraints cannot be used with an identity column. Both the seed and increment or neither must be specified. If neither is specified, the default is (1,1).

How to achieve rowid of Oracle in SQL Server

Hi:
How can I get the same function of rowid of Oracle in SQL Server.
thanks
Hi:
How can I get the same function of rowid of Oracle in SQL Server.

thanks
Link|||Just use MSSQL 2005. ROW_NUMBER() function (see ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/82fa9016-77db-4b42-b4c8-df6095b81906.htm) is presented there.

How to achieve partition parallelism?

Hi,

Is it possible to achieve partition parallelism in SSIS? What I am asking is, In DataStage, if I load some data like 'data reader -> trans1 -> trans2 -> destination' (and assume that I have 4 nodes configured), the tool divides the data into 4 different datasets and executes the package as 4 instances. This way the data load is very fast. Is it possible in SSIS?

Of course we can divide the dataset and load them thru multiple instances? But then dividing the dataset will differ for every load and so we need to modify the package all the time. Even if we divide the dataset, I am not sure 4 instances will run in 4 different nodes or in a same node? So anybody has any idea about it?

Thanks.

You can't do that like you can in Data Stage/Ab Initio etc... unfortunately, no. The only way is, like you describe, mimicing parallellism by breaking teh data up explicitly and executing (say) 4 different data-flows.

I wouldn't be surprised to see it in an upcoming version though. I know its on their radar.

-Jamie

|||Thanks for your clarification Jamie. If I mimic parallelism, how can I ensure that each instance is executed in different node? Is it possible to do it?|||

Have a look at this entry in BOL: http://msdn2.microsoft.com/en-us/ms345184.aspx

One approach that can be successful is to use a conditional split to divide a data load to (say) four remote raw files, then use SQL Server Agent to execute jobs on the four remote servers to read the raw files.

The performance is impacted by the need to write and read the data from the raw files on disk, which may negate any benefit of the parallelism. However, net benefits depend on the size of the load, nature of the data, speed of the network, and speed of read/write from disk.

Donald

How to achieve "On Error Resume Next" login in SSIS For Loop

Hello All,

I am developing a package using SSIS which needs to do the following.

1. Read all flat file from a folder. I am doing this using For Loop task. I know the total number of files in that folder hence I am setting the loop counter = file count.

2. The next step is to import the data from flat file to SQL server destination table using data flow task.

3. Upon successful completion of data flow task there are some other tasks like SQL to do some checks/validation on the data, export it to another tables.

Upon successful completion of step 3 the iteration goes to next file.

I want to achieve the following

IF step 2 has error (for example corrupt file or incomplete data), I want to fail data transfer completely, skip step 3, and go to step 1 for next available file and do rest.

How do I do this in SSIS?

Thanks for your help.

SGK

Hey SGK,

trying setting the maxerror count in properties to = 0

works for me, so it will keep going,

default is 1 so if an error occours it fails the whole thing

How to achieve "On Error Resume Next" login in SSIS For Loop

Hello All,

I am developing a package using SSIS which needs to do the following.

1. Read all flat file from a folder. I am doing this using For Loop task. I know the total number of files in that folder hence I am setting the loop counter = file count.

2. The next step is to import the data from flat file to SQL server destination table using data flow task.

3. Upon successful completion of data flow task there are some other tasks like SQL to do some checks/validation on the data, export it to another tables.

Upon successful completion of step 3 the iteration goes to next file.

I want to achieve the following

IF step 2 has error (for example corrupt file or incomplete data), I want to fail data transfer completely, skip step 3, and go to step 1 for next available file and do rest.

How do I do this in SSIS?

Thanks for your help.

SGK

Hey SGK,

trying setting the maxerror count in properties to = 0

works for me, so it will keep going,

default is 1 so if an error occours it fails the whole thing

How to acesses sql server 2000 from remote pc ?

hi masters,

i am using Sql server 2000 (MSDE) , and i want to acesses database from remote machin, how it is possible any one tell me pls?

You need to enable NP and/or TCP protocols on the server side using SQL Server Server Utility. You need restart MSDE after the configuration change. If you have SQL Server 2005 installed, you need to run SQL Browser as well. In addition, make sure your firewall does not block the TCP ports if you have firewall installed. Check you error log to find out which TCP port your MSDE is listening on. For Sql Browser, you need put UDP port 1434 into exception.

Thanks,

|||

Thanks for reply,

but still unable to connect the remote pc,

i have working with only sqlserver 2000

and also

enable NP and/or TCP protocols on the server side using SQL Server Server Utility

what can i do next?

|||

Are the client and server on the same network?
Can you ping your server from remote client machine?
What's the tcp port sql server listen on? You can get this from errorlog. Try "telnet remotemachine yoursqlport", can you connect? If you have firewall, please put both your sqlport(tcp) and UDP 1434 into exception.

|||

thanks for reply

1. Yes the client and server on the same network.

2. Yes ping is replying

but how to do--?

What's the tcp port sql server listen on? You can get this from errorlog. Try "telnet remotemachine yoursqlport", can you connect? If you have firewall, please put both your sqlport(tcp) and UDP 1434 into exception.

will you guide me?

|||Please refer to: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=362498&SiteID=1

You can also check books-on-line.

how to accumulate values in different columns ?

Hi,

I have a table similar to this :

id | name | res_id
--+---+---
1 | foo | 1
2 | bar | 1
3 | foo | 1
4 | test | 2
5 | wheyho | 3
6 | ding | 2
7 | dong | 3
8 | foo | 2
9 | foo | 3
10 | bar | 1
11 | bar | 2
12 | bar | 2
13 | wheyho | 3

Now, I am looking for a SELECT statement that counts the result_id depending on its value ( 1,2 or 3 in this example ) and grouped by name. To make it a bit more clear I am looking for this result (with result_id 1 evaluates to 'pass', 2 to 'fail' and 3 to 'unknown' ):

name | pass | fail | unknown
---+--+--+----
foo | 2 | 1 | 1
bar | 2 | 2 | 0
test | 0 | 1 | 0
wheyho | 0 | 0 | 2
ding | 0 | 1 | 0
dong | 0 | 0 | 1

I can get single results when doing this staements :

SELECT name, count(result_id) AS pass FROM t1 GROUP BY name, res_id HAVING res_id = 1;

name | pass
--+--
foo | 2
bar | 2

SELECT name, count(result_id) AS fail FROM t1 GROUP BY name, res_id HAVING res_id = 2;

name | fail
--+--
foo | 1
bar | 2
ding | 1
test | 1

SELECT name, count(result_id) AS unknown FROM t1 GROUP BY name, res_id HAVING res_id = 3;

name | unknown
---+---
foo | 1
wheyho | 2
dong | 1

Is there any way to bring these single results together with one SELECT statements ?With SUMs and CASEs:

SELECT
name,
SUM(CASE WHEN res_id = 1 THEN 1 ELSE 0 END) as pass
SUM(CASE WHEN res_id = 2 THEN 1 ELSE 0 END) as fail
SUM(CASE WHEN res_id = 3 THEN 1 ELSE 0 END) as unknown
FROM t1
GROUP BY name

Untested, syntax may be off.|||Wow, this was a fast reply! And, what is more, a very good one. It works just the way I was looking for !

thanks a lot !

how to accquer certain number of records

Hi
http://www.aspfaq.com/show.asp?id=2120
"Crespo" <wuhuanfa@.21cn.com> wrote in message
news:eWB4YGflGHA.4716@.TK2MSFTNGP04.phx.gbl...
> hi,every body.
> I want to use SQL syntax to get records from a database table. As the
> table may contain large number of records,I would like to get a certain
> number of records first and also can get more records later as
> necessary.Can
> anyone help me?
>
> Crespo
>hi,every body.
I want to use SQL syntax to get records from a database table. As the
table may contain large number of records,I would like to get a certain
number of records first and also can get more records later as necessary.Can
anyone help me?
Crespo|||Hi
http://www.aspfaq.com/show.asp?id=2120
"Crespo" <wuhuanfa@.21cn.com> wrote in message
news:eWB4YGflGHA.4716@.TK2MSFTNGP04.phx.gbl...
> hi,every body.
> I want to use SQL syntax to get records from a database table. As the
> table may contain large number of records,I would like to get a certain
> number of records first and also can get more records later as
> necessary.Can
> anyone help me?
>
> Crespo
>|||Good work.Thank Uri Dimant very much!
Crespo
"Uri Dimant" <urid@.iscar.co.il> д?
news:eEkxHJflGHA.4716@.TK2MSFTNGP04.phx.gbl...
Hi
http://www.aspfaq.com/show.asp?id=2120
"Crespo" <wuhuanfa@.21cn.com> wrote in message
news:eWB4YGflGHA.4716@.TK2MSFTNGP04.phx.gbl...
> hi,every body.
> I want to use SQL syntax to get records from a database table. As the
> table may contain large number of records,I would like to get a certain
> number of records first and also can get more records later as
> necessary.Can
> anyone help me?
>
> Crespo
>|||Good work.Thank Uri Dimant very much!
Crespo
"Uri Dimant" <urid@.iscar.co.il> д?
news:eEkxHJflGHA.4716@.TK2MSFTNGP04.phx.gbl...
Hi
http://www.aspfaq.com/show.asp?id=2120
"Crespo" <wuhuanfa@.21cn.com> wrote in message
news:eWB4YGflGHA.4716@.TK2MSFTNGP04.phx.gbl...
> hi,every body.
> I want to use SQL syntax to get records from a database table. As the
> table may contain large number of records,I would like to get a certain
> number of records first and also can get more records later as
> necessary.Can
> anyone help me?
>
> Crespo
>|||"Crespo" <wuhuanfa@.21cn.com> wrote in message
news:eWB4YGflGHA.4716@.TK2MSFTNGP04.phx.gbl...
> hi,every body.
> I want to use SQL syntax to get records from a database table. As the
> table may contain large number of records,I would like to get a certain
> number of records first and also can get more records later as
necessary.Can
> anyone help me?
If using SQL 2005, look into using ROW_NUMBER also.

>
> Crespo
>|||"Crespo" <wuhuanfa@.21cn.com> wrote in message
news:eWB4YGflGHA.4716@.TK2MSFTNGP04.phx.gbl...
> hi,every body.
> I want to use SQL syntax to get records from a database table. As the
> table may contain large number of records,I would like to get a certain
> number of records first and also can get more records later as
necessary.Can
> anyone help me?
If using SQL 2005, look into using ROW_NUMBER also.

>
> Crespo
>|||Thank Uri Dimant. your suggestion works well. Thans again.
Crespo
"Uri Dimant" <urid@.iscar.co.il> д?
news:eEkxHJflGHA.4716@.TK2MSFTNGP04.phx.gbl...
Hi
http://www.aspfaq.com/show.asp?id=2120
"Crespo" <wuhuanfa@.21cn.com> wrote in message
news:eWB4YGflGHA.4716@.TK2MSFTNGP04.phx.gbl...
> hi,every body.
> I want to use SQL syntax to get records from a database table. As the
> table may contain large number of records,I would like to get a certain
> number of records first and also can get more records later as
> necessary.Can
> anyone help me?
>
> Crespo
>|||Thank Uri Dimant. your suggestion works well. Thans again.
Crespo
"Uri Dimant" <urid@.iscar.co.il> д?
news:eEkxHJflGHA.4716@.TK2MSFTNGP04.phx.gbl...
Hi
http://www.aspfaq.com/show.asp?id=2120
"Crespo" <wuhuanfa@.21cn.com> wrote in message
news:eWB4YGflGHA.4716@.TK2MSFTNGP04.phx.gbl...
> hi,every body.
> I want to use SQL syntax to get records from a database table. As the
> table may contain large number of records,I would like to get a certain
> number of records first and also can get more records later as
> necessary.Can
> anyone help me?
>
> Crespo
>

how to accquer certain number of records

hi,every body.
I want to use SQL syntax to get records from a database table. As the
table may contain large number of records,I would like to get a certain
number of records first and also can get more records later as necessary.Can
anyone help me?
CrespoHi
http://www.aspfaq.com/show.asp?id=2120
"Crespo" <wuhuanfa@.21cn.com> wrote in message
news:eWB4YGflGHA.4716@.TK2MSFTNGP04.phx.gbl...
> hi,every body.
> I want to use SQL syntax to get records from a database table. As the
> table may contain large number of records,I would like to get a certain
> number of records first and also can get more records later as
> necessary.Can
> anyone help me?
>
> Crespo
>|||Good work.Thank Uri Dimant very much!
Crespo
"Uri Dimant" <urid@.iscar.co.il> дÈëÏûÏ¢
news:eEkxHJflGHA.4716@.TK2MSFTNGP04.phx.gbl...
Hi
http://www.aspfaq.com/show.asp?id=2120
"Crespo" <wuhuanfa@.21cn.com> wrote in message
news:eWB4YGflGHA.4716@.TK2MSFTNGP04.phx.gbl...
> hi,every body.
> I want to use SQL syntax to get records from a database table. As the
> table may contain large number of records,I would like to get a certain
> number of records first and also can get more records later as
> necessary.Can
> anyone help me?
>
> Crespo
>|||"Crespo" <wuhuanfa@.21cn.com> wrote in message
news:eWB4YGflGHA.4716@.TK2MSFTNGP04.phx.gbl...
> hi,every body.
> I want to use SQL syntax to get records from a database table. As the
> table may contain large number of records,I would like to get a certain
> number of records first and also can get more records later as
necessary.Can
> anyone help me?
If using SQL 2005, look into using ROW_NUMBER also.
>
> Crespo
>|||Thank Uri Dimant. your suggestion works well. Thans again.
Crespo
"Uri Dimant" <urid@.iscar.co.il> дÈëÏûÏ¢
news:eEkxHJflGHA.4716@.TK2MSFTNGP04.phx.gbl...
Hi
http://www.aspfaq.com/show.asp?id=2120
"Crespo" <wuhuanfa@.21cn.com> wrote in message
news:eWB4YGflGHA.4716@.TK2MSFTNGP04.phx.gbl...
> hi,every body.
> I want to use SQL syntax to get records from a database table. As the
> table may contain large number of records,I would like to get a certain
> number of records first and also can get more records later as
> necessary.Can
> anyone help me?
>
> Crespo
>

How to accomplish what I am trying to do?

Hello,

I need to write a package the does the following, and not sure the best approach:

The package will read a list of stored procedure names stored in a table. Then, for each stored procedure name, I need to run the stored procedure and save the output to a named file.

That is, run sp1 and output to file sp1.txt. That is, I need the name to reflect the name of the stored procedure.

Also, each time I create the new output file, I need to delete the old named file.

I'm sure this is a fairly straightforward task, but not sure how to do the "dynamic" file naming part.

Any help appreciated!!

Thank you

You can use an expression on your flat file connection manager to change the file name at runtime. Just set the ConnectionString property. To replace the file, make sure your Flat File destination has Overwrite data enabled.

Do all your stored procs return the same resultset? If not, using a data flow will probably not work for you, so you wouldn't be able to use a Flat File destination.

|||

Hi John,

You say that if my procs do not return the same result set a data flow will probably not work?

What can I use instead?

I am having serious problems trying to get the output from my stored procedure loops to flat files.

That is, I added a data flow task to the foreach loop, so that each execsql task will run a stored proc then write the output to a file. But it seems this is not possible?

I find it hard to believe... but is there a workaround?

|||You could use a custom script task inside your loop to execute the procedures, read the results, and write them to a file.

How to accomplish this?

I'm a new user to SSIS and am trying to figure out something that I suspect may be very basic, but I'm having a hard time figuring it out.

I have a single table of "events" generated by an application. I will get a daily feed of these events. Events may be like so:

Event A Created
Event A Modified
Event A Property A Created
Event B Created
Event C Created
Event B Property A Modified
Event A Property B Created
Event C Voided
Event A Property A Closed
Event A Closed

In the end I want to create two tables like so:

Table 1:
Event A / Closed
Event B / Modified

(Event C is not here because it was voided, so any prior records of C are gone).

Table 2:
Event A / Property A / Closed
Event A / Property B / Created

(Only events with properties would be in this table).

In essence, I'm collapsing the transaction-based feed into normalized event-based and property-based tables, with only the latest "status" stored. These tables would be used to generate various other reports.

Obviously, as usual, the true situation is more complex than this but this simplified example covers my basic problem and I hope it can help someone to point me in the right direction.

"Events" can span over a day. It isn't practical to recreate the two sub-tables every day from scratch using SQL (and we don't want to do a complex query against the massive source table).

I would like to iterate over the new batch of events one record at a time. If I get an Event Created, then I will create a row in Table 1. If I get an Event Property Created, I will create a row in Table 2. Voids would cause a delete in both tables. Modifies of Events or Properties would cause the appropriate update in either table (Creates will always come before Modifies in the feed).

I tried using a Conditional Split, but I found that it appears to not go record-by-record, but instead prepares a list of records for each condition and then processes it in parallel... That was quite a shock. I expected it to go record-by-record and process things in order because I depend on "Create" to create the records that "Modifies" will update.

So my question is, is there a way to do what I am looking for in SSIS? Am I missing a simple setting to make Conditional Split process records in order?

Thanks for your help.May be you need to move the 'modifies' logic to a separate dataflow after the 'creates' are done. I don't think there is a easy way of enforcing the order of parallel data pipelines in a single dataflow.|||

Hi StGeorge,

I think you need to create some mechanism by which to order properties. I trust you when you say your actual process is more complicated, so I may be headed down the path with this suggestion - but here goes:

It sounds as if you are adding a row to Table 2 on a state named Created. It also sounds as if you are removing this row from Table 2 and its parent row in Table 1 on a similar state named Void.

Would it work if you created an integer column in Table 2 called PropertyOrder? You could populate it with 1 on Created, 3 on Void, and 2 for any other property event. In SSIS, you could then load the records from the source ordered by this field. If your transactional environment is intense (lots of asynchronous transactions), you may be forced to stage the ordered data (in a table or raw file) and then act on this resultset in a subsequent data flow or Execute SQL Task to guarantee transactional integrity (which is how I interpret Table 1).

I may be way off here. If I am, please accept my apologies.

The method I propose above is roughly analogous to a method many deterministic network protocols use to guarantee packets are output in the order sent. The packets are generated synchronously, transmitted asynchronously, received in any order, cached, and reconstructed by ordered key before delivery. I think you have all the pieces except the key by which to order the records.

Hope this helps,
Andy

How to Accomplish This

If I have the following Data in a Table:
TranType Qty
1 10
1 20
2 15
3 7
What is the best way to accomplish this?
Type1 Type2 Type3
30 15 7
Thanks,
KFKeith,
if you've fixed trantype ie limited to 3 then following query will solve the purpose otherwise you
will have to
make use of dynamic crosstab using T-SQL.
--sample table and data.
create table #t(TranType int,Qty int)
insert into #t select 1, 10 union all
select 1 ,20 union all
select 2 ,15 union all
select 3 ,7
--Required query.
select sum(case trantype when 1 then qty else 0 end) type1,
sum(case trantype when 2 then qty else 0 end) type2,
sum(case trantype when 3 then qty else 0 end) type3
from #t
Refer to following url for dynamic crosstab solution.
http://www.sqlteam.com/item.asp?ItemID=2955
--
- Vishal|||"Vishal Parkar" wrote
.
.
> Refer to following url for dynamic crosstab solution.
> http://www.sqlteam.com/item.asp?ItemID=2955
Save yourself a lot of time and aggravation
and check out the RAC utility for all kinds of
crosstabs and pivoting:)
RAC v2.2 and QALite @.
www.rac4sql.net

How to Accomplish MySQL-style LIMIT

I've graduated from MySQL to MSSQL Server, but the LIMIT command, which was very useful in MySQL isn't in MSSQL. There's the top command, but that doesn't really help me with my DataGrid paging needs. So how do I go about simulating the LIMIT command in MSSQL?

I tried searching the forums, but I couldn't seem to find anything...This link might be helpful|||Cool. Thanks. That was most helpful. I actually searched the forums for "paging," which returned a lot more results, but this was the best. Thanks again!

How to accomplish creating a simple calculated column in a table

Is there a way to create a calculated column in a table like this, or is
there a better way to accomplish this task? I've tried, but SQL keeps
adjusting my precedence and it goofs up the formula:
Let's say the column name is "NetPrice". I want to deduct the discounted
amount from the total to arrive at the net price. Discount is a decimal
value, ie: 25% = .25.
[TotalPrice] - ([TotPrice] * [Discount])Oops, typo below. Should be: [TotalPrice] - ([TotalPrice] * [Discount])
"Bill Hicks" <waylien_no_spam@.yahoo.com> wrote in message
news:uJhvc2lnGHA.4668@.TK2MSFTNGP02.phx.gbl...
> Is there a way to create a calculated column in a table like this, or is
> there a better way to accomplish this task? I've tried, but SQL keeps
> adjusting my precedence and it goofs up the formula:
> Let's say the column name is "NetPrice". I want to deduct the discounted
> amount from the total to arrive at the net price. Discount is a decimal
> value, ie: 25% = .25.
> [TotalPrice] - ([TotPrice] * [Discount])
>|||Bill
See if this helps
CREATE TABLE #Test (
TotalPrice DECIMAL, Discount DECIMAL,
NetPrice AS [TotalPrice] - ([TotalPrice] * [Discount]))
INSERT INTO #Test (TotalPrice,Discount) VALUES (50,15)
SELECT * FROM #Test
"Bill Hicks" <waylien_no_spam@.yahoo.com> wrote in message
news:uJhvc2lnGHA.4668@.TK2MSFTNGP02.phx.gbl...
> Is there a way to create a calculated column in a table like this, or is
> there a better way to accomplish this task? I've tried, but SQL keeps
> adjusting my precedence and it goofs up the formula:
> Let's say the column name is "NetPrice". I want to deduct the discounted
> amount from the total to arrive at the net price. Discount is a decimal
> value, ie: 25% = .25.
> [TotalPrice] - ([TotPrice] * [Discount])
>|||Thanks Uri. I think I was entering something incorrectly when I originally
entered the formula into the table designer.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ePsmpBmnGHA.764@.TK2MSFTNGP03.phx.gbl...
> Bill
> See if this helps
> CREATE TABLE #Test (
> TotalPrice DECIMAL, Discount DECIMAL,
> NetPrice AS [TotalPrice] - ([TotalPrice] * [Discount]))
>
> INSERT INTO #Test (TotalPrice,Discount) VALUES (50,15)
> SELECT * FROM #Test
>
> "Bill Hicks" <waylien_no_spam@.yahoo.com> wrote in message
> news:uJhvc2lnGHA.4668@.TK2MSFTNGP02.phx.gbl...
>

How to accomplish (Date)Between in MDX?

Hi all,

I thought it would be simple, but it turned out to be to difficult for me. I have the next problem;

I want to get the salesinfo of the latest 30 days, It should show me all the data between _Now() minus 30 days and Now()_
Has anyone any Idea how this could be accomplished?

Regards.

The main trick here is how to map the result of the now() function to a particular member in the date dimension.

Could you tell us how your Date dimension is structured and give some examples of what you are using for the key and name for the day attribute.

Also, are you sure that you want to use now() - another common approach would be to find the last day with data and then go back 30 days. A lot of data warehouses get re-populated overnight, so they are always lagging atleast one day behind the live data.

|||

Thanks for your reply Darren,

My Time Dimension is structured as follow:
Year (1-1-2005, 1-1-2006)
Quarter (1-1-2006, 1-4-2006)
Month (1-1-2006, 1-2-2006)
Week (1-1-2006, 8-1-2006)
PKDate (1-1-2006, 2-1-2006)- this is the Key

The reason why I want to use Now() is that the Time Table in the Datamart has fixed dates until the year 2008. And I'm not able to change this because I'm only working on reporting, and we work with subscription which have an enddate in 2007 or 2008.

Hopely this clearifies it a little bit.

Regards.

|||

I'm still making some assumptions, but I think you could set up a calculated member roughly like the following.

Create member CurrentCube.Time.PKDate.Today as StrToMember("[Time].[PKDate].[" + format(now(),"d-m-yyyy") + "]")

Then your measure for the last 30 days would be:

Create member CurrentCube.measures.Last30Days as Aggregate([Time].[PKDate].Today:[Time].[PKDate].Today.lag(30))

How to accomodate

I have a user (also my boss) who wants the system to work a specific way
which amounts to this:
1) ability to create an application as a developer
2) ability to deploy that application anywhere on any server in the network
3) ability to not need to worry about user permissions
I don't know how to explain how Windows Authentication works well enough -
in other words, in a syllable or so, that explains why this may not be
possible given the state of the various user groups and the concept of least
permission across a network.
We use groups and give read/write access to groups. When groups access
procedures, occasionally there are problems, particularly if the newly
created procedure is used by a group user and the procedure has not been
added to the role that allows access to the procedure.
Is there a command that will easily place all new procedures into the user
procedure role that doesn't involve putting every procedure into the role
(there are some we don't want the users to run)?
Regards,
JamieActually, posting in the wrong group here. This is a question for VS 2005
Net Framework deployment.
--
Regards,
Jamie
"thejamie" wrote:

> I have a user (also my boss) who wants the system to work a specific way
> which amounts to this:
> 1) ability to create an application as a developer
> 2) ability to deploy that application anywhere on any server in the networ
k
> 3) ability to not need to worry about user permissions
> I don't know how to explain how Windows Authentication works well enough -
> in other words, in a syllable or so, that explains why this may not be
> possible given the state of the various user groups and the concept of lea
st
> permission across a network.
> We use groups and give read/write access to groups. When groups access
> procedures, occasionally there are problems, particularly if the newly
> created procedure is used by a group user and the procedure has not been
> added to the role that allows access to the procedure.
> Is there a command that will easily place all new procedures into the user
> procedure role that doesn't involve putting every procedure into the role
> (there are some we don't want the users to run)?
> --
> Regards,
> Jamie

How to accomodate

I have a user (also my boss) who wants the system to work a specific way
which amounts to this:
1) ability to create an application as a developer
2) ability to deploy that application anywhere on any server in the network
3) ability to not need to worry about user permissions
I don't know how to explain how Windows Authentication works well enough -
in other words, in a syllable or so, that explains why this may not be
possible given the state of the various user groups and the concept of least
permission across a network.
We use groups and give read/write access to groups. When groups access
procedures, occasionally there are problems, particularly if the newly
created procedure is used by a group user and the procedure has not been
added to the role that allows access to the procedure.
Is there a command that will easily place all new procedures into the user
procedure role that doesn't involve putting every procedure into the role
(there are some we don't want the users to run)?
Regards,
Jamie
Actually, posting in the wrong group here. This is a question for VS 2005
Net Framework deployment.
Regards,
Jamie
"thejamie" wrote:

> I have a user (also my boss) who wants the system to work a specific way
> which amounts to this:
> 1) ability to create an application as a developer
> 2) ability to deploy that application anywhere on any server in the network
> 3) ability to not need to worry about user permissions
> I don't know how to explain how Windows Authentication works well enough -
> in other words, in a syllable or so, that explains why this may not be
> possible given the state of the various user groups and the concept of least
> permission across a network.
> We use groups and give read/write access to groups. When groups access
> procedures, occasionally there are problems, particularly if the newly
> created procedure is used by a group user and the procedure has not been
> added to the role that allows access to the procedure.
> Is there a command that will easily place all new procedures into the user
> procedure role that doesn't involve putting every procedure into the role
> (there are some we don't want the users to run)?
> --
> Regards,
> Jamie

How to accomodate

I have a user (also my boss) who wants the system to work a specific way
which amounts to this:
1) ability to create an application as a developer
2) ability to deploy that application anywhere on any server in the network
3) ability to not need to worry about user permissions
I don't know how to explain how Windows Authentication works well enough -
in other words, in a syllable or so, that explains why this may not be
possible given the state of the various user groups and the concept of least
permission across a network.
We use groups and give read/write access to groups. When groups access
procedures, occasionally there are problems, particularly if the newly
created procedure is used by a group user and the procedure has not been
added to the role that allows access to the procedure.
Is there a command that will easily place all new procedures into the user
procedure role that doesn't involve putting every procedure into the role
(there are some we don't want the users to run)?
--
Regards,
JamieActually, posting in the wrong group here. This is a question for VS 2005
Net Framework deployment.
--
Regards,
Jamie
"thejamie" wrote:
> I have a user (also my boss) who wants the system to work a specific way
> which amounts to this:
> 1) ability to create an application as a developer
> 2) ability to deploy that application anywhere on any server in the network
> 3) ability to not need to worry about user permissions
> I don't know how to explain how Windows Authentication works well enough -
> in other words, in a syllable or so, that explains why this may not be
> possible given the state of the various user groups and the concept of least
> permission across a network.
> We use groups and give read/write access to groups. When groups access
> procedures, occasionally there are problems, particularly if the newly
> created procedure is used by a group user and the procedure has not been
> added to the role that allows access to the procedure.
> Is there a command that will easily place all new procedures into the user
> procedure role that doesn't involve putting every procedure into the role
> (there are some we don't want the users to run)?
> --
> Regards,
> Jamie

How to access!

Hi everybody,
I want to know how can I access to SQL Server 7.0 (installed on windows 2000) from Other platforms line Win9X in a visual basic program.

Please tell me complete story,
1) What I have to do on server (windows 2000 - MSSQL 7.0)
2) What I have to do on clients (Windows 9X)
3) Connection string to connect to server from client in VB 6.0 language

Thanks :)Ali,

This is a pretty big topic, and you might be better off talking with VB programmers than Database Gurus.

Your VB documentation should tell you how to go about getting data from outside datasources.

My short answer: ODBC connections.

blindman|||Originally posted by blindman
Ali,

This is a pretty big topic, and you might be better off talking with VB programmers than Database Gurus.

Your VB documentation should tell you how to go about getting data from outside datasources.

My short answer: ODBC connections.

blindman

Just a question:
Is there any configuration nedded on Server and Client system?|||Ali,

This is a pretty big topic, and you might be better off talking with VB programmers than Database Gurus.

Your VB documentation should tell you how to go about getting data from outside datasources.

My short answer: ODBC connections.

Unatratnag

1) What I have to do on server (windows 2000 - MSSQL 7.0)
you have to create the database, and if you're doing odbc, then you'll need to create the odbc on the pc where the application resides

2) What I have to do on clients (Windows 9X)
write the code to connect to the database =P

3) Connection string to connect to server from client in VB 6.0 language
well if you have access to the database files create a .udl file and then double click it and it will allow you to create a connection string through the gui, open it back up in notepad and the connection string is the third line.|||People talking about ODBC are running behind for two Microsoft Data Access generations. After ODBC (related to DAO = Data Access Objects) came ADO (= ActiveX Data Objects) with its collection of OLE DB providers, needing as you asked connectionstrings. Currently, we are living in the ADO.NET age, however, you are working with VB 6, which means you would have to choose ADO.

You don't need any extra installations on your server.

On your Win9x client, you need to start with DCOM98. Furthermore, you need MDAC, preferably the latest version 2.7 SP1a. You may browse the www.keper.com (ftp://dbxprof:dbxprof@.FTP server of my DB Explorer for the installations, and you find some hints in the installation manual.

For a discussion of the connection string in trusted environments, or with DB security see ABLE Consulting (http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForSQLServer). However, you may also want to build a connection string. Herefore, Microsoft utilizes Data Link Property forms, which can be accessed within your application.|||The FTP server link doesn't work. It is DB Explorer (ftp://dbxprof:dbxprof@.213.84.56.3/)|||setup an ADODB connection, use SQLOLEDB as the connection provider.|||I don't think there are Gurus here, just a bunch of people with no life :)

amirnezhad,

The topic may be big, but not the task. What is it you're trying to do? If you want to learn and looking for the way to start, then follow DoktorBlue's links. But if you were given a task to do a specific thing, then post it and someone may give you the whole answer.

P.S.: Since you're from Tehran, how are things looking from there?|||rocheyc: can't figure out the added vaue of your contribution, please help me.

rdjabarov: sounds like you are proposing a new exciting thread about guru properties. Is it really the number of contributions? Or the lack of social life? Or just the nick name? :confused: Maybe the real gurus know ?! ;)|||Originally posted by DoktorBlue
rocheyc: can't figure out the added vaue of your contribution, please help me.

Funny, when I read your post I felt the same way about you!

Originally posted by DoktorBlue
rdjabarov: sounds like you are proposing a new exciting thread about guru properties. Is it really the number of contributions? Or the lack of social life? Or just the nick name? :confused: Maybe the real gurus know ?! ;)

Yes, DoktorBlue the real qurus know.|||Thanks, Paul, you let me see that real gurus don't contribute to the topic, right?

I'd prefer to move further discussions in a new thread, or to keep it private.|||I see the p***ing contest is starting again. Well, I'll be here in the front row watching. Is it time to place bets yet?