Query Help

Hi i need some help in a Query,
i have a table
Table1 ( field1) with values 1,2,3,4 in the field 1.
when i run,
select field1 from table1
the query returns
1
2
3
4
but instead i need to get these values comma seperated
like 1,2,3,4. i dont want to use cursors for this.
can somebody help me in this.
thanks in advance.
sathyaHi
The only safe way is to use a cursor, alternatively the better method it to
do it on the client or a third party tool such as http://rac4sql.net/
John
"sathya" <softwaremaniac@.hotmail.com> wrote in message
news:060901c38bfc$65979c00$a001280a@.phx.gbl...
> Hi i need some help in a Query,
> i have a table
> Table1 ( field1) with values 1,2,3,4 in the field 1.
> when i run,
> select field1 from table1
> the query returns
> 1
> 2
> 3
> 4
> but instead i need to get these values comma seperated
> like 1,2,3,4. i dont want to use cursors for this.
> can somebody help me in this.
> thanks in advance.
> sathya|||Isn't this something cool:
declare @.List varchar(1000)
select @.List = coalesce(@.List + ',', '') + ltrim(rtrim(col1)) from table1
select @.List
hth
Quentin
"sathya" <softwaremaniac@.hotmail.com> wrote in message
news:060901c38bfc$65979c00$a001280a@.phx.gbl...
> Hi i need some help in a Query,
> i have a table
> Table1 ( field1) with values 1,2,3,4 in the field 1.
> when i run,
> select field1 from table1
> the query returns
> 1
> 2
> 3
> 4
> but instead i need to get these values comma seperated
> like 1,2,3,4. i dont want to use cursors for this.
> can somebody help me in this.
> thanks in advance.
> sathya|||Hi
This solution has been suggested by many people in the past (including
myself) but it is not a safe solution.
John
"Quentin Ran" <ab@.who.com> wrote in message
news:%23AGtH7CjDHA.360@.TK2MSFTNGP10.phx.gbl...
> Isn't this something cool:
> declare @.List varchar(1000)
> select @.List = coalesce(@.List + ',', '') + ltrim(rtrim(col1)) from table1
> select @.List
> hth
> Quentin
>
> "sathya" <softwaremaniac@.hotmail.com> wrote in message
> news:060901c38bfc$65979c00$a001280a@.phx.gbl...
> > Hi i need some help in a Query,
> >
> > i have a table
> >
> > Table1 ( field1) with values 1,2,3,4 in the field 1.
> >
> > when i run,
> >
> > select field1 from table1
> > the query returns
> > 1
> > 2
> > 3
> > 4
> >
> > but instead i need to get these values comma seperated
> > like 1,2,3,4. i dont want to use cursors for this.
> >
> > can somebody help me in this.
> > thanks in advance.
> >
> > sathya
>|||hi john,
What is the problem you are expecting
for me is in a loop, if i use it will slow down the
process.
it works fine for me
thanks
>--Original Message--
>Hi
>This solution has been suggested by many people in the
past (including
>myself) but it is not a safe solution.
>John
>"Quentin Ran" <ab@.who.com> wrote in message
>news:%23AGtH7CjDHA.360@.TK2MSFTNGP10.phx.gbl...
>> Isn't this something cool:
>> declare @.List varchar(1000)
>> select @.List = coalesce(@.List + ',', '') + ltrim(rtrim
(col1)) from table1
>> select @.List
>> hth
>> Quentin
>>
>> "sathya" <softwaremaniac@.hotmail.com> wrote in message
>> news:060901c38bfc$65979c00$a001280a@.phx.gbl...
>> > Hi i need some help in a Query,
>> >
>> > i have a table
>> >
>> > Table1 ( field1) with values 1,2,3,4 in the field 1.
>> >
>> > when i run,
>> >
>> > select field1 from table1
>> > the query returns
>> > 1
>> > 2
>> > 3
>> > 4
>> >
>> > but instead i need to get these values comma
seperated
>> > like 1,2,3,4. i dont want to use cursors for this.
>> >
>> > can somebody help me in this.
>> > thanks in advance.
>> >
>> > sathya
>>
>
>.
>

Query Help

When I run this query it outputs the dates from the 14th of Nov to the 31st (well up to the last date in the db which atm is the 6th of dec) twice for each date because we have two order status values for each day.. Which is what I would like it to do..

However I would like to make it so if a particular row does not have a value <> 0 in any of the Orders fields that row is not displayed hence only displaying days/orderstatuses with actual orders and not days with 0's in all the COUNT(CASE WHEN discountoffercode = '##' THEN 1 END) Fields... I have tried so many things and I just cant figure out how to do this

Code: ( sql )

SELECT ReceivedDate, OrderStatus, COUNT(CASE WHEN discountoffercode = '88' THEN 1 END) AS OrdersCisco, SUM(CASE WHEN discountoffercode = '88' THEN ordertotal END) AS SalesCisco, COUNT(CASE WHEN discountoffercode = '89' THEN 1 END) AS OrdersTDS, SUM(CASE WHEN discountoffercode = '89' THEN ordertotal END) AS SalesTDS, COUNT(CASE WHEN discountoffercode = '90' THEN 1 END) AS OrdersBerbeeCDW, SUM(CASE WHEN discountoffercode = '90' THEN ordertotal END) AS SalesBerbeeCDW, COUNT(CASE WHEN discountoffercode = '91' THEN 1 END) AS OrdersQuadGfx, SUM(CASE WHEN discountoffercode = '91' THEN ordertotal END) AS SalesQuadGfx, COUNT(CASE WHEN discountoffercode = '92' THEN 1 END) AS OrdersATT, SUM(CASE WHEN discountoffercode = '92' THEN ordertotal END) AS SalesATT, COUNT(CASE WHEN discountoffercode = '93' THEN 1 END) AS OrdersGlobalCrossing, SUM(CASE WHEN discountoffercode = '93' THEN ordertotal END) AS SalesGlobalCrossing, COUNT(CASE WHEN discountoffercode = '94' THEN 1 END) AS OrdersPerformics, SUM(CASE WHEN discountoffercode = '94' THEN ordertotal END) AS SalesPerformics, COUNT(CASE WHEN discountoffercode = 'AA' THEN 1 END) AS OrdersOzburnHessey, SUM(CASE WHEN discountoffercode = 'AA' THEN ordertotal END) AS SalesOzburnHessey, COUNT(CASE WHEN discountoffercode = 'AB' THEN 1 END) AS OrdersFry, SUM(CASE WHEN discountoffercode = 'AB' THEN ordertotal END) AS SalesFry, COUNT(CASE WHEN discountoffercode = 'AC' THEN 1 END) AS OrdersEMC, SUM(CASE WHEN discountoffercode = 'AC' THEN ordertotal END) AS SalesEMC, COUNT(CASE WHEN discountoffercode = 'AD' THEN 1 END) AS OrdersGlasshouse, SUM(CASE WHEN discountoffercode = 'AD' THEN ordertotal END) AS SalesGlasshouse, COUNT(CASE WHEN discountoffercode = 'AE' THEN 1 END) AS OrdersSecureWorks, SUM(CASE WHEN discountoffercode = 'AE' THEN ordertotal END) AS SalesSecureWorks, COUNT(CASE WHEN discountoffercode = 'AF' THEN 1 END) AS OrdersPDS, SUM(CASE WHEN discountoffercode = 'AF' THEN ordertotal END) AS SalesPDS, COUNT(CASE WHEN discountoffercode = 'AG' THEN 1 END) AS OrdersMenashaPkg, SUM(CASE WHEN discountoffercode = 'AG' THEN ordertotal END) AS SalesMenashaPkg, COUNT(CASE WHEN discountoffercode = 'A2' THEN 1 END) AS OrdersBelmark, SUM(CASE WHEN discountoffercode = 'A2' THEN ordertotal END) AS SalesBelmark, COUNT(CASE WHEN discountoffercode = 'A3' THEN 1 END) AS OrdersPlasticIngeniuty, SUM(CASE WHEN discountoffercode = 'A3' THEN ordertotal END) AS SalesPlasticIngenuity, COUNT(CASE WHEN discountoffercode = 'A4' THEN 1 END) AS OrdersUFP, SUM(CASE WHEN discountoffercode = 'A4' THEN ordertotal END) AS SalesUFP, COUNT(CASE WHEN discountoffercode = 'A5' THEN 1 END) AS OrdersStyrene, SUM(CASE WHEN discountoffercode = 'A5' THEN ordertotal END) AS SalesStyreneFROM dbo.OrdersWHERE (ReceivedDate BETWEEN '20071114' AND '20071231')GROUP BY ReceivedDate, OrderStatus

Thanks for the edit :P

um anyone able to help me on this? It is kind of high priority and as i said i can't seem to figure it outsql

query hangs

Hi,
Have a query that runs okay in SQL 2000 but when I try to run the same query
in 2005 it hans the server.
any suggestions
thxWhat is the nature of the query?
What are the differences in hardware?
Are you using the same network connection?
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
Make SQL Server faster - www.quicksqlserver.com
___________________________________
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:FB83E362-FA12-471C-AA36-52F1C0DDD3FA@.microsoft.com...
> Hi,
> Have a query that runs okay in SQL 2000 but when I try to run the same
query
> in 2005 it hans the server.
> any suggestions
> thx|||stoney wrote:
> Hi,
> Have a query that runs okay in SQL 2000 but when I try to run the same que
ry
> in 2005 it hans the server.
> any suggestions
> thx
Begin by comparing the Estimated Execution Plan for the query in 2000
vs. the plan in 2005. Are they different? Assuming the database in
2005 was migrated from 2000, did you update statistics after the migration?
When the query hangs, check the master..sysprocesses table, does
anything show as being blocked?
Tracy McKibben
MCDBA
http://www.realsqlguy.comsql

query hangs

Hi,
Have a query that runs okay in SQL 2000 but when I try to run the same query
in 2005 it hans the server.
any suggestions
thxWhat is the nature of the query?
What are the differences in hardware?
Are you using the same network connection?
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
Make SQL Server faster - www.quicksqlserver.com
___________________________________
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:FB83E362-FA12-471C-AA36-52F1C0DDD3FA@.microsoft.com...
> Hi,
> Have a query that runs okay in SQL 2000 but when I try to run the same
query
> in 2005 it hans the server.
> any suggestions
> thx|||stoney wrote:
> Hi,
> Have a query that runs okay in SQL 2000 but when I try to run the same query
> in 2005 it hans the server.
> any suggestions
> thx
Begin by comparing the Estimated Execution Plan for the query in 2000
vs. the plan in 2005. Are they different? Assuming the database in
2005 was migrated from 2000, did you update statistics after the migration?
When the query hangs, check the master..sysprocesses table, does
anything show as being blocked?
Tracy McKibben
MCDBA
http://www.realsqlguy.com

query governor for users?

How to make only some users not to run long running blocking queries ?
TIAHi
You could SET DEADLOCK_PRIORITY to low and a QUERY_GOVERNOR_COST_LIMIT,
setting ROWCOUNT and a LOCK_TIMEOUT may also be options.
John
"DallasBlue" wrote:

> How to make only some users not to run long running blocking queries ?
> TIA
>sql

query governor for users?

How to make only some users not to run long running blocking queries ?
TIAHi
You could SET DEADLOCK_PRIORITY to low and a QUERY_GOVERNOR_COST_LIMIT,
setting ROWCOUNT and a LOCK_TIMEOUT may also be options.
John
"DallasBlue" wrote:
> How to make only some users not to run long running blocking queries ?
> TIA
>

Query Governor

Anybody out there have any luck (good or bad) using the server-level Query
Governor? We've tried to implement it, but when we run queries (primarily
selects) to test it, it doesn't seem to engage. Is there a source of better
info than BOL on what Query Governor actually does?
The "query governor cost limit" option allows you to limit the maximum length
a query can run on a server, and is one of the few SQL Server configuration
options that I endorse. For example, let's say that some of the users of your
server like to run very long-running queries that really hurt the performance
of your server. By setting this option, you could prevent them from running
any queries that exceeded, say 300 seconds (or whatever number you pick). The
default value for this setting is "0", which means that there are no limits
to how long a query can run.
The value you set for this option is approximate, and is based on how long
the Query Optimizer estimates the query will run. If the estimate is more
than the time you have specified, the query won't run at all, producing an
error instead. This can save a lot of valuable server resources.
On the other hand, users can get real unhappy with you if they can't run the
queries then have to run in order to do their job. What you might consider
doing is helping those users to write more efficient queries. That way,
everyone will be happy.
If this setting is set to "0", consider adding a value here and see what
happens. Just don't make it too small. You might consider starting with value
of about 600 seconds and see what happens. If that is OK, then try 500
seconds, and so on, until you find out when users start complaining.
"Tim Brown, DAC DBA" wrote:

> Anybody out there have any luck (good or bad) using the server-level Query
> Governor? We've tried to implement it, but when we run queries (primarily
> selects) to test it, it doesn't seem to engage. Is there a source of better
> info than BOL on what Query Governor actually does?
|||Another thing to know is that the governer makes the go/nogo decision after
the query has been optimized and before the query is run... It is possible
that the optimizers cost estimate is incorrect. The governer will NOT stop a
query that actually runs longer than the limit...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<Tim Brown>; "DAC DBA" <Tim Brown, DAC DBA@.discussions.microsoft.com> wrote
in message news:7855542F-3805-45B2-96C2-11B386C580C9@.microsoft.com...
> Anybody out there have any luck (good or bad) using the server-level Query
> Governor? We've tried to implement it, but when we run queries (primarily
> selects) to test it, it doesn't seem to engage. Is there a source of
better
> info than BOL on what Query Governor actually does?

Query Governor

Anybody out there have any luck (good or bad) using the server-level Query
Governor? We've tried to implement it, but when we run queries (primarily
selects) to test it, it doesn't seem to engage. Is there a source of better
info than BOL on what Query Governor actually does?The "query governor cost limit" option allows you to limit the maximum lengt
h
a query can run on a server, and is one of the few SQL Server configuration
options that I endorse. For example, let's say that some of the users of you
r
server like to run very long-running queries that really hurt the performanc
e
of your server. By setting this option, you could prevent them from running
any queries that exceeded, say 300 seconds (or whatever number you pick). Th
e
default value for this setting is "0", which means that there are no limits
to how long a query can run.
The value you set for this option is approximate, and is based on how long
the Query Optimizer estimates the query will run. If the estimate is more
than the time you have specified, the query won't run at all, producing an
error instead. This can save a lot of valuable server resources.
On the other hand, users can get real unhappy with you if they can't run the
queries then have to run in order to do their job. What you might consider
doing is helping those users to write more efficient queries. That way,
everyone will be happy.
If this setting is set to "0", consider adding a value here and see what
happens. Just don't make it too small. You might consider starting with valu
e
of about 600 seconds and see what happens. If that is OK, then try 500
seconds, and so on, until you find out when users start complaining.
"Tim Brown, DAC DBA" wrote:

> Anybody out there have any luck (good or bad) using the server-level Query
> Governor? We've tried to implement it, but when we run queries (primarily
> selects) to test it, it doesn't seem to engage. Is there a source of bett
er
> info than BOL on what Query Governor actually does?|||Another thing to know is that the governer makes the go/nogo decision after
the query has been optimized and before the query is run... It is possible
that the optimizers cost estimate is incorrect. The governer will NOT stop a
query that actually runs longer than the limit...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<Tim Brown>; "DAC DBA" <Tim Brown, DAC DBA@.discussions.microsoft.com> wrote
in message news:7855542F-3805-45B2-96C2-11B386C580C9@.microsoft.com...
> Anybody out there have any luck (good or bad) using the server-level Query
> Governor? We've tried to implement it, but when we run queries (primarily
> selects) to test it, it doesn't seem to engage. Is there a source of
better
> info than BOL on what Query Governor actually does?

Query generates failures: Error 3624, Error 5180, or Error 823 when run without MAXDOP 1,

Posting the details of this situation so that others (including Microsoft)
are aware...
We had a simple query such as this on a multi-processor SQL Server:
SELECT count(*) FROM [Northwind].[dbo].[Orders] WHERE ShipRegion IS NULL
That would generate various severe errors (3624, 5180, 823), some of which
at first appeared to be hardware related. The errors were not generated if
the query were run with OPTION (MAXDOP 1). In the end a DBCC REINDEX of a
particular index on the table involved in the query fixed the problem,
although DBCC checks never indicated any problem with indexes.
Symptoms:
--
1. A stack dump indicating a failed assertion in file recbase.cpp
2. One of the following before the stack dump:
A. Error 3624 (retail assertion) of severity 20 (fatal error in current
process) that has follow-up message indicating the cause of the error, i.e.,
just "Error: 3624, Severity: 20, State: 1".
B. Error 5180 (could not open FCB for invalid file) of severity 22 (fatal
error, table integrity suspect) such as the following: "Error: 5180,
Severity: 22, State: 1 <next log entry> Could not open FCB for invalid file
ID 768 in database 'Northwind' ".
C. Error 823 (I/O error) of severity 24 (hardware error) such as the
following: "Error: 823, Severity: 24, State: 2 <new log entry> I/O error
38(Reached the end of the file.) detected during read at offset
0x00002000600000 in file 'C:\Program Files\Microsoft SQL
Server\MSSQL\Northwind.MDF' ".
3. The offending command that generated the initial failed assertion
continues to throw one of three errors listed above on subsequent
executions.
4. There are no hardware-related events in the Windows/NT Event Log.
5. The offending command uses parallelism.
6. When the offending command is run with OPTION (MAXDOP 1) to force serial
execution, it does not throw an error.
7. The following commands do not detect any integrity errors: DBCC
CHECKDB, DBCC CHECKCATALOG, DBCC CHECKTABLE (table only or table plus index
arguments), and DBCC CHECKFILEGROUP.
Investigation Notes:
--
1. Issue came up with the following statement sql statement
SELECT count(*) FROM [Northwind].[dbo].[Orders] WHERE ShipRegion IS NULL
2. Adding OPTION (MAXDOP 1) brought back results
3. Adding WITH (NOLOCK) had no effect.
4. Individual column names were substituted for the * in the SELECT clause.
The results were mixed: some executions brought back results and others
returned one of the errors listed in the Symptoms above. Checking the
execution plans for all the variants using the MAXDOP option (so that the
actual plan could be retrieved, not just the planned one) revealed that the
failing ones all used a particular nonclustered index while the successful
ones used the clustered index.
5. When running the DBCC CHECK commands listed in the symptoms to try to
rectify the issue, the filegroup and indid of the problem index where used
where possible (in addition to the more generic executions with just a table
name). No DBCC commands revealed any integrity errors, though, regardless
of whether they used the table name or the table name plus the
filegroup/index.
Resolution:
--
DBCC REINDEX of the problematic index resolved the issue without
serialization of the query plan (MAXDOP 1). Once working, the actual
execution plan was checked to ensure it used parallelism; it did.
Subsequent tests with OPTION (MAXDOP 1) continued to work, as did the
inclusion/omission of locking hints like (NOLOCK).Hi Frank,
Thans for sharing your experience with MSDN Newsgroup!
Sincerely yours,
Mingqing Cheng
Microsoft Online Support
---
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

query from mssql to sybase sucks

Hi,

I got a big problem. I try to run a query on ms query analyzer or my .net-app, but in both cause, it wouldn't work. when i run the query in ms query analyzer it returns me this error-message:

Der aktuelle Zeilenwert der [verkauf_online]..[hs].[std_ftext].text-Spalte konnte nicht vom OLE DB-Provider 'MSDASQL' gelesen werden.
[OLE/DB provider returned message: Die angeforderte Konvertierung wird nicht untersttzt.]
OLE DB-Fehlertrace [OLE/DB Provider 'MSDASQL' IRowset::GetData returned 0x80040e1d].

sorry, its in german, i know but the most important fact is, that a conversion failed. i tried to read a text-field from a sybase asa-8-database which is linked with a ms sql-server 2000. I tried to find out more about the error-messe an the error-code (x80040e1d) but i couldn't find anything helpful.

Now, I just hope some of you can maybe help me.What I would probably do to avoid such conversion errors would be to make a view in Sybase which has all columns as varchar( or equivalent in sybase) and then query this view.|||:S thanks very much, but...

...I am not the dba from the sybase db, just the guy who should extract some data from it.

Another way is a odbc-connection in vs.net but the i got 2 db-connections, also 2 times more to do, 2 times more problems, etc. you know what i mean...

Carsten

Query From Excel

SELECT * FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\test.xls', [Sheet1$])
When I run this query, I get this response:
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' does not contain table 'Sheet1$'.
The table either does not exist or the current user does not have permission
s
on that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain
the table: ProviderName='Microsoft.Jet.OLEDB.4.0', TableName='Sheet1$'].
Any idea why?David
The error message is pretty clear. Do you have a [Sheet1$]) ? Do you have
permissions to access the file?
"David Samson" <CaptainSlock@.nospam.nospam> wrote in message
news:9FF48606-8332-461E-9F23-662633D592F2@.microsoft.com...
> SELECT * FROM
> OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;Database=c:\test.xls', [Sheet1$])
> When I run this query, I get this response:
> Server: Msg 7314, Level 16, State 1, Line 1
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' does not contain table
> 'Sheet1$'.
> The table either does not exist or the current user does not have
> permissions
> on that table.
> OLE DB error trace [Non-interface error: OLE DB provider does not contain
> the table: ProviderName='Microsoft.Jet.OLEDB.4.0', TableName='Sheet1$'].
> Any idea why?|||Hi CaptainSlock,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you are executing the OPENROWSET but
failed to get the error.
I create a new Excel xls file and input some data on Sheet1, executing the
same query from Query Analyzer, I could get the correct result set as
expected. So would you please help me check the following settings?
1. Please confirm your Excel file contain a Sheet named "Sheet1" (Defautly,
a new Excel will have this sheet)
2. Please confirm you are using local SQL Server instance or the excel file
is located on the local disk
3. What account you are using have sufficient permission on this Excel file.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Yes, I have a C:\Test.xls with 3 columns, 10 rows, and one tab named Sheet1.
I am logged in with an ID that has Admin access to the PC that I am running
directly from. The sql server is on a separate box - Windows Server 2003,
SQL Server 2000 Enterprise.
"Michael Cheng [MSFT]" wrote:

> Hi CaptainSlock,
> Welcome to use MSDN Managed Newsgroup!
> From your descriptions, I understood you are executing the OPENROWSET but
> failed to get the error.
> I create a new Excel xls file and input some data on Sheet1, executing the
> same query from Query Analyzer, I could get the correct result set as
> expected. So would you please help me check the following settings?
> 1. Please confirm your Excel file contain a Sheet named "Sheet1" (Defautly
,
> a new Excel will have this sheet)
> 2. Please confirm you are using local SQL Server instance or the excel fil
e
> is located on the local disk
> 3. What account you are using have sufficient permission on this Excel fil
e.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||I have a C:\Test.xls with 3 columns, 10 rows, and one tab named Sheet1. I a
m
logged in with an ID that has Admin access to the PC that I am running
directly from. The sql server is on a separate box - Windows Server 2003,
SQL Server 2000 Enterprise.
"Uri Dimant" wrote:

> David
> The error message is pretty clear. Do you have a [Sheet1$]) ? Do you have
> permissions to access the file?
>
> "David Samson" <CaptainSlock@.nospam.nospam> wrote in message
> news:9FF48606-8332-461E-9F23-662633D592F2@.microsoft.com...
>
>|||Well, try the following query
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\test.xls;Extended Properties=Excel 8.0')...Sheet1$
Also take a look at Q306397 HOWTO: Use Excel w/ SQL Linked Servers &
Distributed Queries
http://support.microsoft.com/suppor...s/q306/3/97.asp
"David Samson" <CaptainSlock@.nospam.nospam> wrote in message
news:1212A046-58F7-4B00-8EFE-1984FD9F4814@.microsoft.com...
>I have a C:\Test.xls with 3 columns, 10 rows, and one tab named Sheet1. I
>am
> logged in with an ID that has Admin access to the PC that I am running
> directly from. The sql server is on a separate box - Windows Server 2003,
> SQL Server 2000 Enterprise.
> "Uri Dimant" wrote:
>|||Hi
You could try opening the file in DTS to see what sheet names you have, this
will also prove you have the correct access permissions or alternatively
create a new file and paste the data into that then there definately be a
sheet1.
Martin
"David Samson" wrote:
> Yes, I have a C:\Test.xls with 3 columns, 10 rows, and one tab named Sheet
1.
> I am logged in with an ID that has Admin access to the PC that I am runnin
g
> directly from. The sql server is on a separate box - Windows Server 2003,
> SQL Server 2000 Enterprise.
> "Michael Cheng [MSFT]" wrote:
>|||Dear all,
I've trying a very similar query and I obtain the following error from the
Sql Server 2005 CTP(June):
Msg 15501, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of
component 'Ad Hoc Distributed Queries' because this component is turned off
as part of the security configuration for this server. A system administrato
r
can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. Fo
r
more information about enabling 'Ad Hoc Distributed Queries', see "Surface
Area Configuration" in SQL Server Books Online.
From my sql server 2000 sp3a:
Servidor: mensaje 7314, nivel 16, estado 1, l_nea 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' does not contain table
'Clusterdb$'. The table either does not exist or the current user does not
have permissions on that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain
the table: ProviderName='Microsoft.Jet.OLEDB.4.0', TableName='Clusterdb$'].
Clusterdb exists as a sheet
"David Samson" wrote:
> Yes, I have a C:\Test.xls with 3 columns, 10 rows, and one tab named Sheet
1.
> I am logged in with an ID that has Admin access to the PC that I am runnin
g
> directly from. The sql server is on a separate box - Windows Server 2003,
> SQL Server 2000 Enterprise.
> "Michael Cheng [MSFT]" wrote:
>|||I found the problem. I was assuming that the reference to 'C:\test.xls' was
local. It's not, it's actually from the reference point of the server
itself. It makes sense, now that I think about it. So, once I copied the
file over to the C:\ of the database server, it worked like a champ. This i
s
the query I used:
select * FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\test.xls', [Sheet1$])
"John Bell" wrote:
> Hi
> You could try opening the file in DTS to see what sheet names you have, th
is
> will also prove you have the correct access permissions or alternatively
> create a new file and paste the data into that then there definately be a
> sheet1.
> Martin
> "David Samson" wrote:
>|||I found the problem. I was assuming that the reference to 'C:\test.xls' was
local. It's not, it's actually from the reference point of the server
itself. It makes sense, now that I think about it. So, once I copied the
test.xls file over to the C:\ of the database server, it worked like a champ
.
This is the query I used:
select * FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\test.xls', [Sheet1$])
"Uri Dimant" wrote:

> Well, try the following query
> SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
> 'Data Source=c:\test.xls;Extended Properties=Excel 8.0')...Sheet1$
>
> Also take a look at Q306397 HOWTO: Use Excel w/ SQL Linked Servers &
> Distributed Queries
> http://support.microsoft.com/suppor...s/q306/3/97.asp
>
> "David Samson" <CaptainSlock@.nospam.nospam> wrote in message
> news:1212A046-58F7-4B00-8EFE-1984FD9F4814@.microsoft.com...
>
>

query for stored procedures?

I'm working with SQL Server and I'd like to run a query to find out a
list of the user stored procedures in a given database, and what the
parameters for each stored procedure are.http://www.aspfaq.com/2123
http://www.aspfaq.com/search.asp?q=schema%3A
<apandapion@.gmail.com> wrote in message
news:1129665163.315912.19510@.g14g2000cwa.googlegroups.com...
> I'm working with SQL Server and I'd like to run a query to find out a
> list of the user stored procedures in a given database, and what the
> parameters for each stored procedure are.
>

query filling tempdb

Hello,
I've got a user who has been trying to run a query for the last few
days. Every time he tries to run it, the tempdb grows to be about 5G and
ends up filling up the hard drive. Anyone have any insight into why
this particular query is filling up the tempdb like this?
SELECT top 50
SUBSTRING(dbo.CIMSDetail.AccountCode, 1, 1) AS Corp,
SUBSTRING(dbo.CIMSDetail.AccountCode, 2, 2) AS BillGroup,
SUBSTRING(dbo.CIMSDetail.AccountCode, 4, 7) As BillEntity,
SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 2) AS LOB,
SUBSTRING(dbo.CIMSDetail.AccountCode, 13, 3) AS Portfolio,
SUBSTRING(dbo.CIMSDetail.AccountCode, 16, 1) AS WorkType.
SUBSTRING(dbo.CIMSDetail.AccountCode, 17, 3) AS Client,
SUBSTRING(dbo.CIMSDetail.AccountCode, 20, 2) AS Service,
SUBSTRING(dbo.CIMSDetail.AccountCode, 22, 2) AS Service Type,
SUBSTRING(dbo.CIMSDetail.AccountCode, 24, 2) AS Product,
SUBSTRING(dbo.CIMSDetail.AccountCode, 26, 3) AS Department,
SUBSTRING(dbo.CIMSDetail.AccountCode, 29, 3) AS LOC,
SUBSTRING(dbo.CIMSDetail.AccountCode, 32, 4) AS LPAR,
SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 18) AS ORIGSUM,
dbo.CIMSDetail.AccountCode As Acct_Code,
dbo.CIMSDetail.StartDate AS StartDay,
dbo.CIMSDetail.EndDate AS EndDay,
dbo.CIMSDetail.RateCode as RateCode,
dbo.CIMSDetail.ResourceUnits AS Volume,
dbo.CIMSDetailIdent.IdentValue AS Ident_Value,
dbo.CIMSIdent.IdentDescription AS IDENT_Desc,
DATEPART(yyyy, dbo.CIMSDetail.EndDate) AS Year,
DATEPART(mm, dbo.CIMSDetail.EndDate) AS Month,
DATEPART(dd, dbo.CIMSDetail.EndDate) AS Day
FROM dbo.CIMSDetail INNER JOIN
dbo.CIMSDetailIdent ON dbo.CIMSDetail.DetailUID = dbo.CIMSDetailIdent.DetailUID AND
dbo.CIMSDetail.DetailLine = dbo.CIMSDetailIdent.DetailLine INNER JOIN
dbo.CIMSIdent ON dbo.CIMSDetailIdent.IdentNumber = dbo.CIMSIdent.IdentNumber
WHERE DATEPART(DD,GETDATE()) - DATEPART(DD, dbo.CIMSDetail.EndDate) = 1
AND dbo.CIMSDetail.RateCode IN ('Z003', 'Z020', 'ZZ05')
AND dbo.CIMSIdent.IdentDescription = 'JOBNAME'
OR dbo.CIMSIdent.IdentDescription = 'WORK_ID'
GROUP BY
DATEPART(yyyy, dbo.CIMSDetail.EndDate),
DATEPART(mm, dbo.CIMSDetail.EndDate),
DATEPART(dd, dbo.CIMSDetail.EndDate),
SUBSTRING(dbo.CIMSDetail.AccountCode, 32, 4),
SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 2),
SUBSTRING(dbo.CIMSDetail.AccountCode, 29, 3),
dbo.CIMSDetail.RateCode,
dbo.CIMSDetail.RateCode,
dbo.CIMSDetail.StartDate,
dbo.CIMSDetail.EndDate,
dbo.CIMSIdent.IdentDescription,
dbo.CIMSDetailIdent.IdentValue,
dbo.CIMSDetail.AccountCode,
SUBSTRING(dbo.CIMSDETAIL.AccountCode,11,18)
ORDER BY
DATEPART(yyyy, dbo.CIMSDetail.EndDate),
DATEPART(mm, dbo.CIMSDetail.EndDate),
DATEPART(dd, dbo.CIMSDetail.EndDate),
SUBSTRING(dbo.CIMSDetail.AccountCode, 32, 4),
SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 2),
SUBSTRING(dbo.CIMSDetail.AccountCode, 29, 3),
dbo.CIMSDetail.RateCode,
dbo.CIMSDetail.StartDate,
dbo.CIMSDetail.EndDate,
dbo.CIMSIdent.IdentDescription,
dbo.CIMSDetailIdent.IdentValue,
dbo.CIMSDetail.AccountCode,
SUBSTRING(dbo.CIMSDETAIL.AccountCode,11,18)
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!You might consider normalization. :-) Why is all that discrete data in one
column? The database has to do a lot of work separating it out with
SUBSTRING like that.
You might also consider putting tempdb on a drive with more than 5GB free.
:-)
"Rachael Faber" <rfaber@.alldata.net> wrote in message
news:eJh59sOjDHA.3192@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I've got a user who has been trying to run a query for the last few
> days. Every time he tries to run it, the tempdb grows to be about 5G and
> ends up filling up the hard drive. Anyone have any insight into why
> this particular query is filling up the tempdb like this?
> SELECT top 50
> SUBSTRING(dbo.CIMSDetail.AccountCode, 1, 1) AS Corp,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 2, 2) AS BillGroup,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 4, 7) As BillEntity,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 2) AS LOB,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 13, 3) AS Portfolio,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 16, 1) AS WorkType.
> SUBSTRING(dbo.CIMSDetail.AccountCode, 17, 3) AS Client,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 20, 2) AS Service,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 22, 2) AS Service Type,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 24, 2) AS Product,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 26, 3) AS Department,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 29, 3) AS LOC,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 32, 4) AS LPAR,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 18) AS ORIGSUM,
> dbo.CIMSDetail.AccountCode As Acct_Code,
> dbo.CIMSDetail.StartDate AS StartDay,
> dbo.CIMSDetail.EndDate AS EndDay,
> dbo.CIMSDetail.RateCode as RateCode,
> dbo.CIMSDetail.ResourceUnits AS Volume,
> dbo.CIMSDetailIdent.IdentValue AS Ident_Value,
> dbo.CIMSIdent.IdentDescription AS IDENT_Desc,
> DATEPART(yyyy, dbo.CIMSDetail.EndDate) AS Year,
> DATEPART(mm, dbo.CIMSDetail.EndDate) AS Month,
> DATEPART(dd, dbo.CIMSDetail.EndDate) AS Day
>
> FROM dbo.CIMSDetail INNER JOIN
> dbo.CIMSDetailIdent ON dbo.CIMSDetail.DetailUID => dbo.CIMSDetailIdent.DetailUID AND
> dbo.CIMSDetail.DetailLine = dbo.CIMSDetailIdent.DetailLine INNER JOIN
> dbo.CIMSIdent ON dbo.CIMSDetailIdent.IdentNumber => dbo.CIMSIdent.IdentNumber
> WHERE DATEPART(DD,GETDATE()) - DATEPART(DD, dbo.CIMSDetail.EndDate) => 1
> AND dbo.CIMSDetail.RateCode IN ('Z003', 'Z020', 'ZZ05')
> AND dbo.CIMSIdent.IdentDescription = 'JOBNAME'
> OR dbo.CIMSIdent.IdentDescription = 'WORK_ID'
> GROUP BY
> DATEPART(yyyy, dbo.CIMSDetail.EndDate),
> DATEPART(mm, dbo.CIMSDetail.EndDate),
> DATEPART(dd, dbo.CIMSDetail.EndDate),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 32, 4),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 2),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 29, 3),
> dbo.CIMSDetail.RateCode,
> dbo.CIMSDetail.RateCode,
> dbo.CIMSDetail.StartDate,
> dbo.CIMSDetail.EndDate,
> dbo.CIMSIdent.IdentDescription,
> dbo.CIMSDetailIdent.IdentValue,
> dbo.CIMSDetail.AccountCode,
> SUBSTRING(dbo.CIMSDETAIL.AccountCode,11,18)
> ORDER BY
> DATEPART(yyyy, dbo.CIMSDetail.EndDate),
> DATEPART(mm, dbo.CIMSDetail.EndDate),
> DATEPART(dd, dbo.CIMSDetail.EndDate),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 32, 4),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 2),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 29, 3),
> dbo.CIMSDetail.RateCode,
> dbo.CIMSDetail.StartDate,
> dbo.CIMSDetail.EndDate,
> dbo.CIMSIdent.IdentDescription,
> dbo.CIMSDetailIdent.IdentValue,
> dbo.CIMSDetail.AccountCode,
> SUBSTRING(dbo.CIMSDETAIL.AccountCode,11,18)
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Typically coputed values in your select statement (like
all of the substring statements) require use of tempdb
space, and the group by and order by portions of your
query will also require a large amount of tempdb space.
In addition to Aaron's comments, you may want to consider
using a summary table that stores some of these
calculated/summary values so that you don't have to
calculate them within the query itself. While this will
use space within your actual database, this will be
easier to monitor and control then the space allocated on
the fly within tempdb.
Just an idea. I hope that this helps somehow.
Matthew Bando
BandoM@.CSCTechnologies.com
>--Original Message--
>Hello,
>I've got a user who has been trying to run a query for
the last few
>days. Every time he tries to run it, the tempdb grows to
be about 5G and
>ends up filling up the hard drive. Anyone have any
insight into why
>this particular query is filling up the tempdb like this?
>SELECT top 50
> SUBSTRING(dbo.CIMSDetail.AccountCode, 1, 1) AS
Corp,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 2, 2) AS
BillGroup,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 4, 7) As
BillEntity,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 2) AS
LOB,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 13, 3) AS
Portfolio,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 16, 1) AS
WorkType.
> SUBSTRING(dbo.CIMSDetail.AccountCode, 17, 3) AS
Client,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 20, 2) AS
Service,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 22, 2) AS
Service Type,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 24, 2) AS
Product,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 26,
3) AS Department,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 29, 3) AS
LOC,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 32, 4) AS
LPAR,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 18) AS
ORIGSUM,
> dbo.CIMSDetail.AccountCode As Acct_Code,
> dbo.CIMSDetail.StartDate AS StartDay,
> dbo.CIMSDetail.EndDate AS EndDay,
> dbo.CIMSDetail.RateCode as RateCode,
> dbo.CIMSDetail.ResourceUnits AS Volume,
> dbo.CIMSDetailIdent.IdentValue AS Ident_Value,
> dbo.CIMSIdent.IdentDescription AS IDENT_Desc,
> DATEPART(yyyy, dbo.CIMSDetail.EndDate) AS Year,
> DATEPART(mm, dbo.CIMSDetail.EndDate) AS Month,
> DATEPART(dd, dbo.CIMSDetail.EndDate) AS Day
>
>FROM dbo.CIMSDetail INNER JOIN
> dbo.CIMSDetailIdent ON dbo.CIMSDetail.DetailUID =>dbo.CIMSDetailIdent.DetailUID AND
> dbo.CIMSDetail.DetailLine =dbo.CIMSDetailIdent.DetailLine INNER JOIN
> dbo.CIMSIdent ON dbo.CIMSDetailIdent.IdentNumber =>dbo.CIMSIdent.IdentNumber
>WHERE DATEPART(DD,GETDATE()) - DATEPART(DD,
dbo.CIMSDetail.EndDate) =>1
> AND dbo.CIMSDetail.RateCode IN
('Z003', 'Z020', 'ZZ05')
> AND dbo.CIMSIdent.IdentDescription = 'JOBNAME'
> OR dbo.CIMSIdent.IdentDescription
= 'WORK_ID'
>GROUP BY
> DATEPART(yyyy, dbo.CIMSDetail.EndDate),
> DATEPART(mm, dbo.CIMSDetail.EndDate),
> DATEPART(dd, dbo.CIMSDetail.EndDate),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 32, 4),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 2),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 29, 3),
> dbo.CIMSDetail.RateCode,
> dbo.CIMSDetail.RateCode,
> dbo.CIMSDetail.StartDate,
> dbo.CIMSDetail.EndDate,
> dbo.CIMSIdent.IdentDescription,
> dbo.CIMSDetailIdent.IdentValue,
> dbo.CIMSDetail.AccountCode,
> SUBSTRING(dbo.CIMSDETAIL.AccountCode,11,18)
>ORDER BY
> DATEPART(yyyy, dbo.CIMSDetail.EndDate),
> DATEPART(mm, dbo.CIMSDetail.EndDate),
> DATEPART(dd, dbo.CIMSDetail.EndDate),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 32,
4),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 2),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 29, 3),
> dbo.CIMSDetail.RateCode,
> dbo.CIMSDetail.StartDate,
> dbo.CIMSDetail.EndDate,
> dbo.CIMSIdent.IdentDescription,
> dbo.CIMSDetailIdent.IdentValue,
> dbo.CIMSDetail.AccountCode,
> SUBSTRING(dbo.CIMSDETAIL.AccountCode,11,18)
>
>*** Sent via Developersdex http://www.developersdex.com
***
>Don't just participate in USENET...get rewarded for it!
>.
>

query execution time

i want to know is there is a command to know how many time takes a query to run

You can achive this using SQL Profiler. You need not to do any programming/coding for this.

See at Books Online..

You can do it programatically also...

Here the sample code..

Code Snippet

DECLARE @.StartDateTime DATETIME

DECLARE @.EndDateTime DATETIME

DECLARE @.Msg VARCHAR(200)

DECLARE @.RC as Int

SELECT @.StartDateTime = GETDATE()

EXEC YOURSP / QUERY

SELECT @.RC = @.@.ROWCOUNT, @.EndDateTime = GETDATE()

SELECT @.Msg = 'Your SP Name' + CONVERT(VARCHAR(10),@.RC) + ' ' + CONVERT(VARCHAR(25), DATEDIFF(MS, @.StartDateTime, @.EndDateTime)) + 'ms'

PRINT @.Msg

|||

instead of above code use sp_who.

|||there have to be something like just one command
|||

Luis:

Maybe SET STATISTICS TIME ON and SET STATISTICS TIME OFF?

sql

Query execution plan different between production/test - same data

I have a query that runs much much slower on my production server than my test server. By tweaking the query I can make it run fast in production. Why can't I make prod act like test? The difference in execution time is 15 minutes! (test - 5 seconds/prod
- 15 minutes). I need to make PROD more like TEST, or at least TEST should be slower than PROD (what is the point of load testing?)
My test server has the same SQL version/patch level and OS version/patch level as my production server. The data is a replica. The query execution plans are different. The production server has more ram, faster CPUs (dual), and faster disk. Production is
under light utilization (<20%). It is reindexed each night. I have updated statistics on all tables involved. I ran a server configuration and schema configuration comparison with RedGate software and all items are indenticle (save ram/cpu/names of jobs).
The prod server has 2 cpu so I tried restricting parallelism to 1. When the slow query runs on production it chews one of the CPU's @.50% for all 15 minutes. The problem has slowly gotten worse of the past month. The database is only 120mb and is the only
db so far on this server. The DB server is dedicated, there are no other uses for it. Its paired app server is under very low utilization also. As I said, by tweaking the query the query time goes from 15 minutes down to 4 seconds without changing the re
sult set. The main gotcha is that my test server runs the fast execution plan with or without tweaking the query. I need it to run fast without tweaking so my users can run ad hoc queries without me having to get involved. I know it is possible, look at m
y test box!!
I am stumped on this performance problem. Please help if you can with any thoughts or suggestions for getting MSSQL to run consistant with respect to execution plans between my two servers. Our Oracle DBA is laughing...
There are many explanations for difference in query behaviour and
execution. The same query can behave differently on different boxes
depending on configuration and general environfment specific to a
particular box.
You might want to refer to
243588 HOW TO: Troubleshoot the Performance of Ad-Hoc Queries
http://support.microsoft.com/?id=243588
Things you might want to pay attention to :-
* The tables or views the query touches - are they identical on both the
'good' (Test) and the 'bad' (Production) sql servers? Meaning, do they have
identical indexes (you can check doing a sp_help on the tables) OR do they
have the same kind of data and data distribution?
* How often are statistics updated on the good and bad sql servers? Try
doing an UPDATE STATISTICS myTable WITH FULLSCAN on each of the tables that
are accessed by this query. (You might want to do it off peak hours though).
* run the query through the Index Tuning Wizard - see if it recommends any
additional non clustered or covering indexes.
* General load on the server(s), are they the same?
Hope that helps.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Query execution plan different between production/test - same data

I have a query that runs much much slower on my production server than my te
st server. By tweaking the query I can make it run fast in production. Why c
an't I make prod act like test? The difference in execution time is 15 minut
es! (test - 5 seconds/prod
- 15 minutes). I need to make PROD more like TEST, or at least TEST should b
e slower than PROD (what is the point of load testing?)
My test server has the same SQL version/patch level and OS version/patch lev
el as my production server. The data is a replica. The query execution plans
are different. The production server has more ram, faster CPUs (dual), and
faster disk. Production is
under light utilization (<20%). It is reindexed each night. I have updated s
tatistics on all tables involved. I ran a server configuration and schema co
nfiguration comparison with RedGate software and all items are indenticle (s
ave ram/cpu/names of jobs).
The prod server has 2 cpu so I tried restricting parallelism to 1. When the
slow query runs on production it chews one of the CPU's @.50% for all 15 minu
tes. The problem has slowly gotten worse of the past month. The database is
only 120mb and is the only
db so far on this server. The DB server is dedicated, there are no other use
s for it. Its paired app server is under very low utilization also. As I sai
d, by tweaking the query the query time goes from 15 minutes down to 4 secon
ds without changing the re
sult set. The main gotcha is that my test server runs the fast execution pla
n with or without tweaking the query. I need it to run fast without tweaking
so my users can run ad hoc queries without me having to get involved. I kno
w it is possible, look at m
y test box!!
I am stumped on this performance problem. Please help if you can with any th
oughts or suggestions for getting MSSQL to run consistant with respect to ex
ecution plans between my two servers. Our Oracle DBA is laughing...There are many explanations for difference in query behaviour and
execution. The same query can behave differently on different boxes
depending on configuration and general environfment specific to a
particular box.
You might want to refer to
243588 HOW TO: Troubleshoot the Performance of Ad-Hoc Queries
http://support.microsoft.com/?id=243588
Things you might want to pay attention to :-
* The tables or views the query touches - are they identical on both the
'good' (Test) and the 'bad' (Production) sql servers? Meaning, do they have
identical indexes (you can check doing a sp_help on the tables) OR do they
have the same kind of data and data distribution?
* How often are statistics updated on the good and bad sql servers? Try
doing an UPDATE STATISTICS myTable WITH FULLSCAN on each of the tables that
are accessed by this query. (You might want to do it off peak hours though).
* run the query through the Index Tuning Wizard - see if it recommends any
additional non clustered or covering indexes.
* General load on the server(s), are they the same?
Hope that helps.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Query execution plan different between production/test - same data

I have a query that runs much much slower on my production server than my test server. By tweaking the query I can make it run fast in production. Why can't I make prod act like test? The difference in execution time is 15 minutes! (test - 5 seconds/prod
- 15 minutes). I need to make PROD more like TEST, or at least TEST should be slower than PROD (what is the point of load testing?)
My test server has the same SQL version/patch level and OS version/patch level as my production server. The data is a replica. The query execution plans are different. The production server has more ram, faster CPUs (dual), and faster disk. Production is
under light utilization (<20%). It is reindexed each night. I have updated statistics on all tables involved. I ran a server configuration and schema configuration comparison with RedGate software and all items are indenticle (save ram/cpu/names of jobs).
The prod server has 2 cpu so I tried restricting parallelism to 1. When the slow query runs on production it chews one of the CPU's @.50% for all 15 minutes. The problem has slowly gotten worse of the past month. The database is only 120mb and is the only
db so far on this server. The DB server is dedicated, there are no other uses for it. Its paired app server is under very low utilization also. As I said, by tweaking the query the query time goes from 15 minutes down to 4 seconds without changing the re
sult set. The main gotcha is that my test server runs the fast execution plan with or without tweaking the query. I need it to run fast without tweaking so my users can run ad hoc queries without me having to get involved. I know it is possible, look at m
y test box!!
I am stumped on this performance problem. Please help if you can with any thoughts or suggestions for getting MSSQL to run consistant with respect to execution plans between my two servers. Our Oracle DBA is laughing...
Can you show the query? You say you tweaked it? How did you do that? Is
the data the exact same on both machines? What does the query plan look like
for the slow one compared to the fast one?
Andrew J. Kelly SQL MVP
"Aaron" <Aaron@.discussions.microsoft.com> wrote in message
news:33F423DF-1D9C-4227-B1F4-2CAF5142BD9C@.microsoft.com...
> I have a query that runs much much slower on my production server than my
test server. By tweaking the query I can make it run fast in production. Why
can't I make prod act like test? The difference in execution time is 15
minutes! (test - 5 seconds/prod - 15 minutes). I need to make PROD more like
TEST, or at least TEST should be slower than PROD (what is the point of load
testing?)
> My test server has the same SQL version/patch level and OS version/patch
level as my production server. The data is a replica. The query execution
plans are different. The production server has more ram, faster CPUs (dual),
and faster disk. Production is under light utilization (<20%). It is
reindexed each night. I have updated statistics on all tables involved. I
ran a server configuration and schema configuration comparison with RedGate
software and all items are indenticle (save ram/cpu/names of jobs). The prod
server has 2 cpu so I tried restricting parallelism to 1. When the slow
query runs on production it chews one of the CPU's @.50% for all 15 minutes.
The problem has slowly gotten worse of the past month. The database is only
120mb and is the only db so far on this server. The DB server is dedicated,
there are no other uses for it. Its paired app server is under very low
utilization also. As I said, by tweaking the query the query time goes from
15 minutes down to 4 seconds without changing the result set. The main
gotcha is that my test server runs the fast execution plan with or without
tweaking the query. I need it to run fast without tweaking so my users can
run ad hoc queries without me having to get involved. I know it is possible,
look at my test box!!
> I am stumped on this performance problem. Please help if you can with any
thoughts or suggestions for getting MSSQL to run consistant with respect to
execution plans between my two servers. Our Oracle DBA is laughing...
>

Query Execution Failed for Dataset (Beginner)

I got this Error, Query Execution for Dataset 'Source'.

When i try to run a report that is actually a drillthrough from another report. It runs fine in Report Designer, and when i deploy to my Local server. But when i deploy it to my virtual Report Server, I get this error messege. Why is it doing this? and where can i see errors for this type of stuff, so i can figure this out. This uses the same Datasource as the report linked from it. That report works fine, why wont this one? any ideas?

Look for the report server log at this location C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\LogFiles if installed to the default location.|||A collegue of mine helped me figure this one out. My stored procedure was not set to public in the properties section. Thats why it worked on my local computer and not on the report server.

query Exec time?

Why would the same have different run time...less than 5 sec vs more than a
minute.Arul,
Same ? Check the Execution Plans for the queries. Also, ensure it is not
related to the data coming from disk on the first run data coming from
memory on the second run. See:
DBCC DROPCLEANBUFFERS
Stored procedures? Possible recompiles.
More info would help.
HTH
Jerry
"Arul" <Arul@.discussions.microsoft.com> wrote in message
news:9657DDF8-08BA-4908-8FC9-DB2DA5856D43@.microsoft.com...
> Why would the same have different run time...less than 5 sec vs more than
> a
> minute.

Query estimate cost and real run time different

I have a query, before I do any change, the estimate execute plan show me th
e cost is 1400, the real run time is about 1 minute 33 seconds.
After I eliminated some concatinated column, the estimate execute plan shows
me cost reduce to 66, I expect the query will run quite faster than the ori
ginal one, but real run time still keep in 1 minute 27 seconds.
I know estimate execute cost is not accurate, but should not be such a big d
ifference.
Any body can tell me why, what is the good way to tunning my query?
Thanks in advance,
HGHG,
The best way to tune your query is to actually run it and see what it does.
Examine the execution plan and focus on the parts of the plan that are most
expensive. If one step takes 75% of the effort and five other steps each
take 5%, then examine the 75% and see what you can do about it. (Better
join criteria, another index, recasting the code to use a different
approach, etc.)
Estimated plan is sometimes mildly helpful, but (as you note) cannot be
relied upon. Also, even the actual execution plan has blind spots. For
example, it will not measure how much time is spent in a UDF, the IS_MEMBER
function, and so forth, viewing them as nearly free.
Therefore, in addition to the execution plan, test with timing statements
that show how much clock time the steps take. If you run several tests you
will get a good measure of the execution time.
Russell Fields
"HG" <anonymous@.discussions.microsoft.com> wrote in message
news:C01707AF-3B18-45AC-B188-52D313EBE860@.microsoft.com...
> I have a query, before I do any change, the estimate execute plan show me
the cost is 1400, the real run time is about 1 minute 33 seconds.
> After I eliminated some concatinated column, the estimate execute plan
shows me cost reduce to 66, I expect the query will run quite faster than
the original one, but real run time still keep in 1 minute 27 seconds.
> I know estimate execute cost is not accurate, but should not be such a big
difference.
> Any body can tell me why, what is the good way to tunning my query?
> Thanks in advance,
> HG|||I am new to sql. Can you provide an example of a timing statement that will
show me how long a querry took?|||Larry,
DECLARE @.StartTime DATETIME
SET @.StartTime = GETDATE()
Execute your code here
SELECT DATEDIFF(ms,@.StartTime, GETDATE()) AS ElapsedMilliseconds
Because there are variable, run this a few times to get a best time.
Compare best times of two different strategies to determine how they
compare.
Russell Fields
"Larrry Pensil" <anonymous@.discussions.microsoft.com> wrote in message
news:56CC0769-A7B1-4E97-ADE6-FEF9AF056A77@.microsoft.com...
> I am new to sql. Can you provide an example of a timing statement that
will show me how long a querry took?