Way To Retrieve Actual Size Of Ntext Column In Native Code?
Aug 3, 2007
Howdy folks! (I feel im creating too many topics...but all my questions are unrelated :-/)
Dev environment: SSCE, Native c++ OLE code, Win CE 5
I have a routine that calculates the size of a set of columns in order to create a byte buffer for retrieving data from an IRowset and inserting it into a custom class. This method works great for all data types except ntext. When I look at the column information from an ntext field, the DBCOLUMNINFO::ulColumnSize member holds 536,870,911 (the max length of an ntext type). Obviously I dont want to allocate 1GB of memory for each ntext column. Is there anyway (natively) to determine the actual number of characters in a field?
View 12 Replies
ADVERTISEMENT
Aug 11, 2005
Hi,
Please help me to find out the ntext column size in a table.
Thanks in advance
Regards
Karthik
View 2 Replies
View Related
Apr 20, 2015
The Tabular model is showing 19 GB on disk, but it is acquiring around 40 GB in memory.
View 3 Replies
View Related
Feb 15, 2000
Hi,
An MSSQL DB running SAP indicates a smaller DB size (MMC & SAP) than the actual physical size. The difference is about 8 GB.
A lot of records were deleted before this. Did they remain in the DB as NULL values or something ?
Does anyone know what the reason for this could be ? And how to clean this up ?
Thanks in advance,
Paul
View 2 Replies
View Related
Jun 29, 2004
Hi,
How do I check what is the actual DB size that is currently being use?
e.g. I set 10 GB as initial size. A few days and a few transactions later, how can I know how much was used since it is still under 10GB and checking the physical DB file will not tell me anything.
View 2 Replies
View Related
Jun 21, 2004
Hi Everybody,
I like to compute the actual physical database file size using queries.
From the EM database properties, the file size shown is not matching with the master..sysaltfiles.size
Ex. Actual file size is 12MB and 1MB for MDF & LDF respectively.
'sysaltfiles' shows 1464 & 96 resp. Since they are shoing it as 8K Pages,
96*8 = 768KB is not matching with 1024KB for the LDF file.
Is there any overheads (additional space from the physical file for file headers) allocated?
Thanks
Babu
View 3 Replies
View Related
Aug 16, 2007
Need to know the actual size on the tape drive
I€™m using LTO3 tape drive (400GB) the used media is for LTO2, currently I€™m looking for LTO4(1.6TB) but I need the backward compatibility to support LTO3 tapes so I can go for LTO2 media or LTO3 media.
I need to know the size of the database backup (Ms SQL2005 SP1 x64-bit, on Ms Windows 2003 R2 x64-bit) on the tape drive. I can see the backup size of the database but not the size on the tape which is using hardware compression.
Is there a way or a tool to view that.
View 4 Replies
View Related
Aug 28, 2007
Sql Server has many data types.
For Example:
smallint
Integer data from -2^15 (-32,768) through 2^15 - 1 (32,767). Storage size is 2 bytes.
I want to know that
If it contains like 0 or 100 or 1000 or -200 or -2000 or more or less.
What will its actual size?
2 bytes or change with the value.
Please also mention the reference with your answer. if available.
View 3 Replies
View Related
Aug 25, 2014
Someone claimed that the query:
INSERT INTO [table1] SELECT * FROM [table1]
was a result of running DBCC DBREINDEX.I wasn't able to replicate this when I did a trace on my local machine, but SQL Monitor from Red-Gate shows that query happening for at least some of the tables that are being reindexed via a job. Is there anyway to examine the actual code inside DBREINDEX to see exactly what commands it may execute?
View 3 Replies
View Related
Jul 16, 2007
Hi,
What is the best way to have native win32 code tell SQL Server 2005 Express to synchronize for merge replication? What API should I use? Should I exec the distrib.exe and/or replmerg.exe command line utilities? Should I use "Windows Synchronization Manager"? Are they other options?
Thanks,
Bryan
View 4 Replies
View Related
Dec 9, 2003
hi,
Does anyone have any sample code for using ntext data type, when inserting or reading big block of texts? How can you read a big huge block of text back into c#?
thx
-Philip
View 1 Replies
View Related
Jun 26, 2007
I've been searching for a bit but I can't seem to find a difinitive answer.
Can an SQL Server trigger call native C/C++ functions? If so, what is the mechanism?
Thanks in advance.
View 7 Replies
View Related
Mar 29, 2006
I couldn't insert more than 4000 charachters in "ntext" type in SQL server. Is there anyway, i can increase the size or any suggestions?
View 7 Replies
View Related
Feb 26, 2007
I'm getting this error while executing a SQLCECommand (ExecuteNonQuery):
Native Code: 25009
Massage: Unspecified error
HResult: 2147467259
Source: SQL Server 2005
Mobile Edition ADO.NET
Data Provider
This happens only sometimes. If I recreate the command object and executing it again (with the same connection object) no error occurs.
Except for this case the application works fine.
Thanks,
Laschek
View 1 Replies
View Related
Sep 27, 2006
Hello, everyone.
I have to make a gateway to access third-party closed-source native-code COM objects. As an interface, SQL Server stored procedures are absolutely perfect for my needs. I decided to use SQL Server 2005 CLR rather than using deprecated extended stored procedures or accessing COM objects directly through SQL Server. (This decision is aided by the fact that the third party has plans to make a .NET version of their COM objects "really soon now" (i.e.: not soon enough). Backporting their new interface to my abstraction layer will be very simple.)
I'm having problems using these COM objects from my SQL Server 2005 CLR stored procedure. When I try to run my stored procedure, I get the error below. It appears that SQL Server/CLR refuses to perform the disk access necessary to load the COM object from disk so that it can instantiate it. (My message continues after the error.)
Msg 6522, Level 16, State 1, Procedure MyStoredProcedure, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'MyStoredProcedure':
System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
System.IO.FileLoadException:
at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoadFrom(String assemblyFile, Evidence securityEvidence, Byte[] hashValue, AssemblyHashAlgorithm hashAlgorithm, Boolean forIntrospection, StackCrawlMark& stackMark)
at System.Reflection.Assembly.LoadFrom(String assemblyFile, Evidence securityEvidence, Byte[] hashValue, AssemblyHashAlgorithm hashAlgorithm)
at System.Activator.CreateComInstanceFrom(String assemblyName, String typeName, Byte[] hashValue, AssemblyHashAlgorithm hashAlgorithm)
at System.Activator.CreateComInstanceFrom(String assemblyName, String typeName)
at UserDefinedFunctions.MyStoredProcedure()
.
I'll outline the steps I performed so far to get the assembly working:
Ensured I was running at SQL Server 2005 compatibility level. (90)
- Installed and configured the third-party COM objects on my devel box and my DB box. They registered themselves properly.
- Enabled CLR in the DB.
- Made sure the DB owner has UNSAFE ASSEMBLY permission.
- Added the TRUSTWORTHY property to the DB. (Yes, this isn't the "correct" way to do it, but I didn't want to deal with code-signing voodoo. Apologies to actual believers in voodoo.)
- Created an Unsafe assembly "MyAssembly" in SQL Server 2005 and Visual Studio 2005.
- Created all the necessary Interop assemblies (by creating a normal (non-SQL) C# project and building my code, and raiding the bin directory).
- Added these Interop assemblies to SQL Server 2005 as Unsafe assemblies.
- In VS2005, built my SQL Project code and deployed successfully.
- Tried running my stored procedure through a Query window, and I got the error above.
I've done many searches. All of the results I've found concern themselves with Web Services and XML serialization, and suggest using sgen.exe. I don't think they're relevant to my problem. A few said I should add my COM DLLs as assemblies to SQL Server, but that is impossible for native code DLLs. I found something that hinted at tblexp.exe, but I didn't understand how that could help me. I already figured out a way to get me the Interop libs.
Efforts at solving:
- Reading webpages for hours on end.
- I tried using sgen.exe to create the XML serialization assembly, but it didn't help.
- I tried adding various native-code DLLs directly as SQL Server assemblies, but you can bet it didn't like that.
- A few other pitiful attempts not worth mentioning.
- Reading this forum. I didn't see anything that applied.
- Posting here.
I really wish Microsoft had meant what they said when they stated that "Unsafe" assemblies can access anything, instead of trying to protect developers from their folly.
Any insights on getting those COM objects to work in my CLR stored procedure? Thank you.
-Tony Flay
View 3 Replies
View Related
Jan 22, 2006
I had several databases under sqlserver ce 2.0 (in my Pocket PC) which contained ntext fields. The size of the databases varies from 50,000 to 700,000 records. The size of an ntext field is from 4 bytes to 2 megabytes.
When I recreated my databases under sql server 2005 mobile on my desktop using VS2005 (see my post just under this one), I saw a big difference between the old and new database sizes. This problem was mentioned in one of the posts in this forum and the reply was to replace ntext data with ncharvar type.
Since most of my data was longer than 4000 bytes (which is the limit for nvarchar type), I couldn't use this suggestion. Instead, I changed my ntext type to image type and used a GetByte conversion.
No change! The size of the new database is still 50 % larger than the original. Since the difference is around 300 MB, this is an unacceptable thing.
Now, I either wait from somebody to suggest a new solution (apart from keeping the ntext data in a separate binary file and keep index of the records of this file in the records of sql database) or, most preferably, have
Microsoft solve this problem as soon as possible.
Thanks for any help in advance
Talat
View 4 Replies
View Related
Jul 12, 2006
Hi,
I experienced this problems on both Windows Mobile 2003 SE and Windows Mobile 5.0.
Its native development (c++, oledb, atl and mfc).
It's quite simple to reproduce...
1. open a database
2. open a rowset on table A (whatever, valid of course and with both IOpenRowset and ICommandText), read datas and close rowset
3. power off
4. power on
5. try step 2 with another table (failed on openrowset with error 0x80004005) or try table A (sometimes working because of cached memory, sometims failed on Read Datas).
6. being stuck ;-)
Our work-around was, in case we loose our connection (identified by error 0x80004005 on openrowset), we close it and re-open database... ugly for sure, but working.
What I'm looking now is to use some kind of "detection method" like what people in .Net develoment are using "if ConnectionState.Open <> ...) for reopening my database only on demand...
Thanks in advance for any hints,
Fabien.
View 5 Replies
View Related
Jan 29, 2015
Query for output this result on Table C ?
========================================================
I have one table store below information, and other one table is store staff name and phone number. how to display of table c data by new column and using the key staff_code1=staff_id or staff_code2= staff_id or staff_code3=staff_id ?
---------------------------------------------------------
Table A
staff_code1,staff_code2,staff_code3
Table B
staff name
staff_id
staff_phone
-----------------------------------------------
Table C display in new column .
staff name-1 code1 staff name-2 code2 , staff name-3 code3
peter id-01 susuan id-03 david id-05
View 1 Replies
View Related
Jul 25, 2006
I have a view in SQLServer 2005. It took 30 sec. to finish. Then I deleted 4500 records from one table that is used in view. It took 90 sec. to finish now. I did a comparison on Actual Execution Plan between before I deleted data and after I deleted data, they are almost same, only different is Actual Number Rows become less after deleted data. So, I wonder why data become less but time become more. When I look closely on the Actual Execution Plan, the ridiculous thing is, there are only Estimated Operation Cost on each step, no Actual Operation Cost. I guess there are something wrong with optimizer because reuse same Execution Plan, but how can I tell which step wrong without Actual Operation Cost.
Thanks!
Henry
View 2 Replies
View Related
Jul 12, 2004
Hello fellow .net developers,
In a website I'm working on I need to be able to put all of the user tables in a database in a dropdownlist.
Another dropdownlist then will autopopulate itself with the names of all the columns from the table selected in the first dropdownlist.
So, what I need to know is: is there a sql statement that can return this type of information?
Example:
Table Names in Database: Customers, Suppliers
Columns in Customers Table: Name, Phone, Email, Address
I click on the word "Customers" in the first dropdownlist.
I then see the words "Name", "Phone", "Email", "Address" in the second dropdownlist.
I'm sure you all know this (but I'll say it anyways): I could hardcode this stuff in my code behind file, but that would be really annoying and if the table structure changes I would have to revise my code on the webpage. So any ideas on how to do this the right way would be really cool.
Thanks in advance,
Robert
View 5 Replies
View Related
Mar 14, 2006
i have a SQL backend and some of the data in my tables is more or less configuration data and some times i only want to pull one row of data which i can do just fine with a SQL query but whats the best method to pull it.
assume i have a query like this.
Select A, B, C from Table1 where ID=xyz
whats the easiest way to get A, B and C in to string variables?
I know i can use the sqldatasource control but i just feel there is too much overhead there.
whats your suggestions?
Thanks
Justin
View 1 Replies
View Related
Dec 11, 2014
I can retrieve the sql code of a view as follows:
exec sp_helptext @viewName
The result will be in several lines which shows the lines of the sqlcode...
Is it possible to return the sql but not the line numbers?
View 4 Replies
View Related
Aug 1, 2006
Hi,
I want to get an image from a sql server database and display it with an asp:image control. I use C# in MS Visual Studio .Net 2005 and Sql server 2005.
All I've done is:
// and Page Display_image.aspx.cs
protected void Page_Load(object sender, EventArgs e)
{
try
{
SqlConnection Con = new SqlConnection(
"server=localhost;" +
"database=;" +
"uid=;" +
"password=;");
System.String SqlCmd = "SELECT img_data FROM Image WHERE business_id = 2";
System.Data.SqlClient.SqlCommand SqlCmdObj = new System.Data.SqlClient.SqlCommand(SqlCmd, Con);
Con.Open();
System.Data.SqlClient.SqlDataReader SqlReader = SqlCmdObj.ExecuteReader(CommandBehavior.CloseConnection);
SqlReader.Read();
System.Web.HttpContext.Current.Response.ContentType = "image/jpeg";
// I write this:
System.Web.HttpContext.Current.Response.BinaryWrite((byte[])SqlReader["img_data"]);
// Or this:
//System.Drawing.Image _image = System.Drawing.Image.FromStream(new System.IO.MemoryStream((byte[])SqlReader["img_data"]));
//System.Drawing.Image _newimage = _image.GetThumbnailImage(100, 100, null, new System.IntPtr());
//_newimage.Save(System.Web.HttpContext.Current.Response.OutputStream, System.Drawing.Imaging.ImageFormat.Jpeg);
Con.Close();
}
catch (System.Exception Ex)
{
System.Web.HttpContext.Current.Trace.Write(Ex.Message.ToString());
}
}
Both work the same way. I mean the image is displayed well but when I view code of the web page I see this:
....
GIF89aåo÷óå݉97)HS¢Å’xL£0¬B´ç¬D(áâKܠô²â€“I€8`È®û l1ãÂ?#KžL(ˆ*X±â€°Î"«Mè±â€ Ÿ
....
TOO MUCH characters before the html tag. And any code of the master page used for this page does not work as well!
Can anyone help me with this? I've tried for 2 days but I still fail.
Thanks.
View 2 Replies
View Related
Nov 20, 2006
Hello again!
I got a problem using an OLE-DB Source in my data flow task. It is an Informix-Server using the newest OLE DB-Driver provided by IBM. However, when I set up the OLE DB-Source with connection manager and SQl-Query, I get the following warning message:
Warning at {2B334CA3-A792-4BC2-93AA-12FADCF1E696} [OLE DB Source [45]]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.
Later when I try to execute the package, the destination adapter gets errors like
Error 3 Validation error. Data Flow Task: OLE DB Destination [2985]: Columns "antrag" and "Antrag" cannot convert between unicode and non-unicode string data types. Informix_OLEDB.dtsx 0 0
I guess that SQL Server cannot get the data types from the Source.
Has anyone an idea? Thanks in advance!
View 11 Replies
View Related
Nov 5, 2007
I am interested in retrieving the original SQL code of various stored procedures & views that I have created & pass them into a field within a table.
I have used one before but cannot remember whether I used a stored proc or system view to retrieve this information, can anyone be of assistance???
View 3 Replies
View Related
Aug 25, 2006
Hi All,
In my application on click of Delete button, actually I am not deleting the record. I am just updating the flag. But before updating the record I just want to know the dependency of the record ie. if the record I am deleting(internally updating) exists any where in the dependent tables, it should warn the user with message that it is available in the child table, so it can not be deleted. I want to check the dependency dynamically. For that I have written the following procedure. But it is updating in both cases.
CREATE proc SProc_DeleteRecord
@TableName varchar(50),
@DeleteCondition nVarchar(200)
As
Begin
Begin Transaction
Declare @DelString nvarchar(4000)
set @DelString = 'Delete from ' + @TableName + ' where ' + @DeleteCondition
execute sp_executeSql @DelString
RollBack Transaction --because i donot want to delete the record in any case
If @@Error <> 0
Begin
select '1'
End
Else
Begin
Declare @SQLString nvarchar(4000)
set @SQLString = 'Update ' + @TableName + ' set DeletedFlag=1 where ' + @DeleteCondition
execute sp_executeSql @SQLString
select '0'
End
End
Thanks & Regards
Bijay
View 4 Replies
View Related
Feb 27, 2004
I have some text in an NText column that could be quite long.
for the web page that displays it, i want to show the first 200 characters and then have a "more..." link to bring up the full text.
i'd like to create a stored procedure that takes the left 200 characters and copies them to a ShortText column (NVarChar) for initial display and then id like to set a bit column to indicate if the length of the NText column is greater than 200.
Len and Left cant be used against NText so how can i work with the NText data ??
View 2 Replies
View Related
Mar 14, 2008
Hi,
I have an SSIS Package where I am looping through all input columns and creating an XML out of it. The Problem comes in one of the tables where I have an Ntext column. Instead of giving me the value of the column, it gives me
"Microsoft.SqlServer.Dts.Pipeline.BlobColumn"
I am trying to get the string value from this blob column. For that I am using the below code. Can you please point me to where I am going wrong. I am not very good in VB so I might be doing some casting error.
Dim blobCol As Microsoft.SqlServer.Dts.Pipeline.BlobColumn
Dim ByteVal As String
For Each column In Me.ComponentMetaData.InputCollection(0).InputColumnCollection
columnValue = rowType.GetProperty(column.Name)
Select Case column.DataType
Case (DataType.DT_NTEXT)
blobCol = DirectCast(columnValue.GetValue(Row, Nothing), Microsoft.SqlServer.Dts.Pipeline.BlobColumn)
ByteVal = System.Text.Encoding.Unicode.GetString(blobCol.GetBlobData(0, CInt(blobCol.Length)))
newXML += ((FormatElement(column.Name) + ReplaceSpecialChars(ByteVal) + FormatElement(column.Name, True)))
Case Else
newXML += ((FormatElement(column.Name) + ReplaceSpecialChars(columnValue.GetValue(Row, Nothing).ToString()) + FormatElement(column.Name, True)))
End Select
Next
Thanks in advance.
Regards
View 8 Replies
View Related
Nov 30, 2007
Hi,
I have developed a Smart Device Application using VB.NET 2003 on top of Windows Mobile 2003 platform which is running on Symbol PPT 8846, a model used in our construction sites for gathering data bar-coded on Pipes, trucks...etc. This application uses SQL CE as its back-end database and since its difficult to cover our sites with WI-FI coverage, most of the time the application is running offline. As for that, on a daily basis, synchronization is done in the offices before the system is released into sites. I have been doing several implementations in several projects and till now everything is running fine but until I got a native exception on exporting the data back into the server database. The server database or main database is using Fox Pro and this is what led me to build a Web Service for communication between SQL CE and Fox Pro. Anyways, going back to that mysterious exception, I did some of my intensive debug on what caused it. As a start, I went through the code line by line to check if there was a memory leak from P/Invokes; but I was shocked that the exception was raised from "SQLCEDataAdapter.Fill(Dataset)" function. First thought came to mind was to hard reset and to re-install the application but no luck. Since it was about SQLCE, I moved towards checking the data found in the SQLCE database and tried to de-fragment the data found under the table needed for export. What I mean by de-fragmenting is that I grouped the data under different classes or families and created different tables depending on these classes. Then tried exporting again and Bingo the data was exported except one record...That was so weird!! huh? (The database had 515 records)
So I went for the Query Analyzer to try to view that record but no hope, it was taking too long to view the record, I just left it for more than 15 minutes and still no response from the Analyzer...it was so strange since there was only one record in that table!!!?!!! As going further, I was so curious why this record causing that native and inability to be viewed by the Analyzer?? I did testing on the column-level of that record and found out that you can view all the columns except two : nvarchar(200) and nvarchar (20)....I was able to resolve this problem with an SQL statement to update both columns with empty or dummy values...!!!!!
But after this resolution, I really don't know how it could be controlled later on!! and why it happened??
I would be glad if anyone can give me any advise on why such problem popped out and especially on the column-level in SQL CE?? And how to overcome it?
Thanks for your time and effort,
Looking forward to hear from as soon as possible...
Just for Clarification:
.NET CF 1.0 SP3
SQLCE 2.0
were used
View 1 Replies
View Related
Apr 6, 2007
Hi GuysOff late, I've grown with programming that requires more than a number of tables that has foreign keys with other tables' primary keys. It takes a really cumbersome coding to retrieve the code from another table with the other table having foreign keys. My question is, how do we program VS 2005 such that it does all the retrieval of the data from the database instead of us writing the code all by ourself?Is it really good database technique to bend the normalcy rules and have one to two columns having redundant data?Can anyone tell me how to write code that retrieves the foreign key data when the data from the other table is called?Thanks
View 2 Replies
View Related
May 25, 2006
I'm trying to run the following SQL against my ntext column, Testtbl.Task I want to replace where the ampersand sign got HTML encoded to "&", and I want to strip it down to only the ampersand sign and deletethe "amp" and the ";".It executes, but I still have the HTML encoding for the ampersand sign if the issue is NOT the first character in the ntext column. If it's the first part of the ntext column, then it works.
View 5 Replies
View Related
Jan 16, 2007
Hi,
I am working on an application which accesses SQL database. I have a table complaint in which i have a coulmn 'history' of type "ntext". When i insert a value, the value gets stored in the db correctly, but I cannot access the complete data from that field using a simple select query. I had to export the table to a text file only then i could view the complete data of the field.
I wrote the query in query analyzer as:
"select history from complaint where pkey=142"
& the result i got is:
"
###### admin: Jan 8, 2007 3:53:57 PM GMT Type: Inactive (Key 10) Description: Activity yet to be started State: Planned "
This is only a part of the entire string that I had entered.
My requirement is that I need to access the data from the ntext field & save it in a variable & then display the entire data in my application. Can anyone tell me how should i use the select query so that i get the entire data without having to go thru the export data procedure.
Please help!!!
View 2 Replies
View Related
Sep 19, 2007
Hi,
I have a table column type as nText, however there are some Chinese character stored in that field and it is a messed up as it is not readable.
In my vb.net code, I did Convert to unicode by getting the byte of each character and encode it with UTF8 e.g:
Public Shared Function ConvertToUnicode(ByVal s As String) As String
' Convert To Unicode
Dim MyBytes As Byte() = Encoding.Default.GetBytes(s)
Dim GBencoding As Encoding = System.Text.UTF8Encoding.UTF8
Return GBencoding.GetString(MyBytes)
End Function
This works well but ,the problem is that it slows down the process quite alot, and I wonder are there any text encoding method I can use in SQL that can run when i do the SELECT Statement?
SELECT
Convert(MyNTEXTColumn)
....
something like that?
Thanks.
Jon
View 4 Replies
View Related
