Wednesday, July 24, 2013

Dynamics GP - Update GL Segment Descriptions

update gl40200 set dscriptn = 'Freehold Land' where sgmtnumb = 1 and sgmntid = 1000


Excel Formula, col D
="update gl40200 set dscriptn = '"&LEFT(C2,31)&"' where sgmtnumb = "&A2& " and sgmntid = "&B2

Dynamics GP Integration Manager - "Distributions are Unbalanced"

When you try to integrate a Journal Entry, you end up getting a number of single line unbalanced journal entries instead of a proper multi-line balanced journal entry.


  1. Create two data files, a header and a detail
  2. Make sure the header has a single number for the journal number - this is your key field
  3. Make sure the details have a number that corresponds to the header - this will join to the key field i nthe header
  4. Add both files as source files
  5. Join them on the key field in the relationship window
  6. In the entries subfolder, change the data source to use the line detail file

Wednesday, July 17, 2013

Dynamics GP Remove Completed Purchase Orders error "This document number already exists in history; it can't be moved."

A record exists in the history table that sohuld be deleted to allow the open PO t omove ot history correctly.

delete from pop30100 where PONUMBER = 'PO12345'
delete from POP30110 where PONUMBER = 'PO12345'

Re-run the Remove Completed Purchase Orders Routine

CN50 - Handheld Reset / Clean Boot

http://www.hard-reset.com/intermec-cn50-hard-reset.html

Dynamics GP - PO Line items do not want to close or cancel due to hidden fractional quantities

The PO line item does not want to close because GP thinks you still have some quantity on order.
The most common culprit is a change in decimal place, or UofM is preventing the PO from closing properly.
For example an item may be on the PO for 10.25 but the item is set to 0 decimal places, and only shows 10 on the po. however, the 0.25 is hidden, but still counts as part of the PO.

Check the POP10110 table (PO Line items)

select * from pop10110 where ponumber = 'PO12345'

Check the QTYORDER and QTYCANCE fields to see if their are any fractional amounts.
Edit or update these values to remove the fractional amounts so that your cancelled qty matches properly.

To force close a line (Only do this if the PO really does not matter, and you are certain the qtyorder = qtycance, and you are certain you are not receiving against it)

update pop10110 set polnesta = 6 where ponumber = 'PO12345'

Then you can go to Edit PO, change the order to Cancelled, and run the Remove Completed Purchase Order Routine to move it into history.




Other things you can try
http://community.dynamics.com/gp/f/32/t/78081.aspx#.Uead6_nbMYk

Monday, July 15, 2013

Dynamics GP 2013 - Cannot insert the value NULL into column 'UseQtyOverageTolerance', table 'tempdb.dbo.#PODetailed

Other errors
popSelectPOItemsLoadDexTables returned the following results: DBMS:515, Microsoft Dynamics GP: 0.

This error occurs when you try to use the Auto Receive feature against an existing PO

This error occurs because you have a non-inventory item on some PO that is incorrectly flagged as an inventoried item.
This may occur if the company has old PO's with old items that were not imported, or that were removed at some point.

Run this script to identify any PO Line items that are incorrectly flagged.
----------------------------------------------------------------------------------------
select * from pop10110 where NONINVEN = 0 and ITEMNMBR not in (select ITEMNMBR from IV00101)
----------------------------------------------------------------------------------------



Run this script to update all noninventory items to the correct status. This will resolve the error.
-----------------------------------------------------------------------------------------
update pop10110 set noninven = 1 where NONINVEN = 0 and ITEMNMBR not in (select ITEMNMBR from IV00101)
----------------------------------------------------------------------------------------

Friday, July 12, 2013

Dynamics GP - Yes to include new code - Privilege Error

This error occurs when you are trying to install a cnk file.
When you run Dynamics GP it prompts to include code, when you click yes, it pops a message saying "Privilege error"

This is because Dynamics is already running, and the files are locked.
Check your task manager for any sessions of Dynamics.exe that are running. Make sure to close any open sessions before attempting to run GP again to install the cnk file.

Dynamics GP 2013 - Restore copy of live as test - update company id

Original article http://support.microsoft.com/kb/871973


When restoring a company from a live database to a test database:

Check all of the company id's by running

select * from dynamics.dbo.sy01500

Go to the new company you restored
Edit the sy00100 table, and change the companyid to the correct companyid identified above
update TEST.dbo.sy00100 set cmpanyid = 2

Run this script
--------------------------
if exists (select 1 from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'SY00100') begin
  declare @Statement varchar(850)
  select @Statement = 'declare @cStatement varchar(255)
declare G_cursor CURSOR for
select case when UPPER(a.COLUMN_NAME) in (''COMPANYID'',''CMPANYID'')
  then ''update ''+a.TABLE_NAME+'' set ''+a.COLUMN_NAME+'' = ''+ cast(b.CMPANYID as char(3)) 
  else ''update ''+a.TABLE_NAME+'' set ''+a.COLUMN_NAME+'' = ''''''+ db_name()+'''''''' end
from INFORMATION_SCHEMA.COLUMNS a, '+rtrim(DBNAME)+'.dbo.SY01500 b
  where UPPER(a.COLUMN_NAME) in (''COMPANYID'',''CMPANYID'',''INTERID'',''DB_NAME'',''DBNAME'')
    and b.INTERID = db_name() and COLUMN_DEFAULT is not null
 and rtrim(a.TABLE_NAME)+''-''+rtrim(a.COLUMN_NAME) <> ''SY00100-DBNAME''
  order by a.TABLE_NAME
set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
  exec (@cStatement)
  FETCH NEXT FROM G_cursor INTO @cStatement
end
close G_cursor
DEALLOCATE G_cursor
set nocount off'
  from SY00100
  exec (@Statement)
end
else begin
  declare @cStatement varchar(255)
  declare G_cursor CURSOR for
  select case when UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID')
    then 'update '+a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '+ cast(b.CMPANYID as char(3)) 
    else 'update '+a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '''+ db_name()+'''' end
  from INFORMATION_SCHEMA.COLUMNS a, DYNAMICS.dbo.SY01500 b
    where UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID','INTERID','DB_NAME','DBNAME')
      and b.INTERID = db_name() and COLUMN_DEFAULT is not null
    order by a.TABLE_NAME
  set nocount on
  OPEN G_cursor
  FETCH NEXT FROM G_cursor INTO @cStatement
  WHILE (@@FETCH_STATUS <> -1)
  begin
    exec (@cStatement)
    FETCH NEXT FROM G_cursor INTO @cStatement
  end
  close G_cursor
  DEALLOCATE G_cursor
  set nocount off
end
----------------------------
sp_changedbowner 'DYNSA'
-----------------------------

The final script should look something like this
-----------------------------------------
BACKUP DATABASE [TWO] TO  DISK = N'D:\Backups\TWO_temp.bak' WITH NOFORMAT, INIT,  NAME = N'TWO-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

BACKUP DATABASE [TEST] TO  DISK = N'D:\Backups\TEST_temp.bak' WITH NOFORMAT, INIT,  NAME = N'TEST-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Use Master
 Alter Database TEST
  SET SINGLE_USER With ROLLBACK IMMEDIATE
RESTORE DATABASE [TEST] FROM  DISK = N'D:\Backups\TWO_temp.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
GO

update TEST.dbo.sy00100 set cmpanyid = 2

USE TEST
if exists (select 1 from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'SY00100') begin
  declare @Statement varchar(850)
  select @Statement = 'declare @cStatement varchar(255)
declare G_cursor CURSOR for
select case when UPPER(a.COLUMN_NAME) in (''COMPANYID'',''CMPANYID'')
  then ''update ''+a.TABLE_NAME+'' set ''+a.COLUMN_NAME+'' = ''+ cast(b.CMPANYID as char(3)) 
  else ''update ''+a.TABLE_NAME+'' set ''+a.COLUMN_NAME+'' = ''''''+ db_name()+'''''''' end
from INFORMATION_SCHEMA.COLUMNS a, '+rtrim(DBNAME)+'.dbo.SY01500 b
  where UPPER(a.COLUMN_NAME) in (''COMPANYID'',''CMPANYID'',''INTERID'',''DB_NAME'',''DBNAME'')
    and b.INTERID = db_name() and COLUMN_DEFAULT is not null
 and rtrim(a.TABLE_NAME)+''-''+rtrim(a.COLUMN_NAME) <> ''SY00100-DBNAME''
  order by a.TABLE_NAME
set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
  exec (@cStatement)
  FETCH NEXT FROM G_cursor INTO @cStatement
end
close G_cursor
DEALLOCATE G_cursor
set nocount off'
  from SY00100
  exec (@Statement)
end
else begin
  declare @cStatement varchar(255)
  declare G_cursor CURSOR for
  select case when UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID')
    then 'update '+a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '+ cast(b.CMPANYID as char(3)) 
    else 'update '+a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '''+ db_name()+'''' end
  from INFORMATION_SCHEMA.COLUMNS a, DYNAMICS.dbo.SY01500 b
    where UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID','INTERID','DB_NAME','DBNAME')
      and b.INTERID = db_name() and COLUMN_DEFAULT is not null
    order by a.TABLE_NAME
  set nocount on
  OPEN G_cursor
  FETCH NEXT FROM G_cursor INTO @cStatement
  WHILE (@@FETCH_STATUS <> -1)
  begin
    exec (@cStatement)
    FETCH NEXT FROM G_cursor INTO @cStatement
  end
  close G_cursor
  DEALLOCATE G_cursor
  set nocount off
end

exec sp_changedbowner 'DYNSA'

Wednesday, July 10, 2013

Dynamics GP - Decimal Place Change - You cannot complete this process while invoices are being posted

Clear all locks and try again

Run

delete from DYNAMICS..ACTIVITY
delete from tempdb..DEX_SESSION
delete from tempdb..DEX_LOCK
delete from DYNAMICS..SY00800
delete from DYNAMICS..SY00801

Thursday, July 4, 2013

Dynamics GP - Daily Snapshots of Inventory Quantities

--Run this against the company first to create the table

/****** Object:  Table [dbo].[IV00102_snapshots]    Script Date: 07/04/2013 09:28:43 ******/ 
IF EXISTS (SELECT * 
           FROM   sys.objects 
           WHERE  object_id = Object_id(N'[dbo].[IV00102_snapshots]') 
                  AND type IN ( N'U' )) 
  DROP TABLE [dbo].[iv00102_snapshots] 

go 

/****** Object:  Table [dbo].[IV00102_snapshots]    Script Date: 07/04/2013 09:28:43 ******/ 
SET ansi_nulls ON 

go 

SET quoted_identifier ON 

go 

SET ansi_padding OFF 

go 

CREATE TABLE [dbo].[iv00102_snapshots] 
  ( 
     [itemnmbr]         [CHAR](31) NOT NULL, 
     [locncode]         [CHAR](11) NOT NULL, 
     [binnmbr]          [CHAR](21) NOT NULL, 
     [rcrdtype]         [SMALLINT] NOT NULL, 
     [primvndr]         [CHAR](15) NOT NULL, 
     [lsordqty]         [NUMERIC](19, 5) NOT NULL, 
     [lrcptqty]         [NUMERIC](19, 5) NOT NULL, 
     [lstorddt]         [DATETIME] NOT NULL, 
     [lsordvnd]         [CHAR](15) NOT NULL, 
     [lsrcptdt]         [DATETIME] NOT NULL, 
     [qtyrqstn]         [NUMERIC](19, 5) NOT NULL, 
     [qtyonord]         [NUMERIC](19, 5) NOT NULL, 
     [qtybkord]         [NUMERIC](19, 5) NOT NULL, 
     [qty_drop_shipped] [NUMERIC](19, 5) NOT NULL, 
     [qtyinuse]         [NUMERIC](19, 5) NOT NULL, 
     [qtyinsvc]         [NUMERIC](19, 5) NOT NULL, 
     [qtyrtrnd]         [NUMERIC](19, 5) NOT NULL, 
     [qtydmged]         [NUMERIC](19, 5) NOT NULL, 
     [qtyonhnd]         [NUMERIC](19, 5) NOT NULL, 
     [atyalloc]         [NUMERIC](19, 5) NOT NULL, 
     [inactive]         [TINYINT] NOT NULL, 
     [snapdate]         [DATETIME] NOT NULL 
  ) 
ON [PRIMARY] 

go 

SET ansi_padding OFF 

go 


--Setup a daily job to run this script

INSERT INTO iv00102_snapshots 
SELECT [itemnmbr], 
       [locncode], 
       [binnmbr], 
       [rcrdtype], 
       [primvndr], 
       [lsordqty], 
       [lrcptqty], 
       [lstorddt], 
       [lsordvnd], 
       [lsrcptdt], 
       [qtyrqstn], 
       [qtyonord], 
       [qtybkord], 
       [qty_drop_shipped], 
       [qtyinuse], 
       [qtyinsvc], 
       [qtyrtrnd], 
       [qtydmged], 
       [qtyonhnd], 
       [atyalloc], 
       inactive, 
       Getdate() AS SnapDate 
FROM   iv00102 

-- Change this number to increase the amount of history kept. Default is 3 years. 
DELETE FROM iv00102_snapshots 
WHERE  Getdate() - snapdate > 1095 

Wednesday, July 3, 2013

SSRS - Cannot click on Drop Down menus in Internet Explorer 10

You have to click on the Compatibility View Icon to enable the drop down menus.

The compatibility toggle is the broken page icon on the right of the address.

Dynamics GP - SQL Capture Logins Script

Original Post
https://community.dynamics.com/gp/f/32/p/61646/199961.aspx#.UdQ102LHIjg


/************************************************************************************************


*


* Purpose: To capture all the SQL Server logins with the binary password regardless of SQL version.


* Script is compatible with SQL 7 / SQL 2000 / SQL 2005. Need to save the results


* to a text file and run within a new query window.


*


* Created Date: Initial - 01/13/2006


*


* Revisions: 01/15/2006 - Made some formatting changes to the output text.


* 01/16/2006 - Made syntax change to account for Binary version of SQL Server.


* 01/16/2006 - Change version SQL version check because of syntax differences between


* SQL 2000 and 2005 with @@version.


* 04/10/2007 - Made change to deal with user names that begin with numbers instead of characters.


* 11/14/2008 - Made syntax change to update for SQL 2008


*


************************************************************************************************/

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(256) output 
AS 
    DECLARE @charvalue VARCHAR(256) 
    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 @xstatus INT 
    DECLARE @binpwd VARBINARY (256) 
    DECLARE @txtpwd SYSNAME 
    DECLARE @tmpstr VARCHAR (256) 
    DECLARE @SID_varbinary VARBINARY(85) 
    DECLARE @SID_string VARCHAR(256) 

    IF ( @login_name IS NULL ) 
      DECLARE login_curs CURSOR FOR 
        SELECT sid, 
               name, 
               xstatus, 
               password 
        FROM   master..sysxlogins 
        WHERE  srvid IS NULL 
               AND name <> 'sa' 
    ELSE 
      DECLARE login_curs CURSOR FOR 
        SELECT sid, 
               name, 
               xstatus, 
               password 
        FROM   master..sysxlogins 
        WHERE  srvid IS NULL 
               AND name = @login_name 

    OPEN login_curs 

    FETCH next FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd 

    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 '' 

    PRINT 'DECLARE @pwd sysname' 

    WHILE ( @@fetch_status <> -1 ) 
      BEGIN 
          IF ( @@fetch_status <> -2 ) 
            BEGIN 
                PRINT '' 

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

                PRINT @tmpstr 

                IF ( @xstatus & 4 ) = 4 
                  BEGIN -- NT authenticated account/group 
                      IF ( @xstatus & 1 ) = 1 
                        BEGIN -- NT login is denied access 
                            SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name 
                                          + 
                                          '''' 

                            PRINT @tmpstr 
                        END 
                      ELSE 
                        BEGIN -- NT login has access 
                            SET @tmpstr = 'EXEC master..sp_grantlogin ''' + 
                                          @name + 
                                          '''' 

                            PRINT @tmpstr 
                        END 
                  END 
                ELSE 
                  BEGIN -- SQL Server authentication 
                      IF ( @binpwd IS NOT NULL ) 
                        BEGIN -- Non-null password 
                            EXEC Sp_hexadecimal 
                              @binpwd, 
                              @txtpwd out 

                            IF ( @xstatus & 2048 ) = 2048 
                              SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' 
                                            + @txtpwd + ')' 
                            ELSE 
                              SET @tmpstr = 
                              'SET @pwd = CONVERT (varbinary(256), ' 
                              + @txtpwd + ')' 

                            PRINT @tmpstr 

                            EXEC Sp_hexadecimal 
                              @SID_varbinary, 
                              @SID_string out 

                            SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name 
                                          + '' 
                                          + ''', @pwd, @sid = ' + @SID_string 
                                          + ', @encryptopt = ' 
                        END 
                      ELSE 
                        BEGIN 
                            -- Null password 
                            EXEC Sp_hexadecimal 
                              @SID_varbinary, 
                              @SID_string out 

                            SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name 
                                          + '' 
                                          + ''', NULL, @sid = ' + @SID_string 
                                          + ', @encryptopt = ' 
                        END 

                      IF ( @xstatus & 2048 ) = 2048 
                        -- login upgraded from 6.5 
                        SET @tmpstr = @tmpstr + '''skip_encryption_old''' 
                      ELSE 
                        SET @tmpstr = @tmpstr + '''skip_encryption''' 

                      PRINT @tmpstr 
                  END 
            END 

          FETCH next FROM login_curs INTO @SID_varbinary, @name, @xstatus, 
          @binpwd 
      END 

    CLOSE login_curs 

    DEALLOCATE login_curs 

    RETURN 0 

go 

IF Object_id ('seeMigrateSQLLogins') IS NOT NULL 
  DROP PROCEDURE seemigratesqllogins 

go 

CREATE PROCEDURE Seemigratesqllogins @login_name SYSNAME = NULL 
AS 
    DECLARE @name          CHAR(50), 
            @binpwd        VARBINARY (256), 
            @txtpwd        SYSNAME, 
            @tmpstr        VARCHAR (256), 
            @SID_varbinary VARBINARY(85), 
            @SID_string    VARCHAR(256), 
            @Is_Policy     BIT, 
            @Is_Exp        BIT, 
            @type          CHAR(1), 
            @Pol           CHAR(3), 
            @Exp           CHAR(3) 

    SET nocount ON 

    CREATE TABLE #logins 
      ( 
         [name]                  NVARCHAR(128) NOT NULL, 
         [sid]                   VARBINARY(85) NOT NULL, 
         [type]                  CHAR(1) NOT NULL, 
         [is_policy_checked]     BIT DEFAULT 0, 
         [is_expiration_checked] BIT DEFAULT 0, 
         [password_hash]         VARBINARY(256) 
      ) 

    INSERT #logins 
           (name, 
            sid, 
            type) 
    SELECT name, 
           sid, 
           type 
    FROM   sys.server_principals 
    WHERE  ( type_desc = 'SQL_LOGIN' 
              OR type_desc = 'WINDOWS_LOGIN' ) 
           AND name <> 'sa' 
           AND name <> 'NT AUTHORITY\SYSTEM' 

    UPDATE a 
    SET    a.is_policy_checked = b.is_policy_checked, 
           a.is_expiration_checked = b.is_expiration_checked, 
           a.password_hash = b.password_hash 
    FROM   #logins a, 
           sys.sql_logins b 
    WHERE  a.sid = b.sid 

    SET nocount OFF 

    IF ( @login_name IS NULL ) --Not a single user, get the list 
      DECLARE seelogin_curs CURSOR FOR 
        SELECT name, 
               sid, 
               password_hash, 
               type, 
               is_policy_checked, 
               is_expiration_checked 
        FROM   #logins 
        WHERE  name <> 'sa' 
    ELSE 
      DECLARE seelogin_curs CURSOR FOR 
        SELECT name, 
               sid, 
               password_hash, 
               type, 
               is_policy_checked, 
               is_expiration_checked 
        FROM   #logins 
        WHERE  name = @login_name 

    OPEN seelogin_curs 

    FETCH next FROM seelogin_curs INTO @name, @SID_varbinary, @binpwd, @type, 
    @Is_Policy, @Is_Exp 

    IF ( @@fetch_status = -1 ) 
      BEGIN 
          PRINT 'No login(s) found.' 

          CLOSE seelogin_curs 

          DEALLOCATE seelogin_curs 
      END 

    SET @tmpstr = '/* seeMigrateSQLLogins - For SQL Server 2005 Only ' 

    PRINT @tmpstr 

    SET @tmpstr = '** Generated ' 
                  + CONVERT (VARCHAR, Getdate()) + ' on ' 
                  + @@SERVERNAME + ' */' 

    PRINT @tmpstr 

    PRINT '' 

    WHILE ( @@fetch_status <> -1 ) 
      BEGIN 
          IF @type = 'S' 
            BEGIN 
                PRINT '/* SQL Login ******************/' 

                EXEC Sp_hexadecimal 
                  @binpwd, 
                  @txtpwd out 

                EXEC Sp_hexadecimal 
                  @SID_varbinary, 
                  @SID_string out 

                IF @Is_Policy = 1 
                  BEGIN 
                      SET @Pol = 'ON' 
                  END 
                ELSE 
                  BEGIN 
                      SET @Pol = 'OFF' 
                  END 

                IF @Is_Exp = 1 
                  BEGIN 
                      SET @Exp = 'ON' 
                  END 
                ELSE 
                  BEGIN 
                      SET @Exp = 'OFF' 
                  END 

                SET @tmpstr = 'Create Login [' + Rtrim(@name) 
                              + '] WITH PASSWORD = ' + @txtpwd 
                              + ' hashed, sid = ' + @SID_string 
                              + ', CHECK_POLICY = ' + @Pol 
                              + ', CHECK_EXPIRATION = ' + @Exp 

                PRINT @tmpstr 

                PRINT '' 
            END 
          ELSE 
            BEGIN 
                PRINT '/* SQL Login ******************/' 

                EXEC Sp_hexadecimal 
                  @SID_varbinary, 
                  @SID_string out 

                SET @tmpstr = 'Create Login [' + Rtrim(@name) 
                              + '] FROM WINDOWS; ' 

                PRINT @tmpstr 

                PRINT '' 
            END 

          FETCH next FROM seelogin_curs INTO @name, @SID_varbinary, @binpwd, 
          @type, 
          @Is_Policy, @Is_Exp 
      END 

    CLOSE seelogin_curs 

    DEALLOCATE seelogin_curs 

    DROP TABLE #logins 

go 

DECLARE @version2005 CHAR(5) 
DECLARE @version2008 CHAR(5) 

--Get the current version of SQL Server running 
SELECT @version2005 = Substring(@@version, 29, 4) 

SELECT @version2008 = Substring(@@version, 35, 4) 

IF @version2005 = '9.00' 
  BEGIN 
      EXEC Seemigratesqllogins 
  END 
ELSE IF @version2008 = '10.0' 
  BEGIN 
      EXEC Seemigratesqllogins 
  END 
ELSE 
  BEGIN 
      EXEC Sp_help_revlogin 
  END 

IF Object_id ('sp_hexadecimal') IS NOT NULL 
  DROP PROCEDURE sp_hexadecimal 

go 

IF Object_id ('sp_help_revlogin') IS NOT NULL 
  DROP PROCEDURE sp_help_revlogin 

go 

IF Object_id ('seeMigrateSQLLogins') IS NOT NULL 
  DROP PROCEDURE seemigratesqllogins 

go 
/* End Script */