Calling SP

Sep 14, 2007

Hi,
Can I call SP in the Trigger? Googled for this but din gt any satisfactory answer.




Thnkx,
Rahul Jha

View 13 Replies


ADVERTISEMENT

Calling DTS From C#

Oct 25, 2006

Hi all, i have a DTS package that i call from a C# app, i had it working great until i decided to use an activeX script to do the data transformations instead of the row copy.  I need to use ActiveX to add a standard name to the last column in the destination table.  the problem is the task is executing without errors (from c#) but nothing is happening, its failing silently.  If i modify the Data Transformation back to a standard column mapping (with separate DTSTransformations for each column) it works fine, but as soon as i use activeX to handle the transformations it doesn't work.  Can anyone tell me what i may be doing wrong. heres the calling code from C# if(f.Name.Substring(13,7).ToLower() == "product")
{
try
{
activity.Log("Starting Product DTS Package...");
DTS.Package2Class package = new DTS.Package2Class();
object pVarPersistStgOfHost = null;
package.LoadFromSQLServer(
"192.168.8.8",
"username",
"thepassword",
Microsoft.SqlServer.DTSPkg80.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default,
null,
null,
null,
(string)ConfigurationSettings.AppSettings["productDTSPackage"],
ref pVarPersistStgOfHost);
package.GlobalVariables.Item(1).Value = f.FullName.ToString();
package.Execute();
package.UnInitialize();
//force release of COM object
System.Runtime.InteropServices.Marshal.ReleaseComObject(package);
package = null;
}
catch(Exception e)
{
activity.Log(string.Format("Failureprocessing {0}",WorkingPath + f.Name) +" - "+ e.Message);
} and the activex i tried to use for the transformations is: '**********************************************************************'  Visual Basic Transformation Script'************************************************************************'  Copy each source column to the destination columnFunction Main() DTSDestination("Yesmail Id") = DTSSource("product series") DTSDestination("Customer CKM CustId") = DTSSource("product family") DTSDestination("Product SKU") = DTSSource("transaction date") DTSDestination("product model name") = DTSSource("serial number") DTSDestination("serial number") = DTSSource("product model name") DTSDestination("transaction date") = DTSSource("Product SKU") DTSDestination("product family") = DTSSource("Customer CKM CustId") DTSDestination("product series") = DTSSource("Yesmail Id") DTSDestination("fileName") = DTSGlobalVariables("sourceFile").Value  <-- this is why im using the activex for this field to get a Global Variable.  Main = DTSTransformStat_OKEnd Functionthe weird thing is the PACKAGE runs fine from ENTERPRISE MANAGER with this activex, it just doesn't do it from my Calling app, perhaps i have missed something i need to change in the package constructor? BTW: i do have my assembly signed in C# for the COM wrapper.thanks in advance, mcm

View 1 Replies View Related

Calling A SP

Jan 6, 2008

Hello,
I have done extensive work with Classic ASP for 9 years now. Working with Stored Proceedures etc
However now am working in c# am adviced by a friend that its best to use the
1. The "Object data source" to call SP
2. Are there any documentations pointers on best practices, how its done ?
I want to use the "on click event" in my class file.
thanks
Ehi

View 3 Replies View Related

Calling A SP In .net

Jan 9, 2008

Hello,
are there any sample codes that show you how to execute a SP in .net
1. Using a class
2. Calling the class in the onlick_button function ?
thanks
Ehi

View 1 Replies View Related

Calling SP From SP

Jan 20, 2004

I'm developing a new Stored Proc that will be taking information in to enter customer and order info. Looking at the current Stored Proc its using alot of If statements and I'm thinking of breaking the sp up into different Stored Procs and calling them all from One main stored proc. I know how to do this but I was wondering how it would effect performance. So should I simply keep all the stuff in one proc or module it out into multiple ones to make it easier to follow and read?

Thanks

View 1 Replies View Related

Example Of A Sp_ Calling Another Sp_.

Sep 18, 1998

Does anyone have an example of a Stored Procedure calling
another stored procedure and passing it parms.

What I would like to do is: Have a stored procedure select
data from a table and pass that data to another stored
procedure.


Thanks in advance,

Rodney

View 3 Replies View Related

Calling One SP From Another SP

Mar 26, 2008

I'm working in SQL Server 2005. I have an existing SP that does exactly what I need (it's the aspnet_UsersInRoles_IsUserInRole SP). I want to reuse this SP and use it's return value as a field in the SP I'm writing. How do I go about doing this? I could take the logic out of the called SP and wrap it in a function; but, I would really like to reuse the SP aspnet_UsersInRoles_IsUserInRole. Thanks in advance for any assistance.

View 7 Replies View Related

Calling A Dts Package From VB.NET

Jan 8, 2007

hi,
i'm trying to call a dts package from vb.net.
i got 2 examples which both don't work.
first one gives me a [DBNETLIB][ConnectionOpen(Connect()).]SQL Server does not exist or access denied error.
source code:Dim serverName As String = "SERVERNAME"Dim oPackage As New DTS.Package()Dim oStep As DTS.StepDim pVarPersistStgOfHost As Object = Nothing
oPackage.LoadFromSQLServer(serverName, "USERID", "PASSWORD",     DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, _  "DTSPASSWORD", Nothing, Nothing, "DTSPACKAGENAME", pVarPersistStgOfHost)
For Each oStep In oPackage.Steps      oStep.ExecuteInMainThread = TrueNext
oPackage.Execute()
Dim err As LongDim source, description, message As StringFor Each oStep In oPackage.StepsIf oStep.ExecutionResult = DTSStepExecResult.DTSStepExecResult_Failure Then        oStep.GetExecutionErrorInfo(err, source, description)        message = String.Format("ErrorCode: {0}, Source: {1}, Description: {2}", err.ToString(), source,                              description)Else         message = "Success"End IfNext
oPackage.UnInitialize()oPackage = Nothing
second example tries to create a dts package dynamically. this time i get the error that the CustomTask can not be casted, somehting about the QueryInterface.
source code:Dim oPackage As New Package2()Dim oConnection As Connection2Dim oStep As Step2Dim oTask As TaskDim oCustomTask As BulkInsertTask
oConnection = oPackage.Connections.[New]("SQLOLEDB")oStep = oPackage.Steps.[New]()oTask = oPackage.Tasks.[New]("DTSBulkInsertTask")oCustomTask = CType(oTask.CustomTask, BulkInsertTask) <-- error
With oConnection       .Catalog = "CATALOG"       .DataSource = "SERVERNAME"       .ID = 1       .UseTrustedConnection = True       .UserID = "USERID"       .Password = "PASSWORD"End With
oPackage.Connections.Add(oConnection)oConnection = Nothing
With oStep        .Name = "InsertGemal"        .ExecuteInMainThread = TrueEnd With
With oCustomTask        .Name = "InsertGemal"        .DataFile = "D:ImportGemal.dat"        .ConnectionID = 1        .DestinationTableName = "Gemal"        .FieldTerminator = ";"        .RowTerminator = "
"End With
oStep.TaskName = oCustomTask.Name
With oPackage        .Steps.Add(oStep)        .Tasks.Add(oTask)        .FailOnError = TrueEnd With
oPackage.Execute()
oPackage.UnInitialize()oPackage = Nothing
any help highly appreciated!
t.i.a.,ratjetoes.

View 3 Replies View Related

Calling SQL Function In C#

Feb 22, 2007

Hello,
I am trying to call a SQL Function in C#, as I want to get the value binded back to the column in a datagrid.
 
Any answers will be appreciated ...............Urgent.
 
:)

View 4 Replies View Related

Calling A SP In A Class

Jan 17, 2008

Hello,
I have the class below. And trying to execute it on a button click event.
What am i doing wrong ?
Thanks
Here is the button click event1 protected void Button1_Click(object sender, EventArgs e)
2 {
3 signup_data_entry signup = new signup_data_entry();
4 signup.signup_data_entry();
5
6 }

  
Here is my class file. please advice
 1 public class signup_data_entry
2 {
3 public signup_data_entry()
4 {
5 //SqlConnection con = new SqlConnection("cellulant_ConnectionString");
6 SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["cellulant_ConnectionString"]);
7
8 SqlCommand command = new SqlCommand("Cellulant_Users_registration", con);
9 command.CommandType = CommandType.StoredProcedure;
10
11 con.Open();
12
13 //string IP = new string();
14
15
16 command.Parameters.Add(new SqlParameter("@RegionID", SqlDbType.Int, 0, "RegionID"));
17 command.Parameters.Add(new SqlParameter("@RegionDescription", SqlDbType.NChar, 50, "RegionDescription"));
18
19 command.Parameters[0].Value = 4;
20 command.Parameters[1].Value = "SouthEast";
21
22 int i = command.ExecuteNonQuery();
23
24
25 }
26 }

 

View 6 Replies View Related

Calling The SP From ASP.NET Application

Feb 8, 2008

Hi All,
 
I have problem in calling the SP from ASP.NET application 
 @DefApp nvarchar(255)= '' ,@DefBusFunction nvarchar(255)= '' ,@DefImpact nvarchar(255) = '',AS Begin declare @sql nvarchar(4000)declare @whereClause nvarchar(4000)DECLARE     @return_value intdeclare @sqlWhere nvarchar(4000) select @sql = 'SELECT DefApp, DefBusFunction, DefImpact FROM Def LEFT JOIN ZFunction ON (def.DefApp = ZFunction.App) AND (Def.DefBusFunction = ZFunction.BusFunction)' if @DefImpact <> ''      Set @whereClause = ' where DefImpact '      SET @whereClause = @whereClause + ' = ''' + @DefImpact + '''' set @sqlWhere=@sql + @WhereClauseEXEC @sqlWhereEnd 
and i am calling this SP from my ASP.net application to fill the SSRS report. I have written code in ASP.NET like:
 sqlCmd = new SqlCommand("subbusample", conn);
sqlCmd.CommandType = CommandType.StoredProcedure;sqlCmd.Parameters.Add(new SqlParameter("@DefImpact", SqlDbType.NVarChar, 255, txtValue3.Text.ToString()));
sqlCmd.Parameters.Add(new SqlParameter("@DefBusFunction", SqlDbType.NVarChar, 255, txtValue1.Text.ToString()));
sqlCmd.Parameters.Add(new SqlParameter("@DefApp", SqlDbType.NVarChar, 255, txtValue2.Text.ToString()));
 RptViewer.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote;
RptViewer.ServerReport.ReportServerUrl = new System.Uri("http://servername/ReportServer");RptViewer.ShowParameterPrompts = false;
RptViewer.ServerReport.ReportPath = "/folder name/Subbu_Sample";
 
when i execute it, i am not able to fill the report with data.
 
Please let me know what i am doing wrong.
 
Thanks,
SR.

View 6 Replies View Related

Calling Asynchronously

Mar 7, 2008

In my shared hosting environment where I am making large volumes of database calls, I am very anxious to preserve my heavily throttled threads-I presume when I make a database call, that it is going to a separate server and any threads that server may be using are in addition to my " hosting plan ".
My question (s) is, is it therefore a no-brainer to always make calls to:Dim oResult As IAsyncResult = iDBCmd.BeginExecuteReader()arWaitHandles(0) = oResult.AsyncWaitHandleiIndex = WaitHandle.WaitAny(arWaitHandles, 60000, False)
i.e. run all calls to the database asynchronously?
Is it really releasing a thread back to my asp.net App while it is waiting? (I.e. Is it worth doing)
 

View 4 Replies View Related

Help Calling DTS Package.

Aug 18, 2004

I'm new to using DTS packages and I'm running into a problem. Hopefully someone can help me out. I have an ASP.NET page that needs to call a DTS package. Everything seems to be working right up until I call opkg.Execute. The package seems to run but nothing happens. After further investigation I have found what looks like a permissions issue. this is the message I'm getting"
Error Number:
-2147467259 {Integer}

Error Description:
"The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data." {String}

This is obviously a permissions issue. I'm just not sure who the DTS package is running as and where to set the permissions.

Any ideas????

Thanks

View 1 Replies View Related

Calling A DTS Package From An Sp

Aug 31, 2001

How to do this?thanks :-)

View 1 Replies View Related

Calling A Dts Package From VB

Sep 15, 2000

Does anyone know the command that can be used to call a dts package directly from VB?

regards,
reshma

View 1 Replies View Related

Calling SP With Parameters

Jun 3, 2000

Hello,

What is the syntax of calling stored procedure from vb

Is there any1 can please give me a example of how to call sp with passing parameters, thanks a lot.




Thanks

View 1 Replies View Related

Calling DTS Package

May 27, 2004

I have a DTS package created in a databasse and need to call that DTS package from a stored procedure.Is there a way that I can do it?

View 2 Replies View Related

Rollback Of Sp Calling Another Sp

Mar 10, 2006

If you have a begin tran in a sp that calls another sp and that called sp fails. can you execute a roll back from the called sp, or should it return and error which will roll back the begin tran in the original sp

View 1 Replies View Related

Calling A DTS Package From VB

Mar 22, 2002

Hi all,
I want to call dts package from visual basic. The dts package will do a backup of the database.
I am not sure how this can be achieved.
Any help will be great
Thanks

View 1 Replies View Related

Calling All GURUS ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! !

May 19, 2002

SQL Server Load Balancing -- Any ideas?

There is no inherent mechanism available in SQL Server (replication, log shipping, or clustering) which allows you to load balance you database server.
Clustering is only useful for a failover situation and does not allow active/active balancing.

Is is possible to use merge replication between two identical OLTP servers
and manage transactions via MSMQ? Will this mechanism allow for a load balanced OLTP server?

Will this work? If not, why not?
What will work? Will federated servers work for an entire database??

View 1 Replies View Related

Calling Dll`s/exe`s From SQL Server

Oct 5, 1998

I need a simple example of calling a dll or exe with params
from a stored procedure.

Thanks
Robert Kapciuk

View 1 Replies View Related

Calling SP Inside The SP

Jul 13, 2004

hai guys

how should we have to cal the store procedure inside the same store procedure.

for Example

Create procedure A
as
Begin
Select * from mytable
execute A
end.

is this the correct one

View 3 Replies View Related

Calling Sp From DTS On Different Server

Sep 16, 2004

Is it possibe to call a Stored Proc on a remote server in a DTS package?

Thanks in advance,

John

View 3 Replies View Related

Calling Functions

Nov 23, 2004

Dear Everybody,

Hi again!

Supposing I have an SQL Server Function named Fxn1 which returns a VARCHAR(6) value. I want to use this returned value in another function named Fxn2 but Fxn2 accepts a parameter of type INT. Fxn2 itself returns an INT value. This has to be achieved in a single line.

The following line of code will not work in SQL Server. How should I achieve this?

declare @Res INT
EXEC @Res = Fxn2 (CONVERT( INT, Fxn1 ))

Thanx in advance!

View 14 Replies View Related

Calling VBA Functions From SQL

Mar 16, 2006

I have a rather complex function (part of a production planning engine) that is written in VBA, and is part of my front end app. I'd like to be able to somehow call this function from either a sproc or DTS package in SQL. Is this possible, or am I going to have to convert the function to a SQL sproc (ugh)?

View 2 Replies View Related

Calling Vbscript From SP

Apr 21, 2004

I am trying to call a VB script file with the following command,

xp_cmdshell 'cscript c:file.vbs'

But its giving the following output -

Microsoft (R) Windows Script Host Version 5.1 for Windows
Copyright (C) Microsoft Corporation 1996-1999. All rights reserved.
NULL
Input Error: Can not find script file "c:file.vbs".
NULL
------------
If i changed the command to

xp_cmdshell 'cscript c:file.vbs'

it gives the folowing error -

Input Error: Can not find script file "C:WINNTsystem32file.vbs".


Actually my main intention is to call a url from the sql server where '
i can pass some value. Plz let me know if its the right step and whts
going wrong.

Thanks & regards,

-dkoushik

View 4 Replies View Related

Calling A Procedure

Apr 8, 2008

create procedure t1 (@var int output)
as
begin
set @var =3
end


declare @var int
execute t1 @var output
print @var


the output is ok

but i need it like

execute t1 @var output i should get value

View 1 Replies View Related

Calling A SP Within A SP And Back

Aug 24, 2007

I was wondering if there was a way to call a stored procedure within a stored procedure, and after that's done, it goes back where the other stored procedure left off? I was also hoping to return a value also. Thank you.

View 3 Replies View Related

Calling Exe Using Xp_cmdshell.

Nov 29, 2007

I have is a .exe file developed on vb.net. I'm able to run it on QA as exec master..xp_cmdshell 'D:maxCustomMX_SyncAddressBook.exe, then if I run it using a SQL job it shows the error:
Executed as user: CAPERSQLAdmin. The process could not be created for step 1 of job 0x9E75D06B9FC4474EB7503A1CCA0F9AAF (reason: The system cannot find the file specified). The step failed.
Anyone familiary with sql jobs calling .exe file developed on vb.net. Your help would be appreaciated.




http://www.sqlserverstudy.com

View 7 Replies View Related

Calling A Function.

Feb 26, 2008

I'm calling a function with its full qualifiers.
That means servername.dbname.schemaname.functionname

I'm getting an error like this
Invalid column name 'servername'.

Is it possible to call a function name with its server name?


Thanks
Somu

View 4 Replies View Related

Help With Calling A Job From Stored P And VBA......HELP

Sep 12, 2005

I would like to know if it is possible to start a job from a storedprocedure?I have a DTS that I set as a job and would like to either call it froman ADP withConn.Execute "EXEC msdb..sp_start_job @job_name = 'Volusia'"OR just strat it with a stored procedure and call the stored procedurefrom the adpCREATE PROCEDURE sde.Volusia_Import ASEXEC msdb..sp_start_job @job_name = 'Volusia_Import'GOI tried both of these and it does not give me an error but it does notrun the job... what am I missing?Thanks,Chuck

View 2 Replies View Related

Q: Calling All Gurus

Jul 20, 2005

The Scenario:We have some data that can be in three states. One is a saved state,a temporary state, and a complete state.The complete state is the bulk of the data. This data will berequested 1% of the time, maybe even less.The saved state and temporary state will only number 10-15 records.This data will be used 99% of the time, very frequently.So to summarize there will be 10-15 records that will be used ALL thetime…. And 50,000-100,000 that will be used very infrequently.The programmer that I work with wants to combine the tables. I wantto keep the tables sepperate.My logic is that it will be much faster to work with 10-15 recordsthan sift threw 50-100k most of which are unnecessary to 99% of therequests. Also there are frequent deletions/modifications in thetemporary table(10-15 records). The complete table can NOT havemodifications or deletions (50-100k records). The optimization forthese tables is also very different.He says that since the structures are almost identical they should bein the same table, that we should just use a field to denote thedifferent types of records. He insists that my view is wrong and thatI know nothing about databases.I may be new to SQL, but am I really that out to lunch? Would it notmake sense to keep these tables separate?(Thank you for taking the time to read this)Sat

View 14 Replies View Related

How To List All SPs That Are Calling A SP?

Apr 2, 2008



I already post one thread to list all SPs that are starting with sp_.
I found 150 SPs on average for each database.
The next step in my assignment is to find all SP's that are using these SPs.

Here is an example. Lets say I have a procedure that is 'sp_appname' I need to find out all SPs that are using this SP.
For this I wrote following statement.


SELECT name from sysobjects so INNER JOIN syscomments sc

ON so.id=sc.id

WHERE sc.text LIKE '%sp_appname%' AND so.type='P'


Let me know the the above statement will give accurate result. If anybody can make this statement more efficient ....Let me know.

Hope you reply soon.

Thanks,
Pradeep

View 2 Replies View Related







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