|
No offense, but this sounds like a flawed design.
I presume you are transfering the whole dataset as an object using remoting. This means the whole thing gets serialized as XML (even if you override the serialization for the dataset to binary, the contained datatable will serialize to an xml diffgram first - then to binary.) The result is a lot of memory consumed at both ends for serialization/deserialization, as well as a significant amount of time. One solution is to convert the datatable into an array or collection, serialize that as binary, then recreate the dataset at the client end.
if you need the metatdata for the dataset, not just the data, you should create a copy that has an empty datatable, and pass that, then pass the data and reconstruct at the client.
This will reduce the memory footprint by as much as an order of magnitude.
Even so, if the dataset is growing without bound, you will ultimately run out of memory. A better aproach is to transfer the data a few rows at a time, and preferably restrict the transferred data to the 'needed' rows only.
Good luck!
Power corrupts and PowerPoint corrupts absolutely. - Vint Cerf
|
|
|
|
|
I am trying to call Oracle store procedures from MSSQL via linked server connection.
For every kind of oracle store procedure, I am receiving several error messages coming from oracle OLEDB or ODBC provider telling that there are syntax errors.
I am looking for a solution.
Thanks.
|
|
|
|
|
Show the complete code that you're using to call the Oracle stored procedure, please, including OPENQUERY or whatever else, the error text, etc.
Thank you.
Jeff Varszegi
|
|
|
|
|
I am trying to connect to an excel file to read the data in ASP using ADO. I have established a connection but I am not sure why the following SQL statement does not work:
strCmd = "SELECT * from Prices"
I get an error saying:
The Microsoft Jet database engine could not find the object 'Prices'. Make sure the object exists and that you spell its name and the path name correctly.
Why is this error being caused?
'Prices' is the sheet in the excel file that the data is in, is this the correct thing to enter?
Thanks
|
|
|
|
|
According to the MSDN documentation you either need to name the range in the Excel file or use the $ after the object you reference in the spreadsheet. Try something like:
strCmd ="SELECT * FROM [Prices$]";
I can't remember if you need the square brackets but I know the $ helps. If you want more info on naming the range in EXCEL, look at the help for Insert/Name command.
Hope this helps.
Jeff
|
|
|
|
|
Actually, "prices" would look for a named range of that name, while "prices$" looks for a sheet named 'prices', which is why his query fails.
Power corrupts and PowerPoint corrupts absolutely. - Vint Cerf
|
|
|
|
|
1) What is diference between varchar and nvarchar?
2) Anyone knows what is maxlenght of a E-Mail and of a URL?
Thanks for all
|
|
|
|
|
|
Hopes this helps...
Question 1
Char[(n)]
Fixed-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is n bytes. The SQL-92 synonym for char is character.
*Use char when the data values in a column are expected to be consistently close to the same size.
*Use varchar when the data values in a column are expected to vary considerably in size.
nvarchar(n)
Variable-length Unicode character data of n characters. n must be a value from 1 through 4,000. Storage size, in bytes, is two times the number of characters entered. The data entered can be 0 characters in length. The SQL-92 synonyms for nvarchar are national char varying and national character varying.
Question 2
MAXLENGTH sets the maximum number of characters for text or password fields.
I hope that answers question 2... if not please explain?
Scratch... Thanks Jon I misunderstood his question...
|
|
|
|
|
Thank you John and Bryan
My question is because I need create Email and URL fields in my Database and I would like sugestions to size of this fields...
|
|
|
|
|
An nvarchar column is interpreted and stored as UTF-16. If your source language uses UTF-16 for native strings (e.g. VB6, C#, Java, VB.NET), use nvarchar to save all the overhead of translation to and from byte-oriented character sets, and any possibility of trying to store characters not present in the configured collation's byte-oriented character set (which can lead to round-tripping problems). If your source data is in a byte-oriented character set, it may still be worth using nvarchar to avoid problems in conversion between different character sets, if your database is configured with a collation that uses a different character set to your source data.
There is no maximum length for email addresses or URLs. They could be any length. To store these in SQL Server, use a text or ntext column.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Hi Mike, thank you for the response
What you sugests? I wonder between 50 and 255 length to e-mails and URLs...
URL and E-mail sizes appears vary greatly and I do not wish waste space setting a field too large and do not wish lost important data because of a small field, understand?
Again, thank you very much!
|
|
|
|
|
Hello
I have a MS Access DB
with a table
[user_stories]
which looks a bit like
story_id(autnum), story_title(text[200] , story_text[memo], user_id[int], date_posted[datetime], date_last_edited[datetime], publish[yes/no]
How may i select 10 (or N) random recordsets from this table in sql?
regards
Bryce
---
Publitor, making Pubmed easy.
http://www.sohocode.com/publitor
|
|
|
|
|
Hi Bryce,
I do not use Access, so I can't comment on Access SQL other than to say that it would be a very odd thing for a RDBM to do..
You might want get a list of story_id's, pick ten at random in your code and build a comma delimited string (like strIDList= "id1,id2,id3,...,id10"; ), then run a query like:
<br />
"select * from [user_stories] where story_id in (" + strIDList + ")";<br />
Bill
|
|
|
|
|
Anyone knows a SQL Script Editor? My webpage has scripts in .sql files, what create tables, stored procedures and sample data.
But edit this in notepad is very bad... Query Analiser hels a bit... but does not exists anything with "code completion", "syntax highlight", etc.
If possible, "free", "opensource" or "multi-database" (SQL Server, MySQL, Interbase, etc...)
Thanks for all
|
|
|
|
|
There is a tool called SqlBuddy that fits your request nicely...
SqlBuddy is a tool for use with Microsoft SQL Server and MSDE, written in csharp/ DotNet (.NET), to facilitate SQL script writing. It is serves a slightly different purpose that of Query Analyzer, in that it is aimed to *help* the user write SQL.
http://sqlbuddy.sourceforge.net/index.shtml - SQL Buddy (Screen Shots)
http://sourceforge.net/projects/sqlbuddy/ - Download
Note: you must connect to your SQL or MSDE database before you can view a file or Sql script already created. It is just like query analyzer... only better!
Have a great one!
|
|
|
|
|
Thank you very much, Bryan! I go check it!
|
|
|
|
|
The only problems what I find in SqlBuddy are does not search words in script and does not support the GO statement!!!
I will see the sources and try fix this
Thank you!
|
|
|
|
|
Hi all,
I am thinking about writing a program to compare two databases. Basically I need to know what tables are missing or added and what the differences between them are. Has anyone done anything like this before that could give me some pointers? I will be trying to do it using C# and hoping it won't be database specific (if possible).
Thanks
Ash
|
|
|
|
|
Hi Ash,
Assuming you have the privs., you can run this query:
<br />
select * from sysobjects<br />
against each database. That will give you a list of everything in the DB: tables, views, sprocs, triggers, constraints, etc. Use the xtype column to identify what each object is.
hth,
Bill
|
|
|
|
|
Thanks Bill,
I have done that before but unfortunately it makes it database specific. eg have to use all_tables in Oracle.
I was hoping maybe ADO.net had a generic method of getting the structure of the database similar to how you create a XML schema by dragging over the tables from the database explorer.
Ash
|
|
|
|
|
Yes, you can probably write it yourself but when you browse to www.planetsourcecode.com you can probably find a solution there.
Grtz,
Guus
|
|
|
|
|
Hi,
I am currently working on a project recording names of toxins. Some of them have greek lettering and i have found a major problem with MSSQL.
When i came across the character 'ω' which is omega MSSQL inserts it as a ? where it occurs.
I have read around and found it seems to be with collation settings.
If i copy and paste a 'ω' into a asp.net page it says i need to save the page with encoding unicode 1200 which works fine.
When setting collation with MS SQL there is no code 1200 option so i dont know what to do
Even microsoft access by default allows me to use 'ω' but surely there there is a way to do this with an enterprise class DB as MS SQL ?
Please help i'm pulling my hair out!
|
|
|
|
|
I still haven't worked with other code pages in MSSQL yet, although I want to sometime. Have you tried playing around with NVARCHAR yet? Sorry, it's the only thing I can think of off the top of my head.
Thank you.
Jeff Varszegi
|
|
|
|
|
i found my answer
use
N'stringwhichhasunicode'
instead of just using 'stringwhichhasunicode' whenever you use an instance of it.
EG:
INSERT INTO TABLE VALUES(N'value1','value2'
SELECT * FROM TABLE WHERE Column = N'unival'
Atul
|
|
|
|