Parameter Store Procedure Not Working

Aug 9, 2006

I have this select statement:

SELECT * FROM [enews] WHERE ([name_nws] LIKE '%" + strSearch + "%' OR
[title_nws] LIKE '%" + strSearch + "%'OR [sub_nws] LIKE '%" + strSearch
+ "%' OR [sum_nws] LIKE '%" + strSearch + "%' OR [content_nws] LIKE '%"
+ strSearch + "%')



This statement works fine but when I turned it into a store procedure,
it returns nothing. I created the store procedure as follows:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:        <Author,,Name>

-- Create date: <Create Date,,>

-- Description:    <Description,,>

-- =============================================

CREATE PROCEDURE sqlSearch

    -- Add the parameters for the stored procedure here

    @strSearch varchar(250)

AS

BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;



    -- Insert statements for procedure here

    WHERE ([name_nws] LIKE '%" + @strSearch + "%' OR
[title_nws] LIKE '%" + @strSearch + "%'OR [sub_nws] LIKE '%" +
@strSearch + "%' OR [sum_nws] LIKE '%" + @strSearch + "%' OR
[content_nws] LIKE '%" + @strSearch + "%')

END

GO



Help is appreciated.

View 1 Replies


ADVERTISEMENT

SQL Store Procedure Problem Like Operator Not Working

Nov 16, 2001

Dear Friends,

I Creat a store procedure and i used the like operator but i cant get the correct result can any one help me...

My Store Procedure Code
---------------------------------

Creat Proc [myrecods]
@name nvarchar[200]
AS
Select * from Customers
Where name LIKE '%@name%'

and i execute this proc through this line

exec myrecords 'ina'

but cant received corret result any help.

Thanks
Inayat.

View 3 Replies View Related

How Do I Call A Store Procedure With Parameter?

Aug 9, 2006

I have searched this forum for how to call a store procedure with
parameter and so far no result has answered my question yet. I have the
following store procedure:


SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:        <Author,,Name>

-- Create date: <Create Date,,>

-- Description:    <Description,,>

-- =============================================

CREATE PROCEDURE sqlSearch

    -- Add the parameters for the stored procedure here

    @strSearch varchar(250)

AS

BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;



    -- Insert statements for procedure here

    WHERE ([name_nws] LIKE '%" + @strSearch + "%' OR
[title_nws] LIKE '%" + @strSearch + "%'OR [sub_nws] LIKE '%" +
@strSearch + "%' OR [sum_nws] LIKE '%" + @strSearch + "%' OR
[content_nws] LIKE '%" + @strSearch + "%')

END

GO

I got this far in my code on how to call the parameter store procedure.
string strConn = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString; SqlConnection sqlConn = new SqlConnection(strConn); SqlCommand sqlComm = new SqlCommand(); sqlComm.Connection = sqlConn; sqlComm.CommandType = CommandType.StoredProcedure; sqlComm.CommandText = "sqlSearch"; sqlComm.Parameters.Add("@strSearch", SqlDbType.VarChar);  I don't know what do next.

Help is much appreciated.

View 4 Replies View Related

Input Parameter To Store Procedure

Jan 26, 2004

Hi,
I am using a ListBox where a user can choose multiple lines.
The index of the selected items are then used in a stored procedure.

I wan´t to use the ID´s in this statement:
SELECT * FROM MyTable WHERE MyID IN (1,2,4,9)

But how can I do this?
If I pass them as a string, then I can´t use them as above.
Can I separate the string '1,2,4,9' so I can use them in the statement above?
Or can I send the values as a array to the stored procedure?


Regards!
Jonas

View 5 Replies View Related

Wont Execute Store Procedure With Parameter

Oct 4, 2007

Hi
Am trying execute a store procedure with a date parameter than simply get back ever record after this todays date. It wont except the value i give. I can just do it in the store procedure as it will passed other values later on.
It works fine if I take the parameter out, of both store procedure and code. It must be a syntax thing but im stuck sorry --- the error i get is: Incorrect syntax near 'GetAppointmentSessions'.
here is my code: ' build calendar for appointment sessions
Dim Today As Date = Date.Now
Dim ConnStr As String = WebConfigurationManager.ConnectionStrings("ConnString").ConnectionString
Dim Conn As New SqlConnection(ConnStr)
Conn.Open()

Dim cmd As New SqlCommand("GetAppointmentSessions", Conn)
cmd.Parameters.Add("InputDate", SqlDbType.DateTime).Value = CType(Today, DateTime)
Dim adapter As New SqlDataAdapter(cmd)



Dim dt As New DataTable

adapter.Fill(dt)

Dim row As DataRow
 Here is the SQL:ALTER procedure [dbo].[GetAppointmentSessions]

@InputDate Datetime
AS


SELECT TOP (5) uidAppointmentSession, dtmDate,
(SELECT strRoomName
FROM tblRooms
WHERE (uidRoom = tblAppointmentSessions.fkRoom)) AS Room,
(SELECT strName
FROM tblHMResources
WHERE (uidHMResources = tblAppointmentSessions.fkHMResource)) AS Clinician,
dtmStartBusinessHours, dtmEndBusinessHours
FROM tblAppointmentSessions

Where
dtmDate > @InputDate

Order By dtmDate  

View 3 Replies View Related

Passing Column Name As Parameter To Sql Store Procedure

Dec 21, 2007

i am using asp.net 2005 with sql server 2005. in my database table contains
Table Name : Page_Content




Page_Id
101
102

1
Abc
Pqr

2
Lmn
oiuALTER PROCEDURE [dbo].[SELECT_CONTENT]
(@lang_code varchar(max))    
AS
begin declare @a as varchar(max)set @a = @lang_code
 
Select page_id,@a From page_content
end
Here in this above store procedure i want to pass 101 to @lang_code
here is my output, but this is wrong output




Page_Id
Column1

1
101

2
101
 but i want following output




Page_Id
101
102

1
Abc
Pqr

2
Lmn
oiu

View 4 Replies View Related

Creating A Store Procedure With Output Parameter

Oct 22, 2005

Hi all,Could someone give me an example on how to create and execute the store procedure with using output parameter?In normal, I create the store procedure without using output parameter, and I did it as follow:CREATE PROC NewEmployee (ID int(9), Name Varchar (30), hiredate DateTime, etc...)ASBEGIN      //my codeENDGOWhen i executed it, I would said: Execute NewEmployee 123456789, 'peter mailler', getDate(), etc....For output parameter:CREATE PROC NewEmployee (ID int(9), Name Varchar (30), hiredate DateTime, @message Varchar(40) out)ASBEGIN      insert into Employee ......      //if error encountered      set @message = "Insertion failure"ENDGOExec NewEmployee 123456789, 'peter mailler', getDate(), do I need to input something for the output parameter here?Anyone could give me an example on how to handle the output parameter within the store procedure coz I am not sure how to handle it?Many thanks.

View 1 Replies View Related

Sort Expression As Store Procedure Parameter?

Jul 23, 2005

Can I pass a sort expression into a store procedure as a parameter? Iwant to do sth like the following but do not know if it's possible.Maybe I need some tricks to work around?CREATE Procedure SelectAllRoles(@SortExpression varchar(20))ASIF @SortExpression <> ''THENSELECT Id, Name, Name2, LastUpdatedFROM [Role]ORDER BY @SortExpressionELSESELECT Id, Name, Name2, LastUpdatedFROM [Role]GOThanks!Zhu Ming

View 1 Replies View Related

Want To Write Store Procedure That Takes Parameter In SQL

Mar 5, 2008



Some one please tell me how do I write store procedure that receives/takes parameter values
I want to write store procedure which takes ID as a parameter

some one tell me how do I write store procedure that takes parameter
if possibel please show me example of it

iam new to this
thankyou


maxs

View 3 Replies View Related

SQL Server 2012 :: Input Parameter For Store Procedure Like In Statement Value 1 / Value 2

Jun 20, 2014

How can I input parameter for Store Procedure like In ('Value1','Value2')

Example :
Create procedure GetUsers
@Users nvarchar(200)
as
Select * from Users where UserID in (@Users)

View 6 Replies View Related

Creating Store Procedure Accepting Customer ID As Input Parameter

Nov 12, 2015

Display based on customerid display max of item they purchased on a order display only number like cust id pursed 12 items in 3rd order so when i enter customerid it should display 12.

using row number in sql server 2012.creating storeprocedure accepting customer id as input parameter.

cid        oid       items
1           1            10
1           2          12
1          3           3
1        4               4

so if we enter 1 as custid it got to give us 12 as the result..

View 7 Replies View Related

Reporting Services :: Pass Selected Parameter To Store Procedure Used To Populate Filter

May 12, 2015

I am working on a report where some customization is need to be delivered.. situation is , i have some parameter in report @USER_ID , @Report_Type where i am proving selection to user to select Report Type (Pending Or Completed) and passing USER_ID auto matically from URL string of user login(C# code).

I have another parameter @USER_IDS which is multiple selection for user and it will be filled with the users which lie under passed @USER_ID means i just need to add dataset with the query to select users from mapper table where reporting_head =@USER_ID, simple, but i have requirement to populate the underlined users with the selection of @USER_ID and @Report_Type and it need some TSQL code to populate so i am using Another store procedure and using same parameter as my main store procedure has .

Now i am using dataset with this store procedure  to fill my @USER_IDS  parameter 

Both parameters value will be passed from main report parameters now , when i am previewing a report i am getting error

and i also tried to write exec statement in dataset query with the main repport paramters but exec is not supported ..

View 2 Replies View Related

Call Store Procedure From Another Store Procedure

Nov 13, 2006

I know I can call store procedure from store procedure but i want to take the value that the store procedure returned and to use it:

I want to create a table and to insert the result of the store procedure to it.

This is the code: Pay attention to the underlined sentence!

ALTER PROCEDURE [dbo].[test]



AS

BEGIN

SET NOCOUNT ON;



DROP TABLE tbl1

CREATE TABLE tbl1 (first_name int ,last_name nvarchar(10) )

INSERT INTO tbl1 (first_name,last_name)

VALUES (exec total_cash '8/12/2006 12:00:00 AM' '8/12/2006 12:00:00 AM' 'gilad' ,'cohen')

END

PLEASE HELP!!!! and God will repay you in kind!

Thanks!

View 7 Replies View Related

Using Pack.Conf. SQLServer To Store Source Conn. Str. But Its Not Working.

Feb 21, 2008

Problem:

i have configure a SSIS package in BIDS to use an Env. Var. to store the location of the .dtsconfig file for the connection to my SSIS configuration table in SQL. I than use another Env. Var. to retrieve the source conn. str. from my SQL SSIS configuration table. Once my package loads, i select a simple sql task to execute, but it comes back and says that it cannot make the connection. How does the SQLServer connection string know what password to use? This is where i'm confused. I believe it loads the connection string for my source database but it doesn't get the password. I just started working in SSIS and i'm trying to get my head around all of the dynamic options of SSIS. I appreciate your time and help.

Problem Details:
package A has 1 sql execute task
Env.Var. 1 stores the location of the .dtsconfig file that stores the connection data to ssis sqlserver configuration table (used to store additional package properties in sql server)
Env. Var. 2 stores the connection string of my source database.
package protection level set at "EncryptSensitiveWithPassword"
the sql task is assigned to the source connection string,right click and execute task.

here is the error:
Failed to acquire connection "source database" Connection may not be configured correctly or you may not have the right permission on this connection.

I'm executing this from within BIDS just to check that my setup is going to work.

Here are some items i've tried or troubleshoot:

1. If I roll back the 2 package Configs or disable using package configs and replace my original source connection manager and create a new connection manager than i can get the sql task to successfully execute again. It won't usually work by just disable the package configs. i had to delete the existing source connection manager.


2. create a new package trying the same steps as above.

3. change the package protection level to "encryptSensitiveWithPassword" expecting this to retain the source connection password. I tried a variety of package protection levels.

Thoughts???? help.....please....

View 9 Replies View Related

Good Practice When Working With Objects Using SQLServer As A Secure Store.

Oct 11, 2006



I'm sure this has been asked plenty of times before, so I'm after a link to a good answer.

I have tens of thousands of milk crates, holding dozens of different types of milk in hundreds of locations. I am used to working with objects but not databases. For this situation however I want the security of SQLServer transactions to track, for example, when a robot moves a crate from one location to another.

I am thinking of using SQLServer as a store. On startup I want to get my ecosystem of objects out of the store. While I am running, I'll just use objects. When I change an object property I want it to securely persist. I don't want to snapshot the whole menagerie of object states, just update the values that changed. Which will sometimes include the addition or deletion of objects. How do I do this? Is there an example somewhere that does this (or approximately this)?

I use VB and have Visual Studio 2005. (Which, by the way, is stunning. I thought all that "you will use less time and code more and better" talk was just hype. But its for real. Amazing product.)

tia

John









View 1 Replies View Related

How It Is Working Using SQL Server Configuration Type To Store Connection Strings

Dec 12, 2007

Hello,
I am wondering how it is going to work using SQL Server configuration type to store connection string in the SQL Server table. How does SSIS know what database to connect to if its connection string is store in the database? Thanks

View 5 Replies View Related

Store Pocedure And Parameter

Jan 30, 2008

hello guy...i have a problem with my coding....i have store procedure that already create in mssql server 2005...that store procedure have a parameter....
Store procedure code
 set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[Users_login]
@username varchar(50),
@password varchar(50)
AS
BEGIN
SET NOCOUNT ON;
SELECT User_application_id, User_fname, User_lname, User_last_activity
FROM Users U
WHERE U.User_name = @username AND U.User_password=@password
END this is my asp.net code Dim connLog As New SqlConnection
Dim cmdlog As New SqlCommand
Dim readerLog As SqlDataReader

connLog.ConnectionString = ConfigurationManager.ConnectionStrings("PVMCCon").ToString()
With cmdlog
.CommandType = CommandType.StoredProcedure
.CommandText = "[Users_login]"
.Connection = connLog
.Parameters.Add("@username", SqlDbType.VarChar, 50, userTxt.Text.ToString())
.Parameters.Add("@password", SqlDbType.VarChar, 50, passTxt.Text.ToString())
End With
Try
connLog.Open()
readerLog = cmdlog.ExecuteReader()
If readerLog.HasRows Then
While readerLog.Read
usernameStatusLabel.Text = readerLog("User_fname").ToString() & " " & readerLog("User_lname").ToString()
lastActivityLabel.Text = readerLog("User_last_activity").ToString()
Session("AuthenticatedID") = readerLog("User_application_id").ToString()
End While
PanelLoginSuccess.Visible = True
PanelLoginView.Visible = False
Else
SignInPopupExtender.Show()
End If
Catch ex As Exception
loginStatusLabel.Text = ex.ToString
Finally
connLog.Close()
End Try
End Sub so the problem is...how can using the store procedure that already have a parameter and how i want to set the parameter value in my code?????

View 5 Replies View Related

Parameter Not Working

Jan 19, 2007



I am pretty new to reporting services and hope some of you experts can help.

I created a report from AS cube that has a column "Description" with 30 distinct values. The report has a multi-value parameter "Description" with the following details:

Data Type : String
Value Field: Description
Label Field : Description

The filter that i have on the table is =(Fields!Description.Value) = Cstr(Parameters!Description.Value(0))

When i preview the report i only see the top 1 item from the values that i select from the parameter list.

What am i doing wrong?

Any help is appreciated.



Thanks

View 8 Replies View Related

RS 2005: Stored Procedure With Parameter It Runs In The Data Tab But The Report Parameter Is Not Passed To It

Feb 19, 2007

Hello,
since a couple of days I'm fighting with RS 2005 and the Stored Procedure.

I have to display the result of a parameterized query and I created a SP that based in the parameter does something:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE [schema].[spCreateReportTest]
@Name nvarchar(20)= ''

AS
BEGIN

declare @slqSelectQuery nvarchar(MAX);

SET NOCOUNT ON
set @slqSelectQuery = N'SELECT field1,field2,field3 from table'
if (@Name <> '')
begin
set @slqSelectQuery = @slqSelectQuery + ' where field2=''' + @Name + ''''
end
EXEC sp_executesql @slqSelectQuery
end

Inside my business Intelligence Project I created:
-the shared data source with the connection String
- a data set :
CommandType = Stored Procedure
Query String = schema.spCreateReportTest
When I run the Query by mean of the "!" icon, the parameter is Prompted and based on the value I provide the proper result set is displayed.

Now I move to "Layout" and my undertanding is that I have to create a report Paramater which values is passed to the SP's parameter...
So inside"Layout" tab, I added the parameter: Name
allow blank value is checked and is non-queried

the problem is that when I move to Preview -> I set the value into the parameter field automatically created but when I click on "View Report" nothing has been generated!!

What is wrong? What I forgot??

Thankx for any help!
Marina B.





View 3 Replies View Related

Output Parameter In SP Not Working?

Feb 28, 2008

Hi everyone,
   This is the sp i have written which works fine in the sql server management studio when i run it but when i call it from the vb.net web application the output parameters are generated as nothing but the sp return the data from the select statement, so here is the sp followed by vb.net code to access the sp. 
This is the Store ProcedureCREATE PROCEDURE [dbo].StaffAuthenticate
@Staff_ID nvarchar(50),
@Password nvarchar(15),
@IsAuthenticated int OUTPUT,
@P_message_code int OUTPUT
AS
BEGIN
SET NOCOUNT ON;
Select * From Staff;
SET @IsAuthenticated = 0;
SET @P_message_code = 100;
RETURN END
GOThis is the VB.NET code.  Dim ConStr As String = GenericDataAccess.GetConnection(IConnectionStrNames.OAAStaffAuthStr)
Dim Conn As SqlConnection = New SqlConnection(ConStr)
Dim Command As SqlCommand = Nothing
Conn.Open()
Command = New SqlCommand()
Command.Connection = Conn
Command.CommandText = "StaffAuthenticate"
Command.CommandType = CommandType.StoredProcedure

Command.Parameters.Add("@Staff_ID", SqlDbType.NVarChar, 50).Value = UserId
Command.Parameters.Add("@Password", SqlDbType.NVarChar, 15).Value = Password
Command.Parameters.Add("@IsAuthenticated", SqlDbType.Int).Direction = ParameterDirection.Output
Command.Parameters.Add("@P_message_code", SqlDbType.Int).Direction = ParameterDirection.Output

Dim myDataReader As SqlDataReader = Command.ExecuteReader()
Dim Res As New StringBuilder

While (myDataReader.Read())
For i As Integer = 0 To myDataReader.FieldCount - 1
Res.Append(myDataReader(i).ToString())
Res.Append(" ; ")
Next
Res.AppendLine()
End While

MsgBox(Command.Parameters("@IsAuthenticated").Value)
MsgBox(Command.Parameters("@P_message_code").Value)
MsgBox(Res.ToString, MsgBoxStyle.Information, "DAL : StaffSqlAuthenticate")  Thanks for all the help, Really could use one. Kabir 
 

View 5 Replies View Related

Working With DataTableAdapter And Parameter

Apr 18, 2006

I have created a dataset table adapter with existing stored procedure.Stored Procedure has 3 parameters.I am trying to create a page where use can search on different field and generate report on gried view control. They can either search by one parameter , two or may be multiple.
But When I try to run this it gives me error if I dont put all three parameters, how can I make this work so that even if I put one, two or multiple parameter it works error free.
CREATE PROCEDURE [dbo].[ConvertParentSupplier_SP] @supplierID varchar(10),@active varchar(10)ASselect distinct Supplier.SupplierID, SupplierName, ParentSupplierID, isnull(Active,'')as Activefrom supplier where supplier.ParentSupplierID =@supplierIDand Supplier.SupplierID=Supplier.ParentSupplierID and supplier.active=@activeorder by supplier.supplieridGO

View 1 Replies View Related

Why Is My Output Parameter Not Working?

Jul 23, 2005

Hi,I am currently creating an ASP page that returns a recordset of searchresult based on multiple keywords. The where string is dynamicallybuilt on the server page and that part work quite well. However I wantto also return the number of records in the recordset and I can notmanage to get any output parameter working. Even if I assign SELECT@mycount=100 (or SET @mycount=100) in the SP the only value set inmycount is always NULL. I tested various theories (e.g. early exit,order & naming of parameters etc. but I can not make the SP set theoutput parameters - has it anything to do with the execute?). @mycountalso returns NULL if I test it in SQL QA.What's wrong with this SP (as regards mycount):CREATE PROCEDURE dbo.spFindProducts@mycount integer OUTPUT,@whereString varchar (1000)AS--SET NOCOUNT ON--Set a Default value for the Wherestring which will return all recordsif the Wherestring is blankIF @whereString is NullSELECT @whereString = 'AND TblProduct.ProductID is not null'--Declare a variable to hold the concatenated SQL stringDECLARE @SQL varchar(2500)-- AND (((UPPER([TblProduct].[ProductName] + [ProductGroupCode] +[AttributeValue1] +-- [SearchWords] + [tblSupplier].[SupplierCode] + [SupplierDesc]))Like '%screw%'))SELECT @SQL = 'SELECT TblProduct.ProductID, TblProduct.ProductName,TblProduct.ChapterCode, tblProduct.ProductGroupCode' +' FROM (TblProduct LEFT JOIN TblStockItem ON TblProduct.ProductID =TblStockItem.ProductID) ' +' LEFT JOIN tblSupplier ON TblProduct.SupplierCode =tblSupplier.SupplierCode' +' WHERE 1=1 ' + @whereString +' GROUP BY TblProduct.ProductID, TblProduct.ProductName,TblProduct.ChapterCode, TblProduct.ProductGroupCode'SELECT @mycount = 200; -- testexecute (@SQL);-- next line seems to be ignored ?SELECT @mycount = @@rowcount;GOtiaAxel

View 11 Replies View Related

Cascading Parameter Not Working

Sep 20, 2007

Hi Friends,

I have 2 parameter One is Office (Listbox) another Account(list box) , where Account list box filling is dependent on the Office selection.

In published report when I select the office from list box the page got refreshed but it not updating the account list, the account list box looks diabled?

I have define the two parameter and for the second parameter here is the query

="SELECT ACCOUNT_ID, ACCOUNT_NUMBER FROM MLGDB2.A_ACCOUNT " &
"where OFFICE_ID =" & Parameters!paramOffice.Value &
"ORDER BY ACCOUNT_ID "

Can any one help me.

Thanks

Novin

View 4 Replies View Related

Stored Procedure With User!UserID As Parameter, As Report Parameter?

Jul 2, 2007

I had thought that this was possible but I can't seem to figure out the syntax. Essentially I have a report where one of the parameters is populated by a stored procedure.

Right now this is easily accomplished by using "exec <storedprocname>" as the query string for the report parameter. However I am not clear if it is possible to now incorporate User!UserID as parameter to the stored procedure. Is it? Thanks

View 1 Replies View Related

SQL Server 2012 :: Store Multi Values In One Parameter?

Sep 29, 2015

i design SP for insert data in 2 tables i need to store list of array in one parameter to complete my query i try the table value but it`s not good for me because table value is readonly and i need to insert data with list of array .....

View 5 Replies View Related

Dynamic SQL && Parameter Inside A SP Not Working

Nov 5, 2006

When I run the query below on my DB, it gives the following error:
Server: Msg 8114, Level 16, State 5, Procedure partstlist ...Error converting data type varchar to float.
Only after I replace the      " + @d + " section with a float numnber such as 39020.5 does it work fineIt's reported that my temporary table named #tbl doesn't exist within the scope of the environment that the EXEC(UTE) command operates.Someone said that I couldn't combine dynamic SQL and temporary tables or table variables...and that I would have to use a global temp table "##tbl" or a permanent table.
So here I have appearantly a "DYNAMiC SQL & TEMPORARY TABLE" problem. That's sure.
But I don't have much knowledge and experience with SQL tables and have difficulty forming a global temp table "##tbl" or a permanent table.and I have to employ the datetime variable dynamically @d inside the Dynamic SQL string.
So what kind of a code should I use here to properly combine Dynamic SQL string with my dynamic datetime variable? I'd be grateful if you could post your code suggestions so that I can try them on my DB ..
alter PROCEDURE partslist@no INT,@dt DATETiMEASDECLARE @s VARCHAR(5000)DECLARE @d floatSET @d = CONVERT (float, @dt2)create TABLE #tbl(pageindex int IDENTITY(1,1) PRIMARY KEY , prt_name varchar(50), prt_country varchar(50) , prt_date smalldatetime, partID int , prt_cat varchar(50),prt_product_type nvarchar(10), producer_company nvarchar(50), producer_city nvarchar(50), prt_released DATETIME, dvalue float, datevalue varchar(26) )BEGiNSET ANSI_WARNINGS OFFSET ROWCOUNT @noSELECT @s = 'INSERT INTO #tbl SELECT prt_name, Countries.Val AS ''prt_country'' , prt_date, partID , partsCategories.Val AS ''prt_cat'' , partsProductTypes.Val AS ''prt_product_type'' , producers.producer_company AS ''producer_company'' , producers.producer_city AS ''producer_city'' ,  prt_released , CONVERT (float, prt_released) , CONVERT (varchar(26) , prt_released, 109) FROM producer_parts  JOIN partsProductTypes ON (producer_parts.prt_product_type = partsProductTypes.ID) JOIN partsCategories ON (producer_parts.prt_cat = partsCategories.ID)JOIN Countries ON (producer_parts.prt_country = Countries.ID) JOIN producers ON (producer_parts.producerID = producers.producerID) WHERE prt_visible = 1  AND CONVERT (float, prt_released) > ' + @d + ' AND CONVERT (float, prt_released) < 39025.5ORDER BY prt_released DESC SELECT * FROM  #tbl'EXEC (@s)SET ROWCOUNT 0ENDGO partslist 10 , '10.10.2006 10:10:10' -- This is just to test the SP.
This code may not be syntatically correct, it's a re-edited version of my code which works fine in my DB.

View 2 Replies View Related

Store Procedure Not Store

Nov 20, 2013

My store Procedure is not save in Strore Procedure folder at the time of saving it give me option to save in Project folder and file name default is SQLQuery6.sql when i save it after saving when i run my store procedure

exec [dbo].[SP_GetOrdersForCustomer] 'ALFKI'

I am getting below error :

Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'dbo.SP_GetOrdersForCustomer'.

View 13 Replies View Related

Report Parameter Not Working Properly After Deploy

Feb 21, 2008

Hi All

I have a parameter @User givning a dropdown list from a dataset:

SELECT FullName FROM tvf_GetListOfUsers ('LDAP://OU=Business
Users,OU=Users,OU=Production,DC=MRP,DC=NET,DC=NZ','Subtree')
ORDER BY FullName


The parameter is passed to a second dataset when the User is chosen:

SELECT GroupName FROM tvf_GetUserMembership('LDAP://cn=' + @User +
',ou=business users,ou=users,ou=production,DC=MRP,DC=NET,DC=NZ')
ORDER BY GroupName

This works fine in VS but when I deploy it only works the first time.

That is, I enter the username and run the report - it brings back the
desired results. But when I enter a different username it just redisplays the
old data from the first username I entered.

Any ideas?

Thanks
Pete

PS The funcions above called in the datasets are for querying Active
Directory but I think this is not the cause of the problem.

View 1 Replies View Related

Multi-value Parameter Default Values Not Working

Jul 12, 2007



I have a parameter that gets it's available values from a dataset.



I use this exact same data to populate the default values.



When I run the report, the available values get populated; however the default values are not being selected.



This works on other parameters on the same report. However, on this parameter, it is not working.



I have tried ltrim/rtrim (Been burned with that before when the field type is a char)

I change the data field in the query, without changing the parameter setup, and it works...



Here is my query:




Code Snippet

Select distinct
CityName

from dimHotel dH (NOLOCK)
Inner join factHotel fH (NOLOCK)
on dh.HotelKey = fH.HotelKey

Where dH.CityName <> ''
and dH.CityName is not null
and fH.ClientKey in (@ClientID)

Order by CityName





The parameter is setup correctly, and matches the setup of another parameter on the same report that is working fine.



I have tried deleting the parameter and re-adding it. This did not work.

I also deleted and re-added the query. No luck.



Any ideas??

Thanks!!



Thanks

View 3 Replies View Related

Reporting Services :: Hidden Parameter Stops Working

Nov 22, 2015

I have a working set of parameters, when they are all visible. However, when I change the second parameter to hidden it stops working. 

First Parameter - Contains static values 'MTD' & Daily
Second Parameter - has available values and a default. Set up to be invalidated when the First parameter changes... like i say it works beautifully when not hidden.
Third Parameter - has an expression takes the value of the 2nd parameter value, removes the first character off and then converts the remainder to a date and uses it as its default value.

It based on this webpage: URL....However, when i set the second parameter to hidden it no longer works.

View 2 Replies View Related

SSRS 2005 Multivalue Parameter Not Working On The Asp Page.

Oct 30, 2007

Hi,
i am working on SSRS 2005. i have one problem while accessing report throgh ASP.net pages.

i have used multivalue parameters in the report. on the report server it works fine but on the .asp page when that report is accessed, drop down is not working,

can anybody guide me regarding this , what to do?

thanks and Regards,
Vishruti.

View 1 Replies View Related

Multivalue Parameter Dropdown Not Working In Aspx Page

Feb 21, 2008



Hi All
i have created one report using SSRS 2005, in this report one parameter is multivalue parameter, this report preview working fine and user able to select multivalues. but the problem is i deployed this report in server and opened in client system (reports shown to user using report viewer in aspx pages), i am able to access the report but multivalue parameter dropdown is not working.
when i clicked on multivalue parameter dropdown report refreshing and remaining parameters default vaues coming up..
i am unable to find the solution
please anyone help me

thanks

View 3 Replies View Related

Reportviewer Date Parameter And Calendar.. Not Working And Javascript Errors??

Oct 22, 2007


Hi, I've got this ssrs 2005 report that works great passing a few
security related parameters from asp.net codebehind. However, there
are two date related parameters that won't be coming from my web form,
but rather from the report form itself. When I test the report's date
parameters from visual studio it work fine, but when I attempt the
same report from a reportviewer no matter what input I place on the
report's date fields or even if I select the date picker, the report
simply resets to default and reloads. And actually the date picker
from the reportviewer does not not even pop up.



Here's my aspx code:


<%@ Page Language="VB" AutoEventWireup="false"
Inherits="_ReportViewer" MasterPageFile="~/Main/MasterPage.master"
CodeFile="~/Reports/CashSales.aspx.vb" Title="Cash Sales" %>


<%@ Register Assembly="Microsoft.ReportViewer.WebForms,
Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>


<asp:Content ID="Content1" ContentPlaceHolderID="Content1"
runat="Server">
<rsweb:ReportViewer BackColor="Transparent" ZoomMode="PageWidth"
Width="100%" ProcessingMode="Remote" ID="ReportViewer1"
runat="server">
<ServerReport ReportPath="/Retailer/CashSales"
ReportServerUrl="http://myserver/reportserver" />
</rsweb:ReportViewer>
</asp:Content>


Note: I'm only passing the parameters that are not coming from the
reportviewer form.




the code behind:




Imports system.web.security.membership
Imports system.web.security.Roles
Imports Microsoft.Reporting.Webforms


Partial Class _ReportViewer
Inherits System.Web.UI.Page
Private Users As New Retailer.Core()


Protected Overrides Sub OnLoad(ByVal e As EventArgs)


Dim Roles() As String =
GetRolesForUser(Page.User.Identity.Name.ToString)


Dim cred As New Retailer.ReportServerCredentials("myuser",
"mypassword", "mydomain")
ReportViewer1.ServerReport.ReportServerCredentials = cred


Dim param As New ReportParameter("r_user",
Page.User.Identity.Name.ToString)
Dim param2 As New ReportParameter("r_role", Roles(0))
Dim p() As ReportParameter = {param, param2}


ReportViewer1.ServerReport.SetParameters(p)
ReportViewer1.ServerReport.Refresh()


End Sub
End Class



on my reportviewer form, at the top I have parameters for startdate
and end date, they are not set to internal or hidden.

If I select the calendar icon, i get a javascript error


Line: 606
Object Required


When I attempt to debug I get a Just-In-time failed : Unspecified
error. Check the documentation index for 'Just-in-time debugging,
errors' for more information. So pretty much I can't see the error or javascript in question.


If i enter anything in the date fields, it disregards them setting them to the defaults.

Again, if i run the report from my vs.net client (not using reportviewer), i can select the calendar and enter dates and it respects them.

Any chance I need to patch sql server or reporting services? maybe ie. I'm on IE 7.0.5730.11
Could my problem be that selecting items on the report itself fails to send my credential information from my codebehind?


Thanks for any help or information!

View 26 Replies View Related







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