Running the exec command on the second machine...

I have a query to execute. The query is :-

exec master.dbo.xp_fileexist @.nFilePath

This i can execute on the current machine.

But how do i run this one on another machine.
I took the machinename as a parameter and executed like :-

exec @.nMachinename.master.dbo.xp_fileexist @.nFilePath

But the exec command is not taking this...

Giving me errors.

Now how can i give the second machines name as a paramter and run dynamically??

Thanks,
Sandu.not sure about this but try using linked server.|||Okies..

I got it...

I have to set the value into a variable of the above mentioned query and then execute the variable...

Anyway thanks...

--Sandu.|||try

declare @.sql nvarchar(1000)
select @.sql = 'exec ' + @.nMachinename + '.master.dbo.xp_fileexist ''' + @.nFilePath + ''''
exec (@.sql)

Running sql jobs

Hi,
I have 2 sql jobs (sql2k sp4). I need to exec from sql query analyzer..
USE msdb
go
EXEC sp_start_job @.job_id = 'FD75FF33-AC18-42DB-A90B-94F6B14A9EA7'
exec sp_start_job @.job_id = 'FF75FF33-AC18-42DB-A90B-94F6B14A9EA7'
I need the first one finish processing then start the second job.
Any ideas?
ThanksYou could create just one job with 2 steps and when the first steps
finished goto the second step.
Charles Deaton
www.SQLSIG.org
mecn wrote:
> Hi,
> I have 2 sql jobs (sql2k sp4). I need to exec from sql query analyzer..
> USE msdb
> go
> EXEC sp_start_job @.job_id = 'FD75FF33-AC18-42DB-A90B-94F6B14A9EA7'
> exec sp_start_job @.job_id = 'FF75FF33-AC18-42DB-A90B-94F6B14A9EA7'
> I need the first one finish processing then start the second job.
> Any ideas?
> Thanks|||I could not make it one job. Business reasons.
Thanks
"Charles Deaton" <sqldba@.comcast.net> wrote in message
news:1163017637.649285.240680@.m73g2000cwd.googlegroups.com...
> You could create just one job with 2 steps and when the first steps
> finished goto the second step.
> Charles Deaton
> www.SQLSIG.org
> mecn wrote:
>|||Create a third job, and set these two jobs as contingent steps.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"mecn" <mecn2002@.yahoo.com> wrote in message
news:uYaaCA3AHHA.996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have 2 sql jobs (sql2k sp4). I need to exec from sql query analyzer..
> USE msdb
> go
> EXEC sp_start_job @.job_id = 'FD75FF33-AC18-42DB-A90B-94F6B14A9EA7'
> exec sp_start_job @.job_id = 'FF75FF33-AC18-42DB-A90B-94F6B14A9EA7'
> I need the first one finish processing then start the second job.
> Any ideas?
> Thanks
>|||I guess you could check for the execution status of the job, in a loop, usin
g sp_help_job.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mecn" <mecn2002@.yahoo.com> wrote in message news:uYaaCA3AHHA.996@.TK2MSFTNGP02.phx.gbl...[vb
col=seagreen]
> Hi,
> I have 2 sql jobs (sql2k sp4). I need to exec from sql query analyzer..
> USE msdb
> go
> EXEC sp_start_job @.job_id = 'FD75FF33-AC18-42DB-A90B-94F6B14A9EA7'
> exec sp_start_job @.job_id = 'FF75FF33-AC18-42DB-A90B-94F6B14A9EA7'
> I need the first one finish processing then start the second job.
> Any ideas?
> Thanks
>[/vbcol]|||thanks all, I'll do the test
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OLcLwd3AHHA.4428@.TK2MSFTNGP04.phx.gbl...[vbcol=seagreen]
>I guess you could check for the execution status of the job, in a loop,
>using sp_help_job.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:uYaaCA3AHHA.996@.TK2MSFTNGP02.phx.gbl...

Running sql jobs

Hi,
I have 2 sql jobs (sql2k sp4). I need to exec from sql query analyzer..
USE msdb
go
EXEC sp_start_job @.job_id = 'FD75FF33-AC18-42DB-A90B-94F6B14A9EA7'
exec sp_start_job @.job_id = 'FF75FF33-AC18-42DB-A90B-94F6B14A9EA7'
I need the first one finish processing then start the second job.
Any ideas?
Thanks
You could create just one job with 2 steps and when the first steps
finished goto the second step.
Charles Deaton
www.SQLSIG.org
mecn wrote:
> Hi,
> I have 2 sql jobs (sql2k sp4). I need to exec from sql query analyzer..
> USE msdb
> go
> EXEC sp_start_job @.job_id = 'FD75FF33-AC18-42DB-A90B-94F6B14A9EA7'
> exec sp_start_job @.job_id = 'FF75FF33-AC18-42DB-A90B-94F6B14A9EA7'
> I need the first one finish processing then start the second job.
> Any ideas?
> Thanks
|||I could not make it one job. Business reasons.
Thanks
"Charles Deaton" <sqldba@.comcast.net> wrote in message
news:1163017637.649285.240680@.m73g2000cwd.googlegr oups.com...
> You could create just one job with 2 steps and when the first steps
> finished goto the second step.
> Charles Deaton
> www.SQLSIG.org
> mecn wrote:
>
|||Create a third job, and set these two jobs as contingent steps.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"mecn" <mecn2002@.yahoo.com> wrote in message
news:uYaaCA3AHHA.996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have 2 sql jobs (sql2k sp4). I need to exec from sql query analyzer..
> USE msdb
> go
> EXEC sp_start_job @.job_id = 'FD75FF33-AC18-42DB-A90B-94F6B14A9EA7'
> exec sp_start_job @.job_id = 'FF75FF33-AC18-42DB-A90B-94F6B14A9EA7'
> I need the first one finish processing then start the second job.
> Any ideas?
> Thanks
>
|||thanks all, I'll do the test
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OLcLwd3AHHA.4428@.TK2MSFTNGP04.phx.gbl...[vbcol=seagreen]
>I guess you could check for the execution status of the job, in a loop,
>using sp_help_job.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:uYaaCA3AHHA.996@.TK2MSFTNGP02.phx.gbl...

Running sql jobs

Hi,
I have 2 sql jobs (sql2k sp4). I need to exec from sql query analyzer..
USE msdb
go
EXEC sp_start_job @.job_id = 'FD75FF33-AC18-42DB-A90B-94F6B14A9EA7'
exec sp_start_job @.job_id = 'FF75FF33-AC18-42DB-A90B-94F6B14A9EA7'
I need the first one finish processing then start the second job.
Any ideas?
ThanksYou could create just one job with 2 steps and when the first steps
finished goto the second step.
Charles Deaton
www.SQLSIG.org
mecn wrote:
> Hi,
> I have 2 sql jobs (sql2k sp4). I need to exec from sql query analyzer..
> USE msdb
> go
> EXEC sp_start_job @.job_id = 'FD75FF33-AC18-42DB-A90B-94F6B14A9EA7'
> exec sp_start_job @.job_id = 'FF75FF33-AC18-42DB-A90B-94F6B14A9EA7'
> I need the first one finish processing then start the second job.
> Any ideas?
> Thanks|||Create a third job, and set these two jobs as contingent steps.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"mecn" <mecn2002@.yahoo.com> wrote in message
news:uYaaCA3AHHA.996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have 2 sql jobs (sql2k sp4). I need to exec from sql query analyzer..
> USE msdb
> go
> EXEC sp_start_job @.job_id = 'FD75FF33-AC18-42DB-A90B-94F6B14A9EA7'
> exec sp_start_job @.job_id = 'FF75FF33-AC18-42DB-A90B-94F6B14A9EA7'
> I need the first one finish processing then start the second job.
> Any ideas?
> Thanks
>|||I could not make it one job. Business reasons.
Thanks
"Charles Deaton" <sqldba@.comcast.net> wrote in message
news:1163017637.649285.240680@.m73g2000cwd.googlegroups.com...
> You could create just one job with 2 steps and when the first steps
> finished goto the second step.
> Charles Deaton
> www.SQLSIG.org
> mecn wrote:
>> Hi,
>> I have 2 sql jobs (sql2k sp4). I need to exec from sql query analyzer..
>> USE msdb
>> go
>> EXEC sp_start_job @.job_id = 'FD75FF33-AC18-42DB-A90B-94F6B14A9EA7'
>> exec sp_start_job @.job_id = 'FF75FF33-AC18-42DB-A90B-94F6B14A9EA7'
>> I need the first one finish processing then start the second job.
>> Any ideas?
>> Thanks
>|||I guess you could check for the execution status of the job, in a loop, using sp_help_job.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mecn" <mecn2002@.yahoo.com> wrote in message news:uYaaCA3AHHA.996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have 2 sql jobs (sql2k sp4). I need to exec from sql query analyzer..
> USE msdb
> go
> EXEC sp_start_job @.job_id = 'FD75FF33-AC18-42DB-A90B-94F6B14A9EA7'
> exec sp_start_job @.job_id = 'FF75FF33-AC18-42DB-A90B-94F6B14A9EA7'
> I need the first one finish processing then start the second job.
> Any ideas?
> Thanks
>|||thanks all, I'll do the test
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OLcLwd3AHHA.4428@.TK2MSFTNGP04.phx.gbl...
>I guess you could check for the execution status of the job, in a loop,
>using sp_help_job.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:uYaaCA3AHHA.996@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I have 2 sql jobs (sql2k sp4). I need to exec from sql query analyzer..
>> USE msdb
>> go
>> EXEC sp_start_job @.job_id = 'FD75FF33-AC18-42DB-A90B-94F6B14A9EA7'
>> exec sp_start_job @.job_id = 'FF75FF33-AC18-42DB-A90B-94F6B14A9EA7'
>> I need the first one finish processing then start the second job.
>> Any ideas?
>> Thanks

Running SP versus SQL Statements

Folks:

I need some help with this. I have a stored procedure which if I run as :

exec test_sptma34 @.asofdate = '10/31/2007', @.analysisid = 'TVE-2004-10'

would NOT Complete at all and goes in a loop looks like (waited for 12 mins and then cancelled). Whereas If I run the same statements which are in SP then I get around 41 rows and that too within 1 to 2 seconds. Am I missing anything here?

STORED PROCEDURE:
==================
Code: ( sql )

CREATE PROCEDURE test_spTMA34(@.asofdate DATETIME, @.analysisid VARCHAR(50))AS SET NOCOUNT ON BEGIN declare @.ShiftData TABLE([BP Shift] float, [Elasticity] float, [Convexity] float) INSERT INTO @.ShiftData ([BP Shift], Elasticity, Convexity)SELECT cast(substring(D1.datatype, 12, len(D1.datatype)-13) AS float) AS '[BP Shift]', SUM(S.Weight*D1.value) AS Elasticity, SUM(S.Weight*D2.Value) AS ConvexityFROM tblData D1JOIN tblData D2 ON D1.asofdate = D2.asofdate AND D1.ticker = D2.ticker AND RIGHT(D1.datatype,LEN(D1.DataType) - 10) = RIGHT(D2.datatype, LEN(D2.DataType) - 9)JOIN tblPortfolio P ON P.ticker = D1.tickerJOIN tblSBMIWeights S ON S.ticker = D1.tickerWHERE P.portfolio = 'SBMI-TBA' AND P.asofdate = (SELECT MAX(asofdate) FROM tblPortfolio WHERE portfolio = 'SBMI-TBA' AND asofdate <= @.asofdate)AND S.asofdate = (SELECT MAX(asofdate) FROM tblSBMIWeights WHERE asofdate <=@.asofdate)AND D1.datatype LIKE 'Elasticity@.%' AND D1.DataSource = @.analysisidAND D2.datatype LIKE 'Convexity@.%' AND D2.DataSource = @.analysisidAND D1.asofdate = @.asofdateGROUP BY D1.datatype, D2.datatype INSERT INTO @.ShiftData ([BP Shift], Elasticity, Convexity)SELECT 0 AS '[BP Shift]', SUM(S.Weight*D1.value) AS Elasticity, SUM(S.Weight*D2.Value) AS ConvexityFROM tblData D1JOIN tblData D2 ON D1.asofdate = D2.asofdate AND D1.ticker = D2.ticker AND RIGHT(D1.datatype,LEN(D1.DataType) - 10) = RIGHT(D2.datatype, LEN(D2.DataType) - 9)JOIN tblPortfolio P ON P.ticker = D1.tickerJOIN tblSBMIWeights S ON S.ticker = D1.tickerWHERE P.portfolio = 'SBMI-TBA' AND P.asofdate = (SELECT MAX(asofdate) FROM tblPortfolio WHERE portfolio = 'SBMI-TBA' AND asofdate <= @.asofdate)AND S.asofdate = (SELECT MAX(asofdate) FROM tblSBMIWeights WHERE asofdate <=@.asofdate)AND D1.datatype = 'Elasticity' AND D1.DataSource = @.analysisidAND D2.datatype = 'Convexity' AND D2.DataSource = @.analysisidAND D1.asofdate = @.asofdateGROUP BY D1.datatype, D2.datatype SELECT * FROM @.ShiftDataORDER BY [BP Shift] END

================================================== =======

SAME SQL STATEMENTS (this runs within 1 to 2 secs and returs around 41 rows which is right)
================================================== =====
Code: ( sql )

SET NOCOUNT ONBEGINdeclare @.asofdate DATETIME, @.analysisid VARCHAR(50) SELECT @.asofdate = '10/31/2007'SELECT @.analysisid = 'TVE-2004-10' declare @.ShiftData TABLE([BP Shift] float, [Elasticity] float, [Convexity] float) INSERT INTO @.ShiftData ([BP Shift], Elasticity, Convexity)SELECT cast(substring(D1.datatype, 12, len(D1.datatype)-13) AS float) AS '[BP Shift]', SUM(S.Weight*D1.value) AS Elasticity, SUM(S.Weight*D2.Value) AS ConvexityFROM tblData D1JOIN tblData D2 ON D1.asofdate = D2.asofdate AND D1.ticker = D2.ticker AND RIGHT(D1.datatype,LEN(D1.DataType) - 10) = RIGHT(D2.datatype, LEN(D2.DataType) - 9)JOIN tblPortfolio P ON P.ticker = D1.tickerJOIN tblSBMIWeights S ON S.ticker = D1.tickerWHERE P.portfolio = 'SBMI-TBA' AND P.asofdate = (SELECT MAX(asofdate) FROM tblPortfolio WHERE portfolio = 'SBMI-TBA' AND asofdate <= @.asofdate)AND S.asofdate = (SELECT MAX(asofdate) FROM tblSBMIWeights WHERE asofdate <=@.asofdate)AND D1.datatype LIKE 'Elasticity@.%' AND D1.DataSource = @.analysisidAND D2.datatype LIKE 'Convexity@.%' AND D2.DataSource = @.analysisidAND D1.asofdate = @.asofdateGROUP BY D1.datatype, D2.datatype INSERT INTO @.ShiftData ([BP Shift], Elasticity, Convexity)SELECT 0 AS '[BP Shift]', SUM(S.Weight*D1.value) AS Elasticity, SUM(S.Weight*D2.Value) AS ConvexityFROM tblData D1JOIN tblData D2 ON D1.asofdate = D2.asofdate AND D1.ticker = D2.ticker AND RIGHT(D1.datatype,LEN(D1.DataType) - 10) = RIGHT(D2.datatype, LEN(D2.DataType) - 9)JOIN tblPortfolio P ON P.ticker = D1.tickerJOIN tblSBMIWeights S ON S.ticker = D1.tickerWHERE P.portfolio = 'SBMI-TBA' AND P.asofdate = (SELECT MAX(asofdate) FROM tblPortfolio WHERE portfolio = 'SBMI-TBA' AND asofdate <= @.asofdate)AND S.asofdate = (SELECT MAX(asofdate) FROM tblSBMIWeights WHERE asofdate <=@.asofdate)AND D1.datatype = 'Elasticity' AND D1.DataSource = @.analysisidAND D2.datatype = 'Convexity' AND D2.DataSource = @.analysisidAND D1.asofdate = @.asofdateGROUP BY D1.datatype, D2.datatype SELECT * FROM @.ShiftDataORDER BY [BP Shift]END

====================Question moved to SQL Server Forum.