.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Easy Web
Imran Ghani
Post New Web Links

problems running stored procs on AS400 iSeries DB2

Posted By:      Posted Date: September 08, 2010    Points: 0   Category :Sql Server
 
Hi, Im working on SSIS packages to populate a SQLServer Data warehouse from an I Series AS400 DB2. Downoading data is no problem, using the ISeries IBMDA400 provider. But I need to upload a couple od tables to the iSeries and then run a stored procedure to copy them into the prodution tables. To do this I had to switch to the iSeries provider IBMDASQL. this runs fine when running in BIDS debug mode but when I save the package on the server and run it from a job, it dails with an invalid password on the connection. I have tried this with, Protection level Server Storage, encrypt with password, and dont save sensitive, using a configuration table, in all cases it works fine running in BIDS, on the dev server, but runing as a SQLSERVE job on the same server it fails. when I saved as Server storage i got the following from the job but it works from BIDS Message Executed as user: Domain\XXXXXXXX. ...lity  Version 9.00.3042.00 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  7:38:53 AM  Error: 2010-09-08 07:38:55.30     Code: 0xC0202009     Source: PJCMTest001 Connection manager "GroupQAC.RunProc"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.&


View Complete Post


More Related Resource Links

Test Run: Automate Testing of Your Stored Procs

  

Many Windows®-based applications have a SQL ServerT back-end component that contains stored procedures. Although techniques to automatically test functions in the front-end code are well known, the techniques to write test automation for stored procedures are not.

James McCaffrey

MSDN Magazine September 2004


Problems when Running

  

Every time I try to run I get the following Error.  Error 1 Object reference not set to an instance of an object.  

I can then run again and it works.  Any Ideals?

 

Mike 


Problems connecting stored procedure to Crystal Reports

  

I am working on updating a reporting system that uses Crystal Reports.  All of the 250+ reports were created in CR 8 and I have been opening up the old files in Visual Studio 2005 and updating the database location.  All I have had to do with all connections to views and even a couple of stored procedures is create a new connection to the database and then update the old report's datasource location.  The reason I need to update the location is so that the .NET application will use the correct driver.  Everything has worked fine up until I got to one stored procedure.  The old version of the report works perfectly with the stored procedure, but when I try to run the report using VS 2005 I keep getting errors.

First I connected to the database using the Oracle OLE DB provider, then updated the location of the stored procedure in the report.  It then prompts me for the two parameter values for the stored procedure, both of which I keep as NULL so that the report parameters will be passed to the procedure.  Then I get the following error:

Query Engine Error: 'ADO Error Code: 0x
Source: OraOLEDB
Description: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'STORED_PROCEDURE'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored'

The proce

Extended stored procs on 64 bit MSSQL 2008

  
We need to develop some extended stored procedures for 64-bit SQL Server 2008. We have written the DLL in Visual C++ but when we try to register it with the database, we see the following error: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not load the DLL c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\xp_test.dll, or one of the DLLs it references. Reason: 193(failed to retrieve text for this error. Reason: 15105).(42000,17750) Procedure(xp_test) Can anyone please tell me if you have come across such an issue and how it was successfully resolved? 

Problems running a bat file within a SSIS package

  
I have a ssis package which has 2 tasks, first it builds an excel sheet with data on the server from where I am running the ssis pkg and the second task (a sql process task), runs a bat. the bat file that basically copies the  excel sheet created from the first step to another server. Problem is that the ssis pkg runs succesful when i run it from BIDS, bust when I run is as ajon, it gets hung. It completes the first step, it is the sescond step that is get hung. I noticed that when i run from BIDs, it does come up with a window open file - security warning and says the publisher could not be verified, are you sure you want to run this software. why does it do that ??? the file I am asking to run is a .bat file. it should automatically use the cmd.exe app to run the bat file. Please help !!!   Thanks in advance.

Problems while creating the Stored Procedures with Concatination

  
Hi I am creating one Stored Procedure where want to use concatination in that. For that I had written the query but I am unable to do it, so I need help for this. My SQL query is: SET QUOTED_IDENTIFIER ON CREATE PROCEDURE Team @Int VARCHAR(5), @str VARCHAR(150), AS SELECT * FROM Emp WHERE ID = @Int + '@str' Go In @str is we are passing the value at run time (like @str =AND NAME = 'Test') Actuall quey is: SELECT * FROM Emp WHERE ID = 1 AND NAME = 'Test' Thanks in advance Amit

Calling AS400 Stored Procedure from ASP.net C# Coding

  
Hi, I have a req in which I have to call an AS400 stored procedure.The stored has an input and output parameter. Can any body help me.I am new to this technology. Please help me its urgent Thanks, A2H  

Enterprise t-sql Stored Procs against tables -vs- Views

  
I work for a very large enterprise and I am wanting to know what is the best way to design t-sql for the stored proces to be used from an application? Option 1 - Be sure to optimize your Database design so your application will only require at most 6 joins and used stored procs for queries using proper indexs to gain the most performance Option 2 - Create Views and use stored procs against the views My questions:  1. What is the perfromance hit in using views.   My thought process is that views should only be used when you know the data you want to return "hence the name 'VIEW', should not be used as a query".   2. Are stored procs against the schema tables the best for performance. 3. What is the best way to tune and ensure your stored proc is optimal with the correct indexes, so you build a lighting fast query? http://www.mmwebs.com

Stored Procs tutorial/learning resources?

  
I've always used some kind of ORM to fetch data,  LinqToSQL or NHibernate so i guess im spoiled - but now i have a prospective job where they primarily use stored procs.  Not my first choice as i'm not all too comfortable with DBs in general - but i like to brush up on stored procs - almost a Stored Procs for Dummies kind of thingWhats a good resource you guys use for this? I just wanna show that i some initiative and more or less hit the ground running when it comes to stored procs - assuming im selected.Thanks!

have sql 64 bit with sql express and having issues with oledb running from stored proc in sql expres

  
Hello, I have added sqlexpress with my sql 64 bit in windows xp so i could use oledb to access excel files.  I can not use ace as the excel is 2003 and not all 64 bit.  It works perfectly until i try and run exec stored proc (has my oledb procedure) to insert the results into a table variable. declare   @MyTable table ( FirstName varchar(50), LastName varchar(50) ) INSERT   INTO @MyTable exec   [MyDevServer\sqlexpress].MySQLexpressDatabase.dbo.exceltest   the excel test procedure works fine by itself but as soon as i use insert it crashes.  any ideas??    

Report using Stored Proc calling multiple Stored Procs

  

Hi guys,

I am new to SSRS. I have to create a report from a Stored Proc. Thi Stored Proc excepts just one parameter 'ErecNo'. This parameter is passed to fetch the columns from table1. This result set contains OrderID. The OrderId is further passes to 5 SP's in to fetch 5 more result sets. Output of the main stored proc contains 6 result sets.

Now I have to create a report using this SP to show all the Result sets in a Single report. But When I pass the Parameter in SP in SSRS. It ends up showing just one result set with Order ID. 

Can you please guide me how can I show all the result set in a Single report? 

 

Thanks 


Problems using sp_send_dbmail stored procedure

  

I want to send an email if my stored procedure fails on any one of the 6 table inserts. The email is not working.

After each insert, I include a check for errors. If errors are found, the program breaks out of the cursor loop and does a GOTO to a Fail block.

This is the code snippet:

 Fail:
      BEGIN --begin Fail
  --#1 - send email to itsystems
  IF ( @err <> 0 ) -- Error found in one of the inserts
   BEGIN
    --SET @recipients1 =
'mail@email.com;
    SET @recipients1 =
AnotherMail@email.com;
    SET @subject1 = 'COOP DATABASE STORED PROCEDURE FAILURE -- Project Number' -- "' + RTRIM(LTRIM(@ProjectName)) + '"' ;
    SET @body1 = 'COOP stored procedure uspCorporateAdSubmitClaims failed.'
    --SET @body1 = 'COOP stored procedure uspCorporateAdSubmitClaims failed for project number '

+ LTRIM(RTRIM(@ProjectNumber)) + '.'  
         + CHAR(13) + CHAR(10) + 'No claims were created for project numb

Can a stored procedure programmatically determine if an SSIS package is running?

  

Greetings,

If I have the 'ExecutionInstanceGUID' value for an SSIS package that I know was started, is there a way that a stored procedure can programmatically determine whether the SSIS package still is running?

Thanks -

BCB

 


Running SSIS package from Stored Procedure using dtexec and Pass boolean value

  

Hi,

We are using SSIS 2005 and sql server 2005. My package has a boolean type package level variable. Eg: IsClientNull boolean type.

I call this package from stored procedure. In addition to passing values for other parameters, how can I pass boolean value.

My code is like this:

SET @CMD='dtexec ' +
'/FILE ' + @pShareName + @pPackageName + ' ' +
'/MAXCONCURRENT " -1 " ' +
'/CHECKPOINTING OFF ' +
'/REPORTING EW ' +
'/SET "\Package.Variables[User::StartDate].Properties[Value]";' +
CONVERT(char(10), @pStartDate, 120) + ' ' +
'/SET "\Package.Variables[User::EndDate].Properties[Value]";' +
CONVERT(char(10), @pEndDate, 120)
IF (@pClient IS NULL)
BEGIN

 SET @CMD = @CMD + ' ' +
 '/SET "\Package.Variables[User::IsClientNull].Properties[Value]";' +
 'True%


Stored Proc Problems...

  

Hi there,

Having big problems with a stored procedure I'm trying to write.  It seems really simple but when I try to execute the procedure it throws an error.

Here is the proc:

USE [master]
GO
ALTER PROCEDURE [InsertTerm]
@TableName nvarchar(300),
@SearchTerm nvarchar(300)
AS
DECLARE @sql nvarchar(4000)
SELECT @sql = ' INSERT INTO [dbo].[tempdb].[@TableName]
SELECT [Col1] FROM [Table1] WHERE [Col1] = ' + @SearchTerm + ' OR [Col2] LIKE ' + '%' + @SearchTerm + '%' + ' OR [Col3] LIKE  ' + '%' + @SearchTerm + '%'
EXEC sp_executesql @proc
GO
 
I've been playing with this now for best part of an hour and the errors seem to be coming from the @SearchTerm parameters.
 
Please Help...!
Many Thanks

Unexpected Error when running large stored procedure report in SharePoint

  

I have a report which uses a stored procedure to retreive a resource intensive dataset from a Data Warehouse.  With a small scope it renders in SharePoint no problem, but when a larger scope is specified ShrePoint throws this non-descript error. 

I can execute the procedure in SQL Server Management Studio, and though it takes 2+ minutes with the larger scope (30 sec with smaller) it does return the data.  Similarly, in Business Intelligence Development Studio it returns the rendered report with data after 2-3 minutes.

Only in SharePoint and only with a larger dataset does this occur.  Timeout is set at 30 minutes and it occurs long before then.  SSRS server has over 12 gigs memory, running SQL Server 2008, SharePoint 2007 is in integrated mode. No errors in SharePoint server or SSRS server logs.  Ideas?


Unexpected Error when running large stored procedure report in SharePoint

  

I have a report which uses a stored procedure to retreive a resource intensive dataset from a Data Warehouse.  With a small report it renders in SharePoint no problem, but when a larger scope is specified ShrePoint throws this non-descript error. 

I can execute the procedure in SQL Server Management Studio, and though it takes 2+ minutes with the larger scope (30 sec with smaller) it does return the data.  Similarly, in Business Intelligence Development Studio it returns the rendered report with data after 2-3 minutes.

Only in SharePoint and only with a larger dataset does this occur.  Timeout is set at 30 minutes and it occurs long before then.  SSRS server has over 12 gigs memory, running SQL Server 2008, SharePoint 2007 is in integrated mode. No errors in SharePoint server or SSRS server logs.  Ideas?



-Erik
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend