Basecamp API

What is Basecamp:

The basecamp API is project management tool where the project can be created along with the related documents can also be posted. The comments can also be posted for the particular project.

What is Basecamp API:

The Basecamp API is implemented as vanilla XML over HTTP using all four verbs (GET/POST/PUT/DELETE). Every resource, like Post, Comment, or TodoList, has their own URL and are manipulated in isolation.

For reference:

http://developer.37signals.com/basecamp/

My Experience

I have used the Basecamp API in one of my project named http://www.sluiceit.com/ which is a requirement management tool for projects, the sluiceit will integrate the Basecamp with the help of the basecamp API  the completed projects is exported to the existing project or with the help of the new project in the basecamp with the help of the API token. The completed requirments are transferred to the basecamp in the form of message along with their corresponding documents as well.

In the sluiceit.com only the clientAdmin and the projectAdmin have the rights to export the project into the basecamp.

For more information on how it is designed in the sluiceit.com, and the samples related to how to upload a document, create a message, todolist, milestones. You can e-mail me to gmail .

Parsing the Querystring using Regular Expression

public static string GetParamValue(string query,string parameter)

{

string value = string.Empty;

Match match = Regex.Match(query, "[?&]" + parameter + "(?:=([^&]*))?", RegexOptions.IgnoreCase RegexOptions.Singleline);

if (match.Success)

value = match.Groups[1].Value;

return value;

}

Concatenating multiple rows into single values, Storing a dynamic returned value in a variable

Input :

1 a@a.com

2 b@b.com

3 c@c.com

Output:

a@a.com, b@b.com, c@c.com

/*

SELECT

LEFT(Emails,LEN(Emails)-1)

FROM (

SELECT TOP 1

(

SELECT

UserName + ', ' as [text()]

FROM Members

WHERE ID in (1,2,3)

ORDER BY UserName ASC FOR XML PATH('')

) AS Emails ) AS ME*/

ALTER PROCEDURE [dbo].[Comments_SelectByIds4ReplyMail]

(

@Ids NVARCHAR(MAX)

)

AS

SET NOCOUNT ON

DECLARE @Sql NVARCHAR(MAX)

DECLARE @Params NVARCHAR(30)

DECLARE @EmailIds NVARCHAR(MAX)

SET @Params = '@val NVARCHAR(MAX) OUTPUT'

SET @Sql = 'SELECT @val=LEFT(Emails,LEN(Emails)-1) '

SET @Sql = @Sql + ' FROM '

SET @Sql = @Sql + '(SELECT TOP 1 ( SELECT UserName + '', '' as [text()] FROM Members WHERE ID in '

SET @Sql = @Sql + '('+@Ids + ') ORDER BY UserName ASC FOR XML PATH('''') ) AS Emails  ) AS ME'

Exec sp_executesql @Sql,@Params, @val=@EmailIds OUTPUT

SELECT @EmailIds AS EmailIds

Retrieving the last inserted ID value in sql server 2005

SELECT @@IDENTITY

It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.@@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.

SELECT SCOPE_IDENTITY()

It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.

SELECT IDENT_CURRENT(‘tablename’)

It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.

To avoid the potential problems associated with adding a trigger later on, always use SCOPE_IDENTITY() to return the identity of the recently added row in your T SQL Statement or Stored Procedure.

How to centralize your SQL Server Event Logs

SQL Server running on Windows provides a wonderful feature many DBA's overlook. It's called the Event Log. SQL Server will automatically record all sorts of info about what SQL is doing into the Event Log. I am sure we have all read the Event Log looking for things like why a server rebooted unexpectedly or to try to figure out why something is not working. But there is a lot of info in there that we may overlook. Especially since most of it does not really apply to SQL.

There are many modern day tools that will make this function very easy, but they typically cost a lot of money. Not that I am opposed to spending the boss's money, but sometimes the boss will not let me. So I came up with a process that will get some important info about SQL into my hands without having to spend time on every single instance setting up individual alerts and SQL Agent jobs to find this information.

Step 1 - create the database

USE [MASTER]
GO

CREATE DATABASE [DBA]
GO

Step 2 - Create the Table

USE [DBA]

GO-----------------------------------
SET ANSI_NULLS ON
GO
-----------------------------------
SET QUOTED_IDENTIFIER ON
GO
-----------------------------------
CREATE TABLE [dbo].[EventLogStaging](
[RecordNumber] [int] NOT NULL,
[Category] [int] NOT NULL,
[ComputerName] [nvarchar](250) NOT NULL,
[EventCode] [int] NOT NULL,
[EventType] [int] NOT NULL,
[Message] [nvarchar](4000) NULL,
[SourceName] [nvarchar](250) NOT NULL,
[TimeGenerated] [datetime] NULL,
[TimeWritten] [datetime] NOT NULL
) ON [PRIMARY]
-----------------------------------
GO

Step 3 - Create a VB script as EventLog2DB.vbs

strComputer = "."

Set objConn = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")
objConn.Open "Provider=SQLOLEDB.1;Data Source=.;
Initial Catalog=DBA;Integrated Security=SSPI"

objRS.CursorLocation = 3
objRS.Open "SELECT * FROM EventLogStaging" , objConn, 3, 3
' Get to the Event Log
Set objWMIService = GetObject("winmgmts:" _& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
' get the events we want 
query = "Select * from __InstanceCreationEvent" _& " " & "where TargetInstance isa 'Win32_NTLogEvent'" _& " " & "and TargetInstance.Logfile = 'Application'" _& " " & "and (TargetInstance.EventType = 1 or TargetInstance.EventType = 2)" _& " " & "and (TargetInstance.SourceName like 'MSSQL%')"
' get ready to insert into our DBA table 
Set colMonitoredEvents = objWMIService.ExecNotificationQuery(query)
Do
Set objLatestEvent = colMonitoredEvents.NextEvent
objRS.AddNew
objRS("RecordNumber") = objLatestEvent.TargetInstance.RecordNumber
objRS("Category") = objLatestEvent.TargetInstance.Category
objRS("ComputerName") = objLatestEvent.TargetInstance.ComputerName
objRS("EventCode") = objLatestEvent.TargetInstance.EventCode
objRS("EventType") = objLatestEvent.TargetInstance.EventType
objRS("Message") = objLatestEvent.TargetInstance.Message
objRS("SourceName") = objLatestEvent.TargetInstance.SourceName
objRS("TimeGenerated") = WMIDateStringToDate(objLatestEvent.TargetInstance.TimeGenerated)
objRS("TimeWritten") = WMIDateStringToDate(objLatestEvent.TargetInstance.TimeWritten)
objRS.UpdateLoop
' if we ever finish, we close cleanly.
objRS.Close
objConn.CloseSet objRS = Nothing
Set objConn = Nothing
'******************************************************************************
'* This conversion is necessary because WMI uses a different date/time format *
'******************************************************************************
Function WMIDateStringToDate(dtmInstallDate)
WMIDateStringToDate = CDate(Mid(dtmInstallDate, 5, 2) & "/" & _
Mid(dtmInstallDate, 7, 2) & "/" & Left(dtmInstallDate, 4) _& " " & Mid (dtmInstallDate, 9, 2) & ":" & _
Mid(dtmInstallDate, 11, 2) & ":" & Mid(dtmInstallDate, _13, 2))
End Function

Hey, what's this VB script doing to my system? Is it safe?

strComputer = "."

Set objConn = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")
objConn.Open "Provider=SQLOLEDB.1;Data Source=.;
Initial Catalog=DBA;Integrated Security=SSPI"
Here we are simply making a connection to the local instance on the  server
where this script is run from. You can see that we are using  integrated
security and the database we created called DBA. The  strComputer = "."
is a variable that we pass later on in the script.

objRS.CursorLocation = 3
objRS.Open "SELECT * FROM EventLogStaging" , objConn, 3, 3
' Get to the Event Log
Set objWMIService = GetObject("winmgmts:" _& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
The connection we made on the previous step is connecting the table we  created
earlier called EventLogStaging. It is also going to connect to  the local
Event Log service.


Here you see we connect to and read from the Event Log. We are only  interested
in the Application log. That is where SQL likes to put its  info. We are only
interested in event types of 1 and 2. Those are  warnings and errors. The source
name of MSSQL% will guarantee that we  get the event logs of all the instances
installed.


' get ready to insert into our DBA table 
Set colMonitoredEvents = objWMIService.ExecNotificationQuery(query)
Do
Set objLatestEvent = colMonitoredEvents.NextEvent
objRS.AddNew
objRS("RecordNumber") = objLatestEvent.TargetInstance.RecordNumber
objRS("Category") = objLatestEvent.TargetInstance.Category
objRS("ComputerName") = objLatestEvent.TargetInstance.ComputerName
objRS("EventCode") = objLatestEvent.TargetInstance.EventCode
objRS("EventType") = objLatestEvent.TargetInstance.EventType
objRS("Message") = objLatestEvent.TargetInstance.Message
objRS("SourceName") = objLatestEvent.TargetInstance.SourceName
objRS("TimeGenerated") = WMIDateStringToDate(objLatestEvent.TargetInstance.TimeGenerated)
objRS("TimeWritten") = WMIDateStringToDate(objLatestEvent.TargetInstance.TimeWritten)
objRS.UpdateLoop

Step 4 - Create the SQL agent job to insert records in real time.

Pay attention to the line that says @command=N'cscript "E:\Monitor\EventLog2DB.vbs"',

That location E:\Monitor is where I placed the VB script we made. You can put yours wherever you want, but change that line to where you put it. Like C:\Monitor.

Also, just in case you do not run your SQL Server in mixed mode authentication, you will have to change the line that says @owner_login_name=N'sa', to a user that can run the job.

USE [msdb]
GO
------------------------------------------------------
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
------------------------------------------------------
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategoriesWHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback------------------------------------------------------
END
------------------------------------------------------
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Monitor Event Log',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUTIF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback------------------------------------------------------
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'always running',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'CmdExec',
@command=N'cscript "E:\Monitor\EventLog2DB.vbs"', 
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'always',
@enabled=1,
@freq_type=64,
@freq_interval=0,
@freq_subday_type=0,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20100831,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'check every 1 minute',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=1,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20100901,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackCOMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
---------------------------------------------
GO

What it creates is a new SQL Server Agent job called Monitor Event Log.
It is set to start every time the SQL Agent starts and every 1 minute
after that. Of course, every minute the SQL Agent Job sees the previous
job still running, it will just skip it. Basically, we want our little
VB script to always be running. And this job will do just that.

Step 5 - Test that the events are being inserted.

Open SSMS and run a query on the instance you are monitoring. Copy and paste the code into your window.

raiserror ('working great',16,1) with log
The 'raiserror' command will write an event to the Windows Application log.
SELECT * FROM [DBA].[dbo].[EventLogStaging]
order by TimeWritten desc

Well, so it's working. Great! Now I have another place to see event log info. So why do I really care about that? Let's not forget about another feature SQL has had for many years called "SQL Server event forwarding". Consider that you might have 50 instances of SQL under your watchful eye. With a quick adjustment to the SQL Server Agent properties, you can forward SQL events from the Event log to where this job and VB script script are running.

(To enable that option go to Sql Server Agent -> Advanced -> Check 'Forward Events to different server', select a server name where the logs needs to be inserted.

Now I have a table with ALL my events in one place. I have used this method on over 150 instances forwarding their events to a central monitor server.

(Ref http://www.sqlservercentral.com/articles/Monitoring/71390/)

Use a Compiled Setup Project to Install the Windows Service

Assuming you have configured the Windows Service project, follow these steps to add a deployment project that packages the service application so that the service application can be installed:

1. Add a new project to your LogWriterService project. To do this, follow these steps:
   
   1. In Solution Explorer, right-click Solution 'LogWriterService' (1 project), point to Add, and then click New Project.
      
   2. Click Setup and Deployment Projects under Project Types, and then click Setup Project under Templates.
      
   3. In the Name text box, type ServiceSetup.
      
   4. Type C:\ in the Location text box, and then click OK.
      
2. Tell the deployment project what to package. To do this, follow these steps:
   
   1. In Solution Explorer, right-click ServiceSetup, point to Add, and then click Project Output
      
      
   2. In the Add Project Output Group dialog box, in the Project box, click LogWriterService
      
      
   3. Click Primary Output, and then click OK.
      
3. For correct installation, add only primary output. To add the custom actions, follow these steps:
   
   1. In Solution Explorer, right-click ServiceSetup, point to View, and then click Custom Actions
      
      
   2. Right-click Custom Actions, and then click Add Custom Action.
      
   3. Click Application Folder, and then click OK.
      
   4. Click Primary output from LogWriterService (Active), and then click OK.
      
      
      Notice that Primary output appears under Install, Commit, RollbackUninstall.
      and
4. By default, setup projects are not included in the build configuration. To build the solution, use one of the following methods:
   
   • Method 1
     1. Right-click LogWriterService, and then click Build.
        
     2. Right-click ServiceSetup, and then click Build.
        
   • Method 2
     1. On the Build menu, click Configuration Manager to build the whole solution.
        
     2. Click to select the Build check box for ServiceSetup.
        
     3. Press F7 to build the whole solution. When the solution is built, you have a complete installation package that is available for the service.
        
5. To install the newly built service, right-click ServiceSetup, and then click Install.
   
6. In the ServiceSetup dialog box, click Next three times. Notice that a progress bar appears while the service installs.
   
7. When the service is installed, click Close.