filter sqldatasource: view

Showing posts with label view. Show all posts

Wednesday, March 28, 2012

How to format a datetime field?

Hi,
I have a datetime field SDate with a value '1/1/02'. I want to display it
as 01/01/2002 in a view. So I use this statement:
SELECT CONVERT(datetime, SDate,101) from Table1.
But it still display it as 1/1/02. The Help says 101 will display yyyy if I
use it with CONVERT.
Thanks.declare @.t datetime
set @.t=getdate()
select convert(char(10),@.t,101)
"Chrissi" <anubisofthydeath@.hotmail.com> wrote in message
news:OYpFl5yUFHA.548@.tk2msftngp13.phx.gbl...
> Hi,
> I have a datetime field SDate with a value '1/1/02'. I want to display it
> as 01/01/2002 in a view. So I use this statement:
> SELECT CONVERT(datetime, SDate,101) from Table1.
> But it still display it as 1/1/02. The Help says 101 will display yyyy if
> I use it with CONVERT.
> Thanks.
>
>|||Thanks a lot. I replaced datetime with char(10) and it works.
"Farmer" <someone@.somewhere.com> wrote in message
news:eRMn27yUFHA.548@.tk2msftngp13.phx.gbl...
> declare @.t datetime
> set @.t=getdate()
> select convert(char(10),@.t,101)
>
> "Chrissi" <anubisofthydeath@.hotmail.com> wrote in message
> news:OYpFl5yUFHA.548@.tk2msftngp13.phx.gbl...
>|||Note of course that when you format it this way that it is no longer a date
value, it is a character value. May not be a problem for you, but it could
be confusing in how it gets used by a client program. and if you want to
sort by it, since it will sort on month first (assuming you are American!)
then day, then year.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Chrissi" <anubisofthydeath@.hotmail.com> wrote in message
news:%23XAycCzUFHA.1552@.TK2MSFTNGP10.phx.gbl...
> Thanks a lot. I replaced datetime with char(10) and it works.
> "Farmer" <someone@.somewhere.com> wrote in message
> news:eRMn27yUFHA.548@.tk2msftngp13.phx.gbl...
>|||I think the best way, on the backend and on the client application is to
store the value in ISO format, so you need not mess up with cutting the
time, formatting from one pattern to another.
Just my two pence and experience within projects.
Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> schrieb im Newsbeitrag
news:OihPLOzUFHA.3176@.TK2MSFTNGP12.phx.gbl...
> Note of course that when you format it this way that it is no longer a
> date value, it is a character value. May not be a problem for you, but it
> could be confusing in how it gets used by a client program. and if you
> want to sort by it, since it will sort on month first (assuming you are
> American!) then day, then year.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
> "Chrissi" <anubisofthydeath@.hotmail.com> wrote in message
> news:%23XAycCzUFHA.1552@.TK2MSFTNGP10.phx.gbl...
>

How to force View to change automatically when table schema changes?

I am still having problem with making View automatically updates itself when the underlying table schema changes. Running sp_recompile on the view table doesn't seem to work either, as I am still getting old format from the view (in Design mode the view returns the right info, but not when I open the View by doing Open View) even though the underlying schema has changed. Right now I find that I have to go into the View and change it a bit to force a recompilation.

And even if sp_recompile does, it would require that I manually do it each time I change a table. Any idea?Is your view an indexed view?|||Originally posted by sbaru
Is your view an indexed view?

Nope, it's not an indexed view, the view joins a few tables so it's not eligible for that.

Another strange thing is that if I select Open View the results is still from the old view, but if I do Design View the output from running the SQL is correct (not the same as what I saw when I did Open View). It's only when I deliberately change a field or two in the view in Design mode will the Open View gives me the correct output. That's why I am thinking the View is still using the old, compiled execution plan until I changed something in the View. That is a pain though since I am constanly change table schema.|||You should run :

--First option
EXEC sp_refreshview @.ViewName

--Secnod option

CREATE PROCEDURE REFRESH_ALL_VIEWS
AS
DECLARE @.ViewName varchar(100)
DECLARE curViews CURSOR FOR select name from sysobjects where xtype='V'
OPEN curViews
FETCH NEXT FROM curViews INTO @.ViewName
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC sp_refreshview @.ViewName
FETCH NEXT FROM curViews INTO @.ViewName
END
CLOSE curViews
DEALLOCATE curViews

GO|||Beautiful! Thank you!

How to force linked rpt to open in new window?

This seems like such a simple thing, I must be missing something obvious. I have linked reports to view detail in my humongous main report, and I want them to open in their own windows for the sake of speed of navigation. How do I force a new window in Jump to URL?

Here you go: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_soapapi_dev_3i49.asp

|||

Thanks, I appreciate your answer, but I'm still left trying to figure out WHERE to put the html code. It would make sense that it should go in the main report's RDL, but the code for this hyperlink looks like this:

<ReportName>BusDev_Detail</ReportName>

<Value>=Fields!tkinit.Value</Value>

</Parameter>

<Value>=Parameters!Begindate.Value</Value>

</Parameter>

<Value>=Parameters!Enddate.Value</Value>

</Parameter>

</Parameters>

</Drillthrough>

</Action>

So the <LinkTarget=blank> tag doesn't seem to go here. Can you point me in the right direction?

Monday, March 26, 2012

how to fixed the size of the report and landscape view state?

Good day.

How to make or fixed the size of the templates? and how to make the report in landscape mode? i have make a layout in the landscape mode but the priblem in print preview it is not in the landscape mode.

thanks your help is greatly appreciated

Check the width and height property of the "Report" and "Page". Both should be proper, for your report to print (preview) in landscape.|||

thanks rajeeb

but there's a way to make it fixed landscape view. Coz when previewing in the Web form its ok coz the layout of the report is in the landscape but when you export it example in the PDF file it will not set in landscape mode.

thanks

|||

If your widht and height property have proper values and you can view the report in landscape mode, then the export to pdf will also not be a problem.

I have used it, no issues, though I export to PDF using Render method of the ReportViewer control, which is same as what you are trying to do.

Friday, March 23, 2012

How to find what views a table is used

I want to find all views using a particular table name.
Also, I want to find all tables in a view and similarly I want to check
which stored procs use a particular table or view.
Can you please let me know how I can find this.
Thanks
KarenQuickest and easiest but not necessarily 100% reliable is to query
SYSCOMMENTS:
SELECT DISTINCT OBJECT_NAME(id)
FROM syscomments
WHERE text LIKE '%object_name%' ;
Better is to search the source code in your source control system. I'm
assuming you do have source control. You certainly ought to have if you do
any kind of SQL development.
--
David Portas
SQL Server MVP
--
<karenmiddleol@.yahoo.com> wrote in message
news:1128990708.992145.32340@.g47g2000cwa.googlegroups.com...
>I want to find all views using a particular table name.
> Also, I want to find all tables in a view and similarly I want to check
> which stored procs use a particular table or view.
> Can you please let me know how I can find this.
> Thanks
> Karen
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:89OdnaebcPHXktbenZ2dnUVZ8qmdnZ2d@.giganews.com...
> Quickest and easiest but not necessarily 100% reliable is to query
> SYSCOMMENTS:
> SELECT DISTINCT OBJECT_NAME(id)
> FROM syscomments
> WHERE text LIKE '%object_name%' ;
> Better is to search the source code in your source control system. I'm
> assuming you do have source control. You certainly ought to have if you do
> any kind of SQL development.
Wouldn't sysdepends be better?
Michael|||The easiest way is to use the INFORMATION Schema Views:
SELECT view_name
FROM INFORMATION_SCHEMA.View_table_usage
WHERE Table_name = 'SomeTableName'
HTH, Jens Suessmeyer.|||Sysdepends isn't totally reliable. It isn't always updated because of
deferred name resolution. I'd say that searching the source code should
be the most reliable method.
--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1129015487.553756.55240@.o13g2000cwo.googlegroups.com...
> Sysdepends isn't totally reliable. It isn't always updated because of
> deferred name resolution. I'd say that searching the source code should
> be the most reliable method.
Just drop and recreate the query and sysdepends will be accurate. I have a
routine that does this for every object in the database to make sysdepends
accurate throughout the database.
Michael

How to find what views a table is used

I want to find all views using a particular table name.
Also, I want to find all tables in a view and similarly I want to check
which stored procs use a particular table or view.
Can you please let me know how I can find this.
Thanks
Karen
Quickest and easiest but not necessarily 100% reliable is to query
SYSCOMMENTS:
SELECT DISTINCT OBJECT_NAME(id)
FROM syscomments
WHERE text LIKE '%object_name%' ;
Better is to search the source code in your source control system. I'm
assuming you do have source control. You certainly ought to have if you do
any kind of SQL development.
David Portas
SQL Server MVP
<karenmiddleol@.yahoo.com> wrote in message
news:1128990708.992145.32340@.g47g2000cwa.googlegro ups.com...
>I want to find all views using a particular table name.
> Also, I want to find all tables in a view and similarly I want to check
> which stored procs use a particular table or view.
> Can you please let me know how I can find this.
> Thanks
> Karen
>
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:89OdnaebcPHXktbenZ2dnUVZ8qmdnZ2d@.giganews.com ...
> Quickest and easiest but not necessarily 100% reliable is to query
> SYSCOMMENTS:
> SELECT DISTINCT OBJECT_NAME(id)
> FROM syscomments
> WHERE text LIKE '%object_name%' ;
> Better is to search the source code in your source control system. I'm
> assuming you do have source control. You certainly ought to have if you do
> any kind of SQL development.
Wouldn't sysdepends be better?
Michael
|||The easiest way is to use the INFORMATION Schema Views:
SELECT view_name
FROM INFORMATION_SCHEMA.View_table_usage
WHERE Table_name = 'SomeTableName'
HTH, Jens Suessmeyer.
|||Sysdepends isn't totally reliable. It isn't always updated because of
deferred name resolution. I'd say that searching the source code should
be the most reliable method.
David Portas
SQL Server MVP
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1129015487.553756.55240@.o13g2000cwo.googlegro ups.com...
> Sysdepends isn't totally reliable. It isn't always updated because of
> deferred name resolution. I'd say that searching the source code should
> be the most reliable method.
Just drop and recreate the query and sysdepends will be accurate. I have a
routine that does this for every object in the database to make sysdepends
accurate throughout the database.
Michael

How to find what views a table is used

I want to find all views using a particular table name.
Also, I want to find all tables in a view and similarly I want to check
which stored procs use a particular table or view.
Can you please let me know how I can find this.
Thanks
KarenQuickest and easiest but not necessarily 100% reliable is to query
SYSCOMMENTS:
SELECT DISTINCT OBJECT_NAME(id)
FROM syscomments
WHERE text LIKE '%object_name%' ;
Better is to search the source code in your source control system. I'm
assuming you do have source control. You certainly ought to have if you do
any kind of SQL development.
David Portas
SQL Server MVP
--
<karenmiddleol@.yahoo.com> wrote in message
news:1128990708.992145.32340@.g47g2000cwa.googlegroups.com...
>I want to find all views using a particular table name.
> Also, I want to find all tables in a view and similarly I want to check
> which stored procs use a particular table or view.
> Can you please let me know how I can find this.
> Thanks
> Karen
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:89OdnaebcPHXktbenZ2dnUVZ8qmdnZ2d@.gi
ganews.com...
> Quickest and easiest but not necessarily 100% reliable is to query
> SYSCOMMENTS:
> SELECT DISTINCT OBJECT_NAME(id)
> FROM syscomments
> WHERE text LIKE '%object_name%' ;
> Better is to search the source code in your source control system. I'm
> assuming you do have source control. You certainly ought to have if you do
> any kind of SQL development.
Wouldn't sysdepends be better?
Michael|||The easiest way is to use the INFORMATION Schema Views:
SELECT view_name
FROM INFORMATION_SCHEMA.View_table_usage
WHERE Table_name = 'SomeTableName'
HTH, Jens Suessmeyer.|||Sysdepends isn't totally reliable. It isn't always updated because of
deferred name resolution. I'd say that searching the source code should
be the most reliable method.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1129015487.553756.55240@.o13g2000cwo.googlegroups.com...
> Sysdepends isn't totally reliable. It isn't always updated because of
> deferred name resolution. I'd say that searching the source code should
> be the most reliable method.
Just drop and recreate the query and sysdepends will be accurate. I have a
routine that does this for every object in the database to make sysdepends
accurate throughout the database.
Michael

How to find what views a table is used

I want to find all views using a particular table name.
Also, I want to find all tables in a view and similarly I want to check
which stored procs use a particular table or view.
Can you please let me know how I can find this.
Thanks
KarenQuickest and easiest but not necessarily 100% reliable is to query
SYSCOMMENTS:
SELECT DISTINCT OBJECT_NAME(id)
FROM syscomments
WHERE text LIKE '%object_name%' ;
Better is to search the source code in your source control system. I'm
assuming you do have source control. You certainly ought to have if you do
any kind of SQL development.
David Portas
SQL Server MVP
--
<karenmiddleol@.yahoo.com> wrote in message
news:1128990708.992145.32340@.g47g2000cwa.googlegroups.com...
>I want to find all views using a particular table name.
> Also, I want to find all tables in a view and similarly I want to check
> which stored procs use a particular table or view.
> Can you please let me know how I can find this.
> Thanks
> Karen
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:89OdnaebcPHXktbenZ2dnUVZ8qmdnZ2d@.gi
ganews.com...
> Quickest and easiest but not necessarily 100% reliable is to query
> SYSCOMMENTS:
> SELECT DISTINCT OBJECT_NAME(id)
> FROM syscomments
> WHERE text LIKE '%object_name%' ;
> Better is to search the source code in your source control system. I'm
> assuming you do have source control. You certainly ought to have if you do
> any kind of SQL development.
Wouldn't sysdepends be better?
Michael|||The easiest way is to use the INFORMATION Schema Views:
SELECT view_name
FROM INFORMATION_SCHEMA.View_table_usage
WHERE Table_name = 'SomeTableName'
HTH, Jens Suessmeyer.|||Sysdepends isn't totally reliable. It isn't always updated because of
deferred name resolution. I'd say that searching the source code should
be the most reliable method.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1129015487.553756.55240@.o13g2000cwo.googlegroups.com...
> Sysdepends isn't totally reliable. It isn't always updated because of
> deferred name resolution. I'd say that searching the source code should
> be the most reliable method.
Just drop and recreate the query and sysdepends will be accurate. I have a
routine that does this for every object in the database to make sysdepends
accurate throughout the database.
Michael

Monday, March 19, 2012

How to find Server Collation from SQL 2005 tables/views?

Does anyone know what table, view, etc., contains the SQL Server 2005 server
collation (not database collation) value? When I use SQLDMO it returns an
empty string, yet when I view the SQL Server 2005 instance properties in SQL
Server Management Studio, it correctly shows as SQL_Latin... etc.
I have tried querying the master.sys.sysservers view and msdb.sys.sysservers
view but they both report null as well.
Where can I find the Server Collation property programmatically in SQL 2005?
Thanks experts!Perhaps you are looking for
serverproperty('collation')
Ben Nevarez, MCDBA, OCP
Database Administrator
"Mark Findlay" wrote:

> Does anyone know what table, view, etc., contains the SQL Server 2005 serv
er
> collation (not database collation) value? When I use SQLDMO it returns an
> empty string, yet when I view the SQL Server 2005 instance properties in S
QL
> Server Management Studio, it correctly shows as SQL_Latin... etc.
> I have tried querying the master.sys.sysservers view and msdb.sys.sysserve
rs
> view but they both report null as well.
> Where can I find the Server Collation property programmatically in SQL 200
5?
> Thanks experts!
>|||Perfect! Thanks!
Mark
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:715E7532-7B21-4015-94F7-72237EF02FFA@.microsoft.com...
> Perhaps you are looking for
> serverproperty('collation')
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Mark Findlay" wrote:
>

Wednesday, March 7, 2012

How to find key property without using functions or procedure?

I make simple application for view main properties of selected database (using systables). I have to find relation between systables that lets me find property of each key, without using functions or procedures. For example this SQL code:

*******example1

SELECT dbo.sysobjects.name, dbo.sysindexes.name AS IndexName, dbo.syscolumns.name AS ComponentName, dbo.syscolumns.length,
dbo.systypes.name AS Type
FROM dbo.systypes RIGHT OUTER JOIN
dbo.syscolumns ON dbo.systypes.xusertype = dbo.syscolumns.xusertype RIGHT OUTER JOIN
dbo.sysindexkeys INNER JOIN
dbo.sysobjects ON dbo.sysindexkeys.id = dbo.sysobjects.id INNER JOIN
dbo.sysindexes ON dbo.sysindexkeys.indid = dbo.sysindexes.indid AND dbo.sysobjects.id = dbo.sysindexes.id ON
dbo.syscolumns.id = dbo.sysobjects.id AND dbo.syscolumns.colid = dbo.sysindexkeys.colid
WHERE (dbo.sysindexes.name NOT LIKE '_WA_%')
ORDER BY dbo.sysobjects.name, IndexName

*******

make view showing all keys components (columns that each key is made of). It works pretty good. But now I want find properties of each key (I mean is key ascending or descending). I can do it using simple function INDEXKEY_PROPERTY like that :

******example2

SELECT dbo.sysindexes.id, dbo.sysindexes.name AS Indexname, dbo.syscolumns.name AS Components, dbo.syscolumns.length,
dbo.systypes.name AS Type, (CASE WHEN INDEXKEY_PROPERTY(dbo.sysobjects.id, dbo.sysindexes.indid, dbo.sysindexkeys.keyno,
'IsDescending') = 0 THEN 'Ascending' ELSE 'Descending' END) AS SortProperty
FROM dbo.systypes RIGHT OUTER JOIN
dbo.syscolumns ON dbo.systypes.xusertype = dbo.syscolumns.xusertype RIGHT OUTER JOIN
dbo.sysindexkeys INNER JOIN
dbo.sysobjects ON dbo.sysindexkeys.id = dbo.sysobjects.id INNER JOIN
dbo.sysindexes ON dbo.sysindexkeys.indid = dbo.sysindexes.indid AND dbo.sysobjects.id = dbo.sysindexes.id ON
dbo.syscolumns.id = dbo.sysobjects.id AND dbo.syscolumns.colid = dbo.sysindexkeys.colid
WHERE (dbo.sysindexes.name NOT LIKE '_WA_%')
ORDER BY dbo.sysobjects.name, Indexname

*******

This is what I want but I have to do it without using function (like in exp.1) So I need to find good relation between some systables.

Please!!! Somebody help me!

Sorry about my english

I would like to know why you want to go in this route, as it is easy to manage the code with such functions in the release. Using system tables directly is not a good practice.|||

I work in Clarion, so I don't want to use SQL code at all. I just need information where in system tables can I find this, and then I can use Clarion interface to connect with matching table and get all information I need.

I can use SQL code, but it's real hardcore in Clarion. I have been askin' before how to find all components of index/key without functions and everybody suggest, that I should use function, but it's not so easy. I founded, how to do it my way. Now the same problem with key properties. Where is it? If you know something about it please help.

Friday, February 24, 2012

How to find binding errors to views

I have a view with no table present such as
Create table T1
(Col1 int)
go
Create view V1
as
select * from T1
go
drop table T1
go
select * from V1
I want to be able to run a query against a database that would give me all
the views that are inconsistent as above
I tried dbcc checkdb and dbcc checktable and it doesnt seem to work..I don't think there is a single way to get the list of all the views with
binding errors. Perhaps, one option is to execute sp_refreshview in a
cursor/loop which will error out.
If this is something you'd want to preserve for any future views, perhaps
you should consider using explicit column names in SELECT clauses, two part
naming in FROM clauses and WITH SCHEMABINDING option while creating the
view.
Anith|||I tried the cursor approach but the cursor aborts after it finds the first
violation. How can I let it continue ?
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eHi%23IVnjFHA.2444@.TK2MSFTNGP10.phx.gbl...
> I don't think there is a single way to get the list of all the views with
> binding errors. Perhaps, one option is to execute sp_refreshview in a
> cursor/loop which will error out.
> If this is something you'd want to preserve for any future views, perhaps
> you should consider using explicit column names in SELECT clauses, two
part
> naming in FROM clauses and WITH SCHEMABINDING option while creating the
> view.
> --
> Anith
>|||I do a build of the database using the source code using DB Ghost Database
Builder. That way all errors are quickly reported and can therefore be fixed
.
The builder builds objects at around 1000/minute making this a very quick an
d
extremely thorough process.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Hassan" wrote:

> I have a view with no table present such as
> Create table T1
> (Col1 int)
> go
> Create view V1
> as
> select * from T1
> go
> drop table T1
> go
> select * from V1
> I want to be able to run a query against a database that would give me all
> the views that are inconsistent as above
> I tried dbcc checkdb and dbcc checktable and it doesnt seem to work..
>
>

How to find binding errors to views

I have a view with no table present such as
Create table T1
(Col1 int)
go
Create view V1
as
select * from T1
go
drop table T1
go
select * from V1
I want to be able to run a query against a database that would give me all
the views that are inconsistent as above
I tried dbcc checkdb and dbcc checktable and it doesnt seem to work..
I don't think there is a single way to get the list of all the views with
binding errors. Perhaps, one option is to execute sp_refreshview in a
cursor/loop which will error out.
If this is something you'd want to preserve for any future views, perhaps
you should consider using explicit column names in SELECT clauses, two part
naming in FROM clauses and WITH SCHEMABINDING option while creating the
view.
Anith
|||I tried the cursor approach but the cursor aborts after it finds the first
violation. How can I let it continue ?
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eHi%23IVnjFHA.2444@.TK2MSFTNGP10.phx.gbl...
> I don't think there is a single way to get the list of all the views with
> binding errors. Perhaps, one option is to execute sp_refreshview in a
> cursor/loop which will error out.
> If this is something you'd want to preserve for any future views, perhaps
> you should consider using explicit column names in SELECT clauses, two
part
> naming in FROM clauses and WITH SCHEMABINDING option while creating the
> view.
> --
> Anith
>
|||I do a build of the database using the source code using DB Ghost Database
Builder. That way all errors are quickly reported and can therefore be fixed.
The builder builds objects at around 1000/minute making this a very quick and
extremely thorough process.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Hassan" wrote:

> I have a view with no table present such as
> Create table T1
> (Col1 int)
> go
> Create view V1
> as
> select * from T1
> go
> drop table T1
> go
> select * from V1
> I want to be able to run a query against a database that would give me all
> the views that are inconsistent as above
> I tried dbcc checkdb and dbcc checktable and it doesnt seem to work..
>
>

How to find binding errors to views

I have a view with no table present such as
Create table T1
(Col1 int)
go
Create view V1
as
select * from T1
go
drop table T1
go
select * from V1
I want to be able to run a query against a database that would give me all
the views that are inconsistent as above
I tried dbcc checkdb and dbcc checktable and it doesnt seem to work..I don't think there is a single way to get the list of all the views with
binding errors. Perhaps, one option is to execute sp_refreshview in a
cursor/loop which will error out.
If this is something you'd want to preserve for any future views, perhaps
you should consider using explicit column names in SELECT clauses, two part
naming in FROM clauses and WITH SCHEMABINDING option while creating the
view.
--
Anith|||I tried the cursor approach but the cursor aborts after it finds the first
violation. How can I let it continue ?
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eHi%23IVnjFHA.2444@.TK2MSFTNGP10.phx.gbl...
> I don't think there is a single way to get the list of all the views with
> binding errors. Perhaps, one option is to execute sp_refreshview in a
> cursor/loop which will error out.
> If this is something you'd want to preserve for any future views, perhaps
> you should consider using explicit column names in SELECT clauses, two
part
> naming in FROM clauses and WITH SCHEMABINDING option while creating the
> view.
> --
> Anith
>|||I do a build of the database using the source code using DB Ghost Database
Builder. That way all errors are quickly reported and can therefore be fixed.
The builder builds objects at around 1000/minute making this a very quick and
extremely thorough process.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Hassan" wrote:
> I have a view with no table present such as
> Create table T1
> (Col1 int)
> go
> Create view V1
> as
> select * from T1
> go
> drop table T1
> go
> select * from V1
> I want to be able to run a query against a database that would give me all
> the views that are inconsistent as above
> I tried dbcc checkdb and dbcc checktable and it doesnt seem to work..
>
>

Wednesday, March 28, 2012

How to format a datetime field?

How to force View to change automatically when table schema changes?

How to force linked rpt to open in new window?

Monday, March 26, 2012

how to fixed the size of the report and landscape view state?

Friday, March 23, 2012

How to find what views a table is used

How to find what views a table is used

How to find what views a table is used

How to find what views a table is used

Monday, March 19, 2012

How to find Server Collation from SQL 2005 tables/views?

Wednesday, March 7, 2012

How to find key property without using functions or procedure?

Friday, February 24, 2012

How to find binding errors to views

How to find binding errors to views

How to find binding errors to views

How to find binding errors to views

filter sqldatasource

Blog Archive

About Me