Hi,
I am trying to generate an XML output form the database. I am
executing
select * from Request_vw for xml auto
The output data is being truncated. is there a limit on the output
result of an XML query?
Thanks
Hi
I have an SP, which queries a table and correspondingly outputs an ntext column. Before executing this SP. I used "save results as " option under Query analyzer tool bar. So that, when ever i execute this SP I can store the value under a file. The query is executing fine , but it is truncating the output of this ntext value.
Does any body knowsfaced this issue?
Iam using sql 2000
Thanks!
Santhosh
Hi,I created the SQL 2005 stored procedure below:CREATE PROCEDURE [dbo].[STP_val_deliverable_path]@s_no smallint,@deliverable_path nvarchar(255) OUTPUTWhen I run in ASP.NET 2005 the stored procedure from server explorer Iget the value 'X:my directory.......'.When I run the procedure from code: Dim var_deliverable_path As String Dim cmm_select As New SqlCommand("STP_val_deliverable_path",connection) cmm_select.CommandType = Data.CommandType.StoredProcedure var_param = New SqlParameter var_param.ParameterName = "deliverable_path" var_param.Direction = Data.ParameterDirection.Output var_param.Value = "C:" cmm_select.Parameters.Add(var_param) cmm_select.Connection.Open() cmm_select.ExecuteNonQuery() cmm_select.Connection.Close() var_deliverable_path =CType(cmm_select.Parameters("deliverable_path").Value, String)The var_deliverable_path has the value of 'X' only, not the wholestring.What could be the problem ?
View 2 Replies View RelatedWhile using Aggregate Transformation to group one column,the rows of output sometimes larger than the rows returned by a T-SQL statement via SSMS.
For example,the output of the Aggregate Transformation may be 960216 ,but the
'Select Count(Orderid) From ... Group By ***' T-SQL Statement returns 96018*.
I'm sure the Group By of the Aggregate Transformation is right!
But ,when I set the "keyscale" property of the transformation,the results match!
In my opinion,the "keyscale" property will jsut affects the performance of the transformaiton,but not the result of the transformation.
Thanks for your advice.
Inside some TSQL programmable object (a SP/or a query in Management Studio)I have a parameter containing the name of a StoreProcedure+The required Argument for these SP. (for example it's between the brackets [])
EX1 :Â @SPToCall : [sp_ChooseTypeOfResult 'Water type']
EX2 :Â @SPToCall : [sp_ChooseTypeOfXMLResult 'TABLE type', 'NODE XML']
EX3 :Â @SPToCall : [sp_GetSomeResult]
I can't change thoses SP, (and i don't have a nice output param to cach, as i would need to change the SP Definition)All these SP 'return' a 'select' of 1 record the same datatype ie: NVARCHAR. Unfortunately there is no output param (it would have been so easy otherwise. So I am working on something like this but I 'can't find anything working
DECLARE @myFinalVarFilledWithCachedOutputÂ
NVARCHAR(MAX);
DECLARE @SPToCall NVARCHAR(MAX) = N'sp_ChooseTypeOfXMLResult
''TABLE type'', ''NODE XML'';'
DECLARE @paramsDefintion = N'@CatchedOutput NVARCHAR(MAX) OUTPUT'
[code]...
Hi
I have Lookup task to determine if source data should be updated to or insert to the customer table. After Lookup task, the Error Output pipeline will redirect to insert new data to the table and the Output pipeline will update customer table. But these two tasks will be processing at the same time which causes stall on the process. Never end.....
The job is similiart to what Slow Changing Dimention does but it won't update the table at the same time.
What can I do to avoid such situation?
Thanks in advance,
JD
I am working on an OLAP modeled database.
I have a Lookup Transformation that matches the natural key of a dimension member and returns the dimension key for that member (surrogate key pipeline stuff).
I am using an OLE DB Command as the Error flow of the Lookup Transformation to insert an "Inferred Member" (new row) into a dimension table if the Lookup fails.
The OLE DB Command calls a stored procedure (dbo.InsertNewDimensionMember) that inserts the new member and returns the key of the new member (using scope_identity) as an output.
What is the syntax in the SQL Command line of the OLE DB Command Transformation to set the output of the stored procedure as an Output Column?
I know that I can 1) add a second Lookup with "Enable memory restriction" on (no caching) in the Success data flow after the OLE DB Command, 2) find the newly inserted member, and 3) Union both Lookup results together, but this is a large dimension table (several million rows) and searching for the newly inserted dimension member seems excessive, especially since I have the ID I want returned as output from the stored procedure that inserted it.
Thanks in advance for any assistance you can provide.
Hi!Server info -Win2K3 Server +SP1 with 1 GB Memory and 1.5 GB Virtual MemorySQL Server 2000 Enterprise Edition + SP3 running on this.Required result -Create a SQL Script that will contain a set of create, update, insert& delete statements (about 17500 lines including blank lines) thatcan be run on different databases seperatelyHow we do this -We have a SP - that creates a temporary table and then calls anotherSP that actually populates the temporary table created by the first SP*Samples of both SPs are below -PROBLEMThe result is directed to a file -However when the query is run it runs through the entire script but'Jumbles' the outputRunning the same scripts on a copy of the database on other machineswork fine and the size of the outfiles is exactly the sameI have increased the page size to 2.5 GB and restarted the server.Running the sp now generated the correct output a few times but gotjumbled as before after a few more users logged in and activity on theserver increased.Another interesting point is that the output is jumbled exactly thesame way each time. It seems the sql executes correctly and writesthe output in chunks only writting the chunks out of sequence - butin the same sequence each time.e.g. of expected resultInsert into Table1Values x, y, z, 1, 2Insert into Table1Values q, s, g, 3, 4Insert into Table1Values c, d, e, 21, 12....Insert into Table2Values ...Insert into Table3Values ................Update RefGenSet Last = 1234Where RefGenRef = 1JUMBLED OUTPUTInsert into Table1Values x, y, z, 1, 2Insert into Table1Values q, s, g, 3, 4Insert into Table1Values c, d, e, 21, 12....Insert into Table2Values ...Insert into Table2Values ...Values ...Update RefGenSet Last = 1234Where RefGenRef = 1Insert into Table3Values ................Insert into Table1Values c, d, e, 21, 12....Insert into Table2----------------------------------------Sample of First Script - STATDATA_RSLT**************************************SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOSET NOCOUNT ONGOCREATE PROCEDURE StatData_rsltASCREATE TABLE #tbl_Script(ScriptText varchar(4000))EXEC TestStatData_intSELECT t.ScriptTextFROM #tbl_Script tGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO*******************************************Sample of CALLED SP - TestStatData_int*******************************************SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOCREATE PROCEDURE TestStatData_intASDECLARE @AttrRef int,@TestID int,@PrtTestRef int,@AttrType tinyint,@EdtblSw tinyint,@NmValRef int,@SrtTypeRef int,@AttrStr varchar(20),@TestStr varchar(20),@PrtTestStr varchar(20),@AttrTypeStr varchar(20),@EdtblStr varchar(20),@NmValStr varchar(20),@SrtTypeStr varchar(20),@TestRef int,@Seq int,@PrtRef int,@Value varchar(255),@TermDate datetime,@AttrID int,@DefSw tinyint,@WantSw tinyint,@TestRefStr varchar(20),@SeqStr varchar(20),@PrtStr varchar(20),@TermDateStr varchar(255),@AttrIDStr varchar(20),@DefStr varchar(20),@WantStr varchar(20),@LanRef int,@LanStr varchar(20),@Code varchar(20),@Desc varchar(255),@MultiCode varchar(20),@MultiDesc varchar(255),@InhSw tinyint,@InhStr varchar(20),@InhFrom int,@InhFromStr varchar(20),@Lan_TestRef int,@ActSw tinyint,@ActSwStr varchar(20)SELECT @Lan_TestRef = dbo.fn_GetTestRef('Lan')INSERT INTO #tbl_ScriptVALUES('')-- Create tablesINSERT INTO #tbl_ScriptVALUES ('CREATE TABLE #tbl_Test (AttrRef int, TestID int , PrtTestRefint, AttrType tinyint, EdtblSw tinyint, NmValRef int, SrtTypeRefint)')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES('CREATE TABLE #tbl_TestAttr(AttrRef int, TestRef int, Seq int,PrtRef int, AttrType tinyint, Value varchar(255), TermDate datetime,AttrID int, DefSw tinyint, WantSw tinyint, ActSw tinyint)')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('CREATE TABLE #tbl_AttrName(AttrRef int, LanRef int, Codevarchar(20), [Desc] varchar(255), MultiCode varchar(20), MultiDescvarchar(255), InhSw tinyint, InhFrom int)')INSERT INTO #tbl_ScriptVALUES ('')-- insert Test valuesDECLARE Test_cursor CURSOR FORSELECT l.AttrRef, l.TestID, l.PrtTestRef, l.AttrType, l.EdtblSw,l.NmValRef, l.SrtTypeRefFROM Test lOPEN Test_cursorFETCH NEXT FROM Test_cursorINTO @AttrRef, @TestID, @PrtTestRef, @AttrType, @EdtblSw, @NmValRef,@SrtTypeRefWHILE @@FETCH_STATUS = 0BEGINSELECT @AttrStr = ISNULL(CAST(@AttrRef as varchar), 'NULL'),@TestStr = ISNULL(CAST(@TestID as varchar), 'NULL'),@PrtTestStr = ISNULL(CAST(@PrtTestRef as varchar), 'NULL'),@AttrTypeStr = ISNULL(CAST(@AttrType as varchar), 'NULL'),@EdtblStr = ISNULL(CAST(@EdtblSw as varchar), 'NULL'),@NmValStr = ISNULL(CAST(@NmValRef as varchar), 'NULL'),@SrtTypeStr = ISNULL(CAST(@SrtTypeRef as varchar), 'NULL')INSERT INTO #tbl_ScriptVALUES ('INSERT INTO #tbl_Test(AttrRef, TestID, PrtTestRef,AttrType,EdtblSw, NmValRef, SrtTypeRef)')INSERT INTO #tbl_ScriptVALUES ('VALUES ( ' + @AttrStr + ', ' + @TestStr + ', ' +@PrtTestStr+ ', ' + @AttrTypeStr + ', ' + @EdtblStr + ', ' + @NmValStr + ', ' +@SrtTypeStr + ')')INSERT INTO #tbl_ScriptVALUES ('')FETCH NEXT FROM Test_cursorINTO @AttrRef, @TestID, @PrtTestRef, @AttrType, @EdtblSw, @NmValRef,@SrtTypeRefENDCLOSE Test_cursorDEALLOCATE Test_cursorDECLARE TestAttr_cursor CURSOR FORSELECT le.AttrRef, le.TestRef, le.Seq, le.PrtRef, le.AttrType,le.Value,le.TermDate, le.AttrID, le.DefSw, le.WantSw, le.ActSwFROM TestAttr leWHERE le.WantSw = 1AND le.ActSw = 1OPEN TestAttr_cursorFETCH NEXT FROM TestAttr_cursorINTO @AttrRef, @TestRef, @Seq, @PrtRef, @AttrType, @Value,@TermDate, @AttrID, @DefSw, @WantSw, @ActSwWHILE @@FETCH_STATUS = 0BEGINSELECT @AttrStr = ISNULL(CAST(@AttrRef as varchar), 'NULL'),@TestRefStr = ISNULL(CAST(@TestRef as varchar), 'NULL'),@SeqStr = ISNULL(CAST(@Seq as varchar), 'NULL'),@PrtStr = ISNULL(CAST(@PrtRef as varchar), 'NULL'),@AttrTypeStr = ISNULL(CAST(@AttrType as varchar), 'NULL'),@Value = ISNULL(@Value, 'NULL'),@TermDateStr = ISNULL(CAST(@TermDate as varchar), 'NULL'),@AttrIDStr = ISNULL(CAST(@AttrID as varchar), 'NULL'),@DefStr = ISNULL(CAST(@DefSw as varchar), 'NULL'),@WantStr = ISNULL(CAST(@WantSw as varchar), 'NULL'),@ActSwStr = ISNULL(CAST(@ActSw as varchar), '1')SELECT @Value = '''' + @Value + ''''WHERE @Value <> 'NULL'INSERT INTO #tbl_ScriptVALUES ('INSERT INTO #tbl_TestAttr(AttrRef, TestRef, Seq, PrtRef,AttrType, Value, TermDate, AttrID, DefSw, WantSw, ActSw)')INSERT INTO #tbl_ScriptVALUES ('VALUES (' + @AttrStr + ', ' + @TestRefStr + ', ' +@SeqStr+ ', ' + @PrtStr + ', ' + @AttrTypeStr + ', ' + @Value + ', ' +@TermDateStr + ', ' + @AttrIDStr + ', ' + @DefStr + ', ' + @WantStr+', '+ @ActSwStr + ')')INSERT INTO #tbl_ScriptVALUES ('')FETCH NEXT FROM TestAttr_cursorINTO @AttrRef, @TestRef, @Seq, @PrtRef, @AttrType, @Value,@TermDate, @AttrID, @DefSw, @WantSw, @ActSwENDCLOSE TestAttr_cursorDEALLOCATE TestAttr_cursorDECLARE AttrName_cursor CURSOR FORSELECT e.AttrRef, e.LanRef, e.Code, e.[Desc], e.MultiCode,e.MultiDesc, e.InhSw, e.InhFromFROM AttrName e, TestAttr leWHERE e.LanRef = 0AND e.AttrRef = le.AttrRefAND le.WantSw = 1AND le.ActSw = 1OPEN AttrName_cursorFETCH NEXT FROM AttrName_cursorINTO @AttrRef, @LanRef, @Code, @Desc, @MultiCode,@MultiDesc, @InhSw, @InhFromWHILE @@FETCH_STATUS = 0BEGINSELECT @AttrStr = ISNULL(CAST(@AttrRef as varchar), 'NULL'),@LanStr = ISNULL(CAST(@LanRef as varchar), 'NULL'),@Code = ISNULL(@Code, 'NULL'),@Desc = ISNULL(@Desc, 'NULL'),@MultiCode = ISNULL(@MultiCode, 'NULL'),@MultiDesc = ISNULL(@MultiDesc, 'NULL'),@InhStr = ISNULL(CAST(@InhSw as varchar), 'NULL'),@InhFromStr = ISNULL(CAST(@InhFrom as varchar), 'NULL')SELECT @Code = REPLACE(@Code, '''',''''''),@Desc = REPLACE(@Desc, '''','''''') ,@MultiCode = REPLACE(@MultiCode, '''','''''') ,@MultiDesc = REPLACE(@MultiDesc, '''','''''')INSERT INTO #tbl_ScriptVALUES ('INSERT INTO #tbl_AttrName(AttrRef, LanRef, Code, [Desc],MultiCode, MultiDesc, InhSw, InhFrom)')INSERT INTO #tbl_ScriptVALUES ('VALUES (' + @AttrStr + ', ' + @LanStr + ', ''' + @Code +''', ''' + @Desc + ''', ''' + @MultiCode + ''', ''' + @MultiDesc +''',' + @InhStr + ', ' + @InhFromStr + ')')INSERT INTO #tbl_ScriptVALUES ('')FETCH NEXT FROM AttrName_cursorINTO @AttrRef, @LanRef, @Code, @Desc, @MultiCode,@MultiDesc, @InhSw, @InhFromENDCLOSE AttrName_cursorDEALLOCATE AttrName_cursor-- Do update TestAttr dataINSERT INTO #tbl_ScriptVALUES ('UPDATE le')INSERT INTO #tbl_ScriptVALUES ('SET')INSERT INTO #tbl_ScriptVALUES (' le.TestRef = t.TestRef,')INSERT INTO #tbl_ScriptVALUES (' le.PrtRef = t.PrtRef,')INSERT INTO #tbl_ScriptVALUES (' le.AttrType = t.AttrType,')INSERT INTO #tbl_ScriptVALUES (' le.Value = t.Value,')INSERT INTO #tbl_ScriptVALUES (' le.TermDate = t.TermDate,')INSERT INTO #tbl_ScriptVALUES (' le.AttrID = t.AttrID,')INSERT INTO #tbl_ScriptVALUES (' le.DefSw = t.DefSw,')INSERT INTO #tbl_ScriptVALUES (' le.WantSw = t.WantSw,')INSERT INTO #tbl_ScriptVALUES (' le.ActSw = t.ActSw')INSERT INTO #tbl_ScriptVALUES ('FROM TestAttr le, #tbl_TestAttr t')INSERT INTO #tbl_ScriptVALUES ('WHERE le.AttrRef = t.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')-- Update AttrNameINSERT INTO #tbl_ScriptVALUES ('UPDATE en')INSERT INTO #tbl_ScriptVALUES ('SET')INSERT INTO #tbl_ScriptVALUES (' en.Code = te.Code,')INSERT INTO #tbl_ScriptVALUES (' en.[Desc] = te.[Desc],')INSERT INTO #tbl_ScriptVALUES (' en.MultiCode = te.MultiCode,')INSERT INTO #tbl_ScriptVALUES (' en.MultiDesc = te.MultiDesc,')INSERT INTO #tbl_ScriptVALUES (' en.InhSw = te.InhSw,')INSERT INTO #tbl_ScriptVALUES (' en.InhFrom = te.InhFrom')INSERT INTO #tbl_ScriptVALUES ('FROM AttrName en, #tbl_AttrName te')INSERT INTO #tbl_ScriptVALUES ('WHERE en.AttrRef = te.AttrRef')INSERT INTO #tbl_ScriptVALUES (' AND en.LanRef = te.LanRef')INSERT INTO #tbl_ScriptVALUES (' AND te.LanRef = 0')-- Do update Test the dataINSERT INTO #tbl_ScriptVALUES ('UPDATE l')INSERT INTO #tbl_ScriptVALUES ('SET')INSERT INTO #tbl_ScriptVALUES (' l.TestID = t.TestID,')INSERT INTO #tbl_ScriptVALUES (' l.PrtTestRef = t.PrtTestRef,')INSERT INTO #tbl_ScriptVALUES (' l.AttrType = t.AttrType,')INSERT INTO #tbl_ScriptVALUES (' l.EdtblSw = t.EdtblSw,')INSERT INTO #tbl_ScriptVALUES (' l.NmValRef = t.NmValRef')INSERT INTO #tbl_ScriptVALUES ('FROM Test l, #tbl_Test t')INSERT INTO #tbl_ScriptVALUES ('WHERE l.AttrRef = t.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')--DELETE where just updatedINSERT INTO #tbl_ScriptVALUES ('DELETE FROM t')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_Test t, Test l')INSERT INTO #tbl_ScriptVALUES ('WHERE t.AttrRef = l.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('DELETE FROM t')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_TestAttr t, TestAttr le')INSERT INTO #tbl_ScriptVALUES ('WHERE t.AttrRef = le.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('DELETE FROM te')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_AttrName te, TestAttr le')INSERT INTO #tbl_ScriptVALUES ('WHERE te.AttrRef = le.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')-- Insert TestAttrINSERT INTO #tbl_ScriptVALUES ('INSERT INTO TestAttr (AttrRef, TestRef, Seq, PrtRef,AttrType,Value, TermDate, AttrID, DefSw, WantSw, ActSw)')INSERT INTO #tbl_ScriptVALUES ('SELECT t.AttrRef, t.TestRef, t.Seq, t.PrtRef, t.AttrType,t.Value, t.TermDate, t.AttrID, t.DefSw, t.WantSw, t.ActSw')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_TestAttr t')INSERT INTO #tbl_ScriptVALUES ('')-- AttrNameINSERT INTO #tbl_ScriptVALUES ('INSERT INTO AttrName(AttrRef, LanRef, Code, [Desc],MultiCode,MultiDesc, InhSw, InhFrom)')INSERT INTO #tbl_ScriptVALUES ('SELECT te.AttrRef, le.AttrRef, te.Code, te.[Desc],te.MultiCode, te.MultiDesc, ')INSERT INTO #tbl_ScriptVALUES (' CASE le.AttrRef ')INSERT INTO #tbl_ScriptVALUES (' WHEN 0 THEN 0')INSERT INTO #tbl_ScriptVALUES (' ELSE 1 END,')INSERT INTO #tbl_ScriptVALUES (' CASE le.AttrRef ')INSERT INTO #tbl_ScriptVALUES (' WHEN 0 THEN NULL')INSERT INTO #tbl_ScriptVALUES (' ELSE 0 END')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_AttrName te, TestAttr le')INSERT INTO #tbl_ScriptVALUES ('WHERE le.TestRef = ' + CAST(@Lan_TestRef as varchar))INSERT INTO #tbl_ScriptVALUES ('')-- Insert new rowsINSERT INTO #tbl_ScriptVALUES ('INSERT INTO Test(AttrRef, TestID, PrtTestRef, AttrType,EdtblSw, NmValRef, SrtTypeRef)')INSERT INTO #tbl_ScriptVALUES ('SELECT t.AttrRef, t.TestID, t.PrtTestRef, t.AttrType,t.EdtblSw, t.NmValRef, t.SrtTypeRef')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_Test t')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('DROP TABLE #tbl_Test')INSERT INTO #tbl_ScriptVALUES ('DROP TABLE #tbl_TestAttr')INSERT INTO #tbl_ScriptVALUES ('DROP TABLE #tbl_AttrName')-- Update RefGenDECLARE @RefGenReflast int,@RefGenRefStr varchar(10)SELECT @RefGenReflast = lastFROM RefGenWHERE RefGenRef = 1SELECT @RefGenRefStr = ISNULL(CAST(@RefGenReflast as varchar), 'NULL')INSERT INTO #tbl_ScriptVALUES('')INSERT INTO #tbl_ScriptVALUES('UPDATE RefGen')INSERT INTO #tbl_ScriptVALUES ('SET Last = ' + @RefGenRefStr)INSERT INTO #tbl_ScriptVALUES ('WHERE RefGenRef = 1')INSERT INTO #tbl_ScriptVALUES ('')GOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO*******************************RegardsGlenn
View 5 Replies View RelatedWhen overriding the PrimeOutput method in a custom component, you get as parameters the outputIDs and the output buffers (of type PipelineBuffer). using the outputIDs you can get IDTSOutput90 outputs.
View 5 Replies View RelatedHi!
In the results of our queries, the datas in the fields are truncated to 255 chars.
Is there a parameter to change to get the entire field? Their format is varchar 8000 and the fields are longer than 255 characters in the db.
Thanks for your help.
Hello,
I am trying to run a query
select logid, count(logid) from temp2
group by logid
order by logid
compute sum(count(logid))
when I get the result the numbers are being truncated
eg instead of 10471066 it shows 104710 so last two digits
get truncated. Any ideas or hints appreciated.
Thanks
HP
Recently we've upgraded SQL 7.0 to SQL 2000. We use a Database Maintenance Plans to backup the databases and transaction logs. Everything seems to be working, but I’m getting an error message in the event viewer that has been described in Microsoft Knowledge Base article 818202 "PRB: A'Database log truncated" Error is logged in the Event Log When you try to Backup the Transaction Log. The article describes the cause of this warning, but does not give any fix or some kind of work around. Does anybody knows what to do or may be had this problem before? Please help. Thanks a lot.
View 6 Replies View Relatedsay if I am doing an simple insert (50 million records) with union all into a table.... when i check it while the query is running I see some records inserted.... however, at the end i have no data... table gets truncated. Is it so because I was out of space (I checked the drives and seems like data drive is running out of space).
Thanks for ur help.
I am relatively new to SQL server. I am tring to send some decimalvalues to the database using a stored procedure with parameters of typeDECIMAL. Every time it inserts the values into the database thedecimals are truncated. I saw on the MSDN library that you have to setthe precision and scale values b/f you run the stored procedure. So Iset the precision to 8 and the scale to 4 and it still didn't help. Cananyone help me?
View 6 Replies View RelatedHello,Using SQL SERVER 2000I have 4 columns with varchar(80) each that I want to concatenate.When I look at the result, it only gives me 256 characters. What am Imissing on my code?Select Cust_Number, Info = convert(varchar(1000),rtrim(line1) +char(13)+rtrim(Line2) + char(13)+ rtrim(line3) + char(13)+rtrim(line4))[color=blue]>From tableOne[/color]GoThank you for your input.Edgar
View 3 Replies View RelatedI'm a newbie to Transact-SQL so I apologize if this is a stupid question. Whenever I try to divide two variables of type int, I get a result where the decimal part is truncated. Here is an example:
ALTER PROCEDURE [dbo].[DIVIDE_TEST]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE
@VAR1 int,
@VAR2 int,
@MYRESULT dec(10,5)
SELECT @VAR1 = 10
SELECT @VAR2 = 3
SELECT @MYRESULT = (@VAR1/@VAR2)
SELECT @MYRESULT
-- @MYRESULT has decimal part truncated, Returned value = 3.00000
END
Any ideas?
I have a problem with a NVARCHAR(MAX) parameter being truncated.
The signature of the UDF is:
[SqlFunction(FillRowMethodName = "SplitFillRow",IsPrecise=true,IsDeterministic=true,
DataAccess = DataAccessKind.None,TableDefinition = "pos INT, token NVARCHAR(10) ")]
public static IEnumerable SplitText (SqlChars input, SqlString strDelimiter)
{...
and deployed as:
CREATE FUNCTION [dbo].[SplitText](@input [nvarchar](max), @strDelimiter [nvarchar](10))
....
But it is still being truncated to 8000 characters. I tried using a sqlstring or using
([SqlFacet(MaxSize = -1,IsFixedLength=false)] on the parameter and a bunch of other things, but it is still truncated to 8000.
I am out of ideas...anyone with a tip?
Thanks
Eric
I have a standard asp.net form and a single database table that I’m trying to pass data into. There are three nvarchar fields of lengths 50, 150, and 2400. Whenever I do an insert or update it truncates all data except for the first character. So if I pass “asdfasdf�, the db field will only receive “a�.
The data going from the form is correct; it gets truncated somewhere after the ExecuteNonQuery() call.
Here is the c# code:
public bool InsertCareer (DataRow row) {
SqlConnection conn = new SqlConnection(connStr);
try {
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = storedProcs[(int)StoredProcedures.Insert];
cmd.Connection = conn;
cmd.Parameters.Add("@pkCareerID", row[0]);
cmd.Parameters.Add("@jobTitle", row[1]);
cmd.Parameters.Add("@postedDate", row[2]);
cmd.Parameters.Add("@submitBy", row[3]);
cmd.Parameters.Add("@department", row[4]);
cmd.Parameters.Add("@description", row[5]);
cmd.Parameters.Add("@intranet", row[6]);
cmd.Parameters.Add("@public", row[7]);
conn.Open();
cmd.ExecuteNonQuery();
return true;
} catch (Exception ex) {
//… stuff
return false;
} finally {
conn.Close();
}
}
This is the sproc code:
CREATE PROCEDURE dbo.zak_CareersIU_sp
@pkCareerID int,
@jobTitle nvarchar,
@postedDate datetime,
@submitBy datetime,
@department nvarchar,
@description nvarchar,
@intranet bit,
@public bit
AS
IF EXISTS (SELECT pkCareerID FROM zak_Careers WHERE pkCareerID=@pkCareerID)
BEGIN
UPDATE zak_Careers SET
jobTitle=@jobTitle,
postedDate=@postedDate,
submitBy=@submitBy,
department=@department,
[description]=@description,
intranet=@intranet,
[public]=@public
WHERE
pkCareerID=@pkCareerID
END
ELSE
BEGIN
INSERT INTO zak_Careers
(jobTitle, postedDate, submitBy, department, [description], intranet, [public])
VALUES
(@jobTitle, @postedDate, @submitBy, @department, @description, @intranet, @public)
END
Please let me know if you have any ideas!
thanks
Hi,
I have hit a brick wall with this. My code is as below
public void fillCustomer()
{
string connectionString = "server='local'; trusted_connection= true; integrated security=sspi; database='Mrbob'";
System.Data.SqlClient.SqlConnection dbConnection = new System.Data.SqlClient.SqlConnection(connectionString);
string queryString = "SELECT * FROM [Customer] WHERE ([CustomerID] = @CustomerID)";
System.Data.SqlClient.SqlCommand dbCommand= new System.Data.SqlClient.SqlCommand();
dbCommand.CommandText = queryString;
dbCommand.Connection = dbConnection;
System.Data.IDataParameter param_CustomerID = new System.Data.SqlClient.SqlParameter();
param_CustomerID.ParameterName ="@CustomerID";
param_CustomerID.Value = customerID;dbCommand.Parameters.Add("@CustomerID", SqlDbType.Int);
dbCommand.Connection.Open();
System.Data.IDataReader dataReader = dbCommand.ExecuteReader();
dbCommand.Connection.Close();
while(dataReader.Read())
{
customerID = dataReader.GetInt32(0);
date = dataReader.GetDateTime(1);
eposCode = dataReader.GetInt32(2);
}
dataReader.Close();
}
The error I am getting is
Prepared statement '(@CustomerID int)SELECT * FROM [Customer] WHERE ([CustomerID] = ' expects parameter @CustomerID, which was not supplied.
As you can see from my queryString the @CustomerID parameter is passed in. It seems as if the string is being truncated at 64 characters long. If I remove the paramter to pass the relevant infomration and pass in a customerID I know exists it works.
I am really stumped on this and would really appreciate any pointers
Hi! When I run a select statement, it would retrieve a product description. In some rows, it is long. Consequently, the product description was truncated. Did anybody have resulotion for this issue?
View 7 Replies View RelatedMy SQL Server 2000 database log was full (2MB) and I am unable to add new column to a table. So that I follow this article to truncate my log file to 1MB. It works. But when I add a new column to a table after that. The log file went back to 2MB in size and I got the "the log file is full" error message again for the operation. BTW, my reovery mode was set to "Simple". I don't understand how the save table operation can recover my change on log size. What should I do to "permanently" truncate my log?
http://www.support.microsoft.com/?id=272318
I did this:
DBCC SHRINKFILE (myDB_Log, 1)
BACKUP LOG myDB WITH TRUNCATE_ONLY
DBCC SHRINKFILE (myDB_Log, 1)
Hi
I have a table Test a execute this below query
truncate table Test
I want to retrive records of Test table .How can i do.
Ranjeet Kumar Singh
My table has got 67K records but the tool is showing just 100 records other records are getting truncated. Now what i have to do in SQL statement to view the other records starting from 100 to 1000?
View 2 Replies View Related"String or binary data would be truncated"Can MS SQL automatically do the truncation?--John MexIT: http://johnbokma.com/mexit/personal page: http://johnbokma.com/Experienced programmer available: http://castleamber.com/Happy Customers: http://castleamber.com/testimonials.html
View 6 Replies View RelatedThe transaction log in a database in our SQLSERVER-2000 server hasgrown to 16GB. I cannot shrink the transaction log manually because itsays that the entire 16GB log size is not free. This is strangebecause we backup the transaction log every hour, and that should havetruncated the transaction log, and should have limited the size of thetransaction log; somehow, the entire transaction log is still markedas being used.I was under the impressionI believe that must have something to do with the fact that thedatabase is a part of our nightly replication. The reason is that whenI tried the following commands, I got that error message:checkpointdump transaction isprod with no_logThe log was not truncated because records at the beginningof the log are pending replication. Ensure the Log ReaderAgent is running or use sp_repldone to mark transactionsas distributed.What does this mean? How can I get away from this mess?Thanks.Jay Chan
View 5 Replies View RelatedI am trying to send a large string to my table where I have typed the field as "text". The data is truncated ( I am only sending about 5kb and it is being truncated to about 4kb). I am using SQL 2000.
My web page is classic.asp and I am sending a parameterized query to my stored procedure.
This is the relevelant code for the parameter I'm sending:
objCmd.Parameters.Append = objCmd.CreateParameter("@PostBody",adLongVarChar,adParamInput,20000,PostBody)
This is the typing in my stored proc: @PostBody text,
I can either INSERT or UPDATE using Query Analyzer and the data is NOT truncated.
Wondering if it was my parameterized query that was causing me trouble, I rewrote the code to send the SQL upfront (skipping the stored proc), and the calling the stored proc from my .asp page passing in the parameters in a string. All three ways are truncating the data.
I have isolated that the truncation is happening on the way in, not on the way out.
Because I can successfully insert/update with QA, I'm wondering if there is some IIS issue that is causing the truncation. But I don't manage our IIS server so I really don't know much about that end of things.
I have been researching this issue for two days with no luck. Any ideas will be greatly appreciated!
Sometime when I€™m using the Data Flow designer I create a task that displays a red circle with a white €œx€? in it. Usually, but not always, the tooltip appears explaining the problem. Sometimes when the tooltip appears I can€™t read the message fully because it is truncated. What can I do in that case?
Barkingdog
I have an Oracle table which contains a LONG column and I need to migrate the data from this table using SSIS to a table in SQLServer. The column in SQLServer is defined as nvarchar(MAX). This works until I changed the Oracle table to a view. The columns in the view are defined exactly the same as the source table except that it uses a database link to point to the table in a different schema. When executing the data flow task using the view, it gives me the following error message -
SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "ORA-01406: fetched column value was truncated ".
The error happens only on views with LONG column. Views with only varchar or numeric data types work fine.
Why does it work with a table but not a view which contains a LONG column?
Any suggestion is greatly appreciated.
Hello all,
I have a COM object (written in VB6) that uses the Microsoft Remote Data Object 2.0 (MSRDO20.dll) to perform non-exotic SQL statements (i.e. SELECTs, INSERTs, UPDATEs and DELETE).
I am using SQL Server 2000 with SP4.
Some of our INSERT statements are being truncated within SQL2K...
We have turned on the SQL Profiler to verify this. Using our own logging mechanism, we log the INSERT SQL being passed to SQL2K immediately before executing the MS SQL statement and our log shows that the INSERT statement is indeed correct. However, we get a SQL error back due to malformed SQL. The SQL Profiler shows that the last 10 bytes have been truncated from the INSERT SQL within SQL2K.
The INSERT statement that we are passing is in the following form...
INSERT (column names...) VALUES (...)
There are embedded CRs (0x0D) in the INSERT.
The length of the INSERT being passed to SQL2K is 2243 bytes.
The length of the INSERT from the Profiler is 2233 bytes (the last 10 bytes were truncated).
Here's a brief glimpse of our VB6 code...
Dim Cn as rdoConnection
Dim sSql as String
Set Cn = New rdoConnection
Cn.Connect = "DSN=FrontOff;UID=Front;PWD=Front;DATABASE=FrontOff;"
Cn.CursorDriver = iCursorDriver
Cn.LoginTimeout = 5
Cn.QueryTimeout = 5
Cn.EstablishConnection rdDriverNoPrompt
sSql = "INSERT INTO PLU €¦"
Cn.QueryTimeout = 300
// Log sSQL to our logging mechanism...
Cn.Execute sSql, rdExecDirect
So, is there a maximum length for a SQL statement in SQL2K???
Any help would be appreciated.
TIA!!!
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
strName = txtName.Text
straddress = txtAdd.Text
strEmail = txtEmail.Text
strContact = txtboxContact.Text
strIC = txtIC.Text
strsqlcmd = "Insert Into Shopper (Name, Address, Contact, Email, IC) Values (@Name, @Address, @Contact, @Email, @IC)"
SqlCommand1.CommandText = strsqlcmd
With SqlCommand1.Parameters
.Add("@Name", strName)
.Add("@Address", straddress)
.Add("@Contact", strContact)
.Add("@Email", strEmail)
.Add("@IC", strIC)
End With
SqlCnt.Open()
SqlCommand1.ExecuteNonQuery()
SqlCnt.Close()
'add the data into the presc
strsqlcmd = "Insert Into Prescription (Name, Address, Contact, Email, IC) Values (@Name, @Address, @Contact, @Email, @IC)"
Sqlinsert.CommandText = strsqlcmd
With Sqlinsert.Parameters
.Add("@Name", strName)
.Add("@Address", straddress)
.Add("@Contact", strContact)
.Add("@Email", strEmail)
.Add("@IC", strIC)
End With
Sqlinsert.Connection.Open()
Sqlinsert.ExecuteNonQuery()
Sqlinsert.Connection.Close()
' 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
Thanks,
xyz789