Exporting SQL Server Procedures and Functions to Files

Sometimes it is necessary to export all or some of the stored procedures and functions in a database to files (one file per procedure). This is useful for example if you want to place all your database scripts under source control.

The bcp utility shipped with SQL Server can help. The following query generates one bcp command line per procedure. All commands can then be copied to a batch file and executed from the command line.

In the query below replace and with the appropriate values. Also note that the -T parameter stands for Trusted Authentication, but it is possible to specify a username and password using the -U and -P parameters of the bcp utility (more details and other parameters here).


select 'bcp "SELECT routine_definition from .information_schema.routines where routine_schema = ''' + routine_schema + ''' and routine_name='''
+ routine_name + '''" QueryOut "c:\temp\procs\'
+ routine_schema + '.' + routine_name + '.sql" -c -S -T'
from information_schema.routines

Generate INSERT statements from SQL Server table data

It is often necessary to copy data between two tables (for example between staging and production databases). SQL Server makes this task rather complicated (you have to resort to export/import). In MySQL it is possible to generate INSERT statements for all the records in a table, and some SQL Server client tools (such as the somewhat immature but excellent Toad) also do this.

The really low cost solution though is to use this stored procedure:
http://vyaskn.tripod.com/code.htm#inserts

SQL Server: Executing Jobs Synchronously

SQL Server jobs can be started from T-SQL using msdb.dbo.sp_start_job:

EXEC msdb.dbo.sp_start_job @job_name = 'My job name'

This call however will return immediately (i.e. the job executes asynchronously). Sometimes it is desirable for the job to be executed synchronously.  The following procedure accomplishes this.

CREATE PROCEDURE execute_job_synchronously @jobID UNIQUEIDENTIFIER, @jobName NVARCHAR(500), @timeoutSeconds INT = 60 AS BEGIN SET NOCOUNT ON DECLARE @start DATETIME DECLARE @end DATETIME DECLARE @status INT IF @jobID IS NULL SELECT @jobID = job_id FROM msdb.dbo.sysjobs WHERE [name] = @jobName SET @start = GETDATE() SET @end = DATEADD(s, @timeoutSeconds, @start) SET @status = 0 EXEC msdb.dbo.sp_start_job @job_id = @jobID WAITFOR DELAY '00:00:01' CREATE TABLE #CURRENT_JOB_STATUS ( [Job ID] UNIQUEIDENTIFIER, [Last Run Date] NVARCHAR(50), [Last Run Time] NVARCHAR(50), [Next Run Date] NVARCHAR(50), [Next Run Time] NVARCHAR(50), [Next Run Schedule ID] NVARCHAR(50), [Requested To Run] NVARCHAR(50), [Request Source] NVARCHAR(50), [Request Source ID] NVARCHAR(50), [Running] NVARCHAR(50), [Current Step] NVARCHAR(50), [Current Retry Attempt] NVARCHAR(50), [State] INT) INSERT INTO #CURRENT_JOB_STATUS EXEC master.dbo.xp_sqlagent_enum_jobs 1, '', @jobID SELECT TOP 1 @status = State FROM #CURRENT_JOB_STATUS WHILE (@status != 4 AND GETDATE() < @end) BEGIN WAITFOR DELAY '00:00:01' TRUNCATE TABLE #CURRENT_JOB_STATUS INSERT INTO #CURRENT_JOB_STATUS EXEC master.dbo.xp_sqlagent_enum_jobs 1, '', @jobID SELECT TOP 1 @status = State FROM #CURRENT_JOB_STATUS END DECLARE @message VARCHAR(1000) IF @status != 4 BEGIN SET @message = 'Timeout (' + CONVERT(VARCHAR(20), @timeoutSeconds) + ' seconds) while waiting for job "' + ISNULL(@jobName, @jobID) + '" to complete' RAISERROR(@message, 16, 1) END ELSE BEGIN SET @message = 'The job "' + ISNULL(@jobName, @jobID) + '" failed' IF 0 = (SELECT last_run_outcome FROM msdb.dbo.sysjobservers WHERE (CONVERT(FLOAT, last_run_date) * 1000000) + last_run_time = (SELECT MAX((CONVERT(FLOAT, last_run_date) * 1000000) + last_run_time) FROM msdb.dbo.sysjobservers WHERE (job_id = @jobID)) AND job_id = @jobID) RAISERROR(@message, 16, 1) END END GO

Usage

Parameters:

• @jobID UNIQUEIDENTIFIER (optional, only required if @jobName is not provided)
• @jobName NVARCHAR(500) (optional, only required if @jobID is not provided)
• @timeoutSeconds INT (optional, default 60 seconds)

Remarks:

• The procedure will throw an error if the timeout period is reached before the job completes
• The procedure will throw an error if the job fails

Examples:

EXEC common.dbo.execute_job_synchronously '2696980E-1E38-43C4-8350-29DBFC56974D', null, 30

EXEC common.dbo.execute_job_synchronously null, ‘My cool job’

EXEC common.dbo.execute_job_synchronously null, ‘My cool job’, 3600

PONG

Surface Computing

Cool Microsoft Surface Computing demo

 

http://link.brightcove.com/services/player/bcpid932579976?bclid=932553050&bctid=933742930

Cool Phone Hacks

I came across these neat services (free during beta testing) on http://4hourworkweek.com.

 

http://jott.com  uses voice recognition to transcribe your spoken message into an SMS or email. The way they put it, "send SMS with your voice." Great for emailing yourself reminders, too.

 

http://pinger.com  lets you send voicemail to people without calling them. Perfect for late night messages or when you don't really want to talk to the recipient... (Next courtship ritual: breaking up on pinger.) Also great for messaging multiple people at once. Think of it as voice email.

 

http://grandcentral.com is your personal assistant that screens and routes calls and much, much more. My favorite feature: designating certain callers as SPAM. Future calls from those numbers automatically get a "number not in service" message when they call you. Genius.

 

 

The Visit

I thought this was really cool--a sign of things to come in our society. Crowd-financed movies... (Maybe there's hope for humanity after all.) I look forward to the day when blockbusters are financed with lunch money by kids who vote on what happens to Spidey in the next installment. (There's no hope for humanity.)

 

http://thevisitmovie.blogspot.com/

The web is officially getting scary

Can you guess guess how much information about YOU is scattered on the web?

 

http://www.zoominfo.com/

Determining the next identity value for a table

SELECT IDENT_CURRENT('Table1') + IDENT_INCR('Table1')

Also:

DBCC CHECKIDENT ('table_name', NORESEED)

To reseed the identity value:

DBCC CHECKIDENT ('table_name', RESEED, 10000)