Profiler and set fmtonly On issue

Hi,
why do I have a lot of
set fmtonly On, the sql phrase and then set fmtonly Off
example :
SET FMTONLY ON Select PROD_VAT_CODE From Product Where PROD_MANF_SKU =
'Q2477A#401'
SET FMTONLY OFF
I know that turning it on means retuns columns info with no data back. So it
is called when c alling A SP that has a simple T-SQL phrase as the example
in my trace in profilee and how can I disable so it will not be included in
the trace results
set fmtonly off%
Most probably your API (probably ADO) that wants to retrieve meta-data for the query before the
actual data is returned. I.e., a matter of how you are using ADO. I suggest you post to an ADO
group, including a small comment or slimmed-down-code-sample showing your current coding technique.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SalamElias" <eliassal@.online.nospam> wrote in message
news:FCE31D96-46D0-4659-B4C0-DF95EEA53EE2@.microsoft.com...
> Hi,
> why do I have a lot of
> set fmtonly On, the sql phrase and then set fmtonly Off
> example :
> SET FMTONLY ON Select PROD_VAT_CODE From Product Where PROD_MANF_SKU =
> 'Q2477A#401'
> SET FMTONLY OFF
> I know that turning it on means retuns columns info with no data back. So it
> is called when c alling A SP that has a simple T-SQL phrase as the example
> in my trace in profilee and how can I disable so it will not be included in
> the trace results
> set fmtonly off%
>
|||SalamElias wrote:
> Hi,
> why do I have a lot of
> set fmtonly On, the sql phrase and then set fmtonly Off
> example :
> SET FMTONLY ON Select PROD_VAT_CODE From Product Where PROD_MANF_SKU =
> 'Q2477A#401'
> SET FMTONLY OFF
> I know that turning it on means retuns columns info with no data
> back. So it is called when c alling A SP that has a simple T-SQL
> phrase as the example
> in my trace in profilee and how can I disable so it will not be
> included in the trace results
> set fmtonly off%
I've seen the problem in Delphi client applications that use ADO. When
you discover how to turn it off in your code (regardless of what you
wrote it in), please post the solution here so I can inform my
colleagues.
Thanks.
David Gugick
|||I never do any manipulation using the set fmtonly. It is a straight forward
asp ADO code
(open conn, give it a T-SQL or a stored procedure, populate parameters and
get back the result using a dataset or call execute methode.
For the T-SQL I gave as an example, her is the relevant piece of code :
objConn.Open "Provider=SQLOLEDB;Password=user;Persist Security Info=True; _
User ID=user;Initial Catalog=PETC;Data Source=myserver"
cmdSQL.ActiveConnection = objConn
Then I have a record set which I loop through, in the loopi Execute the
following
sstrsql = "Select PROD_VAT_CODE From Product Where PROD_MANF_SKU = '" &
OrderForm.items(i)("SKU") & "'"
cmdSQL.CommandText = sstrsql
Set rsSKUVat = cmdSQL.Execute
"Tibor Karaszi" wrote:

> Most probably your API (probably ADO) that wants to retrieve meta-data for the query before the
> actual data is returned. I.e., a matter of how you are using ADO. I suggest you post to an ADO
> group, including a small comment or slimmed-down-code-sample showing your current coding technique.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "SalamElias" <eliassal@.online.nospam> wrote in message
> news:FCE31D96-46D0-4659-B4C0-DF95EEA53EE2@.microsoft.com...
>
>
|||Just out of curiousity, why are you persisting security info? Also,
there are a number of properties that you can set on the resultset and
command objects. Can you provide that information as well?
David Gugick
Imceda Software
www.imceda.com
SalamElias wrote:[vbcol=seagreen]
> I never do any manipulation using the set fmtonly. It is a straight
> forward asp ADO code
> (open conn, give it a T-SQL or a stored procedure, populate
> parameters and get back the result using a dataset or call execute
> methode.
> For the T-SQL I gave as an example, her is the relevant piece of code
> : ---
> objConn.Open "Provider=SQLOLEDB;Password=user;Persist Security
> Info=True; _ User ID=user;Initial Catalog=PETC;Data Source=myserver"
> cmdSQL.ActiveConnection = objConn
> Then I have a record set which I loop through, in the loopi Execute
> the following
> sstrsql = "Select PROD_VAT_CODE From Product Where PROD_MANF_SKU = '"
> & OrderForm.items(i)("SKU") & "'"
> cmdSQL.CommandText = sstrsql
> Set rsSKUVat = cmdSQL.Execute
> "Tibor Karaszi" wrote:
|||What do you mean by persisiting scurity info?Secondly, can you you indicate
the different parameters that are usefull to use in commands or datasets
Thanks a lot
"David Gugick" wrote:

> Just out of curiousity, why are you persisting security info? Also,
> there are a number of properties that you can set on the resultset and
> command objects. Can you provide that information as well?
> --
> David Gugick
> Imceda Software
> www.imceda.com
> SalamElias wrote:
>
>
|||Profiler and set fmtonly On issue|||Thanks for the info, but would you please can you you
indicate the different parameters that are usefull to use in commands
or datasets Thanks a lot
"David Gugick" wrote:

> SalamElias wrote:
>
> What parameters are you setting for the recordset object. Ther are
> settings for cursor location and type in particular.
> The persist is in your connect string as you posted it:
> objConn.Open "Provider=SQLOLEDB;Password=user; Persist Security
> Info=True; _
> User ID=user;Initial Catalog=PETC;Data Source=myserver"
> You should read about that option if you're not sure why it's there.
> From MSDN:
> "Remarks
> If Persist Security Info is set to False at the time the data source is
> initialized, the data source cannot persist sensitive authentication
> information. Furthermore, a call to a property that contains sensitive
> authentication information, such as a password, returns a default value
> instead of the actual password.
> After the data source has been uninitialized, sensitive information that
> was set when the data source was initialized with the Persist Security
> Info property set to False still cannot be obtained from the Properties
> collection or by persisting the uninitialized data source object.
> However, new properties set after the data source object has been
> uninitialized can be persisted or obtained if Persist Security Info is
> set to True.
> Before the data source is initialized for the first time, sensitive
> information can be obtained from the Properties collection, and can be
> persisted, regardless of the setting of the Persist Security Info
> property. Therefore, sensitive applications should avoid passing
> uninitialized data source objects.
>
> Note The recommended method for connecting to an instance of Microsoft?
> SQL Server? 2000 is to use Windows Authentication mode."
>
>
> --
> David Gugick
> Imceda Software
> www.imceda
|||SalamElias wrote:
> Thanks for the info, but would you please can you you
> indicate the different parameters that are usefull to use in commands
> or datasets Thanks a lot
>
Can you post what you are using please.
Generally, with ADO, I like to use forward-only, read-only, server-side
cursors. They are the fastest when minimal row processing on the client
is required. If you need to perform a lot of processing on each row in
the results, then I might move over to a client-side cursor just to get
the rows off the server more quickly.
David Gugick
Imceda Software
www.imceda.com

Produce data -> Store in table?

Hi!

I'm quite new to T-SQL and is about to build a small reporting db using SQL.
Most of the data I can move with normal INSERT INTO ... SELECT, but there are some tables that I want to
produce using T-SQL. For example I want to build a Date table like..

Date
Year
Quarter
Month
WeekDay
...

With some precalculated values for each date.

I've searched the forum but have not found any information on how to produce table contents in a good manner. I would appreciate if someone would have the time to point me in the right direction. I do not want to do this by code in my application.

My first thought is to use some kind of Insert Cursor in a While loop...

Pseudo:

declare cursor ex for 'Insert table ... '

while
begin

(produce data)
insert data

end

close cursor

While browsing the net I've got the feeling that you use cursor less in SQL Server than in other db-engines...

Have a nice day!

You could use WHILE loop without cursor:

Code Snippet

create table dates

(

Year int,

Quarter int,

Month int,

WeekDay int,

SomeData decimal

)

go

declare @.start_date datetime

declare @.end_date datetime

set @.start_date = '2007-01-01'

set @.end_date = getdate()

while @.start_date<@.end_date

begin

set @.start_date = dateadd(day, 1, @.start_date)

insert into dates values(

datepart(year,@.start_date),

datepart(quarter,@.start_date),

datepart(month,@.start_date),

datepart(weekday,@.start_date),

rand() --Just for demo

)

end

go

select * from Dates

|||

Best have a calendar table (you can add all the nessasary columns here), fill the table with required date range, join with your table & use the required calculations,

Code Snippet

create table calendar

(

year int,

quarter int,

month int,

weekday int,

date datetime primary key

)

Go

create proc fill_calendar(@.start_date datetime,@.end_date datetime)

as

begin

set nocount on;

while @.start_date <= @.end_date

begin

insert into calendar

select

datepart(year,@.start_date),

datepart(quarter,@.start_date),

datepart(month,@.start_date),

datepart(weekday,@.start_date),

@.start_date

where

not exists(select 1 from calendar where date=@.start_date);

set @.start_date = dateadd(day, 1, @.start_date)

end

end

go

--Fill your calendar when required

exec fill_calendar '1/1/2000','12/31/2007'

select * from calendar

go

select

year --other columns

,sum(yourtable.calculation)

from

calendar

inner join yourtable on yourtable.datefield = calendar.date

group by

year --other columns

processing each row in a multirow insert

I'm looking for a way to process each row in a insert ... select sentece. What i need is to update a column which is type int (not identity nor sequence) in a way it always get the max record + 1, in a sentence:

insert into my_table
(a,b,c,d)
select isnull((select max(id) from another_table),0) as a,
'b' as b, 'c' as c, 'd' as d
from table1 join table2 on t1=t2

the problem with such a sentence is the embbebed select is it is executed only once so it provides always the same value, i.e. assuming the subselect returns 7 and the join provides 2 rows, what would be inserted in my_table is:

a b c d
-- -- -- --
7 b c d
7 b c d

and not

7 b c d
8 b c d

what i expected

so what i looking for is a way to resolve such a sentence or at least some kind of fuction which generates a identity value in a similar way as identity() but being useful for no-identity tables. Maybe something similar to newid() but returning int value and it would be great if that value would be max(table_field)+1

any idea?i forgot to say don't want to use a cursor to process and insert each row|||This can be done using a single direct, if complicated, SQL statement. It would be simpler to insert your data into a temporary table with an autoincrement field, and then select from the temporary table, adding max(id) to the values in the autoincrement field.

Otherwise, you will need a select statement with a subquery that fabricates sequential values for your data (what order to you want them in? You have to choose an order with this method) and then add max(id) to these sequential values.

blindman

process is blocking itself

I have a stored proc created for report and now suddenly the report is takin
g
lot of time to run. The store proc is having simple select statement with
multiple case statements.
I checked the sysprocesses table and noticed that the process is blocking
itself.
What could be reason and how to solve this issue'
ThanksSo you are saying that when you run sp_who the spid that appears in
the "blk" column is the SAME as the value in the "spid" column of the
same line?
Roy Harvey
Beacon Falls, CT
On Thu, 8 Jun 2006 12:54:02 -0700, TSQL
<TSQL@.discussions.microsoft.com> wrote:

>I have a stored proc created for report and now suddenly the report is taki
ng
>lot of time to run. The store proc is having simple select statement with
>multiple case statements.
>I checked the sysprocesses table and noticed that the process is blocking
>itself.
>What could be reason and how to solve this issue'
>Thanks|||You're running SQL 2000 with SP4 installed:
http://support.microsoft.com/defaul...KB;EN-US;906344
TSQL wrote:
> I have a stored proc created for report and now suddenly the report is tak
ing
> lot of time to run. The store proc is having simple select statement with
> multiple case statements.
> I checked the sysprocesses table and noticed that the process is blocking
> itself.
> What could be reason and how to solve this issue'
> Thanks|||Yes Exactly.
I ran -
select * from sysprocesses
where physical_io>25 or cpu>15 or memusage>15
order by blocked desc
"Roy Harvey" wrote:

> So you are saying that when you run sp_who the spid that appears in
> the "blk" column is the SAME as the value in the "spid" column of the
> same line?
> Roy Harvey
> Beacon Falls, CT
>
> On Thu, 8 Jun 2006 12:54:02 -0700, TSQL
> <TSQL@.discussions.microsoft.com> wrote:
>
>|||seems wierd. Try it with
OPYION (MAXDOP 1) and
and recompile the stored procedure.
Can you post the script?
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||SQL 2000 latches quite often block other latch request from the same
SPID by design (which Tracy also acknowledged; see
http://support.microsoft.com/default.aspx/kb/906344). With SP4
Microsoft have started displaying those latch blocks (in addition to the
lock blocks) in the blocked column of sysprocesses, so now it looks like
a SPID is blocking itself (which, I guess, technically, it is) but in
fact it's usually just waiting on a page to be read into memory due to
slow I/O.
*mike hodgson*
http://sqlnerd.blogspot.com
Omnibuzz wrote:

>seems wierd. Try it with
>OPYION (MAXDOP 1) and
>and recompile the stored procedure.
>Can you post the script?
>|||Thanks Mike, Tracy. Went through the article. Makes sense. Makes a lot of
sense.
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/

procedures inside a package

I have 48 procedures sitting in a PL/SQL package. when I execute the package I have to select one procedure at a time to get it executed. What could I do to execute the package once and get all the procedures executed at the same time sequencially.A package is just a bag of routines and stuff, somewhat like a library. You cannot execute a package, only the procedures within it.

You need to write another procedure, or anonymous PL/SQL block, like this:

BEGIN
pkg.proc1;
pkg.proc2;
...
pkg.proc48;
END;
/|||Thank you very much

procedure runs with an error??

This is my procedure and the error is incorrect syntax near '01'

DECLARE @.returnDay int

--Looking at current date,
SELECT @.returnDay = DatePart(day,GetDate())
--If is the 7th of the current moth then
If @.returnDay = 24

EXEC master.dbo.xp_sendmail
@.query = 'SELECT a.HospitalName,a.HospitalCode, c.ProductName,b.UnitsDiscarded,d.FateOfProducts,b. DateEntered,
b.DateCompleted,b.CompiledBy FROM test.dbo.Units b inner join Hospitals a ON (a.HospitalID = b.HospitalID)
inner join Products c ON (b.ProductID = c.ProductID)
inner join FateOfProducts d ON (d.FateID = b.FateID)
where b. DateEntered = DateAdd(month, -1, Convert(CHAR(8), GetDate(), 121) + '01') order by a.HospitalID',
@.recipients=test@.hotmail.com', @.message='Submitting Results for the previous month',
@.subject=' results for previous month', @.attach_results = 'true', @.separator = '/s'

SELECT @.@.ERROR As ErrorNumber

What am I missing here now, I am quite new to stored procedures

REgardsEXEC master.dbo.xp_sendmail
@.query = "SELECT a.HospitalName,a.HospitalCode, c.ProductName,b.UnitsDiscarded,d.FateOfProducts,b. DateEntered,
b.DateCompleted,b.CompiledBy FROM test.dbo.Units b inner join Hospitals a ON (a.HospitalID = b.HospitalID)
inner join Products c ON (b.ProductID = c.ProductID)
inner join FateOfProducts d ON (d.FateID = b.FateID)
where b. DateEntered = DateAdd(month, -1, Convert(CHAR(8), GetDate(), 121) + '01') order by a.HospitalID",
@.recipients=test@.hotmail.com', @.message='Submitting Results for the previous month',
@.subject=' results for previous month', @.attach_results = 'true', @.separator = '/s'|||Thank you for your help.
Now I have this error message

The identifier that starts with 'SELECT a.HospitalName,c.ProductName,b.UnitsDiscarded,d.Fa teOfProducts,b.DateEntered,
b.DateCompleted,b.CompiledBy
FROM Test.database' is too long. Maximum length is 128.

I have seen this error but not a solution for it...
Could you help me with that?|||it seems that the allowed lenght of the @.query is 128 only so it is giving an error.|||Yeah I got that too,but I need all the data from the query so how I am suppose to go around it, any suggestions...|||Yeah I got that too,but I need all the data from the query so how I am suppose to go around it, any suggestions...

how about using a staging table (some temp table) to get the results and while sending the mail just select the records from this staging table?|||I hope I am not annoying everybody,but how do I use the temp table,do I create a replica of a table that has all the data or...I see what you mean expect I don't quite know what is my next step...

Any help appreciated|||set quoted_identifier off
EXEC master.dbo.xp_sendmail
@.query = "SELECT a.HospitalName,a.HospitalCode, c.ProductName,b.UnitsDiscarded,d.FateOfProducts,b. DateEntered,
b.DateCompleted,b.CompiledBy FROM test.dbo.Units b inner join Hospitals a ON (a.HospitalID = b.HospitalID)
inner join Products c ON (b.ProductID = c.ProductID)
inner join FateOfProducts d ON (d.FateID = b.FateID)
where b. DateEntered = DateAdd(month, -1, Convert(CHAR(8), GetDate(), 121) + '01') order by a.HospitalID",
@.recipients='test@.hotmail.com', @.message='Submitting Results for the previous month',
@.subject=' results for previous month', @.attach_results = 'true', @.separator = '/s'|||I hope I am not annoying everybody,but how do I use the temp table,do I create a replica of a table that has all the data or...I see what you mean expect I don't quite know what is my next step...

Any help appreciated
SELECT a.HospitalName,a.HospitalCode, c.ProductName,b.UnitsDiscarded,d.FateOfProducts,b. DateEntered,
b.DateCompleted,b.CompiledBy into #temp FROM master.dbo.units b inner join Hospitals a ON (a.HospitalID = b.HospitalID)
inner join Products c ON (b.ProductID = c.ProductID)
inner join FateOfProducts d ON (d.FateID = b.FateID)
where b. DateEntered = DateAdd(month, -1, Convert(CHAR(8), GetDate(), 121) + '01') order by a.HospitalID

EXEC master.dbo.xp_sendmail
@.query = "SELECT * from #temp",
@.recipients=test@.hotmail.com', @.message='Submitting Results for the previous month',
@.subject=' results for previous month', @.attach_results = 'true', @.separator = '/s'|||set quoted_identifier off
EXEC master.dbo.xp_sendmail
@.query = "SELECT a.HospitalName,a.HospitalCode, c.ProductName,b.UnitsDiscarded,d.FateOfProducts,b. DateEntered,
b.DateCompleted,b.CompiledBy FROM test.dbo.Units b inner join Hospitals a ON (a.HospitalID = b.HospitalID)
inner join Products c ON (b.ProductID = c.ProductID)
inner join FateOfProducts d ON (d.FateID = b.FateID)
where b. DateEntered = DateAdd(month, -1, Convert(CHAR(8), GetDate(), 121) + '01') order by a.HospitalID",
@.recipients='test@.hotmail.com', @.message='Submitting Results for the previous month',
@.subject=' results for previous month', @.attach_results = 'true', @.separator = '/s'

:eek:
smart|||Thank you guys heaps...|||By default in SQL Server 2000 ... the entries inside "" (double quotes) are taken as identifiers ... you need to turn them off while using them as a way to avoid this happening.

alternatively you can use the single quote two times to use them within a string

eg .


set quoted_identifier off
EXEC master.dbo.xp_sendmail
@.query = 'SELECT a.HospitalName,a.HospitalCode, c.ProductName,b.UnitsDiscarded,d.FateOfProducts,b. DateEntered,
b.DateCompleted,b.CompiledBy FROM test.dbo.Units b inner join Hospitals a ON (a.HospitalID = b.HospitalID)
inner join Products c ON (b.ProductID = c.ProductID)
inner join FateOfProducts d ON (d.FateID = b.FateID)
where b. DateEntered = DateAdd(month, -1, Convert(CHAR(8), GetDate(), 121) + ''01'') order by a.HospitalID',
@.recipients='test@.hotmail.com', @.message='Submitting Results for the previous month',
@.subject=' results for previous month', @.attach_results = 'true', @.separator = '/s'


I hope I have been able to explain it clearly ...|||Excellent, many thanks