I have a stored procedure that creates a normalized table from an existing denorm table. So I just need a simple way to call this SP from an aspx page. It would be good though to know how many records were effected, but this is not a requirement.
View 5 RepliesI create a store procedure in sql, how do I call it?
thankss
Hi, how to call the shrink_file proc from shrink_file_caller proc as below??
CREATE PROCEDURE [dbo].[Shrink_File]
@DBName VARCHAR(100),
@err INT OUTPUT
AS
...
GO
CREATE PROCEDURE [dbo].[Shrink_File_Caller]
@DBName VARCHAR(100),
@err INT OUTPUT
AS
SET NOCOUNT ON;
DECLARE @nsql NVARCHAR(4000);
SET @nsql = '' + @DBName + '.dbo.Shrink_File ''' + @DBName + ''', @err OUTPUT' ;
EXEC sp_executesql @nsql,N'@err INT OUTPUT', @err = @err OUTPUT;
GO
declare @err int;
EXEC Shrink_File_CALLER 'DATABASE1', @err
select @err as err;
I get the error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'DATABASE1'.
(1 row(s) affected)
I have searched this forum for how to call a store procedure with
parameter and so far no result has answered my question yet. I have the
following store procedure:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE sqlSearch
-- Add the parameters for the stored procedure here
@strSearch varchar(250)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
WHERE ([name_nws] LIKE '%" + @strSearch + "%' OR
[title_nws] LIKE '%" + @strSearch + "%'OR [sub_nws] LIKE '%" +
@strSearch + "%' OR [sum_nws] LIKE '%" + @strSearch + "%' OR
[content_nws] LIKE '%" + @strSearch + "%')
END
GO
I got this far in my code on how to call the parameter store procedure.
string strConn = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString; SqlConnection sqlConn = new SqlConnection(strConn); SqlCommand sqlComm = new SqlCommand(); sqlComm.Connection = sqlConn; sqlComm.CommandType = CommandType.StoredProcedure; sqlComm.CommandText = "sqlSearch"; sqlComm.Parameters.Add("@strSearch", SqlDbType.VarChar); I don't know what do next.
Help is much appreciated.
Hi,is there any method to call a store procedure into a function?ThanksFabio
View 1 Replies View RelatedHow can I get MS ACCES database records from an sql Stored Procedure. Idea is I have a SQL stored procedure, which wants to calculate a few things using some data from an MS access DB. Is it possible or am I talking against any DB principles?
View 1 Replies View Related I know I can call store procedure from store procedure but i want to take the value that the store procedure returned and to use it:
I want to create a table and to insert the result of the store procedure to it.
This is the code: Pay attention to the underlined sentence!
ALTER PROCEDURE [dbo].[test]
AS
BEGIN
SET NOCOUNT ON;
DROP TABLE tbl1
CREATE TABLE tbl1 (first_name int ,last_name nvarchar(10) )
INSERT INTO tbl1 (first_name,last_name)
VALUES (exec total_cash '8/12/2006 12:00:00 AM' '8/12/2006 12:00:00 AM' 'gilad' ,'cohen')
END
PLEASE HELP!!!! and God will repay you in kind!
Thanks!
Can i call Tigger in store procedure or vice versa in sql server 2000.
and How it implement ?
Hello, I have one store procedure that writes something data to one physical sql table 'MyTable' and on beginning I first call:DELETE FROM MyTableProcedure returns at the end data from 'MyTable' as on SELECT. PROBLEM:I started SQL Manager on my Laptop and on Computer near me and all pointed to same database (on Server) and I run that procedure simultaneously on Laptop and Computer at the same time (two hands on F5 button on these computers). Occasionally when clicking with both hands at the same time I got no records at the end of execution (on both computers), otherwise I got the results.WHAT I THINK IS PROBLEM:Somhow one SP start working and it did not come to the end and another computer called this SP and execute first command (DELETE FROM MyTable) and I got empty records at the end..WHAT I NEED:I need to prevent this situation to get empty records. All computers must get these records always. How I can use some sort of LOCKs to do this?Or to do that on C# application level using LOCK command? Thanks in advanceAleksandar
View 2 Replies View RelatedInside some TSQL programmable object (a SP/or a query in Management Studio)I have a parameter containing the name of a StoreProcedure+The required Argument for these SP. (for example it's between the brackets [])
EX1 :Â @SPToCall : [sp_ChooseTypeOfResult 'Water type']
EX2 :Â @SPToCall : [sp_ChooseTypeOfXMLResult 'TABLE type', 'NODE XML']
EX3 :Â @SPToCall : [sp_GetSomeResult]
I can't change thoses SP, (and i don't have a nice output param to cach, as i would need to change the SP Definition)All these SP 'return' a 'select' of 1 record the same datatype ie: NVARCHAR. Unfortunately there is no output param (it would have been so easy otherwise. So I am working on something like this but I 'can't find anything working
DECLARE @myFinalVarFilledWithCachedOutputÂ
NVARCHAR(MAX);
DECLARE @SPToCall NVARCHAR(MAX) = N'sp_ChooseTypeOfXMLResult
''TABLE type'', ''NODE XML'';'
DECLARE @paramsDefintion = N'@CatchedOutput NVARCHAR(MAX) OUTPUT'
[code]...
Hi,
I want to create a text file and write to text it by calling its assembly from Stored Procedure. Full Detail is given below
I write a code in class to create a text file and write text in it.
1) I creat a class in Visual Basic.Net 2005, whose code is given below:
Imports System
Imports System.IO
Imports Microsoft.VisualBasic
Imports System.Diagnostics
Public Class WLog
Public Shared Sub LogToTextFile(ByVal LogName As String, ByVal newMessage As String)
Dim w As StreamWriter = File.AppendText(LogName)
LogIt(newMessage, w)
w.Close()
End Sub
Public Shared Sub LogIt(ByVal logMessage As String, ByVal wr As StreamWriter)
wr.Write(ControlChars.CrLf & "Log Entry:")
wr.WriteLine("(0) {1}", DateTime.Now.ToLongTimeString(), DateTime.Now.ToLongDateString())
wr.WriteLine(" :")
wr.WriteLine(" :{0}", logMessage)
wr.WriteLine("---------------------------")
wr.Flush()
End Sub
Public Shared Sub LotToEventLog(ByVal errorMessage As String)
Dim log As System.Diagnostics.EventLog = New System.Diagnostics.EventLog
log.Source = "My Application"
log.WriteEntry(errorMessage)
End Sub
End Class
2) Make & register its assembly, in SQL Server 2005.
3)Create Stored Procedure as given below:
CREATE PROCEDURE dbo.SP_LogTextFile
(
@LogName nvarchar(255), @NewMessage nvarchar(255)
)
AS EXTERNAL NAME
[asmLog].[WriteLog.WLog].[LogToTextFile]
4) When i execute this stored procedure as
Execute SP_LogTextFile 'C:Test.txt','Message1'
5) Then i got the following error
Msg 6522, Level 16, State 1, Procedure SP_LogTextFile, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'SP_LogTextFile':
System.UnauthorizedAccessException: Access to the path 'C:Test.txt' is denied.
System.UnauthorizedAccessException:
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)
at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, ileOptions options)
at System.IO.StreamWriter.CreateFile(String path, Boolean append)
at System.IO.StreamWriter..ctor(String path, Boolean append, Encoding encoding, Int32 bufferSize)
at System.IO.StreamWriter..ctor(String path, Boolean append)
at System.IO.File.AppendText(String path)
at WriteLog.WLog.LogToTextFile(String LogName, String newMessage)
Can i call tigger in store procdure and vice versa??
if yes how it ?????????
Hi,
I have an integration solution that backs into two databases. Database A is custom and Database B is integrated from a COTS product. The manufacturer of B supplies a formal interface as stored procedures (and I intend to honor that contract by limiting my access to B through those stored procs).
Now, I have a search that is going to involve a subselect in the form of the following pseudocode:
select a,b from dbA.user.TableX where exists (select a from dbB.user.TableY)
I am trying to avoid creating 2 temporary cursors in the integration application and doing things in-memory (b/c of performance concerns).
So, my q is:
How can I make this happen within database A?
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/****** Object: Stored Procedure dbo.ostat_CDS_rpt47_2006 Script Date: 4/30/2008 2:25:41 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ostat_CDS_rpt47_2006]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ostat_CDS_rpt47_2006]
GO
/****** Object: Stored Procedure dbo.ostat_CDS_rpt47_2006 Script Date: 23/04/2008 11:26:14 ******/
/****** Object: Stored Procedure dbo.ostat_CDS_rpt47_2006 Script Date: 22/04/2008 14:35:33 ******/
/****** Object: Stored Procedure dbo.ostat_CDS_rpt47_2006 Script Date: 21/04/2008 11:46:08 ******/
/****** Object: Stored Procedure dbo.ostat_CDS_rpt47_2006 Script Date: 16/04/2008 17:18:04 ******/
/****** Object: Stored Procedure dbo.ostat_CDS_rpt47_2006 Script Date: 04/04/2008 12:44:34 ******/
/****** Object: Stored Procedure dbo.ostat_CDS_rpt47_2006 Script Date: 31/03/2008 12:03:31 ******/
/****** Object: Stored Procedure dbo.ostat_CDS_rpt47_2006 Script Date: 31/03/2008 11:11:07 ******/
/****** Object: Stored Procedure dbo.ostat_CDS_rpt47_2006 Script Date: 09/03/2008 14:31:30 ******/
-- ostat_CDS_rpt47_2006 2008,03,17,17
CREATE procedure ostat_CDS_rpt47_2006
@pYear int,
@pMonth int,
@pDay1 int,
@pDay2 int
as
begin
declare @sql varchar(4000)
declare @tb1 varchar(128)
declare @tb2 varchar(128)
set @tb1= 'margin..ncds0421'
set @tb2='Report.dbo.ob_apr08'
set @sql=
'
insert into '+@tb2+'
select yyyy,mm,dd,HH,sitecode,TrunkOut,SwitchCode,prefixCode,0 isCallback,
cast(callcost as decimal (10,5)) cost,count(*) attempt,
sum(case when connectflg=1 then 1 else 0 end) connected,sum(talktime) Talktime
from '+@tb1+'
where yyyy='+ltrim(str(@pYear))+'
and mm='+ltrim(Str(@pMonth)) +'
and dd between '+ltrim(Str(@pDay1))+' and '+ltrim(Str(@pDay2))+'
and talktime >0 and trunkin is null
group by yyyy,mm,dd,HH,sitecode,TrunkOut,SwitchCode,prefixCode,cast(callcost as decimal (10,5))
order by yyyy,mm,dd,HH,sitecode,TrunkOut,SwitchCode,prefixCode,cast(callcost as decimal (10,5))
'
--print (@sql)
exec (@sql)
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
My store Procedure is not save in Strore Procedure folder at the time of saving it give me option to save in Project folder and file name default is SQLQuery6.sql when i save it after saving when i run my store procedure
exec [dbo].[SP_GetOrdersForCustomer] 'ALFKI'
I am getting below error :
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'dbo.SP_GetOrdersForCustomer'.
I have a stored procedure I created in SQL server 2005. Now I need to call the stored procedure in C#. Can someone help me out here? What is the C# code I need to call this stored procedure? I have never done this before and need some help.
CREATE PROCEDURE [dbo].[MarketCreate]
( @MarketCode nvarchar(20), @MarketName nvarchar(100), @LastUpdateDate nvarchar(2),)
ASINSERT INTO Market( MarketCode MarketName LastUpdateDate)VALUES( @MarketCode @MarketName @LastUpdateDate
)
dear all
i am using VS 2005 and sqlserver 2005....
i created one procedure [dbo].[Rptdailyactivity_jul](@cond Varchar(8000), @Type varchar(100),@groupname4fsumm varchar(100) ,@Fromdate4fsumm datetime, @todate4fsumm datetime)
i want to call this procedure from VS how???
i tried based on the following code...
usig the following code i can able to manipulate tables...but how i can able to call procedure based on this code??and how i will pass parameters???
************
Dim Conomandb As New System.Data.SqlClient.SqlConnection("Data Source=HJIOertsv;Initial Catalog=tr;Integrated Security=True") If Conomandb.State = 1 Then Conomandb.Close() Conomandb.Open()
Dim Comomandb1 As New System.Data.SqlClient.SqlCommand(strSQL, Conomandb) Dim rsOman1 As System.Data.SqlClient.SqlDataReader = Comomandb1.ExecuteReader()
If rsOman1.Read() Then fghghfghf
End If Comomandb1.Cancel() Comomandb1.Dispose() rsOman1.Close() ''*********************
I have created a stored procedure only with an insert statement in sql server 2005.
How can i call this stored procedure through code in ASP.NET page using vb.
i want to pass one parameter that comes from a text box in asp.net page.
my emailid is: g12garg@yahoo.co.in pls reply.
Thank you
Gaurav
Does anyone give me syntax for adding a bcp script within a stored procedure..I had done it once 3 yrs back does'nt seem to work now, and I do not know where I am going wrong??
Thanks
Hi, If I have a dll file and I know the interface of that dll file. How can I use stored procedure to call this dll file? Thank you.
View 1 Replies View RelatedHi, If I have a dll file and I know the interface of that dll file. How can I use stored procedure to call this dll file? Thank you.
View 2 Replies View RelatedI have a SQL2000(sp2) database (ServerA) and a SQL7(sp2) database(ServerB)
From the SQL2000 database I want to call a remote stored procedure on ServerB and store the result set in a table on ServerB.
E,g
insert TableA
exec ServerB...sp_GetStuff
If I try this by making serverB a remote server,
I get the following error message
Server: Msg 18456, Level 14, State 1, Line 1
Login failed for user 'sa'.
If I make ServerB a Linked server, I get the error
Server: Msg 8501, Level 16, State 1, Line 1
MSDTC on server '' is unavailable.
Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
Now the MSDTC on ServerB won't start - It returns error 3221229574 to which the solution seems to be to reinstall MSDTC which looks like a very messy job with registry hacks and also the threat of reformatting the hard drive So I don't want to do this if possible
I really don't want a distibuted transaction anyway so I tried to stop the transaction being promoted using
SET REMOTE_PROC_TRANSACTIONS OFF
But this has no effect
Any ideas would be appreciated"
Greetings,
Even though this may be not right place with this issue I would like to try!
I facing with the problem “Object Variable or With Block variable not set” while I am trying to execute the stored procedure from Ms. Access form.
I need some help very badly or maybe a good sample of code that works in this issue is very welcome.
Many thanks in Advance
Hi,
I have two SQL SERVER server1 and server2. A server1's stored procedure should call server2's stored procedure. how can i do this.please explain me in detail.i am new to this concept.
VENKAT
hi,
i created a stored procedure and below is the code segment of it;
create Procedure test1
@price as varchar(50) output,
@table as varchar(50) = ''
AS
Declare @SQL_INS VarChar(1000)
SELECT @SQL_INS = 'select ['+@price+'] from ['+@table+']'
Exec (@SQL_INS)
Procedure gets 2 parameters, one of them is just INPUT parameter and the other one is both OUTPUT and INPUT. What i wanna do is to get the result set of this procedure to use in my application.
Hi, i wanna know if we can call a stored procedure from another one. If yes, what's the syntax?
Thanks
Respected Sir/MAm
i am working on VC++ (visual studio2005 with sql200).i have created one stored procedure to insert data into table.
i want to call this strd procedure in the VC++ main.cpp file....
Could you please help me for the correct syntax code with example.
Thank You.
Upali
Hi there. My problem is: I have two stored procedures.
1. SELECT A FROM B
2. SELECT C FROM D WHERE A = EXEC First procedure
The meaning: First procedure gets some Id from B table. The second one gets a DataSet by this Id number. The problem is that when I getting an Id from first proc I use SELECT, than in the second one I use EXEC, and in the end, seconf procedure returns two DataSets. The first contains an Id from first procedure, second contains a valid DataSet. Therefore my application falls because it suppose that valid data in first DataSet. Hoow can I call to stored procedure from another stored procedure without creating two DataSets?
P.S. I already tried to use return instead of select in the first procedure. Same result.
Thank you
Hi I am trying to run a dll or exe from a procedure or trigger in the database.
I have tried EXEC master ..xp_cmdshell '\xx empdivConsoleApplication1.exe' and
use master exec dbo.StoredProcedure1
If I run the exe from explorer all is working, but from a trigger or procedure in the database nothing happens only executed successfully.
I loaded the assembly and procedure into the master database
Code Block
CREATE PROCEDURE [dbo].[ClassLibrary3] AS EXTERNAL NAME
[ClassLibrary3].[ClassLibrary3.Class1].[StoredProcedure1]
My C# code:
Code Block
public partial class StoredProcedures
{ [Microsoft.SqlServer.Server.SqlProcedure]
public static void StoredProcedure1()
{ //------first a select to get some data String prosjekter = "";
String connString = @"Data Source=serverOFFICESERVERS;Initial Catalog=WSS_Content;Integrated Security=SSPI";
SqlConnection conn = new SqlConnection(connString);
SqlCommand sqlcmd = new SqlCommand("Select nvarchar3 prosjekt from dbo.AllUserData where tp_listId = @listid", conn);
sqlcmd.Parameters.AddWithValue("@listid", "xxx"); try { conn.Open(); SqlDataReader r = sqlcmd.ExecuteReader();
while (r.Read())
{ prosjekter += "" + r["prosjekt"] + "" + "";
}
r.Close(); conn.Close();
} catch (Exception)
{ conn.Close(); }
//-----write some changes back to a program pocketForm pf = new pocketForm();
try {
pf.LoadFromFile("C:/temp/div/xxx.xpf");
string xmlstring = pf.FormXML;
int id, item, items; id = xmlstring.IndexOf("xxxxx");
item = xmlstring.IndexOf("", id);
items = xmlstring.IndexOf("", item);
xmlstring = xmlstring.Remove(item, items - item);
xmlstring = xmlstring.Insert(item, prosjekter);
pf.FormXML = xmlstring;
pf.StatusModifiedLocally = true;
WebService.Login("http://xxxx.no", "xx", string.Empty, string.Empty);
WebService.Save(pf);
} catch (Exception)
{
}
What am I missing here ? Please answer this as I have tried everything with no success. Regards Ivar
Hi,
I would like to trigger a DTS or a stored procedure from asp.net 1.1 BUT
I don't want to wait for it to finish. In fact the DTS/Storeproc calculates values into different tables.
Those values are not needed immediately. The calculation takes between 20 or 30 minutes.
Do you have any idea how to do it ?
Thanks
I have gridview display a list of users. I have added a column for a check box. If the box is checked I move the users to another table.I need to pass some parameter of or each row to a stored proc. My question. In the loop where I check if the checkbox is selected I need to call the stored procedure.Currently I do an open and closed inside the loop.What is best and most effficent method of doing this should I open and close the connection outside the loop and change the procs parameters as loop through. System.Data.SqlClient.SqlConnection conn =
new System.Data.SqlClient.SqlConnection(
System.Configuration.ConfigurationManager.ConnectionStrings["connString"].ConnectionString);
System.Data.SqlClient.SqlCommand commChk = new System.Data.SqlClient.SqlCommand("storedProc", conn);
commChk.CommandType = System.Data.CommandType.StoredProcedure;
commChk.Parameters.AddWithValue("@mUID", ddMainUser.SelectedValue.ToString());
commChk.Parameters.AddWithValue("@sUId", gvUsers.Rows[i].Cells[2].Text);
commChk.Connection.Open();
commChk.ExecuteNonQuery();
conn.Close(); If so exactly how do I do this? How do I reset the parmaters for the proc? I haven't done this before where I need to loop through passing parameter to the same proc. thanks
Hi All,
I'll admit that I'm not the greatest at stored procedure/functions but I want to learn as much as possible. So I have two questions:
1) I had VS2005 autogenerate a sqldatasource that created Select/Insert/Update stored procedures. When Updating a record and calling the stored procedure, I want to query another table (we'll call it tblBatchNo) that has only one record, Batchno. I want to put that current batchno into the Update statement and update the record with the current batchno. Can someone point me in the right direction? Remember that I'm still a beginner on this subject.
2) Can someone provide any links to online tutorials on t-sql?
Thanks in advance.
Curtis
Dear Masters;
How can I call a stored procedure with VB code?
Thanks