Help with ServerAgent Job syntax

Hi,

Hopefully someone can help me. I'm having difficulty with the syntax to delete a record from 4 joined tables when creating a job.

I have an 'Applicants' table linked to four other tables 'Courses', 'EmploymentHistory', 'Qualifications', and 'References' using the field 'ApplicantID'.

I want to create a job to delete all the records where the Finalised field = '0' and the record was created more than 3 days ago.

The syntax I have been using on just one of the joined tables to start with doesn't delete from the joined table:

USE OnlineApplications
DELETE Applicants
FROM Applicants
INNER JOIN Courses
ON Applicants.ApplicantID = Courses.ApplicantID
WHERE Finalised = 0 AND Created < DATEADD(d, 3, Created)

How can I delete the records from the other four tables?

ThanksHave you defined foreign key relationships?|||Darnit Poots, you beat me to it again!
I was going to ask for the joins between the tables :'(|||joins <> relationships ;)|||*shifty look*
I knew that ;)|||Have you defined foreign key relationships?

Nope, makes sense to do that I suppose. :o

For the projects I do, I merely create the tables then use the tables to store the data, haven't needed to create relationships in the past, bad development I know.

But I haven't really looked into the features of SQL Server yet.

I take it the syntax should work then if I create relationships?
Can I do this by creating a diagram?
Then I take it I need to set the joins to cascade to delete from other tables?|||Can I do this by creating a diagram?Yeah - but script it out.
Basic sample code. Check BoL for more options.

IF EXISTS (SELECT NULL FROM sys.tables WHERE name = N't_name') BEGIN
DROP TABLE t_name
END
IF EXISTS (SELECT NULL FROM sys.tables WHERE name = N'other_t_name') BEGIN
DROP TABLE other_t_name
END
CREATE TABLE dbo.other_t_name
(
c_name INT NOT NULL CONSTRAINT df_other_t_name_c_name DEFAULT 0
, CONSTRAINT pk_other_t_name PRIMARY KEY CLUSTERED (c_name) WITH (FILLFACTOR = 80)
, CONSTRAINT ix_other_t_name_c_name_u_nc UNIQUE NONCLUSTERED (c_name) WITH (FILLFACTOR = 80)
, CONSTRAINT ck_other_t_name_c_name CHECK (c_name BETWEEN 1 AND 10)
)
GO
CREATE TABLE dbo.t_name
(
c_name INT NOT NULL CONSTRAINT df_t_name_c_name DEFAULT 0
, CONSTRAINT pk_t_name PRIMARY KEY CLUSTERED (c_name) WITH (FILLFACTOR = 80)
, CONSTRAINT ix_t_name_c_name_u_nc UNIQUE NONCLUSTERED (c_name) WITH (FILLFACTOR = 80)
, CONSTRAINT ck_t_name_c_name CHECK (c_name BETWEEN 1 AND 10)
, CONSTRAINT fk_t_name_other_t_name FOREIGN KEY (c_name) REFERENCES other_t_name (c_name) ON DELETE CASCADE
)
GO

HTH

Help with one-to-many relation join

I have two tables defined below. I would like to join them but choose
only the record from tblb which has the highest value of BID.
I know the following is wrong and I need help. Thanks, larzeb.
SELECT MAX(A), MAX(B), MAX(C), MAX(D), MAX(Y), MAX(Z)
FROM tbla A
JOIN tblb B
ON A.AID = B.AID
GROUP BY A.AID
CREATE TABLE tbla (
AID int IDENTITY(1,1) NOT NULL ,
A char (10),
B char (10),
C char (10),
D char (10),
CONSTRAINT PK_ID PRIMARY KEY (AID)
)
CREATE TABLE tblb (
BID int IDENTITY(1, 1) NOT NULL ,
AID int NOT NULL ,
Y char (10),
Z char (10) ,
CONSTRAINT PK_B PRIMARY KEY (BID),
CONSTRAINT FK_B_A FOREIGN KEY (AID)
REFERENCES tbla (AID)
)Here are a few possibilities:
select A, B, C, D, Y, Z
from tbla A
join tblb B
on A.AID = B.AID
where not exists (
select * from tblb as B2
where B2.AID = B.AID
and B2.BID < B.BID
)
or
select A, B, C, D, Y, Z
from tbla A
join tblb B
on A.AID = B.AID
where B.BID in (
select max(BID) from tblb as B2
group by B2.AID
)
or
select A, B, C, D, Y, Z
from tbla A
join tblb B
on A.AID = B.AID
where B.BID = (
select max(BID)
from tblb as B2
where B2.AID = B.AID
)
Steve Kass
Drew University
larzeb wrote:

>I have two tables defined below. I would like to join them but choose
>only the record from tblb which has the highest value of BID.
>I know the following is wrong and I need help. Thanks, larzeb.
>SELECT MAX(A), MAX(B), MAX(C), MAX(D), MAX(Y), MAX(Z)
>FROM tbla A
>JOIN tblb B
> ON A.AID = B.AID
>GROUP BY A.AID
>CREATE TABLE tbla (
> AID int IDENTITY(1,1) NOT NULL ,
> A char (10),
> B char (10),
> C char (10),
> D char (10),
> CONSTRAINT PK_ID PRIMARY KEY (AID)
> )
>CREATE TABLE tblb (
> BID int IDENTITY(1, 1) NOT NULL ,
> AID int NOT NULL ,
> Y char (10),
> Z char (10) ,
> CONSTRAINT PK_B PRIMARY KEY (BID),
> CONSTRAINT FK_B_A FOREIGN KEY (AID)
> REFERENCES tbla (AID)
> )
>
>

help with integer field

hi all,

i have an autonumber field (primary key) and another integer field as part of a table. What i want to do is when a record is created, the default value of the integer field should be the_autonumber+1000 for eg record with pk 82 will have an integer field that's automatically 1082. Would it be possible to do this ? Thanks in advance.Hi, try this trigger...

CREATE TRIGGER trig_MyTableAddTrigger
ON MyTable
FOR INSERT
AS
-- Get the value to put in the second column
DECLARE @.NewValue integer
SELECT @.NewValue = (SELECT MyTableID FROM Inserted) + 1000

-- Update the same table [MyTable] with new value
UPDATE [MyTable] SET SecondColumName = @.NewValue
WHERE MyTableID = (SELECT MyTableID FROM INSERTED)|||thank you very,very much for your help.

Help with inserting a new record into database - error Must declare the scalar variable "@

Hi,

Can anybody help me with this, I've got a simple program to add a new record to a table (2 items ID - Integer and Program - String) that matches all examples I can find, but when I run it I get the error :

Must declare the scalar variable "@.BookMarkArrayA".

when it reaches the .insert command, I've tried using a local variable temp in place of the array element and.ToString , but still get the same error

This is the code :

PublicSub NewCustomer()

Dim tempAsString =" "

Dim IDAsInteger = 1

'Restore the array from the view state

BookMarkArrayA =Me.ViewState("BookMarkArrayA")

temp = BookMarkArrayA(6)

Dim CustomerAs SqlDataSource =New SqlDataSource()

Customer.ConnectionString = ConfigurationManager.ConnectionStrings("CustomerConnectionString").ToString()

Customer.InsertCommand ="INSERT INTO [Table1] ([ID],[Program]) VALUES (@.ID, @.BookMarkArrayA(6))"

Customer.InsertParameters.Add ("ID", ID)

Customer.InsertParameters.Add ("Program",@.BookMarkArrayA(6))

Customer.Insert()

EndSub

Cheers

Ken

I'm not sure where you got the (6) syntax from?

Try this. Change these 3 lines:
Customer.InsertCommand ="INSERT INTO [Table1] ([ID],[Program]) VALUES (@.ID, @.BookMarkArrayA(6))"

Customer.InsertParameters.Add ("ID", ID)

Customer.InsertParameters.Add ("Program",@.BookMarkArrayA(6))

to this and you should have better luck:
Customer.InsertCommand ="INSERT INTO [Table1] ([ID],[Program]) VALUES (@.ID, @.BookMarkArrayA)"

Customer.InsertParameters.Add ("@.ID", SqlDbType.Integer).Value = ID

Customer.InsertParameters.Add ("@.Program", SqlDbType.VarChar,6).Value =@.BookMarkArrayA)

|||

Terri,

Thanks for the reply, it didn't compile as the SqlDbType wasn't recognised.

The origional error was @.BookMarkArray was an undeclared scalar variable, which suggests to me that this part of the command requires a pointer to the actual variable BookMarkArray denoted by putting the @. symbol first.

The (6) syntak by the way, was the element of the array I wanted to load into the DB.

The code fits with other examples I've looked up, I'm proberbly missing something simple.

Ken

|||Boy did I screw up the code. I am sorry about that. Here's what it *should* be:

Customer.InsertCommand ="INSERT INTO [Table1] ([ID],[Program]) VALUES (@.ID, @.Program)"

Customer.InsertParameters.Add ("@.ID", SqlDbType.Int).Value = ID

Customer.InsertParameters.Add ("@.Program", SqlDbType.VarChar,6).Value = BookMarkArray(6)

|||

Terri,

I still get the error that SqlDbType is not declared, am i missing inheriting a library or some thing

Ken

|||Well, try qualifying it by adding the namespace in front and see if that takes care of it.

SqlClient.SqlDbType.Int|||

Terri,

I get the error SqlClient not declared, so I assume that I'm not inheriting something.

I tried the following code which worked, but I can't find a method to change the String value 'Test Program' into a parameter:

Dim sqlConnection1AsNew System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("CustomerConnectionString").ToString())Dim cmdAsNew System.Data.SqlClient.SqlCommandWith cmd

.CommandType = System.Data.CommandType.Text

.CommandText =

"INSERT Into Customer (Program) VALUES ('Test Program')"

.Connection = sqlConnection1

EndWith

sqlConnection1.Open()

cmd.ExecuteNonQuery()

sqlConnection1.Close()

Ken

|||Try that code block like this (updates in pink):

Dim sqlConnection1AsNew System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("CustomerConnectionString").ToString())Dim cmdAsNew System.Data.SqlClient.SqlCommandWith cmd

.CommandType = System.Data.CommandType.Text

.CommandText = "INSERT Into Customer (Program) VALUES (@.Program)"

.Connection = sqlConnection1

EndWith

cmd.Parameters.Add("@.Program", System.Data.SqlDbType.VarChar, 99).Value = "Test Program"

sqlConnection1.Open()

cmd.ExecuteNonQuery()

sqlConnection1.Close()

|||

Terri,

Thanks, that worked, althrough I'd tried variations of adding parameters before without success.

My concern is that I don't seem to be able to see the namespace System.Data.SQLClient and when you look at the methods, it says you must reference this namespace but don't tell you how.

Ken

|||

KenWalker:

My concern is that I don't seem to be able to see the namespace System.Data.SQLClient

If you are using code inline (ie, not a separate .vb file), put this at the top of the page, right below the @.Page directive:

<%@. Import Namespace="System.Configuration" %>

If you are using code beside/behind, put this at the very top of your code:

Imports System.Data.Client

|||

I'm lazy, so I put it in web.config under the system.web section:

<pagestheme="default">

<namespaces>

<addnamespace="System.Data"/>

<addnamespace="System.Data.SqlClient"/>

<addnamespace="System.Configuration.ConfigurationManager"/>

</namespaces>

</pages>

|||

Thanks Terry, I have solved the problem using -Dim cmdAsNew System.Data.SqlClient.SqlCommand and then adding the parameters that way.

Ken

Help with INSERT Procedure

Hello,

I have a procedure which INSERTS a new record in two tables [Content] and [ContentLocalized] given [ContentName] and [ContentCulture].

Here are the table structures:

<Content>
|-- [ContentId] Type=UniqueIdentifier PK
| [ContentName] Type=NVarChar(100)
|
| <ContentLocalized>
| [ContentLocalizedId] Type=UniqueIdentifier PK
| ---> [ContentId] Type=UniqueIdentifier FK
| [ContentCulture] Type=NVarChar(5)
| [ContentHtml] Type=NVarChar(MAX)

WHAT I AM MISSING:

> If in <Content> THERE IS a record with the same [ContentName] then this record will be used AND:

If in <ContentLocalized> for the given [ContentName] THERE IS NO such [ContentCulture] then a new will be created with [ContentCulture] and [ContentHtml]

If in <ContentLocalized> for the given [ContentName]THERE IS such [ContentCulture] then its [ContentHtml] will be replaced by the given [ContentHtml]

> If in <Content> THERE IS NOT a record with the same [ContentName] then:

A new <Content> record will be created with [ContentName] and a new <ContentLocalized> record will be created with [ContentCulture] and [ContentHtml].

I know I didn't get there yet.

Could somebody help em out?

I am posting the INSERT Store Procedure as I have now:

1SET ANSI_NULLSON2GO3SET QUOTED_IDENTIFIERON4GO5ALTER PROCEDURE [dbo].[Content_CreateContentByNameAndCulture]6 @.ContentNameNVARCHAR(100),7 @.ContentCultureNVARCHAR(5),8 @.ContentHtmlNVARCHAR(MAX)9AS10BEGIN11 SET NOCOUNT ON;12DECLARE @.ContentIdUNIQUEIDENTIFIER;13SET @.ContentId =NEWID();14INSERT dbo.Content15 (16 ContentName17 )18SELECT19 @.ContentName;20SELECT @.ContentId;21INSERT dbo.ContentLocalized22 (23 ContentId,24 ContentCulture,25 ContentHtml26 )27SELECT28 @.ContentId,29 @.ContentCulture,30 @.ContentHtml;31END32GO333435

Thanks,

Miguel

Hello:

Here is one example involving the uniqueidentifier column:

ALTERPROCEDURE [dbo].[test_sp]

@.Captionnvarchar(50),

@.IsPublic

bit

AS

INSERTINTO [Albums]([c_id], [Caption],[IsPublic])VALUES(NEWID(), @.Caption, @.IsPublic)

RETURN

--Thid is the table I copied for you to test:

CREATE

TABLE [dbo].[Albums](

[AlbumID] [int]

IDENTITY(1,1)NOTNULL,

[Caption] [nvarchar]

(50)NOTNULL,

[IsPublic] [bit]

NOTNULL,

[c_id] [uniqueidentifier]

NULL)|||

SET ANSI_NULLSONGOSET QUOTED_IDENTIFIERONGOALTER PROCEDURE [dbo].[Content_CreateContentByNameAndCulture] @.ContentNameNVARCHAR(100), @.ContentCultureNVARCHAR(5), @.ContentHtmlNVARCHAR(MAX)ASBEGINSET NOCOUNT ON;DECLARE @.ContentIdUNIQUEIDENTIFIER,@.ExistingContentNameNVARCHAR(100)SELECT @.ExistingContentName = ContentNameFROM ContentWHERE ContentName = @.ContentName;IF (@.ExistingContentName =null)BEGININSERT INTO dbo.Content (ContentId, ContentName)VALUES (NEWID(), @.ContentName);ENDENDGO

Its not yet complete but i think its a start, does anyone knows how to make a statement that will return FALSE if the select statement returned no data and TRUE if there is >= 1?