Visual Basic Slow Down Sql Server 2000 Running A Stored Procedure
Nov 30, 2007
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 2 Replies
ADVERTISEMENT
Jan 23, 2002
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 1 Replies
View Related
Jan 5, 2007
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 6 Replies
View Related
Mar 24, 2008
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 3 Replies
View Related
Jan 9, 2008
We have a stored procedure which is running fine on a SQL server 2000 from Query Analyzer. However, when we try to execute the same stored procedure from ADO.NET in an executable, the execution is hung or takes extremely long. Does anyone have any ideas or suggestions about how it could happen and how to fix. thanks
View 22 Replies
View Related
Jul 20, 2005
We have forms written in Visual Basic 6.0. They were previouslywritten for an Access database which was moved to SQL Server 2000.Here's the connect string I used:ODBC;UID=visualbasic;PWD=password;SERVER=MPSQL;DRI VER={SQLSERVER};DATABASE=MPPhotoThe user visualbasic and the user logged in to the computer have fullrights to the database.When they open the form which is based on a view it is opened as readonly with an error 3027. The data control is not set to read only.The database file is not read only. All of the permissions are set toallow this user read, write and modify.Can anyone hep me?
View 3 Replies
View Related
Jan 17, 2008
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 22 Replies
View Related
Jan 23, 2008
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 6 Replies
View Related
Apr 28, 2004
I am using the SQL Server 2000 query analyser to write stored procedures for an SQL Server 2000 database.
I would like to find a debugger to help me "look into" my stored procedures as they are executed (e.g. to watch a Cursor fill a table row by row, see the contents of temporary tables as a stored procedure progresses, etc.)
Could anybody, please, direct me to-
* Any debugger that does this
OR
* Simple instructions how to run stored procedures from Visual Basic. I suspect :) that VB's debugger may be able to look into the SQL as it is being executed, but I don't actually know how to use VB itself.
Thanks very much,
Angelos
View 3 Replies
View Related
Apr 18, 2008
Hi there,
I was wondering if someone can point out the error or the thing I shouldn't be doing in a stored procedure on SQL Server 2005. I want to switch from SQL Server 2000 to SQL Server 2005 which all seems to work just fine, but one stored procedure is causing me headache.
I could pin the problem down to this query:
DECLARE @Package_ID bigint
DECLARE @Email varchar(80)
DECLARE @Customer_ID bigint
DECLARE @Payment_Type tinyint
DECLARE @Payment_Status tinyint
DECLARE @Booking_Type tinyint
SELECT @Package_ID = NULL
SELECT @Email = NULL
SELECT @Customer_ID = NULL
SELECT @Payment_Type = NULL
SELECT @Payment_Status = NULL
SELECT @Booking_Type = NULL
CREATE TABLE #TempTable(
PACKAGE_ID bigint,
PRIMARY KEY (PACKAGE_ID))
INSERT INTO
#TempTable
SELECT
PACKAGE.PACKAGE_ID
FROM
PACKAGE (nolock) LEFT JOIN BOOKING ON PACKAGE.PACKAGE_ID = BOOKING.PACKAGE_ID
LEFT JOIN CUSTOMER (nolock) ON PACKAGE.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID
LEFT JOIN ADDRESS_LINK (nolock) ON ADDRESS_LINK.SOURCE_TYPE = 1 AND ADDRESS_LINK.SOURCE_ID = CUSTOMER.CUSTOMER_ID
LEFT JOIN ADDRESS (nolock) ON ADDRESS_LINK.ADDRESS_ID = ADDRESS.ADDRESS_ID
WHERE
PACKAGE.PACKAGE_ID = ISNULL(@Package_ID,PACKAGE.PACKAGE_ID)
AND PACKAGE.CUSTOMER_ID = ISNULL(@Customer_ID,PACKAGE.CUSTOMER_ID)
AND PACKAGE.PAYMENT_TYPE = ISNULL(@Payment_Type,PACKAGE.PAYMENT_TYPE)
AND PACKAGE.PAYMENT_STATUS = ISNULL(@Payment_Status,PACKAGE.PAYMENT_STATUS)
AND BOOKING.BOOKING_TYPE = ISNULL(@Booking_Type,BOOKING.BOOKING_TYPE)
-- If this line below is included the request will take about 90 seconds whereas it takes 1 second if it is outcommented
--AND ADDRESS.EMAIl LIKE '%' + ISNULL(@Email, ADDRESS.EMAIL) + '%'
GROUP BY
PACKAGE.PACKAGE_ID
DROP TABLE #TempTable
The request is performing quite well on the SQL Server 2000 but on the SQL Server 2005 it takes much longer. I already installed the SP2 x64, I'm running the SQL Server 2005 on a x64 environment.
As I stated in the comment in the query it takes 90 seconds to finish with the line included, but if I exclude the line it takes 1 second.
I think there must be something wrong with the join's or something else which has maybe changed in SQL Server 2005. All the tables joined have a primary key.
Maybe you folks can spot the error / mistake / wrong type of doing things easily.
I would appreciate any help you can offer me to solve this problem.
On the web I saw that there is a Cumulative Update 4 for the SP2 which fixes the following:
942659 (http://support.microsoft.com/kb/942659/)
FIX: The query performance is slower when you run the query in SQL Server 2005 than when you run the query in SQL Server 2000
Anyhow I think the problem is something else, I haven't tried out the cumulative update yet, as I think it is something different, more general why this query takes ages to process.
Thanks again for any help
Best regards,
Pascal
View 9 Replies
View Related
Jan 27, 2006
Hi friends
after working visual studio (on my report model project) few minutes it runs too slow. i mean clicking on entities ,attributes takes ages to finish. I opened task manager i see "devenv.exe" is taking more than 800,000 k !!
am using sql server 2005 standard edition.
have you seen similar problem.
Thanks for your help.
View 19 Replies
View Related
Aug 2, 2000
Is it possible to write extended stored procs in VB? I do not know C or C++ or Perl or whatever ....
How about calling a COM from a stroed procedure???
Thanks,
Judith
View 4 Replies
View Related
Mar 9, 2001
You know this sample ODS dll project:
$80oolsdevtoolssamplesodsxp_hello
I need to find a template like this in Visual Basic.
On this site I read through the xp_Encrypt project which was developed in VB. I did not see source code or a downloadable project file.
My searching on the internet hasn't yielded any practicle results, all examples are either how to use an extended stored procedure or are a MSVC++ project.
Anyone find VB related resources, anywhere? Books, TV, magazines????
View 1 Replies
View Related
Nov 18, 2005
I'm trying to set up Service Broker Services on SQL 2005 x86. I've got two services set up, and a stored procedure associated with one of them.
View 5 Replies
View Related
Jun 6, 2005
Hi,
We are having SQL2000 Advance Server.
4 processor with hypherthreading, Memory 4 GB and it is a high transactional OLTP server. We are also running Transaction Replication on that server.
We recently bought Double Take and implemented on the server with File Difference with block check sum option for Disaster Recovery Purpose.
The Queue and the Log file folder is on local drive where the system doesn't use that folder except double take.
We are replicating from Source to Taget thru the WAN (DS3).We are replicating approx. 200 Gig of Data but just the difference.
Now the CPU usage is normal,Memory utilization is normal, but the network is a major problem as the Applications connecting to the Server timesout and the applications running very slow.
We have set just like the Tech support recommended.
I would appreciate, if someone give us some recommendations to run the double take without any problem.
Thanks in advance.
Anu
View 3 Replies
View Related
Jan 3, 2004
Hi experts I am new to SQL Server and have got to do a project using VB & SQL Server. right now i got to prepare case study document wherein i got to mention the pros & cons of using VB+sql server... kindly help me. Thanks in advance.
View 1 Replies
View Related
Aug 3, 2001
is there any way to use Visual Basic and MSSQL without using ODBC?
please let me know.
View 1 Replies
View Related
Sep 18, 2006
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 2 Replies
View Related
Jan 17, 2006
I found the following article:
http://msdn.microsoft.com/vbrun/vbfusion/usingsqlexpress/
downloaded the files for it
ran the program and encounter an error about Error Locating Server
anyone tried using that code?
any ideas what I am missing?
thanks.
View 1 Replies
View Related
Jun 3, 2008
HI ALL
I have created a stored procedure for a routine task to be performed periodically in my application. Say, i want to execute my stored procedure at 12:00 AM daily.
How can I add my stored procedure to the SQL server agent jobs??
Any Idea..
View 1 Replies
View Related
Oct 8, 2007
Hi, i am new to sql server and visual basic, i need to connect my sql server database to a new application i've developed in visual basic 2005 express. Can any one tell me the steps to do this. Many Thanks.
View 2 Replies
View Related
Apr 20, 2007
Hi everybody:
I go to Server Explorer, right click on Data Connection, input the server name (that is, my own computer), give a new name to the database to create, and then inevitably get the error window saying:
An error occurred while establishing a connection to the server. When connectiong to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL server does not allow remote connections. (Provider: Named Pipes Provider, error 40, could not open a conection to SQL Server)
So, how to open a connection to SQL server 2005? I looked in the msdn but found absolutely nothing.
Please help
Paolo
View 5 Replies
View Related
Feb 19, 2008
ALTER PROCEDURE dbo.SelectItems (@PRN NVARCHAR(7))AS
SELECT eName AS EName, ePRN, cID, eCC, SUBSTRING(sDate, 7, 2) AS Day, SUBSTRING(sDate, 5, 2) AS Month, SUBSTRING(sDate, 1, 4) AS Year, eLOC, eTel, ePhone, eMobile, less20, over20, q1, inSVmn, inSVyr, inSVRetIns, outSVmn, outSVyr, outSVRetIns, insNo, q2, qper2, qplc2, q3, qper3, qplc3, q4, qper4, qplc4, pic1, pic2, pic3, esigdt, CCHName, CCHTitle, CCHsigdt, username, levent, eventdate, eventtimeFROM iTrns Where ePRN = @PRNRETURN
===
My code behind is
SqlConnection SqlCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["hrdbConnectionString1"].ConnectionString);String SQL = "EXECUTE SelectItems";SqlDataAdapter Adptr = new SqlDataAdapter(SQL, SqlCon);SqlCommandBuilder CB = new SqlCommandBuilder(Adptr);DataSet ds = new DataSet();Adptr.Fill(ds);return ds;SqlCon.Close();
Where I tried to search for PRN by using TextBox it gives me error
Procedure or Function 'SelectItems' expects parameter '@PRN', which was not supplied
I checked the Stored Proc and it was fine. So I guess the problem in the code behind
Any
View 3 Replies
View Related
Feb 10, 2007
Of all the Visual Basic.NET data access books that I have purchased and all the Internet site example code that I have reviewed, none have had any good examples of multi-threaded VB.NET code doing data access.
I am trying to avoid the non-responsiveness in a VB app while a simple data retrieval from SQL Server 2005 is in progress.
If anyone knows of any book titles or web sites that have example code (good or not) of multi-threaded VB.NET applications doing data access against Microsoft SQL Server (7, 2000, or 2005) or even against Microsoft Access(TM), it would be very much appreciated if you could provide the book title or URL to point me in the right direction.
The more examples the better.
Thanks in advance.
View 5 Replies
View Related
Sep 13, 2007
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 3 Replies
View Related
May 27, 2014
I have stored procedure
ALTER PROCEDURE dbo.usp_Create_Fact_Job (@startDate date, @endDate date) AS
/*--Debug--*/
--DECLARE @startDate date
--DECLARE @endDate date
--SET @startDate = '01 APR 2014'
--SET @endDate = '02 APR 2014'
;
/*-- end of Debug*/
WITH CTE_one AS ( blah blah blah)
SELECT a whole bunch of fields from the joined tables and CTEs...When I run the code inside the stored procedure by Declaring and setting the start and enddates manually the code runs in 4 minutes (missing some indexes ).When I call the stored procedure with the ExEC
DECLARE@return_value int
EXEC@return_value = [ClaimCenter].[usp_Create_Fact_Job]
@startDate = '01 apr 2014',
@endDate = '01 apr 2014'
SELECT'Return Value' = @return_value
It never returns a results set but doesn't error out either. I have left it for 40 minutes and still no joy.The sproc is reasonably complicated; 6 CTEs to find the most recent version of records and some 2 joins to parent tables (parent and grandparent), 3 joins to child tables (child, grandchild and great grandchild) and 3 joins to lookup views each of which self references a table to filter for last version of a record.
View 3 Replies
View Related
May 11, 2015
I have create a batch file to execute a stored proc to import data.
When I run it from the server (Remote Desktop) it works fine, but if I share the folder and try to run it from my pc, it doesn't do anything. I don't get an error, it just doesn't do anything. My windows user has admin rights in SQL. Why is it not executing from my PC?
View 9 Replies
View Related
Apr 30, 2007
hi all,
I have a stored procedure with a input parameter ( xml block).
sp: spGetAddr @xmlText
the style of parameter @xmlText looks like
<Param RestricBy="UserName IN ('Mayá', 'Bob')"/>
If I use non-basic ASCII character, just like 'á' in the parameter, the execution fails and I get 'The error description is 'An invalid character was found in text content.''
But this kind of special characters should be allowed in the name column of the database table.
If I use the htlm code 'á" to replace 'á' in the parameter, then the execution of the stored procedure is fine. but how can I change all of the possible sepcial characters?
If somebody knows how to handle this issue, please help me. I'm pre-appreciated for all the idea and suggestion!
Thanks,
Jone
View 4 Replies
View Related
Oct 14, 2007
I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.
View 3 Replies
View Related
Mar 23, 2015
I have simple query which creates tables by passing database name as parameter from a parameter table .
SP1 --> creates databases and calls SP2--> which creates tables . I can run it fine via SSMS but when I run it using SSIS it fails with below error .The issue gets more interesting when it fails randomly on some database creation and some creates just fine .
Note** I am not passing any database of name '20'
Exception handler error :
ERROR :: 615 :: Could not find database ID 20, name '20'. The database may be offline. Wait a few minutes and try again. ---------------------------------------------------------------------------------------------------- SPID: 111 Origin: SQL Stored Procedure (SP1) ---------------------------------------------------------------------------------------------------- Could not find database ID 20, name '20'. The database may be offline. Wait a few minutes and try again. ----------------------------------------------------------------------------------------------------
Error in SSIS
[Execute SQL Task] Error: Executing the query "EXEC SP1" failed with the following error: "Error severity levels greater than 18 can only be specified by members of the sysadmin role, using the WITH LOG option.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.I have sysadmin permission .
View 6 Replies
View Related
May 2, 2001
Our server is running. There are no locks, and server has been rebooted but the problem is still there. This has been going on for some time now. I intend to restart the server. Does anybody have a quick solution, please help. Thanks for your assistance!!
View 4 Replies
View Related
May 17, 2001
Please what do I look out for 6.5 if I want to troubleshoot for slow running.
Thanks.
View 2 Replies
View Related
Jun 6, 2001
Hi,
When first time I start my sql server is running faster. After 10 to 15 days later, sql sever performance is very slow. After I restart SQL service, to become normal.
Thanks
Mohan
View 1 Replies
View Related