Best Query Strategy

I am faced with the need to query the price of parts from a 3 table join.
The problem is the number of parts that need to be queried at one time; 10 t
o
100 parts. That would make for a very messy WHERE clause. I am wonder if
there is a better strategy?
If it matters, I am using VB.NET.
Thanks
--Rob
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200510/1Robin,
Are the parts PART OF a larger organizational unit i.e., a project or a
company or...?
HTH
Jerry
"Robin H via droptable.com" <u4108@.uwe> wrote in message
news:5655fd7d2e20a@.uwe...
>I am faced with the need to query the price of parts from a 3 table join.
> The problem is the number of parts that need to be queried at one time; 10
> to
> 100 parts. That would make for a very messy WHERE clause. I am wonder if
> there is a better strategy?
> If it matters, I am using VB.NET.
> Thanks
> --Rob
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200510/1|||Robin,
Is it possible you could do a VIEW for this as long as the query is not
dynamic?
Shahryar
Robin H via droptable.com wrote:

>I am faced with the need to query the price of parts from a 3 table join.
>The problem is the number of parts that need to be queried at one time; 10
to
>100 parts. That would make for a very messy WHERE clause. I am wonder if
>there is a better strategy?
>If it matters, I am using VB.NET.
>Thanks
>--Rob
>
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is
legally privileged. The information is solely for the use of the intended
recipient(s); any disclosure, copying, distribution, or other use of this in
formation is strictly prohi
bited. If you have received this e-mail in error, please notify the sender
by return e-mail and delete this message. Thank you.

Best Query Strategy

I am faced with the need to query the price of parts from a 3 table join.
The problem is the number of parts that need to be queried at one time; 10 to
100 parts. That would make for a very messy WHERE clause. I am wonder if
there is a better strategy?
If it matters, I am using VB.NET.
Thanks
--Rob
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200510/1
Robin,
Are the parts PART OF a larger organizational unit i.e., a project or a
company or...?
HTH
Jerry
"Robin H via droptable.com" <u4108@.uwe> wrote in message
news:5655fd7d2e20a@.uwe...
>I am faced with the need to query the price of parts from a 3 table join.
> The problem is the number of parts that need to be queried at one time; 10
> to
> 100 parts. That would make for a very messy WHERE clause. I am wonder if
> there is a better strategy?
> If it matters, I am using VB.NET.
> Thanks
> --Rob
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200510/1
|||Robin,
Is it possible you could do a VIEW for this as long as the query is not
dynamic?
Shahryar
Robin H via droptable.com wrote:

>I am faced with the need to query the price of parts from a 3 table join.
>The problem is the number of parts that need to be queried at one time; 10 to
>100 parts. That would make for a very messy WHERE clause. I am wonder if
>there is a better strategy?
>If it matters, I am using VB.NET.
>Thanks
>--Rob
>
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohi
bited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.

Best Query Strategy

I am faced with the need to query the price of parts from a 3 table join.
The problem is the number of parts that need to be queried at one time; 10 to
100 parts. That would make for a very messy WHERE clause. I am wonder if
there is a better strategy?
If it matters, I am using VB.NET.
Thanks
--Rob
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200510/1Robin,
Are the parts PART OF a larger organizational unit i.e., a project or a
company or...?
HTH
Jerry
"Robin H via SQLMonster.com" <u4108@.uwe> wrote in message
news:5655fd7d2e20a@.uwe...
>I am faced with the need to query the price of parts from a 3 table join.
> The problem is the number of parts that need to be queried at one time; 10
> to
> 100 parts. That would make for a very messy WHERE clause. I am wonder if
> there is a better strategy?
> If it matters, I am using VB.NET.
> Thanks
> --Rob
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200510/1|||Robin,
Is it possible you could do a VIEW for this as long as the query is not
dynamic?
Shahryar
Robin H via SQLMonster.com wrote:
>I am faced with the need to query the price of parts from a 3 table join.
>The problem is the number of parts that need to be queried at one time; 10 to
>100 parts. That would make for a very messy WHERE clause. I am wonder if
>there is a better strategy?
>If it matters, I am using VB.NET.
>Thanks
>--Rob
>
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohibited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.

Best Practices Managing Control Flow

Hello,
My package "splits" into a number of work flows by the use of precedent management.

After a number of steps I want to combine the flows back to one and finish the package.

What is the mechanism to combine the flows into a singular, linear process again so that I do not have to multiply define identical tasks to finish each flow leg?

Just connect them all back to a common task. Double click on one of the precedence connectors and ensure that the operation is set for "AND" and not "OR" to ensure that all preceding tasks complete before going into the final task.sql

Best Practices (Forms/Letters)

I have a rather large number of already existing forms and letters (in paper
format) that I need to recreate in Reporting Services. After reading a few
posts with regards to formatting etc I have noted that several recommend not
using Text Boxes/Labels but rather tables.
As I'm very new to RS could somebody please advise as to the best possible
way to go about creating these.
Often there is a few lines of text then something that needs to be populated
from the database then another few lines of text etc.
Any help on this would be greatly appreciated.On Sun, 3 Apr 2005 18:03:02 -0700, "Nat Johnson"
<NatJohnson@.discussions.microsoft.com> wrote:
>I have a rather large number of already existing forms and letters (in paper
>format) that I need to recreate in Reporting Services. After reading a few
>posts with regards to formatting etc I have noted that several recommend not
>using Text Boxes/Labels but rather tables.
>As I'm very new to RS could somebody please advise as to the best possible
>way to go about creating these.
>Often there is a few lines of text then something that needs to be populated
>from the database then another few lines of text etc.
>Any help on this would be greatly appreciated.
>
Nat,
Can you explain more about what you are trying to do (ignoring what
the appropriate software might or might not be).
On the face of it, I suspect that Reporting Services may not be the
optimal solution. But I would prefer to better understand your
objective. Forms, for example, in my terminology are used to collect
data from users. I assume that is not part of your aim?
Andrew Watt
MVP - InfoPath|||The company I work for are creating an application which includes an SQL
database. I have to use RS for the reports that are required. Most of the
reports are the traditional style (eg lineflow looking) but others are
letters or forms that the system will automatically print out if a certain
condition is met.
The forms that will be printed will have parts that will be populated from
the database. The web team for the project are creating the online forms. I
have to make my forms similar to theirs for printing out as once the parts
are populated from db they need to be printed so a user can take them to a
site for hand completion.
Most of the letters that need to be printed out need to get data from the
db. And will be automatically printed by the system when a condition is met.
The normal reports that need to be created are not the problem. My question
really relates to the forms and letters and the formatting of these.
Let me know if this helps you anymore or not.
cheers
Nat
"Andrew Watt [MVP - InfoPath]" wrote:
> On Sun, 3 Apr 2005 18:03:02 -0700, "Nat Johnson"
> <NatJohnson@.discussions.microsoft.com> wrote:
> >I have a rather large number of already existing forms and letters (in paper
> >format) that I need to recreate in Reporting Services. After reading a few
> >posts with regards to formatting etc I have noted that several recommend not
> >using Text Boxes/Labels but rather tables.
> >
> >As I'm very new to RS could somebody please advise as to the best possible
> >way to go about creating these.
> >
> >Often there is a few lines of text then something that needs to be populated
> >from the database then another few lines of text etc.
> >
> >Any help on this would be greatly appreciated.
> >
> Nat,
> Can you explain more about what you are trying to do (ignoring what
> the appropriate software might or might not be).
> On the face of it, I suspect that Reporting Services may not be the
> optimal solution. But I would prefer to better understand your
> objective. Forms, for example, in my terminology are used to collect
> data from users. I assume that is not part of your aim?
> Andrew Watt
> MVP - InfoPath
>|||You should be able to render to PDF and print the PDF documents.
If the documents are to be completed by hand that ought to work for
you.
Almost certainly there will be some tweaking involved to get the
appearance that everybody is happy with.
Andrew Watt
MVP - InfoPath
On Mon, 4 Apr 2005 13:05:03 -0700, "Nat Johnson"
<NatJohnson@.discussions.microsoft.com> wrote:
>The company I work for are creating an application which includes an SQL
>database. I have to use RS for the reports that are required. Most of the
>reports are the traditional style (eg lineflow looking) but others are
>letters or forms that the system will automatically print out if a certain
>condition is met.
>The forms that will be printed will have parts that will be populated from
>the database. The web team for the project are creating the online forms. I
>have to make my forms similar to theirs for printing out as once the parts
>are populated from db they need to be printed so a user can take them to a
>site for hand completion.
>Most of the letters that need to be printed out need to get data from the
>db. And will be automatically printed by the system when a condition is met.
>The normal reports that need to be created are not the problem. My question
>really relates to the forms and letters and the formatting of these.
>Let me know if this helps you anymore or not.
>cheers
>Nat
>"Andrew Watt [MVP - InfoPath]" wrote:
>> On Sun, 3 Apr 2005 18:03:02 -0700, "Nat Johnson"
>> <NatJohnson@.discussions.microsoft.com> wrote:
>> >I have a rather large number of already existing forms and letters (in paper
>> >format) that I need to recreate in Reporting Services. After reading a few
>> >posts with regards to formatting etc I have noted that several recommend not
>> >using Text Boxes/Labels but rather tables.
>> >
>> >As I'm very new to RS could somebody please advise as to the best possible
>> >way to go about creating these.
>> >
>> >Often there is a few lines of text then something that needs to be populated
>> >from the database then another few lines of text etc.
>> >
>> >Any help on this would be greatly appreciated.
>> >
>> Nat,
>> Can you explain more about what you are trying to do (ignoring what
>> the appropriate software might or might not be).
>> On the face of it, I suspect that Reporting Services may not be the
>> optimal solution. But I would prefer to better understand your
>> objective. Forms, for example, in my terminology are used to collect
>> data from users. I assume that is not part of your aim?
>> Andrew Watt
>> MVP - InfoPath

Best practice for storing long text fields

As we all know, there is a 8060 bytes size limit on SQL Server rows. I
have a table which requires a number of text fields (5 or 6). Each of
these text fields should support a max of 4000 characters. We currently
store the data in varchar columns, which worked fine untill our
appetite for text fields increased to the current requirement of 5, 6
fields of 4000 characters size. I am given to review a design, which
esentially suggests moving the text columns to a separate TextFields
table. The TextFields table will have two columns - a unique reference
and a VARCHAR (4000) column, thus allowing us to crossreference with
the original record. My first impresion is that I'd rather use the SQL
Server 'text' DB type instead, which would allow me the same
functionality with much less effort and possibly better performance.
Can anyone advise on advantages and disadvantages of the two options
and what the best practice in this case would be.
Any advise will be well appreciated.
TzankoTzanko wrote:

Quote:

Originally Posted by

As we all know, there is a 8060 bytes size limit on SQL Server rows. I
have a table which requires a number of text fields (5 or 6). Each of
these text fields should support a max of 4000 characters. We currently
store the data in varchar columns, which worked fine untill our
appetite for text fields increased to the current requirement of 5, 6
fields of 4000 characters size. I am given to review a design, which
esentially suggests moving the text columns to a separate TextFields
table. The TextFields table will have two columns - a unique reference
and a VARCHAR (4000) column, thus allowing us to crossreference with
the original record. My first impresion is that I'd rather use the SQL
Server 'text' DB type instead, which would allow me the same
functionality with much less effort and possibly better performance.
Can anyone advise on advantages and disadvantages of the two options
and what the best practice in this case would be.

I hear that VARCHAR(MAX) is the new TEXT, but it's only available
in SQL 2005.|||Tzanko (tzanko.tzanev@.strategicthought.com) writes:

Quote:

Originally Posted by

As we all know, there is a 8060 bytes size limit on SQL Server rows.

Yes, in SQL 2000. Not in SQL 2005. There a row can span pages.

Quote:

Originally Posted by

I have a table which requires a number of text fields (5 or 6).

Do these text fields hold the same text that spans fields, or are
they different texts?

Quote:

Originally Posted by

I am given to review a design, which esentially suggests moving the text
columns to a separate TextFields table. The TextFields table will have
two columns - a unique reference and a VARCHAR (4000) column, thus
allowing us to crossreference with the original record.

If they are different texts they should be in different columns, or you
should have some type column telling them apatt.

Quote:

Originally Posted by

My first impresion is that I'd rather use the SQL Server 'text' DB type
instead, which would allow me the same functionality with much less
effort and possibly better performance.

Yes, if they the column are all the same text, this might be the way
to go. You can store up to 2GB in a text column.

But better performance? Nah. If nothing else, text is difficult to
work with and there are lot of limitations. As Ed mention, SQL 2005
comes with varchar(MAX) which also can fit 2GB, but which you can
work with in the same way as a regular varchar.

If the columns are different texts, I see little point to use the
text data type.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Are you saying that in SQL 2000 you can Span VarChar's into multiple columns
automatically? If so how?

Cheers, @.sh

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns983CEED4849AEYazorman@.127.0.0.1...

Quote:

Originally Posted by

Tzanko (tzanko.tzanev@.strategicthought.com) writes:

Quote:

Originally Posted by

>As we all know, there is a 8060 bytes size limit on SQL Server rows.

>
Yes, in SQL 2000. Not in SQL 2005. There a row can span pages.
>

Quote:

Originally Posted by

>I have a table which requires a number of text fields (5 or 6).

>
Do these text fields hold the same text that spans fields, or are
they different texts?
>

Quote:

Originally Posted by

>I am given to review a design, which esentially suggests moving the text
>columns to a separate TextFields table. The TextFields table will have
>two columns - a unique reference and a VARCHAR (4000) column, thus
>allowing us to crossreference with the original record.

>
If they are different texts they should be in different columns, or you
should have some type column telling them apatt.
>

Quote:

Originally Posted by

>My first impresion is that I'd rather use the SQL Server 'text' DB type
>instead, which would allow me the same functionality with much less
>effort and possibly better performance.

>
Yes, if they the column are all the same text, this might be the way
to go. You can store up to 2GB in a text column.
>
But better performance? Nah. If nothing else, text is difficult to
work with and there are lot of limitations. As Ed mention, SQL 2005
comes with varchar(MAX) which also can fit 2GB, but which you can
work with in the same way as a regular varchar.
>
If the columns are different texts, I see little point to use the
text data type.
>
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||@.sh (spam@.spam.com) writes:

Quote:

Originally Posted by

Are you saying that in SQL 2000 you can Span VarChar's into multiple
columns automatically? If so how?

No. What I said is that on SQL 2005 a row can span pages, so that you can
have more than 8060 bytes per row.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Cool!

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns983D9BE59C16AYazorman@.127.0.0.1...

Quote:

Originally Posted by

@.sh (spam@.spam.com) writes:

Quote:

Originally Posted by

>Are you saying that in SQL 2000 you can Span VarChar's into multiple
>columns automatically? If so how?

>
No. What I said is that on SQL 2005 a row can span pages, so that you can
have more than 8060 bytes per row.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Many thnaks for your replies.

Just to clarify the issue:
The requirement is to create a table that has say 6 columns which store
strings (such as Description, Notes, etc.) Each of these 6 columns
should store a char string of max length of 4000 characters. The
problem is that SQL Server 2000 will not work if I simply defined the
columns as varchar(4000) as at some point the row size reaches the page
size of 8060 and this generates an error. There is a 8060 bytes limit
on SQL Server 2000 rows. Note that I am not trying to store the same
string into 6 different columns spanning from column to column. I have
a separate string to store in each column.

The question:
What is the best way to implement this in SQL Server 2000. In
particular I am looking at two options: Setting each of the 6 columns
to be of type 'text'. Looking at the documentation, it appears that
this would behave for as long as each string is not longer than 4000
characters and I am happy to have this limit. It however is unpleasant
to use the text type for longer than 4000 char strings, as in this case
I understand there are some specific ways of handling the data. Option
two is to create a new LongStrings table with 2 columns - long unique
number and varchar(4000). Each string is stored in this LongStrings
table and is crosreferenced (by using the unique ID) with its original
cell in its original table. Now I'd preffer option 1 (provided I do not
have to do anything special to handle the strings) and would like to
avoid option 2 because it is not easy to write queries to get the data.

Second question is what is the situation with SQL Server 2005. I
understand I can simply define the columns as varchar(max) and do not
have to do anything special. Has someone used this successfully and can
you confirm it ste case?

Thanks for your help.

Tzanko

@.sh wrote:

Quote:

Originally Posted by

Cool!
>
>
"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns983D9BE59C16AYazorman@.127.0.0.1...

Quote:

Originally Posted by

@.sh (spam@.spam.com) writes:

Quote:

Originally Posted by

Are you saying that in SQL 2000 you can Span VarChar's into multiple
columns automatically? If so how?

No. What I said is that on SQL 2005 a row can span pages, so that you can
have more than 8060 bytes per row.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Tzanko (tzanko.tzanev@.strategicthought.com) writes:

Quote:

Originally Posted by

The question:
What is the best way to implement this in SQL Server 2000. In
particular I am looking at two options: Setting each of the 6 columns
to be of type 'text'. Looking at the documentation, it appears that
this would behave for as long as each string is not longer than 4000
characters and I am happy to have this limit. It however is unpleasant
to use the text type for longer than 4000 char strings, as in this case
I understand there are some specific ways of handling the data. Option
two is to create a new LongStrings table with 2 columns - long unique
number and varchar(4000). Each string is stored in this LongStrings
table and is crosreferenced (by using the unique ID) with its original
cell in its original table. Now I'd preffer option 1 (provided I do not
have to do anything special to handle the strings) and would like to
avoid option 2 because it is not easy to write queries to get the data.

The best in my opinion is to create two or three new tables and rename
the existing tbable, and the create a view that unifies them all. Then in
SQL 2005 you can scrap the view, and move the columns back to the mother
table. Very litte code would actually be affected.

If the key of the table is (cola, colb) the new tables should also have
the keys (cola, colb). Simply, what you do is that you split the columns
over several tables.

You should consider text or varchar(max) if you really need to fit more
than 8000 characters.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Best practice for SQL connections and Asp.Net

Hi.

We have developed as quite simple ASP.Net webpage that fetches a number of information from a SQL 2005 database. We are having some problems though, becuase of a firewall that is beetween the webserver and the SQL server, and I think this is because of bad code from my part. I'm not that experiensed yet, so I'm sure that there is much to learn.

Usualy when I do a query against a SQL database, I do something like this:

Function GO_FormatRecordBy(ByVal intRecordByAs Integer)
Dim dbQueryStringAs String
Dim dbCommandAs OleDbCommand
Dim dbQueryResultAs OleDbDataReader

dbQueryString ="SELECT Name FROM tblRegistrators WHERE tblRegistratorsID ='" & intRecordBy & "'"
dbCommand = New OleDbCommand(dbQueryString, dbConn)

dbConn.Open()

dbQueryResult = dbCommand.ExecuteReader(CommandBehavior.CloseConnection)
dbQueryResult.Read()

dbConn.Close()

dbCommand = Nothing

Return dbQueryResult("Name")

End Function

Now, lets say that I have a DataList that I populate with Integer values, and I want to "resolve" the from another table, then i do a function like the one above. I guess that this means that I open and close quite alot of connections against the database server when I have a large tabel. Is there any better way of doing this? Chould one open a database connection globaly in lets say the ASA fil? Whould that be a better aproch?

When I added the CommandBehavior.CloseConnection to the ExecuteReader statment, I noticed that it was a bit faster, and I think there was fewer connections in the database, so maby there is more to the "closing connections" then I usualy do.

Any tips on this?

Best reagrds,
Johan Christensson

The thing with DataReaders is that you have to explicitly close them when you have finished with them. The CommandBehaviour.CloseConnection option makes sure that the connection is closed when you close the DataReader.

Since you are only retrieving one value in the example you have shown, you should use ExecuteScalar(), which doesn't need a DataReader. That would be the most efficient way of accomplishing this particular task.

Also, in answer to your question about global connections, forget you ever thought about it. That is a very poor idea. It forces everyone who accesses your app to use the same connection, and as traffic increases, delays occur as requests are queued.

I don't quite understand what you mean when you talk about resolving records from another table, but my instinct is that nested datalists or join queries might be a better way to go.

|||

Thanks for your awnser.

What I mean with "resolving records fro another tabel" is that lets say that I have one table that consists, in this case of a SiteID and a TechnicanID, when the DataList is generated, I invoke a function where the output in the DataList is looked up in another table and resolved as a name. I guess that this is quite stupid, since I guess that it would generate an extra query, in this case two querys for each row in the datalist. Right now I have a datalist/asp page that generated about 400 connections to the database, and because of this, the firewall blocks the webserver.

I guess that this would better be done using some kind of SQL statement, but I'm not sure on how. Lets say that I have the following SQL string:

dbQueryString ="SELECT SiteID, TechnicanID FROM tblRegistrators WHERE tblRegistratorsID ='" & intRecordBy & "'"

And I want the values from SiteID and TechnicanID to be the result from a query in another table, how do I do this?

Best reagrds,
Johan Christensson

|||

I think this will work for you:

dbQueryString = "Select tblSites.SiteName, tblTechnicians.TechnicianName from tblRegistrators inner join tblSites on tblRegistrators.SiteID = tblSites.SiteID inner join tblTechnicians on tblRegistrators.TechnicianID = tblTechnicians.TechnicianID where tblRegistrators.ID = '"&intRecordBy&"'"

you may want to look up some information on Joins (Inner and Outer primarily) they make life much simpler, and more complicated at the same time. Trying to sort out queries with upwards of 20 joins is one of the reasons I have so much grey hair

Hope this helps

-madrak

|||

Madrak has given you a solution using Joins in your SQL as I suggested earlier, but another approach could be to select all from the SiteID and TechnicianID tables into separate DataSets on one connection. Once the dataset has been populated, you can close the connection and work with it in a disconnected fashion, looping though it to your heart's content.

You may well have trouble getting to grips with Joins to start with, so I suggest that you make use of the Query Designer in SQL Management Studio (Express) to write the query for you. Simply add the 3 tables to the designer, then drag foreign keys onto primary keys to create relationships eg SiteID in tblRegistrators would be a foreign key and should be dragged to SiteID (primary key) in what I presume might be called tblSites. As soon as you do that, you will see that the textual part of the query builds the default inner join into itself.

But in essence, your instinct is correct. Hammering a database repeatedly in a loop is poor practice - especially if you aren't closing DataReaders correctly

|||

After some googeling I figured out that I should do some kind of nested statment, but the anwser from Madrak actualy was in a format that I understod it. :) Not only does it work, is fast as lightning. It took som trial and error in teh Query Designer before I got hold of it. The way I have done this previusly, always was very slow, so I have some tweeking to do i guess on some other webpages.

So thanks a milion.

A followup question on Mikesdotnetting awnser:

What is best here? To do the complete table using nested SQL statements or do it the DataSet way? I guess that would depend on the load on the webservers verses the SQL server, but is we ignore that right now, and say that you have a some application that you run on you computer. What way would you go then?

Thanks again.

Best regards,
Johan Christensson

|||

JohanCh:

A followup question on Mikesdotnetting awnser:

What is best here? To do the complete table using nested SQL statements or do it the DataSet way? I guess that would depend on the load on the webservers verses the SQL server, but is we ignore that right now, and say that you have a some application that you run on you computer. What way would you go then?

Oh, without a doubt I would use Joins in my SQL. I'm a firm believer in making as few requests of the database as possible. The Join approach requires just one request. DataSets require 3. You might think 3 is nothing compared to the 35,000 you were making (), but I look at it as 300% more than I need. And it's a lot of code less too.

Best practice for handling XML schema hierarchies?

I have a number of tables with columns of xml datatype. Each of these columns are typed against a different XML schema collection. However, each of the XML schema collections contain a hierarchy of schema definitions - and the schemas towards the top of the hierarchy are used by a number of different XML schema collections. I want to define the schemas in such a way that if I need to change a schema towards the top of the hierarchy, I only need to change it in one place.

I understand that it is not possible to reference a schema in one collection from another - is my understanding correct? (If I am wrong, then please disregard the following)

If the schemas must be duplicated in each xml schema collection that needs them, then I am considering the following approach. Are there any better methods available?

- Create a 'reference' xml schema collection that contains all the schemas
- Create a table that relates a schema to all the collections that need it
- Write a stored proc that updates all the individual collections appropriately when the reference collection is updated

Using this method, I would anticipate updating the 'reference' schema and running the stored procedure at a quiet time

I could just use a single collection for everything - but although it would ensure that the contents of a column satisfied a schema - it wouldn't check that it satisfied the correct schema. I guess I could put separate validation on the column to ensure that the right contents had been added, but this seems to run counter to the whole idea of using the XML collections

Any thoughts?

You are correct that you cannot refer to schemas from other schema collections.

You approach of using a master schema collection sounds ok. Alternatively, you could use a special table that contains each one of the schemas in an XML datatype column. That way, you could even perform updates on the schemas programmatically, and you would preserve annotations and comments in the schema as an added bonus.

You then could still do the stored procs.

Note however, that you have to be careful with evolving your schemas in that they should only be gaining new elements and types. Otherwise the schema collection will disallow such updates since the cost of revalidation and potential validation failure of old data was too high for be done implicitly...

Best regards

Michael

|||Thanks Michael - will give it a go

Best Data type to hole phonenumber

Whats the best type to hold a phone number in this format with a column in my sql table...should i use varchar? or something else....any help would be great!!

Is this phone number US-only or is it international?

Thanks

Waseem

|||its only US|||

Take a look at this example

http://www.rampant-books.com/t_super_sql_128_user-defined_data_types.htm

Hope this helps

Best Advice

I have a table containing 65 fields. One of the fields is a varchar(6) field
which is unique and contains a client number . witin the 64 other fields
there are 10 'PIN number' fields ( PIN1, PIN2 etc ) and i need to check if a
given pin exists
ie check if '123456' exists in PIN1, PIN2 - PIN10
any suggestionsDo all the pin columns have different values? Or is one of them populated
and the rest of them NULL? And is '123456' the client number in this case?
On 3/12/05 3:37 PM, in article
A13152B1-6807-4282-8D2A-1035B7A4DAB7@.microsoft.com, "Peter Newman"
<PeterNewman@.discussions.microsoft.com> wrote:

> I have a table containing 65 fields. One of the fields is a varchar(6) fie
ld
> which is unique and contains a client number . witin the 64 other fields
> there are 10 'PIN number' fields ( PIN1, PIN2 etc ) and i need to check if
a
> given pin exists
> ie check if '123456' exists in PIN1, PIN2 - PIN10
> any suggestions|||Peter,
If there is no business-related distinction beween what is in PIn1, from
what is in Pin2 - Pin 10, i.e., if it does not matter where ValueA is in Pin
1
and ValueB in Pin2, or the other way around, then what you have is a simple
list of Pins associated with the parent record... (which s a client, I take
it)
If this is true, then you might want to read up on database normalization
concepts somewhere. The *Right* way to store these data items would be in
another table. If you had the data that way, this query, (as well as man
y
others) would be much simpler... not to mention a whole hos of other
issues...
"Peter Newman" wrote:

> I have a table containing 65 fields. One of the fields is a varchar(6) fie
ld
> which is unique and contains a client number . witin the 64 other fields
> there are 10 'PIN number' fields ( PIN1, PIN2 etc ) and i need to check if
a
> given pin exists
> ie check if '123456' exists in PIN1, PIN2 - PIN10
> any suggestions|||Sorry Aaron, The PIN numbers will be different or Null. There will always b
e
a PIN1 value but from PIN2 - PIN10 may be Null's
'123456' is the pin number i am sing in this case ane you '111111' the
client number
Thanks
"Aaron [SQL Server MVP]" wrote:

> Do all the pin columns have different values? Or is one of them populated
> and the rest of them NULL? And is '123456' the client number in this case
?
>
>
> On 3/12/05 3:37 PM, in article
> A13152B1-6807-4282-8D2A-1035B7A4DAB7@.microsoft.com, "Peter Newman"
> <PeterNewman@.discussions.microsoft.com> wrote:
>
>|||Hi
You may want to try something like:
CREATE FUNCTION dbo.fn_IsNew( @.ClientName VARCHAR(20), @.PIN CHAR(6))
RETURNS INT
AS
BEGIN
IF EXISTS ( SELECT 1 FROM Cushion WHERE ClientName = @.ClientName
AND ( PIN1 = @.PIN OR PIN2 = @.PIN OR PIN3 = @.PIN OR PIN4 = @.PIN OR PIN5 =
@.PIN OR PIN6 = @.PIN OR PIN7 = @.PIN OR PIN8 = @.PIN OR PIN9 = @.PIN OR PIN10 =
@.PIN ) )
RETURN 1
RETURN 0
END
CREATE TABLE Cushion ( ClientName VARCHAR(20) NOT NULL,
PIN char(6) NOT NULL ,
PIN1 char(6),
PIN2 char(6),
PIN3 char(6),
PIN4 char(6),
PIN5 char(6),
PIN6 char(6),
PIN7 char(6),
PIN8 char(6),
PIN9 char(6),
PIN10 char(6),
CONSTRAINT IsNew CHECK ( dbo.fn_IsNew(ClientName ,PIN) = 0 )
)
INSERT INTO Cushion ( ClientName, PIN, PIN1, PIN2 )
VALUES ( 'ABC', '123456', '234567', '345678' )
INSERT INTO Cushion ( ClientName, PIN, PIN1, PIN2 )
VALUES ( 'DEF', '123456', '123456', '345678' )
/*
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with TABLE CHECK constraint 'IsNew'. The
conflict occurred in database 'Needlepoint', table 'Cushion'.
The statement has been terminated.
*/
Normalising the structure would make your queries easier, but you may want
to try something like:
CREATE VIEW NormalisedCushion AS
SELECT ClientName, PIN1 AS OldPin FROM Cushion
UNION ALL SELECT ClientName, PIN2 FROM Cushion
UNION ALL SELECT ClientName, PIN3 FROM Cushion
UNION ALL SELECT ClientName, PIN4 FROM Cushion
UNION ALL SELECT ClientName, PIN5 FROM Cushion
UNION ALL SELECT ClientName, PIN6 FROM Cushion
UNION ALL SELECT ClientName, PIN7 FROM Cushion
UNION ALL SELECT ClientName, PIN8 FROM Cushion
UNION ALL SELECT ClientName, PIN9 FROM Cushion
UNION ALL SELECT ClientName, PIN10 FROM Cushion
SELECT * FROM NormalisedCushion
WHERE OldPin = '234567'
John
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:A13152B1-6807-4282-8D2A-1035B7A4DAB7@.microsoft.com...
>I have a table containing 65 fields. One of the fields is a varchar(6)
>field
> which is unique and contains a client number . witin the 64 other fields
> there are 10 'PIN number' fields ( PIN1, PIN2 etc ) and i need to check if
> a
> given pin exists
> ie check if '123456' exists in PIN1, PIN2 - PIN10
> any suggestions|||Okay, can you provide DDL, sample data, and desired results. See
http://www.aspfaq.com/5006
http://www.aspfaq.com/
(Reverse address to reply.)
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:0A679D44-51D8-45D5-8737-C4791812C84B@.microsoft.com...
> Sorry Aaron, The PIN numbers will be different or Null. There will always
be
> a PIN1 value but from PIN2 - PIN10 may be Null's
> '123456' is the pin number i am sing in this case ane you '111111' the
> client number
> Thanks
> "Aaron [SQL Server MVP]" wrote:
>
populated
case?
field
fields
check if a

benefits/disadvantages of activex/sql-dmo

i have just setup my main sql server as a central publisher/distributor and a number of laptops (only connect to network once a week) with msde as annonymous pull subscribers, using merge replication. Using windows synchronisation manager i have run the c
ommon conflicts i expect to occur and everything works ok.
Could anyone tell me the benefits/pitfalls of using SQL Merge Control or SQL-DMO, over Windows Syncronisation Manager?
Is there a way during syncronisation to determine which side publisher/subscriber has priority on each conflict as and when they occur?
Please bear in mind this is the first time I have worked with SQL Server and I am the only IT person in a small company so I am avoiding over-complicating things for users as much as possible. These message boards are brilliant for advice from people more
experienced than me.
Thanks for your help
SQL DMO is what the replication wizards use. Under the covers it runs replication stored procedures.
Think of the Active
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"James P" wrote:

> i have just setup my main sql server as a central publisher/distributor and a number of laptops (only connect to network once a week) with msde as annonymous pull subscribers, using merge replication. Using windows synchronisation manager i have run the
common conflicts i expect to occur and everything works ok.
> Could anyone tell me the benefits/pitfalls of using SQL Merge Control or SQL-DMO, over Windows Syncronisation Manager?
> Is there a way during syncronisation to determine which side publisher/subscriber has priority on each conflict as and when they occur?
> Please bear in mind this is the first time I have worked with SQL Server and I am the only IT person in a small company so I am avoiding over-complicating things for users as much as possible. These message boards are brilliant for advice from people mo
re experienced than me.
> Thanks for your help
>
|||sorry that last message was send prematurely.
Think of the ActiveX controls as a lightweight version of SQL DMO. Windows Synchronization Manager uses the ActiveX controls.
Here is a brief rundown of the differences. BTW - I only use SQL DMO, although its more complex to code with, it is more feature rich.
1) If you are building publications, you must use SQL-DMO. You cannot build publications or push subscriptions with ActiveX replication controls.
2) The ActiveX replication controls' functionality is limited to copying subscription databases (but not attaching them), managing the Snapshot and Distribution Agents, creating pull subscriptions, and reinitializing subscriptions.
3) Despite their limitations, the ActiveX replication controls have proven to be far more popular than SQL-DMO is as they contain only three classes, and are simpler to work with .
4) you can't control the ActiveX agents through the agents folder in EM.
To answer your specific question regarding priority in SQL DMO its the priority property of the MergePublication class, in ActiveX its the SubscriptionPriority and SubscriptionPriorityType of the SQLMerge class.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"James P" wrote:

> i have just setup my main sql server as a central publisher/distributor and a number of laptops (only connect to network once a week) with msde as annonymous pull subscribers, using merge replication. Using windows synchronisation manager i have run the
common conflicts i expect to occur and everything works ok.
> Could anyone tell me the benefits/pitfalls of using SQL Merge Control or SQL-DMO, over Windows Syncronisation Manager?
> Is there a way during syncronisation to determine which side publisher/subscriber has priority on each conflict as and when they occur?
> Please bear in mind this is the first time I have worked with SQL Server and I am the only IT person in a small company so I am avoiding over-complicating things for users as much as possible. These message boards are brilliant for advice from people mo
re experienced than me.
> Thanks for your help
>

Begins With

I am trying to reduce the number of fax numbers I have in my access database querying out all numbers except 6 area codes.

I have tried to do:

SELECT FaxNumbers
FROM FAXList
WHERE FaxNumbers BEGINS WITH 972 OR 817 OR 214;

but it doesn't work, what am I doing wrong?Hi
Try the following query

select phone from users where phone like '301%' or phone like '201%' or phone like '212%';
Thanx and Regards
Aruneesh|||I have tried both the command BEGINS WITH 972 or 817 or 214 or 903 and LIKE 972% or 817% or 214% or 903%, and neither work. I get no results even though I know that there are some in there.

Can anybody help me?
Gurka|||Could u post the command u r using to query the DB.|||I think I got it to work.
I used * instead of %.

Gurka|||Gurka
Good luck with your query.
Aruneesh

Beginning PL/SQL question

I'm writing a pl/sql program to insert the month number and number of days in that month into a table called Month_Days. I only want to use the Insert Into command once, and I also don't want to use a nested If statement. i can get the month number into month column using a loop and a counter going up to 13, but i don't know how to get the number of days into the days column. I'm assuming Jan has 31 days, Feb has 29, March has 31, Apr has 30, etc.
Here's what i have so far:

Create Table Month_Days(
Month Number(2)
Days Number(2));

Declare
LoopX Binary_Integer;
Begin
LoopX:=0;
Loop
LoopX:=LoopX+1;
If LoopX=13 Then
Exit;
End If;
Insert Into Month_Days Values (LoopX);
End Loop;
End;

Thanks in advance for any help!Hello,

the easiest way to get the lastnumber of a months is:

cDate VARCHAR2(20);
cLastDay VARCHAR2(2);

-- Build date
cDate := TO_CHAR(loopx) || '01' || '2003'

SELECT TO_CHAR(LAST_DAY(TO_DATE(cDate, 'MMDDYYYY')), 'DD')
INTO cLastDay FROM dual;

Hope that helps ?

If you want to use a PL/SQL editor try our product AlligatorSQL. It is very helpful ...

Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com

BDE 5.01 not connecting to SQL2K after patching

Hello All,
I am experiencing unusual behaviour with a number of desktops (3 of 20) running the BDE and the SQL connectivity option from the sql cd. For reasons unknown the BDE suddenly stops connecting to the database and cant be fixed without a reimage. Appears t
o be a key or dll that is causing interoperability issues.
No idea what o/s patch is causing trouble at this stage but its a recent phenomena.
has anyone seen this type of behaviour with third part software using BDE/SQL connectivity ?
Brian in AU
+61 431 479 751
What error do you get?
Could the problem be something along these lines?
259569 PRB: Installing Third-Party Product Breaks Windows 2000 MDAC Registry
http://support.microsoft.com/?id=259569
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.