producing a date time report in SQL/DTS

I have need to produce a report (excel sheet actually) from SQL that
would run each Tuesday and each Friday of every w.
What needs to be on the Tuesday report is everything that came in from
the Friday midnight time, until the Monday midnight time. The friday
report(sheet) would have everything that came in from Midnight Monday
evening, thru midnight Thursday. The next Tuesday report would have
everything from Midnight Thursday thru midnight Monday, and so on.
I know I can schedule the jobs to run on that interval, but how do I
selectively pick the records I want? There is a datetime field on the
table, "submit date" and what I am basically doing is a select * from
tbl_literature_orders where date > x.
Any ideas?
Thanks,
BC"Blasting Cap" schrieb:
> I have need to produce a report (excel sheet actually) from SQL that
> would run each Tuesday and each Friday of every w.
> What needs to be on the Tuesday report is everything that came in from
> the Friday midnight time, until the Monday midnight time. The friday
> report(sheet) would have everything that came in from Midnight Monday
> evening, thru midnight Thursday. The next Tuesday report would have
> everything from Midnight Thursday thru midnight Monday, and so on.
> I know I can schedule the jobs to run on that interval, but how do I
> selectively pick the records I want? There is a datetime field on the
> table, "submit date" and what I am basically doing is a select * from
> tbl_literature_orders where date > x.
> Any ideas?
> Thanks,
> BC
Try it with two jobs, one for Tuesday, one for Friday, and set the execution
time of the job appropriately. Search for your data by difference:
select * from MyTable where datefield > dateadd(d, -3, GetDate()) -- Friday
and
select * from MyTable where datefield > dateadd(d, -4, GetDate()) -- Tuesday|||Just use the DATEPART() or DATENAME() functions to determine which day it
is. Then use DATEADD() with the appropriate days to get the from and to
that you need for your WHERE clause.
Andrew J. Kelly SQL MVP
"Blasting Cap" <goober@.christian.net> wrote in message
news:eiUP1w9CGHA.4080@.TK2MSFTNGP09.phx.gbl...
>I have need to produce a report (excel sheet actually) from SQL that would
>run each Tuesday and each Friday of every w.
> What needs to be on the Tuesday report is everything that came in from the
> Friday midnight time, until the Monday midnight time. The friday
> report(sheet) would have everything that came in from Midnight Monday
> evening, thru midnight Thursday. The next Tuesday report would have
> everything from Midnight Thursday thru midnight Monday, and so on.
> I know I can schedule the jobs to run on that interval, but how do I
> selectively pick the records I want? There is a datetime field on the
> table, "submit date" and what I am basically doing is a select * from
> tbl_literature_orders where date > x.
> Any ideas?
> Thanks,
> BCsql

Processing SSAS2005 from DTS

Hi,

I have a requirement to process SSAS2005 cubes from DTS. Anyone know how to do this or does anyone have any recommendations?

I've done a search on this forum and came up with teh following which was no help at all unfortunately: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=198374&SiteID=1

-Jamie

Hi Jamie,

Will the ASCMD command-line utility (available under SQL Server Analysis Services Samples) serve your purpose (invoked within a DTS "Execute Process" task, of course)?

http://msdn2.microsoft.com/en-us/ms365187.aspx

>>

SQL Server 2005 Books Online

Readme For Ascmd Command-line Utility Sample

...

Processing a Partition from a Third-Party Tool

A database administrator must process partitions and dimensions as part of a nightly extract, transform, and load (ETL) process. The ETL tool is not a SQL Server tool, and so the database administrator cannot use SQL Server Agent’s built-in support of XMLA scripts and cannot run a SQL Server 2005 Integration Services (SSIS) package. The database administrator wants an automated solution that utilizes the third-party tool. This means a command-line utility to run the XMLA script that can be called from the third-party tool. The database administrator downloads and compiles the ascmd command-line utility sample. The administrator can then use the ascmd command-line utility to process partitions and dimensions.

>>

|||

Deepak,

I'm certainly hoping so. Thanks for the reply.

-Jamie

|||

It works a treat. Thanks again!

XMLA - loving it!

cheers

-Jamie

|||

Did you download ASCMD.exe from this link or was it already on installed for you in the location specified in the ReadMe?

I've installed the developer edition on my machine and can't find the file in the location specified.

|||

We have not yet upgraded to 2005. Is there a similar tool for MSAS2000?

Thanks

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

Momo:

ascmd was added to the SQL Server samples in SP1, you can download the latest samples from here http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en

|||Tried that. Contains sample code but not the actual exe?|||That's right, ascmd was only released as source code.|||

How can I get the XMLA script to process a cube in SSAS 2000. If I can get this I would think I could make a SOAP call from my program to execute the XMLA. This would be great for me. I would have everything in one program.

|||

Unfortunately I am pretty sure AS 2000 does not support the XMLA process command.

The best work around I can think of in order to process AS 2000 objects using SOAP would be to write your own web service that used DSO to process the cube. You would need to have the web service running under a domain account that was a member of the OLAP Administrators group to get this working.

|||

Hi,

I tried to install the SSAS Samples but the SqlServerSamples.msi file available in the following link is corrupted and I can′t find another place to obtain it.

http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en

|||I just tried the download and it appears to work OK. Give the download another try, maybe there were some transient network issues.

Processing SSAS2005 from DTS

Hi,

I have a requirement to process SSAS2005 cubes from DTS. Anyone know how to do this or does anyone have any recommendations?

I've done a search on this forum and came up with teh following which was no help at all unfortunately: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=198374&SiteID=1

-Jamie

Hi Jamie,

Will the ASCMD command-line utility (available under SQL Server Analysis Services Samples) serve your purpose (invoked within a DTS "Execute Process" task, of course)?

http://msdn2.microsoft.com/en-us/ms365187.aspx

>>

SQL Server 2005 Books Online

Readme For Ascmd Command-line Utility Sample

...

Processing a Partition from a Third-Party Tool

A database administrator must process partitions and dimensions as part of a nightly extract, transform, and load (ETL) process. The ETL tool is not a SQL Server tool, and so the database administrator cannot use SQL Server Agent’s built-in support of XMLA scripts and cannot run a SQL Server 2005 Integration Services (SSIS) package. The database administrator wants an automated solution that utilizes the third-party tool. This means a command-line utility to run the XMLA script that can be called from the third-party tool. The database administrator downloads and compiles the ascmd command-line utility sample. The administrator can then use the ascmd command-line utility to process partitions and dimensions.

>>

|||

Deepak,

I'm certainly hoping so. Thanks for the reply.

-Jamie

|||

It works a treat. Thanks again!

XMLA - loving it!

cheers

-Jamie

|||

Did you download ASCMD.exe from this link or was it already on installed for you in the location specified in the ReadMe?

I've installed the developer edition on my machine and can't find the file in the location specified.

|||

We have not yet upgraded to 2005. Is there a similar tool for MSAS2000?

Thanks

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

Momo:

ascmd was added to the SQL Server samples in SP1, you can download the latest samples from here http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en

|||Tried that. Contains sample code but not the actual exe?|||That's right, ascmd was only released as source code.|||

How can I get the XMLA script to process a cube in SSAS 2000. If I can get this I would think I could make a SOAP call from my program to execute the XMLA. This would be great for me. I would have everything in one program.

|||

Unfortunately I am pretty sure AS 2000 does not support the XMLA process command.

The best work around I can think of in order to process AS 2000 objects using SOAP would be to write your own web service that used DSO to process the cube. You would need to have the web service running under a domain account that was a member of the OLAP Administrators group to get this working.

|||

Hi,

I tried to install the SSAS Samples but the SqlServerSamples.msi file available in the following link is corrupted and I can′t find another place to obtain it.

http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en

|||I just tried the download and it appears to work OK. Give the download another try, maybe there were some transient network issues.

Processing OutPut from exec command

Hi,
I am using exec command command to run xp_cmdshell ext SP.
the command line string given to xp_cmdshell is for executing DTS Packages.
i want to process the out put returned by the exec command in my stored proc
Is it possible to capture the out put in addition of return code(0/1),which
is returned by xp_cmdshell, if yes then how?
thanks in advance
Kailuxdeclare @.e int
exec @.=master.dbo.xp_cmdshell ....
select @.e
"kailux4" wrote:
> Hi,
> I am using exec command command to run xp_cmdshell ext SP.
> the command line string given to xp_cmdshell is for executing DTS Packages.
> i want to process the out put returned by the exec command in my stored proc
> Is it possible to capture the out put in addition of return code(0/1),which
> is returned by xp_cmdshell, if yes then how?
> thanks in advance
> Kailux|||declare @.e int
exec @.e=master.dbo.xp_cmdshell ....
select @.e
"kailux4" wrote:
> Hi,
> I am using exec command command to run xp_cmdshell ext SP.
> the command line string given to xp_cmdshell is for executing DTS Packages.
> i want to process the out put returned by the exec command in my stored proc
> Is it possible to capture the out put in addition of return code(0/1),which
> is returned by xp_cmdshell, if yes then how?
> thanks in advance
> Kailux

Processing OutPut from exec command

Hi,
I am using exec command command to run xp_cmdshell ext SP.
the command line string given to xp_cmdshell is for executing DTS Packages.
i want to process the out put returned by the exec command in my stored proc
Is it possible to capture the out put in addition of return code(0/1),which
is returned by xp_cmdshell, if yes then how?
thanks in advance
Kailuxdeclare @.e int
exec @.=master.dbo.xp_cmdshell ....
select @.e
"kailux4" wrote:

> Hi,
> I am using exec command command to run xp_cmdshell ext SP.
> the command line string given to xp_cmdshell is for executing DTS Packages
.
> i want to process the out put returned by the exec command in my stored pr
oc
> Is it possible to capture the out put in addition of return code(0/1),whic
h
> is returned by xp_cmdshell, if yes then how?
> thanks in advance
> Kailux|||declare @.e int
exec @.e=master.dbo.xp_cmdshell ....
select @.e
"kailux4" wrote:

> Hi,
> I am using exec command command to run xp_cmdshell ext SP.
> the command line string given to xp_cmdshell is for executing DTS Packages
.
> i want to process the out put returned by the exec command in my stored pr
oc
> Is it possible to capture the out put in addition of return code(0/1),whic
h
> is returned by xp_cmdshell, if yes then how?
> thanks in advance
> Kailux

Processing OutPut from exec command

Hi,
I am using exec command command to run xp_cmdshell ext SP.
the command line string given to xp_cmdshell is for executing DTS Packages.
i want to process the out put returned by the exec command in my stored proc
Is it possible to capture the out put in addition of return code(0/1),which
is returned by xp_cmdshell, if yes then how?
thanks in advance
Kailux
declare @.e int
exec @.=master.dbo.xp_cmdshell ....
select @.e
"kailux4" wrote:

> Hi,
> I am using exec command command to run xp_cmdshell ext SP.
> the command line string given to xp_cmdshell is for executing DTS Packages.
> i want to process the out put returned by the exec command in my stored proc
> Is it possible to capture the out put in addition of return code(0/1),which
> is returned by xp_cmdshell, if yes then how?
> thanks in advance
> Kailux
|||declare @.e int
exec @.e=master.dbo.xp_cmdshell ....
select @.e
"kailux4" wrote:

> Hi,
> I am using exec command command to run xp_cmdshell ext SP.
> the command line string given to xp_cmdshell is for executing DTS Packages.
> i want to process the out put returned by the exec command in my stored proc
> Is it possible to capture the out put in addition of return code(0/1),which
> is returned by xp_cmdshell, if yes then how?
> thanks in advance
> Kailux

Processing of cubes in a scheduled DTS

Hi everyone!

I created a DTS which does some data transformations before processing some cubes. It finished processing in abt 10mins when I run this DTS manually. However, when I schedule this DTS to run, it took around 3 over hours to run. Does anybody know where the problem lies? I have been looking for a solution for this for a long time and I'm hoping that somebody can help me...

Thank you!! :)

MichelleDo you have the latest SP on the server? I seem to recall something in sp3 that is supposed to help this|||My database is in a different server from the cube. The SQL server for this database is Server 2000, service pack 3a. The one for my cubes is version 7.00.623. Will the difference in version slow down the process?

Processing Cubes via a SQL Server 2000 DTS

Hi,

I have a DTS which processes a set of AS 2000 cubes via a set of AS Processing Tasks.

I want to know how I can accurately trap and report errors once a cube fails to process?

Thanks in advance

Jon Derbyshire

The logging of processing errors in DTS ( SQL Server 2000 ) is bit limited.

You should try and look at migrating your application to SQL Server 2005 instead. I know it is bit a far fetched suggestion to migrate your whole solution to SQL Server 2005 just because of a single problem with accurately tracking processing errors. But the problem is: your will find increasingly harder to implement any new functionality with old version of the product. You will find it harder to find answers to your questions in the community that has moved to new ( different) technologies. At some point you should look at developing new solution using SQL Server 2005.

Hope that helps.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Processing a cube on SQL server 2005 from a dts package on SQL server 2000

We just started migrating some cubes to the 2005 platform. Still there are some DTS packages running on the 2000 platform that needs some recoding to fit the 2005 enviroment. Therefore we have some solutions where we have DTS packages running on SQL Server 2000 and their "related" cubes running on the 2005 platform.

My question is therefore: Is there an easy way to initiate a processing of a cube on the 2005 platform from a Sql server 2000 DTS package ?

Hmm seems that there are no easy way|||

Hello cgpl,

I think this may be of help to you: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=265208&SiteID=1

-Jamie

Processing a cube on SQL server 2005 from a dts package on SQL server 2000

We just started migrating some cubes to the 2005 platform. Still there are some DTS packages running on the 2000 platform that needs some recoding to fit the 2005 enviroment. Therefore we have some solutions where we have DTS packages running on SQL Server 2000 and their "related" cubes running on the 2005 platform.

My question is therefore: Is there an easy way to initiate a processing of a cube on the 2005 platform from a Sql server 2000 DTS package ?

Hmm seems that there are no easy way|||

Hello cgpl,

I think this may be of help to you: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=265208&SiteID=1

-Jamie

Processing a cube on SQL server 2005 from a dts package on SQL server 2000

We just started migrating some cubes to the 2005 platform. Still there are some DTS packages running on the 2000 platform that needs some recoding to fit the 2005 enviroment. Therefore we have some solutions where we have DTS packages running on SQL Server 2000 and their "related" cubes running on the 2005 platform.

My question is therefore: Is there an easy way to initiate a processing of a cube on the 2005 platform from a Sql server 2000 DTS package ?

Hmm seems that there are no easy way|||

Hello cgpl,

I think this may be of help to you: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=265208&SiteID=1

-Jamie

processadmin role

I'm trying to allow my developers the ability to modify/execute their jobs and dts packages in production...without giving away the security farm so to speak.

Is the processadmin role a possibility?

BOL and the net only seems to say this role allows user to "manage process"...duh.

Your thoughts and advice would be great appreciated.The processadmin server role conveys the ability to kill a process (SPID) in SQL Server. Can't say as I would be comfy with a lot of people with that ability, myself.

In order to create/delete jobs, they will need access to the msdb database (by default all users do), and permissions on the following stored procedures, which also default to public:

sp_add_job
sp_add_jobschedule
sp_add_jobserver
sp_add_jobstep
sp_delete_job
sp_delete_jobschedule
sp_delete_jobserver
sp_delete_jobstep
sp_start_job
sp_stop_job
sp_update_job
sp_update_jobschedule
sp_update_jobstep|||If I give them this kind of access in msdb, won't it give them job and dts access to all databases?|||Jobs and DTS packages are stored only in the msdb database, so yes. That's just the way the system is set up. If their user ids can access all databases, you would have had that, anyway. I am not sure if a user can try to specify a different user to run a job.

Process Cube using SQL DTS

Hi
I have created a package to do this which simply returns with the error
'1 task(s) failed during execution'. I can process the dimensions and
cubes manually in analysis Manager fine.
SQL and analysis Manager are both on the same server and the task
appears to be running with the domain administrator user.
Cany anyone help?
Cheers
Brettos.
brettos
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message542345.html
In workflow properties, set "Execute on main package thread", including all
packages that contains OLAP processing.
James Ma
"brettos" wrote:

> Hi
> I have created a package to do this which simply returns with the error
> '1 task(s) failed during execution'. I can process the dimensions and
> cubes manually in analysis Manager fine.
> SQL and analysis Manager are both on the same server and the task
> appears to be running with the domain administrator user.
> Cany anyone help?
> Cheers
> Brettos.
>
> --
> brettos
> Posted via http://www.webservertalk.com
> View this thread: http://www.webservertalk.com/message542345.html
>
|||To help in your debuging.
Open The package in design mode.
Right Click and Select "Package Properties"
Select The "Logging" tab.
Provide a log file location to use during processing of the package.
save and close the package.
Now run the package.
Open the log file and see what details you can gleam.
report back to us.
Hope this helps.
dlr
"brettos" <brettos.1gk5p2@.mail.webservertalk.com> wrote in message
news:brettos.1gk5p2@.mail.webservertalk.com...
> Hi
> I have created a package to do this which simply returns with the error
> '1 task(s) failed during execution'. I can process the dimensions and
> cubes manually in analysis Manager fine.
> SQL and analysis Manager are both on the same server and the task
> appears to be running with the domain administrator user.
> Cany anyone help?
> Cheers
> Brettos.
>
> --
> brettos
> Posted via http://www.webservertalk.com
> View this thread: http://www.webservertalk.com/message542345.html
>

Process Cube using SQL DTS

Hi
I have created a package to do this which simply returns with the error '1 t
ask(s) failed during execution'. I can process the dimensions and cubes man
ually in analysis Manager fine.
SQL and analysis Manager are both on the same server and the task appears to
be running with the domain administrator user.
Cany anyone help?
Cheers
Brettos.In workflow properties, set "Execute on main package thread", including all
packages that contains OLAP processing.
James Ma
"brettos" wrote:

> Hi
> I have created a package to do this which simply returns with the error
> '1 task(s) failed during execution'. I can process the dimensions and
> cubes manually in analysis Manager fine.
> SQL and analysis Manager are both on the same server and the task
> appears to be running with the domain administrator user.
> Cany anyone help?
> Cheers
> Brettos.
>
> --
> brettos
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message542345.html
>|||To help in your debuging.
Open The package in design mode.
Right Click and Select "Package Properties"
Select The "Logging" tab.
Provide a log file location to use during processing of the package.
save and close the package.
Now run the package.
Open the log file and see what details you can gleam.
report back to us.
Hope this helps.
dlr
"brettos" <brettos.1gk5p2@.mail.webservertalk.com> wrote in message
news:brettos.1gk5p2@.mail.webservertalk.com...
> Hi
> I have created a package to do this which simply returns with the error
> '1 task(s) failed during execution'. I can process the dimensions and
> cubes manually in analysis Manager fine.
> SQL and analysis Manager are both on the same server and the task
> appears to be running with the domain administrator user.
> Cany anyone help?
> Cheers
> Brettos.
>
> --
> brettos
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message542345.html
>

Process Admin

Can I give a developer the ability to execute DTS pkgs without this server wide permission?If that user has underlying permission on the tables then no need to have any server-wide permissions. Refer to KBA http://support.microsoft.com/kb/269074 for more information.

Proceesing cube using analysis services processing task

Hi,

I have a dts package stored in sql server 2005 which has a analysis service processing task to process a cube stored in sql server 2005 itself.

whenever i try to execute this package it crashes the sql server management studio and in the logfile i can find the error operation has been cancelled.

When i try to process the cube directly it processes without any problem and i can browse and see the data also.

In the package i am trying to do full process of the cube.

Also the cube and the package are migrated from 2000 to 2005.

The onemore problem is after migrating the package i am unable to see the cube setting also

Please help me to solve this

Thanks in advance

Mervin

Analysis Services 2005 uses completely new management object model: AMO. The old object model: DSO used in DTS processing packages although supported, requires from you to go through additional conifugration process.

You should try to create a new SSIS 2005 package to process your AS 2005 cube.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi,

thanks you i will try this.

But if i want to keep the dts packages itself what kind of configuration process i need to add.

And if i try to create the new packages i am not clear about the processing options that are to be provided

for the refresh data and rebuild dimension structure of dts.

Regards

Mervin

|||

Full process is going to rebuild dimension stucture

Process Update will refresh data in your dimension.

Here is whitepaper talking about processing options in Analysis Services 2005: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5_asprocarch.asp

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.