What could be the problem with the following script?

Question

Post reply

What could be the problem with the following script?

Mallikarjuna

SSC Veteran

Points: 221
More actions
February 11, 2004 at 2:23 am

#107630

The above script generating the following error?
Error Executing ADO.Activeconnection Property 0x80004005 Microsoft OLE DB Provider for ODBC Drivers [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
--start here
declare @rs INT, @nRes INT,@sSource VARCHAR(255),
@sDescription VARCHAR(255),@nOLEResult INT,@sqry varchar(300),
@sConstr varchar(100)
Execute @nRes = sp_OACreate 'ADODB.Recordset',@rs OUT
    IF @nRes <> 0
    BEGIN
        EXEC sp_OAGetErrorInfo @rs, @sSource OUT, @sDescription OUT
        SELECT 'Error Creating Recordset Object',
            hResult=convert(varbinary(4),@nOLEResult),
            Source=@sSource,
            escription=@sDescription">Description=@sDescription
        RETURN
    END
set @sqry = 'select count(*) from titles'
    EXECUTE @nOLEResult = sp_OASetProperty @rs, 'Source', @sqry
    IF @nOLEResult <> 0
    BEGIN
        EXEC sp_OAGetErrorInfo @rs, @sSource OUT, @sDescription OUT
        SELECT 'Error Executing ADO.Source Property',
            hResult=convert(varbinary(4),@nOLEResult),
            Source=@sSource,
            escription=@sDescription">Description=@sDescription
        RETURN
    END
    set @sConstr = 'Driver={SQL Server};Server=Apollolife;uid=sa;database=pubs'
    EXECUTE @nOLEResult = sp_OASetProperty @rs, 'ActiveConnection',@sConstr
    IF @nOLEResult <> 0
    BEGIN
        EXEC sp_OAGetErrorInfo @rs, @sSource OUT, @sDescription OUT
        SELECT 'Error Executing ADO.Activeconnection Property',
            hResult=convert(varbinary(4),@nOLEResult),
            Source=@sSource,
            escription=@sDescription">Description=@sDescription
        RETURN
    END
    EXECUTE @nOLEResult = sp_OAMethod @rs, 'Open', Null
    IF @nOLEResult <> 0
    BEGIN
        EXEC sp_OAGetErrorInfo @rs, @sSource OUT, @sDescription OUT
        SELECT 'Error Executing ADO.OPen Method',
            hResult=convert(varbinary(4),@nOLEResult),
            Source=@sSource, escription=@sDescription">Description=@sDescription
        RETURN
    END
    EXECUTE @nOLEResult = sp_OAGetProperty @rs, 'RecordCount', @sConstr
    IF @nOLEResult <> 0
    BEGIN
        EXEC sp_OAGetErrorInfo @rs, @sSource OUT, @sDescription OUT
        SELECT 'Error Executing ADO.RecordCount Property',
            hResult=convert(varbinary(4),@nOLEResult),
            Source=@sSource,
            escription=@sDescription">Description=@sDescription
        RETURN
    END
    EXECUTE @nOLEResult = sp_OADestroy @rs
February 16, 2004 at 8:00 am

This was removed by the editor as SPAM

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

Because you are trying to query a remote SQL server I would rewrite that code as:

SELECT a.Cnt

FROM OPENROWSET('SQLOLEDB','Apollo';'sa';'',

'SELECT COUNT(*) as Cnt FROM pubs.dbo.titles ORDER BY au_lname, au_fname') AS a

OR

Create a linked Server and

SELECT count(*) as Cnt

FROM LinkedSeverName.DBName.dbo.titles

and you DEFINETLY have a DSN setup problem (Check it in the ODBC Administrator)

* Noel