|
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.
|
|
|
|
|
Hi Luc pattyn,
Thanks for your reply. Instead of restoring database Is there any other way by changing configuration in "my.cnf" file?.
Thanks,
Periyasamy.R
|
|
|
|
|
Hi
Can anyone advise/help me with the following
I am trying to
Select from table
where xfield HAS either (,),/,%, : in the first 3chars
For instance, the query should return the row
G1: darararararararar
and not
G12 easfsafsafsfsfsfsfsf
Thanks in advance
|
|
|
|
|
Can you explain more your question?
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
|
|
|
|
|
You could try this:
select * from Table
where (xfield like '[,/%:]%' OR xfield like '_[,/%:]%' OR xfield like '__[,/%:]%')
|
|
|
|
|
How about
where instr(substr(xfield,1,3),'(') > 0
or instr(substr(xfield,1,3),')') > 0
or instr(substr(xfield,1,3),'/') > 0
or instr(substr(xfield,1,3),'%') > 0
or instr(substr(xfield,1,3),':') > 0
Can't remember if the % or : character need to be escaped.
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]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
There is probably a really easy answer to this series of questions but I'm sort of a newbie in designing a database.
I know what a relational database is. I'm just not that familiar with designing one. My problem: Let's say I want to design a GUI interface for a kiosk for a library or bookstore. There could be several implementations and it could even be thought of as HTML links but the GUI looks nicer. The reason for this comparison is because I want the categories and sub categories to be dynamic, based on information stored in tables and obtained through queries.
I was thinking that I could have a table of main categories called "cats", and a table of sub categories called "sub cats", and of course a table for all of the books with a sub-category field. If a GUI is used, a blob for the image that represents the category or sub category can be assigned a field in the tables.
If I query the cats table and display all of the menu selections for the main categories and then when a main menu selection is made, say for "biographies", I can query for the second level menu items by looking for records in the "sub cat" table with a "Parent" field of "Biographies" which I imagine this could be only two records like "Biographies" and "Auto Biographies" and then drill down to the books table with a query to either of those two sub categories if a menu selection is made.
The problem I have understanding is a many-to-one relationship and how to implement it. I've discussed the first and 2nd level menus but if there is a third level menu and a book, item, or even a fourth level menu, can belong to more than one sub category, basically a sub cat or item with more than one parent, what do I do? Does this require multiple blank fields in one of the sub cat or book tables? Or, should this be implemented through using a primary key that encompasses many fields, or, even possibly should I have a field that declares what level menu the sub cat belongs in?
|
|
|
|
|
Put all categories in the same table and let the subcategories reference their parent categories like this:
CREATE TABLE Categories (
id NUMBER(8,0) NOT NULL,
parentid NUMBER(8,0) NULL,
name VARCHAR2(36) NOT NULL,
more stuff..
)
/
ALTER TABLE Categories ADD CONSTRAINT Categories_pk PRIMARY KEY (id)
/
ALTER TABLE Categories ADD CONSTRAINT Categories_id_fk FOREIGN KEY (parentid) REFERENCES Categories (id)
Then select the top categories with: select id,name from categories where parentid is null
And a subcategory with: select id,name from categories where parentid = <ID>
The self referencing foreign key blocks accidental deletion of a category that has subcategories
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
Yes, exactly. 
|
|
|
|
|
Wow, I'm impressed. I had no idea it could be accomplished in so few of steps. Of course, I'm still trying to decode some of the "ADD CONSTRAINT" and REFERENCES language but I get the basic gist of what you're saying. I also had no idea that you could do the self referencing and thought, at minimum, it would require two tables. Very cool.
Thanks so much !!
|
|
|
|
|
I have a stored procedure defined thusly -- actual variable, database, and table names have been altered for NDA reasons:
CREATE PROCEDURE [dbo].[CountUniqueBirthdays]
@PersonID bigint
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CalendarDaysToSearch int
SET @CalendarDaysToSearch = 90
SELECT COUNT(DISTINCT o.Birthdays)
FROM
[MYDATABASE].[dbo].[PersonInfo] n,
[MYDATABASE].[dbo].[PersonInfo] o
WHERE
n.PersonID = @PersonID AND
--n.PersonID <> o.PersonID AND
-- all leads with...
n.KeyPerson=o.KeyPersonAND
-- within X days of current lead
o.CalendarDate between dateadd(dd, (-1 * @CalendarDaysToSearch ), n.CalendarDate ) and dateadd(dd, 0, n.CalendarDate )
END
For the sake of argument, assume this stored procedure is written correctly and is known to work. Now, I opened up VS2005 (my boss wants me to use it) and in the Data Sources window, I did the whole Add New Data Source/Data Source Configuration Wizard yada yada and added a TableAdapter to my DataSet for PersonInfo (not the real table name again).
I configured the TableAdapterto do a SELECT PersonId FROM PersonInfo and then also to call the Stored Procedure, CountUniqueBirthdays, above. ok, so i write the code to grab the records containing only the PersonIDcolumn and then I iterate over the column, passing each ID into the stored procedure one by one to check if the number of distinct persons with birthdays in a certain 90-day period is bigger than 1 -- AND DON'T ASK ME WHY DID I NOT JUST USE A CURSOR, CURSORS ARE SLOW AND WE HATE THEM, as below.
You know how you can open .xsd files in the Dataset Designer in VS2005? Well, I did, right-clicked the PersonInfo TableAdapter, clicked Add > New Query... and in the Add New Query Wizard I picked Existing stored procedure, and then configured CountUniqueBirthdays as the stored procedure to use.
<br />
try<br />
{<br />
Console.WriteLine("Querying the MYDATABASE.dbo.PersonInfo table...Please wait.");<br />
MYDATABASEDataSetTableAdapters.PersonInfoTableAdapter adapter =<br />
new MYDATABASEDataSetTableAdapters.PersonInfoTableAdapter();<br />
MYDATABASEDataSet.PersonInfoDataTable table =<br />
adapter.GetData();<br />
Console.WriteLine("Finished getting data.");<br />
<br />
Console.WriteLine("Running the CountUniqueBirthdays stored procdure. Searching for return values > 1...");<br />
foreach (MYDATABASEDataSet.PersonInfoRow row in table.Rows)<br />
{<br />
long? personID = row.PersonId;<br />
if (!personID.HasValue)<br />
continue;<br />
<br />
Console.WriteLine("Checking output of CountUniqueBirthdays for PersonId = {0}...", leadID);<br />
<br />
long? value = adapter.CountUniqueBirthdays(leadID);<br />
if (value.HasValue) {<br />
if (value.Value > 1)<br />
{<br />
Console.WriteLine("Value > 1 found for return from LQSCountVendor190Days...for <br />
leadId = {0}",leadID);<br />
break;<br />
}<br />
}<br />
}<br />
}<br />
catch (Exception e)<br />
{<br />
Console.WriteLine(e.Message);<br />
return;<br />
}
The output from the console app is:
<br />
Querying the MYDATABASE.dbo.PersonInfo table...Please wait.<br />
Finished getting data.<br />
Running the CountUniqueBirthdays stored procdure. Searching for return values > 1...<br />
Checking output of CountUniqueBirthdays for PersonId = 8752702...<br />
Specified cast is not valid.
Why? I followed what the IntelliSense had me do when I was typing in the stored procedure call! I don't get it, can somebody please help a newbie at Database programming?
Brian
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
