asked on

ADO with T-SQL

OK, so I figured that I could use ADO within T-SQL and retrieve the result generated from a FOR XML statement....

I have been trying to figure something out for months now, I have been able to retrieve the result in VB easily, but that is not one of the languages I can use, it was either ColdFusion or MS SQL, well in CF I get errors when trying to use ADO so gave up on that one, now I am hanging on to my last straw, which is to use T-SQL.

Today was the first time ever that I used sp_OACreate and I am getting some errors that I can't seem to solve.
The errors are:

ActiveConnection Error:
ADODB.Command      Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

Properties("Output Stream") Error:
ADODB.Command      Item cannot be found in the collection corresponding to the requested name or ordinal.

Execute Error:
Provider      Type mismatch.

The code is

DECLARE       @returnCode INT,
            @objStream INT,
            @objConnection INT,
            @objCommand INT,
            @errorSource VARCHAR(2550),
            @errorDescription VARCHAR(2550),
            @connectionString VARCHAR(1000),
            @returnVal INT

SET @connectionString = 'Data Source='+@@SERVERNAME+'; Provider=SQLOLEDB; Initial Catalog=dbCCDB_development; Integrated Security=SSPI'

EXEC @returnCode = sp_OACreate 'ADODB.Connection', @objConnection OUTPUT
IF @returnCode <> 0
BEGIN
      EXEC sp_OAGetErrorInfo @objConnection, @errorSource OUTPUT, @errorDescription OUTPUT
      SELECT 'Creating ADODB.Connection Error: ', @errorSource, @errorDescription
END

EXEC @returnCode = sp_OACreate 'ADODB.Stream', @objStream OUTPUT
IF @returnCode <> 0
BEGIN
      EXEC sp_OAGetErrorInfo @objStream, @errorSource OUTPUT, @errorDescription OUTPUT
      SELECT 'Creating ADODB.Stream Error: ', @errorSource, @errorDescription
END

EXEC @returnCode = sp_OACreate 'ADODB.Command', @objCommand OUTPUT
IF @returnCode <> 0
BEGIN
      EXEC sp_OAGetErrorInfo @objCommand, @errorSource OUTPUT, @errorDescription OUTPUT
      SELECT 'Creating ADODB.Command Error: ', @errorSource, @errorDescription
END

EXEC @returnCode = sp_OAMethod @objConnection,'Open',NULL,@connectionString,'sa',''
IF @returnCode <> 0
BEGIN
      EXEC sp_OAGetErrorInfo @objConnection, @errorSource OUTPUT, @errorDescription OUTPUT
      SELECT 'Connection Open Error: ', @errorSource, @errorDescription
END

EXEC @returnCode = sp_OASetProperty @objCommand, 'ActiveConnection', @objConnection
IF @returnCode <> 0
BEGIN
      EXEC sp_OAGetErrorInfo @objCommand, @errorSource OUTPUT, @errorDescription OUTPUT
      SELECT 'ActiveConnection Error: ', @errorSource, @errorDescription
END

EXEC @returnCode = sp_OASetProperty @objCommand, 'CommandText', "SELECT * FROM tblTQUser FOR XML AUTO, elements"
IF @returnCode <> 0
BEGIN
      EXEC sp_OAGetErrorInfo @objCommand, @errorSource OUTPUT, @errorDescription OUTPUT
      SELECT 'CommandText Error: ', @errorSource, @errorDescription
END

EXEC @returnCode = sp_OASetProperty @objCommand, 'Properties("Output Stream")', @objStream
IF @returnCode <> 0
BEGIN
      EXEC sp_OAGetErrorInfo @objCommand, @errorSource OUTPUT, @errorDescription OUTPUT
      SELECT 'Properties("Output Stream") Error: ', @errorSource, @errorDescription
END

EXEC @returnCode = sp_OAMethod @objStream,'Open'
IF @returnCode <> 0
BEGIN
      EXEC sp_OAGetErrorInfo @objStream, @errorSource OUTPUT, @errorDescription OUTPUT
      SELECT 'Open Error: ', @errorSource, @errorDescription
END

EXEC @returnCode = sp_OASetProperty @objCommand, 'Execute', NULL, NULL, adExecuteStream
IF @returnCode <> 0
BEGIN
      EXEC sp_OAGetErrorInfo @objCommand, @errorSource OUTPUT, @errorDescription OUTPUT
      SELECT 'Execute Error: ', @errorSource, @errorDescription
END

EXEC @returnCode = sp_OAMethod @objStream,'ReadText'
IF @returnCode <> 0
BEGIN
      EXEC sp_OAGetErrorInfo @objStream, @errorSource OUTPUT, @errorDescription OUTPUT
      SELECT 'ReadText Error: ', @errorSource, @errorDescription
END

SELECT @returnCode AS 'Result'

-- Clean up.
EXEC @returnCode = sp_OADestroy @objConnection
EXEC @returnCode = sp_OADestroy @objStream
EXEC @returnCode = sp_OADestroy @objCommand

Hilaire

Hi,

I think you don't need the connection object here
Command.ActiveConnection takes connection string directly

Also note I used db_name() to avoid hard-coding the initial_catalog

I also used sp_oamethod for the command.execute intead of setproperty

Here is what I have so far.
I'm stuck on the command.Execute for the moment

DECLARE @returnCode INT,
@objStream INT,
@objCommand INT,
@errorSource VARCHAR(2550),
@errorDescription VARCHAR(2550),
@connectionString VARCHAR(1000),
@returnVal INT

SET @connectionString = 'Data Source='+@@SERVERNAME+'; Provider=SQLOLEDB; Initial Catalog=' + db_name() + '; Integrated Security=SSPI'

EXEC @returnCode = sp_OACreate 'ADODB.Stream', @objStream OUTPUT
IF @returnCode <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objStream, @errorSource OUTPUT, @errorDescription OUTPUT
SELECT 'Creating ADODB.Stream Error: ', @errorSource, @errorDescription
END

EXEC @returnCode = sp_OACreate 'ADODB.Command', @objCommand OUTPUT
IF @returnCode <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objCommand, @errorSource OUTPUT, @errorDescription OUTPUT
SELECT 'Creating ADODB.Command Error: ', @errorSource, @errorDescription
END

EXEC @returnCode = sp_OASetProperty @objCommand, 'ActiveConnection', @connectionString
IF @returnCode <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objCommand, @errorSource OUTPUT, @errorDescription OUTPUT
SELECT 'ActiveConnection Error: ', @errorSource, @errorDescription
END

EXEC @returnCode = sp_OASetProperty @objCommand, 'CommandText', "SELECT * FROM tblTQUser FOR XML AUTO, elements"
IF @returnCode <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objCommand, @errorSource OUTPUT, @errorDescription OUTPUT
SELECT 'CommandText Error: ', @errorSource, @errorDescription
END

EXEC @returnCode = sp_OAMethod @objStream,'Open'
IF @returnCode <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objStream, @errorSource OUTPUT, @errorDescription OUTPUT
SELECT 'Open Error: ', @errorSource, @errorDescription
END

EXEC @returnCode = sp_OASetProperty @objCommand, 'Properties("Output Stream")', @objStream
IF @returnCode <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objCommand, @errorSource OUTPUT, @errorDescription OUTPUT
SELECT 'Properties("Output Stream") Error: ', @errorSource, @errorDescription
END

EXEC @returnCode = sp_OAMethod @objCommand, 'Execute', NULL, null, null, 1024
IF @returnCode <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objCommand, @errorSource OUTPUT, @errorDescription OUTPUT
SELECT 'Execute Error: ', @errorSource, @errorDescription
END
/*
EXEC @returnCode = sp_OAMethod @objStream,'ReadText'
IF @returnCode <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objStream, @errorSource OUTPUT, @errorDescription OUTPUT
SELECT 'ReadText Error: ', @errorSource, @errorDescription
END

SELECT @returnCode AS 'Result'
*/
-- Clean up.
EXEC @returnCode = sp_OADestroy @objStream
EXEC @returnCode = sp_OADestroy @objCommand

Tacobell777

ASKER

hey hey we are finally getting somewhere ;-))
This thing has been bothering me for months... ...
I'll try your code at work tomorrow and see where we are at.

ASKER CERTIFIED SOLUTION

Hilaire

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tacobell777

ASKER

Hi Hillaire,

I'm not sure how it can work for you and not for me?
I get two errors which are listed after the code.

DECLARE @returnCode INT,
@objStream INT,
@objCommand INT,
@errorSource VARCHAR(2550),
@errorDescription VARCHAR(2550),
@connectionString VARCHAR(1000),
@returnVal INT,
@filesize int,
@objproperties int,
@obj int,
@strout nvarchar(4000)

SET @connectionString = 'Data Source='+@@SERVERNAME+'; Provider=SQLOLEDB; Initial Catalog=' + db_name() + '; Integrated Security=SSPI'

EXEC @returnCode = sp_OACreate 'ADODB.Stream', @objStream OUTPUT
IF @returnCode <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objStream, @errorSource OUTPUT, @errorDescription OUTPUT
SELECT '1: ', @errorSource, @errorDescription
END

EXEC @returnCode = sp_OACreate 'ADODB.Command', @objCommand OUTPUT
IF @returnCode <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objStream, @errorSource OUTPUT, @errorDescription OUTPUT
SELECT '2: ', @errorSource, @errorDescription
END

EXEC @returnCode = sp_OASetProperty @objCommand, 'ActiveConnection', @connectionString
IF @returnCode <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objStream, @errorSource OUTPUT, @errorDescription OUTPUT
SELECT '3: ', @errorSource, @errorDescription
END

EXEC @returnCode = sp_OASetProperty @objCommand, 'CommandText', "SELECT * FROM lkpPersonTitle FOR XML AUTO"
IF @returnCode <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objStream, @errorSource OUTPUT, @errorDescription OUTPUT
SELECT '4: ', @errorSource, @errorDescription
END

EXEC @returnCode = sp_OAMethod @objStream,'Open'
IF @returnCode <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objStream, @errorSource OUTPUT, @errorDescription OUTPUT
SELECT '5: ', @errorSource, @errorDescription
END

--Get a pointer to the properties collection
EXEC @returnCode = sp_OAGetProperty @objCommand, 'Properties', @objproperties out
IF @returnCode <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objStream, @errorSource OUTPUT, @errorDescription OUTPUT
SELECT '6: ', @errorSource, @errorDescription
END

-- Get a pointer to the "Output Stream" property
EXEC @returnCode = sp_OAMethod @objproperties, 'Item', @obj out, 'Output Stream'
IF @returnCode <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objStream, @errorSource OUTPUT, @errorDescription OUTPUT
SELECT '7: ', @errorSource, @errorDescription
END

-- Set Property value
EXEC @returnCode = sp_OASetProperty @obj, 'Value', @objStream
IF @returnCode <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objStream, @errorSource OUTPUT, @errorDescription OUTPUT
SELECT '8: ', @errorSource, @errorDescription
END

-- Invoke execute method
EXEC sp_OAMethod @objCommand, 'Execute', null, null, null, 1024
IF @returnCode <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objStream, @errorSource OUTPUT, @errorDescription OUTPUT
SELECT '9: ', @errorSource, @errorDescription
END

-- Check output stream size
EXEC @returnCode = sp_OAGetProperty @objStream, 'Size', @FileSize OUT
if @filesize < 4000
begin
-- ReadText
EXEC @returnCode = sp_OAMethod @objStream,'ReadText', @strout out
IF @returnCode <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objStream, @errorSource OUTPUT, @errorDescription OUTPUT
SELECT 'ReadText Error: ', @errorSource, @errorDescription
END
end
else
begin
set @strout = 'Output is too large to fit nvarchar(4000) variable'
end

select @strout as 'Result'

-- Clean up.
EXEC @returnCode = sp_OADestroy @objStream
EXEC @returnCode = sp_OADestroy @objCommand
EXEC @returnCode = sp_OADestroy @objProperties
EXEC @returnCode = sp_OADestroy @obj

8: ADODB.Property Application uses a value of the wrong type for the current operation.

9: ADODB.Command Object or provider is not capable of performing requested operation.

Tacobell777

ASKER

hold on I sent the wrong code, let me fix this and send the correct error.

Tacobell777

ASKER

Ok fixed the source for sp_OAGetErrorInfo but the messages remain the same, so those two errers are the errors I get.

Hilaire

Hi Tacobell,
This is quite surprising indeed !
I'm not at work today, but please post the last version of your code, so that I can check the differences with mine, and maybe try on a different machine (test machine was SQL 2000 sp3 on W2K + IIS box, so maybe it has a different version of MDAC - which ADO is part of unless I'm wrong - )
I'll follwow-up as soon as I can.

Cheers

Hilaire

Tacobell777

ASKER

mate your a legend! tried it at work and it worked on a same spec machine as your test machine.

Well we finally done it! It took a lot of persistance (I believe anything can be done)...

Cheers mate,
I'll put your name in the credits of this beauty if thats allright with you?