Hi
My PM and me are on a discussion. He wants SSIS to simply upload the source excel files in staging and do rest of the ETL in SQl server stored procedure which I feel is primitive.
I have suggested him to use SSIS for whole ETL and not Stored procedure( method which indistry has discarded)
He sayd Stored procedure are more efficient than SSIS.
Please guide with some facts.
Hi,
I Have a stored procedure named pPatientFindByID that return one patient record from the Client.Patient table. The stored procedure has one parameter @patientID uniqueidentifier. i have set the NOCOUNT ON before running the SQL statement and making it OFF after it finishes execution. The table has a primary key ( clustered ) on patientID. The table has 66840 records in it. The index IS 0 (ZERO) percent fragmented and 3 level depth.
The stored proc returnes null or 1 record maximum. My problem is that the stored procedure takes about 2268 microsecond (2 millisecond) on average. while i execute the same SQL using sp_executeSQL it takes about 710 microsecond (< 1 millisecond). That is 3 time faster than stored procedure.
The SET OPTION are same for both the statement.
To troubleshoot the problem i have enabled the trace and captured the events. there were no recompile for both the stored proc and sp_executeSQL and not even the statement level recompilation.
I am trying to figure out why my stored procedure is taking longer than sp_executesql. Both the (stored proc and sp_executeSQL) has the same execution plan.
This is affecting our application performance. Does anyone know about this or have experience the same problem ever? your help would be greatly appriciated.
Data
1. Stored Procedure
/****** Object: StoredProcedure [dbo].[pPatientFindByID] Script Date: 12/24/2007 11:19:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[pPatientFindByID]
@patientID uniqueidentifier
WITH EXECUTE AS OWNER
AS
BEGIN
SET NOCOUNT ON
SELECT
[patient].[AVINumber] AS 'AVINumber',
[patient].[BirthDate] AS 'BirthDate',
[patient].[BreedID] AS 'BreedID',
[patient].[ChangeDate] AS 'ChangeDate',
[patient].[ChangeWorkstationID] AS 'ChangeWorkstationID',
[patient].[ClientID] AS 'ClientID',
[patient].[Color] AS 'Color',
[patient].[County] AS 'County',
[patient].[CountyRabiesTagNumber] AS 'CountyRabiesTagNumber',
[patient].[CreateDate] AS 'CreateDate',
[patient].[CreateWorkstationID] AS 'CreateWorkstationID',
[patient].[DeathDate] AS 'DeathDate',
[patient].[DeathReasonCode] AS 'DeathReasonCode',
[patient].[FirstVisitDate] AS 'FirstVisitDate',
[patient].[HasAllergy] AS 'HasAllergy',
[patient].[HasBitesOrScratch] AS 'HasBitesOrScratch',
[patient].[HasChronicCondition] AS 'HasChronicCondition',
[patient].[HasDeclinedPreventiveCare] AS 'HasDeclinedPreventiveCare',
[patient].[HasMedicalAllergy] AS 'HasMedicalAllergy',
[patient].[HospitalID] AS 'HospitalID',
[patient].[IsBites] AS 'IsBites',
[patient].[IsBitesInFear] AS 'IsBitesInFear',
[patient].[IsClinicalStudy] AS 'IsClinicalStudy',
[patient].[IsLitters] AS 'IsLitters',
[patient].[IsMixBreed] AS 'IsMixBreed',
[patient].[IsScratches] AS 'IsScratches',
[patient].[LastVisitDate] AS 'LastVisitDate',
[patient].[ManufacturerNameOfAVIChip] AS 'ManufacturerNameOfAVIChip',
[patient].[ManufacturerNameOfMicrochip] AS 'ManufacturerNameOfMicrochip',
[patient].[MicrochipNumber] AS 'MicrochipNumber',
[patient].[Name] AS 'Name',
[patient].[Note] AS 'Note',
[patient].[PatientID] AS 'PatientID',
[patient].[RabiesTagNumber] AS 'RabiesTagNumber',
[patient].[SexCode] AS 'SexCode',
[patient].[SpeciesID] AS 'SpeciesID',
[patient].[Status] AS 'Status',
[patient].[SystemLog] AS 'SystemLog',
[patient].[Weight] AS 'Weight',
[patient].[WellnessplanLevel] AS 'WellnessplanLevel',
[patient].[WellnessplanValue] AS 'WellnessplanValue'
FROM
[Client].[Patient] [patient]
WHERE
[PatientID] = @patientID
SET NOCOUNT OFF
END
2. sp_executeSQL
set nocount on
execute
sp_executeSQL N'
SELECT [patient].[AVINumber]
,[patient].[BirthDate]
,[patient].[BreedID]
, [patient].[ChangeDate]
, [patient].[ChangeWorkstationID]
, [patient].[ClientID]
, [patient].[Color]
, [patient].[County]
, [patient].[CountyRabiesTagNumber]
, [patient].[CreateDate]
, [patient].[CreateWorkstationID]
, [patient].[DeathDate]
, [patient].[DeathReasonCode]
, [patient].[FirstVisitDate]
, [patient].[HasAllergy]
, [patient].[HasBitesOrScratch]
, [patient].[HasChronicCondition]
, [patient].[HasDeclinedPreventiveCare]
, [patient].[HasMedicalAllergy]
, [patient].[HospitalID]
, [patient].[IsBites]
, [patient].[IsBitesInFear]
, [patient].[IsClinicalStudy]
, [patient].[IsLitters]
, [patient].[IsMixBreed]
, [patient].[IsScratches]
, [patient].[LastVisitDate]
, [patient].[ManufacturerNameOfAVIChip]
, [patient].[ManufacturerNameOfMicrochip]
, [patient].[MicrochipNumber]
, [patient].[Name]
, [patient].[Note]
, [patient].[PatientID]
, [patient].[RabiesTagNumber]
, [patient].[SexCode]
, [patient].[SpeciesID]
, [patient].[Status]
, [patient].[SystemLog]
, [patient].[Weight]
, [patient].[WellnessplanLevel]
, [patient].[WellnessplanValue]
FROM [Client].[Patient] [patient] WHERE [PatientID] = @patientID
',N'@patientID uniqueidentifier'
,'B1ABC1EA-D4AB-DB11-921E-00087434402F'
set nocount off
3- Trace Data
Name Event TextData Duration
SQL:BatchCompleted 12 exec dbo.pPatientFindByID @patientID='B1ABC1EA-D4AB-DB11-921E- 00087434402F' 2268
SQL:BatchCompleted 12 set nocount on execute sp_executeSQL N' SELECT [patient].[AVINumber] 710
DBA
I have an stored procedure that takes 23 seconds to execute. However, the same query is executed in 4 seconds.
All the tests where made using the Query Analizer.
What could be wrong?
Thanks a lot in advance.
Hi !!
We are in a situaltion.... and don't have any answer...
We have 3 stored procedure. We want to write output of all the 3 stored procedure into one text file.. How do we do this?
We thought about DTS but its not solving our problem...
I need to display the records from a start record number to an end record number. I need a stored procedure for this. The procedure should accept the query, start record number and the end record number as parameters. The procedure should be optimized such that it would work fast with even 1 lac records or more in the database table. Please help.....
View 1 Replies View RelatedHi i have been working on these stored procedures for what seems like ages now and i just cant understand why they dont work. I have followed many tutorials and think i have the correct synat but i jus keep getting errors.
Basically, i have SPOne and SPTwo - SPOne is writing to a table called Publication that has PublicationID as its PK (auto generated). SPtwo needs to get this PublicationID from SPOne and use it to insert rows into a second table PublicationAuthors - the PublicationID is hence a FK in the table PublicationAuthors.
The error i get is: Procedure or function 'SPOne' expects parameter '@publicationID', which was not supplied. Cannot insert the value NULL into column 'publicationID', table .dbo.PublicationAuthors'; column does not allow nulls. INSERT fails.
SPOne is as follows: ALTER PROCEDURE dbo.StoredProcedureOne @typeID smallint=null, @title nvarchar(MAX)=null, @publicationID smallint OUTPUTASBEGIN SET NOCOUNT ON INSERT INTO Publication (typeID, title) VALUES (@typeID, @title) SELECT @publicationID = scope_identity()
END
and SPtwo is as follows: ASDECLARE @NewpublicationID IntEXEC StoredProcedureOne @NewpublicationID = OUTPUTSET @publicationID = @NewpublicationIDINSERT INTO PublicationAuthors (publicationID, authorID) VALUES (@publicationID, @authorID)
SELECT @NewpublicationID
Thanks
Gemma
Hi everyone, I have a stored procedure as the following:1 ALTER PROCEDURE dbo.logincheck2 @ID int,3 @Password varchar(50),4 @Result int output5 AS6 IF EXISTS (SELECT * FROM users WHERE (ID=@ID AND Password=@Password))7 BEGIN8 SET @Result=19 END10 11 ELSE12 BEGIN13 SET @Result=014 END15 16 RETURN @Result What I'm trying to do is using @result in my login.aspx.vb to check if user exists in my database. If he is, the result should be 1 and if not it should be 0. Unfortunately, I don't know how to do it. I must submit my project in 2 days and I'm desperately seeking for help. Here is my code: 1 Dim sqlcon As New SqlClient.SqlConnection2 Dim sqlcmd As New SqlCommand()3 Dim sonuc As Integer = 04 If IsValid Then5 sqlcon.ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename=C:UsersShadow ShooterWebSiteMainProjectApp_Datamydb.mdf;Integrated Security=True;User Instance=True"6 sqlcon.Open()7 sqlcmd.Connection = sqlcon8 sqlcmd.CommandType = CommandType.StoredProcedure9 sqlcmd.CommandText = "logincheck"10 sqlcmd.Parameters.Add("@ID", SqlDbType.Int, 4, "ID")11 sqlcmd.Parameters("@ID").Value = txtUsername.Text12 13 sqlcmd.Parameters.Add("@Password", SqlDbType.VarChar, 50, "Password")14 sqlcmd.Parameters("@Password").Value = txtPassword.Text15 16 sqlcmd.Parameters.Add("@Result", SqlDbType.Int, 4, sonuc)17 sqlcmd.Parameters("@Result").Direction = ParameterDirection.Output18 19 20 sqlcmd.ExecuteNonQuery()21 22 If (sonuc = 1) Then23 24 Response.Write("USER FOUND :)")25 'cookie is set with SESSION ID.26 Response.Cookies("User").Value = Session.SessionID27 Response.Cookies("user").Expires = DateTime.Now.AddMinutes(30)28 'Response.Redirect("Welcome.aspx")29 Else30 Response.Write("USER NOT FOUND :(")31 'Response.Redirect("invalid.aspx")32 33 End If
View 5 Replies View RelatedSince i am newbie to sqlserver and & stored procedure. I need some help to modify the existing stored procedure.
The values and parameters are passing to stored procedure from Asp page.
My question is in Stored procedure, there is one value is Company name (vchCompany Name) , I need to check the values
passed from the asp page, whether the company name is already exists in the table. If exists display the pop up message window.
Thanks
The code is below:
************************************************** ****
CREATE procedure wbospsiCompany
@iSiteId int,
@iCompanyId int = NULL OUTPUT,
@chLanguageCode char(4),
@vchAssignedId varchar(255),
@vchCompanyName varchar(255),
@vchAddress1 varchar(255),
@vchAddress2 varchar(255),
@vchAddress3 varchar(255),
@vchCity varchar(255),
@chRegionCode char(4),
@chCountryCode char(4),
@vchPostCode varchar(40),
@vchPhoneNumber varchar(40),
@vchEmailAddress varchar(255),
@vchURL varchar(255),
@iCompanyTypeCode int,
@iCompanySubTypeCode int,
@iFamilyId int,
@iParentId int,
@iPrimaryContactId int,
@vchContactFirstName varchar(255),
@vchContactLastName varchar(255),
@iDivisionCode int,
@iSICCode int,
@iMarketSector int,
@vchTaxId varchar(255),
@vchDunnsNumber varchar(255),
@iPhoneTypeId int,
@iAddressTypeId int,
@iSourceId int,
@iStatusId int,
@bValidAddress tinyint,
@iAccessCode int,
@bPrivate tinyint,
@vchUser1 varchar(255) = NULL,
@vchUser2 varchar(255) = NULL,
@vchUser3 varchar(255) = NULL,
@vchUser4 varchar(255) = NULL,
@vchUser5 varchar(255) = NULL,
@vchUser6 varchar(255) = NULL,
@vchUser7 varchar(255) = NULL,
@vchUser8 varchar(255) = NULL,
@vchUser9 varchar(255) = NULL,
@vchUser10 varchar(255) = NULL,
@chInsertBy char(10) = NULL,
@dtInsertDate datetime = NULL,
@tiLockRecord tinyint = 0,
@tiRecordStatus tinyint = 1,
@tiReturnType tinyint = 1
AS
/*
** ObjectName: wbospsiCompany
**
** Project: Apollo
** SubProject:
** FileName: Insert.sql
** Type: Production
**
** Description: Inserts a record into the Company table.
**
** Valid Values for @tiLockRecord Valid Values for @tiReturnType
** ------------------------------ ------------------------------
** 1 = Lock Record 1 = Result Set
** 0 = Don't Lock Record 0 = Output Only
**
** Revision History
** ----------------------------------------------------------------------------
** Date Name Description
** ----------------------------------------------------------------------------
** 1/26/99 RobertA Created
** 10/11/99 GregP Dion schema compatibility changes
**
*/
BEGIN
/*
** Declare & initialize Local Variables
*/
declare @iReturnCode int,
@iWBOCPExists int,
@iWBOCPReturn int
select @iReturnCode = 0,
@iWBOCPExists = 0,
@iWBOCPReturn = 0
/*
** Declare & initialize Local Constants
*/
declare @PRE_OPTION int,
@CORE_OPTION int,
@POST_OPTION int,
@INSERT_CODE char(1),
@chUpdateBy char(4),
@dtUpdateDate datetime,
@dtModifiedDate datetime
select @PRE_OPTION = 0,
@CORE_OPTION = 0,
@POST_OPTION = @tiLockRecord * 2, /* Lock if @tiLockRecord = 1 */
@INSERT_CODE = "I",
@chUpdateBy = NULL,
@dtUpdateDate = NULL,
@dtModifiedDate = NULL
exec @iReturnCode = ospsiCompanyPre @iSiteId OUTPUT,
@iCompanyId OUTPUT,
@chLanguageCode OUTPUT,
@vchAssignedId OUTPUT,
@vchCompanyName OUTPUT,
@vchAddress1 OUTPUT,
@vchAddress2 OUTPUT,
@vchAddress3 OUTPUT,
@vchCity OUTPUT,
@chRegionCode OUTPUT,
@chCountryCode OUTPUT,
@vchPostCode OUTPUT,
@vchPhoneNumber OUTPUT,
@vchEmailAddress OUTPUT,
@vchURL OUTPUT,
@iCompanyTypeCode OUTPUT,
@iCompanySubTypeCode OUTPUT,
@iFamilyId OUTPUT,
@iParentId OUTPUT,
@iPrimaryContactId OUTPUT,
@vchContactFirstName OUTPUT,
@vchContactLastName OUTPUT,
@iDivisionCode OUTPUT,
@iSICCode OUTPUT,
@iMarketSector OUTPUT,
@vchTaxId OUTPUT,
@vchDunnsNumber OUTPUT,
@iPhoneTypeId OUTPUT,
@iAddressTypeId OUTPUT,
@iSourceId OUTPUT,
@iStatusId OUTPUT,
@bValidAddress OUTPUT,
@iAccessCode OUTPUT,
@bPrivate OUTPUT,
@vchUser1 OUTPUT,
@vchUser2 OUTPUT,
@vchUser3 OUTPUT,
@vchUser4 OUTPUT,
@vchUser5 OUTPUT,
@vchUser6 OUTPUT,
@vchUser7 OUTPUT,
@vchUser8 OUTPUT,
@vchUser9 OUTPUT,
@vchUser10 OUTPUT,
@chInsertBy OUTPUT,
@dtInsertDate OUTPUT,
@chUpdateBy,
@dtUpdateDate,
@tiRecordStatus OUTPUT,
@dtModifiedDate,
@PRE_OPTION
if @iReturnCode <> 0
begin
exec @iReturnCode = ospCheckError "p", @iReturnCode
end
/*
** Determine if @vchAssignedId and @iIndividualId should be set by configs
*/
if @iReturnCode <= 0
begin
exec @iReturnCode = ospsuCustomerIds @iSiteId,
"Company",
@chCountryCode,
@iCompanyId OUTPUT,
@vchAssignedId OUTPUT
if @iReturnCode <> 0
begin
exec @iReturnCode = ospCheckError "p", @iReturnCode
end
end
/*
** Determine if WBOCP configurable procedure exists
*/
exec @iWBOCPExists = ospObjectExists "wbocpscCompany", "p"
if @iReturnCode <= 0
begin
begin transaction
if @iWBOCPExists = 1
begin
exec @iWBOCPReturn = wbocpscCompany @INSERT_CODE,
@iSiteId,
@iCompanyId,
@chLanguageCode OUTPUT,
@vchAssignedId OUTPUT,
@vchCompanyName OUTPUT,
@vchAddress1 OUTPUT,
@vchAddress2 OUTPUT,
@vchAddress3 OUTPUT,
@vchCity OUTPUT,
@chRegionCode OUTPUT,
@chCountryCode OUTPUT,
@vchPostCode OUTPUT,
@vchPhoneNumber OUTPUT,
@vchEmailAddress OUTPUT,
@vchURL OUTPUT,
@iCompanyTypeCode OUTPUT,
@iCompanySubTypeCode OUTPUT,
@iFamilyId OUTPUT,
@iParentId OUTPUT,
@iPrimaryContactId OUTPUT,
@vchContactFirstName OUTPUT,
@vchContactLastName OUTPUT,
@iDivisionCode OUTPUT,
@iSICCode OUTPUT,
@iMarketSector OUTPUT,
@vchTaxId OUTPUT,
@vchDunnsNumber OUTPUT,
@iPhoneTypeId OUTPUT,
@iAddressTypeId OUTPUT,
@iSourceId OUTPUT,
@iStatusId OUTPUT,
@bValidAddress OUTPUT,
@iAccessCode OUTPUT,
@bPrivate OUTPUT,
@vchUser1 OUTPUT,
@vchUser2 OUTPUT,
@vchUser3 OUTPUT,
@vchUser4 OUTPUT,
@vchUser5 OUTPUT,
@vchUser6 OUTPUT,
@vchUser7 OUTPUT,
@vchUser8 OUTPUT,
@vchUser9 OUTPUT,
@vchUser10 OUTPUT,
@chInsertBy OUTPUT,
@dtInsertDate OUTPUT
if @iWBOCPReturn <> 0
begin
exec @iReturnCode = ospCheckError "p", @iWBOCPReturn
end
if @iReturnCode <= 0
begin
exec @iReturnCode = espcpCheckCustomerId @iSiteId, @iCompanyId
end
end
if @iReturnCode <= 0
begin
exec @iReturnCode = ospsiCompanyCore @iSiteId,
@iCompanyId,
@chLanguageCode OUTPUT,
@vchAssignedId OUTPUT,
@vchCompanyName OUTPUT,
@vchAddress1 OUTPUT,
@vchAddress2 OUTPUT,
@vchAddress3 OUTPUT,
@vchCity OUTPUT,
@chRegionCode OUTPUT,
@chCountryCode OUTPUT,
@vchPostCode OUTPUT,
@vchPhoneNumber OUTPUT,
@vchEmailAddress OUTPUT,
@vchURL OUTPUT,
@iCompanyTypeCode OUTPUT,
@iCompanySubTypeCode OUTPUT,
@iFamilyId OUTPUT,
@iParentId OUTPUT,
@iPrimaryContactId OUTPUT,
@vchContactFirstName OUTPUT,
@vchContactLastName OUTPUT,
@iDivisionCode OUTPUT,
@iSICCode OUTPUT,
@iMarketSector OUTPUT,
@vchTaxId OUTPUT,
@vchDunnsNumber OUTPUT,
@iPhoneTypeId OUTPUT,
@iAddressTypeId OUTPUT,
@iSourceId OUTPUT,
@iStatusId OUTPUT,
@bValidAddress OUTPUT,
@iAccessCode OUTPUT,
@bPrivate OUTPUT,
@vchUser1 OUTPUT,
@vchUser2 OUTPUT,
@vchUser3 OUTPUT,
@vchUser4 OUTPUT,
@vchUser5 OUTPUT,
@vchUser6 OUTPUT,
@vchUser7 OUTPUT,
@vchUser8 OUTPUT,
@vchUser9 OUTPUT,
@vchUser10 OUTPUT,
@chInsertBy OUTPUT,
@dtInsertDate OUTPUT,
@chUpdateBy,
@dtUpdateDate,
@tiRecordStatus OUTPUT,
@dtModifiedDate,
@CORE_OPTION
if @iReturnCode <> 0
begin
exec @iReturnCode = ospCheckError "p", @iReturnCode
end
end
/*
** Transaction Management
*/
if @iReturnCode <=0
begin
commit transaction
end
else
begin
rollback transaction
end
if @iReturnCode <= 0
begin
/*
** Call post procedure
*/
exec @iReturnCode = ospsiCompanyPost @iSiteId,
@iCompanyId,
@chLanguageCode OUTPUT,
@vchAssignedId OUTPUT,
@vchCompanyName OUTPUT,
@vchAddress1 OUTPUT,
@vchAddress2 OUTPUT,
@vchAddress3 OUTPUT,
@vchCity OUTPUT,
@chRegionCode OUTPUT,
@chCountryCode OUTPUT,
@vchPostCode OUTPUT,
@vchPhoneNumber OUTPUT,
@vchEmailAddress OUTPUT,
@vchURL OUTPUT,
@iCompanyTypeCode OUTPUT,
@iCompanySubTypeCode OUTPUT,
@iFamilyId OUTPUT,
@iParentId OUTPUT,
@iPrimaryContactId OUTPUT,
@vchContactFirstName OUTPUT,
@vchContactLastName OUTPUT,
@iDivisionCode OUTPUT,
@iSICCode OUTPUT,
@iMarketSector OUTPUT,
@vchTaxId OUTPUT,
@vchDunnsNumber OUTPUT,
@iPhoneTypeId OUTPUT,
@iAddressTypeId OUTPUT,
@iSourceId OUTPUT,
@iStatusId OUTPUT,
@bValidAddress OUTPUT,
@iAccessCode OUTPUT,
@bPrivate OUTPUT,
@vchUser1 OUTPUT,
@vchUser2 OUTPUT,
@vchUser3 OUTPUT,
@vchUser4 OUTPUT,
@vchUser5 OUTPUT,
@vchUser6 OUTPUT,
@vchUser7 OUTPUT,
@vchUser8 OUTPUT,
@vchUser9 OUTPUT,
@vchUser10 OUTPUT,
@chInsertBy OUTPUT,
@dtInsertDate OUTPUT,
@chUpdateBy,
@dtUpdateDate,
@tiRecordStatus OUTPUT,
@dtModifiedDate,
@POST_OPTION
if @iReturnCode <> 0
begin
exec @iReturnCode = ospCheckError "p", @iReturnCode
end
end
end
/*
** Manage return: return ID of record based upon @ReturnType param
*/
exec @iReturnCOde = ospManageReturn @tiReturnType,
@iReturnCode,
@iSiteID,
@iCompanyId,
"Company",
"I"
if @iReturnCode = 0
begin
select @iReturnCode = @iWBOCPReturn
end
return @iReturnCode
END
************************************************** ****
Hi, I have a problem with input parameter which has Decimal DataType. Stored procedure works but it rounds all
values, i.e 5.555 input becomes 6 and 1.3 input becomes 1.
In table QTY has data type decimal(5) - precision(8) scale(3).
Please, suggest what's wrong with this:
newqty = Request.Form("quantity")
..........
cmd.Parameters.Append(cmd.CreateParameter("qty", adDecimal, adParamInput, 5, newqty))
cmd.Parameters("qty").Precision = 8
cmd.Parameters("qty").NumericScale = 3
Please, help!
Thanks in advance.
i am trying to pass a large XML file from VS2005 (web service layer) to stored procedure (SQL Server 2000)In my stored procedure, the input parameter takes as "nText" (which will be XML file)Question:While performing ExecuteNonQuery, i am getting request timeout i think this is coz of large XML file i am passing.can anyone plz tell me how to pass XML file to SP...it would be better if you can provide me with some codei am completely new to this XML file passing between web service and SP...... thanks a lot in advance.....
View 7 Replies View RelatedHi all;
Question:
=======
Q1) How can I insert a record into a table "Parent Table" and get its ID (its PK) (which is an Identity "Auto count" column) via one Stored Procedure??
Q2) How can I insert a record into a table "Child Table" whose (FK) is the (PK) of the "Parent Table"!! via another one Stored Procedure??
Example:
------------
I have two tables "Customer" and "CustomerDetails"..
SP1: should insert all "Customer" data and return the value of an Identity column (I will use it later in SP2).
SP2: should insert all "CustomerDetials" data in the record whose ID (the returned value from SP1) is same as ID of the "Customer" table.
FYI:
----
MS SQL Server 2000
VS.NET EA 2003
Win XP SP1a
VB.NET/ASP.NET :)
Thanks in advanced!
I have a stored procedure in an Informix-database that returns a string. Its used in a SELECT statement like this.
SELECT t1.id, t2.id, sp_name(t1.id, t2.id) FROM table1 t1, table2 t2
I want to write it in SQLserver. I have tried this syntax but get error.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 't'.
SELECT t1.id, t2.id, dbo.sp_name(t1.id, t2.id, "") FROM table1 t1, table2 t2
Can I use stored proc in this way in SQL-server?
Hi,
I have created the following stored procedure to get the text from one table and compare with them with another table and the one's that match will assign the corresponding ID. But the problem is that it only assigns the last id in the table from the main table which new_cur2 holds. So the problem is that its not updating with the correct ID its just updating with the last ID the cursor holds. Does any one know what it could be.....I think it may just be a little coding error....thanks
CREATE PROCEDURE [MYSP] AS
Declare @pdesc nvarchar(30)
Declare @ssc int
Declare @myid int
Declare @name nvarchar(30)
Declare new_cur CURSOR DYNAMIC FOR
SELECT ProductDescription, SubSubCatID
FROM C2000HPB
FOR UPDATE
Open new_cur
FETCH FROM new_cur INTO @pdesc, @ssc
While @@FETCH_STATUS = 0
BEGIN
Declare new_cur2 CURSOR DYNAMIC FOR
SELECT SubSubCatID, SubSubCategory FROM SSC
FOR READ ONLY
Open new_cur2
FETCH FROM new_cur2 INTO @myid, @name
While @@FETCH_STATUS = 0
BEGIN
IF PATINDEX ('@name%',@pdesc) = 0
Set @ssc = @myid
UPDATE C2000HPB
SET SubSubCatID = @ssc
FETCH NEXT FROM new_cur2 INTO @myid, @name
END
Close new_cur2
DEALLOCATE new_Cur2
FETCH NEXT FROM new_cur INTO @pdesc,@ssc
END
Close new_cur
DEALLOCATE new_Cur
i want to calculate the month salary of an employee.which will be calculated on the basis of previous available leaves and present available leave(i.e) 2 per month.
View 2 Replies View RelatedI am trying to execute a stored procedure on Server1 which creates an excel report on a share of Server2:
The following error message is thrown:
Saving of scheduled report(s) to Excel file failed : 203 SaveReportToExcel() in TlRptToFile.RptExcel failed in sproc_SaveReportAsFile -
TLRptXL::SaveReportToExcel - Connection to Database failed for Analytics DataBase
Server : TKTALSQL3, Application Server: and DataBase : tlAnalytics : Windows
Error - The system could not find the environment option that was entered.
A DCOM component on Server1 runs the stored procedure that creates the excel report. The account under which DCOM component runs is a member of 'Administrators' group on both Server1 and Server2. Checked permissions on the shares. Account is a local Admin on both Server1 and Server2. Account under which SQLServer and ServerAgent runs is also an Admin on these shares (implicitly as part of 'Administrators' group).
I am manually able to create an excel/text file on the Server2 share while accessing it from Server1, though.
Appreciate your help in resolving the issue.
I'm working on a fairly straight forward data transfer package and have found that the package runs dramatically faster when I run the package inside BIDS than with DTExec. When I run the package on the server using debug in BIDS, the job completes 1 million rows in around 6 minutes. When I run DTExec with the same package on the same server it is much slower and the package takes roughly 25 minutes to complete.
I know this sounds crazy and that it's supposed to be the other way around with DTExec running much faster, but I'm stumped as to what could be causing the issue. The machine this is running on is a two processor, dual core CPU with GB of RAM and I'm using terminal server to login and create the package with BIDS on SQL Server 2005 SP2.
The main feature of this package is a Foreach container that uses an ADO record set to loop over a set of values from a control table. There are a large number of iterations so the package loops frequently, but the data flow task is fairly simple and uses an OLEDB source and OLEDB destination to transfer data between two SQL Server 2005 databases.
The package works in either BIDS and DTExec, but I'm really puzzled why it would run so much faster inside BIDS?
Thanks in advance,
-Russ
Hi all,
I have writen a Function which call's the same function it self. I'm getting the error as below.
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
Can any one give me a solution for this problem I have attached the function also.
CREATE FUNCTION dbo.GetLegsFor(@IncludeParent bit, @EmployeeID float)
RETURNS @retFindReports TABLE (EmployeeID float, Name nvarchar(255), BossID float)
AS
BEGIN
IF (@IncludeParent=1)
BEGIN
INSERT INTO @retFindReports SELECT MemberId,Name,referredby FROM Amemberinfo WHERE Memberid=@EmployeeID
END
DECLARE @Report_ID float, @Report_Name nvarchar(255), @Report_BossID float
DECLARE RetrieveReports CURSOR STATIC LOCAL FOR
SELECT MemberId,Name,referredby FROM Amemberinfo WHERE referredby=@EmployeeID
OPEN RetrieveReports
FETCH NEXT FROM RetrieveReports INTO @Report_ID, @Report_Name, @Report_BossID
WHILE (@@FETCH_STATUS = 0)
BEGIN
INSERT INTO @retFindReports SELECT * FROM dbo.GetLegsFor(0,@Report_ID)
INSERT INTO @retFindReports VALUES(@Report_ID,@Report_Name, @Report_BossID)
FETCH NEXT FROM RetrieveReports INTO @Report_ID, @Report_Name, @Report_BossID
END
CLOSE RetrieveReports
DEALLOCATE RetrieveReports
RETURN
END
Anyone got any alternatives to the SQL Statement below that would make it run a bit faster:
ALTER PROCEDURE sproc_ReturnAvailability
@ExtractDate DateTime,
@DateFrom DateTime,
@DateTo DateTime,
@96hrPlusFlag int,
@AppointmentsCount int OUTPUT
AS
IF @96hrPlusFlag = 0
BEGIN
SELECT @AppointmentsCount = COUNT(tbl_SurgerySlot.SurgerySlotKey)
FROM tbl_SurgerySlot
INNER JOIN tbl_SurgerySlotDescription ON (tbl_SurgerySlot.Label = tbl_SurgerySlotDescription.Label AND tbl_SurgerySlot.PracticeCode = tbl_SurgerySlotDescription.PracticeCode)
AND tbl_SurgerySlot.ExtractDate = @ExtractDate
AND tbl_SurgerySlot.StartTime BETWEEN @DateFrom AND @DateTo
AND tbl_SurgerySlotDescription.NormalBookable = 1
AND tbl_SurgerySlot.SurgerySlotKey NOT IN(
SELECT tbl_Appointment.SurgerySlotKey
FROM tbl_Appointment
WHERE tbl_Appointment.ExtractDate = @ExtractDate
AND tbl_Appointment.Deleted = 0
AND tbl_Appointment.Cancelled = 0
)
END
ELSE
BEGIN
IF @96hrPlusFlag = 1
SELECT @AppointmentsCount = COUNT(tbl_SurgerySlot.SurgerySlotKey)
FROM tbl_SurgerySlot
INNER JOIN tbl_SurgerySlotDescription ON (tbl_SurgerySlot.Label = tbl_SurgerySlotDescription.Label AND tbl_SurgerySlot.PracticeCode = tbl_SurgerySlotDescription.PracticeCode)
AND tbl_SurgerySlot.ExtractDate = @ExtractDate
AND tbl_SurgerySlot.StartTime >@DateTo
AND tbl_SurgerySlotDescription.NormalBookable = 1
AND tbl_SurgerySlot.SurgerySlotKey NOT IN(
SELECT tbl_Appointment.SurgerySlotKey
FROM tbl_Appointment
WHERE tbl_Appointment.ExtractDate = @ExtractDate
AND tbl_Appointment.Deleted = 0
AND tbl_Appointment.Cancelled = 0
)
END
Cheers...
Hello,
Lets say I have a SP that return 1000 records,
do I get any better speed if doing it on a SP instead of just SELECT without an SP ?
if I have many users on a web-site that will execute this SP - will they get any better
speed because it is a SP ? - can SP cache itself - if so - for how long ?
(Why should I use SP if not passing any parameters ?)
I am using execute sql task to run a stored procedure in oracle database which returns a resultset. This works. Now I need to send the ouput to a destination table in a sql database. Should I use for each loop to pick the resultset and insert it into the destination one by one (which I dont think is a great idea) or is there a better way to accomplish this task (in data flow task) ?
When I use dataflow task instead of execute sql task, the main issue is I am not able to see the output columns when I execute an oracle stored procedure, but when I see the preview I can see the resultset . But I can see the output columns for a sql server stored procedure.
Yesterday i face a strange SQL Server 2000 behaviour :-(
I had a query that was wrapped inside a stored procedure, as usual.
Suddenly, the stored procedure execution time raised from 9 secs to 80.
So to understand where the problem was i cut and pasted the sp body's into a new query analyzer window an then executed it again. Speed back to 9 secs.
Tried stored procedure again, and speed again set to 80 secs.
Tried to recompile sp. Nothing. Tried to restart SQL Server. Nothing. Tried to DROP & RE-CREATE sp. Done! Speed again at 9 secs.
My collegue asked me "why?", but i had no words. :confused: Do you have any explanation?
I am trying to execute a stored procedure that runs a SSIS package. This package imports a .csv flat file (from a different server) and appends to an existing table. When I run the package from SQL Server Management Studio it works great. When I execute the Stored Procedure, I get an error stating "The system cannot find the file specified." Eventually, I would like to execute the Stored Procedure from an asp.net webpage button click event.
The code for the stored procedure:
USE [JElog]GODECLARE @return_value intEXEC @return_value = [dbo].[importGLJEdata]SELECT 'Return Value' = @return_valueGO
asp.net VB code:
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim myconn As New SqlConnection(ConfigurationManager.ConnectionStrings("JElog-SQL").ConnectionString)
Dim MyCmd As New SqlCommand("importGLJEdata", myconn)
MyCmd.CommandType = CommandType.StoredProcedure
myconn.Open()
MyCmd.ExecuteNonQuery()
myconn.Close()
End SubThanks,SMC
Hi
I use a stored procedure which uses its source as a database. It returns 3 outputs ie 2 int's and one datetime . I need to use these as the input columns to map to the destination coulmns in another stored procedure.
Help!!
Hi,
I apologize for the long post but I am trying to give as much information as I can about the steps I've taken to troubleshoot this.
We have a stored procedure that builds a sql statement and executes it using the Execute command. When I execute the stored procedure through query analyzer it takes close to 5 seconds to execute. When I print out the exact same statement and execute it directly in query analyzer as "raw sql", it takes 0.5 seconds - meaning it takes 10 times longer for the code to execute in the stored proc. I altered the stored proc to execute the printed sql instead of building but it still takes the full 5 seconds and there were no changes in the execution plan. This makes me confident that the issue is not caused by the dynamic sql. I've used with recompile to make sure that the stored procedure caches the most recent execution plan. When I compare the execution plans, the stored proc uses a nested loop whereas the raw sql statement uses a hash join. Seeing that, I added the hash hint to the stored proc and doing so brought down the execution time down from 5 secs to 2 secs but still the raw sql statement uses a clustered index whereas the stored proc uses a non-clustered index and that makes the statement 4 times slower. This proves how efficient clustered indexes are over non-clustered ones, but it doesn't help me since, as far as I know, I can't force SQL Server to use the clustered index.
Does anyone know why sql server is generating such an inefficient execution plan for the stored proc compared to the execution plan that it generates when executing the raw sql statement? The only thing I can think of is that some stats are not updated and that somehow throws off the stored proc. But then again, shouldn't it affect the raw sql statement?
Thank you,
Michael Tzoanos
Please anyone help me with my question
How do I execute a stored Procedure using SSIS ?
I have a stored procedure in SQL SERVER 2005 database that I need to execute using a SSIS package using Execute SQL Task from Toolbox in Visual studio 2005
Thanks,
George
Is there a programatic way to execute an SSIS package (passing the appropriate parameters) from within a stored procedure. I've seen some examples using xp_CmdShell, but would rather not go that route if possible
View 1 Replies View RelatedHi all,
I need to know whether changing/migrating process in SP to SSIS is a good idea to enhance performance in datawarehouse.
My client want to do this because they have problems in performance while executing SP from dts 2000 packages.
So they don't want to use SP anymore. They want to use all SSIS features.
Since I'm newby with SSIS, I don't have any idea how to implement SPs in SSIS, especially while working with temporary
tables.
Btw, this is snippet of the SP I want to change :
ALTER PROCEDURE [dbo].[DBAS_BBCBG_DataDaily]
@CustomDate datetime = null
AS
BEGIN -- Begin Procedure-
--15 Minute
DECLARE @date datetime
SET @date = isnull(@CustomDate,
(SELECT top 1 Reporting_Date From DBDS..DataDate))
SELECT * INTO #TbDataDaily FROM DBABC.dbo.TbDataDaily WHERE 1=2
BEGIN
INSERT INTO #TbDataDaily (
Col1, Col2, Col3, Col4, ValDate
)
SELECT CD1,CD2,CD3, CD4, @date
FROM DBAS..HDKFS
CREATE INDEX IDX_ABC_DataDaily ON
#ABC_DataDaily (Col3,Col4) ON [PRIMARY]
END
BEGIN
UPDATE #TbDataDaily
SET Col2 = B.CABLVT
FROM #TbDataDaily A, DBAS..COLACBFP B
WHERE A.Col3 = B.CAKNTN
END
... and so on...
all other processes in the SP are just about to SELECT/INSERT/UPDATE temporary table, and finally
fill the data to physical table.
So how can I change this SP to SSIS package? (of course if this is the best way to improve performance)
Thank You in advance.
Best Regards,
Ricky Lie
Quick question: In the OLE DB Command transform, you can pass parameters to a stored procedure.
One of my stored procedures has an OUTPUT parameter. How do I retrieve the output from the stored procedure?
Hi
I am currently trying to write a number of processe's to keep track of what information is held in my SSIS package. The package I have created is rather large and it would prove a long labourious process to look through every task to see what stored procedure has been used.
What I wanted to do was write a stored procedure in SQL Server 2005 that pick's up each package name and checks for any stored procedures used and returns the names of these stored procedures and any other relevant information i.e required variables.
So far I have managed to create a stored procedure that picks up the name(s) of the packages but I am stuck after this.
Declare @Filename varchar(1000)
Declare @cmd varchar(1000)
Create table #dir (Filename varchar(1000))
Insert #dir
Exec master..xp_cmdshell 'dir /B C:DevelopmentSumColumnSumColumn*.dtsx'
delete #dir where Filename is null or Filename like '%not found%'
Select @Filename = ''
While @Filename < (select max(Filename) from #dir)
drop table #dir
Any help would be appreciated.
Thanks
I have to transfer data from source to destination using stored procedures result set. There might be some more transformation needed to store the final result in the destination table.
Appreciate an early feedback.
Qadir Syed
Hi
I am trying to call a stored procedure which akes 1 input param from SSIS. I am using Execute SQL Task->Expressions->"exec s_Staging '"+ @[User::tblName] +"'"
@[User::tblName] is the variable with Data Type:String ,Value:My_table
SQLStatement->Stored Procedure Name
But It throws an error
[Execute SQL Task] Error: Executing the query "exec s_Staging 'My_Table' " failed with the following error: "Incorrect syntax near 'My_Table' ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I'm executing an SSIS package using the following stored procedure
ALTER PROC [dbo].[SSISRunBuildSCCDW] AS
BEGIN
DECLARE @ServerName VARCHAR(30), @ReturnValue int, @Cmd varchar(1000)
SET @ReturnValue = -1
SET @ServerName = 'myserver'
SET @Cmd = 'DTExec /SER ' + @ServerName + ' ' +
' /SQL ' + 'BuildSCCDW ' --Location of the package stored in the mdb
--' /CONF "\ConfigFilePath.dtsConfig" ' +
--' /SET Package.Variables[ImportUserID].Value; ' +
--' /U "LoginName" /P "password" '
EXECUTE @ReturnValue = master..xp_cmdshell @Cmd, NO_OUTPUT
RETURN @ReturnValue
--SELECT @ReturnValue [Result]
END
I'm then using a tableadapter to execute this from my ASP.NET page using the following code, Protected Sub ExecutePackage()
Dim ExecuteAdapter As New SCC_DAL.RunSSISTableAdapters.SSISRunBuildSCCDWTableAdapter()
ExecuteAdapter.SetCommandTimeOut(0)
Dim strResult As String
strResult = ExecuteAdapter.Execute()
lblResult.Text = strResult
End Sub
If I remove 'NO_OUTPUT' from my stored procedure and run it the results contain a field named 'output' with all the steps from my package. Then below this is my return value. In my code I can only return the first step of the package results - which tells me nothing useful. I need to be able to return the return value (0-6) in my code.
When I have 'NO_OUTPUT' in my stored procedure and execute it I am left with just the return value. However no value is returned in my code at all although the package does run. I've tried bother RETURN @ReturnValue and SELECT @ReturnValue to no avail.
Can someone suggest how I can get the value 0-6 to my code?
Hello friends!
I have one query regarding execution of SSIS package through Stored Procedure.
I have created SSIS package which extract data from database and put that into various text files.Here I am using two global variables one is for Department ID and another is path where I wanna to place my text files as per departments.When I ran it through command prompt it works fine but now I want that dtsx package to run from stored procedure with same input parameters
when i searched on line i got this solution
Declare @FilePath varchar(2000)
Declare @Filename varchar(1000)
Declare @cmd varchar(2000)
set @FilePath = 'C:setupSSIS PackagesSSIS Package File Extract DataSSIS Package File Extract Datain'
set @Filename = 'DataExtract.dtsx'
select @cmd = 'DTExec /F "' + @FilePath + @Filename + '"'
print @cmd
exec master..xp_cmdshell @cmd
but when i execute it i got error like
Source: {8A27E8DF-051B-4F6B-9538-85BED1F161D8}
Description: Unable to load the package as XML because of package does not have a valid XML format. A specific XML parser error will be posted.
End Error
Error: 2007-02-22 11:31:37.32
Code: 0xC0011002
Source: {8A27E8DF-051B-4F6B-9538-85BED1F161D8}
Description: Failed to open package file "C:setupSSIS PackagesSSIS Package File Extract DataSSIS Package File Extract DatainDataExtract.dtsx" due to error 0x80070003 "The system cannot find the path specified.". This happens when loadin
g a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format.
End Error
Could not load package "C:setupSSIS PackagesSSIS Package File Extract DataSSIS Package File Extract DatainDataExtract.dtsx" because of error 0xC0011002.
Description: Failed to open package file "C:setupSSIS PackagesSSIS Package File Extract DataSSIS Package File Extract DatainDataExtract.dtsx" due to error 0x80070003 "The system cannot find the path specified.". This happens when loading a
package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format.
And also I am not understand where i should pass my two input parameters which I used in SSIS package variables???????
Please help me out
Thanks