Adding a resource (Generic Script) to the SQL Cluster

I would like to add a VB Script to the SQL Cluster so that
in the event of a failover the script will execute on the
new active node. The script goes out and checks for the
existance of some key files once a day.
I am running Windows 2003 SQL 2000 in an Active / Passive
configuration.
I have the VBS running every morning through task Mgr, but
I would like to get it configured to run as a clustered
resource and failover to the active node.
Thanks,
In 2003, there is a "Generic Script" resource type that support VBS and Perl
scripts. Check out the following for more details:
http://msdn.microsoft.com/library/de...ric_script.asp
Regards,
John
"Rodney" <anonymous@.discussions.microsoft.com> wrote in message
news:441301c47333$a3be4aa0$a601280a@.phx.gbl...
> I would like to add a VB Script to the SQL Cluster so that
> in the event of a failover the script will execute on the
> new active node. The script goes out and checks for the
> existance of some key files once a day.
> I am running Windows 2003 SQL 2000 in an Active / Passive
> configuration.
> I have the VBS running every morning through task Mgr, but
> I would like to get it configured to run as a clustered
> resource and failover to the active node.
> Thanks,

add new record - weird results

I have written a generic script in asp to add records to a table. The
script works fine with one table but in the other tables it updates
the first record in the table with the values for the new record and
adds a new record with all null values?!? Here is the script:

adOpenKeyset=1
adLockOptimistic=3
Set cnnFormToDB = Server.CreateObject("ADODB.Recordset")
'INSERT******************************************* ******************
'Open connection to sub-table
if action = "insert" then
cnnFormToDB.Open "SELECT top 1 * FROM " &subtable,
"DSN=Barrheadsql;UID=barrhead;PWD=ty93eta",
adOpenKeyset,adLockOptimistic
cnnFormToDB.AddNew
else
cnnFormToDB.Open "SELECT top 1 * FROM " & subtable & " WHERE ID = " &
ID, "DSN=Barrheadsql;UID=barrhead;PWD=ty93eta", adOpenKeyset,
adLockOptimistic
End If

if not cnnFormToDB.eof then
cnnFormToDB.MoveFirst
end if
'DELETE******************************************* ********************
if action = "delete" then
cnnFormToDB.Delete
cnnFormToDB.Close
else
'Build 2nd SQL String
For i=0 To Ubound(aFields)
cnnFormToDB(aFields(i)) = aValues(i)
Next

'Insert record into sub-table
cnnFormToDB.Update

The even weirder thing is I know that values in aFields and aValues
are OK because this test script I wrote for one of the tables works
just fine:

adOpenKeyset=1
adLockOptimistic=3
Set cnnFormToDB = Server.CreateObject("ADODB.Recordset")
cnnFormToDB.Open "SELECT top 1 * FROM FlightsDirect",
"DSN=Barrheadsql;UID=barrhead;PWD=ty93eta", adOpenKeyset,
adLockOptimistic
cnnFormToDB.AddNew

cnnFormToDB("fkCity") = 198
cnnFormToDB("fkDepartureAirport") = 159
cnnFormToDB("ValidFrom") = "17/09/2003"
cnnFormToDB("ValidTo") = "15/10/2003"
cnnFormToDB("fkType") = 1
cnnFormToDB("ReturnFlight") = 1
cnnFormToDB("fkReturnAirport") = 184
cnnFormToDB("Price") = yyyyyy
cnnFormToDB("fkATOL") = 5346

cnnFormToDB.Update

Any suggestions appreciated

Thanks

AlisonButtercup (alison_clark20@.hotmail.com) writes:
> I have written a generic script in asp to add records to a table. The
> script works fine with one table but in the other tables it updates
> the first record in the table with the values for the new record and
> adds a new record with all null values?!? Here is the script:

I cannot really say what is going on. The problem with ADO is that
while it tries to hides to the SQL from you, it does actually makes
you more confused, because you don't know what is going on under the
covers.

You can use the Profiler to see what ADO submits to SQL Server.

However, rather than relying on ADO doing things right by chance, I
would encourage you to use stored procedures instead. Then you don't
use these .AddNew or .Update methods.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Add a where condition only if a parameter exists

I'm pretty new to TSQL. I'm trying to write a generic proc that returns
either ALL records from a table or (if a parameter is set to 1) or only a
sub-set of that table (if parameter is set to 0).
Here's an example of what I'm aiming for:
Create table contact (
contactID int,
FullName varchar (40),
IsActive bit)
Create procedure sp_select_Contacts
@.ContactID int,
@.ShowAll bit = null
AS
-- if @.ShowAll is null, return all records
--otherwise limit records to records where isActive = True
How can I conditionally add a WHERE clause based on the value of @.ShowAll?
Thanks!
- JohnnyI presume that ContactId is the prinary key of Contacts. If this is the case
,
skip the @.ShowAll parameter.
If @.ContactId is null then have it return all, otherwise have it return only
that contact:
SELECT *
FROM Contacts
WHERE ContactId = COALESCE(@.ContactId,ContactId)
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis
"JohnnyMagz" wrote:

> I'm pretty new to TSQL. I'm trying to write a generic proc that returns
> either ALL records from a table or (if a parameter is set to 1) or only a
> sub-set of that table (if parameter is set to 0).
> Here's an example of what I'm aiming for:
> Create table contact (
> contactID int,
> FullName varchar (40),
> IsActive bit)
> Create procedure sp_select_Contacts
> @.ContactID int,
> @.ShowAll bit = null
> AS
> -- if @.ShowAll is null, return all records
> --otherwise limit records to records where isActive = True
> How can I conditionally add a WHERE clause based on the value of @.ShowAll?
> Thanks!
> - Johnny|||You need dynamic sql in this case
something like (untested)
Create procedure sp_select_Contacts
@.ContactID int,
@.ShowAll bit = null
AS
declare @.sql varchar(8000)
set @.sql = 'select * from contact'
if @.showall is not null
set @.sql = @.sql + ' where isactive = 1'
exec (@.sql)
go
"JohnnyMagz" <JohnnyMagz@.discussions.microsoft.com> wrote in message
news:B48A82FB-F620-44C5-82B5-F51D23D19B41@.microsoft.com...
> I'm pretty new to TSQL. I'm trying to write a generic proc that returns
> either ALL records from a table or (if a parameter is set to 1) or only a
> sub-set of that table (if parameter is set to 0).
> Here's an example of what I'm aiming for:
> Create table contact (
> contactID int,
> FullName varchar (40),
> IsActive bit)
> Create procedure sp_select_Contacts
> @.ContactID int,
> @.ShowAll bit = null
> AS
> -- if @.ShowAll is null, return all records
> --otherwise limit records to records where isActive = True
> How can I conditionally add a WHERE clause based on the value of @.ShowAll?
> Thanks!
> - Johnny|||Assuming isActive is never NULL and @.ShowAll is either NULL or 1.
...where isActive = isnull(@.ShowAll, 0) or isActive = @.ShowAll
"JohnnyMagz" <JohnnyMagz@.discussions.microsoft.com> wrote in message
news:B48A82FB-F620-44C5-82B5-F51D23D19B41@.microsoft.com...
> I'm pretty new to TSQL. I'm trying to write a generic proc that returns
> either ALL records from a table or (if a parameter is set to 1) or only a
> sub-set of that table (if parameter is set to 0).
> Here's an example of what I'm aiming for:
> Create table contact (
> contactID int,
> FullName varchar (40),
> IsActive bit)
> Create procedure sp_select_Contacts
> @.ContactID int,
> @.ShowAll bit = null
> AS
> -- if @.ShowAll is null, return all records
> --otherwise limit records to records where isActive = True
> How can I conditionally add a WHERE clause based on the value of @.ShowAll?
> Thanks!
> - Johnny

Add a column to a view

hello,
I am trying to add a column to a view. This is a generic column not pulling
from the other tables but will be updated based on the values of the other
columns.
I thought I simply altering the view and add a column and set a value, if
the other values were found true. However, it is not allowing me to add a
column unless it is a integer value. Any suggestions?you cannot add a column here.
You use alter view to change the vew definition. You will have to give the
full sql stament with the computed column (if I am not wrong) in the view.
use it this way. Its very similar to create view just the keyword create is
replaced by alter. Hope this helps.
alter view vname
as
select .... col1 + col2 as new_col
from table1
where <conditions>|||> I thought I simply altering the view and add a column and set a value, if
> the other values were found true. However, it is not allowing me to add a
> column unless it is a integer value.
What does "not allowing" mean? Can you show the DDL for the table
referenced in the view, the existing code for the view, the change you are
attempting, and the exact text of the error? A lot of the people here are
pretty smart, but not many are psychic.
A|||Well, I tried to alter the view but it not giving me what I want.
Currently, I am pulling all the columns from other tables. However, I want
to create a new column called columnD and set it to 1 if columnA, columnB an
d
columnC are 1 else set it to 0.
"Omnibuzz" wrote:

> you cannot add a column here.
> You use alter view to change the vew definition. You will have to give the
> full sql stament with the computed column (if I am not wrong) in the view.
> use it this way. Its very similar to create view just the keyword create i
s
> replaced by alter. Hope this helps.
> alter view vname
> as
> select .... col1 + col2 as new_col
> from table1
> where <conditions>|||Are you using Query Analyzer or the view editor in Enterprise Manager? What
code are you trying to run? This sounds like it needs a CASE expression,
and the ability to understand CASE is a serious limitation in Enterprise
Manager. As I asked before, if you can provide *SPECIFIC* information, we
may be able to help.
"Sonya" <Sonya@.discussions.microsoft.com> wrote in message
news:9240DFD4-B5A0-482B-B64E-AE13F3B31E4E@.microsoft.com...
> Well, I tried to alter the view but it not giving me what I want.
> Currently, I am pulling all the columns from other tables. However, I want
> to create a new column called columnD and set it to 1 if columnA, columnB
> and
> columnC are 1 else set it to 0.
> "Omnibuzz" wrote:
>|||>I have it sort of working but it is not totally correct. So i am sing a
> better way in adding a column to the view based on the below criteria.
And I'd like my car to run better! But if I can't be bothered to give my
mechanic more information, he's going to tell me to go jump off a bridge...|||Since you haven't given the defn, I have created a sample. See if this is
what you wnted. Hope this helps.
create table tbl (a int,b int,c int)
go
insert into tbl values (1,1,1)
insert into tbl values (1,1,0)
create view vw1
as
select a,b,c from tbl
select * from vw1
-- till above was your setup...
--now do this
alter view vw1
as
select a,b,c, case when a=b and b=c and c=1 then 1 else 0 end as d
from tbl
select * from vw1|||Initially,
I wrote to add columnD
alter view sam_generic
AS
select columnD, a.columnA, a.columnB,a. columnC, m.member, v.history,
u.category, c.content, l.letter
from tableA AS a INNER JOIN
tableU on a.category = u.category LEFT OUTER JOIN
tableC on a.id = c.id LEFT OUTER JOIN
tableL on a.id = l.id LEFT OUTER JOIN
tableM on a.id = m.id LEFT OUTER JOIN
tableV on a.id =v.id
Set columnD = 1 Where Exists (a.columnA = 1) AND (if a.columnB = 1) AND (if
(a. columnC=1)
"Aaron Bertrand [SQL Server MVP]" wrote:

> What does "not allowing" mean? Can you show the DDL for the table
> referenced in the view, the existing code for the view, the change you are
> attempting, and the exact text of the error? A lot of the people here are
> pretty smart, but not many are psychic.
> A
>
>|||I was using query analyzer. I didn't think about using case statements. I'll
try that.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Are you using Query Analyzer or the view editor in Enterprise Manager? Wh
at
> code are you trying to run? This sounds like it needs a CASE expression,
> and the ability to understand CASE is a serious limitation in Enterprise
> Manager. As I asked before, if you can provide *SPECIFIC* information, we
> may be able to help.
>
>
> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> news:9240DFD4-B5A0-482B-B64E-AE13F3B31E4E@.microsoft.com...
>
>|||I was submiting the information that you requested. I was working on multipl
e
tasks, so give me a minute to send the information to you.
"Aaron Bertrand [SQL Server MVP]" wrote:

> And I'd like my car to run better! But if I can't be bothered to give my
> mechanic more information, he's going to tell me to go jump off a bridge..
.
>
>