Adding Parameter To Items In Where Command

Nov 6, 2013

I have 3 specific where searches in where command I want to add a defined text to each specific where query if that makes sense. highlighted in red, how do I do this? going to add status after and then do pivot table afterwards.

SELECT
POHEAD.DocumentNo
,POHEAD.CreditorCode
,POHEAD.InternalReference
,POHEAD.QuantityOrdered
,POHEAD.Z_ArrivalDate

[Code] .....

View 1 Replies


ADVERTISEMENT

Reporting Services :: Adding Dataset Parameter Doesn't Add Report Parameter In Visual Studio 2008 SSRS

Apr 22, 2015

I'll go to a dataset, open up the query designer, add a new parameter, then refresh the fields, but the parameter won't be added as a report parameter.  If I go to the dataset properties under the list of parameters, the value in the dropdown will be blank.  However, sometimes this will automatically add. 

Is this a bug in Visual Studio?  How do I get around this? 

View 3 Replies View Related

Can Pass List Of Items Through A Parameter?

Feb 12, 2014

I have a SQL query like this:

Select * from myTable where myTable_ID in (2,6,7,9)

I want to build the list of values that are in parenthesis, in my VB code and pass it in through a parameter, so it's like this:

Select * from myTable where myTable_ID in (@myValues)

Is this possible? I tried it where myValues = '2,6,7,9' but am getting a conversion error. I'm starting to believe it's not possible to do what I'm trying to do. Is there another way?

View 2 Replies View Related

Make Items Bold In Parameter Selection List

Mar 19, 2007

Is it possible to make certain items in a parameter selection list appear bold?

View 3 Replies View Related

Adding Parameters To An Sql Command

Mar 5, 2008



Hi All,
I have an sql command which has subqueries in it. I need to pass in variables like startdate and enddate to the sub query query and then execute the query. What is the best way to do this?
I am trying to use ole db source but this has a limitation of not allowing parameters at the sub query level. What else can I use? I am working with sql server 2005.
Thanks a lot.

View 6 Replies View Related

SQL 2012 :: SSIS Data Flow Items Tab Missing For Adding Data Source / Destination

Apr 3, 2014

I need to see inside a SSIS 2012 project a new SSIS installed component, but in the SSDT 2010 I cannot see the SSIS Data Flow Items tab for adding data source/data destination respect to the choose toolbox items pane.

View 4 Replies View Related

Adding The Column By Using The Alter Command.

Jun 1, 2008

ALTER TABLE customers
add column active_flg int(1)

but i got error.

quote:Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'column'.

what should i do?

ps. thanks for warning... i won't edit the post after i got the answer anymore.

View 6 Replies View Related

SQL Server 2008 :: Command For Adding New Drives?

Aug 26, 2015

Currently we're running everything on C: but I've convinced management to split out, what's the command to add drives?

View 9 Replies View Related

SQL 2012 :: Pass List Items To Stored Proc As Comma Separated Parameter - Foreach Loop

Feb 11, 2015

I have a multiselect checkbox list in my UI. I pass the list items to my stored proc as comma separated parameter and then I have a function which converts this parameter to a table with separate rows.

E.g. : a,b,c,d

Converted to result table

result
a
b
c
d

I want to insert each row of the result table into another table. How to do that.

E.g., the table after the function is :

CREATE TABLE #result
(
Subject varchar(100)
)

insert into #result values ('a')
insert into #result values ('b')
insert into #result values ('c')
insert into #result values ('d')

So the pseudo code is something like

for each row in #result

insert row into another table

View 9 Replies View Related

Summing Invoice Items - The Multi-part Identifier Items.TAX Could Not Be Bound

Apr 17, 2007

Hi: I'm try to create a stored procedure where I sum the amounts in an invoice and then store that summed amount in the Invoice record.  My attempts at this have been me with the error "The multi-part identifier "items.TAX" could not be bound"Any help at correcting my procedure would be greatly appreciate. Regards,Roger Swetnam  ALTER PROCEDURE [dbo].[UpdateInvoiceSummary]    @Invoice_ID intAS    DECLARE @Amount intBEGIN    SELECT     Invoice_ID, SUM(Rate * Quantity) AS Amount, SUM(PST) AS TAX    FROM         InvoiceItems AS items    GROUP BY Invoice_ID    HAVING      (Invoice_ID = @Invoice_ID)    Update Invoices SET Amount = items.Amount    WHERE Invoice_ID =@Invoice_IDEND

View 3 Replies View Related

SQL Server 2012 :: Identify Sets That Have Same Items (where Set ID And Items In Same Table)

Feb 25, 2015

I am struggling to come up with a set-based solution for this problem (i.e. that doesn't involve loops/cursors) ..A table contains items (identified by an ItemCode) and the set they belong to (identified by a SetId). Here is some sample data:

SetIdItemCode
1A
1B
24
28
26
310
312
410

[code]....

You can see that there are some sets that have the same members:

- 1 and 10
- 2 and 11
- 7, 8 & 9

What I want to do is identify the sets that have the same members, by giving them the same ID in another column called UniqueSetId.

View 8 Replies View Related

Adding Sql Parameter In VB

Oct 16, 2006

I have a details view with several parameters set up in my asp.net 2.0 code, I want to add a parameter before the sql parameter is executed. I need to use the find control of the details view because I am using items/edit item templates in my details view control. I tried this(see below) as well as the detailsview item command event args to no avail. It doesn't see the other parameters that have already been declared in my asp.net code. I don't want to have to declare all my varibles that are already in my asp.net code. I just want to add another parameter.  Sub InsertNew(ByVal sender As Object, ByVal e As DetailsViewInsertEventArgs) _    Handles dvEvents.ItemInserting        Dim dvr As DetailsViewRow        For Each dvr In dvEvents.Rows  Dim CatIDup As Integer = CType(dvr.FindControl("ddlCat"), DropDownList).SelectedValue            sdsevents.InsertParameters.Add("evCatID", CatIDup)sdsevents.Insert()

View 4 Replies View Related

Adding Numeric Data To A Dataflow In A Scriptcomponent-&&> Invalid Number In OLE Db Command Transformation

Jun 9, 2006

Hi there,

This seems a bug to me. Or does anyone has a logical explanation that escapes me?

When in SSIS Designer Version 9.00.1399.00 I add output columns (numeric 4,0 ) to a scriptcomponent and fill them with valid numeric data in thescript I get a database error 'invalid number' when I use these columns in an OLE db Command-transformation . This errormessage disappears when I replaces those columns by a dataconversion to the datatype they originally have.

Derived Column Name Derived Column Expression

STATUS_DEF Replace 'STATUS_DEF' (DT_NUMERIC,4,0)STATUS_DEF

Maybethis info is usefull for somebody else who can't figure out wathever he's doing wrong.



Paul Baudouin









View 1 Replies View Related

Reporting Services :: Group And Sum Items / Sub-items Into One Record

Apr 10, 2015

I'm having an issue creating a report that can group & sum similar items together (I know in some ways, the requirement doesn't make sense, but it's what the client wants).

I have a table of items (i.e. products).  In some cases, items can be components of another item (called "Kits").  In this scenario, we consider the kit itself, the "parent item" and the components within the kit are called "child items".  In our Items table, we have a field called "Parent_Item_Id".  Records for Child Items contain the Item Id of the parent.  So a sample of my database would be the following:

ItemId | Parent_Item_Id | Name | QuantityAvailable
----------------------------------------
1 | NULL | Kit A | 10
2 | 1 | Item 1 | 2
3 | 1 | Item 2 | 3
4 | NULL | Kit B | 4
5 | 4 | Item 3 | 21
6 | NULL | Item 4 | 100

Item's 2 & 3 are child items of "Kit A", Item 5 is a child item of "Kit B" and Item 6 is just a stand alone item.

So, in my report, the client wants to see the SUM of both the kit & its components in a single line, grouped by the parent item.  So an example of the report would be the following:

Name | Available Qty
--------------------------
Kit A | 15
Kit B | 25
Item 4 | 100

How I can setup my report to group properly?

View 6 Replies View Related

Adding Global Parameter

Jan 31, 2008



Is there any possibility to add global parameters to the reports? I'd like to add my parameter that would be avilable on the all reports on the reports server and use it in actions.

Thank you!

View 1 Replies View Related

Adding Parameter To SQL-sentence?

Feb 5, 2008

Hi,

I want to add a parameter to the following SQL-sequence. ActivityGroup is the table and I want to select the current activityGroupID in place of XXXXX. Do I need to put this in a stored procedure?


select * from ActivityGroup where cpgActivityGroupID = XXXXX and cgpRegistredFrom > getDate() and cgpRegistredTo < getDate()";

Thanks in advance!

View 5 Replies View Related

Adding Non Obligatory Parameter To SP

Apr 7, 2008



Hello,

How do I add a non obligatory parameter to a stored procedure? Suppose I want the parameter @mbrTelephoneJob to be non obligatory?


ALTER PROCEDURE [dbo].[insert_member]

(

@mbrFirstName nvarchar(30),

@mbrLastName nvarchar(30),

@mbrStreetAddress nvarchar(30),

@mbrPostalAddress nvarchar(30),

@mbrTelephoneHome nvarchar(30),

@mbrTelephoneJob nvarchar(30),

@mbrEmail nvarchar(40),



)

AS

INSERT INTO dbo.member(mbrFirstName, mbrLastName, mbrStreetAddress, mbrPostalAddress, mbrTelephoneHome, mbrTelephoneJob, mbrEmail)

VALUES (@mbrFirstName, @mbrLastName, @mbrStreetAddress, @mbrPostalAddress, @mbrTelephoneHome, @mbrTelephoneJob, @mbrEmail)

View 4 Replies View Related

Adding A New Parameter To A Subreport

Jun 4, 2007

Each time we add a new parameter to a subreport and we link the main report's fields to be the value's to the parameters for the subreport, we get an error message stating that the parameter for the subreport has not been specified. If we physically REBOOT the computer and then reload the project, the error seems to clear up.



Is this a bug in Reporting Services or is there some other explaination.



Thanks for the information.

View 1 Replies View Related

Adding A Parameter Value In A Textbox

Feb 22, 2008



hello

i have a parameter which can have multiple values

and in the report body, on the top i want to add a textbox which has the report parameter value (or values)

it works when i have parameter with a unique value but for a multiple values parameter it doesn't work

i wrote that

and it doesn't work



Code Snippet

=Parameters!ActivityClient.Value




thanks in advance for help

View 6 Replies View Related

Getting OUTPUT Parameter In An OLE DB Command

Jul 12, 2007

Is it possible to use stored procedure with output parameter and retrieves the values of that output parameter, in an OLE DB Command within a Data Flow?

What I wanted to do is to get the newly created identity of a row so that I can insert it to the main data set in data flow. I'm not even sure if there is even a much better design to achieve this. I've rummaged the internet but everything I got were all about Execute SQL Task.

View 5 Replies View Related

C# Command Timestamp Input Parameter

Mar 27, 2007

I have a stored proc that inserts a customer and it expects a timestamp input parameter. I dont know what a timestamp datatype is for sql 2005 and Ive tried to parse all sorts of data types but the proc errors out saying it needs "Byte[]" which Ive tried. Can anyone help me with this? ThanksRyan 

View 2 Replies View Related

How To Pass Parameter Into Source SQL Command?

Nov 15, 2007

Hi all,
I want to pass a parameter into my OLE DB source. For eg, I want to set the condition
SELECT * FROM "tablename" where ID = parameter  <-----------------
I do not know how to do this? can it be done by using package variable?

View 2 Replies View Related

SQL 2012 :: Using OVER Command Together With (WITH) And Excel With A Parameter?

Dec 29, 2014

I'm am using an Excel with a parameter, in this case a customer-number. For retrieving the data I use a view. In this view I start with a WITH to retrieve the data because I need the customer number in several places in the view. In the beginning all data I need is a 1:1 relation with the customer but problems occurs when there is a 1:n relation, in my case a customer can have more contact persons. When I try to collect the contact person information it always finds the first contact person in the table and that's from a different customer.

What do I have to do to find the contact person(s) that belongs to my specific customer/?

with Customer as (
Select * from [ods].[custtable] where ODSACTIVE = '1' AND DATAAREAID = 'NL01'
)
union all
select src.accountnum, 'Contact Person 1 Name' as 'Field', SRC.NAME as 'Value'
FROM (SELECT CP.NAME, CT.accountnum, ROW_NUMBER() OVER (ORDER BY CP.PARTYID) AS RN, ct.partyid
from [ods].[contactperson] cp inner join CUSTOMER CT on ct.partyid = cp.ORGpartyid and ct.DATAAREAID = cp.DATAAREAID
WHERE cp.DATAAREAID = 'NL01'
) AS SRC WHERE SRC.RN = 1

View 2 Replies View Related

Passing A Parameter To A USE Command Within A Cursor

May 27, 2008

Hi everyone I am trying to pass a parameter to a USE command within the body of a cursor. I keep getting an error. Here is my code.




Code Snippet
DECLARE @SQLText AS NVARCHAR(4000)
Declare @SQLText2 as NVARCHAR (2000)


DECLARE Cursor_Trial CURSOR FOR SELECT name FROM sys.sysdatabases order by name
Declare @name as varchar(255)
Open Cursor_Trial
FETCH NEXT FROM Cursor_Trial INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
Use @name --------This is where I'm getting the error


Set @SQLText2 = @name
select @SQLText2, * from sys.database_principals
where type_desc='DATABASE_ROLE'
and name = 'xxxxx'

FETCH NEXT FROM Cursor_Trial INTO @name

END

CLOSE Cursor_Trial
DEALLOCATE Cursor_Trial


As you can see I am trying to pass the next database name into the body of the cursor so that the query will pull the role information only for the current database in the loop. Any thoughts or tips? Is there a better way to accomplish what I'm trying to do here?


View 1 Replies View Related

Integration Services :: Using A Parameter In Command

Nov 3, 2015

I have a Data Flow task Within a for loop Container.. the for loop container receives the result of sql query, iterates over the result set and passes each variable to the OLE DB Source.. Now based on the parameter passed I need to execute a query.. say for example if the parameter passed is P1 execute SQL S1 , P2 then S2 & so on.. How do I use the Parameter passed in SQL Query?

View 12 Replies View Related

Parameter Cannot Set The Command Text For Dataset

May 14, 2007

Hello



I get the following error in a very quick test system i created:



An error occurred during local report processing.

An error has occurred during report processing.

Cannot set the command text for data set 'test'.

Error during processing of the CommandText expression of dataset 'test'.



My sql is:

= "select T.testid, T.test from test T " & Iif(Parameters!test1.Value = 1, "", "Where T.testid = " & Parameters!test1.Value)



There is nothing obviously wrong in the code as far as i can see



The parameter 'test1' is of type 'string'



The database 'test' has 2 columns of type 'smallint' and 'Name:nvarchar(50)'



I am at a loss, as this query is really simple, and is similar to the example query set up my microsoft which works fine



Thanks

View 5 Replies View Related

Optional Command Line Parameter

Jan 17, 2006





Hello,



I want to use an optional parameter at the command line for my package.

I planned to make the first task a script which simply checks the variable (which is a string), and if it is empty, go one way, and if it is not, go another way. Is the best to go?



Many thnaks in advane

View 4 Replies View Related

Procedure Expects Parameter Error When Adding To DB

May 15, 2007

Hi,I'm trying to create a page where a user can search the database according to some criteria and get back the result in the form of a GridView. Also, the user has the option of saving the criteria to another table in the database by assigning it a name so that it can be retrieved easily in the future.I have the search and display part working, however, saving the criteria to the database is giving problems for some reason.Given below is my stored procedure to add the info to the db.  SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[AddToReport]
(@ReportName varchar(100), @ProjID varchar(300), @DeptCode varchar(20), @ProjType varchar(20), @ProjectTitle varchar(300),
@ProjectManagerID int, @DateRequested datetime, @DueDate datetime, @ProjectStatusID int)

AS
SET NOCOUNT ON
DECLARE @Dept varchar(50)
DECLARE @err int

BEGIN TRANSACTION

IF @ReportName IS NULL
BEGIN
RETURN -1
END
ELSE
BEGIN
IF @DeptCode IS NOT NULL
BEGIN
SET @Dept = REPLACE(CONVERT(char,@DeptCode),'.','')
END
SET @err = @@ERROR


INSERT INTO dbo.tbl_Report (ReportName, ProjID, DeptCode, ProjType, ProjectTitle, ProjectManagerID, DateRequested, DueDate, ProjectStatusID)
VALUES (@ReportName, @ProjID, @Dept, @ProjType, @ProjectTitle, @ProjectManagerID, @DateRequested, @DueDate, @ProjectStatusID);
IF @err<>0
BEGIN
ROLLBACK TRANSACTION
RETURN @err
END
END
COMMIT TRANSACTION Given below is the relevant codebehind.  This is how the values are initialized:   Dim newManager As New ListItem
newManager.Text = "Choose a Manager"
newManager.Value = 0
projectManagerDDL.Items.Add(newManager)

Dim newDept As New ListItem
newDept.Text = "Choose a Department"
newDept.Value = ""
deptCodeDDL.Items.Add(newDept)

Dim newID As New ListItem
newID.Text = "Choose a Project"
newID.Value = ""
projIDDDL.Items.Add(newID)

Dim newStatus As New ListItem
newStatus.Text = "Choose a Status"
newStatus.Value = 0
projectStatusDDL.Items.Add(newStatus)

Dim newDateRequestedMonth As New ListItem
newDateRequestedMonth.Text = "Month"
newDateRequestedMonth.Value = 0
dateRequestedMonthDDL.Items.Add(newDateRequestedMonth)

Dim newDateRequestedDay As New ListItem
newDateRequestedDay.Text = "Day"
newDateRequestedDay.Value = 0
dateRequestedDayDDL.Items.Add(newDateRequestedDay)

Dim newDateRequestedYear As New ListItem
newDateRequestedYear.Text = "Year"
newDateRequestedYear.Value = 0
dateRequestedYearDDL.Items.Add(newDateRequestedYear)

Dim newDueDateMonth As New ListItem
newDueDateMonth.Text = "Month"
newDueDateMonth.Value = 0
dueDateMonthDDL.Items.Add(newDueDateMonth)

Dim newDueDateDay As New ListItem
newDueDateDay.Text = "Day"
newDueDateDay.Value = 0
dueDateDayDDL.Items.Add(newDueDateDay)

Dim newDueDateYear As New ListItem
newDueDateYear.Text = "Year"
newDueDateYear.Value = 0
dueDateYearDDL.Items.Add(newDueDateYear) This is the submit code:  Protected Sub saveButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim connString As String
Dim con As SqlConnection


Try
connString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("ConnectionString1").ConnectionString

con = New SqlConnection(connString)

Dim cmd As SqlCommand = New SqlCommand()
cmd.Connection = con

If (([String].IsNullOrEmpty(reportNameTextBox.Text) = False) Or reportNameTextBox.Text <> "Enter Report Name") Then
cmd.Parameters.Add("ReportName", SqlDbType.VarChar, 300).Value = reportNameTextBox.Text
End If

If ([String].IsNullOrEmpty(projIDDDL.SelectedItem.Value)) = False Then
cmd.Parameters.Add("ProjID", SqlDbType.VarChar, 30).Value = projIDDDL.SelectedItem.Value
End If

If ([String].IsNullOrEmpty(deptCodeDDL.SelectedItem.Value)) = False Then
cmd.Parameters.Add("DeptCode", SqlDbType.VarChar, 20).Value = deptCodeDDL.SelectedItem.Value
End If

If (typeRBL.SelectedItem.Value <> "All") Then
cmd.Parameters.Add("ProjType", SqlDbType.VarChar, 20).Value = typeRBL.SelectedItem.Value
End If

If ([String].IsNullOrEmpty(projectTitleTextBox.Text)) = False Then
cmd.Parameters.Add("ProjectTitle", SqlDbType.VarChar, 300).Value = projectTitleTextBox.Text
End If

If CInt(projectManagerDDL.SelectedItem.Value) <> 0 Then
cmd.Parameters.Add("ProjectManagerID", SqlDbType.Int).Value = CInt(projectManagerDDL.SelectedItem.Value)
End If

If (dateRequestedDayDDL.SelectedItem.Value = 0 Or dateRequestedMonthDDL.SelectedItem.Value = 0 Or dateRequestedYearDDL.SelectedItem.Value = 0) Then
Dim dateRequested As New DateTime
dateRequested = Nothing
Else
Dim dateRequested As New DateTime(dateRequestedYearDDL.SelectedValue, dateRequestedMonthDDL.SelectedValue, dateRequestedDayDDL.SelectedValue)
If (dateRequested) <> Nothing Then
cmd.Parameters.Add("DateRequested", SqlDbType.DateTime).Value = dateRequested
End If
End If


If (dueDateDayDDL.SelectedItem.Value = 0 Or dueDateMonthDDL.SelectedItem.Value = 0 Or dueDateYearDDL.SelectedItem.Value = 0) Then
Dim dueDate As New DateTime
dueDate = Nothing
Else
Dim dueDate As New DateTime(dueDateYearDDL.SelectedValue, dueDateMonthDDL.SelectedValue, dueDateDayDDL.SelectedValue)
If (dueDate) <> Nothing Then
cmd.Parameters.Add("DueDate", SqlDbType.DateTime).Value = dueDate
End If
End If

If (projectStatusDDL.SelectedItem.Value) <> 0 Then
cmd.Parameters.Add("ProjectStatusID", SqlDbType.Int).Value = CInt(projectStatusDDL.SelectedItem.Value)
End If

cmd.CommandText = "dbo.AddToReport"
cmd.CommandType = CommandType.StoredProcedure

Try
con.Open()
cmd.ExecuteNonQuery()
Response.Write("Report Saved")
Catch ex As Exception
Response.Write(ex)
Finally
con.Close()
con.Dispose()

End Try



Catch ex As ApplicationException
Response.Write("Could not load the database")
End Try

End Sub   The only absolute requirement when saving to the table is the ReportName. All the other criteria can be NULL. If I don't select and values and try to save the values, I get an error:System.Data.SqlClient.SqlException: Procedure or function 'AddToReport' expects
parameter '@ProjID', which was not supplied. at
System.Data.SqlClient.SqlConnection.OnError...  etc If I choose the ProjID (thus giving it a value), I get the following error:System.Data.SqlClient.SqlException: Procedure or function 'AddToReport' expects
parameter '@DeptCode', which was not supplied. at
System.Data.SqlClient.SqlConnection.OnError... etc  and so forth. I'm guessing it's a problem of NULLs somewhere, but I'm not sure. Thanks. 

View 6 Replies View Related

Dataset Becomes Empty After Adding Query Parameter

Mar 13, 2008



Hi all,

hope anyone can help me on this one.

I have a parameterized report based on a MDX query (so no drag and drop of filters and fields).
Now i have to ad a parameter. So i open the query parameter dialog box and i ad my parameter.
when i click ok , my dataset becomes empty , resulting in an error on my report saying that the fields on the report cannot by found and should be in the scope of the dataset.

Has anyone experienced this problem before, or am i forgetting something here?

thanks in advance

Steven J

View 1 Replies View Related

Using Adodb Command - Output Parameter Problem- HELP!

Jul 20, 2005

We use a number of similar databases and frequently create a newdatabase using a backup restore of another similar database. We try tokeep changes between databases in _Additional tables - like AccountAdditional, Sale_Additional so most tables stay the same. The latestrestored database (I'll call it DBaseA) is behaving differently in VB6code and I need help trying to make it work.I have been using use an ADODB.Command to execute a stored procedureusing adCmdStoredProc and Parameters.Refresh to get an output parametervalue which is an integer. Here is a sample of the VB:Public Function UnlockAccount(Account_ID As String) As BooleanDim LCmd As ADODB.CommandOn Error GoTo ERROR_UnlockAccountUnlockAccount = TrueIf Account_ID <> "" ThenIf DBConnect() ThenSet LCmd = New ADODB.CommandLCmd.ActiveConnection = CN(0)LCmd.CommandTimeout = 0LCmd.CommandType = adCmdStoredProcLCmd.CommandText = "Unlock_Account"LCmd.Parameters.RefreshLCmd.Parameters("@Account_ID").VALUE = Account_IDLCmd.Execute , , adExecuteNoRecordsIf LCmd.Parameters("@Status") <> 0 ThenUnlockAccount = FalseEnd IfElsemsgbox "UnlockAccount: DBConnect Failed", "UnlockAccount"End IfEnd IfExit FunctionERROR_UnlockAccount:UnlockAccount = Falsemsgbox "UnlockAccount: App Error: " & Err & " " & _Err.Description, "UnlockAccount"DBConnect TrueExit FunctionResume'for debuggingEnd Functionand this is one of the sps that fails - can't be more simple!Create PROCEDURE Unlock_Account( @Account_ID UNIQUEIDENTIFIER,@Status INTEGER =null OUTPUT) AS/* Strip functionality */SET @Status = 0goThis code is still working in at least 4 other databases. It fails indatabase DBaseA with the error:Invalid character for cast conversionWe are talking about the exact same table definition and the exact samestored procedure just in different databases.I fumbled around on the Internet and found a mention in a PowerBuilderweb site of additional parameters in the connect string which seems tofix the problem in SOME of the stored procs - but not all.The added parameters are:DelimitIdentifier='No';MsgTerse='Yes';CallEscape='No';FormatArgsAsExp='N'They are not documented in MS but are in Sybase?? No idea why, but someof the stored proc functions work when I add this to the connect string.The complete connect string is:Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DBaseA;Data Source=PHASE2-S500,1433;Network Library=DBMSSOCN;DelimitIdentifier='No';MsgTerse='Yes';CallEscape='No';FormatArgsAsExp='N'The databases are on different servers - but they are running the sameversion of SQL2000, the same version of Windows. I see no differencesin options set in the different servers or between databases that workand this one. And dbcc checkdb runs clean.The compiled code that fails, fails across the board - not just on my(developer) PC. The same source code works on the same tables in otherdatabases, including the one we copied....The final kicker - if I build a SQL statement string and get therecordset instead, it works like a charm. But I have to fix about 20 VBfunctions and check that I do have a recordset and SET NOCOUNT ON in all20 sps.I feel this is either something so obvious I will kick myself or soserious Microsoft will be digging into it.Any ideas anyone??Sandie*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

OLEDB Command Parameter Type Is Different In Different Situations

Dec 3, 2007



Hi All,

I am using OLEDB Command transformation in a data flow to update the table. Find the columns for the table EMP as follows.


EmpID - int
EmpName - varchar(40)
EmpSal - float
Status - varchar(20)

I am using the following command to update the table.


Update EMP set status = 'Disabled' where EmpID = ? and EmpSal = ?


when I use the above condition the type of the second parameter is taking as "double precision float" as the incoming input column type is "double - precision float". This is fine.


But when I use the following condition the type of the param is taking as different one.

Update EMP set status = 'Disabled' where EmpID = ? and ( EmpSal + ? ) = 0


It is taking as "four byte signed integer" even though the incoming input column type is "double precision float" thus I am loosing the precision and getting error when the limit exeeds. If I use 0.0 then it is taking as "Numeric" type.


If I use convert function like (EmpSal + ? ) = Convert(float, 0) then it is taking as "double precision float".
Update EMP set status = 'Disabled' where EmpID = ? and ( EmpSal + ? ) = Convert(float, 0)


My question is how it behaves in the above situation. Can any body clarify please?

Venkat.

View 1 Replies View Related

Listbox Contents To Sql Stored Procedure Command Parameter?

Feb 11, 2004

Hello,

Stuck in a spot and hoping someone will nudge me in the right direction....

I'm trying to write to a sql db via a storedprocedure using a parameter. i'm pretty certain the below statement is causing the problem. but i'm not sure how to properly refer to it....


Dim connString As String
connString = "integrated security=false;user id=sa;server=HCENT1;database=LicenseRenewal;persist security info=False"
Dim myConnection As New SqlConnection(connString)
Dim myCommand As New SqlCommand("InsertPage1", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
' .......other (working) parameter statements.........
Dim parameterDates As New SqlParameter("@Dates", SqlDbType.VarChar, 4000)
parameterDates.Value = Session("lstDates")
myCommand.Parameters.Add(parameterDates)

myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()


the session("lstDates") is the contents of lstDates.Items (listbox) from a previous page.
i'm guessing its not valid to refer to it as a varchar, can someone point me to the proper way to handle this?

tia
andy

View 4 Replies View Related

Sql Command Oracle Oledb Connection - Parameter Issue

Jul 5, 2006

I need to extract rows using date as parameter... where source contains millions of rows but few thousands per date.

I tried using SQL command in data access mode in OLE DB Source Editor but having problem with passing parameter...

Anyone has solution?

View 10 Replies View Related







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