Hello,
If I grant execute permissions on stored procedures in a database and the proc in turn creates tables in the DB, and if the user is not a db_owner, will the procedure be allowed to create those tables? or will the stored procs fail?
Thanks
Arun
I am building a stored procedure that changes based on the data that is available to the query. See below.
The query fails on line 24, I have the line highlighted like this.
Can anyone point out any problems with the sql?
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the error...
Msg 8114, Level 16, State 5, Procedure sp_SearchCandidatesAdvanced, Line 24
Error converting data type varchar to numeric.
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the exec point...
EXEC [dbo].[sp_SearchCandidatesAdvanced]
@LicenseType = 4,
@PositionType = 4,
@BeginAvailableDate = '10/10/2006',
@EndAvailableDate = '10/31/2007',
@EmployerLatitude = 29.346675,
@EmployerLongitude = -89.42251,
@Radius = 50
GO
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the STORED PROCEDURE...
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_SearchCandidatesAdvanced]
@LicenseType int = 0,
@PositionType int = 0,
@BeginAvailableDate DATETIME = NULL,
@EndAvailableDate DATETIME = NULL,
@EmployerLatitude DECIMAL(10, 6),
@EmployerLongitude DECIMAL(10, 6),
@Radius INT
AS
SET NOCOUNT ON
DECLARE @v_SQL NVARCHAR(2000)
DECLARE @v_RadiusMath NVARCHAR(1000)
DECLARE @earthRadius DECIMAL(10, 6)
SET @earthRadius = 3963.191
-- SET @EmployerLatitude = 29.346675
-- SET @EmployerLongitude = -89.42251
-- SET @radius = 50
SET @v_RadiusMath = 'ACOS((SIN(PI() * ' + @EmployerLatitude + ' / 180 ) * SIN(PI() * p.CurrentLatitude / 180)) + (COS(PI() * ' + @EmployerLatitude + ' / 180) * COS(PI() * p.CurrentLatitude / 180) * COS(PI()* p.CurrentLongitude / 180 - PI() * ' + @EmployerLongitude + ' / 180))) * ' + @earthRadius
SELECT @v_SQL = 'SELECT p.*, p.CurrentLatitude, p.CurrentLongitude, ' +
'Round(' + @v_RadiusMath + ', 0) AS Distance ' +
'FROM ProfileTable_1 p INNER JOIN CandidateSchedule c on p.UserId = c.UserId ' +
'WHERE ' + @v_RadiusMath + ' <= ' + @Radius
IF @LicenseType <> 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND LicenseTypeId = ' + @LicenseType
END
IF @PositionType <> 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND Position = ' + @PositionType
END
IF LEN(@BeginAvailableDate) > 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND Date BETWEEN ' + @BeginAvailableDate + ' AND ' + @EndAvailableDate
END
--SELECT @v_SQL = @v_SQL + 'ORDER BY CandidateSubscriptionEmployerId DESC, CandidateFavoritesEmployerId DESC, Distance'
PRINT(@v_SQL)
EXEC(@v_SQL)
-----------------------------------------------------------------------------------------------------------------
Hello
Which is faster :
to write a a big stored procedure with if conditions, or to separate them and call them using exec??
i.e:
if @id=1 insert into ....else if @id=2 update...-----------------------orif @id=1 exec InsertProcedureelse if @id=2 exec UpdateProcedurePlease help
Hi,
Hope someone can help me out here - I'm trying to design a data flow in SSIS which relies in part on a stored procedure as a data source - I'm running it through OLE DB.
Sure Exec MystoredProc works fine on preview and on parsing, but it refuses to acknowledge any of the columns, when I go to Edit-->Columns everything is blank.
Just out of interest - the reason I am using a stored procedure is because I dump the data into a temp table and then amend a couple of the columns to make it the same as my other database (for example where len(field) = 6 then field = '0000' + field).
Possibly I'm better off taking the raw data through the OLE connection and then transforming it through SSIS, but my gut feeling is I should minimise what I'm dumping into SSIS and offload the processing onto the local DB. Any thoughts?
Thanks
Rich
Hi,
I'm new to SSIS and SQL Server 2005 and this is now driving me very mad!!
I have an OLE DB Command in my data flow task that I want to update a table with. I have looked round this forum and on Google and just can not find a solution or what I am doing wrong. So any help would be great!
The ole db command calls a stored procedure with two input variables:
exec stp_updedgrsholds status, temp_cr_num
from debugging the ssis it says it has updated 4 rows and also from doing a data view, the data it is updating seems all correct.
but nothing gets updated in the database.
If I call the stored procedure the following way
exec stp_updedgrsholds 'C', 87
It updates fine! I have tried a number of different way with @ symbols and assignment p_status = @status
but nothing seems to work.
Any ideas are much appreciated.
Ninder Bassi
hi,
how do I exec stored procedure that accept parameter and return a single value?
here is example of report
stu_id = ******
stu_name = ****
subject | marks
aa****** | call sp_mark and return student mark for that particular student id and subject
bb****** | call sp_mark and return student mark for that particular student id and subject
cc****** | call sp_mark and return student mark for that particular student id and subject
thks,
Hi there
i have a stored procedure like this:
CREATE PROCEDURE SP_Main
AS
SET NOCOUNT ON
BEGIN TRANSACTION
exec SP_Sub_One output
exec SP_Sub_Two output
IF @@ERROR = 0
BEGIN
-- Success. Commit the transaction.
Commit Tran
END
ELSE
Rollback Tran
return
GO
now the problem is, when i execute the stored procedure's inside the main stored procedure, and these sub sp's has an error on it, the main stored procedure doesnt rollback the transation.
now i can put the "begin transation" in the two sub stored procedure's. The problem is
what if the first "SP_Sub_One" executed successfully, and there was error in "SP_Sub_Two"
now the "SP_Sub_One" has been executed and i cant rollback it... the error occured in the
"SP_Sub_Two" stored procedure ... so it wont run ... so there will be error in the data
how can i make a mian "BEGIN TRANSACTION" , that even it include the execution of stored procedure in it.
Thanks in advance
Mahmoud Manasrah
I have this code in a stored procedure: DECLARE @SQLString VarChar(200)
SET @SQLString = 'SELECT ' + @LookupField + ' FROM ' + @DBTable + ' WHERE (' + @IDField + ' = ''' + @IDValue + ''')'
Exec (@SQLString)
it works fine - with just one issue - I must grant select permission on the table.
Is there a way to do this WITHOUT granting the select permissions?
I Have a problem When I execute a stored procedure from query analyzer
(Exec storedname @parameter1='', @Parameter2='') it take 7 min. and I stop running
If I copy stored procedure , past it in Query analyzer and declare parameters
it take 3 sec.
Is there a way to execute a stored procedure and have it move on without waiting for a response from the stored procedure. I am trying to create a button on a webpage that will execute a stored procedure but the procedure takes to long to run and my page times out. Instead I would like the button to start the procedure and the webpage look at a table of data. When the table of data is empty then I will know the stored procedure is complete. Is this possible?
View 1 Replies View Relatedone of the developer has a stored procedure that selects a data from one table and update to another table.
i moved that stored procedure from development server to production server.
i gave that developer grant execute permission for that stored procedure.
since that stored procedure selects and do update the tables, do i need to give update permission to that developer to that underlying tables also.
thanks
Hi everybody, I would like to know if it's possible to execute a stored procedure, passing it parameters, using not CommandType.StoredProcedure value of sqlcommand, but CommandType.Text.
I tried to use this:
sqlCmd.CommandType = CommandType.Text
sqlCmd.Parameters.Add(sqlPar)
sqlCmd.ExecuteNonQuery()
With this sql command:
"exec sp ..."
I wasn't able to make it to work, and I don't know if it's possible.
Another question:
if it's not possible, how can I pass a Null value to stored procedure?
This code:
sqlPar = new SqlParameter("@id", SqlDbType.Int)
sqlPar.Direction = ParameterDirection.Output
cmd.Parameters.Add(sqlPar)
sqlPar = new SqlParameter("@parent_id", DBNull)
cmd.Parameters.Add(sqlPar)
doesn't work, 'cause I get this error:
BC30684: 'DBNull' is a type and cannot be used as an expression.
How can I solve this?
Bye and thanks in advance.
P.S. I would prefer first method to call a stored procedure ('cause I could call it with 'exec sp null' sql command, solving the other problem), but obviusly if it's possible...=)
Sorry for grammatical mistakes.
Hi All,
I have created a dynamic SQL STATEMENT , but the result of the execution of that statement matters to me. within stored procedure.
Note: If run the statement using EXEC() command, the result gets displayed on the SQL Editor.
But I DO NOT KNOW HOW to Capture that value.
Any idea how to capture as I would like capture the result and stored it in a table.
Thank you.
--Israr
Hi,
Our report is working fine with data loaded from a stored procedure (#1) that contains a fairly simple Select statement. We need the same report to work with a dataset loaded from a stored procedure (#2) that uses 'Exec sp_executesql @queryString'. Unfortunately, attempts to call the latter cause an error in the report. From everything that I've read, there should be no difference between datasets created using either method. Any ideas what could be getting in the way of the latter?
I have doublechecked that the dynamic query is returning a valid dataset and that all the columns are in the same format as sp #1. The designer shows the dataset and the report with the data loaded, but the live system produces an error.
Any help is much appreciated.
Debbie
Hi
I've used a temporary table in the stored procedure
I've created it as DECLARE @Temp TABLE (id INT)
in select clause I've used this temp table through the joins..
But I've also used a varchar variable to store my criteria...
which I'm building in the stored procedure
so inorder to use it in the where clause I used
EXEC ('SELECT ....................
FROM @Temp T
LEFT OUTER JOIN ...
LEFT OUTER JOIN ...
WHERE ' + @Criteria )
Unfortunately this is not working.
Giving the errror
Must declare the variable '@TempT'.
I had to use Temporary table using #, which I feel is really waste of memory...
Is there a way by which I can use my Table variable in the EXEC statement.
Thanks In advance
Hi,
I have a schedule task which call one of my stored procedure,
In this stored procedure, I need to change db owner of one of
database, but I find sp_changedbowner do not allow me to specify
db name,it only change current db,so I have to open a db before
call sp_changedbowner,but it is invalid..
CREATE PROC demo
as
begin
...
use demo_db //it is invalid
exec sp_changedbowner 'scott'
end
Can anyone give me ideas?
Thanks
Which fixed database role allows a user to execute a user defined stored procedure while minimizing the amount of permissions given. I think db_Datareader will do the trick.
View 1 Replies View RelatedHello,Is it possible to EXEC stored procedure from a query?I want to execute stored procedure for every line of SELECT resulttable.I guess it's possible with cursors, but maybe it's possible to make iteasier.Give an example, please.Thank you in advance.Hubert
View 2 Replies View Related Hello,I have a CLR stored procedure which send some values to an external URL by using the webclient, but for some reason I am getting this error.A .NET Framework error occurred during execution of user-defined routine or
aggregate "sp_LeadSend": System.Security.SecurityException: Request for
the permission of type 'System.Net.WebPermission, System, Version=2.0.0.0,
Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException: at System.Net.WebClient.UploadValues can anyone please advice how to resolve this one...I am really having a hard luck....... thanks.
I'm trying to get a stored procedure working for a website on my local machine that uses ASP.NET 1.1 and MSDE. (I have a single instance of the latter installed, using Windows Authentication mode.)
I've been able to run SQL queries and such directly (using SqlCommand and so forth) by adding the proper reader role to the account MACHINENAMEASPNET. (Substituting my actual machine name for MACHINENAME, of course.) However, when I try to run a stored procedure from an .aspx page, I get the following error:
System.Data.SqlClient.SqlException: EXECUTE permission denied on object 'MySPName', database 'mydbasename', owner 'dbo'.
I've researched this problem here and other places, and every time I get to a response that says to grant execute permission (via OSQL -E) with the following statements:
use mydbasename
go
grant execute on MySPName to MACHINENAMEASPNET
go
(There are sometimes some other intervening statements to add ASPNET as a user account, but when I use those I'm told that the account already exists ... I had added it previously via the Web Data Administrator in order to get reader permissions for SELECT statements and so forth.)
My problem is that the GRANT EXECUTE statement always fails with the following error:
Line 1: Incorrect syntax near ''
Using a forward slash instead doesn't make any difference. If I put single quotes around 'MACHINENAMEASPNET', then the error changes to:
Line 1: Incorrect syntax near 'MACHINENAMEASPNET'
And if I eliminate the machine name, then the error is:
Msg 4604, Level 16, State 1, Server MACHINENAME, Line 1
There is no such user or group 'ASPNET'
So can someone please let me know what I am missing that doesn't allow the GRANT EXECUTE to work?
Here is the stack trace (note that I have altered some names and paths for purposes of security):
[SqlException: EXECUTE permission denied on object 'MySPName', database 'mydbasename', owner 'dbo'.]
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +194
ASP.Login_aspx.MySPName(String str1, String str2) in C:Inetpubwwwrootloginlogin.aspx:43
ASP.Login_aspx.Button_Click(Object s, EventArgs e) in C:Inetpubwwwrootloginlogin.aspx:20
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1277
Thank you in advance for any assistance!
Suddenly a stored procedure, very much like several others, is givingEXECUTE permission denied on object 'Add_Adjustment', database'InStab', owner 'zhoskin'.server:Msg 229, Level 14, State 5, Procedure Add_Adjustment, Line 18.I'm zhoskin. I am the dbo and created the procedure, and when I lookat its properties, I have EXEC permission. Line 18 is just the returnstatement. The values are all appropriate for the table. So what isusually going on when a stored procedure denies access to its owner?Thanks//Zeke HoskinCREATE Procedure Add_Adjustment (@AdjAcc Int, @AdjType Char, @AdjAmtMoney, @AdjYrMth Int, @AdjDate Datetime)/* Add a Dep Adj (Type Z, Negative) or WD Adj (type Y, Positive) */ASIF @AdjType = 'Z'BEGINInsert Into tblTxn(TxnAcc, TxnType, TxnAmt, TxnSign, TxnYrMth,TxnDate)VALUES(@AdjAcc, @AdjType, @AdjAmt, -1, @AdjYrMth, @AdjDate)ENDIF @AdjType = 'Y'BEGINInsert Into tblTxn(TxnAcc, TxnType, TxnAmt, TxnSign, TxnYrMth,TxnDate)VALUES(@AdjAcc, @AdjType, @AdjAmt, 1, @AdjYrMth, @AdjDate)/*this is just to afect line numbers*/END/* set nocount on *//*space holder*/return/*more space*/GO
View 3 Replies View RelatedHello,
I develop a database that notifies clients when data changes by sending an UDP broadcast message using an extended stored procedure. Now I want to use a CLR stored procedure to send the UDP broadcast instead:
using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Net.Sockets;
public partial class UserDefinedFunctions
{
[SqlProcedure]
public static void UdpSend(SqlString address, SqlInt32 port, SqlString message)
{
System.Net.Sockets.UdpClient client = new System.Net.Sockets.UdpClient();
byte[] datagram = message.GetUnicodeBytes();
client.Send(datagram, datagram.Length, (string)address, (int)port);
}
};
I have found that to be allowed to send to 255.255.255.255 I must give the assembly permission set 'Unsafe'. If I change to 'External access' I get:
Msg 6522, Level 16, State 1, Procedure UdpSend, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'UdpSend':
System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.CodeAccessPermission.Demand()
at System.Net.Sockets.Socket.CheckSetOptionPermissions(SocketOptionLevel optionLevel, SocketOptionName optionName)
at System.Net.Sockets.UdpClient.CheckForBroadcast(IPAddress ipAddress)
at System.Net.Sockets.UdpClient.Send(Byte[] dgram, Int32 bytes, String hostname, Int32 port)
at UserDefinedFunctions.UdpSend(SqlString address, SqlInt32 port, SqlString message)
I cannot use permission set 'Unsafe' in production environment, so what I want is to customize the effective permissions with higher resoloution than the three pre-defined permission sets 'Safe', 'External access' and 'Unsafe'. Except from what is allowed by 'Safe' I only want the permissions necessary to send an UDP broadcast.
Anyone who has something like this ?
VS.NET 2005 automatically deploys a CLR stored procedure when you start a project with debugging. However, if the CLR stored procedure attempts to access external resources you will get a message stating:
System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
I've isolated this to being because the CLR stored procedure does not have the EXTERNAL_ACCESS permission set.
Is there a security attribute that can be used to decorate the CLR stored procedure code so that VS.NET 2005 will register the CLR stored procedure with the correct permission set?
Steve Miller
I have a temporary table with multiple records and a Stored Procedure requiring a value. In a Stored Procedure, I want to loop through records in the table and use the value from each record read as input to another Stored Procedure. How do I do this?
View 7 Replies View RelatedHello,
I have a stored procedure where I run an insert statement. I want to knwo if it is possible to do it using a variable for the table name (either in-line or with an EXEC statement without building a string first and executing that string. See examples of what I am talking about in both cases below:
I want to be able to do this (with or without the EXEC) :
------------------------------------------------------------------------------------
DECLARE @NewTableNameOut as varchar(100)
Set @NewTableNameOut = 'TableToInsertInto'
EXEC(
Insert Into @NewTableNameOut
Select * From tableToSelectFrom
)
------------------------------------------------------------------------------------
I can not do the above because it says I need to declare/set the @NewTableNameOut variable (assuming it is only looking at this for the specific insert statement and not at the variable I set earlier in the stored procedure.
I can do it like this by creating a string with the variable built into the string and then executing the string but I want to know if I can do it like I have listed above.
------------------------------------------------------------------------------------
DECLARE @NewTableNameOut as varchar(100)
Set @NewTableNameOut = 'TableToInsertInto'
EXEC(
'Insert Into ' + @NewTableNameOut + ' ' +
'Select * From tableToSelectFrom'
)
------------------------------------------------------------------------------------
It is not an issue for my simple example above but I have some rather large queries that I am building and I want to run as described above without having to build it into a string.
Is this possible at all?
If you need more info please let me know.
I have a permissions problem with a table/procedure that I hope someone can help me with.
To set the scene .......
All my procs/tables/functions etc are owned by dbo.
I have a windows security group that is granted permissions to EXEC all procs. No one has permissions to tables.
I have a table that has an XML column and the column has a schema collection bound to it.
The table has a computed column that relies on a function to extract a datetime element from the XML in the XML column and I have an index on this computed column.
I have a proc that selects from this table and uses the computed date column for filtering. However, the select statement is build dynamically and uses sp_execute to perform the SELECT. This of course breaks the ownership chain.
To fix the above I have a user that was created from a certificate and the above proc is signed with the certificate. The user is granted select privileges on the table. This fixes the problem. (In fact, all procs/functions/triggers are signed in this way).
Now (finally) the problem ....
When I run the above proc as admin, it works fine.
When I run it as a member of the security group (mentioned earlier) I receive ...
EXECUTE permission is denied on object 'my_schema_collection', database 'mydb', schema 'dbo'
The 'my_schema_collection' mentioned above is the schema collection to which my xml column is bound.
What? How can I grant EXEC permission to a schema collection?
Anyone have any ideas?
Thanks,
~swg
PLEASE PLEASE PLEASE......
I did not get a single response for the last 6 hours... And during this time I was searching and trying to understand the problem but I am really stuck. If this is the wrong forum to ask this question, please redirect me. Really begging for replies...[:'(]
If I use the custom SQL statements in SqlDataSource, the application runs fine within the development environment (VS2005) but errors out if I publish the web site and access outside of the environment. In order to find-out the problem, I made the following test:
I created a select statement in one SqlDataSource to fill-in a GridView. I used the exact same statement to create a stored procedure and used that SP in second SqlDataSource and I fill a second GridView. When I debug or run the application, both grids are filled OK and everything works fine. However, when I publish the web site and try to do same only the stored procedure works fine and when I try to fill the grid using the built-in SQL, the page gives error. The error mesage is as follows when I use the address 'localhost':
Server Error in '/' Application.
The SELECT permission was denied on the object 'Contacts', database 'Homer', schema 'dbo'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: The SELECT permission was denied on the object 'Contacts', database 'Homer', schema 'dbo'.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[SqlException (0x80131904): The SELECT permission was denied on the object 'Contacts', database 'Homer', schema 'dbo'.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +859322.......da da da .......
If I access the page using the IP address the message chages to below but it is not the issue, I just give it if it helps to find the problem:
Server Error in '/' Application.
Runtime Error
Description: An application error occurred on the server. The current custom error settings for this application prevent the details of the application error from being viewed remotely (for security reasons). It could, however, be viewed by browsers running on the local server machine. Details: To enable the details of this specific error message to be viewable on remote machines, please create a <customErrors> tag within a "web.config" configuration file located in the root directory of the current web application. This <customErrors> tag should then have its "mode" attribute set to "Off".
My SqlDataSource s are like this: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:HomerConnectionString %>"
SelectCommand="TestRemoteAccess" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter ControlID="TextBox1" Name="Param1" PropertyName="Text" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:HomerConnectionString %>"
SelectCommand="SELECT FirstName, LastName, Business FROM Contacts WHERE (ContactID = @Param1)">
<SelectParameters>
<asp:ControlParameter ControlID="TextBox2" Name="Param1" PropertyName="Text" />
</SelectParameters>
</asp:SqlDataSource>
Environment: SQL Server 2005, VS2005, Vista
Hi all,
In my SQL Server Management Studio Express (SSMSE), I executed the following sql code suuccessfully:
--insertNewRocord.sql--
USE shcDB
GO
CREATE PROC sp_insertNewRecord @procPersonID int,
@procFirstName nvarchar(20),
@procLastName nvarchar(20),
@procAddress nvarchar(50),
@procCity nvarchar(20),
@procState nvarchar(20),
@procZipCode nvarchar(20),
@procEmail nvarchar(50)
AS INSERT INTO MyFriends
VALUES (@procPersonID, @procFirstName, @procLastName, @procAddress,
@procCity, @procState, @procZipCode, @procEmail)
GO
EXEC sp_insertNewRecord 7, 'Peter', 'Wang', '678 Old St', 'Detroit',
'Michigon', '67899', 'PeterWang@yahoo.com'
GO
=======================================================================
Now, I want to insert a new record into the dbo.Friends table of my shcDB by executing the following T-SQL and Visual Basic 2005 codes that are programmed in a VB2005 Express project "CallshcDBspWithAdoNet":
--Form1.vb--
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form1
Public Sub InsertNewFriend()
Dim connectionString As String = "Integrated Security-SSPI;Persist Security Info=False;" + _
"Initial Catalog=shcDB;Data Source=.SQLEXPRESS"
Dim connection As SqlConnection = New SqlConnection(connectionString)
connection.Open()
Try
Dim command As SqlCommand = New SqlCommand("sp_InsertNewRecord", connection)
command.CommandType = CommandType.StoredProcedure
EXEC sp_insertNewRecord 6, 'Craig', 'Utley', '5577 Baltimore Ave',
'Ellicott City', 'MD', '21045', 'CraigUtley@yahoo.com'
Console.WriteLine("Row inserted: " + _
command.ExecuteNonQuery().ToString)
Catch ex As Exception
Console.WriteLine(ex.Message)
Throw
Finally
connection.Close()
End Try
End Sub
End Class
===========================================================
I ran the above project in VB 2005 Express and I got the following 5 errors:
1. Name 'EXEC' is not declared (in Line 16 of Form1.vb)
2. Method arguments must be enclosed in parentheses (in Line 16 of Form1.vb)
3. Name 'sd-insertNewRecord' is not declared. (in Line 16 of Form1.vb)
4.Comma, ')', or a valid expression continuation expected (in Line 16 of Form1.vb)
5. Expression expected (in Line 16 of Form1.vb)
============================================================
I know that "EXEC sp_insertNewRecord 6, 'Craig', 'Utley', '5577 Baltimore Ave',
'Ellicott City', 'MD', '21045', 'CraigUtley@yahoo.com' "in Line 16 of Form1.vb is grossly in error.
But I am new in doing the programming of T-SQL in VB 2005 Express and I am not able to change it.
Please help and advise me how to correct these problems.
Thanks in advance,
Scott Chang
HelloI need to find out what stored procedure was executed when I click ona button in a third party application, is there a way to find thisinformation out in some sort of log?Some kind of profiling to see what procedure was executed and withwhat parameters.Kind RegardsMattwww.fruitsalad.org
View 1 Replies View RelatedHi,
My DTS package performs the following:
1. 4 transformations to transform data from Sybase tables A,B,C,D to the temp tables in MSSQL
tmpA, tmpB,tmpC,tmpD
2. Next, I have a task to run 4 stored procedures to load the 4 tmp* tables to actual tables A,B,C,D
(so the task is "exec spA, exec spB, exec spC, exec spD".
3. There are 9/26/280/10000 records in the tables A,B,C,D
4. each stored procedure basically checks whether the record in the tmp* tables exist in the actual
table baesd on the primary key and then perform an insert/update.
The strange thing is:
1. All 9 records in the tmpA is loaded to A. Only 17 records from tmpB is loaded to B
2. The same codes "exec spA, exec spB, exec spC, exec spD", if copied to query analyzer,
they all run to completion, ie all 10000+ records are loaded. so there is no pbm with the data.
3. If I "split" the task such that task1 loads A and task2 loads B,C,D (and task2 runs after task1)
now again all data for A is loaded, but 25 records from B is loaded
4. I tried to catch the @@error in the stored procedure for the insert/update statements
but there's no error. Most importantly, the stored procedures run fine in query analyzer
Is there some sort of timeout or buffer issue here that is causing this strange behaviour.
All help is appreciated.
Thanx
Jes
Hi: I would like to know if I invoke a stored procedure using "EXEC [database].[user].[StoredProcedure] param1, param2..." is just like to use the ADO.NET objects: SqlCommand myCommand = new SqlCommand("StoredProcedure", myConnection);myCommand.CommandType = CommandType.StoredProcedure;myCommand.Parameters.Add("@param1", param1);myCommand.Parameters.Add("@param2", param2); ..... Thanks
View 3 Replies View RelatedI've been bangging my head against the wall on this one and its probably something really easy but for the love of god, I can't figure it out.
I have the following stored procedure
ALTER PROCEDURE [dbo].[InsertUserProjectPhase]
(
@ProjectPhaseID int,
@ProductionEngineerUserID int,
@QuantityCompleted decimal(18, 2),
@BillableHours decimal(18, 2),
@TotalWorkingHours decimal(18, 2),
@DateUpdated datetime,
@UpdatedByUserID int,
@IsDeleted bit
)
AS
SET NOCOUNT OFF;
INSERT INTO User_Project_Phase
(ProjectPhaseID, ProductionEngineerUserID, QuantityCompleted, BillableHours, TotalWorkingHours, DateUpdated, UpdatedByUserID, IsDeleted)
VALUES (@ProjectPhaseID,@ProductionEngineerUserID,@QuantityCompleted,@BillableHours,@TotalWorkingHours,@DateUpdated,@UpdatedByUserID,@IsDeleted);
SELECT ID FROM User_Project_Phase WHERE (ID = SCOPE_IDENTITY())
How can I call the above stored procedure (from within another stored procedure) AND save get the return value (the ID). I tried the following:
exec @newProjectPhaseID = dbo.InsertProjectPhase @ProjectID=3796,@PhaseID=@PhaseID,@EstimatedQuantity=@EstimatedQuantity,
@EstimatedDuration=@EstimatedDuration,@TotalQuantityCompleted=@TotalQuantityCompleted,
@TotalBilledQuantity=@TotalBilledQuantity,@QuantityType=@QuantityTypeID,@StatusID=@StatusID,
@CreatedByUserID=18569
Where @newProjectPhaseID was declared at the top as int. The numbers you see in there will be replaced with variables. They are there now because I'm executing the sp directly in management studio.
Of course, the above doesn't work. I was hoping that the return value form InsertProjectPhase would be saved in @newProjectPhaseID but I get an error.
Any help will be appreciated. THANKS A MILLION!
-Psion
I need some help with the following store proc, something is wrong but I just dont see it.
Thanks
btw I am no expert at sp so something might be complety wrong.
ALTER PROCEDURE dbo.GetSearchByDateRange
(
@strColumnNamenvarchar (50),
@dtDate1 Date,
@dtDate2 Date
)
as
EXEC ('SELECT * FROM Customers WHERE ' + @strColumnName + ' BETWEEN ' + ''' + @dtDate1 + ''' + ' AND ' + ''' + @dtDate2 + '''')