How can i cluster sql 2005 for load balancing?

i have a table with 10,000,000,000 records and i need Select and Insert many

records from or into this table in less than one second.

i can't buy a very expensive hardware(Server) for this SQL Server 2005

but i can buy many medium price hardwares(Servers) for this SQL Server

2005.

how can i distribute or cluster this table between many hardwares(Servers)?

note: i have few users (maximum 5 users) for my database but i have a

very large table and Sql server 2005 server need to respond to this

users in less than 1 second.

i want to distribute this huge table in seperated hardwares. becuase i

can't buy a very expensive hardware from my server but i can buy many

medium price hardware for my server.

note: i need this: when a user run a select query on this huge table

his/her request distribute between many hardwares not one hardware.moving to availablility folder...|||

One option for you is to use peer-to-peer replication as a way of partitioning the data between various servers and having the updates on each server reflected on all others. You'll need some mid-tier logic to direct queries to the appropriate server though.

Checkout http://msdn2.microsoft.com/en-us/library/ms151196.aspx

Thanks

How can i cluster sql 2005 for load balancing?

i have a table with 10,000,000,000 records and i need Select and Insert many

records from or into this table in less than one second.

i can't buy a very expensive hardware(Server) for this SQL Server 2005

but i can buy many medium price hardwares(Servers) for this SQL Server

2005.

how can i distribute or cluster this table between many hardwares(Servers)?

note: i have few users (maximum 5 users) for my database but i have a

very large table and Sql server 2005 server need to respond to this

users in less than 1 second.

i want to distribute this huge table in seperated hardwares. becuase i

can't buy a very expensive hardware from my server but i can buy many

medium price hardware for my server.

note: i need this: when a user run a select query on this huge table

his/her request distribute between many hardwares not one hardware.moving to availablility folder...|||

One option for you is to use peer-to-peer replication as a way of partitioning the data between various servers and having the updates on each server reflected on all others. You'll need some mid-tier logic to direct queries to the appropriate server though.

Checkout http://msdn2.microsoft.com/en-us/library/ms151196.aspx

Thanks

How can i cluster sql 2005 for load balancing?

i have a table with 10,000,000,000 records and i need Select and Insert many
records from or into this table in less than one second.
i can't buy a very expensive hardware(Server) for this SQL Server 2005
but i can buy many medium price hardwares(Servers) for this SQL Server
2005.
how can i distribute or cluster this table between many hardwares(Servers)?
note: i have few users (maximum 5 users) for my database but i have a
very large table and Sql server 2005 server need to respond to this
users in less than 1 second.
i want to distribute this huge table in seperated hardwares. becuase i
can't buy a very expensive hardware from my server but i can buy many
medium price hardware for my server.
note: i need this: when a user run a select query on this huge table
his/her request distribute between many hardwares not one hardware.
Clusters are for high availability (HA) - not load balancing. You should
look at partitioned tables (SQL 2005) or partitioned views (SQL 2000). If
your queries always include the partitioning column in the WHERE clause, you
should be able to realize a performance benefit without necessarily going to
more servers. In the event that partitioned tables, or local partitioned
views, don't do it, then distributed partitioned views -0 where you do use
multiple servers - can be the solution.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"abssoft2000" <abssoft2000@.discussions.microsoft.com> wrote in message
news:0366D5B7-B160-417E-8037-5269258365FF@.microsoft.com...
i have a table with 10,000,000,000 records and i need Select and Insert many
records from or into this table in less than one second.
i can't buy a very expensive hardware(Server) for this SQL Server 2005
but i can buy many medium price hardwares(Servers) for this SQL Server
2005.
how can i distribute or cluster this table between many hardwares(Servers)?
note: i have few users (maximum 5 users) for my database but i have a
very large table and Sql server 2005 server need to respond to this
users in less than 1 second.
i want to distribute this huge table in seperated hardwares. becuase i
can't buy a very expensive hardware from my server but i can buy many
medium price hardware for my server.
note: i need this: when a user run a select query on this huge table
his/her request distribute between many hardwares not one hardware.
|||Table partitioning allows me to spread the load across disks. However, all
data will still be managed by a single server. my performance bottleneck is
not only Disks resources but also CPU or memory, this method can resolve my
Disk Performance bottleneck but it can't resolve my CPU or memory Performance
bottleneck.
please help me to find a solution for all resource include (CPU,Ram,Disk,...).
"Tom Moreau" wrote:

> Clusters are for high availability (HA) - not load balancing. You should
> look at partitioned tables (SQL 2005) or partitioned views (SQL 2000). If
> your queries always include the partitioning column in the WHERE clause, you
> should be able to realize a performance benefit without necessarily going to
> more servers. In the event that partitioned tables, or local partitioned
> views, don't do it, then distributed partitioned views -0 where you do use
> multiple servers - can be the solution.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "abssoft2000" <abssoft2000@.discussions.microsoft.com> wrote in message
> news:0366D5B7-B160-417E-8037-5269258365FF@.microsoft.com...
> i have a table with 10,000,000,000 records and i need Select and Insert many
> records from or into this table in less than one second.
> i can't buy a very expensive hardware(Server) for this SQL Server 2005
> but i can buy many medium price hardwares(Servers) for this SQL Server
> 2005.
> how can i distribute or cluster this table between many hardwares(Servers)?
> note: i have few users (maximum 5 users) for my database but i have a
> very large table and Sql server 2005 server need to respond to this
> users in less than 1 second.
> i want to distribute this huge table in seperated hardwares. becuase i
> can't buy a very expensive hardware from my server but i can buy many
> medium price hardware for my server.
> note: i need this: when a user run a select query on this huge table
> his/her request distribute between many hardwares not one hardware.
>
|||Well, without seeing your entire system, that would be difficult.
If you have a disk I/O bottleneck, it could be that you're memory starved.
It could also be that your RAID array has very few spindles.
What numbers have you been getting from Perf Mon?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"abssoft2000" <abssoft2000@.discussions.microsoft.com> wrote in message
news:D066863B-BAB8-48CE-8D1B-C3BBC451E505@.microsoft.com...
Table partitioning allows me to spread the load across disks. However, all
data will still be managed by a single server. my performance bottleneck is
not only Disks resources but also CPU or memory, this method can resolve my
Disk Performance bottleneck but it can't resolve my CPU or memory
Performance
bottleneck.
please help me to find a solution for all resource include
(CPU,Ram,Disk,...).
"Tom Moreau" wrote:

> Clusters are for high availability (HA) - not load balancing. You should
> look at partitioned tables (SQL 2005) or partitioned views (SQL 2000). If
> your queries always include the partitioning column in the WHERE clause,
> you
> should be able to realize a performance benefit without necessarily going
> to
> more servers. In the event that partitioned tables, or local partitioned
> views, don't do it, then distributed partitioned views -0 where you do use
> multiple servers - can be the solution.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "abssoft2000" <abssoft2000@.discussions.microsoft.com> wrote in message
> news:0366D5B7-B160-417E-8037-5269258365FF@.microsoft.com...
> i have a table with 10,000,000,000 records and i need Select and Insert
> many
> records from or into this table in less than one second.
> i can't buy a very expensive hardware(Server) for this SQL Server 2005
> but i can buy many medium price hardwares(Servers) for this SQL Server
> 2005.
> how can i distribute or cluster this table between many
> hardwares(Servers)?
> note: i have few users (maximum 5 users) for my database but i have a
> very large table and Sql server 2005 server need to respond to this
> users in less than 1 second.
> i want to distribute this huge table in seperated hardwares. becuase i
> can't buy a very expensive hardware from my server but i can buy many
> medium price hardware for my server.
> note: i need this: when a user run a select query on this huge table
> his/her request distribute between many hardwares not one hardware.
>
|||i think it is not difficult. i want to distribute my big table between many
servers(include CPU,Ram,Disks) not one server.
i want to use a method that is is distributable. for example: when my table
being larger i could add some new servers to my system to increase my
performance without changing current servers.
note: i only want increase my performance only by add a complete new servers
(include CPU,Ram,...) to currenct system
"Tom Moreau" wrote:

> Well, without seeing your entire system, that would be difficult.
> If you have a disk I/O bottleneck, it could be that you're memory starved.
> It could also be that your RAID array has very few spindles.
> What numbers have you been getting from Perf Mon?
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "abssoft2000" <abssoft2000@.discussions.microsoft.com> wrote in message
> news:D066863B-BAB8-48CE-8D1B-C3BBC451E505@.microsoft.com...
> Table partitioning allows me to spread the load across disks. However, all
> data will still be managed by a single server. my performance bottleneck is
> not only Disks resources but also CPU or memory, this method can resolve my
> Disk Performance bottleneck but it can't resolve my CPU or memory
> Performance
> bottleneck.
> please help me to find a solution for all resource include
> (CPU,Ram,Disk,...).
> "Tom Moreau" wrote:
>
>
|||As Tom mentioned, look in Books Online for "Distributed Partitioned Views".
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"abssoft2000" <abssoft2000@.discussions.microsoft.com> wrote in message
news:A1D3E5D7-2077-443D-9F6E-D6056A4BCA83@.microsoft.com...[vbcol=seagreen]
>i think it is not difficult. i want to distribute my big table between many
> servers(include CPU,Ram,Disks) not one server.
> i want to use a method that is is distributable. for example: when my
> table
> being larger i could add some new servers to my system to increase my
> performance without changing current servers.
> note: i only want increase my performance only by add a complete new
> servers
> (include CPU,Ram,...) to currenct system
>
> "Tom Moreau" wrote:
|||That's the definition of distributed partitioned views. If you have a copy
of "Advanced Transact-SQL for SQL Server 2000", Chapter 13 gives you details
on how to do it. Using DPV's, Microsoft was able to set a new tpmC
benchmark soon after going RTM with SQL Server 2000.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"abssoft2000" <abssoft2000@.discussions.microsoft.com> wrote in message
news:A1D3E5D7-2077-443D-9F6E-D6056A4BCA83@.microsoft.com...
i think it is not difficult. i want to distribute my big table between many
servers(include CPU,Ram,Disks) not one server.
i want to use a method that is is distributable. for example: when my table
being larger i could add some new servers to my system to increase my
performance without changing current servers.
note: i only want increase my performance only by add a complete new servers
(include CPU,Ram,...) to currenct system
"Tom Moreau" wrote:

> Well, without seeing your entire system, that would be difficult.
> If you have a disk I/O bottleneck, it could be that you're memory starved.
> It could also be that your RAID array has very few spindles.
> What numbers have you been getting from Perf Mon?
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "abssoft2000" <abssoft2000@.discussions.microsoft.com> wrote in message
> news:D066863B-BAB8-48CE-8D1B-C3BBC451E505@.microsoft.com...
> Table partitioning allows me to spread the load across disks. However, all
> data will still be managed by a single server. my performance bottleneck
> is
> not only Disks resources but also CPU or memory, this method can resolve
> my
> Disk Performance bottleneck but it can't resolve my CPU or memory
> Performance
> bottleneck.
> please help me to find a solution for all resource include
> (CPU,Ram,Disk,...).
> "Tom Moreau" wrote:
>
>
|||I think you are not considering the full ramifications of partitioning that
Tom has recommended, even on a single hardware installation.
It is not just a disk resource issue that partitioning attempts to resolve.
Unless you expect each of the 5 users to query the entire 10 Billion rows on
each pass, partitioning will limit the user to the specified data needed at
for any individual pass if the partitioning function is chosen correctly.
I suspect this table is large because the data is time sensitive, that is,
historical?
If so, if you had a dedicated table (or partition) for each month, week,
day, whatever the granularity is, and then one only needed data for that
particular date range, they would then only access a single table
(partition) instead of the entire data set.
If this data represents years of monthly, or daily, data, then each query
would only have to deal with a handful of tables, each only a few hundred
thousand or so records. Each query then becomes 10, 100, maybe even 1,000
or more times quicker.
Sincerely,
Anthony Thomas

"abssoft2000" <abssoft2000@.discussions.microsoft.com> wrote in message
news:A1D3E5D7-2077-443D-9F6E-D6056A4BCA83@.microsoft.com...
> i think it is not difficult. i want to distribute my big table between
many
> servers(include CPU,Ram,Disks) not one server.
> i want to use a method that is is distributable. for example: when my
table
> being larger i could add some new servers to my system to increase my
> performance without changing current servers.
> note: i only want increase my performance only by add a complete new
servers[vbcol=seagreen]
> (include CPU,Ram,...) to currenct system
>
> "Tom Moreau" wrote:
starved.[vbcol=seagreen]
all[vbcol=seagreen]
is[vbcol=seagreen]
my[vbcol=seagreen]
should[vbcol=seagreen]
If[vbcol=seagreen]
clause,[vbcol=seagreen]
going[vbcol=seagreen]
partitioned[vbcol=seagreen]
use[vbcol=seagreen]
Insert[vbcol=seagreen]

How can i cluster sql 2005 for load balancing?

i have a table with 10,000,000,000 records and i need Select and Insert many
records from or into this table in less than one second.
i can't buy a very expensive hardware(Server) for this SQL Server 2005
but i can buy many medium price hardwares(Servers) for this SQL Server
2005.
how can i distribute or cluster this table between many hardwares(Servers)?
note: i have few users (maximum 5 users) for my database but i have a
very large table and Sql server 2005 server need to respond to this
users in less than 1 second.
i want to distribute this huge table in seperated hardwares. becuase i
can't buy a very expensive hardware from my server but i can buy many
medium price hardware for my server.
note: i need this: when a user run a select query on this huge table
his/her request distribute between many hardwares not one hardware.
I would look at distributed partition views for something like this.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"abssoft2000" <abssoft2000@.discussions.microsoft.com> wrote in message
news:C90FB31F-57D4-4E3D-AA02-001133C904F3@.microsoft.com...
>i have a table with 10,000,000,000 records and i need Select and Insert
>many
> records from or into this table in less than one second.
> i can't buy a very expensive hardware(Server) for this SQL Server 2005
> but i can buy many medium price hardwares(Servers) for this SQL Server
> 2005.
> how can i distribute or cluster this table between many
> hardwares(Servers)?
> note: i have few users (maximum 5 users) for my database but i have a
> very large table and Sql server 2005 server need to respond to this
> users in less than 1 second.
> i want to distribute this huge table in seperated hardwares. becuase i
> can't buy a very expensive hardware from my server but i can buy many
> medium price hardware for my server.
> note: i need this: when a user run a select query on this huge table
> his/her request distribute between many hardwares not one hardware.
|||i need a method like partitioned tables in SQL 2005 but Table
partitioning only allows me to spread the load across disks. However,
all data will still be managed by a single server. my performance
bottleneck is not only Disks resources but also CPU or memory, this
method can resolve my Disk Performance bottleneck but it can't resolve
my CPU or memory Performance bottleneck.
please help me to find a solution for all resource include (CPU,Ram,Disk,...).
"abssoft2000" wrote:
|||As I mentioned previously it is called distributed partition views. Look it
up in BOL.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"abssoft2000" <abssoft2000@.discussions.microsoft.com> wrote in message
news:219F0D2E-B2AF-42EB-A6C2-F186956F6341@.microsoft.com...
>i need a method like partitioned tables in SQL 2005 but Table
> partitioning only allows me to spread the load across disks. However,
> all data will still be managed by a single server. my performance
> bottleneck is not only Disks resources but also CPU or memory, this
> method can resolve my Disk Performance bottleneck but it can't resolve
> my CPU or memory Performance bottleneck.
> please help me to find a solution for all resource include
> (CPU,Ram,Disk,...).
> "abssoft2000" wrote:

How can I check for Null or Empty in an Insert/Select Statement - example in Access

The following sample of code in access is what i need to be able to do in
MSSQL 2000.
Can i use iif statements like this in the select part of the insert if so i
cannot get this to work in MSSQL
iif(IsNull(NBCDON.CODE),"9999",NBCDON.Code),
INSERT INTO dbo_ContactAddress (ContactID, AddressTypeCode, AddressLine1,
AddressLine2, AddressLine3, AddressLine4, AddressLine5,AddressLine6,
CountryCode, Town, PostalCode, State, DoNotMarket, DoNotSell )
SELECT NBCDON.ID+100100000, 1,iif(IsNull(NBCDON.Unit), "",NBCDON.Unit+"/") +
NBCDON.Number +iif(IsNull(NBCDON.suf), "",NBCDON.suf)+ " " + NBCDON.Street
," ", " ", " ", " ", " ",1, iif(IsNull(NBCDON.SubTown),
"?",NBCDON.SubTown) ,iif(IsNull(NBCDON.CODE),"9999",NBCDON.Code),
NBCDON.State, 0, 0
FROM NBCDON
WHERE not IsEmpty(NBCDON.Street)
Regards
Jeff
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.554 / Virus Database: 346 - Release Date: 20/12/2003I resolved this by Using the IsNul( field, value if null) Expression.
Regards
Jeff
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.554 / Virus Database: 346 - Release Date: 20/12/2003sql

How can I check for Null or Empty in an Insert/Select Statement

Is there a way in the following Query to check if the _vocon.COMPANY is
null/empty and if so replace _vocon.Id + 10200000 with null (as shown in
statement 2) as each record is processed. I would like to use something like
IIF( IsNull(_vocon.COMPANY), Null, _vocon.Id + 10200000)
INSERT INTO Contact (ContactID, ContactCode, ContactFormat,Title,
FirstName, MiddleName, LastName, notes, CompanyName, Position, CompanyID,
ContactOwner, CreateDate, CreatedBy, LastChangeDate, LastChangedBy,
LanguageCode, Sex, CurrentBalance, Flag19 ) SELECT _vocon.Id + 10100000,
_vocon.Id + 10100000, 'I', _vocon.TITLE, _vocon.FIRSTNAME,
_vocon.OTHERNAMES, _vocon.LASTNAME, NULL, _vocon.COMPANY, NULL, _vocon.Id +
10200000, 1, '01/08/2003', NULL, '01/08/2003', 0, 1, 'U', 0, 1 FROM _vocon
If the company name is null
INSERT INTO Contact (ContactID, ContactCode, ContactFormat,Title,
FirstName, MiddleName, LastName, notes, CompanyName, Position, CompanyID,
ContactOwner, CreateDate, CreatedBy, LastChangeDate, LastChangedBy,
LanguageCode, Sex, CurrentBalance, Flag19 ) SELECT _vocon.Id + 10100000,
_vocon.Id + 10100000, 'I', _vocon.TITLE, _vocon.FIRSTNAME,
_vocon.OTHERNAMES, _vocon.LASTNAME, NULL, _vocon.COMPANY, NULL, NULL, 1,
'01/08/2003', NULL, '01/08/2003', 0, 1, 'U', 0, 1 FROM _vocon
Regards
Jeff
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.554 / Virus Database: 346 - Release Date: 20/12/2003Hi
You don't give the version of SQLServer, it is also better to post DDL
(Create table statements etc...), example data (as Insert statements) to
avoid ambiguities.
The behaviour of concatenating with null can be set with the
CONCAT_NULL_YIELDS_NULL option see:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\tsq
lref.chm::/ts_set-set_2z8s.htm
To update your values you could use:
UPDATE _vocon
SET Company = Id + 10200000
WHERE Company IS NULL
OR LEN(RTRIM(Company)) = 0
John
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:uqveIhNzDHA.2568@.TK2MSFTNGP09.phx.gbl...
> Is there a way in the following Query to check if the _vocon.COMPANY is
> null/empty and if so replace _vocon.Id + 10200000 with null (as shown in
> statement 2) as each record is processed. I would like to use something
like
> IIF( IsNull(_vocon.COMPANY), Null, _vocon.Id + 10200000)
> INSERT INTO Contact (ContactID, ContactCode, ContactFormat,Title,
> FirstName, MiddleName, LastName, notes, CompanyName, Position, CompanyID,
> ContactOwner, CreateDate, CreatedBy, LastChangeDate, LastChangedBy,
> LanguageCode, Sex, CurrentBalance, Flag19 ) SELECT _vocon.Id + 10100000,
> _vocon.Id + 10100000, 'I', _vocon.TITLE, _vocon.FIRSTNAME,
> _vocon.OTHERNAMES, _vocon.LASTNAME, NULL, _vocon.COMPANY, NULL, _vocon.Id
+
> 10200000, 1, '01/08/2003', NULL, '01/08/2003', 0, 1, 'U', 0, 1 FROM
_vocon
> If the company name is null
> INSERT INTO Contact (ContactID, ContactCode, ContactFormat,Title,
> FirstName, MiddleName, LastName, notes, CompanyName, Position, CompanyID,
> ContactOwner, CreateDate, CreatedBy, LastChangeDate, LastChangedBy,
> LanguageCode, Sex, CurrentBalance, Flag19 ) SELECT _vocon.Id + 10100000,
> _vocon.Id + 10100000, 'I', _vocon.TITLE, _vocon.FIRSTNAME,
> _vocon.OTHERNAMES, _vocon.LASTNAME, NULL, _vocon.COMPANY, NULL, NULL, 1,
> '01/08/2003', NULL, '01/08/2003', 0, 1, 'U', 0, 1 FROM _vocon
> Regards
> Jeff
>
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.554 / Virus Database: 346 - Release Date: 20/12/2003
>

how can i check date, hour and minutes only?

I am using this code to insert starttime and endtime.

INSERT INTO working_schedule (id_number, starttime, endtime, created_user, created_pc, created_version, created_domain, created_os, created_workingset) VALUES(@.id_number, @.starttime, @.endtime, @.created_user, @.created_pc, @.created_version, @.created_domain, @.created_os, @.created_workingset)

and this code to check for duplicate before inserting..

IF EXISTS (SELECT id_number, starttime, endtime FROM working_schedule WHERE id_number = @.id_number AND starttime = @.starttime AND endtime = @.endtime)

but it's checking the seconds as well..

how can i only check date, hour and minutes (without the seconds?

If you know that your @.starttime and @.endtime variables NEVER include seconds or milliseconds you can check like this:

AND starttime >= @.starttime
AND starttime < dateadd (mi, 1, @.starttime)
AND endtime >= @.endtime
AND endtime < dateadd (mi, 1, @.endtime)

If your @.starttime and @.endtime variables might include seconds or milliseconds you can do your comparisons like this:

AND starttime >= convert (datetime, convert(varchar(20), @.starttime, 100))
AND starttime < dateadd (mi, 1, convert (datetime, convert(varchar(20), @.starttime, 100)))
AND endtime >= convert (datetime, convert(varchar(20), @.endtime, 100))
AND endtime < dateadd (mi, 1, convert (datetime, convert(varchar(20), @.endtime, 100)))

|||

Use the datediff function:

SELECT id_number, starttime, endtime FROM working_schedule
WHERE id_number = @.id_number
AND DATEDIFF(mi,starttime,@.starttime) = 0
AND DATEDIFF(mi,endtime,@.endtime) = 0

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

How can i bulk insert dashes?

when i do a bulk insert with dashes (i.e. one - two), i get the character u with two dots above it ( ü ) in place of all the dashes? can't seem to figure out why. the field terminator is a comma ( , ).

i'm inserting just text with dashes into char(75) field.

can someone explain and provide a solution? i would greatly appreciate it!

my script is quite simple, maybe i'm missing something.

Code Snippet

bulk insert testtable

from '\\local\c$\adjust.txt'

with (fieldterminator = ',')

thanks!

nevermind, i figured it out... it seems the dashes were not actually dashes, but the symbol that word autocorrects when you type in a dash. seems the strings were copied from a word document and formatted that way.

it works fine now, just replaced all the dash symbols with actual dashes.

How can a SP to handle a "TempDB Full" error ?

I always need to create a temporary table and insert a large amount of
records into it within a stored procedure, my problem is : even I have
already set the growth rate of the TempDB to 100%, but once all the free
TempDB disk space is used up, the stored procedure will fail and terminated
no matter how much free hard disk space outside the TempDB is.
Could anybody tell me is it possible to handle this error within a stored
procedure ?
For example, will the SP generate an error code for such a suitation ? Or
can I add some commands with a SP so that the SP can increase or shrink the
TempDB dynamically ?stuff I can recommend:
1. find out what has caused the tempdb to grow so much? uncommitted
transactions during a long period of time? try to reduce the transaction
size.
2. Alter the tempdb to larger size so that when it gets recreated upon
server restart, it doesn't have to grow as much.
3. don't set autogrowth by 100%. That may take too long to expand and cause
procs to time out.
4. Set up an alert on tempdb size exceeding a threshold. Run dbcc
shrinkdatabase or dbcc shrinkfile to reduce tempdb.
richard
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:c60sfj$ns41@.imsp212.netvigator.com...
> I always need to create a temporary table and insert a large amount of
> records into it within a stored procedure, my problem is : even I have
> already set the growth rate of the TempDB to 100%, but once all the free
> TempDB disk space is used up, the stored procedure will fail and
terminated
> no matter how much free hard disk space outside the TempDB is.
> Could anybody tell me is it possible to handle this error within a stored
> procedure ?
> For example, will the SP generate an error code for such a suitation ? Or
> can I add some commands with a SP so that the SP can increase or shrink
the
> TempDB dynamically ?
>
>

How can a SP to handle a "TempDB Full" error ?

I always need to create a temporary table and insert a large amount of
records into it within a stored procedure, my problem is : even I have
already set the growth rate of the TempDB to 100%, but once all the free
TempDB disk space is used up, the stored procedure will fail and terminated
no matter how much free hard disk space outside the TempDB is.
Could anybody tell me is it possible to handle this error within a stored
procedure ?
For example, will the SP generate an error code for such a suitation ? Or
can I add some commands with a SP so that the SP can increase or shrink the
TempDB dynamically ?
stuff I can recommend:
1. find out what has caused the tempdb to grow so much? uncommitted
transactions during a long period of time? try to reduce the transaction
size.
2. Alter the tempdb to larger size so that when it gets recreated upon
server restart, it doesn't have to grow as much.
3. don't set autogrowth by 100%. That may take too long to expand and cause
procs to time out.
4. Set up an alert on tempdb size exceeding a threshold. Run dbcc
shrinkdatabase or dbcc shrinkfile to reduce tempdb.
richard
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:c60sfj$ns41@.imsp212.netvigator.com...
> I always need to create a temporary table and insert a large amount of
> records into it within a stored procedure, my problem is : even I have
> already set the growth rate of the TempDB to 100%, but once all the free
> TempDB disk space is used up, the stored procedure will fail and
terminated
> no matter how much free hard disk space outside the TempDB is.
> Could anybody tell me is it possible to handle this error within a stored
> procedure ?
> For example, will the SP generate an error code for such a suitation ? Or
> can I add some commands with a SP so that the SP can increase or shrink
the
> TempDB dynamically ?
>
>

How can a SP to handle a "TempDB Full" error ?

I always need to create a temporary table and insert a large amount of
records into it within a stored procedure, my problem is : even I have
already set the growth rate of the TempDB to 100%, but once all the free
TempDB disk space is used up, the stored procedure will fail and terminated
no matter how much free hard disk space outside the TempDB is.
Could anybody tell me is it possible to handle this error within a stored
procedure ?
For example, will the SP generate an error code for such a suitation ? Or
can I add some commands with a SP so that the SP can increase or shrink the
TempDB dynamically ?stuff I can recommend:
1. find out what has caused the tempdb to grow so much? uncommitted
transactions during a long period of time? try to reduce the transaction
size.
2. Alter the tempdb to larger size so that when it gets recreated upon
server restart, it doesn't have to grow as much.
3. don't set autogrowth by 100%. That may take too long to expand and cause
procs to time out.
4. Set up an alert on tempdb size exceeding a threshold. Run dbcc
shrinkdatabase or dbcc shrinkfile to reduce tempdb.
richard
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:c60sfj$ns41@.imsp212.netvigator.com...
> I always need to create a temporary table and insert a large amount of
> records into it within a stored procedure, my problem is : even I have
> already set the growth rate of the TempDB to 100%, but once all the free
> TempDB disk space is used up, the stored procedure will fail and
terminated
> no matter how much free hard disk space outside the TempDB is.
> Could anybody tell me is it possible to handle this error within a stored
> procedure ?
> For example, will the SP generate an error code for such a suitation ? Or
> can I add some commands with a SP so that the SP can increase or shrink
the
> TempDB dynamically ?
>
>

how ca i do that?

how can i insert an image in a database and how can i show the image for
example in an active server page?
Do you have overwhelming and compelling reasons to store the files in the
database, instead of the filesystem?
http://www.aspfaq.com/2149
http://www.aspfaq.com/
(Reverse address to reply.)
"qwerty" <pompeighuII@.yahoo.com> wrote in message
news:eTTXSChYEHA.212@.TK2MSFTNGP12.phx.gbl...
> how can i insert an image in a database and how can i show the image for
> example in an active server page?
>
>

how ca i do that?

how can i insert an image in a database and how can i show the image for
example in an active server page?Do you have overwhelming and compelling reasons to store the files in the
database, instead of the filesystem?
http://www.aspfaq.com/2149
http://www.aspfaq.com/
(Reverse address to reply.)
"qwerty" <pompeighuII@.yahoo.com> wrote in message
news:eTTXSChYEHA.212@.TK2MSFTNGP12.phx.gbl...
> how can i insert an image in a database and how can i show the image for
> example in an active server page?
>
>

how ca i do that?

how can i insert an image in a database and how can i show the image for
example in an active server page?Do you have overwhelming and compelling reasons to store the files in the
database, instead of the filesystem?
http://www.aspfaq.com/2149
--
http://www.aspfaq.com/
(Reverse address to reply.)
"qwerty" <pompeighuII@.yahoo.com> wrote in message
news:eTTXSChYEHA.212@.TK2MSFTNGP12.phx.gbl...
> how can i insert an image in a database and how can i show the image for
> example in an active server page?
>
>

How about cascade insert and update

I am desgining a new database.
Is it suitable to set the cascade insert and cascade update to the foreign
key constrain?
While this can be done on the application level, or through triggers it is
best to do it through cascading update and deletes. Please refer to
http://support.microsoft.com/default...NoWebContent=1
for more information.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:egYkwM1PGHA.648@.TK2MSFTNGP14.phx.gbl...
>I am desgining a new database.
> Is it suitable to set the cascade insert and cascade update to the foreign
> key constrain?
>
|||"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:egYkwM1PGHA.648@.TK2MSFTNGP14.phx.gbl...
>I am desgining a new database.
> Is it suitable to set the cascade insert and cascade update to the foreign
> key constrain?
>
"Cascade insert" doesn't exist. "Cascade delete" is useful, and you should
use it on foreign keys between related entities, but not on foreign keys to
reference or "lookup" tables. "Cascade updates" should almost never be
used, since they only apply where you are updating a primary key, which you
should almost never do.
David

How about cascade insert and update

I am desgining a new database.
Is it suitable to set the cascade insert and cascade update to the foreign
key constrain?While this can be done on the application level, or through triggers it is
best to do it through cascading update and deletes. Please refer to
http://support.microsoft.com/defaul...&NoWebContent=1
for more information.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:egYkwM1PGHA.648@.TK2MSFTNGP14.phx.gbl...
>I am desgining a new database.
> Is it suitable to set the cascade insert and cascade update to the foreign
> key constrain?
>|||"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:egYkwM1PGHA.648@.TK2MSFTNGP14.phx.gbl...
>I am desgining a new database.
> Is it suitable to set the cascade insert and cascade update to the foreign
> key constrain?
>
"Cascade insert" doesn't exist. "Cascade delete" is useful, and you should
use it on foreign keys between related entities, but not on foreign keys to
reference or "lookup" tables. "Cascade updates" should almost never be
used, since they only apply where you are updating a primary key, which you
should almost never do.
David

How about cascade insert and update

I am desgining a new database.
Is it suitable to set the cascade insert and cascade update to the foreign
key constrain?While this can be done on the application level, or through triggers it is
best to do it through cascading update and deletes. Please refer to
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q142/4/80.asp&NoWebContent=1
for more information.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:egYkwM1PGHA.648@.TK2MSFTNGP14.phx.gbl...
>I am desgining a new database.
> Is it suitable to set the cascade insert and cascade update to the foreign
> key constrain?
>|||"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:egYkwM1PGHA.648@.TK2MSFTNGP14.phx.gbl...
>I am desgining a new database.
> Is it suitable to set the cascade insert and cascade update to the foreign
> key constrain?
>
"Cascade insert" doesn't exist. "Cascade delete" is useful, and you should
use it on foreign keys between related entities, but not on foreign keys to
reference or "lookup" tables. "Cascade updates" should almost never be
used, since they only apply where you are updating a primary key, which you
should almost never do.
David

how 2 insert the value from a SP into a tmp table

can any one advice me on how to insert the results of a SP into a temp
table
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!INSERT INTO #tmp (col1, col2, ...)
EXEC procname
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Emil Henrico" <emil@.interres.co.za> wrote in message
news:u%23myVz9HFHA.3332@.TK2MSFTNGP14.phx.gbl...
>
> can any one advice me on how to insert the results of a SP into a temp
> table
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||Something like
[script]
create table #MyTable(column1,column2,...,columnX)
go
insert into #MyTable (column1,column2,...,columnX) exec MyProcedure
[/script]
Cristian Lefter, SQL Server MVP
"Emil Henrico" <emil@.interres.co.za> wrote in message
news:u%23myVz9HFHA.3332@.TK2MSFTNGP14.phx.gbl...
>
> can any one advice me on how to insert the results of a SP into a temp
> table
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||Emil
INSERT INTO #Temp EXEC sp
"Emil Henrico" <emil@.interres.co.za> wrote in message
news:u%23myVz9HFHA.3332@.TK2MSFTNGP14.phx.gbl...
>
> can any one advice me on how to insert the results of a SP into a temp
> table
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||
evry ting
execp the SP returns 10 vals and i only need to use 2 of them...
how do i do that
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Delete the others from the table after the INSERT. Or, a nasty workaround, i
s to call back to the
SQL Server as a linked server using either OPENQUERY or OPENROWSET and do SE
LECT TOP 2 from that
table valued function.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Emil Henrico" <emil@.interres.co.za> wrote in message
news:u46$uU%23HFHA.1476@.TK2MSFTNGP09.phx.gbl...
>
> evry ting
> execp the SP returns 10 vals and i only need to use 2 of them...
> how do i do that
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||let my try and explain better...itonly returns one row..with 10
Columns..i onle need 2 of those ..not all 10...
thisis my question ...
create table #test
( mktcode int, rttotal float,
)
insert into #test (mktcode, rttotal)
exec sp...but the reslut gives me culmun a,b,c,d,e,f,g,
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Well, with INSERT EXEC you get all. How about modifying the stored procedure
, or extracting the
relevant part of the procedure to make another suitable procedure. Or re-wri
te the procedure into a
table valued user defined function?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Emil Henrico" <emil@.interres.co.za> wrote in message
news:exVfFo%23HFHA.4076@.TK2MSFTNGP10.phx.gbl...
> let my try and explain better...itonly returns one row..with 10
> Columns..i onle need 2 of those ..not all 10...
> thisis my question ...
> create table #test
> ( mktcode int, rttotal float,
> )
> insert into #test (mktcode, rttotal)
> exec sp...but the reslut gives me culmun a,b,c,d,e,f,g,
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||Create a temporary Table Variable, say @.Tmp,
Declare @.Tmp Table (
Col1 Varchar(20),
Col2 Varchar(20),
Col3 Varchar(20),
..
Col10 Varchar(20))
Only make the column definitions match the output of the stored proc.
Then Insert @.Tmp Exec SP -- This inserts all ten values into @.Tmp
Then Insert from @.tmp into your real table.
Insert #test (mktcode, rttotal)
Select Col3, Col 7 From @.tmp -- WHichever 2 columns you want
"Emil Henrico" wrote:

> let my try and explain better...itonly returns one row..with 10
> Columns..i onle need 2 of those ..not all 10...
> thisis my question ...
> create table #test
> ( mktcode int, rttotal float,
> )
> insert into #test (mktcode, rttotal)
> exec sp...but the reslut gives me culmun a,b,c,d,e,f,g,
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
>

HostName Function with Access2K front end

In an Insert Into statement I have used the Host_Name() function to
identify which user has suppied a record to a table that holds
temporary data.
I'm using an Access2K front end.

Code:
Alter procedure SPName
@.parameter1 int
AS
Set nocount On
Set xact_abort off

Declare @.myHost nvarchar(50)
Set @.myHost = Host_Name()

Insert Into tblMyNameTEMP(UniqueID, AnyField1, AnyField2,
myMachineName)
SELECT tblMyName.UniqueID, AnyField1, AnyField2, @.myHost
FROM tblMyName
WHERE tblMyName.UniqueID = @.parameter1

I have two problems.
In some cases (and only on one or two of maybe about 250 client
workstations) Host_Name() returns the name of MY machine. I'm thinking
this is because I developed the app and distributed it and for some
reason it's retaining the info contained in my original connection
setup set in the MS-Access Connection window?

Also, on some occasions, I get blocking messages in my trace log
following this operation.

Any help on these two issues is appreciated.
lq"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:47e5bd72.0404100634.3ab98872@.posting.google.c om...
> In an Insert Into statement I have used the Host_Name() function to
> identify which user has suppied a record to a table that holds
> temporary data.
> I'm using an Access2K front end.
> Code:
> Alter procedure SPName
> @.parameter1 int
> AS
> Set nocount On
> Set xact_abort off
> Declare @.myHost nvarchar(50)
> Set @.myHost = Host_Name()
> Insert Into tblMyNameTEMP(UniqueID, AnyField1, AnyField2,
> myMachineName)
> SELECT tblMyName.UniqueID, AnyField1, AnyField2, @.myHost
> FROM tblMyName
> WHERE tblMyName.UniqueID = @.parameter1
> I have two problems.
> In some cases (and only on one or two of maybe about 250 client
> workstations) Host_Name() returns the name of MY machine. I'm thinking
> this is because I developed the app and distributed it and for some
> reason it's retaining the info contained in my original connection
> setup set in the MS-Access Connection window?
> Also, on some occasions, I get blocking messages in my trace log
> following this operation.
> Any help on these two issues is appreciated.
> lq

As a complete guess, there may be some network name resolution issue which
means that the server sometimes resolves workstation names incorrectly. You
should be able to investigate this with your network admin if you can pin it
down to just a couple of machines.

As for the blocking issue, it's hard to say without more information, such
as the text of the errors.

Simon