question re: maintenance

Ok, I want to write a script that

1. backups my sql database
2. commits the transactionlog
3. shrinks the db
4. shrinks the db log

does anyone have a script that will do this or can show me the light?

thank you! (sorry, i'm a programmer and not a very good dba regarding maintenance!)SQL Server 2000?

I would recommend creating a maintenance plan in Enterprise Manager, as it is easier than scripting the whole thing out. Open the database in question and set to TaskPad view is the easiest. You can then click on the dropdown second from the bottom and choose maintenance plan.

I will have to look, as I am not sure this does 100% of what you want. If not, you can set up a job with multiple steps and run the stored procedures necessary to backup, et al. The SQL Books Online (installed with client tools) is a great source of knowledge.

Question on Restore Script

I am trying to write a generic restore script which could be used for
all the similar restores we do on a dialy baisis. Basically the only
thing that changes are the database names and all these backup are from
all different servers.
So I understand I would have to write a restore script with the MOVE
option and I am planning on passing the parameter
@.databasename,@.backupFileLocation.
However is there anyway to determine the names of the file in the
backup so that I could use those in my Restore Command without any user
intervention? Unless I am able to do that I cannot get the entire
process automated. From everthing I have read so far it suggests that I
would have to run RESTORE FILELISTONLY command to get the file names
and then edit my T_SQL command for each restore operation.
Is there a cool way of doing without any intervention?
Any help in this regard will be appreciated.
Thanks
Check the code in http://www.karaszi.com/SQLServer/uti...l_in_file.asp. That should give
you a good starting point.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"shub" <shubtech@.gmail.com> wrote in message
news:1137513510.543961.83750@.g44g2000cwa.googlegro ups.com...
>I am trying to write a generic restore script which could be used for
> all the similar restores we do on a dialy baisis. Basically the only
> thing that changes are the database names and all these backup are from
> all different servers.
> So I understand I would have to write a restore script with the MOVE
> option and I am planning on passing the parameter
> @.databasename,@.backupFileLocation.
> However is there anyway to determine the names of the file in the
> backup so that I could use those in my Restore Command without any user
> intervention? Unless I am able to do that I cannot get the entire
> process automated. From everthing I have read so far it suggests that I
> would have to run RESTORE FILELISTONLY command to get the file names
> and then edit my T_SQL command for each restore operation.
> Is there a cool way of doing without any intervention?
> Any help in this regard will be appreciated.
> Thanks
>
|||This is exactly the kind of script I was looking for. Thank you very
much. I really appreciate it.

Question on Restore Script

I am trying to write a generic restore script which could be used for
all the similar restores we do on a dialy baisis. Basically the only
thing that changes are the database names and all these backup are from
all different servers.
So I understand I would have to write a restore script with the MOVE
option and I am planning on passing the parameter
@.databasename,@.backupFileLocation.
However is there anyway to determine the names of the file in the
backup so that I could use those in my Restore Command without any user
intervention? Unless I am able to do that I cannot get the entire
process automated. From everthing I have read so far it suggests that I
would have to run RESTORE FILELISTONLY command to get the file names
and then edit my T_SQL command for each restore operation.
Is there a cool way of doing without any intervention?
Any help in this regard will be appreciated.
ThanksCheck the code in http://www.karaszi.com/SQLServer/ut...ver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"shub" <shubtech@.gmail.com> wrote in message
news:1137513510.543961.83750@.g44g2000cwa.googlegroups.com...
>I am trying to write a generic restore script which could be used for
> all the similar restores we do on a dialy baisis. Basically the only
> thing that changes are the database names and all these backup are from
> all different servers.
> So I understand I would have to write a restore script with the MOVE
> option and I am planning on passing the parameter
> @.databasename,@.backupFileLocation.
> However is there anyway to determine the names of the file in the
> backup so that I could use those in my Restore Command without any user
> intervention? Unless I am able to do that I cannot get the entire
> process automated. From everthing I have read so far it suggests that I
> would have to run RESTORE FILELISTONLY command to get the file names
> and then edit my T_SQL command for each restore operation.
> Is there a cool way of doing without any intervention?
> Any help in this regard will be appreciated.
> Thanks
>|||This is exactly the kind of script I was looking for. Thank you very
much. I really appreciate it.

Question on Restore Script

I am trying to write a generic restore script which could be used for
all the similar restores we do on a dialy baisis. Basically the only
thing that changes are the database names and all these backup are from
all different servers.
So I understand I would have to write a restore script with the MOVE
option and I am planning on passing the parameter
@.databasename,@.backupFileLocation.
However is there anyway to determine the names of the file in the
backup so that I could use those in my Restore Command without any user
intervention? Unless I am able to do that I cannot get the entire
process automated. From everthing I have read so far it suggests that I
would have to run RESTORE FILELISTONLY command to get the file names
and then edit my T_SQL command for each restore operation.
Is there a cool way of doing without any intervention?
Any help in this regard will be appreciated.
ThanksCheck the code in http://www.karaszi.com/SQLServer/util_restore_all_in_file.asp. That should give
you a good starting point.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"shub" <shubtech@.gmail.com> wrote in message
news:1137513510.543961.83750@.g44g2000cwa.googlegroups.com...
>I am trying to write a generic restore script which could be used for
> all the similar restores we do on a dialy baisis. Basically the only
> thing that changes are the database names and all these backup are from
> all different servers.
> So I understand I would have to write a restore script with the MOVE
> option and I am planning on passing the parameter
> @.databasename,@.backupFileLocation.
> However is there anyway to determine the names of the file in the
> backup so that I could use those in my Restore Command without any user
> intervention? Unless I am able to do that I cannot get the entire
> process automated. From everthing I have read so far it suggests that I
> would have to run RESTORE FILELISTONLY command to get the file names
> and then edit my T_SQL command for each restore operation.
> Is there a cool way of doing without any intervention?
> Any help in this regard will be appreciated.
> Thanks
>|||This is exactly the kind of script I was looking for. Thank you very
much. I really appreciate it.

Question on MDX

I am trying to write a calculated field. I need to calculate the Sum of a Item Sold over a period of time where 2 measures called BV value and Unit Price are zero. Can someone suggest what functions I should use to get this done

Thanks

Ann

Hello Ann,

Did you mean to use a calculated member? If yes, please, try the following:

WITH MEMBER [Measures].SumOfSoldItems AS 'sum ( filter ( [Date].[Fiscal Year].members, [Measures].[BV Value] = 0 AND [Measures].[Unit Price] = 0 ) , [Measures].[Item Sold] ) '
SELECT [Measures].SumOfSoldItems on 0
FROM [Adventure Works]

In the first argument of the filter function you should put a set representing the period of time that you are interested in.

I hope this helps

Greg

|||

Hi Greg

Thanks for your post. Thats exactly what I am trying to achieve. I tried using the above method, but it gives me a syntax error . It says, Syntax for "WITH" is incorrect.

Thanks

Ann

|||

Could you tell me what client application you are using? I wrote 2 more MDX queries for you so you can use them as a small tutorial. They both work with Adventure Works.

1. This one doesn't use calculated member but should give you the right results. After you try it, you can change the query to work with your own cube. Change the time dimension members ( [Date].[Fiscal Year].members ) to a set representing the time period you need. The two other measures in the filter you can change to [Measures].[BV value]=0 and [Measures].[Unit Price] = 0. The measure in the WHERE clause you may want to change to [Measures].[Item Sold]

SELECT filter ( [Date].[Fiscal Year].members, [Measures].[Amount] > 3500000 AND [Measures].[Order Quantity] > 90000 ) on 0
FROM [Adventure Works]
WHERE [Measures].[Sales Amount]

2. This is the query that I showed you yesterday but I changed it back slightly so now it works with Adventure Works. Try it first against AW and then change the time period and measures for your own needs as I explained in the first paragraph:

WITH MEMBER [Measures].x AS 'sum ( filter ( [Date].[Fiscal Year].members, [Measures].[Amount] > 0 AND [Measures].[Order Quantity] > 0 ) , [Measures].[Sales Amount] ) '
SELECT [Measures].x on 0
FROM [Adventure Works]

I hope this works for you. If it doesn't or you have more questions, feel free to ask :)

- Greg

Question on DTS

HI all,
Which is better, to use DTS for CSV type file imports or to write your own
import app.
Considering the following that it is most likely that the structure of the
import file will change a few times within the next year, and that this is
not inhouse but for an outside client, and that they would most probably be
using SQLXpress
Value your thoughts
Thanks
RobertDTS is the right tool for this task.
Although I would not be surprised if a very experienced C# developer with
basic DTS knowledge can write this application a lot faster than writing the
DTS package.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Robert Bravery" wrote:

> HI all,
> Which is better, to use DTS for CSV type file imports or to write your own
> import app.
> Considering the following that it is most likely that the structure of the
> import file will change a few times within the next year, and that this is
> not inhouse but for an outside client, and that they would most probably b
e
> using SQLXpress
> Value your thoughts
> Thanks
> Robert
>
>|||Thanks Ben,
Next question:
How easy would it be to update the dts package on a clients machine when and
if structure and protocol changes.
Thanks
Robert
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:256513B7-AABF-4276-9E7F-2343D256C30B@.microsoft.com...
> DTS is the right tool for this task.
> Although I would not be surprised if a very experienced C# developer with
> basic DTS knowledge can write this application a lot faster than writing
the
> DTS package.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Robert Bravery" wrote:
>
own
the
is
be|||Using the DTS GUI Interface (DTS Designer) is very easy. Depending on the
complexity of your DTS package you may want to document what to do to
implement these future changes, instead of trying to understand how the
package works after you have not seen it for ws or months.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Robert Bravery" wrote:

> Thanks Ben,
> Next question:
> How easy would it be to update the dts package on a clients machine when a
nd
> if structure and protocol changes.
> Thanks
> Robert
> "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> news:256513B7-AABF-4276-9E7F-2343D256C30B@.microsoft.com...
> the
> own
> the
> is
> be
>
>

Question on Custom code assembly

Hi,
I have to build a report whose content is very complex and comes from
multiple tables. I think the best possible way is to write custom
code(assembly). Is it possible to return a data set using custom code
assembly and assign it to report ?
Please help.
-- Thanks
RKOn Oct 19, 11:19 am, "S V Ramakrishna"
<ramakrishna.seeth...@.translogicsys.com> wrote:
> Hi,
> I have to build a report whose content is very complex and comes from
> multiple tables. I think the best possible way is to write custom
> code(assembly). Is it possible to return a data set using custom code
> assembly and assign it to report ?
> Please help.
> -- Thanks
> RK
Hi,
What you could do is write a storeproc for your dataset that handles
all (or most of)
your complex table handling.
V.|||Hi,
Thanks for the reply. Stored Procedure is a very good idea. I have a small
doubt. Is it possible at all to have a return type other than primitive
type(for example an array of integers ) for a method in a custom code
assembly in SSRS ?
--
RK
"Vinnie" <vsempoux@.gmail.com> wrote in message
news:1192786497.261274.131220@.e34g2000pro.googlegroups.com...
> On Oct 19, 11:19 am, "S V Ramakrishna"
> <ramakrishna.seeth...@.translogicsys.com> wrote:
>> Hi,
>> I have to build a report whose content is very complex and comes from
>> multiple tables. I think the best possible way is to write custom
>> code(assembly). Is it possible to return a data set using custom code
>> assembly and assign it to report ?
>> Please help.
>> -- Thanks
>> RK
> Hi,
> What you could do is write a storeproc for your dataset that handles
> all (or most of)
> your complex table handling.
> V.
>

Question on cube write back

Hi, all experts here,

I encountered a problem with cube writing back which contains measures with count aggregation rather than sum which said write back can not be allowed on measure groups with other aggregations rather than sum? Is it saying we can only enable write back on cubes with measure groups all based on sum aggregations?

But in my case, the measures needed to be counted rather than sum. What can I try to enable write back on this cube?

I am looking forward to hearing from you for your advices and thank you very much in advance.

With best regards,

Yours sincerely,

I'm having trouble thinking of a situation where you would need to writeback to a count measure, but that is beside the point.

One work around might be to create a new column in your fact table for this measure and pre-populate it with a value of 1 during ETL. Then you would be able to use a sum aggregation and get the same value as you would with a count measure. It would then also be possible to write back to this measure.

|||

Hi,

In my data, the facts are bit data, which only with values of 1 and 0 meaning different things respectively. Therefore I need to count these different situations happened in the fact table across different levels. There are no numeric data types at the moment which are for sum.

Thanks for your advices and I am looking forward to hearing from any of you here more ideas in this situation. Maybe you would give me some better ideas on how to deal with it.

With best regards,

Yours sincerely,

|||

Helen,

Explain me better what is the goal, I didnt understood... Explain me as I a dummy!

:-)

Regards!

|||

HI, PedroCGD,

The facts in the facts tables are with 0 or 1 values only. (0 and 1 therefore represent different meanings), therefore I dont see any point to sum these facts in any cube. Instead, count aggregation makes more sense which can count the numbers for the fact being 0 or 1.

Hope my explanation is clear for your help.

With best regards,

Yours sincerely,

|||It sounds like you have some flags which should modelled as separate attributes, not as measures. You would have one count measure and set either a single dimension or a combined dimension (also known as a 'junk' dimension). In this way you should be able to get the count by any combination of attributes.|||

Hi, Darren,

Thanks for your kind advices.

As you kindly advised, I will then need to update the underlying relational database (as the underlying data warehouse is designed by others)?Since at the moment, the data residing in the data warehouse does not have any junk dimensions at all. All those flags attributes are residing in the fact tables.

I would need to create single dimension for each flag attribute or combine some of the flag attributes together into one single dimension? But only one count measure for all these dimension attributes?

Will it be any good to complete these in data source view of the cube? Thanks again. And hope you can give me more further advices on it.

With best regards,

Yours sincerely,