tag:blogger.com,1999:blog-1072220331929891892024-02-08T00:21:49.390-05:00SQL_QuestA blog from SQL Server and Sybase DBASQL_Questhttp://www.blogger.com/profile/14909213275379800133noreply@blogger.comBlogger25125tag:blogger.com,1999:blog-107222033192989189.post-5559321199606277372014-11-24T10:54:00.000-05:002014-11-24T10:54:52.912-05:00Moved the blog to Azure - New SiteThis will be the last post on this blog ... The blog have moved to <a href="http://sqlsybase.azurewebsites.net/">http://sqlsybase.azurewebsites.net/</a>
Thanks for visiting.
Cheers !SQL_Questhttp://www.blogger.com/profile/14909213275379800133noreply@blogger.com0tag:blogger.com,1999:blog-107222033192989189.post-21085177597039134622012-01-23T23:17:00.001-05:002012-01-23T23:32:18.654-05:00BCP for Sybase ASE<p><strong><font color="#4dbc0a" size="3">As I promised to keep my blog green with cool stuff, below is my first posting for 2012.</font></strong></p> <p><u><strong>Scenario:</strong></u> We had a weird problem with Sybase ASE 1503 just for one database where in if we try to dump a database, ASE will throw stack trace. This was weird as out of all the databases on the server, this was the only one having problem. I will call this database as “<em><strong>problem_db</strong></em>”.</p> <p><strong><u>Approach:</u></strong> First and the most common approach was to restart backup server which did not helped much and the stack trace was still thrown in the error log.</p> <p>Secondly, we contacted Sybase and asked for help with this situation and they recommended to delete statistics and recreate them back and we ran dbcc to make sure there were no errors reported. We followed that and still the situation was same.</p> <p>Until Sybase comes back to us with a proper fix, we decided to bcp out the data and bcp in the data to a new database. Lets call this new database as “<em><strong>healthy_db</strong></em>”</p> <p>Below are the scripts that I used for BCP out and BCP in the data. More info on bcp command can be found <a href="http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.utility/html/utility/utility195.htm">here</a>.</p> <p>Below parameters are quiet useful for BCPing the data into the database:</p> <table border="0" cellspacing="0" cellpadding="2" width="630"><tbody> <tr> <td valign="top" width="76">Parameter</td> <td valign="top" width="552">Explanation</td> </tr> <tr> <td valign="top" width="76">-n</td> <td valign="top" width="552">performs the copy operation using native (operating system) formats. Specifying the <b>-n</b> parameter means <b><abbr>bcp</abbr></b> will not prompt for each field. Files in native data format are not human-readable.</td> </tr> <tr> <td valign="top" width="76">-Y </td> <td valign="top" width="552">specifies that character-set conversion is disabled in the server, and is instead performed by <b><abbr>bcp</abbr></b> on the client side when using <b><abbr>bcp</abbr> IN</b>.</td> </tr> <tr> <td valign="top" width="76">-E </td> <td valign="top" width="552"><b><abbr>bcp</abbr></b> reads the Identity value from the data file and sends it to the server which inserts the value into the table.</td> </tr> <tr> <td valign="top" width="76">-T </td> <td valign="top" width="552">allows you to specify, in bytes, the maximum length of <i>text</i> or <i>image</i> data that Adaptive Server sends. The default is 32<acronym>K</acronym>. If a <i>text</i> or an <i>image</i> field is larger than the value of <b>-T</b> or the default, <b><abbr>bcp</abbr></b> does not send the overflow. <br /> <br />You can find this value by doing: <strong>SELECT @@TEXTSIZE</strong></td> </tr> <tr> <td valign="top" width="76">-e</td> <td valign="top" width="552">This is the error log file created by bcp and it will log only the first 10 rows that caused the errors as the default for bcp is 10 errors and after that bcp will fail.</td> </tr> </tbody></table> <p>Note that ASE was running 1503 on a Solaris platform.</p> <p><font color="#0000ff">-- BCP OUT SCRIPT</font></p> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:9ce6104f-a9aa-4a17-a79f-3a39532ebf7c:583c63d0-80c8-4314-8d57-f5d5181d072a" class="wlWriterEditableSmartContent"> <div style="border: #000080 1px solid; color: #000; font-family: 'Courier New', Courier, Monospace; font-size: 10pt"> <div style="background-color: #ffffff; overflow: auto; padding: 2px 5px; white-space: nowrap"><br> <span style="color:#0000ff">select</span> <span style="color:#ff0000">'bcp problem_db.dbo.'</span> <span style="color:#808080">+</span> name <span style="color:#808080">+</span> <span style="color:#ff0000">' out /bcp_out_files/'</span> <span style="color:#808080">+</span> name <span style="color:#808080">+</span> <span style="color:#ff0000">'.out -SSERVER_NAME -Usa -PSTRONG_PASSWORD -n'</span><br> <span style="color:#0000ff">from</span> <span style="color:#008000">sysobjects</span> <br> <span style="color:#0000ff">where</span> <span style="color:#0000ff">type</span><span style="color:#808080">=</span><span style="color:#ff0000">'U'</span><br> <span style="color:#0000ff">order</span> <span style="color:#0000ff">by</span> name</div> </div> </div> <style type="text/css"><br /><br /><br /><br /><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><style type="text/css"><br /><br /><br /><br /><br /><br /><br /><br /><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> <p>Now for the bcp in script, there are two different scripts used for Tables having Identity columns and the ones which did not have Identity columns:</p> <p>-- BCP IN SCRIPT</p> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:9ce6104f-a9aa-4a17-a79f-3a39532ebf7c:efe4fb4b-67f8-4a2d-9252-46e4f01bf2ec" class="wlWriterEditableSmartContent"> <div style="border: #000080 1px solid; color: #000; font-family: 'Courier New', Courier, Monospace; font-size: 10pt"> <div style="background-color: #ffffff; overflow: auto; padding: 2px 5px; white-space: nowrap"><span style="color:#008000">-- (this will be for tables not having identity columns)</span><br> <span style="color:#0000ff">select</span> <span style="color:#ff0000">'LANG=C'</span> <span style="color:#808080">+</span> <span style="color:#0000ff">char</span><span style="color:#808080">(</span>10<span style="color:#808080">)+</span><span style="color:#ff0000">'bcp healthy_db.dbo.'</span> <span style="color:#808080">+</span> so<span style="color:#808080">.</span>name <span style="color:#808080">+</span> <span style="color:#ff0000">' in /bcp_out_files/'</span> <span style="color:#808080">+</span> so<span style="color:#808080">.</span>name <span style="color:#808080">+</span> <span style="color:#ff0000">'.out -SSERVER_NAME -Usa -PSTRONG_PASSWORD -n -Y -T 64512 -e /bcp/bcp_error.out'</span> <span style="color:#808080">+</span> <span style="color:#0000ff">char</span><span style="color:#808080">(</span>10<span style="color:#808080">)</span> <span style="color:#808080">+</span><br> <span style="color:#ff0000">'echo '</span><span style="color:#808080">+</span> so<span style="color:#808080">.</span>name <span style="color:#808080">+</span><span style="color:#ff0000">' done ...'</span> <span style="color:#0000ff">from</span> <span style="color:#008000">sysobjects</span> so <span style="color:#0000ff">where</span> so<span style="color:#808080">.</span>name <span style="color:#808080">not</span> <span style="color:#808080">in</span><span style="color:#0000ff"> </span><span style="color:#808080">(</span><br> <span style="color:#0000ff">select</span> so<span style="color:#808080">.</span>name<br> <span style="color:#0000ff">from</span> <span style="color:#008000">sysobjects</span> so<span style="color:#808080">,</span> <span style="color:#008000">syscolumns</span> sc<br> <span style="color:#0000ff">where</span> so<span style="color:#808080">.</span>id <span style="color:#808080">=</span> sc<span style="color:#808080">.</span>id<br> <span style="color:#808080">and</span> sc<span style="color:#808080">.</span><span style="color:#0000ff">status</span> <span style="color:#808080">&</span> 0x80 <span style="color:#808080">=</span> 0x80<span style="color:#808080">)</span><br> <span style="color:#808080">and</span> so<span style="color:#808080">.</span><span style="color:#0000ff">type</span> <span style="color:#808080">=</span> <span style="color:#ff0000">'U'</span><br> <span style="color:#0000ff">order</span> <span style="color:#0000ff">by</span> so<span style="color:#808080">.</span>name<br> <br> <span style="color:#008000">-- (this will be for tables having identity columns, E parameter is used for bcp)</span><br> <span style="color:#0000ff">select</span> <span style="color:#ff0000">'LANG=C'</span> <span style="color:#808080">+</span> <span style="color:#0000ff">char</span><span style="color:#808080">(</span>10<span style="color:#808080">)+</span><span style="color:#ff0000">'bcp healthy_db.dbo.'</span> <span style="color:#808080">+</span> so<span style="color:#808080">.</span>name <span style="color:#808080">+</span> <span style="color:#ff0000">' in /bcp_out_files/'</span> <span style="color:#808080">+</span> so<span style="color:#808080">.</span>name <span style="color:#808080">+</span> <span style="color:#ff0000">'.out -SSERVER_NAME -Usa -PSTRONG_PASSWORD -n -Y -E -T 64512 -e /bcp/bcp_error.out'</span> <span style="color:#808080">+</span> <span style="color:#0000ff">char</span><span style="color:#808080">(</span>10<span style="color:#808080">)</span> <span style="color:#808080">+</span><br> <span style="color:#ff0000">'echo '</span><span style="color:#808080">+</span> so<span style="color:#808080">.</span>name <span style="color:#808080">+</span><span style="color:#ff0000">' done ...'</span><br> <span style="color:#0000ff">from</span> <span style="color:#008000">sysobjects</span> so<span style="color:#808080">,</span> <span style="color:#008000">syscolumns</span> sc<br> <span style="color:#0000ff">where</span> so<span style="color:#808080">.</span>id <span style="color:#808080">=</span> sc<span style="color:#808080">.</span>id<br> <span style="color:#808080">and</span> sc<span style="color:#808080">.</span><span style="color:#0000ff">status</span> <span style="color:#808080">&</span> 0x80 <span style="color:#808080">=</span> 0x80<br> <span style="color:#808080">and</span> so<span style="color:#808080">.</span><span style="color:#0000ff">type</span> <span style="color:#808080">=</span> <span style="color:#ff0000">'U'</span><br> <span style="color:#0000ff">order</span> <span style="color:#0000ff">by</span> so<span style="color:#808080">.</span>name</div> </div> </div> <br /> <p>The above scripts will generate code for bcp out and in to run using isql (command prompt) on Solaris server.</p> <p>Also, the schema and other dependent objects were created before hand.<style type="text/css"><br /><br /><br /><br /><br /><br /><br /><br /><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><style type="text/css"><br /><br /><br /><br /><br /><br /><br /><br /><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></p> <p>HTH,</p> <p><a href="file://\\K">\\K</a></p> SQL_Questhttp://www.blogger.com/profile/14909213275379800133noreply@blogger.com0tag:blogger.com,1999:blog-107222033192989189.post-43406312518819442262012-01-21T20:47:00.000-05:002012-01-21T21:24:08.606-05:00Back to writing blog …<p>I have been very busy with job duties as now learning Sybase ASE and taking on new responsibilities as SQL server and Sybase DBA.</p> <p>I was not able to allocate time to keep my blog alive for almost a year.</p> <p>This is my first post in 2012. And want to keep this as habit of regularly updating my blog with both MS SQL Server and Sybase.</p> <p>Hope you will enjoy reading new cool stuff on this blog.</p> <p>Many thanks,</p> <p>SQL_Quest</p> SQL_Questhttp://www.blogger.com/profile/14909213275379800133noreply@blogger.com0tag:blogger.com,1999:blog-107222033192989189.post-85004691669792705402010-03-13T20:09:00.001-05:002010-03-13T20:09:10.993-05:00Strange SQL Agent job failure.<div class="wlWriterHeaderFooter" style="float:right; margin:0px; padding:0px 0px 4px 8px;"><script type="text/javascript">digg_url = "http://sqlquest.blogspot.com/2010/03/strange-sql-agent-job-failure.html";digg_title = "Strange SQL Agent job failure.";digg_bgcolor = "#FFFFFF";digg_skin = "normal";</script><script src="http://digg.com/tools/diggthis.js" type="text/javascript"></script><script type="text/javascript">digg_url = undefined;digg_title = undefined;digg_bgcolor = undefined;digg_skin = undefined;</script></div><p><strong><u>Scenario:</u></strong>   Recently, I came across a situation on our production server. It was a SQL agent job failure with below error message:</p> <p><em><strong>Could not obtain information about Windows NT group/user 'kks', error code 0xffff0002.". Possible failure reasons: Problems with the query, "ResultSet"</strong></em></p> <p><em><strong>property not set correctly, parameters not set correctly, or connection not established correctly.  E...  The package execution fa...  The step failed.</strong></em></p> <p>The interesting thing was that the job runs every 10 mins and it fails occasionally only e.g every 1 or 2 day or so.</p> <p><strong><u>Approach:</u></strong>   I started troubleshooting the error message by first looking into the permissions for the user account that the job is using for execution. The permissions were OK for the account.</p> <p>I looked into the error logs and found that the failure was coinciding with the DB mail failure. So I looked into the code that the job was executing. The job was executing a SP that was calling internally another SP named sp_send_dbmail. And Voila … found the offending part !</p> <p>I checked the DB mail configuration and noticed that the user ‘kks’ didn’t have any default mail profile associated with it. </p> <p>So when it tries to execute the SP, it fails as the user is not associated with any default profile that it can use to send email. So I assigned the user ‘kks’ a profile with proper distribution list and made that profile default for it. </p> <p>This way it was fixed and never failed again.</p> <p>“sometimes little things become unnoticed and cause a lot of pain” :-)</p> <p>HTH,</p> <p><a href="file://\\K">\\K</a></p> SQL_Questhttp://www.blogger.com/profile/14909213275379800133noreply@blogger.com0tag:blogger.com,1999:blog-107222033192989189.post-23537997236320157682010-02-25T17:01:00.001-05:002010-02-25T17:01:42.726-05:00How to enable the trace flags when the server restarts ?<div class="wlWriterHeaderFooter" style="float:right; margin:0px; padding:0px 0px 4px 8px;"><script type="text/javascript">digg_url = "http://sqlquest.blogspot.com/2010/02/how-to-enable-trace-flags-when-server.html";digg_title = "How to enable the trace flags when the server restarts ?";digg_bgcolor = "#FFFFFF";digg_skin = "normal";</script><script src="http://digg.com/tools/diggthis.js" type="text/javascript"></script><script type="text/javascript">digg_url = undefined;digg_title = undefined;digg_bgcolor = undefined;digg_skin = undefined;</script></div><p><strong><u>Scenario:</u></strong>  Recently, I was asked by my team lead to enable the trace flags esp. 1204 and 1205 which are deadlock trace flags and they should be in effect after the server restarts.</p> <p><strong><u>Approach:</u></strong>  The idea as seems much simple and straight forward. My first approach was to do </p> <blockquote> <p><strong>DBCC TRACEON (1204, 1205, -1)</strong>  -- here –1 is to enable the trace flags globally </p> <p>                                                 --i.e. not just at the session level</p> </blockquote> <p>But this will be in effect till the SQL server service is running and once it is restarted the trace flags are no longer in effect.</p> <p>To overcome above problem, the trace flags can be enabled when the SQL server service starts. This can be done using SQL server Configuration Manager as show below: </p> <p><strong>SQL server Configuration Manager -> SQL server 2005 services -> SQL server –> Properties -> Advanced -> startup parameters -> ;-T1204;-T1205</strong></p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhr3bnkqkEnLqm4X9hTYIDP0O98ELVFzHVA9cAlnDljgvdr9xtD3_vDntlXemZWp18st9oUaDiuh7s4xTwZjF2WtoDvo10n702q9hayUmdBQcxabf1lQwEcZCx7ReUGNskji0_pm07f4iQ/s1600-h/clip_image002%5B24%5D.jpg"><img title="clip_image002" style="border-top-width: 0px; display: inline; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" height="644" alt="clip_image002" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjodb-Ig2slZ-yTKnE9NUOpV961Ex45_Cgqqs7sf2WkblwuNFuzxKlioq9XlLNyvgslVNFryfvL_HYRTw21wwfPD6BEitETaY5qHOkAN-80c4NQUWZ3vu7S0zaTUoMYHPumL-dezJ1a0Zs/?imgmax=800" width="1028" border="0" /></a></p> <p>The only downside is that we need to <b>restart the service</b> for the changes to take affect !</p> <p>A quick check if the changes are in affect is to run in SSMS Query window : </p> <p><strong>DBCC TRACESTATUS</strong></p> <p>The result should be: </p> <p>TraceFlag Status Global Session</p> <p>--------- ------ ------ -------</p> <p>1204          1       1        0</p> <p>1205          1       1        0</p> <p>(2 row(s) affected)</p> <p>DBCC execution completed. If DBCC printed error messages, contact your system administrator.</p> <p>HTH,</p> <p><a href="file://\\K">\\K</a></p> SQL_Questhttp://www.blogger.com/profile/14909213275379800133noreply@blogger.com0tag:blogger.com,1999:blog-107222033192989189.post-80152544739037778502010-02-20T23:38:00.001-05:002010-02-20T23:38:22.205-05:00How to check whether “SQL Agent” is running or not ?<div class="wlWriterHeaderFooter" style="float:right; margin:0px; padding:0px 0px 4px 8px;"><script type="text/javascript">digg_url = "http://sqlquest.blogspot.com/2010/02/how-to-check-whether-sql-agent-is.html";digg_title = "How to check whether “SQL Agent” is running or not ?";digg_bgcolor = "#FFFFFF";digg_skin = "normal";</script><script src="http://digg.com/tools/diggthis.js" type="text/javascript"></script><script type="text/javascript">digg_url = undefined;digg_title = undefined;digg_bgcolor = undefined;digg_skin = undefined;</script></div><p><strong><u>Scenario:</u></strong>  Recently, I was asked by a friend to find the status of SQL server agent job i.e. either it is in running or stopped state and if it is in a stopped state then email it to concerned DBA team.</p> <p><strong><u>Approach:</u></strong> Below is the script that I used to check the status of SQL agent:</p> <p>/* schedule below script as a T-SQL agent job to run every 2 or 5 min depending on your environment. */</p> <p>IF EXISTS (  SELECT 1 <br />           FROM MASTER.dbo.sysprocesses <br />           WHERE program_name = N'SQLAgent - Generic Refresher') <br />BEGIN <br />   SELECT @@SERVERNAME AS 'InstanceName', 1 AS 'SQLServerAgentRunning' <br />END <br />ELSE <br />BEGIN <br />EXEC msdb.dbo.sp_send_dbmail <br />    @profile_name = 'kinjal.dba test profile', <br />    @recipients = 'kinjal.dba@gmail.com', <br />    @body = 'Please check the status of SQL Agent. It is not running !', <br />    @query = 'SELECT @@SERVERNAME AS [InstanceName], 0 AS [SQLServerAgentRunning]', <br />    @subject = 'SQL Agent is not running', <br />    @attach_query_result_as_file = 0 ; -- set it to 1 to receive as txt file attachment <br />END</p> <p><strong>Enhancing the above code to a further step to</strong> <strong>know the uptime for our SQL server and check whether sql server and sql agent both are running or not.</strong></p> <p>USE master; </p> <p>SET NOCOUNT ON </p> <p>DECLARE @crdate DATETIME, @hr VARCHAR(50), @min VARCHAR(5) </p> <p>SELECT @crdate=crdate FROM sysdatabases WHERE NAME='tempdb' </p> <p>SELECT @hr=(DATEDIFF ( mi, @crdate,GETDATE()))/60 </p> <p>IF ((DATEDIFF ( mi, @crdate,GETDATE()))/60)=0 </p> <p>SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE())) </p> <p>ELSE </p> <p>SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))-((DATEDIFF( mi, @crdate,GETDATE()))/60)*60 </p> <p>PRINT 'SQL Server "' + CONVERT(VARCHAR(20),SERVERPROPERTY('SERVERNAME'))+'" is Online for the past '+@hr+' hours & '+@min+' minutes' </p> <p>IF NOT EXISTS (SELECT 1 FROM master.dbo.sysprocesses WHERE program_name = N'SQLAgent - Generic Refresher') </p> <p>BEGIN </p> <p>PRINT 'Attention DBA Team ! SQL Server is running but SQL Server Agent is NOT running !!!!' </p> <p>END </p> <p>ELSE BEGIN </p> <p>PRINT 'OK ! SQL Server and SQL Server Agent both are running' </p> <p>END</p> <p> </p> <p>HTH,</p> <p><a href="file://\\K">\\K</a></p> SQL_Questhttp://www.blogger.com/profile/14909213275379800133noreply@blogger.com0tag:blogger.com,1999:blog-107222033192989189.post-87344183657294030792010-02-14T19:49:00.001-05:002010-02-14T19:49:15.357-05:00Some free training for enhancing your career…<div class="wlWriterHeaderFooter" style="float:right; margin:0px; padding:0px 0px 4px 8px;"><script type="text/javascript">digg_url = "http://sqlquest.blogspot.com/2010/02/some-free-training-for-enhancing-your.html";digg_title = "Some free training for enhancing your career…";digg_bgcolor = "#FFFFFF";digg_skin = "normal";</script><script src="http://digg.com/tools/diggthis.js" type="text/javascript"></script><script type="text/javascript">digg_url = undefined;digg_title = undefined;digg_bgcolor = undefined;digg_skin = undefined;</script></div><p>Recently, I was reading a weekly email from <strong>sqlservercentral.com</strong> which is named <strong>Database Weekly</strong> and it is a complete round-up sql server news for the whole week.</p> <p>What caught my eyes the free training given for DBA’s and BI Developers by Pragmatic works authored by MVP’s like Brian Knight, Jorge Segarra, Steve Jones, Brad McGehee, and more. Without discussing more below is the link for the registration:</p> <p><a title="http://denglishbi.spaces.live.com/Blog/cns!CD3E77E793DF6178!2526.entry" href="http://denglishbi.spaces.live.com/Blog/cns!CD3E77E793DF6178!2526.entry">http://denglishbi.spaces.live.com/Blog/cns!CD3E77E793DF6178!2526.entry</a></p> <p><a title="http://denglishbi.spaces.live.com/?_c11_BlogPart_BlogPart=blogview&_c=BlogPart&partqs=amonth%3d2%26ayear%3d2010" href="http://denglishbi.spaces.live.com/?_c11_BlogPart_BlogPart=blogview&_c=BlogPart&partqs=amonth%3d2%26ayear%3d2010">http://denglishbi.spaces.live.com/?_c11_BlogPart_BlogPart=blogview&_c=BlogPart&partqs=amonth%3d2%26ayear%3d2010</a></p> <p>Also, below is the link that points to Microsoft for some free training: </p> <p><a title="http://learning.microsoft.com/Manager/Catalog.aspx?view=free" href="http://learning.microsoft.com/Manager/Catalog.aspx?view=free">http://learning.microsoft.com/Manager/Catalog.aspx?view=free</a></p> <p>HTH,</p> <p>\\K</p> SQL_Questhttp://www.blogger.com/profile/14909213275379800133noreply@blogger.com0tag:blogger.com,1999:blog-107222033192989189.post-16125171489619602012010-02-06T16:30:00.001-05:002010-02-06T16:30:29.631-05:00Powershell – How to rename and move files ?<div class="wlWriterHeaderFooter" style="float:right; margin:0px; padding:0px 0px 4px 8px;"><script type="text/javascript">digg_url = "http://sqlquest.blogspot.com/2010/02/powershell-how-to-rename-and-move-files.html";digg_title = "Powershell – How to rename and move files ?";digg_bgcolor = "#FFFFFF";digg_skin = "normal";</script><script src="http://digg.com/tools/diggthis.js" type="text/javascript"></script><script type="text/javascript">digg_url = undefined;digg_title = undefined;digg_bgcolor = undefined;digg_skin = undefined;</script></div><blockquote> <p><strong><u>Scenario:</u></strong>  Recently, I came across a situation where in we had a a folder called Job_logs which is used for an output for SQL server agent job logs. All the jobs that are scheduled by SQL agent like SSIS packages, TSQL scripts, etc have their output files written to this Job_logs folder (this is done for better logging).</p> <p>Now the situation is that when the output file for SQL agent is set to append the log file, the output file which is a .txt file gets bigger in size in long run.</p> <p>Now when the output file is big lets say more than 5000KB, it downgrades the performance as the output file has to be opened up and then written !</p> <p><strong><u>Approach: </u></strong>To overcome above situation, I thought of writing a handy script that will do 2 tasks:</p> <ol> <li>Rename the file that is above 5000KB. </li> <li>Move the renamed file to another folder called Archive. </li> </ol> </blockquote> <p>  As we are on Windows Server 2008, the first thing came to my mind is using Powershell. It make life easier !</p> <p>Now the question that come to mind is: “Is 5000 a magical number?” The answer is NO. Our servers are very heavy in terms of CPU (8 cores) and RAM (64GB). So after doing some testing, I came to the conclusion of going with 5000KB files.</p> <p>Below is the Powershell script that I wrote to achieve what I wanted:</p> <blockquote> <p><font face="Times New Roman"><strong>## set the file location where the job log files are <br />$file = "F:\temp\Job_logs" <br />## this will get the current date and format it and store in the variable <br />$ext = get-Date -format MMddyyyyhhmmss <br />## Loop through all the .txt files in the job_logs Folder <br />foreach ($file in gci $file -include *.txt -recurse) <br />{    ## using the length property of the file <br />     if ($file.Length -gt 5000KB )  </strong></font></p> <p><font face="Times New Roman"><strong>   { ## will rename the file as file_datetime.txt <br />    rename-item -path $file -newname ($file.Name + "_$ext.txt")    <br />       } <br />}; </strong></font></p> <p><font face="Times New Roman"><strong>$file = "F:\temp\Job_logs" <br />$archive = "F:\temp\archive\" <br />foreach ($file in gci $file -include *.txt -recurse) <br />{    <br />     if ($file.Length -gt 5000KB ) <br />   { ## Move the files to the archive folder <br />    Move-Item -path $file.FullName -destination $archive  </strong></font></p> <p><font face="Times New Roman"><strong>  } <br />}</strong></font></p></blockquote> SQL_Questhttp://www.blogger.com/profile/14909213275379800133noreply@blogger.com0tag:blogger.com,1999:blog-107222033192989189.post-72760046464952850222010-01-25T16:14:00.000-05:002010-01-25T16:19:14.392-05:00How to test whether your SQL server is available or not without installing anything?<div class="wlWriterHeaderFooter" style="float:right; margin:0px; padding:0px 0px 4px 8px;"><script type="text/javascript">digg_url = "http://sqlquest.blogspot.com/2010/01/how-to-test-whether-your-sql-server-is.html";digg_title = "How to test whether your SQL server is available or not without installing anything?";digg_bgcolor = "#FFFFFF";digg_skin = "normal";</script><script src="http://digg.com/tools/diggthis.js" type="text/javascript"></script><script type="text/javascript">digg_url = undefined;digg_title = undefined;digg_bgcolor = undefined;digg_skin = undefined;</script></div><div class="wlWriterSmartContent" id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:39907c21-b932-4c18-bcec-9407e78a4bf1" style="padding-right: 0px; display: inline; padding-left: 0px; float: none; padding-bottom: 0px; margin: 0px; padding-top: 0px">Technorati Tags: <a href="http://technorati.com/tags/Data+Connection" rel="tag">Data Connection</a></div> <p><strong><u>Scenario:</u></strong> Recently, I was asked what if I want to test connectivity to my SQL server from a web server or from my local desktop machine ?</p> <p><strong><u>Approach:</u></strong> Well, this seems a much obvious question that can be answered on the spur as use PING <server IP> from the command prompt.</p> <p>But PING just does the basic test and tells that whether your server is available or not. What if you want to see that your databases that your SQL server is hosting are available or not ?</p> <p>There is a more smatter and easy way of doing this. The idea is to use UDL (Universal Data Link) files (i.e  .udl files). </p> <p><strong><u>Overview of UDL files:</u></strong> </p> <p>UDL files are an easy, efficient and fast way to test connection to a server from your work station or from web server eliminating the need to install any application (e.g dtcping) or SSMS (SQL Server Management Studio). Also, its a good way to quickly test that the credentials you specified will connect to a server and to build a connection string.</p> <p><strong><u>How do I do this ?</u></strong></p> <ol> <li>Create an empty .txt file. Name it for example MyConnection.txt . </li> <li>Now rename the extension and save it as MyConnection.udl . </li> <li>Below is the image of the MyConnection.udl file. </li> </ol> <a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgyN83RJT6QakCyFc5VOQQ0DEdLXEeYGQKcippXoXAqxGbnLjHN77TqC0E-usupoxxhB1wYS4_roqc3tE9SumFXQ_j_oM5px2i3EEEh5SBsraxzP_2w4oUuhLTklyKbk78zWonN5zToHtc/s1600-h/image%5B2%5D.png"><img title="image" style="border-top-width: 0px; display: inline; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" height="62" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhPO2sXsdOpu2ARPOEkM7Z221LODO3THTXgZsLDfzwtWn1zz7GdLL4tTNTZimBuMVTdRBy68TL41Khe7Ax1TF9lhmfdLrhZ8Qqhtp1YVD_kfQCsd62azaFAxosdKSqiwXpRdUueR8JgHak/?imgmax=800" width="167" border="0" /></a> <p></p> <blockquote> <p>4. Now double click the MyConnection.udl file to see the below screen.</p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiK5Z16hK6KVOFUYMNCPbUoH-V2axGTAg7KfPeEZfuQh7fREtqKwY9TIBAhKxbH8qnQewzik_3JCfjykJRXFNvosV9mjhDQyPmuFE9yuso0ZbKFv1Kqwx2k0zLJho9ZJB9u8FzMCVX9spA/s1600-h/image%5B8%5D.png"><img title="image" style="border-top-width: 0px; display: inline; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" height="298" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjeSqvkVBEJYPjAyaqOF7P5nGpDigxs_Mp2c9ugL5SLX_sNly9vE_g0EozJp4h21JTbhhdiMvJto1eIoyOf7eD-qslKGMJB9768c9FTDN_SOMXSWYpXMYvFZH3ar2UBdW6b3C-nBgSUUE8/?imgmax=800" width="350" border="0" /></a> </p> <p>5. Click the Provider tab and select SQL Native Client and then click Next.</p> <p>6. Enter the data source: localhost or IP address of the server. Here I have used Windows authentication, but choosing the second option allows you to use SQL server authentication wherein you have to provide username and password. (Note: Here there is an option to save the password as blank or Allow saving password option and choosing this option will save the password as clear text on the disk which is a security risk !). Next you can choose to connect to the specific database by clicking the drop box and selecting the database name. Click test connection button to verify that the connection is successful. Below is what you will see:          </p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEim8TyRxkRt1aFGImtCGuBjzUtFikdIbdkEMf_-9DBcsZSkHC0bZaCozmJZzP-ZYOKj9NyiXoDQ5G_chqD9749TTz45uwfWeu2fCUADwCJNPrAVq5tiUlO3n3g-fiQs-CYqRolJLR5oXw4/s1600-h/image%5B14%5D.png"><img title="image" style="border-top-width: 0px; display: inline; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" height="344" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjRvrfFh97MS1lceAxyjwbw29xxMe1G9xRZzeTb_MzHB_-IHwSRk3WObNbgvpoDn6udJcKPGVAeGtQHICOitw__vWVwXGzfslIqVjiwEZlAlIJt2OyrP8hHEbjk70qZqBhgr745eaxfSDY/?imgmax=800" width="412" border="0" /></a> </p> <p>7. Press OK when you are done. </p> <p>8. Now we can examine the contents of our MyConnection.udl file by renaming it back to MyConnection.txt </p> <p>9. Below are the contents of the file: </p> <p><strong>[oledb] <br />; Everything after this line is an OLE DB initstring <br />Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=AdventureWorks;Data Source=localhost</strong></p> </blockquote> <p></p> <blockquote> <p>10. You can use the above connection string in your application code directly !</p> </blockquote> <p>Hope this helps !</p> <p>Thanks,</p> <p><a href="file://\\K">\\K</a></p> SQL_Questhttp://www.blogger.com/profile/14909213275379800133noreply@blogger.com0tag:blogger.com,1999:blog-107222033192989189.post-46478464357469968112010-01-24T21:31:00.001-05:002010-01-24T21:31:19.665-05:00How to rename Distribution Job in an automated fashion ?<div class="wlWriterHeaderFooter" style="float:right; margin:0px; padding:0px 0px 4px 8px;"><script type="text/javascript">digg_url = "http://sqlquest.blogspot.com/2010/01/how-to-rename-distribution-job-in.html";digg_title = "How to rename Distribution Job in an automated fashion ?";digg_bgcolor = "#FFFFFF";digg_skin = "normal";</script><script src="http://digg.com/tools/diggthis.js" type="text/javascript"></script><script type="text/javascript">digg_url = undefined;digg_title = undefined;digg_bgcolor = undefined;digg_skin = undefined;</script></div><div class="wlWriterSmartContent" id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:c9e3ca1a-13c8-4062-b91b-e989e472deaf" style="padding-right: 0px; display: inline; padding-left: 0px; float: none; padding-bottom: 0px; margin: 0px; padding-top: 0px">Technorati Tags: <a href="http://technorati.com/tags/Replication" rel="tag">Replication</a></div> <p><strong><u>Situation:</u></strong> Recently I came across a situation wherein I have to rename the distribution jobs that are created when replication is recreated. The need to recreate replication is due to the fact that we do every 3 or 4th week staging refresh in our environment.</p> <p>Our environment is on Windows server 2008 (64bit) with SQL server 2005 enterprise edition SP3 with Transactional replication involved.</p> <p><strong><u>Approach: </u></strong>We have a automated process called a Master Job (I will blog this in my future posts) that does the staging refresh job. This job is named as per what it does, meaning it drops replication, does restores and recreates replication with just one click ! Isn’t that cool !</p> <p>But this job has some manual steps to rename the distribution jobs that are created when the replication is recreated. So to fully automate the job, I thought of automating some how to rename the distribution jobs.</p> <p>First I would try to explain how the jobs are created when replication is created or recreated.</p> <p>When transactional replication is created either using GUI (wizard) or recreated using scripts, the respective agent (snapshot, distribution and log reader) jobs are created with their default naming conventions. For example: </p> <p>Snapshot Agent: <br />[PublisherServerName]-PublisherDB-PublisherDB- [# of time replication script/wizard run on this server]</p> <p>Log reader Agent: <br />[PublisherServerName]-PublisherDB- [# of time replication script/wizard run on this server]</p> <p>Distribution Agent: <br />[publisherserverName]-publisherDB-SubsriberServerName [# of times subscribers created run on this server]</p> <p>[Ref: <a title="http://rdbmsexperts.com/Blogs/?p=40" href="http://rdbmsexperts.com/Blogs/?p=40">http://rdbmsexperts.com/Blogs/?p=40</a> . Thanks to Vinay for his help !]</p> <p>This shows that there is no control of creating our own job names (for better management and maintenance).</p> <p>There are two ways of renaming the distribution jobs:</p> <ol> <li>On the distribution server, double click the respective job names and then rename them. Uh! that’s a manual way of doing and it can be tricky or prone to mistakes and being a DBA, its not smart way to do things ! </li> <li>The second way is creating a custom script as below: </li> </ol> <p>In the distribution database, there is a system table called <strong>dbo.MSdistribution_agents</strong> that gives the info about the distribution agent with respect to sysjobs.</p> <p>Below is the script that I used to rename the distribution jobs ( we have multiple publisher – subscribers.) <br /></p> <p>declare @jobID1 uniqueidentifier  <br />select @jobID1=job_id from [dist_db_name].dbo.MSdistribution_agents a,master.sys.servers s <br />where a.publisher_db='[pub_db_name]'  and <br />a.subscriber_db='[subscriber_db_name]' and <br />a.subscriber_id=s.server_id and <br />s.name='subscriber_server_name' </p> <p>print @jobID1  -- this will print the jobID of the distribution job  <br /></p> <p>select job_id, name from msdb.dbo.sysjobs where job_id = @jobID1   -- this will give the jobID as well as job name of  the distribution job that we will be renaming</p> <p>–-rename the distribution agent job that is created to new job name </p> <p>exec msdb.dbo.sp_update_job @job_id = @jobID1, @new_name = ‘ New_meaning_ful_name’</p> <p><strong>To sum up above method:</strong> </p> <p><strong>-- Get the job ID for the distribution job created <br />declare @jobID1 uniqueidentifier <br /></strong></p> <strong>select @jobID1=job_id from [dist_db_name].dbo.MSdistribution_agents a,master.sys.servers s <br />where a.publisher_db='[pub_db_name]'  and <br />a.subscriber_db='[subscriber_db_name]' and <br />a.subscriber_id=s.server_id and <br />s.name='subscriber_server_name' </strong> <p><strong>–-rename the distribution agent job that is created to new job name </strong></p> <p><strong>exec msdb.dbo.sp_update_job @job_id = @jobID1, @new_name = ‘ New_meaning_ful_name’</strong></p> <p>In the same way we can rename the job for snapshot and logreader agent as well with MSlogreader_agents & MSsnapshot_agents tables respectively.</p> <p>Hope this helps !</p> <p>Thanks,</p> <p><a href="file://\\K">\\K</a></p> SQL_Questhttp://www.blogger.com/profile/14909213275379800133noreply@blogger.com0tag:blogger.com,1999:blog-107222033192989189.post-56285495538713796642010-01-17T18:10:00.003-05:002010-01-17T19:23:08.475-05:00Strange issue with database mail in clustered environment - not working tried everythingScenario: We have a 4 node clustered environment having windows server 2008 64bit with SQL server 2005 SP3 enterprise edition.<br /><br />Problem: The issue was that out of 4 servers in a clustered environment, on only one server the database mail was not working.<br /><br />Approach:<br /><br /><span style="font-weight:bold;">Step1:</span> Check that the Database mail is enabled using Surface Area Configuration. Then I rebuilt the Profiles and Accounts within the SQL Database Mail. I sent test messages after the rebuild and didn't see anything logged. I used the troubleshoot option on the test message send page and found some stored procedures which allow you to check to see if the queue is active and to start it. I both confirmed it was active, and sent it another start command, but still don't see email getting sent. I did see that the DB Mail start command was logged in the event logs, but nothing other than that.<br /><br /><span style="font-weight:bold;">Below is what was done: </span><br /><br />--The following query will show database mail is working correctly<br />SELECT * FROM msdb.dbo.sysmail_sentitems order by sent_date desc<br /><br />No results are seen after running the above command.<br /><br /><br />SELECT * FROM msdb.dbo.sysmail_allitems order by sent_date desc<br /><br />WHEN I RUN above COMMAND I SEE THAT ALL THE MESSAGES ARE IN AN "failed" send_status<br /><br />--The following query will show Database mail is started and running<br />SELECT * FROM msdb.dbo.sysmail_event_log order by log_date desc<br /><br />WHEN I RUN THIS COMMAND I CAN SEE DB MAIL IS STARTED, BUT I DON'T SEE ANY SUCCESSFULLY SENT MESSAGES LIKE I DO WHEN I RUN IT ON ANY OF THE OTHER DB SERVERS<br /><br />--If Database mail was having problems, we would see failed items in this view.<br />SELECT * FROM msdb.dbo.sysmail_unsentitems <br /><br />WHEN I RUN THIS COMMAND I SEE THAT ALL THE MESSAGES ARE IN AN "unsent" send_status<br /><br />Then the FIREWALL and SMTP relay were working fine or not were checked and they were all working fine !!<br /><br />Then I GOOGLED and found "http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/fd210efa-9c01-439e-8573-4f25a4177b08", but this did not work !!<br /><br />Then the decision was taken to open a ticket with MS... spent 2.5 hours on the phone with Microsoft trying to figure out what's going on with DB Mail.<br /><br />At this point they took some traces, other diagnostic data ... and yes they were able to figure the REAL problem ....<br /><br />When you select MSDB and run the following to show DB Mail config:<br /><br />------------------------------------------------------------------<br /><br />USE msdb<br /><br />select * from sysmail_configuration<br /><br />------------------------------------------------------------------<br /><br />...We can see that the following is set (which MS says would have been done manually as the default is to NOT use a config file):<br /><br />------------------------------------------------------------------<br />ReadFromConfigurationFile 1 Send mail from mail server in configuration file <br />------------------------------------------------------------------<br /><br />With this option enabled there MUST be a configuration file that tells the DB Mail exe what Server\Instance to send the mail to. This file existed (I don't know if it has existed from the beginning, who created it, etc...), but it was not in the correct location.<br /><br />The file was in:<br /><br />C:\Program Files\Microsoft SQL Server\90\Tools\Binn<br /><br />...and needed to be in (MSSQL.3 since registry shows [server_name] being the 3rd instance):<br /><br />C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Binn<br /><br />Once this file was moved into the proper location, we were able to define the correct server name:<br /><br />------------------------------------------------------------------<br />Contents of config file: "DatabaseMail90.exe.config"<br />------------------------------------------------------------------<br /><br />configuration <br /> appSettings <br /> add key="DatabaseServerName" value ="[server_name]" / <br /> add key="DatabaseName" value ="msdb" / <br /> /appSettings <br />/configuration <br /><br />------------------------------------------------------------------<br />Once this had been properly configured and the file put into the correct location we were able to successfully send mail<br /><br />Since this is a 4-node SQL Cluster we also had to copy the "DatabaseMail90.exe.config" file over to the other 3 nodes to:<br /><br />\\[server_name]\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Binn<br />\\[server_name]\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Binn<br />\\[server_name]\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Binn<br /><br />...so that if this instance fails over to any other node it will continue to function.<br /><br /><br />This is how the Strange database mail issue was fixed ... Ah.. it was with the config file .... and lastly got resolved !!!SQL_Questhttp://www.blogger.com/profile/14909213275379800133noreply@blogger.com0tag:blogger.com,1999:blog-107222033192989189.post-79225364863484311002009-12-09T11:53:00.002-05:002009-12-09T11:57:39.206-05:00Terminal serrver has exceeded max allowed connectionsWhen RDPing to remote server, this is a most common message displayed - " The terminal server has exceeded maximum allowed connections"<br /><br />To overcome this message, following should be done.<br /><br /><span style="font-weight:bold;">start -> run -> mstsc /v ipOfServer /admin</span> ===> for using RDP 6 client or above !<br /><br />here ipOfServer = ip address of the server you are trying to connect.<br /><br />instead you can use name of the server too.SQL_Questhttp://www.blogger.com/profile/14909213275379800133noreply@blogger.com0tag:blogger.com,1999:blog-107222033192989189.post-36030998259778020762009-11-15T20:40:00.002-05:002009-11-15T21:02:30.656-05:00Change password on remote server when using RDPRDP (remote desktop) key combinations:<br /><br /> * CTRL+ALT+END: Open the Microsoft Windows NT Security dialog box (CTRL+ALT+DEL) --- to change the password on the server <br /><br /> * ALT+PAGE UP: Switch between programs from left to right (CTRL+PAGE UP)<br /> * ALT+PAGE DOWN: Switch between programs from right to left (CTRL+PAGE DOWN)<br /> * ALT+INSERT: Cycle through the programs in most recently used order (ALT+TAB)<br /> * ALT+HOME: Display the Start menu (CTRL+ESC)<br /> * CTRL+ALT+BREAK: Switch the client computer between a window and a full screen<br /> * ALT+DELETE: Display the Windows menu<br /> * CTRL+ALT+Minus sign (-): Place a snapshot of the entire client window area on the Terminal server clipboard and provide the same functionality as pressing ALT+PRINT SCREEN on a local computer (ALT+PRT SC)<br /> * CTRL+ALT+Plus sign (+): Place a snapshot of the active window in the client on the Terminal server clipboard and provide the same functionality as pressing PRINT SCREEN on a local computer (PRT SC)SQL_Questhttp://www.blogger.com/profile/14909213275379800133noreply@blogger.com0tag:blogger.com,1999:blog-107222033192989189.post-20196038698461719852009-11-11T21:30:00.001-05:002009-11-11T21:32:29.511-05:00Moving databases between different servers when replication and full text catalog are defined !<meta equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 12"><meta name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CAnkur%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CDOCUME%7E1%5CAnkur%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CDOCUME%7E1%5CAnkur%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><!--[if gte mso 9]><xml> <w:worddocument> <w:view>Normal</w:View> <w:zoom>0</w:Zoom> <w:trackmoves/> <w:trackformatting/> <w:punctuationkerning/> <w:validateagainstschemas/> <w:saveifxmlinvalid>false</w:SaveIfXMLInvalid> <w:ignoremixedcontent>false</w:IgnoreMixedContent> <w:alwaysshowplaceholdertext>false</w:AlwaysShowPlaceholderText> <w:donotpromoteqf/> <w:lidthemeother>EN-US</w:LidThemeOther> <w:lidthemeasian>X-NONE</w:LidThemeAsian> <w:lidthemecomplexscript>X-NONE</w:LidThemeComplexScript> <w:compatibility> <w:breakwrappedtables/> <w:snaptogridincell/> <w:wraptextwithpunct/> <w:useasianbreakrules/> <w:dontgrowautofit/> <w:splitpgbreakandparamark/> <w:dontvertaligncellwithsp/> <w:dontbreakconstrainedforcedtables/> <w:dontvertalignintxbx/> <w:word11kerningpairs/> <w:cachedcolbalance/> </w:Compatibility> <w:browserlevel>MicrosoftInternetExplorer4</w:BrowserLevel> <m:mathpr> <m:mathfont val="Cambria Math"> <m:brkbin val="before"> <m:brkbinsub val="--"> <m:smallfrac val="off"> <m:dispdef/> <m:lmargin val="0"> <m:rmargin val="0"> <m:defjc val="centerGroup"> <m:wrapindent val="1440"> <m:intlim val="subSup"> <m:narylim val="undOvr"> </m:mathPr></w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:latentstyles deflockedstate="false" defunhidewhenused="true" defsemihidden="true" defqformat="false" defpriority="99" latentstylecount="267"> <w:lsdexception locked="false" priority="0" semihidden="false" unhidewhenused="false" qformat="true" name="Normal"> <w:lsdexception locked="false" priority="9" semihidden="false" unhidewhenused="false" qformat="true" name="heading 1"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 2"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 3"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 4"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 5"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 6"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 7"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 8"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 9"> <w:lsdexception locked="false" priority="39" name="toc 1"> <w:lsdexception locked="false" priority="39" name="toc 2"> <w:lsdexception locked="false" priority="39" name="toc 3"> <w:lsdexception locked="false" priority="39" name="toc 4"> <w:lsdexception locked="false" priority="39" name="toc 5"> <w:lsdexception locked="false" priority="39" name="toc 6"> <w:lsdexception locked="false" priority="39" name="toc 7"> <w:lsdexception locked="false" priority="39" name="toc 8"> <w:lsdexception locked="false" priority="39" name="toc 9"> <w:lsdexception locked="false" priority="0" name="footer"> <w:lsdexception locked="false" priority="35" qformat="true" name="caption"> <w:lsdexception locked="false" priority="0" name="page number"> <w:lsdexception locked="false" priority="10" semihidden="false" unhidewhenused="false" qformat="true" name="Title"> <w:lsdexception locked="false" priority="1" name="Default Paragraph Font"> <w:lsdexception locked="false" priority="0" name="Body Text"> <w:lsdexception locked="false" priority="11" semihidden="false" unhidewhenused="false" qformat="true" name="Subtitle"> <w:lsdexception locked="false" priority="22" semihidden="false" unhidewhenused="false" qformat="true" name="Strong"> <w:lsdexception locked="false" priority="20" semihidden="false" unhidewhenused="false" qformat="true" name="Emphasis"> <w:lsdexception locked="false" priority="59" semihidden="false" unhidewhenused="false" name="Table Grid"> <w:lsdexception locked="false" unhidewhenused="false" name="Placeholder Text"> <w:lsdexception locked="false" priority="1" semihidden="false" unhidewhenused="false" qformat="true" name="No Spacing"> <w:lsdexception locked="false" priority="60" semihidden="false" unhidewhenused="false" name="Light Shading"> <w:lsdexception locked="false" priority="61" semihidden="false" unhidewhenused="false" name="Light List"> <w:lsdexception locked="false" priority="62" semihidden="false" unhidewhenused="false" name="Light Grid"> <w:lsdexception locked="false" priority="63" semihidden="false" unhidewhenused="false" name="Medium Shading 1"> <w:lsdexception locked="false" priority="64" semihidden="false" unhidewhenused="false" name="Medium Shading 2"> <w:lsdexception locked="false" priority="65" semihidden="false" unhidewhenused="false" name="Medium List 1"> <w:lsdexception locked="false" priority="66" semihidden="false" unhidewhenused="false" name="Medium List 2"> <w:lsdexception locked="false" priority="67" semihidden="false" unhidewhenused="false" name="Medium Grid 1"> <w:lsdexception locked="false" priority="68" semihidden="false" unhidewhenused="false" name="Medium Grid 2"> <w:lsdexception locked="false" priority="69" semihidden="false" unhidewhenused="false" name="Medium Grid 3"> <w:lsdexception locked="false" priority="70" semihidden="false" unhidewhenused="false" name="Dark List"> <w:lsdexception locked="false" priority="71" semihidden="false" unhidewhenused="false" name="Colorful Shading"> <w:lsdexception locked="false" priority="72" semihidden="false" unhidewhenused="false" name="Colorful List"> <w:lsdexception locked="false" priority="73" semihidden="false" unhidewhenused="false" name="Colorful Grid"> <w:lsdexception locked="false" priority="60" semihidden="false" unhidewhenused="false" name="Light Shading Accent 1"> <w:lsdexception locked="false" priority="61" semihidden="false" unhidewhenused="false" name="Light List Accent 1"> <w:lsdexception locked="false" priority="62" semihidden="false" unhidewhenused="false" name="Light Grid Accent 1"> <w:lsdexception locked="false" priority="63" semihidden="false" unhidewhenused="false" name="Medium Shading 1 Accent 1"> <w:lsdexception locked="false" priority="64" semihidden="false" unhidewhenused="false" name="Medium Shading 2 Accent 1"> <w:lsdexception locked="false" priority="65" semihidden="false" unhidewhenused="false" name="Medium List 1 Accent 1"> <w:lsdexception locked="false" unhidewhenused="false" name="Revision"> <w:lsdexception locked="false" priority="34" semihidden="false" unhidewhenused="false" qformat="true" name="List Paragraph"> <w:lsdexception locked="false" priority="29" semihidden="false" unhidewhenused="false" qformat="true" name="Quote"> <w:lsdexception locked="false" priority="30" semihidden="false" unhidewhenused="false" qformat="true" name="Intense Quote"> <w:lsdexception locked="false" priority="66" semihidden="false" unhidewhenused="false" name="Medium List 2 Accent 1"> <w:lsdexception locked="false" priority="67" semihidden="false" unhidewhenused="false" name="Medium Grid 1 Accent 1"> <w:lsdexception locked="false" priority="68" semihidden="false" unhidewhenused="false" name="Medium Grid 2 Accent 1"> <w:lsdexception locked="false" priority="69" semihidden="false" unhidewhenused="false" name="Medium Grid 3 Accent 1"> <w:lsdexception locked="false" priority="70" semihidden="false" unhidewhenused="false" name="Dark List Accent 1"> <w:lsdexception locked="false" priority="71" semihidden="false" unhidewhenused="false" name="Colorful Shading Accent 1"> <w:lsdexception locked="false" priority="72" semihidden="false" unhidewhenused="false" name="Colorful List Accent 1"> <w:lsdexception locked="false" priority="73" semihidden="false" unhidewhenused="false" name="Colorful Grid Accent 1"> <w:lsdexception locked="false" priority="60" semihidden="false" unhidewhenused="false" name="Light Shading Accent 2"> <w:lsdexception locked="false" priority="61" semihidden="false" unhidewhenused="false" name="Light List Accent 2"> <w:lsdexception locked="false" priority="62" semihidden="false" unhidewhenused="false" name="Light Grid Accent 2"> <w:lsdexception locked="false" priority="63" semihidden="false" unhidewhenused="false" name="Medium Shading 1 Accent 2"> <w:lsdexception locked="false" priority="64" semihidden="false" unhidewhenused="false" name="Medium Shading 2 Accent 2"> <w:lsdexception locked="false" priority="65" semihidden="false" unhidewhenused="false" name="Medium List 1 Accent 2"> <w:lsdexception locked="false" priority="66" semihidden="false" unhidewhenused="false" name="Medium List 2 Accent 2"> <w:lsdexception locked="false" priority="67" semihidden="false" unhidewhenused="false" name="Medium Grid 1 Accent 2"> <w:lsdexception locked="false" priority="68" semihidden="false" unhidewhenused="false" name="Medium Grid 2 Accent 2"> <w:lsdexception locked="false" priority="69" semihidden="false" unhidewhenused="false" name="Medium Grid 3 Accent 2"> <w:lsdexception locked="false" priority="70" semihidden="false" unhidewhenused="false" name="Dark List Accent 2"> <w:lsdexception locked="false" priority="71" semihidden="false" unhidewhenused="false" name="Colorful Shading Accent 2"> <w:lsdexception locked="false" priority="72" semihidden="false" unhidewhenused="false" name="Colorful List Accent 2"> <w:lsdexception locked="false" priority="73" semihidden="false" unhidewhenused="false" name="Colorful Grid Accent 2"> <w:lsdexception locked="false" priority="60" semihidden="false" unhidewhenused="false" name="Light Shading Accent 3"> <w:lsdexception locked="false" priority="61" semihidden="false" unhidewhenused="false" name="Light List Accent 3"> <w:lsdexception locked="false" priority="62" semihidden="false" unhidewhenused="false" name="Light Grid Accent 3"> <w:lsdexception locked="false" priority="63" semihidden="false" unhidewhenused="false" name="Medium Shading 1 Accent 3"> <w:lsdexception locked="false" priority="64" semihidden="false" unhidewhenused="false" name="Medium Shading 2 Accent 3"> <w:lsdexception locked="false" priority="65" semihidden="false" unhidewhenused="false" name="Medium List 1 Accent 3"> <w:lsdexception locked="false" priority="66" semihidden="false" unhidewhenused="false" name="Medium List 2 Accent 3"> <w:lsdexception locked="false" priority="67" semihidden="false" unhidewhenused="false" name="Medium Grid 1 Accent 3"> <w:lsdexception locked="false" priority="68" semihidden="false" unhidewhenused="false" name="Medium Grid 2 Accent 3"> <w:lsdexception locked="false" priority="69" semihidden="false" unhidewhenused="false" name="Medium Grid 3 Accent 3"> <w:lsdexception locked="false" priority="70" semihidden="false" unhidewhenused="false" name="Dark List Accent 3"> <w:lsdexception locked="false" priority="71" semihidden="false" unhidewhenused="false" name="Colorful Shading Accent 3"> <w:lsdexception locked="false" priority="72" semihidden="false" unhidewhenused="false" name="Colorful List Accent 3"> <w:lsdexception locked="false" priority="73" semihidden="false" unhidewhenused="false" name="Colorful Grid Accent 3"> <w:lsdexception locked="false" priority="60" semihidden="false" unhidewhenused="false" name="Light Shading Accent 4"> <w:lsdexception locked="false" priority="61" semihidden="false" unhidewhenused="false" name="Light List Accent 4"> <w:lsdexception locked="false" priority="62" semihidden="false" unhidewhenused="false" name="Light Grid Accent 4"> <w:lsdexception locked="false" priority="63" semihidden="false" unhidewhenused="false" name="Medium Shading 1 Accent 4"> <w:lsdexception locked="false" priority="64" semihidden="false" unhidewhenused="false" name="Medium Shading 2 Accent 4"> <w:lsdexception locked="false" priority="65" semihidden="false" unhidewhenused="false" name="Medium List 1 Accent 4"> <w:lsdexception locked="false" priority="66" semihidden="false" unhidewhenused="false" name="Medium List 2 Accent 4"> <w:lsdexception locked="false" priority="67" semihidden="false" unhidewhenused="false" name="Medium Grid 1 Accent 4"> <w:lsdexception locked="false" priority="68" semihidden="false" unhidewhenused="false" name="Medium Grid 2 Accent 4"> <w:lsdexception locked="false" priority="69" semihidden="false" unhidewhenused="false" name="Medium Grid 3 Accent 4"> <w:lsdexception locked="false" priority="70" semihidden="false" unhidewhenused="false" name="Dark List Accent 4"> <w:lsdexception locked="false" priority="71" semihidden="false" unhidewhenused="false" name="Colorful Shading Accent 4"> <w:lsdexception locked="false" priority="72" semihidden="false" unhidewhenused="false" name="Colorful List Accent 4"> <w:lsdexception locked="false" priority="73" semihidden="false" unhidewhenused="false" name="Colorful Grid Accent 4"> <w:lsdexception locked="false" priority="60" semihidden="false" unhidewhenused="false" name="Light Shading Accent 5"> <w:lsdexception locked="false" priority="61" semihidden="false" unhidewhenused="false" name="Light List Accent 5"> <w:lsdexception locked="false" priority="62" semihidden="false" unhidewhenused="false" name="Light Grid Accent 5"> <w:lsdexception locked="false" priority="63" semihidden="false" unhidewhenused="false" name="Medium Shading 1 Accent 5"> <w:lsdexception locked="false" priority="64" semihidden="false" unhidewhenused="false" name="Medium Shading 2 Accent 5"> <w:lsdexception locked="false" priority="65" semihidden="false" unhidewhenused="false" name="Medium List 1 Accent 5"> <w:lsdexception locked="false" priority="66" semihidden="false" unhidewhenused="false" name="Medium List 2 Accent 5"> <w:lsdexception locked="false" priority="67" semihidden="false" unhidewhenused="false" name="Medium Grid 1 Accent 5"> <w:lsdexception locked="false" priority="68" semihidden="false" unhidewhenused="false" name="Medium Grid 2 Accent 5"> <w:lsdexception locked="false" priority="69" semihidden="false" unhidewhenused="false" name="Medium Grid 3 Accent 5"> <w:lsdexception locked="false" priority="70" semihidden="false" unhidewhenused="false" name="Dark List Accent 5"> <w:lsdexception locked="false" priority="71" semihidden="false" unhidewhenused="false" name="Colorful Shading Accent 5"> <w:lsdexception locked="false" priority="72" semihidden="false" unhidewhenused="false" name="Colorful List Accent 5"> <w:lsdexception locked="false" priority="73" semihidden="false" unhidewhenused="false" name="Colorful Grid Accent 5"> <w:lsdexception locked="false" priority="60" semihidden="false" unhidewhenused="false" name="Light Shading Accent 6"> <w:lsdexception locked="false" priority="61" semihidden="false" unhidewhenused="false" name="Light List Accent 6"> <w:lsdexception locked="false" priority="62" semihidden="false" unhidewhenused="false" name="Light Grid Accent 6"> <w:lsdexception locked="false" priority="63" semihidden="false" unhidewhenused="false" name="Medium Shading 1 Accent 6"> <w:lsdexception locked="false" priority="64" semihidden="false" unhidewhenused="false" name="Medium Shading 2 Accent 6"> <w:lsdexception locked="false" priority="65" semihidden="false" unhidewhenused="false" name="Medium List 1 Accent 6"> <w:lsdexception locked="false" priority="66" semihidden="false" unhidewhenused="false" name="Medium List 2 Accent 6"> <w:lsdexception locked="false" priority="67" semihidden="false" unhidewhenused="false" name="Medium Grid 1 Accent 6"> <w:lsdexception locked="false" priority="68" semihidden="false" unhidewhenused="false" name="Medium Grid 2 Accent 6"> <w:lsdexception locked="false" priority="69" semihidden="false" unhidewhenused="false" name="Medium Grid 3 Accent 6"> <w:lsdexception locked="false" priority="70" semihidden="false" unhidewhenused="false" name="Dark List Accent 6"> <w:lsdexception locked="false" priority="71" semihidden="false" unhidewhenused="false" name="Colorful Shading Accent 6"> <w:lsdexception locked="false" priority="72" semihidden="false" unhidewhenused="false" name="Colorful List Accent 6"> <w:lsdexception locked="false" priority="73" semihidden="false" unhidewhenused="false" name="Colorful Grid Accent 6"> <w:lsdexception locked="false" priority="19" semihidden="false" unhidewhenused="false" qformat="true" name="Subtle Emphasis"> <w:lsdexception locked="false" priority="21" semihidden="false" unhidewhenused="false" qformat="true" name="Intense Emphasis"> <w:lsdexception locked="false" priority="31" semihidden="false" unhidewhenused="false" qformat="true" name="Subtle Reference"> <w:lsdexception locked="false" priority="32" semihidden="false" unhidewhenused="false" qformat="true" name="Intense Reference"> <w:lsdexception locked="false" priority="33" semihidden="false" unhidewhenused="false" qformat="true" name="Book Title"> <w:lsdexception locked="false" priority="37" name="Bibliography"> <w:lsdexception locked="false" priority="39" qformat="true" name="TOC Heading"> </w:LatentStyles> </xml><![endif]--><style> <!-- /* Font Definitions */ @font-face {font-family:Wingdings; panose-1:5 0 0 0 0 0 0 0 0 0; mso-font-charset:2; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:0 268435456 0 0 -2147483648 0;} @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:0; mso-generic-font-family:roman; mso-font-pitch:variable; mso-font-signature:-1610611985 1107304683 0 0 159 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin-top:0in; margin-right:0in; margin-bottom:10.0pt; margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} a:link, span.MsoHyperlink {mso-style-priority:99; color:blue; mso-themecolor:hyperlink; text-decoration:underline; text-underline:single;} a:visited, span.MsoHyperlinkFollowed {mso-style-noshow:yes; mso-style-priority:99; color:purple; mso-themecolor:followedhyperlink; text-decoration:underline; text-underline:single;} pre {mso-style-noshow:yes; mso-style-priority:99; mso-style-link:"HTML Preformatted Char"; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Courier New"; mso-fareast-font-family:"Times New Roman";} p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph {mso-style-priority:34; mso-style-unhide:no; mso-style-qformat:yes; margin-top:0in; margin-right:0in; margin-bottom:10.0pt; margin-left:.5in; mso-add-space:auto; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} p.MsoListParagraphCxSpFirst, li.MsoListParagraphCxSpFirst, div.MsoListParagraphCxSpFirst {mso-style-priority:34; mso-style-unhide:no; mso-style-qformat:yes; mso-style-type:export-only; margin-top:0in; margin-right:0in; margin-bottom:0in; margin-left:.5in; margin-bottom:.0001pt; mso-add-space:auto; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} p.MsoListParagraphCxSpMiddle, li.MsoListParagraphCxSpMiddle, div.MsoListParagraphCxSpMiddle {mso-style-priority:34; mso-style-unhide:no; mso-style-qformat:yes; mso-style-type:export-only; margin-top:0in; margin-right:0in; margin-bottom:0in; margin-left:.5in; margin-bottom:.0001pt; mso-add-space:auto; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} p.MsoListParagraphCxSpLast, li.MsoListParagraphCxSpLast, div.MsoListParagraphCxSpLast {mso-style-priority:34; mso-style-unhide:no; mso-style-qformat:yes; mso-style-type:export-only; margin-top:0in; margin-right:0in; margin-bottom:10.0pt; margin-left:.5in; mso-add-space:auto; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} span.HTMLPreformattedChar {mso-style-name:"HTML Preformatted Char"; mso-style-noshow:yes; mso-style-priority:99; mso-style-unhide:no; mso-style-locked:yes; mso-style-link:"HTML Preformatted"; mso-ansi-font-size:10.0pt; mso-bidi-font-size:10.0pt; font-family:"Courier New"; mso-ascii-font-family:"Courier New"; mso-fareast-font-family:"Times New Roman"; mso-hansi-font-family:"Courier New"; mso-bidi-font-family:"Courier New";} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} .MsoPapDefault {mso-style-type:export-only; margin-bottom:10.0pt; line-height:115%;} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} /* List Definitions */ @list l0 {mso-list-id:73405993; mso-list-type:hybrid; mso-list-template-ids:938793630 67698703 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;} @list l0:level1 {mso-level-tab-stop:none; mso-level-number-position:left; text-indent:-.25in;} @list l1 {mso-list-id:432677569; mso-list-type:hybrid; mso-list-template-ids:-595164190 67698703 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;} @list l1:level1 {mso-level-tab-stop:none; mso-level-number-position:left; text-indent:-.25in;} ol {margin-bottom:0in;} ul {margin-bottom:0in;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin-top:0in; mso-para-margin-right:0in; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin;} </style> <![endif]--> <p class="MsoNormal">I have come across many articles on web that describes how to move databases from one server to another. But I didn’t find much information pertaining to my situation.</p> <p class="MsoNormal">Recently, we carried out a server consolidation project and I was required to move databases and cubes between different servers. Moving databases is fine and is pretty straight forward task (you can use Detach/Attach method). But when the databases you are moving are involved in replication or a Full text <span style=""> </span>(FT) catalog is defined on a database, there things become tricky ! </p> <p class="MsoNormal">In my situation, transactional replication was involved and the databases were having FT catalog defined. <span style=""> </span>So to move databases, <span style=""> </span>I followed below mentioned steps:</p> <p class="MsoListParagraph" style="text-indent: -0.25in;"><!--[if !supportLists]--><span style=""><span style="">1.<span style="font-family: "Times New Roman"; font-style: normal; font-variant: normal; font-weight: normal; font-size: 7pt; line-height: normal; font-size-adjust: none; font-stretch: normal;"> </span></span></span><!--[endif]-->Use the below query to find what files are involved with the database.</p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; text-indent: 0.5in; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;">USE [database_name]<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; text-indent: 0.5in; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;">go<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; text-indent: 0.5in; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;">select name as logical_name, physical_name, type_desc, state_desc<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; text-indent: 0.5in; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;">from sys.database_files<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; text-indent: 0.5in; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;"><o:p> </o:p></span></p> <p class="MsoNormal" style="margin-left: 0.5in;">This will give you the details of the files associated with a particular database. <span style=""> </span>(Note: here you can use <span style="font-size: 10pt; line-height: 115%; font-family: "Courier New"; color: blue;">SP_HELPDB ‘db_name’</span>; but this will not give you the location of FT file.)</p> <p class="MsoListParagraphCxSpFirst" style="text-indent: -0.25in;"><!--[if !supportLists]--><span style=""><span style="">2.<span style="font-family: "Times New Roman"; font-style: normal; font-variant: normal; font-weight: normal; font-size: 7pt; line-height: normal; font-size-adjust: none; font-stretch: normal;"> </span></span></span><!--[endif]-->Then script out the replication. (One of the cool feature of SQL server 2005 SSMS is the ability to script out things: rt click the publisher <span style="font-family: Wingdings;"><span style="">à</span></span> generate scripts <span style="font-family: Wingdings;"><span style="">à</span></span> select create scripts and give a proper location where the script file will be put).</p> <p class="MsoListParagraphCxSpMiddle" style="text-indent: -0.25in;"><!--[if !supportLists]--><span style=""><span style="">3.<span style="font-family: "Times New Roman"; font-style: normal; font-variant: normal; font-weight: normal; font-size: 7pt; line-height: normal; font-size-adjust: none; font-stretch: normal;"> </span></span></span><!--[endif]-->Also, to remove replication generate Drop scripts.</p> <p class="MsoListParagraphCxSpLast" style="text-indent: -0.25in;"><!--[if !supportLists]--><span style=""><span style="">4.<span style="font-family: "Times New Roman"; font-style: normal; font-variant: normal; font-weight: normal; font-size: 7pt; line-height: normal; font-size-adjust: none; font-stretch: normal;"> </span></span></span><!--[endif]-->Then you can use the Detach/Attach method of moving databases form one server to another.</p> <pre style="margin-left: 0.5in;">--Detach the database.<o:p></o:p></pre> <p class="MsoNormal" style="margin-bottom: 0.0001pt; text-indent: 0.5in; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;"></span>USE master<o:p></o:p></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; text-indent: 0.5in; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;">Go<o:p></o:p></span></p> <p class="MsoListParagraphCxSpFirst"><span style="font-size: 10pt; line-height: 115%; font-family: "Courier New"; color: blue;">Sp_detach_db [database_name]<o:p></o:p></span></p> <p class="MsoListParagraphCxSpMiddle"><span style="font-size: 10pt; line-height: 115%; font-family: "Courier New"; color: blue;">Go<o:p></o:p></span></p> <p class="MsoListParagraphCxSpMiddle"><span style="font-size: 10pt; line-height: 115%; font-family: "Courier New"; color: blue;"><o:p> </o:p></span></p> <p class="MsoListParagraphCxSpLast">At this point, Copy the mdf, ldf, ndf (if any) and the FT directory to the new server.</p> <p class="MsoNormal" style="margin: 0in 0in 0.0001pt 0.5in; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: green;">--Physically move the full-text catalog to the new location.<o:p></o:p></span></p> <p class="MsoNormal" style="margin: 0in 0in 0.0001pt 0.5in; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: green;">--Attach the database and specify the new location of the full-text catalog.<o:p></o:p></span></p> <p class="MsoNormal" style="margin: 0in 0in 0.0001pt 0.5in; line-height: normal;"><o:p> </o:p></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; text-indent: 0.5in; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;">CREATE DATABASE [database_name] ON <o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; text-indent: 0.5in; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;"><span style=""> </span>(FILENAME = 'S:\data\[database_name]_Data.mdf'), <o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; text-indent: 0.5in; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;"><span style=""> </span>(FILENAME = 'X:\Logs\[database_name]_log.ldf'),<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; text-indent: 0.5in; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;"><span style=""> </span>(FILENAME = 'S:\myFTCatalogs\AdvWksFtCat')<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; text-indent: 0.5in; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;">FOR ATTACH;<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; text-indent: 0.5in; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;">GO<o:p></o:p></span></p> <p class="MsoListParagraphCxSpFirst" style="text-indent: -0.25in;"><!--[if !supportLists]--><span style=""><span style="">5.<span style="font-family: "Times New Roman"; font-style: normal; font-variant: normal; font-weight: normal; font-size: 7pt; line-height: normal; font-size-adjust: none; font-stretch: normal;"> </span></span></span><!--[endif]-->After the attach is done, you can verify that the files that are attached are online or not by using:</p> <p class="MsoListParagraphCxSpMiddle" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;">USE [database_name]<o:p></o:p></span></p> <p class="MsoListParagraphCxSpMiddle" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;">go<o:p></o:p></span></p> <p class="MsoListParagraphCxSpMiddle" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;">select name as logical_name, physical_name, type_desc, state_desc<o:p></o:p></span></p> <p class="MsoListParagraphCxSpMiddle" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;">from sys.database_files<o:p></o:p></span></p> <p class="MsoListParagraphCxSpLast" style="margin-bottom: 0.0001pt; text-indent: -0.25in; line-height: normal;"><!--[if !supportLists]--><span style=""><span style="">6.<span style="font-family: "Times New Roman"; font-style: normal; font-variant: normal; font-weight: normal; font-size: 7pt; line-height: normal; font-size-adjust: none; font-stretch: normal;"> </span></span></span><!--[endif]-->Then you can run the create replicaiton scripts to recreate replication. (Note, as you are moving databases to different server, the scripts are to be edited for the new location of the server!).</p> <p class="MsoNormal" style="margin: 0in 0in 0.0001pt 0.25in; line-height: normal;"><o:p> </o:p></p> <p class="MsoNormal" style="margin: 0in 0in 0.0001pt 0.25in; line-height: normal;"><b style=""><u>GOTCHAS! <o:p></o:p></u></b></p> <p class="MsoListParagraphCxSpFirst" style="margin-bottom: 0.0001pt; text-indent: -0.25in; line-height: normal;"><!--[if !supportLists]--><span style=""><span style="">1.<span style="font-family: "Times New Roman"; font-style: normal; font-variant: normal; font-weight: normal; font-size: 7pt; line-height: normal; font-size-adjust: none; font-stretch: normal;"> </span></span></span><!--[endif]-->If replication is not dropped, then detach database will fail.</p> <p class="MsoListParagraphCxSpMiddle" style="margin-bottom: 0.0001pt; text-indent: -0.25in; line-height: normal;"><!--[if !supportLists]--><span style=""><span style="">2.<span style="font-family: "Times New Roman"; font-style: normal; font-variant: normal; font-weight: normal; font-size: 7pt; line-height: normal; font-size-adjust: none; font-stretch: normal;"> </span></span></span><!--[endif]-->While attaching the database, if the full text catalog path is not provided correctly, then the full backup will fail with following error:</p> <p class="MsoListParagraphCxSpLast" style="line-height: normal;"><span style="color: rgb(54, 95, 145);">"Backup failed for Server 'ComputerName\SQLInstanceName' " or "The backup of the file or filegroup 'sysft_FullTextCatalog' is not permitted because it is not online"<o:p></o:p></span></p> <p class="MsoNormal" style="line-height: normal;">This can be resolved as follows:</p> <p class="MsoNormal" style="line-height: normal;"><span style="color: rgb(79, 129, 189);">ALTER DATABASE [database_name] SET OFFLINE<o:p></o:p></span></p> <p class="MsoNormal" style="line-height: normal;"><span style="color: rgb(79, 129, 189);">ALTER DATABASE [database_name] <span style=""> </span>MODIFY FILE ( NAME = [logical_name_of_FTCatalog] , FILENAME = 'S:\FullText\catalog_folder_name')<o:p></o:p></span></p> <p class="MsoNormal" style="line-height: normal;"><span style="color: rgb(79, 129, 189);">ALTER DATABASE [database_name] <span style=""> </span>SET ONLINE<o:p></o:p></span></p> <p class="MsoNormal" style="line-height: normal;">This should bring the FT catalog ONLINE and you are good to take full backup.</p> <p class="MsoNormal" style="line-height: normal;">If that does not bring the FTcatalog Online, then you have to rebuild the FT catalog or delete the old one and recreate from scratch.</p> <p class="MsoNormal" style="line-height: normal;"><o:p> </o:p></p> <p class="MsoNormal" style="line-height: normal;">Ref: <a href="http://support.microsoft.com/kb/923355">http://support.microsoft.com/kb/923355</a>; <a href="http://msdn.microsoft.com/en-us/library/ms345483.aspx">http://msdn.microsoft.com/en-us/library/ms345483.aspx</a>; <a href="http://msdn.microsoft.com/en-us/library/ms174397.aspx">http://msdn.microsoft.com/en-us/library/ms174397.aspx</a>; <a href="http://social.msdn.microsoft.com/forums/en-US/commserver2007/thread/307b9141-9063-4788-9673-e23b49fe6aeb">http://social.msdn.microsoft.com/forums/en-US/commserver2007/thread/307b9141-9063-4788-9673-e23b49fe6aeb</a> </p> <p class="MsoNormal" style="line-height: normal;"><o:p> Hope this helps !</o:p></p><p class="MsoNormal" style="line-height: normal;">
<br /><o:p></o:p></p>\\K <p class="MsoNormal" style="margin: 0in 0in 0.0001pt 0.5in; line-height: normal;"><o:p> </o:p></p> <p class="MsoNormal" style="margin: 0in 0in 0.0001pt 0.25in; line-height: normal;"><o:p> </o:p></p> <p class="MsoNormal" style="margin: 0in 0in 0.0001pt 0.25in; line-height: normal;"><o:p> </o:p></p> <p class="MsoListParagraph"><o:p> </o:p></p> <p class="MsoNormal" style="margin-left: 0.5in;"><o:p> </o:p></p> <p class="MsoListParagraphCxSpFirst"><span style="font-size: 10pt; line-height: 115%; font-family: "Courier New"; color: blue;"><o:p> </o:p></span></p> <p class="MsoListParagraphCxSpLast"><span style="font-size: 10pt; line-height: 115%; font-family: "Courier New"; color: blue;"><o:p> </o:p></span></p> SQL_Questhttp://www.blogger.com/profile/14909213275379800133noreply@blogger.com0tag:blogger.com,1999:blog-107222033192989189.post-24997432740297821552009-11-11T13:21:00.001-05:002009-11-11T15:35:20.397-05:00Are you exaggerating on your résumé?Recently, I came across a very good post worth reading: <a href="http://resources.jobsbridge.com/?p=157">Are you exaggerating on your résumé?</a><br /><br />Just check it out as it has good points to discuss and keep in mind !<br /><br />\\KSQL_Questhttp://www.blogger.com/profile/14909213275379800133noreply@blogger.com0tag:blogger.com,1999:blog-107222033192989189.post-86267531768801193032009-10-13T18:48:00.003-04:002009-10-13T21:41:02.639-04:00Script to append the output file for SQL Agent job logging.SQL Agent has a provision to log output to an external file. This is called "verbose logging" and this helps in proper management of multiple SQL Agent jobs.<br />Below is the script by which an output file can be pointed for logging as per the job is configured (either on failure, success or both).<br /><br />script:<br /><br />--Note: first create a folder called "Job_logs" in K:\<br /><br />USE msdb<br />go<br /><br />declare @bkpath varchar(300)<br />Set @bkpath = 'K:\job_Logs\' --- sets the path to the folder created<br /><br />update sysjobsteps<br />set output_file_name = @bkpath + REPLACE(j.name, '''', '') + ' - ' + s.step_name + '.txt'<br />from sysjobsteps s, sysjobs j<br />where s.job_id = j.job_id and output_file_name is null<br /><br /><br />--Check if the path pointed is added correctly:<br /><br />USE msdb ;<br />GO<br />EXEC dbo.sp_help_job<br /> @job_name = N'<job_name>', -- specify the job name<br /> @job_aspect = 'steps' ; -- tells the output_file_name<br />GO<br /><br />Hope this helps !<br /><br />Thanks,<br /><br />\\KSQL_Questhttp://www.blogger.com/profile/14909213275379800133noreply@blogger.com0tag:blogger.com,1999:blog-107222033192989189.post-34467953017671172532009-09-26T11:44:00.004-04:002009-09-26T12:21:26.867-04:00Is it good to put data and log files together ?This is a most common question asked ! The answer to this depends on the environment you work in.<br />What I mean by that is: If your environment is a heavy read / write then it is good to put data (.mdf) and log files (.ldf) on separate physical disks (preferably RAID). The reason for this is that when there are heavy inserts/updates/deletes, the way SQL server handles is that<br />1. It writes to the log files that it is going to change the data<br />2. It writes to the data files<br />3. It writes to the log file that it has changed the data i.e it commits the transaction.<br />This shows that it has to write both to the log and data files and the writes are random meaning it writes to the log file then jumps to data file and then again to the log file. This kills the performance making the writes much slower !<br /><br />There might be good reasons to put data and log files on same disk/drive like<br />1. If your database is READ-ONLY i.e having more read activity than write like reporting or data warehouse.<br />2. If there is a lot of use of TEMP-DB only as compared to data / log file drives.<br />3. If you have a RAID-10 array, its good to put your data and log files on the same drive as RAID-10 is fast and have both mirroring and data striping functionality.<br /><br />Although, to decide what should be implemented, a thorough research about the environment should be done and when the system is implemented using PERFMON to track the disk activity i.e disk read/writes helps !<br /><br />Also to change the location of data and log file ALTER DATABASE can be used. **see my previous post how this can be done **<br /><br />Hope this helps !<br /><br />Thanks,<br />\\KSQL_Questhttp://www.blogger.com/profile/14909213275379800133noreply@blogger.com0tag:blogger.com,1999:blog-107222033192989189.post-60815308344190598122009-07-08T13:06:00.000-04:002009-07-08T13:40:14.664-04:00Reporting Services Login box appears when trying to deploy in SSRS 2005 SP3<span style=";font-family:Verdana,Arial;font-size:85%;" >Hi All,<br /><br />I installed <span style="text-decoration: underline;">SQL Server</span> 2005 SP3 recently. After that when I try to<br />deploy a solution in SSRS 2005, I get a dialog box with title:<br />"Reporting Services Login". It has my server listed as the first row:<br />"<a title="http://localhost/reportserver%22" href="http://localhost/reportserver%22">http://localhost/reportserver"</a> and then it has two <span style="text-decoration: underline;">text boxes </span>asking<br />for Username and Password.<br /><br />Solution/Approach:<br /><br />When I was deploying the reports, in the properties of the report in target server URL field, I had http://localhost/Reports<br /><br />I changed it to http://localhost/Reportserver, and everything worked fine.<br /><br />e.g:<br /></span><span style="font-size:85%;"><span style="font-family:Verdana;">1. Open your project in <span style="font-weight: bold;">SQL Server Business Intelligence Development Studio</span><br />2. Select the project menu followed by the properties of the project.<br />3. Change the TargetServerURL: To the appropriate path to your reportserver.(</span></span><span style=";font-family:Verdana,Arial;font-size:85%;" >http://localhost/Reportserver</span><span style="font-size:85%;"><span style="font-family:Verdana;">)</span></span><br /><span style=";font-family:Verdana,Arial;font-size:85%;" ><br /><br />Hope this helps,<br /><br />Thanks,<br /><br />\\K<br /></span>SQL_Questhttp://www.blogger.com/profile/14909213275379800133noreply@blogger.com0tag:blogger.com,1999:blog-107222033192989189.post-66878233137718258642009-06-05T11:14:00.002-04:002009-06-05T12:25:03.375-04:00Moving database from one location to anotherHi All,<br /><br />Recently I ran across a situation where in there was a GIS application which was trying to feed a large amount of data (more than 22GB) to SQL server. This led to the increase of the data file (.mdf) and eventually this led to filling the disk space. So came the situation of moving the database to a different location where there was more room for growth !!<br /><br /><span style="font-weight: bold;">Solution/Approach</span>:<br />1. using Attach/detach method.<br />use adventureworks<br />go<br /><br /><span style="font-weight: bold;">sp_helpdb 'adventureworks' </span> ---> this will show you the logical and physical file names of the data and the log files for a particula database<br /><br /><span style="font-weight: bold;">Detach the database:</span><br /><pre class="libCScode" style="white-space: pre-wrap;" id="ctl00_MTContentSelector1_mainContentContainer_ctl30other" space="preserve">USE master;<br />GO<br />EXEC sp_detach_db @dbname = N'AdventureWorks';<br />GO<br /><br /><span style="font-weight: bold;">Move the files data</span>(.mdf), secondary data file(.ndf) and log files (.ldf) to the desired location.<br /><br /><span style="font-weight: bold;">Attach the database:</span><br /><br /><br />USE master;<br />GO<br />CREATE DATABASE MyAdventureWorks<br /> ON (FILENAME = 'C:\MySQLServer\AdventureWorks_Data.mdf'),<br /> (FILENAME = 'C:\MySQLServer\AdventureWorks_Log.ldf')<br /> FOR ATTACH;<br />GO<br /></pre>This method has certain drawback and is not recomended by BOL: "We recommend that you move databases by using the ALTER DATABASE planned relocation procedure, instead of using detach and attach."<br /><br />So the recomended method is to use ALTER DATABASE.<br /><br /><br />1. sp_helpdb 'adventureworks' -> <span style="color: rgb(51, 102, 255);">will give logical and physical file names </span><br /><br />2. use the alter database command to move the files.<br /><br /> ALTER DATABASE adventureworks<br />MODIFY FILE (NAME = adventureworks_dat, FILENAME = 'E:\adventureworks_database\adventureworks.mdf');<br />ALTER DATABASE adventureworks<br />MODIFY FILE (NAME = adventureworks_dat2, FILENAME = 'D:\adventureworks_data_file2\adventureworks_dat2.ndf'); --- <span style="color: rgb(51, 51, 255);">here if you have any secondary data file, if not ignore this</span><br />ALTER DATABASE adventureworks<br />MODIFY FILE (NAME = adventureworks_log, FILENAME = 'E:\adventureworks_database\adventureworks.ldf');<br /><br /><br />ALTER DATABASE adventureworks SET OFFLINE<br />WITH ROLLBACK IMMEDIATE --- <span style="color: rgb(51, 51, 255);">this will take your database immediately offline</span><br /><br />Now move the data files and log file to the respective desired location.<br /><br />ALTER DATABASE adventureworks SET ONLINE --<span style="color: rgb(51, 51, 255);"> this will take your database online</span><br /><br />This will move your database from one location to another and is more efficient way to do.<br /><br />This is a good link to follow: <a href="http://www.sqlservercentral.com/articles/Administration/65896/"><span style="color: rgb(0, 153, 0);">http://www.sqlservercentral.com/articles/Administration/65896/</span></a><br /><br />Hope this helps !!<br /><br />\\KSQL_Questhttp://www.blogger.com/profile/14909213275379800133noreply@blogger.com0tag:blogger.com,1999:blog-107222033192989189.post-75945642001911397632009-06-03T09:39:00.003-04:002009-06-03T09:55:09.202-04:00SQL2005 [264] An attempt was made to send an email when no email session has been established.Hi All,<br /><br />Below is the problem which is more obvious to face when a fresh install of SQL server 2005 is done.<br /><br /><span style="font-weight: bold;">Problem</span>: "[264] An attempt was made to send an email when no email session has been established." This message is logged in the SQL agent error logs.<br /><br />The message is from a job which will send email to an operator when it is completed. In the operator notification setting, I the correct email address has been entered for the 'Email Name' text box, such as <a title="mailto:abc@xyz.com" href="mailto:abc@xyz.com">kkk@xyz.com</a><br />However the Database Mail is configured and I can send/receive a test email.<br /><br /><span style="font-weight: bold;">Solution/Approach:</span><br /><br />1. In Management Studio, right-click on SQL Server Agent and select "Properties."<br />2. Click "Alert System"<br />3. <span style="font-weight: bold;">Uncheck</span> "Enable mail profile"<br />4. Click "OK"<br />5. right-click SQL Server Agent again and select "Properties."<br />6. click "Alert System"<br />7. <span style="font-weight: bold;">Check</span> "Enable mail profile"<br />8. Click "OK"<br />9. <span style="font-weight: bold;">Restart SQL Server Agent.</span> (this will not bring any thing down, unless jobs are running at the moment when SQL agent is restarted i.e the running jobs will fail, but they can be ran after the agent is restarted)<br /><br />Hope this helps !!!<br /><br />\\KSQL_Questhttp://www.blogger.com/profile/14909213275379800133noreply@blogger.com0tag:blogger.com,1999:blog-107222033192989189.post-44224464790058099882009-06-01T16:27:00.002-04:002009-06-01T16:44:40.925-04:00SSIS package fails when scheduled as SQL server agent job<span style="font-weight: bold;">Problem:</span> When run through the BIDS or Visual Studio, the SSIS package runs well. But when scheduled as a SQL agent job, the SSIS package fails with the error message:<br />" Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS:Property" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. "<br /><br /><span style="font-weight: bold;">Solution:</span> It took me a while to figure out the problem and the same was mentioned at SSC.<br /><span><span id="_ctl6_ctlTopic"><span id="_ctl6_ctlTopic_ctlPanelBar"><span id="_ctl6_ctlTopic_ctlPanelBar_ctlTopicsRepeater__ctl22_lblFullMessage">The standard security on an SSIS package is "EncryptSensistiveWithUserKey" which only allows the user that created it to be able to run in as long as there is sensitive information (passwords) stored within the package.<br /><br />A workaround that I am using is that I changed the package security to "<span style="font-weight: bold;">EncryptAllWithPassword</span>" which protects the entire package with a password. When you set the package as a job step, go into the properties of that job step, click on the "Command Line" tab and you should be prompted for the password. After you enter the password, the /DECRYPT switch should show up in the command line and the job should then be able to run under a different user.<br /><br />Users won't be able to see the password because they will need to enter it before viewing the command line for the job step.</span></span></span></span><br />(The issue is addressed nicely with many inputs at http://www.sqlservercentral.com/Forums/Topic349385-148-1.aspx)SQL_Questhttp://www.blogger.com/profile/14909213275379800133noreply@blogger.com0tag:blogger.com,1999:blog-107222033192989189.post-61706688455081419922009-05-28T14:21:00.002-04:002009-05-28T14:25:47.841-04:00Installing and configuring the ArcSDE service for SQL server is an easy job, but sometimes the ArcSDE service which is dependent on SQL server stops due to server restart or due to user shutdown. This happens even though the ArcSDE service is set to automatic start in case there is a server reboot.<br /><br /><span style="font-weight: bold;">Problem:</span> The ArcSDE 9.3 service running on SQL server cannot be started. When attempted to start from GUI (cmd ->services.msc -> rt click esri_sde service -> start), it will show an error message that "the ArcSDE service started and stopped. This may happen due to the fact that some services may stop as they have no work to do"<br /><br /><span style="font-weight: bold;">Solution/Approach: </span><br /><br />1. run -> cmd -> sdeservice -o list (this will list the status of the sde service running)<br /> Note: to see the status of the service use ------ sdemon -o status -i 5151 ------<br />2. sdemon -o start<br /> Provide the sde password that was created during the ArcSDE installation.<br /><br />This will start the service !!!SQL_Questhttp://www.blogger.com/profile/14909213275379800133noreply@blogger.com0tag:blogger.com,1999:blog-107222033192989189.post-19927400739502354732009-05-28T12:37:00.000-04:002009-05-28T13:24:11.341-04:00I came across a very minor issue, but often not scrutinized and this can lead to major trouble !!<br /><br /><span style="font-weight: bold;">Problem:</span> After the SP3 installation, the report server was not available to the application accessing it. i.e the user (report user: it is a fixed user used to pull out reports -- user1) was denied access to the database.<br /><br /><span style="font-weight: bold;">solution: </span><br /><br />1. login to the server where your report server is installed.<br /><br />2. go to Administrative tools --> IIS manager --> on left part Expand local computer --> websites --> default websites --> reports --> rt click --> properties --> DIRECTORY SECURITY --> Authentication and access control --> EDIT --> CHECK THE CHECK BOX at top part [enable annonymous access]<br /><br />This happens during the installation of service pack and the ""enable annonymous access"" becomes unchecked !!!<br /><br /><br />This fixes the issue !!SQL_Questhttp://www.blogger.com/profile/14909213275379800133noreply@blogger.com0tag:blogger.com,1999:blog-107222033192989189.post-38848343168218939652009-05-28T12:33:00.000-04:002009-05-28T12:37:13.239-04:00SQL server SP3 installation problemRecently, I installed SP3 for SQL server 2005. The installation went successful, but after the installation the maintenance plans were failing.<br /><br /><span style="font-weight: bold;">Problem:</span> The maintenance jobs are failing after the SP3 installation on the test server.<br /><br /><span style="font-weight: bold;">Error Message</span>: Unable to cast COM object of type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.PackageNeutralClass' to interface type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSContainer90'.<br /><br /><span style="font-size:100%;"><br /><span style="font-weight: bold;">Solution/Approach</span></span>: This message is generally due to the unregistered COM in the dts.dll file in the \90\binn folder of the SQL server installation folder.<br /><br />On Server follow below steps to fix the issue:<br /><br />1. start -> run -> cmd<br />2. cd c:\Program Files\Microsoft SQL Server\90\DTS\binn<br />3. REGSVR32.EXE dts.dll<br /><br />This fixes the issue !!SQL_Questhttp://www.blogger.com/profile/14909213275379800133noreply@blogger.com0tag:blogger.com,1999:blog-107222033192989189.post-78671893743829147152009-05-26T20:47:00.001-04:002009-05-26T20:52:46.317-04:00SSIS package fails to execute when scheduled as a jobRecently, I was creating an SSIS package and the package when executed in the BIDS environment was successfully run, but when scheduled through a job in SSMS gave me an error:<br /><span style="color: rgb(255, 0, 0);"><em><span style="color: rgb(255, 102, 0);">Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node “DTS:Password” with error 0×8009000B “Key not valid for use in specified state.”. 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: 2008-09-15 16:34:32.57 Code: 0xC0202009 Source: TextFileImport Connection manager “DecipherDestination” Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0×80004005. An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0×80004005 Description: “Login timeout expired”. An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0×80004005 Descrip… The package execution fa… The step failed.</span></em></span><br /><br />Meanwhile I came across a good post describing the same situation:<br /><br />http://decipherinfosys.wordpress.com/2008/09/17/scheduling-ssis-packages-with-sql-server-agent/<br /><br />Thanks to the original poster !!SQL_Questhttp://www.blogger.com/profile/14909213275379800133noreply@blogger.com0