SQLServerCentral / SQL Server 2005 / SQL Server Express

Failed to generate a user instance of SQL Server.

Tom Brown — Tue, 14 May 2013 02:53:06 GMT

My SqlExpress application was running fine, I renamed the ASPNETDB database (it was called [C:\inetpub\wwwroot\website1\App_Data\ASPNETDB.mdf] and the application ran ok. I then tried removing the Integrated Security=SSPI and using a username and password in the connection string. This also worked for a while, but I started getting this error:[code="other"]Failed to generate a user instance of SQL Server. Only an integrated connection can generate a user instance. The connection will be closed. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. [SqlException (0x80131904): Failed to generate a user instance of SQL Server. Only an integrated connection can generate a user instance. The connection will be closed.] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +5296071 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +558 System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +1682 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +69 System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +30... Yada Yada ...[/code]I have reverted to the connection string, but am still getting the error. I've tried User Instance=false - same error. Googling suggested removing the C:\Users\<username>\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS directory - same error. I can still connect to SQLEXPRESS using SSMS - its just the ADO.NET connection that fails.Also tried rebooting and restarting SQLEXPRESS. List of connection Strings tried: (none work)[code="other"]<add name="ConStr_Main" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=false" providerName="System.Data.SqlClient" /><add name="ConStr_Main" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient" /><add name="ConStr_Main" connectionString="data source=.\SQLEXPRESS;Uid=UserName;Pwd=Password;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=false" providerName="System.Data.SqlClient" /><add name="ConStr_Main" connectionString="data source=.\SQLEXPRESS;Uid=UserName;Pwd=Password;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient" />[/code]I am out of ideas.

Transaction Log Full in Simple Recovery mode

clare.xia — Fri, 04 Feb 2011 17:00:40 GMT

The transaction log in MSDB is 106% full and Log_Reuse_wait_Desc is 'Check Point'. The server is restarted every night and still has the same error. I cann't do backup or shrink log file because the Log is full. Any idea to solve the problem?Thanks

Cross-database query in SQLEXPRESS

Tom Brown — Mon, 13 May 2013 10:30:24 GMT

The site I am working works on SQL Server 2008, but is being adapted to work on SQLEXPRESS.I am using the Microsoft ASPNETDB.mdf for membership profile roles and personalization, and the main data is stored in another SQLEXPRESS database file: MAINDB.mdfin some stored procedures I need to check the roles of the current user - which is achieved on 2008 using cross database querying - I can't find a way to make this work in SQLEXPRESS.e.g.[code="sql"]USE [MAINDB]GOCREATE FUNCTION [dbo].uf_GetAccessLevel ( @MyUserID UniqueIdentifier, @OwnerID UniqueIdentifier )RETURNS INTBEGINDECLARE @Access INT;-- Users share a role IF EXISTS (SELECT * FROM aspnetDB.dbo.aspnet_UsersInRoles AS UIR JOIN aspnetDB.dbo.aspnet_UsersInRoles AS R2 ON UIR.RoleId = R2.RoleId WHERE UIR.UserId = @MyUserID AND R2.UserId = @OwnerID ) SET @Access = 10; ELSE -- public access only allowed SET @Access = 0; ENDRETURN @Access;END[/code]The function creates OK when connected but will not run:[code="plain"]Msg 208, Level 16, State 1, Line 1Invalid object name 'aspnetDB.dbo.aspnet_UsersInRoles'.[/code]My question is: Are cross-database queries allowed in SQLEXPRRESS? and if so, how do you reference one from the other, (both database files are in the same App_Data directory)

sql server 2005??????

hninousse — Mon, 29 Apr 2013 04:38:36 GMT

hello,i have big problems using sql server 2005 with access 2010, actually the data base was attached and detached from access 2007, and i was obliged to install another server in my personal computer with access 2010, since then i can't open my project using this one.i've created another sql instance "mycomuter\sqlexpress" and i switched to tcp/ip mode using port:1433.i have no problem with attaching my data base but i can't open it on access 2010.it says : " [DBNETLIB][ConnectionOpen(Connect())."....what should i do , I've tried everything but i can't fix it.i'll appreciate your help.thx.

SQL Server 2012 Instances

Sikhu — Thu, 25 Apr 2013 03:57:14 GMT

TITLE: Microsoft SQL Server 2012 Service Pack 1 Setup------------------------------The following error has occurred:Cannot create a new Service Broker in read-only database "msdb".ALTER DATABASE statement failed.For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=9775&ProdVer=11.00.3128&EvtType=0x7590AFD6%25400x9A41B222------------------------------BUTTONS:OK------------------------------

SP4 on Express

sqlquest2575 — Mon, 26 Sep 2011 10:15:34 GMT

Hello Friends, I want to update sp4 on SQLExpress 2005 edition. Do I need to reinstall the SQL server Express edition in order to update the patch on this one? Please help me on this one.When I go to the following link I get a couple of download options. I am not sure which one to choose.http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=184#system-requirementsFile Name Size SQLEXPR.EXE 54.0 MB Download SQLEXPR_ADV.EXE 254.0 MB Download SQLEXPR_TOOLKIT.EXE 224.0 MB Download SQLEXPR32.EXE 35.0 MB Download

Windows Management Instrumentation (WMI) service fails

kevinsql7 — Tue, 16 Apr 2013 13:08:01 GMT

Please help. We are running Windows XP SP3 with SQL Server Express 2005 installed. When we try to do a new install of SQL Server 2008 Express it fails with "Windows Management Instrumentation (WMI) service fails" message. The "Windows Management Instrumentation (WMI) service cannot be started" is also part of the message. We have tried various batch scripts (posted on the internet) and none seems to have worked. Does anyone know of a proven fix for this error? Or, if there is a fix provided on Microsoft's website?Below are some erros we received when running one of the batch scripts:---- Net stop has issue.--Microsoft Windows XP [Version 5.1.2600](C) Copyright 1985-2001 Microsoft Corp.H:\>%systemdrive%C:\>cd "%windir%\temp"C:\WINDOWS\Temp>net stop winmgmt /yThe following services are dependent on the Windows Management Instrumentation service.Stopping the Windows Management Instrumentation service will also stop these services. Windows Firewall/Internet Connection Sharing (ICS) Desktop Authority CBM ServiceThe Windows Firewall/Internet Connection Sharing (ICS) service was stopped successfully.The Desktop Authority CBM Service service is stopping.The Desktop Authority CBM Service service was stopped successfully.System error 5 has occurred.Access is denied.C:\WINDOWS\Temp>---- Bottom of the Output Bat file….--An error occurred while opening the namespace for object 1 defined on lines 16 - 55:Error Number: 0x80070005, Facility: Win32Description: Access is denied.Compiler returned error 0x80070005C:\WINDOWS\system32\wbem>mofcomp POLPROU.MOFMicrosoft (R) 32-bit MOF Compiler Version 5.1.2600.5512Copyright (c) Microsoft Corp. 1997-2001. All rights reserved.Parsing MOF file: POLPROU.MOFMOF file has been successfully parsedStoring data in the repository...An error occurred while opening the namespace for object 1 defined on lines 16 - 55:Error Number: 0x80070005, Facility: Win32Description: Access is denied.Compiler returned error 0x80070005C:\WINDOWS\system32\wbem>mofcomp regevent.mofMicrosoft (R) 32-bit MOF Compiler Version 5.1.2600.5512Copyright (c) Microsoft Corp. 1997-2001. All rights reserved.Parsing MOF file: regevent.mofMOF file has been successfully parsedStoring data in the repository...An error occurred while opening the namespace for object 1 defined on lines 6 -8:Error Number: 0x80070005, Facility: Win32Description: Access is denied.Compiler returned error 0x80070005C:\WINDOWS\system32\wbem>mofcomp rsop.mofMicrosoft (R) 32-bit MOF Compiler Version 5.1.2600.5512Copyright (c) Microsoft Corp. 1997-2001. All rights reserved.Parsing MOF file: rsop.mofMOF file has been successfully parsedStoring data in the repository...An error occurred while opening the namespace for object 1 defined on lines 5 -8:Error Number: 0x80070005, Facility: Win32Description: Access is denied.Compiler returned error 0x80070005C:\WINDOWS\system32\wbem>mofcomp scersop.mofMicrosoft (R) 32-bit MOF Compiler Version 5.1.2600.5512Copyright (c) Microsoft Corp. 1997-2001. All rights reserved.Parsing MOF file: scersop.mofMOF file has been successfully parsedStoring data in the repository...An error occurred while opening the namespace for object 1 defined on lines 10 - 13:Error Number: 0x80070005, Facility: Win32Description: Access is denied.Compiler returned error 0x80070005C:\WINDOWS\system32\wbem>mofcomp scm.mofMicrosoft (R) 32-bit MOF Compiler Version 5.1.2600.5512Copyright (c) Microsoft Corp. 1997-2001. All rights reserved.Parsing MOF file: scm.mofMOF file has been successfully parsedStoring data in the repository...An error occurred while opening the namespace for object 1 defined on lines 8 -10:Error Number: 0x80070005, Facility: Win32Description: Access is denied.Compiler returned error 0x80070005C:\WINDOWS\system32\wbem>mofcomp scrcons.mofMicrosoft (R) 32-bit MOF Compiler Version 5.1.2600.5512Copyright (c) Microsoft Corp. 1997-2001. All rights reserved.Parsing MOF file: scrcons.mofMOF file has been successfully parsedStoring data in the repository...An error occurred while opening the namespace for object 1 defined on lines 4 -11:Error Number: 0x80070005, Facility: Win32Description: Access is denied.Compiler returned error 0x80070005C:\WINDOWS\system32\wbem>mofcomp secrcw32.mofMicrosoft (R) 32-bit MOF Compiler Version 5.1.2600.5512Copyright (c) Microsoft Corp. 1997-2001. All rights reserved.Parsing MOF file: secrcw32.mofMOF file has been successfully parsedStoring data in the repository...An error occurred while opening the namespace for object 1 defined on lines 7 -14:Error Number: 0x80070005, Facility: Win32Description: Access is denied.Compiler returned error 0x80070005C:\WINDOWS\system32\wbem>mofcomp smtpcons.mofMicrosoft (R) 32-bit MOF Compiler Version 5.1.2600.5512Copyright (c) Microsoft Corp. 1997-2001. All rights reserved.Parsing MOF file: smtpcons.mofMOF file has been successfully parsedStoring data in the repository...An error occurred while opening the namespace for object 1 defined on lines 4 -16:Error Number: 0x80070005, Facility: Win32Description: Access is denied.Compiler returned error 0x80070005C:\WINDOWS\system32\wbem>mofcomp sr.mofMicrosoft (R) 32-bit MOF Compiler Version 5.1.2600.5512Copyright (c) Microsoft Corp. 1997-2001. All rights reserved.Parsing MOF file: sr.mofMOF file has been successfully parsedStoring data in the repository...An error occurred while opening the namespace for object 1 defined on lines 25 - 31:Error Number: 0x80070005, Facility: Win32Description: Access is denied.Compiler returned error 0x80070005C:\WINDOWS\system32\wbem>mofcomp subscrpt.mofMicrosoft (R) 32-bit MOF Compiler Version 5.1.2600.5512Copyright (c) Microsoft Corp. 1997-2001. All rights reserved.Parsing MOF file: subscrpt.mofMOF file has been successfully parsedStoring data in the repository...An error occurred while opening the namespace for object 1 defined on lines 6 -9:Error Number: 0x80070005, Facility: Win32Description: Access is denied.Compiler returned error 0x80070005C:\WINDOWS\system32\wbem>mofcomp system.mofMicrosoft (R) 32-bit MOF Compiler Version 5.1.2600.5512Copyright (c) Microsoft Corp. 1997-2001. All rights reserved.Parsing MOF file: system.mofMOF file has been successfully parsedStoring data in the repository...An error occurred while opening the namespace for object 1 defined on lines 5 -10:Error Number: 0x80070005, Facility: Win32Description: Access is denied.Compiler returned error 0x80070005C:\WINDOWS\system32\wbem>mofcomp tmplprov.mofMicrosoft (R) 32-bit MOF Compiler Version 5.1.2600.5512Copyright (c) Microsoft Corp. 1997-2001. All rights reserved.Parsing MOF file: tmplprov.mofMOF file has been successfully parsedStoring data in the repository...An error occurred while opening the namespace for object 1 defined on lines 4 -12:Error Number: 0x80070005, Facility: Win32Description: Access is denied.Compiler returned error 0x80070005C:\WINDOWS\system32\wbem>mofcomp trnsprov.mofMicrosoft (R) 32-bit MOF Compiler Version 5.1.2600.5512Copyright (c) Microsoft Corp. 1997-2001. All rights reserved.Parsing MOF file: trnsprov.mofMOF file has been successfully parsedStoring data in the repository...An error occurred while opening the namespace for object 1 defined on lines 4 -6:Error Number: 0x80070005, Facility: Win32Description: Access is denied.Compiler returned error 0x80070005C:\WINDOWS\system32\wbem>mofcomp tscfgwmi.mofMicrosoft (R) 32-bit MOF Compiler Version 5.1.2600.5512Copyright (c) Microsoft Corp. 1997-2001. All rights reserved.Parsing MOF file: tscfgwmi.mofMOF file has been successfully parsedStoring data in the repository...An error occurred while opening the namespace for object 1:Error Number: 0x80070005, Facility: Win32Description: Access is denied.Compiler returned error 0x80070005C:\WINDOWS\system32\wbem>mofcomp updprov.mofMicrosoft (R) 32-bit MOF Compiler Version 5.1.2600.5512Copyright (c) Microsoft Corp. 1997-2001. All rights reserved.Parsing MOF file: updprov.mofMOF file has been successfully parsedStoring data in the repository...An error occurred while opening the namespace for object 1 defined on lines 4 -14:Error Number: 0x80070005, Facility: Win32Description: Access is denied.Compiler returned error 0x80070005C:\WINDOWS\system32\wbem>mofcomp wbemcons.mofMicrosoft (R) 32-bit MOF Compiler Version 5.1.2600.5512Copyright (c) Microsoft Corp. 1997-2001. All rights reserved.Parsing MOF file: wbemcons.mofMOF file has been successfully parsedStoring data in the repository...An error occurred while opening the namespace for object 1 defined on lines 3 -10:Error Number: 0x80070005, Facility: Win32Description: Access is denied.Compiler returned error 0x80070005C:\WINDOWS\system32\wbem>mofcomp whqlprov.mofMicrosoft (R) 32-bit MOF Compiler Version 5.1.2600.5512Copyright (c) Microsoft Corp. 1997-2001. All rights reserved.Parsing MOF file: whqlprov.mofMOF file has been successfully parsedStoring data in the repository...An error occurred while opening the namespace for object 1 defined on lines 14 - 21:Error Number: 0x80070005, Facility: Win32Description: Access is denied.Compiler returned error 0x80070005C:\WINDOWS\system32\wbem>mofcomp wmi.mofMicrosoft (R) 32-bit MOF Compiler Version 5.1.2600.5512Copyright (c) Microsoft Corp. 1997-2001. All rights reserved.Parsing MOF file: wmi.mofMOF file has been successfully parsedStoring data in the repository...An error occurred while opening the namespace for object 1 defined on lines 8 -11:Error Number: 0x80070005, Facility: Win32Description: Access is denied.Compiler returned error 0x80070005C:\WINDOWS\system32\wbem>mofcomp wmipcima.mofMicrosoft (R) 32-bit MOF Compiler Version 5.1.2600.5512Copyright (c) Microsoft Corp. 1997-2001. All rights reserved.Parsing MOF file: wmipcima.mofMOF file has been successfully parsedStoring data in the repository...An error occurred while opening the namespace for object 1 defined on lines 7 -12:Error Number: 0x80070005, Facility: Win32Description: Access is denied.Compiler returned error 0x80070005C:\WINDOWS\system32\wbem>mofcomp wmipdskq.mofMicrosoft (R) 32-bit MOF Compiler Version 5.1.2600.5512Copyright (c) Microsoft Corp. 1997-2001. All rights reserved.Parsing MOF file: wmipdskq.mofMOF file has been successfully parsedStoring data in the repository...An error occurred while opening the namespace for object 1 defined on lines 7 -12:Error Number: 0x80070005, Facility: Win32Description: Access is denied.Compiler returned error 0x80070005C:\WINDOWS\system32\wbem>mofcomp wmipicmp.mofMicrosoft (R) 32-bit MOF Compiler Version 5.1.2600.5512Copyright (c) Microsoft Corp. 1997-2001. All rights reserved.Parsing MOF file: wmipicmp.mofMOF file has been successfully parsedStoring data in the repository...An error occurred while opening the namespace for object 1:Error Number: 0x80070005, Facility: Win32Description: Access is denied.Compiler returned error 0x80070005C:\WINDOWS\system32\wbem>mofcomp wmipiprt.mofMicrosoft (R) 32-bit MOF Compiler Version 5.1.2600.5512Copyright (c) Microsoft Corp. 1997-2001. All rights reserved.Parsing MOF file: wmipiprt.mofMOF file has been successfully parsedStoring data in the repository...An error occurred while opening the namespace for object 1 defined on lines 7 -12:

SQL Server and file permissions

khoi — Mon, 15 Apr 2013 16:06:02 GMT

Hi,I'm trying to make my app work happily under Windows 7 (and 8) UAC. Currently, the app uses SQL Server 2005 Express as the backend DB. I have built a feature for backing up the database. This is nothing fancy, but just implementing this process: detaching the DB files (.mdf and .ldf), make a copy of the files, add the copies to a ZIP archive, then re-attach the original files.Initially, before the DB files are attached to the SQL Server instance, in source code, I had made sure that the files have "Everyone - Full Control" in their ACLs. But once they are attached, SQL Server process takes over, and removes "Everyone - Full Control". Once the files are detached, "Everyone - Full Control" is not added back into their ACLs.Does anyone know why this happens? And how and I stop it from happening? The problem arises when after detaching the DB files, I have to add them to a ZIP archive. Running as a standard user on Windows 7, with UAC enabled, UAC will not allow me to add these files to a ZIP archive, nor copy them to a different folder. Also, I cannot manually add "Everyone - Full Control" to their ACLs either. I received this error when attempting to modify their ACLs: “Attempted to perform an unauthorized operation”.Please help. Thanks.

Unspecified error MS visual database tools

rafaelbonfa — Tue, 30 Oct 2007 08:39:36 GMT

Hi, i am using sql server management studio 2005 express, and every time i try to open a table in a database i receive this message "Unspecified Error (ms visual database tools)" And i can't open the table. Any one can help me? What could be this problem?Thank you.

BCP error 'Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file'

tony.walsh — Mon, 18 Mar 2013 04:15:02 GMT

The code is designed to geneate a csv file from a query using BCP. It works okay when run against a database on my PC. But I get the error when running it againast a database on a separate server. @Database varchar(20), @Filepath varchar(50), @Filename varchar(30), @Username varchar(10), @Password varchar(10), @Server varchar(50), @Query varchar(1000) /* -- works fine with these settings set @Database = 'BPC' set @Username = 'fw' set @Password = 'fw' set @Server = 'TONY_DESKTOP\SQLEXPRESS' */ -- fails for these settings set @Filepath = 'C:\Development\GENERAL\Childrens_Index\' set @Database = 'reports_worcs' set @Username = 'fw' set @Password = 'd3v3lop' set @Server = 'ss-lon1.dev.corelogic.local' set @Filename = 'Address_YP_'+ @v_id+'_'+replace(replace(CONVERT(VARCHAR(19), @v_processed_on, 120),':',''),' ','_') set @Query = 'master.dbo.xp_cmdshell ''bcp "select * from ' + @Database + '..ci_address_details where address_id in ('+'''''ADDRESS_ID'''''+',''''' + @v_id + ''''')" queryout ' + @Filepath + @Filename + '.csv' + ' -U' + @Username + ' -P' + @Password + ' -c -t"|" -S' + @Server + ''''; exec (@Query);

Remove Time from DateTime

Scott Anderson #2 — Fri, 08 Feb 2013 14:49:48 GMT

What is the difference between these 2 lines in SQL Server 2005 Express?[code="sql"]DATEADD(d, 0, DATEDIFF(d, 0, @Today));[/code]and[code="sql"]DATEADD(d, DATEDIFF(d, 0, @Today), 0);[/code]Other than making this statement fail at random times:[code="sql"]DECLARE @DateSrc DATETIME;-- Chop off the time part:SET @DateSrc = DATEADD(d, 0, DATEDIFF(d, 0, @Today));INSERT INTO dbo.SeqNo(MyGUID, TheDay, LastNo)SELECT @MyGUID, @DateSrc, 0WHERE NOT EXISTS ( SELECT 1 FROM dbo.SeqNo AS sn WHERE sn.MyGUID = @MyGUID AND sn.TheDay = @DateSrc );[/code]

How to add user to sql server 2005 express programatically

niteshrajgopal — Thu, 05 Aug 2010 01:25:37 GMT

Hi Guys,I have a class that checks if sql server express 2005 is installed on a pc and if not it installs it. In my class I specify stuff like sa password etc for the install.When a user installed the software the database was created but he can't login as he gets a meesage saying:[quote]unhandled exception has occurred in your application. Cannot open database "Games" requested by the login. The login failed.[/quote]How can I create a user for my database programmatically. Or even I run a sql script that creates the database. How can I do this in the script.I'm just installing on a single pc, no networking required, so in my connectionstring I use .\SQLExpress as the server. please help

Joins on three tables

karthik_ayyagari — Mon, 25 Feb 2013 03:46:56 GMT

I have three tables namely, TBL_SRTAPPGROUPPOWER ,TBL_SRTTICKETMASTER andTBL_MARVEL_STATUS which has status id 1to25I wrote the following query to fetch the count of tickets with status id ( MSTAT_N_SID ) but i'm unable to get the status ids with count 0 (zero)How can i get it ? Please helpselect MSTAT_N_SID, count(TSTM.TICKET_N_TICKETNO) from TBL_MARVEL_STATUS TSM left join TBL_SRTTICKETMASTER TSTM on TSM.MSTAT_N_SID=TSTM.TICKET_N_STATUSID WHERE TICKET_N_APPID IN (SELECT AG_N_APPID FROM TBL_SRTAPPGROUPPOWER WHERE AG_V_APPGROUPID='1') GROUP BY MSTAT_N_SID;[b]The Output:[/b]MSTAT_N_SID COUNT(TSTM.TICKET_N_TICKETNO) ---------------------- ----------------------------- 1 11 4 1 7 2 9 2 11 80 15 2

SQL Express 2005 stoppped running on win xp sp3

ayyazm64 — Sun, 06 Jan 2013 20:35:45 GMT

Hi,we have ms sql express installed on a computer which we use as server and it is on LAN. Before it was working fine but now it stopped conneting to the database. If we try to start it in control panel, it gives the error"Couldn't start sql express service on local computer: error 193:oxc1"sql browser is running and ms sql server is also running.sql express is set to automatic in services.Could any one help me out.Thanks!Ayyaz

Database restore

mourad.b — Tue, 08 Jan 2013 02:40:40 GMT

Hello All,I hope and I'm sure to find an answer with all the experts on this forum.Well this is the situation, this is in my home lab and I'm looking to restore an SQl Express 2005 DB to a 2008 SQL server is possible using just the MDF and the LDF files? (no migration no backup and restore)I've looked around the web and what I can see is attaching these two files to the SQL 2008 server would be enough?one other thing is that I no longer have the credentials of the user account where this SQL Express DB is created?

Does Installing SSMS stop the database

peteroc — Mon, 10 Dec 2012 22:33:54 GMT

Hi,I've got a SQL Server Express 2005 installed on a server for vmware. The install didn't come with SQL Server Management Studio.I want to now install this but I don't want SQL Server service to stop even momentarily. Can someone confirm if I can go ahead with the install without stopping the database.Thanks,Peter

detach multiple databases using sp_detach_db

smokinphoenix2000 — Tue, 29 Apr 2008 18:27:36 GMT

All I'm trying to detach multiple databases with the sp_detach_db. All the databases (about 20 of them) have a similar name like LM_04302008. I was hoping to detach all of them in one shot by using a wild card (%) but its not working. How can I detach 20 databases in one shot with one query? I'm a rookie at this and have not found anything on line to help. Any help is appreciated.

Database stuck in RECOVERY_PENDING state

gdhillon — Thu, 30 Aug 2012 08:11:38 GMT

Hi,I have client who sql express installation hosting search database for sharepoint search. they informed yesterday that search not working and I found the search database in SUSPECT state.I tried put the database in Emergency mode and then to Single User mode in order to runALTER DATABASE dbname SET EMERGENCYGOALTER DATABASE dbname SET SINGLE_USERGODBCC CheckDB ('dbname', REPAIR_ALLOW_DATA_LOSS)but I'm getting the message ''dbname is being recovered. wait until recovery is finished"This yesterday afternoon, even after 16 hours , I'm getting same message. I tried the restarting SQL services but didn't helpit complaining the corruption is in .mdf file which 300mb in size and Log is 8Gb in sizeThey don't have any backups :(Thanks in Advance

new comer of sql server 2005

boobalanmca07 — Thu, 22 Nov 2012 02:52:46 GMT

how to study sql server 2005, as a dotnet developer what are the concepts should know and what are the concepts in sql server 2005

Had problems installing SQL 2008 R2 Express on a PC

Rod at work — Wed, 07 Nov 2012 14:57:44 GMT

My colleague was trying, unsuccessfully, to install SQL Server 2008 R2 Express onto her new PC (Windows 7 Ultimate, 64-bit). I thought that it would be OK to use the 32-bit version of SQL Express, but when she ran the setup all it would do is indicate it was extracting the files, and then just disappear. Nothing at all would happen. Finally after several failed attempts she downloaded and installed the 64-bit version, and that worked. So the question is this, is it necessary to install the 64-bit version of SS 2008 R2 Express on a 64-bit version of Windows?

What installs SQL Express?

Stuart Davies — Thu, 01 Nov 2012 05:54:30 GMT

We're in the middle of a security audit at my place of work and we have unearthed a lot of SQL express installations (different versions). I know that a Visual Studio installation can also install SQL Express - but not all of the PCs have Visual studio installed.Some of the users have had the PCs from new and claim that they haven't installed it.Does anyone know how to determine what has installed it - or am I asking for the impossible and have to compare installed software on all the PCs to try to find a common element?Thanks in advance

How do I configure a named instance in SQL EXPRESS 2008 R2?

Rod at work — Wed, 31 Oct 2012 11:39:06 GMT

I've installed a SQL Server 2008 R2 Express onto a user's machine (he's stuck on the east coast, due to the aftermath of Hurricane Sandy), and we'll be putting this onto all of our users' machines. Anyway, after installing it I expected the installation routine to prompt me to define an instance of SQL Server 2008 R2 Express, either the default instance or a named instance. It didn't, so I closed the installer. Now when I bring up SSMSE, and it wants to connect to an instance, there isn't one on his machine. So, how do I create a named instance?

SQL 2008 SSMS Server Management Studio crashes all the time since last week.

Alan Levin — Mon, 29 Jun 2009 11:42:48 GMT

SQL 2008 Express Server Management Studio SSMS crashes all the time with the following message:SSMS - SQL Server Management Studio has encountered a problem and needs to close. We are sorry for the inconvenience.What can I do??? Please Help! Is this a .NET Framework issue??!! How can I get more on this error??Thanks so much!!!

Can I have one version of SSMS installed and another of SSMSE?

Rod at work — Wed, 10 Oct 2012 10:18:56 GMT

We use SSRS 2005 for doing many of our reports. (Our production database is still in SQL Server 2005. Maybe next fiscal year we can upgrade...) However, we're working on a new app which will user SQL Server 2008 R2 Express. I'd like to put SSMS for 2008 R2 Express onto the same machine. Can I have both SSMS for SQL Server 2005 and SSMS for SQL Server 2008 R2 Express on the same machine?

Is it possible to do database maintenance using an older version of SSMS?

Rod at work — Mon, 08 Oct 2012 14:21:19 GMT

This question isn't so much about SQL Server 2005, as it is about SQL Server 2008 R2 Express. My colleague has SSMS 2005 installed on her PC. And we're working on developing a new application using SQL Server 2008 R2 Express, on the users' desktops, which means it has to be on our development boxes as well. We downloaded and installed SQL Server 2008 R2 Express onto her PC, I scripted out the definition of the database from my SQL Server 2008 R2 Express database, and ran the .sql in her instance. We've had to make some changes to some constraints, so I tried getting into SSMS 2005 on her system (since that's the only version of SSMS she has installed) and fix it that way. However, she got errors when we tried to go into the table's design view in SSMS 2005.This leads me to wonder, is it possible to do table design editing in a SQL Server 2008 R2 Express database, using SSMS 2005?

How can we install SQl server 2005 Express edition on Windows 7 64-bit machine

bslchennai — Wed, 29 Feb 2012 10:36:09 GMT

Hi,I have a windows 7 home premium 64-bit laptop and i want to install SQL Server 2005 Express edition-(server and client). how can i install.please provide me the install guide and also the download links for the SQl server 2005 Express.Please do me needful.Regards,Sarath Babu Vellampalli

The database is full!

visualserge — Mon, 09 Mar 2009 03:25:30 GMT

Any one can help me, how to manage this situation if the database is full.::D

Copy rows returned from a RESTORE FILELISTONLY command into a tble

bryan.duchesne — Thu, 10 Jun 2010 11:55:41 GMT

I am trying to copy the rows returned from a RESTORE FILELISTONLY command into a temp table so that I can get the current column structure so I can then query the results to further filter them.sp_configure 'Ad Hoc Distributed Queries', 1 GO RECONFIGURE GO SELECT a.* into #tmp FROM OPENROWSET('SQLNCLI','Server=(local)\SQLEXPRESS;Trusted_Connection=yes;','RESTORE FILELISTONLY FROM DISK =''c:\code3Billing\ClientSQLData\Master\code3billing.bak''') as abut I am getting an error Msg 7357, Level 16, State 2, Line 4Cannot process the object "RESTORE FILELISTONLY FROM DISK ='c:\code3Billing\ClientSQLData\Master\code3billing.bak'". The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.I have permissions since I can run the RESTORE command as a stand-alone. I am sure there are columns since I can see them when I do run the command as stand-alone.Does anyone have any insight here?

bcp error: Unable to open BCP host data-file

manas.panda — Thu, 18 Dec 2008 00:25:46 GMT

I get this error message:SQLState = HY000, NativeError = 0Error = [Microsoft][SQL Native Client]Unable to open BCP host data-fileif I try to run EXEC master..xp_cmdshell bcp "select top 100 * from test.dbo.Table_1" queryout "c:\data.txt" -c -Usa -Psa123 -SD-D-MIRS\SQLEXPRESS'from Query Analyzer (or a store procedure).If run the same command in cmd or run window (without EXEC and xp_cmdshell) I do not get any error and the content of the table is exported in the file I want...If the exported file is on a local folder on the server everything is working fine even in Query Analyzer...ThankManas

Insert into Master and Detail Table using Stored Procedure

sankar.nandakumaran — Wed, 25 Jul 2012 01:59:15 GMT

Hi....I am using SQL Server 2005 in my project. I want to know how to insert data to master as well as detail table simultaneously using stored procedure.In my scenerio am using EmployeeMaster table and EmployeeDetail Table.The empcode is nvarchar(300) which is primary key of EmployeeMaster table, i want pass this empcode to EmployeeDetail table after saving the EmployeeMaster. The values inserted in EmployeeDetail table is from a grid. Here we want to note that if there is any error in inserting the detail table both tables should not be saved.Kindly help me regarding this scenerio....Thanks and RegardsSankar

Database size exceeds license limit of 4096MB per Database error

nishantshende-532951 — Tue, 24 Jul 2012 06:49:19 GMT

Hi,I have installed SQL Express 2008 Express version on one of my staging server.But one of the database on above mentioned server was previously set to "SQL Server 2005(90)" compatibility level in Option section. and I am getting the Database size exceeds license limit of 4096MB per Database error while rebuilding the Indexes.Now I have changed the compatibility level in Option section to "SQL Server 2005(100)". But still I am getting the same above error.Following are my SQL Server version information:Microsoft SQL Server Management Studio = 10.0.2531.0Instance ID = MSSQL10.SQLEXPRESSMicrosoft Data Access Components (MDAC) = 6.1.7600.16385Microsoft MSXML = 3.0 6.0 Microsoft Internet Explorer = 8.0.7600.16385Microsoft .NET Framework = 2.0.50727.4971Operating System = 6.1.7600As I am having sql express 2008 Express version installed, my database size should have max 10GB size not 4GB.Please help me...

Connect by server name but not by IP

tim_o_mast — Thu, 19 Jul 2012 14:50:09 GMT

OK, I'm not sure where the problem is but here is what is happening.2005 server crashed. rebuilt server with last backup. (backed up the night right before crash. good luck there.)this was the domain server also so had to move everyone to "new" domain.we have a thrid party software that connects to this sql server. Got it working except one part. uses Crystal reports to make letters to be sent out and other reports.the report engine works on two computer but not the other 10.the reporter connects to the server with ip address\sqlexpress.I can make an odbc connection with the ip address or name on the 2 good onesbut I can't make an odbc connection with the ip address on the bad computers, I can make the connection with the server name.I have turned off the firewalls on the bad computer, didn't help.I know the SQL server is correct because 2 computers can connect. What is stopping the bad ones from connecting? I can't see any differences on these computers. (The can all use the 3rd party software to lookup accounts and enter information. Just not produce letters.)Any ideas are worth trying.

Applying Service Pack 3 to SQL Server 2005 Express Edition

sjerromeharris — Wed, 18 Jul 2012 04:29:58 GMT

We need to apply SP3 to our install of SQL 2005 Express (SP2 has previously applied).We've found this link to the service pack:http://www.microsoft.com/en-us/download/details.aspx?id=15291I would just like to check that this is the correct link to use for the Service Pack 3 (and not a fresh install of Express Edition). Also, exactly how do we go installing the service pack? - is it really as simple as downloading the service pack and then running the .exe?Thanks

SQLSERVER Agent is not running- SQLSERVER 2008R2

sram24_mca — Tue, 17 Jul 2012 02:52:19 GMT

Hello: We have Windows 7 with SQLSERVER 2008R2 Express Edition 10.5 Installed. Database Engine and SQL Browser services are running perfect .The Problem is SQLSERVER Agent is not running. We have Provided the all the permissions to logged in users. Can anyone help us to resolve the problem?Thanks in advance ..!Regards,Ram

How to replicate data to a remote place

phuongdnx — Fri, 13 Jul 2012 03:31:07 GMT

My issue is:DB in HK got 3-5 million records/day. I need to replicate an amount of selective records to Canada, which is supposed to be 1-3 million records/day.Can someone please advise me some possible solution? Acceptable delay is a few minutes, or even 10-20 minutes in the worst case. Even some 3rd party tools is considerableMy current dbms is sql server 2k8 r2A million thanks to any reply

tsql code

debris_flow — Fri, 06 Jul 2012 22:12:17 GMT

Imagine a table which contains records for all weekly number of bugsdiscovered by each QA analysts in the team for one year. Please describe asimple SQL statement for identifying a winner (an employee who found thebiggest number of bugs during that year) by writing one statement?

Error 262 Can not create new DB

logicinside22 — Thu, 04 Aug 2011 16:08:16 GMT

Hi Guys Have SQL Server 2005 Express 32 bit installed on win-7 64 bit.after installation when i am trying to create new data base its doesn't allow me to do .Error show: create database permission denied in database 'master' (microsoft sql server error:262)So any Ideas ?pls reply me ASAPThanks

Insert via stored proc. and incomplete data recorded

adocity — Sat, 23 Jun 2012 15:23:15 GMT

Hello,I was hoping someone could point me in the right direction. I have a program written in VB.NET that inserts data into our SQL Server via a stored procedure. Every now and then one specific row does not insert correctly: Either nothing was inserted, or only a portion of the data was recoreded. Here are the facts:1.) The six digit integer I am inserting is validated application side. 2.) There is no way for the program to function if a valid six digit integer is not obtained and validated prior to insertion.3.) After the insertion this one row is the only one prone to invalid data issues, but it is very random (Only 22 errors with over 500 inserts).4.) 6 other rows are inserted with the stored procedure and are not missing any data.I don't understand how this could be happening. Please help. THANKS!

Comparing data between 2 datasets into a table - SQL reporting services

jackn 40579 — Mon, 20 Feb 2012 09:51:46 GMT

Hi there,This is my first post so I'm not sure if I'm putting it in the right place. I'm working with a reporting server that I only have read access to, so I cannot create tables or procedures etc. What I'm trying to do is compare values returned from 2 datasets and then display it appropriately in a table. The 1st dataset grabs a list of dates that match if certain criteria are matched, and the second one does the same but with different criteria. I then want to be able to display a list of these dates in a table, excluding any that match between the 2 datasets. Can anyone advise me please?Regards,Jack

Another Error 18456

Assun — Thu, 23 Jul 2009 05:16:13 GMT

Hello,I'm not a DBA and have very little knowledge of SQL, so I'd like to know if someone could help me, please. Forgive me if any question seems dumb. Here's the problem: I'm having several entries in the SQL Server 2005 error logs related to login failures. Whenever a user tries to login, the frontend reports a possible wrong password and the SQL log records the following:Error: 18456, Severity: 14, State: 8.Login failed for user [Client: "IP"]Error: 18456, Severity: 14, State: 8.Login failed for user [Client: "IP"]Error: 18456, Severity: 14, State: 8.Login failed for user [Client: "IP"]Error: 18456, Severity: 14, State: 5.Login failed for user [Client: "IP"]The entries are repeated in pairs for each login - 2 entries for sa followed by two entries for the user. It appears that everytime a user tries to login, the client app attempts to login first with sa and then the user (maybe that's the way it's supposed to be?!). If I try to change the user's password on the client app, the problem remains. If I change the user's pass on Management Studio, everything's ok. However, some of the user's accounts do not show up on Management Studio, although they were active yesterday.I've been looking around about these error codes and found out that 8 is for wrong passwords and 5 for invalid logins. However, can't really figure out what went wrong.Hope someone can give me a hint.Thanks,PDA>>> EDIT:Apparently, other Administrator changed the sa password 2 weeks ago. I reset the password to the previous value and everything went back to normal. However, I still can't figure out how and why the users managed to work without a problem until now. Any thoughts?