Fields In My Tables Are Disappearing!

Feb 24, 2006

I am using a hosted MSSQL 2000 database that powers the backend of my
website. Website visitors interact with it via ASP pages I have
developed. I also have an internal FileMaker 7 database that
periodically synchs with it via Filemaker's ODBC functionality.

Several times now, I have come in one day to discover that my ASP pages
don't work. When I look into it, one of my MSSQL tables will be missing
a few fields. There is nothing in any of my code that sends an ALTER
TABLE command or any other command that affects table structure. These
deletions of fields is totally random.

The most recent example was with a table called FreeTeacherSubs. I just
discovered today that three fields went missing:

HowDidYouHearAboutYES TEXT
FollowUpCallOrEmail TEXT
HowMaterialsFit TEXT

Suffice to say I am baffled! Has anyone ever experienced fields
disappearing from their tables?

Kevin

View 2 Replies


ADVERTISEMENT

SQL Security :: Permissions Disappearing From Tables And Views

Feb 26, 2014

As a CRM person, I sometimes have to update the database to cope with custom reports that I have written. Usually, I will

Go into SQL Server Management Studio 2008Open the database
Locate either the table or the view
Right click on permissions.
Add in the role or user that's needed
Grant them Select permissions

This allows the report in CRM to access the database and this works.

The problem I have is that after a certain amount of time, the user or role will disappear from the permissions meaning that the report will not run. Sometimes, this can be as soon as a couple of days or it can be months. It happens on views more than tables and, whilst it's not too difficult to put the permissions back, it is annoying for the users.

I don't use SQL Server Management Studio very regularly, only to write a few queries and set up these permissions so I'm not sure if there's anything else in there that might be doing this.

View 6 Replies View Related

Creating A Table In SQL Server With Fields From Other Tables And Some Fields User Defined

Feb 20, 2008

How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu".
can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.

View 8 Replies View Related

Mapping Fields From Two Tables

Apr 1, 2004

Hi all,

I'm not sure how easy of a task this is, or how to go about it, so I thought I'd ask here. I am doing a major overhaul to my site and am re-programming the backend which involves re-formatting my tables, table names etc.

What I want to do is set up a new table in the format I want for the new site, but still be able to have the live data that is being submitted to the site now, be entered into the new table so that I don't have to shut down the site for days to transfer and reformat data. Example

CURRENT SITE

Table 1
Field1: Name
Field2: Address

NEW SITE

Table 1
Field1: Name
Field2: myAddress
Field3: City

I want to be able to somehow map the two together so that when data is entered into the Name field on the current site, that same name is also inserted into the new site table as well. Mapping like this:

(current site)Name -> (new site)Name
(current site)Address -> (new site)myAddress

The city field would be left unaffected as that data would only be entered once the new site is up and running.

I know I could do this from a stored procedure on the exiting site or by re-writting all of my SQL to insert into both tables, but right now all of the SQL is hardcoded into the pages and I have A LOT of pages. Is there a way to map the tables together from within SQL Server?

Cheers,
Jeff

View 5 Replies View Related

Getting Names Of Tables And Fields

Oct 8, 2004

Hi All,

I need to get the names of tables and fields (or atleast the fields) from a database. Is there a query I can use that will return what all the db's tables and those tables fields are?

Thanks

Lee

View 3 Replies View Related

How Do I Get A Printout Of All My Tables And Fields ?

Mar 15, 2001

Hi,

I have a database with approx 95 tables. I would like to get a printout of each table, the fields contained therein and the field type
(i.e the information displayed when rightclicking a table in Enterprise Manager and selecting Properties).

I have tried to use the diagram function but due to the large number of tables the diagram is very complex and would have to be printed out on a huge sheet of paper to make it legible. I have also looked at the SP_HELP stored procedure but can't seem to get the syntax correct to display properties on all tables (info on one table is no problem).

Any ideas ?

Thanks,

Tim H

View 1 Replies View Related

Comparing Fields In Tables

Aug 4, 2005

I am working with the article that MAK wrote on SecurityLogs http://www.databasejournal.com/features/mssql/article.php/3515886

I have completed this, but I have made some changes to the database (for normalization to 3NF purposes). I now have problems with a query.

I am trying to "Insert a new record in a table if it does not already exist in the table". To try to clarify I perform the following query:

INSERT INTO Tmp_Event
SELECT DISTINCT EventID, EventType, EventTypeName from Tmp

Which gives me the Tmp_Event table consisting of EventID's etc. (no duplicates). What I then want to do, is compare the 'Tmp_event' table and an already existing 'Event' table. These two tables are in fact identical. I would like to insert any records from 'Tmp_Event' into 'Event' if they do not already exist in 'Event'.

This query gives me all records that do not exist in 'Event'

SELECT EventID, EventType, EventTYpeName from Tmp_Event
WHERE EventID NOT IN (SELECT EventID from Event)

How can I change this query into performing an INSERT INTO Event as well?

Hope this makes sense :)

-Silia

View 2 Replies View Related

Merge Two Fields From Two Different Tables Into One

Aug 24, 2004

If possible, I would like to create a view using two different tables, but with the fields merged (ie. create one table from two tables). The table structures are pretty much the same. Can you do this from a singe SQL statement? Any ideas? My only alternative is creating a temp file that inserts records from both tables, but I want to avoid doing that.

View 6 Replies View Related

Add Similar Fields To Many Tables

Dec 13, 2004

Folks, i have to create four fields in every user table within my database:

CREATED_BY VARCHAR(25), CREATED_DATE [DATETIME], MODIFIED_BY VARCHAR(25), MODIFIED_DATE [DATETIME]

There are more than hundred tables, so i wanna automate this. i am tryin to do this in a cursor: please guide!

declare @name VARCHAR (50)
declare cur cursor
fast_forward
for select name from sysobjects where type='u' and status not like '-%'
open cur
WHILE (1=1)
BEGIN
FETCH NEXT
FROM cur
INTO @name
IF @@fetch_status = 0
BEGIN
ALTER TABLE @name
ADD created_by [VARCHAR] (25)
GO
ALTER TABLE @name
ADD created_by [VARCHAR] (25)
GO
ALTER TABLE @name
ADD created_date [DATETIME]
GO
ALTER TABLE @name
ADD modified_by [VARCHAR] (25)
GO
ALTER TABLE @name
ADD modified_date [DATETIME]
END
ELSE
BREAK
END
DEALLOCATE cur


I also want that if one column for a table exists; the other columns should be created rather than it quits.


Howdy!

View 4 Replies View Related

Compare Fields In 2 Tables

Jan 30, 2008

For example, I have 2 tables that have very similar fields

Table 1 Table 2
ShipRpt ShipRpt
invoice invoice
total sold total sold

Is it possible to compare each of these fields and return any record that has non matching data? I would join the tables on the shipping report or invoice and only compare the total sold field. If the invoices match and the total sold is different in each table, can I just return the 1 record?

View 2 Replies View Related

Unique Fields In SQL Server Tables

Oct 12, 2006

 I feel sure that this is a very naive question, but it is freeky me out that I can solve it. With SQL server what is the easiest way to make a field other than the Primary key unique?Would appreciate help with this. ThanksPaul

View 3 Replies View Related

Database Tables And NULL Fields

Nov 17, 2006

Hi  there,
 I have a fairly generic question regarding database design.
I am working  on my first database using SQL  Express.
I am testing the database by inserting records into the pre  defined tables.
Sometimes I  forget to input text into a non null  field. When
this  happens  it displays an error  and then skips a  few rows
which makes my  row numbering  messy.  For example  say I have 
Row  1, Row 2 Row  3 all  nicely  formatted but on Row 4  I make  a mistake
this  row then disapears and  their is  gap  between  3 and 5 where  4  was.
Although I am just testing, I was  wondering  what happens in a  real
scenario if an inexperienced data input person makes a few  mistakes it
makes the database  numbering  systems look bad. In  this case
is it common  to  make  most  fields  NULL to allow for admin errors??
 
 
 

View 8 Replies View Related

Adding Description To Tables And Fields

Oct 27, 2000

Can I add a description to a table or field? The way access gives you to add a description to a field.


Thanks!

View 1 Replies View Related

Documenting Tables/fields - Utility?

Aug 16, 2006

As part of a project documentation I want to list out the fields of all the tables to say Excel to plug in a description of the field and other notes.

Is there a tool in EM that would facilitate? Or, is there a utility that would work or a SQL script? Any recommendations is greatly appreciated.

Thanks,

Peter

View 7 Replies View Related

Removing 0 And Copying Fields To Other Tables

May 19, 2008

Hi guys,

please help.

I use to copy one column from one database to another database. What query should I use?

moreover,

How can I convert this

code
000001

the result would be
code
1

thanks!

View 5 Replies View Related

Create A Link Between Fields Of Two Tables

Jan 24, 2007

Amir writes "Dear Team Members;

Im writing a program in ASP language which it is using SQL Server for database.

I want to know how to create a link between fields of two tables. For example:
I have a table named: Projects containing:
p_name
status
date
member1
member2

and another table named results containing:
p_name
s_status
status1
status2

I want to create a link between field s_status of STATUS table and field status of table Projects

Users are sending data to table projects, when a user send some data to status of projects, I want this link to send data from status in project to s_status of results.

Could you please help me how to do it?

Regards.
Amir Zandi"

View 1 Replies View Related

Q On Joining Tables With Nullable Fields

Sep 27, 2006

Question.I have a new table that I am adding to a script that I wrote. Thistable has 3 fields, the first 2 fields are used in the on statement asbeing = other fields in the script.The first field always has data in it, but the 2nd field is sometimesnull.So my problem is if both fields have data in them and they both matchto the data in the fields that I am linking them to, then it returnsthe 3rd field without a problem. However if the 2nd field is null thenit is returning a null for the 3rd field. I have checked and the fieldthat I am linking to is null also.So if I haveselect t1.field1, t1.field2, t2.field1, t2.field2, t2.field3from table1 t1join table2 t2on t1.field1=t2.field1 and t1.field2=t2.field2with 2 records in each tabletable1: record1: data, datarecord2: data, nulltable2: record1: data,data,datarecord2: data,null,datawhat I get from the script isrecord1: data, data,data,data,datarecord2: data,null,data,null,nullI would expectrecord2: data,null,data,null,dataI hope this makes sense, I didn't want to post the entire actual scriptas it is about 150 lines long.Thanks in advance.

View 5 Replies View Related

Create Fields In Tables If They Do Not Exist In Database

Mar 31, 2008

Hello.
I need some help constructing a query i need to run on my database. I need to add 2 fields to every table in my databse. However, some of the tables already have1 or both these fields so i need to somehow do a check if the dield already exists. If it does not create the fields. Im using a MS SQL express 2005 server.
Could anyone help me construct this. Im pretty novice at SQL.
Thanks.

View 7 Replies View Related

How Can I Print Or Export All Fields For All Tables In My Sql 2005 Db?

Mar 25, 2008

How can i print or export all fields for all tables in my sql 2005 db?

View 3 Replies View Related

Keeping Track Of Mismatch Fields In Two Tables?

Mar 10, 2014

I have four fields in two different tables.

ID, field1, field2 and field3 in Table 1 has value 1 or 0. Type integer.
ID, field1, field2 and field3 in table2 are text fields.

If field1 in table1 has value 1, then there should be a text value in corresponding field in table2. That is, in field1 in table2.

However, if there is value 0 in field1 in table1 there should not be any value in the corresponding field in table2.

Now it is not always so, which do that I want to make a track of which fields miss match in these two tables.

How do I list them on the smartest ways? ID links the tables.

View 3 Replies View Related

Cursor To Compare/report On The Same Fields In 2 Tables

Sep 20, 2006

I have the following cursor that I am comparing 2 tables, theproduction table and a copy of the production table, I want results ofall address's that are like the address1 field...the problem is...myresults are giving me every field and if there is more than one, it isputting it in a grid....I only want to see results if they are 1 for the same address fieldthis is what I have so far....declare @address1 char(61),@city char(61)declare address_cursor CURSOR FORSELECT address1,city FROM test.dbo.testaddOPEN address_cursorfetch next from address_cursor into @address1,@citywhile @@fetch_status = 0BEGINselect * from testadd where @address1 like '%' + address1 + '%' and@city = cityFetch next from address_cursor into @address1,@cityPrintENDCLOSE address_cursorDEallocate address_cursor

View 4 Replies View Related

Insert Into Two Tables, Both With Auto Increment Fields

Jul 20, 2005

Just when I though I knew what I was doing. I ran into a roadblock.I have two tables, organizations & usersI am building a form online for orgs to input their own information.How do I insert into both tables when the org table needs to know what theuser_id is, but the user_id hasn't been created until the form is submitted?Any help or request for additional info would be more than great.Thanks!

View 1 Replies View Related

Select Fields From Different Tables All With Same Date Range

Nov 22, 2006

Can someone show me what I need to do to have a result set only displaying records from the specified date range. All the tables can relate on Date, shift, and ProductType (Besides UnprocessedProductTracking which can only relate on Date and Shift) I have a lot of trouble with joins...well I'm just not to experienced. Help!

CREATE Procedure dbo.spButterMarTotals
(
@fromdate smalldatetime,
@todate smalldatetime
)
AS
--
--Exec spButterMarTotals '11/15/06','11/15/06'
--
--


Select Distinct ButterballsThrownPounds, ReworkUsedPounds, ReworkMadePounds, CoolersUsedPounds, CoolersMadePounds, TrucksMadePounds,
ResidualFilmPounds, ResidualSqueezePounds, ProducedPounds, Blocks As ProcessingThrown, Weight AS NFCPounds, Pounds AS BirdsEyePounds,
T1AL50Type, T2Type, T3Type, T4Type, AL51Type, FillMach1Type



from tblproductionMaterialmetrics A

Inner Join tblNFCTracking B on
A.ProductType = B.ProductType

Inner Join tblProcessingDtl C on
C.ProductType = B.ProductType

Inner Join tblIngredientProduction D on
D.shift = C.shift

Inner Join tblUnprocessedProductTracking E on
D.shift = C.Shift

Where A.Date between @Fromdate and @todate

View 1 Replies View Related

Design Of Tables With Large Optional Fields?

Jan 4, 2006

I have a general SQL design-type question.

I want to log errors to a table. If the error is with a URL, I want to store the URL. These URLs can be very large, hundreds of characters, but I only need to store it if it causes the error, which should be very infrequent. Which is the better design:

Create a large varchar field in the log table to hold the URL, or null if the error wasn't with the URL.
Create a foreign key field in the log table to a second URL table, which has a unique ID and a large varchar, and only create a record in this table if the error is with the URL.

One concern I have with design 2 is that there could be many other fields that are infrequent. Do I create a separate table for every one?

Richard

View 3 Replies View Related

Changing Data Type To The Fields Of My Tables.

Nov 21, 2006

Hello.

i have a database with 300 tables. All the data types of the fields of my tables are custom. Ex. IFGint:int and stuff like that.

I want to know how can i through a stored procedure, change in batch mode all the fields of my tables. I don't want to modify by hand everytable. It's a lot of work and i think that maybe there might be a way for this programatically.

thanks in advance

View 2 Replies View Related

Problem In Stored Procedures When I Change The Name Of The Tables Of Fields

Oct 5, 2004

Hi My friends

I have develop big Database System in Microsoft SQL Server 2000 and my DB System content more then 30 Table and also more then 150 Stored procedure and from that stored procedures I have call my the tables

But the problem when I change the name of any table, SQL Server don’t prompt my about the stored procedure that use that table .. so I cant remember witch Stored procedure use that table

By example if I am in programming language like C# or VB.NET when I try to change any name of any class the compiles catch all the error that coming from changing the name of the class and simply I will fix the problem because the compiler help me and list all the object content the error

But in SQL Server how can I search for stored procedure that use un existing table like compiler in any programming language ??

Or is there any why to solve my problem ??

And the above problem also there when I change the filed name

And thanks with my best regards

Fraas

View 1 Replies View Related

SQL Server 2008 :: Finding Only Fields That Don't Match In Two Different Tables

Feb 12, 2015

I have two table People and Employee, both have firstname and lastname as fields

I want to display only the names that don't match on firstname and lastname

View 3 Replies View Related

SQL Server 2012 :: Select Maximum - Using Fields From 2 Tables?

Jun 11, 2015

I have Database Library, which has a lot of tables and we need 3 tables for quary:

Table Librarians: field ID, field Surname;
Table StudentCard: field ID, foreign key on table Librarians and other fields, which we don't use
Table TeacherCard: field ID, foreign key on table Librarians and other fields, which we don't use

Query: Select the librarian's surname, which gave the most count of books.

I know, how to resolve, when i took datas only from one table, e. g. TeacherCard

SELECT TOP 1 WITH TIES Librarians.LastName, MAX(Librarians.CountOfBooks) AS Books FROM
(SELECT L.LastName, COUNT(*) AS CountOfBooks FROM Libs L, T_Cards T
WHERE T.Id_Lib IN (SELECT L.Id)
GROUP BY L.LastName) AS Librarians
GROUP BY Librarians.LastName
ORDER BY MAX(Librarians.CountOfBooks) DESC
GO

I dont know, how to use datas from TeacherCard and from StudetnCard at the same time.

View 9 Replies View Related

Creating Update Trigger That Involve Two Tables And Few Fields?

Oct 13, 2013

I need creating an update trigger that involved two tables and a few fields.

tblCases
Fields
Defendent1
Defendent2
Defendant3
tblCaseBillingDetails
Fields
DefCount

I would like to create the trigger on tblCaseBillingDetails so that when the data in the Defendant fields are updated, the trigger fires and updates the Defendant count DefCount.

View 1 Replies View Related

How Do I Create A Composite Primary Key, Using Fields From Multiple Tables?

May 22, 2007

Hi All



I hope someone smart can help me, it would be highly appriciated.



I am developing an SQL Serverdatabase and in on of the tables I need the primary key to consist of two pieces of data.



TblOrders: OrderNum, Orderdate, ....

TblDispatchers: DispatcerID, Dispatcher, DispatcherAddress



The OrderNum field in TblOrders need to be a composite of an AutoNum-field (incrementet by 1) and the DispatcerID from the tblDispatchers.



Can this be done, and how.



Many thanks



Kind regards

Tina

View 3 Replies View Related

Composing A Reference From Fields Located In Mutiple Tables

Jun 12, 2007

Consider a situation. There is a table of submitted 'documents'. They have some attributes. There are assignments to process the things, which have a date they were created. Finally there is a price list which specifies the price according to document features and date, so that the assignment to process a document created at different time will have a different cost. In other words, there is a relation
(assignment->document.attribute(s) + assignment.date) -> pricelist.price

Creating relations has the integrity advantages: it is not possible to create an assignment, which price is not defined in the pricelist; precludes the pricelist entry removal if it is referred by any assignments.

Should a view, which combines all the foreign fields into one virtual table, be created to make establishing the reference possible?

View 2 Replies View Related

DB Engine :: Compare Two Tables And Log Difference In New Table With Fields?

Apr 21, 2015

I want to compare two tables and log the difference in new table with the fields as (old value,new value, column name). The column name should be the changes value column.

View 10 Replies View Related

Help Needed On How To Update Individual Fields When Importing Data Into Tables

Sep 12, 2001

Help -
I need to import data into an existing table. Most import rows were unique, so I had no problem using DTS and appending. However, some import rows match existing rows except for one column/field that contains updated/new data, and I have to either replace the entire row with the imported row, or replace the individual field with the new data. How do I do that when there are many rows to import? It would take forever typing in all the data using UPDATE. Thanks in advance for your help!

rb

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved