View Field Content Update When Real Table Fields Change
Sep 1, 2005
Hi,
I use view to join difference table together for some function. However,
when the "real" table fields changed (e.g. add/delete/change field). The
view table still use the "old fields".
Therefore everytimes when I change the real table, I also needed open the
view table and save it by SQL enterprise manager manually for update the
view table field.
Can we use a SQL command or other method to update it directly?
I was able to view table content easily before, but after Ireinstalled everything, I cannot find the option to view table contentin MS SQL Server Express. I can define table with no problem. Checkout the screen snapshot below, from which you'll see that the popupmenu frrom right-clicking on the table name does not have the "Opentable" option. What is going on?http://farm1.static.flickr.com/167/...839620d0b_o.png
I need to write the countryid of table 2 to the field countryid in table1 using the criteria of the correspoinding country name table 2 to country of table 1 if it write countryid else 0..
I've a DateTime field in a table and I want to separate it into two fields in an SQL Server 2005 view one for Date and the other for Time so What is the function I can use to do this process?
Hi all,I asked a similar question a few weeks ago but I didn't explain myself to well so I was hoping for some more input.I have created a webpage in Visual web developer that contains a gridview that points to an SQL table.All works well as far as searching the table and sorting. The SQL table itself is Droped ( deleted ) and then re-created every 1 minute from a query to a linked DB2 database so that we have a "live" table of our DB2 system but in SQL.....However,There is now a requirement to be able to ADD data to this SQL table. eg: a comments field.. from the website.My procedure for re-creating the SQL DB is now useless as any data added through the website would be lost when the table gets re-created.What is the best way for me to achieve a permanent table in SQL that gets updated from the Linked server DB2 but that I can also add comments from the front end (Website Gridview)..I'm assuming some sort of update query and a join to a seperate, permanent table that contains the comments?.Please help. Ray..
I have a table with eight (8) fields, including the primary key (rfpid). Three of the fields are foreign keys, which take their values form lookup tables. They are int fields (pmid, sectorid, officeid). One of the fields in this table is based on putting together the descriptive field in the lookup table for sector (tblsector). The two other fields to be part of this string are the rfpname and rfpid. This creates the following string:
The words rfp, proposals are words that have to be part of string;
the slashes are to also appear.
current_year would be defaulting to datepart = year (2008)
The part that has the last two digits of the current year then the underscore and then the rfpid should be connected by an underscore to the rfpname. I am at a loss and would greatly appreciate any help.
I have a field where all of the data is 5 characters in length. The last character denotes a status and will always be an F, H, or T. I want to add a new field (which I will do manually) and populate the new field with the last character from the "old" field. Once that is complete, I want to eliminate that 5th character from the old field.
Hello:I have an Access 2K form I built from a SQL Server 7.0 view. I want tolock certain fields in the database from users so they can see them onthe views and forms, but NOT be able to edit them.I've looked in BOL, MS SQL Server web page and SQL Server 7.0 books andcould not find how to do this.Any advise will be greatly appreciated.Thanks,Richard
CREATE VIEW dbo.vwFeat AS SELECT dbo.Lk_Feat.Descr, dbo.Lk_Feat.Price, dbo.Lk_Feat.Code, dbo.SubFeat.SubNmbr FROM dbo.Lk_Feat INNER JOIN dbo.SubFeat ON dbo.Lk_Feat.Idf = dbo.SubFeat.Idt
When ever I open using SQL Entreprise manager to edit it by adding or removing a field i inserts Expr1,2.. and I don t want that. The result I get is:
SELECT dbo.Lk_Feat.Descr AS Expr1, dbo.Lk_Feat.Price AS Expr2, dbo.Lk_Feat.Code AS Expr3, dbo.SubFeat.SubNmbr AS Expr4 FROM dbo.Lk_Feat INNER JOIN dbo.SubFeat ON dbo.Lk_Feat.Idf = dbo.SubFeat.Idt
I don t want Entreprise manager to generate the Expr fields since I use the real fields in my application. Thanks for help
I am still having problem with making View automatically updates itself when the underlying table schema changes. Running sp_recompile on the view table doesn't seem to work either, as I am still getting old format from the view (in Design mode the view returns the right info, but not when I open the View by doing Open View) even though the underlying schema has changed. Right now I find that I have to go into the View and change it a bit to force a recompilation.
And even if sp_recompile does, it would require that I manually do it each time I change a table. Any idea?
need help with my problem i have this view this code change the value field "new_unit" evry month from 1 > 2 > 3 > 4 like this evry 4 month it return to 1 >2.......... ------------------------------------------ for example
if i put unit_date = 01/05/2008 and unit=1 than new_unit=1
my question is how to create a stored procedure that move forward (all the employee) the "new_unit" field in +1 OR "unit_date" value MONTH +1
like create stored procedure name "plus" + so if i run this name stored procedure name "plus"
the stored procedure go to the viewor table and change the code view or table value
so i forward all the the "new_unit" or "unit_date" value IN one (change the cycle)+1
it doesn't matter if it change the "unit" value in the table "dbo.empList" or "unit_date" value
the important thing is that i can forward +1 or backward -1
evry time i run the stored procedure i get +1 (in the "new_unit") and olso create stored procedure name "minus" + so if i run this name stored procedure name "minus" this stored procedure that move backward the the "unit" value in the table "dbo.empList" or "unit_date" value in -1
I would like to ensure data integrity in a column (actually multiple columns will need a trigger) in my table(s) by setting up a trigger which allows an update of my database field only if the value which is being written to the field in the database exists in another column (in another "check" table).eg. I only want values "Yes", "No" or "" in many of my fields, which I store in a column named "YesNoBlank" in another table.Does anyone know the easy way to do this? / Syntax for the trigger?
Hi, Im trying to pull data from 2 fields in the same table into a SqlDataSource that feeds into a GridView, and display them as 1 field in GridView? I have a database table that has entries of users and their friends. so this tblFriendUser has a column called UserName and another column called FriendUserName. I am trying to get a list of friends for that particular user. Note that if User1 initiated the friend request, he will be listed as UserName and his friend as FriendUserName, but if his friend initiated the friend request, it will be vice versa: him being the FriendUserName and his friend the UserName. So I want the following 2 queries run and merged into one query in order to return 2 columns only: UserFriendID & UserName, is that possible? Is my design bad? Any suggestions/advice would help! Thanks a lot!
SELECT UserFriendID, UserName FROM tblUserFriends WHERE (UserName = @UserName);
SELECT UserFriendID, FriendUserName AS UserName FROM tblUserFriends WHERE (FriendUserName= @UserName);
One of my employes deleted a MSSQL Database instead of detach. Now i tried to recover the .mdf and .ldf file but when i trie to attach it it says. "File is not a primary database file"
I use Chinese Windows XP+SQL 2005 express+Microsoft Visual Web Developer 2005 Express Edition In aspnet_Profile table , the Collation of PropertyValuesString field is <database default> I hope to replace <database default> with SQL_Latin1_General_CP1_CI_AS ,but I get the following error, why? The following error was encountered while changing the collation: Setting for Length must be from 16 to 16.
--Then somehow iterate each value in the field dbname to create a view something like Create View viewcreatedfromtable As Select * from foxfire.dbo.abcd Union All Select * from foxfire.dbo.abcde
[Code] ....
I tried this, but it is only printing the last result returned not the entire result set
Code: Declare @database varchar(max), @sql varchar(max) Declare c1 Cursor For Select Program from tocreateviewfrom Open c1
I've just written a query that successfully brings back the data from one table based on the information from another. Basically we have been given a table of information and need to update certain fields in our user_group table with the new info.
Here is the SELECT statement SELECT user_group.id, user_group.name, user_group.description, Consultants.description AS Expr10, user_group.btype, user_group.rootmenu, user_group.intra_user, user_group.primary_g_id, user_group.fname, user_group.lname, user_group.ntlogon, user_group.lang_id, user_group.[external], user_group.title, user_group.work_tel, user_group.work_fax, user_group.work_ext, user_group.mobile, user_group.sex, user_group.add2, user_group.add3, user_group.town, user_group.county, user_group.pcode, user_group.private_flag,
[code]....
We want to update the 'description' on the user_group table with the 'description' from the 'consultants' table. To test this, we only want to write the UPDATE so that it changes the description where the name is 'Adam Froth. The UPDATE statement that we've written is
UPDATE user_group SET user_group.description = Consultants.description FROM user_group INNER JOIN Consultants ON user_group.description = consultants.description WHERE name like 'Adam Froth%'
but it keeps erroring and saying that it could 'Not be bound'.
question need help how can i use this code below not for insert i need it for update another table but only if exist the link to the code in this FORUM http://forums.microsoft.com/MSDN/AddPost.aspx?PostID=3208536&SiteID=1&Quote=True
Adam Haines wrote:
GPS,
Since you have such a dependency on dates and date calculations, you should implement a calendar table. A calendar table will make calculation such as this much more simplistic.
Calendar table link http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html.
Note the calendar table I use is a little different than this one. The only thing you will need to change is isodate to dt.
Now the code to get the results you need:
Code Snippet declare @t table( id int, fname char(4), Start_Date datetime, End_Date datetime, val_holiday int ) insert into @t values (111, 'aaaa', '3/15/2008', '03/21/2008', 1 ) insert into @t values (222, 'bbbb', '05/2/2008', '05/9/2008', 3) insert into @t values (333, 'cccc', '04/3/2008', '05/15/2008', 4) insert into @t values (333, 'cccc', '04/29/2008', '07/07/2008', 1 )
select id, fname, cal.ISODate, val_holiday from @t t1
inner join Calendar cal on cal.isodate >= t1.start_date and cal.ISODate <= t1.end_date
I am working with a vendor application called Cisco Unified Attendant Console - it operates on a Windows server with a SQL express database. The CUPs function of the application needs to reference a "contact" field with only the user portion of the contact's email address - generally, the contact's User ID will match the user portion of their email address, however, for this customer it does not (they use the employee number as the User ID and firstname.lastname as user portion of the email address.
Writing a script to accomplish the following:
The dbo.Contact_Properties table of the ATTCFG database has the following fields that we can work with:
- First_Name - Last_Name - Email - User_Field_2 - Contact_Unique_Ref (appears to be the field that ties all other contact tables together ?)
Is it possible to create a script that could run daily to either, combine the First_Name and Last_Name fields (with a period between) and populate the User_Field_2 field for each user, or populate the User_Field_2 field with everything before the @ symbol in the Email field for each user?
Also, by default the servers that this application is installed on does not have SQL Server Management Studio installed - is it possible to accomplish with PowerShell script triggered from the Windows Scheduler?
Hi everyone, I wonder how one can see or save the real sql statement that was executed by some stored procedure (including the one that used supplied parameters)?
I have a field 'LastName' in a SQL 2000 table 'customers' that is populated with all our customer's full names in this order:
Smith John M
I need to flip the information around so that it can be exported into a CSV file for an automated email notification program to notify them their items are in and having it in first, last, MI format is much more professional.
I've been playing with charindex and stuff routines and am spinning my wheels. Is there a canned routine for something like this - this must have been done a hundreed times out there in SQL somewhere (or it's so easy to do that everyone (but me) just cranks it from scratch).
I Create a measure group and two dimensions using [AdventureWorksDW2012], I try to change one dimension's storage mode with setting property proactive caching as Real-Time ROlap. There is no any warning message when deploying and processing, but error occurs when I query in sql server analysis services, see below for the error messages and the screen capture.
Error occurred retrieving child nodes: the current operation was cancelled because another operation in the transaction failed.
I have 1 table with some empty product codes that I have imported from a txt file. And I would like to fill in the empty product codes by taking the previous product code
I have a table with a column [FileData] of type TEXT or XML. I need to write the content of the column to a file, using sp_OAWrite for example, but the stored procedure who needs to read the content and write it to the file does not work because the limitation is no local variable for those types. What use is that datatype if i can not use it in procedures like this?
Anyone out there who could give me a tip on how to solve that?
Hi all, I have two tables (staging and Cdate) and neither objects has any constraints. staging table has ID, date, A, B, and C fields and Cdate has id,date and day fields. I need to update/insert date from Vdate into staging where staging ID=' ' and date is null Here is the code I wrote, however, it seemed the information was updated to one date only instead of time series - Cdate contains time series in column date. Anyone can help to fix it? Thank you for the help!
update s set s.date=c.date FROM cdate c join staging s on(s.id=c.id) Where s.date is null and id=2
I have two tables STUDENTS and REGISTRATIONS. I wish to update mySTUDENT table field, LAST_ATTENDED_SESSION_ID from my REGISTRATIONtable of the max SESSION_ID found in the REGISTRATION table.STUDENT starts off with:STUDENT_ID LAST_ATTENDED_SESSION_ID123456789 000000234567890 000000345678901 000000REGISTRATIONS:STUDENT_ID SESSION_ID CLASS_ID123456789 200001 BUS100123456789 200102 BUS150123456789 200203 BUS160I want to update my STUDENT.LAST_ATTENDED_SESSION_ID with theMAX(SESSION_ID) found in REGISTRATIONS for each student.I'd end up with:STUDENT_ID LAST_ATTENDED_SESSION_ID123456789 200203234567890 000000345678901 000000I've found examples here about selects and sub-queries, but nothingyet in my situation with an update.TIARob
