Grid display: executed

Showing posts with label executed. Show all posts

Thursday, March 29, 2012

Get all active transactions

I have executed a data script using query analyser and closed the analyser without commiting the transaction.

I want to know are there any pending transactions still.

thanks

Quote:

Originally Posted by kvm

I have executed a data script using query analyser and closed the analyser without commiting the transaction.

I want to know are there any pending transactions still.

thanks

Did you use transactions explictly in your SQL or only wondering about Query analyser transaction?

get a return value from an insert without using a stored proc.

hi all,

lets say i have this insert command being executed from C# to a SQL Db.

//store transaction logSqlCommand cmdStoreTrans =new SqlCommand("INSERT into Transactions(ImportID,ProfileID,RowID) values (@.ImportID,@.ProfileID,@.RowID);",conn);cmdStoreTrans.Parameters.Add("@.ImportID",importId);cmdStoreTrans.Parameters.Add("@.ProfileID",profileId);cmdStoreTrans.Parameters.Add("@.RowID",i);try{conn.Open();cmdStoreTrans.ExecuteNonQuery();conn.Close();}catch(SqlException ex){throw(ex);}

I need the new Identity number of that record added. how can i get that within THIS Sqlcommand. Currently im closing the connection, creating a new command with 'SELECT MAX(id) from transactions" and getting the value that way, im sure there is a easier way keeping it all within one command object? someone mentioned using something liek @.@.Identity

any help appreciated

TIA, mcm

Try doing a SELECT SCOPE_IDENTITY() at the end of your INSERT statement. and use ExecuteScalar instead of ExecuteNonQuery.|||

thanks,

will do.

mcm

Monday, March 26, 2012

Generation of estimated execution plan takes a long time

Hello,
I am puzzled by following problem. One of our tables has more than 12
billion rows. Usually, query plan and query itself is executed almost
immediately but occasionally, it take about 2 minutes to generate estimated
query execution plan for following query:
SELECT MAX(qwEventIx) FROM FTE_STREAM_EVENT (NOLOCK)
There is a PK clustered index defined on qwEventIx field. I do not observer
any blocking on this object when it takes a long time to generate estimated
query plan. There is a plan cached in syscacheobjects for this query.
I was wondering if anyone can explain why it takes such a long time to
generate estimated query plan on some occasions.
Thanks,
IgorIgor,
Are the statistics current and up-to-date?
HTH
Jerry
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:uIwoFzZ2FHA.4076@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I am puzzled by following problem. One of our tables has more than 12
> billion rows. Usually, query plan and query itself is executed almost
> immediately but occasionally, it take about 2 minutes to generate
> estimated query execution plan for following query:
> SELECT MAX(qwEventIx) FROM FTE_STREAM_EVENT (NOLOCK)
> There is a PK clustered index defined on qwEventIx field. I do not
> observer any blocking on this object when it takes a long time to
> generate estimated query plan. There is a plan cached in syscacheobjects
> for this query.
> I was wondering if anyone can explain why it takes such a long time to
> generate estimated query plan on some occasions.
> Thanks,
> Igor
>|||It could be that this is when auto-update of statistics occurs. You can catch that in Profiler using
Object, Auto Stats.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:uIwoFzZ2FHA.4076@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I am puzzled by following problem. One of our tables has more than 12 billion rows. Usually, query
> plan and query itself is executed almost immediately but occasionally, it take about 2 minutes to
> generate estimated query execution plan for following query:
> SELECT MAX(qwEventIx) FROM FTE_STREAM_EVENT (NOLOCK)
> There is a PK clustered index defined on qwEventIx field. I do not observer any blocking on this
> object when it takes a long time to generate estimated query plan. There is a plan cached in
> syscacheobjects for this query.
> I was wondering if anyone can explain why it takes such a long time to generate estimated query
> plan on some occasions.
> Thanks,
> Igor
>|||Tibor,
You were absolutely correct! SQL Server was updating statistics on 12
billion rows table. It was taking over 2 minutes. I suppose it might make
sense to disable AUTOSTATS on this table with sp_autostats and update
statistics manually during off-peak hours. Would you recommend this
approach?
Thanks,
Igor
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%2311fWFg2FHA.956@.TK2MSFTNGP10.phx.gbl...
> It could be that this is when auto-update of statistics occurs. You can
> catch that in Profiler using Object, Auto Stats.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:uIwoFzZ2FHA.4076@.TK2MSFTNGP09.phx.gbl...
>> Hello,
>> I am puzzled by following problem. One of our tables has more than 12
>> billion rows. Usually, query plan and query itself is executed almost
>> immediately but occasionally, it take about 2 minutes to generate
>> estimated query execution plan for following query:
>> SELECT MAX(qwEventIx) FROM FTE_STREAM_EVENT (NOLOCK)
>> There is a PK clustered index defined on qwEventIx field. I do not
>> observer any blocking on this object when it takes a long time to
>> generate estimated query plan. There is a plan cached in syscacheobjects
>> for this query.
>> I was wondering if anyone can explain why it takes such a long time to
>> generate estimated query plan on some occasions.
>> Thanks,
>> Igor
>|||Jerry,
It is indeed statistics related.
Thanks,
Igor
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uQmyqVa2FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Igor,
> Are the statistics current and up-to-date?
> HTH
> Jerry
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:uIwoFzZ2FHA.4076@.TK2MSFTNGP09.phx.gbl...
>> Hello,
>> I am puzzled by following problem. One of our tables has more than 12
>> billion rows. Usually, query plan and query itself is executed almost
>> immediately but occasionally, it take about 2 minutes to generate
>> estimated query execution plan for following query:
>> SELECT MAX(qwEventIx) FROM FTE_STREAM_EVENT (NOLOCK)
>> There is a PK clustered index defined on qwEventIx field. I do not
>> observer any blocking on this object when it takes a long time to
>> generate estimated query plan. There is a plan cached in syscacheobjects
>> for this query.
>> I was wondering if anyone can explain why it takes such a long time to
>> generate estimated query plan on some occasions.
>> Thanks,
>> Igor
>|||Yes, with that long auto.stats time, disabling autostats for that table is reasonable. Make sure you
schedule manual update statistics with a reasonable sample and at a good time (like not before some
big batch, but after :-) ).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:e1KGDu%232FHA.3420@.TK2MSFTNGP15.phx.gbl...
> Tibor,
> You were absolutely correct! SQL Server was updating statistics on 12 billion rows table. It
> was taking over 2 minutes. I suppose it might make sense to disable AUTOSTATS on this table with
> sp_autostats and update statistics manually during off-peak hours. Would you recommend this
> approach?
> Thanks,
> Igor
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%2311fWFg2FHA.956@.TK2MSFTNGP10.phx.gbl...
>> It could be that this is when auto-update of statistics occurs. You can catch that in Profiler
>> using Object, Auto Stats.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
>> news:uIwoFzZ2FHA.4076@.TK2MSFTNGP09.phx.gbl...
>> Hello,
>> I am puzzled by following problem. One of our tables has more than 12 billion rows. Usually,
>> query plan and query itself is executed almost immediately but occasionally, it take about 2
>> minutes to generate estimated query execution plan for following query:
>> SELECT MAX(qwEventIx) FROM FTE_STREAM_EVENT (NOLOCK)
>> There is a PK clustered index defined on qwEventIx field. I do not observer any blocking on this
>> object when it takes a long time to generate estimated query plan. There is a plan cached in
>> syscacheobjects for this query.
>> I was wondering if anyone can explain why it takes such a long time to generate estimated query
>> plan on some occasions.
>> Thanks,
>> Igor
>>
>sql

Monday, March 12, 2012

Generating a flat file output from a select

I want to create a script file to executed from the command line. The script will contain a simple select, which depending on which database it is run against will produce a flat file with the output results.
e.g. a script file with a select such as 'SELECT name FROM sysusers'. when the script is executed from the command line it will produce a flat file with a list of the users on the database.
http://www.aspfaq.com/2482
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Tiarnan" <anonymous@.discussions.microsoft.com> wrote in message
news:736E002A-D0DC-4B49-A370-6FF2D203B53A@.microsoft.com...
> I want to create a script file to executed from the command line. The
script will contain a simple select, which depending on which database it is
run against will produce a flat file with the output results.
> e.g. a script file with a select such as 'SELECT name FROM sysusers'.
when the script is executed from the command line it will produce a flat
file with a list of the users on the database.
|||Hi,
You can use BCP OUT with QUERYOUT option. Inside
Query out you can give your TSQL to extract the data out.
Sample:-
BCP "Select name from dbname..sysusers" QUERYOUT
c:\sysusers.txt -Usa -Ppassword -SServer_name -c
Thanks
Hari
MCDBA
"Tiarnan" <anonymous@.discussions.microsoft.com> wrote in message
news:736E002A-D0DC-4B49-A370-6FF2D203B53A@.microsoft.com...
> I want to create a script file to executed from the command line. The
script will contain a simple select, which depending on which database it is
run against will produce a flat file with the output results.
> e.g. a script file with a select such as 'SELECT name FROM sysusers'.
when the script is executed from the command line it will produce a flat
file with a list of the users on the database.

Generating a flat file output from a select

I want to create a script file to executed from the command line. The scrip
t will contain a simple select, which depending on which database it is run
against will produce a flat file with the output results.
e.g. a script file with a select such as 'SELECT name FROM sysusers'. when
the script is executed from the command line it will produce a flat file wit
h a list of the users on the database.http://www.aspfaq.com/2482
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Tiarnan" <anonymous@.discussions.microsoft.com> wrote in message
news:736E002A-D0DC-4B49-A370-6FF2D203B53A@.microsoft.com...
> I want to create a script file to executed from the command line. The
script will contain a simple select, which depending on which database it is
run against will produce a flat file with the output results.
> e.g. a script file with a select such as 'SELECT name FROM sysusers'.
when the script is executed from the command line it will produce a flat
file with a list of the users on the database.|||Hi,
You can use BCP OUT with QUERYOUT option. Inside
Query out you can give your TSQL to extract the data out.
Sample:-
BCP "Select name from dbname..sysusers" QUERYOUT
c:\sysusers.txt -Usa -Ppassword -SServer_name -c
Thanks
Hari
MCDBA
"Tiarnan" <anonymous@.discussions.microsoft.com> wrote in message
news:736E002A-D0DC-4B49-A370-6FF2D203B53A@.microsoft.com...
> I want to create a script file to executed from the command line. The
script will contain a simple select, which depending on which database it is
run against will produce a flat file with the output results.
> e.g. a script file with a select such as 'SELECT name FROM sysusers'.
when the script is executed from the command line it will produce a flat
file with a list of the users on the database.

Friday, February 24, 2012

Generate Executed Subscriptions Report.

Hi
Now i need a report of executed subscriptions in specific shared schedules,
Someone have something to parse Subscriptions table or only way is consuming
webservice?
Someone have an idea?
I program a module to get subscriptions (with lastexecution, emails, report
name, etc.) but i need a report.
--
Carlos E. Mosquera
DBA - Project Manager
BAC PanamÃ¡There is a RSExecution DTS Package under tools that comes with SQL Reporting
services.. Have you looked into it..?
"Carlos E. Mosquera" <CarlosEMosquera@.discussions.microsoft.com> wrote in
message news:9940F556-770B-4DD4-8C73-BDF4F2B263CC@.microsoft.com...
> Hi
> Now i need a report of executed subscriptions in specific shared
schedules,
> Someone have something to parse Subscriptions table or only way is
consuming
> webservice?
> Someone have an idea?
> I program a module to get subscriptions (with lastexecution, emails,
report
> name, etc.) but i need a report.
> --
> Carlos E. Mosquera
> DBA - Project Manager
> BAC Panamá|||Hi RK,
Where i can find info about this tool?
Thanks for you help.
"RK Balaji" wrote:
> There is a RSExecution DTS Package under tools that comes with SQL Reporting
> services.. Have you looked into it..?|||Hi RK,
Where i can find info about this tool?
Thanks for you help.
"RK Balaji" wrote:
> There is a RSExecution DTS Package under tools that comes with SQL Reporting
> services.. Have you looked into it..?|||Hi all,
Someone know about this RSExecution DTS Package?
Thanks.
"Carlos E. Mosquera" wrote:
> Hi RK,
> Where i can find info about this tool?
> Thanks for you help.
> "RK Balaji" wrote:
> > There is a RSExecution DTS Package under tools that comes with SQL Reporting
> > services.. Have you looked into it..?

Thursday, March 29, 2012

Get all active transactions

get a return value from an insert without using a stored proc.

Monday, March 26, 2012

Generation of estimated execution plan takes a long time

Generation of estimated execution plan takes a long time

Generation of estimated execution plan takes a long time

Monday, March 12, 2012

Generating a flat file output from a select

Generating a flat file output from a select

Friday, February 24, 2012

Generate Executed Subscriptions Report.

Grid display

Blog Archive

About Me