Why in the hell doesn't SQL Server provide a facility for dropping an
active connection on an attached database in SQL Server Management
console? I can't detach an attached database because apparently there
is an active connection.
I know you can use SSEUTIL but it seems like kluge for a poorly
thought out function.
Crazy
Hi all (newbie @ asp.net)(oldie @ ASP 3)What is the purpose of using an attached MDF database files in the App_Data folder on a web site as to importing it into the SQL server directly or creating it on the SQL server. Does a mdf database attached file purely use the SQL server as a connection interface.Is it something similiar to DSN(ODBC) Connections for ms access databases.
View 2 Replies View Relatedhow can i attached MDF file only without .log and data only
View 6 Replies View RelatedHi,
I was trying to figure out how to back up a database that has been attached to the SQL Express instance. I found on the web this tutorial and tried to use it, however it seems like it cannot find the database. The DB is attached by the web application using AttachDbFilename in the connectionstring.
http://www.sqldbatips.com/showarticle.asp?ID=27
Any ideas?
thanks
Hello all,
I'm using SQL 2005 Express edition. I'm currently connecting to the database file directrly (from ASP.NET) and that db file is not attached to the server.
My question is this:
What is the difference ? Will the performance improve if the db is attached and I connect to it throught the server ? I'm really not sure what the difference is.
Thanks
Hey!
I need help getting all the users that are "attached" or "allowed" to access a given database and their login information. I do not know if this is even possible but I had tried to do my own research and cant figure it out/find any info on it. Any help is greatly appreciated!
Thank you!
-Teenprogrammer
I attached my database to SQL 2005 but it's showing up as read-only for some reason. I've attached this before just fine in another instance of the Vista OS. Why would it attach differently this time compared to all other times and show read only?
The .mdb and .ldf files are not read only if you right-click and check out their properties.
Hello all,
I'm using SQL 2005 Express edition. I'm currently connecting to the database file directrly (from ASP.NET) and that db file is not attached to the server.
My question is this:
What is the difference ? Will the performance improve if the db is attached and I connect to it throught the server ? I'm really not sure what the difference is.
Thanks
Hi All
I am in process of moving a SQL 2005 solution from a development box that used local storage to UAT environment with SAN attached storage. The solution uses database snapshots
The database files are on the SAN storage but during testing I was unable to create a Database snapshot on the SAN disk. Creating snapshots on the local disk worked fine.
Is their some restriction/problem in using the database snapshot technology with SAN storage?
Hello,
I was using a SQL Express database for some time now with no problems for a NUnit Test project. No, all of a sudden, I'm getting these problems:
DatabaseProviderTest.TestContainsUser : System.Data.SqlClient.SqlException : Cannot open user default database. Login failed.Login failed for user 'XXXXXX'.
For the SQLEXPRESS database, I checked and I am a user, and I gave myself every permission imaginable. The default database is master, so I don't know why I am getting this problem?
Any ideas? Thanks.
Hi allI have a question concerning sql database mdf files. In the old days I would user a ms access database. This file would be stored with the actual web files and would utilise a dsn connection. I have noted when designing with vwd 2005 express it allows you to use 2 methods of creating a mdf database. You can either create it as an attachment mdf or you can create it directly using sql manager. My question is, if you create the mdf database as an attachement file can you store it in the same manner as if you where using a ms access database, meaning can you store it with the web site's files so it uses the file storage allocated size and then create a connection similar to a dsn (but for sql) to the isp's sql engine or does it have to be uploaded to the isp' s sql server.The reason for this question is some of my customers do not want to pay the extra cost to have an sql allocation, however I do not want to go back to using old asp methods to create advanced sites as I prefer using stored procedures. Any help will be appreciated
View 4 Replies View RelatedI am supposed to be documenting the SQL Express 2005 SP2 installation and connection to our application on Vista. I have installed and uninstalled the application several times in trying to create the connection and document the process. I thought I was done but now the database will not attach because it thinks the databasse is already attached. The database is not attached. I even uninstalled it again thinking something was left over.
Among the possibilities are that the database we are trying to attach is from SQL Server 2003 on a Server 2003. I tend not to think it is a server issue but have no reason to make that statement.
I tend to think it is a Vista issue because I have encountered and circumvented several. Our applications work fine on the full version of SQL. I can connect my Vista computer to the servers without issue. We have no Vista Servers other than the workstation I am trying to get SQL Express running on.
I am about out of ideas. Any suggestion will be tried at this point. Brian
Hello,
In my vb.net application, I have an attached database called dbMaster with the following tables:
tblCustomer, tblProducts, tblUsers
In visual studio 2005, is it possible to publish only one table? My problem is, is that I want to send my end users updated data from my tblCustomer, and am finding that on my test laptop, if I publish dbMaster, it sends all tables when I just want one to be sent.
Thanks in advance.
Is it possible to try repair a database without being attached, giving the path to the files?
I'm trying to attach a database that i recover from a disk, and i'm not able to attach, gives me an error, saying i should do a db check.
Hi every body.
As you can see in the title I would like to import my tables from the data base which is in the SQL Server, to an mdf file which can be attached to the project.Can some one give an indice please?
Tran Backup failed. How to find the database for which Tran Backup failed as attached?
View 2 Replies View Relatedour clients have the flexibility to detach and attach databases (I know there are a lot of considerations around this but there is now way of changing it), once they attach a database we need to run some code to update a bunch of values in the database.
Other than creating a SQL Agent job are there any other options available to automatically execute a script once the database has been attached?
I downloaded the SR2 - major mistake Now I cannot get into the sample databases I have on my machine. I can attach to the Servers, but they have SR1 and now I guess if I would make changes from my SR2, then they will be screwed up.
My error when I try to log into my machine (adventure works) is:
TITLE: Connect to Server
------------------------------
Cannot connect to LND620JORRIT.
------------------------------
ADDITIONAL INFORMATION:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=2&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
Is there any way to reverse SR2? Should I reinstall SQL 2005?
Help!
Anke
Hi,You all may be knowing that Connection.isClosed() does not tells usif the underying DB connection is active or not; it only checks ifConnection.close() had been previously called or not.One sure shot way to find out this is by executing some dummy SELECTquery and catching it via SQLException.This could be done in various DB's as follows:SELECT * from 1 (MS SQL)SELECT * from DUAL(Oracle)My question is what if you use some other DB , which is not famous asthe above.This could still be achieved by creating dummy table with one columnand querying it. One pitfall of doing this approach is we may not havecreate permissions to create table. Even if we have permissions tocreate table, you need to do the following, if you need to check DBConnection every time.a) Create Tableb) Use SELECT queryc) Drop tableYou may ask me why we need to use drop table. This is because, we cannot create many tables and keep them alive if we were to check (DBConn) it for 100 times. One way is we can use IF NOT EXISTS along withCreate table. Unfortunately, this command is not supported by all DBvendors. So, this is ruled out.One more way of doing is writing simple stored procedure that returnsplain constant. Unfortunatley the syntax for Stored procedures isdifferent for different DB Vendors.So, do we have a correct way of finding if DB connection is active,that would work on all DB's ?Fortunately, there is a way to do this.We could use Connection.getMetaData().getTables(null,null,null, null).We could use this way as this would surely get the number of tablespresent at that moment. How many tables are present in a DB will notbe cached as this may change dynamically. One disadvantage of usingthis approach is performance. What if a DB has 1000 tables, it tries toget the names of 1000 tables and it is performance hit.Is there a solution for this?. Yes, we can use getTables method byinvoking only against the SYSTEM table types. I am sure any DB willnot have many system tables.So, our call would be,Conn.getMetaData().getTables(null,null,null,new String[]{"SYSTEMTABLE"});The above statement is expected to give whether connection is active;if connection is not active, then it throws SQLException. And best partis it will work on all DB Drivers.What if some JDBC driver does not implement the above getTables() call,then we would get some AbstractMethodError that can be caught usingLinkageError. So, finally code for checking if connection is active ornot is as follows:try {ResultSet rs = conn.getMetaData().getTables(null,null,null,newString[]{"SYSTEM TABLE"});} catch (SQLException e) {conn.close();// use try catch block here to catch SQLException forConn.close();//call to open new DB connection.getNewConnection();}catch(LinkageError e){conn.close();// use try catch block here to catch SQLException forConn.close();//call to open new DB connection.getNewConnection();}}This limitation (if it can be called) is going to be fixed for JDBC4.0 implemented drivers(if they implement it in right way).Any comments on this would be appreciated.Regards,Venkata Narayana
View 2 Replies View RelatedAny suggestions on how to create a report to show when someone first connected to SQL Server and when they last processed anything on SQL Server?
I wrote a query to check sysprocesses every 10 minutes, but it only reports that the person has a connection. It does not tell me if the connection is active. I thought I may need to look at processor and disk to see if those numbers change, but I'm not sure if that is the best approach. Any thoughts?
Thanks, Dave
In sql server 2005 i want to connect to active directory of exchange server ... there is one option in which we can search outlook but its not fruitful ... please guide ...
View 3 Replies View RelatedI'm trying to query against AD to grab some data. I've done this setup and got it to work at my location but can't get it working at one of my customers.
Per information I've found via this site I did the following:
Task: from SS2005, select data elements from Active Directory and populate in ODS (using an
SS2005 Package in SSIS)
I created a linked server on the MS2005
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'
I then created the following View (in the Master DB):
CREATE VIEW viewADContacts
AS
SELECT [NAME],MAIL
FROM OPENQUERY( ADSI,
'SELECT NAME, MAIL
FROM ''LDAP://#######/ DC=####,DC=###''
')
The View created w/ no errors
When I execute
select * from viewADContacts
I get this error:
Cannot fetch a row from OLE DB provider "ADSDSOObject" for linked server "ADSI".
Any thoughts on this error? Again, I did the exact same thing at my office (against my local AD) and it worked fine.
Thanks in advance
Tom
Hi,
Currently I am trying to connect to SQL Server 2005 via Excel VBA. I would like to create a connection to the server as I have previously done with my 2000 server. However, the diffence this time is that I am using Active Directory so there are no specific log-in's for SQL per se just Windows Users that are part of groups, any of which could use this spreadsheet. So where-as previously I included the username and password for SQL Server in the connection string I would now like to use the credentials currently logged onto the particular machine.
My previous code was this
Public Function getConnection() As ADODB.Connection
' Create a connection object.
Dim Conn As ADODB.Connection
Set Conn = New ADODB.Connection
' Provide the connection string.
Dim strConn As String
'Use the SQL Server OLE DB Provider.
strConn = "Network Library=DBMSSOCN;PROVIDER=SQLOLEDB;"
'Connect to the Pubs database on the local server.
strConn = strConn & "DATA SOURCE=SQL02,1433;INITIAL CATALOG=dbDataWareHouse;"
'Use a login.
strConn = strConn & " Uid=*******;Pwd=******;"
'Now open the connection.
Conn.ConnectionString = strConn
Conn.Open
Set getConnection = Conn
End Function
Would anyone be able to point me in the eight direction ? Your help would be much appreciated
Many Thanks
James
I am having weird problems using ADSI and SQL Server. Our local intranet is ASP with a SQL database on Windows 2003 Server. It uses Active Directory (via ADSI linked server) to get authenicate users, etc.
Every now and then (about once a month) the SQL connection to AD will "crash". The following error is what I see when it has crashed:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandText::Execute returned 0x80040e37].
Sometimes it will come back on its own later, others are solved by a server reboot. Here is how my SQL query looks:
SELECT adspath
FROM OPENQUERY(ADSI,
'<LDAP://DC=servername,DC=net>;(&(objectCategory=Person)(objectClass=user));adspath;subtree')
Rowset_1
WHERE (sn <> '')Where can I start to get to the bottom of this?
Hi,
After I drop the database, I backed up the master database, I recreate the database with new name. When I tried to do the import on the command level,
It gives me the error:
Attempt to locate entry in the sysdatabases for database 'db1' by name,
failed -- no entry found in that name.
What should I do? Thanks.
Hi friends,
How I can drop a database insatance from a different server ( data base is SQL Server 2005) from my code
Hoping a early reply ....
Thanks :)
I am trying to run a dymanic DROP DATABASE <dbname> command in a stored procedure called from ASP.NET. I typically connect from the .net using a SQL builtin account. I am getting this error:
System.Data.SqlClient.SqlException: Cannot drop the database 'db_testco', because it does not exist or you do not have permission.
The database is there for sure asI can browse it via the Studio manager. What permission do I need to set for my .net login to allow it to drop ? I tried adding the Delete permission to the database and still no go...
Setup: SQL 2005 SP2 2 Node cluster.
Thanks.
Is there any specific event i have to select in SQL profiler to monitor the process / user that kills active connection which is performing a batch data transfer. Any other alternative other than profiler that catches this (like XEvents)?
View 4 Replies View Relatedi would like to drop a distribution database after disabling both susbscriber and publisher .but i get a message saying distribution database is still in use .and as far as i can tell its there is no one using it .also stopped sql agent but no luck . and when i do select @@ servername i get servername is null how do i correct it .
thanks
Is there a way in SQL to drop any users from a database. I am trying to schedule some nightly DB maintenance and some users are still in there sometimes. I need to be able to kick the out to do dbcc checkdbs, etc. Is there a way for me to do this without stopping the SQL services?
View 3 Replies View RelatedHi,
i have a problem dropping a database?
I have a WinForm and i can create DB, create the Tables and drop the DB via buttons.
When i start myProgram it's no problem to drop the Database.
Then i can create the database without problems.
The tables also without problems.
Now if i try to drop my whole DB, i get the error message:
SqlException Handle :System.Data.SqlClient.SqlException: Cannot drop database "XtmsDb" because it is currently in use.
The code for dropping db:
public void deleteProjectDB()
{
conn.ConnectionString = "Data Source=TURM21;Initial Catalog=master;Integrated Security=SSPI;";
sqlStr = "Drop database XTmsDB";
try
{
System.Console.WriteLine("Opening Connection...");
conn.Open();
System.Console.WriteLine("Connection opened!!!");
SqlCommand cmd = new SqlCommand(sqlStr, conn);
cmd.ExecuteNonQuery();
System.Console.WriteLine("Database dropped!!!");
}
catch (SqlException dropEx)
{
System.Console.WriteLine("SqlException Handle :{0}", dropEx.ToString());
}
finally
{
conn.Close();
System.Console.WriteLine("Connection closed!!!");
}
}
The code for creating db:
public void createProjectDB()
{
conn.ConnectionString = "Data Source=TURM21;Initial Catalog=master;Integrated Security=SSPI;";
sqlStr = projectDBStrings.getDBCreateString();
try
{
System.Console.WriteLine("Opening Connection...");
conn.Open();
System.Console.WriteLine("Connection opened!!!");
SqlCommand cmd = new SqlCommand(sqlStr, conn);
cmd.ExecuteNonQuery();
System.Console.WriteLine("Database created!!!");
}
catch (Exception)
{
System.Console.WriteLine("Could not establish Connection!");
}
finally
{
conn.Close();
System.Console.WriteLine("Connection closed!!!");
}
}
The code for creating Tables:
public void createProjectTables()
{
conn.ConnectionString = "Data Source=TURM21;Initial Catalog=XtmsDb;Integrated Security=SSPI;";
ArrayList createList = projectDBStrings.fillProjectSchema();
SqlTransaction tx;
SqlCommand cmd = new SqlCommand("", conn);
IEnumerator createListEnum = createList.GetEnumerator();
try
{
System.Console.WriteLine("Opening Connection...");
conn.Open();
System.Console.WriteLine("Connection opened!!!");
try
{
while (createListEnum.MoveNext())
{
sqlStr = (String)createListEnum.Current.ToString();
tx = conn.BeginTransaction();
cmd.CommandText = sqlStr;
cmd.Transaction = tx;
cmd.ExecuteNonQuery();
tx.Commit();
}
System.Console.WriteLine("Schema created!!!");
}
catch (SqlException deleteEx)
{
System.Console.WriteLine("SqlException Handle :{0}", deleteEx.ToString());
}
}
catch (SqlException connectionEx)
{
System.Console.WriteLine("SqlConnection Handle : {0}", connectionEx.ToString());
}
finally
{
conn.Close();
System.Console.WriteLine("Connection closed!!!");
}
}
Who can help me?
Hello all,
I need to restore my database and I keep getting a database in use error. Can someone tell me the easiest way to drop all connections to a database and open it exclusively?
Also, I'm not sure where this connection is coming from, there is another database on the same machine that has user connected, can these connections be the problem?
Thanks
Kevin Kraus
I am trying to delete a database that currently subscribes for replication. The problem however, is that the publishing server no longer exists and I can't disable the subscription therefore I can't drop the database. When I try to disable the replication the server tries to communicate with the old server. Is there anyway to force the dropping of the database or to force the server to disable replication.
thanks in advance
Michael