Query across multiple databases/SQL Servers

Is it possible to run a single query that pulls data from multiple
tables in multiple databases, perhaps also from multiple SQL Servers?
Any input would be appreciated.
Thanks!
RichardF
Yes. Specify the server and database:
select Select_Column_List
from Server_Name.DB_Name.Object_owner.Table_or_view_nam e
If from another server, you need to set up a linked server. See Linked
Server in Books Online.
"RichardF" <no.one@.no.where.com> wrote in message
news:41acf571.7680223@.msnews.microsoft.com...
> Is it possible to run a single query that pulls data from multiple
> tables in multiple databases, perhaps also from multiple SQL Servers?
> Any input would be appreciated.
> Thanks!
> RichardF
sql

Query across multiple databases/SQL Servers

Is it possible to run a single query that pulls data from multiple
tables in multiple databases, perhaps also from multiple SQL Servers?
Any input would be appreciated.
Thanks!
RichardFYes. Specify the server and database:
select Select_Column_List
from Server_Name.DB_Name.Object_owner.Table_or_view_name
If from another server, you need to set up a linked server. See Linked
Server in Books Online.
"RichardF" <no.one@.no.where.com> wrote in message
news:41acf571.7680223@.msnews.microsoft.com...
> Is it possible to run a single query that pulls data from multiple
> tables in multiple databases, perhaps also from multiple SQL Servers?
> Any input would be appreciated.
> Thanks!
> RichardF

Query across multiple databases/SQL Servers

Is it possible to run a single query that pulls data from multiple
tables in multiple databases, perhaps also from multiple SQL Servers?
Any input would be appreciated.
Thanks!
RichardFYes. Specify the server and database:
select Select_Column_List
from Server_Name.DB_Name.Object_owner.Table_or_view_name
If from another server, you need to set up a linked server. See Linked
Server in Books Online.
"RichardF" <no.one@.no.where.com> wrote in message
news:41acf571.7680223@.msnews.microsoft.com...
> Is it possible to run a single query that pulls data from multiple
> tables in multiple databases, perhaps also from multiple SQL Servers?
> Any input would be appreciated.
> Thanks!
> RichardF

Query 6.5 db

I know that 2005 does not support dbs with 6.5 compatibility level. However,
I can see the db and I can run a query against it. If this is possible and
works great, why can't I view/edit the SPs that are on those dbs? It is
basically the same thing as running a query, depending of course on the
difficulty of the SPs. I realize that not all new commands will work on
those older dbs, but still it would be a major help when trying to convert or
rewrite code to new standards before upgrading. Any thoughts on whether or
not I am missing something or if it is not possible no matter how hard I
would like to have it?
Thanks in advance.
cd
Hi Chris
It sounds like you are not using version control software, as this would not
be an issue if you were!
Have you tried sp_helptext?
John
"ChrisD" wrote:

> I know that 2005 does not support dbs with 6.5 compatibility level. However,
> I can see the db and I can run a query against it. If this is possible and
> works great, why can't I view/edit the SPs that are on those dbs? It is
> basically the same thing as running a query, depending of course on the
> difficulty of the SPs. I realize that not all new commands will work on
> those older dbs, but still it would be a major help when trying to convert or
> rewrite code to new standards before upgrading. Any thoughts on whether or
> not I am missing something or if it is not possible no matter how hard I
> would like to have it?
> Thanks in advance.
> cd
|||Let me clarify a little more. We have a program and database which are stuck
currently on level 6.5. Within the SSMS you can see those databases only has
their name and the classification (6.5 compatibility). You cannot drill down
into them. You can right click and choose new query. With this you can use
T-SQL to go and do anything that you can do through Enterprise Manager on the
same db. You cannot however, do anything with the SPs. You cannot even see
them. The question more clearly is whether or not it is possible to view and
edit Stored Procedures despite the limitations?
I am not writing SPs in 6.5, just needing to view and edit without having to
install 2000 tools on subsequent admin machines.
Thanks...cd
"John Bell" wrote:
[vbcol=seagreen]
> Hi Chris
> It sounds like you are not using version control software, as this would not
> be an issue if you were!
> Have you tried sp_helptext?
> John
> "ChrisD" wrote:
|||Chris
You will need to have a separate machine to do this.
John
"ChrisD" wrote:
[vbcol=seagreen]
> Let me clarify a little more. We have a program and database which are stuck
> currently on level 6.5. Within the SSMS you can see those databases only has
> their name and the classification (6.5 compatibility). You cannot drill down
> into them. You can right click and choose new query. With this you can use
> T-SQL to go and do anything that you can do through Enterprise Manager on the
> same db. You cannot however, do anything with the SPs. You cannot even see
> them. The question more clearly is whether or not it is possible to view and
> edit Stored Procedures despite the limitations?
> I am not writing SPs in 6.5, just needing to view and edit without having to
> install 2000 tools on subsequent admin machines.
> Thanks...cd
> "John Bell" wrote:

Query 6.5 db

I know that 2005 does not support dbs with 6.5 compatibility level. However,
I can see the db and I can run a query against it. If this is possible and
works great, why can't I view/edit the SPs that are on those dbs? It is
basically the same thing as running a query, depending of course on the
difficulty of the SPs. I realize that not all new commands will work on
those older dbs, but still it would be a major help when trying to convert or
rewrite code to new standards before upgrading. Any thoughts on whether or
not I am missing something or if it is not possible no matter how hard I
would like to have it?
Thanks in advance.
cdHi Chris
It sounds like you are not using version control software, as this would not
be an issue if you were!
Have you tried sp_helptext?
John
"ChrisD" wrote:
> I know that 2005 does not support dbs with 6.5 compatibility level. However,
> I can see the db and I can run a query against it. If this is possible and
> works great, why can't I view/edit the SPs that are on those dbs? It is
> basically the same thing as running a query, depending of course on the
> difficulty of the SPs. I realize that not all new commands will work on
> those older dbs, but still it would be a major help when trying to convert or
> rewrite code to new standards before upgrading. Any thoughts on whether or
> not I am missing something or if it is not possible no matter how hard I
> would like to have it?
> Thanks in advance.
> cd|||Let me clarify a little more. We have a program and database which are stuck
currently on level 6.5. Within the SSMS you can see those databases only has
their name and the classification (6.5 compatibility). You cannot drill down
into them. You can right click and choose new query. With this you can use
T-SQL to go and do anything that you can do through Enterprise Manager on the
same db. You cannot however, do anything with the SPs. You cannot even see
them. The question more clearly is whether or not it is possible to view and
edit Stored Procedures despite the limitations?
I am not writing SPs in 6.5, just needing to view and edit without having to
install 2000 tools on subsequent admin machines.
Thanks...cd
"John Bell" wrote:
> Hi Chris
> It sounds like you are not using version control software, as this would not
> be an issue if you were!
> Have you tried sp_helptext?
> John
> "ChrisD" wrote:
> > I know that 2005 does not support dbs with 6.5 compatibility level. However,
> > I can see the db and I can run a query against it. If this is possible and
> > works great, why can't I view/edit the SPs that are on those dbs? It is
> > basically the same thing as running a query, depending of course on the
> > difficulty of the SPs. I realize that not all new commands will work on
> > those older dbs, but still it would be a major help when trying to convert or
> > rewrite code to new standards before upgrading. Any thoughts on whether or
> > not I am missing something or if it is not possible no matter how hard I
> > would like to have it?
> >
> > Thanks in advance.
> >
> > cd|||Chris
You will need to have a separate machine to do this.
John
"ChrisD" wrote:
> Let me clarify a little more. We have a program and database which are stuck
> currently on level 6.5. Within the SSMS you can see those databases only has
> their name and the classification (6.5 compatibility). You cannot drill down
> into them. You can right click and choose new query. With this you can use
> T-SQL to go and do anything that you can do through Enterprise Manager on the
> same db. You cannot however, do anything with the SPs. You cannot even see
> them. The question more clearly is whether or not it is possible to view and
> edit Stored Procedures despite the limitations?
> I am not writing SPs in 6.5, just needing to view and edit without having to
> install 2000 tools on subsequent admin machines.
> Thanks...cd
> "John Bell" wrote:
> > Hi Chris
> >
> > It sounds like you are not using version control software, as this would not
> > be an issue if you were!
> >
> > Have you tried sp_helptext?
> >
> > John
> >
> > "ChrisD" wrote:
> >
> > > I know that 2005 does not support dbs with 6.5 compatibility level. However,
> > > I can see the db and I can run a query against it. If this is possible and
> > > works great, why can't I view/edit the SPs that are on those dbs? It is
> > > basically the same thing as running a query, depending of course on the
> > > difficulty of the SPs. I realize that not all new commands will work on
> > > those older dbs, but still it would be a major help when trying to convert or
> > > rewrite code to new standards before upgrading. Any thoughts on whether or
> > > not I am missing something or if it is not possible no matter how hard I
> > > would like to have it?
> > >
> > > Thanks in advance.
> > >
> > > cd

Query 6.5 db

I know that 2005 does not support dbs with 6.5 compatibility level. However
,
I can see the db and I can run a query against it. If this is possible and
works great, why can't I view/edit the SPs that are on those dbs? It is
basically the same thing as running a query, depending of course on the
difficulty of the SPs. I realize that not all new commands will work on
those older dbs, but still it would be a major help when trying to convert o
r
rewrite code to new standards before upgrading. Any thoughts on whether or
not I am missing something or if it is not possible no matter how hard I
would like to have it?
Thanks in advance.
cdHi Chris
It sounds like you are not using version control software, as this would not
be an issue if you were!
Have you tried sp_helptext?
John
"ChrisD" wrote:

> I know that 2005 does not support dbs with 6.5 compatibility level. Howev
er,
> I can see the db and I can run a query against it. If this is possible an
d
> works great, why can't I view/edit the SPs that are on those dbs? It is
> basically the same thing as running a query, depending of course on the
> difficulty of the SPs. I realize that not all new commands will work on
> those older dbs, but still it would be a major help when trying to convert
or
> rewrite code to new standards before upgrading. Any thoughts on whether o
r
> not I am missing something or if it is not possible no matter how hard I
> would like to have it?
> Thanks in advance.
> cd|||Let me clarify a little more. We have a program and database which are stuc
k
currently on level 6.5. Within the SSMS you can see those databases only ha
s
their name and the classification (6.5 compatibility). You cannot drill dow
n
into them. You can right click and choose new query. With this you can use
T-SQL to go and do anything that you can do through Enterprise Manager on th
e
same db. You cannot however, do anything with the SPs. You cannot even see
them. The question more clearly is whether or not it is possible to view an
d
edit Stored Procedures despite the limitations?
I am not writing SPs in 6.5, just needing to view and edit without having to
install 2000 tools on subsequent admin machines.
Thanks...cd
"John Bell" wrote:
[vbcol=seagreen]
> Hi Chris
> It sounds like you are not using version control software, as this would n
ot
> be an issue if you were!
> Have you tried sp_helptext?
> John
> "ChrisD" wrote:
>|||Chris
You will need to have a separate machine to do this.
John
"ChrisD" wrote:
[vbcol=seagreen]
> Let me clarify a little more. We have a program and database which are st
uck
> currently on level 6.5. Within the SSMS you can see those databases only
has
> their name and the classification (6.5 compatibility). You cannot drill d
own
> into them. You can right click and choose new query. With this you can u
se
> T-SQL to go and do anything that you can do through Enterprise Manager on
the
> same db. You cannot however, do anything with the SPs. You cannot even s
ee
> them. The question more clearly is whether or not it is possible to view
and
> edit Stored Procedures despite the limitations?
> I am not writing SPs in 6.5, just needing to view and edit without having
to
> install 2000 tools on subsequent admin machines.
> Thanks...cd
> "John Bell" wrote:
>sql

Query ... Distinct rows

I have a table as follows
ORDER_ID CODE STATUS
1000 XA3 5
1000 XA1 4
1000 XA7 5
1001 X35 5
1001 XA3 5
I want to run a query that will return the distinct ORDER_ID that is Status
= 5. If any records have Status <> 5, I dont want that ORDER_ID returned.
For example above, the result set will be 1001 only (as 1000 has one record
with Status of 4).
I have tried using 'HAVING MIN(Status) = 5 AND MAX(Status = 5) but it doesnt
appear to work :-(
Thanks in advance!
Wez
On Fri, 17 Jun 2005 03:30:02 -0700, Wez wrote:

> I have a table as follows
>ORDER_ID CODE STATUS
>1000 XA3 5
>1000 XA1 4
>1000 XA7 5
>1001 X35 5
>1001 XA3 5
>I want to run a query that will return the distinct ORDER_ID that is Status
>= 5. If any records have Status <> 5, I dont want that ORDER_ID returned.
>For example above, the result set will be 1001 only (as 1000 has one record
>with Status of 4).
>I have tried using 'HAVING MIN(Status) = 5 AND MAX(Status = 5) but it doesnt
>appear to work :-(
>Thanks in advance!
>Wez
Hi Wez,
This one should work, actually:
SELECT Order_ID
FROM YourTable
GROUP BY Order_ID
HAVING MIN(Status) = 5 AND MAX(Status) = 5
What eexactly does "doesn't appear to work" mean? Error messages? Wrong
results? Blue smoke in the server room? It's hard to help you without
knowing what's happening!
BTW, here's another query that should also work:
SELECT DISTINCT t1.Order_ID
FROM YourTable AS t1
WHERE NOT EXISTS (SELECT *
FROM YourTable AS t2
WHERE t2.Order_ID = t1.Order_ID
AND t2.Status <> 5)
/* Adding the line below might improve performance
AND t1.Status = 5
*/
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi Hugo,
when i say didnt work I mean I was getting wrong results i.e. orders
appearing in the result set that had lines not yet equal to status '5'.
However your alternative method has worked well :-)
Thanks,
Wez

Query

Guys:
I run a query and get a field with 13 characters lenght. Is there any way to get or truncate the lenght of the field in the same scripts??. I need just 10 characters starting from the third one.
ThanksUse the right string function.|||How I can use the right string function??

How I can indicate the lenght and from what position to truncate??

Regards|||Do all the fields have a length of 13 ? Do you always want the 10 characters, starting with the 3rd character - no matter what the length is ?|||Yes, always 13 positions and always I will need 10 positions after third.

Regards,|||select right(fieldname, 10) from ...

Since you know the field is always 13 characters grabbing 10 characters will always start from the 3rd position:

e.g. fieldname = 'abcdefghijklm'
right(fieldname,10) = 'defghijklm'|||I will try it.

Thanks

Query

I have attached the Adventureworks database to SQL Server 2005.

Whenerver I try to run a new query to this database

Ex: select * from sales.customer

I get this error message

Msg 208, Level 16, State 1, Line 1

Invalid object name 'sales.customer'.

I don't know why this is happining?

Hi,

make sure you are deling with the right database (Select db_name()).

Didi you change the default collation ? Seems that you are using a CS Coallation, that means that its case sensity, you can check that by using the query:

SELECT databasepropertyex('Adventureworks','collation')

Select * from Sales.Customer

So I guess, if you *are* connected to the right database, you should be fine using the proper typing of the name (otherwise, if you are feeling not comfortable withthis solution, you could change the collation of the database, by using the statemetn ALTER DATABSE, look in the BOL for more information)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Thanks a lot Jenssql

quering an excel limked server

hi all,
I've configured an excel file as a linked server to my sql 2000 server (win
2003)
I used kb 814398 to do that, and now if I run a query againt the excel file
from the server I do get the results.
the problem occures when I try to run the same query from the query analyzer
on a computer in the network. ofcourse, I connect and query againt my sql
server.
I get the following massage:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'microsoft.jet.oledb.4.0' reported an error.
[OLE/DB provider returned message: 'I:\prices\opelfrontera9698.xls' is not a
valid path. Make sure that the path name is spelled correctly and that you
are connected to the server on which the file resides.]
OLE DB error trace [OLE/DB Provider 'microsoft.jet.oledb.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
again, if I run the same query from the query analyzer on the server, there
is no error massage.
thanks,
elad.Hi
I assume you followed the KB abd restarted the server?
If this is on a mapped network drive, you may want to try specifying a UNC
name instead.
John
"×?×?×¢×? ש×?×?×?" wrote:
> hi all,
> I've configured an excel file as a linked server to my sql 2000 server (win
> 2003)
> I used kb 814398 to do that, and now if I run a query againt the excel file
> from the server I do get the results.
> the problem occures when I try to run the same query from the query analyzer
> on a computer in the network. ofcourse, I connect and query againt my sql
> server.
> I get the following massage:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'microsoft.jet.oledb.4.0' reported an error.
> [OLE/DB provider returned message: 'I:\prices\opelfrontera9698.xls' is not a
> valid path. Make sure that the path name is spelled correctly and that you
> are connected to the server on which the file resides.]
> OLE DB error trace [OLE/DB Provider 'microsoft.jet.oledb.4.0'
> IDBInitialize::Initialize returned 0x80004005: ].
> again, if I run the same query from the query analyzer on the server, there
> is no error massage.
> thanks,
> elad.

quering an excel limked server

hi all,
I've configured an excel file as a linked server to my sql 2000 server (win
2003)
I used kb 814398 to do that, and now if I run a query againt the excel file
from the server I do get the results.
the problem occures when I try to run the same query from the query analyzer
on a computer in the network. ofcourse, I connect and query againt my sql
server.
I get the following massage:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'microsoft.jet.oledb.4.0' reported an error.
[OLE/DB provider returned message: 'I:\prices\opelfrontera9698.xls' is n
ot a
valid path. Make sure that the path name is spelled correctly and that you
are connected to the server on which the file resides.]
OLE DB error trace [OLE/DB Provider 'microsoft.jet.oledb.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
again, if I run the same query from the query analyzer on the server, there
is no error massage.
thanks,
elad.Hi
I assume you followed the KB abd restarted the server?
If this is on a mapped network drive, you may want to try specifying a UNC
name instead.
John
"???? ????" wrote:

> hi all,
> I've configured an excel file as a linked server to my sql 2000 server (wi
n
> 2003)
> I used kb 814398 to do that, and now if I run a query againt the excel fil
e
> from the server I do get the results.
> the problem occures when I try to run the same query from the query analyz
er
> on a computer in the network. ofcourse, I connect and query againt my sql
> server.
> I get the following massage:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'microsoft.jet.oledb.4.0' reported an error.
> [OLE/DB provider returned message: 'I:\prices\opelfrontera9698.xls' is
not a
> valid path. Make sure that the path name is spelled correctly and that yo
u
> are connected to the server on which the file resides.]
> OLE DB error trace [OLE/DB Provider 'microsoft.jet.oledb.4.0'
> IDBInitialize::Initialize returned 0x80004005: ].
> again, if I run the same query from the query analyzer on the server, ther
e
> is no error massage.
> thanks,
> elad.

quering an excel limked server

hi all,
I've configured an excel file as a linked server to my sql 2000 server (win
2003)
I used kb 814398 to do that, and now if I run a query againt the excel file
from the server I do get the results.
the problem occures when I try to run the same query from the query analyzer
on a computer in the network. ofcourse, I connect and query againt my sql
server.
I get the following massage:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'microsoft.jet.oledb.4.0' reported an error.
[OLE/DB provider returned message: 'I:\prices\opelfrontera9698.xls' is not a
valid path. Make sure that the path name is spelled correctly and that you
are connected to the server on which the file resides.]
OLE DB error trace [OLE/DB Provider 'microsoft.jet.oledb.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
again, if I run the same query from the query analyzer on the server, there
is no error massage.
thanks,
elad.
Hi
I assume you followed the KB abd restarted the server?
If this is on a mapped network drive, you may want to try specifying a UNC
name instead.
John
"???? ????" wrote:

> hi all,
> I've configured an excel file as a linked server to my sql 2000 server (win
> 2003)
> I used kb 814398 to do that, and now if I run a query againt the excel file
> from the server I do get the results.
> the problem occures when I try to run the same query from the query analyzer
> on a computer in the network. ofcourse, I connect and query againt my sql
> server.
> I get the following massage:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'microsoft.jet.oledb.4.0' reported an error.
> [OLE/DB provider returned message: 'I:\prices\opelfrontera9698.xls' is not a
> valid path. Make sure that the path name is spelled correctly and that you
> are connected to the server on which the file resides.]
> OLE DB error trace [OLE/DB Provider 'microsoft.jet.oledb.4.0'
> IDBInitialize::Initialize returned 0x80004005: ].
> again, if I run the same query from the query analyzer on the server, there
> is no error massage.
> thanks,
> elad.

queries running very slow on a particular table

I have a web application that hits this database 24/7. I have an orders table
and any query that I run on this table is very slow. A simple select query
also runs very slow. This was working fine until this morning. All the other
tables work fine. I ran dbcc showcontig on then ran dbcc dbreindex. Still no
good. Please advise.Have you looked at what the query plan shows for the query on the orders
table? It could be the statistics need to be updated, however the dbcc
dbreindex should have handled that. You may need to run a profiler to
see if the problem is with the table access or perhaps temp tables.
Shahryar
Beginner wrote:
>I have a web application that hits this database 24/7. I have an orders table
>and any query that I run on this table is very slow. A simple select query
>also runs very slow. This was working fine until this morning. All the other
>tables work fine. I ran dbcc showcontig on then ran dbcc dbreindex. Still no
>good. Please advise.
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohibited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.|||Any blocking going on?
run sp_who2 and look for the BlkBy column
http://sqlservercode.blogspot.com/

queries running very slow on a particular table

I have a web application that hits this database 24/7. I have an orders tabl
e
and any query that I run on this table is very slow. A simple select query
also runs very slow. This was working fine until this morning. All the other
tables work fine. I ran dbcc showcontig on then ran dbcc dbreindex. Still no
good. Please advise.Have you looked at what the query plan shows for the query on the orders
table? It could be the statistics need to be updated, however the dbcc
dbreindex should have handled that. You may need to run a profiler to
see if the problem is with the table access or perhaps temp tables.
Shahryar
Beginner wrote:

>I have a web application that hits this database 24/7. I have an orders tab
le
>and any query that I run on this table is very slow. A simple select query
>also runs very slow. This was working fine until this morning. All the othe
r
>tables work fine. I ran dbcc showcontig on then ran dbcc dbreindex. Still n
o
>good. Please advise.
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is
legally privileged. The information is solely for the use of the intended
recipient(s); any disclosure, copying, distribution, or other use of this in
formation is strictly prohi
bited. If you have received this e-mail in error, please notify the sender
by return e-mail and delete this message. Thank you.|||Any blocking going on?
run sp_who2 and look for the BlkBy column
http://sqlservercode.blogspot.com/sql

queries running very slow on a particular table

I have a web application that hits this database 24/7. I have an orders table
and any query that I run on this table is very slow. A simple select query
also runs very slow. This was working fine until this morning. All the other
tables work fine. I ran dbcc showcontig on then ran dbcc dbreindex. Still no
good. Please advise.
Have you looked at what the query plan shows for the query on the orders
table? It could be the statistics need to be updated, however the dbcc
dbreindex should have handled that. You may need to run a profiler to
see if the problem is with the table access or perhaps temp tables.
Shahryar
Beginner wrote:

>I have a web application that hits this database 24/7. I have an orders table
>and any query that I run on this table is very slow. A simple select query
>also runs very slow. This was working fine until this morning. All the other
>tables work fine. I ran dbcc showcontig on then ran dbcc dbreindex. Still no
>good. Please advise.
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohi
bited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.
|||Any blocking going on?
run sp_who2 and look for the BlkBy column
http://sqlservercode.blogspot.com/

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

Queries run faster with replication then without

Hello everyone,
I have a situation where I am using a Cursor to retrieve records from one table and inserting those records one at a time into another table. When I perform this action without replication, it takes longer to insert the 474 records into this table then it
does with replication. The table that is being inserted to is the table that is being replicated. It doesn't make sense. You would think that using replication would slow down this action. By contrast, if I use a simple INSERT statement without using a c
ursor (ie: Insert table2 select * from table1), the process runs much faster without replication then with replication. Any idea why?
Thanks!!
this is counter intuitive, unless the replication process is bringing the
database pages off disk and into cache thereby resulting in faster reads.
"Nupee" <anonymous@.discussions.microsoft.com> wrote in message
news:C5EDAB30-AB12-46BA-812B-1C0A6EB3562F@.microsoft.com...
> Hello everyone,
> I have a situation where I am using a Cursor to retrieve records from one
table and inserting those records one at a time into another table. When I
perform this action without replication, it takes longer to insert the 474
records into this table then it does with replication. The table that is
being inserted to is the table that is being replicated. It doesn't make
sense. You would think that using replication would slow down this action.
By contrast, if I use a simple INSERT statement without using a cursor (ie:
Insert table2 select * from table1), the process runs much faster without
replication then with replication. Any idea why?
> Thanks!!
|||Is it possible that it has something to do with the system procedures, and triggers that are added to the database through replication configuration? Either that or is there any documentation on the effects of replication of Cursors?
Thanks!!
-- Hilary Cotter wrote: --
this is counter intuitive, unless the replication process is bringing the
database pages off disk and into cache thereby resulting in faster reads.
"Nupee" <anonymous@.discussions.microsoft.com> wrote in message
news:C5EDAB30-AB12-46BA-812B-1C0A6EB3562F@.microsoft.com...[vbcol=seagreen]
> Hello everyone,
table and inserting those records one at a time into another table. When I
perform this action without replication, it takes longer to insert the 474
records into this table then it does with replication. The table that is
being inserted to is the table that is being replicated. It doesn't make
sense. You would think that using replication would slow down this action.
By contrast, if I use a simple INSERT statement without using a cursor (ie:
Insert table2 select * from table1), the process runs much faster without
replication then with replication. Any idea why?[vbcol=seagreen]

Queries run against database

Is it possible to tell on sql server what queries are run
against it?
If a program is built within a company and it links to a
SQL server that I manage. Can I tell what is run against
it?
Thanks,
JeffSure. Use Profiler!
--
Tibor Karaszi
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:0c9f01c3a2e9$f8cab620$a601280a@.phx.gbl...
> Is it possible to tell on sql server what queries are run
> against it?
> If a program is built within a company and it links to a
> SQL server that I manage. Can I tell what is run against
> it?
> Thanks,
> Jeff|||Thanks. I forgot about all those other programs in the
Start Menu.
Jeff
>--Original Message--
>Sure. Use Profiler!
>--
>Tibor Karaszi
>
>"Jeff" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0c9f01c3a2e9$f8cab620$a601280a@.phx.gbl...
>> Is it possible to tell on sql server what queries are
run
>> against it?
>> If a program is built within a company and it links to
a
>> SQL server that I manage. Can I tell what is run
against
>> it?
>> Thanks,
>> Jeff
>
>.
>sql

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