Exporting Output Data of an Stored Procedure

  • Hello

    Does anybody know what the simplest way would be to export the output data of a stored procedure?

    Basically, I have a SP which when executed, outputs CREATE LOGIN statements for all the server logins for the instance, so I can save and run elsewhere (on DR Instance for example). I'm trying to automate this as much as possible.

    I'm using MSSQL 2012 Standard Edition.

    Thanks.

  • My favourite option would be bcp (http://msdn.microsoft.com/en-us/library/ms162802.aspx)

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
    There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
    I would never join a club that would allow me as a member - Groucho Marx

  • I will go for SSIS package and schedule a job in sql server agent to run SSIS

    Pramod
    SQL Server DBA | MCSE SQL Server 2012/2014

    in.linkedin.com/in/pramodsingla/
    http://pramodsingla.wordpress.com/

  • Thanks.

    I've tried:

    exec master..xp_cmdshell 'bcp " execute master.dbo.sp_help_revlogin" queryout "\\location names\example.txt" -c -T -SServername -'

    I get in the output; "host-files must contain at least one column, Unable to resolve column level collations"

    The SP:

    USE master

    GO

    IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL

    DROP PROCEDURE sp_hexadecimal

    GO

    CREATE PROCEDURE sp_hexadecimal

    @binvalue varbinary(256),

    @hexvalue varchar (514) OUTPUT

    AS

    DECLARE @charvalue varchar (514)

    DECLARE @i int

    DECLARE @length int

    DECLARE @hexstring char(16)

    SELECT @charvalue = '0x'

    SELECT @i = 1

    SELECT @length = DATALENGTH (@binvalue)

    SELECT @hexstring = '0123456789ABCDEF'

    WHILE (@i <= @length)

    BEGIN

    DECLARE @tempint int

    DECLARE @firstint int

    DECLARE @secondint int

    SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))

    SELECT @firstint = FLOOR(@tempint/16)

    SELECT @secondint = @tempint - (@firstint*16)

    SELECT @charvalue = @charvalue +

    SUBSTRING(@hexstring, @firstint+1, 1) +

    SUBSTRING(@hexstring, @secondint+1, 1)

    SELECT @i = @i + 1

    END

    SELECT @hexvalue = @charvalue

    GO

    IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL

    DROP PROCEDURE sp_help_revlogin

    GO

    CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS

    DECLARE @name sysname

    DECLARE @type varchar (1)

    DECLARE @hasaccess int

    DECLARE @denylogin int

    DECLARE @is_disabled int

    DECLARE @PWD_varbinary varbinary (256)

    DECLARE @PWD_string varchar (514)

    DECLARE @SID_varbinary varbinary (85)

    DECLARE @SID_string varchar (514)

    DECLARE @tmpstr varchar (1024)

    DECLARE @is_policy_checked varchar (3)

    DECLARE @is_expiration_checked varchar (3)

    DECLARE @defaultdb sysname

    IF (@login_name IS NULL)

    DECLARE login_curs CURSOR FOR

    SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM

    sys.server_principals p LEFT JOIN sys.syslogins l

    ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'

    ELSE

    DECLARE login_curs CURSOR FOR

    SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM

    sys.server_principals p LEFT JOIN sys.syslogins l

    ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name

    OPEN login_curs

    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin

    IF (@@fetch_status = -1)

    BEGIN

    PRINT 'No login(s) found.'

    CLOSE login_curs

    DEALLOCATE login_curs

    RETURN -1

    END

    SET @tmpstr = '/* sp_help_revlogin script '

    PRINT @tmpstr

    SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'

    PRINT @tmpstr

    PRINT ''

    WHILE (@@fetch_status <> -1)

    BEGIN

    IF (@@fetch_status <> -2)

    BEGIN

    PRINT ''

    SET @tmpstr = '-- Login: ' + @name

    PRINT @tmpstr

    IF (@type IN ( 'G', 'U'))

    BEGIN -- NT authenticated account/group

    SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'

    END

    ELSE BEGIN -- SQL Server authentication

    -- obtain password and sid

    SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )

    EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT

    EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

    -- obtain password policy state

    SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

    SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

    SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

    IF ( @is_policy_checked IS NOT NULL )

    BEGIN

    SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked

    END

    IF ( @is_expiration_checked IS NOT NULL )

    BEGIN

    SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked

    END

    END

    IF (@denylogin = 1)

    BEGIN -- login is denied access

    SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )

    END

    ELSE IF (@hasaccess = 0)

    BEGIN -- login exists but does not have access

    SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )

    END

    IF (@is_disabled = 1)

    BEGIN -- login is disabled

    SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'

    END

    PRINT @tmpstr

    END

    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin

    END

    CLOSE login_curs

    DEALLOCATE login_curs

    RETURN 0

    GO

    I did not write the SP, it's from MS, I see it uses a cursor. Is the issue occurring with the bcp because the SP is using cursor to populate a table, where the data is never returned from that table?

  • /*********In master.dbo.sp_help_revlogin*******/

    --Replace "print" command with "SELECT"

    create table test (a varchar(4000))

    insert into test

    execute master.dbo.sp_help_revlogin

    exec master..xp_cmdshell 'bcp "master.dbo.test" out "\\location names\example.txt" -c -T -SServername '

    😎

    Pramod
    SQL Server DBA | MCSE SQL Server 2012/2014

    in.linkedin.com/in/pramodsingla/
    http://pramodsingla.wordpress.com/

  • if you know the shape of the data being output from the procedure, you can insert it directly into a table;

    for example, if your proc returns a single column table of CREATE LOGIn statments:

    CREATE TABLE #Results(ResultsID int identity(1,1) NOT NULL Primary Key, ResultsText varchar(2000) )

    INSERT INTO Results(ResultsText )

    EXECUTE MyProcedure @parameter = 42

    will that help?

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thanks.

    If I run the SP unaltered, it outputs a neat looking script, is there no way I can simply backup the output, without having to amend the SP and create table?

  • wak_no1 (6/26/2013)


    Thanks.

    If I run the SP unaltered, it outputs a neat looking script, is there no way I can simply backup the output, without having to amend the SP and create table?

    doh i missed the core issue: print statements vs SELECT statements that psingla so wisely identified.

    here's a slightly updated version that puts the results into a temp table, which it finally selects from after the cursor is done.

    USE master

    GO

    IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL

    DROP PROCEDURE sp_hexadecimal

    GO

    CREATE PROCEDURE sp_hexadecimal

    @binvalue varbinary(256),

    @hexvalue varchar (514) OUTPUT

    AS

    DECLARE @charvalue varchar (514)

    DECLARE @i int

    DECLARE @length int

    DECLARE @hexstring char(16)

    SELECT @charvalue = '0x'

    SELECT @i = 1

    SELECT @length = DATALENGTH (@binvalue)

    SELECT @hexstring = '0123456789ABCDEF'

    WHILE (@i <= @length)

    BEGIN

    DECLARE @tempint int

    DECLARE @firstint int

    DECLARE @secondint int

    SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))

    SELECT @firstint = FLOOR(@tempint/16)

    SELECT @secondint = @tempint - (@firstint*16)

    SELECT @charvalue = @charvalue +

    SUBSTRING(@hexstring, @firstint+1, 1) +

    SUBSTRING(@hexstring, @secondint+1, 1)

    SELECT @i = @i + 1

    END

    SELECT @hexvalue = @charvalue

    GO

    IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL

    DROP PROCEDURE sp_help_revlogin

    GO

    CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS

    DECLARE @name sysname

    DECLARE @type varchar (1)

    DECLARE @hasaccess int

    DECLARE @denylogin int

    DECLARE @is_disabled int

    DECLARE @PWD_varbinary varbinary (256)

    DECLARE @PWD_string varchar (514)

    DECLARE @SID_varbinary varbinary (85)

    DECLARE @SID_string varchar (514)

    DECLARE @tmpstr varchar (1024)

    DECLARE @is_policy_checked varchar (3)

    DECLARE @is_expiration_checked varchar (3)

    DECLARE @defaultdb sysname

    CREATE TABLE #tmp(ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,Results varchar(4000))

    IF (@login_name IS NULL)

    DECLARE login_curs CURSOR FOR

    SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM

    sys.server_principals p LEFT JOIN sys.syslogins l

    ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'

    ELSE

    DECLARE login_curs CURSOR FOR

    SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM

    sys.server_principals p LEFT JOIN sys.syslogins l

    ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name

    OPEN login_curs

    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin

    IF (@@fetch_status = -1)

    BEGIN

    INSERT INTO #tmp(Results)

    SELECT 'No login(s) found.'

    CLOSE login_curs

    DEALLOCATE login_curs

    RETURN -1

    END

    SET @tmpstr = '/* sp_help_revlogin script '

    INSERT INTO #tmp(Results)

    SELECT @tmpstr

    SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'

    INSERT INTO #tmp(Results)

    SELECT @tmpstr

    INSERT INTO #tmp(Results)

    SELECT ''

    WHILE (@@fetch_status <> -1)

    BEGIN

    IF (@@fetch_status <> -2)

    BEGIN

    INSERT INTO #tmp(Results)

    SELECT ''

    SET @tmpstr = '-- Login: ' + @name

    INSERT INTO #tmp(Results)

    SELECT @tmpstr

    IF (@type IN ( 'G', 'U'))

    BEGIN -- NT authenticated account/group

    SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'

    END

    ELSE BEGIN -- SQL Server authentication

    -- obtain password and sid

    SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )

    EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT

    EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

    -- obtain password policy state

    SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

    SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

    SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

    IF ( @is_policy_checked IS NOT NULL )

    BEGIN

    SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked

    END

    IF ( @is_expiration_checked IS NOT NULL )

    BEGIN

    SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked

    END

    END

    IF (@denylogin = 1)

    BEGIN -- login is denied access

    SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )

    END

    ELSE IF (@hasaccess = 0)

    BEGIN -- login exists but does not have access

    SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )

    END

    IF (@is_disabled = 1)

    BEGIN -- login is disabled

    SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'

    END

    INSERT INTO #tmp(Results)

    SELECT @tmpstr

    END

    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin

    END

    CLOSE login_curs

    DEALLOCATE login_curs

    SELECT RESULTS FROM #tmp ORDER BY ID

    RETURN 0

    GO

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thanks you, I will test this and get back to you.

  • Thank you, this work pretty exactly how I wanted it work.

    The only issue I'm having now is that when I open the output script (with the CREATE LOGIN statements), MSSQL first asks me to select the encoding (which I set to 'auto-detect'), but when I try and run the script as whole, it does not complete, I have to run each statement individually, which isn't ideal.

    Has this got anything to do with MSSQL asking in respect to the Encoding, which it wasn't doing so with the original script?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply