Call To Stored Proc Returning Null Datatable

Jun 6, 2007

I have a stored proc which should be returning a datatable.  When I execute it manually it returns all requested results. However, when I call it via code (C#) it is returning a null table which leads me to believe the problem is in my code.  I'm not getting any errors during runtime.  Any help at all would be a BIG help!

private void PopulateControls()
    {
        DataTable table = CartAccess.getCart();
    }

public static DataTable getCart()
 {
        DbCommand comm = GenericDataAccess.CreateCommand();
        comm.CommandText = "sp_cartGetCart";

        DbParameter param = comm.CreateParameter();
        param.ParameterName = "@CartID";
        param.Value = cartID;
        param.DbType = DbType.String;
        param.Size = 36;
        comm.Parameters.Add(param);

        DataTable table = (GenericDataAccess.ExecuteSelectCommand(comm));
        return table;
 }


public static DataTable ExecuteSelectCommand(DbCommand command)
    {
        // The DataTable to be returned
        DataTable table;
        // Execute the command making sure the connection gets closed in the end
        try
        {
            // Open the data connection
            command.Connection.Open();
            // Execute the command and save the results in a DataTable
            DbDataReader reader = command.ExecuteReader();
            table = new DataTable();
            table.Load(reader);
            // Close the reader
            reader.Close();
        }
        catch (Exception ex)
        {
            Utilities.SendErrorLogEmail(ex);
            throw ex;
        }
        finally
        {
            // Close the connection
            command.Connection.Close();
        }
        return table;
    }

View 1 Replies


ADVERTISEMENT

Filling A DataTable From SqlQuery : If SqlQuery Returns Null Values Problem Ocurrs With DataTable

Sep 3, 2007

Filling a DataTable from SqlQuery : If SqlQuery returns some null values problem ocurrs with DataTable.
Is it possible using DataTable with some null values in it?
Thanks

View 2 Replies View Related

How Can I Call One Or More Stored Procedures Into Perticular One Stored Proc ?

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

Need To Call Sp_replicationdboption From Another Stored Proc

Feb 10, 2006

i am trying to call sp_replicationdboption from another stored procedure that i wrote myself but it is giving me the following error
"sp_replicationdboption cannot be executed within a transaction"
note that i'm not using begin or commit transaction in my stored procedure
is there a way to do this

View 1 Replies View Related

Remote Stored Proc Call

Aug 29, 2007

I'm calling this from another sql server....
I created a linked server... and want to restore database backups on the other box....
The restore script runs fine when ran locally but fails with the message below when calling it remotely

Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Server: Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.

CREATE PROCEDURE usp_restore_database_backups AS

RESTORE DATABASE BesMgmt
FROM DISK = 'D:MSSQLBACKUPBesMgmtBesMgmt_backup_device.bak '
WITH
--DBO_ONLY,
REPLACE,
--STANDBY = 'D:MSSQLDataBesMgmtundo_BesMgmt.ldf',
MOVE 'BesMgmt_data' TO 'D:MSSQLDataBesMgmt.mdf',
MOVE 'BesMgmt_log' TO 'D:MSSQLDataBesMgmt.ldf'

WAITFOR DELAY '00:00:05'


EXEC sp_dboption 'BesMgmt', 'single user', true
GO


I have set it to read only dbo only .... single user.... still get the same message....
does anyone have any suggestions....

View 6 Replies View Related

HELP!! HOW TO CALL A DTS PACKAGE FROM A STORED PROC

Jul 20, 2005

I'm a rookie with MSSQL. I need to run a DTS package to export a result setto an MX Excel spread sheet. I need to call the DTS from a stored procedureand pass it three values, depending on the input parameters to the storedproc.DTS package is no problem. Pretty easy with the DTS wizard. My problem isthat I can't figure out how to instansiate the DTS package object from astored proc and pass the three values as parameters to the DTS package sothey can populate the parameters I created in it.I found an article related to it, but I'm too much of a rookie to grasp it.It showed how to do this from a stored procedure:EXEC @hr = sp_OASetProperty @oPKG, 'GlobalVariables("MyGVName").Value,'MyGVValue'IF @hr <> 0BEGINPRINT '*** GlobalVariable Assignment Failed'EXEC sp_displayoaerrorinfo @oPKG, @hrENDI have three values and tree global variables to populate. Do I need to dothe above 3 times?How do I instantiate the package object? I've read up some on sp_OACreate,but I don't get it, yet.How do I initiate the variable @oPKG?It contains the name of the sp_OACreate string, right? How do I address aDTS package in the sp_OACreate string?I would really appreciate just writing out the sp_OACreate string and how Ipass values for three existing global variables to a DTS package named"DTS_1".I'm under some real pressure to get this done.Thanks for any help I can get.Gunny

View 1 Replies View Related

Recursive Call In Stored Proc

Jul 20, 2005

Hi allI am implementing a stored procedure which needs to recursively callitself until specific condition is reached, Could anyone give someadvice about that?Thanks a lotRobert Song

View 3 Replies View Related

Returning Recordset From Stored Proc

Mar 7, 2008

I need to return a rowset from a stored procedure.  The returned rows will have ten columns and need to be bound to a gridview.  Here is a code snippet.
'Create a DataAdapter, and then provide the name of the stored procedure.MyDataAdapter = New SqlDataAdapter("TMPTABLE_QUERY", MyConnection)
'Set the command type as StoredProcedure.
MyDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
'Create and add a parameter to Parameters collection for the stored procedure.MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@condition_cl", _
SqlDbType.VarChar, 100))
'Assign the search value to the parameter.MyDataAdapter.SelectCommand.Parameters("@condition_cl").Value = sqlwhere & orderby
'ASSIGN THE OUTPUT PARAMETERS. HERE IS WHERE I NEED HELP  (?????????????????)
 
DS = New DataSet() 'Create a new DataSet to hold the records.
MyDataAdapter.Fill(DS, "TMPTABLE_QUERY") 'Fill the DataSet with the rows returned.
'Set the data source for the DataGrid as the DataSet that holds the rows.GridView1.DataSource = DS.Tables("TMPTABLE_QUERY").DefaultView
GridView1.DataBind()
MyDataAdapter.Dispose() 'Dispose of the DataAdapter.
MyConnection.Close()
 
In my little research, I have seen examples of how to return a single value, but not multiple rows.  I essentially have two problems.  I'm not sure how my output parameters are to be defined and added.  Do I need a separate 'Parameters.Add' statement for each column field value returned or can I do a single 'Parameters.Add' statement to define the whole row as an output parameter?  Also, upon returning from the call to the SP, will I need a looping mechanism to populate the recordset for each individual record returned, or will the 'MyDataAdapter.Fill(DS, "TMPTABLE_QUERY") suffice, as included in my code above?
Thanks in advance.

View 3 Replies View Related

Stored Proc Returning 0 Size

Mar 3, 2004

i have a stored proc


CREATE PROCEDURE pop_notes ( @contnum as bigint,@cusnotes varchar(1000) OUTPUT) as

begin

declare @noteid as int
select @noteid=max(noteid) from cusaddnotes where contractnum=@contnum and rtrim(popup)='yes'
if @noteid > 0
begin
select @cusnotes=(notes + ' [' + convert(varchar(100),dateadded) + ']' ) from cusaddnotes where noteid =@noteid

end
else
begin
set @cusnotes='none'
end
print @cusnotes
end
GO


the srored proc is returning the @cusnotes with a size 0 and its throwing out errors in my asp.net page

'@cusnotes' of type: String, the property Size has an invalid size: 0

i tried to run the stored proc frm the QA

declare @note as varchar(1000)
exec @note=pop_notes 3430,'y'
print @note



it returned

none
0 <-- this seems to be causing the error


what could be the error/reason...

thanks in advance

View 3 Replies View Related

Returning @@IDENTITY FROM Stored Proc

Nov 15, 2005

I am trying to get the identity of an inserted record using this SP:

<code>
ALTER PROCEDURE acereal_Admin.AuctionInsertCommand
(
    @AuctionID Int OUTPUT,
    @StartDate char(255),
    @StartTime char(255),
    @Location char(255),
    @Title char(255),
    @Description char(255),
    @Images char(255)
)
AS
INSERT INTO Auctions
(
    StartDate,
    StartTime,
    Location,
    Title,
    Description,
    Images
)
VALUES
(
    @StartDate,
    @StartTime,
    @Location,
    @Title,
    @Description,
    @Images
)

SELECT     AuctionID AS ID
FROM         Auctions
WHERE     (AuctionID = @@IDENTITY)
</code>

Then, I am using this class for a file called dataaccess.cs to return the ID:

<code>
public string SaveImageName(string ImageName, string AuctionID)
        {
            SqlConnection
sqlConnection = new
SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);

           
this.newSqlCommand = new
System.Data.SqlClient.SqlCommand("[AuctionImageSave]", sqlConnection);
           
this.newSqlCommand.CommandType =
System.Data.CommandType.StoredProcedure;

            SqlParameter
paramImagePath = new System.Data.SqlClient.SqlParameter("@ImagePath",
System.Data.SqlDbType.Char, 255);
            paramImagePath.Value = ImageName;
            this.newSqlCommand.Parameters.Add(paramImagePath);

            SqlParameter
paramAuctionID = new System.Data.SqlClient.SqlParameter("@AuctionID",
System.Data.SqlDbType.Int, 4);
            paramAuctionID.Value = AuctionID;
            this.newSqlCommand.Parameters.Add(paramAuctionID);

            SqlParameter
paramImageID = new System.Data.SqlClient.SqlParameter("@ImageID",
System.Data.SqlDbType.Int, 4);
            paramImageID.Direction = ParameterDirection.Output;
            this.newSqlCommand.Parameters.Add(paramImageID);

            //Return SqlDataReader Struct
            this.newSqlCommand.Connection.Open();
            this.newSqlCommand.ExecuteNonQuery();
            this.newSqlCommand.Connection.Close();

            int returnID = (int)paramImageID.Value;

            return returnID.ToString();
        }
</code>

The above code returns null.

Can anyone tell me what I am doing wrong?

THanks, Justin.

View 2 Replies View Related

Returning Errors From Stored Proc

Feb 5, 2007

Hi,

Does sql server return an error code when a stored procedure fails, or an operation inside a stored procedure fails. How does one trap it?

We have an application that executes a number of stored procedures. I would like to verify that the procedures executed successfully, and the operations contained within the stored procedures (i.e. insert into a table) did not result in an error. If there was an error with the stored procedure of the operations inside, I would like to trap it, and return it to the calling program (non sql server).

I have seen the @@error global variable, but it only gives the return code of the last operation, which means I would have to check after every select, insert, delete, etc. I am hoping to avoid this.

Any thoughts?

Thanks in advance

View 2 Replies View Related

Returning A Value From Stored Proc With Query

Jul 18, 2014

In some of our business object reports we have to create variables to decode values to what we want. I am trying to replicate this in SQL Server and remember doing this in SQL server 2000 years ago back can't remember the exact way to do it. I remember running a query and calling stored proc within query which would return the value I wanted but not sure if I can still do this in SQL server 2008 and by that I mean doing it within query or have to do it another way.

Basically what I want is to have a procedure with all the variables replicated within that procedure so that when I run a query I can just call the appropriate bit of code by passing a specific name like

select job.dept, dbo.decodevariable('ShowJobDesc' job.jobtitle), job.salary
from job

so 'ShowJobDesc' and the job.jobtitle would be used to decode each job title to return job description.Just a bit unsure and can't remember if I am doing this the right way, is this possible?

View 2 Replies View Related

Retreive @@Identity From Stored Proc Call

May 3, 2007

Hi all, I've been struggling with this one for a while, but am still doing something wrong: I have three tables which need to be updated. I have a stored proc which accomplishes the first writes the data in for the first 2 tables. The last table is a one to many, so needs a seperate stored proc which will be called multiple times depending on the number of items in the order.  My only question I am trying to get to here is: How do I get the first stored proc to return me the primary key value from the 1st insert (NOT the second)? I've tried a few different methods: the current one shown below returns me  "2", as in the number of inserts performed.   Dim InsertCmd As New SqlCommand("WriteOrder", oSQLConn)
InsertCmd.CommandType = CommandType.StoredProcedure
InsertCmd.Parameters.AddWithValue("@CartTotal", Session("CartTotal"))
InsertCmd.Parameters.AddWithValue("@CARDFNAME", BillingInfo("CARDFNAME"))
InsertCmd.Parameters.AddWithValue("@CARDLNAME", BillingInfo("CARDLNAME"))
...
InsertCmd.Parameters.AddWithValue("@CONTACTEMAIL", BillingInfo("CONTACTEMAIL"))
InsertCmd.Parameters.AddWithValue("@COMPANYMATCH", 0)
InsertCmd.Parameters.AddWithValue("@RECNUM", 0)
Response.Write("---" & InsertCmd.ExecuteNonQuery().ToString() & "---")


---------------------STORED PROC---------------------

ALTER PROCEDURE [dbo].[WriteOrder]
@CartTotal float,
@CARDFNAME varchar(30),
...
@CONTACTEMAIL varchar(100),
@COMPANYMATCH bit,
@RecNum int = 0 OUTPUT
AS

INSERT INTO [dbo].[ORDER]
([OrderDate], [OrderTotal], [CARDFIRSTNAME], [CARDLASTNAME], [BILLINGADDR1], [BILLINGADDR2], [BILLINGADDR3], [BILLINGCITY], [BILLINGSTATE], [BILLINGPOSTALCODE], [BILLINGCOUNTRY], [BILLINGPHONE], [BILLINGEMAIL], [CCTYPE], [ACCOUNTNUMBER], [CARDEXPIREMONTH], [CARDEXPIREYEAR], [CVV2], [TransactionID], [TransDateStamp], [ProcessorAuthCode])

VALUES
(GetDate() ,@CartTotal , @CARDFNAME, @CARDLNAME, @BILLINGADDRESS1, @BILLINGADDRESS2, @BILLINGADDRESS3, @BILLINGCITY, @BILLINGSTATE, @BILLINGPOSTALCODE, @BILLINGCOUNTRY, @BILLINGPHONE, @BILLINGEMAIL, @CCTYPE, @ACCOUNTNUMBER, @EXPIRATIONMONTH, @EXPIRATIONYEAR, @CVV2,@TransactionID, @TransDateStamp,@ProcessorAuthCode)

SET @RecNum = @@IDENTITY

INSERT INTO [dbo].[CONTACT]
([ORDERID], [CONTACTFNAME], [CONTACTLNAME], [CONTACTADDRESS1], [CONTACTADDRESS2], [CONTACTADDRESS3],
[CONTACTCITY], [CONTACTSTATE], [CONTACTPOSTALCODE], [CONTACTCOUNTRY], [CONTACTPHONE], [CONTACTEMAIL], [COMPANYMATCH])
VALUES
(@@IDENTITY , @CONTACTFNAME, @CONTACTLNAME, @CONTACTADDRESS1, @CONTACTADDRESS2, @CONTACTADDRESS3, @CONTACTCITY, @CONTACTSTATE, @CONTACTPOSTALCODE, @CONTACTCOUNTRY, @CONTACTPHONE, @CONTACTEMAIL, @COMPANYMATCH)
RETURN @RecNum
GO

 I've also tried returning parameters like this, with no luck:        InsertCmd.Parameters(32).SqlDbType = SqlDbType.Int        InsertCmd.Parameters(32).Direction = ParameterDirection.ReturnValue        Response.Write("---" & InsertCmd.Parameters(32).Value() & "---")Any help is greatly appreciated! 

View 4 Replies View Related

Call A DTS Export Into Excel From A Stored Proc ??

Jul 8, 1999

I'm new to 7.0 and to DTS, and I find it all very confusing and need some help. :)

1) I need to automatically import data from a text file on a daily basis. Can someone tell me in short, simple steps how to set this up?

2) I need to export certain data into a new Excel sheet. Can this be triggered from a stored procedure, by calling some function? If yes, is it possible to send parameters to this function?

thanks bunches.

-M

View 1 Replies View Related

Call Webservice From Trigger/stored Proc

Sep 17, 2004

Is it possible to call an external web service from a SQL Server trigger or stored procedure?

View 6 Replies View Related

Call A Oracle Stored Proc In SQL SERVER

Jul 20, 2005

We have set up Oracle database as a linked server in SQL Server.We are able to access Oracle tables fine.I am trying to call a Oracle stored procedure in SQL Server as follows:declare @p1 varchar(1000)set @p1 = 'HHH'exec GENRET..OPS$GENRET.BOB_TEST_PROC @p1This is the message:Server 'GENRET' is not configured for RPC.Please help.Thanks in advancev

View 1 Replies View Related

SQL Stored Proc Not Returning Any Data In The Web Page

Feb 26, 2007

Hi
I have coded the simple login page in vb .net  which calls the stored proc to verify whether the user login details exists in the database.  The stored procudure returns data back when I execute it in the SQL SERVER Management studio. But when I  execute the stored proc in the 'Run stored Proc' wizard' , it is not retuning any data back. Connection string works fine as another SQL select command returns data in the same page.. I have included the VB code . Please help me to sort out this problem.Thank you.
 
If Not ((txtuser.Text = "") Or (txtpassword.Text = "")) Then
 
 
Dim conn As New SqlConnection()
conn.ConnectionString = Session("constr")
conn.Open()
 
Dim cmd As New SqlCommand("dbo.CheckLogin", conn)
cmd.CommandType = CommandType.StoredProcedure
' Create a SqlParameter for each parameter in the stored procedure.
Dim usernameParam As New SqlParameter("@userName", SqlDbType.VarChar, 10)
usernameParam.Value = Trim(txtuser.Text)
 
Dim pswdParam As New SqlParameter("@password", SqlDbType.NVarChar, 10)
pswdParam.Value = Trim(txtpassword.Text)
 
Dim useridParam As New SqlParameter("@userid", SqlDbType.NChar, 5)
Dim usercodeParam As New SqlParameter("@usercode", SqlDbType.VarChar, 10)
Dim levelParam As New SqlParameter("@levelname", SqlDbType.VarChar, 50)
 
'IMPORTANT - must set Direction as Output
useridParam.Direction = ParameterDirection.Output
usercodeParam.Direction = ParameterDirection.Output
levelParam.Direction = ParameterDirection.Output
 
'Finally, add the parameter to the Command's Parameters collection
cmd.Parameters.Add(usernameParam)
cmd.Parameters.Add(pswdParam)
cmd.Parameters.Add(useridParam)
cmd.Parameters.Add(usercodeParam)
cmd.Parameters.Add(levelParam)
 
Dim reader1 As SqlDataReader
Try
If conn.State = ConnectionState.Closed Then
conn.Open()
End If
Try
reader1 = cmd.ExecuteReader
Using reader1
 
If reader1.Read Then
Response.Write(CStr(reader1.Read))
Session("userid") = reader1.GetValue(0)
Session("usercode") = CStr(usercodeParam.Value)
Session("level") = CStr(levelParam.Value)
Server.Transfer("home.aspx")
Else
ErrorLbl.Text = "Inavlid Login. Please Try logging again" & Session("userid") & Session("usercode") & Session("level")
End If
End Using
Catch ex As InvalidOperationException
ErrorLbl.Text = ex.ToString()
End Try
Finally
If conn.State <> ConnectionState.Closed Then
conn.Close()
End If
 
End Try
 
Else
ErrorLbl.Text = "Please enter you username and password"
 
 
 
End If
 

View 5 Replies View Related

Returning Mutliple Strings From Stored Proc

May 14, 2007

Hey guys, could somebody pls provide me with an easy example as to how you would return multiple strings from a stored proc? Even a link to a decent tut would be great!

Muchos gracias!

View 12 Replies View Related

Can You Call A Stored Proc That Returns A Table Variable Using ADO?

Jan 8, 2004

I have a stored proc that inserts into a table variable (@ReturnTable) and then ends with "select * from @ReturnTable."

It executes as expected in Query Analyzer but when I call it from an ADO connection the recordset returned is closed. All the documentation that I have found suggests that table variables can be used this way. Am I doing somthing wrong?

View 1 Replies View Related

Call Cfusion Web Page/webserice From Stored Proc

Jun 12, 2008

I am trying to call a CF web page/web service from a SQL 2005 stored proc and getting proxy info cannot be created. I cannot use stored proc 2005 CLR assembly because it will help us in creating only .asmx proxy not CFC proxy , any help would be appreciated.

exec master..xp_cmdshell 'http://wifi.abctest.com/Test/lartnerCall.cfm

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.

View 1 Replies View Related

Transact SQL :: Stored Proc Call By Adding Right Login

May 14, 2015

I have dw schema in the database, owned by user dw.The login name is dw. The login had db_owner right in the database. The default schema for the login on the database is dw.Now Once I assign 'sysadmin' serverrole to dw login, I started seeing stored proc not found error, if try to execute stored proc without mentioning dw.spname;Also I am seeing table not found error while quering tables under dw schema, after the change.

View 20 Replies View Related

Stored Proc Call, 'table Name' As String, And T-sql Statement

Jun 16, 2006

I need to write a storedproc that receives the name of a table (as a string) and inside the stored proc uses select count(*) from <tablename>. The problem is the passed in tablename is a string so it can't be used in the select statement. Any ideas how I can do what I want?



TIA,

barkingdog



View 1 Replies View Related

SELECT Returning Multiple Values In A Stored Proc

Jul 20, 2005

HiI'm not sure what the best approach for this is:I have a stored procedure which I would like to use to return severaloutput values instead of returning a recordset.CREATE PROCEDURE Test (@param1 int, @param2 int OUTPUT, @param3 intOUTPUT) ASSELECT field2, field3 FROM Table WHERE field1 = @param1I would like to return @param2 as field2 and @param3 as field3How do I do this without using SELECT multiple times?THanks in advanceSam

View 6 Replies View Related

Openrowset In Stored Proc Returning 'S:' Is Not A Valid Path

Nov 2, 2007

I am having a difficult time figuring this one out. I have a stored procedure that as part of an ASP.Net app connects to a csv file through a mapped network drive and imports the data into a table in SQL Server. Here it is.

------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[usp_ImportComp]

-- Add the parameters for the stored procedure here
@CSV as varchar(255)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Begin Try
Begin Tran
Declare @sql as nvarchar(max)
Set @SQL = 'Insert into dlrComp ( SN,
CM,
PM,
MC,
Comp_Total,
XU,
GM,
From_Date,
To_Date)
Select [Serial Number],
CM,
PM,
MC,
[Comp Total],
XU,
GM,
[From Date],
[To Date]
FROM OPENROWSET(' + char(39) + 'MICROSOFT.JET.OLEDB.4.0' + char(39) + ',' + char(39) + 'Text;Database=S:' + char(39) + ',' + char(39) + 'SELECT * FROM ' + @csv + char(39) + ')'
--print @sql
Exec sp_executesql @stmt=@sql
Commit Tran
End Try
Begin Catch
Rollback Tran
--Do some logging and stuff here
End Catch
END

------------------------------------------------------------------------------------

If I am connected to SQL through SQL Management Studio while logged in on the server that is running SQL as DomainUser1 and execute

exec usp_ImportComp @CSV='Comp.csv'

It completes successfully

However if I open SSMS (while logged into Windows on my PC as DomainUser2) using runas to run it as DomainUser1 while logged into my PC and connect to SQL Server using WIndows Auth and run the same I get the following error message.

OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)" returned message "'S:' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.".

If I add
With Execute as 'DomainUser1' and modify the stored procedure I get the same error message above.

If I log onto the Server that is running SQL as DomainUser2 I can successfully run

exec usp_ImportComp @CSV='Comp.csv'

Both User1 and User2 have the same permissions to the Share and csv as does the Domain user under whose context SQL Server is running.

What am I doing wrong?

View 4 Replies View Related

NH: Best Practices Approach - Call Stored Proc - Or Run It Via Linked Server?

Mar 27, 2007

what pro's cons would there be to having a linked server run a local stored proc against another sql server or create that stored proc on that other sql server and call it from there in the c# code.
i would think that calling the stored proc would be more efficient that running a linked server - but please let me know your thoughts. I'm not sure i can have permission to add a stored proc on that server, so possibly the linked server is the only solution - but if i can put a stored proc on that server should i?
thanks.
Jeff 

View 4 Replies View Related

SQL Server 2012 :: Call Stored Proc Once Per Each Row Of A Table Without Using CURSOR

Jul 10, 2014

I have a situation where I need to call a stored procedure once per each row of table (with some of the columns of each row was its parameters). I was wondering how I can do this without having to use cursors.

Here are my simulated procs...

Main Stored Procedure: This will be called once per each row of some table.

-- All this proc does is, prints out the list of parameters that are passed to it.

CREATE PROCEDURE dbo.MyMainStoredProc (
@IDINT,
@NameVARCHAR (200),
@SessionIDINT
)
AS
BEGIN

[Code] ....

Here is a sample call to the out proc...

EXEC dbo.MyOuterStoredProc @SessionID = 123

In my code above for "MyOuterStoredProc", I managed to avoid using cursors and was able to frame a string that contains myltiple EXEC statements. At the end of the proc, I am using sp_executesql to run this string (of multipl sp calls). However, it has a limitation in terms of string length for NVARCHAR. Besides, I am not very sure if this is an efficient way...just managed to hack something to make it work.

View 9 Replies View Related

How To Call AS400 Stored Proc And Evaluate The Return Code?

May 30, 2007

I am trying to use SSIS to update an AS400 DB2 database by calling a stored procedure on the AS400 using an OLE DB command object. I have a select statement running against the SQL Server 2005 that brings back 20 values, all of which are character strings, and the output of this select is piped into the OLE DB command object. The call from SSIS works just fine to pass parameters into the AS400 as long as the stored procedure being called does not have an output parameter defined in its signature. There is no way that I can find to tell the OLE DB command object that one of the parameters is an output (or even an input / output) parameter. As soon as one of the parameters is changed to an output type, I get an error like this:






Code Snippet


Error: 0xC0202009 at SendDataToAs400 1, OLE DB Command [2362]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8000FFFF.

Error: 0xC0047022 at SendDataToAs400 1, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Command" (2362) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

Error: 0xC0047021 at SendDataToAs400 1, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0202009. There may be error messages posted before this with more information on why the thread has exited.

Information: 0x40043008 at SendDataToAs400 1, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x40043009 at SendDataToAs400 1, DTS.Pipeline: Cleanup phase is beginning.

Task failed: SendDataToAs400 1

Warning: 0x80019002 at RetrieveDataForSchoolInitiatedLoans: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

Warning: 0x80019002 at Load_ELEP: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Load_ELEP.dtsx" finished: Failure.





I really need to know if the call to the AS400 stored procedure succeeded or not, so I need a way to obtain and evaluate the output parameter. Is there a better way to accomplish what I am trying to do? Any help is appreciated.

View 3 Replies View Related

Stored Proc Returning Dataset && Number Of Records Not Working

Jul 7, 2004

Hi

I've created a sproc in SQL2000 that returns a dataset from a temp table & the number of records it's returning as an output parameter, although I can't seem to retrieve the value it's returning in asp.net although I get the dataset ok.

This is my sproc
create procedure return_data_and_value
@return int output
as
set nocount on
...
...
select * from #Table
select @return = count(*) from #Table
drop table #Table
go

This is asp.net code

Dim nRecords as Int32
Dim cmd As SqlCommand = New SqlCommand("return_data_and_value", conn)
cmd.CommandType = CommandType.StoredProcedure

Dim prm As SqlParameter = New SqlParameter("@return", SqlDbType.Int)
prm.Direction = ParameterDirection.Output
cmd.Parameters.Add(prm)

conn.Open()

dr = cmd.ExecuteReader

nRecords = convert.int32(cmd.parameters(@return).value)

conn.close


Thanks
Lbob

View 1 Replies View Related

FoxPro Triggers Call FoxPro Stored Proc Calls SQL Server Stored Procedure

Mar 10, 2005

I didn't want to maintain similar/identical tables in a legacy FoxPro system and another system with SQL Server back end. Both systems are active, but some tables are shared.

Initially I was going to use a Linked Server to the FoxPro to pull the FP data when needed. This works. But, I've come up with what I believe is a better solution. Keep in mind that these tables are largely static - occassional changes, edits.

I will do a 1 time DTS from FP into SQL Server tables.

I then create INSERT and UPDATE triggers within FoxPro.

These triggers fire a stored procedure in FoxPro that establishes a connection to the SQL Server and fire the appropriate stored procedure on SQL Server to CREATE and/or UPDATE the corresponding table there.

In the end - the tables are local to both apps.

If the UPDATES or TRIGGERS fail I write to an error log - and in that rare case - I can manually fix. I could set it up to email me from within FoxPro as well if needed.

Here's the FoxPro and SQL Server code for reference for the Record Insert:

FOXPRO employee.dbf InsertTrigger:
employee_insert_trigger(VAL(Employee.ep_pk),Employ ee.fname,Employee.lname,Employee.email,Employee.us er_login,Employee.phone)

FOXPRO corresponding Stored Procedure:
FUNCTION EMPLOYEE_INSERT_TRIGGER
PARAMETERS wepk,wefname,welname,weemail,WEUSERID,WEPHONE

nhandle=SQLCONNECT('SS_PDITHP3','userid','password ')

IF nhandle<0
m.errclose=.f.
IF !USED("errorlog")
USE tisdata!errorlog IN SELECT(1)
m.errclose=.t.
ENDIF

SELECT errorlog
INSERT INTO errorlog (date, time, program,source,user) ;
values (DATE(), TIME(), 'EMPLOYEE_INSERT_TRIGGER','nhandle<0 PARAMS: '+STR(wepk)+wefname+welname+weemail+WEUSERID+WEPHO NE,GETENV("username"))

IF m.errclose
USE IN errorlog
ENDIF
RETURN

ENDIF
nquery="exec ewo_sp_insertNewEmployee @WEPK ="+STR(wepk)+",@WEFNAME ='"+wefname+"',@WELNAME ='"+welname+"',@WEEMAIL ='"+weemail+"',@WEUSERID ='"+weuserid+"',@WEPHONE='"+wephone+"',@RETCODE =0"
nsucc=SQLEXEC(nhandle,nquery)

SQLDISCONNECT(nhandle)

IF nSucc<0
m.errclose=.f.
IF !USED("errorlog")
USE tisdata!errorlog IN SELECT(1)
m.errclose=.t.
ENDIF

SELECT errorlog
INSERT INTO errorlog (date, time, program,source,user) ;
values (DATE(), TIME(), 'EMPLOYEE_INSERT_TRIGGER','nSucc<0 PARAMS: '+STR(wepk)+wefname+welname+weemail+WEUSERID+WEPHO NE,GETENV("username"))

IF m.errclose
USE IN errorlog
ENDIF
ENDIF

RETURN

SQL SERVER Stored Procedure called from FOXPRO Stored Procedure
CREATE procedure ewo_sp_insertNewEmployee (
@WEPK int,
@WEFNAME char(20),
@WELNAME char(20),
@WEEMAIL char(50),
@WEUSERID char(15),
@WEPHONE char(25),
@RETCODE int OUTPUT
)

AS

insert into WO_EMP (
WE_PK,
WE_FNAME,
WE_LNAME,
WE_EMAIL,
WE_USERID,
WE_PHONE
)

VALUES (
@WEPK,
@WEFNAME,
@WELNAME,
@WEEMAIL,
@WEUSERID,
@WEPHONE
)


IF @@ERROR <> 0
BEGIN
SET @RETCODE=@@ERROR
END
ELSE
BEGIN
-- SUCCESS!!
SET @RETCODE=0
END

return @RETCODE
GO

View 2 Replies View Related

TableAdapter/DataTable Query Returning No Results

Sep 6, 2007

Hello,
I have a query that works in query analyzer; it looks that a certain date is between the start and end date of a certain value.  I also have a status field, which can be null, but if provided, provides the appropriate status to filter by.
Again, the query works in QA, but not in the application.  I test in SQL by using start date = '1/1/1900', end date = '12/31/9999', and status = null.  Results are returned.  But, not when the results are done through code.  In code, I set the begin date to new DateTime(1900, 1, 1), the end date to DateTime.MaxValue, and the status to a null string.  But, no results are returning.  Why isn't that mapping over correctly?  In the function, it has the two dates as Nullable(Of DateTime), which I provide a date, and the string is getting passed Nothing.
Any ideas?  Can't post any code on this one...
Thanks.

View 5 Replies View Related

Pass Null To Stored Proc In Sql Server Mgmt

Jan 18, 2008

I'm in sql server mgmt 2005 and i highlighted a stored proc and clicked on "execute". I need to pass a null value in as the first param value to test this stored proc. what is the syntax, is below correct?

EXECUTE @RC = [SomeDataBaseName].[dbo].[sp_SomeStoredProcedure]
null
,'1234'
,1

View 3 Replies View Related

Select Statement Returns Null In Stored Proc

Feb 22, 2006

If I run this statement in Query Analyzer, it properly returns 1for my testing table. But if I put the statement into a storedprocedure, the stored procedure returns NULL. What am I doingwrong? I suspect it may be related to how I defined the parametersfor the stored procedure. Perhaps my definition of TableName andColumnName don't match what COLUMNPROPERTY and OBJECT_ID expect toreceive, but I don't know where to look for the function declarationsfor those. Any pointers would be appreciated.Select statement:SELECT COLUMNPROPERTY(OBJECT_ID('Table1'), 'TestID', 'IsIdentity') ASIsIdentityTable definition:CREATE TABLE [dbo].[Table1] ([TestID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]Stored Procedure definition:CREATE PROCEDURE spTest(@TableName varchar,@ColumnName varchar)AS SELECT COLUMNPROPERTY(OBJECT_ID(@TableName), @ColumnName,'IsIdentity') AS IsIdentity

View 2 Replies View Related

Returning Hours A Call Was Open

Apr 24, 2008

What I am trying to do is look at a 30 day history of calls that were closed and find (in hours) how long the work orders were open. My major problem is working with dates, the results I'm getting appear to be inaccurate. My Code is as follows:


Code:

SELECT C.Priority, MIN(CONVERT([INT], DATEPART(hh, T1.CalcDate), 120)) AS Minimum, MAX(CONVERT([INT], DATEPART(hh, T1.CalcDate), 120))
AS Maximum
FROM HEAT.dbo.CallLog C, HEAT.dbo.Profile P,
(SELECT CallID, CONVERT(datetime, ClosedDate + ' ' + ClosedTime, 120) - CONVERT(datetime, RecvdDate + ' ' + RecvdTime, 120)
AS CalcDate
FROM HEAT.dbo.CallLog C1
WHERE (CONVERT(datetime, CONVERT([INT], GETDATE(), 120) - 30, 120) >= ClosedDate)) T1
WHERE C.CustID = P.CustID AND C.CallID = T1.CallID AND (C.CallStatus = 'Closed') AND (P.CustType <> 'services') AND (P.PrimarySupportGroupID = 'CTS') AND
(C.Priority IN ('1', 'E1', 'E2')) AND (CONVERT([INT], T1.CalcDate, 1) >= 0)
GROUP BY C.Priority
ORDER BY C.Priority



If anyone could give me any suggestions or help me out I would greatly appreciate it. The results are used to draw a bar chart of the information.

Thank you
Court

View 2 Replies View Related







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