|
Could someone pont me in the right direction here cos I am pulling my hair out and I have little to start with lol
I am working with 3 tables
User Table:
Auto_ID
User_Name
Profile Table:
Auto_ID
User_ID (This is taken from the Auto_ID in the User Table e.g. 20)
Prof_Img
Comments Table:
Auto_ID
Message_To (This is taken from the Auto_ID in the User Table e.g. 41)
Message_From (This is taken from the Auto_ID in the User Table e.g. 20)
Message
I need to be able to display the Prof_Img for who the message was _to and _from I just can't figure how to get the individual picture references from the same table in the same query. I can get one of the pictures but not both, am probably not thinking about it correctly, well obviously I am or it would work lol
Oh and I need the User_Name to go with each relevant picture
If someone could give me a little guidance please it would save my hair line lol
Many thanks in advance
Regards
Ray
|
|
|
|
|
OK, you need two joins to the same tables (once for from, once for to; and both once for name, once for image); this will only work when you use aliases to discriminate the identical tables.
This will not be correct, but indicates the technique; try something like this:
SELECT FU.Name, TU.Name FROM Comments
JOIN User as FU ON Comments.Message_From=FU.Auto_ID
JOIN User as TU ON Comments.Message_To=TU.Auto_ID
WHERE Comments.Auto_ID=MessageID
which, once correct, you can extend to also handle the images.
|
|
|
|
|
I finally got to sit and try your suggestion and this was wonderful help thank you so much.
I do have another question about this which I will post a new messgae for shortly.
Again thank you very much for your help
Regards
Ray
|
|
|
|
|
You're welcome.
|
|
|
|
|
Hi All,
I have a table with 50000000+ rows in SQL Server 2008 R2 Express. I am querying this database table from a .NET application. Unfortunately this table is not having any PK and probably its now possible to implement one.
When I fire my query it doesn't works due to Timeout. I get error:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
I am reading this with execute dataset method to fill my Datagridview.
Please suggest me approach I should take to make this work.
thanks
|
|
|
|
|
Increase CommandTimeout period to avoid this error
logicon wrote: 50000000+ rows
However I would suggest whenever you are pooling such a huge data in one dataset, if possible, try to fetch data in small chunks.
|
|
|
|
|
millions of rows in a DataGridView? I pity the users then.
This forum holds some 50,000 messages, yet you see only a few (say 25) of them at once.
|
|
|
|
|
Sounds to me like you are trying to fetch way too many rows in one shot. You should re-think your logic and select only the rows you really need in your applicaiton.
If you really need to accessing millions of rows, then maybe you should be using a datareader and not try to fill a dataset.
Why don't you post a brief summary of what you are trying to do any the folks here will give you all kinds of ideas on how to attack the problem.
Good luck. 
|
|
|
|
|
you're talking to the wrong person here.
|
|
|
|
|
Yep.
I replied to the wrong thread. My previous comment should have been applied to the original author, "logicon".
Sorry Luc.
|
|
|
|
|
Hi David,
You are correct, I should use datareader.
The requirement is to provide a tool which brings rows in Excel from SQL Server so that end user can play with it using pivot and other excel stuff. What I initially planned was to write Excel Add-in. Unfortunately due to version issue (I had Excel 2007 and end user is having Excel 2010) I decided to write Windows Form Application which will have a DataGridView and "Export To Excel" button.
Internally, SqlDataAdapter 's Fill is used here in my current code.
I will try the DataReader and share the result with all you
Thanks Everyone.
|
|
|
|
|
Use a DataReader to write a CSV file -- the user can then open the CSV in Excel. Slick as snot.
|
|
|
|
|
Use SQL server to output the resultset to a CSV file on a shared folder - slicker than snot!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
logicon wrote: 50000000+ rows
It's horrible to load at a time, use paging technique in query. An example SQL Server 2005 Paging Results[^]
thatraja |Chennai|India|
Brainbench certifications
Down-votes are like kid's kisses don't reject it
Do what you want quickly because the Doomsday on 2012
|
|
|
|
|
logicon wrote: I have a table with 50000000+ rows in SQL Server 2008 R2 Express...Datagridview
You need to change your requirements.
And what is the growth rate of this table? What happens when it is 10 times bigger than it is now?
|
|
|
|
|
I have a xml file on which i have to operate.
I am using following approach
Declare @c_cmd VARCHAR(255),@C_940IN_FILE varchar(100),@C_940IN_DIR varchar(100)<br />
<br />
Set @C_940IN_FILE ='S_outbound.xml'<br />
Set @C_940IN_DIR ='\\xceed\tech\'<br />
<br />
CREATE TABLE #TEMP_940 <br />
( <br />
ROWDATA varchar(8000) Null <br />
) <br />
<br />
SELECT @c_cmd='BULK INSERT #TEMP_940 FROM '+'''' + @C_940IN_DIR + '\' + @C_940IN_FILE + '''' <br />
+ ' WITH (FIELDTERMINATOR = ''><'')' <br />
PRINT @c_cmd <br />
EXEC(@c_cmd) <br />
----------------------- <br />
select * from #TEMP_940 <br />
<br />
Drop Table #TEMP_940
.
.
It gives me temp table with all nodes, like
<Case_Dimension><br />
<br />
<unit_of_measure>PK</unit_of_measure><br />
<br />
<quantity>6</quantity><br />
<br />
<unit_length/><br />
<br />
<unit_width/><br />
<br />
<unit_height/><br />
<br />
<dimension_measure>CM</dimension_measure><br />
<br />
</Case_Dimension>
Now i have to use a cursor for getting all the values of corresponding fields.
Is there any better way?
By which i get table which will create a temp table like, without using cursor
<br />
unit_of_measure quantity ... .... .... so on<br />
--------------- --------<br />
______PK_______ ___6____ ... ... ....<br />
regards
|
|
|
|
|
Hi
I have read the question a couple of times, but I'm still having trouble understanding your query. So, let's verify what we got;
Hum Dum wrote: I have a xml file on which i have to operate.
That's a physical file, located on the harddisk? I mean, it's not stored inside an SQL Server table or anything like that?
Hum Dum wrote: Now i have to use a cursor for getting all the values of corresponding fields.
To make sure I understood that correctly; you're first reading the columns from that file, and you'd be fetching it's values with a cursor?
Is it a requirement to use Sql to import the data, or would it be allowed to use C# or VB.NET? How will the import-process be started, does it get run by the server automatically, or does the user init the import?
Hum Dum wrote: Is there any better way?
There might be, depending on your requirements and restrictions. If you're allowed to program a solution, I'd rather suggest the XmlDocument[^]-class.
If it has to be done from Sql, I'd suggest converting the file from XML to (several?) CSV-files. Then again, if it's Sql Server, then you might even get away with creating a linked server to your file and SELECT INTO the destination table.
I are Troll 
|
|
|
|
|
Eddy Vluggen wrote: How will the import-process be started, does it get run by the server automatically
SELECT @c_cmd='BULK INSERT #TEMP_940 FROM '+'''' + @C_940IN_DIR + '\' + @C_940IN_FILE + ''''
+ ' WITH (KEEPIDENTITY, FIELDTERMINATOR = ''><'', ROWTERMINATOR= ''<Product_Information>'')'
the above command will import xml data in to temp table #Temp_940 (see my original post).
I just need to execute this command and i have the data of XML.
Now i have to operate on this data row by row and insert it into another table. for that i have to use cursor.
Eddy Vluggen wrote: I'd rather suggest the XmlDocument[^]-class.
I know and used it also. when i suggest to use C# my PM says "NO". 
"You must use SQL server."
So, its not my choice 
|
|
|
|
|
Hum Dum wrote: for that i have to use cursor.
I presume you select statement will not do the job for you
Insert Tablename (columnames....)
select columnnames...
from #Temp_940
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hi....
in which version of sql server i can get the below two option...
1) performance tools
2)database tuning advisor..
please send me the download site...
|
|
|
|
|
SQL Server 2005. But those features(which you have mentioned above) not exists in Express edition so you need buy SQL server 2005 enterprise edition. For more info click here[^]
thatraja |Chennai|India|
Brainbench certifications
Down-votes are like kid's kisses don't reject it
Do what you want quickly because the Doomsday on 2012
|
|
|
|
|
thanks .. i got in 2008 enterprise edition....
|
|
|
|
|
Hi,
In my server the MySQL database is not started. Because, the database was corrupted since the table size is large. So i resolved this issue and started the MySQL server by extending the innodb table space.
The below line is i added in my.cnf
innodb_data_file_path = ibdata1:100M:autoextend:max:500M;ibdata2:100M:autoextend
The MySQL is started and database and tables are there. The problem is when i select any table data, i got the error as "incorrect information in file frm".
How can we fix this issue?. Any idea pls.
Regards,
Periyasamy.R
|
|
|
|
|
restore your backup.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
