September 25, 2007 at 8:56 am
Hi I have the following code which scripts objects in a database reports:
The script scripts objects withingn the database, however it doesnt work on some tables
I get the output
using EXEC sp_OAGetErrorInfo @oServer as:
[Microsoft][ODBC SQL Server Driver][SQL Server]Assert failed: @indid is null in sp_MStablekeys (pk/uq)
Declare @rc int
DECLARE @oServer int
DECLARE @method varchar(300)
DECLARE @TSQL varchar(4000)
DECLARE @ScriptType int
EXEC sp_OACreate 'SQLDMO.SQLServer', @oServer OUT
--EXEC sp_OASetProperty @oServer, 'loginsecure', 'true'
EXEC sp_OAMethod @oServer , 'Connect', NULL, 'LON-RPTSQL02', ChrisW , password99
SET @ScriptType =1|4|32|262144
SET @method = 'Databases("Reports").' +
'Tables("statsIntelliTXTKeywordsMonthly").Script' +
'(' + CAST (@ScriptType AS CHAR) + ')'
print @method
EXEC @rc=sp_OAMethod @oServer, @method , @TSQL OUTPUT
PRINT @TSQL
print @rc
EXEC sp_OAGetErrorInfo @oServer
EXEC sp_OADestroy @oServer
How do I script this object
This is what the output should be:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[statsIntelliTXTKeywordsMonthly](
[EntryMonth] [smalldatetime] NOT NULL,
[DetailID] [int] NOT NULL,
[IPID] [int] NOT NULL,
[Source] [int] NOT NULL,
[Hooks] [bigint] NOT NULL,
[AdViews] [bigint] NOT NULL,
[Clicks] [int] NOT NULL,
CONSTRAINT [PK__statsIntelliTXTK__6F82EA71] PRIMARY KEY CLUSTERED
(
[EntryMonth] ASC,
[DetailID] ASC,
[IPID] ASC,
[Source] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PS_KeywordsMonthly]([EntryMonth])
)
It is partitioned. It seems the query has a problem scripting the primary key
Im a bit stuck
September 26, 2007 at 4:23 am
Have you tried isolating the problem?
* Does the problem occur on other tables
* Does the problem occur when you try the process interactively using SSMS
* Does the problem occur when you remove the option to script the primary key
I have had issues in SQL 2000 when generating scripts using SP_OA... calls. If I passed options to get everything I wanted to script for a table in a single call, sometimes not everything was scripted. I ended up using multiple calls for a given table, asking for table structure, keys and constraints, indexes, and triggers in separate calls. No errors were given for my original SQL 2000 scripts, but maybe in SQL 2005 it is now reporting an error instead of giving no output for the problem item.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply