I have an overloaded method in my data access layer that I use to load the parameters into a command object. One of these overloaded methods is as follows:
My app uses .net 1.1 and MS SQL 2005 as backend. How to add a SqlParamter that is varchar(MAX)?SqlParameter myParameter = new SqlParameter("@Description",SqlDbType.VarChar,whatToPutHere?,ParameterDirection.Input, true,0,0,"Whatever",DataRowVersion.Current,"Whatever");
hi all, I'm after a way to produce a single-lined output of a char/varchar string replicated over 8000 times.
basically I've been given a task to create a stored procedure which can accept any integer to replicate a string X times.
From what I've read the replicate() fn will covert to varchar of max 8000 bytes.
I've thought about creating a UDF to accept the varchar and int and run a loop to keep outputting but 'print' will pass an endline to the buffer which is no good for my loop.
I receive the error " [OLE DB Destination [1907]] Warning: Truncation may occur due to inserting data from data flow column "GENDER" with a length of 255 to database column "GENDER" with a length of 2. " on the source flow.
I have done some googling and came up with this post: http://torontosql.dotnetnuke-portal.com/Default.aspx?tabid=32233 which I thought may help, but it does not.
The query against the access datasource features the column: iif([sex]=1, 'm', 'f'). I tried using left(..., 2) as well, but SSIS is determind to treat the field as 255 characters for some reason.
I don't even particualrly care that the field is 255 chars and the sources is only two, I just want the data in! I have other fields coming up with similar error.
Can someone please advise?
PS, what is th significance of the "External Columns" Vs "Output Columns" on the Input and Output Properties tab in Advanced Editor?
I am really struggling with SSIS, it is not as intuitive as DTS.
How do I dim SqlDbType in my code?Dim conn As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("TrainUserConnectionString").ConnectionString) Dim cmd As New Data.SqlClient.SqlCommandWith cmd .Connection = conn .CommandType = Data.CommandType.StoredProcedure .CommandText = "UpdateTopicscmd.Parameters.Add("@classificationID", SqlDBType.Int)cmd.Parameters.Add("@TitleID", SqlDBType.Int) conn.Open() For Each item As ListItem In CheckBoxList1.Items If item.Selected Thencmd.Parameters("@classificationID").Value = item.Valuecmd.Parameters("@TitleID").Value = DropDownList1.SelectedValue cmd.ExecuteNonQuery() End If Next conn.Close() End WithEnd Sub End Class
Hello,Im calling a stored proc that has a numeric value as a varible.In the asp im using the code:myPrivate.Parameters.Add("@memberID", SqlDbType.Decimal, 18).Value = Decimal.Parse(uxToText.Text);I'm using decimal as there is no numeric sqldb type, is this correct? Also is the decimal parse correct as Im getting a 'Input string was not in a correct format' error.Thanks
Hi, I am creating a parameter of type text, and using either of the 3 statements below, to specify the size of the text. Unfortunately none of the statements put all the text into the database. The receiving column is Text(16) and Text in Row is turned off. I really would appreciate any help with this. Many thanks, Polly Anna
sqlParam = New SqlClient.SqlParameter("@varResponse", SqlDbType.Text) ', 2147483647, ParameterDirection.Input) or sqlParam = New SqlClient.SqlParameter("@varResponse", SqlDbType.Text) or sqlParam = New SqlClient.SqlParameter("@varResponse", SqlDbType.VarChar, 4000) then sqlParam.Value = Me.ResponseText
I have a field in my SQL Server 2005 database of type numeric(18,3)In code, I treat the value as decimalWhen creating my command parameters, this is how I'm declaring them:prm.SqlDbType = SqlDbType.Decimal;prm.Precision = (byte)int.Parse("18");prm.Size = int.Parse("0");prm.Scale = (byte)int.Parse("3");Inserting a number like 5.687 is rounding to 6.000 anyone know why it is doing that?
i have string variable as, String str="Int"; now, while assigning sql parameters, i want param.SqlDBType=SqlDBType.Int; but, value of Int is dynamic. it may be string or double so, i want it to be as, param.SqlDBType=(SqlDBType)str; but its not acceptable(its invalid cast). in any way can i do it and how? regards---
I am trying to insert a large amount of data from a web form I created into a "text" field in my DB. Problem is its oly entering the 1st 16 characters. I can see why but I dont understand what I need to do to avoid this shortcomming. Ive searched the forums here and in google groups to no avail :( <code> sqlCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Content", System.Data.SqlDbType.Text, 16, "Content")).Value = ftbContent.Text </code> How do I need to restructure the above line? Any help would be appreciated. TIA,Stue
Dim oParameter As New System.Data.SqlServerCe.SqlCeParameter("@pMyParameter", SqlDbType.Binary, 3000)
If you set a watch on this object, the size is set back to 510. I have tried resetting the size back to 3000 after construction using oParameter.Size, but it doesn't change from 510. If the command is executed using ExecNonQuery, this causes the bytes to get cut off at 510 bytes and returns the error: Byte array truncation to a length of 510.
Can I insert data into SQL Server 2005 Mobile Edition, into a field of data type binary(3000) using .NET CF 2.0 via SqlServerCe objects?
Hi, I'm specify the size of the interger dataypes in my code behind files; e.g. commad.Parameters.Add("@SomeID", SqlDbType.Int, 4) I know it may seem silly, but I have to ask: Will specifying ths size (i.e. 4) eventually stop the auto-icnrement of the underlying primary key SomeID in the SQL Server 2005 database? What restriction, if any, does this place if the next SomeID value is 10000, which is 5 digits? or 999999, which is 6 digits? Thank you
I have looked far and wide and have not found anything that works to allow me to resolve this issue.
I am moving data from DB2 using the MS OLEDB Provider for DB2. The OLEDB source sees the column of data as DT_TEXT. I setup a destination to SQL Server 2005 and everything looks good until I try and run the package.
I get the error: [OLE DB Source [277]] Error: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft DB2 OLE DB Provider" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
[OLE DB Source [277]] Error: Failed to retrieve long data for column "LIST_DATA_RCVD".
[OLE DB Source [277]] Error: There was an error with output column "LIST_DATA_RCVD" (324) on output "OLE DB Source Output" (287). The column status returned was: "DBSTATUS_UNAVAILABLE".
[OLE DB Source [277]] Error: The "output column "LIST_DATA_RCVD" (324)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "LIST_DATA_RCVD" (324)" specifies failure on error. An error occurred on the specified object of the specified component.
[DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (277) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Any suggestions on how I can get the large string data in the varchar column in DB2 into the varchar(max) column in SQL Server 2005?
I am trying to create a store procedure inside of SQL Management Studio console and I kept getting errors. Here's my store procedure.
Code Block CREATE PROCEDURE [dbo].[sqlOutlookSearch] -- Add the parameters for the stored procedure here @OLIssueID int = NULL, @searchString varchar(1000) = NULL AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here IF @OLIssueID <> 11111 SELECT * FROM [OLissue], [Outlook] WHERE [OLissue].[issueID] = @OLIssueID AND [OLissue].[issueID] = [Outlook].[issueID] AND [Outlook].[contents] LIKE + ''%'' + @searchString + ''%'' ELSE SELECT * FROM [Outlook] WHERE [Outlook].[contents] LIKE + ''%'' + @searchString + ''%'' END
And the error I kept getting is:
Msg 402, Level 16, State 1, Procedure sqlOutlookSearch, Line 18
The data types varchar and varchar are incompatible in the modulo operator.
Msg 402, Level 16, State 1, Procedure sqlOutlookSearch, Line 21
The data types varchar and varchar are incompatible in the modulo operator.
For the life of me I cannot figure out why SSIS will not convert varchar data. instead of using the table to table method, I wrote a SQL query so that I could transform the datatype ntext to varchar 512 understanding that natively MS is going towards all Unicode applications.
The source fields from Access are int, int, int and varchar(512). The same is true of the destination within SQL Server 2005. the field 'Answer' is the varchar field in question....
I get the following error
Validating (Error)
Messages
Error 0xc02020f6: Data Flow Task: Column "Answer" cannot convert between unicode and non-unicode string data types. (SQL Server Import and Export Wizard)
Error 0xc004706b: Data Flow Task: "component "Destination - Query" (28)" failed validation and returned validation status "VS_ISBROKEN". (SQL Server Import and Export Wizard)
Error 0xc004700c: Data Flow Task: One or more component failed validation. (SQL Server Import and Export Wizard)
Error 0xc0024107: Data Flow Task: There were errors during task validation. (SQL Server Import and Export Wizard)
DTS used to be a very strong tool but a simple import such as this is causing me extreme grief and wondering of SQL2005 is ready for primetime. FYI SP1 is installed. I am running this from a workstation and not on the server if that makes a difference...
I have a field name call firstname and other field middleinit, now the table contains data. and i want to extract data, the firstname field sometimes contains the middle initial, for for example JOHH D. now I would like to set the firstname field with JOHN only and and set the middleinit field like D only thanks
With this procedure,can't show if GiftTitle contains space ,nonalphabetic chars(&,-,..) and if start with numnber. How can i modify ?? Plz help me. Thanks.
CREATE PROCEDURE dbo.trymatrix AS DECLARE @SQLCommand varchar(4096), @column sysname
SET @SQLCommand = 'select c.CustomerID as Cust_ID,Name as Co_Name, ' + char(13)
DECLARE curGift CURSOR STATIC LOCAL FOR
select distinct GiftTitle from Redemption1
OPEN curGift
WHILE 1 = 1
BEGIN
FETCH curGift INTO @column
IF @@FETCH_STATUS <> 0
BREAK
SET @SQLCommand = @SQLCommand +
'sum(case GiftTitle when ''' +@column +''' then 1 else null end) as '+ @column + ','+char(13)
END
DEALLOCATE curGift
SET @SQLCommand = left (@SQLcommand, len(@SQLcommand)-2) + char(13)
Hi, I have a problem with a text string which is more than 8000 chars. I am taking this string as an input from an application.so,I cannot define a local variable as text or ntext and varchar has limitation of only 8000 char. Can anyone help me in dealing with this situation. Also,I cannot break the string at application level.I wish if I could solve it at db level somehow? TIA pd
In a query Analyser, there is a provision to change the "Maximum characters per column 256 " to our size in advanced option.
It works ok.
We are using VisualInterdev for developing ASP's -- there thro ADO - we are getting the rows from sql server 7.0 -- there data more than 256 characters are truncated.
How will i get the actual data ?.. where i can do the settings..?
How do you insert more than 255 characters into a text field? or am I using READTEXT and DATALENGTH incorrectly?
I am trying to concatenate two 255 varchar strings. Both do have 255 characters of data. I want to insert the concatenated strings into a field define as a text data type. if I try to ' insert into tempTbl(textFieldDataType) values (@text1+@text2) it appears only the @text1 is inserted. I have been told the data is really there, but I can only read the 256 position + with READTEXT.
DECLARE @textptr varbinary(16) select @textptr = (select TEXTPTR(textFieldDataType) from tempTbl where pktempTbl = 33) READTEXT tempTbl.textFieldDataType @textPtr 255 10
This should have read 10 text positions after the 255th position. However, I get msg Msg 7124, Level 16, State 1 The offset and length specified in the READTEXT command is greater than the actual data length of 255.
If I use the datalength function, it appears to confirm that @text2 was never inserted
declare @length int select @length = (select datalength(textFieldDataTypet) from tempTbl where pktempTbl = 33) select @length
I am trying to get the domain name of the server running this instance of SQL server 2000 using xp_loginconfig but have been getting some very strange results. The code example below shows the problem quite nicely. Basically the output shows white space of some sort before the "/HelloWorld" in the second select.
Any ideas why? and how to get rid of it. I've tried using RTRIM() but that made no difference.
On some server instances I dont even see the "/HelloWorld" perhaps it's put in a new line char or something like that.
select ConfigName+'/HelloWorld' from #tmp where ConfigName = 'default domain' select ConfigValue+'/HelloWorld' from #tmp where ConfigName = 'default domain'
I have a table that contains a lot of demographic information. The data is usually small (<20 chars) but ocassionally needs to handle large values (250 chars). Right now its set up for varchar(max) and I don't think I want to do this.
How does varchar(max) store info differently from varchar(250)? Either way doesn't it have to hold the container information? So the word "Crackers" have 8 characters to it and information sayings its 8 characters long in both cases. This meaning its taking up same amount of space?
Also my concern will be running queries off of it, does a varchar(max) choke up queries because the fields cannot be properly analyzed? Is varchar(250) any better?
Should I just go with char(250) and watch my db size explode?
Usually the data that is 250 characters contain a lot of blank space that is removed using a SPROC so its not usually 250 characters for long.
Hi all,I have a variable called Description which is set from a database field - How do i set another variable to grab the first 200 chars from this var? hope this makes sense
I have a column of varchar(2000) but when I use it in a select statement I only get the first 255 characters displayed. (all the data is there as I can see different parts using substring) How do I get the complete column to display?
CREATE TABLE saved_query ( id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, name VARCHAR(32) NOT NULL, query_text VARCHAR(2048) NOT NULL )
CREATE TABLE saved_query ( id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, name VARCHAR(32) NOT NULL, query_text VARCHAR(2048) NOT NULL)
and whenever I insert or update the query_text field, it's always truncated to 255 characters. Why? I clearly specified a larger field size. I also tried defining query_text as a "TEXT" but got the same result.