Here I need to create a view by using following criteria, there is 3 tables which are Tbl.adminCategory, tbl_adminuser, tbl_outbox respectively. I am working on 2000SQL server
I am treing to create view as following but getting some error.
SELECT tbl_adminuser.adminUserName, tbl_AdminCategory.Name,
COUNT(tbl_outbox.msgUserID),
FROM tbl_adminuser INNER JOIN
tbl_AdminCategory ON
tbl_adminuser.adminUserID = tbl_AdminCategory.CatID INNER JOIN
tbl_outbox ON tbl_AdminCategory.CatID = tbl_outbox.msgUserID
AND tbl_outbox.msgUserID <> 0
GROUP BY tbl_outbox.msgUserID
But I am getting error pls correct the view,
In my SQL I am having temporary tables. And in Microsoft SQL Server Management Studio (Microsoft SQL Server 2005) whenever I execute sql statement its working fine & I am getting the records.
My SQL statement is using 2 databases as follows: 1.PerformanceDeficiencyNotice 2.HRDataWarehouse
Both the above databases are SQL SERVER 2000(80) with a compatibility level of 80.
The problem is when I am trying to create a new view with my sql statement and when I am saying “Verify SQL Syntax�, I am getting an error as “Invalid Object Name ‘#pdninfo’.
And when I am saying “execute SQL�, I am getting an error as “Unable to parse query text� but when I am continuing with the error, the sql statement is running and I am getting the data.
And now when I am trying to save the view I am getting the error as below “Incorrect syntax near the keyword ‘INTO’�. Views or functions are not allowed on temporary tables. Table names that begin with ‘#’ denote temporary tables.
Please suggest how to solve this problem. Any help is greatly appreciated.
Thank You
MY SQL Statement is as follows:
SELECT pdn.transactionid, pdn.employeenbr, pdn.lastname, pdn.firstname, pdn.processlevel, pl.facilityname as processlevelname, pdn.department, pdn.jobcode, pdn.title, pdn.supemployeenbr, pdn.managername, pdn.timeframe as pdn_timeframe, pdn.actualeffectivedate as pdn_startdate, /*actualeffectivedate is the start date for the pdn. starteddate is when info starts being put in the system*/ /*the pdn end date has to be calculated for the pdn based on the timeframe and actualeffectivedate*/ case when pdn.actualeffectivedate <> convert(datetime,'01/01/1900',110) then case pdn.timeframe when '30' then dateadd(month,1,pdn.actualeffectivedate) when '60' then dateadd(month,2,pdn.actualeffectivedate) when '90' then dateadd(month,3,pdn.actualeffectivedate) else null end end as pdn_enddate, pdn.status as pdn_status, status.description as pdn_statusdesc, pdn.managersignoff as pdn_managersignoff, pdn.managersignoffdate as pdn_managersignoffdate, pdn.associatesignoff as pdn_associatesignoff, pdn.associatesignoffdate as pdn_associatesignoffdate, pdn.witnessname as pdn_witnessname, /*the start date for the extension has to be calculated by subtracting 30 days from the evaluationdate*/ /*where the evaluationtype = 'X' (Extension Final).*/ /*there is only one timeframe of 30 days for an extension and only one extension is allowed per pdn for an associate*/ case when (eval.evaluationtype = 'X' and eval.status not in ('C','D','N')) then dateadd(month,-1,eval.evaluationdate) else null end as ext_startdate, eval.evaluationdate as eval_evaluationdate,/*end date of the evaluation or extension*/ eval.evaluationtype as eval_evaluationtype, evaltype.description as eval_evaltypedesc, eval.status as eval_status, status2.description as eval_statusdesc, eval.effectivedate as eval_effectivedate, eval.managersignoff as eval_managersignoff, eval.managersignoffdate as eval_managersignoffdate, eval.associatesignoff as eval_associatesignoff, eval.associatesignoffdate as eval_associatesignoffdate, eval.witnessname as eval_witnessname into #pdninfo FROM [PerformanceDeficiencyNotice].[dbo].[PDNMain] pdn left outer join [PerformanceDeficiencyNotice].[dbo].[EvaluationsMain] eval on pdn.transactionid = eval.transactionid left outer join [HRDataWarehouse].[dbo].[ProcessLevel] pl on pdn.processlevel = pl.processlevel left outer join [PerformanceDeficiencyNotice].[dbo].[StatusDescriptions] status on pdn.status = status.status and status.type = 'PDN' left outer join [PerformanceDeficiencyNotice].[dbo].[StatusDescriptions] status2 on eval.status = status2.status and status2.type = 'EVAL' left outer join [PerformanceDeficiencyNotice].[dbo].[EvaluationTypes] evaltype on eval.evaluationtype = evaltype.type /*select active pdns from PDNMain (status: 'A' = Approved, 'S' = Submitted)*/ WHERE pdn.status in ('A','S') /*select extensions from EvaluationsMain (evaluation type: 'X' = Extension Final; status: <> 'C' - Completed,*/ /*'D' - In Progress, or 'N' - Not started)*/ OR (eval.evaluationtype = 'X' and eval.status not in ('C','D','N'))
/*get last performance rating and last (maximum) performance review date from PerformanceReviewHistory*/ /*Note: A PerformanceReviewHistory record gets created within a couple of days after an associate is hired.*/ /* The rating and updatedate are null initially. Aggregate functions (i.e. MAX) ignore null values.*/ /* You must check for "updatedate IS NOT NULL" as shown below or the record will be dropped.*/ SELECT distinct(#pdninfo.employeenbr), perfreview.rating, perfreview.updatedate into #perfreview FROM #pdninfo, [HRDataWarehouse].[dbo].[PerformanceReviewHistory] perfreview WHERE #pdninfo.employeenbr = perfreview.employeenbr AND perfreview.updatedate = (SELECT max(updatedate) FROM [HRDataWarehouse].[dbo].[PerformanceReviewHistory] perfreview2 WHERE perfreview2.employeenbr = perfreview.employeenbr AND updatedate IS NOT NULL)
/*select active pdns ('orig' = original)*/ SELECT 'orig' as orig_or_ext, #pdninfo.*, #perfreview.rating as lastperfrating, #perfreview.updatedate as lastperfreviewdate, /*get empstatus, lasthiredate, originalhiredate, gender, race, middle init, supervisor name from Employee*/ emp.empstatus, emp.lasthiredate, emp.originalhiredate, emp.gender, emp.race, emp.mi, (SELECT emp2.lastname FROM [HRDataWarehouse].[dbo].[Employee] emp2 WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_lastname, (SELECT emp2.firstname FROM [HRDataWarehouse].[dbo].[Employee] emp2 WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_firstname, (SELECT emp2.mi FROM [HRDataWarehouse].[dbo].[Employee] emp2 WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_mi FROM #pdninfo left outer join #perfreview on #pdninfo.employeenbr = #perfreview.employeenbr left outer join [HRDataWarehouse].[dbo].[Employee] emp on #pdninfo.employeenbr = emp.employeenbr WHERE #pdninfo.pdn_status in ('A','S')
union
/*select extensions ('ext' = extension)*/ SELECT 'ext' as orig_or_ext, #pdninfo.*, #perfreview.rating as lastperfrating, #perfreview.updatedate as lastperfreviewdate, /*get empstatus, lasthiredate, originalhiredate, gender, race, middle init, supervisor name from Employee*/ emp.empstatus, emp.lasthiredate, emp.originalhiredate, emp.gender, emp.race, emp.mi, (SELECT emp2.lastname FROM [HRDataWarehouse].[dbo].[Employee] emp2 WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_lastname, (SELECT emp2.firstname FROM [HRDataWarehouse].[dbo].[Employee] emp2 WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_firstname, (SELECT emp2.mi FROM [HRDataWarehouse].[dbo].[Employee] emp2 WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_mi FROM #pdninfo left outer join #perfreview on #pdninfo.employeenbr = #perfreview.employeenbr left outer join [HRDataWarehouse].[dbo].[Employee] emp on #pdninfo.employeenbr = emp.employeenbr WHERE #pdninfo.eval_evaluationtype = 'X' and #pdninfo.eval_status not in ('C','D','N')
I am looking to create a constraint on a table that allows multiplenulls but all non-nulls must be unique.I found the following scripthttp://www.windowsitpro.com/Files/0.../Listing_01.txtthat works fine, but the following lineCREATE UNIQUE CLUSTERED INDEX idx1 ON v_multinulls(a)appears to use indexed views. I have run this on a version of SQLStandard edition and this line works fine. I was of the understandingthat you could only create indexed views on SQL Enterprise Edition?
I created a query, which makes use of a temp table, and I need the results to be displayed in a View. Unfortunately, Views do not support temp tables, as far as I know, so I put my code in a stored procedure, with the hope I could call it from a View....
-- populate declare @i int set @i = 1000 while @i > 0 begin insert into dbo.test1 select @i, '1.' + cast(@i as varchar(5)) set @i = @i - 1 end
insert into dbo.test2 select 1, '2.1' union all select 2, '2.2' go
-- create view create view dbo.vw_Test as select1 as QueryID, TestName fromdbo.Test1 union all select2 as QueryID, TestName fromdbo.Test2; go
-- this works as i want, only scans table dbo.Test2 select * fromdbo.vw_Test whereQueryId = 2
-- joining to a table triggers scan of both tables in view: declare @table table (QueryID int) insert into @table select 2;
selectvt.TestName fromdbo.vw_Test vt join@table t on vt.QueryID = t.QueryID
Using the showplan I can see why the optimizer ends up scanning all tables, but maybe there is a way to force it to use the QueryID param evaluation earlier in the filtering.
I have a report that I am building that consist of 4 multi-value selections from four different queries. When I choose "Select All" from all the drop downs and click on "View Reports", it blanks out all the selections from one of the drop downs.
Can someone please point me in the right direction on this problem. The report will run sometimes, but most of the time it will not.
Hi thereGot a interesting problem (depends on a point of view.....).Background :-Agent (within Domino) is run daily basis which extracts data fromDomino Notes application to SQL Server 2000 database. Agent firstremoves ALL contents and then appends ALL data.Reporting Tool is SQL Reporting Services (very cool !!).Problem :-Within Domino Notes, it can have a field which is mult-value fieldi.e. contain multi valuese.g.(from Helpdesk application)!HowTo!!Access Email;!HowTo!!Access the Web;etc..Need to create a view, then use sql to create stored proc, to be usedas the dataset for report within SQL Reporting services.Format. (using above as data as example)!HowTo!!Access Email; (1 row) WWL/SDR/04023/010 (DocID)!HowTo!!Access the Web; (2 row) WWL/SDR/04023/010 (DocID)I have a unique KEY within table called DocID. The report will have 8separate datasets (i.e. using subreports), all linked back to UniqueKey. That's easy.The dataset causing me hassle is the 1 above. How do u split outvalues as separate rows ?Name of field called --> "ImpFunctionsImpacted".Also, notes expert, who's working on the agent, tell's me the data canbe split either as a comma or semi-colon.Any suggestions most welcome.
JOIN (Select DISTINCT qcParent_ID, dbo.openItemsIntoList(' / AND ',qcParent_ID) as openItemListToFix FROM dbo.a3_qcItems2Fix) i
on a.qcParent_ID = i.qcParent_ID
But data is needed from 3 tables... - Created a VIEW that returns all (82) rows (negating distinct of the function on qcParent_ID) - Failed Miserably Integrating Function call into a multi-table select (inexperienced with complex joins)
JOIN (Select DISTINCT qcParent_ID, dbo.openItemsIntoList(' / AND ',qcParent_ID) as openItemListToFix FROM dbo.a3_qcItems2Fix) i
on a.qcParent_ID = i.qcParent_ID
AND THEN THERES... Failing miserably on Integrating the Function call into This SELECT ON MULTI-TABLES:
How to integrate the Function call: JOIN (Select DISTINCT qcParent_ID, dbo.openItemsIntoList(' / AND ',qcParent_ID) as openItemListToFix FROM dbo.a3_qcItems2Fix) i
on a.qcParent_ID = i.qcParent_ID
into the multi-table Select relationships (while maintaining Where & Order By):
I have a list of ClassID that is stored based on users multi select on a listview
For example ClassID might contain
301 302 303 304
Now I need to find InstructorID where classID matches all the value in the above list.
I am using this query
Code: Dim assSQL = "Select InstructorID from ClassInstructors where ClassID = @P0" For i = 1 To classIDs.Count - 1 assSQL &= " UNION Select InstructorID from ClassInstructors where ClassID = @P" & i.ToString Next
[Code] ....
But the problem is the query is returning InstructorID where ClassID matches any of the ClassIDs. I want it to return Instructor ID where ClassID matches all of the ClassIDs in the string.
i have a table which has 2 columns 1 'report' 2 'part' now in my 'report' cloumn i have # with 6 digits ex. '111111' and 'part' has '1, 2, 3, 4,..to 50' i want to create a view that will put them together in format like this: 1111110001 1111110002 1111110003 .. and on it needs to be in 10 digits. is there anyway i can create a View or may be a column in the table which can create the #'s in this format.
What are some possible purposes of creating a view and how can it be used to reinforce data security. What description of circumstances can be used for a view to save re-programming?
I have a sql command with temporary tables, but whenever I am trying to create a view with temporary tables I am getting an error as below:
"Views or functions are not allowed on temporary tables. Table names that begin with ‘#’ denote temporary tables."
Please anybody let me know is it possible to create a view with temporary tables in SQL Server 2005.If not, then is their any way how I can create a view with temporary tables.
I have a sql command with temporary tables, but whenever I am trying to create a view with temporary tables I am getting an error as below:
"Views or functions are not allowed on temporary tables. Table names that begin with ‘#’ denote temporary tables."
Please anybody let me know is it possible to create a view with temporary tables in SQL Server 2005.If not, then is their any way how I can create a view with temporary tables.
I need to create a view that scores a research assessment. So I havequestions 1 through 10, but the problem is that Q3 and Q5-10 have to berecoded so that if the response is 3, it becomes 0, 2=1, 1=2 and 0=3.So this is what I have. I keep getting an error message that there is"incorrect syntax near the keyword THEN". I don't know which "THEN" itis referring to (or all of them)?? HELP! I am new to this.CREATE VIEW name ASSELECT ID, DATE, TOTAL=Q1+Q2+CASE WHEN (Q3=0 THEN 3 WHEN Q3=1 THEN 2 WHEN Q3=2 THEN 1 WHEN Q3=3 THEN0)+Q4+CASE WHEN (Q5=0 THEN 3 WHEN Q5=1 THEN 2 WHEN Q5=2 THEN 1 WHEN Q5=3 THEN0)+CASE WHEN (Q6=0 THEN 3 WHEN Q6=1 THEN 2 WHEN Q6=2 THEN 1 WHEN Q6=3 THEN0)+CASE WHEN (Q7=0 THEN 3 WHEN Q7=1 THEN 2 WHEN Q7=2 THEN 1 WHEN Q7=3 THEN0)+CASE WHEN (Q8=0 THEN 3 WHEN Q8=1 THEN 2 WHEN Q8=2 THEN 1 WHEN Q8=3 THEN0)+CASE WHEN (Q9=0 THEN 3 WHEN Q9=1 THEN 2 WHEN Q9=2 THEN 1 WHEN Q9=3 THEN0)+CASE WHEN (Q10=0 THEN 3 WHEN Q10=1 THEN 2 WHEN Q10=2 THEN 1 WHEN Q10=3THEN 0) ENDFROM tablename
I have a sql command with temporary tables, but whenever I am trying to create a view with temporary tables I am getting an error as below:
"Views or functions are not allowed on temporary tables. Table names that begin with €˜#€™ denote temporary tables."
Please anybody let me know is it possible to create a view with temporary tables in SQL Server 2005.If not, then is their any way how I can create a view with temporary tables.
I've created a number of tables, views, sproc, and functions whose names begin with "sys_", but when I tried to create a multi-statement table-valued function with this type of name, I got:
Server: Msg 1706, Level 16, State 2, Procedure sys_tmp, Line 9 System table 'sys_test' was not created, because ad hoc updates to system catalogs are not enabled.
I had a quick look in this forum for 1706 (and on Google) but couldn't find anything. Does anyone know for certain if this is a bug in SQL2K?
Thanks, Jos
Here's a test script: /* ---------------------------------------------------------------------------------------------------- T-SQL code to test creation of three types of function where the function name begins with "sys_". Jos Potts, 02-Nov-2006 ---------------------------------------------------------------------------------------------------- */
PRINT @@VERSION go
PRINT 'Scalar function with name "sys_" creates ok...' go
CREATE FUNCTION sys_test () RETURNS INT AS BEGIN RETURN 1 END go
DROP FUNCTION sys_test go
PRINT '' go
PRINT 'In-line table-valued function with name "sys_" creates ok...' go
CREATE FUNCTION sys_test () RETURNS TABLE AS RETURN SELECT 1 c go
DROP FUNCTION sys_test go
PRINT '' go
PRINT 'Multi-statement table-valued function with name "sys_" generates error 1706...' go
CREATE FUNCTION sys_tmp () RETURNS @t TABLE (c INT) AS BEGIN
And here€™s the output from running the test script in Query Analyser on our server: Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Scalar function with name "sys_" creates ok...
In-line table-valued function with name "sys_" creates ok...
Multi-statement table-valued function with name "sys_" generates error 1706... Server: Msg 1706, Level 16, State 2, Procedure sys_tmp, Line 11 System table 'sys_tmp' was not created, because ad hoc updates to system catalogs are not enabled. Server: Msg 3701, Level 11, State 5, Line 2 Cannot drop the function 'sys_test', because it does not exist in the system catalog.
-- declare table declare @Table1 table(ID int, Value int )-- Insert sample data provided into tableinsert into @Table1select 1 , 2 union allselect 2 , 1 union allselect 7 , 2 -- declare table declare @Table2 table(ID int, Value int )-- Insert sample data provided into tableinsert into @Table2select 1 , 2 union allselect 5 , 1 union allselect 3 , 2 -- declare table declare @Table3 table(ID int, Value int )-- Insert sample data provided into tableinsert into @Table3select 1 , 2 union allselect 2 , 1 union allselect 3 , 2 -- declare table declare @Table4 table(ID int, Value int )-- Insert sample data provided into tableinsert into @Table4select 5 , 2 union allselect 2 , 1 union allselect 10 , 2 /*Is there anyway I can write one sql query which will give me the following result:IDs = All unique ID from all tablesSumOfAllValueFromAllTables =for the same ID, value from Table1+for the same ID, value from Table2 + so on ...IDs - SumOfAllValueFromAllTables===============================1 - 62 - 33 - 45 - 37 - 210 - 2*/--I have tried by the following way, but for more tables, it is becoming complicated, because I have 12 tables.--Is there any better way to do it? Thanks for the help.select coalesce(t1.ID,t2.ID) as IDs, coalesce(sum(t1.value),0) +coalesce(sum(t2.value),0) as SumOfAllValueFromAllTablesfrom @Table1 t1full join @Table2 t2 on t1.id=t2.idgroup by t1.ID,t2.IDorder by IDs
I currently have a website with a page that displays the flags/ keys of the entire roster of guilded characters. Some more background is that I run a website for my Everquest guild, users can log in , create characters , and update their flags / keys. There are 4 associated tables that are used in displaying the flags. the Characters , Flags, Expansion, and Char_Flags tables. The char_flags table consists of 2 foreign keys and a bit field for enabled / disabled. Char_flags = char_fk, flag_fk, enabled (bit) The select statement I'm currently using to get information is as follows. SELECT Expansion.ExpansionName, Flags.Flag_Name, Characters.Char_Name, char_flags.enabled FROM char_flags INNER JOIN Flags ON char_flags.Flag_FK = Flags.Flag_PK INNER JOIN Expansion ON Flags.Expansion_FK = Expansion.ExpansionPK INNER JOIN Characters ON char_flags.Char_FK = Characters.Char_PK WHERE (Expansion.ExpansionPK = @expansion) That displays the information in a format like Expansion Name, FlagName, CharacterName, Enabled. And there are usually 10 -15 flags per expansion so this lists the expansion 10 times, each flag name, then the character name 10-15 times, and wether or not the flag is enabled.
I would like to display the information a little differently and I'm not sure how to. I would like to display the inormation like this Flag Name 1 Flag Name 2, Flag name 3, Flag Name 4, etc... Char 1 X X X Char2 X X X Char 3 X X X X Char 4 Char 5 etc where the characters make up the first column, the flag names make up the first row(headers) and wether or not the flag is enabled is in the column under the corresponding flag. This way the name of the flag, and the character name are only displayed one time instead of the flags and character names being repeated over and over. If anyone can help me on how to display the data I would appreciate it. Here is a link to the page to show how it looks now if it helps www.shattereddestinies.net/flagstest.aspx Thanks Brad Fortner
I have a query on one of my pages that is just too large to keep in the page, so I need to reference a stored view in sql. I'm kind of new to this, so I'm having trouble getting the syntax right. The query is just a simple select statement using the results of several textboxes as parameters. I know how to do the query inside an asp.net page, but when I move it to sql, I don't know how to reference the textbox value i.e. @textbox. Here's what I have so far: USE [Maindb] GO CREATE VIEW [tblMain_view] (@textbox nvarchar(30)) ???? AS SELECT dbo.tblMain.Field1, .... FROM dbo.tblMain WHERE dbo.tblMain.Field1 = @textbox and ....
First of all, I know that where I declare @textbox is wrong, so where is the right place to declare it? Also, how do I reference the view from the webpage and do I still use: cmd.SelectCommand.Parameters.Add . . . in the page to establish the value. Anyone know a good tutorial on this. All the ones I've found were either in C# or didn't really apply. I need to know how to do this in VB. Thanks
Hi I have two SQL server 2000 machine in my corporateHow to create a SQL View if the database table is located on another SQL server?Example :I am currently working at SQL Server "S01", but need to create a sql view to table customer on "Marketing" database located on SQL Server "S02"if the table is reside on the same machine then it's simple for me, but this customer table is not located on "S01" but located on different machine "S02"how to do this? any tutorial?thanks a lotYonscun
Hi guys, I am trying to create a view through SP as follows, but I am getting as error as Invalid syntax near view. Please let me know where I am doing wrong.
Thanks,Rau CREATE PROCEDURE PurgeReport @intJob as int AS if @intjob=1 begin CREATE VIEW Purge AS SELECT Btch_id AS Batch_ID, DLN, Process,End_DLN, Job, Tcode FROM Batch_HDR end
Need help in writing a query. I have a table contains details about an item. Each item belongs to a group. Items have different status. If any one of the item in a group is not "Completed", then the itemgroup is in state incomplete. if all the item under the group is completed then the item group itself is completed. Now I need to create a view with itemgroup and itemstatus. Suppose I have five records
I'm using SQL Server 2008 to solve the following exercise.
I have three Tables: Table CUSTOMER with columns: CustomerID,Company,ContactName,Phone Table ORDER with columns: OrderID,OrderDate,ShippedDate,ShipperID,Freight,CustomerID Table Shipper with columns:ShipperID,Company,Phone
I need to Create a view listing the shipper and the number of unshipped orders. I have been trying to use joins or possibly subqueries, but I'm not getting a correct result for a ShipperID with NO unshipped orders.My view should looks like: ShipperID, Company, Phone, UnshippedOrders
I am looking to create a new view by combining two tables, but i would like to create a new column in the view that identifies what table the data came from. For example I have a Table A and Table B, using a union i combined the two table but i want a new column titled Source which could be populated by A or B.
This is my code so far:
Create View Table_AB As Select *From Table_A Union All Select*From Table_B
Hi im trying to create a view that creates another column for a table and then adds the value but i cant get it could sum1 help
Create View BONUS_PAYOUTS As
Alter Table Employee Add EMP_BONUS Decimal(6,2)
Update Employee Set EMP_BONUS = EMP_PCT / 500000
Select Employee.EMP_LNAME, Employee.EMP_FNAME, Employee.EMP_PCT, Position.POS_TITLE, Employee.EMP_BONUS From Employee Inner Join Position On Employee.POS_ID = Position.POS_ID
(1) create view qcostcentre as select * from dbo.costcentre.dtblcostcentre
>ERR: Msg 208, Level 16, State 1, Procedure qcostcentre, Line 4 Invalid object name 'dbo.costcentre.dtblcostcentre'.
(2) create view qcostcentre as select * from costcentre.pcusers.dbo.dtblcostcentre
>ERR: Msg 7202, Level 11, State 2, Procedure qcostcentre, Line 4 Could not find server 'costcentre' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
note: pcusers is a user of 2 database (costcentre & datamaster) with dbo_datareader owned & role schema .
I am trying to create an indexed view, on a date from a date dimension table...I am new to SQL, and I am at a loss of ideas on this one. Any help would be greatly appreciated!
Here is the Error I am given
"Msg 4513, Level 16, State 2, Procedure VEW_F_MZT_ORDER_HEADER_DAY, Line 3
Cannot schema bind view 'JJWHSE.VEW_F_MZT_ORDER_HEADER_DAY'. 'JJWHSE.VEW_F_INVC_SHIP_TO' is not schema bound.
Msg 1939, Level 16, State 1, Line 1
Cannot create index on view 'VEW_F_MZT_ORDER_HEADER' because the view is not schema bound."
Here is my code..
CREATE VIEW [JJWHSE].[VEW_F_MZT_ORDER_HEADER_DAY] WITH SCHEMABINDING
AS
SELECT TEW_D_DT.DT_KEY AS DATE_KEY,
VEW_F_MZT_ORDER_HEADER.LOCATION_KEY AS LOC_KEY,
TEW_D_LOC.LOC_DESC AS LOC_DESC ,
TEW_D_LOC.RGN_DESC AS REGION_DESC,
TEW_D_LOC.DISTRICT_DESC AS DISTRICT_DESC,
ISNULL(SUM(VEW_F_INVC_PAY_EXT.PRORATED_NET_PRICE),0) AS CONCIERGE_FLASH,
Hi all. I'm new to SQL Server and I've run into a tough problem. I currently have a SQL Server database with 54 US states/territories tables. I'm working on creating a view so that I can view all the states data in a single table-like structure. I've mapped all the columns from all the different tables to their new names and made sure that all the new attributes match. When I use the Management Express tool to open the view, it executes but fails because for some reason it's chosen to map a field called 'id' into an int, rather than a varchar. Is there a way to force the view to treat the 'id' as a varchar and not an int? I can't see a way to change its type through the GUI tools.
I have been working on a view and now I want to create a table which has not only similar columns and datatypes but also same data as I have fetched in the view. How can I do that? Do I have to use a cursor or there is a simpler way to do it? Please advice me here..
Can I use Create table <tablename> as ( Select * from <viewname>) ?????
Hello,I am having an error when using the CREATE VIEW statement and trying to execute it from an ASP.net page. I get an error that says something along the lines of 'dbo' schema does not exist or you do not have permissions to access it. I have checked and the user has been granted every permission I can think of (including setting it to the DB owner), but it does not work. I am able to execute all the Select, Update, Insert statements that I wish to, and when I copy the statement into the SQL 2005 Management studio query menu it works perfectly, it just gives the error message from the ASP.net page.Here is an example (or as close as I can remember at this point) of some code I tried:myCommand.Connection = myConnectionmyCommand.Open()myCommand.CommandText = "Create View TestView as Select 1, 2"myCommand.ExecuteNonQuery()myCommand.Close() Any help would be appreciated.Thanks,StuporMan
I am still confounded with how to create a VIEW involving multiple DATATABLES in a DATASET. Could someone point out a MSDN page where I can see this working and how I go about doing this? Let me re-iterate - I want to create a VIEW that uses JOIN type statements that involves multiple DATATABLES in my loaded DATASET.
I need a view that will: 1) select the distinct values in a column (the easy part) 2) assign the equivalent of an identity column to each row containing the distinct value it returns.
For example TableA contains 25 records but one of the columns only contains three unique values (say, 'A','B', & 'C').
This is what I want my view to return:
1 A 2 B 3 C
In other words, I need the view to assign the 1, 2 and 3 to the individual rows.
