Hi Everyone -
iam facing a small problem i want to convert an output from the context_info() which is binary to string (or int)
example:
SET CONTEXT_INFO 3
select CONTEXT_INFO() --- it will return 0x0000000300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
hello all, I am reading a file that contains textual data (formatted,e.g font=bold,font size=10 etc). After reading the file, i store the data read from the file to a table in Sql server 2005. But in the table, this data is stored like this:
daohORIGINAL TEXTm............
how can i just store the original text of the file in the table and get rid of these boxes? Or when i read this data from table, how to remove these boxes and get only the original text? Thanks in advance. I am using C# FileStream to read the file from disk. VS 2005
I am trying to insert a row into a table of Microsoft SQL Server 2000.
There are various columns.
[SNO] [numeric](3, 0) NOT NULL , [DATT] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [DATTA] [char] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [CODECS] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
The [DATTA] column is causing a problem. Even if I am trying to put only 1700 character string into [DATTA], the java code throws the following exception:-
StaleConnecti A CONM7007I: Mapping the following SQLException, with ErrorCode 0 and SQLState 08S01, to a StaleConnectionException: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Connection reset
at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
Why is it throwing an exception even though the sum-total of this row doesn't exceed 8000 characters?
I have a Data Control Task that has a OLE DB Source and corresponding OLE DB Destination Data Flow Task. In the referenced source table there exists a field defined as a varchar(12), in the corresponding destination table it is defined as a binary(12). How do I perform this conversion?
I tried inserting a Data Conversion Task and assigning the new data type as byte stream[DT_BYTES] and a Length = 12, but this was a bust. Output test is as follows:
Error: 0xC020901C at DFT - Windows, OLE_SRC - Windows [1]: There was an error with output column "Payload" (40) on output "OLE DB Source Output" (12). The column status returned was: "The value could not be converted because of a potential loss of data.".
Hello,I'm trying to decifer the data in the table that stores the data in thebinary format. All numbers are placed in varbinary fields. All I know is theMS SQL 2000 database useing collation SQL_Latin1_General_CP1_CI_AS(default).For example the content of the field is:(0xB4F5000000000000) in unicode and defined as varbinary(8).Are there any procedures that convert the unicode binary (or hexa) numbersback to ascii or readable form?I tried as following but it didn't work.select cast(0xB4F5000000000000 as decimal(8,2))Any help is appreciated,Adam
There must be a way to convert the binarydata field in the SQL trace outputto text data when the event type is a show plan. SQL Profiler does it buthow can it be done from an imported table?Danny
Hi all,how r u everyone, guys i need a help, i have a interface in my application, adding attachments to issues, ie users can attach a file for a particular issue and if anyone have to view they ve click a link and it will download.iam using filestream and binaryreader concept in that, ie read the file using filestream and then use binaryreader.readbytes method to convert it into byte array and store it in the database in a column with a image datatype as a binary content. FileStream oImg; BinaryReader oBinaryReader; byte[] oImgByteArray; oImg = new FileStream(sFilePath,FileMode.Open,FileAccess.Read); oBinaryReader = new BinaryReader(oImg); oImgByteArray = oBinaryReader.ReadBytes((int)oImg.Length); oBinaryReader.Close(); oImg.Close(); this is the code iam using.when i use this its taking so much of time to get uploaded. so what i thought of doing is save the file in a specific folder using some postedfile.saveas(not sure of syntax) and when the user wants to view the file they can just download the file. i can do this by googling but what i want is wat abt the existing attachments in the database. so i need a help to do this........ is it possible to create a query to read the files and convert it into original file and save it in the specific folder or do i ve to create a simple interface to create do it manually , pls someone help me what to do ......thanks in advance.waiting for a reply soon Note: any unclear statement in my question kindly reply meWishes n RegardsVenkat.
Dear Experts,Ok, I hate to ask such a seemingly dumb question, but I'vealready spent far too much time on this. More that Iwould care to admit.In Sql server, how do I simply change a character into a number??????In Oracle, it is:select to_number(20.55)from dualTO_NUMBER(20.55)----------------20.55And we are on with our lives.In sql server, using the Northwinds database:SELECTr.regionid,STR(r.regionid,7,2) as a_string,CONVERT(numeric, STR(r.regionid,7,2)) as a_number,cast ( STR(r.regionid) as int ) as cast_to_numberFROM REGION R1 1.00112 2.00223 3.00334 4.0044SELECTr.regionid,STR(r.regionid,7,2) as a_string,CONVERT(numeric, STR(r.regionid,7,2) ) as a_number,cast (STR(r.regionid,7,2) as numeric ) as cast_to_numberFROM REGION R1 1.00112 2.00223 3.00334 4.0044Str converts from number to string in one motion.Isn't there a simple function in Sql Server to convertfrom string to number?What is the secret?Thanks
I have been given some data from a Mainframe (AS400?) which has some fields coded in Packed Decimal. I have been able to load the data into a SQL2005 database table, but I now need to convert the Packed Decimal data in the binary(6) field to the appropriate integer (or float) value.
The field contains values such as the following:-
0x20202020200C
0x202020022025
0x20202020DFFA
I don't know how to interpret these. Has anyone got a function that can do this for me?
I've read several articles online that explain how packed decimal works, but none tell me how to interpret the last of my three examples. Can you help?
I am trying to write a function to convert a hex string to binary. I would like it in a function so I can use it on aggregate data in queries (instead of having to cursor through the data). So, I write my function:
CREATE FUNCTION HexToBinary (@hexstring char(16)) RETURNS binary(8) AS BEGIN declare @b binary(8) ,@sql nvarchar(255)
SET @sql = N'SET @b = 0x' + @hexstring EXEC sp_executesql @sql,N'@b binary(8) out',@b output RETURN @b END
Then, I try to call my function:
SELECT HexToBinary('E0')
...and I get:
Msg 195, Level 15, State 10, Line 1 'HexToBinary' is not a recognized built-in function name.
However, I can get it to work if I use a slightly different syntax:
declare @b binary(8) exec @b = HexToBinary 'E0' select @bAny thoughts as to what might be going on? Obviously, the lower syntax does not help me call this function in queries, which is really my goal.
Hi Folks, After I inserted a row in my Database (row 27) I started getting this error when I try to insert, update or delete the record in the database. I've searched about the error on google and it says that I should have a field that crossed the limit of characters. I have only the autoincrement field, two varchar fields and a text field, neither one of the varchar fields crossed the limit, they arent even close. I found in google that one solution would be turn the field that is having problems in a text field, but the only field that actually can be causing the problem already is of the text type. The exact error I get on VS 2005 when trying to change something in the row 27 is: "No row was updated. The data in row 27 was not committed.Error Source: .Net SqlClient Data Provider.Error Message: String or binary data would be truncated.The statement has been terminated. Corret the errors and retry or press ESC to cancel the change(s)." I need some help guyz, see ya, hugs.
Hi Guys, I'm trying to save the data into 2 table when i click the button. But it pops out this error: String or binary data would be truncated. The statement has been terminated. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: String or binary data would be truncated. The statement has been terminated.Source Error:
Line 116:Line 117: Sqlinsert.Connection.Open()Line 118: Sqlinsert.ExecuteNonQuery()Line 119:Line 120: Sqlinsert.Connection.Close() I Don't know what it means so i paste my codes regarding the button & the redline Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click 'add the data into shopper
Dim strsqlcmd As String
Dim strName, straddress As String Dim strContact, strEmail, strPw As String Dim strIC As String
' go the add item Response.Redirect("NewPrescItem.aspx") why my sqlinsert.excutenonquery will have this error? and what is this error really means? Thanks in advance
I am currently developing a simple program that will upload ms-word documents to a database so users can view them within the department. The program worked prefectly while using sql 2005 with the these fields: FormId intFileName nvarchar(50)FileBytes varbinary(Max) This was done in unit testing, now in system testing there is a difference. A slight downgrade in the database and server software! We are using sql 2000 on a 2000 server and the FileBytes datatype had to change to the following: FormId intFileName nvarchar 50 FileBytes varbinary 8000 There is no 'Max' option in sql 2000 for the datatype varbinary. So when the insert is perform, we get this error message: String or binary data would be truncated.The statement has been terminated. I am guessing it has something to do with FileBytes, because this is the only thing that changed. I just don't know how to solve the problem.P.S.The application is in ASP.NET 2.0
I keep getting the error "String or binary data would be truncated." when I try to insert data into SQL Sever 2005 from an ASP.net page. Having searched thoughout the web, I know this is generally caused because one of the values being inserted is bigger than the size of the field it's going into. But, I have tested this out in many different ways, and this is not what's causing the problem. I have tried doing the insert as a SQL statement and using the "ExecuteNonQuery" command. I have also tried running this through a stored procedure. Neither works. In both situations, I have captured the TSQL statements via SQL Profiler and run them in a query window. In both cases, the statements work just fine in a query window. When I try to do the insert via a stored procedure, all of the statements in the stored procedure show up in SQL Profiler, including the final SELECT statement I have the indicates a successful result. But, the data does not end up in the table and the ASP.net page returns an error. Also, I have run both the Stored Proceure and the SQL insert statement making all of the fields blank as a way of ensuring that no value can be longer than a field's size. But, in both cases I still get the same error. Here's the line I use when doing a regular insert: Dim Command4 = New Data.SqlClient.SqlCommand("INSERT INTO ResourceCenter(UserEmail, UserPassword, Region, FirstName, LastName, Company, JobTitle, Address1, Address2, City, StateProvince, ZipPostalCode, Country, BusinessPhone, WebSiteURL, HowDidYouFind, Industry, WhatTypeOfSolution, CompanySize, NumLogins, LastLogin) VALUES ('" & UserEmail & "', '" & UserPassword & "', '" & Region & "', '" & FirstName & "', '" & LastName & "', '" & Company & "', '" & JobTitle & "', '" & Address1 & "', '" & Address2 & "', '" & City & "', '" & StateProvince & "', '" & ZipPostalCode & "', '" & Country & "', '" & BusinessPhone & "', '" & WebSiteURL & "', '" & HowDidYouFind & "', '" & Industry & "', '', '', 1, " & Now & ")", conn2) Dim NumRowsUpdated2 = Command4.ExecuteNonQuery() Here's my code when I call a stored procedure: Dim dsSignup As New Data.DataSet()Dim Command4 As New Data.SqlClient.SqlDataAdapter("ap_ResourceCenterModify", conn2)Command4.SelectCommand.CommandType = Data.CommandType.StoredProcedureCommand4.SelectCommand.Parameters.Add("@UserID", Data.SqlDbType.Int, 0).Value = 0Command4.SelectCommand.Parameters.Add("@UserEmail", Data.SqlDbType.VarChar, 100).Value = UserEmailCommand4.SelectCommand.Parameters.Add("@UserPassword", Data.SqlDbType.VarChar, 20).Value = UserPasswordCommand4.SelectCommand.Parameters.Add("@Region", Data.SqlDbType.VarChar, 50).Value = RegionCommand4.SelectCommand.Parameters.Add("@FirstName", Data.SqlDbType.VarChar, 100).Value = FirstNameCommand4.SelectCommand.Parameters.Add("@LastName", Data.SqlDbType.VarChar, 100).Value = LastNameCommand4.SelectCommand.Parameters.Add("@Company", Data.SqlDbType.VarChar, 100).Value = CompanyCommand4.SelectCommand.Parameters.Add("@JobTitle", Data.SqlDbType.VarChar, 100).Value = JobTitleCommand4.SelectCommand.Parameters.Add("@Address1", Data.SqlDbType.VarChar, 100).Value = Address1Command4.SelectCommand.Parameters.Add("@Address2", Data.SqlDbType.VarChar, 100).Value = Address2Command4.SelectCommand.Parameters.Add("@City", Data.SqlDbType.VarChar, 100).Value = CityCommand4.SelectCommand.Parameters.Add("@StateProvince", Data.SqlDbType.VarChar, 50).Value = StateProvinceCommand4.SelectCommand.Parameters.Add("@ZipPostalCode", Data.SqlDbType.VarChar, 50).Value = ZipPostalCodeCommand4.SelectCommand.Parameters.Add("@Country", Data.SqlDbType.VarChar, 200).Value = CountryCommand4.SelectCommand.Parameters.Add("@BusinessPhone", Data.SqlDbType.VarChar, 100).Value = BusinessPhoneCommand4.SelectCommand.Parameters.Add("@WebSiteURL", Data.SqlDbType.VarChar, 200).Value = WebSiteURLCommand4.SelectCommand.Parameters.Add("@HowDidYouFind", Data.SqlDbType.VarChar, 100).Value = HowDidYouFindCommand4.SelectCommand.Parameters.Add("@Industry", Data.SqlDbType.VarChar, 100).Value = IndustryCommand4.SelectCommand.Parameters.Add("@WhatTypeOfSolution", Data.SqlDbType.VarChar, 250).Value = WhatTypeOfSolutionCommand4.SelectCommand.Parameters.Add("@CompanySize", Data.SqlDbType.VarChar, 100).Value = CompanySizeCommand4.Fill(dsSignup)Any ideas of what else I can try? Thanks in advance.
Hi,I am getting the following error:System.Data.SqlClient.SqlException: String or binary data would be truncated.The statement has been terminated.Can someone tell me what it means?Thanks,Jon
I am trying to pass a multi lined text field into my SQL Server database. I tried varchar and set the length to 1000 but I still can only pass upto 50 charcters. I also have tried to use other data types like nvarchar, text, and ntext but the text and ntext won't allow me to chenge the length of the field and n varchar will only allow up to 50 charcters no matter how big I make the length. Any help you can give me would be great.
I have a stored proc that inserts binary data into an image field.
I am getting a "String or binary data would be truncated" error when trying to insert large data, but this is *no where* near the maximum size of the image datatype.
Should I be declaring the size of the image parameter in my SQLCommand or something?
TIA, Mark
Code example: The sproc:
ALTER PROCEDURE sproc_Content_Insert ( @ContentName varchar(250), @ContentDesc varchar(500), @ContentType varchar(25), @Content image, @LocationID int ) AS INSERT INTO tbl_Content (ContentName, ContentDesc, ContentType, Content, LocationID) VALUES (@ContentName, @ContentDesc, @ContentType, @Content, @LocationID)
The method:
Public Sub SaveBinaryContent(ByRef ContentName As String, ByRef ContentDesc As String, ByRef ContentType As String, ByRef Content As String, ByRef LocationID As Integer)
'convert the content to a binary stream. Dim ms As New System.IO.MemoryStream Dim bf As New BinaryFormatter bf.Serialize(ms, Content) ms.Position = 0
Dim oConn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString")) Dim cmd As New SqlCommand("sproc_Content_Insert", oConn) With cmd .CommandType = CommandType.StoredProcedure .Parameters.Add("@ContentName", ContentName) .Parameters.Add("@ContentDesc", ContentDesc) .Parameters.Add("@ContentType", ContentType) .Parameters.Add("@Content", ms.ToArray) .Parameters.Add("@LocationID", LocationID) End With oConn.Open() cmd.ExecuteNonQuery() oConn.Close() oConn.Dispose() cmd.Dispose()
Hi all, when ever i try uploadin the file to the database, i am facing this weird probs :( Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: String or binary data would be truncated.Source Error:
Line 124: Line 125:// Update data source Line 126:dbAdapt.Update(dbSet,"tblFile1"); Line 127: Line 128:// Get newFileID i donno what this means :( well the code is like this private void Button1_Click(object sender, System.EventArgs e) { HttpPostedFile myFile = filMyFile.PostedFile; // Get size of uploaded file nFileLen = myFile.ContentLength; nFiletype = myFile.ContentType; // Allocate a buffer for reading of the file myData = new byte[nFileLen]; // Read uploaded file from the Stream myFile.InputStream.Read(myData, 0, nFileLen); strFilename = Path.GetFileName(myFile.FileName); if( myData.Length != 0) { WriteToDB(strFilename,nFiletype,ref myData); } } private int WriteToDB(string strName, string strType, ref byte[] Buffer) { int nFileID = 0; // Create connection SqlConnection dbConn = new SqlConnection("Data Source=D3BKIRAN; Database=ooc;UID=sa;Password=*****"); // Create Adapter SqlDataAdapter dbAdapt = new SqlDataAdapter("SELECT * FROM tblFile1", dbConn);
// Create and initialize CommandBuilder SqlCommandBuilder dbCB = new SqlCommandBuilder(dbAdapt); // Open Connection dbConn.Open();
// New DataSet DataSet dbSet = new DataSet();
// Populate DataSet with data dbAdapt.Fill(dbSet, "tblFile1"); // Get reference to our table DataTable dbTable = dbSet.Tables["tblFile1"]; // Create new row DataRow dbRow = dbTable.NewRow(); // Store data in the row dbRow["FileName"] = strName; dbRow["FileSize"] = Buffer.Length; dbRow["ContentType"] = strType; dbRow["FileData"] = Buffer; // Add row back to table dbTable.Rows.Add(dbRow); // Update data source dbAdapt.Update(dbSet,"tblFile1"); // Get newFileID if( !dbRow.IsNull("FileID") ) nFileID = (int)dbRow["FileID"];
// Close connection dbConn.Close(); // Return FileID return nFileID; }
plz any one give me an soln for the same thanks in advance
Hi, I appreciate your help. I used to run a script to populate a table then send a message notification to a user via email. Lately, I am getting this error message. I have not changed any of the code in the procedure that run. What could be wrong.
Thanks a gain for your help
Ali
Server: Msg 8152, Level 16, State 9, Line 12 String or binary data would be truncated.
Is there anything I can put in a stored procedure so instead of proc fallingover because of "String or binary data would be truncated" the offendingrecords are just truncated?Thanks for any help!
I am trying to take a hexadecimal representation of a binary number and convert to the true binary representation so that I can compare it against a binary field in one of my tables.
After reading the documentation it seems I should be able to do this with the CAST or CONVERT function. However it does not appear to be working correctly.
Can you tell me why this T-SQL code produces the wrong binary value:
I recieved this error messege after trying to do an insert on a memory table. Here's part of the stored proc: I know im getting the error from the Bill_Customer_code, but dont know why! it has varchar 20, and when i run the query on its own, most of the values are about 5 characters. Any ideas?
Code Snippet
USE [RC_STAT] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[PROC_RPT_Breeder_Sales_Distributor] AS BEGIN SET NOCOUNT ON;
Declare @ReportingTbl TABLE ( Source_Id int, Territory_Code varchar(20), Territory_Description varchar (30), Sort_Id int, Column_Text varchar(20), Bill_Customer_Code varchar(20), Customer_Name varchar (50), Subbrand_Key int, SubBrand_Description varchar (30), Period_1 Decimal(18,0), Period_2 Decimal(18,0), Period_3 Decimal(18,0), Period_4 Decimal(18,0), Period_5 Decimal(18,0), Period_6 Decimal(18,0), Period_7 Decimal(18,0), Period_8 Decimal(18,0), Period_9 Decimal(18,0), Period_10 Decimal(18,0), Period_11 Decimal(18,0), Period_12 Decimal(18,0), Period_13 Decimal(18,0), YTD Decimal (18,0), Total_Amount decimal (18,0)) --Distributor Goals INSERT INTO @ReportingTbl (Source_Id, Territory_Code, Territory_Description, Bill_Customer_Code, Customer_Name, Sort_Id, Column_Text, Subbrand_Key, SubBrand_Description, Period_1, Period_2, Period_3, Period_4, Period_5, Period_6, Period_7, Period_8, Period_9, Period_10, Period_11, Period_12, Period_13, YTD, Total_Amount) SELECT 4 AS Source_Id, Tbv_Customer.Breeder_Territory_Code, RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Territory_Name, 4 AS Sort_Id, 'Goal' AS Column_Text, Bill_Customer_Code, Tbv_Customer_1.Customer_Name, 999 AS Sub_Brand_ID, 'Distributor_Goal' AS Sub_Brand_Description, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 1 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_1, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 2 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_2, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 3 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_3, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 4 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_4, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 5 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_5, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 6 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_6, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 7 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_7, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 8 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_8, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 9 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_9, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 10 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_10, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 11 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_11, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 12 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_12, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 13 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_13, 0 AS YTD, SUM(cusSales.Customer_Sales_Summary_Amount) AS Total FROM RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension INNER JOIN Tbv_Customer ON RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Territory_Code = dbo.Tbv_Customer.Breeder_Territory_Code INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Customer_Sales_Summary_Fiscal AS cusSales ON Tbv_Customer.Customer_Code = cusSales.Customer_Code INNER JOIN Tbv_Customer AS Tbv_Customer_1 ON cusSales.Bill_Customer_Code = Tbv_Customer_1.Customer_Code WHERE (cusSales.Bill_Customer_Code NOT IN ('RNPROC', 'RNPROF','11825', '11990', '11971', '12013', '08105')) AND (cusSales.Sub_Brand_Id <> 65) AND (cusSales.Report_Level_Id = 85) AND (cusSales.Consolidated_Sales_Tables_Id = 32) GROUP BY Tbv_Customer.Breeder_Territory_Code, RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Territory_Name, cusSales.Customer_Sales_Summary_Year, cusSales.Bill_Customer_Code, Tbv_Customer_1.Customer_Name
In MS SQL Server 2005, we currently have a stored procedure which creates a table where one of the fields Client ID is declared as a VARCHAR(8).
Then there is an INSERT INTO table statement but it returns the following error:
"String or binary data would be truncated."
We have checked the values to be inserted into the table and none of them exceed the limit.
The Client ID column currently contains values less than or equal to 8 characters in length and null values.
To debug, we have already done the following:
1. Insert the values seperately into the table
e.g. WHERE Client ID = 8 OR Client ID < 8 OR Client ID is null
There were no errors returned and all values were inserted into the table
2. (a) Selected all distinct values to be inserted and pasted it into Excel (b) Then set the ANSI WARNINGS OFF - so it will select all distinct values and truncate the offending Client ID (c) Pasted the result set returned in step (b) into Excel (d) Compared the two columns to look for any differences and there were none!!
3. There are Client IDs like "0", "01", "11" - so thought that it may be something to do with SQL treating it like a binary....but we tried eliminating these values and performing the INSERT and the still get the error.
We are out of ideas and would greatly appreciate your help!!