Help In Setting Up Tables
Jul 20, 2005
I need to develop a Cold Fusion application using SQL tables, I am not sure
how to setup my tables or that this is the optimal way of setting my tables
for the application that I am trying top automate.
I've first created 2 tables as follows:
SOSC
-----
1 Record ID (Primary key)
2 CMP_TIME_ID (Foreign key)
....
8 item 8
SOSCF
-----
1 CMP_TIME_ID (Primary key)
2 FAX_TIME
3 FAXED_BY
This is my first foray into creating primary/foreign keys, linking tables,
utilizing SQL. (I am not very sure the above was a good idea so I unified
the two tables, and got rid of the foreign key CMP_TIME_ID. If some kind
person that utilizes SQL and CF on a daily basis would be of help it would
greatly help me get up the speed on both SQL as well as CF.
SOSC
-----
1 Record ID (Primary key)
2 FAX_TIME
3 FAXED_BY
4 SO
....
11 item 11
This will be an intranet web based page where end users are adding to the
above table information about a task (service order) that they are
completing. The completion needs to be faxed to customer and the requirement
is to fax completion as they are being completed in the following agreed to
times, 10AM, 1PM, 4PM, 5PM, 8:30PM and 10:30PM.
I hope I did the right thing in getting rid of the second table, I think
this will make my application easier to develop, and easier to maintain.
Also I believe the queries will be easier to develop since I may want to be
able to look at any given fax time, that is look up all the service orders
that were completed for a given fax time.
View 1 Replies
ADVERTISEMENT
Sep 12, 2005
I have 2 related tables I want to include in a single full text search. How would I go about setting this up?
View 1 Replies
View Related
Mar 12, 2014
I am creating a small piece of software which handles a pre-advice data file, several scanner inputting weights and a supplier weight report.
The pre-advice contains a list of barcodes and details revolving around that barcode, each barcode will be scanned and a weight will be obtained, this will be place in the DB.
Currently my tables can be summarized like this.
Pre-Advice Table
Barcode
Lots of other info…
Scanners Table
Barcode
Date
Time
Weight
Which device input this information
Suppliers Weight Table
Barcode
Date
Time
Weight
Here is the process(es):
1.Pre-advice table populated.
2.When the barcode is scanned and a weight is obtained, a record will be added to the scanners table and this becomes the ‘active’ weight.If there is already an ‘active’ weight against this barcode the user will be asked if they wish to use the ‘active’ weight already in the system or update the ‘active’ weight to the weight they have entered.
3.Then when the supplier weight report is inputted into its table, should the ‘active’ weight be less than the supplier weight, the supplier weight becomes the ‘active’ weight.
At any given time a user can pull a report based on information in the Pre-Advice table, which will need to include the ‘active’ weight for that barcode’s record.
Additionally, no weights should be deleted, I need a fully traceable log of who, what, where & when. I will need to pull reports on the trials of scans so it really is a must to keep these.
Furthermore, if a scanner was to scan the same barcode after the Suppliers weight had been input and the user chooses to replace the weight then, the scanners weight should become the new ‘active’ weight.
I believe I will need to modify my tables to accommodate my needs, however I am not sure exactly what or how to set up what I need.I understand I will need some type of relationship between all of the tables to be able to pull the report on the Pre-Advice table, but I am lost.
how I will need to set up my tables/relationships in order to achieve my requirements?
View 3 Replies
View Related
Mar 22, 2007
Hi,
How can I set the permissions on all tables in a database to be exactly the same for 1 user name? Let's say I need all the tables in the DB to allow the user to Select,Update,Delete. Can this be done without going to the properties of each individual table?
Thanks,
Razzle
View 2 Replies
View Related
Apr 27, 2006
Due to bad programming techniques and legacy databases, my company has a large database with all the tables in it. I would like to start moving these tables out of the database and into more appropriately named databases.I was wondering if there was a way to set an alias up so that, when the table is moved, there will be no problems with out code still trying to access the table from the old database.Maybe an example is the best way to explain this. Lets say we have a DB_Company database with table T_Customers, T_Suppliers, etc.Now, as the company grows, we decide to create a dedicated database for all the customers. So we create a database DB_Customers and move the T_Customers table into it.The problem is that they may be some code that is still trying to access the DB_Company.T_Customers table. Is there a way of setting an alias up so that, when someone tries to access the DB_Company.T_ Customers table, he is automatically redirected to the DB_Customers.T_Customers?Hope that's not too confusing :-(Jag
View 2 Replies
View Related
Aug 22, 2006
hi! I've been using sql server for a while but until recently have kept things pretty simple. now I'm trying to expand my horizons by trying to tackle some more complex applications, and one I'm really struggling with is nested relations. I hope this is in the right forum; if it is not, please feel free to move it, thank you!
here is my problem: I'm desigining a simple "Downloads" page in asp.net, and I have two tables set up. One is Downloads and the other is DownloadCategories. here is a simplified layout of the tables:
Downloads:
ID PK
Title
Description
CatID FK
DownloadCategories
ID PK
Name
Description
ParentID FK
basically CatID in downloads is a foreign key to the ID in downloadcategories, and ParentID is a foreign key to the ID in the same table, downloadcategories. This is set up because I want to support an infinite number of categories, each being able to support their own subcategories, which can go deeper into more subcats, and so on...
the problem is that I can't seem to get them to fill into the dataset I've created in vs 2005's designer. I have a procedure SelectAll which retrieves all rows, and SelectMain which retrieves only the topmost categories (where ParentID=Null). If I fill the datatable with SelectMain, I don't get any of the child categories, and if I call SelectAll, I get just a single table with all the rows, but no relations.
I have defined a relation in the datatable that mirrors that of the database, but no matter what I try, I cannot get it to show the relationship in the datatable when I fill it. am I doing something wrong? this is kind of how I have it setup:
dataset with Downloads and Categories datatables, relations from Categories to Downloads, and from Categories to Categories. I have the two queries added to the table adapter, and I call the SelectAll query to fill it, but all it does is fill the table with rows; it doesn't create any relations.
I hope this explanation of my problem makes sense. as I said I'm still very new to this complex stuff, and I'm hoping to get my head around it soon, because I really need the functionality (not to mention the skills!) so if you can take a moment to go over what I've explained and point out where my flaw might be, I would really appreciate it!!
if you need any more information to help, please let me know and i'll get right back to you. thanks a bunch!
-SelArom
View 3 Replies
View Related
Oct 16, 2006
I need to set up 1 new user in SQL Server 2005 to be able to read specific tables in a db (db1).
The user will connect from MS access using odbc links (SQL Native client ot SQL Server driver)
I've tried to set up one and once logged on from the user workstation, I can only see sys. tables and INFORMATION_SCHEMA tables.
None of the required db1 tables appear.
under Security/Logins I've created User1:
SQL Server auth. with password
default db = db1
server_roles = none
user mapping = map, db1, user1,dbo
securables = none
status = grant, enabled
on the access db, the odbc link was set up with default db = db1
Why can't I see any of the db1 tables?
How can I restrict access to the sys. tables?
Thank you
View 3 Replies
View Related
Oct 22, 2007
In my ASP.NET app, I'm executing a stored procedure via a SQLCommand the searches a customer database. I believe the default timeout is 90 seconds. I'm curious of what happens to the SQL Server Stored Procedure after timing out from the ASP.NET application. Does it timeout at the same time or do you have to set up a value in SQL Server?
View 1 Replies
View Related
Jan 24, 2008
I'm new to my company, although not new to SQL 2005 and I found something interesting. I don't have an ERD yet, and so I was asking a co-worker what table some data was in, they told me a table that is NOT in SQL Server 2005's list of tables, views or synonyms.
I thought that was strange, and so I searched over and over again and still I couldn't find it. Then I did a select statement the table that Access thinks exists and SQL Server does not show and to my shock, the select statement pulled in data!
So how did this happen? How can I find the object in SSMS folder listing of tables/views or whatever and what am I overlooking?
Thanks,
Keith
View 4 Replies
View Related
Dec 7, 2006
We have 20 -30 normalized tables in our dartabase . Also we have 4tables where we store the calculated data fron those normalised tables.The Reason we have these 4 denormalised tables is when we try to dothe calcultion on the fly, our site becomes very slow. So We haveprecalculated and stored it in 4 tables.The Process we use to do the precalcultion, will get do thecalculation and and store it in a temp table. It will compare the thetemp with denormalised tables and insert new rows , delte the old oneans update if any changes.This process take about 20 mins - 60mins. Ittakes long time because in this process we first do the calculationregardless of changes and then do a compare to see what are changed andremove if any rows are deleted, and insert new rowsand update thechanges.Now we like to capture the rows/columns changed in the normalisedtables and do only those chages to the denormalised table , which weare hoping will reduce the processing time by atleast 50%WE have upgraded to SQL SERVER 2005.So We like to use the newtechnology for this process.I have to design the a model to capture the changes and updated onlythose changes.I have the list of normalised tables and te columns which will affectthe end results.I thought of using Triggers or OUTPUT clause to capture the changes.Please help me with the any ideas how to design the new process
View 3 Replies
View Related
Jan 31, 2004
Hello, I am a SQL rookie. I have followed the tutorial and installed MSDN as it says. However, I am unable to create a database with WebMatrix. I keep getting an error that reads "SQL Server does not exist or access denied. ConnectionOpen (Connect())."
When I loaded the SQL, everything seemed to go well. I got all of the results that the tutorial said I should.
Please advise on my next steps. Thank you in advance.
JS
View 13 Replies
View Related
Jan 17, 2006
If you have a SQL server setup that isn’t using a SAN but just a normal raid array and you wanted to utilize a SAN now.
What are the steps, I'm guessing that you will need to start the services under a domain user correct?
Is it as easy as detaching the DB and then reattaching the DB on the SAN?
I'm assuming you need to map the data and log arrays from the server to the SAN correct?
I can’t seem to find any documentation on moving a server to a new SAN. Can someone post links if you have them?
Do you need ENT edition or is Standard OK .
Thanks for your help.
View 8 Replies
View Related
Feb 20, 2006
Hi
I'm trying to set up a new job to update a field in the table, I've managed to get the select syntax to work, but when I added an IF statement and Update syntax it didn't like it and the following error was shown:-
Server: Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'BEGIN'.
This is the syntax I'm trying to use for the job:-
USE EmployerEngagement
IF (SELECT On_Stop
FROM tblEmployer LEFT OUTER JOIN tblWP_Details
ON tblEmployer.Emp_ID = tblWP_Details.Emp_ID LEFT OUTER JOIN tblVetting
ON tblWP_Details.Record_ID = tblVetting.Record_ID
WHERE tblEmployer.On_Stop = 0
AND tblVetting.Next_Vett_Date <= GETDATE())
BEGIN
UPDATE tblEmployer
SET On_Stop = 1
END
Basically I just want to change the On_Stop value from 0 to 1 if the Next_Vett_Date is before or on todays date.
Am I using the wrong syntax for this?
Thanks
View 2 Replies
View Related
Jul 30, 2007
How do i do it? do i have to set up an ODBC?
View 20 Replies
View Related
Jul 20, 2005
Hello,I've noticed on my DTS that it is connecting to the remote SQL Servervia Named Pipes. Not sure why I've been getting the following error:[DBNETLIB][ConnectionWrite (WrapperWrite()).] but I wanted to changethe DTS from using Named Pipes to use TCP/IP. How to do it?Also, if anyone has any clues on what this error means, please let meknow!Step Error Source: Microsoft Data Transformation Services (DTS) DataPumpStep Error Description:The number of failing rows exceeds the maximumspecified. (Microsoft OLE DB Provider for SQL Server (80004005):[DBNETLIB][ConnectionWrite (WrapperWrite()).]General network error.Check your network documentation.)Step Error code: 8004206AStep Error Help File:sqldts80.hlpStep Error Help Context ID:0Tnks!
View 1 Replies
View Related
Oct 10, 2006
I am trying to connect to my database in SQL SERVER EXPRESS via dreamweaver. I am trying to create DSN connection but when i click on sql server in the optionlist it lists about 100 options of the sql server i want to connect to. How do I find out which one is the one I have my tables in and the sql server express that I use.
Cheers
View 1 Replies
View Related
Jun 20, 2006
I am unable to set the value of my SqlParameter unless it is to a string.sqlCmd.Parameters.Add('@myParameter', SqlDbType.Bit).Value := false;
Setting it to false or 0 results in error: "Incompatible types: 'Object' and 'Boolean' (or 'Integer')"
I've tried to cast them as objects and that didn't work. I don't understand why something like 'false' (as a string) will.
FYI: I'm using Delphi.NET and hating it.
View 1 Replies
View Related
Aug 27, 2006
sry im an idiotbut how do u take like "SELECTbla FROM blar WHERE blam=blfda" and make the thing it "selects" into a variable thnx
View 5 Replies
View Related
Dec 30, 2006
Hi, I have just started with ASP, and have a problem. I have created a stored procedure which looks to a specific tablename for information, based upon the users choice from a dropdown list.
The control works fine when executed from within visual web developer, and I manually enter the value that the variable expects. However I can not get the dropdown listbox value to be written to the SQL value. I have tried for days, traweled the net for answers, borrowed 3ft in height of SQL books! so either I am doing something fundamentally wrong, or I am missing something. My SP is:
ALTER Procedure GenericTableSelect
@tablename VarChar(20)
AS
Declare @SQL VarChar(1000)
SELECT @SQL = 'SELECT [base model] FROM '
SELECT @SQL = @SQL + @tablename
Exec ( @SQL)
and from the page the command to call it is:
SelectCommand=generictableselect></asp:SqlDataSource>
But this fails to compile and comes back with "@tablename not defined"
any pointers in the right direction would help.
The object of this is for two drop down boxes - the first is populated from one database of categories, the selection of which populates the second drop down list with items from within that category.
Cheers,
Richard
View 2 Replies
View Related
Jan 8, 2007
Hello all,
I've gotten approval at my work place to implement a new web server cluster. We currently have one web server running Windows 2003 and one SQL 2005 box. I've gotten approval to purchase 2 new web servers that will eventually become our primary servers.
I've browsed Microsoft's site but can't seem to find any FAQ's or How-Tos on clustering that are of any help. Is there anyone out there who has some info that could help me get started?
Thanks.
Richard M
View 4 Replies
View Related
Apr 7, 2007
I have been using MySQL as the back end of my .Net 2.0 applications for a while using a custom membership provider without any problems.Having now switched to a new webhost which supports both MySQL and MS SQL Server 2005 and also creating a new site thought I should set up the new site to use MS SQL..... I am having trouble however setting up the SQL server to act as the membership provider. Following the advice on here I tried using aspnet_regsql.exe... Firstly, when trying to get the list of databasses on the remote server I get the following error: Failed to query a list of database names from the SQL Server. Invalid object name 'Sysdatabases' If I then just type in the name of my database and go through to the end I get the following error:Exception:An error occurred during the execution of the SQL file 'InstallCommon.sql'. The SQL error number is 8152 and the SqlException message is: String or binary data would be truncated. I can connect to my MS SQL server remotely without any problem using the likes of MS SQL Server Management Studio Express. Is there any other way I can setup the databases needed for user and roles access control or is the usage of MS SQL much more complicated than what MySQL was?
View 4 Replies
View Related
Jun 11, 2007
I've received the error - Setup failed to configure the server. Refer to the server error logs and setup error logs
View 3 Replies
View Related
Jan 19, 2004
Hello
my host required to have a fix IP in order to access his SQL Server database.
I got the fix IP, but I don't know where to define the port number while creating a SQL Server connection in Enterprise Manager.
Anyone knows ?
Thanks a lot
Johann
View 1 Replies
View Related
May 23, 2005
hey guys heres my problem iam trying to install SQL server2000 but i keep on running into problems....??
here is what iam having problems with
setup SAPWD="AStrongSAPwd" SECURITYMODE=SQL
it keeps saying the instance name specified is invalid there has to be an easyer way to run the program...??
i have all ready set up an instance name and the SAPWD.
any suggestions?? please help
View 2 Replies
View Related
Feb 2, 2006
I have the following to create a new table....
CREATE TABLE dbo.test (Id varchar(20) NOT NULL PRIMARY KEY, Name varchar(256) NOT NULL, visible bit NOT NULL )
I need to set the default value of the bit visible to 1.
How can this be done using a T-SQL statement?
Thanks,
Zath
View 1 Replies
View Related
Jul 15, 2002
Hi all:
How can I change the Default value of a column in a table using sql?
for instance I have a table called Phone and I have a field called AreaCode and the default now is '123' . I would like to change it to '456'.
Thanks,
Nisha
View 1 Replies
View Related
May 2, 2000
Can someone point me in the direction of a good place to find out how to set this up properly please.
thanks
View 1 Replies
View Related
Dec 4, 1999
Hi
I am using Windows NT 4.0, SQL Server 6.5 and Exchange 5.0. I would like to setup SQL mail.
I tried to create mail login, NT expects for MS mail server name and login.
When i enter my exchange server/login name does not recoganise.
Any guide please.. Thanks in advance
Moorthy
View 2 Replies
View Related
Apr 26, 2006
I am getting the message
DESCRIPTION: Error: 1204, Severity: 19, State: 1 The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.
it is a 3rd party app I can't change with some ugly sql.
My current lock setting is
min max config run
locks 5000 2147483647 0 0
Do I need to change the min value to higher? What would be a good value to try, I have 16 gig of memory on the server
View 5 Replies
View Related
Aug 31, 1998
Hi,Patrick and VK
Thank you for your help!
the setting is follow : system account(service in control panel)
create a NT user : netbackup(privilege SA in SQL security manager)
remote server setting->
remote server login name: netbackup
mapped login name : sa
I am unclear the setting! Please tell me the detail!
best regard!
Steven/98.9.1
View 1 Replies
View Related
Jul 27, 1998
Hello, Iam new at this SQL stuff and want to setup an SQL Server at work and need to know what do i need to use this software. I have a EVAL version of 6.5 and was told you need MSACCES or Visual Basic. Is there anything else that i need to use this? Thanks!!
View 14 Replies
View Related
Jul 11, 2006
Does anyone know if it is possible to set permissions on certain fields in a way that will restrict access for reading a field but will allow a user to do a select using that field? I am looking for a way to block read access to a sensitive field but I want to be able to query other fields using the value that is in the field.
Thanks.
View 2 Replies
View Related
Jun 6, 2006
Hello, I am trying to setup a Microsoft Express SQL 2005 server to be accessed by PHP. PHP is setup just fine, the problem lies in the database (or so I believe). Quite simply, the problem is that it won't connect. I need to know if I need to have any special configuration on the database for it to connect. I am fairly positive that I have all of the login information correct, so that is not the problem.
View 3 Replies
View Related