SQL String Too Long?
Dec 28, 2006
My SQL statement:
SQL Server seems to think my SQL Statement is too long........ I'm not sure why.
"INSERT INTO tblChangeControls (Initiator,BPM,AddRemMod,ChangeType,SpecificChange,ChangeDescription,TechnicalDescriptionOfChange,DateInitiated) select 'Benjamin Short' ,'6' ,'2' ,'Printer' ,'40' ,'' ,'';"
Error message:
The identifier that starts with 'INSERT INTO tblChangeControls (Initiator,BPM,AddRemMod,ChangeType,SpecificChange,ChangeDescription,TechnicalDescriptionOfChange,' is too long. Maximum length is 128.
View 2 Replies
ADVERTISEMENT
Aug 7, 2007
This is what I am trying to save and what is actually being saved:
All variables are STRINGS.
Saving: 08:02:40 08:18:05 ^09:34:07 09:39:25 ^10:02:50 10:18:19 10:37:33 11:01:34 11:23:26 11:50:47 ^12:52:30 13:42:37 ^14:41:04 ^18:36:13
Saved: 08:02:40 08:18:05 ^09:34:07 09:39:25 ^10:02:50 10:
Saving: 08:08:52 08:59:55 ^09:38:04 09:54:20 ^10:15:37 10:34:40 10:39:02 11:12:45 11:39:33 ^12:18:51 13:11:24 ^14:04:52 15:18:43 ^19:13:10
Saved: 08:08:52 08:59:55 ^09:38:04 09:54:20 ^10:15:37 10:
The codes:
Table scripted to a query:
USE [Analytical]
GO
/****** Object: Table [dbo].[DailyTickMinMaxAnalysis] Script Date: 08/07/2007 15:44:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DailyTickMinMaxAnalysis](
[symbol] [varchar](50) NOT NULL,
[cDate] [varchar](50) NOT NULL,
[part] [varchar](50) NOT NULL,
[collection] [varchar](max) NULL,
CONSTRAINT [remPat_Trades] PRIMARY KEY CLUSTERED
(
[symbol] ASC,
[cDate] ASC,
[part] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Stored procedure is:
USE [Analytical]
GO
/****** Object: StoredProcedure [dbo].[InsertDailyTickMinMaxAnalysis] Script Date: 08/07/2007 15:45:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[InsertDailyTickMinMaxAnalysis]
@symbol varchar(50),
@cDate varchar(50),
@part varchar(50),
@collection varchar(50) = NULL
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.DailyTickMinMaxAnalysis (
symbol,
cDate,
part,
[collection])
Values (
@symbol,
@cDate,
@part,
@collection)
END
C# procedure:
public void saveHistTickMinMaxAnalysis ( int tableIndex )
{
if ( tableIndex != -1 )
{
using ( SqlConnection conn_Analytical = new SqlConnection ( "server=ddmnvt61\sqlexpress;" +
"database=Analytical;Trusted_Connection=Yes" ) )
{
SqlCommand cmdm = new SqlCommand ( );
conn_Analytical.Open ( );
cmdm.Connection = conn_Analytical;
cmdm.CommandType = CommandType.StoredProcedure;
cmdm.CommandText = "InsertDailyTickMinMaxAnalysis";
cmdm.Parameters.Clear ( );
cmdm.Parameters.Add ( "@symbol", SqlDbType.VarChar, 50, "symbol" );
cmdm.Parameters.Add ( "@cDate", SqlDbType.VarChar, 50, "cDate" );
cmdm.Parameters.Add ( "@part", SqlDbType.VarChar, 50, "part" ); // 2
cmdm.Parameters.Add ( "@collection", SqlDbType.VarChar, 500, "collection" ); // 3
foreach ( DataRow row in Globals.dtSets.Tables[tableIndex].Rows )
{
cmdm.Parameters[0].Value = ( string )row[0];
cmdm.Parameters[1].Value = ( string )row[1];
cmdm.Parameters[2].Value = ( string )row[2];
cmdm.Parameters[3].Value = ( string )row[3];
Console.WriteLine ( "Saving {0}", ( string )row[3] ); // <= where the print out occurs to confirm that the right string is being passed
try
{
cmdm.ExecuteNonQuery ( );
}
catch { }
}
}
}
} // saveHistTickMinMaxAnalysis
Table is defined with the last columns[3].Length = 500;
What happened to the rest of my strings?
I would appreciate any help.
Many thanks.
View 3 Replies
View Related
Aug 27, 2007
hi all, i'm retreiving user input using textboxes and saving to a gridview. i'm getting this error and i dont know whats causing it. <asp:SqlDataSource ID="SqlDataSource1" runat="server"
InsertCommand="INSERT INTO test101(Surname,Names,Regno)VALUES (@Surname, @Names, @Regno)"ConnectionString="<%$ ConnectionStrings:engineeringConnectionString %>" ProviderName=System.Data.SqlClient
ConflictDetection="CompareAllValues" >
<InsertParameters>
<asp:ControlParameter ControlID="TextBox1" DefaultValue="TextBox1.Text" Name="Surname"
PropertyName="Text" Size="50" Type=Char />
<asp:ControlParameter ControlID="TextBox2" DefaultValue="TextBox2.Text" Name="Names"
PropertyName="Text" Size="50" Type=Char />
<asp:Parameter DefaultValue="TextBox3.Text" Name="Regno" />
</InsertParameters>
</asp:SqlDataSource>
<asp:GridView ID="GridView1"
runat="server" AutoGenerateColumns="False" AutoGenerateDeleteButton="True" DataKeyNames="ID"AutoGenerateEditButton="True" AllowSorting="True" BackColor="LightGoldenrodYellow"
BorderColor="Tan" BorderWidth="1px" CellPadding="2" ForeColor="Black" GridLines="None" PageSize="20"
Height="374px" EmptyDataText="null" DataSourceID=SqlDataSource1 Visible="False" >
<Columns>
<asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" ReadOnly="True"
SortExpression="ID" />
<asp:BoundField DataField="Surname" HeaderText="Surname" SortExpression="Surname" />
<asp:BoundField DataField="Names" HeaderText="Names" SortExpression="Names" />
<asp:BoundField DataField="Registration" HeaderText="Registration" SortExpression="Registration" />
<asp:BoundField DataField="Grade" HeaderText="Grade" SortExpression="Grade" />
</Columns>
</asp:GridView>
and the code behind is:protected void Page_Load(object sender, EventArgs e)
{SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=engineering; Integrated Security=True");
conn.Open();
GridView1.DataBind();
conn.Close();
}
public void login1_Click(object sender, EventArgs e)
{
SqlDataSource1.Insert();
Response.Write("you have successfully being added to the database");
can anyone help?!!!
View 1 Replies
View Related
Sep 12, 2007
Hello Everybody,I have a problem that I have an exception from a SQLDataSource when a user types in more characters to a TextBox then is allowed.First of all - is there ant built in ASP.NET way to handle that problem?Second - if not then I have to handle the Exception from the SQLDataSource myself. I can write a handler for that which will look like:1 protected void DetailsViewDS_OnUpdated(object sender, SqlDataSourceStatusEventArgs e)
2 {
3 if (e.Exception != null)
4 {
5 // do some stuff
6 }
7 }
The problem is that I would like to show the user an error message which says how many characters he can insert. So the other question is: is there any way to get automatically from the SQL DB what is a maximum length of a varchar? If I use LEN/LENGTH it gives me the current length of given entry (which actually is the current length of the varchar). But I want to get the maximum length of this concrete varchar (NOT the varchar(max) - this shows maximum length of a varchar in general - like ~20000 ).I hope I did not make it too complex ;)All the best,Joseph A. Habdank
View 6 Replies
View Related
Mar 23, 2004
Anyone know how to check the length of a varchar data in a table? I want to delete rows that contains an entry that exceeds 100 characters.
I should be able to write a program to do it, but I am wondering if I can do it in SQL.
View 2 Replies
View Related
Apr 17, 2015
How to separate a long string and put them into three columns.
For example string
Toronto|Ontario|Canada,Dallas|Texas|USA,New York|New York|USA,Windsor|Ontario|Canada
I have a table with 5 columns, 3 columns are City, State and Country.
I would like to separate them and put those into categories
Continent City State Country Added
Toronto Ontario Canada
Dallas Texas USA
New York New York USA
View 1 Replies
View Related
Dec 31, 2007
I have a column with varchar(2000) that contains events with time-stamps. The data looks something like this for a record:
03:14:46: abc 03:14:47: def 03:14:59: xyz 03:15:17: zzz
I would like to parse out each time-stamp and event to a separate columns, like
col1= 03:14:46: abc
col2= 03:14:47: def
col3= 03:14:59: xyz
col4= 03:15:17: zzz
The number of events are dynamic so number of time-stamps with events can be anything from 2-30 of them.
Anyone would suggest how I can resolve this?
Thanks.
View 5 Replies
View Related
May 18, 2006
Hi there, I'm having a problem when I insert a string (from C#) which is 167 characters long. The field in SQL Server Express is a varchar(250), but the string gets cut of somehow at 150 characters.
Why does this happen?
The table and stored proc I use are defined like this:
USE [Test]
GO
/****** Object: Table [dbo].[tblImage] Script Date: 05/18/2006 10:42:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblImage](
[imgID] [bigint] IDENTITY(1,1) NOT NULL,
[persID] [bigint] NOT NULL,
[imgPad] [varchar](250) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
ALTER PROCEDURE [dbo].[spInsertImage]
@persID BIGINT
, @imgPad VARCHAR(150)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO tblImage ( persID
, imgPad )
VALUES ( @persID
, @imgPad)
END
Does anybody know what is going on?
Thanks,
Starik
View 3 Replies
View Related
Mar 30, 2005
Any way to parse out a text value (not varChar, using text data type) that is > than 8000 characters long? I'm looping through 1 big string passed to the DB that is pipe delimited, but I find myself needing the substring function to keep track of which segment I'm acting on (after an update, I then need to take that segment and remove it from the string)...but the subString function won't take anything larger than 8000 chars.
Say I have this string that is text data type...
'aaa|bbb|ccc|ddd|....'
..and so on, surpassing 8000 char length, how could you parse it out using the pipes as the delimter, then do an Update using that segment? Afterward, return to that string and find the next segment, then use it, and so on (in a loop). I tried using an update to set the string = replace(string, segmentJustUsed, '') to "erase" it, but replace can't take text as the datatype. Any help? Hope this isn't to confusing.
View 3 Replies
View Related
Jun 4, 2007
hi,
i have worked three days on a query to display all my results in a beautiful report. The query is fine because when i execute it in Query Analyzer i have all results i want to see in my statistics-table in my report...
One thing: it's contains about 100 unioned statements, which results in a super-long query. Performance is OK because it are all 100 very easy statements that are union-ed together.
But, when I copy-paste it in my query-string window/textbox of the report designer, I see that there's a maximum on that textbox lenght, which results in the fact that my long query suddenly stops.
Any solutions????
View 4 Replies
View Related
Mar 26, 2004
I have the following problem. My SQL Query that i wrote works but the result that is displayed in Query analyzer cuts most of my long text that I want in my result. The long text string is approx about 400 characters and the type is varchar of the field. Any ideas??
SELECT '510', PRODCLASSID
, '1', COMPONENTID,'ENG'+SPACE(2),'#'+SPACE(254),'#'+SPAC E(254),'#'+SPACE(254),'#'+SPACE(99),externalid,
'Desc1' = CASE
WHEN SUBSTRING(externalid,1,2) = 'MF'
THEN 'Full machine warranty : parts, labour, mileage and others covered at warranty rates applicable at the time of repair. '
WHEN SUBSTRING(externalid,1,2) = 'MP'
THEN 'Full machine warranty, parts only : parts covered at warranty rates applicable at the time of repair. '
WHEN SUBSTRING(externalid,1,2) = 'PF'
THEN 'Power line warranty : parts, labour, mileage and others covered at warranty rates applicable at the time of repair. '
WHEN SUBSTRING(externalid,1,2) = 'PP'
THEN 'Power line warranty, parts only : parts are covered at warranty rates applicable at the time of repair. '
END
+
CASE
WHEN SUBSTRING(externalid,LEN(externalid)- 3,4) = '2018'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 18 month or 2000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '3024'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 24 month or 3000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '4030'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 30 month or 4000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '5036'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 36 month or 5000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '6042'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 42 month or 6000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '8054'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 54 month or 8000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '1074'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 74 month or 10000 HRS, whichever comes first. '
END
+ 'Flexible warranty is handled according to the procedures described in ESPPM 3-10.'
+
CASE
WHEN prodclassid IN ('P1','P11','P8','P9')
THEN ' (mileage limited to 300 km)'
WHEN prodclassid IN ('P7')
THEN ' (mileage limited to 200 km)'
ELSE NULL
END
+
SPACE(5000 - LEN('Desc1'))
.......
View 1 Replies
View Related
Nov 10, 2007
Hi
I sent a long string of ID from front end to my stored procedure...till now I was using varchar(8000)...but if the string crossess that limit it is breaking.
If I try to use text datatype..It doesn't support rtrim, stuff functions etc...
So could any one suggest me a best way to save a long string without any restriction of size...
My front end is C#.Net and Back End is SQL SERVER 2000
Thanks in advance
View 1 Replies
View Related
Aug 15, 2006
Hi,
I have a string almost 11006 length.. when i try to send back from SQLCLR procedure
it says cannot send ..
here is Exception Text "Message length 11060 exceeds maximum length supported of 4000."
Max limit to send a string using pipe is 4000
How I can send a string which is large in size than 4000.
Thanks
View 6 Replies
View Related
Mar 10, 2015
I have a scenario where in I need to use a comma delimited string as input. And search the tables with each and every string in the comma delimited string.
Example:
DECLARE @StrInput NVARCHAR(2000) = '.NET,Java, Python'
SELECT * FROM TABLE WHERE titleName = '.NET' AND titleName='java' AND titleName = 'Python'
As shown in the example above I need to take the comma delimited string as input and search each individual string like in the select statement.
View 3 Replies
View Related
Mar 20, 2014
We have some URLs within a bulk block of text some of which are very long. I need to identify rows where such urls exceed say 100 characters in length in amongst other text.So the rule would be return a record if within the string there is a string (without spaces) longer than 100 characters.
View 9 Replies
View Related
Sep 8, 2015
I have following query which return me SP/Views and Functions script using:
select DEFINITION FROM .SYS.SQL_MODULESNow, the result looks like
Create proc
create procedure
create proc
create view
create function
I need its result as:
Alter Procedure
Alter Procedure
Alter Procedure
Alter View
Alter Function
I used following
select replace(replace(replace(DEFINITION,'CREATE PROCEDURE','Alter Procedure'), 'create proc','Alter Procedure'),'create view','Alter View') FROM .SYS.SQL_MODULESto but it is checking fixed space like create<space>proc, how can i check if there are two or more spaces in between create view or create proc or create function, it should replace as i want?
View 5 Replies
View Related
Nov 2, 2006
I am trying to insert a row into a table of Microsoft SQL Server 2000.
There are various columns.
[SNO] [numeric](3, 0) NOT NULL ,
[DATT] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[DATTA] [char] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[CODECS] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
The [DATTA] column is causing a problem. Even if I am trying to put only 1700 character string into [DATTA], the java code throws the following exception:-
StaleConnecti A CONM7007I: Mapping the following
SQLException, with ErrorCode 0 and SQLState 08S01, to a
StaleConnectionException: java.sql.SQLException: [Microsoft][SQLServer 2000
Driver for JDBC]Connection reset
at
com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
Why is it throwing an exception even though the sum-total of this row doesn't exceed 8000 characters?
Can anyone please tell me what's wrong?
View 6 Replies
View Related
May 22, 2007
Hello to all,
I have a problem with ms sql query. I hope that somebody can help me.
i have a table "Relationships". There are two Fields (IDMember und RelationshipIDs) in this table. IDMember is the Owner ID (type: integer) und RelationshipIDs saves all partners of this Owner ( type: varchar(1000)). Example Datas for Table Relationships: IDMember Relationships .
3387 (2345, 2388,4567,....)
4567 (8990, 7865, 3387...)
i wirte a query to check if there is Relationship between two members.
Query:
Declare @IDM int; Declare @IDO int; Set @IDM = 3387, @IDO = 4567;
select *
from Relationship where (IDMember = @IDM) and ( cast(@ID0 as char(100)) in
(select Relationship .[RelationshipIDs] from Relationship where IDMember = @IDM))
But I get nothing by this query.
Can Someone tell me where is the problem? Thanks
Best Regards
Pinsha
View 9 Replies
View Related
Mar 12, 2008
Hello, I am tring to add a string my database. Info is added, but it is the name of the string, not the data contained within. What am I doing wrong? The text "Company" and "currentUserID" is showing up in my database, but I need the info contained within the string. All help is appreciated!
Imports System.Data
Imports System.Data.Common
Imports System.Data.SqlClientPartial Class _DefaultInherits System.Web.UI.Page
Protected Sub CreateUserWizard1_CreatedUser(ByVal sender As Object, ByVal e As System.EventArgs) Handles CreateUserWizard1.CreatedUser
'Database ConnectionDim con As New SqlConnection("Data Source = .SQLExpress;integrated security=true;attachdbfilename=|DataDirectory|ASPNETDB.mdf;user instance=true")
'First Command DataDim Company As String = ((CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Company"), TextBox)).Text)
Dim insertSQL1 As StringDim currentUserID As String = ((CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("UserName"), TextBox)).Text)
insertSQL1 = "INSERT INTO Company (CompanyName, UserID) VALUES ('Company', 'currentUserID')"Dim cmd1 As New SqlCommand(insertSQL1, con)
'2nd Command Data
Dim selectSQL As String
selectSQL = "SELECT companyKey FROM Company WHERE UserID = 'currentUserID'"Dim cmd2 As New SqlCommand(selectSQL, con)
Dim reader As SqlDataReader
'3rd Command Data
Dim insertSQL2 As String
insertSQL2 = "INSERT INTO Company_Membership (CompanyKey, UserID) VALUES ('CompanyKey', 'currentUserID')"Dim cmd3 As New SqlCommand(insertSQL2, con)
'First CommandDim added As Integer = 0
Try
con.Open()
added = cmd1.ExecuteNonQuery()
lblResults.Text = added.ToString() & " records inserted."Catch err As Exception
lblResults.Text = "Error inserting record."
lblResults.Text &= err.Message
Finally
con.Close()
End Try
'2nd Command
Try
con.Open()
reader = cmd2.ExecuteReader()Do While reader.Read()
Dim CompanyKey = reader("CompanyKey").ToString()
Loop
reader.Close()Catch err As Exception
lbl1Results.Text = "Error selecting record."
lbl1Results.Text &= err.Message
Finally
con.Close()
End Try
'3rd Command
Try
con.Open()
added = cmd3.ExecuteNonQuery()
lbl2Results.Text = added.ToString() & " records inserted."Catch err As Exception
lbl2Results.Text = "Error inserting record."
lbl2Results.Text &= err.Message
Finally
con.Close()End Try
End Sub
End Class
View 3 Replies
View Related
Dec 17, 2014
I have a string 'ACDIPFJZ'
In my table one of the column has data like
PFAG
ABCDEFHJMPUYZ
KML
JC
RPF
My requirement is that if the string in the column has any of the characters from 'ACDIPFJZ' , those characters have to be retained and the rest of the characters have to be removed.
My output should be:
PFAG -- PFA (G Eliminated)
ABCDEFHJMPUYZ -- ACDPFJZ (B,E,H,M,U,Y Eliminated)
KML -- No data
JC -- JC
RPF -- PF (R Eliminated)
View 2 Replies
View Related
Jul 14, 2015
I have a text field which has entries of variable length of the form:
"house:app.apx&resultid=1234,clientip"
or
"tost:app.apx&resultid=123,clientip"
or
"airplane:app.apx&resultid=123489,clientip"
I'm trying to pick out the numbers between resultid='...',clientip no matter what the rest of the string looks like. So in this example it would be the numbers:
1234
123
12389
the part of the string of the form resultid='...',clientip always stays the same except the length of the number can vary.
View 5 Replies
View Related
Sep 11, 2006
Hello All,
I'm a non-programmer and an SQL newbie. I'm trying to create a printer usage report using LogParser and SQL database. I managed to export data from the print server's event log into a table in an SQL2005 database.
There are 3 main columns in the table (PrintJob) - Server (the print server name), TimeWritten (timestamp of each print job), String (eventlog message containing all the info I need). My problem is I need to split the String column which is a varchar(255) delimited by | (pipe). Example:
2|Microsoft Word - ราย�ารรับ.doc|Sukanlaya|HMb1_SD_LJ2420|IP_192.10.1.53|82720|1
The first value is the job number, which I don't need. The second value is the printed document name. The third value is the owner of the printed document. The fourth value is the printer name. The fifth value is the printer port, which I don't need. The sixth value is the size in bytes of the printed document, which I don't need. The seventh value is the number of page(s) printed.
How I can copy data in this table (PrintJob) into another table (PrinterUsage) and split the String column into 4 columns (Document, Owner, Printer, Pages) along with the Server and TimeWritten columns in the destination table?
In Excel, I would use combination of FIND(text_to_be_found, within_text, start_num) and MID(text, start_num, num_char). But CHARINDEX() in T-SQL only starts from the beginning of the string, right? I've been looking at some of the user-defind-function's and I can't find anything like Excel's FIND().
Or if anyone can think of a better "native" way to do this in T-SQL, I've be very grateful for the help or suggestion.
Thanks a bunch in advance,
Chutikorn
View 2 Replies
View Related
Sep 1, 2015
Is there way to search for the particular string and return the string after that searched string
SalesID
Rejection reason
21812
[code]....
The timeout period elapsed hence disqualified
View 3 Replies
View Related
Mar 21, 2007
I am trying to find a way to find a certian character in a string and then select everything after that character.
for example i would look for the position of the underscore and then need to return everthing after it so in this case
yes_no
i would return no
View 7 Replies
View Related
Jul 13, 2006
I have a nasty situation in SQL Server 7.0. I have a table, in whichone column contains a string-delimited list of IDs pointing to anothertable, called "Ratings" (Ratings is small, containing less than tenvalues, but is subject to change.) For example:[ratingID/descr]1/Bronze2/Silver3/Gold4/PlatinumWhen I record rows in my table, they look something like this:[uniqueid/ratingIDs/etc...]1/2, 4/...2/null/...3/1, 2, 3/...My dilemma is that I can't efficiently read rows in my table, match thestring of ratingIDs with the values in the Ratings table, and returnthat in a reasonable fashion to my jsp. My current stored proceduredoes the following:1) Query my table with the specified criteria, returning ratingIDs as acolumn2) Split the tokens in ratingIDs into a table3) Join this small table with the Ratings table4) Use a CURSOR to iterate through the rows and append it to a string5) Return the string.My query then returns...1/"Silver, Platinum"2/""3/"Bronze, Silver, Gold"And is easy to output.This is super SLOW! Queries on ~100 rows that took <1 sec now take 12secs. Should I:a) Create a junction table to store the IDs initially (I didn't thinkthis would be necessary because the Ratings table has so few values)b) Create a stored procedure that does a "SELECT * FROM Ratings," putthe ratings in a hashtable/map, and match the values up in Java, sinceJava is better for string manipulation?c) Search for alternate SQL syntax, although I don't believe there isanything useful for this problem pre-SQL Server 2005.Thanks!Adam
View 2 Replies
View Related
Jul 28, 2015
I have a string variable and following data.
Declare @ServiceID varchar(200)
set @ServiceID='change chock','change starter','wiring for lights','servicing'
when I assign values in @ServiceID Â in the above manner then it shows error. How to get string array in @ServiceID variable so that i can go ahead.
View 8 Replies
View Related
Jan 7, 2008
Hey, ive got a listof events that have occured on my site, updates etc. and Im trying to show how long ago the updates happened. For instance, say the date of an update is : 16/10/2007 15:16:03 I want the Label to say "Happened over 2 months ago" etc.Now ive tryed to use an IF statment but I cant seem to get it right : DateTime dt = Convert.ToDateTime("16/10/2007 15:16:03"); if(dt.ToShortDateString() == DateTime.Now.ToShortDateString()) { //happened today if(dt.ToShortTimeString() == DateTime.Now.ToShortTimeString()||dt.ToShortTimeString() < DateTime.Now.AddMinutes(-1).ToShortTimeString()) { //happened within a minute UpdateLabel.Text = "About A Minute Ago"; } } else if (dt.ToShortTimeString() == DateTime.Now.AddDays(-1).ToShortTimeString()) { //happened yesturday UpdateLabel.Text = "Updated Yesturday"; } else if (dt.ToShortDateString() == DateTime.Now.ToShortDateString() || dt.ToShortDateString <= DateTime.Now.AddDays(-7).ToShortDateString()) { UpdateLabel.Text = "Updated Last Week"; }Any ideas where Im going wrong? Im probally staring it straight in the face, but I cant see it. Thanks in advance John
View 1 Replies
View Related
Aug 10, 2001
SQL 7.0 SP1 - NT 4.00 EE SP6a - Cluster - Multiprocessor(2)-VB 6.0 SP4.
Maybe a very stupid question,but I need to know if it is possible to assign a low priority to a VB exe that query for long time eating all the CPU (100% in task manager).
Thank you.
Franco
View 2 Replies
View Related
Jul 18, 2000
I have a challenge and am not very versed with coding this situation. The situation is I am trying to put long text (more that 255 char) in a ntext field in SQL7, but the max char can input is only 255. Anyone can advice on how to put in more that 255 char in a field as well as how to select back the record whice more than 255 char.
Please give advice if you encounter this problem before.
Regards.
View 2 Replies
View Related
Jul 24, 2000
Anyone have same sample on how to put in and retrive long text (more then 255 characters).
Please Comment.
------------
Ray Miao at 7/19/00 8:24:08 AM
You should use readtext and writetext to access text column. By the way, nchar and nvarchar can hold up to 4000 characteres.
------------
Wong at 7/18/00 11:43:15 PM
I have a challenge and am not very versed with coding this situation. The situation is I am trying to put long text (more that 255 char) in a ntext field in SQL7, but the max char can input is only 255. Anyone can advice on how to put in more that 255 char in a field as well as how to select back the record whice more than 255 char.
Please give advice if you encounter this problem before.
Regards.
View 1 Replies
View Related
Mar 2, 2000
How long exactly is the text datatype in non-unicode characters for SQLS7? I guess I need to find out how long my input is and break it up into multiple text fields, but what's the max length?
View 3 Replies
View Related
Mar 6, 2001
I have created a database which has a table whose name is 128 characters long; this table has a column whose name is 128 characters long. When I issue the command sp_helpconstraint table_name, SQL Server 7 returns the message "string or binary data would be truncated."
Any suggestions?
Thanks!
View 1 Replies
View Related
Sep 22, 2000
Hi
When writing a stored procedure,sometimes the line of sql code could be very long and be more than 128 caracthers.
I got an error message saying that the code is vey long and the maximum is 128.
But at the same time, this is erratic in MSSQL server because it works sometimes even with a line of code of more than 800 caracters...
Have you ever experienced the same problem??
What is the solution?
Thanks
View 1 Replies
View Related
