Sql Update command: view

Showing posts with label view. Show all posts

Monday, March 26, 2012

Problems exporting reports to Excel

Hello all

I have a problem with an aplication that developed in .Net to view Crystal 9.0 reports, in my dev PC I can export any report to Excel without a problem, but on my client Pc's, click directly on the Export button, the app show the file location window, select Excel file on type file field and click ok, appear an error, it is in spanish but it is the translation ...

"Error in file c:\pathfile\filename.rpt:
Exportation DLL or format file invalid"

Then appear a new message box:

"Unable to export"

Checking on the web I found tow possible solutions, download the last Hot Fix from seagate site or check and replace the U2F*.dll files located in my user's PC Crystal folder. I tried with both but I'm where I started...

Any idea ?

ThanxDid you have latest service pack of CR?
Refer this for more info
http://www.businessobjects.com/support/default.asp

Problems deploying to a different server

I've been playing with Reporting Services on my computer and got it working fine. I can view the reports on my localhost with no problems. However, when I change the TargetServerURL in the report properties to the server that I want the report to go on (yes, the target server has SQL Reporting Services installed on it), I get the following error message:

Server was unable to process request. > Method ReportingService.SetExecutionOptions can not be reflected. > Unknown error - HRESULT 0x80131047

Anyone have any idea of what's going wrong here? Thanks for any help.

Just to add: I do have rights to the server I'm trying to publish to. This is assuming that SQL Reporting Services is distributing the reports under my username. It could well be that it's trying to use the generic ASPNET user that Visual Studio has a habit of using for its web publishing. Anyone know for sure?Problem solved. Silly mistake on my part. I deployed Framework 2.0 beta to that server and by default it set the folders used by Report Services to use ASP.NET 2.0 which my Visual Studio 2003 didn't like, so I set the folders to use ASP.NET 1.1 and all is good.sql

Friday, March 23, 2012

Problems creating a View

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. ThanksUSE [Maindb]
GO
CREATE VIEW [tblMain_view] (@.textbox nvarchar(30)) ??
AS SELECT dbo.tblMain.Field1, ...
FROM dbo.tblMain
WHERE dbo.tblMain.Field1 = @.textbox and ...

USE Your DB
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'Yourtable')
GO
CREATE VIEW Name
AS
SELECT col1,col2,
FROM your table
WHERE

Try the above statement with your info, your code is missing the WHERE clause before the create view. Run a search for create View in the BOL or use my email address in my profile and I will send you the VIEW tutorial I wrote a while back. Sorry cannot help you with VB I write C#. Hope this helps.

Kind regards,
Gift Peddiesql

Monday, March 12, 2012

Problematic Stored Procedures using Views with Triggers

Hi,

I would like to use the view of the company data in the following stored procedures

without needing to pass a value into the CompanyID identity column. Is there a way to do this? The following code contains the view, the insert trigger on the view, and the desired stored procedures.

Also, this code is given me problem as well the error message says it cannot convert varchar to int. This code is located in the insert trigger.

INSERT INTO State(StateName)
VALUES(@.StateName)

Here is the code of the company view, notice the C.CompanyID it is used in the GetCompany stored procedure.

SELECT C.CompanyID, C.CompanyName, A.Street, A.City, S.StateName, A.ZipCode, A.Country, P.PhoneNumber

FROM Company AS C INNER JOIN
Address AS A ON C.AddressID = A.AddressID INNER JOIN
State AS S ON A.StateID = S.StateID INNER JOIN
Phone AS P ON C.PhoneID = P.PhoneID

Here is code for the insert trigger on the view

CREATE TRIGGER InsertTriggerOnCustomerView
ON ViewOfCompany
INSTEAD OF INSERT
AS
BEGIN

DECLARE @.CompanyName VARCHAR(128)
DECLARE @.AddressID INT
DECLARE @.Street VARCHAR(256)
DECLARE @.City VARCHAR(128)
DECLARE @.StateID INT
DECLARE @.StateName VARCHAR(128)
DECLARE @.ZipCode INT
DECLARE @.Country VARCHAR(128)
DECLARE @.PhoneID INT
DECLARE @.PhoneNumber VARCHAR(32)
--DECLARE @.CompanyID INT

SELECT
--@.CompanyID = CompanyID,
@.CompanyName = CompanyName,
@.Street = Street,
@.City = City,
@.StateName = StateName,
@.ZipCode = ZipCode,
@.Country = Country,
@.PhoneNumber = PhoneNumber
FROM INSERTED

INSERT INTO State(StateName)
VALUES(@.StateName)

SET @.StateID = @.@.IDENTITY

INSERT INTO Address(Street, City, StateID, Country, ZipCode)
VALUES(@.Street, @.City, @.StateID, @.ZipCode, @.Country)

SET @.AddressID = SCOPE_IDENTITY()

INSERT INTO Phone(PhoneNumber)
VALUES(@.PhoneNumber)

SET @.PhoneID = SCOPE_IDENTITY()

INSERT INTO Company(CompanyName, AddressID, PhoneID)
VALUES(@.CompanyName, @.AddressID, @.PhoneID)

END

The stored procedures are here.

CREATE PROCEDURE GetCompany
(
@.CompanyID INT
)
AS
BEGIN
SELECT CompanyName,Street, City, StateName,
ZipCode, Country, PhoneNumber
FROM
ViewOfCompany
WHERE
CompanyID = @.CompanyID
END
GO

CREATE PROCEDURE AddCompany
(
@.CompanyName VARCHAR(128),
@.Street VARCHAR(256),
@.City VARCHAR(128),
@.StateName VARCHAR(128),
@.ZipCode VARCHAR(128),
@.Country VARCHAR(128),
@.PhoneNumber VARCHAR(32)
)
AS
BEGIN
INSERT INTO ViewOfCompany
VALUES(@.CompanyName, @.Street, @.City, @.StateName,
@.ZipCode, @.Country, @.PhoneNumber)
END
GO

Hi,

first of all you have a misdesign in your trigger. Triggers occur per statement not per row, so inserting 50 rows in a table will fire the trigger only once not 50 times. In your case the trigger is only executed once which means that it would ingnore the 49 other inserted rows. I would first fix that and perhaps come back with the error line where the error you are describing occurs, marking it with something like -- <<-- Error occurs here.

HTH; Jens Suessmeyer.

http:/www.sqlserver2005.de

|||

I am only needing to add one company at a time. In testing the AddCompany stored procedure with Query Analyzer, it appears to work with the correct parameters. For example,

-- Insert First Company
EXEC AddCompany '', '', '', ...

-- Insert Second Company
EXEC AddCompany '', '', '', ...

With actual values, the records are inserted in the tables.

But, my delimma is in the AddCompany stored procedure I don't want to have to pass in a value for the identity column and I don't want to remove it from the view because I need it for the GetCompany stored procedure. And, I don't want to have to create a seperate view for each scenerio; so, my question is there anyway to achieve the desired goal.

I have been looking at the "check" and "no check" options, but I am not sure how they function.

Saturday, February 25, 2012

Problem with Views

I have a situation with a view. This view uses 2 or 3 or 4 tables to
retrieve data, doesn't matter I have several. Someone came to me and said
"During design, I forgot a field..." in one of the tables that make up this
view. So the field was inserted into one of the tables. Now when I execute
the view, the data is off one field AFTER the added field. What I mean by
that is I have an "Code" field and then a "Date" field after the newly added
field. The data from the "Code" field now shows up in the "Date" field, and
so on.
To fix this problem, I simply open the view, and resave it, and the problem
goes away. My questions are:
1) Am I doing something wrong in creating my views?
2) Is there a way to fix all my views instead of opening and resaving all.
Thanks for any help with this
Brij
When you create a view,. SQL Server stores meta-data in syscolumns. This doesn't get updated if
change the underlying table. To refresh a view definition, you can use sp_refreshview. Most of us do
not use SELECT * in views, btw, instead we prefer to list all columns to avoid problems as table
structure changes...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Brij Singh" <brijs@.telcorinc.com> wrote in message
news:4141d5d5$0$25239$9a6e19ea@.news.newshosting.co m...
> I have a situation with a view. This view uses 2 or 3 or 4 tables to
> retrieve data, doesn't matter I have several. Someone came to me and said
> "During design, I forgot a field..." in one of the tables that make up this
> view. So the field was inserted into one of the tables. Now when I execute
> the view, the data is off one field AFTER the added field. What I mean by
> that is I have an "Code" field and then a "Date" field after the newly added
> field. The data from the "Code" field now shows up in the "Date" field, and
> so on.
> To fix this problem, I simply open the view, and resave it, and the problem
> goes away. My questions are:
> 1) Am I doing something wrong in creating my views?
> 2) Is there a way to fix all my views instead of opening and resaving all.
> Thanks for any help with this
> Brij
>

Problem with Views

I have a situation with a view. This view uses 2 or 3 or 4 tables to
retrieve data, doesn't matter I have several. Someone came to me and said
"During design, I forgot a field..." in one of the tables that make up this
view. So the field was inserted into one of the tables. Now when I execute
the view, the data is off one field AFTER the added field. What I mean by
that is I have an "Code" field and then a "Date" field after the newly added
field. The data from the "Code" field now shows up in the "Date" field, and
so on.
To fix this problem, I simply open the view, and resave it, and the problem
goes away. My questions are:
1) Am I doing something wrong in creating my views?
2) Is there a way to fix all my views instead of opening and resaving all.
Thanks for any help with this
BrijWhen you create a view,. SQL Server stores meta-data in syscolumns. This doesn't get updated if
change the underlying table. To refresh a view definition, you can use sp_refreshview. Most of us do
not use SELECT * in views, btw, instead we prefer to list all columns to avoid problems as table
structure changes...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Brij Singh" <brijs@.telcorinc.com> wrote in message
news:4141d5d5$0$25239$9a6e19ea@.news.newshosting.com...
> I have a situation with a view. This view uses 2 or 3 or 4 tables to
> retrieve data, doesn't matter I have several. Someone came to me and said
> "During design, I forgot a field..." in one of the tables that make up this
> view. So the field was inserted into one of the tables. Now when I execute
> the view, the data is off one field AFTER the added field. What I mean by
> that is I have an "Code" field and then a "Date" field after the newly added
> field. The data from the "Code" field now shows up in the "Date" field, and
> so on.
> To fix this problem, I simply open the view, and resave it, and the problem
> goes away. My questions are:
> 1) Am I doing something wrong in creating my views?
> 2) Is there a way to fix all my views instead of opening and resaving all.
> Thanks for any help with this
> Brij
>

Problem with View migrated from SQL Server 2000 into 2005

We are in the process of migrating from SQL Server 2000 to 2005. We encountered a problem with one of our web applications (ASP) when attached to the new 2005 database. We do not get this error when the application is attached to the 2000 database.

During execution of the following code:

sub OpenRS_TicketDetails(iTicketID)

strSQL="SELECT * from vwexTicketDetails WHERE TicketID =" & iTicketID

rs.open strSQL, cnReadWrite, adOpenStatic, adLockOptimistic

end sub

We encountered the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e23'

Row handle referred to a deleted row or a row marked for deletion.

The following is the select statement related to the view:

SELECT T.TicketID, T.TicketDate, T.Problem, T.Technician_Assigned, T.Closed_Date, T.PersonID, T.SiteId, T.ProgramId, T.StatusId, T.PriorityId, T.CategoryId,
CMT.Comment, RTRIM(P.LastName) + ', ' + RTRIM(P.FirstName) AS FullName, P.Phone, P.WorkLocation, CT.CategoryName AS Category,
PR.priorityDesc AS Priority, ST.statusDesc AS Status
FROM dbo.dtTickets AS T LEFT OUTER JOIN
dbo.dtComments AS CMT ON T.TicketID = CMT.TicketID LEFT OUTER JOIN
DIVCommon.dbo.dtPersonnel AS P ON T.PersonID = P.PersonID INNER JOIN
dbo.vtCategory AS CT ON T.CategoryId = CT.CategoryID INNER JOIN
dbo.vtPriority AS PR ON T.PriorityId = PR.priorityId INNER JOIN
dbo.vtStatus AS ST ON T.StatusId = ST.statusId

We tracked the problem to the dtComments table and were able to come up with a workaround to our problem. When we added a primary key to the dtComments table, the application ran fine.

CREATE TABLE [dbo].[dtComments](

[CommentId] [int] IDENTITY(1,1) NOT NULL,

[TicketID] [int] NULL,

[Comment] [varchar](8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[LastModUser] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[LastModDate] [datetime] NULL)

Can someone explain to me why we are experiencing this problem in the 2005 environment and if there is a better solution. Please let me know if you need additional information about this situation.

Thanks, Doug

I can't say why it is a problem in 2005 rather than 2000. But I can explain why the problem, its not simple and to do with some inner workings of ADO, metadata and optimistic locking

You are specified to have a optimistic locked recordset based on a view. Firstly you shouldn't do this with views because you can end up with orphaned rows if you try and insert data.

How ADO handles the optimistic locking is it needs to know how to identify the row that is being updated and also how to identify what determines the record has changed so that it can verify the update. However your dtComments table doesn't have a PK thus the problem. ADO does try and obtain the information needed in the absence of a PK but this is what is causing the problem.

Putting a PK should be your answer, all tables should have one unless a specific reason not to.

You could also try changing it to a readonly recordset.

Problem with View

Problem with view

Here is the t-sql code I used to create the view:

CREATE VIEW [vwAllSnailMail]
AS
SELECT [dbo].[_tblLeads].[FirstName] + ' ' +
[dbo].[_tblLeads].[LastName] as Fullname, [dbo].[_tblLeads].[Address 1]
as Address1, [dbo].[_tblLeads].[Address 2] as Address2,
[dbo].[_tblLeads].[City] as City, [dbo].[_tblLeads].[State] as ST,
[dbo].[_tblLeads].[Zip] as Zip
FROM [dbo].[_tblLeads]
WHERE [dbo].[_tblLeads].[Address 1] IS NOT NULL AND
[dbo].[_tblLeads].[City] IS NOT NULL AND [dbo].[_tblLeads].[State] IS
NOT NULL AND [dbo].[_tblLeads].[Zip] IS NOT NULL
UNION
SELECT [dbo].[tblClients].[FName] + ' ' + [dbo].[tblClients].[LName] as
Fullname, [dbo].[tblClients].[Street1] as Address1,
[dbo].[tblClients].[Street2] as Address2, [dbo].[tblClients].[City] as
City, [dbo].[tblClients].[State_cd] as ST, [dbo].[tblClients].[Zip] as
Zip
FROM [dbo].[tblClients]
WHERE [dbo].[tblClients].[Street1]<>'' AND [dbo].[tblClients].[City]
<>'' AND [dbo].[tblClients].[State_cd] <>'0' AND
[dbo].[tblClients].[Zip] <>''

Note - When I separate the select statements and create two separate
views, both execute correctly AND I can view the results for each
individual view. I just can't get the above to work. What am I
missing?When you have UNION the columns in the first SELECT statement define the
datatype of the columns. The columns in the following SELECT statements must
match the datatype or be implicitly convertible. In your case implicit
conversion from varchar to float is allowed. Then the problem is that a
column in your first SELECT statement is of type float and the same column
in the second SELECT is a varchar, but the varchar column has some
characters that cannot be converted to float.

Without seeing the table definitions and just guessing it seems that your
_tblLeads.Zip might be of type float and tblClients.Zip is of type varchar.
Most likely tblClients.Zip has some invalid characters that cannot be
converted to float. One solution is to replace the column
[dbo].[_tblLeads].[Zip] in your first SELECT (if that is the correct column)
with an explicit conversion to varchar, like CAST([dbo].[_tblLeads].[Zip] AS
varchar). To be more precise you can convert to varchar(n) where n is the
exact size of the column.

Regards,

Plamen Ratchev
http://www.SQLStudio.com|||Hi
What are the field format at each table?
Harry

On Jan 23, 1:34 am, "Chris" <cjscu...@.gmail.comwrote:

Quote:

Originally Posted by

I am trying to create a view that creates a mailing list from two
different tables. The t-sql code executes successfully but when I try
to open the view I get this error message [Microsoft][ODBC SQL Server
Driver][SQL Server]Error converting data type vchar to float.
>
Here is the t-sql code I used to create the view:
>
CREATE VIEW [vwAllSnailMail]
AS
SELECT [dbo].[_tblLeads].[FirstName] + ' ' +
[dbo].[_tblLeads].[LastName] as Fullname, [dbo].[_tblLeads].[Address 1]
as Address1, [dbo].[_tblLeads].[Address 2] as Address2,
[dbo].[_tblLeads].[City] as City, [dbo].[_tblLeads].[State] as ST,
[dbo].[_tblLeads].[Zip] as Zip
FROM [dbo].[_tblLeads]
WHERE [dbo].[_tblLeads].[Address 1] IS NOT NULL AND
[dbo].[_tblLeads].[City] IS NOT NULL AND [dbo].[_tblLeads].[State] IS
NOT NULL AND [dbo].[_tblLeads].[Zip] IS NOT NULL
UNION
SELECT [dbo].[tblClients].[FName] + ' ' + [dbo].[tblClients].[LName] as
Fullname, [dbo].[tblClients].[Street1] as Address1,
[dbo].[tblClients].[Street2] as Address2, [dbo].[tblClients].[City] as
City, [dbo].[tblClients].[State_cd] as ST, [dbo].[tblClients].[Zip] as
Zip
FROM [dbo].[tblClients]
WHERE [dbo].[tblClients].[Street1]<>'' AND [dbo].[tblClients].[City]
<>'' AND [dbo].[tblClients].[State_cd] <>'0' AND
[dbo].[tblClients].[Zip] <>''
>
Note - When I separate the select statements and create two separate
views, both execute correctly AND I can view the results for each
individual view. I just can't get the above to work. What am I
missing?

Problem with View

Hi,
Today I encountered with a view that references other views and each of them
reference some other view. The problem began when I started querying this
view.
When I used condition(WHERE), the view produced about 67,000 rows. When I
excluded the condition, I expected to have more rows but the result was
about 50,000 rows. I tried different ways but the condition produced more
rows!
Whereas the views were nested, it was very difficult to trace the problem.
It may help if I say some views used UNION ALL to concatenate some result.
I must find the source of problem. Does anybody have any idea/experience
like that?
Any help would be greatly appreciated.
LeilaI've had problems with nested views where the server has taken hours to get a
query plan (but that developer nested to quite a few levels) and heard of the
sort of thing you are seeing.
have you rebooted the server and updated statistics?
Apart from that I would suggest getting rid of the views and putting the
logic in stored procedures. You'll probably find it faster and certainly
easier to maintain and troubleshoot.
"Leila" wrote:
> Hi,
> Today I encountered with a view that references other views and each of them
> reference some other view. The problem began when I started querying this
> view.
> When I used condition(WHERE), the view produced about 67,000 rows. When I
> excluded the condition, I expected to have more rows but the result was
> about 50,000 rows. I tried different ways but the condition produced more
> rows!
> Whereas the views were nested, it was very difficult to trace the problem.
> It may help if I say some views used UNION ALL to concatenate some result.
> I must find the source of problem. Does anybody have any idea/experience
> like that?
> Any help would be greatly appreciated.
> Leila
>
>|||In addition to Nigel's commends: Are you using any old-style outer joins in any of the view
definitions? (*=)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Leila" <leilas@.hotpop.com> wrote in message news:%23w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Today I encountered with a view that references other views and each of them reference some other
> view. The problem began when I started querying this view.
> When I used condition(WHERE), the view produced about 67,000 rows. When I excluded the condition,
> I expected to have more rows but the result was about 50,000 rows. I tried different ways but the
> condition produced more rows!
> Whereas the views were nested, it was very difficult to trace the problem.
> It may help if I say some views used UNION ALL to concatenate some result.
> I must find the source of problem. Does anybody have any idea/experience like that?
> Any help would be greatly appreciated.
> Leila
>|||Thanks Tibor,
I'm not sure if I have undertood your meaning.
Do you mean old style joins like:
select t1.*,t2.* from t1,t2 where t1.id=t2.id
?
Leila
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:#HRwpeM8EHA.1404@.TK2MSFTNGP11.phx.gbl...
> In addition to Nigel's commends: Are you using any old-style outer joins
in any of the view
> definitions? (*=)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Leila" <leilas@.hotpop.com> wrote in message
news:%23w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> > Today I encountered with a view that references other views and each of
them reference some other
> > view. The problem began when I started querying this view.
> > When I used condition(WHERE), the view produced about 67,000 rows. When
I excluded the condition,
> > I expected to have more rows but the result was about 50,000 rows. I
tried different ways but the
> > condition produced more rows!
> > Whereas the views were nested, it was very difficult to trace the
problem.
> > It may help if I say some views used UNION ALL to concatenate some
result.
> > I must find the source of problem. Does anybody have any idea/experience
like that?
> > Any help would be greatly appreciated.
> > Leila
> >
>|||Thanks Nigel,
Actually I cannot blame that programmer because of the view! Their logic is
really complicated and if I try to use SP, I must write a query with lots of
sub queries and derived tables.
As far as I know, old statistics may cause SQL Server to choose
inappropriate index. Therefore it hinders the performance but logically must
not produce wrong result.
Consider this query:
SELECT * FROM ThatView
This produces the list of customers with their IDs. I saw the ID 100, but I
tried the query below and did not get any result:
SELECT * FROM ThatView WHERE CustomerID=100
I don't know why the first query showed the CustomerID with ID of 100 but
the second one did not!
What is the affect of reboot on this problem?
Leila
"Nigel Rivett" <NigelRivett@.discussions.microsoft.com> wrote in message
news:F289F3DB-E431-493E-9AD4-61A730A9A2CF@.microsoft.com...
> I've had problems with nested views where the server has taken hours to
get a
> query plan (but that developer nested to quite a few levels) and heard of
the
> sort of thing you are seeing.
> have you rebooted the server and updated statistics?
> Apart from that I would suggest getting rid of the views and putting the
> logic in stored procedures. You'll probably find it faster and certainly
> easier to maintain and troubleshoot.
> "Leila" wrote:
> > Hi,
> > Today I encountered with a view that references other views and each of
them
> > reference some other view. The problem began when I started querying
this
> > view.
> > When I used condition(WHERE), the view produced about 67,000 rows. When
I
> > excluded the condition, I expected to have more rows but the result was
> > about 50,000 rows. I tried different ways but the condition produced
more
> > rows!
> > Whereas the views were nested, it was very difficult to trace the
problem.
> > It may help if I say some views used UNION ALL to concatenate some
result.
> > I must find the source of problem. Does anybody have any idea/experience
> > like that?
> > Any help would be greatly appreciated.
> > Leila
> >
> >
> >|||Leila,
I'm referring to old style *outer* joins, such as:
SELECT t1.col1, t2.colZ
FROM t1, t2
WHERE t1.c1 *= t2.c1
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Leila" <Leilas@.hotpop.com> wrote in message news:eib86hO8EHA.3756@.TK2MSFTNGP14.phx.gbl...
> Thanks Tibor,
> I'm not sure if I have undertood your meaning.
> Do you mean old style joins like:
> select t1.*,t2.* from t1,t2 where t1.id=t2.id
> ?
> Leila
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:#HRwpeM8EHA.1404@.TK2MSFTNGP11.phx.gbl...
>> In addition to Nigel's commends: Are you using any old-style outer joins
> in any of the view
>> definitions? (*=)
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "Leila" <leilas@.hotpop.com> wrote in message
> news:%23w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
>> > Hi,
>> > Today I encountered with a view that references other views and each of
> them reference some other
>> > view. The problem began when I started querying this view.
>> > When I used condition(WHERE), the view produced about 67,000 rows. When
> I excluded the condition,
>> > I expected to have more rows but the result was about 50,000 rows. I
> tried different ways but the
>> > condition produced more rows!
>> > Whereas the views were nested, it was very difficult to trace the
> problem.
>> > It may help if I say some views used UNION ALL to concatenate some
> result.
>> > I must find the source of problem. Does anybody have any idea/experience
> like that?
>> > Any help would be greatly appreciated.
>> > Leila
>> >
>>
>|||No, but there're ansi style outer joins.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uqA6jYP8EHA.3124@.TK2MSFTNGP11.phx.gbl...
> Leila,
> I'm referring to old style *outer* joins, such as:
> SELECT t1.col1, t2.colZ
> FROM t1, t2
> WHERE t1.c1 *= t2.c1
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Leila" <Leilas@.hotpop.com> wrote in message
news:eib86hO8EHA.3756@.TK2MSFTNGP14.phx.gbl...
> > Thanks Tibor,
> > I'm not sure if I have undertood your meaning.
> > Do you mean old style joins like:
> > select t1.*,t2.* from t1,t2 where t1.id=t2.id
> > ?
> >
> > Leila
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:#HRwpeM8EHA.1404@.TK2MSFTNGP11.phx.gbl...
> >> In addition to Nigel's commends: Are you using any old-style outer
joins
> > in any of the view
> >> definitions? (*=)
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> http://www.sqlug.se/
> >>
> >>
> >> "Leila" <leilas@.hotpop.com> wrote in message
> > news:%23w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
> >> > Hi,
> >> > Today I encountered with a view that references other views and each
of
> > them reference some other
> >> > view. The problem began when I started querying this view.
> >> > When I used condition(WHERE), the view produced about 67,000 rows.
When
> > I excluded the condition,
> >> > I expected to have more rows but the result was about 50,000 rows. I
> > tried different ways but the
> >> > condition produced more rows!
> >> > Whereas the views were nested, it was very difficult to trace the
> > problem.
> >> > It may help if I say some views used UNION ALL to concatenate some
> > result.
> >> > I must find the source of problem. Does anybody have any
idea/experience
> > like that?
> >> > Any help would be greatly appreciated.
> >> > Leila
> >> >
> >>
> >>
> >
> >
>|||Leila,
can you post a concise DDL statements to replicate the issue, if possible..
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"Leila" <Leilas@.hotpop.com> wrote in message
news:#u0J0dP8EHA.2572@.tk2msftngp13.phx.gbl...
> No, but there're ansi style outer joins.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:uqA6jYP8EHA.3124@.TK2MSFTNGP11.phx.gbl...
> > Leila,
> >
> > I'm referring to old style *outer* joins, such as:
> >
> > SELECT t1.col1, t2.colZ
> > FROM t1, t2
> > WHERE t1.c1 *= t2.c1
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> > http://www.sqlug.se/
> >
> >
> > "Leila" <Leilas@.hotpop.com> wrote in message
> news:eib86hO8EHA.3756@.TK2MSFTNGP14.phx.gbl...
> > > Thanks Tibor,
> > > I'm not sure if I have undertood your meaning.
> > > Do you mean old style joins like:
> > > select t1.*,t2.* from t1,t2 where t1.id=t2.id
> > > ?
> > >
> > > Leila
> > >
> > >
> > > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote
> in
> > > message news:#HRwpeM8EHA.1404@.TK2MSFTNGP11.phx.gbl...
> > >> In addition to Nigel's commends: Are you using any old-style outer
> joins
> > > in any of the view
> > >> definitions? (*=)
> > >>
> > >> --
> > >> Tibor Karaszi, SQL Server MVP
> > >> http://www.karaszi.com/sqlserver/default.asp
> > >> http://www.solidqualitylearning.com/
> > >> http://www.sqlug.se/
> > >>
> > >>
> > >> "Leila" <leilas@.hotpop.com> wrote in message
> > > news:%23w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
> > >> > Hi,
> > >> > Today I encountered with a view that references other views and
each
> of
> > > them reference some other
> > >> > view. The problem began when I started querying this view.
> > >> > When I used condition(WHERE), the view produced about 67,000 rows.
> When
> > > I excluded the condition,
> > >> > I expected to have more rows but the result was about 50,000 rows.
I
> > > tried different ways but the
> > >> > condition produced more rows!
> > >> > Whereas the views were nested, it was very difficult to trace the
> > > problem.
> > >> > It may help if I say some views used UNION ALL to concatenate some
> > > result.
> > >> > I must find the source of problem. Does anybody have any
> idea/experience
> > > like that?
> > >> > Any help would be greatly appreciated.
> > >> > Leila
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >
>|||Sounds like someone coded that ID to be VARCHAR instead of INT. Chances are
WHERE CustomerID = 100 is NULL. Try it with WHERE CustomerID LIKE '100%'
Even though I'd like to, I will refrain from beating up on you for using
nested VIEWS; hwoever, if you must nest, then you might consider
materializing the lower level ones. SQL Server calls these INDEXED VIEWS
but the effect is the same.
Sincerely,
Anthony Thomas
"Leila" <Leilas@.hotpop.com> wrote in message
news:ORQCBiO8EHA.3756@.TK2MSFTNGP14.phx.gbl...
Thanks Nigel,
Actually I cannot blame that programmer because of the view! Their logic is
really complicated and if I try to use SP, I must write a query with lots of
sub queries and derived tables.
As far as I know, old statistics may cause SQL Server to choose
inappropriate index. Therefore it hinders the performance but logically must
not produce wrong result.
Consider this query:
SELECT * FROM ThatView
This produces the list of customers with their IDs. I saw the ID 100, but I
tried the query below and did not get any result:
SELECT * FROM ThatView WHERE CustomerID=100
I don't know why the first query showed the CustomerID with ID of 100 but
the second one did not!
What is the affect of reboot on this problem?
Leila
"Nigel Rivett" <NigelRivett@.discussions.microsoft.com> wrote in message
news:F289F3DB-E431-493E-9AD4-61A730A9A2CF@.microsoft.com...
> I've had problems with nested views where the server has taken hours to
get a
> query plan (but that developer nested to quite a few levels) and heard of
the
> sort of thing you are seeing.
> have you rebooted the server and updated statistics?
> Apart from that I would suggest getting rid of the views and putting the
> logic in stored procedures. You'll probably find it faster and certainly
> easier to maintain and troubleshoot.
> "Leila" wrote:
> > Hi,
> > Today I encountered with a view that references other views and each of
them
> > reference some other view. The problem began when I started querying
this
> > view.
> > When I used condition(WHERE), the view produced about 67,000 rows. When
I
> > excluded the condition, I expected to have more rows but the result was
> > about 50,000 rows. I tried different ways but the condition produced
more
> > rows!
> > Whereas the views were nested, it was very difficult to trace the
problem.
> > It may help if I say some views used UNION ALL to concatenate some
result.
> > I must find the source of problem. Does anybody have any idea/experience
> > like that?
> > Any help would be greatly appreciated.
> > Leila
> >
> >
> >|||I did see couple of issues on my server, where in I do a select * with and
without a where clause. The result was different. But, I did this on a
table. The problem was found to be index corruption. You might want to
check into that.
-Nags
"Leila" <leilas@.hotpop.com> wrote in message
news:#w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Today I encountered with a view that references other views and each of
them
> reference some other view. The problem began when I started querying this
> view.
> When I used condition(WHERE), the view produced about 67,000 rows. When I
> excluded the condition, I expected to have more rows but the result was
> about 50,000 rows. I tried different ways but the condition produced more
> rows!
> Whereas the views were nested, it was very difficult to trace the problem.
> It may help if I say some views used UNION ALL to concatenate some result.
> I must find the source of problem. Does anybody have any idea/experience
> like that?
> Any help would be greatly appreciated.
> Leila
>

Monday, March 26, 2012

Problems exporting reports to Excel

Problems deploying to a different server

Friday, March 23, 2012

Problems creating a View

Monday, March 12, 2012

Problematic Stored Procedures using Views with Triggers

Saturday, February 25, 2012

Problem with Views

Problem with Views

Problem with View migrated from SQL Server 2000 into 2005

We are in the process of migrating from SQL Server 2000 to 2005. We encountered a problem with one of our web applications (ASP) when attached to the new 2005 database. We do not get this error when the application is attached to the 2000 database.

During execution of the following code:

We encountered the following error:

The following is the select statement related to the view:

We tracked the problem to the dtComments table and were able to come up with a workaround to our problem. When we added a primary key to the dtComments table, the application ran fine.

Can someone explain to me why we are experiencing this problem in the 2005 environment and if there is a better solution. Please let me know if you need additional information about this situation.

Problem with View

Problem with view

Problem with View

Problem with View

Sql Update command

Blog Archive

About Me