knows what I did, but it's now gone. I most probably have deleted it, or maybe detached and never reatached. The fact is, I don't care about it and its data anymore. I want it gone. There are no files of it anywhere. I just want to create a new blank database with a same name. And I can't. It says "database already exists". But running sp_databases doesn't show it. Also if I try to "drop database" it says there's no such database in the system catalogue. Could you please help me do something? Mantvydashi Mantvydas, it sometimes happens that db names become '', thats' to say the relative column [name] have been cleared o not ask why.. I do not know try executing SELECT * FROM master..sysdatabases and see if you
without using the DataAdapter does it work. Is there a way around this? Chuck Hello, I am using SQL Express and Visual Studion 2003. I am trying to DROP DATABASE Customers using a Stored Procedure and also using ADO.NET. Query: IF EXISTS (SELECT name FROM sys.databases WHERE name = Customers) DROP DATABASE [Customers] ConnectionString: RemoveConnectionString = "Server = MyComputer\SQLExpress;UID=sa; Password=MyPassword;Initial Catalog=master" Everytime I call the procedure or ExecuteNonQuery I get: Cannot Drop Database 'Customers' because database is currently in use. by sqlclient.Data.SqlClient.SqlCommand.ExecuteNonQuery() I have no problems Dropping the Tables. ChuckThe most likely cause is that the closed/disposed connection is pooled
I've logged into the sharepoint instance using osql and I have tried running sp_resetstatus, but it it neither gives me an error or a confirmation that the status has been reset. Restarting the sharepoint service does not solve the problem. I'm not familiar with SQL Server, MSDE or the technical admin of sharepoint. The OS is Small Business Server 2003 Premium Edition.hi Dave, resetting the database status usually does not help, as this task is used along with successive tasks to check database integrity, torn pages, and data integrity if you have a valid database backup you should go with that instead of "hacking" the current corrupted one you'll loosing far more less time :)Hi Andrea Thanks for reply. We don't have a decent backup
p, 107, 42, @b exec sp_trace_setevent @p, 107, 11, @b exec sp_trace_setevent @p, 107, 23, @b exec sp_trace_setstatus @p, 1 while 1=1 begin insert into tempdb..ChangePasswordTraceTable(columnid, length, data) exec sp_trace_getdata @p, 2 end But either password was changed or was not (wrong old password) I have same value of data in row where columnid = 23 (success). Same problem when I try to log sp_addlogin. Why I receive same value of success?hi, what are you trying to do?I try to audit actions with password. I want to add information about all successful executions of sp_password. To do this I add trigger to tempdb..ChangePasswordTraceTable like this: CREATE TRIGGER tr_ChangePasswordTraceTable ON tempdb..ChangePasswordTraceTable INSTEAD OF INSERT AS begin declare @u nvarchar
2005 Express edition will be adequate for our needs. I want to check that the size of the databases I'm using are less than 4GB. How do I do that? I've looked around in SQL Server Enterprise Manager but I don't see file sizes shown anywhere.In Query Analyzer, try this: EXECUTE sp_spaceused In Enterprise Mangler, right click on the database, choose [Properties]. The file size is in the middle of the page on the [General] tab, Also, you can use Windows Explorer to view the database file. (It's not a precise measure, but it's close.)Thanks, Arnie - I don't know why I couldn't find that :( My db's are all in the single-digit MB size, so I guess that the 4GB limit of the Express version won't be an issue. And of course, if
is an odd one.Out of curiousity, does the MSDE "server" install or just the client tools? If the MSDE server installs, that seems rather biased, as I was unable to load the Oracle 9i(x) client tools on the web edition. Might be they don't have an RTM SP3 install disk for MSDE, which Win2K3 barks for during install? While I didn't have a problem loading RTM SQL Server EE and subsequently loading SP3 on Win2K3 EE, I can't speak for the web edition. Morgan webHi, It's not biased against Oracle, Microsoft knows about this issue. If you want the Oracle client tools, check here: http://groups.google.com/groups?dq=&hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=d6eb7662.0306030902.24212ee3%40posting.google.com
that needs MSDE2000 to be intalled as prerequisit. while building the setup project of my application Using InstallSheild X i've integrated a prerequisit rule which consists about oif MSDE2000 is not installed on the system to stop the intallation ad display an error message. the problem is since we intalled the application on windows 2003 server or Windows 2000 professional the rule worked as it should be But while installing the application on windows XP pro the error message apears even if MSDE2000 is intalled does anybody faced such a problem ? can i heve help to know how to solve it? thank you.hi, unfortunately this is a little bit hard to answer, without knowing the method you adopted to check for MSDE presence Well the issue is that in install sheild i can create my own
be hit by Internet worms like Slammer and Saphire virus and to increase security, so that Microsoft decided to default for disabling SuperSockets Network Protocols on new MSDE 2000 installation. Instances of SQL Server 2000 SP3a or MSDE 2000 SP3a will stop listening on UDP port 1434 when they are configured to not listen on any network protocols. This will stop enlisting these servers. The latest problem has been added by Windows XP service pack 2, which implements a strong protection of the local computer closing all ports for incoming and outgoing connections, requiring to manually open the desired IP port in order to allow external remote connectionsHello, My enabled networks protocols are Named pipes and Tcp/IP With EnumSQLSvr.exe, it doesn't work (when I remove the network
amp;gt; Hi Andrea, Yes, I did install MSDE while logged in as 'Administrator'. If I change the 'Power Users' to 'Administrators' group then I do not have this problem. But, I do not want to change all 'Power Users' to 'Administrator'. What permissions do you have for the MSSQL directory? My installation only set permission for 'Administrators'. If report back latter. Charleshi Charles, and it's correct :D no one but sysadmins should touch my system and database files I really do not understand while an ISP dialer should try installing MSDE if MSDE has been already installed the dialer could, eventually, make use of it, but not install a separate instance for sure..
Manager or any other utility. Thanks in advance and happy new year!hi, it is not possible to password protect single databases.. as you already probably know, the security architecture of SQL Server is not implemented that way you have a 2 phases authentication mechanism of logging to SQL Server, where it is evaluated wheter or not the specified login (both WinNT and SQL Server) can access the server instance, and then whether or not a valid database user is available in your db for the corresponding login.. if the database user "Guest" is available and no other db user is mapped to the corresponding login, that "Guest" db user will be used and applied, while no db access is permitted otherway but the method is instance centric and not
with my SQL server desktop installation. The message says corrupt or did not install properly & that I shoud uninstall & try again. The problem is that there is no place to uninstall that i can find. What should I do? I am using win xp pro sp2 and was trying to install it with business contact manager. It is not listed in the add/remove programs. I can uninstall Business contact manager, but not the sql desktop engine.hi, to manually uninstall MSDE you can have a look at http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q290991 ..That seemed to have worked.
so good, no errors here in my Command Window. I run net start mssql$netsdk and the message "MSSQL$NETSDK service is starting" appears. So far so good, no errors here in my Command Window. I reboot and my SQL Server icon appears in my tray icon in the lower right corner of my screen shows: Not Connected - \\ (is this a problem?) A note in the book says to make sure MSSQL$NETSDK service is started. I then proceed to install the sample database with my download of SQL2000SampleDb.msi In my black Command Window I type osql -E -S (local)\netsdk -i instnwnd.sql instead of the expected: (1 row affected) (1 row affected) (1 row affected) (1 row affected) etc .. I get just a lot of definitions of various -i's & -p's etc. I try to go into visual studio
well) is a "natural" state of life for data and databases and the "case" of your scenario is even wors as SQL Server is not involved at all, shutting down the service ad overwriting the database's files is performed at NTFS level (so adeguate permissions in this area is required) but, again, SQL Server is not involved.. it is later involved when restarting the service and starting the replaced database.. if something goes wrong in the NTFS operation, SQL Server could mark the database as suspect, but this is another story.. on the other hand, SQL Server 2005 introduces DDL triggers, http://msdn.microsoft.com/en-us/library/ms175941.aspx, but, again, the NTFS operation is out of control here as well..Andrea is right. SQL Server connot
What can I do if I failed to attach a database by EM? If the database is corrupted, any method to recover the database?hi Tony, if you have a clean backup, this usually is the best solution to go but, if the database can be saved, perhaps the advices by Jasper as per http://tinyurl.com/3pxhv can be helpfullHi, What is the error you are getting while attaching? Best option after a corrruption will be restoring from a good backup. If you do not have the backup try below:- 1. Execute DBCC CHECKDB('db_name','REPAIR_REBUILD') , this will corrupt minor issues. If you do not have any options try with Jaspers suggestion http://www.
old method, called SQL Server Authentication, requires a user name and password instead. There exists Mixed Mode Authentication, which will allow both, for instance in case you want to allow some user over the network to access some parts of a database, you first have to allow him to talk to SQL Server. Windows Authentication is the recommended mode, unless you have a reason to allow login by name and password. I'm rather new to all this, so some things >admin tools->services ?Mine is working OK now, thank you. It's Chris who is still having a problem.Hi, Appreciating your guys help on my case. I tried, althg still failed to install it. For the case, I’ll thinking to purchase a pc of Visual.net. have a nice day's. Chris ======= Chris
Hi I know I probably shouldn't be doing this but I have a table on an MSDE database that has a bigInt for the primary key. If I try to link to this table from Access (XP) using an ODBC connection the table links ok. The first time I opened the table it was ok. Since then the primary key data type is interpretted as text and all the records (though still there) have "#deleted" in each column of every row? I have tried relinking the table and still get he same problem. Any ideas whats going on? MarkHave you linked the table such that Access recognizes the primary key?Hi Monte As far as I know, yes? I wasn't prompted to select a primary key and looking at the table design from within access shows the primary key present but as text instead of number.
Hi Experts, I am currently having some problem after installing the MSDERelA version. I have performed an installation by running "setup SAPWD='password'". The installation was successful, but when I tried to connect from another machine by using the GUI it doesn't connect. The error message prompt was "SERVERNAME-Specified SQL server not found.ConnectionOpen(Connect())". I could see the server from the server list. What could the possible error. Many thanks in advance.By default, network connections are disabled unless you specify DISABLENETWORKPROTOCOLS=0 in the command line or SETUP.INI To activate these after an install, run SVRNETCN and enable the appropriate protocols.
Need an affordable solution to compare two databases or check the database change released.I not sure what your definition of affordable is but Red Gate has a very good comparison tool. I use it daily and swear by it. http://www.red-gate.com Jim
all' USE master GO DROP DATABASE TEST as you can see you can do it by hand, changing each column setting or doing something handy like letting SQL Server writing the actual Transact-SQL ALTER TABLE ALTER COLUMN statements you have to execute to perform the desired result the actual statements will be --- ALTER TABLE dbo.TestTB ALTER COLUMN name varchar(10) COLLATE Latin1_General_CS_AI_KS_WS NULL ALTER TABLE dbo.TestTB2 ALTER COLUMN name varchar(10) COLLATE Latin1_General_CS_AI_KS_WS NULL I do not love cursors, but this is one of the cases I could not find another way to have the job done by the way, the problem you describe (it's no more a problem in SQL Server 2000) is one of the reasons I do not deploy my databases using restore and/or sp_attach_db features
\SHAREPOINT, Line 1 Line 1: Incorrect syntax near 'STS_svr-apps-1_1414639615'. 1> quit If I do: SELECT name FROM master..sysdatabases ORDER BY name It lists: master model msdb STS_Config STS_svr-apps-1_1414639615 tempdb name?? Can someone suggest a way around this? Chris Moonhi Chris, SQL Server (and MSDE) support this kind of identifier.. executing SET NOCOUNT ON SELECT @@VERSION GO CREATE DATABASE [STS_svr-apps-1_1414639615]; GO USE [STS_svr-apps-1_1414639615]; GO USE master ; GO DROP DATABASE [STS_svr-apps-1_1414639615]; reports --------------------------------------------------- Microsoft SQL Server 2000 - 8.00.2162 (Intel X86) Copyright (c) 1988-2003 Microsoft Corporation Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 2) The CREATE
A PC at work was renamed recently and now our website doesn't work anymore. I think the problem is due to the Login for ASPNET. Which reads: \ASPNET I'm using Web Data Administrator but I don't see anyway to rename the login or delete it. When I try to add \ASPNET, I get an error saying that it can't find the user. Is this a case where MSDE will have to be uninstalled and reinstalled? Or is there another way to fix it?Is there local user named ASPNET? JimYes there is. Which is why I'm not understanding the error.
Not rs Is Nothing Then While Not rs.EOF If StrComp(rs("dbname"), sCatalog, vbTextCompare) = 0 Then con.Execute "kill " & rs("spid") ' Ignore errors Err.Clear End If rs.MoveNext Wend rs.Close End If con.Execute "sp_detach_db('" & sCatalog & "', 'true')", , adCmdStoredProc ''''''''$$$$$$$$$$$$$$$Hello, Do the below command from ISQL window USE MASTER GO ALTER DATABASE <DBNAME> Set SINGLE_USER with Rollback Immediate GO DROP Database <dbname> Hari
Hi NG, the install.log is a very large textfile. If the Install rolls back, where can I find the reason of the error? Rolandhi Roland, if you specified the /L*v "C:\file.txt" parameter, you can inspect for RETURN VALUE 3 entries, that indicate a failing issue.. about 15/20 lines before and/or after each entry you'll fine some (sometime cryptic) description of the problem
Hello, I saw that there was a topic posted on this before but I am unable to view it now. I am having problems connecting to a remote MSDE database. My MSDE database is installed on a Windows 2003 Small Business Server standard edition and I can make an ODBC connection to it on the local server. When I attempt to access the database remotely, I get a message stating that the server is not available. Does anyone know how to fix this? - Stewarthi Stewart, probably you are referencing http://support.microsoft.com/default.aspx?scid=kb;en-us;328306&Product=sql KB article..This is the one that worked. http://support.microsoft.com/kb/814130
box for specifying the remove files older than is blank, and the default timeframe is 0, and the Backup file extension is blank. On other SQL servers, by default these locations show the drop-down as Week(s), the timeframe as 4, and the extention as BAK. If I continue setting up the maintenance plan, when I get to the Specify Transaction Log Backup Disk Directory, I'll run into the same problem as the Database (blank and 0 for the same fields). In any event it will allow me to setup the plans, however I'd like to be able to set the remove feature and I can't Any ideas?hi, this is a long nightmare with Maintenance Plan withard (which actually is very buggy) please have a look at http://groups.google.it/groups?as_q=maintenance+files+older&num=10&
I am using MSDE 2000 , I would like to get information from master..sysprocesses table to see what programs are connected to the database. I would to verfy how many times a particular program is connected to the database Lets say i would like to check the number of times program X is connected to the database. The select statement is like Select * from master..sysprocesses where program_name ='X' but unfortunately whenever i connect using programX , the Program_name field is Empty. So I am unable to compare against the program name. Is there any other table from which i can gather consistent information? Any workaround would also be welcome. Prakashhi Prakash, ADO automatically provides this kind of information to SQL Server, where SQL-DMO requires you to explicitely set
2000 (any sp) with MDAC 2.8. now we try to port our application to XP SP2 and have trouble. Its only one process connecting to the msde instance on the same system with only one active sql-statement at any point in time (ie. no concurrency). after several queries the sql-server does not answer anymore. after starting our db-interface process of the sql-server it all works again. Looks like an odbc problem to me We log into the server at startup time and keep this connection for eternity. After each statement we issue a 'rollback' to have a defined / clean state for the next query. (those queries may of course perform a commit to change data etc.) any idea whats going wrong?hi, I do not understand your design you keep an open connection "eternaly", and
Hi, I experience a problem after installing "SQL Server 2005 Books On Line" (December 2005) on my notebook, which have also an installation of SQL Server 2005 Express Edition. Management Studio Express Edition (November CTP) don't start If I try to open it, nothing happens. Anyone can help me?I find the solution: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=57857
Hello, A customer of mine has a problem with my database : When he makes a backup of my database with Veritas Backup Execute, my database is marked as suspect, (and my application can not start anymore). I know that I can use "sp_resetstatus 'DBName'" to restore the normal status, then stop and restart the server, but is there a tip to avoid to flag the database as suspect? Is there an option in Veritasbackup not to flag the database as suspect? Marc Allard AllcompHi, Can you check the SQL Server error log file for more informations for the cause for Suspect status.base on that we will be able to rectify the issue. Hari SQL Server MVPHello, I have seen that error message in the Error log process cannot access the file because it is being used by another process.)
software, MSDE Manager). In the SQL Server Service Manager, I effectivly see both servers. But in the first PC, where the SQL Server 2000 is installed, I can't connect to the MSDE. I don't see it in the Service Manager neither. Both PCs are logged with the same user/password with administrative privileges. I use NT authentication. From the MSDE, as I said, it's ok (although I can't log in the database with the 'SA' user, but I don't care for now), but from the SQL Server, there is no way I can connect to the MSDE. Alway get: "SQL Server does not exist or access denied". Any idea anyone?? thanks a lot for your help and time, I'd really appreciate. Dominic Gagné, Montréal, Qc, CanadaHi Greg, This worked for me. Thanks!. Jason
remote server through LAN and local IP I use SQL authentication and TCP/IP. Can someone sugest any soutions ? please Arthurhi Arthur, try having a look at http://blogs.msdn.com/sql_protocols/archive/2005/09/28/474698.aspx if helps..thank you Andrea - I got it fixed ArthurIt's always good form to tell the other newsgroup readers how the problem got fixed. That way we can all benefit from the solution. -- ____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP INETA Speaker www.betav.com/blog/billva www.betav.com Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ Visit www.hitchhikerguides
for my program to connect? Also, when I posted earlier, I was using msado 2.5 in my VB program. One of the things in the page you referenced mentioned that I needed at least version 2.6. I have 2.7 (and 2.8) on the machine where VB is running so I changed the reference to 2.7. That did not seems to make any difference however. Does that shed any light on the problem I might be up against?hi Martin, is this all there is or does it continue with " not associated with a trusted connection"? if this is the case, you have to enable Mixed security authentication.. .you can do that at install time unchecking the "hide advanced options" check box and allowing standard SQL Server logins in the wizard or, if you provide a set of command line parameters
testdb; END GO if exists(select 1 from master.dbo.sysdatabases where name = 'testdb') BEGIN use testdb; create table test ( [Id] [int] PRIMARY KEY IDENTITY (1, 1), [Name] [varchar] (255)); END ELSE PRINT 'error' GO if exists(select 1 from master.dbo.sysdatabases where name = 'testdb') BEGIN PRINT 'dropping Database .' USE master; DROP database testdb; END;Hi Andrea, I did have a play with the option you recommended before posting this message but my requirements are pretty different. Say if my big script was to be compressed the logic would be something as follows - DECLARE @new1 INT if not exists(select * from master.dbo.sysdatabases where name = 'testdb') BEGIN PRINT '
we have a pllication that uses MSDE databases, we send out updates we detach copy new file in and reattach. Problem: I have a couple of machine the MSDE server want allow the mdb files to attach. WE remove the current log files after the detatch. Almost like the machine does't know the SQL server exists anymore.Hi, Are you facing the issue to attach the database only with MDF? If yes then try to attach the database using below command:- EXEC sp_attach_single_file_db @dbname = 'DBNAME', @physname = 'd:\MSSQL\Data\DBNAME.mdf' Hari SQL Server MVPThe databases are attached when the program starts I get the following errors when the program tries to attach -2147024769 automation error- the specified proceedure not found
Hi Folks I am a newbie trying to attach an MSDE dbf from a whatever.mdf and whatever.ldf files. I keep getting an error that I can't attach the database to the same name as an existing database. Any help would be appreciated? DannyHello, Looks like the database name you specify in the atatch database is already in the same server instance. Execute SP_HELPDB and make sure that the database you specy is already not there. Hari
I have heard the answer before, but I do not remember and I cannot find it on Microsoft's website. Is the 2 GB limit per database or per server? Can I have multiple databases reach 2 GB on one MSDE server, or is the maximum for all databases on the server 2 GB? Thank you, MarcMarc, The limit is per database. Lars Broberg Elbe-Data AB http://www.elbe-data.se Remove "nothing." when replying to private e-mail!