Odd FMTONLY Statements

Jul 21, 2006

Hi,
I recently started using ADO
for connecting to a remote SQL Server from Delphi.
Prior to this I used BDE (Borland Database Engine).



I noticed that all SQL communication seemed slow €“ only
half the speed of using BDE!

I ran a trace using the Profiler (from MS SQL Server)
and noticed that ADO
generated roughly twice the amount of traffic compared to BDE. Every SQL statement (SELECT,
INSERT and UPDATE) send from my application was preceded by the exact same
statement encapsulated in €˜SET FMTONLY ON/OFF€™.



-Example Start-

SET FMTONLY ON SELECT * FROM TabelVersion SET FMTONLY
OFF

SELECT * FROM TabelVersion

-Example End-



Why are these odd statements being passed to the
server?

Shouldn€™t the provider be able to handle the returned
dataset without sending the FMTONLY statement in advance? And if not, why doesn€™t
it cache this information?



Is it possible to minimize this traffic?



I also spotted quite a lot €˜SET NO_BROWSETABLE ON/OFF€™
statements. What is the use for these?



I found this article (http://support.microsoft.com/kb/836830/en-us)
on MSKB, but I should already have the hotfix for this installed by MDAC 2.8 SP1!


This slow pace is killing me €“ please help - egeskov



My configuration:

Workstation:

WinXP SP2

MDAC 2.8 SP1
(2.81.1117.0)

ConnectionString=€™Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
Security Info=False;Application Name=PD:KE;Data Source=SMIL;Initial
Catalog=PDDebug€™

Server:

Windows
Server 2000
SP4

MS SQL
Server 8

View 12 Replies


ADVERTISEMENT

Fmtonly Problem (bug ??)

Apr 13, 2006

I am having a problem with "SET fmtonly ON" and a function Iimplemented in my database. (The function is actually Erland'sdelimited string to tmp table function for purposes of passing inmultiple values to a stored procedure.)The stored procedure which uses the function is used to return aresultset. But sometimes the stored procedure is called with bogusvalues (preceded by "SET fmtonly ON") to get the column names. And thisis where the problem happens.If fmtonly is ON and the function is called with the list containing asingle value (with no delimiters) (as in 'item1' versus 'item1,item2')then SQL throws a "Invalid length parameter passed to the substringfunction" error.After much testing it seems that certain parts of code in the functiongets IGNORED. (In this case it is the WHILE loop condition check.)(Happens with IF statements also.)I created some sample code to prove this.--SET fmtonly ONDECLARE @flag INTSET @flag = 0IF 0 > 0 -- always falseSELECT @flag = 1 -- should never executeSET fmtonly OFFPRINT 'DEBUG | ' + LTRIM(STR(@flag))If executed as is the code executes correctly and the value printed is"0". But if fmtonly is ON then for whatever reason 0 > 0 evaluates totrue and the @flag variable gets set to "1".While this seems to be by design (I tested it on SQL 2000 sp3, sp4, andSQL 2005 with the same results) this just seems silly to me.

View 6 Replies View Related

SET FMTONLY Being Added To In-line SQL

Jul 23, 2005

Here's the deal.I've been working on an Intranet application for my clients, and todayI went and installed the first prototype. It's a fairly standard thing- VS2003 ASP.NET/VB.NET on a SQL Server 2000 database.I restored the database onto their db server, and installed theapplication on their intranet server, and made the necessary changes tothe web.config and other configuration files.I logged on with no problem. The default form is a Search/Finder formwith no default recordset. I fired up the filter to return all therecords and the thing crashed. The filter form wouldn't get any data(there *was* data in the database - I checked that!). So I set a traceon the database and this is the SQL that was being sent to the server(note that this SQL is dynamically built and is being sent in-line):SET FMTONLY OFF; SET FMTONLY ON;SELECT fldID, fldReferenceNumber,fldRevision, fldPartNumber, fldIndentNumber, fldDescription,fldBatchNumber, fldCreatedDate FROM tblMyTable SET FMTONLY OFF;Note those SET FMTONLY OFF; SET FMTONLY ON;...SET FMTONLY OFF;directives. These direct SQL Server to return only metadata, and notdata rows. The SQL which SHOULD have been sent, and which I have justcaptured in the SQL Profiler on MY system here is:SELECT fldID, fldReferenceNumber, fldRevision, fldPartNumber,fldIndentNumber, fldDescription, fldBatchNumber, fldCreatedDate FROMtblMyTableWHERE (((tblMyTable.fldReferenceNumber LIKE N'%')))Again, note that the WHERE clause is missing from the first lot of SQL.Does anyone have any ideas about why this might be happening? I thinkit might have something to do with ownership/permissions, but Icouldn't find anything different about this database than the system onwhich this new application was modelled, which has no such problems.Edward

View 3 Replies View Related

SQL Statements

Aug 17, 2006

Hi guys,
Need some help in some query processing...
Here goes,this is the results i have obtained so far
|id |cat | name |
1 .....a... apple
1 .....b .. banana
2 .....a ...austria
2 .....b ...brazil
2 .....c ...china
3 .....a ...abraham
3 .....c ...clinton

Column cat can have at most 3 different types of values..{a,b,c}

I wanna write a query such that the result comes out as such
<(....id ......a .......b......... c..) corresponding to these fields.>

|Col 1| Col 2 | Col 3 | Col 4|
....1 ...apple ...banana
....2 ...austria .brazil... china
....3 ...abraham ..........clinton

Anyone can help...thanks guys

View 1 Replies View Related

If Statements In SP

Sep 21, 2006

I use SQL Server 2005.I want to create a SP "Search". With this function a user can serach the member database on several criteria: age minimal, age maximal, name, city..BUT these criteria do not necessarily have to be defined by the user, so it might be that a user searches for all members whose age is between ageMin and ageMax leaving the name and city criteria empty.So in my SP I have to check whether these values are empty or not. If a parameter is not empty I have to add it to my selection query.. BUT, I know that SQL Server makes an execution plan, and understood that when you use if-statements the procedure needs to be recompiled every time?Is this true? If so:how does this work then?Is there another way for me to still be able to create this SP?

View 2 Replies View Related

Help With If-Else Statements In SQL...please...

Dec 11, 2006

Hello guys. How is this declared in SQL?
Select * FROM my_table
     if my_column = "1" UPDATE my_table SET  my_other_column= "a"       ////my_column and my_other_column belong to my_table
     else if my_column="2" UPDATE my_table SET  my_other_column= "b" ////my_column and my_other_column belong to my_table
   

View 4 Replies View Related

Sql Statements Per Second

Jan 27, 2008

Hi, I would like to know the number of SQL statements per second being generated by my web site. How can I know this?  Best Regards,MeeNge  

View 6 Replies View Related

If Statements

Apr 8, 2008

 Hello I have an SQL Data Source  i want to compare 2 dates if one is equal to or less than the other i want to return either a string or a true i have been trying combinations for about an hour and it's getting a little frustrating i;ve tried searching for an example but cant find one so somesthing like Select ValuesWhere Date1 <= Date2 Return "String"(orTrue?) also IF date1 <= Today() Return "String OR True" thanks Chris 

View 3 Replies View Related

Sql If Statements

Feb 22, 2006

I have some trade data. One colum is tran_status_mtf. within that column is "settled/traded", "cancelled", and "revised". I want to write and SQL statement that says if the trade is "settled" or "traded" display "A" in my output. How do I do this? I am new to SQL. Thanks in advance guys!!!

View 14 Replies View Related

IF Then Statements

Jun 3, 2008

I need to create a brief statement in Design View for SQL 2000.

I successfully wrote "isnull(dbo.Data.Parcel,'')in the Column in Design View, which would ensure that a null value would be listed as a blank. Now I need to have dbo.data.status show as 'C&G' in any instance of data, otherwise it will be listed as blank. I tried writing "isnotnull(db0.data.status,'Homestead', '')", but it does not work.

Is there a simple statement I can write in "column" of design view that can get the results that I need?

-Steve

View 4 Replies View Related

Using Sum In Sql Statements

May 21, 2007

I have written an sql statement thats using sum. The problem that I have is that when the value is zero it returns Null. Is there a way that I could return zero instead of null.

View 5 Replies View Related

SQL If Statements

Oct 19, 2007



I am totally new to creating IF statements inside stored procedures. I am passing some parameters from a ColdFusion form to a SP and if the form is empty for the small date I get an error. I would like to be able to check in the procedure to see if the parameter is empty and if it is set a default value for it. Here is my code.
@JOB1,
@COLLEGEDATE smalldatetime,
If(@COLLEGEDATE <> ''")
@COLLEGEDATE smalldatetime,
ELSE
@COLLEGEDATE = getDate(),
RETURN
@JOB2

View 3 Replies View Related

Different T-SQL Statements?

Sep 16, 2006

Hi, i just want to know if the T-SQL statements in SQL server 2005 are different from SQL Server 2000???

If they are different, where can i find the Server 2005 T-SQL Statements?

View 6 Replies View Related

SUM And Iif Statements

Apr 16, 2007

Hello,



I have a report with 1 field that I need to sum into 2 different textboxes based on another field's value.



Specifically, Is it simply doing something like this below?



=Iif(Fields!iBB.Value=1, Sum(Fields!Billed.Value)

=Iif(Fields!iBB.Value=0, Sum(Fields!UnBilled.Value)



Which will not get past intellisense checking, -or- do I have to do something different here?



Thanks in advance.

View 10 Replies View Related

Help With Insert Sql Statements...

Dec 15, 2006

Hi guys! I have these commands that insert into two tables, if condition 1 is met, it will insert into the first table, if the second condition is met, it will insert into the second table.
Is there a way for the insert statements to be merged so that I won't be executing two statements? 
Dim update_phase_before As New SqlCommand("INSERT INTO TE_shounin_todokesho_jizen (syain_No,date_kyou,time_kyou) SELECT syain_No,date_kyou,time_kyou FROM TE_todokesho WHERE TE_todokesho.b_a='before'", cnn)
Dim update_phase_after As New SqlCommand("INSERT INTO TE_shounin_todokesho_jigo   (syain_No,date_kyou,time_kyou) SELECT syain_No,date_kyou,time_kyou FROM TE_todokesho WHERE TE_todokesho.b_a='after'", cnn)     
 
Thanks.

View 1 Replies View Related

Several Select Statements?

Jan 16, 2007

Hello, how can i merge together several select statements?
I have something like this:
CREATE PROCEDURE Forum_GetThreads @ID int,@AscDesc bitASBEGINSET NOCOUNT ON;SELECT * FROM forum_ansageSELECT * FROM forum_topics WHERE (status = 0) ORDER BY (created) DESCIF (@AscDesc = 0)BEGIN      SELECT * FROM forum_topics WHERE (status > 0) ORDER BY (created) DESCENDELSEBEGIN      SELECT * FROM forum_topics WHERE (status > 0) ORDER BY (created) ASCENDHere i want to merge them all together and return only one SELECT statement with all the data

View 5 Replies View Related

Max SQL Statements In One Page

Feb 21, 2007

I'm running a custom built report on .net page  Basically there are a lot categories.  I need to run SQL statements for and for each category, the catch is these statements are all in loops for i = 1 to 12.  Therefore 108 categories X 12 iterations of SQL calls =  a little more that 1200 sql calls from one .net page. 
 There is obviously a major problem with load time and running these reports are painful to the user and the system.
 
Is there any easier way to possibly pre-compile all the data into a temp database so I wouldn't need to make as many sql calls???????  If so, would a stored procedure handle something like this.
 Sorry for being somewhat vague.

View 7 Replies View Related

Apostrophes, Etc. In WHERE Statements?

Apr 11, 2007

How do I handle and apostrophes and other punctuation in stored procedure Sql statements:
SELECT        L_ID, L_NameFROM            tblHVACContractorsWHERE        (L_Name = 'Mare's Heating & Cooling Services')

View 5 Replies View Related

Use Two SQL Statements In One SqlDataSource ?

May 4, 2007

Hello,
I have SqlDataSource to retrieve a data into DetailsView, or GridView, whatever.
I want to use two Select command like this:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=AHMED-4714D04B3;Initial Catalog=mp;Integrated Security=True"
<%
if (( parID == 3) || (ParID ==4)
{
SelectCommand="SELECT [PID], [PageID], [PageContent], [ParID], [ChiID] FROM [mp_page] WHERE ([ParID] = @ParID)">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="ParID" PropertyName="SelectedValue" />
</SelectParameters>
}
else
{
 
SelectCommand="SELECT [PID], [PageID], [PageContent], [ParID], [ChiID] FROM [mp_page] WHERE ([ChiID] = @ChiID)">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="ChiID" PropertyName="SelectedValue" />
</SelectParameters>
}
</asp:SqlDataSource>
Is it possible to do something like that ?

View 3 Replies View Related

Help Writing SQL Statements

Jun 5, 2008

Hey everyone,
  I'm making a site and I need to write some code that will do the following things: 


Write a statement for the Page_Load event to pull the information from the db and load it into a textbox and a textbox with multiline attribute.

View 13 Replies View Related

Help With Combining Sql Statements

Mar 7, 2006

I'm trying to combine the following two strings to create a single Insert statement (and thus only generate one record instead of two).
insertString = "Insert comments (uID) Select uID FROM users WHERE uName = @uName"
insertString2 = "INSERT comments (eventID, text) VALUES ( @eventID, @comment)"
I have tried:
Insert comments (uID, eventID, text) SELECT uID FROM users WHERE uName  = @uName VALUES (uID, @eventID, @comment)
Individually they work fine, but I can't get the syntax correct to allow them to work together. As you can tell, I'm not very good with SQL, so any help would be greatly appreciated!
Thanks in advance.

View 2 Replies View Related

Concatenating Sql Statements

Jan 30, 2002

I have a web application that has SQL7 as the back end. SQLServer and ORACLE have a feature that allows sql commands to be combined in one statement. Another words I am able to to

SELECT * FROM table_name WHERE id = 2 DROP TABLE other_table

If I type this in a query analyzer It will perform both commands. Is there a way to turn this 'feature' off.

The main reason I want to turn it off is so if a numerical value is sent as a url variable someone can't add the drop table statement or any other sql command to the value of the url variable and have it executed. We have added ample code to trap for this problem but I would like to also handle it at the database level.

Thanks,
Jeff

View 2 Replies View Related

HELP....&#34;OR&#34; Conditions In SQL Statements

May 31, 2000

Folks,

I'm having some real problems using the OR condition in a very simple SQL statement and could use your help or insight on where the problem lies, or perhaps a workaround.

I have a large flat table in a SQL 7 database with 10 million + records called "HISTORY". I have not installed either service pack 1 or 2. I'm attempting to run a query that references the following four fields which are all non-clustered keys:

EQUIPMENT_NO TEXT 12
CHASSIS_IN TEXT 12
CHASSIS TEXT 12
SVC_DATE_TIME SMALLDATETIME

Here's the SQL statement:

SELECT * FROM HISTORY WHERE (HISTORY.EQUIPMENT_NO = 'XYZ123' OR HISTORY.CHASSIS = 'XYZ123' OR HISTORY.CHASSIS_IN = 'XYZ123') AND SVC_DATE_TIME >= '01/15/00 00:00:00 AM' AND SVC_DATE_TIME <= '02/28/00 23:59:59 PM'
ORDER BY EQUIPMENT_NO

This query takes 11 min. 5 sec. inder the Query Analyzer and ultimately returns the 31 desired records.

If you remove the SVC_DATE_TIME criteria, about 350 records are returned in a matter of seconds. I've also tried variations on the date syntax such as '01/15/00', etc. with no change in the amount of time to execute.

Other queries such as a simple AND condition combining EQUIPMENT_NO and SVC_DATE_TIME are snappy.

Are there known problems/bugs with "OR" conditions in queries that anyone is aware of, particularly with parentheses; am I composing this query incorrectly? Is there some alternate syntax that would work as expected? I can't see where the query shouldn't execute quickly as expected, particularly with all indexed fields involved. I'm stumped! Lend me your expertise. Thanks much.

Clark R. Farabaugh, Jr.
Financial Systems Analyst
VIT
Norfolk, VA

View 8 Replies View Related

Building Sql Statements

Jan 6, 2003

Hi,

I am having some problems trying to build an sql statement from more than one statement.

Here is the statement

select 'Insert App_Column (Table_ID, Column_Type_Transformation, Column_Name, )
Values (@table_ID,' ,'NULL,', name from payatwork..syscolumns where id in (
select id from payatwork..sysobjects where name like 'Employee_Profile')
order by colorder, ')'

What I am finding is that the bracket at the end of the statement is not appearing - how do I append statements to the end of this sql statement (i've tried various combinations of the + sign and the comma without success.

thanks,

Jim

View 4 Replies View Related

Update Statements

Apr 26, 2004

How to update a column in the table using the data from another column in the same table? Thanks.

View 1 Replies View Related

Concatenating Sql Statements

Jan 30, 2002

I have a web application that has SQL7 as the back end. SQLServer and ORACLE have a feature that allows sql commands to be combined in one statement. Another words I am able to to

SELECT * FROM table_name WHERE id = 2 DROP TABLE other_table

If I type this in a query analyzer It will perform both commands. Is there a way to turn this 'feature' off.

The main reason I want to turn it off is so if a numerical value is sent as a url variable someone can't add the drop table statement or any other sql command to the value of the url variable and have it executed. We have added ample code to trap for this problem but I would like to also handle it at the database level.

Thanks,
Jeff

View 2 Replies View Related

Case Statements

Oct 19, 2005

Hey folks, having a problem here...

I have 2 piece of code designed to do the same thing. My problem is, i'm not getting the same results.

Code 1 where the results are correct


Code:


select Count(*) as TotalCount, Sum(DistAmt) as TotalSum
from table1
inner join table2
on table2.id = table1.id
where MailTypeID = 3
AND MailEventID = 2
and table1.IsActive = 1




code 2 - Y is correct, but Z is not, and not only is it not correct, but it is returning a number which equals more then the total rows from the table.


Code:


select
Y = sum(case when mailtypeid = 3 and maileventid = 2 and IsActive = 1 then distamt else 0 end),
Z = count(case when mailtypeid = 3 and maileventid = 2 and IsActive = 1 then 1 else 0 end)
from table1
inner join table2
on table2.id = table1.id



I have no idea what is going on.

Thanks!
Caden

View 2 Replies View Related

Help With CASE And IF Statements

Dec 9, 2006

Hi,

Is there a way to use more criteria in a CASE statement other than CASE WHEN expression THEN value ELSE value END


I need to test if the count is greater than 0. If so, then perform the case statement, else return zeros. Currently there are entries where the values are blank. These blank values are causing errors in the application and unfortunately, I am not able to update these values.

So far I have the following, but I am getting an error stating "An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or select list, and the column being aggregated is an outer reference."

Thanks in advance!


Code:


IF @Qid = 4
SELECT @Exp as Status, COUNT(*) AS Total, @CourseID as CourseID,

(SELECT
question
FROM
tableQuestions
WHERE
qid = @Qid) AS Question,

IF COUNT(*)>0 THEN

1.0 * SUM(CASE WHEN A.Q1 > 1 THEN 1 ELSE 0 END) / COUNT(*) AS Positive,
1.0 * SUM(CASE WHEN A.Q1 = 0 THEN 1 ELSE 0 END) / COUNT(*) AS Neutral,
1.0 * SUM(CASE WHEN A.Q1 < 0 THEN 1 ELSE 0 END) / COUNT(*) AS Negative,
1.0 * SUM(CASE WHEN A.Q1 = 0 THEN 1 ELSE 0 END) / COUNT(*) AS NA
ELSE
0 AS Positive,
0 AS Neutral,
0 AS Negative,
0 AS NA

END IF

FROM
table1 A INNER JOIN table2 B ON A.SessionID = B.SessionID

WHERE
(B.CourseID = @CourseID) AND (A.SubmitDate >= @BeginDate) AND (A.SubmitDate <=@EndDate)

View 2 Replies View Related

Question About IF Statements

Mar 28, 2007

Im trying to get some of my queries to run a little faster and i think i have an idea on how but i cant seem to get my and IF statements to work

basically what i want is for the different parts of my WHERE clause to run only if certain variables have a value. so something like this


Code:

SELECT * FROM tablename WHERE

IF LEN(@color) > 0
BEGIN
color = '@color'
END

IF LEN(@size) > 0
BEGIN
AND
size = '@size'
END



so i only want it to limit it by color if @color has a length of more than 0. if there is a better way to do this (other than IF statments) then please let me know.

Im just getting an error saying incorrect syntax near IF

View 1 Replies View Related

Solving The ' In ASP With SQL Statements

Feb 8, 2004

Hi All,

It's probaly still Sunday in most areas, but hope I get a quick response on this 1.....


I'm doing ASP with SQL 2000. And for the moment, I write inline SQL Scripts into my ASP pages. Store procs can only come later ( don't ask why...).

So as most of u will know, if any characther from the textboxes comes with a ' , everything will go haywire!

Thus, I'm using this existing code to solve the prob:


FAddress = Replace(FAddress, "'", "%%")

Call OpenDB()
SQL_query =" SET NOCOUNT ON" & _
" set DateFormat dmy" & _
" declare @FAddress varchar(100)" & _
" set @FAddress='"& FAddress &"'" & _
" set @FAddress=(replace(@FAddress,'%%',''''))" & _........and so on


Is there a better way? Some problems from this is if I am inserting large info in a text datatype, I can't do it.....

Some code or function that I can call and apply to my entire system without recoding alot....:)

SQL and ASP jedi masters...please enlighten me.

View 3 Replies View Related

Combine These Two Sql Statements

Jun 2, 2004

SELECT bms_id,email_address,COUNT(*)
INTO #temp
FROM emp_db
WHERE email_address IS NOT NULL
GROUP BY bms_id,email_address
ORDER BY bms_id DESC,COUNT(*) DESC

SELECT bms_id COUNT(*)
FROM #TEMP
GROUP BY bms_id
ORDER BY COUNT(*) DESC

How can i put these two statements into a single sql statement.

Thanks.

View 3 Replies View Related

How To Do Multiple If Statements

Oct 11, 2015

I want the below sql code to force a error if it falls on a saturday or sunday, and there are no rows in the OrderTrans table for the previous day (TransDate) but not sure how to do multiple if statements.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[OrderTrans](
[OrderId] [int] NOT NULL,

[Code] ....

View 0 Replies View Related

How To Use Two Select Statements

May 12, 2008

Both of these work fine separately; How do I join these two Select Statements?

SELECT MPI.CREATE_DT,MPI.MPI_NBR, MPI.LAST_NM,MPI.FIRST_NM,
MPI_CURRENT_ADDR.ADDR_NBR, MPI_CURRENT_ADDR.ADDRESS_1, MPI_CURRENT_ADDR.ADDRESS_2,
MPI_CURRENT_ADDR.CITY,MPI_CURRENT_ADDR.STATE_CD,MPI_CURRENT_ADDR.ZIP_CD,MPI_CURRENT_ADDR.PHN_NBR,
MPI_PERSON.BIRTHDAY,MPI_PERSON.SEX

FROM MPI,MPI_CURRENT_ADDR,MPI_PERSON

WHERE (MPI.MPI_NBR=MPI_PERSON.MPI_NBR) AND (MPI.ADDR_NBR=MPI_CURRENT_ADDR.ADDR_NBR)
AND
MPI.CREATE_DT>=20070101
ORDER BY MPI.CREATE_DT

SELECT PATIENT.PAT_NBR,PATIENT.PHYS_NBR, PHYSICIAN.FIRST_NM,PHYSICIAN.LAST_NM,PHYSICIAN.DE_NBR, PHYSICIAN.SALUTATION_CD
FROM PATIENT, PHYSICIAN
WHERE PATIENT.PHYS_NBR=PHYSICIAN.PHYS_NBR


Thanks!
Lisa

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved