Stored Procedure Version Control
Sep 13, 2001Is there anyone out there who has successfully setup version control of stored procedures with Source Safe and Visual Interdev?
View 1 Replies
ADVERTISEMENT
Stored Procedure Source Control
Jul 20, 2005Hi,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 RelatedControl Output Of Stored Procedure
Feb 19, 2008I have a very simple stored procedure that returns a list of employees. The procedure works fine. However, sometimes there is no records returned. In that case, I'd like to like the procedure to return a single record with predetermined values. I've added the YELLOW portion of the code to my existing proc to reflects the logic of what I am trying to accomplish.
CREATE PROC proc_Emp
@Sel_Country
AS
BEGIN
SELECT DISTINCT employee, hrid
FROM emp_table
WHERE country = @Sel_Country
@SEND_RESULTS =
CASE WHEN row_count = 0 THEN
SELECT 'No Country', 4444444444
ELSE
(send original results)
END
END
I need to:
Query for the employee list
If the row count is zero/null then return fixed values
Else return the orginal data from Step 1.
I'm guessing I need to use the OUTPUT command and configure some variables. But I'm not having much luck decoding BOL and I've not found any similar submissions to the forum.
Rob
Control The Output Of Stored Procedure
Nov 14, 2007
Hi all,
I have a several stored procedures that they are designed to do Update and Insert tasks and all of them after finishing the task will return the Inserted or Updated row.
The problem is sometime I would need to call the stored procedure inside other ones and somehow i need to stop the inner stored procedures from producing resultsets. I don't find any SET options in SQL documentation which could control this behavior and block resultsets from being sent to the client.
Is there any way i could do this?
Exmaple:
In following example as a result of dbo.AddressInsert being called within dbo.CustomerInsert, two resultsets are returned to the client!
Procedure dbo.AddressInsert(....)
as
BEGIN
Insert INTO Adress ....
SELECT * FROM AdressView WHERE AddressID = @@identity
END
Procedure dbo.CustomerInsert(...)
as
begin
-- transcation begin...
EXEC dbo.AddressInsert
INSERT INTO Customer....
-- end transcation
SELECT * FROM Customer WHERE CustomerID = @@identity
end
Identifying A Stored Procedure "version"
Feb 8, 1999When distributing a SQL Server database to customers we need to be able to
tell which version of a stored procedure is currently installed on the user's machine. Does 6.5 or 7.0 provide any way to monitor versions like this?
How Can Control Transactions For Creating Stored Procedure ?
Apr 30, 2007I create StringBuilder type forconcating string to create a lot of stored procedure at once However When I use this command BEGIN TRANSACTIONBEGIN TRY--////////////////////// SQL COMMAND ///////////////////////// -------------------- This any command --/////////////////////////////////////////////////////////// --COMMIT TRANEND TRYBEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;END CATCHIF @@TRANCOUNT > 0 COMMIT TRANSACTION; on any command If I use Create a lot of Tablessuch as BEGIN TRANSACTION
BEGIN TRY
--////////////////////// SQL COMMAND /////////////////////////CREATE TABLE [dbo].[Table1]( Column1 Int , Column2 varchar(50) NULL ) ON [PRIMARY]CREATE TABLE [dbo].[Table2](
Column1 Int ,
Column2 varchar(50) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Table3](
Column1 Int ,
Column2 varchar(50) NULL
) ON [PRIMARY]
--///////////////////////////////////////////////////////////
--COMMIT TRAN
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH
IF @@TRANCOUNT > 0
COMMIT TRANSACTION; It correctly works. But if I need create a lot of Stored procedure as the following code : BEGIN TRANSACTION
BEGIN TRY
--////////////////////// SQL COMMAND /////////////////////////
CREATE PROCEDURE [dbo].[DeleteItem1]
@ProcId Int,
@RowVersion Int
AS
BEGIN
DELETE FROM [dbo].[ItemProcurement]
WHERE
[ProcId] = @ProcId AND
[RowVersion] = @RowVersion
END CREATE PROCEDURE [dbo].[DeleteItem2]
@ProcId Int
AS
BEGIN
DELETE FROM [dbo].[ItemProcurement]
WHERE
[ProcId] = @ProcId
END
CREATE PROCEDURE [dbo].[DeleteItem3]
@ProcId Int
AS
BEGIN
DELETE FROM [dbo].[ItemProcurement]
WHERE
[ProcId] = @ProcId
END
--///////////////////////////////////////////////////////////
--COMMIT TRAN
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH
IF @@TRANCOUNT > 0
COMMIT TRANSACTION; It occurs Error ????? Please help me How should I solve them ?
Assigning A Stored Procedure Return Value To A Control
Jan 11, 2008Hi, I have an app where I am calling a stored procedure that runs and returns an integer. I would like to assign this integer to a control on the page. Here is a sample of code and below I will give the errors: Protected Sub NewButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles NewButton.Click Dim tempconnection As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("TestConnectionString").ConnectionString) Dim tempproc As New Data.SqlClient.SqlCommand("SP_FetchProviders", tempconnection) Dim tempparam As New Data.SqlClient.SqlParameter tempproc.CommandType = Data.CommandType.StoredProcedure tempproc.Parameters.Add(New SqlParameter("@Agency_ID", Data.SqlDbType.Int)) tempproc.Parameters("@Agency_ID").Value = "8" 'Using a number just to test, will be populated by a session variable later tempparam = tempproc.CreateParameter() tempparam.ParameterName = "@OutValue" tempparam.Direction = Data.ParameterDirection.ReturnValue tempparam.SqlDbType = Data.SqlDbType.Int tempconnection.Open() tempproc.ExecuteNonQuery() Dim labelfill As String labelfill = tempproc.Parameters("@OutValue").Value tempconnection.Close() CType(Me.DetailsView1.FindControl("OmbudINSDDL"), DropDownList).Items.Add(labelfill) 'This is inside of a detailsview templatefield End Sub Here is the stored procedure set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER procedure [dbo].[SP_FetchProviders] @Agency_ID intas Select Name, ProviderSubUnit_ID From ProviderSubunit a join Ombudsman b on a.contractproviders_id=b.contractproviders_id Where b.agency_id = @Agency_ID Order by Name This takes the @Agency_ID as input variable, and has returns an integer (according to SQL management studio). When I try to run my code I get the error that an sqlparameter with parametername @OutValue is not contained by this sqlparametercollection. This may be a simple question, but does the outvalue have to be declared in the stored procedure? If so, can you provide the right syntax? If not, can you offer a suggestion to populate the dropdownlist with the returned value? Thanks in advance.
View 3 Replies View RelatedListBox Web Server Control And Stored Procedure
May 15, 2008Hi, I've a ListBox Web Server Control where I select a list of citiesnow I would like to create a stored procedure with the selected values... please note that I use metods like these for execute my Stored procedure public DataSet Getgfdfgh(SqlConnection connection, String IDAttivitaTipo, DateTime DataInizio, DateTime DataFine) { ConnectionState currState = connection.State; if (((connection.State & ConnectionState.Open) != ConnectionState.Open)) connection.Open(); try { SqlParameter[] parameters = new SqlParameter[3]; parameters[0] = new SqlParameter("@IDAttivitaTipo", IDAttivitaTipo); parameters[1] = new SqlParameter("@DataInizio", DataInizio); parameters[2] = new SqlParameter("@DataFine", DataFine); SqlCommand cmd = CreateStoreProcedureCommand("Getfhdfh", connection, parameters); SqlDataAdapter adapter = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); adapter.Fill(ds); return ds; } finally { if ((currState == ConnectionState.Closed)) connection.Close(); } } How Can I manage a list of values ??Thanks for help me!!
View 1 Replies View RelatedPassing Control Flags To Stored Procedure
Jul 23, 2005Wanted to know which among these options is better and why? Or if theircould be scenarios where we could opt for one of these.a) flags passed from code to control the execution of queries within astored procedure i.e. - where queries within a single stored procedureare controlled by flags passed to them.ORb) Break individual queries into separate stored procedure
View 4 Replies View RelatedAnalysis :: Possible To Use Stored Procedure To Control SSAS Partitions?
Aug 19, 2015I have defined a stored procedure with one parameter. With this parameter I'm able to controll which year of the sales amount data should be selected. This works fine.
Now I want to implement this stored procedure as the source of the partitions. But if I do this I get an error. The syntax-check says, that everything is fine. But if I want to calculate the partition with this command: "exec dst.fact_umsatz_year 0" get the following error (in German):
OLE DB-Fehler: OLE DB- oder ODBC-Fehler : Falsche Syntax in der Nähe von ')'.; 42000; Falsche Syntax in der Nähe des exec-Schlüsselworts.; 42000.
Fehler im OLAP-Speichermodul: Fehler beim Verarbeiten der FACT Umsatz Pivot View-Partition der Anzahl Kunden-Measuregruppe für den Vertrieb-Cube aus der OLAP AS-Datenbank.
Stored Procedure Version Of SELECT Statement - Can't Understand Why It's Not Working
Apr 25, 2006Hi,
[I'm using VWD Express (ASP.NET 2.0)]
Please help me understand why the following code, containing an inline SQL SELECT query (in bold) works, while the one after it (using a Stored Procedure) doesn't:
<asp:DropDownList ID="ddlContacts" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource2"
DataTextField="ContactNameNumber" DataValueField="ContactID" Font-Names="Tahoma"
Font-Size="10pt" OnDataBound="ddlContacts_DataBound" OnSelectedIndexChanged="ddlContacts_SelectedIndexChanged" Width="218px">
</asp:DropDownList> <asp:Button ID="btnImport" runat="server" Text="Import" />
<asp:Button ID="Button2" runat="server" OnClick="btnNewAccount_Click" Text="New" />
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ICLConnectionString %>"
SelectCommand="SELECT Contacts.ContactID, Contacts.ContactLastName + ', ' + Contacts.ContactFirstName + ' - ' + Contacts.ContactNumber AS ContactNameNumber FROM Contacts INNER JOIN AccountContactLink ON Contacts.ContactID = AccountContactLink.ContactID WHERE (AccountContactLink.AccountID = @AccountID) ORDER BY ContactNameNumber">
<SelectParameters>
<asp:ControlParameter ControlID="ddlAccounts" Name="AccountID" PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>
How To Execute Stored Procedure Differently Based On SQL Server Version
Oct 16, 2007My product was developed for and works correctly on SQL Server 2000. However, when we upgraded to 2005, we found that certain system stored procedures were different, causing our product to break.
We can easily change our stored procedures to work in 2005, but we have a large client base, some of whom will be using each version. Our current solution is to check the version of SQL Server during installation and choose which script to use at that time in order to have an appropriate stored procedure for that version, but we are concerned about users who install our product with SQL Server 2000 and then upgrade to SQL Server 2005.
How can I make a stored procedure that will run differently depending on the version? I tried something like:
if (select charindex('2000', @@version)) > 0
begin -- SQL Server 2000
SELECT
...
FROM
...
WHERE
end
else -- SQL Server 2005
SELECT
...
FROM
...
WHERE
end
Unfortunately, the system tables I'm selecting from have different stuctures in the different versions (one example is msdb.dbo.sysjobschedules and msdb.dbo.sysschedules), and even though the code never gets into the SQL Server 2000 section on 2005, it parses the whole procedure for errors before allowing it to be saves and will not allow this.
Any thoughts?
How To Use A Stored Procedure Inside Select Query (sql Server Version 8)
Sep 29, 2007
Hi,
Please help me in this problem...
i am new to sql server..
i am using sql server version 8...(doesnot support function with retun values..)
so i have created a procedure...
-----------procedure------------------(to find next monday after 6 months)-------------------
[code]
create proc next_Monday ( @myDate DATETIME )
as
BEGIN
set @myDate = dateadd(mm, 6, @myDate)
while datepart(dw,@myDate) <> 2
begin
set @myDate = dateadd(dd, 1, @myDate)
end
select @myDate
end
go
[/code]
--------------------------------------------------------
i can able to execute this procedure separately.... working well...
but don't know how to call it inside another query....
the following throws error....
select smaster.sname, smaster.Datex, 'xxx'=(execute next_monday smaster.Datex) from smaster
please help me... how to fix this problem...
Problem Use Sqldatasource To Access Stored Procedure And Get Data Bind To Label Control
Aug 30, 2007Hi every experts
I have a exist Stored Procedure in SQL 2005 Server, the stored procedure contain few output parameter, I have no problem to get result from output parameter to display using label control by using SqlCommand in Visual Studio 2003. Now new in Visual Studio 2005, I can't use sqlcommand wizard anymore, therefore I try to use the new sqldatasource control. When I Configure Datasource in Sqldatasource wizard, I assign select field using exist stored procedure, the wizard control return all parameter in the list with auto assign the direction type(input/ouput....), after that, whatever I try, I click on Test Query Button at last part, I always get error message The Query did not return any data table.
My Question is How can I setup sqldatasource to access Stored Procedure which contain output parameter, and after that How can I assign the output parameter value to bind to the label control's Text field to show on web?
Thanks anyone, who can give me any advice.
Satoshi
Can I Use A SqlDataSource Control Exclusively To Pass Data To A Stored Procedure For Execution (insert/update Only)?
Feb 28, 2008Hi,
I'm reasonably new to ASP.NET 2.0
I'm in my wizard_FinishButtonClick event, and from here, I want to take data from the form and some session variables and put it into my database via a stored procedure. I also want the stored procedure to return an output value. I do not need to perform a select or a delete.
For the life of me, I can't find a single example online or in my reference books that tells me how to accomplish this task using a SqlDataSource control. I can find lots of examples on sqldatasources that have a select statements (I don't need one) and use insert and update sql statements instead of stored procedures (I use stored procedures).
I desperately need the syntax to:
a) create the SqlDataSource with the appropriate syntax for calling a stored procedure to update and/or insert (again, this design side of VS2005 won't let me configure this datasource without including a select statement...which I don't need).
b) syntax on how to create the parameters that will be sent to the stored procedure for this sqldatasource (including output parameters).
c) syntax on how to set the values for these parameters (again...coming from form controls and session variables)
d) syntax on how to execute, in the code-behind, the stored procedure via the sqldatasource.
If anybody has sample code or a link or two, I would be most appreciative.
Thank you in advance for any help!
DTS Version Control Utilities?
Jun 15, 2001We are looking for utilities to help us manage DTS packages. Specifically to help us track changes in the packages in a fairly fine-grained fashion; several layers deeper than the version listing under package info, for example. Think of it as an object-oriented diff utility if you will, something that can report on changes in a package very specifically; for example, we need to know that step 4 of a package is a SQL widget and the text has changed between versions, and what the changes are. Converting the package to a VB project & versioning the source text doesn't work, for several reasons too complicated to state here.
Is there aything like this available? We would rather buy than build, but I haven't seen anything like this yet. If anyone has experience with such a utility I would appreciate knowing about this. Thanks.
Jim Rivera
Software Engineer
Grange Insurance Graoup
Seattle, WA
Version Control For SQL Code
Jul 20, 2005We've been using CVS for our HTML, JAVA etc..Now we would like to use it for our SQL CODE to. We have a largedatabase with many stored procedures.I would like to have something using Ant like:Download the latest version of source code.If the SQL patch has been updated, run the patch to update theDatabase.I don't know how to deal with the SQL patch files. Do I update thesame file(over writing the anything from the previous patch, or justwrite new files for each version?)I am sure someone has done something like this.What are the best practices for doing this? Could someone pleaseoutline thier setup for SQL version control?
View 2 Replies View RelatedHow To Version Control In SQL 2000?
Jul 20, 2005Hi.I would like to version control my data in SQL 2000. Currently I'vestored the data/time info of my data whenever I add or edit the table.Are there such built in support in SQL 2000? If not, anyone care toshare some tips and tricks to perform version control with SQL 2000?Thank you.
View 2 Replies View RelatedVersion Control Management
Dec 14, 2007We are creating an SSIS application for population of the datawarehouse. And we arrived at a stage that business side is starting do some real testing. And of course we am getting some feedback and amendment to the specifications. Which is analysed by the PM's and translated to me.
When an change or amendment is coming to me. The package is checked out of the SVN repository and locked. And the developer makes the changes in a development enviroment and change the related SSIS packages. After the development we make a test run a separated the test enviroment. This test is checked by the Business side and the PM's when approved I copy the modified package to a live enviroment.
When the modified package is put in the live environment we manually check in package to the SVN repository.
The problem is that the changes are slow to implement and the SSIS package is locked for a single problem. And this can be very time consuming.
How are you guys handling development of SSIS packages? Especially where you work in teams or at least with more than one developer?
Is there a application/plugin or tool available which show the changes between two of the sames packages ( different versions ). A warning signal or something like that on the component would be very helpful.
I would be very interested in some more documentation about team development of ssis packages.
Version Control For Sql 2000 And 2005
May 3, 2007Hi,
Our compnay is looking for version control for MS SQL 2000 and 2005. Any recommendation? thx
ReportViewer Control And SOAP Version
Dec 19, 2006We have just upgraded from an embedded browser hosted RS2000 system to the Microsoft.ReportViewer.Winforms control for RS2005 in our applications, and have encountered a SOAP / firewall problem again.
We were using an embedded browser to display the reports, as some of our client sites block or strip the SOAP wrapper from SOAP 1.1 commands that the old RS2000 control tried to use when the data passes through their firewall. We have no control over, or visability of their firewall configuration, as this is part of their own site security.
Is RS2005 still using the same older version of SOAP, or has it been moved up to version 1.2, which I believe does not trigger as many alarm bells in some firewall systems.
We have no problems makeing web service calls to our own applications through the firewall, so its only the report viewer control that gives us these problems.
I have not been able to explicitly clarify this down to the SOAP version, but it looks too close the our previous firewall problem to be anything else - comments invited !!!!!
As an aside, is there any way to trace the inbound request through IIS to ReportServer so I can see what hits the report server ? The IIS logs alone show a request has been made, but no qualifying data, and the report server logs only seem to show processing once the request has been accepted. What I want to to is see the inbound request, and then the outbound error message being returned so I can confirm my suspicions.
I have posted a similar thread on google groups for reporting services.
SQLSourceSafe, The Integrated Version Control Solution For SQL Server Objects!
Jul 20, 2005SQLSourceSafe 2.0 is the right version control solution for SQL Serverdatabase development projects. SQLSourceSafe 2.0 Integrates with bothSQL Server and Visual SourceSafe, providing an effective andeffortless version control management system for individuals andproject teams to manage database objects.By using SQLSourceSafe 2.0, you can manage SQL Server database objectssuch as tables, stored procedures, user defined functions, views,triggers, indexes/keys and constraints as you manage regular files andprojects in SourceSafe. SQLSourceSafe 2.0 enables your to archive andlabel database object changes in SourceSafe, so that you can comparedifferent versions, recover from an undesired write-over of objects,or roll back to a previous release. Advanced users can also useSQLSourceSafe 2.0 to find out the differences between SQL Serverdatabases or deploy the latest or any labeled version SQL Serverdatabase archived in SourceSafe to different computers.For more information, please visit: http://www.bestsofttool.com.
View 1 Replies View RelatedCalling A Stored Procedure Inside Another Stored Procedure (or Nested Stored Procedures)
Nov 1, 2007Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
Calling A Stored Procedure From ADO.NET 2.0-VB 2005 Express: Working With SELECT Statements In The Stored Procedure-4 Errors?
Mar 3, 2008Hi all,
I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):
(1) /////--spTopSixAnalytes.sql--///
USE ssmsExpressDB
GO
CREATE Procedure [dbo].[spTopSixAnalytes]
AS
SET ROWCOUNT 6
SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName
FROM LabTests
ORDER BY LabTests.Result DESC
GO
(2) /////--spTopSixAnalytesEXEC.sql--//////////////
USE ssmsExpressDB
GO
EXEC spTopSixAnalytes
GO
I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class
///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance,
Scott Chang
More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
T-SQL (SS2K8) :: One Stored Procedure Return Data (select Statement) Into Another Stored Procedure
Nov 14, 2014I am new to work on Sql server,
I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.
Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.
SQL Server 2014 :: Embed Parameter In Name Of Stored Procedure Called From Within Another Stored Procedure?
Jan 29, 2015I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete]
@QQ_YYYY char(7),
@YYYYQQ char(8)
AS
begin
SET NOCOUNT ON;
select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
[Code] ....
Connect To Oracle Stored Procedure From SQL Server Stored Procedure...and Vice Versa.
Sep 19, 2006I have a requirement to execute an Oracle procedure from within an SQL Server procedure and vice versa.
How do I do that? Articles, code samples, etc???
Error While Using Database Procedure In A Gridview Control
Dec 19, 2006Dear Forum,
I have a gridview control which is associated to a storedprocedure with a parameter(Customer Number) to be supplied. In the Define Custom Statement or stored procedure section I selected stored procedure and selected the stored procedure. The Define Parameter window I defaulted the Parameter Source as 'none' and default value as '%'. In the next screen, I do a test query which retuns the following error
There was an error executing the query. Please check the syntax of the command and if present, the type and values of the parameters and ensure that they are correct.
[Error 42000] [Microsoft][ODBC SQL Server Drive][SQL Server] Procedure 'SP_TransactionDetails' expects parameter '@cnum' which was not supplied.
I am using SQL server studio 2005 version2.0.
But the same procedure, if I use as SQL Statement, it works.
Can somebody help me.
Thanks,
Hidayath
Stored Prcedures And SQLDataSource Control
May 28, 2008Is it possible to place a stored procedure in a SQLDataSource control that needs a variable input then run it later?
DETAILS
I have 2 pages with gridview controls that are linked to a SQLDataSource controls.
What I would like to do is when the user clicks on a link in the gridview of the first page I want to take the value of the link clicked and pass it to the second page where it is placed into a variable that would be used in a WHERE clause to fill the second pages gridview.
I have no problem getting the value into a variable in the page load event of the second page. What I would be nice is since the gridview is already tied to a SQLDataSource control with the columns specified is if I could some how pass the variable to a stored procedure in the control so I don't have to manually pull and fill the data.
Any thoughts would be apperciated.
Thanks,
Ty
Stored Procedures And Source Control
Oct 18, 2005We have a large team working on applications to support our internal sales and our public dotcom site. In the process, as you can imagine, we generate a lot of stored procedures and sql scripts going to our dba's for our staging,qa and production environments- but we don't have a solid way to manage it yet. Some people use Source Safe? Does anyone have any successes in this area or recomendations?Thanks!s!Stephen Rylander
View 2 Replies View RelatedGrab IDENTITY From Called Stored Procedure For Use In Second Stored Procedure In ASP.NET Page
Dec 28, 2005I have a sub that passes values from my form to my stored procedure. The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page. Here's where I'm stuck: Public Sub InsertOrder() Conn.Open() cmd = New SqlCommand("Add_NewOrder", Conn) cmd.CommandType = CommandType.StoredProcedure ' pass customer info to stored proc cmd.Parameters.Add("@FirstName", txtFName.Text) cmd.Parameters.Add("@LastName", txtLName.Text) cmd.Parameters.Add("@AddressLine1", txtStreet.Text) cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue) cmd.Parameters.Add("@Zip", intZip.Text) cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text) cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text) cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text) cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text) cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text) ' pass order info to stored proc cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue) cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue) cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue) 'Session.Add("FirstName", txtFName.Text) cmd.ExecuteNonQuery() cmd = New SqlCommand("Add_EntreeItems", Conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc) <------------------------- Dim li As ListItem Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar) For Each li In chbxl_entrees.Items If li.Selected Then p.Value = li.Value cmd.ExecuteNonQuery() End If Next Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder) and pass that to my second stored procedure (Add_EntreeItems)
View 9 Replies View RelatedSQL Server 2012 :: Executing Dynamic Stored Procedure From A Stored Procedure?
Sep 26, 2014I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure
at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT
I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT
System Stored Procedure Call From Within My Database Stored Procedure
Mar 28, 2007I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.
How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?
Thanks in advance