Execute Stored Proc From OLE DB Destination
Apr 19, 2007
is is posible to execute a stored proc (with parameters) from an OLE DB Destination ??
reason we are trying this is cos
our current setup is an OLE DB Command doing the first database update and then passing over to an OLE DB Destination that does the second update. There is error handling coming off the OLE DB Command to a Script component that passes to an OLE DB Destination.
we are having a problem getting the error reporting working from the OLE DB Command - the updates work fine - but not getting any updates of the error database when there is an error.
have got the error reporting fine on the OLE DB Destination.
if we can execute the stored proc from the OLE DB Destination, we will then do both updates via one stored procedure executed by the OLE DB Destination.
thx
m
n.b. think the updating of the error logs from the OLE DB Command used to work - but cant get it to work now ??!!!!??
View 4 Replies
ADVERTISEMENT
Oct 14, 2007
I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.
View 3 Replies
View Related
Jul 13, 2004
ok I have a stored procedure in my MS-SQL Server database.
It looks something like this.....
CREATE PROCEDURE updatePCPartsList
(
@Descriptionvarchar(255),
@ManCodevarchar(255),
@ProdCodevarchar(255),
@Pricedecimal(6,2),
@Commentsvarchar(255)
)
AS
declare @IDFound bigint
declare @LastChangedDate datetime
select @LastChangedDate = GetDate()
select @IDFound = PK_ID from PCPartsList where ProdCode = @ProdCode
if @IDFound > 0
begin
update PCPartsList set Description = @Description, ManCode = @ManCode, ProdCode = @ProdCode, Price = @Price, Comments = @Comments, LastChanged = @LastChangedDate where PK_ID = @IDFound
end
else
insert into PCPartsList (Description, ManCode, ProdCode, Price, Comments, LastChanged) values(@Description, @ManCode, @ProdCode, @Price, @Comments, @LastChangedDate)
GO
It executes fine so I know i've done that much right....
But what i'd like to know is how I can then return a value - specifically @LastDateChanged variable
I think this is a case of i've done the hard part but i'm stuck on the simple part - but i'm very slowly dragging my way through learning SQL.
Someone help?
View 3 Replies
View Related
Jul 7, 2006
is there a way to execute SSIS Package through stored proceedure.
Or any other method of executing the SSIS Package command line in stored proceedure
Thanks,
jas
View 10 Replies
View Related
Nov 1, 2005
Hi, I have a table containing SQl statements. I need to extract the statements and execute them through stored procedure(have any better ideas?)
Table Test
Id Description
1Insert into test(Id,Name) Values (1,'Ron')
2Update Test Set Name = 'Robert' where Id = 1
3Delete from Test where Id = 1
In my stored procedure, i want to execute the above statements in the order they were inserted into the table. Can Someone shed some light on how to execute multiple sql statements in a stored procedure. Thanks
Reo
View 2 Replies
View Related
Jun 2, 2006
I think I'm being a bit thick, but I just cannot figure out the proper syntax to call a stored proc on a SQL named instance I have. I've tried many variations, but here is the basic format of what I'm trying:
EXEC ServerInstance.DB.dbo.usp_fm_proc 1
It seems it doesn't like the as I get an error "Incorrect syntax near 'Instance'.
What am I missing here?
View 3 Replies
View Related
Aug 6, 2007
My Package Exist in
C:WROXWROXinexcel_database package exist
OR sql server agent ->job-> excel_database package exist
now i want to excute the from master database how please send the
stored procedure code
Regards
koti
View 1 Replies
View Related
Sep 18, 2007
Hi all,
I have a problem while i create a proxy account in SQL Sever 2005.The situation is like this...There is a user who has an login in to the server.He has a stored procedure which calls some on the SSIS packages and XP_cmdshell...so this stored procedure basically load some data in to the tables .So for the login in order to execute the stored procedure as he is not a Sys admin I have created a proxy account in my account as Iam an SA and then in the proxies and in principals I selected his login name and this way I have created a credential and a proxy account.
Now the problem is if he logins with his id and password and try to execute the stored procedure it gives an error message
Server: Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1
The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.
....so this mean the login is not able to see the proxy account.So what I did is I created a job and then in the job owner tab I have selected his login and then created a step with the type operating system (CmdExec) as I need to just execute the stored procedure and used the proxy account that I have created.
so I gave the command -- exec <stored procedure> --.
But this job fails and gives the error message as
[298] SQLServer Error: 536, Invalid length parameter passed to the SUBSTRING function. [SQLSTATE 42000]....
So now ....first My question is am I doing in a right way....if its right then why Iam not able to execute the stored procedure.
If there is any other way through which I can execute the stored procedure using a proxy account for the logins who are not sys admins....please do let me know.....
Thanks
Raja.V
View 1 Replies
View Related
Apr 8, 2008
I am trying to test a stored proc and I can't execute the stored proc when I use a Guid. Has anyboyd had the same problem. I right click in VS 2008 on the Stored proc click Execute and fill in the variables one of them is a Guid and no records are returned.
Here is my guid, I have tried '{0a5c25fa-3aac-4abc-8d07-6d76ab46c9b2}', {0a5c25fa-3aac-4abc-8d07-6d76ab46c9b2} and "0a5c25fa-3aac-4abc-8d07-6d76ab46c9b2" no success. This stored proc might not work but I have had the same issue with other stored procs when I wanted to test them by using execute and using a Guid.
Testing with an int works fine when I try to Select the records by UserID which is a Guid no cigar. I have notice this behavier before is there a way around this problem, thanks newbie
View 3 Replies
View Related
Mar 18, 2014
I am a SysAdmin on a test/dev SQL instance. Other non-sysadmin users (developers) need the ability to execute DBCC commands like the following:
DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL
OR
DBCC FREEPROCCACHE
I tried creating a store proc in a user database and granting those non-sysadmin users EXECUTE permission on it as so:
CREATE PROC spFreeSystemCache
WITH EXECUTE AS 'sa'
AS
DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL
GO
When I try to create this proc, I get the following error:
Msg 102, Level 15, State 1, Procedure spFreeSystemCache, Line 2
Incorrect syntax near 'sa'.
Ok, so I can't EXECUTE AS sa...
View 5 Replies
View Related
Sep 17, 2007
I am writing a SQL 2000 stored procedure which uses an €˜EXEC @sqlString€™ statement. The @sqlString is generated at runtime. I want to give as few permissions as possible and currently allow users to access the database tables using only the stored procedures provided. However, with €˜Exec€™ I discover that I need to grant permissions on the actual tables to the users or groups. I would like to avoid this. I would also prefer not having to maintain a separate user with table level permissions and hardcoding the stored procedure with these details.
Is there anyway for me to dynamically generate the required SQL statement within my stored procedure and let SQL know that this stored procedure is allowed to select whatever tables it wants to without having to define permissions on the tables?
View 1 Replies
View Related
Aug 21, 2007
Hello,
i need to create temporary table inside SP.
i having one string variable @strQuery which contain dynamic query inside SP.
i am executing that trhough execute sp_executesql @strQuery once query build.
now instead of select query , i want to creat hash table.
so i wrote :
set @strQuery = "Select * into #tmp_tbl from table_name..."
when i tried to execute it through
execute sp_executesql @strQuery , its giving error 'Invalid object name '#tmp_tbl'
If i removed Hash then it works fine. even for double Hash also its work fine.
but i want hash table only as i want that table local to that user.
Even direct execution of select statement without @strQuery works fine. but i want to execute @strQuery through execute sp_executesql @strQuery only as query is dynamic .
please guide me how to do this?
its very urgent for me.
thanks in advance.
View 4 Replies
View Related
Feb 13, 2008
I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code.
So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message:
Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages.
(1 row(s) affected)
(1 row(s) affected)
I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution?
Also, Is there a way to trace into a stored procedure through Query Analyzer?
-------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised:
SELECT @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId
IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END
View 3 Replies
View Related
Aug 24, 2006
I am having trouble executing a stored procedure on a remote server. On my
local server, I have a linked server setup as follows:
Server1.abcd.myserver.comSQLServer2005,1563
This works fine on my local server:
Select * From [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.dbo.TableName
This does not work (Attempting to execute a remote stored proc named 'Data_Add':
Exec [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.Data_Add 1,'Hello Moto'
When I attempt to run the above, I get the following error:
Could not locate entry in sysdatabases for database 'Server1.abcd.myserver.comSQLServer2005,1563'.
No entry found with that name. Make sure that the name is entered correctly.
Could anyone shed some light on what I need to do to get this to work?
Thanks - Amos.
View 3 Replies
View Related
Jun 15, 2006
Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK
View 3 Replies
View Related
Feb 23, 2007
I have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.
View 1 Replies
View Related
Sep 5, 2004
--------------------------------------------------------------------------------
Can someone shed some light??? I have searched hi and low for an answer to my dilemma. I have an mdx query that works fine in my olap environment but when I try to run the same query from sql 2000, one of my calculations comes back as a long int and includes (E-02) at the end. Can you please review my store procedure and provide some insight?
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE GetWeeklyTrendData
@Level int,
@year int,
@week int,
@PrintMDX int = 0
AS
SET NOCOUNT ON
DECLARE @MDX varchar(4000),
@CubeQuery varchar(4500), @Leaf int, @Tier int
BEGIN
SET @MDX = '''
WITH
MEMBER [Measures].[OrgName] As ''''[GL].CurrentMember.Name''''
MEMBER [Measures].[LevelSKey] As ''''[GL].CurrentMember.Properties("Key")''''
MEMBER [Measures].[OrgType] As ''''Iif(IsLeaf([GL].CurrentMember), 1, 0)''''
MEMBER Measures.Lookp AS '''' LookupCube("cubeName", "(Measures.Customers, " + [GL].CurrentMember.UniqueName + "," + [Week].CurrentMember.UniqueName + ")" )''''
MEMBER Measures.Trend AS '''' Measures.Customers / LookupCube("cubeName", "(Measures.Customers, " + [GL].CurrentMember.UniqueName + "," + [Week].CurrentMember.UniqueName + ")" )''''
, SOLVE_ORDER = 1, FORMAT_STRING = ''''Percent''''
SELECT
{
CrossJoin
(
{ [Week].[' + CAST(@year AS VARCHAR) + '].[Week ' + CAST(@week AS VARCHAR)+ '].Lag(12) : [Week].[' + CAST(@year AS VARCHAR) + '].[Week ' + CAST(@week AS VARCHAR) + '] },
{ Measures.OrgName, Measures.LevelSKey, Measures.OrgType, Measures.Customers, Measures.Lookp , Measures.Trend
}
) } ON COLUMNS,
NON EMPTY
{ [GL].&[' + CAST (@Level AS VARCHAR) + '].Children } ON Rows
FROM [cubeName]
WHERE
( [Monthly Income].[All Monthly Income].[Y],[Contact Events].[All Contact Events].[Y],
[Other Financial].[All Other Financial].[Y],[Bankers Notes].[All Bankers Notes].[Y],
[Employer Name].[All Employer Name].[Y] )
'''
END
SELECT @CubeQuery =
'
SELECT *
FROM OPENQUERY(ReportDBMart, ' + @MDX + ')'
IF @PrintMDX = 1
PRINT 'MDX: ' + @MDX
EXEC(@CubeQuery)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
The following is the value that I get back.
9.4993517502742597E-2 and is derived from Measures.Trend.
My guess is that it doesn’t recognize FORMAT_STRING = ''''Percent'''' or its trying to return a numeric value inside a varchar. Any thoughts???
View 1 Replies
View Related
Dec 19, 2007
Hi all, I got this scope:
- SSIS and SQL server are on the same machine
- myPackage run perfectly on debug mode and also via DTSexec choosing from stored package directory
My problem is when try to run the REAL job.
Always got an error.
-----
The import process has failed: Unable to complete the phase pre-esecuzione for component SQL Server Destination "(97). Error code returned: 0xC0202071.
----
This is a generic error and don't give me any feedback...one time is on data task A another to diffrent.
SSIS pkg are compose by 34 data task...they insert into SQL server destination a RAW file source.
SQL destination is the same machine of SQL server services and files are store to another machine...is a share like
\BEWSSIS2007-12-18
I have try to run job with different agent (sql agent) and proxy user (same of the log-in sql server machine) where I 'm on.
But always have error.
Now, I'm death point, 'cause PKG run ok into Debug and DTSexec...job is failure and all logs that I write ara vague.
Some data task have big numbr of row like 95000, 625000, 53000....
But I have no one buffer error...anyway I have increase the defaultNumberOfMaxRow on the proprety for "my personal security" but no way out....
Can be a problem of buffer?? A memory problem, maybe I must use anyway DBole destination??
----------------------------
this error message on windows log under history SSIS...'cause I got an handler on PKG
---------------------------
Messaggio
Executed as a user: LQS1 sql. ... Ersion 9.00.3042.00 systems to 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Start: 15.52.26 Error: 2007-12-19 15:53:26.52 Code: 0xC002F210 Origin: Important Team Activities Run SQL Description: Running query "EXEC msdb.dbo.sp_send_dbmail @ profile_name = 'ePrice Alerts', @ recipients = 'myemail @ importance =' High ', @ subject =' Import site hours 19/12/2007 15.53.03 is Failure in task: SSIS: error sku task to define, @ body = 'The import process is failed: Unable to prepare activity Added mass SSIS for data entry. ", @ body_format = 'TEXT' failed with the error:" Incorrect syntax near activity. ". Possible causes: problems in the query, setting incorrect property "ResultSet" parameters that do not correct ... Implementation of the package ... not rius Step failed.
-------------------------
SQLserver LOG from SSIS execution
----------------------
Unable to prepare activity Added mass SSIS for data entry.
Unable to prepare activity Added mass SSIS for data entry.
Unable to prepare activity Added mass SSIS for data entry.
Unable to prepare activity Added mass SSIS for data entry.
Unable to prepare activity Added mass SSIS for data entry.
Unable to prepare activity Added mass SSIS for data entry.
Enforcement query "EXEC msdb.dbo.sp_send_dbmail @ profile_name = 'ePrice Alerts', @ = recipients'myEMAIL', @ importance = 'High', @ subject = 'Import site hours 19/12/2007 15.52.50 is FALLITA in task: SSIS: error in task boxes Home products', @ body = 'import process failed: Unable to prepare activity Added mass SSIS for data entry. ", @ body_format = 'TEXT' failed with the error: "Incorrect syntax near activity.". Possible causes: problems in the query, setting incorrect property "ResultSet" parameters set correctly or not problems activation of the connection.
Enforcement query "EXEC msdb.dbo.sp_send_dbmail @ profile_name = 'ePrice Alerts', @ = recipients' myEmail', @ importance = 'High', @ subject = 'Import site hours 19/12/2007 15.52.50 is FALLITA in task: SSIS: error in task boxes Home products', @ body = 'import process failed: Unable to prepare activity Added mass SSIS for data entry. ", @ body_format = 'TEXT' failed with the error: "Incorrect syntax near activity.". Possible causes: problems in the query, setting incorrect property "ResultSet" parameters set correctly or not problems activation of the connection.
Unable to complete the phase pre-esecuzione for component SQL Server Destination "(128). Error code returned: 0xC0202071.
Unable to prepare activity Added mass SSIS for data entry.
Unable to prepare activity Added mass SSIS for data entry.
Unable to prepare activity Added mass SSIS for data entry.
Unable to prepare activity Added mass SSIS for data entry.
Unable to complete the phase pre-esecuzione for component SQL Server Destination "(128). Error code returned: 0xC0202071.
Unable to prepare activity Added mass SSIS for data entry.
Unable to complete the phase pre-esecuzione for component SQL Server Destination "(128). Error code returned: 0xC0202071.
Unable to prepare activity Added mass SSIS for data entry.
Unable to prepare activity Added mass SSIS for data entry.
Unable to prepare activity Added mass SSIS for data entry.
Unable to prepare activity Added mass SSIS for data entry.
Unable to prepare activity Added mass SSIS for data entry.
Unable to prepare activity Added mass SSIS for data entry.
Unable to prepare activity Added mass SSIS for data entry.
Unable to prepare activity Added mass SSIS for data entry.
Enforcement query "EXEC msdb.dbo.sp_send_dbmail @ profile_name = 'ePrice Alerts', @ = recipients' myEmail', @ importance = 'High', @ subject = 'Import site hours 19/12/2007 15.52.50 is FALLITA in task: SSIS: error task Payment methods tasks', @ body = 'import process failed: Unable to prepare activity Added mass SSIS for data entry. ", @ body_format = 'TEXT' failed with the error: "Incorrect syntax near activity.". Possible causes: problems in the query, setting incorrect property "ResultSet" parameters set correctly or not problems activation of the connection.
Enforcement query "EXEC msdb.dbo.sp_send_dbmail @ profile_name = 'ePrice Alerts', @ = recipients' myEMAIL', @ importance = 'High', @ subject = 'Import site hours 19/12/2007 15.52.50 is FALLITA in task: SSIS: error task Payment methods tasks', @ body = 'import process failed: Unable to prepare activity Added mass SSIS for data entry. ", @ body_format = 'TEXT' failed with the error: "Incorrect syntax near activity.". Possible causes: problems in the query, setting incorrect property "ResultSet" parameters set correctly or not problems activation of the connection.
Unable to prepare activity Added mass SSIS for data entry.
Unable to prepare activity Added mass SSIS for data entry.
Unable to complete the phase pre-esecuzione for component SQL Server Destination "(169). Error code returned: 0xC0202071.
Unable to complete the phase pre-esecuzione for component SQL Server Destination "(169). Error code returned: 0xC0202071.
Unable to complete the phase pre-esecuzione for component SQL Server Destination "(169). Error code returned: 0xC0202071.
Enforcement query "EXEC msdb.dbo.sp_send_dbmail @ profile_name = 'ePrice Alerts', @ = recipients' myEMAIL', @ importance = 'High', @ subject = 'Import site hours 19/12/2007 15.52.50 is FALLITA in task: SSIS: error task Payment methods tasks', @ body = 'import process failed: Unable to prepare activity Added mass SSIS for data entry. ", @ body_format = 'TEXT' failed with the error: "Incorrect syntax near activity.". Possible causes: problems in the query, setting incorrect property "ResultSet" parameters set correctly or not problems activation of the connection.
Enforcement query "EXEC msdb.dbo.sp_send_dbmail @ profile_name = 'ePrice Alerts', @ = recipients' myEMAIL', @ importance = 'High', @ subject = 'Import site hours 19/12/2007 15.52.50 is FALLITA in task: SSIS: error task Payment methods tasks', @ body = 'import process failed: Unable to prepare activity Added mass SSIS for data entry. ", @ body_format = 'TEXT' failed with the error: "Incorrect syntax near activity.". Possible causes: problems in the query, setting incorrect property "ResultSet" parameters set correctly or not problems activation of the connection.
Unable to complete the phase pre-esecuzione for component SQL Server Destination "(212). Error code returned: 0xC0202071.
Unable to complete the phase pre-esecuzione for component SQL Server Destination "(212). Error code returned: 0xC0202071.
Unable to complete the phase pre-esecuzione for component SQL Server Destination "(3409). Error code returned: 0xC0202071.
Unable to complete the phase pre-esecuzione for component SQL Server Destination "(3409). Error code returned: 0xC0202071.
Unable to complete the phase pre-esecuzione for component SQL Server Destination "(212). Error code returned: 0xC0202071.
Unable to prepare activity Added mass SSIS for data entry.
Unable to complete the phase pre-esecuzione for component SQL Server Destination "(3409). Error code returned: 0xC0202071.
Unable to complete the phase pre-esecuzione for component SQL Server Destination "(188). Error code returned: 0xC0202071.
Unable to complete the phase pre-esecuzione for component SQL Server Destination "(97). Error code returned: 0xC0202071.
Unable to prepare activity Added mass SSIS for data entry.
Unable to complete the phase pre-esecuzione for component SQL Server Destination "(188). Error code returned: 0xC0202071.
Unable to complete the phase pre-esecuzione for component SQL Server Destination "(150). Error code returned: 0xC0202071.
Unable to complete the phase pre-esecuzione for component SQL Server Destination "(97). Error code returned: 0xC0202071.
Unable to prepare activity Added mass SSIS for data entry.
Unable to complete the phase pre-esecuzione for component SQL Server Destination "(188). Error code returned: 0xC0202071.
Unable to complete the phase pre-esecuzione for component SQL Server Destination "(150). Error code returned: 0xC0202071.
Unable to complete the phase pre-esecuzione for component SQL Server Destination "(97). Error code returned: 0xC0202071.
Unable to complete the phase pre-esecuzione for component SQL Server Destination "(150). Error code returned: 0xC0202071.
Unable to complete the phase pre-esecuzione for component SQL Server Destination "(509). Error code returned: 0xC0202071.
Unable to complete the phase pre-esecuzione for component SQL Server Destination "(509). Error code returned: 0xC0202071.
Unable to complete the phase pre-esecuzione for component SQL Server Destination "(509). Error code returned: 0xC0202071.
Hope in someone...Alen Italy
View 16 Replies
View Related
Jul 20, 2015
I wanted to execute a proc whenever a new login or a login is droped is created on the server. I was trying for CREATE_LOGIN in the following code but not able to capture the loginname and pass it to the proc.
Server object Trigger:-
USE [master]
GO
/****** Object: DdlTrigger [LoginCreateTrigger] Script Date: 7/20/2015 3:41:06 AM ******/
SET ANSI_NULLS ON
GO
[code]...
View 3 Replies
View Related
Oct 8, 2007
I had created an Script Task to put data into the few variables then I created an SQL Execute Task to use the SQL query to collect data from a database. Now I want to put that data into a different database.
I noticed it can not be done because the SQL Execute Task is one object and the other object is Data Flow Task. (The Destination database is inside that object).
So, if it is possible then how? If not then what should be done instead?
Thanks...
View 1 Replies
View Related
Jan 24, 2007
Hello, I wanted to do the following.
Copy a full directory from source to destination (Done)
then for each file on the destination directory,it must process that file and insert rows on the table.
So I created a foreach loop, and created a varriable aclled CURRENTFILENAME, and assigned it into the foreachloop to index 0.
Inside the foreachloop I created a dataflow task, in the dataflow task I dragged a flat file source and an oledb destination, but I noticed that the flat file source requires flat file manager, and the flat file manager requires a unique FILE NAME. I cant put this c:copia*.txt.
I took a loot at flat file source properties and it has associated the flat file manager, but I can not assign the filename to the variable from the foreach.
Any ideas please
View 5 Replies
View Related
Jul 10, 2015
IS that possible to get teh output of a execute sql task to excel destination.I have query which will comapre the data difference between two databses. It will comapre all tables in both databses and list out the difference in data by each table. I need to run this query using SSIS and need to get the output to a excel sheet...I have used the data flow task to run this query but my query is giving some error when used with data flow task. So i have used excecute sql task and need to write teh out put to a excel sheet.
View 11 Replies
View Related
Jan 12, 2008
Hello,
I am trying to use an Execute SQL Task to execute a stored procedure with parameters in SSIS but it keeps giving me errors, for example I type in 'exec someproc ?, ?, ?' in SQL Task it wouldnt parse correctly and when I set the bypassPrepare to 'true' the package runs but gives me error at the destination saying couldnt find stored procedure 'someProc'.
Does anybody know how I can solve this problem?
Also how do I pass the results of the queries in the Execute SQL Task into the Data Flow Task? The proc is supposed to retrieve data and transform them into XML format to be placed at a XML file. I am not sure if I should used an OLEDB source or an XML source since the data has been transformed into XML. Any help will be greatly appreciated.
Please note proc runs fine when it does not have any parameters.
View 8 Replies
View Related
Jul 23, 2005
How execute extended proc on stopping ms sql server (it mean beforestoping).Is it possible to handle this?Or is in system proc a'la sp_on_stop?Thx
View 2 Replies
View Related
Nov 25, 2015
I am using SSIS integration between two database. Both databases are sql server 2008. Â using many integration but getting problem in two only only two integration giving problem, both are executing perfectly and out put also not showing any error.
but destination table not inserted/updated anything.
first issue integration is using data flow task with oledb source and destination.Â
second one is using execute task with for-eachloop container.
View 12 Replies
View Related
Feb 20, 2003
I have seen this done by viewing code done by a SQL expert and would like to learn this myself. Does anyone have any examples that might help.
I guess I should state my question to the forum !
Is there a way to call a stored proc from within another stored proc?
Thanks In Advance.
Tony
View 1 Replies
View Related
Jan 13, 2006
Hi all,
I have a stored procedure "uspX" that calls another stored procedure "uspY" and I need to retrieve the return value from uspY and use it within uspX. Does anyone know the syntax for this?
Thanks for your help!
Cat
View 5 Replies
View Related
Jan 20, 2004
Hi all
I have about 5 stored procedures that, among other things, execute exactly the same SELECT statement
Instead of copying the SELECT statement 5 times, I'd like each stored proc to call a single stored proc that executes the SELECT statement and returns the resultset to the calling stored proc
The SELECT statement in question retrieves a single row from a table containing 10 columns.
Is there a way for a stored proc to call another stored proc and gain access to the resultset of the called stored proc?
I know about stored proc return values and about output parameters, but I think I am looking for something different.
Thanks
View 14 Replies
View Related
Aug 30, 2007
I would like to know if the following is possible/permissible:
myCLRstoredproc (or some C# stored proc)
{
//call some T SQL stored procedure spSQL and get the result set here to work with
INSERT INTO #tmpCLR EXECUTE spSQL
}
spSQL
(
INSERT INTO #tmpABC EXECUTE spSQL2
)
spSQL2
(
// some other t-sql stored proc
)
Can we do that? I know that doing this in SQL server would throw (nested EXECUTE not allowed). I dont want to go re-writing the spSQL in C# again, I just want to get whatever spSQL returns and then work with the result set to do row-level computations, thereby avoiding to use cursors in spSQL.
View 2 Replies
View Related
Apr 21, 2006
Hi Everybody,
I have a question regarding the running of a procedure from VC++ in SQL server.
well this is the scenario.
i have a procedure in SQL server and i am running that thru my VC++ code.
:eek:
i have a recordset class and i create an object of it and then do a obj.Open(). the procedure is fetching 24 records correctly in the query analyser and in my while(!(obj.IsEOF())) loop it gets the first record correctly but the moment it encounters obj.MoveNext(); it bombs and gives me the following error..
"Invalid Character value for cast specification ".
i don't know why this is happening
Thanks for you help.
View 2 Replies
View Related
May 18, 2008
SSIS Newbie Question:
I have a simple Control Flow setup that checks to see if a particular table exists. If the table does not exists, the table is created in an alternate path, if it does exist, the table is truncated before moving to a file import Data Flow that uses an OLE DB Destination to output the imported data.
My problem is, that I get OLE DB package errors if the table the OLE DB Destination Container references does not exist when I load the package.
How can I over come this issue? I need to be able to dynamically create the table in an earlier step, then use that table to import data into in a later step in the workflow.
Is there a switch I can use to turn off checking in the OLE DB Destination Container so that it will allow me to hook up the table creation step?
Seems like this would be a common task...
Steps:
1. Execute SQL Task to see if the required table exists
2. Use expresions to test a variable to check the results of step 1
3. If table exists, truncate the table and reload it from file in Data Flow using OLE DB Destination
4. If table does not exist, 1st create it, then follow the normal Data Flow
Can someone help me with this?
Signed: Clueless with a deadline approaching...
View 3 Replies
View Related
Apr 23, 2008
Hello friends......How are you ? I want to ask you all that how can I do the following ?
I want to now that how many ways are there to do this ?
How can I call one or more stored procedures into perticular one Stored Proc ? in MS SQL Server 2000/05.
View 1 Replies
View Related
Dec 18, 2007
Hi Peeps
I have a SP that returns xml
I have writen another stored proc in which I want to do something like this:Select FieldOne, FieldTwo, ( exec sp_that_returns_xml ( @a, @b) ), FieldThree from TableName
But it seems that I cant call the proc from within a select.
I have also tried
declare @v xml
set @v = exec sp_that_returns_xml ( @a, @b)
But this again doesn't work
I have tried changing the statements syntax i.e. brackets and no brackets etc...,
The only way Ive got it to work is to create a temp table, insert the result from the xml proc into it and then set @v as a select from the temp table -
Which to be frank is god awful way to do it.
Any and all help appreciated.
Kal
View 3 Replies
View Related
