question

ALEMKEBEDE avatar image
ALEMKEBEDE asked

How can I select from tembdb and insert into linked server

I'm trying to create a job that loop through all of the databases on the server and pulls Index stats and insert into another server but I kept getting error that says linked server not found . I'm not sure if my syntax is correct CREATE table #tmpDBNames ( line int, db varchar(MAX) ) INSERT INTO #tmpDBNames (line, db) SELECT ROW_NUMBER() OVER ( ORDER BY name ) AS line , name FROM master.sys.databases CREATE table #IndexStatistics ( [IndexStatisticsId] INT IDENTITY (1,1), [InstanceID] int , [DatabaseName] varchar(MAX), [TableName] VARCHAR (MAX), [IndexName] VARCHAR (MAX), [IndexId] bigint , [Reads] bigint , [Writes] bigint , [Rows] bigint, [ReadsPerWrite] varchar(50) , [DropStatement] varchar(max) ) DECLARE @count int SET @count = 1 WHILE @count <= (SELECT count(*) from #tmpDBNames) BEGIN DECLARE @db varchar(100) SET @db = (SELECT db FROM #tmpDBNames WHERE line = @count) DECLARE @sql nvarchar(max) SET @sql = N' use ' + @db +' insert into linkedservername ( [InstanceID] ,[DatabaseName] ,TableName ,[Indexname] ,[IndexId] ,[reads] ,[writes] ,[rows] ,[ReadsPerWrite] ,[dropSTATEMENT] ) SELECT 6 ,DB_NAME(database_id) as DatabaseName , o.name , indexname = i.name , i.index_id , reads = user_seeks + user_scans + user_lookups , writes = user_updates , rows = ( SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id ) , CASE WHEN s.user_updates < 1 THEN 100 ELSE 1.00 * ( s.user_seeks + s.user_scans + s.user_lookups ) / s.user_updates END AS reads_per_write , ''DROP INDEX '' + QUOTENAME(i.name) + '' ON '' + QUOTENAME(c.name) + ''.'' + QUOTENAME(OBJECT_NAME(s.object_id)) AS ''drop statement'' FROM sys.dm_db_index_usage_stats s INNER JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id INNER JOIN sys.objects o ON s.object_id = o.object_id INNER JOIN sys.schemas c ON o.schema_id = c.schema_id WHERE OBJECTPROPERTY(s.object_id, ''IsUserTable'') = 1 AND s.database_id = DB_ID() AND i.type_desc = ''nonclustered'' AND i.is_primary_key = 0 AND i.is_unique_constraint = 0 AND ( SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id ) > 10000 ORDER BY reads_per_write ASC' EXECUTE sp_executesql @sql PRINT @sql SET @count = @count + 1 END SELECT * FROM #IndexStatistics --SELECT * FROM #tmpDBNames DROP TABLE #IndexStatistics DROP TABLE #tmpDBNames
linked-server
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
sjimmo avatar image
sjimmo answered
You need to verify that the linked server is valid, and that you can connect to it and see everything. You should be able to open the linked server from SSMS and see the databases on it. Additionally in your insert into linkedservername should be: insert into ... otherwise the insert statement will not know what to do.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.