Dynamic SQL

Question

Post reply

Dynamic SQL

Mike Levan

SSChampion

Points: 14201
More actions
April 1, 2009 at 7:56 am

#130340

I am runnig the follwing sql in the same server though i have given server name in my sql.
When i run this i get an error
Cannot find the object "RevBud' because it does not exist or you do not have permissions.
set @sql = 'truncate table ' + @server + '.' + @Dev_Rev+ '.dbo.RevBud'
but when i run this it works fine and inserts data.
set @sql = 'insert into ' + @server + '.' + @Dev_Rev+ '.dbo.RevBud
select RevID, Revdate '
what is the difference.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

Change the (not posted 😉 )

EXECUTE @sql

To this:

EXECUTE (@sql)

Or this (requires @sql as NVARCHAR):

EXECUTE sp_executesql @sql

Greets

Flo

Mike Levan SSChampion Points: 14201 More actions · Answer 2

ofcourse i am doing that else how can irun it without EXEC command but my doubt is different.

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 3

Oversight...

You are using a linked server, right?

"TRUNCATE TABLE " is a DDL statement, not a DML so I think it is not possible with a linked server.

Maybe it works over OPENQUERY but i did not try.

Greets

Flo

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

How about providing ALL the code not just snippets? From what you have posted, I have no idea why it didn't work.

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 5

Differences!!!

1. "TRUNCATE" deletes all the records from the table whereas "INSERT INTO" inserts data into the table.

2. "TRUNCATE" requires db_owner or db_ddladmin permissions whereas "INSERT INTO" requires INSERT or db_owner or db_datawriter permissions.

3. "TRUNCATE" is a DDL statement whereas "INSERT INTO" is a DML statement.

--Ramesh

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 6

Hi

Are you the owner of the table? If YES then there shouldn't be any problem. In this case looks like you are not the owner of the table. TRUNCATE is DDL privilege and requires special privileges to be granted to the user.

Thanks -- Vijaya Kadiyala

http://www.dotnetvj.vom

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 7

You can either wrap the TRUNCATE in a stored procedure and call that stored procedure instead, or you can use the solution from Perry:

Perry (2/15/2006)
However, you could switch to calling sp_executesql remotely, like so:
execute mylinkedserver.tempdb.dbo.sp_executesql "TRUNCATE TABLE dbo.t1"

http://www.sqlservercentral.com/Forums/Topic258367-23-1.aspx#bm547747

Best Regards,

Chris Büttner