Check Referential Ingerity or Catch Error

Hi all,

Just wondering what would be the normal or more efficient practice to insert/update a record.

1. Check for existence of primary record (using SELECT in stored procedure)
2. Capture error and handling

My problem is that I try to execute an stored procedure from a VB client.. but unable to capture the errors in SP, it just prompts the error and thats it, not responding to my "SELECT @.err = @.@.ERROR" after the insert statement.

so now, I'm thinking of capturing the error on the client (which I am able to do) and handle it from there.. or to make sure that RI is enforced by 'searching' for Pks in the primary tables before executing the INSERT statement in my stored procedure.

Any advise would be appreciated..

CyherusHi all,

Just wondering what would be the normal or more efficient practice to insert/update a record.

1. Check for existence of primary record (using SELECT in stored procedure)
2. Capture error and handling

My problem is that I try to execute an stored procedure from a VB client.. but unable to capture the errors in SP, it just prompts the error and thats it, not responding to my "SELECT @.err = @.@.ERROR" after the insert statement.

so now, I'm thinking of capturing the error on the client (which I am able to do) and handle it from there.. or to make sure that RI is enforced by 'searching' for Pks in the primary tables before executing the INSERT statement in my stored procedure.

Any advise would be appreciated..

Cyherus

Any error above 16 won't be caught. You need to catch those at the client or app level. If you're having these types of RI issues though, you have an app design issue. The PK should already be known by the app when you go to insert foreign key records. On the insert of a new PK, it should either be auto or a true natural key. In this case, there is no chance of error.|||Many thanks.. now things are much clearer..

I'm reading these data from a text file and foreign keys on these records are not checked against the primary tables in my DB, thus the need to handle this.

I dun quite get you when you mention about inserting new PKs, what do you mean by auto or true natural key??

Check null value of long data type

Hi, I have a record set that is bound to a table in MS SQL Server. One
filed in the table is bound to a "long" type member variable in the
RecordSet. What will happen to the "long" variable when the field is
NULL in the table?
Thanks!
-YiYi (huskerchen@.hotmail.com) writes:
> Hi, I have a record set that is bound to a table in MS SQL Server. One
> filed in the table is bound to a "long" type member variable in the
> RecordSet. What will happen to the "long" variable when the field is
> NULL in the table?

I will have to admit that I am out on a limb, but I would expect
IsNull to be true for this field.

The simplest is probably just to make an experiment.

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

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

Check if record is full-text indexed

Hi all,

I am dealing with a very large database, and as soon as a record is
submitted I need to run a full-text query against it. I believe it
might take a while before the record is fully indexed and therefore
would not return a result.

How can I check whether the record in question is already indexed, if
at all?

This is MS SQL 2005

Thanks in advance..what sort of update mechanism are you using to update the Full text index
?for example, change tracking

--

Jack Vamvas
___________________________________
Need an IT job? <a href="http://links.10026.com/?link=http://www.itjobfeed.com">uk it jobs</a>

"Pacific Fox" <tacofleur@.gmail.comwrote in message
news:1177049160.189956.209000@.l77g2000hsb.googlegr oups.com...

Quote:

Originally Posted by

Hi all,
>
I am dealing with a very large database, and as soon as a record is
submitted I need to run a full-text query against it. I believe it
might take a while before the record is fully indexed and therefore
would not return a result.
>
How can I check whether the record in question is already indexed, if
at all?
>
This is MS SQL 2005
>
Thanks in advance..
>

sql

Check if record has dependencies

I have a database where one table 'Project' has a one to many relationship
with several other tables. Other than doing multiple Select queries, is
there a simple quick way of testing to see if there are any dependencies in
the tables connect with the constraints
Cheerstry this:
sp_help <table>
--
current location: alicante (es)
"Newbie" wrote:

> I have a database where one table 'Project' has a one to many relationship
> with several other tables. Other than doing multiple Select queries, is
> there a simple quick way of testing to see if there are any dependencies i
n
> the tables connect with the constraints
> Cheers
>
>|||What ?,
were you replying to someone else by mistake, this makes absolutely no sense
to me whatsoever !
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:2BF857E7-DDE1-4AFF-AA13-965C3573162B@.microsoft.com...
> try this:
> sp_help <table>
> --
> current location: alicante (es)
>
> "Newbie" wrote:
>|||Basically you want to do a outer left join with any related tables. Let's
assume that the Project table has related tables Tasks and Notes. The
following will count the number of projects that have at least one task or
note:
select
count(distinct Project.ProjectID) as CountAssignedProjects
from Project
left join Tasks on Tasks.ProjectID = Project.ProjectID
left join Notes on Notes.ProjectID = Project.ProjectID
where
Tasks.ProjectID is not null or
Notes.ProjectID is not null
"Newbie" <me@.me.com> wrote in message
news:%23yfFEoBSGHA.4792@.TK2MSFTNGP14.phx.gbl...
>I have a database where one table 'Project' has a one to many relationship
>with several other tables. Other than doing multiple Select queries, is
>there a simple quick way of testing to see if there are any dependencies in
>the tables connect with the constraints
> Cheers
>|||I'm sorry I was wrong. I though that you are looking for the current
references for a table and using sp_help such request is returned...
--
current location: alicante (es)
"Newbie" wrote:

> What ?,
> were you replying to someone else by mistake, this makes absolutely no sen
se
> to me whatsoever !
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:2BF857E7-DDE1-4AFF-AA13-965C3573162B@.microsoft.com...
>
>

Check if record exists

Hello,

I created the following SQL script to check if a record exists:

IF (EXISTS (SELECT LevelName FROM dbo.by27_Levels WHERE LOWER(@.LevelName) = LOWER(LevelName)))
Return (1)
ELSE
Return (0)

And I also found in a web page another solution:
IF EXISTS(SELECT 1 FROM TABLENAME WHERE LevelName=@.LevelName)
SELECT 1
ELSE
SELECT 0

- Which approach should I use?
- Why "SELECT 1 FROM"?
- And when should I use SELECT or RETURN?

All I need is to know if the record exists ... nothing else.

I will use this procedure on an ASP.NET 2.0 / C# web site.
I am not sure if this important but anyway ...

Thank You,
Miguel

select 1 from table returns a value which is basically the same as selecting a column name when evaluating from the exists function. The difference is that 1 is a constant so the column name does not need to be looked up, and since you do not need the value of the column then select 1 can be used.

using return or select depends on what you are using to call the sql statement. If you use return then you need to look into the calls returns parameters. Using a select , you need to use a scalar or dataset return call. Most people use the select call because those calls are easier to handle but not necessarily more efficient

|||

In EXISTS you can use any of them but the result will be the same (it always look for first occurrence of value selected) I do not know about time of execution but I would prefer something like this

RETURN (CASE
when EXISTS (SELECT LevelName
FROM dbo.by27_Levels
WHERE LOWER(@.LevelName) = LOWER(LevelName))) then 1
else
0
end)

If it will be executed on SQL server and you server is Case insensitive you do not have to use LOWER and it will speed up a little.

Thanks

|||

ozkary:

If you use return then you need to look into the calls returns parameters.

What do you mean to look the calls returns parameters?

Can you point to some info about it?

Thanks,.

Miguel

|||

By default SQL Server is not case sensitive so the LOWER() is not needed.

If the LevelName is a unique key for the table I would avaoid using T-SQL and use a single generic SQL query:

SELECT COUNT(*) FROM dbo.by27_Levels WHERE @.LevelName = LevelName

RETURN ends the execution of the batch T-SQL and SELECT does not. Note any select results not stored in local variables will be output.

|||

Some stored procedures could have return parameters they are defined with OUTPUT

create procedure TEST

@.tcParam1 as varchar(100) = NULL,

@.tcOutputParam as varchar(100) = NULL OUTPUT

AS

BEGIN

...

SET @.tcOutputParam= 'result'

END

and if you call it

declare @.oparam as varchar(100)

exec test 'Test valuee', @.oparam OUTPUT

you can get output value from procedure

RETURN always is returned by stored procedure and you can get it like:but it only integer, output parameter can be almost any type

EXEC @.result = test 'Test valuee', @.oparam OUTPUT

Thanks

|||

yes, one usually uses parameters to call a stored procedure. Those parameters can have the following directions: INPUT, OUTPUT, RETURN.

To hadle a return value, one needs to add a return parameter to the call:

SqlCommand cmd = new SqlCommand("myProc", myConnection)

cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters..Add("ReturnValue", SqlDbType.Int).Direction = ParameterDirection.ReturnValue

add block to make the call

if using a reader make sure to close it before trying to read the return parameter

read the return parameter value

string value = cmd.Parameters.item["ReturnValue"].Value.Tostring();

for more info search on ParameterDirection.ReturnValue

I hope this helps.

Check for Primary Key before Inserting New Record

Hi,

Can someone please tell me the best practices for checking the primary key field before inserting a record into my database?

As an example I have created an asp.net page using VB with an SQL server database. The web page will just insert two fields into a table (Name & Surname into the Names table). The primary key or the Names table is "Name". When I click the Submit button I would like to check to ensure there is not a duplicate primary key. If there is return a user friendly message i.e. A record already exisits, if there no duplicate, add the record.

I guess I could use try, catch within the .APSX page or would a stored procedure be better?

Thanks

Brett

one way you could do this is write a stored proc where you can check :

CREATE PROC ...

@.intResult INT OUTPUT

SET @.intResult = 0

IF NOT EXISTS (SELECT <col> FROM <table> WHERE <condition>

BEGIN

-- do the insert here

-- SET @.intResult to 1

END

Now in your application check for the value of intResult. If its 1 the INSERT was successful. If it was 0 the record already exists. You can take this further and also return any error messages.

|||

Thanks for the information.

Please can you let me know how can I check in my ASP.NET page the value of intResult?

Regards,

Brett

|||

ndinakar wrote:

Now in your application check for the value of intResult. If its 1 the INSERT was successful. If it was 0 the record already exists. You can take this further and also return any error messages.

Return Codes are not needed in languages supporting exceptions. Instead, throw an exception from your SP within SQL Server ...

IF EXISTS(SELECT * FROM <tb> WHERE <pk> = @.pk) BEGIN
RAISERROR('A Document with a number of %s already exists.', 16, 2, @.pk)
RETURN
END

In the ASP code, use a TRY/CATCH around the Execute method. If the error returned is a user defined error (50000), wrap the message in your own exception and send it directly back to the client.

|||

Thanks again for your help, could you please post me an example of how the code for the Try/Catch would look in ASP.NET using VB.
Regards,

Brett

|||check out the recent articles in my blog..I have some sample code that uses Try/Catch block's.
|||

I have read your article but I still don't understand how I can check the RAISERROR from the stored procedure. I then want to display an error to the user saying for example "Duplicate Name Found" if the RAISERROR occurs but if the record is added I would like a message saying "Record Added".

Are there any book you can recommend that deal with ASP.NET & SQL Stored Procedures.

|||I dont have sample code but am sure you;d find it if you google.

Check for no value in a local variable

How can I check the value of a local variable to see whether it reurned
a record after setting it to a SELECT Query?
I also need to be able use the value as an integer if it finds a match
Declare @.Found as Int
SET @.Found= ( SELECT TOP 1 ID FROM tbl WHERE col1=@.col1 AND col2=@.col2
)
IF @.Found ' -- how to test for no match found
Thanks.Read this article
http://vyaskn.tripod.com/difference..._and_select.htm
IF @.var IS NULL ?
"hals_left" <cc900630@.ntu.ac.uk> wrote in message
news:1143041257.585219.145960@.u72g2000cwu.googlegroups.com...
> How can I check the value of a local variable to see whether it reurned
> a record after setting it to a SELECT Query?
> I also need to be able use the value as an integer if it finds a match
> Declare @.Found as Int
> SET @.Found= ( SELECT TOP 1 ID FROM tbl WHERE col1=@.col1 AND col2=@.col2
> )
> IF @.Found ' -- how to test for no match found
> Thanks.
>|||Thanks!

Check for duplicates

I am trying to do a check if the record already exists in my database. I wrote this code and all is well except it takes about 2-5 seconds to execute on my access database. Wen you know this one is called about 30 000 to 50 000 times that is unacceptable.

Can someone please take a look and see if this can be accelerated?

Thanks

Napivo

Public Function CheckDouble(psWeb As String, psIP As String, pdDate As Date, _
pdTime As Date, psEnvironment As String, psControler As String, _
plType As Long, plSize As Long, pbSpecial As Boolean, plSpend As Long) As Boolean

Dim rs As ADODB.Recordset
Dim sql As String

sql = "select count(ip) as cnt from Logs where [IP] = '" & psIP & "'" _
& " And [Web] = '" & psWeb & "'" _
& " And [Date] = #" & Format(pdDate, "MM/DD/YY") & "#" _
& " and [Time] = #" & Format(pdTime, "HH:MM") & "#" _
& " and [Environment] = '" & psEnvironment & "'" _
& " and [Controler] = '" & psControler & "'" _
& " and [Spend] =" & plSpend & "" _
& " and [Type] =" & plType & "" _
& " and [Special] = " & CBool(pbSpecial) & "" _
& " and [Size] =" & plSize

On Error GoTo CheckDouble_Error
Set rs = oCon.Execute(sql)
On Error GoTo 0
If rs.Fields("cnt").Value > 0 Then
CheckDouble = True
End If
Exit Function
CheckDouble_Error:

Debug.Print "Error " & Err.Number & " (" & Err.Description & ") in procedure CheckDouble of Class Module cLogDatabase"
End Function

This is an example of the SQL statement I get

select count(ip) as cnt from Logs where [IP] = '194.235.127.40 ' And [Web] = 'WEB2' And [Date] = #07/01/04# and [Time] = #14:14# and [Environment] = 'AON' and [Controler] = 'EAFormController' and [Spend] =2 and [Type] =200 and [Special] = False and [Size] =23489My first suggestion would be to look at adding indices to make it easier for Jet to process the query. You may well need to experiment a bit to find a good combination of columns, since Jet often makes "interesting" choices where indicies are concerned.

If that doesn't help enough, I'd switch to MSDE in order to get more help understanding the query itself. It is a lot easier to find and fix query problems in MSDE than it is in Jet, and once you've solved the problem you can almost always move back to Jet if you want.

-PatP

Check for constraint on delete

How do you code a procedure to delete a record where if it has an
error (because of foeign key constraint , no cascade and related
records) it will continue and do an update instead.
I have tried this but it doesnt continue if the delete hits an error.
Thanks
Create procedure dbo.delete_record
@.id smallint
as
delete from table
where id=@.id
if @.@.error <>0
update table set deleted=1
where id=@.idwhy don't you update before deleting?

CHECK DOUBLE RECORD

hi,
I've problem to check row which having duplicate value
for fieldA and fieldC.
Eg.
rec 1
fieldA fieldB fieldC
50000A 123456 A
rec 2
50000A 654321 A
Thanks.
see following example:
create table test(c1 varchar(10), c2 varchar(10), c3 char(1))
insert into test values('50000A','123456','A')
insert into test values('50000A','654321','A')
insert into test values('50000X','654321','A')
query:
select a.*
from test a join (Select c1,c3 from test group by c1,c3 having count(*) >
1) b
on a.c1 = b.c1 and a.c3=b.c3
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com

Check Constraint

I'm having a little trouble enforcing a unique record rule. The problem is
a
DateTime field is used in the unique value, but I only need to the DatePart
(not
the time), otherwise I would use a standard unique constraint.
I need the combination of ResID,RxDayID,RxTimeID, and Just The Date of
DispDateTime to be unique.
The following works for inserts but not updates if a record already exists.
It
does work if there are 2 matching records for an update. I'd rather have th
e
back-end enforce the rules, but if I must I'll build it into the application
.
Is there a function or a method I'm over-looking?
ALTER TABLE [dbo].[ResRxDispensed] ADD
CONSTRAINT [CK_ResRxDispensed_Unique]
CHECK ([dbo].[DispRecordUnique_FN](
[ResID],
[RxDayID],
[RxDoseTimeID],
[DispDateTime]) = 'Y')
Create Function [dbo].[DispRecordUnique_FN](
@.ResID Int,
@.RxDayID Int,
@.RxTimeID Int,
@.ADate DateTime)
Returns VarChar(1)
Begin
Declare @.TheDate DateTime, @.Rtn Varchar(1),@.Cnt Int
Set @.TheDate = Cast(Cast(@.ADate as Char(11)) As DateTime)
Set @.Rtn = 'N'
Select @.Cnt = Count(*)
From ResRxDispensed
Where ResID = @.ResID
And RxDayID = @.RxDayID
And RxDoseTimeID = @.RxTimeID
And (DispDateTime >= @.TheDate And DispDateTime <= (@.TheDate+1))
If (@.Cnt <= 1)
Begin
Set @.Rtn = 'Y'
End
Return @.Rtn
End
TIA,
-Steve-You've overcomplicated the solution IMHO:
1) create a computed column:
alter table dbo.ResRxDispensed
add DispDate as convert(char(8), DispDateTime, 112)
go
2) create the unique constraint:
alter table dbo.ResRxDispensed
add constraint <constraint name>
unique (ResID, RxDayID, RxTimeID, DispDate)
go
If this does not help, please post proper DDL and maybe sample data.
ML
http://milambda.blogspot.com/|||Experiment to see if the year(), month(), day() or datediff() functions
would work here.
"Steve Zimmelman" <skz@.charter.nospam.net> wrote in message
news:%23kFjHVZWGHA.2180@.TK2MSFTNGP02.phx.gbl...
> I'm having a little trouble enforcing a unique record rule. The problem
> is a DateTime field is used in the unique value, but I only need to the
> DatePart (not the time), otherwise I would use a standard unique
> constraint.
> I need the combination of ResID,RxDayID,RxTimeID, and Just The Date of
> DispDateTime to be unique.
> The following works for inserts but not updates if a record already
> exists. It does work if there are 2 matching records for an update. I'd
> rather have the back-end enforce the rules, but if I must I'll build it
> into the application. Is there a function or a method I'm over-looking?
> ALTER TABLE [dbo].[ResRxDispensed] ADD
> CONSTRAINT [CK_ResRxDispensed_Unique]
> CHECK ([dbo].[DispRecordUnique_FN](
> [ResID],
> [RxDayID],
> [RxDoseTimeID],
> [DispDateTime]) = 'Y')
>
> Create Function [dbo].[DispRecordUnique_FN](
> @.ResID Int,
> @.RxDayID Int,
> @.RxTimeID Int,
> @.ADate DateTime)
> Returns VarChar(1)
> Begin
> Declare @.TheDate DateTime, @.Rtn Varchar(1),@.Cnt Int
> Set @.TheDate = Cast(Cast(@.ADate as Char(11)) As DateTime)
> Set @.Rtn = 'N'
> Select @.Cnt = Count(*)
> From ResRxDispensed
> Where ResID = @.ResID
> And RxDayID = @.RxDayID
> And RxDoseTimeID = @.RxTimeID
> And (DispDateTime >= @.TheDate And DispDateTime <= (@.TheDate+1))
> If (@.Cnt <= 1)
> Begin
> Set @.Rtn = 'Y'
> End
> Return @.Rtn
> End
> TIA,
> -Steve-
>|||Thanks.
I tried your suggestion but it apparently doesn't work when you use a comput
ed
column in a constraint. When I attempt to add a record I get the errror:
[INSERT failed because the following SET options have incorrect settings:
'ARITHABORT']
Using SET ARITHABORT ON before the insert/update works, but it seems it must
be
issued before each update/insert statement.

> If this does not help, please post proper DDL and maybe sample data.
CREATE TABLE [dbo].[ResRxDispensed] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ResID] [int] NULL ,
[ResRxID] [int] NULL ,
[RxDayID] [int] NULL ,
[RxDoseTimeID] [int] NULL ,
[DispDateTime] [datetime] NULL ,
[Created] [datetime] NULL ,
[StaffID] [int] NULL ,
[DispDate] AS (convert(char(8),[DispDateTime],112))
) ON [PRIMARY]
Insert Into ResRxDispensed
([ResID],[ResRxID],[RxDayID],[RxDoseTime
ID],[DispDateTime])
Values
(1,1,1,1,{ts '2006-04-06 09:30:00'}) ;
The insert should fail if another record has
ResID = 1
RxDayID = 1
RxDoseTimeID = 1
DispDateTime = 2006-04-06 (with any time factor)
Updates should also fail if the update changes the record to match another
record with the criteria.
-Steve-|||
"JT" <someone@.microsoft.com> wrote in message
news:%23sosFSaWGHA.4424@.TK2MSFTNGP05.phx.gbl...
> Experiment to see if the year(), month(), day() or datediff() functions wo
uld
> work here.
I'm not sure I understand. Experiment how? The function
DispRecordUnique_FN()works, but using it in a Check constraint only works on
Inserts, not updates.
-Steve-|||This worked fine for me on SQL 2000.
CREATE TABLE [dbo].[TestTable] (
[ResID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RxDayID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RxDoseTimeID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DispDateTime] [datetime] NOT NULL ,
[OtherData] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DispDate] AS (convert(char(8),[DispDateTime],112))
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TestTable] ADD
CONSTRAINT [IX_TestTable] UNIQUE NONCLUSTERED
(
[ResID],
[RxDoseTimeID],
[RxDayID],
[DispDate]
) ON [PRIMARY]
GO
"Steve Zimmelman" <skz@.charter.nospam.net> wrote in message
news:ep7wCVaWGHA.1220@.TK2MSFTNGP02.phx.gbl...
> Thanks.
> I tried your suggestion but it apparently doesn't work when you use a
computed
> column in a constraint. When I attempt to add a record I get the errror:
> [INSERT failed because the following SET options have incorrect settings:
> 'ARITHABORT']
> Using SET ARITHABORT ON before the insert/update works, but it seems it
must be
> issued before each update/insert statement.
>
> CREATE TABLE [dbo].[ResRxDispensed] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [ResID] [int] NULL ,
> [ResRxID] [int] NULL ,
> [RxDayID] [int] NULL ,
> [RxDoseTimeID] [int] NULL ,
> [DispDateTime] [datetime] NULL ,
> [Created] [datetime] NULL ,
> [StaffID] [int] NULL ,
> [DispDate] AS (convert(char(8),[DispDateTime],112))
> ) ON [PRIMARY]
>
> Insert Into ResRxDispensed
> ([ResID],[ResRxID],[RxDayID],[RxDoseTime
ID],[DispDateTime])
> Values
> (1,1,1,1,{ts '2006-04-06 09:30:00'}) ;
> The insert should fail if another record has
> ResID = 1
> RxDayID = 1
> RxDoseTimeID = 1
> DispDateTime = 2006-04-06 (with any time factor)
> Updates should also fail if the update changes the record to match another
> record with the criteria.
> -Steve-
>
>|||> This worked fine for me on SQL 2000.
Hi Jim,
Thanks. I'm using SQL 2000, but I'm still getting the same error.
Here's the scripts for creating everything I'm using.
CREATE TABLE [dbo].[ResRxDispensed] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ResID] [int] NULL ,
[ResRxID] [int] NULL ,
[RxDayID] [int] NULL ,
[RxDoseTimeID] [int] NULL ,
[DispDateTime] [datetime] NOT NULL ,
[Created] [datetime] NULL ,
[StaffID] [int] NULL ,
[DispDate] AS (convert(char(8),[DispDateTime],112))
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ResRxDispensed] WITH NOCHECK ADD
CONSTRAINT [PK_ResRxDispensed] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ResRxDispensed] ADD
CONSTRAINT [DF_ResRxDispensed_DispDateTime] DEFAULT (getdate()) FOR
[DispDateTime],
CONSTRAINT [DF_ResRxDispensed_Created] DEFAULT (getdate()) FOR [Created],
CONSTRAINT [IX_ResRxDispensed] UNIQUE NONCLUSTERED
(
[ResID],
[RxDayID],
[RxDoseTimeID],
[DispDate]
) ON [PRIMARY]
GO
CREATE INDEX [ResRxDispensed_ResID] ON [dbo].[ResRxDispensed]([ResID]) ON
[PRIMARY]
GO
CREATE INDEX [ResRxDispensed_ResRxID] ON [dbo].[ResRxDispensed]([ResRxID]) ON
[PRIMARY]
GO
CREATE INDEX [ResRxDispensed_RxDayID] ON [dbo].[ResRxDispensed]([RxDayID]) ON
[PRIMARY]
GO
CREATE INDEX [ResRxDispensed_RxDoseTimeID] ON
[dbo].[ResRxDispensed]([RxDoseTimeID]) ON [PRIMARY]
GO
-Steve-|||Is there a reason why you can't add this SET option to your stored procedure
s?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Steve Zimmelman" <skz@.charter.nospam.net> wrote in message
news:%23Set$paWGHA.2080@.TK2MSFTNGP05.phx.gbl...
> Hi Jim,
> Thanks. I'm using SQL 2000, but I'm still getting the same error.
> Here's the scripts for creating everything I'm using.
> CREATE TABLE [dbo].[ResRxDispensed] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [ResID] [int] NULL ,
> [ResRxID] [int] NULL ,
> [RxDayID] [int] NULL ,
> [RxDoseTimeID] [int] NULL ,
> [DispDateTime] [datetime] NOT NULL ,
> [Created] [datetime] NULL ,
> [StaffID] [int] NULL ,
> [DispDate] AS (convert(char(8),[DispDateTime],112))
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[ResRxDispensed] WITH NOCHECK ADD
> CONSTRAINT [PK_ResRxDispensed] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[ResRxDispensed] ADD
> CONSTRAINT [DF_ResRxDispensed_DispDateTime] DEFAULT (getdate()) FOR [DispDateTime],
> CONSTRAINT [DF_ResRxDispensed_Created] DEFAULT (getdate()) FOR [Created],
> CONSTRAINT [IX_ResRxDispensed] UNIQUE NONCLUSTERED
> (
> [ResID],
> [RxDayID],
> [RxDoseTimeID],
> [DispDate]
> ) ON [PRIMARY]
> GO
> CREATE INDEX [ResRxDispensed_ResID] ON [dbo].[ResRxDispensed]([ResID]) ON [PRIMARY]
> GO
> CREATE INDEX [ResRxDispensed_ResRxID] ON [dbo].[ResRxDispensed]([ResRxID]) ON [PRIMARY]
> GO
> CREATE INDEX [ResRxDispensed_RxDayID] ON [dbo].[ResRxDispensed]([RxDayID]) ON [PRIMARY]
> GO
> CREATE INDEX [ResRxDispensed_RxDoseTimeID] ON [dbo].[ResRxDispensed]([RxDoseTimeID]) ON [PRIMARY]
> GO
>
> -Steve-
>|||"Tibor Karaszi" wrote
> Is there a reason why you can't add this SET option to your stored procedures?[/co
lor]
I suppose not. I just wanted something that didn't require such special
handling for updates in case I needed to do some manual repair/entry outside
of
the application.
-Steve-|||"Tibor Karaszi" wrote:
> Is there a reason why you can't add this SET option to your stored procedures?[/co
lor]
This procedure, when used, produces the same error.
[INSERT failed because the following SET options have incorrect settings:
'ARITHABORT']
I'm at a loss how to proceed...
-Steve-
Exec NewResRxDispensed_SP 1,1,1,1,{ts '2006-05-13 09:30:00'},24
Create Procedure [dbo].[NewResRxDispensed_SP]
@.ResID int,
@.ResRxID int,
@.RxDayID int,
@.RxDoseTimeID int,
@.DispDateTime datetime,
@.StaffID int
As
SET ARITHABORT ON
Insert Into [ResRxDispensed]
([ResID],
[ResRxID],
[RxDayID],
[RxDoseTimeID],
[DispDateTime],
[StaffID])
Values
(@.ResID,
@.ResRxID,
@.RxDayID,
@.RxDoseTimeID,
@.DispDateTime,
@.StaffID)
/*** Return New Int ID [ID] ***/
Select SCOPE_IDENTITY() As NewID

Check constraint

Hi, i want to know if it's possible to have information about a
record, if this record is referenced by another record of another
table ?
Before deleting this record i want to know if another table is
reference it.
Escuse for my english,
SamuelA FOREIGN KEY constraint should do thsi for you.
CREATE TABLE Employees (...department INTEGER NOT NULL
CONSTRAINT FK_employees_dept REFERENCES Departments (department)...)
--
David Portas
SQL Server MVP
--

Check constraint

Hi, i want to know if it's possible to have information about a
record, if this record is referenced by another record of another
table ?
Before deleting this record i want to know if another table is
reference it.
Escuse for my english,
SamuelA FOREIGN KEY constraint should do thsi for you.
CREATE TABLE Employees (...department INTEGER NOT NULL
CONSTRAINT FK_employees_dept REFERENCES Departments (department)...)
David Portas
SQL Server MVP
--

Check condition and wait

Hi,

I have a data-flow-task that imports data to sqlserver.

Now I want to check, if a special column of an imported record is null.

If yes, I have to wait 10 minutes and jump to the data-flow-task again. (Cjeck and wait).

How can I do this with the integration services?

Thanks

Gerd

You can do timer style loops, so this could be extended for your loop on the 10 minute, and maybe use some extra variables to keep track.

For Loop Container Samples
(http://www.sqlis.com/310.aspx)

You could have an Exec SQL Task inside your loop as the first task. Query the column and assign a variable value using the query result to indicate if the column is true. Then have a Data Flow Task linked from the Exec SQL Task and use an Expression on the constraint such that it is satisfied only when the variable in indicates that the Exec SQL Task found a value.

The final thing would be to extend the loop EvalExpression such that if it would not wait/loop if the Exec SQL Task assigned variable indicate data had been found.

The principal seems sound, though you may want to adjust the expressions and variables used to fit exactly with what you want.

Check and send data with xp_sendmail

How yould i loop trought all the records in a table and fetching a specic record that is flagged and sending for each record found a email with that records data to a mail recipient. this should be part of a step in a sql job. PLZ HELPHad a little problem in understanding your phrases. You may check cursor in BOL for looping actions.|||create proc abc as
declare c1 cursor as select primary key from table name where condition
open c1
while (@.@.fetch_status =0)

begin
exec xp_sendmail ....
fetch next
end

something like this and schedule as job !!!

Chart Filters

HI,
Does anybody know how to write a chart filter that will return only the last row in the record set and conversely return all rows except for the last in the data set (for a separate chart)
My Report consists of a dataset that has the total in the last row. I want to create a chart with only the total row and another chart on the same page using the same data set but excluding the total row.
Cheers
KevinIf there is anything special about the row it would be straight forward as a
chart filter and a table filter to separate the rows. If the only
distinguishing feature of the row is that it is the last row then I can not
think of a way in RS 2000 to do this. We are considering allowing
aggregates of aggregates, which would allow you to filter on the max
rownumber.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kevin Wilson" <KevinWilson@.discussions.microsoft.com> wrote in message
news:A23DB1DA-C4EC-4789-A46A-C236B070F5A4@.microsoft.com...
> HI,
> Does anybody know how to write a chart filter that will return only the
last row in the record set and conversely return all rows except for the
last in the data set (for a separate chart)
> My Report consists of a dataset that has the total in the last row. I want
to create a chart with only the total row and another chart on the same
page using the same data set but excluding the total row.
> Cheers
> Kevin|||Jason,
There is nothing special about the row except that it's the last. However I've managed to created a chart filter that returns the last row by using the Bottom N operator.
expression operator value
=Fields!RH0_Product.Value BottomN =1
However do you know how to express a filter that defines where the rows is NOT = to the BottomN 1
If I can get this then I've solved my problem...
Much appreciated.
Kevin
"Jason Carlson [MSFT]" wrote:
> If there is anything special about the row it would be straight forward as a
> chart filter and a table filter to separate the rows. If the only
> distinguishing feature of the row is that it is the last row then I can not
> think of a way in RS 2000 to do this. We are considering allowing
> aggregates of aggregates, which would allow you to filter on the max
> rownumber.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Kevin Wilson" <KevinWilson@.discussions.microsoft.com> wrote in message
> news:A23DB1DA-C4EC-4789-A46A-C236B070F5A4@.microsoft.com...
> > HI,
> > Does anybody know how to write a chart filter that will return only the
> last row in the record set and conversely return all rows except for the
> last in the data set (for a separate chart)
> >
> > My Report consists of a dataset that has the total in the last row. I want
> to create a chart with only the total row and another chart on the same
> page using the same data set but excluding the total row.
> >
> > Cheers
> > Kevin
>
>|||Please see my response to your "NOT BottomN" thread started on 06/29.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kevin Wilson" <KevinWilson@.discussions.microsoft.com> wrote in message
news:A6A1CBD8-F987-472C-B507-E0AA21FD3A4C@.microsoft.com...
> Jason,
> There is nothing special about the row except that it's the last. However
I've managed to created a chart filter that returns the last row by using
the Bottom N operator.
> expression operator value
> =Fields!RH0_Product.Value BottomN =1
> However do you know how to express a filter that defines where the rows
is NOT = to the BottomN 1
> If I can get this then I've solved my problem...
> Much appreciated.
> Kevin
>
> "Jason Carlson [MSFT]" wrote:
> > If there is anything special about the row it would be straight forward
as a
> > chart filter and a table filter to separate the rows. If the only
> > distinguishing feature of the row is that it is the last row then I can
not
> > think of a way in RS 2000 to do this. We are considering allowing
> > aggregates of aggregates, which would allow you to filter on the max
> > rownumber.
> >
> > --
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "Kevin Wilson" <KevinWilson@.discussions.microsoft.com> wrote in message
> > news:A23DB1DA-C4EC-4789-A46A-C236B070F5A4@.microsoft.com...
> > > HI,
> > > Does anybody know how to write a chart filter that will return only
the
> > last row in the record set and conversely return all rows except for the
> > last in the data set (for a separate chart)
> > >
> > > My Report consists of a dataset that has the total in the last row. I
want
> > to create a chart with only the total row and another chart on the same
> > page using the same data set but excluding the total row.
> > >
> > > Cheers
> > > Kevin
> >
> >
> >

Changing values in a record

I have the problem of dynamically changing a field value in a row depending on the value of the same field in the previous row, assuming the table is sorted on that field.
:(
You may consider that field as a key of same sort as other values in the table do not help in selecting them uniquely.
Ex.
Before After
130 -> 130
130 -> 130A
140 -> 140
140 -> 140AIs the order of rows with "ties" arbitrary, or is there some hidden criteria that allows you to order them too? This is important, because it significantly changes the nature of the problem.

-PatP|||Is this a one-time data fix, or will this process need to be run regularly?

I assume, too, that data values such as 130B, 130C, etc... may also be required?

I have to say, it is rarely a good idea to create ID values this way (though I understand that sometimes the business model requires it), but it is also rare that a good application design requires stored data to be ordered in a specific manner, either logically or physically. Records should be independent from one-another.

I suspect that this is a case of either:
1) Poor application design.
2) A data-import issue.
3) A homework problem for a class.|||Yessss,
It is a problem of moving data from a legacy system (a whole lot of rubbish) into a new ERP. Business logics and data models are way different. Under this circumstances, a non-key field in the old system is now a key in the new model, from here the need of updating the values with a trailer character (or what have you). :)

Blindman, the values you mentioned are allowed, altough the repetition is often limited to two rows only. :o

Pat, there are no possible sorting orders because the other fields may vary 'randomly'.|||For a one-time shot, and given data that has no natural order beyond the field you are dealing with, a cursor may be a valid option.

Create a cursor that loops through the dataset order by your field, and that updates the field value if the prior field value had the same ID.|||If you don't have any other way to impose order on the rows, then a cursor is the only option that you've got left. Its messy and a "last resort", but it will get the job done!

-PatP