Calling SQLCMD From Within An Executing Procedure
Apr 4, 2008Is there a way to call SQLCMD from within an executing procedure?
ADVERTISEMENT
Batchfile Calling SQLCMD To Call A SQL Server Agent Job
Jan 15, 2008
Hi All,
You have to forgive me but I am relatively new to this. I created a batch file which would call a SQL Server Agent Job. The said job calls SSIS packages. The last step for the job was to update a table which contains the next run date for the job. The batch file, after calling the job, would then query the table where the next run date is stored and place it in a log file. However, it seems that the log file gets updated first. The user who clicked on my batch file assumes that the job is finished and would proceed to run another program which scans all the records affected by the job. Since the job is not yet finished, the few records that were affected are the ones that are only scanned leaving the other records that are yet to be transferred.
Does calling SQLCMD from a batch file spawn a new thread for the job?
Why does the date get updated even though the job is not finished yet?
I have attached the bat file I created. Below that also is the control flow
@echo off
echo.
echo Executing job PRD_ICBS...
echo.
echo Please wait...
SQLCMD -Q "EXEC msdb.dbo.sp_start_job @job_name='TRANSFER_DATA'"
echo.
echo Creating folder %date:~-4,4%-%date:~-10,2%-%date:~-7,2%...
md %date:~-4,4%-%date:~-10,2%-%date:~-7,2%
cd %date:~-4,4%-%date:~-10,2%-%date:~-7,2%
date/t >> TRANSFER_DATA.txt
echo PRD_Insert_HMTRANSACTIONDATADETAILS_From_ICBS_TD >> PRD_ICBS.txt
echo PRD_Insert_HMTRANSACTIONDATADETAILS_From_ICBS_CASA >> PRD_ICBS.txt
echo PRD_Insert_HMTRANSACTIONDATADETAILS_From_ICBS_LOANS >> PRD_ICBS.txt
SQLCMD -Q "SELECT next_rundate FROM db.NEXT_RUNDATE_PARAM WHERE NAME = 'PRD_Insert_HMTRANSACTIONDATADETAILS_From_ICBS_TD'" -o "PRD_Insert_HMTRANSACTIONDATADETAILS_From_ICBS_TD.txt"
SQLCMD -Q "SELECT next_rundate FROM db.NEXT_RUNDATE_PARAM WHERE NAME = 'PRD_Insert_HMTRANSACTIONDATADETAILS_From_ICBS_CASA'" -o "PRD_Insert_HMTRANSACTIONDATADETAILS_From_ICBS_CASA.txt"
SQLCMD -Q "SELECT next_rundate FROM db.NEXT_RUNDATE_PARAM WHERE NAME = 'PRD_Insert_HMTRANSACTIONDATADETAILS_From_ICBS_LOANS'" -o "PRD_Insert_HMTRANSACTIONDATADETAILS_From_ICBS_LOANS.txt"
----------------------------- ----------------------------------------
| | | |
| Transfer Data | ===> | Update NEXT RUN DATE | |
| | | |
----------------------------- ----------------------------------------
Thanks in advance
Stored Procedure Executing Durations Are Different Between Executing From Application(web) And SQl Server Management Studio - Qu
Jan 24, 2008
Hi,
I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query window
Please see the image through this url http://kyxao.net/127/ExecutionProblem.png
Any ideas for this issue?
Thanks a lot
Stored Procedure Executing Durations Are Different Between Executing From Application(web) And SQl Server Management Studio - Query Window
Jan 23, 2008Hi,I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query windowPlease see the image attached http://kyxao.net/127/ExecutionProblem.png Any ideas for this issue?Thanks a lot Jalijack
View 2 Replies View RelatedManagement Studio Express && SQLcmd: Syntax Errors In Executing A MS-DOS Batch File && A Microsoft SQL Server Query File?
Nov 2, 2007Hi all,
I have the "Northwind" database in my Sql Server Management Studio Express.
In my C:ProSSEAppsSamplesForChapter02Chapter02 folder, I have the following 2 files:
(1) ListColumnValues (MS-DOS Batch File)
sqlcmd -S .sqlexpress -v DBName = "Northwind" CName = "CompanyName" TName =
"Shippers" -i c:prosseappschapter02ListListColumnVales.sql -o
c:prosseappschapter02ColumnValuesOut.rpt
(2) ListColumnValues (Microsoft SQL Server Query File)
USE $(Northwind)
GO
SELECT $(CompanyName) FROM $(Shippers)
GO
When I ran the following SQLcmd:
C:ProSSEAppsSamplesForChapter02Chapter02>ListColumnValues.bat
I got the following "ColumnValuesOut.rpt" with error messages:
'Northwind' scripting variable not defined.
Msg 102, Level 15, State 1, Server L1P2P3SQLEXPRESS, Line 1
Incorrect syntax near '$'.
'CompanyName' scripting variable not defined.
'Shippers' scripting variable not defined.
Msg 102, Level 15, State 1, Server L1P2P3SQLEXPRESS, Line 1
Incorrect syntax near 'CompanyName'.
I copied these T-SQL statements from a book and I do not know how to correct them.
Please help and tell me how to correct these errors.
Thanks in advance,
Scott Chang
Executing SP Having A Dynamic Cursor Fails In Calling SP
Apr 21, 2008Hi,
In a stored procedure (SP1) I call another stored procedure (SP2), passing along parameters. In SP2 I dynamically build cursor c1. I can execute SP2 without any problems but when I start SP1 I get the following message:
Msg 16916, Level 16, State 1, Procedure SP2, Line 114
A cursor with the name 'C1' does not exist.
Yes, the cursor is of type GLOBAL. I am sure I miss something here ...
Any help is highly appreciated !
Thanks: Peter
Executing Store Procedre By Calling It From An Insert Trigger?
Feb 10, 2000hi, I have a trigger on a table for insert, once there is new data into that table I want to run a nother store procedure by passing all input from inserted to the store procedure as input parameter. can I do that.
Thanks
Ali
(Could Not Find Stored Procedure ''.) When Calling A User Defined Procedure
Feb 4, 2008Hi,I'm tring to call a stored procedure i'v made from a DNN module, via .net control.When I try to execute this sql statement: EXEC my_proc_name 'prm_1', 'prm_2', ... the system displays this error: Could not find stored procedure ''. (including the trailings [".] chars :)I've tried to run the EXEC statement from SqlServerManagement Studio, and seems to works fine, but sometimes it displays the same error. So i've added the dbname and dbowner as prefix to my procedure name in the exec statement and then in SqlSrv ManStudio ALWAYS works, but in dnn it NEVER worked... Why? I think it could be a db permission problem but i'm not able to fix this trouble, since i'm not a db specialist and i don't know which contraint could give this problem. Also i've set to the ASPNET user the execute permissions for my procedure... nothing changes :( Shoud someone could help me? Note that I'm using a SqlDataSource object running the statement with the select() method (and by setting the appropriate SelectCommandType = SqlDataSourceCommandType.StoredProcedure ) and I'm using the 2005 sql server express Thank in advance,(/d
View 3 Replies View RelatedCalling A Stored Procedure From ADO.NET 2.0-VB 2005 Express: Working With SELECT Statements In The Stored Procedure-4 Errors?
Mar 3, 2008Hi 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.
Calling A Procedure
Apr 8, 2008create 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
Executing Procedure
May 13, 2008
Hi,
I have StoredProcedure with 2 Parameters. I want to execute this procedure in SQLSERVER Query Builder.
I know without using parameters to execute. i.e EXEC pro_demo
But now i have with parameters.
How to pass that parameters in a Query Builder?
Calling A Nested Procedure
Sep 11, 2006Everytime I execute this script - I am not getting the result I want. I was hoping to take the some parameters call one stored procedufre with in the main procedure. I was hoping to assign a variable to the nested procedure and use that variable in the main procedure, but I am getting a '0' as result - however when I call the a inner procedure by itself if get the correct result. Can someone look at this and tell me where I am wrong? It seems to be a scope problem. Thanks set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGO-- =============================================-- Author: -- Create date: September 8,2006-- Description: Inserts a new project-- =============================================ALTER PROCEDURE [echo88].[insertProject] -- Add the parameters for the stored procedure here @name varchar(50), @desc varchar(MAX), @start datetime, @finish datetime, @memid int, @addr1 varchar(32), @addr2 varchar(16), @city varchar(32), @stateid int, @zipcode char(5)ASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @siteid int; EXEC @siteid = insertSite @addr1, @addr2, @city, @stateid, @zipcode; -- Insert statements for procedure here INSERT INTO Project(Title, Description, Start, Finish, SiteID, MemberID ) VALUES ( @name, @desc, @start, @finish, @siteid, @memid );END
View 1 Replies View RelatedCalling A Stored Procedure
Mar 23, 2007Hi, 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
Out Vs. Ref When Calling Stored Procedure
Apr 8, 2008Hi, 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.
Calling A DTS Stored Procedure From ASP.NET
Dec 15, 2003I 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!
Calling Stored Procedure
Feb 6, 2004I 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
Calling DTS From Stored Procedure
May 22, 2000Hello everybody,
How can I call DTS from stored procedure, any help pls.
PS: I appreciate if you please give me an example.
Thank you....
Calling A Job From A Stored Procedure
Jan 19, 2001I'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 ?
Calling Stored Procedure
Nov 3, 2005Hey, 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 RelatedCalling A VB Exe From A Stored Procedure
Jan 30, 2004Can 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
Calling A Stored Procedure From A Dll
Apr 7, 2004When 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...
Calling Stored Procedure From Another Sp
Apr 29, 2008Hi,
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
Calling A Stored Procedure From Vb.net
May 30, 2008I 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
Calling A Stored Procedure
Jun 10, 2008I 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!
Calling Stored Procedure In Another
Nov 19, 2014I 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 RelatedCalling BCP From A Stored Procedure
Mar 28, 2006Hi 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.
Calling A Stored Procedure From Within Another
Feb 29, 2008Hi,
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
Calling Store Procedure
Mar 10, 2008I have created a database and stored procedure there.I want to execute that store procedure having dot net in my front end,i.e i want to execute store procedure from .net.How can I do that.Pls help me.
View 1 Replies View RelatedCalling DTS From Stored Procedure
Aug 11, 2005wanted 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 Related2005: Calling .NET Procedure
Jul 10, 2006Hi,I am learning SQL Server 2005. I need to call .NET assembly procedurefrom T-SQL.Here's part of my assembly:using System;using System.Data.Sql;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;namespace DemoSQLServer{public sealed class Demo{[SqlProcedure(Name="PodajKsi¹¿ki")]public static void PodajKsi¹¿ki(){SqlCommand cmd = new SqlCommand("SELECT * FROM Ksi¹¿ki");SqlDataReader dr = cmd.ExecuteReader();SqlContext.Pipe.Send(dr);}...}}I have created assembly in Object Explorer (Programmability /Assemblies).How to call procedure? I tried:exec DemoSQLServer.PodajKsi¹¿kibut I got a message:Could not find stored procedure 'DemoSQLServer.PodajKsi¹¿ki'.Please help.Thank you./RAM/
View 8 Replies View RelatedCalling An Oracle Procedure Via A DTS
Jul 20, 2005Is there a way to call an Oracle Procedure using the MS OLD DB Providerfor Oracle object in a SQL Server 2000 DTS package? If it can't be donethis way, is there another way to retrieve data from an Oracle databaseusing an Oracle procedure to a SQL Server table? Also, can parametersbe passed into Oracle from SQL Server via a procedure? Our Oracle DBAdoes NOT want to create views to enable SQL Server to access the data.Examples would be great if possible. Thanks.*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedCalling Stored Procedure
Aug 25, 2005I have a created a report that needs to call a sybase Stored Procedure, my connection string is fine. I am able to call the sp from sql advantage passing in several parameters and it runs just fine. However when I created a dataset and tried to call it through the reporting service I get the following error message.
View 5 Replies View RelatedCalling Procedure With Out Parameter
Apr 8, 2008create 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