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


ADVERTISEMENT

Passing Value To Param Of Datetime Datatype While Debugging

Apr 1, 2003

While debugging a stored proc in Sql Query Analyser i have to pass value to stored proc param which is of datatype datetime
i passed it as '03/31/2003'
it's giving error "[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification"
if i make it null it's working fine
pls suggest which date format value supposed to supply for datetime param to run debugging successfully

View 7 Replies View Related

Can Anyone Help Me With This? It's Giving Me A Bad Headache!

Mar 9, 2005

Hi All,

I have a table called Prizes. Here's how it looks in design view with some value placed inside for Illustration purposes.

PrizeID 1, 2, 3, 4, 5
PromotionID 1, 1, 1, 2, 1
PrizeName 10 Cash, 5 cash, 10 cash, 15 cash, 20 cash

My challenge is that I need to write a stored procedure for example, that will find the PrizeID associated with the 4th count of the PromotionID that equals 1 . So in this example, counting to the 4th PromotionID that equalls 1 give us a PrizeID of 5.

I hope I've made myself clear! Can anyone write out a mini SP on how to do this.

Many many thanks in advance,
Brad

View 2 Replies View Related

Syntax Headache!!!

Mar 13, 1999

I need to get this statement to run.

Declare @MiD datetime

-- The ATime is a datetime field in the table
-- @OrderBy is an input Parameter also, as is @MiD

SELECT @Statement = 'SELECT tblAudit.* FROM tblAudit WHERE ATime >= ' + @MiD + ' ORDER BY ' + @OrderBy
EXECUTE(@Statement)

@Statement then contains:

SELECT tblAudit.* FROM tblAudit WHERE ATime >= Mar 10 1999 ORDER BY Access_ID

I need the quotes around the Mar 10 1999.

Thanks for the help

View 2 Replies View Related

SQL Query Headache

Jun 24, 2004

Hallo

I am interrogating the structure of SQL Server database looking for the occurence of a particular phrase in the object names/definitions. This is in preparation for decommission/replacement...

When I run the script below in SQL Query Analyzer it returns about 200 results, but when I run the same query in my Access VBA code it returns much less!

SQL Script:
SELECT
so.ID,
so.name,
so.type,
CHARINDEX('insita',so.name) AS PosInObjName,
CASE WHEN CHARINDEX('insita',so.name) > 0 THEN 1 ELSE 0 END AS FoundInObjName,
CHARINDEX('insita',sc.text) AS PosInObjDef,
CASE WHEN CHARINDEX('insita',sc.text) > 0 THEN 1 ELSE 0 END AS FoundInObjDef
FROM
sysobjects so INNER JOIN syscomments sc on so.id = sc.id
ORDER BY [name]

VB Code:
Sub InterrogateDatabaseStructure(sServer As String, sDB As String, sSearch4 As String)
On Error GoTo err_IDS
Dim oCnn As ADODB.Connection
Dim oCmd As ADODB.Command
Dim oRs As ADODB.Recordset
Dim sSQL As String

Set oCnn = New ADODB.Connection
oCnn.ConnectionString = "driver={SQL Server};server=" & sServer & ";uid=;pwd=;database=" & sDB & ";dsn=;"
oCnn.Open

CurrentDb.Execute "DELETE * FROM PhraseSearchResults"
CurrentDb.Execute "DELETE * FROM PhraseInObjName"

Set oCmd = New Command
oCmd.ActiveConnection = oCnn
oCmd.CommandType = adCmdText
sSQL = "SELECT so.ID, so.name, so.type, "
sSQL = sSQL & "CASE WHEN CHARINDEX('" & sSearch4 & "',so.name) > 0 THEN 1 ELSE 0 END AS FoundInObjName,"
sSQL = sSQL & "CHARINDEX('" & sSearch4 & "',so.name) AS PosInObjName, "
sSQL = sSQL & "CASE WHEN CHARINDEX('" & sSearch4 & "',sc.text) > 0 THEN 1 ELSE 0 END AS FoundInObjDef, "
sSQL = sSQL & "CHARINDEX('" & sSearch4 & "',sc.text) AS PosInObjDef "
sSQL = sSQL & "FROM " & sDB & ".dbo.sysobjects so INNER JOIN syscomments sc on so.id = sc.id"

oCmd.CommandText = sSQL
Set oRs = oCmd.Execute()

With oRs
If Not .EOF And Not .BOF Then
.MoveFirst
Do Until .EOF
sSQL = "INSERT INTO PhraseSearchResults VALUES('"
sSQL = sSQL & .Fields(0) & "','" & .Fields(1) & "','" & .Fields(2) & "',"
sSQL = sSQL & .Fields(3) & "," & .Fields(4) & ")"
CurrentDb.Execute sSQL
.MoveNext
Loop
End If
End With


Set oCmd = Nothing
Set oCnn = Nothing

Exit Sub

err_IDS:
MsgBox "Error: " & Err.Description & vbCr & "Error Code: " & Err.Number
Screen.MousePointer = 0
End Sub

Can anyone please advise what I am doing wrong? Thanks.

View 1 Replies View Related

Date Headache

Dec 5, 2005

Guys
I have a table 1 row, a start and end date of a period

create table xperiod(startdate datetime , enddate datetime)
insert xperiod (startdate , enddate)
values ('2004-04-01 00:00:00.000' , 2012-03-31 00:00:00.000)

I'm trying to retrieve a batch of 'smaller' periods from this where the relevant period is a number (of months) passed as a parameter (only ever 1, 3 or 6)


for example, if the parameter is 1 I will obtain the following rows each being a 1 month period starting at the xperiod.startdate value up to an end date of the xperiod.enddate value
startperiod endperiod
'2004-04-01 00:00:00.000' '2004-04-30 00:00:00.000'
'2004-05-01 00:00:00.000' '2004-05-31 00:00:00.000'
'2004-05-01 00:00:00.000' '2004-05-31 00:00:00.000'

and so on to
'2012-03-01 00:00:00.000' '2012-03-31 00:00:00.000'


if the parameter is 3 I will obtain the following rows each being a 3 month period starting at the xperiod.startdate value up to an end date of the xperiod.enddate value


startperiod endperiod
'2004-04-01 00:00:00.000' '2004-06-30 00:00:00.000'
'2004-07-01 00:00:00.000' '2004-09-30 00:00:00.000'
'2004-10-01 00:00:00.000' '2004-12-31 00:00:00.000'

and so on to
'2012-01-01 00:00:00.000' '2012-03-31 00:00:00.000'

Hope this makes sense !

I think I'll be ok on the logic for the while loop but my main problem is getting the endperiod value based on the startperiodvalue
Thx in advance

View 2 Replies View Related

'MSDASQL' Headache

Aug 10, 2006

Hello - hope this is in the right group:We have just started with linked servers and have successfully createda view on SQL Server linked to a Progress database. I can query thisview happily in Query Analyzer.I have created an ASP.NET application to display this view in adatagrid but I get the following error:System.Data.SqlClient.SqlException: OLE DB provider 'MSDASQL' reportedan error. at System.Data.SqlClient.SqlConnection.OnError(SqlExc eptionexception, Boolean breakConnection) atSystem.Data.SqlClient.SqlInternalConnection.OnErro r(SqlExceptionexception, Boolean breakConnection) atSystem.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObjectstateObj) at System.Data.SqlClient.TdsParser.Run(RunBehaviorrunBehavior, SqlCommand cmdHandler, SqlDataReader dataStream,BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)at System.Data.SqlClient.SqlDataReader.ConsumeMetaDat a() atSystem.Data.SqlClient.SqlDataReader.get_MetaData() atSystem.Data.SqlClient.SqlCommand.FinishExecuteRead er(SqlDataReader ds,RunBehavior runBehavior, String resetOptionsString) atSystem.Data.SqlClient.SqlCommand.RunExecuteReaderT ds(CommandBehaviorcmdBehavior, RunBehavior runBehavior, Boolean returnStream, Booleanasync) atSystem.Data.SqlClient.SqlCommand.RunExecuteReader( CommandBehaviorcmdBehavior, RunBehavior runBehavior, Boolean returnStream, Stringmethod, DbAsyncResult result) atSystem.Data.SqlClient.SqlCommand.RunExecuteReader( CommandBehaviorcmdBehavior, RunBehavior runBehavior, Boolean returnStream, Stringmethod) atSystem.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehaviorbehavior, String method) atSystem.Data.SqlClient.SqlCommand.ExecuteDbDataRead er(CommandBehaviorbehavior) atSystem.Data.Common.DbCommand.System.Data.IDbComman d.ExecuteReader(CommandBehaviorbehavior) at System.Data.Common.DbDataAdapter.FillInternal(Data Setdataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords,String srcTable, IDbCommand command, CommandBehavior behavior) atSystem.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32startRecord, Int32 maxRecords, String srcTable, IDbCommand command,CommandBehavior behavior) atSystem.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at...The code in my ASP.NET application looks fine and works on non-linkedviews on the same server. Do I need to add a command or change asetting on SQL Server?

View 2 Replies View Related

Beta Headache

May 3, 2006

Hi Folks,

I'm still using the the SQL Server 2005 June CTP to develop. I'm working for a client that intends to upgrade, but has not done so yet. We believed that we had 365 days from the date of installation before it would expire (late July). But Visual Studio 2005 Beta 2 just expired, which is used by SQL Server Business Intelligence Development Studio, so now we can no longer develop SSIS packages or Reporting Services reports.

What can I do?

I thought that maybe installing Visual Studio 2005 express might work, but when I went there, it seems that it is split up into modules (e.g. Visual Basic 2005 express, Visual C++ express, etc...). I don't know if just one of these would be sufficient and if so, which one. Even if it would, it says I'll need to completely remove the SQL Server 2005 beta, as well as the Visual Studio 2005 beta, and I think the .net framework.

My contract is up at the end of June, and it was understood that the client was going to take care of the upgrade. Is there a quick fix here? What can I do with minimal effort? (I don't really have time to wait for the client to get the upgrade approved and done. I know, sniff, sniff! lol.)

View 1 Replies View Related

Object Ownerships Headache

Dec 10, 2005

This is more of an SQL server question than ASP.net.
A stored proc named MyStoredProc is created by admin account (sa) thus the owner of that proce is dbo.
When I log in to the database (Query Analyzer) as another login say User1, I can not use that stored proc since it was owned by sa. I get an error 'Invalid Object Name' or Object does not exist.
If I use the proper name qualifier like dbo.MyStoredProc, It works in the Query analyzser.
The problem I am facing is - My stored procedures, Views, Tables  were created by sa on the development machine. Now its time for deployment. On the deployment machine, I am not the sa. I just belong to the db_Owner role. I have to log in as User1 and not as sa.  In my c# code I have called the stored procedure as MyStoredProc. I get an error 'MyStoredProc does not exist' since I am loging in as User1 and not sa. In order to use the code, I have to change the ownership to this (and all other objects) to User1.
Is that the only way out or I am doing something wrong/missing someting? Your coments/opinions/suggestions welcome.
 

View 1 Replies View Related

Help Constructing A Headache Query...

Sep 28, 2005

I have two tables, both with phone numbers and call times.

one is for incoming calls, one for outgoing calls.

I need to find all phone numbers from the incoming calls table where the number of calls exceeds 100 within the last 30 days, where the last call was within the last 15 mins, and where the number does Not exist in the outgoing call table within the last 30 days.

so far I have this...
(call record is the incoming, callout is the outgoin)

I believe this is giving me all records in the call record table that are within the last month, and not in the outgoing call table OR have not ben called within the last month..

SELECT cr.cli,min(cr.starttime)as "first call",max(cr.starttime)as "last call",count(cr.cli) as "number of calls"
FROM callrecord cr
LEFT JOIN callout co
ON cr.cli = co.cli
where (co.cli is null or datediff(dy,co.calltime,getdate())>30 )
and datediff(dy,cr.starttime,getdate())>30
group by cr.cli
order by cr.cli


i need to add in the 15 minute call check, and also only return those with a count of > 100

can anyone assist? i'm getting a headache :D

tia

a

View 4 Replies View Related

Group By, Creating A Headache

Oct 12, 2006

I am using SQL server 2000.
While using query analyzer I am facing problem.
If a query has group by clause and if that query is not fetching any record (i.e. query is returning nothing), then in this situation, I want zero to be displayed where datatype of field is integer and "-" if datatype of field is varchar.

Please give me solution as soon as possible, a kind request.

Facing problem for the below mentioned query:-

select IsD.ItemCode,
case
when sum(IsD.IssuedQty) is null then 0
else sum(IsD.IssuedQty)
end as IssuedToday
from Inv_IssueMaster IsM, Inv_IssueDetail IsD
where IsM.IssueNo=IsD.IssueNo
group by IsD.ItemCode

In the above mentioned query, datatype of IssuedQty is int and for ItemCode it's varchar.

View 14 Replies View Related

Snapshot Replication Headache

Jan 4, 2006

Hello,
Harry Half wit here!!
I know that snapshot replication is the simplest form of syncing with SQL server and I can't even figure that out today!!.
I keep getting myself confused as to who should be configured to be a publisher, distibutor or subscriber etc etc.
My scenario is simple:
1 server creating a daily snapshot of a table and then 1 remote laptop (msde) pulls the snapshot into it's own database.
Heres what i did so far;
I configured the server to be a publisher and distributor (is that right?) and didn't set up any subscribers because i want to do that from my remote.
From the remote I did nothing but go into EM tools"create new pull subscription" but I cannot see the publication on the server.

SHould I set my remote to be a distributor to do this?

any help very much appreciated!!

View 1 Replies View Related

Reporting Express Headache

Oct 12, 2006

hi all,

i am having a hell of a time trying to understand how im supposed to use the reporting services integrated with sql server 2005 express - advanced. i have searched high and low throughout vb 2005 express for the relevant controls, and i have searched the management studio trying to find the relevant information. there is no reporting server anywhere that i can see. i simply installed the sqlexpr_adv.exe and assumed that all the relevant features would be automatically installed. i cant find them anywhere (also something about a business intelligence thing? also missing). i would be extremely grateful if somebody could help me

regards

adam

View 2 Replies View Related

Subquery Headache With Count And GroupBy

Nov 23, 2005

I'm trying to return an integer from the following table that returnsthe number of unique cities:tblEmployeesName CityJohn BostonFrank New YorkJim OmahaBetty New YorkThe answer should be 3.DECLARE @EmployeeCities intSELECT @EmployeeCities = SELECT ... ???How go I return one row/column into @EmployeeCities from a Count and aGroupBy?Headache already... Maybe it's too early...

View 3 Replies View Related

Linked Server Headache (Access)

Aug 13, 2007

There seems to be a lot of confusion around the groups about linkingto an Access mdb with the SQL Server Jet OLE DB provider and I haventbeen able to find a straight forward solution. Basically, I have anAccess MDB (A2K) on one server and a SQL Server DB (2005 std ed.) onanother - Both on the same network. I'm trying to create a linkedserver object in the SQL server to view data in the mdb. I've set itup and it works - but only from the server machine itself. If you tryto connect the the linked server from any other computer on thenetwork you get the usual access denied / file in use error.The fact that I can use the linked server from the server box itselfbut not from another pc on the network makes me think that it may be apermissions problem. But I am logging in with full Admin rights andstill no joy. Also there is no workgroup security on the mdb, so thatsnot the problem. I've used the surface editor to remove anyrestrictions that may cause problems, OLE DB connect, OPENROWSET etc.but still no joy.I've tried mapping the mdb's location on the server so I could use astandard filepath, rather than a //Server-Name/... path. Again, worksfrom the server, but not from any client PCs, so no joy there either.In frustration, I copied the mdb to the same server and viola - fullaccess to the linked server from anywhere. But this is no good, I needthe mdb file to stay where it is. An mdb full of linked tables wontwork... they don't show up in the linked server.So that's it - out of ideas! Am I just going to have to accept thatlinked server objects are limited just to mdb files on the same servermachine, or is there something I'm missing??? Firewalls, servicelogins, server settings.... something one of you gurus out there knowabout that might be the key to making my headache go away!!!Any input gratefully recieved!!!

View 1 Replies View Related

Index Tuning Wizard - Headache

Jul 20, 2005

Hi,I am having problems getting anything useful out of the index tuningwizard.I have created a table and inserted data into it. When i run the indextuning wizard i expect 2 indexes to be recommended so the book says(Index011 with a key on the uniqueid column and a non clustered indexnamed table02 with a key on the col03 and LongCol02)Instead i get nothing being recommended.What am i doing wrong????Please helpMaryamif exists (select name from dbo.sysobjects where name ='table01' andtype ='u')drop table table01create table table01(uniqueid int identity, longcol02 char(300)DEFAULT 'THIS IS THE DEfault column',col03 char(1))godeclare @counter intset @counter =1while @counter<=1000begininsert table01 (col03) values('a')insert table01 (col03) values('b')insert table01 (col03) values('c')insert table01 (col03) values('d')insert table01 (col03) values('e')insert table01 (col03) values('f')set @counter=@counter+1end

View 3 Replies View Related

VERY Large Binary Import/export Headache

Oct 13, 2006

Hi,

I am currently importing (and exporting) binary flat files to and from Db fields using the TEXTPTR and UPDATETEXT (or READTEXT for export) functions. This allows me to fetch/send the data in manageable packet sizes without the need to load complete files into RAM first.

Given that some files can be up to 1Gb in size I am keen to find out a new way of doing this since the announcement that TEXTPTR, READTEXT and UPDATETEXT are going to be removed from T-SQL.

I had a quick foray into SSIS but couldn't find anything suitable which brings me back to T-SQL. If anyone knows a nice elegant way of doing this and is prepared to share, that would be grand.

Thanks for your time,
Paul

View 9 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

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 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

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

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

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







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