I involved in redesign a database for hotel review listing. My current hotel datatable below
Hotel
ID
HotelName
Description
Address
TotalRating
If I would like to extend the review feature in details based on each user comments for example
Overall 7
Rooms 6
Services 7
Food 8
Could anyone guide me how could I modify the table or add new table that allow me to do so.
Thank you
First of all, this is my initial thread here on dbforums. I come from the land of Broadband Reports and would like to say, Hello fellow DB enthusiasts. :)
I'm not a novice to relational databases (Access MDBs), but new to implementing a db via SQL SERVER (2000 in this case) and using Access Data Projects.
My partial db schema is as follows:
participants
---DID (pk) char(1)
---LID (fk - schools) char(4)
---studentLast varchar(50)
---studentFirst varchar(25)
Sample Data would be
010191M001 | 5671 | SPARKS | JONATHAN
030495F283 | 5671 | DYLAN | CYNTHIA
=====================================
enrollhist (insert/update trigger for enrollactive)
---EID (pk - autonumber) bigint(8)
---EMID (fk - enrollmode) int(4)
---DID (fk - participants) char(10)
---LID (fk - schools) char(4)
---enrollactive bit(1)
Sample Data would be
38173 | 4 | 030495F283 | 9003 | 0
38266 | 3 | 010191M001 | 5671 | 0
39022 | 6 | 030495F283 | 9003 | 0
39036 | 5 | 030495F283 | 9003 | 0
39044 | 4 | 030495F283 | 5671 | 1
39117 | 4 | 010191M001 | 5671 | 1
=====================================
enrollmode
---EMID (pk) int(4)
---mode varchar(25)
Sample Data would be
1 | RECEIVED
2 | WAITING
3 | PENDING
4 | ENROLLED
5 | DROPPED
6 | TRANSFERRED
10 | ORPHANED
11 | DENIED
=====================================
schools
---LID (pk) varchar(4)
---CTID (fk - caltracks) char(1)
---AID (fk - agencies) char(1)
---SDID (fk - schooldist) char(1)
---COID (fk - countydist) char(1)
---sitename varchar(25)
---sitetitle varchar(75)
Sample Data would be
5671 | 3 | 2 | 1 | 4 | ASCOT | ASCOT AVENUE
9003 | 2 | 1 | 4 | 1 | ROWAN | ROWAN AVENUE
2865 | 1 | 3 | 2 | 3 | BRIGHT | BIRDELEE BRIGHT
=====================================
caltracks
---CTID (pk) char(1)
---legend char(4)
---trktitle varchar(15)
---trkcnt int(4)
Sample Data would be
1 | 9030 | 90/30 | 4
2 | CON6 | CONCEPT-6 | 3
3 | SNGL | SINGLE TRACK | 1
=====================================
agencies
---AID (pk) char(1)
---legend varchar(4)
---agencytitle varvhar(50)
Sample Data would be
1 | CRYS | CRYSTAL STAIRS
2 | MAOF | MEXICAN AMERICAN FOUNDATION
3 | PATH | PATHWAYS
4 | CCRC | CHILD CARE RESOURCE CENTER
5 | CHSC | CHILDREN'S HOME SOCIETY OF CALIFORNIA
==========================================
THE REMAINING "FKs" FROM SCHOOL ARE SIMILAR, as is other tables and their relationships. The design of the foreign keys were made using sql and the keyword "REFERENCES" and "FOREIGN KEY."
My questions are: :confused:
(1) Is the use of FK as a Constraint any different than using an INDEX and how?
(2) Should I Alter the Tables to include CASCADING Up/Down?
(3) Are the use of CHARs Ok for the Keys?
(4) Have I over/under-normalized any of the relationships?
My question takes two parts; firstly, is the new table that I'm proposing going to handle the business logic I describe below, and secondly, if I put the new table in, how in hell do I use it?
Right; present schema attached.
The idea, which I hope is fairly clear from the schema, is that you send it a buch of parameters about the event, admission date, etc, and it will return all tickets matching those parameters. An example stored proc for this is below:
CREATE PROCEDURE [dbo].[getSingleTicketsByParameters]
@eventIdINT,
@standIdINT,
@admissionDateIdINT,
@bookingDateIdINT,
@concessionIdINT,
@bookingMinQuantityIdINT,
@bookingMaxQuantityIdINT,
@membershipIdINT
AS
SET NOCOUNT ON
SELECT
[tblTickets].[id],
[tblTickets].[booking_date_id],
[tblTickets].[booking_min_quantity_id],
[tblTickets].[booking_max_quantity_id],
[tblTickets].[ticket_concession_id],
[tblTickets].[membership_id],
[tblTickets].[event_id],
[tblTickets].[stand_id],
[tblTickets].[admission_date_id],
[tblTickets].[price],
[tblTickets].[availability],
[tblTickets].[description],
[tblTickets].[admin_description],
[tblTickets].[ticket_open],
[tblTickets].[ticket_live],
[tblEvents].[event_name],
[tblEvents].[event_open],
[tblStands].[stand_name],
[tblStands].[stand_open],
[tblBookingDates].[booking_start_date],
[tblBookingDates].[booking_end_date],
[tblTicketConcessions].[concession_name],
[tblBookingMinQuantities].[booking_quantity],
[tblBookingMaxQuantities].[booking_quantity],
[tblAdmissionDates].[description],
[tblAdmissionDates].[admission_start_date],
[tblAdmissionDates].[admission_end_date],
[tblAdmissionDates].[date_open],
[tblMemberships].[membership_name]
FROM [tblTickets]
LEFT JOIN [tblEvents] ON [tblEvents].[id] = [tblTickets].[event_id]
LEFT JOIN [tblStands] ON [tblStands].[id] = [tblTickets].[stand_id]
LEFT JOIN [tblBookingDates] ON [tblBookingDates].[id] = [tblTickets].[booking_date_id]
LEFT JOIN [tblTicketConcessions] ON [tblTicketConcessions].[id] = [tblTickets].[ticket_concession_id]
LEFT JOIN [tblBookingQuantities] AS tblBookingMinQuantities ON [tblBookingMinQuantities].[id] = [tblTickets].[booking_min_quantity_id]
LEFT JOIN [tblBookingQuantities] AS tblBookingMaxQuantities ON [tblBookingMaxQuantities].[id] = [tblTickets].[booking_max_quantity_id]
LEFT JOIN [tblAdmissionDates] ON [tblAdmissionDates].[id] = [tblTickets].[admission_date_id]
LEFT JOIN [tblMemberships] ON [tblMemberships].[id] = [tblTickets].[membership_id]
WHERE 1=1
AND ([tblEvents].[id]=@eventId OR @eventId=0)
AND ([tblStands].[id]=@standId OR @standId=0)
AND ([tblTicketConcessions].[id]=@concessionId OR @concessionId=0)
AND ([tblAdmissionDates].[id]=@admissionDateId OR @admissionDateId=0)
AND ([tblBookingDates].[id]=@bookingDateId OR @bookingDateId=0)
AND ([tblBookingMinQuantities].[id]=@bookingMinQuantityId OR @bookingMinQuantityId=0)
AND ([tblBookingMaxQuantities].[id]=@bookingMaxQuantityId OR @bookingMaxQuantityId=0)
AND ([tblMemberships].[id]=@membershipId OR @membershipId=0)
GO
So. It's all about to get horribly, horribly complex (well, it is to me) so take a deep breath.
Tickets are subject to quotas. However, quotas are subject to... well, at the moment they can be based on event, stand, admission date, concession, or any combination of these. They can also be based on an individual ticket. All quotas, however, are annual; they only apply to ticket purchases in the same year. For example:
- you can't buy more than 4 tickets for date A, in stand B at event C, per year.
- you can't buy more than 2 tickets for stand D per year.
- you can't buy more than 4 of ticket number 123.
Now, I'm thinking that all I need to manage this is one table, and it's going to look a little like this:
tblQuotas
id INT PK
ticket_id INT FK
event_id INT FK
stand_id INT FK
admission_date_id INT FK
quota INT
So, if I put a record in there with an event, stand and admission date - and a quota - then I've met the first business rule that I described above. If I put in a record with just a stand id and a quota, then I've met the second sort. If I put in one with just a ticket id and a quota, then I've met the third.
Now we return to that big SQL statement above. The one that says "get me all eligible tickets that match these parameters". And it's got to get a lot bigger because I now need to not only join in tblQuotas, to see if any quotas apply to the ticket I've chosen (or to the event, stand, etc that make it up), but I've also got to join in tblBasket, and tblOrders, and tblUsers, to find out how much of any particular quota they've already used up in previous orders. Although that's only orders placed in the current year, mind. And of course I also now haveto pass the users ID in as a parameter so I can look up their order history.
Your head's hurting too, right?
So... this is where I've got to:
SELECT SUM(ticket_quantity) FROM [tblBasket]
INNER JOIN [tblTickets] ON [tblBasket].[ticket_id] = [tblTickets].[id]
INNER JOIN [tblOrders] ON [tblBasket].[order_id] = [tblOrders].[id]
WHERE
[tblTickets].[stand_id] = @standId
AND [tblOrders].[user_id] = @userId
AND ([tblOrders].[order_date] BETWEEN '2006/01/01' AND '2006/12/31')
What I want to do is incorporate that into the big SQL query up top, in such a way to make it only return tickets that not only match the ticket parameters but that also aren't linked to stands, admission dates or anything else, that the user has reached their quota on.
Oh, and I'm in a bit of a hurry so do try and get a move on won't you? ;)
But seriously - how do I put those two SQL querys together? Do I need one for each paramater that might have a quota attached, or is there a quicker way? All suggestions and advice, up to and including "get an easier job, dude", received gratefully :)
I don't want to any body can backup of my database, even i can also not able to take backup.
View 17 Replies View Relatedis there a way using sqlexpress to review the most recent set of sql commandssubmitted to a database?I remember something about setting up logging in sql server 2000, but don'tsee much about this on google or the news group for 2005.thanksJeff Kish
View 2 Replies View RelatedI have a scenario like below
Product1
Product2 Product3
Product4 Product5
Product1 1
1 0 0
1
Product2 1
1 0 0
1
Product3 0
0 1 1
0
Product4 0
0 1 1
0
Product5 1
1 0 0
1
How to design tables in SQL Server for the above.
Ok, I'm doing a football database for fixtures and stuff. The problem I am having is that in a fixture, there is both a home, and an away team. The tables as a result are something like this:
-------
Fixture
-------
fix_id
fix_date
fix_played
----
Team
----
tem_id
tem_name
-----------
TeamFixture
-----------
fix_id
tem_id
homeorawayteam
goals
It's not exactly like that, but you get the point. The question is, can I do a fixture query which results in one record per fixture, showing both teams details. The first in a hometeam field and the second in an away team field.
Fixture contains the details about the fixture like date and fixture id and has it been played
Team contains team info like team id, name, associated graphic
TeamFixture is the table which links the fixture to it's home and away team.
TeamFixture exists to prevent a many to many type relationship.
Make sense? Sorry if this turns out to be really easy, just can't get my head around it at the mo!
Hi,
Please give the T-SQL script for this ? Thanks
Shanth
Hi Gurus ....
Planning for a MDX Training for me and my development team ...
The following has been proposed for a training course spread over three days ...
I need to know whether this will be enough or should some more things be added on to the same (anything that has been missed)...
I have the option of removing and adding from the list.
Module 1: OLAP Review
OLAP Databases
OLAP Definitions
Warehouse Data Flow
Module 2: MDX Foundations
The Role of MDX
MDX Member Names
Using the Calculated Member Builder
Working with Calculated Members
Module 3: Using MDX Expressions
Displaying Member Information
Displaying Family Tree Relatives
Working with Member Properties
Using Conditional Expressions
Module 4: Retrieving Values from a Cube
Understanding Multidimensional Coordinates
Retrieving a Value from a Cube
Percent of Total Calculations
Growth Calculations
Module 5: Creating Simple MDX Queries
Understanding MDX Query Statements
Creating Simple MDX Query Statements
Module 6: Creating and Manipulating Sets
Using Set Creation Functions
Using Set Manipulation Functions
Using Subquery Set Functions
Working with Dimension Interactions
Module 7: Using Aggregation Functions
Understanding Aggregation Functions
Using Time Set Functions
Calculating a Trend
Module 8: Case Study - Implementing Budget Allocations
Allocating Values from a Grand Total
Allocating Values from a Subtotal
Allocating Values Across Multiple Dimensions
Module 9: Using Calculated Cells
Respose needed urgently since I have to get back to the training team by tomorrow morning
I'm making a site with a series of polls on it so it'll ask the user a lot of questions and it'll have an area where they can vote on new questions that will be added later. If a question gets X number of votes, it gets added to the appropriate poll. I'd also like to tell them at the top of every page how many questions they have not answered (so they can tell how many new ones have been posted). Right now, I'm just designing the database though.
I assume each poll could be a table and each question a column (where I can use one of the column fields to hold the question itself - can you query column descriptions?). Then the users table could link with each of them. I could expand each table as new questions are voted in. But if the number of questions goes into the hundreds, will that be a problem (number of columns)?
Anyone done something like this before?
hi all(happy raksha bandhan day)we have one of Automation software for sales running for acustomer.He was cool for the first month of product, but later poppedwith adding some extra fields.no problems i added in database , put aseperate code in my application for that field.but later every 2 dayshe was adding new fields.....so i thought to put in some inbuilt logicuser defined fields.second his user defined fields are like shoudl benumeric,string , length validation.But do not know whats the best wayto acheive this.I mean should i make seperate table where i definefield name, data types , validation and then in my application code ageneral logic for it in my application code.Any one has prooven designfor user defined fields,just thinking if i can even get a idea.....When i die i die programming........
View 2 Replies View RelatedThanks for looking at this for me. Here is the SQL Script as it stands so far.
UPDATE TableImportHistorySET ImportStopTime = CONVERT(numeric, GETDATE(), 108), Elapsetime = CONVERT(numeric,GETDATE(), 108) - CAST((SELECT TOP 1 ImportStartTime FROM TableImportHistory WHERE TableName = 'ARCHRG'ORDER BY DataImportID DESC) AS numeric), Successful = 1WHERE (DataImportID = (SELECT TOP 1 DataImportID AS LastID FROM TableImportHistory WHERE TableName = 'ARCHRG') ORDER BY DataImportID DESC))
The Elapetime update portion of this statement is where I'm getting stuck (if I take it out the statement works)
I get the error "ERROR converting Data type NVARCHAR to Numeric" I've changed the data type in the Table from numric to varchar, nvarchar, decemial nothing works and not sure what I should set the Starttime and StopTime datatypes in the table to (any suggestions please)
Elapsetime = CONVERT(numeric,GETDATE(), 108) - CAST((SELECT TOP 1 ImportStartTime FROM TableImportHistory WHERE TableName = 'ARCHRG'ORDER BY DataImportID DESC) AS numeric)
Are there better ways to do this statement?
Thank you in advance
Rex
Hi,
I posted last year when we were having problems with a new SQL box we had moved to here...
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74650
...we coded round most of the issues and deadlocks and things seemed to improve for quite a while but we have recently run into performance problems again of late.
The CPUs on our SQL box often thrash away at 100% and our ColdFusion website has begun running painfully slow.
Our developers/hosts have suggested we might need to look for a 3rd party SQL guru who could look at the SQL box, do some tracing etc. and perhaps make some recomendations around optimising the DB architecture or changing the way we run certain queries. At the moment we have only gut instinct on which bits of the DB are likely to be being hit most hard.
Our website has grown from being very small to being really quite busy and it's possible we are running into shortcomings with the design of the DB that need to be tackled before we can expand further.
I'm not sure what the protocol is (I see there is a Jobs part of the site) but I wondered about the possibility of getting one of you guys in for a short while to review our server and database, for a fee of course. I'm not sure how long it would take to review that kind of data and get a feel for the usage?
We are based in the UK and whilst I guess it could be done remotely it might be easiest if the person was UK based too.
I'm as much interested in feedback about the idea (it might be not workable/a good idea for example) as I am to people offering their services.
If this post breaks any rules please let me know.
Cheers,
Tem
Don't know if this is the appropriate forum. I am looking for an experienced SS consultant to review our setup, hardware architecture, recovery plan, and to provide high-level advice moving forward. My company is a CRM hosted software provider with a dynamic, metadata-based product built in Visual Studio 2005. Currently we run on SS 2000, but plan to migrate to SS 2005 or 2008. We anticipate quite a bit of growth and want to make sure that we are on the right path. Let me know if you are interested or know someone who is.
If I should post this elsewhere, let me know.
Hi all,
I'm a beginner to Report Services, and have tons of questions.
Here's the first one:
if the reports are created based on the condition that the user selects, how can I create the reports with Report Services?
For example,
the user can select the fields that will be shown on the reports, as well as the group fields, the sort fields and restrict fields. So I would not be able to pre-create all possible reports and deploy them to the report server, and I think I should create the reports dynamicly based on what the user select.
Could someone tell me how to do it (create and deploy the reports)?
Thanks a million!
Jonee
Hello
This is my 1st attempt at designing a database, and I have not finished
it completely, but I would like somebody to review and critique for me.
I really don't want to make any mistakes with this and I would appreciate any expertise out there to direct, recommend, suggest improvements and/or changes, PLEASE.
Thank you for considering this,,,if you provide me with your e-mail, I can send you a script.
take care,
~Iris~
:)
I'm trying to run the query given below but iFile.Pathname is underlined in red.
Query: SELECT top 1 iFile.Pathname AS 'PathName' FROM Videos
Error: Cannot find either column "iFile" or the user-defined function or aggregate "iFile.Pathname", or the name is ambiguous.
Do I need to run a procedure?
Hello,
I've proposed to the head of IT at my organisation to head an database development export group to:
- Provide assistance in improving the performance of existing relational databases
- Provide assistance for the development of new database, e.g. correct construction of indexes; data contention, etc.
- Creation of database development standards
- Enforce the above standards for new databases so that the database is not deployed to production servers until it passes the standards.
I'd really like this to be a success as it should improve our rather crappy databases, basically because the people currently creating the databases don't know much about relational design.
Can anyone please post their experiences of setting something like this up, or working on such an expert group where they work? I'd really like to hear the good and bad experiences and what and (what not) to avoid.
Thanks
I have an automation project to review the software. The software is using MSVS2012 and uses DBs from MSSQL2012. Is it possible to restore the DB files on my local laptop to review the code completed thus far? If so what other software would I need besides MSQLserver 2012 on my local laptop. I'm able to use MSSQL server 2012 on my local laptop, but the DBs are on servers.
View 4 Replies View RelatedHere is a script I wrote that analyzes datasets and returns all the minimal composite and unary keys that uniquely identify records. I wrote it because I frequently have to analyze client spreadsheets and non-normalized data tables.
On my desktop server it took about two minutes to analyze 2000 permutations of a table with 50 columns and 5000 records.
Please try it out for me and let me know if it chokes on anything, or if you see any ways it could be improved!
My name is W. Curtis Preston, and I'm the author of "Backup & Recovery"(formerly "Unix Backup & Recovery") from O'Reilly & Associates.(http://www.oreilly.com/catalog/unixbr/ )We're updating the book for 2006 and adding a chapter on SQL Serverbackup and recovery. Someone has already written what appears to be astrong chapter. The problem is that I'm not qualified to review it,since I'm not that familiar with SQL ServerCan anyone in this group step up to the plate and review the SQL Serverchapter? You would be mentioned in the acknowledgments of the book andreceive an autographed copy of the book.We're looking for two tech reviewers.
View 1 Replies View RelatedHi,
I'm having 2 issues on my first "real" SSRS report.
1. How to do a print preview via the preview tab. For print rendering, I have no print preview button on the preview toolbar. Is this normal? MS documention said that there is a Print Preview button. (BOL May 2007 - Designing & Creating reports -> working with report designer -> debugging and Publishing reports (Print Preview)) This report will always be rendered via the printer and it is a "pain" to have to always print it to see changes. The print layout option comes up all black with nothing viewable.
2. How do I reset page numbers back to 1 for report groups? I see no option for this. My report group requires a new page and I want page numbering to start at 1 when the grouping changes.
Note: I'm running VS2005 via Vista (with VS2005 Vista fix installed). SQL2005 SP2 also installed.
Any advice on this will be appreciated. I've spent too much time already where this should be obvious.
Not seeing the Review Data Type Mapping Screen in SQL Server Import and Export Wizard?
Is there only a certain version where that screen shows up?
I am trying to import data from an MS Access application to SQL Server and all of the connections are good, but some of the data isn't and if I let it migrate using this tool it crashes on the bad data and there is no data that migrates. The Review Data Type Mapping screen will allow me to bypass the records in error and load the rest. however, I can;t do that if I cannot see the screen.
I actually work in an organisation and we have to find a solution about the data consistancy in the database. our partners use to send details to the organisation and inserted directly in the database, so we want to create a new database as a buffer database to insert informations from the partners then make an update to the main database. is there a better solution instead of that?
View 6 Replies View Related
Hi there,
I want to create database login and set that login to owner of the database? Can anyone help me?
thanks
Hello everyone,I have a webcontrol that uses database-structures alot, it uses the system tables in SQL to read column information from tables. To ease the load of the SQL server I have a property that stores this information in a cache and everything works fine.I am doing some research to find if there are anyway to get information from the SQL server that the structure from a table has changed.I want to know if a column or table has changed any values, like datatype, name, properties, etc.Any suggestions out there ?!
View 3 Replies View RelatedI have a system that basically stores a database within a database (I'msure lots have you have done this before in some form or another).At the end of the day, I'm storing the actual data generically in acolumn of type nvarchar(4000), but I want to add support for unlimitedtext. I want to do this in a smart fashion. Right now I am leaningtowards putting 2 nullable Value fields:ValueLong ntext nullableValueShort nvarchar(4000) nullableand dynamically storing the info in one or the other depending on thesize. ASP.NET does this exact very thing in it's Session State model;look at the ASPStateTempSessions table. This table has both aSessionItemShort of type varbinary (7000) and a SessionItemLong of typeImage.My question is, is it better to user varbinary (7000) and Image? I'mthinking maybe I should go down this path, simply because ASP.NET does,but I don't really know why. Does anyone know what would be the benifitof using varbinary and Image datatypes? If it's just to allow saving ofbinary data, then I don't really need that right now (and I don't thinkASP.NET does either). Are there any other reasons?thanks,dave
View 7 Replies View RelatedHi All,Can u please suggest me some books for relational database design ordatabase modelling(Knowledgeable yet simple) i.e. from which we couldlearn database relationships(one to many,many to oneetc.....),building ER diagrams,proper usage of ER diagrams in ourdatabase(Primary key foreign key relations),designing smallmodules,relating tables and everything that relates about databasedesign....Coz I think database design is the crucial part of databaseand we must know the design part very first before starting up withdatabases.....Thanks and very grateful to all of you....Vikas
View 3 Replies View Related Cannot open user default database. Login failed.Login failed for user
'NT AUTHORITYNETWORK SERVICE'. 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: Cannot open user default database. Login
failed.Login failed for user 'NT AUTHORITYNETWORK SERVICE'. I have This Error When i try to log into My online web site, i have no idea how to fix it,one day it was working and the next it wasnt, is there any way to find out what database the default is and if it's either incorrect or not present change the web.config in a way that will make my system work. i have the NT Authority/Network Service in my Server Properties Permissions, its given the type login and is granted Connect SQL by sa i have 3 colder copies of the web site on my server my question is, how would i use of of these to restore the original site configuration is there a way to restore the original configuration to undo whatever it is i've done to break the system ChrisStressed
Hi, I'm new to SQL Express 2005.
I found information regarding : "Cannot open user default database. Login failed."on this forum but I think that in my case it's a bit different issue.
I have a website (ASP.NET 2.0) accessing DB, in the mean time Windows Service tries to update some data in the same DB (Service runs as NT AUTHORITYLOCAL SYSTEM). The second connection is rejected: "Cannot open user default database. Login failed.Login failed for user ....".
Problem occurs only when both: service and website are running at the same time. So service and website are running without problems when they are connecting DB exclusively.
My connection string is:
"Data Source=.SQLEXPRESS;AttachDbFilename="|DataDirectory|spider-lab.mdf";Integrated Security=True;User Instance=True"
I would be grateful if you can help me
Running a report on sqlserver RS 2008 R2 that uses a data source that looks at a database on a sql express 2012 server.
I can run the report in preview mode from bits on the sql 2008 r2 server.
I have tested the connection of the deployed data source on the sql 2008 R2 reporting services web page and connection has been successful.
I can logon to sql express 2012 using management studio and logon as the user and access the database run stored procedures etc.
windows server 2003, sql server 2008 R2 reporting services server.
windows server 2012 sql express 2012 data source database location.
Hi,
I'm sorry if this is simple, I'm no DBA but have been tasked with solving this problem...
We have a website that connects via ODBC to SQL Server (2k sp1) and at the moment I am getting back about every other time:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open user default database. Login failed.
So this is NOT happening every single time. Now I have seen Microsoft KB - 307864 and I can see that none of the databases are marked as suspect, the database I am trying to connect to does exist and is attached and, I have run the command to switch the database to multi-user mode.
The probable cause of this problem is that a while ago we had a hard-drive failure and I was forced to reattach some old datafiles (mdf,ldf) as the database. This seemed ok and I can view data etc in enterprise manager no problem.
I have checked for orphaned users and the user I am logging in with from the webpage is not listed.
So does anyone have a clue as to why this is happening, and more frustratingly for me, why is it only happening some of the time.
Thanks for your help, appreciated.
James.
I am using SharePoint Services 3.0 (SP1) with default configuration options, which installs the Microsoft##SSEE instance of SQL to my local C: drive.
While attempting to relocate the files to another drive, I set one of the databases (as recommended) to Single User by using the SQL Server Management Express tool.
I cannot now reset that database to Multi User, even by executing the query
exec sp_dboption 'database_name', 'single user', ''FALSE'
again by using the Management Express Tool.
Can someone please help, in plain english???? Thanks