Bypass CREATE PROCEDURE Dependancy Validation? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Bypass CREATE PROCEDURE Dependancy Validation?

Author

Topic

Sorcerysoft
Starting Member

2 Posts

Posted - 2006-11-27 : 17:36:12

I have situation where I need to run a "CREATE PROCEDURE" script and need the server to bypass validation( on dependancies ) of the sql statement. Here's an Example:

CREATE PROCEDURE p_MyTestProcedure
AS
SELECT * FROM TempDB..TempTable
GO

The TempDB may not exist on the server or TempDB will exist but TempTable will not. If I run this in Query Analyzer, with NO TempDB or TempDB ..TempTable, it EXECUTES/COMPILES fine.

If I do the same thing from within my application I get the error message like "Invalid object name Export..TempTable...".

Is there a connection setting or SQL keyword that can make my app behave like Query Analyzer and bypass dependancy validation? How does Query Analyzer do it? Thanks.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-27 : 17:51:53

The CREATE PROCEDURE statement executes, but the stored procedure is not compiled at that time. It is not compiled until you use it the first time. So what is the application doing - the error you are seeing is the result of trying to execute the stored proc, not a result of creating the stored proc.

See "Deferred Name Resolution and Compilation" in Books Online

Sorcerysoft
Starting Member

2 Posts

Posted - 2006-11-27 : 22:12:11

Thanks for the quick response snSQL.

Your right It's not compiled at that time. But the error i'm getting is not because I'm executing the procedure. We have a database update system that iterates through an XML file containing SQL scripts and executes them on the database. This script isn't calling the procedure. It's simply Executing the "CREATE PROCEDURE". And we are not using the WITH RECOMPILE option. I will read the article you suggested. I should also mentioned that I'm running this on SQL 2000. If you have any other ideas please let me know. Thank you.

Here's the C++/ATL code that runs the script:

bool CVersionUpdateMgr::ExecuteSQL( CString strSQL, CComBSTR &strOutputMessage )
{
try
{
strSQL.TrimLeft();
strSQL.TrimRight();

if( strSQL.Left( 5 ) == "PRINT" )
{
strOutputMessage = strSQL.Mid( 6 );
return TRUE;
}
else if( strSQL.GetLength() )
{
m_pConnection->CommandTimeout = 0;
m_pConnection->Execute( _bstr_t( strSQL ), 0, 0 );
OutputDebugString( strSQL );
}
return TRUE;
}
catch( _com_error &e )
{
TRACE( "%s\n", (char *) e.Description() );
strOutputMessage = (char *) e.Description();
}
return FALSE;
}

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-27 : 23:58:29

I don't think you are executing CREATE PROCEDURE statement? I ran the same procedure through VB and it worked fine (even though I don't have either TempDB database or TempTable table).

Can you print the contents of strSQL just before executing it and post them here?

Harsh Athalye
India.
"Nothing is Impossible"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources