tag:blogger.com,1999:blog-27946077079785085682024-02-08T05:09:07.977-08:00The insights of an SQL geekItay Levihttp://www.blogger.com/profile/14883659317375229556noreply@blogger.comBlogger11125tag:blogger.com,1999:blog-2794607707978508568.post-41430040019469391002009-05-10T02:09:00.000-07:002009-05-10T02:26:34.265-07:00EZManage SQL Pro - Recommended MS SQL Server Management ToolHello.<br /><a title="EZManage SQL Pro" href="http://www.futureitsoft.com/index.aspx?id=2928">EZManage SQL Pro</a> is a powerful AGENTLESS centralized management tool for the optimal performance of Microsoft SQL Servers. Enterprise version for 1-500 servers<br />FutureIT’s <a href="http://www.futureitsoft.com">EZManage SQL Pro </a>offers one click management of multiple servers allowing SQL Server databases to run smoothly, efficiently and continuously. Be able to monitor the status of all your SQL servers and databases from one screen, restore to any point in time with no data loss and easily set up notifications. All this while using less disk space and with applications running faster. What could be better?<br /><br /><a href="http://www.futureitsoft.com/">http://www.futureitsoft.com/</a><br /><br /><strong><span style="color:#3333ff;">Central Management of Multiple SQL Servers</span> </strong><br /><br /><ul><li>Monitor the status of all your SQL Servers and databases in one screen with drill- down capabilities<br /></li><li>Configurable dashboard to view servers and databases<br /></li><li>Display database locks and their cause (SQL statement)<br /></li><li>Intervene and release locks<br /></li><li>Monitor all server jobs in one screen and prioritize<br /></li><li>Edit scheduled jobs from multiple servers<br /></li><li>Quick launch of Microsoft remote desktop,</li></ul>Windows Explorer or a command shell screen on a remote server<br />Assign multiple servers to groups for visual and logical order and administration<br /><br /><strong><span style="color:#3333ff;">Backup & Restore</span></strong><br /><strong><span style="color:#3333ff;"></span></strong><ul><li>Backup speed up to 5 times faster than native SQL<br /></li><li>Restores up to 8 times faster than native SQL<br /></li><li>Backup encryption (up to 320 bit)<br /></li><li>Ultra-high compression of backup files by up to 90%<br /></li><li>Open compressed backups with standard tools<br /></li><li>Fine tuning options to set optimum backup speed and compression<br /></li><li>Restore to any point of time in a single click<br /></li><li>Easy restore and duplication of databases to different servers<br /></li><li>Sends backup files off-site by FTP, automatically<br /></li><li>View backup history<br /></li><li>Perform backups in parallel or sequentially<br /></li><li>Backup files to TSM and Tape<br /></li></ul><p><br /><strong><span style="font-size:130%;color:#3333ff;">Built-in Disaster Recovery</span></strong> </p><ul><li>Restore databases between server on a schedule routine<br /></li><li>View synchronization statistics<br /></li><li>Setup full DRP solution in just a few minutes<br /></li><li>Reduce company downtime to minimum<br /></li><li>Monitoring and Alerting<br /></li><li>Send notifications By SNMP, SMS or email<br /></li><li>Define alert distribution lists<br /></li><li>Define customs alerts that suite your company needs<br /></li><li>Set your own notification suites and attach them to multiple databases and servers in a single click</li></ul><p><br /><strong><span style="font-size:130%;color:#3333ff;">Automatic Ongoing Optimization</span></strong> </p><ul><li>Intelligent index defragmentation (based on the state of each index)<br /></li><li>Shrinking of log files to specified size<br /></li><li>Real optimization of DB statistics<br /></li><li>All optimization jobs performed according to protocol and correct best practice sequence preventing errors like accidentally shrinking before backup<br /></li></ul><p><strong><span style="font-size:130%;"><span style="color:#3333ff;">Multi Server Scripting</span> </span></strong></p><strong><span style="font-size:130%;"><ul><li></span></strong></li></ul>Run scripts on multiple DBs<li>Query multiple servers and databases simultaneously and view single results table<br /></li><li>Export query results to Excel<br /></li><li>Print a report of the results<br /></li><li>Customize and execute scripts from our large script library<br /></li><p><strong><span style="font-size:130%;color:#3333ff;">Ease of Use & Implementation</span></strong><br /></p><ul><li>Ultra fast implementation based on pre-defined rules and wizards<br /></li><li>Standard, hassle-free installation<br /></li><li>Friendly and easy-to-use interface<br /></li><li>Pure T-SQL communication to all managed servers<br /></li><li>Agent-less architecture - no installation is needed on the target servers, improving security<br /></li></ul><p><strong><span style="font-size:130%;color:#3333ff;">System Supported Platforms</span></strong><br /></p><ul><li>32-bit: Windows 2000, Windows XP, Windows Server 2000, Windows Server 2003, Windows Vista, Windows Server 2008<br /></li><li>64-bit: Windows 2003 (x64), Windows Server 2003, Windows XP, Windows Vista, Windows Server 2008<br /></li><li>IA-64: Windows Server 2003, Windows Server 2008<br /></li></ul><p><strong><span style="color:#3333ff;">System Supported SQL Servers</span></strong><br /></p><ul><li>SQL Server 2000 Standard Enterprise and MSDE Editions<br /></li><li>SQL Server 2005 Standard Enterprise and Express* Editions<br /></li><li>SQL Server 2008 Standard Enterprise and Express* Editions </li></ul>Itay Levihttp://www.blogger.com/profile/14883659317375229556noreply@blogger.com1tag:blogger.com,1999:blog-2794607707978508568.post-72254158102133504162009-02-16T06:44:00.000-08:002009-02-16T06:46:32.503-08:00How to import the error log contents into a specified table?Hello all.<br />try this Tsql to import your error log into a table.<br />usage :<br />EXEC sp_import_errorlog 'myerrorlog'<br /><br /><br /><br />CREATE PROC sp_import_errorlog<br />(<br /> @log_name sysname,<br /> @log_number int = 0,<br /> @overwrite bit = 0<br />)<br />AS<br /><br />BEGIN<br /> SET NOCOUNT ON<br /> <br /> DECLARE @sql varchar(500) --Holds to SQL needed to create columns from error log<br /><br /> IF (SELECT OBJECT_ID(@log_name,'U')) IS NOT NULL<br /> BEGIN<br /> IF @overwrite = 0<br /> BEGIN<br /> RAISERROR('Table already exists. Specify another name or pass 1 to @overwrite parameter',18,1)<br /> RETURN -1<br /> END<br /> ELSE<br /> BEGIN<br /> EXEC('DROP TABLE ' + @log_name)<br /> END<br /> END<br /><br /> <br /> --Temp table to hold the output of sp_readerrorlog<br /> CREATE TABLE #errlog<br /> (<br /> err varchar(1000),<br /> controw tinyint<br /> )<br /><br /> --Populating the temp table using sp_readerrorlog<br /> INSERT #errlog<br /> EXEC sp_readerrorlog @log_number<br /><br /> --This will remove the header from the errolog<br /> SET ROWCOUNT 4<br /> DELETE #errlog<br /> SET ROWCOUNT 0<br /><br /> <br /> SET @sql = 'SELECT<br /> CONVERT(DATETIME,LEFT(err,23)) [Date],<br /> SUBSTRING(err,24,10) [spid],<br /> RIGHT(err,LEN(err) - 33) [Message],<br /> controw<br /> INTO ' + QUOTENAME(@log_name) +<br /> ' FROM #errlog ' +<br /> 'WHERE controw = 0'<br /> <br /> --Creates the table with the columns Date, spid, message and controw<br /> EXEC (@sql) <br /> <br /> --Dropping the temporary table<br /> DROP TABLE #errlog<br /> <br /> SET NOCOUNT OFF<br />PRINT 'Error log successfully imported to table: ' + @log_name<br />ENDItay Levihttp://www.blogger.com/profile/14883659317375229556noreply@blogger.com0tag:blogger.com,1999:blog-2794607707978508568.post-36234903150268478392009-01-21T08:03:00.000-08:002009-01-21T08:05:14.235-08:00How to view MSSQL 2005 loginshello all,<br />use this to view your 2005 server logins<br /><br /><br />SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM<br />sys.server_principals p LEFT JOIN sys.syslogins l<br />ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'<br /><br /><br />Enjoy<br /><br />ItayItay Levihttp://www.blogger.com/profile/14883659317375229556noreply@blogger.com1tag:blogger.com,1999:blog-2794607707978508568.post-3288363339151891802009-01-07T00:33:00.000-08:002009-01-07T00:45:15.694-08:00How to view backup historyHello all,<br /><br />I found this little script that helped me to view all my backup history<br />enjoy<br /><br />SELECT<br />sysdb.name, bkup.description, bkup.backup_finish_date,<br />case<br />when type='D' then '** FULL **'<br />when type='I' then 'DIFFERENTIAL'<br />when type='L' then 'LOG'<br />end as Backup_Type,<br />(STR(ABS(DATEDIFF(day, GetDate(),(backup_finish_date))))) as 'Days_Ago',<br />ceiling(bkup.backup_size /1048576) <br /><wbr>as 'Size Meg' , <wbr></wbr><br />cast((bkup.backup_size /1073741824) as decimal (9,2)) <br /><wbr>as 'Gig',<br />server_name, sysdb.crdate<br />,datediff(minute, bkup.backup_start_date, bkup.backup_finish_date) <br />as 'Mins'<br />,cast(cast(datediff(minute, bkup.backup_start_date, bkup.backup_finish_date)<br />as<br /><wbr>decimal (8,3))/60 as<br /><wbr>decimal (8,1)) <br /><wbr>as 'Hours', first_lsn, last_lsn, checkpoint_lsn<br />FROM master.dbo.sysdatabases sysdb LEFT OUTER JOIN msdb.dbo.backupset bkup ON bkup.database_name<br />= sysdb.name<br /><wbr>where backup_finish_date > <wbr></wbr><br />DATEADD(DAY, -60, (getdate()))<br /><wbr>ORDER BY sysdb.name, bkup.backup_finish_date desc<br /><br /><br />thanks to homebrew01<br /><br />itayItay Levihttp://www.blogger.com/profile/14883659317375229556noreply@blogger.com0tag:blogger.com,1999:blog-2794607707978508568.post-9241761010672658222009-01-06T00:41:00.000-08:002009-01-06T00:47:35.281-08:00Find the next running schedule jobThis is a little script I just found and wanted to share.<br /><br /><br />USE msdb;<br />WITH CTE AS (SELECT schedule_id,<br />job_id, RIGHT('0'+CAST(next_run_time AS VARCHAR(6)),6) AS next_run_time, next_run_date<br />FROM sysjobschedules)<br />SELECT A.name Job_Name,<br />'Will be running today at '+SUBSTRING(CONVERT(VARCHAR(10), CASE WHEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) > 12<br />THEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time),1,2) -12<br />ELSE<br />SUBSTRING (CONVERT(VARCHAR(10),next_run_time),1,2) END),1,2)+':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time),3,2)+':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time ),5,2)<br />'Scheduled At'<br />FROM sysjobs A , CTE B<br />WHERE A.job_id = B.job_id<br />AND<br />SUBSTRING(CONVERT(VARCHAR(10),next_run_date) , 5,2) +'/'+SUBSTRING(CONVERT(VARCHAR(10),next_run_date) , 7,2) +'/'+SUBSTRING(CONVERT(VARCHAR(10),next_run_date),1,4) = CONVERT(VARCHAR(10),GETDATE(),101)<br />AND<br />(SUBSTRING( CONVERT(VARCHAR(10),CASE WHEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) > 12<br />THEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) -12<br />ELSE SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) END),1,2)+':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time ),3,2)+':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time ),5,2)) >SUBSTRING (CONVERT( VARCHAR(30) , GETDATE(),9),13,7)<br /><br /><br />Thanks to <a href="http://mssqlonline.blogspot.com/">http://mssqlonline.blogspot.com/</a><br /><br />enjoy<br /><br />itayItay Levihttp://www.blogger.com/profile/14883659317375229556noreply@blogger.com0tag:blogger.com,1999:blog-2794607707978508568.post-8857967156078295722009-01-05T00:35:00.000-08:002009-01-05T01:10:07.865-08:00The Power of GOHello all.<br /><br />Nice little tip for you all.<br />If you don't want to use complicated loops, declare counters and so on you can use to power of the GO keyword to the rescue.<br />The GO keyword in SQL 2000, 2005 tells the Server to execute the preceding code as one batch<br />Use the GO in 2005 to actually tell the server how many times you would like the batch to execute.<br /><br />For an example we will create a new table and insert new data 1000 times<br /><br />CREATE TABLE<br />dbo.TempTable<br />(ID INT IDENTITY (1,1),<br /> ROWID uniqueidentifier) <br />GO<br />INSERT INTO dbo. TempTable (ROWID)<br /> VALUES (NEWID()) <br />GO 1000<br /><br /><br />Enjoy and don’t forget to leave some comments<br />ItayItay Levihttp://www.blogger.com/profile/14883659317375229556noreply@blogger.com0tag:blogger.com,1999:blog-2794607707978508568.post-66205434192153967042009-01-01T04:39:00.000-08:002009-01-01T04:40:45.617-08:00How to get a list of all Temp tablesHello all.<br />One of my friends asked me today how he can get a list of all Temp tables.<br />Well the answer is very simple, that I have to share.<br />Just use this:<br /><br /><br />use tempdb<br />go<br />select *<br /> from INFORMATION_SCHEMA.TABLES<br /> where TABLE_SCHEMA = USER and<br /> TABLE_CATALOG = 'tempdb'<br />go<br /><br />Enjoy and don’t forget to leave comments.<br />Itay.Itay Levihttp://www.blogger.com/profile/14883659317375229556noreply@blogger.com0tag:blogger.com,1999:blog-2794607707978508568.post-30856359797943223442008-12-30T06:41:00.000-08:002008-12-30T06:55:04.112-08:00How to transfer logins and passwords from one SQL Server to another?Hello all.<br />I needed to transfer logins and passwords from one of my SQL instance to another one.<br />I used this script, and i wanted to share with you all.<br /><br />Run this Script and use the result on the server you would like to transfer the logins to.<br />enjoy<br /><br /><br />USE master<br />GO<br />IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL<br />DROP PROCEDURE sp_hexadecimal<br />GO<br />CREATE PROCEDURE sp_hexadecimal<br />@binvalue varbinary(256),<br />@hexvalue varchar(256) OUTPUT<br />AS<br />DECLARE @charvalue varchar(256)<br />DECLARE @i int<br />DECLARE @length int<br />DECLARE @hexstring char(16)<br />SELECT @charvalue = '0x'<br />SELECT @i = 1<br />SELECT @length = DATALENGTH (@binvalue)<br />SELECT @hexstring = '0123456789ABCDEF'<br />WHILE (@i <= @length)<br />BEGIN<br />DECLARE @tempint int<br />DECLARE @firstint int<br />DECLARE @secondint int<br />SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))<br />SELECT @firstint = FLOOR(@tempint/16)<br />SELECT @secondint = @tempint - (@firstint*16)<br />SELECT @charvalue = @charvalue +<br />SUBSTRING(@hexstring, @firstint+1, 1) +<br />SUBSTRING(@hexstring, @secondint+1, 1)<br />SELECT @i = @i + 1<br />END<br />SELECT @hexvalue = @charvalue<br />GO<br />IF OBJECT_ID ('sp_help_revlogin_2000_to_2005') IS NOT NULL<br />DROP PROCEDURE sp_help_revlogin_2000_to_2005<br />GO<br />CREATE PROCEDURE sp_help_revlogin_2000_to_2005<br />@login_name sysname = NULL,<br />@include_db bit = 0,<br />@include_role bit = 0<br /><br />AS<br />DECLARE @name sysname<br />DECLARE @xstatus int<br />DECLARE @binpwd varbinary (256)<br />DECLARE @dfltdb varchar (256)<br />DECLARE @txtpwd sysname<br />DECLARE @tmpstr varchar (256)<br />DECLARE @SID_varbinary varbinary(85)<br />DECLARE @SID_string varchar(256)<br /><br />IF (@login_name IS NULL)<br />DECLARE login_curs CURSOR STATIC FOR<br />SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master')<br />FROM master.dbo.sysxlogins<br />WHERE srvid IS NULL AND<br />[name] <> 'sa'<br />ELSE<br />DECLARE login_curs CURSOR FOR<br />SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master')<br />FROM master.dbo.sysxlogins<br />WHERE srvid IS NULL AND<br />[name] = @login_name<br /><br />OPEN login_curs<br /><br />FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb <br /><br />IF (@@fetch_status = -1)<br />BEGIN<br />PRINT 'No login(s) found.'<br />CLOSE login_curs<br />DEALLOCATE login_curs<br />RETURN -1<br />END<br /><br />SET @tmpstr = '/* sp_help_revlogin script '<br />PRINT @tmpstr<br />SET @tmpstr = '** Generated '<br />+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'<br />PRINT @tmpstr<br />PRINT ''<br />PRINT ''<br />PRINT ''<br />PRINT '/***** CREATE LOGINS *****/'<br /><br />WHILE @@fetch_status = 0<br />BEGIN<br />PRINT ''<br />SET @tmpstr = '-- Login: ' + @name<br />PRINT @tmpstr<br /><br />IF (@xstatus & 4) = 4<br />BEGIN -- NT authenticated account/group<br />IF (@xstatus & 1) = 1<br />BEGIN -- NT login is denied access<br />SET @tmpstr = '' --'EXEC master..sp_denylogin ''' + @name + ''''<br />PRINT @tmpstr<br />END<br />ELSE<br />BEGIN -- NT login has access<br />SET @tmpstr = 'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE [name] = ''' + @name + ''')'<br />PRINT @tmpstr<br />SET @tmpstr = CHAR(9) + 'CREATE LOGIN [' + @name + '] FROM WINDOWS'<br />PRINT @tmpstr<br />END<br />END<br />ELSE<br />BEGIN -- SQL Server authentication<br />EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT<br /><br />IF (@binpwd IS NOT NULL)<br />BEGIN -- Non-null password<br />EXEC sp_hexadecimal @binpwd, @txtpwd OUT<br />SET @tmpstr = 'CREATE LOGIN [' + @name + '] WITH PASSWORD=' + @txtpwd + ' HASHED'<br />END<br />ELSE<br />BEGIN -- Null password<br />SET @tmpstr = 'CREATE LOGIN [' + @name + '] WITH PASSWORD='''''<br />END<br /><br />SET @tmpstr = @tmpstr + ', CHECK_POLICY=OFF, SID=' + @SID_string<br />PRINT @tmpstr<br />END<br /><br />FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb<br />END<br /><br />IF @include_db = 1<br />BEGIN<br />PRINT ''<br />PRINT ''<br />PRINT ''<br />PRINT '/***** SET DEFAULT DATABASES *****/'<br /><br />FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb<br /><br />WHILE @@fetch_status = 0<br />BEGIN<br />PRINT ''<br />SET @tmpstr = '-- Login: ' + @name<br />PRINT @tmpstr<br /><br />SET @tmpstr = 'ALTER LOGIN [' + @name + '] WITH DEFAULT_DATABASE=[' + @dfltdb + ']'<br />PRINT @tmpstr<br /><br />FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb<br />END<br />END<br /><br />IF @include_role = 1<br />BEGIN<br />PRINT ''<br />PRINT ''<br />PRINT ''<br />PRINT '/***** SET SERVER ROLES *****/'<br /><br />FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb<br /><br />WHILE @@fetch_status = 0<br />BEGIN<br />PRINT ''<br />SET @tmpstr = '-- Login: ' + @name<br />PRINT @tmpstr<br /><br />IF @xstatus &16 = 16 -- sysadmin<br />BEGIN<br />SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''sysadmin'''<br />PRINT @tmpstr<br />END<br /><br />IF @xstatus &32 = 32 -- securityadmin<br />BEGIN<br />SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''securityadmin'''<br />PRINT @tmpstr<br />END<br /><br />IF @xstatus &64 = 64 -- serveradmin<br />BEGIN<br />SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''serveradmin'''<br />PRINT @tmpstr<br />END<br /><br />IF @xstatus &128 = 128 -- setupadmin<br />BEGIN<br />SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''setupadmin'''<br />PRINT @tmpstr<br />END<br /><br />IF @xstatus &256 = 256 --processadmin<br />BEGIN<br />SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''processadmin'''<br />PRINT @tmpstr<br />END<br /><br />IF @xstatus &512 = 512 -- diskadmin<br />BEGIN<br />SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''diskadmin'''<br />PRINT @tmpstr<br />END<br /><br />IF @xstatus &1024 = 1024 -- dbcreator<br />BEGIN<br />SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''dbcreator'''<br />PRINT @tmpstr<br />END<br /><br />IF @xstatus &4096 = 4096 -- bulkadmin<br />BEGIN<br />SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''bulkadmin'''<br />PRINT @tmpstr<br />END<br /><br />FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb<br />END<br />END<br /><br />CLOSE login_curs<br />DEALLOCATE login_curs<br />RETURN 0<br />GO<br /><br />exec sp_help_revlogin_2000_to_2005 @login_name=NULL, @include_db=1, @include_role=1<br />GOItay Levihttp://www.blogger.com/profile/14883659317375229556noreply@blogger.com1tag:blogger.com,1999:blog-2794607707978508568.post-29318239894656248742008-12-30T03:51:00.000-08:002008-12-30T04:06:00.003-08:00SQL Server Uptime.Hello<br /><br />Today i wanted to check when i last restarted my Servers<br />I ran into this nice little script i would like to share with you all<br /><div dir="rtl"><br />SET NOCOUNT ON<br />DECLARE @crdate DATETIME, @hr VARCHAR(50), @min VARCHAR(5)<br />SELECT @crdate=crdate FROM sysdatabases WHERE NAME='tempdb'<br />SELECT @hr=(DATEDIFF ( mi, @crdate,GETDATE()))/60<br />IF ((DATEDIFF ( mi, @crdate,GETDATE()))/60)=0<br />SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))<br />ELSE<br />SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))-((DATEDIFF( mi, @crdate,GETDATE()))/60)*60<br />PRINT 'SQL Server "' + CONVERT(VARCHAR(20),SERVERPROPERTY('SERVERNAME'))+'" is Online for the past '+@hr+' hours & '+@min+' minutes'<br />IF NOT EXISTS (SELECT 1 FROM master.dbo.sysprocesses WHERE program_name = N'SQLAgent - Generic Refresher')<br />BEGIN<br />PRINT 'SQL Server is running but SQL Server Agent <<not>> running'<br />END<br />ELSE BEGIN<br />PRINT 'SQL Server and SQL Server Agent both are running'<br />END</div><div dir="rtl"><br /></div><div dir="rtl">As a result i got</div><div dir="rtl"></div><div dir="rtl"><span style="color:#ff9900;">SQL Server "LOD-LT-ITAYL" is Online for the past 0 hours & 8 minutesSQL Server and SQL Server Agent both are running</span></div><div dir="rtl"><span style="color:#ff9900;"></span></div><div dir="rtl"><span style="color:#333333;">enojy and don't forget to leave some comments</span></div><div dir="rtl"><span style="color:#333333;"></br></span></div><div dir="rtl"><span style="color:#333333;"></br></span></div><div dir="rtl"><span style="color:#333333;">Itay</span></div><div dir="rtl"><span style="color:#ff9900;"></span></div>Itay Levihttp://www.blogger.com/profile/14883659317375229556noreply@blogger.com0tag:blogger.com,1999:blog-2794607707978508568.post-54915156890274385622008-12-29T08:07:00.000-08:002008-12-29T08:14:12.989-08:00Upgrading to SQL Server 2008<div dir="ltr">For all of you out there who would like to get some information about upgrading to SQL Server 2008 ,i found this great guide on "SQL Server News Blog"</div><div dir="ltr"></div><div dir="ltr"></div><div dir="ltr"></div><div dir="ltr"><span style="color:#ff6666;">Ultimate guide for upgrading to SQL Server 2008</span></div><div dir="ltr"><a href="http://blogs.technet.com/dataplatforminsider/archive/2008/12/04/ultimate-guide-for-upgrading-to-sql-server-2008.aspx">http://blogs.technet.com/dataplatforminsider/archive/2008/12/04/ultimate-guide-for-upgrading-to-sql-server-2008.aspx</a></div><div dir="ltr"></div><div dir="ltr">Enjoy</div><div dir="ltr">Itay.</div>Itay Levihttp://www.blogger.com/profile/14883659317375229556noreply@blogger.com0tag:blogger.com,1999:blog-2794607707978508568.post-40920470210073585242008-12-28T14:00:00.000-08:002008-12-31T04:54:29.174-08:00How to get some general information regarding you MSSQL Server?<div dir="ltr" align="right"><strong><span style="color:#33cc00;"></span></strong></div><div dir="ltr" align="right"><strong><span style="color:#33cc00;"></span></strong></div><div dir="ltr" align="right"></div><div dir="ltr" align="right"> </div><div dir="ltr" align="right"> </div><div dir="ltr" align="right">Hello my name is Itay Levi and this is my first blog ever.<br />As a true SQL geek I will try to post some new and neat t-sql scripts i ran into in my day job as a software engineer, hope you like it and of course don't forget to leave some comments<br /><br />I would like to start with a really easy t-sql i needed the other day just to get some information about my mssql server, this one will Display the<br />MachineName, ProductVersion, ProductLevel, Edition, EngineType and LicenseType<br /><br />Enjoy and wait for more to come soon :)<br /><br /><br />SELECT CAST( SERVERPROPERTY( 'MachineName' ) AS varchar( 30 ) ) AS MachineName , CAST( SERVERPROPERTY( 'InstanceName' ) AS varchar( 30 ) ) AS Instance , CAST( SERVERPROPERTY( 'ProductVersion' ) AS varchar( 30 ) ) AS ProductVersion , CAST( SERVERPROPERTY( 'ProductLevel' ) AS varchar( 30 ) ) AS ProductLevel , CAST( SERVERPROPERTY( 'Edition' ) AS varchar( 30 ) ) AS Edition , ( CASE SERVERPROPERTY( 'EngineEdition') WHEN 1 THEN 'Personal or Desktop' WHEN 2 THEN 'Standard' WHEN 3 THEN 'Enterprise' END ) AS EngineType , CAST( SERVERPROPERTY( 'LicenseType' ) AS varchar( 30 ) ) AS LicenseType , SERVERPROPERTY( 'NumLicenses' ) AS #Licenses; </div>Itay Levihttp://www.blogger.com/profile/14883659317375229556noreply@blogger.com0