|
Thank you Ted, I'll give it a try and see how it goes. 
|
|
|
|
|
hello all.
I want to validate category name before insert all information about category into database.
The following condition is the way i want to compare:
Ex: ------------------------------------------------------------------------
| name in TblCategory | name in Text box | comparative result |
------------------------------------------------------------------------
| white bag | whitebag | existing |
------------------------------------------------------------------------
| whitebag | white bag | existing |
------------------------------------------------------------------------
| White Bag | white bag | existing |
------------------------------------------------------------------------
I use this following function in sql database:
BEGIN
IF EXISTS(SELECT TOP 1 *
FROM TblCategory c
WHERE (c.c_name=@name OR c.c_name=REPLACE(@name,' ',''))
AND c.c_id <> @id
)
SET @result=1
ELSE
SET @result =0
END
this give me bad result if TblCategory (white bag) and TextBox to compare has word(whitbag)...this can't compare to me
|
|
|
|
|
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
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
