|
-access 2003.
-Vb6 classic
I cannot have permission Sql Server, but have only a redaing on the sql table.
If for you is a god way to use DTS in vb code this solution is welcome!
Yes if is possible make all wiyh vb6 classic.
Tks.
|
|
|
|
|
hi,
how can we write CURSOR in Pl/SQL.Plz send me the syntax of it.
Thanks
Richa
|
|
|
|
|
Go to Google, Bing or the search engine of your choice.
Type "pl/sql cursor".
Press Enter.
|
|
|
|
|
richa021 wrote: Plz send me the syntax of it.
You're looking for the Manual[^]? 
I are Troll 
|
|
|
|
|
Oh My God.
Please please please get a dba to do this for you.
Dave: But I don't wanna be a barbarian!
Fang: You did when you were ten!
Dave: Yes, but I thought it meant a librarian that also cuts hair.
|
|
|
|
|
I am using MS Sql 2005. In my database I have a table "Products" which has following structure and content
ProductName CategoryId AddedDate
Product1 1 04/12/2010
Product2 1 04/12/2010
Product3 2 05/12/2010
Product4 1 06/12/2010
I want to fetch top 2 records order by AddedDate. To do that I can use sql
Select top 2 * From Products Order By AddedDate
This will populate first two records. Both are in CategoryId "1". I want to Select records from distinct categories.
I requirment is to select records order by AddedDate with distinct CategoryId. I can do it with temporary tables. But is there any simple query to do that?
Thanks and regards,
Poppy
modified on Thursday, May 27, 2010 9:33 AM
|
|
|
|
|
SELECT DISTINCT TOP 2 <br />
<br />
p.CategoryId,<br />
(SELECT TOP 1 p2.AddDate<br />
FROM products p2 WHERE p2.CategoryId = p. CategoryId) AS adddate<br />
<br />
FROM<br />
products p<br />
ORDER BY p.CategoryId<br />
Hope this will help you.
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
modified on Thursday, May 27, 2010 10:45 AM
|
|
|
|
|
Please correct me if I am wrong. This will sort the results order by CategoryId not by AddedDate.
But I could learn something from your SQL. Thanks for your help.
Regards,
Poppy
|
|
|
|
|
Let's start out with a basic testscript that other posters can abuse to test on their machines;
DECLARE @Product TABLE (
[Name] NVARCHAR(250)
,[refCategory] BIGINT
,[Added] DATETIME DEFAULT GETDATE()
PRIMARY KEY ([Name])
)
INSERT INTO @Product
SELECT 'Product1', 1, '20101204' UNION
SELECT 'Product2', 1, '20101206' UNION
SELECT 'Product3', 2, '20101205' UNION
SELECT 'Product4', 1, '20101204' UNION
SELECT 'Product5', 1, '20101209' UNION
SELECT 'Product6', 2, '20101206' UNION
SELECT 'Product7', 2, '20101204' UNION
SELECT 'Service1', 3, '20100101' The testset is somewhat larger to make it somewhat easier to interpret what I'm looking at.
popchecker wrote: I want to Select records from distinct categories. I requirment is to select records order by AddedDate with distinct CategoryId.
If I understand correctly, then you want to group the data by category, sort within that group on the date, and take the top 2 records from each group?
Sql Server 2005 supports "Common Table Expressions", which you could use as an alternative to a temporary table, like this;
SELECT [Name], [refCategory], [Added]
FROM(
SELECT [refCategory],
[Added],
[Name],
row_number() OVER (
PARTITION BY [refCategory]
ORDER BY [Added] ASC
) AS [catGroup]
FROM @Product
GROUP BY [refCategory], [Added], [Name]
) AS ProductCTE
WHERE [catGroup] <= 2
ORDER BY [refCategory] ASC, [Added] ASC
I are Troll
|
|
|
|
|
Thanks for the test script.
SELECT p.refCategory, t.Name, t.Added
FROM
(SELECT DISTINCT refCategory
FROM @Product) p
CROSS APPLY
(SELECT TOP 1 Name, Added
FROM @Product
WHERE refCategory = p.refCategory
ORDER BY Added ASC) t
|
|
|
|
|
Hi russell ,
5 from my side.
This will run perfectly...
Reasons are not Important but Results are Important.
http://www.sql4professional.blogspot.com
Swati Tripathi
modified on Friday, May 28, 2010 1:46 AM
|
|
|
|
|
Learn something every day, I'd have gone with the rownumber/partition solution. I've never used cross apply!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Cool! Thanks for updating it
I are Troll
|
|
|
|
|
Hello,
I have written an application that needs to connect to a database that I have hosted online. I can connect perfectly using the MS SQL Management Studio, but when I try to connect using my own code, the connection fails and gives the following reason:
"The user is not associated with a trusted SQL Server connection"
I am using the same login information that I am using with the management studio. This is how the connection string is coded:
SqlConnection Conn = new SqlConnection("server=" + txtServerName.Text + ";user id=" + txtUID.Text + ";password=" + txtPWD.Text + ";Trusted_Connection=True");
I am able to connect perfectly on Localhost using a clone of this exact database.
Can you explain or correct this problem? Thank you, Pat
|
|
|
|
|
A trusted connection is useful on a local network, where your windows account is "trusted".
For a hosted SQL Server such as the one you described, you would be using a standard connection rather than a trusted one.
|
|
|
|
|
Damian,
Thank you for your answer. Actually, I added that on because it wasn't working without it. Now, since it wasn't working with it, I removed it...and now it is working again. I can't explain it, but since it is now working without it, you must be right. I do not know why it would not connect before. I have marked your answer as correct and I appreciate your assistance. Thank You.
Best Regards, Pat
|
|
|
|
|
Is there any way in sql server 2000, 2005 by which we can export only the definition of tables and stored procedure.
What i am trying to ask:
Suppose there is table creation syntax like:
Create table TableName
(col1 datatype,
col2 datatype,
.
.
.)
Same as for stored procedure.
I want only definition not DATA of whole database.
Can it be possible.
|
|
|
|
|
If you have the management studio available just right click on the object you want to script. In 2000 you select All Tasks -> Generate SQL script. In 2005 it is slightly different but just as easy to accomplish.
Thanx,
>>>-----> MikeO
|
|
|
|
|
You can use SHOW CREATE TABLE which will give you the correct CREATE TABLE statement for each table.
Happy Coding.
|
|
|
|
|
Right click on the database -> All Tasks (Or New i cant remember for 2000 anymore)-> Generate SQL Script then run through the wizard, by defualt 2000 wont script out the data, at least mine wouldnt. I do remember that in 2000 you had to check the "Show All Items" box, then you could use the newly enabled check boxes below it to select all items of a certain type. If yours is giving you all the data there should be an option to disable it in one of the tabs of the wizard, my problem with 2k was getting the data in the table out w/out doing an ExportData.
I've never used 2005, but if its similar to 2008 there is an option in, i believe the second step of the Generate Script wizard, that says Script out Data. Its in the bottom section of the large properties panel looking step. before you pick the objects you want.
Hope it helps.
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
You can try the following transact-sql which will return all the table with the column definition of a particular database.
SELECT * FROM targetDatabase.INFORMATION_SCHEMA.COLUMNS
Thanks
Md. Marufuzzaman
I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.
|
|
|
|
|
Going to try asking this a different way and see if anyone can help.
I have two queries:
SELECT * FROM tblData WHERE Machine = 1 and Operator = 1
and
SELECT SUM(WorkSecs) as WorkTime, SUM(RunSecs) as RunTime
FROM tblData WHERE Machine = 1 and Operator = 1
Both of these queries are in a custom application I wrote that has been in operation for about four years. Both queries were taking only milliseconds to return a record. Some time last week the second query started taking about three seconds to return a record.
The owner of the SQL server admits to mucking about with data table, so I have DROPPED the data table and CREATED it again. I have also backed up the database and truncated the transaction logs (though I don't think it would have any effect on this). The second query is still taking about three seconds to execute.
The custom application is written in C++ using ADO and trusted connection. There are no ODBC/DSN connections defined on the client computer.
Can anyone think of anything I can check on the SQL server or the client?
Thanx,
>>>-----> MikeO
modified on Wednesday, May 26, 2010 10:16 AM
|
|
|
|
|
I don't know enough DBA stuff to answer, but the fact that the second query uses aggregating functions would start me looking at things associated with temporary storage. Also maybe verify the response times by executing the SQL on the server.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
Not sure where the temporary storage would take place. I have executed the query on the server. There is no noticeable delay. And that is puzzling because both the query sent by the code and the query typed into the Management Studio are executed on the server.
Thanx,
>>>-----> MikeO
|
|
|
|
|
In SQL Server Management studio, under the query menu, select the option to "Include Actual execution Plan" and run your query again.
You want to see lots of seeks and no scans. Scans indicate it is reading the table or index from top to bottom.
give it a shot
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
