putting mdx query in sql job and emailing the results?

Is this possible to email w/ just using the job script window, as long as working with 2D and 3D queries?

I want to know the dates of data in the cube ie:

WITH MEMBER Min1 as

Head(Exists([Date].[Date].[Date].Members, , 'Internet Sales'))(0).MemberValue

MEMBER Max1 as

Tail(Exists([Date].[Date].[Date].Members, , 'Internet Sales'))(0).MemberValue

SELECT {Min1, Max1} on COLUMNS

FROM [Adventure Works]

In the past, I've used xp_sendmail to send out query results. Of course, this requires a SQL query to be performed. One option here would be to perform the MDX query through a SQL OPENROWSET function call. This KB article is a little dated but shows you the basics: http://support.microsoft.com/kb/218592.

So, I have to ask, where are the dates in the OLAP cube coming from? Are these coming from a relational database? If so, could you more easily just query that database?

B.

|||I could query the sql data. but I wanted to know without a doubt that the data is in the cube. I can more easily look at my email than remote in to connect to the cube.

I'll first try openrowset().
Thanks!

purging old tranlogs

hi !
I have a job that creates tranlog backups every 15 mins and makes a full database backup at midnight.
I want to purge the old logs after the full backup , how can I do that ?
thanks
SamiHave you considered instead using the Database Maintenance Wizard? You can suppy a value to specify how long to retain both full backups and transaction log backups. It won't precisely meet your requirements since unneeded transaction logs will be retained even after a full backup, but it is simple to create, easy to maintain and easy for anyone to understand (since the database maintenance wizard is pretty well documented in MS texts).

I generally create two maintenance plans: one for system databases (where transaction logs do not need to be backed up) and one for user databases (where transaction logs are backed up).

Regards,

hmscott

hi !
I have a job that creates tranlog backups every 15 mins and makes a full database backup at midnight.
I want to purge the old logs after the full backup , how can I do that ?

thanks
Sami|||The maintenance wizard simply creates a job the class the xp_sqlmaint procedure. Frankly, you are better off bypassing the wizard and creating a the job yourself. You can look up all the parameters available (including purging old files) in Books Online under "sqlmaint Utility".

Pulling data from an excel sheet

I need to pull data from an excel sheet and load the data into a SQL table. This job should be done as a part of a SP. Because there is a lot of SQL code that needs to execute right after the file is loaded. We get like 20 of these files everymonth. I created a job and executed it using xp_cmdshell and DTSRun from my SP. But then the user needs be in sysadmin role or the account under which MSSQLServer runs should be a part of local administrators group. Both are not a very good from a security standpoint. So what are my options? What if I use opendatasource ?? I would doing some dyanmic SQL, but looks like there is no other option. Looking for ideas.

Your time and thoughts are greatly appreciated.Why not use dts and execute the sp within dts after the file has been loaded ?|||Or within the sp you can use sp_start_job.|||How he could load the information to table. Sorry Im not familiar with dts or sp, but are those kind of ready-made scripts?|||jeremas - When creating a dts package, you can either use a wizard or create one from scratch. When using the wizard, basically you select a source, destination and any transformations. The source is the file (in this case) - the destination is the database where you want the source loaded - the transformations allow you to map the columns in your source file to columns in your table ... also allowing you to manipulate the data as well before it is stored in the table.

Pull Subscription for transactional replication

I have setup a pull subscription on the subscriber for transactional replication. The distribution job continues to run, but doesn't do anything because it says: The initial snapshot for publication 'man4' is not yet available.

How can I tell if the snapshot is running, and can I see the snapshot where it was created?The snapshot is set up on the publisher and must be created before any subscriptions are set up.

Publications with Pending Status

I am receiving the following errors in the event viewer:
Application log
SubSystem Message - Job 'BOS01-253-USICOAL-1'
(0x5E7E64490BAF924BB783E4C4F54C3E19), step 2 - SQL Server Agent could not
access the replication agent. Use the DCOMCNFG utility to confirm that the
SQL Server Agent Windows account has permissions to launch the replication
agent.
system log
Access denied attempting to launch a DCOM Server. The server is:
{08B0B2D9-3FB3-11D3-A4DE-00C04F610189}
The user is SYSTEM/NT AUTHORITY, SID=S-1-5-18.
I am running windows 2003 Server with XP Embedded clients, replication is
configured as push jobs from the distributer.
are you using remote agent activation?
If not, run DCOMCNFG to determine exactly where your merge agent is running.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:88C3A06B-CA0A-41D3-9100-4DD33BB79CA5@.microsoft.com...
>I am receiving the following errors in the event viewer:
> Application log
> SubSystem Message - Job 'BOS01-253-USICOAL-1'
> (0x5E7E64490BAF924BB783E4C4F54C3E19), step 2 - SQL Server Agent could not
> access the replication agent. Use the DCOMCNFG utility to confirm that the
> SQL Server Agent Windows account has permissions to launch the replication
> agent.
> system log
> Access denied attempting to launch a DCOM Server. The server is:
> {08B0B2D9-3FB3-11D3-A4DE-00C04F610189}
> The user is SYSTEM/NT AUTHORITY, SID=S-1-5-18.
> I am running windows 2003 Server with XP Embedded clients, replication is
> configured as push jobs from the distributer.
|||I don't thinks so, where do I check?
I have used DCOMCNFG and the merge agent is running locally.
Thanks
Mark
"Hilary Cotter" wrote:

> are you using remote agent activation?
> If not, run DCOMCNFG to determine exactly where your merge agent is running.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:88C3A06B-CA0A-41D3-9100-4DD33BB79CA5@.microsoft.com...
>
>
|||Not sure if we are using remote agent activation, where do I check?
I have checked in DCOMCNFG and the merge agent is running locally.
thanks
Mark
"Hilary Cotter" wrote:

> are you using remote agent activation?
> If not, run DCOMCNFG to determine exactly where your merge agent is running.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:88C3A06B-CA0A-41D3-9100-4DD33BB79CA5@.microsoft.com...
>
>
|||script out your publication and post it here.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:B3C9C4EA-A8EC-44F1-AFA5-FBF2CB721251@.microsoft.com...[vbcol=seagreen]
> I don't thinks so, where do I check?
> I have used DCOMCNFG and the merge agent is running locally.
> Thanks
> Mark
> "Hilary Cotter" wrote:
running.[vbcol=seagreen]
not[vbcol=seagreen]
the[vbcol=seagreen]
replication[vbcol=seagreen]
is[vbcol=seagreen]
|||Also check the Security tab for Microsoft SQL Server Replication Merge Agent
8.0 Properties, and verify that for the default Launch Permissions, the
everyone group has special access.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:7B60532F-1088-4BD9-9061-8BA6A7A044A7@.microsoft.com...[vbcol=seagreen]
> Not sure if we are using remote agent activation, where do I check?
> I have checked in DCOMCNFG and the merge agent is running locally.
> thanks
> Mark
> "Hilary Cotter" wrote:
running.[vbcol=seagreen]
not[vbcol=seagreen]
the[vbcol=seagreen]
replication[vbcol=seagreen]
is[vbcol=seagreen]
|||Here is one of the publications:
-- Enabling the replication database
use master
GO
exec sp_replicationdboption @.dbname = N'USICOAL', @.optname = N'publish',
@.value = N'true'
GO
use [USICOAL]
GO
-- Adding the transactional publication
exec sp_addpublication @.publication = N'PUB_ITEM', @.restricted = N'false',
@.sync_method = N'native', @.repl_freq = N'continuous', @.description =
N'Transactional publication of item data (PLU)', @.status = N'active',
@.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous = N'false',
@.enabled_for_internet = N'false', @.independent_agent = N'false',
@.immediate_sync = N'false', @.allow_sync_tran = N'false', @.autogen_sync_procs
= N'false', @.retention = 0, @.allow_queued_tran = N'false',
@.snapshot_in_defaultfolder = N'true', @.compress_snapshot = N'false',
@.ftp_port = 21, @.ftp_login = N'anonymous', @.allow_dts = N'false',
@.allow_subscription_copy = N'false', @.add_to_active_directory = N'false',
@.logreader_job_name = N'BOS01-253-USICOAL-1'
exec sp_addpublication_snapshot @.publication = N'PUB_ITEM',@.frequency_type =
4, @.frequency_interval = 1, @.frequency_relative_interval = 1,
@.frequency_recurrence_factor = 0, @.frequency_subday = 8,
@.frequency_subday_interval = 1, @.active_start_date = 0, @.active_end_date = 0,
@.active_start_time_of_day = 0, @.active_end_time_of_day = 235959,
@.snapshot_job_name = N'BOS01-253-USICOAL-PUB_ITEM-3'
GO
exec sp_grant_publication_access @.publication = N'PUB_ITEM', @.login = N'admin'
GO
exec sp_grant_publication_access @.publication = N'PUB_ITEM', @.login =
N'BUILTIN\Administrators'
GO
exec sp_grant_publication_access @.publication = N'PUB_ITEM', @.login =
N'distributor_admin'
GO
exec sp_grant_publication_access @.publication = N'PUB_ITEM', @.login = N'sa'
GO
-- Adding the transactional articles
exec sp_addarticle @.publication = N'PUB_ITEM', @.article =
N'ADDITIONAL_INFO', @.source_owner = N'dbo', @.source_object =
N'ADDITIONAL_INFO', @.destination_table = N'ADDITIONAL_INFO', @.type =
N'logbased', @.creation_script = null, @.description = null, @.pre_creation_cmd
= N'drop', @.schema_option = 0x00000000000000F3, @.status = 16,
@.vertical_partition = N'false', @.ins_cmd = N'CALL sp_MSins_ADDITIONAL_INFO',
@.del_cmd = N'CALL sp_MSdel_ADDITIONAL_INFO', @.upd_cmd = N'MCALL
sp_MSupd_ADDITIONAL_INFO', @.filter = null, @.sync_object = null,
@.auto_identity_range = N'false'
GO
exec sp_addarticle @.publication = N'PUB_ITEM', @.article = N'DEPT_SELL_RULE',
@.source_owner = N'dbo', @.source_object = N'DEPT_SELL_RULE',
@.destination_table = N'DEPT_SELL_RULE', @.type = N'logbased', @.creation_script
= null, @.description = null, @.pre_creation_cmd = N'drop', @.schema_option =
0x00000000000000F3, @.status = 16, @.vertical_partition = N'false', @.ins_cmd =
N'CALL sp_MSins_DEPT_SELL_RULE', @.del_cmd = N'CALL sp_MSdel_DEPT_SELL_RULE',
@.upd_cmd = N'MCALL sp_MSupd_DEPT_SELL_RULE', @.filter = null, @.sync_object =
null, @.auto_identity_range = N'false'
GO
exec sp_addarticle @.publication = N'PUB_ITEM', @.article = N'ITEM_GROUP',
@.source_owner = N'dbo', @.source_object = N'ITEM_GROUP', @.destination_table =
N'ITEM_GROUP', @.type = N'logbased', @.creation_script = null, @.description =
null, @.pre_creation_cmd = N'drop', @.schema_option = 0x00000000000000F3,
@.status = 16, @.vertical_partition = N'false', @.ins_cmd = N'CALL
sp_MSins_ITEM_GROUP', @.del_cmd = N'CALL sp_MSdel_ITEM_GROUP', @.upd_cmd =
N'MCALL sp_MSupd_ITEM_GROUP', @.filter = null, @.sync_object = null,
@.auto_identity_range = N'false'
GO
exec sp_addarticle @.publication = N'PUB_ITEM', @.article =
N'ITEM_GROUP_ITEM', @.source_owner = N'dbo', @.source_object =
N'ITEM_GROUP_ITEM', @.destination_table = N'ITEM_GROUP_ITEM', @.type =
N'logbased', @.creation_script = null, @.description = null, @.pre_creation_cmd
= N'drop', @.schema_option = 0x00000000000000F3, @.status = 16,
@.vertical_partition = N'false', @.ins_cmd = N'CALL sp_MSins_ITEM_GROUP_ITEM',
@.del_cmd = N'CALL sp_MSdel_ITEM_GROUP_ITEM', @.upd_cmd = N'MCALL
sp_MSupd_ITEM_GROUP_ITEM', @.filter = null, @.sync_object = null,
@.auto_identity_range = N'false'
GO
exec sp_addarticle @.publication = N'PUB_ITEM', @.article =
N'ITEM_GRP_ADDL_INFO', @.source_owner = N'dbo', @.source_object =
N'ITEM_GRP_ADDL_INFO', @.destination_table = N'ITEM_GRP_ADDL_INFO', @.type =
N'logbased', @.creation_script = null, @.description = null, @.pre_creation_cmd
= N'drop', @.schema_option = 0x00000000000000F3, @.status = 16,
@.vertical_partition = N'false', @.ins_cmd = N'CALL
sp_MSins_ITEM_GRP_ADDL_INFO', @.del_cmd = N'CALL sp_MSdel_ITEM_GRP_ADDL_INFO',
@.upd_cmd = N'MCALL sp_MSupd_ITEM_GRP_ADDL_INFO', @.filter = null, @.sync_object
= null, @.auto_identity_range = N'false'
GO
exec sp_addarticle @.publication = N'PUB_ITEM', @.article = N'PLU',
@.source_owner = N'dbo', @.source_object = N'PLU', @.destination_table = N'PLU',
@.type = N'logbased', @.creation_script = null, @.description = null,
@.pre_creation_cmd = N'drop', @.schema_option = 0x00000000000000F3, @.status =
16, @.vertical_partition = N'false', @.ins_cmd = N'CALL sp_MSins_PLU', @.del_cmd
= N'CALL sp_MSdel_PLU', @.upd_cmd = N'MCALL sp_MSupd_PLU', @.filter = null,
@.sync_object = null, @.auto_identity_range = N'false'
GO
exec sp_addarticle @.publication = N'PUB_ITEM', @.article = N'PLU_PRICE',
@.source_owner = N'dbo', @.source_object = N'PLU_PRICE', @.destination_table =
N'PLU_PRICE', @.type = N'logbased', @.creation_script = null, @.description =
null, @.pre_creation_cmd = N'drop', @.schema_option = 0x00000000000000F3,
@.status = 16, @.vertical_partition = N'false', @.ins_cmd = N'CALL
sp_MSins_PLU_PRICE', @.del_cmd = N'CALL sp_MSdel_PLU_PRICE', @.upd_cmd =
N'MCALL sp_MSupd_PLU_PRICE', @.filter = null, @.sync_object = null,
@.auto_identity_range = N'false'
GO
exec sp_addarticle @.publication = N'PUB_ITEM', @.article =
N'POS_ID_PLU_CODE', @.source_owner = N'dbo', @.source_object =
N'POS_ID_PLU_CODE', @.destination_table = N'POS_ID_PLU_CODE', @.type =
N'logbased', @.creation_script = null, @.description = null, @.pre_creation_cmd
= N'drop', @.schema_option = 0x00000000000000F3, @.status = 16,
@.vertical_partition = N'false', @.ins_cmd = N'CALL sp_MSins_POS_ID_PLU_CODE',
@.del_cmd = N'CALL sp_MSdel_POS_ID_PLU_CODE', @.upd_cmd = N'MCALL
sp_MSupd_POS_ID_PLU_CODE', @.filter = null, @.sync_object = null,
@.auto_identity_range = N'false'
GO
exec sp_addarticle @.publication = N'PUB_ITEM', @.article =
N'POS_ITEM_ADDL_INFO', @.source_owner = N'dbo', @.source_object =
N'POS_ITEM_ADDL_INFO', @.destination_table = N'POS_ITEM_ADDL_INFO', @.type =
N'logbased', @.creation_script = null, @.description = null, @.pre_creation_cmd
= N'drop', @.schema_option = 0x00000000000000F3, @.status = 16,
@.vertical_partition = N'false', @.ins_cmd = N'CALL
sp_MSins_POS_ITEM_ADDL_INFO', @.del_cmd = N'CALL sp_MSdel_POS_ITEM_ADDL_INFO',
@.upd_cmd = N'MCALL sp_MSupd_POS_ITEM_ADDL_INFO', @.filter = null, @.sync_object
= null, @.auto_identity_range = N'false'
GO
-- Adding the transactional subscription
exec sp_addsubscription @.publication = N'PUB_ITEM', @.article = N'all',
@.subscriber = N'TILL01-253', @.destination_db = N'USICOAL', @.sync_type =
N'automatic', @.update_mode = N'read only', @.offloadagent = 0,
@.dts_package_location = N'distributor'
GO
-- Adding the transactional subscription
exec sp_addsubscription @.publication = N'PUB_ITEM', @.article = N'all',
@.subscriber = N'TILL02-253', @.destination_db = N'USICOAL', @.sync_type =
N'automatic', @.update_mode = N'read only', @.offloadagent = 0,
@.dts_package_location = N'distributor'
GO
-- Adding the transactional subscription
exec sp_addsubscription @.publication = N'PUB_ITEM', @.article = N'all',
@.subscriber = N'TILL03-253', @.destination_db = N'USICOAL', @.sync_type =
N'automatic', @.update_mode = N'read only', @.offloadagent = 0, @.offloadserver
= N'<NULL>', @.dts_package_location = N'distributor'
GO
-- Adding the transactional subscription
exec sp_addsubscription @.publication = N'PUB_ITEM', @.article = N'all',
@.subscriber = N'TILL04-253', @.destination_db = N'USICOAL', @.sync_type =
N'automatic', @.update_mode = N'read only', @.offloadagent = 0, @.offloadserver
= N'TILL04-253', @.dts_package_location = N'distributor'
GO
"Hilary Cotter" wrote:

> script out your publication and post it here.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:B3C9C4EA-A8EC-44F1-AFA5-FBF2CB721251@.microsoft.com...
> running.
> not
> the
> replication
> is
>
>