Need To Pass Result Set To Other Packages
Sep 19, 2007
We have an SSIS package that executes a query to a DB2 database and stores the result set in a variable of type object. The result set is typically ten rows or less. Several steps in the package loop through these rows and do various processing based on the contents of the rows.
We now have the need for other packages to have access to the original result set. These packages might be run minutes or hours after the first package. Is there a simple way to save the original result set in a file and then pick it up for processing later? Unfortunately, using a SQL Server table to store the results is not an option for us at this time.
Thanks,
Marty
View 3 Replies
ADVERTISEMENT
Feb 19, 2008
Hello,
I am new to SSIS so I'm sure this is easy to do but I can't seem to figure it out. I created a SQL task that creates a result set. I would like to loop through each result of the result set and insert it into another table.
I'm not sure how to map the result set as input to the second SQL task that will do the insert. I can put the variable in the parameter mapping but I don't know what the SQL should look like to insert this into my table. Each row of the result set has five columns. Normally I have something that looks like INSERT...Values(?,?,?,?,?) but because my input parameter is just one result set object this doesn't work.
Help?
View 5 Replies
View Related
Dec 28, 2007
How to get the CASE results highlighted in BOLD into this equation; "(LogOut - LogIn) + (LunchBreak) -(AMBreak) + (PMBreak) AS TimeWorked" ?
Thank you.
CREATE VIEW dbo.vwu_ReportViewASSELECT EmployeeID , LastName , FirstName , LocationCode , UserID , Today , Login , AMBreakOut , AMBreakIn , CASE WHEN ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) >= 0 AND ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) <= 19 THEN '0' WHEN ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) >= 20 AND ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) <= 34 THEN '15' WHEN ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) >= 35 AND ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) <= 49 THEN '30' WHEN ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) > = 50 AND ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) <= 64 THEN '45' ELSE '60' END AS AMBreak , LunchOut , LunchIn , CASE WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) >= 0 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 66 THEN '0' WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) >= 67 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 81 THEN '15' WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) >= 82 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 96 THEN '30' WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) >= 97 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 111 THEN '45' WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) >= 112 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 126 THEN '60' ELSE '75' END AS LunchBreak, PMBreakOut , PMBreakIn , CASE WHEN ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) >= 0 AND ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) <= 19 THEN '0' WHEN ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) >= 20 AND ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) <= 34 THEN '15' WHEN ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) >= 35 AND ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) <= 49 THEN '30' WHEN ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) >= 50 AND ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) <= 64 THEN '45' ELSE '60' END AS PMBreak , Logout , Comments , LoginLogon , AMBreakOutLogon , AMBreakInLogon , LunchOutLogon , LunchInLogon , PMBreakOutLogon , PMBreakInLogon , LogoutLogon ,(LogOut - LogIn) + (LunchBreak) -(AMBreak) + (PMBreak) AS TimeWorked
View 7 Replies
View Related
Aug 8, 2013
How to pass variable from Parent to child and child to Parent Packages is this possible in SQL SSIS 2012. I need this only in SSIS 2012 ...
View 6 Replies
View Related
Apr 7, 2008
Dear All
I have no idea to write a store procedure or only query to pass a string parameter more than 4000 characters into execute() and return result for FETCH and Cursor.
Here is my query sample for yours to understand.
SET NOCOUNT ON
DECLARE @ITEMCODE int, @ITEMNAME nvarchar(50), @message varchar(80), @qstring varchar(8000)
Set @qstring = 'select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm'
PRINT '-------- ITEM Products Report --------'
DECLARE ITEM_cursor CURSOR FOR
execute (@qstring)
OPEN ITEM_cursor
FETCH NEXT FROM ITEM_cursor
INTO @ITEMCODE
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' '
SELECT @message = '----- Products From ITEM: ' +
@ITEMNAME
PRINT @message
-- Get the next ITEM.
FETCH NEXT FROM ITEM_cursor
INTO @ITEMcode
END
CLOSE ITEM_cursor
DEALLOCATE ITEM_cursor
Why i use @qstring? It is because the query will be changed by different critiera.
Regards
Edmund
View 6 Replies
View Related
Jul 31, 2006
Hi
We already used Oracle Datasatage Server the following Query statement for Source and Lookup.here there is parameter maping in the SQl Statement . How can achive in SSIS the Folowing Querystatment?
Query 1: (source View Query)
SELECT
V_RDP_GOLD_PRICE.GDR_PRODUCT_ID, V_RDP_GOLD_PRICE.ASSET_TYPE, V_RDP_GOLD_PRICE.PREFERENCE_SEQ, V_RDP_GOLD_PRICE.RDP_PRICE_SOURCE, TO_CHAR(V_RDP_GOLD_PRICE.PRICE_DATE_TIME,'YYYY-MM-DD HH24:MI:SS'), TO_CHAR(V_RDP_GOLD_PRICE.REPORT_DATE,'YYYY-MM-DD HH24:MI:SS'), V_RDP_GOLD_PRICE.SOURCE_SYSTEM_ID
FROM
V_RDP_GOLD_PRICE V_RDP_GOLD_PRICE
WHERE
REPORT_DATE = (select max(report_date) from V_RDP_GOLD_PRICE where source_system_id = 'RM' )
Query 2: (look up )
SELECT
GDR_PRODUCT_ID,
TO_CHAR(MAX(PRICE_DATE_TIME),'YYYY-MM-DD HH24:MI:SS') ,
TO_CHAR(REPORT_DATE,'YYYY-MM-DD HH24:MI:SS')
FROM
V_RDP_GOLD_PRICE
where
GDR_PRODUCT_ID = :1 and
report_date = TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS') AND
PRICE_DATE_TIME BETWEEN TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS') - 7) AND TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS')
GROUP BY GDR_PRODUCT_ID, TO_CHAR(REPORT_DATE,'YYYY-MM-DD HH24:MI:SS')
please anyone give the sample control flow and how to pass the parameter?
Thanks & regards
Jeyakumar.M
View 1 Replies
View Related
Jan 9, 2007
I've run into a problem with SSIS packages wherein tasks that write or copy files, or create or delete directories, quit execution without any hint of an error nor a failure message, when called from an ASP.NET 2.0 application running on any other machine than the one where the package was created from. By all indications it appeared to be an identity/permissions problem.
Our application involves a separate web server and database server. Both have SQL Server 2005 installed, but the application server originally only had Integration services. The packages are file system-deployed on the application server, and are called using Microsoft.SqlServer.Dts.Runtime methods. For all packages that involve file system tasks, the above problem occurs.
When the above packages are run using the command prompt (either DTEXEC or DTEXECUI) the packages execute just fine. This is expected since we are using an administrative account. However when a ShellExecute of the same command is called from ASP.NET, the same problem occurs.
I've tried giving administrative permissions to the ASPNET worker process user to no avail.
I have likewise attempted to use the SQL Server Agent job approach but that approach might not be acceptable for our clients since it means installing SQL Server 2005 Database services on the application server.
I have read the relevant threads in this forum, namely http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1044739&SiteID=1 and http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=927084&SiteID=1 but failed to find any solution appropriate for our set up.
Anybody got any idea on how to go about this?
View 33 Replies
View Related
Apr 22, 2015
We manage some SSIS servers, which has only SSIS and SSIS tools installed on them and not the sql server DB.
SSIS packages and configuration files are deployed on a NAS. We run the SSIS packages through DTEXEC by logging in to the server.
We want to allow developers to run their packages on their own on the server, but at the same time we dont want to give them physical access on the server i.e we do not want to add them into RDP users list on server properties. We want them to allow running their packages remotely on the server.
One way We could think of is by using powershell remoting and we are working on that. But is there any other way or any tool already present for the same.
View 4 Replies
View Related
Jul 20, 2005
I need to send the result of a procedure to an update statement.Basically updating the column of one table with the result of aquery in a stored procedure. It only returns one value, if it didnt Icould see why it would not work, but it only returns a count.Lets say I have a sproc like so:create proc sp_countclients@datecreated datetimeasset nocount onselect count(clientid) as countfrom clientstablewhere datecreated > @datecreatedThen, I want to update another table with that value:Declare @dc datetimeset @dc = '2003-09-30'update anothertableset ClientCount = (exec sp_countclients @dc) -- this line errorswhere id_ = @@identityOR, I could try this, but still gives me error:declare @c intset @c = exec sp_countclients @dcWhat should I do?Thanks in advance!Greg
View 4 Replies
View Related
Dec 26, 2007
I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".
Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.
Thanks!
View 5 Replies
View Related
Dec 11, 2007
Hi all,
I copied the following code from Microsoft SQL Server 2005 Online (September 2007):
UDF_table.sql:
USE AdventureWorks;
GO
IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL
DROP FUNCTION dbo.ufnGetContactInformation;
GO
CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)
RETURNS @retContactInformation TABLE
(
-- Columns returned by the function
ContactID int PRIMARY KEY NOT NULL,
FirstName nvarchar(50) NULL,
LastName nvarchar(50) NULL,
JobTitle nvarchar(50) NULL,
ContactType nvarchar(50) NULL
)
AS
-- Returns the first name, last name, job title, and contact type for the specified contact.
BEGIN
DECLARE
@FirstName nvarchar(50),
@LastName nvarchar(50),
@JobTitle nvarchar(50),
@ContactType nvarchar(50);
-- Get common contact information
SELECT
@ContactID = ContactID,
@FirstName = FirstName,
@LastName = LastName
FROM Person.Contact
WHERE ContactID = @ContactID;
SELECT @JobTitle =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee e
WHERE e.ContactID = @ContactID)
THEN (SELECT Title
FROM HumanResources.Employee
WHERE ContactID = @ContactID)
-- Check for vendor
WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
THEN (SELECT ct.Name
FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
-- Check for store
WHEN EXISTS(SELECT * FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE sc.ContactID = @ContactID)
THEN (SELECT ct.Name
FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE ContactID = @ContactID)
ELSE NULL
END;
SET @ContactType =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee e
WHERE e.ContactID = @ContactID)
THEN 'Employee'
-- Check for vendor
WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
THEN 'Vendor Contact'
-- Check for store
WHEN EXISTS(SELECT * FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE sc.ContactID = @ContactID)
THEN 'Store Contact'
-- Check for individual consumer
WHEN EXISTS(SELECT * FROM Sales.Individual i
WHERE i.ContactID = @ContactID)
THEN 'Consumer'
END;
-- Return the information to the caller
IF @ContactID IS NOT NULL
BEGIN
INSERT @retContactInformation
SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;
END;
RETURN;
END;
GO
----------------------------------------------------------------------
I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise.
Thanks in advance,
Scott Chang
View 1 Replies
View Related
Feb 13, 2001
HI,
I ran a select * from customers where state ='va', this is the result...
(29 row(s) affected)
The following file has been saved successfully:
C:outputcustomers.rpt 10826 bytes
I choose Query select to a file
then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend
invalid TLV record
Thanks for your help
Ali
View 1 Replies
View Related
May 1, 2008
As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC.
Tables I have are structured as follows:
SHOWS
showID
showTitle
SHOWACCESS
showID
remoteID
VIDEOS
videoDate
showID
SQL is as follows:
SELECT shows.showID AS showID, shows.showTitle AS showTitle,
(SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID)
FROM shows, showAccess
WHERE shows.showID = showAccess.showID
AND showAccess.remoteID=21
ORDER BY vidDate DESC;
I had it ordering by showTitle and it worked fine, but I need it to order by vidDate.
Can anyone shed some light on where I am going wrong?
thanks
View 3 Replies
View Related
Aug 2, 2007
I am trying to code a WHERE xxxx IN ('aaa','bbb','ccc') requirement but it the return values for the IN keyword changes according to another column, thus the need for a CASE function.
WHERE GROUP.GROUP_ID = 2 AND DEPT.DEPT_ID = 'D' AND WORK_TYPE_ID IN ( CASE DEPT_ID WHEN 'D' THEN 'A','B','C' <---- ERROR WHEN 'F' THEN 'C','D ELSE 'A','B','C','D' END )
I kept on getting errors, like
Msg 156, Level 15, State 1, Line 44Incorrect syntax near the keyword 'WHERE'.
which leads me to assume that the CASE ... WHEN ... THEN statement does not allow mutiple values for result expression. Is there a way to get the SQL above to work or code the same logic in a different manner in just one simple SQL, and not a procedure or T-SQL script.
View 3 Replies
View Related
Aug 2, 2006
Hi all
Our data management system currently runs DTS packages using DTSPKG.dll.
I am currently looking at the possibliity of replacing the DTS packages and SQL 2000 with DTSX packages using SSIS in SQL 2005.
Do I need a new dll? or will the current dtspkg.dll handle the new DTSX packages?
Many thanks in advance!
View 1 Replies
View Related
Apr 7, 2015
I'm using a subquery to return a delivery charge line as a column in the result set. I want to see this delivery charge only on the first line of the results for each contract. Code and results are below.
declare @start smalldatetime
declare @end smalldatetime
set @start = '2015-03-22 00:00' -- this should be a Sunday
set @end = '2015-03-28 23:59' -- this should be the following Saturday
select di.dticket [Contract], di.ddate [Delivered], di.item [Fleet_No], di.descr [Description], dd.min_chg [Delivery_Chg], dd.last_invc_date [Delivery_Invoiced],
[code]....
In this example, I only want to see the delivery charge of 125.00 for the first line of contract HU004377. For simplicity I have only shown the lines for 1 contract here, but there would normally be many different contracts with varying numbers of lines, and I only want to see the delivery charge once for each contract.
View 6 Replies
View Related
Mar 2, 2008
help strange result whan i do this
Code Snippet
SELECT unit_date, unit, ISNULL(NULLIF ((unit + DATEDIFF(mm, GETDATE(), unit_date)) % 4, 0), 4) AS new_unit
FROM dbo.empList
i try to get next unit value to next month
why i get this -1
on date
01/01/2008
1
-1
unit_date unit new_unit
01/02/2008
2
1
01/02/2008
1
4
01/01/2008
1
-1
01/02/2008
1
4
21/01/2008
1
-1
21/01/2008
1
-1
01/02/2008
1
4
TNX
View 3 Replies
View Related
Nov 24, 2004
Is it possible from sql to use SAS as an object? And if so how?
I was recently tasked with Data Profiling and sitting next to me is a SAS developer, who just has to run a couple of functions, to do what has taken me days (and I haven't finished!). If I could call out to SAS and run the SAS functions it would be great. I have looked on the net and can see SAS calling out to SQL but not the other way round. Technically I'm probably not up to this but I don't want this to hold me back... so any help would be great.
View 4 Replies
View Related
Sep 26, 2005
okay..
Here is my first post from pass.
I am here on sunday getting ready for the first of 5 days of nothing but SQL Server
Every time i come to this thing i still get excited about the whole community thing.
who else from dbforums is going to be here? i would be interesting to put faces with those names.
in any event i will post interesting tidbits from the event if any come to mind. starting with the microsoft givaway of a copy of std edtion 2005 (when released) to any attendee. you get a coupon and you register a pin on a web site and they will send you a copy upon release. its a nice perk i guess, but msdn and the CTPs kind of take the savoir faire out of it.
oh well.
remember to post your name if you are attending.
View 14 Replies
View Related
Jul 20, 2005
is it possible to do:(A)declare @numberofitems Int@numberofitems = select max(itemorder)from store, department, etc.and pass the @numberofitems to a #tempStore table, like:(B)(store, department, @numberofitems,...)I got itemorder but not the number of items in each departmentAlex--Sent by 3 from yahoo part from comThis is a spam protected message. Please answer with reference header.Posted via http://www.usenet-replayer.com/cgi/content/new
View 2 Replies
View Related
Oct 19, 2006
Hey everyone I'm having trouble finding a way to pass a particular paramater, my main goal of this is to create a custom paging and sorting control for the DataList, but we wont worry about all the un-nessesarry code, here is what I am dealing with... Sub Page_Load()
Dim Conn As SqlConnection
Dim Query As String
Dim SqlComm As SqlCommand
Dim myDataReader As SqlDataReader
' Define connection object
Conn = New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
' Define query to retrieve main category values
Query = "SELECT [productMainCategory] FROM [User_Products] WHERE [productMainCategory] = @productMainCategory"
' Define command object
SqlComm = New SqlCommand(Query, Conn)
' Open connection to database
Conn.Open()
' Create DataReader
myDataReader = SqlComm.ExecuteReader()
' Iterate through records and add to array list
While myDataReader.Read()
IDList.Add(myDataReader("productMainCategory"))
End While
' Close DataReader and connection objects
myDataReader.Close()
myDataReader = Nothing
Conn.Close()
Conn = Nothing
' If page has not been posted back, retrieve first page of records
If Not Page.IsPostBack Then
Paging()
End If
End Sub
The Entire Point to this bit of code is to open a connection to the database find the category productsMainCategory, count all the fields with the Name 3D Art --> and save that number into an Array List where I will be using it later on in my code......The big old problem here is that it counts every single field in the productsMainCategory, Columb.........I need to figure out how to pass it a parameter so that it only counts my 3D Art --> fields, I have tried using this bit of code but nothing SqlComm.Parameters("@productMainCategory").Value = "3D Art -->"Does anyone have any ideas of how I might go about donig this?Thank You..
View 4 Replies
View Related
Mar 15, 2007
I cannot remember how to pass a value to a stored procedure. I would work this through but I am really running out of time, any help greatly appreciated. This is my stored procedure and I need to pass CompanyID from the code behind page in for the stored procedure @C_ID value.
PROCEDURE dbo.EditCompanyInfo @C_ID int, @CS_CompanyName nchar(100), @CS_City nchar(50) AS UPDATE tblCompanyInfo_Submit SET CS_CompanyName = @CS_CompanyName, CS_City = @CS_City WHERE C_ID = @C_ID RETURN
This is my aspx. page:
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" Runat="Server" > <asp:TextBox ID="TextBox1" runat="server"> </asp:TextBox> <br /> <asp:TextBox ID="TextBox2" runat="server"> </asp:TextBox> <br /> <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox><br /> <asp:DetailsView ID="DetailsView1" runat="server" DataSourceID="SqlDataSource2" Height="50px" Width="125px"> <Fields> <asp:CommandField ShowEditButton="True" ShowInsertButton="True" /> </Fields> </asp:DetailsView> <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString2 %>" InsertCommand="CompanyInfoSubmit" InsertCommandType="StoredProcedure" OnInserted="SqlDataSource2_Inserted" SelectCommand="SELECT CS_CompanyName, CS_City FROM tblCompanyInfo_Submit WHERE C_ID = @CompanyID " UpdateCommand="EditCompanyInfo" UpdateCommandType="StoredProcedure"> <UpdateParameters> <asp:Parameter Name="CS_CompanyName" Type="String" /> <asp:Parameter Name="CS_City" Type="String" /> </UpdateParameters> <InsertParameters> <asp:Parameter Direction="ReturnValue" Name="ReturnValue" Type="Int32" /> <asp:Parameter Name="CS_CompanyName" Type="String" /> <asp:Parameter Name="CS_City" Type="String" /> </InsertParameters> </asp:SqlDataSource> <br /></asp:Content>
CODE BEHIND:
public partial class aaatest : System.Web.UI.Page{ int CompanyID; protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { DetailsView1.ChangeMode(DetailsViewMode.Insert); TextBox1.Text = "insert"; TextBox3.Text = Convert.ToString(CompanyID); } } protected void SqlDataSource2_Inserted(object sender, SqlDataSourceStatusEventArgs e) { { foreach (System.Data.SqlClient.SqlParameter param in e.Command.Parameters) { string RValue = Server.HtmlEncode(param.Direction.ToString()); if ( RValue == "ReturnValue" && Page.IsPostBack) { TextBox1.Text = Server.HtmlEncode(param.Value.ToString()); TextBox2.Text = "Return"; CompanyID = Convert.ToInt16(TextBox1.Text); TextBox3.Text = Convert.ToString(CompanyID); } } } }}
View 1 Replies
View Related
Aug 3, 2007
I have a stored procedure that I want to pass in a number of months and use it in my where caluse. I need to minus the number of months that as passed in by @TaskMonths.
But it is minusing days not months.
@TaskMonths intWHERE (tblTasks.Caller = @Caller) AND (tblTasks.DueDate BETWEEN CONVERT(varchar, GETDATE() - @TaskMonths, 101) AND GETDATE()) I apprecaite any help.
View 2 Replies
View Related
Oct 31, 2007
I'm trying to pass a comma delimited list of numbers to a parameter to use in an IN of my Where clause.
Dim MySqlParamSelected As New SqlParameter("@Selected", SqlDbType.Int) Cmd.Parameters.Add(MySqlParamSelected) MySqlParamSelected.Value = Session("intSelected")
WHERE tblSelected.Selected_ID IN (@Selected)
It will work if I only pass it one number (e.g. 78), but when I pass it more than one (e.g. 78,79) it fails.
Here is the error:
Msg 119, Level 15, State 1, Line 4Must pass parameter number 7 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.
Does anyone know how I can correctly pass multiple numbers to use in my IN?
View 11 Replies
View Related
Jan 29, 2008
How do i pass a parameter to a stored proc using this command:
object SqlHelper.ExecuteScalar (SqlConnection connection, string spName, params object[] parameterValues)
let's say that my parameter name is @ID and the value is IDval ..
thanx
View 2 Replies
View Related
Oct 20, 2000
Good morning one and all,
Does any1 know if when A SQL pass through query is executed in access (to a SQL srv dbase) wether or not access will wait until that call is completed before running other steps (in an macro say). I don't think it does but would appreciate a more definitive answer.
TIA for any and all help
Gurmi
View 6 Replies
View Related
Oct 3, 2001
Anybody know if the PASS Conference in Orlando is going to happen or not? The website www.sqlpass.org is in a holding pattern saying that the conference is "POSTPONED," but I would have thought a post about the intention to resched or cancel the gathering would be in order.
Anyone know what's up?
View 1 Replies
View Related
May 19, 2004
Hi Guys: I am trying to get a top parameter dynamically but it does not seem to work
SELECT TOP
(SELECT COUNT(DISTINCT RegionID) AS COUNT
FROM Sales)
*
FROM Sales
but when I hard code the value it works
SELECT TOP
2
*
FROM Sales
any ideas...Thanks
View 6 Replies
View Related
Aug 8, 2007
Hello,
I placed a post regarding this issue previously but no success. So I thought I explain everything properly this time in a new post. Thanks
I have created a stored procedure which passes variables to the ssis package and then executes the package.
The two variables inside the ssis package are @FileName and @ConnectionPath
As you can see from the below stored procedure, xp_cmdshell is used to execute the package.
If only the first variable is used in the package and the @connectionPath variable is hardcoded inside the package then package runs fine.
Problem is in this particular call as you see below because @ConnectionPath is included.
The output of print is:
dtexec /f d:sysapplCEMSSISImportsTradesBaseProfiles2.dtsx /set Package.Variables[User::FileName].Properties[Value];"d:ApplDataCEMWorkingTempprofiles.csv"
/set Package.Variables[User::ConnectionPath].Properties[Value];"Data Source=servername1instancename1, 2025;Initial Catalog=CounterpartyExposure;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"
Error is:
Error: 2007-08-08 08:46:01.29
Code: 0xC0202009
Source: BaseProfiles2 Connection manager "CounterpartyExposure"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for ODBC Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".
End Error
if only the output is run in the query analyser then the error is:
The identifier that starts with 'Data Source=gblond088sjyMSQL_curves_DEV1, 2025;Initial Catalog=CounterpartyExposure;Provider=SQLNCLI.1;Integrated Security=SSPI' is too long. Maximum length is 128.
/*********************************************************************************
uspCEMTradeExecutePackage2 'd:sysapplCEMSSISImportsTradesStaticMappingOverride.dtsx',
'StaticMappingOverride.csv',
'Data Source=servername1instancename1, 2025;Initial Catalog=CounterpartyExposure;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;'
*********************************************************************************/
ALTER procedure [dbo].[uspCEMTradeExecutePackage2]
@FullPackagePath varchar(1000),
@FileName varchar(500),
@ConnectionPath varchar(1000)
as
declare @returncode int
declare @cmd varchar(1000)
declare @FilePath varchar(1000)
declare @FullFilePath varchar(1000)
set @FilePath = 'd:ApplDataCEMWorkingTemp'
set @FullFilePath = @FilePath + @FileName
print ' ----------- ' + @FileName
set @cmd = 'dtexec /f ' + @FullPackagePath + ' /set Package.Variables[User::FileName].Properties[Value];"' + @FullFilePath + '"'
set @cmd = 'dtexec /f ' + @FullPackagePath +
' /set Package.Variables[User::FileName].Properties[Value];"' + @FullFilePath + '"
/set Package.Variables[User::ConnectionPath].Properties[Value];"' + @ConnectionPath + '"'
print @cmd
set nocount on
begin try
exec @returncode = master..xp_cmdshell @cmd
end try
begin catch
exec @LastGoodVersionSP
DECLARE @msg nvarchar(200)
SET @msg = ('Error during execute package')
EXECUTE uspErrorReporter @msg
end catch
set nocount off
View 3 Replies
View Related
Dec 27, 2007
I have created this sp to and i cant figure out how to use the top 3 as a paramter. I want to be able to use any # like top1,2,3,4,5 etc. I not sure how to compose the syntax i have my sp below.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_moveBackupTables]
@serverName varchar(50) --Parameter
AS
BEGIN
declare @tableName varchar(50) --Variable
--Declare Cursor
DECLARE backup_Cursor CURSOR FOR
select name from adventureworksdw.dbo.sysobjects
where name like 'MyUsers_backup_%' and xtype = 'U'
and name not in(select top 3 name from adventureworksdw.dbo.sysobjects
where name like 'MyUsers_backup_%' and xtype = 'U' order by name desc)
OPEN backup_Cursor
--Move to initial record in the cursor and set variable(s) to result values
FETCH NEXT FROM backup_Cursor
INTO @tableName
--Loop through cursor records
WHILE @@FETCH_STATUS = 0
BEGIN
--dynamically build create table
Declare @SQL varchar(2000)
Set @SQL = 'CREATE TABLE ' + @serverName + '.dbo.' + @tableName + '(
[Id] [numeric](18, 0) NOT NULL,
[Field1] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Field2] [numeric](18, 0) NOT NULL,
[Field3] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Field4] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Field5] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_1' + @tableName + '] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]'
EXEC(@SQL)
--Insert values into new dynamically created table
Declare @backupTableRecords varchar(1000)
Set @backupTableRecords = 'Insert into ' + @serverName + '.dbo.' + @tableName + ' SELECT * FROM AdventureWorksDW.dbo.'+ @tableName
exec(@backupTableRecords)
--Drop old table
Declare @dropTable varchar(200)
set @dropTable = 'drop table adventureworksdw.dbo.' + @tableName
exec(@dropTable)
--Move to the next record in the Cursor and set variable(s) value(s)
FETCH NEXT FROM backup_Cursor INTO @tableName
END
CLOSE backup_Cursor
DEALLOCATE backup_Cursor
END
View 2 Replies
View Related
Nov 7, 2007
HiWe still use SqlServer 2000 and DTSs.My question is: Can I pass a parameter to a DTS and if yes then how ?We would like to plan our screen like this.The user will input a parameter on the screen/form and then the DTS willbe activated using the value inputed.We do have how to work around it if parameters can't be passed but itwould make things a lot easier and straightforward if we could.Thanks !David Greenberg
View 2 Replies
View Related
Aug 8, 2007
Hello,
I placed a post regarding this issue previously but no success. So I thought I explain everything properly this time in a new post. Thanks
I have created a stored procedure which passes variables to the ssis package and then executes the package.
The two variables inside the ssis package are @FileName and @ConnectionPath
As you can see from the below stored procedure, xp_cmdshell is used to execute the package.
If only the first variable is used in the package and the @connectionPath variable is hardcoded inside the package then package runs fine.
Problem is in this particular call as you see below because @ConnectionPath is included.
The output of print is:
dtexec /f d:sysapplCEMSSISImportsTradesBaseProfiles2.dtsx /set Package.Variables[User::FileName].Properties[Value];"d:ApplDataCEMWorkingTempprofiles.csv"
/set Package.Variables[User::ConnectionPath].Properties[Value];"Data Source=servername1instancename1, 2025;Initial Catalog=CounterpartyExposure;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"
Error is:
Error: 2007-08-08 08:46:01.29
Code: 0xC0202009
Source: BaseProfiles2 Connection manager "CounterpartyExposure"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for ODBC Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".
End Error
if only the output is run in the query analyser then the error is:
The identifier that starts with 'Data Source=gblond088sjyMSQL_curves_DEV1, 2025;Initial Catalog=CounterpartyExposure;Provider=SQLNCLI.1;Integrated Security=SSPI' is too long. Maximum length is 128.
/*********************************************************************************
uspCEMTradeExecutePackage2 'd:sysapplCEMSSISImportsTradesStaticMappingOverride.dtsx',
'StaticMappingOverride.csv',
'Data Source=servername1instancename1, 2025;Initial Catalog=CounterpartyExposure;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;'
*********************************************************************************/
ALTER procedure [dbo].[uspCEMTradeExecutePackage2]
@FullPackagePath varchar(1000),
@FileName varchar(500),
@ConnectionPath varchar(1000)
as
declare @returncode int
declare @cmd varchar(1000)
declare @FilePath varchar(1000)
declare @FullFilePath varchar(1000)
set @FilePath = 'd:ApplDataCEMWorkingTemp'
set @FullFilePath = @FilePath + @FileName
print ' ----------- ' + @FileName
set @cmd = 'dtexec /f ' + @FullPackagePath + ' /set Package.Variables[User::FileName].Properties[Value];"' + @FullFilePath + '"'
set @cmd = 'dtexec /f ' + @FullPackagePath +
' /set Package.Variables[User::FileName].Properties[Value];"' + @FullFilePath + '"
/set Package.Variables[User::ConnectionPath].Properties[Value];"' + @ConnectionPath + '"'
print @cmd
set nocount on
begin try
exec @returncode = master..xp_cmdshell @cmd
end try
begin catch
exec @LastGoodVersionSP
DECLARE @msg nvarchar(200)
SET @msg = ('Error during execute package')
EXECUTE uspErrorReporter @msg
end catch
set nocount off
View 1 Replies
View Related
Jul 11, 2007
Using SSIS foreach loop I get the files names inside a folder on the network.
How do I pass this variable i.e. file name to a stored procedure?
Thanks
View 9 Replies
View Related
