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 time

Hi,
Is there any command that i can use, that tells me with
precision the execution time of a query?
I'm using:
select getdate()
execute Query
select getdate()
Thanks,
Best regards
Have you tried: SET STATISTICS TIME ON ?
This will return a message with CPU time and elapsed time.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:28b0701c46519$98104f50$a601280a@.phx.gbl...
> Hi,
> Is there any command that i can use, that tells me with
> precision the execution time of a query?
> I'm using:
> select getdate()
> execute Query
> select getdate()
> Thanks,
> Best regards
|||Look at the SET STATISTICS TIME statement. You might want to look ate SET
STATISTICS IO and SET STATISTICS PROFILE as additional performance tuning
tools.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:28b0701c46519$98104f50$a601280a@.phx.gbl...
> Hi,
> Is there any command that i can use, that tells me with
> precision the execution time of a query?
> I'm using:
> select getdate()
> execute Query
> select getdate()
> Thanks,
> Best regards

Query execution time

Hi,
Is there any command that i can use, that tells me with
precision the execution time of a query?
I'm using:
select getdate()
execute Query
select getdate()
Thanks,
Best regardsHave you tried: SET STATISTICS TIME ON ?
This will return a message with CPU time and elapsed time.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:28b0701c46519$98104f50$a601280a@.phx
.gbl...
> Hi,
> Is there any command that i can use, that tells me with
> precision the execution time of a query?
> I'm using:
> select getdate()
> execute Query
> select getdate()
> Thanks,
> Best regards|||Look at the SET STATISTICS TIME statement. You might want to look ate SET
STATISTICS IO and SET STATISTICS PROFILE as additional performance tuning
tools.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:28b0701c46519$98104f50$a601280a@.phx
.gbl...
> Hi,
> Is there any command that i can use, that tells me with
> precision the execution time of a query?
> I'm using:
> select getdate()
> execute Query
> select getdate()
> Thanks,
> Best regards

Query execution time

Hi,
Is there any command that i can use, that tells me with
precision the execution time of a query?
I'm using:
select getdate()
execute Query
select getdate()
Thanks,
Best regardsHave you tried: SET STATISTICS TIME ON ?
This will return a message with CPU time and elapsed time.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:28b0701c46519$98104f50$a601280a@.phx.gbl...
> Hi,
> Is there any command that i can use, that tells me with
> precision the execution time of a query?
> I'm using:
> select getdate()
> execute Query
> select getdate()
> Thanks,
> Best regards|||Look at the SET STATISTICS TIME statement. You might want to look ate SET
STATISTICS IO and SET STATISTICS PROFILE as additional performance tuning
tools.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:28b0701c46519$98104f50$a601280a@.phx.gbl...
> Hi,
> Is there any command that i can use, that tells me with
> precision the execution time of a query?
> I'm using:
> select getdate()
> execute Query
> select getdate()
> Thanks,
> Best regards

Query error

Hi every one,
When I performed SQL delete command, server return message "[Microsoft][ODBC
SQL Server Driver][SQL Server]Internal Query Processor Error: The query ran
out of stack space during query optimization.". So I cann't delete the
record.
Can any body explain me how to fix this ?
ThanksHi Paul,
No, that table does not have any trigger.
And delete command very simple
DELETE FROM UserInfo WHERE (tp_ID = 667)
Thanks
"Paul Cahill" <paul.cahill@.cableinet.co.uk> wrote in message
news:uz54kDYZDHA.2932@.tk2msftngp13.phx.gbl...
> Any trigger on the tables?
> Is is a complex delete?
> Can you paste the statement here?
> Paul
> "Ben" <minh_nb@.yahoo.com> wrote in message
> news:OczUGXXZDHA.1832@.TK2MSFTNGP10.phx.gbl...
> > Hi every one,
> >
> > When I performed SQL delete command, server return message
> "[Microsoft][ODBC
> > SQL Server Driver][SQL Server]Internal Query Processor Error: The query
> ran
> > out of stack space during query optimization.". So I cann't delete the
> > record.
> >
> > Can any body explain me how to fix this ?
> >
> > Thanks
> >
> >
> >
>

Query database on linked server where database has name like [ABC.DEF]

A database exists on a linked server that I would like to query
When I run the following command
SELECT * from [INSTANCE_NAME].[ABC.DEF].dbo.sysfiles
-- Note the name of database has an "." in it (I cannot change this)
I get the error
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object '"ABC.DEF"."dbo"."sysfiles"'. The OLE DB
provider 'SQLOLEDB' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to
process object, since the object has no columnsProviderName='SQLOLEDB',
Query="ABC.DEF"."dbo"."sysfiles"'].
This query works on other databases on this instance and other
instances.
Does anybody have any ideas?Does the SQL Server has multiple instances in the same box? I have noticed
that Link Server is unable to recognize\access the second instance when
there are multiple instances..
"eahind@.yahoo.co.uk" wrote:
> A database exists on a linked server that I would like to query
> When I run the following command
> SELECT * from [INSTANCE_NAME].[ABC.DEF].dbo.sysfiles
> -- Note the name of database has an "." in it (I cannot change this)
> I get the error
> Server: Msg 7357, Level 16, State 2, Line 1
> Could not process object '"ABC.DEF"."dbo"."sysfiles"'. The OLE DB
> provider 'SQLOLEDB' indicates that the object has no columns.
> OLE DB error trace [Non-interface error: OLE DB provider unable to
> process object, since the object has no columnsProviderName='SQLOLEDB',
> Query="ABC.DEF"."dbo"."sysfiles"'].
> This query works on other databases on this instance and other
> instances.
> Does anybody have any ideas?
>|||This is the only instance on the host. I can also query other databases
on this instance - they do not have "." in their name.

Query Cube using SQL Select instead of MDX Select

In Analysis Services 2000, you can query cube using regular SQL Select command.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmpr/prsql_70e0.asp

Does Analysis Services 2005 support this feature?

Right now, I am using OpenQuery with MDX query to return Cube data into a SQL table:

select * FROM OPENQUERY(Olap_Linked_Server, '

select [Measures].[Sales] on columns, [Accounts].[Hierarchy].Members on rows from SalesCube')

But the result SQL table has long column names. How can I rename them?

Thanks,

Bo

Do your first insert into a temp table(TSQL) and rename the columns when you do the next insert to a second table, by using standard 'as', for renaming.

Regards

Thomas Ivarsson