Recently we upgraded our server from SQL 7.0 to SQL 2000. I created a
new view in Enterprise Manager which pulls data from a linked server's
database. When I write the view...
CREATE VIEW dbo.VIEW1
AS
SELECT *
FROM LinkedServerName.DatabaseName.dbo.TableName
and I click on the run button it changes to:
CREATE VIEW dbo.VIEW1
AS
SELECT *
From LinkedServerName.DatabaseName.dbo.TableName TableName_1
I can still see the correct results, but the users can't- Once I
remove the TableName_1 they can see the results.
When I tried creating a view and did not use a linked server,
everything worked fine. Anyone have any answers?
Bit of a SQL server newbie here, I have two tables; Fish (containing Name and ID) and Fishing (containing just a concatonatted FishingID), but to relate the tables i would have to join (reference) the fields in the Fish table ie: fishname and FishID to match the other tables FishingID.
Every time i concanonate the fishname and FishID in the criteria the view doesent run but if i actually write an example FishingID in the criteria the view runs.
How do i refernce the fields in the criteria section?
I am not sure if this can be done I have never tried to do this before. What I am looking to do is to create a view in a database to select information from another table in a different database. Is this possiable?
can anyone tell me why when I create a view if I do this in my select statement "CAST(CrewNo AS char(10)) AS CrewNo" and then save the view the above CAST statement will be changed to this "CAST(CrewNo AS char(10)) AS Expr1". After I have it saved and then go back and change it back to "CAST(CrewNo AS char(10)) AS CrewNo" it will stay that way. Just looking for a reason why this is happening as I don't know if I'm doing something wrong or not.
Hi NG,I'm trying to run the following script as part of a larger scriptfileusing isql:USE MYDBGOCREATE VIEW AGENCYSEARCH_mwbASSELECTId,SaveId,Checked,SessionId,substring(convert(char(26),InsertedDate,109),1,20) +substring(convert(char(26),InsertedDate,109),25,26 )"InsertedDate",Name,key,sortorder,initnameFROM MYDB.dbo.AGENCYSEARCHGOThis results in an error:Msg 156, Level 15, State 1, Server Myserver, ProcudereAGENCYSEARCH_mwbIncorrect syntax near keyword 'key'OK, so my guess is that 'key' is a reserved word and cannot be used inthis context. But the column in this table is named 'key' so how can Icreate my view?Probably a basic problem for all the cracks over here but I'm just asimple Oracle DBA :-))))t.i.a.
In database permissions I have granted a user rights to create a view. When she tries to save the view (even save as) it automatically wants to save it to the dbo schema and says she does not have rights to save to the dbo schema.
Two questions:
1) Can I set it up to where she can save a view to a schema which she is the owner?
2) If not, then what permissions must be set to allow her to create / save views but not be able to create, etc. other objects such as tables and stored procedures?
Again i need help with a couple of issues in migrating from Oracle to SQL Server 2005. I need the equivalent sentence in SQL Server of the following sentence:
CREATE OR REPLACE VIEW IBA_MPDATOSGENERALES AS ( SELECT IBA_MPPROCEDXLOC.IDPROCEDENCIALOCALIZACION, IBA_MPREQUERIMIENTOS.APLAZADOREQ, IBA_MPACCIONESXREQ.FECHAFINALAXR, IBA_USUARIOS.NOMBREUSU FROM IBA_MPPROCEDXLOC, IBA_MPREQUERIMIENTOS, IBA_MPACCIONESXREQ, IBA_USUARIOS WHERE IBA_MPPROCEDXLOC.IDPROCEDENCIALOCALIZACION = IBA_MPREQUERIMIENTOS.IDPROCEDENCIALOCALIZACION AND IBA_MPREQUERIMIENTOS.IDREQUERIMIENTO = IBA_MPACCIONESXREQ.IDREQUERIMIENTO(+) AND IBA_MPACCIONESXREQ.USERNAME = IBA_USUARIOS.USERNAME AND IBA_MPACCIONESXREQ.TIPOACCIONAXR = 'S' AND IBA_MPACCIONESXREQ.CERRADAAXR = 'N' AND NOT IBA_MPACCIONESXREQ.FECHAFINALAXR IS NULL UNION SELECT IBA_MPPROCEDXLOC.IDPROCEDENCIALOCALIZACION, IBA_MPREQUERIMIENTOS.APLAZADOREQ, IBA_MPACCIONESXREQ.FECHAFINALAXR, IBA_USUARIOS.NOMBREUSU FROM IBA_MPPROCEDXLOC, IBA_MPREQUERIMIENTOS, IBA_MPACCIONESXREQ, IBA_USUARIOS WHERE IBA_MPPROCEDXLOC.IDPROCEDENCIALOCALIZACION = IBA_MPREQUERIMIENTOS.IDPROCEDENCIALOCALIZACION AND IBA_MPREQUERIMIENTOS.IDREQUERIMIENTO = IBA_MPACCIONESXREQ.IDREQUERIMIENTO(+) AND IBA_MPACCIONESXREQ.USERNAME = IBA_USUARIOS.USERNAME AND IBA_MPACCIONESXREQ.TIPOACCIONAXR = 'N' AND IBA_MPACCIONESXREQ.CERRADAAXR = 'N' AND NOT IBA_MPACCIONESXREQ.FECHAPROXEJECUCIONAXR IS NULL UNION SELECT IBA_MPPROCEDXLOC.IDPROCEDENCIALOCALIZACION, IBA_MPREQUERIMIENTOS.APLAZADOREQ, IBA_MPACCIONESXREQ.FECHAFINALAXR, IBA_USUARIOS.NOMBREUSU FROM IBA_MPPROCEDXLOC, IBA_MPREQUERIMIENTOS, IBA_MPACCIONESXREQ, IBA_USUARIOS WHERE IBA_MPPROCEDXLOC.IDPROCEDENCIALOCALIZACION = IBA_MPREQUERIMIENTOS.IDPROCEDENCIALOCALIZACION AND IBA_MPREQUERIMIENTOS.IDREQUERIMIENTO = IBA_MPACCIONESXREQ.IDREQUERIMIENTO(+) AND IBA_MPACCIONESXREQ.USERNAME = IBA_USUARIOS.USERNAME AND (IBA_MPACCIONESXREQ.CERRADAAXR = 'S' OR (IBA_MPACCIONESXREQ.CERRADAAXR = 'N' AND ((IBA_MPACCIONESXREQ.TIPOACCIONAXR = 'N' AND IBA_MPACCIONESXREQ.FECHAPROXEJECUCIONAXR IS NULL) OR (IBA_MPACCIONESXREQ.TIPOACCIONAXR = 'S' AND IBA_MPACCIONESXREQ.FECHAFINALAXR IS NULL)))) )
I know that (+) is for outer joins (left or right). I also know that UNION may mean FULL OUTER JOIN. but this query? The real query is a lot more complex, well actually there are just more fields needed from more tables but with this exmple is enough. The bottom line is that i need data from various tables, most of them involved in a JOIN clause. Also check out that in SQL Server doesnt exists the REPLACE word or does it? Thank you in advance,
Hello, I need to create a view which will include an additional table every week of our financial year. Example: Every week we create a table called Sales_YYYY_WW where YYYY is the Year and WW is the Week, so after the first week we have the table Sales_2007_01 and after the second week we have Sales_2007_01 and Sales_2007_02 etc.. These tables are created evey week and don't exist at the start of the financial year. I'd like to create a view at the beginning of each year to encompass each table evey week as it is created. If all of the tables were there at the start of the year it would be simple: CREATE VIEW VIEW_SALES_2007 AS SELECT * from Sales_2007_01 UNION ALL SELECT * from Sales_2007_02 etc. But I don't know how to achieve this when the tables don't already exist. I don't want to have to edit the view each week to add in the new table.
Forgive me if I'm asking a simple question, but I'm new to database administration I'm trying to migrate a database from a server running Windows 2000 Server and SQL Server 2000 to a new machine running Windows Server 2003 and SQL Server 2005. I moved a copy of a recent database backup file to the new server, and created a new database with Restore From Backup. The tables seem to have restored fine, but my views are non-existent. Has anyone seen this problem before, or does anyone know of something I may have skipped?
I have 2 views which contain the following fields: EVENT, WEEK, SUBSCRIPTION, QTY, GROSS_AMOUNT, SEASON
The 2 views differ by SEASON. I'm attempting to combine the 2 views in to a single view or table grouping by EVENT, WEEK and SUBSCRIPTION:
EVENT, WEEK, SUBSCRIPTION, Q6 (qty of season 1), A6 (gross_amount of season 1), Q7 (qty of season 2), A7 (gross_amount of season 2)
Below is my select command:
------
SELECT TOP 100 PERCENT dbo.vw_SEASON06.EVENT, SUM(dbo.vw_SEASON06.QTY) AS Q6, SUM(dbo.vw_SEASON06.GROSS_AMOUNT) AS A6, SUM(dbo.vw_SEASON07.QTY) AS Q7, SUM(dbo.vw_SEASON07.GROSS_AMOUNT) AS A7, dbo.vw_SEASON06.WEEK, dbo.vw_SEASON06.SUBSCRIPTION
FROM dbo.vw_SEASON06 FULL OUTER JOIN dbo.vw_SEASON07 ON dbo.vw_SEASON06.WEEK = dbo.vw_SEASON07.WEEK AND dbo.vw_SEASON06.SUBSCRIPTION= dbo.vw_SEASON07.SUBSCRIPTION AND dbo.vw_SEASON06.EVENT = dbo.vw_SEASON07.EVENT
GROUP BY dbo.vw_SEASON06.EVENT, dbo.vw_SEASON06.WEEK, dbo.vw_SEASON06.SUBSCRIPTION
ORDER BY dbo.vw_SEASON06.EVENT
-----
This creates the view but there are some issues. If an 'EVENT' exists in dbo.vw_SEASON07.EVENT and doesn't exist in dbo.vw_SEASON06.EVENT the value of the field 'EVENT' is set to NULL because the 'EVENT' name is returned from dbo.vw_SEASON06.EVENT. The same issue exists for 'SUBSCRIPTIONS' and 'WEEK'.
How can I create a single view/table that will include all the data from these 2 views without the NULL values in EVENT or SUBSCRIPTION?
I have problem that Im sure others must have had before so I am looking for advice on the best way to solve it. I have a table of text information tbl_base which is related to another table containing tags, which can contain several rows for each row in base.
I want to create an indexed view of the data for full text search. I would like to select all the tags related to a particular row in the tbl_base together into a string and join it to the end of the tbl_base table so they can be indexed along with the tbl_base data for full text indexing. Ive tried several methods but I am never able to make an index on my view because it say I cant use COALESCE, or Cursors.
Sample data here :
tbl_base
id | Text ------------------------ 1 | BLah blah 2 | Dum de dum 3 | HAr HAr
tbl_base_tags
tagID | base_id | TagText -------------------------- 1 | 1 | first 2 | 1 | second 3 | 1 | third 4 | 2 | fourth
The view I want to end up with will look like this :
vw_tables
base_id | Text | Tags ------------------------------------- 1 | Blah blah | first second third 2 | dum de dum | fourth
How can I achieve this? Do I have to index all the tables seperately?
Hi, I have a problem, maybe someone can help me.I'm traing to create a view with a Linked ServerThis query works great:select id, descrfrom SERVER.DB.dbo.TABLEWhen I tray to create the view:CREATE VIEW dbo.View1 WITH SCHEMABINDINGASselect id, descrfrom SERVER.DB.dbo.TABLEGOI have this error:Server: Msg 4512, Level 16, State 3, Procedure Pais2, Line 3Cannot schema bind view 'dbo.View1' because name 'SERVER.DB.dbo.TABLE'is invalid for schema binding. Names must be in two-part format and anobject cannot reference itself.So I try this:CREATE VIEW dbo.View1 WITH SCHEMABINDINGASselect SERVER.DB.dbo.TABLE.id, SERVER.DB.dbo.TABLE.descrfrom SERVER.DB.dbo.TABLEGOI have this error:Server: Msg 117, Level 15, State 2, Procedure Pais2, Line 3The number name 'SERVER.DB.dbo.TABLE' contains more than the maximumnumber of prefixes. The maximum is 3.Then I try this:CREATE VIEW dbo.View1 WITH SCHEMABINDINGASselect a.id, a.descrfrom SERVER.DB.dbo.TABLE as AGOI Have this errorServer: Msg 4512, Level 16, State 3, Procedure View1, Line 3Cannot schema bind view 'dbo.View1' because name'iservsql1.osderrhh.dbo.pais' is invalid for schema binding. Namesmust be in two-part format and an object cannot reference itself.This query alone works great:select a.id, a.descrfrom SERVER.DB.dbo.TABLE as AThe names aren't what I describe here (id is not valid without []).ANY IDEAS?!??!?!I don't know what else can I do.I need help!!!TANKS A LOT!!!!!!!!
Lalitha writes "Can I use DML statements against indexed views? If yes how it works internally, means will the pages gets locked during update and inserts and when the base tables get reflected of these modified data?"
hi all, i had a view in my project, i am inserting a record in to that view(View contains a identity key for a column), with in stored procedure i am inserting a record i am not passing the identity key value to the insert statement. The record is getting inserted,Based on the identity key(i am getting the identity key value with Scope_Identity()) and with that value i am inserting records into another two tables.In this scenario every thing is working fine. but now i am trying to place a trigger(instead of insert trigger) on the view, when i placed,it is not inserting record into first table,so i am not able to get the identity value of that record and process failed. how can this achived, let me know.
I am using the import/export wizard to import all the objects from one database into another.My problem is that any views that use User Defined Functions are failing because the view is being created before the UDF.I have searched for this error, and found the following microsoft article:http://support.microsoft.com/kb/300272/en-usThe article says that I should upgrade to the latest service pack. The problem with this is that I already have the latest service pack When I run the following:SELECT @@VersionI get: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) Could anyone please help me solve this problem.Jag
I wonder if anyone can help me - I am trying to write a little application which recompiles any views dependent on a given view. I have a script which will identify any views containing a given text string, but I now need a way to force SQL Server to refresh its cache info (or recompile) the views. I have to admit that I am unclear on exactly how the caching works; i have tried using sp_recompile, but this does not seem to do the job. Does anyone have any ideas?
I am trying to write some admin only procedures which will collect information to one of my development server from other production and development servers.
I have created linked servers to access these other servers on the development server. This development server is SQL Server 2000 EE. Other servers which I want to access are 2000 and 2005 (vaious editions)
E.g I have another development server called PRODTEST which is SQL Server 2005 and on the development server I have created a linked server pointing to PRODTEST called TESTLINKSRV. I want to access new object catalog view (as I do not want to use sysobjects)
When I run the following query
SELECT * FROM [TESTLINKSRV].[DBNAME].[sys].[objects]
I get following error,
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName=' TESTLINKSRV ', TableName='" DBNAME "."sys"."objects"'].
Msg 7314, Level 16, State 1, Line 1
OLE DB provider ' TESTLINKSRV ' does not contain table '"DBNAME"."sys"."objects"'. The table either does not exist or the current user does not have permissions on that table.
So I try this query
SELECT * FROM [TESTLINKSRV].[DBNAME].[sys.objects]
and I get following error
Msg 208, Level 16, State 1, Line 1
Invalid object name TESTLINKSRV.DBNAME.sys.objects'.
So bottom line is how do I access catalog views on a 2005 server from a 2000 server using linked server?
I hope someone understands what I am trying to achieve. Please let me know what is it that I am doing wrong. Thank you
Fellow database developers,I would like to draw on your experience with views. I have a databasethat includes many views. Sometimes, views contains other views, andthose views in turn may contain views. In fact, I have some views inmy database that are a product of nested views of up to 6 levels deep!The reason we did this was.1. Object-oriented in nature. Makes it easy to work with them.2. Changing an underlying view (adding new fields, removing etc),automatically the higher up views inherit this new information. Thismake maintenance very easy.3. These nested views are only ever used for the reporting side of ourapplication, not for the day-to-day database use by the application.We use Crystal Reports and Crystal is smart enough (can't believe Ijust said that about Crystal) to only pull back the fields that arebeing accessed by the report. In other words, Crystal will issue aSelect field1, field2, field3 from ReportingView Where .... eventhough "ReportingView" contains a long list of fields.Problems I can see.1. Parent views generally use "Select * From childview". This meansthat we have to execute a "sp_refreshview" command against all viewswhenever child views are altered.2. Parent views return a lot of information that isn't necessarilyused.3. Makes it harder to track down exactly where the information iscoming from. You have to drill right through to the child view to seethe raw table joins etc.Does anyone have any comments on this database design? I would love tohear your opinions and tales from the trenches.Best regards,Rod.
I opened SSMSexpress and attached to my sql 2005 database and automatically created the scripts for creating all the object in a SQL 2000 DB. The objects seem to be created but I did get script messages. Are these messages harmless?thanksMilton Msg 208, Level 16, State 1, Procedure Requestview, Line 3 Invalid object name 'dbo.requests'. Msg 15135, Level 16, State 1, Procedure sp_validatepropertyinputs, Line 147 Object is invalid. Extended properties are not permitted on 'dbo.Requestview', or the object does not exist. Msg 15135, Level 16, State 1, Procedure sp_validatepropertyinputs, Line 147 Object is invalid. Extended properties are not permitted on 'dbo.Requestview', or the object does not exist.
I have a SQL Server 7.0 backend server but I am using SQL Server 2000 EM to create DTS packages. Can the DTS packages be opened in SQL Server 7.0? We are contemplating the install of SQL 2000 but we are worried that any new DTS packages created won't work when opened on a SQL Server 7.0 server or that the SQL 2000 DTS functionality is just not compatibile with SQL Server 7.0 and will cause problems.
My platform is mainly windows nt4 server with two windows 2000 member servers. We recently acquired a higher spec machine to replace the old sql 7 server that is on windows NT4 box. The new server is a windows 2000 box and I have installed sql 7 on it.
The problem is when I try to create a new login it does not give me the option to select my domain. When I select new login and go for windows nt4 authentication the drop arrow for Domain only has the server name and not the domain name. If I manually input the domain name it rejects it.
I do not have Active Directory or a local dns server running on my network as we have no intention of migrating to Win2000 until next year. Our internet service provider handles our dns issues.
hi how could i create my own server connection in sql 2000.i have a problem because everytime i make a new server registration it always tell that it does not exist and access denied.pls help me.
AA!I am having problems on creating table through Enterprise Manager. Itgives me Error 1038 i.e. is as followsUnexpected ErrorODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot useempty objector column names. Use a single space if necessary.
I hope someone can help me out here? I have a fresh install of SQL Server 2000 (standard edition - sp4), on a server running Windows 2003 SE (v 5.2.3790). I am trying to create a system dsn on my client pc (running Windows 2000) to a newly created database in SQL server called BLISS.
I am getting the following error message:
Connection failed:
SQL State :'01000'
SQL Server Error: 11001
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]Connection Open (Connect()).
Connection Failed:
SQL State :'08001'
SQL Server Error: 6
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]Specified SQL server not found.
From support.microsft.com I have tried changing the TCPPort value to 0, but this has not helped. Does anyone have any further ideas please?
I am trying to find a solution to a thorny problem I have encountered...
I am a newbie in Sql Server 2000, but have quite a bit of experience in databases, both on PC and mainframe.
I am currently writing a program, which needs, among other things, to create Sql Server databases, to be located NOT in the default Mssql2000 directory, but in the user's home directory.
I am using an ADO connection and trying to execute it, as follows:
The text of the relevant part of the program goes like this:
Dim strCreateDatabase As String Dim MyCn As ADODB.Connection
Set MyCn = New ADODB.Connection
With MyCn .ConnectionTimeout = 25 .Provider = "sqloledb" .Properties("Data Source").Value = "MOP" .Properties("Initial Catalog").Value = "" .Properties("Integrated Security").Value = "SSPI" .Open End With
