SQL 2012 :: Using Stored Procedure To Truncate Tables?
Jun 30, 2014
I was just looking at an SSIS package someone else set up and I went into one of the execute SQL tasks and it is calling a stored procedure to truncate a table. There are a lot of places that tables are truncated within this SSIS package. But each one the 'database', 'owner', and 'table' name are hard coded.
Like this: exec dbo.uspTruncateTable 'dbname', 'dbo', 'tblname'
Of course there are different names, just changed for an example.
Here is the code in the Stored Procedure.
CREATE procedure [dbo].[uspTruncateTable]
@nDatabase varchar(255),
@nOwner varchar(255),
@ntable varchar(255)
as
declare @sqlString nvarchar(max)
set @sqlString = 'Truncate Table ' + @nDatabase + '.' + @nOwner + '.' + @ntable
exec (@sqlString)
This just seems like an unneeded step, just code the 'truncate table dbname.dbo.tblname'.
View 5 Replies
ADVERTISEMENT
Feb 14, 2015
I have a table with the list of all TableNames in the database. I would like to query that table and find any tables used in any stored procedure in that DB.
Select * from dbo.MyTableList
where Table_Name in
(
Select Name
From sys.procedures
Where OBJECT_DEFINITION(object_id) LIKE '%MY_TABLE_NAME%'
Order by name
)
View 7 Replies
View Related
Oct 4, 2013
I have never created a stored procedure before so Not even sure what i am trying to do is even viable
Aim – Create a stored procedure which truncates a table at a certain time of the day
Table name Dan.Stg_Jitter_Opp want to truncate table at 11am
View 7 Replies
View Related
Oct 26, 2006
I have a procedure that uses varchar(max) as output parameter, when I tried to retrieve
the result of calling the procedure, the result is truncated to 4000 character. Is this a driver bug?
Any workaround?
Here is the pseudo code:
create Procedure foo(@output varchar(max))
{
set @foo = 'string that has more than 4000 characters...';
return;
}
Java code:
CallableStatement cs = connection.prepareCall("{call foo ?}");
cs.registerOutputParameter(1, Types.longvarchar); // also tried Types.CLOB.
cs.execute();
String result = cs.getString(1); // The result is truncated to 4000 char.
-- Also tried
CLOB clob = cs.getClob(1);
long len = clob.length(); // The result is 4000.
Thanks,
Eric Wang
View 3 Replies
View Related
Sep 26, 2014
I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure
at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT
I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT
View 3 Replies
View Related
Jan 21, 2014
On SQL 2012 (64bit) I have a CLR stored procedure that calls another, T-SQL stored procedure.
The CLR procedure passes a sizeable amount of data via a user defined table type resp.table values parameter. It passes about 12,000 rows with 3 columns each.
For some reason the call of the procedure is verz very slow. I mean just the call, not the procedure.
I changed the procdure to do nothing (return 1 in first line).
So with all parameters set from
command.ExecuteNonQuery()to
create proc usp_Proc1
@myTable myTable read only
begin
return 1
end
it takes 8 seconds.I measured all other steps (creating the data table in CLR, creating the SQL Param, adding it to the command, executing the stored procedure) and all of them work fine and very fast.
When I trace the procedure call in SQL Profiler I get a line like this for each line of the data table (12,000)
SP:StmtCompleted -- Encrypted Text.
As I said, not the procedure or the creation of the data table takes so long, really only the passing of the data table to the procedure.
View 5 Replies
View Related
Nov 13, 2014
In one store procedure I do insert same data into two tables (They have the same structure): OrderA and OrderB
insert into OrderA select * from OrderTemp
insert into OrderB select * from OrderTemp
And then got an error for code below.
"Multi-part identifier "dbo.orderB.OrderCity" could not be bound
IF dbo.OrderB.OrderCity=''
BEGIN
update dbo.OrderB
set dbo.orderB.OrderCity='London'
END
View 5 Replies
View Related
Jun 2, 2015
I am running a sproc we have developed and I am getting a -6 as a returned value.what that actually means.
View 7 Replies
View Related
Mar 8, 2001
I frequently have tables that I want to empty and refill. I thought I read somewhere that trucating was a bad idea, example:
Truncate table tblFruits
A bad idea because of something to do with the log? I'm a beginner so I don't understand transaction processing very well, or logs.
Is it better to delete the table and rebuild it in code before you add the values?
View 1 Replies
View Related
Jun 22, 2004
Anyone?
I tried the following code...thought I could trap the RI Error and move on...
It raises all the way out though....
Server: Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'myTable99' because it is being referenced by a FOREIGN KEY constraint.
I thought you could trap it this way...
CREATE TABLE myTable99(Col1 int PRIMARY KEY)
GO
CREATE TABLE myTable00(Col1 int, Col2 int, PRIMARY KEY(Col1,Col2), FOREIGN KEY (Col1) REFERENCES myTable99(Col1))
GO
INSERT INTO myTable99(Col1) SELECT 1
INSERT INTO myTable00(Col1, Col2) SELECT 1,2
GO
CREATE PROC myDynamicSQL99 @sql varchar(8000) AS EXEC(@sql)
GO
DECLARE @error int, @TABLE_NAME sysname, @sql varchar(8000), @x int, @rc int
SELECT @error = 0, @x = 0
DoItAgain:
DECLARE myCursor99 CURSOR
FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME LIKE 'myTable%'
ORDER BY TABLE_NAME DESC
OPEN myCursor99
FETCH NEXT FROM myCursor99 INTO @TABLE_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = 'TRUNCATE TABLE ' + @TABLE_NAME
SELECT @SQL
EXEC @rc = myDynamicSQL99 @SQL
SELECT @Error = @@Error
SELECT 'Error Code: ' + CONVERT(varchar(15),@Error) + ' @rc: '+ CONVERT(varchar(15),@rc)
IF @Error <> 0
SELECT @x = @x + 1
FETCH NEXT FROM myCursor99 INTO @TABLE_NAME
END
CLOSE myCursor99
DEALLOCATE myCursor99
IF @x <> 0
BEGIN
SELECT @Error = 0
GOTO DoItAgain
END
GO
DROP PROC myDynamicSQL99
DROP TABLE myTable00
DROP TABLE myTable99
GO
View 14 Replies
View Related
Apr 11, 2007
Hi,
I'm quite new to SQL 2000 and I've been trying to find an easy way to truncate a number of tables in a script. The table names are all prefixed with 'RESULTS_' so they are easy enough to identify. Is this fairly straightforward?
View 12 Replies
View Related
Mar 23, 2014
I want to know, Is it possible to check a particular stored procedure is recompiling or not?
View 7 Replies
View Related
Nov 26, 2014
I have a database user dsrpReader that can execute stored procedures in one database; it's the only thing that this user can do. Works great except for the below stored procedure.
[code=sql]
CREATE PROCEDURE [dbo].[__usp_DatabaseFieldsize_Get]
@pTablename nvarchar(128)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
[Code] ....
If I run the above as an administrative user (windows login), I get N rows of information back (N > 0). If I run it as an unprivileged user (see beginning of post), I get 0 rows back and no error messages.
Adding 'with execute as owner' solves the issue, but I'm not sure of the implications. Am I opening up the database to attacks (or even the complete server)?
If so, how to continue.
In an attempt to solve the issue I have given permissions to the user dsrpReader on the information_schema.columns but have no success. It did not work. This was just a try, I actually want to set up a dedicated user with some permissions that I can use in the 'with execute as 'limiteduser'.
View 2 Replies
View Related
Jan 15, 2015
I am logging into a SQL instance to run the following query:
DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_create_subdir N'sharemasterFULL' IF @ReturnCode <> 0 RAISERROR('Error creating directory.', 16, 1)
The share in which the folder is to be created has my account added with full permissions to create files. However this command fails unless I add the SQL Service account user with rights to the folder also.
Is this expected behaviour, is this something specific to extended stored procedures?
View 0 Replies
View Related
Mar 3, 2015
We need to create a pdf file from SQL server preferably from a stored procedure. Application will call the stored procedure and it should generate pdf. From my research it appears it can be done using various external tools with licensing/costs. But is it possible to do this within sql server database without additional costs? I read that this can be done by SSRS in SQL server but not sure if it is a good solution and if it is additional licensing..
View 3 Replies
View Related
Sep 21, 2015
Is it possible to pass a sum of vars to a SP ?I've tried this but it gives me an error
exec mysp
@param = (@var1 + @var2)
View 1 Replies
View Related
Dec 7, 2007
I am currently building an electricity quoting facility on my website. I am trying to insert the data which the user enters into 3 linked tables within my database. My stored procedure therefore, includes 3 inserts, and uses the @@Identity, to retrieve the primary keys from the first 2 inserts and put it as a foreign key into the other table.
When the user comes to the quoting page, they enter their contact details which goes into a client_details table, then they enter the supply details for their electric meter these get inserted into the meter table which is linked to client_details. The supply details which the users enters are used to calculate a price. The calculated price, then gets put into a quote table which is linked to the meter table. This all seems to work fine with my stored procedure.
However I want to be able to allow a user to enter more than one meter supply details and insert this into the meter table, with the same client_id for the foreign key. This will also generate another quote to insert into the quoting table. However I do not know how to get this to work.
Should I be looking at using Sessions and putting a SessionParameter on the client_id for the inserts for these additional meters??
View 4 Replies
View Related
Mar 7, 2008
Hello,
Is it possible to search in two tables, let's say table # 1 in specific field e.g. (age). If that field is empty then retrieve the data from table #1, if not retrieve the data from table # 2.
Is it possible to do it by using SQL Stored Procedure??
Thank you
View 4 Replies
View Related
May 12, 2004
ok, ill lay it out like this, and try not to be too confusing.
I need to check my trasaction table to see that the user with a UserID bought, keeping track(adding up) of how many times they bought the Product with a productID, , then i need to selcect items from product table with the productID for the items the user bought,
i know how to return the product information, and how to check the tranaction table and i think how to add up the amount of tranastions for that item
so bacially i need to select data from a product table, for the products that the user bought tahts stored in the transaction table.
well i tried not to be confusing, but i think i failed
View 1 Replies
View Related
Nov 15, 2006
Hi,If one uses a temporary table/ table variable within a storedprocedure, will it use the compiled plan each time the stored procedureis executed or will it recompile for each execution?Thanks in advance,Thyagu
View 1 Replies
View Related
Oct 22, 2007
Hi
I have one stored procedure which uses temp tables in it .
I am trying to use this stored procedure in my SSRS report as the source.
I am getting an error which says that the temporary table doesnot exist while validating the sql syntax in report.
Also, this stored proc executes in SSMS ithout any problem and giving the result as desired.
Can we use Temp tables in Stored proc and call that in SSRS?
If not, what is the workaround for that.
Thanks
View 13 Replies
View Related
Oct 17, 2006
Hi,
I was wondering if there was a way to create two temp tables within the same stored procedure.
Can we have two create statements one after the other?
Thanks
View 2 Replies
View Related
Apr 28, 2008
Hi,
I want write STORED PROCEDURE to join TWO tables.
I am displaying all the details here. Please help me.
INPUT DATA:
Table1
(OLD DATA)
SYMBOL CATEGORY SHARES Prev.Close Last Trade
ABB
ACC
AMBUJACEM
BHARTIARTL
BHEL
BPCL
CAIRN
CIPLA
DLF
DRREDDY
electrical
cement
cement
telecom
electrical
refinery
petrochemical
pharma
construction
pharma
211908375
187634983
1522375422
1897907446
489520000
361542124
1778399420
777291357
1704832680
168172746
1181.25
799.00
115.25
843.00
1850.70
382.60
256.20
226.60
676.00
613.35
1170.15
782.40
115.45
922.40
1870.00
392.90
255.80
224.20
667.85
616.55
Table2
(NEW DATA)
SYMBOL LAST TRADE
ABB
ACC
AMBUJACEM
BHARTIARTL
BHEL
BPCL
CAIRN
CIPLA
DLF
DRREDDY
1153.25
766.95
113.45
928.05
1866.30
390.90
258.70
214.55
668.50
625.20
OUTPUT DATA:
(OLD DATA) (NEW DATA)
as
SYMBOL CATEGORY SHARES Prev.Close Last Trade
ABB
ACC
AMBUJACEM
BHARTIARTL
BHEL
BPCL
CAIRN
CIPLA
DLF
DRREDDY
electrical
cement
cement
telecom
electrical
refinery
petrochemical
pharma
construction
pharma
211908375
187634983
1522375422
1897907446
489520000
361542124
1778399420
777291357
1704832680
168172746
1170.15
782.40
115.45
922.40
1870.00
392.90
255.80
224.20
667.85
616.55
1153.25
766.95
113.45
928.05
1866.30
390.90
258.70
214.55
668.50
625.20
View 1 Replies
View Related
Aug 26, 2014
We are planning to upgrade. We are using Sql 2008R2 now. Which is the better option migrating to SQL 2012 or migrating to 2014?I am thinking 2014 has memory optimized tables and updatable column stored index. So it is better option.
View 2 Replies
View Related
Apr 24, 2015
We have an issue where we are trying to replicate a DB using SQL replication, the Replication Monitor reports an error:
Cannot TRUNCATE TABLE 'dbo.xxx' because it is being referenced by object 'View_xxx'.
Although I am the DBA, I do not have an inside knowledge of this particular DB.
View 2 Replies
View Related
Dec 16, 2005
I am attempting to how to truncate list of tables using STP. That is decalre a cursor for a list table names and then to truncate the table names one by one.
The code below shows what I want to achieve. I want to truncate all the tables with names beginning with ZZ but this is failing. I have tried using both delete and truncate.
Is it possible and what do I need to do?
-- Code
SET QUOTED_IDENTIFIER Off
GO
SET ANSI_NULLS ON
GO
create PROCEDURE dbo.Empty_ZZ
AS
DECLARE @tablename sysname
DECLARE @localname varchar(50)
DECLARE ZZtablenames_cursor CURSOR FOR
select [name]
from sysobjects
WHERE [xtype] = 'U'
and name like 'ZZ %'
OPEN ZZtablenames_cursor
FETCH NEXT FROM ZZtablenames_cursor
into @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
set @localname = '[' + @tablename + ']'
TRUNCATE + @localname
FETCH NEXT FROM ZZtablenames_cursor
END
CLOSE ZZtablenames_cursor
DEALLOCATE ZZtablenames_cursor
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
View 4 Replies
View Related
Aug 12, 2013
I'm using Excel 2010 (if it matters), and I can execute a stored procedure fine - as long as it has no parameters.
Create a new connection to SQL Server, then in the Connection Properties dialog, specify
Command Type: SQL
Command Text: "SCRIDB"."dbo"."uspDeliveryInfo"
but if I want to pass a parameter, I would normally do something like
SCRIDB.dbo.uspDeliveryInfo @StartDate = '1/1/2010', @EndDate = GETDATE()
but in this case, I would want to pass the values from a couple of cells in the worksheet. Do I have to use ADO (so this isn't a SQL Server question at all?)
View 9 Replies
View Related
Dec 9, 2013
After my Stored Procedure has run, I need to call [URL] ..... file. Can this be done from my Stored Procedure?
View 2 Replies
View Related
Dec 17, 2013
I've been tasked with creating a stored procedure which will be executed after a user has input one or more parameters into some search fields. So they could enter their 'order_reference' on its own or combine it with 'addressline1' and so on.
What would be the most proficient way of achieving this?
I had initially looked at using IF, TRY ie:
IF @SearchField= 'order_reference'
BEGIN TRY
select data
from mytables
END TRY
However I'm not sure this is the most efficient way to handle this.
View 2 Replies
View Related
Mar 31, 2014
I have a sp. I alter stored procedure by adding some logic to that. How can I test that is there any functional impact by altering that stored procedure? How to prove to the team that the modifications doesn't impact any functionality?
View 5 Replies
View Related
Apr 15, 2014
To get the results of a stored proc into a table you always had to know the structure of the output and create the table upfront.
CREATE TABLE #Tmp (
Key INT NOT NULL,
Data Varchar );
INSERT INTO #Tmp
EXEC dbo.MyProc
Has SQL 2012 (or SQL 2014) got any features / new tricks to let me discover the table structure of a stored procedure output, - i.e treat it as a table
EXEC dbo.MyProc
INTO #NewTmp
or
SELECT *
INTO #NewTmp
FROM ( EXEC dbo.MyProc )
View 9 Replies
View Related
May 6, 2014
Only to a specific schema? Can this be done?
View 5 Replies
View Related
Jun 5, 2014
I have a really big stored proc that needs to be rolled out to various databases as part of db installs I run through SSIS.
The Stored proc is too long to run using Execute SQL Task. Is there another way that just running the create script manually.
View 9 Replies
View Related