sql reports: below

Showing posts with label below. Show all posts

Thursday, March 22, 2012

Can update accumulate?

I need to write an UPDATE statement that adds to a field from data in
another table. Can someone help? below is sample:
UPDATE TableA
SET Total = Total + TableB.Amount
FROM TableB JOIN TableA ON TableB.EmpNo = TableA.EmpNo
WHERE TableB.PrdYr = 2005
When I do this, it does not add in the incremented Total field and I end up
with the last TableB.Amount value.
Thanks.
David>> I need to write an UPDATE statement that adds to a field from data in
Yes, but you will have to provide sufficient information for others to
understand your problem. Pl. read www.aspfaq.com/5006 and post your DDLs,
sample data & expected results
Anith|||Try this, it will keep a running total in TableA each time the query is
run. If this is going to be run and needs all of the values to start
out 0 (no running total), then remove the 'Total + ' part of the query.
UPDATE TableA
SET Total = Total +
( SELECT ISNULL(SUM(TableB.Amount),0)
FROM TableB
WHERE TableB.PrdYr = 2005
and TableB.EmpNo = TableA.EmpNo
)
Kalvin|||David,
An UPDATE statement will only make one assignment
to each column. UPDATE .. FROM is a T-SQL extension
to standard SQL that allows poorly defined statements, and
while it can be handy, it can also cause confusion. I wish an
error were raised in situations like this, but that's not the case.
To do what you want, you probably need something like
update TableA set
Total = Total + (
select sum(TableB.Amount)
from TableB
where TableB.EmpNo = TableA.EmpNo
and TableB.PrdYr = 2005
)
Steve Kass
Drew University
David wrote:

>I need to write an UPDATE statement that adds to a field from data in
>another table. Can someone help? below is sample:
>UPDATE TableA
>SET Total = Total + TableB.Amount
>FROM TableB JOIN TableA ON TableB.EmpNo = TableA.EmpNo
>WHERE TableB.PrdYr = 2005
>When I do this, it does not add in the incremented Total field and I end up
>with the last TableB.Amount value.
>Thanks.
>David
>
>|||Kalvin caught one thing I didn't. This needs either COALESCE
or a WHERE condition on the update, to avoid NULLing out
Total values when there's no match in TableB. Here's a WHERE
condition that ought to do it.
update TableA set
Total = Total + (
..
)
where exists (
select *
from TableB
where TableB.EmpNo = TableA.EmpNo
and TableB.PrdYr = 2005
and TableB.Amount is not null
)
SK
Steve Kass wrote:
> David,
> An UPDATE statement will only make one assignment
> to each column. UPDATE .. FROM is a T-SQL extension
> to standard SQL that allows poorly defined statements, and
> while it can be handy, it can also cause confusion. I wish an
> error were raised in situations like this, but that's not the case.
> To do what you want, you probably need something like
> update TableA set
> Total = Total + (
> select sum(TableB.Amount)
> from TableB
> where TableB.EmpNo = TableA.EmpNo
> and TableB.PrdYr = 2005
> )
>
> Steve Kass
> Drew University
> David wrote:
>|||1) Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.
2) Would you like to learn REAL SQL or only some proprietary kludges
that have unpredicatable results, as you have posted?
Did you actually split out a year as a temporal column'!! Surely not
!
Why don't you know that column and field are **totally** different?
Why don't you know that there is no such thing as a generic, magical
"amount" -- it has to be the amount of something. Have you ever had a
BASIC -- repeat BASIC in capital letters -- data modeling class?
You can probably get enough kludges in a newsgroup to slip past your
boss unitl you get to the next job to screw up them too.
I got an email tonight form a kid who volunteered to do a DB for an
African Relief agency and seriously screwed it up. I got the consult
after things got messed up and I posted this in some newsgroups as an
example. I guess he found me via those postings.
I know his design crippled some children; I am not sure about causing
deaths and a part of me does not want to know. Please care enough not
to do that. To other people. To other people.sql

can u help with my select ?

Hello All,

I have a simple table with 2 columns.

datetime int
27.05.04 1
29.05.04 2
31.05.04 5

and i need to get a view that looks like this below :

27.05.04 1 1
29.05.04 2 3
31.05.04 5 8

The third column shows a sum of numbers that were display before present one
...
I was trying build stored proc however I'm not so familiar with SQL so I
failed.. :-(

can anybody help ?

thx in advance
Krzysiek"Krzysiek" <krzysiek79@.hotmail.com> wrote in message
news:c9fq48$25ka$1@.news2.ipartners.pl...
> Hello All,
> I have a simple table with 2 columns.
> datetime int
> 27.05.04 1
> 29.05.04 2
> 31.05.04 5
> and i need to get a view that looks like this below :
> 27.05.04 1 1
> 29.05.04 2 3
> 31.05.04 5 8
> The third column shows a sum of numbers that were display before present
one
> ..
> I was trying build stored proc however I'm not so familiar with SQL so I
> failed.. :-(
> can anybody help ?
> thx in advance
> Krzysiek

It looks like you got an answer in another group - please do not post to
multiple groups independently.

Simon

Tuesday, March 20, 2012

Can this Stored Proc be more efficient?

Hey all,
Figured I'd get everyone's input on this. The stored proc below works
fine, no errors, however the ASP.NET page which calls it takes forever
to load (it averages 25 seconds per search). Anyone have any insight on
how I can boost the speed? 25-35 seconds is too dang long. For those
who want a full perspective: I have a sortable datagrid with custom
paging. On the site there is a textbox where one can search by
"containernum." As you can see below, the core of the search uses LIKE
'%'+@.con_num+'%' which is where the slowdown seems to occur. I have a
full-text index on the containernum field but perhaps I did it wrong
(it's the first time I've used that feature) because there appears to
be no gain in speed. Help! :-)
BTW, there are about a million records in my test table, the real table
has almost 5 million records, so I can just imagine the slowdown on
that one. :-(
CREATE PROCEDURE [Get_Data]
@.CurrentPage int,
@.PageSize int,
@.SortField nvarchar(50),
@.TotalRecords int output,
@.con_num nvarchar(8)
AS
SET NOCOUNT ON
CREATE TABLE #TempTable
(
ID int IDENTITY PRIMARY KEY,
uid uniqueidentifier NOT NULL,
event nvarchar(6) NOT NULL,
bookingnum nvarchar(50) NOT NULL,
vanowner nvarchar(6) NOT NULL,
containernum nvarchar(8) NULL,
tcn nvarchar(20) NULL,
poe nvarchar(50) NULL,
pod nvarchar(6) NULL,
shipname nvarchar(50) NULL,
vdn nvarchar(8) NULL,
eventlocation nvarchar(50) NOT NULL,
pcfn nvarchar(8) NULL
)
INSERT INTO #TempTable
(
uid,
event,
bookingnum,
vanowner,
containernum,
tcn,
poe,
pod,
shipname,
vdn,
eventlocation,
pcfn
)
SELECT
uid,
event,
bookingnum,
vanowner,
containernum,
tcn,
poe,
pod,
shipname,
vdn,
eventlocation,
pcfn
FROM
dbo.new315_itv
WHERE
containernum LIKE '%'+@.con_num+'%'
ORDER BY
CASE
WHEN @.SortField = 'event' THEN event
WHEN @.SortField = 'bookingnum' THEN bookingnum
WHEN @.SortField = 'containernum' THEN containernum
WHEN @.SortField = 'tcn' THEN tcn
WHEN @.SortField = 'poe' THEN poe
WHEN @.SortField = 'pod' THEN pod
WHEN @.SortField = 'shipname' THEN shipname
WHEN @.SortField = 'vdn' THEN vdn
WHEN @.SortField = 'eventlocation' THEN eventlocation
WHEN @.SortField = 'pcfn' THEN pcfn
END
DECLARE @.FirstRec int, @.LastRec int
SELECT @.FirstRec = (@.CurrentPage - 1) * @.PageSize
SELECT @.LastRec = (@.CurrentPage * @.PageSize + 1)
SELECT
uid,
event,
bookingnum,
vanowner,
containernum,
tcn,
poe,
pod,
shipname,
vdn,
eventlocation,
pcfn
FROM
#TempTable
WHERE
ID > @.FirstRec AND ID < @.LastRec
SELECT @.TotalRecords = COUNT(*) FROM #TempTable
GOANytime you use Like with a % at the beginning of the value, as in
containernum LIKE '%'+@.con_num+'%'
you automatically induce a full table scan. this is why your query is so
slow.
You need to extract the @.con_num portion of the data into a separate column
and index that... change the query so that it uses = instead of like, or at
least so that there is no % at the beginning...
Also the idea you are using of creating a temp table of all the values, and
then extracting only one pages wrth to return over the wire is a good one,
but you cancarry this a step furthur... Instead of using a temp table, use a
table variable, with an identity Primary Key RowNum, and ONLY put the keys
into this table variable... (You are incurring an enormous amount of
overhead right now stuffing ALL the data into the temp table, not just the
data you will eventually return to client)
Declare @.T Table (RowNum Integer Primary Key Identity Not Null,
PK Integer Not Null)
Insert @.T
Select uid
From ....
Then, at the end just use this table variable t o join back to your main
table, based on which StartRownNum and EndRowwNum defines the page you want
SELECT uid,event,bookingnum,
vanowner,containernum,tcn,
poe,pod,shipname,
vdn,eventlocation,pcfn
FROM new315_itv O Join @.T
On T.RowNum = O.uid
WHERE RowNum Between @.FirstRec AND @.LastRec
"roy.anderson@.gmail.com" wrote:

> Hey all,
> Figured I'd get everyone's input on this. The stored proc below works
> fine, no errors, however the ASP.NET page which calls it takes forever
> to load (it averages 25 seconds per search). Anyone have any insight on
> how I can boost the speed? 25-35 seconds is too dang long. For those
> who want a full perspective: I have a sortable datagrid with custom
> paging. On the site there is a textbox where one can search by
> "containernum." As you can see below, the core of the search uses LIKE
> '%'+@.con_num+'%' which is where the slowdown seems to occur. I have a
> full-text index on the containernum field but perhaps I did it wrong
> (it's the first time I've used that feature) because there appears to
> be no gain in speed. Help! :-)
> BTW, there are about a million records in my test table, the real table
> has almost 5 million records, so I can just imagine the slowdown on
> that one. :-(
>
> CREATE PROCEDURE [Get_Data]
> @.CurrentPage int,
> @.PageSize int,
> @.SortField nvarchar(50),
> @.TotalRecords int output,
> @.con_num nvarchar(8)
> AS
> SET NOCOUNT ON
> CREATE TABLE #TempTable
> (
> ID int IDENTITY PRIMARY KEY,
> uid uniqueidentifier NOT NULL,
> event nvarchar(6) NOT NULL,
> bookingnum nvarchar(50) NOT NULL,
> vanowner nvarchar(6) NOT NULL,
> containernum nvarchar(8) NULL,
> tcn nvarchar(20) NULL,
> poe nvarchar(50) NULL,
> pod nvarchar(6) NULL,
> shipname nvarchar(50) NULL,
> vdn nvarchar(8) NULL,
> eventlocation nvarchar(50) NOT NULL,
> pcfn nvarchar(8) NULL
> )
> INSERT INTO #TempTable
> (
> uid,
> event,
> bookingnum,
> vanowner,
> containernum,
> tcn,
> poe,
> pod,
> shipname,
> vdn,
> eventlocation,
> pcfn
> )
> SELECT
> uid,
> event,
> bookingnum,
> vanowner,
> containernum,
> tcn,
> poe,
> pod,
> shipname,
> vdn,
> eventlocation,
> pcfn
> FROM
> dbo.new315_itv
> WHERE
> containernum LIKE '%'+@.con_num+'%'
> ORDER BY
> CASE
> WHEN @.SortField = 'event' THEN event
> WHEN @.SortField = 'bookingnum' THEN bookingnum
> WHEN @.SortField = 'containernum' THEN containernum
> WHEN @.SortField = 'tcn' THEN tcn
> WHEN @.SortField = 'poe' THEN poe
> WHEN @.SortField = 'pod' THEN pod
> WHEN @.SortField = 'shipname' THEN shipname
> WHEN @.SortField = 'vdn' THEN vdn
> WHEN @.SortField = 'eventlocation' THEN eventlocation
> WHEN @.SortField = 'pcfn' THEN pcfn
> END
> DECLARE @.FirstRec int, @.LastRec int
> SELECT @.FirstRec = (@.CurrentPage - 1) * @.PageSize
> SELECT @.LastRec = (@.CurrentPage * @.PageSize + 1)
> SELECT
> uid,
> event,
> bookingnum,
> vanowner,
> containernum,
> tcn,
> poe,
> pod,
> shipname,
> vdn,
> eventlocation,
> pcfn
> FROM
> #TempTable
> WHERE
> ID > @.FirstRec AND ID < @.LastRec
> SELECT @.TotalRecords = COUNT(*) FROM #TempTable
> GO
>|||Hey CB,
Thanks for the terrific knowledge. I learned something new today! :-)
Having said that... while using a table variable has increased the
performance, it's only saved me 3 or 4 seconds on average. Here's the
weird thing, you would imagine that using LIKE '%'+@.con_num+'%' would
slow down the search, but it doesn't. In fact, the opposite occurs!
When I use LIKE @.con_num+'%' or LIKE '%'+@.con_num the average time is
27 seconds. When I use LIKE '%'+@.con_num+'%' the average time is 24
seconds.
I'm clueless as to why this is occuring. :-( The only thing I can
come up with is that the "containernum" field is a nvarchar and
includes a mishmash of char's and integers, which may be distorting the
scans somehow.|||This is an indication that your quuery optimizer has decided NOT to use the
index on containernum, and is still doing table scan... (There IS an index o
n
containernum , right ?)
This can happen when there are a large number of records which are a "match"
for the criteria you are passing in... If this query the only query you are
running on this table than you might cnsider mking the index on containernum
the clustered index.. That would improve perfoemce when using ...
containernum Where Like @.con_Num + '%'
What xactly is containernum, and what kind of values are stored in there?
"roy.anderson@.gmail.com" wrote:

> Hey CB,
> Thanks for the terrific knowledge. I learned something new today! :-)
> Having said that... while using a table variable has increased the
> performance, it's only saved me 3 or 4 seconds on average. Here's the
> weird thing, you would imagine that using LIKE '%'+@.con_num+'%' would
> slow down the search, but it doesn't. In fact, the opposite occurs!
> When I use LIKE @.con_num+'%' or LIKE '%'+@.con_num the average time is
> 27 seconds. When I use LIKE '%'+@.con_num+'%' the average time is 24
> seconds.
> I'm clueless as to why this is occuring. :-( The only thing I can
> come up with is that the "containernum" field is a nvarchar and
> includes a mishmash of char's and integers, which may be distorting the
> scans somehow.
>|||"containernum" is an nvarchar(8) field and contains various
alphanumeric characters. The length of entries in that field varies
from 1 to 8. I did have an clustered index on "containernum"
originally, but I was getting slow results and one of my coworkers
suggested enabled a Full-Text Index using "containernum." When I did
that it deleted the original "containernum" clustered index and
replaced it with a "UID" clustered index (UID is the PK for table
new315_itv). I'm currently using the full-text index. Should I delete
it and switch back to using the containernum clustered index?
Am I making sense? :-)|||<roy.anderson@.gmail.com> wrote in message
news:1112638709.920563.71130@.f14g2000cwb.googlegroups.com...
> "containernum" is an nvarchar(8) field and contains various
> alphanumeric characters. The length of entries in that field varies
> from 1 to 8. I did have an clustered index on "containernum"
> originally, but I was getting slow results and one of my coworkers
> suggested enabled a Full-Text Index using "containernum." When I did
> that it deleted the original "containernum" clustered index and
> replaced it with a "UID" clustered index (UID is the PK for table
> new315_itv). I'm currently using the full-text index. Should I delete
> it and switch back to using the containernum clustered index?
> Am I making sense? :-)
>
I think CBretana was suggesting that you reconsider you table design.
Specifically, the containernum column seems to contain composite data; the
container number plus whatever the alphabetic data represents. If these
pieces of data were separated out into discreet columns, the query analyzer
could take advantage of the index on the container_number column. In the
alternative, if you are unable to alter the table model, you might consider
creating an indexed view on the new315_itv table that includes a calculated
expression to extract the actual container number from the containernum
column. Here's a proof of concept on how to extract a number from an
alphanumeric string:
DECLARE @.s NVARCHAR(8)
SET @.s = 'abc123de'
SELECT SUBSTRING(
@.s,
PATINDEX('%[0-9]%',@.s),
CASE PATINDEX('%[0-9]',@.s)
WHEN 0 THEN PATINDEX('%[0-9][^0-9]%',@.s) - PATINDEX('%[0-9]%',@.s) + 1
ELSE PATINDEX('%[0-9]',@.s) - PATINDEX('%[0-9]%',@.s) + 1
END
)
Also, have you considered using VARCHAR instead of NVARCHAR for your textual
data. NVARCHAR requires twice the storage of VARCHAR an should only be used
if your textual data includes Unicode characters. Here's an article:
http://aspfaq.com/show.asp?id=2354
Finally, here's an article that compares various methods of paging through a
recordset. It includes an example of a stored procedure that makes use of a
temp table as well as other stored procedure examples with better
performance.
http://aspfaq.com/show.asp?id=2120
HTH
-Chris Hohmann|||I agree w/Chris... If you can, Redesign the table so that each discreet data
element is in it's own column... But yes, you should switch back to using
Clustered index on the column that the query predicate (Whats in the Where
Clause) uses... ESPECIALLY If the query extracts a range of values.
And that is what ...
Where X Like @.Value + '%' will be doing, since it translates to Where X >=
@.Value And <= @.Value + 'ZZZZZZZZZZZZZZZZZZZZZZZZ' (actually whatever the
Query Parser determines is the last possible value in sort order that will
satisfy the Like.)
"Chris Hohmann" wrote:

> <roy.anderson@.gmail.com> wrote in message
> news:1112638709.920563.71130@.f14g2000cwb.googlegroups.com...
> I think CBretana was suggesting that you reconsider you table design.
> Specifically, the containernum column seems to contain composite data; the
> container number plus whatever the alphabetic data represents. If these
> pieces of data were separated out into discreet columns, the query analyze
r
> could take advantage of the index on the container_number column. In the
> alternative, if you are unable to alter the table model, you might conside
r
> creating an indexed view on the new315_itv table that includes a calculate
d
> expression to extract the actual container number from the containernum
> column. Here's a proof of concept on how to extract a number from an
> alphanumeric string:
> DECLARE @.s NVARCHAR(8)
> SET @.s = 'abc123de'
> SELECT SUBSTRING(
> @.s,
> PATINDEX('%[0-9]%',@.s),
> CASE PATINDEX('%[0-9]',@.s)
> WHEN 0 THEN PATINDEX('%[0-9][^0-9]%',@.s) - PATINDEX('%[0-9]%',@.s) + 1
> ELSE PATINDEX('%[0-9]',@.s) - PATINDEX('%[0-9]%',@.s) + 1
> END
> )
> Also, have you considered using VARCHAR instead of NVARCHAR for your textu
al
> data. NVARCHAR requires twice the storage of VARCHAR an should only be use
d
> if your textual data includes Unicode characters. Here's an article:
> http://aspfaq.com/show.asp?id=2354
> Finally, here's an article that compares various methods of paging through
a
> recordset. It includes an example of a stored procedure that makes use of
a
> temp table as well as other stored procedure examples with better
> performance.
> http://aspfaq.com/show.asp?id=2120
>
> HTH
> -Chris Hohmann
>
>|||Thanks for the info you two. I'll be sure to check out those articles
Chris.
Actually, believe it or not, I have the Stored Proc at a comfortable
spot now. When a user searches using at least 3 characters, the results
return in under a second, when searches occur with less than 3
characters, the results can take up to 35 seconds to return (but this
warning is now noted on the site). See my new stored proc below. I
utilized CB's table variable idea but maintained the full-text index.
Basically (I believe) if one uses LIKE sqlserver looks for a regular
index, if one uses CONTAINS or FREETEXT sqlserver looks for a full-text
index. However, for whatever reason, I can't use CONTAINS on character
searches that contain less than 3 characters. It doesn't error out, it
just doesn't display anything. Probably an idiosyncrasy of full-text
searches.
CREATE PROCEDURE [Get_Data]
@.CurrentPage int,
@.PageSize int,
@.TotalRecords int output,
@.con_num nvarchar(8)
AS
SET NOCOUNT ON
DECLARE @.T Table
(
RowNum INTEGER PRIMARY KEY Identity NOT NULL,
PK UNIQUEIDENTIFIER NOT NULL
)
IF LEN(@.con_num) >= 3
BEGIN
SET @.con_num = '"'+@.con_num+'*"'
INSERT INTO @.T (PK)
SELECT
uid
FROM
dbo.new315_itv
WHERE CONTAINS (containernum, @.con_num)
END
ELSE
BEGIN
INSERT INTO @.T (PK)
SELECT
uid
FROM
dbo.new315_itv
WHERE containernum LIKE '%'+@.con_num+'%'
END
DECLARE @.FirstRec int, @.LastRec int
SELECT @.FirstRec = (@.CurrentPage - 1) * @.PageSize
SELECT @.LastRec = (@.CurrentPage * @.PageSize + 1)
SELECT
A.uid,
A.event,
A.bookingnum,
A.vanowner,
A.containernum,
A.tcn,
A.poe,
A.pod,
A.shipname,
A.vdn,
A.eventlocation,
A.pcfn
FROM
dbo.new315_itv A INNER JOIN @.T T ON T.PK = A.uid
WHERE
T.RowNum BETWEEN @.FirstRec AND @.LastRec
SELECT @.TotalRecords = COUNT(*) FROM @.T
GO

Can This Query Be Improved?

The query displayed below currently takes approximately 5-6 seconds to run in SQL Query Analyzer. It returns 685 rows. In my opinion, 5 seconds seems way to long, so I am wondering if there is a way to optimize this query. Does anyone have suggestions on what I could do to improve the performance of this query?

DECLARE @.SearchTerm varchar(200)
SET @.SearchTerm = 'john'

SET NOCOUNT ON

SELECT DISTINCT
SalesLead.SalesLeadID,
SalesLead.Prefix,
SalesLead.FirstName,
SalesLead.LastName,
SalesLead.Email,
SalesLead.Phone,
SalesLead.LastContact,
Schools.SchoolID,
Schools.SchoolName,
Schools.City AS 'SchoolCity'

FROM SalesLead
INNER JOIN jnSalesLeadSchool
ON SalesLead.SalesLeadID = jnSalesLeadSchool.SalesLeadID
INNER JOIN Schools
ON jnSalesLeadSchool.SchoolID = Schools.SchoolID
LEFT OUTER JOIN jnSalesLeadDepartment
ON SalesLead.SalesLeadID = jnSalesLeadDepartment.SalesLeadID
LEFT OUTER JOIN Department
ON jnSalesLeadDepartment.DepartmentID = Department.DepartmentID
LEFT OUTER JOIN jnSalesLeadOpportunity
ON SalesLead.SalesLeadID = jnSalesLeadOpportunity.SalesLeadID
LEFT OUTER JOIN AdoptionOpportunity
ON jnSalesLeadOpportunity.OpportunityID = AdoptionOpportunity.AdoptionOpportunityID
LEFT OUTER JOIN CourseNames
ON AdoptionOpportunity.CourseNameID = CourseNames.CourseNameID
LEFT OUTER JOIN SalesLeadNotes
ON SalesLead.SalesLeadID = SalesLeadNotes.SalesLeadID

WHERE
SalesLead.Active = 1
AND (
SalesLead.FirstName + ' ' + SalesLead.LastName LIKE '%' + @.SearchTerm + '%'
OR SalesLead.Address1 LIKE '%' + @.SearchTerm + '%'
OR SalesLead.City LIKE '%' + @.SearchTerm + '%'
OR SalesLead.Email LIKE '%' + @.SearchTerm + '%'
OR SalesLeadNotes.Note LIKE '%' + @.SearchTerm + '%'
OR Schools.SchoolName + ' - ' + Schools.City LIKE '%' + @.SearchTerm + '%'
OR Department.Name LIKE '%' + @.SearchTerm + '%'
OR CourseNames.CourseName LIKE '%' + @.SearchTerm + '%'
OR AdoptionOpportunity.Term LIKE '%' + @.SearchTerm + '%'
OR AdoptionOpportunity.Chances LIKE '%' + @.SearchTerm + '%'
)

ORDER BY SalesLead.LastName

Thanks in advance!
AaronPerhaps the execution plan has a hint. How much has the distinct? Don't know how expensive it really is. It might be an option to rewrite the whole thing to match the exact search-type so there's no OR-left.

Whats the rowcount without the where-clause?|||Creating an index on Saleslead.Active might help a bit. Enabling full text indexing (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_15_74oj.asp) will help a lot, but at a significant cost in disk space and INSERT/UPDATE performance.

-PatP|||According to the execution plan, the DISTINCT is costing 2%, but I need that to filter out the duplicate information that the OUTER joins cause.

The rowcount without the where clause is 15,617.

According to the execution plan, the two biggest costs are:
38% - SORT(Sorting the Input) - ARGUMENT: ORDER BY:(jnSalesLeadSchool.SalesLeadID ASC)

19% - INDEX SEEK(Scanning a particular range of rows from a non-clusted index) - OBJECT: SalesLeadNotes.IX_SalesLeadNotes.SalesLeadID, SEEK: SalesLeadNotes.SalesLeadID = SalesLead.SalesLeadID|||LIKE '%something'??

That's a scan everytime|||I understand that the wildcard matches are probably the biggest problem area, but is there any way around them besides a full text search?|||I was gonna say...

"Drop back and punt"...

Ah the Giants of yesteryear....

how does this do?

AND (
SalesLead.FirstName + ' ' + SalesLead.LastName
+ SalesLead.Address1
+ SalesLead.City
+ SalesLead.Email
+ SalesLeadNotes.Note
+ Schools.SchoolName + ' - ' + Schools.City
+ Department.Name
+ CourseNames.CourseName
+ AdoptionOpportunity.Term
+ AdoptionOpportunity.Chances
LIKE '%' + @.SearchTerm + '%'
)|||I was gonna say...

"Drop back and punt"...

Ah the Giants of yesteryear....

how does this do?

AND (
SalesLead.FirstName + ' ' + SalesLead.LastName
+ SalesLead.Address1
+ SalesLead.City
+ SalesLead.Email
+ SalesLeadNotes.Note
+ Schools.SchoolName + ' - ' + Schools.City
+ Department.Name
+ CourseNames.CourseName
+ AdoptionOpportunity.Term
+ AdoptionOpportunity.Chances
LIKE '%' + @.SearchTerm + '%'
)|||Brett,
I actually tried exactly that yesterday before going home. Here is the problem: if any of those fields are null (which some are), the entire string will default to NULL - which means rows are not matched. To combat this, I tried using an ISNULL(field, '') on each field while concatenating, but the overhead of the ISNULL cancelled out the gain.|||OK, then punt...

Or simplify the criteria...

or...and this is my last shot before FULLTEXT

DECLARE @.SearchTerm varchar(200)
SET @.SearchTerm = 'john'

SET NOCOUNT ON
SELECT DISTINCT * FROM (
SELECT
x.SalesLeadID,
x.Prefix,
x.FirstName,
x.LastName,
x.Email,
x.Phone,
x.LastContact,
x.SchoolID,
x.SchoolName,
x.City AS 'SchoolCity'

FROM ( SELECT * FROM SalesLead a
INNER JOIN jnSalesLeadSchool b
ON a.SalesLeadID = b.SalesLeadID
INNER JOIN Schools c
ON b.SchoolID = c.SchoolID

WHERE SalesLead.Active = 1
AND ( REPLACE(a.FirstName, @.SearchTerm, '') <> a.FirstName
OR REPLACE(a.LastName, @.SearchTerm, '') <> a.LastName
OR REPLACE(a.Address1, @.SearchTerm, '') <> a.Address1
OR REPLACE(a.City, @.SearchTerm, '') <> a.City
OR REPLACE(a.Email, @.SearchTerm, '') <> a.Email
OR REPLACE(c.SchoolName, @.SearchTerm, '') <> c.SchoolName
OR REPLACE(c.City, @.SearchTerm, '') <> c.City
) AS X
LEFT OUTER JOIN jnSalesLeadDepartment d
ON X.SalesLeadID = d.SalesLeadID
LEFT OUTER JOIN jnSalesLeadOpportunity f
ON X.SalesLeadID = f.SalesLeadID
LEFT OUTER JOIN SalesLeadNotes i
ON X.SalesLeadID = i.SalesLeadID
LEFT OUTER JOIN Department e
ON X.DepartmentID = e.DepartmentID
LEFT OUTER JOIN AdoptionOpportunity g
ON X.OpportunityID = g.AdoptionOpportunityID
LEFT OUTER JOIN CourseNames h
ON g.CourseNameID = h.CourseNameID

WHERE
OR REPLACE(i.Note, @.SearchTerm, '') <> i.Note
OR REPLACE(e.[Name], @.SearchTerm, '') <> e.[Name]
OR REPLACE(h.CourseName, @.SearchTerm, '') <> h.CourseName
OR REPLACE(g.Term, @.SearchTerm, '') <> g.Term
OR REPLACE(g.Chances, @.SearchTerm, '') <> g.Chances
) AS XXX
ORDER BY LastName|||Thanks Brett, but that query takes exactly the same amount of time. I like your idea of using Replace rather than the wildcard comparison, that was clever.

It looks like fulltext is my last resort - are there any rules agains using fulltext searching on columns that contain such a small amount of text (usually less than 100 characters)?|||ow btw: why the order by on a distinct ?|||What you want is really a full-text search. We are beating this mouse bloody when you really want an ox. Byte the bullet and move on!

-PatP

Sunday, March 11, 2012

Can table1 in example below get updated by another process while the transaction is in pro

I am afraid that just after @.statusOfEmployee is retrieved from table1, but before table2 is updated, someone else (a second user) calls this same stored procedure and changes the @.statusOfEmployee value. This would create aninconsistentupdate of table2 by first user, since the update of table2 'might' not have gone ahead if the latest value of @.statusOF Employee was used. CAN SOMEONE PLEASE HELP ME WITH THIS SITUATION AND HOW I CAN BE SURE THAT ABOVE DOES NOT HAPPEN SINCE MULTIPLE USERS WILL BE HITTING THIS STORED PROCEDURE?

declare @.status int
begin tran
set @.status = (select statusOfEmployee from table1)
if @.@.ERROR = 0
begin
update table2
set destination = @.destination /* @.destination is an input parameter passed to the sp*/
where @.currentStatus = @.status
if @.ERROR = 0
commit tran
else
rollback tran
end
else
rollback tran

return

update table2
set destination = @.destination /* @.destination is an input parameter passed to the sp*/
where @.currentStatus = (select @.statusOfEmployee from table1)

This will only work if you have only 1 row in table1, but I'm guessing this isn't your real SP. If there can be more than 1, then you have other issues. And to answer your question, yes, it could have been updated between those statements.

|||

Sorryy. I meant a field there. I will edit it.

One question for you: If I used the original sp I mentioned in my post, then is there a danger of incosistent update as I have explained OR because the select is in a transaction, SQL Server will prevent any changes to tables being used in the transaction?

I was going to mark the ADO.Net code that calls this stored procedure as 'critical' in my C# code using lock(this) { }. That way only one user can execute this stored procedure at a time from the application, which eliminates any chances of inconsistent updates. ANYONE HAS ANY COMMENTS ON USING THIS APPROACH TO PREVENT INCOSISTENT UPDATES?

|||

I would think even with your approach, since the select and update are on different tables, there is nothing preventing another user from updating the table used in select statement. SQL Server willonlyprevent any user from updating 'table2' while this update statement is in progress.

|||

Yes, your original SP isn't multi-user safe. No, the one I gave you is. The difference is the type of locks that are requested and the duration for which they are held.

|||

So even though in your query a simple select is being executed on 'table1', SQL Server will place an exclusive lock on 'table1' row.

I thought that under default SQL Server 2000 locking (read committed), select statements will not place an exclusive lock on the row involved in select query. And if this is true, then including the 'select' within the 'update' will still allow someone else to update 'table1' before the update to 'tabl2' happens. Right or wrong?

|||

Because the select is happening within the confines of the UPDATE statement, the subquery will place a read-lock (sharing) on table1's row during the entire time the update is occuring. The update can not happen without this read-lock, and no other updates can happen to table1 while this statement has the read-lock in place.

The real issue that your prior SP had was that the read-lock on table 1 was released as soon as the SET @.var= was completed, which would allow someone to update table1 before table2 was updated. You could accomplish nearly the same thing with some of the locking hints, or changing the transaction isolation mode, but the SP won't execute as quickly as incorporating it into one statement like I did, and that means locks are being held longer than they need to be.

SET @.var=(SELECT ... WITH (HOLDLOCK) ...)

would have accomplished the same thing. If you add more steps to your transaction, then the read lock will be held until the completion of the transaction. With the combined UPDATE, the read lock is dropped when the UPDATE completes (The update lock on table2 is held until the end of the UPDATE if it's not in a transaction, or until the transaction is commited/rolled back...).

|||

Great explanation. It helped clarify an important point to me.

Thanks for that.

Can table ownership be changed?

When I create tables in SQL I can specify dbo as the owner using the
syntax below but when using the upsize wizard in Access 2000 I own
everything. Is there a way that the system administrator can change
ownership after the fact?

CREATE TABLE dbo.mytable (c1 int not null)

Thank You,
Randy K
wawork@.hotmail.comOn 18 May 2004 12:16:39 -0700, Randy wrote:

>When I create tables in SQL I can specify dbo as the owner using the
>syntax below but when using the upsize wizard in Access 2000 I own
>everything. Is there a way that the system administrator can change
>ownership after the fact?
>CREATE TABLE dbo.mytable (c1 int not null)
>
>Thank You,
> Randy K
>wawork@.hotmail.com

Hi Randy,

sp_changeobjectowner [ @.objname = ] 'object' , [ @.newowner = ] 'owner'

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Thursday, February 16, 2012

Can Reporting Server do this?

I have a simple database see below and I would like the outcome group by
month with subtotal. How do I write the query and then use Reporting Server
to genereate a report like the following?
Data in database:
--
Datetime Amount
1/1/2005 1400
1/1/2005 1600
2/1/2005 800
2/5/2005 600
Report outcome:
--
January
1/1/2005 1400
1/1/2005 1600
Subtotal : 3000
Febuary
2/1/2005 800
2/5/2005 600
Subtotal: 1400Create a report containing the dataset from your database. Create a field
that only shows the month of your DateTime and then group it by that field
(Month) and include subtotals.
If you create a view that strips out the month in your datetime, then you
should be able to achieve your result by using the report wizard.
I know Crystal Reports has the ability to group by datetime based on
month/week/daily basis, but I am not sure if that functionality is in RS.
"Zean Smith" <nospam@.nospamaaamail.com> wrote in message
news:UqqdnbKEK4HT5g3eRVn-jA@.rogers.com...
>I have a simple database see below and I would like the outcome group by
>month with subtotal. How do I write the query and then use Reporting
>Server to genereate a report like the following?
> Data in database:
> --
> Datetime Amount
> 1/1/2005 1400
> 1/1/2005 1600
> 2/1/2005 800
> 2/5/2005 600
>
> Report outcome:
> --
> January
> 1/1/2005 1400
> 1/1/2005 1600
> Subtotal : 3000
> Febuary
> 2/1/2005 800
> 2/5/2005 600
> Subtotal: 1400
>|||Thanks Pedro!! To help other people.. here is the Query I used:
By using DataName and DatePart in SQL query:
SELECT DATENAME(mm, DateTime) + ', ' + CAST(DATENAME(yyyy, DateTime) AS
varchar(4)) AS MonthYearName, DATEPART(yyyy, DateTime) AS Year,
DATEPART(mm, DateTime) AS Month, *
FROM CorporateSales
Then, in Reporting Server, add the GROUP and then group the data by Month,
and by Year.
Add "MonthYearName" in header.
Add "Subtotal" in footer.
I will be able to show exactly what I wanted in the first place.
"Pedro" <pedro@.newsgroups.nospam> wrote in message
news:e16lK2J%23FHA.3308@.TK2MSFTNGP11.phx.gbl...
> Create a report containing the dataset from your database. Create a field
> that only shows the month of your DateTime and then group it by that field
> (Month) and include subtotals.
> If you create a view that strips out the month in your datetime, then you
> should be able to achieve your result by using the report wizard.
> I know Crystal Reports has the ability to group by datetime based on
> month/week/daily basis, but I am not sure if that functionality is in RS.
> "Zean Smith" <nospam@.nospamaaamail.com> wrote in message
> news:UqqdnbKEK4HT5g3eRVn-jA@.rogers.com...
>>I have a simple database see below and I would like the outcome group by
>>month with subtotal. How do I write the query and then use Reporting
>>Server to genereate a report like the following?
>> Data in database:
>> --
>> Datetime Amount
>> 1/1/2005 1400
>> 1/1/2005 1600
>> 2/1/2005 800
>> 2/5/2005 600
>>
>> Report outcome:
>> --
>> January
>> 1/1/2005 1400
>> 1/1/2005 1600
>> Subtotal : 3000
>> Febuary
>> 2/1/2005 800
>> 2/5/2005 600
>> Subtotal: 1400
>>
>

Sunday, February 12, 2012

Can not save relationship

Hi
I am using Sqlserver 2005 and in management I created some relations.
One of them fale with the below error.
I have tried to look for unmatched data and dublicates etc, but still keep
gettitng this error message.
What do you recommend me to do?
Thank you in advance
- Unable to create relationship 'FK_Transaktionsrader_Transaktionhuvud'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint
"FK_Transaktionsrader_Transaktionhuvud".
The conflict occurred in database "MbaseMuseumServerNetSQL", table
"dbo.Transaktionhuvud", column 'TransaktionhuvudTransaktionsnr'.
It would help us better assist you if you could include table DDL for both
tables; without it, we cannot help you. (For help with that refer to:
http://www.aspfaq.com/5006 and to
http://classicasp.aspfaq.com/general...-answered.html )
The less 'set up' work we have to do, the more likely you are going to have
folks tackle your problem and help you. Without this effort from you, we are
just playing guessing games.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Mattias" <Mattias@.discussions.microsoft.com> wrote in message
news:3EAD7DFC-C005-4415-A17F-5FD29EF8D4A2@.microsoft.com...
> Hi
> I am using Sqlserver 2005 and in management I created some relations.
> One of them fale with the below error.
> I have tried to look for unmatched data and dublicates etc, but still keep
> gettitng this error message.
> What do you recommend me to do?
> Thank you in advance
> - Unable to create relationship 'FK_Transaktionsrader_Transaktionhuvud'.
> The ALTER TABLE statement conflicted with the FOREIGN KEY constraint
> "FK_Transaktionsrader_Transaktionhuvud".
> The conflict occurred in database "MbaseMuseumServerNetSQL", table
> "dbo.Transaktionhuvud", column 'TransaktionhuvudTransaktionsnr'.
|||Hi and thanks for your reply.
I have the DDL for both tables ready here.
Can it be attached to the post or shall I send it separatly somewhere?
I tried to use sql code you recommend to generate inserts, sample data but I
receive the error below
"Could not find stored procedure sp_generate_inserts'." What do you
recommend me to do?
Mattias
"Arnie Rowland" wrote:

> It would help us better assist you if you could include table DDL for both
> tables; without it, we cannot help you. (For help with that refer to:
> http://www.aspfaq.com/5006 and to
> http://classicasp.aspfaq.com/general...-answered.html )
>
> The less 'set up' work we have to do, the more likely you are going to have
> folks tackle your problem and help you. Without this effort from you, we are
> just playing guessing games.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Mattias" <Mattias@.discussions.microsoft.com> wrote in message
> news:3EAD7DFC-C005-4415-A17F-5FD29EF8D4A2@.microsoft.com...
>
>
|||Please include (copy and paste) the DDL in a post.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Mattias" <Mattias@.discussions.microsoft.com> wrote in message
news:14FF1450-A7AB-4D25-BE5C-4D75B50845F7@.microsoft.com...[vbcol=seagreen]
> Hi and thanks for your reply.
> I have the DDL for both tables ready here.
> Can it be attached to the post or shall I send it separatly somewhere?
> I tried to use sql code you recommend to generate inserts, sample data but
> I
> receive the error below
> "Could not find stored procedure sp_generate_inserts'." What do you
> recommend me to do?
> Mattias
>
> "Arnie Rowland" wrote:
|||Ok here it comes!
Mattias
USE [MbaseMuseumServerNetSQL]
GO
/****** Objekt: Table [dbo].[Transaktionhuvud] Skriptdatum: 10/09/2006
16:13:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Transaktionhuvud](
[TransaktionhuvudTransaktionsnr] [int] IDENTITY(1,1) NOT NULL,
[Transaktionskategorinummer] [int] NULL,
[TransaktionhuvudTransaktionhuvudKontaktnr] [int] NULL,
[ForetagsregisterGRUNDNR] [int] NULL,
[TransaktionhuvudTransaktionhuvudAnstalldHandlagge snr] [int] NULL,
[TransaktionhuvudTransaktionhuvudAnstalldFramtages nr] [int] NULL,
[TransaktionhuvudTransaktionhuvudAnstalldAvsynasnr ] [int] NULL,
[TransaktionhuvudTransaktionhuvudAnstalldPackas nr] [int] NULL,
[TransaktionhuvudTransaktionhuvudAnstalldKurirn r] [int] NULL,
[UtskriftsstatusSTATUSNR] [int] NULL,
[BetalningsStatusSTATUSNR] [int] NULL,
[TransaktionhuvudRegistreringsdatum] [datetime] NULL,
[DatumPreliminarRetur] [datetime] NULL,
[UtstallningStartdatum] [datetime] NULL,
[UtstallningSlutdatum] [datetime] NULL,
[Utstallning] [nvarchar](70) COLLATE Finnish_Swedish_CI_AS NULL,
[TransaktionhuvudTransaktionhuvudKontaktTransporto rAvhamtningnr] [int] NULL,
[TransaktionhuvudTransaktionhuvudKontaktTransporto rReturnr] [int] NULL,
[TransaktionhuvudTransaktionhuvudTransportsattAvha mtningnr] [int] NULL,
[TransaktionhuvudTransaktionhuvudTransportsattRetu rnr] [int] NULL,
[TransaktionhuvudDefinitivRetur] [bit] NULL,
[TransaktionhuvudDatumDefinitivRetur] [datetime] NULL,
[TransaktionhuvudAnmarkningar] [nvarchar](max) COLLATE
Finnish_Swedish_CI_AS NULL,
PRIMARY KEY CLUSTERED
(
[TransaktionhuvudTransaktionsnr] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
[ITransaktionhuvud] FOREIGN
KEY([TransaktionhuvudTransaktionhuvudKontaktTransp ortorAvhamtningnr])
REFERENCES [dbo].[Kontakter] ([Kontaktnummer])
GO
ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud]
GO
ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
[ITransaktionhuvud1] FOREIGN
KEY([TransaktionhuvudTransaktionhuvudKontaktTransp ortorReturnr])
REFERENCES [dbo].[Kontakter] ([Kontaktnummer])
GO
ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud1]
GO
ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
[ITransaktionhuvud10] FOREIGN
KEY([TransaktionhuvudTransaktionhuvudAnstalldAvsyn asnr])
REFERENCES [dbo].[Anstallda] ([AnstalldaAnstalldnr])
GO
ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud10]
GO
ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
[ITransaktionhuvud11] FOREIGN
KEY([TransaktionhuvudTransaktionhuvudAnstalldPacka snr])
REFERENCES [dbo].[Anstallda] ([AnstalldaAnstalldnr])
GO
ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud11]
GO
ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
[ITransaktionhuvud12] FOREIGN
KEY([TransaktionhuvudTransaktionhuvudAnstalldKurir nr])
REFERENCES [dbo].[Anstallda] ([AnstalldaAnstalldnr])
GO
ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud12]
GO
ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
[ITransaktionhuvud13] FOREIGN KEY([TransaktionhuvudTransaktionhuvudKontaktnr])
REFERENCES [dbo].[Kontakter] ([Kontaktnummer])
GO
ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud13]
GO
ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
[ITransaktionhuvud2] FOREIGN KEY([BetalningsStatusSTATUSNR])
REFERENCES [dbo].[BetalningsStatus] ([BetalningsStatusSTATUSNR])
GO
ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud2]
GO
ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
[ITransaktionhuvud3] FOREIGN KEY([UtskriftsstatusSTATUSNR])
REFERENCES [dbo].[Utskriftsstatus] ([UtskriftsstatusSTATUSNR])
GO
ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud3]
GO
ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
[ITransaktionhuvud4] FOREIGN KEY([ForetagsregisterGRUNDNR])
REFERENCES [dbo].[Foretagsregister] ([ForetagsregisterGRUNDNR])
GO
ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud4]
GO
ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
[ITransaktionhuvud5] FOREIGN KEY([Transaktionskategorinummer])
REFERENCES [dbo].[Transaktionskategori] ([Transaktionskategorinummer])
GO
ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud5]
GO
ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
[ITransaktionhuvud6] FOREIGN
KEY([TransaktionhuvudTransaktionhuvudTransportsatt Avhamtningnr])
REFERENCES [dbo].[TRANSPORTSATT] ([TRANSPORTSATTNR])
GO
ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud6]
GO
ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
[ITransaktionhuvud7] FOREIGN
KEY([TransaktionhuvudTransaktionhuvudTransportsatt Returnr])
REFERENCES [dbo].[TRANSPORTSATT] ([TRANSPORTSATTNR])
GO
ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud7]
GO
ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
[ITransaktionhuvud8] FOREIGN
KEY([TransaktionhuvudTransaktionhuvudAnstalldHandl aggesnr])
REFERENCES [dbo].[Anstallda] ([AnstalldaAnstalldnr])
GO
ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud8]
GO
ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
[ITransaktionhuvud9] FOREIGN
KEY([TransaktionhuvudTransaktionhuvudAnstalldFramt agesnr])
REFERENCES [dbo].[Anstallda] ([AnstalldaAnstalldnr])
GO
ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud9]
USE [MbaseMuseumServerNetSQL]
GO
/****** Objekt: Table [dbo].[Transaktionsrader] Skriptdatum: 10/09/2006
16:17:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Transaktionsrader](
[TransaktionsraderTransaktionsraderTransaktionsrad ] [int] IDENTITY(1,1) NOT
NULL,
[TransaktionsraderTransaktionsnr] [int] NULL,
[TransaktionsraderTransaktionsraderAvgiftsnr] [int] NULL,
[TransaktionsraderTransaktionsraderAvgiftsnr2] [int] NULL,
[TransaktionsraderTransaktionsraderAvgiftsnr3] [int] NULL,
[TransaktionsraderTransaktionsraderAvgiftsnr4] [int] NULL,
[TransaktionsraderTransaktionsraderEmballagenr] [int] NULL,
[Artikelnummer] [int] NULL,
[Bildnr] [int] NULL,
[TransaktionsraderForetagsnr] [int] NULL,
[TransaktionsraderAntal] [int] NULL,
[ExaktPlacering] [nvarchar](200) COLLATE Finnish_Swedish_CI_AS NULL,
[TillfalligRetur] [bit] NOT NULL,
[DatumTillfalligRetur] [datetime] NULL,
[TransaktionsraderForsakringsvarde] [decimal](17, 6) NULL,
[TransaktionsraderDefinitivRetur] [bit] NOT NULL,
[TransaktionsraderDatumDefinitivRetur] [datetime] NULL,
[AterDeposition] [bit] NOT NULL,
[DatumHamtningUtlamning] [datetime] NOT NULL,
[Undervisningstypnr] [int] NULL,
[DatumVisningStart] [datetime] NULL,
[TidVisningStart] [datetime] NULL,
[DatumVisningSlut] [datetime] NULL,
[TidVisningSlut] [datetime] NULL,
[Amne] [nvarchar](50) COLLATE Finnish_Swedish_CI_AS NULL,
[AntalLektioner] [nvarchar](50) COLLATE Finnish_Swedish_CI_AS NULL,
[AntalPersoner] [nvarchar](50) COLLATE Finnish_Swedish_CI_AS NULL,
[VisningInstalld] [bit] NULL,
[DatumVisningInstalld] [datetime] NULL,
[VisningUtford] [bit] NULL,
[transkontaktnr] [int] NULL,
[TransraderAnstalldGuideAnstalldnr] [int] NULL,
[accessionsnummer] [nvarchar](20) COLLATE Finnish_Swedish_CI_AS NULL,
PRIMARY KEY CLUSTERED
(
[TransaktionsraderTransaktionsraderTransaktionsrad ] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
[FK_Transaktionsrader_Master] FOREIGN KEY([accessionsnummer])
REFERENCES [dbo].[Master] ([MasterAccessionsnr])
GO
ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT
[FK_Transaktionsrader_Master]
GO
ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
[ITransaktionsrader] FOREIGN KEY([Artikelnummer])
REFERENCES [dbo].[Artiklar] ([Artikelnummer])
GO
ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader]
GO
ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
[ITransaktionsrader10] FOREIGN KEY([transkontaktnr])
REFERENCES [dbo].[Kontakter] ([Kontaktnummer])
GO
ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader10]
GO
ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
[ITransaktionsrader2] FOREIGN
KEY([TransaktionsraderTransaktionsraderEmballagenr ])
REFERENCES [dbo].[Emballage] ([Emballagenr])
GO
ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader2]
GO
ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
[ITransaktionsrader3] FOREIGN
KEY([TransaktionsraderTransaktionsraderAvgiftsnr])
REFERENCES [dbo].[Avgifter] ([Avgiftsnr])
GO
ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader3]
GO
ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
[ITransaktionsrader4] FOREIGN
KEY([TransaktionsraderTransaktionsraderAvgiftsnr2])
REFERENCES [dbo].[Avgifter] ([Avgiftsnr])
GO
ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader4]
GO
ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
[ITransaktionsrader5] FOREIGN
KEY([TransaktionsraderTransaktionsraderAvgiftsnr3])
REFERENCES [dbo].[Avgifter] ([Avgiftsnr])
GO
ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader5]
GO
ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
[ITransaktionsrader6] FOREIGN
KEY([TransaktionsraderTransaktionsraderAvgiftsnr4])
REFERENCES [dbo].[Avgifter] ([Avgiftsnr])
GO
ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader6]
GO
ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
[ITransaktionsrader7] FOREIGN KEY([Undervisningstypnr])
REFERENCES [dbo].[UNDERVISNINGSTYP] ([Undervisningstypnr])
GO
ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader7]
GO
ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
[ITransaktionsrader8] FOREIGN KEY([Bildnr])
REFERENCES [dbo].[Bilder] ([Bildnr])
GO
ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader8]
GO
ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
[ITransaktionsrader9] FOREIGN KEY([TransraderAnstalldGuideAnstalldnr])
REFERENCES [dbo].[Anstallda] ([AnstalldaAnstalldnr])
GO
ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader9]
"Arnie Rowland" wrote:

> Please include (copy and paste) the DDL in a post.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Mattias" <Mattias@.discussions.microsoft.com> wrote in message
> news:14FF1450-A7AB-4D25-BE5C-4D75B50845F7@.microsoft.com...
>
>
|||Mattias,
Try this. Use your script and create the two tables on
another test server or some test database.
Then run the script to create the relationship you are
having problems creating - or use Management Studio if
that's how you were doing it.
If you can create the relationship and don't get an error
then that tells you it's likely related to the data in the
two tables violating the constraint. If you can't create the
relationship from Management Studio, hit the script button
(hopefully there is one in the GUI end...don't use that much
so I don't remember) and try just executing the script. If
you still get an error using the script and with no data,
then post the script here.
-Sue
On Mon, 9 Oct 2006 07:20:02 -0700, Mattias
<Mattias@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Ok here it comes!
>Mattias
>USE [MbaseMuseumServerNetSQL]
>GO
>/****** Objekt: Table [dbo].[Transaktionhuvud] Skriptdatum: 10/09/2006
>16:13:57 ******/
>SET ANSI_NULLS ON
>GO
>SET QUOTED_IDENTIFIER ON
>GO
>CREATE TABLE [dbo].[Transaktionhuvud](
>[TransaktionhuvudTransaktionsnr] [int] IDENTITY(1,1) NOT NULL,
>[Transaktionskategorinummer] [int] NULL,
>[TransaktionhuvudTransaktionhuvudKontaktnr] [int] NULL,
>[ForetagsregisterGRUNDNR] [int] NULL,
>[TransaktionhuvudTransaktionhuvudAnstalldHandlagge snr] [int] NULL,
>[TransaktionhuvudTransaktionhuvudAnstalldFramtages nr] [int] NULL,
>[TransaktionhuvudTransaktionhuvudAnstalldAvsynasnr ] [int] NULL,
>[TransaktionhuvudTransaktionhuvudAnstalldPackas nr] [int] NULL,
>[TransaktionhuvudTransaktionhuvudAnstalldKurirn r] [int] NULL,
>[UtskriftsstatusSTATUSNR] [int] NULL,
>[BetalningsStatusSTATUSNR] [int] NULL,
>[TransaktionhuvudRegistreringsdatum] [datetime] NULL,
>[DatumPreliminarRetur] [datetime] NULL,
>[UtstallningStartdatum] [datetime] NULL,
>[UtstallningSlutdatum] [datetime] NULL,
>[Utstallning] [nvarchar](70) COLLATE Finnish_Swedish_CI_AS NULL,
>[TransaktionhuvudTransaktionhuvudKontaktTransporto rAvhamtningnr] [int] NULL,
>[TransaktionhuvudTransaktionhuvudKontaktTransporto rReturnr] [int] NULL,
>[TransaktionhuvudTransaktionhuvudTransportsattAvha mtningnr] [int] NULL,
>[TransaktionhuvudTransaktionhuvudTransportsattRetu rnr] [int] NULL,
>[TransaktionhuvudDefinitivRetur] [bit] NULL,
>[TransaktionhuvudDatumDefinitivRetur] [datetime] NULL,
>[TransaktionhuvudAnmarkningar] [nvarchar](max) COLLATE
>Finnish_Swedish_CI_AS NULL,
>PRIMARY KEY CLUSTERED
>(
>[TransaktionhuvudTransaktionsnr] ASC
>)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
>) ON [PRIMARY]
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
>[ITransaktionhuvud] FOREIGN
>KEY([TransaktionhuvudTransaktionhuvudKontaktTrans portorAvhamtningnr])
>REFERENCES [dbo].[Kontakter] ([Kontaktnummer])
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud]
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
>[ITransaktionhuvud1] FOREIGN
>KEY([TransaktionhuvudTransaktionhuvudKontaktTrans portorReturnr])
>REFERENCES [dbo].[Kontakter] ([Kontaktnummer])
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud1]
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
>[ITransaktionhuvud10] FOREIGN
>KEY([TransaktionhuvudTransaktionhuvudAnstalldAvsy nasnr])
>REFERENCES [dbo].[Anstallda] ([AnstalldaAnstalldnr])
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud10]
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
>[ITransaktionhuvud11] FOREIGN
>KEY([TransaktionhuvudTransaktionhuvudAnstalldPack asnr])
>REFERENCES [dbo].[Anstallda] ([AnstalldaAnstalldnr])
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud11]
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
>[ITransaktionhuvud12] FOREIGN
>KEY([TransaktionhuvudTransaktionhuvudAnstalldKuri rnr])
>REFERENCES [dbo].[Anstallda] ([AnstalldaAnstalldnr])
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud12]
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
>[ITransaktionhuvud13] FOREIGN KEY([TransaktionhuvudTransaktionhuvudKontaktnr])
>REFERENCES [dbo].[Kontakter] ([Kontaktnummer])
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud13]
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
>[ITransaktionhuvud2] FOREIGN KEY([BetalningsStatusSTATUSNR])
>REFERENCES [dbo].[BetalningsStatus] ([BetalningsStatusSTATUSNR])
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud2]
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
>[ITransaktionhuvud3] FOREIGN KEY([UtskriftsstatusSTATUSNR])
>REFERENCES [dbo].[Utskriftsstatus] ([UtskriftsstatusSTATUSNR])
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud3]
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
>[ITransaktionhuvud4] FOREIGN KEY([ForetagsregisterGRUNDNR])
>REFERENCES [dbo].[Foretagsregister] ([ForetagsregisterGRUNDNR])
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud4]
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
>[ITransaktionhuvud5] FOREIGN KEY([Transaktionskategorinummer])
>REFERENCES [dbo].[Transaktionskategori] ([Transaktionskategorinummer])
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud5]
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
>[ITransaktionhuvud6] FOREIGN
>KEY([TransaktionhuvudTransaktionhuvudTransportsat tAvhamtningnr])
>REFERENCES [dbo].[TRANSPORTSATT] ([TRANSPORTSATTNR])
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud6]
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
>[ITransaktionhuvud7] FOREIGN
>KEY([TransaktionhuvudTransaktionhuvudTransportsat tReturnr])
>REFERENCES [dbo].[TRANSPORTSATT] ([TRANSPORTSATTNR])
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud7]
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
>[ITransaktionhuvud8] FOREIGN
>KEY([TransaktionhuvudTransaktionhuvudAnstalldHand laggesnr])
>REFERENCES [dbo].[Anstallda] ([AnstalldaAnstalldnr])
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud8]
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
>[ITransaktionhuvud9] FOREIGN
>KEY([TransaktionhuvudTransaktionhuvudAnstalldFram tagesnr])
>REFERENCES [dbo].[Anstallda] ([AnstalldaAnstalldnr])
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud9]
>
>USE [MbaseMuseumServerNetSQL]
>GO
>/****** Objekt: Table [dbo].[Transaktionsrader] Skriptdatum: 10/09/2006
>16:17:38 ******/
>SET ANSI_NULLS ON
>GO
>SET QUOTED_IDENTIFIER ON
>GO
>CREATE TABLE [dbo].[Transaktionsrader](
>[TransaktionsraderTransaktionsraderTransaktionsrad ] [int] IDENTITY(1,1) NOT
>NULL,
>[TransaktionsraderTransaktionsnr] [int] NULL,
>[TransaktionsraderTransaktionsraderAvgiftsnr] [int] NULL,
>[TransaktionsraderTransaktionsraderAvgiftsnr2] [int] NULL,
>[TransaktionsraderTransaktionsraderAvgiftsnr3] [int] NULL,
>[TransaktionsraderTransaktionsraderAvgiftsnr4] [int] NULL,
>[TransaktionsraderTransaktionsraderEmballagenr] [int] NULL,
>[Artikelnummer] [int] NULL,
>[Bildnr] [int] NULL,
>[TransaktionsraderForetagsnr] [int] NULL,
>[TransaktionsraderAntal] [int] NULL,
>[ExaktPlacering] [nvarchar](200) COLLATE Finnish_Swedish_CI_AS NULL,
>[TillfalligRetur] [bit] NOT NULL,
>[DatumTillfalligRetur] [datetime] NULL,
>[TransaktionsraderForsakringsvarde] [decimal](17, 6) NULL,
>[TransaktionsraderDefinitivRetur] [bit] NOT NULL,
>[TransaktionsraderDatumDefinitivRetur] [datetime] NULL,
>[AterDeposition] [bit] NOT NULL,
>[DatumHamtningUtlamning] [datetime] NOT NULL,
>[Undervisningstypnr] [int] NULL,
>[DatumVisningStart] [datetime] NULL,
>[TidVisningStart] [datetime] NULL,
>[DatumVisningSlut] [datetime] NULL,
>[TidVisningSlut] [datetime] NULL,
>[Amne] [nvarchar](50) COLLATE Finnish_Swedish_CI_AS NULL,
>[AntalLektioner] [nvarchar](50) COLLATE Finnish_Swedish_CI_AS NULL,
>[AntalPersoner] [nvarchar](50) COLLATE Finnish_Swedish_CI_AS NULL,
>[VisningInstalld] [bit] NULL,
>[DatumVisningInstalld] [datetime] NULL,
>[VisningUtford] [bit] NULL,
>[transkontaktnr] [int] NULL,
>[TransraderAnstalldGuideAnstalldnr] [int] NULL,
>[accessionsnummer] [nvarchar](20) COLLATE Finnish_Swedish_CI_AS NULL,
>PRIMARY KEY CLUSTERED
>(
>[TransaktionsraderTransaktionsraderTransaktionsrad ] ASC
>)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
>) ON [PRIMARY]
>GO
>ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
>[FK_Transaktionsrader_Master] FOREIGN KEY([accessionsnummer])
>REFERENCES [dbo].[Master] ([MasterAccessionsnr])
>GO
>ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT
>[FK_Transaktionsrader_Master]
>GO
>ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
>[ITransaktionsrader] FOREIGN KEY([Artikelnummer])
>REFERENCES [dbo].[Artiklar] ([Artikelnummer])
>GO
>ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader]
>GO
>ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
>[ITransaktionsrader10] FOREIGN KEY([transkontaktnr])
>REFERENCES [dbo].[Kontakter] ([Kontaktnummer])
>GO
>ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader10]
>GO
>ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
>[ITransaktionsrader2] FOREIGN
>KEY([TransaktionsraderTransaktionsraderEmballagen r])
>REFERENCES [dbo].[Emballage] ([Emballagenr])
>GO
>ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader2]
>GO
>ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
>[ITransaktionsrader3] FOREIGN
>KEY([TransaktionsraderTransaktionsraderAvgiftsn r])
>REFERENCES [dbo].[Avgifter] ([Avgiftsnr])
>GO
>ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader3]
>GO
>ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
>[ITransaktionsrader4] FOREIGN
>KEY([TransaktionsraderTransaktionsraderAvgiftsnr2 ])
>REFERENCES [dbo].[Avgifter] ([Avgiftsnr])
>GO
>ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader4]
>GO
>ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
>[ITransaktionsrader5] FOREIGN
>KEY([TransaktionsraderTransaktionsraderAvgiftsnr3 ])
>REFERENCES [dbo].[Avgifter] ([Avgiftsnr])
>GO
>ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader5]
>GO
>ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
>[ITransaktionsrader6] FOREIGN
>KEY([TransaktionsraderTransaktionsraderAvgiftsnr4 ])
>REFERENCES [dbo].[Avgifter] ([Avgiftsnr])
>GO
>ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader6]
>GO
>ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
>[ITransaktionsrader7] FOREIGN KEY([Undervisningstypnr])
>REFERENCES [dbo].[UNDERVISNINGSTYP] ([Undervisningstypnr])
>GO
>ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader7]
>GO
>ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
>[ITransaktionsrader8] FOREIGN KEY([Bildnr])
>REFERENCES [dbo].[Bilder] ([Bildnr])
>GO
>ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader8]
>GO
>ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
>[ITransaktionsrader9] FOREIGN KEY([TransraderAnstalldGuideAnstalldnr])
>REFERENCES [dbo].[Anstallda] ([AnstalldaAnstalldnr])
>GO
>ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader9]
>"Arnie Rowland" wrote: