db time conversion: machine

Showing posts with label machine. Show all posts

Wednesday, March 21, 2012

Queries run slower in 2000 than 7

I created a new SQL Server 2000 SP2 on a 4 processor machine wiht 2 gig of r
am. I moved the database from a SQL Server 7 SP2 box with only 2 processors
and 1 gig of RAM. I inherited a monster report that runs in 30 seconds or
less on the sql 7 box, but
takes a minute and a half on the new box. This report uses a tremendous amo
unt of temp tables and dynamic sql (as I said I inherited it). By using sp_
executesql instead of EXEC for the dynamic sql, i was able to get the report
to run in 55 seconds. How
ever, I am confused as to why it would run so much slower on a much larder b
ox with 2000, especially considering i was the only one on the 2000 box, and
the SQL 7 box has 400 users on it.
Any ideas?Did you remember to update statistics on all tables, preferably with the
FULLSCAN option?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Tammy Moisan" <anonymous@.discussions.microsoft.com> wrote in message
news:6AA5B19E-7050-4FF7-914D-B0772F6E6ADC@.microsoft.com...
I created a new SQL Server 2000 SP2 on a 4 processor machine wiht 2 gig of
ram. I moved the database from a SQL Server 7 SP2 box with only 2
processors and 1 gig of RAM. I inherited a monster report that runs in 30
seconds or less on the sql 7 box, but takes a minute and a half on the new
box. This report uses a tremendous amount of temp tables and dynamic sql
(as I said I inherited it). By using sp_executesql instead of EXEC for the
dynamic sql, i was able to get the report to run in 55 seconds. However, I
am confused as to why it would run so much slower on a much larder box with
2000, especially considering i was the only one on the 2000 box, and the SQL
7 box has 400 users on it.
Any ideas?|||I just ran sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'. it took a
lmost 12 minutes to run and actually rhe query came back at 57 seconds inste
ad of 55. It is the same exact database file as sql 7. i am so confused.
i am supposed to be releasi
ng this server to productiona t the end of the week, but my queries are runn
ing slower. the whole justification for this purchase was to make things fa
ster and now i have no explanation why things are slower. any toehr ideas a
re greatly appreciated.|||Could you please list your hardware as well as where you placed your data
files on each server?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Tammy Moisan" <anonymous@.discussions.microsoft.com> wrote in message
news:04C40E3B-AFD1-4862-8957-CA29F358B3FE@.microsoft.com...
I just ran sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'. it took
almost 12 minutes to run and actually rhe query came back at 57 seconds
instead of 55. It is the same exact database file as sql 7. i am so
confused. i am supposed to be releasing this server to productiona t the
end of the week, but my queries are running slower. the whole justification
for this purchase was to make things faster and now i have no explanation
why things are slower. any toehr ideas are greatly appreciated.|||Can you list your hardware and DB configuration? Especially the disk config.
Eric Li
SQL DBA
MCDBA
Tammy Moisan wrote:

> I just ran sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'. it took almost
12 minutes to run and actually rhe query came back at 57 seconds instead of 55. It
is the same exact database file as sql 7. i am so confused. i am supposed to be re
lea
sing this server to productiona t the end of the week, but my queries are running slower. t
he whole justification for this purchase was to make things faster and now i have no explana
tion why things are slower. any toehr ideas are greatly appreciated.
>|||Here’s the specifications for this server.
Compaq DL580 G2
4x 2800 MHz processors
2GB Ram – SQL is configured to dynamically use all of this except the last
128MB which is saved for the OS.
4GB Page file on C: drive
C: - 34 GB Local Mirror (OS and SQL binn files only)
E: - 34 GB Local Mirror (SQL Log Files Only)
F: - 200 GB SAN RAID 5 (SQL Data Files Only)
I: - 200 GB SAN RAID 5 (SQL Data Files or Application data staging area)
J: - 100 GB SAN RAID 5 (SQL Data Files or Application data staging area)|||Outside of using RAID0+1, instead of RAID 5, I'd expect this to be OK. What
did you have for hardware for your SQL 7 box? Also, for your particular
query, have you tried running off parallelism:
SELECT
*
FROM
MyTable
OPTION (MAXDOP 1)
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Tammy Moisan" <anonymous@.discussions.microsoft.com> wrote in message
news:BA36F91D-17C8-47A3-9E7F-C8DCBCC5F620@.microsoft.com...
Here’s the specifications for this server.
Compaq DL580 G2
4x 2800 MHz processors
2GB Ram – SQL is configured to dynamically use all of this except the last
128MB which is saved for the OS.
4GB Page file on C: drive
C: - 34 GB Local Mirror (OS and SQL binn files only)
E: - 34 GB Local Mirror (SQL Log Files Only)
F: - 200 GB SAN RAID 5 (SQL Data Files Only)
I: - 200 GB SAN RAID 5 (SQL Data Files or Application data staging area)
J: - 100 GB SAN RAID 5 (SQL Data Files or Application data staging area)|||What is your SQL 7 box config.?
Eric Li
SQL DBA
MCDBA
Tammy Moisan wrote:
> Here’s the specifications for this server.
> Compaq DL580 G2
> 4x 2800 MHz processors
> 2GB Ram – SQL is configured to dynamically use all of this except the la
st 128MB which is saved for the OS.
> 4GB Page file on C: drive
> C: - 34 GB Local Mirror (OS and SQL binn files only)
> E: - 34 GB Local Mirror (SQL Log Files Only)
> F: - 200 GB SAN RAID 5 (SQL Data Files Only)
> I: - 200 GB SAN RAID 5 (SQL Data Files or Application data staging area)
> J: - 100 GB SAN RAID 5 (SQL Data Files or Application data staging area)|||I do nto have so much info about this box, except that it is much smaller
2 1.2 ghz Processors
1 GIG Ram
Data files on d:\ with 500MB free
Log FIles on e:swap file on f:\ 250MB
I do not see why this matters, as it is a smaller box|||I just wanted to confirm that fact. Have you tried turning off parallelism
on the disaffected query?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Tammy Moisan" <anonymous@.discussions.microsoft.com> wrote in message
news:A4598E2A-D769-41FB-8FDC-E3FDD1459B2C@.microsoft.com...
I do nto have so much info about this box, except that it is much smaller
2 1.2 ghz Processors
1 GIG Ram
Data files on d:\ with 500MB free
Log FIles on e:swap file on f:\ 250MB
I do not see why this matters, as it is a smaller box

Queries run slower in 2000 than 7

I created a new SQL Server 2000 SP2 on a 4 processor machine wiht 2 gig of ram. I moved the database from a SQL Server 7 SP2 box with only 2 processors and 1 gig of RAM. I inherited a monster report that runs in 30 seconds or less on the sql 7 box, but
takes a minute and a half on the new box. This report uses a tremendous amount of temp tables and dynamic sql (as I said I inherited it). By using sp_executesql instead of EXEC for the dynamic sql, i was able to get the report to run in 55 seconds. How
ever, I am confused as to why it would run so much slower on a much larder box with 2000, especially considering i was the only one on the 2000 box, and the SQL 7 box has 400 users on it.
Any ideas?
Did you remember to update statistics on all tables, preferably with the
FULLSCAN option?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Tammy Moisan" <anonymous@.discussions.microsoft.com> wrote in message
news:6AA5B19E-7050-4FF7-914D-B0772F6E6ADC@.microsoft.com...
I created a new SQL Server 2000 SP2 on a 4 processor machine wiht 2 gig of
ram. I moved the database from a SQL Server 7 SP2 box with only 2
processors and 1 gig of RAM. I inherited a monster report that runs in 30
seconds or less on the sql 7 box, but takes a minute and a half on the new
box. This report uses a tremendous amount of temp tables and dynamic sql
(as I said I inherited it). By using sp_executesql instead of EXEC for the
dynamic sql, i was able to get the report to run in 55 seconds. However, I
am confused as to why it would run so much slower on a much larder box with
2000, especially considering i was the only one on the 2000 box, and the SQL
7 box has 400 users on it.
Any ideas?
|||I just ran sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'. it took almost 12 minutes to run and actually rhe query came back at 57 seconds instead of 55. It is the same exact database file as sql 7. i am so confused. i am supposed to be releasi
ng this server to productiona t the end of the week, but my queries are running slower. the whole justification for this purchase was to make things faster and now i have no explanation why things are slower. any toehr ideas are greatly appreciated.
|||Could you please list your hardware as well as where you placed your data
files on each server?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Tammy Moisan" <anonymous@.discussions.microsoft.com> wrote in message
news:04C40E3B-AFD1-4862-8957-CA29F358B3FE@.microsoft.com...
I just ran sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'. it took
almost 12 minutes to run and actually rhe query came back at 57 seconds
instead of 55. It is the same exact database file as sql 7. i am so
confused. i am supposed to be releasing this server to productiona t the
end of the week, but my queries are running slower. the whole justification
for this purchase was to make things faster and now i have no explanation
why things are slower. any toehr ideas are greatly appreciated.
|||Can you list your hardware and DB configuration? Especially the disk config.
Eric Li
SQL DBA
MCDBA
Tammy Moisan wrote:

> I just ran sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'. it took almost 12 minutes to run and actually rhe query came back at 57 seconds instead of 55. It is the same exact database file as sql 7. i am so confused. i am supposed to be relea
sing this server to productiona t the end of the week, but my queries are running slower. the whole justification for this purchase was to make things faster and now i have no explanation why things are slower. any toehr ideas are greatly appreciated.
>
|||Here’s the specifications for this server.
Compaq DL580 G2
4x 2800 MHz processors
2GB Ram – SQL is configured to dynamically use all of this except the last 128MB which is saved for the OS.
4GB Page file on C: drive
C: - 34 GB Local Mirror (OS and SQL binn files only)
E: - 34 GB Local Mirror (SQL Log Files Only)
F: - 200 GB SAN RAID 5 (SQL Data Files Only)
I: - 200 GB SAN RAID 5 (SQL Data Files or Application data staging area)
J: - 100 GB SAN RAID 5 (SQL Data Files or Application data staging area)
|||Outside of using RAID0+1, instead of RAID 5, I'd expect this to be OK. What
did you have for hardware for your SQL 7 box? Also, for your particular
query, have you tried running off parallelism:
SELECT
*
FROM
MyTable
OPTION (MAXDOP 1)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Tammy Moisan" <anonymous@.discussions.microsoft.com> wrote in message
news:BA36F91D-17C8-47A3-9E7F-C8DCBCC5F620@.microsoft.com...
Here’s the specifications for this server.
Compaq DL580 G2
4x 2800 MHz processors
2GB Ram – SQL is configured to dynamically use all of this except the last
128MB which is saved for the OS.
4GB Page file on C: drive
C: - 34 GB Local Mirror (OS and SQL binn files only)
E: - 34 GB Local Mirror (SQL Log Files Only)
F: - 200 GB SAN RAID 5 (SQL Data Files Only)
I: - 200 GB SAN RAID 5 (SQL Data Files or Application data staging area)
J: - 100 GB SAN RAID 5 (SQL Data Files or Application data staging area)
|||What is your SQL 7 box config.?
Eric Li
SQL DBA
MCDBA
Tammy Moisan wrote:
> Here’s the specifications for this server.
> Compaq DL580 G2
> 4x 2800 MHz processors
> 2GB Ram – SQL is configured to dynamically use all of this except the last 128MB which is saved for the OS.
> 4GB Page file on C: drive
> C: - 34 GB Local Mirror (OS and SQL binn files only)
> E: - 34 GB Local Mirror (SQL Log Files Only)
> F: - 200 GB SAN RAID 5 (SQL Data Files Only)
> I: - 200 GB SAN RAID 5 (SQL Data Files or Application data staging area)
> J: - 100 GB SAN RAID 5 (SQL Data Files or Application data staging area)
|||I do nto have so much info about this box, except that it is much smaller
2 1.2 ghz Processors
1 GIG Ram
Data files on d:\ with 500MB free
Log FIles on e:swap file on f:\ 250MB
I do not see why this matters, as it is a smaller box
|||I just wanted to confirm that fact. Have you tried turning off parallelism
on the disaffected query?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Tammy Moisan" <anonymous@.discussions.microsoft.com> wrote in message
news:A4598E2A-D769-41FB-8FDC-E3FDD1459B2C@.microsoft.com...
I do nto have so much info about this box, except that it is much smaller
2 1.2 ghz Processors
1 GIG Ram
Data files on d:\ with 500MB free
Log FIles on e:swap file on f:\ 250MB
I do not see why this matters, as it is a smaller box

Tuesday, March 20, 2012

Queries are slow when accessed from remote machine

Hi,

I have succesfully created a Stored Procedure which runs under 2 seconds locally.

However when i run the same proc from another machine in the LAN, the response times vary from 5 sec to over 40 Secs and even occassionally times out.

My server is SQL 2005 Dev Edition (32 Bit) running on a Dual Core Box with 2GB memory.

Any Ideas why this would be happening?

Does the query return a lot of data? If so, it is very likely that network latency and bandwidth are the bottleneck, since the results have to be sent over the network back to the client.

How many rows are you returning? You can turn on Client Statistics in SSMS, and see how much data (in bytes) is being returned to the client (assuming you are calling the SP from SSMS on one machine, talking to a remote server).

|||

Thanks for the reply.

It is returning about 400k. But what is interesting is that, even that delay is not consistant ( from 5 sec to over 40sec, when i have run over 100 tests) The other machine is on the same lan with 100Mbps network card. I couldnt also see any significant rise in network utilization in both the machines

|||Did you ever resolve this? It really sounds like a network issue.|||Yes. It turns out that the SSRS was in a web farm scenario. I was checking only one server. duh!!!|||

Hi, me too have the same problem:

I made a migration from SQL2000 to SQL2005. Before migration, both IIS and SQL were on the same server and performance was good.

We decided to split application server (IIS) from db server (SQL). Now the wait time to display page is 5/10 more high.

We made migration of db (backup/restore, detach/attach), rebuilded indexes and compiled store procedure.

Both server are in the same LAN, switched to 1Gb

Some help?

Regards

vito

Queries are slow when accessed from remote machine

Hi,

I have succesfully created a Stored Procedure which runs under 2 seconds locally.

However when i run the same proc from another machine in the LAN, the response times vary from 5 sec to over 40 Secs and even occassionally times out.

My server is SQL 2005 Dev Edition (32 Bit) running on a Dual Core Box with 2GB memory.

Any Ideas why this would be happening?

Does the query return a lot of data? If so, it is very likely that network latency and bandwidth are the bottleneck, since the results have to be sent over the network back to the client.

|||

Thanks for the reply.

|||Did you ever resolve this? It really sounds like a network issue.|||Yes. It turns out that the SSRS was in a web farm scenario. I was checking only one server. duh!!!|||

Hi, me too have the same problem:

I made a migration from SQL2000 to SQL2005. Before migration, both IIS and SQL were on the same server and performance was good.

We decided to split application server (IIS) from db server (SQL). Now the wait time to display page is 5/10 more high.

We made migration of db (backup/restore, detach/attach), rebuilded indexes and compiled store procedure.

Both server are in the same LAN, switched to 1Gb

Some help?

Regards

vito

Saturday, February 25, 2012

q; Linked server from SQL2005 to SQL2000

Hi
I use the following SQL statements to create a link server. RemoteServerName
is an SQL2000 and I am executing this in another machine which is SQL2005.
Though the link server is created successfully, I am not able to se the
tables under it so could not query anything.
--
USE [master]
EXEC master.dbo.sp_addlinkedserver @.server = N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'collation compatible', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'data access', @.optvalue=N'true'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'rpc',@.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'rpc out', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'connect timeout', @.optvalue=N'0'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'collation name', @.optvalue=null
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'query timeout', @.optvalue=N'0'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'use remote collation', @.optvalue=N'true'
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself = N'False',
@.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'Jim
First of all , have you enabled remote connections on SQL Server 2005
server?
I did just testing on my machine and it works fine.
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @.server = N'ServerName',
@.srvproduct=N'SQL Server'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'collation
compatible', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'data
access', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc out',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'connect
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'collation
name', @.optvalue=null
GO
EXEC master.dbo.sp_serveroption @.server=N'NT_SQL_4', @.optname=N'query
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'use remote
collation', @.optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'ServerName',
@.locallogin = NULL , @.useself = N'False', @.rmtuser = N'blb', @.rmtpassword =N'blblbl'
GO
--usage
select * from ServerName.dtabasename.dbo.tablename
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:81C56D0D-3727-46C5-889F-6AB39E88CCFA@.microsoft.com...
> Hi
> I use the following SQL statements to create a link server.
> RemoteServerName
> is an SQL2000 and I am executing this in another machine which is SQL2005.
> Though the link server is created successfully, I am not able to se the
> tables under it so could not query anything.
> --
> USE [master]
> EXEC master.dbo.sp_addlinkedserver @.server => N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'collation compatible', @.optvalue=N'false'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'data access', @.optvalue=N'true'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'rpc',@.optvalue=N'false'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'rpc out', @.optvalue=N'false'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'connect timeout', @.optvalue=N'0'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'collation name', @.optvalue=null
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'query timeout', @.optvalue=N'0'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'use remote collation', @.optvalue=N'true'
> EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname => N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself => N'False',
> @.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'
>|||Hi Uri,
How do I enable remote connection in SQL2000?
"Uri Dimant" wrote:
> Jim
> First of all , have you enabled remote connections on SQL Server 2005
> server?
> I did just testing on my machine and it works fine.
> USE [master]
> GO
> EXEC master.dbo.sp_addlinkedserver @.server = N'ServerName',
> @.srvproduct=N'SQL Server'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'collation
> compatible', @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'data
> access', @.optvalue=N'true'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc',
> @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc out',
> @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'connect
> timeout', @.optvalue=N'0'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'collation
> name', @.optvalue=null
> GO
> EXEC master.dbo.sp_serveroption @.server=N'NT_SQL_4', @.optname=N'query
> timeout', @.optvalue=N'0'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'use remote
> collation', @.optvalue=N'true'
> GO
> USE [master]
> GO
> EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'ServerName',
> @.locallogin = NULL , @.useself = N'False', @.rmtuser = N'blb', @.rmtpassword => N'blblbl'
> GO
>
> --usage
> select * from ServerName.dtabasename.dbo.tablename
>
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:81C56D0D-3727-46C5-889F-6AB39E88CCFA@.microsoft.com...
> >
> > Hi
> > I use the following SQL statements to create a link server.
> > RemoteServerName
> > is an SQL2000 and I am executing this in another machine which is SQL2005.
> >
> > Though the link server is created successfully, I am not able to se the
> > tables under it so could not query anything.
> >
> > --
> >
> > USE [master]
> > EXEC master.dbo.sp_addlinkedserver @.server => > N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'collation compatible', @.optvalue=N'false'
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'data access', @.optvalue=N'true'
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'rpc',@.optvalue=N'false'
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'rpc out', @.optvalue=N'false'
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'connect timeout', @.optvalue=N'0'
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'collation name', @.optvalue=null
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'query timeout', @.optvalue=N'0'
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'use remote collation', @.optvalue=N'true'
> > EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname => > N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself => > N'False',
> > @.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'
> >
>
>|||Jim
Co to Surface Area Configuration for Services and Connections , then
navigate to Remote Connection and check Local and Remote Connections
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:CEE13CAB-BD2D-4307-98B4-E5F223D6E30F@.microsoft.com...
> Hi Uri,
> How do I enable remote connection in SQL2000?
> "Uri Dimant" wrote:
>> Jim
>> First of all , have you enabled remote connections on SQL Server 2005
>> server?
>> I did just testing on my machine and it works fine.
>> USE [master]
>> GO
>> EXEC master.dbo.sp_addlinkedserver @.server = N'ServerName',
>> @.srvproduct=N'SQL Server'
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'ServerName',
>> @.optname=N'collation
>> compatible', @.optvalue=N'false'
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'data
>> access', @.optvalue=N'true'
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc',
>> @.optvalue=N'false'
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc
>> out',
>> @.optvalue=N'false'
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'connect
>> timeout', @.optvalue=N'0'
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'ServerName',
>> @.optname=N'collation
>> name', @.optvalue=null
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'NT_SQL_4', @.optname=N'query
>> timeout', @.optvalue=N'0'
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'use
>> remote
>> collation', @.optvalue=N'true'
>> GO
>> USE [master]
>> GO
>> EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'ServerName',
>> @.locallogin = NULL , @.useself = N'False', @.rmtuser = N'blb', @.rmtpassword
>> =>> N'blblbl'
>> GO
>>
>> --usage
>> select * from ServerName.dtabasename.dbo.tablename
>>
>>
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:81C56D0D-3727-46C5-889F-6AB39E88CCFA@.microsoft.com...
>> >
>> > Hi
>> > I use the following SQL statements to create a link server.
>> > RemoteServerName
>> > is an SQL2000 and I am executing this in another machine which is
>> > SQL2005.
>> >
>> > Though the link server is created successfully, I am not able to se the
>> > tables under it so could not query anything.
>> >
>> > --
>> >
>> > USE [master]
>> > EXEC master.dbo.sp_addlinkedserver @.server =>> > N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'collation compatible', @.optvalue=N'false'
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'data access', @.optvalue=N'true'
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'rpc',@.optvalue=N'false'
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'rpc out', @.optvalue=N'false'
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'connect timeout', @.optvalue=N'0'
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'collation name', @.optvalue=null
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'query timeout', @.optvalue=N'0'
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'use remote collation', @.optvalue=N'true'
>> > EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname =>> > N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself =>> > N'False',
>> > @.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'
>> >
>>

q; Linked server from SQL2005 to SQL2000

Monday, February 20, 2012

Q: run reports in another machine

Hello,
Can I deploy my reports to another machine which does not have a SQL Server
installed and run it from there?
Thanks,
Jim.Yes. You can install RS on a server that has IIS installed. Then point the
new RS installation to use your designated SQL Server.
--
| Thread-Topic: run reports in another machine
| thread-index: AcVwFODsaBcOS7MHRumvBdJ2Zzyw7w==| X-WBNR-Posting-Host: 216.26.230.88
| From: =?Utf-8?B?SklNLkgu?= <JIMH@.discussions.microsoft.com>
| Subject: Q: run reports in another machine
| Date: Mon, 13 Jun 2005 05:39:02 -0700
| Lines: 8
| Message-ID: <AF088332-758D-4D91-AF56-AE22AB606213@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:45811
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hello,
|
| Can I deploy my reports to another machine which does not have a SQL
Server
| installed and run it from there?
|
| Thanks,
| Jim.
|
|

Q: RS on XP

Hello,

I have SQL Server 2000 installed on my Windows XP, can I install Reporting Services to this machine? If yes, where can I get it?

Thanks,

No you can't install SRS Server on an XP machine. You could fiind more info onhttp://www.microsoft.com/sql/reporting/default.mspx
|||

Thank you for the reply. I could not really locate it, can you tell me the exact URL that tells we can not install it to XP. I need to send it to my manager.

Thanks,

|||http://www.microsoft.com/sql/reporting/productinfo/sysreqs.mspx
The above url won't show you how to install SRS it will just show your manager that you can't install the server componets on an XP box. You can only install the development edition as per the URL above.|||

Is there anyway I can download the developer edition on the internet? I think the trial RS version is Enterprise, I have SQL Server license what should I do to get RS Developer if it is not in the internet?
Thanks for your help.

|||

Unfortuneatly I don't know of any site where you can download developer edition of RS. You should be able to install the development part of Enterprise SRS on your XP machine. However you won't be able to host SRS reports, for this you'll need a server OS.

q: reporting service in SQL 2005

Hello,
I installed SQL Server 2005 into a Windows 2005 Pro machine, will I be able
to use Reporting service to deploy the reports here? Is there any web site
that show step by step how I can do that?
Thanks,On Sun, 21 May 2006 04:59:02 -0700, JIM.H.
<JIMH@.discussions.microsoft.com> wrote:

>Hello,
>I installed SQL Server 2005 into a Windows 2005 Pro machine, will I be able
>to use Reporting service to deploy the reports here? Is there any web site
>that show step by step how I can do that?
>Thanks,
Jim,
There is no Windows 2005. I guess you mean Windows XP Pro.
XP Pro is really appropriate only for a development machine for SQL
Server 2005.There are technical and potential licensing issues.
Questions on Reporting Services are better directed to the
microsoft.public.sqlserver.reportingsvcs newsgroup.
There is series of webcasts under way on Reporting Services 2005 at
http://www.microsoft.com/events.
Andrew Watt MVP

q: reporting service in SQL 2005

Hello,
I installed SQL Server 2005 into a Windows 2005 Pro machine, will I be able
to use Reporting service to deploy the reports here? Is there any web site
that show step by step how I can do that?
Thanks,On Sun, 21 May 2006 04:59:02 -0700, JIM.H.
<JIMH@.discussions.microsoft.com> wrote:
>Hello,
>I installed SQL Server 2005 into a Windows 2005 Pro machine, will I be able
>to use Reporting service to deploy the reports here? Is there any web site
>that show step by step how I can do that?
>Thanks,
Jim,
There is no Windows 2005. I guess you mean Windows XP Pro.
XP Pro is really appropriate only for a development machine for SQL
Server 2005.There are technical and potential licensing issues.
Questions on Reporting Services are better directed to the
microsoft.public.sqlserver.reportingsvcs newsgroup.
There is series of webcasts under way on Reporting Services 2005 at
http://www.microsoft.com/events.
Andrew Watt MVP

q: reporting service 2005

Hello,
I installed SQL Server 2005 into a Windows 2000 Pro machine, I have
following question:
1. will I be able to use Reporting services to deploy the reports to the
same machine as a report server?
2. Can I modify and deploy mo old reports written in RS2000
3. Is there any web site that show step by step how I can work with RS2005?
Thanks,On Sun, 21 May 2006 09:36:01 -0700, JIM.H.
<JIMH@.discussions.microsoft.com> wrote:
>Hello,
>I installed SQL Server 2005 into a Windows 2000 Pro machine, I have
>following question:
>1. will I be able to use Reporting services to deploy the reports to the
>same machine as a report server?
Yes, you should be able to do that. Consider performance implications
if the server is anything but lightly loaded.
>2. Can I modify and deploy mo old reports written in RS2000
Yes, but it's a one way process (for most practical purposes). Once
you have opened an RS2000 report in BIDS and deployed it on RS2005 you
won't be able (without some hand editing) to deploy the report at a
future date on an RS2000 server.
If you have a possible need to return/use RS2000 make a backup in the
old format before deploying on RS2005.
>3. Is there any web site that show step by step how I can work with RS2005?
Books Online has a lot of useful information.
Also take a look at http://www.microsoft.com/events. There is a series
of RS2005 webcasts underway.
Andrew Watt MVP
>Thanks,

Wednesday, March 21, 2012

Queries run slower in 2000 than 7

Queries run slower in 2000 than 7

Tuesday, March 20, 2012

Queries are slow when accessed from remote machine

Queries are slow when accessed from remote machine

Saturday, February 25, 2012

q; Linked server from SQL2005 to SQL2000

q; Linked server from SQL2005 to SQL2000

q; Linked server from SQL2005 to SQL2000

Monday, February 20, 2012

Q: run reports in another machine

Q: RS on XP

q: reporting service in SQL 2005

q: reporting service in SQL 2005

q: reporting service 2005

db time conversion

Blog Archive

About Me