|
I already answered your question in the other forum. Don't cross post. Please follow the post from there.
|
|
|
|
|
Hi All,
I have a SQL database column "ID", in which I added one by one entries as 2,5,6,1,8... randomely.
I want to arrange these entries in Ascending order so that in datagridview it get displayed in order 1,2,3,4... like this.
Can someone help me on this?
|
|
|
|
|
Ever tried the ORDER BY clause?
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Try this
declare @t table(id int)
insert into @t
select 3 union all
select 1 union all
select 5 union all
select 4 union all
select 2
Query:
select * from @t order by id
Output:
id
1
2
3
4
5
Niladri Biswas
|
|
|
|
|
I have already inserted "EmpID" as 1,2,3,4,5,6,7,8,9,10,11,12. But when i see the column data of table, i found that the data is arranged like this:
1
10
11
12
3
4
5
6
7
8
9
Since, the data is already inserted in the above order, i found that in crystal report the same format is coming...but i want the order in exactly ascending order in report finally, if not possible in database.
Can someone help me on this?
|
|
|
|
|
What's a good way to modify a large number of stored procedures? I took over a project that is not using SET NOCOUNT ON, i.e. the following query returns several hundred rows.
SELECT Distinct SO.Name, SC.TEXT
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
AND SO.Type = 'P'
AND SC.colid=1 --multiple colid's are utilized when the procedures text is very long
AND NOT UPPER(SC.TEXT) LIKE '%SET NOCOUNT ON%'
ORDER BY SO.Name
modified on Friday, November 27, 2009 2:17 AM
|
|
|
|
|
hi Ted
SET NOCOUNT ON is used if you dont want to show results. Lets say i have a Query that Updates a Table, at the end it will give me something like
100 Rows Affected
to make sure that the update does not show that message , you use SET NOCOUNT ON
i have never seen this use in a query passed as a parameter, am not sure if this is correct because as i remember correctly you are not supposed to do that. mybe you can explain your challenge clearly and we can suggest another option
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
I wanted to add SET NOCOUNT ON in the body of the stored procedure after AS. I am relatively new to SQL Server (I used Oracle before) and the articles I read on google suggested that you should always SET NOCOUNT ON before executing queries inside a stored procedure.
|
|
|
|
|
then the correct way to do it is the Following
Create Proc myprc
as
set nocount on
--Do what Ever and set it back to off
set nocount off
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
That's not what was asked, he knows how to set nocount on.
He wants to know if there is a way to easily modify all of his stored procedures to include this line.
|
|
|
|
|
I must have misunderstood him , No there is no way even SQL Compare wouldnt do that
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
You could perhaps do this using a cursor with sysobjects and sp_helptext.
You would use the cursor to step through "select name from sysobjects where xtype = 'P'".
You could then get the stored procedure using sp_helptext and modify it and execute accordingly.
|
|
|
|
|
Or you could go to your version control system, check out the scripts for the stored procedures, modify them as required (using a little noddy program to automate the process), test them, check them back in again and then re-run them to drop and recreate the stored procedures in the target database.
Oh, dear. No scripts.
|
|
|
|
|
But that would be too easy. 
|
|
|
|
|
Thanks. I think sp_helptext will do the trick. I also will take a look at SMO, which was suggested by another helpful programmer.
|
|
|
|
|
You can use SMO to generate scripts add the statement you need and execute the script.
|
|
|
|
|
I just found out that there is SQL Server Options -> Query Execution -> Advanced has the option to set NOCOUNT
|
|
|
|
|
Hi
I have tried to define a full-text index, but when I come to the dialog define population schedules in the wizard, I get errors. 
The errors are
This wizard will close because it encountered the following error: (Microsoft SQL Server)
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
EXECUTE permission denied on object 'sp_help_category', database 'msdb', schema 'dbo'.
SELECT permission denied on object 'sysjobs_view', database 'msdb', schema 'dbo'. (.Net SqlClient Data Provider)
Error Number: 229
Severity: 14
State: 5
Procedure: sp_help_category
Line Number: 1
I followed the article in Codeproject http://www.codeproject.com/KB/database/SQLServer2K8FullTextSearh.aspx
Please Help Me
Regards
Fia
|
|
|
|
|
You will need to be in the dbo_role on the msdb database.
|
|
|
|
|
Hi
Thanks för your answer.
What do I have to do, to become in the dbo_role. And what is the msdb database, is that the master database. The database I'm using is in a remote server, so what can I than do to define full-text index or can't I.
Fia
|
|
|
|
|
Does anyone know how to execute a parameterized stored procedure that returns a recordset using ADO .NET? I noticed neither direct Parameter members on the DataAdapter nor Fill members on a db_Command. My workaround has been to use an Adapter to execute a query such as "EXECUTE Procedure 1, 2, 3". I would prefer not to execute a reader.
--1st method
System::Data::OleDb::OleDbCommand ^db_Command=gcnew System::Data::OleDb::OleDbCommand(gcnew System::String(Procedure_Name), db_Conn);
db_Command->CommandType = System::Data::CommandType::StoredProcedure;
db_Command->Parameters->Add("usr_id", System::Data::OleDb::OleDbType::Integer, 4);
db_Command->Parameters[0]->Value=1;
--2nd method --- I get an error about the parameter not being supplied.
System::Data::OleDb::OleDbDataAdapter ^db_Adapter=gcnew System::Data::OleDb::OleDbDataAdapter( "exec "+gcnew System::String(Procedure_Name), db_Conn);
db_Adapter->SelectCommand->Parameters->Add("usr_id", System::Data::OleDb::OleDbType::Integer, 4);
db_Adapter->SelectCommand->Parameters[0]->Value=1;
System::Data::DataSet ^db_DataSet=gcnew System::Data::DataSet;
int const Fill_Result=db_Adapter->Fill( db_DataSet); //note that an error will be thrown if the query is invalid
modified on Thursday, November 26, 2009 3:13 AM
|
|
|
|
|
You should have posted this in C++ forum.
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
I tried my best to Normalize the tables but someone suggested me there is something wrong.
Could anyone please help me out.
COURSE[CourseCode, CourseName]
INSTRUCTOR[InstructorNumber, InstructorName]
CLASS[CourseCode, ClassCode, InstrNo, InstrName, CourseStartDate]
ENROLLEMENT[CourseCode, ClassCode, StudentNumber, StudentName,Grade]
Relationships: The relationship I have defined are as below.
Instructor and Class tables: 1 to many.
Class and Course: 1 to many
Thanks
|
|
|
|
|
Generally speaking, each data item (that is not a primary or foreign key) should only appear in one table, otherwise you will have referential integrity issues.
Course table - looks fine.
Instructor table - looks fine.
Class table - Probably doesn't need a composite key between ClassCode and CourseCode, however I assume InstrName is the Instructor name - this doesn't need to be in there, as it's in the Instructor Table.
Enrollment table - Students enrol in a class. You probably need a StudentNumber and a ClassCode, and a Grade. This of course depends on changes you make to your class table.
Add a Student table in - StudentNumber, StudentName etc etc.
Hope this points you in the right direction.
|
|
|
|
|
I suggest you get your naming convention sorted first, decide whether you are going to use CourseCode, CourseNo, CourseID and stick to it religiously, your life will be much happier. As a principle I avoid composite keys, this is a personal choice, same as I wash my hands after taking a pee. I would have class with a primary key of classid and a foreign key to course unless it is a many to many in which case you need another table ClassCourseLink
Course is ok
Instructor is ok
Can a class only have 1 instructor - are you sure, what about relief instructors
Can a class be in only 1 course, then use a many to many table
Enrolment should be Student > Class no refernce to course
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
