October 15, 2010 at 1:26 am
Hi All,
I Have 2 servers A&B.
1) A contains sp and target server is B.
now i used open row set query t pull the sp results into table inServer B.
But it is executing only 1 row ,rest of them not coming to table in B.
QUERY:
Insert into DB_Sizeinfo select *
FROM OPENROWSET('SQLNCLI10', 'server=B;trusted_connection=yes',
'set fmtonly off exec master.dbo.SP_SQL_DB_Sizeinfo')
RESULT : 1 ROW EFFECTED ,BUT IF EXECTE SP CONATINS 30 ROWS ARE THERE. wHY ALL ROWS ARE NOT INSERTING INTO TABLE IN Target Server Table B.
Thanks in Advance
Syamala
October 15, 2010 at 7:07 am
can you show us your stored procedure you are calling?
October 15, 2010 at 8:32 am
Are ad hoc queries enabled?
October 18, 2010 at 3:08 am
USE [master]
GO
/****** Object: StoredProcedure [dbo].[SP_SQL_DB_Sizeinfo1] Script Date: 10/18/2010 03:56:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_SQL_DB_Sizeinfo1]
AS
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
--SET ANSI_WARNINGS OFF
--SET ARITHABORT OFF
--SET ARITHIGNORE ON
DECLARE @vDB_Name AS NVARCHAR (200)
DECLARE @vSQL_String AS NVARCHAR (MAX)
SET @vDB_Name = (SELECT TOP 1 DB.name FROM [master].[sys].[databases] DB WHERE DB.state = 0 AND DB.is_read_only = 0 AND DB.is_in_standby = 0 AND DB.source_database_id IS NULL ORDER BY DB.name)
WHILE @vDB_Name IS NOT NULL
BEGIN
SET @vSQL_String =
'
USE ['+@vDB_Name+'];
--INSERT INTO [DWDB1].[master].[DBO].[DB_Sizeinfo1]
SELECT
@@SERVERNAME AS server_name
,DB_NAME () AS database_name
,REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (15), CONVERT (MONEY, ROUND ((A.total_size*CONVERT (BIGINT, 8192))/1048576.0, 0)), 1)), 4, 15)) AS total_size_mb
,(CASE
WHEN A.database_size >= B.total_pages THEN REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (15), CONVERT (MONEY, ROUND (((A.database_size-B.total_pages)*CONVERT (BIGINT, 8192))/1048576.0, 0)), 1)), 4, 15))
ELSE ''0''
END) AS unallocated_mb
,REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (15), CONVERT (MONEY, ROUND ((B.total_pages*CONVERT (BIGINT, 8192))/1048576.0, 0)), 1)), 4, 15)) AS reserved_mb
FROM
(
SELECT
SUM (CASE
WHEN DBF.type = 0 THEN DBF.size
ELSE 0
END) AS database_size
,SUM (DBF.size) AS total_size
FROM
[sys].[database_files] AS DBF
WHERE
DBF.type IN (0,1)
) A
CROSS JOIN
(
SELECT
SUM (AU.total_pages) AS total_pages
,SUM (AU.used_pages) AS used_pages
,SUM (CASE
WHEN IT.internal_type IN (202,204) THEN 0
WHEN AU.type <> 1 THEN AU.used_pages
WHEN P.index_id <= 1 THEN AU.data_pages
ELSE 0
END) AS pages
FROM
[sys].[partitions] P
INNER JOIN [sys].[allocation_units] AU ON AU.container_id = P.partition_id
LEFT JOIN [sys].[internal_tables] IT ON IT.[object_id] = P.[object_id]
) B
'
EXEC (@vSQL_String)
SET @vDB_Name = (SELECT TOP 1 DB.name FROM [master].[sys].[databases] DB WHERE DB.state = 0 AND DB.is_read_only = 0 AND DB.is_in_standby = 0 AND DB.source_database_id IS NULL AND DB.name >@vDB_Name ORDER BY DB.name)
END
GO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply