Problem in Displaying NTEXT field from database?

Hello,

I have around 7 ntext fields in my data base table and I am getting data from the data base table through executing stored procedure, But when I am displaying data using record set, few of the ntext fields in recored set are empty .Iam sure that these are having data in table.

I am not sure why recordset is lossing that ntext field data?Because of this I am unable to display that data in web form.

any ideas really appriciated.

Thanks

Ram

text and ntext fields should always be either the only data returned or the last item in a select statement.

For example:

SELECT ntextField FROM myTable WHERE something = true

or

SELECT myFirstField, myOtherField, ntextField FROM myTable WHERE something = true

This is a limitation of MSSQL server and (some versions) of MySQL. If you are using MSSQL Server 2005, I would suggest changing the database column to a nvarchar(MAX) as this will store the same number of characters as an ntext column. (I believe they intended to remove text and ntext from the next release of MSSQL Server in favor of varchar(MAX) and nvarchar(MAX), but don't quote me on that.)

|||

I am using SQLserver 2000, What will be the possible solutions for this problem.

Thanks

|||

You will have to select each ntext field individually. So you will need 7 select statements each time.

However, I would recommend changing the database to use a different type of column. If you know the data will never be larger than 8K you can still use nvarchar(8000) on SQL Server 2000. Or you could combine some of the columns and then use delimiters so that you have one huge column with something like ||| between entries (but this may cause other problems.)

In general text, ntext and blob fields should be used as rarely as possible, it is usually easier to put that much data into a file and then store the files name and location in SQL Server.

Let me know if this doesn't really answer your question.

|||

Hi,

Its working for me now..What I did is ...

I have got all ntext fields from the recordset first into some varialbles before acesing non ntext fields and mapped to the form controls.

thats it.

thanks

Problem copy a table from SQLServer 2000 data base to SQLExpress database using SMO.

I am trying to to copy a table from a SQLserver 2000 database to a new table in as SQLExpress database. The following is the code I used.

Private Sub btnCopyTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCopyTable.Click

Try
mFromDatabase = New Database(mFromSrv, Me.cboSelectFromDatabase.Text)
mToDatabase = New Database(mFromSrv, Me.cboSelectToDatabase.Text)
'mToDatabase.Create()
Dim xfr As New Transfer
xfr.CopyAllTables = False
'xfr.Options.WithDependencies = False
'xfr.Options.ContinueScriptingOnError = True
xfr.DestinationDatabase = Me.cboSelectToDatabase.Text
xfr.DestinationServer = mFromSrv.Name
xfr.DestinationLoginSecure = True
xfr.CopySchema = True
xfr.TransferData()
Catch ex As Exception
Dim errMsg As String
errMsg = ex.ToString
Throw
End Try

End Sub

When I do this I get the following error on the xfr.TransferData line.

Microsoft.SqlServer.Management.Smo.SmoException: The Integration Services component is not installed or you do not have permission to use it. > System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.SqlServer.DtsTransferProvider, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.
File name: 'Microsoft.SqlServer.DtsTransferProvider, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'
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.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.Load(String assemblyString)
at Microsoft.SqlServer.Management.Smo.Transfer.CreateDtsTransferProvider()

=== Pre-bind state information ===
LOG: User = ACER-1\phowatt
LOG: DisplayName = Microsoft.SqlServer.DtsTransferProvider, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
(Fully-specified)
LOG: Appbase = file:///D:/VS2005_Projects/AnalyzeProjects/Forms/bin/
LOG: Initial PrivatePath = NULL
Calling assembly : Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91.
===
LOG: This bind starts in default load context.
LOG: Using application configuration file: D:\VS2005_Projects\AnalyzeProjects\Forms\bin\Forms.vshost.exe.config
LOG: Using machine configuration file from C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\config\machine.config.
LOG: Post-policy reference: Microsoft.SqlServer.DtsTransferProvider, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
LOG: Attempting download of new URL file:///D:/VS2005_Projects/AnalyzeProjects/Forms/bin/Microsoft.SqlServer.DtsTransferProvider.DLL.
LOG: Attempting download of new URL file:///D:/VS2005_Projects/AnalyzeProjects/Forms/bin/Microsoft.SqlServer.DtsTransferProvider/Microsoft.SqlServer.DtsTransferProvider.DLL.
LOG: Attempting download of new URL file:///D:/VS2005_Projects/AnalyzeProjects/Forms/bin/Microsoft.SqlServer.DtsTransferProvider.EXE.
LOG: Attempting download of new URL file:///D:/VS2005_Projects/AnalyzeProjects/Forms/bin/Microsoft.SqlServer.DtsTransferProvider/Microsoft.SqlServer.DtsTransferProvider.EXE.

End of inner exception stack trace
at Microsoft.SqlServer.Management.Smo.Transfer.CreateDtsTransferProvider()
at Microsoft.SqlServer.Management.Smo.Transfer.GetTransferProvider()
at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
at Forms.frmTransfer.btnCopyTable_Click(Object sender, EventArgs e) in D:\VS2005_Projects\AnalyzeProjects\Forms\frmTransfer.vb:line 149

I used the following references with this code.

Microsoft.SqlServer.ConnectionInfo

Microsoft.SqlServer.Smo

Microsoft.SqlServer.SmoEnum

Microsoft.SqlServer.SqlEnum

SMO uses SSIS to perform data transfer operations, but SQL Server Express doesn't install the SSIS engine, so the transfer operation fails because the service SMO relies on to perform the operation doesn't exist on the machine you're running on. On this page of my blog - http://sqljunkies.com/WebLog/marathonsqlguy/archive/2006/10/31/24756.aspx - I described how to build a table from an existing one. Perhaps you can use this code to build your destination tables, and then use Transact-SQL to copy the data from the source to the destination database.|||I also have SQL Server 2005. If I install it will that install SSIS? Is there another way to install the SSIS engine? Or maybe your recommended approach is the better approach in any case.|||The full versions (Developer, Standard, Enterprise) of SQL Server allow you to install SSIS, which I believe will solve your problem (based on the error messages in your first post.)|||I have written myself a little application that enables me to set the various copy properties and option properties and then display the resulting scripts so that I can see how the various copy and option property settings affect the outcome. Since there is little documentation about SMO you cannot depend on your intuition about how these different property settings might affect the resulting script. Also there is IMO a shortcomming in the scripting of views. The problem occurs when there are views that are based on other views. If the underlying views are not created first then there will be an error when using the script when trying to create a view that uses another view and that other view has not yet been created. You might think that setting the CopyAllViews to True and setting the Options.WithDependencies to True would be how to deal with that but it appears that the Options.WithDependencies only affects scripts for tables. It appears that if you try to copy a database from one server to another and there are views like I have described above the Transfer will fail and it seems that there is no way to set the options to avoid this problem. So it appears that copying a database using Transfer will work only if there are no conditions in the source database that Transfer cannot handle.

problem connexion to db

i m using an sqlserver base in creating dynamic site with asp.
i m using automatic asp generator code named aspmaker.

in aspmaker i can connect to the db whitout problems and so generate html pages . the problem is that when i ry navigating in my new site i get this message :

error type :
Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
[Microsoft][ODBC SQL Server Driver][SQL Server]connexion fail of user
'AWATEF\IUSR_AWATEF'.
/inbmi/fournisseurlist.asp, line 114

navigator type:
Mozilla/4.0 (compatible; MSIE 5.01; Windows NT 5.0)

Page :
GET /inbmi/fournisseurlist.aspCheck the permissions for the user 'AWATEF\IUSR_AWATEF', check your ODBC configuration, also check the ASP forums.