Visual Source Safe Data Cannot Be Accessed Through SQL CLR Stored Procedure

May 15, 2008

The goal is to address visual source safe database on the network. We have the srcsafe.ini in the network as \ipaddrsrcsafe.ini. Now I create a new VSSDatabase object and call its OpenDb. Well for simple consle app or winform it is ok. But I was running it under Sql server Stored Procedure. It failed for I cannot access the source safe path throgh the COM object.

I know it is because of Windows identity. So I add the following code before I want to open the database, changing the to the WindowsIdentity:
WindowsIdentity impersonId = SqlContext.WindowsIdentity;
WindowsImpersonationContext orgCtx = null;
try
{
orgCtx = impersonId.Impersonate();
VSS_Database = new MVSI.VSSDatabase();
// VSS_Database.ImpersonateCaller = true;
VSS_Database.Open(Path, UserName, PassWord);
}
catch (Exception err)
{

orgCtx.Undo();
throw err;
}
finally
{
orgCtx.Undo();
}


Without the commented line "// VSS_Database.ImpersonateCaller = true", this does not work at all. It just behave like no changes to the windows identity.
However if I add this code, well, OpenDb will result in a No-response query. The Sql server is running the query with no responses.
Have you ever met that before? I am really frustrated. Thanks

View 3 Replies


ADVERTISEMENT

Stored Procedures In Visual Source Safe

Nov 15, 2007



I have about 40 stored procedures in a Visual Source Safe stored procedures which we are using for change control.

All stored procedures require to be moved into production across at least 13 different databases each on a dedicated server.

The only way I know at the minute is to do this manually. Does anyone out there have any ideas of how this task can be automated?

View 9 Replies View Related

Visual Source Safe Admin Question

Jan 3, 2007

Hi - I am wondering if anyone can give me good advice on the following situation:

I'm a new employee at a place where the SQL Server/Visual Source Safe admin has left. Only his co-worker has a VSS account and the VSS admin never gave her the admin p/w and he cannot be contacted. We need to have an account set up for me.

My question is: How can this be done with the knowledge of the admin p/w? Would we have to backup the files, un-install VSS, re-install it and set up the admin account again, or have I answered my own question? Or are we screwed? I don't have enough experience with VSS to make a decision.
At this point we're considering calling "Geek Sqaud" or "Geeks on Call", etc.

Thanks for any suggestions?

View 4 Replies View Related

Failed Running Package From Visual Source Safe

Dec 28, 2006

Hi guys..

I had created a Integration services project within my local system.All my packages are running fine.I added it to source control.Now i added this project from source control to another machine.It is failing to run...The path it is trying to execute is the location of the where i actually created my project.

How can i make it work.let me know

View 8 Replies View Related

Problem When Adding Ssis Packages From Microsoft Visual Source Safe 6.0

Jun 4, 2007

I have created a integration services project on machine A and have added to visual source safe 6.0

When i try to take the project from source safe and store that on a different machine B i have a problem with it....

I check in everything on Machine A

When i try to add on machine B it tries to add it with "Name of package(1)" which means that its adding a copy.But it dosent do for all only for few



Could anyone let me know what the problem is??????

View 1 Replies View Related

Stored Procs And Source Safe Good Idea?

Sep 25, 2000

Hi,
Any pros and cons of putting sprocs into Source Safe?

Thanks,
Judith

View 2 Replies View Related

Permissions For Objects Accessed Through A Stored Procedure.

Apr 24, 2008


While trying to execute a stored procedure I am getting error that 'SELECT permission denied on table .......' The DBA has given execute permission for the sp and still the same error. What needs to be done. When permissions are given through the SP it implies that the objects are given permissions ?

Putting in db_datareader group will give permission to read from all tables across all the databases in the server. We want that the user should be able to read data from only those tables called in the sp. Normally in SQL 2000 we used to give EXECUTE permissions to the sp only. This in turn would be enough for that user to get data while executing the sp.

View 5 Replies View Related

How Can I Assign A Stored Procedure As Cursor's Data Source In AStored Procedure?

Oct 8, 2007

How can I create a Cursor into a Stored Procedure, with another Stored Procedure as data source?

Something like this:

CREATE PROCEDURE TestHardDisk
AS
BEGIN

DECLARE CURSOR HardDisk_Cursor
FOR Exec xp_FixedDrives
-- The cursor needs a SELECT Statement and no accepts an Stored Procedure as Data Source

OPEN CURSOR HardDisk_Cursor


FETCH NEXT FROM HardDisk_Cursor
INTO @Drive, @Space

WHILE @@FETCH_STATUS = 0
BEGIN

...
END
END

View 6 Replies View Related

Stored Procedure Performance Degrading Dramatically When Accessed Through ADO.NET

Oct 2, 2007

Hi

I am having a problem with a particular stored procedure in a database application and I have ran out of ideas as to what is the cause. When calling this stored procedure from a .Net application it typically returns results in about 0.2 seconds. 24 hours after it's creation, the procedure takes over 40 seconds to return the same results to the application. However if I call the procedure via Management Studio or Query Analyzer, the performance remains consistently fast.

It's a fairly complicated query making use of the following features:


FOR XML EXPLICIT

The ROW_NUMBER function

Input Parameters

The procedure is replicated, along with the tables that it references
The calling application is using ExecuteXMLReader to retrieve the results.

To fix the problem, I can simply run an ALTER PROCEDURE statement (without changing any of the contents of the stored procedure). However, by the next morning, the problem will have reoccurred.

Can anyone shed any light on why this is happening?

Phil

View 9 Replies View Related

Can You Use A Stored Procedure In A Data Source View?

Aug 2, 2007

Is it possible to use a stored procedure in a Data Source View? When I use the wizard to create the Datasource View, all it lets me choose is tables and views in the database. Am I missing something, or trying to do something that is impossible?

View 8 Replies View Related

Using A Stored Procedure As A Data Flow Source

Dec 11, 2007



Simple question - can I use an external stored procedure for my data flow source. I assume that I can use the OLEDB connection and go from there?

Can I use SQL command and do exec sp_thisprocedure?

Thanks for the information.

View 5 Replies View Related

How To Export Data From Stored Procedure Source?

Mar 31, 2006

Hi,

I have a SSIS package which exports data from a view in SQL database into Excel file. It was created by Export Wizard in SQL 2005 Server.

Now I would like to modify this package and change data source from view to stored procedure.

What component from toolbox should I use? it should be placed in Control Flow or Data Flow? And how connetc it with my Excel Destination?

thanks,

Przemo

View 3 Replies View Related

SSIS-Use Stored Procedure/function As The Data Source

Jan 28, 2008



How do I use stored procedure or a user defined function as the data source in my SSIS package.
I am very new to SSIS.
Thanks

View 5 Replies View Related

Calling A Stored Procedure On A Data Source (with Parameters)

Nov 29, 2007

This seems to be much more difficult than it was in DTS (or perhaps I just need to adjust to the new way of doing things).

Eventually I found that I needed to use "SQL command from variable" and using two other variables as input parameters. The expresion for the command is

"usp_ValveStatusForDay '" + @[User:ate] + "','" + @[User::Report] + "'"

which typically evaluates as

usp_ValveStatusForDay '18 Oct 07','Report_Name'

This previews correctly and the resulting columns are available for mapping to a destination. So far so good.

By the way, is this the best way to call a stored procedure with parameters?

I have pasted the stored procedure at the end of this posting because I have come accross a puzzling problem. The query as shown below works correctlly but if I un-comment the delete statement, the preview still works and the columns are still avilable for mapping but I get the following errors when the package is executed.


Error: 0xC02092B4 at Data Flow Task, OLE DB Source [1]: A rowset based on the SQL command was not returned by the OLE DB provider.

Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "OLE DB Source" (1) failed the pre-execute phase and returned error code 0xC02092B4.

I realise that I could execute the delete query in a separate SSIS package step but I am curious as to why there is a problem with the way I tried to do it.

At one stage the stored procedure used a temp table and later I also experimented with a table variable. In both cases I got similar errors at execution time. In the case of the temp table there was another problem in that, while the preview worked, there were no columns available for mapping. Using a table variable seemed to overcome this problem but I still got the run time error. eventually I found a way to avoid using either a temp table or a table variable and the package then worked correctly, copying the data into the desitnation table.

It seems to me that if there is any complexity at all to the stored procedure, these errors seem to occur. Can anyone enlighten me as to what the "rules of engagement" are in this regard? Is one solution to use a wrapper stored procedure that simply calls the more complex one?



ALTER procedure [dbo].[usp_ValveStatusForDay]

(

@dateTime DateTime,

@reportName VarChar(100)

)

AS

BEGIN

DECLARE @day VarChar(10)

DECLARE @month VarChar(10)

DECLARE @year VarChar(10)

DECLARE @start VarChar(25)

DECLARE @end VarChar(25)

SET @day = Convert(Varchar(10),DatePart(day, @dateTime))

SET @month = Convert(VarChar(10), DatePart(month, @dateTime))

SET @year = Convert(VarChar(10), DatePart(year, @dateTime))

IF @month = '1' SET @month = 'Jan'

IF @month = '2' SET @month = 'Feb'

IF @month = '3' SET @month = 'Mar'

IF @month = '4' SET @month = 'Apr'

IF @month = '5' SET @month = 'May'

IF @month = '6' SET @month = 'Jun'

IF @month = '7' SET @month = 'Jul'

IF @month = '8' SET @month = 'Aug'

IF @month = '9' SET @month = 'Sep'

IF @month = '10' SET @month = 'Oct'

IF @month = '11' SET @month = 'Nov'

IF @month = '12' SET @month = 'Dec'

SET @start = @day + ' ' + @month + ' ' + @year + ' 00:00:00'

SET @end = @day + ' ' + @month + ' ' + @year + ' 23:59:59'

--delete from ValveStatus where SampleDateTime between dbo.ToBigInt(@start) and dbo.ToBigInt(@end)

exec dbo.usp_ValveStats_ReportName @reportName, @start, @end, '1h'

END

View 8 Replies View Related

ODBC Connection With Stored Procedure On ISeries As Data Source

Mar 6, 2006

I have created a stored procedure on the iSeries that creates a cursor and opens it.  I am trying to write my report to use the stored procedure. I cannot get the data source to work.  How do I create my data source so that it uses the stored procedure?  My SP has three parameters I am trying to pass from the report.  The parms are created in the layout.

Thank you

View 6 Replies View Related

SP & Source Safe

May 3, 2001

Can anyone tell me how I can copy store procedures from SQL Server 2000 to Source Safe 6.0? I right-click the store procedure and cop
y it but when I got to the visual source safe it will not work. I therefore went through the soruce code control on Tool menu which I cannot see such commands. I do really need your help.

Regards,

View 2 Replies View Related

Source Safe And SQL Server 7.0

Oct 11, 2000

I'm in the process of locking down our SQL Server development environment and wanted to implement some type of version control, not just for stored procedures but for all database objects (tables, triggers, etc.)

A while back I read an article that explained how to utilize Visual Source Safe to establish version control for databases. If I recall correctly, I believe it had something to do with Visual Interdev and creating some project with Visual Interdev.

If anyone can provide alittle more insight into how to accomplish this, or at least point me in the right direction, it would be greatly appreciated.

Thank you in advance for your help!

View 3 Replies View Related

Urgent. Output Columns Are Not Appearing When I Use OLEDB Data Source With An Oracle Stored Procedure In Dataflow Task

Nov 12, 2007

I am using execute sql task to run a stored procedure in oracle database which returns a resultset. This works. Now I need to send the ouput to a destination table in a sql database. Should I use for each loop to pick the resultset and insert it into the destination one by one (which I dont think is a great idea) or is there a better way to accomplish this task (in data flow task) ?

When I use dataflow task instead of execute sql task, the main issue is I am not able to see the output columns when I execute an oracle stored procedure, but when I see the preview I can see the resultset . But I can see the output columns for a sql server stored procedure.

View 9 Replies View Related

Setting Up SQL Server 2005 With Source Safe

Jul 17, 2006

My manager has asked me to install and configure Source Safe so that our team can check out stored procedures, (and other objects), and check them back in when we are finished working on them.

I'm not familiar with Visual Source Safe. Does SQL Server 2005 work with Visual Source Safe in such a way that I can check out/in SQL Server objects such as procedures?

If someone knows the answer, or perhaps an existing thread that covers this, please let me know.

Thank you for your time!

Frank

View 1 Replies View Related

Team Foundation Server Vs Source Safe

Apr 3, 2006

Can someone give some comments on which program to use with SSIS ?

View 2 Replies View Related

Cannot Access Data Source In Visual Studio 2012

May 25, 2015

I am not able to see the Data Source in Visual studio 2012. I have tried several things like 'Alt + shift + D' which is meant to bring up the datasource tab but it does not. I have also looked in view->Other windows but its not displayed there.

View 5 Replies View Related

Visual Studio Closes When Data Source Has Changed

Aug 22, 2007

I am trying to edit an SSIS package due to a change in the schema of one of the source tables. When I open the package in Visual Studio, I see the exclamation mark on the object but before I can make change or go to edit anything in the package, I hear the "dumphhh" sound that indicates an error. There is no dialogue box but the program is now locked up. Then after 10 - 20 seconds the program closes.

How am i supposed to add the new column to the data source metadata if I can't edit the package?

View 7 Replies View Related

User 'Unknown User' Could Not Execute Stored Procedure - Debugging Stored Procedure Using Visual Studio .net

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

When Using Data Source As Analysis Services Visual Studio 2005 Gets Crashed

Jan 8, 2008



Hi ,

I am using sql server 2005 standard edition and i have installed MSXML core services 6 and also ADOMD.net for analysis services.
i am using visual studio 2005 version of 8.0.5072742 and the problem that i am facing is when i use to develop reports using Analysis services as a data source , the error message pops up and VS 2005 gets crashed.

i could define the dataset , layout part and when i prees the preview , it gets crashed.
could any one help me on this problem to sort out.

Regards
RMP

View 1 Replies View Related

Problem Debbuging A Stored Procedure In Visual Studio

Jan 14, 2008

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 1 Replies View Related

Problem Executing A Stored Procedure From Visual Web Developer

Jan 23, 2008

Hi, i'm using visual web developer 2005 express and visual basic .net, and sql server, i'm trying to execute a stored procedure to make a bulk insert from a text file into sql server table, but i'm facing problems when i get here:  Dim myCommand As New SqlClient.SqlCommand(strsql1, objConn)            'Try            myCommand.CommandType = Data.CommandType.StoredProcedure            myCommand.Parameters.AddWithValue("path", "C: est.txt")            myCommand.Connection.Open()            myCommand.ExecuteNonQuery()            myCommand.Connection.Close()            myCommand.Dispose()   when i get to the line in bold i receive a message saying that couldn't find the file "Cannot bulk load. The file "C: est.txt" does not exist." , but the file is there on C: and visual web developer can't find it. Bellow i pasted the stored procedure. Seems that my code can't find the file. set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[sp_ed_insert]@Path varchar(max)AS--Step 1: Build Valid BULK INSERT StatementDECLARE @SQL varchar(2000) BEGIN  -- Valid format: "John","Smith","john@smith.com"  SET @SQL = "BULK INSERT Ed_order_temporary FROM '"+ @Path +"' WITH (FIELDTERMINATOR = '"",""') " END--Step 2: Execute BULK INSERT statementEXEC (@SQL)--Step 3: INSERT data into final tableINSERT Ed_order (ed_order_number)SELECT  CASE WHEN order_header = 'O' THEN SUBSTRING(ed_order_number,1,DATALENGTH(ed_order_number)-1)end                       FROM Ed_order_temporary--Step 4: Empty temporary tableTRUNCATE TABLE Ed_order_temporary  Any help is welcome. Thanks. 

View 1 Replies View Related

Debugging Stored Procedure From Visual Studio 2005

Feb 29, 2008

 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 7 Replies View Related

Stored Procedure Error When Run From Visual Basic In Access

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

Passing Parameters To A Stored Procedure In Visual Basic

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

Stored Procedure Not Returning OUTPUT Parameters To Visual Basic Program

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

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 View Related

Stored Procedure Source Control

Jul 20, 2005

Hi,I am trying to put SQL Server Stored Procedures into Sourcesafe as perthe Microsoft Knowledge Base article 818368, but have run into aproblem.The web server is SQL Server 2000 running on Windows 2003 Server. Theclient dev environment is Visual Studio 2003 Enterprise DeveloperEdition.I have carried out the following steps successfully:-1. Installed Sourcesafe client tools on the Server (sourcesafe is onanother server)2. Run the MSSQLServer service under a domain account that has Readand Write access to the Sourcesafe database.3. Added the above user to Sourcesafe using the Administrator tool.4. Installed the VS6 Stored Procedure Version Control components onthe Server5. Enabled Version Control for Stored Procedures on the clientHowever when I right-click on the Stored Proc node in Server explorerI do not get any of the Sourcesafe menu options. They are not greyedout, they are simply not there!Any help would be appreciated.Alternatively if anyone has recommendations for other strategies orother tools to use for this purpose than Sourcesafe then this wouldalso be welcomeKarl

View 1 Replies View Related

Excel Source And Stored Procedure

Oct 4, 2007

Hello,
I'm a SSIS beginner.

I need to create users in my database from an Excel file source.
I have an Excel source with user name and email.

I would like to use SSIS to go though the Excel file and execute a store procedure (create_user @name @email @password OUTPUT) for each row and then create an output file with the new created password (a random password is created for each user and the stored procedure has an output @password)

I tried to "plug" an "Excel source" to an "Execute SQL task" which execute my store procedure.
But as a SSIS beginner I don't really understand how to pass the parameters (name and email in the Excel file) to my stored procedure. There is the concept of variable a for each loop thing... but i dont really know how to start.

I would be greatfull if someone could tell me a solution to my problem.

Cheers

Fabrice

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved