February 24, 2012 at 2:37 am
Hi,
I don't know if I've just been staring at this too long and can't see something obvious, but I'm trying to:
1) If a table 'x' doesn't exist, create it (across linked server)
2) If table 'x' does exist, insert data into it (which it should do based on 1))
The code I have parses ok, but at runtime it seems to skip the bits I have about checking the existance of the table, and fails the insert into statement.
DECLARE @errmsg NVARCHAR(2048)
DECLARE @VerboseLogging BIT
SET @VerboseLogging = 1
IF @VerboseLogging = 1
IF NOT EXISTS(SELECT TABLE_NAME FROM [My_Linked_Server_Name].[My_Database_Name].INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'indexlog_CurrProcessList')
BEGIN TRY
EXEC ('CREATE TABLE [My_Database_Name].[dbo].[indexlog_CurrProcessList](
[DatabaseName] [sysname] NOT NULL,
[Status] [datetime] NOT NULL,
[Hostname] [datetime] NULL,
[DomainName] [int] NULL,
[Session_id] [float] NOT NULL,
[Most_recent_session_id] [float] NOT NULL,
[Connect_time] [datetime] NULL,
[net_transport] [int] NULL,
[protocol_type] [int] NULL,
[protocol_version] [int] NULL,
[auth_scheme] [nvarchar] (20) NULL,
[client_net_address] [nvarchar] (50) NULL,
[most_recent_sql_handle] [nvarchar] (2048)
) ON [PRIMARY]') AT [My_Linked_Server_Name]
END TRY
BEGIN CATCH
RAISERROR('Problem creating indexlog_CurrProcessList table', 0, 1) WITH NOWAIT
SET @errmsg = ERROR_MESSAGE()
RAISERROR(@errmsg, 0, 1) WITH NOWAIT
END CATCH
IF NOT EXISTS(SELECT TABLE_NAME FROM [My_Linked_Server_Name].[My_Database_Name].INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'indexlog_CurrProcessList')
BEGIN
RAISERROR('Table does not exist', 0, 1) WITH NOWAIT
END
ELSE
BEGIN TRY
INSERT INTO [My_Linked_Server_Name].[My_Database_Name].dbo.indexlog_CurrProcessList(
[DatabaseName],
[Status],
[Hostname],
[DomainName],
[Session_id],
[Most_recent_session_id],
[Connect_time] ,
[net_transport],
[protocol_type],
[protocol_version],
[auth_scheme],
[client_net_address],
[most_recent_sql_handle]
)
SELECT [DatabaseName],[Status],[Hostname],[DomainName],[Session_id],[Most_recent_session_id],[Connect_time],[net_transport],[protocol_type],[protocol_version],[auth_scheme],[client_net_address],[most_recent_sql_handle]
FROM #CurrProcessesTemp --temp table has same structure as [indexlog_CurrProcessList]
END TRY
BEGIN CATCH
RAISERROR('There was a problem inserting current process information into the indexlog_CurrProcessList table', 0, 1) WITH NOWAIT
SET @errmsg = ERROR_MESSAGE()
RAISERROR(@errmsg, 0, 1) WITH NOWAIT
END CATCH
The error I get is:
Msg 7314, Level 16, State 1, Line 343
The OLE DB provider "SQLNCLI" for linked server "My_Linked_Server_Name" does not contain the table ""My_Database_Name"."dbo"."indexlog_CurrProcessList"". The table either does not exist or the current user does not have permissions on that table.
Any ideas?
Thanks,
Andrew
February 24, 2012 at 2:55 am
Add the INSERT part into dynamic SQL then execute it from that.
It won't work otherwise, because if the table doesn't exist then the code fails before executing.
February 24, 2012 at 3:03 am
ok, will do. I thought I'd got it working previously, obviously not.
Thanks for the suggestion
Andrew
February 24, 2012 at 3:07 am
that's worked. thanks again
February 24, 2012 at 3:07 am
adb2303 (2/24/2012)
ok, will do. I thought I'd got it working previously, obviously not.Thanks for the suggestion
Andrew
The code would work if the table exists, it only fails because it doesn't. Really, MS should have designed the parser to fail when the objects don't exist. Instead it fails at compile time.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply