EXECUTE AS question: queries

Showing posts with label queries. Show all posts

Tuesday, March 27, 2012

Executing more than one stored procedures in a DataReader

Hey guys,

I have found out that we can execute multiple queries and receive multiple resultsets in a SqlDataReader by executing the queries with ";" separators,

However, what if we wanted to execute two sqlcommand storedprocedures? are there any other way rather than placing "Execute sp1;Execute sp2" in the command text?

I would like to do it in a way whereby I can pass in two storedprocedures with parameters binding capability rather than execute sp1(param1, param2);execute sp2(param1, param2, param3)

Hope to get some suggestions and advice from you guys,

Thank you very much in advance.

Combine the two stored procedures into a third that will execute both.

|||

thanks Mike for your response,

any other better ways?

as there would be too many storedprocs just for combining procedures and it might get messy..

I was thinking to write a custom class and configure the class as I would for Sqlcommand storedprocedure, and then combine the various StoredProc Classes with a ";" and pass into the sqlcommand text.

But would this add-on to more performance defect? as there will be extra objects involved..

Please advice. Thanks.

|||

I don't think by any means or ways its a good idea to execute 2 or more procedures simultaneously from code. If you create such a class which can call and handle 2 or more SPs then also you'll have some questions to answer, such as what about the parameters ? You must be having different parameters for different SPs. Out of them, some may be out put type. What if an error occurs while executing any of the SPs ?.

So, my advice to you is better you execute them one by one if you don't have the compulsion ( which I don't think you'll be having ) to execute them simultaneously.

|||

Hi,

The best way as one user suggested you is to create a stored proc that combines multiple stored procs into one :

For eg:

CREATE PROCEDURE sp_ProcCallMultiple
(
@.var1 varchar(10)
@.var2 varchar(10),
)
AS

EXEC sp_Proc1 @.var1

EXEC sp_Proc2 @.var1

Once done, you can then use sp_ProcCallMultiple using a data reader

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

|||

Suprotim Agarwal:
Hi,
The best way as one user suggested you is to create a stored proc that combines multiple stored procs into one :
For eg:
CREATE PROCEDURE sp_ProcCallMultiple
(
@.var1 varchar(10)
@.var2 varchar(10),
)
AS

EXEC sp_Proc1 @.var1
EXEC sp_Proc2 @.var1

Once done, you can then use sp_ProcCallMultiple using a data reader

HTH,
Suprotim Agarwal
--
http://www.dotnetcurry.com
--

Hi,

Thanks for showing this sample but I already know about this, just wanted to find out if there is an alternative.

To Dhimant: It is possible to handle Output Parameters and so on and it is more effective as it only takes one trip to the server. However, I only need to do this mostly for queries with select statements, other stored procs which require calling two or more procs, i usually execute them in one proc itself. The reason why i didnt want to combine the selection procs is because, there may be too many combinations and things will get messy. Thanks for your advice.

Executing MDX queries from inside SQL Server Stored Procedures (SQL Server 2005)

Hello all,

Does anyone have any idea how to access a cube from stored procedures in SQL Server 2005?

My idea was to use SQLCLR and write a function in .NET that accessed the cube through ADOMD, but there are problems with that.

See the following code sample:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using Microsoft.AnalysisServices.AdomdClient;

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString MDXRdr()
{
AdomdConnection conn = new AdomdConnection();
conn.ConnectionString = @."Provider=SQLNCLI.1;Data Source=JOAHSE0\SQL2005;Integrated Security=SSPI;Initial Catalog=dbRenTAK";
conn.Open();
// Just output cube name
string str = conn.Cubes[0].Name;
conn.Close();

return new SqlString(str);
}
};

First, I wasn't able to add a reference to AdomdClient from Visual Studio. I then did add a reference manually in the project file and it compiles. But when I try to deploy, I get an error message that the "assembly adomdclient was not found in the SQL catalog".

Thanks in advance for any suggestions or hints!

Best Regards,

Johan ?hln
Consultant, IFS

Moving to the "Data Mining" Forum, which is better suited for this question.

Executing large result queries

Hi all,
I've developed an app (C#) that connects to SQL Server 2000 without using
thread pooling (using SqlConnection) and I run a query that returns ~4000
tuples each time it runs and displays the results in a listview. The problem
is that after I run it 3-4 times the results are read very slowly from the
DB. The first times the results are displayed in 1-2 secs but the 4,5 time
it takes more than 2 minutes! What am I doing wrong? Are there any
in-between buffers that need to be emptied?
Thanks,
-peterHi Peter,
If you could show us some code we might be able to make a better
diagnosis.
This is a windows forms application?
Any chance you are appending the results from the second query to the
listbox without clearning the list box first?
--
Scott
http://www.OdeToCode.com
On Sun, 8 Aug 2004 13:14:04 +0300, "pnp" <pnp.at.softlab.ece.ntua.gr>
wrote:
>Hi all,
>I've developed an app (C#) that connects to SQL Server 2000 without using
>thread pooling (using SqlConnection) and I run a query that returns ~4000
>tuples each time it runs and displays the results in a listview. The problem
>is that after I run it 3-4 times the results are read very slowly from the
>DB. The first times the results are displayed in 1-2 secs but the 4,5 time
>it takes more than 2 minutes! What am I doing wrong? Are there any
>in-between buffers that need to be emptied?
>Thanks,
>-peter
>|||The results are displayed in a listview and its' items are always cleared
before putting in the new ones...
"Scott Allen" <bitmask@.[nospam].fred.net> wrote in message
news:llbch0dojiom2vgf88iltuhpngpl4ikllh@.4ax.com...
> Hi Peter,
> If you could show us some code we might be able to make a better
> diagnosis.
> This is a windows forms application?
> Any chance you are appending the results from the second query to the
> listbox without clearning the list box first?
> --
> Scott
> http://www.OdeToCode.com
> On Sun, 8 Aug 2004 13:14:04 +0300, "pnp" <pnp.at.softlab.ece.ntua.gr>
> wrote:
> >Hi all,
> >I've developed an app (C#) that connects to SQL Server 2000 without using
> >thread pooling (using SqlConnection) and I run a query that returns ~4000
> >tuples each time it runs and displays the results in a listview. The
problem
> >is that after I run it 3-4 times the results are read very slowly from
the
> >DB. The first times the results are displayed in 1-2 secs but the 4,5
time
> >it takes more than 2 minutes! What am I doing wrong? Are there any
> >in-between buffers that need to be emptied?
> >
> >Thanks,
> >-peter
> >
>|||Start by using Profiler to see if you get the same execution plan between the executions. Depending on whether
you do or not, you can determine whether this is a SQL Server issue or a client app issue.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message news:OiAfzBTfEHA.4092@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I've developed an app (C#) that connects to SQL Server 2000 without using
> thread pooling (using SqlConnection) and I run a query that returns ~4000
> tuples each time it runs and displays the results in a listview. The problem
> is that after I run it 3-4 times the results are read very slowly from the
> DB. The first times the results are displayed in 1-2 secs but the 4,5 time
> it takes more than 2 minutes! What am I doing wrong? Are there any
> in-between buffers that need to be emptied?
> Thanks,
> -peter
>|||well i checked it with the profiler and while the SQL:BatchCompleted takes
about the same amount of time for each execution (~2200) the Audit Logout is
different and when the program slows down it takes numbers up to ~584263
while a normal execution takes up to ~20296.
What does this mean?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23q6Ow0XfEHA.224@.TK2MSFTNGP10.phx.gbl...
> Start by using Profiler to see if you get the same execution plan between
the executions. Depending on whether
> you do or not, you can determine whether this is a SQL Server issue or a
client app issue.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
news:OiAfzBTfEHA.4092@.TK2MSFTNGP10.phx.gbl...
> > Hi all,
> > I've developed an app (C#) that connects to SQL Server 2000 without
using
> > thread pooling (using SqlConnection) and I run a query that returns
~4000
> > tuples each time it runs and displays the results in a listview. The
problem
> > is that after I run it 3-4 times the results are read very slowly from
the
> > DB. The first times the results are displayed in 1-2 secs but the 4,5
time
> > it takes more than 2 minutes! What am I doing wrong? Are there any
> > in-between buffers that need to be emptied?
> >
> > Thanks,
> > -peter
> >
> >
>|||If you check Books Online, you will find following for the "Audit Logout" event:
Duration The approximate amount of time since the user logged in.
But the SQL:BatchCompleted are the same, so this indicates that the query executes in the same time, the added
time for the logout is the time for the client to process the results before it can logout from the SQL
Server.
I.e., the problem is with the client app (at least as far as I can see). You might want to post this (with
appropriate details) to an ADO.NET group...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message news:%23LQX4gZfEHA.3016@.tk2msftngp13.phx.gbl...
> well i checked it with the profiler and while the SQL:BatchCompleted takes
> about the same amount of time for each execution (~2200) the Audit Logout is
> different and when the program slows down it takes numbers up to ~584263
> while a normal execution takes up to ~20296.
> What does this mean?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23q6Ow0XfEHA.224@.TK2MSFTNGP10.phx.gbl...
> > Start by using Profiler to see if you get the same execution plan between
> the executions. Depending on whether
> > you do or not, you can determine whether this is a SQL Server issue or a
> client app issue.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
> news:OiAfzBTfEHA.4092@.TK2MSFTNGP10.phx.gbl...
> > > Hi all,
> > > I've developed an app (C#) that connects to SQL Server 2000 without
> using
> > > thread pooling (using SqlConnection) and I run a query that returns
> ~4000
> > > tuples each time it runs and displays the results in a listview. The
> problem
> > > is that after I run it 3-4 times the results are read very slowly from
> the
> > > DB. The first times the results are displayed in 1-2 secs but the 4,5
> time
> > > it takes more than 2 minutes! What am I doing wrong? Are there any
> > > in-between buffers that need to be emptied?
> > >
> > > Thanks,
> > > -peter
> > >
> > >
> >
> >
>|||I'll try to post it there... Thanks anyway.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:upyMcDjfEHA.708@.TK2MSFTNGP09.phx.gbl...
> If you check Books Online, you will find following for the "Audit Logout"
event:
> Duration The approximate amount of time since the user logged in.
>
> But the SQL:BatchCompleted are the same, so this indicates that the query
executes in the same time, the added
> time for the logout is the time for the client to process the results
before it can logout from the SQL
> Server.
> I.e., the problem is with the client app (at least as far as I can see).
You might want to post this (with
> appropriate details) to an ADO.NET group...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
news:%23LQX4gZfEHA.3016@.tk2msftngp13.phx.gbl...
> > well i checked it with the profiler and while the SQL:BatchCompleted
takes
> > about the same amount of time for each execution (~2200) the Audit
Logout is
> > different and when the program slows down it takes numbers up to ~584263
> > while a normal execution takes up to ~20296.
> >
> > What does this mean?
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:%23q6Ow0XfEHA.224@.TK2MSFTNGP10.phx.gbl...
> > > Start by using Profiler to see if you get the same execution plan
between
> > the executions. Depending on whether
> > > you do or not, you can determine whether this is a SQL Server issue or
a
> > client app issue.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
> > news:OiAfzBTfEHA.4092@.TK2MSFTNGP10.phx.gbl...
> > > > Hi all,
> > > > I've developed an app (C#) that connects to SQL Server 2000 without
> > using
> > > > thread pooling (using SqlConnection) and I run a query that returns
> > ~4000
> > > > tuples each time it runs and displays the results in a listview. The
> > problem
> > > > is that after I run it 3-4 times the results are read very slowly
from
> > the
> > > > DB. The first times the results are displayed in 1-2 secs but the
4,5
> > time
> > > > it takes more than 2 minutes! What am I doing wrong? Are there any
> > > > in-between buffers that need to be emptied?
> > > >
> > > > Thanks,
> > > > -peter
> > > >
> > > >
> > >
> > >
> >
> >
>

Friday, March 23, 2012

Executing a queries contained in a column of a table IN SQL Server.

Hi,
I have a Table that contains SQL queries in one of its columns.I need to execute those query and finally want to retrive the result in another table i cannot use Cursors,Its working extremely slow, near about 1 min only for 2000 rows.Please tell me how can i minimize my time.Or any solution (without cursor) for such problem.Please help its very urgent.
e.g Say TableContainQuery(PKID,QueryField)
Now above table have 10000 of records,
I need to retrieve data from by executing queries contain in above table. I am using SQL Server.

Regards,
Dheeraj

Using SQL Server, queries that are slow are generally slow because there are not proper indexes on the tables involved.
Have you ensured that there are useful indexes on the tables involved?|||Yes,
There is a primary key in the table I suppose indexing will automatically done.
Regards,
Dheeraj Verma|||No, indexing, other than indexes you created, willnot automatically be done. If all your queries use only columns involved in the primary keys, then you are fine. However, if your query references other columns in joins or where clauses, you must add indexes yourself.

Executing A No. of queries thru another query

Dear Sir,

My database have a no. of tables. I have created separate sql files for different tables. Now i want to create all the tables by creating another sql file which will contain the individual sql files.

for example

USE DealSoft

EXEC("c:\SQLAccountTypes.sql")

EXEC("c:\\SQLAccounts.sql")

EXEC("c:\\SQLParties.sql")

EXEC command doesn't work this way. can u suggest the proper syntax.

with regards

wilfi

You could use xp_cmdshell for execute any external program and you could run osql script.sql from command line.

As result you could run:

Code Snippet

xp_cmdshell 'osql C:\yourScript.sql'

or (only in SQL Server 2005)

Code Snippet

xp_cmdshell 'sqlcmd C:\youScript.sql'

May be you need to configure account for executing external apps. You could use sp_xp_cmdshell_proxy_account stored procedure

|||

Wilfi,

If using SQL Server 2005, you might want to consider creating an SSIS package to perform such an action. With SQL Server 2000 that solution becomes "using a DTS package."

Another aspect of this matter would be to put your SQL scripts into stored procedures. You can have one stored procedure invoke multiple other stored procedures, similar to the BATCH action you have described. This plan works for any version of SQL Server, as far as I know.

Dan

|||

Dear Dan,

I would like to invoke my individual Stored Procedures thru a Master S.P. as suggested by you.

Can u tell me the syntax for the same with a small example(of Master S.P.).

thanking U.

With Regards,

wilfi

|||

Code Snippet

--Create procedures

CREATE PROCEDURE mySp1
as
BEGIN
PRINT 'Call To MySP1'
END
go

CREATE PROCEDURE mySp2
as
BEGIN
PRINT 'Call to MySP2'
END
go

CREATE PROCEDURE myMasterSP
as
BEGIN
EXEC mySP1
EXEC mySP2
END
go

-- Execute Master SP, you could do it any time after creating

myMasterSP

|||

Dear Sir,

Hearty Thanx for the immediate response. I could do as suggested by u.

with regards,

wilfi

|||

Konstantin,

Thanks! You beat me to it! ;-)

Dan

|||

Hey Konstantin, i was lookin for something related to inline store procs and saw your post..

do you know if those 2 last procs : mySP1 and mySP2 run async.

meaning, does the mySP2 proc waits for the mySP1 to be completed?.

It will be very helpful if you know!

anyways thanks in advance

Dave.

|||

Dave,

Sorry for butting in. All my experiences are that they run sequentially, in the order listed in the SP.

I would have all kinds of wrong answers in my computations were that not so.

Dan

|||

mySp1 and mySp2 run sync. Meaning the mySp2 wait for the mySp1 to be completed.

If you need async call, you could emulate this approach by using SQL Server Broker

Executing A No. of queries thru another query

Dear Sir,

My database have a no. of tables. I have created separate sql files for different tables. Now i want to create all the tables by creating another sql file which will contain the individual sql files.

for example

USE DealSoft

EXEC("c:\SQLAccountTypes.sql")

EXEC("c:\\SQLAccounts.sql")

EXEC("c:\\SQLParties.sql")

EXEC command doesn't work this way. can u suggest the proper syntax.

with regards

wilfi

You could use xp_cmdshell for execute any external program and you could run osql script.sql from command line.

As result you could run:

Code Snippet

xp_cmdshell 'osql C:\yourScript.sql'

or (only in SQL Server 2005)

Code Snippet

xp_cmdshell 'sqlcmd C:\youScript.sql'

May be you need to configure account for executing external apps. You could use sp_xp_cmdshell_proxy_account stored procedure

|||

Wilfi,

If using SQL Server 2005, you might want to consider creating an SSIS package to perform such an action. With SQL Server 2000 that solution becomes "using a DTS package."

Dan

|||

Dear Dan,

I would like to invoke my individual Stored Procedures thru a Master S.P. as suggested by you.

Can u tell me the syntax for the same with a small example(of Master S.P.).

thanking U.

With Regards,

wilfi

|||

Code Snippet

--Create procedures

CREATE PROCEDURE mySp1
as
BEGIN
PRINT 'Call To MySP1'
END
go

CREATE PROCEDURE mySp2
as
BEGIN
PRINT 'Call to MySP2'
END
go

CREATE PROCEDURE myMasterSP
as
BEGIN
EXEC mySP1
EXEC mySP2
END
go

-- Execute Master SP, you could do it any time after creating

myMasterSP

|||

Dear Sir,

Hearty Thanx for the immediate response. I could do as suggested by u.

with regards,

wilfi

|||

Konstantin,

Thanks! You beat me to it! ;-)

Dan

|||

Hey Konstantin, i was lookin for something related to inline store procs and saw your post..

do you know if those 2 last procs : mySP1 and mySP2 run async.

meaning, does the mySP2 proc waits for the mySP1 to be completed?.

It will be very helpful if you know!

anyways thanks in advance

Dave.

|||

Dave,

Sorry for butting in. All my experiences are that they run sequentially, in the order listed in the SP.

I would have all kinds of wrong answers in my computations were that not so.

Dan

|||

mySp1 and mySp2 run sync. Meaning the mySp2 wait for the mySp1 to be completed.

If you need async call, you could emulate this approach by using SQL Server Broker

Wednesday, March 7, 2012

execute remote stored procedure failed

I have set up a linked server to Sybase. I can run
distributed queries, but I'm trying to execute a stored
procedure on the remote server and I receive the
following message:
Error 7212: Could not execute procedure on remote server.
Any ideas as to what I'm doing wrong?
Thanks,
MTMT
Perhaps you want to look into permission issues
"MT" <anonymous@.discussions.microsoft.com> wrote in message
news:0d4b01c39e26$7ddf7c10$a301280a@.phx.gbl...
> I have set up a linked server to Sybase. I can run
> distributed queries, but I'm trying to execute a stored
> procedure on the remote server and I receive the
> following message:
> Error 7212: Could not execute procedure on remote server.
> Any ideas as to what I'm doing wrong?
> Thanks,
> MT
>
>
>|||Was there ever a satisfactory solution to this? I am having very similar problems when using a Sybase OLE DB linked server. No such problems when using an ODBC link
-- MT wrote: --
I have set up a linked server to Sybase. I can run
distributed queries, but I'm trying to execute a stored
procedure on the remote server and I receive the
following message
Error 7212: Could not execute procedure on remote server
Any ideas as to what I'm doing wrong
Thanks
M

execute query later

Hi NG
Im writing a web-interface that enables the user to update a DB. The
(non)queries takes heaps of time, and since my page "waits" for the query to
finish, wich is a pain. So basically I was wondering if there is kinda a
"update mytable set myrow=... LATER" kinda keyword or something, to make the
query execute whenever the sqlserver feels like it, but return "ok" right
away.
- Kasper"Kasper Birch Olsen" <kasper@.nospam.com> wrote in message
news:eYDSkU2ZFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hi NG
> Im writing a web-interface that enables the user to update a DB. The
> (non)queries takes heaps of time, and since my page "waits" for the query
to
> finish, wich is a pain. So basically I was wondering if there is kinda a
> "update mytable set myrow=... LATER" kinda keyword or something, to make
the
> query execute whenever the sqlserver feels like it, but return "ok" right
> away.
> - Kasper
>
As far as I know, there is no direct way of doing this.
One thing comes to mind... Have a simple table where you can drop the
information quickly. A SQL Server job can come along and check the table
every few minutes and then perform the work.
There are some caveats however. What if the data is bad, or SQL Server has
an error attempting to process the data. You have already told the web-user
that everything is ok, but in reality it is not. How do you notify the
user that there were problems.
You may need to do some architecturual work to get this to work the way you
want it to.
Rick Sawtell
MCT, MCSD, MCDBA|||Implement the sql update as a stored procedure. If you are using an ADO /
ADO.NET connection, then look executing the SP asynchronously. The SP can
send an email notification back to the user when it completes.
"Kasper Birch Olsen" <kasper@.nospam.com> wrote in message
news:eYDSkU2ZFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hi NG
> Im writing a web-interface that enables the user to update a DB. The
> (non)queries takes heaps of time, and since my page "waits" for the query
to
> finish, wich is a pain. So basically I was wondering if there is kinda a
> "update mytable set myrow=... LATER" kinda keyword or something, to make
the
> query execute whenever the sqlserver feels like it, but return "ok" right
> away.
> - Kasper
>|||Service Broker queue(s) come to mind. Need 2005 however or come up with
your own queue table.
William Stacey [MVP]
"Kasper Birch Olsen" <kasper@.nospam.com> wrote in message
news:eYDSkU2ZFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hi NG
> Im writing a web-interface that enables the user to update a DB. The
> (non)queries takes heaps of time, and since my page "waits" for the query
> to finish, wich is a pain. So basically I was wondering if there is kinda
> a "update mytable set myrow=... LATER" kinda keyword or something, to make
> the query execute whenever the sqlserver feels like it, but return "ok"
> right away.
> - Kasper
>

Sunday, February 19, 2012

Execute only and don't return results

quote:

> Is there an option in SQL Server to just execute a query but don't return
> it's results? The aim is to run a batch of queries to record the number

quote:

> reads they make, without having to return data back to the client, which
> will take some time.
> Thanks in advance.
> --
> Regards
> Ray Mond
>

|||That only turns off the 'x rows affected' message. I know of the SET
ROWCOUNT option, but would that affect the execution plan in any way? I
want the query to run to completion, I just don't want the results returned.
Regards
Ray Mond
"SriSamp" <ssampath@.sct.co.in> wrote in message
news:e4ZsCBa2DHA.2160@.TK2MSFTNGP12.phx.gbl...

quote:

> Check the SET NOCOUNT option.
> --
> HTH,
> SriSamp
> Please reply to the whole group only!
> http://www32.brinkster.com/srisamp
> "Ray Mond" <yeohray@.hotmail.com> wrote in message
> news:O$l1kcY2DHA.2680@.TK2MSFTNGP11.phx.gbl...
return[QUOTE]
> of
>

|||Using SET ROWCOUNT could be dangerous, since execution will stop after that
many rows are reached.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Ray Mond" <yeohray@.hotmail.com> wrote in message
news:eFyBi5a2DHA.2700@.tk2msftngp13.phx.gbl...

quote:

> That only turns off the 'x rows affected' message. I know of the SET
> ROWCOUNT option, but would that affect the execution plan in any way? I
> want the query to run to completion, I just don't want the results

returned.

quote:

> --
> Regards
> Ray Mond
> "SriSamp" <ssampath@.sct.co.in> wrote in message
> news:e4ZsCBa2DHA.2160@.TK2MSFTNGP12.phx.gbl...
> return
number[QUOTE]
which[QUOTE]
>

|||you might select count(*) instead of the other table columns... The client
will only get the number of rows that were selected.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"Ray Mond" <yeohray@.hotmail.com> wrote in message
news:O$l1kcY2DHA.2680@.TK2MSFTNGP11.phx.gbl...

quote:

> Is there an option in SQL Server to just execute a query but don't return
> it's results? The aim is to run a batch of queries to record the number

quote:

> reads they make, without having to return data back to the client, which
> will take some time.
> Thanks in advance.
> --
> Regards
> Ray Mond
>

|||I can't do this because this will affect the execution plan.
Regards
Ray Mond
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:%23$F2Dnc2DHA.2208@.TK2MSFTNGP12.phx.gbl...

quote:

> you might select count(*) instead of the other table columns... The client
> will only get the number of rows that were selected.
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.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
>
> "Ray Mond" <yeohray@.hotmail.com> wrote in message
> news:O$l1kcY2DHA.2680@.TK2MSFTNGP11.phx.gbl...
return[QUOTE]
> of
>

|||[posted and mailed, please reply in news]
Ray Mond (yeohray@.hotmail.com) writes:

quote:

> Is there an option in SQL Server to just execute a query but don't
> return it's results? The aim is to run a batch of queries to record the
> number of reads they make, without having to return data back to the
> client, which will take some time.

The best way is probably to insert the data into a table. Of course,
that will incur the cost of writing to disc, but that is probably
cheaper than to return to the client. At least you will get more
consistent performance, since you would not depend on network performance.
But you need to make sure that the database you are insering data into
is big enough, so that you results does not get distorted by auto-grow.
It may be more convenient to use SELECT INTO, than a pre-created table,
but creating a table SELECT INTO is more expensive than CREATE TABLE
and may cause some hundreds of reads on its own.
Whatever, don't use a table variable, because this could affect the
query plan, since you cannot get parallelism when you insert into a
table variable.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Execute only and don't return results

Is there an option in SQL Server to just execute a query but don't return
it's results? The aim is to run a batch of queries to record the number of
reads they make, without having to return data back to the client, which
will take some time.
Thanks in advance.
--
Regards
Ray MondCheck the SET NOCOUNT option.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Ray Mond" <yeohray@.hotmail.com> wrote in message
news:O$l1kcY2DHA.2680@.TK2MSFTNGP11.phx.gbl...
> Is there an option in SQL Server to just execute a query but don't return
> it's results? The aim is to run a batch of queries to record the number
of
> reads they make, without having to return data back to the client, which
> will take some time.
> Thanks in advance.
> --
> Regards
> Ray Mond
>|||That only turns off the 'x rows affected' message. I know of the SET
ROWCOUNT option, but would that affect the execution plan in any way? I
want the query to run to completion, I just don't want the results returned.
--
Regards
Ray Mond
"SriSamp" <ssampath@.sct.co.in> wrote in message
news:e4ZsCBa2DHA.2160@.TK2MSFTNGP12.phx.gbl...
> Check the SET NOCOUNT option.
> --
> HTH,
> SriSamp
> Please reply to the whole group only!
> http://www32.brinkster.com/srisamp
> "Ray Mond" <yeohray@.hotmail.com> wrote in message
> news:O$l1kcY2DHA.2680@.TK2MSFTNGP11.phx.gbl...
> > Is there an option in SQL Server to just execute a query but don't
return
> > it's results? The aim is to run a batch of queries to record the number
> of
> > reads they make, without having to return data back to the client, which
> > will take some time.
> >
> > Thanks in advance.
> >
> > --
> > Regards
> > Ray Mond
> >
> >
>|||Using SET ROWCOUNT could be dangerous, since execution will stop after that
many rows are reached.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Ray Mond" <yeohray@.hotmail.com> wrote in message
news:eFyBi5a2DHA.2700@.tk2msftngp13.phx.gbl...
> That only turns off the 'x rows affected' message. I know of the SET
> ROWCOUNT option, but would that affect the execution plan in any way? I
> want the query to run to completion, I just don't want the results
returned.
> --
> Regards
> Ray Mond
> "SriSamp" <ssampath@.sct.co.in> wrote in message
> news:e4ZsCBa2DHA.2160@.TK2MSFTNGP12.phx.gbl...
> > Check the SET NOCOUNT option.
> > --
> > HTH,
> > SriSamp
> > Please reply to the whole group only!
> > http://www32.brinkster.com/srisamp
> >
> > "Ray Mond" <yeohray@.hotmail.com> wrote in message
> > news:O$l1kcY2DHA.2680@.TK2MSFTNGP11.phx.gbl...
> > > Is there an option in SQL Server to just execute a query but don't
> return
> > > it's results? The aim is to run a batch of queries to record the
number
> > of
> > > reads they make, without having to return data back to the client,
which
> > > will take some time.
> > >
> > > Thanks in advance.
> > >
> > > --
> > > Regards
> > > Ray Mond
> > >
> > >
> >
> >
>|||you might select count(*) instead of the other table columns... The client
will only get the number of rows that were selected.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"Ray Mond" <yeohray@.hotmail.com> wrote in message
news:O$l1kcY2DHA.2680@.TK2MSFTNGP11.phx.gbl...
> Is there an option in SQL Server to just execute a query but don't return
> it's results? The aim is to run a batch of queries to record the number
of
> reads they make, without having to return data back to the client, which
> will take some time.
> Thanks in advance.
> --
> Regards
> Ray Mond
>|||I can't do this because this will affect the execution plan.
--
Regards
Ray Mond
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:%23$F2Dnc2DHA.2208@.TK2MSFTNGP12.phx.gbl...
> you might select count(*) instead of the other table columns... The client
> will only get the number of rows that were selected.
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.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
>
> "Ray Mond" <yeohray@.hotmail.com> wrote in message
> news:O$l1kcY2DHA.2680@.TK2MSFTNGP11.phx.gbl...
> > Is there an option in SQL Server to just execute a query but don't
return
> > it's results? The aim is to run a batch of queries to record the number
> of
> > reads they make, without having to return data back to the client, which
> > will take some time.
> >
> > Thanks in advance.
> >
> > --
> > Regards
> > Ray Mond
> >
> >
>|||[posted and mailed, please reply in news]
Ray Mond (yeohray@.hotmail.com) writes:
> Is there an option in SQL Server to just execute a query but don't
> return it's results? The aim is to run a batch of queries to record the
> number of reads they make, without having to return data back to the
> client, which will take some time.
The best way is probably to insert the data into a table. Of course,
that will incur the cost of writing to disc, but that is probably
cheaper than to return to the client. At least you will get more
consistent performance, since you would not depend on network performance.
But you need to make sure that the database you are insering data into
is big enough, so that you results does not get distorted by auto-grow.
It may be more convenient to use SELECT INTO, than a pre-created table,
but creating a table SELECT INTO is more expensive than CREATE TABLE
and may cause some hundreds of reads on its own.
Whatever, don't use a table variable, because this could affect the
query plan, since you cannot get parallelism when you insert into a
table variable.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Friday, February 17, 2012

execute multiple queries over a single connection

Hi!
Is SQL Server 2000, just a toy?
Accordind validation tests it needs 30.000 connections to move
170 rows from Linked Server.
JackWe need more information. How do you "move 170 rows from Linked Server"? Can
you show us some code?
How did you determine that 30000 connection were needed?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jack" <none@.INVALIDmail.com> wrote in message news:AYa%e.137$xG6.129@.read3.inet.fi...[vbcol
=seagreen]
> Hi!
> Is SQL Server 2000, just a toy?
> Accordind validation tests it needs 30.000 connections to move
> 170 rows from Linked Server.
> Jack
>[/vbcol]|||Then it is a BizTalk issue. Perhaps BizTalk isn't very intelligent in how it
interacts with SQL
Server, or BizTalk isn't used in the most efficient way? I can't tell as I d
on't know anything about
BizTalk.
I suggest you raise the issue in a BizTalk group, as they will understand wh
at you want to achieve,
and can respond to how you try to achieve that goal. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jack" <Jack@.none.com> wrote in message news:Y3T0f.251$865.187@.read3.inet.fi...en">
> Well it is this BizTalk Orchestration
> http://msdn.microsoft.com/biztalk/
> BTW, it is PowerToys in their own words ;)
>
> -- Original Message --
> From: "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> Newsgroups: microsoft.public.sqlserver.server
> Sent: Friday, September 30, 2005 5:51 PM
> Subject: Re: execute multiple queries over a single connection
>
>

execute multiple queries over a single connection

Hi!
Is SQL Server 2000, just a toy?
Accordind validation tests it needs 30.000 connections to move
170 rows from Linked Server.
Jack
We need more information. How do you "move 170 rows from Linked Server"? Can you show us some code?
How did you determine that 30000 connection were needed?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jack" <none@.INVALIDmail.com> wrote in message news:AYa%e.137$xG6.129@.read3.inet.fi...
> Hi!
> Is SQL Server 2000, just a toy?
> Accordind validation tests it needs 30.000 connections to move
> 170 rows from Linked Server.
> Jack
>
|||Then it is a BizTalk issue. Perhaps BizTalk isn't very intelligent in how it interacts with SQL
Server, or BizTalk isn't used in the most efficient way? I can't tell as I don't know anything about
BizTalk.
I suggest you raise the issue in a BizTalk group, as they will understand what you want to achieve,
and can respond to how you try to achieve that goal. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jack" <Jack@.none.com> wrote in message news:Y3T0f.251$865.187@.read3.inet.fi...
> Well it is this BizTalk Orchestration
> http://msdn.microsoft.com/biztalk/
> BTW, it is PowerToys in their own words ;)
>
> -- Original Message --
> From: "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> Newsgroups: microsoft.public.sqlserver.server
> Sent: Friday, September 30, 2005 5:51 PM
> Subject: Re: execute multiple queries over a single connection
>
>

Tuesday, March 27, 2012

Executing more than one stored procedures in a DataReader

Executing MDX queries from inside SQL Server Stored Procedures (SQL Server 2005)

Executing large result queries

Executing large result queries

Executing large result queries

Friday, March 23, 2012

Executing a queries contained in a column of a table IN SQL Server.

Executing A No. of queries thru another query

Executing A No. of queries thru another query

Wednesday, March 7, 2012

execute remote stored procedure failed

execute query later

Sunday, February 19, 2012

Execute only and don't return results

Execute only and don't return results

Friday, February 17, 2012

execute multiple queries over a single connection

execute multiple queries over a single connection

execute multiple queries over a single connection

EXECUTE AS question

Blog Archive

About Me