EXEC sp_start_job Remote Server

I am trying to create a centralized job monitoring system that can be
managed by some operators to restart a job if it fails. Everything
will be running on one system but I need to invoke remote jobs on
remote system. I am having trouble starting a job on a remote system
even if I have the permissions to do so.
[Code]
DECLARE @.SQLCMD VARCHAR(MAX)
SET @.SQLCMD = ''
DECLARE @.SystemName VARCHAR(50)
DECLARE @.JobName VARCHAR(50)
DECLARE @.StepName VARCHAR(50)
SET @.SystemName = RemoteServer
SET @.JobName = Explode
SET @.StepName = BadStep
SET @.SQLCMD = '' + @.SystemName + '.msdb.dbo.sp_start_job @.job_name = ''' + @.JobName + ''', @.step_name = ''' + @.StepName + ''''
PRINT @.SQLCMD
[/code]
This will generate the following
RemoteServer.msdb.dbo.sp_start_job @.job_name = 'Explode', @.step_name = 'Bad'
If i run this command as an adhoc command, it will execute on the
RemoteServer and start the job at the step. However for the SP i am
writing this does not work
[Code]
EXEC @.SQLCMD
EXEC msdb.dbo.sp_start_job @.job_name = 'Explode', @.step_name = 'Bad'
[/Code]
This error appers.
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'msdb.dbo.sp_start_job @.job_name = 'Explode', @.step_name = 'Bad''.
If I try to run it this way, I get the following error message.
EXEC msdb.dbo.sp_start_job @.server_name = 'dbdev4', @.job_name = 'Explode', @.step_name = 'Bad'
Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers,
Line 67
The specified @.job_name ('Explode') does not exist.
Any ideas
Thanks
-Matt-Hi Matt
"Matthew" wrote:
> I am trying to create a centralized job monitoring system that can be
> managed by some operators to restart a job if it fails. Everything
> will be running on one system but I need to invoke remote jobs on
> remote system. I am having trouble starting a job on a remote system
> even if I have the permissions to do so.
> [Code]
> DECLARE @.SQLCMD VARCHAR(MAX)
> SET @.SQLCMD = ''
> DECLARE @.SystemName VARCHAR(50)
> DECLARE @.JobName VARCHAR(50)
> DECLARE @.StepName VARCHAR(50)
> SET @.SystemName = RemoteServer
> SET @.JobName = Explode
> SET @.StepName = BadStep
> SET @.SQLCMD = '' + @.SystemName + '.msdb.dbo.sp_start_job @.job_name => ''' + @.JobName + ''', @.step_name = ''' + @.StepName + ''''
> PRINT @.SQLCMD
> [/code]
> This will generate the following
> RemoteServer.msdb.dbo.sp_start_job @.job_name = 'Explode', @.step_name => 'Bad'
> If i run this command as an adhoc command, it will execute on the
> RemoteServer and start the job at the step. However for the SP i am
> writing this does not work
> [Code]
> EXEC @.SQLCMD
> EXEC msdb.dbo.sp_start_job @.job_name = 'Explode', @.step_name = 'Bad'
> [/Code]
> This error appers.
> Msg 2812, Level 16, State 62, Line 1
> Could not find stored procedure 'msdb.dbo.sp_start_job @.job_name => 'Explode', @.step_name = 'Bad''.
Try:
EXEC ( @.SQLCMD )
or
EXEC RemoteServer.msdb.dbo.sp_start_job @.job_name = 'Explode', @.step_name ='Bad'
> If I try to run it this way, I get the following error message.
> EXEC msdb.dbo.sp_start_job @.server_name = 'dbdev4', @.job_name => 'Explode', @.step_name = 'Bad'
> Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers,
> Line 67
> The specified @.job_name ('Explode') does not exist.
> Any ideas
> Thanks
> -Matt-
John

exclusive lock

(pls correct if u feel anything here is incorrect)
i know in SQL2000, locks are well managed internally, and many DML/DDL
statements will auto use lock.
just want to know, why and how a DBA will need to exclusively use lock,
under what kind of situations ?
tks for sharing.you may change locking behaviour by changing isolation levels for the
connection
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE (others doc'd in books on
line)
You may also change locking behaviour for a single table in a single
transaction using lock hints ie
Select * from titles with ( nolock)
In my experience the most common two reasons for adjusting locking is
1. to increase locking so that no-one can touch the data until you are
through with it... Perhaps you are doing some kind of adjustments to the
money fields of many rows ( like cost allocation)... Any changes made by
others during your process will skew your work and make it inaccurate...
Therefore you may choose to increase the locking level to serializable so
no-one else can touch the data until you are through..
Another similar case is when you are doing a select ( but intend to
update later) and do not wish the values to change, you might select with
holdlock.
2. the second reason is that you wish to read some data and NOT be blocked
by updaters. You may choose to ignore exclusive locks and read dirty data.
The select above will do that..
Hope this helps.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"pk" <pk@.> wrote in message news:utAG1ewEEHA.2768@.tk2msftngp13.phx.gbl...
> (pls correct if u feel anything here is incorrect)
> i know in SQL2000, locks are well managed internally, and many DML/DDL
> statements will auto use lock.
> just want to know, why and how a DBA will need to exclusively use lock,
> under what kind of situations ?
> tks for sharing.
>

Exception error SQL_NO_DATA when data on server changed

Hello,
I finally managed to track the problem. If one program issues a
transaction which changes a record and a second program already defined
a recordset including this record, then the second program will receive
SQL_NO_DATA error when moving to this record.
The problem appeared with ODBC connection to SQL Server and to MySQL.
The following piece of code demonstrates the problem:
{
srand(time(NULL));
CDatabase db1, db2;
db1.Open("DBTEST");
db2.Open("DBTEST");
CTblTest rs1(&db1), rs2(&db2);
rs1.m_strFilter = "ID=3";
rs2.m_strFilter = "ID>=2 AND ID<5";
rs2.m_strSort = "ID";
db1.BeginTrans();
rs2.Open();
rs1.Open();
rs1.Edit();
rs1.m_TEXT = (char)(rand() % 26 + 'A');
rs1.SetFieldDirty(&rs1.m_BIN);
rs1.Update();
db1.CommitTrans();
while(!rs2.IsEOF())
{
OutputDebugString(rs2.m_TEXT + "\r\n");
try
{
rs2.MoveNext();
}
catch(CDBException *e)
{
char err[512];
CString msg;
err[0] = '\0';
e->GetErrorMessage(err, sizeof(err));
msg.Format("Exception error (%d) %s, %s, %s\r\n",
e->m_nRetCode, err, e->m_strError, e->m_strStateNativeOrigin);
e->Delete();
OutputDebugString(msg);
}
}
rs1.Close();
rs2.Close();
OutputDebugString("Done\r\n");
}
Record 2 is shown
Exception 100 for record 3
Record 4 is shown
So, it seems all I need to do is move to the next record and ignore the
error. What do you think?
Thanks, Reuven
Thanks for your followup Reuven,
I think this should be the case, and the behavior is likely due to the
SQLServer or ODBC driver's internal implementation for such concurrent
condition... since the recordset you opened hold a live connection and at
the sametime another program connection is manipulating the same
table/records, the recordset return NO_DATA for that record... If it
dosn't broke your program, you can just catch the SQL_NO_DATA and continue
as you mentioned...
Thanks,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
| Date: Wed, 18 Jan 2006 23:08:05 +0200
| From: Reuven Nisser <rnisser@.newsgroup.nospam>
| User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923)
| X-Accept-Language: en-us, en
| MIME-Version: 1.0
| Subject: Exception error SQL_NO_DATA when data on server changed
| References: <43C24382.9050505@.newsgroup.nospam>
<9jWc#HbFGHA.3696@.TK2MSFTNGXA02.phx.gbl>
<uN8YSWjFGHA.376@.TK2MSFTNGP12.phx.gbl>
<TqKcHPqFGHA.3696@.TK2MSFTNGXA02.phx.gbl>
<#r9xOl#FGHA.3936@.TK2MSFTNGP12.phx.gbl>
<E7L1jknGGHA.3680@.TK2MSFTNGXA02.phx.gbl>
| In-Reply-To: <E7L1jknGGHA.3680@.TK2MSFTNGXA02.phx.gbl>
| Content-Type: text/plain; charset=ISO-8859-8-I; format=flowed
| Content-Transfer-Encoding: 7bit
| Message-ID: <eMbcLNHHGHA.1032@.TK2MSFTNGP11.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.odbc
| NNTP-Posting-Host: cbl217-132-80-84.bb.netvision.net.il 217.132.80.84
| Lines: 1
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP11.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.odbc:44651
| X-Tomcat-NG: microsoft.public.sqlserver.odbc
|
| Hello,
| I finally managed to track the problem. If one program issues a
| transaction which changes a record and a second program already defined
| a recordset including this record, then the second program will receive
| SQL_NO_DATA error when moving to this record.
| The problem appeared with ODBC connection to SQL Server and to MySQL.
| The following piece of code demonstrates the problem:
|
| {
| srand(time(NULL));
| CDatabase db1, db2;
| db1.Open("DBTEST");
| db2.Open("DBTEST");
|
| CTblTest rs1(&db1), rs2(&db2);
| rs1.m_strFilter = "ID=3";
| rs2.m_strFilter = "ID>=2 AND ID<5";
| rs2.m_strSort = "ID";
|
| db1.BeginTrans();
| rs2.Open();
|
| rs1.Open();
| rs1.Edit();
| rs1.m_TEXT = (char)(rand() % 26 + 'A');
| rs1.SetFieldDirty(&rs1.m_BIN);
| rs1.Update();
|
| db1.CommitTrans();
|
| while(!rs2.IsEOF())
| {
| OutputDebugString(rs2.m_TEXT + "\r\n");
| try
| {
| rs2.MoveNext();
| }
| catch(CDBException *e)
| {
| char err[512];
| CString msg;
| err[0] = '\0';
| e->GetErrorMessage(err, sizeof(err));
| msg.Format("Exception error (%d) %s, %s, %s\r\n",
| e->m_nRetCode, err, e->m_strError, e->m_strStateNativeOrigin);
| e->Delete();
| OutputDebugString(msg);
| }
| }
|
| rs1.Close();
| rs2.Close();
| OutputDebugString("Done\r\n");
| }
|
|
| Record 2 is shown
| Exception 100 for record 3
| Record 4 is shown
|
| So, it seems all I need to do is move to the next record and ignore the
| error. What do you think?
|
| Thanks, Reuven
|

Exception error SQL_NO_DATA when data on server changed

Hello,
I finally managed to track the problem. If one program issues a
transaction which changes a record and a second program already defined
a recordset including this record, then the second program will receive
SQL_NO_DATA error when moving to this record.
The problem appeared with ODBC connection to SQL Server and to MySQL.
The following piece of code demonstrates the problem:
{
srand(time(NULL));
CDatabase db1, db2;
db1.Open("DBTEST");
db2.Open("DBTEST");
CTblTest rs1(&db1), rs2(&db2);
rs1.m_strFilter = "ID=3";
rs2.m_strFilter = "ID>=2 AND ID<5";
rs2.m_strSort = "ID";
db1.BeginTrans();
rs2.Open();
rs1.Open();
rs1.Edit();
rs1.m_TEXT = (char)(rand() % 26 + 'A');
rs1.SetFieldDirty(&rs1.m_BIN);
rs1.Update();
db1.CommitTrans();
while(!rs2.IsEOF())
{
OutputDebugString(rs2.m_TEXT + "\r\n");
try
{
rs2.MoveNext();
}
catch(CDBException *e)
{
char err[512];
CString msg;
err[0] = '\0';
e->GetErrorMessage(err, sizeof(err));
msg.Format("Exception error (%d) %s, %s, %s\r\n",
e->m_nRetCode, err, e->m_strError, e->m_strStateNativeOrigin);
e->Delete();
OutputDebugString(msg);
}
}
rs1.Close();
rs2.Close();
OutputDebugString("Done\r\n");
}
Record 2 is shown
Exception 100 for record 3
Record 4 is shown
So, it seems all I need to do is move to the next record and ignore the
error. What do you think?
Thanks, ReuvenThanks for your followup Reuven,
I think this should be the case, and the behavior is likely due to the
SQLServer or ODBC driver's internal implementation for such concurrent
condition... since the recordset you opened hold a live connection and at
the sametime another program connection is manipulating the same
table/records, the recordset return NO_DATA for that record... If it
dosn't broke your program, you can just catch the SQL_NO_DATA and continue
as you mentioned...
Thanks,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
| Date: Wed, 18 Jan 2006 23:08:05 +0200
| From: Reuven Nisser <rnisser@.newsgroup.nospam>
| User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923)
| X-Accept-Language: en-us, en
| MIME-Version: 1.0
| Subject: Exception error SQL_NO_DATA when data on server changed
| References: <43C24382.9050505@.newsgroup.nospam>
<9jWc#HbFGHA.3696@.TK2MSFTNGXA02.phx.gbl>
<uN8YSWjFGHA.376@.TK2MSFTNGP12.phx.gbl>
<TqKcHPqFGHA.3696@.TK2MSFTNGXA02.phx.gbl>
<#r9xOl#FGHA.3936@.TK2MSFTNGP12.phx.gbl>
<E7L1jknGGHA.3680@.TK2MSFTNGXA02.phx.gbl>
| In-Reply-To: <E7L1jknGGHA.3680@.TK2MSFTNGXA02.phx.gbl>
| Content-Type: text/plain; charset=ISO-8859-8-I; format=flowed
| Content-Transfer-Encoding: 7bit
| Message-ID: <eMbcLNHHGHA.1032@.TK2MSFTNGP11.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.odbc
| NNTP-Posting-Host: cbl217-132-80-84.bb.netvision.net.il 217.132.80.84
| Lines: 1
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.odbc:44651
| X-Tomcat-NG: microsoft.public.sqlserver.odbc
|
| Hello,
| I finally managed to track the problem. If one program issues a
| transaction which changes a record and a second program already defined
| a recordset including this record, then the second program will receive
| SQL_NO_DATA error when moving to this record.
| The problem appeared with ODBC connection to SQL Server and to MySQL.
| The following piece of code demonstrates the problem:
|
| {
| srand(time(NULL));
| CDatabase db1, db2;
| db1.Open("DBTEST");
| db2.Open("DBTEST");
|
| CTblTest rs1(&db1), rs2(&db2);
| rs1.m_strFilter = "ID=3";
| rs2.m_strFilter = "ID>=2 AND ID<5";
| rs2.m_strSort = "ID";
|
| db1.BeginTrans();
| rs2.Open();
|
| rs1.Open();
| rs1.Edit();
| rs1.m_TEXT = (char)(rand() % 26 + 'A');
| rs1.SetFieldDirty(&rs1.m_BIN);
| rs1.Update();
|
| db1.CommitTrans();
|
| while(!rs2.IsEOF())
| {
| OutputDebugString(rs2.m_TEXT + "\r\n");
| try
| {
| rs2.MoveNext();
| }
| catch(CDBException *e)
| {
| char err[512];
| CString msg;
| err[0] = '\0';
| e->GetErrorMessage(err, sizeof(err));
| msg.Format("Exception error (%d) %s, %s, %s\r\n",
| e->m_nRetCode, err, e->m_strError, e->m_strStateNativeOrigin);
| e->Delete();
| OutputDebugString(msg);
| }
| }
|
| rs1.Close();
| rs2.Close();
| OutputDebugString("Done\r\n");
| }
|
|
| Record 2 is shown
| Exception 100 for record 3
| Record 4 is shown
|
| So, it seems all I need to do is move to the next record and ignore the
| error. What do you think?
|
| Thanks, Reuven
|

Excel Missing Rows (Managed NewsGroup Post)

Hello,
I am posting this through the MSDN Managed NewsGroup section. We are
experiencing the following problem:
When programatically exporting MS Reporting Services Reports to Excel - the
last row turns up missing. This does not happen 100% of the time but enough
that we cannot reliably send out to customers.
If you have never seen this happen I can provide you with a zip file with
sample data and the report. The report is a very simple column style report
that uses a stored procedure.
We have checked the stored procedure and it is returning the appropriate
data. When viewing in HTML all of the rows show up - it is only when the
report is exported to Excel that the last row comes up missing.Microsoft,
Please contact me and I will forward the report and the dataset to reproduce
the prolem.
"chanley54" wrote:
> Hello,
> I am posting this through the MSDN Managed NewsGroup section. We are
> experiencing the following problem:
> When programatically exporting MS Reporting Services Reports to Excel - the
> last row turns up missing. This does not happen 100% of the time but enough
> that we cannot reliably send out to customers.
> If you have never seen this happen I can provide you with a zip file with
> sample data and the report. The report is a very simple column style report
> that uses a stored procedure.
> We have checked the stored procedure and it is returning the appropriate
> data. When viewing in HTML all of the rows show up - it is only when the
> report is exported to Excel that the last row comes up missing.