Showing posts with label format. Show all posts
Showing posts with label format. Show all posts

Wednesday, March 21, 2012

Queries on linked server are very slow.

This is a multi-part message in MIME format.
--=_NextPart_000_0063_01C35A9F.BBD8E6C0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi All,
We have been using linked servers for the past 3 years, and we never had = any issues with the linked servers.
Now suddenly all the queries against the linkes servers are very very = slow, Some queries takes 4 hours to run before they were only taking 5 = secondsa to run.
I have no idea what is causing this delay.
Can any of you please help,
Thanks
Raju
--=_NextPart_000_0063_01C35A9F.BBD8E6C0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi All,

We have been using linked servers for the past 3 = years, and we never had any issues with the linked servers.

Now suddenly all the queries against the linkes = servers are very very slow, Some queries takes 4 hours to run before they were = only taking 5 secondsa to run.

I have no idea what is causing this = delay.

Can any of you please help,

Thanks
Raju
--=_NextPart_000_0063_01C35A9F.BBD8E6C0--This is a multi-part message in MIME format.
--=_NextPart_000_09CC_01C35B33.D6EE3160
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Raju,
of course you need to look at the obvious changes -- what changes have =been made in your systems and network that may cause this.
On the sql server side, you can run profiler to catch the long running =queries and analyze the results.
More information is needed for the folks here to join your trouble =shooting.
Quentin
"Raju" <npraju1@.hotmail.com> wrote in message =news:uWFSoptWDHA.2040@.TK2MSFTNGP11.phx.gbl...
Hi All,
We have been using linked servers for the past 3 years, and we never =had any issues with the linked servers.
Now suddenly all the queries against the linkes servers are very very =slow, Some queries takes 4 hours to run before they were only taking 5 =secondsa to run.
I have no idea what is causing this delay.
Can any of you please help,
Thanks
Raju
--=_NextPart_000_09CC_01C35B33.D6EE3160
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Raju,
of course you need to look at the =obvious changes -- what changes have been made in your systems and network that may =cause this.
On the sql server side, you can run =profiler to catch the long running queries and analyze the results. =
More information is needed for the =folks here to join your trouble shooting.
Quentin
"Raju" wrote =in message news:uWFSoptWDHA.2040=@.TK2MSFTNGP11.phx.gbl...
Hi All,

We have been using linked servers for the past =3 years, and we never had any issues with the linked servers.

Now suddenly all the queries against the =linkes servers are very very slow, Some queries takes 4 hours to run before they were =only taking 5 secondsa to run.

I have no idea what is causing this =delay.

Can any of you please help,

Thanks
Raju

--=_NextPart_000_09CC_01C35B33.D6EE3160--|||This is a multi-part message in MIME format.
--=_NextPart_000_0135_01C35CC8.907BE880
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I did use sp_who2 active, I don't see any blocks.

I tried linking the same server to different server and ran the query, =it worked fine.

Net work everything looks fine, I am suspecting it has to do with =protocols but I am not sure.

Even if it is a protocol issue have no idea how to troubleshoot and fix =the protocol related problems.


Thanks
Raju
"Quentin Ran" <ab@.who.com> wrote in message =news:ODdoo11WDHA.2424@.TK2MSFTNGP12.phx.gbl...
Raju,
of course you need to look at the obvious changes -- what changes have =been made in your systems and network that may cause this.
On the sql server side, you can run profiler to catch the long running =queries and analyze the results.
More information is needed for the folks here to join your trouble =shooting.
Quentin
"Raju" <npraju1@.hotmail.com> wrote in message =news:uWFSoptWDHA.2040@.TK2MSFTNGP11.phx.gbl...
Hi All,
We have been using linked servers for the past 3 years, and we never =had any issues with the linked servers.
Now suddenly all the queries against the linkes servers are very =very slow, Some queries takes 4 hours to run before they were only =taking 5 secondsa to run.
I have no idea what is causing this delay.
Can any of you please help,
Thanks
Raju
--=_NextPart_000_0135_01C35CC8.907BE880
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

I did use sp_who2 active, =I don't see any blocks.
I tried linking the same =server to different server and ran the query, it worked =fine.
Net work everything looks =fine, I am suspecting it has to do with protocols but I am not =sure.
Even if it is a protocol =issue have no idea how to troubleshoot and fix the protocol related problems.
Thanks
Raju
"Quentin Ran" wrote in message news:ODdoo11WDHA.2424=@.TK2MSFTNGP12.phx.gbl...
Raju,

of course you need to look at the =obvious changes -- what changes have been made in your systems and network that may =cause this.

On the sql server side, you can run =profiler to catch the long running queries and analyze the results. =
More information is needed for the =folks here to join your trouble shooting.

Quentin

"Raju" =wrote in message news:uWFSoptWDHA.2040=@.TK2MSFTNGP11.phx.gbl...
Hi All,

We have been using linked servers for the =past 3 years, and we never had any issues with the linked =servers.

Now suddenly all the queries against the =linkes servers are very very slow, Some queries takes 4 hours to run before =they were only taking 5 secondsa to run.

I have no idea what is causing this delay.

Can any of you please help,

Thanks
Raju

--=_NextPart_000_0135_01C35CC8.907BE880--sql
Posted by chlorinenmqp at 1:51 PM 0 comments
Labels: , , , , , , , , , , , , , , , , , , ,

Monday, February 20, 2012

Q: time convert

Hello
I have string 6:10, 11:30, 23:10, â?¦, in one of column. I need to convert
these string to time with the same format (HH:MM) so that I can sort by time.
How should I do this?
Thanks,
Jim.Jim,
I would try an Instr() function to find the position of the ":" and then
split the hours from the minutes (6) (30) with a mid() function; then sort on
the two values. But I usually find the hard way -- I'm sure there is an
easier way.
:-)
HTH
Jim
"JIM.H." wrote:
> Hello
> I have string 6:10, 11:30, 23:10, â?¦, in one of column. I need to convert
> these string to time with the same format (HH:MM) so that I can sort by time.
> How should I do this?
> Thanks,
> Jim.
>|||If you want to do the conversion in the SQL query, you could try and
use the Convert(datetime, field) function, not sure if it will accept
just the time. If not prefix string, eg
Convert(datetime, '1901/01/01 ' + field)
Then you can do comparisions and extract specific parts (hours, minutes
etc).
Chris
Jim_OLAP wrote:
> Jim,
> I would try an Instr() function to find the position of the ":" and
> then split the hours from the minutes (6) (30) with a mid() function;
> then sort on the two values. But I usually find the hard way -- I'm
> sure there is an easier way.
> :-)
> HTH
> Jim
>
> "JIM.H." wrote:
> > Hello
> > I have string 6:10, 11:30, 23:10, â?¦, in one of column. I need to
> > convert these string to time with the same format (HH:MM) so that I
> > can sort by time. How should I do this?
> > Thanks,
> > Jim.
> >
> >
Posted by chlorinenmqp at 10:59 PM 0 comments
Labels: , , , , , , , , , , , ,

Q: time conversion

Hello
I have string 6:10, 11:30, 23:10, â?¦, in one of column. I need to convert
these string to time with the same format (HH:MM) so that I can sort by time.
How should I do this?
Thanks,
Jim.You may find these articles helpful:
http://www.sommarskog.se/arrays-in-sql.html
http://www.bizdatasolutions.com/tsql/sqlarrays.asp
--
Keith
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:ED6FF7F2-7B23-488A-8881-88339EBD9103@.microsoft.com...
> Hello
> I have string 6:10, 11:30, 23:10, â?¦, in one of column. I need to convert
> these string to time with the same format (HH:MM) so that I can sort by
time.
> How should I do this?
> Thanks,
> Jim.
>|||Some examples:
select * from (
select '6:10' as timecol
union all select '11:30'
union all select '23:10'
union all select '6:03' )
as t1
order by cast(timecol as datetime)
order by case when len(timecol) < 5 then '0' + timecol else timecol end
select cast(timecol as datetime) as newcol from (
select '6:10' as timecol
union all select '11:30'
union all select '23:10'
union all select '6:03' )
as t1
order by newcol
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:ED6FF7F2-7B23-488A-8881-88339EBD9103@.microsoft.com...
> Hello
> I have string 6:10, 11:30, 23:10, ., in one of column. I need to convert
> these string to time with the same format (HH:MM) so that I can sort by
time.
> How should I do this?
> Thanks,
> Jim.
>|||or instead of
order by case when len(timecol) < 5 then '0' + timecol else timecol end
order by right('0' + timecol,5)
"Scott Morris" wrote:
> Some examples:
> select * from (
> select '6:10' as timecol
> union all select '11:30'
> union all select '23:10'
> union all select '6:03' )
> as t1
> order by cast(timecol as datetime)
> order by case when len(timecol) < 5 then '0' + timecol else timecol end
> select cast(timecol as datetime) as newcol from (
> select '6:10' as timecol
> union all select '11:30'
> union all select '23:10'
> union all select '6:03' )
> as t1
> order by newcol
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:ED6FF7F2-7B23-488A-8881-88339EBD9103@.microsoft.com...
> > Hello
> > I have string 6:10, 11:30, 23:10, ., in one of column. I need to convert
> > these string to time with the same format (HH:MM) so that I can sort by
> time.
> > How should I do this?
> > Thanks,
> > Jim.
> >
>
>|||Good one. The left() function came to mind at first but I couldn't think of
a way to use it. I was looking at the wrong end!
"Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
news:29C1C2D5-CB91-4102-85BC-07A7F18146E5@.microsoft.com...
> or instead of
> order by case when len(timecol) < 5 then '0' + timecol else timecol end
> order by right('0' + timecol,5)
>
> "Scott Morris" wrote:
> > Some examples:
> >
> > select * from (
> > select '6:10' as timecol
> > union all select '11:30'
> > union all select '23:10'
> > union all select '6:03' )
> > as t1
> > order by cast(timecol as datetime)
> > order by case when len(timecol) < 5 then '0' + timecol else timecol end
> >
> > select cast(timecol as datetime) as newcol from (
> > select '6:10' as timecol
> > union all select '11:30'
> > union all select '23:10'
> > union all select '6:03' )
> > as t1
> > order by newcol
> >
> >
> > "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> > news:ED6FF7F2-7B23-488A-8881-88339EBD9103@.microsoft.com...
> > > Hello
> > > I have string 6:10, 11:30, 23:10, ., in one of column. I need to
convert
> > > these string to time with the same format (HH:MM) so that I can sort
by
> > time.
> > > How should I do this?
> > > Thanks,
> > > Jim.
> > >
> >
> >
> >
Posted by chlorinenmqp at 10:59 PM 0 comments
Labels: , , , , , , , , , , , , ,
Subscribe to: Posts (Atom)