Finding a line number

I have a large query that I am trying to debug in query analyzer.
However, the errors I get have no line number or reference to where
they are failing. How can I find out what line in the query is causing
a particular error message to appear?

Thanks
John Ivey
South Pike School Districtjohnivey@.gmail.com wrote:

Quote:

Originally Posted by

I have a large query that I am trying to debug in query analyzer.
However, the errors I get have no line number or reference to where
they are failing. How can I find out what line in the query is causing
a particular error message to appear?
>
Thanks
John Ivey
South Pike School District

What is the error message?

Try simplifying the query until it works. Exactly where to start
depends on just what the error message is.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||(johnivey@.gmail.com) writes:

Quote:

Originally Posted by

I have a large query that I am trying to debug in query analyzer.
However, the errors I get have no line number or reference to where
they are failing. How can I find out what line in the query is causing
a particular error message to appear?

Errors usually come with a line number, and you can double-click on
the error message and be taken to the troublesome line.

Unfortunately, though, SQL Server's reporting of line numbers is often
inaccurate. Some errors are reported on the statement following the
statement with the error. If your error is incorrect column name or
table name, SQL Server will only point to where the query starts, which
is not very helpful for a 50-line query. (SQL 2005 does actually report
the exact line number in some of these situations, but not all.)

And if the error occurs during execution of the query, it's not really
sure that it would be meaningful with a line number within the query,
as the query is not processed line by line.

What error message do you get?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

find which sprocs/views reference a table

Is there a quick way to find what sprocs/views reference a table within ?
Does sp_depends work accurately ?
sp_depends can not be relied on if you cannot guarentee the database has been
built in the correct order. As far as we know we're the only provider of an
application that can do this.
regards,
Mark Baekdal
www.dbghost.com
+44 (0)208 241 1762
Living and breathing database change management for SQL Server
"Hassan" wrote:

> Is there a quick way to find what sprocs/views reference a table within ?
> Does sp_depends work accurately ?
>
>
|||Hi Hassan
sp_depends has been notorious for years. If you want a quick, simple answer,
you can simply select the procs from syscomments where the name of the table
is in the text of the sprocs / views. Otherwise, I'd recommend you check out
SQL Scribe which is a nice 3rd party tool that does the trick nicely.
http://www.ag-software.com
Regards,
Greg Linwood
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:u4w6JtIyEHA.3416@.TK2MSFTNGP09.phx.gbl...
> Is there a quick way to find what sprocs/views reference a table within ?
> Does sp_depends work accurately ?
>
|||As mentioned, the dependency records in a SQL Server database can be
problematic.
The SQL Server Tools WT3 application addresses the problems. It can list
dependencies as reflected in the dependency records. It can list
dependencies as found with a text search. It can rebuild the dependency
records to their original/correct state. It can generate detailed database
documentation, including dependencies. All of this for $19, which is about
1/20 (5%) of a previously mentioned product.
Please visit http://www.sqlservertools.us for more information.
"Hassan" wrote:

> Is there a quick way to find what sprocs/views reference a table within ?
> Does sp_depends work accurately ?
>
>

find which sprocs/views reference a table

Is there a quick way to find what sprocs/views reference a table within ?
Does sp_depends work accurately ?sp_depends can not be relied on if you cannot guarentee the database has been
built in the correct order. As far as we know we're the only provider of an
application that can do this.
regards,
Mark Baekdal
www.dbghost.com
+44 (0)208 241 1762
Living and breathing database change management for SQL Server
"Hassan" wrote:
> Is there a quick way to find what sprocs/views reference a table within ?
> Does sp_depends work accurately ?
>
>|||Hi Hassan
sp_depends has been notorious for years. If you want a quick, simple answer,
you can simply select the procs from syscomments where the name of the table
is in the text of the sprocs / views. Otherwise, I'd recommend you check out
SQL Scribe which is a nice 3rd party tool that does the trick nicely.
http://www.ag-software.com
Regards,
Greg Linwood
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:u4w6JtIyEHA.3416@.TK2MSFTNGP09.phx.gbl...
> Is there a quick way to find what sprocs/views reference a table within ?
> Does sp_depends work accurately ?
>|||As mentioned, the dependency records in a SQL Server database can be
problematic.
The SQL Server Tools WT3 application addresses the problems. It can list
dependencies as reflected in the dependency records. It can list
dependencies as found with a text search. It can rebuild the dependency
records to their original/correct state. It can generate detailed database
documentation, including dependencies. All of this for $19, which is about
1/20 (5%) of a previously mentioned product.
Please visit http://www.sqlservertools.us for more information.
"Hassan" wrote:
> Is there a quick way to find what sprocs/views reference a table within ?
> Does sp_depends work accurately ?
>
>

find which sprocs/views reference a table

Is there a quick way to find what sprocs/views reference a table within ?
Does sp_depends work accurately ?sp_depends can not be relied on if you cannot guarentee the database has bee
n
built in the correct order. As far as we know we're the only provider of an
application that can do this.
regards,
Mark Baekdal
www.dbghost.com
+44 (0)208 241 1762
Living and breathing database change management for SQL Server
"Hassan" wrote:

> Is there a quick way to find what sprocs/views reference a table within ?
> Does sp_depends work accurately ?
>
>|||Hi Hassan
sp_depends has been notorious for years. If you want a quick, simple answer,
you can simply select the procs from syscomments where the name of the table
is in the text of the sprocs / views. Otherwise, I'd recommend you check out
SQL Scribe which is a nice 3rd party tool that does the trick nicely.
http://www.ag-software.com
Regards,
Greg Linwood
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:u4w6JtIyEHA.3416@.TK2MSFTNGP09.phx.gbl...
> Is there a quick way to find what sprocs/views reference a table within ?
> Does sp_depends work accurately ?
>|||As mentioned, the dependency records in a SQL Server database can be
problematic.
The SQL Server Tools WT3 application addresses the problems. It can list
dependencies as reflected in the dependency records. It can list
dependencies as found with a text search. It can rebuild the dependency
records to their original/correct state. It can generate detailed database
documentation, including dependencies. All of this for $19, which is about
1/20 (5%) of a previously mentioned product.
Please visit http://www.sqlservertools.us for more information.
"Hassan" wrote:

> Is there a quick way to find what sprocs/views reference a table within ?
> Does sp_depends work accurately ?
>
>

Find the coumns in the reference

Given a table A, I need to find all the tables that are in PK-FK with A and the columns in the reference. I can get the tables that have the FK relation through sysreferences or sysconstraints or sysforeignkeys but I have not been able to find out how to identify the specific column that is in the relation. Any one has any idea?

Sample:

SELECT * FROM sysreferences WHERE fkeyid = Object_Id('TableA')

Thanks.

Have you tried a system procedure sp_helpconstraint? You can get more detailed information about constraints including PK/FK of specified table. For example:

sp_helpconstraint 'TableA'

|||Thanks for the reply Iori_Jay. I managed to get something working.