I'm using the ADO command object to call a stored procedure in SQL Server from Access 2000.
Set rst = cmd.execute
I receive the correct data back but I can't edit the recordset or addnew.
Is this possible????
Thanks
Hello,
I have some problems to edit a recordset in an ActiveX DTS using Vbscript.
Here is an example of the script :
dim varsql, varset, varconn
set varconn = CreateObject("ADODB.Connection")
set varset = CreateObject("ADODB.Recordset")
varconn.Open = "Provider=SQLOLEDB.1;Data Source=(local);Initial Catalog=Enregistrement3;user id = 'sa';password=''"
varsql = "SELECT * "
varsql = varsql & "FROM mytable "
varset.Open varsql, varconn, 3,3
msgbox "How much : " & varset.recordcount
if varset.recordcount >0 then
do while varset.eof
varset.edit
.........................
varset.update
varset.movenext
loop
end if
varset.close
varconn.close
Does someone see what is wrong ?
I allways get -1 for the varset.recordcount ! (I checked there are some records into the table).
I don't think it is a problem of user's right as it works with the same user configuration using a SQL action requery.
If I modify the open statement like this :
varset.Open varsql, varconn, 1
varset.recordcount contains the good number of records but the recordset is read noly and can't be modified...
Any Help will be very wellcome !
TIA
i am using .NET front end and back end SQL Server 2k.
for insert, update, delete and select queries what should i use stored procedure or direct sql through front end.
Hi,
I'm from Argentina. I'm not an expert at all in SQL, I know very little about it. I've read in MSDN Library that in order to edit a stored procedure I must right-click the procedure to modify, and then click Design.
I'm using SQL Server Management Studio (not Express) and I don't see any "design" option. I do see a "modify" option. I clicked there and modified just a number I wanted to. Once modified I clicked on the X button to shut the file hoping the system would ask me if I wanted to save the changes made. When I clicked "yes" the file saved into "my documents > sql management studio > proyects".
I checked the original Store Procedure file inside "Programmability > Stored Procedures" and obviously it wasn't altered. I have now a file called "SQLQuery31" in "my documents > sql management studio > proyects" that seems to have the modification made.
Why do I have it there (inside My Documents) instead of modifying the original one? What must I do to get that stored procedure modified?
Thanks from Argentina !!
Help!
I am trying to write a stored procedure that will execute a SQL statement that returns multiple records. Then I need to be able to step through each record, examining a value in one of the fields (and then perform subsequent actions depending on the value).
My problem is that I don't know how to step through a recordset in T-SQL. There doesnt seem to be a "recordset" type object that I can move through. Or can this be done via a cursor? If so, how?
Any help is greatly appreciated!
Thanks!
Decidion
HELP HELP HELP!!
I have two questions.
I'm using VB 6.0 with ADO to SQL 7.0. My stored procedure works fine with Query Analyzer. I pass one parameter.
sp_test "1234"
And I get a recordset returned.
The stored procedure looks like this:
CREATE PROCEDURE sp_test
@facility_key varchar(255) = null
AS
/*DECLARE @sql1 varchar(255)*/
SELECT * FROM v_reimbursement_report
WHERE facility_key = @facility_key
/*IF @facility_key <> null
PRINT "0"
BEGIN
SELECT * FROM v_reimbursement_report
WHERE facility_key = @facility_key
END*/
When I uncomment the commented lines, I can still get a recordset returned using the query analyzer. And I get the same recordset returned when I use VB ADO and leave the stored proc lines commented out. However, when I call the procedure using the same VB code with the stored proc lines uncommented, I get -1 returned for rs.recordcount:
Private Sub Main_Click()
On Error GoTo Err_Main
Dim lsFacility As String
Dim lsReportName As String
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim gconn As ADODB.Connection
Dim param_facility_key As ADODB.Parameter
Dim gConnString As String
gConnString = "Trusted_Connection=Yes;UID=sa;PWD=yoyo;DATABASE=y ada;SERVER=ya;Network=dbnmpntw;Address=ya;DRIVER={ SQL Server};DSN=''"
Set gconn = New ADODB.Connection
Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command
gconn.Open gConnString
cmd.Parameters.Refresh
lsFacility = "1234"
Set param_facility_key = cmd.CreateParameter("facility_key", adVarChar, adParamInput, 255)
cmd.Parameters.Append param_facility_key
param_facility_key.Value = lsFacility
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_reimbursement_report_test"
cmd.Name = "sp_reimbursement_report_test"
Set cmd.ActiveConnection = gconn
rs.Open cmd, , adOpenKeyset, adLockOptimistic
MsgBox rs.RecordCount
Exit_Main:
Screen.MousePointer = vbNormal
gconn.Close
rs.Close
Exit Sub
Err_Main:
Screen.MousePointer = vbNormal
MsgBox "Error " & Err.Number & " has occurred: " & Err.Description
End Sub
Here are my questions:
1. Why don't I get a recordset returned to VB if I have anything (the commented lines) except a simple SELECT statement in the stored proc.
2. Why must I do a Print "0" (or anything) command in the stored procedure within the IF statement to see a recordset return?
TIA for any help you can give....this one has been keeping me up....and my company down.
JWB
Dear all,
I have a big problem in using SQL Server stored procedures:
When I have two select statement in the same procedure, the first one will use for returning specific information for the second one to select appropiate result to the client, but the stored procedure just return the first select statement recordset! What can I do? (I use VB Data Environment to access the stored procedures)
Is it possible to have a stored procedure that passes recordsets which are created and passed from different tables and joins the information in those recordsets to form one group of information?
View 1 Replies View RelatedIn a nutshell, I am trying to set a combobox's row source using a stored procedure. Surely there is an easy way to do that.
I am working with SQL 2000 as my back-end, and my front-end is an Access Project. The stored procedure I am trying to run is on a different database then the one my project is connected to, but from what I can see in my de-bugging efforts, that is not the problem.
The Stored Procedure;
CREATE PROCEDURE dbo.sp_eeLinksByName
@EmployerNum char(6)
AS
SELECT dbo.TIMS_eeLinksByName.eeLink, dbo.TIMS_eeLinksByName.Employee FROM dbo.TIMS_eeLinksByName
WHERE (dbo.TIMS_eeLinksByName.eeErNum = @EmployerNum)
ORDER BY dbo.TIMS_eeLinksByName.Employee
returns 169 records when I run it directly from the MS Visual Studio environment.
However whe I try to run it from VBA with the following code;
Dim sp_eeLinksByName As String
Dim ConnectionString As String
Const DSeeLinksByName = "SOS-1"
Const DBeeLinksByName = "Insync"
Const DPeeLinksByName = "SQLOLEDB"
Dim objeeLinksByNameConn As New ADODB.Connection
Dim objeeLinksByNameRs As New ADODB.Recordset
Dim objeeLinksByNameComm As New ADODB.Command
ConnectionString = "Provider=" & DPeeLinksByName & _
";Data Source=" & DSeeLinksByName & _
";Initial Catalog=" & DBeeLinksByName & _
";Integrated Security=SSPI;"
' Connect to the data source.
objeeLinksByNameConn.Open ConnectionString
' Set a stored procedure
objeeLinksByNameComm.CommandText = sp_eeLinksByName
objeeLinksByNameComm.CommandType = adCmdStoredProc
Set objeeLinksByNameComm.ActiveConnection = objeeLinksByNameConn
' Execute the stored procedure on
' the active connection object...
' "CurrTSCalendar" is the required input parameter,
' objRs is the resultant output variable.
objeeLinksByNameConn.sp_eeLinksByName CurrTSEmployer, objeeLinksByNameRs
' Display the result.
'Debug.Print "Results returned from sp_CustOrdersOrders for ALFKI: "
Select Case objeeLinksByNameRs.RecordCount
Case 0
'Do Nothing
Case Is > 0
'Get the Employee List
objeeLinksByNameRs.MoveFirst
Do While Not objeeLinksByNameRs.EOF
MyControl.AddItem (objeeLinksByNameRs.Fields("eeLink") & ";" & objeeLinksByNameRs.Fields("Employee"))
objeeLinksByNameRs.MoveNext
Loop
End Select
'Clean up.
'objRs.Close
objeeLinksByNameConn.Close
Set objeeLinksByNameRs = Nothing
Set objeeLinksByNameConn = Nothing
Set objeeLinksByNameComm = Nothing
I get an "Object Variable or With Blick Vraiable not Set"...... for the life of me I do not know why? Does anyone have any thoughts?
The stored procedure I created returns all records from the table if no parameters are passed, as expected. When I pass in the only parameter, I get 0 records returned when there should be one or more returned. I'm sure it's something simple in my syntax, but I don't see it.
This call returns all records:
exec webservices_BENEFICIAL_USES_DM_SELECT
This call returns 0 records, when it should return 1:
exec webservices_BENEFICIAL_USES_DM_SELECT @DISPOSAL_AREA_NAME='Cell 8'
Here is the stored procedure:
ALTER PROCEDURE [dbo].[webservices_BENEFICIAL_USES_DM_SELECT]
-- Add the parameters for the stored procedure here
@DISPOSAL_AREA_NAME DISPOSAL_AREA_NAME_TYPE = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
IF @DISPOSAL_AREA_NAME IS NULL
BEGIN
SELECT*
FROMBENEFICIAL_USES_DM
END
ELSE
BEGIN
SELECT*
FROMBENEFICIAL_USES_DM
WHEREDISPOSAL_AREA_NAME = '@DISPOSAL_AREA_NAME'
END
END
Hi All. My question is this. I have a complex stored procedure in SQLServer which works fine when I run it in Query Analyzer. However, whenI call it within my ASP script, it returns nothing, and sometimes locksup. If I change my script to call other existing stored procedures itworks fine. It's just with this particular stored proc. I have triedvarious DB calls in ASP, such as opening the recordset through an ADOconnection and through the Command object but to no avail. Here is mySQL:SET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS OFFGOALTER PROCEDURE dbo.sp_getProfessionalsByTypeID@typeID intASdeclare @catID intdeclare @userID intdeclare @strUserName varchar(100)declare @strFirstName varchar(100)declare @strLastName varchar(100)declare @strTypeName varchar(100)declare @strCategoryName varchar(100)declare @strPictureURL varchar(100)declare @sql varchar(1000)declare @a varchar(100)declare @b varchar(100)declare @c varchar(100)declare @d varchar(100)beginset @a=''set @b=''set @c=''set @d=''--Create Temp TableCREATE TABLE #QueryResult (nCatID int, nTypeID int, nUserID int,strUserName varchar(100), strFirstName varchar(100), strLastNamevarchar(100), strTypeName varchar(100), strCategoryNamevarchar(100),strPictureURL varchar(100))--Search QuerybeginINSERT #QueryResultSELECTdbo.tbl_musician_type.nCatID, dbo.tbl_musician_type.nTypeID,dbo.tbl_users.nUserID, dbo.tbl_users.strUserName,dbo.tbl_users.strLastName,dbo.tbl_users.strFirstName,dbo.tbl_musician_type.strTypeName, dbo.tbl_category.strCategoryName,dbo.tbl_professionals.strPictureURLFROMdbo.tbl_musician_type INNER JOINdbo.tbl_category ON dbo.tbl_musician_type.nCatID= dbo.tbl_category.nCategoryID INNER JOINdbo.tbl_profile ONdbo.tbl_musician_type.nTypeID = dbo.tbl_profile.nTypeID INNER JOINdbo.tbl_users ON dbo.tbl_profile.nUserID =dbo.tbl_users.nUserID LEFT OUTER JOINdbo.tbl_professionals ON dbo.tbl_users.nUserID= dbo.tbl_professionals.nUserIDWHEREdbo.tbl_musician_type.nTypeID = @typeIDend--Create Temp TableCREATE TABLE #QueryResult2 (ID int IDENTITY,nCatID int, nTypeID int,nUserID int, strUserName varchar(100), strFirstName varchar(100),strLastName varchar(100), strTypeName varchar(100), strCategoryNamevarchar(100),strPictureURL varchar(100), strArtist varchar(100),strAlbumTitle varchar(100), strRecordLabel varchar(100), strYearvarchar(100))--Now Declare the Cursor for Speakersdeclare cur_musicians CURSOR FOR--Combined Results Groupedselect distinct nCatID, nTypeID, nUserID, strUserName, strLastName,strFirstName, strTypeName, strCategoryName, strPictureURLFrom #QueryResultopen cur_musiciansfetch next from cur_musicians INTO @catID, @typeID, @userID,@strUserName, @strLastName, @strFirstName, @strTypeName,@strCategoryName, @strPictureURL--Loop Through Cursorwhile @@FETCH_STATUS = 0beginSELECT TOP 1 @a = strArtist, @b=strAlbumTitle,@c=strRecordLabel, @d=strYearFROM dbo.tbl_profile_discogwhere nTypeID = @typeID AND nCategoryID = @catID AND nUserID =@userIDinsert #QueryResult2select @catID as nCatID, @typeID as nTypeID, @userID as nUserID,@strUserName as strUserName, @strLastName as strLastName, @strFirstNamestrFirstName, @strTypeName as strTypeName, @strCategoryName asstrCategoryName, @strPictureURL as strPictureURL, @a ashighlightArtist, @b as highlightAlbumTitle, @c as highlightRecordLabel,@d as highlightYearfetch next from cur_musicians INTO @catID, @typeID, @userID,@strUserName, @strLastName, @strFirstName, @strTypeName,@strCategoryName, @strPictureURLset @a = ''set @b=''set @c=''set @d=''endselect * from #QueryResult2 TI--Clean Upclose cur_musiciansdeallocate cur_musiciansdrop table #QueryResultdrop table #QueryResult2endGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO
View 5 Replies View RelatedHi All,
I'm hoping somebody can help me with this as it is driving me mad. I've created a stored procedure which returns Employee information recordset when the windows username is passed to it as a parameter. I want to then store this information in Session variables so content can be filtered depending on employee status, but when I execute my code no records are returned. I know for a fact that the stored procedure works because I bound it sqldatasource and displayed results from it in a Datalist and tested it in sql server. My code is as follows can anybody see any problems with it, in runs through fine with but when I try a read a field from the datareader in says there is no data to read.
Dim CurrentUser As String, Pos1 As Int16, EmployeeId As Int32
Dim cn As New System.Data.SqlClient.SqlConnection
Dim param As New System.Data.SqlClient.SqlParameter
Dim reader As System.Data.SqlClient.SqlDataReader
Dim cmd As New System.Data.SqlClient.SqlCommand
CurrentUser = CStr(User.Identity.Name)
Pos1 = InStr(CurrentUser, "") + 1
CurrentUser = Mid(CurrentUser, Pos1)
Session("User") = CurrentUser
Session("CID") = Nothing
cn.ConnectionString = "Data Source=LAPTOP-4SQLEXPRESS;Initial Catalog=SCMdb;Integrated Security=True"
cn.Open()
cmd.Connection = cn
cmd.CommandText = "CurrentUser"
cmd.CommandType = CommandType.StoredProcedure
param = cmd.CreateParameter
param.ParameterName = "@UserName"
param.SqlDbType = SqlDbType.VarChar
param.Value = CurrentUser
cmd.Parameters.Add(param)
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
EmployeeId = reader.Item("EmployeeID")
reader.Close()
Any help would be much appricated this is driving me mad.
Thank You
Shaft
I'm having trouble getting a recordset out of stored procedure in ADO. The SP executes without errors, but the recordset object I return into is always closed.Here is my code:
<%
......
Set cmm = Server.CreateObject("ADODB.Command")
Set cmm.ActiveConnection = Connect
cmm.CommandType = adCmdStoredProc
cmm.CommandText = "dbo.client_updates_proc"
cmm.Parameters.Refresh
cmm.Parameters(1) = client_id
Set logRS = cmm.Execute()
if not logRS.EOF then
......
%>
My SP has one parameter, which I set above, and it ends with a select statement. When I run the SP in Query Analyzer, it outputs the table of results as is should, but I always get an error on 'if logRS.EOF then', saying that the object is closed.
I have a recordset returned from a stored procedure executed in the form open event. Could this recordset append to the form's recordsource property in the form's open event in VB? if so, what's the syntax?
Private Sub Form_Open(Cancel As Integer)
dim ...
Set add_bag_results = Nothing
With add_bag_results
.ActiveConnection = CurrentProject.Connection
.CommandType = adCmdStoredProc
.CommandText = "spSampling_add_bag_results"
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
'.Parameters.Append .CreateParameter("@lotnum", adInteger, adParamInput, 4, rs_add_bag_results(1))
.Parameters.Append .CreateParameter("@lotnum", adInteger, adParamInput, 4, lot_n)
..
Set rs_add_bag_results = .Execute
End With
Me.RecordSource = rs_add_bag_results ?
Thanks!
I am trying to follow an exercise for debugging a stored procedure in the .net IDE. I have had success in adding a connection to the application, until I get the point of where I should set a BreakPoint in the stored procedure "Ten Most Expensive Products" which belongs to the Northwind database. When I open Sever Explorer -->Data Connections- ->WI0001.NorthWind.dbo-->Stored Procedures and Right-click the procedure, the context menu does not give me an edit item to select to open this SP in the editor. Nor can I double-click on the stored procedure to open it either.
View 1 Replies View RelatedI was working on a flat file import spec yesterday, using the import wizard, and I saved my work as a SSIS package. Today, if I connect to Integration Services through SQL Management Studio, I can see the package, but I can't edit it. I've tried to open the package using BIDS, but I can't find it on the server. Obviously it exists, but where?
How the heck can I edit this package? And, where is it? I don't understand why things are so much more difficult in 2005 vs. 2000.
Thanks.
Hi, I created the stored procedure with encryption and now I want to see the same procedure but I am not able to see the text of procedure with SP_HELPTEXT command. Please help so that I could see my stored procedure with sp_helptext command. Thanks.
My command was:
sp_helptext dm_sp_Outstanding_Events
Error message:
The text for object 'dm_sp_Outstanding_Events' is encrypted.
I created a few in SqlServer2005 DEV version and ran them manually with success. But where are they? I chose the SqlServer storage option and I see them when I create Job and steps but how do I view/edit these packages from SqlServer Mgmt. Studio? The SSIS service is running.
View 6 Replies View RelatedHello,
I have created a simple maintenance plan in SQL Studio. When I double-click on the task boxes (ie 'Rebuild Index', 'Backup') to just make changes or to look, I get the following message:
Cannot show editor for this task
Additional information:
Exception from HRESULT: 0xC0010014 (Microsoft.SqlServer.DTSRuntimeWrap)
I also cannot save any change that I make in the schedule of the plan.
I HAVE installed Integration Services, which at least enabled me to initially SAVE the plan. I just can't edit the tasks or change the schedule.
Any help is most appreciated!
Eunice Griffin
Urgent help needed!I moved an application from ASP+ACCESS to ASP+MS SQLSERVER and I havethe following problem:If the join on two tables results on duplicate colum names (whichappear in both tables) I could reference them by using:RECORDSET("TABLENAME.COLUMNAME")However with SQLServer if I try this kind of reference I get an errormessage.How can between two colums with the same name from two differen tables?Thanks in advance!Adam
View 5 Replies View RelatedI can create 1 view of a database then use that views results to query
but i want do this with a stored procedure
pass some data to it, so it selects some data
query the results it created and output this data.
ie
pass 2 values to the procedure
(
@FirstValue int,
@secondValue int output
)
select from a database with the firstpassed value
Select *
From TableName
Where ID = @FirstValue
Then
using the results from the above select
Select *
From theResultsOfAbove
Where ID = @SecondValue
any ideas would be fabo !
I have a Stored Procedure that I am calling from ADO 2.7 in Visual Basic. The process works fine when the stored procedure has records to return. However , when the recordset is return emtpy ADO does not recognize the recordset as being open and empty. For example:
VB Code to open recordset:
Set rstClass1 = New ADODB.Recordset
With rstClass1
.CursorLocation = adUseServer
Set .ActiveConnection = DBConn
.Source = strRecordSource
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
If Not .EOF Or Not .BOF Then
The program bombs on the If Not .EOF statement with Error 3704 -
"Operation is not allowed when the object is closed.
When I run the Stored Procedure In QA using an ID that I know will NOT return any records the result window returns nothing. I noticed that on some of my other SP's the result window will at least return the column titles. These SP's also do not produce the 3704 error in ADO.
Is there a command or a certain method to construct my SP so that is will return enough for ADO to know that the recordset did open its just empty?
The SP is ugly looking but I will post if need be.
Hi
I want a functionality such that I want to return a select query resultset and a varchar variable from a procedure. How can I achieve that,and moreover how can I fetch them in ASP??
Waiting for someone to shed a light of hope.
Thanx a lot
I have two questions:
1) Is it possible to rename a SQL table –or- copy the content of a table into a new table ?
2) How to replace the renamed or replaced table name inside the code of Stored Procedures that references it ?
Just curious; since the new GUI doesn`t show you the master databse, how can I use it to edit stored procedures that I want to reside in master?
View 1 Replies View RelatedI'm looking for a way to track usage of SPs. I haven't found any TSQL tool that is already tracking that information. Long ago another DBA added an exec to the end of some SPs to log the usage to a table.
If there is a function already tracking this I'd like to know.
If not, I want to tack an exec line to the end of all the SPs but I don't want to do it by adding it 1600 times manually. Is there any trick in the scripting process I can do to make this easier?
JB
I have two questions:
1) Is it possible to rename a SQL table –or- copy the content of a table into a new table ?
2) How to replace the renamed or replaced table name inside the code of ALL Stored Procedures that references it ?
Have sql 2005 express installed.
Running a database for a DotNet Nuke site
Opened Microsoft SQL Server Management Studio Express.
Navigated to CASPORTALDatabasesDotNetNukeProgrammabilityStored Proceduresdbo.AddUser
Right clicked dbo.AddUser and selected "modify"
This allowed me to paste the additional code into the right hand window/pane, however when I try and save this it wants to save it a seperate file /query. Is there something I don't understand?
am I not able to edit the original stored procedure?
I need to return a rowset from a stored procedure. The returned rows will have ten columns and need to be bound to a gridview. Here is a code snippet.
'Create a DataAdapter, and then provide the name of the stored procedure.MyDataAdapter = New SqlDataAdapter("TMPTABLE_QUERY", MyConnection)
'Set the command type as StoredProcedure.
MyDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
'Create and add a parameter to Parameters collection for the stored procedure.MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@condition_cl", _
SqlDbType.VarChar, 100))
'Assign the search value to the parameter.MyDataAdapter.SelectCommand.Parameters("@condition_cl").Value = sqlwhere & orderby
'ASSIGN THE OUTPUT PARAMETERS. HERE IS WHERE I NEED HELP (?????????????????)
DS = New DataSet() 'Create a new DataSet to hold the records.
MyDataAdapter.Fill(DS, "TMPTABLE_QUERY") 'Fill the DataSet with the rows returned.
'Set the data source for the DataGrid as the DataSet that holds the rows.GridView1.DataSource = DS.Tables("TMPTABLE_QUERY").DefaultView
GridView1.DataBind()
MyDataAdapter.Dispose() 'Dispose of the DataAdapter.
MyConnection.Close()
In my little research, I have seen examples of how to return a single value, but not multiple rows. I essentially have two problems. I'm not sure how my output parameters are to be defined and added. Do I need a separate 'Parameters.Add' statement for each column field value returned or can I do a single 'Parameters.Add' statement to define the whole row as an output parameter? Also, upon returning from the call to the SP, will I need a looping mechanism to populate the recordset for each individual record returned, or will the 'MyDataAdapter.Fill(DS, "TMPTABLE_QUERY") suffice, as included in my code above?
Thanks in advance.
I have a user on my database that has the following base permissions :
public
db_datareader
I need to give this user permission to edit a single stored procedure. I have tried using the following command :
GRANT ALL ON stored_procedure_name TO username
Which executes successfully, but the user still cannot edit the stored procedure.
If I give the user db_ddladmin permission they can edit all the user stored procedures, but for security reasons I would prefer to be able to this this at procedure level rather than a global permission on all user procs.
Does anybody know how I can do this?
EDIT : This is on SQL 2000
Which tool is used to maintain packages stored in the MSDB. Do you have to export them as file based to edit them?
View 3 Replies View RelatedHello everybody!
trying to do the following:
-create a report in access project
-got 3 stored procedures which return data that shall be shown on report
-need one recordset as datasource (or can i use more than one here?)
Problem:
Data was unrelated before, now needs to be on same report, that's why until now i have 3 different pretty complex stored procedures returning a recordset each.
I could of course copy and paste the whole 3 into 1 new stored proc, but when one changes i had to change the newly created one too (which might get messy when doing a lot of maintenance and changes on the others)
Can create a stored procedure that simply integrates those 3 into one recordset something like this (in pseudo-code):
CREATE PROCEDURE IntegrateSPs AS
INTEGRATE
SP1,SP2,SP3
INTO myRecordset
Anything like this possible?
thx in advance,
Kuma
Hi,
This may be a really silly question, but I am starting to experiment with stored procedures for the first time and I am not really sure about how they work. I am working in SQL Server Management Studio Express...
When putting a SELECT query statement into a stored procedure and executing it, how or where can you get or find the actual record set supposedly retrieved?
Thanks for any help..
--------------------------set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [petrander].[DynamicQuery] @taxparent int = 1ASBEGIN SET NOCOUNT ON; SELECT ID, TaxonID, Taxon FROM QueryView WHERE ParentID1 = COALESCE(@taxparent, ParentID1) AND ParentID2 = COALESCE(@taxparent, ParentID2) AND ParentID3 = COALESCE(@taxparent, ParentID3) AND ParentID4 = COALESCE(@taxparent, ParentID4) AND ParentID5 = COALESCE(@taxparent, ParentID5) AND ParentID6 = COALESCE(@taxparent, ParentID6) AND ParentID7 = COALESCE(@taxparent, ParentID7) AND ParentID8 = COALESCE(@taxparent, ParentID8)END