tag:blogger.com,1999:blog-117437292024-03-04T20:42:04.277-08:00SIVA SQL ServerAdding fuel to my passion on SQL ServerUnknownnoreply@blogger.comBlogger248125tag:blogger.com,1999:blog-11743729.post-40388259458078355992014-04-09T09:11:00.001-07:002014-04-09T09:11:51.572-07:00How to add SQL Login in Secondary server in Log Shipping?<p> </p> <p>For SQL Login, the SID is unique to the SQL Server instance level, so SID of SQL Login of Primary and Secondary are different. <br />It will result in orphan user.</p> <p>Whereas Windows Login SID are unique to the Domain level. Hence when we create the Windows Login, SID are matching</p> <p> <table cellspacing="0" cellpadding="2" width="746" border="1"><tbody> <tr> <td valign="top" width="335"><strong>Windows Login <br /></strong></td> <td valign="top" width="409"><strong>SQL Login</strong> <br /></td> </tr> <tr> <td valign="top" width="335">If you have Windows logins, the login SID in SQL Server will match the SID from Active Directory. So all you need to do is create the same login on both your primary and secondary server. </td> <td valign="top" width="409">SQL Server logins are a little more complicated, as the SQL Server assigns the SID when the login is created <br />All database users are linked to logins by SID; if the SID of a login is different on the secondary server, the database users tied to that login will be orphaned. This can be fixed, but it's better to avoid the problem all together</td> </tr> </tbody></table> <br /> <strong><u>Solution : <br /></u></strong> Manually specifying of SID, while creating login on the Secondary SQL Server will resolve the issues.</p> <p> <br /> Execute the <strong><font color="#0000ff">sp_help_revlogin</font></strong> on your primary to generate all the create statements for your logins, both Windows and SQL Server. For each Windows Login you get code like this:</p> <p> <br /><font color="#0000ff">CREATE LOGIN [WinDomain\siva] FROM WINDOWS WITH DEFAULT_DATABASE = [master]</font></p> <p>This will allow you to just recreate the login on the secondary server. </p> <p>For each SQL Server Login you will get something like this statement:</p> <p><font color="#0000ff">CREATE LOGIN [sql_test1] WITH PASSWORD = 0x0100A7B0020EEAB8D0B17D28883AA11267W7575B2F55CEC9EE HASHED, <br /><strong><font style="background-color: #ffff00">SID</font> = </strong>0x7DE18E2C10F4FE4CA127E9837FCE4D3C, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF</font></p> <p>Notice that the statement contains the encrypted version of the password and the SID. <br />This means that when run on the secondary server, the password will match the SID and we will not have any issues after the failover. </p> Unknownnoreply@blogger.com3tag:blogger.com,1999:blog-11743729.post-27633745328281517002014-02-19T01:46:00.001-08:002014-02-19T01:46:26.287-08:00HP Data Protector command to backup the SQL Server DB Backup flat file to Tape media<p> </p> <p>First we have to <strong><u><font color="#0000ff">configure the HPDP Backup Specification</font></u></strong></p> <p>1.Login HP Data Protector</p> <p>2. Create the Backup Specification</p> <p>3. Source – Select the Server, Folder where the SQL Server Database are backed up</p> <p>   Destination – Select the Tape Media Library to which the DB Flat files from disk to be baked up</p> <p>   Options  -- Provide the Description details and  the Protection like 6 Weeks</p> <p>   Schedule – Skip it</p> <p>   Click Apply and generate the Backup Specification. </p> <p>   Use this Backup Specification name on calling the HPDP Backup using</p> <p><strong><u><font color="#0000ff">Create the DOS Batch files below</font></u></strong></p> <p>For Full Backup – It can be used weekly when we backup <strong>FULL </strong>databases </p> <table cellspacing="0" cellpadding="2" width="603" border="1"><tbody> <tr> <td valign="top" width="601"> <p>c: <br />cd\Program Files\OmniBack\bin <br /><font color="#333333"><strong>omnib -datalist</strong></font> Win-SQL-FS-OnDemand <font color="#333333"><strong>-mode Full -load high</strong></font></p> </td> </tr> </tbody></table> <p>For Incremental Backup – It can be used daily when we take <strong>differential </strong>backup of databases</p> <table cellspacing="0" cellpadding="2" width="605" border="1"><tbody> <tr> <td valign="top" width="603"> <p>c: <br />cd\Program Files\OmniBack\bin <br /><font color="#333333"><strong>omnib -datalist</strong></font> Win-SQL-FS-OnDemand <font color="#333333"><strong>-mode incremental1 -load high <br /></strong></font></p> </td> </tr> </tbody></table> <p><font color="#0000ff"><strong>On the SQL Server Agent Jobs for</strong></font> the FULL / Differential Backup add another job step to trigger the DB Backup of flat files to the Tape Media by calling the DOS batch file appropriately.</p> <p><a href="http://lh4.ggpht.com/-OR8SPgHAoFg/UwR9UvipmsI/AAAAAAAABFw/cThBC6FkXms/s1600-h/image%25255B18%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjLarBcLc8L8xGSsm8e4QBDbuNWmLFnatlHvU2Xcwuzbs36QGivBlCwGnOP5yH7nv11Z4NCU_iNNY9L_qIvrEvxb0M_x_soTrRRMJ09kn_oefts-DV8wuo6xRlRzcwKUNDszk-Wtw/?imgmax=800" width="946" height="435" /></a></p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEijJ-i_tGmEbnH8wsVyUeFI_nEcCj0sIVDWvbEei2BynccU7YBAPx0jmQJ2PFj_dTgYd82odAIyD4akjILDdcwjygr5s0YO7V-SeuBr3FE0N_CHbu2TZxwfYviwz_PoIQc9PSTl1g/s1600-h/image%25255B19%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh4.ggpht.com/-OA_aQeQrdDg/UwR9albdTRI/AAAAAAAABGI/rXU2croYb6Q/image_thumb%25255B17%25255D.png?imgmax=800" width="943" height="527" /></a></p> <p><strong>Omnib usage</strong></p> <table cellspacing="0" cellpadding="2" width="912" border="1"><tbody> <tr> <td valign="top" width="910"> <p> <br />Usage synopsis: <br />  <br />    omnib -version | -help <br />  <br />    omnib -filesystem Host:MountPoint Label -device LogicalDevice <br />          [Mirror options]...[Mirror options] <br />          [General options] [Filesystem options] [-public] <br />  <br />    omnib -filesystem Host:MountPoint Label -device LogicalDevice <br />          -ndmp Type [NDMP options] [-public] <br />  <br />    omnib -winfs Host:MountPoint Label -device LogicalDevice <br />          [Mirror options]...[Mirror options] <br />          [General options] [Filesystem options] [Winfs options] <br />          [-public] <br />  <br />    omnib -winfs Host:MountPoint Label -device LogicalDevice <br />          -iap [General options] [Filesystem options] [Winfs options] <br />          [-public] <br />  <br />    omnib -NetWare Host:MountPoint Label -device LogicalDevice <br />          [[Mirror options]...[Mirror options] <br />          [NetWare options] [General options] <br />          [Filesystem options] [-public] <br />  <br />    omnib -host HostName:/ Label -device LogicalDevice <br />          [Mirror options]...[Mirror options] <br />          [General options] [Filesystem options] [-public] [-storedrim] <br />  <br />    omnib -rawdisk Host Label SectionList -device LogicalDevice <br />          [Mirror options]...[Mirror options] <br />          [General options] [-public] <br />  <br />    omnib -omnidb Host:MountPoint Label -device LogicalDevice <br />          [Mirror options]...[Mirror options] <br />          [General options] <br />  <br />    omnib -restart SessionID [-disk_only] <br />  <br />  <strong>  </strong><font style="background-color: #ffff00"><strong>omnib -datalist Name [Datalist options] <br /></strong></font>    <br />    omnib -resume SessionID [-no_monitor] <br />     <br />    omnib -sap_list ListName    <br />          [-barmode SapMode]    <br />          [List options] <br />  <br />    omnib -sapdb_list ListName    <br />          [-barmode SapDBMode]    <br />          [List options] <br />  <br />    omnib -oracle8_list ListName    <br />          [-barmode Oracle8Mode]    <br />          [List options] <br />  <br />    omnib -sybase_list ListName    <br />          [-barmode SybaseMode]    <br />          [List options] <br />  <br />    omnib -informix_list ListName    <br />          [-barmode InformixMode]    <br />          [List options]</p> <p>    omnib -mssql_list ListName    <br />          [-barmode MSSQLMode]    <br />          [List options] <br />  <br />    omnib -msese_list ListName    <br />          [-barmode MSExchangeMode]    <br />          [List options] <br />     <br />    omnib -e2010_list ListName    <br />          [-barmode MSExchange2010Mode]    <br />    omnib -lotus_list ListName    <br />          [-barmode LotusMode]    <br />          [List options] <br />     <br />    omnib -msvssw_list ListName    <br />          [-barmode VSSMode]    <br />          [List options] <br />     <br />    omnib -mbx_list ListName    <br />          [-barmode MSMailboxMode]    <br />          [List options] <br />     <br />    omnib -vmware_list ListName    <br />          [-barmode VMwareMode]    <br />          [List options] <br />     <br />    omnib -db2_list ListName    <br />          [-barmode Db2Mode]    <br />          [List options] <br />     <br />    omnib -mssps_list ListName    <br />          [-barmode MSSPSMode]    <br />          [List options] <br />    <br />    omnib -mssharepoint_list ListName    <br />          [-barmode MSSharePointMode]</p> <p>    omnib -veagent_list ListName    <br />          [-barmode VEPAMode] <br />    </p> <p>    Filesystem options: <br />  <br />        -trees TreeList <br />        -only MatchPattern <br />        -exclude TreeList <br />        -skip MatchPattern <br />        -lock <br />        -touch <br />        -[no_]log | -log_dirs | -log_file <br />        -mode {full | incremental | incremental1 ... | incremental9} <br />        -enh_incr <br />        -clp <br />        -[no_]hlink <br />        -size FromRange ToRange </p> <p>    Winfs options: <br />  <br />        -no_share_[info] <br />        -[no_]nthlinks <br />        -[no_]archatt <br />        -vss {fallback | no_fallback} <br />        -no_vss <br />        -[no_]async </p> <p>    Mirror options: <br />  <br />        -mirror LogicalDevice [-pool MediaPool -prealloc MediaList] </p> <p>    General options: <br />  <br />        -preview <br />        -pool MediaPool <br />        -prealloc MediaList <br />        -protect {none | weeks n | days n | until Date | permanent} <br />        -report {warning | minor | major | critical} <br />        -pre_exec Pathname <br />        -post_exec Pathname <br />        -compress <br />        -encode ["aes256"] <br />        -load {low | medium | high} <br />        -crc <br />        -no_monitor <br />        -keepcatalog {weeks n | days n | until Date} <br />        -var[iable] <var_name> <var_value>     </p> <p>    <font style="background-color: #ffff00"><strong>Datalist options:</strong></font> <br />  <br />        -select SelectList <br />        -mode {full | incremental | incremental1 ... | incremental9} <br />        -protect {none | weeks n | days n | until Date | permanent } <br />        -keepcatalog {weeks n | days n | until Date} <br />        -preview <br />        -load {low | medium | high} <br />        -disk_only <br />        -crc <br />        -no_monitor</p> <p>    List options: <br />  <br />        -barcmnd Command <br />        -protect {none | weeks n | days n | until Date | permanent} <br />        -keepcatalog {weeks n | days n | until Date} <br />        -load {low | medium | high} <br />        -disk_only <br />        -crc <br />        -no_monitor <br />        -test_bar</p> <p>    NetWare options: <br />  <br />        -[no_]NWuncompress </p> <p>    NDMP options:</p> <p>        -ndmp_user UserName <br />        -ndmp_passwd Password <br />        -ndmp_env FileName <br />        -ndmp_bkptype {dump | NVB | SMTape}       <br />    Type = { Generic | NetApp | Celerra | BlueArc | Hitachi | HP-X9000 }    </p> <p>    SapMode      = { full | incr }    </p> <p>    SapDBMode    = { full | diff | trans }     </p> <p>    Oracle8Mode  = { -full | -incr1 | -incr2 | -incr3 | -incr4 }    </p> <p>    SybaseMode   = { full | trans }    </p> <p>    InformixMode = { full | inf_incr1 | inf_incr2 }    </p> <p>    MSSQLMode    = { full | diff | trans }    </p> <p>    MSExchangeMode  = { full | incr }    </p> <p>    MSExchange2010Mode = { full | diff | incr | copy }    </p> <p>    LotusMode    = { -full | -incremental }    </p> <p>    MSMailboxMode   = { -full | -incr | -incr1}    </p> <p>    VMwareMode   = { full | diff | incr}    </p> <p>    VSSMode      = { full | copy | incr | diff}    </p> <p>    Db2Mode      = { -full | -incr | -delta}    </p> <p>    MSSPSMode    = { full | diff | trans }    </p> <p>    MSSharePointMode = { full | diff | incr }    </p> <p>    VEPAMode     = { full | diff | incr }    </p> <p>    Date = [YY]YY/MM/DD   (1969 < [YY]YY < 2038)</p> </td> </tr> </tbody></table> Unknownnoreply@blogger.com2tag:blogger.com,1999:blog-11743729.post-8729801125518241922013-10-18T03:45:00.001-07:002013-10-18T04:00:14.382-07:00Query to get the latest FULL backup details<p> </p> <table border="1" cellspacing="0" cellpadding="2" width="700"><tbody> <tr> <td valign="top" width="698"> <p> SELECT   <br /> cast ((backup_finish_date - backup_start_date) AS TIME(0)) as DURATION, <br /> SERVER_NAME,b.DATABASE_NAME,[TYPE],backup_set_id,   <br />  b.backup_start_date,b.backup_finish_date, <br /> Round(b.backup_size/(1024*1024),2) as Size_n_MB, <br /> Physical_device_name <br />  FROM msdb.dbo.backupset b, <br />  ( select a.DATABASE_NAME, MAX(a.backup_set_id) id <br />  from msdb.dbo.backupset a  <br /> WHERE a.TYPE = 'D' and  <br /> a.DATABASE_NAME = DATABASE_NAME  <br /> group by a.DATABASE_NAME ) cc, <br />  msdb.dbo.backupmediafamily f <br /> WHERE TYPE = 'D' <br /> AND b.backup_set_id = cc.id <br />  AND b.media_set_id = f.media_set_id</p> </td> </tr> </tbody></table> Unknownnoreply@blogger.com2tag:blogger.com,1999:blog-11743729.post-61792784140131212102013-10-18T03:17:00.001-07:002013-10-18T03:17:24.952-07:00Find time difference between two dates<p> </p> <table border="1" cellspacing="0" cellpadding="2" width="527"><tbody> <tr> <td valign="top" width="525"> <p>SELECT <br />b.backup_finish_date, <br />b.backup_start_date, <br /><font color="#0000ff">CAST ((b.backup_finish_date - b.backup_start_date) AS TIME(0))</font> <br />FROM backupset b <br />WHERE convert(varchar(12),b.backup_finish_date,101) = '10/13/2013'</p> </td> </tr> </tbody></table> Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-11743729.post-91250853499574189172013-09-25T02:16:00.001-07:002013-09-25T02:16:45.628-07:00Update job notification<p> </p> <p>Following Query will update the Existing job name starts with ‘WIN%’ as on complete to notify the email operator 'SQLDBASupport'</p> <table border="1" cellspacing="0" cellpadding="2" width="717"><tbody> <tr> <td valign="top" width="715"> <p>select <br />'EXEC msdb.dbo.sp_update_job @job_name=N'+CHAR(39)+name+CHAR(39) <br /> +' , <br />        @notify_level_email=3, <br />        @notify_level_netsend=2, <br />        @notify_level_page=2, <br />        @notify_email_operator_name=N'+char(39)+'SQLDBASupport'+char(39) <br />from msdb.dbo.sysjobs <br />where name like 'WIN%' <br />and notify_email_operator_id =0</p> </td> </tr> </tbody></table> Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-11743729.post-11245347270435968232013-08-15T03:18:00.001-07:002013-08-15T03:18:06.072-07:00SQL Server: Query to check if given column exists<p> </p> <table border="1" cellspacing="0" cellpadding="2" width="400"><tbody> <tr> <td valign="top" width="400"> <pre class="csharpcode"><span class="kwrd">select</span> <br /> s.[name] <span class="str">'Schema'</span>,<br /> t.[name] <span class="str">'Table'</span>,<br /> c.[name] <span class="str">'Column'</span>,<br /> d.[name] <span class="str">'Data Type'</span>,<br /> d.[max_length] <span class="str">'Max Length'</span>,<br /> d.[<span class="kwrd">precision</span>] <span class="str">'Precision'</span>,<br /> c.[is_identity] <span class="str">'Is Id'</span>,<br /> c.[is_nullable] <span class="str">'Is Nullable'</span>,<br /> c.[is_computed] <span class="str">'Is Computed'</span>,<br /> d.[is_user_defined] <span class="str">'Is UserDefined'</span>,<br /> t.[modify_date] <span class="str">'Date Modified'</span>,<br /> t.[create_date] <span class="str">'Date created'</span><br /><span class="kwrd">from</span> sys.schemas s<br /><span class="kwrd">inner</span> <span class="kwrd">join</span> sys.tables t<br /><span class="kwrd">on</span> s.schema_id = t.schema_id<br /><span class="kwrd">inner</span> <span class="kwrd">join</span> sys.columns c<br /><span class="kwrd">on</span> t.object_id = c.object_id<br /><span class="kwrd">inner</span> <span class="kwrd">join</span> sys.types d<br /><span class="kwrd">on</span> c.user_type_id = d.user_type_id<br /><span class="kwrd">where</span> c.name <span class="kwrd">like</span> <span class="str">'Person%'</span></pre><br /> </td><br /> </tr><br /> </tbody></table><br /><style type="text/css"><br />.csharpcode, .csharpcode pre<br />{<br /> font-size: small;<br /> color: black;<br /> font-family: consolas, "Courier New", courier, monospace;<br /> background-color: #ffffff;<br /> /*white-space: pre;*/<br />}<br />.csharpcode pre { margin: 0em; }<br />.csharpcode .rem { color: #008000; }<br />.csharpcode .kwrd { color: #0000ff; }<br />.csharpcode .str { color: #006080; }<br />.csharpcode .op { color: #0000c0; }<br />.csharpcode .preproc { color: #cc6633; }<br />.csharpcode .asp { background-color: #ffff00; }<br />.csharpcode .html { color: #800000; }<br />.csharpcode .attr { color: #ff0000; }<br />.csharpcode .alt <br />{<br /> background-color: #f4f4f4;<br /> width: 100%;<br /> margin: 0em;<br />}<br />.csharpcode .lnum { color: #606060; }</style> Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-11743729.post-87203362769270338642013-04-30T04:47:00.001-07:002013-04-30T04:47:19.143-07:00How to: search for a word or column name across the Procedure, Function, Trigger, View<p> </p> <table border="1" cellspacing="0" cellpadding="2" width="672"><tbody> <tr> <td valign="top" width="670"> <p>DECLARE @SearchPhrase nvarchar(1000) <br />  <br />SET @SearchPhrase = N'backup'   -- Place your search string <br />  <br />SELECT <br />DISTINCT sysobjects.name AS [Object Name], <br />CASE <br />  WHEN sysobjects.xtype = 'P' THEN 'Stored Proc' <br />  WHEN sysobjects.xtype = 'TF' THEN 'Function' <br />  WHEN sysobjects.xtype = 'TR' THEN 'Trigger' <br />  WHEN sysobjects.xtype = 'V' THEN 'View' <br />END as [Object Type], <br />(SELECT ParentTable.[Name] <br /> FROM sysobjects ParentTable <br /> WHERE ParentTable.id = sysobjects.Parent_obj) ParentTable <br />FROM sysobjects, <br />     syscomments <br />WHERE sysobjects.id = syscomments.id <br />AND sysobjects.type in ('P','TF','TR', 'V') <br />--AND sysobjects.category = 0  --- To restrict only user defined object <br />AND CHARINDEX(@SearchPhrase, syscomments.text) > 0 <br /></p> </td> </tr> </tbody></table> Unknownnoreply@blogger.com2tag:blogger.com,1999:blog-11743729.post-92175425973730079172013-04-30T04:30:00.001-07:002013-04-30T04:30:20.403-07:00How to: find when the database last got restored?<p> </p> <div class="csharpcode"> <pre class="alt"><span class="lnum"> 1: </span><span class="kwrd">SELECT</span> destination_database_name,<span class="kwrd">max</span>(restore_date) <span class="kwrd">as</span> restore_date</pre><br /><br /> <pre><span class="lnum"> 2: </span><span class="kwrd">FROM</span> msdb.dbo.restorehistory </pre><br /><br /> <pre class="alt"><span class="lnum"> 3: </span><span class="kwrd">WHERE</span> destination_database_name = <span class="str">'testDB’ ---- Place your database name here</span></pre><br /><br /> <pre><span class="lnum"> 4: </span><span class="kwrd">GROUP</span> <span class="kwrd">BY</span> destination_database_name</pre><br /></div><br /><style type="text/css"><br />.csharpcode, .csharpcode pre<br />{<br /> font-size: small;<br /> color: black;<br /> font-family: consolas, "Courier New", courier, monospace;<br /> background-color: #ffffff;<br /> /*white-space: pre;*/<br />}<br />.csharpcode pre { margin: 0em; }<br />.csharpcode .rem { color: #008000; }<br />.csharpcode .kwrd { color: #0000ff; }<br />.csharpcode .str { color: #006080; }<br />.csharpcode .op { color: #0000c0; }<br />.csharpcode .preproc { color: #cc6633; }<br />.csharpcode .asp { background-color: #ffff00; }<br />.csharpcode .html { color: #800000; }<br />.csharpcode .attr { color: #ff0000; }<br />.csharpcode .alt <br />{<br /> background-color: #f4f4f4;<br /> width: 100%;<br /> margin: 0em;<br />}<br />.csharpcode .lnum { color: #606060; }</style><br /><br /><p>The result will look like;</p><br /><br /><p>destination_database_name     restore_date<br /> <br />testDB                        2013-04-29 11:49:19.940</p><br /><style type="text/css"><br />.csharpcode, .csharpcode pre<br />{<br /> font-size: small;<br /> color: black;<br /> font-family: consolas, "Courier New", courier, monospace;<br /> background-color: #ffffff;<br /> /*white-space: pre;*/<br />}<br />.csharpcode pre { margin: 0em; }<br />.csharpcode .rem { color: #008000; }<br />.csharpcode .kwrd { color: #0000ff; }<br />.csharpcode .str { color: #006080; }<br />.csharpcode .op { color: #0000c0; }<br />.csharpcode .preproc { color: #cc6633; }<br />.csharpcode .asp { background-color: #ffff00; }<br />.csharpcode .html { color: #800000; }<br />.csharpcode .attr { color: #ff0000; }<br />.csharpcode .alt <br />{<br /> background-color: #f4f4f4;<br /> width: 100%;<br /> margin: 0em;<br />}<br />.csharpcode .lnum { color: #606060; }</style> Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-11743729.post-89184303758320578192013-04-30T04:17:00.001-07:002013-04-30T04:17:40.789-07:00SQL 2008 Management Studio does not allow to save changes to tables<p> </p> <p>In SQL Server 2008 Management Studio,</p> <p>We can not do</p> <p>   •Change the Allow Nulls setting for a column</p> <p>   •Reorder the columns</p> <p>   •Change the column data type</p> <p>    •Add a new column</p> <p>How to fix / Resolution:</p> <p>    •Go to Tools -> Options -> Designers -> Tables and Designers</p> <p>     •Uncheck the Prevent Saving Changes that require table re-creation</p> <p><a href="http://lh5.ggpht.com/-Ik_BMD6_LSc/UX-oRmb_B_I/AAAAAAAAA_8/9MhVhBXQgWw/s1600-h/image%25255B4%25255D.png"><img style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh6.ggpht.com/-ARBCs9FVs0w/UX-oUpOcWgI/AAAAAAAABAE/J2WZZbzwID4/image_thumb%25255B2%25255D.png?imgmax=800" width="601" height="368" /></a></p> Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-11743729.post-20256489548928864832013-03-20T09:34:00.001-07:002013-03-20T09:34:29.542-07:00SQL Server Contained Database Authentication process flow<p> </p> <p> </p> <p> </p> <p><a href="http://lh5.ggpht.com/-Hf_JsqfduC4/UUnlCvN9rzI/AAAAAAAAA_I/-Dk2oPO8Yc4/s1600-h/image%25255B7%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh9id0dfEoFCuUJF3SzH_ZU_YvekkC8wWNXDuAJejDMxgpIQL2P6LmTlVGZ8G_uL8wXRL8m4yLO2kX6uOIeBr3C2TZwSVRzPpJPpAcKbQ_l5bVNQNa4EuInsq4p__FZfE4rHCWZQw/?imgmax=800" width="712" height="651" /></a></p> <p>Source: <a title="http://blogs.msdn.com/b/sqlsecurity/archive/2010/12/08/contained-database-authentication-in-depth.aspx" href="http://blogs.msdn.com/b/sqlsecurity/archive/2010/12/08/contained-database-authentication-in-depth.aspx">http://blogs.msdn.com/b/sqlsecurity/archive/2010/12/08/contained-database-authentication-in-depth.aspx</a></p> Unknownnoreply@blogger.com2tag:blogger.com,1999:blog-11743729.post-38026561779930646612012-12-03T00:31:00.001-08:002012-12-03T00:31:14.444-08:00The operation cannot be performed on a database with database snapshots or active DBCC replicas<p> </p> <p>When trying to restore the database with snapshots got the following error SQL message</p> <p><strong>SQL Server T-SQL Command</strong></p> <table border="0" cellspacing="0" cellpadding="2" width="685"><tbody> <tr> <td valign="top" width="683"><strong><font color="#0000ff">restore database PRD_Accounts from disk ='H:\PRD_Accounts_backup_daily.bak' with replace, stats</font></strong></td> </tr> </tbody></table> <p><strong>Error:</strong></p> <p><font color="#0000ff">Msg 5094, Level 16, State 2, Line 1 <br />The operation cannot be performed on a database with database snapshots or active DBCC replicas. <br />Msg 3013, Level 16, State 1, Line 1 <br />RESTORE DATABASE is terminating abnormally. <br /></font></p> <p><strong><font color="#333333">Resolution</font></strong></p> <p>Since we cannot restore a database if we have any snapshots. <br />Please drop all snapshots and try database restore again.</p> <p>1. To check the database snapshot and it’s source database</p> <p> <table border="0" cellspacing="0" cellpadding="2" width="730"><tbody> <tr> <td valign="top" width="728"> <p>select database_id, name,source_database_id,Source_DBName=DB_NAME(source_database_id) <br /> from master.sys.databases <br />where source_database_id IS NOT NULL</p> </td> </tr> </tbody></table> </p> <p>2. Drop the Snapshot database</p> <table border="0" cellspacing="0" cellpadding="2" width="265"><tbody> <tr> <td valign="top" width="263"> DROP DATABASE Accounts_Rpt </td> </tr> </tbody></table> <p> <br />3. Create script of snapshot database, before dropping it. If at all we need to re-establish the snapshot after restore we can use it</p> <table border="0" cellspacing="0" cellpadding="2" width="766"><tbody> <tr> <td valign="top" width="764"> <p>USE master <br />GO</p> <p>/****** Object:  Database [Benefits_Rpt_sol48u]    Script Date: 12/03/2012 00:01:02 ******/ <br />CREATE DATABASE [Accounts_Rpt] ON <br />( NAME = N'PRD_Accounts', FILENAME = N'E:\MSSQL\Data\Accounts_Rpt.ss1' ), <br />( NAME = N'indexdat1', FILENAME = N'E:\MSSQL\Data\Accounts_Rpt.ss2' ) AS SNAPSHOT OF [PRD_Accounts] <br />GO <br /></p> </td> </tr> </tbody></table> <font color="#0000ff"></font> Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-11743729.post-10721490246739580432012-11-08T04:38:00.001-08:002012-11-08T04:38:35.116-08:00Error on executing sp_cycle_agent_errorlog Msg 22022, Level 16, State 1, Line 0<p><strong>sp_cycle_agent_errorlog</strong></p> <p><a></a></p> <p>Every time SQL Server Agent is started, the current SQL Server Agent error log is renamed to <strong>SQLAgent.1</strong>; <strong>SQLAgent.1</strong> becomes <strong>SQLAgent.2</strong>, <strong>SQLAgent.2</strong> becomes <strong>SQLAgent.3</strong>, and so on. <strong>sp_cycle_agent_errorlog</strong> enables you to cycle the error log files without stopping and starting the server.</p> <p>This stored procedure must be run from the <strong>msdb</strong> database.</p> <p>Error on executing of sp_cycle_agent_errorlog</p> <p><a href="http://lh6.ggpht.com/-YzWSN9DZMNY/UJunkNxalGI/AAAAAAAAA-Y/uOKWWF_oZ0U/s1600-h/clip_image002%25255B16%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="clip_image002" border="0" alt="clip_image002" src="http://lh5.ggpht.com/-aU1ryD0U8Po/UJunqaCA8wI/AAAAAAAAA-g/qi3PBQkpijo/clip_image002_thumb%25255B13%25255D.jpg?imgmax=800" width="530" height="460" /></a></p> <p><a href="http://lh4.ggpht.com/-DxmhLoqgX00/UJuns7Rtg2I/AAAAAAAAA-o/8YvYcV9dvSo/s1600-h/image%25255B19%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh6j2NwUBXBIy35w_9XuPTGbaQuaF1BoX1EjWB7DXNcrrc3hbOUAx58NXUpvpK6c1JsrllxwsGq4VLuWcS6uxbfKWIpF-Um-Rq673pYoTvy8Qs9PgYIulqnaGjOecS9FX8n2pyj0A/?imgmax=800" width="667" height="580" /></a></p> <p> </p> <p>USE msdb</p> <p>go</p> <p>EXEC sp_cycle_agent_errorlog</p> <p>GO</p> <p>Msg 22022, Level 16, State 1, Line 0</p> <p>SQLServerAgent Error: Access is denied.</p> <p>Each time the stored procedure was run it just overwritten the current agent log without renaming it and because of this only one SQLAGENTOUT file was left.</p> Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-11743729.post-54634504971757746832012-11-07T23:17:00.001-08:002012-11-07T23:17:57.864-08:00SQL Mail: The mail could not be sent to the recipients because of the mail server failure.<p> <br />SQL Mail Sent fail with errors below;</p> <p><strong>Error from the SQL Mail log:</strong></p> <p>The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2012-11-07T22:09:50). Exception Message: Could not connect to mail server. (No connection could be made because the target machine actively refused it 205.191.22.44:25). )</p> <p>The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2012-01-17T11:13:58). Exception Message: Could not connect to mail server. (No such host is known). )</p> <p>The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2012-01-17T11:12:44). Exception Message: Cannot send mails to mail server. (Failure sending mail.). )</p> <p>The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2010-10-24T19:55:11). Exception Message: Could not connect to mail server. (A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond 16.236.32.51:25). )</p> <p><strong>Resolution:</strong></p> <p>From the error message it is clear that it is a communication issue between SQL Server host and the Mail Exchange Server.</p> <p>Problem was related to Exchange Server not allowing relaying from SQL Server. </p> <p>Please engage Network Administrator to add SQL Server host IP Address to allow to send emails taking below steps, like </p> <p><font color="#0000ff">On Mail server > Exchange Server Manager > SMTP Connector Properties <br /> > Access Properties > Access Tab > Relay button > Add  IP Address of SQL Server</font></p> <p><font color="#333333">Also cross check if the Anti Virus (AV) program running on the SQL Server host. AV blocks services from sending on TCP-25. Adjust the AV software accordingly. Add DATABASEMAIL90.EXE to the list of innocent programs in your AV.</font></p> Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-11743729.post-7007792986029143582012-11-06T02:10:00.001-08:002012-11-06T02:10:45.233-08:00Query to find the Linked Servers with given remote login<p> </p> <p>It would be useful to cross check, when the Linked Server remote login password has changed. <br />In this case, linked server will wail due to wrong password. To fix this we have reset the correct password.</p> <table border="0" cellspacing="0" cellpadding="2" width="771"><tbody> <tr> <td valign="top" width="769"> <p>SELECT s.server_id,S.name,S.product,S.provider,l.remote_name,'Remote_Login_Modified_Dt'=l.modify_date,s.modify_date <br />FROM SYS.SERVERS S <br />INNER JOIN SYS.LINKED_LOGINS L <br />ON  s.server_id = L.server_id <br />WHERE S.server_id > 0 <br />AND S.product ='SQL Server' <br />AND L.remote_name ='sa'</p> </td> </tr> </tbody></table> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-11743729.post-7133505605790582832012-11-06T00:44:00.001-08:002012-11-06T00:44:56.938-08:00Returning the parameters for a specified stored procedure or function<p> </p> <p>Before you run the following query, replace <database_name> and <schema_name.object_name> with valid names. <br /></p> <table border="0" cellspacing="0" cellpadding="2" width="548"><tbody> <tr> <td valign="top" width="546"> <p>USE <database_name>; <br />GO <br />SELECT SCHEMA_NAME(schema_id) AS schema_name <br />    ,o.name AS object_name <br />    ,o.type_desc <br />    ,p.parameter_id <br />    ,p.name AS parameter_name <br />    ,TYPE_NAME(p.user_type_id) AS parameter_type <br />    ,p.max_length <br />    ,p.precision <br />    ,p.scale <br />    ,p.is_output <br />FROM sys.objects AS o <br />INNER JOIN sys.parameters AS p ON o.object_id = p.object_id <br />WHERE o.object_id = OBJECT_ID('<schema_name.object_name>') <br />ORDER BY schema_name, o.object_name, p.parameter_id; <br />GO</p> </td> </tr> </tbody></table> <p> </p> <p>Reference: <a title="http://msdn.microsoft.com/en-us/library/ms190324.aspx" href="http://msdn.microsoft.com/en-us/library/ms190324.aspx">http://msdn.microsoft.com/en-us/library/ms190324.aspx</a></p> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-11743729.post-6318465553118547302012-11-06T00:42:00.001-08:002012-11-06T00:42:23.371-08:00List database objects modified in the last ‘N’ days<p> </p> <p>Before you run the following query, replace <database_name> and <n_days> with valid values.</p> <p> </p> <table border="0" cellspacing="0" cellpadding="2" width="400"><tbody> <tr> <td valign="top" width="400"> <p>USE <database_name>; <br />GO <br />SELECT name AS object_name <br />  ,SCHEMA_NAME(schema_id) AS schema_name <br />  ,type_desc <br />  ,create_date <br />  ,modify_date <br />FROM sys.objects <br />WHERE modify_date > GETDATE() - <n_days> <br />ORDER BY modify_date; <br />GO <br /></p> </td> </tr> </tbody></table> <p> </p> <p>Source: <a title="http://msdn.microsoft.com/en-us/library/ms190324.aspx" href="http://msdn.microsoft.com/en-us/library/ms190324.aspx">http://msdn.microsoft.com/en-us/library/ms190324.aspx</a></p> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-11743729.post-22654448628321596272012-11-01T03:18:00.001-07:002012-11-01T03:18:35.491-07:00Script to check SQL Server Linked Server Status<p> </p> <p>It leverages on the system stored procedure <strong><font color="#0000ff">sys.sp_testlinkedserver</font></strong> </p> <p> <br /> <table border="0" cellspacing="0" cellpadding="2" width="883"><tbody> <tr> <td valign="top" width="881"> <p>SET NOCOUNT ON; <br />DECLARE @LinkName NVARCHAR (128), @retval INT, @msg varchar(300) <br />IF (SELECT COUNT (srvname) FROM sys.sysservers WHERE srvname <> @@SERVERNAME) = 0 RETURN</p> <p>DECLARE srvname INSENSITIVE CURSOR FOR <br />        (SELECT srvname FROM sys.sysservers WHERE srvname <> @@SERVERNAME) FOR READ ONLY <br />OPEN srvname <br />    FETCH NEXT FROM srvname INTO @LinkName <br />WHILE @@FETCH_STATUS = 0 <br />BEGIN <br />    BEGIN TRY <br />        EXEC @retval = sys.sp_testlinkedserver @LinkName <br />    END TRY <br />    <br />    BEGIN CATCH <br />        IF @LinkName IS NULL RETURN <br />        SET @retval = SIGN(@@ERROR) <br />        IF @retval <> 0 <br />        BEGIN <br />          SET @msg = 'Unable to connect to the Linked server : ' + @LinkName <br />          RAISERROR (@msg, 16, 2 ) <br />        END <br />    END CATCH <br />    FETCH NEXT FROM srvname INTO @LinkName <br />    <br />END    <br />    <br />CLOSE srvname <br />DEALLOCATE srvname <br /></p> </td> </tr> </tbody></table></p> Unknownnoreply@blogger.com4tag:blogger.com,1999:blog-11743729.post-83571566019071617052012-10-03T02:10:00.000-07:002012-10-05T02:11:51.488-07:00Failed to decrypt protected XML node "PassWord" with error 0x80070002 "The system cannot find the file specified.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available<p> </p> <h4>SSIS Package – SQL Agent Job Error:</h4> <p>Executed as user: PRODUCTION\SQLSrvr. ...te Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:08:22 PM Error: 2012-07-25 22:08:23.31 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "PassWord" with error 0x80070002 "The system cannot find the file specified.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2012-07-25 22:08:23.32 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "PassWord" with error 0x80070002 "The system cannot find the file specified.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error DTExec: The package execution returned DTSER_SUCCESS (0). Started: 10:08:22 PM Finished: 10:. The step succeeded.</p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgdVrCQhFzbXSlbfmBJZNDO-4Ws2_yzt6_2k3DsTAkPkgr1XFGdBxzK_5v1U1NWVA52qV9mlMEDjydWhMdjX9oAJ1KvpDf0Sl4rMVR2KuviVnR2f9fIfxK1zVVKNmzkr48jJ_iE3A/s1600-h/image%25255B28%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhcMIrWLHbHat9h71GSRNwlztv6_FLlhP24XynkJfHREBe7D0CKFs9I6fMPbBCVMZjtFBcMKXu6N14XjTrRrXCxewpMPVkbpIuNjD6NqIBng38ipET4QIgxFbgTO6bHKnGjjP1pLA/?imgmax=800" width="600" height="255" /></a></p> <p><strong>Solution:</strong></p> <p> If you are using SSIS 2005 - you need to save the packages as <b><font color="#0000ff">Do Not save sensitive</font></b> - which you are doing.</p> <p>When you import the package into the SSIS system you need to make sure you alter the package protection level there to <b><font color="#0000ff">do not save sensitive</font></b> too. In SQL Server 2008 and above this is the default. <br /> <br /><a href="http://lh5.ggpht.com/-6lL4asVFCZQ/UG6kSvWw4zI/AAAAAAAAA98/koRw8XOQkVc/s1600-h/image%25255B10%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh6.ggpht.com/-LxVwuoKIdR8/UG6kUqK9x4I/AAAAAAAAA-E/qJxn-JXwDWw/image_thumb%25255B8%25255D.png?imgmax=800" width="533" height="371" /></a></p> <p>Reference: <a href="http://support.microsoft.com/kb/918760">http://support.microsoft.com/kb/918760</a></p> Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-11743729.post-32145702970650631112012-09-25T00:55:00.001-07:002012-09-25T00:55:24.151-07:00How to find on which node SQL Server instance was running before restart?<p> </p> <p>In situation, where SQL Server instance running on a node is restarted usually after Windows Operating System patching or for some other reason, there may be need to find to which node the SQL Server instance was running on before.</p> <p>Here is the solution for this;</p> <p>By default whenever SQL Server instance restarted on Clustered Environment, on SQL Server Error Log it will post the message below;</p> <table border="0" cellspacing="0" cellpadding="2" width="611"><tbody> <tr> <td valign="top" width="609">The NETBIOS name of the local node that is running the server is 'ClusterNode1'.</td> </tr> </tbody></table> <p>So, making use of the xp_ReadErrorLog we can query the ErrorLog and find the details.</p> <table border="0" cellspacing="0" cellpadding="2" width="524"><tbody> <tr> <td valign="top" width="522">-- To know the list available Error log files <br />exec xp_enumerrorlogs 1 <br /> <br /> <br />-- Search the available error log files <br />exec xp_ReadErrorLog 0, 1 , 'NETBIOS name of the local node' <br />exec xp_ReadErrorLog 1, 1 , 'NETBIOS name of the local node' <br />exec xp_ReadErrorLog 2, 1 , 'NETBIOS name of the local node' <br />exec xp_ReadErrorLog 3, 1 , 'NETBIOS name of the local node' <br />exec xp_ReadErrorLog 4, 1 , 'NETBIOS name of the local node' <br />exec xp_ReadErrorLog 5, 1 , 'NETBIOS name of the local node' <br /></td> </tr> </tbody></table> Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-11743729.post-55051416551374501262012-09-06T01:47:00.000-07:002012-09-25T01:53:33.753-07:00How to connect to Oracle Database from SQL Server instance and query the Oracle Database?<p> </p> <p>How to connect to Oracle Database from SQL Server instance and query the Oracle Database?</p> <p>Linked Server is the solution for this. <br />A linked server allows for access to <i><u>distributed, heterogeneous</u></i> queries against <u>OLE DB data sources</u>. After a linked server is created, distributed queries can be run against this server, and queries can join tables from more than one data source. If the linked server is defined as an instance of SQL Server, remote stored procedures can be executed.</p> <p>A linked server definition specifies the following objects: </p> <p>· An OLE DB provider : is a DLL that manages and interacts with a specific data source</p> <p>· An OLE DB data source : identifies the specific database that can be accessed through OLE DB</p> <p><b>Basics of Linked Server Configuration:</b></p> <p><a href="http://lh5.ggpht.com/-kzM7BNBNWSQ/UGFwDhq5cII/AAAAAAAAA6Q/f3P92fEfQuc/s1600-h/clip_image001%25255B3%25255D.gif"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="clip_image001" border="0" alt="clip_image001" src="http://lh6.ggpht.com/-j7TxycHakM4/UGFwGy49BmI/AAAAAAAAA6Y/dfp5WlA-kV4/clip_image001_thumb.gif?imgmax=800" width="244" height="204" /></a></p> <p><b>Advantages of Linked Server:</b></p> <p>· The ability to access data from outside of SQL Server.</p> <p>· The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.</p> <p>· The ability to address diverse data sources similarly.</p> <p><b>Pre-requisite</b></p> <p>· Install Oracle Client</p> <p>· Configure tnsnames.ora <br /></p> <p>Example <table border="1" cellspacing="0" cellpadding="0"><tbody> <tr> <td valign="top" width="638"> <p>STG21.SIVA =</p> <p>(DESCRIPTION =</p> <p>(ADDRESS_LIST =</p> <p>(ADDRESS = (PROTOCOL = TCP)(HOST = oraDBsrvr.siva.com)(PORT = 60000))</p> <p>)</p> <p>(CONNECT_DATA =</p> <p>(SERVICE_NAME = stg01.siva)</p> <p>)</p> </td> </tr> </tbody></table> </p> <p>Validate tnsnames.ora entry <table border="1" cellspacing="0" cellpadding="0"><tbody> <tr> <td valign="top" width="638"> <p>On Command Prompt</p> <p>Tnsping STG21.SIVA</p> </td> </tr> </tbody></table> </p> <p><strong><u>Notes:</u></strong></p> <p>Here we are going to use <b><i>Microsoft ODBC for Oracle</i></b> as the OLE DB Provider. <br />The STG21.SIVA is going to be <b>OLE DB Data Source</b> pointing to the <b><i>Oracle Database</i></b> STG01.SIVA</p> <p>Open up ODBC</p> <p><a href="http://lh6.ggpht.com/-ZjhnRbkq0S0/UGFwJKk0hLI/AAAAAAAAA6g/CygsRpy3HeE/s1600-h/clip_image003%25255B3%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="clip_image003" border="0" alt="clip_image003" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhfkVSHX-MFNJ8DAbB-wpcX7N3DjROk1nc_XPdkuUqqTDtC5YGcH_rEk6uRoB2lGHgibINEN-0ItW8fFGMQllTqPYeJR0oAQ9WO0CvYRhFQvUG3KgTARYQ9IawpLXurM60dYoRVQQ/?imgmax=800" width="208" height="244" /></a></p> <p>Select <b>System</b> tab</p> <p><a href="http://lh5.ggpht.com/-Fv9z0EZ9AYs/UGFwPESHxvI/AAAAAAAAA6w/iCwTuyJ04QM/s1600-h/clip_image005%25255B3%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="clip_image005" border="0" alt="clip_image005" src="http://lh5.ggpht.com/-8iwRcNrplbI/UGFwQ01cjrI/AAAAAAAAA64/SntIj76zHdY/clip_image005_thumb.jpg?imgmax=800" width="244" height="199" /></a></p> <p>Click <b>Add</b></p> <p>Select <b>Microsoft ODBC for Oracle</b></p> <p><a href="http://lh6.ggpht.com/-jiAg2RkIVwU/UGFwTF3McyI/AAAAAAAAA7A/Ig4zv6xXsA4/s1600-h/clip_image007%25255B3%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="clip_image007" border="0" alt="clip_image007" src="http://lh6.ggpht.com/-U02cXVSoh9M/UGFwU_wZadI/AAAAAAAAA7I/KZISMobft8o/clip_image007_thumb.jpg?imgmax=800" width="244" height="196" /></a></p> <p>Click Finish</p> <p><a href="http://lh6.ggpht.com/-dS_Trs5hqsI/UGFwXpLm-HI/AAAAAAAAA7Q/KOGLB5OfVP4/s1600-h/clip_image009%25255B3%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="clip_image009" border="0" alt="clip_image009" src="http://lh5.ggpht.com/-f3K5tOhLB3Q/UGFwZ_HAhnI/AAAAAAAAA7Y/EiikPKtriRQ/clip_image009_thumb.jpg?imgmax=800" width="244" height="183" /></a></p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjAE24UbNdEJUuF3BFmM59m67AmZ_CjNzHKGmJv4ukwhCJW2BRGFDwQP40tA5sVVUqWAD80ejiE2_in7_1jsKehO2CzqpqtvGsl_SRKjLKnkIXDhAogcf6xKMMTsHo5KNupsPvUXQ/s1600-h/clip_image011%25255B3%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="clip_image011" border="0" alt="clip_image011" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg9h8U3a-CHRqD3Iur-ZMQkyOBaGFsG5bdpkXMz83hj-qjlFcNDZ5b0Z7PhrJBAGmTQI4_6lYD5foIWUQCV20dnN2q6cWgRDwolgzL47UeCtNOz_KOwYQyDIq-uW7uMpOShcAXDIA/?imgmax=800" width="244" height="195" /></a></p> <p>Click OK, OK</p> <p>Connect to <b>SQL Server Management Studio</b></p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgqrHtAeTujXpn3ygbs-cs7jl1A8ulkvKCQT7NMp4D1sCpLlTj5PxHlM1dZ69IgCzzcJEs_5diW1DuHQ8E8moY4FH5RqAaxcIpMDwGRkhDbBHKvpk1EpbabDqDjFSJLXgPQKjRA4w/s1600-h/clip_image013%25255B3%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="clip_image013" border="0" alt="clip_image013" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjDzf-junDkX0g2a0bzQPzgdeCsmMVyIZO6uGmFcV2EmMi_1ktPhmSzDLaodk97YeGSbUQlHxStku_s4QOjCbNL9YijpqATvKsTnxNF0xTVEMLbJFDYAcMS9vtZQLjriMMPMsLuDA/?imgmax=800" width="218" height="244" /></a></p> <p>Right click select new <b>Linked Server</b></p> <p><a href="http://lh6.ggpht.com/-CNniyp_ODDI/UGFwoiEuM6I/AAAAAAAAA8A/ETXPsL5ew3I/s1600-h/clip_image015%25255B3%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="clip_image015" border="0" alt="clip_image015" src="http://lh4.ggpht.com/-HrWuDoCp1L8/UGFwqcDlK8I/AAAAAAAAA8I/7CXVSdUOzjQ/clip_image015_thumb.jpg?imgmax=800" width="244" height="91" /></a></p> <p><a href="http://lh6.ggpht.com/-F9x6JvNtyV4/UGFws2jRjSI/AAAAAAAAA8Q/NyS2wjhQRMQ/s1600-h/clip_image017%25255B3%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="clip_image017" border="0" alt="clip_image017" src="http://lh3.ggpht.com/-ouBWZa1qpxs/UGFwvG9dqAI/AAAAAAAAA8Y/ne3UOnmAeRs/clip_image017_thumb.jpg?imgmax=800" width="244" height="219" /></a></p> <p><a href="http://lh6.ggpht.com/-po7Fzyrk7yM/UGFwxv5KI6I/AAAAAAAAA8g/j4lkhrMtWuY/s1600-h/clip_image019%25255B3%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="clip_image019" border="0" alt="clip_image019" src="http://lh3.ggpht.com/-PuFAa5O3hqY/UGFwzlIvBkI/AAAAAAAAA8o/75F1DJ5PN7Q/clip_image019_thumb.jpg?imgmax=800" width="244" height="223" /></a></p> <p><a href="http://lh3.ggpht.com/-eZpCBNzuEhs/UGFw3C79gqI/AAAAAAAAA8w/Pt25x_dyE3o/s1600-h/clip_image021%25255B3%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="clip_image021" border="0" alt="clip_image021" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhWhFDIeunS5Brpukb_E43HA1eu9dub6VHmiE7b1V5DVy8N7M3HKOqQVUI42NcH9O2HnMzbKYAB7TiIUPWf7UiCV3H7dQglodKAIkT1D7aFFoWs-PEvENTwRHdaPTC2APxoyGW2eQ/?imgmax=800" width="244" height="221" /></a></p> <p>Click OK</p> <p>Test Connection</p> <p>Right click select Test connection</p> <p><a href="http://lh6.ggpht.com/-BwkxMp5i9Uk/UGFw74cxl1I/AAAAAAAAA9A/VwUo67rB-HY/s1600-h/clip_image023%25255B3%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="clip_image023" border="0" alt="clip_image023" src="http://lh4.ggpht.com/-3LMcsTCYaqE/UGFw-NWmQMI/AAAAAAAAA9I/NVGnpNlSljU/clip_image023_thumb.jpg?imgmax=800" width="244" height="143" /></a><a href="http://lh6.ggpht.com/-WyO1HtwXVdI/UGFw_nemc0I/AAAAAAAAA9Q/hlpIc_RHXn8/s1600-h/clip_image025%25255B3%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="clip_image025" border="0" alt="clip_image025" src="http://lh6.ggpht.com/-tNDlSlxp94E/UGFxCd77pgI/AAAAAAAAA9Y/oc4FsGry39c/clip_image025_thumb.jpg?imgmax=800" width="244" height="96" /></a></p> <p><strong>Validation:</strong></p> <p>Just run the sample query <table border="1" cellspacing="0" cellpadding="0"><tbody> <tr> <td valign="top" width="638"> <p>exec ('select user, sysdate from dual') at STG21_SQLSVR54</p> </td> </tr> </tbody></table> </p> <p>Output look like; <table border="1" cellspacing="0" cellpadding="0"><tbody> <tr> <td valign="top" width="638"> <p>USER SYSDATE</p> <p>SQLSVR07 2012-09-25 01:37:02.000</p> </td> </tr> </tbody></table></p> Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-11743729.post-82067001695722936302012-05-24T04:41:00.001-07:002012-05-24T04:41:26.253-07:00MS Visual Studio 2010 Source Control Explorer shortcut<p> </p> <table border="1" cellspacing="0" cellpadding="2" width="834"><tbody> <tr> <td valign="top" width="832">"C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\devenv.exe"/Command View.TfsSourceControlExplorer </td> </tr> </tbody></table> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-11743729.post-7276893791781105432012-05-10T18:07:00.001-07:002012-05-10T18:07:14.106-07:00Free ebooks: Great content from Microsoft Press that won’t cost you a penny<p> </p> <p>Please try downloading SQL Server 2012, 2008 R2 and more from the link below</p> <p><a title="http://blogs.msdn.com/b/microsoft_press/archive/2012/05/04/free-ebooks-great-content-from-microsoft-press-that-won-t-cost-you-a-penny.aspx" href="http://blogs.msdn.com/b/microsoft_press/archive/2012/05/04/free-ebooks-great-content-from-microsoft-press-that-won-t-cost-you-a-penny.aspx">http://blogs.msdn.com/b/microsoft_press/archive/2012/05/04/free-ebooks-great-content-from-microsoft-press-that-won-t-cost-you-a-penny.aspx</a></p> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-11743729.post-86190379216570916912012-04-02T23:28:00.001-07:002012-04-02T23:28:31.083-07:00Start multiple Putty sessions using DOS Batch file<p> </p> <p>There is a requirement for me open up multiple sessions of same HP Unix Host server. I just automate this using simple DOS Batch file below;</p> <table border="1" cellspacing="0" cellpadding="2" width="863"><tbody> <tr> <td valign="top" width="861"> <p>@echo off <br />set uname=siva <br />set <a href="mailto:pwd=#`@F$">pwd=#`F$</a>(*&!-=~</p> <p>start /max  C:\Windows\System32\putty.exe -load "D1- Session1" -l %uname% -pw %pwd%   <br />start /max  C:\Windows\System32\putty.exe -load "D1- Session2" -l %uname% -pw %pwd% <br />start /max  C:\Windows\System32\putty.exe -load "D1- Session3" -l %uname% -pw %pwd%   <br />start /max  C:\Windows\System32\putty.exe -load "D1- Session4" -l %uname% -pw %pwd% <br />start /max C:\Windows\System32\putty.exe –load  "D1–Session5" -l %uname% -pw %pwd% </p> <p>@echo on</p> </td> </tr> </tbody></table> Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-11743729.post-12394348111935294802012-03-21T23:42:00.000-07:002012-03-22T00:00:37.639-07:00Outlook Public folder: How to set up the AutoForward Rule on Public Folder<p> </p> <p>You must have Owner (Owner permission: With Owner permission you can create, read, modify, and delete all items and files and create subfolders. As folder owner, you can change the permission level that others have for the folder. This permission does not apply to delegates.) permission for a public folder to create or modify rules (rule: One or more automatic actions taken on e-mail messages and meeting requests that meet certain conditions, along with any exceptions to those conditions. Rules are also referred to as filters.) for it.</p> <ol> <li>In the Folder List (Folder List: Displays the folders available in your mailbox. To view subfolders, click the plus sign (+) next to the folder. If the Folder List is not visible, on the Go menu, click Folder List.), in the Navigation Pane (Navigation Pane: The column on the left side of the Outlook window that includes panes such as Shortcuts or Mail and the shortcuts or folders within each pane. Click a folder to show the items in the folder.), right-click the public folder you want to create or modify rules for, and then click Properties on the shortcut menu. </li> <li>Click the Administration tab. </li> <li>Click Folder Assistant. <br />Do either of the following: </li> <li>To create a rule, click Add Rule. </li> <li>To modify an existing rule, click a rule in the list, and then click Edit Rule. <br />Enter the conditions that the item must meet for the action to occur. For more conditions, click Advanced. <br />Under Perform these actions, select the options you want. <br /></li> </ol> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-11743729.post-86315169721450240452012-03-19T02:40:00.001-07:002012-03-19T02:40:50.412-07:00Quick way of getting Date and Time appended on filename in DOS Batch<p> </p> <table border="1" cellspacing="0" cellpadding="2" width="875"><tbody> <tr> <td valign="top" width="873"> <p>echo off</p> <p>set filename=F:\Sivaprasad\Error%time:~0,2%%time:~3,2%%time:~6,2%_%date:~-10,2%%date:~-7,2%%date:~-4,4%.txt</p> <p>echo  %filename%</p> </td> </tr> </tbody></table> Unknownnoreply@blogger.com15