Creating a snapshot for merge replication

I'm trying to test merge replication, but am unable to create the publication, the snapshot agent keeps failing. The error I get from replication monitor is "Script failed for Table <tablename>"

Our database has quite a few XML datatype columns, when I take out the xml columns the snapshot succeeds OK, but fails with the xml column.

I created a simple table containing a varchar column and xml column. I created this table in our database, and in Adventure Works. Was able to create a publication with this test table in the adventureworks database, but was unable to create publication with this table in our database. The snapshot failed with error "Script failed for table testtable". I removed the xml column on the test table in our database and was able to create the publication successfully. Creating a publication in the adventureworks database with an xml column has no problems, but does in our database.

Anyone have any ideas?

Hi|||

Hi

What is the name of the database you are creating the publication in?

Can you post a script for the xml schema collection you are trying to publish?

Thanks

|||

Hi Katie,

I suspect that the compatibility level of your database is set to <90, and you can find out whether this is the case by executing sp_dbcmptlevel with the name of your database. If the compatibility level of your database is indeed set to < 90, you can use sp_dbcmptlevel again to change it to 90 and see if the snapshot problem goes away.

Hope that helps.

-Raymond

Creating a Proxy Account

I am trying to run SSIS packages under SQL Server Agent 2005 and I keep getting a package failed error in the event viewer.

I've heard that I need to set up a proxy account. I have found the following code and need a little explanation on what all the parts mean since I am very new to this:

Use master

CREATE CREDENTIAL [MyCredential] WITH IDENTITY = 'yourdomain\myWindowAccount', secret = 'WindowLoginPassword'

Use msdb

Sp_add_proxy @.proxy_name='MyProxy', @.credential_name='MyCredential'

Sp_grant_login_to_proxy @.login_name=' devlogin', @.proxy_name='MyProxy'

Sp_grant_proxy_to_subsystem @.proxy_name='MyProxy', @.subsystem_name='SSIS'

Let's say for the sake of argument my domain is called CompanyInc and I log into windows with my name Philip_Jaques and my password is badpassw0rd. Would I modify the above code this way to create my proxy?

Use master

CREATE CREDENTIAL [MyCredential] WITH IDENTITY = 'CompanyInc\Philip_Jaques', secret = 'badpassw0rd'

Use msdb

Sp_add_proxy @.proxy_name='MyProxy', @.credential_name='MyCredential'

Sp_grant_login_to_proxy @.login_name='Philip_Jaques', @.proxy_name='MyProxy'

Sp_grant_proxy_to_subsystem @.proxy_name='MyProxy', @.subsystem_name='SSIS'

Also, when I create this proxy account where in SQL Server 2005 can I go to view it and its properties? And assuming I get the proxy account set up correctly, how do I get my current jobs to start using it so they will successfully run?

Thanks in advance for your help and advice!

I've never heard of having to create a proxy to get SqlAgent to run SSIS accounts. Sql Agent should be setup to run under a service account already, and will run SSIS packages no problem. Where did you find this information?|||

This article pretty much explains my problem:

http://support.microsoft.com/default.aspx/kb/918760

Creating a list of SQL Server Agent Job Status in RealTime

Essentially I want to replicate the functionality of the SQL Agent Job Monitor.

I am trying to find the correct system view or stored procedure to get a list of SQL Agent Jobs that are executing. Similar to what you would see when you use the SQL Agent Job Monitor to oversee SQL Agent Job execution. (I have verified that I have sysadmin permissions.)

There is a stored procedure called msdb.dbo.sp_help_jobactivity however it still does not return information on currently running jobs. There is a system table called 'sysjobactivity' that contains job information but not the runtime status of jobs. In the BOL there is a detailed list of all of the tables that SQL Server Agent uses...but none of them seem to give runtime information.

Do I need to join the sysjobactivity table to some other process table to get realtime status?

...cordell...

This should get you started:
http://www.microsoft.com/technet/abouttn/flash/tips/tips_060804.mspx

...except that the author is wrong. You can't run sp_help_job into a table because it calls a nested stored proc.

This, however, should actually work:

declare @.job_owner_name varchar(100)
set @.job_owner_name = '' -- insert job owner name here, required for extended stored proc

if OBJECT_ID('tempdb..#xp_results') is not null
drop table #xp_results

CREATE TABLE #xp_results
(job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL,
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL,
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)

insert into #xp_results
exec master.dbo.xp_sqlagent_enum_jobs 1, @.job_owner_name

select name, * from #xp_results rj
inner join msdb.dbo.sysjobs sj
on sj.job_id = rj.job_id
where rj.running = 1

Creating a list of SQL Server Agent Job Status in RealTime

Essentially I want to replicate the functionality of the SQL Agent Job Monitor.

I am trying to find the correct system view or stored procedure to get a list of SQL Agent Jobs that are executing. Similar to what you would see when you use the SQL Agent Job Monitor to oversee SQL Agent Job execution. (I have verified that I have sysadmin permissions.)

There is a stored procedure called msdb.dbo.sp_help_jobactivity however it still does not return information on currently running jobs. There is a system table called 'sysjobactivity' that contains job information but not the runtime status of jobs. In the BOL there is a detailed list of all of the tables that SQL Server Agent uses...but none of them seem to give runtime information.

Do I need to join the sysjobactivity table to some other process table to get realtime status?

...cordell...

This should get you started:
http://www.microsoft.com/technet/abouttn/flash/tips/tips_060804.mspx

...except that the author is wrong. You can't run sp_help_job into a table because it calls a nested stored proc.

This, however, should actually work:

declare @.job_owner_name varchar(100)
set @.job_owner_name = '' -- insert job owner name here, required for extended stored proc

if OBJECT_ID('tempdb..#xp_results') is not null
drop table #xp_results

CREATE TABLE #xp_results
(job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL,
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL,
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)

insert into #xp_results
exec master.dbo.xp_sqlagent_enum_jobs 1, @.job_owner_name

select name, * from #xp_results rj
inner join msdb.dbo.sysjobs sj
on sj.job_id = rj.job_id
where rj.running = 1

CreateSubscription - Specify the Job Name or Get the GUID Job Name

We're using the CreateSubscription method to schedule a report for automatic delivery, using SQL Server Agent. Everything is working great. But, we had a question (or two).

Is there anyway to specify the name of the SQL Server Agent job that gets created when the CreateSubscription method is called? If so, how?
If not, is there anyway to get the GUID job name back after calling CreateSubscription?

TIA

There is no way to set the name of the SQL Agent job via the CreateSubscription method.

You can use the ListSubscriptions to get the guid and GetSubscriptionProperties to get further information.

ReportingService2005 rs = new ReportingService2005();
Subscription[] subscription = rs.ListSubscriptions(ReportAndPath, UserName);

rs.GetSubscriptionProperties(

subscription[0].SubscriptionID,

out actualExtensionSettings,

out actualDescription,

out actualActive,

out actualStatus,

out actualEventType,

out actualMatchData,

out actualParameters);

|||

Brad,

Thanks for the info. However, does using the ListSubscriptions and GetSubscriptionProperties give me the GUID name of the SQL Server Agent job?

Since there is no way to set the SQL Server Agent job to something more meaningful to end-users, the next best option for us is to provide the GUID name of the SQL Server Agent job to the end-user after is has been created. Thus, our ASP.NET app. will display to the end-user something like, "Your job has been created. The job name is xxxx." (where xxxx is the GUID job name as shown in the SQL Server list of jobs). In our situation, our end-users are 'knowledgeable' enough about our product to open SQL Server, navigate to SQL Server Agent, and find the job they just tried to create. So, we need to be able to give them some help with which job name is theirs.

Thanks.

|||

No. The guid for the SQL Agent job is not exposed through the SOAP Api's.

Why not just give them the subscription information? Instead of trying to tell them the SQL Agent Job, tell them the subscription name. "Your subscription has been created. The subscription is on "ReportX" for user "Foo" and is scheduled to send at "Time Selected". In Management Studio or Report Manager, more information is stored for the Subcription in RS then for the Job in the Agent. They can get parameter values, security info, and other information.

Just a thought.