Suppress Results From Running DTS Package Within Stored Procedure
Apr 6, 2015
I've created this stored procedure to run two DTS packages which pull in data from two Excel files. I'd like to supress the Results tab since the results from the DTS packages are fed to it. I'd like to keep the Messages tab visible.
CREATE PROCEDURE [dbo].[ImportHelpDeskTickets]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
[Code] ....
For those of you like me who will spend 8 hours trying to figure out how to get this working... Double up on the quotes as shown above. Also, be certain that NT SERVICEMSSQLSERVER has been granted appropriate rights to the folders with the the saved packages and the Excel files. You may also need to download the Access Database Engine.
Keywords: run dts package from stored procedure xp_cmdshell
'C:Program' is not recognized as an internal or external command, operable program or batch file.'
View 3 Replies
ADVERTISEMENT
Nov 6, 2006
Hi,
I need to write a select query that will run all returned results through a separate stored procedure before returning them to me.
Something like....
SELECT TOP 10 userid,url,first name FROM USERS ORDER by NEWID()
......and run all the selected userids through a stored procedure like.....
CREATE PROCEDURE AddUserToLog (@UserID int ) AS INSERT INTO SelectedUsers (UserID,SelectedOn) VALUES (@UserID,getdate())
Can anyone help me to get these working togethsr in the same qurey?
Thanks
View 7 Replies
View Related
May 27, 2014
I have stored procedure
ALTER PROCEDURE dbo.usp_Create_Fact_Job (@startDate date, @endDate date) AS
/*--Debug--*/
--DECLARE @startDate date
--DECLARE @endDate date
--SET @startDate = '01 APR 2014'
--SET @endDate = '02 APR 2014'
;
/*-- end of Debug*/
WITH CTE_one AS ( blah blah blah)
SELECT a whole bunch of fields from the joined tables and CTEs...When I run the code inside the stored procedure by Declaring and setting the start and enddates manually the code runs in 4 minutes (missing some indexes ).When I call the stored procedure with the ExEC
DECLARE@return_value int
EXEC@return_value = [ClaimCenter].[usp_Create_Fact_Job]
@startDate = '01 apr 2014',
@endDate = '01 apr 2014'
SELECT'Return Value' = @return_value
It never returns a results set but doesn't error out either. I have left it for 40 minutes and still no joy.The sproc is reasonably complicated; 6 CTEs to find the most recent version of records and some 2 joins to parent tables (parent and grandparent), 3 joins to child tables (child, grandchild and great grandchild) and 3 joins to lookup views each of which self references a table to filter for last version of a record.
View 3 Replies
View Related
May 18, 2015
I have used BCP to perform this, but I now need an SSIS package. Is this possible to use an SSIS package to automate the task?
View 1 Replies
View Related
Aug 28, 2006
I have a novice question. How does one suppress result sets returned from a stored procedure?
I have created a procedure which makes use of multiple stored procedures . The purpose of this procedure (lets call it procA), is to count the rows returned from other procedures. The €œOther€? procedures will return rows having an unknown number of columns. I would like to limit any changes which may be needed to be made to the €œOther€? procs.
Once procA has collected all of the information (@@rowcount) from the inner procedures, then it will return a result set having several columns €“ mainly the subProcedure name and number of rows returned.
The purpose of procA is to query several subsystems and identify which ones need attention.
Cursor While Loop
exec @ProcName @ObjectName,@userID,@syncDate
set @recs = @@rowcount;
My c# program calls the sp as follows:
cmd = DataUtility.GetSQLCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "FetchAdminData";
cmd.Parameters.AddWithValue("@userID", userAlias);
cmd.Parameters.AddWithValue("@adminDate",userDate);
reader = cmd.ExecuteReader();
I do not wish to iterate over each resultSet when I am only interested in the last one. Any suggestions?
View 2 Replies
View Related
Sep 2, 2004
I really must be missing something here...
Trying to cross-update 2 tables.
Picture a checkbook reconcilliation without common check numbers. The checkbook has uniqueids and the bank has transaction ids but they are different. So the match is on date/payee and amount. So I wrote 2 checks to the same person, on the same day, for the same amount but forgot to enter one in the register.
when i run the update statement:
update b set b.bankid=c.myid
from checks c
join bank b on c.cdate=b.cdate
and c.payee=b.payee
and c.cost=b.cost
Both bank statement records would be updated to my one check record [can't happen]
Also: this will be running on a hundred thousand records per month with potential for duplication/ommission on either side.
What's a poor newbie missing??
I'm doing something similar on a lesser volume by running sequential statements through an ASP script but performance is poor. I know SQL can do this, just not how to approach it.
Thanks for any guidance
Dale
View 4 Replies
View Related
Apr 28, 2008
I'm updating a process that recreates a large table every night. The table is the result of a bunch of nightly batch processes and holds a couple million records. In the past, each night at the end of the batch jobs the table would be dropped and re-created with the new data. This process was embodied in dynamic sql statements from an MFC C++ program, and my task is to move it to a SQL Server 2000 stored procedure that will be called from a .Net app. Here's the relevant code from my procedure:
sql Code:
Original
- sql Code
-- recreate new empty BatchTable table
print 'Dropping old BatchTable table...'
exec DropBatchTable --stored procedure called from old code that does a little extra work when dropping the table
-- validate drop
If exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BatchTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Begin
RAISERROR( 'Unable to drop old BatchTable!',0,1) WITH NOWAIT
End Else Begin
print 'Old BatchTable dropped.'
End
print 'Creating new BatchTable...'
SELECT TOP 0 *, cast('' as char(3)) as Client, cast('' as char(12)) as ClientDB
INTO dbo.BatchTable
FROM differentDB.dbo.BatchArchives
--validate create
If Not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BatchTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Begin
RAISERROR( 'Unable to create new BatchTable!',0,1) WITH NOWAIT
End Else Begin
print 'New BatchTable Created.'
End
-- recreate new empty BatchTable table print 'Dropping old BatchTable table...' exec DropBatchTable --stored procedure called from old code that does a little extra work when dropping the table -- validate drop IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[BatchTable]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN RAISERROR( 'Unable to drop old BatchTable!',0,1) WITH NOWAIT END ELSE BEGIN print 'Old BatchTable dropped.' END print 'Creating new BatchTable...' SELECT TOP 0 *, CAST('' AS CHAR(3)) AS Client, CAST('' AS CHAR(12)) AS ClientDB INTO dbo.BatchTable FROM differentDB.dbo.BatchArchives --validate create IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[BatchTable]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN RAISERROR( 'Unable to create new BatchTable!',0,1) WITH NOWAIT END ELSE BEGIN print 'New BatchTable Created.' END
The print statements are there because the .net app will read them in and then write them to a log file. Some of the other mechanics are there to mimic the old process. The idea is to duplicate the old process first and then work on other improvements.
This works in Management studio. The .Net App reports that the old table was dropped, but when it tries to create the new table it complains that "There is already an object named 'BatchTable' in the database." I have verified that the old table is removed.
Any ideas on how to fix this?
View 4 Replies
View Related
Apr 30, 2008
Hi
I have a SSIS package stored on the database server under Stored Packages within the Integration Services section.
Does anyone know how to run this package using SQL?
Thanks
View 5 Replies
View Related
Jan 30, 2008
I'm trying to suppress whitespace in a drilldown for textboxes that have suppress duplicates applied.
I have a matrix report that is showing whitespace in a drilldown because I am supressing duplicates. Based on what I read in other forums, if I set the ToggleItem to Len(FieldName)=0 that should supress the whitespace, right?
I can see that I have a field in the toggleitem called: Firstname. If I put the value Len(Firstname)=0 in the toggleitem property, then I get the error: The textbox 'textbox21' has Len(Firstname)=0' as a toggle item. Toggle items must be text boxes that share the same scope as the hidden item. I think the code 'Len' is throwing it off.
If I put the value "Firstname" in the toggleitem property, then it doesn't return the error, so I know that firstname is a valid value for toggleitem, but setting the value to firstname doesn't suppress anything.
If someone can tell me how to supress a textbox based on a value, then this may get rid of the whitespace I'm trying to suppress. Any ideas? Thanks...
View 3 Replies
View Related
Jun 13, 2007
Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
View 3 Replies
View Related
Mar 28, 2006
i'm retrieving addresses from a database and displaying them in my report. i have an addr line 2 for addition address data if needed. i have placed this addr line 2 on its own detail row. however i do not want that row to display if there is no data. the following is happening even though i have set the visibility on the row and text field to =iif(fields!addr2="",false,true)
the name prints on the first line, the main address on the second line, i have a space where addr line 2 would have been, finally i get the city, state, zip on the last line.
expected outcome i would like is that addr line 2 does not appear for those addresses that addr line 2 does not have any data. if addr line 2 does have data then print.
any help would be greatly appreciated.
Chuck
View 7 Replies
View Related
Jul 23, 2005
Hi, How can I store a stored procedure's results(returning dataset) intoa table?Bob*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Jun 18, 2004
datagrid. Sorry, I'm new to it. For the few times I've done datagrids in the past, I've built
my own selects and filled the grid. This time I need to use someone elses stored procedure.
I simply don't know how to get the results back into my VB datagrid. Can someone point
me in the right direction? Thanks.
View 6 Replies
View Related
Feb 1, 2007
How do I filter the results of a stored procedure?
View 2 Replies
View Related
Feb 25, 2008
Hi
I am trying to execute a stored procedure (which returns multiple tables) and use these results to populate an Excel file. I am totally lost on how to capture the results and use it. Any help will be appreciated.
Thanks
View 8 Replies
View Related
May 15, 2007
Hi,I'm creating a stored procedure that pulls information from 4 tables based on 1 parameter. This should be very straightforward, but for some reason it doesn't work.Given below are the relevant tables: SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_Project](
[ProjID] [varchar](300) NOT NULL,
[ProjType] [varchar](20) NULL,
[ProjectTitle] [varchar](max) NULL,
[ProjectDetails] [varchar](max) NULL,
[ProjectManagerID] [int] NULL,
[RequestedBy] [varchar](max) NULL,
[DateRequested] [datetime] NULL,
[DueDate] [datetime] NULL,
[ProjectStatusID] [int] NULL,
CONSTRAINT [PK__tbl_Project__0B91BA14] PRIMARY KEY CLUSTERED
(
[ProjID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tbl_Project] WITH CHECK ADD CONSTRAINT [FK_tbl_Project_tbl_ProjectManager] FOREIGN KEY([ProjectManagerID])
REFERENCES [dbo].[tbl_ProjectManager] ([ProjectManagerID])
GO
ALTER TABLE [dbo].[tbl_Project] CHECK CONSTRAINT [FK_tbl_Project_tbl_ProjectManager]
GO
ALTER TABLE [dbo].[tbl_Project] WITH CHECK ADD CONSTRAINT [FK_tbl_Project_tbl_ProjectStatus] FOREIGN KEY([ProjectStatusID])
REFERENCES [dbo].[tbl_ProjectStatus] ([ProjectStatusID])
GO
ALTER TABLE [dbo].[tbl_Project] CHECK CONSTRAINT [FK_tbl_Project_tbl_ProjectStatus]
-----------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_Report](
[ReportName] [varchar](50) NOT NULL,
[ProjID] [varchar](300) NULL,
[DeptCode] [varchar](50) NULL,
[ProjType] [varchar](50) NULL,
[ProjectTitle] [varchar](500) NULL,
[ProjectDetails] [varchar](3000) NULL,
[ProjectManagerID] [int] NULL,
[RequestedBy] [varchar](50) NULL,
[DateRequested] [datetime] NULL,
[DueDate] [datetime] NULL,
[ProjectStatusID] [int] NULL,
CONSTRAINT [PK_tbl_Report] PRIMARY KEY CLUSTERED
(
[ReportName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tbl_Report] WITH CHECK ADD CONSTRAINT [FK_tbl_Report_tbl_ProjectManager] FOREIGN KEY([ProjectManagerID])
REFERENCES [dbo].[tbl_ProjectManager] ([ProjectManagerID])
GO
ALTER TABLE [dbo].[tbl_Report] CHECK CONSTRAINT [FK_tbl_Report_tbl_ProjectManager]
GO
ALTER TABLE [dbo].[tbl_Report] WITH CHECK ADD CONSTRAINT [FK_tbl_Report_tbl_ProjectStatus] FOREIGN KEY([ProjectStatusID])
REFERENCES [dbo].[tbl_ProjectStatus] ([ProjectStatusID])
GO
ALTER TABLE [dbo].[tbl_Report] CHECK CONSTRAINT [FK_tbl_Report_tbl_ProjectStatus]
--------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_ProjectStatus](
[ProjectStatusID] [int] IDENTITY(1,1) NOT NULL,
[ProjectStatus] [varchar](max) NULL,
CONSTRAINT [PK__tbl_ProjectStatu__023D5A04] PRIMARY KEY CLUSTERED
(
[ProjectStatusID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
-----------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_ProjectManager](
[ProjectManagerID] [int] IDENTITY(1,1) NOT NULL,
[FName] [varchar](50) NULL,
[LName] [varchar](50) NULL,
[Inactive] [int] NULL,
CONSTRAINT [PK__tbl_ProjectManag__7D78A4E7] PRIMARY KEY CLUSTERED
(
[ProjectManagerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
And here is the stored procedure that I wrote (doesn't return results): SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetReportQuery]
(
@ReportName varchar(100)
)
AS
BEGIN
SET
NOCOUNT ON
IF @ReportName IS NULL
BEGIN
RETURN -1
END
ELSE
BEGIN
DECLARE @DeptCode varchar(50), @ProjID varchar(50)
SELECT @DeptCode = DeptCode FROM tbl_Report WHERE ReportName = @ReportName
SET @ProjID = @DeptCode + '-' + '%'
SELECT P.ProjID, P.ProjType, P.ProjectTitle, P.ProjectDetails, M.FName, M.LName, P.DateRequested, P.DueDate, S.ProjectStatus
FROM tbl_Project P, tbl_ProjectManager M, tbl_ProjectStatus S
WHERE ((P.ProjID = (SELECT ProjID FROM tbl_Report WHERE ((ReportName = @ReportName))))
AND (P.ProjectDetails = (SELECT ProjectDetails FROM tbl_Report WHERE ReportName = @ReportName) OR P.ProjectDetails IS NULL)
AND (M.FName = (SELECT FName FROM tbl_ProjectManager WHERE (ProjectManagerID = (SELECT ProjectManagerID FROM tbl_Report WHERE ReportName = @ReportName))) OR M.FName IS NULL)
AND (M.LName = (SELECT LName FROM tbl_ProjectManager WHERE (ProjectManagerID = (SELECT ProjectManagerID FROM tbl_Report WHERE ReportName = @ReportName))) OR M.LName IS NULL)
AND (P.DateRequested = (SELECT DateRequested FROM tbl_Report WHERE ReportName = @ReportName) OR P.DateRequested IS NULL)
AND (P.DueDate = (SELECT DueDate FROM tbl_Report WHERE ReportName = @ReportName) OR P.DueDate IS NULL)
AND (S.ProjectStatus = (SELECT ProjectStatusID FROM tbl_Report WHERE ReportName = @ReportName) OR S.ProjectStatus IS NULL)
)
END
END Can someone see what's wrong? Thanks.
View 7 Replies
View Related
Mar 30, 2008
I am having trouble getting data back from my stored procedure insert commands. Here is what I currently have set up.
1 - A dataset called YagDag
2 - A Table adapter called tadUsers
3 - A Stored Procedure that the tadUsers uses to insert called spUser_i
It seems like the way I have my VB code set up I can only retrieve a return int. I can't figure out how to get my User GUID back, any help would be really appreciated
Public Shared Function AddUser(ByVal EMail As String, ByVal FirstName As String, ByVal LastName As String, ByVal Password As String, ByVal EMailActive As Boolean) As Integer
Dim strEMail = AppFunction.SQLSafeString(EMail)
Dim strFirstName = AppFunction.SQLSafeString(FirstName)
Dim strLastName = AppFunction.SQLSafeString(LastName)
Dim strPassword = AppFunction.SQLSafeString(Password)
Dim blnEMailActive = EMailActive
Dim Users As New YagDagTableAdapters.tadUsers
Dim guidUserYID As Guid = Users.Insert(strFirstName, strLastName, strPassword)
Return 1
End Function -- =============================================
-- Author:Greg Moser
-- Create date: 3/29/2008
-- Description:Adds a User to the tblUser
-- =============================================
ALTER PROCEDURE [dbo].[spUser_i]
@FirstName varchar(50),
@LastName varchar(50),
@Password varchar(16)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @UserYID uniqueidentifier
SET @UserYID = NEWID()
-- Add User to tblUsers
INSERT INTO tblUsers (
YID,
Password,
FirstName,
LastName
)
VALUES (
@UserYID,
@Password,
@FirstName,
@LastName
)
--Return Users YID
SELECT @UserYID
END
View 2 Replies
View Related
Sep 23, 2004
Hi,
Is there a way I can display the data returned from more than one stored procedure in a report using reporting services. Or can I use more than one dataset in the report????
View 3 Replies
View Related
Oct 8, 2004
Two questions - one slightly off topic:
1) How do I write a stored procedure in MS SQL so that it returns a results set?
2) How do I get this into ASP.NET so that I can put the values into controls?
Jags
View 1 Replies
View Related
Oct 28, 1999
I wrote a stored procedure with three variables to be passed as input parameters. The stored procedure runs a select into statement into a temp table. The resulting temp table with another table was queried using right outer join to produce the desired results.
The stored procedure compiles error free.However when I ran the stored procedure with the parameters(3) in ISQL/W (SQL Server 6.5) the headers(column-names) were displayed but no records.
When runned as a query with the same parameter values, records were produced.
Help please urgent.
Regards
Olutimi Ooruntoba
View 2 Replies
View Related
Jul 13, 2006
I'm testing some code to look up values from my database and update a specific field when certain conditions are met. I'm having trouble with some code that is giving me the results I expect when I submit one set of parameters, but is not finding anything in the database for another set, when I know the data exists.
Here's the code for my stored procedure, SP:
Code:
@flightid bigint,
@departuretime smalldatetime
SELECT flightid, flightno, departuretime, origincode, destinationcode
FROM flightschedules
WHERE flightid <> @flightid
AND departuretime = CONVERT(SMALLDATETIME, @departuretime, 120)
And here's the vbscript that calls it:
Code:
vOutboundID = 452
vReturnID = 453
'--- Get the flight details ---
strOrigin = "confirmflightdetails '" & vOutboundID & "';"
set rsOrigin = Server.CreateObject("ADODB.Recordset")
rsOrigin.Open strOrigin, objConn
response.write "origin " & rsOrigin("departuretime") & "<BR>"
strReturn = "confirmflightdetails '" & vReturnID & "';"
set rsReturn = Server.CreateObject("ADODB.Recordset")
rsReturn.Open strReturn, objConn
response.write "return " & rsReturn("departuretime") & "<BR>"
strGetOFID = "SP '" & vOutboundID & "', '" & rsOrigin("departuretime") & "';"
set rsOFID = Server.CreateObject("ADODB.Recordset")
rsOFID.Open strGetOFID, objConn
DO WHILE NOT rsOFID.EOF
response.write "OFNO " & rsOFID("flightno") & " " & rsOFID("flightid") & "<br>"
rsOFID.MoveNext
Loop
strGetRFID = "SP '" & vReturnID & "', '" & rsReturn("departuretime") & "';"
set rsRFID = Server.CreateObject("ADODB.Recordset")
rsRFID.Open strGetRFID, objConn
DO WHILE NOT rsRFID.EOF
response.write "RFNO " & rsRFID("flightno") & " " & rsRFID("flightid") & "<br>"
rsRFID.MoveNext
Loop
Here's the code for confirmflightdetails:
Code:
@flightid bigint
AS
SELECT flightid, flightno, departuretime
FROM flightschedules
WHERE flightid = @flightid
When confirmflightdetails is tested, I the proper results, as confirmed by the response.write statements:
4521092006-07-29 08:00:00
4531102006-07-29 12:05:00
I put the response.write statements and loops in so I could verify the functionality.
Here's what it produces:
out 452
ret 453
origin 7/29/2006 8:00:00 AM
return 7/29/2006 12:05:00 PM
OFNO 109 450
Here's what it should produce:
out 452
ret 453
origin 7/29/2006 8:00:00 AM
return 7/29/2006 12:05:00 PM
OFNO 109 450
RFNO 110 451
If I do this in query analyzer:
Code:
select flightid, flightno, departuretime
from flightschedules
where flightid > 449 and flightid < 454
this is what I get from the database:
flightid flightno departuretime origin destination
4521092006-07-29 08:00:00 A C
4501092006-07-29 08:00:00 A B
4531102006-07-29 12:05:00 C A
4511102006-07-29 13:15:00 B A
What I'm trying to do is look up the chosen flight, then find the flight with the matching origin/destination (the other flight leg) on the same day.
I can't figure out why it's working for one set of parameters and not for the other.
Thanks in advance for any help!
View 1 Replies
View Related
Dec 19, 2004
Hi,
I'm trying to write a nested stored procedure, with the outer proc named "spAssociate", and inner proc named "spSales".
So far I have created the inner proc
CREATE PROCEDURE spSales@userID intASSET NOCOUNT ON
SELECT SalesOppID
FROM Sales_Opportunities
WHERE SalesOppCurrentStatus NOT IN ('Sale Lost','Sales Closed','Sale Closed','Unqualified','Deferred','Dropped')
AND OppOwnerUserID = @userIDGO
This was successfully created. I wanted to use the return set in the outer proc, which I tried creating as:
CREATE PROCEDURE spAssociate(@userID int,
@containerType varchar(100),
@associateType varchar(100))AS SET NOCOUNT ON
SELECT AssociateID
FROM AppRelations
WHERE ContainerType=@containerType
AND ContainerID IN (EXECUTE spSales @userID)
AND AssociateType=@associateType GO
I get an error "incorrect syntax near execute".
How can I use the results from the inner proc for the WHERE condition in my outer proc?
Any help is greatly appreciated. Thanks!
View 2 Replies
View Related
Jul 31, 2007
how can i refer to the results of a query inside a stored procedure?
and use them later
View 8 Replies
View Related
Jul 23, 2005
I'm sure this is an obvious question but much of SQL is new to me.I have a stored procedure and I want to use the results of a selectstatement elsewhere in the stored prcedure. The select statement willreturn at most one record and only one column, here's an example :select top 1 Sequence from MyTablewhere ParentId=4 and Sequence > 200 order by sequenceCan I put this result in a variable? Do I have to use SELECT INTOTempTable and refer to TempTable and then drop the table at the end ofthe stored procedure? I have read a little about cursors, are thesethe way to go?I'm confused as to what my options are, any help or links to help willbe appreciated.Thanks for reading.
View 1 Replies
View Related
Jun 7, 2006
Hi,I created a VB SQL CRL Stored procedure for calculating a value. Valueis returned as belowUsing sConn4 As New SqlConnection("context connection=true")sConn4.Open()scmd = New SqlCommand("SELECT " & var_max, sConn4)sdrd = scmd.ExecuteReader()SqlContext.Pipe.Send(sdrd)End UsingWhen calling this stored procedure from a TSQL stored procedure forusing the value for further processing the value returned to myvariable is 0. The correct value should be 56. In results tab I get thecorrect result, but how can I assign it to my variable @max ?DECLARE @max1 intDECLARE @max intEXEC @max1 = [dbo].[VBSTP_calculate_MAX_no]@vsp_table_name = N'[dbo].[Message]',@vsp_table_key = N'message_no',@vsp_WHERE = N''print @max1 -- value here is 0SET @max = (SELECT @max1)Thanks a lot.
View 1 Replies
View Related
Jul 8, 2006
hi, hvae a little trouble with thos procedure.
When executed it always adds 2 to the value of fields Telefon and Mobil.
Can anyone help me, please
Kurt
ALTER Procedure opdaterbruger
(
@Initialer nvarchar(100),
@Mailadr nvarchar(100),
@Telefon float(8),
@PlusNet float(8),
@Mobil float(8),
@pnmobil float(8)
)
AS
UPDATE Brugere
SET Mailadresse = @Mailadr
WHERE Initialer = @Initialer
IF (@Initialer= '0') OR NOT EXISTS ( SELECT * FROM PNetlokal WHERE Bruger = @Initialer)
INSERT INTO pnetlokal ( Bruger, Kortnr, Telnr )
VALUES ( @initialer, @Plusnet, @Telefon)
ELSE
UPDATE PNetLokal
SET TelNr = @Telefon, KortNr = @PlusNet
WHERE Bruger = @Initialer
IF (@Initialer= '0') OR NOT EXISTS (SELECT * FROM PlusNet WHERE Bruger = @Initialer )
INSERT INTO plusnet (Bruger, Kortnr, Mobilnr)
VALUES (@initialer, @pnMobil, @mobil)
ELSE
UPDATE PlusNet
SET Mobilnr = @Mobil, KortNr = @pnmobil
WHERE Bruger = @Initialer
View 5 Replies
View Related
Sep 30, 2006
I need to return the results of a stored procedure into a temporary table. Something like this:
Select * into #temp from exec (the stored procedure). It appears that I can not do this.
The following will not work for me cause I am not sure how many columns will be returned. I want this to work even if the calling stored procedure is changed (i.e add or take away columns)
insert into (...) exec (the stored procedure.
Does anyone have any ideas how I could do this.
View 4 Replies
View Related
May 17, 2007
If I have multiple selects statements in one stored procedure. How do I access the different results in a report in Reporting Services? Where the first select is the detail lines of my report and the second select is just a field for a my header? Or am I going about this wrong with putting it all the results I want into one stored procedure for one report?
Example stored procedure:
ALTER PROCEDURE [dbo].[proc_DepositsByOfficer]
As
SELECT MASTER_DSC.APP
, MASTER_DSC.BRANCH
, qlkpOfficer.strName
, MASTER_DSC.DSC_OFFICER_49
, qlkpBranchName.strDescrS
, MASTER_DSC.DSC_CUR_BAL_21
, Case MASTER_DSC.APP WHEN 1 Then DSC_CUR_BAL_21 End AS DDA_BAL
, Case MASTER_DSC.APP WHEN 2 Then DSC_CUR_BAL_21 End AS SAV_BAL
, Case MASTER_DSC.APP WHEN 3 Then DSC_CUR_BAL_21 End AS CD_BAL
, MASTER_DSC.DSC_INT_RATE_45
, Case When [DSC_CUR_BAL_21]>0 Then ([DSC_CUR_BAL_21]*[DSC_INT_RATE_45])/100 Else 0 End As ANN_EXP
, Case When [DSC_CUR_BAL_21]>0 And MASTER_DSC.APP=1 Then ([DSC_CUR_BAL_21]*[DSC_INT_RATE_45])/100 Else 0 End AS ANN_EXP_DDA
, Case When [DSC_CUR_BAL_21]>0 And MASTER_DSC.APP=2 Then ([DSC_CUR_BAL_21]*[DSC_INT_RATE_45])/100 Else 0 End AS ANN_EXP_SAV
, Case When [DSC_CUR_BAL_21]>0 And MASTER_DSC.APP=3 Then ([DSC_CUR_BAL_21]*[DSC_INT_RATE_45])/100 Else 0 End AS ANN_EXP_CD
, Case MASTER_DSC.APP WHEN 1 Then 1 End AS DDA_Count
, Case MASTER_DSC.APP WHEN 2 Then 1 End AS SAV_Count
, Case MASTER_DSC.APP WHEN 3 Then 1 End AS CD_Count
, qlkpApplicationCode.strDescrS AS strApplCode
FROM
MASTER_DSC
INNER JOIN qlkpApplicationCode ON MASTER_DSC.APP=qlkpApplicationCode.dblReference
LEFT JOIN qlkpOfficer ON MASTER_DSC.DSC_OFFICER_49=qlkpOfficer.intID
LEFT JOIN qlkpBranchName ON MASTER_DSC.BRANCH=qlkpBranchName.dblReference
WHERE
MASTER_DSC.DSC_CUR_BAL_21<>0
ORDER BY
MASTER_DSC.BRANCH;
SELECT dbo.fBankName() AS BankName;
View 7 Replies
View Related
Mar 21, 2004
Hi
I am trying to upload an excel file into a sql server database. I uploading the spreadsheet from an asp.net page and then running the dts froma stored procedure. But it doesn't work, I am totally lost on what I am doing wrong.
Any help would be greatly appreciated.
Asp.net code;
Dim oCmd As SqlCommand
oCmd = New SqlCommand("exportData", rtConn)
oCmd.CommandType = CommandType.StoredProcedure
rtConn.Open()
With oCmd
.CommandType = CommandType.StoredProcedure
Response.write("CommandType.StoredProcedure")
End With
Try
oCmd.ExecuteNonQuery()
Response.write("ExecuteNonQuery")
Finally
rtConn.Close()
End Try
StoredProcedure;
CREATE PROCEDURE exportData AS
Exec master..xp_cmdshell
'DTSRUN /local/DTS_ExamResults'
GO
Thanks
Rachel
View 4 Replies
View Related
Nov 3, 2006
I've set up a linked server between my SQL 2005 server and my AS400 DB2 server. I can query data successfully.
How do i call a DB2 stored procedure?
View 1 Replies
View Related
Aug 1, 2006
I thought I would impliment a new feature of my web page using stored procedures and the SqlDataSource object, for practice or whatever, since I don't normally use that stuff.
This is the stored procedure:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:Lance Colton
-- Create date: 7/31/06
-- Description:Find the contest winner
-- =============================================
ALTER PROCEDURE [dbo].[AppcheckContest]
-- Add the parameters for the stored procedure here
@BeginDate datetime = '1/1/2006',
@EndDate datetime = '12/31/2006',
@SectionID int = 10,
@WinnerID int = 0 OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT top 1 @WinnerID = P.UserID
FROM cs_Posts P
WHERE P.PostDate BETWEEN @BeginDate AND @EndDate
AND P.SectionID = @SectionID
AND P.UserID <> 2100 -- I don't want to win my own contest...
AND SettingsID = 1000 -- This number could be different if i had more than one CS installed?
AND IsApproved = 1
ORDER BY NEWID() -- yes this is slow, but it works...
RETURN @WinnerID
END
It's really simple - just needs to return the one randomly chosen integer userID. I've tested it in query designer or whatever it's called in Management Studio and it works fine there at least.
Thinking I was done the hard part, I created a new web form in visual studio, dropped a SqlDataSource on it, and used the 'configure data source' wizard from the smart tag to do all the work for me. I didn't have any trouble using the wizard to select my stored procedure, and i'm using the sa connection string to simplify my debugging. I tried using the FormParameter / FormField way of getting the output and setting the input parameters. I can't seem to get it working though. There's no errors or anything, just the output isn't coming through.
Here's the code from the aspx codebehind file:Partial Class Contest
Inherits System.Web.UI.Page
Protected Sub btnSelectWinner_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSelectWinner.Click
Dim stuff As New System.Web.UI.DataSourceSelectArguments
SqlDataSource1.Select(stuff)
SqlDataSource1.DataBind()
lblWinnerID.Text = SqlDataSource1.SelectParameters("WinnerID").ToString
End Sub
End Class
As you can see, I wasn't sure if you're supposed to call databind() or select() to actually get the stored procedure to execute, so I tried both. I was hoping the last line of code there would set the label to the value contained in the @WinnerID parameter, but instead it sets it to "WinnerID".
Here's the code from the .aspx file. Most of this was generated by the Wizard, but I messed around with it a bit. <%@ Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="Contest.aspx.vb" Inherits="Contest" title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="CPHMain" Runat="Server">
<asp:Button ID="btnSelectWinner" runat="server" Text="Find Winner" />
<asp:Calendar ID="Calendar_From" runat="server"></asp:Calendar>
<asp:Calendar ID="Calendar_To" runat="server"></asp:Calendar>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:appcheck-csConnectionString-sa %>"
SelectCommand="AppcheckContest" SelectCommandType="StoredProcedure" CancelSelectOnNullParameter="False">
<SelectParameters>
<asp:FormParameter FormField="Calendar_From" Name="BeginDate" Type="DateTime" />
<asp:FormParameter FormField="Calendar_To" Name="EndDate" Type="DateTime" />
<asp:Parameter DefaultValue="10" Name="SectionID" Type="Int32" />
<asp:formParameter FormField="lblWinnerID" defaultvalue="666" Direction="InputOutput" Name="WinnerID" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
<asp:Label ID="lblWinnerID" runat="server" Text="???"></asp:Label>
</asp:Content>
View 3 Replies
View Related
Feb 2, 2008
Hello,I have written a stored procedure where I prompt the user for the Year and the Month (see below)How do I take the variable @TheMonth and find out how many days is in the month and then loop to display a total for every day in the selected month.Can someone please point me in the right direction.
CREATE PROCEDURE crm_contact_frequency_report
@TheYear varchar(4),@TheMonth varchar(2)
AS
SELECT
/* EMAILS (B) */(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Email B ON A.subject = B.subject WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth) AND (B.directioncode = 1)) AS Total_EmailOutgoing,
(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Email B ON A.subject = B.subject WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth) AND (B.directioncode = 0)) AS Total_EmailImconing,
(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Email B ON A.subject = B.subject WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth) AND (B.directioncode IS NULL)) AS Total_EmailNotListed,
(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Email B ON A.subject = B.subject WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth)) AS Total_All_Emails,
/* PHONE CALLS (C) */(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN PhoneCall C ON A.subject = C.subject WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth) AND (C.directioncode = 1)) AS Total_CallOutgoing,
(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN PhoneCall C ON A.subject = C.subject WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth) AND (C.directioncode = 0)) AS Total_CallIncoming,
(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN PhoneCall C ON A.subject = C.subject WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth) AND (C.directioncode IS NULL)) AS Total_CallNotListed,
(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN PhoneCall C ON A.subject = C.subject WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth)) AS Total_All_Calls,
/* FAXES (D) */(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Fax D ON A.subject = D.subject WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth) AND (D.directioncode = 1)) AS Total_FaxOutgoing,
(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Fax D ON A.subject = D.subject WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth) AND (D.directioncode = 0)) AS Total_FaxIncoming,
(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Fax D ON A.subject = D.subject WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth) AND (D.directioncode IS NULL)) AS Total_FaxNotListed,
(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Fax D ON A.subject = D.subject WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth)) AS Total_All_Faxes
FROM CampaignResponse AGO
View 2 Replies
View Related
Apr 7, 2008
Hi I've run into a slight problem with my stored procedure.If I fire a query in a stored procedure, how can I access the results of that query in the stored procedure itself ?? ALTER PROCEDURE GetDetailsOfWebsiteUserForMail @request_id intAS declare @k int select person_id from Website_user w, Request r where r.user_id=w.user_id and r.request_id=@request_id set @k =person_id /* What do I do here??, value returned is unique */ if (@k!=0) /* Exists */ begin /* do something */ end else /* entry on person table does not exist */ begin /* do something */
end
View 1 Replies
View Related
