Using Adodb Command - Output Parameter Problem- HELP!

Jul 20, 2005

We use a number of similar databases and frequently create a new
database using a backup restore of another similar database. We try to
keep changes between databases in _Additional tables - like Account
Additional, Sale_Additional so most tables stay the same. The latest
restored database (I'll call it DBaseA) is behaving differently in VB6
code and I need help trying to make it work.

I have been using use an ADODB.Command to execute a stored procedure
using adCmdStoredProc and Parameters.Refresh to get an output parameter
value which is an integer. Here is a sample of the VB:

Public Function UnlockAccount(Account_ID As String) As Boolean
Dim LCmd As ADODB.Command
On Error GoTo ERROR_UnlockAccount
UnlockAccount = True
If Account_ID <> "" Then
If DBConnect() Then
Set LCmd = New ADODB.Command
LCmd.ActiveConnection = CN(0)
LCmd.CommandTimeout = 0
LCmd.CommandType = adCmdStoredProc
LCmd.CommandText = "Unlock_Account"
LCmd.Parameters.Refresh
LCmd.Parameters("@Account_ID").VALUE = Account_ID

LCmd.Execute , , adExecuteNoRecords

If LCmd.Parameters("@Status") <> 0 Then
UnlockAccount = False
End If
Else
msgbox "UnlockAccount: DBConnect Failed", "UnlockAccount"
End If
End If
Exit Function
ERROR_UnlockAccount:
UnlockAccount = False
msgbox "UnlockAccount: App Error: " & Err & " " & _
Err.Description, "UnlockAccount"
DBConnect True
Exit Function
Resume'for debugging
End Function

and this is one of the sps that fails - can't be more simple!
Create PROCEDURE Unlock_Account
( @Account_ID UNIQUEIDENTIFIER,
@Status INTEGER =null OUTPUT) AS
/* Strip functionality */
SET @Status = 0
go

This code is still working in at least 4 other databases. It fails in
database DBaseA with the error:
Invalid character for cast conversion
We are talking about the exact same table definition and the exact same
stored procedure just in different databases.

I fumbled around on the Internet and found a mention in a PowerBuilder
web site of additional parameters in the connect string which seems to
fix the problem in SOME of the stored procs - but not all.
The added parameters are:
DelimitIdentifier='No';MsgTerse='Yes';
CallEscape='No';FormatArgsAsExp='N'
They are not documented in MS but are in Sybase?? No idea why, but some
of the stored proc functions work when I add this to the connect string.

The complete connect string is:
Provider=SQLOLEDB.1;Integrated Security=SSPI;
Persist Security Info=False;
Initial Catalog=DBaseA;
Data Source=PHASE2-S500,1433;
Network Library=DBMSSOCN;
DelimitIdentifier='No';MsgTerse='Yes';
CallEscape='No';FormatArgsAsExp='N'

The databases are on different servers - but they are running the same
version of SQL2000, the same version of Windows. I see no differences
in options set in the different servers or between databases that work
and this one. And dbcc checkdb runs clean.

The compiled code that fails, fails across the board - not just on my
(developer) PC. The same source code works on the same tables in other
databases, including the one we copied....

The final kicker - if I build a SQL statement string and get the
recordset instead, it works like a charm. But I have to fix about 20 VB
functions and check that I do have a recordset and SET NOCOUNT ON in all
20 sps.

I feel this is either something so obvious I will kick myself or so
serious Microsoft will be digging into it.

Any ideas anyone??

Sandie

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

View 1 Replies


ADVERTISEMENT

Getting OUTPUT Parameter In An OLE DB Command

Jul 12, 2007

Is it possible to use stored procedure with output parameter and retrieves the values of that output parameter, in an OLE DB Command within a Data Flow?

What I wanted to do is to get the newly created identity of a row so that I can insert it to the main data set in data flow. I'm not even sure if there is even a much better design to achieve this. I've rummaged the internet but everything I got were all about Execute SQL Task.

View 5 Replies View Related

ADODB.Command Question

Sep 2, 2006

How to using Adodb.command to get Access file data??
i using this script:

dim connection as new adodb.connection
dim command as new adodb.command
dim recordset as new adodb.recordset

connection.open("connectionstring")
command.activeconnection=connection
command.commandtext="querystring"
recordset=command.execute

but,the Recordset is empty.how to using to get data on Microsoft Access database??



thank!!!!

View 1 Replies View Related

ADODB.Command Error '800a0cb3'

Feb 19, 2004

I have posted various questions on the microsoft ng trying to identify the cause of this error.

ADODB.Command error '800a0cb3'
Object or provider is not capable of performing requested operation.

I have MDAC 2.8 installed locally on my machine.

Via IIS I created a virtual directory via IIS that points to my ASP files on c:

Via the SQL Server IIS for XML configuration utility I created a virtual directory with a different names that points to the same directory as that created via IIS.

The SQL database is on a different machine and I connect via the OLEDB DSNless connection string.

I used a ADODB.Stream to transform the XML against the XSL but I couldnt get it to work. To simplify things and work towards a solution I inserted the code into my ASP from the MS KB article Q272266 (see below). I amended the ms code to change the connection code and call a stored procedure that exists on the database. The ms code gives the same error as my original code.

I tried changing the CursorLocation to server and client but the results were the same.

I put a SQL trace on the DB to determine if the stored procedure gets ran, it does not.

If I run the following in the URL it works:

If I run http://localhost/p2/?sql=SELECT+*+FROM+tblStatus+FOR+XML+AUTO&root=root&xsl=tblStatusDesc.xsl it works.
If I run the xml template it works: http://localhost/p2/xml/test.xml

The two lines above run. My IIS server uses a virtual directory called dev, so when I run the ASP I type http://localhost/DEV/secure/aframes.asp the IIS virtual directory creted by sql server is called p2 but has the same source code directory.

Here is the MS code amended as described above that does not work.

sQuery = "<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'><sql:query>Select StatusDesc from tblStatus for XML Auto</sql:query></ROOT>"
'*************************************************

Dim txtResults ' String for results
dim CmdStream ' as ADODB.Stream


sConn = "Provider=SQLOLEDB;Data Source=[name of sql server];UId=sa; Pwd=xxxxx; Initial Catalog=[DB Name]"

Set adoConn = CreateObject("ADODB.Connection")
Set adoStreamQuery = CreateObject("ADODB.Stream")

adoConn.ConnectionString = sConn
adoConn.Open

Set adoCmd = CreateObject("ADODB.Command")
set adoCmd.ActiveConnection = adoConn

adoConn.CursorLocation = adUseClient

Set adoCmd.ActiveConnection = adoConn

adoStreamQuery.Open ' Open the command stream so it may be written to
adoStreamQuery.WriteText sQuery, adWriteChar ' Set the input command stream's text with the query string
adoStreamQuery.Position = 0 ' Reset the position in the stream, otherwise it will be at EOS

Set adoCmd.CommandStream = adoStreamQuery ' Set the command object's command to the input stream set above
adoCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}" ' Set the dialect for the command stream to be a SQL query.
Set outStrm = CreateObject("ADODB.Stream") ' Create the output stream
outStrm.Open
adoCmd.Properties("Output Stream") = response ' Set command's output stream to the output stream just opened
adoCmd.Execute , , adExecuteStream ' Execute the command, thus filling up the output stream.

Response.End

View 8 Replies View Related

ADODB.Command Error '800a0d5d'

Jun 20, 2008

Hello all,
Need help with this error message:

ADODB.Command error '800a0d5d'

Application uses a value of the wrong type for the current operation.

/forum/pmsend.asp, line 146


Any input appreciated
Thanks

View 2 Replies View Related

ADODB Command (Stored Procedure)

Jun 4, 2007

Hi!I already sent this to the ACCESS newsgroup. But since I do not know reallywhich side is really causing the problem, I have decided to send thisinquiryto this newsgroup also, if I may.Below is the environment of the application:a. MS Access 2003 application running on Windows XPb. SQL Server 2000 - backend running MS Server 2003 OSBelow is the code that is giving me an error:Dim com As ADODB.CommandSet com = New ADODB.CommandWith com.ActiveConnection = "DSN=YES2;DATABASE=YES100SQLC;".CommandText = "sp_Recalculate".CommandType = adCmdStoredProc.Parameters.Refresh.Parameters("@ItemNumber") = ItemNum.Execute ' This is where it hangs up...TotalItems = .Parameters("@TotalInStock")TotalCost = .Parameters("@TotalCost")End WithSet com = Nothingand the store procedure is:CREATE PROCEDURE DBO.sp_Recalculate@ItemNumber nvarchar(50),@TotalInStock int = 0,@TotalCost money = 0ASBEGINSET @TotalInStock = (SELECT Sum([Quantity in Stock])FROM [Inventory Products]WHERE [Item Number] = @ItemNumber)SET @TotalCost = (SELECT Sum([Cost] * [Quantity in Stock])FROM [Inventory Products]WHERE [Item Number] = @ItemNumber)ENDWhen the process goes to the ".Execute" line, it hangs up for a long timethen gives me an error message "Everflow". I have been trying to solvethis issue but do not have an idea for now of the cause.Below is my finding:a. When I run the stored procedure in the SQL analyzer, it works just fine.I placed a SELECT statement to view the result of the stored procedure.It gives the correct values.Can anyone have ideas or similar problems?Thanks.

View 8 Replies View Related

Behavior Of ADODB.Command .Execute Changes On Different Servers???

Nov 28, 2007

Hello.

We have an ASP 3.0 application that currently works "correctly" on one server, Server A, and we€™re testing it on another server, Server B, which is 64 bit.

The connection string for Server A is:

DRIVER={SQL Server};SERVER=...;DATABASE=...;UID=...;PWD=...

The connection string for Server B is:

PROVIDER=sqloledb;SERVER=...;DATABASE=...;UID=...;PWD=...

(Note: Both servers point to the same database on the same server)


The unexpected behavior occurs after calling .Execute on a command. Here is some sample code:

Dim DBConn
Set DBConn = CreateObject("ADODB.Connection")
DBConn.Open strDBConnection '(the ones shown above)

Dim objCmd
Set objCmd = Server.CreateObject("ADODB.Command")
objCmd.ActiveConnection = DBConn

objCmd.CommandType = adCmdStoredProc
objCmd.CommandText = "sp_TestProcedure"

objCmd.Parameters.Append objCmd.CreateParameter("@Name", adVarChar, adParamInput, 50, "Test")

Dim rs
Set rs = objCmd.Execute


For this example, the stored procedure sp_TestProcedure is:

CREATE PROCEDURE sp_TestProcedure
@Name varchar(50)
AS
INSERT INTO tblTest ([Name], Date) VALUES (@Name, getDate())
SELECT COUNT(*) AS 'Count' FROM tblTest

The basic point is the stored procedure does an INSERT and then a SELECT.


Now... to the issue. On Server A, the variable rs above ends up with a single open Recordset which is the results of the SELECT statement.
However, on Server B, rs is set to a closed recordset, and rs.NextRecordset() gets a second recordset of the results of the SELECT statement.

I understand what's going on. Server B is first returning the number of rows affected by the INSERT which translates to a closed recordset. But Server A does not do this.

I would like to know why the default behavior of the command's .Execute is different on the different servers. Does it relate to the Provider/Driver settings in the connection string? Does it have anything to do with 64 bit VS. 32 bit servers?

I know that one way to address this issue to add SET NOCOUNT ON to the start of the stored procedure. But we have many stored procedures, and if the solution is a change in the connection string, that would be preferred. Also, whatever the possible solution is, I also looking to discover *why* it's happening.

Any help would be greatly appreciated.

View 2 Replies View Related

Using Output From A Stored Procedure As An Output Column In The OLE DB Command Transformation

Dec 8, 2006

I am working on an OLAP modeled database.

I have a Lookup Transformation that matches the natural key of a dimension member and returns the dimension key for that member (surrogate key pipeline stuff).

I am using an OLE DB Command as the Error flow of the Lookup Transformation to insert an "Inferred Member" (new row) into a dimension table if the Lookup fails.

The OLE DB Command calls a stored procedure (dbo.InsertNewDimensionMember) that inserts the new member and returns the key of the new member (using scope_identity) as an output.

What is the syntax in the SQL Command line of the OLE DB Command Transformation to set the output of the stored procedure as an Output Column?

I know that I can 1) add a second Lookup with "Enable memory restriction" on (no caching) in the Success data flow after the OLE DB Command, 2) find the newly inserted member, and 3) Union both Lookup results together, but this is a large dimension table (several million rows) and searching for the newly inserted dimension member seems excessive, especially since I have the ID I want returned as output from the stored procedure that inserted it.

Thanks in advance for any assistance you can provide.

View 9 Replies View Related

Script Component Has Encountered An Exception In User Code - Object Is Not An ADODB.RecordSet Or An ADODB.Record

Nov 26, 2007

hi have written SSIS script and i am using script component to Row count below my code what i have written. and i am getting error below i have mention...after code see the error
using System;

using System.Data;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

using Microsoft.SqlServer.Dts.Runtime.Wrapper;

using System.Data.SqlClient;

using System.Data.OleDb;



[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]

public class ScriptMain : UserComponent

{

IDTSConnectionManager100 connMgr;

OleDbConnection sqlConn = null;

OleDbDataReader sqlReader;



public override void AcquireConnections(object Transaction)

{

connMgr = this.Connections.MyConnection;

sqlConn = (OleDbConnection )connMgr.AcquireConnection(null);

//sqlConn = (SqlConnection)connMgr.AcquireConnection(null);

}

public override void PreExecute()

{

base.PreExecute();

/*

Add your code here for preprocessing or remove if not needed

*/

OleDbCommand cmd = new OleDbCommand("SELECT CustomerID,TerritoryID,AccountNumber,CustomerType FROM Sales.Customer", sqlConn);



sqlReader = cmd.ExecuteReader();

}

public override void PostExecute()

{

base.PostExecute();

/*

Add your code here for postprocessing or remove if not needed

You can set read/write variables here, for example:

Variables.MyIntVar = 100

*/

}

public override void CreateNewOutputRows()

{

/*

Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".

For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".

*/

System.Data.OleDb.OleDbDataAdapter oLead = new System.Data.OleDb.OleDbDataAdapter();

//SqlDataAdapter oLead = new SqlDataAdapter();

DataSet ds = new DataSet();



System.Data.DataTable dt = new System.Data.DataTable();

//DataRow row = new DataRow();

oLead.Fill(dt,this.Variables.ObjVariable);





foreach (DataRow row in dt.Rows)

{

{

Output0Buffer.AddRow();

Output0Buffer.CustomerID = (int)row["CustomerID"];

Output0Buffer.TerritoryID =(int)row["TerritoryID"];

Output0Buffer.AccountNumber = row["AccountNumber"].ToString();

Output0Buffer.CustomerType = row["CustomerType"].ToString();

}

}



}

}
the error
Script component has encountered an exception in user code
Object is not an ADODB.RecordSet or an ADODB.Record.
Parameter name: adodb
at System.Data.OleDb.OleDbDataAdapter.FillFromADODB(Object data, Object adodb, String
srcTable, Boolean multipleResults)
at System.Data.OleDb.OleDbDataAdapter.Fill(DataTable dataTable, Object ADODBRecordSet)
at ScriptMain.CreateNewOutputRows()
at UserComponent.PrimeOutput(Int32 Outputs, Int32[] OutputIDs, PipelineBuffer[] Buffers)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PrimeOutput(Int32 outputs,
Int32[] outputIDs, PipelineBuffer[] buffers)

thanks
kedarnath

View 4 Replies View Related

Unable To Cast COM Object Of Type 'ADODB.CommandClass' To Interface Type 'ADODB._Command'

Dec 20, 2006

I have an application which runs successfully on a couple of my customer's machines but fails on a third. It seems to fail when opening the database:

Unable to cast COM object of type 'ADODB.CommandClass' to interface type 'ADODB._Command'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{B08400BD-F9D1-4D02-B856-71D5DBA123E9}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=false; Initial Catalog=lensdb;Data Source = SQL

Before I got this error I was getting another problem (sorry didn't make a copy of that error's text) that made me think that adodb.dll simply wasn't loaded/registered. I got rid of that error by copying my adodb.dll onto the third machine and running gacutil /i. There is now an entry in winntassemblies for adodb.

Just in case you think it could be an obvious registry problem: when I started getting the current error I thought that maybe the registry needed updating and I merged the following lines into onto the target machine (from my dev machine):

Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOTInterface{B08400BD-F9D1-4D02-B856-71D5DBA123E9}]
@="_Command"

[HKEY_CLASSES_ROOTInterface{B08400BD-F9D1-4D02-B856-71D5DBA123E9}ProxyStubClsid]
@="{00020424-0000-0000-C000-000000000046}"

[HKEY_CLASSES_ROOTInterface{B08400BD-F9D1-4D02-B856-71D5DBA123E9}ProxyStubClsid32]
@="{00020424-0000-0000-C000-000000000046}"

[HKEY_CLASSES_ROOTInterface{B08400BD-F9D1-4D02-B856-71D5DBA123E9}TypeLib]
@="{EF53050B-882E-4776-B643-EDA472E8E3F2}"
"Version"="2.7"

[HKEY_LOCAL_MACHINESOFTWAREClassesInterface{B08400BD-F9D1-4D02-B856-71D5DBA123E9}]
@="_Command"

[HKEY_LOCAL_MACHINESOFTWAREClassesInterface{B08400BD-F9D1-4D02-B856-71D5DBA123E9}ProxyStubClsid]
@="{00020424-0000-0000-C000-000000000046}"

[HKEY_LOCAL_MACHINESOFTWAREClassesInterface{B08400BD-F9D1-4D02-B856-71D5DBA123E9}ProxyStubClsid32]
@="{00020424-0000-0000-C000-000000000046}"

[HKEY_LOCAL_MACHINESOFTWAREClassesInterface{B08400BD-F9D1-4D02-B856-71D5DBA123E9}TypeLib]
@="{EF53050B-882E-4776-B643-EDA472E8E3F2}"
"Version"="2.7"


but, no change alas.

All three machines are running Windows 2000.

Any advice would be appreciated.

Thanks in advance,

Ross

View 1 Replies View Related

The Formal Parameter @ReportingId Was Not Declared As An OUTPUT Parameter, But...what Is This?

Apr 17, 2008

After running my ssis pkg for some time with no problems I saw the following error come up, probably during execution of a stored procedure ...

An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The formal parameter "@ReportingId" was not declared as an OUTPUT parameter, but the actual parameter passed in requested output.".

I see some references to this on the web (even this one) but so far they seem like deadends. Doe anybody know what this means? Below is a summary of how the variable is used in the stored proc.

The sp is declared with 21 input params only, none of them is @ReportingId. It declares @ReportingId as a bigint variable and uses it in the beginning of the proc as follows...




Code Snippet
select @ReportingId = max(ReportingId)
from [dbo].[GuidToReportingid]
where Guid = @UniqueIdentifier and
EffectiveEndDate is null

if @ReportingId is null
BEGIN
insert into [dbo].[GuidToReportingId]
select @UniqueIdentifier,getdate(),null,getdate()
set @ReportingId = scope_identity()
END
ELSE
BEGIN
select @rowcount = count(*) from [dbo].[someTable]
where ReportingId = @ReportingId and...lots of other conditions






...later as part of an else it does the following...




Code Snippet
if @rowcount > 0 and @joinsMatch = 1
begin
set @insertFlag = 0
end
else
begin
update [dbo].[GuidToReportingId]
set EffectiveEndDate = getdate()
where ReportingId = @ReportingId
insert into [dbo].[GuidToReportingId]
select @UniqueIdentifier,getdate(),null,getdate()
set @ReportingId = scope_identity()
end






...and before the return it's value is inserted to different tables.

View 5 Replies View Related

My Output Parameter Is Being Treated As An Input Parameter...why

Sep 25, 2006

I have a stored procedure which takes an input parm and is supposed to return an output parameter named NewRetVal.  I have tested the proc from Query Analyzer and it works fine, however when I run the ASP code and do a quickwatch I see that the parm is being switched to an input parm instead of the output parm I have it defined as...any ideas why this is happening?  The update portion works fine, it is the Delete proc that I am having the problems... ASP Code...<asp:SqlDataSource ID="SqlDS_Form" runat="server" ConnectionString="<%$ ConnectionStrings:PTNConnectionString %>" SelectCommand="PTN_sp_getFormDD" SelectCommandType="StoredProcedure" OldValuesParameterFormatString="original_{0}" UpdateCommand="PTN_sp_Form_Update" UpdateCommandType="StoredProcedure" OnUpdated="SqlDS_Form_Updated" OnUpdating="SqlDS_Form_Updating" DeleteCommand="PTN_sp_Form_Del" DeleteCommandType="StoredProcedure" OnDeleting="SqlDS_Form_Updating" OnDeleted="SqlDS_Form_Deleted"><UpdateParameters><asp:ControlParameter ControlID="GridView1" Name="DescID" PropertyName="SelectedValue" Type="Int32" /><asp:ControlParameter ControlID="GridView1" Name="FormNum" PropertyName="SelectedValue" Type="String" /><asp:Parameter Name="original_FormNum" Type="String" /><asp:Parameter Direction="InputOutput" size="25" Name="RetVal" Type="String" /></UpdateParameters><DeleteParameters><asp:Parameter Name="original_FormNum" Type="String" /><asp:Parameter Direction="InputOutput" Size="1" Name="NewRetVal" Type="Int16" /></DeleteParameters></asp:SqlDataSource>Code Behind:protected void SqlDS_Form_Deleted(object sender, SqlDataSourceStatusEventArgs e){  if (e.Exception == null)    {   string strRetVal = (String)e.Command.Parameters["@NewRetVal"].Value.ToString();    ............................Stored Procedure:CREATE PROCEDURE [dbo].[PTN_sp_Form_Del] (
@original_FormNum nvarchar(20),
@NewRetVal INT OUTPUT )
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @stoptrans varchar(5), @AvailFound int, @AssignedFound int

Set @stoptrans = 'NO'

/* ---------------------- Search PART #1 ----------------------------------------------------- */
SET @AvailFound = ( SELECT COUNT(*) FROM dbo.AvailableNumber WHERE dbo.AvailableNumber.FormNum = @original_FormNum )
SET @AssignedFound = ( SELECT COUNT(*) FROM dbo.AssignedNumber WHERE dbo.AssignedNumber.FormNum=@original_FormNum )

IF @AvailFound > 0 OR @AssignedFound > 0 /* It is ok if no rows found on available table, continue on to Assigned table, otherwise stop the deletion.*/
-----This means the delete can't happen...........
BEGIN

IF @AssignedFound > 0 AND @AvailFound = 0
BEGIN
SET @NewRetVal = 1
END

IF @AssignedFound > 0 AND @AvailFound > 0
BEGIN
SET @NewRetVal = 2
END

IF @AssignedFound = 0 AND @AvailFound > 0
BEGIN
SET @NewRetVal = 3
END
END

ELSE
BEGIN
DELETE FROM dbo.Form
WHERE dbo.Form.FormNum=@original_FormNum

SET @NewRetVal = 0
---Successful deletion
END
GO
 --------------------------------------------------------  When I go into the debug mode and do a quickwatch, the NewRetVal is showing as string input.

View 2 Replies View Related

How To Get Quantity From Output Of Query Command

Jun 7, 2012

how to get the quantity from output of sql query command for example, I could get the container name by below command

select container_name from sysibmadm.snapcontainer
get the container number by
select TBSP_NUM_CONTAINERS from SYSIBMADM.SNAPTBSP_PART

now I want to get the container number by below command output result select container_name from sysibmadm.snapcontainer..so what more command should I add on above command, I mean I want to get the container number by container name from the output of above command, not by 'join'.

View 1 Replies View Related

Command Prompt Output Capture?

Jun 25, 2014

How can i get the output (like 1 rows(s) inserted)in a file from an input file through sql command prompt.

View 1 Replies View Related

How To Get The Output Column In OLE DB Command Transformation

Jul 3, 2006



Hi,

I am writing a Dataflow task which will take a Particular column from the source table and i am passing the column value in the SQL command property. My SQL Command will look like this,

Select SerialNumber From SerialNumbers Where OrderID = @OrderID

If i go and check the output column in the Input and output properties tab, I am not able to see this serial number column in the output column tree,So i cant able to access this column in the next transformation component.

Please help me.

Thanks in advance.





View 13 Replies View Related

How To Get Error Output From And OLE DB Command Destination

Apr 21, 2006

I have a data flow that takes an OLE DB Source, transforms it and then uses an OLE DB Command as a destination. The OLE DB Command executes a call to a stored procedure and I have the proper wild cards indicated. The entire process runs great and does exactly what is intended to do.

However, I need to know when a SQL insert fails what record failed and I need to log this in a file somewhere. I added a Flat File Destination object and configured appropriately. I created 3 column names for the headers in the flat file and matched them with column names existing for output. When I run this package the flat file log is created ok, but no data is ever pumped into the file when a failure of the OLE DB Command occurs.

I checked the Advanced Editor for the OLE DB Command object and under the OLE DB Command Error Output node on the Input and Output Properties tab I notice that the ErrorCode and ErrorColumn output columns both have ErrorRowDisposition set to RD_NotUsed. I would guess this is the problem and why no data is written to my log file, but I cannot figure out how to get this changed (fields are greyed out so no access).

Any help would be greatly appreciated.

View 3 Replies View Related

OLE DB Command Transform And Output Columns.

Jan 10, 2007

Hi All,

I have an OLE DB transform with a SQL Command of:

sp_get_sponsor_parent ?,? OUTPUT

where sp_get_sponsor_parent is defined like:




CREATE PROCEDURE [dbo].[sp_get_sponsor_parent]

@pEID int,

@results int OUTPUT

AS

BEGIN


.

.

.
END

I map the columns, refresh & OK out of the component without trouble, but on executing the package it fails during validation on this component. I'm utterly stumped.
Any light shed would be greatly appreciated.
Many thanks in advance,
Tamim.

View 3 Replies View Related

Oledb Command Transformation Output

Feb 13, 2008



Hi,

I have two tables,

Table A on Server 1 (3 ROWS)
ID Name Address
ID1 A B
ID2 X Y
ID3 M N

There is another table on a different server which looks like

Table B on Server 2
PKColumn ID Details
1 ID1 Desc1
2 ID1 Desc2
3 ID1 Desc 3
4 ID2 Desc
5 ID2 Description

As you can see the ID is the common column for these two tables,
I want to get the Query the above 2 tables and the output should be dumped into a new table on Server2.

I am using the following SSIS Package

OledbDataSource-------> OledbCommand(Select * from TableB where ID =?)

From here, how can insert the rows returned from the oledb command into another table.
Since, for each row of TableA it will return some output rows...How can I insert all these into the New Table.

Please help on configuring the output of the oledb command.

Thanks,



View 5 Replies View Related

Output Param Of Stored Proc - Oledb Command

Jun 5, 2007

Hello

This is my first post so please be gentle!!

I have a stored proc that returns a value:



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
SET NOCOUNT ON

GO
-- =============================================
ALTER PROCEDURE [dbo].[spPK]
-- Add the parameters for the stored procedure here
@varNSC varchar(4),
@varNC varchar(2),
@varIIN varchar(7),
@varIMCDMC varchar(8),
@varOut as int output
AS
Declare
@varPK int
set @varPK = 0
BEGIN

--This checks Method 1
--NSC = @varNSC
--NC = @varNC
--IIN = @varIIN
begin
if exists
(select Item_id
From Item
Where NSC = @varNSC
and NC = @varNC
and IIN = @varIIN)
set @varPK =
(select Item_id
From Item
Where NSC = @varNSC
and NC = @varNC
and IIN = @varIIN)
set @varOut = @varPK
if @varPK <> 0 Return
end

[There are some more methods here]

Return

END



How do I get at the output value?

I have tried using derived column and ole db command but can't seem to grasp how to pass the value to the derived column. I can get oledb command to run using 'exec dbo.spPK ?, ?, ?, ?, ? output' but don't know what to do from here.

View 10 Replies View Related

SQLCMD Gives Crazy Output From A Simple SELECT Command

Sep 25, 2006

Hi everyone,

I am new to T-SQL and am trying to do some simple database for fun. Here is my problem and hope you guys can drop me some hint or solution:

OS: Win XP pro
SQL Server 2005 Express
Tool: SQLCMD in command prompt

I created a database and then create a table. Then I tried : select * from contract_Info, it gave me this:

1> CREATE TABLE Contract_Info
2> (
3> Order_No varchar(50) NOT NULL ,
4> Company varchar(255),
5> Product_Name varchar(255) NOT NULL,
6> Discount_Rate varchar(50), --Discount_Rate (%) -> Discount_Rate
7> Status varchar(50) NOT NULL,
8> Quantity varchar(50) NOT NULL,
9> Remainder varchar(50),
10> Deleted bit
11> )
12>
14>
15>
16> INSERT INTO Contract_Info values ('00000', 'DEFAULT','DEFAULT','0','Cancelle
d','0','0',0)
17>
18> go

(1 rows affected)
1> select * from contract_Info
2> go
Order_No Company


Product_Name



Discount_Rate Status
Quantity Rema
inder Deleted
-------------------------------------------------- -----------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------------ -------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-- -------------------------------------------------- --------------------------
------------------------ -------------------------------------------------- ----
---------------------------------------------- -------
00000 DEFAULT


DEFAULT



0 Cancelled
0 0
0

(1 rows affected)
1>





In SQL Express Management , the output looks perfectly fine

What is missing??

Thanks for your time

View 6 Replies View Related

Error Output In Oledb Command Advance Editor

Sep 27, 2007

I have a data flow and inside the data flow , i have a ole db source and ole db command task to execute an insert transaction ( SP).. i would like to save the error output if the insert didn;t happend into a error log table..
but when I darg an error output line ( red) to another ole db command to insert an error log , i can only see two columns( error code and error column) are available in OLEDB command advance editor related to errors.. this doesn;t tell you much information about the error.how can i grap the error reson(desc) as the error output and store into a erro log table? so that i can see what the problem is?

View 1 Replies View Related

Command Line Parameters Are Invalid Is The Output For A SQL Job To Run SSIS Package

May 25, 2006

Hello,

I created a SSIS package that has a flat file manager whose connection string is a package variable, the reason is that there is a foreachfile container loop to loop thru CSV format files in a directory and load them to a sql table.

The package execution is working in the designer studio as well as in the management studio-- a copy was saved to a sql 2005 server where a sql job is deployed to run the package. However when I ran the job, it claims successful but doesn€™t do anything in reality. If I checked the box failing the package upon validation warming, the job will fail with the error: the command line parameters are invalid. The command line looks like below:

/SQL "Package" /SERVER FTLQSQL03 /WARNASERROR /MAXCONCURRENT " -1 " /CHECKPOINTING OFF

One thing that I think maybe wrong is on the data source tab of the job step GUI, the flat file manager€™s connection string is blank, compared to other connection managers having related values.

Does anyone know how to make the job working?

Thanks in advance





I

View 3 Replies View Related

Getting Value From Output Parameter

Aug 2, 2006

I have an SQL INSERT statement with an output parameter called @CusRef. I have been trying to store this in a session variable, however all i am able to store is the reference to the parameter object. (The returned value stored in the parameter is an Integer)Session("CustomerReference") = DataConn.InsertParameters.Item("CusRef")I cant seem to find a value field or a method to get the value from a parameter. I have also tried using CType(DataConn.InsertParameters.Item("CusRef"), Integer) but it cant convert the Parameter type to an Integer.Please help,ThanksGareth

View 1 Replies View Related

Output Parameter?

Aug 25, 2006

A SQL Server 2005 DB table named "Users" has the following columns:
ID - int (IDENTITY)FirstName - varchar(50)LastName - varchar(50)UserID - varchar(20)Password - varchar(20)
Before inserting a new record in the DB table, ASP.NET first checks whether the UserID supplied by the new record already exists or not. If it exists, the new record shouldn't be inserted in the DB table & the user should be shown a message saying UserID already exists. To do this, ASP.NET uses a stored procedure. If the value returned by the stored procedure is 1, it means that the UserID already exists. If the value returned by the stored procedure is 0, then the new record should be inserted in the DB table. This is how I have framed the stored procedure:
CREATE PROCEDURE RegUsers        @FName varchar(50),        @LName varchar(50),        @UserID varchar(50),        @Password varchar(50),        @return_value int OUTPUTAS        IF EXISTS(SELECT UserID FROM Users WHERE UserID=@UserID)        BEGIN                SET @return_value=1        END        ELSE        BEGIN                SET @return_value=0                INSERT INTO Users VALUES (@FName,@LName,@UserID,@Password)        END
& this is how I am invoking the stored procedure from the ASPX page:
<script runat="server">    Sub btnSubmit(ByVal obj As Object, ByVal ea As EventArgs)        Dim sqlCmd As SqlCommand        Dim sqlConn As SqlConnection
        sqlConn = New SqlConnection("Data Source=MyDBSQLEXPRESS;Initial Catalog=DB;Integrated Security=True")        sqlCmd = New SqlCommand("RegUsers", sqlConn)        sqlCmd.CommandType = CommandType.StoredProcedure
        With sqlCmd            Parameters.Add("@return_value", SqlDbType.Int, 4).Direction = ParameterDirection.ReturnValue            Parameters.AddWithValue("@FName", txtFName.Text)            Parameters.AddWithValue("@LName", txtLName.Text)            Parameters.AddWithValue("@UserID", txtUserID.Text)            Parameters.AddWithValue("@Password", txtPassword.Text)        End With
        sqlConn.Open()        sqlCmd.ExecuteNonQuery()
        If (sqlCmd.Parameters(0).Value = 1) Then            lblMessage.Text = "UserID Already Exists!"        ElseIf (sqlCmd.Parameters(0).Value = 0) Then            lblMessage.Text = "Thanks For Registering!"        End If
        sqlConn.Close()    End Sub</script><form runat="server"><%-- the 4 TextBoxes come here --></form>
When I execute the above ASPX code, if the UserID I entered already exists in the DB table, then ASPX generates the following error:
Procedure or Function 'RegisterUsers' expects parameter '@return_value', which was not supplied.
pointing to the line
sqlCmd.ExecuteNonQuery()
Can someone please point out where am I going wrong?

View 1 Replies View Related

How Can I Get And Use An OUTPUT Parameter

Jan 31, 2007

Here is what I have so far, I can get a number added to the table running my sproc from management studio. But how do I get it out as it is being intserted and then use it in my code?ALTER PROCEDURE [dbo].[NumberCounter]
-- Add the parameters for the stored procedure here
@InsertDate datetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO tblNumberCounter (InsertDate) Values (@InsertDate);Select IDENT_CURRENT('tblNumberCounter')
END

Public Sub SubmitAdd_Click(ByVal Sender As System.Object, ByVal E As System.EventArgs)

Dim Con As SqlConnection
Dim StrInsert As String
Dim cmdInsert As SqlCommand
Dim myNewReceiptNumber As Integer
Dim ConnectStr As String = _
ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString

'Add row to receipt table, then get it for ReceiptNumberText field.
cmdInsert = New SqlCommand
cmdInsert.CommandText = "NumberCounter"
cmdInsert.CommandType = CommandType.StoredProcedure
cmdInsert.Connection = New SqlConnection(ConnectStr)

cmdInsert.Parameters.AddWithValue("@InsertDate", System.DateTime.Today.ToShortDateString())
Try
Con.Open()
myNewReceiptNumber = cmdInsert.ExecuteScalar()
'Response.Write(myNewReceiptNumber)
Catch objException As SqlException
Dim objError As SqlError
For Each objError In objException.Errors
Response.Write(objError.Message)
Next
Finally
Con.Close()
End Try

End Sub 

View 6 Replies View Related

Why Am I Not Getting My OUTPUT Parameter

Jan 7, 2008

Can anyone see in this stored procedure code and my post sub why the stored procedure is not getting the @ReceiptNumber?
@ReceiptNumber int OUTPUTASBEGININSERT INTO tblNumberCounter (InsertDate)Values (GETDATE())SET @ReceiptNumber=SCOPE_IDENTITY()INSERT INTO tblReceipts (pl_ID,client_ID,PaymentDate,Fund_ID,TenderTypeID,AmountPaid,ReceiptNumber,DateEntered,EnteredBy) SELECT     PL.Pl_ID, RS.client_id, PL.pmtDate, RS.rec_fund_id, RS.rec_tendertypeid, RS.rec_amount,                       @ReceiptNumber, RS.DateEntered, RS.EnteredByFROM         tblRec_setup AS RS INNER JOIN                      tblPayments AS PL ON RS.rec_id = PL.rec_id    Sub Post()        Dim cmdInsert1 As SqlCommand        Dim tr As SqlTransaction = Nothing        Dim ConnectStr As String = _        ConfigurationManager.ConnectionStrings("2ConnectionString").ConnectionString        Dim conn As New SqlConnection(ConnectStr)        cmdInsert1 = New SqlCommand        cmdInsert1.CommandType = CommandType.StoredProcedure        cmdInsert1.CommandText = "BatchMonthly"        'Get a new receipt number and add it.        Dim InsertedIntegerRN As New SqlParameter("@ReceiptNumber", SqlDbType.Int)        InsertedIntegerRN.Direction = ParameterDirection.Output        cmdInsert1.Parameters.Add(InsertedIntegerRN)        'Try        conn.Open()        tr = conn.BeginTransaction()        cmdInsert1.Transaction = tr        cmdInsert1.Connection = conn        cmdInsert1.ExecuteScalar()         tr.Commit()        'Catch objException As SqlException        tr.Rollback()        'Dim ex As SqlError        'Response.Write(ex.Message)        'Finally        conn.Close()        'End Try    End Sub 

View 7 Replies View Related

Can This Be Done With An Output Parameter?

Nov 4, 2003

Hi I want to make a Function in my User class that adds new members into the db. What I want to do is add the users details in using a stored procedure and input parameters and then return a parameter indicating their userid value and set it to a variable.

My userid column in the my db is auto incrementing with a seed of 1 and a step value of 1.

How could I create an output parameter that would return their new user id and then how would i set it to an integer variable.

Thanks

View 6 Replies View Related

Output Parameter

Jun 21, 2004

I keep getting an error stating my stored proc expects "@ret_value" and it's not declared. Even once it is declared, I'm not sure if my proc is going to return the value properly...been working on this half a day.


params put on new line for readability...

Private Sub update_DB()
Dim intResult, ret_value As Integer
ret_value = 0
intResult = SqlHelper.ExecuteNonQuery(ConfigurationSettings.AppSettings("connString"), CommandType.StoredProcedure, "Media_Tracking_add_MarketingID",
New System.Data.SqlClient.SqlParameter("@strMarketingID", txtMarketingID.Text),
New System.Data.SqlClient.SqlParameter("@strAdCampaignName", txtAdCampaignName.Text),
New System.Data.SqlClient.SqlParameter("@strCompany", ddlCompany.SelectedItem.Value),
New System.Data.SqlClient.SqlParameter("@strCampaignType", ddlCampaignType.SelectedItem.Value),

New System.Data.SqlClient.SqlParameter("@ret_value", ret_value, ParameterDirection.Output))


lblSuccess.Text = "RETURN = " & ret_value
End Sub


S Proc

CREATE PROCEDURE [dbo].[media_tracking_add_MarketingID]
@strMarketingID nvarchar(50),
@strAdCampaignName nvarchar(50),
@strCompany nvarchar(3),
@strCampaignType nvarchar(50),
@ret_value int OUTPUT
AS
INSERT INTO media_tracking_Marketing_IDs(MarketingID,AdCampaignName,company,status,CampaignType)
values (@strMarketingID,@strAdCampaignName,@strCompany,'1',@strCampaignType);
set @ret_value = scope_identity();
return @ret_value;
GO

View 4 Replies View Related

Output Parameter

Nov 17, 2005

Hello!Can anybody tell me how can I retreive the value of an output parameter from an stored procedure using an SqlDataSource control?Thank you

View 1 Replies View Related

Parameter Output

Apr 28, 2008

Can any body help me
CREATE PROCEDURE getRowCount
@where NVARCHAR(500)
,@totalRows INT OUTPUT
AS
BEGIN
DECLARE @SQL NVARCHAR(2000)
SET @SQL = ' SELECT ' + cast(@totalRows as NVARCHAR) + ' = COUNT(*)
FROM Employee E'

IF LEN(@where ) > 0
BEGIN
SET @SQL= @SQL + @Where
END

EXEC (@SQL)
END

It doesn't return a totalRows value.
Thanks in advance.

View 9 Replies View Related

How Do I Run This SP To Get Output Parameter?

Dec 7, 2007

I've tried using:
Declare @answer bit
Execute procUserVer username answer output

Create PROCEDURE procUserVer
@User char(15),
@Validate bit output
AS
Declare @UserFound int
Select @UserFound = count(*)
From usernames
where networklogin = @user
select @userFound

if @userFound >0
set @validate = 1
else
set @validate = 0

View 6 Replies View Related

Exec Sp With Output Parameter

Feb 28, 2007

I have the following sp:ALTER PROCEDURE myspPrepareTextForHTML @MessageContent nvarchar(1400), @returnval nvarchar(1400) outputASBEGINSET NOCOUNT ON;SET @returnval='something'RETURN @returnvalENDWhen I try this:
EXEC myspPrepareTextForHTML @MessageContent='dfgsdfgdf', @returnval OUTPUT
print @returnval
I get the error:Must declare the scalar variable "@returnval".
How can I get this to work?

View 7 Replies View Related

Need Help Processing SP Output Parameter

May 7, 2007

Stored procedure works:
PROCEDURE dbo.VerifyZipCode@CZip nvarchar(5),@CZipVerified nvarchar(5) outputAS IF (EXISTS(SELECT ZIPCode FROM ZipCensus11202006 WHERE ZIPCode = @CZip)) SET @CZipVerified = 'Yes' ELSE SET @CZipVerified = 'Not Valid Zip'RETURN
 Need help calling and processing sp information:
 
protected void C_Search_btn_Click(object sender, EventArgs e){        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["localhomeexpoConnectionString2"].ConnectionString);        SqlCommand cmd = new SqlCommand("VerifyZipCode", con);        cmd.CommandType = CommandType.StoredProcedure;        cmd.Parameters.AddWithValue("@CZip", UserZipCode_tbx.Text);
             . . . how do you set up output parameter, call the SP and capture output. . . ?        if (@CZipVerified == "Not Valid Zip")               {                    TextBox5.Text = "Zip code not valid please re-enter zip";               }                    else               {                    Continue processing page               }
}

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved