How to create SELECT?

I have the two following tables:

tblProject:
ProjectID
5001
5002
6001
6002
7001
7002

tblProject_type:
ProjectTypeID ProjectIDFrom ProjectIDTo
A 5000 5999
A 7000 7999

I need to create a SELECT statement which shows all records from
tblProjects WHERE ProjectTypeID = A. In this case every project except
6001 and 6002.

Unfortunately I can't have the ProjectTypeID in tblProject because each
project can belong to many project types. I know it sounds crazy but
that's the way my customers company is organized.

I'm very grateful for help in the right direction!

Regards,

SCREATE TABLE tblProjects(ProjectID INT)
INSERT INTO tblProjects(ProjectID) VALUES(5001)
INSERT INTO tblProjects(ProjectID) VALUES(5002)
INSERT INTO tblProjects(ProjectID) VALUES(6001)
INSERT INTO tblProjects(ProjectID) VALUES(6002)
INSERT INTO tblProjects(ProjectID) VALUES(7001)
INSERT INTO tblProjects(ProjectID) VALUES(7002)

CREATE TABLE tblProject_type(ProjectTypeID CHAR(1),ProjectIDFrom
INT,ProjectIDTo INT)
INSERT INTO tblProject_type(ProjectTypeID,ProjectIDFrom,Projec tIDTo)
VALUES('A',5000,5999)
INSERT INTO tblProject_type(ProjectTypeID,ProjectIDFrom,Projec tIDTo)
VALUES('A',7000,7999)

SELECT ProjectID
FROM tblProjects
WHERE EXISTS (SELECT * FROM tblProject_type
WHERE ProjectID BETWEEN ProjectIDFrom AND ProjectIDTo
AND ProjectTypeID='A')|||sta...@.gmail.com wrote:
> I have the two following tables:
> tblProject:
> ProjectID
> 5001
> 5002
> 6001
> 6002
> 7001
> 7002
> tblProject_type:
> ProjectTypeID ProjectIDFrom ProjectIDTo
> A 5000 5999
> A 7000 7999
> I need to create a SELECT statement which shows all records from
> tblProjects WHERE ProjectTypeID = A. In this case every project except
> 6001 and 6002.

Try:

SELECT projectid
FROM tblProject AS P
WHERE NOT EXISTS
(SELECT *
FROM tblProject_type AS T
WHERE P.projectid BETWEEN T.projectidfrom AND T.projectidto
AND T.projecttypeid = 'A');

> Unfortunately I can't have the ProjectTypeID in tblProject because each
> project can belong to many project types. I know it sounds crazy but
> that's the way my customers company is organized.

Instead of what you posted, a more common solution would be:

CREATE TABLE tblProject_ProjectType
(projectid INTEGER NOT NULL
REFERENCES tblProject (projectid),
projecttypeid CHAR(1) NOT NULL
REFERENCES tblProjectType (projecttypeid),
PRIMARY KEY (projectid, projecttyypeid);

There isn't necessarily anything wrong with what you posted and your
version certainly could make for a smaller table but it's also hard to
avoid redundancy. Specifically, you would have to use a trigger to
prevent overlapping ranges of rows for the same type - otherwise you
could get duplicate rows out of joins, which could give you incorrect
results.

The benefit of my tblProject_ProjectType version is that there is no
redundancy and joins to the table are always equijoins.

Hope this helps.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Thank's a lot for excellent help!

Regards,

S|||Thank's a lot for excellent help!

Regards,

S

how to create rule which update other columns

Hi,

I have a table with the following columns:
ID INTEGEDR,
Name VARCHAR(32),
Surname VARCHAR(32),
GroupID INTEGER,
SubGroupOneID INTEGER,
SubGroupTwoID INTEGER

How can I create a rule/default/check which update SubGroupOneID &
SubGroupTwoID columns when GroupID for example is equal 15 on
MSSQL2000.

It is imposible to make changes on client, so I need to check
inserted/updated value of GroupID column and automaticly update
SubGroupOneID & SubGroupTwoID columns.

Sincerely,
Rustam BogubaevYou haven't explained what value(s) you want the subgroup columns updated
to.

If the two subgroup columns are solely determined by the the Groupid then
the answer is not to put those columns in the table at all because to do so
destroys normalisation in your schema. Put the subgroups in a separate,
related Groups table.

Rules and Check constraints don't actually change data - they just validate
it. Use a trigger update dependent columns when data is inserted or updated.
See CREATE TRIGGER in Books Online for details.

--
David Portas
----
Please reply only to the newsgroup
--|||You can do this in a trigger, assuming the primary key value is never
changed. For example:

CREATE TABLE MyTable
(
ID int
CONSTRAINT PK_MyTable PRIMARY KEY,
Name varchar(32),
Surname varchar(32),
GroupID int,
SubGroupOneID int,
SubGroupTwoID int
)
GO

CREATE TRIGGER TR_MyTable
ON MyTable FOR INSERT, UPDATE
AS
UPDATE t
SET
SubGroupOneID = 1,
SubGroupTwoID = 1
FROM MyTable t
JOIN inserted i ON t.ID = i.ID
WHERE i.GroupID = 15
GO

INSERT INTO MyTable (ID, Name, SurName, GroupID) VALUES(1,'name',
'surname', 1)
INSERT INTO MyTable (ID, Name, SurName, GroupID) VALUES(2,'name',
'surname', 2)
INSERT INTO MyTable (ID, Name, SurName, GroupID) VALUES(3,'name',
'surname', 15)
SELECT * FROM MyTable
UPDATE MyTable SET GroupID = 15 WHERE GroupID IN(1, 2)
SELECT * FROM MyTable
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Rustam Bogubaev" <rbogubaev@.bookinturkey.com> wrote in message
news:20046852.0401202332.386aa33c@.posting.google.c om...
> Hi,
> I have a table with the following columns:
> ID INTEGEDR,
> Name VARCHAR(32),
> Surname VARCHAR(32),
> GroupID INTEGER,
> SubGroupOneID INTEGER,
> SubGroupTwoID INTEGER
> How can I create a rule/default/check which update SubGroupOneID &
> SubGroupTwoID columns when GroupID for example is equal 15 on
> MSSQL2000.
> It is imposible to make changes on client, so I need to check
> inserted/updated value of GroupID column and automaticly update
> SubGroupOneID & SubGroupTwoID columns.
> Sincerely,
> Rustam Bogubaev|||Hi dan

This problem was one I am grappling with, and despite its apparent
simplicity, is not touched on in any simple way in SQL books online, or
other 3rd party books, or rarely in Deja archives.

Could I impose on you a little more to elaborate on a couple of points in
this eample please ?

> For example:
> CREATE TABLE MyTable
> (
> ID int
> CONSTRAINT PK_MyTable PRIMARY KEY,
> Name varchar(32),
> Surname varchar(32),
> GroupID int,
> SubGroupOneID int,
> SubGroupTwoID int
> )
> GO

Yup, I can handle that, lets make a table

> CREATE TRIGGER TR_MyTable
> ON MyTable FOR INSERT, UPDATE
> AS
> UPDATE t
> SET
> SubGroupOneID = 1,
> SubGroupTwoID = 1
> FROM MyTable t
> JOIN inserted i ON t.ID = i.ID
> WHERE i.GroupID = 15
> GO

Making a trigger i can handle, but ..
Bits that puzzle me
UPDATE t, can you explain the reason for and the use of 't' ?
Is that a temporary table where data is stored in the process?

the MyTable t bit, assuming t is a table, should that be MyTable, t -
joining two different tables ?

> INSERT INTO MyTable (ID, Name, SurName, GroupID) VALUES(1,'name',
> 'surname', 1)
> INSERT INTO MyTable (ID, Name, SurName, GroupID) VALUES(2,'name',
> 'surname', 2)
> INSERT INTO MyTable (ID, Name, SurName, GroupID) VALUES(3,'name',
> 'surname', 15)
> SELECT * FROM MyTable
> UPDATE MyTable SET GroupID = 15 WHERE GroupID IN(1, 2)
> SELECT * FROM MyTable
> GO
I presume this next bit is an alternative method, where we insert 3 records,
and then do a bulk update to make
the coumn GroupID = 15 when GroupID IN(1, 2) - i dont really understand the
"GroupID IN(1, 2)" logic. I cant even find the IN function using SQL Books
online, as it rarely gives me any usefull results from my inquiries :-)

The other bit that puzzles me is why the "SELECT * FROM MyTable" is needed.
Does the UPDATE row not process all records automatically when the GO is
encountered?

Many thanks for any help you can provide.

> "Rustam Bogubaev" <rbogubaev@.bookinturkey.com> wrote in message
> news:20046852.0401202332.386aa33c@.posting.google.c om...
> > Hi,
> > I have a table with the following columns:
> > ID INTEGEDR,
> > Name VARCHAR(32),
> > Surname VARCHAR(32),
> > GroupID INTEGER,
> > SubGroupOneID INTEGER,
> > SubGroupTwoID INTEGER
> > How can I create a rule/default/check which update SubGroupOneID &
> > SubGroupTwoID columns when GroupID for example is equal 15 on
> > MSSQL2000.
> > It is imposible to make changes on client, so I need to check
> > inserted/updated value of GroupID column and automaticly update
> > SubGroupOneID & SubGroupTwoID columns.
> > Sincerely,
> > Rustam Bogubaev|||> > CREATE TRIGGER TR_MyTable
> > ON MyTable FOR INSERT, UPDATE
> > AS
> > UPDATE t
> > SET
> > SubGroupOneID = 1,
> > SubGroupTwoID = 1
> > FROM MyTable t
> > JOIN inserted i ON t.ID = i.ID
> > WHERE i.GroupID = 15
> > GO
> Making a trigger i can handle, but ..
> Bits that puzzle me
> UPDATE t, can you explain the reason for and the use of 't' ?
> Is that a temporary table where data is stored in the process?
> the MyTable t bit, assuming t is a table, should that be MyTable, t -
> joining two different tables ?

The 't' is simply an alias declared for MyTable so that I didn't need to
specify the full table name when qualifying column names. The following is
functionally identical. Both examples join MyTable with the inserted table
in order to identify newly inserted or updated rows.

CREATE TRIGGER TR_MyTable
ON MyTable FOR INSERT, UPDATE
AS
UPDATE MyTable
SET
SubGroupOneID = 1,
SubGroupTwoID = 1
FROM inserted
WHERE MyTable.ID = inserted.ID AND
inserted.GroupID = 15
GO

> > INSERT INTO MyTable (ID, Name, SurName, GroupID) VALUES(1,'name',
> > 'surname', 1)
> > INSERT INTO MyTable (ID, Name, SurName, GroupID) VALUES(2,'name',
> > 'surname', 2)
> > INSERT INTO MyTable (ID, Name, SurName, GroupID) VALUES(3,'name',
> > 'surname', 15)
> > SELECT * FROM MyTable
> > UPDATE MyTable SET GroupID = 15 WHERE GroupID IN(1, 2)
> > SELECT * FROM MyTable
> > GO
> I presume this next bit is an alternative method, where we insert 3
records,
> and then do a bulk update to make
> the coumn GroupID = 15 when GroupID IN(1, 2) - i dont really understand
the
> "GroupID IN(1, 2)" logic. I cant even find the IN function using SQL Books
> online, as it rarely gives me any usefull results from my inquiries :-)

'GroupID IN(1, 2)' is equivalent to 'GroupID = 1 OR GroupID = 2'. You can
find details of 'IN' in the SQL 2000 Books Online. I was able to find the
BOL topic by clicking the index tab, typing 'IN' and double-clicking on the
'IN' keyword in the list. I then selected the 'IN' title from the topic
list.

> The other bit that puzzles me is why the "SELECT * FROM MyTable" is
needed.

The SELECT statements before and after the UPDATE are to display the data
before and after the UPDATE. These are only for illustration.

> Does the UPDATE row not process all records automatically when the GO is
> encountered?

GO is a batch separator. Tools like Query Analyzer execute the preceding
batch of SQL statements when a GO is encountered in the script. The insert,
update and select statements are executed sequentially as part of the same
batch.

--
Hope this helps.

Dan Guzman
SQL Server MVPsql

how to create row locks

Hello,
We would like to use the following concept :
There are two processes.
First one creates the transacton, makes update on one row via the primary
key
(by the command UPDATE not by UPDATE CURSOR).
We need to achieve
that in the duration this transaction
there is not possible to execute UPDATE for this row by the second process,
but we must have the possibility to make UPDATE on the another rows.
Our problem is
that by using the above construction
the whole table is locked for the second process.
Thanks
misoMiso
By deafult SQL Server creates a lock on row level.
BEGIN TRAN
UPDATE Table SET col1 =@.par1,col2=@.par2 WHERE PK=something
COMMIT TRAN
"Miso" <valent@.spirit.sk> wrote in message
news:eQl5Hw08GHA.2128@.TK2MSFTNGP05.phx.gbl...
> Hello,
> We would like to use the following concept :
> There are two processes.
> First one creates the transacton, makes update on one row via the primary
> key
> (by the command UPDATE not by UPDATE CURSOR).
> We need to achieve
> that in the duration this transaction
> there is not possible to execute UPDATE for this row by the second
> process,
> but we must have the possibility to make UPDATE on the another rows.
> Our problem is
> that by using the above construction
> the whole table is locked for the second process.
>
> Thanks
> miso
>|||Miso,
the update on the single row within the transaction will have an exclusive
lock on that row, but as it's a single row, I don't see how lock escalation
has increased it to a table lock (an intent exclusive is expected). How many
rows are in the table? Are there other transactions operating here? What is
returned if you run sp_lock (for the relevant objid)? Also, what happens if
you use the ROWLOCK hint?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

how to create row locks

Hello,
We would like to use the following concept :
There are two processes.
First one creates the transacton, makes update on one row via the primary
key
(by the command UPDATE not by UPDATE CURSOR).
We need to achieve
that in the duration this transaction
there is not possible to execute UPDATE for this row by the second process,
but we must have the possibility to make UPDATE on the another rows.
Our problem is
that by using the above construction
the whole table is locked for the second process.
Thanks
miso
Miso
By deafult SQL Server creates a lock on row level.
BEGIN TRAN
UPDATE Table SET col1 =@.par1,col2=@.par2 WHERE PK=something
COMMIT TRAN
"Miso" <valent@.spirit.sk> wrote in message
news:eQl5Hw08GHA.2128@.TK2MSFTNGP05.phx.gbl...
> Hello,
> We would like to use the following concept :
> There are two processes.
> First one creates the transacton, makes update on one row via the primary
> key
> (by the command UPDATE not by UPDATE CURSOR).
> We need to achieve
> that in the duration this transaction
> there is not possible to execute UPDATE for this row by the second
> process,
> but we must have the possibility to make UPDATE on the another rows.
> Our problem is
> that by using the above construction
> the whole table is locked for the second process.
>
> Thanks
> miso
>
|||Miso,
the update on the single row within the transaction will have an exclusive
lock on that row, but as it's a single row, I don't see how lock escalation
has increased it to a table lock (an intent exclusive is expected). How many
rows are in the table? Are there other transactions operating here? What is
returned if you run sp_lock (for the relevant objid)? Also, what happens if
you use the ROWLOCK hint?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

how to create row locks

Hello,
We would like to use the following concept :
There are two processes.
First one creates the transacton, makes update on one row via the primary
key
(by the command UPDATE not by UPDATE CURSOR).
We need to achieve
that in the duration this transaction
there is not possible to execute UPDATE for this row by the second process,
but we must have the possibility to make UPDATE on the another rows.
Our problem is
that by using the above construction
the whole table is locked for the second process.
Thanks
misoMiso
By deafult SQL Server creates a lock on row level.
BEGIN TRAN
UPDATE Table SET col1 =@.par1,col2=@.par2 WHERE PK=something
COMMIT TRAN
"Miso" <valent@.spirit.sk> wrote in message
news:eQl5Hw08GHA.2128@.TK2MSFTNGP05.phx.gbl...
> Hello,
> We would like to use the following concept :
> There are two processes.
> First one creates the transacton, makes update on one row via the primary
> key
> (by the command UPDATE not by UPDATE CURSOR).
> We need to achieve
> that in the duration this transaction
> there is not possible to execute UPDATE for this row by the second
> process,
> but we must have the possibility to make UPDATE on the another rows.
> Our problem is
> that by using the above construction
> the whole table is locked for the second process.
>
> Thanks
> miso
>|||Miso,
the update on the single row within the transaction will have an exclusive
lock on that row, but as it's a single row, I don't see how lock escalation
has increased it to a table lock (an intent exclusive is expected). How many
rows are in the table? Are there other transactions operating here? What is
returned if you run sp_lock (for the relevant objid)? Also, what happens if
you use the ROWLOCK hint?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

how to create mail merge with word 2003 and sql server 2000 as database

hi, i have the following command and generated an error :
"word was unable to open the data source"

any help will be greatly appreciated.

Thanks in advance.

Jude

Private Sub Command1_Click()

Dim oMainDoc As Word.Document
Dim oSel As Word.Selection
Dim sDBPath As String

'Start a new main document for the mail merge.
Set oMainDoc = oApp.Documents.Add

With oMainDoc.MailMerge

.MainDocumentType = wdFormLetters

.OpenDataSource Name:="", _
connection:="DSN=JLU;DATABASE=PAMI_DB;UID=;pwd=;", _
sqlstatement:="select * from TBLUSERINFO"
'Add the field codes to the document to create the form letter.
With .Fields
Set oSel = oApp.Selection
.Add oSel.Range, "USERID"
oSel.TypeParagraph
.Add oSel.Range, "LASTNAME"
oSel.TypeParagraph
.Add oSel.Range, "FIRSTNAME"
oSel.TypeText ", "
.Add oSel.Range, "MI"
oSel.TypeParagraph
oSel.TypeParagraph
oSel.TypeText "Dear "
.Add oSel.Range, "FULLNAME"
oSel.TypeText ","
oSel.TypeParagraph
oSel.TypeParagraph
oSel.TypeText " This letter is to inform you..."
oSel.TypeParagraph
oSel.TypeParagraph
oSel.TypeText "Sincerely, [Your Name Here]"
End With
End With

'Perform the mail merge to a new document.
With oMainDoc
.MailMerge.Destination = wdSendToNewDocument
.MailMerge.Execute Pause:=False
End With

End SubMake sure your connection string is valid.|||hi, tried this one but no luck again. it gave no error but my computer does not have any activity.

With oMainDoc.MailMerge

.MainDocumentType = wdFormLetters
.OpenDataSource Name:="", _
connection:="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=pami_db;Data Source=JLU", _
sqlstatement:="select * from TBLUSERINFO", SubType:=wdMergeSubTypeWord2000
With .Fields
Set oSel = oApp.Selection
.Add oSel.Range, "USERID"
oSel.TypeParagraph
.Add oSel.Range, "LASTNAME"
oSel.TypeParagraph
.Add oSel.Range, "FIRSTNAME"
oSel.TypeText ", "
.Add oSel.Range, "MI"
oSel.TypeParagraph
oSel.TypeParagraph
oSel.TypeText "Dear "
.Add oSel.Range, "FULLNAME"
oSel.TypeText ","
oSel.TypeParagraph
oSel.TypeParagraph
oSel.TypeText " This letter is to inform you..."
oSel.TypeParagraph
oSel.TypeParagraph
oSel.TypeText "Sincerely, [Your Name Here]"
End With
End With

With oMainDoc
.MailMerge.Destination = wdSendToNewDocument
.MailMerge.Execute Pause:=False
End With

How to Create an XSD/XML Schema file?

I am trying to load the following XML file into a database table. I
receive the file from an external source exactly as follows:
<list type='full' level='state' val='CA'>
<ac val='714'>
<ph val='2011882' />
<ph val='2013327' />
<ph val='2016543' />
<ph val='2017632' />
<ph val='2017656' />
<ph val='2018788' />
<ph val='2018211' />
<ph val='2019345' />
</ac>
</list>
I have a SQL Server 2000 running with a Table named PHONE that has only
one column:
PHONENUMBER <VARCHAR 10> (Sample Data: 7025556511)
I am new to XML as it works with SQL Server so I have the following
issues:
(1) Because the external file can have millions of PH records, I would
like to load the data into the SQL Server table using OPENXML and the
SQLXMLBulkLoad object (I understand this is a desirable option for
performance).
(2) How should the XSD Mapping Schema file be formatted to
combine/merge the AC and PH values into the PHONE table as one piece of
data?
(3) The bottom line is 'What will the XSD Mapping Schema file look
like'?
Any help would be greatly appreciated.> I am trying to load the following XML file into a database table. I
> receive the file from an external source exactly as follows:
> <list type='full' level='state' val='CA'>
> <ac val='714'>
> <ph val='2011882' />
> <ph val='2013327' />
> <ph val='2016543' />
> <ph val='2017632' />
> <ph val='2017656' />
> <ph val='2018788' />
> <ph val='2018211' />
> <ph val='2019345' />
> </ac>
> </list>
Since I am a developer, I am kind of weak on these XML-related features of
SQL Server. But since no one else has answered your post, I will tell you
what I would try as a developer.
In any of the .NET programming languages, you have this thing called a
DataSet object, which represents a series of DataTable objects. A DataTable
represents a resultset, like you would get from a SELECT statement. A
DataSet can contain any number of DataTables, and can even enforce
referential integrity between them. A DataSet object has a OpenXML or
ReadXML method (I forget which -- I haven't slept in days) where you can
pass the filename of your XML file, and your DataSet will be populated with
the relational structure and data that's represented inside the XML file.
Then you enumerate all the DataTable objects inside the DataSet.Tables
collection. For each DataTable, you build a InsertCommand or UpdateCommand
for a DataAdapter (the thing that issues commands to the data store)
Then you say DataSet.Update() and presto, all your data is in the database.
In theory.
Peace & happy computing,
Mike Labosh, MCSD MCT
Owner, vbSensei.Com
"Escriba coda ergo sum." -- vbSensei|||<dsijta@.netscape.net> wrote in message
news:1140837455.552719.197710@.v46g2000cwv.googlegroups.com...
>I am trying to load the following XML file into a database table. I
> receive the file from an external source exactly as follows:
> <list type='full' level='state' val='CA'>
> <ac val='714'>
> <ph val='2011882' />
> <ph val='2013327' />
> <ph val='2016543' />
> <ph val='2017632' />
> <ph val='2017656' />
> <ph val='2018788' />
> <ph val='2018211' />
> <ph val='2019345' />
> </ac>
> </list>
> I have a SQL Server 2000 running with a Table named PHONE that has only
> one column:
> PHONENUMBER <VARCHAR 10> (Sample Data: 7025556511)
> I am new to XML as it works with SQL Server so I have the following
> issues:
> (1) Because the external file can have millions of PH records, I would
> like to load the data into the SQL Server table using OPENXML and the
> SQLXMLBulkLoad object (I understand this is a desirable option for
> performance).
> (2) How should the XSD Mapping Schema file be formatted to
> combine/merge the AC and PH values into the PHONE table as one piece of
> data?
> (3) The bottom line is 'What will the XSD Mapping Schema file look
> like'?
> Any help would be greatly appreciated.
>
Here's an example of an XSD for the document you posted. Many XML utilities
will generate schemas automatically for you. This one I produced using SQL
Server 2005 Integration Services.
An XSD only validates the format. It doesn't perform any transformations.
<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="list">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="ac">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="ph">
<xs:complexType>
<xs:attribute name="val" type="xs:unsignedInt"
use="optional" />
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="val" type="xs:unsignedShort" use="optional"
/>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="type" type="xs:string" use="optional" />
<xs:attribute name="level" type="xs:string" use="optional" />
<xs:attribute name="val" type="xs:string" use="optional" />
</xs:complexType>
</xs:element>
</xs:schema>
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||You can also this free tool (from MS):
http://apps.gotdotnet.com/xmltools/...e/overview.html
http://www.gotdotnet.com/Community/...0c-f24b91e47228
-oj
<dsijta@.netscape.net> wrote in message
news:1140837455.552719.197710@.v46g2000cwv.googlegroups.com...
>I am trying to load the following XML file into a database table. I
> receive the file from an external source exactly as follows:
> <list type='full' level='state' val='CA'>
> <ac val='714'>
> <ph val='2011882' />
> <ph val='2013327' />
> <ph val='2016543' />
> <ph val='2017632' />
> <ph val='2017656' />
> <ph val='2018788' />
> <ph val='2018211' />
> <ph val='2019345' />
> </ac>
> </list>
> I have a SQL Server 2000 running with a Table named PHONE that has only
> one column:
> PHONENUMBER <VARCHAR 10> (Sample Data: 7025556511)
> I am new to XML as it works with SQL Server so I have the following
> issues:
> (1) Because the external file can have millions of PH records, I would
> like to load the data into the SQL Server table using OPENXML and the
> SQLXMLBulkLoad object (I understand this is a desirable option for
> performance).
> (2) How should the XSD Mapping Schema file be formatted to
> combine/merge the AC and PH values into the PHONE table as one piece of
> data?
> (3) The bottom line is 'What will the XSD Mapping Schema file look
> like'?
> Any help would be greatly appreciated.
>|||> http://apps.gotdotnet.com/xmltools/...e/overview.html
WOAH! COOL!!
--
Peace & happy computing,
Mike Labosh, MCSD MCT
Owner, vbSensei.Com
"Escriba coda ergo sum." -- vbSensei|||Hi -
For an example of a XML schema generator utility, see:
http://www.stylusstudio.com/autogen_xsd.html
It shows how to create an XML schema from an XML instance document.
hope that helps.
Sincerely,
The Stylus Studio Team
http://www.stylusstudio.comsql

How to create an excel connection manager programmatically ?

I am following the samples in the online books. I've got an OLEDB connection manager and source component defined. I now want to create an Excel connection manager and destination component (see below). How can I do this?

Dim conMgr As ConnectionManager = package.Connections.Add("OLEDB")

conMgr.ConnectionString = "Provider=SQLOLEDB.1;" "Data Source=FLL-EIS;Initial Catalog=DataLoad;" & _

"Integrated Security=SSPI;"

conMgr.Name = "SSIS Connection Manager for OLE DB"

conMgr.Description = "OLE DB connection to FLL-EIS."

' Create and configure an OLE DB source component.

Dim source As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New

source.ComponentClassID = "DTSAdapter.OleDbSource"

' Create the design-time instance of the source.

Dim srcDesignTime As CManagedComponentWrapper = source.Instantiate

srcDesignTime.ProvideComponentProperties()

' Assign the connection manager.

source.RuntimeConnectionCollection(0).ConnectionManager = _

DtsConvert.ToConnectionManager90(conMgr)

' Set the custom properties of the source.

srcDesignTime.SetComponentProperty("AccessMode", 2)

srcDesignTime.SetComponentProperty("SqlCommand", Dts.Variables("User::gsQuery").Value.ToString)

' Connect to the data source,

' and then update the metadata for the source.

srcDesignTime.AcquireConnections(Nothing)

srcDesignTime.ReinitializeMetaData()

srcDesignTime.ReleaseConnections()

' Create and configure an OLE DB destination. (This is where I need help, as this code does not work)

Dim destination As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New

destination.ComponentClassID = "DTSAdapter.ExcelDestination"

' Create the design-time instance of the destination.

Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate

' Where can I find documentation about the various ComponentClassIds and Properties?

destDesignTime.ProvideComponentProperties()

destDesignTime.SetComponentProperty("ExcelFilePath", Dts.Variables("User::gsExcelFile").Value.ToString)

destDesignTime.SetComponentProperty("TableName", Dts.Variables("User::gsSheetName").Value.ToString)

Here are a couple of wrapper functions I have for creating connections, including an Excel connection-

Code Snippet

private static ConnectionManager AddExcelConnection(Package package, string filename)

{

return AddConnection(package, "EXCEL", String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES\"", filename));

}

private static ConnectionManager AddSqlConnection(Package package, string server, string database)

{

return AddConnection(package, "OLEDB", String.Format("Provider=SQLOLEDB.1;Data Source={0};Persist Security Info=False;Initial Catalog={1};Integrated Security=SSPI;", server, database));

}

private static ConnectionManager AddConnection(Package package, string type, string connectionString)

{

ConnectionManager manager = package.Connections.Add(type);

manager.ConnectionString = connectionString;

manager.Name = String.Format("{0} Connection", type);

return manager;

}

Call it like this -

Code Snippet

Package package = new Package();

// Add the Excel connection

ConnectionManager excelConnection = AddExcelConnection(package, @."C:\Temp\Export.xls");

Is your destination code correct? I the properties look wrong, I always used -

componentInstance.SetComponentProperty("AccessMode", 2);

componentInstance.SetComponentProperty("SqlCommand", query);

|||

Thanks for the info, Darren. So, now I have the following code, but how do I specify the destination table (sheet) name?

Dim conDest As ConnectionManager = package.Connections.Add("Excel")

conDest.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _

Dts.Variables("User::gsExcelFile").Value.ToString & ";Extended Properties=""Excel 8.0;HDR=YES"""

|||

Code Snippet

componentInstance.SetComponentProperty("AccessMode", 0);

componentInstance.SetComponentProperty("OpenRowset", sheetName);

I was wrong above, it is OpenRowset for teh table/sheet not SqlCommand.

|||

Thanks for the reply, Darren. The first SetComponentProperty worked, but not the second, which gets a runtime exception. Is the property under a different name? Thanks for your help!

' Create and configure an OLE DB destination.

Dim conDest As ConnectionManager = package.Connections.Add("Excel")

conDest.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _

Dts.Variables("User::gsExcelFile").Value.ToString & ";Extended Properties=""Excel 8.0;HDR=YES"""

conDest.Name = "Excel File"

conDest.Description = "Excel File"

Dim destination As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New

destination.ComponentClassID = "DTSAdapter.ExcelDestination"

' Create the design-time instance of the destination.

Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate

' The ProvideComponentProperties method creates a default input.

destDesignTime.ProvideComponentProperties()

destination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(conDest)

destDesignTime.SetComponentProperty("AccessMode", 0)

'runtime Exception here

destDesignTime.SetComponentProperty("OpenRowSet", "functions")

|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2175638&SiteID=1

How to create an excel connection manager programmatically ?

I am following the samples in the online books. I've got an OLEDB connection manager and source component defined. I now want to create an Excel connection manager and destination component (see below). How can I do this?

Dim conMgr As ConnectionManager = package.Connections.Add("OLEDB")

conMgr.ConnectionString = "Provider=SQLOLEDB.1;" "Data Source=FLL-EIS;Initial Catalog=DataLoad;" & _

"Integrated Security=SSPI;"

conMgr.Name = "SSIS Connection Manager for OLE DB"

conMgr.Description = "OLE DB connection to FLL-EIS."

' Create and configure an OLE DB source component.

Dim source As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New

source.ComponentClassID = "DTSAdapter.OleDbSource"

' Create the design-time instance of the source.

Dim srcDesignTime As CManagedComponentWrapper = source.Instantiate

srcDesignTime.ProvideComponentProperties()

' Assign the connection manager.

source.RuntimeConnectionCollection(0).ConnectionManager = _

DtsConvert.ToConnectionManager90(conMgr)

' Set the custom properties of the source.

srcDesignTime.SetComponentProperty("AccessMode", 2)

srcDesignTime.SetComponentProperty("SqlCommand", Dts.Variables("User::gsQuery").Value.ToString)

' Connect to the data source,

' and then update the metadata for the source.

srcDesignTime.AcquireConnections(Nothing)

srcDesignTime.ReinitializeMetaData()

srcDesignTime.ReleaseConnections()

' Create and configure an OLE DB destination. (This is where I need help, as this code does not work)

Dim destination As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New

destination.ComponentClassID = "DTSAdapter.ExcelDestination"

' Create the design-time instance of the destination.

Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate

' Where can I find documentation about the various ComponentClassIds and Properties?

destDesignTime.ProvideComponentProperties()

destDesignTime.SetComponentProperty("ExcelFilePath", Dts.Variables("User::gsExcelFile").Value.ToString)

destDesignTime.SetComponentProperty("TableName", Dts.Variables("User::gsSheetName").Value.ToString)

Here are a couple of wrapper functions I have for creating connections, including an Excel connection-

Code Snippet

private static ConnectionManager AddExcelConnection(Package package, string filename)

{

return AddConnection(package, "EXCEL", String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES\"", filename));

}

private static ConnectionManager AddSqlConnection(Package package, string server, string database)

{

return AddConnection(package, "OLEDB", String.Format("Provider=SQLOLEDB.1;Data Source={0};Persist Security Info=False;Initial Catalog={1};Integrated Security=SSPI;", server, database));

}

private static ConnectionManager AddConnection(Package package, string type, string connectionString)

{

ConnectionManager manager = package.Connections.Add(type);

manager.ConnectionString = connectionString;

manager.Name = String.Format("{0} Connection", type);

return manager;

}

Call it like this -

Code Snippet

Package package = new Package();

// Add the Excel connection

ConnectionManager excelConnection = AddExcelConnection(package, @."C:\Temp\Export.xls");

Is your destination code correct? I the properties look wrong, I always used -

componentInstance.SetComponentProperty("AccessMode", 2);

componentInstance.SetComponentProperty("SqlCommand", query);

|||

Thanks for the info, Darren. So, now I have the following code, but how do I specify the destination table (sheet) name?

Dim conDest As ConnectionManager = package.Connections.Add("Excel")

conDest.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _

Dts.Variables("User::gsExcelFile").Value.ToString & ";Extended Properties=""Excel 8.0;HDR=YES"""

|||

Code Snippet

componentInstance.SetComponentProperty("AccessMode", 0);

componentInstance.SetComponentProperty("OpenRowset", sheetName);

I was wrong above, it is OpenRowset for teh table/sheet not SqlCommand.

|||

Thanks for the reply, Darren. The first SetComponentProperty worked, but not the second, which gets a runtime exception. Is the property under a different name? Thanks for your help!

' Create and configure an OLE DB destination.

Dim conDest As ConnectionManager = package.Connections.Add("Excel")

conDest.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _

Dts.Variables("User::gsExcelFile").Value.ToString & ";Extended Properties=""Excel 8.0;HDR=YES"""

conDest.Name = "Excel File"

conDest.Description = "Excel File"

Dim destination As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New

destination.ComponentClassID = "DTSAdapter.ExcelDestination"

' Create the design-time instance of the destination.

Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate

' The ProvideComponentProperties method creates a default input.

destDesignTime.ProvideComponentProperties()

destination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(conDest)

destDesignTime.SetComponentProperty("AccessMode", 0)

'runtime Exception here

destDesignTime.SetComponentProperty("OpenRowSet", "functions")

|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2175638&SiteID=1

How to create an excel connection manager programmatically ?

I am following the samples in the online books. I've got an OLEDB connection manager and source component defined. I now want to create an Excel connection manager and destination component (see below). How can I do this?

Dim conMgr As ConnectionManager = package.Connections.Add("OLEDB")

conMgr.ConnectionString = "Provider=SQLOLEDB.1;" "Data Source=FLL-EIS;Initial Catalog=DataLoad;" & _

"Integrated Security=SSPI;"

conMgr.Name = "SSIS Connection Manager for OLE DB"

conMgr.Description = "OLE DB connection to FLL-EIS."

' Create and configure an OLE DB source component.

Dim source As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New

source.ComponentClassID = "DTSAdapter.OleDbSource"

' Create the design-time instance of the source.

Dim srcDesignTime As CManagedComponentWrapper = source.Instantiate

srcDesignTime.ProvideComponentProperties()

' Assign the connection manager.

source.RuntimeConnectionCollection(0).ConnectionManager = _

DtsConvert.ToConnectionManager90(conMgr)

' Set the custom properties of the source.

srcDesignTime.SetComponentProperty("AccessMode", 2)

srcDesignTime.SetComponentProperty("SqlCommand", Dts.Variables("User::gsQuery").Value.ToString)

' Connect to the data source,

' and then update the metadata for the source.

srcDesignTime.AcquireConnections(Nothing)

srcDesignTime.ReinitializeMetaData()

srcDesignTime.ReleaseConnections()

' Create and configure an OLE DB destination. (This is where I need help, as this code does not work)

Dim destination As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New

destination.ComponentClassID = "DTSAdapter.ExcelDestination"

' Create the design-time instance of the destination.

Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate

' Where can I find documentation about the various ComponentClassIds and Properties?

destDesignTime.ProvideComponentProperties()

destDesignTime.SetComponentProperty("ExcelFilePath", Dts.Variables("User::gsExcelFile").Value.ToString)

destDesignTime.SetComponentProperty("TableName", Dts.Variables("User::gsSheetName").Value.ToString)

Here are a couple of wrapper functions I have for creating connections, including an Excel connection-

Code Snippet

private static ConnectionManager AddExcelConnection(Package package, string filename)

{

return AddConnection(package, "EXCEL", String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES\"", filename));

}

private static ConnectionManager AddSqlConnection(Package package, string server, string database)

{

return AddConnection(package, "OLEDB", String.Format("Provider=SQLOLEDB.1;Data Source={0};Persist Security Info=False;Initial Catalog={1};Integrated Security=SSPI;", server, database));

}

private static ConnectionManager AddConnection(Package package, string type, string connectionString)

{

ConnectionManager manager = package.Connections.Add(type);

manager.ConnectionString = connectionString;

manager.Name = String.Format("{0} Connection", type);

return manager;

}

Call it like this -

Code Snippet

Package package = new Package();

// Add the Excel connection

ConnectionManager excelConnection = AddExcelConnection(package, @."C:\Temp\Export.xls");

Is your destination code correct? I the properties look wrong, I always used -

componentInstance.SetComponentProperty("AccessMode", 2);

componentInstance.SetComponentProperty("SqlCommand", query);

|||

Thanks for the info, Darren. So, now I have the following code, but how do I specify the destination table (sheet) name?

Dim conDest As ConnectionManager = package.Connections.Add("Excel")

conDest.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _

Dts.Variables("User::gsExcelFile").Value.ToString & ";Extended Properties=""Excel 8.0;HDR=YES"""

|||

Code Snippet

componentInstance.SetComponentProperty("AccessMode", 0);

componentInstance.SetComponentProperty("OpenRowset", sheetName);

I was wrong above, it is OpenRowset for teh table/sheet not SqlCommand.

|||

Thanks for the reply, Darren. The first SetComponentProperty worked, but not the second, which gets a runtime exception. Is the property under a different name? Thanks for your help!

' Create and configure an OLE DB destination.

Dim conDest As ConnectionManager = package.Connections.Add("Excel")

conDest.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _

Dts.Variables("User::gsExcelFile").Value.ToString & ";Extended Properties=""Excel 8.0;HDR=YES"""

conDest.Name = "Excel File"

conDest.Description = "Excel File"

Dim destination As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New

destination.ComponentClassID = "DTSAdapter.ExcelDestination"

' Create the design-time instance of the destination.

Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate

' The ProvideComponentProperties method creates a default input.

destDesignTime.ProvideComponentProperties()

destination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(conDest)

destDesignTime.SetComponentProperty("AccessMode", 0)

'runtime Exception here

destDesignTime.SetComponentProperty("OpenRowSet", "functions")

|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2175638&SiteID=1sql

How to create a table (structure) based on a result of a stored procedure

following statement will not work ..SELECT * INTO #xxx FROM (EXECUTE
sp_storedprocedure)
instead of select you can use INSERT INTO will work
eg:
create table #t(i int)
insert into #t exec myproc
create proc myproc
as
select 1
vinu
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
> Dear all,
> There is a stored procedure that returns a result set consisting of some
> 30
> columns of different types.
> How can I automatically (i.e. not by means of CREATE TABLE statement)
> create
> a table with a structure that correspons to the returned result set?
> I mean something like:
> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
> (The above unfortunately doesn't work).
> To be specific: the stored procedure in question is:
> sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
> Any help would be greatly appreciated!
> Thank you in advance.
> Best regards,
> Andrew
>
Well .. it is not possible to use select into...exec
vt
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:OXdJJayAHHA.4328@.TK2MSFTNGP03.phx.gbl...
> As I wrote, the idea is to create the table automatically, i.e. NOT to use
> the CREATE TABLE statement.
> Best regards,
> Andrew
>
> "vt" <vinu.t.1976@.gmail.com> wrote in message
> news:%23GSuhRyAHHA.4672@.TK2MSFTNGP02.phx.gbl...
>
sql

How to create a MAX Named Set for a date dimension?

I have an OLAP cube and I need to create a Named Set to return a latest date from a date dimension. For example I have the following dimension:

[Account Period].[Account Period].[Prescription Date].[Prescription Date].12-July-2006

Based on this dimension structure, how to create a named set only to return the latest date in any grouping of the other dimensions. Or to create a named set is not the correct approach to this?

You can simply use the Tail function unless your date dimension contains dates into the future for forecasting or other measures:

Tail([Account Period].[Account Period].[Prescription Date].Members,1)

If you have dates into the future you can use a measure group to filter the dates so that only dates with data for that measure group are considered:

Tail(Exists([Account Period].[Account Period].[Prescription Date].Members,,"Sales"),1)

HTH,

Steve

|||

Thank you for your reply. I guess that it is best to put down what I really want to achieve here and for the expert to give me the best advice. I am designing a report for a pharmist on a OLAP cube. the OLAP cube consists of the following dimensions:

Measure: Count of the logical primary key (treatment count)

Dimension: Patient_ID.Patient_ID.Patient_ID

Dimension: Account_Period.Prescription_Date.Prescription_Date

Dimension: Drug.Drug_Name.Drug_Name

Dimension: Hospital.Hospital.Hospital

Dimension: Drug.Drug_Ingriedient..Drug_Ingriedient

I need an MDX query for the report so that for a selected Drug_Name (first report single parameter) and the selected Drug_Ingriedient (second report multiple parameters) the "latest" Prescription_Date of the selected Drug_Name, the "earliest" Prescription_Date of the selected Drug_Name, the duration of the selected Drug_Name given to this Patient_ID, the Drug_Ingridient at the "latest" Prescription_Date (Drug_Ingridient is one to one relationship to the Drug_Name, different Drug_Ingridient can occour on the same Drug_Name but not on the same Prescription_Date), the hospital where the Drug_Name prescripted to the Patient_ID at the "latest" Prescription_Date (Patient can be prescripted the selected Drug_Name from different hospital over the period of several years) and the total count of the Drug_Name treatment.

The dataset should consists of eight columns, Patient_ID, Latest_Date, Earliest_Date, Duration, Drug_Name, Drug_Ingridient, Treatment_Count, Hospital.

Thanks.

|||

This will be tough to do via MDX on this discussion board. There are several questions that I need to ask.

1.) What is the formula for "Duration"?

2.) Are the "Earliest" and "Latest" dates for all prescriptions given to the patient?

My email is stevepon@.microsoft.com if you want to send me information off-line. A copy of your AS project files would be a big help.

Steve

How to create a constraint like this:

Hi,

Suppose the following table definition in Sql Server 2005,

create table CompanySymbol
CompanyId int,
SymbolId int,
IsPrimarySymbol bit

Primary Key (CompanyId, SymbolId)

How can I create a constraint which wil ensure that IsPrimarySymbol will be set to 1(true) only once per CompanyId while allowing it to be set to 0 an unlimited amount of time per CompanyId. i.e.:

CompanyId SymbolId IsPrimarySymbol
--
1 1 1 (IsPrimarySymbol to 1 for CompanyId 1)
1 2 0
1 3 0
2 1 1 (IsPrimarySymbol to 1 for CompanyId 2)
2 2 0
3 1 0
4 1 1 (IsPrimarySymbol to 1 for CompanyId 4)
4 2 0
4 3 0

Thanks

Your best bet is to use a trigger to enforce this rule. It can be coded to either reject any Insert/Update that leaves the table with two rows flagged as IsPrimarySymbol. Alternatively, it could be coded to zero out all but the latest record set to IsPrimarySymbol. Be sure to consider Inserts and Updates of more than one record at a time.

It can be accomplished with a Constraint, but you need to also add a computed column.

create table CompanySymbol
CompanyId int,
SymbolId int,
IsPrimarySymbol bit
Guard as Case when IsPrimarySymbol = 1 Then 0 else SymbolId End

Now you can create a Unique Constraint( CompanyId, Guard )

My vote is for the trigger.

|||

Thanks for the answer, this should do the trick.

How to create a CLR integrated trigger on table in schema?

In CLR integrated trigger:

If I want to make a trigger:
- For Insert
- With name: NewEmployeeInserted
- On table dbo.Employees

I add the following attribute above the desired .net method logic:
[SqlTrigger(Event = "For Insert", Name = "NewEmployeeInserted", Target = "Employees")]

How to make a trigger on for example: Production.Employees table?
where Production is the schema where this table resides.

Thank you.

Hi,

this was discussed in an earlier thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=132062&SiteID=1

Normally you should be able to only prefix the Schema before the table target.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

How to count the records in a datatable

I have the following:

Dim ResourceAdapter As New ResourcesTableAdapters.ResourcesTableAdapter
Dim dF As Data.DataTable = ResourceAdapter.GetDataByDistinct
Dim numRows = dF.Rows.Count
Dim dS As Data.DataTable = ResourceAdapter.CountQuery
Dim sumRows = dS.Rows.Count
DataList1.DataSource = dF
DataList1.DataBind()
LblCount.Text = numRows
LblSum.Text = sumRows

numRows is the number of records in a particular category. CountQuery is a method that returns the total number of records in all the categories. I don't know how to get the result of that query, the code line in bold isn't right. How do I get this number?

Diane

ds.Rows[0][0].ToString()

If you are getting a single count value, you can use the above statement. Make sure you validate nulls, else an exception will be raised

Thanks

|||

Thank you.

Dim ResourceAdapter As New ResourcesTableAdapters.ResourcesTableAdapter
Dim dF As Data.DataTable = ResourceAdapter.GetDataByDistinct
Dim numRows = dF.Rows.Count
Dim dS As Data.DataTable = ResourceAdapter.CountQuery
Dim sumRows = ds.Rows[0][0].ToString()

But before I can assign the count to sumRows, don't I have to fix the line above it (in bold)? That line gives me the compiler error

Value of type 'System.Nullable(Of Integer)' cannot be converted to 'System.Data.DataTable'.

Diane

|||

Are you sure ResourceAdapter.CountQuery returns a datatable? If so check if you have any rows available or not first

If (ds.Rows.Count > 0)
Dim sumRows = ds.Rows(0)(0).ToString() /// i am c# code, not sure of how you refer a two dimensional arrary
End If

Thanks

|||

I'm not sure what it returns. The query is one that's designed to return only one row, the number of records in the table. I'm not sure how to get that.

Diane

|||

Looking at the error you are getting (ie: Value of type 'System.Nullable(Of Integer)' cannot be converted to 'System.Data.DataTable'. )

I would say you need to have

Dim sumRows as Int

sumRows = ResourceAdapter.CountQuery

How to count number of Groups in MS-SQL ?

The following SQL works on Access and Oracle to return the number ofGroups(Rows) of the SQL. In MS-SQL this SQL is not valid. What is theequivalent in MS-SQL?
Select Count(1) FROM (Select ShipRegion, Sum(FREIGHT) as [TotalFreight]from ORDERS WHERE OrderID < 123456 GROUP BY ShipRegion )
Thanks,

Frankk
Hello,
this seems to work:
> Select Count(1) FROM (Select ShipRegion, Sum(FREIGHT) as [TotalFreight] from ORDERS WHERE OrderID < 123456 GROUP BY ShipRegion) AS X

Hope this helps. -LV
|||LV,
Thanks, it works !!!!!

Frankk

How to count no of items in nested table

I have the following table

Region Table

ID

ParentID

RegionName

RestaurantTable

ID

RestaurantName

RegionID

What i tried to do is count the number of restaurants by specific regionname. My current query is

Select RegionID, RegionName, count(*) as RestaurantNo

From Region Inner Join Restaurant

On Region.ID = Restaurant.RegionID

However I only get the results below

RegionID RegionName RestaurantNO

1 A1 0

2 A1.1 2

3 A1.2 1

4 A1.3 0

Where A1.1 , A1.2, and A1.3 are children of A1 in Region table

The result is not correct due to A1 should have 3 in RestaurantNo due to it contains A1.1 , A1.2 and A1.3

Could anyone help me to solve this problem.

Thank you

You need to account for the hierarchy in your join. If you only have 2-deep regions then a simple OR base on ParentID will take care of the problem; however, if your nesting can go more than 2 deep you ought to use a CTE to account for all ancestor possibilities.

The simple alternative would append the OR condtion to your ON condition; something like:

Code Snippet

ON Region.id = Restaurant.RegionId
OR Region.ParentID = Restaurant.RegionId

If you need to take the CTE route, do a search of this forum for HIERARCHY to get some ideas.|||

I need to do on nested query and I tried to search for CTE but I couldn't get the job done, It's always return the incorrect results

WITH RegionRestaurantCount (RegionID, RegionName,ParentID, NumberOfRestaurants) AS
(
SELECT
RegionID,
RegionName,

ParentID,
(SELECT COUNT(*) FROM Region node
WHERE node.RegionID = parent.RegionID) as NumberOfRestaurants
FROM Region parent
)

SELECT rc.RegionName, rc.NumberOfRestaurants
FROM Restaurant res
INNER JOIN RegionRestaurantCount rc ON
res.RegionID = rc.RegionID
ORDER BY RegionName

Could you please help? Thank you|||

Try creating a function that returns a table including each row with its children, including itself. Then use that table to join to [restaurant] and do the aggregation.

Code Snippet

use tempdb

go

create table dbo.region (

ID int not null primary key,

ParentID int null references dbo.region(ID),

RegionName varchar(25) not null

)

go

create table dbo.restaurant (

ID int not null primary key,

RestaurantName varchar(25) not null,

RegionID int not null references dbo.region(ID)

)

go

insert into dbo.region values(1, null, 'A1')

insert into dbo.region values(2, 1, 'A1.1')

insert into dbo.region values(3, 1, 'A1.2')

insert into dbo.region values(4, 1, 'A1.3')

go

insert into dbo.restaurant values(1, 'r1', 2)

insert into dbo.restaurant values(2, 'r2', 2)

insert into dbo.restaurant values(3, 'r3', 3)

go

create function dbo.ufn_children (

@.ID int

)

returns table

as

return (

with cte

as

(

select ID, ParentID

from dbo.region

where ID = @.ID

union all

select

c.ID, c.ParentID

from

cte as p inner join dbo.region as c

on c.ParentID = p.ID

)

select *

from cte

)

go

;with bridge

as

(

select

a.ID as pID,

b.ID as cID

from

dbo.region as a

cross apply

dbo.ufn_children(a.ID) as b

)

select

c.pID,

count(*) as cnt

from

bridge as c

inner join

dbo.restaurant as d

on d.RegionID = c.cID

group by

c.pID

order by

c.pID

option (maxrecursion 0)

go

drop function dbo.ufn_children

go

drop table dbo.restaurant, dbo.region

go

AMB|||

Thank you for your solution however could you explain more details about the logic behind the function do, i am a little bit lost,also how can i translate it into stored procedure that I will use to retrieve the no_of_restaurants based on the regionID.

|||

1 - The function uses a recursive CTE to pull an [ID] and all its children, including itself. If you use that function to pull all childrens for every row in table [region], you will get something like this:

Example:

1 - 1

1 - 2

1 - 3

1 - 4

2 - 2

3 - 3

4 - 4

2 - If you join previous result to the table [restaurant], you will get:

1 - 2 - 1

1 - 2 - 2

1 - 3 - 3

2 - 2 - 1

2 - 2 - 2

3 - 3 - 3

so, if you group by first [ID] then you will get

1 - 3 rest

2 - 2 rest

3 - 1 rest

3 - Here is the sp

Code Snippet

create procedure dbo.usp_p1

@.ID int,

@.cnt int output

as

set nocount on

;with bridge

as

(

select

a.ID as pID,

b.ID as cID

from

dbo.region as a

cross apply

dbo.ufn_children(a.ID) as b

where

a.[ID] = @.ID

)

select

@.cnt = count(*)

from

bridge as c

inner join

dbo.restaurant as d

on d.RegionID = c.cID

group by c.pID

option (maxrecursion 0)

go

declare @.cnt int

exec dbo.usp_p1 1, @.cnt output

print @.cnt

go

AMB

How to count a very large volume of request

Hello,
What is the best way to accomplish the following.
1) An ASP.NET program (consiting of one file somepage.aspx) receives about
25,000,000 requests pay day.
2) The requests come from a limited set of IP addresses.
3) How to count the total number of request from each IP address per day.
SQL Server 2000 is used on the backend.
Currently we used the following architecture:
* Each request to somepage.aspx generates an INSERT into a LogTable1
* There is a clustered index on the LogTable1 on the dateStamp field which
is of the type smalldatetime
* The dateStamp field has a default that sets it to the getdate()
* At the end of each day at 12:01AM there is a simple query that runs and
does a group by to count the number of requests from each IP in the given
date range (past 24 hours). This query works great and takes only 2 minutes
to run.
Is there a better way to accomplish this without having to do INSERTS into
the LogTable1 for each request?
It would not work to have the ASP.NET program execute an UPDATE each time to
increment the total number of request, since this would cause LOTSSS of
locking in the database layer.
Thanks in advance.
ArsenYou could store the data in your Application Cache, and update it
periodically to the database.
HTH,
Kevin Spencer
.Net Developer
Microsoft MVP
Big things are made up
of lots of little things.
"Arsen V." <arsen.NoSpamPlease@.emergency24.com> wrote in message
news:emdbHKo6DHA.2412@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Hello,
> What is the best way to accomplish the following.
> 1) An ASP.NET program (consiting of one file somepage.aspx) receives about
> 25,000,000 requests pay day.
> 2) The requests come from a limited set of IP addresses.
> 3) How to count the total number of request from each IP address per day.
> SQL Server 2000 is used on the backend.
> Currently we used the following architecture:
> * Each request to somepage.aspx generates an INSERT into a LogTable1
> * There is a clustered index on the LogTable1 on the dateStamp field which
> is of the type smalldatetime
> * The dateStamp field has a default that sets it to the getdate()
> * At the end of each day at 12:01AM there is a simple query that runs and
> does a group by to count the number of requests from each IP in the given
> date range (past 24 hours). This query works great and takes only 2

---

minutes

quote:

---

> to run.
> Is there a better way to accomplish this without having to do INSERTS into
> the LogTable1 for each request?
> It would not work to have the ASP.NET program execute an UPDATE each time

---

to

quote:

---

> increment the total number of request, since this would cause LOTSSS of
> locking in the database layer.
> Thanks in advance.
> Arsen
>

---

|||Hi,
What about using the IIS log file?
you could set it in the way you need it, basically the client IP and maybe
the time, if you configure it to update daily all you have to do at 12:01
is run a process that read the file generated and do what you need.
Cheers,
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation
"Arsen V." <arsen.NoSpamPlease@.emergency24.com> wrote in message
news:emdbHKo6DHA.2412@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Hello,
> What is the best way to accomplish the following.
> 1) An ASP.NET program (consiting of one file somepage.aspx) receives about
> 25,000,000 requests pay day.
> 2) The requests come from a limited set of IP addresses.
> 3) How to count the total number of request from each IP address per day.
> SQL Server 2000 is used on the backend.
> Currently we used the following architecture:
> * Each request to somepage.aspx generates an INSERT into a LogTable1
> * There is a clustered index on the LogTable1 on the dateStamp field which
> is of the type smalldatetime
> * The dateStamp field has a default that sets it to the getdate()
> * At the end of each day at 12:01AM there is a simple query that runs and
> does a group by to count the number of requests from each IP in the given
> date range (past 24 hours). This query works great and takes only 2

---

minutes

quote:

---

> to run.
> Is there a better way to accomplish this without having to do INSERTS into
> the LogTable1 for each request?
> It would not work to have the ASP.NET program execute an UPDATE each time

---

to

quote:

---

> increment the total number of request, since this would cause LOTSSS of
> locking in the database layer.
> Thanks in advance.
> Arsen
>

---

|||Hi Kevin,
Do you suggest storing the TOTALs in the Cache?
Or storing the actual requests: date and ip
Would there be a locking problem?
How to do the "periodic updates" to the database from the Cache?
Thanks,
Arsen
"Kevin Spencer" <kevin@.takempis.com> wrote in message
news:ezSYvso6DHA.1936@.TK2MSFTNGP12.phx.gbl...

quote:

---

> You could store the data in your Application Cache, and update it
> periodically to the database.
> --
> HTH,
> Kevin Spencer
> .Net Developer
> Microsoft MVP
> Big things are made up
> of lots of little things.
> "Arsen V." <arsen.NoSpamPlease@.emergency24.com> wrote in message
> news:emdbHKo6DHA.2412@.TK2MSFTNGP09.phx.gbl...
about[QUOTE]
day.[QUOTE]
which[QUOTE]
and[QUOTE]
given[QUOTE]
> minutes
into[QUOTE]
time[QUOTE]
> to
>

---

|||Hi Arsen,
You could put a DataTable in the Application Cache, and add records to it
with each Request. As for periodic updating, you could put a routine in the
Session_OnStart Sub that checks an Application DateTime variable, and at
certain intervals, inserts all the records from the DataTable into the
database and clears out the DataTable.
To be safe, you would want to add code to your Application_OnEnd sub to
update the database if the Application stops or times out; however, with 25M
requests per day, that might not be necessary.
HTH,
Kevin Spencer
.Net Developer
Microsoft MVP
Big things are made up
of lots of little things.
"Arsen V." <arsen.NoSpamPlease@.emergency24.com> wrote in message
news:#daQL6o6DHA.2908@.tk2msftngp13.phx.gbl...

quote:

---

> Hi Kevin,
> Do you suggest storing the TOTALs in the Cache?
> Or storing the actual requests: date and ip
> Would there be a locking problem?
> How to do the "periodic updates" to the database from the Cache?
> Thanks,
> Arsen
> "Kevin Spencer" <kevin@.takempis.com> wrote in message
> news:ezSYvso6DHA.1936@.TK2MSFTNGP12.phx.gbl...
> about
> day.
> which
> and
> given
> into
> time
of[QUOTE]
>

---

|||what i have done is cache the last 15 minutes. the cache has the ipaddress,
the start of the 15 min interval, pagename, and number of hits during the
interval. as most users cluster their hits, this cuts down the number of
inserts.
i flush the cache every 15 minutes of when too large.
you can then get daily or hourly stats from the db with simple queries.
-- bruce (sqlwork.com)
"Arsen V." <arsen.NoSpamPlease@.emergency24.com> wrote in message
news:emdbHKo6DHA.2412@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Hello,
> What is the best way to accomplish the following.
> 1) An ASP.NET program (consiting of one file somepage.aspx) receives about
> 25,000,000 requests pay day.
> 2) The requests come from a limited set of IP addresses.
> 3) How to count the total number of request from each IP address per day.
> SQL Server 2000 is used on the backend.
> Currently we used the following architecture:
> * Each request to somepage.aspx generates an INSERT into a LogTable1
> * There is a clustered index on the LogTable1 on the dateStamp field which
> is of the type smalldatetime
> * The dateStamp field has a default that sets it to the getdate()
> * At the end of each day at 12:01AM there is a simple query that runs and
> does a group by to count the number of requests from each IP in the given
> date range (past 24 hours). This query works great and takes only 2

---

minutes

quote:

---

> to run.
> Is there a better way to accomplish this without having to do INSERTS into
> the LogTable1 for each request?
> It would not work to have the ASP.NET program execute an UPDATE each time

---

to

quote:

---

> increment the total number of request, since this would cause LOTSSS of
> locking in the database layer.
> Thanks in advance.
> Arsen
>

---

How to count a very large volume of request

Hello,
What is the best way to accomplish the following.
1) An ASP.NET program (consiting of one file somepage.aspx) receives about
25,000,000 requests pay day.
2) The requests come from a limited set of IP addresses.
3) How to count the total number of request from each IP address per day.
SQL Server 2000 is used on the backend.
Currently we used the following architecture:
* Each request to somepage.aspx generates an INSERT into a LogTable1
* There is a clustered index on the LogTable1 on the dateStamp field which
is of the type smalldatetime
* The dateStamp field has a default that sets it to the getdate()
* At the end of each day at 12:01AM there is a simple query that runs and
does a group by to count the number of requests from each IP in the given
date range (past 24 hours). This query works great and takes only 2 minutes
to run.
Is there a better way to accomplish this without having to do INSERTS into
the LogTable1 for each request?
It would not work to have the ASP.NET program execute an UPDATE each time to
increment the total number of request, since this would cause LOTSSS of
locking in the database layer.
Thanks in advance.
ArsenYou could store the data in your Application Cache, and update it
periodically to the database.
--
HTH,
Kevin Spencer
.Net Developer
Microsoft MVP
Big things are made up
of lots of little things.
"Arsen V." <arsen.NoSpamPlease@.emergency24.com> wrote in message
news:emdbHKo6DHA.2412@.TK2MSFTNGP09.phx.gbl...
> Hello,
> What is the best way to accomplish the following.
> 1) An ASP.NET program (consiting of one file somepage.aspx) receives about
> 25,000,000 requests pay day.
> 2) The requests come from a limited set of IP addresses.
> 3) How to count the total number of request from each IP address per day.
> SQL Server 2000 is used on the backend.
> Currently we used the following architecture:
> * Each request to somepage.aspx generates an INSERT into a LogTable1
> * There is a clustered index on the LogTable1 on the dateStamp field which
> is of the type smalldatetime
> * The dateStamp field has a default that sets it to the getdate()
> * At the end of each day at 12:01AM there is a simple query that runs and
> does a group by to count the number of requests from each IP in the given
> date range (past 24 hours). This query works great and takes only 2
minutes
> to run.
> Is there a better way to accomplish this without having to do INSERTS into
> the LogTable1 for each request?
> It would not work to have the ASP.NET program execute an UPDATE each time
to
> increment the total number of request, since this would cause LOTSSS of
> locking in the database layer.
> Thanks in advance.
> Arsen
>|||Hi,
What about using the IIS log file?
you could set it in the way you need it, basically the client IP and maybe
the time, if you configure it to update daily all you have to do at 12:01
is run a process that read the file generated and do what you need.
Cheers,
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation
"Arsen V." <arsen.NoSpamPlease@.emergency24.com> wrote in message
news:emdbHKo6DHA.2412@.TK2MSFTNGP09.phx.gbl...
> Hello,
> What is the best way to accomplish the following.
> 1) An ASP.NET program (consiting of one file somepage.aspx) receives about
> 25,000,000 requests pay day.
> 2) The requests come from a limited set of IP addresses.
> 3) How to count the total number of request from each IP address per day.
> SQL Server 2000 is used on the backend.
> Currently we used the following architecture:
> * Each request to somepage.aspx generates an INSERT into a LogTable1
> * There is a clustered index on the LogTable1 on the dateStamp field which
> is of the type smalldatetime
> * The dateStamp field has a default that sets it to the getdate()
> * At the end of each day at 12:01AM there is a simple query that runs and
> does a group by to count the number of requests from each IP in the given
> date range (past 24 hours). This query works great and takes only 2
minutes
> to run.
> Is there a better way to accomplish this without having to do INSERTS into
> the LogTable1 for each request?
> It would not work to have the ASP.NET program execute an UPDATE each time
to
> increment the total number of request, since this would cause LOTSSS of
> locking in the database layer.
> Thanks in advance.
> Arsen
>|||Hi Kevin,
Do you suggest storing the TOTALs in the Cache?
Or storing the actual requests: date and ip
Would there be a locking problem?
How to do the "periodic updates" to the database from the Cache?
Thanks,
Arsen
"Kevin Spencer" <kevin@.takempis.com> wrote in message
news:ezSYvso6DHA.1936@.TK2MSFTNGP12.phx.gbl...
> You could store the data in your Application Cache, and update it
> periodically to the database.
> --
> HTH,
> Kevin Spencer
> .Net Developer
> Microsoft MVP
> Big things are made up
> of lots of little things.
> "Arsen V." <arsen.NoSpamPlease@.emergency24.com> wrote in message
> news:emdbHKo6DHA.2412@.TK2MSFTNGP09.phx.gbl...
> > Hello,
> >
> > What is the best way to accomplish the following.
> >
> > 1) An ASP.NET program (consiting of one file somepage.aspx) receives
about
> > 25,000,000 requests pay day.
> >
> > 2) The requests come from a limited set of IP addresses.
> >
> > 3) How to count the total number of request from each IP address per
day.
> >
> > SQL Server 2000 is used on the backend.
> >
> > Currently we used the following architecture:
> >
> > * Each request to somepage.aspx generates an INSERT into a LogTable1
> > * There is a clustered index on the LogTable1 on the dateStamp field
which
> > is of the type smalldatetime
> > * The dateStamp field has a default that sets it to the getdate()
> > * At the end of each day at 12:01AM there is a simple query that runs
and
> > does a group by to count the number of requests from each IP in the
given
> > date range (past 24 hours). This query works great and takes only 2
> minutes
> > to run.
> >
> > Is there a better way to accomplish this without having to do INSERTS
into
> > the LogTable1 for each request?
> >
> > It would not work to have the ASP.NET program execute an UPDATE each
time
> to
> > increment the total number of request, since this would cause LOTSSS of
> > locking in the database layer.
> >
> > Thanks in advance.
> >
> > Arsen
> >
> >
>|||Hi Arsen,
You could put a DataTable in the Application Cache, and add records to it
with each Request. As for periodic updating, you could put a routine in the
Session_OnStart Sub that checks an Application DateTime variable, and at
certain intervals, inserts all the records from the DataTable into the
database and clears out the DataTable.
To be safe, you would want to add code to your Application_OnEnd sub to
update the database if the Application stops or times out; however, with 25M
requests per day, that might not be necessary.
--
HTH,
Kevin Spencer
.Net Developer
Microsoft MVP
Big things are made up
of lots of little things.
"Arsen V." <arsen.NoSpamPlease@.emergency24.com> wrote in message
news:#daQL6o6DHA.2908@.tk2msftngp13.phx.gbl...
> Hi Kevin,
> Do you suggest storing the TOTALs in the Cache?
> Or storing the actual requests: date and ip
> Would there be a locking problem?
> How to do the "periodic updates" to the database from the Cache?
> Thanks,
> Arsen
> "Kevin Spencer" <kevin@.takempis.com> wrote in message
> news:ezSYvso6DHA.1936@.TK2MSFTNGP12.phx.gbl...
> > You could store the data in your Application Cache, and update it
> > periodically to the database.
> >
> > --
> > HTH,
> > Kevin Spencer
> > .Net Developer
> > Microsoft MVP
> > Big things are made up
> > of lots of little things.
> >
> > "Arsen V." <arsen.NoSpamPlease@.emergency24.com> wrote in message
> > news:emdbHKo6DHA.2412@.TK2MSFTNGP09.phx.gbl...
> > > Hello,
> > >
> > > What is the best way to accomplish the following.
> > >
> > > 1) An ASP.NET program (consiting of one file somepage.aspx) receives
> about
> > > 25,000,000 requests pay day.
> > >
> > > 2) The requests come from a limited set of IP addresses.
> > >
> > > 3) How to count the total number of request from each IP address per
> day.
> > >
> > > SQL Server 2000 is used on the backend.
> > >
> > > Currently we used the following architecture:
> > >
> > > * Each request to somepage.aspx generates an INSERT into a LogTable1
> > > * There is a clustered index on the LogTable1 on the dateStamp field
> which
> > > is of the type smalldatetime
> > > * The dateStamp field has a default that sets it to the getdate()
> > > * At the end of each day at 12:01AM there is a simple query that runs
> and
> > > does a group by to count the number of requests from each IP in the
> given
> > > date range (past 24 hours). This query works great and takes only 2
> > minutes
> > > to run.
> > >
> > > Is there a better way to accomplish this without having to do INSERTS
> into
> > > the LogTable1 for each request?
> > >
> > > It would not work to have the ASP.NET program execute an UPDATE each
> time
> > to
> > > increment the total number of request, since this would cause LOTSSS
of
> > > locking in the database layer.
> > >
> > > Thanks in advance.
> > >
> > > Arsen
> > >
> > >
> >
> >
>|||what i have done is cache the last 15 minutes. the cache has the ipaddress,
the start of the 15 min interval, pagename, and number of hits during the
interval. as most users cluster their hits, this cuts down the number of
inserts.
i flush the cache every 15 minutes of when too large.
you can then get daily or hourly stats from the db with simple queries.
-- bruce (sqlwork.com)
"Arsen V." <arsen.NoSpamPlease@.emergency24.com> wrote in message
news:emdbHKo6DHA.2412@.TK2MSFTNGP09.phx.gbl...
> Hello,
> What is the best way to accomplish the following.
> 1) An ASP.NET program (consiting of one file somepage.aspx) receives about
> 25,000,000 requests pay day.
> 2) The requests come from a limited set of IP addresses.
> 3) How to count the total number of request from each IP address per day.
> SQL Server 2000 is used on the backend.
> Currently we used the following architecture:
> * Each request to somepage.aspx generates an INSERT into a LogTable1
> * There is a clustered index on the LogTable1 on the dateStamp field which
> is of the type smalldatetime
> * The dateStamp field has a default that sets it to the getdate()
> * At the end of each day at 12:01AM there is a simple query that runs and
> does a group by to count the number of requests from each IP in the given
> date range (past 24 hours). This query works great and takes only 2
minutes
> to run.
> Is there a better way to accomplish this without having to do INSERTS into
> the LogTable1 for each request?
> It would not work to have the ASP.NET program execute an UPDATE each time
to
> increment the total number of request, since this would cause LOTSSS of
> locking in the database layer.
> Thanks in advance.
> Arsen
>

How to correctly propogate data back to the database

I have the following code but do not know the best way to return the updated
DataTable back to the database. I believe I can use the Update method of the
Data Adapter, BUT if true, I also believe I have to 'long-hand' write code
for each individual column data that's being added.....this seems a bit
daft considering that the data is already in the disconnected data table.
Have I lost the plot?? Based on the code below, what is the correct
approach?

Note: sqlcnn is defined at module level.

Public Sub AddRequest(ByVal Eng As String, ByVal Bran As String, ByVal Req
As String) Implements IHelpSC.AddRequest

Dim dtNew As New DataTable("dtNew")
Dim drNew As DataRow
sqlda = New SqlDataAdapter("SELECT * FROM ActiveCalls", sqlcnn)
sqlda.Fill(dtNew)

'Add and populate the new datarow with
'data passed into this subroutine

drNew = dtNew.NewRow
drNew("CallTime") = Format(Date.Now, "dd MMM yyyy").ToString
drNew("Engineer") = Eng
drNew("Branch") = Bran
drNew("Request") = Req
dtNew.Rows.Add(drNew)

End Sub

Hope one of you wizards can help.

Rgds....and Merry Christmas.

PhilPhil (Phil@.nospam.com) writes:
> I have the following code but do not know the best way to return the
> updated DataTable back to the database. I believe I can use the Update
> method of the Data Adapter, BUT if true, I also believe I have to
> 'long-hand' write code for each individual column data that's being
> added.....this seems a bit daft considering that the data is already in
> the disconnected data table.

Yes and no.

First, if memory serves, you don't have to write any extra code, if
you use the default .Update method on the data adapter, but it will
include all columns. But you are better of asking about that in group
like microsoft.public.dotnet.framework.adonet that is devoted to ADO .Net.
Or, instead of asking, just conduct an experiment.

Then comes the next question, whether you actually want it. For a
application of any size, it is usually best to perform all access to
SQL Server through stored procedure. In this way, users does not have
to have direct access to the tables, but only access to the stored
procedures. This makes a big difference for the security of the database.
In many shops, the DBA will not permit anything but stored procedures
anyway.

And if you use stored procedures, it follows by necessity that if you
add another column to a query, that you will have to add it to the
SelectCommand, UpdateCommand and InsertCommand of the DataAdapater as
well. And, yes, that means some extra overhead when you add a new
column, but it is not really a big deal.

Finally some notes about your code:

> sqlda = New SqlDataAdapter("SELECT * FROM ActiveCalls", sqlcnn)

This may be just an example, but permit me to point out that "SELECT *"
should never occur in production code. It may look convenient, but it
isn't. It gives you an extra overhead of retrieving columns, you don't
nead. And it makes it very difficult to find out if a column is actually
used or not, in case you are looking into to drop a column.

> drNew("CallTime") = Format(Date.Now, "dd MMM yyyy").ToString

I don't really know what happens in the end, but you should probably
pass dates as dates. If you format dates and passes them as strings to
SQL Server, they may not be understood by SQL Server, if there are
some unexpected dateformat and langauge settings.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

It sounds like you know what you are talking about! :-).....and you raise
some excellent points. I am extremely new to db programming and just finding
my feet. The comments you make about using 'SELECT *' and stored procedures
I have read in my 'programming databases using .NET ' book. THANKS for the
confirmation. I will eventually change to using stored procedures but I am
trying to pick up the basics regarding 'connected classes' and 'disconnected
classes', Data Adapters, Data Tables, Datasets etc etc etc.

I will take a look at the suggested newsgroups, thanks for your response.

All the best,

Phil

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns95CB77CF3AB42Yazorman@.127.0.0.1...
> Phil (Phil@.nospam.com) writes:
> > I have the following code but do not know the best way to return the
> > updated DataTable back to the database. I believe I can use the Update
> > method of the Data Adapter, BUT if true, I also believe I have to
> > 'long-hand' write code for each individual column data that's being
> > added.....this seems a bit daft considering that the data is already in
> > the disconnected data table.
> Yes and no.
> First, if memory serves, you don't have to write any extra code, if
> you use the default .Update method on the data adapter, but it will
> include all columns. But you are better of asking about that in group
> like microsoft.public.dotnet.framework.adonet that is devoted to ADO .Net.
> Or, instead of asking, just conduct an experiment.
> Then comes the next question, whether you actually want it. For a
> application of any size, it is usually best to perform all access to
> SQL Server through stored procedure. In this way, users does not have
> to have direct access to the tables, but only access to the stored
> procedures. This makes a big difference for the security of the database.
> In many shops, the DBA will not permit anything but stored procedures
> anyway.
> And if you use stored procedures, it follows by necessity that if you
> add another column to a query, that you will have to add it to the
> SelectCommand, UpdateCommand and InsertCommand of the DataAdapater as
> well. And, yes, that means some extra overhead when you add a new
> column, but it is not really a big deal.
> Finally some notes about your code:
> > sqlda = New SqlDataAdapter("SELECT * FROM ActiveCalls", sqlcnn)
> This may be just an example, but permit me to point out that "SELECT *"
> should never occur in production code. It may look convenient, but it
> isn't. It gives you an extra overhead of retrieving columns, you don't
> nead. And it makes it very difficult to find out if a column is actually
> used or not, in case you are looking into to drop a column.
> > drNew("CallTime") = Format(Date.Now, "dd MMM yyyy").ToString
> I don't really know what happens in the end, but you should probably
> pass dates as dates. If you format dates and passes them as strings to
> SQL Server, they may not be understood by SQL Server, if there are
> some unexpected dateformat and langauge settings.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp