I have a table in which 2 of the fields are defined as TEXT. It was determined that neither of these fields would have to hold more than 3500 characters, so I changed both fields to VARCHAR (3500). The problem is that any query against this table takes 20 seconds to run. When the fields were defined as data type TEXT, queries ran lick-itty-split!? I would have expected just the opposite. Can someone explain this behavior?
View 1 RepliesHopefully, someone can help me.
I am working with a database that contains multiple fields within the tables that are being used for Clinical notes. The fields are defined as VARCHAR(3500). But when I try to extract data (either through Query Analyzer or Crystal Reports), only the first 256 characters are displayed. I ran a query to give me the length of the maximum entry size which returned 2722 characters, yet only 256 are displayed.
How do I go about extracting ALL of the data from this field? Any help is much appreciated.
Thanks in advance.
I am begginner at best so I hope someone that is better can help.I have a stored procedure that updates a view that I wrote using 2cursors.(Kind of a Inner Loop) I wrote it this way Because I couldn'tdo it using reqular transact SQL.The problem is that this procedure is taking longer and longer to run.Up to 5 hours now! It is anaylizing about 30,000 records. I thinkpartly because we add new records every month.The procedure works like this.The first Cursor stores a unique account and duedate combination fromthe view.It then finds all the accts in the view that have that account duedatecombo and loads them into Cursor 2 this groups them together for datamanipulation. The accounts have to be grouped this way because aaccount can have different due dates and multiple records within eachaccount due date combo and they need to be looked at this way aslittle singular groups.Here is my procedure I hope someone can shead some light on this. Myboss is giving me heck about it. (I think he thinks Girls cant code!)I got this far I hope someone can help me optimize it further.CREATE PROCEDURE dbo.sp_PromiseStatusASBEGINSET NOCOUNT ON/* Global variables */DECLARE @tot_pay moneyDECLARE @rec_upd VARCHAR(1)DECLARE @todays_date varchar(12)DECLARE @mActivityDate2_temp datetimeDECLARE @tot_paydate datetime/* variables for cursor ACT_CUR1*/DECLARE @mAcct_Num1 BIGINTDECLARE @mDueDate1 datetime/* variables for ACT_CUR2 */DECLARE @mAcct_Num2 BIGINTDECLARE @mActivity_Date2 datetimeDECLARE @mPromise_Amt_1 moneyDECLARE @mPromise_Status varchar(3)DECLARE @mCurrent_Due_Amt moneyDECLARE @mDPD intDECLARE @mPromise_Date datetimeSELECT @todays_date =''+CAST(DATEPART(mm,getdate()) AS varchar(2))+'/'+CAST(DATEPART(dd,getdate()) AS varchar(2))+'/'+CAST(DATEPART(yyyy,getdate()) AS varchar(4))+''DECLARE ACT_CUR1 CURSOR FORSELECT DISTINCTA.ACCT_NUM,A.DUE_DATEFROM VWAPPLICABLEPROMISEACTIVITYRECORDS AOPEN ACT_CUR1FETCH NEXT FROM ACT_CUR1 INTO @mAcct_Num1 , @mDueDate1WHILE (@@FETCH_STATUS = 0)BEGINSELECT @rec_upd = 'N 'DECLARE ACT_CUR2 CURSOR FORSELECTB.ACCT_NUM,B.ACTIVITY_DATE,B.PROMISE_AMT_1,B.PROMISE_STATUS,B.CURRENT_DUE_AMT,B.DAYS_DELINQUENT_NUM,B.PROMISE_DATE_1FROM VWAPPLICABLEPROMISEACTIVITYRECORDS B (UPDLOCK)WHERE B.ACCT_NUM = @mAcct_Num1ANDB.DUE_DATE = @mDueDate1ORDER BY B.ACCT_NUM,B.DUE_DATE,B.ACTIVITY_DATE,CASEB.Time_ObtainedWHEN 0 THEN 0ELSE 1END Desc, B.Time_ObtainedOPEN ACT_CUR2FETCH NEXT FROM ACT_CUR2INTO @mAcct_Num2 ,@mActivity_Date2,@mPromise_Amt_1,@mPromise_Status ,@mCurrent_Due_Amt,@mDPD,@mPromise_DateWHILE (@@FETCH_STATUS = 0)BEGIN----CHECK------------------------------------------------------------------------DECLARE @PrintVariable2 VARCHAR (8000)--SELECT @PrintVariable2 = CAST(@MACCT_NUM2 AS VARCHAR)+''+CAST(@MACTIVITY_DATE2 AS VARCHAR)+' '+CAST(@MPROMISE_AMT_1 ASVARCHAR)+' '+CAST(@MPROMISE_STATUS AS VARCHAR)+''+CAST(@mCurrent_Due_Amt AS VARCHAR)+' '+CAST(@mDPD AS VARCHAR)+''+CAST(@mPromise_Date AS VARCHAR)--PRINT @PrintVariable2----ENDCHECK------------------------------------------------------------IF @mDPD >= 30BEGINSELECT @tot_pay = SUM(CONVERT(FLOAT, C.PAY_AMT))FROM vwAplicablePayments CWHERE C.ACCT_NUM = @mAcct_Num2ANDC.ACTIVITY_DATE >= @mActivity_Date2ANDC.ACTIVITY_DATE < @mActivity_Date2 + 15----CHECK------------------------------------------------------------------------DECLARE @PrintVariable3 VARCHAR (8000)--SELECT @PrintVariable3 ='Greater=30 DOLLARS COLLECTED'--PRINT @PrintVariable3----ENDCHECK------------------------------------------------------------ENDELSE IF @mDPD < 30BEGINSELECT @tot_pay = SUM(CONVERT(FLOAT, C.PAY_AMT))FROM vwAplicablePayments CWHERE C.ACCT_NUM = @mAcct_Num2ANDC.ACTIVITY_DATE >= @mActivity_Date2ANDC.ACTIVITY_DATE BETWEEN @mActivity_Date2 AND@mPromise_Date + 5----CHECK----------------------------------------------------------------------DECLARE @PrintVariable4 VARCHAR (8000)--SELECT @PrintVariable4 ='Less 30 DOLLARS COLLECTED'--PRINT @PrintVariable4----END CHECK------------------------------------------------------------END----------------------------------------MY REVISEDLOGIC-------------------------------------------------------IF @rec_upd = 'N'BEGINIF @mDPD >= 30BEGINSELECT @mActivityDate2_temp = @mActivity_Date2 + 15--DECLARE @PrintVariable5 VARCHAR (8000)--SELECT @PrintVariable5 =' GREATER= 30 USING ACTVITY_DATE+15'--PRINT @PrintVariable5ENDELSE IF @mDPD < 30BEGINSELECT @mActivityDate2_temp = @mPromise_Date + 5--DECLARE @PrintVariable6 VARCHAR (8000)--SELECT @PrintVariable6 =' LESS 30 USING PROMISE_DATE+5'--PRINT @PrintVariable6ENDIF @tot_pay >= 0.9 * @mCurrent_Due_Amt--used to be promise amtBEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET PROMISE_STATUS = 'PK',TOTAL_DOLLARS_COLL = @tot_payWHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto PK.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDSELECT @rec_upd = 'Y 'ENDIF ((@tot_pay < 0.9 * @mCurrent_Due_Amt) OR @tot_pay IS NULL)AND( @mActivityDate2_temp > @todays_date )--need to put 1dayof month here for snapshot9/01/2004BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'OP'WHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto OP which is the original Activity Date.--The record will hold this date until it goes into PK,PB,orIP.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @mActivity_Date2WHERE CURRENT OF ACT_CUR2ENDENDELSE IF ((@tot_pay < 0.9 * @mCurrent_Due_Amt) OR @tot_pay ISNULL)AND( @mActivityDate2_temp <= @todays_date )--need to put 1dayof month here for snapshot 9/01/2004BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'PB',TOTAL_DOLLARS_COLL = case when @tot_pay is nullthen 0 else @tot_pay endWHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto PB.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDENDENDELSE IF @rec_upd = 'Y'BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'IP',TOTAL_DOLLARS_COLL = 0WHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto IP.IF @mPromise_Status NOT IN ('IP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDENDFETCH NEXT FROM ACT_CUR2 INTO @mAcct_Num2,@mActivity_Date2,@mPromise_Amt_1,@mPromise_Status ,@mCurrent_Due_Amt,@mDPD,@mPromise_DateENDCLOSE ACT_CUR2DEALLOCATE ACT_CUR2FETCH NEXT FROM ACT_CUR1 INTO @mAcct_Num1 , @mDueDate1ENDCLOSE ACT_CUR1DEALLOCATE ACT_CUR1SET NOCOUNT OFFENDGO
View 15 Replies View RelatedI have followed many tutorials on selecting and replacing text in text fields, varchar fields and char fields, but I have yet to find a single script that will to all 3 based on field type. Let's assume for a moment that I don't know where all in my database a certain value that I need changed resides ... i.e., the data's tablename and fieldname. How would I go about doing the following ... or more importantly, is this even possible in a SQL only procedure?1) Loop over entire database and get all user tables2) Loop over all user tables and get all fields3) Loop over all fields and determine the field type4) switch between field types and change a string of text from 'a' to 'b'Please be gentle, I'm a procedure newb.
View 9 Replies View Relatedhi, friends, please look at this:
I have a SSIS package, and inside it I do something like below:
1. I have a SQL component, to give back a object to store the records.
2. I have a VB script component, I direct the object I got in 1 step into the script as a dataset.
My problem is:
I run the package in the SQL SERVER 2005 Store Procedue like this:
do
dtexec.exe package.dtsx
loop untill i>t
I control the it runs 30 times. But I found that the speed is slower and slower.
the first time, it takes about 600 s, but the last time, it takes the 1800 s.
Why?
The package don't drop the object it create during the loop in the Store Procedue ?
Thanks!
for example:
SELECT * from [table1] WHERE CONTAINS([msgcomment], '"fast" NEAR "performance"')
would always slower than
SELECT * from [table1] WHERE [msgcomment] = '%fast%performance%'
Why? and how can it be solved? can you help me?
:)
I have a table with a column of varchar(4000) and another table with 2 columns that are varchar(2000)
Is it better to make these columns text columns?
The 2 varchar(2000) columns do not need to be searched, but many of the other columns in that table do need to be searched.
The varchar(4000) column has a full text index on it.
Any help would be appreciated
Thank you
Hi ,
I have a website where we review cds ... the users write their own reviews ...
But when the reviews are displayed on screen all the breaks are gone and all the text comes in one block .
Can anyone advise on what to do ?
I tried to change from varchar to text but it didn't change anything.
Thanks for the help
Hello, I have read that microsoft is getting rid of the text datatype and replacing it with varchar. The maximum that varchar can hold is 8000 bytes. Is there a way to get above 8000 bytes without using text?
View 1 Replies View RelatedHi,What is the maximum character a text type column can contain ? Can'tchange the length to upper than 16....What is the bigger ? Varchar(8000) or Text ?Regards
View 2 Replies View RelatedHi all
iam trying to but a varchar variable into a TEXT var but i get this error "The assignment operator operation cannot take a text data type as an argument"
anybody know what shall i do
best regards
hi everyone a question for all you wise men out there! ;-)
i run a db with thousands of reviews, interviews, article and so on. What's better: 1) leaving all the articles on .txt files and then using the file system object to show them in the page; 2) insert the articles in the db splitting them in several varchar fields 3) insert the articles in the db using the text/blob fields
thanx, it's a very hard question for me!
is it smarter to use the text field type or to use a varchar field type with a length of 100-150? please give me a detailed answer. thanx
View 1 Replies View RelatedHello Guys.
Here is my issue i have email addresses in a column of a table in sql server. Are addreses has changed since and i need to do a mass update of these email addreses i need to replace a few char to reflect this change like ex:
AAAAA@BBB.CCC.Com i need to replace the BBB. part with nothing so that it looks like this AAAAA@CCC.Com.
Please help.
hi,
i would like to convert text string(field) to varchar so I can use later group by a special string.
what shall i use?
thank you
Hello,I have a column (text datatype) and has to send an email as a text(not attachment) using cdonts. I am reading the data from text columnstoring in a varchar field and saying cdonts.body = [data].This way I can send email to as a text format. Now, my problem is whenlength of data is greater than the 8000 chars it truncates the rest ofthe data.......and email I send is a truncated email.......loosingimporatnt data.How should I resolve this situation.......I am trying some differentideas but not worked yet. Finally, I am writting the entire content ina file and sending it as attachment but the reaquirement is to send itas a body text.Any ideas?Let me know if you need more details!Thanks,-Hayatwww.mysticssoft.com
View 1 Replies View RelatedI have the following form in this adress:(it's framework 1.1 asp.net vb.net)http://admin.artemrede.pt/login.aspx?ReturnUrl=%2fdefault.aspxthe utilizador is: testepalavra passe is: 12345I'm using some text datatypes and some very big varchars the problem is that when you try to add a new record or to edit a existing record, if for example in one of the very big varchars or text, textboxes, several lines of text, It only puts in the database the 2 first words that you write. I used the VS debug and apparently everything looks ok......(if it would I probably hadn't this error....) Dim OurConnection As SqlConnectionOurConnection = New SqlConnection(conn_default)Dim OurCommand As SqlCommandOurCommand = New SqlCommand("Insert Into espectaculo (foto_destaque, thumb, area_prog, nome_espectaculo, coord, nome_comp, duracao, f_etaria, sinopse, iterancia, ficha, bio_interv, bio_comp, link_comp, notas_imprensa) Values (@fotod, @thumb, @areap, @ne, @coord, @nc, @duracao, @fe, @sinopse, @it, @ficha, @bioI, @bioC, @link, @notasp)", OurConnection)If (foto_destaque.Text = "") ThenOurCommand.Parameters.Add("@fotod", SqlDbType.VarChar, 12).Value = " "ElseOurCommand.Parameters.Add("@fotod", SqlDbType.VarChar, 12).Value = foto_destaque.TextEnd IfIf (thumb.Text = "") ThenOurCommand.Parameters.Add("@thumb", SqlDbType.VarChar, 12).Value = " "ElseOurCommand.Parameters.Add("@thumb", SqlDbType.VarChar, 12).Value = thumb.TextEnd IfIf (area_prog.Text = "") ThenOurCommand.Parameters.Add("@areap", SqlDbType.VarChar, 50).Value = " "ElseOurCommand.Parameters.Add("@areap", SqlDbType.VarChar, 50).Value = area_prog.TextEnd IfIf (nome_esp.Text = "") ThenOurCommand.Parameters.Add("@ne", SqlDbType.VarChar, 100).Value = " "ElseOurCommand.Parameters.Add("@ne", SqlDbType.VarChar, 100).Value = nome_esp.TextEnd IfIf (coord.Text = "") ThenOurCommand.Parameters.Add("@coord", SqlDbType.VarChar, 100).Value = " "ElseOurCommand.Parameters.Add("@coord", SqlDbType.VarChar, 100).Value = coord.TextEnd IfIf (nome_comp.Text = "") ThenOurCommand.Parameters.Add("@nc", SqlDbType.VarChar, 50).Value = " "ElseOurCommand.Parameters.Add("@nc", SqlDbType.VarChar, 50).Value = nome_comp.TextEnd IfIf (duracao.Text = "") ThenOurCommand.Parameters.Add("@duracao", SqlDbType.VarChar, 25).Value = " "ElseOurCommand.Parameters.Add("@duracao", SqlDbType.VarChar, 25).Value = duracao.TextEnd IfIf (faixa.Text = "") ThenOurCommand.Parameters.Add("@fe", SqlDbType.VarChar, 50).Value = " "ElseOurCommand.Parameters.Add("@fe", SqlDbType.VarChar, 50).Value = faixa.TextEnd IfIf (sinopse.Text = "") ThenOurCommand.Parameters.Add("@sinopse", SqlDbType.VarChar, 8000).Value = " "ElseOurCommand.Parameters.Add("@sinopse", SqlDbType.Text, 16).Value = sinopse.TextEnd IfIf (itener.Text = "") ThenOurCommand.Parameters.Add("@it", SqlDbType.VarChar, 200).Value = " "ElseOurCommand.Parameters.Add("@it", SqlDbType.VarChar, 200).Value = itener.TextEnd IfIf (ficha.Text = "") ThenOurCommand.Parameters.Add("@ficha", SqlDbType.Text, 16).Value = " "ElseOurCommand.Parameters.Add("@ficha", SqlDbType.Text, 16).Value = ficha.TextEnd IfIf (bio_interv.Text = "") ThenOurCommand.Parameters.Add("@bioI", SqlDbType.Text, 16).Value = " "ElseOurCommand.Parameters.Add("@bioI", SqlDbType.Text, 16).Value = bio_interv.TextEnd IfIf (bio_comp.Text = "") ThenOurCommand.Parameters.Add("@bioC", SqlDbType.Text, 16).Value = " "ElseOurCommand.Parameters.Add("@bioC", SqlDbType.Text, 16).Value = bio_comp.TextEnd IfIf (linkComp.Text = "") Or (linkComp.Text = "http://") ThenOurCommand.Parameters.Add("@link", SqlDbType.VarChar, 100).Value = " "ElseOurCommand.Parameters.Add("@link", SqlDbType.VarChar, 100).Value = linkComp.TextEnd IfIf (notas_press.Text = "") ThenOurCommand.Parameters.Add("@notasp", SqlDbType.VarChar, 5000).Value = " "ElseOurCommand.Parameters.Add("@notasp", SqlDbType.VarChar, 5000).Value = notas_press.TextEnd If OurConnection.Open()OurCommand.ExecuteNonQuery()OurConnection.Close()carrega()'grid1.DataBind()End IfEnd Sub Sub carrega()conn_default = ConfigurationSettings.AppSettings("ArtemredeConnection")Dim OurConnection As SqlConnectionOurConnection = New SqlConnection(conn_default)OurConnection.Open() Dim OurCommand As SqlCommandDim SelectCommand As StringSelectCommand = "select id_espectaculo, area_prog, nome_espectaculo, nome_comp from espectaculo"OurCommand = New SqlCommand(SelectCommand, OurConnection) Dim Select_DataAdapter As New SqlDataAdapter(OurCommand)Dim Select_DataSet As New DataSet'Dim SP_DataTable_Rowcount As IntegerSelect_DataAdapter.Fill(Select_DataSet, "Espectaculos")grid1.DataSource = Select_DataSetgrid1.DataBind()OurConnection.Close() End Sub
View 2 Replies View RelatedI am trying to do a simple select statement on my db but keep getting the fallowing exception being thrown ...System.Web.Services.Protocols.SoapException was unhandled Actor="" Lang="" Message="System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Data.SqlClient.SqlException: The data types text and varchar are incompatible in the equal to operator. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader() at RelayService.ValidAccessID(String ID) in C:Documents and SettingsDeanMy DocumentsVisual Studio 2005ProjectsMsgSRXMsgSRXServiceApp_CodeRelayService.vb:line 193 at RelayService.SendMessage(String msg, String AccessID, Int64& MsgID) in C:Documents and SettingsDeanMy DocumentsVisual Studio 2005ProjectsMsgSRXMsgSRXServiceApp_CodeRelayService.vb:line 377 --- End of inner exception stack trace ---" Node="" Role="" Source="System.Web.Services" StackTrace: at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall) at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters) at ChatClient.MsgSRX.RelayService.SendMessage(String msg, String AccessID, Int64& MsgID) in C:Documents and SettingsDeanMy DocumentsVisual Studio 2005ProjectsMsgSRXChatClientWeb ReferencesMsgSRXReference.vb:line 342 at ChatClient.Form1.btnSend_Click(Object sender, EventArgs e) in C:Documents and SettingsDeanMy DocumentsVisual Studio 2005ProjectsMsgSRXChatClientForm1.vb:line 13 at System.Windows.Forms.Control.OnClick(EventArgs e) at System.Windows.Forms.Button.OnClick(EventArgs e) at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent) at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.ButtonBase.WndProc(Message& m) at System.Windows.Forms.Button.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg) at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData) at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.Run(ApplicationContext context) at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun() at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel() at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine) at ChatClient.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81 at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args) at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() at System.Threading.ThreadHelper.ThreadStart_Context(Object state) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ThreadHelper.ThreadStart() ... Here is the source code to the method I am using ... Private Function ValidAccessID(ByVal ID As String) As Boolean Dim conn As New SqlConnection() conn.ConnectionString = ConfigurationManager.ConnectionStrings("dbConnect").ConnectionString Dim cmd As String cmd = "SELECT AccessID, ExpireTime " cmd &= "FROM AccessIDNumbers " cmd &= "WHERE AccessID IN ('" & ID & "')" ' cmd &= "WHERE AccessID ='@ID'" Dim C As New SqlCommand(cmd, conn) ' C.Parameters.AddWithValue("@ID", New SqlTypes.SqlString(ID)) ' C.Parameters.Item("@ID").SqlDbType = SqlDbType.Text conn.Open() Dim Count As Integer = 0 Dim reader As SqlDataReader = C.ExecuteReader Dim expired As Boolean = False If reader.HasRows Then While reader.Read Count += 1 Dim et As SqlTypes.SqlDateTime et = SqlTypes.SqlDateTime.Parse(reader("ExpireTime")) Dim ct As New SqlTypes.SqlDateTime(Now) If ct > et AndAlso expired = False Then expired = True End While End If conn.Close() C.Dispose() C = Nothing conn.Dispose() conn = Nothing Return expired = False End Function ... the problem is with the cmd &= "WHERE AccessID IN ('" & ID & "')" statement. How Can I get this to work properly!!! My data types for the AccessIDNumbers table are as fallows ..UserName -> text -> nulls not allowedAccessID -> text -> nulls not allowedCreationTime -> datatime -> nulls not allowedExpireTime -> datatime -> nulls not allowedCurrentTable -> text -> nulls allowedI don't understand why I'm getting this error!!!
View 3 Replies View RelatedI encounter this particular error.
Exception Details: System.Data.SqlClient.SqlException: The data types varchar and text are incompatible in the equal to operator.
Line 21: Dim reader As SqlDataReader = command.ExecuteReader()
This is the first time I'm trying out with MS SQL so I'm abit lost. I hope my code is correct and I've did a little search. I did not set "Text" in my database, I use int and varchar. Here's the affected part of my code and the database. Dim password As String = ""
Dim querystring As String = "SELECT Password FROM Member WHERE Username = @username"
'Dim conn as SqlConnection
Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("mainconnect").ConnectionString)
Dim command As New SqlCommand(querystring, conn)
command.Parameters.Add("@username", SqlDbType.Text)
command.Parameters("@username").Value = txtLogin.Text
conn.Open()
Dim reader As SqlDataReader = command.ExecuteReader()
While reader.Read()
password = reader("Password").ToString()
End While
reader.Close()
End Using
My database:
User_ID int(4)
Username varchar(50)
Password varchar(255)
Email varchar(50)
Any ideas?
I am trying to change a text field into a varchar 8000.
I get his error message when trying to convert.
Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot create a row of size 8317 which is greater than the allowable maximum of 8060.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.
Anyone know of a way to either truncate the text field or
to select only those that are over the 8000 character mark?
Please advise
Thanks
Susan
Please, i have a big db with a "VarChar" field (8000 char) and now i need more "space".
I can use "Text" but how i can convert all my data?
If i try to change from the Enterprise Manager i loose all data!!!!
:confused: :confused: :confused:
Please, help me!
Hi, all
I am seting up a table with email message, I am wondering what is the max length for varchar field. I am so reluctant to use text field, since when
I run query for the descriptiona in sql analyzer, text field cannot be fully display in column. Any tricks to share?
Thanks
Betty
I am trying to add a carriage return to the end of a text field through a script. This is what I'm trying:
UPDATE Table_Name SET Column_TEXT = Column_TEXT) + '
' WHERE Column_TEXT = 'Some text'
I also tried
UPDATE Table_Name SET Column_TEXT = Column_TEXT) + '<cr>' WHERE Column_TEXT = 'Some text'
But I keep getting the error
The data types text and varchar are incompatible in the equal to operator.
Help!!
Thanks in advance
Mangala
I understood that sql 2005 has EncryptByCert(varchar) function to encrypt data field. but varchar is limit by 8,000 chars long.
View 3 Replies View RelatedWhen using DTS (in SQL 7) to export via OLE DB a large varchar to a text file, it clips it at 255 chars.
No other data access drivers seem to work, either. This is lame! I cannot use bcp as a work
around, because i want to use quoted comma-delimited, which it doesn't support, and I
am using query-based export, where the query calls a stored proc, which bcp also doesn't
support.
Are there any new versions of MDAC that fix this? Anyone know a workaround? My current hack fix
is to split my field into 2, but this is a grubby fix that hassles my reciptients.
This is a pretty fundamental limitation to a major product!
dn
We're connecting to SQL Server 7.0 (sp2) via ODBC for our ASP application, and are having trouble saving information collected in a <textarea> tag on an HTML form. I assume the <textarea> tag contains data of type TEXT. i thought there would be no problem in converting it into a string and saving it in a VARCHAR field in the database. this works fine as long as the string is 255 characters or less. anything over that give me an "Errors Occurred" SQL Server error. the field that i'm saving this into is a VARCHAR length 8000. i thought SQL 7.0 had gotten rid of the 255 limit on varchar - could this be an ODBC driver problem (on my web server i currently have 3.50.0305) and/or should i install service pack 2 on my SQL Server? anyone advice would be much appreciated.
thanks,
matt
create table tblxmldata
(id int, xmltext varchar(max))
insert into tblxmldata values(1,'<associatedText><value type="PO">GTT taken</value></associatedText>')
insert into tblxmldata values(1,'<associatedText><value type="PO">Check sugar today please</value></associatedText>')
I want the output as
GTT taken
Check sugar today
Hi, I'm trying to improve performance for a db that stores messages. The message is currently stored in an ntext field, but when I look at all the records from the past 3 months, I found that 88% are less than 1000 characters, and 97% are less than 3000 characters. I don't want to actually limit the message size, but it seems like I might get much better performance using a varchar(3000) field to hold most of the messages, and a separate text field just used for those 3% that really are long. Is this a good idea? If so, is it better to put the Message and LongMessage fields in the same table; or, have a separate table to hold the long messages? If it is in a separate table, it would need to be left joined with the message table each time messages are retrieved.Also -- I am getting about 700 new messages daily, and right now have over 150,000 messages stored. The vast majority of activity involves new messages. Is this a good situation to look at using horizontal partitioning?Thanks for any help, I don't really have anyone to discuss this with and it is really helpful to get some other views!!
View 6 Replies View RelatedI am stuck! and need help!
I have a problem I can't seem to find the solution with this aweful limitations on VARCHAR fields of 255.
Within a stored procedure called Store_Check, I need to dynamically build a string (@string) using VARCHAR(255) since the text datatype can't be used in a stored procedure.
So,this string is built according to whether the Store ID is NOT NULL. So if the StoreID is not null, I start building this string 'Exec Update_Store_Address @StoreID1, @address2'. There are 20 StoreID's passed into Store_Check. IF all 20 StoreID's are not NULL, the executed String greatly exceeds 255 because the string winds up looking like this
'Exec Update_Store_Address @StoreID1, @address1 Exec Update_Store_Address @StoreID2, @address2 Exec Update_Store_Address @StoreID3, @address3 Exec Update_Store_Address @StoreID4, @address4 Exec Update_Store_Address @StoreID5, @address5 Exec Update_Store_Address @StoreID6, @address6 etc. etc.'
I am not executing this string within the StoredCheck procedure. It needs to be passed as ONE string to a VB program and it gets executed by the VB program. Even if I create 4 local variables and concatenate them, it stops at the 255th character.
Also, a local varialbe of type TEXT cannot be declared within stored procedure.
What can I do? I am stuck!
Angel
One purchased app stored user's multiple lines input into a varchar column in which including char(13) and char(10).
My app need to select this value and format to multiple lines text in one text box.
How to code to output it?
Impact on performance of changing Text column size.
However, many of them were regarding older releases of SQL Server.
Does the architecture in 2012, 2014 releases make this less of an issue ?
In other words, why bother going from Varchar(1000) to Varchar(50) ?
I'm just thinking maybe there is column compression automatically now.
When I change a column from text to varchar using thedesign view of a table within Enterprise Manager thevarchar value (less than 8000 characters) appears in thecolumn but does SQL Server automatically delete the textvalues from their pages?If not are they removed by routine reindex/defrag orshould I create a new table, import from the text asvarchar and drop the old table to make sure the pagesstoring the original text version of the values aredeleted?..
View 1 Replies View RelatedI have the following code tha imports the contents of a text file into a varchar(max) field.
Unfortunately the CR/LF are stripped out when I look at the field.
How can I preserve them?
Code Snippet
DECLARE @obj VARCHAR(MAX)
SELECT @obj=BulkColumn
FROM
OPENROWSET(BULK 'C:qsiObjectCreation.sql',SINGLE_CLOB) AS ExternalFile
insert into scriptor (script) values (@obj)