I am creating a simple application form using visual studio 2015. I can create database.mdf successfully and create dbo.table successfully. but when i tried to view table by expanding the table icon on the server explorer, the table should be able to show list of table but it didn't show any record and why is it like that.
View 4 RepliesI have replaced the installation of MSDE 1.0 with MSDE 2000.
Prior to installation of MSDE 2000, I can perform the activities of all
database role. Now, I can only add, change and delete data - no
more database design.
What should I do to regain database design permission?
I'm using "sa" user with blank password when I login to MS Access Project.
How do you get the design view of a table in sql server query analyser?
I know in oracle its desc table_name.
Thanks
Help, is something wrong with my SL Server? I am unable to return any rows from all tables in all databases (user and system)on My SQL 7.0 SP2 machine. Whne i right click on the table in E.M and select design or open table i get no results. Does anyone know why this is happening? It did not always happen either.
Thanks
I have a database called ‘Objects’ which has many field. One of its fields is called ‘Image’ and has a data type image.
I want to add pictures to each one of my records offline, is this possible? i.e. by copying the address from my C drive such as C:Documents and SettingsfseyedarabiMy DocumentsMy Pictures
I renamed a database table, and then try to create a view with that table. However, the renamed table does not appear in view designer. I have to close and re-open SQL Server Management studio 2014.
View 5 Replies View RelatedHi All,
I have a view that contains 30 million records.I want to move the view to a table in my database using DTS,but it is taking a lot of time,and making my tempdb to grow fast in giga bytes.Please is there anyway i can copy this view into the table easily in minutes.The view structure and the table structure are the same.Also, how can I index a view and can I add unique key to a view.
Thanks All in advance.
Mokah
I have a problem with inserting records into table when an indexed viewis based on it.Table has text field (without it there is no problem, but I need it).Here is a sample code:USE testGOCREATE TABLE dbo.aTable ([id] INT NOT NULL, [text] TEXT NOT NULL)GOCREATE VIEW dbo.aViewWITH SCHEMABINDING ASSELECT [id], CAST([text] AS VARCHAR(8000)) [text]FROM dbo.aTableGOCREATE TRIGGER dbo.aTrigger ON dbo.aView INSTEAD OF INSERTASBEGININSERT INTO aTableSELECT [id], [text]FROM insertedENDGODo the insert into aTable (also through aView).INSERT INTO dbo.aTable VALUES (1, 'a')INSERT INTO dbo.aView VALUES (2, 'b')Still do not have any problem. But when I need index on viewCREATE UNIQUE CLUSTERED INDEX [id] ON dbo.aView ([id])GOI get following error while inserting record into aTable:-- Server: Msg 8626, Level 16, State 1, Procedure aTrigger, Line 4-- Only text pointers are allowed in work tables, never text, ntext, orimage columns. The query processor produced a query plan that requireda text, ntext, or image column in a work table.Does anyone know what causes the error?
View 1 Replies View RelatedI have a question regarding table design (I'm using SQL Server Express 2005). Is it better and is there a performance gain by using layout # 2 instead of layout # 1? Someone told me it was better to divide everything up but it seems kind of useless here, plus it complicates the handling. So I want to know which one is faster (or better) considering large amount of records. Thanks! Layout # 1Objet==========ObjetIDClientIDObjetTypeStateObjetNoTitleDescriptionStartDateReturnDateEndDateLayout # 2
Objet==========ObjetIDClientIDObjetTypeStateDetailsObjet==========DetailIDObjetIDObjetNoTitleDescriptionStartDateReturnDateEndDate
Im trying to design a database to use in SQL server , the design i've got now is working but It just doesn't look right to me and there must be a better way to set things out.
Heres the current situation
Theres 3 tables
USERTYPES
USERS
TICKETS
The users table holds the user information i.e.
userID
userName
Phone Number
userTypeID *
It contains a foreign key called userTypeID which is a primary key of USERTYPES
The USERTYPES table contains the two types of users on the system , these are standard and admin, so the table looks like this.
USERTYPESID
typeName
The TICKETS table holds the ticket details of tickets submitted by users. so
ticketID
ticketName
ticketDescription
DueDate
ticketRequestor *
AssignedTo *
Now here is where I have the problem, the ticketRequestor is a foreign key of the STAFF table staffID however AssignedTo is also a foreign key of the STAFF table staffID.
Currently in order to get this working i've had to turn off the enforce relationship for the AssignedTO - staffID link but idealy I think this enforce relationship should be on to ensure its a truely relational.
any ideas as to what I need to do to organise the relationships better ?
thanks
Hi guys, may I know is it possible to create an update trigger like this ? Assuming there are two database, database A and database B and both are having same tables called 'Payments' table. I would like to update the Payments records on database A automatically after Payments records on database B had been updated. I can't use replication because both tables might having different records and some records are the same. Hope can get any assistance here, thank you.
Best Regards,
Hans
Hi all,
I have to come up with a design choice for a DB Table for a financial firm(i.e. Tables with Fixed column or Value-Pair).
Case: SQL Server 2005 is used as a back-end for a web application(which basically is designed for doing web based analysis). The business people are insisting on a feature that enable power users to add fields to the underlying tables. One option proposed is named value-pair design approach? But from performance point of view, this seems a nightmare. How can this be integrated into static-column table approach. FYI, security policy of the client do not allow the underlying table structures to be changed by the user and currently
there are 5000 fields, with a possibility to grow.
Thanks so much for your suggestions!!
Dilla
I have questions table and answers in survey database. Now I design responses table from users !
CREATE TABLE [dbo].[Response](
[ResponseId] [int] IDENTITY(1,1) NOT NULL,
[SurveyId] [int] NULL,
[QuestionId] [int] NULL,
[AnswerId] [int] NULL,
[VoterId] [int] NULL,
[Code] ...
My Questions : How can I store data if questions is multiply answers (checkbox type) ?What is best way ?
In the past, we had table names such as:
PurchaseOrders
SalesOrders
Now with SQL 2005, you can use schema namespaces, so it could become:
Purchasing.Orders
Sales.Orders
So essentially you can use exactly the SAME table names, in different schemas.
So... would you recommend to change to the last model, or just stay with the old way?
What is best practice?
One of the reasons why I ask this question is that I looked at the AdventureWorks sample database included with SQL Server 2005, and Microsoft is using:
Sales.SalesOrderDetail
Purchasing.PurchaseOrderDetail
etc...
Why not:
Sales.OrderDetail
Purchasing.OrderDetail
etc...
????????
Hi
I have a table (Sql server 2000) which has 14 cost columns for each record, and now due to a new requirement, I have 2 taxes which needs to be applied on two more fields called Share1 and share 2
e.g
Sales tax = 10%
Use Tax = 10%
Share1 = 60%
Share2 = 40%
So Sales tax Amt (A) = Cost1 * Share1 * Sales Tax
So Use tax Amt (B) = cost1 * share2 * Use tax
same calculation for all the costs and then total cost with Sales tax = Cost 1 + A , Cost 2 + A and so on..
and total cost with Use tax = Cost1 +B, Cost 2 +B etc.
So there are around 14 new fields required to save Sales Tax amt for each cost, another 14 new fields to store Cost with Sales Tax, Cost with Use tax. So that increases the table size.
Some of these fields might be used for making reports.
I was wondering which is a better approach out of the below 3:
1) To calculate these fields dynamically while displaying them on the User interface and not save in DB (while making reports, again calculate these fields dynamically and show), or
2) Add new formula field columns in database table to save each field, which would make the table size bigger, but reporting becomes easier.
3) Add only those columns in database on which reports needs to be made, calculate rest of the fields dynamically on screen.
Your help is greatly appreciated.
Thanks
Hi
I have a table (Sql server 2000) which has 14 cost columns for each record, and now due to a new requirement, I have 2 taxes which needs to be applied on two more fields called Share1 and share 2
e.g
Sales tax = 10%
Use Tax = 10%
Share1 = 60%
Share2 = 40%
So Sales tax Amt (A) = Cost1 * Share1 * Sales Tax
So Use tax Amt (B) = cost1 * share2 * Use tax
same calculation for all the costs and then total cost with Sales tax = Cost 1 + A , Cost 2 + A and so on..
and total cost with Use tax = Cost1 +B, Cost 2 +B etc.
So there are around 14 new fields required to save Sales Tax amt for each cost, another 14 new fields to store Cost with Sales Tax, Cost with Use tax. So that increases the table size.
Some of these fields might be used for making reports.
I was wondering which is a better approach out of the below 4:
1) To calculate these fields dynamically while displaying them on the User interface and not save in DB (while making reports, again calculate these fields dynamically and show), or
2) Add new formula field columns in database table to save each field, which would make the table size bigger, but reporting becomes easier.
3) Add only those columns in database on which reports needs to be made, calculate rest of the fields dynamically on screen.
4) Create a view just for reports, and calculate values dynamically in UI and not adding any computed values in table.
Your help is greatly appreciated.
Thanks
Hello,friends
1) CustomerID
2) FirstName
3) MiddleName
4) SurName
5) Title
6) Marital Status
7) Education
8) Occupation
9) Annual Income
10) Line of Business
11) DOB
12) Father Name
13) Mother Name
14) SpouseName
15) Gender
16) Email
17) MainTel
18) Home Tel
19) Passport Number
20)----------------------
21)- - - - - - - - - - -
100)-------------------
Above mentioned list is a snapshot of our customer master table ,which contain approximately 100 attributes related to a customer.
We are designing an application for banking sector (but NOT Core banking solution),for which we may need to capture variable number of addresses for bank's customer,i.e more then three types of addresses Fixed,Temporary and Communication addresses(which is generally the case with all banks).
A single address includes address1/address2/city/country/state/pincode fields.
In context of OLTP database,We have option to put multiple addresses in child table but that involves various joins at the time of data retrival and slow down the query.
As another option we can can create redundent addresses columns(address1/address2/city/country/state/pincode) in master table that will accumulate addresses if demand for more then three type addresses arises(although there is reasonable numer of extra addresses is expected, i.e 10)
Database is expected to serve the records of 25 million(approx) bank's customer,so does someone can suggest me how to maintan the balance between two approches.
Hi,Facts:I created a database to support an application that tracks events ondifferent objects. The two main tables are tbl_Object andtbl_EventLog. Each table has unique ID and on the tbl_EventLog thereis FK for a record in the tbl_Object. The events are inserted all thetime for the same or different objects from the tbl_Object. There areabout 600,000 objects in the tbl_Object and 1,500,000 (and growing)events in tbl_EventLog.Question:The user often wants to know what the last event was for a specificobject.What is the best way of retrieving the last event?Should I simply do a max(eventdatetime) on a specific object? orShould I add a LastEventID column to tbl_Object and update it everytime a new event is inserted? or any other way to implement it?I chose the second method because I didn't think it made sense searchthe event table everytime the user wants to know the last event, but Iwanted to know what the experts thought.Please let me know what you think.Thank you,Oran Levin
View 5 Replies View RelatedI would like to create a database that keeps track of our companies subject matter experts. I have roughed out some of the tables. I would appreciate any feedback on if this is the right approach and if there might be any issues when I start writting a front-end (probably VB 2005).
What makes this interesting (at least for me) is that a subject has an owner and at least 1 "expert", possibly up to 3. Here is what I am thinking for tables:
tblEmployee
EmployeeID (PK)
LastName
FirstName
etc......
tblSubject
SubjectID (PK)
Description
tblOwner
SubjectID (FK)
EmployeeID (FK)
tblExpert1
SubjectID (FK)
EmployeeID (FK)
tblExpert2
SubjectID (FK)
EmployeeID (FK)
tblExpert3
SubjectID (FK)
EmployeeID (FK)
Does this make sense? Would I run into any issues when trying to display this on a form in VB?
Thanks in advance for any help!!!!!
Cal
I know how to generate scripts that would contain insert queries of all records in all tables. But how can I limit this to top 10 records only from each table?I can select "Data Only" when generating the script, but it generates insert queries for all data. How can get the script to have only top 10 record insert statements? These top 10 should be ordered by the primary key.
View 4 Replies View RelatedGot a question here and as I am no expert programmer, this should be easy for you gurus. I have this fairly generic code I've created where I return data from an SQL table in a DataList control. I want to take it to the next level and return only the last record in the table, but I am unsure of how to do that. Perhaps I shouldn't even be using a DataList control, I'm not sure.
View 2 Replies View RelatedI don't work much with the back end of software development so there is a lot about SQL Server I do not know.
We are building a database. The database will have about 10 tables in it. 3 of these tables will probably have a huge amount of data in them. Specifically each one of the 3 tables will each have about a half a million database records in it. Each record is about 100 characters max in length.(Im am including numbers as characters and summing the individual columns/fields to come up with 100).
Will a SQL server database table with A half a million records in it be possible? We have tried to normalize the database to cut down on the size of the table but it all comes out to about a half a million records per table.
Any help is deeply appreciated.
Bill
I have two tables in my database: order_id with fields order (text) and comp_ID (int) and another table called customers with comp_ID (int) and company name (text) and other company information fields. The link between the two tables is the comp_ID. With every order that's made the company that made the order is stored with it in the order_id table. If I type in the order id (text), I want to be able to use the order id to search the order_id table and find out what the comp_ID of the company that made that order is. Then use that comp_ID to pull up the record of company information from the customers table with the same comp_ID. Is there some way to do this in one query? Or how do I accomplish this?
View 4 Replies View Related
hi all,
I need to select the no of records on the basis of specified range of records.
In oracle i found rownum, i could not find it in sqlserver. how the data are extracted from the huge records..
I have used temporary table,map the table primary key to the next table with identity
but i dont find it good. I need to ignore the insert the data in next table or craeting new table having the rowid ...
Is there some other best way to extract the specified data
here is the type of query.
select * from customers where rownum between 1000 and 10000
this is in oracle
i am in need to do this in the sql server
waiting for the response...............................
I'm using SQL Server 2005 Express.
In my main database table I have many fields but the two following fields are my main concern.
1) email_address
2) unsubscribe
In my secondary database table I have one record only.
1) email_address
What I want to accomplish... I want to compare the email_address of the secondary database table to the email_address of the main database table and if it exist, change the value of the unsubscribe field. (or if I can't do that, then delete the record within the main database table completely.)
I'd really appreciate any help I can get.
Thanks,
Bill
I'm trying to setup my foreign keys so I can use them in a VisualStudio projects but I'm getting an error and I can't track down what's causing it.
'tbl_departments' table saved successfully
'tbl_people' table saved successfully
'tbl_locations' table saved successfully
'tbl_position' table
- Unable to create relationship 'FK_tbl_position_tbl_departments'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_tbl_position_tbl_departments". The conflict occurred in database "MyProjectDB", table "dbo.tbl_departments", column 'ID'. All of my tables have an ID field that is a primary key with Allow Null unchecked.
,I am creating a database where each record is required to have a twin record in the database.These is a type a value and a type b value and both must be present for the record to be valid.
Customer_ID, Order_Type, Product_Code
54, a, 00345
54, b, 00356
Is this something that would have to be done programmatically, or is it possible to create a constraint of some sort to ensure this?
when I try to open the SSRS in BIDS ( VS 2008), it is displaying the code portion alone. If i click 'View Designer', it prompts me like "....already opened. Do you want to close?" Only XML content of report is displaying. if I click 'Viee Code' then it displays error like "There is no editor available for 'c: eport Sample. rdl'.Make sure the application for the file type (.rdl) is installed."how to correct this issue by installing or by upgrading?
View 6 Replies View RelatedWe have a vendor created database with 9000+ tables, one of which has about 6 billion rows. The vendor redesigned the database recently and ever since we've had terrible performance.
What the vendor did was increase any and all varchar columns (tens of thousands of columns) to 256.
Before the upgrade we had no problems creating an index on the 6billion row table, it would take 2 hours.
Now after the upgrade we've let the index creation command run for 5 days and killed it because it was consuming terabytes of logspace.
The previous design had combined column width of 1049 to what is now over 4000. The primary key itself is 1283 characters (SQL limit is 900).
There is no additional data, just wider columns. Why we are unable to create the index?
What is happening inside SQL Server? Does SQL make "room" in memory for the index for the entire width of the potential max row length?
Hi all, this is a really simple application to record results of a questionaiire. Basically, each question can have multiple yes/no type answers.
Any help appreciated. Also going to develop a c# front end.
hi
suppose that i have 2 tables:
TBCars(Keep information about one car like model,plaqueNo, owner,...)
&
TBServices(keep service information,like serviceType,serviceDate,..)
in this system, when a car for a first time enter to this system, i insert 1 record for it into TBCar, from second time, every time this car enter to system, one record inserted into TBServices(that refer to 1 record in TBCar,1-& relationship).
now i build a VIEW with these fields:
IDCar,Model,PlaqueNo,Owner,ServiceType,ServiceDate
the expected result from this view, return n record that n is number of records in TBServices. but i want 1 record in result for every car in TBCar.
in this result grid, serviceDate & serviceType is the last record inserted into TBServices.
& i want records in TBCars that dont have any child in TBServices come into my VIEW.
Is there a way to allow users to see the design view of a table withouthaving dbo permissions?Thanks
View 1 Replies View Relatedi have a question about updating views:for example: lets assume we have a table which keep the users score and we have a view which created from so many tables and calculate the sum of the users scores. now if one of the user's score changes all the view created again or just part of the view will update?i ask this to find out how can i design my database? how can calculate users scores?i can wrote a trigger to do it or use the view. which way is faster and better in this case?
View 9 Replies View Related