AdventureWorks inserting records to JobCandidates example

Hi
re: AdventureWorks example
Are there source code to how the
HumanResources.JobCandidate
table was created?
(I would like to look at the sample code for INSERTing)
thanks
joyceWhat exactly do you want to know about?
Thanks
Michael
PS: I don't think we have sample code for specifically this table...
"joyce chan" <joyceschan@.fastmail.fm> wrote in message
news:1169588888.525240.259000@.j27g2000cwj.googlegroups.com...
> Hi
> re: AdventureWorks example
> Are there source code to how the
> HumanResources.JobCandidate
> table was created?
> (I would like to look at the sample code for INSERTing)
> thanks
> joyce
>|||Actually, what I am really about is namespace...
So I thought I should take a look at how the JobCandidates table was
created in AdventureWorks.
I understand targetnamespace, and
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
I don't really understand default namespace such as
xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
"
What is the purpose of the def ns?
And also, what is the purpose of the schema collection? And how come a
schema collection can contain multiple schema.
And what is the advantage of typed vs untyped xml in sql server. Also,
does Sql server support any other types of schemas?
I'm still slowly going through the books online material, but it'd be
nice if all these questions are all answered at once.
Thank you, this is a great usenet group,
Joyce
On Jan 24, 8:05 pm, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
wrote:
> What exactly do you want to know about?
> Thanks
> Michael
> PS: I don't think we have sample code for specifically this table...
> "joyce chan" <joycesc...@.fastmail.fm> wrote in messagenews:1169588888.5252
40.259000@.j27g2000cwj.googlegroups.com...
>
>
>
>
>|||See inline below.
Best regards
Michael
"joyce chan" <joyceschan@.fastmail.fm> wrote in message
news:1169736748.697916.238950@.s48g2000cws.googlegroups.com...
> Actually, what I am really about is namespace...
> So I thought I should take a look at how the JobCandidates table was
> created in AdventureWorks.
> I understand targetnamespace, and
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> I don't really understand default namespace such as
> xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resu
me"
> What is the purpose of the def ns?
Default namespaces are just a syntactic convenience in that you do not have
to write a prefix (this is true for XML documents and XQuery, but not XPath
1.0 where no support for default namespace exists, you have to define a
prefix for the query).
Namespaces per se are just scoping mechanisms to scope a name to a space
that can have different semantics associated than the same name in a
different namespace... e.g., river:bank vs finance:bank.

> And also, what is the purpose of the schema collection? And how come a
> schema collection can contain multiple schema.
A schema collection is a meta data object in SQL Server that allows you to
combine multiple schemas in a logical unit to constrain and type an XML data
type instance with. It also introduces a way that allows you to have more
than one version of the same schema within your database. Note that a single
schema defines structural and some semantic constraints for names within a
namespace (including the non-namespace). However not every document having
data with namespaces needs to have a schema.

> And what is the advantage of typed vs untyped xml in sql server. Also,
> does Sql server support any other types of schemas?
Untyped XML gives you the advantage of complete flexibity of your structure
and no additional validation cost.
Typed XML gives you semantical assurances over the structure of the data,
provides error detection at compile time in your queries (using static
typing), and can be used for some storage and query optimizations.
SQL Server has only limited support for DTDs and other wise does not support
other types of schemas.

> I'm still slowly going through the books online material, but it'd be
> nice if all these questions are all answered at once.
> Thank you, this is a great usenet group,
> Joyce
> On Jan 24, 8:05 pm, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
> wrote:
>

AdventureWorks inserting records to JobCandidates example

Hi
re: AdventureWorks example
Are there source code to how the
HumanResources.JobCandidate
table was created?
(I would like to look at the sample code for INSERTing)
thanks
joyce
What exactly do you want to know about?
Thanks
Michael
PS: I don't think we have sample code for specifically this table...
"joyce chan" <joyceschan@.fastmail.fm> wrote in message
news:1169588888.525240.259000@.j27g2000cwj.googlegr oups.com...
> Hi
> re: AdventureWorks example
> Are there source code to how the
> HumanResources.JobCandidate
> table was created?
> (I would like to look at the sample code for INSERTing)
> thanks
> joyce
>
|||Actually, what I am really confused about is namespace...
So I thought I should take a look at how the JobCandidates table was
created in AdventureWorks.
I understand targetnamespace, and
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
I don't really understand default namespace such as
xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"
What is the purpose of the def ns?
And also, what is the purpose of the schema collection? And how come a
schema collection can contain multiple schema.
And what is the advantage of typed vs untyped xml in sql server. Also,
does Sql server support any other types of schemas?
I'm still slowly going through the books online material, but it'd be
nice if all these questions are all answered at once.
Thank you, this is a great usenet group,
Joyce
On Jan 24, 8:05 pm, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
wrote:[vbcol=seagreen]
> What exactly do you want to know about?
> Thanks
> Michael
> PS: I don't think we have sample code for specifically this table...
> "joyce chan" <joycesc...@.fastmail.fm> wrote in messagenews:1169588888.525240.259000@.j27g2000cwj.g ooglegroups.com...
>
>
|||See inline below.
Best regards
Michael
"joyce chan" <joyceschan@.fastmail.fm> wrote in message
news:1169736748.697916.238950@.s48g2000cws.googlegr oups.com...
> Actually, what I am really confused about is namespace...
> So I thought I should take a look at how the JobCandidates table was
> created in AdventureWorks.
> I understand targetnamespace, and
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> I don't really understand default namespace such as
> xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"
> What is the purpose of the def ns?
Default namespaces are just a syntactic convenience in that you do not have
to write a prefix (this is true for XML documents and XQuery, but not XPath
1.0 where no support for default namespace exists, you have to define a
prefix for the query).
Namespaces per se are just scoping mechanisms to scope a name to a space
that can have different semantics associated than the same name in a
different namespace... e.g., river:bank vs finance:bank.

> And also, what is the purpose of the schema collection? And how come a
> schema collection can contain multiple schema.
A schema collection is a meta data object in SQL Server that allows you to
combine multiple schemas in a logical unit to constrain and type an XML data
type instance with. It also introduces a way that allows you to have more
than one version of the same schema within your database. Note that a single
schema defines structural and some semantic constraints for names within a
namespace (including the non-namespace). However not every document having
data with namespaces needs to have a schema.

> And what is the advantage of typed vs untyped xml in sql server. Also,
> does Sql server support any other types of schemas?
Untyped XML gives you the advantage of complete flexibity of your structure
and no additional validation cost.
Typed XML gives you semantical assurances over the structure of the data,
provides error detection at compile time in your queries (using static
typing), and can be used for some storage and query optimizations.
SQL Server has only limited support for DTDs and other wise does not support
other types of schemas.

> I'm still slowly going through the books online material, but it'd be
> nice if all these questions are all answered at once.
> Thank you, this is a great usenet group,
> Joyce
> On Jan 24, 8:05 pm, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
> wrote:
>
sql

Advantage of Temp Table

When I import data I first import it from a text file into a table of
it's own, then using some logic insert some of the records into a
permanent table.

I am considering having the table that the data from the text file is
placed in being there all the time and just clearing it out after I do
the import, or creating it and after using it then drop it, or using a
temporary table.

What are the advantages of a temporary table as opposed to creating a
regular table and dropping it after use?Not much difference between a temp table and permenant table. Security,
automatic distruction, and one or the other may faster depending on the
disk/file layout. I would ask why not just push it directly into the
destination? Also if the text is coming from another SQL available source,
why not skip both the text file and the temporary table?

It also may be faster depending on need for availability, space, and
indexing to create a new table and do select into based on a union all of
the two tables. Then rename the table.

<shumaker@.cs.fsu.edu> wrote in message
news:1114111315.338927.231880@.g14g2000cwa.googlegr oups.com...
> When I import data I first import it from a text file into a table of
> it's own, then using some logic insert some of the records into a
> permanent table.
> I am considering having the table that the data from the text file is
> placed in being there all the time and just clearing it out after I do
> the import, or creating it and after using it then drop it, or using a
> temporary table.
> What are the advantages of a temporary table as opposed to creating a
> regular table and dropping it after use?|||Thanks.

"I would ask why not just push it directly into the
destination?"

Because I have to do some logic based on what is not in the imported
data. If a certain record exists in my table, but not in the data
being imported, then I need to modify some flags in the existing
record. So I import into an empty table, and do updates "if not in"
imported table then update my existing table.|||>> What are the advantages of a temporary table as opposed to creating
a regular table and dropping it after use? <<

A regular table will port, can have constraints, DRI actions, etc. But
do not drop it after use; just clean it out before and after you load
the data for scrubbing.

Advantage of setting isolation level to READ UNCOMMITED

Hi,
I have two questions.
1) What is the advantage of setting isolation level to READ UNCOMMITED if
there are noupdates for the records that we are selecting?
Does it make the select faster when we don't issue any shared lock?
2) I found this statement in books online:
'if an update acquires a large number of row locks and has locked a
significant percentage of a table, the row locks are escalated to a table
lock'
Is this statement true when we have a select instead of update and isolation
level is READ COMMITED?
Thanks,
RosiePerformance is slightly better when using READ UNCOMMITTED for selects, as
SQL Server does not have to do any of the work of issuing shared locks.
And yes, SELECTs can escalate lock granularities.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Rosie" <Rosie@.discussions.microsoft.com> wrote in message
news:2E0888C6-59F0-4E52-B4C8-2D91645D1FDB@.microsoft.com...
> Hi,
> I have two questions.
> 1) What is the advantage of setting isolation level to READ UNCOMMITED if
> there are noupdates for the records that we are selecting?
> Does it make the select faster when we don't issue any shared lock?
> 2) I found this statement in books online:
> 'if an update acquires a large number of row locks and has locked a
> significant percentage of a table, the row locks are escalated to a table
> lock'
> Is this statement true when we have a select instead of update and
isolation
> level is READ COMMITED?
> Thanks,
> Rosie|||> 1) What is the advantage of setting isolation level to READ UNCOMMITED if
> there are noupdates for the records that we are selecting?
> Does it make the select faster when we don't issue any shared lock?
Sure, since there is less work to do. Will it make it that much faster, not
really unless the statement would have been blocked.

> 2) I found this statement in books online:
> 'if an update acquires a large number of row locks and has locked a
> significant percentage of a table, the row locks are escalated to a table
> lock'
> Is this statement true when we have a select instead of update and
> isolation
> level is READ COMMITED?
If you are in read committed isolation level, then you would not escalate.
Basically, as rows are fetched a lock is taken on that row. Then the row is
placed on the output buffer, the lock is released, a new row is locked and
fetched, etc. So only one row should be locked during the select. The
holdup comes when the single row that need to be locked is already locked.
Then wait city. If you are doing lots of updates, or in READ COMMITTED or
SERIALIZABLE transaction isolation level, then the table lock might come in
to play, but you would likely have to lock a lot of rows.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Rosie" <Rosie@.discussions.microsoft.com> wrote in message
news:2E0888C6-59F0-4E52-B4C8-2D91645D1FDB@.microsoft.com...
> Hi,
> I have two questions.
> 1) What is the advantage of setting isolation level to READ UNCOMMITED if
> there are noupdates for the records that we are selecting?
> Does it make the select faster when we don't issue any shared lock?
> 2) I found this statement in books online:
> 'if an update acquires a large number of row locks and has locked a
> significant percentage of a table, the row locks are escalated to a table
> lock'
> Is this statement true when we have a select instead of update and
> isolation
> level is READ COMMITED?
> Thanks,
> Rosie

Advanced Expression?

I'm creating a report that looks through detail records and want it to sum
amounts based on a record type. If FieldType = 1 to sum accounts in a range
otherwise to sum all records with that account. The result is an error
[BC30201] Expression expected. The formula I have so far is:
=iif( Fields!FieldType.Value = 1, select sum( Fields!amount.Value) from
db.owner.table with (nolock) where Fields!account between
Fields!fromaccount.Value and Fields!toaccount.Value ,Sum(Fields!amount.Value))
I've also tried VB's Select/Case statement rather than IIF but it still
returns an error.
If this can't be done with expressions, can it be done someother way?
Thanks,
RickThis seems you might want to try creating a stored procedure and using the sp
as the basis of your report, not trying to calc that in a report cell. You
can't use a select statement from within the iif statement.
"rickp3131" wrote:
> I'm creating a report that looks through detail records and want it to sum
> amounts based on a record type. If FieldType = 1 to sum accounts in a range
> otherwise to sum all records with that account. The result is an error
> [BC30201] Expression expected. The formula I have so far is:
> =iif( Fields!FieldType.Value = 1, select sum( Fields!amount.Value) from
> db.owner.table with (nolock) where Fields!account between
> Fields!fromaccount.Value and Fields!toaccount.Value ,Sum(Fields!amount.Value))
> I've also tried VB's Select/Case statement rather than IIF but it still
> returns an error.
> If this can't be done with expressions, can it be done someother way?
> Thanks,
> Rick|||Mike,
Thanks for your reply. Are you referring to creating a stored procedure in
SQL or does RS have the ability to create stored procedures as well, like in
Report properties.custom code?
Since my post I came to the same conclusion you mention that what I'm trying
to do won't work in the reporting cell so I've been looking into creating a
function using custom code but as you mention, the select statement can't be
used within IIF. Can a stored procedure be called from IIF?
As a general custom code question, can C as well as VB code be used?
Thanks again!
Rick
"mike" wrote:
> This seems you might want to try creating a stored procedure and using the sp
> as the basis of your report, not trying to calc that in a report cell. You
> can't use a select statement from within the iif statement.
> "rickp3131" wrote:
> > I'm creating a report that looks through detail records and want it to sum
> > amounts based on a record type. If FieldType = 1 to sum accounts in a range
> > otherwise to sum all records with that account. The result is an error
> > [BC30201] Expression expected. The formula I have so far is:
> >
> > =iif( Fields!FieldType.Value = 1, select sum( Fields!amount.Value) from
> > db.owner.table with (nolock) where Fields!account between
> > Fields!fromaccount.Value and Fields!toaccount.Value ,Sum(Fields!amount.Value))
> >
> > I've also tried VB's Select/Case statement rather than IIF but it still
> > returns an error.
> >
> > If this can't be done with expressions, can it be done someother way?
> >
> > Thanks,
> > Rick|||You can try an SQL user defined function returning a table with summary
details, then use the IIF statement to get more specific, or you can use a
"Where" statement in your dataset
"rickp3131" wrote:
> I'm creating a report that looks through detail records and want it to sum
> amounts based on a record type. If FieldType = 1 to sum accounts in a range
> otherwise to sum all records with that account. The result is an error
> [BC30201] Expression expected. The formula I have so far is:
> =iif( Fields!FieldType.Value = 1, select sum( Fields!amount.Value) from
> db.owner.table with (nolock) where Fields!account between
> Fields!fromaccount.Value and Fields!toaccount.Value ,Sum(Fields!amount.Value))
> I've also tried VB's Select/Case statement rather than IIF but it still
> returns an error.
> If this can't be done with expressions, can it be done someother way?
> Thanks,
> Rick

ADP/ADE Permissions Issue

I cannot add, update or delete records in a SQL Server database using ADP/ADE but I can within another database on the same server.

I have two databases. One is the test environment and the other is production. Both database reside on the same server but they have unique logins so as not to allow an admin in the test environment to automatically be an admin in production.

I can do everything and anything necessary through Enterprise Manager to both databases. I can do everything necessary to an Access ADP/ADE application in the test environment (add, update, delete) but I cannot do the same in the production database. I can connect and get a valid connection but the tables do not allow me to update or add rows to tables through the ADP/ADE application.

Given that the only difference to the application is which database it connects to I am left with the conclusion that there is something different to the login in the production environment that is not allowing the ADP application appropriate permissions. Can anyone please help or at least direct me to an answer to correct this problem?

Thanks!Well... I suppose this is what happens when you aren't in charge of the data and it is migrated by someone else.

After much digging I found that the tables in question did not have their primary key migrated with them to the production server. If the table did not have a primary key defined then ADP/ADE would not allow a user to add rows.

This was structural, not security. Hopefully someone else who runs across a similar problem in the future will find this information and more quickly correct their issue.

ADO:Dispaly Limited Records

Hi all;
qrymillcgetbytype_app ( stored query in MS-Access) in my code :

MS-ACCESS Part:
query defination
PARAMETERS prmMillToolType Long;
SELECT MILLC.ID, MILLC.InsMillBdyID AS [Tool ID], MILLC.SubType, MILLC.Radius AS [End Radius], MILLC.CuttingDia AS [Tool Dia], MILLC.EffAxlCutLen AS [Effec Cut Length], MILLC.OverallLen AS [Overall Length], MILLC.HandOfCut AS [Hand Of Cut], MILLC.NoOfFlutes AS [No Of Flutes], MILLC.TmcID AS [Tool Class], MILLC.Comment, MILLC.Protrusion, MILLC.ShankDia, millC.ShoulderLen AS [Shoulder Length]
FROM MILLC
WHERE (((MILLC.[Mill Tool Type])=[prmMillToolType]) AND ((MILLC.[ON])=True))
ORDER BY MILLC.ID;

C++ PART
bstrSQL contain stored query name "qrymillcgetbytype_app"
hr = piRecordSet->put_Source(bstrSQL);
piRecordSet->Open(vNull, vNull, adOpenKeyset, adLockOptimistic, adCmdUnknown)

it opens fine
but when bstrSQL contain this query name " qrymillcgetbytype_app WHERE ID > -1 AND [Tool Dia] >= 0.000000 AND [Tool Dia] <= 5.000000 "

it displays the same output. Where clause won't work?
Pls help me out.

It sounds like you want to further refine the results of the qrymillcgetbytype query...In this case, I wouldn't expect appending an additional where clause to the query name to be parsed correctly. One suggestion that might work is to use something like:

SELECT * FROM qrymillcgetbytype_app WHERE <your where clause here>

|||

Hi ;

Thanks for reply.

Well previous code was functional in case of DAO.

When I do Creating a recordeset by DAO.

but that Code should also be functional for the case of ADO?

Waiting for your response.

Thanks in Advance Again.

ado datasets

hi

i am using older ado datasets in a borland 6 program.

i need to retrieve the data per record count....

example is that i need all 1000 records but i only want to retrieve them 100 at a time....

how will i accomplish this?

This may give you some usable ideas.

Paging Queries
www.aspfaq.com/2120

ADO connection

In VB6 I use more Forms where users can Add, Edit, Delete Records from more tables.

WHICH IS THE BEST METHOD

1. To open a connection when user start the application and I close the connection when user leave the application.

In Login form (Public cn As ADODB.Connection)

SirConectare_SQL = "Provider=SQLOLEDB.1" & _
";Password='" & Pass & "'" & _
";Persist Security Info=False" & _
";User ID='" & UserName & "'" & _
";Initial Catalog='" & DataBaseName & "'" & _
";Data Source='" & ServerName & "'"

Set cn = New ADODB.Connection

With cn
.ConnectionString = SirConectare_SQL
.Open
End With

OR

2. To Open a connection in each Form and close the connection when Form is UnLoadetThe first case is not possible because

Set cn = New ADODB.Connection (this is in Form Login)
In other form it is necesary to set the connection again.|||The rule is that you only keep the connection open as long as needed - and no longer. What are your concerns ?|||How many clients will use this application ?|||5-8 users|||Will the security credentials be different for each user ? What is the purpose for the application and the multiple forms ? Are you thinking about opening 1 connection object that will be used for multiple recordset/command objects ?|||How I can open a connection when user start the application and kepp open until user leave the application ?

In module:
Public cn As ADODB.Connection

In first form when is load:
SirConectare_SQL = "Provider=SQLOLEDB.1" & _
";Password='" & Pass & "'" & _
";Persist Security Info=False" & _
";User ID='" & UserName & "'" & _
";Initial Catalog='" & DataBaseName & "'" & _
";Data Source='" & ServerName & "'"

Set cn = New ADODB.Connection

With cn
.ConnectionString = SirConectare_SQL
.Open
End With

If I want to use "cn" in other forms is not possible (is not open)|||Are you destroying/closing the connection before the 2nd form is loaded ?|||I don`t close the connection, but I close (Unload) the first form.
The connection is closed ?|||Where are you declaring - Public cn As ADODB.Connection ?|||This declaration is made on a Module

Public cn As ADODB.Connection

In the Startup Form (where user input UserName and Password)

Set cn = New ADODB.Connection

With cn
.ConnectionString = SirConectare_SQL
.Open
End With

If I want to use this connection on other forms I can't because is Closed.
I want to open the connection only one time(when application startup), and if it possible to use the connection in all form.|||Open the connection in a module or class when login in form successfull. Close it when application main form is closed.|||This Looks Like VB code If it is Do not forget to set the DBconnection ( and all all objects associated to it) = nothing

set cn = nothing

I personnally like to use 1 public DB connection instead of multiple connections per app.

Hope this helps.
LJ|||Hello Gurus out there!

I want to know what would be the best method in opening a connection to server:

1. a connection (declared globally) that is open once during Login and access thru all forms and be closed only when application is terminated
What is the advantage/disadvantage of this method in my SQL Server 2k resources or in any RDBMS?

2. a connection is opened only when needed but everytime i execute a query against the database i have to open also that connection and terminate when it is not used...What is the advantage/disadvantage of this method in my SQL Server 2k resources or in any RDBMS?

Secondly, how can we know the resources used by the users that are connected to my SQL SERVER in terms of memory usage and CPU?

Im using VB/FOxPro and I want to know the best practice in terms of opening a connection to the database coz Im expecting to have 20 or more users online simultanously and hook to my server as soon as we are finished with our system.

I hope you can light up our minds with these concerns.

Thanks,

Bernie|||Bernie - What driver are you using ?