T-SQL And Visual Basic 2005 Codes That Execute A User-Defined Stored Procedure In Management Studio:How To Declare EXEC && Sp?
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
View Complete Forum Thread with Replies
Related Forum Messages:
T-SQL And Visual Basic 2005 Codes That Execute A User-Defined Stored Procedure In Management Studio Express (Part 2)
Hi Jonathan Kehayias, Thanks for your valuable response. I had a hard time to sumbit my reply in that original thread yesterday. So I created this new thread. Here is my response to the last code/instruction you gave me: I corrected a small mistake (on Integrated Security-SSPI and executed the last code you gave me. I got the following debug error message: 1) A Box appeared and said: String or binary data would be truncated. The statement has been terminated. |OK| 2) After I clicked on the |OK| button, the following message appeared: This "SqlException was unhandled String or binary data would be truncated. The statement has been terminated." is pointing to the "Throw" code statement in the middle of ....................................... Catch ex As Exception MessageBox.Show(ex.Message) Throw Finally .......... Please help and advise how to correct this problem in my project that is executed in my VB 2005 Express-SQL Server Management Studio Express PC. Thanks, Scott Chang The code of my Form1.vb is listed below: Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Public Class Form1 Public Sub InsertNewFriend() Dim connectionString As String = "Data Source=.SQLEXPRESS;Initial Catalog=shcDB;Integrated Security=SSPI;" Dim connection As SqlConnection = New SqlConnection(connectionString) Try connection.Open() Dim command As SqlCommand = New SqlCommand("sp_insertNewRecord", connection) command.CommandType = CommandType.StoredProcedure command.Parameters.Add("@procPersonID", SqlDbType.Int).Value = 7 command.Parameters.Add("@procFirstName", SqlDbType.NVarChar).Value = "Craig" command.Parameters.Add("@procLastName", SqlDbType.NVarChar).Value = "Utley" command.Parameters.Add("@procAddress", SqlDbType.NVarChar).Value = "5577 Baltimore Ave" command.Parameters.Add("@procCity", SqlDbType.NVarChar).Value = "Ellicott City" command.Parameters.Add("@procState", SqlDbType.NVarChar).Value = "MD" command.Parameters.Add("@procZipCode", SqlDbType.NVarChar).Value = "21045" command.Parameters.Add("@procEmail", SqlDbType.NVarChar).Value = "CraigUtley@yahoo.com" Dim resulting As String = command.ExecuteNonQuery MessageBox.Show("Row inserted: " + resulting) Catch ex As Exception MessageBox.Show(ex.Message) Throw Finally connection.Close() End Try End Sub Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load InsertNewFriend() End Sub End Class
View Replies !
User 'Unknown User' Could Not Execute Stored Procedure - Debugging Stored Procedure Using Visual Studio .net
Hi all, I am trying to debug stored procedure using visual studio. I right click on connection and checked 'Allow SQL/CLR debugging' .. the store procedure is not local and is on sql server. Whenever I tried to right click stored procedure and select step into store procedure> i get following error "User 'Unknown user' could not execute stored procedure 'master.dbo.sp_enable_sql_debug' on SQL server XXXXX. Click Help for more information" I am not sure what needs to be done on sql server side We tried to search for sp_enable_sql_debug but I could not find this stored procedure under master. Some web page I came accross says that "I must have an administratorial rights to debug" but I am not sure what does that mean? Please advise.. Thank You
View Replies !
I Need To Add A Row To A SQL CE Database If It Does Not Exists.I Am In Visual Studio 2005 Using Visual Basic 2005.
I need to add a row to a SQL CE database if it does not exists using Visual Basic 2005 in Visual Studio 2005. I can't seem to find the duplicate record using a tableadapter query. It adds the same record again. I am trying to use tableadapters, but do not have to. Any suggestions? Here is the add row code I am using... checkCustRow = PcDatabase1.MainToolData.NewMainToolDataRow() checkCustRow("Name") = "" checkCustRow("Size1") = cmbSize1 checkCustRow("Size2") = "" checkCustRow("Size3") = "" checkCustRow("Size4") = "" checkCustRow("Pressure1") = cmbPressure checkCustRow("Pressure2") = "" checkCustRow("Pressure3") = "" checkCustRow("Pressure4") = "" checkCustRow("Category") = "BOPs" checkCustRow("VSSName") = "BOPs.vss" checkCustRow("Type") = cmbType checkCustRow("Manufacturer") = "WFT" checkCustRow("Height") = cmbHeight checkCustRow("Width") = cmbWidth checkCustRow("Weight") = cmbWeight checkCustRow("VolumeOpen") = cmbOpen checkCustRow("VolumeClosed") = cmbClosed checkCustRow("EndConnection") = "" checkCustRow("Userdefined") = "T" PcDatabase1.Tables("MainToolData").Rows.Add(checkCustRow) Dim ta As New PCDatabaseTableAdapters.MainToolDataTableAdapter ta.Update(PcDatabase1.MainToolData) Any suggestions? Jeff
View Replies !
Debugging Stored Procedure From Visual Studio 2005
Hi ,I am using Visual studio 2005 with sql server 2005. I want to debug my stored procedure, which is situated on the server on the network(accessible through network share). I followed the following URL: http://aspnet.4guysfromrolla.com/articles/051607-1.aspxI have used Direct database debugging : When I right click my stored procedure and click 'step into stored procedure', I get the following error: "Unable to start T-SQL debugging. could not attach to SQL server process on 'sql_server_name'. The remote procedure call failed and did not execute" I am using windows authentication to login to sql server Any help?
View Replies !
Debugging Stored Procedure From Visual Studio 2005
Hi , I am using Visual studio 2005 with sql server 2005. I want to debug my stored procedure, which is situated on the server on the network(accessible through network share). I followed the following URL: http://aspnet.4guysfromrolla.com/articles/051607-1.aspx I have used Direct database debugging : When I right click my stored procedure and click 'step into stored procedure', I get the following error: "Unable to start T-SQL debugging. could not attach to SQL server process on 'sql_server_name'. The remote procedure call failed and did not execute" I am a sysadmin in SQL server. I am using windows authentication to login to sql server Any help?
View Replies !
How To View Permissions Of User-defined Database Roles In Management Studio?
As part of our security project, I've done the following when logged in as 'sa': Created database roles 'dbrole1' within dbAccount Created login and user 'user1' and added user to be a member of 'dbrole1' Granted execute permissions on sp1 and sp2 to 'dbrole1' However, I didn't see the above permissions listed in SQL Server Management Studio - Database - Security - Roles - Database Roles - 'dbrole1' properties - securables Any ideas? Thanks!
View Replies !
Visual Studio 2005 Standard And SQL Server Management Studio?
I am new to visual studio and I am still not sure of all its components and features. I installed visual studio 2005 standard edition but cannot find SQL Server Management Studio? I guess this must be because it is not included with Visual studio 2005 standard. Is it included with VS 2005 professional? I want to add pictures of products to my shopping site using an SQL database and I’ve been told that SQL Server Management studio is required as it is a graphical tool. How would I go about obtaining the SQL server management studio. There seems to be different versions of SQL server that it is confusing to know which one to purchase. Will the SQL server 2005 version that comes with Visual studio standard be sufficient for me now right? I want to create a shopping site with hundreds, perhaps even thousands of products. I want to use an SQL server 2005 database. The database will include ‘dynamically generated’ product images if that is the correct terminology. My goodness, it seems I still have so much to learn. Thanks
View Replies !
SQL Server Management Studio Express: Object Explorer - How To Re-attach The Content Of User-defined Database
Hi all, I just found that the content of my Database "ssmsExpressDB" is gone, but the name "ssmsExpressDB" remains in the Object Explorer of SQL Server Management Studio Express. If I delected the name "ssmsExpressDB" and executed the following .sql: exec sp_attach_db @dbname = N'ssmsExpressDB', @filename1 = N'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatassmsExpressDB.mdf', @filename2 = N'C:Program filesMicrosoft SQL ServerMSSQL.1MSSQLDatassmsExpressDB_log.LDF' GO I got the following error message: Msg 5120, Level 16, State 101, Line 1 Unable to open the physical file "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatassmsExpressDB.mdf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)". And I have closed all my projects and I do not know what " The process cannot access the file because it is being used by another process" is all about!? Please help and tell me how I can re-attach the content of my "ssmsExpressDB" in the Object Explorer of SQL Server Management Studio Express. Thanks in advance, Scott Chang ==================================================================================== I found the "ssmsExpressDB" is being used by my VB 2005 Express project "Hello-SQLCLR-1": in the Database Explorer, Data Connections place. How can I put it back to the Object Explorer of SQL Server Management Studio Express? Please help and advise. =======================================================
View Replies !
SQL Server Management Studio Express: Object Explorer - How To Re-attach The Content Of User-defined Database
Hi all, I just found that the content of my Database "ssmsExpressDB" is gone, but the name "ssmsExpressDB" remains in the Object Explorer of SQL Server Management Studio Express. If I delected the name "ssmsExpressDB" and executed the following .sql: exec sp_attach_db @dbname = N'ssmsExpressDB', @filename1 = N'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatassmsExpressDB.mdf', @filename2 = N'C:Program filesMicrosoft SQL ServerMSSQL.1MSSQLDatassmsExpressDB_log.LDF' GO I got the following error message: Msg 5120, Level 16, State 101, Line 1 Unable to open the physical file "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatassmsExpressDB.mdf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)". And I have closed all my projects and I do not know what " The process cannot access the file because it is being used by another process" is all about!? Please help and tell me how I can re-attach the content of my "ssmsExpressDB" in the Object Explorer of SQL Server Management Studio Express. Thanks in advance, Scott Chang ==================================================================================== I found the "ssmsExpressDB" is being used by my VB 2005 Express project "Hello-SQLCLR-1": in the Database Explorer, Data Connections place. How can I put it back to the Object Explorer of SQL Server Management Studio Express? Please help and advise. =====================================================================================
View Replies !
Stored Procedure Error When Run From Visual Basic In Access
I am working in an access data project. I have a stored procedure that runs fine when I open and run it directly in sql. When I use the DoCmd.OpenStoredProcedure method in VB code, the stored procedure also runs fine (and successfully adds records as it should) but then I get an error: #7874 "...can't find the object...'[Name of sp'". This halts the vb code and is a problem. Here's example code from a sp that causes this problem: Insert into Table (Field1, Field2, Field3, Field4) Select Field1, 'Test', Field5, GetDate() from View1 I understand there may be another syntax to run a stored procedure from access visual basic other than DoCmd. I would very much appreciate guidance as to how to do this. Thank you.
View Replies !
Passing Parameters To A Stored Procedure In Visual Basic
Hi peeps, I need some help with passing parameters to a stored procedure from my visual basic code. Unfortunately im a bit of a novice with Visual basic and therefore have very little experience with it. I have written a stored procedure in VS 2005 which when executed from the server explorer appears to retrieve the results that I require. However I am at a loss for how to actually call this procedure from my visual basic code. The stored procedure is fairly simple requiring 5 colums from 2 tables. The procedure requires a single parameter to be passed to it. The code for the procedure is listed below: /* Name: usp_display_all_users Description: Displays activeuser, personid, comment from table: pswds Userid and sort from table: people Where the username is like the parameter supplied. Both tables joined on personid Author: Iain Blackwood Modification log: Change Description Date Changed by Created proc 02/01/07 Iain Blackwood */ ALTER PROCEDURE usp_display_all_users ( @searchStr nvarchar(128) ='' ) AS SELECT dbo.pswds.activeuser, dbo.pswds.personid, dbo.people.userid, dbo.people.sort, dbo.pswds.comment FROM dbo.pswds INNER JOIN dbo.people ON dbo.pswds.personid = dbo.people.personid WHERE (dbo.people.sort LIKE @searchStr + '%') ORDER BY dbo.people.sort The Visual Basic application I am working on firstly requires login details from the user to build a connection string for the SqlConnection. Once these vaules have been succesfully retrieved the application should display a view with the data returned by the stored procedure (in this case the stored procedure should use the default input parameter value of an empty string to return every row of data from the tables). However I also require that the stored procedure be called if the user enters a search string into the relevant textbox. I have managed to reproduce the view I require with the following code however this is using SQL commands passed directly to the an SqlDataAdapter and not by calling the Stored procedure that i have written. Private Sub fillDataGrid() ' I NEED TO: ' 1: Fill the data set with all Accounts ' 2: Diplay the Data to the data grid ' delcare a new SQL connection sqlCon = New SqlConnection(conStr) ' Delcare and build the SQL Command String: WILL BE REPLACED BY STORED PROCEDURE Dim comStrPeople As String = "SELECT pswds.activeuser, pswds.personid, userid, sort, pswds.comment" comStrPeople += " FROM pswds INNER JOIN" comStrPeople += " people ON pswds.personid = people.personid" comStrPeople += " ORDER BY sort" ' Display the command string: TEMPOARY testlbl2.Text = comStrPeople ' Declare a new SQL data adapter sqlDataAdapter1 = New SqlDataAdapter(comStrPeople, sqlCon) Try ' Declare a new dataset sqlDataSet = New DataSet ' fill the sql data adapter with data from dataset: called PeoplePswds sqlDataAdapter1.Fill(sqlDataSet, "PeoplePswds") ' Fill the forms datagrid view with data from the Dataset table PeoplePswds DataGrid1.DataSource = sqlDataSet.Tables("PeoplePswds").DefaultView Catch ex As Exception ' Display suitable error message MessageBox.Show("Unable to retrieve Account Data at sub fillDataGrid" + ex.Message) End Try End Sub I Guess what im asking for is someone to show / help with how the stored procedure is called from the visual basic code and passed the parameter/s required. Thanx Flakkie
View Replies !
Visual Basic Slow Down Sql Server 2000 Running A Stored Procedure
I hav the following problem. I have written an stored procedure in sql server 2000 as the following CREATE PROCEDURE dbo.pa_rellena @pFechaInicio datetime AS declare @pFechaFin datetime declare @auxcod_cen char(10) declare @importeEfectivo decimal(17,2) declare @importeTarjetas1 decimal(17,2) declare @importeTarjetas2 decimal(17,2) declare @importeVales decimal(17,2) declare @importeTalones decimal(17,2) declare @importeGastos decimal(17,2) select @pFechaFin=@pFechaInicio+1 --Borramos las tablas temporales si las hemos creado con anterioridad y no se han borrado if object_id('tmpCentros') is not null drop table tmpCentros if object_id('tmpCentros2') is not null drop table tmpCentros2 if object_id('tmpMaxCajas') is not null drop table tmpMaxCajas if object_id('tmpCajasCentro') is not null drop table tmpCajasCentro if object_id('tmpVales') is not null drop table tmpVales if object_id('tmpDiarioEfectivo') is not null drop table tmpDiarioEfectivo if object_id('tmpDiarioTalones') is not null drop table tmpDiarioTalones if object_id('tmpDiarioTarjetas') is not null drop table tmpDiarioTarjetas if object_id('tmpDiarioSegundaForma') is not null drop table tmpDiarioSegundaForma if object_id('tmpDiarioGastosTarjetas') is not null drop table tmpDiarioGastosTarjetas if object_id('temp1') is not null drop table temp1 --Seleccionamos todos los centros de Salvador Bachiller select * into tmpCentros2 from centros where centros.tienda=1 order by cod_cen --Seleccionamos el maximo de cajas por cada centro select cod_cen, max(cod_caja) as cajas into tmpMaxCajas from cierrecaja where fecha>=@pFechaInicio and fecha<@pFechaFin group by cod_cen order by cod_cen --Mezclamos los centros con el maximo de cajas select c.cod_cen, c.Centro, c.Direccion, c.localidad, c.provincia, c.cpostal, c.telefono, m.cajas, operaciones, cajas_tot, tienda, franquicia into tmpCentros from tmpCentros2 as c left outer join tmpMaxCajas as m on c.cod_cen=m.cod_cen --Cajas por centro select distinct cod_cen as cod_cen, cod_caja as cod_caja into tmpCajasCentro from cierrecaja where fecha>=@pFechaInicio and fecha<@pFechaFin --Los vales de cada centro select cod_cen,sum(importe) as imp1 into tmpVales from vales where fecha>=@pFechaInicio and fecha<@pFechaFin group by cod_cen --Efectivo de cada centro select cod_cen,'01' as vendedor,'EFECTIVO' as descripcion, (sum(diario.TotEuro)-Sum(Diario.Imppa2)) as importe1,0 as exp1, (sum(Diario.TotEuro)-sum(Diario.imppa2)) as importe2 into tmpDiarioEfectivo from diario where fecha>=@pFechaInicio and fecha<@pFechaFin and cod_cen in (select cod_cen from tmpCentros) and cod_caja in (select cod_caja from tmpCajasCentro) and diario.cod_pago='01' group by cod_cen --Talones por centro select centros.cod_cen,'02' as vendedor,'TALONES' as descripcion, sum(diario.TotEuro) as importe1,0 as exp1, sum(Diario.TotEuro) as importe2 into tmpDiarioTalones from centros inner join diario on centros.cod_cen=diario.cod_cen where fecha>=@pFechaInicio and fecha<@pFechaFin and diario.cod_cen in (select cod_cen from tmpCentros) and cod_caja in (select cod_caja from tmpCajasCentro) and diario.cod_pago='02' group by centros.cod_cen --Tarjetas por centro select cod_cen,'03' as vendedor,'TARJETAS' as descripcion, sum(diario.TotEuro) as importe1,0 as exp1, sum(Diario.TotEuro*(FPago.Descuento/100)) as importe2, sum(Diario.TotEuro) - sum(Diario.TotEuro*(FPago.Descuento/100)) as importe3 into tmpDiarioTarjetas from FPago left join Diario on fpago.Cod_pago=Diario.cod_pago where fecha>=@pFechaInicio and fecha<@pFechaFin and cod_cen in (select cod_cen from tmpCentros) and cod_caja in (select cod_caja from tmpCajasCentro) and Fpago.Descuento<>0 group by cod_cen --Segunda Froma de Pago select cod_cen,'03' as vendedor,'TARJETAS' as descripcion,sum(diario.imppa2) as importe1 into tmpDiarioSegundaForma from fpago left join Diario on Fpago.cod_pago=diario.cod_pa1 where fPago.cod_pago<>'99' and fecha>=@pfechaInicio and fecha<@pFechaFin and cod_cen in (select cod_cen from tmpCentros) and cod_caja in (select cod_caja from tmpCajasCentro) and Fpago.Descuento<>0 group by cod_cen --Comisiones tarjetas de pago select cod_cen,'10' as vendedor, 'GASTOS (-)' as descripcion, sum(Diario.imppa2*(fPago.Descuento/100)) as importe2 into tmpDiarioGastosTarjetas from Fpago left join Diario on FPago.cod_pago= Diario.cod_pa1 where fPago.cod_pago<>'99' and fecha>=@pFechaInicio and fecha<@pFechaFin and cod_cen in (select cod_cen from tmpCentros) and cod_caja in (select cod_caja from tmpCajasCentro) and Fpago.Descuento<>0 group by cod_cen /* --Venta neta por centro declare cursortemporal cursor for select cod_cen from TmpCentros2 open cursortemporal delete detallecaja_aux fetch next from cursortemporal into @auxcod_cen while @@fetch_status=0 Begin select @importeVales=imp1 from tmpVales where cod_cen=@auxcod_Cen select @importeEfectivo=importe2 from tmpDiarioEfectivo where cod_cen=@auxcod_Cen select @importeTalones=importe2 from tmpDiarioTalones where cod_cen=@auxcod_cen select @importeTarjetas1=importe3 from tmpDiarioTarjetas where cod_cen=@auxcod_cen select @importeTarjetas2=importe1 from tmpDiarioSegundaForma where cod_cen=@auxcod_cen select @importeGastos=importe2 from tmpDiarioGastosTarjetas where cod_cen=@auxcod_cen select @importeVales=isnull(@importeVales,0) select @importeEfectivo=isnull(@importeEfectivo,0) select @importeTalones=isnull(@importeTalones,0) select @importeTarjetas1=isnull(@importeTarjetas1,0) select @importeTarjetas2=isnull(@importeTarjetas2,0) select @importeGastos=isnull(@importeGastos,0) print @auxcod_cen print @importeVales print @importeEfectivo print @importeTalones print @importeTarjetas1 print @importeTarjetas2 print @importeGastos insert into detallecaja_aux (cod_cen,importe1) values(@auxcod_cen, @importeVales+@importeEfectivo+@ImporteTalones+@ImporteTarjetas1+@importeTarjetas2-@importeGastos) fetch next from cursortemporal into @auxcod_cen select @importeVales=0 select @importeEfectivo=0 select @importeTalones=0 select @importeTarjetas1=0 select @importeTarjetas2=0 select @importeGastos=0 end close cursortemporal */ select * from detallecaja_aux GO When I try to run it from visual basic it slow down the sql server. What can I do?
View Replies !
Stored Procedure Not Returning OUTPUT Parameters To Visual Basic Program
I have coded a stored procedure to return nearly all of the columns of a single record selected by using a unique key value. The record is in an SQL database, not within an in-memory DataSet. All of the parameters that I wish to have returned to my program are defined as OUTPUT; the two key values are defaulted to INPUT, as there is no need to return them to the calling program. I also have defined the direction of these parameters in the calling SQLDataAdapter function. However, when I run this, the values returned are either the current date for my DateTime parameters, Nothing for my Char parameters or 0's for my integer parameters. When I try testing the sproc alone, by using the "Step Into Stored Procedure" action in Visual Studio, I get a message in the Debug Output window indicating that parameter @TktClassID was expected and not supplied. This is an OUTPUT parameter, which makes me question why I should be providing any sort of value for it within my VB code. Following are the function definition from my SQLDataAdapter class that calls my sproc, and the sproc itself. I appreciate any help that anyone can provide. **FUNCTION DEFINITION FROM SQLDataAdapter Class Public Function Fetch(ByVal ticket As Ticket) As Ticket Dim connbuilder As New System.Data.SqlClient.SqlConnectionStringBuilder connbuilder("Data Source") = "ITS-KCGV7VZSQLEXPRESS" connbuilder("Integrated Security") = "True" connbuilder("Initial Catalog") = "ITSHelpDesk" Using conn As New System.Data.SqlClient.SqlConnection(connbuilder.ConnectionString) Using comm As New System.Data.SqlClient.SqlCommand("dbo.TicketFetch", conn) conn.Open() comm.CommandType = CommandType.StoredProcedure Dim parm As System.Data.SqlClient.SqlParameter 'Add Input parameters (i.e. Key values) 'Add @TicketYear parameter parm = comm.Parameters.Add("@TicketYear", SqlDbType.SmallInt) parm.Value = DBNull.Value parm.Direction = ParameterDirection.Input 'Add @TicketID parameter parm = comm.Parameters.Add("@TicketID", SqlDbType.Int) parm.Value = DBNull.Value parm.Direction = ParameterDirection.Input 'Add Output parameters 'Add @TktClassID parameter parm = comm.Parameters.Add("@TktClassID", SqlDbType.SmallInt) parm.Direction = ParameterDirection.Output parm.SourceColumn = ticket.TktClassID 'Add @TktRequestTypeID parameter parm = comm.Parameters.Add("@TktRequestTypeID", SqlDbType.SmallInt) parm.Direction = ParameterDirection.Output parm.SourceColumn = ticket.TktRequestTypeID 'Add @DateOpened parameter parm = comm.Parameters.Add("@DateOpened", SqlDbType.DateTime) parm.Direction = ParameterDirection.Output parm.SourceColumn = ticket.DateOpened 'Add @DateClosed parameter parm = comm.Parameters.Add("@DateClosed", SqlDbType.DateTime) parm.Direction = ParameterDirection.Output parm.SourceColumn = ticket.DateClosed 'Add @DateLastAssigned parameter parm = comm.Parameters.Add("@DateLastAssigned", SqlDbType.DateTime) parm.Direction = ParameterDirection.Output parm.SourceColumn = ticket.DateLastAssigned 'Add @DateLastStatusChange parameter parm = comm.Parameters.Add("@DateLastStatusChange", SqlDbType.DateTime) parm.Direction = ParameterDirection.Output parm.SourceColumn = ticket.DateLastStatusChange 'Add @TktStatus parameter parm = comm.Parameters.Add("@TktStatusID", SqlDbType.SmallInt) parm.Direction = ParameterDirection.Output parm.SourceColumn = ticket.TktStatusID 'Add @DescrRequest parameter parm = comm.Parameters.Add("@DescrRequest", SqlDbType.VarChar) parm.Direction = ParameterDirection.Output parm.SourceColumn = ticket.DescrRequest 'Add @DescrResolution parameter parm = comm.Parameters.Add("@DescrResolution", SqlDbType.VarChar) parm.Direction = ParameterDirection.Output parm.SourceColumn = ticket.DescrResolution parm.Value = " " 'Handle bug? 'Add @OpenStatus parameter parm = comm.Parameters.Add("@OpenStatus", SqlDbType.Bit) parm.Direction = ParameterDirection.Output parm.SourceColumn = ticket.OpenStatus 'Add @UserLastUpdate parameter parm = comm.Parameters.Add("@UserLastUpdate", SqlDbType.Char) parm.Direction = ParameterDirection.Output parm.SourceColumn = ticket.UserLastUpdate 'Add @DateLastUpdate parameter parm = comm.Parameters.Add("@DateLastUpdate", SqlDbType.DateTime) parm.Direction = ParameterDirection.Output parm.SourceColumn = ticket.DateLastUpdate comm.ExecuteNonQuery() End Using End Using Return ticket End Function **STORED PROCEDURE DEFINITION USE [ITSHelpDesk] GO /****** Object: StoredProcedure [dbo].[TicketFetch] Script Date: 03/24/2008 08:40:53 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Tim Peters -- Create date: 3/17/2008 -- Description: Fetch Ticket from Ticket table -- ============================================= ALTER PROCEDURE [dbo].[TicketFetch] -- Add the parameters for the stored procedure here @TicketYear smallint = 0, @TicketID int = 0, @TktClassID smallint = NULL OUTPUT, @TktRequestTypeID smallint = NULL OUTPUT, @DateOpened datetime = NULL OUTPUT, @DateClosed datetime = NULL OUTPUT, @DateLastAssigned datetime = NULL OUTPUT, @DateLastStatusChange datetime = NULL OUTPUT, @TktStatusID smallint = NULL OUTPUT, @DescrRequest varchar(500) = NULL OUTPUT, @DescrResolution varchar(500) = NULL OUTPUT, @OpenStatus bit = NULL OUTPUT, @UserLastUpdate char(10) = NULL OUTPUT, @DateLastUpdate datetime = NULL OUTPUT AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT @TktClassID = [TktClassID], @TktRequestTypeID = [TktRequestTypeID], @DateOpened = [DateOpened], @DateClosed = [DateClosed], @DateLastAssigned = [DateLastAssigned], @DateLastStatusChange = [DateLastStatusChange], @TktStatusID = [TktStatusID], @DescrRequest = [DescrRequest], @DescrResolution = [DescrResolution], @OpenStatus = [OpenStatus], @UserLastUpdate = [UserLastUpdate], @DateLastUpdate = [DateLastUpdate] FROM [dbo].[Ticket] WHERE [TicketYear] = @TicketYear AND [TicketID] = @TicketID END RETURN
View Replies !
Debug Stored Procedure Using Visual Studio.NET
I am trying to debug a stored procedure following the instruction at: http://support.microsoft.com/default.aspx?scid=kb;EN-US;316549. But when I set the breakpoint in the stored procedure and run the web app, the breakpoint has little white question mark when I mouse over the break point I get this message: "The breakpoint will not currently be hit. Unable to bind SQL breakpoint at this time. Object containing the breakpoint not loaded." Has anyone been able do this successfully?
View Replies !
Visual Studio Step Into Stored Procedure
Hi, I have been developing a web application on Visual Studio 2003.NET and MSSQL2005 Developer Edition for some time without problem. Then suddenly today (and for no apparent reason that I am aware of), I lost the ability to step into stored procedures. I get a message saying I do not have permission to run master.sp_sdidebug. I cannot even find this stored procedure - doesn anyone know where it might live or how to reinstall it? Also, does anyone know what user Visual Studio uses when it is talking to MSSQL? Any help would be most appreciated. Best wishes, Patrick Skelton
View Replies !
Stored Procedure - User Defined Function.
Hi.I'm really new to MSSQL, so therefore my question can sound stupid.Is it possible to use a function written in a module in MS-ACCESS in astored procedure?Or how can it be done, it is a complicated function with loop and more.I'll appreciate all answers also negatives ones.TIAJørn
View Replies !
User-Defined-Function With-in Stored-Procedure??
Does MS-SQL allow us to create an user-defined function within the stored-procedure script? I have been getting errors. It's my first time using the user-defined function with stored-procedure. I welcome your help. Code: CREATE FUNCTION ftnVehicleYearFormattor (@sValue VARCHAR(2)) RETURNS VARCHAR(2) AS BEGIN IF (LEN(@sValue) < 2) SET @sValue = '0' + @sValue RETURN @sValue END Thanks...
View Replies !
How To Use User Defined Function In Stored Procedure?
Hello friends, I want to use my user defined function in a stored procedure. I have used it like , select statement where id = dbo.getid(1,1,'abc') //dbo.getid is a user defined function. procedure is created successfully but when i run it by exec procedurename parameter I get error that says "Cannot find either column "dbo" or the user-defined function or aggregate "dbo.getid", or the name is ambiguous." Can any body help me? Rgds, Kiran.
View Replies !
Problem Debbuging A Stored Procedure In Visual Studio
Hello,I want to debug a Stored Procedure in the VIsual Studio. Actually I managed to do that, but only from Step into SP and Execute. I want to put a breakpoint in the procedure and when it is hit to stop, but if I Run(With Debug) my Site it doesn't stop at the breakpoint in the SP. I put a mark in the project options to debug SQL. What can be wrong?
View Replies !
Stored Procedure And Calling User Defined Function
I seem to be getting tasks that I am not familiar with these days. I am a guy that has coded it all in the asp page or in the code behind in .NET. This problem is outlined below and I need a help / advice on doing this. I had the flow of the 3 parts to it expanded below. A call is made to a Stored Procedure, The SP then calls a user defined function that runs SQL, this returns a 1 or 0 to the SP which then returns the value back to the call on the asp page. This is a lot I know but it is the way the lead guy wants it done. Any help so I can keep most of the hair I have left is appreciated :-) Short list of process flow: 1. Form.asp calls to rx_sp_HasAccessToClient in SQL SERVER 2. rx_sp_HasAccessToClient then calls ab_HasAccessToClient 3. ab_HasAccessToClient runs SQL command on db and sends return bit back to rx_sp_HasAccessToClient 4. rx_sp_HasAccessToClient then sends this back to the call in the Form.asp page 5. Form.asp then checks the Boolean and if 1 then show or if 0 then deny. <FLOW WITH CODE AND FUNCTIONS :> This is not the correct syntax but is showing what I understand sort of how this is to be done so far. This panel loads up the Vendors and id's when the user clicks on the link "view detailed list of vendors associated with this client". This is the beginning of the process. This is code in Form.asp 'PANEL ONE XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXX---- > If ValidateInput(Request.Querystring("Postback"))="FormDetails" then 'Check Postback Type 'We need to load up vendors associated with the current client. '--------- CHECK ACCESS HERE via function ab_HasAccessToClient -------- 'If the call returns 1, then the employee has access. 'Otherwise, just write out "Access to this client is denied." 'CALL SP - Not sure what parameters need to go with it or its syntax Execute_SP("rx_sp_HasAccessToClient '" & ClientSSN & "', 1) 'When it returns can check it here........ if ab_HasAccessToClient result is a 1 then 'boolean would be 1 so show panel Else 'boolean would be 0 so show access denied 'allow them to go back to the original page. end if 'PANEL ONE XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXX---- > ON SQL SERVER: Stored Procedure ---------------------------------------------------------- -------------------------------- rx_sp_HasAccessToClient CREATE PROCEDURE [dbo].[ rx_sp_HasAccessToClient] @EmployeeID INT, @ClientSSN varchar(50), @ReturnBitValue = OUTPUT /* ' Parameters here passed via call from Form.asp - not sure what is passed yet. */ AS set nocount on /* Written by Mike Belcher 9/27/2007 for Form.asp 'Calls ab_HasAccessToClient function - not sure of the syntax as of yet, just making flow. 'Gets return bit and passes that back to the call from Form.asp */ GO ---------------------------------------------------------- -------------------------------- ON SQL SERVER: User-Defined Function ---------------------------------------------------------- -------------------------------- ab_HasAccessToClient CREATE FUNCTION ab_HasAccessToClient (@employeeID INT, @ClientSSN VARCHAR(50)) @ClientSSN varchar(50), @EmployeeID, @ReturnBitValue = OUTPUT AS SELECT 1 FROM tblEmployeesClients ec INNER JOIN tblClients c ON ec.ClientID = c.ClientSSN INNER JOIN tblEmployees e ON ec.Employee = e.EmployeeLogInName WHERE e.EmployeeID= @EmployeeID AND c.InActiveClient=0 AND c.ClientSSN = @ClientSSN 'Some Code here to save result bit .. RETURN @ReturnBitValue 'Back to rx_sp_HasAccessToClient ---------------------------------------------------------- -------------------------------- </FLOW WITH CODE AND FUNCTIONS :>
View Replies !
Stored Procedure And Calling User Defined Function
I seem to be getting tasks that I am not familiar with these days. I am a guy that has coded it all in the asp page or in the code behind in .NET. This problem is outlined below and I need a help / advice on doing this. I had the flow of the 3 parts to it expanded below. A call is made to a Stored Procedure, The SP then calls a user defined function that runs SQL, this returns a 1 or 0 to the SP which then returns the value back to the call on the asp page. This is a lot I know but it is the way the lead guy wants it done. Any help so I can keep most of the hair I have left is appreciated :-) Short list of process flow: 1. Form.asp calls to rx_sp_HasAccessToClient in SQL SERVER 2. rx_sp_HasAccessToClient then calls ab_HasAccessToClient 3. ab_HasAccessToClient runs SQL command on db and sends return bit back to rx_sp_HasAccessToClient 4. rx_sp_HasAccessToClient then sends this back to the call in the Form.asp page 5. Form.asp then checks the Boolean and if 1 then show or if 0 then deny. <FLOW WITH CODE AND FUNCTIONS :> This is not the correct syntax but is showing what I understand sort of how this is to be done so far. This panel loads up the Vendors and id's when the user clicks on the link "view detailed list of vendors associated with this client". This is the beginning of the process. This is code in Form.asp 'PANEL ONE XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXX---- > If ValidateInput(Request.Querystring("Postback"))="Fo rmDetails" then 'Check Postback Type 'We need to load up vendors associated with the current client. '--------- CHECK ACCESS HERE via function ab_HasAccessToClient -------- 'If the call returns 1, then the employee has access. 'Otherwise, just write out "Access to this client is denied." 'CALL SP - Not sure what parameters need to go with it or its syntax Execute_SP("rx_sp_HasAccessToClient '" & ClientSSN & "', 1) 'When it returns can check it here........ if ab_HasAccessToClient result is a 1 then 'boolean would be 1 so show panel Else 'boolean would be 0 so show access denied 'allow them to go back to the original page. end if 'PANEL ONE XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXX---- > ON SQL SERVER: Stored Procedure ---------------------------------------------------------- -------------------------------- rx_sp_HasAccessToClient CREATE PROCEDURE [dbo].[ rx_sp_HasAccessToClient] @EmployeeID INT, @ClientSSN varchar(50), @ReturnBitValue = OUTPUT /* ' Parameters here passed via call from Form.asp - not sure what is passed yet. */ AS set nocount on /* Written by Mike Belcher 9/27/2007 for Form.asp 'Calls ab_HasAccessToClient function - not sure of the syntax as of yet, just making flow. 'Gets return bit and passes that back to the call from Form.asp */ GO ---------------------------------------------------------- -------------------------------- ON SQL SERVER: User-Defined Function ---------------------------------------------------------- -------------------------------- ab_HasAccessToClient CREATE FUNCTION ab_HasAccessToClient (@employeeID INT, @ClientSSN VARCHAR(50)) @ClientSSN varchar(50), @EmployeeID, @ReturnBitValue = OUTPUT AS SELECT 1 FROM tblEmployeesClients ec INNER JOIN tblClients c ON ec.ClientID = c.ClientSSN INNER JOIN tblEmployees e ON ec.Employee = e.EmployeeLogInName WHERE e.EmployeeID= @EmployeeID AND c.InActiveClient=0 AND c.ClientSSN = @ClientSSN 'Some Code here to save result bit .. RETURN @ReturnBitValue 'Back to rx_sp_HasAccessToClient ---------------------------------------------------------- -------------------------------- </FLOW WITH CODE AND FUNCTIONS :>
View Replies !
Visual Studio Database File And SQL Server Management Studio Express Question
I have a database in my "App_Data" folder of my visual studio project. I can view it fine in Visual Studio's built-in tools for managing a database attached to a solution. However i recently started playing around with the SQL Server Management Studio Express program. When i attach my database to Management Studio, and try to run my program it crashes. I think it might be a permissions error?!? When i detatch it and reattach it in visual studio it runs fine again. Any suggestions? ThanksJason
View Replies !
Stored Procedure Only Worked After I Rebuild Web Site In Visual Studio
Hi,today I was modifying a number of Stored Procedures and upon altering my code to reflect the changes in the Stored Procedures my my database (SQL Express) was returning no values where it should be, and was before.I recompiled the whole website and one of the stored procedures worked. This made no sense, so I built the site again, and guess what, the next stored procedure worked. This worked for the final procedure aswell.So to summarise, I had to rebuild for the stored procedures to work again. Also of note is that when they were not working, I was getting no errors, just simply no data coming back from the procedure. And to make them work again, I had to rebuild the site 3 times in Visual Studio!!!Im using Visual Studio 2005, Sql Express 2005, and Microsoft Enterprise Library for accessing the database. Has anyone had this experience, or can anyone explain it?Conor
View Replies !
How To Save A Stored Procedure With Management Studio?
Hi, i can make and save a stored procedure in Visual Web Developer (via Database Explorer). It appears then in the list op stored procedure in Management Sudio. But how to do the same in Management Studio? When i make a sp and i want to save it, Management Studio asks me a name, but put the file in a Projects directory in 'My documents'. It never appears in the list of sp. Thanks tartuffe
View Replies !
How To Exec SQL User Defined Function?
Hi,How to exec a SQL user defined function in query analyzer when it accepts parameters.. I know for a stored procedure we can write EXEC nameofstored procedure abc (@abc is the parameter passed).. But How to run a SQL function ?Thanks
View Replies !
User Defined Functions And EXEC
I'm using MS SQL Server 2005. I want to simulate a table, using a Multi-statement Table-Value User-Defined Function, but I need the function build the SQL statement from scratch each time so I can dynamically define values like the table it references. The only way I know how to run the query after it has been defined in this manner is to run an EXEC command. However I'm getting an error basically saying that the EXEC command is off limits in a User Defined Function. The Exsact Error is: >[Error] Script lines: 1-108 ------------------------ Invalid use of side-effecting or time-dependent operator in 'EXECUTE STRING' within a function. Is there some way to get around this limitation of User Defined Functions. Or perhaps a way to simulate the functionality of Multi-statement Table-Value User-Defined Functions in a Store Procedure, specifically the ability to run where statements, or transform the data on the fly without re-writing the Stored Procedure every time. The code I’m trying to run is below. (Note: The code works as a stored procedure, so I'm sure that the core of the statment is correct) CREATE FUNCTION [dbo].[TrendLine] ( @Summary as smallint, @Start as datetime, @End as datetime, @Table as varchar(100), @X as varchar(100), @Count as varchar(100), @Duration as varchar(100) ) RETURNS @TrendLineTable table ( DATE_DAY datetime , EQ_REGION varchar(25) , EQ_MARKET_CLUSTER varchar(30) , Y float , X int , DURATION float , FORMULA varchar(100) , a float , b float , EX int , EY float , EX2 int , EXY float , N int ) AS BEGIN DECLARE @SQL as varchar(3000) , @CountText as varchar(150) , @StartText as varchar(50) , @EndText as varchar(50) SET @StartText = 'cast( ' + char(39) + cast( @Start as varchar(20) ) + char(39) + ' as datetime ) ' SET @EndText ='cast( ' + char(39) + cast( @End as varchar(20) ) + char(39) + ' as datetime ) ' IF @Summary = 1 BEGIN SET @CountText = 'sum' END ELSE BEGIN SET @CountText = 'count' END SET @SQL = 'INSERT INTO @TrendLineTable DECLARE TrendlineC cursor for SELECT a.DATE_DAY , s2.EQ_REGION , s2.EQ_MARKET_CLUSTER , ( ( EY - ( b * EX ) ) / N ) + ( b * X ) AS Y , X , Y AS DURATION , cast( b as varchar(100) ) + ' + char(39) + 'x + ' + char(39) + ' + cast( ( EY - ( b * EX ) ) / N as varchar(100) ) AS FORMULA , ( EY - ( b * EX ) ) / N AS a , b , EX , EY , EX2 , EXY , N FROM ( SELECT EQ_REGION , EQ_MARKET_CLUSTER , sum( X ) AS EX , sum( Y ) AS EY , sum( X2 ) AS EX2 , sum( XY ) AS EXY , count( X ) AS N , ( ( count( X ) * sum( XY ) ) - ( sum( X ) * sum( Y ) ) ) / ( ( count( X ) * sum( X2 ) ) - POWER( sum( X ), 2 ) ) AS b FROM ( SELECT ' + @X + ' AS DATE_DAY , EQ_REGION , EQ_MARKET_CLUSTER , row_number() over (partition by EQ_MARKET_CLUSTER order by ' + @X + ' ) AS X , cast( sum( ' + @Duration + ' ) as float ) / ' + @CountText + '( ' + @Count + ' ) AS Y , POWER( row_number() over (partition by EQ_MARKET_CLUSTER order by ' + @X + ' ), 2) X2 , row_number() over (partition by EQ_MARKET_CLUSTER order by ' + @X + ' ) * cast( sum( ' + @Duration + ' ) as float ) / ' + @CountText + ' ( ' + @Count + ' ) AS XY FROM ' + @Table + ' WHERE ' + @X + ' >= ' + @StartText + ' AND ' + @X + ' < ' + @EndText + ' GROUP BY ' + @X + ' , EQ_REGION , EQ_MARKET_CLUSTER ) s1 GROUP BY EQ_REGION , EQ_MARKET_CLUSTER ) s2 INNER JOIN ( SELECT ' + @X + ' AS DATE_DAY , EQ_MARKET_CLUSTER , row_number() over (partition by EQ_MARKET_CLUSTER order by ' + @X + ' ) AS X , cast( sum( ' + @Duration + ' ) as float ) / ' + @CountText + '( ' + @Count + ' ) AS Y FROM ' + @Table + ' WHERE ' + @X + ' >= ' + @StartText + ' AND ' + @X + ' < ' + @EndText + ' GROUP BY ' + @X + ' , EQ_REGION , EQ_MARKET_CLUSTER ) a ON s2.EQ_MARKET_CLUSTER=a.EQ_MARKET_CLUSTER' EXEC ( @SQL ) RETURN END
View Replies !
Strange Problew With User Defined Function Or Stored Procedure
I am trying to add a simple case statement to a stored procedure oruser defined function. However when I try and save thefunction/procedure I get 2 syntax errors. Running the query in queryanalyser works fine and a result is given with no syntax errors. Ibelieve its something to do with the spaces in the field names. Not mychoice as its an existing system I have to work around. Any helpgreatly appreciatedSQL QueryDECLARE @pfid VARCHAR(100)SET @pfid = '000101'SELECTCaseWHEN GetDate()BETWEEN gg_shop_product.sale_start AND gg_shop_product.sale_endTHEN((((gg_shop_product.Sale_Price/100)/1.175)-("dbo"."Navision_Cost_Prices"."Unit Cost" *Navision_Codes."Navision QTY"))/((gg_shop_product.Sale_Price/100)/1.175)) * 100WHEN dbo.Navision_Cost_Prices."Unit Cost" = 0Then '100'WHEN gg_shop_product.list_price > 0 THEN((((gg_shop_product.List_Price /100)/1.175)-("dbo"."Navision_Cost_Prices"."UnitCost"*dbo.Navision_Codes."NavisionQTY"))/((gg_shop_product.List_Price/100)/ 1.175)) * 100END as 'Margin'from gg_shop_product INNER JOINgg_shop_variant ON gg_shop_product.pf_id =gg_shop_variant.pf_id LEFT OUTER JOINgg_shop_cost_prices ON gg_shop_product.pf_id =gg_shop_cost_prices.pf_id INNER JOINNavision_Codes ON gg_shop_variant.sku = Navision_Codes.skuINNER JOIN NAVISION_Cost_Prices ON Navision_Codes."Navision No" =Navision_Cost_Prices.NoWHERE gg_shop_product.pf_id = @pfidUser Defined Function (Errors Line 11 & 15)CREATE FUNCTION dbo.get_Margin(@pfid VARCHAR(100), @dtNow DATETIME)RETURNS DECIMAL ASBEGINDECLARE @Return as DECIMALSET @Return = (SELECTCaseWHEN @dtNowBETWEEN gg_shop_product.sale_start AND gg_shop_product.sale_endTHEN((((gg_shop_product.Sale_Price/100)/1.175)-(dbo.Navision_Cost_Prices."Unit Cost" *Navision_Codes."Navision QTY"))/((gg_shop_product.Sale_Price/100)/1.175)) * 100WHEN dbo.Navision_Cost_Prices."Unit Cost" = 0Then '100'WHEN gg_shop_product.list_price > 0 THEN((((gg_shop_product.List_Price /100)/1.175)-("dbo"."Navision_Cost_Prices"."UnitCost"*dbo.Navision_Codes."NavisionQTY"))/((gg_shop_product.List_Price/100)/ 1.175)) * 100END as 'Margin'from gg_shop_product INNER JOINgg_shop_variant ON gg_shop_product.pf_id =gg_shop_variant.pf_id LEFT OUTER JOINgg_shop_cost_prices ON gg_shop_product.pf_id =gg_shop_cost_prices.pf_id INNER JOINNavision_Codes ON gg_shop_variant.sku = Navision_Codes.skuINNER JOIN NAVISION_Cost_Prices ON Navision_Codes."Navision No" =Navision_Cost_Prices.NoWHERE gg_shop_product.pf_id = @pfid)RETURN @ReturnEND
View Replies !
User Defined Data Type Used In Stored Procedure Parameters
I have several stored procedures with parameters that are defined withuser defined data types. The time it takes to run the procedures cantake 10 - 50 seconds depending on the procedure.If I change the parameter data types to the actual data type such asvarchar(10), etc., the stored procedure takes less that a second toreturn records. The user defined types are mostly varchar, but someothers such as int. They are all input type parameters.Any ideas on why the stored procedure would run much faster if notusing user defined types?Using SQL Server 2000.Thanks,DW
View Replies !
SQL Sever 2005 Management Studio - Connect As Different Windows User
I want to connect to a SQL server from my workstation using the application in the Subject, but I need to connect as a different Windows user. Basically, I login to my workstation with my "Joe User" account, but for servers I used my "Joe Admin" account for security purposes. However, the Management console autofills in my workstation username when I select "Windows authentication" for the connection, and grays out the username/password fields so that I cannot specify another set of credentials. Is there a way to change this behavior? Otherwise I will need to termserv into a server using my administrative account, install the management software there, and then begin the process. That is a lot of unecessary overhead that I would like to avoid. Thank You, John G
View Replies !
How Do I Create A Stored Procedure In SQL Server Management Studio Express?
I have wrriten many stored procedures in the past without issue, but this is my first time using SQL Server Management Studio Express. I am having trouble creating a new stored procedure. Here is what's happening: I am opening my database, right clicking on "Stored Procedures" and selecting "New Stored Procedure". A new window opens with a template for creating a stored procedure. The window is called: "SQLEXPRESS.DBName - sqlquery1.sql". I then type up my stored procedure without an issue. However, when I go to save the stored procedure it wants to save it as a separate file in a projects folder. It does not become part of the DB (as far as I can tell). When I used to use Enterprise Manager (not an option anymore) this never happened. I'm sure I'm doing something dumb. Can someone enlighten me. Thanks,Chris
View Replies !
How To Test Stored Procedure With Output Parameters In Management Studio
I have this SP: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[GetSessionInformation] @CustomerID int, @Success bit OUTPUT, @Email VarChar(55) OUTPUT, @FirstName VarChar(55) OUTPUT, @LastName VarChar(50) OUTPUT, @PhoneNumber VarChar(50) OUTPUT, @CompanyName VarChar(50) OUTPUT AS SET NOCOUNT ON DECLARE @UserKey AS int SELECT @CustomerID = CustomerID FROM Customers WHERE CustomerID = @CustomerID IF @CustomerID IS NULL BEGIN SET @Success = 0 END ELSE BEGIN SET @Success = 1 END BEGIN SELECT customerID, Email, FirstName, LastName, PhoneNumber, CompanyName FROM Customers WHERE CustomerID = @UserKey How do I test it in management studio? When I run a EXECUTE GetSessionInformation 56 I get this error: Procedure 'GetSessionInformation' expects parameter '@Success', which was not supplied. Thanks for any help!
View Replies !
Attaching A Stored Procedure To A Database In MS SQL Server Management Studio
Hello all! Quick question... I've created my DB in MSSQLSMS, then attempted to created a stored procedure for it. The code itself is fine, I just need to know how to actually attach it so that it appears in the "Stored Procedures" section of my Database. I have Right Clicked on Stored Procedures > New Stored Procedure... > Edited as required > Save When I attempt to save it, it prompts me for a file. That's fine, did that - but I can't see ANY way to actually attach this to the DB. Any help is appreciated! Daniel Davies
View Replies !
How To Empty A Stored Procedure In Ms Sql Server Management Studio Express
hi everyone, I have a db based on the Tracking_Schema.sql / Tracking_Logic.sql (find in &windir%/Microsoft.NET/Framework/v3.0/Windows Workflow Foundation/SQL/EN), so after executing both of them I get several stored procedures, especially dbo.GetWorkflows. And I have a solution in VS05 which when executed is filling this stored procedure with Instance-Id´s. My question is: how is the working command (like exec, truncate,..) to empty my st.procedure, not to drop/delete it? Thanks in advance, best regards bg
View Replies !
Debugging SQL Server 2005 Stored Proc With Visual Studio 2005
Hello, we have a SQL server 2005 with Visual studio Prof. 2005 in the employment. The debuggers function only in Visual studio correctly, as long as no code on the SQL server must be implemented. If a BREAK POINT in a Stored Procedure is set, this is not activated, since this cannot be bound. Does someone know, what it lies and can like one it eliminate? Thank you for your assistance in advance. Yours sincerely Big_Ben_31 This entry was translated automatically with the translation service babel.altavista.com from the German into English.
View Replies !
Unable To Debug SQL Server 2005 Stored Procedures From Another Workstation Running Visual Studio 2005
I'm having some problems debugging SQL Server stored procedures on a SQL Server 2005 server. I have installed Visual Studio 2005 on a workstation running Windows XP, now I'm trying to debug a ASP.Net web application that has some code that executes the stored procedures on a Windows 2003 Server running SQL Server 2005. I opened VS2005 ... created a connection to the SQL Server 2005 instance ... open the Stored procedure ... right click the stored procedure name and selected Step into Stored Procedure and the following message is displayed: Unable to start T-SQL debugging.Could not attach to SQL Server process on 'ServerName'. Any ideas. Thanks,
View Replies !
How Can I Copy The Content Of MS Access Table In Database SQL Using Visual Studio Basic
Code Snippet Hi there, I'm struggeling for more than a week now with this problem, without a finding the solution. I have two databases, MS Access and SQL Server 2005 Express Edition Using a procedure in Visual Studio i would like to copy all the records from one table in MS Access into an existing table in SQL Server (the tables have the same name and the same layout) I tried to prepare one Dataset to copy from Access into SQL Server but when i run the command 'DaSQL.Update(DsSQL, "Tabella") nothing happens (not even an exeption has been raised), looking during debug, the DataSet seems filled though... Please could anyone explain what's wrong and / or is there a more quicker way to copy data from a table to another? Note i woul have as a final goal to get data from an AS400 database by ODBC, manage it, and put it on SQL Server for a 'data mining' scope (eliminating the use of MS Access, not suited for FE-BE). the procedure goes like this; ' Create a connection to the MS Access Database Dim connectionToAccess As New OleDbConnection(DBConnectionAccString) strsql = "SELECT * FROM [TABELLA]" connectionToAccess.Open() Dim DaAccess As New OleDbDataAdapter(strsql, connectionToAccess) Dim DsAccess As New DataSet("ACCESS") DaAccess.FillSchema(DsAccess, SchemaType.Source, "Tabella") DaAccess.Fill(DsAccess, "Tabella") ' Create a connection to the SQL Database Dim connectionToSQL As New SqlConnection(DBConnectionSQLString) connectionToSQL.Open() Dim DaSQL As New SqlDataAdapter(strsql, connectionToSQL) Dim DsSQL As New DataSet("SQL") DaSQL.FillSchema(DsSQL, SchemaType.Source, "Tabella") DaSQL.Fill(DsAccess, "Tabella") DaSQL.Update(DsSQL, "Tabella") Note I tried also the following, withou a result; DsSQL = DsAccess.Copy DaSQL.Update(DsSQL, "Tabella") Please is there someone who could respond !!???
View Replies !
Accessing SQL Server 2005 Stored Procedures From Visual Studio.NET
Hello, What permissions do I need to set on our new SQL Server 2005 test server so that I can see the stored procedures, views and tables from Visual Studio development environment. Example, I can see my older SQL Server 7 tables, edit them, write stored procedures, and so on. But while I can see the SQL Server 2005 and its databases, the folder underneath are empty and I cannot right-click to create New stored procedure or table. My guess is that there is security involved, if so, what do I set on the new server? If I'm wrong, and it's something else entirely, please advise. Thanks!
View Replies !
A .NET Framework Error Occurred During Execution Of User-defined Routine Or Aggregate -While Creating A SQL SERVER 2005 Stored Prodecure In VS.NET 2005
Running [dbo].[insertlogin] ( @log = hiten, @pas = hiten ).A .NET Framework error occurred during execution of user-defined routine or aggregate "insertlogin": System.Data.SqlClient.SqlException: Must declare the scalar variable "@Log".System.Data.SqlClient.SqlException: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.ProcessMessagesAndThrow(Boolean ignoreNonFatalMessages) at Microsoft.SqlServer.Server.SmiEventSink_Default.ProcessMessagesAndThrow(Boolean ignoreNonFatalMessages) at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at SqlServerProject1.StoredProcedures.insertlogin(SqlString log, SqlString pas).No rows affected.(0 row(s) returned)@RETURN_VALUE = Finished running [dbo].[insertlogin]. ***************************************************************all i am trying to do is : creating a SP in VS using managed code and then trying to execute it. But every time i get the above error. If you can tell me how to edit connection string in this that would be very helpful. At present i am using : Using conn As New SqlConnection("context connection=true") I tried to do "" ALTER ASSEMBLY SqlServerProject1 WITH PERMISSION_SET=EXTERNAL_ACCESS""but i get this error "" Msg 10327, Level 14, State 1, Line 1ALTER ASSEMBLY for assembly 'SqlServerProject1' failed because assembly 'SqlServerProject1' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission. If you have restored or attached this database, make sure the database owner is mapped to the correct login on this server. If not, use sp_changedbowner to fix the problem.""" *********************************************************************Plz help
View Replies !
Execute Stored Procedure (with Parameters) With An &"exec&" Command
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.
View Replies !
Help: Why Excute A Stored Procedure Need To More 30 Seconds, But Direct Excute The Query Of This Procedure In Microsoft SQL Server Management Studio Under 1 Second
Hello to all, I have a stored procedure. If i give this command exce ShortestPath 3418, '4125', 5 in a script and excute it. It takes more 30 seconds time to be excuted. but i excute it with the same parameters direct in Microsoft SQL Server Management Studio , It takes only under 1 second time I don't know why? Maybe can somebody help me? thanks in million best Regards Pinsha My Procedure Codes are here:set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= ALTER PROCEDURE [dbo].[ShortestPath] (@IDMember int, @IDOther varchar(1000),@Level int, @Path varchar(100) = null output ) AS BEGIN if ( @Level = 1) begin select @Path = convert(varchar(100),IDMember) from wtcomValidRelationships where wtcomValidRelationships.[IDMember]= @IDMember and PATINDEX('%'+@IDOther+'%',(select RelationshipIDs from wtcomValidRelationships where IDMember = @IDMember) ) > 0 end if (@Level = 2) begin select top 1 @Path = convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember) from wtcomValidRelationships as A, wtcomValidRelationships as B where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0 and PATINDEX('%'+@IDOther+'%',B.RelationshipIDs) > 0 end if (@Level = 3) begin select top 1 @Path = convert(varchar(100),A.IDMember)+ '-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember) from wtcomValidRelationships as A, wtcomValidRelationships as B, wtcomValidRelationships as C where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0 and charindex(convert(varchar(100),C.IDMember),B.RelationshipIDs) > 0 and PATINDEX('%'+@IDOther+'%',C.RelationshipIDs) > 0 end if ( @Level = 4) begin select top 1 @Path = convert(varchar(100),A.IDMember)+ '-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember) from wtcomValidRelationships as A, wtcomValidRelationships as B, wtcomValidRelationships as C, wtcomValidRelationships as D where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0 and charindex(convert(varchar(100),C.IDMember),B.RelationshipIDs) > 0 and charindex(convert(varchar(100),D.IDMember), C.RelationshipIDs) > 0 and PATINDEX('%'+@IDOther+'%',D.RelationshipIDs) > 0 end if (@Level = 5) begin select top 1 @Path = convert(varchar(100),A.IDMember)+ '-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember)+'-'+convert(varchar(100),E.IDMember) from wtcomValidRelationships as A, wtcomValidRelationships as B, wtcomValidRelationships as C, wtcomValidRelationships as D, wtcomValidRelationships as E where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0 and charindex(convert(varchar(100),C.IDMember),B.RelationshipIDs) > 0 and charindex(convert(varchar(100),D.IDMember), C.RelationshipIDs) > 0 and charindex(convert(varchar(100),E.IDMember),D.RelationshipIDs) > 0 and PATINDEX('%'+@IDOther+'%',E.RelationshipIDs) > 0 end if (@Level = 6) begin select top 1 @Path = '' from wtcomValidRelationships end END
View Replies !
(&"Could Not Find Stored Procedure ''.&") When Calling A User Defined Procedure
Hi,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 Replies !
|
TRACKER
