Does anyone know how to save the output of a stored procedure into a table? I would like to be able to use some procedures such as the sp_helpfile and dbcc sqlperf(logspace) and save to output into a table. Does anyone have any ideas?
View 2 RepliesHi All,I am looking for a store procedure or any alternate method which save my html file(s) text (with or without tags) in my table column automatically when I upload my html file to my file system (local hard drive).any help will be appreciate.Thanks in advance.
View 7 Replies View RelatedI am running a select statment which retruns one row how I can assign that results to a memory variable ?
thanks
I have a table that has 5 values that I want to MSSQL to create a csv file for. After it is created, dump that csv file back into the database as a binary file. Is this possible?
View 1 Replies View RelatedHi
I am new to SQL programming. This is what I am trying to do in store procedure.
Alter a table - adding two columns.
Then update those columns
Both the above action in same procedure .
When I execute them - it complains that the columns I am adding in the first part of store procedure , does not exists.
So the SP looks like this
create store procedure <name> as
alter table <name> ADD
column_1 nvarchar (256),
column_2 nvarchar (256);
update table
set
column_1 = <condition>
column_2 = <condition>
The SQL complains -
invalid column name column_1
invalid column name column_2
Can some one help please...
hi, good day,
when i run query in query analyzer
exec sp_test '2006-07-21'
it show result , however when i run it using BCP tools
exec master..xp_cmdshell 'bcp "exec sp_test '2006-07-21' " queryout c: est.txt -c '
it give me error which is something like "invalid object #temp_tbl",
i do using temporary table in store procedure
how to we solve it ? seem that bcp not support temporary table ? really need guidance from expert here ,
thank you :eek:
I have a sp that will do two select from two table. now, can datareader read both table or only dataset can? if datareader can? how to handle it?
View 1 Replies View RelatedHI
I have a problem related Store Procedure, that i am trying to extact a value from Database (Like FirstName,LastName,Email Address) through Store Procedure and Display it in the DropDownList(Like: FirstName LastName ,(xyz@xyz.com)) , and this is working correctly.
Now i try to check the value at the same time if it is NULL value in the Database then pass EmptyString to the DropDownList Like ("" "" ,(xyz@xyz.com))
how i can do that in the store procedure.
Comments will be appreciated.
Hi people.
I need some help to export a table to Microsoft Access using a Store Procedure.
My Store Procedure will pupolate a temporary table with some information. After that I want that the store procedure export this table to Access.
Is there any script or command that can do it? And how can i use it into a Store Procedure?
Please, if possible give a example.
thanks in advance
:confused:
While writing store procedure in db most of Time i will Use common Table to write select queries for selecting more than seven table whether it reduce speed performance or it won't
;with cte
{
}
I have following hash table in C#.
Rank Slug
1 ABC
2 XYZ
3 PQR
I want to pass hash table or data table to the store procedure and use the looping inside the store procedure.
I have a INSERT INTO where i retunr the result from a store procedure. But I want to only insert the data if the row not already exist. How can i do that? (See Where xxxxxxxxxxxx).
I can't use a function as i store data in a temporary table in the store procedure.
--Get Generated Times
INSERT INTO @GeneratedTimes(
ResourceId ,
DateFrom ,
DateTo )
EXEC dbo.P_GenerateTimes @ApplicationId , @EventId , @FromDate , @ToDate , @WeekScheduleId , @FromTimeToBook , @ToTimeToBook
WHERE xxxxxxxxxxxxxxxxxx
now i want to learn how to make a stored procedure to insert a record to `purchase` table, and many records to `purchase_detail` table with transaction where the some value are passed from vb6 through the parameters. i've made a SP to insert 1 record to `purchase` table n 1 record to `purchase_detail` just for testing, so i set the disc value to 10. it works fine... --------------------------------------------------------------------------------- CREATE PROCEDURE `usp_save_purchase`(xpurch_id VARCHAR(10), xpurch_date VARCHAR(10), xsupp_id VARCHAR(10), xitem_id VARCHAR(10), xqty TINYINT(3), xprice DOUBLE(15,2)) BEGIN START TRANSACTION; INSERT INTO purchase(purch_id,purch_date,supplier_id) VALUES(xpurch_id, xpurch_date, xsupplier_id); INSERT INTO purchase_detail(purch_id,item_id,qty,price,disc) VALUES(xpurch_id, xitem_id, xqty, xprice, 10); COMMIT; END --------------------------------------------------------------------------------- what i need is something like that but i only pass 3 variables (purch_id, purch_date, and supp_id) to SP, and then the SP will insert 1 record of purchase to `purchase` table, and add the purchase items to `purchase_detail` automatically from `purch_temp` table, and use the disc rate based on `supplier_id` and `item_id` from supplier_disc table, which will be looked something like this: --------------------------------------------------------------------------------- CREATE PROCEDURE `usp_save_purchase`(xpurch_id VARCHAR(10), xpurch_date VARCHAR(10), xsupp_id VARCHAR(10)) BEGIN START TRANSACTION; INSERT INTO purchase(purch_id,purch_date,supplier_id) VALUES(xpurch_id, xpurch_date, xsupplier_id); /*start looping here get the disc rate for each items where supp_id = xsupplier_id and item_id = the item_id from purch_temp table, and save it in a local variable (let's say local_disc) INSERT INTO purchase_detail(purch_id,item_id,qty,price,disc) VALUES(xpurch_id, xitem_id, xqty, xprice, local_disc); */ COMMIT; END --------------------------------------------------------------------------------- can anyone help me please? thank you in advance...
View 1 Replies View RelatedI am looking for an example of a store procedure that will run a existing view and copy the results to a table. Every time it runs the table needs to be truncated. Will run once a day.
My view is a follows
SELECT PClass
FROM mydatabase.dbo.ProductClassDes
WHERE (ProductClass <> '_RBS') AND (ProductClass <> '_EDT') AND (ProductClass <> '_BMS') AND (ProductClass <> '_PAZ') AND (ProductClass <> '_PBC')
View results need to be copied to a table tblCurrentProductClasses that will only contain one field PClass.
Hi all,
I've a requirement to store the output of the stored procedure into temp. tables/ table varibles.
I've 4 select statements as my output of the stored procedure. How do I store the results of all the 4 select stmnts into 4 different temp tables.
Simplified SP is as...
Create procedure usp_test
as
begin
select c1,c2 from table1
select c3,4 from table2
select c9,c8 from table3
select c5,c7 from Table4
end
I'm expecting something like this...
declare @table1 table (c1, c2)
insert into @table1
Exec <Sp_Name>
select * from @table1
I know the above stmnt works, if my SP has only 1 select stmnt as output.
Please help me to acheive this for multiple select statements.
Thanks,
I have a code that is working, and want to create a store procedure in order to put the information of the running code into a new table.
View 7 Replies View RelatedI wanted to know if it is possible to run a stored proc (report) when a new record is inserted / updated into a certain table. Also if it could do so for records with a column that meet a certain criteria ie. sell description like '%test%'
View 2 Replies View RelatedI passed .net datatable from a .net app to a store procedure. From this store procedure, how to code to bulk insert (or another way) to SQL table?
View 7 Replies View RelatedI have on stored procedure which returns mote than one resultset i want that to store in two different temp table how can achieve this in SQL server.
Following is the stored procedure and table that i need to create.
create procedure GetData as begin select * from Empselect * from Deptend
create table #tmp1 (Ddeptid int, deptname varchar(500),Location varchar(100))
Insert into #tmp1 (Ddeptid , deptname ,Location )
exec GetData
create table #tmp (empid int , ename varchar(500),DeptId int , salary int)
Insert into #tmp (empId,ename,deptId,salary)
exec GetData
I am having one store procedure which use to load data from flat file to staging table dynamically.
Everything is working fine.staging_temp table have single column. All the data stored in that single column. below is the sample row.
AB¯ALBERTA ¯93¯AI
AI¯ALBERTA INDIRECT ¯94¯AI
AL¯ALABAMA ¯30¯
After the staging_temp data gets inserted into main table.my probelm is to handle such a file where number of columns are more than the actual table.
If you see the sample rows there are 4 column separated by "¯".but actual I am having only 3 columns in my main table.so how can I get only first 3 column from the satging_temp table.
Output should be like below.
AB¯ALBERTA ¯93
AI¯ALBERTA INDIRECT ¯94
AL¯ALABAMA ¯30
How to achieve above scenario...
I know I can call store procedure from store procedure but i want to take the value that the store procedure returned and to use it:
I want to create a table and to insert the result of the store procedure to it.
This is the code: Pay attention to the underlined sentence!
ALTER PROCEDURE [dbo].[test]
AS
BEGIN
SET NOCOUNT ON;
DROP TABLE tbl1
CREATE TABLE tbl1 (first_name int ,last_name nvarchar(10) )
INSERT INTO tbl1 (first_name,last_name)
VALUES (exec total_cash '8/12/2006 12:00:00 AM' '8/12/2006 12:00:00 AM' 'gilad' ,'cohen')
END
PLEASE HELP!!!! and God will repay you in kind!
Thanks!
There must be a difference, but I don't know what it is.
Can someone please explain the advantages and disadvantages of each method?
Thanks in advance.
Hi,
I've a large number of stored procedures that output result sets, as part of a test script it would be useful for me to be able to save the results from these stored procedures into temporary tables, without having to pre-create these tables, i.e. use the equivalent of a select * into statement.
Is this possible ?
Sean
I have had the same problem. In the past I had the sql commands in the code. Last project was with SQL2000 and I decided to add store procedures part as a learning tool and part to better control the code. With the current project I am using SQL2005 and have a ummm fun time with the learning curve.
Below is my stored procedure.
USE [AdventureWorks]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[WDR_CountryAdd]
(
@CountryName nvarchar(50),
@CountryID int OUTPUT
)
AS
INSERT INTO Country
(
CountryName
)
VALUES
(
@CountryName
)
SELECT
@CountryID = @@Identity
When I hit the execute button, I get the following error.
Msg 208, Level 16, State 6, Procedure WDR_CountryAdd, Line 24
Invalid object name 'WDR_CountryAdd'.
I I know that it does not exist as that is what I am trying to do, create it. I have checked to make sure that I am on the currect database. So how do I associate this procedure with the data base? Do I need to write a SQL query to create the procedure? Maybe that is the answer and then just modify it.
I would like to find out how to capture record count for a table in oracle using SSIS and then writing that value in a SQL Server table.
I understand that I can use a variable to accomplish this task. Well first issue I run into is that what import statement do I need to use in the design script section of Script Task. I see that in many examples following statement is used for SQL Server databases:
Imports System.Data.SqlClient
Which Import statement I need to use to for Oracle database. I am using a OLE DB Connection.
any idea?
thanks
Hi, I need an ouput of difference in two Strings
Declare @StringA varchar(100), @StringB varchar(100)
@StringA = 'A,B,C'
@StringB = 'A,C,C'
The values are seperated in the strings by ','
Since B value in first string changed to C value in the second string
I want to return the difference between the two strings as 'B,C'
Can anyone help me with this.
Thanks
is it possible to return multiple output values in stored prcodeure
for eg.
Create Proc XYZ
( @x int output,@y int output,@z int ouput)
As
Begin
SET @X= Select from table
SEt @Y=SELECT from table
set @z=slect from table
end
Hello everyone I have a stored procedure that I am trying to save datetime data to at the moment I can create a string that will output 25/05/2007 18:30 Does anyone know the best way to get this into the correct format for my SP parameter
cmdPublish.Parameters.Add(new SqlParameter("@datPublishDate", System.Data.SqlDbType.DateTime));
cmdPublish.Parameters["@datPublishDate"].Value = ????
Thanks
Hi all.
I want something like this :
create proc myProc ( @num int ,@name varchar(80))
as
select @num = max(field1) from @name
but there is a problem that the @name is a varchar and the error is :
Server: Msg 170, Level 15, State 1, Procedure sp_myProc, Line 3
Line 3: Incorrect syntax near '@name'.
I tried
select @num = max(field1) from object_id(@name)
and the error was the same
is ther another way than :
exec ("declare @num int select @num = max(field1) insert into anotherTable values( @num) " ) ?
thx
Eyal Peleg
Hi all,
I have 2 tables, Order and Payment
Order
Order_NumberCust_NumberOrder_Date
10001 C1 23-May
10002 C2 24-May
10003 C1 25-May
10004 C3 28-May
Payment
Order_Number|Card_Type|Card_#|Merchant
10001 Gift Card1234null
10001 Gift Card1235null
10001 Gift Card 1236null
10001 Credit Cardxxxxprd
10002 Credit Cardxxxxprd
10003 Credit Card xxxxprd
10004 Credit Card xxxx prod
I have to populate th below table to track last gift card used for each cust_number.
1. last gift card used for each customer, each order
2. In a single order , if card used is gift card, last gift card used is gift card itself. if card used is a credit card, then the max gift card number from with in the order.
3. First time if a customer uses a credit card, then last gift card used is defaulted to 99 for merchant = prd and 88 for merchant = prod
4. In a new order, a past customer only uses a gift card, then last gift card used is gift card from his previous order.
[b]Last_Gift_Card
Cust_No|Order_number|card_number|last_gift_card
C11000112431234
C11000112351235
C11000112361236
C110001xxxx1236
C210002xxxx99
C110003xxxx1236
C310004xxxx88
Please help me with the sql.
I tried this using subqueries to find the max gift card for a customer for an order and could get the last gift card used correctly for credit cards for an order, but not able to insert the gift card from a previous order if the new order has only credit card as in for customer C1.
Thanks.
I am working on a SSIS package. I want error records to be redirected to a different table. Natively, the package passes the Error Code and Column Code (don't know what to call it, but it's a number). I found a script to get the error description, but can't find an equivalent to get the Column name.
Any ideas?
Row.ErrorDescription = _
Me.ComponentMetaData.GetErrorDescription(Row.ErrorCode)
I'm just starting to get a handle on parameters. I now want to create a parameter that is "multi-value". How do I get this to work? I have the report parameter set up, I just don't know how to actually get the report to use it.
Does it go in my dataset query? ie select 1,2,3 from table where 1 = @1
if so, how do you do this for a multi?
I have in the past created stored procedures using SQL Server 2000. It was easy to do. Now I am using SQL Server 2005 and the whole process is different and confusing to me. I performed the following steps to create a stored procedure:
1.) In SQL Server management studio, I wen to the folder named "Stored Procedures"2.) I right clicked on this folder and selected "New Stored Procedure..."3.) A generic sql server stored procedure is created for me.4.) I modify the stored procedure and now want to save it?
Now where do I go from here? How should I properly save this new stored procedure and where should I save it?I noticed that a generic name is assigned such as SQLQuery13.sql, but I want to name it something else.
I actually saved the new stored procedure but I can't see it listed under the "Stored Procedures" folder. I even tried doing a refresh.