How Can I Create View Within Declare ?
dear all
how can i create view within declare ?
if i had one script contains declare if it is possible to combine both table with union ?
declare @a varchar(20)
select @a = ptraceno from phonelevel
where(ptraceno = '0913787170' or otherno = '0913787170')
select * from phonelevel where ptraceno = @a
declare @b varchar(20)
select @b = ptraceno from phonelevel
where(ptraceno = '0913011676' or otherno = '0913011676')
select * from phonelevel where ptraceno = @b
View Complete Forum Thread with Replies
Related Forum Messages:
Declare Or Create Cursor
Hello guys,just wanted to ask a question some might percieve it as a stupid one but I don't know so I will ask anyway? Is Declare Cursor same as Create Cursor and if not what is the major difference?
View Replies !
SQL 2000 Partitioned View Works Fine, But CURSOR With FOR UPDATE Fails To Declare
This one has me stumped. I created an updateable partioned view of a very large table. Now I get an error when I attempt to declare a CURSOR that SELECTs from the view, and a FOR UPDATE argument is in the declaration. There error generated is: Server: Msg 16957, Level 16, State 4, Line 3 FOR UPDATE cannot be specified on a READ ONLY cursor Here is the cursor declaration: declare some_cursor CURSOR for select * from part_view FOR UPDATE Any ideas, guys? Thanks in advance for knocking your head against this one. PS: Since I tested the updateability of the view there are no issues with primary keys, uniqueness, or indexes missing. Also, unfortunately, the dreaded cursor is requried, so set based alternatives are not an option - it's from within Peoplesoft.
View Replies !
Grant CREATE VIEW, CREATE PROCEDURE ...
Hi, I have currently a problem with setting up the permissions for some developers. My configuration looks like this. DB A is the productive database. DB B is a kind of "development" database. Now we have a couple of users call them BOB, DAVID, ... who are members of the db role db_reader and db_writer for the productive db a but they should be allowed to do nearly everything on db b. Therefor I added them to the db role db_owner for db b. For testing purposes I tried to "CREATE" a view TEST as BOB in database B but I received the error message 'Msg 262, Level 14, State 1, Procedure Test, Line 3 CREATE VIEW permission denied in database 'b'.' I cross checked the permissions on db level and I even granted all available permissions on db level but nevertheless I receive this error message. What's my mistake? Of course it worked fine when I give them sysadmin rights but then they have far too much permissions. Regards, Stefan
View Replies !
Alter View / Create View
I had given one of our developers create view permissions, but he wants to also modify views that are not owned by him, they are owned by dbo. I ran a profiler trace and determined that when he tries to modify a view using query designer in SQLem or right clicks in SQLem on the view and goes to properties, it is performing a ALTER VIEW. It does the same for dbo in a trace (an ALTER View). He gets a call failed and a permission error that he doesn't have create view permissions, object is owned by dbo, using both methods. If it is doing an alter view how can I set permissions for that and why does it give a create view error when its really doing an alter view? Very confusing.
View Replies !
Please Help: Create View
Hi Gurus,I'm a beginner. Would you please tell me if it's possible to create a viewhaving a calcuated column based on the condition of the column on the sqltable.create view vwImaging ASselectEmpID, LastName, FirstName, EmpTag = 'Act' ifFROM tblPerPayI have a table EMP:SSN (char 9)A view giving a formatted SSN (XXX-XX-XXXX,) a--Message posted via http://www.sqlmonster.com
View Replies !
Create A View In VBA
Hi,Is it possible to create some SQL in VBA, 'run it' then view as a datasheetthe results?I'm trying to throw together a fairly large search form, and VBA seems thebest way to parse the parameters.Cheers,Chris
View Replies !
Need Help On Create View
Hi.I created a simple view with the following statements:CREATE VIEW dbo.VIEW1ASSELECT dbo.VIEW_ALL.ID,dbo.VIEW_ALL.Code,Another.dbo.OTHER_VIEW.Label as SpecialCodeFROM dbo.VIEW_ALL LEFT OUTER JOINAnother.dbo.OTHER_VIEW ON(dbo.VIEW_ALL.Code + '_0') = Another.dbo.OTHER_VIEW.LabelWhen I run :select * from VIEW1 where code = 'abcde',I would get all 10 rows that are of the same data where the code is'abcde'. But what I thought it should return is really just one rowsince the rest of the 9 rows are exactly the same! I know that I canget my result if I useselect DISTINCT * from VIEW1 where code = 'abcde'But, is there any way I could change my SQL statements(CREATE VIEW)above so that I automatically get 1 row instead of 10 rows of exactlythe same value without using DISTINCT?Thank you for any help.
View Replies !
Create An SQL View From VBA
Hello All; I'm new to SQL, and developing my first application using SQL 2000 as the back end for my data. Can anyone tell me if I can create an SQL View from VBA? I'm trying to create a data view that access various records from a single table, and it has to distribute that data 2 14 different subforms, representing different days in a 2 week period, which is distingiushed by a field called "Day", that is numbered from 1 to 14. I also have a summary subform that has the weekly summary (days 1 to 7 and 8 to 14) on seperate subforms. In total I have 16 subforms, which actually source from a single table, just split depending on the day in the period. As I see it now, creating views iis the best way for me to go, but I also have to be able to change the period id on the fly, so I'm think I have to use VBA to generate these views. Does any of this make sense, and am I on the right track??
View Replies !
Create View
hi,if the below query select DISTINCT group_module_menu_info.menulink FROM user_group_role_info,group_role_module_info,group_ module_menu_info where user_group_role_info.userid=user_table.userid and ((group_module_menu_info.moduleid = user_group_role_info.moduleid)OR (user_group_role_info.roleid=group_role_module_inf o.roleid and group_module_menu_info.moduleid = group_role_module_info.moduleid)) if i want to create a view how ? thank you!
View Replies !
Create View
When I use following command it works fine. CREATE VIEW dbo.[ikw_custom] AS SELECT * FROM rns_keyword.dbo.ikw_custom but if I use following command with condition it gives me an error. if not exists(select * from sysobjects where name ='ikw_custom' and type='V') begin CREATE VIEW dbo.[ikw_custom] AS SELECT * FROM rns_keyword.dbo.ikw_custom end help will be appriciated. Thanks in advance -Balbir
View Replies !
Create View
I am learning how to work with MSSQL here is what I like to do I have a table with URLs like this "www.urls.com" I need to create a view that will insert this "http://" before "www.urls.com" can some please let me know what I need to do Thank you
View Replies !
Create View
CREATE VIEW getUsedColumns AS SELECT * FROM (SELECT DISTINCT attr FROM iba UNION SELECT DISTINCT attr FROM new_iba) WHERE attr != 0; its working with oracle how to chenge in t-sql thanx
View Replies !
Create View
I have a table in my program which going all the invoices,and I want to create a view where I can see the quantity of the products as many times by 1 as the quantity in the invoice: Invoice: Product name quantity woods 3 at the view woods 1 woods 1 woods 1 Thank you.
View Replies !
Create View Only
Good afternoon, I have a user that needs read only access to all tables in a particular database - Which is working fine. He also needs to create views, as well as the above, but nothing else. Is this possible? Many thanks.
View Replies !
How Can I Create IIf In A View
I know IIf doesn't exist in SQL. However, I am creating a view and want to check the value of a field and if it is 1 the field returns 'This value' if it is 2 then 'That value' , if 3 then 'Another value'. How can I do this ?
View Replies !
Create A View NEED HELP!!
Create a view showing every order that was shipped to Spain. Name the destination column "DestinationSpain". Include code that checks if the view already exists. If it does, it should be dropped and re-created. Use Northwind Go DROP VIEW Spain_Orders Go CREATE VIEW Spain_Orders AS SELECT * FROM Orders WHERE ShipCountry = 'Spain' this is what I get. server: Msg 3701, Level 11, State 5, Line 1 Cannot drop the view 'Spain_Orders', because it does not exist in the system catalog. albanie
View Replies !
Problem With Create View
I need to create view from the results of executing dynamic SQL.Query like this have incorrect syntax: CREATE VIEW vModuleCatalogObjectValues AS EXECUTE sp_executesql @sql, @paramDefinition, @moduleId; ENDWhat i need to do to get it work?!
View Replies !
How Do I Create An Expression In A View?
Hey guys, im really new ASP.net. I just upsized my access database to SQL Server Express and I'm trying to mimic some view functionalitty I had in access. I think they call it the expression builder.So bassically Im trying to make a column that does some calculations on other columns. Like:if columnA-columnB > 100 then columnC='YES' else columnC='NO'Does anyone know how to do this? I want to add this column in a view. Can i perhaps just do it in the datagrid? I'm so frusterated at the moment, ive been trying for about 3 hours!Thanks in advance for your help.
View Replies !
Create View From Cursor
I have multiple locations that I want to create views for eachindividual location.I am using a cursor to create the views for each location. So, thecursor grabs site #1 then <should> create view_site_#1, then grab site#2 and <should> create view_site_#2.For some reason it doesn't like the view name with the @site in it.Any ideas of how to get this done?Here's the cursor...declare @site varchar(5)declare c_site cursor forselect station from VHAISLCAUDIA.VISN_SITEorder by stationopen c_sitefetch from c_siteinto @sitewhile (@@fetch_status = 0)beginCREATE VIEW Site_All_Data_+ @siteASSELECT *FROM dbo.[600_All_Suggested_Data]WHERE (Site = @site)Print 'View for ' + @site + ' Created'fetch next from c_site into @siteendclose c_sitedeallocate c_sitereturnend
View Replies !
Can't Create An Index On My View
G'day all. I am trying to create an index on a view that joins two tables. I get the classic error of course: 'Cannot index the view 'dbname.dbo.HJC_net'. It contains one or more disallowed constructs.' Thing that gets me is that it all seems pretty normal stuff and I can't see what is stopping it. Code is below and any help greatly appreciated. CREATE VIEW dbo.HJC_net WITH SCHEMABINDING AS SELECTt_number FROM dbo.ticket_cancellations RIGHT OUTER JOIN dbo.tickets ON dbo.ticket_cancellations.tc_system_ref = dbo.tickets.t_number WHERE dbo.tickets.t_cancelled <> - 1 OR -- Add all cancellation codes that are to be excluded from the NET view below (dbo.ticket_cancellations.tc_cancellation_code <> 83943 AND dbo.ticket_cancellations.tc_cancellation_code <> 83946) GO -- Create a clustered index, it MUST be unique CREATE UNIQUE CLUSTERED INDEX t_number_unique ON HJC_net(t_number)
View Replies !
How Can Create View Autonumber
now i have two table 1st table type_id type_name 4 a 5 b 6 c 2nd table category_id category_name type_id 6 aaa 4 7 bbb 5 9 ccc 6 i want to join two table to create view autonumber type_name category_name 1 c ccc 2 b bbb 3 a aaa how can i write this sql to create autonumber field thanks
View Replies !
Create Update View
I think I need to create an update view, but am unsure of the syntax... One of my programmers would like an Update View created so he can use the view to update 2 fields in a table. (He is creating a new program in Delphi on my SQL database.) I can create an update statement if I know what value is to be inserted, but how do I write one for a value that is yet to be determined? Here is my Billing_Desc table: BD_ID pk int Not Null BD_DESC text Null Thank you in advance for your help! Toni
View Replies !
Challenging : Create View
Hi, I tried to create a view on following 4 tables with two different syntaxes. One with ANSI syntax and other with conventional(using WHERE clause). Both SELECT work fine and return same result sets. Views are created in both cases. SELECT from Conventional_View works fine But SELECT from the ANSI_View returns error message without Error Number as : 'DB-Library Process Dead - Connection Broken' CREATE TABLE dbo.SILT01 ( SILF01A varchar (8) NOT NULL , SILF01B varchar (25) NULL , SILF01C smallint NULL , CONSTRAINT SILT01_PK PRIMARY KEY CLUSTERED (SILF01A) ) GO CREATE TABLE dbo.SILT19 ( SILF23A varchar (8) NOT NULL , SILF01A varchar (8) NOT NULL , CONSTRAINT aaaaaSILT19_PK PRIMARY KEY CLUSTERED ( SILF23A, SILF01A ) ) GO CREATE TABLE dbo.SILT20 ( SILF23A varchar (8) NOT NULL , SILF15A varchar (8) NOT NULL , SILF01A varchar (8) NOT NULL , CONSTRAINT aaaaaSILT20_PK PRIMARY KEY CLUSTERED ( SILF23A, SILF15A, SILF01A ) ) GO CREATE TABLE dbo.SILT23 ( SILF23A varchar (8) NOT NULL , SILF23B varchar (30) NULL , SILF23C varchar (40) NULL , SILF23D varchar (8) NULL , SILF23E varchar (1) NULL , SILF23F text NULL , SILF23G varchar (25) NULL , SILF23H varchar (8) NULL , SILF23I varchar (25) NULL , SILF23J varchar (25) NULL , SILF23K text NULL , SILF23L varchar (25) NULL , SILF23M varchar (8) NULL , SILF23N varchar (25) NULL , SILF23O varchar (25) NULL , SILF23P text NULL , SILF23Q varchar (25) NULL , SILF23R varchar (8) NULL , SILF23S varchar (25) NULL , SILF23T varchar (25) NULL , SILF23U varchar (15) NULL , SILF23V varchar (15) NULL , SILF23W varchar (15) NULL , SILF23X varchar (15) NULL , SILF23Y varchar (15) NULL , SILF23Z varchar (15) NULL , SILF23A1 varchar (15) NULL , SILF23A2 varchar (15) NULL , SILF23A3 varchar (15) NULL , SILF23A4 varchar (15) NULL , SILF23A5 varchar (15) NULL , SILF23A6 varchar (3) NULL , CONSTRAINT aaaaaSILT23_PK PRIMARY KEY CLUSTERED ( SILF23A ) ) GO create view ANSI_View as SELECT DISTINCT SILT01.SILF01A, SILT23.SILF23A, SILT23.SILF23B, SILT20.SILF15A FROM ((SILT01 INNER JOIN SILT19 ON SILT01.SILF01A = SILT19.SILF01A) INNER JOIN SILT23 ON SILT19.SILF23A = SILT23.SILF23A) LEFT JOIN SILT20 ON (SILT19.SILF01A = SILT20.SILF01A) AND (SILT19.SILF23A = SILT20.SILF23A) create view Conventional_View as SELECT DISTINCT SILT01.SILF01A, SILT23.SILF23A, SILT23.SILF23B, SILT20.SILF15A FROM SILT01, SILT19,SILT23, SILT20 WHERE ( SILT01.SILF01A = SILT19.SILF01A AND SILT19.SILF23A = SILT23.SILF23A ) AND ( ( SILT19.SILF01A *= SILT20.SILF01A) AND (SILT19.SILF23A *= SILT20.SILF23A) ) SELECT * FROM ANSI_VIEW 'DB-Library Process Dead - Connection Broken' SELECT * FROM Conventional_View (Works fine) Expecting Help or Suggestions from experts. Thanx, Gunvant Patil( gunvantp@mail.usa.com )
View Replies !
Question About Create View
Hi there I made a view for some tables to be used for linking to Access2007 and I was wondering if I have to manually keep updating the view with new records or does it automatically get updated as the actual table gets updated?? thank you,
View Replies !
Create View Issue
Hello - I am creating a view using "Execute SQL Task" - this gives me the following error: "The CREATE VIEW SQL construct or statement is not supported." This worked in SQL 2000 DTS, and I know it is supported in 2k5. It is probably user error on my part - but I can't figure out what I am doing wrong. Thanks, Sean
View Replies !
Cannot Create A Order By View
Hello, I am trying to create a simple view which uses self joins. I want the final result order by. I am able to create the view using Top clause followed by order by but when I simply query the view it does not appear in the correct order. Can some body help me with the issue. CREATE VIEW [dbo].[vw_SalesRep_Chaining] AS SELECT Top 100 percent Rep_id AS RepId, Rep_cd AS RepCode, Region as Region, CASE WHEN Market IN ('Arizona - North', 'Arizona - South', 'Gtr TX / New Mexico') THEN 'Arizona / New Mexico' WHEN Market IN ('L.A. Metro - West', 'L.A. Metro - North', 'L.A. Metro - East') THEN 'LA Metro' WHEN Region = 'Western' AND Market NOT IN ('Arizona - North', 'Arizona - South', 'Gtr TX / New Mexico', 'L.A. Metro - West', 'L.A. Metro - North', 'L.A. Metro - East') THEN 'Western - Other' ELSE '' END AS Sub_Region, RSM AS RSM, UPPER(Regional_Manager) AS Regional_Manager, Market, Rep_Manager_id AS SalesManager, UPPER(MarketManager) AS MarketManager, New_Position AS NewPosition, Rep_Status AS RepStatus, UPPER(Rep_Payroll_name) AS PayrollName FROM (SELECT SalesRep_GUID, Rep_id, Rep_cd, Rep_Payroll_name, Rep_SSN, Rep_Status, Hire_dt, Termination_dt, Commission_start_dt, Rep_Manager_id, Region, Market, New_Position, Validity_start_dt, Validity_end_dt, Load_interval_id, Create_process_id, current_ind FROM dbo.SalesRep WHERE (current_ind = 1) AND (New_Position IN ('SC1', 'SC2', 'SC3', 'MSO')) AND (Region NOT IN ('Emerging Market')) OR (current_ind = 1) AND (New_Position IS NULL) AND (Region IN ('TeleSales')) AND (Region NOT IN ('Emerging Market'))) AS A INNER JOIN (SELECT Rep_id RepId, Rep_Payroll_name as MarketManager, Rep_Manager_id as RSM FROM dbo.SalesRep AS SalesRep_2 WHERE (current_ind = 1)) AS B ON A.Rep_Manager_id = B.Repid INNER JOIN (SELECT Rep_id RepId1, Rep_Payroll_name as Regional_Manager FROM dbo.SalesRep AS SalesRep_1 WHERE (current_ind = 1)) AS C ON B.RSM = C.Repid1 WHERE (A.Region IS NOT NULL) Order by Rep_Id, Rep_Cd This is the script I have used to create the view. But when is simply query the view it does not appear in the order of Rep_Id, Rep_Cd
View Replies !
Create Trigger For View
Hi, just started to write my first trigger for a view. Of course I got some errors, which I could could resolve except one. Whenever I run my script I do get the following message: Msg 213, Level 16, State 1, Procedure IO_Trig_INS_Zuordnung_Alles, Line 11 Insert Error: Column name or number of supplied values does not match table definition. The Code where the error occurs according that message is the following: CREATE TRIGGER IO_Trig_INS_Zuordnung_Alles ON Zuordnung_Alles INSTEAD OF INSERT AS BEGIN SET NOCOUNT ON -- Check for duplicate Zuordnung. If there is no duplicate, do an insert. IF (NOT EXISTS (SELECT Z.[Anlagen-Nr_Z] FROM Zuordnung Z, inserted I WHERE Z.[Anlagen-Nr_Z] = I.[Anlagen-Nr_Z])) INSERT INTO Zuordnung SELECT [Anlagen-Nr_Z], [I-Nr], [an_A-Nr], [APS-Reg], [KSt des Inventars], [Gelände_Raum], [Servicenummer], [S-Nummer], [Hostname], [LOGIN-Name], [Mitarbeiter von], [Kategorie], [Verwendung], [Hersteller und Typ], [Ausstattung], [F-Nr], [Board], [Bios], [Prozessor], [Cache], [RAM], [SCSI-Contr], [CD-Rom], [Festplatten], [Wechselplatten], [Sonderausstattung], [Graphik], [Sound], [MPI], [NW-Karte], [MAC-Adr], [DHCP], [IP-Adr], [Netz], [Port], [Segment_ID], [NAP], [NW-Karte_2], [MAC-Adr_2], [DHCP_2], [IP-Adr_2], [Netz_2], [Port_2], [Segment_ID_2], [NAP_2], [Bemerkungen], [Betriebssysteme], [Dual-Boot], [geplante Maßnahmen], [Servicearbeiten], [aktualisiert], [wieder frei], [zurück von], [COB-Kostentyp], [COB-Import], [Dummy3], [Dummy4], [Inventursuche FROM inserted ELSE... I did check on the columns serveral times, also I wrote them back with vba and used that but nothing helps. I would appreciate any help on possible errors in that code.
View Replies !
Create View Syntax
Why can't one use a three part naming convention when creating a view? example: CREATE VIEW databasename.schemaname.anyview AS SELECT * FROM anytable The following error is returned: Msg 166, Level 15, State 1, Line 1 'CREATE/ALTER VIEW' does not allow specifying the database name as a prefix to the object name.
View Replies !
Create View On Sql 7 From Sql 2005
hi i need to create view in sql 2005 db from table in sql 7 db when i try to connect to the sql server i get this messeg: This version of Microsoft SQL Server Management Studio can only be used to connect to SQL Server 2000 and SQL Server 2005 servers. (ConnectionDlg) there is away to resolve this problam? yaakov
View Replies !
How Do I Create A Variable In A View
Hello, We have a need to create a variable within a View. We tried declaring our variable like we would in a stored procedure, but that doesn't seem to work. What is the proper way to do this? Thank you. Kay
View Replies !
CREATE INDEX ON VIEW
Cannot create index on view 'View name ' because the view is not schema bound." What is the Schema bound ? How can i create the view Schema bound ? Pls help me out Sir Yaman
View Replies !
Is There Anyway To Create A View Within A Function
Hi, Is there anyway to create a view within a Function? The code is as below. I execute the code between "BEGIN" and "END". SQL Analyzer report error that said 'CREATE VIEW' must be the first statement in a query batch. I could make the variable constant in SELECT statement, but I'm wondering if there is a way to make CREATE VIEW as part of code piece. CREATE Function GetCommonFailurs() AS BEGIN IF OBJECT_ID(N'CommonFailures') IS NOT NULL DROP VIEW CommonFailures DECLARE @Run1Result as char(4), @Run2Result as char(4); SET @Run1Result='Fail'; SET @Run2Result='Fail'; CREATE VIEW CommonFailures AS SELECT Run1Failures.RunID as Run1ID, Run2Failures.RunID as Run2ID, @Run1Result as 'Run1Result', @Run2Result as 'Run2Result', Run1Failures.SmartyDOTXMLFilePath as Run1SmartyFilePath, Run2Failures.SmartyDOTXMLFilePath as Run2SmartyFilePath, Run1Failures.SDET as SDET, Run1Failures.CommandLine as CommandLine, Run1Failures.OutputFilePath as OutputFilePath FROM Run1Failures INNER JOIN Run2Failures ON Run1Failures.TestID = Run2Failures.TestID END
View Replies !
Trying To Create A View With Parameters.
Trying to create a view with parameters. I want to use view inside the SP. But I'm getting an error. Why? -- 1st -- drop table funkete create table funkete (funketeid int, funketeFname varchar(20)) insert funkete select 1, 'funkete' union all select 5, 'amanda' union all select 6, 'funkete' go -- 2nd -- drop proc a create proc a (@funketeid int) as select @funketeid, funketefname from funkete go -- 3rd exec a @funketeid = 4 go -- 4th create view vw_funkete4 as declare @funketeid int select @funketeid, funketefname from funkete go ============================= http://www.sqlserverstudy.com
View Replies !
Trying To Create A View Across Two SQL Servers
I have two machines running SQL server. I need to create a view that contains data from both servers on Server 2. ex: Server 1 contains: database (Employees) Table (EmpMaster) Column (EmpNumber) column (EmpName) Server 2 contains: database (Training) Table (TrainingEntry) Column (EmpNumber) Column (TrainingDate) Column (TrainingDescription) If the two tables were on the same server and in the same database, I'd just write the following: select TrainingEntry.EmpNumber ,Employee.EmployeeName ,TrainingEntry.TrainingDate ,TrainingEntry.TrainingDescription from TrainingEntry Left Outer Join Employee on TrainingEntry.EmpNumber = Employee.EmpNumber The question is how do I handle going between two databases, and more importantly, how do I handle linking between two servers? All examples are appreciated. Thanks
View Replies !
How To Create A View With Schemabinding
hi, my sql query is like this create view table1 with schemabidning as select columnname from table1 but it is giving error msg like this you cannot create view 'Cannot schema bind view' please help me out how to create a view with binding to underlying table. Raghu sunkara.
View Replies !
Is It Possible To Create A View From A Stored Procedure
Is it possible to drop and then create a view from a stored procedure? Like the way you can drop and create a temp table. I want to create a view of the fields in a table something like: But I cannot include the field names, they may be changed by an admin user. If exists view 'custom_fields" drop view 'custom_fields' Create view custom_fields Select * From tblCustomFields And make this a view in the db named custom_fields. And I want to call it from a button click in my UI.
View Replies !
How Do I Create A Fulltext Index On A View?
Hello all,I'm wanting to do a CONTAINS query on fields that belong to two seperate tables. So, for example, this is what I'd *like* to do :SELECT ci.itemNameFROM Content_Items ciINNER JOIN Content_Pages AS cp ON ci.contentItemId = cp.contentItemIdINNER JOIN FREETEXTTABLE(Content_Items, (ci.title, cp.pageText), @searchString) AS ft ON ci.contentItemId = ft.[KEY]However, this will not work, since SQL Server won't let you do fulltext queries against more then one table.So my idea is to create a view that combines my Content_Items table with the desired column from my Content_Pages table. I would then create a fulltext index on this view, and do fulltext queries against it.It seems like this should work. However, when I try to create a fulltext index on my newly-created view, I get this error :A unique column must be defined on this table/view.How do I create a unique column within a view?Also, will my approach work, or will it have unintended consequences? I'm using this as part of a search component in my software, so it has to perform reasonably well.
View Replies !
Not Being Able To Create A View That Requires A Loop
Hey i have a table A that contains 3 columns : id, entry ,sessionid i want to create a view on this table that will contain - for each sessionid s in A --> select top 5 rows having s as sessionid and ordered by id desc (s can have 1 or 2 or 5 or 300 entries i want to get only the latest 5 rows that correspond to this session) I tried many queries and different combinations i could find one yet to do the following. Can anyone help me plz? Can we have a loop in a view?is it possible?
View Replies !
Create A View Based On Variables?
Hi All, I would like to create a view based on a variable (a date). CREATE VIEW testView (@myDate) AS select * from testTable WHERE testVar = @myDate -- This is far from what the final view would look like, but if anyone knows if something similiar to this can be done, I would greatly appreciate it. Thanks.
View Replies !
Create A View From A FoxPro Table???
Does anyone know of a way to create a view in Sql Server from a Foxpro table? I am unfamilair with foxpro but another application is using it and I need to access the data and have up to the minute results. If there is a way can someone provide an example of how this view would be created with the ODBC connection etc... Thanks, eric
View Replies !
Want To Be Able To Create Or View Database Diagrams
Our MS SQL (SQL Server 2000) DBA has database privileges locked downpretty tightly. We end users/developers do not have administratorprivileges for most databases. That arrangement has worked out OK forthe most part. However, it's a bit aggravating that we can't evencreate our own database diagrams. When we attempt to do so (inEnterprise Manager), we get a dialog that says "You do not havesufficient privilege to create a new database diagram."Our DBA is so busy that it's difficult to get them to create ones forus. And even when they do, it seems that we can't even view it online-- we rely on the DBA to give us printed copies or screenshots for ourdocumentation. How ridiculous is that? We'd also like to be able tomanipulate the diagrams online so that we can more easily study ourtable structures, indexes, foreign key constraints, etc. In fact,there doesn't even seem to be any other way to easily view currentforeign key constraints.I realize that this might have to do with the diagramming tool havingthe ability to actually manipulate the database in ways we aren'tsupposed to do (e.g., like creating new tables). However, isn't therea "read-only" version of this tool that will give us what I'm lookingfor? Short of that, are there any other free or cheap tools that willprovide this? Thanks!(Please forgive my ignorance if this has been hashed out before.However, I did search news groups and the web before posting, figuringthis had been covered before, but I didn't find anything thatspecifically addressed this issue.)
View Replies !
Create View Script With Dependencies
Hi all,i'm looking for a way to script all of my views in the correct order.Some views are dependant on other views and i want to be able to runthe script in one time fully correct. Now when i create such a scripti have to run it multiple times to generate all the scripts.p.s. i found a script here :http://www.eggheadcafe.com/articles/20030609.aspbut this script does not work with views with a lot of text (views forwhich you need sp_helptext to see the full create statement)Greetings Sjaak
View Replies !
|
TRACKER
