How can I restrict bandwidth between to SQL servers

Hi
I have two SQL2000 servers in different sites, once a day approximately
1M of data in the form of a large update is required to be transfered
between the 2. We have use of a 2M pipe between the servers but there
is no quality of service, the other users on the pipe are traders so
there must be no interruption in the quality of their bandwidth at any
time.

Is there any way of throttling back the data transfer between the two
servers to restrict its bandwidth use. Obviously we want to retain the
max bandwidth on our local network.
The pipe is administered by a seperate company so we do not have admin
access to their gateways, routers etc.. so a solution which we can
implement on our database servers would be the easiest.
I am not sure if this is the right newsgroup for this but any
information would be great

Thanks
MarkMSSQL itself doesn't have any way to manage this - you would have to
work it out at the OS or network level. From the database side you
could update only a few rows at a time, with pauses in between, but
it's not a great solution and wouldn't be suitable if you need
everything in a transaction. Or perhaps export the data to flat files,
send them in chunks, then reassemble them and do the update on the
server, but again that seems very clumsy.

You might also want to post in a networking group - there may be an
OS-level solution which would do what you need,

Simon

how can I read a table value every minute

Hi
I was wondering if there is any function by which I can read the value in a
date field every minute or so to check to see if it has been updated.
In MS Access I would have done this using the timer function but I wonder if
there is an equivalent in MSDE.
Thanks
June
hi June,
"June Macleod" <junework@.hotmail.com> ha scritto nel messaggio
news:OrxlWq4gEHA.632@.TK2MSFTNGP12.phx.gbl...
> Hi
> I was wondering if there is any function by which I can read the value in
a
> date field every minute or so to check to see if it has been updated.
> In MS Access I would have done this using the timer function but I wonder
if
> there is an equivalent in MSDE.
>
MSDE provides access to the SQL Server Agent, a native scheduler which can
be used to execute maintanance jobs, execute Transact SQL staments, run
stored procedures...
perhaps you can use it... server side... instaead of a client timer...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||June,
Depending on what you are up to, you might find the WAITFOR command useful.
Russell Fields
"June Macleod" <junework@.hotmail.com> wrote in message
news:OrxlWq4gEHA.632@.TK2MSFTNGP12.phx.gbl...
> Hi
> I was wondering if there is any function by which I can read the value in
a
> date field every minute or so to check to see if it has been updated.
> In MS Access I would have done this using the timer function but I wonder
if
> there is an equivalent in MSDE.
> Thanks
> June
>

how can i pass dataset to stored procedure

hi
i have customized dataset. i want to insert into database. how can i insert
on a single round trip. how can i pass dataset to stored procedure'examnotes (sdfsdf@.discussions.microsoft.com) writes:
> i have customized dataset. i want to insert into database. how can i
> insert on a single round trip. how can i pass dataset to stored
> procedure'
Make an XML string of it, and then use OPENXML in SQL Server to unpack it.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||yes but i have to first save the xml file. mine is web application so how ca
n
i serialize file.
is there any other alternative?
"Erland Sommarskog" wrote:

> examnotes (sdfsdf@.discussions.microsoft.com) writes:
> Make an XML string of it, and then use OPENXML in SQL Server to unpack it.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>|||Hi,
What I think Erland was saying to create XML object ( e.g MSXML.DomDocument)
in your application and not file. This XML can be created from dataset, and
while calling SP pass the XML string as parameter ( Varchar lenght is the
constraint here).
Inside SP we can access the Node values from XML string using OpenXML.
Regards,
Vishal Khajuria
"sdfsdf" wrote:
> yes but i have to first save the xml file. mine is web application so how
can
> i serialize file.
> is there any other alternative?
>
> "Erland Sommarskog" wrote:
>|||Vishal Khajuria (Vishal Khajuria@.discussions.microsoft.com) writes:
> What I think Erland was saying to create XML object ( e.g
> MSXML.DomDocument) in your application and not file.
Yes. (Except that I don't really know how you build the XML string.
I only know that it's doable.)

> This XML can be created from dataset, and while calling SP pass the XML
> string as parameter ( Varchar lenght is the constraint here).
Pass the XML string as ntext, and length should not be a problem. (Unless
you exceed the 2GB limit for large objects.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

How can I move a database?

Hi
I am having to move a database from one pc to another and would like to know
the best way to do so making sure that users and logins come across as well.
I am using MSDE 2000 Rel A and am moving it from a Windows 2000 pc to a
Windows XP Prof 2002 SP2 machine.
I have sucessfully installed MSDE on the new pc and can view it through
DBAMgr2k.
I need to know how to create the database and pull across all the
properties.
I tried generating an SQL script for all objects but when I tried to run it
on the XP machine (using DBAMgr2k) I received a "Count field incorrect or
syntax error" message.
Any help would be much appreciated.
Thanks
June
Hello,
Please read the article and go ahead based on the steps defined. This help
you to move all the user databases and system databases to new server.
Since you are moving the system databases you will get the Logins, users,
Jobs,... to the new environment automatically.
http://support.microsoft.com/kb/224071
Thanks
Hari
"June Macleod" <junemacleod@.btconnect.com> wrote in message
news:csKdncKr1KVXoD_YRVnyjQA@.bt.com...
> Hi
> I am having to move a database from one pc to another and would like to
> know
> the best way to do so making sure that users and logins come across as
> well.
> I am using MSDE 2000 Rel A and am moving it from a Windows 2000 pc to a
> Windows XP Prof 2002 SP2 machine.
> I have sucessfully installed MSDE on the new pc and can view it through
> DBAMgr2k.
> I need to know how to create the database and pull across all the
> properties.
> I tried generating an SQL script for all objects but when I tried to run
> it
> on the XP machine (using DBAMgr2k) I received a "Count field incorrect or
> syntax error" message.
> Any help would be much appreciated.
> Thanks
> June
>
|||That worked well.
Many thanks
June
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23g60%23WyMHHA.4992@.TK2MSFTNGP04.phx.gbl...
> Hello,
> Please read the article and go ahead based on the steps defined. This
help[vbcol=seagreen]
> you to move all the user databases and system databases to new server.
> Since you are moving the system databases you will get the Logins, users,
> Jobs,... to the new environment automatically.
> http://support.microsoft.com/kb/224071
> Thanks
> Hari
>
> "June Macleod" <junemacleod@.btconnect.com> wrote in message
> news:csKdncKr1KVXoD_YRVnyjQA@.bt.com...
or
>

How Can I make one Job dependant on the sucess of another?

Hi

I need to create a SQL Server job that is dependant on the sucess of another. I'm not able to put both steps in the same job becuase they need to run at different times of the day.

If my first job over runs or hangs for what ever reason I do not want my second job that starts an hour later to start until the first job finishes OR if my backup hangs or over runs I do not want my first job to start.

You can add multiple schedules to a SQL job but I can't see away of assigning individual stepes to the schedules and don't even know if this would do what I need.

How do other people contorl dpendancies between jobs that need to execute at different times of the day and therefore can't exist int he same job.

Thanks for your help

Marcus

In the first job, you could add a second step that upon competition of the first step enables the second job, and then in that second job add a step disables the job upon successful completition.|||Even on that job step you can state to stop on failure, where the next job setps will not be executed.|||With the OP's requirement that the subsequent 'job' or 'steps' run on a different schedule, it seems that 'stop on failure'/'continue on success' would not really be a solution.|||Or else you can program the same using SP_START_JOB for that specified step name, books online is best resource to check for syntax and examples.|||

I tried this solution but unfortunately it doesn't work. The master job that issues the start job for ALL the others works fine.

the problem is it starts the step, thus starting the job and immediately goes on to the next one. Basically starting them all at once

Does anyone have any other ideas?

I tried the SSIS task that starts jobs but this has the same behaviour.

I'm really suprised that microsoft hasn't figured out that someome may want to schedule jobs that have dependacies.

cheers

Marcus

How Can I make one Job dependant on the sucess of another?

Hi

I need to create a SQL Server job that is dependant on the sucess of another. I'm not able to put both steps in the same job becuase they need to run at different times of the day.

If my first job over runs or hangs for what ever reason I do not want my second job that starts an hour later to start until the first job finishes OR if my backup hangs or over runs I do not want my first job to start.

You can add multiple schedules to a SQL job but I can't see away of assigning individual stepes to the schedules and don't even know if this would do what I need.

How do other people contorl dpendancies between jobs that need to execute at different times of the day and therefore can't exist int he same job.

Thanks for your help

Marcus

In the first job, you could add a second step that upon competition of the first step enables the second job, and then in that second job add a step disables the job upon successful completition.|||Even on that job step you can state to stop on failure, where the next job setps will not be executed.|||With the OP's requirement that the subsequent 'job' or 'steps' run on a different schedule, it seems that 'stop on failure'/'continue on success' would not really be a solution.|||Or else you can program the same using SP_START_JOB for that specified step name, books online is best resource to check for syntax and examples.|||

I tried this solution but unfortunately it doesn't work. The master job that issues the start job for ALL the others works fine.

the problem is it starts the step, thus starting the job and immediately goes on to the next one. Basically starting them all at once

Does anyone have any other ideas?

I tried the SSIS task that starts jobs but this has the same behaviour.

I'm really suprised that microsoft hasn't figured out that someome may want to schedule jobs that have dependacies.

cheers

Marcus

How can I format the Date in the SQL Table using a SQL query

Hi

I have a SQL table that contains date in this format :-

2006-07-02 16:20:01.000
2006-07-02 16:21:00.000
2006-07-02 16:21:01.000
2006-07-02 16:22:00.000
2006-07-02 16:22:02.000
2006-07-02 16:23:00.000

The date above contains seconds that I dont want, how can I remove those seconds so that the output looks like :-

2006-07-02 16:20:00.000
2006-07-02 16:21:00.000
2006-07-02 16:21:00.000
2006-07-02 16:22:00.000
2006-07-02 16:22:00.000
2006-07-02 16:23:00.000

Your help will be highly appreciated.

Hi,

You can do as..

update tablename set datecolumn = select dateadd(s, -datepart(s,datecolumn), datecolumn)

|||

That will change the data. If you only want to change the display, then try this ;

select replace(convert(varchar(20), columnName, 102), '.', '-') + ' ' + left(convert(varchar(20), columnName, 108), 5) + ':00.000'
from tableName

|||

In case you do not want to update the table you just need to change it in display you can do as..

select dateadd(s, -datepart(s,datecolumn), datecolumn)

from tablename

|||

Hi, thanks for the reply

Here is what i have tried

UPDATE [dbo].[Date_Test] SET Date = SELECT DATEADD(s, -DATEPART(s,Date), Date)

and Im getting the following error, i dont understand whats causing it.

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'SELECT'.

Please help.

|||

Rod Colledge wrote:

That will change the data. If you only want to change the display, then try this ;

select replace(convert(varchar(20), columnName, 102), '.', '-') + ' ' + left(convert(varchar(20), columnName, 108), 5) + ':00.000'
from tableName

Thanks, but I want to change the data, not to display it.

|||

Hi,

You need to remove Select Key word..

UPDATE [dbo].[Date_Test] SET Date = DATEADD(s, -DATEPART(s,Date), Date)

|||

Shallu wrote:

Hi,

You need to remove Select Key word..

UPDATE [dbo].[Date_Test] SET Date = DATEADD(s, -DATEPART(s,Date), Date)

Thanks Shallu, it work perfect