SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Error handling in long-running batch jobs
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 12/18/2000 :  08:51:56  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Andy writes "We are developing a set of stored procedures that will run overnight, scheduled by the SQL Server 7 Job Scheduler. These jobs perform various (potentially) long-running activities, and result in large and complex changes in our data.

In order for our production support team to sucessfully support these jobs, we want to output progress messages and, in the event of failure, error messages. It's possible that, for a given batch run, there could be thousands of messages being written.

Initially, we used PRINT statements, and captured the output of the jobs to a file. This does capture all the info required, but is not exactly an elegant solution. Since the action required in response to a problem relies heavily on this information, we decided that the obvious place to store the info is in SQL Server tables, what we've called the Batch Log, and provide a nice user interface to view the messages, raise alerts, etc.

We've run into a logical catch-22 though (I'm sure it's obvious to everyone). A discrete unit of work (a transaction) may generate multiple output messages, in some cases hundreds. If this transaction fails, we need to rollback. But we want to keep the messages in the Batch Log tables. Problem.

We've come up with a few ugly options, all revolving around the idea of somehow stepping around the transaction rollback, carrying the log information forward and committing it. One suggestion is, when logging a message, call xp_cmdshell that calls a batch file, that calls ISQL to insert the message row. Another is to somehow decipher the log after the rollback.

This seems to be a tricky problem. Can you offer any advice?

Thanks,
Andy."

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 12/18/2000 :  10:26:12  Show Profile  Visit robvolk's Homepage  Reply with Quote
If your INSERT statements to your log tables are done with stored procedures, you might be able to wrap them inside their own transaction and commit them. A ROLLBACK in the calling procedure shouldn't affect this (? Can't say I've tried it so I'm probably wrong).

Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 12/18/2000 :  11:19:49  Show Profile  Visit graz's Homepage  Reply with Quote
I've been noodling on this for a while and haven't come up with anything great. I kind of like the idea about calling xp_cmdshell but I think it will be slow. I think your best bet might be to hold that status information in a series of variables and at the end write that information into a table.

[url="http://www.SQLTeam.com"]SQLTeam.com[/url]
Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 12/18/2000 :  11:55:57  Show Profile  Visit robvolk's Homepage  Reply with Quote
Not sure if this will help--SAVE TRANSACTION. It allows you to set a save point for a transaction, which may permit you to save your log records and then roll back the failed transaction. Books Online strikes again!

Go to Top of Page

andypope
Starting Member

USA
3 Posts

Posted - 01/15/2001 :  14:26:04  Show Profile  Reply with Quote
I think that using SAVEPOINTs doesn't really help in our situation.

Our stored procedure is long running (half an hour at least at this stage). When it runs, we don't really know what it's going to do, but it goes through a lot of steps, and modifies thousands of records in about 25 different tables. Because we don't know in advance what it's going to do (the business logic is quite complex and many nested stored procs are called), we want to output messages to a log so we have the "story" of what happened. If something fails (either due to a business logic rule or a SQL Server error) we want to roll back ALL of the updates to the tables, but keep the log messages.

For example...
BEGIN TRANSACTION

WHILE blah blah
INSERT a record into the LOG
UPDATE some records in tables 1, 2 and 3
INSERT some records into table 4
INSERT a record into the LOG
DELETE some records from table 2
INSERT a record into the LOG
etc.
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN @@ERROR
END
END WHILE

IF @@ERROR = 0
COMMIT TRANSACTION

In this situation of course, we do error checking after every statement, but you get the general idea. You can see from this that when we rollback, we rollback everything, including the log messages. Savepoints don't really help us here because we would need to rollback all the way to the very first savepoint (since we need to roll everything back) which would include the log messages.

If anyone can find any holes in my logic here, please point them out. I'd really like to be proven wrong on this!

Cheers,
Andy.
Go to Top of Page

andypope
Starting Member

USA
3 Posts

Posted - 01/15/2001 :  15:04:54  Show Profile  Reply with Quote
We've found a solution. We've tested it. And it works.

In essence, what we need to do here is find some way to step outside the bounds of the transaction - not something to be taken lightly if you want to maintain transaction/data integrity of course, but that's precisely what we want to do. So here's how we do it...

I alluded to using xp_cmdshell and ISQL in the original post and we did some tests that proved it to be too slow. All that shelling out was just too tiresome.

So we tried SQL-DMO instead. Works like a charm.

We create a SQLServer object, log in using NT Authentication (so we don't need to store usernames or passwords anywhere), get the database and run an SQL statement against it. It all operates under a new connection, so everything is performed outside the current transaction. And we only need to get the objects once at the beginning of the process - the connection is maintained until we destroy the objects at the end. Each insert into the message log takes very little time once the connection is open, as you'd expect.

Of course, we need to ensure that we only ever process log messages this way (to ensure data integrity).

Here's some sample code. Obviously I haven't included what the stored procs do, or what our error message numbers are, but you'll get the general idea...

DECLARE @oSQLServer INTEGER
DECLARE @oDatabase INTEGER
DECLARE @oQueryResults INTEGER
DECLARE @CurrentDB VARCHAR(255)
DECLARE @Return INTEGER
DECLARE @SQL VARCHAR(8000)
DECLARE @Source VARCHAR(255)
DECLARE @Length INTEGER

DECLARE @JobName CHAR(10)
DECLARE @JobID INTEGER
DECLARE @UserID VARCHAR(30)
DECLARE @Error INTEGER
DECLARE @ErrorRet INTEGER
DECLARE @ErrorMsg VARCHAR(255)

SET @UserID = USER

SET @JobID = 50
SET @JobName = 'Test job'

-- Batch start message
EXEC spi_BatchLog @JobID, @JobName, 2126, '', 0, @UserID
SELECT @Error = @@ERROR


BEGIN TRAN -- This is never committed, it's just a test

-- Insert another message - this one will be rolled back
-- (This is just to give is something to rollback in our test proc)
EXEC spi_BatchLog @JobID, @JobName, 1000, '', 0, @UserID

-- Create the SQLServer object
EXEC @Error = sp_OACreate 'SQLDMO.SQLServer', @oSQLServer OUT
IF @Error <> 0
GOTO OA_Error

-- Set the login process to use NT Authentication
EXEC @Error = sp_OASetProperty @oSQLServer, 'LoginSecure', -1
IF @Error <> 0
GOTO OA_Error

-- Connect to server using NT Authentication
EXEC @Error = sp_OAMethod @oSQLServer, 'Connect', NULL, @@SERVERNAME
IF @Error <> 0
GOTO OA_Error

-- Verify the connection
EXEC @Error = sp_OAMethod @oSQLServer, 'VerifyConnection', @Return OUTPUT
IF @Error <> 0
GOTO OA_Error
IF @Return = 0
GOTO OA_Error

-- Set the database object to be the current database
-- (We do this so we don't need to execute the SQL against the
-- SQLServer object, which would require explicit DB prefixing)
SELECT @CurrentDB = DB_NAME()
SELECT @CurrentDB = 'Databases("' + RTRIM(@CurrentDB) + '")'
EXEC @Error = sp_OAGetProperty @oSQLServer, @CurrentDB, @oDatabase OUT
IF @Error <> 0
GOTO OA_Error

-- Build the SQL string
SET @SQL = 'EXECUTE spi_BatchLog ' + CONVERT(CHAR(12),@JobID) + ', ''' + @JobName + ''', 1001, '''', 0, ''' + @UserID + ''''

-- Execute the SQL (this will NOT be rolled back)
-- We could use ExecuteImmediate here, but it doesn't return a result
-- and we found while debugging that you really NEED that
SET @Length = LEN(@SQL)
SET @ErrorMsg = '' -- Must initialize the @ErrorMsg string
EXEC @Error = sp_OAMethod oDatabase, 'ExecuteWithResultsAndMessages', @oQueryResults OUTPUT, @Length = @Length, @Command = @SQL , @Messages = @ErrorMsg OUTPUT
IF @Error <> 0
GOTO OA_Error

-- Destroy the objects
EXEC @Error = sp_OADestroy @oSQLServer
EXEC @Error = sp_OADestroy @oDatabase
EXEC @Error = sp_OADestroy @oQueryResults


-- For testing, pretend there was an error
-- (It will rollback the 1000 message, but keep the 1001 message)
SET @Error = 50001 -- Our SQL Server test message
GOTO CriticalError

-----------------------------------------------
CriticalError:
ROLLBACK TRAN

-- Get the message description
SELECT
@ErrorMsg = CONVERT(CHAR(10), @Error) + ' ' + FORMATMESSAGE(@Error)

-- Write the error to the log
EXEC spi_BatchLog @JobID, @JobName, 2135, @ErrorMsg, 3, @UserID

RETURN @Error



-----------------------------------------------
OA_Error:
ROLLBACK TRAN

-- Get the error text
EXEC sp_OAGetErrorInfo @oSQLServer, @Source OUT, @ErrorMsg OUT
SELECT
@ErrorMsg = CONVERT(CHAR(16), @Error) + ': ' + @ErrorMsg + ' (Source: ' + @Source + ')'

-- Write the error to the log
EXEC spi_BatchLog @JobID, @JobName, 2135, @ErrorMsg, 3, @UserID

EXEC @Error = sp_OADestroy @oSQLServer
EXEC @Error = sp_OADestroy @oDatabase
EXEC @Error = sp_OADestroy @oQueryResults

RETURN @Error

Feedback would be appreciated.

Best regards,
Andy.

Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 01/15/2001 :  15:15:24  Show Profile  Visit robvolk's Homepage  Reply with Quote
Andy-

Brilliant! I was starting to play with creating an ADO connection, then thinking of creating a COM object, but your solution is perfect. Thank you for this idea.

-Rob

Go to Top of Page

tansaku
Starting Member

3 Posts

Posted - 07/06/2005 :  00:21:00  Show Profile  Reply with Quote
A very inventive solution indeed - I've run it locally and it works. However when I run with the logging operation inside a cursor loop the whole thing seems to slow down enormously.

The process usually takes about 2 seconds. Adding all the code to create the OLE connection doesn't change that - nor a single log statement outside the cursor loop (I say log, actually its a statement creating a table).

However when I set it so that one statement inside the cursor loop (an insert) goes over the OLE connection the whole thing just takes forever - it hasn't finished yet - been running for half an hour.

I was wondering if anyone else had experienced this? Is there some restriction on this kind of operation in loops, or can only one statement be sent per connection?

Many thanks in advance.

CHEERS> SAM

p.s. my code (the procedure is called from the spunit framework which runs everything from within a transaction):

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE usp_SimulateByEventLog
@startDatetime datetime, --= CONVERT(datetime,'2005-01-28 03:59:52.950'),
@CeregoUser_ID varchar(16) = '350251001585434',
@noStudyOperations int = 2,
@studyInterval int = 24, -- in hours
@lessonsPerOperation int = 2,
@lessonInterval int = 2, -- in mins
@Min float = 0.6,
@Range float = 0.3,
@Engine_ID int = 1,
@Verbosity nvarchar(8) = 'QUIET'
AS
BEGIN

-- set up OLE objects for permanent storage

DECLARE @oSQLServer INTEGER
DECLARE @oDatabase INTEGER
DECLARE @oQueryResults INTEGER

DECLARE @CurrentDB VARCHAR(255)
DECLARE @Return INTEGER
DECLARE @Source VARCHAR(255)
DECLARE @Length INTEGER


DECLARE @Error INTEGER
DECLARE @ErrorRet INTEGER
DECLARE @ErrorMsg VARCHAR(255)

-- Create the SQLServer object
EXEC @Error = sp_OACreate 'SQLDMO.SQLServer', @oSQLServer OUT
IF @Error <> 0
GOTO OA_Error

-- Set the login process to use NT Authentication
EXEC @Error = sp_OASetProperty @oSQLServer, 'LoginSecure', -1
IF @Error <> 0
GOTO OA_Error

-- Connect to server using NT Authentication
EXEC @Error = sp_OAMethod @oSQLServer, 'Connect', NULL, @@SERVERNAME
IF @Error <> 0
GOTO OA_Error

-- Verify the connection
EXEC @Error = sp_OAMethod @oSQLServer, 'VerifyConnection', @Return OUTPUT
IF @Error <> 0
GOTO OA_Error
IF @Return = 0
GOTO OA_Error

-- Set the database object to be the current database
-- (We do this so we don't need to execute the SQL against the
-- SQLServer object, which would require explicit DB prefixing)
SELECT @CurrentDB = DB_NAME()
SELECT @CurrentDB = 'Databases("' + RTRIM(@CurrentDB) + '")'
EXEC @Error = sp_OAGetProperty @oSQLServer, @CurrentDB, @oDatabase OUT
IF @Error <> 0
GOTO OA_Error



-- remove the temporary tables associated with the previous simulation

if exists (select * from sysobjects where id = object_id(N'[dbo].[tblTmpRatios2]') )--and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblTmpRatios2]

if exists (select * from sysobjects where id = object_id(N'[dbo].[tblTmpTrace]') )--and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblTmpTrace]

if exists (select * from sysobjects where id = object_id(N'[dbo].[tblTmpStudyData]') )--and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblTmpStudyData]

-- create a temporary table to store the ratios generated by our simulated user

CREATE TABLE tblTmpRatios2(
TmpRatioID int IDENTITY(1,1),
Item_ID int,
Upload_ID int,
Lesson_ID int,
Course_ID int,
CeregoUser_ID varchar(16),
Ratio float,
TimeStamp datetime
)

DECLARE @limit int
SET @limit = (0 - @noStudyOperations) * @lessonsPerOperation

DECLARE @sessionAt datetime
SET @sessionAt = @startDatetime

-- creates a table to store a trace of user activity item by item

DECLARE @FieldSep VARCHAR(2)
SET @FieldSep = ', '

DECLARE @createTraceTable varchar(8000)
SET @createTraceTable = 'CREATE TABLE tblTmpTrace(Timestamp datetime,'

DECLARE @Course_ID int
DECLARE @Lesson_ID int
DECLARE @Item_ID int
DECLARE @new_lesson_ID int
DECLARE @new_item_ID int
DECLARE @TmpRatioID int

/****************************
Sam's fix to Herry's fix
****************************/

SELECT TOP 1 @Course_ID = Course_ID
FROM tblUserCourse WHERE CeregoUser_ID = @CeregoUser_ID
--print @course_ID
/****************************
Herry's fix
****************************/

SELECT @createTraceTable=@createTraceTable+'Item'+CONVERT(varchar(5),Item_ID)+ ' NVARCHAR(16) DEFAULT ''''' + @FieldSep FROM tblLessonItem WHERE Lesson_ID IN
(SELECT Lesson_ID FROM tblCourseLesson WHERE Course_ID = @Course_ID)
SET @createTraceTable = LEFT(@createTraceTable, LEN(@createTraceTable) - LEN(@FieldSep))
SET @createTraceTable = @createTraceTable + ')'
--PRINT @createTraceTable
EXEC (@createTraceTable)
-- us this below to leave the trace table behind after operation
--EXEC usp_ExecuteOutsideTransaction @createTraceTable

-- creates a table that will hold all the study data we are interested in

DECLARE @createStudyDataTable varchar(8000)
SET @createStudyDataTable = 'CREATE TABLE tblTmpStudyData(
Item_ID int,
Lesson_ID int,
Cue nvarchar(32),
Response nvarchar(32),
Urgency float,
now datetime,
max_timestamp datetime,
max_ratio float,
TimeStamp datetime,
Ratio float
)'

--EXEC usp_ExecuteOutsideTransaction_SetUp @oSQLServer OUTPUT, @oDatabase OUTPUT, @oQueryResults OUTPUT
--EXEC usp_ExecuteOutsideTransaction @oSQLServer, @oDatabase, @oQueryResults, @createStudyDataTable
--EXEC (@createStudyDataTable)

SET @Length = LEN(@createStudyDataTable)
SET @ErrorMsg = '' -- Must initialize the @ErrorMsg string
EXEC @Error = sp_OAMethod @oDatabase, 'ExecuteWithResultsAndMessages', @oQueryResults OUTPUT, @Length = @Length, @Command = @createStudyDataTable , @Messages = @ErrorMsg OUTPUT
IF @Error <> 0
GOTO OA_Error

DECLARE @insertStudyDataTable varchar(8000)
SET @insertStudyDataTable = '
INSERT INTO tblTmpStudyData (Item_ID, Lesson_ID, Cue, Response, Urgency, now, max_timestamp, max_ratio, TimeStamp, Ratio)
(SELECT u.Item_ID, u.Lesson_ID, u.Cue, u.Response, u.Urgency, u.now, u.max_timestamp, u.max_ratio, r.TimeStamp, r.Ratio
FROM tblTmpUrgencies AS u
LEFT JOIN tblTmpRatios2 AS r ON u.Item_ID = r.Item_ID)'

DECLARE @Upload_ID int

DECLARE upload_cursor CURSOR FOR
SELECT Upload_ID, MIN([TimeStamp]) as Start
FROM tblEventLog
WHERE (CeregoUser_ID = @CeregoUser_ID)
GROUP BY Upload_ID
ORDER BY Start

IF @Verbosity = 'VERBOSE'
BEGIN
print @CeregoUser_ID
--DECLARE @CeregoUser_ID varchar(16)
--SET @CeregoUser_ID = '350251001585434'
--SELECT DISTINCT Upload_ID
--FROM tblEventLog
--WHERE CeregoUser_ID = @CeregoUser_ID
END

OPEN upload_cursor

FETCH NEXT FROM upload_cursor
INTO @Upload_ID, @sessionAt

DECLARE @subtraction int
SET @subtraction = -1

WHILE @@FETCH_STATUS = 0
BEGIN

-- since we don't currently have an event for the data request have to subtract some value from the first event we saw

SET @sessionAt = DATEADD(mi,@subtraction,@sessionAt)


IF @Verbosity = 'VERBOSE'
BEGIN
print @Upload_ID
END
-- now insert ratios for recommended items
-- this will insert the ratios from the tblTmpRatios2 table and put them in tblRatioSummary

EXEC usp_InsertRatioSummariesAndCalculateUrgencies @CeregoUser_ID, @sessionAt, @Upload_ID, 'tblTmpRatios2','tblTmpUrgencies', @Engine_ID = @Engine_ID

-- the next round of suggested study will now be in tblTmpUrgencies

IF @Verbosity = 'VERBOSE'
BEGIN
print 'first sp executed'
print @sessionAt
SELECT * from tblTmpRatios2
select * from tblRatioSummary
END

-- clear tmpRatio table of any old ratios

DELETE FROM tblTmpRatios2

-- this statement grabs the event log for this user and places ratio summaries for this upload id
-- in tblTmpRatios2 - however these will be for the items the user originally studies
-- ideally this should be taken from engine, but at the moment only the complete one can handle dynamic tables
EXEC usp_PopulateRatioSummaries_Complete @Upload_ID, @Engine_ID, 'tblTmpRatios2','tblEventLog'

-- now we need to adjust the item_ids and lesson_ids in tblTmpRatios2

DECLARE rewrite_cursor CURSOR FOR SELECT TmpRatioID FROM tblTmpRatios2
OPEN rewrite_cursor

FETCH NEXT FROM rewrite_cursor
INTO @TmpRatioID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @new_lesson_ID = NULL
SET @new_item_ID = NULL
-- the newid creates randomness that we may want to avoid ...
SELECT TOP 1 @new_lesson_ID = Lesson_ID, @new_item_ID = Item_ID
FROM tblTmpUrgencies WHERE Item_ID NOT IN (SELECT Item_ID FROM tblTmpRatios2) ORDER BY newid()

IF(@new_lesson_ID IS NOT NULL AND @new_lesson_ID IS NOT NULL)
BEGIN
UPDATE tblTmpRatios2
SET Lesson_ID = @new_lesson_ID, Item_ID = @new_item_ID
WHERE TmpRatioID = @TmpRatioID
-- if we tracked the itemid swaps here we'd effectively have
-- item difficulty incorporated - actually no, because we can't rely on things repeating with the same frequency
END

FETCH NEXT FROM rewrite_cursor
INTO @TmpRatioID
END
CLOSE rewrite_cursor
DEALLOCATE rewrite_cursor


IF @Verbosity = 'VERBOSE'
BEGIN
print 'second sp executed'
--select * from tblTmpUrgencies
select * from .tblTmpRatios2
END

-- This stored procedure adds a line to the trace table for a rotated view of the users progress so it easier to follow the ERS

EXEC usp_InsTraceTable 'tblTmpTrace', 'tblTmpUrgencies', 'tblTmpRatios2'

-- This insert takes the current rows from tblTmpRatios2 and places them in tblTmpStudyData
-- so that we have a complete record of all study by this user (we are deleting the contents of tblTmpRatios2)

--EXEC usp_ExecuteOutsideTransaction @oSQLServer, @oDatabase, @oQueryResults, @insertStudyDataTable
--EXEC (@insertStudyDataTable)

SET @Length = LEN(@insertStudyDataTable)
SET @ErrorMsg = '' -- Must initialize the @ErrorMsg string
EXEC @Error = sp_OAMethod @oDatabase, 'ExecuteWithResultsAndMessages', @oQueryResults OUTPUT, @Length = @Length, @Command = @insertStudyDataTable , @Messages = @ErrorMsg OUTPUT
IF @Error <> 0
GOTO OA_Error


if exists (select * from dbo.sysobjects where id = object_id(N'tblTmpUrgencies') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table tblTmpUrgencies

IF @Verbosity = 'VERBOSE'
BEGIN

print 'third sp executed'
select * from tblRatioSummary
END

FETCH NEXT FROM upload_cursor
INTO @Upload_ID, @sessionAt
END

CLOSE upload_cursor
DEALLOCATE upload_cursor

--EXEC usp_InsertRatioSummariesAndCalculateUrgencies @CeregoUser_ID, @sessionTwo, -2, '.tmpRatios','tmpUrgencies'
IF @Verbosity = 'VERBOSE' OR @Verbosity = 'BASIC'
BEGIN
SELECT * FROM tblTmpTrace
SELECT * FROM tblTmpStudyData
END
--TRUNCATE TABLE .tmpTrace
--TRUNCATE TABLE .tmpStudyData

--EXEC usp_ExecuteOutsideTransaction_TearDown @oSQLServer, @oDatabase, @oQueryResults


EXEC @Error = sp_OADestroy @oSQLServer
EXEC @Error = sp_OADestroy @oDatabase
EXEC @Error = sp_OADestroy @oQueryResults
-----------------------------------------------

-----------------------------------------------
OA_Error:

-- Get the error text
EXEC sp_OAGetErrorInfo @oSQLServer, @Source OUT, @ErrorMsg OUT
SELECT
@ErrorMsg = CONVERT(CHAR(16), @Error) + ': ' + @ErrorMsg + ' (Source: ' + @Source + ')'

-- Display the error message
print @ErrorMsg

EXEC @Error = sp_OADestroy @oSQLServer
EXEC @Error = sp_OADestroy @oDatabase
EXEC @Error = sp_OADestroy @oQueryResults

END


-- SELECT REVERSE(Item_ID), (1), ('fred'),(RAND(UNICODE(CONVERT(nvarchar,CONVERT(binary,NEWID()))))),
--CONVERT(int,CONVERT(binary,NEWID())),UNICODE(CONVERT(nvarchar,CONVERT(binary,NEWID()))), NEWID() FROM tblRatioSummary

-- SELECT LEFT('0xBC661F64B8E5854290A92126A5A6BAE20000000000000000000000000000',10)
/*
INSERT INTO .tmpRatios ( Ratio,Item_ID, TimeStamp)
VALUES ( 0.3,1, @sessionOne)
INSERT INTO .tmpRatios ( Ratio,Item_ID, TimeStamp)
VALUES ( 0.3,2, @sessionOne)
INSERT INTO .tmpRatios ( Ratio,Item_ID, TimeStamp)
VALUES ( 0.5,3, @sessionOne)
INSERT INTO .tmpRatios ( Ratio,Item_ID, TimeStamp)
VALUES ( 0.5,4, @sessionOne)
*/










GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Go to Top of Page

kleine
Starting Member

1 Posts

Posted - 08/28/2006 :  16:38:36  Show Profile  Reply with Quote
I've run the script on SQl Server 2000.
But now with Sql Server 2005 the Connect with SQLDMO doesn't work.

I get no error desciption. Only error number -2147165949.


Any idea ?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/29/2006 :  01:38:41  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I think SQL DMO is no more with SQL Server 2005. It is called SMO. However, since DMO is deprecated in 2005, it still supports DMO. Not fully though.

http://www.microsoft.com/sql/prodinfo/overview/whats-new-in-sqlserver2005.mspx


Peter Larsson
Helsingborg, Sweden
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000