You can enable connection logging. For SQL Server 2008, you can enable Login Auditing. In SQL Server Management Studio, open SQL Server Properties &gt; Security &gt; Login Auditing select &quot;Both failed and successful logins&quot;.

Make sure to restart the SQL Server service.

Once you&#39;ve done that, connection attempts should be logged into SQL&#39;s [error log](http://msdn.microsoft.com/en-us/library/ms187109(v=SQL.100).aspx). The physical logs location can be determined [here](http://msdn.microsoft.com/en-us/library/ms187885(v=SQL.100).aspx).

Another way to check on connection attempts is to look at the server&#39;s event log.  On my Windows 2008 R2 Enterprise machine I opened the server manager (right-click on Computer and select Manage.  Then choose Diagnostics -&gt; Event Viewer -&gt; Windows Logs -&gt; Applcation.
You can filter the log to isolate the MSSQLSERVER events.
I found a number that looked like this

Login failed for user &#39;bogus&#39;. The user is not associated with a trusted SQL Server connection. [CLIENT: 10.12.3.126]

If you&#39;d like to track only failed logins, you can use the SQL Server Audit feature (available in SQL Server 2008 and above). You will need to add the SQL server instance you want to audit, and check the failed login operation to audit.

Note: tracking failed logins via SQL Server Audit has its disadvantages. For example - it doesn&#39;t provide the names of client applications used. 

If you want to audit a client application name along with each failed login, you can use an Extended Events session.

To get you started, I recommend reading this article: http://www.sqlshack.com/using-extended-events-review-sql-server-failed-logins/

I&#39;m trying to think of the best way to return an empty array, rather than `null`.

Is there any difference between `foo()` and `bar()`?

    private static File[] foo() {
        return Collections.emptyList().toArray(new File[0]);
    }

&lt;!-- separator --&gt;

    private static File[] bar() {
        return new File[0];
    }

Returning an empty array

How would I use python to check a list and delete all duplicates? I don&#39;t want to have to specify what the duplicate item is - I want the code to figure out if there are any and remove them if so, keeping only one instance of each. It also must work if there are multiple duplicates in a list. 

For example, in my code below, the list lseparatedOrbList has 12 items - one is repeated six times, one is repeated five times, and there is only one instance of one. I want it to change the list so there are only three items - one of each, and in the same order they appeared before. I tried this:

    for i in lseparatedOrbList:
	   for j in lseparatedOrblist:
		    if lseparatedOrbList[i] == lseparatedOrbList[j]:
			    lseparatedOrbList.remove(lseparatedOrbList[j])

But I get the error:

    Traceback (most recent call last):
      File &quot;qchemOutputSearch.py&quot;, line 123, in &lt;module&gt;
        for j in lseparatedOrblist:
    NameError: name &#39;lseparatedOrblist&#39; is not defined

I&#39;m guessing because it&#39;s because I&#39;m trying to loop through lseparatedOrbList while I loop through it, but I can&#39;t think of another way to do it.




How to remove all duplicate items from a list

Does SQL Server has an external log file or internal table for attempted connections, or is that kind of info put somewhere in the Windows Event Log? 

Where are SQL Server connection attempts logged?

<p>Does SQL Server has an external log file or internal table for attempted connections, or is that kind of info put somewhere in the Windows Event Log?</p>


This isn&#39;t working in SQL Server 2008:

    ALTER TABLE Employee ALTER COLUMN CityBorn SET DEFAULT &#39;SANDNES&#39;

The error is:

&gt; Incorrect syntax near the keyword &#39;SET&#39;.

What am I doing wrong?

How to set a default value for an existing column

When I create a table in SQL Server and save it, if I try to edit the table design, like change a column type from int to real, I get this error:

&gt; Saving changes is not permitted. The change you have made requires the following table to be dropped and re-created. You have either made changes to a table that can&#39;t be recreated or enabled the option prevent saving changes that require the table to be re-created.

Why do I have to re-create the table? I just want to change a data type from `smallint` to `real`.

The table is empty, and I didn&#39;t use it until now.


Sql Server &#39;Saving changes is not permitted&#39; error ► Prevent saving changes that require table re-creation

I have a question about [User-Defined Table Types][1] in SQL Server 2008.

For the need of one of the ASP.NET application we defined our own table-types on SQL Server 2008 to use them  as parameters in the stored procedures (when executing sql command in ASP.NET application we pass DataTable object as parameter for stored procedure [see here for an example][2])

The problem is that when we run Sql command (execute stored procedure) from ASP.NET we get an error:

&gt; The EXECUTE permission was denied on the object &#39;ourTableType&#39;,
&gt; database &#39;ourDatabase&#39;, schema &#39;ourSchema&#39;.

Why is that so? Why do we need to set permission on user-defined table types? Why is not enough to have permission set just on stored procedure that uses it? And if we have to set it no matter what, why there is no `EXECUTE` permission type to set in properties window whatsoever (I can see only `Control`, `References`, `Take Ownership`, `View Definition`)?

What I also don&#39;t understand is that setting permission to `Control` in properties window solves the problem and the stored procedure runs without problems.

  [1]: http://msdn.microsoft.com/en-us/library/bb522526.aspx
  [2]: http://www.codeproject.com/KB/database/sqlserver2008.aspx

The EXECUTE permission is denied on the user-defined table types?

Usually in `SQL Server` Common Table Expression clause there is semicolon in front of the statement, like this:

    ;WITH OrderedOrders AS --semicolon here
    (
        SELECT SalesOrderID, OrderDate,
        ROW_NUMBER() OVER (ORDER BY OrderDate) AS &#39;RowNumber&#39;
        FROM Sales.SalesOrderHeader 
    ) 
    SELECT * 
    FROM OrderedOrders 
    WHERE RowNumber BETWEEN 50 AND 60

Why?

Common Table Expression, why semicolon?

What is the difference between using

    SELECT ... INTO MyTable FROM...

and

    INSERT INTO MyTable (...)
    SELECT ... FROM ....

?

From BOL [ [INSERT][1], [SELECT...INTO][2] ], I know that using SELECT...INTO will create the insertion table *on the default file group* if it doesn&#39;t already exist, and that the logging for this statement depends on the recovery model of the database.

 1. Which statement is preferable? 
 2. Are there other performance implications?
 3. What is a good use case for SELECT...INTO over INSERT INTO ...?

[1]: http://msdn.microsoft.com/en-us/library/ms174335.aspx
[2]: http://msdn.microsoft.com/en-us/library/ms188029.aspx

Edit: I already stated that I know that that SELECT INTO... creates a table where it doesn&#39;t exist.  What I want to know is that SQL includes this statement for a reason, what is it? Is it doing something different behind the scenes for inserting rows, or is it just syntactic sugar on top of a `CREATE TABLE` and `INSERT INTO`.


INSERT INTO vs SELECT INTO

I am developing a large commercial program and keep confusing myself between what kind of information i want to log with Log.INFO and Log.DEBUG.  

Are there any standards or canonical Python Enhancement Proposal / Java standard conventions / rules defined for other languages on what each type of log message contains?

Log.INFO vs. Log.DEBUG

For simple debugging in a complex project is there a reason to use the python logger instead of print?  What about other use-cases?  Is there an accepted best use-case for each (especially when you&#39;re only looking for stdout)?

I&#39;ve always heard that this is a &quot;best practice&quot; but I haven&#39;t been able to figure out why.

In python, why use logging instead of print?

In a python script I am writing, I am trying to log events using the logging module. I have the following code to configure my logger:

    ERROR_FORMAT = &quot;%(levelname)s at %(asctime)s in %(funcName)s in %(filename) at line %(lineno)d: %(message)s&quot;
    DEBUG_FORMAT = &quot;%(lineno)d in %(filename)s at %(asctime)s: %(message)s&quot;
    LOG_CONFIG = {&#39;version&#39;:1,
                  &#39;formatters&#39;:{&#39;error&#39;:{&#39;format&#39;:ERROR_FORMAT},
                                &#39;debug&#39;:{&#39;format&#39;:DEBUG_FORMAT}},
                  &#39;handlers&#39;:{&#39;console&#39;:{&#39;class&#39;:&#39;logging.StreamHandler&#39;,
                                         &#39;formatter&#39;:&#39;debug&#39;,
                                         &#39;level&#39;:logging.DEBUG},
                              &#39;file&#39;:{&#39;class&#39;:&#39;logging.FileHandler&#39;,
                                      &#39;filename&#39;:&#39;/usr/local/logs/DatabaseUpdate.log&#39;,
                                      &#39;formatter&#39;:&#39;error&#39;,
                                      &#39;level&#39;:logging.ERROR}},
                  &#39;root&#39;:{&#39;handlers&#39;:(&#39;console&#39;, &#39;file&#39;)}}
    logging.config.dictConfig(LOG_CONFIG)
When I try to run `logging.debug(&quot;Some string&quot;)`, I get no output to the console, even though [this page in the docs][1] says that `logging.debug` should have the root logger output the message. Why is my program not outputting anything, and how can I fix it?


  [1]: http://docs.python.org/library/logging.html#logging.debug

Python logging not outputting anything

We have developed an assembly for SQL Server 2008 R2.

The assembly has been working for a week.  The managed stored proc inside the assembly was working fine for the whole week and then it stops working.  We have been seeing this problem couple times.  The way to make it work again is to restart the SQL Server.


    Msg 10314, Level 16, State 11, Line 4
    An error occurred in the Microsoft .NET Framework while trying to load assembly id 65536. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error: 
      System.IO.FileLoadException: Could not load file or assembly &#39;myAssembly, Version=2.0.0.490, Culture=neutral, PublicKeyToken=5963130873dd3a75&#39; or one of its dependencies. Exception from HRESULT: 0x80FC0E21 System.IO.FileLoadException:
      at System.Reflection.Assembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark&amp; stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
      at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark&amp; stackMark, Boolean forIntrospection)
      at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark&amp; stackMark, Boolean forIntrospection)
      at System.Reflection.Assembly.Load(String assemblyString)

I have found different articles on the web.  

[This KB](http://support.microsoft.com/kb/918040) suggested that I might have restored the database from another SQL Server, which I swear I didn&#39;t.  

[This blog](http://www.sqlskills.com/BLOGS/BOBB/category/SQLCLR.aspx) said I might run into this if I installed .NET 3.5 on SQL Server 2005 but mine was SQL Server 2008 R2 and I did not install anything when this problem occurs.

The main point is that it can keep going for a period of time.  It just stops working randomly.  Then, if we restart the SQL Server, it will start working again.  I have thought of my server was really running out of memory but now, I just see the problem again.  SQL Server is using 300MB RAM only and my server has 16GB RAM.  This sounds impossible that it&#39;s because I am running out of memory.

Now, I want to collect more information on this problem.  Any log that I can turn on and look at?  Any suggestion that help troubleshooting this problem is welcome.

I have run some SQL queries.

    SELECT * from sys.dm_clr_properties
    =============================================
    directory	C:\Windows\Microsoft.NET\Framework64\v2.0.50727\
    version	v2.0.50727
    state	CLR is initialized
.

    SELECT * from sys.dm_clr_appdomains
    ======================================================
    0x0000000087160240	3	mydatabase.dbo[runtime].2	2011-08-12 08:44:08.940	10	1	E_APPDOMAIN_SHARED	1	1
.

    SELECT * from sys.dm_clr_tasks
    ======================================================
    0x000000008185A080	0x00000000818562C8	0x0000000000000000	E_TASK_ATTACHED_TO_CLR	E_ABORT_NONE	E_TYPE_ADUNLOAD	0	0
    0x00000000818CE080	0x00000000818CA2C8	0x0000000000000000	E_TASK_ATTACHED_TO_CLR	E_ABORT_NONE	E_TYPE_FINALIZER	0	0
    0x0000000081AD4C30	0x000000000400D048	0x0000000000000000	E_TASK_ATTACHED_TO_CLR	E_ABORT_NONE	E_TYPE_USER	0	0

.

    SELECT * from sys.dm_clr_loaded_assemblies
    &lt;returns nothing&gt;

*** UPDATE ***

On my SQL Server, I have created four databases.  Each of them with the same assembly attached to it.  Now, SQL Server refused to load the assembly and gave me the above error.

`SELECT * from sys.dm_clr_appdomains` shows me at that point there was only one appdomain loaded and `SELECT * from sys.dm_clr_loaded_assemblies` showed me there were no assemblies loaded at all.

Then, I ran the same stored proc on the other three databases.  It worked and successfully loaded up the assemblies and successfully ran the stored proc.  After executing the stored proc.  `SELECT * from sys.dm_clr_appdomains` now shows me there are only four appdomain loaded and `SELECT * from sys.dm_clr_loaded_assemblies` showed me there are now three assemblies loaded.

This makes sense.  Now, I hope if I run the stored proc again in the original database, it should get the assembly loaded as it were.  Guess what.  No, it doesn&#39;t.  It still gives me the same error.  It looks like this database is completely stuck.  The only way to fix it is to reboot the SQL Server.  I am hoping there is a flag/lock somewhere in the system table holding up this.  I cannot find it.  Any idea is welcome.

Now, my SQL Server is in the state that requiring me to reboot to make it work again.  

*** UPDATE (8/31/2011) ***

It sounds like it&#39;s related to the database owner of the database.  This is kind of complicated.  We have two sites and two AD forests.  The SQL Server machine is joined to forest A but the database owner is from forest B.  The connection between forest A and forest B is not that stable since they are in two different sites physically connected by WAN.

Once I change the database owner to a SQL Login (Non-Windows account), my stored proc is up running for couple weeks so far with no interruption.

I will accept the answer if anybody can explain it.

SQL Server stops loading assembly

There are two ways of configuring and using log4net. First one is when I can configure my own appender and associated logger:

    &lt;!-- language: xml --&gt;

    &lt;appender name=&quot;myLogAppender&quot; type=&quot;log4net.Appender.RollingFileAppender&quot; &gt;
        &lt;file value=&quot;Logs\myLog.log&quot; /&gt;
        &lt;layout type=&quot;log4net.Layout.PatternLayout&quot;&gt;
            &lt;conversionPattern value=&quot;%date %level - %message%n&quot; /&gt;
        &lt;/layout&gt;
    &lt;/appender&gt;
    
    &lt;logger name=&quot;myLog&quot;&gt;
        &lt;level value=&quot;All&quot;&gt;&lt;/level&gt;
        &lt;appender-ref ref=&quot;myLogAppender&quot; /&gt;
    &lt;/logger&gt;

And then when I want to write something in log, I can do the following:

    ILog log = LogManager.GetLogger(&quot;myLog&quot;);
    log.Info(&quot;message&quot;);

Another way to use it is to configure root to be as detailed as I want:

    &lt;!-- language: xml --&gt;
  
    &lt;root&gt;
        &lt;level value=&quot;Error&quot; /&gt;
        &lt;appender-ref ref=&quot;myLogAppender&quot; /&gt;
    &lt;/root&gt;

And in this case I can log messages like this:

    ILog log = LogManager.GetLogger(typeof(Bar));
    log.Info(&quot;message&quot;);

The benefits of second approach is that you can enable or disable some messages on the fly. But the problem is that I&#39;m developing in EPiServer CMS and it has its own logging system that uses log4net and if I enable info logging at root level, then a lot of system logs will be written. 

How do you use log4net? Each part of a system writes in its own logger, or everything is written in default logger, and configuration decides what to do next? 

Correct way of using log4net (logger naming)

Having this simple code I get &quot;Cannot drop database &quot;test_db&quot; because it is currently in use&quot; (CleanUp method) as I run it. 

    [TestFixture]
    public class ClientRepositoryTest
    {
		private const string CONNECTION_STRING = &quot;Data Source=.;Initial Catalog=test_db;Trusted_Connection=True&quot;;
		private DataContext _dataCntx;

		[SetUp]
		public void Init()
		{
			Database.SetInitializer(new DropCreateDatabaseAlways&lt;DataContext&gt;());
			_dataCntx = new DataContext(CONNECTION_STRING);
			_dataCntx.Database.Initialize(true);
		}

		[TearDown]
		public void CleanUp()
		{
			_dataCntx.Dispose();
			Database.Delete(CONNECTION_STRING);
		}
	}


DataContext has one property like this 

     public DbSet&lt;Client&gt; Clients { get; set; }


How can force my code to remove database?
Thanks

&quot;Cannot drop database because it is currently in use&quot;. How to fix?

I am doing something like this...

    conn = sqlite3.connect(db_filename)

    with conn:
        cur = conn.cursor()
        cur.execute( ... )

`with` automatically commits the changes. But the docs say nothing about closing the connection. 

Actually I can use `conn` in later statements (which I have tested). Hence it seems that the context manager is not closing the connection.

Do I have to manually close the connection. What if I leave it open?

**EDIT**

My findings:

 - The **connection is not closed** in the context manager, I have tested and confirmed it. Upon `__exit__`, the context manager only commits the changes by doing `conn.commit()`
 - `with conn` and `with sqlite3.connect(db_filename) as conn` are same, so using either will still keep the connection alive
 - `with` statement does not create a new scope, hence all the variables created inside the suite of with will be accessible outside it
 - Finally, you should close the connection manually


What if I don&#39;t close the database connection in Python SQLite

When attempting to connect to MSSQL database via ASP.NET online, I will get the following when two or more people connect simultaneously:

&gt;ExecuteReader requires an open and available Connection. The connection&#39;s current state is Connecting.

The site works fine on my localhost server.

This is the rough code.

    public Promotion retrievePromotion()
    {
        int promotionID = 0;
        string promotionTitle = &quot;&quot;;
        string promotionUrl = &quot;&quot;;
        Promotion promotion = null;
        SqlOpenConnection();
        SqlCommand sql = SqlCommandConnection();

        sql.CommandText = &quot;SELECT TOP 1 PromotionID, PromotionTitle, PromotionURL FROM Promotion&quot;;

        SqlDataReader dr = sql.ExecuteReader();
        while (dr.Read())
        {
            promotionID = DB2int(dr[&quot;PromotionID&quot;]);
            promotionTitle = DB2string(dr[&quot;PromotionTitle&quot;]);
            promotionUrl = DB2string(dr[&quot;PromotionURL&quot;]);
            promotion = new Promotion(promotionID, promotionTitle, promotionUrl);
        }
        dr.Dispose();
        sql.Dispose();
        CloseConnection();
        return promotion;
    }

May I know what might have gone wrong and how do I fix it?

Edit: Not to forget, my connection string and connection are both in static. I believe this is the reason. Please advise.

    public static string conString = ConfigurationManager.ConnectionStrings[&quot;dbConnection&quot;].ConnectionString;
    public static SqlConnection conn = null;

ExecuteReader requires an open and available Connection. The connection&#39;s current state is Connecting

In order to avoid the overhead of establishing a new connection each time a query needs fired against MySQL, there are two options available:

1. Persistent connections, whereby a new connection is requested a check is made to see if an &#39;identical&#39; connection is already open and if so use it. 
2. Connection pooling, whereby the client maintains a pool of connections, so that each thread that needs to use a connection will check out one from the pool and return it back to the pool when done. 

So, if I have a multi-threaded server application expected to handle thousands of requests per second, and each thread needs to fire a query against the database, then what is a better option? 

From my understanding, With persistent connections, all the threads in my application will try and use the same persistent connection to the database because they all are using identical connections. So it is one connection shared across multiple application threads - as a result the requests will block on the database side soon. 

If I use a connection pooling mechanism, I will have all application threads share a pool of connections. So there is less possibility of a blocking request. However, with connection pooling, should an application thread wait to acquire a connection from the pool or should it send a request on the connections in the pool anyway in a round-robin manner, and let the queuing if any, happen on the database? 

MySQL - Persistent connection vs connection pooling

How can i perform a remote connect to ClearDB MySQL database on heroku using for example MySQL Query Browser. Where to get url, port, login and password?

Content Type	Original Author	Original Content on Stackoverflow
Question	John K	View Question on Stackoverflow
Solution 1 - Sql Server-2008	vcsjones	View Answer on Stackoverflow
Solution 2 - Sql Server-2008	DDay	View Answer on Stackoverflow
Solution 3 - Sql Server-2008	MarcelO	View Answer on Stackoverflow

Where are SQL Server connection attempts logged?

Sql Server-2008 Problem Overview

Sql Server-2008 Solutions

Solution 1 - Sql Server-2008

Solution 2 - Sql Server-2008

Solution 3 - Sql Server-2008

How to remove all duplicate items from a list

Returning an empty array

Attributions