Hi,
Will someone please tell me how to use
"Destination adapter in store procedure" .............
I'm using Sql server 2005, instead of using BCP i have to use
"Sql server destination adapter" in my store procedure,
Is it possible to implement destination adapter in store procs,
if it is yes please let me know how
Thanks and Regards
Altaf Hussain Nizamuddin
Hi,
Will someone please tell me how to use
"Destination adapter in store procedure" .............
I'm using Sql server 2005, instead of using BCP i have to use
"Sql server destination adapter" in my store procedure,
Is it possible to implement destination adapter in store procs,
if it is yes please let me know how
Thanks and Regards
Altaf Hussain Nizamuddin
Hi,
Will someone please tell me how to use
"Destination adapter in store procedure" .............
I'm using Sql server 2005, instead of using BCP i have to use
"Sql server destination adapter" in my store procedure,
Is it possible to implement destination adapter in store procs,
if it is yes please let me know how
Thanks and Regards
Altaf Hussain Nizamuddin
I'm trying to figure this out
I have a store procedure that return the userId if a user exists in my table, return 0 otherwise
------------------------------------------------------------------------
Create Procedure spUpdatePasswordByUserId
@userName varchar(20),
@password varchar(20)
AS
Begin
Declare @userId int
Select @userId = (Select userId from userInfo Where userName = @userName and password = @password)
if (@userId > 0)
return @userId
else
return 0
------------------------------------------------------------------
I create a function called UpdatePasswordByUserId in my dataset with the above stored procedure that returns a scalar value. When I preview the data from the table adapter in my dataset, it spits out the right value.
But when I call this UpdatepasswordByUserId from an asp.net page, it returns null/blank/0
passport.UserInfoTableAdapters oUserInfo = new UserInfoTableAdapters();
Response.Write("userId: " + oUserInfo.UpdatePasswordByUserId(txtUserName.text, txtPassword.text) );
Do you guys have any idea why?
Hi All,
I have built a custom flat file destination adapter but it appears that the code is not working. When I debug the process I notice that the ProcessInput section is called multiple times. The first time it looks like everything is working, then it call it again and there is no inpout from the DTSInput90.
Why would it do this?
Thanks
Mike
Quick question...
I have a conditional split transform in a dataflow...
One of the outputs of the conditional split will take records that are just not needed...
Do I have to send that output into the Trash Destination adapter? Or can I just let those records hang?
Will there be a memory leak if I don't use a trash adapter?
I'm just thinking about if I move the package I'd have to go reinstalling the trash adapter every time...
Thanks
I would Like to know whether anyone tried to SSIS from SQL to AS400.
I am getting Exception when I set destination adapter ( for As400).
Whats the acceptable value .
Hi,
I have conditional split on a boolean variable, when the value is true, case1 output goes to Oledb Destination, defaukt Output goes to Sql Server Destination.
When I run the package with value "true" the package runs, but when i run the package with bool value "false", the data is inserted through oledb but Sql Server destination fails.
Can any one tell me Why.
[SQL Server Destination [292]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.".
I don't have any linked server.
thanks
Dharmbir
Hi,
I'm getting some unexpected behaviour from my SSIS packages when targeting tables that are being referenced by Indexed Views. There's two separate issues:
1. When writing into a pair of tables with a SuperType / SubType relationship concurrently with a pair of SS destinations I'm getting deadlocks between the two. Removing the index on the view that references both of these fixes the problem.
2. Much odder, I'm getting some extremely long waits (10 times longer than the whole package should take to run!) from an SS Destination adapter even when there's no data in the flow for it to bulk insert. Again, removing the indexed views that reference the destination table fixes the problem.
The views aren't mine, and (apparently) are required by the reporting app (BO), so removing them isn't really an option. I realise that there's quite a lot of overhead to maintaining indexed views, but unfortunately, the project is on a very tight timeline, so I can't look into it in as much detail as I'd like.
I was wondering if anyone's experienced any similar issues, or would have any ideas as to where to start investigating?
Thanks a lot
Mark
I didn't want to deal with any truncation issues so I edited the SQL created by the Import Wizard. I made the fields VARCHAR instead of NVARCHAR and changed the size from the default 50 to 250.
Now I have a triangle warning on the OLE DB Destination control. So I am wondering, is it limited to only handling 50 characters?
IanO
I am writing a custom task to import data from delimited files into SQL tables. I use the standard Flat File Source adapter, a custom transformation to add a URN column and a filename column to the data and the standard OLEDB Destination Adapter.
Most of my test data files work fine except for ones with a lot of columns (around 350 columns). I get an error when I call the ReinitializeMetaData() method for the destination adapter.
Q1) Is there a restriction on the number of columns (or data row size) that can be imported into an OLEDB Destination Adapter?
Q2) The reason I use this adapter rather than the SQL Server Destination Adapter is that I need to set the destination table name using a variable. I don't believe I can do this with the SQL Server Destination Adapter. Is this the case?
Q3) Anyone know of a better/alternative way of acheiving the above? One way I have thought of is to create a custom destination adapter using the SQL Server Destination adapter as the base but I'm not sure whether this is a) possible and b) worth the hassle.
Hope someone can help...
Cheers
Nick
Hi,
Will someone please tell me what is the difference between Destination adapter and bcp in Sql server 2005 ?
Thanks and Regards
Altaf Nizamuddin
It€™s always said that using the SQL Server Destination adapter is much faster when loading data to a SQL Server database. My question is, if I have a SQL 2000 database, can I still use the SQL Server Destination adapter, or is it only for SQL 2005 db? Is OLE DB Destination adapter my best bet? My data files(flat files), SSIS package and destination SQL 2000 db are all in the same physical server.
View 1 Replies View Related 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!
My Pocket PC application exports signature as an image. Everything is fine when choose Use SQL statements in TableAdapter Configuration Wizard.
main.ds.MailsSignature.Clear();
main.ds.MailsSignature.AcceptChanges();
string[] signFiles = Directory.GetFiles(Settings.signDirectory);
foreach (string signFile in signFiles)
{
mailsSignatureRow = main.ds.MailsSignature.NewMailsSignatureRow();
mailsSignatureRow.Singnature = GetImageBytes(signFile); //return byte[] array of the image.
main.ds.MailsSignature.Rows.Add(mailsSignatureRow);
}
mailsSignatureTableAdapter.Update(main.ds.MailsSignature);
But now I am getting error "General Network Error. Check your network documentation" after specifying Use existing stored procedure in TableAdpater Configuration Wizard.
ALTER PROCEDURE dbo.Insert_MailSignature( @Singnature image )
AS
SET NOCOUNT OFF;
INSERT INTO MailsSignature (Singnature) VALUES (@Singnature);
SELECT Id, Singnature FROM MailsSignature WHERE (Id = SCOPE_IDENTITY())
For testing I created a desktop application and found that the same Code, same(Use existing stored procedure in TableAdpater Configuration Wizard) and same stored procedure is working fine in inserting image into the table.
Is there any limitation in CF?
Regards,
Professor Corrie.
My store Procedure is not save in Strore Procedure folder at the time of saving it give me option to save in Project folder and file name default is SQLQuery6.sql when i save it after saving when i run my store procedure
exec [dbo].[SP_GetOrdersForCustomer] 'ALFKI'
I am getting below error :
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'dbo.SP_GetOrdersForCustomer'.
Trying to create a package that will copy all user stored procs from one database to another - on the same server.
I am using a Transfer SQL Server Objects Task to do this.
I am getting the error below.
Warning: 0x8002F356 at STORED PROCS, Transfer SQL Server Objects Task: Stored procedure "{0}" already exists at destination.
DropObjectsFirst is set to True
Can anyone help? Thank you in advance.
Hi guysi would really appreciate your help here. what am i doing wrong with this stored procedure ALTER PROCEDURE usp_CreateNewCustomer( @LName as varchar(50), @FName as varchar(50), @Dept as varchar(50)=NULL, @PhoneType as varchar(50)=NULL, @Complete as Bit=NULL, @CustomerID as Int OUTPUT, @FaxModel as varchar(50)=NULL, @FaxNumber as varchar(50)=NULL, )ASINSERT INTO [CustomerInfo] ([LName], [FName], [Dept], [PhoneType], [Complete]) VALUES (@LName, @FName, @Dept, @PhoneType, @Complete)SELECT SCOPE_IDENTITY()INSERT INTO Extras (CustomerID, FaxModel, FaxNumber) VALUES (@CustomerID, @FaxModel, @FaxNumber)RETURN It keeps on complaning "'usp_CreateNewCustomer' expects parameter '@CustomerID', which was not supplied."thanks
View 4 Replies View RelatedHi all,
I have a few question regarding SPROC. Firstly, I want to create a sp, that will return a multiple column of data, how do you achieve that. Secondly, I want to create a store procedure that will return a multiple columns in a cursor as an output variable. Any help will be much appreciated. Can you have more then 1 return parameters???
Thanks.
Kabir
I have asp.net web application which interface with SQL server,
I want to run store procedure query of SQL using my asp.net application.
How to declare connectons strings, dataset, adapter etc to run my store procedure resides in sql server.
for Instance Dim connections as string,
Dim da as dataset, Dim adpt as dataadapter. etc
if possible , then show me completely code so I can run store procedure using my button click event in my asp.net application
thank you
maxmax
I am not sure if it is right place to ask the question. I have a store procedure in which funcitons are called and several temp tables are created. When I use sql analyzer, it runs fast. But when I called it from asp.net app, it runs slow and it waits and waits to get data and insert into temp tables and return. Though I have used with (nolock), it still not imprvoed. Any suggestions? Thanks in advance.
NetAdventure
Hello, I am very new to ASP.NET and SQL Server. I am a college student and just working on some project to self teaching myself them both. I am having a issue. I was wondering if anyone could help me.
Ok, I am creating a web app that will help a company to help with inventory. The employee's have cretin equipment assign to them (more then one equipment can be assigned). I have a search by equipment and employee, to search by equipment the entire database even then equipment that isn't assigned with the employee's it shows only that employees and if they have any equipment. They can select a recorded to edit or see the details of the record. The problem i am having is that the Info page will not load right. I am redirected the Serial Number and EmployeeID to the info page. I got everything working except one thing. If a person has more then one equipment when i click on the select it redirect and only shows the first record not the one I selected. and when i change it, a employee that doesn't have equipment will not show up totally.
SELECT E.FirstName AS UserFirstName, E.LastName AS UserLastName, eqtype.Description AS UserEquipType, empeq.UserEquipID, E.EmployeeID, E.cwopa_id, eq.IPAddress, eq.Location, eq.DataLine3, eq.DataLine2, eq.Voice, eq.DataLine1, eq.FloorBox, eq.Comments, eq.SerialNo, eq.Model, eq.Brand, eq.TagNo, eq.PurchaseLease, eq.WarrantyExpDate, eq.PhoneType, eq.PhoneNum, eq.CellNumber, eq.DataNumber, eq.SIDNumber, eq.Carrier
FROM dbo.EMPLOYEES AS E LEFT OUTER JOIN dbo.EMPLOYEES_EQUIP AS empeq ON empeq.EmployeeID = E.EmployeeID LEFT OUTER JOIN dbo.EQUIPMENT AS eq ON eq.EquipID = empeq.EquipID LEFT OUTER JOIN dbo.EQUIP_TYPE AS eqtype ON eqtype.EquipTypeID = eq.EquipTypeID WHERE E.EmployeeID = @EmployeeID and eq.SerialNo=@SerialNo
EmployeeID and SerialNo are primary keys.
I was thinking maybe create a store procedures, but I have no idea how to do it. I created this and no syntax error came up but it doesn't work
CREATE PROCEDURE dbo.inventoryinfo AS
DECLARE @EmployeeID int DECLARE @SerialNo varchar(50)
IF( @SerialNo is NULL)
SELECT E.FirstName AS UserFirstName, E.LastName AS UserLastName, eqtype.Description AS UserEquipType, empeq.UserEquipID, E.EmployeeID, E.cwopa_id, eq.IPAddress, eq.Location, eq.DataLine3, eq.DataLine2, eq.Voice, eq.DataLine1, eq.FloorBox, eq.Comments, eq.SerialNo, eq.Model, eq.Brand, eq.TagNo, eq.PurchaseLease, eq.WarrantyExpDate, eq.PhoneType, eq.PhoneNum, eq.CellNumber, eq.DataNumber, eq.SIDNumber, eq.Carrier
FROM dbo.EMPLOYEES AS E LEFT OUTER JOIN dbo.EMPLOYEES_EQUIP AS empeq ON empeq.EmployeeID = E.EmployeeID LEFT OUTER JOIN dbo.EQUIPMENT AS eq ON eq.EquipID = empeq.EquipID LEFT OUTER JOIN dbo.EQUIP_TYPE AS eqtype ON eqtype.EquipTypeID = eq.EquipTypeID WHERE E.EmployeeID = @EmployeeID
ELSE
SELECT E.FirstName AS UserFirstName, E.LastName AS UserLastName, eqtype.Description AS UserEquipType, empeq.UserEquipID, E.EmployeeID, E.cwopa_id, eq.IPAddress, eq.Location, eq.DataLine3, eq.DataLine2, eq.Voice, eq.DataLine1, eq.FloorBox, eq.Comments, eq.SerialNo, eq.Model, eq.Brand, eq.TagNo, eq.PurchaseLease, eq.WarrantyExpDate, eq.PhoneType, eq.PhoneNum, eq.CellNumber, eq.DataNumber, eq.SIDNumber, eq.Carrier
FROM dbo.EMPLOYEES AS E LEFT OUTER JOIN dbo.EMPLOYEES_EQUIP AS empeq ON empeq.EmployeeID = E.EmployeeID LEFT OUTER JOIN dbo.EQUIPMENT AS eq ON eq.EquipID = empeq.EquipID LEFT OUTER JOIN dbo.EQUIP_TYPE AS eqtype ON eqtype.EquipTypeID = eq.EquipTypeID WHERE E.EmployeeID = @EmployeeID and eq.SerialNo=@SerialNoGO
Also, when a user selects the select button in either Employee search or Equipment search it redirects them to info.aspx. Should I create a different aspx page for both? But I don't believe I should do that. I don't know if I gave you enough information or not. But if you could give examples that would be wonderful! Thanks so much Nickie
I want to know when does SQL flushes Store Procedure from the cache.
Thank You,
Piyush Patel
Hi, All,
I need to write a sales report which needs to seperate total from direct sale and agentsale. The report looks like this( in the table of query,we have agentnumber, productname, sales, month
Month salefromagent directsale total
Jan 1100 2300 3400
Feb 800 500 1300
..........
Dec
---------------------------------
I know we can handle this in the program use two queries.
But is there a way to do it use store procedure and then pass the result of store procedure to the ASP program. I am trying to write my first store procedure, thanks for any clue.
Betty
Hi all,
I need to write a sales store procedure.
The sales summary is basically group by agentCode.
But the problem is some agents have subagents.
i.e., in the sales table.
one agent 123456 has many subagents whose agentCode start with 17. And I want to group sales for all subagents who have agentCode 17XXXX to agent 123456's sales.
what should I do in the store procedure.
Thanks
Betty.
I am trying to create a store procedure that look at a table and only maintain 30 worth of data only forever. The data that fall outside the 30 days need to be deleted. Can anyone help me with this?
Thanks
Lystra
I would like to create a employee store procedure to insert a record and mean while i can retrieve the EmployeeID for the record just inserted. Can you show me how to create a store procedure like this. Now i have a insert store procedure in the following:
CREATE PROCEDURE AddEmployee
(@efname nvarchar(20),
@elname nvarchar(20)
)
AS
insert into tblEmployeeName
(EmployeeFName, EmployeeLName)
values (@efname,@elname)
GO
Thanks.
I created a batch file to run a DTS saved as a STRUCTURED STORE FILE. Works fine on the command promt.
Can I use a SPROC to call something like a shell command to execute that batch file? If yes, how is this be possible?
Thanks
This is an Oracle store procedure. Can Any body help me to convert it into SQL Server stored Procedure
PROCEDURE CALC_PERC (DB_ID IN NUMBER, LAT_TYPE IN CHAR) IS
Tot_work_all number(12,2);
Bid_tot number(12,2);
Ewo number(12,2);
Overruns number(12,2);
Underruns number(12,2);
Contr_tot_all number(12,2);
sContractType ae_contract.contr_type%type;
BEGIN
select sum(nvl(tamt_ret_item,0) + nvl(tamt_paid_item,0))
into Tot_work_all
from valid_item
Where db_contract = db_id;
Select sum(Contq * Contr_Price) into Bid_tot
From Valid_item
Where nvl(New_Item,'N') <> 'Y'
and db_contract = db_id;
Select sum(Qtd * Contr_price) into Ewo
From Valid_item
Where nvl(New_item,'N') = 'Y'
and db_contract = db_id;
Select Sum((Qtd-Nvl(Projq,0))*Contr_Price) into Overruns
From Valid_item
Where Qtd > Nvl(Projq,0)
and db_contract = db_id
and nvl(New_Item,'N') = 'N';
IF LAT_type <> 'R' THEN
Select Sum((Nvl(Projq,0)-Contq) * Contr_Price) into Underruns
From Valid_item
Where Nvl(Projq,0) < Contq
and db_contract = db_id
and nvl(New_Item,'N') = 'N';
ELSE
Select Sum((Nvl(Qtd,0)-Contq) * Contr_Price) into Underruns
From Valid_item
Where Nvl(Qtd,0) < Contq
and db_contract = db_id
and nvl(New_Item,0) = 'N';
end if;
Contr_tot_all:= NVL(Bid_tot,0) +NVL(ewo,0) +NVL(overruns,0)
+NVL(underruns,0);
IF Contr_tot_all = 0 THEN
Select Contr_type into sContractType from ae_contract where db_contract = db_id;
IF sContractType = 'A' OR sContractType = 'T' THEN
--If the divisor is zero here, it's not an error.
update ae_contract set perc_compu = 0 where db_contract = db_id;
ELSE
--If the divisor is zero here, it would be an error
update ae_contract set perc_compu = 100 * tot_work_all/contr_tot_all where db_contract = db_id;
END IF;
Else
--Here we have a real number to calculate, so go ahead and do your stuff!
update ae_contract set perc_compu = 100 * tot_work_all/contr_tot_all where db_contract = db_id;
END IF;
END;
1. can a stored procedure call itself? if else how ?>
2. how to run a job for a stored procedure to exucte every day.
hello
iam new to store procedures.can anyone tell me how do i put in this sql statement into store procedure
SELECT * FROM PortMaster WHERE PortCode= '" & Request.QueryString("PortCode") & "' "
TheProblem is coming in ,how do i put "Request.QueryString("PortCode")" into sql queranalyzer.iam using this in .aspx page.
depending on querystring the record is selected.
hello
iam using store procedure to open the the selected rows in datagrid into Excel.but iam getting blank spreadsheet.the querystrings are getting passed but the rows turn out to be blank.
if i do not use store procedure than everything is ok.the querystring contains multiple strings so as to display it in excel.
the stroe procedure is
CREATE PROCEDURE Employee_ExcelDisplayHO(@ID varchar(100))
AS
SELECT * FROM employee_master WHERE empuser_id IN (@ID)
and this is the statement that i use in my .aspxpage to call store procedure
Dim Cmd As New SqlDataAdapter("Employee_ExcelDisplayHO", myconn)
cmd.SelectCommand.CommandType = CommandType.StoredProcedure
cmd.SelectCommand.Parameters.Add(New SqlParameter("ID", SqlDbType.VarChar, 100))
cmd.SelectCommand.Parameters("@ID").Value = Request.QueryString("ID")
any suggestions wht may be wrong
iam using store procedure to return certain no. of columns frm table.the query is
SELECT empuser_id,FullName,CAddress,PAddress,PhResidence,Mobile FROM employee_master WHERE empuser_id IN (" & txtID.Text & ")
i return it to datagrid in my .aspx(using VB.net) page.but i am getting blank datagrid. when i write the above query in my codebehind it runs normally and datagrid is populated with records.
i tried this in query analyzer as well it runs normally returing rows.
only in store procedure it gives me blank record.
any suggestions what is wrong ?