phanindra writes "Hello all,
I am phanindra....
I have a question sir/madam,
suppose we have an view named "VIEW" for a table named "TABLE"
will the values in the TABLE alter if we change it in VIEW
thanks in advance
phanindra"
I have a table (in Access) that is linked to the SQL server, and Ineed to add a column to it. I wrote the following:ALTER TABLE CensusADD COLUMN 'ActiveFacility' BIT;and I get a syntax error that I do not know how to solve. The columnneeds to contain yes/no data for each record.Any help for a struggling newbie?Thanks,Christine
View 1 Replies View RelatedFolks,
Is there any way I can dynamically alter the structure of a table? In other words if my Transact SQL
statement returns 5 rows I want to alter an existing table and put in 5 columns. Strange?!
Something like
alter table add
column+@i
The alter table statement is within a Cursor and ideally @i should increment each time and alter
the table to put in a new column.
If I went 3 times thru the loop, I should have
column1
column2
column3
added to the new table.
Any pointers would be greatly appreciated. Thanks much in advance.
I have been using the below query to copy a record of data that exists in several draft tables to the original tables. how do i change this query such that i can input multiple records at one time so multiple records get copied to original tables from the draft tables
At this time when i feed mutiple requestids to my query it simply errors out
SET NOCOUNT ON
GO
DECLARE @oldrequestid varchar(50),@newrequestid varchar(50)
DECLARE db_cursor CURSOR FOR
SELECT RequestId from report_request_draft where requestid in (320762)
[Code] ....
Hello!
I have 2 database (DB1 and DB2) in 1 server. I want to reflect new changes made in DB1.tbl_pm_project to DB2.tbl_pm_project. Any idea on how to do it.
Thank you
JJ-hon
I am having trouble with a VIEW. I modify the view to add a sort criteria. I can Execute the SQL and get the results I am looking for. I save the VIEW. Then if I open the VIEW using the OPEN VIEW menu option(right clicking the VIEW name) the sort order I set does not work. Please help.
Using Microsoft SQL Server Management Studio Express to access the SQL Server 2005
Got a table as:
Report_Date [Date/Time]
Customer_ID [nvarchar(50)],
Manager [nvarchar(50)]
Amount [Decimal(18,2)]
In this regard need to show the recoveries made by each Manager from their relevant customers? In other words, instead of the customer-wise balances need to show the difference between the balances comparing the dates. The dates are in month-end formats.For example, if Customer A has a record of $10,000 as on January 10, 2015 and the same has two entries on February 28, 2015 with balances of $4,000 and $4,000, instead of balances, need to show ($2,000). Similarly, in case of March 31, 2015 reflecting three entries amounting to $3,000 each, need to reflect a positive amount of $1,000. And where the same customer has no entry on April 30, 2015, need to reflect a negative amount of $9,000.These entries thereafter being grouped on Manager level.
there is any service or technique to reflect database changes to form?
am mean if there is two people update same data and one of them
does the update i need (search) on service in sql server or .net that(or triger) that can automatically reflect changes to the form control that display data automatically when data changes before the another persone make second aupdate, so he can see the update made by the first person pefore he make another update.
Thanks So Much for who try to answer.
Hi
I need to setup transactional replication for a production server.
The tables (articles) at publisher being replicated can expect schema changes.
Is there any way to reflect schema changes at subscriber using transactional replication.
Early Thanks,
Salman Shehbaz.
I am an SQL beginner / intermediate.
My question is assuming everything is equal is a view of a 4 table join slower than the SQL query of the 4 table join when running reports?
My client has a three tier system.
and they are using only views to assess the database and doing about 5 joints what are the advantages and disavantages of using only view vs hiting the table dirrectly
Are views used for securiry reasons, such as restricting access to certain columns/rows or is there performance advantages to using views. There is disagreement in my dept.
I created the following view PAYACTIVE:
Select * FROM PAYMENTS WHERE STATUS = 'ACTIVE'
The PAYACTIVE view contains 260 rows. The PAYMENTS table contains over 2 million rows. I executed the following commands:
Select * FROM PAYACTIVE
Select * FROM PAYMENTS WHERE STATUS = 'ACTIVE'
They seem to take the exact same amount of time. In other words, selecting from the view seemed to not help performance. Am I conducting a valid test?
My client has a three tier system.
and they are using only views to assess the database and doing about 5 joints what are the advantages and disavantages of using only view vs hiting the table dirrectly>
Emily
I have a SQL script that I attempted to write into a view. If I run the script in SQL Query Analyzer it works perfectly. When I attempt to run execute as a view I receive the following error message: "[MS][ODBC SQL Server Driver][SQL Server] The query and the views or function in it exceeded the limit of 256 tables."
My objective is to use the recordset from the view to create a crystal report but have been unable to replicate the "query" effect directly in crystal. (unfortunately I am also a new crystal user)
All my searches have been fruitless. I remember seeing something about SQL Server 2000 supporting 1000 tables and older versions supporting 256 in an Access forum but have not been able to find that thread a second time.
Any provided solution would be greatly appreciated. (I am getting tired of hearing the "gears" in my head grinding. Is smoke from the ears normal?)
SELECT SubID, SubMenuDesc, ItemID, ItemDesc, 0 AS ModID, 'n/a' AS ModDesc, round(ItemPrice/100,2) as ItemPrice
FROM vwSubMenuNorm
WHERE (ItemPrice <> 0)
UNION
SELECT vwSubMenuNorm.SubID, vwSubMenuNorm.SubMenuDesc, vwSubMenuNorm.ItemID, vwSubMenuNorm.ItemDesc, vwModifiersNorm.ItemID AS ModID,
vwModifiersNorm.ItemDesc AS ModDesc, round(vwModifiersNorm.ItemPrice/100,2) as ItemPrice
FROM vwSubMenuNorm INNER JOIN
vwItemNorm ON vwSubMenuNorm.ItemID = vwItemNorm.ID INNER JOIN
vwModifiersNorm ON vwItemNorm.MOD1 = vwModifiersNorm.ModID
WHERE (vwSubMenuNorm.ItemPrice = 0) AND (vwModifiersNorm.ItemPrice <> 0)
UNION
SELECT vwSubMenuNorm.SubID, vwSubMenuNorm.SubMenuDesc, vwSubMenuNorm.ItemID, vwSubMenuNorm.ItemDesc, vwModifiersNorm.ItemID AS ModID,
vwModifiersNorm.ItemDesc AS ModDesc, ROUND((vwSubMenuNorm.ItemPrice + vwModifiersNorm.ItemPrice)/100, 2) AS ItemPrice
FROM vwSubMenuNorm INNER JOIN
vwItemNorm ON vwSubMenuNorm.ItemID = vwItemNorm.ID INNER JOIN
vwModifiersNorm ON vwItemNorm.MOD1 = vwModifiersNorm.ModID
WHERE (vwSubMenuNorm.ItemPrice <> 0) AND (vwModifiersNorm.ItemPrice <> 0)
I am using a Microsoft Access ADP to get to data on SQL Server 2000. I would like to protect the base tables from being edited directly, but allow the views and SPs to handle all the work of getting data in and out. From what I have read in numerous articles and boods, I should be able to grant permissions just on the Views and NOT on the tables. However, the ONLY way I can make my views 'updatable' is by graniting UPDATE permissions on the TABLE! Worse yet, if I DENY permissions to UPDATE, INSERT, and DELETE in the view, but allow them in the table, the view allows the updates anyway, apparently not looking at the fact that it should be DENIED.
:confused:
I want to allow a group of users to create views but not be able to create new tables or stored procedures... how can I do this ??
Thanks, John :eek:
Is there an easy way to find all instances of a table/view name in packages?
View 1 Replies View RelatedI want to link in a table from another sql server in a view, how is this done?
View 1 Replies View RelatedIs there any gud topic on "updating tables using Views".Plz let me know
View 3 Replies View RelatedWe are attempting to implement security on top of a shrink-wrapped softwarepackage and are trying to get row-level security. Here's the scenario:1. Table dbo.BOOK contains all the information about books in everydepartment.2. There are a large number of developed reports that run queries like"select * from BOOK..."3. We wish to have each Department only be able to see their books - withoutchanging the existing reports.Our thought was to create a series of views:create view Dept1.BOOK asselect * from BOOK where Dept=1....and then create Roles for each Dept. We'd then remove rights to dbo.BOOKand grant rights to DeptN.BOOK as appropriate for each role. We startedtesting this and seemed to get it working, but are now having problems. Isthis possible? Is there another, better solution?Thanks!
View 5 Replies View RelatedI have an applicaton in which I collect data for different parametersfor a set of devices. The data are entered into a single table, eachset of name, value pairs time-stamped and associated with a device.The definition of the table is as follows:CREATE TABLE devicedata(device_idintNOT NULL REFERENCES devices(id),-- id in the devicetabledatetimedatetimePRIMARY KEY CLUSTERED,-- date creatednamenvarchar(256)NOT NULL,-- name of the attributevaluesql_variantNOT NULL-- value)For example, I have 3 devices, and each is monitored for two attributes-- temperature and pressure. Data for these are gathered at say every20 minute and every 15 minute intervals.The table is filled with records over a period of time, and I canperform a variety of SQL queries.I have another requirement which requires me to retrieve the *latest*values of temperature and pressure for each device.Ideally, I'd like to use the data I have collected to get thisinformation, and I suppose I can.What I need is the SELECT statement to do this.I'd appreciate it very much, if someone can help provide that.Conceivably, I could use a SQL server View for making this easier forsome of my users.One alternate technique I thought was to create another table which I*update* with the latest value, each time I *insert* into the abovetable. But it seems like a waste to do so, and introduces needlessreferential integrity issues (minor). Maybe for fast access, that isthe best thing to do.I have requirements to maintain this data for several months/year ortwo, so I am dealing with a large number of samples.Any help would be appreciated.(I apologize if this post appears twice)
View 9 Replies View RelatedHi,
I've got a table T1 and a view V1 based on T1's rows (but not all rows).
I've granted users to do update and select on my View V1.
But i don't want users directly update my table T1.
then, i log as a standard user in Enterprise mgr, and open my view V1. I modify a value in a field, and when i validate, an error occurs saying i'm not allowed to make Select neither Update on table T1.
Is there a way of doing this ? (sql 2000).
Hi,
I want to list the table names in a database "mydev_db".What would be the query ?.
I want to run a similar query to find out the indexes,views,stored_procs etc.
Regards,
Copernicus
This is a SQL2000 sp4 Ent. machine.
I was heard that loading certain tables/views (the most frequently used ones) could help in performance.
How could i do that?
thanks
david
I am looking for an efficient mechanism to compare data between 2 tables/views.
Rationale:
I use a proprietary tool to data transfer between 2 databases. The tool itself uses Microsoft SSIS (integration service) to transfer data. I want to make sure that the data is transfered properly. Both the source and target database are not live database. The source and target database are in seperate servers.
Based on the discussion at:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=23038
and BSmith's idea (no relation), I came up with this silly stored proc to compare any two tables or views. Supply the table names and the columns you wish to compare and have fun!
The key to the GROUP BY approach is that it handles NULLS quite well which JOINS have trouble with.
CREATE PROCEDURE CompareTables(@table1 varchar(100),
@table2 Varchar(100), @T1ColumnList varchar(1000),
@T2ColumnList varchar(1000) = '')
AS
-- Table1, Table2 are the tables or views to compare.
-- T1ColumnList is the list of columns to compare, from table1.
-- Just list them comma-separated, like in a GROUP BY clause.
-- If T2ColumnList is not specified, it is assumed to be the same
-- as T1ColumnList. Otherwise, list the columns of Table2 in
-- the same order as the columns in table1 that you wish to compare.
--
-- The result is all records from either table that do NOT match
-- the other table, along with which table the record is from.
declare @SQL varchar(8000);
IF @t2ColumnList = '' SET @T2ColumnList = @T1ColumnList
set @SQL = 'SELECT ''' + @table1 + ''' AS TableName, ' + @t1ColumnList +
' FROM ' + @Table1 + ' UNION ALL SELECT ''' + @table2 + ''' As TableName, ' +
@t2ColumnList + ' FROM ' + @Table2
set @SQL = 'SELECT Max(TableName) as TableName, ' + @t1ColumnList +
' FROM (' + @SQL + ') A GROUP BY ' + @t1ColumnList +
' HAVING COUNT(*) = 1'
exec ( @SQL)
- Jeff
Hello,We are researching whether the following scenario would be possible:In an upcoming application release, we have to move some tables (Logtables, look up tables, and a couple of secure tables) from database Ato database B.Rather than wait and do everything all at once, and have no roll-backplan should it fail, we'd like to create database B now, and startmoving those tables one by one over to it.To ensure compatibility with the existing code-base, we'd like todetermine whether we can use updatable views to allow the current codeto continue to run against the existing DB.Essentially, we would do this:Given a table named LogTable In database A, we'd copy all ofLogTable's data to database B. (We'd look at the transaction log tocopy any changes made on rows modified after copying started.)Then, we'd turn off the site for a few moments, and:In database A, we'd rename LogTable to LogTable-Old, and create a viewcalled LogTable which points to DatabaseB.dbo.LogTable.When we turn the site back on, updates and selects to LogTable wouldphysically pull from database B from now on.I have already verified that performing selects and updates against aview that refers to another physical database actually does work inSQL 2K5.My question is are there any pitfalls or things we should be aware ofthat anyone else has experienced trying to do something like this?Does it sound feasible?Thank you,Josh
View 3 Replies View RelatedHi,I am not getting option as 'new view' and 'new table' when I rightclick onviews and tables option in VisualStudio.net IDE server explorersqlservers database to create new objects.Looks like some setup issue in my database.Thanks for your help in advance.RgdsCV
View 1 Replies View RelatedWith my understanding of indexed views and according to books I read"indexed views" are supposed to perform much better than "temp tables"(temp table having primary key and indexed view with clustered indexon the same keys).But when I tried in my system I am getting opposite results. WithIndexed Views it takes 3 times more time.Any body has any reasons for that? Or my understanding was wrong?thanksRaghu Avirneni
View 2 Replies View RelatedI have a pretty large database that has tables that will contain millions of rows of records. I will predominantly be using Views just to select the data. (I will not be performing any updates or inserts). I propose creating indexes on the views. My question is - if I create indexes on my views, do I have to create them on the tables as well? Is it good practice to create indexes on tables by default even if I am not going to be performing select statements directly on my tables but via my indexed views? Any advice is appreciated.
Thanks
Ran
Hi,
I'm working with few examples from a 70-315 exam prep book.
I'm trying to view the Table design... edit/ create views from VS. NET environment.
But when i right click on the View node under Data Connection tree i get only "Refresh" & "Properties". According to this book.. i should be able to create new views from here.
I'm working with a SQL Server 2000 local installation. VS .NET is connected to the DB, as i tried other examples and i'm able to read from the DB.
My SQL server installation is a 120 days eval installation... has this got anything to do with the behavior ??
I also reied to establish a connection with SQL server 'sa' login... i could connect, but again i wasnt able to create 'views' access tables design.
Any help would be appreciated.
Thanks.
How can I use the GetSchema method new in 2.0 to get a list of only user tables and views.
View 6 Replies View RelatedI just started a new job and 1st time on sql server, how can i print list of queries, tables, views, stored procedures and functions?
View 6 Replies View Related