Or try OPENROWSET instead

Did u define a linked server before executing the query? If the linked Server entry is not found in the sysservers table - u cannot execute the given query.

U can create Maintenance Plans or create jobs to run pre-written TSQL scripts to do that. Search in BOL for Maintenance Plans

U said - "when the package is called from a sql agent it fails." Does it succeed when started manually without scheduling via Agent?

Not sure what u r doing. But...

The only way to see what is the SQL that user is running on SQL Server 2000 is to run SQL Server Profiler, assuming that the stored procedures or SQL Statements comming...

-What ServicePack are you on? Please post the result for Select @@version

-Is there a stack dump in SQL Server Error Logs? Can u post it here?

-Can u also post SQLAgent.out...

I am not an expert on C# and .Net stuff, but as far as SQL Server is concerned, sp_executesql can cache the plan for dynamic SQL. But there are other...

The error relates to LSNs and hence Transaction log files - the log reader agent fails and generates the error. Run DBCC CHECKDB on the replicated database, MSDB and Master. If...

Please also have a look at the article: http://support.microsoft.com/default.aspx?scid=kb;en-us;Q302621

If you use a permanent table instead of temp table - do u get the same error? Did u try using the sa login to perform the same operation -...

Hey Guys

I agree from the database performance and scalability point of view that stored procedures are the way to go, but I have no say in that. Table names and...

Yeah, I wanted that but Architects want to implement their own persistence layer and they are against Stored procedures.

Hi Noeld

Thanks for the reply. I am using dynamic SQL because our developer asked me if this operation is possible from .net application by touching the db layer only once.

-Gary

Fantastic - RSharma U rock

Thanks a lot Govin.

Steve's answer helped.

This will do:

set @sql = 'select @rcount = Count(*) from ' + @tablename

exec sp_executesql @sql, N'@rcount int OUTPUT', @rcount OUTPUT