Return BIGINT OUTPUT param to VB!?

Please help me on this one.

I need to return a value to VB.
I've tried returning a numeric value NUMERIC(25,20) via an output parameter but this didn't work. I'm know at a point in wich I created a bigint and multiplied the value so that the decimals are gone. However it only returns NULL?!?!?!?!!?!?
Here's part of my stored proc

CREATE PROCEDURE dbo.uspCalcWeightedAverage
@.StartDate2 varchar(10),
@.EndDate2 varchar(10),
@.InMarket nvarchar(50),
@.InProductType int,
@.InWeekDay int,
@.WeightedAverage bigint OUTPUT
AS
...
...
SELECT @.WeightedAverage = cast(10000000000 * (SUM(HHF.FACTOR) / COUNT(PDF.FLAG)) as bigint)
FROM
TBL_PRODUCTDEFS PDF
INNER JOIN #DATESBETWEENINTERVAL DBI ON DATEPART(HH, [DBI].[DATE]) = [PDF].[HOUR]
INNER JOIN tbl_historichourlyfactors HHF ON DATEPART(D,DBI.DATE) = HHF.DayID
AND [PDF].[HOUR] = [HHF].[HOUR]
AND DATEPART(M,DBI.DATE) = [HHF].[Month]
WHERE
PDF.MARKETID = @.InMarketID
AND PDF.PRODUCTTYPEID = @.InProductTypeID
AND [PDF].[WD-WE] = @.InWeekDay
AND HHF.MARKETID = @.InMarketID
AND PDF.FLAG = 1
GROUP BY FLAG

When I retrieve the output param it returns a NULL value. the properties in VB say that the parameter has the following props:
attribute 64 (Long)
NumericScale 0 (Byte)
Precision 19 (Byte)
Size 0 (ADO_LNGPTR)
Type adBigInt
Value Null

I try to return it with the following code (got the code from a friend)

Public Function RunProcedure(ByVal v_strStoredProcName As String, ByRef r_varParamValues() As Variant) As ADODB.Recordset
Dim objAdoRecordset As ADODB.Recordset
Dim objAdoCommand As ADODB.Command
Dim lngCtr As Long
On Error GoTo RunCommand_Error

' Create cmd object
Set objAdoCommand = New ADODB.Command
Set objAdoCommand.ActiveConnection = m_oAdoConnection
objAdoCommand.ActiveConnection = m_oAdoConnection
objAdoCommand.CommandText = v_strStoredProcName
objAdoCommand.CommandType = adCmdStoredProc
Call objAdoCommand.Parameters.Refresh
'Stop
For lngCtr = 0 To UBound(r_varParamValues)
If objAdoCommand.Parameters(lngCtr + 1).Direction = adParamInput Then
objAdoCommand.Parameters(lngCtr + 1).Value = r_varParamValues(lngCtr)
End If
Next

Set objAdoRecordset = New ADODB.Recordset
objAdoRecordset.CursorLocation = adUseClient

Set objAdoRecordset = objAdoCommand.Execute
'Stop
For lngCtr = 0 To objAdoCommand.Parameters.Count - 1
If objAdoCommand.Parameters(lngCtr).Direction = adParamOutput Or objAdoCommand.Parameters(lngCtr).Direction = adParamInputOutput Then
r_varParamValues(lngCtr - 1) = objAdoCommand.Parameters(lngCtr).Value
End If
Next
Set RunProcedure = objAdoRecordset
RunCommand_Exit:
' Collect your garbage here
Exit Function
RunCommand_Error:
' Collect your garbage here
Call g_oGenErr.Throw("WeatherFcst.CDbsConn", "RunCommand")
End Function

PLEASE HELP.

Regards,

Sander

Grrrr...If I add the following line (before the DROP) to the sp it returns the value!

SET @.WeightedAverage = 9587558855

?! Que Pasa ?!

Retrieving url parameters

I have a 2 reports - Product Master and Product Details. I have the ProductID parameter being passed properly from the Master via the URL to the Details Report, but the Details report doesn't seem to be picking up the paramater from the URL and keeps prompting for the ProductID.

What settings should I be configuring in my Details report to say - "Get the parameter from the URL"?

I've tried multiple configs within the Report Parameters dialog box but I seem to be missing something...

?

In the first report, on the navigation tab for the textbox:

Choose the Report action, choose a report or enter a path to the report and then click the parameters button. Provide the values you want. you can use an expression here which is something like Parameters!ParameterName.Value which will pass to the drill-through report the same value as was passed to the original report.

Hope that helps,

-Lukasz

Retrieving Database Names via C#

Hello,
I am trying to develop a desktop application by using C#.Net. I am working with .NET Framewrok 2.0.
I need to list the database names which are taking place in Anlaysis Services.
Actually, my application will work on the OLAP Cube which is going to be chosen by the user.
In order to do this, I have to retrieve the Cube names so the user can choose what cube he/she wants to work with!
any help appritiated.

thanks in advance.

best regards

Tunc OVACIK

Check out Analysis Management Objects (AMO).

http://msdn2.microsoft.com/en-us/library/ms124924.aspx

|||Thanks for the link which is very usefull and has good informations about the whole programming stuff of OLAP technology but I guess those classes are for Analysis Manager 2005.
I am using Analysis Manager 2000 and those classes do not support AM 2000 as far as I understand. Because I have tried to implement the sample codes given in the link but
it did not work out.
Do you have any documents or any other side which is explaining how to get database names and such stuff from Analysis Manager 2000.

thanks for your time
best regards

Tunc OVACIK|||

In that case, check out Decision Support Objects (DSO)

http://msdn2.microsoft.com/en-us/library/aa902639(sql.80).aspx

http://msdn2.microsoft.com/en-us/library/ms133828.aspx

Retrieving an integer value from a database function via PHP

I am trying to retrieve an integer value that is returned from an MS SQL function that our DBA wrote and is saved in a table in the database. Using the ODBC_EXEC command I am able to successfully call and execute the function. All I am able to get back is the Resource id. This is the PHP code:

$sqlquery = "dbo.fn_gcc_total_applied '123456'";
$result = odbc_exec($sqlconnect, $sqlquery);
if (!$result) {
exit("Error in SQL 3");
}

Is there a way of capturing the actual integer value that is being returned by the function?

This is an actual function not a stored procedure. The actual MS SQL code is as follows:

declare @.ret_value int

set @.ret_value = dbo.fn_gcc_total_applied ('123456')
print 'Total Applied: ' + cast(@.ret_value as varchar(3))

Thank you in advance for any help you can offer.

Fred BernsteinIn my opinion this has more to do with the MS SQL function and the way the query is setup then with PHP. I therefore think you'll get better support when this thread is moved to the MS SQL forum. Good luck!

Ronald :cool:|||

Quote:

Originally Posted by ronverdonk

In my opinion this has more to do with the MS SQL function and the way the query is setup then with PHP. I therefore think you'll get better support when this thread is moved to the MS SQL forum. Good luck!

Ronald :cool:

Thank you very much for pointing me in the right direction.

Fred Bernstein

Retrieve SQL Server data via MS Query to spreadsheet

Hi,
Here is a security question: what way can I restrict the access to SQL
Server 2005 database objects ?
I'm going to let some my users use some tables, views to create their own
MSQuery queries to retrieve data from SQLServer, but not all ones! (I have
hundreds of tables, views, sprocs...)
What is the bast practices both from server side and Excel-side for this
kind of restriction ?
Thanks,
szabtiBest thing is to create a special login in sql server and grant the user to
have permission only to those tables.
These permission depends on what all privileges you want to grant them, if
its simple select , them give only db_datareader access
vt
"szabti" <tibor.szabo@.iconsulting.hu> wrote in message
news:uANKaC4FHHA.5004@.TK2MSFTNGP03.phx.gbl...
> Hi,
> Here is a security question: what way can I restrict the access to SQL
> Server 2005 database objects ?
> I'm going to let some my users use some tables, views to create their own
> MSQuery queries to retrieve data from SQLServer, but not all ones! (I have
> hundreds of tables, views, sprocs...)
> What is the bast practices both from server side and Excel-side for this
> kind of restriction ?
>
> Thanks,
> szabti
>|||szabti
Security is a huge subject especially in SQL Server 2005. I'd suggest to
spend a few days to study it .BOL is good place to start.
"szabti" <tibor.szabo@.iconsulting.hu> wrote in message
news:uANKaC4FHHA.5004@.TK2MSFTNGP03.phx.gbl...
> Hi,
> Here is a security question: what way can I restrict the access to SQL
> Server 2005 database objects ?
> I'm going to let some my users use some tables, views to create their own
> MSQuery queries to retrieve data from SQLServer, but not all ones! (I have
> hundreds of tables, views, sprocs...)
> What is the bast practices both from server side and Excel-side for this
> kind of restriction ?
>
> Thanks,
> szabti
>

Retrieve GUID or SID FROM Active Directory via ADSI and T-SQL only?

Hi all,

is there a way to get an object's SID or GUID using T-SQL only?

Up to now I got the following code of Active Directory Service Interfaces working,

but GUID or SID are not among the parameters known to me.

Code Snippet

EXEC sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'

SELECT * FROM OpenQuery(ADSI, 'SELECT title, displayName, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn FROM ''LDAP://DC=whatever,DC=domain,DC=org'' where objectClass = ''User''')

I do not want to use anything other then SQL Server 2000 to get an AD-object's primary key.

Any comments would be appreciated.

Thank you!

Regards,

caracol

Windows 2000 Server, SQL Server 2000, AD in W2K only mode

This is not an SSIS question. Moving to the Transact-SQL Forum.|||

Hi all,

just in case there should be someone else looking for identifiable data to be extracted from Active Directory:

A complete list with all attributes can be found at

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adschema/adschema/attributes_all.asp

For every attribute the LDAP-Display-Name is given which can be accessed by ADSI.

My SELECT from above should be

Code Snippet

SELECT * FROM OpenQuery(ADSI, 'SELECT objectGUID, title, displayName, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn FROM ''LDAP://DC=whatever,DC=domain,DC=org'' where objectClass = ''User''')

then.

Regards,

caracol