Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2003
    Posts
    55

    Post Unanswered: Scripting Functionality

    Dears,
    I am using SQL 2000.
    Whenever I want to send new updates for the client I am used to script all objects in the DB except the tables, then send it to the client to run the script. The problem is that I have views that depend on each other, I mean ViewA is using ViewB, the script is dropping all objects and creating them again, when creating ViewA, ViewB is not created yet, and so an error message comes up telling that ViewB does not exist and so ViewA was not created. Same occurs when a function is using another function.
    Currently, I am applying the script, and moving manually the order of any object so that to be created before the objects depend on it.

    Any of you guys know better solution? Is there a way to script all DB objects with ALTER command instead of DROP / CREATE?

    I imagine that if the scripting functionality has the ability to drop object1, create object1 then drop object2, create object2 instead of drop obect1, drop object2 then create object1, create object2, the problem will not show up anymore.

    Your comments are highly appreciated.

    Regards,

  2. #2
    Join Date
    Mar 2002
    Location
    Finland
    Posts
    18
    I myself had a similar problem. To solve the problem I created a stored procedure (on basis of one found from the net) that scripts all the objects one by one, hence first dropping an object, then creating it again and only then moving on to the next object by using cursors run into the sysobjects. This procedure is used for updating stored procedures, user define functions and views but can be extended to do more.

    If you have any more questions, I'm more than happy to help if only I know how.


    Code:
    
    CREATE  PROCEDURE GenerateUpdateScript(
      @server varchar(30) = NULL
      @uname varchar(30) = 'default_username',
      @pwd varchar(30) = 'default_password',
      @dbname varchar(30) = NULL
      @filename varchar(200) = NULL
    )
    AS
    
    IF @filename IS NULL
    	SET @filename = 'your_path/test_' + GetDate() + '.sql'
    
    DECLARE @object int
    DECLARE @hr int
    DECLARE @return varchar(200)
    DECLARE @exec_str varchar(200)
    DECLARE @tbname varchar(128)
    
    SET NOCOUNT ON
    
    -- Set the server to the local server
    IF @server is NULL
      SELECT @server = @@servername
    
    -- Set the database to the current database
    IF @dbname is NULL
      SELECT @dbname = db_name()
    
    -- Create an object that points to the SQL Server
    EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT
    IF @hr <> 0
    BEGIN
      EXEC sp_displayoaerrorinfo @object, @hr
      RETURN
    END
    
    -- Connect to the SQL Server
    IF (@uname is NULL) AND (@pwd is NULL)
      BEGIN
        -- Windows NT Authentication mode is used
        EXEC @hr = sp_OAMethod @object, 'Connect', NULL,  @server
        IF @hr <> 0
          BEGIN
            EXEC sp_displayoaerrorinfo @object, @hr
            RETURN
          END
      END
    ELSE
    IF (@uname is NULL)
      BEGIN
        -- Set the username to the current user name
        SELECT @uname = SYSTEM_USER
        EXEC @hr = sp_OAMethod @object,'Connect',NULL,@server,@uname,@pwd
        IF @hr <> 0
          BEGIN
            EXEC sp_displayoaerrorinfo @object, @hr
            RETURN
          END
      END
    ELSE
    IF (@pwd is NULL)
      BEGIN
        EXEC @hr = sp_OAMethod @object, 'Connect', NULL,  @server, @uname
        IF @hr <> 0
          BEGIN
            EXEC sp_displayoaerrorinfo @object, @hr
            RETURN
          END
      END
    ELSE
      BEGIN
        EXEC @hr = sp_OAMethod @object,'Connect',NULL,@server,@uname,@pwd
        IF @hr <> 0
          BEGIN
            EXEC sp_displayoaerrorinfo @object, @hr
            RETURN
          END
      END
    
    -- Verify the connection
    EXEC @hr = sp_OAMethod @object, 'VerifyConnection', @return OUT
    IF @hr <> 0
    BEGIN
      EXEC sp_displayoaerrorinfo @object, @hr
      RETURN
    END
    
    --Script UDFs
    SET @exec_str = 'DECLARE script_cursor CURSOR FOR SELECT name FROM '
                  + @dbname + '..sysobjects WHERE type IN (''FN'', ''IF'', ''TF'') ORDER BY Name'
    EXEC (@exec_str)
    
    OPEN script_cursor
    FETCH NEXT FROM script_cursor INTO @tbname
    WHILE (@@fetch_status <> -1)
    BEGIN
      SET @exec_str = 'Databases("'+ @dbname +'").UserDefinedFunctions("'
                      + RTRIM(@tbname)+'").Script(74077,"'
                      + @filename +'", 2)'
      EXEC @hr = sp_OAMethod @object, @exec_str, @return OUT
      IF @hr <> 0
        BEGIN
          EXEC sp_displayoaerrorinfo @object, @hr
          RETURN     
        END
      FETCH NEXT FROM script_cursor INTO @tbname
    END
    CLOSE script_cursor
    DEALLOCATE script_cursor
      
    
    --Script views
    SET @exec_str = 'DECLARE script_cursor CURSOR FOR SELECT name FROM '
                  + @dbname + '..sysobjects WHERE type=''v'' and category=''0'' ORDER BY Name'
    EXEC (@exec_str)
    
    OPEN script_cursor
    FETCH NEXT FROM script_cursor INTO @tbname
    WHILE (@@fetch_status <> -1)
    BEGIN
      SET @exec_str = 'Databases("'+ @dbname +'").Views("'
                      + RTRIM(@tbname)+'").Script(74077,"'
                      + @filename +'", 2)'
      EXEC @hr = sp_OAMethod @object, @exec_str, @return OUT
      IF @hr <> 0
        BEGIN
          EXEC sp_displayoaerrorinfo @object, @hr
          RETURN     
        END
      FETCH NEXT FROM script_cursor INTO @tbname
    END
    CLOSE script_cursor
    DEALLOCATE script_cursor
      
    --Script procedures
    SET @exec_str = 'DECLARE script_cursor CURSOR FOR SELECT name FROM '
                  + @dbname + '..sysobjects WHERE type = ''P'' AND category = 0 ORDER BY Name'
    EXEC (@exec_str)
    
    OPEN script_cursor
    FETCH NEXT FROM script_cursor INTO @tbname
    WHILE (@@fetch_status <> -1)
    BEGIN
      SET @exec_str = 'Databases("'+ @dbname +'").StoredProcedures("'
                      + RTRIM(@tbname)+'").Script(74077,"'
                      + @filename +'", 2)'
      EXEC @hr = sp_OAMethod @object, @exec_str, @return OUT
      IF @hr <> 0
        BEGIN
          EXEC sp_displayoaerrorinfo @object, @hr
          RETURN     
        END
      FETCH NEXT FROM script_cursor INTO @tbname
    END
    CLOSE script_cursor
    DEALLOCATE script_cursor
    
    -- Destroy the object
    EXEC @hr = sp_OADestroy @object
    IF @hr <> 0
    BEGIN
      EXEC sp_displayoaerrorinfo @object, @hr
      RETURN
    END
    
    
    GO
    Cipher
    If you reach for the stars, you might not quite catch one but you won't get a handful of mud, either.

  3. #3
    Join Date
    Jul 2003
    Posts
    55

    Thumbs up

    Dear Mr. Cipher,
    I really appreciate your interest in my thread, thanks so much.

    As for the SP you provided, I am currently testing it, it is obvious how professional way the SP was implemented, yet, the SP "sp_displayoaerrorinfo" is not found, I guess its only for displaying the error -if any- so I just commented it out.

    I will get back to you once I have any results, it’s highly recommended for all members here to have a look at this SP. Thanks again.

    Regards,


    Originally posted by Cipher
    I myself had a similar problem. To solve the problem I created a stored procedure (on basis of one found from the net) that scripts all the objects one by one, hence first dropping an object, then creating it again and only then moving on to the next object by using cursors run into the sysobjects. This procedure is used for updating stored procedures, user define functions and views but can be extended to do more.

    If you have any more questions, I'm more than happy to help if only I know how.


    Code:
    
    CREATE  PROCEDURE GenerateUpdateScript(
      @server varchar(30) = NULL
      @uname varchar(30) = 'default_username',
      @pwd varchar(30) = 'default_password',
      @dbname varchar(30) = NULL
      @filename varchar(200) = NULL
    )
    AS
    
    IF @filename IS NULL
    	SET @filename = 'your_path/test_' + GetDate() + '.sql'
    
    DECLARE @object int
    DECLARE @hr int
    DECLARE @return varchar(200)
    DECLARE @exec_str varchar(200)
    DECLARE @tbname varchar(128)
    
    SET NOCOUNT ON
    
    -- Set the server to the local server
    IF @server is NULL
      SELECT @server = @@servername
    
    -- Set the database to the current database
    IF @dbname is NULL
      SELECT @dbname = db_name()
    
    -- Create an object that points to the SQL Server
    EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT
    IF @hr <> 0
    BEGIN
      EXEC sp_displayoaerrorinfo @object, @hr
      RETURN
    END
    
    -- Connect to the SQL Server
    IF (@uname is NULL) AND (@pwd is NULL)
      BEGIN
        -- Windows NT Authentication mode is used
        EXEC @hr = sp_OAMethod @object, 'Connect', NULL,  @server
        IF @hr <> 0
          BEGIN
            EXEC sp_displayoaerrorinfo @object, @hr
            RETURN
          END
      END
    ELSE
    IF (@uname is NULL)
      BEGIN
        -- Set the username to the current user name
        SELECT @uname = SYSTEM_USER
        EXEC @hr = sp_OAMethod @object,'Connect',NULL,@server,@uname,@pwd
        IF @hr <> 0
          BEGIN
            EXEC sp_displayoaerrorinfo @object, @hr
            RETURN
          END
      END
    ELSE
    IF (@pwd is NULL)
      BEGIN
        EXEC @hr = sp_OAMethod @object, 'Connect', NULL,  @server, @uname
        IF @hr <> 0
          BEGIN
            EXEC sp_displayoaerrorinfo @object, @hr
            RETURN
          END
      END
    ELSE
      BEGIN
        EXEC @hr = sp_OAMethod @object,'Connect',NULL,@server,@uname,@pwd
        IF @hr <> 0
          BEGIN
            EXEC sp_displayoaerrorinfo @object, @hr
            RETURN
          END
      END
    
    -- Verify the connection
    EXEC @hr = sp_OAMethod @object, 'VerifyConnection', @return OUT
    IF @hr <> 0
    BEGIN
      EXEC sp_displayoaerrorinfo @object, @hr
      RETURN
    END
    
    --Script UDFs
    SET @exec_str = 'DECLARE script_cursor CURSOR FOR SELECT name FROM '
                  + @dbname + '..sysobjects WHERE type IN (''FN'', ''IF'', ''TF'') ORDER BY Name'
    EXEC (@exec_str)
    
    OPEN script_cursor
    FETCH NEXT FROM script_cursor INTO @tbname
    WHILE (@@fetch_status <> -1)
    BEGIN
      SET @exec_str = 'Databases("'+ @dbname +'").UserDefinedFunctions("'
                      + RTRIM(@tbname)+'").Script(74077,"'
                      + @filename +'", 2)'
      EXEC @hr = sp_OAMethod @object, @exec_str, @return OUT
      IF @hr <> 0
        BEGIN
          EXEC sp_displayoaerrorinfo @object, @hr
          RETURN     
        END
      FETCH NEXT FROM script_cursor INTO @tbname
    END
    CLOSE script_cursor
    DEALLOCATE script_cursor
      
    
    --Script views
    SET @exec_str = 'DECLARE script_cursor CURSOR FOR SELECT name FROM '
                  + @dbname + '..sysobjects WHERE type=''v'' and category=''0'' ORDER BY Name'
    EXEC (@exec_str)
    
    OPEN script_cursor
    FETCH NEXT FROM script_cursor INTO @tbname
    WHILE (@@fetch_status <> -1)
    BEGIN
      SET @exec_str = 'Databases("'+ @dbname +'").Views("'
                      + RTRIM(@tbname)+'").Script(74077,"'
                      + @filename +'", 2)'
      EXEC @hr = sp_OAMethod @object, @exec_str, @return OUT
      IF @hr <> 0
        BEGIN
          EXEC sp_displayoaerrorinfo @object, @hr
          RETURN     
        END
      FETCH NEXT FROM script_cursor INTO @tbname
    END
    CLOSE script_cursor
    DEALLOCATE script_cursor
      
    --Script procedures
    SET @exec_str = 'DECLARE script_cursor CURSOR FOR SELECT name FROM '
                  + @dbname + '..sysobjects WHERE type = ''P'' AND category = 0 ORDER BY Name'
    EXEC (@exec_str)
    
    OPEN script_cursor
    FETCH NEXT FROM script_cursor INTO @tbname
    WHILE (@@fetch_status <> -1)
    BEGIN
      SET @exec_str = 'Databases("'+ @dbname +'").StoredProcedures("'
                      + RTRIM(@tbname)+'").Script(74077,"'
                      + @filename +'", 2)'
      EXEC @hr = sp_OAMethod @object, @exec_str, @return OUT
      IF @hr <> 0
        BEGIN
          EXEC sp_displayoaerrorinfo @object, @hr
          RETURN     
        END
      FETCH NEXT FROM script_cursor INTO @tbname
    END
    CLOSE script_cursor
    DEALLOCATE script_cursor
    
    -- Destroy the object
    EXEC @hr = sp_OADestroy @object
    IF @hr <> 0
    BEGIN
      EXEC sp_displayoaerrorinfo @object, @hr
      RETURN
    END
    
    
    GO

  4. #4
    Join Date
    Mar 2002
    Location
    Finland
    Posts
    18
    Originally posted by RaedT
    Dear Mr. Cipher,
    I really appreciate your interest in my thread, thanks so much.

    As for the SP you provided, I am currently testing it, it is obvious how professional way the SP was implemented, yet, the SP "sp_displayoaerrorinfo" is not found, I guess its only for displaying the error -if any- so I just commented it out.

    I will get back to you once I have any results, it’s highly recommended for all members here to have a look at this SP. Thanks again.

    Regards,
    Sorry about that, I didn't realize I had used a custom made sp to display the error. But yes, the sp_displayoaerrorinfo only shows (and, if need be, logs) the error, so you can etiher just leave the error checking out or apply your own.

    Hope the sp is of use to you!!!
    Cipher
    If you reach for the stars, you might not quite catch one but you won't get a handful of mud, either.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    The SP is neat, even though I personally don't like cursors. I too had similar problems sending back-end updates to clients by running into dependency issues. This prompted me to look into coding methodologies of the gurus around. It turns out that view-on-top-of-view approach is typically the result of unfinished or non-thorough system analysis and/or design flaw. Upon identifying this as a real issue I changed my design, eliminated any front-end-driven SELECTs, implemented SPs and UDFs (which do end up having multi-layered dependency, but this is an exception), and got rid of views all together. Resulting design needs very little maintenance, but when an update is needed, - I use a $250 tool (SQLCompare) that generates the script for me. You can call it being lazy, of course, but I'd rather be lazy

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    After several attempts to run this SP I realized that it would not work. Here are the reasons:

    - The following error gets generated when running the procedure:
    Server: Msg 16915, Level 16, State 1, Line 1
    A cursor with the name 'script_cursor' already exists.

    - If @uname and @pwd are null the connection cannot be established.

    - If other than a local server is attempted to be connected to, the scripts will not be correctly generated unless connecting to a virgin MASTER, MSDB, PUBS, NORTHWIND, or TEMPDB databases of the same version of SQL, since the cursor does not retrieve object names from the specified server.

    - If sp_displayoaerrorinfo and sp_Hexadecimal procedures do not exist, - the procedure will bomb out.

    So I decided to re-write your procedure. Here it is:

    if exists (select 1 from information_schema.routines where routine_schema = 'dbo' and routine_name = 'sp_GenerateScripts')
    drop procedure dbo.sp_GenerateScripts
    go
    /*
    ************************************************** ***********************************
    Created : 07/31/2003
    By : Robert Djabarov
    Purpose : Create scripts for Stored Procedures, Views, and UDFs using SQLDMO.
    ************************************************** ***********************************
    */
    create procedure dbo.sp_GenerateScripts (
    @UName varchar(30) = null,
    @PWD varchar(30) = null,
    @FileName varchar(128) = null )
    as
    set nocount on
    declare @DateTime datetime, @hc int, @obj int, @output varchar(200), @type varchar(5),
    @objname varchar(128), @DBName varchar(128), @method varchar(1000)
    set @DateTime = getdate()
    if @FileName is null set @FileName = 'c:\test_' +
    convert(char(8), @DateTime, 112) +
    substring(convert(char(2), @DateTime, 108), 1, 2) +
    substring(convert(char(2), @DateTime, 108), 4, 2) +
    substring(convert(char(2), @DateTime, 108), 7, 2) + '.SQL'
    set @DBName = db_name()

    print 'Creating SERVER object...'
    exec @hc = sp_OACreate 'SQLDMO.SQLServer', @obj output
    if @hc != 0 goto ErrorTrap

    print 'Connecting to server ' + @@servername + '...'
    if @UName is not null
    exec @hc = sp_OAMethod @obj, 'Connect', null, @@servername, @UName, @PWD
    else begin
    exec @hc = sp_OASetProperty @obj, 'LoginSecure', 'True'
    if @hc != 0 goto ErrorTrap
    exec @hc = sp_OAMethod @obj, 'Connect', null, @@servername
    end
    if @hc != 0 goto ErrorTrap

    print 'Verifying connection with ' + @@servername + '...'
    exec @hc = sp_OAMethod @obj, 'VerifyConnection', @output output
    if @hc != 0 goto ErrorTrap

    select @objname = min([name]) from dbo.sysobjects (nolock)
    where [type] in ('fn', 'if', 'tf', 'v', 'p')
    and objectproperty(object_id([name]), 'IsMSShipped') = 0
    while @objname is not null begin
    select @type = [type] from dbo.sysobjects (nolock) where [name] = @objname
    print 'Processing ' + case @type when 'p' then 'procedure' when 'v' then 'view' else 'UDF' end + ' ' + @objname + '...'
    if @type = 'p'
    set @method = 'Databases("' + @DBName + '").StoredProcedures("' +
    @objname + '").Script(74077, "' + @FileName + '", 2)'
    else if @type = 'v'
    set @method = 'Databases("' + @DBName + '").Views("' +
    @objname + '").Script(74077, "' + @FileName + '", 2)'
    else
    set @method = 'Databases("' + @DBName + '").UserDefinedFunctions("' +
    @objname + '").Script(74077, "' + @FileName + '", 2)'
    exec @hc = sp_OAMethod @obj, @method, @output output
    if @hc != 0 goto ErrorTrap
    select @objname = min([name]) from dbo.sysobjects (nolock)
    where [type] in ('fn', 'if', 'tf', 'v', 'p')
    and objectproperty(object_id([name]), 'IsMSShipped') = 0
    and [name] > @objname
    end

    print 'Disconnecting from ' + @@servername + '...'
    exec @hc = sp_OAMethod @obj, 'Disconnect', null
    if @hc != 0 goto ErrorTrap

    print 'Destroying the SERVER object...'
    exec @hc = sp_OADestroy @obj
    if @hc != 0 goto ErrorTrap
    return (0)

    ErrorTrap:
    declare @hrhex char(10),
    @hr int,
    @source varchar(255),
    @description varchar(255),
    @charvalue varchar(255),
    @i int,
    @length int,
    @hexstring char(16),
    @tempint int,
    @firstint int,
    @secondint int
    print 'OLE Automation Error Information'
    select @charvalue = '0x',
    @i = 1,
    @length = DATALENGTH(@hc),
    @hexstring = '0123456789abcdef'
    while (@i <= @length) begin
    select @tempint = convert(int, substring(cast(@hc as varbinary(255)),@i,1))
    select @firstint = floor(@tempint/16)
    select @secondint = @tempint - (@firstint*16)
    select @hrhex = @charvalue +
    substring(@hexstring, @firstint+1, 1) +
    substring(@hexstring, @secondint+1, 1)
    select @i = @i + 1
    end
    select @output = ' HRESULT: ' + @hrhex
    print @output
    exec @hr = sp_OAGetErrorInfo @obj, @source output, @description output
    if @hr = 0 begin
    select @output = ' Source: ' + @source + char(13) +
    ' Description: ' + @description
    print @output
    end else
    print ' sp_OAGetErrorInfo failed.'
    return (1)

    go

  7. #7
    Join Date
    Jul 2003
    Posts
    55
    Dears,
    Thank you guys for your replies. Its obvious that it’s a common problem for most of you, what “rdjabarov” stated is a very good idea that most of us should keep on mind, unfortunately, for now its too late for me to start applying new naming convention.
    Regarding the SP for generating the script, I’m still unable to run it, as when I do not provide the username and the password, I fail to connect to the server. I still believe that Microsoft may have a service pack or something like this to make the functionality scripting the objects in a better way rather than the used one. Any one of you guys has any other suggestions?

    Regards,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •