Sql Server–find some texts in Stored Procedures

I often need to search for some texts in the body of stored procedures. This query can help:

SELECT routine_name, routine_definition
FROM information_schema.routines
WHERE UPPER(routine_definition) LIKE UPPER('%texttobesearched%')
AND routine_type='procedure'

EDIT: just discovered that this method has some problems with long stored procedures; found another one that works better:

declare @searchString varchar(100)

Set @searchString = '%' + 'text to be searched' + '%'

SELECT Distinct SO.Name
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
AND SO.Type = 'P'
AND SC.Text LIKE @searchString
ORDER BY SO.Name

Sql Server Fragmentation

Found in an article on SqlServerCentral (VERY useful site), some scripts that show the fragmentation degree of your database objects:

-- check fragmentation on @db
Declare @db     SysName;
Set @db = 'MCD_SAWFC_PROD';

SELECT CAST(OBJECT_NAME(S.Object_ID, DB_ID(@db)) AS VARCHAR(20)) AS 'Table Name',
CAST(index_type_desc AS VARCHAR(20)) AS 'Index Type',
I.Name As 'Index Name',
avg_fragmentation_in_percent As 'Avg % Fragmentation',
record_count As 'RecordCount',
page_count As 'Pages Allocated',
avg_page_space_used_in_percent As 'Avg % Page Space Used'
FROM sys.dm_db_index_physical_stats (DB_ID(@db),NULL,NULL,NULL,'DETAILED' ) S
LEFT OUTER JOIN sys.indexes I On (I.Object_ID = S.Object_ID and I.Index_ID = S.Index_ID)
AND S.INDEX_ID > 0
ORDER BY avg_fragmentation_in_percent DESC

The following SQL can be used to rebuild all indexes for the specified table;

ALTER INDEX ALL ON <Table Name> REBUILD;

while the following SQL can be used to rebuild a specific index.

ALTER INDEX <Index Name> ON <Table Name> REBUILD;

Alternatively, indexes can be reorganised. The following SQL can be used to reorganise all indexes for the specified table;

ALTER INDEX ALL ON <Table Name> REORGANIZE; 

while the following SQL can be used to reorganise a specific index.

ALTER INDEX <Index Name> ON <Table Name> REORGANIZE; 

stsadm, where is it?

I often forget the location of stsadm, the admin command line tool for MOSS. Here it is:

%COMMONPROGRAMFILES%\microsoft shared\web server extensions\12\bin

bye

Get a list of installed programs in Windows

I’m in the middle of a transition from a pc to another, and I’d like to reinstall all the software I (probably) need.

I recently discovered that there is a way to get such a list without the need of third party software, using WMIC (Windows Management Instrumentation Command-line). Here’s how.

• open a command prompt with administrative rights (“cmd” and Control+Shift+Enter)
• type wmic and press enter
• then type this command: /output:C:\swlist.txt product get name,version,installdate,description

This way you’ll obtain the complete list of what has been installed on your Windows environment… but do not forget that sometimes you use software that does not installs but instead runs directly – so probably this list will not be complete.

To get more insights on WMIC you can browse the command-line help with /? – or read articles like this one.

SOLVED: Sql Server 2008 log shipping on servers not in same domain

Lately I suffered some headache trying to set up log shipping on a couple of Sql Server 2008 machines.

In my farm, the “second” server arrived when the first was already running, responding to a high traffic website. And the two servers are not in the same domain – actually they are not in any domain.

I won’t enter in detail on how to set up log shipping, follow the wizard, it’s quite easy (to reach the wizard: right click on a db, choose Tasks, then “Ship transaction logs”).

When you set up log shipping, and the servers are in the same domain, you have little problems: you need to create a couple of file share on the two servers, and give read permissions to accounts running the Sql Server Agent service “on the other” server. But that is quite easy, follow instructions and it’s done.

A different story is when the shipping server don’t know anything of the “shipped” one. I tried different configuration, but I always end in “Access denied” errors.

At last I found this answer on Serverfalult.com, and that was the path to follow. The answer is about Sql 2005, but the same works on sql 2008.

In brief, here’s what I did:
- created an account on the two servers, with exactly the SAME NAME and the SAME PASSWORD, and put the user in Administrators group
- changed identity (“log on” tab) of both Sql Server Agent AND Sql Server services (only Agent did not suffice) on both servers
- gave read permission on the share used in the log shipping configuration

Done all that, when I ran the log shipping job they started working immediately.

Now I’m not a windows authentication guru, but all this looks a little crazy to me… but hey, who cares! Now log shipping works… :-)

Facebook – wall post with Facebook Developer Toolkit, stream.publish

Just a c# sample that I find useful to have here – so that everybody around me can find it whenever needed :-)

public void Post(facebook.API fbAPI, string appLink)
{
    string response = fbAPI.stream.publish(
        " decided to go nuts.",
        new attachment()
        {
            name = "my application rocks",
            href = appLink,
            caption = "{*actor*}decided to go nuts.",
            description = "One morning, when Gregor Samsa woke from troubled dreams, he found himself transformed in his bed into a horrible vermin. http://indialimaalpha.blogspot.com/",
            properties = new attachment_property()
            {
                category = new attachment_category() { text = "Take another action...", href = appLink }
            },
            media = new List<attachment_media>() {
                new attachment_media_image() { src = WebConfigurationManager.AppSettings["AbsolutePath"] + "images/go_joseki.jpg", href = appLink }
            }
        },
        new List<action_link>() {
            new action_link() { text = "Take Action!", href = appLink }
        },
        null,
        0);
}

Sql Server Restore

 

Some statements I found useful to recover a database backup applying some transaction logs (had to step into this due to a wrong delete operation on a production database – no, it wasn’t me…).

RESTORE DATABASE [TheDatabase]
FROM DISK = 'D:\foldername\bak\FULL_20091102_024436.bak'
WITH
MOVE 'TheDatabase_Data' TO 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\TheDatabase_Data.mdf',
MOVE 'TheDatabase_log' TO 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\TheDatabase_Log.ldf',
NORECOVERY

RESTORE LOG [TheDatabase] FROM DISK = 'D:\foldername\bak\20091102_060214.bak' WITH NORECOVERY

RESTORE LOG [TheDatabase] FROM DISK = 'D:\foldername\bak\20091103_180221.bak' WITH NORECOVERY

[…]

RESTORE LOG [TheDatabase] FROM DISK = 'D:\foldername\bak\20091105_000219.bak' WITH NORECOVERY

RESTORE LOG [TheDatabase] FROM DISK = 'D:\foldername\bak\20091105_060204.bak' WITH RECOVERY

Some comments: the NORECOVERY option used in all the statement but one causes Sql Server to leave the db in a non operational state; this is needed because we will apply other restore statements. In the last one I use the RECOVERY option, in order to put the database in operational state.

Obviously this is only a little part of the big big world of database maintenance: just to say, give a look at the RESTORE command syntax… http://msdn.microsoft.com/en-us/library/ms186858.aspx

Bye!

Windows Mobile: Messaging not working, a solution

Sometimes in WM5 (but also in WM6, I’m sure) the messaging system stops working. You cannot even open the inbox, or send an SMS, nothing.

If you find yourself in this situation… try this:

- open your storage card file system
- look for a folder named Inbox.mst12345678 (the number here may change) 
- delete it and reboot your device.

This should work, hope this helps!

Shutdown! Now!!!

Sometimes I need to do a shutdown on computers thru command-line, and every time I need to look for the correct parameters list. So I decided today to write here some hints.

The command needed to force a restart on a windows vista computer is:

shutdown /t 0 /f /r

Btw you can force a shutdown or restart also on remote computers, and in this case the command is

shutdown /t 0 /f /r /m \\computername

I noticed that depending on the OS version you will be using “-“ instead of “/” to prefix commands. So for example on Win Server 2000 you could be using:

shutdown -t 0 -f -r

And finally here’s a complete parameters list:

C:\>shutdown
Usage: shutdown [/i | /l | /s | /r | /g | /a | /p | /h | /e] [/f]
    [/m \\computer][/t xxx][/d [p|u:]xx:yy [/c "comment"]]

    No args    Display help. This is the same as typing /?.
    /?         Display help. This is the same as not typing any options.
    /i         Display the graphical user interface (GUI).
               This must be the first option.
    /l         Log off. This cannot be used with /m or /d options.
    /s         Shutdown the computer.
    /r         Shutdown and restart the computer.
    /g         Shutdown and restart the computer. After the system is
               rebooted, restart any registered applications.
    /a         Abort a system shutdown.
               This can only be used during the time-out period.
    /p         Turn off the local computer with no time-out or warning.
               Can be used with /d and /f options.
    /h         Hibernate the local computer.
               Can be used with the /f option.
    /e         Document the reason for an unexpected shutdown of a computer.
    /m \\computer Specify the target computer.
    /t xxx     Set the time-out period before shutdown to xxx seconds.
               The valid range is 0-600, with a default of 30.
               Using /t xxx implies the /f option.
    /c "comment" Comment on the reason for the restart or shutdown.
               Maximum of 512 characters allowed.
    /f         Force running applications to close without forewarning users.
               /f is automatically set when used in conjunction with /t xxx.
    /d [p|u:]xx:yy  Provide the reason for the restart or shutdown.
               p indicates that the restart or shutdown is planned.
               u indicates that the reason is user defined.
                 if neither p nor u is specified the restart or shutdown is unplanned.
               xx is the major reason number (positive integer less than 256).
               yy is the minor reason number (positive integer less than 65536).

Accessing web services from swf – never use virtual directories

Today I learned something, as usual the hard way :-(

We were deploying a “dynamic banner” developed in flash, and we were already late: this banner is going to access a web service (that we developed, .Net 2.0, Sql server-based) every time is loaded in a web page.

Now, to keep things easy, I decided to deploy the web service in a virtual directory in the production servers, such avoiding the need of updating DNS etc etc (the domain is maintained elsewhere, we are only host for this initiative).

Then when we switched in the production environment, the banner loaded from other domains… simply did not call anything but the web service WSDL.

After one “panic hour” without understanding where the problem was, and modifying in every known way the crossdomain.xml file, it came to my mind that maybe the problem was the virtual directory.

We moved as fast as possible everything in a new domain by itself and – MAGIC – now everything works.

So when dealing with swf that need to access server resources – never use virtual directories!!!

Bye

Top 50 – SQL

Some time ago I had to select the users from a table that reached the first 50 scores in a particular game. Not 50 users, but the users that made the first top 50 scores. Here’s my solution, for my future memory :-)

CREATE PROCEDURE [dbo].[GetTop50]
AS
DECLARE @minscore int
CREATE TABLE #top50
    (userid INT PRIMARY KEY,
     score int)

INSERT INTO #top50 (userid, score)
    SELECT top 50 userid, TotalScore 
    FROM UserList 
    order by TotalScore desc

select @minscore = min(score) from #top50

delete from #top50 where score = @minscore

INSERT INTO #top50 (userid, score)
    SELECT userid, TotalScore 
    FROM UserList where TotalScore = @minscore

select * from UserList 
    where userid in (select userid from #top50)
    order by TotalScore desc

Hope this helps

Andrea

EDIT: what about a simple one like

select userid, TotalScore
from UserList
where TotalScore in
(select distinct top 50 TotalScore from UserList order by TotalScore desc)

uff... :-)

Service Control (sc.exe)

Today I had to remove a Windows service I'm writing from my Vista pc. I hoped that uninstalling it would be enough, bit no, the service was still there showing up in services.msc console.

So googling around I found that this can be easily done using a little known (or so I think) utility named sc.exe (sc is for Service Control, resides on System32 folder).

It has many flags and functions, but to go straight to my needs, to remove a service simply type at a command prompt (run it with "elevation", i.e. "as Administrator"):

sc.exe delete NameOfTheService

That's all. But try to run sc.exe with no parameters to have an idea of the many functionalities of the utility.

bye!

A couple of Sql Server (useful) things

1) Changing sa password

A couple of days ago I discovered with horror that I forgot my "sa" password, on my sql 2005 local instance.

To solve this, googling around I found this couple of methods:

USE MASTER

ALTER LOGIN [sa] WITH PASSWORD=N'new_password'

or from a command prompt

    OSQL -S <server_name> -E
    1> EXEC sp_password NULL, 'new_password', 'sa'
    2> GO

2) Transact sql Split function

I needed a split function, and I fpound this good forum discussion exactly on this topic:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

I used this one:

CREATE FUNCTION dbo.Split
(
@RowData nvarchar(2000),
@SplitOn nvarchar(5)
) 
RETURNS @RtnValue table
(
Id int identity(1,1),
Data nvarchar(100)
)
AS 
BEGIN
Declare @Cnt int
Set @Cnt = 1

While (Charindex(@SplitOn,@RowData)>0)
Begin
  Insert Into @RtnValue (data)
  Select
   Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

  Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
  Set @Cnt = @Cnt + 1
End
 
Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))

Return
END

Survival: Sql Server, change ownership on stored procedures

Some time ago I wrote about a couple of methods to grant execution permission on stored procedures, on Sql Server 2000.

Now I had to write something similar, but the scope was to change ownership of the objects; after some googling I found this page on support.microsoft.com that lists a good way to do the job... but I modified slightly the code from MS, because I needed the possibility to decide to actually execute the commands or simply print them.

So I added a simple parameter (the third), datatype bit, default 0 (false). When set to 1 (true) will cause the execution of the commands.

Here's the code:

CREATE PROCEDURE [dbo].[chObjOwner]( @usrName varchar(20), @newUsrName varchar(50), @exec bit = 0)
as
-- @usrName is the current user
-- @newUsrName is the new user

set nocount on
declare @uid int                   -- UID of the user
declare @objName varchar(50)       -- Object name owned by user
declare @currObjName varchar(50)   -- Checks for existing object owned by new user
declare @outStr nvarchar(256)       -- SQL command with 'sp_changeobjectowner'
set @uid = user_id(@usrName)

declare chObjOwnerCur cursor static
for
select name from sysobjects
where 1=1
AND uid = @uid
AND xtype in ( 'P', 'U', 'V')
and name <> 'chObjOwner'
-- category: zero is valid for Stored Procedures... but not for tables
-- and category = 0

open chObjOwnerCur
if @@cursor_rows = 0
begin
  print 'Error: No objects owned by ' + @usrName
  close chObjOwnerCur
  deallocate chObjOwnerCur
  return 1
end

fetch next from chObjOwnerCur into @objName

while @@fetch_status = 0
begin
  set @currObjName = @newUsrName + '.' + @objName
  if (object_id(@currObjName) > 0)
    print 'WARNING *** ' + @currObjName + ' already exists ***'
  set @outStr = 'sp_changeobjectowner ''' + @usrName + '.' + @objName + ''',''' + @newUsrName + ''''
  print @outStr
  IF @exec = 1
    execute sp_executesql @outStr
  --print 'go'
  fetch next from chObjOwnerCur into @objName
end

close chObjOwnerCur
deallocate chObjOwnerCur
set nocount off
return 0

Windows Mobile 5 - Left Soft Key and PocketInformant

Just because I needed it and it's not so easy to find... If you need to remap the left soft key in Windows Mobile (the standard link is to "Calendar", \Windows\Calendar.exe) you have to modify the registry key:

HKEY_CURRENT_USER/Software/Microsoft/Today/Keys/112

There you have two string keys, Default and Open. Default contains the String you want to display in the menu, Open contains the path to the app you need to use for that.

For example I lost my PocketInformant Calendar link (due to the setup of another app that overwrote that voice) and I restored to my previous situation writing in the "Open" key the following:

Programmi\WebIS\PocketInformant\PITab.exe" 11

If Outlook doesn't open web links anymore...

On Windows Vista, open Control Panel, click "Programs", then "Default Programs".

There, click "Set program access and computer defaults", open the "Custom" section. You should then choose "Internet Explorer", then close everything , Control Panel, Outlook, Internet Explorer... this worked for me.

Hope this helps!

Windows update lasting forever

I had to completely rebuild my Windows XP machine, and after a lot of setup and reboot, and setup, and reboot (imagine Office, Visual Studio 2003, its SP, then Visual Studio 2005, its SP, then a lot of other packages, and then... a nigthmare!!!) I found that Windows Update was not responding anymore.

After some research I found this post, and given that it solved my problem I'm sharing it with you: problem connecting windows update - CPU 100% svchost.exe in Windows Update.

The symptoms I had wew exactly those described in the post title: a svchost.exe process using continuously near 100% of CPU, and nothing more for long time.

Now I can update my pc without problems...

Sql Server, remove duplicate records

Sometimes I need to remove duplicates from a table, given a particular column to be checked.

This few lines of transact-sql code will help, I hope:

CREATE TABLE #tmp_tableCleanDup (id int, email varchar(200))
CREATE UNIQUE CLUSTERED INDEX pk ON #tmp_tableCleanDup(ID)
CREATE UNIQUE INDEX removeduplicates on #tmp_tableCleanDup (email) WITH IGNORE_DUP_KEY

BEGIN TRANSACTION

 INSERT #tmp_tableCleanDup
 SELECT e.ID, e.email
 FROM OriginalTable e

 DELETE OriginalTable
 WHERE 1=1
 AND id NOT IN (SELECT id FROM  #tmp_tableCleanDup)

COMMIT TRANSACTION

 DROP TABLE #tmp_tableCleanDup

System.Web.Mail and System.Net.Mail... Complete faqs for ASP.NET

Useful links:

http://www.systemwebmail.com/

http://www.systemnetmail.com/

the first is a "Complete FAQ for sending email in ASP.NET", covers framework 1.x, while  the second is a "Complete FAQ for the System.Net.Mail namespace found in .NET 2.0"

Survival: Sql Server, grant on all stored procedure and shrink db and logs

1) Stored Procedures: to grant execute permission on all the stored procedures in a database, use this (quick and dirty) solution:

use DATABASE_NAME

select 'grant execute on ' + specific_name + ' to [LOGIN_NAME] '
from  information_schema.routines
where routine_type = 'PROCEDURE'

executing this after the obvious substitutions of LOGIN_NAME and DATABASE_NAME will return a bunch of lines like

grant execute on stored_procedure_name to [LOGIN_NAME]

if you copy and execute those lines, you're done.

2) Stored Procedures: another (less dirty) way to obtain the same result:

DECLARE @proc_name  SYSNAME
DECLARE @sql   VARCHAR(4000)
DECLARE @username  VARCHAR(255)

SET @username = 'LOGIN_NAME_HERE'
SET @proc_name = ''

WHILE 1=1
 BEGIN
  SET @proc_name = (SELECT TOP 1 ROUTINE_NAME
     FROM INFORMATION_SCHEMA.ROUTINES
     WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'IsMSShipped') = 0
   -- Only user stored procedures here!
    AND ROUTINE_TYPE = 'PROCEDURE'
    AND ROUTINE_NAME > @proc_name
    ORDER BY ROUTINE_NAME
  )
  IF @proc_name IS NULL BREAK
  SET @sql = 'GRANT EXECUTE ON ' + QUOTENAME(@proc_name) + ' TO ' + @username
 EXEC (@sql)
 --Print (@sql)
 END

3)  Shrink database and transaction log: Just a couple of instructions that sometimes are useful to shrink databases log files:

BACKUP LOG  databasename  WITH TRUNCATE_ONLY
 
DBCC SHRINKFILE (  databasename_Log  , 1)

DBCC SHRINKDATABASE (databasename, 10)