sp_OAMethod - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

sp_OAMethod

Author

Topic

carumuga
Posting Yak Master

174 Posts

Posted - 2009-10-09 : 05:25:24

Hi,

In this procedure the second parameter is defined something like
this.

DECLARE @ScriptType INT

SELECT @ScriptType = '1|4|32|262144'
or
SELECT @ScriptType = '1|4|32'
or
SELECT @ScriptType = '74077'

SELECT @SQL = 'Databases("MASTER").' + 'Tables("Test").Script' + '(' + CAST (@ScriptType AS CHAR) + ')'

This scripts just generates the table scripts in the Master db i believe but would like to get it clear, what @scriptType variable mean here in this context, I mean the actual value assgined to this variable.
Also like to know what other values can be assigned to this variable.

Thanks in advance...

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-10-09 : 06:32:39

What kind of syntax is this? Doesn't look like sql server...

DECLARE @ScriptType INT
SELECT @ScriptType = '1|4|32|262144'

-> Conversion failed when converting the varchar value '1|4|32|262144' to data type int.

- Lumbago
http://xkcd.com/327/

carumuga
Posting Yak Master

174 Posts

Posted - 2009-10-09 : 07:51:38

Declare @rc int
DECLARE @oServer int
DECLARE @SQL varchar(300)
DECLARE @TSQL varchar(4000)
DECLARE @ScriptType int, @hr int
DECLARE @src varchar(255), @desc varchar(255);
DECLARE @filename varchar(100)

SELECT @filename = '\\sharename\'

SELECT @filename = @filename + 'test.sql'

--SET @ScriptType =1|4|32|262144

EXEC @RC = MASTER..sp_OACreate 'SQLDMO.SQLServer', @oServer OUT --'Word.Application'
IF @RC <> 0 GOTO ERROR

EXEC @RC = sp_OASetProperty @oServer, 'LoginSecure', 'TRUE'
IF @RC <> 0 GOTO ERROR

EXEC @RC = sp_OAMethod @oServer, 'Connect', NULL, 'localhost', NULL, NULL
IF @RC <> 0 GOTO ERROR

--SET @SQL = 'Databases("MASTER").' + 'Tables("Test").Script' + '(' + CAST (@ScriptType AS CHAR) + ')'
SET @SQL = 'Databases("MASTER").' + 'Tables("Test").Script(74077,"'+ @filename +'")' --writing to the output file.

print @SQL EXEC @rc = sp_OAMethod @oServer, @SQL , @TSQL OUTPUT
PRINT @TSQL
RETURN

ERROR:
EXEC sp_OAGetErrorInfo @oServer, @src OUTPUT, @DESC OUTPUT
SELECT HR = convert(varbinary(4),@HR), Src = @Src, Description = @Desc;
EXEC sp_OADestroy @oServer

My question is, the valid values of @scriptType are '1|4|32|262144' or 1|4|32 or "74077". What this value indicates?

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-09 : 09:03:21

Is this homework?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

carumuga
Posting Yak Master

174 Posts

Posted - 2009-10-14 : 05:48:18

Any update on the below folks.
@scriptType variable accepts any of these values
'1|4|32|262144'
or
'1|4|32'
or
"74077".
What this value indicates while generating table script through OLE Automation. I tested all these values one by one in my code and table scripts were generated as expected. Also, any other values can be assigned to the @scripttype variable in my code.

Thanks in advance.

asgast
Posting Yak Master

149 Posts

Posted - 2009-10-14 : 08:54:39

you can never store values like '1|4|32|262144'
or
'1|4|32'
or
"74077"
in an int variable

Can't fall asleep, the ghost of sql server will get me for writing bad queries. Have to stay awake ...

carumuga
Posting Yak Master

174 Posts

Posted - 2009-10-16 : 05:08:54

Just execute my above query and you can see the DDL scripts written to the output file.

nicodemus
Starting Member

1 Post

Posted - 2009-12-14 : 10:20:30

sp_OAMethod is a way to integrate with external com objects in SQL 2000
The syntax for sp_OAMethod is documented at http://msdn.microsoft.com/en-us/library/ms174984.aspx
The com object that carumuga is using is SQL-DMO documented at http://msdn.microsoft.com/en-us/library/aa312550%28SQL.80%29.aspx
The value 74077 is a mask and consist of the following parameters:

SQLDMOScript_Indexes 73736
SQLDMOScript_AppendToFile 256
SQLDMOScript_ToFileOnly 64
SQLDMOScript_Triggers 16
SQLDMOScript_PrimaryObject 4
SQLDMOScript_Drops 1

So if you as my self don't like to append things and don't wan't drops in your script you can instead use 73820

There are a lot of other parameters to play with.
Couldn't find the documentation at msdn for this but you have a reference at http://doc.ddart.net/mssql/sql2000/html/sqldmo/dmoref_m_s_5e2a.htm

carumuga
Posting Yak Master

174 Posts

Posted - 2009-12-14 : 22:23:12

Thanks a lot buddy! It is really a useful stuff.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources