Selecting A View And Selecting FROM A View Is Wildly Different
Feb 21, 2006
A 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
View 2 Replies
ADVERTISEMENT
Oct 3, 2001
I have created a view where the data is a subset of the table. When a non dbo user selects only the first column from that view, the query returns the value. However, when the non dbo user selects any of the other columns or a combination of columns I get an invalid column name error. The syntax of my query is correct because it works when I use QA using a login with dbo permissions. Ideas?
Any help would be appreciated.
View 1 Replies
View Related
Feb 6, 2006
I have created the view below, when I highlight the select statement in QA it works fine, but when I try to access the view I get back 0 records!
ie:
select * from Email_All
It believe its something to do with the last not in section:
EmailsAddresses.Email not in (
if I comment it out everything works fine!
does not make any sense...anyone have any clues?
they are all views except the EmailSubscriptions table.
-------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[Email_All]
AS
select * from
(
--SHELBY
select * from hcf..HM_EmailAddressShelby
union all
--KMS
select * from hcf..HM_EmailAddressKmai
union all
--EMAIL SUBSCRIPTIONS
select * from hcf..HM_EmailAddressWeb
where id in (select distinct id from hcf.dbo.EmailSubscriptions where List = 'PeriodicEmailsFromGreg' and status = 1)
) as EmailsAddresses
where
ltrim(rtrim(isnull(EmailsAddresses.Email,''))) <> ''
and
EmailsAddresses.Email not in (
select Email from hcf..ShelbyIdEmailSkip --OFF list SHELBY
union
select Email from hcf..KmaiIdEmailSkip --OFF list KMAI
union
select Email from hcf..EmailSubscriptions where List = 'PeriodicEmailsFromGreg' and status = 0 --OFF list PERIODIC FROM GREG
)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
View 2 Replies
View Related
Sep 11, 2013
I have a view that is involved in an import. Sometimes the import will fail with the following error:
Msg 242, Level 16, State 3, Line 2
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
I can recreate the error just from selecting from the view. Why would something be converting when just selecting from view?
View 4 Replies
View Related
Nov 23, 2005
I am having a problem with indexes on specific tables. For some reasona query that runs against a view is not selecting the correct index ona table. I run the same query against the table directly and it looksfine. Can anyone give me some insight? Thanks.PRODUCTION1:CREATE TABLE MyTest1 (ID INT IDENTITY(1,1), COLUMN1 CHAR(10), COLUMN2CHAR(10))CREATE CLUSTERED INDEX IDX_MyTest1 ON MyTest1 ON (ID)CREATE NONCLUSTERED INDEX IDX_MyTest2 ON MyTest1 ON (COLUMN2, ID)ARCHIVE1:CREATE TABLE MyTest1 (ID INT IDENTITY(1,1), COLUMN1 CHAR(10), COLUMN2CHAR(10))CREATE CLUSTERED INDEX IDX_MyTest1 ON MyTest1 ON (ID)CREATE NONCLUSTERED INDEX IDX_MyTest2 ON MyTest1 ON (COLUMN2, ID)REPORTDB:CREATE VIEW MyTest1 ASSELECT ID, COLUMN1, COLUMN2 FROM PRODUCTION1..MyTest1UNION ALLSELECT ID, COLUMN1, COLUMN2 FROM ARCHIVE1..MyTest1While in PRODUCTION1:SELECT ID, COLUMN2 FROM MyTest1 WHERE COLUMN2 = 'Testing'--> Clustered index seek PRODUCTION1..IDX_MyTest2--> Results returnedWhile in ARCHIVE1:SELECT ID, COLUMN2 FROM MyTest1 WHERE COLUMN2 = 'Testing'--> Clustered index seek ARCHIVE1..IDX_MyTest2--> Results returnedWhile in REPORTDB:SELECT ID, COLUMN2 FROM MyTest1 WHERE COLUMN2 = 'Testing'--> Index seek PRODUCTION1..IDX_MyTest2--> Bookmark lookup PRODUCTION1..IDX_MyTest1--> Index seek ARCHIVE1..IDX_MyTest2--> Bookmark lookup ARCHIVE1..IDX_MyTest1--> Concatenate data and results returned
View 3 Replies
View Related
Dec 15, 2006
I moved this from another forum because it seems more related to replication the longer I look into it!
The following code often dies on a deadlock. I have also seen "Spid x blocked by Spid x", where x is the spid of this connection. The view selects from several tables and some other views as well, and many of the underlying tables are being updated by replication. I have seen cases where the replication Insert proc participated in the deadlock, and the table being inserted into is joined twice in the view, suggesting that somehow this
causes the deadlock or makes a deadlock more likely?
SELECT * INTO dbo.tbl_stg_LoansMarketingFirstBorrower
FROM view_loans_marketing_first_borrower OPTION (MAXDOP 1)
I do not understand how selecting from a view can cause a deadlock with a
proc which does not reference tbl_stg_LoansMarketingFirstBorrower. Any
suggestions on how to diagnose this? I used (1204) to identify the proc and underlying table.
Thanks a bunch.
View 1 Replies
View Related
Sep 20, 2007
I've got a big problem that I'm trying to figure out:
I have an address table out-of-which I am trying to select mailing addresses for companies UNLESS a mailing address doesn't exist; then I want to select the physical addresses for that company. If I get multiple mailing or physical addresses returned I only want the most recently edited out of those.
I don't need this for an individual ID select, I need it applied to every record from the table.
My address table has some columns that look like:
[AddressID] [int]
[LocationID] [int]
[Type] [nvarchar](10)
[Address] [varchar](50)
[City] [varchar](50)
[State] [char](2)
[Zip] [varchar](5)
[AddDate] [datetime]
[EditDate] [datetime]
AddressID is a primary-key non-null column to the address table and the LocationID is a foreign key value from a seperate Companies table.
So there will be multiple addresses to one LocationID, but each address will have it's own AddressID.
How can I do this efficiently with perfomance in mind???
Thank you in advance for any and all replies...
View 2 Replies
View Related
Jul 23, 2005
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 Related
Jul 24, 2012
Write 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]...
View 2 Replies
View Related
Aug 24, 2007
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!
View 3 Replies
View Related
Mar 9, 2006
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 Related
Aug 14, 2000
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 1 Replies
View Related
Feb 17, 2006
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
View 4 Replies
View Related
Jun 10, 2004
Hi,
I am trying to select all entries from a database apart from the top/latest entry, via a stored procedure.
Below is the code that i have but i am doing it wrong somehow.
Any ideas?
Thanks
CREATE PROCEDURE spNEWSARCHIVE AS
SELECT *
FROM tblNEWS
WHERE NewsID NOT TOP 1
ORDER BY NewsID DESC
GO
View 7 Replies
View Related
Apr 14, 2006
Hi,I have the following set and would like to select all rows that havemin value of column 4 for a given column 1 and 2 group, irrespective ofcolumn 3, as below:C1 C2 C3 C4---------------------A B x 5A B y 10A BB XX 55A BB YY 11AA CC z 1AA CC zz 11Need---A B x 5A BB YY 11AA CC z 1using sql server 2000 (which does not provide rank or partition by)Thanks in advance,Tamas
View 2 Replies
View Related
Oct 27, 2006
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 Related
Sep 5, 2006
Hello,I have a gridview bound to a sqldatasource control. I have an emptydatatemplate setup. In the selecting event, I make sure that an actual select has been performed (and not on null data); however, I have cancelselectonnullparameters set to true to stop it, because initially it will be null.However, the selecting event runs, even when I cancel it, the emptydatatemplate shows up. How do I prevent that from occuring?
View 2 Replies
View Related
Jan 7, 2007
Hi Folks,
You can select all fields with:SELECT * FROM [TableName]
How do you select NOTHING or NONE with a SELECT statement?
Thanks
View 4 Replies
View Related
Feb 19, 2007
Dear All,
I need to select records between two datetimes.
My Database Structure and sample values are as below
Fromdate ToDate
2007-02-20 09:00:00.000 2007-02-20 12:00:00.0002007-02-20 08:00:00.000 2007-02-20 12:00:00.0002007-02-20 06:00:00.000 2007-02-20 13:00:00.000
Query i used
select * from tablename where ((fromdate between Convert(datetime,'2007-19-2 10:00',103) and Convert(datetime,'2007-19-2 11:00',103)) or (todate between Convert(datetime,'2007-19-2 10:00',103) and Convert(datetime,'2007-19-2 11:00',103)))
My query is not returning values when i am querying between '2007-19-2 10:00' and '2007-19-2 11:00'
Please help
Thanks
View 5 Replies
View Related
Jul 18, 2007
Hi, This is quite an obvious problem, but for some reason I can't think through the solution. I have two columns, a datetime (datecreated) and an id (FK) othertableid (id)what I would like to be able to do is select a list of id and datecreated, but only chose the latest row for each id.So I guess it's a bit like a distinct on the id column, but ensuring the date returned is the top date. Please help
View 1 Replies
View Related
Sep 4, 2007
Hi i was wanting to know how to select a record in a gridview. I have a gridview with firsname and lastname. I currently have select command on it but don't want it. I want to be able to select first name or last name and have it take me to that record on the database.
View 3 Replies
View Related
Apr 21, 2008
hello all,
i have a nested gridview. how do i add a SELECTING event for this datasource for my inner gridview? because i need to increase the commandtimeout. thnx.
private SqlDataSource ChildDataSource(string strCustno) { string strQRY = "Select ..."; SqlDataSource dsTemp = new SqlDataSource(); dsTemp.ConnectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString ; dsTemp.SelectCommand = strQRY; return dsTemp; }
View 1 Replies
View Related
Jun 15, 2008
How can I select data between Mth row and Nth row from a specific search condition? for example , I want to get the 5th to 10th employee who is born in March and ordered by their name.
View 1 Replies
View Related
Oct 21, 2004
Hello:
I have one table and it contains a column named ID Number, and a column named Date. I have a Do While statement that runs a SQL select statement a few times based on the number of records with the same ID Number. During the Do While statement the information is copied into another table and deleted from the old table. After I look at the results, I see that at the second Do While loop, the data was not selected and the Select statement did not run... so the old variable value from varValue is used again... Any reasons on why?
Here is a code snippet of what is going on:
Do While varCount < varRecordCount
conSqlConnect.Open()
cmdSelect = New SqlCommand ("Select * From temp_records_1 where [id number]=@idnumber and date<@date", conSqlConnect)
cmdSelect.Parameters.Add( "@accountnumber", "10000" )
cmdSelect.Parameters.Add( "@date", dtnow )
dtrdatareader = cmdSelect.ExecuteReader()
While dtrdatareader.Read()
If IsDbNull(dtrdatareader("value")) = false Then
varValue = dtrdatareader("value")
End If
End While
dtrdatareader.Close()
conSqlConnect.Close()
'#####The information above is copied to another table here
'#####The record where the information was received is deleted.
varCount = varCount + 1
LoopAny ideas?
View 2 Replies
View Related
Jun 2, 2005
I have a tbale with maybe 30 columns and I'm selecting all where a
record number matches a parameter I've passed. That works fine,
however is there any way to do some conversions to fields when
selecting without having to list out each column? For instance
this is what it looks like now:
SELECT *
FROM Losses
WHERE @AppRecord = AppRecord
ORDER BY Record ASC
But I want to convert say one column, is there a way to keep similar syntax instead of listing out each column?
SELECT *, CONVERT(varchar(15), [Losses]) as [Losses]
FROM Losses
WHERE @AppRecord = AppRecord
ORDER BY Record ASC
And the column I'm converting is of type 'money'. I'm converting
it to get rid of the extra zeroes at the end. If you know a
better way to do it I'd be interested in knowing.
View 4 Replies
View Related
Sep 6, 2005
CREATE PROCEDURE {databaseOwner}{objectQualifier} [PreciseData_IssueTracker_GetAllIssues]@moduleId intASSELECT PreciseData_IssueTracker_Issue.id AS 'issueId',PreciseData_IssueTracker_Status.name AS 'statusName',PreciseData_IssueTracker_Issue.subject AS 'subject',PreciseData_IssueTracker_Type.name AS 'typeName',Users.Username AS 'assignedUserName',Users.Username AS 'raiserUserName',PreciseData_IssueTracker_Issue.raiseDate AS 'raiseDate'FROMPreciseData_IssueTracker_Issue INNER JOIN PreciseData_IssueTracker_Status ON PreciseData_IssueTracker_Issue.statusId=PreciseData_IssueTracker_Status.idINNER JOIN PreciseData_IssueTracker_Type ON PreciseData_IssueTracker_Issue.typeId=PreciseData_IssueTracker_Type.idINNER JOIN Users ON Users.UserID=PreciseData_IssueTracker_Issue.assignedUserIdINNER JOIN Users ON Users.UserID=PreciseData_IssueTracker_Issue.raiserUserIdWHERE PreciseData_IssueTracker_Issue.moduleId=@moduleIdORDER BY PreciseData_IssueTracker_Issue.raiseDate DESCGOHow do i make it work?
View 1 Replies
View Related
Sep 20, 2005
Hi
I have a content management system that I am building. My CMS has a
pages table which contains 60 fields, some are varchars, some are ntext
some are dates etc. They have field names which match their purpose
like varchar1, ntext3 etc. These fields are used up depending on which
template is applied to the page.
The templatefields table holds holds the information about each field
in a template. It's control type (rich text control, date control etc)
and the field in the pages table that the data will be stored in
(varchar1,ntext3 etc)
What I want to be able to do is loop through the templatefeilds table
for a particular template and then using the field name for the pages
table, go to the pages table and get the value to populate the control.
So to clarify, I would like to produce a stored procedure which outputs
the page field name (varchar1) from the templatefields table and
varchar1's value in the pages table. Maybe in a temporary table.
Is this possible?
Any guidance would be appreciated..
Thanks in advance
View 1 Replies
View Related
Mar 23, 2006
Hi --
What is the proper way to select results with a date in the where statement when the datatype of the column is datetime?
select * from table where date_field = '3-23-2006' does not get any results.
Thank-you for your help.
View 4 Replies
View Related
Jan 7, 2002
how can I select 10 random rows from a table?
Please help
View 2 Replies
View Related
May 8, 2001
We are developing an Access application that will be used to run adhoc reports against a SQL Server table. Selection criteria is entered on a form, a passthru query is built on the fly, and the results are formatted for output via an Access report.
There are 16 different fields on the query form. Two are date fields (from / to), the rest are text. The user is required to supply a date range for the report. They may specify any combination of the remaining query fields.
Examples:
From/To dates, part number, work order number
From/To dates, location, operation, sales order number, operator
What is the best scheme to use for the indexes? One index for each potential query field? Or, a single index that includes ALL query fields? Or, some other combination?
I should also mention that there will be no realtime updates to the table. On a weekly basis, we will import updates from a mainframe extract (dropping the indexes prior to the import, rebuilding them afterwards).
View 1 Replies
View Related
Jan 25, 2002
I have a database which has a field called fldTimes. basically this field records the number of hits a file gets. How can I choose the most 5 popular files with the greatest hits. Thanks
View 2 Replies
View Related
Oct 10, 2000
Can anyone help me in finding out the database name?
For example, @@SERVERNAME gives you the name of the server
on which you SQL Server is running.
In the same way, is there any global variable which gives me the
name of the database in the context in which I am running my queries or stored procs?
Thanks
Sushruth Nanduri.
View 2 Replies
View Related
Nov 16, 2001
I have a query that returns a certain amount of results. How do I select the last row??? I know I can do the top row by doing (top 1). Basically I need the complete opposite. Please help...
I looked around and couldn't find anything on it.
thanks in advance
View 2 Replies
View Related
