Handling Large Data Values In Textboxes
Mar 28, 2007
Hi, Ive got a report using a List item that is vertically displaying the columns from a table. The problem I run into, is that some of the fields in this table contain large blocks of text where the users have entered comments and such.
I am using Textboxes to display this data.
So my report will look something like
-----
Field label 1 Field value 1
Field label 2 Field value 2
Field label 3
<white space>
<page break>
Field value 3 ---> this is a big block of text
Field label 4 Field value 4
etc
------
It appears as though the report attempts to keep the contents of each textbox together even if that means breaking onto an entirely new page to do this. I would prefer for the data to flow more natrually instead where the page breaks in the middle of the data being displayed should it be too large to fit on the page it started on.
-----
Field label 1 Field value 1
Field label 2 Field value 2
Field label 3 Field value 3 --- As much as can fit on this page
<page break>
Field value 3 ---> remaining data that broke over the page
Field label 4 Field value 4
etc
------
Any suggestions would be apprecaited.
View 3 Replies
ADVERTISEMENT
Jul 20, 2005
I have a problem trying to get a value from textbox 1 and textbox 2.If those two values match the data in the database then it has toreturn a third corresponding value to a third textbox (or label) aftera user clicks a submit button. Basically, I am trying to allow usersto enter a value of weight in pounds in textbox 1 and a zip code intextbox 2 then pulling the shipping amount from a courier's databaseand returning that dollar amount to a third textbox or label afterthey click submit. How would I write this code in vb.NET?Thanks,TG
View 1 Replies
View Related
Oct 9, 2007
Hello, my problem is that I have 2 textboxes and when the user writes somthing and clicks the submit button I want these values to be stored in a database table. I am using the following code but nothing seems to hapen. Do I have a problem with the Query (Insert)??? Or do I miss something else. Please respond as I can't find this.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Manufacturer2.aspx.cs" Inherits="Manufacturer2" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server"><title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Label ID="Label1" runat="server" Text="Name"></asp:Label>
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br />
<asp:Label ID="Label2" runat="server" Text="Password"></asp:Label>
<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
<br />
<br />
<asp:Button ID="Button1" runat="server" Text="Submit" OnClick="Button1_Click" />
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="INSERT INTO Manufacturer(Name, Password) VALUES (@TextBox1, @TextBox2)">
<SelectParameters>
<asp:ControlParameter ControlID="TextBox1" Name="TextBox1" PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox2" Name="TextBox2" PropertyName="Text" />
</SelectParameters>
</asp:SqlDataSource>
</div></form>
</body></html>
View 10 Replies
View Related
Jan 9, 2008
Hi. I have an sql query that i use to insert articles to a sql databse table and for that i use addWithValue to select witch textboxes etc goes where in the database.
Now i need to retrive these values and place them back into some other textboxes (some of them multiline) ,and i wonder if there are any similar ways to retrive values like AddWithparameter so i can easily do textBox.text = //whatever goes here ?
View 4 Replies
View Related
Apr 28, 2008
hello,
I have the following and what to do SUM the valueS in each in another textbox, what is the proper method?
=count(Fields!Month_Submitted.Value, "SALES")
=count(Fields!Month_Submitted_1.Value, "SALESDIR")
=count(Fields!Month_Submitted_2.Value, "SALESVP")
=count(Fields!Month_Submitted_3.Value, "CREDIT")
Thanks,
Rhonda
View 8 Replies
View Related
May 7, 2007
I have to Lists filtered by the same field. One List shows all records with the field IsClokedIn value = TRUE and the other list =FALSE. I have only one dataset that retrieve the ClockIn and Clock out for one user. In a perfect world, the user MUST clock in and always clock out. So, when i created my dataset i ordered by UserClockingID (whick is primary key). So, this MUST retrieve a dataset with alternate records IN / OUT (TRUE or FALSE)
Ex.
UserClokingID UserID Time IsClockedIN
2323 34 8:32:03 TRUE
2324 34 12:07:02 FALSE
2325 34 13:05:03 TRUE
2326 34 14:53:02 FALSE
2327 34 15:10:03 TRUE
2328 34 17:32:02 FALSE
All i need is to calculate (sum then divide by 60) the minutes returned by the method DateDiff between the first row in my TRUE filtered list and the first row in my FALSE filetered list... and so on for the 2nd, 3rd ..... I can get the minutes by taking the values directly from the Fields!IsClockedIn.Value and using a condition to "grab" the previous record if the current record ClockedIn.Value=FALSE. I already did that.
my problem is that when i try to perform a calculation of textboxes values(those with the ammount of minutes for every pair of records from the datalist) out of the group. It says you can only do that inside the group or dataregion.
Is there any work around?
I really need to get this solved.
View 2 Replies
View Related
May 7, 2015
In my SSRS report i have a date parameter and i want to set to it a default value with a complicated logic. I arrived at this strange behaviour:
Today is May 6th, wednesday. If i use the following expression:
DateAdd("d",Weekday(Today(),DayOfWeek.Sunday),Today())
for the default time picker I get May 9th. If I use exactly the same expression in a textbox in the same report
DateAdd("d",Weekday(Today(),DayOfWeek.Sunday),Today()).ToLongDateString()
I get May 10th! The only thing that changes is the toString. Why are the two values different? I tried with different expressions and the difference arises when i start using Weekday(Today(), somevalue).
The result of Weekday should be independent from regional settings as I am forcing Sunday to be the first day of the week, right?
View 2 Replies
View Related
Apr 7, 2008
Lets say you want to offer users access to a table that has over 1,000,000 records in it. You dont want to fetch the entire table into a dataset. Even with paging, it will still cause a performance lag, will it not ?Lets say there was a column that held last names. You could then divide up all the data in to alphabetical groups by letter. That will at least let you split up the data 26 different ways (letters in teh alphabet). Thats still roughly 38,000 possible records in the dataset at any given time. I suppose since the database and web server are very close to each other, physically, the latency shouldnt be too bad. However, Im worried about having several dozen or more users, each requesting datasets with tens of thousands of records. That would cause the memory use on the web server to spike most likely. Right now, its just one user do this sort of processing, but that might grow to include several dozens.ideas ?
View 3 Replies
View Related
Jan 29, 2008
Hi I have created one store procedure which handles global updates I am using cursor to fetch one be one row for updating (It is required for implementing business logic)Now when i execute this store procedure ---it gives me dedlock error , I dont know why i m getting this error(Approx number of rows 1.5lakh)if then i removed unnecessary records from table (Approx -50000) it works fine,Is there any way to handle itI am calling this storeprocedure from my window service.please give me a good solution if possible
View 3 Replies
View Related
Jul 13, 2004
In my Java application, I have a stream of character data in a java.io.Reader object.
I am using a PreparedStatement object to insert data into a table containing such a large object column (datatype - text). I am using the following API call:
PreparedStatement.setCharacterStream(colIndex, reader, size);
In order to find the size in the above statement, I read the stream and find the length.
Because of this I am getting the following error message and the data is not getting inserted:
Exception during insertion : Failed for MYTABLE Reason [Microsoft][SQLServer 2000 Driver for JDBC]Transliteration failed.
Is there any alternate method to handle this? Please help.
View 1 Replies
View Related
May 20, 2008
Hello,
We have created several Table Valued User Defined Functions in a Production SQL Server 2005 DB that are returning large (tens of thousands of) rows obtained through a web service. Our code is based on the MSDN article Extending SQL Server Reporting Services with SQL CLR Table-Valued Functions .
What we have found in our implementations of variations of this code on three seperate servers is that as the rowset grows, the length of time required to return the rows grows exponentially. With 10 columns, we have maxed out at approximately 2 500 rows. Once our rowset hit that size, no rows were being returned and the queries were timing out.
Here is a chart comparing the time elapsed to the rows returned at that time for a sample trial i ran:
Sec / Actual Rows Returned
0 0
10 237
20 447
30 481
40 585
50 655
60 725
70 793
80 860
90 940
100 1013
110 1081
120 1115
130 1151
140 1217
150 1250
160 1325
170 1325
180 1430
190 1467
200 1502
210 1539
220 1574
230 1610
240 1645
250 1679
260 1715
270 1750
280 1787
290 1822
300 1857
310 1892
320 1923
330 1956
340 1988
350 1988
360 2022
370 2060
380 2094
390 2094
400 2130
410 2160
420 2209
430 2237
440 2237
450 2274
460 2274
470 2308
480 2342
490 2380
500 2380
510 2418
520 2418
530 2451
540 2480
550 2493
560 2531
570 2566
It took 570 seconds (just over 9 1/2 minutes to return 2566 rows).
The minute breakdown during my trial is as follows:
1 = 655 (+ 655)
2 = 1081 (+ 426)
3 = 1325 (+244)
4 = 1610 (+285)
5 = 1822 (+212)
6 = 1988 (+166)
7 = 2160 (+172)
8 = 2308 (+148)
9 = 2451 (+143)
As you can tell, except for a few discrepancies to the resulting row count at minutes 4 and 7 (I will attribute these to timing as the results grid in SQL Management Studio was being updated once every 5 seconds or so), as time went on, fewer and fewer rows were being returned in a given time period. This was a "successful" run as the entire rowset was returned but on more than several occasions, we have reached the limit and have had 0 new rows per minute towards the end of execution.
Allow me to explain the code in further detail:
[SqlFunction(FillRowMethodName = "FillListItem")]
public static IEnumerable DiscoverListItems(...)
{
ArrayList listItems = new ArrayList();
SPToSQLService service = new SPToSQLService();
[...]
DataSet itemQueryResult = service.DoItemQuery(...); // This is a synchronous call returning a DataSet from the Web Service
//Load the DS to the ArrayList
return listItems;
}
public static void FillListItem(object obj, out string col1, out string col2, out string col3, ...)
{
ArrayList item = (ArrayList) obj;
col1 = item.Count > 0 ? (string) item[0] : "";
col2 = item.Count > 0 ? (string) item[1] : "";
col3 = item.Count > 0 ? (string) item[2] : "";
[...]
}
As you will notice, the web service is called, and the DataSet is loaded to an ArrayList object (containing ArrayList objects), before the main ArrayList is returned by the UDF method. There are 237 rows returned within 10 seconds, which leads me to believe that all of this has occured within 10 seconds. The method GetListItems has executed completely and the ArrayList is now being iterated through by the code calling the FillListItem method. I believe that this code is causing the result set to be returned at a decreasing rate. I know that the GetListItems code is only being executed once and that the WebService is only being called once.
Now alot of my larger queries ( > 20 000 rows) have timed out because of this behaviour, and my workaround was to customize my web service to page the data in reasonable chunks and call my UDF's in a loop using T-SQL. This means calling the Web Service up to 50 times per query in order to return the result set.
Surely someone else who has used Table Valued UDFs has come accross this problem. I would appreciate some feedback from someone in the know, as to whether I'm doing something wrong in my code, or how to optimize an SQL Server properly to allow for better performance with CLR functions.
Thanks,
Dragan Radovic
View 7 Replies
View Related
Jan 29, 2008
Im trying to display data from a database based on an input value. The value in the Label12.Text which is("hotmail") is the input value thats stored in the database, this value is been searched for in the strSQL.
Dim strSQL As String = "SELECT Name FROM Jobseeker WHERE Email='" & Label12.Text & "' "
strSQL = Label5.Text
the code builds successfully, but Label5.Text appears blank.
View 2 Replies
View Related
Dec 1, 2007
I am trying to set a variable to be passed to my asp page but the value is not carrying over. I don't even know if this is supposed to work or not below:
<td colspan="3" style="font-size: 10pt; vertical-align: top; color: <%=Div7fontcolor%>; background-color: <%=Div7bgcolor%>; text-align: center; width: 0px;" id="TopBox" runat="server">
The above Div7fontcolor and Div7bgcolor are variables that I set in the VB file as shown below:
Dim obConnection As SqlConnection = New SqlConnection("Data Source=localhostsqlexpress;Initial Catalog=OrgBoard;Integrated Security=True")Dim obCommand As SqlCommand = New SqlCommand("SELECT Divisions.*, Dept19.*, Dept20.*, Dept21.*, ConfigDisplay.* FROM Divisions CROSS JOIN Dept19 CROSS JOIN Dept20 CROSS JOIN Dept21 CROSS JOIN ConfigDisplay", obConnection)
obConnection.Open()Dim dr As SqlDataReader = obCommand.ExecuteReader()
dr.Read()
Dim Div7bgcolor As String = dr("Div7color").ToString().Trim()Dim Div7fontcolor As String = dr("Div7textcolor").ToString().Trim()
dr.Close()
obConnection.Close()
The web page runs fine with no errors but the value does not carry over and change the property correctly.
I am able to set the bgColor value of the TopBox within the VB file if I use "TopBox.BgColor = Div7bgcolor" but I can't set other values on my web page like the font color. If I can do this from the VB file then please correct me. Using variables within the page will allow me to set almost any value but I don't even know if what I want is possible. Any help is greatly apprectiated.
Thank you,
Kris
View 1 Replies
View Related
Oct 19, 2007
Hi,
I would like to know the solution for the below problem.
Actually I have a single Hibernate xml file which has the query on it. And I am accessing the xml file for SQL server 2005 and Oracle 10G. But the query is working in Oracle and not working in SQl Server.
I noticed from the query that handling 'NULL' is the problem.
Findings:
1. The following query is the same in both Oracle and SQL Server, which is causing the Issue.
SELECT *
FROM CLIENT_SKU AS o
WHERE o.OU_ID = 20000000
AND ((o.CLIENT_SKU_START_CODE <= 'wwwww' AND o.CLIENT_SKU_END_CODE >= 'wwwww')
OR (o.CLIENT_SKU_START_CODE <= '' AND o.CLIENT_SKU_END_CODE >= '')
OR (o.CLIENT_SKU_START_CODE >= 'wwwww' AND o.CLIENT_SKU_END_CODE < '')
OR (o.CLIENT_SKU_START_CODE = 'wwwww' AND o.CLIENT_SKU_END_CODE is null))
CAUSE:
Oracle treats NULL and the empty string almost the same making the latter almost an alias for NULL
The following two statements produce identical results:
1. update employee set lastname = ''
2. update employee set lastname = NULL
In our case, the CLIENT_SKU_END_CODE is an empty string which is being checked with an empty string returns False in Oracle where as returns True in Sql Server causing the issue.
It would be great if any one who can give some possible solution to resolve the same.
Thnx
View 3 Replies
View Related
Jun 30, 2000
I would like some suggestions on how to keep from displaying the value "1/1/1900" on my asp pages when my recordset field returns a Sql date value that is null
Thanks for any tips
Jim
View 1 Replies
View Related
Jun 9, 2006
I am writing a stored procedure that needs a access individual entries in a column with multiple entries delimited by a comma(yeah i know, not 1st NF) . Like this:
Key
NotANormalizedCol
1
1324, 5124, 5435,5467
2
423, 23, 5345
3
52334, 53443, 1224
4
12, 4, 1243,66
is there a function that returns a substring given a delimiter character? the only substring returning function that i found are the LEFT and RIGHT that returns fixed length substring.
I am pretty new to this, so I apologize if this is a trivial questions
View 3 Replies
View Related
Apr 9, 2008
HI
I need help
how can i fill data in textboxes from sql databases but two different tables when i select a name that is inside a dropdownlist
my controls are as follows
<asp:DropDownList ID="ddl" runat="server" DataSourceID="SqlDataSource13" DataTextField="fullname" DataValueField="fullname">
</asp:DropDownList>
<asp:SqlDataSource ID="SqlDataSource13" runat="server" ConnectionString="<%$ ConnectionStrings:NPI Employee MasterConnectionString2 %>"
SelectCommand="SELECT [FirstName]+' '+ [Surname] as fullname FROM [Employee] where CurrentEmployee_YN=1 order by FirstName "></asp:SqlDataSource><table bordercolor="#111111" cellpadding="0" cellspacing="0" style="width: 100%;
border-collapse: collapse; height: 32px; visibility: hidden;" id="table0">
<tr>
<td style="width: 159px; visibility: hidden;">
</td>
<td style="width: 170px">
</td>
<td bgcolor="#eeeddb" style="width: 20%; height: 25px">
<strong>
Order No：</strong></td>
<td bgcolor="#eeeddb" style="width: 26%; height: 25px">
<asp:Label ID="OrderNo" runat="server" Width="104px"></asp:Label></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 159px; height: 25px">
<strong>
Account No：</strong></td>
<td bgcolor="#eeeddb" style="width: 170px">
<asp:TextBox ID="AccountNo" runat="Server" MaxLength="10" Width="130px"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator6" runat="server" ControlToValidate="AccountNo"
Display="Static" ErrorMessage="Enter Acc No." Text="*"></asp:RequiredFieldValidator></td>
<td bgcolor="#eeeddb" style="width: 20%; height: 25px">
<strong>
Today's Date:</strong></td>
<td>
<asp:Label ID="Label1" runat="server" Text="Label" Width="200px"></asp:Label></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 159px; height: 25px">
<strong>
Travel Consultant：</strong></td>
<td bgcolor="#eeeddb" style="width: 170px">
<asp:TextBox ID="Consultant" runat="Server" MaxLength="30" Width="128px"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator7" runat="server" ControlToValidate="Consultant"
Display="Static" ErrorMessage="Enter Travel Consultant." Text="*"></asp:RequiredFieldValidator></td>
</tr>
</table>
<center>
</center>
<center>
</center><table bordercolor="#111111" cellpadding="0" cellspacing="0" style="width: 80%;
border-collapse: collapse; height: 32px; display: block; visibility: hidden;" id="table2">
<tr>
<td align="center" bgcolor="#ffcc33" colspan="3" style="width: 90%; height: 29px">
<font color="#000000" size="5">Enter Passenger(s) Details</font></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 25px">
<strong>
Surname：</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<asp:TextBox ID="Surname" runat="Server" MaxLength="30" Width="148px"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"
ControlToValidate="Surname" Display="Static" ErrorMessage="Enter Surname." Text="*"></asp:RequiredFieldValidator></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 20px">
<strong>
Name：</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 20px">
<asp:TextBox ID="Name" runat="Server" MaxLength="30" Width="148px"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="Name"
Display="Static" ErrorMessage="Enter Name." Text="*"></asp:RequiredFieldValidator></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 25px">
<strong>
Initials：</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<asp:TextBox ID="Initials" runat="Server" MaxLength="5" Width="148px"></asp:TextBox>
</td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 25px">
<strong>
Title：</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<asp:DropDownList ID="DropDownList1" runat="server" Width="156px">
<asp:ListItem></asp:ListItem>
<asp:ListItem Value="Mr"></asp:ListItem>
<asp:ListItem Value="Mrs"></asp:ListItem>
<asp:ListItem Value="Ms"></asp:ListItem>
<asp:ListItem Value="Dr"></asp:ListItem>
<asp:ListItem Value="Prof"></asp:ListItem>
<asp:ListItem Value="Min"></asp:ListItem>
<asp:ListItem Value="Other"></asp:ListItem>
</asp:DropDownList>
<asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ControlToValidate="Dropdownlist1"
Display="Static" ErrorMessage="Select Title." Text="*" Width="20px"></asp:RequiredFieldValidator>
</td>
</tr>
<tr><td bgcolor="#eeeddb">
<strong>
Department</strong>
</td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<asp:TextBox ID="Department" runat="server"></asp:TextBox></td>
</tr>
<tr><td bgcolor="#eeeddb">
<strong>
Cost Centre</strong>
</td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<asp:TextBox ID="CostCentre" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 25px">
<strong>
Tel：</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<input id="Tel" runat="SERVER" maxlength="15" name="Tel" onkeypress="if((event.keyCode<48)|| (event.keyCode>57))event.returnValue=false"
style="width: 143px" type="text" />
</td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 25px">
<strong>
Fax：</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<input id="Fax" runat="SERVER" maxlength="15" name="Fax" onkeypress="if((event.keyCode<48)|| (event.keyCode>57))event.returnValue=false"
style="width: 143px" type="text" />
</td>
</tr>
</table>
cost centre and department are from cost table
and the rest are from employee table
View 10 Replies
View Related
Jan 21, 2008
Hi all,
I’m returning two values from a stored procedure, one is a basic string confirming that an email has been sent and the other is the normal value returned from running an INSERT statement. So in my code I’m using the ExecuteNonQuery() method. I’m not sure how to handle both returned values in my code in my data layer. This is what I have:
ExecuteNonQuery(cmd);
return Convert.ToString(cmd.Parameters["@ReturnedValue"].Value).ToLower();
Obviously I’d need to return the value returned by the ExecuteNonQuery method as well, normally I’d simply convert the value to an int and precede this with the return keyword like so:
return (int)ExecuteNonQuery(cmd);
Obviously I can’t do this as I need to return two values, the normal value returned by the ExecuteNonQuery() method and my own output parameter value. Any ideas how I can do both? My current method containing the code further above returns a string but clearly this doesn’t help. I’m guessing that maybe I should return an object array so I can return both values? I haven’t encountered this problem before so I’m just guessing. Please help.
Thanks
View 4 Replies
View Related
Sep 24, 2007
Can anyone let me know the prefered method for handling stored procedure errors. I usually trap the error in the stored proc and then return a value using an output parameter
e.g stored proc
if @@error <> 0 beginset @returnValue = -1returnend
c#com.Parameters.Add("@returnValue", SqlDbType.Int).Direction = ParameterDirection.Output;con.Open();com.ExecuteNonQuery();
int result = (int)com.Parameters[0].Value;
if (result == -1){//throw exception}else{//do whatever}
View 2 Replies
View Related
Oct 10, 2007
Hi all,
I have an existing database, which is used to store complaints data and have one field, which stores notes in a varchar(8000) column.
My users are now wanting to store information, which exceed the number of available characters. What options do I have in facilitating this?
Many thanks
View 5 Replies
View Related
Aug 2, 2007
HiThis is a question of "what does it cost me".Lets say I have an integer value which would fit into a smallint fieldbut the field is actually defined as int or even larger as bigint.What would that "cost" me ? How would definitions larger than I need forthe values in the field affect me ?Its obvious that the volume of the database would grow but with the sizeof resources etc that we have nowadays disc space isn't a problem likeit used to be and i/o is much faster and many people would tell me "whocares" , or IS it a problem ?How does it affect performance of data retrieves ? Searches ? Updatesand inserts ? How would it affect all db access if tables are pointingat each other with foreign keys ?Thanks !David Greenberg
View 3 Replies
View Related
May 23, 2014
Table definition:
Create table code (
id identity(1,1)
code
parentcode
internalreference)
There are other columns but I have omitted them for clarity.
The clustered index is on the ID.
There are indexes on the code, parentcode and internalreference columns.
The problem is the table stores a parentcode with an internalreference and around 2000 codes which are children of the parentcode. I realise the table is very badly designed, but the company love orms!!
Example:
ID| Code| ParentCode| InternalReference|
1 | M111| NULL | 1|
2 | AAA | M111 | 2|
3 | .... | .... | ....|
4 | AAB | M111 | 2000|
5 | M222 | NULL | 2001|
6 | ZZZ | M222 | 2002|
7 | .... | .... | .... |
8 | ZZA | M222 | 4000|
The table currently holds around 300 millions rows.
The application does the following two queries to find the first internalreference of a code and the last internal refernce of a code:
--Find first internalrefernce
SELECT TOP 1 ID, InternalReference
FROM code
WHERE ParentCode = 'M222'
Order By InternalReference
-- Find last ineternalreference
SELECT TOP 1 ID, InternalReference
FROM code
WHERE ParentCode = 'M222'
Order By InternalReference DESC
These queries are running for a very long time, only because of the sort. If I run the query without the sort, then they return the results instantly, but obviously this doesn't find the first and last internalreference for a parentCode.
I realize the best way to fix this is to redesign the table, but I cannot do that at this time.
Is there a better way to do this so that two queries which individually run very slowly, can be combined into one that is more efficient?
View 7 Replies
View Related
Apr 16, 2008
Hi every one,
I am facing problem in printing the reports from browser and also when i export it to pdf,the problem i am facing is blank pages are coming when report column getting the large amount of text around 2500 characters into column value.
can any one help me in this issue?. if the report is getting acceptable amout of data it is printing in proper way i.e no balnk pages at all.i maintained all properties like margins+body size < page size.
View 4 Replies
View Related
Jan 2, 2008
I have a peculiar requirement. I need the unmatched data to passed on a bad data table as well as tied to a dimension "Others". Please guide how to suffice these two condition simultaneously
View 1 Replies
View Related
Jul 25, 2007
I have an SSIS package that takes in a flat file and pushes the data into a table. However, once in a while the file will have some bad data in it (for example, this particular time I have too many delimiters on one line). I want the package to redirect the row to an error table and keep going with the processing for the rest of the data. To do this, I have hooked up an error output from the flat file source to an OLE DB destination and assigned all the rows to Redirect Row on Error in the Error Output section of the flat file source. Unfortunately, it does not work! The flat file receives an error and stops. Is this because something different has to happen when it is a problem with the whole row? Any help would be appreciated, thanks.
View 6 Replies
View Related
Feb 21, 2007
Hi,
I want to fetch Unicode data from MS SQL server 2005.I have created one user with russian language characters.
Following is my code snippet:
------------------------------------
wchar_twchar1[55];
char VALUE[255];
while (rc != SQL_NO_DATA)
{
rc = SQLFetch(hstmt);
if (rc != SQL_NO_DATA)
memset(VALUE,0,255);
int length=wcstombs( NULL,(wchar_t *)&wchar1, wcslen(wchar1));
char* strChar = (char *) malloc((length +1 )* sizeof(char));
if (strChar != NULL)
{
count++;
memset(strChar,0, length + 1 );
int i =wcstombs(strChar, wchar1, wcslen(wchar1) );
printf( "i is %d",i);
}
printf( "The length is %d",length);
wprintf(L" <%s>", wchar1);
printf(" simple char <%s>", strChar);
wprintf(L" simple char <%S>", wchar1);
}
-----------------------------------
CAn anybody help me ?
I think problem is in fetching of name.
Does anybody have working code for fetching unicode data from SQL server 2005.
Also I want query that will tell the locale setting of the server and also client?
Thanks-----
View 4 Replies
View Related
Apr 18, 2007
Hi Guys,
I am facing problems with concurrent access in SQL Server 2000,The scenario is that the DB contains one huge de-normalized table containing 40 million records.
The application frequently queries this table to populate other derived tables,the sql queries take a long time to return results.
So if one query is in execution the other user's query goes into a
wait mode.Please suggest how I can better this.
Or do I need to upgrade to 2005.
Regards,
Prashant
View 2 Replies
View Related
May 2, 2007
I have a need to read a table and extract records that matches a criteria (dough!, not that simple). Then I put the record in a text message and send them out via email. Works OK, but the problem is when I have multiple records for one entry. Since SQL doesn't have arrays "per say", I need to load the records on a "temporary array" then assembly the message at the end to include the records. Any suggestions how to "emulate" an array on a stored procedure (SQL), not sure what direction to follow. thanks
View 2 Replies
View Related
Mar 11, 2008
Hi, I have source data in encrypted format. How should i handle it in SSIS?
I have found no information for such situation.
Anybody have any idea about it.
Bhakti
View 2 Replies
View Related
Oct 3, 2007
Hi,
i have a package that uses a ForEach loop component to import flat files, and uses an OLE DB Destination component to insert the data into some staging tables (using table fast load with a max insert commit of 1000 rows), the biggest individual table import would be circa 5000 rows. At the end of each file import a stored proc is called to transfer the data into production tables, then the next file is imported.
Periodically (when importing more than 5 or 6 files) the process fails with the error message:
The transaction log for database 'blah' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
This occurs when committing the data to staging. I do not start any transactions during this process, and the staging tables are cleaned out by truncating them, so i'm not sure exactly what is causing the log file to fill up (i'm not a DBA).
I know this is not specifically a SSIS problem, but can anyone give me a suggestion about the best way to handle the log file during an SSIS import? Should i execute a DBCC SHRINKFILE before each flat file is imported? Is there some other approach i should be taking to either insert the data or to move it from staging to production?
Thanks!
View 3 Replies
View Related
Oct 4, 2015
I am studying indexes and keys. I have a table that has a fixed width of data to be loaded in the first column which is parsed in a view based on data types within the fixed width specifications.
Example column A:
(name phone house cost of house,zipcodecountystatecountry)
-a view will later split this large varchar string based
column b: is the source filename of the data load (varchar 256)
....
a. would there be a benefit of adding a clustered or nonclustered index (if so which/point in direction on why)
b. is there benefit of making one of these two columns a primary key (millions of records) or for adding a 3rd new column as a pk?
c. view: this parses the data in column a so it ends up looking more like "name phone house cost of house zipcode county state country" each having their own column.
-any pros/cons of adding indexes (if so which) to the view instead of the tables or both for once the data is parsed?
View 4 Replies
View Related
Jun 21, 2001
I'm trying to convert a column from a varchar(14) to float, some of the rows contain some non-numeric characters. I've tried to write a cursor to step through the records, run cast(col1 as float) on each record, then use if @@error <> 0 to capture an error but it doesn't work. It steps through fine but when the cast command fails it ends the execution of the script with "Error converting data type varchar to float."
How can i capture this error without having the script fail? Is there another way to get this done?
Thanks,
Jim
View 1 Replies
View Related
Nov 23, 2005
Hi guys,Got a problem now :( please help...now we got a project handling records saved in a table in a sql2000(will upgraded to 2005 soon) server. every month around a millionrecords will be inserted.now user raised a request, that is, once criterios are matched, theproject should do some backend handle, for example, ifSELECT colID, fieldC, fieldD FROM dataTable WHERE fieldA ='fieldA_valueB'returns some recordset, for each @fieldC, @fieldD we shall do someback-end trick, maybeUPDATE dataTable SET fieldE = 'fieldE_valueF' WHERE fieldC = @fieldCAND fieldD = @fieldDlet's say such a rule is named as rule01. Hope I'm expressing theproblem clearly?my questoin is, shall we do this in Database side, using triggers, orby informing our .net project to do it?1. since the records are coming around 1 millison per month, how can wehandling the performance issue?2. now the rules are still somewhat simple, seems at least we could doit by EXEC or SP_EXECUTESQL. but rules may turn quite complex, forexample, audit log, or more complex issues, shall we do it in .netprogram? but how can a trigger in SQL database informing a .netprogram, or webservice, or windows service? by executeing an executableconsole program?3. user may raise more and more rules, how could we provied a flexiblesolution? i mean we're trying to build it less hard-coded. seems in sqldatabase EXEC or SP_EXECUTESQL are still somewhat flexible, while in..net to do something like eval() in javascript or EXEC in sql server isa little bit troublesome. but, put all these bussiness logic in storedprocedure sounds a little bit weired.guys, hope i have made myself clear. any suggestion? Thanks very mcuhyours,athos.
View 7 Replies
View Related
