Help Backup: analyzer

Showing posts with label analyzer. Show all posts

Monday, March 12, 2012

Heterogeneous queries error

I'am attempting to modify an SMS 2003 web report to include data from a
linked SQL server. Running the query (report) in Query Analyzer works fine,
however, running the report via the web returns the error:
---
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be
set for the connection. This ensures consistent query semantics. Enable these
options and then reissue your query.
Error Number: -2147217900
Source: Microsoft OLE DB Provider for SQL Server
Native Error: 7405
---
Any ideas how to would fix this?Inside your proc, add SET ANSI_WARNINGS ON to the beginning of your code.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chad" <Chad@.discussions.microsoft.com> wrote in message
news:D892A9F1-4184-479E-822F-A6F1939F85A8@.microsoft.com...
> I'am attempting to modify an SMS 2003 web report to include data from a
> linked SQL server. Running the query (report) in Query Analyzer works fine,
> however, running the report via the web returns the error:
> ---
> Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be
> set for the connection. This ensures consistent query semantics. Enable these
> options and then reissue your query.
> Error Number: -2147217900
> Source: Microsoft OLE DB Provider for SQL Server
> Native Error: 7405
> ---
> Any ideas how to would fix this?|||I'm not using a stored procedure. SMS 2003 stores the SQL query in the
database as a text field, not as a stored procedure.
"Tibor Karaszi" wrote:
> Inside your proc, add SET ANSI_WARNINGS ON to the beginning of your code.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Chad" <Chad@.discussions.microsoft.com> wrote in message
> news:D892A9F1-4184-479E-822F-A6F1939F85A8@.microsoft.com...
> > I'am attempting to modify an SMS 2003 web report to include data from a
> > linked SQL server. Running the query (report) in Query Analyzer works fine,
> > however, running the report via the web returns the error:
> >
> > ---
> > Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be
> > set for the connection. This ensures consistent query semantics. Enable these
> > options and then reissue your query.
> >
> > Error Number: -2147217900
> > Source: Microsoft OLE DB Provider for SQL Server
> > Native Error: 7405
> > ---
> >
> > Any ideas how to would fix this?
>
>|||OK, let me re-phrase:
Preceding any other statement in your code, execute that SET command...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chad" <Chad@.discussions.microsoft.com> wrote in message
news:00AB7DD9-148F-4602-9E46-63C34CA3F576@.microsoft.com...
> I'm not using a stored procedure. SMS 2003 stores the SQL query in the
> database as a text field, not as a stored procedure.
> "Tibor Karaszi" wrote:
> > Inside your proc, add SET ANSI_WARNINGS ON to the beginning of your code.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Chad" <Chad@.discussions.microsoft.com> wrote in message
> > news:D892A9F1-4184-479E-822F-A6F1939F85A8@.microsoft.com...
> > > I'am attempting to modify an SMS 2003 web report to include data from a
> > > linked SQL server. Running the query (report) in Query Analyzer works fine,
> > > however, running the report via the web returns the error:
> > >
> > > ---
> > > Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be
> > > set for the connection. This ensures consistent query semantics. Enable these
> > > options and then reissue your query.
> > >
> > > Error Number: -2147217900
> > > Source: Microsoft OLE DB Provider for SQL Server
> > > Native Error: 7405
> > > ---
> > >
> > > Any ideas how to would fix this?
> >
> >
> >

Friday, March 9, 2012

HELP--cannot shrink the transaction log

Hello:
I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
analyzer in SQL 2000 against the master database.
What is the EXACT syntax? You see, the name of the database is JC and the
log file is named GPSJClog.ldf. I have tried to place both names in the
syntax of the statement but with no success. I keep getting a message saying
that the file name is not in sysfiles.
This is what I have tried:
DBCC SHRINKFILE (GPJClog.ldf , 2)
Please help!
childofthe1980sYou specify the *logical name of the file*. See the sysfiles table. Also, see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
news:268984E4-5D32-48E5-8438-7252EE44255A@.microsoft.com...
> Hello:
> I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
> analyzer in SQL 2000 against the master database.
> What is the EXACT syntax? You see, the name of the database is JC and the
> log file is named GPSJClog.ldf. I have tried to place both names in the
> syntax of the statement but with no success. I keep getting a message saying
> that the file name is not in sysfiles.
> This is what I have tried:
> DBCC SHRINKFILE (GPJClog.ldf , 2)
> Please help!
> childofthe1980s|||I have done both of those things--no success...
PLEASE HELP!!!
childofthe1980s
"Tibor Karaszi" wrote:
> You specify the *logical name of the file*. See the sysfiles table. Also, see
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
> news:268984E4-5D32-48E5-8438-7252EE44255A@.microsoft.com...
> > Hello:
> >
> > I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
> > analyzer in SQL 2000 against the master database.
> >
> > What is the EXACT syntax? You see, the name of the database is JC and the
> > log file is named GPSJClog.ldf. I have tried to place both names in the
> > syntax of the statement but with no success. I keep getting a message saying
> > that the file name is not in sysfiles.
> >
> > This is what I have tried:
> >
> > DBCC SHRINKFILE (GPJClog.ldf , 2)
> >
> > Please help!
> >
> > childofthe1980s
>|||Can you post the commands you tried to execute?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
news:C2E7133B-E6A1-4B09-A351-C242160C2FA3@.microsoft.com...
>I have done both of those things--no success...
> PLEASE HELP!!!
> childofthe1980s
> "Tibor Karaszi" wrote:
>> You specify the *logical name of the file*. See the sysfiles table. Also, see
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
>> news:268984E4-5D32-48E5-8438-7252EE44255A@.microsoft.com...
>> > Hello:
>> >
>> > I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
>> > analyzer in SQL 2000 against the master database.
>> >
>> > What is the EXACT syntax? You see, the name of the database is JC and the
>> > log file is named GPSJClog.ldf. I have tried to place both names in the
>> > syntax of the statement but with no success. I keep getting a message saying
>> > that the file name is not in sysfiles.
>> >
>> > This is what I have tried:
>> >
>> > DBCC SHRINKFILE (GPJClog.ldf , 2)
>> >
>> > Please help!
>> >
>> > childofthe1980s
>>|||DBCC SHRINKFILE ('GPSJCLog.ldf', 2)
"Tibor Karaszi" wrote:
> Can you post the commands you tried to execute?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
> news:C2E7133B-E6A1-4B09-A351-C242160C2FA3@.microsoft.com...
> >I have done both of those things--no success...
> >
> > PLEASE HELP!!!
> >
> > childofthe1980s
> >
> > "Tibor Karaszi" wrote:
> >
> >> You specify the *logical name of the file*. See the sysfiles table. Also, see
> >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
> >> news:268984E4-5D32-48E5-8438-7252EE44255A@.microsoft.com...
> >> > Hello:
> >> >
> >> > I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
> >> > analyzer in SQL 2000 against the master database.
> >> >
> >> > What is the EXACT syntax? You see, the name of the database is JC and the
> >> > log file is named GPSJClog.ldf. I have tried to place both names in the
> >> > syntax of the statement but with no success. I keep getting a message saying
> >> > that the file name is not in sysfiles.
> >> >
> >> > This is what I have tried:
> >> >
> >> > DBCC SHRINKFILE (GPJClog.ldf , 2)
> >> >
> >> > Please help!
> >> >
> >> > childofthe1980s
> >>
> >>
>|||Where you IN the database in question? I believe that is required.
Roy Harvey
Beacon Falls, CT
On Tue, 21 Nov 2006 14:04:01 -0800, childofthe1980s
<childofthe1980s@.discussions.microsoft.com> wrote:
>Hello:
>I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
>analyzer in SQL 2000 against the master database.
>What is the EXACT syntax? You see, the name of the database is JC and the
>log file is named GPSJClog.ldf. I have tried to place both names in the
>syntax of the statement but with no success. I keep getting a message saying
>that the file name is not in sysfiles.
>This is what I have tried:
>DBCC SHRINKFILE (GPJClog.ldf , 2)
>Please help!
>childofthe1980s|||Nevermind. I figured it out.
I had to run the following scripts against each of the databases:
BACKUP LOG JC with TRUNCATE_ONLY
DBCC SHRINKFILE (2, 20)
You see, the support documentation on the web did not say that you have to
take the fileID of the database--2 in the syntax above-- in order to make it
work! You get the number 2 from running the following query against the
database:
select * from sysfiles
It would have been nice if this had been in the documentation!
Thanks!
childofthe1980s
"Roy Harvey" wrote:
> Where you IN the database in question? I believe that is required.
> Roy Harvey
> Beacon Falls, CT
> On Tue, 21 Nov 2006 14:04:01 -0800, childofthe1980s
> <childofthe1980s@.discussions.microsoft.com> wrote:
> >Hello:
> >
> >I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
> >analyzer in SQL 2000 against the master database.
> >
> >What is the EXACT syntax? You see, the name of the database is JC and the
> >log file is named GPSJClog.ldf. I have tried to place both names in the
> >syntax of the statement but with no success. I keep getting a message saying
> >that the file name is not in sysfiles.
> >
> >This is what I have tried:
> >
> >DBCC SHRINKFILE (GPJClog.ldf , 2)
> >
> >Please help!
> >
> >childofthe1980s
>|||You needed to use the Logical name not the physical one. Drop the .ldf and
you should be golden.
--
Andrew J. Kelly SQL MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:C3E65A22-82FF-405A-8EC9-B770E5206E4E@.microsoft.com...
> DBCC SHRINKFILE ('GPSJCLog.ldf', 2)
>
> "Tibor Karaszi" wrote:
>> Can you post the commands you tried to execute?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
>> message
>> news:C2E7133B-E6A1-4B09-A351-C242160C2FA3@.microsoft.com...
>> >I have done both of those things--no success...
>> >
>> > PLEASE HELP!!!
>> >
>> > childofthe1980s
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> You specify the *logical name of the file*. See the sysfiles table.
>> >> Also, see
>> >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:268984E4-5D32-48E5-8438-7252EE44255A@.microsoft.com...
>> >> > Hello:
>> >> >
>> >> > I'm trying to shrink the transaction log by running DBCC SHRINKFILE
>> >> > in query
>> >> > analyzer in SQL 2000 against the master database.
>> >> >
>> >> > What is the EXACT syntax? You see, the name of the database is JC
>> >> > and the
>> >> > log file is named GPSJClog.ldf. I have tried to place both names in
>> >> > the
>> >> > syntax of the statement but with no success. I keep getting a
>> >> > message saying
>> >> > that the file name is not in sysfiles.
>> >> >
>> >> > This is what I have tried:
>> >> >
>> >> > DBCC SHRINKFILE (GPJClog.ldf , 2)
>> >> >
>> >> > Please help!
>> >> >
>> >> > childofthe1980s
>> >>
>> >>
>>|||Not true. You can use either the ID or the LOGICAL name not the physical
name. And hopefully the db is not in FULL recovery mode or you just lost the
log chain with that truncate.
--
Andrew J. Kelly SQL MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:F4F525D1-24BC-4482-A164-55B00F0336F6@.microsoft.com...
> Nevermind. I figured it out.
> I had to run the following scripts against each of the databases:
> BACKUP LOG JC with TRUNCATE_ONLY
> DBCC SHRINKFILE (2, 20)
> You see, the support documentation on the web did not say that you have to
> take the fileID of the database--2 in the syntax above-- in order to make
> it
> work! You get the number 2 from running the following query against the
> database:
> select * from sysfiles
> It would have been nice if this had been in the documentation!
> Thanks!
> childofthe1980s
>
>
> "Roy Harvey" wrote:
>> Where you IN the database in question? I believe that is required.
>> Roy Harvey
>> Beacon Falls, CT
>> On Tue, 21 Nov 2006 14:04:01 -0800, childofthe1980s
>> <childofthe1980s@.discussions.microsoft.com> wrote:
>> >Hello:
>> >
>> >I'm trying to shrink the transaction log by running DBCC SHRINKFILE in
>> >query
>> >analyzer in SQL 2000 against the master database.
>> >
>> >What is the EXACT syntax? You see, the name of the database is JC and
>> >the
>> >log file is named GPSJClog.ldf. I have tried to place both names in the
>> >syntax of the statement but with no success. I keep getting a message
>> >saying
>> >that the file name is not in sysfiles.
>> >
>> >This is what I have tried:
>> >
>> >DBCC SHRINKFILE (GPJClog.ldf , 2)
>> >
>> >Please help!
>> >
>> >childofthe1980s|||No, Andrew, I tried using the logical file name without the ldf and it did
not work either. So, yes, the documentation needs updating.
"Andrew J. Kelly" wrote:
> Not true. You can use either the ID or the LOGICAL name not the physical
> name. And hopefully the db is not in FULL recovery mode or you just lost the
> log chain with that truncate.
> --
> Andrew J. Kelly SQL MVP
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
> message news:F4F525D1-24BC-4482-A164-55B00F0336F6@.microsoft.com...
> > Nevermind. I figured it out.
> >
> > I had to run the following scripts against each of the databases:
> >
> > BACKUP LOG JC with TRUNCATE_ONLY
> > DBCC SHRINKFILE (2, 20)
> >
> > You see, the support documentation on the web did not say that you have to
> > take the fileID of the database--2 in the syntax above-- in order to make
> > it
> > work! You get the number 2 from running the following query against the
> > database:
> >
> > select * from sysfiles
> >
> > It would have been nice if this had been in the documentation!
> >
> > Thanks!
> >
> > childofthe1980s
> >
> >
> >
> >
> >
> > "Roy Harvey" wrote:
> >
> >> Where you IN the database in question? I believe that is required.
> >>
> >> Roy Harvey
> >> Beacon Falls, CT
> >>
> >> On Tue, 21 Nov 2006 14:04:01 -0800, childofthe1980s
> >> <childofthe1980s@.discussions.microsoft.com> wrote:
> >>
> >> >Hello:
> >> >
> >> >I'm trying to shrink the transaction log by running DBCC SHRINKFILE in
> >> >query
> >> >analyzer in SQL 2000 against the master database.
> >> >
> >> >What is the EXACT syntax? You see, the name of the database is JC and
> >> >the
> >> >log file is named GPSJClog.ldf. I have tried to place both names in the
> >> >syntax of the statement but with no success. I keep getting a message
> >> >saying
> >> >that the file name is not in sysfiles.
> >> >
> >> >This is what I have tried:
> >> >
> >> >DBCC SHRINKFILE (GPJClog.ldf , 2)
> >> >
> >> >Please help!
> >> >
> >> >childofthe1980s
> >>
>
>|||> No, Andrew, I tried using the logical file name without the ldf and it did
> not work either. So, yes, the documentation needs updating.
The documentation clearly states that you can use *either* the logical filename or the file id. The
documentation is correct. The command you posted had 'GPSJCLog.ldf' for the logical filename, and I
guess that you by mistake put the *physical* filename there instead of the *logical* filename. If
you show ut the output from below, we can say for sure:
SELECT name, filename FROM sysfiles
I have shrunk database files occasionally and I always used the logical filename. In addition,
here's a script (executed on my SQL Server 2000) which clearly show the usage of the *logical*
filename:
USE master
CREATE DATABASE myTestShrink ON PRIMARY
(NAME = N'myTestShrink', FILENAME = N'C:\myTestShrink.mdf'
,SIZE = 1024KB , FILEGROWTH = 10%)
LOG ON
(NAME = N'myTestShrink_log', FILENAME = N'C:\myTestShrink_log.ldf'
, SIZE = 1024KB , FILEGROWTH = 10%)
GO
--Increase log file size
BACKUP DATABASE myTestShrink TO DISK = 'nul'
CREATE TABLE myTestShrink..test(c1 int identity, c2 char(2000))
INSERT INTO myTestShrink..test(c2)
SELECT TOP 10000 'hello' FROM sysobjects, syscolumns
GO
--Check log file size
SELECT name, size*8192/(1024*1024) AS SizeInMb FROM myTestShrink..sysfiles WHERE groupid = 0
--Empty the log
BACKUP LOG myTestShrink WITH NO_LOG
--Check the logical and physical files names
SELECT name, filename FROM myTestShrink..sysfiles
--Perform the shrink, ***based on logical filename***
USE myTestShrink
GO
DBCC SHRINKFILE('myTestShrink_log', 2)
--Check log file size
SELECT name, size*8192/(1024*1024) AS SizeInMb FROM myTestShrink..sysfiles WHERE groupid = 0
USE master
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
news:04A6393F-154E-4A1A-946B-0768F7B47F85@.microsoft.com...
> No, Andrew, I tried using the logical file name without the ldf and it did
> not work either. So, yes, the documentation needs updating.
> "Andrew J. Kelly" wrote:
>> Not true. You can use either the ID or the LOGICAL name not the physical
>> name. And hopefully the db is not in FULL recovery mode or you just lost the
>> log chain with that truncate.
>> --
>> Andrew J. Kelly SQL MVP
>> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
>> message news:F4F525D1-24BC-4482-A164-55B00F0336F6@.microsoft.com...
>> > Nevermind. I figured it out.
>> >
>> > I had to run the following scripts against each of the databases:
>> >
>> > BACKUP LOG JC with TRUNCATE_ONLY
>> > DBCC SHRINKFILE (2, 20)
>> >
>> > You see, the support documentation on the web did not say that you have to
>> > take the fileID of the database--2 in the syntax above-- in order to make
>> > it
>> > work! You get the number 2 from running the following query against the
>> > database:
>> >
>> > select * from sysfiles
>> >
>> > It would have been nice if this had been in the documentation!
>> >
>> > Thanks!
>> >
>> > childofthe1980s
>> >
>> >
>> >
>> >
>> >
>> > "Roy Harvey" wrote:
>> >
>> >> Where you IN the database in question? I believe that is required.
>> >>
>> >> Roy Harvey
>> >> Beacon Falls, CT
>> >>
>> >> On Tue, 21 Nov 2006 14:04:01 -0800, childofthe1980s
>> >> <childofthe1980s@.discussions.microsoft.com> wrote:
>> >>
>> >> >Hello:
>> >> >
>> >> >I'm trying to shrink the transaction log by running DBCC SHRINKFILE in
>> >> >query
>> >> >analyzer in SQL 2000 against the master database.
>> >> >
>> >> >What is the EXACT syntax? You see, the name of the database is JC and
>> >> >the
>> >> >log file is named GPSJClog.ldf. I have tried to place both names in the
>> >> >syntax of the statement but with no success. I keep getting a message
>> >> >saying
>> >> >that the file name is not in sysfiles.
>> >> >
>> >> >This is what I have tried:
>> >> >
>> >> >DBCC SHRINKFILE (GPJClog.ldf , 2)
>> >> >
>> >> >Please help!
>> >> >
>> >> >childofthe1980s
>> >>
>>

Monday, February 27, 2012

HELP: sp_help and object browser report view column sizes differently

Hi,

I've run into a curious problem with MS SQL Server 8.0. Using sp_help and
SQL Query Analyzer's object browser to view the columns returned by a view,
I find that sp_help is reporting stale information.

In a recent schema change, for example, someone lengthened a varchar column
from 15 to 50 characters. If we use sp_help to find out about a view that
depends upon this column, it still shows up as VARCHAR(15), whereas the
object browser correctly reports it as VARCHAR(50).

Dropping and recreating the view fixes the problem, but we have quite a few
views, and dropping and re-creating all of them any time a schema change is
made is something we want to avoid. I tried using DBCC CHECKDB in hopes that
it would 'refresh' SQL Server's information, but no luck.

(if you're curious as to why I don't just use the object browser instead,
read boring technical details below)

Has anyone seen this before? Is there some other way (other than
re-creating every view) to tell SQL Server to "refresh" it's information?

Thanks!

-Scott

-------
Boring Technical Information:

The reason this is an issue for us (i.e., I can't just use the object
browser instead) is that our object model classes are built using standard
metadata query methods in Java that seem to be returning the same stale
information that sp_help is returning. These methods are a part of the
standard JDK, so we can't easily fiddle with them. Anyway, as a result, our
object model (at least with respect to views) may not match our current
schema!A view need to expose its columns and each columns datatypes in the system
tables, just like a table. However, in SQL Server, this information is not
refreshed when you modify an underlying object (like ALTER TABLE). This is
why sp_help will show you the old information, it picks it up from
syscolumns. Repro below:
USE tempdb
GO
DROP VIEW v
GO
DROP TABLE t
GO
CREATE TABLE t(c1 varchar(10))
GO
CREATE VIEW v AS SELECT c1 FROM t
GO
EXEC sp_help v
GO
ALTER TABLE t ALTER COLUMN c1 VARCHAR(20)
GO
EXEC sp_help v -- Here, the info is still old
EXEC sp_refreshview v
EXEC sp_help v

Note that you can use sp_refreshview to refresh the view definition.

QA's object browser doesn't pick up the meta-data from syscolumns, that is
why it can show current information. Here's what QA seems to be doing to
pick up the meta-data info:

declare @.P1 int
set @.P1=1
exec sp_prepare @.P1 output, NULL, N'SELECT * FROM [tempdb].[dbo].[v]', 1
select @.P1
exec sp_unprepare 1
--
Tibor Karaszi

sp_refreshview - Refreshes the metadata for the specified view. Persistent
metadata for a view can become outdated because of changes to the underlying
objects upon which the view depends.

exec sp_refreshview 'viewname'

--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net

"ScottyBaby" <scottseansmith2NO@.SPAM.hotmail.com> wrote in message
news:OOWdnchUCvygmzWiRTvUrg@.texas.net...
> Hi,
> I've run into a curious problem with MS SQL Server 8.0. Using sp_help and
> SQL Query Analyzer's object browser to view the columns returned by a
view,
> I find that sp_help is reporting stale information.
> In a recent schema change, for example, someone lengthened a varchar
column
> from 15 to 50 characters. If we use sp_help to find out about a view that
> depends upon this column, it still shows up as VARCHAR(15), whereas the
> object browser correctly reports it as VARCHAR(50).
> Dropping and recreating the view fixes the problem, but we have quite a
few
> views, and dropping and re-creating all of them any time a schema change
is
> made is something we want to avoid. I tried using DBCC CHECKDB in hopes
that
> it would 'refresh' SQL Server's information, but no luck.
> (if you're curious as to why I don't just use the object browser instead,
> read boring technical details below)
> Has anyone seen this before? Is there some other way (other than
> re-creating every view) to tell SQL Server to "refresh" it's information?
> Thanks!
> -Scott
> -------
> Boring Technical Information:
> The reason this is an issue for us (i.e., I can't just use the object
> browser instead) is that our object model classes are built using standard
> metadata query methods in Java that seem to be returning the same stale
> information that sp_help is returning. These methods are a part of the
> standard JDK, so we can't easily fiddle with them. Anyway, as a result,
our
> object model (at least with respect to views) may not match our current
> schema!|||"ScottyBaby" <scottseansmith2NO@.SPAM.hotmail.com> wrote in message news:<OOWdnchUCvygmzWiRTvUrg@.texas.net>...
> Hi,
> I've run into a curious problem with MS SQL Server 8.0. Using sp_help and
> SQL Query Analyzer's object browser to view the columns returned by a view,
> I find that sp_help is reporting stale information.
> In a recent schema change, for example, someone lengthened a varchar column
> from 15 to 50 characters. If we use sp_help to find out about a view that
> depends upon this column, it still shows up as VARCHAR(15), whereas the
> object browser correctly reports it as VARCHAR(50).
> Dropping and recreating the view fixes the problem, but we have quite a few
> views, and dropping and re-creating all of them any time a schema change is
> made is something we want to avoid. I tried using DBCC CHECKDB in hopes that
> it would 'refresh' SQL Server's information, but no luck.
> (if you're curious as to why I don't just use the object browser instead,
> read boring technical details below)
> Has anyone seen this before? Is there some other way (other than
> re-creating every view) to tell SQL Server to "refresh" it's information?
> Thanks!
> -Scott
> -------
> Boring Technical Information:
> The reason this is an issue for us (i.e., I can't just use the object
> browser instead) is that our object model classes are built using standard
> metadata query methods in Java that seem to be returning the same stale
> information that sp_help is returning. These methods are a part of the
> standard JDK, so we can't easily fiddle with them. Anyway, as a result, our
> object model (at least with respect to views) may not match our current
> schema!

See sp_refreshview in Books Online, which is intended for exactly this situation.

Simon|||Hi

Try looking at sp_refreshview. Previous posts have described ways to
do this for all tables if you need to write a procedure.

John

"ScottyBaby" <scottseansmith2NO@.SPAM.hotmail.com> wrote in message news:<OOWdnchUCvygmzWiRTvUrg@.texas.net>...
> Hi,
> I've run into a curious problem with MS SQL Server 8.0. Using sp_help and
> SQL Query Analyzer's object browser to view the columns returned by a view,
> I find that sp_help is reporting stale information.
> In a recent schema change, for example, someone lengthened a varchar column
> from 15 to 50 characters. If we use sp_help to find out about a view that
> depends upon this column, it still shows up as VARCHAR(15), whereas the
> object browser correctly reports it as VARCHAR(50).
> Dropping and recreating the view fixes the problem, but we have quite a few
> views, and dropping and re-creating all of them any time a schema change is
> made is something we want to avoid. I tried using DBCC CHECKDB in hopes that
> it would 'refresh' SQL Server's information, but no luck.
> (if you're curious as to why I don't just use the object browser instead,
> read boring technical details below)
> Has anyone seen this before? Is there some other way (other than
> re-creating every view) to tell SQL Server to "refresh" it's information?
> Thanks!
> -Scott
> -------
> Boring Technical Information:
> The reason this is an issue for us (i.e., I can't just use the object
> browser instead) is that our object model classes are built using standard
> metadata query methods in Java that seem to be returning the same stale
> information that sp_help is returning. These methods are a part of the
> standard JDK, so we can't easily fiddle with them. Anyway, as a result, our
> object model (at least with respect to views) may not match our current
> schema!

Monday, March 12, 2012

Heterogeneous queries error

Heterogeneous queries error

Friday, March 9, 2012

HELP--cannot shrink the transaction log

Monday, February 27, 2012

HELP: sp_help and object browser report view column sizes differently

Help Backup

Blog Archive

About Me