|
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
|
|
|
|
|
Mycroft Holmes wrote: As a principle I avoid composite keys
Composite indices can on the other hand under certain circumstances considerably speed up a query.
|
|
|
|
|
Jörgen Andersson wrote: Composite indices can on the other hand under certain circumstances considerably speed up a query
Ah but I was being derogetry about composite KEYS not indexes. Composite indexes are a basic tool for tuning a database, composite keys annoy me.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks you fellows for your guidance. I have corrected alot of mistakes I had made earlier. I have revised and resposted my work again. I am not sure of the Class and Enrollement tables relationship.
Isn't whenever the Foregin Key is involved it becomes 1 to many relationship. Please correct me. Also, shouldn't the CourseStartDate and CourseExpiryDate be in the Class table and student enrolledDate in Enrollement table.
STUDENT[StudentID(PK), StudentFName]
COURSE[CourseID(PK), CourseName]
INSTRUCTOR[InstructorID(PK), InstructorName]
CLASS[ClassID(PK),CourseID(FK), InstructorID, Location, CourseStartDate,CourseExpiryDate]
ENROLLEMENT[ClassID(PK), StudentID(PK),Grade, EnrolledDate]
Relationships are as follows:
Course and Class: 1 to many
Class and Instructor: 1 to many.
modified on Thursday, November 26, 2009 11:34 AM
|
|
|
|
|
Looking good, however.
It looks like your CourseStartDate and CourseExpiryDate should be on your course table. The classes probably have thier own dates within the course date range, unless they should be the ClassStartDate and ClassExpiryDate.
Personaly I would prefer to call it the EndDate rather than ExpiryDate. 'ExpiryDate' makes it sound like the class will begin to smell if you leave it in the cupboard to long. 
|
|
|
|
|
netJP12L wrote: Isn't whenever the Foregin Key is involved it becomes 1 to many relationship
You are correct! A many to many relationship requires a link table. Your structure loks good and I have yet to run across a smelly table no matter what the fields are named.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have the following code
SET @sql = 'SELECT *
FROM tbl_Employee
WHERE tbl_Employee.Department_ID = ' + @Department_ID + AND tbl_Employee.Surname LIKE %' + @Surname + '%'
If i remove the Like part it works fine but with it added it's casuing some problems. I think I'm concatenating the sql wrong.
If someone can help it would be much appreciated.
ASP all the way
|
|
|
|
|
Do this and check your SQL:
print @sql
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
