|
Sorry, the data in ExampleData column could be anything, just purely an example to indicate that the record has additional data.
|
|
|
|
|
Ok, how about to explain you more your case, which result you want to get from your table data?
I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.
www.cacttus.com
|
|
|
|
|
The resultset you had in your original post is the resultset I am looking for, however, like I said in my original post, there is nothing to group by. What I probably should have posted is given the following dataset, how do I find the last RowID in the sequence and missed off the ExampleData column.
|
|
|
|
|
For anyone who is interested, here is my solution:
SELECT
Orders.ExampleData
FROM
OrderSeq AS Orders
LEFT JOIN
OrderSeq AS Updates ON (Orders.OrderId = Updates.OldOrderId) AND (Updates.[Type] = 'U')
where
(Updates.RowId IS NULL)
I knew it was easy, just couldn't get my head around it this morning.
Thanks to everyone who replied.
|
|
|
|
|
Hello people of CP,
I have a problem and would like you to help me fix it.
The problem is that right after I call a stored procedure calling the method stated in the title, the execution of the program is interrupted without any warning or prompt whatsoever.
The code block is this:
public virtual object Execute()
{ return this.command.ExecuteNonQuery();
}
I know the command executes because the state in the DataBase is changed. But right after the return this.command.ExecuteNonQuery(); line, the program just stops. It doesn't throw an exception, it doesn't even go to the last bracket of the method, the program just exits.
So, any idea what this might be?
Thanks a lot!
Edit:
Here's a clearer example:
private void SetSystemEnviroment()
{
try
{
using (OracleCommand command = this.con.CreateCommand())
{
command.CommandText = "SetSystemEnviroment";
command.CommandType = CommandType.StoredProcedure;
command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
}
Again, after the command.ExecuteNonQuery(); line, it just stops the execution of the program, no warnings, no exceptions, nothing.
modified on Monday, July 25, 2011 6:23 PM
|
|
|
|
|
Can you post the SetSystemEnviroment Stored Procedure code?
|
|
|
|
|
I miss the Command.Connection.Open() before you executeNonQuery()
In Word you can only store 2 bytes. That is why I use Writer.
|
|
|
|
|
Hello,
I would like to show data as a single row instead of multiple rows.
For example, a certain user has two status associated, like maybe "Pending Approval" and "Waiting Email Verfication". Instead of showing these statuses on two different rows, I would like to show as one row.
Currently,
John Smith Pending Approval
John Smith Waiting Email Verfication
Should be
John Smith Pending Approval, Waiting Email Verfication
There could such multiple states for this user, but all show in one row.
Thanks so much for your time!
|
|
|
|
|
You can use C# code to do it. Or do you need to do it only in SQL side?
I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.
www.cacttus.com
|
|
|
|
|
|
Can you post structure of your table? I guess your table contain unique column?
I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.
www.cacttus.com
|
|
|
|
|
You can either build your state list directly in a stored procedure, or create a function that will build it for you.
I encountered a similar situation yesterday: for a given item, multiple sources of data.
I created a function to concatenate the items, comma delimited, and used the function in the query to return all of the data in a single row.
Tim
|
|
|
|
|
My apologizes.
This link worked perfectly,
<a href="http://www.sqlservercentral.com/scripts/Miscellaneous/32004/">http: , using COALESE
|
|
|
|
|
This should help.
DECLARE @Col1 varchar(max)
SELECT @Col1 = COALESCE(@Col1 + ', ', '') + CAST(Col1 AS varchar(100))
FROM Table1
WHERE (................)
SELECT @EmployeeList
|
|
|
|
|
|
Assuming Sql Server 2005 and above, try this
Declare @t table([Name] varchar(20),[Status] varchar(50))
insert into @t values
('John Smith','Pending Approval'),('John Smith','Waiting Email Verfication')
--,('User2','Pending Approved'),('User2','Sent email'),('User2','Other activity')
--Select * from @t
select Result = [Name] + ' ' + Stuff((Select ',' + CAST([Status] as varchar(100)) from @t t2 where t1.[Name]
= t2.[Name] for XML Path('')),1,1,'')
from @t t1
group by t1.[Name]
Niladri Biswas
|
|
|
|
|
STUFF is what you're looking for indeed.
|
|
|
|
|
Thanks for your responses.
Instead of a comma separated list, i would like it as line breaks.
I am separating the information by pipes, and later doing a replace after calling the function like below.
I am doing this -- SELECT replace(DBO.fn_getAssociateComments(168), '|', char(13)+char(10))
But it is putting any line breaks??Am I missing something?
Thanks a bunch!
|
|
|
|
|
Hi,
According to this stackoverflow thread [^], it should work.
Don't know why it doesn't work for you...
Did you try replacing CHAR(13) + CHAR(10) with "\r\n" just to try?
Edit:
Maybe you should take a look at this example too: [^]
|
|
|
|
|
I keep bumping into a need for this.
I'm tossing this into my code library.
Very, very useful. In fact it was useful today in a problem.
_____________________________
Give a man a mug, he drinks for a day. Teach a man to mug...
|
|
|
|
|
|
Hi,
I wondered if Sql Server supported somehow to do that?
By that, I mean having the structure on one database and the data on another.
The purpose is to be able to lunch a set of integration tests that would get the data on the test database but would execute the stored procedures from the original database.
I didn't found much on google, so I suppose this isn't possible.
Edit: I didn't said, that I'd like to leave the stored procedures as they are, prefixing table names with a database would be too much work. Ideally it should be a config parameter or something like that.
modified on Friday, July 22, 2011 8:06 AM
|
|
|
|
|
The stored procedure is executed on a server not a database. You can have multiple databases on one server.
Normally you would want to run a test using code on a test machine, but if you need data from the primary database a linked server might be the way to go.
Look into linked servers either way.
Also look at IMPORT / EXPORT of tables.
Good luck,
djj
|
|
|
|
|
djj55 wrote: The stored procedure is executed on a server not a database. You can have
multiple databases on one server.
Not so sure about that, I may be wrong though.
We have multiple databases in one single server, indeed, but some databases contain some procedures and other databases contain other procedures, so I'd say that the database is the procedure's owner.
Anyway, linked servers may be a solution indeed, but it would require to prefix the table names with the server/database name. We actually have too many stored procedures, so this is not an option right now.
Thanks
|
|
|
|
|
_Zorro_ wrote: Not so sure about that, I may be wrong though.
If you try running a stored procedure that stores to the c:\ drive, see where the file ends up. Now there is most likely a way to change behavior of where a process runs but I use the defaults. See sp_who also will show you what is running on a given server.
You can run a stored procedure from one linked server on another by using the server/database name. Within a stored procedure I like to have the database with the table so if it is ran from another database it still runs. I think this is called fully qualified but I do not remember. But database.schema.table is what I suggest and with the new schema importance in 2005/2008 referencing the schema should be the minimum. (We still currently use dbo for our tables though).
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
