Site Search:
Sign in | Join | Help
4Penny.net

SQL Server (T-SQL)

Comments and notes on SQL Server 2000, 2005, and T-SQL

  • Query to get the names and locations of SQL logs and database files

    I have occasion to look at large servers, and as part of that process I need to look at all the files to see if they're ok, I'm particularly interested in the size of the log files (we might need to shrink them)

     This query will quickly show you where they all are

     select db.name , a.filename,a.size, a.*
 from sysdatabases db
  join sysaltfiles a on a.dbid = db.dbid

    Posted Mar 18 2009, 01:35 PM by Steve Gray
    [Edit Tags]

  • Error Locating Server/Instance Specified

    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)
    Posted Feb 20 2009, 01:54 PM by Steve Gray
    [Edit Tags]

  • Initialize a new Dynamics 10.0 database

    Here is a script that will write code to initialize a Dynamics GP 10.0 database. It depends on a table called _temp that has a list of tables to initialize. This script will scan that table and write insert statements for all the tables in the source table

    -- =============================================
-- 
-- =============================================
IF OBJECT_ID('tempdb..#tables', 'U') IS NOT NULL begin
  DROP TABLE #tables
end
GO


SET NOCOUNT on



CREATE TABLE #tables (tablename VARCHAR(10))
INSERT INTO #tables (tablename) VALUES ('CM00100')
INSERT INTO #tables (tablename) VALUES ('CM40100') 
INSERT INTO #tables (tablename) VALUES ('CM40101') 
INSERT INTO #tables (tablename) VALUES ('GL00100') 
INSERT INTO #tables (tablename) VALUES ('GL00102') 
INSERT INTO #tables (tablename) VALUES ('GL00103') 
INSERT INTO #tables (tablename) VALUES ('GL00104') 
INSERT INTO #tables (tablename) VALUES ('GL00105') 
INSERT INTO #tables (tablename) VALUES ('GL00200') 
INSERT INTO #tables (tablename) VALUES ('GL00201') 
INSERT INTO #tables (tablename) VALUES ('GL40000') 
INSERT INTO #tables (tablename) VALUES ('GL40100') 
INSERT INTO #tables (tablename) VALUES ('GL40101') 
INSERT INTO #tables (tablename) VALUES ('GL40200') 
INSERT INTO #tables (tablename) VALUES ('IV00101') 
INSERT INTO #tables (tablename) VALUES ('IV00102') 
INSERT INTO #tables (tablename) VALUES ('IV00103') 
INSERT INTO #tables (tablename) VALUES ('IV00104') 
INSERT INTO #tables (tablename) VALUES ('IV00105') 
INSERT INTO #tables (tablename) VALUES ('IV00106') 
INSERT INTO #tables (tablename) VALUES ('IV00107') 
INSERT INTO #tables (tablename) VALUES ('IV00108') 
INSERT INTO #tables (tablename) VALUES ('IV00109') 
INSERT INTO #tables (tablename) VALUES ('IV40100') 
INSERT INTO #tables (tablename) VALUES ('IV40201') 
INSERT INTO #tables (tablename) VALUES ('IV40202') 
INSERT INTO #tables (tablename) VALUES ('IV40400') 
INSERT INTO #tables (tablename) VALUES ('IV40401') 
INSERT INTO #tables (tablename) VALUES ('IV40500') 
INSERT INTO #tables (tablename) VALUES ('IV40600') 
INSERT INTO #tables (tablename) VALUES ('IV40700') 
INSERT INTO #tables (tablename) VALUES ('IV40800') 
INSERT INTO #tables (tablename) VALUES ('IV40900') 
INSERT INTO #tables (tablename) VALUES ('IV41000') 
INSERT INTO #tables (tablename) VALUES ('IV41001') 
INSERT INTO #tables (tablename) VALUES ('IVC40100') 
INSERT INTO #tables (tablename) VALUES ('IVC40101') 
INSERT INTO #tables (tablename) VALUES ('MC40000') 
INSERT INTO #tables (tablename) VALUES ('MC40100') 
INSERT INTO #tables (tablename) VALUES ('PM00100') 
INSERT INTO #tables (tablename) VALUES ('PM00101') 
INSERT INTO #tables (tablename) VALUES ('PM00200') 
INSERT INTO #tables (tablename) VALUES ('PM00203') 
INSERT INTO #tables (tablename) VALUES ('PM00300') 
INSERT INTO #tables (tablename) VALUES ('PM40100') 
INSERT INTO #tables (tablename) VALUES ('PM40101') 
INSERT INTO #tables (tablename) VALUES ('PM40102') 
INSERT INTO #tables (tablename) VALUES ('PM40103') 
INSERT INTO #tables (tablename) VALUES ('POP00101') 
INSERT INTO #tables (tablename) VALUES ('POP40100') 
INSERT INTO #tables (tablename) VALUES ('POP40600') 
INSERT INTO #tables (tablename) VALUES ('RM00101') 
INSERT INTO #tables (tablename) VALUES ('RM00102') 
INSERT INTO #tables (tablename) VALUES ('RM00105') 
INSERT INTO #tables (tablename) VALUES ('RM00201')
INSERT INTO #tables (tablename) VALUES ('RM00301') 
INSERT INTO #tables (tablename) VALUES ('RM00303') 
INSERT INTO #tables (tablename) VALUES ('RM40101') 
INSERT INTO #tables (tablename) VALUES ('RM40201') 
INSERT INTO #tables (tablename) VALUES ('RM40401') 
INSERT INTO #tables (tablename) VALUES ('SOP00100')
INSERT INTO #tables (tablename) VALUES ('SOP00200')
INSERT INTO #tables (tablename) VALUES ('SOP40100')
INSERT INTO #tables (tablename) VALUES ('SOP40200')
INSERT INTO #tables (tablename) VALUES ('SOP40201')
INSERT INTO #tables (tablename) VALUES ('SOP40300')
INSERT INTO #tables (tablename) VALUES ('SOP40400')
INSERT INTO #tables (tablename) VALUES ('SOP40500')
INSERT INTO #tables (tablename) VALUES ('SOP40600')
INSERT INTO #tables (tablename) VALUES ('SY00300')
INSERT INTO #tables (tablename) VALUES ('SY01100')
INSERT INTO #tables (tablename) VALUES ('SY02200')  
INSERT INTO #tables (tablename) VALUES ('SY02300') 
INSERT INTO #tables (tablename) VALUES ('SY03000') 
INSERT INTO #tables (tablename) VALUES ('SY03100') 
INSERT INTO #tables (tablename) VALUES ('SY03300') 
INSERT INTO #tables (tablename) VALUES ('SY04100') 
INSERT INTO #tables (tablename) VALUES ('SY40100') 
INSERT INTO #tables (tablename) VALUES ('SY40101') 
INSERT INTO #tables (tablename) VALUES ('TX00101') 
INSERT INTO #tables (tablename) VALUES ('TX00102') 
INSERT INTO #tables (tablename) VALUES ('TX00201') 
INSERT INTO #tables (tablename) VALUES ('UPR40100')
INSERT INTO #tables (tablename) VALUES ('UPR40101')
INSERT INTO #tables (tablename) VALUES ('UPR40200')
INSERT INTO #tables (tablename) VALUES ('UPR40301')
INSERT INTO #tables (tablename) VALUES ('UPR40500')
INSERT INTO #tables (tablename) VALUES ('UPR40501')
INSERT INTO #tables (tablename) VALUES ('UPR40600')
INSERT INTO #tables (tablename) VALUES ('UPR40700')
INSERT INTO #tables (tablename) VALUES ('UPR40800')
INSERT INTO #tables (tablename) VALUES ('UPR40801')
INSERT INTO #tables (tablename) VALUES ('UPR40900')
INSERT INTO #tables (tablename) VALUES ('UPR40901')
INSERT INTO #tables (tablename) VALUES ('UPR40902')
INSERT INTO #tables (tablename) VALUES ('UPR41100')
INSERT INTO #tables (tablename) VALUES ('UPR41200')
INSERT INTO #tables (tablename) VALUES ('UPR41201')
INSERT INTO #tables (tablename) VALUES ('UPR41400')
INSERT INTO #tables (tablename) VALUES ('UPR41401')
INSERT INTO #tables (tablename) VALUES ('UPR41500')
INSERT INTO #tables (tablename) VALUES ('UPR41700')
INSERT INTO #tables (tablename) VALUES ('UPR41800')
INSERT INTO #tables (tablename) VALUES ('UPR41801')
INSERT INTO #tables (tablename) VALUES ('UPR41900')
INSERT INTO #tables (tablename) VALUES ('UPR41901')
INSERT INTO #tables (tablename) VALUES ('UPR41902')


 


DECLARE curName CURSOR local fast_forward FOR 
    select tablename from #tables



DECLARE @tname varchar(10),
    @intCount int,
    @fieldList varchar(8000),
    @cname varchar(255)



OPEN curName



WHILE 1=1 
BEGIN
    FETCH NEXT FROM curName INTO @tname



    if @@fetch_status <> 0 begin
        break
    end 
    set @fieldList = ''



    declare curFields CURSOR for
    select syscolumns.name
        from sysobjects
            left join syscolumns on syscolumns.id = sysobjects.id
        where sysobjects.name = @tname
            and colstat <> 1
    
    
    OPEN curFields
    
    
    FETCH NEXT FROM curFields INTO @cname
    WHILE @@FETCH_STATUS = 0
    BEGIN
        set @fieldList = @fieldList + @cname + '  '  + ', '
        FETCH NEXT FROM curFields INTO @cname
    END
    CLOSE curFields
    DEALLOCATE curFields



    if len(@fieldlist) > 0 begin
        set @fieldList = left(@fieldList, len(@fieldList) - 1)
    end
        
    print 'truncate table ' + @tname 
    print 'insert into ' + @tname + '(' + @fieldList + ')'
    print '    select           ' + @fieldList
    print '        from ngb01..' + @tname
    print 'print ''initialized ' + @tname + '''' 
    print ''
END



CLOSE curName
DEALLOCATE curName
GO
 

     

    Posted Jan 10 2009, 10:54 PM by Steve Gray
    [Edit Tags]
More Posts Next page »