-1

I have stored procedure which is using dynamic query and inserts details of severs into table. If I execute stored procedure manually it works fine. but if try to execute the same with JOB, Job completes successfully but no data get inserted into table.

CREATE TABLE #temp (
    id INT identity
    ,dbname VARCHAR(150)
    )

INSERT INTO #temp
SELECT NAME
FROM sys.databases
WHERE state_desc = 'online'

DECLARE @count INT = 1
DECLARE @dbname NVARCHAR(max)
DECLARE @maxcount INT = (
        SELECT count(1)
        FROM #temp
        )
DECLARE @sql NVARCHAR(max)
DECLARE @Server_Name NVARCHAR(max) = (
        SELECT @@SERVERNAME
        )
DECLARE @IP_Address NVARCHAR(max) = (
        SELECT convert(NVARCHAR(max), CONNECTIONPROPERTY('local_net_address')) AS local_net_address
        )

WHILE @count <= @maxcount
BEGIN
    SELECT @dbname = dbname
    FROM #temp
    WHERE id = @count

    --print @dbname 
    DECLARE @query NVARCHAR(max) = ' INSERT INTO dm_staging..database_size (
        [IP_Address]
        ,[Server_Name]
        ,[DBName]
        ,[name]
        ,[Used_Space_MB]
        ,[Available_ Free_Space_MB]
        ,[Total_Size_MB]
        )
    SELECT ''' + @IP_Address + '''
        ,''' + @Server_Name + '''
        ,DB_NAME() AS DbName
        ,Physical_name
        ,size / 128.0 AS Used_Space_MB
        ,size / 128.0 - CAST(FILEPROPERTY(NAME, '' SpaceUsed '') AS INT) / 128.0 AS FreeSpaceMB
        ,(size / 128.0) + (size / 128.0 - CAST(FILEPROPERTY(NAME, '' SpaceUsed '') AS INT) / 128.0) AS Total_Size_MB
    FROM sys.database_files '

    SET @sql = 'use [' + @dbname + ']' + @query

    EXECUTE sp_executesql @sql

    --exec (@sql) 
    SET @count = @count + 1
END
3
  • 1
    Can you provide us with some example code of what exactly the Stored Procedure does and how it is called in both scenarios? Otherwise we are not able to help you Oct 16, 2015 at 10:07
  • I will post code in answer.
    – Vinayak
    Oct 16, 2015 at 10:15
  • Edit your question... Oct 16, 2015 at 10:22

1 Answer 1

1

This happens because you use the variable @IP_Address which is null when you run the stored procedure via the job.

The SERVERPROPERTY('local_net_address') returns the IP address on the server that the connection targeted which is available only for connections that are using the TCP transport provider and is 'nullable'. Appearently the SQL Server Agent jobs do not use the TCP transport provider.

As you add the @IP_Address to the @query variable, the @query variable also becomes NULL. So nothing is executed.

2
  • is there any solution for this ?
    – Vinayak
    Oct 20, 2015 at 6:42
  • You really need the IP Address? Oct 20, 2015 at 6:52

Not the answer you're looking for? Browse other questions tagged or ask your own question.