October 19, 2017 at 11:20 pm
Hello,
I have the following basic sp to truncate a table...
Create Procedure sp_Truncate_Address
@DBName varchar(30) = [AdventureWorks2014],
@TableName varchar(30) = [Person].[Address_Staging_A]
@SQL = 'TRUNCATE TABLE' + @DatabaseName + ' ' + @TableName
EXEC (@SQL)
...it does not like the two-part name, but I cant work out why or how to fix it, I get a red squiggle under the '.', or if I play around with it, under the [Person] schema name. Is there a knack to dealing with 2 and 3 part names in stored procs? Thank you.
Regards,
D.
October 20, 2017 at 12:28 am
Duran - Thursday, October 19, 2017 11:20 PMHello,
I have the following basic sp to truncate a table...Create Procedure sp_Truncate_Address
@DBName varchar(30) = [AdventureWorks2014],
@TableName varchar(30) = [Person].[Address_Staging_A]
@SQL = 'TRUNCATE TABLE' + @DatabaseName + ' ' + @TableName
EXEC (@SQL)...it does not like the two-part name, but I cant work out why or how to fix it, I get a red squiggle under the '.', or if I play around with it, under the [Person] schema name. Is there a knack to dealing with 2 and 3 part names in stored procs? Thank you.
Regards,
D.
You are going across DBs. So you need a 3-part name@SQL = 'TRUNCATE TABLE ' + @DatabaseName + '.' + @ScemaName + '.' + @TableName
October 20, 2017 at 2:10 am
Indeed. And please make sure you read and understand about SQL injection before that goes anywhere near a live server.
John
October 21, 2017 at 10:16 am
John Mitchell-245523 - Friday, October 20, 2017 2:10 AMIndeed. And please make sure you read and understand about SQL injection before that goes anywhere near a live server.John
Try to use Databasename.schemaname.tablename. It will work across databases. Make sure you have enough ddl rights to execute the commands across databases.
October 21, 2017 at 2:10 pm
John Mitchell-245523 - Friday, October 20, 2017 2:10 AMIndeed. And please make sure you read and understand about SQL injection before that goes anywhere near a live server.John
Duran, please heed John's warning here - it's a serious topic. You don't want to hear about the company you work for on the news.
At a minimum, start here: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql
October 21, 2017 at 4:34 pm
DesNorton - Friday, October 20, 2017 12:28 AMDuran - Thursday, October 19, 2017 11:20 PMHello,
I have the following basic sp to truncate a table...Create Procedure sp_Truncate_Address
@DBName varchar(30) = [AdventureWorks2014],
@TableName varchar(30) = [Person].[Address_Staging_A]
@SQL = 'TRUNCATE TABLE' + @DatabaseName + ' ' + @TableName
EXEC (@SQL)...it does not like the two-part name, but I cant work out why or how to fix it, I get a red squiggle under the '.', or if I play around with it, under the [Person] schema name. Is there a knack to dealing with 2 and 3 part names in stored procs? Thank you.
Regards,
D.You are going across DBs. So you need a 3-part name
@SQL = 'TRUNCATE TABLE ' + @DatabaseName + '.' + @ScemaName + '.' + @TableName
Gosh... don't use 3 part naming. Someday when someone moves either database or renames the further database, you'll hate yourself trying to find all the bloody 3 part naming you did.
My recommendation is to either write procs on the further database and call them to do things like TRUNCATE in that database or use synonyms but don't use 3 part naming in code.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 22, 2017 at 3:16 pm
Duran - Thursday, October 19, 2017 11:20 PMHello,
I have the following basic sp to truncate a table...Create Procedure sp_Truncate_Address
@DBName varchar(30) = [AdventureWorks2014],
@TableName varchar(30) = [Person].[Address_Staging_A]
@SQL = 'TRUNCATE TABLE' + @DatabaseName + ' ' + @TableName
EXEC (@SQL)...it does not like the two-part name, but I cant work out why or how to fix it, I get a red squiggle under the '.', or if I play around with it, under the [Person] schema name. Is there a knack to dealing with 2 and 3 part names in stored procs? Thank you.
Regards,
D.
Create Procedure sp_Truncate_Family
AS
DECLARE @DBName varchar(30) = '<database name>'
Declare @TableName varchar(30) = 'dbo.<table_name>'
Declare @SQl Varchar(250) = 'TRUNCATE TABLE '+' ' + @DBName + '.'+ @TableName
EXEC (@SQl)
Modify your store proc as per the above script, it should work now.
October 22, 2017 at 5:48 pm
chennam9 - Sunday, October 22, 2017 3:16 PMDuran - Thursday, October 19, 2017 11:20 PMHello,
I have the following basic sp to truncate a table...Create Procedure sp_Truncate_Address
@DBName varchar(30) = [AdventureWorks2014],
@TableName varchar(30) = [Person].[Address_Staging_A]
@SQL = 'TRUNCATE TABLE' + @DatabaseName + ' ' + @TableName
EXEC (@SQL)...it does not like the two-part name, but I cant work out why or how to fix it, I get a red squiggle under the '.', or if I play around with it, under the [Person] schema name. Is there a knack to dealing with 2 and 3 part names in stored procs? Thank you.
Regards,
D.Create Procedure sp_Truncate_Family
AS
DECLARE @DBName varchar(30) = '<database name>'
Declare @TableName varchar(30) = 'dbo.<table_name>'
Declare @SQl Varchar(250) = 'TRUNCATE TABLE '+' ' + @DBName + '.'+ @TableNameEXEC (@SQl)
Modify your store proc as per the above script, it should work now.
I say again... don't use 3 part naming in code. You'll absolutely hate yourself someday for the previous reasons given.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 3, 2017 at 10:42 am
My preferred solution for this is to create a linked server and then create synonyms for the remote tables. When you call the truncate with the synonym it translates that to the correct server and table.
The other advantage of the synonym approach is that moving from DEV to TEST to PROD is much easier as you only need to change the synonym signatures and the SPROC code remains the same in source code control. It is also much easier to do if you are rolling this out across multiple servers or even multiple clients.
November 3, 2017 at 10:57 am
Duran - Thursday, October 19, 2017 11:20 PMHello,
I have the following basic sp to truncate a table...Create Procedure sp_Truncate_Address
@DBName varchar(30) = [AdventureWorks2014],
@TableName varchar(30) = [Person].[Address_Staging_A]
@SQL = 'TRUNCATE TABLE' + @DatabaseName + ' ' + @TableName
EXEC (@SQL)...it does not like the two-part name, but I cant work out why or how to fix it, I get a red squiggle under the '.', or if I play around with it, under the [Person] schema name. Is there a knack to dealing with 2 and 3 part names in stored procs? Thank you.
Regards,
D.
You need to put quotes around strings.Create Procedure sp_Truncate_Address
(
@DBName varchar(30) = '[AdventureWorks2014]',
@TableName varchar(30) = '[Person].[Address_Staging_A]'
)
AS
DECLARE @SQL nvarchar(MAX)
SET @SQL = 'TRUNCATE TABLE' + @DBName + '.' + @TableName
EXEC (@SQL)
GO
November 3, 2017 at 2:03 pm
Not a big fan of this procedure. But if you're going to use it, try keeping it safe and robust.
CREATE PROCEDURE sp_Truncate_Anything
(
@DBName nvarchar(130) = '[AdventureWorks2014]',
@TableName nvarchar(261) = '[Person].[Address_Staging_A]'
)
AS
DECLARE @SQL nvarchar(MAX);
SELECT @SQL = REPLACE('
DECLARE @SQL nvarchar(MAX);
SELECT @SQL = ''TRUNCATE TABLE '' + QUOTENAME(s.name) + N''.'' + QUOTENAME(t.name)
FROM <<db_name>>.sys.schemas s
JOIN <<db_name>>.sys.tables t ON s.schema_id = t.schema_id
WHERE s.name = PARSENAME(@TableName, 2)
AND t.name = PARSENAME(@TableName, 1);
PRINT @SQL;
EXEC (@SQL);
', '<<db_name>>', name)
FROM sys.databases
WHERE name = PARSENAME(@DBName, 1);
EXEC sp_executesql @SQL, N'@TableName nvarchar(261)', @TableName;
GO
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply