SQL,tables in a DB: row

Showing posts with label row. Show all posts

Tuesday, March 20, 2012

"Views"(Edited)

Hai,
I have a table named "Student" which contains four columns and three rows.I want to see just the first row only from the "Student" table and the marks should be calculated.
Here is my table
sno smark1 smark2 smark3
1 60 60 60
2 70 70 70
3 90 90 90

The Ouput Should be:
sno smarks(smark1+ smark2+smark3)
1 180
2 210
3 270

I can't get ur question!!

What is the result u expecting from this table?

If you need only the first row,

Select Top 1 * from Student

|||

This would give you the first row based on just the sno. Depending on if you would want to see row 1 or 3 would be dependent on the order by. So to get sno[1] use "order by sno" alternately for the last sno[3] "order by sno desc".

Code Snippet

Select top 1 sno from student order by sno

Select top 1 sno from student order by sno desc

|||

You can do this by simply:

select sno, coalesce(smark1,0) + coalesce(smark2,0) + coalesce(smark3,0) as smarks

from yourTable

The coalesces are done to remove NULLS from the math.

In reality, it would be better if you build your table as:

studentMarks

============

studentNumber

sequenceNumber

dateOfScore

mark

Then you can sum any number of marks like this:

select studentNumber, sum(mark)

from studentMarks

group by studentNumber

sql

"Views"(Edited)

The Ouput Should be:
sno smarks(smark1+ smark2+smark3)
1 180
2 210
3 270

I can't get ur question!!

What is the result u expecting from this table?

If you need only the first row,

Select Top 1 * from Student

|||

Code Snippet

Select top 1 sno from student order by sno

Select top 1 sno from student order by sno desc

|||

You can do this by simply:

select sno, coalesce(smark1,0) + coalesce(smark2,0) + coalesce(smark3,0) as smarks

from yourTable

The coalesces are done to remove NULLS from the math.

In reality, it would be better if you build your table as:

studentMarks

============

studentNumber

sequenceNumber

dateOfScore

mark

Then you can sum any number of marks like this:

select studentNumber, sum(mark)

from studentMarks

group by studentNumber

"Views"

The Ouput Should be:
sno smarks(smark1+ smark2+smark3)
1 180
2 210
3 270

I can't get ur question!!

What is the result u expecting from this table?

If you need only the first row,

Select Top 1 * from Student

|||

Code Snippet

Select top 1 sno from student order by sno

Select top 1 sno from student order by sno desc

|||

You can do this by simply:

select sno, coalesce(smark1,0) + coalesce(smark2,0) + coalesce(smark3,0) as smarks

from yourTable

The coalesces are done to remove NULLS from the math.

In reality, it would be better if you build your table as:

studentMarks

============

studentNumber

sequenceNumber

dateOfScore

mark

Then you can sum any number of marks like this:

select studentNumber, sum(mark)

from studentMarks

group by studentNumber

Friday, March 16, 2012

"Row yielded no match during lookup" while there is no row going through the Lookup

Hi all,

I don't understand what's happening here.

I have a Conditional Split with 3 outputs. On the first output I have a lookup, when I execute the package I have 56 rows going through the Conditional Split, all rows are then going to the 2nd and 3rd output but the lookup on the first output generates an error "Row yielded no match during lookup".

I don't understand why the lookup is generating an error while there is no row going through it.

Any idea ?

Sbastien.

As an image worth more than a thousand words... http://www.mediamax.com/sebnunes/Links/98B4724720

|||

I have no idea why is that happening; but have you try to configure errors in the lookup task to redirect them? just to see if there is something going through it....just a thought

Rafael Salas

|||Yes I did, there is no row redirected in the Error output either :-\|||

Am I the only to have this behaviour ? I'm stuck here, how a transformation could raise an error while there is no row going through it ? It's nonsense isn't it ?

Any idea how to workaround this ?

|||I've found the problem, it's just the Designer which was messed up. I have 2 different lookup transformation with the same query and the designer was highlighting (in red in Debug Mode) one of the Lookup transformation as the one causing the error but in fact the error was happening in the other Lookup transformation.

"Row yielded no match during lookup" while there is no row going through the Looku

Hi all,

I don't understand what's happening here.

I don't understand why the lookup is generating an error while there is no row going through it.

Any idea ?

Sbastien.

As an image worth more than a thousand words... http://www.mediamax.com/sebnunes/Links/98B4724720

|||

I have no idea why is that happening; but have you try to configure errors in the lookup task to redirect them? just to see if there is something going through it....just a thought

Rafael Salas

|||Yes I did, there is no row redirected in the Error output either :-\|||

Am I the only to have this behaviour ? I'm stuck here, how a transformation could raise an error while there is no row going through it ? It's nonsense isn't it ?

Any idea how to workaround this ?

Sunday, March 11, 2012

"Other" grouping or Row aggregate

AS 2005, SP1:

I have a dimension with 15 members which I can filter the Top N members by measure.
What I would like to do is append an additional Row as a group or aggreagate the reads "Other" that reflects the remaining measure value. For example,

Top 5 Widgets

W5 5000
W9 4000
W2 3000
W11 2000
W7 1000
+Other 6000

The "Other" Row would sum the remaining 10 widgets so the user could see 100% of the totals.
I can do the Top N but have no idea how to start this one. Thanks in advance for your help.

-Troy

Reed Jacobson wrote up the standard solution to this problem on his blog recently:
http://sqljunkies.com/WebLog/hitachiconsulting/archive/2006/08/03/22345.aspx

In AS2K this could be optimised, and I wrote up how on my blog here:
http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!170.entry
... however, you'd need to test whether this was still a good idea in AS2005 - I think it might actually harm performance.

HTH,

Chris

|||Sorry for the delay. This was exactly what I what was looking for.

Thanks Reed.

"order by" in xquery

Given the following XML data in SQL Server (where each Employee is a separate rowset), is there a way to order by ID using XQuery?

Row 1<Employee><ID>3</ID></Employee>
Rpw 2<Employee><ID>1</ID></Employee>
Row 3<Employee><ID>2</ID></Employee>

Ex:

SELECT Employee.query('
for $emp in /Employee
order by $emp/ID
')

If you want to order multiple rows containing xml data you need to use SQL order by. XQuery order by is scoped to only one XML instance.

Assuming that table t has the Employee column, you can try something like this:

SELECT Employee

FROM t

ORDER BY Employee.value('(/Employee/ID)[1]', 'int')

|||Adrian, you are the man! Thanks!!

"Object reference not set to an instance of an object" error

Hi,guys
I met a problem, the error message is "Object reference not set to an instance of an object." ,which indicating the * row has this problem. I really don't understand, please help me!
Bellow is my code:

private void BindTypeData(){
string query = "select * from tblFacilityType";
sqlDataAdapter = new SqlDataAdapter(query,sqlConnection);
ds = new DataSet();
sqlDataAdapter.Fill(ds,"tblFacilityType");
* FacilityDataGrid.DataSource = ds.Tables["tblFacilityType"];
FacilityDataGrid.DataBind();
}

Thanks first!

feiMake sure that sqlConnection and FacilityDataGrid are instantiated somewhere else.|||hi,jason
I've checked, it is there. But i still have the same problem!

public class __FacilityType : System.Web.UI.UserControl
{
protected DataSet ds;
protected SqlConnection sqlConnection;
protected SqlDataAdapter sqlDataAdapter;
protected SqlCommand sqlSelectCommand;
protected SqlCommand sqlInsertCommand;
protected Panel FacilityTypePanel;
protected DataGrid FacilityTypeDataGrid;
protected SqlDataReader reader;

private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
if(!Page.IsPostBack){
BindTypeData();
}
}
//Todo: bind data for the facility list datagrid
private void BindTypeData(){
string query = "select * from tblFacilityType";
sqlDataAdapter = new SqlDataAdapter(query,sqlConnection);
ds = new DataSet();
sqlDataAdapter.Fill(ds,"tblFacilityType");
FacilityTypeDataGrid.DataSource = ds.Tables["tblFacilityType"];
FacilityTypeDataGrid.DataBind();
}|||Where and how are u instantiating sqlConnection?

"No data exists for the row/column" (InvalidOperationException) after a number of read

I've been looking around for some kind of known issue or something, but can't find anything. Here's what I'm experiencing:

I have a table with about 50,000 rows. I open several connections and use a command to ExecuteResultSet against each command, with CommandType.TableDirect, CommandText set to the name of the table, and IndexName set to various indexes. In the end, I have several SqlCeResultSet instances which are then maintained for the life of the AppDomain.

In a loop, I call SqlCeResultSet.Read() on one of the instances, and if it returns false, I call SqlCeResultSet.ReadFirst() - essentially creating a circular pass through the result set.

In a Visual Studio debug session, this approach goes swimmingly for a short time, and then after a successful Read(), I'm pegged with an InvalidOperationException (text: "No data exists for the row/column") for a column which was succesfully read on the previous Read(). If, in the immediate window, I call SqlCeResultSet.Read() again on the result set instance, the Get methods work as they had been in the previous reads.

It seems like the internal state of the ResultSet is getting corrupted somehow, but it is opaque to me. Any insights on why this suddenly throws this exception?

Well, the way around this, apparently, is to keep trying the operation until it succeeds. I have a governer on this repetition, and I've never had a problem with setting it to 5 - so it apparently succeeds after 5 or fewer retries, at least in my testing

|||

I got this error when I was using the SqlCeResultSet.Seek().
Use a breakpoint on this method to see what happens internally to the SqlCeResultSet
The values are restored once a read() is done, but after the seek and before the read is performed, the fields are unintelligible.
In other words a SqlCeResultSet.Seek() should always be followed up by a read of some kind.

"No data exists for the row/column" (InvalidOperationException) after a number of read

I've been looking around for some kind of known issue or something, but can't find anything. Here's what I'm experiencing:

In a loop, I call SqlCeResultSet.Read() on one of the instances, and if it returns false, I call SqlCeResultSet.ReadFirst() - essentially creating a circular pass through the result set.

It seems like the internal state of the ResultSet is getting corrupted somehow, but it is opaque to me. Any insights on why this suddenly throws this exception?

|||

"No data exists for the row/column" (InvalidOperationException) after a number of

I've been looking around for some kind of known issue or something, but can't find anything. Here's what I'm experiencing:

In a loop, I call SqlCeResultSet.Read() on one of the instances, and if it returns false, I call SqlCeResultSet.ReadFirst() - essentially creating a circular pass through the result set.

It seems like the internal state of the ResultSet is getting corrupted somehow, but it is opaque to me. Any insights on why this suddenly throws this exception?

|||

Thursday, March 8, 2012

"Keys lock row not found" error

Jens

I also am having trouble with the reporting services not being able to the report server databases. I get the error "Keys lock row not found". I can't find any citation to help me figure out what this means nor how to fix it. Any help you can give me would be greatly appreciated.

Ron

What version of Reporting Services are you using?

How did you create your report server database? Did you make any changes to it manually using OSQL or Enterprise Manager or Management Studio?

-Lukasz

"Keys lock row not found" error

Jens

Ron

What version of Reporting Services are you using?

How did you create your report server database? Did you make any changes to it manually using OSQL or Enterprise Manager or Management Studio?

-Lukasz

Thursday, February 16, 2012

<<identity field>>

hi friends,
i have an identity column in my table.(e.x : id )
and it's identity increment is 1.
when i insert a row the id field is 1, and in next record the field is 2....
now , i delete second record(id=2)
and now when i insert a record again , the id column is 3.
i want to record be 2 instead 3.

plz help me.
thanks

This cannot be accomplished "as standard"

When you delete a record, add the id to a "secondhand id" table, then, when you want to insert a new record, see if there is a secondhand id.

The rationale for this approach is that there are no fast methods to iterate the table with id's that may be free when there are large numbers of records

|||

well you can do it but it would not be the best way to do programming......... there can be 2 ways.

Method 1 :

Forget about identity columns.
insert the record by yourself using insert command.
before using insert command retrieve the id (as in your case) of the last record of the table. you can do it like this.
str=select id from <tablename> orderby id
get the results by using this command into a datareader and store the last value in a variable
dim last_id as integer
while dr.read
last_id=dr(0)
end while.
now insert the last_id as the id in your insert statement...

Method 2:

you can reseed(reset) the identity value when you delete a record. i mean to say when you delete a record just reset the identity column to that id(or might be one previous id) for reseting the coulmn see the link below.
http://www.mssqlcity.com/FAQ/Devel/reset_identity_column.htm

hope this could be helpful

Monday, February 13, 2012

'concatenating' several columns together for Insert into an XML column

Hi,

I am inserting a row into a Table. One of the columns in this table is of XML datatype.

I am using a Select statement to provide the values that will be inserted into the row. However, I would like to combine some of these values and parse them into XML for insertion into the XML column.

An example might help:

The Table to insert into has the following columns:

Table name: myTable

ID int

Data1 string

Data2 string

Data3 string

DataXml xml

I am inserting into this table using the following:

Code Snippet

insertinto myTable

(Data1, Data2, Data3, DataXml)

select Data1, Data2, Data3,

(select Data4, Data5, Data6)from myOtherTable as inside

where inside.ID = outside.ID forxmlraw)

from myOtherTable as outside

This works but it strikes me as inefficient. There is an additional lookup for each row which seems unnecessary as we are already at that row of the same table.

If it was a simple concatenation, I would do something like 'Data4 + Data5 + Data6', and it seems this is not much different except that instead of concatenation everything is being wrapped in Xml.

Does anyone have any ideas of a better way to do this?

Any help much appreciated.

Holf:

I am definitely not an expert at XML. I too am learning. This is also pretty much the way that I do it. I am also interested in seeing if Martin has a better way of doing this. To me, it looks like you are doing it right.

Kent

|||

Kent,

Thanks for the response. Well, I've checked the query plan generated and I can confirm that there is defintely some nested looping going on. I was wondering if the optimizer would realize what is happening and do some shortcuts.

Given this, I'm thinking it is going to be more efficient to concatenate the XML bits in, e.g. '<row Data4="' + Data4 + '" />' etc.

I don't want to do this because to manipulate XML I'd like to use XML tools, but I'll happily concatenate if it works out to be faster.

Thanks again for the reply. I'm learning XML too, and it does seem as though there's a lot to learn just now!

Holf

|||Be aware that FOR XML will do necessary escaping (e.g. a value like "foo & bar" will be escaped as "foo & bar") while SQL string concatenation will not do any escaping that XML requires.|||Thank you, Martin, I had forgotten about that particular side effect; I have experienced pain from this particular problem a couple of times.|||

Ah yes, that is a very good point.

Well, I may write a function to which I can pass my XML elements and which will respond with some nicely crafted XML. I am considering compiling and importing a .NET assembly specifically for this task, as in .NET there are some nice tools for building up valid XML from raw elements, and these tools take care of escaping invalid characters.

I know this sounds like overkill but I still think it will be more efficient than the subquery approach above. Of course, I will be testing to find out.

Better check that my hosting provider allows upload of .NET assemblies to SQL Server...

Thanks Martin and Kent for your thoughts on this.

|||

Well, I've checked with my hosting provider and they do not allow upload of .NET assemblies. This is not surprising really, given it is a shared database and with the rights to upload assemblies, users could compromise the entire database.

So, I've written a very simple function to escape the necessary characters (of which there are not many from what I read):

Code Snippet

SETANSI_NULLSON

SETQUOTED_IDENTIFIERON

ALTERFUNCTION [dbo].[fn_EscapeForXml]

(

@.stringToEscape asnvarchar(256)

)

RETURNSnvarchar(256)

BEGIN

SELECT @.stringToEscape =REPLACE(@.stringToEscape,'&','&')

SELECT @.stringToEscape =REPLACE(@.stringToEscape,'<','<')

SELECT @.stringToEscape =REPLACE(@.stringToEscape,'>','>')

SELECT @.stringToEscape =REPLACE(@.stringToEscape,'''',''')

SELECT @.stringToEscape =REPLACE(@.stringToEscape,'"','"')

RETURN(@.stringToEscape)

END

Note that the '&' has to be escaped first, or otherwise the '&'s resulting from the other replace operations are themselves replaced.

When I am concatenating my strings to make an XML document, I pass anything which I know may have invalid characters through this function first.

I have had some XML that I have treated in this way returned to a .NET environment. When the XML is deserialized these characters are resolved as they should be into their original form, so it all seems to be working.

Thursday, February 9, 2012

#@ Row 1, Column 7: String data, right truncation @#

I am trying to BCP a ton of data files into a SQL 2005 database. The first row of data in one of my files looks like:

1000|100000156752|100000176409|100000000000|100000000000|9.4|M|9.4||1/22/1993||1||||100|||||||||1|1/22/1993|||||||

The error file has this:

#@. Row 1, Column 7: String data, right truncation @.#
0 0 0 0 0 .00 17|27.7|M|27.7||2/2/1993||1||||100|||||||||1|2/2/1993|||||||

I've built a format file based on the SQL Table definitions with this command:
FOR %%f IN (*.*) DO bcp IRIS.dbo.%%f format nul -T -n -t"|" -r"\n" -f%%f.format

Here is the DOS command window results for this file:
F:\Data>cd import

F:\Data\Import>FOR %f IN (CONVT_CORES.*) DO bcp IRIS.dbo.%f in %f -e..\BCP_Error
\%f.error -Slocalhost -Usa -Psol3admin -f..\BCP_Format\%f.format

F:\Data\Import>bcp IRIS.dbo.CONVT_CORES in CONVT_CORES -e..\BCP_Error\CONVT_CORE
S.error -Slocalhost -Usa -Psol3admin -f..\BCP_Format\CONVT_CORES.format

Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22003, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Numeric value out of range
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation

BCP copy in failed

F:\Data\Import>cd..

So, what is wrong?

Seems, that the data types do not make, make sure that the data types in the destination tables are capable to import the data, otherwsie truncation will take place and will chop the data.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||All datat types are made larger than needed by the largest data piece being imported. It works quite easily in SSIS but BCP gives me this error. With a few hundred tables, SSIS will take me days to setup and execute each one.

Something is wrong with BCP in SQL 2005!!!|||

Hi Guys,

Make sure the table schema on the target server matches the one on the source server. This will solve the proble. There is nothing wrong in the BCP in SQL 2005.

Roshan.

|||

Try using sqlchar across the board. If you are exporting from a SQL server and then importing the data back in, use native format to export, and specify this in your format file.

I ran into this problem myself a few weeks ago.

|||Exporting from Oracle. I've even gone so far as to make all columns varchar(xx) to see if that would fix it. It does *not* fix it. I've tried various formats for the CSV and they all give me this error. I exported to XML, wrote a small app to import that and I have problems with one, and only one, table out of 736 tables. I cannot say BCP works as I cannot get it to work. SSIS works on all but 7 tables so I know the data can go in. <bah type="humbug" />|||I have bcp jobs that run every night from production to staging, and one started failing after putting SP2 on SQL server. It was only on one table. I finally fixed the problem by deleting the table and recreating it from a script extracted from production. All I can figure is that something in the table schema got corrupted.

#@ Row 1, Column 7: String data, right truncation @#

I am trying to BCP a ton of data files into a SQL 2005 database. The first row of data in one of my files looks like:

1000|100000156752|100000176409|100000000000|100000000000|9.4|M|9.4||1/22/1993||1||||100|||||||||1|1/22/1993|||||||

The error file has this:

#@. Row 1, Column 7: String data, right truncation @.#
0 0 0 0 0 .00 17|27.7|M|27.7||2/2/1993||1||||100|||||||||1|2/2/1993|||||||

I've built a format file based on the SQL Table definitions with this command:
FOR %%f IN (*.*) DO bcp IRIS.dbo.%%f format nul -T -n -t"|" -r"\n" -f%%f.format

Here is the DOS command window results for this file:
F:\Data>cd import

F:\Data\Import>FOR %f IN (CONVT_CORES.*) DO bcp IRIS.dbo.%f in %f -e..\BCP_Error
\%f.error -Slocalhost -Usa -Psol3admin -f..\BCP_Format\%f.format

F:\Data\Import>bcp IRIS.dbo.CONVT_CORES in CONVT_CORES -e..\BCP_Error\CONVT_CORE
S.error -Slocalhost -Usa -Psol3admin -f..\BCP_Format\CONVT_CORES.format

BCP copy in failed

F:\Data\Import>cd..

So, what is wrong?

Seems, that the data types do not make, make sure that the data types in the destination tables are capable to import the data, otherwsie truncation will take place and will chop the data.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Something is wrong with BCP in SQL 2005!!!|||

Hi Guys,

Make sure the table schema on the target server matches the one on the source server. This will solve the proble. There is nothing wrong in the BCP in SQL 2005.

Roshan.

|||

Try using sqlchar across the board. If you are exporting from a SQL server and then importing the data back in, use native format to export, and specify this in your format file.

I ran into this problem myself a few weeks ago.

Tuesday, March 20, 2012

"Views"(Edited)

"Views"(Edited)

"Views"

Friday, March 16, 2012

"Row yielded no match during lookup" while there is no row going through the Lookup

"Row yielded no match during lookup" while there is no row going through the Looku

Sunday, March 11, 2012

"Other" grouping or Row aggregate

"order by" in xquery

"Object reference not set to an instance of an object" error

"No data exists for the row/column" (InvalidOperationException) after a number of read

"No data exists for the row/column" (InvalidOperationException) after a number of read

"No data exists for the row/column" (InvalidOperationException) after a number of

Thursday, March 8, 2012

"Keys lock row not found" error

"Keys lock row not found" error

Thursday, February 16, 2012

<<identity field>>

Monday, February 13, 2012

'concatenating' several columns together for Insert into an XML column

Thursday, February 9, 2012

#@ Row 1, Column 7: String data, right truncation @#

#@ Row 1, Column 7: String data, right truncation @#

SQL,tables in a DB

Blog Archive

About Me