Does SQL Server Support the "MINUS" Keyword?

Maybe there's another better way to do this... Anyway, here's what I'm trying to do:

I have two tables CarType & Cars. Table CarType has a column CarTypeId which is the primary key (int, identity). Table Cars includes a column CarTypeId (int) which is a foreign key into the CarType table.

Now I am trying to build a list of all those CarTypeId entries (from the CarType table) for which there are no Cars (i.e. there is no corresponding entry in the Cars table).

I read that there is an SQL "MINUS" keyword that you can use like this:

SELECT CarTypeId FROM CarType
MINUS
SELECT CarTypeId FROM Cars

So if CarType contains records with the keys {1, 2, 3, 4, 5} and Cars contains records with the foreign keys {2, 4} (using set notation), your result should be the set {1, 3, 5}. (Note: I have tried alternate versions of this where I selected the DISTINCT CarTypeId entries from Cars, and where I sorted them; results same in both cases).

I tried it in SQL Server 2005 Express. The result is just {1, 2, 3, 4, 5}.

My questions:

Is the MINUS capability supported by SQL Server? I know that there are all sorts of different SQL dialects & implementations and that SQL has evolved & transmutated over the years; perhaps they opted to leave this out of SQL Server... If not supported, well then I guess I have to throw myself at the feet of the real SQL gurus and ask if there's another way to accomplish this.

Here's a reference to the website where I initially found out about MINUS:
http://www.1keydata.com/sql/sql-minus.html

Thanks in advance for your time/comments.

I think you want EXCEPT

ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/tsqlref9/html/b1019300-171a-4a1a-854f-e1e751de3565.htm

|||You can also use a NOT EXISTS to find records that don't exist in the other table|||

Thanks Simon for your comments and pointing me in the right direction.

This is what worked for me:

SELECT CarTypeId FROM CarType
WHERE (CarTypeId NOT IN
(SELECT DISTINCT CarTypeId FROM Cars)
)

Still interested to know what became of the MINUS keyword though, because I think that syntax is slicker...

Ian

|||

SQL Server has never supported MINUS.

in SQL 2005 it now has the EXCEPT clause which does the same thing

|||

Caution: Microsoft talks about this issue at https://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part2/c0761.mspx?mfr=true. However, it is incomplete in that it does not address a curveball.

That is, the = operator does not address nulls.

The Microsoft example is as follow:

Oracle Microsoft SQL Server

SELECT CCODE, CNAME
FROM DEPT_ADMIN.CLASS
MINUS
SELECT C.CCODE, C.CNAME
FROM STUDENT_ADMIN.GRADE G,
DEPT_ADMIN.CLASS C
WHERE C.CCODE = G.CCODE

SELECT CCODE, CNAME
FROM DEPT_ADMIN.CLASSC
WHERE NOT EXISTS
(SELECT 'X' FROM
STUDENT_ADMIN.GRADE G
WHERE C.CCODE = G.CCODE)

This will work to identify students in one table but not in another. However, it will not work like Oracle's MINUS command to spot differences between tables. So, say you want to see this -- between the 2 tables, find where students are missing AND students whose names have changed -- either from A to B or from not null to null or from null to not null.

Try this:

SELECT CCODE, CNAME
FROM DEPT_ADMIN.CLASS C
WHERE NOT EXISTS
(SELECT 'X' FROM
STUDENT_ADMIN.GRADE G
WHERE C.CCODE = G.CCODE

and (C.CNAME = G.CNAME or (C.CNAME is null and G.NAME is null) )

That will mimic the full functionality of Oracle's MINUS command in SQL Server 2000 T-SQL.

|||

Hi Ian,

I havent come across the Minus in SQL server but to do what you want I would try the following

SELECT T.cartypeid

FROM cartype as T

LEFT OUTER JOIN cars C

ON T.cartypeID = C.cartypeID

WHERE c.caretypeID is null

This joins everything in the cartype table but the where clause restricts the result set to those records with a null car.cartypeID

i.e. no matching record

Does SQL Server Support the "MINUS" Keyword?

Maybe there's another better way to do this... Anyway, here's what I'm trying to do:

I have two tables CarType & Cars. Table CarType has a column CarTypeId which is the primary key (int, identity). Table Cars includes a column CarTypeId (int) which is a foreign key into the CarType table.

Now I am trying to build a list of all those CarTypeId entries (from the CarType table) for which there are no Cars (i.e. there is no corresponding entry in the Cars table).

I read that there is an SQL "MINUS" keyword that you can use like this:

SELECT CarTypeId FROM CarType
MINUS
SELECT CarTypeId FROM Cars

So if CarType contains records with the keys {1, 2, 3, 4, 5} and Cars contains records with the foreign keys {2, 4} (using set notation), your result should be the set {1, 3, 5}. (Note: I have tried alternate versions of this where I selected the DISTINCT CarTypeId entries from Cars, and where I sorted them; results same in both cases).

I tried it in SQL Server 2005 Express. The result is just {1, 2, 3, 4, 5}.

My questions:

Is the MINUS capability supported by SQL Server? I know that there are all sorts of different SQL dialects & implementations and that SQL has evolved & transmutated over the years; perhaps they opted to leave this out of SQL Server... If not supported, well then I guess I have to throw myself at the feet of the real SQL gurus and ask if there's another way to accomplish this.

Here's a reference to the website where I initially found out about MINUS:
http://www.1keydata.com/sql/sql-minus.html

Thanks in advance for your time/comments.

I think you want EXCEPT

ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/tsqlref9/html/b1019300-171a-4a1a-854f-e1e751de3565.htm

|||You can also use a NOT EXISTS to find records that don't exist in the other table|||

Thanks Simon for your comments and pointing me in the right direction.

This is what worked for me:

SELECT CarTypeId FROM CarType
WHERE (CarTypeId NOT IN
(SELECT DISTINCT CarTypeId FROM Cars)
)

Still interested to know what became of the MINUS keyword though, because I think that syntax is slicker...

Ian

|||

SQL Server has never supported MINUS.

in SQL 2005 it now has the EXCEPT clause which does the same thing

|||

Caution: Microsoft talks about this issue at https://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part2/c0761.mspx?mfr=true. However, it is incomplete in that it does not address a curveball.

That is, the = operator does not address nulls.

The Microsoft example is as follow:

Oracle Microsoft SQL Server

SELECT CCODE, CNAME
FROM DEPT_ADMIN.CLASS
MINUS
SELECT C.CCODE, C.CNAME
FROM STUDENT_ADMIN.GRADE G,
DEPT_ADMIN.CLASS C
WHERE C.CCODE = G.CCODE

SELECT CCODE, CNAME
FROM DEPT_ADMIN.CLASSC
WHERE NOT EXISTS
(SELECT 'X' FROM
STUDENT_ADMIN.GRADE G
WHERE C.CCODE = G.CCODE)

This will work to identify students in one table but not in another. However, it will not work like Oracle's MINUS command to spot differences between tables. So, say you want to see this -- between the 2 tables, find where students are missing AND students whose names have changed -- either from A to B or from not null to null or from null to not null.

Try this:

SELECT CCODE, CNAME
FROM DEPT_ADMIN.CLASS C
WHERE NOT EXISTS
(SELECT 'X' FROM
STUDENT_ADMIN.GRADE G
WHERE C.CCODE = G.CCODE

and (C.CNAME = G.CNAME or (C.CNAME is null and G.NAME is null) )

That will mimic the full functionality of Oracle's MINUS command in SQL Server 2000 T-SQL.

|||

Hi Ian,

I havent come across the Minus in SQL server but to do what you want I would try the following

SELECT T.cartypeid

FROM cartype as T

LEFT OUTER JOIN cars C

ON T.cartypeID = C.cartypeID

WHERE c.caretypeID is null

This joins everything in the cartype table but the where clause restricts the result set to those records with a null car.cartypeID

i.e. no matching record

Does SQL Server put a shared lock on all tables within a transaction?

Would table1, table2 and table3 in code below, be locked with a shared lock from start of transaction to the end of transaction Or they would only be locked for the duration of their update, or insert statements and not for the entire transaction? Default isolation level is in effect in SQL Server.

begin tran
update table1 set column1 = 100
if @.ERROR = 0
begin
declare @.stat int
set @.stat = (select stat from table2 where employeeid = 10)
insert into table3 (col1, col2) values (@.stat , 325)
if @.@.ERROR = 0
commit tran
else
rollback tran
end
else
roll back tran

The answer to your first question is no. The answer to your second question is no.|||

Here's the long answer:

To keep this long answer from becoming a book, I'm going to classify IS/S locks as "shared", IU/I locks as "Update" and IX/X locks as "Exclusive". The I-versions are basically the same, but has a few differences, and usually used prior to obtaining the non-I version to eliminate lock starvation.

I'm going to assume table1 only has 1 row in it for this.

Update table1 ... will initially request an update lock as it scans the table for the rows to update, once it's located the correct rows, it will acquire an exclusive lock on those pages and rows then release the update locks.

set @.stat=(SELECT .. will request shared locks on the table, retrieve it's results, then release it's shared locks.

insert into table3 will acquire an exlusive lock on either an existing page, or a new page if either none are available, or the available ones are already exclusively locked. (There is an additional exclusive lock placed on the particular row as well).

The exclusive locks will be held until the transaction is either commited or rolled back. This stored procedure will not block any other session from either reading table2 at any time, or inserting a new record into table3. It will however block any other session from updating it's only row until the transaction is complete.

Does SQL Server creates some temporary table during query execution?

Hi,
I want to know when does SQL Server use temporary tables for query
processing?
Does it use for all queries or for some complex queries? Or doesn't use at
all?
Thanks
PushkarYes, it does. If you show the query plan, you'll see icons such as Table
Spool/Eager Spool. That implies that a temporary table is being created by
the optimizer behind the scenes.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:ekuGHMiNGHA.2320@.TK2MSFTNGP11.phx.gbl...
Hi,
I want to know when does SQL Server use temporary tables for query
processing?
Does it use for all queries or for some complex queries? Or doesn't use at
all?
Thanks
Pushkar|||Pushkar
I think SQL Server decides internally to perform some operations in tempdb
database. For sure I know that if your query has ORDER BY ,GROUP BY clauses
and it has to operate in large amount of data , so SQL Server will create a
work tables to perfom that.
I'd suggest to visit at Aaron's web site to get more explanation
www.aspfaq.com
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:ekuGHMiNGHA.2320@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I want to know when does SQL Server use temporary tables for query
> processing?
> Does it use for all queries or for some complex queries? Or doesn't use at
> all?
> Thanks
> Pushkar
>|||Thanks !!!
Pushkar
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:urnhSSiNGHA.2916@.tk2msftngp13.phx.gbl...
> Pushkar
> I think SQL Server decides internally to perform some operations in
> tempdb database. For sure I know that if your query has ORDER BY ,GROUP BY
> clauses and it has to operate in large amount of data , so SQL Server will
> create a work tables to perfom that.
> I'd suggest to visit at Aaron's web site to get more explanation
> www.aspfaq.com
>
>
>
> "Pushkar" <pushkartiwari@.gmail.com> wrote in message
> news:ekuGHMiNGHA.2320@.TK2MSFTNGP11.phx.gbl...
>

Does RS allow querying between tables?

I have a very complex query that needs to return monthly and year-to-date
data. I'm using one table (table A) to correctly return the monthly data. I
would now like to create another table and use a result from table A to
correctly filter and display the year-to-date data along side the monthly
data.
Does RS allow querying between tables?
Example of desired results
Account July 2004 Year-to-Date
Ace Hardware $3000.00 $8452.21
--
DCountYou can't really use table A to display results into a different table. But
what you may want to try is to use the same dataset used in table A to create
a new table B and then on table B goto the properties of the table and then
goto the filters entry there and apply the filters you want to the table.
That should get you the results you're looking for.
> I have a very complex query that needs to return monthly and year-to-date
> data. I'm using one table (table A) to correctly return the monthly data. I
> would now like to create another table and use a result from table A to
> correctly filter and display the year-to-date data along side the monthly
> data.
> Does RS allow querying between tables?
> Example of desired results
> Account July 2004 Year-to-Date
> Ace Hardware $3000.00 $8452.21
>
> --
> DCount

Does reporting services need SQL Server for the RS application tables?

Would it be possible to buy a license for SQL server, not install SQL
server and then have both the data and application tables in an Oracle
database?
A customer of ours does not want to support SQL Server at all. I can
accept that they need a SQL license to use RS, but is there a way to
get around the SQL requirment for the app tables? Will moving the
tables from SQL to Oracle (and the configuring RS to use Oracle)
result in application errors?
I was hoping someone knew the answers before I started testing the
scenarios above.No. SQL Server is needed by Reporting Services. The data can be anywhere but
RS needs SQL Server to operate (the database can be on a separate server but
it has to be somewhere).
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"coldfact" <bryan@.coldfact.com> wrote in message
news:249185cd.0410261208.1283bdf1@.posting.google.com...
> Would it be possible to buy a license for SQL server, not install SQL
> server and then have both the data and application tables in an Oracle
> database?
> A customer of ours does not want to support SQL Server at all. I can
> accept that they need a SQL license to use RS, but is there a way to
> get around the SQL requirment for the app tables? Will moving the
> tables from SQL to Oracle (and the configuring RS to use Oracle)
> result in application errors?
> I was hoping someone knew the answers before I started testing the
> scenarios above.

Does Query plans change on static tables

I have attached two query plans , which are generated by Production server
at different times (1 day apart). ( i could not send mail with attached
plans I had to remove them)
Same query I have executed every day over two weeks time to see how the
query is performing in prod. server. I got two different plans. I didnt
understand how come the plan is changing so drastic when there are not many
data updates on the tables referred in the query. These tables are very
static and i am running dbcc reindex every night on these tables. Plan 1 has
index scan which is taking long time to execute (11 Sec), Plan 2 has no
index scan and so it is executing fast (<200ms). I didnt get a clue why it
is going for index scan some time and no index scan other times. I hope some
one will have answer for it.
Windows 2000 Server/SQL Server 2000 Standard SP4, 2 cpu, 1GB RAM, RAID 1
Disk
Thanks,
Subbu.
underprocessable|||underprocessable|||Statistics can influence the plan choice. Not all stats are tied to
indexes. So, you likely will want to make sure that all statistics are
up-to-date before making any conclusions. (update statistics <tablename>)
If you are close to a boundary between two plan choices, small changes can
push you from one plan to another on a recompile.
Hope that helps,
Conor
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:%23wq2y6NWGHA.1192@.TK2MSFTNGP03.phx.gbl...
> Plan 1: No index SCAN
> Plan 2: Index SCAN
> I had to send in two separate mail as it crossed max limit size
> Thanks,
> Subbu.
> "subbu" <subbaiahd@.hotmail.com> wrote in message
> news:%238V3L3NWGHA.4212@.TK2MSFTNGP03.phx.gbl...
> many
> has
> it
> some
>
>

Does Query plans change on static tables

I have attached two query plans , which are generated by Production server
at different times (1 day apart). ( i could not send mail with attached
plans I had to remove them)
Same query I have executed every day over two weeks time to see how the
query is performing in prod. server. I got two different plans. I didnt
understand how come the plan is changing so drastic when there are not many
data updates on the tables referred in the query. These tables are very
static and i am running dbcc reindex every night on these tables. Plan 1 has
index scan which is taking long time to execute (11 Sec), Plan 2 has no
index scan and so it is executing fast (<200ms). I didnt get a clue why it
is going for index scan some time and no index scan other times. I hope some
one will have answer for it.
Windows 2000 Server/SQL Server 2000 Standard SP4, 2 cpu, 1GB RAM, RAID 1
Disk
Thanks,
Subbu.underprocessable|||underprocessable|||Statistics can influence the plan choice. Not all stats are tied to
indexes. So, you likely will want to make sure that all statistics are
up-to-date before making any conclusions. (update statistics <tablename> )
If you are close to a boundary between two plan choices, small changes can
push you from one plan to another on a recompile.
Hope that helps,
Conor
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:%23wq2y6NWGHA.1192@.TK2MSFTNGP03.phx.gbl...
> Plan 1: No index SCAN
> Plan 2: Index SCAN
> I had to send in two separate mail as it crossed max limit size
> Thanks,
> Subbu.
> "subbu" <subbaiahd@.hotmail.com> wrote in message
> news:%238V3L3NWGHA.4212@.TK2MSFTNGP03.phx.gbl...
> many
> has
> it
> some
>
>

Does Query plans change on static tables

I have attached two query plans , which are generated by Production server
at different times (1 day apart). ( i could not send mail with attached
plans I had to remove them)
Same query I have executed every day over two weeks time to see how the
query is performing in prod. server. I got two different plans. I didnt
understand how come the plan is changing so drastic when there are not many
data updates on the tables referred in the query. These tables are very
static and i am running dbcc reindex every night on these tables. Plan 1 has
index scan which is taking long time to execute (11 Sec), Plan 2 has no
index scan and so it is executing fast (<200ms). I didnt get a clue why it
is going for index scan some time and no index scan other times. I hope some
one will have answer for it.
Windows 2000 Server/SQL Server 2000 Standard SP4, 2 cpu, 1GB RAM, RAID 1
Disk
Thanks,
Subbu.Statistics can influence the plan choice. Not all stats are tied to
indexes. So, you likely will want to make sure that all statistics are
up-to-date before making any conclusions. (update statistics <tablename>)
If you are close to a boundary between two plan choices, small changes can
push you from one plan to another on a recompile.
Hope that helps,
Conor
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:%23wq2y6NWGHA.1192@.TK2MSFTNGP03.phx.gbl...
> Plan 1: No index SCAN
> Plan 2: Index SCAN
> I had to send in two separate mail as it crossed max limit size
> Thanks,
> Subbu.
> "subbu" <subbaiahd@.hotmail.com> wrote in message
> news:%238V3L3NWGHA.4212@.TK2MSFTNGP03.phx.gbl...
>> I have attached two query plans , which are generated by Production
>> server
>> at different times (1 day apart). ( i could not send mail with attached
>> plans I had to remove them)
>> Same query I have executed every day over two weeks time to see how the
>> query is performing in prod. server. I got two different plans. I didnt
>> understand how come the plan is changing so drastic when there are not
> many
>> data updates on the tables referred in the query. These tables are very
>> static and i am running dbcc reindex every night on these tables. Plan 1
> has
>> index scan which is taking long time to execute (11 Sec), Plan 2 has no
>> index scan and so it is executing fast (<200ms). I didnt get a clue why
> it
>> is going for index scan some time and no index scan other times. I hope
> some
>> one will have answer for it.
>>
>> Windows 2000 Server/SQL Server 2000 Standard SP4, 2 cpu, 1GB RAM, RAID 1
>> Disk
>>
>> Thanks,
>> Subbu.
>>
>>
>>
>
>

does not have a corresponding mapping in the schema

I am trying to update several tables using an Updategram. I get the followin
g
error:
HRESULT="0x80004005" Description="Specified attribute or element ('USR')
does not have a corresponding mapping in the schema, and no overflow field
defined"
The Schema is:
<?xml version="1.0" encoding="utf-8" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="USRMerge" parent="USR" parent-key="USRID"
child="MergeHdr" child-key="USRID" />
<sql:relationship name="MergeMergeList" parent="MergeHdr"
parent-key="MergeID" child="MergeList" child-key="MergeID" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="USR" sql:relation="USR" sql:key-field="USRID">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="USRID" type="xsd:integer" nillable="true"
sql:identity="ignore" />
<xsd:element name="USRTitle" type="xsd:string" minOccurs="1" />
<xsd:element name="USRStatusID" type="xsd:integer" sql:datatype="int"
nillable="true" />
<xsd:element name="CSRID" type="xsd:integer" sql:datatype="int"
nillable="true" />
<xsd:element name="CustomerID" type="xsd:integer" sql:datatype="int"
nillable="true" />
<xsd:element name="CustUSRRef" type="xsd:string" nillable="true" />
<xsd:element name="CustContact" type="xsd:string" nillable="true" />
<xsd:element name="ProjectID" type="xsd:integer" sql:datatype="int"
nillable="true" />
<xsd:element name="ReleaseID" type="xsd:integer" sql:datatype="int"
nillable="true" />
<xsd:element name="ModuleID" type="xsd:integer" sql:datatype="int"
nillable="true" />
<xsd:element name="KeyWords" type="xsd:string" nillable="true"
sql:use-cdata="1" />
<xsd:element name="Expected" type="xsd:string" nillable="true"
sql:use-cdata="1" />
<xsd:element name="Observed" type="xsd:string" nillable="true"
sql:use-cdata="1" />
<xsd:element name="Steps" type="xsd:string" nillable="true" />
<xsd:element name="Billable" type="xsd:boolean" nillable="true"
default="false" />
<xsd:element name="IsFree" type="xsd:boolean" default="1" />
<xsd:element name="UserID" type="xsd:integer" sql:datatype="int"
minOccurs="1" />
<xsd:element name="VersionRef" type="xsd:string" nillable="true" />
<xsd:element name="ProductID" type="xsd:integer" nillable="true" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="MergeHdr" sql:relation="MergeHdr" sql:key-field="MergeID"
>
<xsd:complexType>
<xsd:sequence>
<xsd:element name="MergeID" type="xsd:integer" nillable="true"
sql:identity="ignore" />
<xsd:element name="USRID" type="xsd:integer" nillable="true"
sql:datatype="int" />
<xsd:element name="ScreenChange" type="xsd:boolean" nillable="true"
default="false" />
<xsd:element name="Translation" type="xsd:boolean" nillable="true"
default="false" />
<xsd:element name="MenuChange" type="xsd:boolean" nillable="true"
default="false" />
<xsd:element name="SecurityChange" type="xsd:boolean" nillable="true"
default="false" />
<xsd:element name="JobSetupChange" type="xsd:boolean" nillable="true"
default="false" />
<xsd:element name="MessageFileChange" type="xsd:boolean" nillable="true"
default="false" />
<xsd:element name="ManualCodeTableChange" type="xsd:boolean"
nillable="true" default="false" />
<xsd:element name="DatabaseChange" type="xsd:boolean" nillable="true"
default="false" />
<xsd:element name="DictionaryChange" type="xsd:boolean" nillable="true"
default="false" />
<xsd:element name="DataBomb" type="xsd:boolean" nillable="true"
default="false" />
<xsd:element name="CoreReports" type="xsd:boolean" nillable="true"
default="false" />
<xsd:element name="PlanetPressJetForm" type="xsd:boolean"
nillable="true" default="false" />
<xsd:element name="ConfigurationFile" type="xsd:boolean" nillable="true"
default="false" />
<xsd:element name="XSLTFile" type="xsd:boolean" nillable="true"
default="false" />
<xsd:element name="SQLFile" type="xsd:boolean" nillable="true"
default="false" />
<xsd:element name="ExtractFile" type="xsd:boolean" nillable="true"
default="false" />
<xsd:element name="DevCompleteDate" type="xsd:dateTime" nillable="true"
sql:datatype="dateTime" />
<xsd:element name="TestCompleteDate" type="xsd:dateTime" nillable="true"
sql:datatype="dateTime" />
<xsd:element name="Notes" type="xsd:string" nillable="true"
sql:use-cdata="1" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="MergeList" sql:relation="MergeHdr"
sql:key-field="MergeListID">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="MergeListID" type="xsd:string" nillable="true"
sql:identity="ignore" />
<xsd:element name="MergeID" type="xsd:integer" nillable="true" />
<xsd:element name="Environment" type="xsd:string" nillable="true" />
<xsd:element name="CodeReviewUserID" type="xsd:integer" nillable="true" />
<xsd:element name="Patch" type="xsd:string" nillable="true" />
<xsd:element name="WhoMergedUserID" type="xsd:integer" nillable="true" />
<xsd:element name="DateMerged" type="xsd:dateTime"
sql:datatype="dateTime" nillable="true" />
<xsd:element name="TestImperialUserID" type="xsd:integer"
nillable="true" />
<xsd:element name="TestMetericUserID" type="xsd:integer" nillable="true"
/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
The Update Gram is:
<root xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<sync mapping-schema="..\templates\USRMapSchema.xml"
xmlns="urn:schemas-microsoft-com:xml-updategram">
<updg:before>
<USR updg:id="USR1">
<USRID>1</USRID>
<USRTitle>New USR</USRTitle>
<Billable>false</Billable>
<IsFree>true</IsFree>
<UserID>1</UserID>
</USR>
</updg:before>
<updg:after>
<USR updg:id="USR1">
<USRID>1</USRID>
<USRTitle>updated title usr 1</USRTitle>
<Billable>false</Billable>
<IsFree>true</IsFree>
<UserID>1</UserID>
</USR>
</updg:after>
<updg:before>
<MergeHdr updg:id="MergeHdr1">
<MergeID>1</MergeID>
<USRID>1</USRID>
<ScreenChange>false</ScreenChange>
<Translation>false</Translation>
<MenuChange>false</MenuChange>
<SecurityChange>false</SecurityChange>
<JobSetupChange>false</JobSetupChange>
<MessageFileChange>false</MessageFileChange>
<ManualCodeTableChange>false</ManualCodeTableChange>
<DatabaseChange>false</DatabaseChange>
<DictionaryChange>false</DictionaryChange>
<DataBomb>false</DataBomb>
<CoreReports>false</CoreReports>
<PlanetPressJetForm>false</PlanetPressJetForm>
<ConfigurationFile>false</ConfigurationFile>
<XSLTFile>false</XSLTFile>
<SQLFile>false</SQLFile>
<ExtractFile>false</ExtractFile>
</MergeHdr>
</updg:before>
<updg:after>
<MergeHdr updg:id="MergeHdr1">
<MergeID>1</MergeID>
<USRID>1</USRID>
<ScreenChange>true</ScreenChange>
<Translation>true</Translation>
<MenuChange>true</MenuChange>
<SecurityChange>false</SecurityChange>
<JobSetupChange>false</JobSetupChange>
<MessageFileChange>false</MessageFileChange>
<ManualCodeTableChange>false</ManualCodeTableChange>
<DatabaseChange>false</DatabaseChange>
<DictionaryChange>false</DictionaryChange>
<DataBomb>false</DataBomb>
<CoreReports>false</CoreReports>
<PlanetPressJetForm>false</PlanetPressJetForm>
<ConfigurationFile>false</ConfigurationFile>
<XSLTFile>false</XSLTFile>
<SQLFile>false</SQLFile>
<ExtractFile>false</ExtractFile>
</MergeHdr>
</updg:after>
<updg:before>
<MergeList updg:id="MergeList1">
<MergeListID>1</MergeListID>
<MergeID>1</MergeID>
<DateMerged>2005-01-17T17:27:36.4470000-08:00</DateMerged>
</MergeList>
</updg:before>
<updg:after>
<MergeList updg:id="MergeList1">
<MergeListID>1</MergeListID>
<MergeID>1</MergeID>
<Environment>new item usr 1</Environment>
</MergeList>
</updg:after>
</sync>
</root>
Thanks in advance!In your updategram, you define a default namespase:

> xmlns="urn:schemas-microsoft-com:xml-updategram">
That will put all the elments to this namespace which is different than
declared in schema.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Squiggs" <Squiggs@.discussions.microsoft.com> wrote in message
news:5B75E7C4-CB9A-4707-82FF-C42034ED9D89@.microsoft.com...
> I am trying to update several tables using an Updategram. I get the
following
> error:
> HRESULT="0x80004005" Description="Specified attribute or element ('USR')
> does not have a corresponding mapping in the schema, and no overflow field
> defined"
> The Schema is:
> <?xml version="1.0" encoding="utf-8" ?>
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:annotation>
> <xsd:appinfo>
> <sql:relationship name="USRMerge" parent="USR" parent-key="USRID"
> child="MergeHdr" child-key="USRID" />
> <sql:relationship name="MergeMergeList" parent="MergeHdr"
> parent-key="MergeID" child="MergeList" child-key="MergeID" />
> </xsd:appinfo>
> </xsd:annotation>
> <xsd:element name="USR" sql:relation="USR" sql:key-field="USRID">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="USRID" type="xsd:integer" nillable="true"
> sql:identity="ignore" />
> <xsd:element name="USRTitle" type="xsd:string" minOccurs="1" />
> <xsd:element name="USRStatusID" type="xsd:integer" sql:datatype="int"
> nillable="true" />
> <xsd:element name="CSRID" type="xsd:integer" sql:datatype="int"
> nillable="true" />
> <xsd:element name="CustomerID" type="xsd:integer" sql:datatype="int"
> nillable="true" />
> <xsd:element name="CustUSRRef" type="xsd:string" nillable="true" />
> <xsd:element name="CustContact" type="xsd:string" nillable="true" />
> <xsd:element name="ProjectID" type="xsd:integer" sql:datatype="int"
> nillable="true" />
> <xsd:element name="ReleaseID" type="xsd:integer" sql:datatype="int"
> nillable="true" />
> <xsd:element name="ModuleID" type="xsd:integer" sql:datatype="int"
> nillable="true" />
> <xsd:element name="KeyWords" type="xsd:string" nillable="true"
> sql:use-cdata="1" />
> <xsd:element name="Expected" type="xsd:string" nillable="true"
> sql:use-cdata="1" />
> <xsd:element name="Observed" type="xsd:string" nillable="true"
> sql:use-cdata="1" />
> <xsd:element name="Steps" type="xsd:string" nillable="true" />
> <xsd:element name="Billable" type="xsd:boolean" nillable="true"
> default="false" />
> <xsd:element name="IsFree" type="xsd:boolean" default="1" />
> <xsd:element name="UserID" type="xsd:integer" sql:datatype="int"
> minOccurs="1" />
> <xsd:element name="VersionRef" type="xsd:string" nillable="true" />
> <xsd:element name="ProductID" type="xsd:integer" nillable="true" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> <xsd:element name="MergeHdr" sql:relation="MergeHdr"
sql:key-field="MergeID">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="MergeID" type="xsd:integer" nillable="true"
> sql:identity="ignore" />
> <xsd:element name="USRID" type="xsd:integer" nillable="true"
> sql:datatype="int" />
> <xsd:element name="ScreenChange" type="xsd:boolean" nillable="true"
> default="false" />
> <xsd:element name="Translation" type="xsd:boolean" nillable="true"
> default="false" />
> <xsd:element name="MenuChange" type="xsd:boolean" nillable="true"
> default="false" />
> <xsd:element name="SecurityChange" type="xsd:boolean" nillable="true"
> default="false" />
> <xsd:element name="JobSetupChange" type="xsd:boolean" nillable="true"
> default="false" />
> <xsd:element name="MessageFileChange" type="xsd:boolean" nillable="true"
> default="false" />
> <xsd:element name="ManualCodeTableChange" type="xsd:boolean"
> nillable="true" default="false" />
> <xsd:element name="DatabaseChange" type="xsd:boolean" nillable="true"
> default="false" />
> <xsd:element name="DictionaryChange" type="xsd:boolean" nillable="true"
> default="false" />
> <xsd:element name="DataBomb" type="xsd:boolean" nillable="true"
> default="false" />
> <xsd:element name="CoreReports" type="xsd:boolean" nillable="true"
> default="false" />
> <xsd:element name="PlanetPressJetForm" type="xsd:boolean"
> nillable="true" default="false" />
> <xsd:element name="ConfigurationFile" type="xsd:boolean" nillable="true"
> default="false" />
> <xsd:element name="XSLTFile" type="xsd:boolean" nillable="true"
> default="false" />
> <xsd:element name="SQLFile" type="xsd:boolean" nillable="true"
> default="false" />
> <xsd:element name="ExtractFile" type="xsd:boolean" nillable="true"
> default="false" />
> <xsd:element name="DevCompleteDate" type="xsd:dateTime" nillable="true"
> sql:datatype="dateTime" />
> <xsd:element name="TestCompleteDate" type="xsd:dateTime" nillable="true"
> sql:datatype="dateTime" />
> <xsd:element name="Notes" type="xsd:string" nillable="true"
> sql:use-cdata="1" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> <xsd:element name="MergeList" sql:relation="MergeHdr"
> sql:key-field="MergeListID">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="MergeListID" type="xsd:string" nillable="true"
> sql:identity="ignore" />
> <xsd:element name="MergeID" type="xsd:integer" nillable="true" />
> <xsd:element name="Environment" type="xsd:string" nillable="true" />
> <xsd:element name="CodeReviewUserID" type="xsd:integer" nillable="true" />
> <xsd:element name="Patch" type="xsd:string" nillable="true" />
> <xsd:element name="WhoMergedUserID" type="xsd:integer" nillable="true" />
> <xsd:element name="DateMerged" type="xsd:dateTime"
> sql:datatype="dateTime" nillable="true" />
> <xsd:element name="TestImperialUserID" type="xsd:integer"
> nillable="true" />
> <xsd:element name="TestMetericUserID" type="xsd:integer" nillable="true"
> />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> The Update Gram is:
> <root xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
> <sync mapping-schema="..\templates\USRMapSchema.xml"
> xmlns="urn:schemas-microsoft-com:xml-updategram">
> <updg:before>
> <USR updg:id="USR1">
> <USRID>1</USRID>
> <USRTitle>New USR</USRTitle>
> <Billable>false</Billable>
> <IsFree>true</IsFree>
> <UserID>1</UserID>
> </USR>
> </updg:before>
> <updg:after>
> <USR updg:id="USR1">
> <USRID>1</USRID>
> <USRTitle>updated title usr 1</USRTitle>
> <Billable>false</Billable>
> <IsFree>true</IsFree>
> <UserID>1</UserID>
> </USR>
> </updg:after>
> <updg:before>
> <MergeHdr updg:id="MergeHdr1">
> <MergeID>1</MergeID>
> <USRID>1</USRID>
> <ScreenChange>false</ScreenChange>
> <Translation>false</Translation>
> <MenuChange>false</MenuChange>
> <SecurityChange>false</SecurityChange>
> <JobSetupChange>false</JobSetupChange>
> <MessageFileChange>false</MessageFileChange>
> <ManualCodeTableChange>false</ManualCodeTableChange>
> <DatabaseChange>false</DatabaseChange>
> <DictionaryChange>false</DictionaryChange>
> <DataBomb>false</DataBomb>
> <CoreReports>false</CoreReports>
> <PlanetPressJetForm>false</PlanetPressJetForm>
> <ConfigurationFile>false</ConfigurationFile>
> <XSLTFile>false</XSLTFile>
> <SQLFile>false</SQLFile>
> <ExtractFile>false</ExtractFile>
> </MergeHdr>
> </updg:before>
> <updg:after>
> <MergeHdr updg:id="MergeHdr1">
> <MergeID>1</MergeID>
> <USRID>1</USRID>
> <ScreenChange>true</ScreenChange>
> <Translation>true</Translation>
> <MenuChange>true</MenuChange>
> <SecurityChange>false</SecurityChange>
> <JobSetupChange>false</JobSetupChange>
> <MessageFileChange>false</MessageFileChange>
> <ManualCodeTableChange>false</ManualCodeTableChange>
> <DatabaseChange>false</DatabaseChange>
> <DictionaryChange>false</DictionaryChange>
> <DataBomb>false</DataBomb>
> <CoreReports>false</CoreReports>
> <PlanetPressJetForm>false</PlanetPressJetForm>
> <ConfigurationFile>false</ConfigurationFile>
> <XSLTFile>false</XSLTFile>
> <SQLFile>false</SQLFile>
> <ExtractFile>false</ExtractFile>
> </MergeHdr>
> </updg:after>
> <updg:before>
> <MergeList updg:id="MergeList1">
> <MergeListID>1</MergeListID>
> <MergeID>1</MergeID>
> <DateMerged>2005-01-17T17:27:36.4470000-08:00</DateMerged>
> </MergeList>
> </updg:before>
> <updg:after>
> <MergeList updg:id="MergeList1">
> <MergeListID>1</MergeListID>
> <MergeID>1</MergeID>
> <Environment>new item usr 1</Environment>
> </MergeList>
> </updg:after>
> </sync>
> </root>
> Thanks in advance!

does not have a corresponding mapping in the schema

I am trying to update several tables using an Updategram. I get the following
error:
HRESULT="0x80004005" Description="Specified attribute or element ('USR')
does not have a corresponding mapping in the schema, and no overflow field
defined"
The Schema is:
<?xml version="1.0" encoding="utf-8" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="USRMerge" parent="USR" parent-key="USRID"
child="MergeHdr" child-key="USRID" />
<sql:relationship name="MergeMergeList" parent="MergeHdr"
parent-key="MergeID" child="MergeList" child-key="MergeID" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="USR" sql:relation="USR" sql:key-field="USRID">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="USRID" type="xsd:integer" nillable="true"
sql:identity="ignore" />
<xsd:element name="USRTitle" type="xsd:string" minOccurs="1" />
<xsd:element name="USRStatusID" type="xsd:integer" sql:datatype="int"
nillable="true" />
<xsd:element name="CSRID" type="xsd:integer" sql:datatype="int"
nillable="true" />
<xsd:element name="CustomerID" type="xsd:integer" sql:datatype="int"
nillable="true" />
<xsd:element name="CustUSRRef" type="xsd:string" nillable="true" />
<xsd:element name="CustContact" type="xsd:string" nillable="true" />
<xsd:element name="ProjectID" type="xsd:integer" sql:datatype="int"
nillable="true" />
<xsd:element name="ReleaseID" type="xsd:integer" sql:datatype="int"
nillable="true" />
<xsd:element name="ModuleID" type="xsd:integer" sql:datatype="int"
nillable="true" />
<xsd:element name="KeyWords" type="xsd:string" nillable="true"
sql:use-cdata="1" />
<xsd:element name="Expected" type="xsd:string" nillable="true"
sql:use-cdata="1" />
<xsd:element name="Observed" type="xsd:string" nillable="true"
sql:use-cdata="1" />
<xsd:element name="Steps" type="xsd:string" nillable="true" />
<xsd:element name="Billable" type="xsd:boolean" nillable="true"
default="false" />
<xsd:element name="IsFree" type="xsd:boolean" default="1" />
<xsd:element name="UserID" type="xsd:integer" sql:datatype="int"
minOccurs="1" />
<xsd:element name="VersionRef" type="xsd:string" nillable="true" />
<xsd:element name="ProductID" type="xsd:integer" nillable="true" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="MergeHdr" sql:relation="MergeHdr" sql:key-field="MergeID">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="MergeID" type="xsd:integer" nillable="true"
sql:identity="ignore" />
<xsd:element name="USRID" type="xsd:integer" nillable="true"
sql:datatype="int" />
<xsd:element name="ScreenChange" type="xsd:boolean" nillable="true"
default="false" />
<xsd:element name="Translation" type="xsd:boolean" nillable="true"
default="false" />
<xsd:element name="MenuChange" type="xsd:boolean" nillable="true"
default="false" />
<xsd:element name="SecurityChange" type="xsd:boolean" nillable="true"
default="false" />
<xsd:element name="JobSetupChange" type="xsd:boolean" nillable="true"
default="false" />
<xsd:element name="MessageFileChange" type="xsd:boolean" nillable="true"
default="false" />
<xsd:element name="ManualCodeTableChange" type="xsd:boolean"
nillable="true" default="false" />
<xsd:element name="DatabaseChange" type="xsd:boolean" nillable="true"
default="false" />
<xsd:element name="DictionaryChange" type="xsd:boolean" nillable="true"
default="false" />
<xsd:element name="DataBomb" type="xsd:boolean" nillable="true"
default="false" />
<xsd:element name="CoreReports" type="xsd:boolean" nillable="true"
default="false" />
<xsd:element name="PlanetPressJetForm" type="xsd:boolean"
nillable="true" default="false" />
<xsd:element name="ConfigurationFile" type="xsd:boolean" nillable="true"
default="false" />
<xsd:element name="XSLTFile" type="xsd:boolean" nillable="true"
default="false" />
<xsd:element name="SQLFile" type="xsd:boolean" nillable="true"
default="false" />
<xsd:element name="ExtractFile" type="xsd:boolean" nillable="true"
default="false" />
<xsd:element name="DevCompleteDate" type="xsd:dateTime" nillable="true"
sql:datatype="dateTime" />
<xsd:element name="TestCompleteDate" type="xsd:dateTime" nillable="true"
sql:datatype="dateTime" />
<xsd:element name="Notes" type="xsd:string" nillable="true"
sql:use-cdata="1" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="MergeList" sql:relation="MergeHdr"
sql:key-field="MergeListID">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="MergeListID" type="xsd:string" nillable="true"
sql:identity="ignore" />
<xsd:element name="MergeID" type="xsd:integer" nillable="true" />
<xsd:element name="Environment" type="xsd:string" nillable="true" />
<xsd:element name="CodeReviewUserID" type="xsd:integer" nillable="true" />
<xsd:element name="Patch" type="xsd:string" nillable="true" />
<xsd:element name="WhoMergedUserID" type="xsd:integer" nillable="true" />
<xsd:element name="DateMerged" type="xsd:dateTime"
sql:datatype="dateTime" nillable="true" />
<xsd:element name="TestImperialUserID" type="xsd:integer"
nillable="true" />
<xsd:element name="TestMetericUserID" type="xsd:integer" nillable="true"
/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
The Update Gram is:
<root xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<sync mapping-schema="..\templates\USRMapSchema.xml"
xmlns="urn:schemas-microsoft-com:xml-updategram">
<updg:before>
<USR updg:id="USR1">
<USRID>1</USRID>
<USRTitle>New USR</USRTitle>
<Billable>false</Billable>
<IsFree>true</IsFree>
<UserID>1</UserID>
</USR>
</updg:before>
<updg:after>
<USR updg:id="USR1">
<USRID>1</USRID>
<USRTitle>updated title usr 1</USRTitle>
<Billable>false</Billable>
<IsFree>true</IsFree>
<UserID>1</UserID>
</USR>
</updg:after>
<updg:before>
<MergeHdr updg:id="MergeHdr1">
<MergeID>1</MergeID>
<USRID>1</USRID>
<ScreenChange>false</ScreenChange>
<Translation>false</Translation>
<MenuChange>false</MenuChange>
<SecurityChange>false</SecurityChange>
<JobSetupChange>false</JobSetupChange>
<MessageFileChange>false</MessageFileChange>
<ManualCodeTableChange>false</ManualCodeTableChange>
<DatabaseChange>false</DatabaseChange>
<DictionaryChange>false</DictionaryChange>
<DataBomb>false</DataBomb>
<CoreReports>false</CoreReports>
<PlanetPressJetForm>false</PlanetPressJetForm>
<ConfigurationFile>false</ConfigurationFile>
<XSLTFile>false</XSLTFile>
<SQLFile>false</SQLFile>
<ExtractFile>false</ExtractFile>
</MergeHdr>
</updg:before>
<updg:after>
<MergeHdr updg:id="MergeHdr1">
<MergeID>1</MergeID>
<USRID>1</USRID>
<ScreenChange>true</ScreenChange>
<Translation>true</Translation>
<MenuChange>true</MenuChange>
<SecurityChange>false</SecurityChange>
<JobSetupChange>false</JobSetupChange>
<MessageFileChange>false</MessageFileChange>
<ManualCodeTableChange>false</ManualCodeTableChange>
<DatabaseChange>false</DatabaseChange>
<DictionaryChange>false</DictionaryChange>
<DataBomb>false</DataBomb>
<CoreReports>false</CoreReports>
<PlanetPressJetForm>false</PlanetPressJetForm>
<ConfigurationFile>false</ConfigurationFile>
<XSLTFile>false</XSLTFile>
<SQLFile>false</SQLFile>
<ExtractFile>false</ExtractFile>
</MergeHdr>
</updg:after>
<updg:before>
<MergeList updg:id="MergeList1">
<MergeListID>1</MergeListID>
<MergeID>1</MergeID>
<DateMerged>2005-01-17T17:27:36.4470000-08:00</DateMerged>
</MergeList>
</updg:before>
<updg:after>
<MergeList updg:id="MergeList1">
<MergeListID>1</MergeListID>
<MergeID>1</MergeID>
<Environment>new item usr 1</Environment>
</MergeList>
</updg:after>
</sync>
</root>
Thanks in advance!
In your updategram, you define a default namespase:

> xmlns="urn:schemas-microsoft-com:xml-updategram">
That will put all the elments to this namespace which is different than
declared in schema.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Squiggs" <Squiggs@.discussions.microsoft.com> wrote in message
news:5B75E7C4-CB9A-4707-82FF-C42034ED9D89@.microsoft.com...
> I am trying to update several tables using an Updategram. I get the
following
> error:
> HRESULT="0x80004005" Description="Specified attribute or element ('USR')
> does not have a corresponding mapping in the schema, and no overflow field
> defined"
> The Schema is:
> <?xml version="1.0" encoding="utf-8" ?>
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:annotation>
> <xsd:appinfo>
> <sql:relationship name="USRMerge" parent="USR" parent-key="USRID"
> child="MergeHdr" child-key="USRID" />
> <sql:relationship name="MergeMergeList" parent="MergeHdr"
> parent-key="MergeID" child="MergeList" child-key="MergeID" />
> </xsd:appinfo>
> </xsd:annotation>
> <xsd:element name="USR" sql:relation="USR" sql:key-field="USRID">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="USRID" type="xsd:integer" nillable="true"
> sql:identity="ignore" />
> <xsd:element name="USRTitle" type="xsd:string" minOccurs="1" />
> <xsd:element name="USRStatusID" type="xsd:integer" sql:datatype="int"
> nillable="true" />
> <xsd:element name="CSRID" type="xsd:integer" sql:datatype="int"
> nillable="true" />
> <xsd:element name="CustomerID" type="xsd:integer" sql:datatype="int"
> nillable="true" />
> <xsd:element name="CustUSRRef" type="xsd:string" nillable="true" />
> <xsd:element name="CustContact" type="xsd:string" nillable="true" />
> <xsd:element name="ProjectID" type="xsd:integer" sql:datatype="int"
> nillable="true" />
> <xsd:element name="ReleaseID" type="xsd:integer" sql:datatype="int"
> nillable="true" />
> <xsd:element name="ModuleID" type="xsd:integer" sql:datatype="int"
> nillable="true" />
> <xsd:element name="KeyWords" type="xsd:string" nillable="true"
> sql:use-cdata="1" />
> <xsd:element name="Expected" type="xsd:string" nillable="true"
> sql:use-cdata="1" />
> <xsd:element name="Observed" type="xsd:string" nillable="true"
> sql:use-cdata="1" />
> <xsd:element name="Steps" type="xsd:string" nillable="true" />
> <xsd:element name="Billable" type="xsd:boolean" nillable="true"
> default="false" />
> <xsd:element name="IsFree" type="xsd:boolean" default="1" />
> <xsd:element name="UserID" type="xsd:integer" sql:datatype="int"
> minOccurs="1" />
> <xsd:element name="VersionRef" type="xsd:string" nillable="true" />
> <xsd:element name="ProductID" type="xsd:integer" nillable="true" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> <xsd:element name="MergeHdr" sql:relation="MergeHdr"
sql:key-field="MergeID">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="MergeID" type="xsd:integer" nillable="true"
> sql:identity="ignore" />
> <xsd:element name="USRID" type="xsd:integer" nillable="true"
> sql:datatype="int" />
> <xsd:element name="ScreenChange" type="xsd:boolean" nillable="true"
> default="false" />
> <xsd:element name="Translation" type="xsd:boolean" nillable="true"
> default="false" />
> <xsd:element name="MenuChange" type="xsd:boolean" nillable="true"
> default="false" />
> <xsd:element name="SecurityChange" type="xsd:boolean" nillable="true"
> default="false" />
> <xsd:element name="JobSetupChange" type="xsd:boolean" nillable="true"
> default="false" />
> <xsd:element name="MessageFileChange" type="xsd:boolean" nillable="true"
> default="false" />
> <xsd:element name="ManualCodeTableChange" type="xsd:boolean"
> nillable="true" default="false" />
> <xsd:element name="DatabaseChange" type="xsd:boolean" nillable="true"
> default="false" />
> <xsd:element name="DictionaryChange" type="xsd:boolean" nillable="true"
> default="false" />
> <xsd:element name="DataBomb" type="xsd:boolean" nillable="true"
> default="false" />
> <xsd:element name="CoreReports" type="xsd:boolean" nillable="true"
> default="false" />
> <xsd:element name="PlanetPressJetForm" type="xsd:boolean"
> nillable="true" default="false" />
> <xsd:element name="ConfigurationFile" type="xsd:boolean" nillable="true"
> default="false" />
> <xsd:element name="XSLTFile" type="xsd:boolean" nillable="true"
> default="false" />
> <xsd:element name="SQLFile" type="xsd:boolean" nillable="true"
> default="false" />
> <xsd:element name="ExtractFile" type="xsd:boolean" nillable="true"
> default="false" />
> <xsd:element name="DevCompleteDate" type="xsd:dateTime" nillable="true"
> sql:datatype="dateTime" />
> <xsd:element name="TestCompleteDate" type="xsd:dateTime" nillable="true"
> sql:datatype="dateTime" />
> <xsd:element name="Notes" type="xsd:string" nillable="true"
> sql:use-cdata="1" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> <xsd:element name="MergeList" sql:relation="MergeHdr"
> sql:key-field="MergeListID">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="MergeListID" type="xsd:string" nillable="true"
> sql:identity="ignore" />
> <xsd:element name="MergeID" type="xsd:integer" nillable="true" />
> <xsd:element name="Environment" type="xsd:string" nillable="true" />
> <xsd:element name="CodeReviewUserID" type="xsd:integer" nillable="true" />
> <xsd:element name="Patch" type="xsd:string" nillable="true" />
> <xsd:element name="WhoMergedUserID" type="xsd:integer" nillable="true" />
> <xsd:element name="DateMerged" type="xsd:dateTime"
> sql:datatype="dateTime" nillable="true" />
> <xsd:element name="TestImperialUserID" type="xsd:integer"
> nillable="true" />
> <xsd:element name="TestMetericUserID" type="xsd:integer" nillable="true"
> />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> The Update Gram is:
> <root xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
> <sync mapping-schema="..\templates\USRMapSchema.xml"
> xmlns="urn:schemas-microsoft-com:xml-updategram">
> <updg:before>
> <USR updg:id="USR1">
> <USRID>1</USRID>
> <USRTitle>New USR</USRTitle>
> <Billable>false</Billable>
> <IsFree>true</IsFree>
> <UserID>1</UserID>
> </USR>
> </updg:before>
> <updg:after>
> <USR updg:id="USR1">
> <USRID>1</USRID>
> <USRTitle>updated title usr 1</USRTitle>
> <Billable>false</Billable>
> <IsFree>true</IsFree>
> <UserID>1</UserID>
> </USR>
> </updg:after>
> <updg:before>
> <MergeHdr updg:id="MergeHdr1">
> <MergeID>1</MergeID>
> <USRID>1</USRID>
> <ScreenChange>false</ScreenChange>
> <Translation>false</Translation>
> <MenuChange>false</MenuChange>
> <SecurityChange>false</SecurityChange>
> <JobSetupChange>false</JobSetupChange>
> <MessageFileChange>false</MessageFileChange>
> <ManualCodeTableChange>false</ManualCodeTableChange>
> <DatabaseChange>false</DatabaseChange>
> <DictionaryChange>false</DictionaryChange>
> <DataBomb>false</DataBomb>
> <CoreReports>false</CoreReports>
> <PlanetPressJetForm>false</PlanetPressJetForm>
> <ConfigurationFile>false</ConfigurationFile>
> <XSLTFile>false</XSLTFile>
> <SQLFile>false</SQLFile>
> <ExtractFile>false</ExtractFile>
> </MergeHdr>
> </updg:before>
> <updg:after>
> <MergeHdr updg:id="MergeHdr1">
> <MergeID>1</MergeID>
> <USRID>1</USRID>
> <ScreenChange>true</ScreenChange>
> <Translation>true</Translation>
> <MenuChange>true</MenuChange>
> <SecurityChange>false</SecurityChange>
> <JobSetupChange>false</JobSetupChange>
> <MessageFileChange>false</MessageFileChange>
> <ManualCodeTableChange>false</ManualCodeTableChange>
> <DatabaseChange>false</DatabaseChange>
> <DictionaryChange>false</DictionaryChange>
> <DataBomb>false</DataBomb>
> <CoreReports>false</CoreReports>
> <PlanetPressJetForm>false</PlanetPressJetForm>
> <ConfigurationFile>false</ConfigurationFile>
> <XSLTFile>false</XSLTFile>
> <SQLFile>false</SQLFile>
> <ExtractFile>false</ExtractFile>
> </MergeHdr>
> </updg:after>
> <updg:before>
> <MergeList updg:id="MergeList1">
> <MergeListID>1</MergeListID>
> <MergeID>1</MergeID>
> <DateMerged>2005-01-17T17:27:36.4470000-08:00</DateMerged>
> </MergeList>
> </updg:before>
> <updg:after>
> <MergeList updg:id="MergeList1">
> <MergeListID>1</MergeListID>
> <MergeID>1</MergeID>
> <Environment>new item usr 1</Environment>
> </MergeList>
> </updg:after>
> </sync>
> </root>
> Thanks in advance!

Does many tables matters

Does it matter if we have hugh number of tables vs few tables.
One example is this.
We have a table called Vendors where VendorID is the Primary key, and
another table VendorNotes (VendorID Int, Note varchar(500)) where VendorID
is the foreign key. This is one to one relation, and all vendors won't have
notes.
Is it good practice to do like this, or just add Note column to the vendors
table, and let it be null.
And does it matter if we add many columns to a table without using it.
Please give me some advices/suggestions. I need it desperately.
Thanks for any help you can provide
SteveDatabases with hundreds, even thousands, of tables is not that unusual.
Unused columns take small amounts of storage space (space is inexpensive).
The trade off is storage/retreival cost vs. development/programming cost. In
general, I think it's a 'non issue'.
Having Vendors and VendorNotes is quite acceptable -especially if the notes
are subject to frequent change and growth in size. Of course, the db purists
would say NO, all data related directly to the Vendor key *should* be in the
same table. Others would accept this arrangement for performance and
stability reasons.
So, the tried and true response is: It Depends. It depends upon what works
best for your design and the skill sets of those that have to develop and
maintian the applications that use the database.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Steve, Putman" <Steve@.noemailcom> wrote in message
news:OY$nP5tlGHA.4144@.TK2MSFTNGP05.phx.gbl...
> Does it matter if we have hugh number of tables vs few tables.
> One example is this.
> We have a table called Vendors where VendorID is the Primary key, and
> another table VendorNotes (VendorID Int, Note varchar(500)) where VendorID
> is the foreign key. This is one to one relation, and all vendors won't
> have notes.
> Is it good practice to do like this, or just add Note column to the
> vendors table, and let it be null.
> And does it matter if we add many columns to a table without using it.
> Please give me some advices/suggestions. I need it desperately.
> Thanks for any help you can provide
> Steve
>
>|||Thanks Arnie for your quick reply.
My assumption was that it does not take any storage space if column values
are null.
Steve,
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:OXLSCFulGHA.4512@.TK2MSFTNGP04.phx.gbl...
> Databases with hundreds, even thousands, of tables is not that unusual.
> Unused columns take small amounts of storage space (space is inexpensive).
> The trade off is storage/retreival cost vs. development/programming cost.
> In general, I think it's a 'non issue'.
> Having Vendors and VendorNotes is quite acceptable -especially if the
> notes are subject to frequent change and growth in size. Of course, the db
> purists would say NO, all data related directly to the Vendor key *should*
> be in the same table. Others would accept this arrangement for performance
> and stability reasons.
> So, the tried and true response is: It Depends. It depends upon what works
> best for your design and the skill sets of those that have to develop and
> maintian the applications that use the database.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Steve, Putman" <Steve@.noemailcom> wrote in message
> news:OY$nP5tlGHA.4144@.TK2MSFTNGP05.phx.gbl...
>|||Oh NULL values take up storage space alright.
"Steve, Putman" <Steve@.noemailcom> wrote in message
news:eo2IHYwlGHA.748@.TK2MSFTNGP02.phx.gbl...
> Thanks Arnie for your quick reply.
> My assumption was that it does not take any storage space if column values
> are null.
> Steve,
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:OXLSCFulGHA.4512@.TK2MSFTNGP04.phx.gbl...
>|||NULL values do take up storage space. Not as much as, say, a CHAR(40), but
unfortunately representing NULLs does take up some space. Is space your
primary concern?
"Steve, Putman" <Steve@.noemailcom> wrote in message
news:eo2IHYwlGHA.748@.TK2MSFTNGP02.phx.gbl...
> Thanks Arnie for your quick reply.
> My assumption was that it does not take any storage space if column values
> are null.
> Steve,
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:OXLSCFulGHA.4512@.TK2MSFTNGP04.phx.gbl...
>|||I tend to avoid one-to-one joins if I can. I recognize that NULLs can
take up space, and uing a 1-to-1 is a solution for that, but having a
1-to-1 join means that everytime I want to pull back information about
a vendor (including notes), I have to perform a table join. This (in
my opinion) is unnecessary in most scenarios. If an attribute of an
entity exists, then it should belong with that entity.
However, why do your vendors only have one note? This seems like a
great scenario for a 0-to-many join; if you want to add information to
a vendor, INSERT another row in your notes table. If two people are
adding notes about a vendor, then there is minimal opportunity for
concurrency issues.
In sum: if your entity (Vendor) truly has only one instance of an
attribute (VendorNote), then I would include it in the table, and allow
for NULLs (again, a design choice). However, I would first question
why is a VendorNote a singularity.
Stu
Steve, Putman wrote:
> Does it matter if we have hugh number of tables vs few tables.
> One example is this.
> We have a table called Vendors where VendorID is the Primary key, and
> another table VendorNotes (VendorID Int, Note varchar(500)) where VendorID
> is the foreign key. This is one to one relation, and all vendors won't hav
e
> notes.
> Is it good practice to do like this, or just add Note column to the vendor
s
> table, and let it be null.
> And does it matter if we add many columns to a table without using it.
> Please give me some advices/suggestions. I need it desperately.
> Thanks for any help you can provide
> Steve|||>> We have a table called Vendors where VendorID is the Primary key, and an
other table .. <<
The other table is tricky than your pseudo-code:
CREATE TABLE VendorNotes
(vendor_id INTEGER NOT NULL PRIMARY KEY
REFERENCES Vendors(vendor_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
vendor_note VARCHAR(500) NOT NULL);
The **required** uniqueness constraint has overhead. The **required**
DRI actions have overhead. Or you can take the attitude that the
database can fill up with orphans and other crap until it chokes or has
no integrity. And every time you use it, you need an OUTER JOIN. My
favorite was one of these things where a series of identifiers got
re-used and inherited orphans in the un-constrainted 1:1 table.
The cost of adding a few of NULLs is basically a bit flag to mark a
column as NULL-able, or you can default it to an empty string. That is
not looking so bad now.|||Thanks Guys,
Actualy Vendors-VendorNote is just an example I gave.
Actually there are around 40 columns which we have added to a table, and
which is very very rarely used, or may never be used.
In this scenerio should be ok to keep in on a same table or better to
seperate it.
My original question was related to this.
I am still in a learning stage. So I need to follow some good practice.
Steve
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1151201152.819334.35550@.y41g2000cwy.googlegroups.com...
> The other table is tricky than your pseudo-code:
> CREATE TABLE VendorNotes
> (vendor_id INTEGER NOT NULL PRIMARY KEY
> REFERENCES Vendors(vendor_id)
> ON DELETE CASCADE
> ON UPDATE CASCADE,
> vendor_note VARCHAR(500) NOT NULL);
> The **required** uniqueness constraint has overhead. The **required**
> DRI actions have overhead. Or you can take the attitude that the
> database can fill up with orphans and other crap until it chokes or has
> no integrity. And every time you use it, you need an OUTER JOIN. My
> favorite was one of these things where a series of identifiers got
> re-used and inherited orphans in the un-constrainted 1:1 table.
> The cost of adding a few of NULLs is basically a bit flag to mark a
> column as NULL-able, or you can default it to an empty string. That is
> not looking so bad now.
>|||When learning, it's always best to rely on theory. You can develop
"practical" work-arounds later in your career (when theory fails to
perform as well as needed in real-world scenarios). Doing a 1-to-1
join in order to build a complete entity (to avoid the storage of
NULLS) is a practical solution, not a theoretical one.
My .02
Stu
Steve, Putman wrote:
> Thanks Guys,
> Actualy Vendors-VendorNote is just an example I gave.
> Actually there are around 40 columns which we have added to a table, and
> which is very very rarely used, or may never be used.
> In this scenerio should be ok to keep in on a same table or better to
> seperate it.
> My original question was related to this.
> I am still in a learning stage. So I need to follow some good practice.
> Steve
>
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1151201152.819334.35550@.y41g2000cwy.googlegroups.com...

does linked server solve performance issue??

Hi,
I've this query using four tables of 3 different databases residing on
the same server
select top 5 * from dblezen.dbo.ads ta
left outer join dbalgemeen.dbo.users tb on ta.ads_usrid = tb.usr_id
left outer join dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
left outer join dbingeven.dbo.edition td on tc.ads_editionid = td.ed_id
where ta.ads_boonline > dateadd(n, -100, getdate())
In the near future we'll propably put the 3 databases on 3 different
physical servers. So I'll have to create linked servers, meaning I've
got to execute something like this :
select top 5 * from server1.dblezen.dbo.ads ta
left outer join server2.dbalgemeen.dbo.users tb on ta.ads_usrid = tb.usr_id
left outer join server3.dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
left outer join server3.dbingeven.dbo.edition td on tc.ads_editionid =
td.ed_id
where ta.ads_boonline > dateadd(n, -100, getdate())
First question:
Can I fix that the servernames server1, server2 and server3 are logical
names (different than the computername) all refering to the same local
server?
If this is possible I guess I can adapt all my stored procedures before
effectively put the 3 databases on 3 different servers, and if
necessary, I can go back to 1 server again afterwards, without having to
change my stored procedures again.
Second question:
If it can be done, will it have consequences on the execution time of
the queries? Will there be overhead 1) because SQL server is going to
use distributed transaction instead of local transactions or 2) because
SQL server has to translate the logical server names into physical
server names while it's actually not necessary when the databases
reside on the same server.
Third question:
Is moving the 3 databases to 3 different servers and start using linked
server the obvious best option to resolve the performance problem of our
database server?
At the moment we have one IIS-server, running ASP.NET and one database
server, running SQL Server, using 3 databases.
- dbingeven is mainly used to insert new rows
- dblezen is mainly used to read rows (full text indexed)
- dbalgemeen contains general data used by the other two (user data,
parameters, statistical data, ...)
Data is continuously inserted in dbingeven and continuously copied
(after processing) to dblezen.
All 3 databases contain stored procedures refering each other all the
time (joins as in the query above as well as calling each others stored
procedures).
Fourth question:
Is it predictable the gain of performance win (on CPU, and disk access)
by spreading the data will be lost on network traffic and distributed
transactions processes, meaning our problem will not be really solved?
Thanks in Advance,
Peter Van Wilrijk.
Why don't you replicate the data back to the server that you're query is run
on, and select from there?
"Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message
news:N_Vte.126388$I62.7018547@.phobos.telenet-ops.be...
> Hi,
> I've this query using four tables of 3 different databases residing on the
> same server
> select top 5 * from dblezen.dbo.ads ta
> left outer join dbalgemeen.dbo.users tb on ta.ads_usrid = tb.usr_id
> left outer join dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
> left outer join dbingeven.dbo.edition td on tc.ads_editionid = td.ed_id
> where ta.ads_boonline > dateadd(n, -100, getdate())
> In the near future we'll propably put the 3 databases on 3 different
> physical servers. So I'll have to create linked servers, meaning I've
> got to execute something like this :
> select top 5 * from server1.dblezen.dbo.ads ta
> left outer join server2.dbalgemeen.dbo.users tb on ta.ads_usrid =
> tb.usr_id
> left outer join server3.dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
> left outer join server3.dbingeven.dbo.edition td on tc.ads_editionid =
> td.ed_id
> where ta.ads_boonline > dateadd(n, -100, getdate())
> First question:
> Can I fix that the servernames server1, server2 and server3 are logical
> names (different than the computername) all refering to the same local
> server?
> If this is possible I guess I can adapt all my stored procedures before
> effectively put the 3 databases on 3 different servers, and if necessary,
> I can go back to 1 server again afterwards, without having to change my
> stored procedures again.
> Second question:
> If it can be done, will it have consequences on the execution time of the
> queries? Will there be overhead 1) because SQL server is going to use
> distributed transaction instead of local transactions or 2) because SQL
> server has to translate the logical server names into physical server
> names while it's actually not necessary when the databases
> reside on the same server.
> Third question:
> Is moving the 3 databases to 3 different servers and start using linked
> server the obvious best option to resolve the performance problem of our
> database server?
> At the moment we have one IIS-server, running ASP.NET and one database
> server, running SQL Server, using 3 databases.
> - dbingeven is mainly used to insert new rows
> - dblezen is mainly used to read rows (full text indexed)
> - dbalgemeen contains general data used by the other two (user data,
> parameters, statistical data, ...)
> Data is continuously inserted in dbingeven and continuously copied (after
> processing) to dblezen.
> All 3 databases contain stored procedures refering each other all the time
> (joins as in the query above as well as calling each others stored
> procedures).
> Fourth question:
> Is it predictable the gain of performance win (on CPU, and disk access)
> by spreading the data will be lost on network traffic and distributed
> transactions processes, meaning our problem will not be really solved?
>
> Thanks in Advance,
> Peter Van Wilrijk.
>
>
>
>
>
|||ChrisR wrote:
> Why don't you replicate the data back to the server that you're query is run
> on, and select from there?
>
Thanks, Good question?
I surely must start checking out how to implement replication ... but I
guess it will not be an option for our website, because our users, while
surfing ... read, insert and update data in all three databases. I
guess this means we should replicate continuously in two directions, so
updates, deletes and inserts on server 2 must be immediately available
on server 1 and vice versa. Can replication do that?
Kind regards,
Peter Roothans.
|||Yes. Look up Transactional Replication in BOL. You will want to use the
Immediate Updating option.
"Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message
news:Tu9ue.126847$Xj7.7072901@.phobos.telenet-ops.be...
> ChrisR wrote:
> Thanks, Good question?
> I surely must start checking out how to implement replication ... but I
> guess it will not be an option for our website, because our users, while
> surfing ... read, insert and update data in all three databases. I guess
> this means we should replicate continuously in two directions, so updates,
> deletes and inserts on server 2 must be immediately available on server 1
> and vice versa. Can replication do that?
> Kind regards,
> Peter Roothans.
>
|||Thanks ChrisR.
I just found out you can give 1 server multiple names as follows.
sp_addlinkedserver N'SRVDBI', ' ', N'SQLOLEDB', N'SRV-WEBDB'
sp_addlinkedserver N'SRVDBL', ' ', N'SQLOLEDB', N'SRV-WEBDB'
sp_addlinkedserver N'SRVDBA', ' ', N'SQLOLEDB', N'SRV-WEBDB'
So, this way I can move my database to any server without the need to
adapt all code. The only thing to do than, is to let refer the logical
name to another physical server.
I've read BOL concerning Transactional Replication. I'm certainly going
to try it out, but since I'm not familiair with it and since the
document warns for loopback detection when replicating multiple related
databases, I'll start with the linked server solution.
Thanks,
Kind regards,
Peter Van Wilrijk
ChrisR wrote:
> Yes. Look up Transactional Replication in BOL. You will want to use the
> Immediate Updating option.
>
> "Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message
> news:Tu9ue.126847$Xj7.7072901@.phobos.telenet-ops.be...
>
>

does linked server solve performance issue??

Hi,
I've this query using four tables of 3 different databases residing on
the same server
select top 5 * from dblezen.dbo.ads ta
left outer join dbalgemeen.dbo.users tb on ta.ads_usrid = tb.usr_id
left outer join dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
left outer join dbingeven.dbo.edition td on tc.ads_editionid = td.ed_id
where ta.ads_boonline > dateadd(n, -100, getdate())
In the near future we'll propably put the 3 databases on 3 different
physical servers. So I'll have to create linked servers, meaning I've
got to execute something like this :
select top 5 * from server1.dblezen.dbo.ads ta
left outer join server2.dbalgemeen.dbo.users tb on ta.ads_usrid = tb.usr_id
left outer join server3.dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
left outer join server3.dbingeven.dbo.edition td on tc.ads_editionid =
td.ed_id
where ta.ads_boonline > dateadd(n, -100, getdate())
First question:
Can I fix that the servernames server1, server2 and server3 are logical
names (different than the computername) all refering to the same local
server?
If this is possible I guess I can adapt all my stored procedures before
effectively put the 3 databases on 3 different servers, and if
necessary, I can go back to 1 server again afterwards, without having to
change my stored procedures again.
Second question:
If it can be done, will it have consequences on the execution time of
the queries? Will there be overhead 1) because SQL server is going to
use distributed transaction instead of local transactions or 2) because
SQL server has to translate the logical server names into physical
server names while it's actually not necessary when the databases
reside on the same server.
Third question:
Is moving the 3 databases to 3 different servers and start using linked
server the obvious best option to resolve the performance problem of our
database server'
At the moment we have one IIS-server, running ASP.NET and one database
server, running SQL Server, using 3 databases.
- dbingeven is mainly used to insert new rows
- dblezen is mainly used to read rows (full text indexed)
- dbalgemeen contains general data used by the other two (user data,
parameters, statistical data, ...)
Data is continuously inserted in dbingeven and continuously copied
(after processing) to dblezen.
All 3 databases contain stored procedures refering each other all the
time (joins as in the query above as well as calling each others stored
procedures).
Fourth question:
Is it predictable the gain of performance win (on CPU, and disk access)
by spreading the data will be lost on network traffic and distributed
transactions processes, meaning our problem will not be really solved'
Thanks in Advance,
Peter Van Wilrijk.Why don't you replicate the data back to the server that you're query is run
on, and select from there?
"Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message
news:N_Vte.126388$I62.7018547@.phobos.telenet-ops.be...
> Hi,
> I've this query using four tables of 3 different databases residing on the
> same server
> select top 5 * from dblezen.dbo.ads ta
> left outer join dbalgemeen.dbo.users tb on ta.ads_usrid = tb.usr_id
> left outer join dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
> left outer join dbingeven.dbo.edition td on tc.ads_editionid = td.ed_id
> where ta.ads_boonline > dateadd(n, -100, getdate())
> In the near future we'll propably put the 3 databases on 3 different
> physical servers. So I'll have to create linked servers, meaning I've
> got to execute something like this :
> select top 5 * from server1.dblezen.dbo.ads ta
> left outer join server2.dbalgemeen.dbo.users tb on ta.ads_usrid =
> tb.usr_id
> left outer join server3.dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
> left outer join server3.dbingeven.dbo.edition td on tc.ads_editionid =
> td.ed_id
> where ta.ads_boonline > dateadd(n, -100, getdate())
> First question:
> Can I fix that the servernames server1, server2 and server3 are logical
> names (different than the computername) all refering to the same local
> server?
> If this is possible I guess I can adapt all my stored procedures before
> effectively put the 3 databases on 3 different servers, and if necessary,
> I can go back to 1 server again afterwards, without having to change my
> stored procedures again.
> Second question:
> If it can be done, will it have consequences on the execution time of the
> queries? Will there be overhead 1) because SQL server is going to use
> distributed transaction instead of local transactions or 2) because SQL
> server has to translate the logical server names into physical server
> names while it's actually not necessary when the databases
> reside on the same server.
> Third question:
> Is moving the 3 databases to 3 different servers and start using linked
> server the obvious best option to resolve the performance problem of our
> database server'
> At the moment we have one IIS-server, running ASP.NET and one database
> server, running SQL Server, using 3 databases.
> - dbingeven is mainly used to insert new rows
> - dblezen is mainly used to read rows (full text indexed)
> - dbalgemeen contains general data used by the other two (user data,
> parameters, statistical data, ...)
> Data is continuously inserted in dbingeven and continuously copied (after
> processing) to dblezen.
> All 3 databases contain stored procedures refering each other all the time
> (joins as in the query above as well as calling each others stored
> procedures).
> Fourth question:
> Is it predictable the gain of performance win (on CPU, and disk access)
> by spreading the data will be lost on network traffic and distributed
> transactions processes, meaning our problem will not be really solved'
>
> Thanks in Advance,
> Peter Van Wilrijk.
>
>
>
>
>|||ChrisR wrote:
> Why don't you replicate the data back to the server that you're query is r
un
> on, and select from there?
>
Thanks, Good question?
I surely must start checking out how to implement replication ... but I
guess it will not be an option for our website, because our users, while
surfing ... read, insert and update data in all three databases. I
guess this means we should replicate continuously in two directions, so
updates, deletes and inserts on server 2 must be immediately available
on server 1 and vice versa. Can replication do that?
Kind regards,
Peter Roothans.|||Yes. Look up Transactional Replication in BOL. You will want to use the
Immediate Updating option.
"Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message
news:Tu9ue.126847$Xj7.7072901@.phobos.telenet-ops.be...
> ChrisR wrote:
> Thanks, Good question?
> I surely must start checking out how to implement replication ... but I
> guess it will not be an option for our website, because our users, while
> surfing ... read, insert and update data in all three databases. I guess
> this means we should replicate continuously in two directions, so updates,
> deletes and inserts on server 2 must be immediately available on server 1
> and vice versa. Can replication do that?
> Kind regards,
> Peter Roothans.
>|||Thanks ChrisR.
I just found out you can give 1 server multiple names as follows.
sp_addlinkedserver N'SRVDBI', ' ', N'SQLOLEDB', N'SRV-WEBDB'
sp_addlinkedserver N'SRVDBL', ' ', N'SQLOLEDB', N'SRV-WEBDB'
sp_addlinkedserver N'SRVDBA', ' ', N'SQLOLEDB', N'SRV-WEBDB'
So, this way I can move my database to any server without the need to
adapt all code. The only thing to do than, is to let refer the logical
name to another physical server.
I've read BOL concerning Transactional Replication. I'm certainly going
to try it out, but since I'm not familiair with it and since the
document warns for loopback detection when replicating multiple related
databases, I'll start with the linked server solution.
Thanks,
Kind regards,
Peter Van Wilrijk
ChrisR wrote:
> Yes. Look up Transactional Replication in BOL. You will want to use the
> Immediate Updating option.
>
> "Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message
> news:Tu9ue.126847$Xj7.7072901@.phobos.telenet-ops.be...
>
>
>

does linked server solve performance issue??

Hi,
I've this query using four tables of 3 different databases residing on
the same server
select top 5 * from dblezen.dbo.ads ta
left outer join dbalgemeen.dbo.users tb on ta.ads_usrid = tb.usr_id
left outer join dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
left outer join dbingeven.dbo.edition td on tc.ads_editionid = td.ed_id
where ta.ads_boonline > dateadd(n, -100, getdate())
In the near future we'll propably put the 3 databases on 3 different
physical servers. So I'll have to create linked servers, meaning I've
got to execute something like this :
select top 5 * from server1.dblezen.dbo.ads ta
left outer join server2.dbalgemeen.dbo.users tb on ta.ads_usrid = tb.usr_id
left outer join server3.dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
left outer join server3.dbingeven.dbo.edition td on tc.ads_editionid = td.ed_id
where ta.ads_boonline > dateadd(n, -100, getdate())
First question:
Can I fix that the servernames server1, server2 and server3 are logical
names (different than the computername) all refering to the same local
server?
If this is possible I guess I can adapt all my stored procedures before
effectively put the 3 databases on 3 different servers, and if
necessary, I can go back to 1 server again afterwards, without having to
change my stored procedures again.
Second question:
If it can be done, will it have consequences on the execution time of
the queries? Will there be overhead 1) because SQL server is going to
use distributed transaction instead of local transactions or 2) because
SQL server has to translate the logical server names into physical
server names while it's actually not necessary when the databases
reside on the same server.
Third question:
Is moving the 3 databases to 3 different servers and start using linked
server the obvious best option to resolve the performance problem of our
database server'
At the moment we have one IIS-server, running ASP.NET and one database
server, running SQL Server, using 3 databases.
- dbingeven is mainly used to insert new rows
- dblezen is mainly used to read rows (full text indexed)
- dbalgemeen contains general data used by the other two (user data,
parameters, statistical data, ...)
Data is continuously inserted in dbingeven and continuously copied
(after processing) to dblezen.
All 3 databases contain stored procedures refering each other all the
time (joins as in the query above as well as calling each others stored
procedures).
Fourth question:
Is it predictable the gain of performance win (on CPU, and disk access)
by spreading the data will be lost on network traffic and distributed
transactions processes, meaning our problem will not be really solved'
Thanks in Advance,
Peter Van Wilrijk.Why don't you replicate the data back to the server that you're query is run
on, and select from there?
"Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message
news:N_Vte.126388$I62.7018547@.phobos.telenet-ops.be...
> Hi,
> I've this query using four tables of 3 different databases residing on the
> same server
> select top 5 * from dblezen.dbo.ads ta
> left outer join dbalgemeen.dbo.users tb on ta.ads_usrid = tb.usr_id
> left outer join dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
> left outer join dbingeven.dbo.edition td on tc.ads_editionid = td.ed_id
> where ta.ads_boonline > dateadd(n, -100, getdate())
> In the near future we'll propably put the 3 databases on 3 different
> physical servers. So I'll have to create linked servers, meaning I've
> got to execute something like this :
> select top 5 * from server1.dblezen.dbo.ads ta
> left outer join server2.dbalgemeen.dbo.users tb on ta.ads_usrid => tb.usr_id
> left outer join server3.dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
> left outer join server3.dbingeven.dbo.edition td on tc.ads_editionid => td.ed_id
> where ta.ads_boonline > dateadd(n, -100, getdate())
> First question:
> Can I fix that the servernames server1, server2 and server3 are logical
> names (different than the computername) all refering to the same local
> server?
> If this is possible I guess I can adapt all my stored procedures before
> effectively put the 3 databases on 3 different servers, and if necessary,
> I can go back to 1 server again afterwards, without having to change my
> stored procedures again.
> Second question:
> If it can be done, will it have consequences on the execution time of the
> queries? Will there be overhead 1) because SQL server is going to use
> distributed transaction instead of local transactions or 2) because SQL
> server has to translate the logical server names into physical server
> names while it's actually not necessary when the databases
> reside on the same server.
> Third question:
> Is moving the 3 databases to 3 different servers and start using linked
> server the obvious best option to resolve the performance problem of our
> database server'
> At the moment we have one IIS-server, running ASP.NET and one database
> server, running SQL Server, using 3 databases.
> - dbingeven is mainly used to insert new rows
> - dblezen is mainly used to read rows (full text indexed)
> - dbalgemeen contains general data used by the other two (user data,
> parameters, statistical data, ...)
> Data is continuously inserted in dbingeven and continuously copied (after
> processing) to dblezen.
> All 3 databases contain stored procedures refering each other all the time
> (joins as in the query above as well as calling each others stored
> procedures).
> Fourth question:
> Is it predictable the gain of performance win (on CPU, and disk access)
> by spreading the data will be lost on network traffic and distributed
> transactions processes, meaning our problem will not be really solved'
>
> Thanks in Advance,
> Peter Van Wilrijk.
>
>
>
>
>|||ChrisR wrote:
> Why don't you replicate the data back to the server that you're query is run
> on, and select from there?
>
Thanks, Good question?
I surely must start checking out how to implement replication ... but I
guess it will not be an option for our website, because our users, while
surfing ... read, insert and update data in all three databases. I
guess this means we should replicate continuously in two directions, so
updates, deletes and inserts on server 2 must be immediately available
on server 1 and vice versa. Can replication do that?
Kind regards,
Peter Roothans.|||Yes. Look up Transactional Replication in BOL. You will want to use the
Immediate Updating option.
"Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message
news:Tu9ue.126847$Xj7.7072901@.phobos.telenet-ops.be...
> ChrisR wrote:
>> Why don't you replicate the data back to the server that you're query is
>> run on, and select from there?
> Thanks, Good question?
> I surely must start checking out how to implement replication ... but I
> guess it will not be an option for our website, because our users, while
> surfing ... read, insert and update data in all three databases. I guess
> this means we should replicate continuously in two directions, so updates,
> deletes and inserts on server 2 must be immediately available on server 1
> and vice versa. Can replication do that?
> Kind regards,
> Peter Roothans.
>|||Thanks ChrisR.
I just found out you can give 1 server multiple names as follows.
sp_addlinkedserver N'SRVDBI', ' ', N'SQLOLEDB', N'SRV-WEBDB'
sp_addlinkedserver N'SRVDBL', ' ', N'SQLOLEDB', N'SRV-WEBDB'
sp_addlinkedserver N'SRVDBA', ' ', N'SQLOLEDB', N'SRV-WEBDB'
So, this way I can move my database to any server without the need to
adapt all code. The only thing to do than, is to let refer the logical
name to another physical server.
I've read BOL concerning Transactional Replication. I'm certainly going
to try it out, but since I'm not familiair with it and since the
document warns for loopback detection when replicating multiple related
databases, I'll start with the linked server solution.
Thanks,
Kind regards,
Peter Van Wilrijk
ChrisR wrote:
> Yes. Look up Transactional Replication in BOL. You will want to use the
> Immediate Updating option.
>
> "Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message
> news:Tu9ue.126847$Xj7.7072901@.phobos.telenet-ops.be...
>>ChrisR wrote:
>>Why don't you replicate the data back to the server that you're query is
>>run on, and select from there?
>>
>>Thanks, Good question?
>>I surely must start checking out how to implement replication ... but I
>>guess it will not be an option for our website, because our users, while
>>surfing ... read, insert and update data in all three databases. I guess
>>this means we should replicate continuously in two directions, so updates,
>>deletes and inserts on server 2 must be immediately available on server 1
>>and vice versa. Can replication do that?
>>Kind regards,
>>Peter Roothans.
>
>