Query Syntax: invalid

Showing posts with label invalid. Show all posts

Wednesday, March 28, 2012

Query with user-define function John Bell

Yes!! It works. But just a point. In the [Index] column I have both strings
and numbers. When I query [Index]=AA there is an error: Invalid column name
'AA'
Any more suggestion?
Thank you
Helen
"John Bell" wrote:

> Hi
> Maybe
> CREATE TABLE foo ( [index] int not null identity(1,1), a int, b int, c
> varchar(10) )
> INSERT INTO Foo ( a, b, c ) SELECT 5,9,'a+@.q+3*b'
> DECLARE @.sql VARCHAR(255)
> SELECT @.sql = 'DECLARE @.q int SET @.q=7 SELECT a,b,' + c+ ' FROM dbo.foo
> where [Index]=1'
> FROM dbo.foo where [Index]=1
> EXEC(@.sql)
> John
>
> "Helen" <Helen@.discussions.microsoft.com> wrote in message
> news:A3A0BB12-2B9D-4173-81B3-F17D30F6D59B@.microsoft.com...
>
>Hi
If your column is a character datatype use 'AA' As quotes will require
escaping with a second quote within the string you end up with:
CREATE TABLE foo ( [index] CHAR(2) not null , a int, b int, c
varchar(10) )
INSERT INTO Foo ( [Index],a, b, c ) SELECT 'AA',5,9,'a+@.q+(3*b)'
DECLARE @.sql VARCHAR(255)
SELECT @.sql = 'DECLARE @.q int SET @.q=7 SELECT a,b,' + c+ ' FROM dbo.foo
where [Index]=''AA'''
FROM dbo.foo where [Index]='AA'
EXEC(@.sql)
John
"Helen" <Helen@.discussions.microsoft.com> wrote in message
news:196B3526-043F-40E1-9BD5-F1D6669C093A@.microsoft.com...
> Yes!! It works. But just a point. In the [Index] column I have both
> strings
> and numbers. When I query [Index]=AA there is an error: Invalid column
> name
> 'AA'
> Any more suggestion?
> Thank you
> Helen
> "John Bell" wrote:
>|||Yes!!! It is all right now. I wonder how to write this in visualbasic.net
Thanks
Helen
"John Bell" wrote:

> Hi
> If your column is a character datatype use 'AA' As quotes will require
> escaping with a second quote within the string you end up with:
> CREATE TABLE foo ( [index] CHAR(2) not null , a int, b int, c
> varchar(10) )
> INSERT INTO Foo ( [Index],a, b, c ) SELECT 'AA',5,9,'a+@.q+(3*b)'
> DECLARE @.sql VARCHAR(255)
> SELECT @.sql = 'DECLARE @.q int SET @.q=7 SELECT a,b,' + c+ ' FROM dbo.foo
> where [Index]=''AA'''
> FROM dbo.foo where [Index]='AA'
> EXEC(@.sql)
> John
> "Helen" <Helen@.discussions.microsoft.com> wrote in message
> news:196B3526-043F-40E1-9BD5-F1D6669C093A@.microsoft.com...
>
>

Friday, March 9, 2012

Query to find invalid objects?

Is there a system table that holds data on invalid objects/procedures? In
Oracle, I can query dba_objects table where status='INVALID' to find all of
the invalid objects. Is there anything similar in SQL Server?
thanks!
SusanHi Susan
What do you mean by invalid objects?
If you require to know any stored procedure that is referencing another
stored procedure or table etc.. that does not exists then you may want to try
doing a textual search of the source code. There is a sysdepends table for
dependencies, but this is not guaranteed to list everything.
John
"Susan Cooper" wrote:
> Is there a system table that holds data on invalid objects/procedures? In
> Oracle, I can query dba_objects table where status='INVALID' to find all of
> the invalid objects. Is there anything similar in SQL Server?
> thanks!
> Susan|||Can you define what an "invalid" object is? This is not a term used in the SQL Server world, so it
could mean different things. The short story is "no", there is no such table...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Susan Cooper" <SusanCooper@.discussions.microsoft.com> wrote in message
news:303B1E52-65C4-4520-BE87-D1B1249D7DA9@.microsoft.com...
> Is there a system table that holds data on invalid objects/procedures? In
> Oracle, I can query dba_objects table where status='INVALID' to find all of
> the invalid objects. Is there anything similar in SQL Server?
> thanks!
> Susan|||By invalid, I mean that dependencies are broken. I was hoping that SQL
Server stored something saying a procedure will not work if an object it
depends on is dropped or changed, ect. In the Oracle world, these are called
invalid objects and you can query the dba_objects table.
Thanks for the help!
"Tibor Karaszi" wrote:
> Can you define what an "invalid" object is? This is not a term used in the SQL Server world, so it
> could mean different things. The short story is "no", there is no such table...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Susan Cooper" <SusanCooper@.discussions.microsoft.com> wrote in message
> news:303B1E52-65C4-4520-BE87-D1B1249D7DA9@.microsoft.com...
> > Is there a system table that holds data on invalid objects/procedures? In
> > Oracle, I can query dba_objects table where status='INVALID' to find all of
> > the invalid objects. Is there anything similar in SQL Server?
> >
> > thanks!
> > Susan
>|||Hi
You may want to look at using SCHEMABINDING with views and functions (which
is the converse of what you wanted!), but for stored procedures it is not
available.
John
"Susan Cooper" wrote:
> By invalid, I mean that dependencies are broken. I was hoping that SQL
> Server stored something saying a procedure will not work if an object it
> depends on is dropped or changed, ect. In the Oracle world, these are called
> invalid objects and you can query the dba_objects table.
> Thanks for the help!
> "Tibor Karaszi" wrote:
> > Can you define what an "invalid" object is? This is not a term used in the SQL Server world, so it
> > could mean different things. The short story is "no", there is no such table...
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Susan Cooper" <SusanCooper@.discussions.microsoft.com> wrote in message
> > news:303B1E52-65C4-4520-BE87-D1B1249D7DA9@.microsoft.com...
> > > Is there a system table that holds data on invalid objects/procedures? In
> > > Oracle, I can query dba_objects table where status='INVALID' to find all of
> > > the invalid objects. Is there anything similar in SQL Server?
> > >
> > > thanks!
> > > Susan
> >
> >|||As already posted, the sp_depends and sysdependencies tables (and the 2005 catalog view counterpart)
will give you a clue. But it isn't 100 percent for reasons like dynamic SQL, deferred name
resolution etc.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Susan Cooper" <SusanCooper@.discussions.microsoft.com> wrote in message
news:951437A6-A3F4-493D-A17C-4E753FC9E526@.microsoft.com...
> By invalid, I mean that dependencies are broken. I was hoping that SQL
> Server stored something saying a procedure will not work if an object it
> depends on is dropped or changed, ect. In the Oracle world, these are called
> invalid objects and you can query the dba_objects table.
> Thanks for the help!
> "Tibor Karaszi" wrote:
>> Can you define what an "invalid" object is? This is not a term used in the SQL Server world, so
>> it
>> could mean different things. The short story is "no", there is no such table...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Susan Cooper" <SusanCooper@.discussions.microsoft.com> wrote in message
>> news:303B1E52-65C4-4520-BE87-D1B1249D7DA9@.microsoft.com...
>> > Is there a system table that holds data on invalid objects/procedures? In
>> > Oracle, I can query dba_objects table where status='INVALID' to find all of
>> > the invalid objects. Is there anything similar in SQL Server?
>> >
>> > thanks!
>> > Susan
>>

Wednesday, March 28, 2012

Query with user-define function John Bell

Query with user-define function John Bell

Friday, March 9, 2012

Query to find invalid objects?

Query to find invalid objects?

Query Syntax

Blog Archive

About Me