I have an Access application with various DB's linked together. One of the DB's contains a field, SOURCECODE, which was mistakenly entered in as an nvarchar(4000) field in MS Sql Server. When I link the table, Access converts it to a memo field.
There will never be more than 10 chars in that field. The company that created the DB says its will be too risky to change the data type. I need to link this field to another field that's a VARCHAR.
How can I do this? Access doesn't allow the CAST feature.
Does anyone know how to convert data in a Memo field from Foxpro into a Text field in SQL table? Straight import via DTS package seems to entirely ignore this memo field and result in blank. Thanks in advance for your reply.
I'm using DTS to import data from an Access memo field into a SQL Server ntext field. DTS is only importing the first 255 characters of the memo field and truncating the rest.I'd appreciate any insights into what may be causing this problem, and what I can do about it.Thanks in advance for any help!
I'm importing an Access database to SQL Server 2000. The issue I ran into is pretty frustrating... All Memo fields that get copied over (as Text fields) appear to be fine and visible in SQL Server Enterprise Manager... except when I display them on the web via ASP - everything is blank (no content at all).
I didn't have that problem with Access, so I ruled out the possibility that there's something wrong with the original data.
Is this some sort of an encoding problem that arose during database import? I would appreciate any pointers.
i've a reasonable amount of experience with MS Access and less experience with SQL Server. I've just written an .NET application that uses an SQL Server database. I need to collate lots of data from around the company in the simplest way, that can then be loaded into the SQL Server database.
I decided to collect the info in Excel because that's what most people know best and is the quickest to use. The idea being i could just copy and paste the records directly into the SQL Server database table (in the same format) using the SQL Server Management Studio, for example.
Trouble is, i have a problem with line feed characters. If an Excel cell contains a chunk of text with line breaks (Chr(10) or Chr(13)) then the copy'n'paste doesn't work - only the text up to the first line break is pasted into the SQL Server database cell. The rest is not pasted for some reason.
I've tried with MS Access too, copying and pasting the contents of a memo field into SQL Server database, but with exactly the same problem. I've tried with 'text' or 'varchar' SQL Server database field formats.
Since i've no experience of using different types of databases interacting together, can someone suggest the simplest way of transferring the data without getting this problem with the line feeds? I don't want to spend hours writing scripts/programs when it's just this linefeed problem that is preventing the whole lot just being cut'n'pasted in 5 seconds!
Can anyone point me any solution how to export a MEMO field from an Access database to a TEXT field from an MS SQL Server 2000. The import export tool from SQL server doesn't import these fields if they are very large - around 9000 characters.
Our company is migrating a Microsoft Access 2010 backend database to a SQL Server 2008 database. One of the memo fields in the Access backend can store up to 150 Kb of Unicode data. To store this data in SQL server, we found that we can use the following data types:
Because ntext will be deprecated in future releases of SQL Server, the only good alternative to store an Access memo field in SQL server is to use nvarchar(max), which is what Microsoft recommends for large Unicode texts.Storing a large amount of text like 150 Kb in an nvarchar(max) field using only SQL server works as expected. However, if Access is used to store the data in a table linked to SQL server, the maximum number of characters allowed is only 4000. We found that this limitation is imposed by the ODBC driver that limits nvarchar(max) to 4000 characters.
The connection string we are currently using to link a table to SQL server is this:
ODBC;DRIVER={SQL Server Native Client 10.0};SERVER= SQLEXPRESS;DATABASE=TestDB;Trusted_Connection=No;UID=uid;PWD=pwd;
Any solution for this limitation storing large amounts of data in a Microsoft Access memo field mapped to an nvarchar(max) data field in a SQL Server database?
I'm having some problems importing data from a memo column (Access) into varchar column in SQL Server.
My idea was to use slowly changing dimesion to identify modified and new rows. No matter what data type I use to convert the memo column (using Data Conversion Transformation) and then using the converted column in SCD, I get the following error : 'The SCD transform does not allow mapping between columns of different types except for DT_STR and DT_WSTR.'
What do I have to do to get Memo column 'to behave' as a string?
Same problem with a different data type - decimal (18,5) in sql server - no matter what datatype I use in Data Conversion Trans, I get the same error trying to generate scd.
I'm trying to create a site which allows me to add Memo type fields but when I insert or edit my record it will not take any of my text after an enter (vbNewLine). In Access I used the field type "Memo" but I do not see that type in SQL Server 2005 just a nvarchar(max) which does not seam to work. Thanks for the help! Chad
Hey guys. I have a SQL query I'm trying to create. It's nice and dandy, but I have a memo field that's being trunicated to 256 characters.
SQL is something like this:
Code:
Select distinct `group`,sortorder,HideLabel,category,img,Description,Null as num,Null as subcat from catalogimages where Inact=0 and subcateg is NULL group by `group`,sortorder,HideLabel,category,img,Description order by `group`,sortorder,category
Description would be the memo field.
Is the 'GROUP BY' clause even necessary here? I'm also willing to bet that the 'DISTINCT' clause might not be necessary. Any help would be greatly appreciated.
I have an access database that I am about to move over to SQL Server. In there I have member table that has a memo field. This table has the potential to grow quite big. My question is: what issues does having a large text field have on my database. Will it slow down my updates, searches or inserts? I plan on using this field for such things like a biography of the member. Is using a text field the best way to go, or should I look at text files instead? Many thanks,
I have an application written in Access 97 that connects to a SQL2000backend. One field is a description field that is a data type NTEXT in theSQL database. In my access form, I can not enter more than 255 characters.Before I converted the backend to SQL, the description field was a memofield in Access.What do I need to do to make it so I can enter more text into this field?
Does anyone know what datatype I could use to store a large amount of text? I just started using sql two days ago and still trying to become familar with it. Took me a whole day to set up a damn trusted connection.....newbie
I have used DTS in SQL Server 2000 to import an MDB filed (MS ACCESS) of a table. When the table is imported the primary key is lost and the memo field data is completely gone.
I use the tranformation option in the DTS wizard to add the primary key and make sure the data type for the memo field is varchar and has a size of 8000. I need that large size since I am storing lots of html code.
When I preview the data I see the html code that is supposed to get imported. However, when I return all rows from the table in Enterprise Manager the field is empty.
So I tried to manually copy the data from the MS Access Database into SQL Server. Could not figure out if SQL Server has an interface like MS Access to simply copy data into a table. So I linked to the tables from MS Access to the SQL Server table.
When I opened the linked table I see the data in the description field. However, if I return the rows from within SQL Server no data is present.
I have some ASP code trying to read the data in the SQL Server table. However, nothing is returned and when I run the SQL Statement, nothing gets returned. The SQL statement returns all rows. All the other data is present but nothing in the description field.
What am I doing wrong? Any suggestions anyone, please!
I have one column in SQL Server 2005 of data type VARCHAR(4000).
I have imported sql Server 2005 database data into one mdb file.After importing a data into the mdb file, above column data type converted into the memo type in the Access database.
now when I am trying to import a data from this MS Access File(db1.mdb) into the another SQL Server 2005 database, got the error of Unicode Converting a memo data type conversion in Export/Import data wizard.
Could you please let me know what is the reason?
I know that memo data type does not supported into the SQl Server 2005.
I am with SQL Server 2005 Standard Edition with SP2.
Please help me to understans this issue correctly?
I need to pass in null/blank value in the date field or declare the field as string and convert date back to string.
I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits. The mfg_start_date is delcared as a string variable
option 1 I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.
I have a table with a column that is currently a varchar(50), but I want to convert it into an int. When I try to just change the type in design mode I get an error that conversion cannot proceed. When I look at the field it appears some of the entries have special characters appended at the end, I see a box after the value.
How can I remove all speical characters and then convert that field to an int?
Also I tried the following query which did not work as well, same error about conversion.
I'm not entirely sure that this is the correct forum for this question but it relates to my SSIS package.
I am currently implementing an SSIS package to replace an existing stored procedure which is getting very unmanageable. I have come across a part in the stored procedure which performs a convert(datetime, @Parameter3, 14) on a string of data. The string value of @Parameter3 is in the following format HH:mm:ss.
The problem i am having is how to implement similar functionality in a script task. everything i have tried involving the datetime object returns 1,1,0001, 00:00:00 or similar. It never seems to get the time so that it can be passed to a datetime field in the database.
I am getting a date string that is contained in a character field (char(20)).
An example of the data is as follows:
2005-09-20121315001
it is in the format YYYY-MM-DDHHMMSSMMM.
I want to insert this value into a datetime field. I have used convert, but can only insert it when I split out the time portion and separate them with a colon
i.e. to get it to insert into a datetime field the data has to be in the following format
2005-09-20 13:15:18.001
YYYY-MM-DD HH:MM:SS.MMM
Is there a style parameter in the convert function that will allow me to insert this value without having to separate the hours/minutes/seconds with a colon? I have tried BOL but it seems that all the examples have colons in them where there is a time portion to the date,
So as the subject says, I have a few fields that are nvarchar but hold date information. Most of these fields I have been able to move to datetime easiliy enough, simply by going into edit mode for the table and converting the fields to datetime. But 1 field is giving me problems I keep getting this error. quote:- Unable to modify table. Arithmetic overflow error converting expression to data type datetime. The statement has been terminated.
I really dont know why I'm getting this error, but I"m assuming it may have to be something like one of the records may not be in date format. But I don't know if this is the case and I don't know how to locate where my problem is coming from. Any guidance is greatly appreciated.
How can I generate the result using typical SQL statement based on the following tables?
Table a - Salesman (salesId, Name) pk : salesId
Table b - Invoice(InvoiceNo, salesId, InvoiceAmt) pk : invoiceNo fk : salesId -> Table a
The result set :
salesId, Name, sum(InvoiceAmt), InvoiceNos with comma separator
For example:
Table a SalesId Name S001 Peter S002 Alice Table b InvoiceNo SalesId InvoiceAmt INV001 S001 $100 INV002 S001 $100 INV003 S001 $400 INV004 S002 $200 Result set SalesId Name Sum(InvoiceAmt) InvoiceNos S001 Peter $600 INV001, INV002, INV003 S002 Alice $200 INV004
how i convert varchar sal field to numeric in query select sum(sal) from emp1 error:the sum or average aggregate operation cannot take a varchar data type as an argument.
I am struggling getting a time field from as/400 into SQL 200 using SSIS. The time field for some reason is being seen as dt_I8, And every type of conversion I try to do with it, it gives me an error about truncation. Example of my time field 12.35.00
How do you convert time field from as/400 which sql is seeing as DT_I8 to a string so that I can combine it with a date field to put it into a database timestamp field. Stacy
The following is my code for Access... can someone help me convert it to sql: My Connectionstring is "server=(local);database=Database;trusted_connection=true"
<%@ Page Language="VB" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDb" %> <script language="VB" runat="server"> Sub btnLogin_OnClick(Src As Object, E As EventArgs) Dim myConnection As OleDbConnection Dim myCommand As OleDbCommand Dim intUserCount As Integer Dim strSQL As String strSQL = "SELECT COUNT(*) FROM tblLoginInfo " _ & "WHERE username='" & Replace(txtUsername.Text, "'", "''") & "' " _ & "AND password='" & Replace(txtPassword.Text, "'", "''") & "';" myConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " _ & "Data Source=" & Server.MapPath("login.mdb") & ";") myCommand = New OleDbCommand(strSQL, myConnection) myConnection.Open() intUserCount = myCommand.ExecuteScalar() myConnection.Close() If intUserCount > 0 Then lblInvalid.Text = "" FormsAuthentication.SetAuthCookie(txtUsername.Text, True) Response.Redirect("login_db-protected.aspx") Else lblInvalid.Text = "Sorry... try again..." End If End Sub </script>
Dear All, I would like to convert from Access To SQL DB undervisual Studio.Net 2005... How can I do it easily, or if there any software to do this automaticlly, please your help.. Awaiting your valuable reply. Many thanks in advance for your cooperation and continuous support....
I am currently in the process of writing an application to convert an Access database to SQL. Basically, I have created an odbc link in access and then I transfer the data from the access table to the linked table.
The problem is where I have to transfer the Identity keys. I can transfer all the data but the keys. They will auto increment. I tried using INSERT_IDENTITY tablename ON but it just returns an error mosty of the time. I have gotten it to transfer once or twice by stepping through.
Here is the basic code:
The code that causes everything to error is commented out.
Private Sub CopySQLRecordSet(ByVal stTableName As String, _ Optional ByVal blIdentity As Boolean = True, _ Optional ByVal stSearch As String = "") Dim cat As ADOX.Catalog Dim tbl As ADOX.Table Set cat = New ADOX.Catalog Set tbl = New ADOX.Table Dim adoCommand As New ADODB.Command, stCommand As String Dim adoSQLCommand As New ADODB.Command Dim errorString As String
On Error GoTo ErrorCopying PrgPart.Value = PrgPart.Value + 1 If blCancelPressed Then End '???fix later cat.ActiveConnection = db1 'This doesn't seem to work with our normal settings for spectrumDbase tbl.ParentCatalog = cat tbl.Name = "dbo_" & stTableName tbl.Properties("Temporary Table") = False 'possibly, this line will work for Oracle as well tbl.Properties("Jet OLEDB:Link Provider String") = "odbc;DSN= ;DATABASE=database;" tbl.Properties("Jet OLEDB:Remote Table Name") = stTableName tbl.Properties("Jet OLEDB:Create Link") = True tbl.Properties("Jet OLEDB:Table Hidden In Access") = False tbl.Properties("Jet OLEDB:Cache Link Name/Password") = False cat.Tables.Append tbl Suspend 1 adoCommand.CommandType = adCmdText adoCommand.ActiveConnection = database adoSQLCommand.CommandType = adCmdText adoSQLCommand.ActiveConnection = rsDbase
'spectrumDBase.BeginTrans 'stCommand = "BEGIN TRANSACTION " & vbNewLine _ ' & "go" & vbNewLine 'If there is an identity field in the table, it must be temporarily disabled 'to insert from an foreign DB. 'If blIdentity Then ' stCommand = stCommand & "SET IDENTITY_INSERT dbo_" & stTableName & " ON " & vbNewLine & "GO" & vbNewLine 'adoCommand.CommandText = stCommand 'adoCommand.Execute 'End If
'insert the records from the source table stCommand = "" stCommand = stCommand & "INSERT INTO dbo_" & stTableName _ & " SELECT * FROM " & stTableName & vbNewLine _ & "GO" & vbNewLine
Set adoCommand = Nothing Set adoSQLCommand = Nothing Exit Sub ErrorCopying: 'need to save this to a string so it doesn't reset when Resume occurs errorString = Err.Description ' MsgBox "Error copying the [" & stTableName & "] table." & vbNewLine _ ' & "Error: " & errorstring Resume errorCatch errorCatch: On Error Resume Next 'set a log Dim fso As FileSystemObject, fStream As TextStream Set fso = New FileSystemObject Set fStream = fso.OpenTextFile(App.Path & "DBTransfer.log", ForAppending, True) fStream.WriteLine "***Error copying the [" & stTableName & "] table." fStream.WriteLine " Error: " & errorString fStream.WriteLine " " fStream.Close Set fStream = Nothing Set fso = Nothing 'clean up adoCommand.CommandText = "DROP TABLE dbo_" & stTableName adoCommand.Execute If blIdentity Then 'reinsert identity property adoSQLCommand.CommandText = "SET IDENTITY_INSERT " & stTableName & " OFF" adoSQLCommand.Execute End If Set adoCommand = Nothing Set adoSQLCommand = Nothing End Sub
Who convert access dba to ms sql 2000 standard. I try with access conversion, but some buttons commands and rules don´t work. The dba contains tabs, forms, rel´s, macros, modules and relations with tabs and examination images. It´s possible convert everything without damage or transform this itens? The dba contains critical information about identification and personal clinic story about patients, but the access capacity it´s out (2GB) and i need to expand this dba.
I have some urgency to this problem, it´s depends to buy a windows 2008 server and mssql server 2008.
