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

SQL Server 2012 :: Find All Tables Used In Any Stored Procedure

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

Truncate Table Via Stored Procedure

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

SQL Server 2005 JDBC Driver 1.1 Truncate Output Parameter From A Stored Procedure To 4000 Char.

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

SQL Server 2012 :: Executing Dynamic Stored Procedure From A Stored Procedure?

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

SQL Server 2012 :: CLR Procedure Takes Ages To Pass TVP To Stored Procedure?

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

SQL 2012 :: Store Procedure - Inserting Same Data Into Two Tables

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

SQL 2012 :: Getting Returned Value Of A Stored Procedure?

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

Truncate Or Rebuild Tables?

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

Truncate All Tables In DB With RI Intact

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

How Can I Truncate Multiple Tables?

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

SQL 2012 :: Check If Particular Stored Procedure Is Recompiling Or Not

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

SQL 2012 :: Executing Stored Procedure As Different User

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

SQL 2012 :: Extended Stored Procedure Permissions

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

SQL Server 2012 :: Generate PDF From Stored Procedure

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

SQL Server 2012 :: Passing Sum To Stored Procedure

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

Inserting Into Tables Using Stored Procedure

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

How To Search In 2 Tables By Using SQL Stored Procedure??

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

Stored Procedure Checking 2 Tables

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

Using Temporary Tables Within The Stored Procedure

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

Temp Tables In Stored Procedure.

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

Creation Of Two Tables In A Stored Procedure

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

Joining Two Tables In Stored Procedure

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

SQL 2012 :: Memory Optimized Tables And Updatable Column Stored Index

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

SQL 2012 :: Replication Fails - Cannot Truncate Table

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

Truncate Tables With Spaces In Their Names Using STP

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

SQL 2012 :: Executing Parameterized Stored Procedure From Excel

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

SQL Server 2012 :: Run ASPX File From Stored Procedure?

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

SQL Server 2012 :: Stored Procedure With One Or More Input Parameters?

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

SQL 2012 :: Is There Any Functional Impact By Altering Stored Procedure?

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

SQL 2012 :: Get Structure Of Stored Procedure Output Table

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

SQL 2012 :: Assign Create Stored Procedure Permissions?

May 6, 2014

Only to a specific schema? Can this be done?

View 5 Replies View Related

SQL 2012 :: Create Stored Procedure In SSIS Package

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







Copyrights 2005-15 www.BigResource.com, All rights reserved