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)
{
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
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.
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.
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.
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?
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?
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
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))
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.
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.
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.
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.
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.
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.
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?
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?
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.
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?
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.
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?
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.
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.
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
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.
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
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)
--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
insert into detallecaja_aux (cod_cen,importe1) values(@auxcod_cen, @importeVales+@importeEfectivo+@ImporteTalones+@ImporteTarjetas1+@importeTarjetas2-@importeGastos) fetch next from cursortemporal into @auxcod_cen
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
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.