Database

Mycroft Holmes wrote: I wonder when that was written Oracle is still from a world far behind. Even the latest version cannot be installed into C:\Program Files\. So I guess it was written back in the Windows 3.1 era.

Thanks

My assumption is that without and order by clause it should not have to scan the table, just get the first 10 records. It was during development so the performance was not really relevant as I had to tune the indexes after stabilising the query. I was just astonished it was slower than the full select. Transforming this data in is going to take about 6 hours by the look of it, thankfully it is monthly not daily! Never underestimate the power of human stupidity RAH

I have never actually needed to use custom types, you would then need to maintain the type in both SQL and your code as SQLDBType would not include your IDENTIFIER type. Not a big deal until someone forgets to sync a new type! Never underestimate the power of human stupidity RAH

I don't really understand your question. Are you asking: when should I use nvarchar instead of varchar? when should I use nvarchar(max) instead of a specified size such as nvarchar(100)? when should I use nvarchar(max) instead of ntext? when should I use nvarchar(max) instead another datatype? something else all of the above These are all different questions (some of which have been answered already).

Just look at: suburb LIKE '%' + @suburb1 + '%' AND suburb LIKE '%' + @suburb2 + '%' If @suburb2 is different from @suburb1, that will select exactly 0 rows because suburb is either like @suburb1 or like @suburb2 but surely not like both at the same time. Use OR instead of AND here. To differentiate between @suburb2 being set or not, use a CASE statement.

There are three records already in the database you said. What are *those* values? (List them all three please) And what is the value of the PK-field of the record that you're inserting? I are Troll :suss:

I hate it when someone univotes a post with no explanation. You error is that this is a database forum and you are asking a question that requires VBA, I know you think you are using a database - Access but that is because Access should no longer be called a database, abomination maybe but not a database. You will get a much better response in the VB forum. You should also mention that you are using spit MS Access and the version as there are differences that may be critical. A possible answer - define some global variables representing the data, load them from the dataset/recordset or make the dataset/recordset a global. Sorry have not used Access for more than a decade. Never underestimate the power of human stupidity RAH

developerit wrote: i want to execute stored procedure in a function This is not possible by design, but you can convert the stored procedure in a function and then call it from another function or stored procedure. developerit wrote: and the same function i want to call in view. Not at all possible, View is a mare logical representation of a select statement over tables/ other views. An in line table-valued function can perform as a parametrized view and you can call that in other functions/ stored procedures. WJFK (Write Just for Kicks)

Thanks for the reply, in the end the index itself was marked as being clustered to include fields that were text ones.

You are in big problem, just drop refference and recreate them. Thanks

When you encounter such a performance difference, does it then matter, which one was executed first? Or in other words: is the stored procedure faster after you run the normal query? SQL Server can cache execution plans and results, maybe that's the issue here.

True, but we don't know how it'll be (mis-)used.

How's about this? SELECT ID=MAX(Agent.ID) ,FirstName=MAX(Agent.FirstName) ,LastName=MAX(Agent.SecondName) ,Sold=SUM(CASE Seller WHEN Agent.ID THEN 1 ELSE 0 END) ,Bought=SUM(CASE Buyer WHEN Agent.ID THEN 1 ELSE 0 END) FROM Agent LEFT OUTER JOIN [Order] ON Agent.ID=[Order].Seller OR Agent.ID=[Order].Buyer GROUP BY Agent.ID :-D (And, yeah, be sure to index Buyer and Seller)

My previous post for this thread was only example how to do the insert from select statement. For more specified case then it must have other conditions on select statement and so. :) I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.aktualiteti.com

Hi! Thanks, that was helpful, but only partly, since passing SQL_NULL_DATA (-1) instead of 0x00, I get 0xFF value in the base field. if (v[i] == 0x00) v[i] = SQL_NULL_DATA; How do I later distingush real FF bytes from wraped null data? :sigh: Still many thanks for help

suppose you will insert some data in a table from two other's table/data base. Begin Tran Use DataBase2 insert into Table1(Name,Id,Roll,Address) select A.Name,A.Id,B.Roll,B.Address from DataBase1..Table2 A inner join DataBase2..Table3 B on A.Id = B.Id Commit tran If You use this query then you will easily transaction (Insert) data from two different data base & tables. Mrityunjoy Bhattacharjee modified on Sunday, August 8, 2010 12:06 PM

Did you try SUM function to get sum of working hours. example: select sum(workinghours) from mytable Or post your query and we will see how concretely to add too sum of working hours. I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.aktualiteti.com

thanks so much Guys...^_^