I have SQL SERVER 2005, I have created a dtspkg using Business intellegence dev sttudio... However, can i see thsi pkg from the SQLS ERVER Managemnet studio??? I can see it from BI, But wanted to know if we can view dtspkgs created in 2005, under SQL SERVER Management studio.
View 6 RepliesHi, everybody
My question. I put 3 table in "view designer" and I want to do a view to refer me her for updatings and "inserts" in "visual basic" someone can tell me please how can I create a view for it.
MDH
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
Hi All,
I am using VS2005 Trial version, In my website project i can't able to see Designer View of all .aspx files but i can able to view code part. Earlier days i can able to see Designer page but now i can't do why? This problem occurs even new website project also.
Can any one help me?
Thanks in advance
Regards
Nat Raja
Hi,
I keep running into the following Error message when I try to execute the query below. Can anyone please give me an idea of how I can solve this issue and keep my data??
CREATE VIEW [dbo].[view_mCover]
AS
SELECT dbo.tProduct.intProductId AS dblCoverId, dbo.tProduct.strProductName AS strCoverName,strProductCode AS strCoverCode, dbo.tProduct.strProdPictUrl AS strCoverThumbnailSrc,
dbo.tProduct.intSubCategoryId AS dblCoverCategoryId, dbo.tProduct.strProdPictUrl AS strCoverSrc, dbo.tProduct.floatPrice AS floatCoverCost,
dbo.tProduct.intStockQuantity AS intProductStock, dbo.tProduct.isInDesignTool as isInDesignTool
FROM dbo.tProduct INNER JOIN
dbo.tCategory ON dbo.tProduct.intCategoryId = dbo.tCategory.intCategoryId
WHERE (dbo.tCategory.intCategoryId = 6) AND (dbo.tProduct.isDeleted = 0)
ERROR message
===============
Error Message: Column Name 'StrProdPictURL' appears more than once in the result column list
Thanks in Advance...
DollarJunkie
I have an application running SQL Server 2005. I have an external Database from an outside source that is running DB2(v9).
To get data into SQL Server I need to pull data from DB2 using views on an odbc connection.
Does anyone know of any performance issues or issues at all with SQL Server reading from DB2(v9) using an odbc connection?
I don't want to even get started trying if there are known errors.
Thanks for any help or suggestions
Luke
Hello everyone,
I'm using sqlserver 2005 with SQL management studio.I have a view and it has a problem.
The problem is that...SQL management studio automatically generated some sql script for me to update the view.But,it doesn't work.HELP ME please! This is the script it gave me...
-------------
UPDATE [skips].[dbo].[Schedule]
SET [ContractId] = <ContractId, int,>
,[DriverId] = <DriverId, int,>
,[TruckId] = <TruckId, int,>
,[Completed] = <Completed, bit,>
,[Day] = <Day, varchar,>
WHERE <Search Conditions,,>
--------------
When I try to execute it...I get this error ,
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '<'.
How do I make this work and update the view?
Thanks so much in advance
I am having no luck reporting this bug on the feedback link. I type in all information and click "Submit" and it just refreshes the page. So, here it is, I hope someone from MS will post this for me.
The problem appears to be a RIGHT OUTER JOIN on a VIEW causes the query to never return, or return very, very slowly. In 2000 SP4 the query returns in 2 seconds, in 2005 (2153) 64bit, it ran for 42 MINUTES before I killed it.
The is a duplication script to show a problem I have. This script uses the AdventureWorks database to demonstrate the problem. This is script is not the best, but shows the problem.
Please no comments on how to work around the problem. This query is generated by a user using and AdHoc reporting tool. I have NO control over how the user or the tool generates the SQL query.
The word "FAILURE" shows the original query which as far as I can tell never returns. The queries after, are different solutions, if I was able to change the user query, which I am not able to do.
-- Create Test Data Table CustomerListTom and Views
USE AdventureWorks
GO
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'ViewATom'))
DROP VIEW [ViewATom]
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'ViewBTom'))
DROP VIEW [ViewBTom]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'CustomerListTom') AND type in (N'U'))
DROP TABLE CustomerListTom
GO
CREATE TABLE [dbo].[CustomerListTom](
[CustomerID] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[RegionID] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FirstName] [dbo].[Name] NOT NULL,
[LastName] [dbo].[Name] NOT NULL,
[EmailAddress] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AddressLine] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AddressCity] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AddressState] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AddressZip] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AddressCountry] [dbo].[Name] NOT NULL,
[Phone] [dbo].[Phone] NULL,
[BillAddressLine] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[BillAddressCity] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[BillAddressState] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[BillAddressZip] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[BillAddressCountry] [dbo].[Name] NOT NULL,
[BillPhone] [dbo].[Phone] NULL,
[ModifiedDate] [datetime] NOT NULL,
)
INSERT INTO CustomerListTom
SELECT
CustomerID = RIGHT(cu.AccountNumber,6),
RegionID = RIGHT('000'+CAST(cu.TerritoryID AS VARCHAR(3)),3),
FirstName = ct.FirstName,
LastName = ct.LastName,
EmailAddress = ct.EmailAddress,
AddressLine = ad.AddressLine1,
AddressCity = ad.City,
AddressState = sp.StateProvinceCode,
AddressZip = ad.PostalCode,
AddressCountry = sp.[Name],
Phone = ct.Phone,
BillAddressLine = ad.AddressLine1,
BillAddressCity = ad.City,
BillAddressState = sp.StateProvinceCode,
BillAddressZip = ad.PostalCode,
BillAddressCountry = sp.[Name],
BillPhone = ct.Phone,
ModifiedDate = cu.ModifiedDate
--,*
FROM Sales.Customer cu
JOIN Sales.Individual id ON id.CustomerID = cu.CustomerID
JOIN Person.Contact ct ON ct.ContactID = id.ContactID
JOIN Sales.CustomerAddress ca ON cu.CustomerID = ca.CustomerID
JOIN Person.Address ad ON ad.AddressID = ca.AddressID
JOIN Person.StateProvince sp ON sp.StateProvinceID = ad.StateProvinceID
-- Create a big enough set of data for testing
DECLARE @i INT
SET @i = 1
WHILE (@i < 30)
BEGIN
INSERT INTO CustomerListTom
SELECT TOP 15 PERCENT
CustomerID = RIGHT(cu.AccountNumber,6),
RegionID = RIGHT('000'+CAST(cu.TerritoryID+@i AS VARCHAR(3)),3),
FirstName = ct.FirstName,
LastName = ct.LastName,
EmailAddress = ct.EmailAddress,
AddressLine = ad.AddressLine1,
AddressCity = ad.City,
AddressState = sp.StateProvinceCode,
AddressZip = ad.PostalCode,
AddressCountry = sp.[Name],
Phone = ct.Phone,
BillAddressLine = ad.AddressLine1,
BillAddressCity = ad.City,
BillAddressState = sp.StateProvinceCode,
BillAddressZip = ad.PostalCode,
BillAddressCountry = sp.[Name],
BillPhone = ct.Phone,
ModifiedDate = cu.ModifiedDate + CASE WHEN @i > 3 THEN 10 ELSE -25 END + @i
FROM Sales.Customer cu
JOIN Sales.Individual id ON id.CustomerID = cu.CustomerID
JOIN Person.Contact ct ON ct.ContactID = id.ContactID
JOIN Sales.CustomerAddress ca ON cu.CustomerID = ca.CustomerID
JOIN Person.Address ad ON ad.AddressID = ca.AddressID
JOIN Person.StateProvince sp ON sp.StateProvinceID = ad.StateProvinceID
SET @i = @i + 1
END
-- Cleanup - Delete Dups for PK
DELETE FROM CustomerListTom
WHERE CustomerID+RegionID IN (
SELECT CustomerID+RegionID
FROM CustomerListTom cu
GROUP BY CustomerID, RegionID
HAVING COUNT(*) > 1)
ALTER TABLE [CustomerListTom]
ADD CONSTRAINT [PK_CustomerListTom] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC,
[RegionID] ASC
)
GO
-- Create Views
GO
CREATE VIEW ViewATom
AS
SELECT *
FROM CustomerListTom cu
WHERE cu.RegionID = '004'
UNION
SELECT *
FROM CustomerListTom cu
WHERE CustomerID NOT IN
(SELECT CustomerID FROM CustomerListTom c2 WHERE c2.RegionID = '004')
AND (CustomerID + CONVERT(char(8), ModifiedDate, 112) + RegionID IN
(SELECT MAX(CustomerID + CONVERT(char(8), ModifiedDate, 112) + RegionID)
FROM CustomerListTom
GROUP BY CustomerID))
GO
CREATE VIEW ViewBTom
AS
SELECT DISTINCT CustomerID
FROM CustomerListTom
GO
QUERY:
USE AdventureWorks
-- FAILURE
-- This query FAILS to return in over 15 mins, cancelled
SELECT TABLEB.CustomerID,
TABLEC.RegionID,
TABLEC.LastName,
TABLEC.FirstName
FROM ViewATom TABLEA
RIGHT OUTER JOIN ViewBTom TABLEB ON TABLEA.CustomerID = TABLEB.CustomerID
RIGHT OUTER JOIN CustomerListTom TABLEC ON TABLEC.CustomerID = TABLEB.CustomerID
WHERE TABLEB.CustomerID IN
('012870','012997','011000','011001','011002','011003','011004','011005','011006','011007',
'011008','011009','011010','011011','011012','011013','011014','011015','011016','011017',
'011018','011019','011020','011150','011153','011144','017230','017220','017254','017257',
'025338','025333','025338','025397','025389','025424','025483','025485','025682','025673',
'029478','029405','029402','029317','029109','018393'
)
return
-- SOLUTIONS
-- Change WHERE TABLEB to WHERE TABLEA, this query returns in less than 1 second
SELECT TABLEB.CustomerID,
TABLEC.RegionID,
TABLEC.LastName,
TABLEC.FirstName
FROM ViewATom TABLEA
RIGHT OUTER JOIN ViewBTom TABLEB ON TABLEA.CustomerID = TABLEB.CustomerID
RIGHT OUTER JOIN CustomerListTom TABLEC ON TABLEC.CustomerID = TABLEB.CustomerID
WHERE TABLEA.CustomerID IN
('012870','012997','011000','011001','011002','011003','011004','011005','011006','011007',
'011008','011009','011010','011011','011012','011013','011014','011015','011016','011017',
'011018','011019','011020','011150','011153','011144','017230','017220','017254','017257',
'025338','025333','025338','025397','025389','025424','025483','025485','025682','025673',
'029478','029405','029402','029317','029109','018393'
)
return
-- Remove RIGHT OUTER on TABLEB, this Query returns in less than 2 seconds
SELECT TABLEB.CustomerID,
TABLEC.RegionID,
TABLEC.LastName,
TABLEC.FirstName
FROM ViewATom TABLEA
JOIN ViewBTom TABLEB ON TABLEA.CustomerID = TABLEB.CustomerID
RIGHT OUTER JOIN CustomerListTom TABLEC ON TABLEC.CustomerID = TABLEB.CustomerID
WHERE TABLEB.CustomerID IN
('012870','012997','011000','011001','011002','011003','011004','011005','011006','011007',
'011008','011009','011010','011011','011012','011013','011014','011015','011016','011017',
'011018','011019','011020','011150','011153','011144','017230','017220','017254','017257',
'025338','025333','025338','025397','025389','025424','025483','025485','025682','025673',
'029478','029405','029402','029317','029109','018393'
)
return
-- Drop PK and run ORIGINAL query, returns in less than 4 seconds
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[CustomerListTom]') AND name = N'PK_CustomerListTom')
ALTER TABLE [dbo].[CustomerListTom] DROP CONSTRAINT [PK_CustomerListTom]
GO
SELECT TABLEB.CustomerID,
TABLEC.RegionID,
TABLEC.LastName,
TABLEC.FirstName
FROM ViewATom TABLEA
RIGHT OUTER JOIN ViewBTom TABLEB ON TABLEA.CustomerID = TABLEB.CustomerID
RIGHT OUTER JOIN CustomerListTom TABLEC ON TABLEC.CustomerID = TABLEB.CustomerID
WHERE TABLEB.CustomerID IN
('012870','012997','011000','011001','011002','011003','011004','011005','011006','011007',
'011008','011009','011010','011011','011012','011013','011014','011015','011016','011017',
'011018','011019','011020','011150','011153','011144','017230','017220','017254','017257',
'025338','025333','025338','025397','025389','025424','025483','025485','025682','025673',
'029478','029405','029402','029317','029109','018393'
)
return
-- Create PK with NONCLUSTERED and run ORIGINAL query, returns in less than 1 second
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[CustomerListTom]') AND name = N'PK_CustomerListTom')
ALTER TABLE [dbo].[CustomerListTom] DROP CONSTRAINT [PK_CustomerListTom]
GO
ALTER TABLE [CustomerListTom]
ADD CONSTRAINT [PK_CustomerListTom] PRIMARY KEY NONCLUSTERED
(
[CustomerID] ASC,
[RegionID] ASC
)
GO
SELECT TABLEB.CustomerID,
TABLEC.RegionID,
TABLEC.LastName,
TABLEC.FirstName
FROM ViewATom TABLEA
RIGHT OUTER JOIN ViewBTom TABLEB ON TABLEA.CustomerID = TABLEB.CustomerID
RIGHT OUTER JOIN CustomerListTom TABLEC ON TABLEC.CustomerID = TABLEB.CustomerID
WHERE TABLEB.CustomerID IN
('012870','012997','011000','011001','011002','011003','011004','011005','011006','011007',
'011008','011009','011010','011011','011012','011013','011014','011015','011016','011017',
'011018','011019','011020','011150','011153','011144','017230','017220','017254','017257',
'025338','025333','025338','025397','025389','025424','025483','025485','025682','025673',
'029478','029405','029402','029317','029109','018393'
)
return
We have a script that drops the views on all the database tables and then creates them as part of version update of the database. For instance:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SPONSOR_SYSTEM_TD]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[SPONSOR_SYSTEM_TD]
GO
CREATE VIEW dbo.SPONSOR_SYSTEM_TD AS SELECT sponsor_installation_type_code,install_directory,sponsor_version,data_directory,system_install_text,record_status_code,mod_date_time,mod_login_id,create_date_time,create_login_id,replicate_to_server_flag,Sponsor_ID FROM SPONSOR_SYSTEM
GO
This above code is done for all 120 tables and has worked fine until now. All our clients have been using SQL 2000. Now we are getting clients with SQL 2005.
The problem is that for these clients using SQL 2005 on 20 of the tables we get the error:
Could not resolve expression for schemabound object or constraint.
And the View is not made for these tables. I saw where this could because the server and database collation is differemt but in this case it's the same for both. I can't see any differences so far between the tables that a View was successfully made for and the ones that it wasn't.
What else should I be looking for that would give this error?
Thanks
Hi all,
I have a problem reading binary data in MSSQL using the Server Mgmt Studio. All it shows in the column is "<Binary data>". Is there a way to view this data at least the SIZE?
Thanks.
Hi Gurus,
Where can i enable procs view option on 2005 SERVER..
Please advice..
Thanks,
ServerTeam
I have the following a view on a SQL2K box that uses the following SELECT statement:
SELECT
SF.SKU,
SAT.PublicationDate AS SATPubDate,
SAM.PublicationDate AS SAMPubDat
FROM SkuFlags SF
LEFT OUTER JOIN SpringArbor_ttlsparb SAT ON SF.ISBN = SAT.ISBN
LEFT OUTER JOIN SpringArbor_music SAM ON SF.ISBN = SAM.PrimaryKey
WHERE (
(
( SAT.PublicationDate IS NOT NULL ) AND
( SAT.PublicationDate <> '010001' ) AND
( GETDATE() <= DATEADD(day, -1, ( CAST(LEFT(SAT.PublicationDate, 2) + '/01/' + RIGHT(SAT.PublicationDate, 4) AS DATETIME) )))
)
OR (
( SAM.PublicationDate <> '010001' ) AND
( SAM.PublicationDate IS NOT NULL ) AND
( GETDATE() <= DATEADD(day, -1, ( CAST(LEFT(SAM.PublicationDate, 2) + '/01/' + RIGHT(SAM.PublicationDate, 4) AS DATETIME)))
)
)
The view works in SQL2K. When I try to run it under SQL2K5, I get a "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value." error. I know what the error is, the SAM.PublicationDate field has NULL values in it (and this is vendor supplied data that is updated frequently, so not dealing with NULL values isn't an option), so during the CAST function it's try to CAST NULL + /01/ + NULL into a DATETIME value and crashing.
My question is why this works in SQL2K and not SQL2K5?
Thanks,
Kevin
Hi all,
sorry for this question, but I am brand new with SQL Server 2005 Express.
I have nothing found in the documentation.
With which tool can I view the data records in my SQL Server?
I have tried with MS SQL Server Management Studion Express but had no success.
thanks for help
hawk
Hello,
We recently did an "in place" upgrade of SQL Server and SSRS 2000 to 2005.
After the upgrade we cannot view the exisiting reports. (although we can publish new reports using the VS2005 Designer and they show up just fine.)
We have SP2 installed for SSRS2005 and all SPs for SQL are up to date.
When we navigate to an existing report via the reporting services website, the report acts like it is trying to run and then returns and error message:
An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help
When we look at the logs we see that the report server cannot initialize the stream from the Catalog table.
aspnet_wp!library!1!08/28/2007-16:52:29:: Call to GetPropertiesAction(/Report Administration/All Process Dates, PathBased).
aspnet_wp!chunks!1!08/28/2007-16:52:29:: i INFO: Returning old chunk for: (fd690975-ec5b-4033-9664-81335e3990c0, 'CompiledDefinition', 0)
aspnet_wp!library!1!08/28/2007-16:52:29:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details., Could not initialize stream;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details.
aspnet_wp!library!1!08/28/2007-16:53:01:: i INFO: Exception dumped to: C:Program FilesMicrosoft SQL ServerMSSQL.2Reporting ServicesLogFiles flags= ReferencedMemory, AllThreads, SendToWatson
aspnet_wp!chunks!1!08/28/2007-16:53:01:: e ERROR: ### SnapshotConverter(fd690975-ec5b-4033-9664-81335e3990c0, True), Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details.
aspnet_wp!library!1!08/28/2007-16:53:01:: Call to GetSystemPropertiesAction()
We welcome any suggestions.
How do I view the data stored within SQL Express? What programs do I need to view the database information?
View 5 Replies View RelatedHi All,
I have created a table using VIEWS in SQL server 2005, now i want to be ablle to edit it in a datagrid but i cannot do so as i there is no primary key!
now does anybody know how to set a primary key constraint so i can set one of the fields as a primary key to identify the row?
many thanks
I have a table:
CREATE TABLE [dbo].[tx1]( [f1] [nvarchar](50) , [seq] [int] IDENTITY(1000,1) NOT NULL ) ON [PRIMARY]
SELECT *
FROM dbo.tx1
ORDER BY seq DESC
go
f1 seq
zz 1003
uu 1002
kk 1001
yy 1000
create view vx1 as
SELECT top 100 percent *
FROM dbo.tx1
ORDER BY seq DESC
go
select * from vx1
yy 1000
kk 1001
uu 1002
zz 1003
Background - I have a publication that propigates schema changes. I have a view in which I want to remove a column.
Error - Going by what the BOL says, I use Alter View and delete the column from my select statement. I issue the alter view command against the Publication database and it just "churns". I do not get any locking errors or any other type of error, but the statement never completes execution. I watched it run for 10 minutes and cancelled the query. Executing the same statement against a copy of the database that is not being published executes in 1, 2 seconds.
Here is what I am doing:
Old View: Select table1.record_number, table1.record_date, table1.status_code, table2.status_desc,
table2.txt_sort_order
FROM table1 join table2 on table1.status_code = table2.status_code
The query I am executing:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW myview
AS
Select table1.record_number, table1.record_date, table1.status_code, table2.status_desc
FROM table1 join table2 on table1.status_code = table2.status_code
Hello,
In SQL Server Management Studio (2005), 'Open table' command or a SELECT query displays table rows in a grid (or text).Please tell how to view a single row at a time i.e. all only ONE row is displayed at a time (evenly arranged to cover the screen).
This feature (Single record view) is available in other database client like TOAD.
Thank You
I have a complex view in my sql 2005 database.
The view returns a column that could be null (as the result of a left outer join).
The coulmn that is returned is an integer.
Everything works fine if I run the view from SQL 2005 Management Studio.
My column value is always null if I use ADO.NET's SqlAdapter to return a DataTable.
Has anybody seen this behaviour before?
Any help appreciated.
Regards,
Paul.
For SQL Server 2000 we have a user login mapped to msdb with database role membership of db_datareader and public checked. This seems to allow the developers to view the Management Activity monitor. For SQL Server 2005 the same mapping is in place but the developers cannot view the Management Activity monitor. Developers are NOT granted the sysadmin role, and should not have that role.
What permissions need to be set for SQL Server 2005 to allow users to view the Management Activity monitor? They should not be allowed to take actions on the activities.
Hi.
I building some reports with SSRS, and showing data in a table. The table has one Details row that forms at the end multiple rows with the tree view (+ and -).
Is there a way to open the tree view upwards and not downwards so that the children of level 2, 3 etc come over the parent row?
I've goggled it everywhere, but I don't seem to find the answer. If anyone could help me out, I would appreciated.
Thanks
Novi
Which sort of data (from application architecture) point of view it's worth to put in XML datatype (MS SQL 2005)?
View 2 Replies View RelatedHas anyone encounted a problem with Reporting Services (2005) with the following scenario:
A report with a dropdown parameter allowing a Select All choice -
If the selection list is fairly long and Select All is selected - if the user clicks the View Report button the selection list is completely reset (i.e. all items are unselected). This seems to happen if the user quickly clicks the View Report button immediately after clicking the Select All box on the parameter dropdown.
This happens inconsistently - some users experience it frequently while others do not see it at all (using the same parameter values, etc.)
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?
View 3 Replies View RelatedWrite a CREATE VIEW statement that defines a view named Invoice Basic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.
This is what I have so far,
CREATE VIEW InvoiceBasic AS
SELECT VendorName, InvoiceNumber, InvoiceTotal
From Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
[code]...
Hi guys 'n gals,
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....
I tried:
CREATE VIEW [qryMyView]
AS
EXEC pr_MyProc
and unfortunately, it does not let this run.
Anybody able to help me out please?
Cheers!
I compared view query plan with query plan if I run the same statementfrom view definition and get different results. View plan is moreexpensive and runs longer. View contains 4 inner joins, statisticsupdated for all tables. Any ideas?
View 10 Replies View RelatedIs it possible to connect SQL Sever 2005 Express from SQL Sever 2000 Enterprise Manager? Tried to connect but got the error message "must use SQL Server management tools to connect this sever"
Thanks
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.
I have this view in SQL server:
CREATE VIEW dbo.vwFeat
AS
SELECT dbo.Lk_Feat.Descr, dbo.Lk_Feat.Price, dbo.Lk_Feat.Code, dbo.SubFeat.SubNmbr
FROM dbo.Lk_Feat INNER JOIN
dbo.SubFeat ON dbo.Lk_Feat.Idf = dbo.SubFeat.Idt
When ever I open using SQL Entreprise manager to edit it by adding or removing a field i inserts Expr1,2.. and I don t want that. The result I get is:
SELECT dbo.Lk_Feat.Descr AS Expr1, dbo.Lk_Feat.Price AS Expr2, dbo.Lk_Feat.Code AS Expr3, dbo.SubFeat.SubNmbr AS Expr4
FROM dbo.Lk_Feat INNER JOIN
dbo.SubFeat ON dbo.Lk_Feat.Idf = dbo.SubFeat.Idt
I don t want Entreprise manager to generate the Expr fields since I use the real fields in my application.
Thanks for help
I was looking through our vendors views, searching for something Ineeded for our Datawarehouse and I came across something I do notunderstand: I found a view that lists data when I use it in t-sql,however when I try to use the statement when I modified the view (viaMS SQL Server Management Studio) I can not execute the statement. I getThe column prefix 'dbo.tbl_5001_NumericAudit' does not match with atable name or alias name used in the query.Upon closer inspection, I found two ON for the inner join, which I dontthink is correct.So, how can the view work, but not the SQL that defines the view?SQL Server 2000, up to date patches:SELECT dbo.tbl_5001_NumericAudit.aEventID,dbo.tbl_5001_NumericAudit.nParentEventID,dbo.tbl_5001_NumericAudit.nUserID,dbo.tbl_5001_NumericAudit.nColumnID,dbo.tbl_5001_NumericAudit.nKeyID,dbo.tbl_5001_NumericAudit.dChangeTime,CAST(dbo.tbl_5001_NumericAudit.vToValue ASnVarchar(512)) AS vToValue, dbo.tbl_5001_NumericAudit.nChangeMode,dbo.tbl_5001_NumericAudit.tChildEventText, CASEWHEN nConstraintType = 3 THEN 5 ELSE tblColumnMain.nDataType END ASnDataType,dbo.tbl_5001_NumericAudit.nID,CAST(dbo.tbl_5001_NumericAudit.vFromValue AS nVarchar(512)) ASvFromValueFROM dbo.tbl_5001_NumericAudit WITH (NOLOCK) LEFT OUTER JOINdbo.tblColumnMain WITH (NoLock) INNER JOIN---- Posters comment: here is the double ON--dbo.tblCustomField WITH (NoLock) ONdbo.tblColumnMain.aColumnID = dbo.tbl_5001_NumericAudit.nColumnID ONdbo.tbl_5001_NumericAudit.nColumnID =dbo.tblCustomField.nColumnID LEFT OUTER JOINdbo.tblConstraint WITH (NOLOCK) ONdbo.tblCustomField.nConstraintID = dbo.tblConstraint.aConstraintID AND(dbo.tblConstraint.nConstraintType = 4 ORdbo.tblConstraint.nConstraintType = 9 ORdbo.tblConstraint.nConstraintType = 3)UNION ALLSELECT aEventID, nParentEventID, nUserID, nColumnID, nKeyID,dChangeTime, CAST(CAST(vToValue AS decimal(19, 6)) AS nVarchar(512)) ASvToValue,nChangeMode, tChildEventText, 5 AS nDataType,nID, CAST(CAST(vFromValue AS decimal(19, 6)) AS nVarchar(512)) ASvFromValueFROM dbo.tbl_5001_FloatAudit WITH (NOLOCK)UNION ALLSELECT aEventID, nParentEventID, nUserID, nColumnID, nKeyID,dChangeTime, CAST(vToValue AS nVarchar(512)) AS vToValue, nChangeMode,tChildEventText, 2 AS nDataType, nID,CAST(vFromValue AS nVarchar(512)) AS vFromValueFROM dbo.tbl_5001_StringAudit WITH (NOLOCK)UNION ALLSELECT aEventID, nParentEventID, nUserID, nColumnID, nKeyID,dChangeTime, CONVERT(nVarchar(512), vToValue, 121) AS vToValue,nChangeMode,tChildEventText, 3 AS nDataType, nID,CONVERT(nVarchar(512), vFromValue, 121) AS vFromValueFROM dbo.tbl_5001_DateAudit WITH (NOLOCK)
View 1 Replies View RelatedA colleague of mine has a view that returns approx 100000 rows in about 60 seconds.
He wants to use the data returned from that view in an OLE DB Source component.
When he selects the view from the drop-down list of available tables then SSIS seems to hang without any data being returned (he waited for about 15 mins).
He then changed the OLE DB Source component to use a SQL statement and the SQL statement was: SELECT * FROM <viewname>
In this instance all the data was returned in approx 60 seconds (as expected).
This makes no sense. One would think that selecting a view from the drop-down and doing a SELECT *... from that view would be exactly the same. Evidently that isn't the case.
Can anyone explain why?
Thanks
-Jamie