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 stored
procedure?
I have a DTS that I set as a job and would like to either call it from
an ADP with
Conn.Execute "EXEC msdb..sp_start_job @job_name = 'Volusia'"
OR just strat it with a stored procedure and call the stored procedure
from the adp
CREATE PROCEDURE sde.Volusia_Import AS
EXEC msdb..sp_start_job @job_name = 'Volusia_Import'
GO
I tried both of these and it does not give me an error but it does not
run the job... what am I missing?
Thanks,
Chuck
View 2 Replies
ADVERTISEMENT
Dec 18, 2007
Hi Peeps
I have a SP that returns xml
I have writen another stored proc in which I want to do something like this:Select FieldOne, FieldTwo, ( exec sp_that_returns_xml ( @a, @b) ), FieldThree from TableName
But it seems that I cant call the proc from within a select.
I have also tried
declare @v xml
set @v = exec sp_that_returns_xml ( @a, @b)
But this again doesn't work
I have tried changing the statements syntax i.e. brackets and no brackets etc...,
The only way Ive got it to work is to create a temp table, insert the result from the xml proc into it and then set @v as a select from the temp table -
Which to be frank is god awful way to do it.
Any and all help appreciated.
Kal
View 3 Replies
View Related
Jul 20, 2005
Hi all,I have a stored procedure that return a resultsete.g. stored proc: get_employee_detailsselect emp_id, emp_name, emp_salary, emp_positionfrom empoloyeeI would like to write another stored procedure that executes the abovestored procedure - returning the same number of records but it willonly show 2 columnse.g. new stored proc: get_employee_pay -- executesget_employee_detailsI only need to know emp_id, emp_salary.How can this be done in sql stored procedure?Thanks,June Moore.
View 2 Replies
View Related
May 8, 2008
I am writing a set of store procedures (around 30), most of them require the same basic logic to get an ID, I was thinking to add this logic into an stored procedure.
The question is: Would calling an stored procedure from within an stored procedure affect performance? I mean, would it need to create a separate db connection? am I better off copying and pasting the logic into all the store procedures (in terms of performance)?
Thanks in advance
John
View 5 Replies
View Related
Nov 1, 2007
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
View 1 Replies
View Related
Mar 3, 2008
Hi all,
I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):
(1) /////--spTopSixAnalytes.sql--///
USE ssmsExpressDB
GO
CREATE Procedure [dbo].[spTopSixAnalytes]
AS
SET ROWCOUNT 6
SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName
FROM LabTests
ORDER BY LabTests.Result DESC
GO
(2) /////--spTopSixAnalytesEXEC.sql--//////////////
USE ssmsExpressDB
GO
EXEC spTopSixAnalytes
GO
I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class
///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance,
Scott Chang
More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
View 11 Replies
View Related
Mar 23, 2007
Hi, i've had this query method:
34 public void AddDagVerslagCategorie(int logID, HistoriekDetail historiekDetail)35 {36 SqlConnection oConn = new SqlConnection(_connectionString);37 string strSql = "Insert into LogDetail (LogID, CategorieID, Inhoud)";38 strSql += "values(@logID, @categorieID, @inhoud)";39 SqlCommand oCmd = new SqlCommand(strSql, oConn);40 oCmd.Parameters.Add(new SqlParameter("@logID", SqlDbType.Int)).Value = logID;41 oCmd.Parameters.Add(new SqlParameter("@categorieID", SqlDbType.Int)).Value = historiekDetail.CategorieID;42 oCmd.Parameters.Add(new SqlParameter("@inhoud", SqlDbType.VarChar, 100)).Value = historiekDetail.Inhoud;43 44 try45 {46 oConn.Open();47 int rowsAffected = oCmd.ExecuteNonQuery();48 if (rowsAffected == 0) throw new ApplicationException("Fout toevoegen historiek detail");49 oCmd.CommandText = "select @@IDENTITY";50 oCmd.Parameters.Clear();51 historiekDetail.HistoriekDetailID = (int)(decimal)oCmd.ExecuteScalar();52 }53 catch (Exception ex)54 {55 throw new ApplicationException("Fout toevoegen historiek detail: " + ex.Message);56 }57 finally58 {59 if (oConn.State == ConnectionState.Open) oConn.Close();60 }61 }
which i've converted to a stored procedure: 1 ALTER PROCEDURE [dbo].[insert_DagVerslagDetail]
2 -- Add the parameters for the stored procedure here
3 @dagverslagdetailID int,
4 @logID int,
5 @categorieID int,
6 @inhoud varchar(100)
7 AS
8 BEGIN
9 -- SET NOCOUNT ON added to prevent extra result sets from
10 -- interfering with SELECT statements.
11 SET NOCOUNT ON;
12 SET @dagverslagdetailID = SCOPE_IDENTITY()
13
14 -- Insert statements for procedure here
15 BEGIN TRANSACTION
16 INSERT LogDetail (LogID, CategorieID, Inhoud)
17 VALUES(@logID, @categorieID, @inhoud)
18 COMMIT TRANSACTION
19 END
Now i would like to call that stored procedure in my previous method, so i've changed it to this:
1 public void AddDagVerslagCategorie(int logID, HistoriekDetail historiekDetail)
2 {
3 SqlConnection oConn = new SqlConnection(_connectionString);
4 string strSql = "insert_DagVerslagDetail";
5 strSql += "values(@logID, @categorieID, @inhoud)";
6 SqlCommand oCmd = new SqlCommand(strSql, oConn);
7 oCmd.CommandType = CommandType.StoredProcedure;
8 oCmd.Parameters.Add(new SqlParameter("@logID", SqlDbType.Int)).Value = logID;
9 oCmd.Parameters.Add(new SqlParameter("@categorieID", SqlDbType.Int)).Value = historiekDetail.CategorieID;
10 oCmd.Parameters.Add(new SqlParameter("@inhoud", SqlDbType.VarChar, 100)).Value = historiekDetail.Inhoud;
11
12 try
13 {
14 oConn.Open();
15 int rowsAffected = oCmd.ExecuteNonQuery();
16 if (rowsAffected == 0) throw new ApplicationException("Fout toevoegen historiek detail");
17 oCmd.CommandText = "select @@IDENTITY";
18 oCmd.Parameters.Clear();
19 historiekDetail.HistoriekDetailID = (int)(decimal)oCmd.ExecuteScalar();
20 }
21 catch (Exception ex)
22 {
23 throw new ApplicationException("Fout toevoegen historiek detail: " + ex.Message);
24 }
25 finally
26 {
27 if (oConn.State == ConnectionState.Open) oConn.Close();
28 }
29 }
Do i still need the lines 17 oCmd.CommandText = "select @@IDENTITY";
19 historiekDetail.HistoriekDetailID = (int)(decimal)oCmd.ExecuteScalar();
Because i've declared the identity in my stored procedure
View 1 Replies
View Related
Jul 1, 2007
Hi All... I'm calling a stored procedure from C#. I'm sending it input parameters as follows:SqlCommand c = new SqlCommand("AddAuthor", myConnection);c.CommandType = CommandType.StoredProcedure;
c.Parameters.Add(new SqlParameter("@LastName", SqlDbType.VarChar, 100));c.Parameters["@LastName"].Value = "Last";
c.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.VarChar, 100));c.Parameters["@FirstName"].Value = "First";
In my opinion, that works nice - and it's easy. The reference I'm using says I can also specify "output" parameters - instead of supplying a value, one needs to change a property called direction.
But my stored procedure doesnt return any output parameters per se, but it does return a value. That is the last statement in the stored procedure is "RETURN @@IDENTITY" - it returns the identity field after an INSERT... So how do I get that value back in my C# code?
Thanks for the help in advance. Happy 4th to all! : ) -- Curt
View 4 Replies
View Related
Jan 16, 2008
Hi,
I was wondering if someone can tell me why my sub for calling stored procedures doesn't work.The string strFinFileId is not null.
I keep getting the following error System.IndexOutOfRangeException: Total
Here's my sub.
'//get GrandTotal Protected Sub GetGrandTotal(ByVal finfileid As String) Dim myConnection As SqlConnection Dim connString As String Dim strFinFileId As String Dim strGrandTotal As Decimal
strFinFileId = finfileid connString = ConfigurationManager.ConnectionStrings("PMOConnectionString1").ConnectionString
myConnection = New SqlConnection(connString) 'you need to provide password for sql server myConnection.Open()
Dim sql1 As String
sql1 = "GetGrandTotal " & Convert.ToInt32(strFinFileId)
Dim myCommand As SqlCommand
'Response.Write(sql1) 'Response.End()
myCommand = New SqlCommand(sql1, myConnection)
Dim reader As SqlDataReader = myCommand.ExecuteReader() While reader.Read() strGrandTotal = reader.Item("Total") LabelGrandTotal.Text = strGrandTotal 'Response.Write(strFinFileId) 'Response.End()
End While reader.Close() myConnection.Close() End Sub
Cheers
Mark :)
View 2 Replies
View Related
Apr 8, 2008
Hi, I have a stored procedure, and it is expecting an output. If I declared the passing varaible as ref, it compiles fine, but it is not returning any value. If I pass the varaible as out, and add the paramater
MyComm.Parameters.Add(new SqlParameter("@ReturnValue", returnValue)); it gives the following error.
Compiler Error Message: CS0269: Use of unassigned out parameter 'quoteID'.
And if I don't supply the previous statement, the following error occurs.
System.Data.SqlClient.SqlException: Procedure 'CreateData' expects parameter '@ReturnValue', which was not supplied.
How Can I fix this? thanks.
View 2 Replies
View Related
Apr 15, 2008
For a new web project I am working on I wrote a couple of Stored Procs in C#. I have deployed them to the database, and can run them fine using SQL Mgmt Studio. The problem is that when I try and run them from my application, I get a 'The Stored Procedure 'ProcName' does not exist' error message. I orignally thought it might be a permissions issue, so I changed my connection string to connect as 'sa' and it still got the same error. I am using the Microsoft.Practices.EnterpriseLibrary to connect to the database. Any ideas on what I need to do to get this to work?
View 1 Replies
View Related
Apr 16, 2008
I have a stored procedure written to update a table:
The stored procedure has the following parameters:
@Original_TypeID int,
@Type_Desc varchar(35),
@Contact_Name varchar(20),
@Contact_Ad1 varchar(25),
@Contact_Ad2 varchar(25),
@Contact_City varchar(10),
@Contact_Phone varchar(12),
@Contact_Fax varchar(12),
@Contact_Email varchar(35)
And I want to call this procedure when the "update" button is clicked, my code is:Protected Sub UpdateButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("myConnectionString").ConnectionString)Dim myCommand As SqlCommandDim UpdteParam As New SqlParameter()
myConnection.Open()myCommand = New SqlCommand("[dbo].[sp_Update_Types]", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
????????????????????????myCommand.Parameters.Add(UpdateParam)
???????????myCommand.ExecuteNonQuery()
myConnection.Close()
End Sub
Can you please help me with the ????? areas? I don't know how to pass the parameters into the procedure, and also the second set of ??? s, I'm not sure if I am executing the command correctly.
Thank you.
View 3 Replies
View Related
Dec 15, 2003
I have a stored procedure that calls a DTS package to grab a text file that has been uploaded to the server and merge it with a table on the database. The DTS package works woderfully in SQL, as does the the file upload. The problem arrises when I create a stored procedure to run the DTS package. I know that you have to shell out and do a command line on the SQL server (and I think that I got the syntax correct) but its calling the Stored Procedure in the ASP.NET app that is causing me hardship. Here is the code that I have so far:
Stored Procedure:
CREATE PROCEDURE spSampleData AS exec master..xp_cmdshell 'dtsrun /SZEUSsqlServer113 /NdtsPackage /UuserID /Ppassword'
GO
VB to run DTS:
Dim myCommand As SqlCommand
myCommand.CommandType = CommandType.StoredProcedure
myCommand.CommandText = "spSampleData"
myCommand.ExecuteNonQuery()
I'm not sure what I am doing wrong but any help would be great.
Thanks!
View 3 Replies
View Related
Feb 6, 2004
I am trying to set up a call to a Stored Procedure to do an Insert. Here is my code snippet:
<%@ Page Language="VB" %>
<%@ import Namespace="System" %>
<%@ import Namespace="System.Data.SqlClient" %>
.
.
.
Dim loConn as New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim cmdInsert as New SQLCommand("AdminUser_Insert", loConn)
cmdInsert.CommandType = CommandType.StoredProcedure
Dim InsertForm As New SqlDataAdapter()
InsertForm.InsertCommand = cmdInsert
cmdInsert.Parameters.Add(New SqlParameter("@RETURN_VALUE", SqlDbType.bigint, 8, "Account_Number"))
cmdInsert.Parameters("@RETURN_VALUE").Direction = ParameterDirection.ReturnValue
cmdInsert.Parameters.Add(New SqlParameter("@UserID", SqlDbType.varchar, 50, "UserID"))
cmdInsert.Parameters("@UserID").Value = Request("UserID")
cmdInsert.Parameters.Add(New SqlParameter("@Password", SqlDbType.varchar, 32, "Password"))
cmdInsert.Parameters("@Password").Value = Request("Password")
cmdInsert.Parameters.Add(New SqlParameter("@First_Name", SqlDbType.varchar, 32, "First_Name"))
cmdInsert.Parameters("@First_Name").Value = Request("FirstName")
cmdInsert.Parameters.Add(New SqlParameter("@Middle_Name", SqlDbType.varchar, 32, "Middle_Name"))
cmdInsert.Parameters("@Middle_Name").Value = Request("MiddleName")
cmdInsert.Parameters.Add(New SqlParameter("@Last_Name", SqlDbType.varchar, 32, "Last_Name"))
cmdInsert.Parameters("@Last_Name").Value = Request("LastName")
loConn.Open()
command.ExecuteNonQuery()
loConn.Close()
I get the following error:
Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.
Compiler Error Message: BC30451: Name 'CommandType' is not declared.
This error happens on the line: cmdInsert.CommandType = CommandType.StoredProcedure
Any help would be appreciated,
Greg
View 2 Replies
View Related
May 22, 2000
Hello everybody,
How can I call DTS from stored procedure, any help pls.
PS: I appreciate if you please give me an example.
Thank you....
View 2 Replies
View Related
Jan 19, 2001
I'm trying to call a job from a stored procedure.
To do so, I've found that sp_start_job is doing just that.
My problem is that the sp_start_job is not in my master
database stored procedures and I don't know how to add it.
I'm working with SQL Server 7 Enterprise without the SP.
Am I looking for the right thing ? (sp_start_job)
Where can I find it ?
View 1 Replies
View Related
Jan 8, 2003
I need to call a DLL from with one of my stored procedures. How do I achieve this?
The DLL is currently on App server (Not on the SQL Server box). Do I have to register this DLL on my SQL server, create an extended stored proc and call it from my stored proc?
Please throw some light on this topic.
Thanks,
Varma
View 3 Replies
View Related
Jun 21, 2001
I'm relatively new to SQL Server and I was wondering if there is any way to call a DLL from a regular stored procedure? I believe I've read that the extended stored procedure is a DLL and therefore should be able to call another DLL. Can this also be verified? We're looking to speed up some sections of Visual Basic code which does some heavy I/O work. I've also asked this once before but didn't get an absolute answer - can you write the extended stored procedures in Visual Basic or is C/C++ the only language supported right now?
Thanks.
Caroline Kaplonski
ckaplonski@buckconsultants.com
View 2 Replies
View Related
Nov 3, 2005
Hey, I have a parent SP, and within that parent I want to call a a child what is the code to call that child procedure? or teh easiest way to make that happen?
View 1 Replies
View Related
Apr 24, 2007
How can I find what is calling a stored proc in a database or on the server?
Currently a job runs daily that calls a couple of DTS packages. Somewhere in this process a stored proc is being called that pumps out several emails. BUT nowhere in the DTS or job can I find any reference to the stored proc.
View 3 Replies
View Related
Sep 17, 2007
I would like to know how to call a Stored Proc with in code. Have an SQL code but need data from a table that needs to be updated by a Stored Proc. Would need to call proc in the middle of code to get the correct data. Just not sure of what the statement looks like to call the proc. Thank you for your help. David (DHL)
View 9 Replies
View Related
Oct 16, 2007
yet another question unfortunately
I have now created a stored proceedure that has a return parameter, not i am unsure how to call it from another proceedure,
ie
say i have select projectid, project name from projects into temp from projects.
how can i then loop around all the rows in temp, to call my stored proceedure for each record?
in vb i would have created a function like my stored proceedure, then picked up a recordset, looped around it and picked up the return value for each row.
can this be done for sql?
I am trying to do something like
for each record in #temp (projectid, project name) find the stored sprceedure value
so my end result will look like
projectid, project name, @storedproceedure return value
lprojectid, project name, @storedproceedure return value
projectid, project name, @storedproceedure return value
projectid, project name, @storedproceedure return value
any help appreciated
View 1 Replies
View Related
Jan 30, 2004
Can someone tell me a straightforward way to call a VB app (that accepts command line arguments) from a stored procedure.
I have got it to work by using xp_cmdshell, but in practice, the security constraints here prevent using this. Our DBAs don't want to set the proxy account required for a non-sysadmin user to eexecute xp_cmdshell.
I know that writing an extended SP invoking a C++ dll would be the cleanest solution. However I don't have the knowledge to do that.
Any ideas?
Thanks
View 1 Replies
View Related
Apr 7, 2004
When I call a stored procedure from a dll written in Builder C++, it gets blocked. But if I call the same SP from the main program, it works fine. but I need to call SP from the dll. What's the problem?
Thanks...
View 1 Replies
View Related
Apr 29, 2008
Hi,
I am new to SQL and new to stored procedures!
What I am trying to do is call one stored procedure from another stored procedure. Very simple piece of code but can not get it to work correctly.....
This is the calling stored procedure: sp_TechRiskMandatory. It is calling a stored procedure called sp_Test.
What is happening is that it is executing the line before the "EXECUTE" command and never getting to this line.
If I put the "EXECUTE" command first it will execute this line and not get to the next.
My code is returning out of the stored procedure before finishing executing the remainder of the code....
CREATE PROCEDURE [sp_TechRisk_Mandatory]
@Conclusion varchar(100),
@TechRisk varchar(100)
AS
If (@Conclusion = 'Application/Changed') and ((@TechRisk = " ") or (@TechRisk = "N/A"))
Begin
Select "AsxErrorMessage" = "Technical Risk must be specified"
EXECUTE sp_Test
End
Else
Begin
Select "Looks Good" = " "
EXECUTE sp_Test
End
GO
This is the code for the sp_Test:
CREATE PROCEDURE sp_Test
AS
BEGIN
Select "AsxErrorMessage" = "Test"
END
GO
View 2 Replies
View Related
May 30, 2008
I know this thread is sql, and i'm asking a vb.net question but I cannot find a straight forward answer anywhere else. I am using visual studio 2008 designing a vb.net application where I created a stored procedure using sql management studio 2005. Here is the stored procedure:
CREATE PROCEDURE dbo.StoredProcedure2
@intPID char(10)
AS
SELECT SUM(Financial.Fee) from dbo.Financial
WHERE
CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, getdate())))=CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, Financial.Date)))
AND [SPatient Number]=@intPID
/* SET NOCOUNT ON */
RETURN
It takes the sum of my Fee column in my financial table where the current date is equal to the column named Date in my Financial table and SPatient Number equals my variable intPID, which the value is defined for in my application (yes, i know that is a horrible name for a column but I cannot change it for it is not my project).
Now, to my knowledge this procedure works fine and should output a single value. However, like i said, i am using visual studio 2008 and therefore am using vs's more automated way of connecting to sql servers(and by that I mean configuring the server through visual studio rather than manually defining datasets, dataadapters, and connection strings through code) All of the tutorials I find use data adapters and are done by manually declaring sql connections and so forth. I like visual studios more automated method of doing sql tasks, and would like to know if there is a simple way to call a stored procedure using visual studio in such a fashion where I would write something like "exec dbo.StoredProcedure2 'intPID' "
Any help is much appreciated, thank you
edit: If i did not provide enough information please let me know, i'm using a strongly typed dataset
View 1 Replies
View Related
Jun 10, 2008
I understand how to call a stored procedure using ordinal arguments, such as:
exec storedprocedure argument1 argument2
How do you do it while naming the arguments, and passing non-ordinal?
Thanks!
View 2 Replies
View Related
Nov 19, 2014
I have three stored procedure already created ABC. Now I need to create another one and call other three in each situation. Like If Apple then use Sp_A, if Orange then use Sp_B, and if Mango then use sp_C.
View 16 Replies
View Related
Mar 28, 2006
Hi all, I'm new to SQL Server and I'm trying to call BCP from a stored procedure with a parameter passed in as the path to which to export the datafile. This parameter is also the name of a network PC. However, I keep getting this error:
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file
NULL
This is the stored procedure:
PROCEDURE DownloadLinkEvents
@localPath varchar(80)
AS
declare @bcpCommand varchar(200)
begin
set @bcpCommand = 'bcp <dbName> out ' + @localPath + '-c -t"|" -S<dbServer> -Usa -P<passowrd>'
exec master..xp_cmdshell @bcpCommand
end
Thanx.
View 3 Replies
View Related
Feb 29, 2008
Hi,
This is probably the most basic of solutions, but I have spent the last 3 hours trying to work it out, and searching google!
I am trying to call information from a stored procedure (B), from within another stored procedure (A). The select statement from Procedure A contains information to be passed to Procedure B, to get some information.
This is the Procedure I have come up with so far, and I have included dbo.USERS_MEMBERSHIPSTATUS.STATUS(2, dbo.Members.EntryID) as part of the SELECT clause, in a vain attempt that this would work....but it dosn't!
Anyone got any ideas of how to do this? Or even what it would be called so I can start making inteligent searches on google?
USE [QP]
GO
/****** Object: StoredProcedure [dbo].[USERS_LIST] Script Date: 02/29/2008 18:24:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[USERS_LIST]
@STYPE Int, @UserID Int, @Username varchar(100), @Surname varchar(100), @DOB VarChar(40), @Location varchar(100), @Email Varchar(100), @Mobile varchar(100)
As
SELECT TOP 100 PERCENT dbo.Members.EntryID, dbo.Members.EntryDate, dbo.Members.Username, dbo.Members.Forename, dbo.Members.Surname, dbo.Members.Gender,
dbo.Members.DateofBirth, dbo.Members.LastAction, dbo.Members.AdminUser, dbo.ActiveMember_Mobile.Value AS Mobile,
dbo.ActiveMember_Email.Value AS Email, dbo.ActiveMember_Location.Location1, dbo.ActiveMember_Location.Location2, dbo.ActiveMember_Location.Location3,
dbo.ActiveMember_Location.Location4, dbo.F_AGE_IN_YEARS(dbo.members.dateofbirth, GetDate()) As Age, dbo.USERS_MEMBERSHIPSTATUS.STATUS(2, dbo.Members.EntryID)
FROM dbo.Members INNER JOIN
dbo.ActiveMember_Location ON dbo.Members.EntryID = dbo.ActiveMember_Location.UserID LEFT OUTER JOIN
dbo.ActiveMember_Email ON dbo.Members.EntryID = dbo.ActiveMember_Email.UserID LEFT OUTER JOIN
dbo.ActiveMember_Mobile ON dbo.Members.EntryID = dbo.ActiveMember_Mobile.UserID
WHERE @STYPE = '1' AND ((dbo.Members.EntryID = @UserID) or
(dbo.Members.Username = @Username) or
(dbo.Members.Surname = @surname) or
(dbo.Members.DateofBirth = Convert(datetime, @DOB)) or
(dbo.ActiveMember_Location.Location2 = @Location) or
(dbo.ActiveMember_Location.Location3 = @Location) or
(dbo.ActiveMember_Location.Location4 = @Location) or
(dbo.ActiveMember_Email.value = @Email) or
(dbo.ActiveMember_Mobile.value = @Mobile))
ORDER BY dbo.Members.Username
View 1 Replies
View Related
Aug 11, 2005
wanted to use sp_OACreate, sp_OAMethod and sp_OADestroy to execute aDTS package from a stored procedure. I had the dba (using the saaccount) create a wrapper stored procedure as recommended inhttp://msdn.microsoft.com/library/d...rary/en-us/dnsq....However, when I executed the wrapper stored procedure, I stillreceived privilege errors from the underlying sp_oa extended storedprocedures.Server: Msg 229, Level 14, State 5, Procedure sp_OACreate, Line 6EXECUTE permission denied on object 'sp_OACreate', database 'master',owner 'dbo'.Any ideas on what we could be doing wrong, or any suggested resolutionswould be appreciated.Thank you.
View 1 Replies
View Related
Jul 20, 2005
Hi AllI was wondering if there is a way to call a stored procedure from insideanother stored procedure. So for example my first procedure will call asecond stored procedure which when executed will return one record and iwant to use this data in the calling stored procedure. Is this possible ?Thanks in advance
View 18 Replies
View Related
Jul 20, 2005
Is it possible to have a stored procedure in database A while callingit from database B and have it manipulate the tables in database B(whatever the calling database happens to be)?We have a large-scale app that uses many complex stored procedures,and as of now, we're copying the SPs to every new database that iscreated, and it will soon become a nightmare for propagating updatesand fixes. We'd like to keep a master set of the SPs in one DB and"use" them from other DBs so that they only query data and manipulatetables in the calling DB. I hope someone has some suggestions. Thanks.
View 1 Replies
View Related
