Database

Like Command in SQL Server 2005

9-Jul-09 3:50

Hi Biswas,
Thanks for the help,

I achieved this using PIVOT concept. Now I am able to get the data I need Smile | :)

Thanks a lot for the help.

Thanks
Ramm

Isaac Gordon2-Jul-09 23:24

Isaac Gordon

2-Jul-09 23:24

Hi experts
i am using sql server 2005.
How to use like clause of sql server to search Pharse of Word
e.g
select * from abc where name like '%this is my book%'
but not working properly.pls help me
Thanku
Dinesh

Eddy Vluggen3-Jul-09 1:17

Eddy Vluggen

3-Jul-09 1:17

Try this one;

SELECT *
  FROM sysobjects
 WHERE NAME LIKE ('%name%')

I are troll Smile | :)

David Skelly3-Jul-09 2:17

David Skelly

3-Jul-09 2:17

There is nothing wrong with the syntax in the example you have given. When you say it is not working properly, what is happening? Is it giving you an error, or not returning any data, or returning unexpected data?

Niladri_Biswas3-Jul-09 7:15

3-Jul-09 7:15

Hi man,

First of all I agree with David Skelly.
There is nothing wrong in the syntax.

However, I doubt regarding certain things which you need to check from your end.

For example I have a string like "Project Manager needed in this project (mandatory)"

Case 1:

If I give a SELECT QUERY like this

SELECT ROLENAME
FROM TBLROLE
WHERE RoleName LIKE ('%Project Manager needed in this project (mandatory)%')

-OR-

SELECT ROLENAME
FROM TBLROLE
WHERE RoleName LIKE ('%Project Manager %')

-OR-

SELECT ROLENAME
FROM TBLROLE
WHERE RoleName LIKE ('%(mandatory)%')

I get the correct result.

Case 2:

Now if I give the query like this

SELECT ROLENAME
FROM TBLROLE
WHERE RoleName LIKE ('%ProjectManager needed in this project (mandatory)%')

-OR-

SELECT ROLENAME
FROM TBLROLE
WHERE RoleName LIKE ('% (mandatory) %')

I am not getting any result.
The reason is that, in the first case there is no space between Project & Manager while the same holds good for (mandatory)

But the query behaved in the manner it is supposed to.

What I inferred from your statement, is that, may be you also ran through the same problem.
Have a look into that.

Let me know in case of any concern.
Smile | :)

Niladri Biswas

Isaac Gordon3-Jul-09 19:08

Isaac Gordon

3-Jul-09 19:08

Thank u Sir

Niladri_Biswas4-Jul-09 4:49

Re: trigger on system tables [modified]

4-Jul-09 4:49

Niladri Biswas

trigger on system tables

sunit_822-Jul-09 22:54

sunit_82

2-Jul-09 22:54

I have a few questions to ask. Before that let me tell you my problem definition.
I want to create some tables and insert them with some default data when ever a new database is created in my sql server 2005. So i was hoping if there whould be any way to add a trigger on sys.databases table.

So these are the questions I have:
1. How to list and view the system tables like sys.databases, sysobjects, ...etc in the SQL 2005 management studio?
We can query these tables but they are not visible under any database(master or msdb).
2. How to add a trigger on sys.databases or sysobjects table ?
3. How to list all triggers for a particular table?

s.a.w.

Niladri_Biswas3-Jul-09 17:29

Re: trigger on system tables

3-Jul-09 17:29

Hi Sunit

I am presenting the answers of your questions

1) a)How to list and view the system tables like sys.databases, sysobjects, ...etc in the SQL 2005 management studio?<br />

Ans:

SELECT * FROM SYS.DATABASES

     SELECT * FROM SYS.OBJECTS

b)We can query these tables but they are not visible under any database(master or msdb).

Ans:SQL SERVER stores its system objects in a read-only database call RESOURCE DATABASE. However,they appear logically as SYS schema for every database. It can only be modified by Microsoft Customer Support Services (CSS) specialist.

For more information, please visit http://msdn.microsoft.com/en-us/library/ms178028.aspx[^]

However, SYSOBJECTS is just a view(the following query bears the proof) . So you cannot modify the underlying system table.

SELECT OBJECT_DEFINITION(OBJECT_ID('sys.objects'));

Output:

<code>CREATE VIEW sys.objects</code> AS   SELECT name, object_id, principal_id, schema_id, parent_object_id,    type, type_desc, create_date, modify_date,    is_ms_shipped, is_published, is_schema_published   FROM sys.objects$

2) How to add a trigger on sys.databases or sysobjects table ?

Ans:

If I query the SYSOBJECT / SYS.DATABASE, I will get all the SYSTEM TABLES + USER TABLES / SYSTEM DATABASES + USER DATABASES respectively.

So I can easily write a trigger on them.

e.g.In the MASTER database, there is a system table call spt_fallback_dev.

I have fired the following trigger statement & it worked

CREATE TRIGGER SOME_TRIGGER ON spt_fallback_dev

FOR UPDATE AS

SELECT * FROM spt_fallback_dev

The same holds good for any user table too.

For more information , you can look into this http://www.sql-server-performance.com/faq/trigger_system_table_p1.aspx[^]

3) How to list all triggers for a particular table?

Ans:Lets do a practical

Create a table like this

CREATE TABLE SAMPLE_TABLE 
(
	EID INT,
	ENAME VARCHAR (12)
);

Add 2 triggers like the following

Trigger1

CREATE TRIGGER SAMPLE_TRIGGER_A ON SAMPLE_TABLE

FOR UPDATE AS

SELECT * FROM SAMPLE_TABLE

Trigger2

CREATE TRIGGER SAMPLE_TRIGGER_B ON SAMPLE_TABLE

FOR UPDATE AS

SELECT * FROM SAMPLE_TABLE

Now execute the following query

SELECT NAME 
FROM SYSOBJECTS
WHERE DELTRIG = (
SELECT ID 
FROM SYSOBJECTS 
WHERE NAME = 'SAMPLE_TABLE')

OUTPUT:

NAME
------------------
SAMPLE_TRIGGER_A
SAMPLE_TRIGGER_B

Hope this helps.
Smile | :)

Niladri Biswas

modified on Saturday, July 4, 2009 12:17 AM

sunit_825-Jul-09 22:39

sunit_82

5-Jul-09 22:39

Hi

Thanx for trying, but i got the solution for my primary problem defination at below link:

Automatically create a SQL Server table in a new database.[^]

This is what i was looking for. I hope this is usefull for you and others.

s.a.w.

How SQL View can be used in this scenario

Krishna Aditya2-Jul-09 22:29

2-Jul-09 22:29

Hi Guys,

I am using Datareader in my C# code to get data from db and dumping it into excel sheets. I am able to do it for everyrow correctly (based on the data from the sql view).

I have a problem in one of the sql views.

rolename                        roledesc            roletype         roleuser
----------------------------------------------------------------------
Team Leader (required)   xyzxyz               Primary         Mike
Team Leader (required)   xyzxyz               Backup            Cary
Process Focal                  abcabc               Primary         Jeff
Process Focal                  abcabc               Backup            Dave

As, when i read each row of this using reader i get,
(Team Leader (required) xyzxyz Primary Mike) AND (Team Leader (required) xyzxyz Backup Cary )) in 2 separate rows.

But when i have to dump this data to excel sheet, i have to only dump the Team Leader (required) once and the roleuser should be Mike and next to him Cary.

I mean,

cell1                              cell2            cell3      cell4
----------------------------------------------------------------------
Team Leader (required)   xyzxyz         Mike         Cary
Process Focal                  abcabc         Jeff         Dave

Please help me in modifying the sql view for this.

Tables: rolename, roledesc comes from roledescription table, roletype comes from roletypedescription table, roleuser from username table,

I can explain if the above statements are not clear Smile | :)

Thanks Ramm

Krishna Aditya2-Jul-09 22:41

2-Jul-09 22:41

My SP reads as

ALTER PROCEDURE [dbo].[tblRoleSummary_sel_all_sp]

SELECT txtRoleName, txtRoleDescription, txtRoleTypeDescription, txtUserName FROM tblRoleSummary_View

Do I need to change the SP and display the data as per my requiremnt or view itself can be changed for this/?

Thank YOu
Ramm

David Skelly3-Jul-09 2:26

David Skelly

3-Jul-09 2:26

Do a self-join on the table, something like:

select p.rolename, p.roledesc, p.roleuser, b.roleuser
from RoleTable p, RoleTable b
where p.roletype = 'Primary'
and p.rolename = b.rolename
and b.roletype = 'Backup'

I think that's right, off the top of my head.

This assumes that every Primary has a Backup. If not, you will need an outer join.

Krishna Aditya3-Jul-09 2:58

3-Jul-09 2:58

Hi,

Its not compulsory, for every Primary, a backup will be available,

My query is

Hi, This is my SQL Query

SELECT dbo.tblRoleDescription.txtRoleName, dbo.tblRoleDescription.txtRoleDescription, dbo.tblRoleType.txtRoleTypeDescription, dbo.tblUserInfo.txtUserName, dbo.tblRoleAssignment.idSubProject, dbo.tblRoleAssignment.idLaunch FROM dbo.tblLaunchInfo INNER JOIN dbo.tblRoleAssignment ON dbo.tblLaunchInfo.idLaunch = dbo.tblRoleAssignment.idLaunch INNER JOIN dbo.tblProjectUsers ON dbo.tblRoleAssignment.idSubProject = dbo.tblProjectUsers.idSubProject AND dbo.tblRoleAssignment.idUser = dbo.tblProjectUsers.idUser INNER JOIN dbo.tblUserInfo ON dbo.tblProjectUsers.idUser = dbo.tblUserInfo.idUser INNER JOIN dbo.tblRoleDescription ON dbo.tblRoleAssignment.idRole = dbo.tblRoleDescription.idRole INNER JOIN dbo.tblRoleType ON dbo.tblRoleAssignment.idRoleType = dbo.tblRoleType.idRoleType

tblroleassignment -> idSubProject , idUser , idRoleType, idRole , idLaunch tblroledescription -> idRole(PK), txtRoleName, txtRoleDescription tblRoleType -> idRoleType (PK) , txtRoleTypeDescription tbluserinfo -> iduser, txtusername tbllaunchinfo -> idlaunch tblprojusers -> idsubproject, iduser

the sql query and the tables are given above, Please help me

Thank you,
Ramm

Full Text Search in SQL Server 2005

David Skelly3-Jul-09 5:57

David Skelly

3-Jul-09 5:57

To be completely honest, I don't have time to write your query for you. I have my own work to do.

I've given you an idea of how to do it: a self-join on the table to link the Primary user to the associated Backup user will give you what you want.

If you want to simplify things for yourself you could create a view that only includes Primary users. Then create another view that only includes Backup users. Then join them together on role id. That would make the query easier to work with.

Isaac Gordon2-Jul-09 18:21

Isaac Gordon

2-Jul-09 18:21

Hi Experts
I am using sql server 2005 express edition
i am not able to run the full text search.
when i create the catalog or any query realted to Full Text Search
it give me message
Laugh | :laugh:

Full-Text Search is not installed, or a full-text component cannot be loaded.

plz help me how the full text search is runing
Thank u
Dinesh

Re: Full Text Search in SQL Server 2005

Niladri_Biswas2-Jul-09 18:53

Complicated Summation Query Help

2-Jul-09 18:53

Even when I started learning Full-Text Search, I also fall into the same problem. However, I visited these site

http://www.devnewsgroups.net/group/microsoft.public.sqlserver.tools/topic25572.aspx[^]

http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/2b44f674-0cf2-4c79-89b3-4d9d8b641d53[^]

and got the solution.

Hope this helps.

Smile | :)

Niladri Biswas

JohnQuar12-Jul-09 7:44

JohnQuar1

2-Jul-09 7:44

Hello,

I have two columns, a category and a value. Below is the data:

Category		Total
Devices		38111
Plant		6063
Improvements		2266

I want to add two more columns: Zero and Sum

So the columns would be Category, Zero, Total, Sum

Is there any way to take the addition of Zero and Total and have the result in Sum, THEN take the result of Sum, and make it the answer in the next row of Zero, then repeat by adding zero and Total?

The reason why I want to do this is b/c I am trying to create a waterfall graph and this is the only way I can think to do it with the data that I have. Thanks for your time.

JM

I would be forever indebt to you if you could help out. Thanks!

Re: Complicated Summation Query Help

David Mujica2-Jul-09 8:37

David Mujica

2-Jul-09 8:37

After thinking about this for a bit, I think I would do this by creating a TEMP table and cursor to loop through the main table.

The TEMP table would be something like this:

Category      Zero  Total    Sum
Devices          0  38111   38111
Plan         38111   6063   44147
Improvements 44174   2266    4640

Use the following code snipet to get you going with the logic ...

DECLARE  myCURSOR CURSOR FOR 
	  SELECT CATEGORY, TOTAL
	  FROM myTABLE

OPEN myCURSOR
FETCH NEXT FROM myCURSOR INTO @XCATEGORY, @XTOTAL

SET @PBAL = 0.0
	
WHILE(@@FETCH_STATUS=0)	BEGIN

  SET @GTOTAL = @PBAL + @XTOTAL

  SET @MYSQL = 'INSERT INTO #MYTEMP'
  SET @MYSQL = @MYSQL + ' VALUES(' + CHAR(39) + @XCATEGORY+ CHAR(39) + ',' + @PBAL, + ',' + @XTOTAL + ','
  SET @MYSQL = @MYSQL + @GTOTAL + ')'

  PRINT @MYSQL

  EXEC(@MYSQL)

  FETCH NEXT FROM myCURSOR INTO @XCATEGORY, @XTOTAL

END

CLOSE myCURSOR
DEALLOCATE myCURSOR

GO

Hope this helps you ! Thumbs Up | :thumbsup:

Re: Complicated Summation Query Help

JohnQuar12-Jul-09 8:44

JohnQuar1

2-Jul-09 8:44

I will give that a shot, thank you for your help!

Sequential updates

Gymnast1-Jul-09 18:42

Gymnast

1-Jul-09 18:42

I am trying to update records in a table in sequence by setting up a Cursor and using Fetch like so..

DECLARE VPres_Cursor CURSOR FOR

select dbo.Person.PersonID, dbo.Person.LastName from dbo.Person inner join dbo.VicePresident
ON dbo.Person.PersonID = dbo.VicePresident.PersonID

OPEN VPres_Cursor;

FETCH NEXT FROM VPres_Cursor

WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM VPres_Cursor;
update dbo.VicePresident
set dbo.VicePresident.LastName = LastName
END;

CLOSE VPres_Cursor;
DEALLOCATE VPres_Cursor;

While I am able to see the records in the Results window in SQL Management Studio, I am not able to see the actual update in the table.
47 Burr
48 Clinton
...
..
81 Humphrey

Can anyone help??
Thanks in advance..

Gymnast.

Re: Sequential updates [modified]

Niladri_Biswas1-Jul-09 18:58

1-Jul-09 18:58

Do like this

DECLARE @LASTNAME VARCHAR(50)

DECLARE VPres_Cursor CURSOR FOR

select dbo.Person.PersonID, dbo.Person.LastName from dbo.Person inner join dbo.VicePresident
ON dbo.Person.PersonID = dbo.VicePresident.PersonID

OPEN VPres_Cursor;

FETCH VPres_Cursor INTO @LASTNAME

WHILE @@FETCH_STATUS = 0
BEGIN

update dbo.VicePresident
        set dbo.VicePresident.LastName = @LASTNAME 

        FETCH VPres_Cursor INTO @LASTNAME

END;

CLOSE VPres_Cursor;
DEALLOCATE VPres_Cursor;

I have only highlighted those lines which I think to be changed.

And it is working fine in my system.

Hope this helps.

Let me know in case of any concern
Smile | :)

Niladri Biswas

modified on Thursday, July 2, 2009 2:45 AM

Re: Sequential updates

Mycroft Holmes1-Jul-09 21:37

Mycroft Holmes

1-Jul-09 21:37

Using the update in a cursor I thought you needed Where Current of cursorname otherwise you set all the names to @Lastname

Never underestimate the power of human stupidity
RAH

Re: Sequential updates

Niladri_Biswas1-Jul-09 22:17