Multi Value Param Limit???

Feb 20, 2007

Is there a limit to how many items you can have in a multi value param list. I have 20 items in an activity type param and when I chose "Select All" and run the report, it doesn't return. I opened profiler and picked up the following statement sent to SQL:

exec sp_executesql N'SELECT de.employeenumber as Employee_Id,de.employeelastname + '', '' + de.employeefirstname + '', '' + case when de.employeemiddlename=''N/A'' then '''' else de.employeemiddlename end as Employee_Name,da.activitytype as Activity_Type,da.activitycode as Activity_Code,da.activityname as Activity_Name,dd.fulldate as Completion_Date,das.currentattemptstatus as Current_Attempt,das.successstatus as Success_Status,das.completionstatus as Completion_Status,das.registrationstatus as Registration_Status,fa.score as Score,fa.dimgradeid as GradeId

FROM dimemployee de inner join factattempt fa on (de.dimemployeeid = fa.dimemployeeid) inner join dimattemptstatus das on (fa.dimattemptstatusid = das.dimattemptstatusid) inner join dimactivity da on (fa.dimactivityid = da.dimactivityid) inner join dimdate dd on (fa.attemptenddateid = dd.dimdateid)

WHERE

de.employeenumber = (@EmployeeId) and da.activitytype in (N''CBT'',N''Course'',N''Dart'',N''Discuss'',N''Document'',N''Evaluator'',N''JPM'',N''Lesson Plan'',N''Module'',N''Observation'',N''Procedure'',N''PSG'',N''Qual'',N''Read'',N''Reference'',N''Session'',N''Sign-off'',N''Simulator'',N''Task'',N''Trainer'')

and das.isvalidattempt = ''Yes'' and ((@LastAttempt=1 and das.currentattemptstatus = ''Yes'') or (@LastAttempt=0 and das.currentattemptstatus in (''Yes'',''No''))) and das.lmsmartcompletionstatus in (@CompletionStatus) and (dd.fulldate >= @StartDt or @StartDt is NULL) and (dd.fulldate <= @EndDt or @EndDt is NULL)

ORDER BY de.employeefirstname + '' '' + de.employeelastname,da.activitytype,da.activityname,dd.fulldate',N'@EmployeeId int,@LastAttempt nvarchar(1),@CompletionStatus nvarchar(10),@StartDt nvarchar(4000),@EndDt nvarchar(4000)',@EmployeeId=108001,@LastAttempt=N'1',@CompletionStatus=N'Successful',@StartDt=NULL,@EndDt=NULL



If I take any one of the items in "da.activitytype in" out (for example....N''Trainer'')...the query is fine. But if I run it as is, it never returns. I have also reduced the number of items in the MVP to 19 and "Select All" and it runs fine...it just bombs when I have 20 and Select All. Any ideas?

View 1 Replies


ADVERTISEMENT

Populating And Passing Multi-value Param From A Url

Dec 1, 2005

I have a rs report with a parameter(named Site) that is defined as multi-value. What I am trying to do is call the report from an html form with the parameter populated with a comma seperated list of values. The html code is a minimally modified version of the code found in ch. 11 of the hitchhikers guide(code included below after the solid line). If I don't select any items, then execution is fine. 

View 6 Replies View Related

Multi-value Parameter In Master Report Passing To Single Param Sub-report In A List.

Aug 20, 2007

Here's tricky one.

I have a fairly complex report that was given to me that was hard coded for single parameters. There is a dropdown for each market (created from a query in SSRS). The users have to run for each market each week.

Is there a way to use this report as a Sub-report inside a list of a master report and then use a mult-value parameter?

I want this multi-value parameter to build the values for the list and then run the "sub-report" for each value.

Essentially, I want to create a for each loop.

Any ideas?

View 4 Replies View Related

How To Display Multiple Values On Report Page From A Multi-value Report Param

Nov 5, 2007

How do I display multiple parameter values on report page from a multi-value report parameter. For example, I have a report parameter where users can select multiple attendance codes and I want them displayed at the top of the report after it's run.

Currently, only the first value is showing on the report.

Thanks.

View 1 Replies View Related

Multi Value Parameter - Limit Number Of Selections

Jun 12, 2007

Hello



Anyone know if it is possible to limit the number of selections in a multi value parameter? Eg: There are 50 values in the drop down combo, but I want the user to be able to select a maximum of 10?



Cheers

View 1 Replies View Related

SQL 2012 :: Disaster Recovery Options For Multi-Database Multi-Instance Environment

Sep 23, 2014

Disaster Recovery Options based on the following criteria.

--Currently running SQL 2012 standard edition
--We have 18000 databases (same schema across databases)- majority of databases are less than 2gb-- across 64 instances approximately
--Recovery needs to happen within 1 hour (Not sure that this is realistic
-- We are building a new data center and building dr from the ground up.

What I have looked into is:

1. Transactional Replication: Too Much Data Not viable
2. AlwaysOn Availability Groups (Need enterprise) Again too many databases and would have to upgrade all instances
3. Log Shipping is a viable option and the only one I can come up with that would work right now. Might be a management nightmare but with this many databases probably all options with be a nightmare.

View 1 Replies View Related

SQL 2012 :: MSDTC In Multi-node / Multi-instanced Cluster

Aug 17, 2015

More often than not, I typically don't touch DTC on clusters anymore; however on a project where the vendor states that it's required. So a couple things here.

1) Do you really need DTC per instance or one for all?
2) Should DTC be in its own resource group or within the instance's group?
2a) If in it's own resource group, how do you tie an instance to an outside resource group? tmMappingSet right?

View 9 Replies View Related

The Multi Delete &&amp; Multi Update - Stored Procedure Not Work Ok

Feb 4, 2008

the stored procedure don't delete all the records
need help



Code Snippet
DECLARE @empid varchar(500)
set @empid ='55329429,58830803,309128726,55696314'
DELETE FROM [Table_1]
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0




TNX

View 2 Replies View Related

Help With Multi Join Or Multi Tier Select.

Jul 20, 2005

Hello,I am trying to construct a query across 5 tables but primarily 3tables. Plan, Provider, ProviderLocation are the three primary tablesthe other tables are lookup tables for values the other tables.PlanID is the primary in Plan andPlanProviderProviderLocationLookups---------------------------------------------PlanIDProviderIDProviderIDLookupTypePlanNamePlanIDProviderStatusLookupKeyRegionIDLastName...LookupValue....FirstName...Given a PlanID I want all the Providers with a ProviderStatus = 0I can get the query to work just fine if there are records but what Iwant is if there are no records then I at least want one record withthe Plan information. Here is a sample of the Query:SELECT pln.PlanName, pln.PlanID, l3.LookupValue as Region,p.ProviderID, p.SSNEIN, pl.DisplayLocationOnPCP,pl.NoDisplayDate, pl.ProviderStatus, pl.InvalidDate,l1.LookupValue as ReasonMain, l2.LookupValue as ReasonSub,pl.InvalidDataFROM Plans plnINNER JOIN Lookups l3 ON l3.LookupType = 'REGN'AND pln.RegionID = l3.Lookupkeyleft outer JOIN Provider p ON pln.PlanID = p.PlanIDleft outer JOIN ProviderLocation pl ON p.ProviderID = pl.ProviderIDleft outer JOIN Lookups l1 ON l1.LookupType = 'PLRM'AND pl.ReasonMain = l1.LookupKeyleft outer JOIN Lookups l2 ON l2.LookupType = 'PLX1'AND pl.ReasonSub = l2.LookupkeyWHERE pln.PlanID = '123456789' AND pl.ProviderStatus = 0ORDER BY p.PlanID, p.ProviderID, pl.SiteLocationNumI know the problew the ProviderStatus on the Where clause is keepingany records from being returned but I'm not good enough at this toanother select.Can anybody give me some suggestions?ThanksDavid

View 5 Replies View Related

USe DMX With @param

Jan 28, 2008

I would like to test following DMX, but it seems like we cannot use @param in DMX. If i indeed need what other tricks can avoid this constraint?

Declare @HCVS_MemberId nvarchar(15);

INSERT INTO test
(HCVS_MemberId, HCVS_MeasureDate, SysPressure, DiaPressure, Pluse)
OPENQUERY(Healthcare,
'SELECT TimeIndex, Quantity
FROM v_VitalSignForecast
WHERE HCVS_MemberId=@HCVS_MemberId AND HCVS_MeasureDate>=@From AND HCVS_MeasureDate<=@To')

Thanks,
Ricky.

View 3 Replies View Related

Db Passed As Param To Sp

Mar 12, 2008

is there a way in t-sql to pass a db name as a parameter, so that select * from [@passedDB].[dbo].[tableName] would work? Without dynamically building and executing the sql statement?

View 1 Replies View Related

@@RowCount Output Param

Sep 6, 2007

The following stored procedure sets a value for the @@RowCount global variable.
How do I make use of it in the Data Access Layer?
When I set the SPROC as the source for the object, the value numberRows does not appear to be an option. In the end I just want to set the value of @@RowCount to a Label.Text
What should I do?ALTER PROCEDURE dbo.ap_Select_ModelRequests_RequestDateTime
@selectDate datetime
,@selectCountry Int
AS

SELECT DISTINCT configname FROM ModelRequests JOIN
CC_host.dbo.usr_cmc As t2 ON
t2.user_id = ModelRequests.username JOIN
Countries ON
Countries.Country_Short = t2.country
WHERE RequestDateTime >= @selectDate and RequestDateTime < dateadd(dd,1, @selectDate)
AND configname <> '' AND interfacename LIKE '%DOWNLOAD%' AND result = 0 AND Country_ID = @selectCountry
ORDER BY configname
SELECT @@RowCount As numberRows

GO 

View 2 Replies View Related

How To Check If Value Is In Array Or Mv-param?

Dec 27, 2007

I'm working with a dataset something like this:

TypeID Sales($)
------ -------
1 123.45
1 47.98
2 9.21
3 87.23
3 99.88
4 123.43

And a multivalued parameter that lets the user select which TypeIDs specifically he wants to see:

ParamID ParamValue
1 Q1
2 Q2
3 Q3
4 Q4


And in my Report, I have data showing up something like this:

CountofAllSales: 6
SumOfAllSales: 491.18
CountofCustomSales: (count of sales with type specified in parameter)
SumOfCustomSales: (sum of sales with type specified in parameter)

The count and sum of custom sales should show -ONLY- the numbers from the TypeIDs selected in the multi-value parameter. But the CountAll and SumAll show everything, regardless. This is where I run into problems. I can't seem to find an "in" clause in the SSRS expressions. If the TypeID parameter was single value, I could write something like this

Expression for CountOfCustomSales:
=SUM(iif(Fields!TypeID.Value = Parameters!TypeID.Value, 1, 0))

However, since its multi-valued, that won't work. You'd have to write something like:
=SUM(iif(Fields!TypeID.Value = Parameters!TypeID.Value(0), 1, 0)) +
SUM(iif(Fields!TypeID.Value = Parameters!TypeID.Value(1), 1, 0)) +
....
SUM(iif(Fields!TypeID.Value = Parameters!TypeID.Value(length), 1, 0))

And obviously this doesn't work when you don't know exactly how many elements are going to be selected.

What would be ideal would be something like an "in" clause, but I can't find any such functionality or think how to write my own function:
=SUM(iif(Fields!TypeID.Value in
Parameters!TypeID.Values, 1, 0))

Short of modifying the StoredProc itself (and for me, that means red tape. :( :( ) can anyone think of a way to count/sum only the values specified in an MVP??

View 1 Replies View Related

Getting Back Set Order From The IN Param

Nov 5, 2006

Hi AllMy query is as follows:SELECT STRINGTEXT, TOKENIDFROM WEBSTRINGSWHERE TOKENID IN (6,20,234,19,32,4,800,177)All I want is my resultset to come back in the order that I have defined inthe IN clause, but unfortunately SQL is trying to be too helpful and sortsthe numbers in the IN clause so that the resultset comes back with a TOKENIDorder of 4,6,19,20,32,177,234,800.I don't want this bloody order I want 6,20,234,19,32,4,800,177!!Sorry for my rant, but its got my hot under the collar.Is there anyway round this?ThanksYobbo

View 3 Replies View Related

StrdPrcdr Param Get All Values

Apr 21, 2008

Hello....
I am trying for several weeks to figure out how can I create a stored procedure with parameters that returns all rows from a column (even then Nulls) when the parameter value is not set (or is '%', or anything tha might be. In a few words, in case the user hasn't input any data).

I 've created a WHERE clause that goes like this: WHERE fieldName LIKE @param + N'%' OR IS NULL
Well this query returns all rows in case the user hasn't input data but if the user inputs data it returns the correct rows but it is also returns null fields.

Thanks a lot in advance!
Manolo....

View 6 Replies View Related

Report Param Question

May 16, 2008



Is it possible to take a report input parameter and display it at the top of the report? StartDate/EndDate are two input params for my report that would be nice to display at the top.

thanks

(Reporting Services 2005)

View 6 Replies View Related

Multi-database Multi-server

Mar 27, 2007

I am new to Reporting Services and hope that what I am looking to do is within capabilities :-)



I have many identical schema databases residing on a number of data servers. These support individual clients accessing them via a web interface. What I need to be able to do is run reports across all of the databases. So the layout is:



Dataserver A

Database A1

Database A2

Database A3



Dataserver B

Database B1

Database B2



Dataserver C

Database C1

Database C2

Database C3



I would like to run a report that pulls table data from A1, A2, A3, B1, B2, C1, C2, C3



Now the actual number of servers is 7 and the number of databases is close to 1000. All servers are running SQL2005.



Is this something that Reporting Services is able to handle or do I need to look at some other solution?



Thanks,



Michael

View 5 Replies View Related

DateTime Param For SP Causing BIG Headache...!!

Aug 8, 2006

I've got a stored procedure and one of the parameters is a DateTime.  But no matter what I do to the string that's passed into the form for that field, it doesn't like the format.  Here's my code: SqlConnection conn = new SqlConnection(KPFData.getConnectionString());
SqlCommand cmd = new SqlCommand("KPFSearchName", conn);
cmd.CommandType = CommandType.StoredProcedure;

SqlParameter param = cmd.Parameters.Add("@DOB", SqlDbType.SmallDateTime);
param.Direction = dir;
param.Value = txtDOB.Text;

// also have tried this:

param.Value = Convert.ToDateTime(txtDOB.Text);

// and

param.Value = Convert.ToDateTime(txtDOB.Text).ToShortDateString;

No matter what I do I always get a formatting error - either I can't convert the string to a DateTime, or the SqlParameter is in the incorrect format, or something along those lines.  I've spent a couple hours on this and hoping someone can point out my obvious mistake here...??Thanks for your help!!eddie

View 5 Replies View Related

Doing A SELECT X FROM Y WHERE Z IN (@param) In Stored Procedure?

Jan 21, 2005

Hello,

I have a table with a foreign key field. I need to retrieve all the records where the foreign key matches any of a set. In plain ol' SQL this is accomplished with the IN(a,b,c) statement but I can't get that to work in a stored procedure.

How would I do this? I can imagine that I could parse the input string and create a temporary table and use that to do a join but that seems rather convoluted.

Any tips highly appreciated! If I'm not being clear I'll gladly post more details.

Thanks,

Noc

PS SQL 2000, ASP.NET 1.1, VS 2003.

View 2 Replies View Related

Uniqueidentifier Param Inside A SQL String

Apr 7, 2006

I have a uniqueidentifier probleme when i want to execute my sql string. The "PortalID" is my uniqueidentifier passed on my StoredProc parameter. But i cant make it work. It said that i cant add a nvarchar with a uniqueidentifier.!!
what is the best way to do it!!  i try to convert(nvarchar(40), @PortalID) but its not workingdeclare @sql nvarchar(4000)
set @sql = 'select
t.[PortalID],
t.[City],
t.[Name],
t.[Code],
ts.*
from [Hockey_Team_Statistics] ts inner join [Hockey_Teams] t on (t.[TeamID] = ts.[TeamID])
where ts.[SeasonYear] = '+CONVERT(nvarchar(10),@SeasonYear)+' and ts.[LeagueMode] = '+CONVERT(nvarchar(1),@LeagueMode)
+'t.[PortalID] = '+@PortalID+'

order by '+ @SortExpression +''

exec sp_executesql @sql 

View 3 Replies View Related

Output Param With A Stored Procedure

Aug 21, 2001

Hello everyone,

I am working through a tutorial and have stumbled into something that does not quite make sense to me. I was wondering if someone could help me understand this.

I have created this SP, this all makes sense to me due to the assignment of the artistname column value to the @artistname variable. In other words what is on the right of the equal sign is assigned to what is on the left.

create procedure ShowPopStyle
@style varchar(30),
@artistname varchar(30) output
as
select @artistname = artistname
from artists
where style = @style
go

Now when you execute this SP, what does not makes sense to me is if I need to declare a variable to hold the output, which I presume is null, shouldn't the @returnname be on the left side of the equal sign instead of the right?

declare @returnname varchar(30) -- variable for the output from the procedure
exec showpopstyle 'Pop', @artistname = @returnname output
print @returnname

Thanks
Kevin

View 2 Replies View Related

Error Trying To Insert Into Table Using XML Param.

May 9, 2008

Hi all,

I also have the same error, I am trying to do two things in my Stored Proc.

1) - Insert a parent record.
2) - Insert 1 or many records in the child table using the parent ID

The child records are being passed as a XML param.

I am also getting the
'Subqueries are not allowed in this context. Only scalar expressions are allowed.'
error when I try to create the procedure.


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:pryder
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE TestProc
-- Add the parameters for the stored procedure here
@Name String ,
@bings XML
AS
BEGIN transaction
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @NewID as int
declare @err as int
-- Insert statements for procedure here
INSERT INTO PARENT
(Name)
VALUES (@Name)
SELECT @err = @@error
if @err <> 0
begin
rollback transaction
return @err
end


SELECT SCOPE_IDENTITY = @NewID


INSERT INTO Child
(ParentID,
name)

Values

((
SELECT @NewID, ParamValues.ID.value('.','VARCHAR(MAX)')
FROM @bings.nodes('bings/group') as ParamValues(ID)
))


SELECT @err = @@error
if @err <> 0 begin rollback transaction return @err end


commit transaction
return @@error
GO

View 7 Replies View Related

Like Pattern Matching - Problem With Param

Mar 19, 2008

I've decalred this stored procedure with a simple where and like statement. The problem is I don't see any result.
Here is my code


create procedure sp_Select_ProfileNames
@NameSearch varchar(50)
as
select ProfileFirstName +' '+ ProfileLastName as ProfileName
from Profiles
where ProfileLastName like '%@NameSearch%';


When i change this line:
where ProfileLastName like '%@NameSearch%';
to
where ProfileLastName like '%Bil%';

I see names starting with Bil, but when i enter Bil as param, I don't get anything.

What's wrong here ?

View 2 Replies View Related

VB.NET Stored Procedure, Can't Pass Param

Jun 18, 2007

Hello,



I have a VB.NET stored procedure as below:




Code Snippet

Partial Public Class StoredProcedures

Public Shared Sub My_UpdateCountsManaged( ByRef paramInOut As Integer)

'here I perform update statement using "paramInOut" passed form calling code

.......

'then I return value to the calling code

paramInOut = 555

End Sub

End Class



Calling code specifies a parameter like this:






Code Snippet

Dim param as Sqlparameter = New SqlParameter("@paramInOut", SqlDbType.Int)

param.Direction = ParameterDirection.InputOutput

param.Value = 999

cmd.Parameters.Add(param)





When I execute the code, it surely gets back "555" from SP, the problem is that SP never gets "999" from calling code despite ParamDirection is InputOutput. It always receives 0. I am afraid I don't understand something fundamental ?

Any help would be appreciated.

Thanks a lot,Fly.

View 4 Replies View Related

INSERT INTO WITH VALUE RETURNED BY EXECUTE SP(@PARAM)

Oct 27, 2006

Dear friends,

How can insert into a temp table 2 parameters. If it's only on, I dont have problem, but if I have 2 there is a error... The problem is the sintax of the execute SP ... thanks

ALTER PROCEDURE [dbo].[GD_SP_FACTURAS_TOTAL2]

AS

CREATE TABLE #TotaisDir

(

DirTotal bigint,

DirNome nvarchar(10)

)

DECLARE @DIR nvarchar(10)

DECLARE @Return_Status bigint

DECLARE LINHAS_CURSOR CURSOR FOR SELECT DIR_NOME FROM Direccao

OPEN LINHAS_CURSOR

FETCH NEXT FROM LINHAS_CURSOR INTO @DIR

WHILE @@FETCH_STATUS=0

BEGIN

INSERT INTO #TotaisDir (DirNome,DirTotal) VALUES

('ww', EXECUTE dbo.GD_SP_FACTURA_ValorTotal @DIR)

FETCH NEXT FROM LINHAS_CURSOR INTO @DIR

END

CLOSE LINHAS_CURSOR

DEALLOCATE LINHAS_CURSOR

SELECT DirTotal FROM #TotaisDir

SELECT SUM(DirTotal) As SOMATOTAL FROM #TotaisDir



ERROR:

Msg 156, Level 15, State 1, Procedure GD_SP_FACTURAS_TOTAL2, Line 21

Incorrect syntax near the keyword 'EXECUTE'.

Msg 102, Level 15, State 1, Procedure GD_SP_FACTURAS_TOTAL2, Line 21

Incorrect syntax near ')'.

View 3 Replies View Related

Why Is Param Query Effective Against SQL Injection?

Mar 12, 2008

Reading about SQL Injection attacks I came across this example:

SSN="172-32-9999';DROP DATABASE pubs --"
SqlQuery = "SELECT au_lname, au_fname FROM authors WHERE au_id = '" + SSN + "'"

One remedy given was a parameterized query as follows:

Dim cmd As new SqlCommand("SELECT au_lname, au_fname FROM authors WHERE au_id = @au_id")
Dim param = new SqlParameter("au_id", SqlDbType.VarChar)
param.Value = SSN
cmd.Parameters.Add(param)

Why does this parameter which is defined as a varchar solve the problem? It's defined as a varchar, basically a string. Why is the result different in the solution? How is the query string in the second sample different from the one in the first? A simple question I know, but I've been wondering.

Thanks
Mike Thomas

View 7 Replies View Related

SQL Agent Job Execute SSIS With Param

May 1, 2008



Hi,

My package takes 1 global variable and I have set up jobs to run this SSIS with different variable values.

I select the package using the UI provided via the job set up screen. The job step type is Operating system (CmdExec).

When package are run by job, it seems that the job can not find the package. But, how can it not find the package when the package was selected from a list that the UI displayed? The error is below.

"Executed as user: <<LOGGEDIN USER(with admin right)>>. The process could not be created for step 1 of job 0xFECED6C09CC650489084E91C2FCF52FB (reason: The system cannot find the file specified). The step failed."


Thanks a lot in advance.

View 1 Replies View Related

Setting A Bit Value To (@param='C') Ie True Or False

Nov 19, 2007

I want to pass a single char to a query and use that to set two flags.

(

@ID int,

@AssessedID int,

@CompetencyID int,

@Status char,

@Creator int

)



AS

UPDATE P4_Assessment

SET P4_Cancelled_f = (@Status = ('C')),

P4_Competent_f = (@Status = ('P')),

P4_Date = getdate(),

P4_Creator = @Creator

WHERE P4_ID = @ID


I want to set the P4_Cancelled_f to true (if @Status = 'C') or false if it doesn't.
This sort of syntax is fine in C#, but fails in a query. I also tried using IN (@Status IN ('C'))


Is this sort of logic possible in TSQL or should I use two parameters and set them in my code as 1 or 0?

TIA

View 3 Replies View Related

Passing Param - SSIS Package ??

Sep 21, 2007

Hi,


I'm exporting some data from a table to fixed-width file using SSIS.
The query inside the package is:


SELECT col1, col3, col6 FROM Table1
WHERE code = 5 AND status = 'S'


The 'code' value will be passed from the front-end.

How can I make the query dynamically take the 'code' value and export to a file?

If a user selects 'code' as 7 , then the query should change to


SELECT col1, col3, col6 FROM Table1
WHERE code = 7 AND status = 'S'


and with this it has to export to a file.


Is this possible? If so, any suggestions on how to do it? !!!


Thanks,
Siva.

View 9 Replies View Related

Return BIGINT OUTPUT Param To VB!?

May 9, 2006

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 OUTPUTAS......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 = 1GROUP 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.RecordsetDim objAdoRecordset As ADODB.RecordsetDim objAdoCommand As ADODB.CommandDim 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 = objAdoRecordsetRunCommand_Exit: ' Collect your garbage here Exit FunctionRunCommand_Error: ' Collect your garbage here Call g_oGenErr.Throw("WeatherFcst.CDbsConn", "RunCommand")End Function


PLEASE HELP.

Regards,

Sander

View 1 Replies View Related

Different Plans With Literal String Vs. Param

May 9, 2008

The proc below does two queries that are functionally identical. The only difference is that one LIKE 'foo%', and the other uses LIKE @searchText, where @searchText = 'foo%'.

But the first does an index seek, and the second does an index scan -- and it makes a big difference in performance. (Timing stats are below.)

How can I make the second query seek instead of scanning?


CREATE PROCEDURE test_like

@searchText nvarchar(64)

AS


-- Straight literal string search

SELECT companyId

FROM companies

WHERE searchbrand LIKE 'foo%'




-- With param

SELECT companyId

FROM companies

WHERE searchbrand LIKE @searchText

OPTION(OPTIMIZE FOR(@searchText = 'foo%'))

GO


EXEC test_like 'foo%'




-- Query 1:

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.


-- Query 2:
SQL Server Execution Times:

CPU time = 47 ms, elapsed time = 40 ms.

View 4 Replies View Related

Consuming Stored Procedure Output Param

Sep 14, 2007

This is my SProc:
CREATE PROCEDURE dbo.ap_Select_ModelRequests_RequestDateTime
/* Input or Output Parameters *//* Note that if you declare a parameter for OUTPUT, it can still be used to accept values. *//* as is this procedure will very well expect a value for @numberRows */@selectDate datetime ,@selectCountry int ,@numberRows int OUTPUT
AS
SELECT DISTINCT configname FROM ModelRequests JOIN CC_host.dbo.usr_smc As t2 ON t2.user_id = ModelRequests.username JOIN Countries ON Countries.Country_Short = t2.country WHERE RequestDateTime >= @selectDate and RequestDateTime < dateadd(dd,1, @selectDate) AND configname <> '' AND interfacename LIKE '%DOWNLOAD%' AND result = 0 AND Country_ID = @selectCountry ORDER BY configname
/* @@ROWCOUNT returns the number of rows that are affected by the last statement. *//* Return a scalar value of the number of rows using an output parameter. */SELECT @numberRows = @@RowCount
GO
And This is my code. I know there will be 100's of records that are selected in the SProc, but when trying to use the Output Parameter on my label it still says -1Protected Sub BtnGetModels_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim dateEntered As String = TxtDate.TextDim selectCountry As String = CountryList.SelectedValue
Dim con As New SqlClient.SqlConnection
con.ConnectionString = "Data Source=10.10;Initial Catalog=xx;Persist Security Info=True;User ID=xx;Password=xx"Dim myCommand As New SqlClient.SqlCommand
myCommand.CommandText = "ap_Select_ModelRequests_RequestDateTime"
myCommand.CommandType = CommandType.StoredProceduremyCommand.Parameters.AddWithValue("@selectDate", dateEntered)
myCommand.Parameters.AddWithValue("@selectCountry", CInt(selectCountry))Dim myParam As New SqlParameter("@numberRows", SqlDbType.Int)
myParam.Direction = ParameterDirection.Output
myCommand.Parameters.Add(myParam)
myCommand.Connection = con
con.Open()Dim reader As SqlDataReader = myCommand.ExecuteReader()Dim rowCount As Integer = reader.RecordsAffected
numberParts.Text = rowCount.ToString
con.Close()
End Sub
 
What should I fix?

View 5 Replies View Related

How To Pass NULL To SQLdatasource Stored Param

Sep 23, 2007

I'm developing a web app using VS2005.  I have a webpage with panel containing a gridview populated by a SQLdatasource.  The SQLdatasource in turn is populated by a stored procedure that can take up to 5 parameters.  The user types in up to 5 separate words (searchterms) in a text box which are then parsed and passed to the stored proc in the datasource which performs a fulltext search.  The gridview then becomes visible.  My problem is that unless the user types in 5 searchterms (no less), the gridview returns zero rows.  5 searchterms returns the proper results.  Somehow, I need to be able to pass in null or empty values for unneeded parameters.
I've tested the stored procedure in Query Analyzer and from within the SQLdatasource configuration (using Test Query) using 0 up to 5 parameters and it works fine, so that's not my problem.  Here's the code that runs after the user types in their search term(s) and presses a button:Public Sub FTSearch_Command(ByVal sender As Object, ByVal e As CommandEventArgs) Handles btnFullText.Command
Dim x As Integer
pnlFullText.Visible = Falsefiltertext = Replace(txtSearchTxt.Text, "'", "''")
If Not filtertext Is Nothing Then
filtertext = filtertext.Trim
Else
Return
End IfDim arrayString() As String = filtertext.Split(" ")
Dim length As Integer = arrayString.LengthFor x = 0 To (length - 1)
If Not arrayString(x) Is Nothing ThenSelect Case x
Case 0 : lblFTParm1.Text = arrayString(0)Case 1 : lblFTParm2.Text = arrayString(1)
Case 2 : lblFTParm3.Text = arrayString(2)Case 3 : lblFTParm4.Text = arrayString(3)
Case 4 : lblFTParm5.Text = arrayString(4)
End Select
End If
Next
pnlFullText.Visible = "True"
End Sub
Any ideas? 
Thanks in advance.
 

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved