Controlling outbound IP address on a SQL Cluster

Hello,

Is there any way to specify which IP address is used when executing a SQL Job. We recently found that Jobs starting from our cluster (Windows 2003 SQL 2000 SP 4 in a Active / Passive configuration) use the IP address from the local node rather then the virtual IP address tied to the SQL cluster. Jobs executing from the remote SQL server are running. The use of the local address has caused a failure at the firewall.

Thanks

mjdenn

Moving to the right alias so SQL Agent experts can reply to your question.

Thanks,

Zhiqiang Feng

Control jobs using SQL code?

Is there a way (system stored proc) to schedule/reschedule jobs through
SQL code (stored procedure) instead of GUI? We have a job set up on SQL
server and we are trying to control scheduling piece of it through
stored proc. Any help would be appreciated. Thanx!
*** Sent via Developersdex http://www.examnotes.net ***Have a look at sp_update_jobschedule in Books Online. This procedure is in t
he
msdb database, so it is used like
EXEC msdb.dbo.sp_update_jobschedule
"Test Test" wrote:

> Is there a way (system stored proc) to schedule/reschedule jobs through
> SQL code (stored procedure) instead of GUI? We have a job set up on SQL
> server and we are trying to control scheduling piece of it through
> stored proc. Any help would be appreciated. Thanx!
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||have a look at following system stored procedures in BOL, there are few more
as well which are related to scheduling the job, you can have a look at them
in BOL.
sp_add_job
sp_add_jobstep
sp_add_jobschedule
sp_delete_job
sp_help_job
sp_help_jobstep
sp_update_job
"Test Test" wrote:

> Is there a way (system stored proc) to schedule/reschedule jobs through
> SQL code (stored procedure) instead of GUI? We have a job set up on SQL
> server and we are trying to control scheduling piece of it through
> stored proc. Any help would be appreciated. Thanx!
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Thanks, Mark. It really helps!
*** Sent via Developersdex http://www.examnotes.net ***

CONTAINS function and OR

I am attempting to create a query with a "dynamic" CONTAINS query, e.g.
DECLARE @.Keywords VARCHAR(128)
SET @.Keywords = NULL
SELECT * FROM Jobs WHERE (@.Keywords IS NULL OR CONTAINS(JobTitle,
@.Keywords))
However, the CONTAINS function seems to behave very weirdly when OR is
involved. Even though @.Keywords is null, it still evaluates the contains,
and fails because of the null.
Server: Msg 7603, Level 15, State 1, Line 40
Syntax error in search condition, or empty or null search condition ''.
On the other and, if I change the query to this:
SELECT * FROM Jobs WHERE (@.Keywords IS NULL OR CONTAINS(JobTitle,
@.Keywords)) AND JobID IN (SELECT JobID FROM JobSkills WHERE SkillID = 2)
It works fine.
But that's not it. If I move the order of the clauses:
SELECT * FROM Jobs WHERE JobID IN (SELECT JobID FROM JobSkills WHERE SkillID
= 2) AND (@.Keywords IS NULL OR CONTAINS(JobTitle, @.Keywords))
I get the original error. Same goes for adding another contains clause at
the beginning. It seems that the original CONTAINS works fine, others defy
all logic.
Can someone suggest what is causing this behaviour (or why it works like
this - doesn't seem to make any sense) and a possible workaround short of
using hideous dynamic SQL?
Edward wrote on Fri, 20 Jan 2006 09:38:58 +1100:

> I am attempting to create a query with a "dynamic" CONTAINS query, e.g.
> DECLARE @.Keywords VARCHAR(128)
> SET @.Keywords = NULL
> SELECT * FROM Jobs WHERE (@.Keywords IS NULL OR CONTAINS(JobTitle,
> @.Keywords))
> However, the CONTAINS function seems to behave very weirdly when OR is
> involved. Even though @.Keywords is null, it still evaluates the contains,
> and fails because of the null.
> Server: Msg 7603, Level 15, State 1, Line 40
> Syntax error in search condition, or empty or null search condition
> ''.
> On the other and, if I change the query to this:
> SELECT * FROM Jobs WHERE (@.Keywords IS NULL OR CONTAINS(JobTitle,
> @.Keywords)) AND JobID IN (SELECT JobID FROM JobSkills WHERE SkillID = 2)
> It works fine.
> But that's not it. If I move the order of the clauses:
> SELECT * FROM Jobs WHERE JobID IN (SELECT JobID FROM JobSkills WHERE
> SkillID = 2) AND (@.Keywords IS NULL OR CONTAINS(JobTitle, @.Keywords))
> I get the original error. Same goes for adding another contains clause at
> the beginning. It seems that the original CONTAINS works fine, others defy
> all logic.
> Can someone suggest what is causing this behaviour (or why it works like
> this - doesn't seem to make any sense) and a possible workaround short of
> using hideous dynamic SQL?
It depends on the query parser and how it decides to process the query.
Depending on the order it processes clauses, and what they contain, it might
skip the CONTAINS clause completely (which it appears to do in the 2nd
case). Using FTS clauses when unnecessary will impact performance as the FTS
process is external to SQL Server. You could try doing the following:
IF COALESCE(@.Keywords,'') = ''
SELECT * FROM Jobs
ELSE
SELECT * FROM Jobs WHERE CONTAINS(JobTitle, @.Keywords)
END
This avoids dynamic SQL, and prevents the error is @.Keywords is NULL or
empty (an empty string will also cause an error, not just a NULL)
Dan

Constraints

Hi,

I have the following problem:

I have a table called Jobs with the fields:

JobNumber, Name, Customer, ...

And a table called Customers with the fields:

ID, Name, Address, ...

Obviously Jobs is linked to Customers with the Customer<->ID fields. I want to set it up so that if a Customer is deleted, then any Jobs that had that customer listed now have the Customer field set to NULL. Can I do this with a constraint, or will I need to use a trigger?

Cheers,
Little'un

Hello,
This is a common problem. SQL 2000 and higher have checkboxes in theDiagram Relationship Properties window that allow for cascading updatesand deletes. Behind the scenes, I think this creates a trigger, thoughI'm not sure. Pre SQL 2000, you ha dto write your own trigger.
Note that either triggers and cascading make your database harder todebug. It may not be intuitive to another developer that data issupposed to be deleted in Tables A & B when you delete data inTable A.Developer Emptor.
Jason
|||Note that in SQL Server 2005 there is a new cascading type, SET NULL.
See the following article:
http://www.sqljunkies.com/Tutorial/D839595A-0BC3-4F7E-97EA-DE4911EE2217.scuk
|||

DRI(Declarative Referential integrity) constraint will do it, you don't need code to do it because you can enable it in the table properties when you are creating it. The code below is from the BOL(books online).

CREATE TABLE order_part
(order_nmbr int,
part_nmbr int
FOREIGN KEY REFERENCES part_sample(part_nmbr)
ON DELETE CASCADE,
qty_ordered int)
GO

CREATE TABLE order_part
(order_nmbr int,
part_nmbr int
FOREIGN KEY REFERENCES part_sample(part_nmbr)
ON UPDATE CASCADE,
qty_ordered int)
GO

Run a search in the BOL(books online) for Cascade Delete. Hope this helps.

|||Caddre,
ON DELETE CASCADE will delete the rows, not set them to NULL as the OP wanted...

|||SET NULL is a new feature in SQL Server 2005. I posted my first look a while back.|||

Unfortunately I don't have SQL Server 2005, so the new feature is no good to me.

I've looked on the Microsoft site and it's not available there, so I'm assuming it's not available yet. Does anyone know a release date?

In the mean time, does anyone know a workaround, I don't really want to have to write it into my ASP code.

Little'un

|||As Jason pointed out, you can write a trigger to handle this.
Something along the lines of:

CREATE TRIGGER tg_delete_setnull
ON YourTable
FOR DELETE
AS
BEGIN
UPDATE SomeOtherTable
SET YourTablePK = NULL
WHERE EXISTS
(SELECT *
FROM deleted
WHERE deleted.YourTablePK = SomeOtherTable.YourTablePK)
END

|||... Actually, that might have to be an INSTEAD OF trigger:

CREATE TRIGGER tg_delete_setnull
ON YourTable
INSTEAD OF DELETE
AS
BEGIN
UPDATE SomeOtherTable
SET YourTablePK = NULL
WHERE EXISTS
(SELECT *
FROM deleted
WHERE deleted.YourTablePK = SomeOtherTable.YourTablePK)
DELETE YourTable
WHERE YourTable.YourTablePK IN
(SELECT YourTablePK
FROM deleted)
END
|||Thank you very much. I can't wait to get my hands on 2005, any ideas when that's out?|||

littlecharva wrote:

Thank you very much. I can't wait to get my hands on 2005, any ideas when that's out?

Later this year