We have one web server, which is running on Win2K server,
MS-SQL and Sharepoint. Last week it went down and i found
this error message "The list or view of a list that is
referenced here no longer exists".
Kindly help us.
"The list or view of a list that is referenced here no
longer exists".Found this error message where? Your application? SQL Server logs?
Windows Event Viewer? Something in sharepoint?
I've never seen that message before, but if you can give us better context
(where the error came from, I'm suspecting SharePoint), as well as anything
abnormal that might have been happening on the box around the same time as
the error message (e.g. backup, restore, DTS, log shipping, long-running
job, high volume).
And also the symptom of the problem -- is the problem that there is an error
message, or is something else going wrong with the application that you are
attributing to the error message?
Is it possible that a table or view in Sharepoint became corrupt? Does
Sharepoint have any repair or self-healing tasks you can run? Not being
familiar with Sharepoint, I recommend that you pursue this in a Sharepoint
newsgroup (microsoft.public.sharepoint.teamservices) where you will be much
more likely to find Sharepoint experts with direct experience with this
problem, or at least a better knowledge base to help you out.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Srichandan T." <srichandan.tirumalai@.wipro.com> wrote in message
news:9f1601c43454$cc4b4a40$a501280a@.phx.gbl...
> We have one web server, which is running on Win2K server,
> MS-SQL and Sharepoint. Last week it went down and i found
> this error message "The list or view of a list that is
> referenced here no longer exists".
> Kindly help us.
>
> "The list or view of a list that is referenced here no
> longer exists".
Saturday, February 25, 2012
Problem with Website and SQL Data base synchronization
Problem with Website and SQL synchronization
We have one web server, which is running on Win2K server,
MS-SQL and Sharepoint. Last week it went down and i found
this error message "The list or view of a list that is
referenced here no longer exists".
Kindly help us.
"The list or view of a list that is referenced here no
longer exists".
I'm almost certain that error message was not generated by SQL Server. Where does the error appear? Try using SQL Profiler to see whether the sql box does actually generate an error.
Alicia
saw this http://www.sqlporn.co.uk - made me laugh anyway!
We have one web server, which is running on Win2K server,
MS-SQL and Sharepoint. Last week it went down and i found
this error message "The list or view of a list that is
referenced here no longer exists".
Kindly help us.
"The list or view of a list that is referenced here no
longer exists".
I'm almost certain that error message was not generated by SQL Server. Where does the error appear? Try using SQL Profiler to see whether the sql box does actually generate an error.
Alicia
saw this http://www.sqlporn.co.uk - made me laugh anyway!
Problem with Website and SQL Data base synchronization
Problem with Website and SQL synchronization
We have one web server, which is running on Win2K server,
MS-SQL and Sharepoint. Last week it went down and i found
this error message "The list or view of a list that is
referenced here no longer exists".
Kindly help us.
"The list or view of a list that is referenced here no
longer exists".I'm almost certain that error message was not generated by SQL Server. Where
does the error appear? Try using SQL Profiler to see whether the sql box do
es actually generate an error.
Alicia
saw this http://www.sqlporn.co.uk - made me laugh anyway!
We have one web server, which is running on Win2K server,
MS-SQL and Sharepoint. Last week it went down and i found
this error message "The list or view of a list that is
referenced here no longer exists".
Kindly help us.
"The list or view of a list that is referenced here no
longer exists".I'm almost certain that error message was not generated by SQL Server. Where
does the error appear? Try using SQL Profiler to see whether the sql box do
es actually generate an error.
Alicia
saw this http://www.sqlporn.co.uk - made me laugh anyway!
Problem with website and db
I am new to .NET and the Visual Web Developer Express and what not...I have created a website on my machine which works wonderfully...however, and I'm sure this is somewhere but I can't find it, I cannot get it to work on my host server...the Default page comes up, but when I try to sign in it says:
"An attempt to attach an auto-named database for file c:\hosting\webhost4life\member\harrison0801\App_Data\aspnetdb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."
The host support tells me to change the db name from aspnetdb to something else...I do this and when I run the site again or try to log in, I get the exact same error message...then they go on to tell me that I have to remove the LocalSqlServer in the web.config file which now looks like this:
<connectionStrings>
<remove name="LocalSqlServer"/>
<add name="LocalSqlServer" connectionString="Data Source=.\SQLEXPRESS;Integrated Security=True;AttachDbFilename=|DataDirectory|aspnetdb.mdf;User instance=true;" providerName="System.Data.SqlClient" />
<add name="Example" connectionString="Data Source=.\SQLEXPRESS;Integrated Security=True;AttachDbFilename=|DataDirectory|example.mdf;User instance=true;Initial Catalog=example;" providerName="System.Data.SqlClient" />
</connectionStrings>
Of course this isn't working either...otherwise I would off playing on my server, and not posting here. Also, when I set up all the roles, users, and what not on my machine...does this carry over to the host machine?
Desperately needing massive help here...
Not a DBA and no expert in .NET
Thank you very much in advance,
Darren
Rename the .mdf file from aspnetdb.mdf to harrison0801.mdf and change your connection string to
<add name="LocalSqlServer" connectionString="Data Source=.\SQLEXPRESS;Integrated Security=True;AttachDbFilename=|DataDirectory|harrison0801.mdf;User instance=true;" providerName="System.Data.SqlClient" />
|||Okay...renamed the ASPNETDB.MDF and changed the config connection string to the above...hit CTRL F5 to run it...and guess what magically appeared? ASPNETDB without regard to the one I just changed the name on.
Also, when I run the ASP NET Configuration utililty in the VWD...walks me through all the roles, users, etc...it loads all that info into the ASPNETDB...is there something in the default config for the DB creator that can be changed? Perhaps this is something that the ASPNET creators can work out...maybe like allow the developers to name there own DB in the process of creating websites? Would be nice...less of a headache.
Problem with way to access all recordsets returned by sp_spaceused using ODBC APIs on C++
Hi all,
I have a problem executing a sp_spaceused on a database.
Please execute sp_spaceused on your local database so that further
discussion will be more easier to understand.
The code snippet is as follows :
SQLCHAR database_size[15];
SQLCHAR unused[15];
The database_size is member of first record set and
unused is member of second recordset returned by the sp_spaceused.
SQLINTEGER rlength;
retcode = SQLAllocHandle(SQL_HANDLE_STMT,hDBC,&hStmt);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
{
retcode = SQLExecDirect(hStmt,(unsigned char *)sqlCommand,
SQL_NTS);
retcode = SQLBindCol(hStmt,2, SQL_CHAR, (SQLPOINTER) &database_size,
sizeof(database_size),&rlength);
//Problem - was not able to bind any
column of the second recordset so not able to fetch the unused space.
if (retcode != SQL_SUCCESS )
{
AfxMessageBox("Error occured while binding");
}
CString csDatabase_size;
//The while loop below returns after the first iteration as only first
recordset is available and nothing is accessible of second recordset so
cant get 'unused' field
while ((retcode = SQLFetch(hStmt)) == SQL_SUCCESS)
{
stored the return
values of database_size and unused.
}
SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
}
else
{
AfxMessageBox("error while allocating a statement handle");
}
So the Problem is :
Is there any way to access all recordsets returned by sp_spaceused
using ODBC APIs on C++?
Please reply.
Thanks in advance
-Ashutosh
exec sp_spaceused returns:
Result set 1:
database_name
database_size
unallocated space
Result set 2:
reserved
data
index_size
unused
So, from your description you are trying to get database_size (col2) from
first result set, and unused (col4) from second result set.
You have to:
SQLExecDirect()
SQLBindCol( database_name, col2 )
SQLFetch() until all records read from first result set
SQLMoreResults() if returns SQL_SUCESS then you have another result set, so
...
SQLFreeStmt( SQL_UNBIND )
SQLBindCol( unused, col4 )
SQLFetch() until all records read from second result set
cmk
|||Thanx alot for your help!
Chris Kushnir wrote:
> exec sp_spaceused returns:
> Result set 1:
> database_name
> database_size
> unallocated space
> Result set 2:
> reserved
> data
> index_size
> unused
> So, from your description you are trying to get database_size (col2) from
> first result set, and unused (col4) from second result set.
> You have to:
> SQLExecDirect()
> SQLBindCol( database_name, col2 )
> SQLFetch() until all records read from first result set
> SQLMoreResults() if returns SQL_SUCESS then you have another result set, so
> ...
> SQLFreeStmt( SQL_UNBIND )
> SQLBindCol( unused, col4 )
> SQLFetch() until all records read from second result set
>
> cmk
I have a problem executing a sp_spaceused on a database.
Please execute sp_spaceused on your local database so that further
discussion will be more easier to understand.
The code snippet is as follows :
SQLCHAR database_size[15];
SQLCHAR unused[15];
The database_size is member of first record set and
unused is member of second recordset returned by the sp_spaceused.
SQLINTEGER rlength;
retcode = SQLAllocHandle(SQL_HANDLE_STMT,hDBC,&hStmt);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
{
retcode = SQLExecDirect(hStmt,(unsigned char *)sqlCommand,
SQL_NTS);
retcode = SQLBindCol(hStmt,2, SQL_CHAR, (SQLPOINTER) &database_size,
sizeof(database_size),&rlength);
//Problem - was not able to bind any
column of the second recordset so not able to fetch the unused space.
if (retcode != SQL_SUCCESS )
{
AfxMessageBox("Error occured while binding");
}
CString csDatabase_size;
//The while loop below returns after the first iteration as only first
recordset is available and nothing is accessible of second recordset so
cant get 'unused' field
while ((retcode = SQLFetch(hStmt)) == SQL_SUCCESS)
{
stored the return
values of database_size and unused.
}
SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
}
else
{
AfxMessageBox("error while allocating a statement handle");
}
So the Problem is :
Is there any way to access all recordsets returned by sp_spaceused
using ODBC APIs on C++?
Please reply.
Thanks in advance
-Ashutosh
exec sp_spaceused returns:
Result set 1:
database_name
database_size
unallocated space
Result set 2:
reserved
data
index_size
unused
So, from your description you are trying to get database_size (col2) from
first result set, and unused (col4) from second result set.
You have to:
SQLExecDirect()
SQLBindCol( database_name, col2 )
SQLFetch() until all records read from first result set
SQLMoreResults() if returns SQL_SUCESS then you have another result set, so
...
SQLFreeStmt( SQL_UNBIND )
SQLBindCol( unused, col4 )
SQLFetch() until all records read from second result set
cmk
|||Thanx alot for your help
!Chris Kushnir wrote:
> exec sp_spaceused returns:
> Result set 1:
> database_name
> database_size
> unallocated space
> Result set 2:
> reserved
> data
> index_size
> unused
> So, from your description you are trying to get database_size (col2) from
> first result set, and unused (col4) from second result set.
> You have to:
> SQLExecDirect()
> SQLBindCol( database_name, col2 )
> SQLFetch() until all records read from first result set
> SQLMoreResults() if returns SQL_SUCESS then you have another result set, so
> ...
> SQLFreeStmt( SQL_UNBIND )
> SQLBindCol( unused, col4 )
> SQLFetch() until all records read from second result set
>
> cmk
Problem with VS.Net when using SQL Reporting Services
I'm experiencing an odd problem with Visual Studio 1.1 when
using/developing reports, etc with the SQL Reporting Services.
After I build/deploy SRS reports to my Reporting Service Visual Studio
seems to lose its ability to display intellisense and format
new/modified VB.NET code per the original tools/option settings. I
have to shut-down Visual Studio (not just the session) and start it up
again if I want to have the editting options and intellisense work (at
all). I've looked at the tools/options settings and everything appears
to be as it should be (VS simply is ignoring the edit/intellisense)
So... I'm wondering if anyone else has experienced this and/or is
there something that I can do about keeping the settings?
TIA
GlennGlenn,
I was suffer from same problem but workaround like following:
1)Remove your MS Report project from the solution1. Leave web/business logic
etc project as they are.
2)Create solution2 and put the MS Report project in. Open one more instance
of VStudio so that you can access all projects same time. (I like the
multi-instance feature of VStudio)
Anyway, I hope someone in MS can look into the issue.
Cheers.
Joe
"Glenn Owens" wrote:
> I'm experiencing an odd problem with Visual Studio 1.1 when
> using/developing reports, etc with the SQL Reporting Services.
> After I build/deploy SRS reports to my Reporting Service Visual Studio
> seems to lose its ability to display intellisense and format
> new/modified VB.NET code per the original tools/option settings. I
> have to shut-down Visual Studio (not just the session) and start it up
> again if I want to have the editting options and intellisense work (at
> all). I've looked at the tools/options settings and everything appears
> to be as it should be (VS simply is ignoring the edit/intellisense)
> So... I'm wondering if anyone else has experienced this and/or is
> there something that I can do about keeping the settings?
> TIA
> Glenn
>|||Joe, thanks for the suggestion. It confirmed my suspicion that I
needed to separate the logic projects from the report projects.
Once done everything is working again!
There are definitely some gottchas in the SRS 1.0.
Regards,
Glenn
Joe <Joe@.discussions.microsoft.com> wrote in message news:<9CFA1EEE-51A3-4A1E-9ECD-DA33664ABC0E@.microsoft.com>...
> Glenn,
> I was suffer from same problem but workaround like following:
> 1)Remove your MS Report project from the solution1. Leave web/business logic
> etc project as they are.
> 2)Create solution2 and put the MS Report project in. Open one more instance
> of VStudio so that you can access all projects same time. (I like the
> multi-instance feature of VStudio)
> Anyway, I hope someone in MS can look into the issue.
> Cheers.
> Joe
> "Glenn Owens" wrote:
> > I'm experiencing an odd problem with Visual Studio 1.1 when
> > using/developing reports, etc with the SQL Reporting Services.
> >
> > After I build/deploy SRS reports to my Reporting Service Visual Studio
> > seems to lose its ability to display intellisense and format
> > new/modified VB.NET code per the original tools/option settings. I
> > have to shut-down Visual Studio (not just the session) and start it up
> > again if I want to have the editting options and intellisense work (at
> > all). I've looked at the tools/options settings and everything appears
> > to be as it should be (VS simply is ignoring the edit/intellisense)
> >
> > So... I'm wondering if anyone else has experienced this and/or is
> > there something that I can do about keeping the settings?
> >
> > TIA
> > Glenn
> >
using/developing reports, etc with the SQL Reporting Services.
After I build/deploy SRS reports to my Reporting Service Visual Studio
seems to lose its ability to display intellisense and format
new/modified VB.NET code per the original tools/option settings. I
have to shut-down Visual Studio (not just the session) and start it up
again if I want to have the editting options and intellisense work (at
all). I've looked at the tools/options settings and everything appears
to be as it should be (VS simply is ignoring the edit/intellisense)
So... I'm wondering if anyone else has experienced this and/or is
there something that I can do about keeping the settings?
TIA
GlennGlenn,
I was suffer from same problem but workaround like following:
1)Remove your MS Report project from the solution1. Leave web/business logic
etc project as they are.
2)Create solution2 and put the MS Report project in. Open one more instance
of VStudio so that you can access all projects same time. (I like the
multi-instance feature of VStudio)
Anyway, I hope someone in MS can look into the issue.
Cheers.
Joe
"Glenn Owens" wrote:
> I'm experiencing an odd problem with Visual Studio 1.1 when
> using/developing reports, etc with the SQL Reporting Services.
> After I build/deploy SRS reports to my Reporting Service Visual Studio
> seems to lose its ability to display intellisense and format
> new/modified VB.NET code per the original tools/option settings. I
> have to shut-down Visual Studio (not just the session) and start it up
> again if I want to have the editting options and intellisense work (at
> all). I've looked at the tools/options settings and everything appears
> to be as it should be (VS simply is ignoring the edit/intellisense)
> So... I'm wondering if anyone else has experienced this and/or is
> there something that I can do about keeping the settings?
> TIA
> Glenn
>|||Joe, thanks for the suggestion. It confirmed my suspicion that I
needed to separate the logic projects from the report projects.
Once done everything is working again!
There are definitely some gottchas in the SRS 1.0.
Regards,
Glenn
Joe <Joe@.discussions.microsoft.com> wrote in message news:<9CFA1EEE-51A3-4A1E-9ECD-DA33664ABC0E@.microsoft.com>...
> Glenn,
> I was suffer from same problem but workaround like following:
> 1)Remove your MS Report project from the solution1. Leave web/business logic
> etc project as they are.
> 2)Create solution2 and put the MS Report project in. Open one more instance
> of VStudio so that you can access all projects same time. (I like the
> multi-instance feature of VStudio)
> Anyway, I hope someone in MS can look into the issue.
> Cheers.
> Joe
> "Glenn Owens" wrote:
> > I'm experiencing an odd problem with Visual Studio 1.1 when
> > using/developing reports, etc with the SQL Reporting Services.
> >
> > After I build/deploy SRS reports to my Reporting Service Visual Studio
> > seems to lose its ability to display intellisense and format
> > new/modified VB.NET code per the original tools/option settings. I
> > have to shut-down Visual Studio (not just the session) and start it up
> > again if I want to have the editting options and intellisense work (at
> > all). I've looked at the tools/options settings and everything appears
> > to be as it should be (VS simply is ignoring the edit/intellisense)
> >
> > So... I'm wondering if anyone else has experienced this and/or is
> > there something that I can do about keeping the settings?
> >
> > TIA
> > Glenn
> >
Problem with VPN connection on SQL server
> Provider=sqloledb;driver={SQL Server};Network=DBMSSOCN;uid=xxx;seagreen">
> server=evnapp;database=xxx and so on the local engine is connected ?
I think you mess ODBC and OLEDB connection strings here...
I think it must be
Provider=sqloledb;Data Source=123.123.123.123,1433;Network
Library=DBMSSOCN;Initial Catalog=yourdb;User ID=dbuser;Password=dbpass
Bojidar AlexandrovNo.
This connections string works fine. I do have it working with other
customers.
By the way it also works in the LAN enviroment.....
"Bojidar Alexandrov" <bojo@.kodar.net> schreef in bericht
news:uiFAzXFNEHA.2996@.TK2MSFTNGP12.phx.gbl...
> I think you mess ODBC and OLEDB connection strings here...
> I think it must be
> Provider=sqloledb;Data Source=123.123.123.123,1433;Network
> Library=DBMSSOCN;Initial Catalog=yourdb;User ID=dbuser;Password=dbpass
>
> Bojidar Alexandrov
>|||I do not say that it does not work, but that it is wrong. Please see
somewhere conenction string reference
> server=evnapp;database=xxx and so on the local engine is connected ?
I think you mess ODBC and OLEDB connection strings here...
I think it must be
Provider=sqloledb;Data Source=123.123.123.123,1433;Network
Library=DBMSSOCN;Initial Catalog=yourdb;User ID=dbuser;Password=dbpass
Bojidar AlexandrovNo.
This connections string works fine. I do have it working with other
customers.
By the way it also works in the LAN enviroment.....
"Bojidar Alexandrov" <bojo@.kodar.net> schreef in bericht
news:uiFAzXFNEHA.2996@.TK2MSFTNGP12.phx.gbl...
> I think you mess ODBC and OLEDB connection strings here...
> I think it must be
> Provider=sqloledb;Data Source=123.123.123.123,1433;Network
> Library=DBMSSOCN;Initial Catalog=yourdb;User ID=dbuser;Password=dbpass
>
> Bojidar Alexandrov
>|||I do not say that it does not work, but that it is wrong. Please see
somewhere conenction string reference
Problem with VPN connection on SQL server
Hi,
my customer has a strange problem. In the LAN-enviroment the application can
connect the local SQL server as well as on the central database. The problem
occurs when a laptopuser wants to connect the central server via VPN.
The server (evnapp) and the disks of the server are accessible. A ping gives
the correct IP address.
But when connecting the database via:
Provider=sqloledb;driver={SQL Server};Network=DBMSSOCN;uid=xxx;
server=evnapp;database=xxx and so on the local engine is connected ?
There is a host table on the pc (laptops) which maps evnapp to the correct
IP.
When using the IP addres in stead of the server name the same thing happens.
A local connection.
The only time it does work is when the host table is removed after the
connection is estabilshed.
Is it the host table? Or the VPN-software?(Tunix/VPN for windows) . What can
it be. But stations (desktops) who were installed several months ago are
using the same host table and do not have any problems.
All pc's are running win2000. SQL 2000 is used.
Does this ring a bell ? If so please reply
Ton
begin 666 LMHOSTS.dat
M,3(W+C$V."XQ.# N-#@.)979N9&,P,0T*,3(W+C$V."XQ.# N-30)979N9&,P
M,@.T*,3(W+C$V."XQ.# N,S4)979N9G,P,0T*,3(W+C$V."XQ.# N-#,);FAL
M<W)V,#0-"C$R-RXQ-C@.N,3@.T+C0R"65V;F1C<F0-"C$R-RXQ-C@.N,3@.P+C0W
D"65V;F%P<# R#0HQ,C<N,38X+C$X,"XS- EN:&QS<G8Q. T*
`
end
begin 666 hosts.dat
M(R!(;W-T($-O<'ER:6=H="!.:65U=R!(;VQL86YD<V4@.3&QO>60@.,3DM,#8M
M,C P,@.T*#0HQ,C<N,"XP+C$@.(" @.(" @.;&]C86QH;W-T#0H-"B,@.<VUT< T*
M,3(W+C$V."XQ.# N-#,);FAL<W)V,#0-"@.T*(R!(5%10#0HQ,C<N,38X+C$X
M,"XS,0EN:&QS<G8Q.0T*,3(W+C$V."XQ.# N,S$):6YT<F%N970N;FAL+79E
M<GIE:V5R:6YG96XN;FP-"@.T*(R!!4R\T,# -"C$R-RXQ-C@.N,3@.P+C(S"5,T
M-$$V,#(V#0HQ,C<N,38X+C$X,"XR- E3-#1&,38Q. T*,3(W+C$V."XQ.# N
M,C8)4S0T,4%#.4$-"@.T*(R!305 -"C$R-RXQ-C@.N,3@.P+C8P"65V;G-A<'!R
M9 T*,3(W+C$V."XQ.# N,S<)979N<V%P86-C#0HQ,C<N,38X+C$X,"XU.0EE
M=FYS87!O;G0-"C$R-RXQ-C@.N,3@.P+C,V"65V;G-A<&1E=@.T*#0HC($YE=$))
M3U,-"C$R-RXQ-C@.N,3@.P+C0X"65V;F1C,#$-"C$R-RXQ-C@.N,3@.P+C4T"65V
M;F1C,#(-"C$R-RXQ-C@.N,3@.P+C,U"65V;F9S,#$-"C$R-RXQ-C@.N,3@.P+C0V
M"65V;F9S,#(-"C$R-RXQ-C@.N,3@.P+C0U"65V;FEI<S Q#0HQ,C<N,38X+C$X
M,"XV,0EE=FYI<V$-"C$R-RXQ-C@.N,3@.P+C,T
my customer has a strange problem. In the LAN-enviroment the application can
connect the local SQL server as well as on the central database. The problem
occurs when a laptopuser wants to connect the central server via VPN.
The server (evnapp) and the disks of the server are accessible. A ping gives
the correct IP address.
But when connecting the database via:
Provider=sqloledb;driver={SQL Server};Network=DBMSSOCN;uid=xxx;
server=evnapp;database=xxx and so on the local engine is connected ?
There is a host table on the pc (laptops) which maps evnapp to the correct
IP.
When using the IP addres in stead of the server name the same thing happens.
A local connection.
The only time it does work is when the host table is removed after the
connection is estabilshed.
Is it the host table? Or the VPN-software?(Tunix/VPN for windows) . What can
it be. But stations (desktops) who were installed several months ago are
using the same host table and do not have any problems.
All pc's are running win2000. SQL 2000 is used.
Does this ring a bell ? If so please reply
Ton
begin 666 LMHOSTS.dat
M,3(W+C$V."XQ.# N-#@.)979N9&,P,0T*,3(W+C$V."XQ.# N-30)979N9&,P
M,@.T*,3(W+C$V."XQ.# N,S4)979N9G,P,0T*,3(W+C$V."XQ.# N-#,);FAL
M<W)V,#0-"C$R-RXQ-C@.N,3@.T+C0R"65V;F1C<F0-"C$R-RXQ-C@.N,3@.P+C0W
D"65V;F%P<# R#0HQ,C<N,38X+C$X,"XS- EN:&QS<G8Q. T*
`
end
begin 666 hosts.dat
M(R!(;W-T($-O<'ER:6=H="!.:65U=R!(;VQL86YD<V4@.3&QO>60@.,3DM,#8M
M,C P,@.T*#0HQ,C<N,"XP+C$@.(" @.(" @.;&]C86QH;W-T#0H-"B,@.<VUT< T*
M,3(W+C$V."XQ.# N-#,);FAL<W)V,#0-"@.T*(R!(5%10#0HQ,C<N,38X+C$X
M,"XS,0EN:&QS<G8Q.0T*,3(W+C$V."XQ.# N,S$):6YT<F%N970N;FAL+79E
M<GIE:V5R:6YG96XN;FP-"@.T*(R!!4R\T,# -"C$R-RXQ-C@.N,3@.P+C(S"5,T
M-$$V,#(V#0HQ,C<N,38X+C$X,"XR- E3-#1&,38Q. T*,3(W+C$V."XQ.# N
M,C8)4S0T,4%#.4$-"@.T*(R!305 -"C$R-RXQ-C@.N,3@.P+C8P"65V;G-A<'!R
M9 T*,3(W+C$V."XQ.# N,S<)979N<V%P86-C#0HQ,C<N,38X+C$X,"XU.0EE
M=FYS87!O;G0-"C$R-RXQ-C@.N,3@.P+C,V"65V;G-A<&1E=@.T*#0HC($YE=$))
M3U,-"C$R-RXQ-C@.N,3@.P+C0X"65V;F1C,#$-"C$R-RXQ-C@.N,3@.P+C4T"65V
M;F1C,#(-"C$R-RXQ-C@.N,3@.P+C,U"65V;F9S,#$-"C$R-RXQ-C@.N,3@.P+C0V
M"65V;F9S,#(-"C$R-RXQ-C@.N,3@.P+C0U"65V;FEI<S Q#0HQ,C<N,38X+C$X
M,"XV,0EE=FYI<V$-"C$R-RXQ-C@.N,3@.P+C,T
problem with visual studio.net5005 connectionstring passed to SQL Server 2000
hi!
i'am recieving this error :
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server).
anybody can help me please
thanks
That is a general error thrown when there is a problem locating or connecting to a server. First place to look would be your connection string to make sure that the username/password is valid and has access to the server and the database.
BobP
Problem with Visual Studio 2005 pro + SQLserver 2005 developer
Hello
Im pretty new to development so.... be nice guys.
I have VS2005 pro and SQL Server 2005 developer edition installed. When I open VS, in the server explorer i can connet to databases located on the SQL directory (SQL Server runs perfectly) But when i try to add an sql server to my project( add new item --> SQL database) it gives me an error saying "Connection to SQL server files (*.mdf) require SQL Server Express 2005 to function properly. Please verify the installation of the component....."
HELP! donno what VS wants!
It seems that Visual Studio has inbuilt support only for Express edition of SQL Server 2005.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
>
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
A while back I ran into a problem where changes to views (such as renaming
them) was not being reflected. Somehow I was able to get past this problem.
Now I want to generate a script that will create my views. When I look at
the script that was generated has old information; the old names, and the old
queries.
Is there something I'm overlooking when I make changes to views?
Ensure that you are not creating views with different owners other than
DBO... It's possible that you are getting messed up with different version
of the same view owned by different owners..
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Tracy" <Tracy@.discussions.microsoft.com> wrote in message
news:711B82B3-D80C-4EA4-9014-7EA62C8DF370@.microsoft.com...
>A while back I ran into a problem where changes to views (such as renaming
> them) was not being reflected. Somehow I was able to get past this
> problem.
> Now I want to generate a script that will create my views. When I look at
> the script that was generated has old information; the old names, and the
> old
> queries.
> Is there something I'm overlooking when I make changes to views?
|||DBO is the only user for this DB. Each create statement in the generated
script looks like this: CREATE VIEW dbo.someview. The problem is that these
view names and associated querys are old, the way the were before I renamed
them and made changes to the queries.
Thanks for your quick reply,
-Tracy
"Wayne Snyder" wrote:
> Ensure that you are not creating views with different owners other than
> DBO... It's possible that you are getting messed up with different version
> of the same view owned by different owners..
>
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Tracy" <Tracy@.discussions.microsoft.com> wrote in message
> news:711B82B3-D80C-4EA4-9014-7EA62C8DF370@.microsoft.com...
>
>
|||On Tue, 16 Aug 2005 09:55:04 -0700, Tracy wrote:
>DBO is the only user for this DB. Each create statement in the generated
>script looks like this: CREATE VIEW dbo.someview. The problem is that these
>view names and associated querys are old, the way the were before I renamed
>them and made changes to the queries.
>Thanks for your quick reply,
>-Tracy
Hi Tracy,
How did you rename the queries? Did you execute a sp_rename? Or did you
open the view definition, change the name and execute it? In the latter
case, you didn't rename it - you made a new view (with a new name),
based on the old view. You'll still have to drop the old view.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Using SQL Server Enterprise Manager (v8.0) I renamed the views the Windows
Explorer way: Highlight the item, single click it, type in the new name.
I generated another script just now, but this time I left the "Generate the
DROP <object>" checked. The script had DROP commands for the current views,
but the CREATE VIEWs were all the old view names and queries.
"Hugo Kornelis" wrote:
> On Tue, 16 Aug 2005 09:55:04 -0700, Tracy wrote:
>
> Hi Tracy,
> How did you rename the queries? Did you execute a sp_rename? Or did you
> open the view definition, change the name and execute it? In the latter
> case, you didn't rename it - you made a new view (with a new name),
> based on the old view. You'll still have to drop the old view.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||X-Newsreader: Forte Agent 1.91/32.564
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Complaints-To: abuse@.supernews.com
Lines: 29
Path: TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-online.de!newsfeed.freenet.de!news.osn.de!diablo1-ffm.news.osn.de!news.tele.dk!news.tele.dk!small.ne ws.tele.dk!sn-xit-03!sn-xit-10!sn-xit-01!sn-post-01!supernews.com!corp.supernews.com!not-for-mail
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:403053
On Wed, 17 Aug 2005 06:45:03 -0700, Tracy wrote:
>Using SQL Server Enterprise Manager (v8.0) I renamed the views the Windows
>Explorer way: Highlight the item, single click it, type in the new name.
Hi Tracy,
Thanks - I never even knew that you could rename objects that way as
well!
However, the best way to rename views, stored procedures, triggers, and
user-defined functions is to explicitly drop and recreate them. Here's a
recent post by Tibor Karaszi about what goes wrong:
(Warning - long URL, check for line wrapping)
http://groups.google.com/groups?hl=e...aszi&safe=off&
qt_s=Search
>I generated another script just now, but this time I left the "Generate the
>DROP <object>" checked. The script had DROP commands for the current views,
>but the CREATE VIEWs were all the old view names and queries.
That's how you should rename your objects next time, but it won't help
now. I guess you'll have to manually change the name in the scripts.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thank you!
I was able to do in-depth research with the starting point you gave me.
I executed "select * from information_schema.views" and found that the
"view_definition" column had the old names. I was a little hasty in my
conclusion that the queries were old as well. I checked them thoroughly and
they look to be correct.
So to solve my current problem, I just need to change the names in the
generated script and then run the script, (including the DROPs).
In the future, I will try to get by without renaming. But if I must rename
the views, I will copy the query, drop the view, create a new view, paste the
query, and save it with the new name.
That's a lot more steps than simply doing the Explorer-style rename, but it
will save a lot of grief in the long run.
Thanks again, Hugo and Wayne, for your help
-Tracy
"Hugo Kornelis" wrote:
> Thanks - I never even knew that you could rename objects that way as
> well!
> However, the best way to rename views, stored procedures, triggers, and
> user-defined functions is to explicitly drop and recreate them. Here's a
> recent post by Tibor Karaszi about what goes wrong:
> (Warning - long URL, check for line wrapping)
> http://groups.google.com/groups?hl=e...araszi&safe=of
f&qt_s=Search
>
|||On Thu, 18 Aug 2005 06:50:08 -0700, Tracy wrote:
(snip)
>In the future, I will try to get by without renaming. But if I must rename
>the views, I will copy the query, drop the view, create a new view, paste the
>query, and save it with the new name.
>That's a lot more steps than simply doing the Explorer-style rename, but it
>will save a lot of grief in the long run.
Hi Tracy,
Slightly shorter: generate a script including the DROP command; edit the
script (but only chage the name on the CREATE command) and execute it.
Better yet: store all DDL for your tables, views, procs, etc outside of
SQL Server - preferably in source control.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
them) was not being reflected. Somehow I was able to get past this problem.
Now I want to generate a script that will create my views. When I look at
the script that was generated has old information; the old names, and the old
queries.
Is there something I'm overlooking when I make changes to views?
Ensure that you are not creating views with different owners other than
DBO... It's possible that you are getting messed up with different version
of the same view owned by different owners..
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Tracy" <Tracy@.discussions.microsoft.com> wrote in message
news:711B82B3-D80C-4EA4-9014-7EA62C8DF370@.microsoft.com...
>A while back I ran into a problem where changes to views (such as renaming
> them) was not being reflected. Somehow I was able to get past this
> problem.
> Now I want to generate a script that will create my views. When I look at
> the script that was generated has old information; the old names, and the
> old
> queries.
> Is there something I'm overlooking when I make changes to views?
|||DBO is the only user for this DB. Each create statement in the generated
script looks like this: CREATE VIEW dbo.someview. The problem is that these
view names and associated querys are old, the way the were before I renamed
them and made changes to the queries.
Thanks for your quick reply,
-Tracy
"Wayne Snyder" wrote:
> Ensure that you are not creating views with different owners other than
> DBO... It's possible that you are getting messed up with different version
> of the same view owned by different owners..
>
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Tracy" <Tracy@.discussions.microsoft.com> wrote in message
> news:711B82B3-D80C-4EA4-9014-7EA62C8DF370@.microsoft.com...
>
>
|||On Tue, 16 Aug 2005 09:55:04 -0700, Tracy wrote:
>DBO is the only user for this DB. Each create statement in the generated
>script looks like this: CREATE VIEW dbo.someview. The problem is that these
>view names and associated querys are old, the way the were before I renamed
>them and made changes to the queries.
>Thanks for your quick reply,
>-Tracy
Hi Tracy,
How did you rename the queries? Did you execute a sp_rename? Or did you
open the view definition, change the name and execute it? In the latter
case, you didn't rename it - you made a new view (with a new name),
based on the old view. You'll still have to drop the old view.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Using SQL Server Enterprise Manager (v8.0) I renamed the views the Windows
Explorer way: Highlight the item, single click it, type in the new name.
I generated another script just now, but this time I left the "Generate the
DROP <object>" checked. The script had DROP commands for the current views,
but the CREATE VIEWs were all the old view names and queries.
"Hugo Kornelis" wrote:
> On Tue, 16 Aug 2005 09:55:04 -0700, Tracy wrote:
>
> Hi Tracy,
> How did you rename the queries? Did you execute a sp_rename? Or did you
> open the view definition, change the name and execute it? In the latter
> case, you didn't rename it - you made a new view (with a new name),
> based on the old view. You'll still have to drop the old view.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||X-Newsreader: Forte Agent 1.91/32.564
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Complaints-To: abuse@.supernews.com
Lines: 29
Path: TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-online.de!newsfeed.freenet.de!news.osn.de!diablo1-ffm.news.osn.de!news.tele.dk!news.tele.dk!small.ne ws.tele.dk!sn-xit-03!sn-xit-10!sn-xit-01!sn-post-01!supernews.com!corp.supernews.com!not-for-mail
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:403053
On Wed, 17 Aug 2005 06:45:03 -0700, Tracy wrote:
>Using SQL Server Enterprise Manager (v8.0) I renamed the views the Windows
>Explorer way: Highlight the item, single click it, type in the new name.
Hi Tracy,
Thanks - I never even knew that you could rename objects that way as
well!
However, the best way to rename views, stored procedures, triggers, and
user-defined functions is to explicitly drop and recreate them. Here's a
recent post by Tibor Karaszi about what goes wrong:
(Warning - long URL, check for line wrapping)
http://groups.google.com/groups?hl=e...aszi&safe=off&
qt_s=Search
>I generated another script just now, but this time I left the "Generate the
>DROP <object>" checked. The script had DROP commands for the current views,
>but the CREATE VIEWs were all the old view names and queries.
That's how you should rename your objects next time, but it won't help
now. I guess you'll have to manually change the name in the scripts.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thank you!
I was able to do in-depth research with the starting point you gave me.
I executed "select * from information_schema.views" and found that the
"view_definition" column had the old names. I was a little hasty in my
conclusion that the queries were old as well. I checked them thoroughly and
they look to be correct.
So to solve my current problem, I just need to change the names in the
generated script and then run the script, (including the DROPs).
In the future, I will try to get by without renaming. But if I must rename
the views, I will copy the query, drop the view, create a new view, paste the
query, and save it with the new name.
That's a lot more steps than simply doing the Explorer-style rename, but it
will save a lot of grief in the long run.
Thanks again, Hugo and Wayne, for your help
-Tracy
"Hugo Kornelis" wrote:
> Thanks - I never even knew that you could rename objects that way as
> well!
> However, the best way to rename views, stored procedures, triggers, and
> user-defined functions is to explicitly drop and recreate them. Here's a
> recent post by Tibor Karaszi about what goes wrong:
> (Warning - long URL, check for line wrapping)
> http://groups.google.com/groups?hl=e...araszi&safe=of
f&qt_s=Search
>
|||On Thu, 18 Aug 2005 06:50:08 -0700, Tracy wrote:
(snip)
>In the future, I will try to get by without renaming. But if I must rename
>the views, I will copy the query, drop the view, create a new view, paste the
>query, and save it with the new name.
>That's a lot more steps than simply doing the Explorer-style rename, but it
>will save a lot of grief in the long run.
Hi Tracy,
Slightly shorter: generate a script including the DROP command; edit the
script (but only chage the name on the CREATE command) and execute it.
Better yet: store all DDL for your tables, views, procs, etc outside of
SQL Server - preferably in source control.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
Problem with Views
A while back I ran into a problem where changes to views (such as renaming
them) was not being reflected. Somehow I was able to get past this problem.
Now I want to generate a script that will create my views. When I look at
the script that was generated has old information; the old names, and the ol
d
queries.
Is there something I'm overlooking when I make changes to views?Ensure that you are not creating views with different owners other than
DBO... It's possible that you are getting messed up with different version
of the same view owned by different owners..
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Tracy" <Tracy@.discussions.microsoft.com> wrote in message
news:711B82B3-D80C-4EA4-9014-7EA62C8DF370@.microsoft.com...
>A while back I ran into a problem where changes to views (such as renaming
> them) was not being reflected. Somehow I was able to get past this
> problem.
> Now I want to generate a script that will create my views. When I look at
> the script that was generated has old information; the old names, and the
> old
> queries.
> Is there something I'm overlooking when I make changes to views?|||DBO is the only user for this DB. Each create statement in the generated
script looks like this: CREATE VIEW dbo.someview. The problem is that these
view names and associated querys are old, the way the were before I renamed
them and made changes to the queries.
Thanks for your quick reply,
-Tracy
"Wayne Snyder" wrote:
> Ensure that you are not creating views with different owners other than
> DBO... It's possible that you are getting messed up with different versio
n
> of the same view owned by different owners..
>
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Tracy" <Tracy@.discussions.microsoft.com> wrote in message
> news:711B82B3-D80C-4EA4-9014-7EA62C8DF370@.microsoft.com...
>
>|||On Tue, 16 Aug 2005 09:55:04 -0700, Tracy wrote:
>DBO is the only user for this DB. Each create statement in the generated
>script looks like this: CREATE VIEW dbo.someview. The problem is that thes
e
>view names and associated querys are old, the way the were before I renamed
>them and made changes to the queries.
>Thanks for your quick reply,
>-Tracy
Hi Tracy,
How did you rename the queries? Did you execute a sp_rename? Or did you
open the view definition, change the name and execute it? In the latter
case, you didn't rename it - you made a new view (with a new name),
based on the old view. You'll still have to drop the old view.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Using SQL Server Enterprise Manager (v8.0) I renamed the views the Windows
Explorer way: Highlight the item, single click it, type in the new name.
I generated another script just now, but this time I left the "Generate the
DROP <object>" checked. The script had DROP commands for the current views,
but the CREATE VIEWs were all the old view names and queries.
"Hugo Kornelis" wrote:
> On Tue, 16 Aug 2005 09:55:04 -0700, Tracy wrote:
>
> Hi Tracy,
> How did you rename the queries? Did you execute a sp_rename? Or did you
> open the view definition, change the name and execute it? In the latter
> case, you didn't rename it - you made a new view (with a new name),
> based on the old view. You'll still have to drop the old view.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||X-Newsreader: Forte Agent 1.91/32.564
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Complaints-To: abuse@.supernews.com
Lines: 29
Path: TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-online.de!newsfeed.f
reenet.de!news.osn.de!diablo1-ffm.news.osn.de!news.tele.dk!news.tele.dk!smal
l.news.tele.dk!sn-xit-03!sn-xit-10!sn-xit-01!sn-post-01!supernews.com!corp.s
upernews.com!not-for-mail
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:403053
On Wed, 17 Aug 2005 06:45:03 -0700, Tracy wrote:
>Using SQL Server Enterprise Manager (v8.0) I renamed the views the Windows
>Explorer way: Highlight the item, single click it, type in the new name.
Hi Tracy,
Thanks - I never even knew that you could rename objects that way as
well!
However, the best way to rename views, stored procedures, triggers, and
user-defined functions is to explicitly drop and recreate them. Here's a
recent post by Tibor Karaszi about what goes wrong:
(Warning - long URL, check for line wrapping)
http://groups.google.com/groups?hl=...r />
safe=off&
qt_s=Search
>I generated another script just now, but this time I left the "Generate the
>DROP <object>" checked. The script had DROP commands for the current views
,
>but the CREATE VIEWs were all the old view names and queries.
That's how you should rename your objects next time, but it won't help
now. I guess you'll have to manually change the name in the scripts.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you!
I was able to do in-depth research with the starting point you gave me.
I executed "select * from information_schema.views" and found that the
"view_definition" column had the old names. I was a little hasty in my
conclusion that the queries were old as well. I checked them thoroughly and
they look to be correct.
So to solve my current problem, I just need to change the names in the
generated script and then run the script, (including the DROPs).
In the future, I will try to get by without renaming. But if I must rename
the views, I will copy the query, drop the view, create a new view, paste th
e
query, and save it with the new name.
That's a lot more steps than simply doing the Explorer-style rename, but it
will save a lot of grief in the long run.
Thanks again, Hugo and Wayne, for your help
-Tracy
"Hugo Kornelis" wrote:
> Thanks - I never even knew that you could rename objects that way as
> well!
> However, the best way to rename views, stored procedures, triggers, and
> user-defined functions is to explicitly drop and recreate them. Here's a
> recent post by Tibor Karaszi about what goes wrong:
> (Warning - long URL, check for line wrapping)
> http://groups.google.com/groups?hl=...Karaszi&safe=of
f&qt_s=Search
>|||On Thu, 18 Aug 2005 06:50:08 -0700, Tracy wrote:
(snip)
>In the future, I will try to get by without renaming. But if I must rename
>the views, I will copy the query, drop the view, create a new view, paste t
he
>query, and save it with the new name.
>That's a lot more steps than simply doing the Explorer-style rename, but it
>will save a lot of grief in the long run.
Hi Tracy,
Slightly shorter: generate a script including the DROP command; edit the
script (but only chage the name on the CREATE command) and execute it.
Better yet: store all DDL for your tables, views, procs, etc outside of
SQL Server - preferably in source control.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
them) was not being reflected. Somehow I was able to get past this problem.
Now I want to generate a script that will create my views. When I look at
the script that was generated has old information; the old names, and the ol
d
queries.
Is there something I'm overlooking when I make changes to views?Ensure that you are not creating views with different owners other than
DBO... It's possible that you are getting messed up with different version
of the same view owned by different owners..
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Tracy" <Tracy@.discussions.microsoft.com> wrote in message
news:711B82B3-D80C-4EA4-9014-7EA62C8DF370@.microsoft.com...
>A while back I ran into a problem where changes to views (such as renaming
> them) was not being reflected. Somehow I was able to get past this
> problem.
> Now I want to generate a script that will create my views. When I look at
> the script that was generated has old information; the old names, and the
> old
> queries.
> Is there something I'm overlooking when I make changes to views?|||DBO is the only user for this DB. Each create statement in the generated
script looks like this: CREATE VIEW dbo.someview. The problem is that these
view names and associated querys are old, the way the were before I renamed
them and made changes to the queries.
Thanks for your quick reply,
-Tracy
"Wayne Snyder" wrote:
> Ensure that you are not creating views with different owners other than
> DBO... It's possible that you are getting messed up with different versio
n
> of the same view owned by different owners..
>
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Tracy" <Tracy@.discussions.microsoft.com> wrote in message
> news:711B82B3-D80C-4EA4-9014-7EA62C8DF370@.microsoft.com...
>
>|||On Tue, 16 Aug 2005 09:55:04 -0700, Tracy wrote:
>DBO is the only user for this DB. Each create statement in the generated
>script looks like this: CREATE VIEW dbo.someview. The problem is that thes
e
>view names and associated querys are old, the way the were before I renamed
>them and made changes to the queries.
>Thanks for your quick reply,
>-Tracy
Hi Tracy,
How did you rename the queries? Did you execute a sp_rename? Or did you
open the view definition, change the name and execute it? In the latter
case, you didn't rename it - you made a new view (with a new name),
based on the old view. You'll still have to drop the old view.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Using SQL Server Enterprise Manager (v8.0) I renamed the views the Windows
Explorer way: Highlight the item, single click it, type in the new name.
I generated another script just now, but this time I left the "Generate the
DROP <object>" checked. The script had DROP commands for the current views,
but the CREATE VIEWs were all the old view names and queries.
"Hugo Kornelis" wrote:
> On Tue, 16 Aug 2005 09:55:04 -0700, Tracy wrote:
>
> Hi Tracy,
> How did you rename the queries? Did you execute a sp_rename? Or did you
> open the view definition, change the name and execute it? In the latter
> case, you didn't rename it - you made a new view (with a new name),
> based on the old view. You'll still have to drop the old view.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||X-Newsreader: Forte Agent 1.91/32.564
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Complaints-To: abuse@.supernews.com
Lines: 29
Path: TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-online.de!newsfeed.f
reenet.de!news.osn.de!diablo1-ffm.news.osn.de!news.tele.dk!news.tele.dk!smal
l.news.tele.dk!sn-xit-03!sn-xit-10!sn-xit-01!sn-post-01!supernews.com!corp.s
upernews.com!not-for-mail
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:403053
On Wed, 17 Aug 2005 06:45:03 -0700, Tracy wrote:
>Using SQL Server Enterprise Manager (v8.0) I renamed the views the Windows
>Explorer way: Highlight the item, single click it, type in the new name.
Hi Tracy,
Thanks - I never even knew that you could rename objects that way as
well!
However, the best way to rename views, stored procedures, triggers, and
user-defined functions is to explicitly drop and recreate them. Here's a
recent post by Tibor Karaszi about what goes wrong:
(Warning - long URL, check for line wrapping)
http://groups.google.com/groups?hl=...r />
safe=off&
qt_s=Search
>I generated another script just now, but this time I left the "Generate the
>DROP <object>" checked. The script had DROP commands for the current views
,
>but the CREATE VIEWs were all the old view names and queries.
That's how you should rename your objects next time, but it won't help
now. I guess you'll have to manually change the name in the scripts.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you!
I was able to do in-depth research with the starting point you gave me.
I executed "select * from information_schema.views" and found that the
"view_definition" column had the old names. I was a little hasty in my
conclusion that the queries were old as well. I checked them thoroughly and
they look to be correct.
So to solve my current problem, I just need to change the names in the
generated script and then run the script, (including the DROPs).
In the future, I will try to get by without renaming. But if I must rename
the views, I will copy the query, drop the view, create a new view, paste th
e
query, and save it with the new name.
That's a lot more steps than simply doing the Explorer-style rename, but it
will save a lot of grief in the long run.
Thanks again, Hugo and Wayne, for your help
-Tracy
"Hugo Kornelis" wrote:
> Thanks - I never even knew that you could rename objects that way as
> well!
> However, the best way to rename views, stored procedures, triggers, and
> user-defined functions is to explicitly drop and recreate them. Here's a
> recent post by Tibor Karaszi about what goes wrong:
> (Warning - long URL, check for line wrapping)
> http://groups.google.com/groups?hl=...Karaszi&safe=of
f&qt_s=Search
>|||On Thu, 18 Aug 2005 06:50:08 -0700, Tracy wrote:
(snip)
>In the future, I will try to get by without renaming. But if I must rename
>the views, I will copy the query, drop the view, create a new view, paste t
he
>query, and save it with the new name.
>That's a lot more steps than simply doing the Explorer-style rename, but it
>will save a lot of grief in the long run.
Hi Tracy,
Slightly shorter: generate a script including the DROP command; edit the
script (but only chage the name on the CREATE command) and execute it.
Better yet: store all DDL for your tables, views, procs, etc outside of
SQL Server - preferably in source control.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Problem with Views
A while back I ran into a problem where changes to views (such as renaming
them) was not being reflected. Somehow I was able to get past this problem.
Now I want to generate a script that will create my views. When I look at
the script that was generated has old information; the old names, and the old
queries.
Is there something I'm overlooking when I make changes to views?Ensure that you are not creating views with different owners other than
DBO... It's possible that you are getting messed up with different version
of the same view owned by different owners..
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Tracy" <Tracy@.discussions.microsoft.com> wrote in message
news:711B82B3-D80C-4EA4-9014-7EA62C8DF370@.microsoft.com...
>A while back I ran into a problem where changes to views (such as renaming
> them) was not being reflected. Somehow I was able to get past this
> problem.
> Now I want to generate a script that will create my views. When I look at
> the script that was generated has old information; the old names, and the
> old
> queries.
> Is there something I'm overlooking when I make changes to views?|||DBO is the only user for this DB. Each create statement in the generated
script looks like this: CREATE VIEW dbo.someview. The problem is that these
view names and associated querys are old, the way the were before I renamed
them and made changes to the queries.
Thanks for your quick reply,
-Tracy
"Wayne Snyder" wrote:
> Ensure that you are not creating views with different owners other than
> DBO... It's possible that you are getting messed up with different version
> of the same view owned by different owners..
>
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Tracy" <Tracy@.discussions.microsoft.com> wrote in message
> news:711B82B3-D80C-4EA4-9014-7EA62C8DF370@.microsoft.com...
> >A while back I ran into a problem where changes to views (such as renaming
> > them) was not being reflected. Somehow I was able to get past this
> > problem.
> >
> > Now I want to generate a script that will create my views. When I look at
> > the script that was generated has old information; the old names, and the
> > old
> > queries.
> >
> > Is there something I'm overlooking when I make changes to views?
>
>|||On Tue, 16 Aug 2005 09:55:04 -0700, Tracy wrote:
>DBO is the only user for this DB. Each create statement in the generated
>script looks like this: CREATE VIEW dbo.someview. The problem is that these
>view names and associated querys are old, the way the were before I renamed
>them and made changes to the queries.
>Thanks for your quick reply,
>-Tracy
Hi Tracy,
How did you rename the queries? Did you execute a sp_rename? Or did you
open the view definition, change the name and execute it? In the latter
case, you didn't rename it - you made a new view (with a new name),
based on the old view. You'll still have to drop the old view.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Using SQL Server Enterprise Manager (v8.0) I renamed the views the Windows
Explorer way: Highlight the item, single click it, type in the new name.
I generated another script just now, but this time I left the "Generate the
DROP <object>" checked. The script had DROP commands for the current views,
but the CREATE VIEWs were all the old view names and queries.
"Hugo Kornelis" wrote:
> On Tue, 16 Aug 2005 09:55:04 -0700, Tracy wrote:
> >DBO is the only user for this DB. Each create statement in the generated
> >script looks like this: CREATE VIEW dbo.someview. The problem is that these
> >view names and associated querys are old, the way the were before I renamed
> >them and made changes to the queries.
> >
> >Thanks for your quick reply,
> >-Tracy
> Hi Tracy,
> How did you rename the queries? Did you execute a sp_rename? Or did you
> open the view definition, change the name and execute it? In the latter
> case, you didn't rename it - you made a new view (with a new name),
> based on the old view. You'll still have to drop the old view.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||On Wed, 17 Aug 2005 06:45:03 -0700, Tracy wrote:
>Using SQL Server Enterprise Manager (v8.0) I renamed the views the Windows
>Explorer way: Highlight the item, single click it, type in the new name.
Hi Tracy,
Thanks - I never even knew that you could rename objects that way as
well!
However, the best way to rename views, stored procedures, triggers, and
user-defined functions is to explicitly drop and recreate them. Here's a
recent post by Tibor Karaszi about what goes wrong:
(Warning - long URL, check for line wrapping)
http://groups.google.com/groups?hl=en&lr=&safe=off&num=10&q=syscomments+group%3Amicrosoft.public.sqlserver.programming+insubject%3ARenaming+insubject%3Astored+insubject%3Aprocedures+insubject%3Ain+insubject%3Abulk+author%3ATibor+author%3AKaraszi&safe=off&qt_s=Search
>I generated another script just now, but this time I left the "Generate the
>DROP <object>" checked. The script had DROP commands for the current views,
>but the CREATE VIEWs were all the old view names and queries.
That's how you should rename your objects next time, but it won't help
now. I guess you'll have to manually change the name in the scripts.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you!
I was able to do in-depth research with the starting point you gave me.
I executed "select * from information_schema.views" and found that the
"view_definition" column had the old names. I was a little hasty in my
conclusion that the queries were old as well. I checked them thoroughly and
they look to be correct.
So to solve my current problem, I just need to change the names in the
generated script and then run the script, (including the DROPs).
In the future, I will try to get by without renaming. But if I must rename
the views, I will copy the query, drop the view, create a new view, paste the
query, and save it with the new name.
That's a lot more steps than simply doing the Explorer-style rename, but it
will save a lot of grief in the long run.
Thanks again, Hugo and Wayne, for your help
-Tracy
"Hugo Kornelis" wrote:
> Thanks - I never even knew that you could rename objects that way as
> well!
> However, the best way to rename views, stored procedures, triggers, and
> user-defined functions is to explicitly drop and recreate them. Here's a
> recent post by Tibor Karaszi about what goes wrong:
> (Warning - long URL, check for line wrapping)
> http://groups.google.com/groups?hl=en&lr=&safe=off&num=10&q=syscomments+group%3Amicrosoft.public.sqlserver.programming+insubject%3ARenaming+insubject%3Astored+insubject%3Aprocedures+insubject%3Ain+insubject%3Abulk+author%3ATibor+author%3AKaraszi&safe=off&qt_s=Search
>|||On Thu, 18 Aug 2005 06:50:08 -0700, Tracy wrote:
(snip)
>In the future, I will try to get by without renaming. But if I must rename
>the views, I will copy the query, drop the view, create a new view, paste the
>query, and save it with the new name.
>That's a lot more steps than simply doing the Explorer-style rename, but it
>will save a lot of grief in the long run.
Hi Tracy,
Slightly shorter: generate a script including the DROP command; edit the
script (but only chage the name on the CREATE command) and execute it.
Better yet: store all DDL for your tables, views, procs, etc outside of
SQL Server - preferably in source control.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
them) was not being reflected. Somehow I was able to get past this problem.
Now I want to generate a script that will create my views. When I look at
the script that was generated has old information; the old names, and the old
queries.
Is there something I'm overlooking when I make changes to views?Ensure that you are not creating views with different owners other than
DBO... It's possible that you are getting messed up with different version
of the same view owned by different owners..
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Tracy" <Tracy@.discussions.microsoft.com> wrote in message
news:711B82B3-D80C-4EA4-9014-7EA62C8DF370@.microsoft.com...
>A while back I ran into a problem where changes to views (such as renaming
> them) was not being reflected. Somehow I was able to get past this
> problem.
> Now I want to generate a script that will create my views. When I look at
> the script that was generated has old information; the old names, and the
> old
> queries.
> Is there something I'm overlooking when I make changes to views?|||DBO is the only user for this DB. Each create statement in the generated
script looks like this: CREATE VIEW dbo.someview. The problem is that these
view names and associated querys are old, the way the were before I renamed
them and made changes to the queries.
Thanks for your quick reply,
-Tracy
"Wayne Snyder" wrote:
> Ensure that you are not creating views with different owners other than
> DBO... It's possible that you are getting messed up with different version
> of the same view owned by different owners..
>
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Tracy" <Tracy@.discussions.microsoft.com> wrote in message
> news:711B82B3-D80C-4EA4-9014-7EA62C8DF370@.microsoft.com...
> >A while back I ran into a problem where changes to views (such as renaming
> > them) was not being reflected. Somehow I was able to get past this
> > problem.
> >
> > Now I want to generate a script that will create my views. When I look at
> > the script that was generated has old information; the old names, and the
> > old
> > queries.
> >
> > Is there something I'm overlooking when I make changes to views?
>
>|||On Tue, 16 Aug 2005 09:55:04 -0700, Tracy wrote:
>DBO is the only user for this DB. Each create statement in the generated
>script looks like this: CREATE VIEW dbo.someview. The problem is that these
>view names and associated querys are old, the way the were before I renamed
>them and made changes to the queries.
>Thanks for your quick reply,
>-Tracy
Hi Tracy,
How did you rename the queries? Did you execute a sp_rename? Or did you
open the view definition, change the name and execute it? In the latter
case, you didn't rename it - you made a new view (with a new name),
based on the old view. You'll still have to drop the old view.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Using SQL Server Enterprise Manager (v8.0) I renamed the views the Windows
Explorer way: Highlight the item, single click it, type in the new name.
I generated another script just now, but this time I left the "Generate the
DROP <object>" checked. The script had DROP commands for the current views,
but the CREATE VIEWs were all the old view names and queries.
"Hugo Kornelis" wrote:
> On Tue, 16 Aug 2005 09:55:04 -0700, Tracy wrote:
> >DBO is the only user for this DB. Each create statement in the generated
> >script looks like this: CREATE VIEW dbo.someview. The problem is that these
> >view names and associated querys are old, the way the were before I renamed
> >them and made changes to the queries.
> >
> >Thanks for your quick reply,
> >-Tracy
> Hi Tracy,
> How did you rename the queries? Did you execute a sp_rename? Or did you
> open the view definition, change the name and execute it? In the latter
> case, you didn't rename it - you made a new view (with a new name),
> based on the old view. You'll still have to drop the old view.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||On Wed, 17 Aug 2005 06:45:03 -0700, Tracy wrote:
>Using SQL Server Enterprise Manager (v8.0) I renamed the views the Windows
>Explorer way: Highlight the item, single click it, type in the new name.
Hi Tracy,
Thanks - I never even knew that you could rename objects that way as
well!
However, the best way to rename views, stored procedures, triggers, and
user-defined functions is to explicitly drop and recreate them. Here's a
recent post by Tibor Karaszi about what goes wrong:
(Warning - long URL, check for line wrapping)
http://groups.google.com/groups?hl=en&lr=&safe=off&num=10&q=syscomments+group%3Amicrosoft.public.sqlserver.programming+insubject%3ARenaming+insubject%3Astored+insubject%3Aprocedures+insubject%3Ain+insubject%3Abulk+author%3ATibor+author%3AKaraszi&safe=off&qt_s=Search
>I generated another script just now, but this time I left the "Generate the
>DROP <object>" checked. The script had DROP commands for the current views,
>but the CREATE VIEWs were all the old view names and queries.
That's how you should rename your objects next time, but it won't help
now. I guess you'll have to manually change the name in the scripts.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you!
I was able to do in-depth research with the starting point you gave me.
I executed "select * from information_schema.views" and found that the
"view_definition" column had the old names. I was a little hasty in my
conclusion that the queries were old as well. I checked them thoroughly and
they look to be correct.
So to solve my current problem, I just need to change the names in the
generated script and then run the script, (including the DROPs).
In the future, I will try to get by without renaming. But if I must rename
the views, I will copy the query, drop the view, create a new view, paste the
query, and save it with the new name.
That's a lot more steps than simply doing the Explorer-style rename, but it
will save a lot of grief in the long run.
Thanks again, Hugo and Wayne, for your help
-Tracy
"Hugo Kornelis" wrote:
> Thanks - I never even knew that you could rename objects that way as
> well!
> However, the best way to rename views, stored procedures, triggers, and
> user-defined functions is to explicitly drop and recreate them. Here's a
> recent post by Tibor Karaszi about what goes wrong:
> (Warning - long URL, check for line wrapping)
> http://groups.google.com/groups?hl=en&lr=&safe=off&num=10&q=syscomments+group%3Amicrosoft.public.sqlserver.programming+insubject%3ARenaming+insubject%3Astored+insubject%3Aprocedures+insubject%3Ain+insubject%3Abulk+author%3ATibor+author%3AKaraszi&safe=off&qt_s=Search
>|||On Thu, 18 Aug 2005 06:50:08 -0700, Tracy wrote:
(snip)
>In the future, I will try to get by without renaming. But if I must rename
>the views, I will copy the query, drop the view, create a new view, paste the
>query, and save it with the new name.
>That's a lot more steps than simply doing the Explorer-style rename, but it
>will save a lot of grief in the long run.
Hi Tracy,
Slightly shorter: generate a script including the DROP command; edit the
script (but only chage the name on the CREATE command) and execute it.
Better yet: store all DDL for your tables, views, procs, etc outside of
SQL Server - preferably in source control.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
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
>
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 viewing column permissions in Enterprise Manager
When trying to configure SQL Server security at the column level I ran into
a problem: if I assigned column-level security through Enterprise Manager it
seemed to work fine, but after reopening the permission dialog box the
permissions on some columns disappeared.
After doing some research I can now simulate the behaviour when running the
following script. In Enterprise manager the security on column 'Col3' does
not show after running the script, though it is there.
CREATE TABLE test
(
Col1 int NOT NULL,
Col2 nvarchar(50) NOT NULL,
Col3 int NOT NULL
)
GO
ALTER TABLE test
DROP COLUMN Col2
GO
GRANT SELECT ON test(Col3) TO public
GO
/* Correct: returns select permission on Col3 */
sp_helprotect 'test'
GO
/* Bug! Does not return anything. */
/* Problem is that sp_MSobjectprivs uses count(*) on syscolumns instead of
max(colid) to determine maximum column number */
sp_MSobjectprivs N'test','column',null,null,null,null,0,1
GO
Enterprise Manager seems to use the sp_MSobjectprivs procedure to retrieve
the column permissions, but that one fails if columns are not numbered
consecutively (as caused by the DROP COLUMN).
Stefan Cuypers.
Dear Stefan,
Thank you for your feedback. I have reproduced the problem. Please rest
assured that we do understand your concerns and that the known issue is
being worked in development with priority after we report it via our
internal channel. We strive to capture any and all product issues / product
feedback so as to ensure that we are continuously developing Microsoft
products to meet customer needs. This is exactly why feedback such as
yours is always taken very seriously.
While we also hope to see the issue fixed as soon as possible, we have
re-confirmed with the development team that there are no better workarounds
at this time. If there are any updates on the issue, I will do my best to
be the first one to let you know.
Once again, thank you for your time and feedback.
Sincerely,
Bill Cheng
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
| From: "Stefan Cuypers" <stefan.cuypers@.nospam.nospam>
| Subject: Problem with viewing column permissions in Enterprise Manager
| Date: Fri, 2 Jul 2004 16:15:01 +0200
| Lines: 35
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.3790.0
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.132
| Message-ID: <#kQW57DYEHA.2908@.TK2MSFTNGP10.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.tools
| NNTP-Posting-Host: d576ae10.kabel.telenet.be 213.118.174.16
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP10.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.tools:24406
| X-Tomcat-NG: microsoft.public.sqlserver.tools
|
| When trying to configure SQL Server security at the column level I ran
into
| a problem: if I assigned column-level security through Enterprise Manager
it
| seemed to work fine, but after reopening the permission dialog box the
| permissions on some columns disappeared.
| After doing some research I can now simulate the behaviour when running
the
| following script. In Enterprise manager the security on column 'Col3' does
| not show after running the script, though it is there.
| CREATE TABLE test
| (
| Col1 int NOT NULL,
| Col2 nvarchar(50) NOT NULL,
| Col3 int NOT NULL
| )
| GO
| ALTER TABLE test
| DROP COLUMN Col2
| GO
| GRANT SELECT ON test(Col3) TO public
| GO
| /* Correct: returns select permission on Col3 */
| sp_helprotect 'test'
| GO
| /* Bug! Does not return anything. */
| /* Problem is that sp_MSobjectprivs uses count(*) on syscolumns instead of
| max(colid) to determine maximum column number */
| sp_MSobjectprivs N'test','column',null,null,null,null,0,1
| GO
|
| Enterprise Manager seems to use the sp_MSobjectprivs procedure to retrieve
| the column permissions, but that one fails if columns are not numbered
| consecutively (as caused by the DROP COLUMN).
|
| Stefan Cuypers.
|
|
|
|||Bill,
Thanks for the feedback. I'm glad you take it seriously and will try to fix
it in a next update.
Stefan.
""Bill Cheng"" <billchng@.online.microsoft.com> wrote in message
news:jNqMXDOYEHA.2352@.cpmsftngxa06.phx.gbl...
> Dear Stefan,
> Thank you for your feedback. I have reproduced the problem. Please rest
> assured that we do understand your concerns and that the known issue is
> being worked in development with priority after we report it via our
> internal channel. We strive to capture any and all product issues /
product
> feedback so as to ensure that we are continuously developing Microsoft
> products to meet customer needs. This is exactly why feedback such as
> yours is always taken very seriously.
> While we also hope to see the issue fixed as soon as possible, we have
> re-confirmed with the development team that there are no better
workarounds
> at this time. If there are any updates on the issue, I will do my best to
> be the first one to let you know.
> Once again, thank you for your time and feedback.
> Sincerely,
> Bill Cheng
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> --
> | From: "Stefan Cuypers" <stefan.cuypers@.nospam.nospam>
> | Subject: Problem with viewing column permissions in Enterprise Manager
> | Date: Fri, 2 Jul 2004 16:15:01 +0200
> | Lines: 35
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.3790.0
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.132
> | Message-ID: <#kQW57DYEHA.2908@.TK2MSFTNGP10.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.tools
> | NNTP-Posting-Host: d576ae10.kabel.telenet.be 213.118.174.16
> | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP10.phx.gbl
> | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.tools:24406
> | X-Tomcat-NG: microsoft.public.sqlserver.tools
> |
> | When trying to configure SQL Server security at the column level I ran
> into
> | a problem: if I assigned column-level security through Enterprise
Manager
> it
> | seemed to work fine, but after reopening the permission dialog box the
> | permissions on some columns disappeared.
> | After doing some research I can now simulate the behaviour when running
> the
> | following script. In Enterprise manager the security on column 'Col3'
does
> | not show after running the script, though it is there.
> | CREATE TABLE test
> | (
> | Col1 int NOT NULL,
> | Col2 nvarchar(50) NOT NULL,
> | Col3 int NOT NULL
> | )
> | GO
> | ALTER TABLE test
> | DROP COLUMN Col2
> | GO
> | GRANT SELECT ON test(Col3) TO public
> | GO
> | /* Correct: returns select permission on Col3 */
> | sp_helprotect 'test'
> | GO
> | /* Bug! Does not return anything. */
> | /* Problem is that sp_MSobjectprivs uses count(*) on syscolumns instead
of
> | max(colid) to determine maximum column number */
> | sp_MSobjectprivs N'test','column',null,null,null,null,0,1
> | GO
> |
> | Enterprise Manager seems to use the sp_MSobjectprivs procedure to
retrieve
> | the column permissions, but that one fails if columns are not numbered
> | consecutively (as caused by the DROP COLUMN).
> |
> | Stefan Cuypers.
> |
> |
> |
>
a problem: if I assigned column-level security through Enterprise Manager it
seemed to work fine, but after reopening the permission dialog box the
permissions on some columns disappeared.
After doing some research I can now simulate the behaviour when running the
following script. In Enterprise manager the security on column 'Col3' does
not show after running the script, though it is there.
CREATE TABLE test
(
Col1 int NOT NULL,
Col2 nvarchar(50) NOT NULL,
Col3 int NOT NULL
)
GO
ALTER TABLE test
DROP COLUMN Col2
GO
GRANT SELECT ON test(Col3) TO public
GO
/* Correct: returns select permission on Col3 */
sp_helprotect 'test'
GO
/* Bug! Does not return anything. */
/* Problem is that sp_MSobjectprivs uses count(*) on syscolumns instead of
max(colid) to determine maximum column number */
sp_MSobjectprivs N'test','column',null,null,null,null,0,1
GO
Enterprise Manager seems to use the sp_MSobjectprivs procedure to retrieve
the column permissions, but that one fails if columns are not numbered
consecutively (as caused by the DROP COLUMN).
Stefan Cuypers.
Dear Stefan,
Thank you for your feedback. I have reproduced the problem. Please rest
assured that we do understand your concerns and that the known issue is
being worked in development with priority after we report it via our
internal channel. We strive to capture any and all product issues / product
feedback so as to ensure that we are continuously developing Microsoft
products to meet customer needs. This is exactly why feedback such as
yours is always taken very seriously.
While we also hope to see the issue fixed as soon as possible, we have
re-confirmed with the development team that there are no better workarounds
at this time. If there are any updates on the issue, I will do my best to
be the first one to let you know.
Once again, thank you for your time and feedback.
Sincerely,
Bill Cheng
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
| From: "Stefan Cuypers" <stefan.cuypers@.nospam.nospam>
| Subject: Problem with viewing column permissions in Enterprise Manager
| Date: Fri, 2 Jul 2004 16:15:01 +0200
| Lines: 35
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.3790.0
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.132
| Message-ID: <#kQW57DYEHA.2908@.TK2MSFTNGP10.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.tools
| NNTP-Posting-Host: d576ae10.kabel.telenet.be 213.118.174.16
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP10.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.tools:24406
| X-Tomcat-NG: microsoft.public.sqlserver.tools
|
| When trying to configure SQL Server security at the column level I ran
into
| a problem: if I assigned column-level security through Enterprise Manager
it
| seemed to work fine, but after reopening the permission dialog box the
| permissions on some columns disappeared.
| After doing some research I can now simulate the behaviour when running
the
| following script. In Enterprise manager the security on column 'Col3' does
| not show after running the script, though it is there.
| CREATE TABLE test
| (
| Col1 int NOT NULL,
| Col2 nvarchar(50) NOT NULL,
| Col3 int NOT NULL
| )
| GO
| ALTER TABLE test
| DROP COLUMN Col2
| GO
| GRANT SELECT ON test(Col3) TO public
| GO
| /* Correct: returns select permission on Col3 */
| sp_helprotect 'test'
| GO
| /* Bug! Does not return anything. */
| /* Problem is that sp_MSobjectprivs uses count(*) on syscolumns instead of
| max(colid) to determine maximum column number */
| sp_MSobjectprivs N'test','column',null,null,null,null,0,1
| GO
|
| Enterprise Manager seems to use the sp_MSobjectprivs procedure to retrieve
| the column permissions, but that one fails if columns are not numbered
| consecutively (as caused by the DROP COLUMN).
|
| Stefan Cuypers.
|
|
|
|||Bill,
Thanks for the feedback. I'm glad you take it seriously and will try to fix
it in a next update.
Stefan.
""Bill Cheng"" <billchng@.online.microsoft.com> wrote in message
news:jNqMXDOYEHA.2352@.cpmsftngxa06.phx.gbl...
> Dear Stefan,
> Thank you for your feedback. I have reproduced the problem. Please rest
> assured that we do understand your concerns and that the known issue is
> being worked in development with priority after we report it via our
> internal channel. We strive to capture any and all product issues /
product
> feedback so as to ensure that we are continuously developing Microsoft
> products to meet customer needs. This is exactly why feedback such as
> yours is always taken very seriously.
> While we also hope to see the issue fixed as soon as possible, we have
> re-confirmed with the development team that there are no better
workarounds
> at this time. If there are any updates on the issue, I will do my best to
> be the first one to let you know.
> Once again, thank you for your time and feedback.
> Sincerely,
> Bill Cheng
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> --
> | From: "Stefan Cuypers" <stefan.cuypers@.nospam.nospam>
> | Subject: Problem with viewing column permissions in Enterprise Manager
> | Date: Fri, 2 Jul 2004 16:15:01 +0200
> | Lines: 35
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.3790.0
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.132
> | Message-ID: <#kQW57DYEHA.2908@.TK2MSFTNGP10.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.tools
> | NNTP-Posting-Host: d576ae10.kabel.telenet.be 213.118.174.16
> | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP10.phx.gbl
> | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.tools:24406
> | X-Tomcat-NG: microsoft.public.sqlserver.tools
> |
> | When trying to configure SQL Server security at the column level I ran
> into
> | a problem: if I assigned column-level security through Enterprise
Manager
> it
> | seemed to work fine, but after reopening the permission dialog box the
> | permissions on some columns disappeared.
> | After doing some research I can now simulate the behaviour when running
> the
> | following script. In Enterprise manager the security on column 'Col3'
does
> | not show after running the script, though it is there.
> | CREATE TABLE test
> | (
> | Col1 int NOT NULL,
> | Col2 nvarchar(50) NOT NULL,
> | Col3 int NOT NULL
> | )
> | GO
> | ALTER TABLE test
> | DROP COLUMN Col2
> | GO
> | GRANT SELECT ON test(Col3) TO public
> | GO
> | /* Correct: returns select permission on Col3 */
> | sp_helprotect 'test'
> | GO
> | /* Bug! Does not return anything. */
> | /* Problem is that sp_MSobjectprivs uses count(*) on syscolumns instead
of
> | max(colid) to determine maximum column number */
> | sp_MSobjectprivs N'test','column',null,null,null,null,0,1
> | GO
> |
> | Enterprise Manager seems to use the sp_MSobjectprivs procedure to
retrieve
> | the column permissions, but that one fails if columns are not numbered
> | consecutively (as caused by the DROP COLUMN).
> |
> | Stefan Cuypers.
> |
> |
> |
>
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
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'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...[vbcol=seagreen]
them reference some other[vbcol=seagreen]
I excluded the condition,[vbcol=seagreen]
tried different ways but the[vbcol=seagreen]
problem.[vbcol=seagreen]
result.[vbcol=seagreen]
like that?
>
|||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[vbcol=seagreen]
> 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:
them[vbcol=seagreen]
this[vbcol=seagreen]
I[vbcol=seagreen]
more[vbcol=seagreen]
problem.[vbcol=seagreen]
result.[vbcol=seagreen]
|||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 any of the view
> news:%23w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
> them reference some other
> I excluded the condition,
> tried different ways but the
> problem.
> result.
> like that?
>
|||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...[vbcol=seagreen]
in[vbcol=seagreen]
joins[vbcol=seagreen]
of[vbcol=seagreen]
When[vbcol=seagreen]
idea/experience
>
|||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[vbcol=seagreen]
> message news:uqA6jYP8EHA.3124@.TK2MSFTNGP11.phx.gbl...
> news:eib86hO8EHA.3756@.TK2MSFTNGP14.phx.gbl...
wrote[vbcol=seagreen]
> in
> joins
each[vbcol=seagreen]
> of
> When
I
> idea/experience
>
|||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[vbcol=seagreen]
> 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:
them[vbcol=seagreen]
this[vbcol=seagreen]
I[vbcol=seagreen]
more[vbcol=seagreen]
problem.[vbcol=seagreen]
result.[vbcol=seagreen]
|||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
>
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'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...[vbcol=seagreen]
them reference some other[vbcol=seagreen]
I excluded the condition,[vbcol=seagreen]
tried different ways but the[vbcol=seagreen]
problem.[vbcol=seagreen]
result.[vbcol=seagreen]
like that?
>
|||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[vbcol=seagreen]
> 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:
them[vbcol=seagreen]
this[vbcol=seagreen]
I[vbcol=seagreen]
more[vbcol=seagreen]
problem.[vbcol=seagreen]
result.[vbcol=seagreen]
|||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 any of the view
> news:%23w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
> them reference some other
> I excluded the condition,
> tried different ways but the
> problem.
> result.
> like that?
>
|||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...[vbcol=seagreen]
in[vbcol=seagreen]
joins[vbcol=seagreen]
of[vbcol=seagreen]
When[vbcol=seagreen]
idea/experience
>
|||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[vbcol=seagreen]
> message news:uqA6jYP8EHA.3124@.TK2MSFTNGP11.phx.gbl...
> news:eib86hO8EHA.3756@.TK2MSFTNGP14.phx.gbl...
wrote[vbcol=seagreen]
> in
> joins
each[vbcol=seagreen]
> of
> When
I
> idea/experience
>
|||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[vbcol=seagreen]
> 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:
them[vbcol=seagreen]
this[vbcol=seagreen]
I[vbcol=seagreen]
more[vbcol=seagreen]
problem.[vbcol=seagreen]
result.[vbcol=seagreen]
|||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
>
Problem with view
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.
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?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 th
e
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 th
em
> 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 th
em 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 l
ike 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...
them reference some other[vbcol=seagreen]
I excluded the condition,[vbcol=seagreen]
tried different ways but the[vbcol=seagreen]
problem.[vbcol=seagreen]
result.[vbcol=seagreen]
like that?[vbcol=seagreen]
>|||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[vbcol=seagreen]
> 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:
>
them[vbcol=seagreen]
this[vbcol=seagreen]
I[vbcol=seagreen]
more[vbcol=seagreen]
problem.[vbcol=seagreen]
result.[vbcol=seagreen]|||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...[v
bcol=seagreen]
> 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 i
n
> message news:#HRwpeM8EHA.1404@.TK2MSFTNGP11.phx.gbl...
> in any of the view
> news:%23w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
> them reference some other
> I excluded the condition,
> tried different ways but the
> problem.
> result.
> like that?
>[/vbcol]|||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...
in[vbcol=seagreen]
joins[vbcol=seagreen]
of[vbcol=seagreen]
When[vbcol=seagreen]
idea/experience[vbcol=seagreen]
>|||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...
> news:eib86hO8EHA.3756@.TK2MSFTNGP14.phx.gbl...
wrote[vbcol=seagreen]
> in
> joins
each[vbcol=seagreen]
> of
> When
I[vbcol=seagreen]
> idea/experience
>|||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[vbcol=seagreen]
> 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:
>
them[vbcol=seagreen]
this[vbcol=seagreen]
I[vbcol=seagreen]
more[vbcol=seagreen]
problem.[vbcol=seagreen]
result.[vbcol=seagreen]|||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
>
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 th
e
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 th
em
> 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 th
em 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 l
ike 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...
them reference some other[vbcol=seagreen]
I excluded the condition,[vbcol=seagreen]
tried different ways but the[vbcol=seagreen]
problem.[vbcol=seagreen]
result.[vbcol=seagreen]
like that?[vbcol=seagreen]
>|||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[vbcol=seagreen]
> 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:
>
them[vbcol=seagreen]
this[vbcol=seagreen]
I[vbcol=seagreen]
more[vbcol=seagreen]
problem.[vbcol=seagreen]
result.[vbcol=seagreen]|||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...[v
bcol=seagreen]
> 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 i
n
> message news:#HRwpeM8EHA.1404@.TK2MSFTNGP11.phx.gbl...
> in any of the view
> news:%23w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
> them reference some other
> I excluded the condition,
> tried different ways but the
> problem.
> result.
> like that?
>[/vbcol]|||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...
in[vbcol=seagreen]
joins[vbcol=seagreen]
of[vbcol=seagreen]
When[vbcol=seagreen]
idea/experience[vbcol=seagreen]
>|||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...
> news:eib86hO8EHA.3756@.TK2MSFTNGP14.phx.gbl...
wrote[vbcol=seagreen]
> in
> joins
each[vbcol=seagreen]
> of
> When
I[vbcol=seagreen]
> idea/experience
>|||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[vbcol=seagreen]
> 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:
>
them[vbcol=seagreen]
this[vbcol=seagreen]
I[vbcol=seagreen]
more[vbcol=seagreen]
problem.[vbcol=seagreen]
result.[vbcol=seagreen]|||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
>
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
>
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
>
Subscribe to:
Posts (Atom)