Only Enable The String Truncation Prevention Of ANSI_WARNINGS
Jul 20, 2005
I'm working with some long standing VB/SQL Server applications and for
the second time we've suffered from having the parameters to a stored
procedure call get silently truncated now that the data field has got
much larger than when the code was developed all those years ago. This
is always very hard to debug and I'd really like SQL Server to throw
an error when this happens.
I don't feel confident enablying the full ANSI_WARNINGS as it is
likely to affect lots of functionality in the database in
unanticipated ways.
What I'd like to be able to do is enable only the ANSI check for the
string data getting truncated but haven't been able to find a way to
do this. Is it possible?
There is a MS Press book that I read which used the following code to prevent SQL Injection attempts from succeeding within a SProc. However, I have found that this example is too inclusive. For example, this will catch the value "Spanish Moss", when this clearly not an injection attempt. Why is that?
IF UPPER(@Query) LIKE UPPER(N'%0x%') OR UPPER(@Query) LIKE UPPER(N'%;%') OR UPPER(@Query) LIKE UPPER(N'%''%') OR UPPER(@Query) LIKE UPPER(N'%--%') OR UPPER(@Query) LIKE UPPER(N'%/*%*/%') OR UPPER(@Query) LIKE UPPER(N'%EXEC%') OR UPPER(@Query) LIKE UPPER(N'%xp_%') OR UPPER(@Query) LIKE UPPER(N'%sp_%') OR UPPER(@Query) LIKE UPPER(N'%SELECT%') OR UPPER(@Query) LIKE UPPER(N'%INSERT%') OR UPPER(@Query) LIKE UPPER(N'%UPDATE%') OR UPPER(@Query) LIKE UPPER(N'%DELETE%') OR UPPER(@Query) LIKE UPPER(N'%TRUNCATE%') OR UPPER(@Query) LIKE UPPER(N'%CREATE%') OR UPPER(@Query) LIKE UPPER(N'%ALTER%') OR UPPER(@Query) LIKE UPPER(N'%DROP%') BEGIN RAISERROR(N'Possible SQL injection attempt.', 16, 1); RETURN; END ELSE BEGIN PRINT N'All is well!'; RETURN; END
If I remove "Spanish" from the query, or the check for "sp_" from the IF statement, this runs fine. So I know the line where this is caught, but it doesn't make sense to me. I can also duplicate this using the work "experienced" which gets caught by the "xp_" check.
I have a data file that has numeric data that looks like:
1.123456
And this column is defined as a DT_NUMERIC(18.6) in the flat file conn mgr.
As an experiment, I changed the destination column to a NUMERIC(18,0) - hoping that this would throw a truncation error at the flat file task level (where I have Truncation on all columns set to "fail component").
Not a peep. It loaded the data into the table, chopping off the 6 digits after the decimal point.
You would THINK that this would cause an error, but no. Why is this? The flat file task complains about all kinds of things, but this is such a gross error, you would think it would catch it!
A data reader is using a connection manager to connect to an ODBC System DSN . A query in the SqlCommand property is provided. Data is being truncated in the only string column . The data type in data reader output-->external columns shows as Unicode string [DT_WSTR] Length 7.
The truncated output in a text file is the first 3 characters from left to right . Changing the column order has no effect.
A linked server was created in SQL Server Management Studio to test the ODBC System DSN using the following:
Data returned using "OPENQUERY" does not truncate the string column indicating that the ODBC Driver returns data as expected with sql 2005, but not with the Data Reader.
DECLARE @a varchar(100) SET @a = @FailedRegionServerName + '.Ithalat.dbo.Product'
DECLARE @s varchar(100) SET @s = ' SELECT * FROM ' + @a EXEC ( @s )
When I execute it I get the error:
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.
Then I put SET ANSI_WARNINGS ON SET ANSI_NULLS ON lines into the procedure. Also checked "Ansi Nulls" and "Ansi Warnings" in the properties of SQL Server. It didn't work
Then I tried:
DECLARE @s varchar(300) SET @s = 'SET ANSI_WARNINGS ON; SET ANSI_NULLS ON; SELECT * FROM ' + @a EXEC ( @s )
I still got the error.
WHAT SHOULD I DO? HOW CAN I GET A TABLE CONTENT FROM A LINKED SERVER? Any will be appreciated, thanks a lot...
Ok, here's my dilemma. We're running SQL Server 2000 with the defaultdb setting for the ANSI_WARNINGS option set to off. However, we stillget "Warning: Null value is eliminated by an aggregate or other SEToperation" messages. To eliminate getting the message, we use the SETANSI_WARNINGS OFF in our stored procs. Using that SET command forces arecompilation of the SP and is causing locks/blocking. Anybody elserun into this problem and if so, how have you handled it.Thanks,-Nate
I have an application running against a SQLServer 2005 Express. For some limitations, I had to access from the same application to another database, but I cannot change to another server.
So I have 2 created a second instances, where the first one refers the second one and I created synonyms in the first one to access to all the objects in the second one, to emulate a database in the first instances, but running on the second one. The final idea is to move to another server, but for the testing I use another instance.
But when I try to access to the aplication database, I hav the following error: Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.
I searched solutions for this issue, but I only found to add SET ANSI_NULLS ON and SET ANSI_WARNINGS ON to my connection, before the queries, but I can't, because I cannot change the application.
I have a SQL200 stored proc that gives me the error "Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query." when I try to execute it through the query analyzer.
I was able to create the stored proc fine but when I try to execute it through the query analyzer it gives me the above error. I do have Link Server select inside the stored proc. I have to turn of warnings inside the stored proc in order for it to not crash my vb6 recordset by putting in the SET ANSI_WARNINGS OFF SET NOCOUNT OFF SET ANSI_NULLS OFF or else my vb6 recordset crashes.
When I created the sproc, I did what every one was telling me to do in the forums by putting in the
SET ANSI_WARNINGS ON Go SET NOCOUNT ON GO SET ANSI_NULLS ON GO
CREATE Procedure usp_SprocName
AS SET ANSI_WARNINGS OFF SET NOCOUNT OFF SET ANSI_NULLS OFF
When I perform a query on a linked Oracle server in the Query analyser Ihave noprboblem' to perform this query.However, when I create this query in a stored procedure I get a compilationerrorwhen saving this procedure. (Not when compiling; it has no errors)Server: Msg 7405, Level 16, State 1, Line 1Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS optionsto be set for the connection. This ensures consistent query semantics.Enable these options and then reissue your query.When I create a dynamic SQL statement then I can save this stored procedurewhen I run the stored procedure this same error happens.What do I have to do.Arno de Jong, The Netherlands
If you are running in Full Recovery Mode and do a full backup every night but never do a backup of the log during the day does the log file ever truncate? From what I read this should be in Simple Recovery Mode but I'm wondering what happens in the case that I mention in the first sentence. Thanks.
Hello Everyone and thanks for your help in advance. I am working on importing a flat text file into SQL Server 2005 and am having problems. The flat file is a CSV text file with " being used as a text qualifier. Each line is broken by a CrLf combination. When I try importing this file into a SQL Server 2000 table using the same datatypes and sizes for each column, it works perfectly fine with the data importing as expected. However, in SQL Server 2005, again using the identical column datatypes and sizes, the import fails giving me warnings such as: * Warning 0x802092a7: Data Flow Task: Truncation may occur due to inserting data from data flow column "Column 0" with a length of 50 to database column "MLS_ID" with a length of 10. (SQL Server Import and Export Wizard) Virtually every columns gives this type of warning, yet I don't understand why since the columns are all variable in length (every message says a column length of 50) and all are delimited rather than fixed size. Then later in the import, errors occur something like: * Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "Column 15" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.". (SQL Server Import and Export Wizard) * Error 0xc020902a: Data Flow Task: The "output column "Column 15" (70)" failed because truncation occurred, and the truncation row disposition on "output column "Column 15" (70)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component. (SQL Server Import and Export Wizard) I haven't got a clue as to why this is happening. For the record, on the flat file source screen, I have ensured that delimited has been selected rather than fixed width. Any help on this issue owuld be greatly appreciated. Thanks.
Could someone please let me know what are the exact steps to follow to truncate the transaction log files? As these log files grow very fast and there seems to be no space in the drive.
Currently am using the below steps to truncate the log file: Step1:Use the below syntax: backup log <database name> with no_log Step2:shrink the log file. Right click the correct database and choose shrink file ->chosse the log -> ok
I would be grateful if someone can give me a proper solution.
Hello All,I am attempting a bulk load of fixed position flat file data via bcpand I have noticed that I get a Right Truncation error when trying toload a row where the last column value is NULL.For example:Flat file row:0000016MFMT file:7.031 SQLCHAR 0 7 "" 1 RECORD_KEY2 SQLCHAR 0 1 "" 2 SEX3 SQLCHAR 0 1 " " 3 HEIGHTIn this row, the height info is null and I get a right truncationerror. The row below, with height info goes in fine:Flat file row:0000016M510Let me know what I am doing wrong!Thanks in advance
How is it possible to avoid truncation errors in MS SQL? For example,if I run the followingdeclare @a as decimal(38,8)declare @b as decimal(38,8)declare @c as decimal(38,8)set @a = 30.0set @b = 350.0set @c = @a/@bselect @cset @c = @c*@bselect @cI get 29.99990000 instead of 30.0. Is there a way around this?ThanksBruno
Hi,I'm trying to upload a large number of log entries currently stored astext files into a database table using bcp. For a few rows I get a"right truncation" error and the offending rows are not uploaded to thetable.I don't want to increase the size of the table varchar fields becauseit's only about a dozen out of almost million rows that have thisproblem ... I want to provide an override - i.e. if a row will resultin truncated data, truncate but still bulk copy the offending row. Isthat possible?I couldn't find such an option in the documentation.Any help is greatly appreciated.Thanks,Mudassir Latif
Hello,I am attempting to write a stored procedure that builds and executes adynamic SQL statement which can be up to 8000 characters long.Therefore, I have declared a variable of type varchar(8000) which,according to the documentation, is the maximum acceptable length ofsuch a variable. Unfortunately, however, SQL Server seems allowvarchars to only be half this size: the resulting string keepingsgetting truncated to 4000 characters as reported by the len function.Is there setting somewhere that would fix this behavior or somework-around that I can employ that would allow me to execute a dynamicsql statement that is longer than 4000 characters?(note: I am not using the sp_executesql proc as it maxes out at 4000; Iam simply calling EXEC which, according to the docs, should be fine)Thank You.
We use SQL Server 2005 x64 Enterprise and I have created a SSIS routine to replace a legacy DTS routine that reads from a Data Reader Source and writes to a SQL Server 2005 database. The field I am receiving the truncation error on is "Description" and it is set as nvarchar(50), which it always has been, and the old DTS routine works fine on it. I checked the contents of description and the maximum number of characters in any row is 28. I have tried changing it to nvarchar(max), nvarchar(4000) and ntext but it still fails with a truncation error. Any leads on how I may solve this issue?
I am having a strange problem that I have been looking at for a day now, and my head is straing to hurt as I have banged my head on the desk so many times. I have written an extensible set of classes that allow me to build SSIS packages dynamically via a web front end. I am finding this code is working OK, but I have this silly bug.
The code is trying to generate an SSIS package that does somethign very simple, and transfer data from a 10 coumn table with a mix of data types, move through another component that adds a couple of extra columns on basic on some variables, then map it onto a OLD DB destination. This code works fine, until I start using strings of various lengths.
When the package runs, it fails validation with errors saying that truncation may occur as I am trying to put a 100 character string, into a 50 character string. The error is logical as you wouldn';t want to do that, but this is not what I am doing. I am actually transfering data from a 50 character string into a 100 character string. When I try it with a table where the strings are the same length at both ends, or no strings are involved, everything works fine, and the data goes from the source to the destination.
I must be setting something slightly wrong which only triggers this problem when the sizes don't match, but the data flow direction is fine, and the data types match. I have included the code from the piece of code that 'writes' the output part of the package. If anyone has any idea what might be going wrong, I would be forever in their debt!
IDTSInput90 input = _Destination.InputCollection[0]; IDTSVirtualInput90 vInput = input.GetVirtualInput(); int iIndex = 0; foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection) { // Call the SetUsageType method of the destination // to add each available virtual input column as an input column. _InstanceOfDestination.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READWRITE); } IDTSExternalMetadataColumn90 exInputColumn; foreach (IDTSInputColumn90 inColumn in _Destination.InputCollection[0].InputColumnCollection) { // create the MAP // What we need to do here, is say what source column, goes to what destination column. // we read by index, and we need to map the specifics, we could control just 3 of 20 columns // to whatever column we wanted in the destination...... // We know the name of the inColumn - It is called inColumn.Name, we need to find the column // we want to map with by finding its name. if (inColumn.Name == "BatchID" || inColumn.Name == "ValidationStatus") { exInputColumn = _Destination.InputCollection[0].ExternalMetadataColumnCollection[inColumn.Name]; // map it _InstanceOfDestination.MapInputColumn(_Destination.InputCollection[0].ID, inColumn.ID, exInputColumn.ID); } else if (Mapping.Map.ContainsKey(inColumn.Name)) { exInputColumn = _Destination.InputCollection[0].ExternalMetadataColumnCollection[Mapping.Map[inColumn.Name]]; // inColumn.Name
// map it _InstanceOfDestination.MapInputColumn(_Destination.InputCollection[0].ID, inColumn.ID, exInputColumn.ID); } } _InstanceOfDestination.ReleaseConnections(); }
I have always assumed that when you backup a SQL Server database the transaction log is automatically truncated so that there is no need to explictly truncate it. It makes sense to me, you would not normally need logs from before the most recent backup. BOL, with all its talk about check points etc, seems to hint at this but I can't find an explicit statement to this effect.
Hello Guys I am using XML files and dumping data to sql server 2005 , i have field called as rate which is having money as datatype and i am getting following error
LoadDataXML to XML Source -- LoadDataXML [907]: The value was too large to fit in the output column "RATE" (95245).
please help me out with the solution of this ...the data which is coming from xml file is unsigned itneger single bit and my database is having money .so should i use a conversion task in between if any body can give idea about this that would be great , if you want more information tell me ... thanks krish
I'm having a problem with one of my packages due to a truncation warning that I can't get rid of. It's not the end of the world, because the package still works. It's just extremely frustrating.
The problem arises in a derived column item in a data flow task. There is a postcode field in the data flow which has space for 20 characters. I create a derived column from this which simply removes any spaces:
Derived Column Name: Postcode
Derived Column: Replace 'Postcode'
Expression: REPLACE(" ",Postcode,"")
Data Type: string [DT_STR]
Length: 20
Code Page: 1252 (ANSI - Latin I)
However when I use this expression, or anything else which uses the replace function, I end up with the warning message:
Warning 1 Validation warning. Create Staging Tables: Derived Column [20555]: The result string for expression "REPLACE(" ",Postcode,"")" may be truncated if it exceeds the maximum length of 4000 characters. The expression could have a result value that exceeds the maximum size of a DT_WSTR.
I have tried everything I can think of to get rid of the warning. Is there some way I can use the replace function, but not have the system convinced that I'm about to go over the maximum size limit?
I am trying to write a SSIS package to move data from an access database table to a SQL db table. I have a field which has data that too long for NVARCHAR(255), so, I end upin this error: "A truncation error occurred on the specified object of the specified component"
i define a clumn like this: clumn name: Body data type: ntext then i run a store procedure which is defined as : create insert_artcle as ( @body ntext ) insert into article{body} values{@body} but the data get into the database is truncated to 8 charactors what is the problem? Is it has something to do with the ntext size 16?
Scenario: I have a database in SQL Server 2000 with 90gb of data. It is growing every day with a some millions records. In order to get superior performance I rebuild the indexes every night. The job takes about 1.5 hours, and sometimes causes to the log file to grow to 30-40 gigs. I do a backup of the log files every 20 minutes, and this causes an log truncation to happen automatically (according to the MS documention). Problem: The log files makes the disk goes full. I cannot insert new disks at this point. Question: When rebuilding the indexes takes 1.5 hours, and I do a log backup (which also does a truncation) every 20 minutes, will the log backup ever get to do something with the transaction log while its still working on a transaction (doing the rebuild of index)? Observation: It seems like the 20 minute log backup is of no help because I see the log files grows so big.
I had to convert "2005-05-17 16:35:49.000" date to string usingCAST(a.ps_ActImplDate AS varchar(20)), 'Not Assigned'), since I had to assign "not assigned" to all null datesProblem here is when I am displaying date in string format it is only displaying like "May 17 2005 4:35PM" missing seconds part. I need everything because I need to query using same date on other table. How to convert datetime to string without any truncationFollowing a snapshot of how it each datetime looks before and after conversion2005-05-17 16:35:49.000 May 17 2005 4:35PM2005-05-17 16:27:03.000 May 17 2005 4:27PM2005-05-17 16:27:03.000 May 17 2005 4:27PM2005-05-17 16:35:48.000 May 17 2005 4:35PM
I have a problem while importing data from Excel to SQL Server.The leading zeros in data get truncated.Even if I try and change the excel data column as 'Text' and copy paste the data back into the Text column, the problem persists.Does any one have any thoughts about this problem?
If we choose "truncate log on checkpoint", then we cannot backup the transaction. May someone tell me what's the difference between them and the advantage over the the other.
