Returing ADO Recordset From Stores Procedure
Nov 24, 2003
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.
View 3 Replies
ADVERTISEMENT
May 5, 2005
I am trying to select the HdwId value of a table named "tblHdw" and trying to insert it into the HwdId field in a table named "tblLoc".
If you have any suggestions?
Thanks,
MattyPee
View 1 Replies
View Related
Feb 14, 1999
I read in a technical journal (somewhere) that it is possible to create a ActiveX DLL in Visual Basic and use the DLL in a stored procedure. There were some limitations (because of stored procedures) but this is still a very powerful feature. Has anyone ever heard of this or read about this? Is there s source for explanation and examples?
Thanks, Rich.
View 1 Replies
View Related
Feb 23, 2004
I used Datagrid to show "Title", "Location" and "Date", It works very well.
I want to sort DataGrid data, that is when user click the "Title", "Location" or "Date",
my asp.net code will through class and send "Sort" parameter to stores procedure to get the new data and bind to DataGrid.
Here is my stores procedure:
CREATE Procedure JobSearch
(
@Search varchar(150),
@Sort varchar(50)
)
AS
SELECT
JobTitle,
JobLocationCity,
JobLocationState,
PostDate
FROM
Job
WHERE
JobTitle LIKE '%' + @Search + '%'
OR
JobKeywords LIKE '%' + @Search + '%'
IF @Sort = "Title"
ORDER BY JobTitle
IF @Sort = "Location"
ORDER BY JobLocationState, JobLocationCity
IF @Sort = "PostDate"
ORDER BY PostDate DESC
When I test stores procedure in SQL Server, I got the error about "Error 156: Incorrect syntax near the keyword 'ORDER' ".
Who has experience about stores procedure, please help me to correct this error.
View 12 Replies
View Related
Apr 7, 2015
I am creating a SP below and it keep saying must declare scalar variable @comments,I have already declared @comments varchar (255)
error---> (Msg 137, Level 15, State 2, Procedure CheckPatientComments, Line 26
Must declare the scalar variable "@commnets".
)
quote:
CREATE PROCEDURE [dbo].[CheckPatientComments] (@enc_id varchar(36) OUTPUT, @data_ind CHAR(1) OUTPUT)
AS
BEGIN
DECLARE
@comments varchar(255)
[code]....
View 2 Replies
View Related
Aug 2, 2006
Hi There
2 Questions :
1. Almost in every SB example you will see this sql :
BEGIN TRANSACTION
WAITFOR (
RECEIVE TOP (1)
@MessageType = message_type_name,
@Message = message_body
FROM [Queue1]
WHERE conversation_handle = @ConversationHandle
), timeout 5000;
If this sql in an activated sp do you really have to have the waitfor ? Since the sp will only be fired if there is a message on the queue ?
2. It is reccomended that for high volume SB apps you do not do a top(1) receive but process batches. Exactly what is the best practice to do this. Receive a batch into a table variable and then what ? Process through it with a cursor ? That is not very efficient either, i would just like some insight into batch queue processing as everywhere i have seen uses top (1) from the queue ?
Thanx
View 3 Replies
View Related
Dec 11, 2000
If a user connects to SQL Server through say QA using a trusted connection.
What would the function be to return the NT username? I tried this (I am a
system admin) and it returned dbo. I was looking for my NTusername which I can see when I query processes
View 1 Replies
View Related
Nov 5, 2004
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.
View 10 Replies
View Related
Feb 12, 2008
I created a report using RS with a SQL stored procedure (with date range parameters and a cursor to build the data) as the data source. When I execute the proc in SQL it returns over 300 records. When i go the the data tab on my report view the data, the proc and the preview only returns the first row. What am i doing wrong? I am new to RS.
View 3 Replies
View Related
Feb 26, 2002
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
View 1 Replies
View Related
Jul 12, 2000
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
View 3 Replies
View Related
Apr 2, 2000
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)
View 2 Replies
View Related
Jun 8, 2000
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 Related
May 28, 2005
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
View 1 Replies
View Related
Dec 7, 2005
In 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?
View 7 Replies
View Related
Jun 12, 2008
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
View 4 Replies
View Related
Jul 23, 2005
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 Related
May 30, 2004
Hi,
I have posted this in the Experts Forum so I have put replies to questions in here as well (Hence the long post!!). Hope someone here can help!!
I am having trouble with a T-SQL query. I have three tables in a join and I need to limit the results returned by query using a group by.
All of the fields in the result group are the same except for two fields, a date field and a varchar field. What I want to achieve is to return the row that has the latest date, and I need the varchar field as that is the information I am after. The problem is if I include the varchar field in the group by, it is returned as a separate group, if I do not included it in the group by, Server: Msg 8120 (aggregate fn / group by) error occurs!
Any ideas how to get around this?
Sample code and results below:
Query:
select distinct s.id, [active-from], code, s.desc, [scheme], [market-id], [market-id]+'CODE'
from [coded] c join sec s on s.id = c.id
join [mkt-security] m on m.id = s.id
where ([market-id] = [scheme] or [market-id]+'CODE' = [scheme])
Group by s.id, [active-from], code, s.desc, [scheme], [market-id], [market-id]+'CODE'
Sample results:
id : [active-from] : code : desc : [scheme] : [market-id]
--------------------------------------------------------------------------------------------------------------------------
10449 :1993-07-21 : ADV : PXX Group Limited : ABCD : ABCD : ABCDCODE
10449 :2003-03-27 : PVO : PXX Group Limited : ABCD : ABCD : ABCDCODE
10469 :1986-10-24 : AQL : CO Ordinary Shares : DEFG CODE : DEFG : DEFG CODE
10469 :2000-10-02 : AQL : CO Ordinary Shares : DEFG CODE : DEFG : DEFG CODE
10469 :2001-09-21 : CER : CO Ordinary Shares : DEFG CODE : DEFG : DEFG CODE
The result I want to achieve is to only return these two rows:
10449 :2003-03-27 : PVO : PXX Group Limited : ABCD : ABCD : ABCDCODE
10469 :2001-09-21 : CER : CO Ordinary Shares : DEFG CODE: DEFG : DEFG CODE
i.e. those with the latest [active-from] date, but I must have the corresponding code value.
I have tried a correlated sub query on the [active-from] field, but it is possible to have different id’s with the same [active-from] date so it did not work.
The primary key for the [coded] table is the combination of [active-from], code and [scheme]. The tables are truncated and then imported back into the SQL database from a Progress database daily, so restructuring the table(s) is not a possibility.
I have rewritten the query to "AND [ACTIVE_FROM] in (SELECT MAX([ACTIVE_FROM]) FROM CODED AND MAX([CODE]) = S.[CODE] AND MAX([SCHEME]) = S.[SCHEME]", (Which I think is the T-SQL for you query), but it returned the error "An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference."
Also I think the query need to use a Group by as the results I need returned are those with the latest [ACTIVE_FROM] date WITHIN the group of [ID]and [MARKET-ID]. I can't think of any conditions where I can filter the results in the where clause.
The [ ] in the T-SQL is to identify table names as some of the table names (from the Progress db) use characters (the "-") that are unsupported for table names in T-SQL.
I am using the group by as I want to return specific groups of results ( [ID] and [MARKET-ID] ), that have the latest active-from date. I have made a change to that posted before to illustrate:
All results:
id : [active-from] : code : desc : [scheme] : [market-id] : [market-id]+’CODE’
10449 :1993-07-21 : ADV : PXX Group Limited : ABCD : ABCD : ABCDCODE
10449 :2003-03-27 : PVO : PXX Group Limited : ABCD : ABCD : ABCDCODE
10769 :1986-10-24 : AQL : CO Ordinary Shares : WXYZ CODE : WXYZ : WXYZ CODE
10769 :2000-10-02 : AQL : CO Ordinary Shares : DEFG CODE : DEFG : DEFG CODE
10769 :2001-09-21 : CER : CO Ordinary Shares : DEFG CODE : DEFG : DEFG CODE
The result I want to achieve is to only return these three rows:
10449 :2003-03-27 : PVO : PXX Group Limited : ABCD : ABCD : ABCDCODE
10769 :2001-09-21 : CER : CO Ordinary Shares : DEFG CODE: DEFG : DEFG CODE
10769 :1986-10-24 : AQL : CO Ordinary Shares : WXYZ CODE : WXYZ : WXYZ CODE
The reason the last row needs to be returned is that it has a different market id to the other row, despite its active-from date being earlier.
I have tried the self join earlier, but it would then only return 1 of the rows that have an id of 10769!!
Hope this makes sense!!
Cheers,
Guytz
View 2 Replies
View Related
Jun 7, 2007
Hi 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
View 5 Replies
View Related
Nov 16, 2001
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
View 1 Replies
View Related
Sep 24, 2003
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.
View 3 Replies
View Related
May 12, 2004
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!
View 2 Replies
View Related
Feb 26, 2008
I am trying to count a column field in a single table and return two count values as one record set using group by.
field1 = group by (department) nvarachar
field2 = count (closed) datetime
I have tried using derived tables with no luck getting the desired result.
field2 is a datetime field as indicated I want a count for two conditions
1. WHERE field2 is null
2. WHERE field2 is not null
End Results would like this
======
Department | OpenItems | ClosedItems
Department1 | 32 | 24
Departmnet2 | 87 | 46
Department3 | 42 | 76
=======
I got it *almost* working with derived tables, but the group by function was not putting the department as one single row. I was getting multiple rows for departments.
I realize this is probably a simple answer and I am making this a lot harder than it actually is....
Any suggestions?
View 3 Replies
View Related
Sep 7, 2006
Hi i have a csv file where the last column usually happens to be null. So my file is like this:
1,2,3,,{LF}
4,5,6,,{LF}
7,8,9,,{LF}
{CR}
I run the dts export from csv file to db and it doesnt save the last row (column5) in the table us null. instead it stores the {LF} character. Ascii =10. How can i resove this problem.
the csv comes from a third party application. the DTS needs to un automatically and somehow resolve that last problem on its own. but how??? is there a code that can trim the last character in the last line to delete the last {LF} or some query in DTS that can achive this.....
View 4 Replies
View Related
Feb 5, 2008
Hi All,
Please let me know within Reporting server database which table stores the actual report name,keywords and author of the report.
I want to search the report based on the report name using my own custom code.It will be helpfull if i get the table name so that i can fire select query.
Thanks in advance,
Prabhanjana
View 1 Replies
View Related
Jan 19, 2008
Hello,I need to learn where mssql server stores its data. I knew it was inProgram filesCommon filesBut I cant find.. Its urgent pls..Thanx
View 4 Replies
View Related
Feb 25, 2008
Hi,
I have report scheduled in a SSRS. SSRS creates a job and from MSDB.DBO.SysJobActivity the
Next_Scheduled_Run_Date column provides the next execution date of the report.
Can any one tell me what is the date format that SSRS stores is it a getdate() or a UTC Date??
View 1 Replies
View Related
Feb 20, 2001
Hi, I was wondering if someone could help out. I need to create a stored procedure, but before i do so, I need to know if I can store a conditional expression in a string and just use that variable as my condition for the WHERE clause. The reason I ask this is because I am trying to create a stored procedure that queries different things depending on what the inputs are.
View 2 Replies
View Related
Jun 9, 2006
Hi,We have a lame app that uses 2 datetime(8) fields, 1 stores the date, theother the time.example query:select aud_dt, aud_tmfrom ordersresults:aud_dt aud_tm2006-06-08 00:00:00.000 1900-01-01 12:32:26.287I'm trying to create a query that give me records from the current date inthe past hour.Here's a script that gives me todays date but I cannot figure out the time:select aud_dt, aud_tm, datediff(d,aud_dt,getdate()), datediff(mi, aud_tm,getdate())from orderswhere (datediff(d,aud_dt,getdate()) = 0)results:aud_dt aud_tmdatediff(0=today) timediff (since 1900-01-01)2006-06-08 00:00:00.000 1900-01-01 12:32:26.287 055978689I added this next part to the above query but it does not work since thedate/time is from 1900-01-01and (datediff(mi, aud_tm, getdate()) <= 60)Thanks for any help.
View 2 Replies
View Related
Mar 21, 2007
DB design question
I have multiple tables with information about a user. The tables are Roles, Users, Groups and Profiles.
For a user session I need information from all those tables. Would it be better to make a table called UserSession and collect the necessary data from the above mentioned tables and stick them in one the UserSession table or should I just write a query that goes out and gets the data from the different tables.
What is better practice and what is faster?
View 4 Replies
View Related
May 8, 2015
how to measure a change in inventory over various stores. Â My sql2008R2 express db gets a new row of data everyday from each store(about 40 stores) for a single product stock count "OnHand" and if there is any new stock on order. Â When the new stock arrives it is added to the "OnHand" count. Â I want to measure the delta change per day,per store. Â I'm stuck on how to separate the stores and how to query the delta of stock.My data base looks like this
               Â
TimeStamp Store
OnHand OnOrder
2015/04/22 18 Â Â 1 - Concord
12
   0
2015/04/23 11 Â Â 1 - Concord
11
Â
[code]....
View 17 Replies
View Related
May 4, 2007
Hello,
Say there are 10 retail store locations that need to push their daily sales to the main server at the head office.
What techniques are used to accomplish this? I'd imagine the transaction rows would have to be some sort of a GUID.
What options are there? What techniques are used to ensure each stores orders have indeed been pushed to the main server?
View 3 Replies
View Related
Oct 22, 2007
Howdy all,
I have an Execute SQL Task that may return a result set. If it returns a result set, I'd like to log a failure in my package with the results visible.
I have logging turned on and that's working great. I've read about assigning results to a user variable of type Object and that's great. I can shred my results, thanks Jamie, with a Foreach loop no problem. Within that loop, I've got some VB that manipulates the values and will call Dts.Events.FireError as appropriate. However, VB is frowned upon here so my boss has asked that I push the VB logic into a Control Flow item.
I've built custom components already so I've got some familiarity with the process. Where I'm stuck at is figuring out _what_ the actual object type is in my code. The Connection manager is Native OLE DBSQL Native client. My Execute SQL Task uses a connection type of OLE DB with a Full result set. Results are stored in a variable named ErrorResultSet. Within the Execute method, I currently have this code set up in an attempt to pick apart the object and discover the available methods.
Code Block
Variables _variableCollection = null;
if (variableDispenser.Contains("ErrorResultSet"))
{
variableDispenser.LockForRead("ErrorResultSet");
}
variableDispenser.GetVariables(ref _variableCollection);
// Iterate through the variables that we were
// able to lock. Assigning values to entities as
// available.
foreach (Variable _en in _variableCollection)
{
switch (_en.Name)
{
case "ErrorResultSet":
Object _rs = _en.Value;
System.Type _type;
_type = _rs.GetType();
System.Data.DataSet _realResults;
_realResults = _rs as System.Data.DataSet;
// My expectation is that the cast of _realResults would
// not fail.
break;
}
}
// unlock before we go
_variableCollection.Unlock();
return DTSExecResult.Success;
At this point, my assumption is that the unboxed type of the recordset is not in the System.Data.DataSet inheritance chain as the cast failed. Anyone have insight into what it is? I can't seem to get any hits on google for what it's using behind the scenes in the Foreach ADO Enumerator.
Beyond the immediate question, anyone have thoughts on how else I can solve the problem? I had thought perhaps the task could raise an event if it returned rows but it didn't seem to have that functionality. Even if that had worked, telling the logging provider to capture the result set into the log might have been too much for native functionality. Another option I was thinking about would be to continue using the Enumerator and my custom component is a pure rewrite of the current Script task with the obvious downside being that I'd lose the generic-ness I was hoping to get with being able to hit my dataset.
View 8 Replies
View Related
