Exec time for a query to run in QA?

Hi All
I want to see how long it takes for my query to execute in QA. How do i do
that? Thanx in advanceThere is an Execution Time element at the lower right hand part of the
screen in QA. Optionally you could turn on Client Statistics, Statistics
Time or using GETDATE() before and after your query.
HTH
Jerry
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:00E0F137-A60C-44F6-964B-23C0D27A89C9@.microsoft.com...
> Hi All
> I want to see how long it takes for my query to execute in QA. How do i do
> that? Thanx in advance|||When it's done, look in the lower right corner of the status bar, the third
box from the right shows elapsed time.
You can also do another tactic, like
SELECT @.dt = CURRENT_TIMESTAMP
-- query here
SELECT DATEDIFF(MS, @.dt, CURRENT_TIMESTAMP)
You can also look at SET STATISTICS TIME and SET STATISTICS IO topics in
books online to see how to return different stats about the query or
queries. Showing execution plan and server/client statictics can also be
useful.
http://www.aspfaq.com/2245
A
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:00E0F137-A60C-44F6-964B-23C0D27A89C9@.microsoft.com...
> Hi All
> I want to see how long it takes for my query to execute in QA. How do i do
> that? Thanx in advance|||MittyKom wrote:
> Hi All
> I want to see how long it takes for my query to execute in QA. How do
> i do that? Thanx in advance
Best way is to use Profiler. But you can use SET STATISTICS TIME ON /
OFF from Query Analyzer to see the execution. STATISTICS IO is also
useful. Try this:
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
SELECT * FROM pubs.dbo.authors
GO
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO
David Gugick
Quest Software
www.imceda.com
www.quest.com

Exec Stored Proc (C#) - the Size property has an invalid size of 0

Hi All
I am trying to execute a stored procedure that does a very simple
lookup and returns a text field. However, when I try to execute it, I
am getting a rather strange error that I can't seem to fix!
There is defiantely information coming back as I have tested this in
Query Analyzer. The error actuall comes on my oCmd.ExecuteNonQuery();
String[1]: the Size property has an invalid size of 0.
Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more
information about the error and where it originated in the code.
Exception Details: System.InvalidOperationException: String[1]: the
Size property has an invalid size of 0.
Many thanks in advance for your help
Darren
STORED PROC CODE
=================
ALTER PROCEDURE [dbo].[sp_ReadSessionXML]
-- Add the parameters for the stored procedure here
@.iID int,
@.tXML text = null output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT @.tXML = [XML] FROM T_Requests WHERE ResponseID = @.iID
pRINT @.tXML
END
C# CODE
=======
SqlConnection oConn = new SqlConnection();
oConn.ConnectionString = m_sConnectionString;
oConn.Open();
SqlCommand oCmd = new SqlCommand("sp_ReadSessionXML",
oConn);
oCmd.Connection = oConn;
oCmd.CommandType = CommandType.StoredProcedure;
SqlParameter spID = oCmd.Parameters.Add("@.iID",
SqlDbType.Int);
spID.Direction = ParameterDirection.Input;
spID.Value = iSQLCacheID;
SqlParameter spXML = oCmd.Parameters.Add("@.tXML",
SqlDbType.Text);
spXML.Direction = ParameterDirection.Output;
oCmd.ExecuteNonQuery();
oConn.Close();
XmlDocument xdDBCache = new XmlDocument();
xdDBCache.LoadXml(oCmd.Parameters["@.tXML"].Value.ToString());
return xdDBCache;
}Just a guess, but since the sp is going to return data, I don't think you
should use ExecuteNonQuery. ExecuteNonQuery is used for executing statements
that don't return a result set (like UPDATE or DELETE).
"daz_oldham" wrote:

> Hi All
> I am trying to execute a stored procedure that does a very simple
> lookup and returns a text field. However, when I try to execute it, I
> am getting a rather strange error that I can't seem to fix!
> There is defiantely information coming back as I have tested this in
> Query Analyzer. The error actuall comes on my oCmd.ExecuteNonQuery();
> String[1]: the Size property has an invalid size of 0.
> Description: An unhandled exception occurred during the execution of
> the current web request. Please review the stack trace for more
> information about the error and where it originated in the code.
> Exception Details: System.InvalidOperationException: String[1]: the
> Size property has an invalid size of 0.
> Many thanks in advance for your help
> Darren
> STORED PROC CODE
> =================
> ALTER PROCEDURE [dbo].[sp_ReadSessionXML]
> -- Add the parameters for the stored procedure here
> @.iID int,
> @.tXML text = null output
> AS
> BEGIN
> -- SET NOCOUNT ON added to prevent extra result sets from
> -- interfering with SELECT statements.
> SET NOCOUNT ON;
> -- Insert statements for procedure here
> SELECT @.tXML = [XML] FROM T_Requests WHERE ResponseID = @.iID
> pRINT @.tXML
> END
>
> C# CODE
> =======
> SqlConnection oConn = new SqlConnection();
> oConn.ConnectionString = m_sConnectionString;
> oConn.Open();
> SqlCommand oCmd = new SqlCommand("sp_ReadSessionXML",
> oConn);
> oCmd.Connection = oConn;
> oCmd.CommandType = CommandType.StoredProcedure;
> SqlParameter spID = oCmd.Parameters.Add("@.iID",
> SqlDbType.Int);
> spID.Direction = ParameterDirection.Input;
> spID.Value = iSQLCacheID;
> SqlParameter spXML = oCmd.Parameters.Add("@.tXML",
> SqlDbType.Text);
> spXML.Direction = ParameterDirection.Output;
> oCmd.ExecuteNonQuery();
> oConn.Close();
> XmlDocument xdDBCache = new XmlDocument();
> xdDBCache.LoadXml(oCmd.Parameters["@.tXML"].Value.ToString());
> return xdDBCache;
> }
>|||daz_oldham (Darren.Ratcliffe@.gmail.com) writes:
> There is defiantely information coming back as I have tested this in
> Query Analyzer. The error actuall comes on my oCmd.ExecuteNonQuery();
> String[1]: the Size property has an invalid size of 0.
> Description: An unhandled exception occurred during the execution of
> the current web request. Please review the stack trace for more
> information about the error and where it originated in the code.
The error message is unknown to me, and I can't say where it's coming
from. However, I do spot an error:

> @.tXML text = null output
This won't fly. text for output parameters is bound to fail. You
cannot assign to variables of the type text.
If you are on SQL 2005, use varchar(MAX) instead. Or even better the
xml data type.
If you are on SQL 2000, return the XML column as a result set instead.
(In which case you must use something different than ExecuteNonQuery
to retrieve the data.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Mark Williams (MarkWilliams@.discussions.microsoft.com) writes:
> Just a guess, but since the sp is going to return data, I don't think
> you should use ExecuteNonQuery. ExecuteNonQuery is used for executing
> statements that don't return a result set (like UPDATE or DELETE).
But Daz's procedure does not return any result set, but returns data in
an OUTPUT parameter (or would have returned, had he chosen a data type
that is eligible for output parameters). The procedure also includes a
PRINT statement. Both of these are fine with ExecuteNonQuery. (To get
the data from the PRINT statement you need an InfoMessage event handler.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland
I have changed this to get the value out via a data reader, and it is
spot on.
I have never been aware in the past about having a text value as an
output parameter, but I know now!
I am currently using SQL 2000 so can't take advantage of the added XML
benefits in 2005 which is a shame really.
Many thanks for your help - and everyone else too.
Regards
Darren

Exclusive Insert Lock on a Table

Hello All!

I want to perform 4 or 5 statements as a transaction but I need to make sure that during this complete transaction no one else inserts or deletes records from a table named SomeTable.

So how can I lock MyTable at the beggining of the transaction so that during my transaction no one else can insert or delete anything in table SomeTable?

Thanks!

David

You can open an transaction explicitly so other transactions are placed in a queue. Use BEGIN TRAN /COMMIT TRAN. Read up books on line for more info on transactions.

|||

Yup, I could read about this as you suggest, but it's certainly pretty complex so I was hoping someone experienced and knowleadgable would give me the answer.

Your post but it doesn't answer my question. I know how to create an explicit transaction.

My question is how do I lock a table during a transaction so that no one else inserts or deletes records from it.

I don't think that by just creating an explicit transaction and reading a row from MyTable this will lock the complete table.

|||

neutrino:

I don't think that by just creating an explicit transaction and reading a row from MyTable this will lock the complete table.

Yes it does. And thats all you have to do. Try this scenario:

(1) In a query analyzer window run this script

BEGIN TRAN

INSERT INTO TheTable ... <complete the rest of the INSERT>

(2) Open another query analyzer and do a SELECT * From TheTable. You will see that your query will be in "suspended" status waiting for the Insert to finish since you opened a Transaction explicitly.

(3) Now go back to the first window and run this:

Commit

(4) Check the second window and you will see results for your SELECT *...

Basically your BEGIN TRAN has opened a transaction --> locked the table for any other commited transactions (Even reads). As soon as you commit the transaction the lock on the table is released and others can read from the table. You can by pass this and do a dirty read by using NOLOCK hint. Not always suggested unless your business requirements allow you to.

|||

Thanks. This tells me how to lock a table by creating a transaction and doing and insert statement. However, in my scenario I need to lock the table innitially before doing any inserts to it and I want it to remain locked until the transaction ends (even if I don't do any inserts). I don't want any other transaction to be able to insert any rows until my transaction finishes.

I think that what I need is to set the transaction isolation level to SERIALIZABLE.

I found this: http://msdn2.microsoft.com/en-us/library/ms173763.aspx

I'll be trying it later and will post results.

David

|||

I do find your query analyzer excercise extremelly useful. I will use it to test my locking 'theories". Thanks!

|||

ndinakar:

neutrino:

I don't think that by just creating an explicit transaction and reading a row from MyTable this will lock the complete table.

Yes it does. And thats all you have to do. Try this scenario:

(1) In a query analyzer window run this script

BEGIN TRAN

INSERT INTO TheTable ... <complete the rest of the INSERT>

(2) Open another query analyzer and do a SELECT * From TheTable. You will see that your query will be in "suspended" status waiting for the Insert to finish since you opened a Transaction explicitly.

(3) Now go back to the first window and run this:

Commit

(4) Check the second window and you will see results for your SELECT *...

Basically your BEGIN TRAN has opened a transaction --> locked the table for any other commited transactions (Even reads). As soon as you commit the transaction the lock on the table is released and others can read from the table. You can by pass this and do a dirty read by using NOLOCK hint. Not always suggested unless your business requirements allow you to.

Actually, that doesn't lock the whole table. It locks a portion of the table, but your SELECT requires access to the entire table (including the locked portion), so it has to wait. If you have the table indexed, and the SELECT can use the index to determine that it doesn't need the locked portion then it won't delay the SELECT. In addition, a second INSERT should complete without being delayed.

To the original poster, what you are asking for is rather uncommon, and you are best to try and avoid doing what you are asking to do. Perhaps you need to rethink why you want the table locked, and what you are trying to accomplish by doing so. Usually there is a much better way of achieving that.

|||

Motley:

To the original poster, what you are asking for is rather uncommon, and you are best to try and avoid doing what you are asking to do. Perhaps you need to rethink why you want the table locked, and what you are trying to accomplish by doing so. Usually there is a much better way of achieving that.

You are totally right. This was actually what I did. I re-thought the process and found a better way that doesn't require the table lock.

But anyways it was a great learning experience.

Thanks all for your support.

David

|||

ndinakar:

neutrino:

I don't think that by just creating an explicit transaction and reading a row from MyTable this will lock the complete table.

Yes it does. And thats all you have to do. Try this scenario:

It actually doesn't but the rest of your post was really helpful. Thank you.

Exclude HTML Tags in my Search

Hi all;
I would like to exclude HTML tags in my search criteria at my full-text
search i.e. when I look for for body word I desire the search to return the
body words which included in the body field but I don't want to include
<body>or </body>.
Thank you
This can't be done easily. The best way to fix this is to convert your html
content to text content using a html parser or filtdump -b (there may be
licensing restrictions with this).
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"yaser" <yaser.abu-khudier@.hotmail.com> wrote in message
news:%23pGqH$3FHHA.4712@.TK2MSFTNGP04.phx.gbl...
> Hi all;
>
> I would like to exclude HTML tags in my search criteria at my full-text
> search i.e. when I look for for body word I desire the search to return
> the body words which included in the body field but I don't want to
> include <body>or </body>.
>
> Thank you
>
>
|||Thank you so much Hilary , I have found another method to solve the problem
by
replacing the html tags with spaces before query them from the DB (ntext),
Thanks a lot for your assist
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eKJn5L6FHHA.3268@.TK2MSFTNGP04.phx.gbl...
> This can't be done easily. The best way to fix this is to convert your
> html content to text content using a html parser or filtdump -b (there may
> be licensing restrictions with this).
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "yaser" <yaser.abu-khudier@.hotmail.com> wrote in message
> news:%23pGqH$3FHHA.4712@.TK2MSFTNGP04.phx.gbl...
>
|||Hello yaser,
I thought that if you set the doc type to HTML the tags are ignored. We had
this issue with searches for colours finding matches in FONT definitions.
Changing the doc type to HTML solved the problem
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
[vbcol=seagreen]
> Thank you so much Hilary , I have found another method to solve the
> problem
> by
> replacing the html tags with spaces before query them from the DB
> (ntext),
> Thanks a lot for your assist
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eKJn5L6FHHA.3268@.TK2MSFTNGP04.phx.gbl...
|||Hi Simon;
is this able to be done even if I use full-text search on column with data
type ntext or I have to keep this column as binary such as (varbinary(max)),
because I know that we specify the TYPE COLUMN key word in the creation
statement of the fulltext index if we are searching in a binary column only
(and in your solution I have to specify the column type to HTML). By the way
do you mean the same for setting the doc type to HTML do you mean somthing
like this:
CREATE FULLTEXT INDEX ON Production.Document (Document TYPE COLUMN HTML) KEY
INDEX PK_Document_DocumentID ON AWCatalog WITH CHANGE_TRACKING AUTO;
Is this going to ignore html tags in my search?
Thanks a lot for your help and support
|||Hi yaser,
I am interested to know if you came up with a solution to this as I am
trying to do exactly the same thing. I will more than likely be converting
our textual data to binary data and specifying the extension as html.
Another thought that I had though - could a person not use an "HTML" word
breaker as such and then specify the language as HTML. I will look into and
let you know if I found a way of doing this.
"yaser" wrote:

> Hi Simon;
> is this able to be done even if I use full-text search on column with data
> type ntext or I have to keep this column as binary such as (varbinary(max)),
> because I know that we specify the TYPE COLUMN key word in the creation
> statement of the fulltext index if we are searching in a binary column only
> (and in your solution I have to specify the column type to HTML). By the way
> do you mean the same for setting the doc type to HTML do you mean somthing
> like this:
>
> CREATE FULLTEXT INDEX ON Production.Document (Document TYPE COLUMN HTML) KEY
> INDEX PK_Document_DocumentID ON AWCatalog WITH CHANGE_TRACKING AUTO;
>
> Is this going to ignore html tags in my search?
> Thanks a lot for your help and support
>
>
>
|||Sorry to be late in reply;
My final decision was building two columns in the database one with html
tags and the other without html tags so when I perform my search I do it
over the column that doesn't has any html tags, I found this the easiest and
fastest technique.
Hope this will help you
|||Hello yaser,
Yes it will. And yes you do have to store it as a binary type.
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> Hi Simon;
> is this able to be done even if I use full-text search on column with
> data type ntext or I have to keep this column as binary such as
> (varbinary(max)), because I know that we specify the TYPE COLUMN key
> word in the creation statement of the fulltext index if we are
> searching in a binary column only (and in your solution I have to
> specify the column type to HTML). By the way do you mean the same for
> setting the doc type to HTML do you mean somthing like this:
> CREATE FULLTEXT INDEX ON Production.Document (Document TYPE COLUMN
> HTML) KEY INDEX PK_Document_DocumentID ON AWCatalog WITH
> CHANGE_TRACKING AUTO;
> Is this going to ignore html tags in my search?
> Thanks a lot for your help and support
>
|||thanks a lot simon i will try to test it, using this method will give better
performance and less storage.
thanks for your hel and support
|||Hi, you can use Regex in C# like this:
[Code]
using System.Text.RegularExpressions;
//..
const string HTML_TAG_PATTERN = "<.*?>";
protected string StripHTML(string strInputString)
{
return Regex.Replace(strInputString, HTML_TAG_PATTERN,
string.Empty);
}
[Code]
"yaser" wrote:

> thanks a lot simon i will try to test it, using this method will give better
> performance and less storage.
> thanks for your hel and support
>
>

Exception Handling

Hi All

I m new to the sqlserver.In Oracle we can handle exceptions like this

declare
name varchar(20);
begin
select ename into name from emp where eno=&eno;
dbms_output.put_line(name);
exception
when no_data_found then
dbms_output.put_line('No Entry');
end;
/

We will get the message No Entry When corrsponding employee number dosent exists.

In Sqlserver how to handle these things.

Specifically my requirement is with select statement(Like above program)
Waiting for valuable suggestions

Baba

You can do in sqlserver using @.@.Rowcount

select ename into name from emp where eno=&eno

if((select @.@.Rowcount)<= 0)

print 'No data'

The following explains the errorhandling in simple terms

http://www.sqlteam.com/article/handling-errors-in-stored-procedures

|||

In SQL Server this situation never treated as exception, There is a workaround available - here it is,

Code Snippet

Declare @.Sno as int;

Declare @.name as varchar(100)

Set @.Sno=4

Select @.name=name from sysobjects Where id=@.Sno

If @.@.RowCount = 0

Print 'No Entry'

Else

Print @.Name

|||Hi manivannan

I tried like this before only . I got the output

Thank u for u r valuable reply.

Baba

Excel Linked Server Problem

To All

I'm using the following code to create a Linked Excel server.

EXEC sp_addlinkedserver 'ExcelSource',

'Jet 4.0',

'Microsoft.Jet.OLEDB.4.0',

'\\schuette-dc\DriveH\Temp\EDI\CSCNEDI.xls',

NULL,

'Excel 8.0';

GO

--

The server create correctly, but when I try the following

SELECT * FROM ExcelSource...CSCN

I get the following error

-

[OLE/DB provider returned message: Unspecified error]

OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].

Msg 7399, Level 16, State 1, Line 15

OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.

-

I have verified that i have full access to the "Temp" directory. I have even tried creating a "Named Range" in the spreadsheet with no success.

Thanks

David Davis

Tried on XP SP2 machine and it works fine for me. Try using OpenRowset and see if it works

select * from OpenRowset( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=\\schuette-dc\DriveH\Temp\EDI\CSCNEDI.xls', 'select * from CSCN')

OpenRowset documentation: http://msdn2.microsoft.com/en-us/library/ms190312.aspx

OpenQuery documentation: http://msdn2.microsoft.com/en-us/library/ms188427.aspx

Also take a look at this : http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/6fbf566977fe8504/5871e88b33c1445b?lnk=st&q=OLE+DB+error+trace+%5BOLE%2FDB+Provider+%27Microsoft.Jet.OLEDB.4.0%27+IDBInitialize%3A%3AInitialize+returned+0x80004005%3A+%5D.&rnum=8&hl=en#5871e88b33c1445b

Hope this helps

|||

Raj

I tried the statement and get the same error message.

Thanks

David Davis

|||Hi, anyone solved this problem yet?

My error is the same. I have seen many reports of this problem but no answers.

Thanks.