Common SQL Server Express Problem That I Can't Seem To Solve
Jul 9, 2006
Hi Guys,
[VS2005, .NET 2.0 SQL Server Express]
I could really use some help with this one - as in desperately!:
I have a couple of database projects which run fine in Debug Mode from VS2005, but I can't get them to run outside of it.
The problem is that, for some reason, the login to the database file isn't succeeding. I'm getting the following errors:
.NET Project: System.Data.SqlClient.SqlException: Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.
DotNetNuke 4.0.3 Project: ERROR: Could not connect to database specified in connectionString for SqlDataProvider. (I sometimes get a "The Server Version does not match the Database Version" error)
Here are my connection strings for the above two scenarios:
My assumption is that I need to switch from Integrated Security (is that Windows Security?) to SQL Server Security, but I'm battling to do that effectively.
A pointer to a simple step-by-step guide would be fantastic, but, if there isn't such a thing available, I have a number of specific questions:
Question 1: Why does one need to switch from Integrated Security to SQL Server Security?
If I'm right, I suspect that my connection strings will become:
<connectionStrings>
<add name="EpgDbConnection" connectionString="Data Source=(local);
user id=gary;password=password;Connect Timeout=30" />
respectively.
Question 2: Are these strings correct?
(I receive a login failed error)
Question 3: How do I go about changing the Security Setting, using either VS2005 or SQL Server?
(I've modified permissions at a SQL Server Express level from Windows Authentication mode to SQL Server and Windows Authentication mode in SSMS, but the Authentication Method can't seem to be changed at the database file level. View connection string (under database properties), reveals an unalterable "Windows Authentication" Authentication Method.)
Question 4: I've managed to add a user and password at the SQL Server Express level. What roles should the user have?
I'd be very grateful for any help you could give me. I've tried everything I can find or think of and am not getting anywhere!
I guess that I had "Unit" (instead of "UnitForConc"), when I executed the sql code last time!!!??? How can I delete the old, wrong CTE that is already in the ChemDatabase of my SSMSE?
Hello, I would like to deploy a per-machine Express database as part of my VS2005 C# desktop application. The deployment must work with both XP and Vista from the same setup files. I'm not using One-Click installation. I don't want my users to have to configure Express with the Surface Area Configuration tool.
Since the database cannot go in C:Program Files because of Vista ACL issues, I am using COMMONAPPDATACompanyNameAppName as the install path for the database and other user writable application files. In XP this expands to C:Documents and SettingsAll UsersApplication DataCompanyNameAppName and in Vista it is C:ProgramDataCompanyNameAppName. Neither of these paths have write privileges for unprivileged users, so the install program needs to change the write permissions at install time. Right now I am using SetACL ( http://setacl.sourceforge.net/ ), to do that and the install goes fine with write permissions on the companyName folder and it's children for all users.
But I can't connect to the mdf file in my COMMONAPPDATACompanyNameAppName folder. If I am the administrator/installer it works fine, but as an unprivileged user I get different errors, but mostly "Could not login user ...". For debugging, I have also tried installing the database in the "AppFolder" directory (which for my app in both Vista and XP is C:Program FilesCompanyNameAppName) and using "User Instance=true" in the connection string, and the connection is made but (at least on XP) the unprivileged user cannot write to the database because the folder lacks write privileges for that user.
I suppose one thing I could do would be to change the folder/file permissions in the AppFolder, but I am reluctant to do that because I have read that Microsoft does not guarantee that the "Compatibility" feature in Vista for the Program Files directory will be available in future releases/service packs.
So is there a way to do a simple installation/db connection for a per-machine database located in a COMMONAPPDATA path?
Hi all, I have the following T-SQL code of Common Table Express (CTE) that works in the SQL Server Management Studio Express (SSMSE):
--CTE.sql--
USE ChemAveRpd
GO
WITH PivotedLabTests AS
(
SELECT LT.AnalyteName, LT.Unit,
Prim = MIN(CASE S.SampleType WHEN 'Primary' THEN LT.Result END),
Dupl = MIN(CASE S.SampleType WHEN 'Duplicate' THEN LT.Result END),
QA = MIN(CASE S.SampleType WHEN 'QA' THEN LT.Result END)
FROM LabTests LT
JOIN Samples S ON LT.SampleID = S.SampleID
GROUP BY LT.AnalyteName, LT.Unit
)
SELECT AnalyteName, Unit, avg1 = (abs(Prim + Dupl)) / 2,
avg2 = (abs(Prim + QA)) / 2,
avg3 = (abs(Dupl + QA)) / 2,
RPD1 = (abs(Prim - Dupl) / abs(Prim + Dupl)) * 2,
RPD2 = (abs(Prim - QA) / abs(Prim + QA)) * 2,
RPD3 = (abs(Dupl - QA) / abs(Dupl + QA)) * 2
FROM PivotedLabTests
GO
=========================================== How can I execute this set of the CTE.sql code in the VB 2005 Express via the Stored Procedure programming?
My Sql express will use up to 1G or more memory and never release. I had the same problem on SQL 2005 standard, I solved by adding /3G in boot.ini and turn on AWE. but it seems SQL express doesn;t support AWE. so how could I do here?
We rebooted our main SQL Server (SQL2k). It had 5 linked servers setup, 2 on the same network and 3 on another network. After the reboot the linked sql servers on the other network only work some of the time when trying to access them via a linked server. The 2 linked servers on the same network have no problems at all. All the routes are setup properly and are persistant.
Linked servers SameNetwork1 -- No Problems SameNetwork2 -- No Problems ServerOtherNetwork1 -- "sqlserver does not exist or access is denied. [SQLSTATE 42000] (Error 17)." ServerOtherNetwork2 -- "sqlserver does not exist or access is denied. [SQLSTATE 42000] (Error 17)." ServerOtherNetwork3 -- "sqlserver does not exist or access is denied. [SQLSTATE 42000] (Error 17)."
All SQL Server 2k Standard with 2 processor licenses and the latest service packs. All have been setup as TCPIP and the port has been defined in the client network utility using the IP address not the name. All servers can be "pinged" and you can pull up the physical drive folders from across the networks with no problems. All linked servers are running windows 2000. The main server is running NT.
After the reboot only server1 was working with the linked server. The other 2 servers were getting either a timeout or "sqlserver does not exist or access is denied. [SQLSTATE 42000] (Error 17)." I tried to fiddle with the linked servers by changing the passwords and got Server1 and Server2 working over the linked server connection but could not get server3 to work. After about 30 minutes or so I noticed that server 2 was working on and off. It was not consistant about failing or working. I decided to go home for the night. When I came in this morning, only Server2 works. Server1 and 3 do not at all.
I have a job setup to pull transactions from a table on server2 to our main sqlserver. This job was running every 5 minutes and it was failing here and there. I have now setup the job to run every 2 minutes and it has not failed for the past hour. When it does fail it gives the error above (server does not exist).
I really hope this makes sence to someone... lol :o
We are developing the web application using ASP.NET 2.0 using C# with Backend of SQL Server 2005.
Now we have one requirement with my client, Already our application live in ASP using MS Access Database. In Access Database our client already wrote the query, those query call the another query from same database. So I want to over take this functionality in SQL Server 2005.
In ASP Application, We call this query €œ081Stats€? from Record set.
This €˜081Stats€™ query call below sub query itself
Master Query: 081Stats
SELECT DISTINCTROW [08Applicants].school, [08Applicants].Applicants, [08Interviewed].Interviewed, [Interviewed]/[Applicants] AS [interviewed%], [08Accepted].Accepted, [Accepted]/[Applicants] AS [Accepted%], [08Dinged].Dinged, [Dinged]/[Applicants] AS [Dinged%], [08Waitlisted].Waitlisted, [Applicants]-[Accepted]-[Dinged] AS Alive, [08Matriculating].Matriculating, [Matriculating]/[Accepted] AS [Yield%] FROM ((((08Applicants LEFT JOIN 08Interviewed ON [08Applicants].school = [08Interviewed].school) LEFT JOIN 08Accepted ON [08Applicants].school = [08Accepted].school) LEFT JOIN 08Dinged ON [08Applicants].school = [08Dinged].school) LEFT JOIN 08Waitlisted ON [08Applicants].school = [08Waitlisted].school) LEFT JOIN 08Matriculating ON [08Applicants].school = [08Matriculating].school;
Sub Query 1: 08Accepted
SELECT statusTbl.school, Count(1) AS Accepted FROM statusTbl WHERE (((statusTbl.decision)=1) AND ((statusTbl.yearapp)="2008")) GROUP BY statusTbl.school ORDER BY Count(1) DESC;
Sub Query 2: 08Applicants
SELECT statusTbl.school, Count(1) AS Accepted FROM statusTbl WHERE (((statusTbl.decision)=1) AND ((statusTbl.yearapp)="2008")) GROUP BY statusTbl.school ORDER BY Count(1) DESC;
Sub Query 3: 08Dinged
SELECT statusTbl.school, Count(1) AS Dinged FROM statusTbl WHERE (((statusTbl.decision)=0) AND ((statusTbl.yearapp)="2008")) GROUP BY statusTbl.school ORDER BY Count(1) DESC;
Sub Query 4: 08Interviewed
SELECT statusTbl.school, Count(1) AS Interviewed FROM statusTbl WHERE (((statusTbl.interview)=True) AND ((statusTbl.yearapp)="2008")) GROUP BY statusTbl.school ORDER BY Count(1) DESC;
Sub Query 5: 08Matriculating
SELECT statusTbl.school, Count(1) AS Matriculating FROM statusTbl WHERE (((statusTbl.userdec)=True) AND ((statusTbl.yearapp)="2008")) GROUP BY statusTbl.school ORDER BY Count(1) DESC;
So now I got the solution from SQL Server 2005. I.e. Common Table Expressions, So I got the syntax and other functionality, Now my doubts is how do implement the CTE in SQL Server 2005, where can I store the CTE in SQL Server 2005.
How can I call that CTE from ASP.NET 2.0 using C#?
CTE is replacing the Stored Procedure and Views, because it is memory based object in SQL Server 2005.
How can I implement the CTE, where can I write the CTE and where can I store the CTE.
And how can I call the CTE from ASP.NET 2.0 using C#.
we have a Dell pe 6600 server running for our ERP Application databases. version of Sql server is 2000 with sp4. OS is windows 2000 with SP 4 .we were running out of space in server and we planned to upgrade the harddisks. after upgradation, the Performance of the server is very slow. Even zipping of .bak files from one drive to other is slow (which is not related to database). In old setup (before upgradation ) , to zip .bak files (backup files of DB's)(tot Size : 90 GB) use to take 3hrs 40 mins. Now it is taking more than 10 Hrs. There are no errors found in Windows Event viewer and SQL Server. we logged a call with Dell and finally, dell said that they could not find any errors in raid configuration or harddisks. all the firmware and bios versions are up todate. Application vendor is also saying that, the database is fully optimized. we could not find out the reason for the sluggish performance and we feel that this is something to do with hardware or with raid configuration. From our analysis,any activity that requires a read or write activity from the harddik is slow.( but server vendor is saying that there is no problem in the hardware.) Activities analysed (all at zero load on server) 1) backup thru SQL Server - 40 mins (old setup) backup thru SQL Server - 2 hrs (New setup) 2) zipping of .bak files (from Raid 5 HDD to Raid 1 HDD) old setup - 3 hrs 40 mins New setup - 8 hrs 30 mins 3) all the schedulers (both windows and sql server) are dead slow. 4) Maintenance jobs for databases old setup - 5 - 6 Hrs New setup - 11 - 12 hrs after analysing all the above activities ,we came to a conclusion that the problem may not be with sql server. But we could not trace out the problem till now. since it is the production server we are not able to take downtime that easily. i have given the old set up and new setup configuration below. Note: the server lies with the same configuration in both old set up and new setup.(only harddisks are upgraded. Please help me to solve this issue As of now, we have decided to start from the scrap once again. before that can i get some help ???
old set up:
Raid 1 - 32 x 2 - (two Partitions C and D ) C - OS , D - Batch Files
Raid 5 - 72 x 5 - (two Partitions E and F ) (1 harddisk - Hotspare - 4 hdd will be used for raid) E - .MDF Files , F - .BAK Files (Backup of database files will be stored here temporarily before moving it to tapes)
Raid 1 - 32 x 2 - (two Partitions G and H ) G - .LDF Files , H - to store the zipped .bak files ( we have a windows sceduler which zip the .bak files in E drive and place the .zip files in H drive)
New Set up:
Raid 1 - 72 x 2 - (two Partitions C: and D ) C - OS , D - Batch Files
Raid 5 - 300 x 5 - (two Partitions E and F ) (1 harddisk - Hotspare) E - .MDF Files , F - .BAK Files (Backup of database files will be stored here temporarily before moving it to tapes)
Raid 1 - 72 x 2 - (two Partitions G and H ) G - .LDF Files , H - to store the zipped .bak files ( we have a windows sceduler which zip the .bak files in E drive and place the .zip files in H drive)
What I am trying to do is count persons in buckets "non-recidivists" and "recidevists" based on how many bkg_nbr they have per STATE_NBR. If they have more than 1 bkg_nbr per STATE_NBR then put them in the "recdivists" bucket. If they only have a 1 to 1 then put them in the "non-recidivists" bucket.
I've inherited a table of members that has the following structure:
CREATE TABLE [dbo].[dimMember]( [dimMemberId] [int] IDENTITY(1,1) NOT NULL, [dimSourceSystemId] [int] NOT NULL CONSTRAINT [DF_dimMember_dimSourceSystemId] DEFAULT ((-1)), [MemberCode] [nvarchar](50) NOT NULL, [FirstName] [nvarchar](250) NOT NULL, [LastName] [nvarchar](250) NOT NULL,
[Code] ....
Based on the way the data loads into the table there's a possibility of some records being near duplicates of each other. For example, we can have a member that has records that have the same first name, last name, SSN, but different addresses, membercodes, subscribercode etc... This can happen in pretty much any variation thereof.
What I want to do, is add a new column and use that to group the similar records under based on comparing on several columns. By this I mean, if a member matches 4 of the 7 values below with another member, we would group these:
First Name (1st 3 characters) Last Name DOB CurrentAddress1 MemberCode SSN SubscriberCode
I'm at a loss of how to structure the SQL to update the new column in the table.
I have a multi-tenant database where each row and each table has a 'TenantId' column. I have created a view which has joins on a CTE. The issue I'm having is that entity framework will do a SELECT * FROM MyView WHERE TenantId = 50 to limit the result set to the correct tenant. However it does not limit the CTE to the same TenantId so that result set is massive and makes my view extremely slow. In the included example you can see with the commented line what I need to filter on in the CTE but I am not sure how to get the sql plan executor to understand this or weather it's even possible.I have included a simplified view definition to demonstrate the issue...
ALTER VIEW MyView AS WITH ContactCTE AS( SELECT Col1, Col2, TenantId
declare @LastDate datetime SELECT @LastDate = max([LastUpdate]) FROM [exhibitor].[dbo].[blgBelongs] WHERE (([Table1]=@module1 OR [Table2]=@module2 )or ([Table2]=@module1 OR [Table1]=@module2 ) AND Exists (SELECT [Table1],[Table1ID] FROM [exhibitor].[dbo].[blgBelongs] WHERE table2=30 and table2ID=@dmn_ID))
Before I see @LastDate , I see this warning
Warning: Null value is eliminated by an aggregate or other SET operation.
CREATE TABLE [Table 2] ( Id varchar, MoreData varchar )
What is the link between these two tables?
I have databases that have zero keys defined, no foreign key constraints, no unique value constraints. I cannot assume that an Identity column is the Id. The end game is be able to output that [Table 1].[TableTwoId] = [Table 2].[Id] but cannot assume that all linkage in the database will be as simple as saying "if the field name contains a table name + Id then it is the Id in that table."
Currently have written a script that will cycle through all columns and start identifying keys in singular tables based on the distinctness of the values in each column (i.e. Column1 is 99% distinct so is the unique value). It will also combine columns if the linkage is the combination of one or more columns from Table 1 = the same number of columns in Table 2. This takes a long time, and it somewhat unreliable as IDENTITY columns may seem to relate to one another when they don't.
I have databases that have zero keys defined, no foreign key constraints, no unique value constraints. I cannot assume that an Identity column is the Id. The end game is be able to output that [Table 1].[TableTwoId] = [Table 2].[Id] but cannot assume that all linkage in the database will be as simple as saying "if the field name contains a table name + Id then it is the Id in that table."
Currently have written a script that will cycle through all columns and start identifying keys in singular tables based on the distinctness of the values in each column (i.e. Column1 is 99% distinct so is the unique value). It will also combine columns if the linkage is the combination of one or more columns from Table 1 = the same number of columns in Table 2. This takes a long time, and it somewhat unreliable as IDENTITY columns may seem to relate to one another when they don't.
What is the Difference between the SQL Server Express Bundled with C#/VB VS the downloadable SQL Server Express SP1 with advanced Services?
I installed C# with SQL Server Express, however I wanted to add the Full Text Searching and the SQL Server Management Studio Express, so I downloaded and installed the SQL Server 2005 Express Edition with Advanced Services SP1. When I installed it over top of my current installation, it complained of version mismatching, and then C# failed to recognize that I had SQL 2005 Express installed at all.
What I'd like to know is, which version is more current (they have to be different, they had different version numbers, one was 9.xx.xxxx the other was 2005.9.xx.xxxx) The one bundled with C#, or the SP1 downloadable one.
Firstly, I want to be up todate as far as security patches, and Secondly, how do I add the full text searching and SSMSE to the one bundled with C# without breaking it.
Q1: Does Sql Server 2005 Express support Web/Internet to other SQL Server 2005 Express Clients or does it have to Synch across the internet to a fully installed setup SQL Server 2005 with IIS?
Q2: Does SQL Server 2005 Express support Direct Replication between other SQL Server 2005 Express clients?
I get this error when i try to connect with my sql, I created my SQL with MS SQL 2005 Workgroup,
the error is:
Server Error in '/' Application.
The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version. 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. Exception Details: System.Configuration.Provider.ProviderException: The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version.
When i used MySql in my appliaction its working well, but now i shifted my web application on SQL Server2000 , its gives problem. It gives this error at runtime... The specified module could not be found. 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. Exception Details: System.IO.FileNotFoundException: The specified module could not be found.Source Error:
Line 79: SqlCommand OcCom=new SqlCommand(Query,OdCon); Line 80: SqlDataReader OdDat; Line 81: OdCon.Open(); Line 82: OdDat=OcCom.ExecuteReader(CommandBehavior.CloseConnection); Line 83: return OdDat;
i read ths stuff from all over and reinstall the Visual studio .NET2003 again . but still some is not solved. So Pls help Me!!!!!!!!!!!!!! Thanx Manish
Lets say I want to delete a record in tableA. tableA has 5 foreign key tables.
Before I delete the row in tableA, I want to make sure I dont get any foreign key violation error. I dont want to make a cascading delete, since the record should not be deleted if it has child records.
Of course I could check all those tables before the delete. Anybody know of a better approach?
hi I have a question: why when we call a stored procedure in delphi that the return in Null (Example Not Match data with our Select) Error occured? Please help me . thanks alot.bye.
hi, i have a doubt whether constraints(for eg:default constraint ) can be applied on table variable or not. plz let me know. iam presenting the table variable which iam using.
declare @t table (a int constraint d default 1 ,b varchar(10) constraint a default 'india' ) insert into @t values ( 1,'ic')
I downloaded the SSEUtilSetup.EXE and extracted the SSEUtil.exe to a folder of C:drive of my PC that is Windows XP Pro. I plan to learn the CLR programming via user-instance of SQL Server Express. I need to have SQL Server Express Utility installed. Please help and tell me how I can install SSEUtil.exe in my PC and how I use it to interact with SQL Server Express.
I'm building an exception management system into my application. What are the most common errors when working with a DAL and a SQL server. The biggest problem can be Database connection. My questions: * Are all SQL related errors catched by the SqlExeption class? * Which are the most common sql errors?
I have a stored procedure I use to fill my datagrid..... While doing this is there a way to get the number of records returned... I use a datareader........any help
I just inherited a Java application with a fairly complex data modelthat does not yet have any indexes except those on primary keys. It isstill in development and before I get to do any performance testing Iwant to put some default indexes on it. I realize that this a veryvague request but it's better than nothing so I want to start withthose that are generally a good idea and then tune those areas thatrequire more fine grained approach. By the way, I'm mostly a Javaprogrammer so please forgive my DB ignorance and I thank you for anyhelp.Since the full schema is quite large I will use a simple example (fullycited below my questions). Here is list of indexes that I think wouldbe a good idea. If anyone can add to it or comment on my questions Iwould appreciate it.1. Index on primary keys in all three tables. My understanding thatthis indexing happens automatically just by declaring that a column isa PK.ALTER TABLE employees ADD PRIMARY KEY (emp_id);ALTER TABLE employee_addresses ADD PRIMARY KEY (address_id);ALTER TABLE departments ADD PRIMARY KEY (dept_id);Question: Does index get created automatically because this is a PK?2. Index on foreign keys in the children tables to prevent deadlocksand lock escalations.CREATE INDEX fk_index ON employee_addresses (emp_id)3. Indexes on common queries on all three tables.CREATE INDEX common_query_idx on employees(last_name, first_name,position)CREATE INDEX common_query_idx on departments(last_name, first_name,position)CREATE INDEX common_query_idx on employee_addresses(street, city)Question: Given that the searches can be on any field separately andin any combination should I also put an index on each columnindividually or will the composite index take care of individualsearches as well? For example, will the above indexes be sufficientfor the following SELECT:SELECT e.last_name, e.first_name from employees e, departments d,employee_addresses ea, dept_employees de WHERE e.emp_id = de.emp_id ANDd.dept_id = de.dept_id AND ea.emp_id = e.emp_id AND e.position ='master chief' AND d.dept_name = 'galactic affairs' AND ea.city='LosAngeles'4. Unique index on the association table. Again this is accomplishedusing PKALTER TABLE dept_employees ADD PRIMARY KEY (dept_id, emp_id)Question: Is the index on (dept_id, emp_id) automatic because of PK?5. The association table has to go both ways and PK takes care only ofthe first half. Thus add an index to go the other way.create unique index dept_employee_idx on dept_employee(emp_id,dept_id)Question: should I use INDEX ORGANIZED TABLE?Question: should I have UNIQUE qualifier on the second index given thatPK already takes care of it?Thanks,Robert===== EXAMPLE ======1) An employee can be in many departments and a department can containmany employees.2) Common searches for employees are on last_name, first_name,position, department_name, department_location separately and in anycombination.3) There are common searches for departments that contain certainemployees e.g. find all departments containing John Smith.CREATE TABLE employees(emp_id INTEGER NOT NULL,last_name VARCHAR(50) NOT NULL,first_name VARCHAR(25) NOT NULL,position VARCHAR(10) NOT NULL);CREATE TABLE employee_addresses(address_id INTEGER NOT NULL,emp_id INTEGER NOT NULL,street VARCHAR(50) NOT NULL,city VARCHAR(25) NOT NULL,);CREATE TABLE departments(dept_id INTEGER NOT NULL,dept_name VARCHAR(50) NOT NULL,dept_location VARCHAR(25) NOT NULL,);CREATE TABLE dept_employees(dept_id INTEGER NOT NULL,emp_id INTEGER NOT NULL,);ALTER TABLE employee_addresses ADD FOREIGN KEY (emp_id) REFERENCESemployees(emp_id)ALTER TABLE dept_employees ADD FOREIGN KEY (emp_id) REFERENCESemployees(emp_id)ALTER TABLE dept_employees ADD FOREIGN KEY (dept_id) REFERENCESdepartments(dept_id)
I'm a beginer! I host my web on a hosting service provider. But when I access an error occur:
Server Error in '/' Application.
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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) 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. Exception Details: System.Data.SqlClient.SqlException: 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[SqlException (0x80131904): 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734867 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) +820 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +130 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83 System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770 System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17 System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149 System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70 System.Web.UI.WebControls.DetailsView.DataBind() +4 System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82 System.Web.UI.WebControls.DetailsView.EnsureDataBound() +181 System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69 System.Web.UI.Control.EnsureChildControls() +87 System.Web.UI.Control.PreRenderRecursiveInternal() +41 System.Web.UI.Control.PreRenderRecursiveInternal() +161 System.Web.UI.Control.PreRenderRecursiveInternal() +161 System.Web.UI.Control.PreRenderRecursiveInternal() +161 System.Web.UI.Control.PreRenderRecursiveInternal() +161 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210
Hi,there are three tables:1) department---------------ID (primary key)name2) reports----------ID (p. key)depIDuserID3) users--------ID (p.key)nameI want to get in one query how many distinct users have made a report for each department.E.g.: table reports may look like this:ID depID userID1 1 12 1 13 1 34 3 65 4 8This gives:for dep 1: 2 distinct usersfor dep 2: 0for dep 3: 1 distinct userfor dep 4: 1 distinct userThanks for helpTartuffe
Hi, I am trying to update my table... but when i try to save my query.. i am getting the following error Column 'SourceDBF.DEFPCT' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. This is my sproc...
UPDATE Statements..ParticipantSourceSummary SET DeferralPct = DEFPCT, BeginingBal = BEGBAL, Deposits = DEPOSITS, Withdraw = WITHDRAW, GainLoss = GAINLOSS, Others = OTHER, EndingBal = ENDBALANCE, VestPercent = VESTPCT, VestBal = VESTBALANC FROM Statements..ParticipantSourceSummary ps Join ( SELECT p.ParticipantId, cp.PlanId, cs.SourceId, @PeriodId PeriodId, s.DEFPCT, s.BEGBAL, s.DEPOSITS, s.WITHDRAW, s.GAINLOSS, s.OTHER, s.ENDBALANCE, s.VESTPCT, s.VESTBALANC FROM SourceDBF s INNER JOIN Statements..ClientPlan cp ON s.PLAN_NUM = cp.ClientPlanId INNER JOIN Statements..ClientSources cs ON s.SOURCE_NUM = cs.ClientSourceId and cs.ClientId = @ClientId Inner Join Statements..Participant p on s.PART_ID = p.SSN Where s.Import = 1 GROUP BY p.ParticipantId, cp.PlanId, cs.SourceId ) s ONps.ParticipantId = s.ParticipantId AND ps.PlanId = s.PlanId AND ps.SourceId = s.SourceId AND ps.PeriodId = s.PeriodId any help will be appreciated.RegardsKaren
